RE: table-write trigger can bypass ATPrepChangePersistence

From: "Hayato Kuroda (Fujitsu)" <kuroda(dot)hayato(at)fujitsu(dot)com>
To: "Hayato Kuroda (Fujitsu)" <kuroda(dot)hayato(at)fujitsu(dot)com>
Cc: "'pgsql-hackers(at)lists(dot)postgresql(dot)org'" <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: RE: table-write trigger can bypass ATPrepChangePersistence
Date: 2026-09-18 07:41:53
Message-ID: TYRPR01MB12845091D307CE5A40AECFEF9F5872@TYRPR01MB12845.jpnprd01.prod.outlook.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

> Problem
> ======

One pointed I did not clarify is that FK can also be affected the issue. For now
normal tables cannot refer the UNLOGGED tables, but the table-rewrite event trigger
can break the rule. In below a normal table rewrite_to_logged referred a unlogged
table rewrite_referenced, which should not be accepted.
The patch proposed here could fix the issue.

```
postgres=# CREATE TABLE rewrite_referenced (a int PRIMARY KEY);
CREATE TABLE
postgres=# CREATE UNLOGGED TABLE rewrite_to_logged (a int REFERENCES rewrite_referenced);
CREATE TABLE
postgres=# CREATE FUNCTION test_evtrig_set_unlogged() RETURNS event_trigger
LANGUAGE plpgsql AS $$
BEGIN
IF pg_event_trigger_table_rewrite_oid() = 'rewrite_to_logged'::regclass THEN
EXECUTE 'ALTER TABLE rewrite_referenced SET UNLOGGED';
END IF;
END;
$$;
CREATE FUNCTION
postgres=# CREATE EVENT TRIGGER set_unlogged_during_rewrite ON table_rewrite
WHEN TAG IN ('ALTER TABLE')
EXECUTE FUNCTION test_evtrig_set_unlogged();
CREATE EVENT TRIGGER
postgres=# ALTER TABLE rewrite_to_logged SET LOGGED;
ALTER TABLE
postgres=# \d rewrite_to_logged
Table "public.rewrite_to_logged"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | |
Foreign-key constraints:
"rewrite_to_logged_a_fkey" FOREIGN KEY (a) REFERENCES rewrite_referenced(a)

postgres=# \d rewrite_referenced
Unlogged table "public.rewrite_referenced"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | not null |
Indexes:
"rewrite_referenced_pkey" PRIMARY KEY, btree (a)
Referenced by:
TABLE "rewrite_to_logged" CONSTRAINT "rewrite_to_logged_a_fkey" FOREIGN KEY (a) REFERENCES rewrite_referenced(a)
```

Best regards,
Hayato Kuroda
FUJITSU LIMITED

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Jakub Wartak 2026-09-18 07:49:34 Re: Init connection time grows quadratically
Previous Message Ajin Cherian 2026-09-18 07:32:06 Re: table-write trigger can bypass ATPrepChangePersistence