[postgis-users] Multiuser system question

[Jan Hartmann]

David Blasby wrote:
> Alexander Pucher wrote:
> 
>> Hi,
>>
>> I hope that my question is clear enough to be answered.
>>
>> I'm setting up a multiuser system with PostGIS and UMN MapServer. When 
>> the user starts the system, a unique id is assigned to the session.
>>
>> I offer the user a selection of "default" geometry sets to work with. 
>> During the session, the user can buffer geometries, which are stored 
>> as new geometry tables in the system. To keep track of the various 
>> users/tables, I have a kind of "lookup table", referencing the unique 
>> id with the geometry tables.
>>
>> The question is a rather pragmatic one. Should I set up one 
>> lookup-table and store all default tables along with all table 
>> references that all users create, or create a seperate lookup table 
>> for every session that is started? So consider 500 people with each 
>> more then 20 geometry tables working on the system the same time. What 
>> is more performant: all users working on one big table or working on 
>> several smaller ones?
> 
> 
> 
> You might want to check out postgresql's temporary tables.  I'm not sure 
> what kind of performance hit you take for using them, but I imagine that 
> its quite small compared to the headache of actually managing real 
> tables....
> 
> 

The problem with temp tables is that they don't live longer than a 
single database connection. If a user wants to save temporary results 
between MapServer calls, he has to create real tables and it is up to 
the application developer to do the bookkeeping. This is a pain in the 
head, as you say. I would guess that keeping all intermediary results in 
one table would be easier to organise than creating and deleting tables 
all over the place. I would also guess that one table is more efficient 
than many, but the main efficiency comes from the ease of application 
development.

I have often run against this problem. It could be solved if database 
connections could be held open between multiple MapServer requests. This 
has been discussed often on the mailing lists, but it is not a trivial 
problem.

Jan