Обсуждение: 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@gmail.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