Horton

Thread

  1. capturing and storing query statement with rules

    Andrew Gould <andrewgould@yahoo.com> — 2003-06-24T20:17:39Z

    I would like to create a audit log of update and
delete queries against a table.  I want the log on the
server side, since access will be done using various
clients via ODBC.  I would like to capture the user,
current datetime and the query statement.  My thought
was to accomplish this by creating a rule that will
insert the information into a log table.

How can I captures the query statement so that I can
place it in a insert query?  Is there a variable in
the server that holds the query statement as a string?

Am I about to go in over my head (again)?

Thanks,

Andrew

  2. Re: capturing and storing query statement with rules

    Joe Conway <mail@joeconway.com> — 2003-06-24T20:53:20Z

    Guillaume LELARGE wrote:
> Le Mardi 24 Juin 2003 20:17, Andrew Gould a écrit :
>>I would like to create a audit log of update and
>>delete queries against a table.  I want the log on the
>>server side, since access will be done using various
>>clients via ODBC.  I would like to capture the user,
>>current datetime and the query statement.  My thought
>>was to accomplish this by creating a rule that will
>>insert the information into a log table.
> 
> To be server side, you need to use a trigger and a plpgsql function. On this 
> page you will find more informations on trigger related functions:
> http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=plpgsql-trigger.html
> Perhaps you can compare OLD and NEW rows to know which rows will be updated.
> And you'll find at the end of the page how to get current user (current_user 
> keyword) and current datetime (function now).
> 
>>How can I captures the query statement so that I can
>>place it in a insert query?  Is there a variable in
>>the server that holds the query statement as a string?

I think this shows how to do what you want:
http://archives.postgresql.org/pgsql-sql/2003-05/msg00301.php

HTH,

Joe

  3. Re: capturing and storing query statement with rules

    Mike Mascari <mascarm@mascari.com> — 2003-06-24T21:05:25Z

    Joe Conway wrote:

> Guillaume LELARGE wrote:
> 
>> Le Mardi 24 Juin 2003 20:17, Andrew Gould a écrit :
>>
>>> I would like to create a audit log of update and
>>> delete queries against a table.  I want the log on the
>>> server side, since access will be done using various
>>> clients via ODBC.  I would like to capture the user,
>>> current datetime and the query statement.  My thought
>>> was to accomplish this by creating a rule that will
>>> insert the information into a log table.
>>
>> And you'll find at the end of the page how to get current user
>> (current_user keyword) and current datetime (function now).
>>
>>> How can I captures the query statement so that I can
>>> place it in a insert query?  Is there a variable in
>>> the server that holds the query statement as a string?
> 
> I think this shows how to do what you want:
> http://archives.postgresql.org/pgsql-sql/2003-05/msg00301.php

Maybe debug_query_string should be mapped into a variable like
CURRENT_USER? Perhaps something like CURRENT_QUERY?

Mike Mascari
mascarm@mascari.com

  4. Re: [GENERAL] capturing and storing query statement with rules

    Joe Conway <mail@joeconway.com> — 2003-06-24T21:07:23Z

    (moving to HACKERS)

Mike Mascari wrote:
> Joe Conway wrote:
>>I think this shows how to do what you want:
>>http://archives.postgresql.org/pgsql-sql/2003-05/msg00301.php
> 
> Maybe debug_query_string should be mapped into a variable like
> CURRENT_USER? Perhaps something like CURRENT_QUERY?
> 

I was thinking something similar. This exact question has come up at 
least three times in the last three months. I doubt we'd want a special 
keyword like CURRENT_QUERY, but maybe current_query()?

Comments?

Joe

  5. Re: [GENERAL] capturing and storing query statement with

    Larry Rosenman <ler@lerctr.org> — 2003-06-24T21:13:31Z

    
--On Tuesday, June 24, 2003 14:07:23 -0700 Joe Conway <mail@joeconway.com> 
wrote:

> (moving to HACKERS)
>
> Mike Mascari wrote:
>> Joe Conway wrote:
>>> I think this shows how to do what you want:
>>> http://archives.postgresql.org/pgsql-sql/2003-05/msg00301.php
>>
>> Maybe debug_query_string should be mapped into a variable like
>> CURRENT_USER? Perhaps something like CURRENT_QUERY?
>>
>
> I was thinking something similar. This exact question has come up at
> least three times in the last three months. I doubt we'd want a special
> keyword like CURRENT_QUERY, but maybe current_query()?
>
> Comments?
I was thinking the same thing.  Currently I use the contrib/dblink supplied
function, but a firstclass, in the default install, version would be nice.



-- 
Larry Rosenman                     http://www.lerctr.org/~ler
Phone: +1 972-414-9812                 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

  6. Re: capturing and storing query statement with rules

    Mike Benoit <mikeb@netnation.com> — 2003-06-24T21:27:29Z

    http://gborg.postgresql.org/project/tablelog/download/download.php

or

http://gborg.postgresql.org/project/audittrail/download/download.php?branch=devel

I haven't used either, but table_log seems to be one that I may start
using in the near future.


On Tue, 2003-06-24 at 13:17, Andrew Gould wrote:
> I would like to create a audit log of update and
> delete queries against a table.  I want the log on the
> server side, since access will be done using various
> clients via ODBC.  I would like to capture the user,
> current datetime and the query statement.  My thought
> was to accomplish this by creating a rule that will
> insert the information into a log table.
> 
> How can I captures the query statement so that I can
> place it in a insert query?  Is there a variable in
> the server that holds the query statement as a string?
> 
> Am I about to go in over my head (again)?
> 
> Thanks,
> 
> Andrew
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
-- 
Best Regards,
 
Mike Benoit
NetNation Communications Inc.
Systems Engineer
Tel: 604-684-6892 or 888-983-6600
 ---------------------------------------
 
 Disclaimer: Opinions expressed here are my own and not 
 necessarily those of my employer

  7. Re: capturing and storing query statement with rules

    Guillaume LELARGE <gleu@wanadoo.fr> — 2003-06-24T22:40:28Z

    Le Mardi 24 Juin 2003 20:17, Andrew Gould a écrit :
> I would like to create a audit log of update and
> delete queries against a table.  I want the log on the
> server side, since access will be done using various
> clients via ODBC.  I would like to capture the user,
> current datetime and the query statement.  My thought
> was to accomplish this by creating a rule that will
> insert the information into a log table.
>
To be server side, you need to use a trigger and a plpgsql function. On this 
page you will find more informations on trigger related functions:
http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=plpgsql-trigger.html
Perhaps you can compare OLD and NEW rows to know which rows will be updated.
And you'll find at the end of the page how to get current user (current_user 
keyword) and current datetime (function now).

> How can I captures the query statement so that I can
> place it in a insert query?  Is there a variable in
> the server that holds the query statement as a string?
>
I don't think... or wasn't able to find something like this one in the 
documentation.

Regards.


-- 
Guillaume <!-- http://absfr.tuxfamily.org/ -->.

  8. Re: [GENERAL] capturing and storing query statement with rules

    Tom Lane <tgl@sss.pgh.pa.us> — 2003-06-25T14:40:53Z

    Joe Conway <mail@joeconway.com> writes:
> I was thinking something similar. This exact question has come up at 
> least three times in the last three months. I doubt we'd want a special 
> keyword like CURRENT_QUERY, but maybe current_query()?

Not unless you want to promote a quick debugging hack, not expected or
required to work 100%, into a supported feature.  I don't think
debug_query_string can be relied on to always reflect what the system
is doing, particularly not in the 3.0 protocol extended-query case.
And how about when you're executing queries inside a function --- is it
supposed to tell you about the most closely nested SQL query?

I don't say this is not worth doing --- but I do say you are opening a
larger can of worms than you probably think.

			regards, tom lane

  9. Re: [GENERAL] capturing and storing query statement with

    Joe Conway <mail@joeconway.com> — 2003-06-25T14:58:21Z

    Tom Lane wrote:
> Joe Conway <mail@joeconway.com> writes:
> 
>>I was thinking something similar. This exact question has come up at 
>>least three times in the last three months. I doubt we'd want a special 
>>keyword like CURRENT_QUERY, but maybe current_query()?
> 
> Not unless you want to promote a quick debugging hack, not expected or
> required to work 100%, into a supported feature.  I don't think
> debug_query_string can be relied on to always reflect what the system
> is doing, particularly not in the 3.0 protocol extended-query case.
> And how about when you're executing queries inside a function --- is it
> supposed to tell you about the most closely nested SQL query?
> 
> I don't say this is not worth doing --- but I do say you are opening a
> larger can of worms than you probably think.
> 

Hmmm. Good points. This one may best wait for 7.5 at least. Does it make 
sense to turn it into a TODO?

  * promote debug_query_string into a documented, supported feature

Anyone who *does* use the function from dblink, please be sure to report 
circumstances where dblink_current_query() returns something other than 
what you would expect.

Thanks,

Joe

  10. Re: [GENERAL] capturing and storing query statement with

    Bruce Momjian <pgman@candle.pha.pa.us> — 2003-06-25T21:40:47Z

    Added to TODO:

	* Promote debug_query_string into a server-side function
	  current_query()


---------------------------------------------------------------------------

Joe Conway wrote:
> Tom Lane wrote:
> > Joe Conway <mail@joeconway.com> writes:
> > 
> >>I was thinking something similar. This exact question has come up at 
> >>least three times in the last three months. I doubt we'd want a special 
> >>keyword like CURRENT_QUERY, but maybe current_query()?
> > 
> > Not unless you want to promote a quick debugging hack, not expected or
> > required to work 100%, into a supported feature.  I don't think
> > debug_query_string can be relied on to always reflect what the system
> > is doing, particularly not in the 3.0 protocol extended-query case.
> > And how about when you're executing queries inside a function --- is it
> > supposed to tell you about the most closely nested SQL query?
> > 
> > I don't say this is not worth doing --- but I do say you are opening a
> > larger can of worms than you probably think.
> > 
> 
> Hmmm. Good points. This one may best wait for 7.5 at least. Does it make 
> sense to turn it into a TODO?
> 
>   * promote debug_query_string into a documented, supported feature
> 
> Anyone who *does* use the function from dblink, please be sure to report 
> circumstances where dblink_current_query() returns something other than 
> what you would expect.
> 
> Thanks,
> 
> Joe
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

  11. Re: [GENERAL] capturing and storing query statement with

    Peter Eisentraut <peter_e@gmx.net> — 2003-06-27T12:37:11Z

    Joe Conway writes:

> I was thinking something similar. This exact question has come up at
> least three times in the last three months. I doubt we'd want a special
> keyword like CURRENT_QUERY, but maybe current_query()?

The current statement can be examined using the statistics views and
functions.

-- 
Peter Eisentraut   peter_e@gmx.net

  12. Re: [GENERAL] capturing and storing query statement with

    Tom Lane <tgl@sss.pgh.pa.us> — 2003-06-27T15:05:20Z

    Peter Eisentraut <peter_e@gmx.net> writes:
> Joe Conway writes:
>> I was thinking something similar. This exact question has come up at
>> least three times in the last three months. I doubt we'd want a special
>> keyword like CURRENT_QUERY, but maybe current_query()?

> The current statement can be examined using the statistics views and
> functions.

That's not very reliable though --- unless the current query has been
running for quite a few milliseconds, there's no guarantee it will be
reflected in the statistics collector's output.

			regards, tom lane