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
