Re: row filtering for logical replication
Erik Rijkers <er@xs4all.nl>
From: Erik Rijkers <er@xs4all.nl>
To: Euler Taveira <euler@timbira.com.br>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>, Suzuki Hironobu
<hironobu@interdb.jp>
Date: 2018-11-01T07:56:48Z
Lists: pgsql-hackers
Commits
Same data as JSON:
GET /api/v1/messages/:b64id/commits
the thread's linked commits as JSON, with link sources.
API reference →
-
Release cache tuple when no longer needed
- ed0fbc8e5ac9 15.0 landed
-
Add some additional tests for row filters in logical replication.
- ceb57afd3ce1 15.0 landed
-
Fix one of the tests introduced in commit 52e4f0cd47.
- cfb4e209ec15 15.0 landed
-
Allow specifying row filters for logical replication of tables.
- 52e4f0cd472d 15.0 landed
-
Move scanint8() to numutils.c
- cfc7191dfea3 15.0 cited
-
Replace Test::More plans with done_testing
- 549ec201d613 15.0 cited
-
Reduce relcache access in WAL sender streaming logical changes
- 6ce16088bfed 15.0 cited
-
Small cleanups related to PUBLICATION framework code
- c9105dd3660f 15.0 cited
-
Add a view to show the stats of subscription workers.
- 8d74fc96db5f 15.0 cited
-
Allow publishing the tables of schema.
- 5a2832465fd8 15.0 cited
-
Doc: improve documentation of CREATE/ALTER SUBSCRIPTION.
- 1882d6cca161 15.0 cited
-
Add PublicationTable and PublicationRelInfo structs
- 0c6828fa987b 15.0 cited
-
Remove unused argument "txn" in maybe_send_schema().
- 93d573d86571 15.0 cited
-
Add prepare API support for streaming transactions in logical replication.
- 63cf61cdeb7b 15.0 cited
-
Unify PostgresNode's new() and get_new_node() methods
- 201a76183e20 15.0 cited
-
Use l*_node() family of functions where appropriate
- 2b00db4fb0c7 15.0 cited
-
Add support for prepared transactions to built-in logical replication.
- a8fd13cab0ba 15.0 cited
-
Restore the portal-level snapshot after procedure COMMIT/ROLLBACK.
- ef9480509622 11.13 cited
-
Rename a parse node to be more general
- 91d1f2d30210 14.0 landed
-
Remove unused column atttypmod from initial tablesync query
- 4ad31bb2ef25 14.0 landed
-
SEARCH and CYCLE clauses
- 3696a600e229 14.0 cited
Attachments
- logrep_rowfilter.sh (text/x-shellscript)
On 2018-11-01 01:29, Euler Taveira wrote:
> Em qua, 28 de fev de 2018 às 20:03, Euler Taveira
> <euler@timbira.com.br> escreveu:
>> The attached patches add support for filtering rows in the publisher.
>>
I ran pgbench-over-logical-replication with a WHERE-clause and could not
get this to do a correct replication. Below is the output of the
attached test program.
$ ./logrep_rowfilter.sh
--
/home/aardvark/pg_stuff/pg_installations/pgsql.logrep_rowfilter/bin.fast/initdb
--pgdata=/tmp/cascade/instance1/data --encoding=UTF8 --pwfile=/tmp/bugs
--
/home/aardvark/pg_stuff/pg_installations/pgsql.logrep_rowfilter/bin.fast/initdb
--pgdata=/tmp/cascade/instance2/data --encoding=UTF8 --pwfile=/tmp/bugs
--
/home/aardvark/pg_stuff/pg_installations/pgsql.logrep_rowfilter/bin.fast/initdb
--pgdata=/tmp/cascade/instance3/data --encoding=UTF8 --pwfile=/tmp/bugs
sleep 3s
dropping old tables...
creating tables...
generating data...
100000 of 100000 tuples (100%) done (elapsed 0.09 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done.
create publication pub_6515_to_6516;
alter publication pub_6515_to_6516 add table pgbench_accounts where (aid
between 40000 and 60000-1) ; --> where 1
alter publication pub_6515_to_6516 add table pgbench_branches;
alter publication pub_6515_to_6516 add table pgbench_tellers;
alter publication pub_6515_to_6516 add table pgbench_history;
create publication pub_6516_to_6517;
alter publication pub_6516_to_6517 add table pgbench_accounts ; -- where
(aid between 40000 and 60000-1) ; --> where 2
alter publication pub_6516_to_6517 add table pgbench_branches;
alter publication pub_6516_to_6517 add table pgbench_tellers;
alter publication pub_6516_to_6517 add table pgbench_history;
create subscription pub_6516_from_6515 connection 'port=6515
application_name=rowfilter'
publication pub_6515_to_6516 with(enabled=false);
alter subscription pub_6516_from_6515 enable;
create subscription pub_6517_from_6516 connection 'port=6516
application_name=rowfilter'
publication pub_6516_to_6517 with(enabled=false);
alter subscription pub_6517_from_6516 enable;
-- pgbench -p 6515 -c 16 -j 8 -T 5 -n postgres # scale 1
transaction type: <builtin: TPC-B (sort of)>
scaling factor: 1
query mode: simple
number of clients: 16
number of threads: 8
duration: 5 s
number of transactions actually processed: 80
latency average = 1178.106 ms
tps = 13.581120 (including connections establishing)
tps = 13.597443 (excluding connections establishing)
accounts branches tellers history
--------- --------- --------- ---------
6515 6546b1f0f 2d328ed28 7406473b0 7c1351523 e8c07347b
6516 6546b1f0f 2d328ed28 d41d8cd98 d41d8cd98 e7235f541
6517 f7c0791c8 d9c63e471 d41d8cd98 d41d8cd98 30892eea1 NOK
6515 6546b1f0f 2d328ed28 7406473b0 7c1351523 e8c07347b
6516 6546b1f0f 2d328ed28 7406473b0 5a54cf7c5 191ae1af3
6517 6546b1f0f 2d328ed28 7406473b0 5a54cf7c5 191ae1af3 NOK
6515 6546b1f0f 2d328ed28 7406473b0 7c1351523 e8c07347b
6516 6546b1f0f 2d328ed28 7406473b0 5a54cf7c5 191ae1af3
6517 6546b1f0f 2d328ed28 7406473b0 5a54cf7c5 191ae1af3 NOK
[...]
I let that run for 10 minutes or so but that pgbench_history table
md5-values (of ports 6516 and 6517) do not change anymore, which shows
that it is and remains different from the original pgbench_history table
in 6515.
When there is a where-clause this goes *always* wrong.
Without a where-clause all logical replication tests were OK. Perhaps
the error is not in our patch but something in logical replication.
Attached is the test program (will need some tweaking of PATHs,
PG-variables (PGPASSFILE) etc). This is the same program I used in
march when you first posted a version of this patch alhough the error is
different.
thanks,
Erik Rijkers