Re: How ugly would this be? (ALTER DATABASE)

On 24 Oct 2014 20:28, "Robert Haas" <robertmhaas(at)gmail(dot)com> wrote:
>
> You could perhaps try to create a command that would move a schema
> between two databases in the same cluster. It's fraught with
> practical difficulties because a single backend can't be connected to
> both databases at the same time, so how exactly do you make the
> required catalog changes all in a single transaction? But if you
> imagine that you have an infinite pool of top-notch PostgreSQL talent
> with unbounded time to work on this problem and no other, I bet
> somebody could engineer a solution.

I think the bigger problem is the dependent objects. Things like data types
which might exist in both databases but with different oids.

If you simplify the problem to only handle tables and indexes and only if
they only use system types and other objects then it seems doable but that
creates a lot of pitfalls for users.

I would do it in three steps more like pg_upgrade. 1) copy the schema to
the new database and note the new oids and relfilenodes. 2) copy or move
the data files over. 3) drop the old schema. Each of those can be done in
separate transactions or even backends -- the intermediate states just have
some empty tables in one of the schemas.

It's not clear to me what state the databases should be in during step 2
though. A simple lock would not be sufficient. Perhaps there needs to be
something like indisvalid for tables. That might be handy for pg_bulkload
as well.