Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3774.600.62\))
Subject: Re: Rules and Command Status - update/insert/delete rule with series
 of commands in action
From: Alban Hertroys <haramrae@gmail.com>
In-Reply-To: <PH0PR10MB695991BDE3588B549CE6A4D7A3F32@PH0PR10MB6959.namprd10.prod.outlook.com>
Date: Fri, 31 May 2024 09:15:54 +0200
Cc: pgsql-general <pgsql-general@lists.postgresql.org>,
 Adrian Klaver <adrian.klaver@aklaver.com>,
 David G Johnston <david.g.johnston@gmail.com>
Content-Transfer-Encoding: quoted-printable
Message-Id: <F8C371B8-F290-4482-983E-4FD3DDD61A7D@gmail.com>
References: <PH0PR10MB6959209E2B0F36BE765E9159A3F32@PH0PR10MB6959.namprd10.prod.outlook.com>
 <d9633950-7f00-49c2-9f74-3b2b1ae0fce0@aklaver.com>
 <PH0PR10MB695991BDE3588B549CE6A4D7A3F32@PH0PR10MB6959.namprd10.prod.outlook.com>
To: John Lumby <johnlumby@hotmail.com>
Archived-At: <https://www.postgresql.org/message-id/F8C371B8-F290-4482-983E-4FD3DDD61A7D%40gmail.com>
Precedence: bulk


> On 31 May 2024, at 00:34, johnlumby@hotmail.com wrote:
>=20
> On 5/30/24 4:56 PM, David G. Johnston wrote:

(=E2=80=A6)

>>  If anything is done it would have to be new syntax. =20
>>=20
>>=20
> A much bigger task surely.=20
>=20
> On 5/30/24 5:19 PM, Adrian Klaver wrote:
>>=20
>> 2) Use INSTEAD OF triggers:=20
>>=20
>>=20
>=20
> Unfortunately the same functionality as in my example with the RULE is =
not supported for triggers on views :   from the manual
> INSTEAD OF triggers may only be defined on views, and only at row =
level;
>=20
> A RULE is essentially a statement-level operation which is what I need =
for this particular case.      A row-level trigger would not work =
because it cannot "see" the query causing it to be fired,   and also ,  =
(most importantly) is not fired at all if no rows match the original =
query,     whereas a RULE is always in effect regardless of which rows =
are involved. before.      I should add that the RULE I showed in my =
example is not the only RULE being used on this view  -    there are =
other conditional RULEs,   and the combined effect is of being able to =
change the effect of the original statement into a set of new =
statements,   one of which does what is needed.
>=20
> And if you are now inclined to say "well,    maybe the application =
itself is poorly written and should be changed"  -   I would have to =
agree,    but that is not mine to change.
>=20
> But I suppose that my next question,   given what you both say about =
the RULE system being a dead-end,  is whether there is any likelihood of =
supporting an INSTEAD OF trigger on a view at statement level?   Maybe =
that stands more chance of going somewhere?

What you=E2=80=99re attempting to do boils down to adding a =
virtualisation layer over the database.

Several middleware products exist that provide data virtualisation, =
products that are accessed as a database (or as a web service, or both) =
that pass on queries to connected systems. The virtualisation layer =
rewrites those queries between the data sources and the user-visible =
virtual database connection and between generalised SQL and native =
dialects and languages.

If existing products support your particular use-case though, namely =
rewriting operational data-storage queries to data-source specific DML =
statements and then report the correct number of affected rows back, I =
don=E2=80=99t know.

However, an important reason that PG rules are deprecated (as I =
understand it) is that it is very hard to get right for generated =
columns, which are operations with side-effects (such as incrementing a =
sequence value, for example) that are included in those queries =
rewritten by the specified rules.
I doubt that a data virtualisation layer would be able to solve that =
particular problem.

Nevertheless, considering what path you=E2=80=99re on, they may be worth =
looking at. I don=E2=80=99t think there are any open-source initiatives =
(unfortunately), they=E2=80=99re all commercial products AFAIK, and not =
cheap. With a suitable use-case they can be rather valuable tools too =
though.

Regards,

Alban Hertroys
--
Als je de draak wilt steken met iemand,
dan helpt het,
als die een punthoofd heeft.