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:
>=20
> Bug reference:      8127
> Logged by:          Matti Aarnio
> Email address:      matti.aarnio@methics.fi
> PostgreSQL version: 9.2.4
> Operating system:   Fedora 18
> Description:
>=20
> With table:
>=20
>   CREATE TABLE demo (
>      pkey INTEGER PRIMARY KEY,
>      key2 VARCHAR UNIQUE,
>      key3 VARCHAR UNIQUE
>   );
>=20
> An insert that fails secondary constraint key does return SQL State
> 23505,
> and maybe an explanation message telling that "Key (key3)=3D".. " is
> duplicate."
>=20
> 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.
>=20
> With PostgreSQL we get following error on those error analysis =
SELECTs:
>   ERROR: current transaction is aborted,
> commands ignored until end of transaction block
>=20
> 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.=20
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.