Is there a logical reason for 25P02?

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

2010/10/5 Konstantin Izmailov <pgfizm(at)gmail(dot)com>:
> 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)?

you probably want pgloader : http://pgloader.projects.postgresql.org/

--
Cédric Villemain               2ndQuadrant
http://2ndQuadrant.fr/     PostgreSQL : Expertise, Formation et Support

On 10/05/2010 12:39 PM, Konstantin Izmailov wrote:
> 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".

Specifically, I suspect the message should be:

ERROR: current transaction is aborted, commands ignored until end of
transaction block

If that's what you're getting, the problem was with an earlier command
that returned an error you didn't notice, not with the command you just
ran. I'm unsure if this could cause PQexecPrepared to return sqlstate
25P02 if PQprepare fails, but would want to investigate the possibility.

--
Craig Ringer

Craig,
I think that failure of a previous command is the most possible reason. It
is an unexpected discovery since I keep track of statements that were
already prepared on the connection (to avoid preparing a statement twice).
However, the code might be flowed somehow. Anyway, I decided to use PQexec
for the time being.

Cédric, I'm basically using a code similar to one in pgloader. I'm trying to
find a way to efficiently handle an integrity violation error when inserting
large amount of data. Graceful handling means that a transaction should not
be aborted when the code tries to insert a duplicate row. Is using of a
SAVEPOINT only solution?

Thank you
Konstantin

On Tue, Oct 5, 2010 at 4:13 AM, Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>wrote:

> On 10/05/2010 12:39 PM, Konstantin Izmailov wrote:
>
>> 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".
>>
>
> Specifically, I suspect the message should be:
>
> ERROR: current transaction is aborted, commands ignored until end of
> transaction block
>
> If that's what you're getting, the problem was with an earlier command that
> returned an error you didn't notice, not with the command you just ran. I'm
> unsure if this could cause PQexecPrepared to return sqlstate 25P02 if
> PQprepare fails, but would want to investigate the possibility.
>
> --
> Craig Ringer
>