Table both does not and does exist! wth?

On Friday 17 December 2010 7:47:44 am Melvin Davidson wrote:

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@builder10.centos.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?

Have you tried a REINDEX on pg_class as superuser?

--
Adrian Klaver
adrian.klaver@gmail.com

Have you tried a REINDEX on pg_class as superuser?

Yes, in fact, I have even done a vacuumdb  -v -a -f
The problem is still there

Melvin

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

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,

...

NOTICE: CREATE TABLE will create implicit sequence...
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit
index "xtmp_changed_ids" for table "xtmp_changed_ids"
ERROR: relation "xtmp_changed_ids" already exists

This seems to indicate it's the xtmp_changed_ids INDEX that
already exists, not the xtmp_changed_ids TABLE. If it was the
table, we'd presumably see the ERROR appear before the
NOTICE (as the table is created first, and then the indexes).
Thus, make sure you don't have an index named xtmp_changed_ids
somewhere already: \di xtmp_changed_ids

- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201012171118
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAk0LjWYACgkQvJuQZxSWSsh/tgCgtKx53lBBjcbTR1178t1ZpLJL
JIUAn0giUTyphUX7D0KGDzb1C7bK0nw5
=n8OS
-----END PGP SIGNATURE-----

On Friday 17 December 2010 8:16:52 am Melvin Davidson wrote:

Have you tried a REINDEX on pg_class as superuser?

Yes, in fact, I have even done a vacuumdb  -v -a -f
The problem is still there

Melvin

Yea, I believe Greg may have the answer, he has a better eye than I.

--
Adrian Klaver
adrian.klaver@gmail.com

On Fri, Dec 17, 2010 at 07:47:44AM -0800, Melvin Davidson wrote:

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)

please don't name your constraint using the same name you named your
table.

constraint name is used to create index, and at the time of index
creation - table already exists.

just skip "CONSTRAINT xtmp_changed_ids" part, and you'll be fine.

depesz

--
Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

please don't name your constraint using the same name you named your
table.

DOH! and duh. :)

I can't believe I missed the obvious, but that's why it's better to have someone else
take a look.
Thanks for spotting that.  I've tacked on _pk to the constraint. Fixed!

Melvin

"Greg Sabino Mullane" <greg@turnstep.com> writes:

This seems to indicate it's the xtmp_changed_ids INDEX that
already exists, not the xtmp_changed_ids TABLE.

Oh, of course: he's got this:

CREATE TABLE xtmp_changed_ids
...
CONSTRAINT xtmp_changed_ids PRIMARY KEY (seq_all)

so he's trying to force the pkey index to have the same name as the
table. Doesn't work.

regards, tom lane

On Dec 17, 2010, at 10:34 AM, Melvin Davidson wrote:

please don't name your constraint using the same name you named your
table.

I can't believe I missed the obvious, but that's why it's better to have someone else
take a look.
Thanks for spotting that. I've tacked on _pk to the constraint. Fixed!

FWIW, I find it useful to provide some kind of indication of what fields are in the index in the index name, ie: xtmp_changed_ids__pk_seq_all.
--
Jim C. Nasby, Database Architect jim@nasby.net
512.569.9461 (cell) http://jim.nasby.net