Thread: Logging of sql statements?
Is it possible to log the actual statement that the server runs? At the moment, the statement that is logged, is logged using ? for parameters; I would like to log the statement after the parameters have been substituted, isn't this possible in 8.1.x? (used to work in 7.x.x) Regards, BTJ -- ----------------------------------------------------------------------------------------------- Bjørn T Johansen btj@havleik.no ----------------------------------------------------------------------------------------------- Someone wrote: "I understand that if you play a Windows CD backwards you hear strange Satanic messages" To which someone replied: "It's even worse than that; play it forwards and it installs Windows" -----------------------------------------------------------------------------------------------
am 23.03.2006, um 14:07:11 +0100 mailte Bjørn T Johansen folgendes: > Is it possible to log the actual statement that the server runs? At the moment, the statement that is > logged, is logged using ? for parameters; I would like to log the statement after the parameters have been > substituted, isn't this possible in 8.1.x? (used to work in 7.x.x) Yes, this is possible. Set in your postgresql.conf: log_statement = all Since 8.0 oder 8.1, there you can see parameters in prepared statements. HTH, Andreas -- Andreas Kretschmer (Kontakt: siehe Header) Heynitz: 035242/47215, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net === Schollglas Unternehmensgruppe ===
On Thu, 23 Mar 2006 14:25:52 +0100 "A. Kretschmer" <andreas.kretschmer@schollglas.com> wrote: > am 23.03.2006, um 14:07:11 +0100 mailte Bjørn T Johansen folgendes: > > Is it possible to log the actual statement that the server runs? At the moment, the statement that is > > logged, is logged using ? for parameters; I would like to log the statement after the parameters have been > > substituted, isn't this possible in 8.1.x? (used to work in 7.x.x) > > Yes, this is possible. > > Set in your postgresql.conf: > > log_statement = all > > Since 8.0 oder 8.1, there you can see parameters in prepared statements. > > > > HTH, Andreas This is what I am already using and it doesn't work... BTJ
On Thu, 2006-03-23 at 10:09, Bjørn T Johansen wrote: > On Thu, 23 Mar 2006 14:25:52 +0100 > "A. Kretschmer" <andreas.kretschmer@schollglas.com> wrote: > > > am 23.03.2006, um 14:07:11 +0100 mailte Bjørn T Johansen folgendes: > > > Is it possible to log the actual statement that the server runs? At the moment, the statement that is > > > logged, is logged using ? for parameters; I would like to log the statement after the parameters have been > > > substituted, isn't this possible in 8.1.x? (used to work in 7.x.x) > > > > Yes, this is possible. > > > > Set in your postgresql.conf: > > > > log_statement = all > > > > Since 8.0 oder 8.1, there you can see parameters in prepared statements. > > > > > > > > HTH, Andreas > > > This is what I am already using and it doesn't work... I was under the impression that back in the 7.x.y days, prepared statements got handled differently and could be logged with their parameters, but in 8.x.y, the way prepared statements are handled made this impossible. It's a memory from at least 6 months ago, so I could be messing it up a bit. Anyone with a better memory, please chime in.
Bjørn T Johansen <btj@havleik.no> schrieb:
> > Set in your postgresql.conf:
> >
> > log_statement = all
> >
> > Since 8.0 oder 8.1, there you can see parameters in prepared statements.
> >
> >
> >
> > HTH, Andreas
>
>
> This is what I am already using and it doesn't work...
It works for me ;-)
test=# prepare my_foo(int) as select * from foo where id = $1;
PREPARE
test=#
test=# execute my_foo(1);
id | x
----+----
1 | 10
(1 row)
In my log:
LOG: statement: prepare my_foo(int) as select * from foo where id = $1;
LOG: statement: execute my_foo(1); [client PREPARE: prepare my_foo(int) as select * from foo where id = $1;]
test=# select version();
version
--------------------------------------------------------------------------------------------------------------
PostgreSQL 8.1.2 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.0.3 20060104 (prerelease) (Debian 4.0.2-6)
(1 row)
HTH, Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknow)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
On Thu, 23 Mar 2006 17:21:38 +0100 Andreas Kretschmer <akretschmer@spamfence.net> wrote: > Bjørn T Johansen <btj@havleik.no> schrieb: > > > Set in your postgresql.conf: > > > > > > log_statement = all > > > > > > Since 8.0 oder 8.1, there you can see parameters in prepared statements. > > > > > > > > > > > > HTH, Andreas > > > > > > This is what I am already using and it doesn't work... > > It works for me ;-) > > test=# prepare my_foo(int) as select * from foo where id = $1; > PREPARE > test=# > test=# execute my_foo(1); > id | x > ----+---- > 1 | 10 > (1 row) > > > In my log: > LOG: statement: prepare my_foo(int) as select * from foo where id = $1; > LOG: statement: execute my_foo(1); [client PREPARE: prepare my_foo(int) as select * from foo where id = > $1;] > > > test=# select version(); > version > -------------------------------------------------------------------------------------------------------------- > PostgreSQL 8.1.2 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.0.3 20060104 (prerelease) (Debian > 4.0.2-6) (1 row) > > > > HTH, Andreas If I try the same from a client, I get the same result.. But when running from my webapp (using Hibernate), only question marks appear....? BTJ
On Thu, 2006-03-23 at 14:50, Bjørn T Johansen wrote: > On Thu, 23 Mar 2006 17:21:38 +0100 > Andreas Kretschmer <akretschmer@spamfence.net> wrote: > > > Bjørn T Johansen <btj@havleik.no> schrieb: > > > > Set in your postgresql.conf: > > > > > > > > log_statement = all > > > > > > > > Since 8.0 oder 8.1, there you can see parameters in prepared statements. > > > > > > > > > > > > > > > > HTH, Andreas > > > > > > > > > This is what I am already using and it doesn't work... > > > > It works for me ;-) > > > > test=# prepare my_foo(int) as select * from foo where id = $1; > > PREPARE > > test=# > > test=# execute my_foo(1); > > id | x > > ----+---- > > 1 | 10 > > (1 row) > > > > > > In my log: > > LOG: statement: prepare my_foo(int) as select * from foo where id = $1; > > LOG: statement: execute my_foo(1); [client PREPARE: prepare my_foo(int) as select * from foo where id = > > $1;] > > > > > > test=# select version(); > > version > > -------------------------------------------------------------------------------------------------------------- > > PostgreSQL 8.1.2 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.0.3 20060104 (prerelease) (Debian > > 4.0.2-6) (1 row) > > > > > > > > HTH, Andreas > > If I try the same from a client, I get the same result.. But when running from my webapp (using Hibernate), > only question marks appear....? Again, I'm pretty sure this is a known issue with the JDBC driver version. If you can force your JDBC driver to run under the previous protocol, you should get the old behaviour.
On Thu, 23 Mar 2006 15:01:17 -0600 Scott Marlowe <smarlowe@g2switchworks.com> wrote: > > > > If I try the same from a client, I get the same result.. But when running from my webapp (using > > Hibernate), only question marks appear....? > > Again, I'm pretty sure this is a known issue with the JDBC driver > version. If you can force your JDBC driver to run under the previous > protocol, you should get the old behaviour. So basically, I need a JDBC driver like version 7.4 Build 216 instead of my version 8.1 Build 405 driver? What about 8.2dev Build 501, does it fix it? And if so, how stable is that version? BTJ
On Thu, 2006-03-23 at 15:19, Bjørn T Johansen wrote: > On Thu, 23 Mar 2006 15:01:17 -0600 > Scott Marlowe <smarlowe@g2switchworks.com> wrote: > > > > > > > If I try the same from a client, I get the same result.. But when running from my webapp (using > > > Hibernate), only question marks appear....? > > > > Again, I'm pretty sure this is a known issue with the JDBC driver > > version. If you can force your JDBC driver to run under the previous > > protocol, you should get the old behaviour. > > > So basically, I need a JDBC driver like version 7.4 Build 216 instead of my version 8.1 Build 405 driver? > What about 8.2dev Build 501, does it fix it? And if so, how stable is that version? The problems is not in the driver itself, it's associated with the change in the front end / back end protocol. I.e. With 8.x the protocol was upped to V3. V2 fe/be protocol communications still get logged with the parameters, but V3 don't. There might be a setting in the jdbc driver to change this, but I'm not real familiar with the JDBC driver really, I just use it.
I'm working on a database that will (at current load) add 100 million records per day to a database, and needs to keep around 6 months of data online. Of course, we don't want the loads to be running all day while queries are going on. Using COPY with indexes active runs great with an empty database, then drops precipitously as the index size grows. It looks like I should be able to hit the numbers if I insert the data using COPY with no indexes, then add the indexes. I'm looking at partitioning with one table per day. So, we'd be looking at about 180 tables with 100 million rows each. Searches would typically be within a single day, although they could span multiple days. This keeps the indexes a more or less reasonable size, and allows quick deleting of the old data. Is there any problem with 180 child tables? How many would be too many (e.g. if I did one table per 6 hours?) Thanks Wes
On 3/23/06, Wes <wespvp@syntegra.com> wrote: > This keeps the indexes a more or less reasonable size, and allows quick > deleting of the old data. Is there any problem with 180 child tables? How > many would be too many (e.g. if I did one table per 6 hours?) I am not a guru. Many, many people on the list have more experience with Table Partitioning (CE), however I will share my experience. I did some work with CE for our syslog data collection with some nice results. * I found that making functions to do the setup work for CE is a must. * I also found that using 1 rule per table to do query rewriting can become a bottleneck. This was fine for a small case (20-30 rules), but it didn't scale nicely when we had 300 rules. I made 1 table per day and got a nice boost in query speed. So in short, I think this will work nicely for you. COPY does not invoke rules, so you should be fine.