Solved, it was an index that didn't work well :) Enrico
Il giorno mer 20 nov 2019 alle ore 15:16 Enrico Pirozzi <sscott...@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-> - i...@pgtraining.com www.enricopirozzi.info - i...@enricopirozzi.info Skype sscotty71 - Gtalk sscott...@gmail.com
