Re: Reindex "locked" standby database

> On 15 Dec 2021, at 00:52, Mladen Gogala wrote: > > On 12/14/21 22:37, Michael Paquier wrote: >> You are referring to the startup process that replays WAL, right? >> Without having an idea about the type of workload your primary and/or >> standbys are facing, as well as an idea of the

Re: Reindex "locked" standby database

Micheal, Thanks for much for the quick response. > On 15 Dec 2021, at 00:37, Michael Paquier wrote: > > On Wed, Dec 15, 2021 at 12:15:27AM -0300, Martín Fernández wrote: >> The reindex went fine in the primary database and in one of our >> standby. The other standby that we also operate for

Re: Reindex "locked" standby database

On 12/14/21 22:37, Michael Paquier wrote: You are referring to the startup process that replays WAL, right? Without having an idea about the type of workload your primary and/or standbys are facing, as well as an idea of the configuration you are using on both (hot_standby_feedback for one), I

Re: Reindex "locked" standby database

On Wed, Dec 15, 2021 at 12:15:27AM -0300, Martín Fernández wrote: > The reindex went fine in the primary database and in one of our > standby. The other standby that we also operate for some reason > ended up in a state where all transactions were locked by the WAL > process and the WAL process

Reindex "locked" standby database

Hello pg hackers! Today we had to run a `REINDEX table CONCURRENTLY my_table;` in our production database due to considerable index bloat. We used to deal with this problem in the past by using pg_repack but we stopped using it because our data replication tool doesn’t support “re creating”

Re: Why can't I have a "language sql" anonymous block?

> david.g.johns...@gmail.com wrote: > >> On Monday, December 13, 2021, Bryn Llewellyn > > wrote: >> >> There must be a reason to prefer a “language sql” procedure over a “language >> plpgsql” procedure—otherwise the former wouldn’t be supported. > > I would say that

Re: Why can't I have a "language sql" anonymous block?

On Monday, December 13, 2021, Bryn Llewellyn wrote: > > > There must be a reason to prefer a “language sql” procedure over a > “language plpgsql” procedure—otherwise the former wouldn’t be supported. > I would say that is true for functions. I wouldn’t assume that for procedures - it’s probable

Re: Properly handling aggregate in nested function call

Matt Magoffin writes: > Any ideas what I’m doing wrong here? The source is available here: > https://github.com/SolarNetwork/aggs_for_vecs/blob/9e742cdc32a113268fd3c1f928c8ac724acec9f5/vec_agg_mean.c Hmm, I think you're abusing the ArrayBuildState API. In particular, what guarantees that the

Re: Why can't I have a "language sql" anonymous block?

On 12/14/21 11:30, Bryn Llewellyn wrote: /Adrian Klaver wrote:/ /Bryn wrote:/ Thanks for the links to the articles on the inlining of “language sql” functions into SQL statements that use them. (I noted “the exact conditions which apply to inlining are somewhat complex and not well

Re: Why can't I have a "language sql" anonymous block?

Adrian Klaver wrote: Bryn wrote: > Well… that’s the answer: “nobody thought it’d be useful”. Thanks, Tom. > The difference between using a “language sql” anonymous block and just > executing the contained SQL statements? is partly a clear declaration of the > intent of your code and a guarantee

Re: locks within select

Excellent ! thanks a lot, it was exactly what I was looking for. Marc MILLAS Senior Architect +33607850334 www.mokadb.com On Tue, Dec 14, 2021 at 6:14 PM Laurenz Albe wrote: > On Tue, 2021-12-14 at 13:38 +0100, Marc Millas wrote: > > but... when you do setup a streaming replication, there

Re: When Update balloons memory

On Tue, 14 Dec 2021 11:18:07 -0500 Tom Lane wrote: > > This leak is new in v14, possibly that's why Vincent didn't reproduce it. Indeed, I'm on v11 -- Bien à vous, Vincent Veyron https://marica.fr Gestion des contentieux juridiques, des contrats et

Postgresql DB on virtual machine in Ceph cluster

Hi Does anyone have experience with PG in virtual machine running in Ceph cluster? I would very much like the opion from Magnus Hagander as the PG specialist he is. Thanks Poul -- Med venlig hilsen / Best regards Poul Kristensen

Re: locks within select

On Tue, 2021-12-14 at 13:38 +0100, Marc Millas wrote: > but... when you do setup a streaming replication, there is, in > postgresql.conf, a variable:  > max_standby_streaming_delay = 30s # max delay before canceling queries when > reading streaming WAL; > > as the secondary is, by nature, read

Re: When Update balloons memory

Klaudie Willis writes: > I'll repost it here, corrected, for others to use who wants to exhaust their > memory: > --PG-14.1 This leak is new in v14, possibly that's why Vincent didn't reproduce it. regards, tom lane

Re: When Update balloons memory

So sorry about that; I'll repost it here, corrected, for others to use who wants to exhaust their memory: --PG-14.1 CREATE TABLE public.part_main ( txid bigint, actiondate timestamp without time zone NOT NULL ); insert into part_main select x, '2019-06-01'::timestamp + x%365 * interval

Re: timestamp default current_timestamp not working

On 12/14/21 08:00, Sanjay Minni wrote: Its an insert and my insert SQL contains the column timestamp and value nil. Will it work if the SQL contains timestamp through value is nil No nil(NULL) is a valid value for the field as you have not specified NOT NULL on the column. If you do add that

Re: timestamp default current_timestamp not working

On Tuesday, December 14, 2021, Sanjay Minni wrote: > Its an insert and my insert SQL contains the column timestamp and value > nil. > Will it work if the SQL contains timestamp through value is nil > >> >> If you explicitly specify NULL for the value of the column then there is no need for the

Re: timestamp default current_timestamp not working

Show us the code (especially since there is no value "nil" in SQL). On 12/14/21 10:00 AM, Sanjay Minni wrote: Its an insert and my insert SQL contains the column timestamp and value nil. Will it work if the SQL contains timestamp through value is nil On Tue, 14 Dec, 2021, 9:20 pm Adrian

Re: timestamp default current_timestamp not working

Its an insert and my insert SQL contains the column timestamp and value nil. Will it work if the SQL contains timestamp through value is nil On Tue, 14 Dec, 2021, 9:20 pm Adrian Klaver, wrote: > On 12/14/21 07:36, Sanjay Minni wrote: > > Hi > > > > I am not getting the timestamp value which i

Re: When Update balloons memory

On Tue, 14 Dec 2021 08:16:08 + Klaudie Willis wrote: > CREATE INDEX partindx ON public.part_main USING btree ((actiondate)::date); > -- mem bug? Nope, syntax error ERROR: syntax error at or near "::" LINE 1: ...indx_1 ON public.part_main USING btree ((actiondate)::date);

Re: timestamp default current_timestamp not working

On 12/14/21 07:36, Sanjay Minni wrote: Hi I am not getting the timestamp value which i am expecting to be automatically inserted in the column defined: ... "timestamp" timestamp with time zone DEFAULT CURRENT_TIMESTAMP, ... (I see the above definition in pgadmin4 in the tab SQL. I have set it

Re: timestamp default current_timestamp not working

On 12/14/21 9:36 AM, Sanjay Minni wrote: Hi I am not getting the timestamp value which i am expecting to be automatically inserted in the column defined: ... "timestamp" timestamp with time zone DEFAULT CURRENT_TIMESTAMP, ... (I see the above definition in pgadmin4 in the tab SQL. I have set

timestamp default current_timestamp not working

Hi I am not getting the timestamp value which i am expecting to be automatically inserted in the column defined: ... "timestamp" timestamp with time zone DEFAULT CURRENT_TIMESTAMP, ... (I see the above definition in pgadmin4 in the tab SQL. I have set it thru pgadmin4 table->properties->columns

Re: locks within select

On Tuesday, December 14, 2021, Marc Millas wrote: > > > So, if its not a locking scheme, what can block hundreds of transaction to > get to the secondary ??? > The transactions are present on the secondary. It just realizes that applying them may cause problems (think drop table or truncate)

Re: locks within select

I did read this, before asking my question... but... when you do setup a streaming replication, there is, in postgresql.conf, a variable: max_standby_streaming_delay = 30s # max delay before canceling queries when reading streaming WAL; as the secondary is, by nature, read only, I was wondering

Re:

On Tue, Dec 14, 2021 at 5:30 PM Dennis wrote: > > Hi, > > Can I add custom sqls for regression tests (pg_regress) ? > If it can be added how? > Check this: https://wiki.postgresql.org/wiki/Regression_test_authoring Regards, Amul

[no subject]

Hi, Can I add custom sqls for regression tests (pg_regress) ? If it can be added how? Dennis

Re: LibPQ: Lifetime of PGresult passed in to PQnoticeReceiver callback

пн, 13 дек. 2021 г. в 11:43, Dominique Devienne : > > Hi, > > The doc at > https://www.postgresql.org/docs/current/libpq-notice-processing.html is not > clear to me on whether it should be PQclear'd or not. Who manages the > lifetime of that PGresult? libpq. That PGresult cleared by libpq right

Re: LibPQ: Lifetime of PGresult passed in to PQnoticeReceiver callback

Hi again. Is this not the right ML? Which PostgreSQL ML should this question be asked to, to have a chance to get an answer? I'm new to this particular ML, and I'm surprised by the silence. Thanks, --DD On Mon, Dec 13, 2021 at 9:46 AM Dominique Devienne wrote: > Hi, > > The doc at >

Re: When Update balloons memory

Hi, Turns out the base case is simpler than I thought. Not involving partitions at all CREATE TABLE public.part_main ( txid bigint, actiondate timestamp without time zone NOT NULL ); insert into part_main select x, '2019-06-01'::timestamp + x%365 * interval '1 day' from