Postgres Pro
Downloads
Home   >   mailing lists

Thread: New column modifier?

New column modifier?

From
John McKown
Date:
I am wondering about useful something might be. So I hope ya'll don't mind me throwing out for feedback. I am fairly good with standard SQL, but not the more advanced DBA things such as TRIGGERs. I am reading good book, "PostgreSQL Server Programming" to increase my knowledge. The section that I'm on now is about triggers. And how to use them to enforce a "read only" column. So I'm wondering if such is very common? I can see some uses for it with things which "should not change", such as a system generated date/time in an audit log. Given how much PostgreSQL extends beyond the ANSI standard, I'm wondering if a new column modifier, perhaps "WORM" (Write Once, Read Many) might be of any real use. Or is this "need" just not prevalent enough to require such a thing?

just thinking. But maybe not productively.

--
If you sent twitter messages while exploring, are you on a textpedition?

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

Re: New column modifier?

From
"Joshua D. Drake"
Date:
On 04/29/2015 10:03 AM, John McKown wrote:
> I am wondering about useful something might be. So I hope ya'll don't
> mind me throwing out for feedback. I am fairly good with standard SQL,
> but not the more advanced DBA things such as TRIGGERs. I am reading good
> book, "PostgreSQL Server Programming" to increase my knowledge. The
> section that I'm on now is about triggers. And how to use them to
> enforce a "read only" column. So I'm wondering if such is very common? I
> can see some uses for it with things which "should not change", such as
> a system generated date/time in an audit log. Given how much PostgreSQL
> extends beyond the ANSI standard, I'm wondering if a new column
> modifier, perhaps "WORM" (Write Once, Read Many) might be of any real
> use. Or is this "need" just not prevalent enough to require such a thing?
>
> just thinking. But maybe not productively.

See here

GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )
     [, ...] | ALL [ PRIVILEGES ] ( column_name [, ...] ) }
     ON [ TABLE ] table_name [, ...]
     TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]


http://www.postgresql.org/docs/9.4/static/sql-grant.html

and

http://www.postgresql.org/docs/9.4/static/sql-revoke.html

JD

--
Command Prompt, Inc. - http://www.commandprompt.com/  503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Announcing "I'm offended" is basically telling the world you can't
control your own emotions, so everyone else should do it for you.

Re: New column modifier?

From
Melvin Davidson
Date:
What you suggest is a new column type, and that would be something that needs to be reviewed and implemented by the PostgreSQL developers. Since it is not a SQL standard, I doubt it has much chance of implementation. That being said, you should understand that all triggers require the need for an associated trigger function. A simpler way to accomplish "WORM" is with a RULE.
EG:
CREATE RULE table_worm AS ON UPDATE TO your_table WHERE NEW.your_column <> OLD your_column DO NOTHING.

http://www.postgresql.org/docs/9.3/interactive/sql-createrule.html

On Wed, Apr 29, 2015 at 1:03 PM, John McKown <john.archie.mckown@gmail.com> wrote:
I am wondering about useful something might be. So I hope ya'll don't mind me throwing out for feedback. I am fairly good with standard SQL, but not the more advanced DBA things such as TRIGGERs. I am reading good book, "PostgreSQL Server Programming" to increase my knowledge. The section that I'm on now is about triggers. And how to use them to enforce a "read only" column. So I'm wondering if such is very common? I can see some uses for it with things which "should not change", such as a system generated date/time in an audit log. Given how much PostgreSQL extends beyond the ANSI standard, I'm wondering if a new column modifier, perhaps "WORM" (Write Once, Read Many) might be of any real use. Or is this "need" just not prevalent enough to require such a thing?

just thinking. But maybe not productively.

--
If you sent twitter messages while exploring, are you on a textpedition?

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown



--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: New column modifier?

From
John McKown
Date:
On Wed, Apr 29, 2015 at 1:05 PM, Melvin Davidson <melvin6925@gmail.com> wrote:
What you suggest is a new column type, and that would be something that needs to be reviewed and implemented by the PostgreSQL developers. Since it is not a SQL standard, I doubt it has much chance of implementation. That being said, you should understand that all triggers require the need for an associated trigger function. A simpler way to accomplish "WORM" is with a RULE.
EG:
CREATE RULE table_worm AS ON UPDATE TO your_table WHERE NEW.your_column <> OLD your_column DO NOTHING.

http://www.postgresql.org/docs/9.3/interactive/sql-createrule.html



​Thanks. I "jumped the gun" and haven't gotten to RULEs in the book yet. Lucky I like to read & have some time right now.​

 
--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.



--
If you sent twitter messages while exploring, are you on a textpedition?

He's about as useful as a wax frying pan.

10 to the 12th power microphones = 1 Megaphone

Maranatha! <><
John McKown

Re: New column modifier?

From
Jim Nasby
Date:
On 4/29/15 1:05 PM, Joshua D. Drake wrote:
>>
>> [ discussion about read-only columns ]
>
> See here
>
> GRANT { { SELECT | INSERT | UPDATE | REFERENCES } ( column_name [, ...] )

What I don't like about relying on GRANT is that the table owner gets to
bypass all that, as does a superuser. So when I'm serious about an
operation (insert, update or delete) not happening on something, I put a
trigger in place. Obviously a table owner or SU can always disable that,
but they can't do it accidentally. I would love the ability to restrict
operations both at a table and a column level.

BTW, John, you mentioned RULEs elsewhere... be very careful about using
those. They're incredibly easy to get wrong and generally not worth the
trouble.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com