Re: pg_dump of regression db?
Philip Warner <pjw@rhyme.com.au> writes:
I thought I would test the latest changes to pg_dump on the regression db,
and got the following output for an aggregate:
CREATE AGGREGATE newcnt (BASETYPE = opaque, SFUNC = int4inc, STYPE =
int4, INITCOND = '0' );
Unfortunately, the backend produces the following error when this statement
is executed:
ERROR: AggregateCreate: Type 'opaque' undefined
The command needs to read "basetype = any". I guess you'll have to
special-case this in pg_dump (or more accurately, change the special
case that's probably there now for aggbasetype = 0). I think I changed
the aggregate regression test to exercise basetype = any not long ago.
It didn't before, which is why you didn't see the failure before.
I vaguely recall seeing something about pg_dump not working of the
regression db, but would be interested to know if this is the known
problem,
No, the known problem is that ALTER TABLE on a inheritance hierarchy
screws up the column ordering of the child tables:
* create parent table w/columns a,b,c
* create child table adding columns d,e to parent
* alter parent* add column f
At this point the parent has columns a,b,c,f and the child has
a,b,c,d,e,f --- in that order.
pg_dump will now produce a script that creates parent with a,b,c,f
and then creates child adding d,e, so that the child table has
columns a,b,c,f,d,e --- in that order. Unfortunately the COPY output
for the child has the columns in order a,b,c,d,e,f, so the data reload
fails.
IMHO this is not pg_dump's fault, it's a bug in ALTER TABLE. See the
archives for prior discussions of how ALTER TABLE might be fixed so that
the child has the "correct" column order a,b,c,f,d,e right off the bat.
In the meantime, it's possible to work around this if you use pg_dump's
most verbose form of data dumping, where the data is reloaded by INSERT
commands with called-out column names (I forget what the option name
is). You should find that pg_dump will work on the regression database
if you use that option.
regards, tom lane
Import Notes
Reply to msg id not found: 3.0.5.32.20000914025515.02a9c8a0@mail.rhyme.com.auReference msg id not found: 3.0.5.32.20000914025515.02a9c8a0@mail.rhyme.com.au
At 12:34 13/09/00 -0400, Tom Lane wrote:
The command needs to read "basetype = any". I guess you'll have to
special-case this in pg_dump (or more accurately, change the special
case that's probably there now for aggbasetype = 0). I think I changed
the aggregate regression test to exercise basetype = any not long ago.
It didn't before, which is why you didn't see the failure before.
The particular piece of code (findTypeByOid) that does this is used to
display types other places (eg. function return types). My guess is that I
should use the new 'format_type' function in these as well, and have a flag
for the specific case of the aggregate dumping code.
So I would build the type info table with a new column that contains
'typedefn', which is just the output of format_type(typeid, NULL), and
pass an 'opaque as any' flag when dumping aggregates.
Does this sound reasonable?
I vaguely recall seeing something about pg_dump not working of the
regression db, but would be interested to know if this is the known
problem,No, the known problem is that ALTER TABLE on a inheritance hierarchy
screws up the column ordering of the child tables:
...
IMHO this is not pg_dump's fault, it's a bug in ALTER TABLE. See the
archives for prior discussions of how ALTER TABLE might be fixed so that
the child has the "correct" column order a,b,c,f,d,e right off the bat.
Am I correct that someone was working on allowing a column order to be
specified in COPY commands? If so, this would fix the problem, I think.
----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/
At 12:34 13/09/00 -0400, Tom Lane wrote:
The command needs to read "basetype = any". I guess you'll have to
Does this apply to any other parts of 'CREATE AGGREGATE' (or anywhere else?)
----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/