Re: Re: SET variables - Mailing list pgsql-hackers

At 11:05 AM 5/13/01 -0400, Tom Lane wrote:
>mlw <markw@mohawksoft.com> writes:
>> Obviously this is a very simple example. I guess I am asking for something
>> analogous to temporary tables, but on a single datum level.
>
>What's wrong with a one-row temporary table?

Well, the following query would then be a join with that one-row table, which
means that you pay the cost of creating the temporary table, optimizing the
join (which presumably isn't terribly bad since it's a trivial one), etc.

This might not be bad if this were something done rarely.   But people in
the Oracle world use BIND variables a LOT (and essentially BIND vars are
what are being asked for).  As was pointed out, the use of BIND variables
make Oracle's brain-dead query caching useful (it does source caching, and
without BIND variables "select * from foo where foo_key = 1" doesn't match
"select * from foo where foo_key = 2", making caching not all that useful).

That's not the only reason to use them, though.

There are literally tens of thousands of them in OpenACS.  We had to work around
the fact that PG doesn't offer this capability by hacking the AOLserver driver.
If we were working in an application that we didn't control at every level (i.e.
a closed-source webserver environment with a closed-source driver) the workaround
you suggest would involve the creation and deletion of tens of thousands of 
temporary tables on a busy website.

Not a very scalable workaround in my world ... obviously rewriting the application
to remove BIND variables would be the solution we would've chosen if we hadn't
been able to hack the functionality into the driver.  One reason for the 
heavy use of BIND variables in the ACS is that you then get type checking in
the query, so removing them would require extensive type checking within the
application code before submitting dynamic queries to the database to help avoid
the "smuggled SQL" problem.  (SQL snippets smuggled in via URL arguments).

Our driver hack was able to provide the same safeguards against "smuggled SQL"
so again, full control over our enviroment means we can live easily without BIND
vars.

But it's easy for me to see why folks want them.  

This reminds me a bit of the argument against incorporating the patch implementing
the Oracle parameter type mechanism.  Folks with a lot of experience with PL/SQL
will just scratch their heads bemusedly when they read an statement saying "I don't
really see that many people would write functions like this", etc.  This patch would
greatly simplify the mechanized translation of PL/SQL into PL/pgSQL, even if the
feature per se is "useless" (which I happen to disagree with).   It's not uncommon
for large Oracle applications to include thousands of PL/SQL procedures and functions,
since many subscribe to the notion that application logic should reside entirely
within the database if possible.  So mechanical translation has a certain attraction
to the person wanting to port a large-scale application from Oracle to PG.

The interesting thing to me doesn't simply lie in the debate over this or that feature.
The interesting thing to me is that more and more requests to ease porting from Oracle
to Postgres are cropping up.  

This says that more and more people from the "real" RDBMS world are starting to take
Postgres seriously.



- Don Baccus, Portland OR <dhogaza@pacifier.com> Nature photos, on-line guides, Pacific Northwest Rare Bird Alert
Serviceand other goodies at http://donb.photo.net.