Re: Edge case problem with pg_dump

* Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> [020523 10:24]:
> "D'Arcy J.M. Cain" <darcy(at)druid(dot)net> writes:
> > Do you mean issue COPY commands with fields or COPY out the fields in a
> > specific order by using the extension in pg_dump?
>
> I intended that the dump scripts would say something like
>
> COPY mytab(field1,field2,field3) FROM STDIN;

Cool. I assume that the "(field1,field2,field3)" would be optional for
backwards compatibility.

> which would make it absolutely clear what the dump's field order is.
> We can't solve it by reordering the fields while we dump, which is
> what I think you mean by the other alternative: how is pg_dump to
> guess what schema you are going to load the data into? For example,

Well, the issue now is that it creates the schema too but it is out of
sync with the data it spits out. I can see how figuring it out is a lot
more difficult though. The above works.

> it should work to do a data-only dump and then reload into the existing
> table structure. So the dump script really needs to work for either
> column ordering in the destination table, and that's why we need
> explicit labeling of the field order in the script.

That's nice. I have scripts that effectively do this in code now when
I have to dump from one schema and load into another.

> If we take this really seriously we might want to eliminate pg_dump's
> -d (simple INSERT) option, and have only two dump formats: COPY with
> field labels, or INSERT with field labels.

Yah, I don't think that I have ever used "-d". In fact, I bet I will
hardly ever use "-D" any more if we make the above change. The only
reason I ever used insert statements was to deal with loading into a
different schema.

So who was it that wanted to make this change. Perhaps I can help.

--
D'Arcy J.M. Cain <darcy(at){druid|vex}.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.