Re: Weird behaviour with ALTER TABLE ... SET TABLESPACE ... statement

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