logical replication worker can't find postgis function
Hi! 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? -- Willy-Bas Loos
Logical subscription / publication lifetimes
If I define a publication at time Tp, then load some data on the publisher, then start a subscription at time Ts, then load some more data on the publisher, does the subscriber get data from Tp or Ts onwards? Also, if a subscription is disabled and then re-enabled does it lose the data inbetween, or is it back-filled? I am not finding the answers to these questions in the docs at https://www.postgresql.org/docs/current/logical-replication.html but maybe I am overlooking something. The link above does mention copying an existing table which may imply Ts? Thanks, Andrew
Re: logical replication worker can't find postgis function
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
Re: logical replication worker can't find postgis function
On Fri, Apr 22, 2022 at 3:20 PM Laurenz Albe wrote: > > 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. > Thanks a lot! Do you mean that all trigger functions are bad and dangerous, or just mine? Do you have any suggestions for an alternative? Cheers, -- Willy-Bas Loos
Re: logical replication worker can't find postgis function
On Fri, 2022-04-22 at 15:26 +0200, Willy-Bas Loos wrote: > On Fri, Apr 22, 2022 at 3:20 PM Laurenz Albe wrote: > > > > 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. > > Thanks a lot! > Do you mean that all trigger functions are bad and dangerous, or just mine? > Do you have any suggestions for an alternative? There is nothing wrong per se with using trigger functions. But, to attempt a generic statement, any function that fails if you change "search_path" is a potential problem. If your application makes sure that "search_path" is always set correctly, the problem is smaller. If highly privileged processes call the function, the problem becomes worse, because the potential damage is bigger. The best way to make sure nothing can happen is to create all functions with a hard-wired "search_path". Then nothing can go wrong. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: logical replication worker can't find postgis function
OK thanks for the help, have a nice weekend! On Fri, Apr 22, 2022 at 3:39 PM Laurenz Albe wrote: > On Fri, 2022-04-22 at 15:26 +0200, Willy-Bas Loos wrote: > > On Fri, Apr 22, 2022 at 3:20 PM Laurenz Albe > wrote: > > > > > > 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. > > > > Thanks a lot! > > Do you mean that all trigger functions are bad and dangerous, or just > mine? > > Do you have any suggestions for an alternative? > > There is nothing wrong per se with using trigger functions. > > But, to attempt a generic statement, any function that fails if you change > "search_path" > is a potential problem. > > If your application makes sure that "search_path" is always set correctly, > the problem > is smaller. > > If highly privileged processes call the function, the problem becomes > worse, because the > potential damage is bigger. > > The best way to make sure nothing can happen is to create all functions > with a > hard-wired "search_path". Then nothing can go wrong. > > Yours, > Laurenz Albe > -- > Cybertec | https://www.cybertec-postgresql.com > > -- Willy-Bas Loos
Re: Logical subscription / publication lifetimes
On Fri, Apr 22, 2022 at 5:00 AM andrew cooke wrote: > > If I define a publication at time Tp, then load some data on the > publisher, then start a subscription at time Ts, then load some more > data on the publisher, does the subscriber get data from Tp or Ts > onwards? > > It depends. By default, neither, the publisher is publishing the entire contents of the table and the subscriber will do everything necessary to replicate those contents in their entirety. If you specify copy_data = false I'm not sure what you end up with initially or after disable. My guess is the subscription defines the first transaction it cares about when it connects to the publisher, defaulting to the most recent publisher transaction (all older transactions would be handled via copy_data = true) but then so long as the slot remains active the publisher will place the data into the slot even while the subscriber is not active and the subscriber will receive all of it next time it comes online/re-enables. David J.
Re: Logical subscription / publication lifetimes
Ah, thanks! I should have read the documentation of all the parameters! So the portion of data that is covered by "copy_data" is going to reflect updates and deletes prior to the creation of the slot even if "publish=insert" (only)? This makes sense because I can't see how else it could be practically implemented, but just want to be sure I am understanding. The idea that there are two phases (copy existing data then replicate operations) is a big help. Thanks again, Andrew On Fri, Apr 22, 2022 at 09:13:15AM -0700, David G. Johnston wrote: > On Fri, Apr 22, 2022 at 5:00 AM andrew cooke wrote: > > > > > If I define a publication at time Tp, then load some data on the > > publisher, then start a subscription at time Ts, then load some more > > data on the publisher, does the subscriber get data from Tp or Ts > > onwards? > > > > > It depends. By default, neither, the publisher is publishing the entire > contents of the table and the subscriber will do everything necessary to > replicate those contents in their entirety. > > If you specify copy_data = false I'm not sure what you end up with > initially or after disable. My guess is the subscription defines the first > transaction it cares about when it connects to the publisher, defaulting to > the most recent publisher transaction (all older transactions would be > handled via copy_data = true) but then so long as the slot remains active > the publisher will place the data into the slot even while the subscriber > is not active and the subscriber will receive all of it next time it comes > online/re-enables. > > David J.
Re: autovacuum_freeze_max_age on append-only tables
Thanks David In any case the cost-based stuff throttles I/O only (per the docs at least) but even while sleeping it still holds its lock. And it won't be kicked off of the lock by other processes. I don't see where it is documented that the autovacuum cost settings are altered during the anti-wraparound vacuum so I presume it will still sleep by default. I knew I was misunderstanding something. I had picked up the impression that the vacuum process cost_delay released the lock for the period. Not just do nothing. Seems like that would be worth mentioning in the Docs. I'm learning this from the inside out in the tradition of "well someone has to do it". I'm sure I'm not alone. -Senor
