Re: [PATCHES] CREATE SYNONYM ...

On 3/14/06, Andrew Dunstan <[EMAIL PROTECTED]> wrote: Maybe one of the proponents could put together a comparison matrix ofhow this is done in each of the databases previously mentioned. Hans, I don't have time to do this, would you like to take a stab at it? -- Jonah H. Harris, Database Internals

Re: [PATCHES] CREATE SYNONYM ...

Tom Lane wrote: Andrew Dunstan <[EMAIL PROTECTED]> writes: Even if they don't all have precisely the same semantics, though, is there an objection in principle to providing synonyms? The point I was trying to bring out is that they aren't standard, which amounts to an objection in pri

Re: [PATCHES] CREATE SYNONYM ...

Andrew Dunstan <[EMAIL PROTECTED]> writes: > Even if they don't all have precisely the same semantics, though, is > there an objection in principle to providing synonyms? The point I was trying to bring out is that they aren't standard, which amounts to an objection in principle. I'd at least li

Re: [PATCHES] CREATE SYNONYM ...

Tom Lane wrote: =?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <[EMAIL PROTECTED]> writes: Peter Eisentraut wrote: Does any SQL-like database system other than Oracle have this feature? the most popular are: db2, max db, informix, ms sql. in other words: all databases which are

Re: [PATCHES] CREATE SYNONYM ...

=?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <[EMAIL PROTECTED]> writes: > Peter Eisentraut wrote: >> Does any SQL-like database system other than Oracle have this feature? > the most popular are: db2, max db, informix, ms sql. > in other words: all databases which are widely used and widely accepted.

Re: [PATCHES] CREATE SYNONYM ...

Peter Eisentraut wrote: Hans-Jürgen Schönig wrote: This feature is especially important to people who want to port from Oracle to PostgreSQL (almost every customer who ports larger Oracle applications will asked for it). Does any SQL-like database system other than Oracle have this feature?

Re: [PATCHES] CREATE SYNONYM ...

Jonah H. Harris wrote: On 3/13/06, *Peter Eisentraut* <[EMAIL PROTECTED] > wrote: Does any SQL-like database system other than Oracle have this feature? I know that SQL Server, DB2, SAP DB/MAX DB, and Mimer have it. Introduced in MSSQL2005: http://msdn2.microso

Re: [PATCHES] CREATE SYNONYM ...

On 3/13/06, Peter Eisentraut <[EMAIL PROTECTED]> wrote: Does any SQL-like database system other than Oracle have this feature? I know that SQL Server, DB2, SAP DB/MAX DB, and Mimer have it. -- Jonah H. Harris, Database Internals ArchitectEnterpriseDB Corporation732.331.1324

Re: [PATCHES] CREATE SYNONYM ...

Hans-Jürgen Schönig wrote: > This feature is especially important to people who want to port from > Oracle to PostgreSQL (almost every customer who ports larger Oracle > applications will asked for it). Does any SQL-like database system other than Oracle have this feature? -- Peter Eisentraut ht

Re: [PATCHES] CREATE SYNONYM ...

On 3/8/06, Stephan Szabo <[EMAIL PROTECTED]> wrote: You responded with a statement about synonyms having cost in response to amessage of mine in response to a message from [EMAIL PROTECTED] whichsaid "the costs will only be added if the "real table" is not found. therefore there is no impact on "no

Re: [PATCHES] CREATE SYNONYM ...

On Wed, 8 Mar 2006, Jonah H. Harris wrote: > On 3/8/06, Stephan Szabo <[EMAIL PROTECTED]> wrote: > > > > What feature though? Part of the definition of a feature like synonym has > > to nail down things like how it interacts with search path > > > I've said how it interacts with the search path

Re: [PATCHES] CREATE SYNONYM ...

On 3/8/06, Stephan Szabo <[EMAIL PROTECTED]> wrote: What feature though?  Part of the definition of a feature like synonym hasto nail down things like how it interacts with search pathI've said how it interacts with the search path (with Oracle) several times and illustrated it in the last one, let

Re: [PATCHES] CREATE SYNONYM ...

On Wed, 8 Mar 2006, Jonah H. Harris wrote: > On 3/8/06, Stephan Szabo <[EMAIL PROTECTED]> wrote: > > > > Yes, however there are two slightly separate discussions going on and I > > think you're taking them as a single discussion. > > > I agree that there are two discussions happening in this thre

Re: [PATCHES] CREATE SYNONYM ...

On 3/8/06, Stephan Szabo <[EMAIL PROTECTED]> wrote: Yes, however there are two slightly separate discussions going on and Ithink you're taking them as a single discussion. I agree that there are two discussions happening in this thread, but I don't think anyone has agreed at all that this patch as

Re: [PATCHES] CREATE SYNONYM ...

Neil Conway wrote: I'm still unconvinced that this feature would be sufficiently useful to justify the maintenance burden, in addition to the added complexity: even if it is implemented in a way that imposes minimal *runtime* overhead, new features add complexity: introducing a bunch of new DD

Re: [PATCHES] CREATE SYNONYM ...

On Wed, 2006-03-08 at 08:16 -0500, Stephen Frost wrote: > If all you were doing with the view was reading from it then sure... > Creating the rules for all the views you want to make them updatable > wouldn't be fun. Well, updateable views are on the TODO list: expending our finite development res

Re: [PATCHES] CREATE SYNONYM ...

On Wed, 8 Mar 2006, Jonah H. Harris wrote: > On 3/8/06, Stephan Szabo <[EMAIL PROTECTED]> wrote: > > > > Doesn't that pretty much go against the (I thought) outstanding behavioral > > question of whether the synonyms are scoped and obey search path? If they > > do, I don't see how the above rule

Re: [PATCHES] CREATE SYNONYM ...

GRR, not enough coffee yet today :( Jonah now enjoys some SumatraOn 3/8/06, Jonah H. Harris <[EMAIL PROTECTED] > wrote:I agree wholeheartedly. regarding "we should take a fresh look at the problem rather than just blindly copying a construct from Oracle". SELECT dblink('host=somehost dbname=remot

Re: [PATCHES] CREATE SYNONYM ...

On 3/8/06, Neil Conway <[EMAIL PROTECTED]> wrote: I'm inclined to agree. The points raised about the difficulties ofmanaging large numbers of schemas are legitimate, but I don't see thatsynonyms are a very effective solution. If we're going to make it lesspainful to work on applications with many t

Re: [PATCHES] CREATE SYNONYM ...

On 3/8/06, Stephan Szabo <[EMAIL PROTECTED]> wrote: Doesn't that pretty much go against the (I thought) outstanding behavioralquestion of whether the synonyms are scoped and obey search path?  If theydo, I don't see how the above rule can hold since finding the "real table" is insufficient to know

Re: [PATCHES] CREATE SYNONYM ...

On 3/7/06, Tom Lane <[EMAIL PROTECTED]> wrote: To be blunt, those people aren't going to be moving to Postgres anyhow.If the notion of fixing this issue daunts them, they are not going to bewilling to deal with the other incompatibilities between Oracle and PG. IMHO, we're not discussing incompati

Re: [PATCHES] CREATE SYNONYM ...

* Neil Conway ([EMAIL PROTECTED]) wrote: > BTW, AFAICS synonyms for tables and views can be approximated by views: > you pay a small runtime hit to expand the view definition, but that's > fairly cheap for a simple view. Synonyms for functions can be > approximated by shell functions defined in SQL

Re: [PATCHES] CREATE SYNONYM ...

On Tue, 2006-03-07 at 17:14 -0500, Tom Lane wrote: > (Actually, I don't think the case for table synonyms has been made > adequately either; "Oracle has it" is *not* enough reason to take on > another feature that we'll have to maintain forever, especially given > that we're being told that one of

Re: [PATCHES] CREATE SYNONYM ...

On Wed, 8 Mar 2006, [ISO-8859-1] Hans-Jürgen Schönig wrote: > Stephan Szabo wrote: > > On Tue, 7 Mar 2006, Jonah H. Harris wrote: > > > > > >>On 3/7/06, Stephan Szabo <[EMAIL PROTECTED]> wrote: > >> > >>>I'd personally be more interested in what the impact is on people not > >>>using synonyms. How

Re: [PATCHES] CREATE SYNONYM ...

Tom Lane wrote: "Jonah H. Harris" <[EMAIL PROTECTED]> writes: Like I said, sometimes the user doesn't have a choice. Sure, it's easy to tell someone that has a 300-line PHP application to fix their code, but I've worked with people who have hundreds of thousands of lines of code and they don'

Re: [PATCHES] CREATE SYNONYM ...

I agree with this to some extent. The main use case, aside from database link objects, is really for generally large applications such as a large ERP system. Most ERP systems have a general or foundation-like schema where common objects lie and each module is separated using schemas. absolu

Re: [PATCHES] CREATE SYNONYM ...

Alvaro Herrera wrote: Jonah H. Harris wrote: Now, one may argue that it's incorrect/bad application-design to not use fully qualified names, however, there are cases (especially in VERY large database applications) where you do not want to use fully qualified naming. In PostgreSQL, the alterna

Re: [PATCHES] CREATE SYNONYM ...

What I don't really understand is what part of this cannot be achieved by changing the search_path. The only case I can think of is when you have tables A and B in schemas R and S, but you want to use R.A and S.B. So there's no way to change search_path for this. But is this really the intended

Re: [PATCHES] CREATE SYNONYM ...

One reason I like the alternative of putting synonym entries into the regular catalogs is that it eliminates the need for extra searches: you'd make exactly the same searches as you did before. Now, to the extent that this requires making catalog entries longer, there'd be a distributed overhead

Re: [PATCHES] CREATE SYNONYM ...

Stephan Szabo wrote: On Tue, 7 Mar 2006, Jonah H. Harris wrote: On 3/7/06, Stephan Szabo <[EMAIL PROTECTED]> wrote: I'd personally be more interested in what the impact is on people not using synonyms. How free is any search for synonyms if you aren't using the feature? Unless synonym ena

Re: [PATCHES] CREATE SYNONYM ...

"Jonah H. Harris" <[EMAIL PROTECTED]> writes: > Like I said, sometimes the user doesn't have a choice. Sure, it's easy to > tell someone that has a 300-line PHP application to fix their code, but I've > worked with people who have hundreds of thousands of lines of code and they > don't just say,

Re: [PATCHES] CREATE SYNONYM ...

On 3/7/06, Alvaro Herrera <[EMAIL PROTECTED]> wrote: Well, if you don't want to have a monstrous search path with 130+schemas, then you'll have a monstrous amount of synonyms.  Given thatschemas are a way to separate the object namespace, it seems moresensible to me to propagate the user of reasona

Re: [PATCHES] CREATE SYNONYM ...

Jonah H. Harris wrote: > Now, one may argue that it's incorrect/bad application-design to not use > fully qualified names, however, there are cases (especially in VERY large > database applications) where you do not want to use fully qualified naming. > In PostgreSQL, the alternative to synonyms i

Re: [PATCHES] CREATE SYNONYM ...

On 3/7/06, Alvaro Herrera <[EMAIL PROTECTED]> wrote: Tom Lane wrote:> (Actually, I don't think the case for table synonyms has been made> adequately either; "Oracle has it" is *not* enough reason to take on> another feature that we'll have to maintain forever, especially given > that we're being to

Re: [PATCHES] CREATE SYNONYM ...

On 3/7/06, Tom Lane <[EMAIL PROTECTED]> wrote: (Actually, I don't think the case for table synonyms has been madeadequately either; "Oracle has it" is *not* enough reason to take onanother feature that we'll have to maintain forever, especially given that we're being told that one of the major use-

Re: [PATCHES] CREATE SYNONYM ...

Tom Lane wrote: > (Actually, I don't think the case for table synonyms has been made > adequately either; "Oracle has it" is *not* enough reason to take on > another feature that we'll have to maintain forever, especially given > that we're being told that one of the major use-cases for synonyms >

Re: [PATCHES] CREATE SYNONYM ...

Stephan Szabo <[EMAIL PROTECTED]> writes: > On Tue, 7 Mar 2006, Jim C. Nasby wrote: >> Wouldn't the cost only be incurred if you searched for something in >> pg_class that wasn't there, and therefor had to fall back to pg_synonym? > I think if synonyms were search path dependant that wouldn't be t

Re: [PATCHES] CREATE SYNONYM ...

On Tue, 7 Mar 2006, Jim C. Nasby wrote: > On Tue, Mar 07, 2006 at 12:39:55PM -0800, Stephan Szabo wrote: > > > > On Tue, 7 Mar 2006, Jonah H. Harris wrote: > > > > > On 3/7/06, Stephan Szabo <[EMAIL PROTECTED]> wrote: > > > > > > > > I'd personally be more interested in what the impact is on peopl

Re: [PATCHES] CREATE SYNONYM ...

On Tue, Mar 07, 2006 at 12:39:55PM -0800, Stephan Szabo wrote: > > On Tue, 7 Mar 2006, Jonah H. Harris wrote: > > > On 3/7/06, Stephan Szabo <[EMAIL PROTECTED]> wrote: > > > > > > I'd personally be more interested in what the impact is on people not > > > using synonyms. How free is any search fo

Re: [PATCHES] CREATE SYNONYM ...

On Tue, 7 Mar 2006, Jonah H. Harris wrote: > On 3/7/06, Stephan Szabo <[EMAIL PROTECTED]> wrote: > > > > I'd personally be more interested in what the impact is on people not > > using synonyms. How free is any search for synonyms if you aren't using > > the feature? > > > Unless synonym enableme

Re: [PATCHES] CREATE SYNONYM ...

On 3/7/06, Stephan Szabo <[EMAIL PROTECTED]> wrote: I'd personally be more interested in what the impact is on people notusing synonyms. How free is any search for synonyms if you aren't usingthe feature? Unless synonym enablement were a configurable parameter (which wouldn't really make sense), th

Re: [PATCHES] CREATE SYNONYM ...

On Tue, 7 Mar 2006, [ISO-8859-1] Hans-J?rgen Sch?nig wrote: > > The semantics of namespace search seem wrong; I would think that a > > synonym in schema A should mask a table in schema B if A precedes B > > on the search path, but this doesn't work that way. > > good point. > any other opionions

Re: [PATCHES] CREATE SYNONYM ...

On 3/7/06, Hans-Jürgen Schönig <[EMAIL PROTECTED]> wrote: we should definitely work together.what is the status of your patch?maybe we can discuss this off list? The last time I worked on it was on 8.0 (I think), but it wouldn't take much to get it up to speed on 8.2.  It's actually very similar to

Re: [PATCHES] CREATE SYNONYM ...

I'd be glad to submit my patch and/or cleanup this one if its something the community would be willing to accept. we should definitely work together. what is the status of your patch? maybe we can discuss this off list? thanks, hans -- Cybertec Geschwinde & Schönig G

Re: [PATCHES] CREATE SYNONYM ...

hi tom, first of all thank you for looking into this so quickly. Tom Lane wrote: Michael Glaesemann <[EMAIL PROTECTED]> writes: On Mar 7, 2006, at 17:29 , Hans-Jürgen Schönig wrote: this patch implements CREATE SYNONYM Is this SQL spec or Oracle-specific? This is not in the spec.

Re: [PATCHES] CREATE SYNONYM ...

On 3/7/06, Tom Lane <[EMAIL PROTECTED]> wrote: I'm inclined to reject this patch on the grounds that it doesn't dowhat Oracle does and does not look like it could be extended to do whatOracle does.  My understanding is that what Oracle people mostly usesynonyms for is to provide cross-database acce

Re: [PATCHES] CREATE SYNONYM ...

Michael Glaesemann <[EMAIL PROTECTED]> writes: > On Mar 7, 2006, at 17:29 , Hans-Jürgen Schönig wrote: >> this patch implements CREATE SYNONYM > Is this SQL spec or Oracle-specific? This is not in the spec. I'm inclined to reject this patch on the grounds that it doesn't do what Oracle does and

Re: [PATCHES] CREATE SYNONYM ...

this is actually what oracle is doing: http://www.lc.leidenuniv.nl/awcourse/oracle/server.920/a96540/statements_72a.htm best regards, hans Michael Glaesemann wrote: On Mar 7, 2006, at 17:29 , Hans-Jürgen Schönig wrote: this patch implements CREATE SYNONYM Thi

Re: [PATCHES] CREATE SYNONYM ...

On Mar 7, 2006, at 17:29 , Hans-Jürgen Schönig wrote: this patch implements CREATE SYNONYM This feature is especially important to people who want to port from Oracle to PostgreSQL (almost every customer who ports larger Oracle applications will asked for it). Is this SQL spec or Orac