Per-session data?

[Steve Atkins]

I have an application where each user session opens and maintains a
long-lived connection to the postgresql backend database.

I need to keep a small amount of information (things like a unique
session identifier, the application - as opposed to database - username
and so on) that is local to each database session. It needs to be
visible from within plpgsql trigger functions and will be used
on a large fraction of updates.

I can see a few ways of doing it, none of them terribly pretty:

Keep all the data in a globally visible table, indexed by the
PID of the database backend.

Create a temporary table at the beginning of each session containing
the data, and simply read it out of that, relying on the temporary
table to be session-local.

Anyone have a suggestion for something that's either prettier, lower
overhead or both?

Cheers,
Steve
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Peter Eisentraut]

Steve Atkins wrote:

I need to keep a small amount of information (things like a unique
session identifier, the application - as opposed to database -
username and so on) that is local to each database session. It needs
to be visible from within plpgsql trigger functions and will be used
on a large fraction of updates. Create a temporary table at the beginning of each session
containing the data, and simply read it out of that, relying on the
temporary table to be session-local.

This is exactly how you should do it.
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

[Peter Eisentraut]

Steve Atkins wrote:

I need to keep a small amount of information (things like a unique
session identifier, the application - as opposed to database -
username and so on) that is local to each database session. It needs
to be visible from within plpgsql trigger functions and will be used
on a large fraction of updates. Create a temporary table at the beginning of each session
containing the data, and simply read it out of that, relying on the
temporary table to be session-local.

This is exactly how you should do it.
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

[Mike Mascari]

Steve Atkins wrote:

I have an application where each user session opens and maintains a
long-lived connection to the postgresql backend database.

I need to keep a small amount of information (things like a unique
session identifier, the application - as opposed to database - username
and so on) that is local to each database session. It needs to be
visible from within plpgsql trigger functions and will be used
on a large fraction of updates.

I can see a few ways of doing it, none of them terribly pretty:

Keep all the data in a globally visible table, indexed by the
PID of the database backend.

Create a temporary table at the beginning of each session containing
the data, and simply read it out of that, relying on the temporary
table to be session-local.

Anyone have a suggestion for something that's either prettier, lower
overhead or both?

One way to do this is to write a C-language function to set a global
variable and another to read from that variable. I.e. write a:

void setCookie(text) ;
text getCookie();

pair, invoking setCookie() upon connecting to the database.

There are three problems with using PostgreSQL temporary tables:

1. PL/pgSQL will cache the OID of the temporary table that existed
when it is first parsed, and when that temporary table is dropped
and recreated later, despite having the same name and structure,
you'll get an error like:

ERROR: relation with OID 869140 does not exist
CONTEXT: PL/pgSQL function "mytest" line 4 at select into variables

The work-around is to use EXECUTE to build the query string at
run-time.

2. For the same reason as above, you cannot build views against the
session-local temporary table. However, you could write a wrapper
PL/pgSQL function that leverages EXECUTE and use that wrapper
function in your view definition.

3. Under pre-7.4 databases, the large number of temporary table
creations/drops caused system catalog index bloat, which required
occassional REINDEXing under a stand-alone postgres backend. Under
7.4's index reuse, that seems to have abated.

You can build both PL/pgSQL functions and VIEWs against your 'C'
functions though. I suspect performance would be better, but you'd
have to do some testing. In the long run, I assume PostgreSQL will
one day have SQL temporary tables (whose structure persists across
sessions but whose content does not) which will ultimately be the
correct solution. Until then, it's a matter of preference and
hoop-jumping depending upon server version...

HTH,

Mike Mascari


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

[Mike Mascari]

Steve Atkins wrote:

I have an application where each user session opens and maintains a
long-lived connection to the postgresql backend database.

I need to keep a small amount of information (things like a unique
session identifier, the application - as opposed to database - username
and so on) that is local to each database session. It needs to be
visible from within plpgsql trigger functions and will be used
on a large fraction of updates.

I can see a few ways of doing it, none of them terribly pretty:

Keep all the data in a globally visible table, indexed by the
PID of the database backend.

Create a temporary table at the beginning of each session containing
the data, and simply read it out of that, relying on the temporary
table to be session-local.

Anyone have a suggestion for something that's either prettier, lower
overhead or both?

One way to do this is to write a C-language function to set a global
variable and another to read from that variable. I.e. write a:

void setCookie(text) ;
text getCookie();

pair, invoking setCookie() upon connecting to the database.

There are three problems with using PostgreSQL temporary tables:

1. PL/pgSQL will cache the OID of the temporary table that existed
when it is first parsed, and when that temporary table is dropped
and recreated later, despite having the same name and structure,
you'll get an error like:

ERROR: relation with OID 869140 does not exist
CONTEXT: PL/pgSQL function "mytest" line 4 at select into variables

The work-around is to use EXECUTE to build the query string at
run-time.

2. For the same reason as above, you cannot build views against the
session-local temporary table. However, you could write a wrapper
PL/pgSQL function that leverages EXECUTE and use that wrapper
function in your view definition.

3. Under pre-7.4 databases, the large number of temporary table
creations/drops caused system catalog index bloat, which required
occassional REINDEXing under a stand-alone postgres backend. Under
7.4's index reuse, that seems to have abated.

You can build both PL/pgSQL functions and VIEWs against your 'C'
functions though. I suspect performance would be better, but you'd
have to do some testing. In the long run, I assume PostgreSQL will
one day have SQL temporary tables (whose structure persists across
sessions but whose content does not) which will ultimately be the
correct solution. Until then, it's a matter of preference and
hoop-jumping depending upon server version...

HTH,

Mike Mascari


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

[Steve Atkins]

On Mon, May 03, 2004 at 02:18:28PM -0400, Mike Mascari wrote:

I wrote:

There are three problems with using PostgreSQL temporary tables:

1. PL/pgSQL will cache the OID of the temporary table that existed when
it is first parsed, and when that temporary table is dropped and
recreated later, despite having the same name and structure, you'll get
an error like:

ERROR: relation with OID 869140 does not exist
CONTEXT: PL/pgSQL function "mytest" line 4 at select into variables

The work-around is to use EXECUTE to build the query string at run-time.

I should point out that if you ensure that you create the temporary
table before invoking the function, the function gets parsed once
after the first invocation at the beginning of the session. So that,
in that instance, it would be okay.

OK, sounds like that'll work nicely for the particular application I'm
working on. Thanks.

Cheers,
Steve
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Steve Atkins]

On Mon, May 03, 2004 at 02:18:28PM -0400, Mike Mascari wrote:

I wrote:

There are three problems with using PostgreSQL temporary tables:

1. PL/pgSQL will cache the OID of the temporary table that existed when
it is first parsed, and when that temporary table is dropped and
recreated later, despite having the same name and structure, you'll get
an error like:

ERROR: relation with OID 869140 does not exist
CONTEXT: PL/pgSQL function "mytest" line 4 at select into variables

The work-around is to use EXECUTE to build the query string at run-time.

I should point out that if you ensure that you create the temporary
table before invoking the function, the function gets parsed once
after the first invocation at the beginning of the session. So that,
in that instance, it would be okay.

OK, sounds like that'll work nicely for the particular application I'm
working on. Thanks.

Cheers,
Steve
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Mike Mascari]

I wrote:

There are three problems with using PostgreSQL temporary tables:

1. PL/pgSQL will cache the OID of the temporary table that existed when
it is first parsed, and when that temporary table is dropped and
recreated later, despite having the same name and structure, you'll get
an error like:

ERROR: relation with OID 869140 does not exist
CONTEXT: PL/pgSQL function "mytest" line 4 at select into variables

The work-around is to use EXECUTE to build the query string at run-time.

I should point out that if you ensure that you create the temporary
table before invoking the function, the function gets parsed once
after the first invocation at the beginning of the session. So that,
in that instance, it would be okay.

Mike Mascari

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Mike Mascari]

I wrote:

There are three problems with using PostgreSQL temporary tables:

1. PL/pgSQL will cache the OID of the temporary table that existed when
it is first parsed, and when that temporary table is dropped and
recreated later, despite having the same name and structure, you'll get
an error like:

ERROR: relation with OID 869140 does not exist
CONTEXT: PL/pgSQL function "mytest" line 4 at select into variables

The work-around is to use EXECUTE to build the query string at run-time.

I should point out that if you ensure that you create the temporary
table before invoking the function, the function gets parsed once
after the first invocation at the beginning of the session. So that,
in that instance, it would be okay.

Mike Mascari

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend