Thread: Antw: Re: Problems with unique restrictions
thanks for responding. Meanwhile I found out that ist was my own fault. A newly installed insert trigger fired
unexpectedlyand caused the error. Now I'm redesigning my functions to make them smaller so that errors can be found
easier.Sometimes I wish there was something like a debugger for PL/PGSQL with breakpoints, single step, variable
watching...
Anyway, PostgreSQL is the best backend I ever have worked with.
Regards
--Marcel
>>> Tom Lane <tgl@sss.pgh.pa.us> 13.01.2007 >>>
"Marcel Gsteiger" <Marcel.Gsteiger@milprog.ch> writes:
> Now since I upgraded to 8.2 I have problems inserting data into tables that have unique indexes. Ugly enough, I get
themessage 'duplicate key violates unique constraint' when inserting the very first record into a table. This happens
everytimewhen the new tuple references another tuple that has been inserted just before this one in the same
transaction.
> Putting a "SET CONSTRAINTS ALL DEFERRED" in my procedure does not help.
> To me it looks that something with referential integrity checking goes wrong, but in this case the error message
wouldbe misleading.
RI would not have anything to do with a duplicate-key error.
Do you have any SERIAL-type columns in these tables? My first thought
is of a sequence that hasn't been updated to be above the existing ID
values. It's fairly easy to get into such a state if you do anything
but a plain vanilla dump-all-and-reload-all update process ...
regards, tom lane
You can give EnterpriseDB PL Debugger a try, details for its usage can be found at --> http://www.enterprisedb.com/documentation/debugger.html
--------------
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)
--------------
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)
On 1/14/07, Marcel Gsteiger < Marcel.Gsteiger@milprog.ch> wrote:
thanks for responding. Meanwhile I found out that ist was my own fault. A newly installed insert trigger fired unexpectedly and caused the error. Now I'm redesigning my functions to make them smaller so that errors can be found easier. Sometimes I wish there was something like a debugger for PL/PGSQL with breakpoints, single step, variable watching...
Anyway, PostgreSQL is the best backend I ever have worked with.
Regards
--Marcel
>>> Tom Lane <tgl@sss.pgh.pa.us> 13.01.2007 >>>
"Marcel Gsteiger" < Marcel.Gsteiger@milprog.ch> writes:
> Now since I upgraded to 8.2 I have problems inserting data into tables that have unique indexes. Ugly enough, I get the message 'duplicate key violates unique constraint' when inserting the very first record into a table. This happens everytime when the new tuple references another tuple that has been inserted just before this one in the same transaction.
> Putting a "SET CONSTRAINTS ALL DEFERRED" in my procedure does not help.
> To me it looks that something with referential integrity checking goes wrong, but in this case the error message would be misleading.
RI would not have anything to do with a duplicate-key error.
Do you have any SERIAL-type columns in these tables? My first thought
is of a sequence that hasn't been updated to be above the existing ID
values. It's fairly easy to get into such a state if you do anything
but a plain vanilla dump-all-and-reload-all update process ...
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
"Marcel Gsteiger" <Marcel.Gsteiger@milprog.ch> writes: > Sometimes I wish there was something like a debugger for PL/PGSQL with > breakpoints, single step, variable watching... Take a look at http://pgfoundry.org/projects/edb-debugger/ ... it's beta but without users it won't get better. The front page says the plpgsql hooks needed will be in 8.3 but I think that's a mistake; they got into 8.2 didn't they? Korry? regards, tom lane
"Marcel Gsteiger" <Marcel.Gsteiger@milprog.ch> writes: > Sometimes I wish there was something like a debugger for PL/PGSQL with > breakpoints, single step, variable watching... Take a look at http://pgfoundry.org/projects/edb-debugger/ ... it's beta but without users it won't get better. The front page says the plpgsql hooks needed will be in 8.3 but I think that's a mistake; they got into 8.2 didn't they? Korry?
Yes - the hooks for the debugger are in 8.2. That means that we can publish the PL/pgSQL debugger at any time (well, as soon as I have the code all cleaned up - still removing some obsolete remnants of the pre-plugin architecture).
-- Korry
-- Korry Douglas korryd@enterprisedb.com EnterpriseDB http://www.enterprisedb.com |