Re: stupid Q regarding "UPDATE waiting"

On Wed, Aug 28, 2002 at 12:38:21PM +0200, Christian Mock wrote:
> On Tue, Aug 27, 2002 at 02:08:17PM -0400, Andrew Sullivan wrote:
>
> > > hangs until the first transaction is done; the related postgres process
> > > shows as "UPDATE waiting" in ps.
> >
> > Do you have referential integrity on any of these?  That's the usual
> > suspect in these cases.
>
> I see. One of the tables that are inserted into in the long-running
> transaction references the table that gets updated in the "UPDATE waiting"
> transaction. But the referencing column is always NULL on import.
>
> Does that mean that there's a table-level lock? Can I get around this?

Sort of.  The problem is that UPDATE take an agressive lock in order
to make sure nobody changes the data while it's being updated.  The
RI triggers enforce a similar lock on the referenced tables.  If
something _else_ tries to do a similar update (or an update on, say,
the referenced table), you get these near-deadlock and actual
deadlock conditions.  I generally find that it is simply impossible
to keep high concurrency on a database using RI constraints if you
have to update a large number of tuples at once.

One answer seems to be to write external scripts to handle these
sorts of cases.  You can do your updates in (say) batches of 1000,
committing after each.  That's not much use to you, though, if you're
hoping for real RI and real transactional integrity.

Someone is, I think, working on adding another kind of lock to the
system to get around this; but that's no help to you now, and it
won't see the light of day in the next release, either.

Someone else has offered some (somewhat nasty) patches that lower the
lock level taken by the RI triggers; it works, but the RI triggers
aren't as strong as they ought to be.  There was some discussion of
this in the past two weeks, so you should be able to check the
archives of (I believe) -general for something like "patch" and get
the code you need, if you decide to go that route.  But beware.

The problem is, certainly, a real big one for anyone using a large
system with Postgres.  Sorry I can't be more help.

A

--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110