Blocking Issue - Mailing list pgsql-hackers

<p><font face="Courier New" size="1">Hi, </font><br /><font face="Courier New" size="1">I have create the following
tables:</font><br /><font face="Courier New" size="1">1. rnc table</font><br /><font face="Courier New" size="1">CREATE
TABLEact_rnc(rnc_id integer NOT NULL PRIMARY KEY, rnc_data BYTEA);</font><br /><font face="Courier New" size="1">2.
rncgentable</font><br /><font face="Courier New" size="1">CREATE TABLE act_rncgen(rnc_id integer NOT NULL PRIMARY KEY,
rncsubObj_Cntinteger, rncgen_data BYTEA);</font><br /><font face="Courier New" size="1">3. iuo table which has a
foreignkey reference to rnc table</font><br /><font face="Courier New" size="1">CREATE TABLE act_iuo(iuo_id integer NOT
NULLprimary key, rnc_id integer NOT NULL, iuo_data BYTEA, FOREIGN KEY(rnc_id) references act_rnc(rnc_id) on delete
cascade);</font><p><fontface="Courier New" size="1">Now i open two transactions (separate session with psql). In the
firsttransaction I give the following sql sequence: </font><br /><font face="Courier New" size="1">begin; </font><br
/><fontface="Courier New" size="1">update act_rnc set rnc_data='rnc_data' where rnc_id=1;</font><p><font face="Courier
New"size="1">The transaction will be open. </font><p><font face="Courier New" size="1">In a second transaction i give
thefollowing sql sequence: </font><br /><font face="Courier New" size="1">begin; </font><br /><font face="Courier New"
size="1">insertinto act_iuo values (1,1,'iuo_data');</font><p><font face="Courier New" size="1">--> now the second
transactionis blocked. I work with PostgreSQL 9.0. </font><p><font face="Courier New" size="1">Some outputs: </font><br
/><fontface="Courier New" size="1">select * from pg_locks;</font><br /><font face="Courier New" size="1">   locktype   
|database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | 
pid |       mode       | granted</font><p><font face="Courier New"
size="1">---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+------------------+---------</font><p><font
face="CourierNew" size="1"> tuple         |    16385 |    16427 |    0 |     8 |            |               |        
|      |          | 3/80               |  9230 | ShareLock        | t</font><p><font face="Courier New"
size="1"> relation     |    16385 |    10985 |      |       |            |               |         |       |          |
4/247             | 16535 | AccessShareLock  | t</font><p><font face="Courier New" size="1"> virtualxid    |         
|         |      |       | 4/247      |               |         |       |          | 4/247              | 16535 |
ExclusiveLock   | t</font><p><font face="Courier New" size="1"> relation      |    16385 |    16443 |      |      
|           |               |         |       |          | 3/80               |  9230 | RowExclusiveLock |
t</font><p><fontface="Courier New" size="1"> transactionid |          |          |      |       |           
|          584 |         |       |          | 3/80               |  9230 | ExclusiveLock    | t</font><p><font
face="CourierNew" size="1"> virtualxid    |          |          |      |       | 3/80       |               |        
|      |          | 3/80               |  9230 | ExclusiveLock    | t</font><p><font face="Courier New"
size="1"> relation     |    16385 |    16433 |      |       |            |               |         |       |          |
3/80              |  9230 | AccessShareLock  | t</font><p><font face="Courier New" size="1"> relation      |    16385
|   16427 |      |       |            |               |         |       |          | 5/535              |  2814 |
RowExclusiveLock| t</font><p><font face="Courier New" size="1"> virtualxid    |          |          |      |       |
5/535     |               |         |       |          | 5/535              |  2814 | ExclusiveLock    |
t</font><p><fontface="Courier New" size="1"> transactionid |          |          |      |       |           
|          583 |         |       |          | 5/535              |  2814 | ExclusiveLock    | t</font><p><font
face="CourierNew" size="1"> relation      |    16385 |    16449 |      |       |            |               |        
|      |          | 3/80               |  9230 | RowExclusiveLock | t</font><p><font face="Courier New"
size="1"> relation     |    16385 |    16427 |      |       |            |               |         |       |          |
3/80              |  9230 | RowShareLock     | t</font><p><font face="Courier New" size="1"> transactionid |         
|         |      |       |            |           583 |         |       |          | 3/80               |  9230 |
ShareLock       | f</font><p><font face="Courier New" size="1"> relation      |    16385 |    16433 |      |      
|           |               |         |       |          | 5/535              |  2814 | RowExclusiveLock |
t</font><p><fontface="Courier New" size="1">(14 rows)</font><p><font face="Courier New" size="1">select relname,
pg_class.oidfrom pg_class;</font><br /><font face="Courier New" size="1"> act_rnc_pkey                            |
16433</font><br/><font face="Courier New" size="1"> pg_inherits_parent_index                |  2187</font><br /><font
face="CourierNew" size="1"> pg_inherits_relid_seqno_index           |  2680</font><br /><font face="Courier New"
size="1"> pg_toast_16435                         | 16438</font><br /><font face="Courier New"
size="1"> pg_trigger_oid_index                   |  2702</font><br /><font face="Courier New"
size="1"> pg_toast_16435_index                   | 16440</font><br /><font face="Courier New"
size="1"> act_rncgen                             | 16435</font><br /><font face="Courier New"
size="1"> act_rncgen_pkey                        | 16441</font><br /><font face="Courier New"
size="1"> pg_toast_16443                         | 16446</font><br /><font face="Courier New"
size="1"> pg_toast_16443_index                   | 16448</font><br /><font face="Courier New"
size="1"> act_iuo_pkey                           | 16449</font><br /><font face="Courier New"
size="1"> pg_amop                                |  2602</font><br /><font face="Courier New"
size="1"> act_iuo                                | 16443</font><br /><font face="Courier New"
size="1"> pg_largeobject                         |  2613</font><br /><font face="Courier New"
size="1"> act_rnc                                | 16427</font><br /><font face="Courier New"
size="1"> pg_toast_11361                         | 11363</font><br /><font face="Courier New"
size="1"> pg_toast_11361_index                   | 11365</font><br /><font face="Courier New"
size="1"> pg_toast_11366_index                   | 11370</font><p><font face="Courier New" size="1">I assume that the
accessto act_rnc_pkey causes the blocking, however why? Or how I can resolve the blocking (commit one transaction
solvesthe problem, but should Postgres not recognize the blocking situation and release one transaction?). Is this an
errorin Postgres?</font><p><font face="Courier New" size="1">Best Regards</font><br /><font face="Courier New"
size="1">IngoSander</font><p><i><font color="#000000" face="Brush Script MT" size="4">Best Regards/mfG</font></i><br
/><i><b><fontcolor="#FF0000" face="Brush Script MT" size="6">Ingo Sander</font></b></i><br /><font face="Times New
Roman"size="2">=========================================================</font><br /><font face="Arial Black"
size="2">NokiaSiemens Networks GmbH &Co. KG</font><br /><span lang="en-us"><font face="Arial" size="2">NWS
EP</font></span><spanlang="de"> <font face="Arial" size="2">I&V Platf Technical Service DE</font><br /></span><span
lang="en-us"></span><br/><span lang="en-us"><font face="Courier New" size="2">St.-Martin-Str. 76</font></span><br
/><spanlang="en-us"><font face="Courier New" size="2">D-81541 München</font></span><br /><span lang="de"><font
color="#008080"face="Wingdings" size="5">(</font><font face="Tahoma" size="2">Tel.:  +49-89-515938390</font></span><br
/><spanlang="de"><font color="#008080" face="Wingdings" size="5">+</font><u></u><u></u><u><font color="#0000FF"
face="Tahoma"size="2">ingo.sander@nsn.com</font></u></span><p><span lang="de"><font color="#808080" face="Arial"
size="1">NokiaSiemens Networks GmbH & Co. KG</font><font face="Times New Roman" size="1"><br /></font><font
color="#808080"face="Arial" size="1">Sitz der Gesellschaft: München / Registered office: Munich</font><font face="Times
NewRoman" size="1"><br /></font><font color="#808080" face="Arial" size="1">Registergericht: München / Commercial
registry:Munich, HRA 88537</font><font face="Times New Roman" size="1"><br /></font><font color="#808080" face="Arial"
size="1">WEEE-Reg.-Nr.:DE 52984304</font><font face="Times New Roman" size="1"><br /></font><font color="#808080"
face="Arial"size="1">Persönlich haftende Gesellschafterin / General Partner: Nokia Siemens Networks Management
GmbH</font><fontface="Times New Roman" size="1"><br /></font><font color="#808080" face="Arial"
size="1">Geschäftsleitung/ Board of Directors: Dr. Hermann Rodler, Lydia Sommer, Olaf Horsthemke</font><font
face="TimesNew Roman" size="1"><br /></font><font color="#808080" face="Arial" size="1">Vorsitzender des Aufsichtsrats
/Chairman of supervisory board: Herbert Merz</font><font face="Times New Roman" size="1"><br /></font><font
color="#808080"face="Arial" size="1">Sitz der Gesellschaft: München / Registered office: Munich</font><font face="Times
NewRoman" size="1"><br /></font><font color="#808080" face="Arial" size="1">Registergericht: München / Commercial
registry:Munich, HRB 163416</font><font face="Times New Roman" size="1"> </font></span><br /><br /><br /><br /><br
/><br/><br />