Thread: contrib mode - pgenv

Out of sheer shame for a stupid remark, I have implemented a pgenv
contrib module which provides three functions:

set_session_variable (name, value)
get_session_variable (name)
reset_session_variables()

I hope my penance is acceptable...

Mike Mascari
mascarm@mascari.com

Mike Mascari writes:

> Out of sheer shame for a stupid remark, I have implemented a pgenv
> contrib module which provides three functions:
>
> set_session_variable (name, value)
> get_session_variable (name)
> reset_session_variables()

How is this better than temporary tables?

--
Peter Eisentraut   peter_e@gmx.net

Peter Eisentraut wrote:
> Mike Mascari writes:
>
>
>>Out of sheer shame for a stupid remark, I have implemented a pgenv
>>contrib module which provides three functions:
>>
>>set_session_variable (name, value)
>>get_session_variable (name)
>>reset_session_variables()
>
>
> How is this better than temporary tables?

Well, I basically implemented it as a result of this thread:

http://archives.postgresql.org/pgsql-general/2003-09/msg01347.php

But I can imagine people doing something like this:

Earlier:
--------

CREATE TABLE budgets (
deptid text not null,
budget numeric(16,2);

INSERT INTO budgets
VALUES ('Peter', 100000);

CREATE VIEW v_budgets AS
SELECT *
FROM budgets
WHERE deptid = get_session_variable('deptid');

Upon connecting:
---------------

SELECT set_session_variable('deptid', 'Peter');

SELECT *
FROM v_budgets;

I assume the PHP folks have other reasons as well. Although I'm not
sure...

Mike Mascari
mascarm@mascari.com

I wrote:

> Peter Eisentraut wrote:
>
>>How is this better than temporary tables?
>
> Well, I basically implemented it as a result of this thread:
>
> http://archives.postgresql.org/pgsql-general/2003-09/msg01347.php

I should also point out that if the idea were to be accepted as a
contrib module, I'd like to submit another one that:

1) Uses int32 consistently. I use int in a few places.
2) Doesn't include trigger.h/spi.h. Copy and paste error.
3) Creates the function get_session_variable() with the STABLE option.

Mike Mascari
mascarm@mascari.com

Mike Mascari writes:

> Well, I basically implemented it as a result of this thread:
>
> http://archives.postgresql.org/pgsql-general/2003-09/msg01347.php

We've rejected session variables many times before because they duplicate
temporary tables.  I don't see anything new added by this proposal.

> But I can imagine people doing something like this:

This is how temporary tables would operate if they followed the SQL
standard.  (That is, the temporary table structure is persistent but the
contents are deleted at the end of the session.  See compatibility section
of CREATE TABLE reference page.)  Perhaps implementing that would satisfy
your needs.

--
Peter Eisentraut   peter_e@gmx.net

Peter Eisentraut <peter_e@gmx.net> writes:
> Mike Mascari writes:
>> Well, I basically implemented it as a result of this thread:
>> http://archives.postgresql.org/pgsql-general/2003-09/msg01347.php

> We've rejected session variables many times before because they duplicate
> temporary tables.  I don't see anything new added by this proposal.

I should think there would be a notable performance advantage, since
one need not create a temp table (which in our current implementation is
just as expensive as creating a permanent table); not to mention
dropping the temp table later, vacuuming up the resulting dead rows in
pg_class and pg_attribute, etc.  Whether that advantage is great enough
to justify a nonstandard feature is unproven, but I imagine Mike could
answer it with a little experimentation.

I guess the main limitation that I see in this proposal is that it can
only handle a single variable datatype (namely text).  In a temp-table
design you can of course make the column datatype(s) whatever you need.
This objection could perhaps be answered just by making the function
names be get_session_variable_text() and set_session_variable_text(),
to leave room for variants for other data types.

            regards, tom lane

Tom Lane writes:

> I should think there would be a notable performance advantage, since
> one need not create a temp table (which in our current implementation is
> just as expensive as creating a permanent table); not to mention
> dropping the temp table later, vacuuming up the resulting dead rows in
> pg_class and pg_attribute, etc.  Whether that advantage is great enough
> to justify a nonstandard feature is unproven, but I imagine Mike could
> answer it with a little experimentation.

We could support that if we implemented temporary tables with the standard
SQL semantics, namely that the table structure persists but the data is
deleted at the end of the session.  That would also help in some other
scenarios, such as creating functions that reference temporary tables.

--
Peter Eisentraut   peter_e@gmx.net

Peter Eisentraut wrote:

> Tom Lane writes:
>
>
>>I should think there would be a notable performance advantage, since
>>one need not create a temp table (which in our current implementation is
>>just as expensive as creating a permanent table); not to mention
>>dropping the temp table later, vacuuming up the resulting dead rows in
>>pg_class and pg_attribute, etc.  Whether that advantage is great enough
>>to justify a nonstandard feature is unproven, but I imagine Mike could
>>answer it with a little experimentation.
>
> We could support that if we implemented temporary tables with the standard
> SQL semantics, namely that the table structure persists but the data is
> deleted at the end of the session.  That would also help in some other
> scenarios, such as creating functions that reference temporary tables.

You're right, Peter. Another problem with the contrib module is that
it ignores transaction semantics:

BEGIN;
SELECT set_session_variable('Mike', 1);
ABORT;

And, in fact, the problem (Richard Huxton's desire for essentially
parameterized views) it attemps to solve could be worked around by
using a normal table with a function, assuming a function to get the
SQL-session-identifier exists, such as the backend_pid() function in
/contrib/misc:

CREATE TABLE session_data (
key SERIAL NOT NULL,
session_identifier text not null,
session_var text not null);

CREATE INDEX i_session_data1 ON session_data(session_identifier);

CREATE FUNCTION APP_SESSION_VAR() RETURNS text AS '
 SELECT session_var
 FROM session_data
 WHERE session_identifier = backend_pid()
 ORDER BY key DESC LIMIT 1;
' LANGUAGE 'SQL' STABLE;

CREATE VIEW my_project_list AS
SELECT *
FROM project_list
WHERE owner = APP_SESSION_VAR();

-- Upon session creation

INSERT INTO session_data (session_identifier, session_var)
VALUES (backend_pid(), 'Mike');

SELECT *
FROM my_project_list;
...

Mike Mascari
mascarm@mascari.com

Tom Lane wrote:

> Peter Eisentraut <peter_e@gmx.net> writes:
>
>>We've rejected session variables many times before because they duplicate
>>temporary tables.  I don't see anything new added by this proposal.
>
> I should think there would be a notable performance advantage, since
> one need not create a temp table (which in our current implementation is
> just as expensive as creating a permanent table); not to mention
> dropping the temp table later, vacuuming up the resulting dead rows in
> pg_class and pg_attribute, etc.  Whether that advantage is great enough
> to justify a nonstandard feature is unproven, but I imagine Mike could
> answer it with a little experimentation.

Yes. I guess the lifetime of this contrib module would be short -  SQL
temporary tables that don't suffer those performance penalties would
be the correct solution.

I think it might be useful to some in the interim. From what I've seen
on the mailing lists, people would like to build VIEW driven
applications where the application maintains users and therefore they
don't have the ability to leverage CURRENT_USER in view definitions...

This gives them that opportunity. Perhaps its usefulness doesn't
warrant a contrib module though...

Mike Mascari
mascarm@mascari.com

On Tuesday 23 September 2003 01:19, Mike Mascari wrote:
> Yes. I guess the lifetime of this contrib module would be short -  SQL
> temporary tables that don't suffer those performance penalties would
> be the correct solution.

Not for what I was thinking of (see below).

> I think it might be useful to some in the interim. From what I've seen
> on the mailing lists, people would like to build VIEW driven
> applications where the application maintains users and therefore they
> don't have the ability to leverage CURRENT_USER in view definitions...

From the PHP/webapp point of view, you need the ability to store a _single_
value so you can reference a permanent session-table. You use your session
table to store the actual values. Temporary tables mean I am doing one of two
things:
1. Creating a table to store one value, repopulating it on every connection.
2. Handling all my session values in the scripting language and
creating/populating a table to hold them all.

Neither is quite as convenient as CURRENT_SESSION_ID or whatever it might be
called. All that needs to do is store one text value (which covers text and
numeric session ids), ideally with the option of "locking" it so once set it
cannot be changed until a disconnect. The locking needs to be optional for
those using connection pooling.

> This gives them that opportunity. Perhaps its usefulness doesn't
> warrant a contrib module though...

Given that a lot of the target audience for this might be sharing an
installation of PG, it would need to be something fairly widely installed.
Given that they're not necessarily experienced DBAs it needs to be something
simple to use and understand.

PS - thanks for ccing me on this, I wasn't subscribed to -patches.
--
  Richard Huxton
  Archonet Ltd