Thread: Immutable attributes?
To protect the database from programming errors (there is a team
working on the project and some beginners may produce bugs), I would
like to flag some attributes as immutable, meaning non modifiable in
an UPDATE. (Typical examples are ID or creation time.)
Currently, I use triggers:
CREATE OR REPLACE FUNCTION check_immutable() RETURNS TRIGGER
AS 'BEGIN
IF NEW.id != OLD.id OR NEW.created != OLD.created THEN
RAISE EXCEPTION ''Change not allowed in that table'';
END IF;
RETURN NEW;
END;'
LANGUAGE PLPGSQL;
CREATE TRIGGER check_immutable
BEFORE UPDATE ON MyTable
FOR EACH ROW
EXECUTE PROCEDURE check_immutable();
It is quite painful, since I need a function (with the list of
immutable attributes) and a trigger per table. If I INHERITS from a
table, triggers on the parent table are not called if I update the
child table.
Is there a better way? I do not find a constraint IMMUTABLE for
attributes.
PostgreSQL 7.4, switching to 8.0 would be difficult.
On Sun, Apr 24, 2005 at 09:26:49PM +0200, Stephane Bortzmeyer wrote: > To protect the database from programming errors (there is a team > working on the project and some beginners may produce bugs), I would > like to flag some attributes as immutable, meaning non modifiable in > an UPDATE. (Typical examples are ID or creation time.) > > Currently, I use triggers: You could use RULEs instead, but the effect would be similar. > PostgreSQL 7.4, switching to 8.0 would be difficult. Now is easier than later. Cheers, D -- David Fetter david@fetter.org http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote!
On Sun, Apr 24, 2005 at 09:26:49PM +0200, Stephane Bortzmeyer wrote:
>
> To protect the database from programming errors (there is a team
> working on the project and some beginners may produce bugs), I would
> like to flag some attributes as immutable, meaning non modifiable in
> an UPDATE. (Typical examples are ID or creation time.)
>
> Currently, I use triggers:
...
> It is quite painful, since I need a function (with the list of
> immutable attributes) and a trigger per table. If I INHERITS from a
> table, triggers on the parent table are not called if I update the
> child table.
This wouldn't solve all your problems, but you could write a generic
trigger function in a language like PL/Tcl or PL/Python (or PL/Perl
in 8.0 and later) and pass the column name(s) as arguments. Here's
a minimally-tested example -- if it doesn't work exactly the way
you want then at least it should serve as inspiration:
CREATE FUNCTION check_immutable() RETURNS trigger AS '
for col in TD["args"]:
if TD["new"][col] != TD["old"][col]:
plpy.error(''attribute "%s" is immutable'' % col)
return "OK"
' LANGUAGE plpythonu;
CREATE TABLE foo (
id serial PRIMARY KEY,
created date NOT NULL DEFAULT current_date,
name text NOT NULL
);
CREATE TRIGGER check_immutable BEFORE UPDATE ON foo
FOR EACH ROW EXECUTE PROCEDURE check_immutable('id', 'created');
INSERT INTO foo (name) VALUES ('name 1');
SELECT * FROM foo;
id | created | name
----+------------+--------
1 | 2005-04-24 | name 1
(1 row)
UPDATE foo SET created = '2005-05-01';
ERROR: plpython: function "check_immutable" failed
DETAIL: plpy.Error: ('attribute "created" is immutable',)
UPDATE foo SET id = 2;
ERROR: plpython: function "check_immutable" failed
DETAIL: plpy.Error: ('attribute "id" is immutable',)
UPDATE foo SET name = 'name 2', created = current_date;
SELECT * FROM foo;
id | created | name
----+------------+--------
1 | 2005-04-24 | name 2
(1 row)
I was going to suggest using contrib/noupdate, but it doesn't appear
to work, at least not the way I was expecting:
CREATE TABLE foo (
id serial PRIMARY KEY,
name text NOT NULL
);
CREATE TRIGGER check_immutable BEFORE UPDATE ON foo
FOR EACH ROW EXECUTE PROCEDURE noup('id');
INSERT INTO foo (name) VALUES ('name 1');
UPDATE foo SET id = 2;
WARNING: id: update not allowed
UPDATE 0
UPDATE foo SET name = 'name 2';
WARNING: id: update not allowed
UPDATE 0
I expected the second update to succeed since we're not changing
the value of id. I'm wondering if that's a bug in the module.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
On Sun, Apr 24, 2005 at 02:53:51PM -0700, David Fetter <david@fetter.org> wrote a message of 21 lines which said: > > PostgreSQL 7.4, switching to 8.0 would be difficult. > > Now is easier than later. Do you mean that PostgreSQL 8 has immutable attributes ? I do not find that.
On Sun, Apr 24, 2005 at 10:05:53PM -0600, Michael Fuhr <mike@fuhr.org> wrote a message of 85 lines which said: > CREATE FUNCTION check_immutable() RETURNS trigger AS ' > for col in TD["args"]: Ah, yes, much better than mine. Thanks.
On Mon, Apr 25, 2005 at 09:18:16AM +0200, Stephane Bortzmeyer wrote: > On Sun, Apr 24, 2005 at 02:53:51PM -0700, David Fetter <david@fetter.org> wrote > > > > > > PostgreSQL 7.4, switching to 8.0 would be difficult. > > > > Now is easier than later. > > Do you mean that PostgreSQL 8 has immutable attributes ? I do not find > that. I think that was a comment about upgrading in general, not an implication that 8.0 supports a particular feature. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
On Sun, Apr 24, 2005 at 10:05:53PM -0600, Michael Fuhr <mike@fuhr.org> wrote a message of 85 lines which said: > This wouldn't solve all your problems, but you could write a generic > trigger function in a language like PL/Tcl or PL/Python (or PL/Perl > in 8.0 and later) and pass the column name(s) as arguments. Here's > a minimally-tested example Tested and it works fine. Thanks.
Hi for all , plese a question ,this function can be write in pl/pgsql ??????? thank for all 2005/4/24, Stephane Bortzmeyer <bortzmeyer@nic.fr>: > To protect the database from programming errors (there is a team > working on the project and some beginners may produce bugs), I would > like to flag some attributes as immutable, meaning non modifiable in > an UPDATE. (Typical examples are ID or creation time.) > > Currently, I use triggers: > > CREATE OR REPLACE FUNCTION check_immutable() RETURNS TRIGGER > AS 'BEGIN > IF NEW.id != OLD.id OR NEW.created != OLD.created THEN > RAISE EXCEPTION ''Change not allowed in that table''; > END IF; > RETURN NEW; > END;' > LANGUAGE PLPGSQL; > > CREATE TRIGGER check_immutable > BEFORE UPDATE ON MyTable > FOR EACH ROW > EXECUTE PROCEDURE check_immutable(); > > It is quite painful, since I need a function (with the list of > immutable attributes) and a trigger per table. If I INHERITS from a > table, triggers on the parent table are not called if I update the > child table. > > Is there a better way? I do not find a constraint IMMUTABLE for > attributes. > > PostgreSQL 7.4, switching to 8.0 would be difficult. > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > -- cordialmente, Ing. Mario Soto Cordones
On Tue, Apr 26, 2005 at 09:49:05AM -0400, Mario Soto Cordones <msotocl@gmail.com> wrote a message of 45 lines which said: > Hi for all , plese a question ,this function can be write in pl/pgsql ??????? I do not understand, it IS pl/pgsql. > > CREATE OR REPLACE FUNCTION check_immutable() RETURNS TRIGGER > > AS 'BEGIN > > IF NEW.id != OLD.id OR NEW.created != OLD.created THEN > > RAISE EXCEPTION ''Change not allowed in that table''; > > END IF; > > RETURN NEW; > > END;' > > LANGUAGE PLPGSQL;
On Tue, 26 Apr 2005, Mario Soto Cordones wrote: > Hi for all , plese a question ,this function can be write in pl/pgsql ??????? No, there is no possibility write it in pl/pgsql. Pavel Stehule >
On Tue, 26 Apr 2005, Mario Soto Cordones wrote: > Hi for all , plese a question ,this function can be write in pl/pgsql ??????? > I am sorry. Universal handler for immutable attributes is not possible in plpgsql. Only if you know names of immutable columns, than you can use plpgsql. You have to use plperl, pltcl else. Regards Pavel Stehule