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 22:11 +0200, Ivan Sergio Borgonovo wrote:

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

Correctly.

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

To an extent cursors provide that ability. The "result" is returned
quite promptly, but it's a placeholder that allows you to retrieve the
real results progressively as you need them. Whether the database
generates the results immediately and stores them to return later, or
whether it generates them on demand, isn't something you can easily tell
it's up to the database.

I don't know if PostgreSQL will pause the execution of set-returning
PL/PgSQL functions connected to cursors until the client requests more
data. Anyone?

By the way, a cursor is only valid within the context of the transaction
in which it's created.

If you don't explicitly ask to use a cursor, then no, commands cannot
run concurrently within the same session.

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

Yes.

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

For a variety of reasons, you REALLY don't want it to work that way.

OS memory managers tend to be _much_ better and faster at managing pages
that're backed by a file. They'll write dirty data out pre-emptively so
that execution doesn't stall when memory runs low; they write data to
the file in order for best disk performance; they efficiently buffer and
read-ahead when pulling the data back in, etc.

The OS knows much less about what anonymous memory (memory not backed by
a file) "means" to a program and can't be as clever with it. Swapping
tends to be _much_ more CPU expensive than writing dirty buffers to a
file. It's a lot more expensive to retrieve from disk, too, and usually
involves lots of seeks for quite scattered pages instead of nice block
readahead.

The OS knows much better than PostgreSQL does when the table will fit in
RAM and when it needs to spill to disk, and it's much better at managing
that than Pg can ever be. It's great that Pg just uses the OS's hardware
knowledge, system-wide awareness, and highly optimised memory manager +
disk IO management to take care of the problem.

--
Craig Ringer