Re: Question -- Session Operations - Feasibility Of Proposed Synchronization Method? - Mailing list pgsql-general

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