Re: logging arguments to prepared statements?

Ted Byers wrote:
> --- 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

I was wondering if there was a way to see the _values_ themselves in
case of errors, as is possible with log_statements=all, without turning
it on. Apparently there isn't. Thanks anyway.

> 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
>
>