Thanks to Melvin for your remarks. My reply is posted below.
Steve
From: "Melvin Davidson"
Sent: Sunday, January 03, 2016 4:32 PM
More to the point, if you 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
Sorry for not having defined the term "session" in my original posting. Here
are quotes from the draft design document, for the proposed session
operations system:
"In this document, the term "session" does not refer to connections with a
postgres server through a postgres client, which connections are referred to
as "sessions" in postgres docs. In this document, the term "session" means a
dialog between an app and the consumer of that app, where the app may or may
not use postgres, for other than its session context data.
"The app that motivates this present proposed design for a postgres-based
session operations facility, is an Internet website application (HTML / HTTP
/ PHP / PostgreSQL) that provides services to a visitor using a web browser.
The "session" under discussion therefore, is used to maintain the
contextual data of the (visitor <=> app) dialog, during which dialog the app
will open and close numerous brief connections with the postgres client,
such connections also being referred to as "sessions" within postgres
docs.
So a TEMPORARY table will not suffice, but using a tablespace in SSHD for
the session operations tables, might yield performance gains like
TEMPORARY..
On Sun, Jan 3, 2016 at 3:43 PM, Pavel Stehule <pavel.stehule@xxxxxxxxx>
wrote:
Hi
2016-01-03 20:46 GMT+01:00 Steve Petrie, P.Eng. <apetrie@xxxxxxxxxxxx>:
*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 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@xxxxxxxxxxxxxx)
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.
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
