Обсуждение: "Locking rows"

I have a customer requirement/desire. The system is (among other things)
essentially a employee time sheet. The manager wants for an employee to not
be able to modify a given row in the table they enter time into once it is
committed. I personally see issues with this, but I am willing to try to give
him what he wants. If it creates issues we can sort them out, once he sees
the issues.

The only way I see to do this is to add a column (call it lock). I will
then set this up as a default entry with a value of TRUE. The form the
employee uses to enter this will, of course, not display this column. The
I will create a function that on UPDATE, checks for the presence of the 1 in
this row, and rejects the update. Then I will give the manager a form where
he can set this flag to FALSE.

Looks ugly, and convulsed to me. 

Is here a more "database native" way to handle this?


-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
                        -- Benjamin Franklin

On 8/12/19 10:51 AM, stan wrote:
> I have a customer requirement/desire. The system is (among other things)
> essentially a employee time sheet. The manager wants for an employee to not
> be able to modify a given row in the table they enter time into once it is
> committed. I personally see issues with this, but I am willing to try to give
> him what he wants. If it creates issues we can sort them out, once he sees
> the issues.
> 
> The only way I see to do this is to add a column (call it lock). I will
> then set this up as a default entry with a value of TRUE. The form the
> employee uses to enter this will, of course, not display this column. The
> I will create a function that on UPDATE, checks for the presence of the 1 in
> this row, and rejects the update. Then I will give the manager a form where
> he can set this flag to FALSE.
> 
> Looks ugly, and convulsed to me.
> 
> Is here a more "database native" way to handle this?

Depends on who is doing the database record changes.

In other words are there defined roles:

https://www.postgresql.org/docs/11/sql-createrole.html

for the object(table) and the entity working with the table?



-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 8/12/19 1:07 PM, stan wrote:
> On Mon, Aug 12, 2019 at 12:14:25PM -0700, Adrian Klaver wrote:
>> On 8/12/19 10:51 AM, stan wrote:
>>> I have a customer requirement/desire. The system is (among other things)
>>> essentially a employee time sheet. The manager wants for an employee to not
>>> be able to modify a given row in the table they enter time into once it is
>>> committed. I personally see issues with this, but I am willing to try to give
>>> him what he wants. If it creates issues we can sort them out, once he sees
>>> the issues.
>>>
>>> The only way I see to do this is to add a column (call it lock). I will
>>> then set this up as a default entry with a value of TRUE. The form the
>>> employee uses to enter this will, of course, not display this column. The
>>> I will create a function that on UPDATE, checks for the presence of the 1 in
>>> this row, and rejects the update. Then I will give the manager a form where
>>> he can set this flag to FALSE.
>>>
>>> Looks ugly, and convulsed to me.
>>>
>>> Is here a more "database native" way to handle this?
>>
>> Depends on who is doing the database record changes.
>>
>> In other words are there defined roles:
>>
>> https://www.postgresql.org/docs/11/sql-createrole.html
>>
>> for the object(table) and the entity working with the table?
>>
> OK, maybe I could set up the "user" role, such that it does not have the
> UPDATE permission on this table, just the INSERT one? Of course the "admin"
> role would have the UPDATE attribute for this table.

'user' should also have SELECT if you want them to see their records.

> 
> Make sense?

Yes.

> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com