SYNONYMS (again)

Per:

http://archives.postgresql.org/pgsql-hackers/2010-11/msg02043.php

It seems we did come up with a use case in the procpid discussion. The
ability to change the names of columns/databases etc, to handle the
fixing of bad decision decisions during development over time.

Thoughts?

JD
--
Command Prompt, Inc. - http://www.commandprompt.com/
PostgreSQL Support, Training, Professional Services and Development
The PostgreSQL Conference - http://www.postgresqlconference.org/
@cmdpromptinc - @postgresconf - 509-416-6579

Excerpts from Joshua D. Drake's message of mié jun 22 15:37:17 -0400 2011:
> Per:
>
> http://archives.postgresql.org/pgsql-hackers/2010-11/msg02043.php
>
> It seems we did come up with a use case in the procpid discussion. The
> ability to change the names of columns/databases etc, to handle the
> fixing of bad decision decisions during development over time.
>
> Thoughts?

Let's start with what was discussed and supported in that thread, that
is, databases. It seems less clear that columns are widely believed to
be a good idea to have synonyms for. Besides, synonyms for databases
should be reasonably simple to implement, which is not something I would
say for columns.

--
Álvaro Herrera <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Jun 23, 2011, at 12:52 AM, Alvaro Herrera wrote:

> Excerpts from Joshua D. Drake's message of mié jun 22 15:37:17 -0400 2011:
>> Per:
>>
>> http://archives.postgresql.org/pgsql-hackers/2010-11/msg02043.php
>>
>> It seems we did come up with a use case in the procpid discussion. The
>> ability to change the names of columns/databases etc, to handle the
>> fixing of bad decision decisions during development over time.
>>
>> Thoughts?
>
> Let's start with what was discussed and supported in that thread, that
> is, databases. It seems less clear that columns are widely believed to
> be a good idea to have synonyms for. Besides, synonyms for databases
> should be reasonably simple to implement, which is not something I would
> say for columns.

yes, implementing synonyms is not too hard.
some time ago (3 or 4 years ago most likely) we already posted a patch providing support for synonyms.
it was rejected because synonyms were said to be a bad design pattern which app developers to do nasty things.
so, if you want to work on it maybe this patch is the place to start.

many thanks,

hans

--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de

On Jun 23, 2011, at 12:52 AM, Alvaro Herrera wrote:

> Excerpts from Joshua D. Drake's message of mié jun 22 15:37:17 -0400 2011:
>> Per:
>>
>> http://archives.postgresql.org/pgsql-hackers/2010-11/msg02043.php
>>
>> It seems we did come up with a use case in the procpid discussion. The
>> ability to change the names of columns/databases etc, to handle the
>> fixing of bad decision decisions during development over time.
>>
>> Thoughts?
>
> Let's start with what was discussed and supported in that thread, that
> is, databases. It seems less clear that columns are widely believed to
> be a good idea to have synonyms for. Besides, synonyms for databases
> should be reasonably simple to implement, which is not something I would
> say for columns.

sorry, i missed the links:

http://archives.postgresql.org/pgsql-patches/2006-03/msg00085.php

many thanks,

hans

--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de

On Wed, Jun 22, 2011 at 3:37 PM, Joshua D. Drake <jd(at)commandprompt(dot)com>wrote:

> Per:
>
> http://archives.postgresql.**org/pgsql-hackers/2010-11/**msg02043.php<http://archives.postgresql.org/pgsql-hackers/2010-11/msg02043.php>
>
> It seems we did come up with a use case in the procpid discussion. The
> ability to change the names of columns/databases etc, to handle the fixing
> of bad decision decisions during development over time.
>
> Thoughts?
>

Instead of just synonyms of columns, why don't we think about implementing
virtual columns (feature as named in other RDBMS). This is the ability to
define a column in a table which is derived using an expression around other
non-virtual columns. I agree it would be much more difficult and some may
even argue it is pointless in the presence of views and expression indexes,
but I leave that as an exercise for others.

Regards,
--
Gurjeet Singh
EnterpriseDB Corporation
The Enterprise PostgreSQL Company

Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)com> wrote:

> Instead of just synonyms of columns, why don't we think about
implementing
> virtual columns (feature as named in other RDBMS). This is the
ability to
> define a column in a table which is derived using an expression
around other
> non-virtual columns.

How do you see that working differently from what PostgreSQL can
currently do?

test=# create table line_item(id int primary key not null, quantity int
not null, unit_price numeric(13,2));
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"line_item_pkey" for table "line_item"
CREATE TABLE
test=# insert into line_item values (1,15,'12.53'),(2,5,'16.23');
INSERT 0 2
test=# create function line_total(line_item) returns numeric(13,2)
language sql immutable as $$ select ($1.quantity *
$1.unit_price)::numeric(13,2);$$;
CREATE FUNCTION
test=# select li.id, li.line_total from line_item li;
id | line_total
----+------------
1 | 187.95
2 | 81.15
(2 rows)

-Kevin

On Thu, Jun 23, 2011 at 2:58 PM, Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov
> wrote:

> Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)com> wrote:
>
> > Instead of just synonyms of columns, why don't we think about
> implementing
> > virtual columns (feature as named in other RDBMS). This is the
> ability to
> > define a column in a table which is derived using an expression
> around other
> > non-virtual columns.
>
> How do you see that working differently from what PostgreSQL can
> currently do?
>
> test=# create table line_item(id int primary key not null, quantity int
> not null, unit_price numeric(13,2));
> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
> "line_item_pkey" for table "line_item"
> CREATE TABLE
> test=# insert into line_item values (1,15,'12.53'),(2,5,'16.23');
> INSERT 0 2
> test=# create function line_total(line_item) returns numeric(13,2)
> language sql immutable as $$ select ($1.quantity *
> $1.unit_price)::numeric(13,2);$$;
> CREATE FUNCTION
> test=# select li.id, li.line_total from line_item li;
> id | line_total
> ----+------------
> 1 | 187.95
> 2 | 81.15
> (2 rows)
>

For one, this column is not part of the table, so we can't gather statistics
on them to help the optimizer.

We can'r create primary keys on this expression.

Also, say if the query wasn't fetching all the columns and we had just the
line_total call in SELECT list, the executor has to fetch the whole row and
pass it on to the function even though the function uses only part of the
row (2 columns in this case).

Regards,
--
Gurjeet Singh
EnterpriseDB Corporation
The Enterprise PostgreSQL Company

2011-06-23 20:44 keltezéssel, Gurjeet Singh írta:
> On Wed, Jun 22, 2011 at 3:37 PM, Joshua D. Drake <jd(at)commandprompt(dot)com
> <mailto:jd(at)commandprompt(dot)com>> wrote:
>
> Per:
>
> http://archives.postgresql.org/pgsql-hackers/2010-11/msg02043.php
>
> It seems we did come up with a use case in the procpid discussion. The ability to
> change the names of columns/databases etc, to handle the fixing of bad decision
> decisions during development over time.
>
> Thoughts?
>
>
> Instead of just synonyms of columns, why don't we think about implementing virtual
> columns (feature as named in other RDBMS). This is the ability to define a column in a
> table which is derived using an expression around other non-virtual columns. I agree it
> would be much more difficult and some may even argue it is pointless in the presence of
> views and expression indexes, but I leave that as an exercise for others.

A few years ago I wrote a patch for (I think) 8.2 that implemented
IDENTITY and GENERATED columns. Look it up in the archives.
The "virtual" column is called GENERATED in the SQL standard
and is part of the table and maintained as a function/expression
of other fields in the same record.

Best regards,
Zoltán Böszörményi

--
----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de
http://www.postgresql.at/