On Sun, 2009-06-21 at 14:57 +0200, Ivan Sergio Borgonovo wrote: > I think everything could be summed up as: > > select into t myaggregate1(field) from dataset where condition1; > if(t>10) then > update dataset set field=myfunc1(a,b,c) where condition1; > end if; > > select into t myaggregate2(field) from dataset where condition2; > if(t>44) then > update dataset set field=myfunc2(a,b,c) where condition2; > end if;
That's really too simplified to see what you're actually doing. I've found that in the _vast_ majority of non-trigger cases where I've considered using PL/PgSQL, a bit more thought and proper consideration of the use of generate_series, subqueries in FROM, join types, etc has allowed me to find a way to do it in SQL. It's almost always faster, cleaner, and nicer if I do find a way to express it in SQL, too. > I think I really don't have a clear picture of how temp tables > really work. > They can be seen by concurrent transactions in the same session. Eh? In this context, what do you mean by "session"? Did you mean consecutive rather than concurrent, ie: BEGIN; CREATE TEMPORARY TABLE x (...); -- do other work COMMIT; BEGIN; -- Can see `x' from here COMMIT; ? Normally, you couldn't see another sessions temp tables, even after commit, unless you explicitly schema-qualified their names - eg 'pg_temp_4.x' . If even then; I haven't actually checked. Two concurrent sessions that issue 'CREATE TEMPORARY TABLE x(...)' get two DIFFERENT tables, both named `x', in different pg_temp schema, eg 'pg_temp_2.x' and 'pg_temp_3.x' . > But if the transaction in which a temp table is created is not > committed yet, other transactions won't see it. Of course, since PostgreSQL doesn't support the READ UNCOMMITTED isolation level. > So it may actually look more as a temporary, private storage > that doesn't have to be aware of concurrency. > > So temp tables should require less disk IO. Is it? The big thing is that they're private to a session, so different sessions can concurrently be doing things with temp tables by the same name without treading on each others' feet. Because they're limited to the lifetime of the session, though, PostgreSQL doesn't need to care about ensuring that they're consistent in the case of a backend crash, unexpected server reset, etc. Tom Lane recently pointed out that as a result writes don't need to go through the WAL, so my understanding is that you're avoiding the doubled-up disk I/O from that. They also don't need to be written with O_SYNC or fsync()ed since we don't care if writes make it to the table in order. As a result I'm pretty sure temp tables don't ever have to hit the disk. If the OS has enough write-cache space it can just store them in RAM from creation to deletion. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
