From: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
---|---|
To: | Willy-Bas Loos <willybas(at)gmail(dot)com>, pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: logical replication worker can't find postgis function |
Date: | 2022-04-22 13:20:41 |
Message-ID: | 8d54fcdc3ea562e2fd1ada68997aa7377bda5187.camel@cybertec.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Willy-Bas Loos | 2022-04-22 13:26:37 | Re: logical replication worker can't find postgis function |
Previous Message | andrew cooke | 2022-04-22 12:00:39 | Logical subscription / publication lifetimes |