473,721 Members | 2,256 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Per-session data?

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

Nov 23 '05 #1
8 1982
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)

Nov 23 '05 #2
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)

Nov 23 '05 #3
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

Nov 23 '05 #4
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

Nov 23 '05 #5
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

Nov 23 '05 #6
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

Nov 23 '05 #7
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

Nov 23 '05 #8
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

Nov 23 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
523
by: roberto3214 | last post by:
Hi Guys, How are you? I have recently begun some testing on IIS 6.0 in regards to an asp.net application. After lots of testing I decided to create a simple 1k page size webpage to find out whats the max rps I can achieve. So I installed Microsoft Application Stress Tool (WAS) and started testing. I setup basic 2 tests page does no processing whatsoever: TEST 1
2
1930
by: Kallis | last post by:
Hello, I have the following situation when trying to localize my software: I have BIG solution with about 80 projects. In one of the projects I have a number of dialogs (the dialog project :-) ). I have decided that I do not want to use the "form"-mechanism (using the property "localized=true") for localization since the strings I show are not that "constant" during the life time of the dialog. Thus, I have to put the resources in...
7
1478
by: Rob Nicholson | last post by:
We're using a well known presentation layer library to implement complex controls on an intranet site. IE has the following limitation which effectively means that you can only have 30 <STYLE> tags per page. That's in effect 30 style sheets per page, not classes - each sheet can have as many style classes as you want. http://support.microsoft.com/default.aspx?scid=kb;en-us;262161 Unfortunately, this presentation library generates...
2
1092
by: needin4mation | last post by:
Hi, I have to decide between a per device and a per license issue. We have several web services that have functions that use things like LOGON_USER. If I have a per device license, does that mean things like LOGON_USER go away? Or that I cannot use Windows Integrated Authentication for UNC file server stuff, web pages, etc.? Thank you.
2
2317
by: Karl O. Pinc | last post by:
Hi, I don't suppose that the todo item: Referential Integrity o Add deferred trigger queue file (Jan) Means that there will be a statement like: CREATE TRIGGER ... FOR EACH TRANSACTION
12
1443
by: bruno at modulix | last post by:
Hi I'm currently playing with some (possibly weird...) code, and I'd have a use for per-instance descriptors, ie (dummy code): class DummyDescriptor(object): def __get__(self, obj, objtype=None): if obj is None: return self return getattr(obj, 'bar', 'no bar')
32
5817
by: Matias Jansson | last post by:
I come from a background of Java and C# where it is common practise to have one class per file in the file/project structure. As I have understood it, it is more common practice to have many classes in a Python module/file. What is the motivation behind it, would it be a bad idea to have a guideline in your project that promotes a one class per file structure (assuming most of the programmers a background similar to mine)?
10
11600
by: Joseph Geretz | last post by:
I need to calculate miles per degree longitude, which obviously depends on latitude since lines of longitude converge at the poles. Via Google, I've come up with the following calculation: private const double MilesPerDegLat = 69.04; private const double EarthRadiusMiles = 3963.1676; private static double PiDiv180 = Math.PI / 180; double MilesPerDegLon = MilesPerDegLat * Math.Cos(Latitude * PiDiv180)
9
12201
by: bakxchaixDD | last post by:
I DON'T GET THIS project: Many treadmills output the speed in miles per hour. However, most runners think of their pace in minutes and seconds per mile. Write a program that inputs a decimal value for miles per hour and converts the value to minutes and seconds per mile. Sample input, output: For input 5.5 mph, the output should be 10 minutes and 55 seconds per mile. For input 4 mph, the output should be 15 minutes and 0 seconds...
0
2106
by: raveekumarg | last post by:
hi what is the difference between per seat , per server , per processor and i am new to sql 2000 , pl do explain the same.
0
8738
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9376
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9230
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9085
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
5994
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4499
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4762
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3207
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2596
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.