Skip site navigation (1) Skip section navigation (2)

Re: first time hacker ;) messing with prepared statements

From: PFC <lists(at)peufeu(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Cc: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Subject: Re: first time hacker ;) messing with prepared statements
Date: 2008-03-31 13:07:54
Message-ID: op.t8vtfgeocigqcu@apollo13.peufeu.com (view raw or flat)
Thread:
Lists: pgsql-hackers
>>     * 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.

In response to

Responses

pgsql-hackers by date

Next:From: Aidan Van DykDate: 2008-03-31 13:25:14
Subject: Re: Status of GIT mirror (Was having problem in rsync'ing cvs)
Previous:From: Pavel StehuleDate: 2008-03-31 12:48:28
Subject: WIP: CASE statement for PL/pgSQL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group