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: EB757FDF-9952-42F3-9D76-7A9B3CB7F1E5@cybertec.at
Views: Raw Message | Whole Thread | Download mbox | Resend email
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';
CREATE TABLESPACE
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
PG_VERSION
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';
CREATE TABLESPACE
test=# \d pg_tablespace
Table "pg_catalog.pg_tablespace"
Column | Type | Modifiers
-------------+-----------+-----------
spcname | name | not null
spcowner | oid | not null
spclocation | text |
spcacl | aclitem[] |
Indexes:
"pg_tablespace_oid_index" UNIQUE, btree (oid), tablespace
"pg_global"
"pg_tablespace_spcname_index" UNIQUE, btree (spcname), tablespace
"pg_global"
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,

hans

--
Cybertec Schönig & Schönig GmbH
Reyergasse 9 / 2
A-2700 Wiener Neustadt
Web: www.postgresql-support.de

Browse pgsql-hackers by date

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