Re: Foreign keys and locks.

Solved, it was an index that didn't work well :)

Enrico

Il giorno mer 20 nov 2019 alle ore 15:16 Enrico Pirozzi <sscotty71(at)gmail(dot)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(at)pgtraining(dot)com
www.enricopirozzi.info - info(at)enricopirozzi(dot)info
Skype sscotty71 - Gtalk sscotty71(at)gmail(dot)com