Couple of issues with prepared FETCH commands
(This came up on IRC, but I'm not sure to what extent it should be
considered a "bug")
If you do PQprepare(conn, "myfetch", "FETCH ALL FROM mycursor", ...);
then the results are unpredictable in two ways:
Firstly, nothing causes the plancache entry to be revalidated just
because "mycursor" got opened with a different query, so the result type
can change between uses. This could be considered a "caveat user" case,
though, and I can't find anything that actually breaks.
But the problem that actually came up is this: if you do the PQprepare
before the named cursor has actually been opened, then everything works
_up until_ the first event, such as a change to search_path, that forces
a revalidation; and at that point it fails with the "must not change
result type" error _even if_ the cursor always has exactly the same
result type. This happens because the initial prepare actually stored
NULL for plansource->resultDesc, since the cursor name wasn't found,
while on the revalidate, when the cursor obviously does exist, it gets
the actual result type.
It seems a bit of a "gotcha" to have it fail in this case when the
result type isn't actually being checked in other cases.
(In the reported case, search_path was actually changing due to the
creation of a temp table, so there was a certain amount of
spooky-action-at-a-distance to figure out in order to locate the
problem.)
--
Andrew (irc:RhodiumToad)
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Tue, Jan 10, 2017 at 5:38 PM, Andrew Gierth
<andrew@tao11.riddles.org.uk> wrote:
But the problem that actually came up is this: if you do the PQprepare
before the named cursor has actually been opened, then everything works
_up until_ the first event, such as a change to search_path, that forces
a revalidation; and at that point it fails with the "must not change
result type" error _even if_ the cursor always has exactly the same
result type. This happens because the initial prepare actually stored
NULL for plansource->resultDesc, since the cursor name wasn't found,
while on the revalidate, when the cursor obviously does exist, it gets
the actual result type.It seems a bit of a "gotcha" to have it fail in this case when the
result type isn't actually being checked in other cases.
To me, that sounds like a bug.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
"Robert" == Robert Haas <robertmhaas@gmail.com> writes:
But the problem that actually came up is this: if you do the
PQprepare before the named cursor has actually been opened, then
everything works _up until_ the first event, such as a change to
search_path, that forces a revalidation; and at that point it fails
with the "must not change result type" error _even if_ the cursor
always has exactly the same result type. This happens because the
initial prepare actually stored NULL for plansource->resultDesc,
since the cursor name wasn't found, while on the revalidate, when
the cursor obviously does exist, it gets the actual result type.It seems a bit of a "gotcha" to have it fail in this case when the
result type isn't actually being checked in other cases.
Robert> To me, that sounds like a bug.
So what's the appropriate fix? My suggestion would be to suppress the
result type check entirely for utility statements; EXPLAIN and SHOW
always return the same thing anyway, and both FETCH and EXECUTE are
subject to the issue described. This would mean conceding that the
result descriptor of a prepared FETCH or EXECUTE might change (i.e. a
Describe of the statement might not be useful, though a Describe of an
opened portal would be ok). I think this would result in the most
obviously correct behavior from the client point of view.
--
Andrew (irc:RhodiumToad)
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Robert Haas <robertmhaas@gmail.com> writes:
On Tue, Jan 10, 2017 at 5:38 PM, Andrew Gierth
<andrew@tao11.riddles.org.uk> wrote:But the problem that actually came up is this: if you do the PQprepare
before the named cursor has actually been opened, then everything works
_up until_ the first event, such as a change to search_path, that forces
a revalidation; and at that point it fails with the "must not change
result type" error _even if_ the cursor always has exactly the same
result type. This happens because the initial prepare actually stored
NULL for plansource->resultDesc, since the cursor name wasn't found,
while on the revalidate, when the cursor obviously does exist, it gets
the actual result type.It seems a bit of a "gotcha" to have it fail in this case when the
result type isn't actually being checked in other cases.
To me, that sounds like a bug.
Yeah --- specifically, I wonder why we allow the reference to an
unrecognized cursor name to succeed. Or were you defining the bug
differently?
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On Wed, Jan 11, 2017 at 11:28 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Robert Haas <robertmhaas@gmail.com> writes:
On Tue, Jan 10, 2017 at 5:38 PM, Andrew Gierth
<andrew@tao11.riddles.org.uk> wrote:But the problem that actually came up is this: if you do the PQprepare
before the named cursor has actually been opened, then everything works
_up until_ the first event, such as a change to search_path, that forces
a revalidation; and at that point it fails with the "must not change
result type" error _even if_ the cursor always has exactly the same
result type. This happens because the initial prepare actually stored
NULL for plansource->resultDesc, since the cursor name wasn't found,
while on the revalidate, when the cursor obviously does exist, it gets
the actual result type.It seems a bit of a "gotcha" to have it fail in this case when the
result type isn't actually being checked in other cases.To me, that sounds like a bug.
Yeah --- specifically, I wonder why we allow the reference to an
unrecognized cursor name to succeed. Or were you defining the bug
differently?
I'm not sure whether that's a bug or not. What I was defining as a
bug is calling a change from "we don't know what the result type will
be" to "we know that the result type will be X" as a change in the
result type. That's really totally inaccurate.
I've never really understood errors about changing the result type.
As a user, I assumed those were unavoidable implementation artifacts,
on the theory that they were annoying and therefore the developers
would have eliminated such messages had it been practical. As a
developer, I've never gotten around to understanding whether that
theory was correct.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers