[PATCHES] CREATE SYNONYM ...
this patch implements CREATE SYNONYM syntax: CREATE SYNONYM [TABLE | INDEX | SEQUENCE | VIEW] synname ON orgname; CREATE SYNONYM FUNCTION synname ON funcname(arg, arg, ...); DROP SYNONYM [TABLE | INDEX | SEQUENCE | VIEW] synname; DROP SYNONYM FUNCTION synname(arg, arg, ...); for details about synonyms see pg_synonym table. The synonym is just like a unix hardlink. Every user who has CREATE rights can create a 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). Documentation will be submitted this week. The patch applies without error against 8.1.3. 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 postgresql-8.1.3-synonym-03032006.patch.gz Description: GNU Zip compressed data ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
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 Oracle-specific? Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 6: explain analyze is your friend
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 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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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 ...
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), the cost would be the same whether they're used or not during searching.-- Jonah H. Harris, Database Internals ArchitectEnterpriseDB Corporation732.331.1324
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 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 ...
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 ...
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 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? I think if synonyms were search path dependant that wouldn't be true since you'd need to know if there was a synonym that shadowed another item. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
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 true since > you'd need to know if there was a synonym that shadowed another item. Right, and that's exactly why I complained. Even if pg_synonym is empty, it takes nonzero effort to find that out. 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 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. (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.) regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[PATCHES] variance aggregates per SQL:2003
This patch implements some new aggregate functions defined by SQL2003:
stddev_pop(), stddev_samp(), var_pop(), and var_samp(). stddev_samp()
and var_samp() are identical to the existing stddev() and variance()
aggregates, so I've made the latter aliases for the former.
I noticed that SQL2003 does not allow DISTINCT to be specified for these
aggregate functions. I can't really see the rationale for this
restriction, and it would be fairly ugly to implement as far as I can
tell. Thoughts?
-Neil
*** doc/src/sgml/func.sgml 8890d46febb8fdd59275cc8499b74abdfdf2877a
--- doc/src/sgml/func.sgml 2cc3ec6fb179bd88c7c26f269a09026f23fb0e2e
***
*** 7917,7922
--- 7917,7962
double precision for floating-point arguments,
otherwise numeric
+ historical alias for stddev_samp
+
+
+
+
+
+ standard deviation
+ population
+
+stddev_pop(expression)
+
+
+smallint, int,
+bigint, real, double
+precision, or numeric
+
+
+double precision for floating-point arguments,
+otherwise numeric
+
+ population standard deviation of the input values
+
+
+
+
+
+ standard deviation
+ sample
+
+stddev_samp(expression)
+
+
+smallint, int,
+bigint, real, double
+precision, or numeric
+
+
+double precision for floating-point arguments,
+otherwise numeric
+
sample standard deviation of the input values
***
*** 7954,7962
double precision for floating-point arguments,
otherwise numeric
! sample variance of the input values (square of the sample standard deviation)
--- 7994,8041
double precision for floating-point arguments,
otherwise numeric
! historical alias for var_samp
+
+
+
+ variance
+ population
+
+var_pop(expression)
+
+
+smallint, int,
+bigint, real, double
+precision, or numeric
+
+
+double precision for floating-point arguments,
+otherwise numeric
+
+ population variance of the input values (square of the population standard deviation)
+
+
+
+
+
+ variance
+ sample
+
+var_samp(expression)
+
+
+smallint, int,
+bigint, real, double
+precision, or numeric
+
+
+double precision for floating-point arguments,
+otherwise numeric
+
+ sample variance of the input values (square of the sample standard deviation)
+
*** src/backend/utils/adt/float.c b5507f45806d6724997c6c0e89bbdb497526d75b
--- src/backend/utils/adt/float.c e4cb057539f9adebd9adbbdaeff84fbe77ea5c5b
***
*** 1861,1871
* FLOAT AGGREGATE OPERATORS
* =
*
! * float8_accum - accumulate for AVG(), STDDEV(), etc
! * float4_accum - same, but input data is float4
! * float8_avg - produce final result for float AVG()
! * float8_variance - produce final result for float VARIANCE()
! * float8_stddev - produce final result for float STDDEV()
*
* The transition datatype for all these aggregates is a 3-element array
* of float8, holding the values N, sum(X), sum(X*X) in that order.
--- 1861,1873
* FLOAT AGGREGATE OPERATORS
* =
*
! * float8_accum - accumulate for AVG(), STDDEV(), etc
! * float4_accum - same, but input data is float4
! * float8_avg - produce final result for float AVG()
! * float8_var_samp - produce final result for float VAR_SAMP()
! * float8_var_pop - produce final result for float VAR_POP()
! * float8_stddev_samp - produce final result for float STDDEV_SAMP()
! * float8_stddev_pop - produce final result for float STDDEV_POP()
*
* The transition datatype for all these aggregates is a 3-element array
* of float8, holding the values N, sum(X), sum(X*X) in that order.
***
*** 2015,2021
}
Datum
! float8_variance(PG_FUNCTION_ARGS)
{
ArrayType *transarray = PG_GETARG_ARRAYTYPE_P(0);
float8 *transvalues;
--- 2017,2023
}
Datum
! float8_var_pop(PG_FUNCTION_ARGS)
{
ArrayType *transarray = PG_GETARG_ARRAYTYPE_P(0);
float8 *transvalues;
***
*** 2024,2034
sumX2,
numerator;
! transvalues = check_float8_array(transarray, "float8_variance");
N = transvalues[0];
sumX = transvalues[1];
sumX2 = transvalues[2];
/* Sample variance is undefined when N is 0 or 1, so return NULL */
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 > 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 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? 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. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(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] variance aggregates per SQL:2003
On Tue, Mar 07, 2006 at 05:54:00PM -0500, Neil Conway wrote:
> This patch implements some new aggregate functions defined by SQL2003:
> stddev_pop(), stddev_samp(), var_pop(), and var_samp(). stddev_samp()
> and var_samp() are identical to the existing stddev() and variance()
> aggregates, so I've made the latter aliases for the former.
>
> I noticed that SQL2003 does not allow DISTINCT to be specified for these
> aggregate functions. I can't really see the rationale for this
> restriction, and it would be fairly ugly to implement as far as I can
> tell. Thoughts?
>
The rationale is kinda mathematical. A measure of deviation from
central tendency (i.e. variance or stddev) is something where you
probably don't want to normalize the weights.
For example, the standard deviation of {0,1,1,1,2} is about 0.707, but
the standard deviation of {0,1,2} is 1.
Cheers,
D (still hoping for some way to extend stddev, etc. to intervals)
--
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 415 235 3778
Remember to vote!
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] variance aggregates per SQL:2003
On Tue, 2006-03-07 at 16:36 -0800, David Fetter wrote:
> The rationale is kinda mathematical. A measure of deviation from
> central tendency (i.e. variance or stddev) is something where you
> probably don't want to normalize the weights.
>
> For example, the standard deviation of {0,1,1,1,2} is about 0.707, but
> the standard deviation of {0,1,2} is 1.
Well, I realize that stddev(DISTINCT x) != stddev(x) and that most
people are going to be interested in stddev(x), but I don't think it's
inconceivable for someone to be interested in stddev(DISTINCT x).
Explicitly checking for and rejecting it doesn't serve any useful
purpose that I can see, beyond compliance with the letter of the
standard -- if the user asks for stddev(DISTINCT x), are we really
providing useful behavior if we refuse to calculate it?
-Neil
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [PATCHES] variance aggregates per SQL:2003
On Tue, Mar 07, 2006 at 07:56:06PM -0500, Neil Conway wrote:
> On Tue, 2006-03-07 at 16:36 -0800, David Fetter wrote:
> > The rationale is kinda mathematical. A measure of deviation from
> > central tendency (i.e. variance or stddev) is something where you
> > probably don't want to normalize the weights.
> >
> > For example, the standard deviation of {0,1,1,1,2} is about 0.707,
> > but the standard deviation of {0,1,2} is 1.
>
> Well, I realize that stddev(DISTINCT x) != stddev(x) and that most
> people are going to be interested in stddev(x), but I don't think
> it's inconceivable for someone to be interested in stddev(DISTINCT
> x).
Not inconceivable. Just really hard to justify unless you're trying
to fudge a number ;)
> Explicitly checking for and rejecting it doesn't serve any useful
> purpose that I can see, beyond compliance with the letter of the
> standard -- if the user asks for stddev(DISTINCT x), are we really
> providing useful behavior if we refuse to calculate it?
Nope. I was just coming up for a rationale for why the standard
disallows it :)
Cheers,
D
--
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 415 235 3778
Remember to vote!
---(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 ...
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 ...
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 ...
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 ...
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] variance aggregates per SQL:2003
Neil Conway <[EMAIL PROTECTED]> writes: > Well, I realize that stddev(DISTINCT x) != stddev(x) and that most > people are going to be interested in stddev(x), but I don't think it's > inconceivable for someone to be interested in stddev(DISTINCT x). > Explicitly checking for and rejecting it doesn't serve any useful > purpose that I can see, beyond compliance with the letter of the > standard -- if the user asks for stddev(DISTINCT x), are we really > providing useful behavior if we refuse to calculate it? Agreed, refusing this is not something we should waste code on. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
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, "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 ...
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 ...
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. 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 ...
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 ...
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 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. synonyms are easier to handle for an application - adding the search path to an existing application can be too intrusive and error prone. 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 ...
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. absolutely - just like jonah stated before; it is not about 100 lines PHP code. recently PostgreSQL was more and more adopted for "enterprise applications" (whatever this might be). 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. nothing to add - this is how things work in reality ... 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 5: don't forget to increase your free space map settings
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'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. i wouldn't say so. assume running 100 oracle databases. some day some financial guy will come around and tell you that you have to migrate because he wants to save money. these guys usually won't dicuss how to port - they simply tell you that things have to be ported ... And we are *not* buying into the notion of becoming a bug-compatible Oracle clone. absolutely - i think we all agree here. the reason why PostgreSQL is so strong nowadays is that it has been designed and implemented nicely. in Oracle even "select 1 + 1" is a problem for no reason ;) (If EnterpriseDB wants to try to do that, fine; they'll be earning their money the old-fashioned way...) i think nobody wants to work on crappy applications or even port them. in most cases it is simply a demand :(. 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 6: explain analyze is your friend
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 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
