I have considered opening a single connection to the server and letting all
threads execute statements through it, and use prepared statements (serviced
by postgresql, not JDBC or DBCP). Or simply opening 50 connections and
establishing prepared statements and then handing them off to the
On 11 August 2015 at 06:44, Mister Junk junkmail3568...@gmail.com wrote:
I'm using prepared statements to prevent SQL injection, but I have done some
reading and it seems like using Prepared statements COULD improve
performance. I understand the concept, but I do not know how to implement
I'm working on a project for school that requires a simple java server (a
ServerSocket creates Socket connections which I send JSON strings over)
which then communicates with a database. Obviously I have chosen
PostgreSQL.
I'm using Apache DBCP for connection pooling. The DataSource is handed
On 02/11/2015 09:42 AM, deepak wrote:
Hi,
I find that one can't have a prepared statement with bind parameters for
a DDL statement,
although I couldn't find the rationale for this restriction. Is this
limitation due to the database
design, or is it something that's imposed by the SQL standard
deepak deepak...@gmail.com writes:
I find that one can't have a prepared statement with bind parameters for a
DDL statement,
Nope. DDL commands generally don't have any support for evaluating
expressions, which would be the context in which parameters would
be useful. Nor have they got plans,
Hi,
I find that one can't have a prepared statement with bind parameters for a
DDL statement,
although I couldn't find the rationale for this restriction. Is this
limitation due to the database
design, or is it something that's imposed by the SQL standard and/or the
JDBC drivers?
Please
On Wed, Feb 11, 2015 at 02:22:10PM -0500, Tom Lane wrote:
deepak deepak...@gmail.com writes:
I find that one can't have a prepared statement with bind parameters for a
DDL statement,
Nope. DDL commands generally don't have any support for evaluating
expressions, which would be the
Martijn van Oosterhout klep...@svana.org writes:
On Wed, Feb 11, 2015 at 02:22:10PM -0500, Tom Lane wrote:
Nope. DDL commands generally don't have any support for evaluating
expressions, which would be the context in which parameters would
be useful. Nor have they got plans, which would be
That is an interesting approach. However, I see the problem that the
functions would have to be removed when no longer needed. If that fails
(broken connection etc.), they would be orphaned.
Prepared statements are bound to the connection, so when the connection
is closed they are gone.
On Thu,
On Tue, Jan 28, 2014 at 7:53 AM, Andreas Lubensky luben...@cognitec.com wrote:
That is an interesting approach. However, I see the problem that the
functions would have to be removed when no longer needed. If that fails
(broken connection etc.), they would be orphaned.
Prepared statements are
Hello,
When implementing a database backend with libpq I realized that it seems
to be impossible to declare a cursor on a prepared statement. Is this
correct? What is the reason for this limitation?
--
with best regards,
Andreas Lubensky
--
Sent via pgsql-general mailing list
pgpool-II may do what you want.
On Thu, Jan 23, 2014 at 6:31 AM, Andreas Lubensky luben...@cognitec.comwrote:
Hello,
When implementing a database backend with libpq I realized that it seems
to be impossible to declare a cursor on a prepared statement. Is this
correct? What is the reason for
Sorry, answered wrong posting.
On Thu, Jan 23, 2014 at 6:31 AM, Andreas Lubensky luben...@cognitec.comwrote:
Hello,
When implementing a database backend with libpq I realized that it seems
to be impossible to declare a cursor on a prepared statement. Is this
correct? What is the reason for
On Thu, Jan 23, 2014 at 8:31 AM, Andreas Lubensky luben...@cognitec.com wrote:
Hello,
When implementing a database backend with libpq I realized that it seems
to be impossible to declare a cursor on a prepared statement. Is this
correct? What is the reason for this limitation?
I can't think
Hi!
My reading to date suggests that prepared statements should be faster to
execute than issuing the same statement multiple times. However, issuing
100'000 INSERTs turned out to be more than ten times faster than executing
the same prepared statement 100'000 times when executed via pgAdmin. The
Hi again,
I did a follow up test using 'multi-value' inserts which is three times
faster than multiple inserts thusly:
TRUNCATE test;
BEGIN;
INSERT INTO test (one, two, three, four, five) VALUES ('2011-01-01', true,
'three', 4, 5.5)
,('2011-01-01', true, 'three', 4, 5.5)
-- 99'998 more ,
Hello
2012/5/10 Daniel McGreal daniel.mcgr...@redbite.com:
Hi again,
I did a follow up test using 'multi-value' inserts which is three times
faster than multiple inserts thusly:
if you need speed, use a COPY statement - it should be 10x faster than INSERTS
Pavel
TRUNCATE test;
BEGIN;
Hi,
Unfortunately these are experimental conditions. The conditions surrounding
the intended application are such that my two options are prepared
statements or many inserts. I put the multi-value inserts in as I was
curious as to why prepared statements would be slower given they only plan
the
On 10 May 2012 11:30, Daniel McGreal daniel.mcgr...@redbite.com wrote:
I put the multi-value inserts in as I was
curious as to why prepared statements would be slower given they only plan
the query once (as also does the multi-value insert, I assume).
That's a common misconception.
The reason
On Thu, 10 May 2012 13:52:29 +0200, Alban Hertroys wrote:
On 10 May 2012 11:30, Daniel McGreal daniel.mcgr...@redbite.com
wrote:
I put the multi-value inserts in as I was
curious as to why prepared statements would be slower given they
only plan
the query once (as also does the multi-value
On Thu, May 10, 2012 at 6:52 AM, Alban Hertroys haram...@gmail.com wrote:
On 10 May 2012 11:30, Daniel McGreal daniel.mcgr...@redbite.com wrote:
I put the multi-value inserts in as I was
curious as to why prepared statements would be slower given they only plan
the query once (as also does the
On 10 May 2012 15:05, Radosław Smogura rsmog...@softperience.eu wrote:
May I ask what kind of planning may occur during insert?
Well, for example, if there's a unique constraint on the table then
the database will have to check that the newly inserted values don't
conflict with values that are
Alban Hertroys haram...@gmail.com writes:
On 10 May 2012 15:05, RadosÅaw Smogura rsmog...@softperience.eu wrote:
May I ask what kind of planning may occur during insert?
Well, for example, if there's a unique constraint on the table then
the database will have to check that the newly
Doing the same tests from psql gives:
1. ~2.5 seconds for INSERT/VALUES
2. ~10 seconds for prepared statement executes
3. ~15 seconds for multiple INSERTs
Dan.
On Thu, May 10, 2012 at 3:42 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Alban Hertroys haram...@gmail.com writes:
On 10 May
-Original Message-
From: Thom Brown [mailto:t...@linux.com]
Sent: Wednesday, September 29, 2010 2:08 PM
To: PGSQL Mailing List
Subject: Prepared statements and unknown types
Could someone explain why the following doesn't work?
test=# PREPARE meow(unknown) AS
test-# SELECT
On 30 September 2010 14:36, Igor Neyman iney...@perceptron.com wrote:
-Original Message-
From: Thom Brown [mailto:t...@linux.com]
Sent: Wednesday, September 29, 2010 2:08 PM
To: PGSQL Mailing List
Subject: Prepared statements and unknown types
Could someone explain why the following
Could someone explain why the following doesn't work?
test=# PREPARE meow(unknown) AS
test-# SELECT $1 as meow;
ERROR: could not determine data type of parameter $1
The problem is that using PDO in PHP, prepared statements aren't
possible if values are used instead of columns in the select
On Wed, Sep 29, 2010 at 07:08:22PM +0100, Thom Brown wrote:
Could someone explain why the following doesn't work?
test=# PREPARE meow(unknown) AS
test-# SELECT $1 as meow;
ERROR: could not determine data type of parameter $1
The problem is that using PDO in PHP, prepared statements
On 29 September 2010 19:15, Peter Bex peter@xs4all.nl wrote:
On Wed, Sep 29, 2010 at 07:08:22PM +0100, Thom Brown wrote:
Could someone explain why the following doesn't work?
test=# PREPARE meow(unknown) AS
test-# SELECT $1 as meow;
ERROR: could not determine data type of parameter $1
On Wed, Sep 29, 2010 at 07:33:53PM +0100, Thom Brown wrote:
Okay, I understand what's happening. But does the planner need to
understand the type of literals in the select list if it's not used
anywhere else?
Fields sent back to the client also carry their type with them.
There's no unknown
On 29 September 2010 19:41, Peter Bex peter@xs4all.nl wrote:
On Wed, Sep 29, 2010 at 07:33:53PM +0100, Thom Brown wrote:
Okay, I understand what's happening. But does the planner need to
understand the type of literals in the select list if it's not used
anywhere else?
Fields sent back
Peter Bex peter@xs4all.nl writes:
On Wed, Sep 29, 2010 at 07:33:53PM +0100, Thom Brown wrote:
Okay, I understand what's happening. But does the planner need to
understand the type of literals in the select list if it's not used
anywhere else?
Fields sent back to the client also carry
On 29 September 2010 20:02, Tom Lane t...@sss.pgh.pa.us wrote:
Peter Bex peter@xs4all.nl writes:
On Wed, Sep 29, 2010 at 07:33:53PM +0100, Thom Brown wrote:
Okay, I understand what's happening. But does the planner need to
understand the type of literals in the select list if it's not
On 24 Jul 2010, at 1:32, Scott Frankel wrote:
Works! The bug in my example was not passing the INSERT statement an
explicit list of column names, as per any non-prepared insert.
You would have needed it for an unprepared statement just as well in this case.
You expect the planner to guess
Hi all,
I'm working with prepared statements directly in pg for the first time
and have a couple of questions.
Does a prepared statement used to insert into a table need to insert
into all columns of the table? I've found that, for a table with a
serial sequence key as its first column,
Scott Frankel wrote:
I've found that, for a table with a
serial sequence key as its first column, I have to specify the key in
my prepared statement or I get type errors: ERROR: column foo_id
is of type integer but expression is of type character varying.
Let's try:
test=
Works! The bug in my example was not passing the INSERT statement an
explicit list of column names, as per any non-prepared insert.
Thanks!
Scott
On Jul 23, 2010, at 2:53 PM, Daniel Verite wrote:
Scott Frankel wrote:
I've found that, for a table with a
serial sequence key as
I'm using PDO in PHP for database access (actually Zend Framework), and
following best practice by using parameters in prepared statements.
My question is, what do people normally do when there's a variable
number of parameters? - for example:
select . where item_id in ( ... );
In this
Hello
2009/10/10 Raymond O'Donnell r...@iol.ie:
I'm using PDO in PHP for database access (actually Zend Framework), and
following best practice by using parameters in prepared statements.
My question is, what do people normally do when there's a variable
number of parameters? - for example:
On 10/10/2009 18:52, Pavel Stehule wrote:
2009/10/10 Raymond O'Donnell r...@iol.ie:
My question is, what do people normally do when there's a variable
number of parameters? - for example:
select . where item_id in ( ... );
You can use arrays here.
select ... where item_id =
-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160
It's perfectly valid (from the DBI's point of view) for prepare() to
return a prepared statement handle for an invalid statement.
On Thu, May 07, 2009 at 06:08:12PM -0700, David Fetter wrote:
On Fri, May 08, 2009 at 01:02:04AM +0100, Tim Bunce wrote:
On Thu, May 07, 2009 at 06:50:11AM -0700, David Fetter wrote:
On Thu, May 07, 2009 at 02:31:08PM +0100, Tim Bunce wrote:
On Thu, May 07, 2009 at 04:54:06AM +1200,
Tim Bunce wrote:
So you're okay with breaking previously working, and prefectly valid,
DBI code?
I think the rationale is that such code was working by virtue of how
prepare() was implemented in DBD::Pg, but was not valid nonetheless,
as outlined with this example:
On Fri, May 08, 2009 at 09:44:56AM +0100, Tim Bunce wrote:
On Thu, May 07, 2009 at 06:08:12PM -0700, David Fetter wrote:
On Fri, May 08, 2009 at 01:02:04AM +0100, Tim Bunce wrote:
On Thu, May 07, 2009 at 06:50:11AM -0700, David Fetter wrote:
On Thu, May 07, 2009 at 02:31:08PM +0100, Tim
On Fri, May 08, 2009 at 04:02:29PM +0200, Daniel Verite wrote:
Tim Bunce wrote:
So you're okay with breaking previously working, and prefectly valid,
DBI code?
I think the rationale is that such code was working by virtue of how
prepare() was implemented in DBD::Pg, but was not
Tim Bunce wrote:
The example that started this thread was that this valid statement
worked:
prepare(CREATE TEMP TABLE foo(...); INSERT INTO foo(1, 1); INSERT
INTO foo(2, 2);)
but this valid statement didn't:
prepare( INSERT INTO foo(1, 1); INSERT
INTO foo(2, 2);)
My
Daniel Verite wrote:
Tim Bunce wrote:
The example that started this thread was that this valid statement
worked:
prepare(CREATE TEMP TABLE foo(...); INSERT INTO foo(1, 1); INSERT
INTO foo(2, 2);)
but this valid statement didn't:
prepare( INSERT INTO foo(1, 1);
On Thu, May 07, 2009 at 04:54:06AM +1200, Andrej wrote:
2009/5/7 JP Fletcher jpfle...@ca.afilias.info:
Hi,
I see different behavior with DBI/DBD::Pg (1.607/2.11.8, pg 8.1) when the
first command in a prepared statement is 'CREATE TEMP TABLE'.
For instance, this works:
my
On Thu, May 07, 2009 at 02:31:08PM +0100, Tim Bunce wrote:
On Thu, May 07, 2009 at 04:54:06AM +1200, Andrej wrote:
2009/5/7 JP Fletcher jpfle...@ca.afilias.info:
Hi,
I see different behavior with DBI/DBD::Pg (1.607/2.11.8, pg 8.1) when the
first command in a prepared statement is
On Thu, May 07, 2009 at 06:50:11AM -0700, David Fetter wrote:
On Thu, May 07, 2009 at 02:31:08PM +0100, Tim Bunce wrote:
On Thu, May 07, 2009 at 04:54:06AM +1200, Andrej wrote:
WARNING: DBD::Pg now (as of version 1.40) uses true prepared
statements by sending them to the backend to be
On Fri, May 08, 2009 at 01:02:04AM +0100, Tim Bunce wrote:
On Thu, May 07, 2009 at 06:50:11AM -0700, David Fetter wrote:
On Thu, May 07, 2009 at 02:31:08PM +0100, Tim Bunce wrote:
On Thu, May 07, 2009 at 04:54:06AM +1200, Andrej wrote:
WARNING: DBD::Pg now (as of version 1.40) uses
Hi,
I see different behavior with DBI/DBD::Pg (1.607/2.11.8, pg 8.1) when
the first command in a prepared statement is 'CREATE TEMP TABLE'.
For instance, this works:
my $prepare_sql =SQL;
CREATE TEMP TABLE foo( id int, user_id int,);
INSERT INTO foo(1, 1);
INSERT
On May 6, 2009, at 9:39 AM, JP Fletcher wrote:
Hi,
I see different behavior with DBI/DBD::Pg (1.607/2.11.8, pg 8.1)
when the first command in a prepared statement is 'CREATE TEMP TABLE'.
For instance, this works:
my $prepare_sql =SQL;
CREATE TEMP TABLE foo( id int, user_id int,);
2009/5/7 JP Fletcher jpfle...@ca.afilias.info:
Hi,
I see different behavior with DBI/DBD::Pg (1.607/2.11.8, pg 8.1) when the
first command in a prepared statement is 'CREATE TEMP TABLE'.
For instance, this works:
my $prepare_sql =SQL;
CREATE TEMP TABLE foo( id int, user_id int,);
The following works executed in the query browser:
PREPARE myquery (text) AS INSERT INTO myTable (word) VALUES ($1);
EXECUTE myquery('blah');
The following works in C++, executed as two statements with PQexecParams
using PQEXECPARAM_FORMAT_BINARY
PREPARE myquery AS INSERT INTO myTable (word)
On Fri, Sep 5, 2008 at 2:52 AM, Subspace god [EMAIL PROTECTED] wrote:
The following does not work in C++, executed as above, in another session
using two subsequent calls to PQexecParams
PREPARE myquery (text) AS INSERT INTO myTable (word) VALUES ($1);
EXECUTE myquery($1::text);
You're
Am Sonntag 13 Januar 2008 00:46:50 schrieb Tom Lane:
Kris Jurka [EMAIL PROTECTED] writes:
On Thu, 10 Jan 2008, [EMAIL PROTECTED] wrote:
What do you mean with longer lifespan? Doesn't the JDBC driver uses
the PREPARE Sql Statement and therefore the prepared Statement has the
same lifespan
On Thu, 10 Jan 2008, [EMAIL PROTECTED] wrote:
ah! So it doesn't help if it's the same statement, it has to be the same
object! So DBCP has a statement pool like a map, say
MapString, PreparedStatement
so it can fetch the reference to already existing prepared Statement by
looking at the
Kris Jurka [EMAIL PROTECTED] writes:
On Thu, 10 Jan 2008, [EMAIL PROTECTED] wrote:
What do you mean with longer lifespan? Doesn't the JDBC driver uses the
PREPARE Sql Statement and therefore the prepared Statement has the same
lifespan as the connection? If so, as connections are pooled and
first: thanks a lot for your answer. it already helped me a lot, but i still
have some questions:
Am Mittwoch, 9. Januar 2008 21:16 schrieb Kris Jurka:
On Wed, 9 Jan 2008, [EMAIL PROTECTED] wrote:
- I know there is a PREPARE Statement in Postgresql and read the docs.
- in PostgresqlJDBC i
On 1/10/08, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
I have not found anything about preparing unnamed statements. What
does it mean?
Unnamed statements are what the driver uses before it hits the
prepareThreshold limit. Once it has determined the statement will be
reused many
Hi,
i am trying to understand Prepared Statements. I am asking because i want to
understand the impact of Prepared statements to my application.
Actually i use Hibernate, DBCP Connection Pool with Postgresql-JDBC Driver and
Postgresql 8.1.
- I know there is a PREPARE Statement in Postgresql
-general@postgresql.org
Sent: Wednesday, January 09, 2008 2:02 PM
Subject: [GENERAL] Prepared Statements
Hi,
i am trying to understand Prepared Statements. I am asking because i
want to
understand the impact of Prepared statements to my application.
Actually i use Hibernate, DBCP Connection Pool
On Wed, 9 Jan 2008, [EMAIL PROTECTED] wrote:
- I know there is a PREPARE Statement in Postgresql and read the docs.
- in PostgresqlJDBC i have a prepareThreshold parameter which i left to
default of 5.
- in DBCP i have a property poolPreparedStatements, set to true. Does ist
just configure
Hi, I'm planning to use prepared statements of indefinite lifetime in a
daemon that will execute same statements rather frequently in reply to
client requests.
This link:
http://www.postgresql.org/docs/8.3/static/sql-prepare.html
has a note on performance:
In some situations, the query plan
Il Wednesday 07 November 2007 12:25:46 rihad ha scritto:
I don't understand why postgres couldn't plan this:
SELECT foo.e, foo.f
FROM foo
WHERE pk=$1 AND b=$2 AND status='1' AND c = $3;
to be later executed any slower than
SELECT foo.e, foo.f
FROM foo
WHERE pk='abcabc' AND b='13' AND
rihad wrote:
Hi, I'm planning to use prepared statements of indefinite lifetime in a
daemon that will execute same statements rather frequently in reply to
client requests.
This link:
http://www.postgresql.org/docs/8.3/static/sql-prepare.html
has a note on performance:
In some situations,
rihad [EMAIL PROTECTED] writes:
Aha, thanks for a thorough explanation. Now I understand that while
looking for a way to fulfill the query postgres will try hard to pick
the one requiring the least number of rows visits. I've skimmed over my
queries: almost all of them make use of the
rihad [EMAIL PROTECTED] writes:
I don't understand why postgres couldn't plan this:
SELECT foo.e, foo.f
FROM foo
WHERE pk=$1 AND b=$2 AND status='1' AND c = $3;
to be later executed any slower than
SELECT foo.e, foo.f
FROM foo
WHERE pk='abcabc' AND b='13' AND status='1' AND c =
Tom Lane wrote:
rihad [EMAIL PROTECTED] writes:
I don't understand why postgres couldn't plan this:
SELECT foo.e, foo.f
FROM foo
WHERE pk=$1 AND b=$2 AND status='1' AND c = $3;
to be later executed any slower than
SELECT foo.e, foo.f
FROM foo
WHERE pk='abcabc' AND b='13' AND status='1'
rihad wrote:
Hi, I'm planning to use prepared statements of indefinite
lifetime in a daemon that will execute same statements
rather frequently in reply to client requests.
This link:
http://www.postgresql.org/docs/8.3/static/sql-prepare.html
has a note on performance:
In some
Does anyone know the exact definition of a Prepared Statement?
String sql = SELECT count(id) FROM customer;
executeQuery(sql);
I assume the sql string is a prepared statement? Semantic debate here. -
Thx
---(end of broadcast)---
TIP
72 matches
Mail list logo
