Обсуждение: Problems inserting data into a table with a sequence

Hi,

we have a table whose definition looks like this (using postgres 8.2.5
on Linux):

CREATE TABLE pn_categories_category (
    cat_id integer NOT NULL,
    cat_parent_id integer DEFAULT 1 NOT NULL,
    cat_is_locked smallint DEFAULT 0 NOT NULL,
    cat_is_leaf smallint DEFAULT 0 NOT NULL,
    cat_name character varying(255) DEFAULT ''::character varying NOT NULL,
    cat_sort_value integer DEFAULT 0 NOT NULL,
    cat_display_name text,
    cat_display_desc text,
    cat_path text,
    cat_ipath character varying(255) DEFAULT ''::character varying NOT NULL,
    cat_status character varying(1) DEFAULT 'A'::character varying NOT NULL,
    cat_obj_status character varying(1) DEFAULT 'A'::character varying NOT NULL,
    cat_cr_date timestamp without time zone DEFAULT '1970-01-01
00:00:00'::timestamp without time zone NOT NULL,
    cat_cr_uid integer DEFAULT 0 NOT NULL,
    cat_lu_date timestamp without time zone DEFAULT '1970-01-01
00:00:00'::timestamp without time zone NOT NULL,
    cat_lu_uid integer DEFAULT 0 NOT NULL
);

ALTER TABLE public.pn_categories_category OWNER TO postgres;
CREATE SEQUENCE pn_categories_category_cat_id_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;
ALTER TABLE public.pn_categories_category_cat_id_seq OWNER TO postgres;
ALTER SEQUENCE pn_categories_category_cat_id_seq OWNED BY
pn_categories_category.cat_id;
ALTER TABLE pn_categories_category ALTER COLUMN cat_id SET DEFAULT
nextval('pn_categories_category_cat_id_seq'::regclass);


Trying to insert data into this table using the following SQL
INSERT INTO pn_categories_category
(cat_id,cat_parent_id,cat_name,cat_display_name,cat_display_desc,cat_cr_date,cat_cr_uid,cat_lu_date,cat_lu_uid)VALUES
(DEFAULT,'2','FAQ','a:1:{s:3:\"eng\";s:3:\"FAQ\";}','a:1:{s:3:\"eng\";s:26:\"Frequently
Asked Questions\";}','2007-10-15 23:47:59',0,'2007-10-15 23:47:59',0)

gives us the following error:
 ERROR:  duplicate key violates unique constraint "pn_categories_category_pkey"

From reading the docs, it would seem that using the 'DEFAULT' keyword
is correct when used this way. Even more puzzling, we have other
tables which are also created with an integer ID field mapped to a
sequence value where the same construct works just fine.

Can someone offer a tip as to what we're doing wrong here?

Greetings/Thanks
R

RNG <rgasch@gmail.com> writes:
> Trying to insert data into this table using the following SQL
> ...
> gives us the following error:
>  ERROR:  duplicate key violates unique constraint "pn_categories_category_pkey"

Usually the reason for this is that you inserted some rows with manually
assigned serial numbers (perhaps a COPY from an old version of the
table?) and forgot to advance the sequence past those numbers.  Try
something like

SELECT setval('pn_categories_category_cat_id_seq',
              (SELECT MAX(cat_id) + 1 FROM pn_categories_category));


            regards, tom lane

On Mon, 15 Oct 2007, RNG wrote:

> Trying to insert data into this table using the following SQL
> INSERT INTO pn_categories_category
> (cat_id,cat_parent_id,cat_name,cat_display_name,cat_display_desc,cat_cr_date,cat_cr_uid,cat_lu_date,cat_lu_uid)VALUES
> (DEFAULT,'2','FAQ','a:1:{s:3:\"eng\";s:3:\"FAQ\";}','a:1:{s:3:\"eng\";s:26:\"Frequently
> Asked Questions\";}','2007-10-15 23:47:59',0,'2007-10-15 23:47:59',0)
>
> gives us the following error:
> ERROR:  duplicate key violates unique constraint "pn_categories_category_pkey"

Hey, this bug looks familiar; wait, that's because I reported it.  Sorry I
haven't been keeping with the PostNuke NOC activity, been out of town
since this topic became active again.  You can reach me off-list to follow
up, but since you've asked here I'll answer publicly to satisfy everyone's
curiousity.

The problem is that you're inserting a starter set of categories right
after the pn_categories_category table is created that aren't using the
sequence; here's some samples of what I'm seeing in the logs (edit your
postgresql.conf file and change "log_statement = 'all'" if you want this
detail):

INSERT INTO pn_categories_category

(cat_id,cat_parent_id,cat_is_locked,cat_is_leaf,cat_name,cat_display_name,cat_display_desc,cat_path,cat_ipath,cat_status,cat_cr_date,cat_cr_uid,cat_lu_date,cat_lu_uid)VALUES

(1,0,1,0,'__SYSTEM__','b:0;','b:0;','/__SYSTEM__','/1','A','2007-10-15
23:57:00',0,'2007-10-15 23:57:00',0)

INSERT INTO pn_categories_category

(cat_id,cat_parent_id,cat_is_locked,cat_is_leaf,cat_name,cat_display_name,cat_display_desc,cat_path,cat_ipath,cat_status,cat_cr_date,cat_cr_uid,cat_lu_date,cat_lu_uid)VALUES


(2,1,0,0,'Modules','a:1:{s:3:\"eng\";s:7:\"Modules\";}','a:1:{s:3:\"eng\";s:0:\"\";}','/__SYSTEM__/Modules','/1/2','A','2007-10-15

23:57:00',0,'2007-10-15 23:57:00',0)

There are more; there are manually assigned category IDs from 1 to 37 (the
final one is '37,30,0,0,'Sports',...').  Then you manually insert category
9999:

INSERT INTO pn_categories_category
(cat_id,cat_cr_date,cat_cr_uid,cat_lu_date,cat_lu_uid)VALUES
(9999,'2007-10-15 23:57:00',0,'2007-10-15 23:57:00',0)

which will keep the workaround Tom already suggested from working quite
the way I think you want it to.  The duplicate key errors start showing up
later, after the pn_categories_registry table is created.  At that point
more records start getting inserted using the sequence:

INSERT INTO pn_categories_category
(cat_id,cat_parent_id,cat_name,cat_display_name,cat_display_desc,cat_cr_date,cat_cr_uid,cat_lu_date,cat_lu_uid)VALUES
(DEFAULT,'2','FAQ','a:1:{s:3:\"eng\";s:3:\"FAQ\";}','a:1:{s:3:\"eng\";s:26:\"Frequently
Asked Questions\";}','2007-10-15 23:57:04',0,'2007-10-15 23:57:04',0)

But the sequence number wasn't incremented by any of the earlier
insertions, so it's still at a low value.  Here's what I got after going
through the whole install process:

postnuke=# select nextval('pn_categories_category_cat_id_seq');
  nextval
---------
        6

So there are values from 1 to 37 (and 9999) in the table already, but the
sequence number is still set to 1 when you're reaching the first insert
using it (there are 5 of these duplicate key errors which is why the
sequence is up to 6 by the time the install script is done).  The actual
statement spitting out the duplicate key error isn't the problem; that one
has the right syntax.  The problem here is whatever is doing that initial
population of 1-37&9999 in the table without using the sequence number.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD