Re: contrib mode - pgenv

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(at)mascari(dot)com