On Tue, May 31, 2011 at 8:08 PM, Tatsuo Ishii <ishii@postgresql.org> wrote:
[snip]
> In summary,
>
> 1) "LOCK table foo" cannot be used because of conflict with autovacuum
> 2) "LOCK sequence" just doesn't work
> 3) "SELECT 1 FROM LOCK sequece" fails after XID wraparound
>
> If you have other idea to serialize concurrent INSERT to a table, I
> would like to hear from you.
Sorry, I'm not real familiar with pgpool, but have you thought about
using an advisory lock on the target table, instead of a "real" lock
(SELECT ... FOR UPDATE / LOCK table)? An advisory lock should not
interfere with autovacuum. Obviously, this would only work if all the
INSERTs in your example were coming from a single application (i.e.
pgpool) which would honor the advisory lock.
Josh
