Regarding the removal of ALTER INDEX OWNER commands from pg_dump,
indexes are now restored with the wrong ownership if the user doing
the restore is different than the user who owned the original index
(if this sounds familiar, I reported the same problem for 8.0.0rc4
in January). ALTER INDEX OWNER no longer works, and ALTER TABLE
OWNER won't change the index ownership if the table ownership doesn't
actually change (i.e., nothing happens if the new owner and the old
owner are the same). Should CREATE INDEX automatically set index
ownership to be the same as the table ownership? Or did I miss
past discussion about that?
Seems like this ought to be fixed before beta1 is announced so it
doesn't bite people who are trying 8.1 for the first time.
postgres=# CREATE ROLE test LOGIN PASSWORD 'test';
CREATE ROLE
postgres=# CREATE DATABASE test1;
CREATE DATABASE
postgres=# CREATE DATABASE test2;
CREATE DATABASE
postgres=# \c test1 test
Password for user test:
You are now connected to database "test1" as user "test".
test1=> CREATE TABLE foo (id serial PRIMARY KEY, val text);
NOTICE: CREATE TABLE will create implicit sequence "foo_id_seq" for serial
column "foo.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for
table "foo"
CREATE TABLE
test1=> CREATE INDEX foo_val_idx ON foo (val);
CREATE INDEX
test1=> \q
% pg_dump -U postgres test1 | psql -U postgres test2
SET
SET
SET
COMMENT
SET
SET
SET
CREATE TABLE
ALTER TABLE
setval
--------
1
(1 row)
ALTER TABLE
CREATE INDEX
REVOKE
REVOKE
GRANT
GRANT
% psql -q -U test test2
Password for user test:
test2=> \d
List of relations
Schema | Name | Type | Owner
--------+------------+----------+-------
public | foo | table | test
public | foo_id_seq | sequence | test
(2 rows)
test2=> \di
List of relations
Schema | Name | Type | Owner | Table
--------+-------------+-------+----------+-------
public | foo_pkey | index | postgres | foo
public | foo_val_idx | index | postgres | foo
(2 rows)
test2=> DROP INDEX foo_val_idx;
ERROR: must be owner of relation foo_val_idx
test2=> \c test2 postgres
Password for user postgres:
You are now connected to database "test2" as user "postgres".
test2=# ALTER INDEX foo_val_idx OWNER TO test;
WARNING: cannot change owner of index "foo_val_idx"
HINT: Change the ownership of the index's table, instead.
ALTER INDEX
test2=# ALTER TABLE foo OWNER TO test;
ALTER TABLE
test2=# \di
List of relations
Schema | Name | Type | Owner | Table
--------+-------------+-------+----------+-------
public | foo_pkey | index | postgres | foo
public | foo_val_idx | index | postgres | foo
(2 rows)
test2=# ALTER TABLE foo OWNER TO postgres;
ALTER TABLE
test2=# ALTER TABLE foo OWNER TO test;
ALTER TABLE
test2=# \di
List of relations
Schema | Name | Type | Owner | Table
--------+-------------+-------+-------+-------
public | foo_pkey | index | test | foo
public | foo_val_idx | index | test | foo
(2 rows)
--
Michael Fuhr
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
