Schema in trigger in logical replication

On 3/3/21 2:35 PM, Fontana Daniel C. (Desartec S.R.L.) wrote:

Using postgres 12.5 in DBA schema, this trigger is executed when the table
is updated through a logical replication. Why is it necessary to name the
schema for it to work?

Because the search_path does include the schema?

When I update the table manually, if it Works.

Example.

This trigger function does not work

CREATE FUNCTION dba.ft_pos_sync_eco_tx()
RETURNS trigger
LANGUAGE 'plpgsql'
COST 100
VOLATILE NOT LEAKPROOF
AS $BODY$
begin
INSERT INTO pos_sync_eco_rx
( id_terminales,
fecha_y_hora_tx,
fecha_y_hora_rx,
dato,
usuario,
fecha_y_hora_proceso )
VALUES ( new.id_terminales,
localtimestamp,
localtimestamp,
new.dato,
new.usuario ,
localtimestamp )
ON CONFLICT (id_terminales)
DO UPDATE SET (fecha_y_hora_tx,
fecha_y_hora_rx,
dato,
usuario,
fecha_y_hora_proceso ) =
(new.fecha_y_hora_tx,
localtimestamp,
new.dato,
new.usuario,
new.fecha_y_hora_proceso );

return new;

end
$BODY$;

This trigger function, if it works

CREATE FUNCTION dba.ft_pos_sync_eco_tx()
RETURNS trigger
LANGUAGE 'plpgsql'
COST 100
VOLATILE NOT LEAKPROOF
AS $BODY$
begin
INSERT INTO dba.pos_sync_eco_rx
( id_terminales,
fecha_y_hora_tx,
fecha_y_hora_rx,
dato,
usuario,
fecha_y_hora_proceso )
VALUES ( new.id_terminales,
localtimestamp,
localtimestamp,
new.dato,
new.usuario ,
localtimestamp )
ON CONFLICT (id_terminales)
DO UPDATE SET (fecha_y_hora_tx,
fecha_y_hora_rx,
dato,
usuario,
fecha_y_hora_proceso ) =
(new.fecha_y_hora_tx,
localtimestamp,
new.dato,
new.usuario,
new.fecha_y_hora_proceso );

return new;

end
$BODY$;

--
Adrian Klaver
adrian.klaver@aklaver.com

On 3/3/21 3:58 PM, Fontana Daniel C. (Desartec S.R.L.) wrote:
Please reply to list also.
Ccing list.
Also please do not top post, use inline and/or bottom posting.

When the update is manual, it works.
It does not work when the update is done using logical replication.
It is as if the logical replication wizard did not use the search_path

Replication would imply at least two database instances in use. If they
both don't have the same search_path set then there would be a problem.
In psql does:

SHOW search_path;

return the same thing on both sides of the replication?

FYI, your life will be easier if you schema qualify objects whenever
possible in any case.

-----Mensaje original-----
De: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
Enviado el: miércoles, 3 de marzo de 2021 20:19
Para: Fontana Daniel C. (Desartec S.R.L.); pgsql-general@lists.postgresql.org
Asunto: Re: Schema in trigger in logical replication

On 3/3/21 2:35 PM, Fontana Daniel C. (Desartec S.R.L.) wrote:

Using postgres 12.5 in DBA schema, this trigger is executed when the
table is updated through a logical replication. Why is it necessary to
name the schema for it to work?

Because the search_path does include the schema?

When I update the table manually, if it Works.

Example.

This trigger function does not work

CREATE FUNCTION dba.ft_pos_sync_eco_tx()
RETURNS trigger
LANGUAGE 'plpgsql'
COST 100
VOLATILE NOT LEAKPROOF
AS $BODY$
begin
INSERT INTO pos_sync_eco_rx
( id_terminales,
fecha_y_hora_tx,
fecha_y_hora_rx,
dato,
usuario,
fecha_y_hora_proceso )
VALUES ( new.id_terminales,
localtimestamp,
localtimestamp,
new.dato,
new.usuario ,
localtimestamp )
ON CONFLICT (id_terminales)
DO UPDATE SET (fecha_y_hora_tx,
fecha_y_hora_rx,
dato,
usuario,
fecha_y_hora_proceso ) =
(new.fecha_y_hora_tx,
localtimestamp,
new.dato,
new.usuario,
new.fecha_y_hora_proceso );

return new;

end
$BODY$;

This trigger function, if it works

CREATE FUNCTION dba.ft_pos_sync_eco_tx()
RETURNS trigger
LANGUAGE 'plpgsql'
COST 100
VOLATILE NOT LEAKPROOF
AS $BODY$
begin
INSERT INTO dba.pos_sync_eco_rx
( id_terminales,
fecha_y_hora_tx,
fecha_y_hora_rx,
dato,
usuario,
fecha_y_hora_proceso )
VALUES ( new.id_terminales,
localtimestamp,
localtimestamp,
new.dato,
new.usuario ,
localtimestamp )
ON CONFLICT (id_terminales)
DO UPDATE SET (fecha_y_hora_tx,
fecha_y_hora_rx,
dato,
usuario,
fecha_y_hora_proceso ) =
(new.fecha_y_hora_tx,
localtimestamp,
new.dato,
new.usuario,
new.fecha_y_hora_proceso );

return new;

end
$BODY$;

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Adrian Klaver
adrian.klaver@aklaver.com

Adrian Klaver <adrian.klaver@aklaver.com> writes:

On 3/3/21 3:58 PM, Fontana Daniel C. (Desartec S.R.L.) wrote:

When the update is manual, it works.
It does not work when the update is done using logical replication.
It is as if the logical replication wizard did not use the search_path

Replication would imply at least two database instances in use. If they
both don't have the same search_path set then there would be a problem.

I'm fairly sure that replication workers run with search_path set
to just "pg_catalog" for security reasons. Any user-written code
that needs to execute in a replication worker *must* schema-qualify
all references to non-system objects.

As a general rule, code that runs in expression indexes, check
constraints, and the like needs to be written to not make assumptions
about what search path it's invoked with. Just fix it; your life
will be less painful.

regards, tom lane