Re: BUG #6704: ALTER EXTENSION postgis SET SCHEMA leaves dangling relations

Excerpts from Alvaro Herrera's message of vie ago 31 16:25:40 -0400 2012:
> Excerpts from Tom Lane's message of vie ago 31 16:01:03 -0400 2012:
> > Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> writes:
> > > Here's a patch.
> >
> > Looks reasonable, but please try a little harder on the comments for the
> > new function --- IMO it should have a header comment that explains what
> > it's supposed to do exactly.
>
> Sure.
>
> This doesn't actually work though: it's trying to move sequences twice.
> Not sure what the right fix for this is ... still looking.

The error message:

alvherre=# alter extension isn set schema foo;
ERROR: tuple already updated by self

Besides being listed with deptype=extension for the extension in
question, the sequence has a deptype=auto entry for the column, which
leads to it being moved twice.

I don't see any clean fix for this:

1. At extension creation time, skip generating dependencies for
sequences that are part of a table. I don't see how to do this: We
would have to reach within heap_create_with_catalog and tell it not to
add an deptype=extension dependency if it's called for a sequence that's
part of a serial column. I don't think heap_create_with_catalog even
knows that at all.

2. During ALTER EXTENSION execution, skip moving objects that have
already been moved. Not really sure how this would be implemented;
perhaps we could have AlterObjectNamespace_oid add each moved object to
a list of moved objects, and skip everything that's already present in
it. Seems very messy to implement.

3. Pass a flag to AlterTableNamespaceInternal, something like
"skip_sequences", and do not call AlterSeqsNamespace it that's set.
This seems really ugly though, and I'm not sure if it might break
something else.

4. Maybe we could have AlterRelationNamespaceInternal check what the
current namespace is for the object, and do nothing if it's already the
target namespace.

One thing to keep in mind is that existing databases might already have
broken catalogs (i.e. extensions have already been moved and objects are
already in nonexistant schemas). This is not very likely unless the
user has not been using pg_dump at all. But many databases already have
deptype=extension pg_depend entries for sequences owned by SERIAL
columns, so it's unclear that (1) is a good solution even if it were
implementable.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services