On 8/20/07, Medi Montaseri <montaseri@gmail.com> wrote:
> You can think of a database as a filesystem as well. That is do some
> processing, store the result in temp table, do some more, etc,etc then merge
> and process temp tables to arrive at some result.
>
> Just as in the case of filesystem, if you are operating in a concurrent
> evironment, you need to fence against that. That is it is possible that at a
> given time two sessions will arrive at the same processing point where they
> need to create such temp tables.
Each session will get it's own temp table, even if they have the same name.
The real issue is what they do with the data in that temp table to
make sure that they're committing changes that make sense given the
current state of data in the database.
> The other solution which I prefer is to write a stored procedure to solve
> this. Or get creative with nested and complex SQL queries.
Note that nested queries still have some race conditions (such as with
aggregate functions) in postgresql.