Re: Logical replication stuck and no data being copied

After further investigation, I discovered the following. On aurora ( the subscriber). select now()::timestamp(0),a.subname,b.srsubstate,count(*) from pg_subscription_rel b, pg_subscription a where b.srsubid=a.oid group by 1,2,3 order by 2,3; now | subname | srsub

permanent setting of config variables

Hi, I have a few PL/pgSQL functions that use queires like "SHOW company.product INTO _product_name" and "SHOW company.cluster INTO _cluster_number". But these are failing because I don't know how to set those values on a permanent basis, or maybe on startup would also be ok. I did read the "ALTE

Re: Slow index creation

On Wed, Feb 17, 2021 at 08:40:17PM +0100, Paul van der Linden wrote: > The st_area calculation is done mostly once or sometimes twice for each geom, > and I suspect that can't explain the factor 20 slower. > Creating an index with only one st_area calculation is also done rather > quickly. In th

Re: permanent setting of config variables

On Thu, 2021-02-18 at 09:09 +, Joao Miguel Ferreira wrote: > I have a few PL/pgSQL functions that use queires like "SHOW company.product > INTO _product_name" and "SHOW company.cluster INTO _cluster_number". > But these are failing because I don't know how to set those values on a > permanent

when is pg_stat_archiver.stats_reset modified?

Hi all, running on 12.5, but I guess this does not mind, I cannot understand when the value of pg_stat_archiver.stats_reset field is modified. I've executed a pg_stat_reset() but it did not affected the specified field. I suppose that field is not "resettable" by the administrator, as I tried to, b

Re: permanent setting of config variables

Hi, On Thu, Feb 18, 2021 at 10:52 AM Laurenz Albe wrote: > On Thu, 2021-02-18 at 09:09 +, Joao Miguel Ferreira wrote: > > I have a few PL/pgSQL functions that use queires like "SHOW > company.product INTO _product_name" and "SHOW company.cluster INTO > _cluster_number". > > But these are fa

Re: when is pg_stat_archiver.stats_reset modified?

2021年2月18日(木) 20:21 Luca Ferrari : > Hi all, > running on 12.5, but I guess this does not mind, I cannot understand > when the value of pg_stat_archiver.stats_reset field is modified. > I've executed a pg_stat_reset() but it did not affected the specified > field. > I suppose that field is not "re

Re: when is pg_stat_archiver.stats_reset modified?

On Thu, 2021-02-18 at 12:20 +0100, Luca Ferrari wrote: > running on 12.5, but I guess this does not mind, I cannot understand > when the value of pg_stat_archiver.stats_reset field is modified. > I've executed a pg_stat_reset() but it did not affected the specified field. > I suppose that field is

Re: permanent setting of config variables

On Thu, 2021-02-18 at 11:42 +, Joao Miguel Ferreira wrote: > On Thu, Feb 18, 2021 at 10:52 AM Laurenz Albe > wrote: > > On Thu, 2021-02-18 at 09:09 +, Joao Miguel Ferreira wrote: > > > I have a few PL/pgSQL functions that use queires like "SHOW > > > company.product INTO _product_name" a

Re: permanent setting of config variables

On 2/18/21 4:09 AM, Joao Miguel Ferreira wrote: > I have a few PL/pgSQL functions that use queires like "SHOW company.product > INTO > _product_name" and "SHOW company.cluster INTO _cluster_number". > > But these are failing because I don't know how to set those values on a > permanent basis, or

Re: permanent setting of config variables

Hi, On Thu, Feb 18, 2021 at 4:05 PM Joe Conway wrote: > On 2/18/21 4:09 AM, Joao Miguel Ferreira wrote: > > I have a few PL/pgSQL functions that use queires like "SHOW > company.product INTO > > _product_name" and "SHOW company.cluster INTO _cluster_number". > > > > But these are failing because

yum update for postgresql rpms

RHEL 7.9 PostgreSQL 12 (12.5.1 to 12.6.1) I am updating RPMs for postgres and was interested to know whether this is indeed expected behavior for the rpm update: When the "yum update" is executed, postgresql rpms are updated. It looks at that point, postgresql (postmaster) processes automatica

Re: Slow index creation

> > https://www.depesz.com/2010/03/18/profiling-stored-proceduresfunctions/ Thanks for this reference. I enjoy your blog, but haven't made the time to read all the archives somehow. Stuff doesn't stick very well when it isn't yet "needed" info besides. I have seen overhead from 'raise notice' in

Re: Slow index creation

On Thu, Feb 18, 2021 at 10:24:25AM -0700, Michael Lewis wrote: > [1]https://www.depesz.com/2010/03/18/profiling-stored-proceduresfunctions/ > > Thanks for this reference. I enjoy your blog, but haven't made the time to > read all the archives somehow. Stuff doesn't stick very > well when it isn

Re: How to post to this mailing list from a web based interface

On Mon, Feb 15, 2021 at 11:47 PM RaviKrishna wrote: > > > Nable didn't seem to work. I got a bounce iirc. > > Not sure what you are doing? My previous reply a day ago and this reply are > from Nable with no issues. Not wtih "no issues". A lot of email sent from Nabble through the lists end up b

Error with pg_dump (of data), with --role

Hi. I'm trying to do a data dump with pg_dump using RLS and --set-role, but am getting an error, and I'm not understanding why. With this command, run as postgres: pg_dump -p 5433 -O --role=rcafe_TACOMA --enable-row-security --column-inserts -a -f ~/ag_tacoma_data.pg_dump ag_rcafe I get [Multipl

Re: Error with pg_dump (of data), with --role

On 2/18/21 6:18 PM, Ken Tanzer wrote: Hi.  I'm trying to do a data dump with pg_dump using RLS and --set-role, but am getting an error, and I'm not understanding why. With this command, run as postgres: pg_dump -p 5433 -O --role=rcafe_TACOMA --enable-row-security --column-inserts -a -f ~/a

Re: Error with pg_dump (of data), with --role

On Thu, Feb 18, 2021 at 5:23 PM Rob Sargent wrote: > > > > > There is only one schema, public. > > > > > > I suspect it is because "set role" doesn't "set search_path" > > > I'm not sure what you mean or are suggesting by that. Is there something I'm supposed to do to set the search path? Is th

Re: Error with pg_dump (of data), with --role

> On Feb 18, 2021, at 8:00 PM, Ken Tanzer wrote: > >  > > >> On Thu, Feb 18, 2021 at 5:23 PM Rob Sargent wrote: >> >> > >> > There is only one schema, public. >> > >> > >> >> I suspect it is because "set role" doesn't "set search_path" >> >> > I'm not sure what you mean or are sugges

Re: Error with pg_dump (of data), with --role

Ken Tanzer writes: > I'm not sure what you mean or are suggesting by that. Is there something > I'm supposed to do to set the search path? Is that a known bug in > pg_dump? Something else? As mentioned, there is only one schema There was a security change to pg_dump a few years ago to mak

RE: Syntax checking DO blocks and ALTER TABLE statements?

>From: Ron > >How does one go about syntax checking this? > >do $$ >begin if exists (select 1 from information_schema.table_constraints >where constraint_name = 'error_to_web_service_error') then > raise notice 'EXISTS error_to_web_service_error'; > else > ALTER TAB

Re: Syntax checking DO blocks and ALTER TABLE statements?

pá 19. 2. 2021 v 6:09 odesílatel Kevin Brannen napsal: > >From: Ron > > > >How does one go about syntax checking this? > > > >do $$ > >begin if exists (select 1 from information_schema.table_constraints > >where constraint_name = 'error_to_web_service_error') then > > raise n