pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedq re removal/recycling of WAL files
4+ messages / 3 participants
[nested] [flat]
* q re removal/recycling of WAL files
@ 2021-11-03 22:24 Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 2 replies; 4+ messages in thread
From: Scott Ribe @ 2021-11-03 22:24 UTC (permalink / raw)
To: pgsql-admin <pgsql-admin@lists.postgresql.org>
Let's say that there's a big update as part of a migration, and that update times out, and the migration utility retries it several times. (Context is that sequence happened through automated utility, and filled the disk, leading to an unfortunate series of events.)
1) At what point does WAL from the failed & rolled back operations "go away"? During the next checkpoint? During the checkpoint after completion of all other transactions which overlapped time-wise with the timing-out ones?
2) Is there a chance that never-visible rows would be written into tables, bloating them?
--
Scott Ribe
scott_ribe@elevated-dev.com
https://www.linkedin.com/in/scottribe/
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: q re removal/recycling of WAL files
@ 2021-11-03 23:37 Rui DeSousa <rui@crazybean.net>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
1 sibling, 1 reply; 4+ messages in thread
From: Rui DeSousa @ 2021-11-03 23:37 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: pgsql-admin <pgsql-admin@lists.postgresql.org>
> On Nov 3, 2021, at 6:24 PM, Scott Ribe <scott_ribe@elevated-dev.com> wrote:
>
> Let's say that there's a big update as part of a migration, and that update times out, and the migration utility retries it several times. (Context is that sequence happened through automated utility, and filled the disk, leading to an unfortunate series of events.)
>
> 1) At what point does WAL from the failed & rolled back operations "go away"? During the next checkpoint? During the checkpoint after completion of all other transactions which overlapped time-wise with the timing-out ones?
>
WAL file can’t be recycled until it is no longer needed; thus after a checkpoint and archived as well (if enabled).
> 2) Is there a chance that never-visible rows would be written into tables, bloating them?
>
Yes, changes would be recorded into the tables; however, they would not be visible if the transaction was rolled back or aborted. A subsequent vacuum would remove those records and truncate the file to high water mark if possible.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: q re removal/recycling of WAL files
@ 2021-11-03 23:38 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
1 sibling, 0 replies; 4+ messages in thread
From: Laurenz Albe @ 2021-11-03 23:38 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; pgsql-admin <pgsql-admin@lists.postgresql.org>
On Wed, 2021-11-03 at 16:24 -0600, Scott Ribe wrote:
> Let's say that there's a big update as part of a migration, and that update times out,
> and the migration utility retries it several times. (Context is that sequence happened
> through automated utility, and filled the disk, leading to an unfortunate series of events.)
>
> 1) At what point does WAL from the failed & rolled back operations "go away"? During the next
> checkpoint? During the checkpoint after completion of all other transactions which
> overlapped time-wise with the timing-out ones?
A WAL segment is deleted if it contains no record newer than the latest checkpoint
or the oldest replication slot, and it is archived (if enabled) and "wal_keep_size"
does not require it to be kept around.
That is the same for committed and rolled back operations.
> 2) Is there a chance that never-visible rows would be written into tables, bloating them?
Yes, that will definitely happen.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: q re removal/recycling of WAL files
@ 2021-11-04 00:57 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Rui DeSousa <rui@crazybean.net>
0 siblings, 0 replies; 4+ messages in thread
From: Scott Ribe @ 2021-11-04 00:57 UTC (permalink / raw)
To: Rui DeSousa <rui@crazybean.net>; +Cc: pgsql-admin <pgsql-admin@lists.postgresql.org>
> On Nov 3, 2021, at 5:37 PM, Rui DeSousa <rui@crazybean.net> wrote:
>
> WAL file can’t be recycled until it is no longer needed; thus after a checkpoint and archived as well (if enabled).
Ah yes, that's it! We are archiving WAL, and I imagine what happened easily outran the pace of archiving.
There was an exponential backoff between the retries, so although I could imagine rapid-fire retries filling disk with WAL before checkpoint running, I was having a little doubt about that happening in this case. I'm sure it was the archiving.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2021-11-04 00:57 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-11-03 22:24 q re removal/recycling of WAL files Scott Ribe <scott_ribe@elevated-dev.com>
2021-11-03 23:37 ` Rui DeSousa <rui@crazybean.net>
2021-11-04 00:57 ` Scott Ribe <scott_ribe@elevated-dev.com>
2021-11-03 23:38 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox