Hi,
I just found a weird behaviour with this statement. Here is a complete
log of my session with a 8.3(.4) server:
[EMAIL PROTECTED] mkdir /home/guillaume/ts1
[EMAIL PROTECTED] createdb db1
[EMAIL PROTECTED] LANG=C psql db1
Welcome to psql 8.3.4, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
db1=# create table t1 (id integer);
CREATE TABLE
db1=# create tablespace ts1 location '/home/guillaume/ts1';
CREATE TABLESPACE
db1=# insert into t1 values (1);
INSERT 0 1
db1=# select pg_database.oid as db_folder, relfilenode as table_filename
db1-# from pg_database, pg_class where datname='db1' and relname='t1';
db_folder | table_filename
-----------+----------------
74472 | 74475
(1 row)
db1=# \! ls -l /opt/postgresql-8.3/data/base/74472/74475
-rw------- 1 guillaume guillaume 8192 Oct 6 10:59
/opt/postgresql-8.3/data/base/74472/74475
Till now, everything seems good. I have one 8k file for my table t1.
db1=# alter table t1 set tablespace ts1;
ALTER TABLE
/opt/postgresql-8.3/data/base/74472/74475
db1=# \! ls -l /home/guillaume/ts1/74472/74475
-rw------- 1 guillaume guillaume 8192 Oct 6 11:00
/home/guillaume/ts1/74472/74475
My table moved to my own tablespace.
db1=# \! ls -l /opt/postgresql-8.3/data/base/74472/74475
-rw------- 1 guillaume guillaume 0 Oct 6 11:00
This seems weird. I expected to have no file 74475 in the pg_default
tablespace after the ALTER TABLE.
Of course, now, I can't get my table back on the previous tablespace.
db1=# alter table t1 set tablespace pg_default;
ERROR: could not create relation 1663/74472/74475: File exists
db1=# alter table t1 set tablespace pg_default;
ERROR: could not create relation 1663/74472/74475: File exists
I finally discovered that a CHECKPOINT resolves my issue.
db1=# checkpoint;
CHECKPOINT
And I can move my table back to the previous tablespace.
db1=# alter table t1 set tablespace pg_default;
ALTER TABLE
db1=# \! ls -l /opt/postgresql-8.3/data/base/74472/74475
-rw------- 1 guillaume guillaume 8192 Oct 6 11:01
/opt/postgresql-8.3/data/base/74472/74475
db1=# \! ls -l /home/guillaume/ts1/74472/74475
-rw------- 1 guillaume guillaume 0 Oct 6 11:01
/home/guillaume/ts1/74472/74475
This behaviour happens on the REL8_3_STABLE and HEAD branches. In
REL8_2_STABLE, it works as I expected it to work :
[EMAIL PROTECTED] mkdir /home/guillaume/ts2
[EMAIL PROTECTED] createdb db2
CREATE DATABASE
[EMAIL PROTECTED] psql db2
Bienvenue dans psql 8.2.10, l'interface interactive de PostgreSQL.
Saisissez:
\copyright pour les termes de distribution
\h pour l'aide-mémoire des commandes SQL
\? pour l'aide-mémoire des commandes psql
\g ou point-virgule en fin d'instruction pour exécuter la requête
\q pour quitter
db2=# create table t2 (id integer);
CREATE TABLE
db2=# create tablespace ts2 location '/home/guillaume/ts2';
CREATE TABLESPACE
db2=# insert into t2 values (1);
INSERT 0 1
db2=# select pg_database.oid as db_folder, relfilenode as table_filename
from pg_database, pg_class where datname='db2' and relname='t2';
db_folder | table_filename
-----------+----------------
47944 | 47945
(1 ligne)
db2=# \! ls -l /opt/postgresql-8.2/data/base/47944/47945
-rw------- 1 guillaume guillaume 8192 2008-10-06 11:12
/opt/postgresql-8.2/data/base/47944/47945
db2=# alter table t2 set tablespace ts2;
ALTER TABLE
db2=# \! ls -l /opt/postgresql-8.2/data/base/47944/47945
ls: cannot access /opt/postgresql-8.2/data/base/47944/47945: No such
file or directory
db2=# \! ls -l /home/guillaume/ts2/47944/47945
-rw------- 1 guillaume guillaume 8192 2008-10-06 11:13
/home/guillaume/ts2/47944/47945
db2=# alter table t2 set tablespace pg_default;
ALTER TABLE
db2=# \! ls -l /home/guillaume/ts2/47944/47945
ls: cannot access /home/guillaume/ts2/47944/47945: No such file or directory
db2=# \! ls -l /opt/postgresql-8.2/data/base/47944/47945
-rw------- 1 guillaume guillaume 8192 2008-10-06 11:13
/opt/postgresql-8.2/data/base/47944/47945
I don't need a checkpoint to be able to move my table back.
It doesn't seem a big issue because checkpoints are issued frequently
but it deserves to get fixed. I looked a bit at the source code. The old
file gets in a queue of to-be-removed files (see smgrscheduleunlink()
function in storage/smgr/smgr.c). But I failed to see where it really
gets deleted. I would welcome any pointer.
Regards.
--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
