Re: Remove mention in docs that foreign keys on partitioned tables are not supported

From: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
To: Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)com>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp>, Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com>, David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: Remove mention in docs that foreign keys on partitioned tables are not supported
Date: 2018-06-18 16:59:10
Message-ID: 20180618165910.p26vhk7dpq65ix54@alvherre.pgsql
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On 2018-Jun-18, Ashutosh Bapat wrote:

> That's a wrong comparison. Inheritance based partitioning works even
> after declarative partitioning feature is added. So, users can
> continue using inheritance based partitioning if they don't want to
> move to declarative partitioning. That's not true here. A user creates
> a BEFORE ROW trigger on each partition that mimics partitioned table
> level BEFORE ROW trigger. The proposed BEFORE ROW trigger on
> partitioned table will cascade the trigger down to each partition. If
> that fails to recognize that there is already an equivalent trigger, a
> partition table will get two triggers doing the same thing silently
> without any warning or notice. That's fine if the trigger changes the
> salaries to $50K but not OK if each of those increases salary by 10%.
> The database has limited ability to recognize what a trigger is doing.

I agree with Robert that nobody in their right minds would be caught by
this problem by adding new triggers without thinking about it and
without testing them. If you add a partitioned-table-level trigger to
raise salaries by 10% but there was already one in the partition level
that does the same thing, you'll readily notice that salaries have been
increased by 21% instead.

So like Robert I'm inclined to not change the wording in the

What does worry me a little bit now, reading this discussion, is whether
we've made the triggers in partitions visible enough. We'll have this
problem once we implement BEFORE ROW triggers as proposed, and I think
we already have this problem now. Let's suppose a user creates a
duplicated after trigger:

create table parent (a int) partition by range (a);
create table child partition of parent for values from (0) to (100);
create function noise() returns trigger language plpgsql as $$ begin raise notice 'nyaa'; return null; end; $$;
create trigger trig_p after insert on parent for each row execute procedure noise();
create trigger trig_c after insert on child for each row execute procedure noise();

Now let's try it:

alvherre=# insert into child values (1);
NOTICE: nyaa
NOTICE: nyaa

OK, so where does that one come from?

alvherre=# \d child
Tabla «public.child»
Columna │ Tipo │ Collation │ Nullable │ Default
a │ integer │ │ │
Partition of: parent FOR VALUES FROM (0) TO (100)

Hmm, there's only one trigger here, why does it appear twice? To find
out, you have to know where to look:

alvherre=# select tgname, tgrelid::regclass, tgisinternal from pg_trigger;
tgname │ tgrelid │ tgisinternal
trig_p │ parent │ f
trig_p │ child │ t
trig_c │ child │ f
(3 filas)

So there is a trigger in table child, but it's hidden because
tgisinternal. Of course, you can see it if you look at the parent's

alvherre=# \d parent
Tabla «public.parent»
Columna │ Tipo │ Collation │ Nullable │ Default
a │ integer │ │ │
Partition key: RANGE (a)
Number of partitions: 1 (Use \d+ to list them.)

I think it'd be useful to have a list of triggers that have been
inherited from ancestors, or maybe simply a list of internal triggers

Or maybe this is not something to worry about?

Álvaro Herrera
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Geoghegan 2018-06-18 17:03:20 Re: Making all nbtree entries unique by having heap TIDs participate in comparisons
Previous Message Tomas Vondra 2018-06-18 16:58:13 Re: [Proposal] Table-level Transparent Data Encryption (TDE) and Key Management Service (KMS)