Re: Processing a work queue

john(at)mitre(dot)org ("John D. Burger") writes:
> I wrote:
>
>> I use a variant of The Tom Lane Solution previously pointed to,
>> your Plan 1 is very similar.
>
> Hmm, per that pointed-to post:
>
> http://archives.postgresql.org/pgsql-general/2003-05/msg00351.php
>
> I decided to run a periodic vacuum on my work queue. Lo and behold,
> I get this:
>
> ERROR: tuple concurrently updated
>
> In addition, all of my workers locked up, apparently indefinitely. I
> presume this was because I was foolishly doing VACUUM FULL, which
> locks the table. But what exactly was going on? Why did my workers
> hang? Thanks in advance, I am new to all this concurrency stuff ...

The error would be the result of two concurrent attempts to ANALYZE
the table; ANALYZE updates pg_catalog.pg_statistic, and so two
concurrent ANALYZEs on the same table will try to update some of the
same tuples.

That's quite separate from anything surrounding VACUUM FULL...
The phenomena that you can expect are thus:

- VACUUM FULL needs exclusive access to the table it is working on, so
it will have to wait for completion of any accesses by other
connections that are in progress.

- As soon as you request VACUUM FULL, any other connections that
request access to the table will be blocked until the VACUUM FULL
completes.
--
select 'cbbrowne' || '@' || 'linuxdatabases.info';
http://cbbrowne.com/info/lsf.html
Never criticize anybody until you have walked a mile in their shoes,
because by that time you will be a mile away and have their shoes.
-- email sig, Brian Servis