Re: Question -- Session Operations - Feasibility Of Proposed Synchronization Method? - Mailing list 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