Re: delete then insert

On 1/17/20 2:59 PM, Ken Benson wrote:

From: Ron <ronljohnsonjr@gmail.com>
Sent: Friday, January 17, 2020 12:12 PM
To: pgsql-admin@lists.postgresql.org
Subject: Re: delete then insert

 

On 1/17/20 2:03 PM, Ken Benson wrote:

 

So – I THINK I know – that when a row is deleted from a table – the row is not actually removed from the table – but, merely marked as deleted, thus becoming a dead tuple.
AUTOVACUUM – takes care of the process of removing these dead tuples.

 

My question. If – an insert occurs to that same table before autovacuum comes along, does that inserted record use a dead tuple? Or – does the insert result in an actual added row?

If there's an open transaction which is still looking at the now-deleted record, then over-writing that area of the file would be a Bad Thing.

So – the answer is – NO – the dead tuple won’t be used until is has been reclaimed by vacuum – and that it doesn’t because it would be a bad thing, right?

I don't know what happens if not other transaction is holding that "slot".