I'm having an odd case where my system is locking such that if I insert
into a table during a transaction, if I start a new connection and
transaction, it blocks while trying to do a similar insert until the
first transaction is committed or rolled back.

The schema is rather complex (currently 157 tables, 200 views), and I
still haven't been able to create a small test case. Everything I've
tried so far just works.

The data is private, but the schema is open source, so I probably could
work with someone on it. When I look at the pg_locks table, I seem to be
blocked on:
SELECT * FROM pg_locks WHERE granted = false;
 relation | database | transaction |  pid  |       mode       | granted
          |          |     1525932 | 30175 | ShareLock        | f

Which if I understand correctly, means that the current transaction is
intentionally blocking waiting for the other transaction to finish.

I'm currently running 8.0.3, but the database was first created under
7.4.? I confirmed this behavior on both systems.

Under what circumstances would this occur?

To try and outline the situation there is a main object table, which is
the root object. It contains a group column which is used for access
rights. There is a groupref table, which keeps track of the group rights
for each user. (Each user has specific insert,update,select rights per

The select rights are enforced by views (the tables are not publicly
accessible, the views join against the groupref table to check for
select permission).
Insert and update rights are validated by BEFORE INSERT triggers.

Most tables references the object table. Basically it is OO, but doesn't
use the postgres inheritance (in our testing postgres inheritance didn't
scale well for deep inheritance, and wasn't able to enforce uniqueness
anyway.) The views present an OO appearance, and behind the scenes
direct table foreign keys maintain referential integrity.

I have checked using RAISE NOTICE and the BEFORE INSERT trigger gets all
the way to the RETURN statement before things hang, so I haven't figured
out what is actually hanging.

I have a bzip'd version of the schema and just enough data to be useful
available here:

This is the commands to replicate the locking:

-- Connect as postgres

-- Required before any inserts, so that the TEMP env table is
-- created and filled out.
select mf_setup_env();

-- Begin a transaction and insert some data
INSERT INTO object(vgroup,otype,oname) VALUES ('test',1,'test');

-- Start a new shell, and connect again and do exactly the same thing
-- as the above.
-- It should hang until you either do END/ROLLBACK in the first
-- connection.

Thanks for any help,

Attachment: signature.asc
Description: OpenPGP digital signature

Reply via email to