pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
A row-level trigger on a partitioned table is not created on a sub-partition created later
8+ messages / 3 participants
[nested] [flat]

Commits from this thread:
  2020-01-02 1fa846f1c9 d732148398 adc9cb6f26  Fix cloning of row triggers to sub-partitions
  2019-12-27 27a3b2ad83 b4507a22fe 0a40f33b87  Add pg_dump test for triggers on partitioned tables
* A row-level trigger on a partitioned table is not created on a sub-partition created later
@ 2019-12-05 07:24 Petr Fedorov <petr.fedorov@phystech.edu>
  2019-12-18 02:11 ` Re: A row-level trigger on a partitioned table is not created on a sub-partition created later Amit Langote <amitlangote09@gmail.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Petr Fedorov @ 2019-12-05 07:24 UTC (permalink / raw)
  To: pgsql-bugs

Hello,

According to the documentation
<https://www.postgresql.org/docs/11/sql-createtrigger.html>,  creating a
row-level trigger on a partitioned table will cause identical triggers
to be created in all its existing partitions; and any partitions created
or attached later will contain an identical trigger, too.

It does not happen for a sub-partition - partition of partition - when
it is created/attached later.  Steps to reproduce (11.6, Centos 7):

create table level1 (id1 integer not null, id2 integer not null, id3
integer not null) partition by list (id2);
create table level2 partition of level1 for values in (1) partition by
list (id3);
create table level3 partition of level2 for values in (1);
create or replace function trigger_func() returns trigger language
'plpgsql' as $body$ begin raise exception 'fired'; return null; end $body$;
create trigger test_trigger after insert on level1 for each row execute
procedure trigger_func();

insert into level1 values (1,1,1);  -- fails as expected due to
test_trigger();

drop table level1;
drop function trigger_func();

create table level1 (id1 integer not null, id2 integer not null, id3
integer not null) partition by list (id2);
create or replace function trigger_func() returns trigger language
'plpgsql' as $body$ begin raise exception 'fired'; return null; end $body$;
create trigger test_trigger after insert on level1 for each row execute
procedure trigger_func();

create table level2 partition of level1 for values in (1) partition by
list (id3);
create table level3 partition of level2 for values in (1);

insert into level1 values (1,1,1); -- row inserted

psql \d+ shows that level3 does not have row level trigger while level2
and level1 have.

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: A row-level trigger on a partitioned table is not created on a sub-partition created later
  2019-12-05 07:24 A row-level trigger on a partitioned table is not created on a sub-partition created later Petr Fedorov <petr.fedorov@phystech.edu>
@ 2019-12-18 02:11 ` Amit Langote <amitlangote09@gmail.com>
  2019-12-18 09:56   ` Re: A row-level trigger on a partitioned table is not created on a sub-partition created later Alvaro Herrera <alvherre@2ndquadrant.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Amit Langote @ 2019-12-18 02:11 UTC (permalink / raw)
  To: Petr Fedorov <petr.fedorov@phystech.edu>; +Cc: pgsql-bugs; Alvaro Herrera <alvherre@2ndquadrant.com>

Hello,

On Thu, Dec 5, 2019 at 4:24 PM Petr Fedorov <petr.fedorov@phystech.edu> wrote:
> create table level1 (id1 integer not null, id2 integer not null, id3 integer not null) partition by list (id2);
> create or replace function trigger_func() returns trigger language 'plpgsql' as $body$ begin raise exception 'fired'; return null; end $body$;
> create trigger test_trigger after insert on level1 for each row execute procedure trigger_func();
>
> create table level2 partition of level1 for values in (1) partition by list (id3);
> create table level3 partition of level2 for values in (1);
>
> insert into level1 values (1,1,1); -- row inserted
>
> psql \d+ shows that level3 does not have row level trigger while level2 and level1 have.

That is a bug. :(

Alvaro, isn't marking triggers cloned to partitions "internal"
unnecessary?  Because the cloned trigger on partition (level2 in above
example) is marked internal, CloneRowTriggersToPartition() skips it
when called on a sub-partition (level3 in above example).

Attached patch to fix that passes make check, although a bit surprised
that it does.

Thanks,
Amit

Attachments:

dont-mark-cloned-triggers-internal.patch (text/plain, 5.1K) download diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c index e9955707fa..9d23388100 100644 --- a/src/backend/catalog/index.c +++ b/src/backend/catalog/index.c @@ -1938,7 +1938,7 @@ index_constraint_create(Relation heapRelation, (void) CreateTrigger(trigger, NULL, RelationGetRelid(heapRelation), InvalidOid, conOid, indexRelationId, InvalidOid, - InvalidOid, NULL, true, false); + InvalidOid, NULL, true); } /* diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index daa80ec4aa..1295f94f54 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -9853,7 +9853,7 @@ CreateFKCheckTrigger(Oid myRelOid, Oid refRelOid, Constraint *fkconstraint, fk_trigger->args = NIL; (void) CreateTrigger(fk_trigger, NULL, myRelOid, refRelOid, constraintOid, - indexOid, InvalidOid, InvalidOid, NULL, true, false); + indexOid, InvalidOid, InvalidOid, NULL, true); /* Make changes-so-far visible */ CommandCounterIncrement(); @@ -9921,7 +9921,7 @@ createForeignKeyActionTriggers(Relation rel, Oid refRelOid, Constraint *fkconstr (void) CreateTrigger(fk_trigger, NULL, refRelOid, RelationGetRelid(rel), constraintOid, - indexOid, InvalidOid, InvalidOid, NULL, true, false); + indexOid, InvalidOid, InvalidOid, NULL, true); /* Make changes-so-far visible */ CommandCounterIncrement(); @@ -9977,7 +9977,7 @@ createForeignKeyActionTriggers(Relation rel, Oid refRelOid, Constraint *fkconstr (void) CreateTrigger(fk_trigger, NULL, refRelOid, RelationGetRelid(rel), constraintOid, - indexOid, InvalidOid, InvalidOid, NULL, true, false); + indexOid, InvalidOid, InvalidOid, NULL, true); } /* @@ -16068,7 +16068,7 @@ CloneRowTriggersToPartition(Relation parent, Relation partition) CreateTrigger(trigStmt, NULL, RelationGetRelid(partition), trigForm->tgconstrrelid, InvalidOid, InvalidOid, trigForm->tgfoid, trigForm->oid, qual, - false, true); + false); MemoryContextSwitchTo(oldcxt); MemoryContextReset(perTupCxt); diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c index faeea16d21..3eeb00a81f 100644 --- a/src/backend/commands/trigger.c +++ b/src/backend/commands/trigger.c @@ -162,7 +162,7 @@ ObjectAddress CreateTrigger(CreateTrigStmt *stmt, const char *queryString, Oid relOid, Oid refRelOid, Oid constraintOid, Oid indexOid, Oid funcoid, Oid parentTriggerOid, Node *whenClause, - bool isInternal, bool in_partition) + bool isInternal) { int16 tgtype; int ncolumns; @@ -837,11 +837,6 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString, /* * Build the new pg_trigger tuple. - * - * When we're creating a trigger in a partition, we mark it as internal, - * even though we don't do the isInternal magic in this function. This - * makes the triggers in partitions identical to the ones in the - * partitioned tables, except that they are marked internal. */ memset(nulls, false, sizeof(nulls)); @@ -852,7 +847,7 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString, values[Anum_pg_trigger_tgfoid - 1] = ObjectIdGetDatum(funcoid); values[Anum_pg_trigger_tgtype - 1] = Int16GetDatum(tgtype); values[Anum_pg_trigger_tgenabled - 1] = CharGetDatum(TRIGGER_FIRES_ON_ORIGIN); - values[Anum_pg_trigger_tgisinternal - 1] = BoolGetDatum(isInternal || in_partition); + values[Anum_pg_trigger_tgisinternal - 1] = BoolGetDatum(isInternal); values[Anum_pg_trigger_tgconstrrelid - 1] = ObjectIdGetDatum(constrrelid); values[Anum_pg_trigger_tgconstrindid - 1] = ObjectIdGetDatum(indexOid); values[Anum_pg_trigger_tgconstraint - 1] = ObjectIdGetDatum(constraintOid); @@ -1192,7 +1187,7 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString, partdesc->oids[i], refRelOid, InvalidOid, indexOnChild, funcoid, trigoid, qual, - isInternal, true); + isInternal); table_close(childTbl, NoLock); diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c index 3a03ca7e2f..678d0e2d74 100644 --- a/src/backend/tcop/utility.c +++ b/src/backend/tcop/utility.c @@ -1520,7 +1520,7 @@ ProcessUtilitySlow(ParseState *pstate, address = CreateTrigger((CreateTrigStmt *) parsetree, queryString, InvalidOid, InvalidOid, InvalidOid, InvalidOid, InvalidOid, - InvalidOid, NULL, false, false); + InvalidOid, NULL, false); break; case T_CreatePLangStmt: diff --git a/src/include/commands/trigger.h b/src/include/commands/trigger.h index a46feeedb0..633d75bd49 100644 --- a/src/include/commands/trigger.h +++ b/src/include/commands/trigger.h @@ -160,7 +160,7 @@ extern PGDLLIMPORT int SessionReplicationRole; extern ObjectAddress CreateTrigger(CreateTrigStmt *stmt, const char *queryString, Oid relOid, Oid refRelOid, Oid constraintOid, Oid indexOid, Oid funcoid, Oid parentTriggerOid, Node *whenClause, - bool isInternal, bool in_partition); + bool isInternal); extern void RemoveTriggerById(Oid trigOid); extern Oid get_trigger_oid(Oid relid, const char *name, bool missing_ok);
^ permalink raw reply [nested|flat] 8+ messages in thread

* Re: A row-level trigger on a partitioned table is not created on a sub-partition created later
  2019-12-05 07:24 A row-level trigger on a partitioned table is not created on a sub-partition created later Petr Fedorov <petr.fedorov@phystech.edu>
  2019-12-18 02:11 ` Re: A row-level trigger on a partitioned table is not created on a sub-partition created later Amit Langote <amitlangote09@gmail.com>
@ 2019-12-18 09:56   ` Alvaro Herrera <alvherre@2ndquadrant.com>
  2019-12-26 08:00     ` Re: A row-level trigger on a partitioned table is not created on a sub-partition created later Amit Langote <amitlangote09@gmail.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Alvaro Herrera @ 2019-12-18 09:56 UTC (permalink / raw)
  To: Amit Langote <amitlangote09@gmail.com>; +Cc: Petr Fedorov <petr.fedorov@phystech.edu>; pgsql-bugs

On 2019-Dec-18, Amit Langote wrote:

> Hello,
> 
> On Thu, Dec 5, 2019 at 4:24 PM Petr Fedorov <petr.fedorov@phystech.edu> wrote:
> > create table level1 (id1 integer not null, id2 integer not null, id3 integer not null) partition by list (id2);
> > create or replace function trigger_func() returns trigger language 'plpgsql' as $body$ begin raise exception 'fired'; return null; end $body$;
> > create trigger test_trigger after insert on level1 for each row execute procedure trigger_func();
> >
> > create table level2 partition of level1 for values in (1) partition by list (id3);
> > create table level3 partition of level2 for values in (1);
> >
> > insert into level1 values (1,1,1); -- row inserted
> >
> > psql \d+ shows that level3 does not have row level trigger while level2 and level1 have.
> 
> That is a bug. :(

Ouch.

> Alvaro, isn't marking triggers cloned to partitions "internal"
> unnecessary?  Because the cloned trigger on partition (level2 in above
> example) is marked internal, CloneRowTriggersToPartition() skips it
> when called on a sub-partition (level3 in above example).
> 
> Attached patch to fix that passes make check, although a bit surprised
> that it does.

IIRC that change would break pg_dump.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: A row-level trigger on a partitioned table is not created on a sub-partition created later
  2019-12-05 07:24 A row-level trigger on a partitioned table is not created on a sub-partition created later Petr Fedorov <petr.fedorov@phystech.edu>
  2019-12-18 02:11 ` Re: A row-level trigger on a partitioned table is not created on a sub-partition created later Amit Langote <amitlangote09@gmail.com>
  2019-12-18 09:56   ` Re: A row-level trigger on a partitioned table is not created on a sub-partition created later Alvaro Herrera <alvherre@2ndquadrant.com>
@ 2019-12-26 08:00     ` Amit Langote <amitlangote09@gmail.com>
  2019-12-27 21:37       ` Re: A row-level trigger on a partitioned table is not created on a sub-partition created later Alvaro Herrera <alvherre@2ndquadrant.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Amit Langote @ 2019-12-26 08:00 UTC (permalink / raw)
  To: Alvaro Herrera <alvherre@2ndquadrant.com>; +Cc: Petr Fedorov <petr.fedorov@phystech.edu>; pgsql-bugs

On Wed, Dec 18, 2019 at 6:56 PM Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
> On 2019-Dec-18, Amit Langote wrote:
> > Alvaro, isn't marking triggers cloned to partitions "internal"
> > unnecessary?  Because the cloned trigger on partition (level2 in above
> > example) is marked internal, CloneRowTriggersToPartition() skips it
> > when called on a sub-partition (level3 in above example).
> >
> > Attached patch to fix that passes make check, although a bit surprised
> > that it does.
>
> IIRC that change would break pg_dump.

Indeed it does, but looks like partition triggers are not tested that
extensively in pg_dump's suite.

Attached updated patch with pg_dump hacks seems to do the trick for
me.  What do you think?

Thanks,
Amit

Attachments:

dont-mark-cloned-triggers-internal_v2.patch (text/plain, 6.9K) download diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c index 7529bf39b8..f58a958d46 100644 --- a/src/backend/catalog/index.c +++ b/src/backend/catalog/index.c @@ -1937,7 +1937,7 @@ index_constraint_create(Relation heapRelation, (void) CreateTrigger(trigger, NULL, RelationGetRelid(heapRelation), InvalidOid, conOid, indexRelationId, InvalidOid, - InvalidOid, NULL, true, false); + InvalidOid, NULL, true); } /* diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index e19772aa90..4ab4615fbe 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -9852,7 +9852,7 @@ CreateFKCheckTrigger(Oid myRelOid, Oid refRelOid, Constraint *fkconstraint, fk_trigger->args = NIL; (void) CreateTrigger(fk_trigger, NULL, myRelOid, refRelOid, constraintOid, - indexOid, InvalidOid, InvalidOid, NULL, true, false); + indexOid, InvalidOid, InvalidOid, NULL, true); /* Make changes-so-far visible */ CommandCounterIncrement(); @@ -9920,7 +9920,7 @@ createForeignKeyActionTriggers(Relation rel, Oid refRelOid, Constraint *fkconstr (void) CreateTrigger(fk_trigger, NULL, refRelOid, RelationGetRelid(rel), constraintOid, - indexOid, InvalidOid, InvalidOid, NULL, true, false); + indexOid, InvalidOid, InvalidOid, NULL, true); /* Make changes-so-far visible */ CommandCounterIncrement(); @@ -9976,7 +9976,7 @@ createForeignKeyActionTriggers(Relation rel, Oid refRelOid, Constraint *fkconstr (void) CreateTrigger(fk_trigger, NULL, refRelOid, RelationGetRelid(rel), constraintOid, - indexOid, InvalidOid, InvalidOid, NULL, true, false); + indexOid, InvalidOid, InvalidOid, NULL, true); } /* @@ -16057,7 +16057,7 @@ CloneRowTriggersToPartition(Relation parent, Relation partition) CreateTrigger(trigStmt, NULL, RelationGetRelid(partition), trigForm->tgconstrrelid, InvalidOid, InvalidOid, trigForm->tgfoid, trigForm->oid, qual, - false, true); + false); MemoryContextSwitchTo(oldcxt); MemoryContextReset(perTupCxt); diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c index b9fca3af3c..a9e46988e8 100644 --- a/src/backend/commands/trigger.c +++ b/src/backend/commands/trigger.c @@ -162,7 +162,7 @@ ObjectAddress CreateTrigger(CreateTrigStmt *stmt, const char *queryString, Oid relOid, Oid refRelOid, Oid constraintOid, Oid indexOid, Oid funcoid, Oid parentTriggerOid, Node *whenClause, - bool isInternal, bool in_partition) + bool isInternal) { int16 tgtype; int ncolumns; @@ -837,11 +837,6 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString, /* * Build the new pg_trigger tuple. - * - * When we're creating a trigger in a partition, we mark it as internal, - * even though we don't do the isInternal magic in this function. This - * makes the triggers in partitions identical to the ones in the - * partitioned tables, except that they are marked internal. */ memset(nulls, false, sizeof(nulls)); @@ -852,7 +847,7 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString, values[Anum_pg_trigger_tgfoid - 1] = ObjectIdGetDatum(funcoid); values[Anum_pg_trigger_tgtype - 1] = Int16GetDatum(tgtype); values[Anum_pg_trigger_tgenabled - 1] = CharGetDatum(TRIGGER_FIRES_ON_ORIGIN); - values[Anum_pg_trigger_tgisinternal - 1] = BoolGetDatum(isInternal || in_partition); + values[Anum_pg_trigger_tgisinternal - 1] = BoolGetDatum(isInternal); values[Anum_pg_trigger_tgconstrrelid - 1] = ObjectIdGetDatum(constrrelid); values[Anum_pg_trigger_tgconstrindid - 1] = ObjectIdGetDatum(indexOid); values[Anum_pg_trigger_tgconstraint - 1] = ObjectIdGetDatum(constraintOid); @@ -1185,7 +1180,7 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString, partdesc->oids[i], refRelOid, InvalidOid, indexOnChild, funcoid, trigoid, qual, - isInternal, true); + isInternal); table_close(childTbl, NoLock); diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c index 3a03ca7e2f..678d0e2d74 100644 --- a/src/backend/tcop/utility.c +++ b/src/backend/tcop/utility.c @@ -1520,7 +1520,7 @@ ProcessUtilitySlow(ParseState *pstate, address = CreateTrigger((CreateTrigStmt *) parsetree, queryString, InvalidOid, InvalidOid, InvalidOid, InvalidOid, InvalidOid, - InvalidOid, NULL, false, false); + InvalidOid, NULL, false); break; case T_CreatePLangStmt: diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 08658c8e86..68f9e6639b 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -7616,13 +7616,39 @@ getTriggers(Archive *fout, TableInfo tblinfo[], int numTables) tbinfo->dobj.name); resetPQExpBuffer(query); - if (fout->remoteVersion >= 90000) + + /* + * For servers that support triggers on partitioned tables, must + * exclude triggers of partitions that are also defined in some + * partitioned ancestor. + * + * NB: think not to use pretty=true in pg_get_triggerdef. It + * could result in non-forward-compatible dumps of WHEN clauses + * due to under-parenthesization. + */ + if (fout->remoteVersion >= 110000) + { + appendPQExpBuffer(query, + "SELECT tgname, " + "tgfoid::pg_catalog.regproc AS tgfname, " + "pg_catalog.pg_get_triggerdef(oid, false) AS tgdef, " + "tgenabled, tableoid, oid " + "FROM pg_catalog.pg_trigger t " + "WHERE tgrelid = '%u'::pg_catalog.oid " + "AND NOT tgisinternal " + "AND NOT EXISTS " + " (SELECT 1 FROM pg_trigger " + " WHERE tgname = t.tgname " + " AND tgrelid IN " + " (SELECT inhparent " + " FROM pg_inherits, pg_class c" + " WHERE inhrelid = t.tgrelid" + " AND t.tgrelid = c.oid" + " AND c.relispartition))", + tbinfo->dobj.catId.oid); + } + else if (fout->remoteVersion >= 90000) { - /* - * NB: think not to use pretty=true in pg_get_triggerdef. It - * could result in non-forward-compatible dumps of WHEN clauses - * due to under-parenthesization. - */ appendPQExpBuffer(query, "SELECT tgname, " "tgfoid::pg_catalog.regproc AS tgfname, " diff --git a/src/include/commands/trigger.h b/src/include/commands/trigger.h index a46feeedb0..633d75bd49 100644 --- a/src/include/commands/trigger.h +++ b/src/include/commands/trigger.h @@ -160,7 +160,7 @@ extern PGDLLIMPORT int SessionReplicationRole; extern ObjectAddress CreateTrigger(CreateTrigStmt *stmt, const char *queryString, Oid relOid, Oid refRelOid, Oid constraintOid, Oid indexOid, Oid funcoid, Oid parentTriggerOid, Node *whenClause, - bool isInternal, bool in_partition); + bool isInternal); extern void RemoveTriggerById(Oid trigOid); extern Oid get_trigger_oid(Oid relid, const char *name, bool missing_ok);
^ permalink raw reply [nested|flat] 8+ messages in thread

* Re: A row-level trigger on a partitioned table is not created on a sub-partition created later
  2019-12-05 07:24 A row-level trigger on a partitioned table is not created on a sub-partition created later Petr Fedorov <petr.fedorov@phystech.edu>
  2019-12-18 02:11 ` Re: A row-level trigger on a partitioned table is not created on a sub-partition created later Amit Langote <amitlangote09@gmail.com>
  2019-12-18 09:56   ` Re: A row-level trigger on a partitioned table is not created on a sub-partition created later Alvaro Herrera <alvherre@2ndquadrant.com>
  2019-12-26 08:00     ` Re: A row-level trigger on a partitioned table is not created on a sub-partition created later Amit Langote <amitlangote09@gmail.com>
@ 2019-12-27 21:37       ` Alvaro Herrera <alvherre@2ndquadrant.com>
  2019-12-27 22:18         ` Re: A row-level trigger on a partitioned table is not created on a sub-partition created later Alvaro Herrera <alvherre@2ndquadrant.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Alvaro Herrera @ 2019-12-27 21:37 UTC (permalink / raw)
  To: Amit Langote <amitlangote09@gmail.com>; +Cc: Petr Fedorov <petr.fedorov@phystech.edu>; pgsql-bugs

On 2019-Dec-26, Amit Langote wrote:
> 
> Indeed it does, but looks like partition triggers are not tested that
> extensively in pg_dump's suite.

Hmm, you're right.  I added a test to the three branches that would be
broken by your first patch.  I hope the buildfarm likes it.

Working on getting your fix pushed now.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services





^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: A row-level trigger on a partitioned table is not created on a sub-partition created later
  2019-12-05 07:24 A row-level trigger on a partitioned table is not created on a sub-partition created later Petr Fedorov <petr.fedorov@phystech.edu>
  2019-12-18 02:11 ` Re: A row-level trigger on a partitioned table is not created on a sub-partition created later Amit Langote <amitlangote09@gmail.com>
  2019-12-18 09:56   ` Re: A row-level trigger on a partitioned table is not created on a sub-partition created later Alvaro Herrera <alvherre@2ndquadrant.com>
  2019-12-26 08:00     ` Re: A row-level trigger on a partitioned table is not created on a sub-partition created later Amit Langote <amitlangote09@gmail.com>
  2019-12-27 21:37       ` Re: A row-level trigger on a partitioned table is not created on a sub-partition created later Alvaro Herrera <alvherre@2ndquadrant.com>
@ 2019-12-27 22:18         ` Alvaro Herrera <alvherre@2ndquadrant.com>
  2019-12-31 19:47           ` Re: A row-level trigger on a partitioned table is not created on a sub-partition created later Alvaro Herrera <alvherre@2ndquadrant.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Alvaro Herrera @ 2019-12-27 22:18 UTC (permalink / raw)
  To: Amit Langote <amitlangote09@gmail.com>; +Cc: Petr Fedorov <petr.fedorov@phystech.edu>; pgsql-bugs

On 2019-Dec-27, Alvaro Herrera wrote:

> Working on getting your fix pushed now.

Ran out of time for today, but I added this test that reproduces the
issue -- fails without your patch, and works with it.

One thing I just realized is that in next minors' release notes we
should publish a recipe to fix catalogs for existing databases, unless
we go for a fix that doesn't require changing the catalogs -- I don't
know what that would be, though, but maybe it would not be totally
insane to clone even internal triggers in the cases that matter.


(I wonder if a better solution for the master branch would involve a new
pg_trigger column that indicates the parent trigger for a cloned
trigger.  That would avoid pg_dump's new subquery.  Also: would it be
better to use pg_partition_ancestors instead of the sub-subquery?
...  though that doesn't work in pg11 ...)

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Attachments:

trigtest.patch (text/x-diff, 3.9K) download commit 8a2318fcbdc501e6b230a22f6a6b1de57f871e74 Author: Alvaro Herrera <alvherre@alvh.no-ip.org> AuthorDate: Fri Dec 27 18:56:05 2019 -0300 CommitDate: Fri Dec 27 18:59:25 2019 -0300 Add failed test that shows the issue diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out index 1e4053ceed..b91fbd0648 100644 --- a/src/test/regress/expected/triggers.out +++ b/src/test/regress/expected/triggers.out @@ -1981,15 +1981,22 @@ create trigger trg1 after insert on trigpart for each row execute procedure trig create table trigpart2 partition of trigpart for values from (1000) to (2000); create table trigpart3 (like trigpart); alter table trigpart attach partition trigpart3 for values from (2000) to (3000); +create table trigpart4 partition of trigpart for values from (3000) to (4000) partition by range (a); +create table trigpart41 partition of trigpart4 for values from (3000) to (3500); +create table trigpart42 (like trigpart); +alter table trigpart4 attach partition trigpart42 for values from (3500) to (4000); select tgrelid::regclass, tgname, tgfoid::regproc from pg_trigger where tgrelid::regclass::text like 'trigpart%' order by tgrelid::regclass::text; - tgrelid | tgname | tgfoid ------------+--------+----------------- - trigpart | trg1 | trigger_nothing - trigpart1 | trg1 | trigger_nothing - trigpart2 | trg1 | trigger_nothing - trigpart3 | trg1 | trigger_nothing -(4 rows) + tgrelid | tgname | tgfoid +------------+--------+----------------- + trigpart | trg1 | trigger_nothing + trigpart1 | trg1 | trigger_nothing + trigpart2 | trg1 | trigger_nothing + trigpart3 | trg1 | trigger_nothing + trigpart4 | trg1 | trigger_nothing + trigpart41 | trg1 | trigger_nothing + trigpart42 | trg1 | trigger_nothing +(7 rows) drop trigger trg1 on trigpart1; -- fail ERROR: cannot drop trigger trg1 on table trigpart1 because trigger trg1 on table trigpart requires it @@ -2003,12 +2010,15 @@ HINT: You can drop trigger trg1 on table trigpart instead. drop table trigpart2; -- ok, trigger should be gone in that partition select tgrelid::regclass, tgname, tgfoid::regproc from pg_trigger where tgrelid::regclass::text like 'trigpart%' order by tgrelid::regclass::text; - tgrelid | tgname | tgfoid ------------+--------+----------------- - trigpart | trg1 | trigger_nothing - trigpart1 | trg1 | trigger_nothing - trigpart3 | trg1 | trigger_nothing -(3 rows) + tgrelid | tgname | tgfoid +------------+--------+----------------- + trigpart | trg1 | trigger_nothing + trigpart1 | trg1 | trigger_nothing + trigpart3 | trg1 | trigger_nothing + trigpart4 | trg1 | trigger_nothing + trigpart41 | trg1 | trigger_nothing + trigpart42 | trg1 | trigger_nothing +(6 rows) drop trigger trg1 on trigpart; -- ok, all gone select tgrelid::regclass, tgname, tgfoid::regproc from pg_trigger diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql index c21b6c124e..7cd835449c 100644 --- a/src/test/regress/sql/triggers.sql +++ b/src/test/regress/sql/triggers.sql @@ -1366,6 +1366,10 @@ create trigger trg1 after insert on trigpart for each row execute procedure trig create table trigpart2 partition of trigpart for values from (1000) to (2000); create table trigpart3 (like trigpart); alter table trigpart attach partition trigpart3 for values from (2000) to (3000); +create table trigpart4 partition of trigpart for values from (3000) to (4000) partition by range (a); +create table trigpart41 partition of trigpart4 for values from (3000) to (3500); +create table trigpart42 (like trigpart); +alter table trigpart4 attach partition trigpart42 for values from (3500) to (4000); select tgrelid::regclass, tgname, tgfoid::regproc from pg_trigger where tgrelid::regclass::text like 'trigpart%' order by tgrelid::regclass::text; drop trigger trg1 on trigpart1; -- fail
^ permalink raw reply [nested|flat] 8+ messages in thread

* Re: A row-level trigger on a partitioned table is not created on a sub-partition created later
  2019-12-05 07:24 A row-level trigger on a partitioned table is not created on a sub-partition created later Petr Fedorov <petr.fedorov@phystech.edu>
  2019-12-18 02:11 ` Re: A row-level trigger on a partitioned table is not created on a sub-partition created later Amit Langote <amitlangote09@gmail.com>
  2019-12-18 09:56   ` Re: A row-level trigger on a partitioned table is not created on a sub-partition created later Alvaro Herrera <alvherre@2ndquadrant.com>
  2019-12-26 08:00     ` Re: A row-level trigger on a partitioned table is not created on a sub-partition created later Amit Langote <amitlangote09@gmail.com>
  2019-12-27 21:37       ` Re: A row-level trigger on a partitioned table is not created on a sub-partition created later Alvaro Herrera <alvherre@2ndquadrant.com>
  2019-12-27 22:18         ` Re: A row-level trigger on a partitioned table is not created on a sub-partition created later Alvaro Herrera <alvherre@2ndquadrant.com>
@ 2019-12-31 19:47           ` Alvaro Herrera <alvherre@2ndquadrant.com>
  2020-01-02 08:06             ` Re: A row-level trigger on a partitioned table is not created on a sub-partition created later Amit Langote <amitlangote09@gmail.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Alvaro Herrera @ 2019-12-31 19:47 UTC (permalink / raw)
  To: Amit Langote <amitlangote09@gmail.com>; +Cc: Petr Fedorov <petr.fedorov@phystech.edu>; pgsql-bugs

On 2019-Dec-27, Alvaro Herrera wrote:

> One thing I just realized is that in next minors' release notes we
> should publish a recipe to fix catalogs for existing databases, unless
> we go for a fix that doesn't require changing the catalogs -- I don't
> know what that would be, though, but maybe it would not be totally
> insane to clone even internal triggers in the cases that matter.

So, the more I thought about this, the more it seemed that marking those
triggers as not internal was the wrong thing to do.  So I instead made
it clone the internal triggers that seem to matter.  This fixes the
originally reported problem, and passes the test case I propose.  Also,
pg_dump continues to work unchanged, and existing database don't need
tweaking (excepting those that might already be missing triggers).

It would be better to fix master afterwards, by adding a
pg_trigger.trgparentid column, which seems like it would be a better
answer going forward.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Attachments:

  0001-Add-failed-test-that-shows-the-issue.patch (text/x-diff, 0B) download

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: A row-level trigger on a partitioned table is not created on a sub-partition created later
  2019-12-05 07:24 A row-level trigger on a partitioned table is not created on a sub-partition created later Petr Fedorov <petr.fedorov@phystech.edu>
  2019-12-18 02:11 ` Re: A row-level trigger on a partitioned table is not created on a sub-partition created later Amit Langote <amitlangote09@gmail.com>
  2019-12-18 09:56   ` Re: A row-level trigger on a partitioned table is not created on a sub-partition created later Alvaro Herrera <alvherre@2ndquadrant.com>
  2019-12-26 08:00     ` Re: A row-level trigger on a partitioned table is not created on a sub-partition created later Amit Langote <amitlangote09@gmail.com>
  2019-12-27 21:37       ` Re: A row-level trigger on a partitioned table is not created on a sub-partition created later Alvaro Herrera <alvherre@2ndquadrant.com>
  2019-12-27 22:18         ` Re: A row-level trigger on a partitioned table is not created on a sub-partition created later Alvaro Herrera <alvherre@2ndquadrant.com>
  2019-12-31 19:47           ` Re: A row-level trigger on a partitioned table is not created on a sub-partition created later Alvaro Herrera <alvherre@2ndquadrant.com>
@ 2020-01-02 08:06             ` Amit Langote <amitlangote09@gmail.com>
  0 siblings, 0 replies; 8+ messages in thread

From: Amit Langote @ 2020-01-02 08:06 UTC (permalink / raw)
  To: Alvaro Herrera <alvherre@2ndquadrant.com>; +Cc: Petr Fedorov <petr.fedorov@phystech.edu>; pgsql-bugs

Sorry, away from work past few days.

On Wed, Jan 1, 2020 at 4:48 AM Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
> On 2019-Dec-27, Alvaro Herrera wrote:
> > One thing I just realized is that in next minors' release notes we
> > should publish a recipe to fix catalogs for existing databases, unless
> > we go for a fix that doesn't require changing the catalogs -- I don't
> > know what that would be, though, but maybe it would not be totally
> > insane to clone even internal triggers in the cases that matter.

On second thought, I agree that not having to have to fix catalog
state for existing users would be good.

> So, the more I thought about this, the more it seemed that marking those
> triggers as not internal was the wrong thing to do.  So I instead made
> it clone the internal triggers that seem to matter.  This fixes the
> originally reported problem, and passes the test case I propose.  Also,, away
> pg_dump continues to work unchanged, and existing database don't need
> tweaking (excepting those that might already be missing triggers).

0002 seems fine as a solution although some comments:

+/*
+ * doesTriggerDependOnAnotherTrigger
+ * Checks pg_depend for what the name says
+ *
+ * This is an ugly hack to cope with a catalog deficiency.
+ * Keep away from children.  Do not stare with naked eyes.  Do not propagate.
+ */
+static bool
+doesTriggerDependOnAnotherTrigger(Oid trigger_oid)

Instead of making this too generic sounding, would it be better to
name this to make clear what hack this is part of, say,
isPartitionTrigger?

> It would be better to fix master afterwards, by adding a
> pg_trigger.trgparentid column, which seems like it would be a better
> answer going forward.

Agreed.

Thanks,
Amit





^ permalink  raw  reply  [nested|flat] 8+ messages in thread


end of thread, other threads:[~2020-01-02 08:06 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-12-05 07:24 A row-level trigger on a partitioned table is not created on a sub-partition created later Petr Fedorov <petr.fedorov@phystech.edu>
2019-12-18 02:11 ` Amit Langote <amitlangote09@gmail.com>
2019-12-18 09:56   ` Alvaro Herrera <alvherre@2ndquadrant.com>
2019-12-26 08:00     ` Amit Langote <amitlangote09@gmail.com>
2019-12-27 21:37       ` Alvaro Herrera <alvherre@2ndquadrant.com>
2019-12-27 22:18         ` Alvaro Herrera <alvherre@2ndquadrant.com>
2019-12-31 19:47           ` Alvaro Herrera <alvherre@2ndquadrant.com>
2020-01-02 08:06             ` Amit Langote <amitlangote09@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox