Thread: 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
Alexander Farber <alexander.farber@gmail.com> 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
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 <tgl@sss.pgh.pa.us> wrote: > Alexander Farber <alexander.farber@gmail.com> 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.
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 joe@mcknight.de C/ Usandizaga 12 1°B ICQ: 37225940 20002 Donostia / San Sebastian (Spain) GPG key available
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
on 1/29/06 8:00 PM, mike@fuhr.org 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"
Keary Suska <hierophant@pcisys.net> 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