Thread: inherited, unique serial field...

is it bad news to have id collisions when you use an inherited
serial field?

    create track (
        id serial,
        created date default current_date,
        primary key ( id )
    );

    create person (
        fname varchar(30),
        lname varchar(30),
        primary key ( id )
    ) inherits ( track );

    create other (
        val text,
    ) inherits ( track );

    insert into other ( .... );
    insert into person(id,lname)values(1,'Flintstone');
    insert into person(id,lname)values(2,'Rubble');


    SELECT
        t.id, t.created, c.relname AS class
    FROM
        track t,
        pg_class c
    WHERE
        t.id = 2
        AND
        (t.tableoid = c.oid);

     id |  created   |  class
    ----+------------+----------
      4 | 2003-02-06 | other
      4 | 2003-02-06 | person

even tho track.id is constrained to be unique, voila! we've got
duplicate "primary keys". is this evil enough to avoid -- or is
it innocuous?

--
There are 10 kinds of people:
ones that get binary, and ones that don't.

will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!

Looking for a firewall? Do you think smoothwall sucks? You're
probably right... Try the folks at http://clarkconnect.org/ !

On Friday 07 February 2003 04:15 pm, you wrote:
>      id |  created   |  class
>     ----+------------+----------
>       4 | 2003-02-06 | other
>       4 | 2003-02-06 | person
>
> even tho track.id is constrained to be unique, voila! we've got
> duplicate "primary keys". is this evil enough to avoid -- or is
> it innocuous?

But they are not in same table, are  they? I mean if you select on base table,
how do you expect primary key constraint to be held against n child tables?

I dunno what standard says about this. But as long as, select from child table
on primary key  does not return duplicate row, I wouldn't worry.

This can be a really annoying issue for somebody. But I would advice him/her
to work on table design rather than proposing it as a defect in PG.

 Shridhar

will trillich wrote:

>is it bad news to have id collisions when you use an inherited
>serial field?
>
>
I am still having trouble determining what is going on in your example
(where does the 4 come from?) but I can see where you might see some
problems if you were trying to preserve referential integrity to the
parent table.

Yes, I would avoid this problem.

Best Wishes,
Chris

On Fri, Feb 07, 2003 at 08:50:38AM -0800, Chris Travers wrote:
> will trillich wrote:
> >is it bad news to have id collisions when you use an
> >inherited serial field?
> >
> I am still having trouble determining what is going on in your
> example (where does the 4 come from?) but I can see where you
> might see some problems if you were trying to preserve
> referential integrity to the parent table.

the 4 was just a sample (duplicate) id. the child tables are
each consistent with their unique constraints -- but the parent
table, which has a unique constraint (primary key!) as well,
turns out to have duplicates in the key field ( id in this
example ).

    create table track (
        id serial,
        created date default current_date,
        primary key ( id )
    );
    create table <yada> (...) inherits ( track );

    -- inserts & sech on child tables, including id values

    SELECT t.id, t.created, c.relname AS class
    FROM track t, pg_class c
    WHERE t.id = 2 AND (t.tableoid = c.oid);

     id |  created   |  class
    ----+------------+----------
      4 | 2003-02-06 | other
      4 | 2003-02-06 | person

> Yes, I would avoid this problem.

i think it's wise, too, but i haven't run into any debilitating
circumstances because of this. yet. i wondered if i was lucky or
if it's really no big deal.

--
There are 10 kinds of people:
ones that get binary, and ones that don't.

will@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!

Looking for a firewall? Do you think smoothwall sucks? You're
probably right... Try the folks at http://clarkconnect.org/ !