Re: I incrementally altered my database into a state where backups couldn't be restored.

"Adam Tomjack" <adamtj(at)zuerchertech(dot)com> writes:

> -- This will succeed:
> SELECT * FROM v_a_b_c;
>
> -- But, this will fail with
> -- ERROR: common column name "aid" appears more than once in left table
> -- SQL state: 42702
> SELECT *
> FROM b
> JOIN c USING (bid)
> JOIN a USING (aid)
>
> -- It is now possible to make a backup with pg_dump that cannot be fully
> restored.
> -- When restoring, this will fail:
> CREATE OR REPLACE VIEW v_a_b_c AS
> SELECT *
> FROM b
> JOIN c USING (bid)
> JOIN a USING (aid)
> ;

That does really suck. But I'm not sure what we can do about it. There's no
SQL which is entirely equivalent to the resulting view. I think the closest
you could get would be something like

SELECT *
FROM (SELECT bid,cid FROM b) AS b
JOIN c USING (bid)
JOIN a USING (aid)

But it's not clear to me that we could generate that easily. AFAIK the
information about which columns were in the table at the time the view was
defined isn't readily available. And checking for conflicts might be hard
since they could happen much higher up in the join tree.

We can't just output the USING as an ON clause which would let pg_dump specify
precisely which column to join against because ON doesn't merge the two
columns. The resulting records would have two bid columns.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's On-Demand Production Tuning