Postgres Pro
Downloads
Home   >   mailing lists

Thread: PL/pgSQL Problem

PL/pgSQL Problem

From
Ron St-Pierre
Date:
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

Re: PL/pgSQL Problem

From
Michael Fuhr
Date:
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

Re: PL/pgSQL Problem

From
"Merlin Moncure"
Date:
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

Re: PL/pgSQL Problem

From
Ron St-Pierre
Date:
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