Two tables for the price of one?

Started by Patrick Alandabout 25 years ago2 messagesgeneral
Jump to latest
#1Patrick Aland
paland@stetson.edu

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
------------------------------------------------------------

#2Peter Eisentraut
peter_e@gmx.net
In reply to: Patrick Aland (#1)
Re: Two tables for the price of one?

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/