Обсуждение: Tracking SQLs that update data

Hi

I'm having some issues with a code base where several different
programs are updating one "status" column in the DB with their code.
Mostly this is working, but in some cases the status column in a DB is
getting updated when it shouldn't have been, and we're trying to
locate which program did it.

While checking through all the code, I'm also wondering if it is
possible to somehow maintain a log of which SQL did the updating?

My question: how can I set up a "RULE" so that when a specific column
is updated, a separate table also logs which update SQL was issued?

Thanks for any ideas.

On Mon, Jun 14, 2010 at 5:55 PM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote:
> Hi
>
> I'm having some issues with a code base where several different
> programs are updating one "status" column in the DB with their code.
> Mostly this is working, but in some cases the status column in a DB is
> getting updated when it shouldn't have been, and we're trying to
> locate which program did it.
>
> While checking through all the code, I'm also wondering if it is
> possible to somehow maintain a log of which SQL did the updating?
>
> My question: how can I set up a "RULE" so that when a specific column
> is updated, a separate table also logs which update SQL was issued?

It's far easier to adjust the logging.

psql mydb
alter mydb set log_statement='mod';

On 06/14/2010 06:55 PM, Phoenix Kiula wrote:
> Hi
>
> I'm having some issues with a code base where several different
> programs are updating one "status" column in the DB with their code.
> Mostly this is working, but in some cases the status column in a DB is
> getting updated when it shouldn't have been, and we're trying to
> locate which program did it.
>
> While checking through all the code, I'm also wondering if it is
> possible to somehow maintain a log of which SQL did the updating?
>
> My question: how can I set up a "RULE" so that when a specific column
> is updated, a separate table also logs which update SQL was issued?
>
> Thanks for any ideas.
>

If you are on 8.5, this will be helpful:

http://www.depesz.com/index.php/2009/12/29/waiting-for-8-5-application-name-reporting/

Scott Marlowe <scott.marlowe@gmail.com> writes:
> On Mon, Jun 14, 2010 at 5:55 PM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote:
>> My question: how can I set up a "RULE" so that when a specific column
>> is updated, a separate table also logs which update SQL was issued?

> It's far easier to adjust the logging.

Or, if you're worried about actions from functions, use a trigger to do
the logging.  There are approximately no cases where a rule is really
better than a trigger :-(

            regards, tom lane

> It's far easier to adjust the logging.
>
> psql mydb
> alter mydb set log_statement='mod';



Thanks Scott.

Two questions:

1. Will this log_statement='mod' be a permanent thing, or when I have
done my testing and identifying the problem SQL, I can set it back to
default so not too much logging is being done?

2. After this statement, how or where do I find the culprit SQL?

Thanks

On Sat, Jun 19, 2010 at 5:28 AM, Phoenix Kiula <phoenix.kiula@gmail.com> wrote:
>> It's far easier to adjust the logging.
>>
>> psql mydb
>> alter mydb set log_statement='mod';
>
>
>
> Thanks Scott.
>
> Two questions:
>
> 1. Will this log_statement='mod' be a permanent thing, or when I have
> done my testing and identifying the problem SQL, I can set it back to
> default so not too much logging is being done?

Just alter it back to 'none' when you're done.  Note you can also set
it per user, so if it's a certain user you want to log you can only
log their statements.

> 2. After this statement, how or where do I find the culprit SQL?

They should be in the postgresql logs.