[GENERAL] Basic questions about PQprepare()
Hello, I'm trying to write an Apache 1.3.29 module connecting to PostgreSQL 8.1.0 on OpenBSD -current and have few probably simple questions: When an Apache child is initialized, I'd like to establish connection to the database and to prepare 2 queries. And then later in the repeating response phase I'd like to execute those prepared queries. 1) If PQconnectdb fails, do I still need to PQfinish the returned pointer? cfg->conn = PQconnectdb(cfg->conninfo); if (NULL == cfg->conn) { ap_log_error(APLOG_MARK, APLOG_ERR, s, "Connection to database '%s' failed: out of memory", cfg->conninfo); exit(1); } if (PQstatus(cfg->conn) != CONNECTION_OK) { ap_log_error(APLOG_MARK, APLOG_ERR, s, "Connection to database '%s' failed: %s", cfg->conninfo, PQerrorMessage(cfg->conn)); PQfinish(cfg->conn); exit(1); } 2) Similar, if PQprepare fails, do I still need to PQclear its result? And what value is returned on PQprepare success, is it always PGRES_COMMAND_OK (I've got that value, but will it always be so)? #define SQL_BANNED_USER \ "select message, expire from bans where username = $1 and " \ "(expire is null or expire > extract(epoch from localtime))" res = PQprepare(cfg->conn, "sql_banned_user", SQL_FIND_USER, 1, NULL); if (NULL == res) { ap_log_error(APLOG_MARK, APLOG_ERR, s, "Preparing statement '%s' failed: out of memory", SQL_BANNED_USER); PQfinish(cfg->conn); exit(1); } if (PQresultStatus(res) != PGRES_COMMAND_OK) { ap_log_error(APLOG_MARK, APLOG_ERR, s, "Preparing statement '%s' failed: %s", SQL_BANNED_USER, PQerrorMessage(cfg->conn)); PQclear(res); PQfinish(cfg->conn); exit(1); } 3) Do I have to PQclear(res) inbetween if I want to prepare another query? 4) How do I set the last PQprepare argument, the const Oid *paramTypes? The FAQ says an OID is a unique int. I'm confused how to use it here. For example I know that the argument to my prepared statement will be a string (a username). What is the OID then? I couldn't find any good examples with PQprepare() yet, does anybody please have a pointer to nice short examples? Regards Alex ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Basic questions about PQprepare()
Alexander Farber <[EMAIL PROTECTED]> writes: > 1) If PQconnectdb fails, do I still need to PQfinish the returned pointer? Yes, if you don't want to leak memory. > 2) Similar, if PQprepare fails, do I still need to PQclear its result? Yes, if you don't want to leak memory. > 3) Do I have to PQclear(res) inbetween if I want to prepare another query? They are not comparable actions --- you can do them in either order. In practice though I don't see why you wouldn't PQclear the result of a PREPARE as soon as you'd checked that it wasn't conveying an error. > 4) How do I set the last PQprepare argument, the const Oid *paramTypes? You'd need to look up the OIDs of the parameter types. In practice it's usally a lot easier to write the queries so that the parameter types can be inferred by the backend. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Basic questions about PQprepare()
Thank you Tom, I also should have reread the docs on 1) and 2) - it is mentioned there too that you have to call PQfinish and PQclear even on failed operations. Could you explain a bit more, where to get the OIDs? There are really not many PQprepare examples around (I wonder why, isn't it the fastest method?) Regards Alex On 1/28/06, Tom Lane <[EMAIL PROTECTED]> wrote: > Alexander Farber <[EMAIL PROTECTED]> writes: > > 4) How do I set the last PQprepare argument, the const Oid *paramTypes? > > You'd need to look up the OIDs of the parameter types. In practice it's > usally a lot easier to write the queries so that the parameter types can > be inferred by the backend. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Basic questions about PQprepare()
Alexander, On Sat, Jan 28, 2006 at 08:26:01PM +0100, Alexander Farber wrote: > Could you explain a bit more, where to get the OIDs? They are in the pg_type table, it is described here: http://www.postgresql.org/docs/8.1/static/catalog-pg-type.html You can select it like any other table: select oid, typname from pg_type; (You might want to restrict the list to non-composite types by adding a "where typrelid = 0") > There are really not many PQprepare examples > around (I wonder why, isn't it the fastest method?) It depends on the query complexity and in how often you want to execute the same query with different arguments. You're always welcome to improve the documentation by submitting a documentation patch that adds more examples ;-) Joachim -- Joachim Wieland [EMAIL PROTECTED] C/ Usandizaga 12 1°B ICQ: 37225940 20002 Donostia / San Sebastian (Spain) GPG key available ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Basic questions about PQprepare()
On Sun, Jan 29, 2006 at 06:03:41PM +0100, Joachim Wieland wrote: > On Sat, Jan 28, 2006 at 08:26:01PM +0100, Alexander Farber wrote: > > Could you explain a bit more, where to get the OIDs? > > They are in the pg_type table, it is described here: > > http://www.postgresql.org/docs/8.1/static/catalog-pg-type.html For standard types you could include server/catalog/pg_type.h and use TEXTOID, INT4OID, etc. But as Tom Lane mentioned, if the query is written so the backend can infer the types then you don't need to bother. -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Basic questions about PQprepare()
on 1/29/06 8:00 PM, [EMAIL PROTECTED] purportedly said: > On Sun, Jan 29, 2006 at 06:03:41PM +0100, Joachim Wieland wrote: >> On Sat, Jan 28, 2006 at 08:26:01PM +0100, Alexander Farber wrote: >>> Could you explain a bit more, where to get the OIDs? >> >> They are in the pg_type table, it is described here: >> >> http://www.postgresql.org/docs/8.1/static/catalog-pg-type.html > > For standard types you could include server/catalog/pg_type.h and > use TEXTOID, INT4OID, etc. But as Tom Lane mentioned, if the query > is written so the backend can infer the types then you don't need > to bother. This thread has particular interest to me as well--the libpq documentation and examples seem to imply that the backend will consider all parameters to be string literals, although postgres is smart enough to perform conversions. However, there was an issue (perhaps no longer in 8.x) that in cases of implicit conversions the planner would have the tendency to perform sequential searches when otherwise (i.e. when the type is more explicit) it would do an index search. My question, then: is this still the case and if so, is there another way to inform the planner of the type such as explicit type casts? I guess I am wondering more specifically what "if the query is written so the backend can infer the types" means. Thanks, Keary Suska Esoteritech, Inc. "Demystifying technology for your home or business" ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Basic questions about PQprepare()
Keary Suska <[EMAIL PROTECTED]> writes: > This thread has particular interest to me as well--the libpq documentation > and examples seem to imply that the backend will consider all parameters to > be string literals, although postgres is smart enough to perform > conversions. No, not a "string literal" exactly --- simple quoted literals are initially taken as being of *unknown* type, as are parameter symbols that haven't had a specific type assigned. While it's true that the parser will often assign unknown literals type "text" if it has no other way to infer a type, that's a last resort. Commonly a unknown literal gets resolved as being the same type as whatever it is compared to or otherwise first used with. Here are a couple of examples: regression=# select 42 = '42'; ?column? -- t (1 row) regression=# select 42 = 'foo'; ERROR: invalid input syntax for integer: "foo" regression=# select 42::numeric = 'foo'; ERROR: invalid input syntax for type numeric: "foo" regression=# select 42 = '42.0'; ERROR: invalid input syntax for integer: "42.0" The parser would similarly have assumed that the unknown literal was integer or numeric if the operator were, say, + rather than =. The contents of the string are not examined at all until the type decision is made, which is why 'foo' and '42' are treated the same. > However, there was an issue (perhaps no longer in 8.x) that in > cases of implicit conversions the planner would have the tendency to perform > sequential searches when otherwise (i.e. when the type is more explicit) it > would do an index search. Implicit conversions never had anything to do with it. The weak spot of the index system is that *cross type* comparisons were unindexable until recently. So if you wrote something like WHERE id = 42 this would be indexable if id was int4 (like the constant) but not if it was int8 or numeric. That problem actually goes away if you use quoted literals or parameters: WHERE id = '42' WHERE id = $1 In either case, the literal or parameter will be resolved as being the same type as "id", and so you don't risk creating an unindexable comparison. So at least for examples like this, it's *better* not to specify a type for the parameter. As of 8.0, btree indexes can handle cross-type comparisons, at least for most of the common cases, so this isn't as big a deal as it once was. But it's still true that you should usually avoid specifying parameter types unless you have to. "Have to" means you get an error like this: regression=# select - '42'; ERROR: operator is not unique: - "unknown" HINT: Could not choose a best candidate operator. You may need to add explicit type casts. regression=# Here the parser is punting because it hasn't any good way to guess which numeric type to impute to the literal. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org