Обсуждение: Really unique session ID - PID + connection timestamp?

Re: Durumdara 2016-04-09 <CAEcMXhkyUMmT3wiKnyWwSK1Xv3gFot46AoTM+6JxWhya3dGWjg@mail.gmail.com>
> 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.
>
> We want create same 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?

Btw, what you are describing is exactly what %c in log_line_prefix
does.

Christoph

On 4/9/2016 1:30 AM, Durumdara wrote:
> In MS we had...

If you want Microsoft's unique version of SQL, run Microsoft SQL. That
stuff you describe is a whole bunch of implementation specific wierdness
from the standpoint of someone outside, looking in..




--
john r pierce, recycling bits in santa cruz

Greetings,

* Durumdara (durumdara@gmail.com) wrote:
> Where I can get timestamp or some other unique data with I can create a
> combined primary key?

Not entirely sure about the rest of it, but if you want backend start
time, you can look at pg_stat_activity (which also happens to have the
pid).

If you want just your own, combine it with pg_backend_pid, as in:

select
  pid || ',' || backend_start
from pg_stat_activity where pid = pg_backend_pid();

Thanks!

Stephen

* Christoph Berg (cb@df7cb.de) wrote:
> Re: Durumdara 2016-04-09 <CAEcMXhkyUMmT3wiKnyWwSK1Xv3gFot46AoTM+6JxWhya3dGWjg@mail.gmail.com>
> > 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.
> >
> > We want create same 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?
>
> Btw, what you are describing is exactly what %c in log_line_prefix
> does.

That's not currently exposed at an SQL level anywhere though, is it?
Perhaps we should add a way to get that.  Another thought would be to
essentially expose 'log_line_prefix()' at the SQL level but I'm not sure
that generally makes sense.  We could also have an explicit function to
get MyStartTime, similar to pg_backend_pid().

Thanks!

Stephen

Re: Stephen Frost 2016-04-09 <20160409115712.GT10850@tamriel.snowman.net>
> > Btw, what you are describing is exactly what %c in log_line_prefix
> > does.
>
> That's not currently exposed at an SQL level anywhere though, is it?
> Perhaps we should add a way to get that.  Another thought would be to
> essentially expose 'log_line_prefix()' at the SQL level but I'm not sure
> that generally makes sense.  We could also have an explicit function to
> get MyStartTime, similar to pg_backend_pid().

I don't see how log_line_prefix() would make sense, but
pg_session_identifier() (= %c) would make sense to have, in the same
way that we have pg_backend_pid(), so the current session could be
located in the logs. (Not sure if pg_session_identifier shouldn't also
be a pg_stat_activity column, but that might be overkill. Maybe
SELECT pg_session_identifier(pid) from pg_stat_activity; ?)

MyStartTime is probably too specific.

Christoph

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.klaver@aklaver.com

Christoph Berg <cb@df7cb.de> writes:
> I don't see how log_line_prefix() would make sense, but
> pg_session_identifier() (= %c) would make sense to have, in the same
> way that we have pg_backend_pid(), so the current session could be
> located in the logs. (Not sure if pg_session_identifier shouldn't also
> be a pg_stat_activity column, but that might be overkill. Maybe
> SELECT pg_session_identifier(pid) from pg_stat_activity; ?)

I do not think we should get into the business of providing something
called a "unique session identifier" when in fact there is no uniqueness
guarantee.  timestamp + PID might be close enough for some uses, but it
won't be unique across multiple servers.  It has hazards if the server's
clock goes backwards.  It has hazards due to limited precision of the
timestamp (who's to say the OS wouldn't reissue the same PID within one
second?).

You can get your PID and session start time already from pg_stat_activity,
as Stephen pointed out; and what's more that technique works today on
all supported branches.  If you want something a bit notationally cleaner
you can wrap it up in a user-defined function to produce a session ID.

I think we should just leave things as they are; people for whom
timestamp + PID is good enough as a session ID already have a solution,
and we should not mislead those for whom it isn't.

            regards, tom lane

> On 10 Apr 2016, at 9:07, Durumdara <durumdara@gmail.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/RecordIDprikey). 
>
> 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
(seemy 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-controlledlock 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
andhandles 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
himto 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
adatabase that provides certain features to make up for the lack of others that are harder to implement. Both MS Access
andFirebird 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
changesare dangerous (the backward version). In PG we can extend our "guid" with IP and Port too, and this could be
enoughsafe for us. 

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

> Thanks
>
>
>
>
> 2016-04-09 16:05 GMT+02:00 Adrian Klaver <adrian.klaver@aklaver.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.klaver@aklaver.com
>

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

Dear Alban!


2016.04.10. 13:05 keltezéssel, Alban Hertroys írta:
>> On 10 Apr 2016, at 9:07, Durumdara <durumdara@gmail.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/RecordIDprikey). 
>>
>> 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. 

This is not real locks. They are logical locks.
Products, offers are edited for long time.
But we must save subdata. This is not a "word like document" which can
saved at once, in a transaction.
When a product edited, we must protect it from other user's edit.
But it's subdata must be posted/commited to the DB, for example
shipping, article quantity changes, vouchers, etc.



>
> This sounds much more like a use-case for sub-transactions and select for update (which puts a temporary
RDBMS-controlledlock on the relevant records) than for manual locking. 

Yes, this is like sub-transaction.
But for only sub-data. The main data must be edited by only the first
user who started the edit.
This is a long time "lock" like thing. This what we simulate here.

Thanks for your suggestions. I will check this in our client library.

dd

On 04/10/2016 06:29 AM, durumdara@gmail.com wrote:
>
> Dear Alban!
>
>
> 2016.04.10. 13:05 keltezéssel, Alban Hertroys írta:
>>> On 10 Apr 2016, at 9:07, Durumdara <durumdara@gmail.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.
>
> This is not real locks. They are logical locks.
> Products, offers are edited for long time.

Define long time, a session, a day, days, etc?

> But we must save subdata. This is not a "word like document" which can
> saved at once, in a transaction.
> When a product edited, we must protect it from other user's edit.
> But it's subdata must be posted/commited to the DB, for example
> shipping, article quantity changes, vouchers, etc.

So folks can make changes to the attributes of a Product, Offer, etc
while it is being changed in ways they can not see?

Or do they get a read only view that changes as the 'locking' user makes
edits?

>
>
>
>>
>> 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.
>
> Yes, this is like sub-transaction.
> But for only sub-data. The main data must be edited by only the first
> user who started the edit.
> This is a long time "lock" like thing. This what we simulate here.
>
> Thanks for your suggestions. I will check this in our client library.

To be clear you are trying to come up with a solution that allows your
application to run against different databases(Firebird, SQL Server,
Postgres, etc?), using a single code base, correct?

>
> dd
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com