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@xxxxxxxxxxx]
Enviado el: miércoles, 3 de marzo de 2021 20:19
Para: Fontana Daniel C. (Desartec S.R.L.); pgsql-general@xxxxxxxxxxxxxxxxxxxx
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@xxxxxxxxxxx
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
