Re: Problem with temporary tables

On Wed, Jun 30, 2010 at 9:51 AM, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> wrote:
> On Wednesday 30 June 2010 6:41:18 am Andrea Lombardoni wrote:
>> > You need to use EXECUTE for the INSERT statement as well per error:
>> >
>> > CONTEXT:  SQL statement "INSERT INTO idmap (oldid, type, newid) VALUES(1,
>> >  1, 1)" PL/pgSQL function "test" line 16 at SQL statement
>>
>> Thanks, this works and solves my problem.
>>
>> Still, I find this behaviour to be rather quirky.
>>
>> Ideally the generated query plan should notice such cases and either
>> report an error or use the version of the temporary table currently
>> 'live'.
>
> In the temporary table case it does for versions of 8.3+. From release notes for
> 8.3:
>
> "
> Automatically re-plan cached queries when table definitions change or statistics
> are updated (Tom)
>
> Previously PL/PgSQL functions that referenced temporary tables would fail if the
> temporary table was dropped and recreated between function invocations, unless
> EXECUTE was used. This improvement fixes that problem and many related issues.

Even in 8.3+ it's still better not to drop tables between function
calls if it can be reasonably done. temp tables are a headache
generally and I try to use them as little as possible.

for cases where 'on commit drop' (transaction local data) might be
used in the cases where arrays are not appropriate for local function
storage, I use a persistent table. Since I generally like being
pedantic, I'll give you a workaround you didn't ask for. :-)

for cases where 'on commit drop' (transaction local data) might be
used in the cases where arrays are not appropriate for local function
storage, I use a persistent table.

create table i_dislike_on_commit_drop
(
tx_time timestamptz default now(),
pid int default pg_backend_pid(),

col1 text,
col2 text, [etc]
);
create index idocd_idx on i_dislike_on_commit_drop(tx_time, pid);

for scratch inserts, you just:
insert into i_dislike_on_commit_drop(col1, col2) values (...)

for select, updates and deletes should you need them, be sure to
WHERE...AND (tx_time, pid) = (now(), pg_backend_pid());

just make sure to delete the records once in a while (you can freely
delete them all...in process records won't get hit). you might be
tempted to use a view to wrap the table and filter out records, I
don't think that's a good idea (requires righting nasty insert/update
rules).

merlin