Re: Really unique session ID - PID + connection timestamp?

> On 10 Apr 2016, at 9:07, Durumdara <durumdara(at)gmail(dot)com> wrote:
>
> Dear Adrian!
>
> Again. As I see the beginning blocks are removed by mailing system in the code.
>
> We have an "ourlocks" table which hold records (TableName, RecordID, SessionInnerID, TimeStamp, etc, with TableName/RecordID prikey).
>
> If anybody wants to lock record "for long time", "over the transactions" it try to insert a new record here.

Why are those records being locked? Reading on, it seems like you're trying to solve a fairly standard concurrency problem. Any RDBMS worth their salt can handle that for you, you don't need to manually do any of that.

> If other process want to lock same record, it can see this record (or got violation error), so it stopped.
>
> This is not for protect all tables, only for protect main entities have many subtables like "Products", "Offers", etc.
> We can't use transactions, because in the editor they must post/commit subdata.
> And because PG is different from other DBs, so if ANY of statements failed, it rollback whole thing automatically (see my prior mail).
> In FireBird we can do simple record lock protection with main connection's second transaction, but in MS and PG not.

This sounds much more like a use-case for sub-transactions and select for update (which puts a temporary RDBMS-controlled lock on the relevant records) than for manual locking.
See: http://www.postgresql.org/docs/9.5/static/sql-begin.html and http://www.postgresql.org/docs/9.5/static/sql-select.html

You might also want to look into transaction isolation levels: http://www.postgresql.org/docs/9.5/interactive/mvcc.html

As an example of how a concurrent workflow with the above goes:

Session 1:
begin;
savepoint offer_update;
select product_id from offers where offer_id = 1234567 for update;

Session 2:
begin;
savepoint offer_update;
select product_id from offers where offer_id = 1234567 for update;
update offers set discount = 0.10 where product_id = 1234567;
# ERROR (the record is locked by session 1)
rollback to offer_update;

Session 1:
update offers set discount = 0.15 where product_id = 1234567;
# success
commit;

Session 2: (retrying earlier update)
select product_id from offers where offer_id = 1234567 for update;
update offers set discount = 0.10 where product_id = 1234567;
# success
commit;

You'll need to add some logic to your application (that editor you were talking about) so that it inserts savepoints and handles failures of sub-transactions appropriately.

> So we used real records in a real table. But how to clean if client disconnected without delete own records?
> For this we created own sessioninfo table with inner id, user id, timestamp, and [connectionid, connectiontime].
> The locking mechanism checks for same lock (Offer, 117), if anybody locks the record, it checks for he's on or not.
> If active connection (ID + TS) then lock is valid, and we can show information that "who is editing, please ask for him to release, etc.".
> If not, we can eliminate the record and insert our.

It sounds to me like you're complicating your code where you could be simplifying it. Possibly, because you're used to a database that provides certain features to make up for the lack of others that are harder to implement. Both MS Access and Firebird are very much file-based desktop databases that are not really meant for concurrent access. The big RDBMSes (PG, MS SQL server, Oracle, DB2) are _designed_ for such workloads.

> The main goal is to protect the main entities. It is working in MS.
> My question was about how to get my client's connection timestamp as get_backend_pid.
> But as you wrote I can get it from activity log. Because PID can't be same as mine, I can select my from the table.
>
> You said it have danger (like guid repetition). Yes, it have. And not the BAD SYSADMIN, but the summer/winter time changes are dangerous (the backward version). In PG we can extend our "guid" with IP and Port too, and this could be enough safe for us.

In that case you should at least use UTC timestamps. Still, with such an implementation it will be hard to create a reliable system.

> Thanks
>
>
>
>
> 2016-04-09 16:05 GMT+02:00 Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>:
> On 04/09/2016 01:30 AM, Durumdara wrote:
> Dear Everybody!
>
>
> In MS we had a "persistent lock" structure and method.
> This over transactions because based on real records in a real table
> with pri key (tablename + id).
>
> For garbaging we had a special session info.
> In MS the session id is smallint, so it can repeats after server
> restarts, but my coll. found a "session creation timestamp".
> This is a key which unique.
> With this we can check for died sessions and we can clean their records.
>
> It might help to explain more what it is you are trying to achieve.
>
> First I am not sure what you mean by 'persistent lock', especially as it applies to Postgres?
>
> Second, I assume by garbaging you mean garbage collection of something?
> If that is the case what exactly are you garbage collecting?
> I see 'clean records', what records would these be?
> In particular, on Postgres, where are you going to do this?
>
>
> We want create same mechanism.
>
> If the above questions did not already cover this, what mechanism?
>
>
> I know there are adv. locks in PG, but I want to use session id.
>
> This could be:
> |pg_backend_pid|()
>
> May pid repeats.
> Where I can get timestamp or some other unique data with I can create a
> combined primary key?
>
> Thanks for your help!
>
> dd
>
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.