Logging of sql statements?

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.