FAQ
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.

Search Discussions

  • Jasen Betts at Jun 24, 2010 at 4:00 am

    On 2010-06-24, Jean-Yves F. Barbier 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.
  • Tom Lane at Jun 24, 2010 at 4:48 am

    Jasen Betts writes:
    On 2010-06-24, Jean-Yves F. Barbier 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
  • Mladen Gogala at Jun 24, 2010 at 5:00 am

    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
  • Tom Lane at Jun 24, 2010 at 5:11 am

    Mladen Gogala 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 at Jun 24, 2010 at 7:55 pm
    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
  • Mladen Gogala at Jun 24, 2010 at 8:57 pm
    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
  • Jean-Yves F. Barbier at Jun 24, 2010 at 11:39 am
    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 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.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedJun 24, '10 at 3:19a
activeJun 24, '10 at 8:57p
posts8
users5
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase