Thread: Question -- Session Operations - Feasibility Of Proposed Synchronization Method?

QUESTION: In supervisory process step sup.2 (below), will the command:

 

   LOCK TABLE sql_table_01 IN ACCESS EXCLUSIVE MODE;

 

ensure that the session process, having read a value from sequence generator sql_sequence_01 in step ses.1, will never ever begin to execute step ses.6:

 

   SELECT currval('sql_sequence_01');

 

so long as the supervisory process, has completed step sup.2:

 

   LOCK TABLE sql_table_01 IN ACCESS EXCLUSIVE MODE;

 

but has not yet completed step sup.4:

 

   COMMIT TRANSACTION;

 

???

• Attachment <eto_sql_pg - Session Context Storage - 8.1 Synchronize Process Access To Table - 20160103.odt>

On 01/03/2016 01:32 PM, Melvin Davidson wrote:
> As others have pointed out, storing session data in a table is not a
> good idea. Even if you use TRUNCATE, you will still not reclaim all the
> space used unless you use vacuum full. More to the point, if you

Actually:

http://www.postgresql.org/docs/9.4/interactive/sql-truncate.html

"TRUNCATE quickly removes all rows from a set of tables. It has the same
effect as an unqualified DELETE on each table, but since it does not
actually scan the tables it is faster. Furthermore, it reclaims disk
space immediately, rather than requiring a subsequent VACUUM operation.
This is most useful on large tables."

the rub is:

"TRUNCATE acquires an ACCESS EXCLUSIVE lock on each table it operates
on, which blocks all other concurrent operations on the table. When
RESTART IDENTITY is specified, any sequences that are to be restarted
are likewise locked exclusively. If concurrent access to a table is
required, then the DELETE command should be used instead."


> absolutely must store session data, then why not just do it in a
> TEMPORARY table, which will reside in memory and clean itself up when
> the session ends?
>
> http://www.postgresql.org/docs/9.4/static/sql-createtable.html
>
>
> On Sun, Jan 3, 2016 at 3:43 PM, Pavel Stehule <pavel.stehule@gmail.com
> <mailto:pavel.stehule@gmail.com>> wrote:
>
>     Hi
>
>     2016-01-03 20:46 GMT+01:00 Steve Petrie, P.Eng.
>     <apetrie@aspetrie.net <mailto:apetrie@aspetrie.net>>:
>
>         __
>         *Greetings To Postgres Forum,*
>         This posting is further to a prior forum thread -- subject
>         "[/GENERAL] using a postgres table as a multi-writer
>         multi-updater queue/", that was started on 23 November 2015 by
>         Chris Withers chris@simplistix.co.uk
>         <mailto:chris@simplistix.co.uk>. I believe the last posting to
>         that thread was on 1 December 2015 by George Neuner
>         <gneuner2@comcast.net <mailto:gneuner2@comcast.net>>.
>         A related thread of interest, was started earlier -- subject
>         /"[GENERAL] using postgresql for session/",  on 7 October 2015
>         by John Tiger <john.tigernassau@gmail.com
>         <mailto:john.tigernassau@gmail.com>>.
>
>
>     I am sorry for off topic. But are you sure, so using Postgres for
>     session data is a good idea? Using Postgres for short living data is
>     can enforce a performance problems when a load will be higher.
>
>     Regards
>
>     Pavel
>
>         * * *
>         * * *
>         I made some postings to the first above-mentioned thread, as I
>         am working to migrate a php website application from mysql to
>         postgres. An important objective of this migration is to find a
>         good way to use a postgres table to store session context data
>         rows, one row for each active website visitor.
>         One item of advice (among much other helpful advice) I took away
>         from the first thread mentioned above, was to avoid use of
>         DELETE commands as a means to recycle session context table row
>         image storage, when a session is terminated.
>         To use instead, a TRUNCATE command on an entire session context
>         table, to quickly and efficiently recycle session context row
>         image storage space, back to the filesystem, so the space is
>         immediately available for reuse.
>         * * *
>         * * *
>         Since then, I have been working to design a way to use postgres
>         table(s) as a session context store, for a simple, reliable and
>         high-performance "session operations system" (SOS).
>         A design for a postgres-based SOS, that follows two key
>         principles to ensure maximum session workload throughput capacity:
>         *PRINCIPLE #1*: *1.1* Use only the TRUNCATE TABLE command, to
>         recycle frequently, rapidly and efficiently back to the
>         filesystem, session context table storage space occupied by
>         obsolete images of session context rows; and *1.2* do not use
>         DELETE / AUTOVACUUM / VACUUM commands at all, for this recycling.
>         *PRINCIPLE #2*: *2.1* Use sequence generators for various
>         globally-addressable fast-access "iterators"**, that provide the
>         php website app (and its PL/pgSQL functions), with e.g. access
>         to an appropriate individual session context table; *2.2* Access
>         granted to a table from a pool of session context tables, each
>         pool having its tables all in the same operational state.
>         The downside of Principle #1 is the considerable added
>         complexity of having to manage multiple tables, to store session
>         context data rows.
>         The downside of Principle #2 is that the sequence generator has
>         no role in sql transaction / savepoint semantics. So explicit
>         provision for synchronization is required, adding further
>         complexity.
>           (** An "iterator" is derived from a sequence generator,
>         by using excess unneeded precision in high-order bits of the
>         sequence integer value, to encode "iterator" metadata -- as an
>         efficient way to make this metadata available to multiple
>         concurrently executing app execution control flow paths.)
>         * * *
>         * * *
>         *The purpose of this present email, is to present (in
>         pseudocode) for critque by forum members, a proposed approach to
>         synchronizing use of the "iterators" (sequence generators)
>         described above, among multiple concurrent actors, in the
>         website php app session operations scenario.*
>         Since I am a postgres novice, I am hoping that members of this
>         postgres forum, will be kind enough to examine and critique the
>         (boiled-down, simplified) pseudocode for the proposed approach
>         to synchronization.
>         (In this discussion, the term "process" does not refer
>         specifically to a "process" as implemented in operating systems,
>         as one form of program execution control, that is contrasted
>         with "thread" as another form of program execution control. In
>         this discussion, the term "process" means the general sense of
>         any program execution path that can occur in parallel
>         concurrently with other program execution paths.)
>         In the pseudocode example provided below, two concurrent
>         processes (session process, supervisory process) operate on the
>         same same table *sql_table_01*, and they use sequence generator
>         *sql_sequence_01* as a "version" number for the operational
>         state of table *sql_table_01*.
>
>             *QUESTION: In _supervisory process_ step sup.2 (below), will
>             the command:*
>             **
>             */LOCK TABLE sql_table_01 IN ACCESS EXCLUSIVE MODE;/*
>             **
>             *ensure that the _session process_, having read a value from
>             sequence generator sql_sequence_01 in step ses.1, will
>             _never ever begin to execute step ses.6_:*
>             **
>             */SELECT currval('sql_sequence_01');/*
>             **
>             *so long as the supervisory process, has _completed step
>             sup.2:_*
>             **
>             */LOCK TABLE sql_table_01 IN ACCESS EXCLUSIVE MODE;/*
>             *__*
>             *_but has not yet completed step sup.4_:*
>             **
>             */COMMIT TRANSACTION;/*
>             **
>             *???*
>
>         Essentially, the idea is to piggyback, synchronization of the
>         use of sequence generator *sql_sequence_01*, on the suprvisory
>         process' LOCK TABLE */sql_table_01/* command, assuming that the
>         session process has some INSERT / SELECT / UPDATE command to
>         perform on the same table (a command that will be blocked by the
>         LOCK TABLE command).
>         * * *
>         * * *
>         Here is pseudocode for the _session process_ (use a wide viewing
>         window to avoid line wrap):
>         *Session Process
>         * ---------------
>                 INSERT / SELECT / UPDATE row in table *sql_table_01
>         *-------------------------------------------------------------
>         |
>         *ses.0* |(Decide to update a row in table *sql_table_01*).
>         |
>         *ses.1* | /SELECT currval(*'sql_sequence_01*');/
>         /ses.2/ | $save_seq1 = (value of sequence obtained in *ses.1*);
>         |
>         *ses.3* | /SAVEPOINT *session_savepoint*;
>         / |
>         *ses.4* | /SELECT ... FROM *sql_table_01* FOR UPDATE;
>         /      |
>         *ses.5* | /UPDATE *sql_table_01* ...;
>         /      |
>         *ses.6* | /SELECT currval(*'sql_sequence_01*');
>         /*ses.7* |  $save_seq2 = (value of seq obtained in ses.6);
>                |
>         |  /*
>                |     IS IT SAFE TO COMMIT THE UNIT OF WORK ?
>         |      (i.e. is operational state of table
>         | *sql_table_01* unchanged?)
>                | */
>         *ses.8* |  if ($save_seq1 == $save_seq2)
>                | /*
>                |     YES -- SAFE TO COMMIT
>                |      ( sequence *sql_sequence_01* is unchanged).
>                | */
>                |  {
>         *ses.9* | /RELEASE SAVEPOINT *session_savepoint*;
>         / |  }
>                | else
>                | /*
>                |     NO -- NOT SAFE TO COMMIT
>                | (sequence *sql_sequence_01* has changed
>         |         abandon unit of work and retry).
>                | */
>                | {
>         *ses.10*| /ROLLBACK TO SAVEPOINT *session_savepoint*;
>         / |  }
>                |
>         | /* DONE */
>                |
>         -------------------------------------------------------------
>         * * *
>         * * *
>         Here is pseudocode for the _supervisoty process_ (use a wide
>         viewing window to avoid line wrap):
>
>         *Supervisory Process
>         * -------------------
>             Change operational state of table sql_table_01
>         -------------------------------------------------------------
>         |
>         *sup.0* | (Decide to change operational state of table
>                 | *sql_table_01*).
>         |
>         *sup.1* | /BEGIN TRANSACTION;/
>         |
>                 | /*
>                 |    Block all other access to table sql_table_01.
>                 | */
>         *sup.2* | /LOCK TABLE sql_table_01 IN ACCESS EXCLUSIVE MODE;
>         /       | ...
>                 | ... (change operational state of table sql_table_01
>                 | ...
>                 | ...  e.g. /TRUNCATE ONLY TABLE sql_table_01;/)
>                 | ...
>         |
>                 | /*
>                 |   Advance sequence
>                 | *sql_sequence_01
>         * |    to indicate that the operational state of table
>                 | *sql_table_01
>         * |     has changed.
>                 | */
>                 |
>         *sup.3* | /SELECT nextval('*sql_sequence_01'*);
>         / |
>                 | /*
>                 |    Release the EXCLUSIVE MODE lock on table
>         | sql_table_01.
>                 | */
>         *sup.4* | /COMMIT TRANSACTION;
>         / |
>                 | /* DONE */
>         |
>         -------------------------------------------------------------
>         * * *
>         * * *
>         I attach a PDF with the pseudocode given above.
>
>           * Attachment <eto_sql_pg - Session Context Storage - 8.1
>             Synchronize Process Access To Table - 20160103.odt>
>
>         The design document for the session operations system (SOS) is
>         well advanced, but  not yet ready for general distribution. If
>         a forum member would like to see a copy of the design document
>         in its present draft state, please feel free to email me offline
>         to request a PDF copy.
>         Thanks and Regards,
>         *Steve*
>
>
>         --
>         Sent via pgsql-general mailing list
>         (pgsql-general@postgresql.org <mailto:pgsql-general@postgresql.org>)
>         To make changes to your subscription:
>         http://www.postgresql.org/mailpref/pgsql-general
>
>
>
>
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.


--
Adrian Klaver
adrian.klaver@aklaver.com

On 1/3/2016 1:32 PM, Melvin Davidson wrote:
> As others have pointed out, storing session data in a table is not a
> good idea. Even if you use TRUNCATE, you will still not reclaim all
> the space used unless you use vacuum full. More to the point, if you
> absolutely must store session data, then why not just do it in a
> TEMPORARY table, which will reside in memory and clean itself up when
> the session ends?

The fundamental problem is that HTTP itself is stateless, and user
sessions have to be enforced by the web app, with the aid of browser
cookies.   you can't simply directly link a http connection to a SQL
connection and assume thats a session.

--
john r pierce, recycling bits in santa cruz

Thanks to Pavel for your remarks. My reply is posted below.

Steve

----- Original Message -----
From: "Pavel Stehule" <pavel.stehule@gmail.com>
To: "Steve Petrie, P.Eng." <apetrie@aspetrie.net>
Cc: <pgsql-general@postgresql.org>
Sent: Sunday, January 03, 2016 3:43 PM
Subject: Re: [GENERAL] Question -- Session Operations - Feasibility Of
Proposed Synchronization Method?


> Hi
>
> 2016-01-03 20:46 GMT+01:00 Steve Petrie, P.Eng. <apetrie@aspetrie.net>:
>
>> *Greetings To Postgres Forum,*
>>
>> This posting is further to a prior forum thread -- subject "[*GENERAL]
>> using a postgres table as a multi-writer multi-updater queue*", that was
>> started on 23 November 2015 by Chris Withers chris@simplistix.co.uk. I
>> believe the last posting to that thread was on 1 December 2015 by George
>> Neuner <gneuner2@comcast.net>.
>>
>> A related thread of interest, was started earlier -- subject *"[GENERAL]
>> using postgresql for session*",  on 7 October 2015 by John Tiger <
>> john.tigernassau@gmail.com>.
>>
>
> I am sorry for off topic. But are you sure, so using Postgres for session
> data is a good idea? Using Postgres for short living data is can enforce a
> performance problems when a load will be higher.
>

You are not off topic. Not at all. The objective is to find a design for a
session operations system based on postgres, that eliminates performance
problems
under high load, that you mention.

Get the benefits of using postgres as the session context store
facility. But use postgres in a way that is optimized for handling smoothly
and efficiently, large workload volumes of  INSERT / SELECT / UPDATE
commands, on short-lived transient session data rows,
under long intense bursts of app session activity.

> Regards
>
> Pavel
>
>
>>
>> * * *
>> * * *
>>
>> I made some postings to the first above-mentioned thread, as I am working
>> to migrate a php website application from mysql to postgres. An important
>> objective of this migration is to find a good way to use a postgres table
>> to store session context data rows, one row for each active website
>> visitor.
>>
>> One item of advice (among much other helpful advice) I took away from the
>> first thread mentioned above, was to avoid use of DELETE commands as a
>> means to recycle session context table row image storage, when a session
>> is terminated.
>>
>> To use instead, a TRUNCATE command on an entire session context table, to
>> quickly and efficiently recycle session context row image storage space,
>> back to the filesystem, so the space is immediately available for reuse.
>>
>> * * *
>> * * *
>>
>> Since then, I have been working to design a way to use postgres table(s)
>> as a session context store, for a simple, reliable and high-performance
>> "session operations system" (SOS).
>>
>> A design for a postgres-based SOS, that follows two key principles to
>> ensure maximum session workload throughput capacity:
>>
>> *PRINCIPLE #1*: *1.1* Use only the TRUNCATE TABLE command, to recycle
>> frequently, rapidly and efficiently back to the filesystem, session
>> context
>> table storage space occupied by obsolete images of session context rows;
>> and *1.2* do not use DELETE / AUTOVACUUM / VACUUM commands at all, for
>> this recycling.
>>
>> *PRINCIPLE #2*: *2.1* Use sequence generators for various
>> globally-addressable fast-access "iterators"**, that provide the php
>> website app (and its PL/pgSQL functions), with e.g. access to an
>> appropriate individual session context table; *2.2* Access granted to a
>> table from a pool of session context tables, each pool having its tables
>> all in the same operational state.
>>
>> The downside of Principle #1 is the considerable added complexity of
>> having to manage multiple tables, to store session context data rows.
>>
>> The downside of Principle #2 is that the sequence generator has no role
>> in
>> sql transaction / savepoint semantics. So explicit provision for
>> synchronization is required, adding further complexity.
>>
>>  (** An "iterator" is derived from a sequence generator, by using excess
>> unneeded precision in high-order bits of the sequence integer value, to
>> encode "iterator" metadata -- as an efficient way to make this metadata
>> available to multiple concurrently executing app execution control flow
>> paths.)
>>
>> * * *
>> * * *
>>
>> *The purpose of this present email, is to present (in pseudocode) for
>> critque by forum members, a proposed approach to synchronizing use of the
>> "iterators" (sequence generators) described above, among multiple
>> concurrent actors, in the website php app session operations scenario.*
>>
>> Since I am a postgres novice, I am hoping that members of this postgres
>> forum, will be kind enough to examine and critique the (boiled-down,
>> simplified) pseudocode for the proposed approach to synchronization.
>>
>> (In this discussion, the term "process" does not refer specifically to a
>> "process" as implemented in operating systems, as one form of program
>> execution control, that is contrasted with "thread" as another form of
>> program execution control. In this discussion, the term "process" means
>> the
>> general sense of any program execution path that can occur in parallel
>> concurrently with other program execution paths.)
>>
>> In the pseudocode example provided below, two concurrent processes
>> (session process, supervisory process) operate on the same same table
>> *sql_table_01*, and they use sequence generator *sql_sequence_01* as a
>> "version" number for the operational state of table *sql_table_01*.
>>
>> *QUESTION: In supervisory process step sup.2 (below), will the command:*
>>
>> *   LOCK TABLE sql_table_01 IN ACCESS EXCLUSIVE MODE;*
>>
>> *ensure that the session process, having read a value from sequence
>> generator sql_sequence_01 in step ses.1, will never ever begin to execute
>> step ses.6:*
>>
>> *   SELECT currval('sql_sequence_01');*
>>
>> *so long as the supervisory process, has completed step sup.2:*
>>
>> *   LOCK TABLE sql_table_01 IN ACCESS EXCLUSIVE MODE;*
>>
>> *but has not yet completed step sup.4:*
>>
>> *   COMMIT TRANSACTION;*
>>
>> *???*
>>
>>
>> Essentially, the idea is to piggyback, synchronization of the use
>> of sequence generator *sql_sequence_01*, on the suprvisory process' LOCK
>> TABLE *sql_table_01* command, assuming that the session process has some
>> INSERT / SELECT / UPDATE command to perform on the same table (a command
>> that will be blocked by the LOCK TABLE command).
>>
>> * * *
>> * * *
>>
>> Here is pseudocode for the *session process* (use a wide viewing window
>> to avoid line wrap):
>>
>>
>> *Session Process*                       ---------------
>>        INSERT / SELECT / UPDATE row in table
>> *sql_table_01*
>> -------------------------------------------------------------
>>       |
>> *ses.0* |(Decide to update a row in table *sql_table_01*).
>>       |
>> *ses.1* |  *SELECT currval('sql_sequence_01');*
>> *ses.2* |  $save_seq1 = (value of sequence obtained in *ses.1*);
>>       |
>> *ses.3* |
>> *SAVEPOINT session_savepoint;*      |
>> *ses.4* |
>> *SELECT ... FROM  sql_table_01 FOR UPDATE;*      |
>> *ses.5* |
>> *UPDATE sql_table_01 ...;*      |
>> *ses.6* |
>> *SELECT currval('sql_sequence_01');**ses.7* |  $save_seq2 = (value of seq
>> obtained in ses.6);
>>       |
>>       |  /*
>>       |     IS IT SAFE TO COMMIT THE UNIT OF WORK ?
>>       |      (i.e. is operational state of table
>>       |        *sql_table_01* unchanged?)
>>       |  */
>> *ses.8* |  if ($save_seq1 == $save_seq2)
>>       |  /*
>>       |     YES -- SAFE TO COMMIT
>>       |      ( sequence *sql_sequence_01* is unchanged).
>>       |  */
>>       |  {
>> *ses.9* |
>> *RELEASE SAVEPOINT session_savepoint;*      |  }
>>       |  else
>>       |  /*
>>       |     NO -- NOT SAFE TO COMMIT
>>       |       (sequence *sql_sequence_01* has changed
>>       |         abandon unit of work and retry).
>>       |  */
>>       |  {
>> *ses.10*|
>> *ROLLBACK TO SAVEPOINT session_savepoint;*      |  }
>>       |
>>       | /* DONE */
>>       |
>>       -------------------------------------------------------------
>>
>> * * *
>> * * *
>>
>> Here is pseudocode for the *supervisoty process* (use a wide viewing
>> window to avoid line wrap):
>>
>>
>> *Supervisory Process*                  -------------------
>>    Change operational state of table sql_table_01
>> -------------------------------------------------------------
>>        |
>>  *sup.0* | (Decide to change operational state of table
>>        |    *sql_table_01*).
>>        |
>>  *sup.1* | *BEGIN TRANSACTION;*
>>        |
>>        | /*
>>        |    Block all other access to table sql_table_01.
>>        | */
>>  *sup.2* |
>> *LOCK TABLE sql_table_01 IN ACCESS EXCLUSIVE MODE;*       | ...
>>        | ... (change operational state of table sql_table_01
>>        | ...
>>        | ...  e.g. *TRUNCATE ONLY TABLE sql_table_01;*)
>>        | ...
>>        |
>>        | /*
>>        |   Advance sequence
>>        |
>> *sql_sequence_01*       |    to indicate that the operational state of
>> table
>>        |
>> *sql_table_01*       |     has changed.
>>        | */
>>        |
>>  *sup.3* |
>> *SELECT nextval('sql_sequence_01');*       |
>>        | /*
>>        |    Release the EXCLUSIVE MODE lock on table
>>        |       sql_table_01.
>>        | */
>>  *sup.4* |
>> *COMMIT TRANSACTION;*       |
>>        | /* DONE */
>>        |
>> -------------------------------------------------------------
>>
>> * * *
>> * * *
>>
>> I attach a PDF with the pseudocode given above.
>>
>>    - Attachment <eto_sql_pg - Session Context Storage - 8.1 Synchronize
>>    Process Access To Table - 20160103.odt>
>>
>> The design document for the session operations system (SOS) is well
>> advanced, but  not yet ready for general distribution. If a forum member
>> would like to see a copy of the design document in its present draft
>> state,
>> please feel free to email me offline to request a PDF copy.
>>
>> Thanks and Regards,
>>
>> *Steve*
>>
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>>
>

Thanks to Melvin for your remarks. My reply is posted below.

Steve

From: "Melvin Davidson"
Sent: Sunday, January 03, 2016 4:32 PM
> More to the point, if you absolutely must
> store session data, then why not just do it in a TEMPORARY table, which
> will reside in memory and clean itself up when the session ends?
>
> http://www.postgresql.org/docs/9.4/static/sql-createtable.html
>

Sorry for not having defined the term "session" in my original posting. Here
are quotes from the draft design document, for the proposed session
operations system:

"In this document, the term "session" does not refer to connections with a
postgres server through a postgres client, which connections are referred to
as "sessions" in postgres docs. In this document, the term "session" means a
dialog between an app and the consumer of that app, where the app may or may
not use postgres, for other than its session context data.

"The app that motivates this present proposed design for a postgres-based
session operations facility, is an Internet website application (HTML / HTTP
/ PHP / PostgreSQL) that provides services to a visitor using a web browser.

The "session" under discussion therefore, is used to maintain the
contextual data of the (visitor <=> app) dialog, during which dialog the app
will open and close numerous brief connections with the postgres client,
such connections also being referred to as "sessions" within postgres
docs.

So a TEMPORARY table will not suffice, but using a tablespace in SSHD for
the session operations tables, might yield performance gains like
TEMPORARY..

>
> On Sun, Jan 3, 2016 at 3:43 PM, Pavel Stehule <pavel.stehule@gmail.com>
> wrote:
>
>> Hi
>>
>> 2016-01-03 20:46 GMT+01:00 Steve Petrie, P.Eng. <apetrie@aspetrie.net>:
>>
>>> *Greetings To Postgres Forum,*
>>>
>>> This posting is further to a prior forum thread -- subject "[*GENERAL]
>>> using a postgres table as a multi-writer multi-updater queue*", that was
>>> started on 23 November 2015 by Chris Withers chris@simplistix.co.uk. I
>>> believe the last posting to that thread was on 1 December 2015 by George
>>> Neuner <gneuner2@comcast.net>.
>>>
>>> A related thread of interest, was started earlier -- subject *"[GENERAL]
>>> using postgresql for session*",  on 7 October 2015 by John Tiger <
>>> john.tigernassau@gmail.com>.
>>>
>>
>> I am sorry for off topic. But are you sure, so using Postgres for session
>> data is a good idea? Using Postgres for short living data is can enforce
>> a
>> performance problems when a load will be higher.
>>
>> Regards
>>
>> Pavel
>>
>>
>>>
>>> * * *
>>> * * *
>>>
>>> I made some postings to the first above-mentioned thread, as I am
>>> working
>>> to migrate a php website application from mysql to postgres. An
>>> important
>>> objective of this migration is to find a good way to use a postgres
>>> table
>>> to store session context data rows, one row for each active website
>>> visitor.
>>>
>>> One item of advice (among much other helpful advice) I took away from
>>> the
>>> first thread mentioned above, was to avoid use of DELETE commands as a
>>> means to recycle session context table row image storage, when a session
>>> is terminated.
>>>
>>> To use instead, a TRUNCATE command on an entire session context table,
>>> to
>>> quickly and efficiently recycle session context row image storage space,
>>> back to the filesystem, so the space is immediately available for reuse.
>>>
>>> * * *
>>> * * *
>>>
>>> Since then, I have been working to design a way to use postgres table(s)
>>> as a session context store, for a simple, reliable and high-performance
>>> "session operations system" (SOS).
>>>
>>> A design for a postgres-based SOS, that follows two key principles to
>>> ensure maximum session workload throughput capacity:
>>>
>>> *PRINCIPLE #1*: *1.1* Use only the TRUNCATE TABLE command, to recycle
>>> frequently, rapidly and efficiently back to the filesystem, session
>>> context
>>> table storage space occupied by obsolete images of session context rows;
>>> and *1.2* do not use DELETE / AUTOVACUUM / VACUUM commands at all, for
>>> this recycling.
>>>
>>> *PRINCIPLE #2*: *2.1* Use sequence generators for various
>>> globally-addressable fast-access "iterators"**, that provide the php
>>> website app (and its PL/pgSQL functions), with e.g. access to an
>>> appropriate individual session context table; *2.2* Access granted to a
>>> table from a pool of session context tables, each pool having its tables
>>> all in the same operational state.
>>>
>>> The downside of Principle #1 is the considerable added complexity of
>>> having to manage multiple tables, to store session context data rows.
>>>
>>> The downside of Principle #2 is that the sequence generator has no role
>>> in sql transaction / savepoint semantics. So explicit provision for
>>> synchronization is required, adding further complexity.
>>>
>>>  (** An "iterator" is derived from a sequence generator, by using excess
>>> unneeded precision in high-order bits of the sequence integer value, to
>>> encode "iterator" metadata -- as an efficient way to make this metadata
>>> available to multiple concurrently executing app execution control flow
>>> paths.)
>>>
>>> * * *
>>> * * *
>>>
>>> *The purpose of this present email, is to present (in pseudocode) for
>>> critque by forum members, a proposed approach to synchronizing use of
>>> the
>>> "iterators" (sequence generators) described above, among multiple
>>> concurrent actors, in the website php app session operations scenario.*
>>>
>>> Since I am a postgres novice, I am hoping that members of this postgres
>>> forum, will be kind enough to examine and critique the (boiled-down,
>>> simplified) pseudocode for the proposed approach to synchronization.
>>>
>>> (In this discussion, the term "process" does not refer specifically to a
>>> "process" as implemented in operating systems, as one form of program
>>> execution control, that is contrasted with "thread" as another form of
>>> program execution control. In this discussion, the term "process" means
>>> the
>>> general sense of any program execution path that can occur in parallel
>>> concurrently with other program execution paths.)
>>>
>>> In the pseudocode example provided below, two concurrent processes
>>> (session process, supervisory process) operate on the same same table
>>> *sql_table_01*, and they use sequence generator *sql_sequence_01* as a
>>> "version" number for the operational state of table *sql_table_01*.
>>>
>>> *QUESTION: In supervisory process step sup.2 (below), will the command:*
>>>
>>> *   LOCK TABLE sql_table_01 IN ACCESS EXCLUSIVE MODE;*
>>>
>>> *ensure that the session process, having read a value from sequence
>>> generator sql_sequence_01 in step ses.1, will never ever begin to
>>> execute
>>> step ses.6:*
>>>
>>> *   SELECT currval('sql_sequence_01');*
>>>
>>> *so long as the supervisory process, has completed step sup.2:*
>>>
>>> *   LOCK TABLE sql_table_01 IN ACCESS EXCLUSIVE MODE;*
>>>
>>> *but has not yet completed step sup.4:*
>>>
>>> *   COMMIT TRANSACTION;*
>>>
>>> *???*
>>>
>>>
>>> Essentially, the idea is to piggyback, synchronization of the use
>>> of sequence generator *sql_sequence_01*, on the suprvisory process' LOCK
>>> TABLE *sql_table_01* command, assuming that the session process has some
>>> INSERT / SELECT / UPDATE command to perform on the same table (a command
>>> that will be blocked by the LOCK TABLE command).
>>>
>>> * * *
>>> * * *
>>>
>>> Here is pseudocode for the *session process* (use a wide viewing window
>>> to avoid line wrap):
>>>
>>>
>>> *Session Process*                       ---------------
>>>        INSERT / SELECT / UPDATE row in table
>>> *sql_table_01*
>>> -------------------------------------------------------------
>>>       |
>>> *ses.0* |(Decide to update a row in table *sql_table_01*).
>>>       |
>>> *ses.1* |  *SELECT currval('sql_sequence_01');*
>>> *ses.2* |  $save_seq1 = (value of sequence obtained in *ses.1*);
>>>       |
>>> *ses.3* |
>>> *SAVEPOINT session_savepoint;*      |
>>> *ses.4* |
>>> *SELECT ... FROM  sql_table_01 FOR UPDATE;*      |
>>> *ses.5* |
>>> *UPDATE sql_table_01 ...;*      |
>>> *ses.6* |
>>> *SELECT currval('sql_sequence_01');**ses.7* |  $save_seq2 = (value of
>>> seq obtained in ses.6);
>>>       |
>>>       |  /*
>>>       |     IS IT SAFE TO COMMIT THE UNIT OF WORK ?
>>>       |      (i.e. is operational state of table
>>>       |        *sql_table_01* unchanged?)
>>>       |  */
>>> *ses.8* |  if ($save_seq1 == $save_seq2)
>>>       |  /*
>>>       |     YES -- SAFE TO COMMIT
>>>       |      ( sequence *sql_sequence_01* is unchanged).
>>>       |  */
>>>       |  {
>>> *ses.9* |
>>> *RELEASE SAVEPOINT session_savepoint;*      |  }
>>>       |  else
>>>       |  /*
>>>       |     NO -- NOT SAFE TO COMMIT
>>>       |       (sequence *sql_sequence_01* has changed
>>>       |         abandon unit of work and retry).
>>>       |  */
>>>       |  {
>>> *ses.10*|
>>> *ROLLBACK TO SAVEPOINT session_savepoint;*      |  }
>>>       |
>>>       | /* DONE */
>>>       |
>>>       -------------------------------------------------------------
>>>
>>> * * *
>>> * * *
>>>
>>> Here is pseudocode for the *supervisoty process* (use a wide viewing
>>> window to avoid line wrap):
>>>
>>>
>>> *Supervisory Process*                  -------------------
>>>    Change operational state of table sql_table_01
>>> -------------------------------------------------------------
>>>        |
>>>  *sup.0* | (Decide to change operational state of table
>>>        |    *sql_table_01*).
>>>        |
>>>  *sup.1* | *BEGIN TRANSACTION;*
>>>        |
>>>        | /*
>>>        |    Block all other access to table sql_table_01.
>>>        | */
>>>  *sup.2* |
>>> *LOCK TABLE sql_table_01 IN ACCESS EXCLUSIVE MODE;*       | ...
>>>        | ... (change operational state of table sql_table_01
>>>        | ...
>>>        | ...  e.g. *TRUNCATE ONLY TABLE sql_table_01;*)
>>>        | ...
>>>        |
>>>        | /*
>>>        |   Advance sequence
>>>        |
>>> *sql_sequence_01*       |    to indicate that the operational state of
>>> table
>>>        |
>>> *sql_table_01*       |     has changed.
>>>        | */
>>>        |
>>>  *sup.3* |
>>> *SELECT nextval('sql_sequence_01');*       |
>>>        | /*
>>>        |    Release the EXCLUSIVE MODE lock on table
>>>        |       sql_table_01.
>>>        | */
>>>  *sup.4* |
>>> *COMMIT TRANSACTION;*       |
>>>        | /* DONE */
>>>        |
>>> -------------------------------------------------------------
>>>
>>> * * *
>>> * * *
>>>
>>> I attach a PDF with the pseudocode given above.
>>>
>>>    - Attachment <eto_sql_pg - Session Context Storage - 8.1 Synchronize
>>>    Process Access To Table - 20160103.odt>
>>>
>>> The design document for the session operations system (SOS) is well
>>> advanced, but  not yet ready for general distribution. If a forum member
>>> would like to see a copy of the design document in its present draft
>>> state,
>>> please feel free to email me offline to request a PDF copy.
>>>
>>> Thanks and Regards,
>>>
>>> *Steve*
>>>
>>>
>>>
>>> --
>>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-general
>>>
>>>
>>
>
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.
>

Thanks to Adrian for your remarks. My replies are posted below.

Steve

----- Original Message -----
From: "Adrian Klaver" <adrian.klaver@aklaver.com>
To: "Melvin Davidson" <melvin6925@gmail.com>; "Pavel Stehule"
<pavel.stehule@gmail.com>
Cc: "Steve Petrie, P.Eng." <apetrie@aspetrie.net>;
<pgsql-general@postgresql.org>
Sent: Sunday, January 03, 2016 4:38 PM
Subject: Re: [GENERAL] Question -- Session Operations - Feasibility Of
Proposed Synchronization Method?


> On 01/03/2016 01:32 PM, Melvin Davidson wrote:
>> As others have pointed out, storing session data in a table is not a
>> good idea. Even if you use TRUNCATE, you will still not reclaim all the
>> space used unless you use vacuum full. More to the point, if you
>
> Actually:
>
> http://www.postgresql.org/docs/9.4/interactive/sql-truncate.html
>
> "TRUNCATE quickly removes all rows from a set of tables. It has the same
> effect as an unqualified DELETE on each table, but since it does not
> actually scan the tables it is faster. Furthermore, it reclaims disk space
> immediately, rather than requiring a subsequent VACUUM operation. This is
> most useful on large tables."
>

Good to have this confirmed. TRUNCATE is way faster than DELETE /.
AUTOVACUUM / VACUUM.

So if the session operations system uses a pool (of e.g. 31) session context
tables, it will be feasible periodically to recycle session context row
image storage space back to the filesystem, by means of the hyper-fast table
TRUNCATE command, without ever disrupting the app's access to the session
context data store. Because many other session context tables in the pool of
(e.g. 31) tables, remain online and available to the app, during the
TRUNCATE operation.

For example, session operations could be configured to impose a 30000 limit,
on the count of row images stored in a session context table.

If each session context row image occupies e.g. 1000 bytes, then the session
operations system will be recycling 30 MB of storage back to the filesystem,
in one quick table TRUNCATE command. Instead of going through a painstaking
per-row AUTOVACUUM / VACUUM process, that is more suitable for long-lived,
high-value data assets.

The app only ever uses INSERT / SELECT / UPDATE commands on session context
data rows. The app never uses DELETE commands on these rows. Session
operations has other means to ensure, before doing the table TRUNCATE, that
the session context context table contains only "dead" session context row
images.

> the rub is:
>
> "TRUNCATE acquires an ACCESS EXCLUSIVE lock on each table it operates on,
> which blocks all other concurrent operations on the table. When RESTART
> IDENTITY is specified, any sequences that are to be restarted are likewise
> locked exclusively. If concurrent access to a table is required, then the
> DELETE command should be used instead."
>
>

My proposal is to turn this disadvantage of the TRUNCATE command (that it
requires an ACCESS EXCLUSIVE lock on the table) into an advantage, by means
of two design ideas:

1. Using a pool (of e.g. 31) session context tables, so that it will be
feasible periodically to recycle large amounts of session context row image
storage space back to the filesystem, by means of the hyper-fast table
TRUNCATE command, without ever disrupting the app's access to the session
context data store. Because many other session context tables in the pool
(of e.g. 31) tables, will always remain online and available to the app,
during the TRUNCATE operation on the one "dead" table, that is briefly
taken offline by session operations to be TRUNCATEd.

2. Using an explicit LOCK TABLE ... IN ACCESS EXCLUSIVE MODE
 command, as a means of synchronizing
usage of sequence generators, with use of TRANSACTION /
SAVEPOINT semantics. The idea is to use a number of sequence generators as
quick-access "iterators"** that are globally-addressable by the app, to
assist efficient orderly usage of individual session context tables by the
app.

(**An "iterator" in this design, piggy-backs iterator fixed-value meta-data,
in the high-order bits of the underlying 64-bit integer sequence generator.
High-order bits that are surplus to the amount of integer precision
required, for the incrementing numeric range needed by the iterator.)

And the objective of my original posting, was to ask this forum to critique
pseudocode that I propose, for achieving the synchronization envisioned in
point 2, above, to coordinate use of sequence generators with SQL
transaction / savepoint semantics. The author being a postgres novice, and a
barely-scratching-the-surface SQL app developer (but an experienced software
engineer).

>> absolutely must store session data, then why not just do it in a
>> TEMPORARY table, which will reside in memory and clean itself up when
>> the session ends?
>>
>> http://www.postgresql.org/docs/9.4/static/sql-createtable.html
>>
>>
>> On Sun, Jan 3, 2016 at 3:43 PM, Pavel Stehule <pavel.stehule@gmail.com
>> <mailto:pavel.stehule@gmail.com>> wrote:
>>
>>     Hi
>>
>>     2016-01-03 20:46 GMT+01:00 Steve Petrie, P.Eng.
>>     <apetrie@aspetrie.net <mailto:apetrie@aspetrie.net>>:
>>
>>         __
>>         *Greetings To Postgres Forum,*
>>         This posting is further to a prior forum thread -- subject
>>         "[/GENERAL] using a postgres table as a multi-writer
>>         multi-updater queue/", that was started on 23 November 2015 by
>>         Chris Withers chris@simplistix.co.uk
>>         <mailto:chris@simplistix.co.uk>. I believe the last posting to
>>         that thread was on 1 December 2015 by George Neuner
>>         <gneuner2@comcast.net <mailto:gneuner2@comcast.net>>.
>>         A related thread of interest, was started earlier -- subject
>>         /"[GENERAL] using postgresql for session/",  on 7 October 2015
>>         by John Tiger <john.tigernassau@gmail.com
>>         <mailto:john.tigernassau@gmail.com>>.
>>
>>
>>     I am sorry for off topic. But are you sure, so using Postgres for
>>     session data is a good idea? Using Postgres for short living data is
>>     can enforce a performance problems when a load will be higher.
>>
>>     Regards
>>
>>     Pavel
>>
>>         * * *
>>         * * *
>>         I made some postings to the first above-mentioned thread, as I
>>         am working to migrate a php website application from mysql to
>>         postgres. An important objective of this migration is to find a
>>         good way to use a postgres table to store session context data
>>         rows, one row for each active website visitor.
>>         One item of advice (among much other helpful advice) I took away
>>         from the first thread mentioned above, was to avoid use of
>>         DELETE commands as a means to recycle session context table row
>>         image storage, when a session is terminated.
>>         To use instead, a TRUNCATE command on an entire session context
>>         table, to quickly and efficiently recycle session context row
>>         image storage space, back to the filesystem, so the space is
>>         immediately available for reuse.
>>         * * *
>>         * * *
>>         Since then, I have been working to design a way to use postgres
>>         table(s) as a session context store, for a simple, reliable and
>>         high-performance "session operations system" (SOS).
>>         A design for a postgres-based SOS, that follows two key
>>         principles to ensure maximum session workload throughput
>> capacity:
>>         *PRINCIPLE #1*: *1.1* Use only the TRUNCATE TABLE command, to
>>         recycle frequently, rapidly and efficiently back to the
>>         filesystem, session context table storage space occupied by
>>         obsolete images of session context rows; and *1.2* do not use
>>         DELETE / AUTOVACUUM / VACUUM commands at all, for this recycling.
>>         *PRINCIPLE #2*: *2.1* Use sequence generators for various
>>         globally-addressable fast-access "iterators"**, that provide the
>>         php website app (and its PL/pgSQL functions), with e.g. access
>>         to an appropriate individual session context table; *2.2* Access
>>         granted to a table from a pool of session context tables, each
>>         pool having its tables all in the same operational state.
>>         The downside of Principle #1 is the considerable added
>>         complexity of having to manage multiple tables, to store session
>>         context data rows.
>>         The downside of Principle #2 is that the sequence generator has
>>         no role in sql transaction / savepoint semantics. So explicit
>>         provision for synchronization is required, adding further
>>         complexity.
>>           (** An "iterator" is derived from a sequence generator,
>>         by using excess unneeded precision in high-order bits of the
>>         sequence integer value, to encode "iterator" metadata -- as an
>>         efficient way to make this metadata available to multiple
>>         concurrently executing app execution control flow paths.)
>>         * * *
>>         * * *
>>         *The purpose of this present email, is to present (in
>>         pseudocode) for critque by forum members, a proposed approach to
>>         synchronizing use of the "iterators" (sequence generators)
>>         described above, among multiple concurrent actors, in the
>>         website php app session operations scenario.*
>>         Since I am a postgres novice, I am hoping that members of this
>>         postgres forum, will be kind enough to examine and critique the
>>         (boiled-down, simplified) pseudocode for the proposed approach
>>         to synchronization.
>>         (In this discussion, the term "process" does not refer
>>         specifically to a "process" as implemented in operating systems,
>>         as one form of program execution control, that is contrasted
>>         with "thread" as another form of program execution control. In
>>         this discussion, the term "process" means the general sense of
>>         any program execution path that can occur in parallel
>>         concurrently with other program execution paths.)
>>         In the pseudocode example provided below, two concurrent
>>         processes (session process, supervisory process) operate on the
>>         same same table *sql_table_01*, and they use sequence generator
>>         *sql_sequence_01* as a "version" number for the operational
>>         state of table *sql_table_01*.
>>
>>             *QUESTION: In _supervisory process_ step sup.2 (below), will
>>             the command:*
>>             **
>>             */LOCK TABLE sql_table_01 IN ACCESS EXCLUSIVE MODE;/*
>>             **
>>             *ensure that the _session process_, having read a value from
>>             sequence generator sql_sequence_01 in step ses.1, will
>>             _never ever begin to execute step ses.6_:*
>>             **
>>             */SELECT currval('sql_sequence_01');/*
>>             **
>>             *so long as the supervisory process, has _completed step
>>             sup.2:_*
>>             **
>>             */LOCK TABLE sql_table_01 IN ACCESS EXCLUSIVE MODE;/*
>>             *__*
>>             *_but has not yet completed step sup.4_:*
>>             **
>>             */COMMIT TRANSACTION;/*
>>             **
>>             *???*
>>
>>         Essentially, the idea is to piggyback, synchronization of the
>>         use of sequence generator *sql_sequence_01*, on the suprvisory
>>         process' LOCK TABLE */sql_table_01/* command, assuming that the
>>         session process has some INSERT / SELECT / UPDATE command to
>>         perform on the same table (a command that will be blocked by the
>>         LOCK TABLE command).
>>         * * *
>>         * * *
>>         Here is pseudocode for the _session process_ (use a wide viewing
>>         window to avoid line wrap):
>>         *Session Process
>>         * ---------------
>>                 INSERT / SELECT / UPDATE row in table *sql_table_01
>>         *-------------------------------------------------------------
>>         |
>>         *ses.0* |(Decide to update a row in table *sql_table_01*).
>>         |
>>         *ses.1* | /SELECT currval(*'sql_sequence_01*');/
>>         /ses.2/ | $save_seq1 = (value of sequence obtained in *ses.1*);
>>         |
>>         *ses.3* | /SAVEPOINT *session_savepoint*;
>>         / |
>>         *ses.4* | /SELECT ... FROM *sql_table_01* FOR UPDATE;
>>         /      |
>>         *ses.5* | /UPDATE *sql_table_01* ...;
>>         /      |
>>         *ses.6* | /SELECT currval(*'sql_sequence_01*');
>>         /*ses.7* |  $save_seq2 = (value of seq obtained in ses.6);
>>                |
>>         |  /*
>>                |     IS IT SAFE TO COMMIT THE UNIT OF WORK ?
>>         |      (i.e. is operational state of table
>>         | *sql_table_01* unchanged?)
>>                | */
>>         *ses.8* |  if ($save_seq1 == $save_seq2)
>>                | /*
>>                |     YES -- SAFE TO COMMIT
>>                |      ( sequence *sql_sequence_01* is unchanged).
>>                | */
>>                |  {
>>         *ses.9* | /RELEASE SAVEPOINT *session_savepoint*;
>>         / |  }
>>                | else
>>                | /*
>>                |     NO -- NOT SAFE TO COMMIT
>>                | (sequence *sql_sequence_01* has changed
>>         |         abandon unit of work and retry).
>>                | */
>>                | {
>>         *ses.10*| /ROLLBACK TO SAVEPOINT *session_savepoint*;
>>         / |  }
>>                |
>>         | /* DONE */
>>                |
>>         -------------------------------------------------------------
>>         * * *
>>         * * *
>>         Here is pseudocode for the _supervisoty process_ (use a wide
>>         viewing window to avoid line wrap):
>>
>>         *Supervisory Process
>>         * -------------------
>>             Change operational state of table sql_table_01
>>         -------------------------------------------------------------
>>         |
>>         *sup.0* | (Decide to change operational state of table
>>                 | *sql_table_01*).
>>         |
>>         *sup.1* | /BEGIN TRANSACTION;/
>>         |
>>                 | /*
>>                 |    Block all other access to table sql_table_01.
>>                 | */
>>         *sup.2* | /LOCK TABLE sql_table_01 IN ACCESS EXCLUSIVE MODE;
>>         /       | ...
>>                 | ... (change operational state of table sql_table_01
>>                 | ...
>>                 | ...  e.g. /TRUNCATE ONLY TABLE sql_table_01;/)
>>                 | ...
>>         |
>>                 | /*
>>                 |   Advance sequence
>>                 | *sql_sequence_01
>>         * |    to indicate that the operational state of table
>>                 | *sql_table_01
>>         * |     has changed.
>>                 | */
>>                 |
>>         *sup.3* | /SELECT nextval('*sql_sequence_01'*);
>>         / |
>>                 | /*
>>                 |    Release the EXCLUSIVE MODE lock on table
>>         | sql_table_01.
>>                 | */
>>         *sup.4* | /COMMIT TRANSACTION;
>>         / |
>>                 | /* DONE */
>>         |
>>         -------------------------------------------------------------
>>         * * *
>>         * * *
>>         I attach a PDF with the pseudocode given above.
>>
>>           * Attachment <eto_sql_pg - Session Context Storage - 8.1
>>             Synchronize Process Access To Table - 20160103.odt>
>>
>>         The design document for the session operations system (SOS) is
>>         well advanced, but  not yet ready for general distribution. If
>>         a forum member would like to see a copy of the design document
>>         in its present draft state, please feel free to email me offline
>>         to request a PDF copy.
>>         Thanks and Regards,
>>         *Steve*
>>
>>
>>         --
>>         Sent via pgsql-general mailing list
>>         (pgsql-general@postgresql.org
>> <mailto:pgsql-general@postgresql.org>)
>>         To make changes to your subscription:
>>         http://www.postgresql.org/mailpref/pgsql-general
>>
>>
>>
>>
>>
>> --
>> *Melvin Davidson*
>> I reserve the right to fantasize.  Whether or not you
>> wish to share my fantasy is entirely up to you.
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com

Thanks to John for your remarks. My reply is posted below.

Steve

----- Original Message -----
From: "John R Pierce" <pierce@hogranch.com>
To: <pgsql-general@postgresql.org>
Sent: Sunday, January 03, 2016 4:40 PM
Subject: Re: [GENERAL] Question -- Session Operations - Feasibility Of
Proposed Synchronization Method?


> On 1/3/2016 1:32 PM, Melvin Davidson wrote:
>> As others have pointed out, storing session data in a table is not a good
>> idea. Even if you use TRUNCATE, you will still not reclaim all the space
>> used unless you use vacuum full. More to the point, if you absolutely
>> must store session data, then why not just do it in a TEMPORARY table,
>> which will reside in memory and clean itself up when the session ends?
>
> The fundamental problem is that HTTP itself is stateless, and user
> sessions have to be enforced by the web app, with the aid of browser
> cookies.   you can't simply directly link a http connection to a SQL
> connection and assume thats a session.
>

Please see my reply to Melvin Davidson.

The "session" I mean is the one that you point out, that has to be enforced
by the web app, to provide continuity missing from the stateless HTTP
protocol.

Except that I do not use browser cookies.

Instead of browser cookies, I use a parameter (session ID key) embedded in
the HTML page served by the app to the consumer's web browser.

And this parameter goes on a round-trip journey, back to the app
program that is launched by the HTTP server, when the visitor actions the
web page submit button and their browser sends an HTTP request message.

The format of the (32-character string) session ID key parameter is:

   CCCCCCCCCC:YYYYMMDDHHMMSS:SSSSTT

where:

1. CCCCCCCCCC --  "quasi-unique" random session identification code
(10-char, hex-encoded), a hash value (e.g. substring of MD5) but NOT a hash
of a password to be remembered and reused, just a randomized value from
x0000000000 to xFFFFFFFFFF (decimal: 0 ... 1099511627775);

2. YYYYMMDDHHMMSS -- session initiation time (14-char, decimal-encoded) e.g.
20160105040427;

3. SSSS -- serial number of corresponding session context row when first
INSERTed (4-char, hex-encoded) value from x0001 to xFFFF (decimal: 1 ...
65535);

4. TT -- serial number of current session context table where session row is
stored (2-char, hex-encoded) value from x01 to x1F (decimal: 1 ... 31);

Here is a paste of example HTML:

<form method="post" action="./eto_session_act_2a.php"
enctype="multipart/form-data" accept-charset="ISO-8859-1">

   <input type="hidden" name="a" value="B37000AC2C:20160105040427:0AD11A" />
   <input type="hidden" name="d" value="BF46C6" />

   <div id="centeredmenu">
    <ul>
       <li> <input type="submit" name="new_message_form_submit" value="New
Message Form" /> </li>
    </ul>
 </div>
...
</form>

In the above HTML, hidden field name="a"
value="B37000AC2C:20160105040427:0AD11A" is a session ID key input parameter
to PHP program eto_session_act_2a.php, that interprets parameter "a" as:

1. CCCCCCCCCC == "B37000AC2C";
2. YYYYMMDDHHMMSS == "20160105040427";
3. SSSS == "0AD1" (decimal 2769);
4. TT == "1A" (decimal 26);

> --
> john r pierce, recycling bits in santa cruz
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

Thanks to forum members for the four helpful replies, to my earlier
message that initiated this thread.

The replies expressed concerns, with the feasibility of my proposal to
use postgres tables to store short-lived context data, for dialog
continuity during website app transient sessions, with visitor browsers
over modeless HTTP connections.

Hope the four emails I sent in response (5 January 2016), went some way
to satisfying the concerns expressed.

Here is a list of the issues discussed, in the dialog mentioned above:

1. "Session" defined;
2. Avoid row DELETEs;
3. Periodically TRUNCATE each table in a pool of session context tables;
4. Embed a session ID key parameter in an HTML "hidden" field
(optional);
5. Use sequence generators as rapid global iterators controlling access
to session context tables;

In the remainder of this email are:

A. Descriptions of the 5 items in the list above.

B. Proposed method to synchronize use of sequence generators, with SQL
transaction / savepoint semantics.

I would appreciate comments from forum members, who examine the proposed
synchronization method.

* * *
* * *

A. Descripttions of the 5 items in the list above:

1. "Session" defined:

The "session" discussed here is an app session. It is the (browser <==>
app) dialog between a website visitor, and the (HTTP / HTML / PHP /
PostgreSQL) app that services requests from the visitor.

The term "session" as used here, is not limited to the connection made
by an app, through a postgres client, to a postgres database server.
During one app session, the app can open and close numerous brief
connections (also called "sessions" in postgres docs) to a postgres
database server, through a postgres client.

* * *

2. Avoid row DELETEs:

The objective is to find a design for a session operations system, based
on postgres as the session context data store.

A design that avoids performance problems under high load, of using a
classic <DELETE row ...  / AUTOVACUUM table / VACUUM table> method, to
recycle session context table row image storage space back to the
filesystem.

* * *

3. Periodically TRUNCATE each table in a pool of (e.g. 31) session
context tables.

The proposed design uses a pool of identically-defined session context
tables, for example:

   sos_sqltbl_session_ctx_01
   ...
   sos_sqltbl_session_ctx_31

Having a pool of session context tables, permits the session operations
system to use a hyper-fast table TRUNCATE command, to recycle
periodically and efficiently large amounts of "dead" session context row
image storage space, back to the filesystem.

Many other session context tables in the pool (of e.g. 31) tables,
always remain online and available to the app, during the TRUNCATE
operation on the one "dead" table, that is briefly taken offline,
TRUNCATEd and then returned to the pool for online reuse.

For example, session operations could be configured to impose a 30000
limit, on the count of row images stored in a session context table. If
each session context row image occupies e.g. 1000 bytes, then the
session operations system will be recycling periodically, 30 MB of
storage space back to the filesystem, with one quick table TRUNCATE
command. Instead of going through a painstaking per-row DELETE ... /
AUTOVACUUM / VACUUM process, that is more  suitable for long-lived,
high-value data assets.

The session app only ever uses INSERT / SELECT / UPDATE commands on
session context data rows. The app never uses DELETE commands on these
rows. Session operations has other means, to ensure that it can safely
TRUNCATE a session context table.

* * *

4. Use a session ID key parameter in an HTML "hidden" field.

Instead of browser cookies, use a session ID key parameter embedded in
an HTML "hidden" field, in the HTML page served by the app to the web
browser. Of course the session ID key is not actually "hidden" in the
security sense.

This session ID key parameter completes a round-trip journey, when the
visitor actions the web page (e.g. submit button) and their browser
sends an HTTP request message, bringing the session ID key value back to
the app program launched by the HTTP server.

(This point is optional. Cookies can be used as an alternative to a
session ID key parameter, with this proposed design for session
operations.)

* * *

5. Use sequence generators as rapid global iterators controlling access
to session context tables:

As source of sequential integer values, a postgres sequence generator
(based on 64-bit bigint precision) has far more bits of precision than
are necessary, to cycle from 1 up to value limits that are sufficiently
large for session operations. These value limits typically being in the
order of anywhere from a few thousand, up to a few million.

So it's feasible to encode a lot of fixed meta-information, in "surplus"
high-order bits of a postgres sequence, transforming that sequence into
a kind of "iterator".

Iterator program code in the session operations system, decodes all 64
bits of the sequence's integer value, into two groups: 1. high-order
bits => (one or more) fixed-value meta-data fields and 2. low-order bits
=> the incrementing "serializer" integer field.

The serializer field in the low-order bits of the sequence value, is the
actual operative incrementing integer sequence value, for purposes of
session operations.

Four example session operations sub-domains, over which iterators can
range are:

-- limit the count of sessions in a session context table;

-- limit the count of session context row images, in a session context
table;

-- cycle in continual circular round robin fashion, around a list of
"hot" session context tables, granting access in turn to the next table
in the circular list, to the next app process that requests permission
to INSERT into a "hot" table, the row of a new session being initiated;

-- keep "quasi-infinite" count of iterator (sequence generator) reset
events, for such a very long time as to be safely considered an event
count that "never" repeats (wraps), assisting safe synchronization of
the use of iterators, with SQL transaction / savepoint semantics;

(I can provide more detail on this iterator idea, and on the whole
session operations system proposed design. Just send me an email
offline.)

* * *
* * *

B. Proposed method to synchronize use of sequence generators, with SQL
transaction / savepoint semantics.

In the pseudocode example provided below, two concurrent processes
(session process, supervisory process) operate on the same same table
sql_table_01, and they use sequence generator sql_sequence_01 as a
"version" number for the operational state of table sql_table_01.

QUESTION: In supervisory process step sup.2 (below), will the command:

   LOCK TABLE sql_table_01 IN ACCESS EXCLUSIVE MODE;

ensure that the session process, having read a value from sequence
generator sql_sequence_01 in step ses.1, will never ever begin to
execute step ses.6:

   SELECT currval('sql_sequence_01');

so long as the supervisory process, has completed step sup.2:

   LOCK TABLE sql_table_01 IN ACCESS EXCLUSIVE MODE;

but has not yet completed step sup.4:

   COMMIT TRANSACTION;

???

Essentially, the idea is to piggyback synchronization of use of sequence
generator sql_sequence_01 by the session process, on the LOCK TABLE
sql_table_01 command, issued by the supervisory process. Assuming that
the session  process has some INSERT / SELECT / UPDATE command to
perform on the same table, a command that will be blocked by the LOCK
TABLE command.

I attach a PDF version of the pseudo code, in case the pastings below
get mangled on the way to recipients:

<eto_sql_pg - Session Context Storage - 8.1 Synchronize Process Access
To Table - 20160103.pdf>.

* * *

                   Session Process
                       ---------------
INSERT / SELECT / UPDATE row in table sql_table_01
-------------------------------------------------------------
         |
ses.0 |(Decide to update a row in table sql_table_01).
         |
ses.1 |  SELECT currval('sql_sequence_01');
ses.2 |  $save_seq1 = (value of sequence obtained in ses.1);
         |
ses.3 |  SAVEPOINT session_savepoint;
         |
ses.4 |  SELECT ... FROM  sql_table_01 FOR UPDATE;
         |
ses.5 |  UPDATE sql_table_01 ...;
         |
ses.6 |  SELECT currval('sql_sequence_01');
ses.7 |  $save_seq2 = (value of seq obtained in ses.6);
         |
         |  /*
         |     IS IT SAFE TO COMMIT THE UNIT OF WORK ?
         |      (i.e. is operational state of table
         |        sql_table_01 unchanged?)
         |  */
ses.8 |  if ($save_seq1 == $save_seq2)
         |  /*
         |     YES -- SAFE TO COMMIT
         |      ( sequence sql_sequence_01 is unchanged).
         |  */
         |  {
ses.9 |     RELEASE SAVEPOINT session_savepoint;
         |  }
         |  else
         |  /*
         |     NO -- NOT SAFE TO COMMIT
         |       (sequence sql_sequence_01 has changed,
         |         abandon unit of work and retry).
         |  */
         |  {
ses.10|     ROLLBACK TO SAVEPOINT session_savepoint;
         |  }
         |
         | /* DONE */
         |      -------------------------------------------------------------

* * *

             Supervisory Process
                  -------------------
   Change operational state of table sql_table_01
-------------------------------------------------------------
          |
 sup.0 | (Decide to change operational state of table
          |    sql_table_01).
          |
 sup.1 | BEGIN TRANSACTION;
          |
          | /*
          |    Block all other access to table sql_table_01.
          | */
 sup.2 | LOCK TABLE sql_table_01 IN ACCESS EXCLUSIVE MODE;
          | ...
          | ... (change operational state of table sql_table_01
          | ...
          | ...  e.g. TRUNCATE ONLY TABLE sql_table_01;)
          | ...
          |
          | /*
          |   Advance sequence
          |      sql_sequence_01
          |    to indicate that the operational state of table
          |      sql_table_01
          |     has changed.
          | */
          |
 sup.3 | SELECT nextval('sql_sequence_01');
          |
          | /*
          |    Release the EXCLUSIVE MODE lock on table
          |       sql_table_01.
          | */
 sup.4 | COMMIT TRANSACTION;
          |
          | /* DONE */
          |
-------------------------------------------------------------

* * *
* * *

Thanks to forum members for taking the time to read my email.

Steve

On 01/07/2016 06:30 PM, Steve Petrie, P.Eng. wrote:
> Thanks to forum members for the four helpful replies, to my earlier message that initiated this thread.
>
> The replies expressed concerns, with the feasibility of my proposal to use postgres tables to store short-lived
contextdata, for dialog continuity during website app transient sessions, with visitor browsers over modeless HTTP
connections.
>
> Hope the four emails I sent in response (5 January 2016), went some way to satisfying the concerns expressed.
>
> Here is a list of the issues discussed, in the dialog mentioned above:
>
> 1. "Session" defined;
> 2. Avoid row DELETEs;
> 3. Periodically TRUNCATE each table in a pool of session context tables;
> 4. Embed a session ID key parameter in an HTML "hidden" field (optional);
> 5. Use sequence generators as rapid global iterators controlling access to session context tables;
>
<SNIP>
>
> Thanks to forum members for taking the time to read my email.
>

This feels hugely overcomplicated.  I also didn't read most of the last thread, so forgive me if you've answered this
already: How many website requests a second (that actually need to touch session data) are you expecting?  How much
spaceis the session data going to take?  (like, 5 Gig a day?) 

If its a huge number, you should put effort into growing horizontally, not all of this stuff.
If its a small number, you'll spend more time fixing all the broken things than its worth.
Have you benchmarked this?  In my mind, complicated == slow.

Sorry if I'm raining on your parade, it looks like you have really put a lot of work into this.

Have you considered saving session data to disk is faster than saving to db?  A good reverse web proxy can stick a
sessionto the same backend.  1 web proxy up front, 5 web servers behind it.  I'd bet its way faster. 

-Andy

Andy,

Thanks very much for your response.

No worries about raining on my parade. Your feedback is exactly what I'm
looking for -- praise is nice, but I really do prefer to have the
experts throwing rocks at my naive ideas :)

Please see my comments embedded below.

Steve

----- Original Message -----
From: "Andy Colson" <andy@squeakycode.net>
To: "Steve Petrie, P.Eng." <apetrie@aspetrie.net>;
<pgsql-general@postgresql.org>
Sent: Thursday, January 07, 2016 10:17 PM
Subject: Re: [GENERAL] Question -- Session Operations - Feasibility Of
Proposed Synchronization Method?


> On 01/07/2016 06:30 PM, Steve Petrie, P.Eng. wrote:
>> Thanks to forum members for the four helpful replies, to my earlier
>> message that initiated this thread.
>>
>> The replies expressed concerns, with the feasibility of my proposal
>> to use postgres tables to store short-lived context data, for dialog
>> continuity during website app transient sessions, with visitor
>> browsers over modeless HTTP connections.
>>
>> Hope the four emails I sent in response (5 January 2016), went some
>> way to satisfying the concerns expressed.
>>
>> Here is a list of the issues discussed, in the dialog mentioned
>> above:
>>
>> 1. "Session" defined;
>> 2. Avoid row DELETEs;
>> 3. Periodically TRUNCATE each table in a pool of session context
>> tables;
>> 4. Embed a session ID key parameter in an HTML "hidden" field
>> (optional);
>> 5. Use sequence generators as rapid global iterators controlling
>> access to session context tables;
>>
> <SNIP>
>>
>> Thanks to forum members for taking the time to read my email.
>>
>
> This feels hugely overcomplicated.

I agree. It is complicated.

But I believe it's the minimum functionality required to both: 1. avoid
using the <row DELETE ... / table AUTOVACUUM / table VACUUM> approach,
to recycling "dead" session context row image storage space back to the
filesystem, and 2. enable use of the much faster TRUNCATE command on an
entire "dead" session context table..

> I also didn't read most of the last thread, so forgive me if you've
> answered this already:  How many website requests a second (that
> actually need to touch session data) are you expecting?  How much
> space is the session data going to take?  (like, 5 Gig a day?)
>

Every incoming request to the website for non-static content, needs to
touch (INSERT or SELECT + UPDATE) the corresponding session context row.
That row is where the transient continuity context for the app session
dialog, gets stored, between request  <i>  and request   <i+1>  coming
in from the browser driving that app session.

So session data will be touched by every request that launches an app
php function, to service the next step in the session dialog with that
visitor.

But an individual session isn't going to live all that long, from the
time that it's context row gets INSERTed until the time that the session
"dies" and it's context row gets UPDATEd as "dead" in its "status"
column (the row is never explicitely DELETEd, the entire table in which
it resides gets TRUNCATEd).

If the website manages to register e.g. 100,000 subscribers in its first
year, it will be a runaway success. I'm not expecting more than a few
percent of subscribers to visit on any given day. So if the website
proves to be a massive winner, there will be maybe 1000 to 5000
subscriber sessions / day, each session being initiated, conducted and
then terminated over the time span of a few minutes (rarely more than 30
minutes).

But I do fear "success disaster" if suddenly, the website (which will
promote a politically controversial technology concept for preventing
freeway traffic congestion) gets media coverage in its initial market
area (the greater Toronto area in the province of Ontario, Canada), and
among a million+ people watching the 6-o'clock Toronto news, a few
thousand jump on their smart-phone browsers to hit the website, looking
to subscribe or send a contact message via web page form.

So I'm looking to build in capacity to handle brief intense bursts of
session traffic workload. Not anywhere near Google-scale, obviously. But
maybe to handle a 10-minute burst driving a maximum rate of  e.g. 1000
requests / second to the database server (being mostly a combination of
an INSERT for each new session row, followed by a few <SELECT + UPDATE>s
to that row, as the session proceeds through its brief "life", towards
its inevitable "death".

Actual access activity to longer-lived data tables: 1. subscriber
membership table, 2. contact message table; will be orders-of-magnitude
lower, than activity in the session context tables.

Each visitor session is allowed a "quota" of requests (e.g. 25) so the
visitor gets 25 chances to e.g. click a "submit" button. There is also a
session timeout "quota" (e.g. 10 minutes) that will kill the session if
the visitor waits too long between requests.

So the session context tables in aggregate, do not keep growing and
growing. And session context data is short-term expendable data. No need
to log it for recovery. No need for checkpoints, or any other backup
provisions.

If all active session context data gets lost in a crash, no big deal.
Maybe a reputational hit for the website, but the visitors who had their
sessions brutally murdered by the crash, will not wait around for a
recovery of their sessions anyway. They will wander off and (hopefully)
come back to retry later. Most likely after the website administrator
sends out a post-crash apologetic mass email message to the entire
subscriber base ("Hey subscribers, guess what? We're so successful we
crashed!!" :)

Consider a worst case severe workload. There might be say, an intense
burst of activity for 10 seconds, that initiates 10000 active sessions
(10% of all website subscribers) spread over e.g. 10 tables. And during
the entire lifetime of each session, table storage consumed by that
session of say 10, session context row images  (1 INSERT image + 9
UPDATE images). So in total there would be space for 100,000 row images
(10 images / session X 10000 sessions) allocated over the 10 tables, at
a peak of 10000 sessions online concurrently. At e.g. 1000 bytes / row
image, that would mean a peak total of 100 MB (100,000 row images X 1000
bytes / image) of storage allocated to the 10 session context tables
altogether.

Those 10000 suddenly-initiated concurrent sessions are then 10000
people, each poking at an HTML page in their browser, maybe once every
30 to 60 seconds, to trigger an HTTP request. So the database server
will receive from 167 to 333 <SELECT + UPDATE> requests per second
(10000 sessions / 60 seconds; 10000 sessions / 30 seconds).

But when the 10000 sessions created by that 10-second burst of session
initiations, gradually "die" over the next half hour or so, the entire
100 MB of storage in the 10 tables, will get recycled back to the
filesystem, by only 10 very swift TRUNCATE commands. And there will be
absolutely no <row DELETE ... / table AUTOVACUUM / table VACUUM)
workload overhead, to hamper performance during all that session
activity.

> If its a huge number, you should put effort into growing horizontally,
> not all of this stuff.
> If its a small number, you'll spend more time fixing all the broken
> things than its worth.
> Have you benchmarked this?  In my mind, complicated == slow.
>

All valid points.

Wouldn't using multiple session context tables, enable the possibility
of horizontal growth? Could separate pools of session context tables go
in separate tablespaces, each tablespace on a different SSHD for one
pool ?

I'm not underestimating the work required to get the idea to a stable
functional state.

Yes -- complicated does consume more cycles. That's why I'm proposing to
use sequence generators (as rapid-access global "iterators"), instead of
using some bottleneck of a tiny table, to keep order among the app
sessions as they look for permission to access the session context
tables.

No I haven't benchmarked the idea. It's still at the feasibility
consultation / investigation stage. But certainly benchmarking is going
to be mandatory. If I decide to proceed beyond just brainstorming the
idea, I will build and benchmark a prototype to stress-test the key
design ideas.

> Sorry if I'm raining on your parade, it looks like you have really put
> a lot of work into this.
>

I'm grateful to you for the downpour of advice :)

Yes I have invested quite a few hours. But all pleasant ones. And I have
the hours to invest, and the luxury of no boss' butt to kiss. So it
won't be a problem if I decide to ditch the idea of using postgres as a
session store, and go with e.g. your web proxy suggestion instead.

But I really would like if possible, to develop some innovation using
postgres, of significant but not overwhelming challenge. Something that
might help postgres invade a new market.

> Have you considered saving session data to disk is faster than saving
> to db?  A good reverse web proxy can stick a session to the same
> backend.  1 web proxy up front, 5 web servers behind it.  I'd bet its
> way faster.
>

Yes I have considered using a plain disk file, but then that's another
complication, no?

I'm already climbing a steep learning curve with postgres. So if I'm
going to complicate my life with some fancy session operations scheme, I
would prefer to lodge that complexity firmly in the world of postgres.

For sure, before I do more work on the idea I'm proposing, I will
investigate your idea of using a web proxy instead.

But then, adding a web proxy to the mix, would be a different kind of
complication in itself ...

> -Andy
>