Horton

Thread

Commits

  1. Avoid creating duplicate cached plans for inherited FK constraints.

  1. Huge memory consumption on partitioned table with FKs

    Tatsuro Yamada <tatsuro.yamada.tf@nttcom.co.jp> — 2020-11-24T07:46:28Z

    Hi Hackers,

My company (NTT Comware) and NTT OSS Center did verification of
partitioned table on PG14dev, and we faced a problem that consumed
huge memory when we created a Foreign key constraint on a partitioned
table including 500 partitioning tables and inserted some data.

We investigated it a little to use the "pg_backend_memory_contextes"
command and realized a "CachedPlan" of backends increased dramatically.
See below:

Without FKs
==============================
CachedPlan 0kB

With FKs (the problem is here)
==============================
CachedPlan 710MB


Please find the attached file to reproduce the problem.

We know two things as following:
   - Each backend uses the size of CachedPlan
   - The more increasing partitioning tables, the more CachedPlan
     consuming

If there are many backends, it consumes about the size of CachedPlan x
the number of backends. It may occur a shortage of memory and OOM killer.
We think the affected version are PG12 or later. I believe it would be
better to fix the problem. Any thoughts?

Regards,
Tatsuro Yamada

  2. Re: Huge memory consumption on partitioned table with FKs

    Keisuke Kuroda <keisuke.kuroda.3862@gmail.com> — 2020-11-26T00:59:28Z

    Hi Hackers,

Analyzed the problem and created a patch to resolve it.

# Problem 1

When you create a foreign key to a partitioned table, referential
integrity function is created for the number of partitions.
Internally, SPI_prepare() creates a plan and SPI_keepplan()
caches the plan.

The more partitions in the referencing table, the more plans will
be cached.

# Problem 2

When referenced table is partitioned table, the larger the number
of partitions, the larger the plan size to be cached.

The actual plan processed is simple and small if pruning is
enabled. However, the cached plan will include all partition
information.

The more partitions in the referenced table, the larger the plan
size to be cached.

# Idea for solution

Constraints with the same pg_constraint.parentid can be combined
into one plan with the same comparentid if we can guarantee that
all their contents are the same.

Problem 1 can be solved
and significant memory bloat can be avoided.
CachedPlan: 710MB -> 1466kB

Solving Problem 2 could also reduce memory,
but I don't have a good idea.

Currently, DISCARD ALL does not discard CachedPlan by SPI as in
this case. It may be possible to modify DISCARD ALL to discard
CachedPlan and run it periodically. However, we think the burden
on the user is high.

# result with patch(PG14 HEAD(e522024b) + patch)

       name       |  bytes  | pg_size_pretty
------------------+---------+----------------
 CachedPlanQuery  |   12912 | 13 kB
 CachedPlanSource |   17448 | 17 kB
 CachedPlan       | 1501192 | 1466 kB

CachedPlan * 1 Record

postgres=# SELECT count(*) FROM pg_backend_memory_contexts WHERE name
= 'CachedPlan' AND ident LIKE 'SELECT 1 FROM%';
 count
-------
     1

postgres=# SELECT * FROM pg_backend_memory_contexts WHERE name =
'CachedPlan' AND ident LIKE 'SELECT 1 FROM%';
-[ RECORD 1 ]-+--------------------------------------------------------------------------------------
name          | CachedPlan
ident         | SELECT 1 FROM "public"."ps" x WHERE "c1"
OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x
parent        | CacheMemoryContext
level         | 2
total_bytes   | 2101248
total_nblocks | 12
free_bytes    | 613256
free_chunks   | 1
used_bytes    | 1487992
(1 record)

# result without patch(PG14 HEAD(e522024b))

       name       |   bytes   | pg_size_pretty
------------------+-----------+----------------
 CachedPlanQuery  |   1326280 | 1295 kB
 CachedPlanSource |   1474528 | 1440 kB
 CachedPlan       | 744009200 | 710 MB

CachedPlan * 500 Records

postgres=# SELECT count(*) FROM pg_backend_memory_contexts WHERE name
= 'CachedPlan' AND ident LIKE 'SELECT 1 FROM%';
 count
-------
   500

SELECT * FROM pg_backend_memory_contexts WHERE name = 'CachedPlan' AND
ident LIKE 'SELECT 1 FROM%';
name          | CachedPlan
ident         | SELECT 1 FROM "public"."ps" x WHERE "c1"
OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x
parent        | CacheMemoryContext
level         | 2
total_bytes   | 2101248
total_nblocks | 12
free_bytes    | 613256
free_chunks   | 1
used_bytes    | 1487992
...(500 same records)

Best Regards,
-- 
Keisuke Kuroda
NTT Software Innovation Center
keisuke.kuroda.3862@gmail.com

  3. Re: Huge memory consumption on partitioned table with FKs

    Kyotaro Horiguchi <horikyota.ntt@gmail.com> — 2020-11-26T03:18:18Z

    At Thu, 26 Nov 2020 09:59:28 +0900, Keisuke Kuroda <keisuke.kuroda.3862@gmail.com> wrote in 
> Hi Hackers,
> 
> Analyzed the problem and created a patch to resolve it.
> 
> # Problem 1
> 
> When you create a foreign key to a partitioned table, referential
> integrity function is created for the number of partitions.
> Internally, SPI_prepare() creates a plan and SPI_keepplan()
> caches the plan.
> 
> The more partitions in the referencing table, the more plans will
> be cached.
> 
> # Problem 2
> 
> When referenced table is partitioned table, the larger the number
> of partitions, the larger the plan size to be cached.
> 
> The actual plan processed is simple and small if pruning is
> enabled. However, the cached plan will include all partition
> information.
> 
> The more partitions in the referenced table, the larger the plan
> size to be cached.
> 
> # Idea for solution
> 
> Constraints with the same pg_constraint.parentid can be combined
> into one plan with the same comparentid if we can guarantee that
> all their contents are the same.

The memory reduction this patch gives seems quite high with a small
footprint.

This shares RI_ConstraintInfo cache by constraints that shares the
same parent constraints. But you forgot that the cache contains some
members that can differ among partitions.

Consider the case of attaching a partition that have experienced a
column deletion.

create table t (a int primary key);
create table p (a int, r int references t(a)) partition by range(a);
create table c1 partition of p for values from (0) to (10);
create table c2 (a int, r int);
alter table c2 drop column r;
alter table c2 add column r int;
alter table p attach partition c2 for values from (10) to (20);

In that case riinfo->fk_attnums has different values from other
partitions.

=# select oid, conrelid::regclass, confrelid::regclass, conparentid, conname, conkey from pg_constraint where confrelid = 't'::regclass;

  oid  | conrelid | confrelid | conparentid | conname  | conkey 
-------+----------+-----------+-------------+----------+--------
 16620 | p        | t         |           0 | p_r_fkey | {2}
 16626 | c1       | t         |       16620 | p_r_fkey | {2}
 16632 | c2       | t         |       16620 | p_r_fkey | {3}
(3 rows)

conkey is copied onto riinfo->fk_attnums.

> Problem 1 can be solved
> and significant memory bloat can be avoided.
> CachedPlan: 710MB -> 1466kB
> 
> Solving Problem 2 could also reduce memory,
> but I don't have a good idea.
> 
> Currently, DISCARD ALL does not discard CachedPlan by SPI as in
> this case. It may be possible to modify DISCARD ALL to discard
> CachedPlan and run it periodically. However, we think the burden
> on the user is high.

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

  4. Re: Huge memory consumption on partitioned table with FKs

    Alvaro Herrera <alvherre@alvh.no-ip.org> — 2020-12-01T00:03:45Z

    On 2020-Nov-26, Kyotaro Horiguchi wrote:

> This shares RI_ConstraintInfo cache by constraints that shares the
> same parent constraints. But you forgot that the cache contains some
> members that can differ among partitions.
> 
> Consider the case of attaching a partition that have experienced a
> column deletion.

I think this can be solved easily in the patch, by having
ri_BuildQueryKey() compare the parent's fk_attnums to the parent; if
they are equal then use the parent's constaint_id, otherwise use the
child constraint.  That way, the cache entry is reused in the common
case where they are identical.

I would embed all this knowledge in ri_BuildQueryKey though, without
adding the new function ri_GetParentConstOid.  I don't think that
function meaningful abstraction value, and instead it would make what I
suggest more difficult.

  5. Re: Huge memory consumption on partitioned table with FKs

    Kyotaro Horiguchi <horikyota.ntt@gmail.com> — 2020-12-01T00:40:39Z

    At Mon, 30 Nov 2020 21:03:45 -0300, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote in 
> On 2020-Nov-26, Kyotaro Horiguchi wrote:
> 
> > This shares RI_ConstraintInfo cache by constraints that shares the
> > same parent constraints. But you forgot that the cache contains some
> > members that can differ among partitions.
> > 
> > Consider the case of attaching a partition that have experienced a
> > column deletion.
> 
> I think this can be solved easily in the patch, by having
> ri_BuildQueryKey() compare the parent's fk_attnums to the parent; if
> they are equal then use the parent's constaint_id, otherwise use the
> child constraint.  That way, the cache entry is reused in the common
> case where they are identical.

*I* think it's the direction.  After an off-list discussion, we
 confirmed that even in that case the patch works as is because
 fk_attnum (or contuple.conkey) always stores key attnums compatible
 to the topmost parent when conparent has a valid value (assuming the
 current usage of fk_attnum), but I still feel uneasy to rely on that
 unclear behavior.

> I would embed all this knowledge in ri_BuildQueryKey though, without
> adding the new function ri_GetParentConstOid.  I don't think that
> function meaningful abstraction value, and instead it would make what I
> suggest more difficult.

It seems to me reasonable.

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

  6. Re: Huge memory consumption on partitioned table with FKs

    Corey Huinker <corey.huinker@gmail.com> — 2020-12-01T02:38:05Z

    >
> I think this can be solved easily in the patch, by having
> ri_BuildQueryKey() compare the parent's fk_attnums to the parent; if
> they are equal then use the parent's constaint_id, otherwise use the
> child constraint.  That way, the cache entry is reused in the common
> case where they are identical.
>

Somewhat of a detour, but in reviewing the patch for Statement-Level RI
checks, Andres and I observed that SPI made for a large portion of the RI
overhead.

Given that we're already looking at these checks, I was wondering if this
might be the time to consider implementing these checks by directly
scanning the constraint index.

  7. Re: Huge memory consumption on partitioned table with FKs

    Tom Lane <tgl@sss.pgh.pa.us> — 2020-12-01T02:48:52Z

    Corey Huinker <corey.huinker@gmail.com> writes:
> Given that we're already looking at these checks, I was wondering if this
> might be the time to consider implementing these checks by directly
> scanning the constraint index.

Yeah, maybe.  Certainly ri_triggers is putting a huge amount of effort
into working around the SPI/parser/planner layer, to not a lot of gain.

However, it's not clear to me that that line of thought will work well
for the statement-level-trigger approach.  In that case you might be
dealing with enough tuples to make a different plan advisable.

			regards, tom lane

  8. Re: Huge memory consumption on partitioned table with FKs

    Corey Huinker <corey.huinker@gmail.com> — 2020-12-01T03:25:30Z

    On Mon, Nov 30, 2020 at 9:48 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Corey Huinker <corey.huinker@gmail.com> writes:
> > Given that we're already looking at these checks, I was wondering if this
> > might be the time to consider implementing these checks by directly
> > scanning the constraint index.
>
> Yeah, maybe.  Certainly ri_triggers is putting a huge amount of effort
> into working around the SPI/parser/planner layer, to not a lot of gain.
>
> However, it's not clear to me that that line of thought will work well
> for the statement-level-trigger approach.  In that case you might be
> dealing with enough tuples to make a different plan advisable.
>
>                         regards, tom lane
>

Bypassing SPI would probably mean that we stay with row level triggers, and
the cached query plan would go away, perhaps replaced by an
already-looked-up-this-tuple hash sorta like what the cached nested loops
effort is doing.

I've been meaning to give this a try when I got some spare time. This may
inspire me to try again.

  9. Re: Huge memory consumption on partitioned table with FKs

    Amit Langote <amitlangote09@gmail.com> — 2020-12-02T13:02:50Z

    Hello,

On Tue, Dec 1, 2020 at 9:40 AM Kyotaro Horiguchi
<horikyota.ntt@gmail.com> wrote:
>
> At Mon, 30 Nov 2020 21:03:45 -0300, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote in
> > On 2020-Nov-26, Kyotaro Horiguchi wrote:
> >
> > > This shares RI_ConstraintInfo cache by constraints that shares the
> > > same parent constraints. But you forgot that the cache contains some
> > > members that can differ among partitions.
> > >
> > > Consider the case of attaching a partition that have experienced a
> > > column deletion.
> >
> > I think this can be solved easily in the patch, by having
> > ri_BuildQueryKey() compare the parent's fk_attnums to the parent; if
> > they are equal then use the parent's constaint_id, otherwise use the
> > child constraint.  That way, the cache entry is reused in the common
> > case where they are identical.
>
> *I* think it's the direction.  After an off-list discussion, we
>  confirmed that even in that case the patch works as is because
>  fk_attnum (or contuple.conkey) always stores key attnums compatible
>  to the topmost parent when conparent has a valid value (assuming the
>  current usage of fk_attnum), but I still feel uneasy to rely on that
>  unclear behavior.

Hmm, I don't see what the problem is here, because it's not
RI_ConstraintInfo that is being shared among partitions of the same
parent, but the RI query (and the SPIPlanPtr for it) that is issued
through SPI.  The query (and the plan) turns out to be the same no
matter what partition's RI_ConstraintInfo is first used to generate
it.  What am I missing?

BTW, one problem with Kuroda-san's patch is that it's using
conparentid as the shared key, which can still lead to multiple
queries being generated when using multi-level partitioning, because
there would be many (intermediate) parent constraints in that case.
We should really be using the "root" constraint OID as the key,
because there would only be one root from which all constraints in a
given partition hierarchy would've originated.  Actually, I had
written a patch a few months back to do exactly that, a polished
version of which I've attached with this email.  Please take a look.

--
Amit Langote
EDB: http://www.enterprisedb.com

  10. Re: Huge memory consumption on partitioned table with FKs

    Simon Riggs <simon@2ndquadrant.com> — 2020-12-02T13:46:04Z

    On Tue, 1 Dec 2020 at 00:03, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
> cache entry is reused in the common case where they are identical.

Does a similar situation exist for partition statistics accessed
during planning? Or planning itself?

It would be useful to avoid repeated access to similar statistics and
repeated planning of sub-plans for similar partitions.

-- 
Simon Riggs                http://www.EnterpriseDB.com/

  11. Re: Huge memory consumption on partitioned table with FKs

    Kyotaro Horiguchi <horikyota.ntt@gmail.com> — 2020-12-03T01:14:55Z

    At Wed, 2 Dec 2020 22:02:50 +0900, Amit Langote <amitlangote09@gmail.com> wrote in 
> Hello,
> 
> On Tue, Dec 1, 2020 at 9:40 AM Kyotaro Horiguchi
> <horikyota.ntt@gmail.com> wrote:
> >
> > At Mon, 30 Nov 2020 21:03:45 -0300, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote in
> > > On 2020-Nov-26, Kyotaro Horiguchi wrote:
> > >
> > > > This shares RI_ConstraintInfo cache by constraints that shares the
> > > > same parent constraints. But you forgot that the cache contains some
> > > > members that can differ among partitions.
> > > >
> > > > Consider the case of attaching a partition that have experienced a
> > > > column deletion.
> > >
> > > I think this can be solved easily in the patch, by having
> > > ri_BuildQueryKey() compare the parent's fk_attnums to the parent; if
> > > they are equal then use the parent's constaint_id, otherwise use the
> > > child constraint.  That way, the cache entry is reused in the common
> > > case where they are identical.
> >
> > *I* think it's the direction.  After an off-list discussion, we
> >  confirmed that even in that case the patch works as is because
> >  fk_attnum (or contuple.conkey) always stores key attnums compatible
> >  to the topmost parent when conparent has a valid value (assuming the
> >  current usage of fk_attnum), but I still feel uneasy to rely on that
> >  unclear behavior.
> 
> Hmm, I don't see what the problem is here, because it's not
> RI_ConstraintInfo that is being shared among partitions of the same
> parent, but the RI query (and the SPIPlanPtr for it) that is issued
> through SPI.  The query (and the plan) turns out to be the same no
> matter what partition's RI_ConstraintInfo is first used to generate
> it.  What am I missing?

I don't think you're missing something. As I (tried to..) mentioned in
another branch of this thread, you're right. On the otherhand
fk_attnums is actually used to generate the query, thus *issue*
potentially affects the query.  Although that might be paranoic, that
possibility is eliminated by checking the congruency(?) of fk_attnums
(or other members).  We might even be able to share a riinfo among
such children.

> BTW, one problem with Kuroda-san's patch is that it's using
> conparentid as the shared key, which can still lead to multiple
> queries being generated when using multi-level partitioning, because
> there would be many (intermediate) parent constraints in that case.
> We should really be using the "root" constraint OID as the key,
> because there would only be one root from which all constraints in a
> given partition hierarchy would've originated.  Actually, I had
> written a patch a few months back to do exactly that, a polished
> version of which I've attached with this email.  Please take a look.

I don't like that the function self-recurses but
ri_GetParentConstrOid() actually climbs up to the root constraint, so
the patch covers the multilevel partitioning cases.

About your patch, it calculates the root constrid at the time an
riinfo is created, but when the root-partition is further attached to
another partitioned-table after the riinfo creation,
constraint_root_id gets stale.  Of course that dones't matter
practically, though.

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

  12. Re: Huge memory consumption on partitioned table with FKs

    Keisuke Kuroda <keisuke.kuroda.3862@gmail.com> — 2020-12-03T01:29:35Z

    Hi Hackers,

>> I would embed all this knowledge in ri_BuildQueryKey though, without
>> adding the new function ri_GetParentConstOid.  I don't think that
>> function meaningful abstraction value, and instead it would make what I
>> suggest more difficult.

> It seems to me reasonable.

Indeed, I tried to get the conparentid with ri_BuildQueryKey.
(v2_reduce_ri_SPI-plan-hash.patch)

> Somewhat of a detour, but in reviewing the patch for
> Statement-Level RI checks, Andres and I observed that SPI
> made for a large portion of the RI overhead.

If this can be resolved by reviewing the Statement-Level RI checks,
then I think it would be better.

> BTW, one problem with Kuroda-san's patch is that it's using
> conparentid as the shared key, which can still lead to multiple
> queries being generated when using multi-level partitioning, because
> there would be many (intermediate) parent constraints in that case.

Horiguchi-san also mentiond,
In my patch, in ri_GetParentConstOid, iterate on getting
the constraint OID until comparentid == InvalidOid.
This should allow to get the "root constraint OID".

However, the names "comparentid" and "ri_GetParentConstOid"
are confusing. We should use names like "constraint_root_id",
as in Amit-san's patch.

Best Regards,
-- 
Keisuke Kuroda
NTT Software Innovation Center
keisuke.kuroda.3862@gmail.com

  13. Re: Huge memory consumption on partitioned table with FKs

    Amit Langote <amitlangote09@gmail.com> — 2020-12-03T03:27:53Z

    Hello,

On Thu, Dec 3, 2020 at 10:15 AM Kyotaro Horiguchi
<horikyota.ntt@gmail.com> wrote:
> At Wed, 2 Dec 2020 22:02:50 +0900, Amit Langote <amitlangote09@gmail.com> wrote in
> > Hello,
> >
> > On Tue, Dec 1, 2020 at 9:40 AM Kyotaro Horiguchi
> > <horikyota.ntt@gmail.com> wrote:
> > >
> > > At Mon, 30 Nov 2020 21:03:45 -0300, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote in
> > > > On 2020-Nov-26, Kyotaro Horiguchi wrote:
> > > >
> > > > > This shares RI_ConstraintInfo cache by constraints that shares the
> > > > > same parent constraints. But you forgot that the cache contains some
> > > > > members that can differ among partitions.
> > > > >
> > > > > Consider the case of attaching a partition that have experienced a
> > > > > column deletion.
> > > >
> > > > I think this can be solved easily in the patch, by having
> > > > ri_BuildQueryKey() compare the parent's fk_attnums to the parent; if
> > > > they are equal then use the parent's constaint_id, otherwise use the
> > > > child constraint.  That way, the cache entry is reused in the common
> > > > case where they are identical.
> > >
> > > *I* think it's the direction.  After an off-list discussion, we
> > >  confirmed that even in that case the patch works as is because
> > >  fk_attnum (or contuple.conkey) always stores key attnums compatible
> > >  to the topmost parent when conparent has a valid value (assuming the
> > >  current usage of fk_attnum), but I still feel uneasy to rely on that
> > >  unclear behavior.
> >
> > Hmm, I don't see what the problem is here, because it's not
> > RI_ConstraintInfo that is being shared among partitions of the same
> > parent, but the RI query (and the SPIPlanPtr for it) that is issued
> > through SPI.  The query (and the plan) turns out to be the same no
> > matter what partition's RI_ConstraintInfo is first used to generate
> > it.  What am I missing?
>
> I don't think you're missing something. As I (tried to..) mentioned in
> another branch of this thread, you're right. On the otherhand
> fk_attnums is actually used to generate the query, thus *issue*
> potentially affects the query.  Although that might be paranoic, that
> possibility is eliminated by checking the congruency(?) of fk_attnums
> (or other members).  We might even be able to share a riinfo among
> such children.

Just to be sure I've not misunderstood you, let me mention why I think
the queries used by different partitions all turn out to be the same
despite attribute number differences among partitions.  The places
that uses fk_attnums when generating a query are these among others:

Oid fk_type = RIAttType(fk_rel, riinfo->fk_attnums[i]);
...
Oid fk_coll = RIAttCollation(fk_rel, riinfo->fk_attnums[i]);
...
            quoteOneName(attname,
                         RIAttName(fk_rel, riinfo->fk_attnums[i]));

For the queries on the referencing side ("check" side),
type/collation/attribute name determined using the above are going to
be the same for all partitions in a given tree irrespective of the
attribute number, because they're logically the same column.  On the
referenced side ("restrict", "cascade", "set" side), as you already
mentioned, fk_attnums refers to the top parent table of the
referencing side, so no possibility of they being different in the
various referenced partitions' RI_ConstraintInfos.

On the topic of how we'd be able to share even the RI_ConstraintInfos
among partitions, that would indeed look a bit more elaborate than the
patch we have right now.

> > BTW, one problem with Kuroda-san's patch is that it's using
> > conparentid as the shared key, which can still lead to multiple
> > queries being generated when using multi-level partitioning, because
> > there would be many (intermediate) parent constraints in that case.
> > We should really be using the "root" constraint OID as the key,
> > because there would only be one root from which all constraints in a
> > given partition hierarchy would've originated.  Actually, I had
> > written a patch a few months back to do exactly that, a polished
> > version of which I've attached with this email.  Please take a look.
>
> I don't like that the function self-recurses but
> ri_GetParentConstrOid() actually climbs up to the root constraint, so
> the patch covers the multilevel partitioning cases.

Sorry, I got too easily distracted by the name Kuroda-san chose for
the field in RI_ConstraintInfo and the function.

> About your patch, it calculates the root constrid at the time an
> riinfo is created, but when the root-partition is further attached to
> another partitioned-table after the riinfo creation,
> constraint_root_id gets stale.  Of course that dones't matter
> practically, though.

Maybe we could also store the hash value of the root constraint OID as
rootHashValue and check for that one too in
InvalidateConstraintCacheCallBack().  That would take care of this
unless I'm missing something.

-- 
Amit Langote
EDB: http://www.enterprisedb.com

  14. Re: Huge memory consumption on partitioned table with FKs

    Kyotaro Horiguchi <horikyota.ntt@gmail.com> — 2020-12-03T05:28:57Z

    At Thu, 3 Dec 2020 12:27:53 +0900, Amit Langote <amitlangote09@gmail.com> wrote in 
> Hello,
> 
> On Thu, Dec 3, 2020 at 10:15 AM Kyotaro Horiguchi
> <horikyota.ntt@gmail.com> wrote:
> > At Wed, 2 Dec 2020 22:02:50 +0900, Amit Langote <amitlangote09@gmail.com> wrote in
> > > Hmm, I don't see what the problem is here, because it's not
> > > RI_ConstraintInfo that is being shared among partitions of the same
> > > parent, but the RI query (and the SPIPlanPtr for it) that is issued
> > > through SPI.  The query (and the plan) turns out to be the same no
> > > matter what partition's RI_ConstraintInfo is first used to generate
> > > it.  What am I missing?
> >
> > I don't think you're missing something. As I (tried to..) mentioned in
> > another branch of this thread, you're right. On the otherhand
> > fk_attnums is actually used to generate the query, thus *issue*
> > potentially affects the query.  Although that might be paranoic, that
> > possibility is eliminated by checking the congruency(?) of fk_attnums
> > (or other members).  We might even be able to share a riinfo among
> > such children.
> 
> Just to be sure I've not misunderstood you, let me mention why I think
> the queries used by different partitions all turn out to be the same
> despite attribute number differences among partitions.  The places
> that uses fk_attnums when generating a query are these among others:
> 
> Oid fk_type = RIAttType(fk_rel, riinfo->fk_attnums[i]);
> ...
> Oid fk_coll = RIAttCollation(fk_rel, riinfo->fk_attnums[i]);
> ...
>             quoteOneName(attname,
>                          RIAttName(fk_rel, riinfo->fk_attnums[i]));
> 
> For the queries on the referencing side ("check" side),
> type/collation/attribute name determined using the above are going to
> be the same for all partitions in a given tree irrespective of the
> attribute number, because they're logically the same column.  On the

Yes, I know that, which is what I meant by "practically" or
"actually", but it is not explicitly defined AFAICS.

Thus that would be no longer an issue if we explicitly define that
"When conpraentid stores a valid value, each element of fk_attnums
points to logically the same attribute with the RI_ConstraintInfo for
the parent constraint."  Or I'd be happy if we have such a comment
there instead.

> referenced side ("restrict", "cascade", "set" side), as you already
> mentioned, fk_attnums refers to the top parent table of the
> referencing side, so no possibility of they being different in the
> various referenced partitions' RI_ConstraintInfos.

Right. (I'm not sure I have mention that here, though:p)A

> On the topic of how we'd be able to share even the RI_ConstraintInfos
> among partitions, that would indeed look a bit more elaborate than the
> patch we have right now.

Maybe just letting the hash entry for the child riinfo point to the
parent riinfo if all members (other than constraint_id, of course)
share the exactly the same values.  No need to count references since
we don't going to remove riinfos.

> > > BTW, one problem with Kuroda-san's patch is that it's using
> > > conparentid as the shared key, which can still lead to multiple
> > > queries being generated when using multi-level partitioning, because
> > > there would be many (intermediate) parent constraints in that case.
> > > We should really be using the "root" constraint OID as the key,
> > > because there would only be one root from which all constraints in a
> > > given partition hierarchy would've originated.  Actually, I had
> > > written a patch a few months back to do exactly that, a polished
> > > version of which I've attached with this email.  Please take a look.
> >
> > I don't like that the function self-recurses but
> > ri_GetParentConstrOid() actually climbs up to the root constraint, so
> > the patch covers the multilevel partitioning cases.
> 
> Sorry, I got too easily distracted by the name Kuroda-san chose for
> the field in RI_ConstraintInfo and the function.

I thought the same at the first look to the function.

> > About your patch, it calculates the root constrid at the time an
> > riinfo is created, but when the root-partition is further attached to
> > another partitioned-table after the riinfo creation,
> > constraint_root_id gets stale.  Of course that dones't matter
> > practically, though.
> 
> Maybe we could also store the hash value of the root constraint OID as
> rootHashValue and check for that one too in
> InvalidateConstraintCacheCallBack().  That would take care of this
> unless I'm missing something.

Seems to be sound.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

  15. Re: Huge memory consumption on partitioned table with FKs

    Amit Langote <amitlangote09@gmail.com> — 2020-12-03T07:41:45Z

    On Thu, Dec 3, 2020 at 2:29 PM Kyotaro Horiguchi
<horikyota.ntt@gmail.com> wrote:
> At Thu, 3 Dec 2020 12:27:53 +0900, Amit Langote <amitlangote09@gmail.com> wrote in
> > On Thu, Dec 3, 2020 at 10:15 AM Kyotaro Horiguchi
> > <horikyota.ntt@gmail.com> wrote:
> > > I don't think you're missing something. As I (tried to..) mentioned in
> > > another branch of this thread, you're right. On the otherhand
> > > fk_attnums is actually used to generate the query, thus *issue*
> > > potentially affects the query.  Although that might be paranoic, that
> > > possibility is eliminated by checking the congruency(?) of fk_attnums
> > > (or other members).  We might even be able to share a riinfo among
> > > such children.
> >
> > Just to be sure I've not misunderstood you, let me mention why I think
> > the queries used by different partitions all turn out to be the same
> > despite attribute number differences among partitions.  The places
> > that uses fk_attnums when generating a query are these among others:
> >
> > Oid fk_type = RIAttType(fk_rel, riinfo->fk_attnums[i]);
> > ...
> > Oid fk_coll = RIAttCollation(fk_rel, riinfo->fk_attnums[i]);
> > ...
> >             quoteOneName(attname,
> >                          RIAttName(fk_rel, riinfo->fk_attnums[i]));
> >
> > For the queries on the referencing side ("check" side),
> > type/collation/attribute name determined using the above are going to
> > be the same for all partitions in a given tree irrespective of the
> > attribute number, because they're logically the same column.  On the
>
> Yes, I know that, which is what I meant by "practically" or
> "actually", but it is not explicitly defined AFAICS.

Well, I think it's great that we don't have to worry *in this part of
the code* about partition's fk_attnums not being congruent with the
root parent's, because ensuring that is the responsibility of the
other parts of the system such as DDL.  If we have any problems in
this area, they should be dealt with by ensuring that there are no
bugs in those other parts.

> Thus that would be no longer an issue if we explicitly define that
> "When conpraentid stores a valid value, each element of fk_attnums
> points to logically the same attribute with the RI_ConstraintInfo for
> the parent constraint."  Or I'd be happy if we have such a comment
> there instead.

I saw a comment in Kuroda-san's v2 patch that is perhaps meant to
address this point, but the placement needs to be reconsidered:

@@ -366,6 +368,14 @@ RI_FKey_check(TriggerData *trigdata)
        querysep = "WHERE";
        for (int i = 0; i < riinfo->nkeys; i++)
        {
+
+           /*
+           * We share the same plan among all relations in a partition
+           * hierarchy.  The plan is guaranteed to be compatible since all of
+           * the member relations are guaranteed to have the equivalent set
+           * of foreign keys in fk_attnums[].
+           */
+
            Oid         pk_type = RIAttType(pk_rel, riinfo->pk_attnums[i]);
            Oid         fk_type = RIAttType(fk_rel, riinfo->fk_attnums[i]);

A more appropriate place for this kind of comment would be where
fk_attnums is defined or in ri_BuildQueryKey() that is shared by
different RI query issuing functions.

> > referenced side ("restrict", "cascade", "set" side), as you already
> > mentioned, fk_attnums refers to the top parent table of the
> > referencing side, so no possibility of they being different in the
> > various referenced partitions' RI_ConstraintInfos.
>
> Right. (I'm not sure I have mention that here, though:p)A

Maybe I misread but I think you did in your email dated Dec 1 where you said:

"After an off-list discussion, we confirmed that even in that case the
patch works as is because fk_attnum (or contuple.conkey) always stores
key attnums compatible to the topmost parent when conparent has a
valid value (assuming the current usage of fk_attnum), but I still
feel uneasy to rely on that unclear behavior."

> > On the topic of how we'd be able to share even the RI_ConstraintInfos
> > among partitions, that would indeed look a bit more elaborate than the
> > patch we have right now.
>
> Maybe just letting the hash entry for the child riinfo point to the
> parent riinfo if all members (other than constraint_id, of course)
> share the exactly the same values.  No need to count references since
> we don't going to remove riinfos.

Ah, something maybe worth trying.  Although the memory we'd save by
sharing the RI_ConstraintInfos would not add that much to the savings
we're having by sharing the plan, because it's the plans that are a
memory hog AFAIK.

> > > About your patch, it calculates the root constrid at the time an
> > > riinfo is created, but when the root-partition is further attached to
> > > another partitioned-table after the riinfo creation,
> > > constraint_root_id gets stale.  Of course that dones't matter
> > > practically, though.
> >
> > Maybe we could also store the hash value of the root constraint OID as
> > rootHashValue and check for that one too in
> > InvalidateConstraintCacheCallBack().  That would take care of this
> > unless I'm missing something.
>
> Seems to be sound.

Okay, thanks.

I have attached a patch in which I've tried to merge the ideas from
both my patch and Kuroda-san's.  I liked that his patch added
conparentid to RI_ConstraintInfo because that saves a needless
syscache lookup for constraints that don't have a parent.  I've kept
my idea to compute the root constraint id only once in
ri_LoadConstraint(), not on every invocation of ri_BuildQueryKey().
Kuroda-san, anything you'd like to add to that?

-- 
Amit Langote
EDB: http://www.enterprisedb.com

  16. Re: Huge memory consumption on partitioned table with FKs

    Kyotaro Horiguchi <horikyota.ntt@gmail.com> — 2020-12-03T08:13:16Z

    At Thu, 3 Dec 2020 16:41:45 +0900, Amit Langote <amitlangote09@gmail.com> wrote in 
> On Thu, Dec 3, 2020 at 2:29 PM Kyotaro Horiguchi
> <horikyota.ntt@gmail.com> wrote:
> > At Thu, 3 Dec 2020 12:27:53 +0900, Amit Langote <amitlangote09@gmail.com> wrote in
> > > On Thu, Dec 3, 2020 at 10:15 AM Kyotaro Horiguchi
> > > <horikyota.ntt@gmail.com> wrote:
> > > For the queries on the referencing side ("check" side),
> > > type/collation/attribute name determined using the above are going to
> > > be the same for all partitions in a given tree irrespective of the
> > > attribute number, because they're logically the same column.  On the
> >
> > Yes, I know that, which is what I meant by "practically" or
> > "actually", but it is not explicitly defined AFAICS.
> 
> Well, I think it's great that we don't have to worry *in this part of
> the code* about partition's fk_attnums not being congruent with the
> root parent's, because ensuring that is the responsibility of the
> other parts of the system such as DDL.  If we have any problems in
> this area, they should be dealt with by ensuring that there are no
> bugs in those other parts.

Agreed.

> > Thus that would be no longer an issue if we explicitly define that
> > "When conpraentid stores a valid value, each element of fk_attnums
> > points to logically the same attribute with the RI_ConstraintInfo for
> > the parent constraint."  Or I'd be happy if we have such a comment
> > there instead.
> 
> I saw a comment in Kuroda-san's v2 patch that is perhaps meant to
> address this point, but the placement needs to be reconsidered:

Ah, yes, that comes from my proposal.

> @@ -366,6 +368,14 @@ RI_FKey_check(TriggerData *trigdata)
>         querysep = "WHERE";
>         for (int i = 0; i < riinfo->nkeys; i++)
>         {
> +
> +           /*
> +           * We share the same plan among all relations in a partition
> +           * hierarchy.  The plan is guaranteed to be compatible since all of
> +           * the member relations are guaranteed to have the equivalent set
> +           * of foreign keys in fk_attnums[].
> +           */
> +
>             Oid         pk_type = RIAttType(pk_rel, riinfo->pk_attnums[i]);
>             Oid         fk_type = RIAttType(fk_rel, riinfo->fk_attnums[i]);
> 
> A more appropriate place for this kind of comment would be where
> fk_attnums is defined or in ri_BuildQueryKey() that is shared by
> different RI query issuing functions.

Yeah, I wanted more appropriate place for the comment.  That place
seems reasonable.

> > > referenced side ("restrict", "cascade", "set" side), as you already
> > > mentioned, fk_attnums refers to the top parent table of the
> > > referencing side, so no possibility of they being different in the
> > > various referenced partitions' RI_ConstraintInfos.
> >
> > Right. (I'm not sure I have mention that here, though:p)A
> 
> Maybe I misread but I think you did in your email dated Dec 1 where you said:
> 
> "After an off-list discussion, we confirmed that even in that case the
> patch works as is because fk_attnum (or contuple.conkey) always stores
> key attnums compatible to the topmost parent when conparent has a
> valid value (assuming the current usage of fk_attnum), but I still
> feel uneasy to rely on that unclear behavior."

fk_attnums *doesn't* refers to to top parent talbe of the referencing
side. it refers to attributes of the partition that is compatible with
the same element of fk_attnums of the topmost parent.  Maybe I'm
misreading.


> > > On the topic of how we'd be able to share even the RI_ConstraintInfos
> > > among partitions, that would indeed look a bit more elaborate than the
> > > patch we have right now.
> >
> > Maybe just letting the hash entry for the child riinfo point to the
> > parent riinfo if all members (other than constraint_id, of course)
> > share the exactly the same values.  No need to count references since
> > we don't going to remove riinfos.
> 
> Ah, something maybe worth trying.  Although the memory we'd save by
> sharing the RI_ConstraintInfos would not add that much to the savings
> we're having by sharing the plan, because it's the plans that are a
> memory hog AFAIK.

I agree that plans are rather large but the sharable part of the
RI_ConstraintInfos is 536 bytes, I'm not sure it is small enough
comparing to the plans.  But that has somewhat large footprint.. (See
the attached)

> > > > About your patch, it calculates the root constrid at the time an
> > > > riinfo is created, but when the root-partition is further attached to
> > > > another partitioned-table after the riinfo creation,
> > > > constraint_root_id gets stale.  Of course that dones't matter
> > > > practically, though.
> > >
> > > Maybe we could also store the hash value of the root constraint OID as
> > > rootHashValue and check for that one too in
> > > InvalidateConstraintCacheCallBack().  That would take care of this
> > > unless I'm missing something.
> >
> > Seems to be sound.
> 
> Okay, thanks.
> 
> I have attached a patch in which I've tried to merge the ideas from
> both my patch and Kuroda-san's.  I liked that his patch added
> conparentid to RI_ConstraintInfo because that saves a needless
> syscache lookup for constraints that don't have a parent.  I've kept
> my idea to compute the root constraint id only once in
> ri_LoadConstraint(), not on every invocation of ri_BuildQueryKey().
> Kuroda-san, anything you'd like to add to that?

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

  17. Re: Huge memory consumption on partitioned table with FKs

    Kyotaro Horiguchi <horikyota.ntt@gmail.com> — 2020-12-03T08:27:02Z

    At Thu, 03 Dec 2020 17:13:16 +0900 (JST), Kyotaro Horiguchi <horikyota.ntt@gmail.com> wrote in 
me> I agree that plans are rather large but the sharable part of the
me> RI_ConstraintInfos is 536 bytes, I'm not sure it is small enough
me> comparing to the plans.  But that has somewhat large footprint.. (See
me> the attached)

0001 contains a bug about query_key and get_ri_constaint_root (from
your patch) is not needed there, but the core part is 0002 so please
ignore them.

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

  18. Re: Huge memory consumption on partitioned table with FKs

    Amit Langote <amitlangote09@gmail.com> — 2020-12-03T12:40:29Z

    On Thu, Dec 3, 2020 at 5:13 PM Kyotaro Horiguchi
<horikyota.ntt@gmail.com> wrote:
> At Thu, 3 Dec 2020 16:41:45 +0900, Amit Langote <amitlangote09@gmail.com> wrote in
> > Maybe I misread but I think you did in your email dated Dec 1 where you said:
> >
> > "After an off-list discussion, we confirmed that even in that case the
> > patch works as is because fk_attnum (or contuple.conkey) always stores
> > key attnums compatible to the topmost parent when conparent has a
> > valid value (assuming the current usage of fk_attnum), but I still
> > feel uneasy to rely on that unclear behavior."
>
> fk_attnums *doesn't* refers to to top parent talbe of the referencing
> side. it refers to attributes of the partition that is compatible with
> the same element of fk_attnums of the topmost parent.  Maybe I'm
> misreading.

Yeah, no I am confused.  Reading what I wrote, it seems I implied that
the referenced (PK relation's) partitions have RI_ConstraintInfo which
makes no sense, although there indeed is one pg_constraint entry that
is defined on the FK root table for every PK partition with its OID as
confrelid, which is in addition to an entry containing the root PK
table's OID as confrelid.  I confused those PK-partition-referencing
entries as belonging to the partitions themselves.  Although in my
defence, all of those entries' conkey contains the FK root table's
attributes, so at least that much holds. :)

> > > > On the topic of how we'd be able to share even the RI_ConstraintInfos
> > > > among partitions, that would indeed look a bit more elaborate than the
> > > > patch we have right now.
> > >
> > > Maybe just letting the hash entry for the child riinfo point to the
> > > parent riinfo if all members (other than constraint_id, of course)
> > > share the exactly the same values.  No need to count references since
> > > we don't going to remove riinfos.
> >
> > Ah, something maybe worth trying.  Although the memory we'd save by
> > sharing the RI_ConstraintInfos would not add that much to the savings
> > we're having by sharing the plan, because it's the plans that are a
> > memory hog AFAIK.
>
> I agree that plans are rather large but the sharable part of the
> RI_ConstraintInfos is 536 bytes, I'm not sure it is small enough
> comparing to the plans.  But that has somewhat large footprint.. (See
> the attached)

Thanks for the patch.

-- 
Amit Langote
EDB: http://www.enterprisedb.com

  19. Re: Huge memory consumption on partitioned table with FKs

    Amit Langote <amitlangote09@gmail.com> — 2020-12-03T12:41:08Z

    On Tue, Dec 1, 2020 at 12:25 PM Corey Huinker <corey.huinker@gmail.com> wrote:
> On Mon, Nov 30, 2020 at 9:48 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Corey Huinker <corey.huinker@gmail.com> writes:
>> > Given that we're already looking at these checks, I was wondering if this
>> > might be the time to consider implementing these checks by directly
>> > scanning the constraint index.
>>
>> Yeah, maybe.  Certainly ri_triggers is putting a huge amount of effort
>> into working around the SPI/parser/planner layer, to not a lot of gain.
>>
>> However, it's not clear to me that that line of thought will work well
>> for the statement-level-trigger approach.  In that case you might be
>> dealing with enough tuples to make a different plan advisable.
>
> Bypassing SPI would probably mean that we stay with row level triggers, and the cached query plan would go away, perhaps replaced by an already-looked-up-this-tuple hash sorta like what the cached nested loops effort is doing.
>
> I've been meaning to give this a try when I got some spare time. This may inspire me to try again.

+1 for this line of work.

-- 
Amit Langote
EDB: http://www.enterprisedb.com

  20. Re: Huge memory consumption on partitioned table with FKs

    Alvaro Herrera <alvherre@alvh.no-ip.org> — 2020-12-03T13:22:47Z

    Hello

I haven't followed this thread's latest posts, but I'm unclear on the
lifetime of the new struct that's being allocated in TopMemoryContext.
At what point are those structs freed?

Also, the comment that was in RI_ConstraintInfo now appears in
RI_ConstraintParam, and the new struct (RI_ConstraintInfo) is now
undocumented.  What is the relationship between those two structs?  I
see that they have pointers to each other, but I think the relationship
should be documented more clearly.

Thanks!

  21. Re: Huge memory consumption on partitioned table with FKs

    Kyotaro Horiguchi <horikyota.ntt@gmail.com> — 2020-12-04T00:17:18Z

    At Thu, 3 Dec 2020 21:40:29 +0900, Amit Langote <amitlangote09@gmail.com> wrote in 
> On Thu, Dec 3, 2020 at 5:13 PM Kyotaro Horiguchi
> <horikyota.ntt@gmail.com> wrote:
> > At Thu, 3 Dec 2020 16:41:45 +0900, Amit Langote <amitlangote09@gmail.com> wrote in
> > > Maybe I misread but I think you did in your email dated Dec 1 where you said:
> > >
> > > "After an off-list discussion, we confirmed that even in that case the
> > > patch works as is because fk_attnum (or contuple.conkey) always stores
> > > key attnums compatible to the topmost parent when conparent has a
> > > valid value (assuming the current usage of fk_attnum), but I still
> > > feel uneasy to rely on that unclear behavior."
> >
> > fk_attnums *doesn't* refers to to top parent talbe of the referencing
> > side. it refers to attributes of the partition that is compatible with
> > the same element of fk_attnums of the topmost parent.  Maybe I'm
> > misreading.
> 
> Yeah, no I am confused.  Reading what I wrote, it seems I implied that
> the referenced (PK relation's) partitions have RI_ConstraintInfo which
> makes no sense, although there indeed is one pg_constraint entry that
> is defined on the FK root table for every PK partition with its OID as
> confrelid, which is in addition to an entry containing the root PK
> table's OID as confrelid.  I confused those PK-partition-referencing
> entries as belonging to the partitions themselves.  Although in my
> defence, all of those entries' conkey contains the FK root table's
> attributes, so at least that much holds. :)

Yes. I think that that confusion doen't hurt the correctness of the
discussion:)

> > > > > On the topic of how we'd be able to share even the RI_ConstraintInfos
> > > > > among partitions, that would indeed look a bit more elaborate than the
> > > > > patch we have right now.
> > > >
> > > > Maybe just letting the hash entry for the child riinfo point to the
> > > > parent riinfo if all members (other than constraint_id, of course)
> > > > share the exactly the same values.  No need to count references since
> > > > we don't going to remove riinfos.
> > >
> > > Ah, something maybe worth trying.  Although the memory we'd save by
> > > sharing the RI_ConstraintInfos would not add that much to the savings
> > > we're having by sharing the plan, because it's the plans that are a
> > > memory hog AFAIK.
> >
> > I agree that plans are rather large but the sharable part of the
> > RI_ConstraintInfos is 536 bytes, I'm not sure it is small enough
> > comparing to the plans.  But that has somewhat large footprint.. (See
> > the attached)
> 
> Thanks for the patch.

That's only to show how that looks like.

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

  22. Re: Huge memory consumption on partitioned table with FKs

    Keisuke Kuroda <keisuke.kuroda.3862@gmail.com> — 2020-12-04T03:00:09Z

    Hi Amit,

> I have attached a patch in which I've tried to merge the ideas from
> both my patch and Kuroda-san's.  I liked that his patch added
> conparentid to RI_ConstraintInfo because that saves a needless
> syscache lookup for constraints that don't have a parent.  I've kept
> my idea to compute the root constraint id only once in
> ri_LoadConstraint(), not on every invocation of ri_BuildQueryKey().
> Kuroda-san, anything you'd like to add to that?

Thank you for the merge! It looks good to me.
I think a fix for InvalidateConstraintCacheCallBack() is also good.

I also confirmed that the patch passed the make check-world.

Best Regards,
-- 
Keisuke Kuroda
NTT Software Innovation Center
keisuke.kuroda.3862@gmail.com

  23. Re: Huge memory consumption on partitioned table with FKs

    Kyotaro Horiguchi <horikyota.ntt@gmail.com> — 2020-12-04T03:05:22Z

    Thanks, but sorry for the confusion.

I intended just to show how it looks like if we share
RI_ConstraintInfo among partition relations.

At Thu, 3 Dec 2020 10:22:47 -0300, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote in 
> Hello
> 
> I haven't followed this thread's latest posts, but I'm unclear on the
> lifetime of the new struct that's being allocated in TopMemoryContext.
> At what point are those structs freed?

The choice of memory context is tentative and in order to shrink the
patch'es footprint. I think we don't use CurrentDynaHashCxt for the
additional struct so a context for this use is needed.

The struct is freed only when the parent struct (RI_ConstraintInfo) is
found to be able to share the child struct (RI_ConstraintParam) with
the parent constraint.  It seems like inefficient (or tending to make
"hole"s in the heap area) but I chose it just to shrink the footprint.

We could create the new RI_ConstraintInfo on stack then copy it to the
cache after we find that the RI_ConstraintInfo needs its own
RI_ConstriantParam.

> Also, the comment that was in RI_ConstraintInfo now appears in
> RI_ConstraintParam, and the new struct (RI_ConstraintInfo) is now
> undocumented.  What is the relationship between those two structs?  I
> see that they have pointers to each other, but I think the relationship
> should be documented more clearly.

I'm not sure the footprint of this patch worth doing but here is a bit
more polished version.

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

  24. Re: Huge memory consumption on partitioned table with FKs

    Kyotaro Horiguchi <horikyota.ntt@gmail.com> — 2020-12-04T05:48:22Z

    At Fri, 4 Dec 2020 12:00:09 +0900, Keisuke Kuroda <keisuke.kuroda.3862@gmail.com> wrote in 
> Hi Amit,
> 
> > I have attached a patch in which I've tried to merge the ideas from
> > both my patch and Kuroda-san's.  I liked that his patch added
> > conparentid to RI_ConstraintInfo because that saves a needless
> > syscache lookup for constraints that don't have a parent.  I've kept
> > my idea to compute the root constraint id only once in
> > ri_LoadConstraint(), not on every invocation of ri_BuildQueryKey().
> > Kuroda-san, anything you'd like to add to that?
> 
> Thank you for the merge! It looks good to me.
> I think a fix for InvalidateConstraintCacheCallBack() is also good.
> 
> I also confirmed that the patch passed the make check-world.

It's fine that constraint_rood_id overrides constraint_id, but how
about that constraint_root_id stores constraint_id if it is not a
partition?  That change makes the patch a bit simpler.

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

  25. Re: Huge memory consumption on partitioned table with FKs

    Amit Langote <amitlangote09@gmail.com> — 2020-12-04T06:32:32Z

    On Fri, Dec 4, 2020 at 2:48 PM Kyotaro Horiguchi
<horikyota.ntt@gmail.com> wrote:
> At Fri, 4 Dec 2020 12:00:09 +0900, Keisuke Kuroda <keisuke.kuroda.3862@gmail.com> wrote in
> > Hi Amit,
> >
> > > I have attached a patch in which I've tried to merge the ideas from
> > > both my patch and Kuroda-san's.  I liked that his patch added
> > > conparentid to RI_ConstraintInfo because that saves a needless
> > > syscache lookup for constraints that don't have a parent.  I've kept
> > > my idea to compute the root constraint id only once in
> > > ri_LoadConstraint(), not on every invocation of ri_BuildQueryKey().
> > > Kuroda-san, anything you'd like to add to that?
> >
> > Thank you for the merge! It looks good to me.
> > I think a fix for InvalidateConstraintCacheCallBack() is also good.
> >
> > I also confirmed that the patch passed the make check-world.
>
> It's fine that constraint_rood_id overrides constraint_id, but how
> about that constraint_root_id stores constraint_id if it is not a
> partition?  That change makes the patch a bit simpler.

My patch was like that before posting to this thread, but keeping
constraint_id and constraint_root_id separate looked better for
documenting the partitioning case as working differently from the
regular table case.  I guess a comment in ri_BuildQueryKey is enough
for that though and it's not like we're using constraint_root_id in
any other place to make matters confusing, so I changed it as you
suggest.  Updated patch attached.

-- 
Amit Langote
EDB: http://www.enterprisedb.com

  26. Re: Huge memory consumption on partitioned table with FKs

    Amit Langote <amitlangote09@gmail.com> — 2020-12-07T14:01:47Z

    On Fri, Dec 4, 2020 at 12:05 PM Kyotaro Horiguchi
<horikyota.ntt@gmail.com> wrote:
> > Also, the comment that was in RI_ConstraintInfo now appears in
> > RI_ConstraintParam, and the new struct (RI_ConstraintInfo) is now
> > undocumented.  What is the relationship between those two structs?  I
> > see that they have pointers to each other, but I think the relationship
> > should be documented more clearly.
>
> I'm not sure the footprint of this patch worth doing but here is a bit
> more polished version.

I noticed that the foreign_key test fails and it may have to do with
the fact that a partition's param info remains attached to the
parent's RI_ConstraintInfo even after it's detached from the parent
table using DETACH PARTITION.

-- 
Amit Langote
EDB: http://www.enterprisedb.com

  27. Re: Huge memory consumption on partitioned table with FKs

    Alvaro Herrera <alvherre@alvh.no-ip.org> — 2020-12-07T14:48:51Z

    On 2020-Dec-07, Amit Langote wrote:

> On Fri, Dec 4, 2020 at 12:05 PM Kyotaro Horiguchi
> <horikyota.ntt@gmail.com> wrote:
> > > Also, the comment that was in RI_ConstraintInfo now appears in
> > > RI_ConstraintParam, and the new struct (RI_ConstraintInfo) is now
> > > undocumented.  What is the relationship between those two structs?  I
> > > see that they have pointers to each other, but I think the relationship
> > > should be documented more clearly.
> >
> > I'm not sure the footprint of this patch worth doing but here is a bit
> > more polished version.
> 
> I noticed that the foreign_key test fails and it may have to do with
> the fact that a partition's param info remains attached to the
> parent's RI_ConstraintInfo even after it's detached from the parent
> table using DETACH PARTITION.

I think this bit about splitting the struct is a distraction.  Let's get
a patch that solves the bug first, and then we can discuss what further
refinements we want to do.  I think we should get your patch in
CA+HiwqEOrfN9b=f3sDmySPGc4gO-L_VMFHXLLxVmmdP34e64+w@mail.gmail.com
committed (which I have not read yet.)  Do you agree with this plan?

  28. Re: Huge memory consumption on partitioned table with FKs

    Amit Langote <amitlangote09@gmail.com> — 2020-12-07T16:16:00Z

    Hi Alvaro,

On Mon, Dec 7, 2020 at 23:48 Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:

> On 2020-Dec-07, Amit Langote wrote:
>
> > On Fri, Dec 4, 2020 at 12:05 PM Kyotaro Horiguchi
> > <horikyota.ntt@gmail.com> wrote:
> > > > Also, the comment that was in RI_ConstraintInfo now appears in
> > > > RI_ConstraintParam, and the new struct (RI_ConstraintInfo) is now
> > > > undocumented.  What is the relationship between those two structs?  I
> > > > see that they have pointers to each other, but I think the
> relationship
> > > > should be documented more clearly.
> > >
> > > I'm not sure the footprint of this patch worth doing but here is a bit
> > > more polished version.
> >
> > I noticed that the foreign_key test fails and it may have to do with
> > the fact that a partition's param info remains attached to the
> > parent's RI_ConstraintInfo even after it's detached from the parent
> > table using DETACH PARTITION.
>
> I think this bit about splitting the struct is a distraction.  Let's get
> a patch that solves the bug first, and then we can discuss what further
> refinements we want to do.  I think we should get your patch in
> CA+HiwqEOrfN9b=f3sDmySPGc4gO-L_VMFHXLLxVmmdP34e64+w@mail.gmail.com
> committed (which I have not read yet.)  Do you agree with this plan?


Yeah, I agree.

- Amit

> --
Amit Langote
EDB: http://www.enterprisedb.com

  29. Re: Huge memory consumption on partitioned table with FKs

    Kyotaro Horiguchi <horikyota.ntt@gmail.com> — 2020-12-08T03:04:04Z

    At Tue, 8 Dec 2020 01:16:00 +0900, Amit Langote <amitlangote09@gmail.com> wrote in 
> Hi Alvaro,
> 
> On Mon, Dec 7, 2020 at 23:48 Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
> 
> > On 2020-Dec-07, Amit Langote wrote:
> >
> > > On Fri, Dec 4, 2020 at 12:05 PM Kyotaro Horiguchi
> > > <horikyota.ntt@gmail.com> wrote:
> > > > > Also, the comment that was in RI_ConstraintInfo now appears in
> > > > > RI_ConstraintParam, and the new struct (RI_ConstraintInfo) is now
> > > > > undocumented.  What is the relationship between those two structs?  I
> > > > > see that they have pointers to each other, but I think the
> > relationship
> > > > > should be documented more clearly.
> > > >
> > > > I'm not sure the footprint of this patch worth doing but here is a bit
> > > > more polished version.
> > >
> > > I noticed that the foreign_key test fails and it may have to do with
> > > the fact that a partition's param info remains attached to the
> > > parent's RI_ConstraintInfo even after it's detached from the parent
> > > table using DETACH PARTITION.
> >
> > I think this bit about splitting the struct is a distraction.  Let's get
> > a patch that solves the bug first, and then we can discuss what further
> > refinements we want to do.  I think we should get your patch in
> > CA+HiwqEOrfN9b=f3sDmySPGc4gO-L_VMFHXLLxVmmdP34e64+w@mail.gmail.com
> > committed (which I have not read yet.)  Do you agree with this plan?
> 
> 
> Yeah, I agree.

Or https://www.postgresql.org/message-id/CA+HiwqGrr2YOO6voBM6m_OAc9w-WMxe1gOuQ-UyDPin6zJtyZw@mail.gmail.com ?

+1 from me to either one.

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

  30. Re: Huge memory consumption on partitioned table with FKs

    Amit Langote <amitlangote09@gmail.com> — 2020-12-08T03:59:16Z

    On Tue, Dec 8, 2020 at 12:04 PM Kyotaro Horiguchi
<horikyota.ntt@gmail.com> wrote:
> At Tue, 8 Dec 2020 01:16:00 +0900, Amit Langote <amitlangote09@gmail.com> wrote in
> > Hi Alvaro,
> >
> > On Mon, Dec 7, 2020 at 23:48 Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
> >
> > > On 2020-Dec-07, Amit Langote wrote:
> > >
> > > > On Fri, Dec 4, 2020 at 12:05 PM Kyotaro Horiguchi
> > > > <horikyota.ntt@gmail.com> wrote:
> > > > > > Also, the comment that was in RI_ConstraintInfo now appears in
> > > > > > RI_ConstraintParam, and the new struct (RI_ConstraintInfo) is now
> > > > > > undocumented.  What is the relationship between those two structs?  I
> > > > > > see that they have pointers to each other, but I think the
> > > relationship
> > > > > > should be documented more clearly.
> > > > >
> > > > > I'm not sure the footprint of this patch worth doing but here is a bit
> > > > > more polished version.
> > > >
> > > > I noticed that the foreign_key test fails and it may have to do with
> > > > the fact that a partition's param info remains attached to the
> > > > parent's RI_ConstraintInfo even after it's detached from the parent
> > > > table using DETACH PARTITION.
> > >
> > > I think this bit about splitting the struct is a distraction.  Let's get
> > > a patch that solves the bug first, and then we can discuss what further
> > > refinements we want to do.  I think we should get your patch in
> > > CA+HiwqEOrfN9b=f3sDmySPGc4gO-L_VMFHXLLxVmmdP34e64+w@mail.gmail.com
> > > committed (which I have not read yet.)  Do you agree with this plan?
> >
> >
> > Yeah, I agree.
>
> Or https://www.postgresql.org/message-id/CA+HiwqGrr2YOO6voBM6m_OAc9w-WMxe1gOuQ-UyDPin6zJtyZw@mail.gmail.com ?
>
> +1 from me to either one.

Oh, I hadn't actually checked the actual message that Alvaro
mentioned, but yeah I too am fine with either that one or the latest
one.

-- 
Amit Langote
EDB: http://www.enterprisedb.com

  31. Re: Huge memory consumption on partitioned table with FKs

    Amit Langote <amitlangote09@gmail.com> — 2020-12-08T09:15:53Z

    On Mon, Dec 7, 2020 at 11:01 PM Amit Langote <amitlangote09@gmail.com> wrote:
> On Fri, Dec 4, 2020 at 12:05 PM Kyotaro Horiguchi
> <horikyota.ntt@gmail.com> wrote:
> > > Also, the comment that was in RI_ConstraintInfo now appears in
> > > RI_ConstraintParam, and the new struct (RI_ConstraintInfo) is now
> > > undocumented.  What is the relationship between those two structs?  I
> > > see that they have pointers to each other, but I think the relationship
> > > should be documented more clearly.
> >
> > I'm not sure the footprint of this patch worth doing but here is a bit
> > more polished version.
>
> I noticed that the foreign_key test fails and it may have to do with
> the fact that a partition's param info remains attached to the
> parent's RI_ConstraintInfo even after it's detached from the parent
> table using DETACH PARTITION.

Just for (maybe not so distant) future reference, I managed to fix the
failure with this:

diff --git a/src/backend/utils/adt/ri_triggers.c
b/src/backend/utils/adt/ri_triggers.c
index 187884f..c67f2a6 100644
--- a/src/backend/utils/adt/ri_triggers.c
+++ b/src/backend/utils/adt/ri_triggers.c
@@ -1932,7 +1932,7 @@ ri_BuildQueryKey(RI_QueryKey *key, const
RI_ConstraintInfo *riinfo,
     * We assume struct RI_QueryKey contains no padding bytes, else we'd need
     * to use memset to clear them.
     */
-   key->constr_id = riinfo->param->query_key;
+   key->constr_id = riinfo->constraint_id;
    key->constr_queryno = constr_queryno;
 }

It seems the shareable part (param) is not properly invalidated after
a partition's constraint is detached, apparently leaving query_key in
it set to the parent's constraint id.

-- 
Amit Langote
EDB: http://www.enterprisedb.com

  32. Re: Huge memory consumption on partitioned table with FKs

    Keisuke Kuroda <keisuke.kuroda.3862@gmail.com> — 2021-01-08T04:02:38Z

    Hi Amit-san,

I noticed that this patch
(v3-0001-ri_triggers.c-Use-root-constraint-OID-as-key-to-r.patch)
is not registered in the commitfest. I think it needs to be registered for
commit, is that correct?

I have confirmed that this patch can be applied to HEAD (master),
and that check-world PASS.

Best Regards,

-- 
Keisuke Kuroda
NTT Software Innovation Center
keisuke.kuroda.3862@gmail.com

  33. Re: Huge memory consumption on partitioned table with FKs

    Amit Langote <amitlangote09@gmail.com> — 2021-01-08T06:49:03Z

    Kuroda-san,

On Fri, Jan 8, 2021 at 1:02 PM Keisuke Kuroda
<keisuke.kuroda.3862@gmail.com> wrote:
> Hi Amit-san,
>
> I noticed that this patch
> (v3-0001-ri_triggers.c-Use-root-constraint-OID-as-key-to-r.patch)
> is not registered in the commitfest. I think it needs to be registered for
> commit, is that correct?
>
> I have confirmed that this patch can be applied to HEAD (master),
> and that check-world PASS.

Thanks for checking.  Indeed, it should have been added to the January
commit-fest.  I've added it to the March one:

https://commitfest.postgresql.org/32/2930/

-- 
Amit Langote
EDB: http://www.enterprisedb.com

  34. Re: Huge memory consumption on partitioned table with FKs

    Keisuke Kuroda <keisuke.kuroda.3862@gmail.com> — 2021-01-12T00:48:59Z

    Hi Amit-san,

> Thanks for checking.  Indeed, it should have been added to the January
> commit-fest.  I've added it to the March one:
>
> https://commitfest.postgresql.org/32/2930/

Thank you for your quick response!

-- 
Keisuke Kuroda
NTT Software Innovation Center
keisuke.kuroda.3862@gmail.com

  35. Re: Huge memory consumption on partitioned table with FKs

    David Steele <david@pgmasters.net> — 2021-03-03T13:21:37Z

    On 12/7/20 10:59 PM, Amit Langote wrote:
> On Tue, Dec 8, 2020 at 12:04 PM Kyotaro Horiguchi
> <horikyota.ntt@gmail.com> wrote:
>> At Tue, 8 Dec 2020 01:16:00 +0900, Amit Langote <amitlangote09@gmail.com> wrote in
>>> On Mon, Dec 7, 2020 at 23:48 Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
>>>>
>>>> I think this bit about splitting the struct is a distraction.  Let's get
>>>> a patch that solves the bug first, and then we can discuss what further
>>>> refinements we want to do.  I think we should get your patch in
>>>> CA+HiwqEOrfN9b=f3sDmySPGc4gO-L_VMFHXLLxVmmdP34e64+w@mail.gmail.com
>>>> committed (which I have not read yet.)  Do you agree with this plan?
>>>
>>>
>>> Yeah, I agree.
>>
>> Or https://www.postgresql.org/message-id/CA+HiwqGrr2YOO6voBM6m_OAc9w-WMxe1gOuQ-UyDPin6zJtyZw@mail.gmail.com ?
>>
>> +1 from me to either one.
> 
> Oh, I hadn't actually checked the actual message that Alvaro
> mentioned, but yeah I too am fine with either that one or the latest
> one.

Any progress on the decision of how to handle this bug? Not sure if that 
was handled elsewhere but it appears to be key to making progress on 
this patch.

Regards,
-- 
-David
david@pgmasters.net

  36. Re: Huge memory consumption on partitioned table with FKs

    Amit Langote <amitlangote09@gmail.com> — 2021-03-03T13:59:35Z

    Hi David,

On Wed, Mar 3, 2021 at 10:21 PM David Steele <david@pgmasters.net> wrote:
> On 12/7/20 10:59 PM, Amit Langote wrote:
> > On Tue, Dec 8, 2020 at 12:04 PM Kyotaro Horiguchi
> > <horikyota.ntt@gmail.com> wrote:
> >> At Tue, 8 Dec 2020 01:16:00 +0900, Amit Langote <amitlangote09@gmail.com> wrote in
> >>> On Mon, Dec 7, 2020 at 23:48 Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
> >>>>
> >>>> I think this bit about splitting the struct is a distraction.  Let's get
> >>>> a patch that solves the bug first, and then we can discuss what further
> >>>> refinements we want to do.  I think we should get your patch in
> >>>> CA+HiwqEOrfN9b=f3sDmySPGc4gO-L_VMFHXLLxVmmdP34e64+w@mail.gmail.com
> >>>> committed (which I have not read yet.)  Do you agree with this plan?
> >>>
> >>>
> >>> Yeah, I agree.
> >>
> >> Or https://www.postgresql.org/message-id/CA+HiwqGrr2YOO6voBM6m_OAc9w-WMxe1gOuQ-UyDPin6zJtyZw@mail.gmail.com ?
> >>
> >> +1 from me to either one.
> >
> > Oh, I hadn't actually checked the actual message that Alvaro
> > mentioned, but yeah I too am fine with either that one or the latest
> > one.
>
> Any progress on the decision of how to handle this bug? Not sure if that
> was handled elsewhere but it appears to be key to making progress on
> this patch.

The v3 patch posted on Dec 4 is still what is being proposed to fix this.

I don't know of any unaddressed comments on the patch, so I've marked
the entry Ready for Committer.

-- 
Amit Langote
EDB: http://www.enterprisedb.com

  37. Re: Huge memory consumption on partitioned table with FKs

    Alvaro Herrera <alvherre@alvh.no-ip.org> — 2021-03-03T15:00:35Z

    On 2021-Mar-03, Amit Langote wrote:

> I don't know of any unaddressed comments on the patch, so I've marked
> the entry Ready for Committer.

Thanks, I'll look at it later this week.

-- 
Álvaro Herrera                            39°49'30"S 73°17'W
#error "Operator lives in the wrong universe"
  ("Use of cookies in real-time system development", M. Gleixner, M. Mc Guire)

  38. Re: Huge memory consumption on partitioned table with FKs

    Tom Lane <tgl@sss.pgh.pa.us> — 2021-03-04T21:00:36Z

    Amit Langote <amitlangote09@gmail.com> writes:
> Updated patch attached.

This claim seems false on its face:

> All child constraints of a given foreign key constraint can use the
> same RI query and the resulting plan, that is, no need to create as
> many copies of the query and the plan as there are partitions, as
> happens now due to the child constraint OID being used in the key
> for ri_query_cache.

What if the child tables don't have the same physical column numbers
as the parent?  The comment claiming that it's okay if riinfo->fk_attnums
doesn't match seems quite off point, because the query plan is still
going to need to use the correct column numbers.  Even if column numbers
are the same, the plan would also contain table and index OIDs that could
only be right for one partition.

I could imagine translating a parent plan to apply to a child instead of
building it from scratch, but that would take a lot of code we don't have
(there's no rewriteHandler infrastructure for plan nodes).

Maybe I'm missing something, because I see that the cfbot claims
this is passing, and I'd sure like to think that our test coverage
is not so thin that it'd fail to detect probing the wrong partition
for foreign key matches.  But that's what it looks like this patch
will do.

			regards, tom lane

  39. Re: Huge memory consumption on partitioned table with FKs

    Amit Langote <amitlangote09@gmail.com> — 2021-03-08T05:27:39Z

    On Fri, Mar 5, 2021 at 6:00 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Amit Langote <amitlangote09@gmail.com> writes:
> > Updated patch attached.
>
> This claim seems false on its face:
>
> > All child constraints of a given foreign key constraint can use the
> > same RI query and the resulting plan, that is, no need to create as
> > many copies of the query and the plan as there are partitions, as
> > happens now due to the child constraint OID being used in the key
> > for ri_query_cache.
>
> What if the child tables don't have the same physical column numbers
> as the parent?  The comment claiming that it's okay if riinfo->fk_attnums
> doesn't match seems quite off point, because the query plan is still
> going to need to use the correct column numbers.  Even if column numbers
> are the same, the plan would also contain table and index OIDs that could
> only be right for one partition.
>
> I could imagine translating a parent plan to apply to a child instead of
> building it from scratch, but that would take a lot of code we don't have
> (there's no rewriteHandler infrastructure for plan nodes).
>
> Maybe I'm missing something, because I see that the cfbot claims
> this is passing, and I'd sure like to think that our test coverage
> is not so thin that it'd fail to detect probing the wrong partition
> for foreign key matches.  But that's what it looks like this patch
> will do.

The quoted comment could have been written to be clearer about this,
but it's not talking about the table that is to be queried, but the
table whose RI trigger is being executed.  In all the cases except one
(mentioned below), the table that is queried is the same irrespective
of which partition's trigger is being executed, so we're basically
creating the same plan separately for each partition.

create table foo (a int primary key) partition by range (a);
create table foo1 partition of foo for values from (minvalue) to (1);
create table foo2 partition of foo for values from (1) to (maxvalue);
create table bar (a int references foo) partition by range (a);
create table bar1 partition of bar for values from (minvalue) to (1);
create table bar2 partition of bar for values from (1) to (maxvalue);

insert into foo values (0), (1);
insert into bar values (0), (1);

For the 2nd insert statement, RI_FKey_check() issues the following
query irrespective of whether it is called from bar1's or bar2's
insert check RI trigger:

SELECT 1 FROM foo WHERE foo.a = $1;

Likewise for:

delete from foo;

ri_restrict() issues the following query irrespective of whether
called from foo1's or foo2's delete action trigger:

SELECT 1 FROM bar WHERE bar.a = $1;

The one case I found in which the queried table is the same as the
table whose trigger has been invoked is ri_Check_Pk_Match(), in which
case, it's actually wrong for partitions to share the plan, so the
patch was wrong in that case.  Apparently, we didn't test that case
with partitioning, so I added one as follows:

+-- test that ri_Check_Pk_Match() scans the correct partition for a deferred
+-- ON DELETE/UPDATE NO ACTION constraint
+CREATE SCHEMA fkpart10
+  CREATE TABLE tbl1(f1 int PRIMARY KEY) PARTITION BY RANGE(f1)
+  CREATE TABLE tbl1_p1 PARTITION OF tbl1 FOR VALUES FROM (minvalue) TO (1)
+  CREATE TABLE tbl1_p2 PARTITION OF tbl1 FOR VALUES FROM (1) TO (maxvalue)
+  CREATE TABLE tbl2(f1 int REFERENCES tbl1 DEFERRABLE INITIALLY DEFERRED);
+INSERT INTO fkpart10.tbl1 VALUES (0), (1);
+INSERT INTO fkpart10.tbl2 VALUES (0), (1);
+BEGIN;
+DELETE FROM fkpart10.tbl1 WHERE f1 = 0;
+UPDATE fkpart10.tbl1 SET f1 = 2 WHERE f1 = 1;
+INSERT INTO fkpart10.tbl1 VALUES (0), (1);
+COMMIT;
+DROP SCHEMA fkpart10 CASCADE;
+NOTICE:  drop cascades to 2 other objects
+DETAIL:  drop cascades to table fkpart10.tbl1
+drop cascades to table fkpart10.tbl2

With the v3 patch, the test case fails with the following error on COMMIT:

+ERROR:  update or delete on table "tbl1_p2" violates foreign key
constraint "tbl2_f1_fkey2" on table "tbl2"
+DETAIL:  Key (f1)=(1) is still referenced from table "tbl2".

That's because in ri_Check_Pk_Match(), partition tbl2 ends up using
the same cached plan as tbl1 and so scans tbl1 to check whether the
row deleted from tbl2 has reappeared, which is of course wrong.  I
have fixed that by continuing to use the individual partition's
constraint's OID as the query key for this particular query.

I have also removed the somewhat confusing comment about fk_attnums.
The point it was trying to make is that fk_attnums being possibly
different among partitions does not make a difference to what any
given shareable query's text ultimately looks like.  Those attribute
numbers are only used by the macros RIAttName(), RIAttType(),
RIAttCollation() when generating the query text and they'd all return
the same values no matter which partition's attribute numbers are
used.

Updated patch attached.

--
Amit Langote
EDB: http://www.enterprisedb.com

  40. Re: Huge memory consumption on partitioned table with FKs

    Andy Fan <zhihui.fan1213@gmail.com> — 2021-03-08T07:43:36Z

    On Fri, Mar 5, 2021 at 5:00 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Amit Langote <amitlangote09@gmail.com> writes:
> > Updated patch attached.
>
> This claim seems false on its face:
>
> > All child constraints of a given foreign key constraint can use the
> > same RI query and the resulting plan, that is, no need to create as
> > many copies of the query and the plan as there are partitions, as
> > happens now due to the child constraint OID being used in the key
> > for ri_query_cache.
>
> What if the child tables don't have the same physical column numbers
> as the parent?


My point below is a bit off-topic, but I want to share it here.  Since
we implement a partitioned table in PG with the inherited class, it has much
more flexibility than other databases.  Like in PG,  we allow different
partitions
have different physical order,  different indexes,  maybe different index
states.
that would cause our development work hard in many places and cause some
runtime issues as well (like catalog memory usage),  have we discussed
limiting some flexibility so that we can have better coding/running
experience?
I want to do some research in this direction, but it would be better that I
can
listen to any advice from others.  More specifically, I want to reduce the
memory
usage of Partitioned table/index as the first step.  In my testing, each
IndexOptInfo
will use 2kB memory in each backend.


> The comment claiming that it's okay if riinfo->fk_attnums
> doesn't match seems quite off point, because the query plan is still
> going to need to use the correct column numbers.  Even if column numbers
> are the same, the plan would also contain table and index OIDs that could
> only be right for one partition.
>
> I could imagine translating a parent plan to apply to a child instead of
> building it from scratch, but that would take a lot of code we don't have
> (there's no rewriteHandler infrastructure for plan nodes).
>
> Maybe I'm missing something, because I see that the cfbot claims
> this is passing, and I'd sure like to think that our test coverage
> is not so thin that it'd fail to detect probing the wrong partition
> for foreign key matches.  But that's what it looks like this patch
> will do.
>
>                         regards, tom lane
>
>
>

-- 
Best Regards
Andy Fan (https://www.aliyun.com/)

  41. Re: Huge memory consumption on partitioned table with FKs

    Andy Fan <zhihui.fan1213@gmail.com> — 2021-03-08T11:39:28Z

    On Mon, Mar 8, 2021 at 3:43 PM Andy Fan <zhihui.fan1213@gmail.com> wrote:

>
>
> On Fri, Mar 5, 2021 at 5:00 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
>> Amit Langote <amitlangote09@gmail.com> writes:
>> > Updated patch attached.
>>
>> This claim seems false on its face:
>>
>> > All child constraints of a given foreign key constraint can use the
>> > same RI query and the resulting plan, that is, no need to create as
>> > many copies of the query and the plan as there are partitions, as
>> > happens now due to the child constraint OID being used in the key
>> > for ri_query_cache.
>>
>> What if the child tables don't have the same physical column numbers
>> as the parent?
>
>
> My point below is a bit off-topic, but I want to share it here.  Since
> we implement a partitioned table in PG with the inherited class, it has
> much
> more flexibility than other databases.  Like in PG,  we allow different
> partitions
> have different physical order,  different indexes,  maybe different index
> states.
> that would cause our development work hard in many places and cause some
> runtime issues as well (like catalog memory usage),  have we discussed
> limiting some flexibility so that we can have better coding/running
> experience?
> I want to do some research in this direction, but it would be better that
> I can
> listen to any advice from others.  More specifically, I want to reduce the
> memory
> usage of Partitioned table/index as the first step.  In my testing, each
> IndexOptInfo
> will use 2kB memory in each backend.
>

As for the compatible issue,  will it be ok to introduce a new  concept
like "
CREATE TABLE p (a int) partitioned by list(a) RESTRICTED".  We can add these
limitation to restricted partitioned relation only.


>
The comment claiming that it's okay if riinfo->fk_attnums
>> doesn't match seems quite off point, because the query plan is still
>> going to need to use the correct column numbers.  Even if column numbers
>> are the same, the plan would also contain table and index OIDs that could
>> only be right for one partition.
>>
>> I could imagine translating a parent plan to apply to a child instead of
>> building it from scratch, but that would take a lot of code we don't have
>> (there's no rewriteHandler infrastructure for plan nodes).
>>
>> Maybe I'm missing something, because I see that the cfbot claims
>> this is passing, and I'd sure like to think that our test coverage
>> is not so thin that it'd fail to detect probing the wrong partition
>> for foreign key matches.  But that's what it looks like this patch
>> will do.
>>
>>                         regards, tom lane
>>
>>
>>
>
> --
> Best Regards
> Andy Fan (https://www.aliyun.com/)
>


-- 
Best Regards
Andy Fan (https://www.aliyun.com/)

  42. Re: Huge memory consumption on partitioned table with FKs

    Amit Langote <amitlangote09@gmail.com> — 2021-03-08T12:40:46Z

    Hi Andy,

On Mon, Mar 8, 2021 at 8:39 PM Andy Fan <zhihui.fan1213@gmail.com> wrote:
> On Mon, Mar 8, 2021 at 3:43 PM Andy Fan <zhihui.fan1213@gmail.com> wrote:
>> My point below is a bit off-topic, but I want to share it here.  Since
>> we implement a partitioned table in PG with the inherited class, it has much
>> more flexibility than other databases.  Like in PG,  we allow different partitions
>> have different physical order,  different indexes,  maybe different index states.
>> that would cause our development work hard in many places and cause some
>> runtime issues as well (like catalog memory usage),  have we discussed
>> limiting some flexibility so that we can have better coding/running experience?
>> I want to do some research in this direction, but it would be better that I can
>> listen to any advice from others.  More specifically, I want to reduce the memory
>> usage of Partitioned table/index as the first step.  In my testing, each IndexOptInfo
>> will use 2kB memory in each backend.
>
>
> As for the compatible issue,  will it be ok to introduce a new  concept like "
> CREATE TABLE p (a int) partitioned by list(a) RESTRICTED".  We can add these
> limitation to restricted partitioned relation only.

I think you'd agree that the topics you want to discuss deserve a
separate discussion thread.  You may refer to this discussion in that
new thread if you think that your proposals can solve the problem
being discussed here more generally, which would of course be great.

-- 
Amit Langote
EDB: http://www.enterprisedb.com

  43. Re: Huge memory consumption on partitioned table with FKs

    Andy Fan <zhihui.fan1213@gmail.com> — 2021-03-08T12:53:33Z

    On Mon, Mar 8, 2021 at 8:42 PM Amit Langote <amitlangote09@gmail.com> wrote:

> Hi Andy,
>
> On Mon, Mar 8, 2021 at 8:39 PM Andy Fan <zhihui.fan1213@gmail.com> wrote:
> > On Mon, Mar 8, 2021 at 3:43 PM Andy Fan <zhihui.fan1213@gmail.com>
> wrote:
> >> My point below is a bit off-topic, but I want to share it here.  Since
> >> we implement a partitioned table in PG with the inherited class, it has
> much
> >> more flexibility than other databases.  Like in PG,  we allow different
> partitions
> >> have different physical order,  different indexes,  maybe different
> index states.
> >> that would cause our development work hard in many places and cause some
> >> runtime issues as well (like catalog memory usage),  have we discussed
> >> limiting some flexibility so that we can have better coding/running
> experience?
> >> I want to do some research in this direction, but it would be better
> that I can
> >> listen to any advice from others.  More specifically, I want to reduce
> the memory
> >> usage of Partitioned table/index as the first step.  In my testing,
> each IndexOptInfo
> >> will use 2kB memory in each backend.
> >
> >
> > As for the compatible issue,  will it be ok to introduce a new  concept
> like "
> > CREATE TABLE p (a int) partitioned by list(a) RESTRICTED".  We can add
> these
> > limitation to restricted partitioned relation only.
>
> I think you'd agree that the topics you want to discuss deserve a
> separate discussion thread.  You may refer to this discussion in that
> new thread if you think that your proposals can solve the problem
> being discussed here more generally, which would of course be great.
>
>
Sure, I can prepare more data and start a new thread for this.

-- 
Best Regards
Andy Fan (https://www.aliyun.com/)

  44. Re: Huge memory consumption on partitioned table with FKs

    Amit Langote <amitlangote09@gmail.com> — 2021-03-08T12:58:03Z

    On Mon, Mar 8, 2021 at 9:53 PM Andy Fan <zhihui.fan1213@gmail.com> wrote:
> On Mon, Mar 8, 2021 at 8:42 PM Amit Langote <amitlangote09@gmail.com> wrote:
>> On Mon, Mar 8, 2021 at 8:39 PM Andy Fan <zhihui.fan1213@gmail.com> wrote:
>> > On Mon, Mar 8, 2021 at 3:43 PM Andy Fan <zhihui.fan1213@gmail.com> wrote:
>> >> My point below is a bit off-topic, but I want to share it here.  Since
>> >> we implement a partitioned table in PG with the inherited class, it has much
>> >> more flexibility than other databases.  Like in PG,  we allow different partitions
>> >> have different physical order,  different indexes,  maybe different index states.
>> >> that would cause our development work hard in many places and cause some
>> >> runtime issues as well (like catalog memory usage),  have we discussed
>> >> limiting some flexibility so that we can have better coding/running experience?
>> >> I want to do some research in this direction, but it would be better that I can
>> >> listen to any advice from others.  More specifically, I want to reduce the memory
>> >> usage of Partitioned table/index as the first step.  In my testing, each IndexOptInfo
>> >> will use 2kB memory in each backend.
>> >
>> >
>> > As for the compatible issue,  will it be ok to introduce a new  concept like "
>> > CREATE TABLE p (a int) partitioned by list(a) RESTRICTED".  We can add these
>> > limitation to restricted partitioned relation only.
>>
>> I think you'd agree that the topics you want to discuss deserve a
>> separate discussion thread.  You may refer to this discussion in that
>> new thread if you think that your proposals can solve the problem
>> being discussed here more generally, which would of course be great.
>
> Sure, I can prepare more data and start a new thread for this.

Great, thanks.

-- 
Amit Langote
EDB: http://www.enterprisedb.com

  45. Re: Huge memory consumption on partitioned table with FKs

    Tom Lane <tgl@sss.pgh.pa.us> — 2021-03-09T23:37:44Z

    Amit Langote <amitlangote09@gmail.com> writes:
> On Fri, Mar 5, 2021 at 6:00 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> This claim seems false on its face:
>>> All child constraints of a given foreign key constraint can use the
>>> same RI query and the resulting plan, that is, no need to create as
>>> many copies of the query and the plan as there are partitions, as
>>> happens now due to the child constraint OID being used in the key
>>> for ri_query_cache.

> The quoted comment could have been written to be clearer about this,
> but it's not talking about the table that is to be queried, but the
> table whose RI trigger is being executed.  In all the cases except one
> (mentioned below), the table that is queried is the same irrespective
> of which partition's trigger is being executed, so we're basically
> creating the same plan separately for each partition.

Hmm.  So, the key point is that the values coming from the partitioned
child table are injected into the test query as parameters, not as
column references, thus it doesn't matter *to the test query* what
numbers the referencing columns have in that child.  We just have to
be sure we pass the right parameter values.  But ... doesn't the code
use riinfo->fk_attnums[] to pull out the values to be passed?

IOW, I now get the point about being able to share the SPI plans,
but I'm still dubious about sharing the RI_ConstraintInfo cache entries.

It looks to me like the v4 patch is now actually not sharing the
cache entries, ie their hash key is just the child constraint OID
same as before; but the comments are pretty confused about this.

It might be simpler if you add just one new field which is the OID of
the constraint that we're building the SPI query from, which might be
either equal to constraint_id, or the OID of some parent constraint.
In particular it's not clear to me why we need both constraint_parent
and constraint_root_id.

			regards, tom lane

  46. Re: Huge memory consumption on partitioned table with FKs

    Amit Langote <amitlangote09@gmail.com> — 2021-03-10T15:44:53Z

    On Wed, Mar 10, 2021 at 8:37 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Amit Langote <amitlangote09@gmail.com> writes:
> > On Fri, Mar 5, 2021 at 6:00 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >> This claim seems false on its face:
> >>> All child constraints of a given foreign key constraint can use the
> >>> same RI query and the resulting plan, that is, no need to create as
> >>> many copies of the query and the plan as there are partitions, as
> >>> happens now due to the child constraint OID being used in the key
> >>> for ri_query_cache.
>
> > The quoted comment could have been written to be clearer about this,
> > but it's not talking about the table that is to be queried, but the
> > table whose RI trigger is being executed.  In all the cases except one
> > (mentioned below), the table that is queried is the same irrespective
> > of which partition's trigger is being executed, so we're basically
> > creating the same plan separately for each partition.
>
> Hmm.  So, the key point is that the values coming from the partitioned
> child table are injected into the test query as parameters, not as
> column references, thus it doesn't matter *to the test query* what
> numbers the referencing columns have in that child.  We just have to
> be sure we pass the right parameter values.

Right.

>  But ... doesn't the code
> use riinfo->fk_attnums[] to pull out the values to be passed?

Yes, from a slot that belongs to the child table.

> IOW, I now get the point about being able to share the SPI plans,
> but I'm still dubious about sharing the RI_ConstraintInfo cache entries.

There was actually a proposal upthread about sharing the
RI_ConstraintInfo too, but we decided to not pursue that for now.

> It looks to me like the v4 patch is now actually not sharing the
> cache entries, ie their hash key is just the child constraint OID
> same as before;

Yeah, you may see that we're only changing ri_BuildQueryKey() in the
patch affecting only ri_query_cache, but not ri_LoadConstraintInfo()
which leaves ri_constraint_cache unaffected.

> but the comments are pretty confused about this.

I've tried improving the comment in ri_BuildQueryKey() a bit to make
clear what is and what is not being shared between partitions.

> It might be simpler if you add just one new field which is the OID of
> the constraint that we're building the SPI query from, which might be
> either equal to constraint_id, or the OID of some parent constraint.
> In particular it's not clear to me why we need both constraint_parent
> and constraint_root_id.

Yeah, I think constraint_parent is a leftover from some earlier
hacking.  I have removed it.

Attached updated patch.

--
Amit Langote
EDB: http://www.enterprisedb.com

  47. Re: Huge memory consumption on partitioned table with FKs

    Tom Lane <tgl@sss.pgh.pa.us> — 2021-03-10T19:24:57Z

    Amit Langote <amitlangote09@gmail.com> writes:
> On Wed, Mar 10, 2021 at 8:37 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Hmm.  So, the key point is that the values coming from the partitioned
>> child table are injected into the test query as parameters, not as
>> column references, thus it doesn't matter *to the test query* what
>> numbers the referencing columns have in that child.  We just have to
>> be sure we pass the right parameter values.

> Right.

I did some cosmetic fooling with this (mostly, rewriting the comments
YA time) and pushed it.

			regards, tom lane

  48. Re: Huge memory consumption on partitioned table with FKs

    Amit Langote <amitlangote09@gmail.com> — 2021-03-11T00:39:27Z

    On Thu, Mar 11, 2021 at 4:25 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Amit Langote <amitlangote09@gmail.com> writes:
> > On Wed, Mar 10, 2021 at 8:37 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> >> Hmm.  So, the key point is that the values coming from the partitioned
> >> child table are injected into the test query as parameters, not as
> >> column references, thus it doesn't matter *to the test query* what
> >> numbers the referencing columns have in that child.  We just have to
> >> be sure we pass the right parameter values.
>
> > Right.
>
> I did some cosmetic fooling with this (mostly, rewriting the comments
> YA time) and pushed it.

Perfect.   Thanks for your time on this.

-- 
Amit Langote
EDB: http://www.enterprisedb.com

  49. Re: Huge memory consumption on partitioned table with FKs

    Keisuke Kuroda <keisuke.kuroda.3862@gmail.com> — 2021-03-11T01:06:57Z

    Hi hackers,

> >
> > I did some cosmetic fooling with this (mostly, rewriting the comments
> > YA time) and pushed it.
>
> Perfect.   Thanks for your time on this.
>

Thank you for your help! I'm glad to solve it.

-- 
Keisuke Kuroda
NTT Software Innovation Center
keisuke.kuroda.3862@gmail.com

  50. Re: Huge memory consumption on partitioned table with FKs

    Tatsuro Yamada <tatsuro.yamada.tf@nttcom.co.jp> — 2021-03-11T06:10:04Z

    On 2021/03/11 9:39, Amit Langote wrote:
> On Thu, Mar 11, 2021 at 4:25 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Amit Langote <amitlangote09@gmail.com> writes:
>>> On Wed, Mar 10, 2021 at 8:37 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>>> Hmm.  So, the key point is that the values coming from the partitioned
>>>> child table are injected into the test query as parameters, not as
>>>> column references, thus it doesn't matter *to the test query* what
>>>> numbers the referencing columns have in that child.  We just have to
>>>> be sure we pass the right parameter values.
>>
>>> Right.
>>
>> I did some cosmetic fooling with this (mostly, rewriting the comments
>> YA time) and pushed it.
> 
> Perfect.   Thanks for your time on this.


Thanks for fixing the problem! :-D


Regards,
Tatsuro Yamada

  51. Re: Huge memory consumption on partitioned table with FKs

    Tom Lane <tgl@sss.pgh.pa.us> — 2021-03-11T17:01:56Z

    Tatsuro Yamada <tatsuro.yamada.tf@nttcom.co.jp> writes:
> Thanks for fixing the problem! :-D

Hmm, I'm not sure we're done with this patch:

https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=husky&dt=2021-03-10%2021%3A09%3A32

The critical log extract is

2021-03-11 05:10:13.012 CET [21574:1082] pg_regress/foreign_key LOG:  statement: insert into fk_notpartitioned_pk (a, b)
	  select 2048, x from generate_series(1,10) x;
2021-03-11 05:10:13.104 CET [11830:368] LOG:  server process (PID 21574) was terminated by signal 11: Segmentation fault
2021-03-11 05:10:13.104 CET [11830:369] DETAIL:  Failed process was running: insert into fk_notpartitioned_pk (a, b)
	  select 2048, x from generate_series(1,10) x;

Now, maybe it's a coincidence that husky failed on a
partitioned-foreign-key test right after this patch went in, but I bet
not.  Since husky runs CLOBBER_CACHE_ALWAYS, it looks to me like we've
overlooked some cache-reset scenario or other.

			regards, tom lane

  52. Re: Huge memory consumption on partitioned table with FKs

    Tom Lane <tgl@sss.pgh.pa.us> — 2021-03-11T17:44:37Z

    I wrote:
> Now, maybe it's a coincidence that husky failed on a
> partitioned-foreign-key test right after this patch went in, but I bet
> not.  Since husky runs CLOBBER_CACHE_ALWAYS, it looks to me like we've
> overlooked some cache-reset scenario or other.

After reproducing it here, that *is* a coincidence.  I shall now go beat
up on the correct blame-ee, instead.

			regards, tom lane

  53. Re: Huge memory consumption on partitioned table with FKs

    Tatsuro Yamada <tatsuro.yamada.tf@nttcom.co.jp> — 2021-03-12T03:35:08Z

    On 2021/03/12 2:44, Tom Lane wrote:
> I wrote:
>> Now, maybe it's a coincidence that husky failed on a
>> partitioned-foreign-key test right after this patch went in, but I bet
>> not.  Since husky runs CLOBBER_CACHE_ALWAYS, it looks to me like we've
>> overlooked some cache-reset scenario or other.
> 
> After reproducing it here, that *is* a coincidence.  I shall now go beat
> up on the correct blame-ee, instead.


I did "make installcheck-parallel" on 7bb97211a, just in case. It was successful. :-D

Regards,
Tatsuro Yamada