On Tue, Apr 28, 2015 at 11:32 AM, Robert Haas wrote:
> On Fri, Apr 24, 2015 at 3:04 PM, Alvaro Herrera
> 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 infr
On Tue, Apr 28, 2015 at 2:44 PM, Alvaro Herrera
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 to
>> > CREATE IN
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. T
Robert Haas wrote:
> On Fri, Apr 24, 2015 at 3:04 PM, Alvaro Herrera
> 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, tha
On Fri, Apr 24, 2015 at 3: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 wo
On 4/25/15 6:30 AM, Simon Riggs wrote:
On 24 April 2015 at 22:36, Jim Nasby 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 tuple goes. For this partic
On 24 April 2015 at 22:36, Jim Nasby 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
> selection logic, but someon
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. I
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 cha
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 eas
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 clus
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 utilit
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 q
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
> 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
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 h
On Thu, 2009-09-17 at 17:44 -0400, Tom Lane wrote:
> Dimitri Fontaine 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 en
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 sub
Tom Lane 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, the ctid is onl
Dimitri Fontaine 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 there is no easy substit
Hannu Krosing 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 index insertion or
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 writes:
> Hannu Krosing writes:
>> On Wed, 2009-09-16 at 21:
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).
>
On Thu, 2009-09-17 at 12:36 -0400, Tom Lane wrote:
> Hannu Krosing 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 jus
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 upgrad
On Thu, 2009-09-17 at 12:30 -0400, Tom Lane wrote:
> Simon Riggs 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
Hannu Krosing 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
> locked_until) column and u
On Thu, Sep 17, 2009 at 12:31 PM, Hannu Krosing 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 much more grie
On Thu, Sep 17, 2009 at 12:18 PM, Tom Lane 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 "concurrent
> updat
On Thu, 2009-09-17 at 12:18 -0400, Tom Lane wrote:
> Heikki Linnakangas writes:
> > Robert Haas wrote:
> >> On Thu, Sep 17, 2009 at 10:21 AM, Tom Lane wrote:
> >>> Anything that moves tuples is not acceptable as a hidden background
> >>> operation, because it will break applications that depend o
Simon Riggs 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 both of
those goals
On Thu, 2009-09-17 at 10:32 -0400, Tom Lane wrote:
> Hannu Krosing 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 do they dea
Heikki Linnakangas writes:
> Robert Haas wrote:
>> On Thu, Sep 17, 2009 at 10:21 AM, Tom Lane 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 bit confused. CTIDs change all the time an
Robert Haas wrote:
> On Thu, Sep 17, 2009 at 10:21 AM, Tom Lane wrote:
>> Hannu Krosing 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 b
On Thu, 2009-09-17 at 11:25 -0400, Robert Haas wrote:
> On Thu, Sep 17, 2009 at 10:21 AM, Tom Lane wrote:
> > Hannu Krosing 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
On Thu, 2009-09-17 at 10:45 -0400, Tom Lane wrote:
> Hannu Krosing 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.
>
> >
On Thu, Sep 17, 2009 at 10:21 AM, Tom Lane wrote:
> Hannu Krosing 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"
>
> Anyt
Hannu Krosing 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 don't happen sh
Hannu Krosing 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 pgsql-hackers mailing li
On Thu, 2009-09-17 at 10:21 -0400, Tom Lane wrote:
> Hannu Krosing 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"
>
On Thu, 2009-09-17 at 09:45 -0400, Robert Haas wrote:
> On Thu, Sep 17, 2009 at 9:35 AM, Joshua Tolley 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 thou
Hannu Krosing 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 hidden backgrou
On Thu, Sep 17, 2009 at 9:35 AM, Joshua Tolley 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 being able
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 Wed, 2009-09-16 at 21:19 -0400, Tom Lane wrote:
> Simon Riggs 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 by "VACUUM FULL
On Wed, 2009-09-16 at 23:12 -0400, Tom Lane wrote:
> Jeff Davis 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 t
Tom Lane wrote:
> Robert Haas writes:
>> On Wed, Sep 16, 2009 at 9:35 PM, Tom Lane wrote:
>>> Simon Riggs writes:
* Shrink a table concurrently - when no dedicated time available
>>> Wishful thinking, which should not stop us from proceeding with the
>>> solutions we know how to implement.
Jeff Davis 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 lock can t
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 exclu
Robert Haas writes:
> On Wed, Sep 16, 2009 at 9:35 PM, Tom Lane wrote:
>> Simon Riggs writes:
>>> * Shrink a table concurrently - when no dedicated time available
>>
>> Wishful thinking, which should not stop us from proceeding with the
>> solutions we know how to implement.
> The UPDATE-style
On Wed, Sep 16, 2009 at 9:35 PM, Tom Lane wrote:
> Simon Riggs 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
>
Simon Riggs 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 in place - when no
Simon Riggs 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 truncate-what-you-can behavior
as an
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 solutio
Simon Riggs 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 (pgsql-hackers@postgresq
On Wed, 2009-09-16 at 20:36 -0400, Tom Lane wrote:
> Heikki Linnakangas 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:
>
>
>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 sla
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 e
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.
Heikki Linnakangas 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 this would entail. I would propos
Heikki Linnakangas 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, AccessShareLock);
You can NOT modi
Robert Haas wrote:
> 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
> I also wonder whether we should consider teaching regular VACUUM to do
> a little
Robert Haas 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 intervene. Making
>
On Wed, Sep 16, 2009 at 1:42 PM, Josh Berkus wrote:
> On 9/16/09 11:20 AM, Kevin Grittner wrote:
>> Josh Berkus 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
On Wed, Sep 16, 2009 at 4:53 PM, 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
>>
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 unt
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 VACUU
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, b
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
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
On Wed, 2009-09-16 at 13:20 -0500, Kevin Grittner wrote:
> Josh Berkus 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://archive
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 brin
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 d
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: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 brin
On 9/16/09 11:20 AM, Kevin Grittner wrote:
> Josh Berkus 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/
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 m
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
Josh Berkus 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
--
Sent via
79 matches
Mail list logo
