From: | Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> |
---|---|
To: | Robert Treat <rob(at)xzilla(dot)net> |
Cc: | Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)com>, 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-27 22:58:18 |
Message-ID: | 20180627225818.sl6cxcebwuuprhqw@alvherre.pgsql |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | Postg범퍼카 토토SQL |
On 2018-Jun-18, Robert Treat wrote:
> One of the things I was thinking about while reading this thread is
> that the scenario of creating "duplicate" triggers on a table meaning
> two triggers doing the same thing is entirely possible now but we
> don't really do anything to prevent it, which is Ok. I've never seen
> much merit in the argument "people should test" (it assumes a certain
> infallibility that just isn't true) but we've also generally been
> pretty good about exposing what is going on so people can debug this
> type of unexpected behavior.
>
> So +1 for thinking we do need to worry about it. I'm not exactly sure
> how we want to expose that info; with \d+ we list various "Partition
> X:" sections, perhaps adding one for "Partition triggers:" would be
> enough, although I am inclined to think it needs exposure at the \d
> level. One other thing to consider is firing order of said triggers...
> if all parent level triggers fire before child level triggers then the
> above separation is straightforward, but if the execution order is, as
> I suspect, mixed, then it becomes more complicated.
The firing order is alphabetical across the whole set, i.e. parent/child
triggers are interleaved. See the regression test output at the bottom
of this email.
I looked into adding a "Partition triggers" section because it seemed a
nice idea, but looking at the code I realized it's a bit tough because
we already split triggers in "categories": normal triggers, disabled
triggers, ALWAYS triggers and REPLICA triggers (src/bin/psql/describe.c
line 2795). Since the trigger being in a partition is orthogonal to
that classification, we would end up with eight groups. Not sure that's
great.
The attached patch (catversion bump not included -- beware of server
crash if you run it without initdb'ing) keeps the categories the same.
So with my previous example setup, you can see the duplicate triggers in
psql:
alvherre=# \d child
Table "public.child"
Column │ Type │ Collation │ Nullable │ Default
────────┼─────────┼───────────┼──────────┼─────────
a │ integer │ │ │
Partition of: parent FOR VALUES FROM (0) TO (100)
Triggers:
trig_c AFTER INSERT ON child FOR EACH ROW EXECUTE PROCEDURE noise()
trig_p AFTER INSERT ON child FOR EACH ROW EXECUTE PROCEDURE noise()
and as soon as you try to drop the second one, you'll learn the truth
about it:
alvherre=# drop trigger trig_p on child;
ERROR: cannot drop trigger trig_p on table child because trigger trig_p on table parent requires it
SUGERENCIA: You can drop trigger trig_p on table parent instead.
Time: 1,443 ms
I say this is sufficient.
-- Verify that triggers fire in alphabetical order
create table parted_trig (a int) partition by range (a);
create table parted_trig_1 partition of parted_trig for values from (0) to (1000)
partition by range (a);
create table parted_trig_1_1 partition of parted_trig_1 for values from (0) to (100);
create table parted_trig_2 partition of parted_trig for values from (1000) to (2000);
create trigger zzz after insert on parted_trig for each row execute procedure trigger_notice();
create trigger mmm after insert on parted_trig_1_1 for each row execute procedure trigger_notice();
create trigger aaa after insert on parted_trig_1 for each row execute procedure trigger_notice();
create trigger bbb after insert on parted_trig for each row execute procedure trigger_notice();
create trigger qqq after insert on parted_trig_1_1 for each row execute procedure trigger_notice();
insert into parted_trig values (50), (1500);
NOTICE: trigger aaa on parted_trig_1_1 AFTER INSERT for ROW
NOTICE: trigger bbb on parted_trig_1_1 AFTER INSERT for ROW
NOTICE: trigger mmm on parted_trig_1_1 AFTER INSERT for ROW
NOTICE: trigger qqq on parted_trig_1_1 AFTER INSERT for ROW
NOTICE: trigger zzz on parted_trig_1_1 AFTER INSERT for ROW
NOTICE: trigger bbb on parted_trig_2 AFTER INSERT for ROW
NOTICE: trigger zzz on parted_trig_2 AFTER INSERT for ROW
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Attachment | Content-Type | Size |
---|---|---|
show-partition-triggers.patch | text/plain | 3.2 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Paquier | 2018-06-27 23:10:28 | Re: ALTER TABLE on system catalogs |
Previous Message | Alvaro Herrera | 2018-06-27 22:21:49 | Re: Listing triggers in partitions (was Re: Remove mention in docs that foreign keys on partitioned tables) |