Обсуждение: 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