by 0xcadams
Customers of our sync engine are complaining about Supabase not working with Zero (https://zero.rocicorp.dev/). After testing, it looks like manually updating publications will succeed but doesn't fire the expected DDL event. Zero depends on that event to refresh its replicated schema. Repro steps:
The user reports an issue where the ALTER PUBLICATION ... ADD TABLE ... command in Supabase does not trigger the expected DDL event, which is necessary for the Zero sync engine to function properly. The user provides a detailed SQL script to reproduce the issue, noting that the event trigger does not fire as expected.
BEGIN;
CREATE TABLE public.evt_pub_t1 (id int PRIMARY KEY);
CREATE TABLE public.evt_pub_t2 (id int PRIMARY KEY);
CREATE PUBLICATION evt_pub_repro FOR TABLE public.evt_pub_t1;
CREATE OR REPLACE FUNCTION public.evt_dbg_fn()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
DECLARE
cmd record;
BEGIN
RAISE NOTICE 'ddl_command_end fired: tag=%', tg_tag;
FOR cmd IN SELECT * FROM pg_event_trigger_ddl_commands() LOOP
RAISE NOTICE 'cmd: type=% identity=%', cmd.object_type, cmd.object_identity;
END LOOP;
END
$$;
CREATE EVENT TRIGGER evt_dbg_trg
ON ddl_command_end
EXECUTE FUNCTION public.evt_dbg_fn();
-- control: should fire notice
CREATE TABLE public.evt_control (id int PRIMARY KEY);
-- test case
ALTER PUBLICATION evt_pub_repro ADD TABLE public.evt_pub_t2;
ROLLBACK;
What happens: ALTER PUBLICATION ... ADD TABLE ... succeeds, but no ddl_command_end notice appears for it
Expected: ALTER PUBLICATION also fires the event trigger and appears in pg_event_trigger_ddl_commands()