Re: Foreign keys and locks.
От | Enrico Pirozzi |
---|---|
Тема | Re: Foreign keys and locks. |
Дата | |
Msg-id | CAEe=WwV3gUE4vyvAq=ctD+6birxNrMvBr3GDF-vp=bSjEJhwPw@mail.gmail.com обсуждение исходный текст |
Ответ на | Foreign keys and locks. (Enrico Pirozzi <sscotty71@gmail.com>) |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: