* Server restart and assorted like failover (you need to redo a
global prepare).
Hmm? He's proposing storing the info in a system catalog. That hardly
seems "volatile"; it'll certainly survive a server restart.
Yes, it's in a system catalog.
I agree with the point that this isn't completely transparent to
applications, but if an app is already using named prepared statements
it would surely be a pretty small matter to make it use this feature.
The app code would likely get simpler instead of more complex, since
you'd stop worrying about whether a given statement had been prepared
yet in the current session.
Thanks. That was the idea behing this hack...
I'm having a problem with the terminology here, since AFAICT what your
patch does is exactly not a global "prepare" --- there is no permanently
stored cached plan. That's a good thing probably, but it seems like
the feature needs to be described differently.
Sure, but I couldn't come up with a suitable name at the time... perhaps
CREATE STATEMENT (and DROP STATEMENT) ? This would describe it better
(especially the DROP, because GLOBAL DEALLOCATE is a rather bad name,
since it doesn't actually deallocate anything...)
I'm also pretty dubious about storing raw text in that catalog. In the
first place, while I've not looked at your patch, I expect you are
pulling the raw text from debug_query_string. That won't work in cases
where multiple SQL commands were submitted in one query string.
LOL, you are right, I had tested with multiple queries on the same line
from psql, but psql apparently splits the queries, when I feed multiple
queries from PHP, one of them being GLOBAL PREPARE, it fails.
In the
second place, raw-text SQL commands will be subject to a whole lot of
ambiguity at parse time. If for instance another session tries to use
the command with a different search_path or standard_conforming_string
setting, it'll get different results. While I can think of use-cases
for that sort of behavior, it seems like mostly a bad idea.
You're right.
I'm thinking that a more appropriate representation would use stored
parse trees, the same as we do in pg_rewrite, and with the same
dependency information so that a stored statement couldn't outlive the
objects it depends on.
Do the parse tree store fully qualified "schema.table" or
"schema.function" ?
I mean, if table T is mentioned in a parse tree which is stored, and the
table is later dropped and recreated... or a column dropped... what
happens ? Dropping the statement would seem more logical, since it would
probably no longer be valid...
Another area that could do with more thought is the hard-wired
association between statement ownership and accessibility. That's
likely to be pretty inconvenient in a lot of cases, particularly
systems that use role membership heavily.
Yes, need to think about that.
I also wonder whether statements should belong to schemas...
Since they are basically an extremely simple form of a function, why
not ?
(but since part of the goodness on prepared statements is that they are
stored in a fast hash cache, wouldn't that add too much overhead ?)
Thanks for the helpful advice.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
