Thread: Huge memory consumption on partitioned table with FKs
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
Attachment
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
Attachment
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
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
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.
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.
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
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
Attachment
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/
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
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
Attachment
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
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
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
Attachment
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
From bbdd0647452a26fdf7da313967f85241c6111fcb Mon Sep 17 00:00:00 2001
From: Kyotaro Horiguchi <horikyoga.ntt@gmail.com>
Date: Thu, 3 Dec 2020 16:15:57 +0900
Subject: [PATCH 1/2] separte riinfo
---
src/backend/utils/adt/ri_triggers.c | 281 ++++++++++++++++------------
1 file changed, 161 insertions(+), 120 deletions(-)
diff --git a/src/backend/utils/adt/ri_triggers.c b/src/backend/utils/adt/ri_triggers.c
index 02b1a3868f..3f8407c311 100644
--- a/src/backend/utils/adt/ri_triggers.c
+++ b/src/backend/utils/adt/ri_triggers.c
@@ -97,14 +97,10 @@
* Information extracted from an FK pg_constraint entry. This is cached in
* ri_constraint_cache.
*/
-typedef struct RI_ConstraintInfo
+typedef struct RI_ConstraintParam
{
- Oid constraint_id; /* OID of pg_constraint entry (hash key) */
- bool valid; /* successfully initialized? */
- uint32 oidHashValue; /* hash value of pg_constraint OID */
- NameData conname; /* name of the FK constraint */
+ Oid query_key;
Oid pk_relid; /* referenced relation */
- Oid fk_relid; /* referencing relation */
char confupdtype; /* foreign key's ON UPDATE action */
char confdeltype; /* foreign key's ON DELETE action */
char confmatchtype; /* foreign key's match type */
@@ -114,6 +110,17 @@ typedef struct RI_ConstraintInfo
Oid pf_eq_oprs[RI_MAX_NUMKEYS]; /* equality operators (PK = FK) */
Oid pp_eq_oprs[RI_MAX_NUMKEYS]; /* equality operators (PK = PK) */
Oid ff_eq_oprs[RI_MAX_NUMKEYS]; /* equality operators (FK = FK) */
+ struct RI_ConstraintInfo *ownerinfo; /* owner info of this param */
+} RI_ConstraintParam;
+
+typedef struct RI_ConstraintInfo
+{
+ Oid constraint_id;
+ bool valid; /* successfully initialized? */
+ uint32 oidHashValue; /* hash value of pg_constraint OID */
+ NameData conname; /* name of the FK constraint */
+ Oid fk_relid; /* referencing relation */
+ RI_ConstraintParam *param; /* sharable parameters */
dlist_node valid_link; /* Link in list of valid entries */
} RI_ConstraintInfo;
@@ -264,7 +271,7 @@ RI_FKey_check(TriggerData *trigdata)
* SELECT FOR KEY SHARE will get on it.
*/
fk_rel = trigdata->tg_relation;
- pk_rel = table_open(riinfo->pk_relid, RowShareLock);
+ pk_rel = table_open(riinfo->param->pk_relid, RowShareLock);
switch (ri_NullCheck(RelationGetDescr(fk_rel), newslot, riinfo, false))
{
@@ -283,7 +290,7 @@ RI_FKey_check(TriggerData *trigdata)
* This is the only case that differs between the three kinds of
* MATCH.
*/
- switch (riinfo->confmatchtype)
+ switch (riinfo->param->confmatchtype)
{
case FKCONSTR_MATCH_FULL:
@@ -364,17 +371,17 @@ RI_FKey_check(TriggerData *trigdata)
appendStringInfo(&querybuf, "SELECT 1 FROM %s%s x",
pk_only, pkrelname);
querysep = "WHERE";
- for (int i = 0; i < riinfo->nkeys; i++)
+ for (int i = 0; i < riinfo->param->nkeys; i++)
{
- Oid pk_type = RIAttType(pk_rel, riinfo->pk_attnums[i]);
- Oid fk_type = RIAttType(fk_rel, riinfo->fk_attnums[i]);
+ Oid pk_type = RIAttType(pk_rel, riinfo->param->pk_attnums[i]);
+ Oid fk_type = RIAttType(fk_rel, riinfo->param->fk_attnums[i]);
quoteOneName(attname,
- RIAttName(pk_rel, riinfo->pk_attnums[i]));
+ RIAttName(pk_rel, riinfo->param->pk_attnums[i]));
sprintf(paramname, "$%d", i + 1);
ri_GenerateQual(&querybuf, querysep,
attname, pk_type,
- riinfo->pf_eq_oprs[i],
+ riinfo->param->pf_eq_oprs[i],
paramname, fk_type);
querysep = "AND";
queryoids[i] = fk_type;
@@ -382,7 +389,7 @@ RI_FKey_check(TriggerData *trigdata)
appendStringInfoString(&querybuf, " FOR KEY SHARE OF x");
/* Prepare and save the plan */
- qplan = ri_PlanCheck(querybuf.data, riinfo->nkeys, queryoids,
+ qplan = ri_PlanCheck(querybuf.data, riinfo->param->nkeys, queryoids,
&qkey, fk_rel, pk_rel);
}
@@ -492,16 +499,16 @@ ri_Check_Pk_Match(Relation pk_rel, Relation fk_rel,
appendStringInfo(&querybuf, "SELECT 1 FROM %s%s x",
pk_only, pkrelname);
querysep = "WHERE";
- for (int i = 0; i < riinfo->nkeys; i++)
+ for (int i = 0; i < riinfo->param->nkeys; i++)
{
- Oid pk_type = RIAttType(pk_rel, riinfo->pk_attnums[i]);
+ Oid pk_type = RIAttType(pk_rel, riinfo->param->pk_attnums[i]);
quoteOneName(attname,
- RIAttName(pk_rel, riinfo->pk_attnums[i]));
+ RIAttName(pk_rel, riinfo->param->pk_attnums[i]));
sprintf(paramname, "$%d", i + 1);
ri_GenerateQual(&querybuf, querysep,
attname, pk_type,
- riinfo->pp_eq_oprs[i],
+ riinfo->param->pp_eq_oprs[i],
paramname, pk_type);
querysep = "AND";
queryoids[i] = pk_type;
@@ -509,7 +516,7 @@ ri_Check_Pk_Match(Relation pk_rel, Relation fk_rel,
appendStringInfoString(&querybuf, " FOR KEY SHARE OF x");
/* Prepare and save the plan */
- qplan = ri_PlanCheck(querybuf.data, riinfo->nkeys, queryoids,
+ qplan = ri_PlanCheck(querybuf.data, riinfo->param->nkeys, queryoids,
&qkey, fk_rel, pk_rel);
}
@@ -679,19 +686,19 @@ ri_restrict(TriggerData *trigdata, bool is_no_action)
appendStringInfo(&querybuf, "SELECT 1 FROM %s%s x",
fk_only, fkrelname);
querysep = "WHERE";
- for (int i = 0; i < riinfo->nkeys; i++)
+ for (int i = 0; i < riinfo->param->nkeys; i++)
{
- Oid pk_type = RIAttType(pk_rel, riinfo->pk_attnums[i]);
- Oid fk_type = RIAttType(fk_rel, riinfo->fk_attnums[i]);
- Oid pk_coll = RIAttCollation(pk_rel, riinfo->pk_attnums[i]);
- Oid fk_coll = RIAttCollation(fk_rel, riinfo->fk_attnums[i]);
+ Oid pk_type = RIAttType(pk_rel, riinfo->param->pk_attnums[i]);
+ Oid fk_type = RIAttType(fk_rel, riinfo->param->fk_attnums[i]);
+ Oid pk_coll = RIAttCollation(pk_rel, riinfo->param->pk_attnums[i]);
+ Oid fk_coll = RIAttCollation(fk_rel, riinfo->param->fk_attnums[i]);
quoteOneName(attname,
- RIAttName(fk_rel, riinfo->fk_attnums[i]));
+ RIAttName(fk_rel, riinfo->param->fk_attnums[i]));
sprintf(paramname, "$%d", i + 1);
ri_GenerateQual(&querybuf, querysep,
paramname, pk_type,
- riinfo->pf_eq_oprs[i],
+ riinfo->param->pf_eq_oprs[i],
attname, fk_type);
if (pk_coll != fk_coll && !get_collation_isdeterministic(pk_coll))
ri_GenerateQualCollation(&querybuf, pk_coll);
@@ -701,7 +708,7 @@ ri_restrict(TriggerData *trigdata, bool is_no_action)
appendStringInfoString(&querybuf, " FOR KEY SHARE OF x");
/* Prepare and save the plan */
- qplan = ri_PlanCheck(querybuf.data, riinfo->nkeys, queryoids,
+ qplan = ri_PlanCheck(querybuf.data, riinfo->param->nkeys, queryoids,
&qkey, fk_rel, pk_rel);
}
@@ -785,19 +792,19 @@ RI_FKey_cascade_del(PG_FUNCTION_ARGS)
appendStringInfo(&querybuf, "DELETE FROM %s%s",
fk_only, fkrelname);
querysep = "WHERE";
- for (int i = 0; i < riinfo->nkeys; i++)
+ for (int i = 0; i < riinfo->param->nkeys; i++)
{
- Oid pk_type = RIAttType(pk_rel, riinfo->pk_attnums[i]);
- Oid fk_type = RIAttType(fk_rel, riinfo->fk_attnums[i]);
- Oid pk_coll = RIAttCollation(pk_rel, riinfo->pk_attnums[i]);
- Oid fk_coll = RIAttCollation(fk_rel, riinfo->fk_attnums[i]);
+ Oid pk_type = RIAttType(pk_rel, riinfo->param->pk_attnums[i]);
+ Oid fk_type = RIAttType(fk_rel, riinfo->param->fk_attnums[i]);
+ Oid pk_coll = RIAttCollation(pk_rel, riinfo->param->pk_attnums[i]);
+ Oid fk_coll = RIAttCollation(fk_rel, riinfo->param->fk_attnums[i]);
quoteOneName(attname,
- RIAttName(fk_rel, riinfo->fk_attnums[i]));
+ RIAttName(fk_rel, riinfo->param->fk_attnums[i]));
sprintf(paramname, "$%d", i + 1);
ri_GenerateQual(&querybuf, querysep,
paramname, pk_type,
- riinfo->pf_eq_oprs[i],
+ riinfo->param->pf_eq_oprs[i],
attname, fk_type);
if (pk_coll != fk_coll && !get_collation_isdeterministic(pk_coll))
ri_GenerateQualCollation(&querybuf, pk_coll);
@@ -806,7 +813,7 @@ RI_FKey_cascade_del(PG_FUNCTION_ARGS)
}
/* Prepare and save the plan */
- qplan = ri_PlanCheck(querybuf.data, riinfo->nkeys, queryoids,
+ qplan = ri_PlanCheck(querybuf.data, riinfo->param->nkeys, queryoids,
&qkey, fk_rel, pk_rel);
}
@@ -901,22 +908,22 @@ RI_FKey_cascade_upd(PG_FUNCTION_ARGS)
fk_only, fkrelname);
querysep = "";
qualsep = "WHERE";
- for (int i = 0, j = riinfo->nkeys; i < riinfo->nkeys; i++, j++)
+ for (int i = 0, j = riinfo->param->nkeys; i < riinfo->param->nkeys; i++, j++)
{
- Oid pk_type = RIAttType(pk_rel, riinfo->pk_attnums[i]);
- Oid fk_type = RIAttType(fk_rel, riinfo->fk_attnums[i]);
- Oid pk_coll = RIAttCollation(pk_rel, riinfo->pk_attnums[i]);
- Oid fk_coll = RIAttCollation(fk_rel, riinfo->fk_attnums[i]);
+ Oid pk_type = RIAttType(pk_rel, riinfo->param->pk_attnums[i]);
+ Oid fk_type = RIAttType(fk_rel, riinfo->param->fk_attnums[i]);
+ Oid pk_coll = RIAttCollation(pk_rel, riinfo->param->pk_attnums[i]);
+ Oid fk_coll = RIAttCollation(fk_rel, riinfo->param->fk_attnums[i]);
quoteOneName(attname,
- RIAttName(fk_rel, riinfo->fk_attnums[i]));
+ RIAttName(fk_rel, riinfo->param->fk_attnums[i]));
appendStringInfo(&querybuf,
"%s %s = $%d",
querysep, attname, i + 1);
sprintf(paramname, "$%d", j + 1);
ri_GenerateQual(&qualbuf, qualsep,
paramname, pk_type,
- riinfo->pf_eq_oprs[i],
+ riinfo->param->pf_eq_oprs[i],
attname, fk_type);
if (pk_coll != fk_coll && !get_collation_isdeterministic(pk_coll))
ri_GenerateQualCollation(&querybuf, pk_coll);
@@ -928,7 +935,7 @@ RI_FKey_cascade_upd(PG_FUNCTION_ARGS)
appendBinaryStringInfo(&querybuf, qualbuf.data, qualbuf.len);
/* Prepare and save the plan */
- qplan = ri_PlanCheck(querybuf.data, riinfo->nkeys * 2, queryoids,
+ qplan = ri_PlanCheck(querybuf.data, riinfo->param->nkeys * 2, queryoids,
&qkey, fk_rel, pk_rel);
}
@@ -1080,15 +1087,15 @@ ri_set(TriggerData *trigdata, bool is_set_null)
fk_only, fkrelname);
querysep = "";
qualsep = "WHERE";
- for (int i = 0; i < riinfo->nkeys; i++)
+ for (int i = 0; i < riinfo->param->nkeys; i++)
{
- Oid pk_type = RIAttType(pk_rel, riinfo->pk_attnums[i]);
- Oid fk_type = RIAttType(fk_rel, riinfo->fk_attnums[i]);
- Oid pk_coll = RIAttCollation(pk_rel, riinfo->pk_attnums[i]);
- Oid fk_coll = RIAttCollation(fk_rel, riinfo->fk_attnums[i]);
+ Oid pk_type = RIAttType(pk_rel, riinfo->param->pk_attnums[i]);
+ Oid fk_type = RIAttType(fk_rel, riinfo->param->fk_attnums[i]);
+ Oid pk_coll = RIAttCollation(pk_rel, riinfo->param->pk_attnums[i]);
+ Oid fk_coll = RIAttCollation(fk_rel, riinfo->param->fk_attnums[i]);
quoteOneName(attname,
- RIAttName(fk_rel, riinfo->fk_attnums[i]));
+ RIAttName(fk_rel, riinfo->param->fk_attnums[i]));
appendStringInfo(&querybuf,
"%s %s = %s",
querysep, attname,
@@ -1096,7 +1103,7 @@ ri_set(TriggerData *trigdata, bool is_set_null)
sprintf(paramname, "$%d", i + 1);
ri_GenerateQual(&qualbuf, qualsep,
paramname, pk_type,
- riinfo->pf_eq_oprs[i],
+ riinfo->param->pf_eq_oprs[i],
attname, fk_type);
if (pk_coll != fk_coll && !get_collation_isdeterministic(pk_coll))
ri_GenerateQualCollation(&querybuf, pk_coll);
@@ -1107,7 +1114,7 @@ ri_set(TriggerData *trigdata, bool is_set_null)
appendBinaryStringInfo(&querybuf, qualbuf.data, qualbuf.len);
/* Prepare and save the plan */
- qplan = ri_PlanCheck(querybuf.data, riinfo->nkeys, queryoids,
+ qplan = ri_PlanCheck(querybuf.data, riinfo->param->nkeys, queryoids,
&qkey, fk_rel, pk_rel);
}
@@ -1217,7 +1224,7 @@ RI_FKey_fk_upd_check_required(Trigger *trigger, Relation fk_rel,
*/
else if (ri_nullcheck == RI_KEYS_SOME_NULL)
{
- switch (riinfo->confmatchtype)
+ switch (riinfo->param->confmatchtype)
{
case FKCONSTR_MATCH_SIMPLE:
@@ -1333,14 +1340,14 @@ RI_Initial_Check(Trigger *trigger, Relation fk_rel, Relation pk_rel)
fkrte->rellockmode = AccessShareLock;
fkrte->requiredPerms = ACL_SELECT;
- for (int i = 0; i < riinfo->nkeys; i++)
+ for (int i = 0; i < riinfo->param->nkeys; i++)
{
int attno;
- attno = riinfo->pk_attnums[i] - FirstLowInvalidHeapAttributeNumber;
+ attno = riinfo->param->pk_attnums[i] - FirstLowInvalidHeapAttributeNumber;
pkrte->selectedCols = bms_add_member(pkrte->selectedCols, attno);
- attno = riinfo->fk_attnums[i] - FirstLowInvalidHeapAttributeNumber;
+ attno = riinfo->param->fk_attnums[i] - FirstLowInvalidHeapAttributeNumber;
fkrte->selectedCols = bms_add_member(fkrte->selectedCols, attno);
}
@@ -1377,10 +1384,10 @@ RI_Initial_Check(Trigger *trigger, Relation fk_rel, Relation pk_rel)
initStringInfo(&querybuf);
appendStringInfoString(&querybuf, "SELECT ");
sep = "";
- for (int i = 0; i < riinfo->nkeys; i++)
+ for (int i = 0; i < riinfo->param->nkeys; i++)
{
quoteOneName(fkattname,
- RIAttName(fk_rel, riinfo->fk_attnums[i]));
+ RIAttName(fk_rel, riinfo->param->fk_attnums[i]));
appendStringInfo(&querybuf, "%sfk.%s", sep, fkattname);
sep = ", ";
}
@@ -1398,20 +1405,20 @@ RI_Initial_Check(Trigger *trigger, Relation fk_rel, Relation pk_rel)
strcpy(pkattname, "pk.");
strcpy(fkattname, "fk.");
sep = "(";
- for (int i = 0; i < riinfo->nkeys; i++)
+ for (int i = 0; i < riinfo->param->nkeys; i++)
{
- Oid pk_type = RIAttType(pk_rel, riinfo->pk_attnums[i]);
- Oid fk_type = RIAttType(fk_rel, riinfo->fk_attnums[i]);
- Oid pk_coll = RIAttCollation(pk_rel, riinfo->pk_attnums[i]);
- Oid fk_coll = RIAttCollation(fk_rel, riinfo->fk_attnums[i]);
+ Oid pk_type = RIAttType(pk_rel, riinfo->param->pk_attnums[i]);
+ Oid fk_type = RIAttType(fk_rel, riinfo->param->fk_attnums[i]);
+ Oid pk_coll = RIAttCollation(pk_rel, riinfo->param->pk_attnums[i]);
+ Oid fk_coll = RIAttCollation(fk_rel, riinfo->param->fk_attnums[i]);
quoteOneName(pkattname + 3,
- RIAttName(pk_rel, riinfo->pk_attnums[i]));
+ RIAttName(pk_rel, riinfo->param->pk_attnums[i]));
quoteOneName(fkattname + 3,
- RIAttName(fk_rel, riinfo->fk_attnums[i]));
+ RIAttName(fk_rel, riinfo->param->fk_attnums[i]));
ri_GenerateQual(&querybuf, sep,
pkattname, pk_type,
- riinfo->pf_eq_oprs[i],
+ riinfo->param->pf_eq_oprs[i],
fkattname, fk_type);
if (pk_coll != fk_coll)
ri_GenerateQualCollation(&querybuf, pk_coll);
@@ -1422,17 +1429,17 @@ RI_Initial_Check(Trigger *trigger, Relation fk_rel, Relation pk_rel)
* It's sufficient to test any one pk attribute for null to detect a join
* failure.
*/
- quoteOneName(pkattname, RIAttName(pk_rel, riinfo->pk_attnums[0]));
+ quoteOneName(pkattname, RIAttName(pk_rel, riinfo->param->pk_attnums[0]));
appendStringInfo(&querybuf, ") WHERE pk.%s IS NULL AND (", pkattname);
sep = "";
- for (int i = 0; i < riinfo->nkeys; i++)
+ for (int i = 0; i < riinfo->param->nkeys; i++)
{
- quoteOneName(fkattname, RIAttName(fk_rel, riinfo->fk_attnums[i]));
+ quoteOneName(fkattname, RIAttName(fk_rel, riinfo->param->fk_attnums[i]));
appendStringInfo(&querybuf,
"%sfk.%s IS NOT NULL",
sep, fkattname);
- switch (riinfo->confmatchtype)
+ switch (riinfo->param->confmatchtype)
{
case FKCONSTR_MATCH_SIMPLE:
sep = " AND ";
@@ -1505,7 +1512,10 @@ RI_Initial_Check(Trigger *trigger, Relation fk_rel, Relation pk_rel)
HeapTuple tuple = SPI_tuptable->vals[0];
TupleDesc tupdesc = SPI_tuptable->tupdesc;
RI_ConstraintInfo fake_riinfo;
+ RI_ConstraintParam fake_riparam;
+ fake_riinfo.param = &fake_riparam;
+
slot = MakeSingleTupleTableSlot(tupdesc, &TTSOpsVirtual);
heap_deform_tuple(tuple, tupdesc,
@@ -1522,15 +1532,15 @@ RI_Initial_Check(Trigger *trigger, Relation fk_rel, Relation pk_rel)
* or fk_rel's tupdesc.
*/
memcpy(&fake_riinfo, riinfo, sizeof(RI_ConstraintInfo));
- for (int i = 0; i < fake_riinfo.nkeys; i++)
- fake_riinfo.fk_attnums[i] = i + 1;
+ for (int i = 0; i < fake_riinfo.param->nkeys; i++)
+ fake_riinfo.param->fk_attnums[i] = i + 1;
/*
* If it's MATCH FULL, and there are any nulls in the FK keys,
* complain about that rather than the lack of a match. MATCH FULL
* disallows partially-null FK rows.
*/
- if (fake_riinfo.confmatchtype == FKCONSTR_MATCH_FULL &&
+ if (fake_riinfo.param->confmatchtype == FKCONSTR_MATCH_FULL &&
ri_NullCheck(tupdesc, slot, &fake_riinfo, false) != RI_KEYS_NONE_NULL)
ereport(ERROR,
(errcode(ERRCODE_FOREIGN_KEY_VIOLATION),
@@ -1615,10 +1625,10 @@ RI_PartitionRemove_Check(Trigger *trigger, Relation fk_rel, Relation pk_rel)
initStringInfo(&querybuf);
appendStringInfoString(&querybuf, "SELECT ");
sep = "";
- for (i = 0; i < riinfo->nkeys; i++)
+ for (i = 0; i < riinfo->param->nkeys; i++)
{
quoteOneName(fkattname,
- RIAttName(fk_rel, riinfo->fk_attnums[i]));
+ RIAttName(fk_rel, riinfo->param->fk_attnums[i]));
appendStringInfo(&querybuf, "%sfk.%s", sep, fkattname);
sep = ", ";
}
@@ -1633,20 +1643,20 @@ RI_PartitionRemove_Check(Trigger *trigger, Relation fk_rel, Relation pk_rel)
strcpy(pkattname, "pk.");
strcpy(fkattname, "fk.");
sep = "(";
- for (i = 0; i < riinfo->nkeys; i++)
+ for (i = 0; i < riinfo->param->nkeys; i++)
{
- Oid pk_type = RIAttType(pk_rel, riinfo->pk_attnums[i]);
- Oid fk_type = RIAttType(fk_rel, riinfo->fk_attnums[i]);
- Oid pk_coll = RIAttCollation(pk_rel, riinfo->pk_attnums[i]);
- Oid fk_coll = RIAttCollation(fk_rel, riinfo->fk_attnums[i]);
+ Oid pk_type = RIAttType(pk_rel, riinfo->param->pk_attnums[i]);
+ Oid fk_type = RIAttType(fk_rel, riinfo->param->fk_attnums[i]);
+ Oid pk_coll = RIAttCollation(pk_rel, riinfo->param->pk_attnums[i]);
+ Oid fk_coll = RIAttCollation(fk_rel, riinfo->param->fk_attnums[i]);
quoteOneName(pkattname + 3,
- RIAttName(pk_rel, riinfo->pk_attnums[i]));
+ RIAttName(pk_rel, riinfo->param->pk_attnums[i]));
quoteOneName(fkattname + 3,
- RIAttName(fk_rel, riinfo->fk_attnums[i]));
+ RIAttName(fk_rel, riinfo->param->fk_attnums[i]));
ri_GenerateQual(&querybuf, sep,
pkattname, pk_type,
- riinfo->pf_eq_oprs[i],
+ riinfo->param->pf_eq_oprs[i],
fkattname, fk_type);
if (pk_coll != fk_coll)
ri_GenerateQualCollation(&querybuf, pk_coll);
@@ -1666,13 +1676,13 @@ RI_PartitionRemove_Check(Trigger *trigger, Relation fk_rel, Relation pk_rel)
appendStringInfoString(&querybuf, ") WHERE (");
sep = "";
- for (i = 0; i < riinfo->nkeys; i++)
+ for (i = 0; i < riinfo->param->nkeys; i++)
{
- quoteOneName(fkattname, RIAttName(fk_rel, riinfo->fk_attnums[i]));
+ quoteOneName(fkattname, RIAttName(fk_rel, riinfo->param->fk_attnums[i]));
appendStringInfo(&querybuf,
"%sfk.%s IS NOT NULL",
sep, fkattname);
- switch (riinfo->confmatchtype)
+ switch (riinfo->param->confmatchtype)
{
case FKCONSTR_MATCH_SIMPLE:
sep = " AND ";
@@ -1762,8 +1772,8 @@ RI_PartitionRemove_Check(Trigger *trigger, Relation fk_rel, Relation pk_rel)
* or fk_rel's tupdesc.
*/
memcpy(&fake_riinfo, riinfo, sizeof(RI_ConstraintInfo));
- for (i = 0; i < fake_riinfo.nkeys; i++)
- fake_riinfo.pk_attnums[i] = i + 1;
+ for (i = 0; i < fake_riinfo.param->nkeys; i++)
+ fake_riinfo.param->pk_attnums[i] = i + 1;
ri_ReportViolation(&fake_riinfo, pk_rel, fk_rel,
slot, tupdesc, 0, true);
@@ -1905,7 +1915,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->constraint_id;
+ key->constr_id = riinfo->param->query_key;
key->constr_queryno = constr_queryno;
}
@@ -1983,25 +1993,25 @@ ri_FetchConstraintInfo(Trigger *trigger, Relation trig_rel, bool rel_is_pk)
if (rel_is_pk)
{
if (riinfo->fk_relid != trigger->tgconstrrelid ||
- riinfo->pk_relid != RelationGetRelid(trig_rel))
+ riinfo->param->pk_relid != RelationGetRelid(trig_rel))
elog(ERROR, "wrong pg_constraint entry for trigger \"%s\" on table \"%s\"",
trigger->tgname, RelationGetRelationName(trig_rel));
}
else
{
if (riinfo->fk_relid != RelationGetRelid(trig_rel) ||
- riinfo->pk_relid != trigger->tgconstrrelid)
+ riinfo->param->pk_relid != trigger->tgconstrrelid)
elog(ERROR, "wrong pg_constraint entry for trigger \"%s\" on table \"%s\"",
trigger->tgname, RelationGetRelationName(trig_rel));
}
- if (riinfo->confmatchtype != FKCONSTR_MATCH_FULL &&
- riinfo->confmatchtype != FKCONSTR_MATCH_PARTIAL &&
- riinfo->confmatchtype != FKCONSTR_MATCH_SIMPLE)
+ if (riinfo->param->confmatchtype != FKCONSTR_MATCH_FULL &&
+ riinfo->param->confmatchtype != FKCONSTR_MATCH_PARTIAL &&
+ riinfo->param->confmatchtype != FKCONSTR_MATCH_SIMPLE)
elog(ERROR, "unrecognized confmatchtype: %d",
- riinfo->confmatchtype);
+ riinfo->param->confmatchtype);
- if (riinfo->confmatchtype == FKCONSTR_MATCH_PARTIAL)
+ if (riinfo->param->confmatchtype == FKCONSTR_MATCH_PARTIAL)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("MATCH PARTIAL not yet implemented")));
@@ -2009,6 +2019,27 @@ ri_FetchConstraintInfo(Trigger *trigger, Relation trig_rel, bool rel_is_pk)
return riinfo;
}
+/*
+ * get_ri_constraint_root
+ * Returns a given RI constraint's root parent
+ */
+static Oid
+get_ri_constraint_root(Oid constrOid)
+{
+ HeapTuple tuple;
+ Oid constrParentOid;
+
+ tuple = SearchSysCache1(CONSTROID, ObjectIdGetDatum(constrOid));
+ if (!HeapTupleIsValid(tuple))
+ elog(ERROR, "cache lookup failed for constraint %u", constrOid);
+ constrParentOid = ((Form_pg_constraint) GETSTRUCT(tuple))->conparentid;
+ ReleaseSysCache(tuple);
+ if (OidIsValid(constrParentOid))
+ return get_ri_constraint_root(constrParentOid);
+
+ return constrOid;
+}
+
/*
* Fetch or create the RI_ConstraintInfo struct for an FK constraint.
*/
@@ -2032,11 +2063,20 @@ ri_LoadConstraintInfo(Oid constraintOid)
riinfo = (RI_ConstraintInfo *) hash_search(ri_constraint_cache,
(void *) &constraintOid,
HASH_ENTER, &found);
+
if (!found)
riinfo->valid = false;
else if (riinfo->valid)
return riinfo;
+ /* allocate riinfo's own param memory if needed */
+ if (!found || riinfo->param->ownerinfo != riinfo)
+ {
+ riinfo->param = (RI_ConstraintParam *)
+ MemoryContextAlloc(TopMemoryContext, sizeof(RI_ConstraintParam));
+ riinfo->param->ownerinfo = riinfo;
+ }
+
/*
* Fetch the pg_constraint row so we can fill in the entry.
*/
@@ -2051,22 +2091,23 @@ ri_LoadConstraintInfo(Oid constraintOid)
/* And extract data */
Assert(riinfo->constraint_id == constraintOid);
+ riinfo->param->query_key = get_ri_constraint_root(riinfo->constraint_id);
riinfo->oidHashValue = GetSysCacheHashValue1(CONSTROID,
ObjectIdGetDatum(constraintOid));
memcpy(&riinfo->conname, &conForm->conname, sizeof(NameData));
- riinfo->pk_relid = conForm->confrelid;
+ riinfo->param->pk_relid = conForm->confrelid;
riinfo->fk_relid = conForm->conrelid;
- riinfo->confupdtype = conForm->confupdtype;
- riinfo->confdeltype = conForm->confdeltype;
- riinfo->confmatchtype = conForm->confmatchtype;
+ riinfo->param->confupdtype = conForm->confupdtype;
+ riinfo->param->confdeltype = conForm->confdeltype;
+ riinfo->param->confmatchtype = conForm->confmatchtype;
DeconstructFkConstraintRow(tup,
- &riinfo->nkeys,
- riinfo->fk_attnums,
- riinfo->pk_attnums,
- riinfo->pf_eq_oprs,
- riinfo->pp_eq_oprs,
- riinfo->ff_eq_oprs);
+ &riinfo->param->nkeys,
+ riinfo->param->fk_attnums,
+ riinfo->param->pk_attnums,
+ riinfo->param->pf_eq_oprs,
+ riinfo->param->pp_eq_oprs,
+ riinfo->param->ff_eq_oprs);
ReleaseSysCache(tup);
@@ -2227,7 +2268,7 @@ ri_PerformCheck(const RI_ConstraintInfo *riinfo,
vals, nulls);
if (oldslot)
ri_ExtractValues(source_rel, oldslot, riinfo, source_is_pk,
- vals + riinfo->nkeys, nulls + riinfo->nkeys);
+ vals + riinfo->param->nkeys, nulls + riinfo->param->nkeys);
}
else
{
@@ -2320,11 +2361,11 @@ ri_ExtractValues(Relation rel, TupleTableSlot *slot,
bool isnull;
if (rel_is_pk)
- attnums = riinfo->pk_attnums;
+ attnums = riinfo->param->pk_attnums;
else
- attnums = riinfo->fk_attnums;
+ attnums = riinfo->param->fk_attnums;
- for (int i = 0; i < riinfo->nkeys; i++)
+ for (int i = 0; i < riinfo->param->nkeys; i++)
{
vals[i] = slot_getattr(slot, attnums[i], &isnull);
nulls[i] = isnull ? 'n' : ' ';
@@ -2361,14 +2402,14 @@ ri_ReportViolation(const RI_ConstraintInfo *riinfo,
onfk = (queryno == RI_PLAN_CHECK_LOOKUPPK);
if (onfk)
{
- attnums = riinfo->fk_attnums;
+ attnums = riinfo->param->fk_attnums;
rel_oid = fk_rel->rd_id;
if (tupdesc == NULL)
tupdesc = fk_rel->rd_att;
}
else
{
- attnums = riinfo->pk_attnums;
+ attnums = riinfo->param->pk_attnums;
rel_oid = pk_rel->rd_id;
if (tupdesc == NULL)
tupdesc = pk_rel->rd_att;
@@ -2395,7 +2436,7 @@ ri_ReportViolation(const RI_ConstraintInfo *riinfo,
if (aclresult != ACLCHECK_OK)
{
/* Try for column-level permissions */
- for (int idx = 0; idx < riinfo->nkeys; idx++)
+ for (int idx = 0; idx < riinfo->param->nkeys; idx++)
{
aclresult = pg_attribute_aclcheck(rel_oid, attnums[idx],
GetUserId(),
@@ -2418,7 +2459,7 @@ ri_ReportViolation(const RI_ConstraintInfo *riinfo,
/* Get printable versions of the keys involved */
initStringInfo(&key_names);
initStringInfo(&key_values);
- for (int idx = 0; idx < riinfo->nkeys; idx++)
+ for (int idx = 0; idx < riinfo->param->nkeys; idx++)
{
int fnum = attnums[idx];
Form_pg_attribute att = TupleDescAttr(tupdesc, fnum - 1);
@@ -2508,11 +2549,11 @@ ri_NullCheck(TupleDesc tupDesc,
bool nonenull = true;
if (rel_is_pk)
- attnums = riinfo->pk_attnums;
+ attnums = riinfo->param->pk_attnums;
else
- attnums = riinfo->fk_attnums;
+ attnums = riinfo->param->fk_attnums;
- for (int i = 0; i < riinfo->nkeys; i++)
+ for (int i = 0; i < riinfo->param->nkeys; i++)
{
if (slot_attisnull(slot, attnums[i]))
nonenull = false;
@@ -2667,12 +2708,12 @@ ri_KeysEqual(Relation rel, TupleTableSlot *oldslot, TupleTableSlot *newslot,
const int16 *attnums;
if (rel_is_pk)
- attnums = riinfo->pk_attnums;
+ attnums = riinfo->param->pk_attnums;
else
- attnums = riinfo->fk_attnums;
+ attnums = riinfo->param->fk_attnums;
/* XXX: could be worthwhile to fetch all necessary attrs at once */
- for (int i = 0; i < riinfo->nkeys; i++)
+ for (int i = 0; i < riinfo->param->nkeys; i++)
{
Datum oldvalue;
Datum newvalue;
@@ -2714,7 +2755,7 @@ ri_KeysEqual(Relation rel, TupleTableSlot *oldslot, TupleTableSlot *newslot,
* operator. Changes that compare equal will still satisfy the
* constraint after the update.
*/
- if (!ri_AttributesEqual(riinfo->ff_eq_oprs[i], RIAttType(rel, attnums[i]),
+ if (!ri_AttributesEqual(riinfo->param->ff_eq_oprs[i], RIAttType(rel, attnums[i]),
oldvalue, newvalue))
return false;
}
--
2.18.4
From ae7f7ab7df353044b8b2878253d1bdb8adbcd054 Mon Sep 17 00:00:00 2001
From: Kyotaro Horiguchi <horikyoga.ntt@gmail.com>
Date: Thu, 3 Dec 2020 16:51:07 +0900
Subject: [PATCH 2/2] share param part of riinfo
---
src/backend/utils/adt/ri_triggers.c | 33 +++++++++++++++++++++++++++++
1 file changed, 33 insertions(+)
diff --git a/src/backend/utils/adt/ri_triggers.c b/src/backend/utils/adt/ri_triggers.c
index 3f8407c311..080e8af1a5 100644
--- a/src/backend/utils/adt/ri_triggers.c
+++ b/src/backend/utils/adt/ri_triggers.c
@@ -110,6 +110,8 @@ typedef struct RI_ConstraintParam
Oid pf_eq_oprs[RI_MAX_NUMKEYS]; /* equality operators (PK = FK) */
Oid pp_eq_oprs[RI_MAX_NUMKEYS]; /* equality operators (PK = PK) */
Oid ff_eq_oprs[RI_MAX_NUMKEYS]; /* equality operators (FK = FK) */
+
+ /* This should follow the aboves, see ri_LoadConstraintInfo */
struct RI_ConstraintInfo *ownerinfo; /* owner info of this param */
} RI_ConstraintParam;
@@ -2111,6 +2113,37 @@ ri_LoadConstraintInfo(Oid constraintOid)
ReleaseSysCache(tup);
+ if (OidIsValid(conForm->conparentid))
+ {
+ Oid pconid = conForm->conparentid;
+ const RI_ConstraintInfo *priinfo = ri_LoadConstraintInfo(pconid);
+ RI_ConstraintParam *p = riinfo->param;
+ RI_ConstraintParam *pp = priinfo->param;
+
+ /* share the same parameters if identical */
+ if (memcmp(p, pp, offsetof(RI_ConstraintParam, pk_attnums)) == 0)
+ {
+ int i;
+
+ for (i = 0 ; i < p->nkeys ; i++)
+ {
+ if (p->pk_attnums[i] != pp->pk_attnums[i] ||
+ p->fk_attnums[i] != pp->fk_attnums[i] ||
+ p->pf_eq_oprs[i] != pp->pf_eq_oprs[i] ||
+ p->pp_eq_oprs[i] != pp->pp_eq_oprs[i] ||
+ p->ff_eq_oprs[i] != pp->ff_eq_oprs[i])
+ break;
+ }
+ if (i == p->nkeys)
+ {
+ /* this riinfo can share the parameters with parent */
+ Assert(p->ownerinfo == riinfo);
+ pfree(riinfo->param);
+ riinfo->param = pp;
+ }
+ }
+ }
+
/*
* For efficient processing of invalidation messages below, we keep a
* doubly-linked list, and a count, of all currently valid entries.
--
2.18.4
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
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
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, perhapsreplaced 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
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!
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
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
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
From 55946e09d33fc7fa43bed04ef548bf8a3f67155d Mon Sep 17 00:00:00 2001
From: Kyotaro Horiguchi <horikyoga.ntt@gmail.com>
Date: Thu, 3 Dec 2020 16:15:57 +0900
Subject: [PATCH 1/2] separte riinfo
---
src/backend/utils/adt/ri_triggers.c | 290 ++++++++++++++++------------
1 file changed, 168 insertions(+), 122 deletions(-)
diff --git a/src/backend/utils/adt/ri_triggers.c b/src/backend/utils/adt/ri_triggers.c
index 02b1a3868f..0306bf7739 100644
--- a/src/backend/utils/adt/ri_triggers.c
+++ b/src/backend/utils/adt/ri_triggers.c
@@ -97,14 +97,29 @@
* Information extracted from an FK pg_constraint entry. This is cached in
* ri_constraint_cache.
*/
+struct RI_ConstraintParam;
+
typedef struct RI_ConstraintInfo
{
- Oid constraint_id; /* OID of pg_constraint entry (hash key) */
+ Oid constraint_id;
bool valid; /* successfully initialized? */
uint32 oidHashValue; /* hash value of pg_constraint OID */
NameData conname; /* name of the FK constraint */
- Oid pk_relid; /* referenced relation */
Oid fk_relid; /* referencing relation */
+ struct RI_ConstraintParam *param; /* sharable part */
+ dlist_node valid_link; /* Link in list of valid entries */
+} RI_ConstraintInfo;
+
+/*
+ * RI_ConstraintParam
+ *
+ * The part sharable among relations in a partitioned table of the cached
+ * constraint information.
+ */
+typedef struct RI_ConstraintParam
+{
+ /* begin with identity members */
+ Oid pk_relid; /* referenced relation */
char confupdtype; /* foreign key's ON UPDATE action */
char confdeltype; /* foreign key's ON DELETE action */
char confmatchtype; /* foreign key's match type */
@@ -114,8 +129,12 @@ typedef struct RI_ConstraintInfo
Oid pf_eq_oprs[RI_MAX_NUMKEYS]; /* equality operators (PK = FK) */
Oid pp_eq_oprs[RI_MAX_NUMKEYS]; /* equality operators (PK = PK) */
Oid ff_eq_oprs[RI_MAX_NUMKEYS]; /* equality operators (FK = FK) */
- dlist_node valid_link; /* Link in list of valid entries */
-} RI_ConstraintInfo;
+
+ /* These should be at the end of struct, see ri_LoadConstraintInfo */
+ Oid query_key; /* key for planned statment */
+ RI_ConstraintInfo *ownerinfo; /* owner RI_ConstraintInfo of this param */
+} RI_ConstraintParam;
+
/*
* RI_QueryKey
@@ -163,6 +182,7 @@ typedef struct RI_CompareHashEntry
/*
* Local data
*/
+static MemoryContext ri_constraint_cache_cxt = NULL;
static HTAB *ri_constraint_cache = NULL;
static HTAB *ri_query_cache = NULL;
static HTAB *ri_compare_cache = NULL;
@@ -264,7 +284,7 @@ RI_FKey_check(TriggerData *trigdata)
* SELECT FOR KEY SHARE will get on it.
*/
fk_rel = trigdata->tg_relation;
- pk_rel = table_open(riinfo->pk_relid, RowShareLock);
+ pk_rel = table_open(riinfo->param->pk_relid, RowShareLock);
switch (ri_NullCheck(RelationGetDescr(fk_rel), newslot, riinfo, false))
{
@@ -283,7 +303,7 @@ RI_FKey_check(TriggerData *trigdata)
* This is the only case that differs between the three kinds of
* MATCH.
*/
- switch (riinfo->confmatchtype)
+ switch (riinfo->param->confmatchtype)
{
case FKCONSTR_MATCH_FULL:
@@ -364,17 +384,17 @@ RI_FKey_check(TriggerData *trigdata)
appendStringInfo(&querybuf, "SELECT 1 FROM %s%s x",
pk_only, pkrelname);
querysep = "WHERE";
- for (int i = 0; i < riinfo->nkeys; i++)
+ for (int i = 0; i < riinfo->param->nkeys; i++)
{
- Oid pk_type = RIAttType(pk_rel, riinfo->pk_attnums[i]);
- Oid fk_type = RIAttType(fk_rel, riinfo->fk_attnums[i]);
+ Oid pk_type = RIAttType(pk_rel, riinfo->param->pk_attnums[i]);
+ Oid fk_type = RIAttType(fk_rel, riinfo->param->fk_attnums[i]);
quoteOneName(attname,
- RIAttName(pk_rel, riinfo->pk_attnums[i]));
+ RIAttName(pk_rel, riinfo->param->pk_attnums[i]));
sprintf(paramname, "$%d", i + 1);
ri_GenerateQual(&querybuf, querysep,
attname, pk_type,
- riinfo->pf_eq_oprs[i],
+ riinfo->param->pf_eq_oprs[i],
paramname, fk_type);
querysep = "AND";
queryoids[i] = fk_type;
@@ -382,7 +402,7 @@ RI_FKey_check(TriggerData *trigdata)
appendStringInfoString(&querybuf, " FOR KEY SHARE OF x");
/* Prepare and save the plan */
- qplan = ri_PlanCheck(querybuf.data, riinfo->nkeys, queryoids,
+ qplan = ri_PlanCheck(querybuf.data, riinfo->param->nkeys, queryoids,
&qkey, fk_rel, pk_rel);
}
@@ -492,16 +512,16 @@ ri_Check_Pk_Match(Relation pk_rel, Relation fk_rel,
appendStringInfo(&querybuf, "SELECT 1 FROM %s%s x",
pk_only, pkrelname);
querysep = "WHERE";
- for (int i = 0; i < riinfo->nkeys; i++)
+ for (int i = 0; i < riinfo->param->nkeys; i++)
{
- Oid pk_type = RIAttType(pk_rel, riinfo->pk_attnums[i]);
+ Oid pk_type = RIAttType(pk_rel, riinfo->param->pk_attnums[i]);
quoteOneName(attname,
- RIAttName(pk_rel, riinfo->pk_attnums[i]));
+ RIAttName(pk_rel, riinfo->param->pk_attnums[i]));
sprintf(paramname, "$%d", i + 1);
ri_GenerateQual(&querybuf, querysep,
attname, pk_type,
- riinfo->pp_eq_oprs[i],
+ riinfo->param->pp_eq_oprs[i],
paramname, pk_type);
querysep = "AND";
queryoids[i] = pk_type;
@@ -509,7 +529,7 @@ ri_Check_Pk_Match(Relation pk_rel, Relation fk_rel,
appendStringInfoString(&querybuf, " FOR KEY SHARE OF x");
/* Prepare and save the plan */
- qplan = ri_PlanCheck(querybuf.data, riinfo->nkeys, queryoids,
+ qplan = ri_PlanCheck(querybuf.data, riinfo->param->nkeys, queryoids,
&qkey, fk_rel, pk_rel);
}
@@ -679,19 +699,19 @@ ri_restrict(TriggerData *trigdata, bool is_no_action)
appendStringInfo(&querybuf, "SELECT 1 FROM %s%s x",
fk_only, fkrelname);
querysep = "WHERE";
- for (int i = 0; i < riinfo->nkeys; i++)
+ for (int i = 0; i < riinfo->param->nkeys; i++)
{
- Oid pk_type = RIAttType(pk_rel, riinfo->pk_attnums[i]);
- Oid fk_type = RIAttType(fk_rel, riinfo->fk_attnums[i]);
- Oid pk_coll = RIAttCollation(pk_rel, riinfo->pk_attnums[i]);
- Oid fk_coll = RIAttCollation(fk_rel, riinfo->fk_attnums[i]);
+ Oid pk_type = RIAttType(pk_rel, riinfo->param->pk_attnums[i]);
+ Oid fk_type = RIAttType(fk_rel, riinfo->param->fk_attnums[i]);
+ Oid pk_coll = RIAttCollation(pk_rel, riinfo->param->pk_attnums[i]);
+ Oid fk_coll = RIAttCollation(fk_rel, riinfo->param->fk_attnums[i]);
quoteOneName(attname,
- RIAttName(fk_rel, riinfo->fk_attnums[i]));
+ RIAttName(fk_rel, riinfo->param->fk_attnums[i]));
sprintf(paramname, "$%d", i + 1);
ri_GenerateQual(&querybuf, querysep,
paramname, pk_type,
- riinfo->pf_eq_oprs[i],
+ riinfo->param->pf_eq_oprs[i],
attname, fk_type);
if (pk_coll != fk_coll && !get_collation_isdeterministic(pk_coll))
ri_GenerateQualCollation(&querybuf, pk_coll);
@@ -701,7 +721,7 @@ ri_restrict(TriggerData *trigdata, bool is_no_action)
appendStringInfoString(&querybuf, " FOR KEY SHARE OF x");
/* Prepare and save the plan */
- qplan = ri_PlanCheck(querybuf.data, riinfo->nkeys, queryoids,
+ qplan = ri_PlanCheck(querybuf.data, riinfo->param->nkeys, queryoids,
&qkey, fk_rel, pk_rel);
}
@@ -785,19 +805,19 @@ RI_FKey_cascade_del(PG_FUNCTION_ARGS)
appendStringInfo(&querybuf, "DELETE FROM %s%s",
fk_only, fkrelname);
querysep = "WHERE";
- for (int i = 0; i < riinfo->nkeys; i++)
+ for (int i = 0; i < riinfo->param->nkeys; i++)
{
- Oid pk_type = RIAttType(pk_rel, riinfo->pk_attnums[i]);
- Oid fk_type = RIAttType(fk_rel, riinfo->fk_attnums[i]);
- Oid pk_coll = RIAttCollation(pk_rel, riinfo->pk_attnums[i]);
- Oid fk_coll = RIAttCollation(fk_rel, riinfo->fk_attnums[i]);
+ Oid pk_type = RIAttType(pk_rel, riinfo->param->pk_attnums[i]);
+ Oid fk_type = RIAttType(fk_rel, riinfo->param->fk_attnums[i]);
+ Oid pk_coll = RIAttCollation(pk_rel, riinfo->param->pk_attnums[i]);
+ Oid fk_coll = RIAttCollation(fk_rel, riinfo->param->fk_attnums[i]);
quoteOneName(attname,
- RIAttName(fk_rel, riinfo->fk_attnums[i]));
+ RIAttName(fk_rel, riinfo->param->fk_attnums[i]));
sprintf(paramname, "$%d", i + 1);
ri_GenerateQual(&querybuf, querysep,
paramname, pk_type,
- riinfo->pf_eq_oprs[i],
+ riinfo->param->pf_eq_oprs[i],
attname, fk_type);
if (pk_coll != fk_coll && !get_collation_isdeterministic(pk_coll))
ri_GenerateQualCollation(&querybuf, pk_coll);
@@ -806,7 +826,7 @@ RI_FKey_cascade_del(PG_FUNCTION_ARGS)
}
/* Prepare and save the plan */
- qplan = ri_PlanCheck(querybuf.data, riinfo->nkeys, queryoids,
+ qplan = ri_PlanCheck(querybuf.data, riinfo->param->nkeys, queryoids,
&qkey, fk_rel, pk_rel);
}
@@ -901,22 +921,24 @@ RI_FKey_cascade_upd(PG_FUNCTION_ARGS)
fk_only, fkrelname);
querysep = "";
qualsep = "WHERE";
- for (int i = 0, j = riinfo->nkeys; i < riinfo->nkeys; i++, j++)
+ for (int i = 0, j = riinfo->param->nkeys;
+ i < riinfo->param->nkeys;
+ i++, j++)
{
- Oid pk_type = RIAttType(pk_rel, riinfo->pk_attnums[i]);
- Oid fk_type = RIAttType(fk_rel, riinfo->fk_attnums[i]);
- Oid pk_coll = RIAttCollation(pk_rel, riinfo->pk_attnums[i]);
- Oid fk_coll = RIAttCollation(fk_rel, riinfo->fk_attnums[i]);
+ Oid pk_type = RIAttType(pk_rel, riinfo->param->pk_attnums[i]);
+ Oid fk_type = RIAttType(fk_rel, riinfo->param->fk_attnums[i]);
+ Oid pk_coll = RIAttCollation(pk_rel, riinfo->param->pk_attnums[i]);
+ Oid fk_coll = RIAttCollation(fk_rel, riinfo->param->fk_attnums[i]);
quoteOneName(attname,
- RIAttName(fk_rel, riinfo->fk_attnums[i]));
+ RIAttName(fk_rel, riinfo->param->fk_attnums[i]));
appendStringInfo(&querybuf,
"%s %s = $%d",
querysep, attname, i + 1);
sprintf(paramname, "$%d", j + 1);
ri_GenerateQual(&qualbuf, qualsep,
paramname, pk_type,
- riinfo->pf_eq_oprs[i],
+ riinfo->param->pf_eq_oprs[i],
attname, fk_type);
if (pk_coll != fk_coll && !get_collation_isdeterministic(pk_coll))
ri_GenerateQualCollation(&querybuf, pk_coll);
@@ -928,7 +950,7 @@ RI_FKey_cascade_upd(PG_FUNCTION_ARGS)
appendBinaryStringInfo(&querybuf, qualbuf.data, qualbuf.len);
/* Prepare and save the plan */
- qplan = ri_PlanCheck(querybuf.data, riinfo->nkeys * 2, queryoids,
+ qplan = ri_PlanCheck(querybuf.data, riinfo->param->nkeys * 2, queryoids,
&qkey, fk_rel, pk_rel);
}
@@ -1080,15 +1102,15 @@ ri_set(TriggerData *trigdata, bool is_set_null)
fk_only, fkrelname);
querysep = "";
qualsep = "WHERE";
- for (int i = 0; i < riinfo->nkeys; i++)
+ for (int i = 0; i < riinfo->param->nkeys; i++)
{
- Oid pk_type = RIAttType(pk_rel, riinfo->pk_attnums[i]);
- Oid fk_type = RIAttType(fk_rel, riinfo->fk_attnums[i]);
- Oid pk_coll = RIAttCollation(pk_rel, riinfo->pk_attnums[i]);
- Oid fk_coll = RIAttCollation(fk_rel, riinfo->fk_attnums[i]);
+ Oid pk_type = RIAttType(pk_rel, riinfo->param->pk_attnums[i]);
+ Oid fk_type = RIAttType(fk_rel, riinfo->param->fk_attnums[i]);
+ Oid pk_coll = RIAttCollation(pk_rel, riinfo->param->pk_attnums[i]);
+ Oid fk_coll = RIAttCollation(fk_rel, riinfo->param->fk_attnums[i]);
quoteOneName(attname,
- RIAttName(fk_rel, riinfo->fk_attnums[i]));
+ RIAttName(fk_rel, riinfo->param->fk_attnums[i]));
appendStringInfo(&querybuf,
"%s %s = %s",
querysep, attname,
@@ -1096,7 +1118,7 @@ ri_set(TriggerData *trigdata, bool is_set_null)
sprintf(paramname, "$%d", i + 1);
ri_GenerateQual(&qualbuf, qualsep,
paramname, pk_type,
- riinfo->pf_eq_oprs[i],
+ riinfo->param->pf_eq_oprs[i],
attname, fk_type);
if (pk_coll != fk_coll && !get_collation_isdeterministic(pk_coll))
ri_GenerateQualCollation(&querybuf, pk_coll);
@@ -1107,7 +1129,7 @@ ri_set(TriggerData *trigdata, bool is_set_null)
appendBinaryStringInfo(&querybuf, qualbuf.data, qualbuf.len);
/* Prepare and save the plan */
- qplan = ri_PlanCheck(querybuf.data, riinfo->nkeys, queryoids,
+ qplan = ri_PlanCheck(querybuf.data, riinfo->param->nkeys, queryoids,
&qkey, fk_rel, pk_rel);
}
@@ -1217,7 +1239,7 @@ RI_FKey_fk_upd_check_required(Trigger *trigger, Relation fk_rel,
*/
else if (ri_nullcheck == RI_KEYS_SOME_NULL)
{
- switch (riinfo->confmatchtype)
+ switch (riinfo->param->confmatchtype)
{
case FKCONSTR_MATCH_SIMPLE:
@@ -1333,14 +1355,16 @@ RI_Initial_Check(Trigger *trigger, Relation fk_rel, Relation pk_rel)
fkrte->rellockmode = AccessShareLock;
fkrte->requiredPerms = ACL_SELECT;
- for (int i = 0; i < riinfo->nkeys; i++)
+ for (int i = 0; i < riinfo->param->nkeys; i++)
{
int attno;
- attno = riinfo->pk_attnums[i] - FirstLowInvalidHeapAttributeNumber;
+ attno = riinfo->param->pk_attnums[i] -
+ FirstLowInvalidHeapAttributeNumber;
pkrte->selectedCols = bms_add_member(pkrte->selectedCols, attno);
- attno = riinfo->fk_attnums[i] - FirstLowInvalidHeapAttributeNumber;
+ attno = riinfo->param->fk_attnums[i] -
+ FirstLowInvalidHeapAttributeNumber;
fkrte->selectedCols = bms_add_member(fkrte->selectedCols, attno);
}
@@ -1377,10 +1401,10 @@ RI_Initial_Check(Trigger *trigger, Relation fk_rel, Relation pk_rel)
initStringInfo(&querybuf);
appendStringInfoString(&querybuf, "SELECT ");
sep = "";
- for (int i = 0; i < riinfo->nkeys; i++)
+ for (int i = 0; i < riinfo->param->nkeys; i++)
{
quoteOneName(fkattname,
- RIAttName(fk_rel, riinfo->fk_attnums[i]));
+ RIAttName(fk_rel, riinfo->param->fk_attnums[i]));
appendStringInfo(&querybuf, "%sfk.%s", sep, fkattname);
sep = ", ";
}
@@ -1398,20 +1422,20 @@ RI_Initial_Check(Trigger *trigger, Relation fk_rel, Relation pk_rel)
strcpy(pkattname, "pk.");
strcpy(fkattname, "fk.");
sep = "(";
- for (int i = 0; i < riinfo->nkeys; i++)
+ for (int i = 0; i < riinfo->param->nkeys; i++)
{
- Oid pk_type = RIAttType(pk_rel, riinfo->pk_attnums[i]);
- Oid fk_type = RIAttType(fk_rel, riinfo->fk_attnums[i]);
- Oid pk_coll = RIAttCollation(pk_rel, riinfo->pk_attnums[i]);
- Oid fk_coll = RIAttCollation(fk_rel, riinfo->fk_attnums[i]);
+ Oid pk_type = RIAttType(pk_rel, riinfo->param->pk_attnums[i]);
+ Oid fk_type = RIAttType(fk_rel, riinfo->param->fk_attnums[i]);
+ Oid pk_coll = RIAttCollation(pk_rel, riinfo->param->pk_attnums[i]);
+ Oid fk_coll = RIAttCollation(fk_rel, riinfo->param->fk_attnums[i]);
quoteOneName(pkattname + 3,
- RIAttName(pk_rel, riinfo->pk_attnums[i]));
+ RIAttName(pk_rel, riinfo->param->pk_attnums[i]));
quoteOneName(fkattname + 3,
- RIAttName(fk_rel, riinfo->fk_attnums[i]));
+ RIAttName(fk_rel, riinfo->param->fk_attnums[i]));
ri_GenerateQual(&querybuf, sep,
pkattname, pk_type,
- riinfo->pf_eq_oprs[i],
+ riinfo->param->pf_eq_oprs[i],
fkattname, fk_type);
if (pk_coll != fk_coll)
ri_GenerateQualCollation(&querybuf, pk_coll);
@@ -1422,17 +1446,17 @@ RI_Initial_Check(Trigger *trigger, Relation fk_rel, Relation pk_rel)
* It's sufficient to test any one pk attribute for null to detect a join
* failure.
*/
- quoteOneName(pkattname, RIAttName(pk_rel, riinfo->pk_attnums[0]));
+ quoteOneName(pkattname, RIAttName(pk_rel, riinfo->param->pk_attnums[0]));
appendStringInfo(&querybuf, ") WHERE pk.%s IS NULL AND (", pkattname);
sep = "";
- for (int i = 0; i < riinfo->nkeys; i++)
+ for (int i = 0; i < riinfo->param->nkeys; i++)
{
- quoteOneName(fkattname, RIAttName(fk_rel, riinfo->fk_attnums[i]));
+ quoteOneName(fkattname, RIAttName(fk_rel, riinfo->param->fk_attnums[i]));
appendStringInfo(&querybuf,
"%sfk.%s IS NOT NULL",
sep, fkattname);
- switch (riinfo->confmatchtype)
+ switch (riinfo->param->confmatchtype)
{
case FKCONSTR_MATCH_SIMPLE:
sep = " AND ";
@@ -1505,6 +1529,9 @@ RI_Initial_Check(Trigger *trigger, Relation fk_rel, Relation pk_rel)
HeapTuple tuple = SPI_tuptable->vals[0];
TupleDesc tupdesc = SPI_tuptable->tupdesc;
RI_ConstraintInfo fake_riinfo;
+ RI_ConstraintParam fake_riparam;
+
+ fake_riinfo.param = &fake_riparam;
slot = MakeSingleTupleTableSlot(tupdesc, &TTSOpsVirtual);
@@ -1522,15 +1549,15 @@ RI_Initial_Check(Trigger *trigger, Relation fk_rel, Relation pk_rel)
* or fk_rel's tupdesc.
*/
memcpy(&fake_riinfo, riinfo, sizeof(RI_ConstraintInfo));
- for (int i = 0; i < fake_riinfo.nkeys; i++)
- fake_riinfo.fk_attnums[i] = i + 1;
+ for (int i = 0; i < fake_riinfo.param->nkeys; i++)
+ fake_riinfo.param->fk_attnums[i] = i + 1;
/*
* If it's MATCH FULL, and there are any nulls in the FK keys,
* complain about that rather than the lack of a match. MATCH FULL
* disallows partially-null FK rows.
*/
- if (fake_riinfo.confmatchtype == FKCONSTR_MATCH_FULL &&
+ if (fake_riinfo.param->confmatchtype == FKCONSTR_MATCH_FULL &&
ri_NullCheck(tupdesc, slot, &fake_riinfo, false) != RI_KEYS_NONE_NULL)
ereport(ERROR,
(errcode(ERRCODE_FOREIGN_KEY_VIOLATION),
@@ -1615,10 +1642,10 @@ RI_PartitionRemove_Check(Trigger *trigger, Relation fk_rel, Relation pk_rel)
initStringInfo(&querybuf);
appendStringInfoString(&querybuf, "SELECT ");
sep = "";
- for (i = 0; i < riinfo->nkeys; i++)
+ for (i = 0; i < riinfo->param->nkeys; i++)
{
quoteOneName(fkattname,
- RIAttName(fk_rel, riinfo->fk_attnums[i]));
+ RIAttName(fk_rel, riinfo->param->fk_attnums[i]));
appendStringInfo(&querybuf, "%sfk.%s", sep, fkattname);
sep = ", ";
}
@@ -1633,20 +1660,20 @@ RI_PartitionRemove_Check(Trigger *trigger, Relation fk_rel, Relation pk_rel)
strcpy(pkattname, "pk.");
strcpy(fkattname, "fk.");
sep = "(";
- for (i = 0; i < riinfo->nkeys; i++)
+ for (i = 0; i < riinfo->param->nkeys; i++)
{
- Oid pk_type = RIAttType(pk_rel, riinfo->pk_attnums[i]);
- Oid fk_type = RIAttType(fk_rel, riinfo->fk_attnums[i]);
- Oid pk_coll = RIAttCollation(pk_rel, riinfo->pk_attnums[i]);
- Oid fk_coll = RIAttCollation(fk_rel, riinfo->fk_attnums[i]);
+ Oid pk_type = RIAttType(pk_rel, riinfo->param->pk_attnums[i]);
+ Oid fk_type = RIAttType(fk_rel, riinfo->param->fk_attnums[i]);
+ Oid pk_coll = RIAttCollation(pk_rel, riinfo->param->pk_attnums[i]);
+ Oid fk_coll = RIAttCollation(fk_rel, riinfo->param->fk_attnums[i]);
quoteOneName(pkattname + 3,
- RIAttName(pk_rel, riinfo->pk_attnums[i]));
+ RIAttName(pk_rel, riinfo->param->pk_attnums[i]));
quoteOneName(fkattname + 3,
- RIAttName(fk_rel, riinfo->fk_attnums[i]));
+ RIAttName(fk_rel, riinfo->param->fk_attnums[i]));
ri_GenerateQual(&querybuf, sep,
pkattname, pk_type,
- riinfo->pf_eq_oprs[i],
+ riinfo->param->pf_eq_oprs[i],
fkattname, fk_type);
if (pk_coll != fk_coll)
ri_GenerateQualCollation(&querybuf, pk_coll);
@@ -1666,13 +1693,13 @@ RI_PartitionRemove_Check(Trigger *trigger, Relation fk_rel, Relation pk_rel)
appendStringInfoString(&querybuf, ") WHERE (");
sep = "";
- for (i = 0; i < riinfo->nkeys; i++)
+ for (i = 0; i < riinfo->param->nkeys; i++)
{
- quoteOneName(fkattname, RIAttName(fk_rel, riinfo->fk_attnums[i]));
+ quoteOneName(fkattname, RIAttName(fk_rel, riinfo->param->fk_attnums[i]));
appendStringInfo(&querybuf,
"%sfk.%s IS NOT NULL",
sep, fkattname);
- switch (riinfo->confmatchtype)
+ switch (riinfo->param->confmatchtype)
{
case FKCONSTR_MATCH_SIMPLE:
sep = " AND ";
@@ -1762,8 +1789,8 @@ RI_PartitionRemove_Check(Trigger *trigger, Relation fk_rel, Relation pk_rel)
* or fk_rel's tupdesc.
*/
memcpy(&fake_riinfo, riinfo, sizeof(RI_ConstraintInfo));
- for (i = 0; i < fake_riinfo.nkeys; i++)
- fake_riinfo.pk_attnums[i] = i + 1;
+ for (i = 0; i < fake_riinfo.param->nkeys; i++)
+ fake_riinfo.param->pk_attnums[i] = i + 1;
ri_ReportViolation(&fake_riinfo, pk_rel, fk_rel,
slot, tupdesc, 0, true);
@@ -1905,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->constraint_id;
+ key->constr_id = riinfo->param->query_key;
key->constr_queryno = constr_queryno;
}
@@ -1983,25 +2010,25 @@ ri_FetchConstraintInfo(Trigger *trigger, Relation trig_rel, bool rel_is_pk)
if (rel_is_pk)
{
if (riinfo->fk_relid != trigger->tgconstrrelid ||
- riinfo->pk_relid != RelationGetRelid(trig_rel))
+ riinfo->param->pk_relid != RelationGetRelid(trig_rel))
elog(ERROR, "wrong pg_constraint entry for trigger \"%s\" on table \"%s\"",
trigger->tgname, RelationGetRelationName(trig_rel));
}
else
{
if (riinfo->fk_relid != RelationGetRelid(trig_rel) ||
- riinfo->pk_relid != trigger->tgconstrrelid)
+ riinfo->param->pk_relid != trigger->tgconstrrelid)
elog(ERROR, "wrong pg_constraint entry for trigger \"%s\" on table \"%s\"",
trigger->tgname, RelationGetRelationName(trig_rel));
}
- if (riinfo->confmatchtype != FKCONSTR_MATCH_FULL &&
- riinfo->confmatchtype != FKCONSTR_MATCH_PARTIAL &&
- riinfo->confmatchtype != FKCONSTR_MATCH_SIMPLE)
+ if (riinfo->param->confmatchtype != FKCONSTR_MATCH_FULL &&
+ riinfo->param->confmatchtype != FKCONSTR_MATCH_PARTIAL &&
+ riinfo->param->confmatchtype != FKCONSTR_MATCH_SIMPLE)
elog(ERROR, "unrecognized confmatchtype: %d",
- riinfo->confmatchtype);
+ riinfo->param->confmatchtype);
- if (riinfo->confmatchtype == FKCONSTR_MATCH_PARTIAL)
+ if (riinfo->param->confmatchtype == FKCONSTR_MATCH_PARTIAL)
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("MATCH PARTIAL not yet implemented")));
@@ -2032,8 +2059,15 @@ ri_LoadConstraintInfo(Oid constraintOid)
riinfo = (RI_ConstraintInfo *) hash_search(ri_constraint_cache,
(void *) &constraintOid,
HASH_ENTER, &found);
+
if (!found)
+ {
riinfo->valid = false;
+ riinfo->param = (RI_ConstraintParam *)
+ MemoryContextAlloc(ri_constraint_cache_cxt,
+ sizeof(RI_ConstraintParam));
+ riinfo->param->ownerinfo = riinfo;
+ }
else if (riinfo->valid)
return riinfo;
@@ -2051,22 +2085,23 @@ ri_LoadConstraintInfo(Oid constraintOid)
/* And extract data */
Assert(riinfo->constraint_id == constraintOid);
+ riinfo->param->query_key = riinfo->constraint_id;
riinfo->oidHashValue = GetSysCacheHashValue1(CONSTROID,
ObjectIdGetDatum(constraintOid));
memcpy(&riinfo->conname, &conForm->conname, sizeof(NameData));
- riinfo->pk_relid = conForm->confrelid;
+ riinfo->param->pk_relid = conForm->confrelid;
riinfo->fk_relid = conForm->conrelid;
- riinfo->confupdtype = conForm->confupdtype;
- riinfo->confdeltype = conForm->confdeltype;
- riinfo->confmatchtype = conForm->confmatchtype;
+ riinfo->param->confupdtype = conForm->confupdtype;
+ riinfo->param->confdeltype = conForm->confdeltype;
+ riinfo->param->confmatchtype = conForm->confmatchtype;
DeconstructFkConstraintRow(tup,
- &riinfo->nkeys,
- riinfo->fk_attnums,
- riinfo->pk_attnums,
- riinfo->pf_eq_oprs,
- riinfo->pp_eq_oprs,
- riinfo->ff_eq_oprs);
+ &riinfo->param->nkeys,
+ riinfo->param->fk_attnums,
+ riinfo->param->pk_attnums,
+ riinfo->param->pf_eq_oprs,
+ riinfo->param->pp_eq_oprs,
+ riinfo->param->ff_eq_oprs);
ReleaseSysCache(tup);
@@ -2227,7 +2262,7 @@ ri_PerformCheck(const RI_ConstraintInfo *riinfo,
vals, nulls);
if (oldslot)
ri_ExtractValues(source_rel, oldslot, riinfo, source_is_pk,
- vals + riinfo->nkeys, nulls + riinfo->nkeys);
+ vals + riinfo->param->nkeys, nulls + riinfo->param->nkeys);
}
else
{
@@ -2320,11 +2355,11 @@ ri_ExtractValues(Relation rel, TupleTableSlot *slot,
bool isnull;
if (rel_is_pk)
- attnums = riinfo->pk_attnums;
+ attnums = riinfo->param->pk_attnums;
else
- attnums = riinfo->fk_attnums;
+ attnums = riinfo->param->fk_attnums;
- for (int i = 0; i < riinfo->nkeys; i++)
+ for (int i = 0; i < riinfo->param->nkeys; i++)
{
vals[i] = slot_getattr(slot, attnums[i], &isnull);
nulls[i] = isnull ? 'n' : ' ';
@@ -2361,14 +2396,14 @@ ri_ReportViolation(const RI_ConstraintInfo *riinfo,
onfk = (queryno == RI_PLAN_CHECK_LOOKUPPK);
if (onfk)
{
- attnums = riinfo->fk_attnums;
+ attnums = riinfo->param->fk_attnums;
rel_oid = fk_rel->rd_id;
if (tupdesc == NULL)
tupdesc = fk_rel->rd_att;
}
else
{
- attnums = riinfo->pk_attnums;
+ attnums = riinfo->param->pk_attnums;
rel_oid = pk_rel->rd_id;
if (tupdesc == NULL)
tupdesc = pk_rel->rd_att;
@@ -2395,7 +2430,7 @@ ri_ReportViolation(const RI_ConstraintInfo *riinfo,
if (aclresult != ACLCHECK_OK)
{
/* Try for column-level permissions */
- for (int idx = 0; idx < riinfo->nkeys; idx++)
+ for (int idx = 0; idx < riinfo->param->nkeys; idx++)
{
aclresult = pg_attribute_aclcheck(rel_oid, attnums[idx],
GetUserId(),
@@ -2418,7 +2453,7 @@ ri_ReportViolation(const RI_ConstraintInfo *riinfo,
/* Get printable versions of the keys involved */
initStringInfo(&key_names);
initStringInfo(&key_values);
- for (int idx = 0; idx < riinfo->nkeys; idx++)
+ for (int idx = 0; idx < riinfo->param->nkeys; idx++)
{
int fnum = attnums[idx];
Form_pg_attribute att = TupleDescAttr(tupdesc, fnum - 1);
@@ -2508,11 +2543,11 @@ ri_NullCheck(TupleDesc tupDesc,
bool nonenull = true;
if (rel_is_pk)
- attnums = riinfo->pk_attnums;
+ attnums = riinfo->param->pk_attnums;
else
- attnums = riinfo->fk_attnums;
+ attnums = riinfo->param->fk_attnums;
- for (int i = 0; i < riinfo->nkeys; i++)
+ for (int i = 0; i < riinfo->param->nkeys; i++)
{
if (slot_attisnull(slot, attnums[i]))
nonenull = false;
@@ -2540,12 +2575,19 @@ ri_InitHashTables(void)
{
HASHCTL ctl;
+ Assert(ri_constraint_cache_cxt == NULL);
+ ri_constraint_cache_cxt = AllocSetContextCreate(CacheMemoryContext,
+ "RI constraint cache",
+ ALLOCSET_DEFAULT_SIZES);
+
memset(&ctl, 0, sizeof(ctl));
ctl.keysize = sizeof(Oid);
ctl.entrysize = sizeof(RI_ConstraintInfo);
+ ctl.hcxt = ri_constraint_cache_cxt;
ri_constraint_cache = hash_create("RI constraint cache",
RI_INIT_CONSTRAINTHASHSIZE,
- &ctl, HASH_ELEM | HASH_BLOBS);
+ &ctl,
+ HASH_ELEM | HASH_BLOBS | HASH_CONTEXT);
/* Arrange to flush cache on pg_constraint changes */
CacheRegisterSyscacheCallback(CONSTROID,
@@ -2555,16 +2597,18 @@ ri_InitHashTables(void)
memset(&ctl, 0, sizeof(ctl));
ctl.keysize = sizeof(RI_QueryKey);
ctl.entrysize = sizeof(RI_QueryHashEntry);
+ ctl.hcxt = ri_constraint_cache_cxt;
ri_query_cache = hash_create("RI query cache",
RI_INIT_QUERYHASHSIZE,
- &ctl, HASH_ELEM | HASH_BLOBS);
+ &ctl, HASH_ELEM | HASH_BLOBS | HASH_CONTEXT);
memset(&ctl, 0, sizeof(ctl));
ctl.keysize = sizeof(RI_CompareKey);
ctl.entrysize = sizeof(RI_CompareHashEntry);
+ ctl.hcxt = ri_constraint_cache_cxt;
ri_compare_cache = hash_create("RI compare cache",
RI_INIT_QUERYHASHSIZE,
- &ctl, HASH_ELEM | HASH_BLOBS);
+ &ctl, HASH_ELEM | HASH_BLOBS | HASH_CONTEXT);
}
@@ -2667,12 +2711,12 @@ ri_KeysEqual(Relation rel, TupleTableSlot *oldslot, TupleTableSlot *newslot,
const int16 *attnums;
if (rel_is_pk)
- attnums = riinfo->pk_attnums;
+ attnums = riinfo->param->pk_attnums;
else
- attnums = riinfo->fk_attnums;
+ attnums = riinfo->param->fk_attnums;
/* XXX: could be worthwhile to fetch all necessary attrs at once */
- for (int i = 0; i < riinfo->nkeys; i++)
+ for (int i = 0; i < riinfo->param->nkeys; i++)
{
Datum oldvalue;
Datum newvalue;
@@ -2702,7 +2746,8 @@ ri_KeysEqual(Relation rel, TupleTableSlot *oldslot, TupleTableSlot *newslot,
* difference for ON UPDATE CASCADE, but for consistency we treat
* all changes to the PK the same.
*/
- Form_pg_attribute att = TupleDescAttr(oldslot->tts_tupleDescriptor, attnums[i] - 1);
+ Form_pg_attribute att =
+ TupleDescAttr(oldslot->tts_tupleDescriptor, attnums[i] - 1);
if (!datum_image_eq(oldvalue, newvalue, att->attbyval, att->attlen))
return false;
@@ -2714,7 +2759,8 @@ ri_KeysEqual(Relation rel, TupleTableSlot *oldslot, TupleTableSlot *newslot,
* operator. Changes that compare equal will still satisfy the
* constraint after the update.
*/
- if (!ri_AttributesEqual(riinfo->ff_eq_oprs[i], RIAttType(rel, attnums[i]),
+ if (!ri_AttributesEqual(riinfo->param->ff_eq_oprs[i],
+ RIAttType(rel, attnums[i]),
oldvalue, newvalue))
return false;
}
--
2.18.4
From 67cbe62a069b3cb6b50eab7215094f71660c4cf5 Mon Sep 17 00:00:00 2001
From: Kyotaro Horiguchi <horikyoga.ntt@gmail.com>
Date: Fri, 4 Dec 2020 12:01:26 +0900
Subject: [PATCH 2/2] share param part of riinfo
---
src/backend/utils/adt/ri_triggers.c | 66 +++++++++++++++++++++++++++--
1 file changed, 62 insertions(+), 4 deletions(-)
diff --git a/src/backend/utils/adt/ri_triggers.c b/src/backend/utils/adt/ri_triggers.c
index 0306bf7739..187884f622 100644
--- a/src/backend/utils/adt/ri_triggers.c
+++ b/src/backend/utils/adt/ri_triggers.c
@@ -2043,9 +2043,11 @@ static const RI_ConstraintInfo *
ri_LoadConstraintInfo(Oid constraintOid)
{
RI_ConstraintInfo *riinfo;
+ RI_ConstraintParam tmpparam;
bool found;
HeapTuple tup;
Form_pg_constraint conForm;
+ Oid conparentid;
/*
* On the first call initialize the hashtable
@@ -2063,10 +2065,10 @@ ri_LoadConstraintInfo(Oid constraintOid)
if (!found)
{
riinfo->valid = false;
- riinfo->param = (RI_ConstraintParam *)
- MemoryContextAlloc(ri_constraint_cache_cxt,
- sizeof(RI_ConstraintParam));
- riinfo->param->ownerinfo = riinfo;
+
+ /* tentatively link the tmp parameter area to the riinfo */
+ riinfo->param = &tmpparam;
+ riinfo->param->ownerinfo = NULL;
}
else if (riinfo->valid)
return riinfo;
@@ -2103,8 +2105,64 @@ ri_LoadConstraintInfo(Oid constraintOid)
riinfo->param->pp_eq_oprs,
riinfo->param->ff_eq_oprs);
+ conparentid = conForm->conparentid;
ReleaseSysCache(tup);
+ Assert(riinfo->param);
+
+ /* check if this relation can share the parameter part with the parent. */
+ if (OidIsValid(conparentid))
+ {
+ const RI_ConstraintInfo *priinfo;
+ RI_ConstraintParam *pparam;
+ RI_ConstraintParam *param = riinfo->param;
+
+ /* load parent's riinfo (recurses to the topmost parent) */
+ priinfo = ri_LoadConstraintInfo(conparentid);
+ pparam = priinfo->param;
+
+ /* check if the parameter is identical with the parent */
+ if (memcmp(param, pparam,
+ offsetof(RI_ConstraintParam, pk_attnums)) == 0)
+ {
+ int i;
+
+ /*
+ * The unused elements of the arrays are not guaranteed to be
+ * zero-filled. Check only the used elements.
+ */
+ for (i = 0 ; i < param->nkeys ; i++)
+ {
+ if (param->pk_attnums[i] != pparam->pk_attnums[i] ||
+ param->fk_attnums[i] != pparam->fk_attnums[i] ||
+ param->pf_eq_oprs[i] != pparam->pf_eq_oprs[i] ||
+ param->pp_eq_oprs[i] != pparam->pp_eq_oprs[i] ||
+ param->ff_eq_oprs[i] != pparam->ff_eq_oprs[i])
+ break;
+ }
+
+ if (i == param->nkeys)
+ {
+ /* all parameters match. share the parameters with the parent */
+ if (riinfo->param->ownerinfo == riinfo)
+ pfree(riinfo->param);
+ riinfo->param = pparam;
+ }
+ }
+ }
+
+ /* make a permanent copy if the parameter area is tentative */
+ if (riinfo->param == &tmpparam)
+ {
+ riinfo->param = (RI_ConstraintParam *)
+ MemoryContextAlloc(ri_constraint_cache_cxt,
+ sizeof(RI_ConstraintParam));
+ /* copy parameter values */
+ memcpy(riinfo->param, &tmpparam,
+ offsetof(RI_ConstraintParam, ownerinfo));
+ riinfo->param->ownerinfo = riinfo;
+ }
+
/*
* For efficient processing of invalidation messages below, we keep a
* doubly-linked list, and a count, of all currently valid entries.
--
2.18.4
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
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
Attachment
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
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?
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
EDB: http://www.enterprisedb.com
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
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
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
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
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
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