Re: Recording how a table is used

Hello,

Le 30/06/09 8:47, Daniel Gordon a écrit :
> I'm trying to record the results of a select statement into a separate
> table.  I need the information selected, the column it was stored in,
> the table it was stored in, and the query that selected it.
> [...]
> Here is the table I'm trying to fill, in case it is useful
> 
> create table sql_query_data_log (
>  id serial,
>  create_ time timestamp DEFAULT now(),
>  query varchar,
>  table text,
>  column text,
> 
>  data varchar
>  );
> 
As you said, the usual solution is to process with a tier language
(Perl, PHP, other) on the application (server) side--the one that
submits the query to the database, ie. the one that knows the query text
and is more appropriate for parsing and retrieving target information
like read and written tables, columns and so on.

On the DBMS side, these informations may be stored--and thence
collected--within log files configured at the startup by then DBMS
admin. An example of query log file import is suggested here:
http://www.postgresql.org/docs/8.3/interactive/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-CSVLOG

However, because of the raw format of the query retrieved ('query' field
as text), some parsing would be needed to identify operated objects
(tables, columns). Moreover it is assumed that one knows the correct
path to the target log file and that one has privileges to import data
from this file.

Another crude alternative may be based on triggers as you mentioned.
Triggers may be created on each table for each possible triggered event
(before, after). List of tables is available from table
information_schema.tables (list of columns from
information_schema.columns). On Before trigger, current (old) value of
each column of table may be stored somewhere; on After trigger, old and
new values may be compared; if not equal, then altered columns can be
inferred.

On both sides, some issues remain:
- log import approach: query can be retrieved, whereas altered table or
column are difficult to identify (parsing required). Current query would
also be not so easy to extract from import, unless some judicious
trigger help identifying the convenient id);
- trigger alternative: altered table and column can be enough easily
retrieved, whereas query text is unknown (except by inferring from
modified object, with an obvious approximation). Moreover relation
between some query and current modified object would be not so trivial
when multiple concurrent transactions occur and access the same object.

Application-side tier language programming definitely seems likely a
less complicated way to address your purpose while both query and
objects (tables and columns) are directly known and can be handled for a
log query.

Hoping these ideas help you go on investigating.

Regards.

--
nha / Lyon / France.