Re: Trigger on Postgres for tables syncronization
От | Pierre-Frédéric Caillaud |
---|---|
Тема | Re: Trigger on Postgres for tables syncronization |
Дата | |
Msg-id | opsbugqvwvcq72hf@musicbox обсуждение исходный текст |
Ответ на | Re: Trigger on Postgres for tables syncronization (Jeff Davis <jdavis-pgsql@empires.org>) |
Ответы |
Re: Trigger on Postgres for tables syncronization
|
Список | pgsql-general |
I'm a postgresql newcomer so correct me if I'm wrong... I also want to ask another question. I would have done this with a view, too, because it's very simple to do in Postgresql. You can also add some rules (or triggers ?) so that an insert attempt in appointment0 or appointment1 (which would normally fail) would be rewritten as an insert into appointment with the 'done' value set accordingly. Now, I've been facing a related problem with tracking user sessions for a web app. I want to use a table to store user sessions, both active sessions and expired sessions for archiving. I also wanted it to look like two different tables. I could have created one table with two views (online and archived), or two tables. In the end I went with two tables because the online session table is read and updated very often, so it better be small and fit in the cache, while the archive table will probably be huge and not used often. So to keep better locality of reference I used two tables, and I created functions to create sessions, update a session to push its timeout value a bit in the future, and close a session. These functions detect timed-out sessions in the "online" table and move them to the "archive" table. I also have a cleanup function which moves expired sessions to the archive table and which will be called by a cron. Advantages of this approach : - There can be only one session for a given user in the "online" table, which makes finding the session fast (userid = primary key). - The online table has only one index for faster updating, this is the primary key on userid. Drawbacks : - Much more complex than a view based approach. Question : how huge is huge, ie. how much records do I need to have in the archive to make the two tables approach worth it ? It is much more complex. On Tue, 27 Jul 2004 10:12:13 -0700, Jeff Davis <jdavis-pgsql@empires.org> wrote: > Try a view defined like: > > CREATE VIEW appointment0 AS SELECT * FROM appointment WHERE done='Y'; > CREATE VIEW appointment1 AS SELECT * FROM appointment WHERE done='N'; > > Then appointment0 and appointment1 are not real tables, but "virtual > tables". You can still do:
В списке pgsql-general по дате отправления: