Re: [PATCHES] CREATE SYNONYM ...

2006-03-14 Thread Andrew Dunstan

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 principle.  I'd at least like to see
some effort made to demonstrate that we are adopting semantics that
match a majority of other DBs, rather than inventing something in a
vacuum which is what appears to be happening in this thread.

 


I agree.

Maybe one of the proponents could put together a comparison matrix of 
how this is done in each of the databases previously mentioned.


cheers

andrew

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly


Re: [PATCHES] CREATE SYNONYM ...

2006-03-14 Thread Jonah H. Harris
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 ArchitectEnterpriseDB Corporation732.331.1324


Re: [PATCHES] CREATE SYNONYM ...

2006-03-13 Thread Andreas Pflug

Jonah H. Harris wrote:
On 3/13/06, *Peter Eisentraut* [EMAIL PROTECTED] 
mailto:[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.microsoft.com/en-us/library/ms177544.aspx

Regards,
Andreas

---(end of broadcast)---
TIP 6: explain analyze is your friend


Re: [PATCHES] CREATE SYNONYM ...

2006-03-09 Thread Jonah H. Harris
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 normal users. Your idea and that of theperson who submitted this patch thus seem to be slightly different.
Yes, this is something that Hans and I would discuss about the patch. 
I'd rather the feature follows the model youdescribed (although I may have greater concerns of the cost) because it
seems consistent with other lookups.
This is correct, for a synonym to be added, the cost for 1 synonym
would be the same as adding 1 more table/function/view/etc depending on
the catalog you're searching; I don't think it's too costly. I'm
going to start a new thread to propose this clearly.

-- Jonah H. Harris, Database Internals ArchitectEnterpriseDB Corporation732.331.1324


Re: [PATCHES] CREATE SYNONYM ...

2006-03-08 Thread Neil Conway
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 resources implementing those would add plainly useful
functionality and improve our conformance with the SQL standard, neither
of which can be said about synonyms.

 It'd be nice to be able to support them in Postgres without
 having to go through alot of work.

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 DDL
commands and a new concept (synonyms) makes the system more difficult
for users to understand.

-Neil



---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster


Re: [PATCHES] CREATE SYNONYM ...

2006-03-08 Thread Andreas Pflug

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 DDL
commands and a new concept (synonyms) makes the system more difficult
for users to understand.


Synonyms appear to me a little like domains. I like them to abstract 
from proprietary data types. Similar, leightweight synonyms (pg_class 
entries) allow some abstraction if needed, without using rules. I don't 
think that synonyms are more difficult to understand than domains.


Regards,
Andreas

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings


Re: [PATCHES] CREATE SYNONYM ...

2006-03-08 Thread Jonah H. Harris
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 it is would be
acceptable for various reasons. There are a couple things that
Hans and I will discuss about the patch assuming we decide this is a
feature that would be nice for PostgreSQL. 
If your search path is A,B and there is a B.EMPLOYEE table and an
A.EMPLOYEE synonym to HR.EMPLOYEE, which table does select * from EMPLOYEEread?The
one first in your search path. You could not, for example, create
a SYNONYM called EMPLOYEE in the HR schema as it would conflict with
the EMPLOYEE table. Synonyms act like the objects they represent
in term of namespace searches.

ASSUME:
CREATE USER joe;
CREATE SCHEMA AUTHORIZATION joe;

Joe's search_path is $user,public

CREATE SCHEMA hr;
CREATE TABLE hr.employee (emp_id, ...)
CREATE TABLE hr.payroll (emp_id, ...)
CREATE TABLE hr.commissions;

For Joe to see this, they either have to add HR to their search_path or
fully qualify it. Let's assume they use current PostgreSQL
behavior:

SET search_path TO ..., HR

Now they can SELECT * FROM EMPLOYEE where EMPLOYEE is HR.EMPLOYEE

Now assume:

CREATE SCHEMA crm;
CREATE TABLE crm.employee;
CREATE TABLE crm.customer;
CREATE TABLE crm.commissions;

Now, joe needs to query customer and employee without qualification...
HR.EMPLOYEE is the common table that, with the exception of the CRM
module, the application refers to simply as EMPLOYEE. Now what
does Joe do:

SET search_path TO ..., HR, CRM;

OK, they still have the tables named correctly but they have to
manually make sure they order search_path. Now, you tell me
(without qualification) how Joe can access the CRM commissions
table? They can't.

With synonyms, the search path for Joe would remain $user, public and one could easily do
CREATE SYNONYM public.employee FOR hr.employee;
CREATE SYNONYM public.commissions FOR crm.commissions;

As Joe: SELECT * FROM EMPLOYEE becomes SELECT * FROM HR.EMPLOYEE
As Joe: SELECT * FROM COMMISSIONS becomes SELECT * FROM CRM.COMMISSIONS

I guess synonym searching could be done iff no object were found in the
current search. I don't know why I thought it would be just as
costly (perhaps too much Sam Adams). The worst-case scenario
would be an additional search only if an object weren't found in a
catalog search, basically this would be the cost of using synonyms and
wouldn't affect performance for everyone else. Oracle does have a
small cost only when using synonyms as well.

-- Jonah H. Harris, Database Internals ArchitectEnterpriseDB Corporation732.331.1324


Re: [PATCHES] CREATE SYNONYM ...

2006-03-08 Thread Stephan Szabo

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 thread, but I don't
 think anyone has agreed at all that this patch as it is would be acceptable
 for various reasons.  There are a couple things that Hans and I will discuss
 about the patch assuming we decide this is a feature that would be nice for
 PostgreSQL.

What feature though?  Part of the definition of a feature like synonym has
to nail down things like how it interacts with search path. The message I
was responding to was talking about the patch and seeming to say that
there wasn't a cost for non-users because the search was done iff a
candidate object wasn't found. IMHO, this is a different feature than a
synonym feature for which each search path entry is checked so that
synonyms in earlier path entries shadow later concrete objects. We
probably don't want both features even if we want either, but they're
really different features.

 With synonyms, the search path for Joe would remain $user, public and one
 could easily do
 CREATE SYNONYM public.employee FOR hr.employee;
 CREATE SYNONYM public.commissions FOR crm.commissions;

I would say that that's a really bad choice, and Joe should have his
synonyms somewhere other than public so as not to pollute other people's
default search path with his particular needs that may not be the same as
someone else's.  What does Jane do now when she needs the opposite set and
why is Joe's choice more relevant than Jane's?

This is not a negative effect of synonyms, merely this use.

 I guess synonym searching could be done iff no object were found in the
 current search.  I don't know why I thought it would be just as costly
 (perhaps too much Sam Adams).  The worst-case scenario would be an
 additional search only if an object weren't found in a catalog search,
 basically this would be the cost of using synonyms and wouldn't affect
 performance for everyone else.  Oracle does have a small cost only when
 using synonyms as well.

Yeah, that just seems less consistent with the rest of the way schema
searches work right now for other objects.

---(end of broadcast)---
TIP 6: explain analyze is your friend


Re: [PATCHES] CREATE SYNONYM ...

2006-03-08 Thread Jonah H. Harris
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's not go through this again.
The message I was responding to was talking about the patch and seeming to say that
there wasn't a cost for non-users because the search was done iff acandidate object wasn't found. IMHO, this is a different feature than asynonym feature for which each search path entry is checked so that
synonyms in earlier path entries shadow later concrete objects. Weprobably don't want both features even if we want either, but they'rereally different features.Like I said in the email before this, there is a way to limit the cost of synonyms for ONLY if a real object does not exist in the search path. However, this would be odd behavior regarding namespace searching IMHO. I think the only *good* implementation is to follow the search path as it is now and include synonyms in it... this would mean a cost for any query whether or not a synonym were used or not. The real question is how to lessen the cost if we decide to implement the functionality.
I would say that that's a really bad choice, and Joe should have hissynonyms somewhere other than public so as not to pollute other people's
default search path with his particular needs that may not be the same assomeone else's.What does Jane do now when she needs the opposite set andwhy is Joe's choice more relevant than Jane's?
Joe and Jane could create synonyms locally in their own schemas, so this isn't an issue at all. The demonstration example was representative of many ERP systems where a synonym is publicly shared by all modules and you wouldn't have the Jane issue.
I'm nearly done fighting this... synonyms are useful functionality that many people in this discussion have not used. I've explained how it works in Oracle and the reasoning behind it. If we want to limit users to search_path for the sake of not being Oracle, fine.
I have patches to work on and this seems to be going nowhere. I'm open to helping anyone implement similar functionality and/or discussion, but this thread has too many sub-discussions to be useful. For functionality descriptions, see the Oracle docs. I'm not averse to straying from Oracle's way of doing it if it makes sense.
-- Jonah H. Harris, Database Internals ArchitectEnterpriseDB Corporation732.331.1324


Re: [PATCHES] CREATE SYNONYM ...

2006-03-08 Thread Stephan Szabo

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 (with Oracle) several times
 and illustrated it in the last one, let's not go through this again.

Argh.

You responded with a statement about synonyms having cost in response to a
message of mine in response to a message from [EMAIL PROTECTED] which
said the costs will only be added if the real table is not found.
therefore there is no impact on normal users. Your idea and that of the
person who submitted this patch thus seem to be slightly different. Maybe
I'm confused, but to me this seems to show incomplete acceptance of this
point when the patch submitter and one of the vocal proponents have
different models in mind. I'd rather the feature follows the model you
described (although I may have greater concerns of the cost) because it
seems consistent with other lookups.


---(end of broadcast)---
TIP 6: explain analyze is your friend


Re: [PATCHES] CREATE SYNONYM ...

2006-03-07 Thread Michael Glaesemann


On Mar 7, 2006, at 17:29 , Hans-Jürgen Schönig wrote:


this patch implements CREATE SYNONYM


snip /

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 Oracle-specific?

Michael Glaesemann
grzm myrealbox com




---(end of broadcast)---
TIP 6: explain analyze is your friend


Re: [PATCHES] CREATE SYNONYM ...

2006-03-07 Thread Hans-Jürgen Schönig

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



snip /

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 Oracle-specific?

Michael Glaesemann
grzm myrealbox com




---(end of broadcast)---
TIP 6: explain analyze is your friend



--
Cybertec Geschwinde  Schönig GmbH
Schöngrabern 134; A-2020 Hollabrunn
Tel: +43/1/205 10 35 / 340
www.postgresql.at, www.cybertec.at

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org


Re: [PATCHES] CREATE SYNONYM ...

2006-03-07 Thread Tom Lane
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 does not look like it could be extended to do what
Oracle does.  My understanding is that what Oracle people mostly use
synonyms for is to provide cross-database access --- and this can't do
that.  I'm not in favor of providing syntax compatibility if we don't
have functional compatibility; I think that isn't doing anyone any
favors.  And if the behavior does get used, then we'd have a backwards
compatibility problem if anyone ever wants to do it right.

I'm also quite dubious that this would work properly, because it hooks
into table and function lookup in only one place respectively.  It's
hard to believe that only one of the many lookups for tables and
functions needs to be changed.

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.

I'm also not very happy about adding an additional catalog search to
function and table lookup, which are already quite expensive enough.

(The last two objections might both be addressed by forgetting the
notion of a separate catalog and instead making synonyms be alternative
kinds of entries in pg_class and pg_proc.  However, that does nothing to
help with the cross-database problem, and might indeed hinder it.)

Just for the record, this is lacking pg_dump support as well as
documentation.

regards, tom lane

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq


Re: [PATCHES] CREATE SYNONYM ...

2006-03-07 Thread Jonah H. Harris
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 access --- and this can't do
that.I'm not in favor of providing syntax compatibility if we don'thave functional compatibility; I think that isn't doing anyone anyfavors.And if the behavior does get used, then we'd have a backwards
compatibility problem if anyone ever wants to do it right.
People in Oracle use synonyms for two reasons... either as a synonym to
an object over a database link or to an object in another schema.
I have an almost completed patch similar to this one that does act as
Oracle does (albeit limited for database links because we don't support
them as Oracle does such as [EMAIL PROTECTED]).
I'm also quite dubious that this would work properly, because it hooksinto table and function lookup in only one place respectively.It's
hard to believe that only one of the many lookups for tables andfunctions needs to be changed.
I did pretty much the same thing for candidate lookups and haven't
found a problem yet, but that's not to say there isn't one.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 Bon the search path, but this doesn't work that way.
This is correct, A should always precede B in namespace lookups.
I'm also not very happy about adding an additional catalog search tofunction and table lookup, which are already quite expensive enough.
(The last two objections might both be addressed by forgetting thenotion of a separate catalog and instead making synonyms be alternative
kinds of entries in pg_class and pg_proc.However, that does nothing tohelp with the cross-database problem, and might indeed hinder it.)
Don't know how to really get around the additional lookup without
extending pg_class and pg_proc. Even so, this would still add
overhead to catalog searches.
Just for the record, this is lacking pg_dump support as well asdocumentation.

True. 

I'd be glad to submit my patch and/or cleanup this one if its something the community would be willing to accept.-- Jonah H. Harris, Database Internals ArchitectEnterpriseDB Corporation732.331.1324



Re: [PATCHES] CREATE SYNONYM ...

2006-03-07 Thread Hans-Jürgen Schönig

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.

I'm inclined to reject this patch on the grounds that it doesn't do
what Oracle does and does not look like it could be extended to do what
Oracle does.  My understanding is that what Oracle people mostly use
synonyms for is to provide cross-database access --- and this can't do
that.  I'm not in favor of providing syntax compatibility if we don't
have functional compatibility; I think that isn't doing anyone any
favors.  And if the behavior does get used, then we'd have a backwards
compatibility problem if anyone ever wants to do it right.



i have not seen too many using cross database link in oracle anyway. 
some major installations i have heard of recently even stopped using 
cross database transactions at all (too much overhead).
however, many people using oracle seriously (= beyond select daddy from 
parents) use synonyms to be compliant with older versions of some 
software. especially for stored procedures this is widely used. people 
use synonyms to link a function which is in one package into some 
different namespace or so to a. avoid duplicate code or b. to avoid 
cross-schema lookups and so forth.
to make it short: in our experience it is often used to solve problems 
introduced in the past (which is a quite common scenario - crappy 
applications are more widespread than good ones).




I'm also quite dubious that this would work properly, because it hooks
into table and function lookup in only one place respectively.  It's
hard to believe that only one of the many lookups for tables and
functions needs to be changed.



good point. which other places do you have on the radar?
i can dig into this further. positive feedback is always highly appreciated.



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 here?



I'm also not very happy about adding an additional catalog search to
function and table lookup, which are already quite expensive enough.


oracle documentation also states that using synonyms will add overhead. 
people will know that and this should be part of the documentation. 
however, i think - the performance impact when using this feature is 
less painful for the customer than any kind of problem related to legacy 
or duplicate code - people using features like that have to pay the 
price for that.




(The last two objections might both be addressed by forgetting the
notion of a separate catalog and instead making synonyms be alternative
kinds of entries in pg_class and pg_proc.  However, that does nothing to
help with the cross-database problem, and might indeed hinder it.)



i used a separate relation to be more flexible - we might also want to 
support synonyms on tablespaces or whatever. we thought this would be 
the better approach (also when thinking about dumps and lookups done by 
the user)




 Just for the record, this is lacking pg_dump support as well as
documentation.


i found out about pg_dump after posting ...
i have two babies ... - maybe sleep helps to prevent bugs ;).
documentation is on the way. i just wanted to post this code straight 
away so that feedback can already be incooperated into this.


finally: we will do whatever is needed to get this patch approved. it is 
sponsored work.


many thanks,

hans


--
Cybertec Geschwinde  Schönig GmbH
Schöngrabern 134; A-2020 Hollabrunn
Tel: +43/1/205 10 35 / 340
www.postgresql.at, www.cybertec.at

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match


Re: [PATCHES] CREATE SYNONYM ...

2006-03-07 Thread Hans-Jürgen Schönig
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 GmbH
Schöngrabern 134; A-2020 Hollabrunn
Tel: +43/1/205 10 35 / 340
www.postgresql.at, www.cybertec.at

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match


Re: [PATCHES] CREATE SYNONYM ...

2006-03-07 Thread Jonah H. Harris
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
yours so it would probably be just as easy to start off with your
patch. I'm open to whatever but I'm really busy so I can only
devote some time to it if it's likely to be accepted.

-Jonah



Re: [PATCHES] CREATE SYNONYM ...

2006-03-07 Thread Stephan Szabo

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 here?

I'd generally agree with Tom's assessment for this.  That seems to be the
most reasonable behavior to me.

  I'm also not very happy about adding an additional catalog search to
  function and table lookup, which are already quite expensive enough.

 oracle documentation also states that using synonyms will add overhead.
 people will know that and this should be part of the documentation.
 however, i think - the performance impact when using this feature is
 less painful for the customer than any kind of problem related to legacy
 or duplicate code - people using features like that have to pay the
 price for that.

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?

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq


Re: [PATCHES] CREATE SYNONYM ...

2006-03-07 Thread Stephan Szabo

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 enablement were a configurable parameter (which wouldn't
 really make sense), the cost would be the same whether they're used or not
 during searching.

Right, but the response was that using synonyms incurred a cost and this
should be documented.  However, if there's a cost to people not using
synonyms there's a higher barrier to entry for the feature.


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match


Re: [PATCHES] CREATE SYNONYM ...

2006-03-07 Thread Jim C. Nasby
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 for synonyms if you aren't using
   the feature?
 
 
  Unless synonym enablement were a configurable parameter (which wouldn't
  really make sense), the cost would be the same whether they're used or not
  during searching.
 
 Right, but the response was that using synonyms incurred a cost and this
 should be documented.  However, if there's a cost to people not using
 synonyms there's a higher barrier to entry for the feature.

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?
(At least I'd hope it was coded this way, but I didn't look at the
patch...)
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match


Re: [PATCHES] CREATE SYNONYM ...

2006-03-07 Thread Jonah H. Harris
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-cases for synonymsisn't going to be supported.AFAICS this patch does nothing youcouldn't do much better with a quick search-and-replace over yourapplication code.In short, I remain unsold.)

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.

As an example, you would have HR, AP, AR, GL, FA, COMMON, ... schemas
which encapsulate the functionality of their respective modules whether
it be procedures, functions, views, tables, etc. For each module
to be able to access, for example, the HR.EMPLOYEE table, they
generally refer to just EMPLOYEE which is a synonym to HR.EMPLOYEE.

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 is
to have a monstrous search path $user, public, HR, AP, AR, GL, FA,
COMMON... Not that we have Oracle Applications running on
PostgreSQL, but 11i has something like 130+? schemas which would be
pretty nasty and semi-unprofessional as a search_path rather than as
something defined similar to synonyms. Another consideration is
poor application design which uses the same named table in one schema
which acts differently than the same named table in another schema...
synonyms resolve this issue which could be problematic if not
impossible to solve using search_path alone.

Without the database link case, the functional reason for not using
search_path is surely reduced but it is in no way wholly eliminated
either. Some users don't have the ability to choose how
vendors/developers write their software and they can't easily just
convert an entire application to use search_path where they once had
synonyms (especially if the application is fairly sizable).


-- Jonah H. Harris, Database Internals ArchitectEnterpriseDB Corporation732.331.1324


Re: [PATCHES] CREATE SYNONYM ...

2006-03-07 Thread Jonah H. Harris
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 told that one of the major use-cases for synonyms isn't going to be supported.AFAICS this patch does nothing you couldn't do much better with a quick search-and-replace over your
 application code.In short, I remain unsold.)What I don't really understand is what part of this cannot be achievedby changing the search_path.The only case I can think of is when youhave 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 reallythe intended use case?
Not totally intended, but (unfortunately) used nonetheless.
I wonder whether synonyms were introduced in Oracle because of that ideaof theirs that each user has its own schema, and can access that schema
only; so to use a table in another schema you need to create a synonym.We don't have that limitation so we don't need that usage either.
No, one could do fully qualified naming in Oracle; synonyms do have other purposes outside of this single one listed.
-- Jonah H. Harris, Database Internals ArchitectEnterpriseDB Corporation732.331.1324


Re: [PATCHES] CREATE SYNONYM ...

2006-03-07 Thread Alvaro Herrera
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 is to have a monstrous search
 path $user, public, HR, AP, AR, GL, FA, COMMON...  Not that we have Oracle
 Applications running on PostgreSQL, but 11i has something like 130+? schemas
 which would be pretty nasty and semi-unprofessional as a search_path rather
 than as something defined similar to synonyms.

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 that
schemas are a way to separate the object namespace, it seems more
sensible to me to propagate the user of reasonable search paths than the
use of hundreds (thousands?) of synonyms.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

---(end of broadcast)---
TIP 6: explain analyze is your friend


Re: [PATCHES] CREATE SYNONYM ...

2006-03-07 Thread Jonah H. Harris
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 reasonable search paths than the
use of hundreds (thousands?) of synonyms.
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, gee, let's just
search-and-replace everything!; that's a testing nightmare.

Also, there's *usually* not thousands of synonyms, usually tens or
hundreds. Again, they are mainly used to easily reference objects
which exist in other schemas or where there are duplicate object names
across schemas.
-- Jonah H. Harris, Database Internals ArchitectEnterpriseDB Corporation732.331.1324


Re: [PATCHES] CREATE SYNONYM ...

2006-03-07 Thread Tom Lane
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, gee, let's just search-and-replace everything!; that's a
 testing nightmare.

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 be
willing to deal with the other incompatibilities between Oracle and PG.

And we are *not* buying into the notion of becoming a bug-compatible
Oracle clone.

(If EnterpriseDB wants to try to do that, fine; they'll be earning their
money the old-fashioned way...)

regards, tom lane

---(end of broadcast)---
TIP 6: explain analyze is your friend


Re: [PATCHES] CREATE SYNONYM ...

2006-03-07 Thread Hans-Jürgen Schönig

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 enablement were a configurable parameter (which wouldn't
really make sense), the cost would be the same whether they're used or not
during searching.



Right, but the response was that using synonyms incurred a cost and this
should be documented.  However, if there's a cost to people not using
synonyms there's a higher barrier to entry for the feature.




the costs will only be added if the real table is not found.
therefore there is no impact on normal users.

again, the most important benefit is not 0.001% more speed but the 
possibility to port from other databases easier and to treat legacy 
problems.


here at cybertec we are facing more and more problems with legacy 
databases and porting crap every day.


many thanks and best regards,

hans


--
Cybertec Geschwinde  Schönig GmbH
Schöngrabern 134; A-2020 Hollabrunn
Tel: +43/1/205 10 35 / 340
www.postgresql.at, www.cybertec.at

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org


Re: [PATCHES] CREATE SYNONYM ...

2006-03-07 Thread Hans-Jürgen Schönig

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 that might partially cancel that out --- but I
don't see any reason that the entries have to get longer for regular
tables.  The link field could be a nullable field at the end, and
the flag that it's a synonym would just be another relkind value.



i don't think this would be extensible in the way the current code is.



I don't think the case for pg_proc synonyms has been made adequately at
all, so I'd personally just blow off that part of the proposal.  There's
no real cost to just making another copy of the proc.




snip

AFAICS this patch does nothing you

couldn't do much better with a quick search-and-replace over your
application code.  In short, I remain unsold.)


in this case you are absolutely wrong - this is far from reality.
assume somebody started off with a DB2 based application. the program 
was good. then it was ported to oracle. meanwhile 300 features were 
changed, adapted, replaced, 5 programmers died and 20 left the company. 
finally some other things were changed - the internal structures of 
stored procedures ended up as don't touch me.
sed s/ /gi ... will be the key to introducing a significant amount of 
unpredictable problems - in business applications nobody will even 
CONSIDER touching something like that. i am not saying that cleaning up 
is a good thing - in some cases it is simply not doable because the guy 
who wrote the code died 5 years ago (this is a real story by the way).


i have seen databases where we had to define DELETE rules DO INSTEAD 
NOTHING because nobody knew where a bad delete actually came from - THIS 
is the kind of problem we are talking about.
to me using an alternative name is definitely not something which is bad 
at all.


the fact that oracle supports something is definitely not an argument. 
however, oracle invented this feature for a situation like the one i 
described above. the problem is: this is a quite common scenario.


assume we would fix:
- search_path issue which was brought up
- pg_dump
- the docs

would there be a serious chance to get that approved?

many thanks,

hans

--
Cybertec Geschwinde  Schönig GmbH
Schöngrabern 134; A-2020 Hollabrunn
Tel: +43/1/205 10 35 / 340
www.postgresql.at, www.cybertec.at

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org


Re: [PATCHES] CREATE SYNONYM ...

2006-03-07 Thread Hans-Jürgen Schönig

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 use case?


yes, this is a very practical case ...



I wonder whether synonyms were introduced in Oracle because of that idea
of theirs that each user has its own schema, and can access that schema
only; so to use a table in another schema you need to create a synonym.
We don't have that limitation so we don't need that usage either.



i am sure this was a reason but not the only one.
some other reason could be (again, bad but widespread): somebody defined 
a bad data structure where everything is in separate tables (tom's cars 
are in table A, bruce's cars are in table B). somebody finally finds out 
that this was a bad idea (3mio lines of code were built on top of this 
crap) and that those tables should be combined. a synonym will help.
just think of broken applications such as SAP - everything is in a 
separate table (maybe they have 100 which only stored 
desciptions), if you want to cleanup a synonym is less error prone than 
'sed -e ...'.


if synonyms are a broken concept then the same would apply for softlinks 
and hardlinks supported by filesystem - still people like soft/hardlinks 
and they are widely adopted because they are useful. of course, you can 
live without file systems links if you can afford changing the path 
after every line of shell code.


best regards,

hans


--
Cybertec Geschwinde  Schönig GmbH
Schöngrabern 134; A-2020 Hollabrunn
Tel: +43/1/205 10 35 / 340
www.postgresql.at, www.cybertec.at

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match


Re: [PATCHES] CREATE SYNONYM ...

2006-03-07 Thread Stephan Szabo
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 free is any search for synonyms if you aren't using
 the feature?
 
 
 Unless synonym enablement were a configurable parameter (which wouldn't
 really make sense), the cost would be the same whether they're used or not
 during searching.
 
 
  Right, but the response was that using synonyms incurred a cost and this
  should be documented.  However, if there's a cost to people not using
  synonyms there's a higher barrier to entry for the feature.
 


 the costs will only be added if the real table is not found.
 therefore there is no impact on normal users.

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 can hold since finding the real table
is insufficient to know if there's an earlier synonym.

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq