public inbox for [email protected]
help / color / mirror / Atom feedRe: wal recycling problem
9+ messages / 4 participants
[nested] [flat]
* Re: wal recycling problem
@ 2023-09-28 17:59 Christoph Moench-Tegeder <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Christoph Moench-Tegeder @ 2023-09-28 17:59 UTC (permalink / raw)
To: Fabrice Chapuis <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
## Fabrice Chapuis ([email protected]):
> We have a cluster of 2 members (1 primary and 1 standby) with Postgres
> version 14.9 and 2 barman server, slots are only configured for barman,
> barman is version 3.7.
The obvious question here is: can both of those barmans keep up with
your database, or are you seeing WAL retention due to exactly these
replication slots? (Check pg_replication_slots).
Regards,
Christoph
--
Spare Space
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: wal recycling problem
@ 2023-09-29 08:48 Fabrice Chapuis <[email protected]>
parent: Christoph Moench-Tegeder <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Fabrice Chapuis @ 2023-09-29 08:48 UTC (permalink / raw)
To: Christoph Moench-Tegeder <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
Yes, barman replication can keep up with primary, wals segments size are
under max_wal_size (24Gb in our configuration)
Here is pg_replication_slots view:
barman_ge physical f t 39409 1EE2/49000000
reserved f
barman_be physical f t 39434 1EE2/3D000000
reserved f
on the other hand there are 2 slots for logical replication which display
status extended. I don't understand why given that the confirmed_flush_lsn
field that is up to date. The restart_lsn remains frozen, for what reason?
pgoutput │ logical │ 2667915 │ db019a00 │ f │ t │ 1880162
│ │ 68512101 │ 1ECA/37C3F1B8 │ 1EE2/4D6BDCF8 │ extended │
│ f │
pgoutput │ logical │ 2668584 │ db038a00 │ f │ t │
363230 │ │ 68512101 │ 1ECA/37C3F1B8 │ 1EE2/4D6BDCF8 │
extended │ │ f │
Regards
Fabrice
On Thu, Sep 28, 2023 at 7:59 PM Christoph Moench-Tegeder <[email protected]>
wrote:
> ## Fabrice Chapuis ([email protected]):
>
> > We have a cluster of 2 members (1 primary and 1 standby) with Postgres
> > version 14.9 and 2 barman server, slots are only configured for barman,
> > barman is version 3.7.
>
> The obvious question here is: can both of those barmans keep up with
> your database, or are you seeing WAL retention due to exactly these
> replication slots? (Check pg_replication_slots).
>
> Regards,
> Christoph
>
> --
> Spare Space
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: wal recycling problem
@ 2023-10-02 10:06 Christoph Moench-Tegeder <[email protected]>
parent: Fabrice Chapuis <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Christoph Moench-Tegeder @ 2023-10-02 10:06 UTC (permalink / raw)
To: Fabrice Chapuis <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
Hi,
## Fabrice Chapuis ([email protected]):
> on the other hand there are 2 slots for logical replication which display
> status extended. I don't understand why given that the confirmed_flush_lsn
> field that is up to date. The restart_lsn remains frozen, for what reason?
There you have it - "extended" means "holding wal". And as long as the
restart_lsn does not advance, checkpointer cannot free any wal beyond
that lsn. My first idea would be some long-running (or huge) transaction
which is in process (active or still being streamed). I'd recommend
looking into what the clients on these slots are doing.
Regards,
Christoph
--
Spare Space
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: wal recycling problem
@ 2023-10-06 10:49 Fabrice Chapuis <[email protected]>
parent: Christoph Moench-Tegeder <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Fabrice Chapuis @ 2023-10-06 10:49 UTC (permalink / raw)
To: Christoph Moench-Tegeder <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
Thanks Christoph for your message.
Now I understand why the wals are preserved if logical replication is
configured and enabled. The problem is that when a large volume of data is
loaded into a database, for example during a pg_restore, the wal sender
process associated with the logical replication slot will have to decrypt
all of the wals generated during this operation which will take a long time
and the restart_lsn will not be modified.
From a conceptual point of view I think that specific wals per subscription
should be used and stored in the pg_replslot folder in order to avoid
working directly on the wals of the instance.
What do you think about this proposal?
Regards
Fabrice
On Mon, Oct 2, 2023 at 12:06 PM Christoph Moench-Tegeder <[email protected]>
wrote:
> Hi,
>
> ## Fabrice Chapuis ([email protected]):
>
> > on the other hand there are 2 slots for logical replication which display
> > status extended. I don't understand why given that the
> confirmed_flush_lsn
> > field that is up to date. The restart_lsn remains frozen, for what
> reason?
>
> There you have it - "extended" means "holding wal". And as long as the
> restart_lsn does not advance, checkpointer cannot free any wal beyond
> that lsn. My first idea would be some long-running (or huge) transaction
> which is in process (active or still being streamed). I'd recommend
> looking into what the clients on these slots are doing.
>
> Regards,
> Christoph
>
> --
> Spare Space
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: wal recycling problem
@ 2023-10-08 13:57 Christoph Moench-Tegeder <[email protected]>
parent: Fabrice Chapuis <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Christoph Moench-Tegeder @ 2023-10-08 13:57 UTC (permalink / raw)
To: Fabrice Chapuis <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
## Fabrice Chapuis ([email protected]):
> From a conceptual point of view I think that specific wals per subscription
> should be used and stored in the pg_replslot folder in order to avoid
> working directly on the wals of the instance.
> What do you think about this proposal?
I think that would open a wholly new can of worms.
The most obvious point here is: that WAL is primarily generated for
the operation of the database itself - it's our kind of transaction
log, or "Redo Log" in other systems' lingo. Replication (be it physical
or logical) is a secondary purpose (an obvious and important one, but
still secondary).
How would you know which part of WAL is needed for any specific
replication slot? You'd have to decode and filter it, and already
you're back at square one. How would you handle multiple replications
for the same table (in the same publication, or even over multiple
(overlapping) publications) - do you multiply the WAL?
For now, we have "any replication using replication slots, be it logical
or physical replication, retains WAL up to max_slot_wal_keep_size
(or "unlimited" if not set - and on PostgreSQL 12 and before); and you
need to monitor the state of your replication slots", which is a
totally usabe rule, I think.
Regards,
Christoph
--
Spare Space
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: wal recycling problem
@ 2023-10-17 10:37 Fabrice Chapuis <[email protected]>
parent: Christoph Moench-Tegeder <[email protected]>
0 siblings, 0 replies; 9+ messages in thread
From: Fabrice Chapuis @ 2023-10-17 10:37 UTC (permalink / raw)
To: Christoph Moench-Tegeder <[email protected]>; +Cc: PostgreSQL Hackers <[email protected]>
Thanks for your feedback
> How would you know which part of WAL is needed for any specific
replication slot?
change are captured for each published table and written twice, once in
the current wal and once in the slot-specific wal
> How would you handle multiple replications
for the same table
added information about from which publication a table belongs is entered
in the wal slot
> be it logical or physical replication, retains WAL up to
max_slot_wal_keep_size
ok but if max_slot_wal_keep_size is exceeded the changes are lost and all
of the replicated tables must be resynchronized
Regards
Fabrice
On Sun, Oct 8, 2023 at 3:57 PM Christoph Moench-Tegeder <[email protected]>
wrote:
> ## Fabrice Chapuis ([email protected]):
>
> > From a conceptual point of view I think that specific wals per
> subscription
> > should be used and stored in the pg_replslot folder in order to avoid
> > working directly on the wals of the instance.
> > What do you think about this proposal?
>
> I think that would open a wholly new can of worms.
> The most obvious point here is: that WAL is primarily generated for
> the operation of the database itself - it's our kind of transaction
> log, or "Redo Log" in other systems' lingo. Replication (be it physical
> or logical) is a secondary purpose (an obvious and important one, but
> still secondary).
> How would you know which part of WAL is needed for any specific
> replication slot? You'd have to decode and filter it, and already
> you're back at square one. How would you handle multiple replications
> for the same table (in the same publication, or even over multiple
> (overlapping) publications) - do you multiply the WAL?
>
> For now, we have "any replication using replication slots, be it logical
> or physical replication, retains WAL up to max_slot_wal_keep_size
> (or "unlimited" if not set - and on PostgreSQL 12 and before); and you
> need to monitor the state of your replication slots", which is a
> totally usabe rule, I think.
>
> Regards,
> Christoph
>
> --
> Spare Space
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Proposal: INSERT ... BY NAME
@ 2026-07-10 07:46 Jim Jones <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Jim Jones @ 2026-07-10 07:46 UTC (permalink / raw)
To: Ayush Tiwari <[email protected]>; pgsql-hackers; +Cc: Peter Eisentraut <[email protected]>
Hi Ayush
On 03/07/2026 13:07, Ayush Tiwari wrote:
> Attached is a draft patch for INSERT ... BY NAME. It matches the result
> columns of a source query to target columns by name instead of by position,
> which is useful when the source and target columns are written in different
> orders. Similar syntax exists in DuckDB [1] and Oracle [2]:
>
> INSERT INTO t1 (c1, c2)
> BY NAME
> SELECT c1 * 10 AS c2, c2 + 5 AS c1 FROM t2;
Thanks for the patch!
I've tested the feature and it's doing what it intends to.
Apparently DuckDB does not support specific target columns:
memory D CREATE TABLE t (a int, b int);
memory D INSERT INTO t (a,b) BY NAME SELECT 37 AS b, 42 AS a;
Parser Error:
syntax error at or near "BY"
LINE 1: INSERT INTO t (a,b) BY NAME SELECT 37 AS b, 42 AS a;
^
memory D INSERT INTO t BY NAME SELECT 37 AS b, 42 AS a;
memory D SELECT * FROM t;
┌───────┬───────┐
│ a │ b │
│ int32 │ int32 │
├───────┼───────┤
│ 42 │ 37 │
└───────┴───────┘
But your patch allows it:
psql (20devel)
Type "help" for help.
postgres=# CREATE TABLE t (a int, b int);
CREATE TABLE
postgres=# INSERT INTO t (a,b) BY NAME SELECT 37 AS b, 42 AS a;
INSERT 0 1
postgres=# SELECT * FROM t;
a | b
----+----
42 | 37
(1 row)
Since I don't have a copy of the SQL spec, I can't tell if this is a
feature gap from DuckDB or if you just added this feature yourself. What
does the spec say about it?
Thanks!
Best, Jim
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Proposal: INSERT ... BY NAME
@ 2026-07-10 08:06 Andreas Karlsson <[email protected]>
parent: Jim Jones <[email protected]>
0 siblings, 1 reply; 9+ messages in thread
From: Andreas Karlsson @ 2026-07-10 08:06 UTC (permalink / raw)
To: Jim Jones <[email protected]>; Ayush Tiwari <[email protected]>; pgsql-hackers; +Cc: Peter Eisentraut <[email protected]>
On 7/10/26 09:46, Jim Jones wrote:
> postgres=# CREATE TABLE t (a int, b int);
> CREATE TABLE
> postgres=# INSERT INTO t (a,b) BY NAME SELECT 37 AS b, 42 AS a;
> INSERT 0 1
> postgres=# SELECT * FROM t;
> a | b
> ----+----
> 42 | 37
> (1 row)
>
> Since I don't have a copy of the SQL spec, I can't tell if this is a
> feature gap from DuckDB or if you just added this feature yourself. What
> does the spec say about it?
It is technically not yet in the standard but it was accepted and will
almost certainly be there in the next version. I was there as a guest at
the meeting when this very question was discussed and that is indeed a
feature gap in DuckDB. But as it is a very new feature in the upcoming
version of the standard that is to be expected.
According the accepted change proposal which was discussed when I was there:
INSERT INTO t (a,b) BY NAME SELECT 37 AS b, 42 AS a;
is effectively the same thing as:
INSERT INTO t (a,b) BY NAME SELECT a, b FROM (SELECT 37 AS b, 42 AS a);
--
Andreas Karlsson
Percona
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Proposal: INSERT ... BY NAME
@ 2026-07-10 08:52 Andreas Karlsson <[email protected]>
parent: Andreas Karlsson <[email protected]>
0 siblings, 0 replies; 9+ messages in thread
From: Andreas Karlsson @ 2026-07-10 08:52 UTC (permalink / raw)
To: Jim Jones <[email protected]>; Ayush Tiwari <[email protected]>; pgsql-hackers; +Cc: Peter Eisentraut <[email protected]>
On 7/10/26 10:06, Andreas Karlsson wrote:
> According the accepted change proposal which was discussed when I was
> there:
>
> INSERT INTO t (a,b) BY NAME SELECT 37 AS b, 42 AS a;
>
> is effectively the same thing as:
>
> INSERT INTO t (a,b) BY NAME SELECT a, b FROM (SELECT 37 AS b, 42 AS a);
Sorry, saw a minor typo in my example, it should have been:
INSERT INTO t (a,b) BY NAME SELECT 37 AS b, 42 AS a;
is effectively the same thing as:
INSERT INTO t (a,b) SELECT a, b FROM (SELECT 37 AS b, 42 AS a);
--
Andreas Karlsson
Percona
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2026-07-10 08:52 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-09-28 17:59 Re: wal recycling problem Christoph Moench-Tegeder <[email protected]>
2023-09-29 08:48 ` Fabrice Chapuis <[email protected]>
2023-10-02 10:06 ` Christoph Moench-Tegeder <[email protected]>
2023-10-06 10:49 ` Fabrice Chapuis <[email protected]>
2023-10-08 13:57 ` Christoph Moench-Tegeder <[email protected]>
2023-10-17 10:37 ` Fabrice Chapuis <[email protected]>
2026-07-10 07:46 Re: Proposal: INSERT ... BY NAME Jim Jones <[email protected]>
2026-07-10 08:06 ` Re: Proposal: INSERT ... BY NAME Andreas Karlsson <[email protected]>
2026-07-10 08:52 ` Re: Proposal: INSERT ... BY NAME Andreas Karlsson <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox