Logging prepared statements in 8.1?

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@svana.org> http://svana.org/kleptog/

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 ?

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@svana.org> http://svana.org/kleptog/

Bj���rn 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@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073