On 03/05/2015 07:10 AM, Merlin Moncure wrote:
On Thu, Mar 5, 2015 at 8:58 AM, Igor Neyman <ineyman@xxxxxxxxxxxxxx> wrote:
From: pgsql-general-owner@xxxxxxxxxxxxxx
[mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of inspector morse
Sent: Thursday, March 05, 2015 9:21 AM
To: pgsql-general@xxxxxxxxxxxxxx
Subject: Sharing data between stored functions?
I have written a simple web application using pure pl/pgsql and so far it is
working really well (I find it quite easy to maintain as well especially in
terms of form validation).
Basically, apache/php passes receives the incoming web request and calls a
"serve_page" function in postgresql passing the querystring and post values.
The serve_page declares 5 temporary tables to store querystring values, post
values, validation messages, and general data that is going to be shared
between the functions.
Then it parses the page url and calls the appropriate "page render" stored
function.
Throughout the "building" the web page, several of the temporary tables are
written too (about 20-30 rows total would be add to the temporary table).
Once the page "html" is built, the temporary tables are dropped and the HTML
is sent back to php to write to the response stream.
I read in the documentation that temporary tables can cause catalog bloat or
performance issues.....in my context (where only 20-30 rows are written
every request and the table is dropped after rending), could it cause an
issue for many incoming requests?
You’d be better off not creating/dropping temp tables every time.
Just create global temp tables once with “ON COMMIT PRESERVE ROWS“ option,
and when any session uses them their contents will be private to this
session.
maybe 'ON COMMIT DELETE ROWS' would be a better choice, depending on
the scenario: if the state is only valid for a requst, then you'd
clear the state at the end of the transaction.
GLOBAL temp tables are deprecated. I'm curious why, because they are
so useful for this particular task.
Because they never existed:
http://www.postgresql.org/docs/9.3/interactive/sql-createtable.html#SQL-CREATETABLE-COMPATIBILITY
Compatibility
"The SQL standard also distinguishes between global and local temporary
tables, where a local temporary table has a separate set of contents for
each SQL module within each session, though its definition is still
shared across sessions. Since PostgreSQL does not support SQL modules,
this distinction is not relevant in PostgreSQL.
For compatibility's sake, PostgreSQL will accept the GLOBAL and LOCAL
keywords in a temporary table declaration, but they currently have no
effect. Use of these keywords is discouraged, since future versions of
PostgreSQL might adopt a more standard-compliant interpretation of their
meaning.
"
In plpgsql, it's also possible to maintain state by keeping it in
things like arrays of records that you pass around. In the future we
might use jsonb for this I think.
merlin
--
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
