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