undead index

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