Re: [HACKERS] SYNONYMS (again)

2011-08-04 Thread Boszormenyi Zoltan
2011-06-23 20:44 keltezéssel, Gurjeet Singh írta:
 On Wed, Jun 22, 2011 at 3:37 PM, Joshua D. Drake j...@commandprompt.com
 mailto:j...@commandprompt.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/



Re: [HACKERS] SYNONYMS (again)

2011-06-23 Thread PostgreSQL - Hans-Jürgen Schönig

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] SYNONYMS (again)

2011-06-23 Thread PostgreSQL - Hans-Jürgen Schönig

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


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] SYNONYMS (again)

2011-06-23 Thread Kevin Grittner
Gurjeet Singh singh.gurj...@gmail.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

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


Re: [HACKERS] SYNONYMS (again)

2011-06-23 Thread Gurjeet Singh
On Thu, Jun 23, 2011 at 2:58 PM, Kevin Grittner kevin.gritt...@wicourts.gov
 wrote:

 Gurjeet Singh singh.gurj...@gmail.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


Re: [HACKERS] SYNONYMS (again)

2011-06-23 Thread Gurjeet Singh
On Wed, Jun 22, 2011 at 3:37 PM, Joshua D. Drake j...@commandprompt.comwrote:

 Per:

 http://archives.postgresql.**org/pgsql-hackers/2010-11/**msg02043.phphttp://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


Re: [HACKERS] SYNONYMS (again)

2011-06-22 Thread Alvaro Herrera
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 alvhe...@commandprompt.com
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers