Re: Foreign keys and locks. - Mailing list pgsql-general

Hi all,
Today I found this behavior on two tables.

I have 2 tables t2 and t3 , described as below:

testdb=# \d t2
                     Tabella "public.t2"
 Colonna |  Tipo   | Ordinamento | Può essere null | Default
---------+---------+-------------+-----------------+---------
 id      | integer |             | not null        |
 value   | integer |             |                 |
Indici:
    "t2_id_p_key" PRIMARY KEY, btree (id)
    "t2_rate_idx" btree (value)
Referenziato da:
    TABLE "t3" CONSTRAINT "t3_roomrate_fkey" FOREIGN KEY (value) REFERENCES t2(id) ON UPDATE CASCADE ON DELETE CASCADE

testdb=# \d t3
                     Tabella "public.t3"
 Colonna |  Tipo   | Ordinamento | Può essere null | Default
---------+---------+-------------+-----------------+---------
 id      | integer |             | not null        |
 value   | integer |             |                 |
Indici:
    "t3_id_p_key" PRIMARY KEY, btree (id)
Vincoli di integrità referenziale
    "t3_roomrate_fkey" FOREIGN KEY (value) REFERENCES t2(id) ON UPDATE CASCADE ON DELETE CASCADE

the two tables are populated with records that satisfy the referential integrity constraints.

1) If I execute

testdb=# delete from t2 where id=1978800 ;

it  works;

2) but if I execute
testdb=# delete from t2 where value=20342;

postgresql applies locks and the query freezes.

testdb=# SELECT locktype, relation::regclass, mode, transactionid AS tid,
virtualtransaction AS vtid, l.pid, granted,a.query
FROM pg_catalog.pg_locks l
JOIN pg_stat_activity a on a.pid=l.pid
LEFT JOIN pg_catalog.pg_database db
ON db.oid = l.database WHERE (db.datname = 'testdb')
AND NOT l.pid = pg_backend_pid()
and a.state = 'active';
 locktype |  relation   |       mode       | tid |  vtid  | pid  | granted |               query              
----------+-------------+------------------+-----+--------+------+---------+-----------------------------------
 relation | t3_id_p_key | RowExclusiveLock |     | 9/1475 | 8685 | t       | delete from t2 where value=20342;
 relation | t3          | RowExclusiveLock |     | 9/1475 | 8685 | t       | delete from t2 where value=20342;
 relation | t2_rate_idx | RowExclusiveLock |     | 9/1475 | 8685 | t       | delete from t2 where value=20342;
 relation | t2_id_p_key | RowExclusiveLock |     | 9/1475 | 8685 | t       | delete from t2 where value=20342;
 relation | t2          | RowExclusiveLock |     | 9/1475 | 8685 | t       | delete from t2 where value=20342;


I tried to check the problem on other tables but on other tables I did not find the same behavior
and the second type of query works without problems.

Any Ideas?

Thanks in advance for your reply

Enrico