Re: cursors, temp tables, dynamic sql technique (was and is: looping over a small record set over and over in a function)

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