I did decide to put your words to the test, so I added a foreign key to
the well known SCOTT/TIGER schema in Postgres:

scott=# \d+ emp
Table "public.emp"
Column | Type | Modifiers | Storage |
empno | smallint | not null | plain |
ename | character varying(10) | not null | extended |
job | character varying(9) | | extended |
mgr | smallint | | plain |
hiredate | timestamp without time zone | | plain |
sal | double precision | | plain |
comm | double precision | | plain |
deptno | smallint | | plain |
"emp_pkey" PRIMARY KEY, btree (empno)
"emp_ename_id" btree (ename)
"ind_emp_deptno" btree (deptno)
Foreign-key constraints:
"fk_deptno" FOREIGN KEY (deptno) REFERENCES dept(deptno)
Has OIDs: no

The next thing to do was to update the parent table:
scott=# begin transaction;
Time: 0.133 ms
scott=# update dept set dname='ACCOUNTING' where deptno=10;
Time: 44.408 ms
scott=# update dept set deptno=10 where dname='ACCOUNTING';
Time: 0.823 ms

The query to monitor locks was the following:
pg_locks.mode, pg_locks.granted,

age(now(),pg_stat_activity.query_start) as "age",
from pg_stat_activity,pg_locks left outer join pg_class on
(pg_locks.relation = pg_class.oid)
where pg_locks.pid=pg_stat_activity.procpid and
pg_class.relname not like 'pg_%'
order by query_start;

The result was somewhat surprising:

datname | relname | transactionid | mode | granted |
usename | substr | query_start |
age | procpid
scott | dept | | RowExclusiveLock | t |
mgogala | <IDLE> in transaction | 2010-06-24 15:33:53.699563-04 |
00:02:41.84465 | 30861
scott | dept_pkey | | RowExclusiveLock | t |
mgogala | <IDLE> in transaction | 2010-06-24 15:33:53.699563-04 |
00:02:41.84465 | 30861
(2 rows)

There were 2 Row-X locks, one on the table, another one on the index. I
also checked for Oracle and the locking of the child table was eliminated.

Tom Lane wrote:
Mladen Gogala <[email protected]> writes:
Interesting question. When modifying the parent record, Oracle RDBMS
locks the entire child table in shared mode, unless an index on the
child table is present. What does Postgres do in that situation? Can
Postgres somehow locate the corresponding child record(s) without an
Sure ... it'll just seqscan the child table. Obviously, this will be
horridly slow --- but as stated, if it's something you very rarely do,
you might not want to pay the overhead of an extra index on the child
table in order to make it faster. It's a tradeoff, you pays your money
and you takes your choice.

This feature of Oracle RDBMS was a source of countless deadlocks
during my 20+ years as an Oracle professional. When I come to think of
it, Postgres probably does the same thing to prevent an update of the
child table while the update of the parent table is going on. I confess
not having time to try. Can you elaborate a bit on that?
No, we don't lock the whole table. The way the anti-race-condition
interlock works is that an insert into the child table attempts to
share-lock the referenced (parent) row. If successful, that prevents a
delete of the referenced row until the child insert has committed.
(After it's committed, no lock is needed because any attempted delete of
the parent row will be able to see that there's a child row.) You can
get some deadlocks that way too, of course, but they're different from
what you're saying Oracle does.

regards, tom lane


Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
The Leader in Integrated Media Intelligence Solutions

Search Discussions

Discussion Posts


Follow ups

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 7 of 8 | next ›
Discussion Overview
grouppgsql-novice @
postedJun 24, '10 at 3:19a
activeJun 24, '10 at 8:57p



site design / logo © 2023 Grokbase