Обсуждение: CREATE TABLE LIKE INCLUDING TRIGGERS

hi.

poc demo:
CREATE TABLE main_table (a int, b int);
CREATE FUNCTION trigger_func() RETURNS trigger LANGUAGE plpgsql AS '
BEGIN
    RAISE NOTICE ''trigger_func(%) called: action = %, when = %, level = %'',
                  TG_ARGV[0], TG_OP, TG_WHEN, TG_LEVEL;
    RETURN NULL;
END;';
CREATE TRIGGER modified_a BEFORE UPDATE OF a ON main_table
FOR EACH ROW WHEN (OLD.a <> NEW.a) EXECUTE PROCEDURE trigger_func('modified_a');

CREATE TABLE main_table1(LIKE main_table INCLUDING TRIGGERS INCLUDING COMMENTS);
\d main_table1
            Table "public.main_table1"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 a      | integer |           |          |
 b      | integer |           |          |
Triggers:
    modified_a BEFORE UPDATE OF a ON main_table1 FOR EACH ROW WHEN
(old.a <> new.a) EXECUTE FUNCTION trigger_func('modified_a')

foreign key associated internal triggers won't be copied to the new table.
source table trigger associated comment will be copied to the new table,
if INCLUDING COMMENTS is specified.

---------------
v1-0001:  "refactor CreateTrigger and CreateTriggerFiringOn".

Similar to CreateStatistics, some of the expressions stored in the
catalog pg_trigger are
already transformed, when we retrieve it as a base model for constructing a new
CreateTrigStmt, we can not do parse analysis of it again.
see transformStatsStmt for similar handling.
The CreateTrigger function, (Node *whenClause) is always NULL,
so I think it's safe to remove the argument whenClause.

v1-0002: CREATE TABLE LIKE INCLUDING TRIGGERS.

On Mon, Sep 29, 2025 at 5:35 PM jian he <jian.universality@gmail.com> wrote:
>
> hi.
>
> poc demo:
> CREATE TABLE main_table (a int, b int);
> CREATE FUNCTION trigger_func() RETURNS trigger LANGUAGE plpgsql AS '
> BEGIN
>     RAISE NOTICE ''trigger_func(%) called: action = %, when = %, level = %'',
>                   TG_ARGV[0], TG_OP, TG_WHEN, TG_LEVEL;
>     RETURN NULL;
> END;';
> CREATE TRIGGER modified_a BEFORE UPDATE OF a ON main_table
> FOR EACH ROW WHEN (OLD.a <> NEW.a) EXECUTE PROCEDURE trigger_func('modified_a');
>
> CREATE TABLE main_table1(LIKE main_table INCLUDING TRIGGERS INCLUDING COMMENTS);
> \d main_table1
>             Table "public.main_table1"
>  Column |  Type   | Collation | Nullable | Default
> --------+---------+-----------+----------+---------
>  a      | integer |           |          |
>  b      | integer |           |          |
> Triggers:
>     modified_a BEFORE UPDATE OF a ON main_table1 FOR EACH ROW WHEN
> (old.a <> new.a) EXECUTE FUNCTION trigger_func('modified_a')
>
> foreign key associated internal triggers won't be copied to the new table.
> source table trigger associated comment will be copied to the new table,
> if INCLUDING COMMENTS is specified.
>

per
https://api.cirrus-ci.com/v1/artifact/task/5194724417470464/testrun/build/testrun/regress/regress/regression.diffs

there are many table name as t1 in regress test, add tests like
""CREATE TABLE t1 (a int, b text, c int);"
may result in error
+ERROR:  relation "t1" already exists
in some OS.
So I changed the table name to avoid parallel regess test failure.

hi.

in CreateTrigger, we have comments:

 * relOid, if nonzero, is the relation on which the trigger should be
 * created.  If zero, the name provided in the statement will be looked up.
 *
 * refRelOid, if nonzero, is the relation to which the constraint trigger
 * refers.  If zero, the constraint relation name provided in the statement
 * will be looked up as needed.

We can put these two parameters into the CreateTrigStmt.
change it from
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)
to:

CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
              Oid constraintOid, Oid indexOid,
              Oid funcoid, Oid parentTriggerOid, Node *whenClause,
              bool isInternal, bool in_partition)

This is needed, ProcessUtilitySlow->CreateTrigger don't have the new
target table relation OID information, using CreateTrigStmt.relation
would cause repeated name lookup issue.

v3-0001 and v3-0002 refactor the CreateTrigger function.
The parameters relOid and refRelOid are removed and instead added to the
CreateTrigStmt structure.

These two patch (v3-0001, v3-0002) will also be used in [1]
[1]: https://postgr.es/m/CACJufxGkqYrmwMdvUOUPet0443oUTgF_dKCpw3TfJiutfuywAQ@mail.gmail.com

v3-0003 is for CREATE TABLE LIKE INCLUDING TRIGGERS.


--
jian
https://www.enterprisedb.com/

On Mon, Dec 29, 2025 at 9:26 AM jian he <jian.universality@gmail.com> wrote:
>
> v3-0001 and v3-0002 refactor the CreateTrigger function.
> The parameters relOid and refRelOid are removed and instead added to the
> CreateTrigStmt structure.
>
> These two patch (v3-0001, v3-0002) will also be used in [1]
> [1]: https://postgr.es/m/CACJufxGkqYrmwMdvUOUPet0443oUTgF_dKCpw3TfJiutfuywAQ@mail.gmail.com
>
> v3-0003 is for CREATE TABLE LIKE INCLUDING TRIGGERS.
>
hi.

https://cirrus-ci.com/build/6583555523870720

https://api.cirrus-ci.com/v1/artifact/task/5664491007901696/testrun/build/testrun/pg_upgrade/002_pg_upgrade/data/regression.diffs

only FreeBSD fails. I suspect this is because pstrdup was not used in
generateClonedTriggerStmt.

now using pstrdup in generateClonedTriggerStmt; otherwise v4-0003 is identical
to v3-0003.


--
jian
https://www.enterprisedb.com/