[GENERAL] 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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PL/pgSQL Problem
On 8/9/06, Ron St-Pierre <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PL/pgSQL Problem
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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] PL/pgsql problem in 6.5
Hi everybody, I have just installed PostgreSQL 6.5 and encountered problems with PL/pgsql: On an insert into a table which has a before-insert trigger I get the following error message: ERROR: Load of file /usr/local/pgsql/lib/plpgsql.so failed: /usr/local/pgsql/lib/plpgsql.so: undefined symbol: SPI_push What's wrong with my installation? The trigger looks like this: DECLARE contentCONSTANT VARCHAR (1) DEFAULT 'N'; new_series art.series%TYPE; new_rating art.rating%TYPE; anzahl INT4; max_indx art.indx%TYPE; BEGIN new_series := NEW.series; new_rating := NEW.rating; SELECT COUNT (*) INTO anzahl FROM series WHERE ke_content = content AND series = new_series; IF anzahl = 0 THEN SELECT NVL (MAX (indx), 0) + 1 INTO max_indx FROM series; INSERT INTO series (ke_content, series, indx, rating) VALUES (content, new_series, max_indx, new_rating); END IF; RETURN NEW; END; -- Oliver Graf Oberhofstr. 11 D-44575 Castrop-Rauxel, Germany Phone: +49 2305 14230 http://www.ruhr.de/home/casbah/