Hi,
I've attached lr02.sql, a minimal psql reproducer for this issue in
text mode (binary = false). It uses separate publisher and subscriber
databases in the same instance.
On an affected build, the initial COPY row gets an empty string
instead of the subscriber's default, while a subsequent incremental
INSERT uses the default correctly.
Please run it on a test instance with logical replication enabled and
authentication configured for the subscription connection.
Best regards,
mingjiegao
\set ON_ERROR_STOP on
\pset pager off
\connect postgres
CREATE DATABASE lr02_pub;
CREATE DATABASE lr02_sub;
\connect lr02_pub
\set pub_conn 'host=' :HOST ' port=' :PORT ' user=' :USER ' dbname=lr02_pub
application_name=lr02_sub'
CREATE TABLE lr02_t ();
INSERT INTO lr02_t DEFAULT VALUES;
CREATE PUBLICATION lr02_pub FOR TABLE lr02_t WITH (publish = 'insert');
-- Precreate the slot to avoid CREATE SUBSCRIPTION waiting on itself.
SELECT slot_name FROM pg_create_logical_replication_slot('lr02_slot',
'pgoutput');
\connect lr02_sub
CREATE TABLE lr02_t (v text DEFAULT 'subscriber-default');
CREATE SUBSCRIPTION lr02_sub CONNECTION :'pub_conn' PUBLICATION lr02_pub
WITH (copy_data = true, binary = false,
create_slot = false, slot_name = 'lr02_slot');
DO $$
DECLARE
deadline timestamptz := clock_timestamp() + interval '60 seconds';
BEGIN
LOOP
EXIT WHEN EXISTS (
SELECT 1 FROM pg_subscription_rel
WHERE srsubid = (SELECT oid FROM pg_subscription WHERE subname =
'lr02_sub')
AND srrelid = 'lr02_t'::regclass AND srsubstate = 'r'
);
IF clock_timestamp() >= deadline THEN
RAISE EXCEPTION 'lr02_sub initial synchronization did not finish
within 60 seconds';
END IF;
PERFORM pg_sleep(0.1);
END LOOP;
END
$$;
\echo 'Initial COPY: correct value is ''subscriber-default''; bug gives an
empty string.'
SELECT quote_nullable(v) AS initial_copy_value FROM lr02_t;
\connect lr02_pub
INSERT INTO lr02_t DEFAULT VALUES;
\connect lr02_sub
DO $$
DECLARE
deadline timestamptz := clock_timestamp() + interval '60 seconds';
BEGIN
LOOP
EXIT WHEN (SELECT count(*) FROM lr02_t) = 2;
IF clock_timestamp() >= deadline THEN
RAISE EXCEPTION 'lr02_sub incremental INSERT did not arrive within
60 seconds';
END IF;
PERFORM pg_sleep(0.1);
END LOOP;
END
$$;
\echo 'The incremental row uses the default, unlike the initial COPY row.'
SELECT quote_nullable(v) AS replicated_value, count(*) AS row_count
FROM lr02_t GROUP BY v ORDER BY v;
