Toward full ACID distributed transaction support with Foreign Data - PowerPoint PPT Presentation

https://www.2ndQuadrant.com Event / Conference name Location, Date Toward full ACID distributed transaction support with Foreign Data Wrapper Masahiko Sawada

https://www.2ndQuadrant.com PGCon 2020 What is Foreign Data Wrapper • Implementation of the SQL/MED • Access data that resides outside PostgreSQL using regular SQL queries • Foreign tables • Pluggable architecture • https://wiki.postgresql.org/wiki/Foreign_data_wrapper • Writable at 9.3 or later

https://www.2ndQuadrant.com PGCon 2020 Foreign Data Wrapper PostgreSQL MySQL Oracle Database PostgreSQL Client mysql_fdw postgres_fdw oracle_fdw

https://www.2ndQuadrant.com PGCon 2020 Current Status • FDW plugin is responsible for transaction management on the remote nodes (foreign transaction) • begin, commit, rollback and savepoint • Foreign transaction termination can use XactCallback, which is called BEFORE committing the local transaction

https://www.2ndQuadrant.com PGCon 2020 postgres_fdw’s Transaction Managements • Open a foreign transaction when FDW access the remote first time within the local transaction • Foreign transaction uses SERIALIZABLE when the local transaction has SERIALIZABLE. • Otherwise use REPEATABLE READ • This ensures that if a query performs multiple table scans on the remote server, it will get snapshot- consistent results for all the scans

https://www.2ndQuadrant.com PGCon 2020 Two Major Limitations • Atomic Commit • Read Issues

https://www.2ndQuadrant.com PGCon 2020 Atomic Commit Problem • Foreign transactions are committed one by one before the local transaction commits • If a remote server crashes during the commit, some transactions are committed whereas other are not (it’s the same when the local node crashes) • There is no guarantee that all servers (including local) are committed or rollbacked

https://www.2ndQuadrant.com PGCon 2020 Transaction Commit w/ FDW L R 1 R 2 COMMIT OK COMMIT COMMIT OK COMMIT OK

https://www.2ndQuadrant.com PGCon 2020 Transaction Commit w/ FDW L R 1 R 2 COMMIT Rollbacked NG!! ROLL BACK COMMIT OK COMMIT

https://www.2ndQuadrant.com PGCon 2020 Two-Phase Commit Protocol • A type of atomic commitment protocol • Blocking protocol • Consists of two phases: prepare phase and commit phase 1.Coordinator sends PREPARE request to all participants 2.Coordinator sends COMMIT request to all participants if and only if all participants sent OK in prepare phase 3.Otherwise coordinator sends ROLLBACK request to all participants

https://www.2ndQuadrant.com PGCon 2020 2PC for FDW • First proposal at 2015 • “Transactions involving multiple postgres foreign servers, take 2” by Masahiko Sawada and Ashutosh Bapat • The core manages remote transactions • Introduces new FDW APIs for transaction managements • Commit, Rollback, Prepare and GetPrepareId

https://www.2ndQuadrant.com PGCon 2020 2PC for FDW - Commit Transaction involving foreign transactions implements commit via following steps: 1.Prepare all foreign transactions 2.Commit locally 3.Commit all foreign transaction

https://www.2ndQuadrant.com PGCon 2020 2PC for FDW • The core persists information about foreign transactions to disk via WAL records so that these can be recovered after restart • Introduce a new background process called Transaction Resolver • Executing COMMIT PREPARED in-progress foreign transactions • Resolving recovered or in-doubt foreign transactions

https://www.2ndQuadrant.com PGCon 2020 FDW transaction with 2PC L TR R 1 R 2 Commit Ok Commit Commit Prepare Prepared Commit Prepare Prepared

https://www.2ndQuadrant.com PGCon 2020 Scenario-1: A participant crashes before commit L R 1 R 2 Commit NG Rollback Rollback Prepare Prepared Prepare NG!!

https://www.2ndQuadrant.com PGCon 2020 R 1 during the recovery. information is recovered Foreign transaction L TR R 2 Scenario-2: Local node crashes after commit L Commit Commit Commit Prepare Prepared Prepare Commit Prepared

https://www.2ndQuadrant.com PGCon 2020 Interruptions L TR R 1 R 2 Commit Ok Commit Commit Prepare Prepared Commit Prepare Prepared

https://www.2ndQuadrant.com PGCon 2020 R 2 R 1 TR R 2 Interruptions L Ok, committed. But we leaves Commit Cancel transactions. Commit Commit Commit Prepare Prepared Prepared Prepare

https://www.2ndQuadrant.com PGCon 2020 Atomic Visibility Either all or none of each transaction’s updates are observed by other transaction

https://www.2ndQuadrant.com PGCon 2020 committed data will be able to see only one A transaction starts in this period TR R 2 R 1 L Atomic Visibility - uncertain period Commit Commit Prepare Prepared Commit Prepare Prepared

https://www.2ndQuadrant.com PGCon 2020 Read Issues • One of the most important goals of FDW is that if the client uses PostgreSQL with the foreign server then it needs to function the same way as a single PostgreSQL server would do • Unfortunately, current FDW doesn’t work even if a transaction involves only one remote node

https://www.2ndQuadrant.com May occur (*1) : PostgreSQL doesn’t support Read Uncommitted transaction isolation level. Don’t occur Don’t occur Don’t occur Serializable (*2) May occur Don’t occur Don’t occur Repeatable Read May occur Don’t occur PGCon 2020 Read Committed May occur May occur May occur (*1) Read Uncommitted Phantom Read Read Non-repeatable Dirty read Transaction Isolation Levels (ANSI/ISO) (*2) : Don’t occur in PostgreSQL

https://www.2ndQuadrant.com Example 1: two clients access the same foreign table L R PGCon 2020 Begin SELECT SELECT RC Tx count(*) 100 rows 100 rows count(*) DELETE 10 rows and COMMIT Read data with the Begin same snapshot RR Tx

https://www.2ndQuadrant.com PGCon 2020 R 1 R 2 L Example 2: two clients access the different foreign table Begin SELECT SELECT RR Tx 90 rows tbl2 tbl1 100 rows DELETE 10 rows from tbl2 and COMMIT Begin RR Tx

https://www.2ndQuadrant.com PGCon 2020 Summary • In both case, the client gets the different result than when using single PostgreSQL server • There is not guarantee that the cluster return the consistent result among all foreign servers even when there is only one foreign table.

https://www.2ndQuadrant.com PGCon 2020 Global Snapshots • Provide globally consistent snapshots • Global snapshots could be PostgreSQL snapshots (xmin, xmax and in-progress xids), CSN, timestamp etc

https://www.2ndQuadrant.com PGCon 2020 Central Transaction Manager • Postgres-XL has a dedicated global transaction manager node (GTM node) • All coordinators have to access GTM to get a global consistent snapshot • GTM provides PostgreSQL snapshot consistent across all data nodes • Google Percolator has similar concept: Timestamp Oracle • Which can produce timestamps in a strictly increasing order • Timestamps coming from the timestamp oracle are used as the time when read/write operation happens • But.. SPOF issues with GTMs

https://www.2ndQuadrant.com PGCon 2020 Clock-SI • Jiaqing Du et al., “Clock-SI: Snapshot Isolation for Partitioned Data Stores Using Loosely Synchronized Clocks”, 2013 IEEE 32nd International Symposium on Reliable Distributed Systems, 2013. • Use physical time as CSN • Address clock skew problem • A patch which implements Clock-SI paper was proposed at 2018 • “Global Snapshots” by Stas Kelvich

https://www.2ndQuadrant.com PGCon 2020 Clock Skew A B Tx1: start Tx1: read(x) t Delay until Read(x) timestamp-t t’ t

https://www.2ndQuadrant.com PGCon 2020 Summary • Foreign Data Wrapper is the powerful feature to access the distributed data across heterogenous data stores • A big missing pieces is transaction management • Several ideas are proposed • 2PC over FDW • Clock-SI

https://www.2ndQuadrant.com PGCon 2020 Thank you masahiko.sawada@2ndquadrant.com