Обсуждение: undead index
Hi, pg_upgrade brakes with the following error: pg_upgrade 8.4.5 to 9.0.4: Restoring user relation files /data1/postgres/pgsql/foo/data_8.4/base/11564/2613 ^M /data1/postgres/pgsql/foo/data_8.4/base/11564/2683 Could not find foo.bar_idx in old cluster This index was deleted several weeks ago. server_version | 8.4.5 foo=# select * from pg_class where relname = 'bar_idx'; (No rows) after pg_dumpall|psql from 8.4 to 9.0 the undead index revived on the target DB: server_version | 9.0.4 foo=# select * from pg_class where relname = 'bar_idx'; -[ RECORD 1 ]---+------------------------------------ relname | bar_idx relnamespace | 16409 Besides the question how this could happen, Is there another way to correct this without using dump|restore? I'd like to get pg_upgrade working. Regards, Jens
Jens Wilke <jens.wilke@affinitas.de> writes: > pg_upgrade brakes with the following error: > Could not find foo.bar_idx in old cluster Hmm, is this an autogenerated index? I suspect pg_upgrade can't cope if it's been assigned a different name in the new cluster. regards, tom lane
2011/5/4 Jens Wilke <jens.wilke@affinitas.de>: > > This index was deleted several weeks ago. > [...] > > after pg_dumpall|psql from 8.4 to 9.0 the undead index revived on the target > DB: I understood that you droped an index and when you dump/restore you get your index again. Did I miss something ? -- Cédric Villemain 2ndQuadrant http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support
On Thursday 05 May 2011 16:46:05 Cédric Villemain wrote: > I understood that you droped an index and when you dump/restore you > get your index again. Yes, that's it, after the pg_upgrade error, i removed the target data directory, and initialzed a new target DB. After pg_dumpall|pg_dump i got an index that is not visible in the source DB. It had either been deleted weeks ago or disappeared from the system catalog for any other reason. I can't find this index in a new pg_dumpall output from the source DB. Regards, Jens
2011/5/6 Jens Wilke <jens.wilke@affinitas.de>: > On Thursday 05 May 2011 16:46:05 Cédric Villemain wrote: > >> I understood that you droped an index and when you dump/restore you >> get your index again. > > Yes, that's it, after the pg_upgrade error, i removed the target data > directory, and initialzed a new target DB. > After pg_dumpall|pg_dump i got an index that is not visible in the source DB. > It had either been deleted weeks ago or disappeared from the system catalog > for any other reason. > I can't find this index in a new pg_dumpall output from the source DB. Okay! (I didn't understood correctly), please check the question from Tom. -- Cédric Villemain 2ndQuadrant http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support
On Wednesday 04 May 2011 17:32:50 Tom Lane wrote: > Hmm, is this an autogenerated index? I don't think so. And to confirm, that i really deleted the new cluster between the pg_upgrade run and the dump|restore i did it again and was able to revive this index again: foo=# \d+ foo.bar_idx Index "foo.bar_idx" Column | Type | Definition | Storage | Description ----------+-----------------------+------------+----------+------------- ulq_guid | character varying(24) | ulq_guid | extended | btree, for table "foo.foo" But it's again not in the pg_dumpall output, using the same binary like for the dump|restore. Regards, Jens
Jens Wilke <jens.wilke@affinitas.de> writes: > On Wednesday 04 May 2011 17:32:50 Tom Lane wrote: >> Hmm, is this an autogenerated index? > I don't think so. > And to confirm, that i really deleted the new cluster between the pg_upgrade > run and the dump|restore i did it again and was able to revive this index > again: > foo=# \d+ foo.bar_idx > Index "foo.bar_idx" > Column | Type | Definition | Storage | Description > ----------+-----------------------+------------+----------+------------- > ulq_guid | character varying(24) | ulq_guid | extended | > btree, for table "foo.foo" > But it's again not in the pg_dumpall output, using the same binary like for > the dump|restore. Well, if you don't see it in the pg_dumpall script, but running that script creates the index, then I'd say it's autogenerated. Possibly if you showed us the actual (not obfuscated) table declaration, associated constraint declarations, and resulting index definition, things would be clearer. regards, tom lane
On Friday 06 May 2011 17:18:29 Tom Lane wrote: Hi Tom, > Possibly if > you showed us the actual (not obfuscated) table declaration, associated > constraint declarations, and resulting index definition, things would be > clearer. Thanks Tom, yes, the index is named Indexes: "concurrently" btree (ulq_guid) In the 8.4 cluster and 9.0.4's pg_dumpall dumps it as CREATE INDEX concurrently ON foo USING btree (ulq_guid); That's it. But shouldn't pg_upgrade be able to handle this? Regards, Jens
Jens Wilke <jens.wilke@affinitas.de> writes: > Thanks Tom, yes, the index is named > Indexes: > "concurrently" btree (ulq_guid) > In the 8.4 cluster and 9.0.4's pg_dumpall dumps it as > CREATE INDEX concurrently ON foo USING btree (ulq_guid); > That's it. Oh, fun. We knew that not reserving that keyword was going to cause some problems. > But shouldn't pg_upgrade be able to handle this? It's not pg_upgrade's fault; it's pg_dump that's failing to reproduce the state of the source database. I'm inclined to think that maybe we should hack pg_dump to forcibly quote "concurrently" in this context, even though it doesn't do so anywhere else since the word isn't reserved. regards, tom lane
I wrote: > It's not pg_upgrade's fault; it's pg_dump that's failing to reproduce > the state of the source database. > I'm inclined to think that maybe we should hack pg_dump to forcibly > quote "concurrently" in this context, even though it doesn't do so > anywhere else since the word isn't reserved. On closer inspection, pg_dump *does* quote "concurrently" ... if you're dumping from a 9.0 or later database. The problem is that it gets the index definition command from pg_get_indexdef(), which means it's relying on the server to do appropriate quoting, and a pre-9.0 server does not think there is any reason to quote "concurrently". There doesn't appear to be any fix for this that doesn't require a time machine and/or a lot more effort than it's worth. Suggest you rename the index in the 8.4 database. regards, tom lane
On Friday 06 May 2011 18:08:58 Tom Lane wrote: > There doesn't appear to be any fix for this that doesn't require a time > machine and/or a lot more effort than it's worth. Isn't it possible to backport the fix for pg_get_indexdef() to 8.* ? > Suggest you rename > the index in the 8.4 database. That's already done. Regards, Jens
Jens Wilke <jens.wilke@affinitas.de> writes: > On Friday 06 May 2011 18:08:58 Tom Lane wrote: >> There doesn't appear to be any fix for this that doesn't require a time >> machine and/or a lot more effort than it's worth. > Isn't it possible to backport the fix for pg_get_indexdef() to 8.* ? We could install a kluge (not backport the actual change, which was adding CONCURRENTLY to the set of grammar keywords). But that would only help people who were trying to update from 8.4.something-after-8. Now that I think about it, I believe we considered that at the time, and rejected it on the grounds that the case would come up so seldom as to not be worth the trouble. I think that's probably still true, and anyway the value of doing it drops further every day. The time to have done it would have been a year ago. regards, tom lane