PL/pgSQL Problem

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