Re: Sharing data between stored functions? - Mailing list pgsql-general

On 03/05/2015 07:37 AM, inspector morse wrote:
> I'm confused with what Igor said. He said to create the temporary table
> with "on commit preseve rows" but in the documentation it states that
> when a session ends (like after each request in a web application when
> not using persistent connections), the temporary table would
> automatically be dropped.
>
> Just to test it, I created the following in a new session:
> CREATE TEMPORARY TABLE http_querystring_values (key TEXT NOT NULL
> PRIMARY KEY, value TEXT NOT NULL) ON COMMIT DELETE ROWS;
>
> However, none of the other sessions can find this table. This is correct
> according to the documentation.
>
> Is there any reason why Postgresql does not implement the SQL standard's
> version of GLOBAL temporary tables?
>
> I also don't like the idea of passing the "state" around to functions.
> I'd rather just query a table OR read some global variable. The JSON
> /HSTORE syntax looks horrible, I just like to stick with the simplicity
> of a table.

So create a permanent session table and assign session keys. Strangely
enough that is one of the ways Django(web framework does it):)  I know
you want to build your set up, but this is a solved problem. It is
solved because people ran into the issues you already have encountered
and are yet to encounter and realized that common problems led to common
solutions. Given that you want to reinvent the wheel I would suggest
spending time looking at the other wheels out there and borrow from them.

>
>
>
>
> On Thu, Mar 5, 2015 at 10:18 AM, Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>
>     On 03/05/2015 07:10 AM, Merlin Moncure wrote:
>
>         On Thu, Mar 5, 2015 at 8:58 AM, Igor Neyman
>         <ineyman@perceptron.com <mailto:ineyman@perceptron.com>> wrote:
>
>             From: pgsql-general-owner@__postgresql.org
>             <mailto:pgsql-general-owner@postgresql.org>
>             [mailto:pgsql-general-owner@__postgresql.org
>             <mailto:pgsql-general-owner@postgresql.org>] On Behalf Of
>             inspector morse
>             Sent: Thursday, March 05, 2015 9:21 AM
>             To: pgsql-general@postgresql.org
>             <mailto:pgsql-general@postgresql.org>
>             Subject: [GENERAL] 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
>     <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@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com