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

I was able to put my database into a state where I couldn't restore a
backup without manually editing the backup file.

I make backups like so:
  $pg_dump --disable-triggers -S postgres -U postgres dbname >
dbname.sql

I did an ALTER TABLE which added a column to a table.  That table was
used in a join in a view.  After adding the column, the SELECT that is
the body of my view would no longer work, even though the ALTER TABLE
succeeded and the view continued to work.

The problem was that when I would try to restore my database from a
backup made after the ALTER TABLE, the statement that restored the view
would fail.  The error got lost in the output, and I didn't notice the
problem until I tried to query the view on the restored database.

Here's an example:

-- =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D
/*
DROP TABLE c CASCADE;
DROP TABLE b CASCADE;
DROP TABLE a CASCADE;
*/

CREATE TABLE a (
 aid SERIAL PRIMARY KEY
);

CREATE TABLE b (
 bid SERIAL PRIMARY KEY,
 aid INTEGER REFERENCES a
);

CREATE TABLE c (
 cid SERIAL PRIMARY KEY,
 bid INTEGER REFERENCES b
);



CREATE VIEW v_a_b_c AS=20
  SELECT *=20
  FROM b
  JOIN c USING (bid)
  JOIN a USING (aid)
;

SELECT * FROM v_a_b_c;

ALTER TABLE c ADD COLUMN aid INTEGER REFERENCES a;

-- This will succeed:
SELECT * FROM v_a_b_c;

-- But, this will fail with=20
-- ERROR: common column name "aid" appears more than once in left table
-- SQL state: 42702
SELECT *=20
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.=20=20
-- When restoring, this will fail:
CREATE OR REPLACE VIEW v_a_b_c AS=20
  SELECT *=20
  FROM b
  JOIN c USING (bid)
  JOIN a USING (aid)
;

-- A workaround is to hand-edit the backup file and fix the broken
SELECT.

-- =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D


I am not subscribed to this list.  If you need clarification, please
email me directly.



Adam Tomjack

Zuercher Technologies LLC
108 E. Missouri Ave. Suite 1
Pierre, SD 57501
(605) 224-4838

Adam Tomjack wrote:
> I was able to put my database into a state where I couldn't restore a
> backup without manually editing the backup file.

I can't reproduce that on 8.1 or 8.2 or CVS HEAD. pg_dump produces this
CREATE statement for the view:

CREATE VIEW v_a_b_c AS
     SELECT b.aid, b.bid, c.cid FROM ((b JOIN c USING (bid)) JOIN a
USING (aid));

Not the CREATE OR REPLACE with a SELECT * you posted.

Which version of Postgres are you using?

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

"Adam Tomjack" <adamtj@zuerchertech.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

Gregory Stark <stark@enterprisedb.com> writes:
> 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.

If we were to do anything about it, I think it would have to be to
forbid the original ALTER.  But I don't see any good way to detect
the situation, either.

Consider also that an ALTER ... RENAME could create a similar failure,
if the new column name conflicts with one in some other table that is
joined against someplace.

I think that the described behavior is actually pretty harmless:
you get everything back except the broken view.  So I'm not too
unhappy saying that it's going to keep working that way.

            regards, tom lane

"Tom Lane" <tgl@sss.pgh.pa.us> writes:

> Gregory Stark <stark@enterprisedb.com> writes:
>> 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.
>
> If we were to do anything about it, I think it would have to be to
> forbid the original ALTER.  But I don't see any good way to detect
> the situation, either.

Hm, we could call pg_get_viewdef() on dependent views and then verify that the
resulting view compiles without error.

For bonus points we could verify that it produces the same parsed view and
throw warning if it doesn't. That would give us a warning if you have a view
with natural joins which changed meanings.

I'm not sure it's worth that much extra work on every ALTER TABLE though. I
suppose pg_dump() could have an option to check these things itself but by
that point it's too late to do anything about it.

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's Slony Replication support!

Heikki Linnakangas wrote:
> Adam Tomjack wrote:
>> I was able to put my database into a state where I couldn't restore a
>> backup without manually editing the backup file.
>
> I can't reproduce that on 8.1 or 8.2 or CVS HEAD. pg_dump produces this
> CREATE statement for the view:
>
> CREATE VIEW v_a_b_c AS
>     SELECT b.aid, b.bid, c.cid FROM ((b JOIN c USING (bid)) JOIN a USING
> (aid));
>
> Not the CREATE OR REPLACE with a SELECT * you posted.

Greg Stark pointed out that the above doesn't work either, though it's
not the exact same output you posted.

I don't see any feasible way to fix that, unfortunately. We'd need to
rewrite view definition quite a bit behind the scenes.

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

> Which version of Postgres are you using?
PostgreSQL 8.1.5 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2
(mingw-special)


> I can't reproduce that on 8.1 or 8.2 or CVS HEAD.=20
> [...]
> Not the CREATE OR REPLACE with a SELECT * you posted.

I was lazy, I apologize.  I typed the CREATE OR REPLACE myself.  I get
the same thing as Heikki from pg_dump.  The point is that it breaks when
restoring with psql[1]


> I think that the described behavior is actually pretty harmless:

Having been bitten by this, I must respectfully disagree.  When I found
the problem, I was  copying a database over to a demonstration laptop
for a show.  I missed the error message when it scrolled past, so it
seemed to work.  Fortunately, I happened to check the area of my app
that used the missing view, so I found it before trying to demo.

Now that I know about it, I can fix my scripts to help me watch out for
it.  It's just one of those things I didn't think to test for until I
ran into it.


>  Hm, we could call pg_get_viewdef() on dependent views and then verify
that the resulting view compiles without error.=20

For what it's worth, I like this idea.  I do ALTER TABLEs infrequently
enough that I would prefer to spend a little extra time to make sure
things are consistent.


Adam Tomjack



[1]
  $ pg_dump -U postgres -S postgres test > test.sql
  $ psql -U postgres -d test -c "DROP SCHEMA public CASCADE;"
  $ psql -U postgres -d test -c "DROP LANGUAGE plpgsql CASCADE;"
  $ psql -U postgres -d test -c "CREATE SCHEMA public AUTHORIZATION
dbusername;"
  $ psql -U postgres -d test -f test.sql
  ...
  psql:test.sql:1396: ERROR:  common column name "aid" appears more than
once in left table
  psql:test.sql:1399: ERROR:  relation "public.v_a_b_c" does not exist
  ...