Bug in pg_restore with EventTrigger in parallel mode
Fabrízio de Royes Mello <fabriziomello@gmail.com>
From: Fabrízio de Royes Mello <fabriziomello@gmail.com>
To: Pgsql Hackers <pgsql-hackers@postgresql.org>
Date: 2020-02-12T16:59:05Z
Lists: pgsql-hackers
Attachments
- fix_pg_restore_parallel_with_event_trigger_v1.patch (text/x-patch) patch v1
Hi all,
Today digging into a customer issue about errors in pg_restore I realized
that pg_restore dispatch a worker to restore EventTrigger
during restore_toc_entries_parallel. IMHO EventTriggers should be restored
during the restore_toc_entries_postfork in serial mode.
For example this simple database schema:
BEGIN;
CREATE TABLE foo(c1 bigserial NOT NULL, c2 varchar(100) NOT NULL, PRIMARY
KEY (c1));
INSERT INTO foo (c2) SELECT 'Foo '||id FROM generate_series(0,10) id;
CREATE INDEX foo_1 ON foo (c2);
CREATE TABLE bar(c1 bigserial NOT NULL, c2 bigint REFERENCES public.foo, c3
varchar(100), PRIMARY KEY (c1));
INSERT INTO bar (c2, c3) SELECT (random()*10)::bigint+1, 'Bar '||id FROM
generate_series(1,10000) id;
CREATE INDEX bar_1 ON bar (c2);
CREATE INDEX bar_2 ON bar (c3);
CREATE OR REPLACE FUNCTION f_test_ddl_trigger()
RETURNS event_trigger AS
$$
DECLARE
r RECORD;
BEGIN
FOR r IN
SELECT objid, objsubid, schema_name, objid::regclass::text AS
table_name, command_tag, object_type, object_identity
FROM pg_event_trigger_ddl_commands()
LOOP
RAISE INFO 'RUN EVENT TRIGGER %', r;
END LOOP;
END;
$$
LANGUAGE plpgsql;
CREATE EVENT TRIGGER test_ddl_trigger
ON ddl_command_end
EXECUTE PROCEDURE f_test_ddl_trigger();
COMMIT;
Running the dump:
$ bin/pg_dump -Fc -f /tmp/teste.dump fabrizio
Restoring with one worker everything is ok:
fabrizio@macanudo:~/pgsql
$ bin/pg_restore -Fc -d fabrizio_restore_serial /tmp/teste.dump | grep 'RUN
EVENT TRIGGER'
Running with more the one worker:
fabrizio@macanudo:~/pgsql
$ bin/pg_restore -Fc -j2 -d fabrizio_restore_parallel /tmp/teste.dump |
grep 'RUN EVENT TRIGGER'
pg_restore: INFO: RUN EVENT TRIGGER (16906,0,public,public.bar,"ALTER
TABLE",table,public.bar)
In parallel mode it's firing the EventTrigger and it can't be happen.
Poking around it I did some test with attached just to leave EventTriggers
in pending_list to process it in restore_toc_entries_postfork and
everything is ok, but my solution is very ugly, so maybe we need to invent
a new RestorePass to take care of it like RESTORE_PASS_ACL and
RESTORE_PASS_REFRESH. I can provide a more polished patch if it'll be a
good way to do that.
Regards,
--
Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
Commits
-
Fix pg_dump/pg_restore to restore event triggers later.
- 8728b2c70357 13.0 landed
- fab5456356c2 9.6.18 landed
- f5d49f22653e 11.8 landed
- 4c40b27b5064 12.3 landed
- 475b061c8675 10.13 landed
- 0b02476442d5 9.5.22 landed