Re: stupid Q regarding "UPDATE waiting"

hello,

I'm doing long-running transactions which insert into tables A and B,
and when I try to update table C from another session, the update
hangs until the first transaction is done; the related postgres process
shows as "UPDATE waiting" in ps.

Why is this, and is there a way around it?

tnx,

cm.

--
Christian Mock Wiedner Hauptstrasse 15
Senior Security Engineer 1040 Wien
CoreTEC IT Security Solutions GmbH +43-1-5037273

On Tue, Aug 27, 2002 at 07:40:09PM +0200, Christian Mock wrote:
> hello,
>
> I'm doing long-running transactions which insert into tables A and B,
> and when I try to update table C from another session, the update
> 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.

A

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

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?

regards,

cm.
--
Christian Mock Wiedner Hauptstrasse 15
Senior Security Engineer 1040 Wien
CoreTEC IT Security Solutions GmbH +43-1-5037273

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(at)libertyrms(dot)info> M2P 2A8
+1 416 646 3304 x110

On Wed, 28 Aug 2002, 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?

Hmm, I may have missed it, but can you give an example of the actual
statments and schema?

The only workarounds I know about are hacking the trigger functions and
making the constraints deferred (which is only a partial solution, but
often helps in practice).