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

Oh yeah, I'm using a fairly complex inheritance hierarchy, so it may be
related to a difference between the order COPY may output fields and the
order fields may be deemed when re-created via a CREATE TABLE,
especially with regard to inheritance and possibly ALTER TABLE ADD
COLUMN.

Because of the complex inheritance, I can't just reorder the columns in
the CREATE TABLE of the pg_dump, because it is mostly postgresql which
is determining field order somehow according to inheritance. In general,
the anonymous field nature of COPY seems particularly bad in conjunction
with inheritance where field order is determined by the database rather
than the user, especially since it seems postgresql doesn't necessarily
re-create the same order after a pg_dump.

I'm pretty sure that the ALTER TABLE ADD COLUMN is still part of the
problem though, because if I re-create the schema from scratch I can
dump and restore properly. It seems to be my use of ADD COLUMN which has
made postgres inconsistent in its column orderings.

Chris Bitmead wrote:
> 
> 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"