Re: BUG #8127: After failed insert a select to figure out what failed is rejected

On Monday, April 29, 2013 4:54 PM matti aarnio wrote:
> The following bug has been logged on the website:
>
> Bug reference: 8127
> Logged by: Matti Aarnio
> Email address: matti(dot)aarnio(at)methics(dot)fi
> PostgreSQL version: 9.2.4
> Operating system: Fedora 18
> Description:
>
> With table:
>
> CREATE TABLE demo (
> pkey INTEGER PRIMARY KEY,
> key2 VARCHAR UNIQUE,
> key3 VARCHAR UNIQUE
> );
>
> An insert that fails secondary constraint key does return SQL State
> 23505,
> and maybe an explanation message telling that "Key (key3)=".. " is
> duplicate."
>
> With Oracle we ask a SELECT after such an error on that table for all
> possibly existing secondary keys values, and get them to report
> detailed
> conflict information.
>
> With PostgreSQL we get following error on those error analysis SELECTs:
> ERROR: current transaction is aborted,
> commands ignored until end of transaction block
>
> Could PostgreSQL be similarly permissive (with respect of Oracle)
> allowing
> SELECTs within same transaction context that was already rejected?

As per current implementation PostgreSQL behavior is different from Oracle in the scenario mentioned by you.
However you can try by using Savepoint before failing statement and then after failure do Rollback To Savepoint_name and then call your select statement.
This will make you select statement run in top transaction context.

With Regards,
Amit Kapila.