-- assumed that there ie postgres_fdw extension available
DROP SERVER loopback1 CASCADE;
DROP SERVER loopback2 CASCADE;
DROP TABLE lt;

-- create two loopback servers for testing consistency on two connections
DO $d$
    BEGIN
        EXECUTE $$CREATE SERVER loopback1 FOREIGN DATA WRAPPER postgres_fdw
            OPTIONS (dbname '$$||current_database()||$$',
                     port '$$||current_setting('port')||$$'
            )$$;
    END;
$d$;

DO $d$
    BEGIN
        EXECUTE $$CREATE SERVER loopback2 FOREIGN DATA WRAPPER postgres_fdw
            OPTIONS (dbname '$$||current_database()||$$',
                     port '$$||current_setting('port')||$$'
            )$$;
    END;
$d$;

CREATE USER MAPPING FOR CURRENT_USER SERVER loopback1;
CREATE USER MAPPING FOR CURRENT_USER SERVER loopback2;

-- create a local table to refer to as foreign table. Add a row. The table has
-- constraints which are deferred till end of transaction. This allows commit
-- time errors occur by inserting data which violates constraints.
CREATE TABLE lt(val int UNIQUE DEFERRABLE INITIALLY DEFERRED);
INSERT INTO lt VALUES (1);
INSERT INTO lt VALUES (3);

-- create two foreign tables each on separate server referring to the local table.
CREATE FOREIGN TABLE ft1 (val int) SERVER loopback1 OPTIONS (table_name 'lt');
CREATE FOREIGN TABLE ft2 (val int) SERVER loopback2 OPTIONS (table_name 'lt');

-- In a transaction insert two rows one each to the two foreign tables. One of
-- the rows violates the constraint and other not. At the time of commit
-- constraints on one of the server will rollback transaction on that server.
-- Ideally, we should not see both the rows. But in case the server where the
-- constraints are violated is checked last for the error, the row inserted on
-- the first server will be visible. So, check both orders.
BEGIN TRANSACTION;
	INSERT INTO ft1 VALUES (1); -- Violates constraint
	INSERT INTO ft2 VALUES (2);
COMMIT TRANSACTION;

BEGIN TRANSACTION;
	INSERT INTO ft1 VALUES (4);
	INSERT INTO ft2 VALUES (3); -- Violates constraint
COMMIT TRANSACTION;

-- Check the data. Ideally we shouldn't see any of the insertions. But we will
-- see either 2 or 4 along-with 1 and 3
SELECT * FROM lt;
-- Clear the data and re-insert for next experiment
DELETE FROM lt;
INSERT INTO lt VALUES (1), (2);
-- Transaction involving local changes and remote changes, one of them or both
-- violating the constraints
BEGIN TRANSACTION;
	INSERT INTO lt VALUES (1); -- violates constraints
	INSERT INTO ft1 VALUES (3);
COMMIT TRANSACTION;
SELECT * FROM lt;

BEGIN TRANSACTION;
	INSERT INTO lt VALUES (3);
	INSERT INTO ft1 VALUES (1); -- violates constraints
COMMIT TRANSACTION;
SELECT * FROM lt;

BEGIN TRANSACTION;
	INSERT INTO lt VALUES (1); -- violates constraints 
	INSERT INTO ft1 VALUES (2); -- violates constraints
COMMIT TRANSACTION;
SELECT * FROM lt;
-- Next test shows local transaction fails if "any" of the remote transactions
-- fail to commit. But any COMMITted transaction on the remote servers remains
-- COMMITTED.
BEGIN TRANSACTION;
	INSERT INTO lt VALUES (3);
	INSERT INTO ft1 VALUES (4);
	INSERT INTO ft2 VALUES (1); -- violates constraints
COMMIT TRANSACTION;
SELECT * FROM lt;
