Re: [PATCHES] 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 [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
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
Re: [PATCHES] contrib mode - pgenv
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