On Fri, Apr 14, 2017 at 9:09 AM, Stephen Frost <sfrost@snowman.net> wrote:
Rod,
* Rod Taylor (rod.taylor@gmail.com) wrote: > My actual use-case involves a range. Most users can see and manipulate the > record when CURRENT_TIMESTAMP is within active_period. Some users > (staff/account admins) can see recently dead records too. And a 3rd group > (senior staff) have no time restriction, though there are a few customers > they cannot see due to their information being a touch more sensitive. > I've simplified the below rules to just deal with active_period and the > majority of user view (@> CURRENT_TIMESTAMP).
Interesting. > NOTE: the active_period range is '[)' by default, so records with upper() = > CURRENT_TIMESTAMP are not visible with @> CURRENT_TIMESTAMP restriction.
Is that really what you intend/want though? For records with upper() = CURRENT_TIMESTAMP to not be visible? You are able to change the range returned from tstzrange by specifying what you want, eg:
Yeah, think of it like a delete. Once a record is deleted you want it to disappear. From the users point of view, who doesn't have time-travel privileges, an UPDATE to upper() = CURRENT_TIMESTAMP should disappear from any actions that take place later in the transaction.
A more common way of implementing this is an archive table. Have a DELETE trigger and shuffle the record to another storage area but with many dependent tuples via foreign key this can be very time consuming. Flipping a time period is consistently fast with the caveat that SELECT pays a price.
If you decide Pg shouldn't allow a user to make a tuple disappear, I would probably make a DO INSTEAD SECURITY DEFINER function that triggers on DELETE for that role only and changes the time range. Reality is after about 1 week for customers to contact their account administrator and say "I accidentally deleted X" it would likely be moved to an archive structure.
select tstzrange(current_timestamp, current_timestamp, '[]'); > CREATE A TABLE t (id integer, active_period tstzrange NOT NULL DEFAULT > tstzrange(current_timestamp, NULL));
Why NULL instead of 'infinity'...?
Diskspace. NULL works (almost) the same as infinity but the storage is quite a bit smaller.
> -- Disallowed due to hide_old_select policy. > UPDATE t SET active_period = tstzrange(lower(active_period), > CURRENT_TIMESTAMP);
Guess I'm still trying to figure out if you really intend for this to make the records invisible to the 'most users' case.
Yep. It's equivalent to a DELETE or DEACTIVATE. RLS may not be the right facility but it was very close to working exactly the way I wanted in FOR ALL mode.