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

Excerpts from Dimitri Fontaine's message of mié sep 12 06:51:43 -0300 2012:
> Hi,
>
> Sorry for being late at the party… been distracted away…

Welcome ;-)

> > On Fri, Jun 22, 2012 at 10:37:10PM -0400, Tom Lane wrote:

> >> A bit of looking shows that ALTER EXTENSION SET SCHEMA calls
> >> AlterObjectNamespace_oid on the table. AlterObjectNamespace_oid
> >> calls AlterRelationNamespaceInternal, and nothing else. In comparison,
> >> ALTER TABLE SET SCHEMA (AlterTableNamespace) calls
> >> AlterRelationNamespaceInternal and about four other things. I'm not
> >> sure if this was broken before the last round of refactoring in this
> >> area, but for sure it's broken now.
>
> Looking at that code, my theory of how we got there is that in the
> submitted extension patch I did only use DEPENDENCY_INTERNAL and Tom
> introduced the much better DEPENDENCY_EXTENSION tracking. With the
> former, indexes and sequences and constraints where found in the
> dependency walking code, but only the main relation is now registered in
> the later model.
>
> I need to do some testing about dependency tracking on SERIAL generated
> sequences compared to manually created sequences in extension scripts, I
> think we track sequences directly only in the manual case.

Well, what I saw was that both the table and its SERIAL-generated
sequence got an DEPENDENCY_EXTENSION row in pg_depend, which is exactly
what (IMV) causes the problem. One of my proposals is to tweak the code
to avoid that row (but if we do that, then we need to do something about
databases that contain such rows today).

> I think we need to share more code in between
> AlterRelationNamespaceInternal and AlterTableNamespace, but I'm not sure
> if that's not exactly what Álvaro did try with his _oid() attempt that
> failed.

What I did was create an AlterTableNamespaceInternal that goes through
all the things that must be moved (this means calling
AlterRelationNamespaceInternal for the table, and then doing some more
calls to move the sequences, indexes, constraints). With this change,
both AlterTableNamespace and AlterObjectNamespace_oid can call it.
Previously, AlterObjectNamespace_oid was calling
AlterRelationNamespaceInternal directly for the relation only.

As far as I can see, that split of AlterTableNamespace is still needed.
The problem here is that we need something *beyond* that fix. Did you
look at my patches?

Am I making sense?

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