public inbox for [email protected]help / color / mirror / Atom feed
Re: Attempting to delete excess rows from table with BATCH DELETE 5+ messages / 4 participants [nested] [flat]
* Re: Attempting to delete excess rows from table with BATCH DELETE @ 2026-01-28 03:52 Tom Lane <[email protected]> 2026-01-28 04:03 ` Re: Attempting to delete excess rows from table with BATCH DELETE Ron Johnson <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Tom Lane @ 2026-01-28 03:52 UTC (permalink / raw) To: David G. Johnston <[email protected]>; +Cc: Gus Spier <[email protected]>; pgsql-general <[email protected]> "David G. Johnston" <[email protected]> writes: > On Tuesday, January 27, 2026, Gus Spier <[email protected]> wrote: >> I write a procedure to accomplish all this work but it persists in >> returning a error to the effect that a COMMIT is not valid in a block >> tht tries to DELETE data. > Haven’t tested to be sure but this doesn’t seem like a community edition > limitation. Yeah, you can definitely do that in standard Postgres (at least since we invented procedures). Sounds like Aurora is behind the times. I know that their storage engine is fundamentally different from ours; perhaps it has problems with this idea. > You’d have to move the logic to a proper client application that executes > top-level commands. Yup. regards, tom lane ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Attempting to delete excess rows from table with BATCH DELETE 2026-01-28 03:52 Re: Attempting to delete excess rows from table with BATCH DELETE Tom Lane <[email protected]> @ 2026-01-28 04:03 ` Ron Johnson <[email protected]> 2026-01-28 04:06 ` Re: Attempting to delete excess rows from table with BATCH DELETE Tom Lane <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Ron Johnson @ 2026-01-28 04:03 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: David G. Johnston <[email protected]>; Gus Spier <[email protected]>; pgsql-general <[email protected]> On Tue, Jan 27, 2026 at 10:52 PM Tom Lane <[email protected]> wrote: > "David G. Johnston" <[email protected]> writes: > > On Tuesday, January 27, 2026, Gus Spier <[email protected]> wrote: > >> I write a procedure to accomplish all this work but it persists in > >> returning a error to the effect that a COMMIT is not valid in a block > >> tht tries to DELETE data. > > > Haven’t tested to be sure but this doesn’t seem like a community edition > > limitation. > > Yeah, you can definitely do that in standard Postgres (at least since > we invented procedures). Hmm. Must have been START TRANSACTION which I remember causing issues in DO blocks. -- Death to <Redacted>, and butter sauce. Don't boil me, I'm still alive. <Redacted> lobster! ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Attempting to delete excess rows from table with BATCH DELETE 2026-01-28 03:52 Re: Attempting to delete excess rows from table with BATCH DELETE Tom Lane <[email protected]> 2026-01-28 04:03 ` Re: Attempting to delete excess rows from table with BATCH DELETE Ron Johnson <[email protected]> @ 2026-01-28 04:06 ` Tom Lane <[email protected]> 2026-01-28 07:32 ` Re: Attempting to delete excess rows from table with BATCH DELETE Olivier Gautherot <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Tom Lane @ 2026-01-28 04:06 UTC (permalink / raw) To: Ron Johnson <[email protected]>; +Cc: David G. Johnston <[email protected]>; Gus Spier <[email protected]>; pgsql-general <[email protected]> Ron Johnson <[email protected]> writes: > Hmm. Must have been START TRANSACTION which I remember causing issues in DO > blocks. Too lazy to test, but I think we might reject that. The normal rule in a procedure is that the next command after a COMMIT automatically starts a new transaction, so you don't need an explicit START. regards, tom lane ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Attempting to delete excess rows from table with BATCH DELETE 2026-01-28 03:52 Re: Attempting to delete excess rows from table with BATCH DELETE Tom Lane <[email protected]> 2026-01-28 04:03 ` Re: Attempting to delete excess rows from table with BATCH DELETE Ron Johnson <[email protected]> 2026-01-28 04:06 ` Re: Attempting to delete excess rows from table with BATCH DELETE Tom Lane <[email protected]> @ 2026-01-28 07:32 ` Olivier Gautherot <[email protected]> 2026-01-28 10:57 ` Re: Attempting to delete excess rows from table with BATCH DELETE Gus Spier <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Olivier Gautherot @ 2026-01-28 07:32 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: Ron Johnson <[email protected]>; David G. Johnston <[email protected]>; Gus Spier <[email protected]>; pgsql-general <[email protected]> Hi Gus! This reminds me of a costly mistake I made and you want to avoid: it was a mission critical database (say physical safety, real people) and the vacuum froze the DB for 24 hours, until I finally took it offline. If you can take it offline (and you have a couple of hours) - disconnect the DB - drop indexes (that's the killer) - remove unnecessary data - vaccuum manually (or better, copy the relevant data to a new table and rename it - this will save the DELETE above and will defragment the table) - rebuild indexes - connect the DB The better solution would be partitioning: - choose a metrics (for instance a timestamp) - create partition tables for the period you want to keep - copy the relevant data to the partitions and create partial indexes - take the DB off line - update the last partition with the latest data (should be a fast update) - truncate the original table - connect partitions - connect the DB In the future, deleting historic data will be a simple DROP TABLE. Hope it helps -- Olivier Gautherot Tel: +33 6 02 71 92 23 El mié, 28 de ene de 2026, 5:06 a.m., Tom Lane <[email protected]> escribió: > Ron Johnson <[email protected]> writes: > > Hmm. Must have been START TRANSACTION which I remember causing issues > in DO > > blocks. > > Too lazy to test, but I think we might reject that. The normal rule > in a procedure is that the next command after a COMMIT automatically > starts a new transaction, so you don't need an explicit START. > > regards, tom lane > > > ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Attempting to delete excess rows from table with BATCH DELETE 2026-01-28 03:52 Re: Attempting to delete excess rows from table with BATCH DELETE Tom Lane <[email protected]> 2026-01-28 04:03 ` Re: Attempting to delete excess rows from table with BATCH DELETE Ron Johnson <[email protected]> 2026-01-28 04:06 ` Re: Attempting to delete excess rows from table with BATCH DELETE Tom Lane <[email protected]> 2026-01-28 07:32 ` Re: Attempting to delete excess rows from table with BATCH DELETE Olivier Gautherot <[email protected]> @ 2026-01-28 10:57 ` Gus Spier <[email protected]> 0 siblings, 0 replies; 5+ messages in thread From: Gus Spier @ 2026-01-28 10:57 UTC (permalink / raw) To: Olivier Gautherot <[email protected]>; +Cc: Tom Lane <[email protected]>; Ron Johnson <[email protected]>; David G. Johnston <[email protected]>; pgsql-general <[email protected]> Thanks to all. I'll give the bash loop method a try and let you know how it works out. Regards to all, Gus On Wed, Jan 28, 2026 at 2:32 AM Olivier Gautherot <[email protected]> wrote: > > Hi Gus! > > This reminds me of a costly mistake I made and you want to avoid: it was a mission critical database (say physical safety, real people) and the vacuum froze the DB for 24 hours, until I finally took it offline. > > If you can take it offline (and you have a couple of hours) > - disconnect the DB > - drop indexes (that's the killer) > - remove unnecessary data > - vaccuum manually (or better, copy the relevant data to a new table and rename it - this will save the DELETE above and will defragment the table) > - rebuild indexes > - connect the DB > > The better solution would be partitioning: > - choose a metrics (for instance a timestamp) > - create partition tables for the period you want to keep > - copy the relevant data to the partitions and create partial indexes > - take the DB off line > - update the last partition with the latest data (should be a fast update) > - truncate the original table > - connect partitions > - connect the DB > > In the future, deleting historic data will be a simple DROP TABLE. > > Hope it helps > -- > Olivier Gautherot > Tel: +33 6 02 71 92 23 > > > El mié, 28 de ene de 2026, 5:06 a.m., Tom Lane <[email protected]> escribió: >> >> Ron Johnson <[email protected]> writes: >> > Hmm. Must have been START TRANSACTION which I remember causing issues in DO >> > blocks. >> >> Too lazy to test, but I think we might reject that. The normal rule >> in a procedure is that the next command after a COMMIT automatically >> starts a new transaction, so you don't need an explicit START. >> >> regards, tom lane >> >> ^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2026-01-28 10:57 UTC | newest] Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2026-01-28 03:52 Re: Attempting to delete excess rows from table with BATCH DELETE Tom Lane <[email protected]> 2026-01-28 04:03 ` Ron Johnson <[email protected]> 2026-01-28 04:06 ` Tom Lane <[email protected]> 2026-01-28 07:32 ` Olivier Gautherot <[email protected]> 2026-01-28 10:57 ` Gus Spier <[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