Thread: IMMUTABLE columns in tables?

Heyho!

(Ok, seems to be feature wish day ...)

I was wondering if others would find an IMMUTABLE (or whatever) column
constraint useful as well.  Semantics would (obviously?) be to disallow
changing the value of this column after insert.

I realize that this is possible via triggers, and with the recent
possibility of having triggers fire only on changes to certain columns it's
even (presumably) not much runtime overhead, but creating triggers is very
verbose and doesn't make the db schema very readable.

cheers
-- vbi

--
Could this mail be a fake? (Answer: No! - http://fortytwo.ch/gpg/intro)

avbidder@fortytwo.ch (Adrian von Bidder) writes:
> Heyho!
>
> (Ok, seems to be feature wish day ...)
>
> I was wondering if others would find an IMMUTABLE (or whatever) column
> constraint useful as well.  Semantics would (obviously?) be to disallow
> changing the value of this column after insert.
>
> I realize that this is possible via triggers, and with the recent
> possibility of having triggers fire only on changes to certain columns it's
> even (presumably) not much runtime overhead, but creating triggers is very
> verbose and doesn't make the db schema very readable.

I'd like that feature, and I don't think it takes too much arguing to
get to the point that a declarative "IMMUTABLE" control is rather less
opaque than someone saying "oh, you could just create a trigger
running PL/LOLCODE to do that!"

I thought that this was on the Todo list, but I don't see it.
<http://wiki.postgresql.org/wiki/Todo>

If you're keen on getting it onto the ToDo list, the argumentation
process would be made easier if the material about this included
answers to a couple more questions:
  - What do other databases use as syntax for this?
  - Does SQL standard have anything to say about how this sort
    of thing ought to be declared?
--
select 'cbbrowne' || '@' || 'cbbrowne.com';
http://cbbrowne.com/info/internet.html
"MS  apparently now  has a  team dedicated  to tracking  problems with
Linux  and publicizing them.   I guess  eventually they'll  figure out
this back fires... ;)" -- William Burrow <aa126@DELETE.fan.nb.ca>

On 15/06/2010 2:41 AM, Adrian von Bidder wrote:
> Heyho!
>
> (Ok, seems to be feature wish day ...)
>
> I was wondering if others would find an IMMUTABLE (or whatever) column
> constraint useful as well.  Semantics would (obviously?) be to disallow
> changing the value of this column after insert.
>
> I realize that this is possible via triggers, and with the recent
> possibility of having triggers fire only on changes to certain columns it's
> even (presumably) not much runtime overhead, but creating triggers is very
> verbose and doesn't make the db schema very readable.

Where possible, I do this with column priveleges, ensuring that nobody
(except the superuser) has UPDATE rights for that column. It's short and
simple, though it's only effective if the regular user isn't the same as
the table owner.

It'd be nice to have something stronger in the form of a column
qualifier (akin to NOT NULL), though, and much easier to show to be
correct. Column privs may be bypassed by a superuser (or a suitably
privileged SECURITY DEFINER function/trigger), and triggers can have
"interesting" interactions between them that make it hard to feel
confident in the effect.

--
Craig Ringer

On Tuesday 15 June 2010 18.56:46 Craig Ringer wrote:

[IMMUTABLE columns]

> Column privs may be bypassed by a superuser

To be fair, constraints can be removed via ALTER TABLE, so this is not an
argument.

For me, the compelling reason to propose this is that it's much more
readable than either triggers or GRANTs.  It may be more efficient at
runtime as well, but (i) this is just speculation and (ii) it has to be
weighed against the small cost even in cases it is not used, and the cost of
maintaining the additional code paths.

Chris:
>   - What do other databases use as syntax for this?

Either my Google skills are lacking, or none of Oracle, MySQL, Sybase or MS
SQL does currently implement such a thing.

There is a proposal from 2008 for MS SQL:
    http://connect.microsoft.com/SQLServer/feedback/details/346200
Whis is essentially identical to my proposal in its first part (IMMUTABLE as
a regular constraint) and adds an extension of the form "IMMUTABLE WHEN
condition" where condition is probably anything that would also be valid as
a check contstraint.

I think the latter part is probably too complicated and opens too many gray
areas (it's not IMMUTABLE if I just can UPDATE to invalidate condition,
change my "immutable" column, and update again to undo the first change...)

The only extension I think might be useful is IMMUTABLE WHEN NOT NULL, which
would allow the row to be unset on INSERT and later be frozen.

Another question opened in that MS SQL change request: should rows with
IMMUTABLE values be allowed to be deleted?  Judgement call, personally, I
think that immutability and DELETE rights have nothing to do with each other
(I imagined use primarily to protect the "id" column against [accidential?]
modification.)

>   - Does SQL standard have anything to say about how this sort
>     of thing ought to be declared?

It appears that at least the SQL 2008 standard is not available for free.
If it were in SQL92 or whatever, I'd expect it to be already implemented,
but admitttedly I did not check these standards.

thanks for the feedback!
-- vbi

--
This is what open source software is all about: creating knockoffs and
giving them away, destroying the value of whatever the other guy is
selling.
        -- Danyel Lyons, Forbes

On Tue, Jun 15, 2010 at 11:54 AM, Chris Browne <cbbrowne@acm.org> wrote:
> I'd like that feature, and I don't think it takes too much arguing to
> get to the point that a declarative "IMMUTABLE" control is rather less
>

Not only that, but if you were to, say, make the PK field IMMUTABLE
you could then optimize out the locks involved when updating rows with
FK relations to the PK (or any other FK field, I suppose)

On Tue, Jun 15, 2010 at 11:54:47AM -0400, Chris Browne wrote:
> I'd like that feature, and I don't think it takes too much arguing to
> get to the point that a declarative "IMMUTABLE" control is rather less
> opaque than someone saying "oh, you could just create a trigger
> running PL/LOLCODE to do that!"

<useless noise>
PL/LOLCODE doesn't support running triggers, 'cuz no one has written that bit
yet
</useless noise>

--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com