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 group). 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: http://www.arbash-meinel.com/extras/schema_and_data.sql.bz2 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 BEGIN; 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, John =:->
Description: OpenPGP digital signature