Subject: Re: [Patch] Temporary tables that do not bloat pg_catalog
 (a.k.a fast temp tables)
To: Tom Lane <tgl@sss.pgh.pa.us>, Pavel Stehule <pavel.stehule@gmail.com>
References: <20160729141552.4062e19b@fujitsu>
 <39b0f423-a4df-3564-3b4a-b1de6f9eea80@2ndquadrant.com>
 <CAFj8pRAv+vPxzTQMviCsYqQrfpYei5bTvX80zRUp1Q3xAbTWyg@mail.gmail.com>
 <1184.1469890030@sss.pgh.pa.us>
Cc: PostgreSQL Hackers <pgsql-hackers@postgresql.org>,
 Aleksander Alekseev <a.alekseev@postgrespro.ru>
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Message-ID: <5cdc3fde-d6a5-f0d2-b930-485bdcaecde5@2ndquadrant.com>
Date: Sun, 31 Jul 2016 18:02:44 +0200
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:46.0) Gecko/20100101
 Thunderbird/46.0a2
MIME-Version: 1.0
In-Reply-To: <1184.1469890030@sss.pgh.pa.us>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

On 07/30/2016 04:47 PM, 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".
>

Agreed - breaking internal features for temporary tables is not 
acceptable. I was thinking more about external code messing with 
catalogs, but on second thought we probably need to keep the records in 
pg_class anyway.

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

Yeah, and I think the patch tries to do that, although in a rather 
invasive / unprincipled way. But this will only work for the current 
behavior (i.e. mostly what SQL standard means by LOCAL). For GLOBAL 
temporary tables I think we need to keep physical catalog row, and only 
override the storage filename.

regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers