Re: Schema in trigger in logical replication

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(dot)klaver(at)aklaver(dot)com]
> Enviado el: miércoles, 3 de marzo de 2021 20:19
> Para: Fontana Daniel C. (Desartec S.R.L.); pgsql-general(at)lists(dot)postgresql(dot)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(dot)klaver(at)aklaver(dot)com
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com