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."
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."
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@xxxxxxxxx
<mailto:pavel.stehule@xxxxxxxxx>> wrote:
Hi
2016-01-03 20:46 GMT+01:00 Steve Petrie, P.Eng.
<apetrie@xxxxxxxxxxxx <mailto: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
<mailto:chris@xxxxxxxxxxxxxxxx>. I believe the last posting to
that thread was on 1 December 2015 by George Neuner
<gneuner2@xxxxxxxxxxx <mailto: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
<mailto: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 <mailto: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.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
