Sharing data between stored functions?

From: 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
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.

Regards,

Igor Neyman

On Thu, Mar 5, 2015 at 8:58 AM, Igor Neyman <ineyman@perceptron.com> wrote:

From: 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
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.

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 03/05/2015 07:10 AM, Merlin Moncure wrote:

On Thu, Mar 5, 2015 at 8:58 AM, Igor Neyman <ineyman@perceptron.com> wrote:

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

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

On Thu, Mar 5, 2015 at 10:18 AM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

From: inspector morse [mailto:inspectormorse86@gmail.com]
Sent: Thursday, March 05, 2015 10:37 AM
To: Adrian Klaver
Cc: Merlin Moncure; Igor Neyman; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Sharing data between stored functions?

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.

----------------------------
Sorry, my reply was based on (old) Oracle knowledge.
Now, I'm wondering too, why PG didn't implement such basic (at least to me) feature.

Regards,
Igor Neyman

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 03/05/2015 07:48 AM, Igor Neyman wrote:

From: inspector morse [mailto:inspectormorse86@gmail.com]
Sent: Thursday, March 05, 2015 10:37 AM
To: Adrian Klaver
Cc: Merlin Moncure; Igor Neyman; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Sharing data between stored functions?

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.

----------------------------
Sorry, my reply was based on (old) Oracle knowledge.
Now, I'm wondering too, why PG didn't implement such basic (at least to me) feature.

Per my previous post, see:

http://www.postgresql.org/docs/9.4/interactive/sql-createtable.html

Compatibility

The CREATE TABLE command conforms to the SQL standard, with exceptions
listed below.

Temporary Tables
....

Regards,
Igor Neyman

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Sorry, my reply was based on (old) Oracle knowledge.
Now, I'm wondering too, why PG didn't implement such basic (at least to

me) feature.

Per my previous post, see:

http://www.postgresql.org/docs/9.4/interactive/sql-createtable.html

Compatibility

The CREATE TABLE command conforms to the SQL standard, with exceptions
listed below.

Temporary Tables
....

Regards,
Igor Neyman

--
Adrian Klaver
adrian.klaver@aklaver.com

m.b. I'm missing something, but this quote from docs doesn't explain "why", it just states that feature is not implemented.

Regards,
Igor Neyman

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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&gt;

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 03/05/2015 11:01 AM, Igor Neyman wrote:

Sorry, my reply was based on (old) Oracle knowledge.
Now, I'm wondering too, why PG didn't implement such basic (at least to

me) feature.

Per my previous post, see:

http://www.postgresql.org/docs/9.4/interactive/sql-createtable.html

Compatibility

The CREATE TABLE command conforms to the SQL standard, with exceptions
listed below.

Temporary Tables
....

Regards,
Igor Neyman

--
Adrian Klaver
adrian.klaver@aklaver.com

m.b. I'm missing something, but this quote from docs doesn't explain "why", it just states that feature is not implemented.

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

Regards,
Igor Neyman

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 3/5/15 1:05 PM, Adrian Klaver wrote:

Is there any reason why Postgresql does not implement the SQL standard's
version of GLOBAL temporary tables?

Because no one has gotten around to it. There's been discussion about it
(search the pgsql-hackers archives).

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.

At this point I'm confused as to whether you want something that spans
multiple sessions or not.

If you want something that goes beyond one backend session (but perhaps
persists for the length of a web session) then do what Adrian suggested.

If you only want something that lasts as long as a Postgres backend
session then another option is to use the global hash that pl/perl
provides. Despite possible confusion from it's name, it's only global
for each Postgres backend. You can store things in there and they will
be accessible for all functions. You could keep an array of composite
types (records) in there and that would act pretty much like a table.
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 03/05/2015 11:20 AM, Igor Neyman wrote:

-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
Sent: Thursday, March 05, 2015 2:07 PM
To: Igor Neyman; inspector morse
Cc: Merlin Moncure; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Sharing data between stored functions?

On 03/05/2015 11:01 AM, Igor Neyman wrote:

Sorry, my reply was based on (old) Oracle knowledge.
Now, I'm wondering too, why PG didn't implement such basic (at least
to

me) feature.

Per my previous post, see:

http://www.postgresql.org/docs/9.4/interactive/sql-createtable.html

Compatibility

The CREATE TABLE command conforms to the SQL standard, with
exceptions listed below.

Temporary Tables
....

Regards,
Igor Neyman

--
Adrian Klaver
adrian.klaver@aklaver.com

m.b. I'm missing something, but this quote from docs doesn't explain

"why", it just states that feature is not implemented.

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

Regards,
Igor Neyman

--
Adrian Klaver
adrian.klaver@aklaver.com

CCing list.

I can't completely agree with this reasoning.

Well that is something you are going to have to take up with the SQL
standards committee:

http://dbis-informatik.uibk.ac.at/files/ext/lehre/ss11/vo-ndbm/lit/ORel-SQL1999-IBM-Nelson-Mattos.pdf

Page 261

http://www.jtc1sc32.org/doc/N2151-2200/32N2153T-text_for_ballot-FDIS_9075-1.pdf

Page 25

May be I'm just used to Oracle's implementation. Oh, and in Oracle temp tables have nothing to do with SQL modules.
From Oracle docs:
"Specify GLOBAL TEMPORARY to indicate that the table is temporary and that its definition is visible to all sessions. The data in a temporary table is visible only to the session that inserts the data into the table.
A temporary table has a definition that persists the same as the definitions of regular tables, but it contains either session-specific or transaction-specific data. You specify whether the data is session- or transaction-specific with the ON COMMIT keywords (below)."

Regards,
Igor Neyman

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general