Horton

Thread

  1. IMMUTABLE columns in tables?

    Adrian von Bidder <avbidder@fortytwo.ch> — 2010-06-14T18:41:45Z

    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)

  2. Re: IMMUTABLE columns in tables?

    Chris Browne <cbbrowne@acm.org> — 2010-06-15T15:54:47Z

    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>

  3. Re: IMMUTABLE columns in tables?

    Craig Ringer <craig@postnewspapers.com.au> — 2010-06-15T16:56:46Z

    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

  4. Re: IMMUTABLE columns in tables?

    Adrian von Bidder <avbidder@fortytwo.ch> — 2010-06-15T19:03:21Z

    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

  5. Re: IMMUTABLE columns in tables?

    Vick Khera <vivek@khera.org> — 2010-06-15T19:46:37Z

    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)

  6. Re: IMMUTABLE columns in tables?

    Joshua Tolley <eggyknap@gmail.com> — 2010-06-16T15:10:22Z

    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