Re: partitioned tables referenced by FKs

Hi Alvaro,

On 3/18/19 6:02 PM, Alvaro Herrera wrote:
> I spent a few hours studying this and my conclusion is the opposite of
> yours: we should make addFkRecurseReferencing the recursive one, and
> CloneFkReferencing a non-recursive caller of that. So we end up with
> both addFkRecurseReferenced and addFkRecurseReferencing as recursive
> routines, and CloneFkReferenced and CloneFkReferencing being
> non-recursive callers of those. With this structure change there is one
> more call to CreateConstraintEntry than before, and now there are two
> calls of tryAttachPartitionForeignKey instead of one; I think with this
> new structure things are much simpler. I also changed
> CloneForeignKeyConstraints's API: instead of returning a list of cloned
> constraint giving its caller the responsibility of adding FK checks to
> phase 3, we now give CloneForeignKeyConstraints the 'wqueue' list, so
> that it can add the FK checks itself. It seems much cleaner this way.
>

Using

-- ddl.sql --
CREATE TABLE t1 (i1 INT PRIMARY KEY, i2 INT NOT NULL) PARTITION BY HASH
(i1);
CREATE TABLE t2 (i1 INT PRIMARY KEY, i2 INT NOT NULL) PARTITION BY HASH
(i1);

\o /dev/null
SELECT 'CREATE TABLE t1_p' || x::text || ' PARTITION OF t1
FOR VALUES WITH (MODULUS 64, REMAINDER ' || x::text || ');'
from generate_series(0,63) x;
\gexec
\o

\o /dev/null
SELECT 'CREATE TABLE t2_p' || x::text || ' PARTITION OF t2
FOR VALUES WITH (MODULUS 64, REMAINDER ' || x::text || ');'
from generate_series(0,63) x;
\gexec
\o

ALTER TABLE t1 ADD CONSTRAINT fk_t1_i2_t2_i1 FOREIGN KEY (i2) REFERENCES
t2(i1);

ANALYZE;

with

-- select.sql --
\set a random(1, 10)
SELECT t1.i1 AS t1i1, t1.i2 AS t1i2, t2.i1 AS t2i1, t2.i2 AS t2i2 FROM
t1, t2 WHERE t1.i1 = :a;

running

pgbench -M prepared -f select.sql ....

I'm seeing 82.64% spent in GetCachedPlan(). plan_cache_mode is auto.

Best regards,
Jesper