On Fri, Apr 14, 2017 at 9:09 AM, Stephen Frost <sfr...@snowman.net> wrote:
> Rod, > > * Rod Taylor (rod.tay...@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. -- Rod Taylor
