
Dear hackers,

I'm trying to use some RULE as simple and maybe slower TRIGGER, so as to
make it impossible for a row to be modified once a boolean is set to lock
it.

     CREATE TABLE foo(data TEXT, locked BOOLEAN NOT NULL DEFAULT FALSE);

This cannot be done with a CHECK constraint as it is not a restriction
about the state of the tuple, but instead to its changes.

What I could do is to skip the attempt with a NOTHING action:

     CREATE RULE PasTouche AS ON UPDATE TO foo
     WHERE old.locked=TRUE
     DO INSTEAD NOTHING;

but as a result the attempt is not reported to the transaction, which
goes on. I really want to generate an error so as to abort the
current transaction. After various attempts, I did the following:

     CREATE TABLE Boom(ok BOOLEAN CHECK(ok=TRUE));

     CREATE RULE PasTouche AS ON UPDATE TO foo
     WHERE old.locked=TRUE
     DO INSTEAD INSERT INTO Boom(ok) VALUES(FALSE);

It works, as an error is raised because the attempt fails on the check.
Other solutions could be thought off with the same result, and that
would basically use the same kind of trick...

However I thing that this would look much better to write simply:

     CREATE RULE PasTouche AS ON UPDATE TO foo
     WHERE old.locked=TRUE
     DO INSTEAD ERROR;

I think this simple new rule action could be added to pg.
I'm planning to do it, if there is no opposition.
Any comments on this proposed new rule action?
Or did I missed something obvious with rules?

Have a nice day,

-- 
Fabien Coelho - coelho@cri.ensmp.fr

Tue, 20 Apr 2004 16:10:32 +0200 (CEST)
Fabien COELHO <coelho@cri.ensmp.fr>
ERROR action extension for rules?
>      CREATE RULE PasTouche AS ON UPDATE TO foo
>      WHERE old.locked=TRUE
>      DO INSTEAD ERROR;
> 
> I think this simple new rule action could be added to pg.
> I'm planning to do it, if there is no opposition.
> Any comments on this proposed new rule action?
> Or did I missed something obvious with rules?

I've often debated creating an sql elog function for the ELSE segment of
a CASE statement.



Tue, 20 Apr 2004 10:54:30 -0400
Rod Taylor <pg@rbt.ca>
Re: ERROR action extension for rules?
Fabien COELHO <coelho@cri.ensmp.fr> writes:
> I'm trying to use some RULE as simple and maybe slower TRIGGER, so as to
> make it impossible for a row to be modified once a boolean is set to lock
> it.
> ...
> However I thing that this would look much better to write simply:

>      CREATE RULE PasTouche AS ON UPDATE TO foo
>      WHERE old.locked=TRUE
>      DO INSTEAD ERROR;

This would be sensible if rules were actually reasonable substitutes for
triggers, but they are not.  If you check the archives you will find
many many cases where people tried to do this sort of thing, and got
burned by the fundamental semantic differences ...

			regards, tom lane

Tue, 20 Apr 2004 13:06:53 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: ERROR action extension for rules? 

> >      CREATE RULE PasTouche AS ON UPDATE TO foo
> >      WHERE old.locked=TRUE
> >      DO INSTEAD ERROR;
>
> This would be sensible if rules were actually reasonable substitutes for
> triggers, but they are not.
>
> If you check the archives you will find many many cases where people
> tried to do this sort of thing, and got burned by the fundamental
> semantic differences ...

Ok, I'll look into that. Thanks for the pointer.

-- 
Fabien Coelho - coelho@cri.ensmp.fr

Wed, 21 Apr 2004 09:21:03 +0200 (CEST)
Fabien COELHO <coelho@cri.ensmp.fr>
Re: ERROR action extension for rules? 