Re: RI - Mailing list pgsql-novice

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.gogala@vmsinfo.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