Guillaume Lelarge wrote:
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.
Hmm. We force a checkpoint in dropdb() for similar reasons.
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.
In mdpostckpt().
The trivial fix is to just force a checkpoint in ALTER TABLE SET
TABLESPACE. Can we do better than that? Perhaps only force a checkpoint
when we find that the file already exists.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
