pg.ddx.io  pgsql-php@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Andrew McMillan <andrew@morphoss.com>
To: V S P <toreason@fastmail.fm>
Cc: pgsql-php@postgresql.org
Subject: Re: [Q] PDO use to bind arrays for insert
Date: Mon, 17 Nov 2008 21:26:42 +1300
Message-ID: <1226910402.5577.280.camel@happy.mcmillan.net.nz> (raw)
In-Reply-To: <1226896018.14093.1285127497@webmail.messagingengine.com>
References: <1226803530.24888.1284994985@webmail.messagingengine.com>
	<492093A5.7040606@gmail.com>
	<20081117002630.d86160fd.knl@bitflop.com>
	<1226890873.32288.1285117971@webmail.messagingengine.com>
	<1226894747.5577.267.camel@happy.mcmillan.net.nz>
	<1226896018.14093.1285127497@webmail.messagingengine.com>

On Sun, 2008-11-16 at 23:26 -0500, V S P wrote:
> Hi,
> thank you Andrew.
> 
> do you know if query planning happens only once
> for the prepared Insert statements
> where data is fed from within a client loop
> 
>  foreach ...
>     execute prepared insert
>  end
> 
> and if it happens once -- is it on the first insert?

Yes, it happens once on the first one.

> 
> And the last question -- with pgpool
> do prepared statements last between
> different client connections?
> 
> that is If I have say apache setup with 5 processes
> (and therefore PHP can be invoked within 5 worker processes
> simultaneously)
> 
> would the Prepared statements last across invocations

Prepared statements are per connection, and the actions taken at the
close of a connection to pgpool to ready the pool for connection from a
different client will very likely deallocate any prepared statements.
The developers are usually actively trying to minimise pollution of
state from one connection to the next, but persistence of prepared
statements might be a different kettle of fish...

Read here for more information.  In paricular note that PREPARE has it's
greatest impact when used on complex SQL - i.e. probably not INSERT
statements:

http://www.postgresql.org/docs/8.3/interactive/sql-prepare.html

If you really need performance on a stream of inserts it probably is
better to feed them to a separate daemon to specifically handle them
serially, otherwise any peak load on your website is going to also peak
load your DB server.  If you can stream the inserts separately you can
potentially shift that load.  Obviously no good if your inserts aren't
blind, but when there are large streams of them they often are only
looked at statistically at a later time.

Your questions here are really getting beyond the scope of the pgsql-PHP
list though, and you might want to try asking them on pgsql-general or
something where you will have a wider resource of PostgreSQL experts...

Cheers,
					Andrew McMillan.

------------------------------------------------------------------------
andrew (AT) morphoss (DOT) com                            +64(272)DEBIAN
 "... all the modern inconveniences ..."
                -- Mark Twain
------------------------------------------------------------------------





view thread (7+ messages)  latest in thread

Message-ID: <1226910402.5577.280.camel@happy.mcmillan.net.nz>
Permalink:  ../1226910402.5577.280.camel@happy.mcmillan.net.nz/
Also on:    postgresql.org/message-id/1226910402.5577.280.camel@happy.mcmillan.net.nz

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-php@postgresql.org
  Cc: andrew@morphoss.com, toreason@fastmail.fm
  Subject: Re: [Q] PDO use to bind arrays for insert
  In-Reply-To: <1226910402.5577.280.camel@happy.mcmillan.net.nz>
  References: <1226803530.24888.1284994985@webmail.messagingengine.com> <492093A5.7040606@gmail.com> <20081117002630.d86160fd.knl@bitflop.com> <1226890873.32288.1285117971@webmail.messagingengine.com> <1226894747.5577.267.camel@happy.mcmillan.net.nz> <1226896018.14093.1285127497@webmail.messagingengine.com> <1226910402.5577.280.camel@happy.mcmillan.net.nz>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox