Subject: Re: Attempting to delete excess rows from table with BATCH DELETE

From: Tom Lane <[email-masked]>
Date: Tue, 27 Jan 2026 22:52:00 -0500

"David G. Johnston" <[email-masked]> writes:
> On Tuesday, January 27, 2026, Gus Spier <[email-masked]> 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

---

From: Ron Johnson <[email-masked]>
Date: Tue, 27 Jan 2026 23:03:46 -0500

On Tue, Jan 27, 2026 at 10:52 PM Tom Lane <[email-masked]> wrote:

> 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!

---

From: Tom Lane <[email-masked]>
Date: Tue, 27 Jan 2026 23:06:41 -0500

> Hmm.  Must have been START TRANSACTION which I remember causing issues in DO

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

---

From: Olivier Gautherot <[email-masked]>
Date: Wed, 28 Jan 2026 08:32:03 +0100

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

---

From: Gus Spier <[email-masked]>
Date: Wed, 28 Jan 2026 05:57:09 -0500

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-masked]> 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.
>
> - 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
>