pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: andreas@elbrief.de
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: create rule with multiple commands
Date: Wed, 15 Jan 2020 10:40:31 -0500
Message-ID: <4633.1579102831@sss.pgh.pa.us> (raw)
In-Reply-To: <1579081610.235f840.27092@debian3>
References: <1579081610.235f840.27092@debian3>

andreas@elbrief.de writes:
> create rule a_update as on update to a do instead (
> | insert into _a (value) values (new.value);
> | insert into deleted (tid) values (old.id)
> );
> [ vs ]
> create rule a_update as on update to a do instead (
> | insert into deleted (tid) values (old.id);
> | insert into _a (value) values (new.value)
> );

> i expected the same result in both begin ... rollback sequences,
> but i get different results.
> i expect 2 rows in _a and one row in deleted, but both results are not that.

Neither of these will do what you want, because in an ON UPDATE rule
"new" and "old" represent references to the output of the view; the only
difference between them is that the desired values are substituted into
the targetlist of "new".  So in either case, the first INSERT action
causes the output of the view to change, and then the second INSERT
action computes what to insert based on the new view output and does the
wrong thing.  See

https://www.postgresql.org/docs/current/rules-update.html

Using EXPLAIN on the view UPDATE command is another way to get some
insight.

The reason why using rules to update views is semi-deprecated is exactly
that it's really hard to get the mechanism to do "simple" things like
what you'd like to do here.  I'd suggest using a trigger instead.

If you want to work with rules anyway, the first thing that you have
to remember is that unlike a trigger, "new" and "old" are not simple
composite values describing single rows --- they are references to the
whole view output.  So an absolutely essential part of any rule is some
WHERE clauses to constrain the action to just the rows you want to modify.
There are examples in the chapter I linked to above.

			regards, tom lane





view thread (2+ messages)

Message-ID: <4633.1579102831@sss.pgh.pa.us>
Permalink:  ../4633.1579102831@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/4633.1579102831@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-bugs@postgresql.org
  Cc: tgl@sss.pgh.pa.us, andreas@elbrief.de
  Subject: Re: create rule with multiple commands
  In-Reply-To: <4633.1579102831@sss.pgh.pa.us>
  References: <1579081610.235f840.27092@debian3> <4633.1579102831@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