Logging prepared statements in 8.1?

I am upgrading from 7.4 to 8.1 and there seems to have been a change in how postgresql log prepared
sql statements.. Before I could see the actual statements (i.e. after every parameters had been
changed with real values) but now I see the sql statements before the change (i.e with $1, $2 as
parameters)..

Is there a way to see the actual statements in 8.1?

Regards,

BTJ

--
-----------------------------------------------------------------------------------------------
Bjørn T Johansen

btj(at)havleik(dot)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"
-----------------------------------------------------------------------------------------------

Dear all,

I've written a bash script that looks like the one below:

#!/bin/bash

DBuser='root'
DBname='test'

psql -q --username=$DBuser --dbname=$DBname -c "prepare test_statement
(integer) as insert into tbl_test (col_test) values (\\$1)";

tail -f /root/testfile | while read a;
do
psql -q --username=$DBuser --dbname=$DBname -c "execute
test_statement ($a)";
done;

psql -q --username=$DBuser --dbname=$DBname -c "deallocate test_statement";

Note that this is very stripped version of the real script, but it gives the
same errors:

ERROR: prepared statement "test_statement" does not exist

I persume that this is caused because of the individual statements, each
using their own session. But is there a way to avoid this?
In reality the statement that is prepared is much more complex, and the
files that are processed are rather big. I hoped to gain more performance by
preparing the statement.

Your sincerely,

Aarjan Langereis

Ps. The bash script is ran on an Fedora Core 3 machine using PostgreSQL
8.1.0

On Wed, Nov 23, 2005 at 10:38:03AM +0100, A.j. Langereis wrote:
> Dear all,
>
> I've written a bash script that looks like the one below:

<snip>

> Note that this is very stripped version of the real script, but it gives the
> same errors:
>
> ERROR: prepared statement "test_statement" does not exist

I think your speed is being limited by backend startup time and
transaction commit time more than anything else. I don't think prepared
statements will help in your case.

The way I usually do it is pipe the output of a whole loop to psql like
so:

for i in blah ; do
echo "insert into ..."
done | psql -q

Or more commonly, just have the script emit all the commands to stdout
and then run it like so:

./myscript | psql -q

An important way to increase speed would be to use explicit
transactions (BEGIN/END). When executing a lot of statements this will
speed up things considerably. Finally, if it's just INSERTs, consider
using COPY, for even more efficiency.

Hope this helps,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Dear Martijn,

The problem with your solution is that the script is meant to process a
log-file real-time.
Therefore the insert should be done immediately, however it is the same
statement over and over agian, just with different parameters i.e. an ideal
case fore PREPARE.

Yours,

Aarjan Langereis

Ps. I recieved your reply as an attechment in the email ?

> I think your speed is being limited by backend startup time and
> transaction commit time more than anything else. I don't think prepared
> statements will help in your case.
>
> The way I usually do it is pipe the output of a whole loop to psql like
> so:
>
> for i in blah ; do
> echo "insert into ..."
> done | psql -q
>
> Or more commonly, just have the script emit all the commands to stdout
> and then run it like so:
>
> ./myscript | psql -q
>
> An important way to increase speed would be to use explicit
> transactions (BEGIN/END). When executing a lot of statements this will
> speed up things considerably. Finally, if it's just INSERTs, consider
> using COPY, for even more efficiency.
>

On Wed, Nov 23, 2005 at 12:07:01PM +0100, A.j. Langereis wrote:
> Dear Martijn,
>
> The problem with your solution is that the script is meant to process a
> log-file real-time.
> Therefore the insert should be done immediately, however it is the same
> statement over and over agian, just with different parameters i.e. an ideal
> case fore PREPARE.

But it will be done in real-time. As soon as the insert reaches psql,
it will execute it. When in a pipe the two programs run in parallel.

My point was, the reason your PREPARE isn't working is because you keep
starting new processes. But if you fix that it will probably be fast
enough that PREPARE won't make a difference anymore.

What I suggested will work with PREPARE also, it just becomes more
complicated:

(echo "PREPARE blah;"
for i in blah ; do
echo "EXECUTE blah;"
done) | psql -q

Or perhaps you should move to a language like Perl where this problem
doesn't exist.

Have a nice day,

> Ps. I recieved your reply as an attechment in the email ?

That's your email client not handling MIME properly.

Have a nice day,

> > I think your speed is being limited by backend startup time and
> > transaction commit time more than anything else. I don't think prepared
> > statements will help in your case.
> >
> > The way I usually do it is pipe the output of a whole loop to psql like
> > so:
> >
> > for i in blah ; do
> > echo "insert into ..."
> > done | psql -q
> >
> > Or more commonly, just have the script emit all the commands to stdout
> > and then run it like so:
> >
> > ./myscript | psql -q
> >
> > An important way to increase speed would be to use explicit
> > transactions (BEGIN/END). When executing a lot of statements this will
> > speed up things considerably. Finally, if it's just INSERTs, consider
> > using COPY, for even more efficiency.
> >
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq

--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Bjrn T Johansen wrote:
> I am upgrading from 7.4 to 8.1 and there seems to have been a change
> in how postgresql log prepared sql statements.. Before I could see the
> actual statements (i.e. after every parameters had been changed with
> real values) but now I see the sql statements before the change (i.e
> with $1, $2 as parameters)..
>
> Is there a way to see the actual statements in 8.1?

Uh, not sure. Perhaps the interface wasn't using real prepared
statements, but now it is. I am not aware we ever output the query with
the parameters in place. 8.1 logs prepared statements while in previous
releases it didn't properly.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073