Re: Bug in CREATE/DROP TABLESPACE command

Postgres 8.2.3 on Windows Server 2003. I looked into this more, and I
narrowed the bug down. It only happens if you issue a DROP TABLESPACE
command and a CREATE TABLESPACE command in one batch, where the CREATE
TABLESPACE command points to an invalid location. I didn't realize how
obscure an edge-case this was.

The code below will demonstrate the problem, with running commentary:

/*
To duplicate the tablespace bug on Postgres 8.2.3 on Windows 2003 Server:

1) Create a directory c:\postgresql\MyDatabase and set the postgresql
user so it has full control of the directory..
Alternatively, change the path to some other path that you prefer
2) Run the first CREATE TABLESPACE command in it's own batch (I did this
by highlighting it in pgadmin3)
3) Run the second two commands in one batch. That is, the drop and the
create at once.
4) Run the drop tablespace command. Alternatively, you can delete it
manually via pgadmin3.
*/

-- Create a tablespace in a valid location
CREATE TABLESPACE bad_tablespace LOCATION E'C:\\postgresql\\MyDatabase';
-- Result:
-- Query returned successfully with no result in 20 ms.

-- Drop the tablespace and re-create in in an invalid location
-- This only causes the bug if both these commands are run in one batch
DROP TABLESPACE IF EXISTS bad_tablespace;
CREATE TABLESPACE bad_tablespace LOCATION E'Z:\\postgresql\\MyDatabase';
-- Result:
-- ERROR: could not set permissions on directory
-- "Z:/postgresql/MyDatabase": No such file or directory
-- SQL state: 58P01

-- Now try to drop it again, and you get an error
DROP TABLESPACE IF EXISTS bad_tablespace;
-- Result:
-- ERROR: could not open directory "pg_tblspc/16827": No such file or
-- directory
-- SQL state: 58P01

It looks to me like postgres creates a hard link with a random number
that points to the physical location of the tablespace. Once you get
stuck like this, you can work around the problem by creating a
C:\Program Files\PostgreSQL\8.2\data\pg_tblspc\##### directory. I
assume this problem is reproducible on other operating systems the same
way. But maybe it is some problem specific to symbolic links on
Windows? Looks more like an internal state issue though.

Bruce Momjian wrote:
> What version of PostgreSQL is this? Please provide the SQL commands
> that cause this problem, with error output.
>
> ---------------------------------------------------------------------------
>
> William Garrison wrote:
>> On Windows Server 2003, if you create a tablespace to a location that
>> doesn't exist, then try to remove that tablespace, you get an error that
>> pg_tblspc/##### does not exist. It appears that postgres created the
>> tablespace internally, but not the folder. When you try to drop the
>> tablespace, the folder doesn't exist and it reports an error.
>>
>> This sounds like two interacting bugs:
>> 1) The tablespace should not have been created because the symlink could
>> not be created.
>> 2) It should be possible to remove a tablespace even if the symlink has
>> already been deleted manually.
>>
>> The workaround is to create a pg_tblsc/##### directory then do the drop.
>>
>> Where do I submit this bug?
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 4: Have you searched our list archives?
>>
>> http://archives.postgresql.org/
>