Re: Regression caused by recent change to initdb?

On 2016/01/06 23:50, Tom Lane wrote:
> Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp> writes:
>> On 2016/01/06 17:32, Amit Langote wrote:
>>> I stumbled upon a possibly strange behavior which may be related to recent
>>> initdb changes. For a freshly initdb'd cluster, the following looks fishy:
>>> ...
>>> These seem to be leftovers of activities of initdb.c's setup_description()
>>> and setup_collaction().
>
>> I noticed these leftovers are not present in template1.
>
> Ah, right: they get deleted from template1 correctly when the
> initdb-driven session shuts down. But because of the merger into a single
> session, they're still there at the instant that we clone template1 into
> template0 and postgres databases, and there is nothing to remove them from
> there.
>
> The minimum-change way to deal with it would be to explicitly DROP those
> tables when we're done with them.
>
> A possibly slightly less fragile answer is to run two sessions, the
> second of which *only* processes the DB copying steps.
>
> Neither of these answers seems all that clean to me...

Thanks for fixing this in initdb.

So in general, we have no way to get rid of the copies in a new database
of temp tables in template1 when the new database is created in the same
session as the one connected to template1. For example:

template1=# CREATE TEMP TABLE foo(a int);
CREATE TABLE

template1=# INSERT INTO foo VALUES (1);
INSERT 0 1

template1=# CREATE DATABASE test;
CREATE DATABASE

template1=# \c test
You are now connected to database "test" as user "amit".

test=# SELECT relname, relnamespace::regnamespace FROM pg_class
WHERE relnamespace != 'pg_catalog'::regnamespace
AND relnamespace != 'pg_toast'::regnamespace
AND relnamespace != 'information_schema'::regnamespace;
relname | relnamespace
---------+--------------
foo | pg_temp_2

-- of course, there is no way to open it here (different backend id)
test=# SELECT * FROM foo;
ERROR: relation "foo" does not exist
LINE 1: SELECT * FROM foo;

-- nor does it prevent from creating a new temp table foo.
test=# CREATE TEMP TABLE foo(a int);
CREATE TABLE

test=# SELECT relname, relnamespace::regnamespace FROM pg_class WHERE
relnamespace != 'pg_catalog'::regnamespace AND relnamespace !=
'pg_toast'::regnamespace AND relnamespace !=
'information_schema'::regnamespace;
relname | relnamespace
---------+--------------
foo | pg_temp_2
foo | pg_temp_3
(2 rows)

Maybe, we need not worry too much about this.

Thanks,
Amit