Re: first time hacker ;) messing with prepared statements

>> * 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.