<br clear="all" /><span class="Apple-style-span" style="border-collapse: collapse; "><p style="font-family: arial,
sans-serif;"><font face="Courier New">Hi, </font><p><font class="Apple-style-span" face="'Courier New'"><br
/></font><fontface="Courier New" style="font-family: arial, sans-serif; ">I have create the following
tables: </font><br/><font face="Courier New" style="font-family: arial, sans-serif; ">1. rnc table</font><font
class="Apple-style-span"face="arial, sans-serif"> </font><br /><font face="Courier New" style="font-family: arial,
sans-serif;">CREATE TABLE act_rnc(rnc_id integer NOT NULL PRIMARY KEY, rnc_data BYTEA);</font><font
class="Apple-style-span"face="arial, sans-serif"> </font><br /><font face="Courier New" style="font-family: arial,
sans-serif;">2. rncgen table</font><font class="Apple-style-span" face="arial, sans-serif"> </font><br /><font
face="CourierNew" style="font-family: arial, sans-serif; ">CREATE TABLE act_rncgen(rnc_id integer NOT NULL PRIMARY KEY,
rncsubObj_Cntinteger, rncgen_data BYTEA);</font><font class="Apple-style-span" face="arial, sans-serif"> </font><br
/><fontface="Courier New" style="font-family: arial, sans-serif; ">3. iuo table which has a <span class="il"
style="background-image:initial; background-attachment: initial; background-origin: initial; background-clip: initial;
background-color:rgb(255, 255, 136); color: rgb(34, 34, 34); background-position: initial initial; background-repeat:
initialinitial; ">foreign</span> key reference to rnc table</font><font class="Apple-style-span" face="arial,
sans-serif"> </font><br/><font face="Courier New" style="font-family: arial, sans-serif; ">CREATE TABLE act_iuo(iuo_id
integerNOT NULL primary key, rnc_id integer NOT NULL, iuo_data BYTEA, <span class="il" style="background-image:
initial;background-attachment: initial; background-origin: initial; background-clip: initial; background-color:
rgb(255,255, 136); color: rgb(34, 34, 34); background-position: initial initial; background-repeat: initial initial;
">FOREIGN</span> KEY(rnc_id)references act_rnc(rnc_id) on delete cascade);</font><p style="font-family: arial,
sans-serif;"><font face="Courier New">Now i open two transactions (separate session with psql). In the first
transactionI give the following sql sequence: </font><br /><font face="Courier New">begin; </font><br /><font
face="CourierNew">update act_rnc set rnc_data='rnc_data' where rnc_id=1;</font><p style="font-family: arial,
sans-serif;"><font face="Courier New">The transaction will be open.</font><p style="font-family: arial, sans-serif;
"><fontface="Courier New">In a second transaction i give the following sql sequence: </font><br /><font face="Courier
New">begin; </font><br/><font face="Courier New">insert into act_iuo values (1,1,'iuo_data');</font><p
style="font-family:arial, sans-serif; "><font face="Courier New">--> now the second transaction is blocked. I work
withPostgreSQL 9.0.</font><p style="font-family: arial, sans-serif; "><font face="Courier New">Some outputs: </font><br
/><fontface="Courier New">select * from pg_locks;</font> <br /><font face="Courier New"> locktype | database |
relation| page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid |
mode | granted</font><p style="font-family: arial, sans-serif; "><font face="Courier
New">---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+------------------+---------</font><p
style="font-family:arial, sans-serif; "><font face="Courier New"> tuple | 16385 | 16427 | 0 | 8
| | | | | | 3/80 | 9230 | ShareLock | t</font><p
style="font-family:arial, sans-serif; "><font face="Courier New"> relation | 16385 | 10985 | |
| | | | | | 4/247 | 16535 | AccessShareLock | t</font><p
style="font-family:arial, sans-serif; "><font face="Courier New"> virtualxid | | | | |
4/247 | | | | | 4/247 | 16535 | ExclusiveLock | t</font><p
style="font-family:arial, sans-serif; "><font face="Courier New"> relation | 16385 | 16443 | |
| | | | | | 3/80 | 9230 | RowExclusiveLock | t</font><p
style="font-family:arial, sans-serif; "><font face="Courier New"> transactionid | | | |
| | 584 | | | | 3/80 | 9230 | ExclusiveLock | t</font><p
style="font-family:arial, sans-serif; "><font face="Courier New"> virtualxid | | | | |
3/80 | | | | | 3/80 | 9230 | ExclusiveLock | t</font><p
style="font-family:arial, sans-serif; "><font face="Courier New"> relation | 16385 | 16433 | |
| | | | | | 3/80 | 9230 | AccessShareLock | t</font><p
style="font-family:arial, sans-serif; "><font face="Courier New"> relation | 16385 | 16427 | |
| | | | | | 5/535 | 2814 | RowExclusiveLock | t</font><p
style="font-family:arial, sans-serif; "><font face="Courier New"> virtualxid | | | | |
5/535 | | | | | 5/535 | 2814 | ExclusiveLock | t</font><p
style="font-family:arial, sans-serif; "><font face="Courier New"> transactionid | | | |
| | 583 | | | | 5/535 | 2814 | ExclusiveLock | t</font><p
style="font-family:arial, sans-serif; "><font face="Courier New"> relation | 16385 | 16449 | |
| | | | | | 3/80 | 9230 | RowExclusiveLock | t</font><p
style="font-family:arial, sans-serif; "><font face="Courier New"> relation | 16385 | 16427 | |
| | | | | | 3/80 | 9230 | RowShareLock | t</font><p
style="font-family:arial, sans-serif; "><font face="Courier New"> transactionid | | | |
| | 583 | | | | 3/80 | 9230 | ShareLock | f</font><p
style="font-family:arial, sans-serif; "><font face="Courier New"> relation | 16385 | 16433 | |
| | | | | | 5/535 | 2814 | RowExclusiveLock | t</font><p
style="font-family:arial, sans-serif; "><font face="Courier New">(14 rows)</font><p style="font-family: arial,
sans-serif;"><font face="Courier New">select relname, pg_class.oid from pg_class;</font> <br /><font face="Courier
New"> act_rnc_pkey | 16433</font> <br /><font face="Courier
New"> pg_inherits_parent_index | 2187</font> <br /><font face="Courier
New"> pg_inherits_relid_seqno_index | 2680</font> <br /><font face="Courier
New"> pg_toast_16435 | 16438</font> <br /><font face="Courier
New"> pg_trigger_oid_index | 2702</font> <br /><font face="Courier
New"> pg_toast_16435_index | 16440</font> <br /><font face="Courier
New"> act_rncgen | 16435</font> <br /><font face="Courier
New"> act_rncgen_pkey | 16441</font> <br /><font face="Courier
New"> pg_toast_16443 | 16446</font> <br /><font face="Courier
New"> pg_toast_16443_index | 16448</font> <br /><font face="Courier
New"> act_iuo_pkey | 16449</font> <br /><font face="Courier
New"> pg_amop | 2602</font> <br /><font face="Courier
New"> act_iuo | 16443</font> <br /><font face="Courier
New"> pg_largeobject | 2613</font> <br /><font face="Courier
New"> act_rnc | 16427</font> <br /><font face="Courier
New"> pg_toast_11361 | 11363</font> <br /><font face="Courier
New"> pg_toast_11361_index | 11365</font> <br /><font face="Courier
New"> pg_toast_11366_index | 11370</font><p style="font-family: arial, sans-serif; "><font
face="CourierNew">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
releaseone transaction?). Is this an error in Postgres?</font><p><span class="Apple-style-span" style="border-collapse:
separate;">-- </span></span>Cheers,<br />Prakash<br />