Обсуждение: prepared statements
Hello All,
We are currently experimenting with a postgresql cluster database (pgcluster 1.5rc11).
The cluster replicates all prepared statements across all database servers (which makes sense if they are for updates and inserts). It also does this for normal select statements.
That means that when using prepared statements, there is absolutely NO gain from using a db cluster. Everything is executed in every database. And there is the additional overhead of replicating that across. So huge investment in hardware, and a performance loss is the gain from that :-(
Our application is written in java and we use the jdbc postgresql driver.
Now it appears that this driver issues ANY statement as a prepared statement. I've tried to switch this off but to no avail. This is a huge nightmare and I don't quite understand why it is deemed necessary to issue every simple select statement as a prepared statement.
Maybe someone on this mailing list can enlighten me?
Kind Regards,
Christian
We are currently experimenting with a postgresql cluster database (pgcluster 1.5rc11).
The cluster replicates all prepared statements across all database servers (which makes sense if they are for updates and inserts). It also does this for normal select statements.
That means that when using prepared statements, there is absolutely NO gain from using a db cluster. Everything is executed in every database. And there is the additional overhead of replicating that across. So huge investment in hardware, and a performance loss is the gain from that :-(
Our application is written in java and we use the jdbc postgresql driver.
Now it appears that this driver issues ANY statement as a prepared statement. I've tried to switch this off but to no avail. This is a huge nightmare and I don't quite understand why it is deemed necessary to issue every simple select statement as a prepared statement.
Maybe someone on this mailing list can enlighten me?
Kind Regards,
Christian
-- Christian Dannemann Managing Director Merus Software Ltd Phone: +44 (0)1453 708 600 DDI: +44 (0)1453 708 610 VoIP: christian@sip.merus.co.uk http://www.merus.co.uk
On Tue, 12 Dec 2006, Christian Dannemann wrote: > We are currently experimenting with a postgresql cluster database (pgcluster > 1.5rc11). > > The cluster replicates all prepared statements across all database > servers (which makes sense if they are for updates and inserts). It also > does this for normal select statements. Seems like your complaint is really with pgcluster, not the JDBC driver. > Now it appears that this driver issues ANY statement as a prepared > statement. I've tried to switch this off but to no avail. This is a huge > nightmare and I don't quite understand why it is deemed necessary to > issue every simple select statement as a prepared statement. > Using the extended query protocol makes more features available to us at little to no cost. The ability to send bytea data in binary form, the ability to implement ParameterMetaData, Mikko's recent work on receiving data in binary form, and so on. If you want to disable all of this you can add on ?protocolVersion=2 to your JDBC URL, but again I think your complain should be to the pgcluster people. Kris Jurka
Christian Dannemann wrote: > Hello All, > > We are currently experimenting with a postgresql cluster database > (pgcluster 1.5rc11). > > The cluster replicates all prepared statements across all database > servers (which makes sense if they are for updates and inserts). It also > does this for normal select statements. > > That means that when using prepared statements, there is absolutely NO > gain from using a db cluster. Everything is executed in every database. > And there is the additional overhead of replicating that across. So huge > investment in hardware, and a performance loss is the gain from that :-( In the future, I'd suggest doing more testing before making huge investments in hardware... > Our application is written in java and we use the jdbc postgresql driver. > > Now it appears that this driver issues ANY statement as a prepared > statement. I've tried to switch this off but to no avail. This is a huge > nightmare and I don't quite understand why it is deemed necessary to > issue every simple select statement as a prepared statement. As Kris said, you really should be blaming pgcluster, and you can set the protocolVersion parameter if you need to. There's better alternatives to pgcluster, for example Sequoia (http://sequoia.continuent.org/), or Slony (http://slony.info/) -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com