Re: RI - Mailing list pgsql-novice

BTW, why was the transactionid column NULL? I did have "BEGIN
TRANSACTION", so I was doing things within a legitimate transaction, it
should have been non-null value.

Mladen Gogala wrote:
> 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