
Would it be possible to allocate varibles that can be addressed with SET?

CREATE [TEMP] VARIABLE fubar ;

set FUBAR=5 ;




-- 
I'm not offering myself as an example; every life evolves by its own laws.
------------------------
http://www.mohawksoft.com

Sat, 12 May 2001 11:19:43 -0400
mlw <markw@mohawksoft.com>
SET variables
mlw <markw@mohawksoft.com> writes:
> Would it be possible to allocate varibles that can be addressed with SET?

And what would you do with them?

There is a simple variable facility in psql these days, if that helps.

			regards, tom lane

Sat, 12 May 2001 23:22:58 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: SET variables 
Tom Lane wrote:

> mlw <markw@mohawksoft.com> writes:
> > Would it be possible to allocate varibles that can be addressed with SET?
>
> And what would you do with them?
>
> There is a simple variable facility in psql these days, if that helps.g

I was thinking more like:

create variable fubar ;

set fubar = select max(column) from table;

select * from table where column = :fubar;

Obviously this is a very simple example. I guess I am asking for something
analogous to temporary tables, but on a single datum level.

I like the way psql does it, but it would be better to have this available in
the native query language.

This is similar to a feature which Oracle has. It is mainly to avoid hitting
the query planner. Oracle caches query execution instructions, and using a
variable is a way to reuse cached queries with different data.

Being able to set variables and use them in queries may help some people port
from Oracle to Postgres.

BTW I am also working on the impression that a view is more efficient than
reissuing a complex query. Or is there no difference?

>
>
>                         regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl


Sun, 13 May 2001 08:31:25 -0400
mlw <markw@mohawksoft.com>
Re: SET variables
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?

			regards, tom lane

Sun, 13 May 2001 11:05:51 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: SET variables 
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 Service and other goodies at
  http://donb.photo.net.

Sun, 13 May 2001 08:33:19 -0700
Don Baccus <dhogaza@pacifier.com>
Re: Re: SET variables 
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?
> 
The syntax would be too different than oracle.

select * from table where field = :var

If it is fairly easy to do, it would be very helpful for would be porting.


-- 
42 was the answer, 49 was too soon.
------------------------
http://www.mohawksoft.com

Sun, 13 May 2001 12:31:33 -0400
mlw <markw@mohawksoft.com>
Re: SET variables
Don Baccus wrote:

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

Speaking for myself, I think Larry has enough money. The costs of Oracle are
astounding. As I see it, I think Postgres could be the "single server" answer
to the sky high per processor licensing that Oracle has.

A Postgres with enough Oracle-isms would be a world beater. As it is, when I
show Oracle people what Postgres can do, they are blown away. They love the
fact that temporary tables are in an isolated name space, sequences are more
flexible, and a lot of the other neat features.

If we could do:
select * from database.table.field where database.table.field =
localtable.field;
select * from table where field = :var;

and not have to vacuum

Postgres would be incredible. As it is, it is a great database. If it could
have features which make Oracle people comfortable it would be a very serious
alternative to Oracle. Companies like Greatbridge and PostgreSQL inc. would
have a much easier sell.



-- 
42 was the answer, 49 was too soon.
------------------------
http://www.mohawksoft.com

Sun, 13 May 2001 12:45:20 -0400
mlw <markw@mohawksoft.com>
Re: SET variables
At 12:45 PM 5/13/01 -0400, mlw wrote:

>A Postgres with enough Oracle-isms would be a world beater.

No doubt.  I'm not as extremist as Philip Greenspun has been in the past
regarding how far PG should go in implementing Oracle-isms, though.  His
stated opinion in the past was that PG should implement Oracle's wretched
date arithmetic (which he recognizes is wretched) rather than stick with
SQL92 date and timestamp types (which he recognizes is superior).  I'd
oppose that.

So "oracle-isms" should be inspected for merit, that's for sure.  The
inclusion of "to_char" and friends not only helped people port from Oracle
to PG but is useful on its own.  

I'd put both BIND vars and the enhanced types in parameter lists (which
we already have in PL/pgSQL var decls) in that class.

There are a lot of other features I'd question, though.  "CONNECT BY" is
difficult to work around because there's no simplistic way to implement
hierarchical queries in straight SQL92, but the solutions in SQL92 tend
to scale a lot better and be more general.  So I'd argue against putting
much effort into "CONNECT BY", or at least putting it at a high priority,
which would probably put me at odds with quite a few Oracle users.

>Postgres would be incredible. As it is, it is a great database. If it could
>have features which make Oracle people comfortable it would be a very serious
>alternative to Oracle. Companies like Greatbridge and PostgreSQL inc. would
>have a much easier sell.

There are actually very few gratuitous features in Oracle - the company's
very, very customer driven.   Most of the really horrible differences from
standard SQL92 - date arithmetic, empty string is the same as NULL in DML
statements - are there for historical reasons, i.e. they predate SQL
standardization and Oracle's found it self locked-in/boxed-in by acres of
existing customer code.



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

Sun, 13 May 2001 10:28:08 -0700
Don Baccus <dhogaza@pacifier.com>
Re: SET variables
> A Postgres with enough Oracle-isms would be a world beater. As it is, when I
> show Oracle people what Postgres can do, they are blown away. They love the
> fact that temporary tables are in an isolated name space, sequences are more

Are you saying that multiple people can't create temp tables with the
same name, or that you can't create a temp table that masks a real
table?  I know PostgreSQL does both.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Sun, 13 May 2001 17:16:03 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Re: SET variables
> In Oracle, temp tables occupy the same name space. One can not have two or more
> users with the same temp table name without it being the same table. This is
> why temp tables are not as used as one would think in Oracle.
> 
> To use a temp table in Oracle you have to some up with some random naming
> scheme. It really blows. Because of this Oracle developers have long stayed
> away from temp tables.

Wow, that really does stink.  I know Informix can't have a temp table
with the same name as a real table, and I thought that was bad, but
Oracle is much worse.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Sun, 13 May 2001 17:29:12 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: SET variables
Bruce Momjian wrote:
> 
> > A Postgres with enough Oracle-isms would be a world beater. As it is, when I
> > show Oracle people what Postgres can do, they are blown away. They love the
> > fact that temporary tables are in an isolated name space, sequences are more
> 
> Are you saying that multiple people can't create temp tables with the
> same name, or that you can't create a temp table that masks a real
> table?  I know PostgreSQL does both.

In Oracle, temp tables occupy the same name space. One can not have two or more
users with the same temp table name without it being the same table. This is
why temp tables are not as used as one would think in Oracle.

To use a temp table in Oracle you have to some up with some random naming
scheme. It really blows. Because of this Oracle developers have long stayed
away from temp tables.


-- 
42 was the answer, 49 was too soon.
------------------------
http://www.mohawksoft.com

Sun, 13 May 2001 17:31:28 -0400
mlw <markw@mohawksoft.com>
Re: SET variables
Bruce Momjian wrote:
> 
> > In Oracle, temp tables occupy the same name space. One can not have two or more
> > users with the same temp table name without it being the same table. This is
> > why temp tables are not as used as one would think in Oracle.
> >
> > To use a temp table in Oracle you have to some up with some random naming
> > scheme. It really blows. Because of this Oracle developers have long stayed
> > away from temp tables.
> 
> Wow, that really does stink.  I know Informix can't have a temp table
> with the same name as a real table, and I thought that was bad, but
> Oracle is much worse.
>

Although....

One can see the advantage in a globally shared temporary table. For instance,
something like user web session management. One can insert and update against
the temp table and never have to worry about disk I/O or vacuuming. (Assuming a
temp table is implemented as a memory buffer)



-- 
42 was the answer, 49 was too soon.
------------------------
http://www.mohawksoft.com

Sun, 13 May 2001 18:45:10 -0400
mlw <markw@mohawksoft.com>
Re: SET variables
> > Wow, that really does stink.  I know Informix can't have a temp table
> > with the same name as a real table, and I thought that was bad, but
> > Oracle is much worse.
> >
> 
> Although....
> 
> One can see the advantage in a globally shared temporary table. For instance,
> something like user web session management. One can insert and update against
> the temp table and never have to worry about disk I/O or vacuuming. (Assuming a
> temp table is implemented as a memory buffer)

Yes, but having a temp table never hit disk is a different issue from
its visibility.  We could eventually implement the memory-only feature
if we wanted to.  Right now, we have it dumping to disk as a backing
store for the table, assuming it wouldn't fit all in memory.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Sun, 13 May 2001 20:16:07 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: SET variables