I've created a function in PostgreSQL 11.10 to handle DDL change in pglogical to apply it into subscriber. Here is the function:
CREATE OR REPLACE FUNCTION public.intercept_ddl()
RETURNS event_trigger
LANGUAGE plpgsql
AS $function$
declare _qry text;
BEGIN
if (tg_tag='CREATE TABLE' or tg_tag='ALTER TABLE' or tg_tag='DROP TABLE') then
SELECT current_query() into _qry;
PERFORM pglogical.replicate_ddl_command('_qry', '{default}'), _qry;
end if;
END;
$function$
;
When I tried it, it threw an error:
ERROR: syntax error at or near "_qry" LINE 1: SELECT pglogical.replicate_ddl_command('_qry', '{default}') ^ QUERY: SELECT pglogical.replicate_ddl_command('_qry', '{default}') CONTEXT: during execution of queued SQL statement: _qry PL/pgSQL function intercept_ddl() line 6 at PERFORM
How to pass _qry
variable so we can use it replicate_ddl_command()
?
After fixing single quotes as instructed by Laurenz, I tried again with the command:
alter table test alter COLUMN description type text;
And got another error message:
ERROR: cannot drop active portal "pglogical" CONTEXT: during execution of queued SQL statement: alter table test alter COLUMN description type text; SQL statement "SELECT pglogical.replicate_ddl_command( _qry, '{default}')" PL/pgSQL function intercept_ddl() line 7 at PERFORM
Best Answer
In addition to fixing single quotes as instructed by Laurenz, you might have to use the (default!) replication set
'{ddl_sql}'
for the commandreplicate_ddl_command()
. The error messageindicates an open portal with a prepared statement that would conflict with the DDL command.
You did not disclose the actual event trigger, but could look like this
Filter command tags in the trigger itself, obviates the need to do so in the function. That's more efficient as the function is only called for applicable tags to begin with.
Quoting the manual of pglogical:
And: