[BUGS] 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: -- === /* 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 SELECT * 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 -- 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) ; -- A workaround is to hand-edit the backup file and fix the broken SELECT. -- === 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 ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [BUGS] I incrementally altered my database into a state where backups couldn't be restored.
> 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. > [...] > 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. 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 ... ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[BUGS] BUG #7906: pg_dump exits successfully after an error
The following bug has been logged on the website: Bug reference: 7906 Logged by: Adam Tomjack Email address: adam.tomj...@zuerchertech.com PostgreSQL version: 9.2.1 Operating system: Linux 2.6.32-39-server #86-Ubuntu SMP x86_64 Description: I have a database that uses a user-defined datatype. If the .so file implementing that type is missing, pg_dump will fail when dumping a table which uses that type, but it will still exit with status 0. The dump file will be truncated and invalid. Output: pg_dump: Dumping the contents of table "foo" failed: PQgetResult() failed. pg_dump: Error message from server: ERROR: could not access file "bar-type": No such file or directory pg_dump: The command was: COPY public.foo (baz) TO stdout; -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
