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. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
