Thread

LinuxTag wrapup

Andreas Pflug <pgadmin@pse-consulting.de> — 2004-07-03T15:59:17Z

Dear developers,

classifying the questions we got those three days in the PostgreSQL 
booth on LinuxTag, we had three ever repeating topics, two of them 
non-surprising:
- what's the difference to MyS***
- what about win32 native
- what about Oracle portability.

The third question was asked from serious corporate users, and what I 
told them about ora2pg and simple procedure migration didn't satisfy 
them completely: they asked about oracle sql query syntax compatibility. 
They were quite disappointed when I told them we're ansi standard and 
after numerous discussions we don't ever intend to implement that oracle 
stuff.

IMHO we should rethink if we could make those people happy. How about a 
loadable personality (IIRC SAPDB has something like that), to exchange 
the parser in use with a custom one (by a SET command)? This way we have 
a pure ansi default, while enabling a way so someone could contribute an 
oracle style parser.

Regards,
Andreas

BTW, many people I addressed when they rested for a few seconds in front 
of the booth just said "no thanks, I don't have any questions, I'm using 
PostgreSQL and I'm happy with it".

Re: LinuxTag wrapup

Dennis Björklund <db@zigo.dhs.org> — 2004-07-03T16:43:47Z

On Sat, 3 Jul 2004, Andreas Pflug wrote:

> IMHO we should rethink if we could make those people happy. How about a 
> loadable personality (IIRC SAPDB has something like that), to exchange 
> the parser in use with a custom one (by a SET command)?

Having two parsers would be a nightmare to maintain.

If anything one could have one parser that handles oracle syntax and give
errors on such constructs unless some variable is set.

The question is how much of the problems that are pure syntax and what 
needs deeper changes. My guess is that just changing some syntax will not 
be enough to make many oracle program work.

> BTW, many people I addressed when they rested for a few seconds in front 
> of the booth just said "no thanks, I don't have any questions, I'm using 
> PostgreSQL and I'm happy with it".

Then they probably just wanted to chat but didn't know how to start a 
conversation. A true geek problem?!? :-)

-- 
/Dennis Björklund

Re: LinuxTag wrapup

Tom Lane <tgl@sss.pgh.pa.us> — 2004-07-03T17:04:25Z

Andreas Pflug <pgadmin@pse-consulting.de> writes:
> - what about Oracle portability.

> IMHO we should rethink if we could make those people happy. How about a 
> loadable personality (IIRC SAPDB has something like that), to exchange 
> the parser in use with a custom one (by a SET command)? This way we have 
> a pure ansi default, while enabling a way so someone could contribute an 
> oracle style parser.

How about an external tool that helps in translating apps to
SQL-standard syntax?  Oracle does accept the standard syntax after all.
That way we are truly helping people liberate themselves: they can
switch to any SQL-compliant database, not only Postgres.

			regards, tom lane

Re: LinuxTag wrapup

Andreas Pflug <pgadmin@pse-consulting.de> — 2004-07-03T17:15:55Z

Dennis Bjorklund wrote:

>
>Having two parsers would be a nightmare to maintain.
>  
>
Probably. It just came to my mind because one visitor mentioned he would 
look at the bison stuff to do it himself. I meant to enable him to do so 
if he likes (and can) without hacking the core product.

>If anything one could have one parser that handles oracle syntax and give
>errors on such constructs unless some variable is set.
>
>The question is how much of the problems that are pure syntax and what 
>needs deeper changes. My guess is that just changing some syntax will not 
>be enough to make many oracle program work.
>
>  
>
That's true, it's the question how much can be offered without too much 
effort.
I'm not too deep in oracle stuff, what comes to my mind is
- outer join syntax (parser thing)
- sequences usage (parser too)
- maybe stored procedure call, with a wrapper to convert output 
parameters to a composite return value.

There's certainly no point supporting any weird ddl command, so there's 
still porting work to be done when migrating.

Regards,
Andreas

Re: LinuxTag wrapup

Andreas Pflug <pgadmin@pse-consulting.de> — 2004-07-03T17:31:05Z

Tom Lane wrote:

>Andreas Pflug <pgadmin@pse-consulting.de> writes:
>  
>
>>- what about Oracle portability.
>>    
>>
>
>  
>
>>IMHO we should rethink if we could make those people happy. How about a 
>>loadable personality (IIRC SAPDB has something like that), to exchange 
>>the parser in use with a custom one (by a SET command)? This way we have 
>>a pure ansi default, while enabling a way so someone could contribute an 
>>oracle style parser.
>>    
>>
>
>How about an external tool that helps in translating apps to
>SQL-standard syntax?  Oracle does accept the standard syntax after all.
>That way we are truly helping people liberate themselves: they can
>switch to any SQL-compliant database, not only Postgres.
>

Nice idea, but
- sources might not be accessible
- sources might not be easily readable (esp. if not embedded sql, 
example pgadmin) or created dynamically.
- probably too many non-ansi compliant servers (i.e. pre-9) still in use.

Regards,
Andreas

Re: LinuxTag wrapup

Tom Lane <tgl@sss.pgh.pa.us> — 2004-07-03T17:50:30Z

Andreas Pflug <pgadmin@pse-consulting.de> writes:
>> How about an external tool that helps in translating apps to
>> SQL-standard syntax?  Oracle does accept the standard syntax after all.

> Nice idea, but
> - sources might not be accessible
> - sources might not be easily readable (esp. if not embedded sql, 
> example pgadmin) or created dynamically.
> - probably too many non-ansi compliant servers (i.e. pre-9) still in use.

Well, I am certainly *not* buying into a goal of "support any
application that has worked with any version of Oracle with zero source
code changes".  As Dennis already pointed out, the syntax is just the
tip of the iceberg.  (Look for instance at the thread on pgsql-bugs
yesterday, where we concluded that Oracle 8 thinks the way to interpret
"WHERE charcolumn = intconstant" is to cast the column to integer.
Talk about bizarre choices...)

If we bought into such a goal, even partially, we'd stop making forward
progress on our own issues and spend all our time hashing over Oracle
compatibility choices.

The plain fact is that users who want to migrate off Oracle are going
to have to take significant responsibility for porting their own apps,
the more so the more they depended on non-standard constructs.
We can perhaps help them with tools, but if they want a zero-effort
solution they are out of luck.

			regards, tom lane

Re: LinuxTag wrapup

Jeroen Vermeulen <jtv@xs4all.nl> — 2004-07-03T18:34:18Z

On Sat, Jul 03, 2004 at 05:59:17PM +0200, Andreas Pflug wrote:

> classifying the questions we got those three days in the PostgreSQL 
> booth on LinuxTag, we had three ever repeating topics, two of them 
> non-surprising:
> - what's the difference to MyS***
> - what about win32 native
> - what about Oracle portability.

That about covers the important stuff.  Some more for the "other" bucket
(although they all came repeatedly):

 - so how do I pronounce "Postgre"?
 - will it support my performance requirements?
 - are you a company?  Can you tell me someone who is?
 - have a job for me?
 - do you have drivers for Kylix?
 - why don't you support <product>?
 - what client GUI programming environment do you offer?

On the "Postgre" point, I remarked to some friendly people (who are
developing a content management system based on postgres, by the way)
that we ought to have something like "just call me Postgres" posters in
our booth.  It turned out they had the gear to cut stickers in letter
shapes, so a little while later we actually had those words plastered
over our booth walls.  I think we got most interested passers-by before
they had a chance to read it, though.

On the last points I eventually learned to stop answering and shoot back
the question instead: "what, doesn't yours support ODBC?"

In particular, X.org's Leon Shiman felt that we Postgres people should be
especially interested in their work on X.  I didn't even see what he was
getting at until he mentioned GUI builders.  Again, I told him that my
personal conviction is that those should be database-agnostic and the very
idea that these should be bundled with database servers is a by-product of
the need to sell proprietary database licenses, and that any good free GUI
builder should build on GUI toolkits rather than on raw X, etc.

But like I said, that's just my personal conviction.  I definitely think
people in our community ought to be willing to work together with the
MySQL people, the FireBird people and anybody else in the free world to
have world-class GUI development tools; it should be a rising tide that
raises all boats.  If anyone feels differently, I did make it perfectly
clear that I wasn't speaking for anyone.

Of course one area where we should care about X, but I completely forgot
to mention this to Leon, is that modern graphics hardware can be used to
speed up database engines.  Hardware detection of collisions or overlaps,
for instance, has been shown to be a viciously effective filter for
spatial joins in GIS databases.  But that's another story!

Jeroen

Re: LinuxTag wrapup

Gavin Sherry <swm@linuxworld.com.au> — 2004-07-04T00:31:33Z

On Sat, 3 Jul 2004, Tom Lane wrote:

> Andreas Pflug <pgadmin@pse-consulting.de> writes:
> > - what about Oracle portability.
>
> > IMHO we should rethink if we could make those people happy. How about a
> > loadable personality (IIRC SAPDB has something like that), to exchange
> > the parser in use with a custom one (by a SET command)? This way we have
> > a pure ansi default, while enabling a way so someone could contribute an
> > oracle style parser.
>
> How about an external tool that helps in translating apps to
> SQL-standard syntax?  Oracle does accept the standard syntax after all.
> That way we are truly helping people liberate themselves: they can
> switch to any SQL-compliant database, not only Postgres.

I totally agree. After all, oracle provides such tools to their customers.

Gavin

Re: LinuxTag wrapup

Mike Mascari <mascarm@mascari.com> — 2004-07-04T03:33:35Z

Jeroen T. Vermeulen wrote:
> That about covers the important stuff.  Some more for the "other" bucket
> (although they all came repeatedly):
> 
>  - so how do I pronounce "Postgre"?

...

> On the "Postgre" point, I remarked to some friendly people (who are
> developing a content management system based on postgres, by the way)
> that we ought to have something like "just call me Postgres" posters in
> our booth.  It turned out they had the gear to cut stickers in letter
> shapes, so a little while later we actually had those words plastered
> over our booth walls.  I think we got most interested passers-by before
> they had a chance to read it, though.

I've argued for years that postgresql.org's front banner should read:

Postgres + SQL = PostgreSQL

The fact that novices can't pronounce the name correctly is a 
problem. People will be afraid to raise the possibility as a 
solution in the enterprise if they think they'll look like a fool 
pronouncing the name aloud. I remember back in '94 being "corrected" 
when talking about Linux in the enterprise - and I was corrected in 
the wrong direction.

Someone needs to poke the propaganda minister with a stick.

Mike Mascari

Re: LinuxTag wrapup

Alvaro Herrera <alvherre@dcc.uchile.cl> — 2004-07-04T04:10:52Z

On Sat, Jul 03, 2004 at 11:33:35PM -0400, Mike Mascari wrote:

> The fact that novices can't pronounce the name correctly is a 
> problem. People will be afraid to raise the possibility as a 
> solution in the enterprise if they think they'll look like a fool 
> pronouncing the name aloud. I remember back in '94 being "corrected" 
> when talking about Linux in the enterprise - and I was corrected in 
> the wrong direction.

You made me remember that some time ago a non-tech fellow presented me
as giving a talk about "Postgresol" ... the audience had quite a laugh.
It seems nobody thought about instructing him on how to pronounce the
thing ... it was rather embarrasing anyway.

-- 
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"En las profundidades de nuestro inconsciente hay una obsesiva necesidad
de un universo lógico y coherente. Pero el universo real se halla siempre
un paso más allá de la lógica" (Irulan)

Re: LinuxTag wrapup

Andreas Pflug <pgadmin@pse-consulting.de> — 2004-07-04T08:10:24Z

Jeroen T. Vermeulen wrote:

>
>But like I said, that's just my personal conviction.  I definitely think
>people in our community ought to be willing to work together with the
>MySQL people, the FireBird people and anybody else in the free world to
>have world-class GUI development tools; 
>

Just a note:
I've been talking again to the DBdesigner4 guy, ho told me that the next 
version of that schema designer is going to support *any* target 
database system, not just MySQL. AFAICS DBdesigner4 is currently the 
most advanced open source tool to design real big data models (with sub 
models, different views on the model etc), not just that useless 
everything-on-one-page crap all around. I'll try to keep contact with 
him, for some real world experience from database system independent design.

Regards,
Andreas

Re: LinuxTag wrapup

Andreas Pflug <pgadmin@pse-consulting.de> — 2004-07-04T08:23:36Z

Tom Lane wrote:

>Andreas Pflug <pgadmin@pse-consulting.de> writes:
>  
>
>>>How about an external tool that helps in translating apps to
>>>SQL-standard syntax?  Oracle does accept the standard syntax after all.
>>>      
>>>
>
>  
>
>>Nice idea, but
>>- sources might not be accessible
>>- sources might not be easily readable (esp. if not embedded sql, 
>>example pgadmin) or created dynamically.
>>- probably too many non-ansi compliant servers (i.e. pre-9) still in use.
>>    
>>
>
>Well, I am certainly *not* buying into a goal of "support any
>application that has worked with any version of Oracle with zero source
>code changes". 
>
I didn't suggest that.

> As Dennis already pointed out, the syntax is just the
>tip of the iceberg.  (Look for instance at the thread on pgsql-bugs
>yesterday, where we concluded that Oracle 8 thinks the way to interpret
>"WHERE charcolumn = intconstant" is to cast the column to integer.
>Talk about bizarre choices...)
>  
>
Yup. No chance to mimic Oracle8 completely. For a heartily laughter: one 
guy hoped to get a PostgeSQL that's completely compatible even on the 
line protocol level. He had listened to Michael's talk, and understood 
that pgsql supports Informix like that...

>If we bought into such a goal, even partially, we'd stop making forward
>progress on our own issues and spend all our time hashing over Oracle
>compatibility choices.
>  
>
I'd offer just some basic stuff, i.e. (+) joins and sequences (BTW, we 
had discussions about sequence calling syntax quite a while ago; AFAIR 
there was consensus that a different syntax is desirable, oracle style 
being one alternative, with no decision).
This certainly wouldn't cover everything, but users could concentrate on 
the remaining 10 % making 90 % of the migration work.

Regards,
Andreas

Re: LinuxTag wrapup

Bruce Momjian <pgman@candle.pha.pa.us> — 2004-07-04T10:48:14Z

Andreas Pflug wrote:
> Jeroen T. Vermeulen wrote:
> 
> >
> >But like I said, that's just my personal conviction.  I definitely think
> >people in our community ought to be willing to work together with the
> >MySQL people, the FireBird people and anybody else in the free world to
> >have world-class GUI development tools; 
> >
> 
> Just a note:
> I've been talking again to the DBdesigner4 guy, ho told me that the next 
> version of that schema designer is going to support *any* target 
> database system, not just MySQL. AFAICS DBdesigner4 is currently the 
> most advanced open source tool to design real big data models (with sub 
> models, different views on the model etc), not just that useless 
> everything-on-one-page crap all around. I'll try to keep contact with 
> him, for some real world experience from database system independent design.

Didn't MySQL hire the DBdesigner guys months ago?  Do they still want to
support PostgreSQL?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: LinuxTag wrapup

Andreas Pflug <pgadmin@pse-consulting.de> — 2004-07-04T11:11:24Z

Bruce Momjian wrote:

>Andreas Pflug wrote:
>  
>
>>Jeroen T. Vermeulen wrote:
>>
>>    
>>
>>>But like I said, that's just my personal conviction.  I definitely think
>>>people in our community ought to be willing to work together with the
>>>MySQL people, the FireBird people and anybody else in the free world to
>>>have world-class GUI development tools; 
>>>
>>>      
>>>
>>Just a note:
>>I've been talking again to the DBdesigner4 guy, ho told me that the next 
>>version of that schema designer is going to support *any* target 
>>database system, not just MySQL. AFAICS DBdesigner4 is currently the 
>>most advanced open source tool to design real big data models (with sub 
>>models, different views on the model etc), not just that useless 
>>everything-on-one-page crap all around. I'll try to keep contact with 
>>him, for some real world experience from database system independent design.
>>    
>>
>
>Didn't MySQL hire the DBdesigner guys months ago?  Do they still want to
>support PostgreSQL?
>
>  
>

That's right, and initially they will only serve MySQL, but it will be 
extendable to support any db system. It will be GPL (or licenseable, but 
since it's a tool and not a platform IMHO GPL is ok).
If things work out as they seem, I'd contribute the pgsql stuff.

Regards,
Andreas

Re: LinuxTag wrapup

Kaare Rasmussen <kar@kakidata.dk> — 2004-07-04T16:41:33Z

> That's right, and initially they will only serve MySQL, but it will be
> extendable to support any db system. It will be GPL (or licenseable, but
> since it's a tool and not a platform IMHO GPL is ok).
> If things work out as they seem, I'd contribute the pgsql stuff.

That would be great news indeed. Currently there is a lack of an Open Source 
heavy duty database design tool.

If it can be compared to Erwin, it will be a big win - if it can do both 
reverse and forward engineering of databases.

Do you know if it will support triggers, FK's, functions, schemas, etc ?

Re: LinuxTag wrapup

Jeroen Vermeulen <jtv@xs4all.nl> — 2004-07-04T19:16:53Z

On Sun, Jul 04, 2004 at 12:10:52AM -0400, Alvaro Herrera wrote:

> You made me remember that some time ago a non-tech fellow presented me
> as giving a talk about "Postgresol" ... the audience had quite a laugh.
> It seems nobody thought about instructing him on how to pronounce the
> thing ... it was rather embarrasing anyway.

I once ran into a case of "Postgresequel."  Asked the presenter about it
afterwards and he apologized, citing prolonged stay in the US where, he
said, everybody pronounced SQL as Sequel all the time.

Jeroen

Re: LinuxTag wrapup

Justin Clift <jc@telstra.net> — 2004-07-05T02:29:20Z

Andreas Pflug wrote:
<snip>
> That's true, it's the question how much can be offered without too much 
> effort.
> I'm not too deep in oracle stuff, what comes to my mind is
> - outer join syntax (parser thing)
> - sequences usage (parser too)
> - maybe stored procedure call, with a wrapper to convert output 
> parameters to a composite return value.

There's also their "FROM DUAL" workaround (in common usage) as well.

i.e. SELECT NEXTVAL.foo FROM DUAL;

Because their SQL queries always seem to need a target object to select 
from.  i.e. "SELECT NEXTVAL.foo" isn't valid for Oracle 8/9.

Regards and best wishes,

Justin Clift

> There's certainly no point supporting any weird ddl command, so there's 
> still porting work to be done when migrating.
> 
> Regards,
> Andreas

Re: LinuxTag wrapup

Tom Lane <tgl@sss.pgh.pa.us> — 2004-07-05T02:57:42Z

Justin Clift <jc@telstra.net> writes:
> There's also their "FROM DUAL" workaround (in common usage) as well.

[ yawn... ]

regression=# create table dual();
CREATE TABLE
regression=# insert into dual default values;  
INSERT 292940 1
regression=# select 2+2 from dual;
 ?column? 
----------
        4
(1 row)

Anyone who needs this has always been able to make it trivially
(though you once had to invent a random column name for the one
required column).

Does anyone have the foggiest idea why they named it DUAL?  Doesn't
seem a very mnemonic choice to me...

			regards, tom lane

Re: LinuxTag wrapup

Mario Weilguni <mweilguni@sime.com> — 2004-07-05T06:09:39Z

> 
> Because their SQL queries always seem to need a target object to select 
> from.  i.e. "SELECT NEXTVAL.foo" isn't valid for Oracle 8/9.
> 

It has been a long time since I've used Oracle, but shouldn't it be "select foo.nextval from dual"?

Regards,
	Mario Weilguni

Re: LinuxTag wrapup

Justin Clift <jc@telstra.net> — 2004-07-05T06:23:22Z

Mario Weilguni wrote:
>>Because their SQL queries always seem to need a target object to select 
>>from.  i.e. "SELECT NEXTVAL.foo" isn't valid for Oracle 8/9.
<snip>
> It has been a long time since I've used Oracle, but shouldn't it be "select foo.nextval from dual"?

Yep, that's sounds better.  It's been a couple of months since I was 
writing SQL in Oracle.  Previous contract.

:)

Regards and best wishes,

Justin Clift


> Regards,
> 	Mario Weilguni
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
> 
>

Re: LinuxTag wrapup

Andreas Pflug <pgadmin@pse-consulting.de> — 2004-07-05T07:29:48Z

Kaare Rasmussen wrote:

>>    
>>
>
>That would be great news indeed. Currently there is a lack of an Open Source 
>heavy duty database design tool.
>
>If it can be compared to Erwin, it will be a big win - if it can do both 
>reverse and forward engineering of databases.
>  
>
It's is aimed to replace ErWIN or AppModeler/PowerDesigner.

>Do you know if it will support triggers, FK's, functions, schemas, etc ?
>  
>
FKs for sure, schemas probably too.

Triggers are db specific, and thus virtually non-supportable if the 
model should be database independent. Still, I have some ideas how to 
create models targeted at more than one db system supporting views 
(which often need to be designed individually for performance reasons) 
and triggers. Same about functions.

Regards,
Andreas

Re: LinuxTag wrapup

Andrew McMillan <andrew@catalyst.net.nz> — 2004-07-05T09:18:58Z

On Sun, 2004-07-04 at 13:11 +0200, Andreas Pflug wrote:
> 
> That's right, and initially they will only serve MySQL, but it will be 
> extendable to support any db system. It will be GPL (or licenseable, but 
> since it's a tool and not a platform IMHO GPL is ok).
> If things work out as they seem, I'd contribute the pgsql stuff.

The fact that it is written in Kylix might make this harder.  I was
looking at it last night, after your post, to see if I could package it
for Debian, but that Kylix requirement just kind of killed any ideas I
had in that direction, since I've never been able to get the environment
to even install on a Debian system :-(

Regards,
					Andrew McMillan.

-------------------------------------------------------------------------
Andrew @ Catalyst .Net .NZ  Ltd,  PO Box 11-053, Manners St,  Wellington
WEB: http://catalyst.net.nz/            PHYS: Level 2, 150-154 Willis St
DDI: +64(4)803-2201      MOB: +64(272)DEBIAN      OFFICE: +64(4)499-2267
Whereof one cannot speak, thereon one must remain silent. -- Wittgenstein
-------------------------------------------------------------------------

Re: LinuxTag wrapup

Andreas Pflug <pgadmin@pse-consulting.de> — 2004-07-05T09:36:24Z

Andrew McMillan wrote:

>On Sun, 2004-07-04 at 13:11 +0200, Andreas Pflug wrote:
>  
>
>>That's right, and initially they will only serve MySQL, but it will be 
>>extendable to support any db system. It will be GPL (or licenseable, but 
>>since it's a tool and not a platform IMHO GPL is ok).
>>If things work out as they seem, I'd contribute the pgsql stuff.
>>    
>>
>
>The fact that it is written in Kylix might make this harder.  I was
>looking at it last night, after your post, to see if I could package it
>for Debian, but that Kylix requirement just kind of killed any ideas I
>had in that direction, since I've never been able to get the environment
>to even install on a Debian system :-(
>  
>
alea non est iacta.
It's currently unknown how the follow-up will be coded. I'm contacting 
Mike Zinner, stay tuned.

Regards,
Andreas

Re: LinuxTag wrapup

Bruce Momjian <pgman@candle.pha.pa.us> — 2004-07-06T17:12:44Z

Gavin Sherry wrote:
> On Sat, 3 Jul 2004, Tom Lane wrote:
> 
> > Andreas Pflug <pgadmin@pse-consulting.de> writes:
> > > - what about Oracle portability.
> >
> > > IMHO we should rethink if we could make those people happy. How about a
> > > loadable personality (IIRC SAPDB has something like that), to exchange
> > > the parser in use with a custom one (by a SET command)? This way we have
> > > a pure ansi default, while enabling a way so someone could contribute an
> > > oracle style parser.
> >
> > How about an external tool that helps in translating apps to
> > SQL-standard syntax?  Oracle does accept the standard syntax after all.
> > That way we are truly helping people liberate themselves: they can
> > switch to any SQL-compliant database, not only Postgres.
> 
> I totally agree. After all, oracle provides such tools to their customers.

Should this be a TODO?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: LinuxTag wrapup

J. Andrew Rogers <jrogers@neopolitan.com> — 2004-07-06T17:15:22Z

On Sun, 2004-07-04 at 19:57, Tom Lane wrote:
> Anyone who needs this has always been able to make it trivially
> (though you once had to invent a random column name for the one
> required column).

In Oracle, DUAL is treated specially internally for performance reasons,
since it is so heavily used.  Making a table with the same name would
probably be a serviceable but under-performing migration mechanism.

> Does anyone have the foggiest idea why they named it DUAL?  Doesn't
> seem a very mnemonic choice to me...

There is no real authoritative answer to this, and it has long been a
mystery.  One semi-official version of the story is that it was
originally an internal table with two rows used for some operations. 
How that became a single row scratch pad table is a mystery, since even
the Oracle old-timers I know have no recollection of it ever being
anything but what it currently is.  Others claim it is a reference to
1x1 matrix operations.  There are a number of different stories that
people have heard -- I've heard three or four completely unrelated
explanations from long-time Oracle folks -- and most of them are
plausible.

It is one of those things we will probably never know.  Whatever its
historical purpose, DUAL has been so pervasively used in the Oracle
universe for so long that giving it a better name would break virtually
every Oracle application in existence.  It is an institution unto
itself.

j. andrew rogers

Re: LinuxTag wrapup

Andreas Pflug <pgadmin@pse-consulting.de> — 2004-07-06T17:37:32Z

Bruce Momjian wrote:

>Gavin Sherry wrote:
>  
>
>>On Sat, 3 Jul 2004, Tom Lane wrote:
>>
>>    
>>
>>>Andreas Pflug <pgadmin@pse-consulting.de> writes:
>>>      
>>>
>>>>- what about Oracle portability.
>>>>        
>>>>
>>>>IMHO we should rethink if we could make those people happy. How about a
>>>>loadable personality (IIRC SAPDB has something like that), to exchange
>>>>the parser in use with a custom one (by a SET command)? This way we have
>>>>a pure ansi default, while enabling a way so someone could contribute an
>>>>oracle style parser.
>>>>        
>>>>
>>>How about an external tool that helps in translating apps to
>>>SQL-standard syntax?  Oracle does accept the standard syntax after all.
>>>That way we are truly helping people liberate themselves: they can
>>>switch to any SQL-compliant database, not only Postgres.
>>>      
>>>
>>I totally agree. After all, oracle provides such tools to their customers.
>>    
>>
>
>Should this be a TODO?
>
>  
>

An external tool helping translating sql is fine, but nothing to be 
defined todo for core pgsql IMHO. I still believe some minor "oracle 
helper" behaviour (not to call it oracle compatibility, to avoid wrong 
expectations) should be added. Currently, pgsql appears a bit arrogant 
towards those oracle centric people (always a matter of point of view, 
of course). We could avoid this by offering some concessions.

Regards,
Andreas

Re: LinuxTag wrapup

Marc G. Fournier <scrappy@postgresql.org> — 2004-07-06T20:01:54Z

On Tue, 6 Jul 2004, Andreas Pflug wrote:

> An external tool helping translating sql is fine, but nothing to be 
> defined todo for core pgsql IMHO. I still believe some minor "oracle 
> helper" behaviour (not to call it oracle compatibility, to avoid wrong 
> expectations) should be added. Currently, pgsql appears a bit arrogant 
> towards those oracle centric people (always a matter of point of view, 
> of course). We could avoid this by offering some concessions.

Actually, we had added awhile back a set of 'Oracle compability' stuff to 
the backend, to handle some of the non-standard functions that Oracle 
users had access to ... is there a reason why that can't be extended?  Or 
are we talking about *really* core changes here?

----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org           Yahoo!: yscrappy              ICQ: 7615664

Re: LinuxTag wrapup

Andreas Pflug <pgadmin@pse-consulting.de> — 2004-07-06T21:17:39Z

Marc G. Fournier wrote:

> On Tue, 6 Jul 2004, Andreas Pflug wrote:
>
>> An external tool helping translating sql is fine, but nothing to be 
>> defined todo for core pgsql IMHO. I still believe some minor "oracle 
>> helper" behaviour (not to call it oracle compatibility, to avoid 
>> wrong expectations) should be added. Currently, pgsql appears a bit 
>> arrogant towards those oracle centric people (always a matter of 
>> point of view, of course). We could avoid this by offering some 
>> concessions.
>
>
> Actually, we had added awhile back a set of 'Oracle compability' stuff 
> to the backend, to handle some of the non-standard functions that 
> Oracle users had access to ... is there a reason why that can't be 
> extended?  Or are we talking about *really* core changes here? 


I don't think so. I'd like to say "we're support oracle style syntax as 
far as it's reasonable in the context of pgsql, and we're supplying best 
practice advice for some more stuff".

Regards,
Andreas

Re: LinuxTag wrapup

Simon Riggs <simon@2ndquadrant.com> — 2004-07-06T23:33:07Z

> > > Andreas Pflug <pgadmin@pse-consulting.de> writes:
> > > > - what about Oracle portability.
> > >
> > > > IMHO we should rethink if we could make those people happy. How about a
> > > > loadable personality (IIRC SAPDB has something like that), to exchange
> > > > the parser in use with a custom one (by a SET command)? This way we have
> > > > a pure ansi default, while enabling a way so someone could contribute an
> > > > oracle style parser.
> > >
> > > How about an external tool that helps in translating apps to
> > > SQL-standard syntax?  Oracle does accept the standard syntax after all.
> > > That way we are truly helping people liberate themselves: they can
> > > switch to any SQL-compliant database, not only Postgres.
> > 
> > I totally agree. After all, oracle provides such tools to their customers.

External tool is one thing, but the loadable personality seems like a
very good idea and worth discussing further.

For ANSI standard, you need a checker that will reject non-ANSI right?
How do you handle the same thing for Oracle and others. It would be very
difficult to go through the parser and annotate everything as IsOracle
or IsANSI etc..

IMHO the loadable personality would allow considerable further
compatibility, but without effecting core behaviours. As we've seen,
many of these products behave in exactly opposite ways, so we need a way
that can cater for them all.

Porting is such a pain...there has to be a better way.

Best regards, Simon Riggs

Loadable Oracle Personality: WAS "LinuxTag wrapup" thread

Justin Clift <jc@telstra.net> — 2004-07-07T01:04:56Z

Simon Riggs wrote:
<snip>
> External tool is one thing, but the loadable personality seems like a
> very good idea and worth discussing further.

Would an interesting, and maybe slightly different way of viewing a 
"loadable personality," be as a set of "rules" that can be applied to 
parser input before the parser actually gets it... and massages input 
SQL into something for the parser to understand.

I'm hugely generalising here of course, but you know how we have a 
PostgreSQL "Rules" system that rewrites queries before handing them to 
the query planner... well, would it be possible/practical to potentially 
have a "Rules" system that rewrites incoming SQL before it gets given to 
the normal parser.

Might get complicated though... we'd need a pre-parser or something.

However, having a generalised system for doing this may make it far 
easier to provide "personalities".  i.e. load a set of Oracle 8i rules, 
load a set of Oracle 9i rules, load a set of DB2 x, rules, etc.

:)

Regards and best wishes,

Justin Clift

Re: Loadable Oracle Personality: WAS "LinuxTag wrapup"

Simon Riggs <simon@2ndquadrant.com> — 2004-07-07T22:52:03Z

On Wed, 2004-07-07 at 02:04, Justin Clift wrote:
> Simon Riggs wrote:
> <snip>
> > External tool is one thing, but the loadable personality seems like a
> > very good idea and worth discussing further.
> 
> Would an interesting, and maybe slightly different way of viewing a 
> "loadable personality," be as a set of "rules" that can be applied to 
> parser input before the parser actually gets it... and massages input 
> SQL into something for the parser to understand.
> 
> I'm hugely generalising here of course, but you know how we have a 
> PostgreSQL "Rules" system that rewrites queries before handing them to 
> the query planner... well, would it be possible/practical to potentially 
> have a "Rules" system that rewrites incoming SQL before it gets given to 
> the normal parser.
> 
> Might get complicated though... we'd need a pre-parser or something.
> 
> However, having a generalised system for doing this may make it far 
> easier to provide "personalities".  i.e. load a set of Oracle 8i rules, 
> load a set of Oracle 9i rules, load a set of DB2 x, rules, etc.
> 
> :)
> 

...Something to return to later, when this release is done.

Best regards, Simon