I'm not observing intermediate SQL errors when using forPrepared_/traversePrepared_. Is it possible to?

[lehmacdj]

With a schema like so:

create table referenced_table (
  id text not null primary key
);

create table referencing_table (
  foreign_key text not null references referenced_table(id)
);

insert into referenced_table (id) values ('foo');

and then try to insert several items with forPrepared_:

forPrepared_ ["bar", "foo"] $
  insertInto #referencing_table
    (Values_ (Set (param @1) `as` #foreign_key))
    OnConflictDoRaise
    (Returning_ Nil)

in a transaction, catching the thrown exceptions with handleSqueal I only observe the second exception ERROR: current transaction is aborted, commands ignored until end of transaction block\n" instead of observing the first foreign key constraint violation exception.

Is it possible to still use prepared statements while either observing the first exception or observing all of the exceptions separately?

[echatav]

Hmmm, I think you might be limited by the exceptions that Postgres itself is throwing. However, you may try experimenting with using the lower level primitive prepare_ to generate a Prepared statement have more control over how you run it.

[lehmacdj]

I tried reproducing with postgresql-libpq but wasn't able to get the same error masking behavior; the error I get is the correct one (e.g. "... violates foreign key constraint ..."): https://gist.github.com/lehmacdj/25415101cae41d65c9720e1101e6b8ea

Unfortunately I wasn't able to try prepare_ because we're still on squeal-postgresql version 0.8.1.1. I did try separately preparing and running every set of parameters separately (passing a singleton list) and still had the same problem. I still had the same problem.

The best solution I've found for now is to run the queries completely separately (e.g. executeParams_).

[lehmacdj]

I think the problem is that the deallocate which is bracket-ed around execPrepared is also failing, because the enclosing transaction is aborted and this masks the original error. https://hackage.haskell.org/package/squeal-postgresql-0.8.1.1/docs/src/Squeal.PostgreSQL.Session.html#line-158

I think this might actually be fixed in 0.9.0, because you're no longer using bracket to ensure deallocate is called.

[echatav]

Good catch!