Re: error moving table to tablespace (8.0 beta win32 )

Hi!

just playing with tablespaces...

- moved a existing table to a new tablespace
- tried to move it back to default tablespace
(ALTER TABLE accounts SET TABLESPACE pg_default;)

Got the following error in logfile:

ERROR: could not create relation 1663/317186/317191: Permission denied

Any suggestions?

Best regards,
Christian

Christian Traber wrote:

> Hi!
>
> just playing with tablespaces...
>
> - moved a existing table to a new tablespace
> - tried to move it back to default tablespace
> (ALTER TABLE accounts SET TABLESPACE pg_default;)
>
> Got the following error in logfile:
>
> ERROR: could not create relation 1663/317186/317191: Permission denied
>
> Any suggestions?

are you able to create new tables in the default tablespace ? looks
like a permission issue did you initdb a fresh folder see if the
owner of $PGDATA/data/base is the user that running postmaster
(usually postgres) . It works fine for me though

Regds
mallah

test=# \d t_b
Table "public.t_b"
Column | Type | Modifiers
--------+---------+-----------
a | integer |
Tablespace: "space"

test=# ALTER TABLE t_b SET TABLESPACE pg_default;
ALTER TABLE
test=# SELECT version();
version
--------------------------------------------------------------------------------------------------------------
PostgreSQL 8.0.0beta1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC)
3.3.2 (Mandrake Linux 10.0 3.3.2-6mdk)
(1 row)

test=#

>
> Best regards,
> Christian
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>

--

regds
Mallah.

Rajesh Kumar Mallah
+---------------------------------------------------+
| Tradeindia.com (3,11,246) Registered Users |
| Indias' Leading B2B eMarketPlace |
| http://www.tradeindia.com/ |
+---------------------------------------------------+

Rajesh Kumar Mallah wrote:

> are you able to create new tables in the default tablespace ?

Yes, permissions are ok.

looks
> like a permission issue did you initdb a fresh folder see if the
> owner of $PGDATA/data/base is the user that running postmaster
> (usually postgres) . It works fine for me though

initdb was run by windows installer

>
> Regds
> mallah
>
> test=# \d t_b
> Table "public.t_b"
> Column | Type | Modifiers
> --------+---------+-----------
> a | integer |
> Tablespace: "space"
>
> test=# ALTER TABLE t_b SET TABLESPACE pg_default;
> ALTER TABLE
> test=# SELECT version();
> version
> --------------------------------------------------------------------------------------------------------------
> PostgreSQL 8.0.0beta1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC)
> 3.3.2 (Mandrake Linux 10.0 3.3.2-6mdk)
> (1 row)
>
> test=#
>

Maybe its only win32 version?

Here what i tried:

CREATE TABLE test (name varchar(100));
CREATE TABLE

CREATE TABLESPACE test LOCATION 'C:\\tmp\\ts_test';
CREATE TABLESPACE

\db
List of tablespaces
Name | Owner | Location
------------+----------+----------------
pg_default | postgres |
pg_global | postgres |
test | postgres | C:/tmp/ts_test
(3 rows)

ALTER TABLE test SET TABLESPACE test;
ALTER TABLE

\d+ test
Table "public.test"
Column | Type | Modifiers | Description
--------+------------------------+-----------+-------------
name | character varying(100) | |
Contains OIDs: yes
Tablespace: "test"

ALTER TABLE test SET TABLESPACE pg_default;
ERROR: could not create relation 1663/317186/1317654: Permission denied

select version();
version

----------------------------------------------------------------------------------------------------------
PostgreSQL 8.0.0beta1 on i686-pc-mingw32, compiled by GCC gcc.exe
(GCC) 3.2.3 (mingw special 20030504-1)
(1 row)

Thanks and regards
Christian

Christian Traber <christian(at)traber-net(dot)de> writes:
> Maybe its only win32 version?

Well, I can't reproduce it on Unix. It seems awfully odd though that
you can create tables in that tablespace to begin with but can't move
them there. I'm cc'ing this to the pgsql-hackers-win32 to see if anyone
there has a clue ...

regards, tom lane

> Here what i tried:

> CREATE TABLE test (name varchar(100));
> CREATE TABLE

> CREATE TABLESPACE test LOCATION 'C:\\tmp\\ts_test';
> CREATE TABLESPACE

> \db
> List of tablespaces
> Name | Owner | Location
> ------------+----------+----------------
> pg_default | postgres |
> pg_global | postgres |
> test | postgres | C:/tmp/ts_test
> (3 rows)

> ALTER TABLE test SET TABLESPACE test;
> ALTER TABLE

> \d+ test
> Table "public.test"
> Column | Type | Modifiers | Description
> --------+------------------------+-----------+-------------
> name | character varying(100) | |
> Contains OIDs: yes
> Tablespace: "test"

> ALTER TABLE test SET TABLESPACE pg_default;
> ERROR: could not create relation 1663/317186/1317654: Permission denied

> select version();
> version

> ----------------------------------------------------------------------------------------------------------
> PostgreSQL 8.0.0beta1 on i686-pc-mingw32, compiled by GCC gcc.exe
> (GCC) 3.2.3 (mingw special 20030504-1)
> (1 row)

Tom Lane wrote:
> Christian Traber <christian(at)traber-net(dot)de> writes:
>
>>Maybe its only win32 version?
>
>
> Well, I can't reproduce it on Unix. It seems awfully odd though that
> you can create tables in that tablespace to begin with but can't move
> them there. I'm cc'ing this to the pgsql-hackers-win32 to see if anyone
> there has a clue ...

I just reproduced this on win32. The file already exists, and is locked.
Apparently, in this case it's the very same backend that's locking the
file; closing the connection that was used to move the table into the
other tablespace will remove the file immediately.

Regards,
Andreas

Andreas Pflug <pgadmin(at)pse-consulting(dot)de> writes:
> I just reproduced this on win32. The file already exists, and is locked.

Oh, I'll bet it's the original copy of the table that hasn't been
removed yet. We may have an issue with SET TABLESPACE failing to close
the old file, so it can't be deleted yet on Windows ...

regards, tom lane

Tom Lane wrote:
> Andreas Pflug <pgadmin(at)pse-consulting(dot)de> writes:
>
>>I just reproduced this on win32. The file already exists, and is locked.
>
>
> Oh, I'll bet it's the original copy of the table that hasn't been
> removed yet.

You win.

> We may have an issue with SET TABLESPACE failing to close
> the old file, so it can't be deleted yet on Windows ...

Might not be the whole truth, the file *is* deleted when the backend is
shut down. Maybe the OS delays the delete if the file is still open; I'm
not familiar enough with that part of code to verify it.

Regards,
Andreas

Andreas Pflug <pgadmin(at)pse-consulting(dot)de> writes:
> Tom Lane wrote:
>> Oh, I'll bet it's the original copy of the table that hasn't been
>> removed yet.

> You win.

I looked at the ALTER TABLE SET TABLESPACE code and saw that it was
indeed failing to close the original table. I've committed a fix.
Can someone check that this particular failure mode is gone on Windows
with CVS tip?

regards, tom lane

Tom Lane wrote:
> Andreas Pflug <pgadmin(at)pse-consulting(dot)de> writes:
>
>>Tom Lane wrote:
>>
>>>Oh, I'll bet it's the original copy of the table that hasn't been
>>>removed yet.
>
>
>>You win.
>
>
> I looked at the ALTER TABLE SET TABLESPACE code and saw that it was
> indeed failing to close the original table. I've committed a fix.
> Can someone check that this particular failure mode is gone on Windows
> with CVS tip?

Yes, it's gone. The table file now vanishes immediately from the
previous tablespace dir.

Regards,
Andreas