Re: PREPARE in bash scripts

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.