v2-repro.sql

application/sql

Download

CREATE DATABASE local;
CREATE DATABASE remote;

\c local
-- Create and configure postgres_fdw against remote
CREATE EXTENSION postgres_fdw ;
CREATE SERVER remote_server
	FOREIGN DATA WRAPPER postgres_fdw
	OPTIONS (
		host 'localhost',
		dbname 'remote',
		port '5432'
	);

CREATE USER MAPPING FOR postgres
	SERVER remote_server
	OPTIONS (
		user 'postgres'
	);

-- Connect to remote and create the leaf table
\c remote
CREATE TABLE remote_partition_2 (a int);

-- Connect back to local
\c local
CREATE TABLE local_root_some_remote_partitions (a int) PARTITION BY LIST ( a );
CREATE TABLE
	local_root_local_partition_1
PARTITION OF
	local_root_some_remote_partitions FOR VALUES IN (1);

CREATE FOREIGN TABLE
	local_root_remote_partition_2
PARTITION OF
	local_root_some_remote_partitions FOR VALUES IN (2)
SERVER
	remote_server
OPTIONS (
	table_name 'remote_partition_2',
	batch_size '10'
);

INSERT INTO local_root_some_remote_partitions VALUES (1), (1);
-- Everything should be on remote_partition_1 and on the same transaction
SELECT ctid, xmin, xmax, cmax, tableoid::regclass, a FROM local_root_some_remote_partitions;

-- Craft an update statement to move tuples to a remote partion in a batch
UPDATE local_root_some_remote_partitions SET a = 2 WHERE a <> 2;

-- Everything should be on remote_partition_2 and on the same transaction
SELECT ctid, xmin, xmax, cmin, cmax, tableoid::regclass, a FROM local_root_some_remote_partitions;

-- Connect on the remote and check the system columns, cmin should be the same for both rows on a batch insert
\c remote
SELECT ctid, xmin, xmax, cmin, cmax, tableoid::regclass, a FROM remote_partition_2;