Received: from BKMAIL.sixtyfootspider.com (sfs-pat.sixtyfootspider.com [216.52.187.112]) by hub.org (8.10.1/8.10.1) with ESMTP id e7LFjVT02257 for ; Mon, 21 Aug 2000 11:45:32 -0400 (EDT) Received: by BKMAIL.sixtyfootspider.com with Internet Mail Service (5.5.2650.21) id ; Mon, 21 Aug 2000 10:37:17 -0500 Message-ID: <01CCE949D2717845BA2E573DC081167E052E84@BKMAIL.sixtyfootspider.com> From: "Ingram, Bryan" To: "'pgsql-sql@postgresql.org'" Subject: Best way to create DML/DDL log? Date: Mon, 21 Aug 2000 10:37:13 -0500 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2650.21) Content-Type: text/plain; charset="iso-8859-1" X-Archive-Number: 200008/203 I would like to create a long of all successful DML/DDL statements. Has anyone accomplished this before? No need to reinvent the wheel ... If not ..I'm wondering what the best approach might be. I have looked at the tcop/postgres.c source, thinking I might start by modifying some of the -dX routines for generating debugging output. I've also tried working a little with the -E option to the postgres backend, (passing it through postmaster as -o -E) but can't seem to redirect its output. Finally, another option might be to create a trigger, but I haven't really explored this option. One of the main requirements is that any function which returns a value which is determined by system environment must be written in the log as the evaluated statement, and not written as the function itself. e.g. now() returns a text string of the current datetime which is written to the log in place of the literal character string 'now()' As far as modifying postgres.c, I'm fairly confident I can create a log of the query buffer, but I haven't yet enough exposure to the source to find where I should check to see whether or not a statement was successful, or from where I might be able to pull text data from evaluated functions. Any commentary on this would be much appreciated. Thanks, Bryan Ingram