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

On Sun, 21 Jun 2009 21:43:16 +0800
Craig Ringer <craig@postnewspapers.com.au> wrote:

> 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'm still checking if real case go far beyond to the above or I've
some more general case.

> 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.

The stuff is really serial in its nature and can't be made parallel.

> > 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:

http://www.postgresql.org/docs/8.3/interactive/sql-createtable.html
"If specified, the table is created as a temporary table. Temporary
tables are automatically dropped at the end of a session"

I'd interpret it as a connection.
I don't even know if it is possible to send more than one command
over a single connection and wait for the results asynchronously.
Any clarification?

If a session is a connection and it's not possible to send more than
one command and wait for the results asyncronously... or the server
doesn't run in parallel several command sent across the same
connection... than a temp table looks like what I need.

> BEGIN;
> CREATE TEMPORARY TABLE x (...);
> -- do other work
> COMMIT;
> BEGIN;
> -- Can see `x' from here
> COMMIT;
>
> ?

I was planning to use ON COMMIT DROP

> 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' .

That's perfect, and what I really need.

> > 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.

So postgresql actually issues writes to disk and delegate to the OS
management of the cache/actual write on disk.
I thought it could just try to hold them in RAM and still delegate
to the OS to save them on disk in swap if the system is short on RAM.

thanks

--
Ivan Sergio Borgonovo
http://www.webthatworks.it