Thread: Bug in CREATE/DROP TABLESPACE command
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?
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/ -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
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/
>
William Garrison <postgres@mobydisk.com> writes:
> -- 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
What do you mean by "one batch" exactly? Both CREATE and DROP TABLESPACE
refuse to run in a transaction block, so I'm confused about this.
regards, tom lane
Not a transaction block. A batch of commands submitted to the server in a single call. In MSSQL land, I call that a batch. I don't know the PostgreSql term. If you are using the pgadmin3 GUI, then I mean pressing F5 once is a single batch. Pressing it twice is two batches. The following will submit this as one batch, and will NOT reproduce the problem: 1) Open pgadmin3. Open the query tool. Paste in the following. CREATE TABLESPACE bad_tablespace LOCATION E'C:\\postgresql\\MyDatabase'; DROP TABLESPACE IF EXISTS bad_tablespace; CREATE TABLESPACE bad_tablespace LOCATION E'Z:\\postgresql\\MyDatabase'; DROP TABLESPACE IF EXISTS bad_tablespace; 2) Press F5 However, the following submits it in 3 batches, and will reproduce the problem: 1) Open pgadmin3. Open the query tool. Paste in the following. CREATE TABLESPACE bad_tablespace LOCATION E'C:\\postgresql\\MyDatabase'; 2) Press F5 3) Delete the text, and replace it with the following: DROP TABLESPACE IF EXISTS bad_tablespace; CREATE TABLESPACE bad_tablespace LOCATION E'Z:\\postgresql\\MyDatabase'; 4) Press F5 5) Delete the text, and replace it with the following: DROP TABLESPACE IF EXISTS bad_tablespace; 6) Press F5 Strangely, I am unable to duplicate the problem with psql. I thought it would submit a single batch if I didn't press enter between each command, but it doesn't seem to work that way. If there is a better term please let me know. Tom Lane wrote: > William Garrison <postgres@mobydisk.com> writes: >> -- 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 > > What do you mean by "one batch" exactly? Both CREATE and DROP TABLESPACE > refuse to run in a transaction block, so I'm confused about this. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq >
William Garrison <postgres@mobydisk.com> writes:
> Tom Lane wrote:
>> What do you mean by "one batch" exactly? Both CREATE and DROP TABLESPACE
>> refuse to run in a transaction block, so I'm confused about this.
> Not a transaction block. A batch of commands submitted to the server in
> a single call. In MSSQL land, I call that a batch. I don't know the
> PostgreSql term. If you are using the pgadmin3 GUI, then I mean pressing
> F5 once is a single batch. Pressing it twice is two batches.
Oh, it's a single simple-Query message. You could reproduce the problem
with psql if you put multiple commands into a "-c" command line switch.
This is a basic oversight in PreventTransactionChain: it doesn't reject
the case where the command is submitted as part of a multi-query string
in a single Query message.
This is relatively easy to fix in CVS HEAD --- we can just teach
exec_simple_query to pass isTopLevel = true only when the querystring
contains a single command, or maybe better only for the last command
of a querystring. I don't see any very practical way to fix it in
older releases though; at least not anything I'd want to backpatch
when it can't be tested first in HEAD. Anyone have an idea about a
reasonable back-branch fix?
regards, tom lane