[PATCH] PREPARE TRANSACTION unexpected behavior with TEMP TABLE

Hi,

While working on one of the issue, I have noticed below unexpected behavior
with "PREPARE TRANSACTION".

We are getting this unexpected behavior with PREPARE TRANSACTION when it is
mixed with Temporary Objects. Please consider the below setup and SQL block.

set max_prepared_transactions to 1 (or any non zero value), this is to
enable the “prepare transaction”.

Now please try to run the below set of statements.
[BLOCK-1:]
postgres=# create temp table fullname (first text, last text);
CREATE TABLE
postgres=# BEGIN;
BEGIN
postgres=*# create function longname(fullname) returns text language sql
postgres-*# as $$select $1.first || ' ' || $1.last$$;
CREATE FUNCTION
postgres=*# prepare transaction 'mytran';
ERROR: cannot PREPARE a transaction that has operated on temporary objects

Above error is expected.

The problem is if we again try to create the same function in the “PREPARE
TRANSACTION” as below.

[BLOCK-2:]
postgres=# BEGIN;
BEGIN
postgres=*# create function longname(fullname) returns text language sql
as $$select $1.first || ' ' || $1.last$$;
CREATE FUNCTION
postgres=*# PREPARE transaction 'mytran';
PREPARE TRANSACTION

Now, this time we succeed and not getting the above error (”ERROR: cannot
PREPARE a transaction that has operated on temporary objects), like the way
we were getting with BLOCK-1

This is happening because we set MyXactFlags in relation_open function
call, and here relation_open is getting called from load_typcache_tupdesc,
but in the second run of “create function…” in the above #2 block will not
call load_typcache_tupdesc because of the below condition(typentry->tupDesc
== NULL) in lookup_type_cache().

/*
* If it's a composite type (row type), get tupdesc if requested
*/
if ((flags & TYPECACHE_TUPDESC) &&
typentry->tupDesc == NULL &&
typentry->typtype == TYPTYPE_COMPOSITE)
{
load_typcache_tupdesc(typentry);
}

We set typentry->tupDesc to non NULL(and populates it with proper tuple
descriptor in the cache) value during our first call to “create function…”
in BLOCK-1.
We have logic in file xact.c::PrepareTransaction() to simply error out if
we have accessed any temporary object in the current transaction but
because of the above-described issue of not setting
XACT_FLAGS_ACCESSEDTEMPNAMESPACE in MyXactFlags second run of “create
function..” Works and PREPARE TRANSACTION succeeds(but it should fail).

Please find attached the proposed patch to FIX this issue.

Thoughts?

Thanks,
Himanshu Upadhyaya
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company