Thread

1. pg_upgrade incorrectly equates pg_default and database tablespace

   Ants Aasma <ants@cybertec.at> — 2012-03-22T12:55:32Z

   Hi,
   
   while working on a support case I stumbled upon a bug in pg_upgrade.
   Upgrade fails with "No such file or directory" when a database is
   moved to a non-default tablespace and contains a table that is moved
   to pg_default. The cause seems to be that the following test
   incorrectly equates empty spclocation with database tablespace:
   
   tblspace = PQgetvalue(res, relnum, i_spclocation);
   /* if no table tablespace, use the database tablespace */
   if (strlen(tblspace) == 0)
       tblspace = dbinfo->db_tblspace;
   
   Patch to fix this is attached.
   
   Regards,
   Ants Aasma
   -- 
   Cybertec Schönig & Schönig GmbH
   Gröhrmühlgasse 26
   A-2700 Wiener Neustadt
   Web: http://www.postgresql-support.de
   

2. Re: pg_upgrade incorrectly equates pg_default and database tablespace

   Jeff Davis <pgsql@j-davis.com> — 2012-03-30T20:11:47Z

   On Thu, 2012-03-22 at 14:55 +0200, Ants Aasma wrote:
   > Hi,
   > 
   > while working on a support case I stumbled upon a bug in pg_upgrade.
   > Upgrade fails with "No such file or directory" when a database is
   > moved to a non-default tablespace and contains a table that is moved
   > to pg_default. The cause seems to be that the following test
   > incorrectly equates empty spclocation with database tablespace:
   > 
   > tblspace = PQgetvalue(res, relnum, i_spclocation);
   > /* if no table tablespace, use the database tablespace */
   > if (strlen(tblspace) == 0)
   >     tblspace = dbinfo->db_tblspace;
   > 
   > Patch to fix this is attached.
   
   I confirmed this bug upgrading 9.1 to master, and that this patch fixes
   it. Thank you for the report!
   
   Patch looks good to me as well, with one very minor nitpick: the added
   comment is missing an apostrophe.
   
   Bruce, can you take a look at this?
   
   Regards,
   	Jeff Davis
   
   
   

3. Re: pg_upgrade incorrectly equates pg_default and database tablespace

   Jeff Davis <pgsql@j-davis.com> — 2012-04-07T03:26:30Z

   On Fri, 2012-03-30 at 13:11 -0700, Jeff Davis wrote:
   > I confirmed this bug upgrading 9.1 to master, and that this patch fixes
   > it. Thank you for the report!
   > 
   > Patch looks good to me as well, with one very minor nitpick: the added
   > comment is missing an apostrophe.
   > 
   > Bruce, can you take a look at this?
   
   Adding this to the next commitfest, just so it doesn't get forgotten.
   
   Regards,
   	Jeff Davis
   
   
   

4. Re: pg_upgrade incorrectly equates pg_default and database tablespace

   Bruce Momjian <bruce@momjian.us> — 2012-04-11T00:05:22Z

   On Thu, Mar 22, 2012 at 02:55:32PM +0200, Ants Aasma wrote:
   > Hi,
   > 
   > while working on a support case I stumbled upon a bug in pg_upgrade.
   > Upgrade fails with "No such file or directory" when a database is
   > moved to a non-default tablespace and contains a table that is moved
   > to pg_default. The cause seems to be that the following test
   > incorrectly equates empty spclocation with database tablespace:
   > 
   > tblspace = PQgetvalue(res, relnum, i_spclocation);
   > /* if no table tablespace, use the database tablespace */
   > if (strlen(tblspace) == 0)
   >     tblspace = dbinfo->db_tblspace;
   > 
   > Patch to fix this is attached.
   
   Thank you for the fine bug report, and patch (and the bug confirmation
   from Jeff Davis).  Sorry for the delay in replying.
   
   You have certainly found a bug, and one that exists all the way back to
   pg_upgrade 9.0.  I was able to reproduce the bug with this SQL:
   
   	-- test database in different tablespace with table in cluster 
   	-- default tablespace
   	CREATE DATABASE tbltest TABLESPACE tt;
   	\connect tbltest
   	CREATE TABLE t1 (x int);
   	CREATE TABLE t2 (x int) TABLESPACE pg_default;
   
   It is exactly as you described --- the database is in a user-defined
   tablespace, but the table (t2) is in the cluster default location.  Not
   sure how no one else reported this failure before.
   
   The crux of the confusion is that pg_class.reltablespace == 0 means the
   database default tablespace, while a join to pg_tablespace that returns
   a zero-length string means it is in the cluster data directory.  The new
   code properly looks at reltablespace rather than testing the tablespace
   location, which was your fix as well.
   
   I have applied three different patches very similar to your helpful
   suggestion, attached.
   
   -- 
     Bruce Momjian  <bruce@momjian.us>        http://momjian.us
     EnterpriseDB                             http://enterprisedb.com
   
     + It's impossible for everything to be true. +