Thread: duplicates on primary key column
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
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
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
> > 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
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
>>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
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
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