Skip site navigation (1) Skip section navigation (2)

Re: contrib mode - pgenv

From: Mike Mascari <mascarm(at)mascari(dot)com>
To: Peter Eisentraut <peter_e(at)gmx(dot)net>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-patches(at)postgresql(dot)org,dev(at)archonet(dot)com
Subject: Re: contrib mode - pgenv
Date: 2003-09-22 23:46:08
Message-ID: 3F6F89C0.6080606@mascari.com (view raw or flat)
Thread:
Lists: pgsql-patches
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




In response to

pgsql-patches by date

Next:From: Mike MascariDate: 2003-09-23 00:19:12
Subject: Re: contrib mode - pgenv
Previous:From: Peter EisentrautDate: 2003-09-22 22:42:24
Subject: Re: contrib mode - pgenv

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group