Thread: SET variables
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
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
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
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
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.
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
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
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 Serviceand other goodies at http://donb.photo.net.
> 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, Pennsylvania19026
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
> 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, Pennsylvania19026
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
> > 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, Pennsylvania19026