X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 192188B9FE3 for ; Thu, 17 Feb 2005 16:56:18 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 70700-10 for ; Thu, 17 Feb 2005 16:56:13 +0000 (GMT) Received: from unisoft.unisoftbg.com (unisoft.unisoftbg.com [194.12.229.193]) by svr1.postgresql.org (Postfix) with SMTP id 00F5E8B9FB2 for ; Thu, 17 Feb 2005 16:56:11 +0000 (GMT) Received: (qmail 34842 invoked by uid 1011); 17 Feb 2005 16:59:29 -0000 Received: from pginfo@t1.unisoftbg.com by unisoft.unisoftbg.com by uid 1009 with qmail-scanner-1.22-st-qms (clamdscan: 0.75. spamassassin: 2.60. Clear:RC:0(192.168.1.201):SA:0(-4.8/5.0):. Processed in 21.655907 secs); 17 Feb 2005 16:59:29 -0000 X-Antivirus-MYDOMAIN-Mail-From: pginfo@t1.unisoftbg.com via unisoft.unisoftbg.com X-Antivirus-MYDOMAIN: 1.22-st-qms (Clear:RC:0(192.168.1.201):SA:0(-4.8/5.0):. Processed in 21.655907 secs Process 34831) Received: from unknown (HELO t1.unisoftbg.com) (pginfo@t1.unisoftbg.com@192.168.1.201) by unisoft.unisoftbg.com with SMTP; 17 Feb 2005 16:59:06 -0000 Message-ID: <4214BFE2.50200@t1.unisoftbg.com> Date: Thu, 17 Feb 2005 17:01:38 +0100 From: pginfo User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.0.1) Gecko/20020823 Netscape/7.0 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Michael Fuhr Cc: Richard_D_Levine@raytheon.com, Michael Glaesemann , pgsql-sql@postgresql.org, pgsql-sql-owner@postgresql.org Subject: Re: pg primary key bug? References: <4214B466.20908@t1.unisoftbg.com> <20050217164013.GA26345@winnie.fuhr.org> Content-Type: multipart/alternative; boundary="------------040003020406010605030601" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=2.396 tagged_above=0 required=5 tests=AWL, HTML_20_30, HTML_MESSAGE, HTML_TITLE_EMPTY, RCVD_IN_XBL, UPPERCASE_25_50 X-Spam-Level: ** X-Archive-Number: 200502/192 X-Sequence-Number: 20303 --------------040003020406010605030601 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Hi, No, I do not inherited tables. The result of this query is: 01=# SELECT tableoid::regclass, * 01-# FROM a_constants_str 01-# WHERE constname = 'DOCPLAID'; tableoid | constname | fid | constvalue -----------------+-----------+-----+------------ a_constants_str | DOCPLAID | 0 | SOF_19738 a_constants_str | DOCPLAID | 0 | SOF_19738 (2 rows) regards, ivan. Michael Fuhr wrote: >On Thu, Feb 17, 2005 at 04:12:38PM +0100, pginfo wrote: > > > >>01=# select * from a_constants_str where constname='DOCPLAID' ; >>constname | fid | constvalue >>-----------+-----+------------ >>DOCPLAID | 0 | SOF_19738 >>DOCPLAID | 0 | SOF_19738 >>(2 rows) >> >> > >Do you have any inherited tables? What's the result of the following >query? > >SELECT tableoid::regclass, * >FROM a_constants_str >WHERE constname = 'DOCPLAID'; > >Inherited tables are documented to have deficiencies regarding >constraints. Observe: > >CREATE TABLE parent ( > constname varchar(30) NOT NULL, > fid integer NOT NULL, > constvalue varchar(30), > PRIMARY KEY (constname, fid) >); > >CREATE TABLE child () INHERITS (parent); > >INSERT INTO parent VALUES ('DOCPLAID', 0, 'SOF_19738'); > >INSERT INTO parent VALUES ('DOCPLAID', 0, 'SOF_19738'); >ERROR: duplicate key violates unique constraint "parent_pkey" > >INSERT INTO child VALUES ('DOCPLAID', 0, 'SOF_19738'); > >SELECT tableoid::regclass, * FROM parent; > tableoid | constname | fid | constvalue >----------+-----------+-----+------------ > parent | DOCPLAID | 0 | SOF_19738 > child | DOCPLAID | 0 | SOF_19738 >(2 rows) > > > --------------040003020406010605030601 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit Hi,

No, I do not inherited tables.

The result of this query is:
01=# SELECT tableoid::regclass, *
01-# FROM a_constants_str
01-# WHERE constname = 'DOCPLAID';
    tableoid     | constname | fid | constvalue
-----------------+-----------+-----+------------
 a_constants_str | DOCPLAID  |   0 | SOF_19738
 a_constants_str | DOCPLAID  |   0 | SOF_19738
(2 rows)

regards,
ivan.


Michael Fuhr wrote:
On Thu, Feb 17, 2005 at 04:12:38PM +0100, pginfo wrote:

  
01=# select * from a_constants_str where constname='DOCPLAID' ;
constname | fid | constvalue
-----------+-----+------------
DOCPLAID  |   0 | SOF_19738
DOCPLAID  |   0 | SOF_19738
(2 rows)
    

Do you have any inherited tables?  What's the result of the following
query?

SELECT tableoid::regclass, *
FROM a_constants_str
WHERE constname = 'DOCPLAID';

Inherited tables are documented to have deficiencies regarding
constraints.  Observe:

CREATE TABLE parent (
    constname   varchar(30) NOT NULL,
    fid         integer NOT NULL,
    constvalue  varchar(30),
    PRIMARY KEY (constname, fid)
);  
 
CREATE TABLE child () INHERITS (parent);

INSERT INTO parent VALUES ('DOCPLAID', 0, 'SOF_19738');

INSERT INTO parent VALUES ('DOCPLAID', 0, 'SOF_19738');
ERROR:  duplicate key violates unique constraint "parent_pkey"

INSERT INTO child VALUES ('DOCPLAID', 0, 'SOF_19738');

SELECT tableoid::regclass, * FROM parent;
 tableoid | constname | fid | constvalue 
----------+-----------+-----+------------
 parent   | DOCPLAID  |   0 | SOF_19738
 child    | DOCPLAID  |   0 | SOF_19738
(2 rows)

--------------040003020406010605030601--