Re: Feedback on getting rid of VACUUM FULL

On Thu, 2009-09-17 at 12:36 -0400, Tom Lane wrote:
> Hannu Krosing <hannu(at)2ndQuadrant(dot)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
> > locked_until) column and use that for concurrent edit control
>
> That's pessimistic locking, and it sucks for any number of reasons,
> most obviously if your client crashes or otherwise forgets to release
> the lock.

That's the (locked_by,locked_until) case. It is used for a) telling
other potential editors that "this row is being edited" and also to time
out the lock.

> The method I was illustrating is specifically meant for
> apps that would prefer optimistic locking.

But surely any reliance on internal implementation details like CTID or -
XMIN should be discouraged in ordinanry user code, or really anything
except maintenance utilities which sometimes _have_ to do that.

Still most people would _not_ want that to fail, if someone just opended
the edit windeo and then clicked "Save" without making any changes.

Telling the user the "You can't save your edited record as somebody just
changed the xmin field seems kind of silly.

> >> 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 grief in this
> > situation ?
>
> Sure, but neither of those are recommended for routine maintenance
> during live database operations.

If they were, then we would net be having this whole discussion now.

> (What you might do during maintenance windows is a different discussion.)

I aim at 24/7 operations with no maintenance window in sight

>
> regards, tom lane
>
--
Hannu Krosing http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability
Services, Consulting and Training