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'
	);

-- First try an all locally partitioned table
CREATE TABLE all_local_root (a int) PARTITION BY LIST ( a );
CREATE TABLE all_local_partition_1 PARTITION OF all_local_root FOR VALUES IN (1);
CREATE TABLE all_local_partition_2 PARTITION OF all_local_root FOR VALUES IN (2);

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

UPDATE all_local_root SET a = 2;
-- Everything should be on all_local_partion_2 and on the same transaction
SELECT ctid, xmin, xmax, cmax, tableoid::regclass, a FROM all_local_root;

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

-- Connect back to local
\c local
CREATE TABLE local_root_remote_partitions (a int) PARTITION BY LIST ( a );
CREATE FOREIGN TABLE
	local_root_remote_partition_1
PARTITION OF
	local_root_remote_partitions FOR VALUES IN (1)
SERVER
	remote_server
OPTIONS (
	table_name 'remote_partition_1',
	batch_size '10'
);
CREATE FOREIGN TABLE
	local_root_remote_partition_2
PARTITION OF
	local_root_remote_partitions FOR VALUES IN (2)
SERVER
	remote_server
OPTIONS (
	table_name 'remote_partition_2',
	batch_size '10'
);

INSERT INTO local_root_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_remote_partitions;

UPDATE local_root_remote_partitions SET a = 2;
-- Everything should be on remote_partition_2 and on the same transaction
SELECT ctid, xmin, xmax, cmax, tableoid::regclass, a FROM local_root_remote_partitions;

-- Try on the remote
\c remote
SELECT ctid, xmin, xmax, cmax, tableoid::regclass, a FROM remote_partition_1;