Re: Regression caused by recent change to initdb?

Hi,

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:

postgres=# SELECT relname, relnamespace::regnamespace FROM pg_class
WHERE relnamespace != 'pg_catalog'::regnamespace
AND relnamespace != 'pg_toast'::regnamespace
AND relnamespace != 'information_schema'::regnamespace;
relname | relnamespace
----------------------+-----------------
pg_toast_11817 | pg_toast_temp_1
pg_toast_11817_index | pg_toast_temp_1
pg_toast_11822 | pg_toast_temp_1
pg_toast_11822_index | pg_toast_temp_1
pg_toast_11827 | pg_toast_temp_1
pg_toast_11827_index | pg_toast_temp_1
tmp_pg_description | pg_temp_1
tmp_pg_shdescription | pg_temp_1
tmp_pg_collation | pg_temp_1
(10 rows)

These seem to be leftovers of activities of initdb.c's setup_description()
and setup_collaction(). Interestingly, they disappear after performing the
following steps:

0. Stop the server
1. Connect to the database in --single mode, create a temp table, exit.
2. Log back into the database in normal mode and execute the same query.

The behavior seems to be as of commit c4a8812cf (Use just one
standalone-backend session for initdb's post-bootstrap steps). Is this a
regression?

Thanks,
Amit

On 2016/01/06 17:32, Amit Langote wrote:
> Hi,
>
> 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:
>
> postgres=# SELECT relname, relnamespace::regnamespace FROM pg_class
> WHERE relnamespace != 'pg_catalog'::regnamespace
> AND relnamespace != 'pg_toast'::regnamespace
> AND relnamespace != 'information_schema'::regnamespace;
> relname | relnamespace
> ----------------------+-----------------
> pg_toast_11817 | pg_toast_temp_1
> pg_toast_11817_index | pg_toast_temp_1
> pg_toast_11822 | pg_toast_temp_1
> pg_toast_11822_index | pg_toast_temp_1
> pg_toast_11827 | pg_toast_temp_1
> pg_toast_11827_index | pg_toast_temp_1
> tmp_pg_description | pg_temp_1
> tmp_pg_shdescription | pg_temp_1
> tmp_pg_collation | pg_temp_1
> (10 rows)
>
> 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.

Thanks,
Amit

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

regards, tom lane

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