Thread: 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@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" -----------------------------------------------------------------------------------------------
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/ > 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.
Attachment
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@svana.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.
Attachment
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