Re: [GENERAL] Table both does not and does exist! wth?
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 j...@nasby.net 512.569.9461 (cell) http://jim.nasby.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Table both does not and does exist! wth?
"Greg Sabino Mullane" 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Table both does not and does exist! wth?
>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
Re: [GENERAL] Table both does not and does exist! wth?
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: dep...@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Table both does not and does exist! wth?
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.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Table both does not and does exist! wth?
-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 g...@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- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Table both does not and does exist! wth?
>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
Re: [GENERAL] 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 > (mockbu...@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.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general