Re: [PATCHES] Allow pooled connections to list all prepared queries
On Mon, Dec 27, 2004 at 01:03:08PM -0500, Kris Jurka wrote: has not prepared. So I don't see why, simply because it's in a pool, that it forgets what's been prepared. The connection (the open socket to Postgres) doesn't forget anything. If you have multiple machines sharing a connection pool, though, there may not be any guarantee that the same client-side connection object (in your example, the proxy object) will always be associated with the same underlying connection (the open socket to Postgres). Multiple machines connecting through a single connection pooling server need some way to communicate the state of a particular connection between themselves (state, in this case, being the list of prepared queries). This information could be tracked by the connection pool, it could be tracked by the clients and shared through some back channel, or it could just be exposed via SQL (since Postgres already has it in a convenient format). The patch provides a way to inspect the list of prepared queries on a connection, via SQL. It'd be up to the client to avoid this round-trip when it isn't necessary (e.g. the single-server or single-process cases you've mentioned, where keeping and sharing the list of prepared statements is cheap). At a bare minimum, it'd at least be convenient for humans to be able to connect to a pool and look at what's been prepared. There was no way to do this before. - Dave ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PATCHES] Allow pooled connections to list all prepared queries
On Sat, 25 Dec 2004, David Brown wrote: You're right, in the case of standalone Perl or Apache::DBI. However, if DBD::Pg happens to grab an already-open connection that doesn't have a one-to-one correspondence with a $dbh (e.g. from a Postgres connection pool, or from an external pooling server like DBBalancer[1]), the state of the connection (with respect to past PREPAREs) isn't known. I'm not clear why the pooling case is different. Speaking from the JDBC side (which does connection pooling, but not statement pooling) there is a proxy connection object which wraps a real connection. So each new connection from the pool gets a new proxy object, but the real underlying connection stays the same. So the real connection will know what it has or has not prepared. So I don't see why, simply because it's in a pool, that it forgets what's been prepared. Kris Jurka ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] Allow pooled connections to list all prepared queries
On Wed, 22 Dec 2004, David Brown wrote: This would allow an application inheriting a pooled connection to know the queries prepared in the current session. I don't see this as all that helpful for a client interface that does the preparation itself. Possibly it could be used for libpq, but you mentioned DBI which should already know what it has or has not prepared. The idea of adding a network round trip to detect a prepared statement seems like a performance loss, not a gain. If this is just to avoid repreparing the same statement then perhaps something like PREPARE OR REPLACE would be more useful. Kris Jurka ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PATCHES] Allow pooled connections to list all prepared queries
I don't see this as all that helpful for a client interface that does the preparation itself. Possibly it could be used for libpq, but you mentioned DBI which should already know what it has or has not prepared. The idea of adding a network round trip to detect a prepared statement seems like a performance loss, not a gain. If this is just to avoid repreparing the same statement then perhaps something like PREPARE OR REPLACE would be more useful. Yes, PREPARE OR REPLACE was the other thing I thought would be useful. However, in my example that still means preparing everytime, wasting the benefits. You could have: PREPARE IF NOT EXISTS :P Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PATCHES] Allow pooled connections to list all prepared queries
David Brown [EMAIL PROTECTED] writes: Attached is a loadable module (and a small backend patch) that allows a client to list the available query plans (created with PREPARE, or with a 'parse' protocol message) on a particular connection. * Extended PostgreSQL's SQL grammar to capture the original SQL query string for PREPARE statements. This seems much the ugliest and most invasive part of the patch. I'd suggest doing something similar to what pg_proc.c is doing: /* We can get the original query text from the active portal (hack...) */ Assert(ActivePortal ActivePortal-status == PORTAL_ACTIVE); queryText = ActivePortal-sourceText; * Modified backend/commands/prepare.c to keep some additional statistics in the prepared statement hash table (plan creation time, execution count, etc.) The usefulness of this seems pretty dubious. You aren't going to have a bunch of random bits of code sharing a connection; it's going to be a single application that probably knows perfectly well exactly which queries it needs prepared. So I don't think the stats will pay for themselves. * Added an accessor function to allow for raw access to the prepared statement hash table (necessary for sequential access). It would have been better to put the function that needs this access into prepare.c. There is no point in trying to hide a data structure inside a module if we then turn around and expose the data structure to the world... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
