PostgreSQL 8.3.11 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2
20080704 (Red Hat 4.1.2-46)
Linux version 2.6.18-194.26.1.el5 ([email protected]) (gcc version
4.1.2 20080704 (Red Hat 4.1.2-48)) #1 SMP Tue Nov 9 12:54:40 EST 2010
How is this possible? I've been working as a PostgreSQL DBA for 5 years, and
frankly I'm baffled.
I had previosly created a TEMP table in a session, but later decided to
make it a permanent table. However, when I attempted to do so, I came across a
very weird problem. PostgreSQL 1st denies that the table exists, because I do a
DROP TABLE IF EXISTS.
But when I do a CREATE TABLE, it says it is already there!
At first I thought I might have to do with shared_buffer memory. But after
shutting down both the client and server, the problem persists. In fact, I have
even dropped and reloaded the database, and it still occurs.
Am I missing something obvious?
Or does PostgreSQL have some undocumented, hidden catalog I am not aware of
where it tracks TEMP tables?
Below is an output from my session showing the problem.
enf=# DROP TABLE IF EXISTS xtmp_changed_ids;
NOTICE: table "xtmp_changed_ids" does not exist, skipping
DROP TABLE
enf=# CREATE TABLE xtmp_changed_ids
(
seq_all SERIAL NOT NULL,
new_id VARCHAR(200),
id VARCHAR(200),
pin VARCHAR(200),
pc VARCHAR(200),
site_id INTEGER,
status INTEGER,
csn INTEGER,
raw_seconds INTEGER,
lastname VARCHAR(200),
firstname VARCHAR(200),
CONSTRAINT xtmp_changed_ids PRIMARY KEY (seq_all)
) WITH (OIDS = FALSE);
NOTICE: CREATE TABLE will create implicit sequence
"xtmp_changed_ids_seq_all_seq" for serial column "xtmp_changed_ids.seq_all"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"xtmp_changed_ids" for table "xtmp_changed_ids"
ERROR: relation "xtmp_changed_ids" already exists
enf=#
enf=# SELECT * FROM pg_class WHERE relname = 'xtmp_changed_ids';
relname | relnamespace | reltype | relowner | relam | relfilenode |
reltablespace | relpages | reltuples | reltoastrelid | reltoastidxid |
relhasindex | relisshared | relkind | relnatts | relchecks | reltriggers |
relukeys | relfkeys | relrefs | relhasoids | relhaspkey | relhasrules |
relhassubclass | relfrozenxid | relacl | reloptions
---------+--------------+---------+----------+-------+-------------+---------------+----------+-----------+---------------+---------------+-------------+-------------+---------+----------+-----------+-------------+----------+----------+---------+------------+------------+-------------+----------------+--------------+--------+------------
(0 rows)
Melvin Davidson
