Re: [HACKERS] Re:pg_dump barfs? - Mailing list pgsql-hackers

As a follow-up to this, I tried creating a new database from the
original CREATE TABLE statements, with the additional field added to the
CREATE TABLE which I had previously used an ALTER TABLE to add.

I found that the fields came out in a different order when I do a SELECT
* FROM urllink.

This re-enforces my theory that postgres is confused about field orders,
and that there is a bad interaction between ALTER TABLE ADD COLUMN and
any database use which assumes a particular column ordering. In my
opinion, any useful SQL must specify columns in order to be reliable
(even COPY). Unfortunately, COPY does not allow you to specify column
names, and INSERT does not allow you to retain oids, thus I am screwed
right now. Any suggestions on how to salvage my data still welcome :-).


Chris Bitmead wrote:
> 
> Hi!
> 
> I'm trying to dump and restore my database which is a 6.5 May 2nd
> snapshot, but psql is barfing on pg_dump's output. Naturally I find that
> quite disturbing! I'd like to find out how I can salvage my data,
> because right now I havn't got a way of backing it up properly. pg_dump
> -D |psql can re-insert my data, but with the loss of oids, and my schema
> relies on oids. If anyone wants the full pg_dump data let me know.
> pg_dump -o |psql results in the errors.....
> 
> The first one, it looks
> 
> COPY "urllink" WITH OIDS FROM stdin;
> ERROR:  pg_atoi: error in "http://www.photogs.com/bwworld/f5.html":
> can't parse
> "http://www.photogs.com/bwworld/f5.html"
> PQendcopy: resetting connection
> 
> This was caused by the following input
> COPY "urllink" WITH OIDS FROM stdin;
> 24265   \N      Review of Nikon F5      \N      \N      \N      24065
> http://www.photogs.com/bwworld/f5.html  t
> 
> It looks like maybe postgres is expecting an integer and getting a
> string maybe?
> 
> One thing I did which was a little unusual is that I did an ALTER TABLE
> foo ADD COLUMN, but I should have said ALTER TABLE foo* ADD COLUMN to
> get the column on inherited attributes. The only solution I could think
> of was to go and add the attribute to all the sub-classes too. This
> seemed to work (is this what I should have done?), but I don't know if
> this might be related to this problem. Maybe postgres is confused now
> about column orders?? So I wanted desperately to do a pg_dump -D -o, but
> -D stops -o from working (Yuk! This really need to be fixed!)
> 
> (Please give us DROP COLUMN soon! :-)
> 
> The other error looks to be something to do with views...
> 
> CREATE RULE "_RETproductv" AS ON SELECT TO "productv" WHERE  DO INSTEAD
> SELECT "
> oid" AS "oidv", "type", "title", "summary", "body", "image", "category",
> "mfrcod
> e", "mfr", "costprice", "taxrate", "profit", "rrprice", "taxrate" *
> "costprice"
> AS "tax", "costprice" + "profit" AS "exsaleprice", "costprice" +
> "profit" + "tax
> rate" * "costprice" AS "saleprice" FROM "product";
> ERROR:  parser: parse error at or near "do"
> CREATE RULE "_RETorderitemv" AS ON SELECT TO "orderitemv" WHERE  DO
> INSTEAD SELE
> CT "oid" AS "oidv", "product", "webuser", "quantity", "price",
> "taxfree", "order
> status", "orderdatetime", "shipdatetime", "price" * "quantity" AS
> "totalprice" F
> ROM "orderitem";
> ERROR:  parser: parse error at or near "do"