Dear All,
I have recently been converting some old code to use $n parameter placeholders,
rather than inlining the values in the query string. It has mostly
gone well, but
I have encountered some odd behaviour with a temporary view. I'm using
libpq to
talk to an 8.1.0 server.
This is the old code which works:
create or replace temporary view u_messages as
select * from messsages where owner='phil';
select msg_id from u_messages
where msgdate>'2007-02-11 21:36:43.886004+00'::timestamp with
time zone
- '1 day'::interval
order by msg_id;
I converted the first query like this:
create or replace temporary view u_messages as
select * from messsages where owner=$1;
I run this with "phil" as the value for $1. This completes without
error. Then I run the second, unchanged, query. It does not have any $n
placeholders, but I get an error:
ERROR: no value found for parameter 1
So I think it is complaining that I have not supplied enough parameters
even though
none are needed.
I have used strace to look at the communication with the backend:
send(4, "P\0\0\0\223\0select msg_id from u_messages where msgdate > \'2007-02-11
21:36:43.886004+00\'::timestamp with time zone - \'1 day\'::interval
order by
msg_id\0\0\0B\0\0\0\16\0\0\0\0\0\0\0\1\0\1D\0\0\0\6P\0E\0\0\0\t\0\0\0\0\0S\0\0\0\4",
185, 0) = 185
Which I have broken down as follows:
P \0\0\0\223 \0 select msg_id from u_messages where msgdate > \'2007-02-11
21:36:43.886004+00\'::timestamp with time zone - \'1 day\'::interval
order by
msg_id\0 \0\0
B \0\0\0\16 \0 \0 \0\0 \0\0 \0\1 \0\1
D \0\0\0\6 P \0
E \0\0\0\t \0 \0\0\0\0
S \0\0\0\4
That looks OK.
recv(4, "1\0\0\0\0042\0\0\0\4T\0\0\0\37\0\1msg_id\0\0\2\20\224\0\1\0\0\0\27\0\4
\377\377\377\377\0\1E\0\0\0OSERROR\0C42704\0Mno value found for
parameter 1\0
Fparams.c\0L120\0RlookupParam\0\0", 16384, 0) = 122
1 \0\0\0\004 (Parse complete)
2 \0\0\0\4 (Bind complete)
T \0\0\0\37 \0\1 msg_id\0 \0\2\20\224 \0\1 \0\0\0\27 \0\4
\377\377\377\377 \0\1
E \0\0\0O S ERROR\0 C 42704\0 M no value found for parameter 1\0
F params.c\0 L 120\0 R lookupParam\0 \0
Also OK I think, apart from the unexpected error. Is it normal to get
the 'T' line
before the error?
For completeness here is the earlier communication to create the
temporary view:
P \0\0\0` \0 create or replace temporary view u_messages as select * from
messages where owner=$1\0 \0\1 \0\0\0\31
B \0\0\0\30 \0 \0 \0\1 \0\0 \0\1 \0\0\0\4 phil \0\1 \0\1
D \0\0\0\6 P \0
E \0\0\0\t \0 \0\0\0\0
S \0\0\0\4
and the response:
1 \0\0\0\004
2 \0\0\0\4
n \0\0\0\4
C \0\0\0\20 CREATE VIEW\0
Z \0\0\0\5 I
It's as if the server has been confused by the presence of the $1 in
the view
query and has tried to substitute a value for it from those supplied
when the view
is used. I know next to nothing about the internals but I could
imagine something
like this happening since it must expand the query to include the view
at some
point.
Maybe I'm not allowed to use $n in "create view" queries at all?
Unfortunately there must be a bit more to it than this as the simple
test case
that I have prepared does not fail. I will investigate further. But I thought
I'd post now in case anyone is aware of any known issues in this area,
or has any
suggestions.
Unrelated to this, it used to be true that recreating temporary tables (and
presumably views) would confuse stored procedures (and presumably prepared
statements) that had cached some sort of pointer to the old instance of
the table
(or view). See for example this thread:
http://archives.postgresql.org/pgsql-general/2004-09/msg00316.php
Is this still true? (This isn't the cause of the problems above as it
doesn't get
anywhere near running any queries for a second time, but I will need to re-visit
this issue.)
Cheers,
Phil.
(You are welcome to CC: me in any replies, so I'll see them more quickly.)
