Re: Patch for ALTER DATABASE WITH TABLESPACE

--On Mittwoch, November 05, 2008 01:10:22 +0100 Guillaume Lelarge
<guillaume(at)lelarge(dot)info> wrote:

> Tom Lane a écrit :
>> Guillaume Lelarge <guillaume(at)lelarge(dot)info> writes:
>>> Should I provide a complete new patch with Bernd's and Tom's changes?
>>
>> Please --- it's better if you integrate it since you know the patch
>> already.
>>
>
> I worked with Bernd's patch and replace the WITH syntax with the SET
> one. It works AFAICS, but I'm not sure this is the best way to do it.
> I'm no bison-guru.

I'm okay with this. However, i found some other issues:

* We really should error out when trying to copy into the same tablespace
the database already lives in. The code doesn't do any dangerous in this
case, but we should tell the DBA that he did something wrong and error out
if src_tblspcoid == dst_tblspoid is true. And i think we can avoid to call
database_file_update_needed() in this case then.

* The current implementation cannot merge a tablespace used by some
database objects already, for example:

CREATE DATABASE bernd;
CREATE DATABASE test;
CREATE TABLESPACE db1 LOCATION '/home/bernd/tmp/pgsql/temp1';
CREATE TABLESPACE db2 LOCATION '/home/bernd/tmp/pgsql/temp2';

\c test

CREATE TABLE foo(id INTEGER) TABLESPACE db2;

\c bernd
ALTER DATABASE test SET TABLESPACE db2;
ERROR: could not create directory "pg_tblspc/16394/16392": Die Datei
existiert bereits

The last message tells the file already exists, which is true since this
tablespace is already in use by an object of database test. I think we have
two choices:

1) Make the error more informative. This would mean the DBA has to copy
tables, indexes etc. manually in this case.

2) Try to merge the database objects into the tablespace.

Opinions?

--
Thanks

Bernd