Thread: duplicates on primary key column

duplicates on primary key column

From
Marcin Gil
Date:
Same problem I reported earlier.
I have a column declared as PK but PostgreSQL 7.4 allowed it
to create entries with the same key..

Schemas, database dump available at:
http://www.vernet.pl/sql/

All files there..
Thanks!

PS. I am also available at jabber. My JID: dentharg (at) chrome pl
--
   Marcin Gil :: marcin.gil@audax.com.pl
   OIS Audax Sp. z o.o., ul. Barlickiego 4, 97-200 Tomaszów Mazowiecki
   tel (44) 7247530 w/22, 7244401 w/22 -- fax w/25

Re: duplicates on primary key column

From
Tom Lane
Date:
Marcin Gil <marcin.gil@audax.com.pl> writes:
> Same problem I reported earlier.
> I have a column declared as PK but PostgreSQL 7.4 allowed it
> to create entries with the same key..

> Schemas, database dump available at:
> http://www.vernet.pl/sql/

I see no duplicate keys in your dump.  How do you provoke the problem
exactly?

            regards, tom lane

Re: duplicates on primary key column

From
Marcin Gil
Date:
Tom Lane wrote:

>
> I see no duplicate keys in your dump.  How do you provoke the problem
> exactly?
>
That seems very odd.
Hmm.. It's enough to do 'select docid from documents where docid=0'
and I get 3 answers (3 rows).

Basically it's a simple web cms system with some kind of versioning.
Duplicate keys are created (probably) at updating the row.
After issuing an update op the row gets updated and new one with the
same data is created; ie. after an update all rows with the same docid
get same contents.
So, when doc is inserted I got one doc. After 1st update I get
(almost certainly) 2 rows, after 2nd update -- 3 rows, etc.

I'll try to investigate a little more on this but I'am very novice
pg admin :(
--
   Marcin Gil :: marcin.gil@audax.com.pl
   OIS Audax Sp. z o.o., ul. Barlickiego 4, 97-200 Tomaszów Mazowiecki
   tel (44) 7247530 w/22, 7244401 w/22 -- fax w/25

Re: duplicates on primary key column

From
Marcin Gil
Date:
>
> I'll try to investigate a little more on this but I'am very novice
> pg admin :(

Maybe you could tell what can I do to help solving the problem?

--
   Marcin Gil :: marcin.gil@audax.com.pl
   OIS Audax Sp. z o.o., ul. Barlickiego 4, 97-200 Tomaszów Mazowiecki
   tel (44) 7247530 w/22, 7244401 w/22 -- fax w/25

Re: duplicates on primary key column

From
Tom Lane
Date:
Marcin Gil <marcin.gil@audax.com.pl> writes:
> Tom Lane wrote:
>> I see no duplicate keys in your dump.  How do you provoke the problem
>> exactly?

> Hmm.. It's enough to do 'select docid from documents where docid=0'
> and I get 3 answers (3 rows).

That's because you made "archives" inherit from "documents", so the scan
includes the rows in "archives" that have docid=0.

You could do "SELECT docid FROM ONLY documents" if you don't want the
scan to include child tables.  But it's probably a bad idea to use
inheritance for the archives table at all.

            regards, tom lane

Re: duplicates on primary key column

From
Marcin Gil
Date:
>>Tom Lane wrote:
>>
> That's because you made "archives" inherit from "documents", so the scan
> includes the rows in "archives" that have docid=0.
>
I see. That's my first time with inheriting tables. I though that it
would be something as C++, ie. structure inherit only.

> You could do "SELECT docid FROM ONLY documents" if you don't want the
> scan to include child tables.  But it's probably a bad idea to use
> inheritance for the archives table at all.
>
Why it's a bad idea? Some pointing out or links would be very
helpful.

Thanks for your kindness!
--
   Marcin Gil :: marcin.gil@audax.com.pl
   OIS Audax Sp. z o.o., ul. Barlickiego 4, 97-200 Tomaszów Mazowiecki
   tel (44) 7247530 w/22, 7244401 w/22 -- fax w/25

Re: duplicates on primary key column

From
Tom Lane
Date:
Marcin Gil <marcin.gil@audax.com.pl> writes:
> I see. That's my first time with inheriting tables. I though that it
> would be something as C++, ie. structure inherit only.

As of 7.4 there is a way to do structural inheritance, but it's not this
syntax.

>> You could do "SELECT docid FROM ONLY documents" if you don't want the
>> scan to include child tables.  But it's probably a bad idea to use
>> inheritance for the archives table at all.
>>
> Why it's a bad idea?

Just because it's error prone.  I can't see that you'd ever want to
include the archives table in searches or updates of the documents table
--- but the default behavior will be to do so.

            regards, tom lane

Re: duplicates on primary key column

From
Marcin Gil
Date:
Tom Lane wrote:

>As of 7.4 there is a way to do structural inheritance, but it's not this
>syntax.
>
>
>
Ok. I'll do my reading. Is it ok to do structural inheritance for my
archives
or do you suggest to do separate structures?

Thanks
-Marcin Gil