Re: [Proposal] Fully WAL logged CREATE DATABASE - No Checkpoints

On Fri, Mar 11, 2022 at 1:10 PM Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> I don't think you've adequately considered temporary relations here.
> It seems to be that ReadBufferWithoutRelcache() could not be safe on a
> temprel, because we'd need a BackendId to access the underlying
> storage. So I think that ReadBufferWithoutRelcache can only accept
> unlogged or permanent, and maybe the argument ought to be a Boolean
> instead of a relpersistence value. I thought that this problem might
> be only cosmetic, but I checked the code that actually does the copy,
> and there's no filter there on relpersistence either. And I think
> there should be.

I hit "send" too quickly there:

rhaas=# create database fudge;
CREATE DATABASE
rhaas=# \c fudge
You are now connected to database "fudge" as user "rhaas".
fudge=# create temp table q ();
CREATE TABLE
fudge=# ^Z
[2]+ Stopped psql
[rhaas Downloads]$ pg_ctl stop -mi
waiting for server to shut down.... done
server stopped
[rhaas Downloads]$ %%
psql
\c
You are now connected to database "fudge" as user "rhaas".
fudge=# select * from pg_class where relpersistence='t';
oid | relname | relnamespace | reltype | reloftype | relowner |
relam | relfilenode | reltablespace | relpages | reltuples |
relallvisible | reltoastrelid | relhasindex | relisshared |
relpersistence | relkind | relnatts | relchecks | relhasrules |
relhastriggers | relhassubclass | relrowsecurity | relforcerowsecurity
| relispopulated | relreplident | relispartition | relrewrite |
relfrozenxid | relminmxid | relacl | reloptions | relpartbound
-------+---------+--------------+---------+-----------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+----------------+---------+----------+-----------+-------------+----------------+----------------+----------------+---------------------+----------------+--------------+----------------+------------+--------------+------------+--------+------------+--------------
16388 | q | 16386 | 16390 | 0 | 10 |
2 | 16388 | 0 | 0 | -1 | 0
| 0 | f | f | t | r
| 0 | 0 | f | f | f
| f | f | t | d
| f | 0 | 721 | 1 | |
|
(1 row)

fudge=# \c rhaas
You are now connected to database "rhaas" as user "rhaas".
rhaas=# alter database fudge is_template true;
ALTER DATABASE
rhaas=# create database cookies template fudge;
CREATE DATABASE
rhaas=# \c cookies
You are now connected to database "cookies" as user "rhaas".
cookies=# select count(*) from pg_class where relpersistence='t';
count
-------
1
(1 row)

You have to be quick, because autovacuum will drop the orphaned temp
table when it notices it, but it is possible.

--
Robert Haas
EDB: http://www.enterprisedb.com