> I think we have found a bug in postgresql 7.0.2. If I'm right then a fix
> for this should probably be added to 7.0.3 also. Anyway without further
> adieu:
>
> I have attached detail of my session at the end of this email, but the
> summary is as follows.
>
> If I run the following commands my pg_group file will be corrupted and up
> to 2Gig in size. (FYI - I am starting this after a fresh initdb, and the
> pg_group file
> starts at 0 bytes)
>
> create user foo1;
> create user foo2;
> create user foo3;
> create group test1;
> alter group test1 add user foo1,foo2,foo3;
> (pg_group is 8192 bytes)
> drop user foo1,foo2;
> (now my pg_group file is 24567 bytes)
> drop user foo3;
>
> (now my psql is hanging, control-c does nothing to kill the query. Also
> my pg_group file is now growing rapidly till it fills the disk or I kill
> -9 postmaster, which ever comes first. Not good!)
>
> I think the problem is with the drop user command (obviously). After the
> alter group command all three user ids are in the pg_group table. After I
> drop two of the users, both users are gone from the pg_user table, but the
> second user is still in pg_group. So we now have a reference to a user
> that does not exist. At this point I think the system is in trouble, and
> the last drop user command just seals the coffin. If I do the same script
> but drop the users one at a time, everything is fine. So apparently while
> the drop user command correctly drops all users from pg_user, it only
> checks pg_group for the first user in the list.
>
> We found this problem on one of our soon to be production database
> servers, fortunately prior to install. The only way to get the server
> back
> in to a fully functional state was to perform an initdb (I stopped
> postgre, then deleted the data directory then restarted using
> /etc/init.d/postgresql start which performs initdb)
>
> Anyway, any comments? Can anyone else repeat this? I hope this is easy to
> fix. I guess the quick fix is to disallow multiple users to be specified
> in the drop user command.
>
> Thanks much,
>
> Matt O'Connor
>
>
> p.s.. as promised here is some detail from my session.
> I have tested this on both RH7 with pg7.0.2 as supplied by RH, and with
> RH6.2 with 7.0.2 rpms from Lamar.
>
>
> Red Hat Linux release 7.0 (Guinness)
> select version();
> version
> -------------------------------------------------------------
> PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by gcc 2.96
> (1 row)
>
> ls -l ./data/pg_group
> -rw------- 1 postgres postgres 0 Oct 17 20:26 ./data/pg_group
> create user foo1;
> CREATE USER
> create user foo2;
> CREATE USER
> create user foo3;
> CREATE USER
> select * from pg_user;
> usename | usesysid | usecreatedb | usetrace | usesuper | usecatupd |
> passwd | valuntil
> ----------+----------+-------------+----------+----------+-----------+----
> ------+----------
> postgres | 26 | t | t | t | t |
> ******** |
> foo1 | 27 | f | f | f | f |
> ******** |
> foo2 | 28 | f | f | f | f |
> ******** |
> foo3 | 29 | f | f | f | f |
> ******** |
> (4 rows)
>
> create group test1;
> CREATE GROUP
> select * from pg_group;
> groname | grosysid | grolist
> ---------+----------+---------
> test1 | 1 |
> (1 row)
>
> alter group test1 add user foo1,foo2,foo3;
> ALTER GROUP
> select * from pg_user;
> usename | usesysid | usecreatedb | usetrace | usesuper | usecatupd |
> passwd | valuntil
> ----------+----------+-------------+----------+----------+-----------+----
> ------+----------
> postgres | 26 | t | t | t | t |
> ******** |
> foo1 | 27 | f | f | f | f |
> ******** |
> foo2 | 28 | f | f | f | f |
> ******** |
> foo3 | 29 | f | f | f | f |
> ******** |
> (4 rows)
>
> select * from pg_group;
> groname | grosysid | grolist
> ---------+----------+------------
> test1 | 1 | {27,28,29}
> (1 row)
>
> ls -l ./data/pg_group
> -rw------- 1 postgres postgres 8192 Oct 17 20:27 ./data/pg_group
> drop user foo1,foo2;
> DROP USER
> ls -l ./data/pg_group
> -rw------- 1 postgres postgres 24576 Oct 17 20:27 ./data/pg_group
> select * from pg_user;
> usename | usesysid | usecreatedb | usetrace | usesuper | usecatupd |
> passwd | valuntil
> ----------+----------+-------------+----------+----------+-----------+----
> ------+----------
> postgres | 26 | t | t | t | t |
> ******** |
> foo3 | 29 | f | f | f | f |
> ******** |
> (2 rows)
>
> select * from pg_group;
> groname | grosysid | grolist
> ---------+----------+---------
> test1 | 1 | {29,27}
> (1 row)
>
> drop user foo3;
> Cancel request sent
> Terminated
> bash-2.04$ ls -l ./data/pg_group
-rw------- 1 postgres postgres 438386688 Oct 17 20:27 ./data/pg_group
bash-2.04$
(as you can see, psql was not responding after the last drop user, I hit
control-c and is said Cancel request sent, but psql never returned to a
prompt, so I had to kill -9 the postmaster.