Skip site navigation (1) Skip section navigation (2)

one more index for pg_tablespace?

From: Hans-Jürgen Schönig <postgres(at)cybertec(dot)at>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: one more index for pg_tablespace?
Date: 2010-02-15 21:50:29
Message-ID: (view raw or whole thread)
Lists: pgsql-hackers
hello ...

i have come an interesting corner case this morning and i am not sure  
if it is worth treating this as a bug or as just "bad luck".
imagine creating a directory along with a tablespace ...
hans-jurgen-schonigs-macbook:html hs$ mkdir /tmp/x
hans-jurgen-schonigs-macbook:html hs$ psql test
psql (8.4.1)
Type "help" for help.

test=# create tablespace x location '/tmp/x';
test=# create tablespace x2 location '/tmp/x';
ERROR:  directory "/tmp/x" is not empty
test=# \q

postgres errors our here correctly because it sees that the tablespace  
is not empty. this is perfect ...

hans-jurgen-schonigs-macbook:html hs$ cd /tmp/x
hans-jurgen-schonigs-macbook:x hs$ ls
hans-jurgen-schonigs-macbook:x hs$ rm PG_VERSION

now, after killing the PG_VERSION file, i am able to create a  
tablespace pointing to the same directoy.
this should be prevented by one more unique index on the directory.

hans-jurgen-schonigs-macbook:x hs$ psql test
psql (8.4.1)
Type "help" for help.

test=# create tablespace x2 location '/tmp/x';
test=# \d pg_tablespace
   Table "pg_catalog.pg_tablespace"
    Column    |   Type    | Modifiers
  spcname     | name      | not null
  spcowner    | oid       | not null
  spclocation | text      |
  spcacl      | aclitem[] |
     "pg_tablespace_oid_index" UNIQUE, btree (oid), tablespace  
     "pg_tablespace_spcname_index" UNIQUE, btree (spcname), tablespace  
Tablespace: "pg_global"

test=# SELECT * FROM pg_tablespace;
   spcname   | spcowner |  spclocation  | spcacl
  pg_default |       10 |               |
  pg_global  |       10 |               |
  x          |       10 | /tmp/x        |
  x2         |       10 | /tmp/x        |
(6 rows)

now, killing PG_VERSION manually is not what people do but what can  
happen is that, say, an NFS connection is gone or that somehow the  
directory is empty because of some other network filesystem doing some  
funny thing. it is quite realistic that this can happen.

how about one more unique index here?
pg_tablespace does not look too good with a duplicate entry ...

	many thanks,


Cybertec Schönig & Schönig GmbH
Reyergasse 9 / 2
A-2700 Wiener Neustadt

pgsql-hackers by date

Next:From: Tim BunceDate: 2010-02-15 22:42:17
Subject: Re: PostgreSQL::PLPerl::Call - Simple interface for calling SQL functions from PostgreSQL PL/Perl
Previous:From: Jeff DavisDate: 2010-02-15 21:36:55
Subject: Re: Listen / Notify - what to do when the queue is full

Privacy Policy | About PostgreSQL
Copyright © 1996-2015 The PostgreSQL Global Development Group