Thread: Two tables for the price of one?

I am seeing something kinda weird when I am creating tables (or viewing
existing ones):

<BEGIN SESSION>
bash$ createdb test
CREATE DATABASE
bash$ psql test
Welcome to psql, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

test=# \dt
No relations found.
test=# CREATE TABLE mytable (
test(#   id     INT,
test(#   stuff  VARCHAR(10)
test(# );
CREATE
test=# \dt
     List of relations
  Name   | Type  |  Owner
---------+-------+----------
 mytable | table | gmguest
 mytable | table | postgres
(2 rows)

test=#
</END SESSION>

As you can see when I create a table it appears it is either creating it
twice (not likely) or thinks that there are two instances of it. The
gmguest user is a guest user for another database however any database I
look at has another table for each table with this gmguest user as the
owner. I found this after seeing duplicate table names while trying to access a
database via odbc and MSAccess.

Anyone seen this before? I imagine somewhere I screwed up a system table
or something (don't remember doing it). I may just recompile and dump
and resoter the tables from scratch but I'd like to avoid it if
possible.

Thanks.



--
------------------------------------------------------------
 Patrick Aland                          paland@stetson.edu
 Network Administrator                  Voice: 904.822.7217
 Stetson University                     Fax: 904.822.7367
------------------------------------------------------------

Patrick Aland writes:

> test=# \dt
> No relations found.
> test=# CREATE TABLE mytable (
> test(#   id     INT,
> test(#   stuff  VARCHAR(10)
> test(# );
> CREATE
> test=# \dt
>      List of relations
>   Name   | Type  |  Owner
> ---------+-------+----------
>  mytable | table | gmguest
>  mytable | table | postgres
> (2 rows)

Both of these users probably have the same user id. See pg_shadow.usesysid
column.  If this installation was ever subject of a backup/restore, then
this might explain things.  If there are indeed duplicate user ids,
dropping and recreating gmguest should work.

--
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/