Table both does not and does exist! wth?

From: Melvin Davidson <melvin6925(at)yahoo(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Table both does not and does exist! wth?
Date: 2010-12-17 15:47:44
Message-ID: 377266.15457.qm@web121801.mail.ne1.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(at)builder10(dot)centos(dot)org) (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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jim Nasby 2010-12-17 15:51:52 Re: Searing array fields - or should I redesign?
Previous Message Mark Watson 2010-12-17 15:46:12 Copy From suggestion