generated columns

classic Classic list List threaded Threaded
75 messages Options
1234
Reply | Threaded
Open this post in threaded view
|

generated columns

Peter Eisentraut-6
Here is another attempt to implement generated columns.  This is a
well-known SQL-standard feature, also available for instance in DB2,
MySQL, Oracle.  A quick example:

  CREATE TABLE t1 (
    ...,
    height_cm numeric,
    height_in numeric GENERATED ALWAYS AS (height_cm * 2.54)
  );

(This is not related to the recent identity columns feature, other than
the similar syntax and some overlap internally.)

In previous discussions, it has often been a source of confusion whether
these generated columns are supposed to be computed on insert/update and
stored, or computed when read.  The SQL standard is not explicit, but
appears to lean toward stored.  DB2 stores.  Oracle computes on read.
MySQL supports both.  So I target implementing both.  This makes sense:
Both regular views and materialized views have their uses, too.  For the
syntax, I use the MySQL/Oracle syntax of appending [VIRTUAL|STORED].  In
this patch, only VIRTUAL is fully implemented.  I also have STORED kind
of working, but it wasn't fully baked, so I haven't included it here.

Known bugs:

- pg_dump produces a warning about a dependency loop when dumping these.
 Will need to be fixed at some point, but it doesn't prevent anything
from working right now.

Open design issues:

- COPY behavior: Currently, generated columns are automatically omitted
if there is no column list, and prohibited if specified explicitly.
When stored generated columns are implemented, they could be copied out.
 Some user options might be possible here.

- Catalog storage: I store the generation expression in pg_attrdef, like
a default.  For the most part, this works well.  It is not clear,
however, what pg_attribute.atthasdef should say.  Half the code thinks
that atthasdef means "there is something in pg_attrdef", the other half
thinks "column has a DEFAULT expression".  Currently, I'm going with the
former interpretation, because that is wired in quite deeply and things
start to crash if you violate it, but then code that wants to know
whether a column has a traditional DEFAULT expression needs to check
atthasdef && !attgenerated or something like that.

Missing/future functionality:

- STORED variant

- various ALTER TABLE variants

- index support (and related constraint support)

These can be added later once the basics are nailed down.

--
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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

0001-Generated-columns.patch (131K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: generated columns

Greg Stark
On 31 August 2017 at 05:16, Peter Eisentraut
<[hidden email]> wrote:
> Here is another attempt to implement generated columns.  This is a
> well-known SQL-standard feature, also available for instance in DB2,
> MySQL, Oracle.  A quick example:
>
>   CREATE TABLE t1 (
>     ...,
>     height_cm numeric,
>     height_in numeric GENERATED ALWAYS AS (height_cm * 2.54)
>   );

I only recently discovered we actually already have this feature. Kind of.
stark=# CREATE TABLE t1 (height_cm numeric);
CREATE TABLE
Time: 38.066 ms
stark***=# create function height_in(t t1) returns numeric language
'sql' as 'select t.height_cm * 2.54' ;
CREATE FUNCTION
Time: 1.216 ms
stark***=# insert into t1 values (2);
INSERT 0 1
Time: 10.170 ms
stark***=# select t1.height_cm, t1.height_in from t1;
┌───────────┬───────────┐
│ height_cm │ height_in │
├───────────┼───────────┤
│         2 │      5.08 │
└───────────┴───────────┘
(1 row)

Time: 1.997 ms

Yours looks better :)

--
greg

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: generated columns

Jaime Casanova-4
In reply to this post by Peter Eisentraut-6
On 30 August 2017 at 23:16, Peter Eisentraut
<[hidden email]> wrote:
> Here is another attempt to implement generated columns.  This is a
> well-known SQL-standard feature, also available for instance in DB2,
> MySQL, Oracle.
>
[...]

>
> In previous discussions, it has often been a source of confusion whether
> these generated columns are supposed to be computed on insert/update and
> stored, or computed when read.  The SQL standard is not explicit, but
> appears to lean toward stored.  DB2 stores.  Oracle computes on read.
> MySQL supports both.  So I target implementing both.  This makes sense:
> Both regular views and materialized views have their uses, too.  For the
> syntax, I use the MySQL/Oracle syntax of appending [VIRTUAL|STORED].  In
> this patch, only VIRTUAL is fully implemented.  I also have STORED kind
> of working, but it wasn't fully baked, so I haven't included it here.
>

Hi,

It applies and compiles without problems, it passes regression tests
and it does what it claims to do:

During my own tests, though, i found some problems:

-- UPDATEing the column, this is at least weird

postgres=# update t1 set height_in = 15;
ERROR:  column "height_in" can only be updated to DEFAULT
DETAIL:  Column "height_in" is a generated column.
postgres=# update t1 set height_in = default;
UPDATE 1


-- In a view it doesn't show any value

postgres=# create view v1 as select * from t1;
CREATE VIEW
postgres=# insert into t1(height_cm) values (10);
INSERT 0 1
postgres=# select * from t1;
   id   | height_cm | height_in
--------+-----------+-----------
 198000 |        10 |     25.40
(1 row)

postgres=# select * from v1;
   id   | height_cm | height_in
--------+-----------+-----------
 198000 |        10 |
(1 row)


-- In a inherits/partition tree, the default gets malformed

postgres=# create table t1_1 () inherits (t1);
CREATE TABLE
postgres=# \d t1_1
                             Table "public.t1_1"
  Column   |  Type   | Collation | Nullable |            Default
-----------+---------+-----------+----------+--------------------------------
 id        | integer |           | not null | nextval('t1_id_seq'::regclass)
 height_cm | numeric |           |          |
 height_in | numeric |           |          | height_cm * 2.54
Inherits: t1

postgres=# insert into t1_1 values (11);
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!> \q

--
Jaime Casanova                      www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: generated columns

Jaime Casanova-4
On 10 September 2017 at 00:08, Jaime Casanova
<[hidden email]> wrote:
>
> During my own tests, though, i found some problems:
>

a few more tests:

create table t1 (
 id serial,
 height_cm int,
 height_in int generated always as (height_cm * 10)
) ;


"""
postgres=# alter table t1 alter height_cm type numeric;
ERROR:  unexpected object depending on column: table t1 column height_in
"""
should i drop the column and recreate it after the fact? this seems
more annoying than the same problem with views (drop view & recreate),
specially after you implement STORED


"""
postgres=# alter table t1 alter height_in type numeric;
ERROR:  found unexpected dependency type 'a'
"""
uh!?


also is interesting that in triggers, both before and after, the
column has a null. that seems reasonable in a before trigger but not
in an after trigger
"""
create function f_trg1() returns trigger as $$
  begin
     raise notice '%', new.height_in;
     return new;
  end
$$ language plpgsql;

create trigger trg1 before insert on t1
for each row execute procedure f_trg1();

postgres=# insert into t1 values(default, 100);
NOTICE:  <NULL>
INSERT 0 1

create trigger trg2 after insert on t1
for each row execute procedure f_trg1();

postgres=# insert into t1 values(default, 100);
NOTICE:  <NULL>
NOTICE:  <NULL>
INSERT 0 1
"""

the default value shouldn't be dropped.
"""
postgres=# alter table t1 alter height_in drop default;
ALTER TABLE
postgres=# \d t1
                              Table "public.t1"
  Column   |  Type   | Collation | Nullable |            Default
----------------+---------+-----------+----------+--------------------------------
 id             | integer |           | not null |
nextval('t1_id_seq'::regclass)
 height_cm | integer |           |          |
 height_in   | integer |           |          | generated always as ()
"""
--
Jaime Casanova                      www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: generated columns

srielau

On Sep 12, 2017, at 12:35 PM, Jaime Casanova <[hidden email]> wrote:

also is interesting that in triggers, both before and after, the
column has a null. that seems reasonable in a before trigger but not
in an after trigger
Why is a NULL reasonable for before triggers?
If I create a table with a column with default and I omit that column on INSERT
Is the column value also NULL in the before trigger? (I hope not)

BTW, the original idea behind generated columns was to materialize them.
Reason being to avoid expensive computations of frequently used expressions 
(and to support indexing in the absence of indexes with expressions)
 
You may find the following amusing:

Cheers
Serge Rielau


Reply | Threaded
Open this post in threaded view
|

Re: generated columns

Simon Riggs
In reply to this post by Peter Eisentraut-6
On 31 August 2017 at 05:16, Peter Eisentraut
<[hidden email]> wrote:
> Here is another attempt to implement generated columns.  This is a
> well-known SQL-standard feature, also available for instance in DB2,
> MySQL, Oracle.  A quick example:
>
>   CREATE TABLE t1 (
>     ...,
>     height_cm numeric,
>     height_in numeric GENERATED ALWAYS AS (height_cm * 2.54)
>   );

Cool

> - pg_dump produces a warning about a dependency loop when dumping these.
>  Will need to be fixed at some point, but it doesn't prevent anything
> from working right now.
>
> Open design issues:
>
> - COPY behavior: Currently, generated columns are automatically omitted
> if there is no column list, and prohibited if specified explicitly.
> When stored generated columns are implemented, they could be copied out.
>  Some user options might be possible here.

If the values are generated immutably there would be no value in
including them in a dump. If you did dump them then they couldn't be
reloaded without error, so again, no point in dumping them.

COPY (SELECT...) already allows you options to include or exclude any
columns you wish, so I don't see the need for special handling here.

IMHO, COPY TO would exclude generated columns of either kind, ensuring
that the reload would just work.

> - Catalog storage: I store the generation expression in pg_attrdef, like
> a default.  For the most part, this works well.  It is not clear,
> however, what pg_attribute.atthasdef should say.  Half the code thinks
> that atthasdef means "there is something in pg_attrdef", the other half
> thinks "column has a DEFAULT expression".  Currently, I'm going with the
> former interpretation, because that is wired in quite deeply and things
> start to crash if you violate it, but then code that wants to know
> whether a column has a traditional DEFAULT expression needs to check
> atthasdef && !attgenerated or something like that.
>
> Missing/future functionality:
>
> - STORED variant

For me, this option would be the main feature. Presumably if STORED
then we wouldn't need the functions to be immutable, making it easier
to have columns like last_update_timestamp or last_update_username
etc..

I think an option to decide whether the default is STORED or VIRTUAL
would be useful.

> - various ALTER TABLE variants

Adding a column with GENERATED STORED would always be a full table rewrite.
Hmm, I wonder if its worth having a mixed mode: stored for new rows,
only virtual for existing rows; that way we could add GENERATED
columns easily.

> - index support (and related constraint support)

Presumably you can't index a VIRTUAL column. Or at least I don't think
its worth spending time trying to make it work.

> These can be added later once the basics are nailed down.

I imagine that if a column is generated then it is not possible to
have column level INSERT | UPDATE | DELETE privs on it. The generation
happens automatically as part of the write action if stored, or not
until select for virtual. It should be possible to have column level
SELECT privs.

--
Simon Riggs                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: generated columns

Andreas Karlsson
On 09/13/2017 04:04 AM, Simon Riggs wrote:
> On 31 August 2017 at 05:16, Peter Eisentraut
> <[hidden email]> wrote:
>> - index support (and related constraint support)
>
> Presumably you can't index a VIRTUAL column. Or at least I don't think
> its worth spending time trying to make it work.

I think end users would be surprised if one can index STORED columns and
expressions but not VIRTUAL columns. So unless it is a huge project I
would say it is worth it.

Andreas


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: generated columns

Simon Riggs
On 13 September 2017 at 09:09, Andreas Karlsson <[hidden email]> wrote:

> On 09/13/2017 04:04 AM, Simon Riggs wrote:
>>
>> On 31 August 2017 at 05:16, Peter Eisentraut
>> <[hidden email]> wrote:
>>>
>>> - index support (and related constraint support)
>>
>>
>> Presumably you can't index a VIRTUAL column. Or at least I don't think
>> its worth spending time trying to make it work.
>
>
> I think end users would be surprised if one can index STORED columns and
> expressions but not VIRTUAL columns. So unless it is a huge project I would
> say it is worth it.

It must be stored in the index certainly. I guess virtual is similar
to expression indexes then.

--
Simon Riggs                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: generated columns

Robert Haas
In reply to this post by Simon Riggs
On Tue, Sep 12, 2017 at 10:04 PM, Simon Riggs <[hidden email]> wrote:
> I think an option to decide whether the default is STORED or VIRTUAL
> would be useful.

That seems like it could be a bit of a foot-gun.  For example, an
extension author who uses generated columns will have to be careful to
always specify one or the other, because they don't know what the
default will be on the system where it's deployed.  Similarly for an
author of a portable application.  I think it'll create fewer
headaches if we just pick a default and stick with it.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: generated columns

David Fetter
In reply to this post by Andreas Karlsson
On Wed, Sep 13, 2017 at 10:09:37AM +0200, Andreas Karlsson wrote:

> On 09/13/2017 04:04 AM, Simon Riggs wrote:
> >On 31 August 2017 at 05:16, Peter Eisentraut
> ><[hidden email]> wrote:
> >>- index support (and related constraint support)
> >
> >Presumably you can't index a VIRTUAL column. Or at least I don't
> >think its worth spending time trying to make it work.
>
> I think end users would be surprised if one can index STORED columns
> and expressions but not VIRTUAL columns. So unless it is a huge
> project I would say it is worth it.

So long as the expression on the normal columns was immutable, it's
fit for an expressional index, as is any immutable function composed
with it.

What am I missing?

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: generated columns

Daniel Gustafsson
In reply to this post by Jaime Casanova-4
> On 12 Sep 2017, at 21:35, Jaime Casanova <[hidden email]> wrote:
>
> On 10 September 2017 at 00:08, Jaime Casanova
> <[hidden email]> wrote:
>>
>> During my own tests, though, i found some problems:
>
> a few more tests:
>
> create table t1 (
> id serial,
> height_cm int,
> height_in int generated always as (height_cm * 10)
> ) ;
>
>
> """
> postgres=# alter table t1 alter height_cm type numeric;
> ERROR:  unexpected object depending on column: table t1 column height_in
> """
> should i drop the column and recreate it after the fact? this seems
> more annoying than the same problem with views (drop view & recreate),
> specially after you implement STORED
>
>
> """
> postgres=# alter table t1 alter height_in type numeric;
> ERROR:  found unexpected dependency type 'a'
> """
> uh!?
>
>
> also is interesting that in triggers, both before and after, the
> column has a null. that seems reasonable in a before trigger but not
> in an after trigger
> """
> create function f_trg1() returns trigger as $$
>  begin
>     raise notice '%', new.height_in;
>     return new;
>  end
> $$ language plpgsql;
>
> create trigger trg1 before insert on t1
> for each row execute procedure f_trg1();
>
> postgres=# insert into t1 values(default, 100);
> NOTICE:  <NULL>
> INSERT 0 1
>
> create trigger trg2 after insert on t1
> for each row execute procedure f_trg1();
>
> postgres=# insert into t1 values(default, 100);
> NOTICE:  <NULL>
> NOTICE:  <NULL>
> INSERT 0 1
> """
>
> the default value shouldn't be dropped.
> """
> postgres=# alter table t1 alter height_in drop default;
> ALTER TABLE
> postgres=# \d t1
>                              Table "public.t1"
>  Column   |  Type   | Collation | Nullable |            Default
> ----------------+---------+-----------+----------+--------------------------------
> id             | integer |           | not null |
> nextval('t1_id_seq'::regclass)
> height_cm | integer |           |          |
> height_in   | integer |           |          | generated always as ()
> “""

Based on this review, and the errors noted in upthread in the previous review,
I’m marking this Returned with feedback.  When an updated version of the patch
is ready, please re-submit it to an upcoming commitfest.

cheers ./daniel

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: generated columns

Nico Williams
In reply to this post by Peter Eisentraut-6
On Thu, Aug 31, 2017 at 12:16:43AM -0400, Peter Eisentraut wrote:
> In previous discussions, it has often been a source of confusion whether
> these generated columns are supposed to be computed on insert/update and
> stored, or computed when read.  The SQL standard is not explicit, but
> appears to lean toward stored.  DB2 stores.  Oracle computes on read.

Question: How would one know the difference between storing computed
          columns vs. computing them on read?

Answer?:  Performance.  If the computation is slow, then you'll really
          notice on read.

Nico
--


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: generated columns

Adam Brusselback
I know that for my use-cases, having both options available would be very appreciated.  The vast majority of the computed columns I would use in my database would be okay to compute on read.  But there are for sure some which would be performance prohibitive to have compute on read, so i'd rather have those stored.

So for me, i'd rather default to compute on read, as long storing the pre-computed value is an option when necessary.

Just my $0.02
Thanks,
-Adam
Reply | Threaded
Open this post in threaded view
|

Re: generated columns

Nico Williams
On Mon, Oct 02, 2017 at 12:50:14PM -0400, Adam Brusselback wrote:
> I know that for my use-cases, having both options available would be very
> appreciated.  The vast majority of the computed columns I would use in my
> database would be okay to compute on read.  But there are for sure some
> which would be performance prohibitive to have compute on read, so i'd
> rather have those stored.
>
> So for me, i'd rather default to compute on read, as long storing the
> pre-computed value is an option when necessary.

Sure, I agree.  I was just wondering whether there might be any other
difference besides performance characteristics.  The answer to that is,
I think, "no".


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: generated columns

Tom Lane-2
Nico Williams <[hidden email]> writes:
> On Mon, Oct 02, 2017 at 12:50:14PM -0400, Adam Brusselback wrote:
>> So for me, i'd rather default to compute on read, as long storing the
>> pre-computed value is an option when necessary.

> Sure, I agree.  I was just wondering whether there might be any other
> difference besides performance characteristics.  The answer to that is,
> I think, "no".

What about non-immutable functions in the generation expression?

                        regards, tom lane


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: generated columns

David Fetter
On Mon, Oct 02, 2017 at 02:30:38PM -0400, Tom Lane wrote:

> Nico Williams <[hidden email]> writes:
> > On Mon, Oct 02, 2017 at 12:50:14PM -0400, Adam Brusselback wrote:
> >> So for me, i'd rather default to compute on read, as long storing the
> >> pre-computed value is an option when necessary.
>
> > Sure, I agree.  I was just wondering whether there might be any other
> > difference besides performance characteristics.  The answer to that is,
> > I think, "no".
>
> What about non-immutable functions in the generation expression?

Assuming they're permitted, which...well, I could make a case, they
should be mutually exclusive with the cached option.

I guess documenting the behavior in the manual would suffice, tempting
as it would be to include a NOTICE when the table goes from having 0
or more generated columns all of which are immutable to having at
least one that's not.

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: generated columns

Nico Williams
In reply to this post by Tom Lane-2
On Mon, Oct 02, 2017 at 02:30:38PM -0400, Tom Lane wrote:

> Nico Williams <[hidden email]> writes:
> > On Mon, Oct 02, 2017 at 12:50:14PM -0400, Adam Brusselback wrote:
> >> So for me, i'd rather default to compute on read, as long storing the
> >> pre-computed value is an option when necessary.
>
> > Sure, I agree.  I was just wondering whether there might be any other
> > difference besides performance characteristics.  The answer to that is,
> > I think, "no".
>
> What about non-immutable functions in the generation expression?

Aha, thanks!  Yes, that would be noticeable.

Nico
--


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: generated columns

Nico Williams
So yes, distinguishing stored vs. not stored computed columns is useful,
especially if the expression can refer to other columns of the same row,
though not only then.

Examples:

      -- useful only if stored (assuming these never get updated)
      inserted_at TIMESTAMP WITHOUT TIME ZONE AS (clock_timestamp())

      -- useful only if stored
      uuid uuid AS (uuid_generate_v4())

      -- useful only if stored
      who_done_it TEXT (current_user)

      -- useful especially if not stored
      user_at_host TEXT (user || '@' || host)

      -- useful if stored
      original_user_at_host TEXT (user || '@' || host)

I assume once set, a stored computed column cannot be updated, though
maybe being able to allow this would be ok.

Obviously all of this can be done with triggers and VIEWs...  The most
useful case is where a computed column is NOT stored, because it saves
you having to have a table and a view, while support for the stored case
merely saves you having to have triggers.  Of course, triggers for
computing columns are rather verbose, so not having to write those would
be convenient.

Similarly with RLS.  RLS is not strictly necessary since VIEWs and
TRIGGERs allow one to accomplish much the same results, but it's a lot
of work to get that right, while RLS makes most policies very pithy.
(RLS for *update* policies, however, still can't refer to NEW and OLD,
so one still has to resort to triggers for updates in many cases).

Nico
--


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: generated columns

Greg Stark
There are some unanswered questions with column grants too. Do we
allow granting access to a calculated column which accesses columns
the user doesn't have access to?

If so then this is a suitable substitute for using updateable views to
handle things like granting users access to things like password
hashes or personal data with details censored without giving them
access to the unhashed password or full personal info.

--
greg


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|

Re: generated columns

Peter Eisentraut-6
In reply to this post by Jaime Casanova-4
On 9/12/17 15:35, Jaime Casanova wrote:
> On 10 September 2017 at 00:08, Jaime Casanova
> <[hidden email]> wrote:
>>
>> During my own tests, though, i found some problems:

Here is an updated patch that should address the problems you have found.

> also is interesting that in triggers, both before and after, the
> column has a null. that seems reasonable in a before trigger but not
> in an after trigger

Logically, you are correct.  But it seems excessive to compute all
virtual columns for every trigger.  I don't know how to consolidate
that, especially with the current trigger API that lets
you look more or less directly into the tuple.

--
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

v2-0001-Generated-columns.patch (146K) Download Attachment
1234