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:
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 <[email protected]> writes:
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.
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
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 belocks 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?
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-conditionduring 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?
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
