Re: BUG #11867: Strange behaviour with composite types after resetting database tablespace

marc(at)bloodnok(dot)com writes:
> I have a script (below) that sets and resets the tablespace for a database
> and drops and recreates a composite type. The script fails when trying to
> re-create the previously dropped composite type because the type has
> apparently been magically resuscitated by changing the tablespace for the
> database. I assume this is a bug.

<spock>Fascinating.</spock>

I believe what is happening is:

* You create the composite type. This results in entries in the
database's pg_class and pg_type tables. These entries are in blocks
of those tables that are in shared buffers, with hashtag RelFileNodes
that mention the database's original tablespace (tbs3).

* You move the database to another tablespace. We faithfully flush
the dirty catalog blocks to disk and then copy them somewhere else
on disk.

* You drop the composite type. This entails fetching in relevant
blocks of the database's pg_class and pg_type tables from their
new tablespace and marking the catalog rows deleted. This happens in
shared buffers that have hashtags mentioning the database's new
tablespace (pg_default, in this example).

* You move the database back to its original tablespace. We faithfully
flush the dirty catalog blocks to disk and then copy them back to the
original on-disk location.

However: at no point in this sequence did we flush the original catalog
blocks out of shared buffers. Therefore, a read of the database's
pg_class or pg_type at this point is likely to find the previous states of
those pages (pre-composite-type-drop) as valid, matching entries, so that
we skip reading the up-to-date page contents back from disk.

A quick fix for this would be to issue DropDatabaseBuffers during
movedb(), though I'm not sure offhand where in that sequence is the
safest place for it. We could be more restrictive and only drop
buffers that belong to the source tablespace, but I'm not sure it's
worth any extra code to do so.

> This is not mission-critical for me but I'd be grateful for suggestions for
> work-arounds.

I don't see any workaround that's much easier than fixing the bug.
But what's your use case that involves flipping databases from one
tablespace to another and then back again within the life expectancy of
unused shared-buffers pages? It doesn't seem terribly surprising that
nobody noticed this before ...

regards, tom lane