Re: what is difference between LOCAL and GLOBAL TEMP TABLES in PostgreSQL

"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
>> Tom Lane escribió:
>>> I rather doubt that. The most likely implementation would involve
>>> cloning a "template" entry into pg_class.
>
>> How about a new relkind which causes the table to be located in
>> PGDATA/base/<dboid>/pg_temp_<backendid>/<relfilenode>
>> So each backend can have its own copy of the table with the same
>> relfilenode; there's no need for extra catalog entries.
>
> Uh-huh. And what do you do with relpages, reltuples, relfrozenxid, and
> pg_statistic entries? What if one backend wants to TRUNCATE or CLUSTER
> its copy (requiring a new relfilenode)? Where does ALTER TABLE fit into
> this?

I would have suggested that when we construct the relcache entry for the table
we substitute a local version of refilenode for the global one.

None of those sound like hard problems. Certainly it's more invasive this way
but the other way is just a hack for complying with the letter of the spec
without actually making it work right. It would be silly and in many use
cases useless to have regular DML operating on data which has no business
being anything but backend-local generate garbage in on-disk catalog tables.

I had a strange thought though. The ideal data structure for local
pg_statistic data in the unlikely case that users analyze their local tables
would in fact be a global temporary table as well. I wonder if we could
bootstrap something similar for pg_class as well.

Incidentally, for what would imagine relfozenxid would be useful for these
tables anyways?

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com