| 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
| 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 |