I have one large table Partitioned via timestamp on monthly parts.
And i use Inheritance on it.
So structure look like:
Parent table:
entity_log
...
And lots child tables like:
entity_log_2002_01
...
Check constraints:
"chk_entity_log_2002_01" CHECK (ctime >= '2002-01-01 00:00:00'::timestamp without time zone AND ctime <
'2002-02-0100:00:00'::timestamp without
time zone)
Inherits: entity_log
... and so on
entity_log_2009_06
...
Business logic required past months entries must by read only. Do it on application side look impossible (way too many
oldspaghetti code).
So after some think about alternatives i choose do it via limiting grants on past months tables.
So i removed write access from application user on all old entity_log_* tables (on test system).
And testers found unexpected bug:
12:15:18 MSD bill@billing 67842 ERROR: permission denied for relation entity_log_2002_01
12:15:18 MSD bill@billing 67842 STATEMENT: UPDATE entity_log SET usermsg = 'some text' WHERE id = 46248962
really entry with id=46248962 located in entity_log_2009_06 table where write grants set (e.g. only entity_log_2009_06
tableneed be updated).
I not sure are it is bug or intentional. Probably would be good first check where entries to update located and check
grantsonly after (I not sure
about possibility learn PostgreSQL do such tricks).
I really look for some working solution of my problem. I cannot solve this issue via adding 'on update' trigger on
entity_logtable.
--
SY, Maxim Boguk