Обсуждение: PL/pgSQL Problem
Hi, I'm having a problem with one of my functions, where I delete all rows containing a particular date and then re-insert a row with that same date. When I try this I get a constraint error. This just started recently, after upgrading our database from 7.4 to 8.1.4 (now on RH EL). here's the function: CREATE OR REPLACE FUNCTION updatesystemCounts() RETURNS void AS ' DECLARE compDate DATE; currCount INT; BEGIN compDate := current_date::date; LOOP DELETE FROM dm.systemCounts WHERE updateDate::date = compDate::date; INSERT INTO dm.systemCounts (updateDate) VALUES (compDate::date); ............. and here's the error: ERROR: duplicate key violates unique constraint "systemcounts_pkey" CONTEXT: SQL statement "INSERT INTO dm.systemCounts (updateDate) VALUES ( $1 ::date)" PL/pgSQL function "updatesystemcounts" line 8 at SQL statement The only explanation I can think of is that maybe the newer version of postgres needs a COMMIT inside the function. Any ideas? Thanks Ron St.Pierre
On Wed, Aug 09, 2006 at 10:33:02AM -0700, Ron St-Pierre wrote: > ERROR: duplicate key violates unique constraint "systemcounts_pkey" > CONTEXT: SQL statement "INSERT INTO dm.systemCounts (updateDate) VALUES ( > $1 ::date)" > PL/pgSQL function "updatesystemcounts" line 8 at SQL statement Is the table's primary key a serial column? If so then the sequence might be out of sync with the values in the table, in which case you'll need to adjust the sequence's value with ALTER SEQUENCE or setval(). -- Michael Fuhr
On 8/9/06, Ron St-Pierre <ron.pgsql@shaw.ca> wrote: > Hi, I'm having a problem with one of my functions, where I delete all > rows containing a particular date and then re-insert a row with that > same date. When I try this I get a constraint error. This just started > recently, after upgrading our database from 7.4 to 8.1.4 (now on RH EL). > > here's the function: > CREATE OR REPLACE FUNCTION updatesystemCounts() RETURNS void AS ' > DECLARE > compDate DATE; > currCount INT; > BEGIN > compDate := current_date::date; > LOOP > DELETE FROM dm.systemCounts WHERE updateDate::date = > compDate::date; > INSERT INTO dm.systemCounts (updateDate) VALUES > (compDate::date); > ............. > > and here's the error: > ERROR: duplicate key violates unique constraint "systemcounts_pkey" > CONTEXT: SQL statement "INSERT INTO dm.systemCounts (updateDate) VALUES ( $1 ::date)" > PL/pgSQL function "updatesystemcounts" line 8 at SQL statement > > The only explanation I can think of is that maybe the newer version of > postgres needs a COMMIT inside the function. commit inside function is impossible (by definition), however you can do subtransaction inside the function and catch the error. what is the primary key for dm.systemCounts. does it have a default? merlin
Michael Fuhr wrote: > On Wed, Aug 09, 2006 at 10:33:02AM -0700, Ron St-Pierre wrote: > >> ERROR: duplicate key violates unique constraint "systemcounts_pkey" >> CONTEXT: SQL statement "INSERT INTO dm.systemCounts (updateDate) VALUES ( >> $1 ::date)" >> PL/pgSQL function "updatesystemcounts" line 8 at SQL statement >> > > Is the table's primary key a serial column? If so then the sequence > might be out of sync with the values in the table, in which case > you'll need to adjust the sequence's value with ALTER SEQUENCE or > setval(). > > Aha, that was exactly what the problem was. Thanks! Ron