Is there a logical reason for 25P02?

From: Konstantin Izmailov <pgfizm(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Is there a logical reason for 25P02?
Date: 2010-10-05 04:39:03
Message-ID: AANLkTimz034qWj1oMZq39jJmvqhhJoMemg7LLNkXM3ro@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Howdy,
I've noticed that there is a difference in result of execution of the
following statement:
INSERT INTO testtable(col1) VALUES(NULL);
depending on whether the command is prepared or not.

If I call PQprepare/PQexecPrepared for the statement, the error "transaction
aborted" is returned with SQL State = "25P02".

However, if I call just PQexec for the same query, it runs successfully. I'm
using Postgres 8.4. Is this a defect? Or is there a logical reason for the
behavior?

Let me ask another question related to the INSERT as well. I'm running
periodic import into Postgres and sometimes the application tries to insert
a duplicate row which, as expected, results in "integrity violation" error.
This aborts entire transaction (which is huge). I can use "SAVEPOINT
...;INSERT ...;RELEASE SAVEPOINT ..." but I'm concerned with potential
performance hit. I haven't had time to benchmark the difference in
performance, so could you please suggest if the difference will be
noticeable. Is there a better approach? Is it possible to customize INSERT
behavior to not abort transaction due to an integrity violation? Would it be
possible to alter COPY command behavior as well (to gracefully continue
after integrity violation)?

Thank you!
Konstantin

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Carlo Stonebanks 2010-10-05 04:47:50 Re: PL/TCL Unkown module
Previous Message Fujii Masao 2010-10-05 04:09:56 Re: streaming replication question