Andrew Sackville-West wrote:
> > I don't think you have provided everything: for instance I see that your
> > log has INSERTs into table access_tokens, which has a foreign key
> > relationship to/from some other table you showed.
>
> Ah you're correct, I'll attach the description of that table. But,
> really, that is all that's going on in the database.
See, access_tokens has a foreign key to access_grants. So when the
insert to access_tokens happens, the process also acquires a lock on a
tuple on access_grants, which should not conflict with the UPDATE; but
apparently it is blocking in some cases, for unknown reasons. But note
that one of the original tables had a large number of foreign keys to
other tables; are those necessary to reproduce the problem? I mean, if
you just copy those three tables to an otherwise empty database and then
run the hanging application, do you see a problem? If not, does adding
all those other tables cause the problem to show up?
Note that the set of columns covered by unique indexes is very
important. Your \d does not have an unique index on the updated column
AFAICS; but if it did have one, things would be completely different
(because then the insert would definitely conflict with the update).
It would help a lot of you posted the table definitions as pg_dump -t
output rather than \d, because it's much easier for me to reproduce the
setup, rather than cutting and pasting the \d output into CREATE TABLE
commands.
--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services