| 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 (mockbuild@xxxxxxxxxxxxxxxxxxxx) (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 |
