On Fri, 2022-04-22 at 13:25 +0200, Willy-Bas Loos wrote: > I'm using logical replication on postgresql 13. On the subscriber, there's a > trigger on a table that calculates the area of the geometry that's in another > column. > I enabled the trigger with > ALTER TABLE atable ENABLE ALWAYS TRIGGER atrigger; > > But the logical replication worker can't find st_area: > 2022-04-22 13:14:11.244 CEST [1932237] LOG: logical replication apply worker > for subscription "ba_acc1" has started > 2022-04-22 13:14:11.282 CEST [1932237] ERROR: function > st_area(public.geometry) does not exist at character 14 > 2022-04-22 13:14:11.282 CEST [1932237] HINT: No function matches the given > name and argument types. You might need to add explicit type casts. > 2022-04-22 13:14:11.282 CEST [1932237] QUERY: SELECT > round(st_area(NEW.epsg28992_geom)) > 2022-04-22 13:14:11.282 CEST [1932237] CONTEXT: PL/pgSQL function > util.location_extras() line 3 at assignment > 2022-04-22 13:14:11.285 CEST [1562110] LOG: background worker "logical > replication worker" (PID 1932237) exited with exit code 1 > > The trigger works well when I fire it in a normal update query. > How can this happen and how can I resolve this?
The trigger function is bad and dangerous, because it relies on the current setting of "search_path". You notice that with logical replication, because "search_path" is empty to avoid security problems. Fix your function: ALTER FUNCTION trigger_function() SET search_path = public; Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
