Horton

Re: [PATCH] Use indexes on the subscriber when REPLICA IDENTITY is full on the publisher

Önder Kalacı <onderkalaci@gmail.com>

View thread View on postgr.es
From: Önder Kalacı <onderkalaci@gmail.com>
To: Amit Kapila <amit.kapila16@gmail.com>
Cc: Peter Smith <smithpb2250@gmail.com>, "shiy.fnst@fujitsu.com" <shiy.fnst@fujitsu.com>, "wangw.fnst@fujitsu.com" <wangw.fnst@fujitsu.com>, Marco Slot <marco.slot@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>, Andres Freund <andres@anarazel.de>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2023-03-07T13:47:07Z
Lists: pgsql-hackers

Attachments

Hi Amit, Peter


> > > Let me give an example to demonstrate why I thought something is fishy
> here:
> > >
> > > Imagine rel has a (non-default) REPLICA IDENTITY with Oid=1111.
> > > Imagine the same rel has a PRIMARY KEY with Oid=2222.
> > >
>

Hmm, alright, this is syntactically possible, but not sure if any user
would do this. Still thanks for catching this.

And, you are right, if a user has created such a schema,
IdxIsRelationIdentityOrPK()
would return the wrong result and we'd use sequential scan instead of index
scan.
This would be a regression. I think we should change the function.


Here is the example:
DROP TABLE tab1;
CREATE TABLE tab1 (a int NOT NULL);
CREATE UNIQUE INDEX replica_unique ON tab1(a);
ALTER TABLE tab1 REPLICA IDENTITY USING INDEX replica_unique;
ALTER TABLE tab1 ADD CONSTRAINT pkey PRIMARY KEY (a);


I'm attaching v35.

Does that make sense to you Amit?

Commits

  1. Add the testcases for 89e46da5e5.

  2. Allow the use of indexes other than PK and REPLICA IDENTITY on the subscriber.

  3. Rework query relation permission checking

  4. Generalize ri_RootToPartitionMap to use for non-partition children

  5. Add wait_for_subscription_sync for TAP tests.

  6. Logical replication