pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feeddelete then insert
7+ messages / 4 participants
[nested] [flat]
* delete then insert
@ 2020-01-17 20:03 Ken Benson <Ken@infowerks.com>
2020-01-17 20:12 ` Re: delete then insert Ron <ronljohnsonjr@gmail.com>
2020-01-17 22:20 ` Re: delete then insert Jeff Janes <jeff.janes@gmail.com>
0 siblings, 2 replies; 7+ messages in thread
From: Ken Benson @ 2020-01-17 20:03 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: delete then insert
2020-01-17 20:03 delete then insert Ken Benson <Ken@infowerks.com>
@ 2020-01-17 20:12 ` Ron <ronljohnsonjr@gmail.com>
2020-01-17 20:59 ` RE: delete then insert Ken Benson <Ken@infowerks.com>
1 sibling, 1 reply; 7+ messages in thread
From: Ron @ 2020-01-17 20:12 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* RE: delete then insert
2020-01-17 20:03 delete then insert Ken Benson <Ken@infowerks.com>
2020-01-17 20:12 ` Re: delete then insert Ron <ronljohnsonjr@gmail.com>
@ 2020-01-17 20:59 ` Ken Benson <Ken@infowerks.com>
2020-01-17 21:20 ` Re: delete then insert Ron <ronljohnsonjr@gmail.com>
2020-01-17 21:50 ` Re: delete then insert David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 2 replies; 7+ messages in thread
From: Ken Benson @ 2020-01-17 20:59 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: delete then insert
2020-01-17 20:03 delete then insert Ken Benson <Ken@infowerks.com>
2020-01-17 20:12 ` Re: delete then insert Ron <ronljohnsonjr@gmail.com>
2020-01-17 20:59 ` RE: delete then insert Ken Benson <Ken@infowerks.com>
@ 2020-01-17 21:20 ` Ron <ronljohnsonjr@gmail.com>
1 sibling, 0 replies; 7+ messages in thread
From: Ron @ 2020-01-17 21:20 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: delete then insert
2020-01-17 20:03 delete then insert Ken Benson <Ken@infowerks.com>
2020-01-17 20:12 ` Re: delete then insert Ron <ronljohnsonjr@gmail.com>
2020-01-17 20:59 ` RE: delete then insert Ken Benson <Ken@infowerks.com>
@ 2020-01-17 21:50 ` David G. Johnston <david.g.johnston@gmail.com>
2020-01-17 21:53 ` RE: delete then insert Ken Benson <Ken@infowerks.com>
1 sibling, 1 reply; 7+ messages in thread
From: David G. Johnston @ 2020-01-17 21:50 UTC (permalink / raw)
To: Ken Benson <Ken@infowerks.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* RE: delete then insert
2020-01-17 20:03 delete then insert Ken Benson <Ken@infowerks.com>
2020-01-17 20:12 ` Re: delete then insert Ron <ronljohnsonjr@gmail.com>
2020-01-17 20:59 ` RE: delete then insert Ken Benson <Ken@infowerks.com>
2020-01-17 21:50 ` Re: delete then insert David G. Johnston <david.g.johnston@gmail.com>
@ 2020-01-17 21:53 ` Ken Benson <Ken@infowerks.com>
0 siblings, 0 replies; 7+ messages in thread
From: Ken Benson @ 2020-01-17 21:53 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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<mailto:Ken@infowerks.com>> wrote:
From: Ron <ronljohnsonjr@gmail.com<mailto:ronljohnsonjr@gmail.com>>
Sent: Friday, January 17, 2020 12:12 PM
To: pgsql-admin@lists.postgresql.org<mailto: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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: delete then insert
2020-01-17 20:03 delete then insert Ken Benson <Ken@infowerks.com>
@ 2020-01-17 22:20 ` Jeff Janes <jeff.janes@gmail.com>
1 sibling, 0 replies; 7+ messages in thread
From: Jeff Janes @ 2020-01-17 22:20 UTC (permalink / raw)
To: Ken Benson <Ken@infowerks.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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
>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2020-01-17 22:20 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-01-17 20:03 delete then insert Ken Benson <Ken@infowerks.com>
2020-01-17 20:12 ` Ron <ronljohnsonjr@gmail.com>
2020-01-17 20:59 ` Ken Benson <Ken@infowerks.com>
2020-01-17 21:20 ` Ron <ronljohnsonjr@gmail.com>
2020-01-17 21:50 ` David G. Johnston <david.g.johnston@gmail.com>
2020-01-17 21:53 ` Ken Benson <Ken@infowerks.com>
2020-01-17 22:20 ` Jeff Janes <jeff.janes@gmail.com>
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