Re: logging arguments to prepared statements?

--- rihad <rihad(at)mail(dot)ru> wrote:
> Dec 18 15:49:41 myhost postgres[29832]: [35-1]
> ERROR: 23505: duplicate
> key value violates unique constraint "foo_key"
> Dec 18 15:49:41 myhost postgres[29832]: [35-4]
> INSERT INTO foo
> Dec 18 15:49:41 myhost postgres[29832]: [35-5]
> (a,b,c)
> Dec 18 15:49:41 myhost postgres[29832]: [35-7]
> VALUES ($1,$2,$3)
> Dec 18 15:49:41 myhost postgres[29832]: [35-8]
>
> And that's it, leaving me wondering which value
> triggered the error. Any
Why? It seems simple enough. You have a table called
foo, with at least three columns: a, b, and c. And
you have a violation of your unique constraint. If it
isn't that simple, you have left out useful
information. You did not say, for example, which of
your columns, if any, are involved in your unique
constraint. If the answer to that is none, then you
need to show how the constraint is defined.

Which of the three columns are involved in a unique
constraint? If none of the columns you use are
involved in a unique constraint, there must be other
columns that are, and that would imply that there is
either a problem with your prepared statement,
ignoring certain columns that can't be ignored, or a
problem with how you set up the default values for
another column that is involved in a unique
constraint; or the table has grown so big that it is
impossible to add a new record without violating the
existing unique constraint (unlikely as that is in
most cases, especially during development).

I could see creating a before insert trigger that
stores the values to be inserted in a log table with a
timestamp, but I don't see the profit in that. Doesn't
such an error generate a SQL exception to your client?
If so, the client code will know immediately what
insert attempt failed, and therefore what values are
involved in the problem. Using JDBC, for example, all
of the JDBC functions that execute a prepared
statement (or any other SQL) will throw a
java.sql.SQLException. One therefore knows
immediately when there is a problem of the sort you
describe, and so you can determine quickly what the
values were that resulting in your error. If need be,
that could be stored in your application's log. If
one needed full audit functionality, one could create
the tables to store the details of every SQL
statement, including who is responsible for the
statement and a timestamp. But if you don't need to
support that kind of detailed audit, why bother when
there are easier ways to address your issue?

HTH

Ted