On Monday 08 December 2003 01:54 pm, Charles Curley wrote: > 7.2.3. When I try to import the old dump as user postgres, I see the > following: > CREATE USER "root" WITH SYSID 0 NOCREATEDB NOCREATEUSER; [Sorry it took me so long to reply to this; I had to do some research on it (while working, too)....] This is no longer supported, which is what the error message is telling you. Trying this line at a psql prompt in 7.4, I find the same behavior. The only reason you didn't see this prior to 7.3 was that the use of CREATE USER versus a COPY of the SYSIDs was done for 7.3, but the problem existed long before that. > I didn't see anything useful at http://www.postgresql.org. This change happened a long time ago. Specifically, file user.c in src/backend/commands was changed on September 8, 2001 with this change. (Reference the web CVS URL:http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/backend/commands/user.c?rev=1.83&content-type=text/x-cvsweb-markup to see the code right after the change: to see the diff between that and the previous version see http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/backend/commands/user.c.diff?r1=1.82&r2=1.83 and for the complete history see http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/backend/commands/user.c ) There can be no database user ID less than 1; SYSID 1 is the user initdb creates and is by default the database superuser (the PostgreSQL SYSID's do not have to match the system UID for ident authentication to work; the names must match: case in point, the default UID for the user postgres is 26, but the SYSID for postgres is 1). The last release that the use of SYSID of 0 was supported would have been 7.1.x, and even then it wasn't supported. Try it out yourself on your 7.2.3 database and see if the raw CREATE USER command you posted will execute (you can see the error even if the SYSID of 0 already exists, since this test is earlier in the codepath than the duplicate test). It imported into 7.2 because the 7.1 pg_dump didn't create CREATE USER commands, it used COPY for this functionality, which bypasses this test. The only clue in the Release Notes for 7.2 that this happened is a line about the SuperUser ID being fixed at 1; the fact that a SYSID of 0 was unavailable is not mentioned. -- Lamar Owen RPM Maintainer, PostgreSQL Global Development Group Director of Information Technology Pisgah Astronomical Research Institute 1 PARI Drive Rosman, NC 28772 (828)862-5554 www.pari.edu
