Re: [PATCHES] contrib mode - pgenv

[Mike Mascari]

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', 10);

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
[EMAIL PROTECTED]





---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [PATCHES] contrib mode - pgenv

[Mike Mascari]

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
[EMAIL PROTECTED]




---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [PATCHES] contrib mode - pgenv

[Mike Mascari]

Tom Lane wrote:

 Peter Eisentraut [EMAIL PROTECTED] 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
[EMAIL PROTECTED]





---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly