Re: [PATCH][DOC] Fix for PREPARE TRANSACTION doc and postgres_fdw message.

[Gilles Darold]

Le 09/11/2019 à 02:22, Michael Paquier a écrit :
> On Fri, Nov 08, 2019 at 10:19:01AM +0100, Gilles Darold wrote:
>> I don't think so. The support or not of 2PC is on foreign data wrapper
>> side. In postgres_fdw contrib the error for use with 2PC is not part of
>> the test but it will be thrown anyway. I guess that a test will be
>> valuable only if there is support for readonly query.
> That's what I call a case for negative testing.  We don't allow 2PC to
> be used so there is a point in having a test to make sure of that.
> This way, if the code in this area is refactored or changed, we still
> make sure that 2PC is correctly prevented.  My suggestion is to close
> this gap.  One point here is that this requires an alternate output
> file because of max_prepared_transactions and there is no point in
> creating one with all the tests of postgres_fdw in a single file as we
> have now as it would create 8k lines of expected file bloat, so it
> would be better to split the tests first.  My 2c.
> --
> Michael


Hi Michael, it looks that a separate test is not required at least for
this test. Here is a patch that enable the test in
contrib/postgres_fdw/, expected output:


    -- Make sure that 2PC is correctly prevented
    BEGIN;
    SELECT count(*) FROM ft1;
     count
    -------
       822
    (1 row)

    -- Must throw an error
    PREPARE TRANSACTION 'fdw_tpc';
    ERROR:  cannot PREPARE a transaction that has operated on
    postgres_fdw foreign tables
    ROLLBACK;
    WARNING:  there is no transaction in progress



-- 
Gilles Darold
http://www.darold.net/


diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index f0c842a607..9004e7e076 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -8781,3 +8781,16 @@ SELECT b, avg(a), max(a), count(*) FROM pagg_tab GROUP BY b HAVING sum(a) < 700
 
 -- Clean-up
 RESET enable_partitionwise_aggregate;
+-- Make sure that 2PC is correctly prevented
+BEGIN;
+SELECT count(*) FROM ft1;
+ count 
+-------
+   822
+(1 row)
+
+-- Must throw an error
+PREPARE TRANSACTION 'fdw_tpc';
+ERROR:  cannot PREPARE a transaction that has operated on postgres_fdw foreign tables
+ROLLBACK;
+WARNING:  there is no transaction in progress
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 630b803e26..5df6fe3ae0 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -2479,3 +2479,10 @@ SELECT b, avg(a), max(a), count(*) FROM pagg_tab GROUP BY b HAVING sum(a) < 700
 
 -- Clean-up
 RESET enable_partitionwise_aggregate;
+
+-- Make sure that 2PC is correctly prevented
+BEGIN;
+SELECT count(*) FROM ft1;
+-- Must throw an error
+PREPARE TRANSACTION 'fdw_tpc';
+ROLLBACK;