Re: Problems inserting data into a table with a sequence - Mailing list pgsql-general

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