Thread: Foreign keys and locks.
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
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
Solved, it was an index that didn't work well :)
Enrico
Il giorno mer 20 nov 2019 alle ore 15:16 Enrico Pirozzi <sscotty71@gmail.com> ha scritto:
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
Enrico Pirozzi
Tel. +39 0861 1855771 - Mob.+39 328 4164437 - Fax +39 0861 1850310
http://www.pgtraining.com <http://www.pgtraining.com-> -
info@pgtraining.com
www.enricopirozzi.info - info@enricopirozzi.info
Skype sscotty71 - Gtalk sscotty71@gmail.com