Re: [HACKERS] Can pessimistic locking be emulated?

2003-02-28 Thread Christoph Haller
> > Just as a suggestion: In most of my applications, we have a security layer > which is implemented through server-side functions. These functions keep a > table updated which contains: > > lock_table > record_id > lock_user > time_locked That's an excellent and even portable idea. > > This all

Re: [HACKERS] Can pessimistic locking be emulated?

2003-02-27 Thread Josh Berkus
Merlin, Just as a suggestion: In most of my applications, we have a security layer which is implemented through server-side functions. These functions keep a table updated which contains: lock_table record_id lock_user time_locked This allows us to avoid nasty "your update cannot be processe

Re: [HACKERS] Can pessimistic locking be emulated?

2003-02-27 Thread Merlin Moncure
This directly answers my question (wasn't previously aware that xid could be queried out in such a useful fashion). Not only does this accomplish what I need, but now allows me to not use select ... for update and stick with a transaction based locking mechanism. The 'Why' isn't that interesting

Re: [HACKERS] Can pessimistic locking be emulated?

2003-02-27 Thread Rod Taylor
On Thu, 2003-02-27 at 15:02, Tom Lane wrote: > Rod Taylor <[EMAIL PROTECTED]> writes: > > Agreed -- but they're stored on the row themselves. You might be able > > to write a function which executes dirty reads on the table and tells > > you if the row is locked or not, but it's not going to be si

Re: [HACKERS] Can pessimistic locking be emulated?

2003-02-27 Thread Tom Lane
Rod Taylor <[EMAIL PROTECTED]> writes: > Agreed -- but they're stored on the row themselves. You might be able > to write a function which executes dirty reads on the table and tells > you if the row is locked or not, but it's not going to be simple. Actually, I don't think you need a dirty read

Re: [HACKERS] Can pessimistic locking be emulated?

2003-02-27 Thread Rod Taylor
> In my case, being able to view outstanding row level locks would be > enormously useful. I'm assuming this is not possible for structural or Agreed -- but they're stored on the row themselves. You might be able to write a function which executes dirty reads on the table and tells you if the ro

Re: [HACKERS] Can pessimistic locking be emulated?

2003-02-27 Thread Tom Lane
"Merlin Moncure" <[EMAIL PROTECTED]> writes: > In my case, being able to view outstanding row level locks would be > enormously useful. The only way to do that would be to grovel through every table in the database, looking for rows that are marked locked by transactions that are still alive. > A

Re: [HACKERS] Can pessimistic locking be emulated?

2003-02-27 Thread Merlin Moncure
I was referring to 10.3 in the administrator's guide, regarding the pg_lock view. According to the documentation, the view only contains table level locks. However, the view also contains an xid for transactions. The unclear part, at least to me, was what the role of the xid was in the view and

Re: [HACKERS] Can pessimistic locking be emulated?

2003-02-27 Thread Christoph Haller
> > That's my fallback position. Obviously, this will lead to false > positives depending on server load. In my case, I'm targeting between > 30-50 users so its likely to throw timeouts for various reasons other > than locks even though my queries of interest are generally select a > from b wher

Re: [HACKERS] Can pessimistic locking be emulated?

2003-02-27 Thread Merlin Moncure
That's my fallback position. Obviously, this will lead to false positives depending on server load. In my case, I'm targeting between 30-50 users so its likely to throw timeouts for various reasons other than locks even though my queries of interest are generally select a from b where id = c type

Re: [HACKERS] Can pessimistic locking be emulated?

2003-02-27 Thread Christoph Haller
> > I am trying to emulate a pessimistic locking system you would find in an > old school database file system, for example cobol. Generally, when a > cobol program tries to read a record that is locked by somebody else, > the read fails and either a message is displayed by the user or a error >

[HACKERS] Can pessimistic locking be emulated?

2003-02-26 Thread Merlin Moncure
I am trying to emulate a pessimistic locking system you would find in an old school database file system, for example cobol. Generally, when a cobol program tries to read a record that is locked by somebody else, the read fails and either a message is displayed by the user or a error handling proc