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

The following bug has been logged on the website:

Bug reference:      8127
Logged by:          Matti Aarnio
Email address:      matti.aarnio@methics.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)=3D".. " 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?

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.