| From: | "Hayato Kuroda (Fujitsu)" <kuroda(dot)hayato(at)fujitsu(dot)com> |
|---|---|
| To: | "'pgsql-hackers(at)lists(dot)postgresql(dot)org'" <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
| Subject: | table-write trigger can bypass ATPrepChangePersistence |
| Date: | 2026-09-17 04:49:23 |
| Message-ID: | TYYPR01MB128415302C6203162977EA9CBF5B82@TYYPR01MB12841.jpnprd01.prod.outlook.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
Hi hackers,
While working on other projects, I found an issue $SUBJECT. Below describes the
exact problem, reproducer, and fix idea.
Problem
======
Unlogged tables cannot be included and excluded in a publication. When SET UNLOGGED
command is executed, validations are done in ATPrepChangePersistence() and the
backend raises an ERROR. However, rewrite-table event trigger can be fired after
the validation, and publication commands can be run at that time. Such commands
would bypass the validation thus unlogged tables could be in the pg_publication_rel.
Reproducer
=======
Below SQL commands could reproduce the inconsistency, unlogged table could be
excluded in the publication. Same thing can be said for the inclusion case.
```
postgres=# CREATE TABLE t (a int);
CREATE TABLE
postgres=# CREATE FUNCTION add_exclusion_during_rewrite()
RETURNS event_trigger
LANGUAGE plpgsql AS $$
BEGIN
IF pg_event_trigger_table_rewrite_oid() = 'public.t'::regclass THEN
EXECUTE
'CREATE PUBLICATION p FOR ALL TABLES EXCEPT (TABLE public.t)';
END IF;
END;
$$;
CREATE FUNCTION
postgres=# CREATE EVENT TRIGGER add_exclusion
ON table_rewrite
EXECUTE FUNCTION add_exclusion_during_rewrite();
CREATE EVENT TRIGGER
postgres=# ALTER TABLE t SET UNLOGGED;
ALTER TABLE
postgres=# SELECT c.relpersistence, pr.prexcept
FROM pg_class AS c
JOIN pg_publication_rel AS pr ON pr.prrelid = c.oid
WHERE c.oid = 'public.t'::regclass;
relpersistence | prexcept
----------------+----------
u | t
(1 row)
```
Fix idea
=====
My primitive idea is to re-validate just after an event trigger is fired. Attached
Patch implemented the idea accordingly. How do you feel?
How do you feel?
Best regards,
Hayato Kuroda
FUJITSU LIMITED
| Attachment | Content-Type | Size |
|---|---|---|
| 0001-Recheck-table-persistence-after-table_rewrite-trigge.patch | application/octet-stream | 12.9 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | solai v | 2026-09-17 04:53:12 | Re: Logical Implication |
| Previous Message | Henson Choi | 2026-09-17 04:13:38 | Re: [SQL/PGQ] Native executor for Graph query |