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
>
>
