Re: replication primary writting infinite number of WAL files

On 11/24/23 14:01, Les wrote: A single sequence for all id columns across all tables? How is the sequence value landing in the id column? In most cases it is by using "nextval(seq_name)" in the SQL statement. But sometimes the sequence value is taken first, and then multiple inserts

Re: replication primary writting infinite number of WAL files

> > > A single sequence for all id columns across all tables? > > How is the sequence value landing in the id column? > In most cases it is by using "nextval(seq_name)" in the SQL statement. But sometimes the sequence value is taken first, and then multiple inserts are sent with fixed increasing va

Re: pg_getnameinfo_all() failed: Temporary failure in name resolution

On Fri, Nov 24, 2023 at 4:26 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Friday, November 24, 2023, Ron Johnson wrote: >> >> >> The second "way" sounds interesting, but what is it filled with? >> > > What does it matter? It’s an internal detail that apparently gets exposed > a

Re: replication primary writting infinite number of WAL files

On 11/24/23 1:05 PM, Les wrote: >> Sequence is incremented by 100,  so for example, between 2023-11-24 >> 10:20:00 and 2023-11-24 10:21:00 it went up 62188671 steps. I think it >> is not possible to insert 62188671 rows into a table. A psql function >> might be able

Re: pg_getnameinfo_all() failed: Temporary failure in name resolution

On Friday, November 24, 2023, Ron Johnson wrote: > > > The second "way" sounds interesting, but what is it filled with? > What does it matter? It’s an internal detail that apparently gets exposed as [unknown] appearing in your log file where the client ip address would normally be. > > I added

Re: pg_getnameinfo_all() failed: Temporary failure in name resolution

On Fri, Nov 24, 2023 at 3:46 PM Adrian Klaver wrote: > On 11/24/23 12:30, Ron Johnson wrote: > > PG 9.6.24 (Yes, I know it's EOL.) > > > > I'm seeing lots of these in the postgresql log file: > > 2023-11-24 15:09:02.224 EST [unknown] [unknown] > > 18163 [unknown]

Re: replication primary writting infinite number of WAL files

> > > >> Sequence is incremented by 100, so for example, between 2023-11-24 > >> 10:20:00 and 2023-11-24 10:21:00 it went up 62188671 steps. I think it > >> is not possible to insert 62188671 rows into a table. A psql function > >> might be able to increment a sequence 62M times / minute, I'm not

Re: pg_getnameinfo_all() failed: Temporary failure in name resolution

On 11/24/23 12:30, Ron Johnson wrote: PG 9.6.24 (Yes, I know it's EOL.) I'm seeing lots of these in the postgresql log file: 2023-11-24 15:09:02.224 EST             [unknown]       [unknown] 18163           [unknown]       01000   WARNING:  01000: pg_getnameinfo_all() failed: Temporary f

pg_getnameinfo_all() failed: Temporary failure in name resolution

PG 9.6.24 (Yes, I know it's EOL.) I'm seeing lots of these in the postgresql log file: 2023-11-24 15:09:02.224 EST [unknown] [unknown] 18163 [unknown] 01000 WARNING: 01000: pg_getnameinfo_all() failed: Temporary failure in name resolution 2023-11-24 15:09:02.22

Re: Corruption or wrong results with 14.10?

On Thu, Nov 23, 2023 at 7:34 AM Torsten Förtsch wrote: > On Thu, Nov 23, 2023 at 2:29 PM Daniel Westermann (DWE) < > daniel.westerm...@dbi-services.com> wrote: > >> smrdbprod=# select crart_id, chemin, count(*) from smrr_mgr.formula group >> by crart_id, chemin having count(*) > 1; >> crart_id

Re: replication primary writting infinite number of WAL files

On 11/24/23 09:51, Adrian Klaver wrote: On 11/24/23 09:32, Les wrote: Please Reply All to include list Ccing list to get information back there. Adrian Klaver > (2023. nov. 24., P, 17:50):     On 11/24/23 03:39, Les wrote: > The only exception is a se

Re: Odd Shortcut behaviour in PG14

Zahir Lalani writes: > Sorry Tom - let me try and clarify: > (ekey is a variable passed into the function) OK, so if this is a plpgsql function and ekey is a function variable, the planner will definitely perceive this as a query parameterized by the value of "ekey". We will consider a "custom"

Re: Can user specification of a column value be required when querying a view ?

On Fri, Nov 24, 2023 at 1:01 PM Peter J. Holzer wrote: > On 2023-11-20 22:03:06 -0500, Ron Johnson wrote: > > Or row level security. > > Does that help here? AIUI row level security can be used to limit access > to specific rows (e.g. user alex can access info about ssn '106-91-9930' > but not '2

Re: Can user specification of a column value be required when querying a view ?

On 2023-11-20 22:03:06 -0500, Ron Johnson wrote: > Or row level security. Does that help here? AIUI row level security can be used to limit access to specific rows (e.g. user alex can access info about ssn '106-91-9930' but not '234-56-7890') but not how many rows can be accessed in a single query

Re: replication primary writting infinite number of WAL files

> > > > I have no explanation for that, except a coincidence. > Replication slots don't generate WAL. > The problem stopped exactly when I dropped the slot. It happened three times in a row. :-(

Re: replication primary writting infinite number of WAL files

On 11/24/23 09:32, Les wrote: Please Reply All to include list Ccing list to get information back there. Adrian Klaver > (2023. nov. 24., P, 17:50): On 11/24/23 03:39, Les wrote: > The only exception is a sequence > value that was moved millio

Re: replication primary writting infinite number of WAL files

On Fri, 2023-11-24 at 16:59 +0100, Les wrote: > > > Laurenz Albe   (2023. nov. 24., P, 16:00): > > On Fri, 2023-11-24 at 12:39 +0100, Les wrote: > > > Under normal circumstances, the number of write operations is relatively > > > low, with an > > > average of 4-5 MB/sec total write speed on the

Re: Inquiry Regarding Initial Seed for pgsql Protocol Fuzz Testing

On 11/23/23 22:36, Cherry Pang wrote: Again please use Reply All and include the list in your responses. Ccing list. Sure, I'm interested in experimenting with SGFuzz, a tool mentioned in the 'Stateful Greybox Fuzzing' paper, to conduct fuzz testing on the PostgreSQL database protocol. I've su

Re: replication primary writting infinite number of WAL files

On 11/24/23 03:39, Les wrote: Hello, Yesterday, the primary server suddenly started writing to the pg_wal directory at a crazy pace, 1.5GB/sec, but sometimes it went up to over 3GB/sec. The pg_wal started fattening up and didn't stop until it ran out of disk space. It happened so fast that we

RE: Odd Shortcut behaviour in PG14

> -Original Message- > From: Tom Lane > Sent: Friday, November 24, 2023 3:35 PM > To: Zahir Lalani > Cc: Ron Johnson ; pgsql- > generallists.postgresql.org > Subject: Re: Odd Shortcut behaviour in PG14 > You *still* haven't defined what you mean by "fails". We can't help you > effe

Re: replication primary writting infinite number of WAL files

On Fri, 24 Nov 2023, 17:12 Ron Johnson, wrote: > On Fri, Nov 24, 2023 at 11:00 AM Les wrote: > [snip] > >> Writing of WAL files continued after we shut down all clients, and >> restarted the primary PostgreSQL server. >> >> The order was: >> >> 1. shut down all clients >> 2. stop the primary >>

Re: replication primary writting infinite number of WAL files

On Fri, Nov 24, 2023 at 11:00 AM Les wrote: [snip] > Writing of WAL files continued after we shut down all clients, and > restarted the primary PostgreSQL server. > > The order was: > > 1. shut down all clients > 2. stop the primary > 3. start the primary > 4. primary started to write like mad ag

Re: replication primary writting infinite number of WAL files

Laurenz Albe (2023. nov. 24., P, 16:00): > On Fri, 2023-11-24 at 12:39 +0100, Les wrote: > > Under normal circumstances, the number of write operations is relatively > low, with an > > average of 4-5 MB/sec total write speed on the disk associated with the > data directory. > > Yesterday, the pr

Re: Odd Shortcut behaviour in PG14

Zahir Lalani writes: > Looking at the application logs this function is being called once per > display row - it is running successfully around 10 times with the same > input params. When it fails, it is with the same params! You *still* haven't defined what you mean by "fails". We can't help yo

Re: replication primary writting infinite number of WAL files

On Fri, 2023-11-24 at 12:39 +0100, Les wrote: > Under normal circumstances, the number of write operations is relatively low, > with an > average of 4-5 MB/sec total write speed on the disk associated with the data > directory. > Yesterday, the primary server suddenly started writing to the pg_wa

Re: Odd Shortcut behaviour in PG14

On Fri, Nov 24, 2023 at 8:01 AM Zahir Lalani wrote: > > > > -Original Message- > > From: Tom Lane > > Sent: Thursday, November 23, 2023 7:45 PM > > To: Ron Johnson > > Cc: pgsql-generallists.postgresql.org < > pgsql-general@lists.postgresql.org> > > Subject: Re: Odd Shortcut behaviour i

Re: replication primary writting infinite number of WAL files

> > > First I was also thinking about vacuum. But removing a replication > > slot should have no effect on vacuum on the primary (AFAIK). Please > > correct me if I'm wrong. > > > > yeah, depends. there are 2 processes: > > * 1 process generating the wal's, maybe a VACUUM > * an inactive slot hold

RE: Odd Shortcut behaviour in PG14

> -Original Message- > From: Tom Lane > Sent: Thursday, November 23, 2023 7:45 PM > To: Ron Johnson > Cc: pgsql-generallists.postgresql.org > Subject: Re: Odd Shortcut behaviour in PG14 > > Ron Johnson writes: > > Out of curiosity, what is the point of adding the "true" predicate no

Re: replication primary writting infinite number of WAL files

Am 24.11.23 um 13:52 schrieb Les: Andreas Kretschmer wrote (2023. nov. 24., P, 13:22): Am 24.11.23 um 12:39 schrieb Les: > > Hello, > please check the database log, a VACUUM can also lead to massive wal generation. Can you find other related messages? by the wa

Re: replication primary writting infinite number of WAL files

Andreas Kretschmer wrote (2023. nov. 24., P, 13:22): > > > Am 24.11.23 um 12:39 schrieb Les: > > > > Hello, > > > > please check the database log, a VACUUM can also lead to massive wal > generation. Can you find other related messages? by the way, the picture > is hard to read, please post text i

Re: replication primary writting infinite number of WAL files

Am 24.11.23 um 12:39 schrieb Les: Hello, Yesterday we were faced with a problem that we do not understand and cannot solve ourselves. We have a postgresql cluster using repmgr, which has three members. The version of all instances (currently) is "PostgreSQL 14.5 (Debian 14.5-2.pgdg110+2)