Thread: "Cluster" means "tangle" for me
I would appreciate any advice on getting out of this strange situation. My table now doesn't exist, but I can't recreate it either (at least under that name). jeffe@kiyoko=> psql -V psql (PostgreSQL) 7.0.0 jeffe@kiyoko=> uname -a FreeBSD kiyoko.la.verio.net 4.0-STABLE FreeBSD 4.0-STABLE #0: Thu Apr 27 10:44:07 CDT 2000 extracts=# create index dc_et_i on dedcolo (equip_type); CREATE extracts=# cluster dc_et_i on dedcolo; ERROR: temp_286bbc3 is an index relation extracts=# drop index dc_et_i; ERROR: index "dc_et_i" nonexistent extracts=# \d dedcolo Did not find any relation named "dedcolo". extracts=# vacuum verbose analyze; (snip) NOTICE: mdopen: couldn't open temp_28421e0: No such file or directory NOTICE: RelationIdBuildRelation: smgropen(temp_28421e0): No such file or directory NOTICE: --Relation temp_28421e0-- NOTICE: mdopen: couldn't open temp_28421e0: No such file or directory ERROR: cannot open relation temp_28421e0 extracts=# drop table temp_28421e0; NOTICE: mdopen: couldn't open temp_28421e0: No such file or directory NOTICE: mdopen: couldn't open temp_28421e0: No such file or directory NOTICE: mdopen: couldn't open temp_28421e0: No such file or directory
Jeff Eckermann <jeckermann@verio.net> writes:
> I would appreciate any advice on getting out of this strange situation. My
> table now doesn't exist, but I can't recreate it either (at least under that
> name).
Hmm, was "dedcolo" a temp table? It looks like clustering a temp table
gets confused. (In current sources, it's still pretty broken: the
cluster succeeds, but the table is no longer temp afterwards ...
will try to fix this for 7.1.)
I'd suggest that you restart your session, then repeat the vacuum,
and for each table that you get "mdopen" notices about, create an
empty file by that name in the database directory. Then you'll be
able to drop that table.
regards, tom lane
Thanks for the advice.
I repeated the vacuum, but it completed without any mdopen notices this
time.
I created files corresponding to the temp* names from the last vacuum, but
was still unable to drop the table.
After confirming that there was no reference to the table name in any of the
system tables, I deleted the "dedcolo" file from the database directory.
Now I was able to create a table with that name.
I couldn't resist going back for a second try, using a similarly named table
with the same data (neither this nor the previous was a temp table, BTW):
extracts=# create table dedcolo (test text);
CREATE
extracts=# drop table dedcolo;
DROP
extracts=# \d dedcolo2
Table "dedcolo2"
Attribute | Type | Modifier
--------------------------+---------+----------
market_code | text |
legacy_acct_no | text |
usage_guiding | text |
service_identifier | text |
subscriber_no | integer |
rev_rcv_cost_center | text |
arbor_acct_no | integer |
last_name | text |
first_name | text |
company_name | text |
sales_code | text |
date_created | date |
tracking_id | integer |
product_start_date | date |
product_stop_date | date |
product_status | text |
prod_billed_thru_date | date |
element_id | text |
equip_type | text |
product_description | text |
billing_frequency | text |
rate | money |
rate_override_start_date | date |
rate_override_end_date | date |
rate_override | money |
disconnect_reason | text |
Indices: dc2_acct_i,
dc2_et_i
extracts=# cluster dc2_acct_i on dedcolo2;
ERROR: temp_28a1899 is an index relation
extracts=# \d dedcolo
Did not find any relation named "dedcolo".
extracts=# \d temp_28a1899
Index "temp_28a1899"
Attribute | Type
---------------+---------
arbor_acct_no | integer
btree
extracts=#
This corresponds to the index being clustered.
> -----Original Message-----
> From: Tom Lane [SMTP:tgl@sss.pgh.pa.us]
> Sent: Tuesday, January 09, 2001 6:57 PM
> To: Jeff Eckermann
> Cc: 'pgsql-general@postgresql.org'
> Subject: Re: [GENERAL] "Cluster" means "tangle" for me
>
> Jeff Eckermann <jeckermann@verio.net> writes:
> > I would appreciate any advice on getting out of this strange situation.
> My
> > table now doesn't exist, but I can't recreate it either (at least under
> that
> > name).
>
> Hmm, was "dedcolo" a temp table? It looks like clustering a temp table
> gets confused. (In current sources, it's still pretty broken: the
> cluster succeeds, but the table is no longer temp afterwards ...
> will try to fix this for 7.1.)
>
> I'd suggest that you restart your session, then repeat the vacuum,
> and for each table that you get "mdopen" notices about, create an
> empty file by that name in the database directory. Then you'll be
> able to drop that table.
>
> regards, tom lane
Jeff Eckermann <jeckermann@verio.net> writes:
> extracts=# cluster dc2_acct_i on dedcolo2;
> ERROR: temp_28a1899 is an index relation
> extracts=# \d dedcolo
> Did not find any relation named "dedcolo".
> extracts=# \d temp_28a1899
> Index "temp_28a1899"
> Attribute | Type
> ---------------+---------
> arbor_acct_no | integer
> btree
Now that I think about it, the "temp_xxx" name does not correspond to
what we use for temporary tables; rather it is the form of temporary
name under which the CLUSTER command creates the new table and index.
(Under the hood, CLUSTER builds the new table and index, then drops
the old ones and renames the new ones into place. This is why you
lose all the other indexes and other decoration :-(.)
The cited error seems to indicate that CLUSTER is trying to open the
new index with heap_open instead of index_open. If so, it'd fail
every time :-( ... but the new index and table ought to go away on
failure, not hang around. So I'm still confused.
What version of Postgres did you say you are using?
regards, tom lane
I'm using version 7.00. I know there has been a lot of bugs cleaned up since, but nothing that has bitten me yet. I was hoping to hold off upgrading until the release of 7.1, if that is what you would be suggesting .... > -----Original Message----- > From: Tom Lane [SMTP:tgl@sss.pgh.pa.us] > Sent: Wednesday, January 10, 2001 9:00 PM > To: Jeff Eckermann > Cc: 'pgsql-general@postgresql.org' > Subject: Re: [GENERAL] "Cluster" means "tangle" for me > > Jeff Eckermann <jeckermann@verio.net> writes: > > extracts=# cluster dc2_acct_i on dedcolo2; > > ERROR: temp_28a1899 is an index relation > > extracts=# \d dedcolo > > Did not find any relation named "dedcolo". > > extracts=# \d temp_28a1899 > > Index "temp_28a1899" > > Attribute | Type > > ---------------+--------- > > arbor_acct_no | integer > > btree > > Now that I think about it, the "temp_xxx" name does not correspond to > what we use for temporary tables; rather it is the form of temporary > name under which the CLUSTER command creates the new table and index. > (Under the hood, CLUSTER builds the new table and index, then drops > the old ones and renames the new ones into place. This is why you > lose all the other indexes and other decoration :-(.) > > The cited error seems to indicate that CLUSTER is trying to open the > new index with heap_open instead of index_open. If so, it'd fail > every time :-( ... but the new index and table ought to go away on > failure, not hang around. So I'm still confused. > > What version of Postgres did you say you are using? > > regards, tom lane
Jeff Eckermann <jeckermann@verio.net> writes:
> I'm using version 7.00.
Well, there's your problem ...
> I know there has been a lot of bugs cleaned up since, but nothing that has
> bitten me yet. I was hoping to hold off upgrading until the release of 7.1,
> if that is what you would be suggesting ....
Consider yourself bitten. You may care to contemplate these post-7.0
CVS log entries:
Revision 1.52 / (download) - annotate - [select for diffs] , Thu May 11 03:54:17 2000 UTC (8 months ago) by tgl
Branch: MAIN
Changes since 1.51: +18 -36 lines
Diff to previous 1.51
Fix CLUSTER ... or at least undo the bit-rot it's suffered since 6.5.
It's still pretty fundamentally bogus :-(.
Freebie side benefit: ALTER TABLE RENAME works on indexes now.
Revision 1.53 / (download) - annotate - [select for diffs] , Fri May 12 16:10:09 2000 UTC (8 months ago) by tgl
Branch: MAIN
CVS Tags: REL7_0_PATCHES
Changes since 1.52: +18 -5 lines
Diff to previous 1.52
Squash some more CLUSTER bugs. Never has worked on multiple-column
indexes, apparently, nor on functional indexes with more than one input
column (force of natts = 1 was in the wrong branch of IF statement).
Coredumped if source relation contained any uncommitted tuples, due to
failure to test for success return from heap_fetch. Fetched tuple
was passed directly to heap_insert, which clobbers the TID and commit
status in the tuple header it's given, which meant that the source
relation's tuples all got trashed as the copy proceeded. Abort partway
through, and you're left with a lot of missing tuples.
I wonder what else is lurking here ...
regards, tom lane