Re: nooby Q: temp tables good for web apps?

Tom Lane wrote:
> Kenneth Tilton <kentilton(at)gmail(dot)com> writes:
>> I am porting a datamining web app to postgres from a non-sql datastore
>> and plan to use temporary tables quite a bit, to manage collections the
>> user will be massaging interactively. They might search and find
>> anywhere from 50 to 50k items, then filter that, unfilter, sort, etc.
>
> The main issue you should think about is whether the required lifespan
> of the temp tables matches up with your application's use of database
> connections. If you are going through a connection pooler, for example,
> it can be pretty awkward to hold onto the connection that has got the
> temp table instances you need. Web apps in general tend to have a hard
> time maintaining such state across successive page references, so I'm
> afraid this could be a show-stopper for you.

Ah, I should disclosed I am a relative web application nooby as well.
But our design depends anyway on the same server process handling a web
session from start to finish, and I thought this was doable with
sufficient effort. I mean, I asked and people said it could be arranged.
Not so? Yes, I am doomed. But if we can do that, I already have a ton of
logic for keeping sessions separate and for hanging onto a connection
for the life of a session (most requests use a PG connection pooler, the
ones that need to see the temp tables use a dedicated connection (or two
I think I might need). Keep sessions separate by working the session key
into the temp table name...well, that's the plan anyway.

>
>> Some on the team think I am nuts, but one reason given was the absence
>> of indices and I see (a) temporary tables *can* be indexed
>
> Yeah, whoever claimed that is simply uninformed, or at least is
> well-informed about some other database.

I think I misrepresented their position. They did not say it, but I
think they were referring to some hairy freetext indexing they did on
the permanent tables. And that's OK, we do not need that on the temp tables.

thx for the input, I will study up on the viability of getting a session
managed by the same process throughout.

ken