Re: [HACKERS] Couple of issues with prepared FETCH commands
On Wed, Jan 11, 2017 at 11:28 PM, Tom Lane wrote: > Robert Haas writes: >> On Tue, Jan 10, 2017 at 5:38 PM, Andrew Gierth >> 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
Re: [HACKERS] Couple of issues with prepared FETCH commands
Robert Haas writes: > On Tue, Jan 10, 2017 at 5:38 PM, Andrew Gierth > 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
Re: [HACKERS] Couple of issues with prepared FETCH commands
> "Robert" == Robert Haas 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
Re: [HACKERS] Couple of issues with prepared FETCH commands
On Tue, Jan 10, 2017 at 5:38 PM, Andrew Gierth 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
[HACKERS] 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