Hi,
On Thu, 14 Nov 2024 at 21:22, Emre Hasegeli <e...@hasegeli.com> wrote:
>
> I encountered a problem with logical replication.
>
> The object identifier types, regclass, regproc, regtype, etc. are
> transferred as an oid in the binary mode. However, the subscriber
> expects them as text which makes sense because it cannot do anything
> with the oids. I am getting "invalid byte sequence for encoding
> "UTF8": 0x00" when I try this.
>
> I think the publisher should not transfer these in binary just like
> the data types from extensions. Any opinions?
I tried to test the scenario described. Steps I followed:
1. Create a logical replication setup on table t1 with some initial data.
2. Columns in table t1 are of types regclass, regproc and regtype
3. Now, I inserted some records in the table t1 and also did some
updates on the table t1.
I tested with both 'binary = true' and 'binary = false' option while
creating a subscription. For me replication is working fine and I am
not getting any errors in both the cases.
I have also attached the test script.
Am I trying the correct steps? Can you share a reproducible test case?
Also, which version are you facing this issue?
Have you set any GUC parameters/ Encoding/ Collation?
Thanks and Regards,
Shlok Kyal
#!/usr/bin/env bash
# setup publisher node
./pg_ctl stop -D ../publisher
rm -rf ../publisher publisher.log
mkdir ../publisher
./initdb -D ../publisher
cat << EOF >> ../publisher/postgresql.conf
wal_level = logical
wal_sender_timeout = 0
listen_addresses = '*'
EOF
./pg_ctl start -D ../publisher -l publisher.log
./psql -d postgres -c "CREATE TABLE t1 (a regclass, b regproc, c regtype);"
./psql -d postgres -c "create publication pub1 for table t1"
./psql -d postgres -c "ALTER TABLE t1 REPLICA IDENTITY FULL"
./psql -d postgres -c "INSERT INTO t1 values('pg_type', 64, 'int')"
# setup subscriber node
cluster_name=subscriber
./pg_ctl stop -D ../$cluster_name
rm -rf ../$cluster_name $cluster_name.log
mkdir ../$cluster_name
./initdb -D ../$cluster_name
cat << EOF >> ../$cluster_name/postgresql.conf
wal_level = logical
wal_receiver_timeout = 0
listen_addresses = '*'
port = 9000
EOF
./pg_ctl start -D ../$cluster_name -l $cluster_name.log
./psql -d postgres -p 9000 -c "CREATE TABLE t1 (a regclass, b regproc, c regtype);"
./psql -d postgres -p 9000 -c "create subscription test1 connection 'dbname=postgres host=localhost port=5432' publication pub1 with (binary = true);"
sleep 2
# operations on publisher
./psql -d postgres -c "INSERT INTO t1 values('pg_type', 64, 'int')"
./psql -d postgres -c "update t1 set c = 'text' where c = 'int'::regtype;"
./psql -d postgres -c "update t1 set a = 'pg_proc' where a = 'pg_type'::regclass"
