Re: logging arguments to prepared statements?
От | rihad |
---|---|
Тема | Re: logging arguments to prepared statements? |
Дата | |
Msg-id | 4767FFDD.7060307@mail.ru обсуждение исходный текст |
Ответ на | Re: logging arguments to prepared statements? (Ted Byers <r.ted.byers@rogers.com>) |
Ответы |
Re: logging arguments to prepared statements?
("Merlin Moncure" <mmoncure@gmail.com>)
|
Список | pgsql-general |
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 > >
В списке pgsql-general по дате отправления: