Thread: Primary Key Problems
Here is a good one. I have three fields set for my primary key. Now I
thought that a primary key was unique, and dups can not be inserted.
CREATE TABLE "inventory" (
"stock" character varying(50) NOT NULL,
"inventory_type" character varying(20) DEFAULT 'unknown' NOT NULL,
"client_id" integer NOT NULL,
[...]
Constraint "inventory_pkey"
Primary Key ("stock", "inventory_type", "client_id")
);
and I insert into the db and get dups.
client_id | stock | inventory_type
-----------+-------+----------------
81 | 2001 | new
81 | 2001 | new
81 | 2003 | new
81 | 2005 | new
81 | 2006 | new
81 | 2006 | new
81 | 2008 | new
81 | 2008 | new
Why did I get dups in my data?
PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.95.4
Thanks.
--
Reason: Flat tire on station wagon with tapes. ("Never underestimate the bandwidth of a station wagon full of tapes
hurlingdown the highway" Andrew S. Tanenbaum)
_ | _
(_()(|('.|)('||.|()|`|(
On Saturday 08 December 2001 02:44, Phill Kenoyer wrote:
> Here is a good one. I have three fields set for my primary key. Now I
> thought that a primary key was unique, and dups can not be inserted.
That's the theory ;-)
> CREATE TABLE "inventory" (
> "stock" character varying(50) NOT NULL,
> "inventory_type" character varying(20) DEFAULT 'unknown' NOT NULL,
> "client_id" integer NOT NULL,
> [...]
> Constraint "inventory_pkey"
> Primary Key ("stock", "inventory_type", "client_id")
> );
>
> and I insert into the db and get dups.
>
> client_id | stock | inventory_type
> -----------+-------+----------------
> 81 | 2001 | new
> 81 | 2001 | new
> 81 | 2003 | new
> 81 | 2005 | new
> 81 | 2006 | new
> 81 | 2006 | new
> 81 | 2008 | new
> 81 | 2008 | new
>
> Why did I get dups in my data?
Are the duplicated values in "stock" and "inventory_type"
genuinely identical? As they are both VARCHARs they could
contain differing amounts of trailing spaces or other
'invisible' characters which would not be obvious from the
console, but would represent valid and unique values
to the constraint checker. These could result from
unfiltered input, such as from an HTML form.
Also, does the primary key still exist?
SELECT relname, relowner FROM pg_class WHERE relname='inventory_pkey'
Ian Barwick
I took your advice and checked the string size using char_length() and
octet_length(). They are the same.
octet_length | stock | octet_length | inventory_type | client_id
5 | 10725 | 4 | used | 60
5 | 10725 | 4 | used | 60
I do a daily pg_dump of the database and the scheme I posted was from
that. So the primary key is still active.
I just dont understand how I can have dups in the primary keys, unless
it is a bug.
|On 011207 18:51
|Ian Barwick (barwick@gmx.net) wrote the following...
|Are the duplicated values in "stock" and "inventory_type"
|genuinely identical? As they are both VARCHARs they could
|contain differing amounts of trailing spaces or other
|'invisible' characters which would not be obvious from the
|console, but would represent valid and unique values
|to the constraint checker. These could result from
|unfiltered input, such as from an HTML form.
|
|Also, does the primary key still exist?
|
|SELECT relname, relowner FROM pg_class WHERE relname='inventory_pkey'
|
|Ian Barwick
|
--
Reason: Did you pay the new Support Fee?
_ | _
(_()(|('.|)('||.|()|`|(
On Fri, 7 Dec 2001, Phill Kenoyer wrote: > I took your advice and checked the string size using char_length() and > octet_length(). They are the same. > > octet_length | stock | octet_length | inventory_type | client_id > 5 | 10725 | 4 | used | 60 > 5 | 10725 | 4 | used | 60 > > I do a daily pg_dump of the database and the scheme I posted was from > that. So the primary key is still active. > > I just dont understand how I can have dups in the primary keys, unless > it is a bug. Hmm, what does explain show for your query? I'd wonder if the unique index got corrupted for some reason and so didn't see the first value when the second was inserted. If you search for a specific value and try to force an index scan (use set enable_seqscan=false) do you still get two rows (and does explain in that case show it using the index?)
Phill and Glen, We've just tracked down one mechanism that allows duplicate rows to be spawned --- see http://fts.postgresql.org/db/mw/msg.html?mid=1078374 and following discussion. In the example given by Brian Hirt, VACUUM's creation of a duplicate row causes a unique-key violation to be reported, but I think if he'd made the indexes in the other order, the error would go undetected, leaving duplicate rows in the table. What I'm currently puzzling over is whether this bug explains your recent problem reports, or whether there are still more bugs lurking. The bug is actually fairly general: checking the validity of a tuple while a VACUUM is in process on the table can lead to the tuple being marked good when it shouldn't be. But I do not currently see any way to trigger the bug other than the one Brian reported, namely creating a functional index with a function that tries to scan its own table. Neither of you mentioned having done any such thing in your reports, but I wonder whether you'd ever had such an index on the tables that you saw problems with. regards, tom lane
pgsql@c0de.net (Phill Kenoyer) wrote in message news:<20011208014433.GA2913@c0de.net>...
> Here is a good one. I have three fields set for my primary key. Now I
> thought that a primary key was unique, and dups can not be inserted.
>
I don't think primary keys per se are unique, but rather can be made
unique by specifying that option. IIRC primary keys just allow you to
index searches within the database. So to make a unique primary key
from your db schema:
CREATE TABLE "inventory" (
"stock" character varying(50) NOT NULL,
"inventory_type" character varying(20) DEFAULT 'unknown' NOT
NULL,
"client_id" integer NOT NULL,
[...]
UNIQUE ("stock", "inventory_type", "client_id")
Constraint "inventory_pkey"
Primary Key ("stock", "inventory_type", "client_id")
);
HTH,
-Tony
BTW, I'm using Google newsgroups to view the Postgres messages, but
haven't seen posted messages in several days on the Hackers list. Does
anyone know if this is a Google glitch or is the hackers list just not
very active during the holiday?
Tony / Phil, my 2c on the Primary Key definition / usage. If you have any SQL / DDL code that disagrees with the definitions below could you please repost them. "Tony Reina" <reina@nsi.edu> wrote in message news:f40d3195.0112281441.223a73ce@posting.google.com... > pgsql@c0de.net (Phill Kenoyer) wrote in message news:<20011208014433.GA2913@c0de.net>... > > Here is a good one. I have three fields set for my primary key. Now I > > thought that a primary key was unique, and dups can not be inserted. > > > > I don't think primary keys per se are unique, but rather can be made > unique by specifying that option. IIRC primary keys just allow you to > index searches within the database. So to make a unique primary key > from your db schema: > The definition of a primary key was that it WAS a unique identifier for the table Definition: The primary key of a relational table uniquely identifies each record in the table. It can either be a normal attribute that is guaranteed to be unique (such as Social Security Number in a table with no more than one record per person) or it can be generated by the DBMS (such as a globally unique identifier, or GUID, in Microsoft SQL Server). Ref: http://databases.about.com/library/glossary/bldef-primarykey.htm Also PRIMARY KEY This column is a primary key, which implies that uniqueness is enforced by the system and that other tables may rely on this column as a unique identifier for rows. See PRIMARY KEY for more information. AND The PRIMARY KEY column constraint specifies that a column of a table may contain only unique (non-duplicate), non-NULL values. The definition of the specified column does not have to include an explicit NOT NULL constraint to be included in a PRIMARY KEY constraint. Ref: http://www.ninthwonder.com/info/postgres/user/sql-createtable.htm Regards sb > > CREATE TABLE "inventory" ( > "stock" character varying(50) NOT NULL, > "inventory_type" character varying(20) DEFAULT 'unknown' NOT > NULL, > "client_id" integer NOT NULL, > [...] > UNIQUE ("stock", "inventory_type", "client_id") > Constraint "inventory_pkey" > Primary Key ("stock", "inventory_type", "client_id") > ); > > HTH, > -Tony > > > BTW, I'm using Google newsgroups to view the Postgres messages, but > haven't seen posted messages in several days on the Hackers list. Does > anyone know if this is a Google glitch or is the hackers list just not > very active during the holiday?
I did have this problem in the past. I have a table with three fields
set for the primary key. Somehow I would get dups in that table. I'm
not sure how as I am too busy to test it. I changed my insert queries
to check for existing data before the insert so I could get back to
work.
Someone did reply that it was from a bug in vacuum. I run vacuum each
night on my database.
I'm running:
PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.95.4
from Debian packages
On:
Linux 2.4.17 #1 SMP Sat Dec 22 09:18:33 PST 2001 i686 unknown
Debian Sid (unstable) and Woody (testing).
Hope this helps.
|On 020114 13:01
|steve boyle (boylesa@dial.pipex.com) wrote the following...
|
|Tony / Phil,
|
|my 2c on the Primary Key definition / usage. If you have any SQL / DDL code
|that disagrees with the definitions below could you please repost them.
|
|"Tony Reina" <reina@nsi.edu> wrote in message
|news:f40d3195.0112281441.223a73ce@posting.google.com...
|> pgsql@c0de.net (Phill Kenoyer) wrote in message
|news:<20011208014433.GA2913@c0de.net>...
|> > Here is a good one. I have three fields set for my primary key. Now I
|> > thought that a primary key was unique, and dups can not be inserted.
|> >
|>
|> I don't think primary keys per se are unique, but rather can be made
|> unique by specifying that option. IIRC primary keys just allow you to
|> index searches within the database. So to make a unique primary key
|> from your db schema:
|>
|
|The definition of a primary key was that it WAS a unique identifier for the
|table
|
|Definition: The primary key of a relational table uniquely identifies each
|record in the table. It can either be a normal attribute that is guaranteed
|to be unique (such as Social Security Number in a table with no more than
|one record per person) or it can be generated by the DBMS (such as a
|globally unique identifier, or GUID, in Microsoft SQL Server).
|
|Ref: http://databases.about.com/library/glossary/bldef-primarykey.htm
|
|Also
|
|PRIMARY KEY
|
|This column is a primary key, which implies that uniqueness is enforced by
|the system and that other tables may rely on this column as a unique
|identifier for rows. See PRIMARY KEY for more information.
|
|AND
|
|The PRIMARY KEY column constraint specifies that a column of a table may
|contain only unique (non-duplicate), non-NULL values. The definition of the
|specified column does not have to include an explicit NOT NULL constraint to
|be included in a PRIMARY KEY constraint.
|
|Ref:
|
|http://www.ninthwonder.com/info/postgres/user/sql-createtable.htm
|
|Regards
|
|sb
|
|>
|> CREATE TABLE "inventory" (
|> "stock" character varying(50) NOT NULL,
|> "inventory_type" character varying(20) DEFAULT 'unknown' NOT
|> NULL,
|> "client_id" integer NOT NULL,
|> [...]
|> UNIQUE ("stock", "inventory_type", "client_id")
|> Constraint "inventory_pkey"
|> Primary Key ("stock", "inventory_type", "client_id")
|> );
|>
|> HTH,
|> -Tony
|>
|>
|> BTW, I'm using Google newsgroups to view the Postgres messages, but
|> haven't seen posted messages in several days on the Hackers list. Does
|> anyone know if this is a Google glitch or is the hackers list just not
|> very active during the holiday?
--
BOFH Reason of the day: Maintence window broken
_ | _
(_()(|('.|)('||.|()|`|(