On Tue, Apr 28, 2015 at 2:44 PM, Alvaro Herrera
alvhe...@2ndquadrant.com wrote:
I think what we need here is something that does heap_update to tuples
at the end of the table, moving them to earlier pages; then wait for old
snapshots to die (the infrastructure for which we have now, thanks
On Tue, Apr 28, 2015 at 11:32 AM, Robert Haas robertmh...@gmail.com wrote:
On Fri, Apr 24, 2015 at 3:04 PM, Alvaro Herrera
alvhe...@2ndquadrant.com wrote:
I think what we need here is something that does heap_update to tuples
at the end of the table, moving them to earlier pages; then wait
On 4/28/15 1:32 PM, Robert Haas wrote:
More than five years have passed since Heikki posted this, and we still
haven't found a solution to the problem -- which neverthless keeps
biting people to the point that multiple user-space implementations of
similar techniques are out there.
Yeah. The
On Fri, Apr 24, 2015 at 3:04 PM, Alvaro Herrera
alvhe...@2ndquadrant.com wrote:
Heikki Linnakangas wrote:
Hmm, AFAICT pg_reorg is much more complex, writing stuff to a temp table
and swapping relfilenodes afterwards. More like the VACUUM REWRITE
that's been discussed.
For the kicks, I looked
Robert Haas wrote:
On Fri, Apr 24, 2015 at 3:04 PM, Alvaro Herrera
alvhe...@2ndquadrant.com wrote:
I think what we need here is something that does heap_update to tuples
at the end of the table, moving them to earlier pages; then wait for old
snapshots to die (the infrastructure for which
On 4/25/15 6:30 AM, Simon Riggs wrote:
On 24 April 2015 at 22:36, Jim Nasby jim.na...@bluetreble.com
mailto:jim.na...@bluetreble.com wrote:
Instead of adding forcefsm, I think it would be more useful to
accept a target block number. That way we can actually control where
the new
On 24 April 2015 at 22:36, Jim Nasby jim.na...@bluetreble.com wrote:
Instead of adding forcefsm, I think it would be more useful to accept a
target block number. That way we can actually control where the new tuple
goes. For this particular case we'd presumably go with normal FSM page
Jim Nasby wrote:
It looks like the biggest complaint (aside from allowing a limited number of
tuples to be moved) is in [1] and [2], where Tom is saying that you can't
simply call heap_update() like this without holding an exclusive lock on the
table. Is that because we're not actually
On 4/24/15 5:30 PM, Alvaro Herrera wrote:
Jim Nasby wrote:
It looks like the biggest complaint (aside from allowing a limited number of
tuples to be moved) is in [1] and [2], where Tom is saying that you can't
simply call heap_update() like this without holding an exclusive lock on the
table.
Heikki Linnakangas wrote:
Hmm, AFAICT pg_reorg is much more complex, writing stuff to a temp table
and swapping relfilenodes afterwards. More like the VACUUM REWRITE
that's been discussed.
For the kicks, I looked at what it would take to write a utility like
that. It turns out to be quite
On 4/24/15 2:04 PM, Alvaro Herrera wrote:
Heikki Linnakangas wrote:
Hmm, AFAICT pg_reorg is much more complex, writing stuff to a temp table
and swapping relfilenodes afterwards. More like the VACUUM REWRITE
that's been discussed.
For the kicks, I looked at what it would take to write a
Jim Nasby wrote:
Honestly, I'd prefer we exposed some way to influence where a new tuple gets
put, and perhaps better ways of accessing tuples on a specific page. That
would make it a lot easier to handle this in userspace, but it would also
make it easier to do things like concurrent
On 4/24/15 3:34 PM, Alvaro Herrera wrote:
Jim Nasby wrote:
Honestly, I'd prefer we exposed some way to influence where a new tuple gets
put, and perhaps better ways of accessing tuples on a specific page. That
would make it a lot easier to handle this in userspace, but it would also
make it
Tom Lane wrote:
I don't see any reason why not breaking the user visible behavior of
tuples CTID between any two major releases,
Am I completely wet here?
Completely. This is a user-visible behavior that we have encouraged
people to rely on, and for which there is no easy substitute.
On Thu, 2009-09-17 at 17:44 -0400, Tom Lane wrote:
Dimitri Fontaine dfonta...@hi-media.com writes:
I don't see any reason why not breaking the user visible behavior of
tuples CTID between any two major releases,
Am I completely wet here?
Completely. This is a user-visible behavior
On Fri, 2009-09-18 at 08:50 +0200, Albe Laurenz wrote:
Tom Lane wrote:
I don't see any reason why not breaking the user visible behavior of
tuples CTID between any two major releases,
Am I completely wet here?
Completely. This is a user-visible behavior that we have encouraged
Exactly. The application is typically going to throw a concurrent
update type of error when this happens, and we don't want magic
background operations to cause that.
I`d give up the possibility of using CTIDs in the way You explained
for an auto-debloater without blinking an eye. Maybe we
Simon Riggs wrote:
CTIDs don't help with optimistic locking, though it seems they can.
If you don't hold open the transaction then someone else can update the
row. That sounds good, but because of HOT it is possible that the same
CTID with the same PK value occupies that exact CTID value
On Wed, 2009-09-16 at 23:12 -0400, Tom Lane wrote:
Jeff Davis pg...@j-davis.com writes:
On Wed, 2009-09-16 at 21:48 -0400, Tom Lane wrote:
Yeah, I was just wondering about that myself. Seems like there would
be lots of situations where short exclusive-lock intervals could be
tolerated,
On Wed, 2009-09-16 at 21:19 -0400, Tom Lane wrote:
Simon Riggs si...@2ndquadrant.com writes:
What we need is VACUUM FULL CONCURRENTLY and REINDEX CONCURRENTLY.
VACUUM FULL CONCURRENTLY is a contradiction in terms. Wishing it were
possible doesn't make it so.
It depends on what do you mean
On Wed, Sep 16, 2009 at 09:48:20PM -0400, Tom Lane wrote:
Seems like there would
be lots of situations where short exclusive-lock intervals could be
tolerated, even though not long ones. So that's another argument
for being able to set an upper bound on how many tuples get moved
per call.
On Thu, Sep 17, 2009 at 9:35 AM, Joshua Tolley eggyk...@gmail.com wrote:
On Wed, Sep 16, 2009 at 09:48:20PM -0400, Tom Lane wrote:
Seems like there would
be lots of situations where short exclusive-lock intervals could be
tolerated, even though not long ones. So that's another argument
for
Hannu Krosing ha...@2ndquadrant.com writes:
On Wed, 2009-09-16 at 21:19 -0400, Tom Lane wrote:
VACUUM FULL CONCURRENTLY is a contradiction in terms. Wishing it were
possible doesn't make it so.
It depends on what do you mean by VACUUM FULL
Anything that moves tuples is not acceptable as a
On Thu, 2009-09-17 at 09:45 -0400, Robert Haas wrote:
On Thu, Sep 17, 2009 at 9:35 AM, Joshua Tolley eggyk...@gmail.com wrote:
On Wed, Sep 16, 2009 at 09:48:20PM -0400, Tom Lane wrote:
Seems like there would
be lots of situations where short exclusive-lock intervals could be
tolerated,
On Thu, 2009-09-17 at 10:21 -0400, Tom Lane wrote:
Hannu Krosing ha...@2ndquadrant.com writes:
On Wed, 2009-09-16 at 21:19 -0400, Tom Lane wrote:
VACUUM FULL CONCURRENTLY is a contradiction in terms. Wishing it were
possible doesn't make it so.
It depends on what do you mean by VACUUM
Hannu Krosing ha...@2ndquadrant.com writes:
On Thu, 2009-09-17 at 10:21 -0400, Tom Lane wrote:
because it will break applications that depend on CTID.
Do you know of any such applications out in the wild ?
Yes, they're out there.
regards, tom lane
--
Sent via
Hannu Krosing ha...@2ndquadrant.com writes:
On Thu, 2009-09-17 at 09:45 -0400, Robert Haas wrote:
Making the code more complicated so that it's easier to tune something
that isn't very hard to tune anyway doesn't seem like a good
trade-off.
I think that just making sure that pessimal cases
On Thu, Sep 17, 2009 at 10:21 AM, Tom Lane t...@sss.pgh.pa.us wrote:
Hannu Krosing ha...@2ndquadrant.com writes:
On Wed, 2009-09-16 at 21:19 -0400, Tom Lane wrote:
VACUUM FULL CONCURRENTLY is a contradiction in terms. Wishing it were
possible doesn't make it so.
It depends on what do you
On Thu, 2009-09-17 at 10:45 -0400, Tom Lane wrote:
Hannu Krosing ha...@2ndquadrant.com writes:
On Thu, 2009-09-17 at 09:45 -0400, Robert Haas wrote:
Making the code more complicated so that it's easier to tune something
that isn't very hard to tune anyway doesn't seem like a good
On Thu, 2009-09-17 at 11:25 -0400, Robert Haas wrote:
On Thu, Sep 17, 2009 at 10:21 AM, Tom Lane t...@sss.pgh.pa.us wrote:
Hannu Krosing ha...@2ndquadrant.com writes:
On Wed, 2009-09-16 at 21:19 -0400, Tom Lane wrote:
VACUUM FULL CONCURRENTLY is a contradiction in terms. Wishing it were
Robert Haas wrote:
On Thu, Sep 17, 2009 at 10:21 AM, Tom Lane t...@sss.pgh.pa.us wrote:
Hannu Krosing ha...@2ndquadrant.com writes:
On Wed, 2009-09-16 at 21:19 -0400, Tom Lane wrote:
VACUUM FULL CONCURRENTLY is a contradiction in terms. Wishing it were
possible doesn't make it so.
It
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes:
Robert Haas wrote:
On Thu, Sep 17, 2009 at 10:21 AM, Tom Lane t...@sss.pgh.pa.us wrote:
Anything that moves tuples is not acceptable as a hidden background
operation, because it will break applications that depend on CTID.
I'm a
On Thu, 2009-09-17 at 10:32 -0400, Tom Lane wrote:
Hannu Krosing ha...@2ndquadrant.com writes:
On Thu, 2009-09-17 at 10:21 -0400, Tom Lane wrote:
because it will break applications that depend on CTID.
Do you know of any such applications out in the wild ?
Yes, they're out there.
How
Simon Riggs si...@2ndquadrant.com writes:
The update utility being discussed is in danger of confusing these two
goals
* compact the table using minimal workspace
* compact the table with minimal interruption to concurrent updaters
Actually, the update utility is explicitly meant to satisfy
On Thu, 2009-09-17 at 12:18 -0400, Tom Lane wrote:
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes:
Robert Haas wrote:
On Thu, Sep 17, 2009 at 10:21 AM, Tom Lane t...@sss.pgh.pa.us wrote:
Anything that moves tuples is not acceptable as a hidden background
operation, because
On Thu, Sep 17, 2009 at 12:18 PM, Tom Lane t...@sss.pgh.pa.us wrote:
It's no different from the situation where another backend UPDATEs the
row under your nose, but it's not something you want to do automatically
without notice.
Exactly. The application is typically going to throw a
On Thu, Sep 17, 2009 at 12:31 PM, Hannu Krosing ha...@2ndquadrant.com wrote:
Exactly. The application is typically going to throw a concurrent
update type of error when this happens, and we don't want magic
background operations to cause that.
Would'nt current VACUUM FULL or CLUSTER cause
Hannu Krosing ha...@2ndquadrant.com writes:
On Thu, 2009-09-17 at 12:18 -0400, Tom Lane wrote:
Or for an update without having to hold a transaction open. We have
recommended this type of technique in the past:
If you need real locking, then just define a locked (or locked_by or
On Thu, 2009-09-17 at 12:30 -0400, Tom Lane wrote:
Simon Riggs si...@2ndquadrant.com writes:
The update utility being discussed is in danger of confusing these two
goals
* compact the table using minimal workspace
* compact the table with minimal interruption to concurrent updaters
On Wed, 16 Sep 2009, Tom Lane wrote:
* Shrink a table in place - when no space available
To be addressed by the UPDATE-style tuple-mover (which could be thought
of as VACUUM FULL rewritten to not use any special mechanisms).
Is there any synergy here with the needs of a future in-place
On Thu, 2009-09-17 at 12:36 -0400, Tom Lane wrote:
Hannu Krosing ha...@2ndquadrant.com writes:
On Thu, 2009-09-17 at 12:18 -0400, Tom Lane wrote:
Or for an update without having to hold a transaction open. We have
recommended this type of technique in the past:
If you need real
On Thu, 2009-09-17 at 14:33 -0400, Greg Smith wrote:
On Wed, 16 Sep 2009, Tom Lane wrote:
* Shrink a table in place - when no space available
To be addressed by the UPDATE-style tuple-mover (which could be thought
of as VACUUM FULL rewritten to not use any special mechanisms).
Is there
Hi,
Forewords: re-reading, I hope my english will not make this sound like a
high-kick when I'm just struggling to understand what all this is
about. Sending in order not to regret missing the oportunity I think I'm
seeing...
Tom Lane t...@sss.pgh.pa.us writes:
Hannu Krosing
Hannu Krosing ha...@2ndquadrant.com writes:
But maybe something can be tahen from this discussion the other way
round - maybe we should not be afraid of doing null updates during
in-place update
The problem for in-place update is that it can't assume that any of the
normal infrastructure (like
Dimitri Fontaine dfonta...@hi-media.com writes:
I don't see any reason why not breaking the user visible behavior of
tuples CTID between any two major releases,
Am I completely wet here?
Completely. This is a user-visible behavior that we have encouraged
people to rely on, and for which
Tom Lane t...@sss.pgh.pa.us writes:
Completely. This is a user-visible behavior that we have encouraged
people to rely on, and for which there is no easy substitute.
Excited to have self-healing tables (against bloat), I parse this as an
opening. Previously on this thread you say:
(Actually,
Hackers,
Here's the feedback on replacing VACUUM FULL with VACUUM REWRITE:
http://it.toolbox.com/blogs/database-soup/getting-rid-of-vacuum-full-feedback-needed-33959
Of note:
a) To date, I have yet to hear a single person bring up an actual
real-life use-case where VACUUM FULL was desireable
Josh Berkus j...@agliodbs.com wrote:
a) To date, I have yet to hear a single person bring up an actual
real-life use-case where VACUUM FULL was desireable and REWRITE
would not be.
Would rewrite have handled this?:
http://archives.postgresql.org/pgsql-hackers/2009-09/msg01016.php
-Kevin
Josh Berkus wrote:
Hackers,
Here's the feedback on replacing VACUUM FULL with VACUUM REWRITE:
http://it.toolbox.com/blogs/database-soup/getting-rid-of-vacuum-full-feedback-needed-33959
Of note:
a) To date, I have yet to hear a single person bring up an actual
real-life use-case where
On Wed, 2009-09-16 at 21:23 +0300, Heikki Linnakangas wrote:
2) Another utility that does something like UPDATE ... WHERE ctid ? to
move tuples to lower pages. It will be different from current VACUUM
FULL in some ways. It won't require a table lock, for example, but it
won't be able to move
On 9/16/09 11:20 AM, Kevin Grittner wrote:
Josh Berkus j...@agliodbs.com wrote:
a) To date, I have yet to hear a single person bring up an actual
real-life use-case where VACUUM FULL was desireable and REWRITE
would not be.
Would rewrite have handled this?:
On Wed, 2009-09-16 at 11:10 -0700, Josh Berkus wrote:
Hackers,
Here's the feedback on replacing VACUUM FULL with VACUUM REWRITE:
http://it.toolbox.com/blogs/database-soup/getting-rid-of-vacuum-full-feedback-needed-33959
Of note:
a) To date, I have yet to hear a single person bring up an
On Wed, 2009-09-16 at 21:23 +0300, Heikki Linnakangas wrote:
I was just going to post that we should make a decision about this,
because ISTM there's some code in Simon's hot standby patch that is only
required to support VACUUM FULL. If we make the decision that we drop
VACUUM FULL in 8.5,
Hannu,
The only case is when you are out of disk space and can't afford to
write out a full set of live rows.
Well, it's actually rather specific. You need to have:
a) *Some* free disk space (FULL requires extra disk) but not enough to
copy one entire table and its indexes.
b) be already
On Wed, 2009-09-16 at 11:10 -0700, Josh Berkus wrote:
Hackers,
Here's the feedback on replacing VACUUM FULL with VACUUM REWRITE:
http://it.toolbox.com/blogs/database-soup/getting-rid-of-vacuum-full-feedback-needed-33959
Of note:
a) To date, I have yet to hear a single person bring up an
On Wed, 2009-09-16 at 13:20 -0500, Kevin Grittner wrote:
Josh Berkus j...@agliodbs.com wrote:
a) To date, I have yet to hear a single person bring up an actual
real-life use-case where VACUUM FULL was desireable and REWRITE
would not be.
Would rewrite have handled this?:
On Wed, 2009-09-16 at 11:48 -0700, Josh Berkus wrote:
Hannu,
The only case is when you are out of disk space and can't afford to
write out a full set of live rows.
Well, it's actually rather specific. You need to have:
a) *Some* free disk space (FULL requires extra disk) but not
Hannu,
If it is a sequence of
1. ordinary VACUUM (it can't run out of FSM anymore, no?)
2. a process moving live tuples from end (using reverse seqscan) to free
space found scanning in first-to-last direction, either one tuple at a
time or one page at a time, until the two scans meet
3.
Hannu Krosing wrote:
On Wed, 2009-09-16 at 21:23 +0300, Heikki Linnakangas wrote:
2) Another utility that does something like UPDATE ... WHERE ctid ? to
move tuples to lower pages. It will be different from current VACUUM
FULL in some ways. It won't require a table lock, for example, but it
On Wed, 2009-09-16 at 23:53 +0300, Heikki Linnakangas wrote:
Hannu Krosing wrote:
On Wed, 2009-09-16 at 21:23 +0300, Heikki Linnakangas wrote:
2) Another utility that does something like UPDATE ... WHERE ctid ? to
move tuples to lower pages. It will be different from current VACUUM
FULL
On Wed, 2009-09-16 at 23:53 +0300, Heikki Linnakangas wrote:
For the kicks, I looked at what it would take to write a utility like
that. It turns out to be quite trivial, patch attached. It uses the same
principle as VACUUM FULL, scans from the end, moving tuples to
lower-numbered pages until
On Wed, Sep 16, 2009 at 4:53 PM, Heikki Linnakangas
heikki.linnakan...@enterprisedb.com wrote:
Hannu Krosing wrote:
On Wed, 2009-09-16 at 21:23 +0300, Heikki Linnakangas wrote:
2) Another utility that does something like UPDATE ... WHERE ctid ? to
move tuples to lower pages. It will be
On Wed, Sep 16, 2009 at 1:42 PM, Josh Berkus j...@agliodbs.com wrote:
On 9/16/09 11:20 AM, Kevin Grittner wrote:
Josh Berkus j...@agliodbs.com wrote:
a) To date, I have yet to hear a single person bring up an actual
real-life use-case where VACUUM FULL was desireable and REWRITE
would not
Robert Haas robertmh...@gmail.com wrote:
I think this should be in core, not a contrib module.
+1
I also wonder whether we should consider teaching regular VACUUM to
do a little of this every time it's run. Right now, once your table
gets bloated, it stays bloated forever, until you
Robert Haas wrote:
Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote:
Hannu Krosing wrote:
On Wed, 2009-09-16 at 21:23 +0300, Heikki Linnakangas wrote:
2) Another utility that does something like UPDATE ... WHERE ctid ? to
I also wonder whether we should consider teaching regular
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes:
For the kicks, I looked at what it would take to write a utility like
that. It turns out to be quite trivial, patch attached.
I don't think you've really thought this through; particularly not this:
+ rel = heap_open(relid,
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes:
I'm in favor of removing VACUUM FULL in 8.5. To replace it, we should offer:
1) VACUUM REWRITE, which is like CLUSTER but doesn't use an index, and
Check, although I'm not eager to make REWRITE a fully reserved word,
which is what
On Wed, 2009-09-16 at 21:23 +0300, Heikki Linnakangas wrote:
I was just going to post that we should make a decision about this,
because ISTM there's some code in Simon's hot standby patch that is only
required to support VACUUM FULL. If we make the decision that we drop
VACUUM FULL in 8.5,
On Wed, 2009-09-16 at 11:40 -0700, Jeff Davis wrote:
Another thing to think about is that lazy vacuum only shrinks the heap
file if it happens to be able to acquire an access exclusive lock.
Because vacuum can't be run inside a transaction block, I don't think
there's currently a way to
Well, Andrew McNamara just posted today:
http://archives.postgresql.org/message-id/20090916063341.0735c5ac...@longblack.object-craft.com.au
Had VACUUM FULL not been available, though, I'm pretty sure he would've
come up with something else instead.
Indeed I would have. And it was our own
On Wed, 2009-09-16 at 20:36 -0400, Tom Lane wrote:
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes:
For the kicks, I looked at what it would take to write a utility like
that. It turns out to be quite trivial, patch attached.
I don't think you've really thought this through;
Simon Riggs si...@2ndquadrant.com writes:
What we need is VACUUM FULL CONCURRENTLY and REINDEX CONCURRENTLY.
VACUUM FULL CONCURRENTLY is a contradiction in terms. Wishing it were
possible doesn't make it so.
regards, tom lane
--
Sent via pgsql-hackers mailing list
On Wed, 2009-09-16 at 21:00 -0400, Tom Lane wrote:
But if
we get rid of old-style VACUUM FULL then we do need something to cover
those few-and-far-between situations where you really do desperately
need to compact a table in place; and a utility like this seems like a
reasonable solution.
Simon Riggs si...@2ndquadrant.com writes:
I think it would be useful to have an additional option to force VACUUM
to wait for the lock so it can truncate. It's annoying to have to re-run
VACUUM just to give it a chance at the lock again.
It would be better to separate out the
Simon Riggs si...@2ndquadrant.com writes:
The way I read the thread so far is that there are multiple
requirements:
* Shrink a table efficiently - when time and space available to do so
To be addressed by the CLUSTER-based solution (VACUUM REWRITE or
whatever we call it).
* Shrink a table
On Wed, Sep 16, 2009 at 9:35 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Simon Riggs si...@2ndquadrant.com writes:
The way I read the thread so far is that there are multiple
requirements:
* Shrink a table efficiently - when time and space available to do so
To be addressed by the CLUSTER-based
Robert Haas robertmh...@gmail.com writes:
On Wed, Sep 16, 2009 at 9:35 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Simon Riggs si...@2ndquadrant.com writes:
* Shrink a table concurrently - when no dedicated time available
Wishful thinking, which should not stop us from proceeding with the
On Wed, 2009-09-16 at 21:48 -0400, Tom Lane wrote:
Yeah, I was just wondering about that myself. Seems like there would
be lots of situations where short exclusive-lock intervals could be
tolerated, even though not long ones.
But a short-lived exclusive lock can turn into a long-lived
Jeff Davis pg...@j-davis.com writes:
On Wed, 2009-09-16 at 21:48 -0400, Tom Lane wrote:
Yeah, I was just wondering about that myself. Seems like there would
be lots of situations where short exclusive-lock intervals could be
tolerated, even though not long ones.
But a short-lived exclusive
Tom Lane wrote:
Robert Haas robertmh...@gmail.com writes:
On Wed, Sep 16, 2009 at 9:35 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Simon Riggs si...@2ndquadrant.com writes:
* Shrink a table concurrently - when no dedicated time available
Wishful thinking, which should not stop us from proceeding
80 matches
Mail list logo