Re: [GENERAL] Prepared Statements and Pooling

On 11 August 2015 at 06:44, Mister Junk 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 > this. They could, they c

Re: [GENERAL] Prepared Statements and Pooling

> 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 t

[GENERAL] Prepared Statements and Pooling

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 of

Re: [GENERAL] Prepared statements with bind parameters for DDL

Martijn van Oosterhout 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 the requirem

Re: [GENERAL] Prepared statements with bind parameters for DDL

On Wed, Feb 11, 2015 at 02:22:10PM -0500, Tom Lane wrote: > deepak 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 p

Re: [GENERAL] Prepared statements with bind parameters for DDL

deepak 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, which would be th

Re: [GENERAL] Prepared statements with bind parameters for DDL

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 a

[GENERAL] Prepared statements with bind parameters for DDL

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 clarify.

Re: [GENERAL] Prepared statements and cursors

On Tue, Jan 28, 2014 at 7:53 AM, Andreas Lubensky 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 bound to the conne

Re: [GENERAL] Prepared statements and cursors

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, 20

Re: [GENERAL] Prepared statements and cursors

On Thu, Jan 23, 2014 at 8:31 AM, Andreas Lubensky 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 of any but it can b

Re: [GENERAL] Prepared statements and cursors

Sorry, answered wrong posting. On Thu, Jan 23, 2014 at 6:31 AM, Andreas Lubensky 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? >

Re: [GENERAL] Prepared statements and cursors

pgpool-II may do what you want. On Thu, Jan 23, 2014 at 6:31 AM, Andreas Lubensky 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?

[GENERAL] Prepared statements and cursors

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 (pgsql-general@

Re: [GENERAL] Prepared statements performance

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 wrote: > Alban Hertroys writes: > > On 10 May 2012 15:05, Radosław Smogura wrote:

Re: [GENERAL] Prepared statements performance

Alban Hertroys writes: > On 10 May 2012 15:05, Radosław Smogura 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

Re: [GENERAL] Prepared statements performance

On 10 May 2012 15:05, Radosław Smogura 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 already in the table. It

Re: [GENERAL] Prepared statements performance

On Thu, May 10, 2012 at 6:52 AM, Alban Hertroys wrote: > On 10 May 2012 11:30, Daniel McGreal 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). > > Th

Re: [GENERAL] Prepared statements performance

On Thu, 10 May 2012 13:52:29 +0200, Alban Hertroys wrote: On 10 May 2012 11:30, Daniel McGreal 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

Re: [GENERAL] Prepared statements performance

On 10 May 2012 11:30, Daniel McGreal 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 that prepared statement

Re: [GENERAL] Prepared statements performance

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 qu

Re: [GENERAL] Prepared statements performance

Hello 2012/5/10 Daniel McGreal : > 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; > INSERT INTO test (

Re: [GENERAL] Prepared statements performance

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 , ('2011-0

[GENERAL] Prepared statements performance

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

Re: [GENERAL] Prepared statements and unknown types

On 30 September 2010 14:36, Igor Neyman 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 doesn't

Re: [GENERAL] Prepared statements and unknown types

> -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-

Re: [GENERAL] Prepared statements and unknown types

On 29 September 2010 20:02, Tom Lane wrote: > Peter Bex 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? > >> Fie

Re: [GENERAL] Prepared statements and unknown types

Peter Bex 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 their type wit

Re: [GENERAL] Prepared statements and unknown types

On 29 September 2010 19:41, Peter Bex 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 to the clie

Re: [GENERAL] Prepared statements and unknown types

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 "unknow

Re: [GENERAL] Prepared statements and unknown types

On 29 September 2010 19:15, Peter Bex 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 >> >> The

Re: [GENERAL] Prepared statements and unknown types

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

[GENERAL] Prepared statements and unknown types

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 list.

Re: [GENERAL] prepared statements

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

Re: [GENERAL] prepared statements

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 it

Re: [GENERAL] prepared statements

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: tes

[GENERAL] prepared statements

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,

Re: [GENERAL] Prepared statements with a variable number of parameters

On 10/10/2009 18:52, Pavel Stehule wrote: > 2009/10/10 Raymond O'Donnell : >> 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 = A

Re: [GENERAL] Prepared statements with a variable number of parameters

Hello 2009/10/10 Raymond O'Donnell : > 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: > >  se

[GENERAL] Prepared statements with a variable number of parameters

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 cas

Re: [GENERAL] prepared statements and DBD::Pg

-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. >

Re: [GENERAL] prepared statements and DBD::Pg

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); INS

Re: [GENERAL] prepared statements and DBD::Pg

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);") M

Re: [GENERAL] prepared statements and DBD::Pg

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 n

Re: [GENERAL] prepared statements and DBD::Pg

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 +0

Re: [GENERAL] prepared statements and DBD::Pg

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: http://archives

Re: [GENERAL] prepared statements and DBD::Pg

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 +1

Re: [GENERAL] prepared statements and DBD::Pg

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 versio

Re: [GENERAL] prepared statements and DBD::Pg

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 ba

Re: [GENERAL] prepared statements and DBD::Pg

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 : > > > 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

Re: [GENERAL] prepared statements and DBD::Pg

On Thu, May 07, 2009 at 04:54:06AM +1200, Andrej wrote: > 2009/5/7 JP Fletcher : > > 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 =<

Re: [GENERAL] prepared statements and DBD::Pg

2009/5/7 JP Fletcher : > 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 =< CREATE TEMP TABLE foo( id int, user_id int,); > > INSERT INTO

Re: [GENERAL] prepared statements and DBD::Pg

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 =

[GENERAL] prepared statements and DBD::Pg

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 =

Re: [GENERAL] Prepared statements aren't working with parameters with PQexecParams

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'r

[GENERAL] Prepared statements aren't working with parameters with PQexecParams

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) VAL

Re: [GENERAL] Prepared Statements

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 > >>

Re: [GENERAL] Prepared Statements

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 poo

Re: [GENERAL] Prepared Statements

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 Map so it can fetch the reference to already existing prepared Statement by looking at the statement itself, right? Exac

Re: [GENERAL] Prepared Statements

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

Re: [GENERAL] Prepared Statements

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

Re: [GENERAL] Prepared Statements

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 p

Re: [GENERAL] Prepared Statements

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 Hib

[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 with Postgresql-JDBC Driver and Postgresql 8.1. - I know there is a PREPARE Statement in Postgresq

Re: [GENERAL] prepared statements suboptimal?

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

Re: [GENERAL] prepared statements suboptimal?

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'

Re: [GENERAL] prepared statements suboptimal?

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 <

Re: [GENERAL] prepared statements suboptimal?

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 s

Re: [GENERAL] prepared statements suboptimal?

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, the

Re: [GENERAL] prepared statements suboptimal?

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='1

[GENERAL] prepared statements suboptimal?

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 pr

[GENERAL] Prepared Statements

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