|
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@xxxxxxxxxxxxxxxx. I
believe the last posting to that thread was on 1 December 2015 by George Neuner
<gneuner2@xxxxxxxxxxx>.
A related thread of interest, was started earlier
-- subject " using postgresql for
session", on 7 October 2015 by John Tiger <john.tigernassau@xxxxxxxxx>.
* * *
* * *
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.
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.
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
|
Attachment:
eto_sql_pg - Session Context Storage - 8.1 Synchronize Process Access To Table - 20160103.pdf
Description: Adobe PDF document
-- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
