Re: [Patch] Temporary tables that do not bloat pg_catalog (a.k.a fast temp tables)

On 7/30/16 10:47 AM, Tom Lane wrote:
> Pavel Stehule <pavel.stehule@gmail.com> writes:
>> But there are some patterns used with work with temp tables,that should not
>> working, and we would to decide if we prepare workaround or not.
> 
>> -- problematic pattern (old code)
>> IF NOT EXISTS(SELECT * FROM pg_class WHERE ....) THEN
>>   CREATE TEMP TABLE xxx()
>> ELSE
>>   TRUNCATE TABLE xxx;
>> END IF;
> 
>> -- modern patter (new code)
>> BEGIN
>>   TRUNCATE TABLE xxx;
>> EXCEPTION WHEN ..... THEN
>>   CREATE TEMP TABLE(...)
>> END;
> 
> If the former stops working, that's a sufficient reason to reject the
> patch: it hasn't been thought through carefully enough.  The key reason
> why I don't think that's negotiable is that if there aren't (apparently)
> catalog entries corresponding to the temp tables, that will almost
> certainly break many things in the backend and third-party extensions,
> not only user code patterns like this one.  We'd constantly be fielding
> bug reports that "feature X doesn't work with temp tables anymore".
> 
> In short, I think that the way to make something like this work is to
> figure out how to have "virtual" catalog rows describing a temp table.
> Or maybe to partition the catalogs so that vacuuming away temp-table
> rows is easier/cheaper than today.

In addition the latter pattern burns an xid which can be a problem for
high-volume databases.

How about CREATE TEMP TABLE IF NOT EXISTS...?

-- 
-David
david@pgmasters.net