Обсуждение: 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