From 19890a064ebf53dedcefed0d8339ed3d449b06e6 Mon Sep 17 00:00:00 2001 From: Amit Kapila Date: Wed, 3 Mar 2021 07:28:43 +0530 Subject: Add option to enable two_phase commits via pg_create_logical_replication_slot. Commit 0aa8a01d04 extends the output plugin API to allow decoding of prepared xacts and allowed the user to enable/disable the two-phase option via pg_logical_slot_get_changes(). This can lead to a problem such that the first time when it gets changes via pg_logical_slot_get_changes() without two_phase option enabled it will not get the prepared even though prepare is after consistent snapshot. Now next time during getting changes, if the two_phase option is enabled it can skip prepare because by that time start decoding point has been moved. So the user will only get commit prepared. Allow to enable/disable this option at the create slot time and default will be false. It will break the existing slots which is fine in a major release. Author: Ajin Cherian Reviewed-by: Amit Kapila and Vignesh C Discussion: https://postgr.es/m/d0f60d60-133d-bf8d-bd70-47784d8fabf3@enterprisedb.com --- doc/src/sgml/catalogs.sgml | 10 ++++++++++ doc/src/sgml/func.sgml | 10 ++++++---- doc/src/sgml/logicaldecoding.sgml | 19 ++++++++++--------- 3 files changed, 26 insertions(+), 13 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index db29905e91f..b1de6d0674b 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -11529,6 +11529,16 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx is -1. + + + + two_phase bool + + + True if the slot is enabled for decoding prepared transactions. Always + false for physical slots. + + diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 6c189bfed25..bf99f821496 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -25559,7 +25559,7 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup()); pg_create_logical_replication_slot - pg_create_logical_replication_slot ( slot_name name, plugin name , temporary boolean ) + pg_create_logical_replication_slot ( slot_name name, plugin name , temporary boolean, two_phase boolean ) record ( slot_name name, lsn pg_lsn ) @@ -25571,9 +25571,11 @@ postgres=# SELECT * FROM pg_walfile_name_offset(pg_stop_backup()); parameter, temporary, when set to true, specifies that the slot should not be permanently stored to disk and is only meant for use by the current session. Temporary slots are also - released upon any error. A call to this function has the same - effect as the replication protocol command - CREATE_REPLICATION_SLOT ... LOGICAL. + released upon any error. The optional fourth parameter, + two_phase, when set to true, specifies + that the decoding of prepared transactions is enabled for this + slot. A call to this function has the same effect as the replication + protocol command CREATE_REPLICATION_SLOT ... LOGICAL. diff --git a/doc/src/sgml/logicaldecoding.sgml b/doc/src/sgml/logicaldecoding.sgml index f1f13d81d56..80eb96d609a 100644 --- a/doc/src/sgml/logicaldecoding.sgml +++ b/doc/src/sgml/logicaldecoding.sgml @@ -55,7 +55,7 @@ postgres=# -- Create a slot named 'regression_slot' using the output plugin 'test_decoding' -postgres=# SELECT * FROM pg_create_logical_replication_slot('regression_slot', 'test_decoding'); +postgres=# SELECT * FROM pg_create_logical_replication_slot('regression_slot', 'test_decoding', false, true); slot_name | lsn -----------------+----------- regression_slot | 0/16B1970 @@ -169,17 +169,18 @@ $ pg_recvlogical -d postgres --slot=test --drop-slot The following example shows SQL interface that can be used to decode prepared transactions. Before you use two-phase commit commands, you must set - max_prepared_transactions to at least 1. You must also set - the option 'two-phase-commit' to 1 while calling - pg_logical_slot_get_changes. Note that we will stream - the entire transaction after the commit if it is not already decoded. + max_prepared_transactions to at least 1. You must also have + set the two-phase parameter as 'true' while creating the slot using + pg_create_logical_replication_slot + Note that we will stream the entire transaction after the commit if it + is not already decoded. postgres=# BEGIN; postgres=*# INSERT INTO data(data) VALUES('5'); postgres=*# PREPARE TRANSACTION 'test_prepared1'; -postgres=# SELECT * FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'two-phase-commit', '1'); +postgres=# SELECT * FROM pg_logical_slot_get_changes('regression_slot', NULL, NULL); lsn | xid | data -----------+-----+--------------------------------------------------------- 0/1689DC0 | 529 | BEGIN 529 @@ -188,7 +189,7 @@ postgres=# SELECT * FROM pg_logical_slot_get_changes('regression_slot', NULL, NU (3 rows) postgres=# COMMIT PREPARED 'test_prepared1'; -postgres=# select * from pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'two-phase-commit', '1'); +postgres=# select * from pg_logical_slot_get_changes('regression_slot', NULL, NULL); lsn | xid | data -----------+-----+-------------------------------------------- 0/168A060 | 529 | COMMIT PREPARED 'test_prepared1', txid 529 @@ -198,7 +199,7 @@ postgres=#-- you can also rollback a prepared transaction postgres=# BEGIN; postgres=*# INSERT INTO data(data) VALUES('6'); postgres=*# PREPARE TRANSACTION 'test_prepared2'; -postgres=# select * from pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'two-phase-commit', '1'); +postgres=# select * from pg_logical_slot_get_changes('regression_slot', NULL, NULL); lsn | xid | data -----------+-----+--------------------------------------------------------- 0/168A180 | 530 | BEGIN 530 @@ -207,7 +208,7 @@ postgres=# select * from pg_logical_slot_get_changes('regression_slot', NULL, NU (3 rows) postgres=# ROLLBACK PREPARED 'test_prepared2'; -postgres=# select * from pg_logical_slot_get_changes('regression_slot', NULL, NULL, 'two-phase-commit', '1'); +postgres=# select * from pg_logical_slot_get_changes('regression_slot', NULL, NULL); lsn | xid | data -----------+-----+---------------------------------------------- 0/168A4B8 | 530 | ROLLBACK PREPARED 'test_prepared2', txid 530 -- cgit v1.2.3