public inbox for [email protected]  

help / color / mirror / Atom feed

From: Alban Hertroys <[email protected]>
To: John Lumby <[email protected]>
Cc: pgsql-general <[email protected]>
Cc: Adrian Klaver <[email protected]>
Cc: David G Johnston <[email protected]>
Subject: Re: Rules and Command Status - update/insert/delete rule with series of commands in action
Date: Fri, 31 May 2024 09:15:54 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <PH0PR10MB695991BDE3588B549CE6A4D7A3F32@PH0PR10MB6959.namprd10.prod.outlook.com>
References: <PH0PR10MB6959209E2B0F36BE765E9159A3F32@PH0PR10MB6959.namprd10.prod.outlook.com>
	<[email protected]>
	<PH0PR10MB695991BDE3588B549CE6A4D7A3F32@PH0PR10MB6959.namprd10.prod.outlook.com>


> On 31 May 2024, at 00:34, [email protected] wrote:
> 
> On 5/30/24 4:56 PM, David G. Johnston wrote:

(…)

>>  If anything is done it would have to be new syntax.  
>> 
>> 
> A much bigger task surely. 
> 
> On 5/30/24 5:19 PM, Adrian Klaver wrote:
>> 
>> 2) Use INSTEAD OF triggers: 
>> 
>> 
> 
> 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;
> 
> 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.
> 
> 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.
> 
> 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’re 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’t 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’re on, they may be worth looking at. I don’t think there are any open-source initiatives (unfortunately), they’re 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.

view thread (4+ messages)  latest in thread

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 using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Rules and Command Status - update/insert/delete rule with series of commands in action
  In-Reply-To: <[email protected]>

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

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