Thread: logging arguments to prepared statements?
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 way to tweak postgres to include the values too, without setting log_statements=all? changed log settings: log_destination = 'syslog' log_error_verbosity = verbose log_min_error_statement = notice log_checkpoints = on log_connections = on log_disconnections = on log_lock_waits = on log_statements = 'none' log_temp_files = 0 log_autovacuum_min_duration = 250 other log_* settings kept as default (commented). PostgreSQL 8.3-beta2 (FreeBSD port is lagging behind a bit). Thanks.
--- rihad <rihad@mail.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
Ted Byers wrote: > --- rihad <rihad@mail.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 > >
On Dec 18, 2007 12:14 PM, rihad <rihad@mail.ru> wrote: > Ted Byers wrote: > > --- rihad <rihad@mail.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. which client api are you using? you can wrap the execution on the client and log there. merlin