pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: Ciprian Craciun <ciprian.craciun@gmail.com>
Cc: David G. Johnston <david.g.johnston@gmail.com>
Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: Re: Unexpected behavior when combining `generated always` columns and update rules
Date: Thu, 13 Apr 2023 13:30:05 -0400
Message-ID: <1357803.1681407005@sss.pgh.pa.us> (raw)
In-Reply-To: <610e53b5-aba2-d232-9448-765327b88431@aklaver.com>
References: <CA+Tk8fz+Vuov584ff_PxxptB=dG3tX_buFr+4RTvFCN5ERFN4Q@mail.gmail.com>
	<CAKFQuwakXc0RRNU49n1FYmXXDBukvTeDRbXZZ+Fuyy2y-Z5N=g@mail.gmail.com>
	<CA+Tk8fyW4d2Q6yKq1dOH3o5LEt2VQ-QPgfrP1tdq0sUC8nVj8Q@mail.gmail.com>
	<610e53b5-aba2-d232-9448-765327b88431@aklaver.com>

Adrian Klaver <adrian.klaver@aklaver.com> writes:
> On 4/13/23 09:27, Ciprian Craciun wrote:
>> So, based on these observations, I think that `generated always`
>> columns are actually computed on insertion, and thus they are not
>> reflected in `new` on rules.

> Just realized we may have both being saying the same thing. That your 
> '...actually computed on insertion,...' meant not just for an INSERT but 
> for any change in the data. In other words when the original query 
> actually ran.

Yeah.  In an ON UPDATE rule, NEW is basically a macro that, for each
column of the target table, expands to the SET expression if that column
is in the SET list, and a reference to the existing column value
otherwise.  The OP wishes that for a generated column, we'd expand it
to the generation expression instead, presumably with the SET expressions
replacing any references to those columns.  I can see the argument
for that, but it's probably several years too late to change it now.
Even if anybody wanted to put the work into it, which frankly I doubt.
Non-SELECT rules are a development backwater, and are unlikely to
escape that category given how hard they are to use safely.

Looking at the code, it appears that generated columns are also not
updated till after BEFORE INSERT/BEFORE UPDATE triggers fire.  That's
not a development backwater, so I suppose the decision was deliberate
in that case.  Probably the reasoning was that we'd have to calculate
the generated columns twice in case a BEFORE trigger makes any relevant
changes in the row, and that seemed unduly costly.  But anyway, it seems
like being consistent with that behavior is another reason not to change
the behavior of ON UPDATE rules.

			regards, tom lane





view thread (7+ messages)

Message-ID: <1357803.1681407005@sss.pgh.pa.us>
Permalink:  ../1357803.1681407005@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/1357803.1681407005@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: tgl@sss.pgh.pa.us, adrian.klaver@aklaver.com, ciprian.craciun@gmail.com, david.g.johnston@gmail.com
  Subject: Re: Unexpected behavior when combining `generated always` columns and update rules
  In-Reply-To: <1357803.1681407005@sss.pgh.pa.us>
  References: <CA+Tk8fz+Vuov584ff_PxxptB=dG3tX_buFr+4RTvFCN5ERFN4Q@mail.gmail.com> <CAKFQuwakXc0RRNU49n1FYmXXDBukvTeDRbXZZ+Fuyy2y-Z5N=g@mail.gmail.com> <CA+Tk8fyW4d2Q6yKq1dOH3o5LEt2VQ-QPgfrP1tdq0sUC8nVj8Q@mail.gmail.com> <610e53b5-aba2-d232-9448-765327b88431@aklaver.com> <1357803.1681407005@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox