Thread

1. inherited, unique serial field...

   Will Trillich <will@serensoft.com> — 2003-02-07T10:45:26Z

   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/ !
   
   

2. Re: inherited, unique serial field...

   Shridhar Daithankar <shridhar_daithankar@persistent.co.in> — 2003-02-07T11:19:28Z

   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
   
   
   

3. Re: inherited, unique serial field...

   Chris Travers <chris@travelamericas.com> — 2003-02-07T16:50:38Z

   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
   
   
   

4. Re: inherited, unique serial field...

   Will Trillich <will@serensoft.com> — 2003-02-08T05:53:50Z

   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/ !