Re: Temporary tables and disk activity

Tom Lane wrote:
> Phil Endecott <spam_from_postgresql_general(at)chezphil(dot)org> writes:
>
>>Does this make sense? I imagine that the temporary table is being added
>>to these tables and then removed again.
>
>
> Yes, a temp table has the same catalog infrastructure as a regular
> table, so creation and deletion of a temp table will cause some activity
> in those catalogs. I thought you were concerned about the data within
> the temp table, though.

I'm concerned about unnecessary disk activity, whatever its cause. I
guessed that it was the temp table contents.

>>I do have quite a large number of tables in the database; I have one
>>schema per user and of the order of 20 tables per user and 200 users. I
>>can imagine that in a system with fewer tables this would be
>>insignificant, yet in my case it seems to be writing of the order of a
>>megabyte in each 5-second update.
>
>
> That seems like a lot. How often do you create/delete temp tables?

Only once or twice per 5-sec update period. I agree that it sounds like
a lot which makes me think this could all be a red herring; I suspect
that there is something else going on as well as this temp table stuff
(possibly nothing to do with postgresql). But FYI this is treefic.com,
a family tree website. Have a look at, for example,
http://treefic.com/treefic/royal92?a=tree_page&root_id=10286&direction=up
The first step in building that diagram is to find the ancestors of the
root individual. I have a pl_pgsql function that itteratively finds all
of the ancestors, progressively adding them to a temporary table. So it
will create, populate, read and then drop one table for each page that
it generates. This is reasonably fast; overall speed is not limited by
postgres.

What would happen if I were to rollback at the end of the transaction,
rather than committing (having made no changes)? Would that eliminate
some or all of the catalog writes?

Many thanks for helping me understand this.

Regards,

Phil.