Hi 2016-01-03 20:46 GMT+01:00 Steve Petrie, P.Eng. <apet...@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 ch...@simplistix.co.uk. I > believe the last posting to that thread was on 1 December 2015 by George > Neuner <gneun...@comcast.net>. > > A related thread of interest, was started earlier -- subject *"[GENERAL] > using postgresql for session*", on 7 October 2015 by John Tiger < > john.tigernas...@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 > >