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

<p dir="ltr"><br /> On 24 Oct 2014 20:28, "Robert Haas" <<a
href="mailto:robertmhaas@gmail.com">robertmhaas@gmail.com</a>>wrote:<br /> ><br /> > You could perhaps try to
createa command that would move a schema<br /> > between two databases in the same cluster.  It's fraught with<br />
>practical difficulties because a single backend can't be connected to<br /> > both databases at the same time,
sohow exactly do you make the<br /> > required catalog changes all in a single transaction?  But if you<br /> >
imaginethat you have an infinite pool of top-notch PostgreSQL talent<br /> > with unbounded time to work on this
problemand no other, I bet<br /> > somebody could engineer a solution.<p dir="ltr">I think the bigger problem is the
dependentobjects. Things like data types which might exist in both databases but with different oids. <p dir="ltr">If
yousimplify the problem to only handle tables and indexes and only if they only use system types and other objects then
itseems doable but that creates a lot of pitfalls for users.<p dir="ltr">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
filesover. 3) drop the old schema. Each of those can be done in separate transactions or even backends -- the
intermediatestates just have some empty tables in one of the schemas.<p dir="ltr">It's not clear to me what state the
databasesshould be in during step 2 though. A simple lock would not be sufficient. Perhaps there needs to be something
likeindisvalid for tables. That might be handy for pg_bulkload as well.