Re: RI

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 |
Description
----------+-----------------------------+-----------+----------+-------------
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 |
Indexes:
"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;
BEGIN
Time: 0.133 ms
scott=# update dept set dname='ACCOUNTING' where deptno=10;
UPDATE 1
Time: 44.408 ms
scott=# update dept set deptno=10 where dname='ACCOUNTING';
UPDATE 1
Time: 0.823 ms
scott=#

The query to monitor locks was the following:
select
pg_stat_activity.datname,pg_class.relname,pg_locks.transactionid,
pg_locks.mode, pg_locks.granted,

pg_stat_activity.usename,substr(pg_stat_activity.current_query,1,30),
pg_stat_activity.query_start,
age(now(),pg_stat_activity.query_start) as "age",
pg_stat_activity.procpid
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 <mladen(dot)gogala(at)vmsinfo(dot)com> 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
>> index?
>>
>
> 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
http://www.vmsinfo.com
The Leader in Integrated Media Intelligence Solutions