Thread: RI
Hi list, does a Referential Integrity toward a table also acts like an index, or am I obliged to create this index? JY -- But sex and drugs and rock & roll, why, they'd bring our blackest day.
On 2010-06-24, Jean-Yves F. Barbier <12ukwn@gmail.com> wrote: > Hi list, > > does a Referential Integrity toward a table also acts like an index, or > am I obliged to create this index? The RI does not create any indices. There is no requirement to create an index. In most cases creating an index at one or both ends or the reference is a good idea.
Jasen Betts <jasen@xnet.co.nz> writes:
> On 2010-06-24, Jean-Yves F. Barbier <12ukwn@gmail.com> wrote:
>> does a Referential Integrity toward a table also acts like an index, or
>> am I obliged to create this index?
> The RI does not create any indices.
> There is no requirement to create an index.
> In most cases creating an index at one or both ends or the reference
> is a good idea.
Well, it's a little bit more complicated than that. A foreign key
constraint can only be created when the referenced (primary key) column
has a unique or primary key constraint. In Postgres, a unique/PK
constraint always has an associated index. So you're already guaranteed
an index on that end of the FK. What will not be present, unless you
create it, is an index on the referencing column. It often is a good
idea to create that index too, but there are some cases where such an
index isn't worth its maintenance overhead. You will want such an index
if you often change or delete entries in the referenced column. If you
seldom do that, and don't often issue queries on the referencing column,
then maybe you don't need that index.
regards, tom lane
Tom Lane wrote: > Well, it's a little bit more complicated than that. A foreign key > constraint can only be created when the referenced (primary key) column > has a unique or primary key constraint. In Postgres, a unique/PK > constraint always has an associated index. So you're already guaranteed > an index on that end of the FK. What will not be present, unless you > create it, is an index on the referencing column. It often is a good > idea to create that index too, but there are some cases where such an > index isn't worth its maintenance overhead. You will want such an index > if you often change or delete entries in the referenced column. If you > seldom do that, and don't often issue queries on the referencing column, > then maybe you don't need that index. > > 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? 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? -- Mladen Gogala Sr. Oracle DBA 1500 Broadway New York, NY 10036 (212) 329-5251 www.vmsinfo.com
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
Le Thu, 24 Jun 2010 00:48:18 -0400, Tom Lane <tgl@sss.pgh.pa.us> a écrit : > Jasen Betts <jasen@xnet.co.nz> writes: > > On 2010-06-24, Jean-Yves F. Barbier <12ukwn@gmail.com> wrote: > >> does a Referential Integrity toward a table also acts like an index, or > >> am I obliged to create this index? > > > The RI does not create any indices. > > There is no requirement to create an index. > > In most cases creating an index at one or both ends or the reference > > is a good idea. > > Well, it's a little bit more complicated than that. A foreign key > constraint can only be created when the referenced (primary key) column > has a unique or primary key constraint. In Postgres, a unique/PK > constraint always has an associated index. So you're already guaranteed > an index on that end of the FK. What will not be present, unless you > create it, is an index on the referencing column. It often is a good > idea to create that index too, but there are some cases where such an > index isn't worth its maintenance overhead. You will want such an index > if you often change or delete entries in the referenced column. If you > seldom do that, and don't often issue queries on the referencing column, > then maybe you don't need that index. This is crystal clear :) Thanks Tom JY -- Sure, and of course I would vote for a woman for president! Quite naturally, we wouldn't have to pay her so much.
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
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