Hi,
I have create the following tables: 1. rnc table CREATE TABLE act_rnc(rnc_id integer NOT NULL PRIMARY KEY, rnc_data BYTEA); 2. rncgen table CREATE TABLE act_rncgen(rnc_id integer NOT NULL PRIMARY KEY, rncsubObj_Cnt integer, rncgen_data BYTEA); 3. iuo table which has a foreign key reference to rnc table CREATE TABLE act_iuo(iuo_id integer NOT NULL primary key, rnc_id integer NOT NULL, iuo_data BYTEA, FOREIGN KEY(rnc_id) references act_rnc(rnc_id) on delete cascade); Now i open two transactions (separate session with psql). In the first transaction I give the following sql sequence: begin; update act_rnc set rnc_data='rnc_data' where rnc_id=1; The transaction will be open. In a second transaction i give the following sql sequence: begin; insert into act_iuo values (1,1,'iuo_data'); --> now the second transaction is blocked. I work with PostgreSQL 9.0. Some outputs: select * from pg_locks; locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted ---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+------------------+--------- tuple | 16385 | 16427 | 0 | 8 | | | | | | 3/80 | 9230 | ShareLock | t relation | 16385 | 10985 | | | | | | | | 4/247 | 16535 | AccessShareLock | t virtualxid | | | | | 4/247 | | | | | 4/247 | 16535 | ExclusiveLock | t relation | 16385 | 16443 | | | | | | | | 3/80 | 9230 | RowExclusiveLock | t transactionid | | | | | | 584 | | | | 3/80 | 9230 | ExclusiveLock | t virtualxid | | | | | 3/80 | | | | | 3/80 | 9230 | ExclusiveLock | t relation | 16385 | 16433 | | | | | | | | 3/80 | 9230 | AccessShareLock | t relation | 16385 | 16427 | | | | | | | | 5/535 | 2814 | RowExclusiveLock | t virtualxid | | | | | 5/535 | | | | | 5/535 | 2814 | ExclusiveLock | t transactionid | | | | | | 583 | | | | 5/535 | 2814 | ExclusiveLock | t relation | 16385 | 16449 | | | | | | | | 3/80 | 9230 | RowExclusiveLock | t relation | 16385 | 16427 | | | | | | | | 3/80 | 9230 | RowShareLock | t transactionid | | | | | | 583 | | | | 3/80 | 9230 | ShareLock | f relation | 16385 | 16433 | | | | | | | | 5/535 | 2814 | RowExclusiveLock | t (14 rows) select relname, pg_class.oid from pg_class; act_rnc_pkey | 16433 pg_inherits_parent_index | 2187 pg_inherits_relid_seqno_index | 2680 pg_toast_16435 | 16438 pg_trigger_oid_index | 2702 pg_toast_16435_index | 16440 act_rncgen | 16435 act_rncgen_pkey | 16441 pg_toast_16443 | 16446 pg_toast_16443_index | 16448 act_iuo_pkey | 16449 pg_amop | 2602 act_iuo | 16443 pg_largeobject | 2613 act_rnc | 16427 pg_toast_11361 | 11363 pg_toast_11361_index | 11365 pg_toast_11366_index | 11370 I assume that the access to act_rnc_pkey causes the blocking, however why? Or how I can resolve the blocking (commit one transaction solves the problem, but should Postgres not recognize the blocking situation and release one transaction?). Is this an error in Postgres? -- Cheers, Prakash
