Re: Temporary Tables and Web Application
От | Tim Tassonis |
---|---|
Тема | Re: Temporary Tables and Web Application |
Дата | |
Msg-id | 48483210.6090200@cubic.ch обсуждение исходный текст |
Ответ на | Re: Temporary Tables and Web Application (Tino Wildenhain <tino@wildenhain.de>) |
Список | pgsql-general |
Tino Wildenhain wrote: > Hi, > > Tim Tassonis wrote: >> Hi all >> >> I assume this is not an uncommon problem, but so far, I haven't been >> able to find a good answer to it. >> >> I've got a table that holds log entries and fills up very fast during >> the day, it gets approx. 25 million rows per day. I'm now building a >> web application using apache/mod_php where you can query the database >> and then should be able to page through the results. > > you should be aware that PHP isnt the only scripting language with an > apache module and not neccessary the best choice among them. There's no need to become insulting. I am aware of the truly astonishing fact that there are other scripting languages apart from php and that not everybody loves php. Apart from the sad fact that I quite like php, the problem is not the choice of scripting language, but the nature of apache mpm processing, making the postgres connection stuck to an apache process. > >> My idea was that whenever a user constructs a query, I create a >> temporary table holding the results and then page through this table, >> which should work very well in principle. > > That means you are more or less constructing materialized views :-) No, I want the data to remain fixed after the query is executed. > But if you hold the session anyway, then see below. I don't hold the session, see above. > > >> But from what I've been able to find out, temporary tables live only >> in the Postgres Session they have been created in and are destroyed >> upon session descructuion. >> >> Now, with apache/php in a mpm environment, I have no guarantee that a >> user will get the same postgresql session for a subsequent request, >> thus he will not see the temporary table. > > Thats the problem and if you have failover/loadbalancing situations, > even more so. > >> Is there a way to create temporary tables in another way, so they are >> visible between sessions, or do I need to create real tables for my >> purpose? And is the perfomance penalty big for real tables, as they >> have been written to disk/read from disk? > > To start with, you should avoid reconnecting to the database for every > request. Not only because of loosing the session context but also > to avoid connection overhead. I don't reconnect after every request, but I'm not guaranteed by mpm that I get the same session/process. I might, but that's hardly what I'd call a stable application, even as a php programmer. > > Usually this is done by connection pooling. You can then try to trac > user:connection relationship as much as possible thru the connection pool. As far as I can see, there is no implementation of a multi client process connection pool in mod_php. I admit that my interprocess communication know-how is not very deep, but that would mean the client postgres/tcpip connection part would have to be held somewhere in shared memory between the different apache processes. From reading the documentation, php does not do that. > > If you have that, there is actually no need for the temp tables. Instead > you can just use a regular cursor and scroll it as neccessary. My problem ist that I don't have that. > > Almost all frameworks should give you reasonable pool implementations, > some additional memory caching on top of it and there are also a lot > of other methods to help you with that, for example pgpool and > pgbouncer. I'm afraid you somehow missed the point, but thanks for your response. Bye Tim
В списке pgsql-general по дате отправления: