Re: Creating a session variable in Postgres

Nagib Abi Fadel wrote:

> So considering those facts, it would be better to use the Sequence Method,
> since it would only require cleaning up one table ....
> Or is there anything else i am missing ???

It is becoming more of a toss-op. Prior to 7.4, the system indexes
would grow until a manual REINDEX was issued in a stand-alone
backend. In 7.4, the dead tuples remain, but at least can be re-used
once they've been marked that way by the occassional vacuum.
autovacuum will tend to make dead-tuple reclaimation transparent,
like Oracle.

The absolutely cheapest method is to write a pair of functions in
'C' that sets/gets a global variable:

#include "postgres.h"
#include "fmgr.h"

#define MAX_DATA 64

char session_data[MAX_DATA] = "";

PG_FUNCTION_INFO_V1(setvalue);

Datum setvalue(PG_FUNCTION_ARGS) {

text *value;
long len;

value = PG_GETARG_TEXT_P(0);
len = VARSIZE(value) - VARHDRSZ;
if (len >= MAX_DATA) {
elog(ERROR, "setvalue: value too long: %li", len);
}
memcpy(session_data, VARDATA(value), len);
session_data[len] = 0;

PG_RETURN_BOOL(true);

}

PG_FUNCTION_INFO_V1(getvalue);

Datum getvalue(PG_FUNCTION_ARGS) {

text *result;
long len;

len = strlen(session_data) + VARHDRSZ;
result = (text *) palloc(len);
VARATT_SIZEP(result) = len;
memcpy(VARDATA(result), session_data, len - VARHDRSZ);

PG_RETURN_TEXT_P(result);

}

-- Compile

gcc -c example.c -I/usr/include/pgsql/server
gcc -shared -o pgexample.so pgexample.o

-- Install somewhere PostgreSQL can get at it

cp pgexample.so /usr/local/mypglibs

-- Create the functions where path-to-lib is the path to
-- the shared library.

CREATE OR REPLACE FUNCTION setvalue(text) RETURNS boolean
AS '/usr/local/mypglibs/pgexample.so'
LANGUAGE 'C' WITH (isStrict);

CREATE OR REPLACE FUNCTION getvalue() RETURNS text
AS '/usr/local/mypglibs/pgexample.so'
LANGUAGE 'C' WITH (isStrict);

Now all you need to to is invoke setvalue() at the start of the
session, and build views around getvalue():

CREATE VIEW v_foo AS
SELECT *
FROM foo
WHERE foo.key = getvalue();

At the start of a session:

SELECT setvalue('Mike Mascari was here');

Hope that helps,

Mike Mascari