Thread: delete then insert

delete then insert

From
Ken Benson
Date:

 

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?

 

 

Ken Benson | ken @ infowerks-dot-com

 

 

Re: delete then insert

From
Ron
Date:
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.


--
Angular momentum makes the world go 'round.

RE: delete then insert

From
Ken Benson
Date:

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?

--
Angular momentum makes the world go 'round.

Re: delete then insert

From
Ron
Date:
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".

--
Angular momentum makes the world go 'round.

Re: delete then insert

From
"David G. Johnston"
Date:
On Fri, Jan 17, 2020 at 1:59 PM Ken Benson <Ken@infowerks.com> 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

Vacuum takes care of dead tuple management.  Auto-Vacuum is just a process whereby the system decides when it should run vacuum for you.

"The standard form of VACUUM removes dead row versions in tables and indexes and marks the space available for future reuse."

Vacuum is the only thing that marks space available for use, everything else relies on those marks as opposed to everyone checking for themselves.

David J.

RE: delete then insert

From
Ken Benson
Date:

From: David G. Johnston <david.g.johnston@gmail.com>
Sent: Friday, January 17, 2020 1:51 PM
To: Ken Benson <Ken@infowerks.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: delete then insert

 

On Fri, Jan 17, 2020 at 1:59 PM Ken Benson <Ken@infowerks.com> 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

Vacuum takes care of dead tuple management.  Auto-Vacuum is just a process whereby the system decides when it should run vacuum for you.

 

"The standard form of VACUUM removes dead row versions in tables and indexes and marks the space available for future reuse."

 

Vacuum is the only thing that marks space available for use, everything else relies on those marks as opposed to everyone checking for themselves.

[Ken.B]

That’s what we thought – but wanted to make certain. Thank you!

 

David J.

 

Re: delete then insert

From
Jeff Janes
Date:
On Fri, Jan 17, 2020 at 3:03 PM Ken Benson <Ken@infowerks.com> 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?


There is a double indirection.  The index entries point to a specific "line item" within a specific table block.  In that table block, that line item points to an area in that same block for the actual row data.

Once a row has been delete long enough that no currently active snapshot can possibly care about it, anyone can "clean up" the table block so that the "line item" has a flag set to indicate it no longer points to any storage within the block, and that former storage for the row is then available for reuse.  But only a vacuum can make the line item itself available for reuse.

Now if someone other than a vacuum has cleaned up a block, will it advertise the reclaimed space in the freespace map, so that an INSERT can easily find it?  I don't know.

Cheers,

Jeff