Thread: pg_dump of regression db?
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 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, and if there is any value in trying to fix it. psql shows the following: regression=# \da newcnt List of aggregates Name | Type | Description --------+-------------+-------------newcnt | (all types) | (1 row) whereas in 7.0.2, it shows int4, so my guess is that this is a problem with pg_dump and the new function manager. Any suggestions would be appreciated. ---------------------------------------------------------------- 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 |/
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
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 |/
Philip Warner <pjw@rhyme.com.au> writes:
> At 12:34 13/09/00 -0400, Tom Lane wrote:
>> The command needs to read "basetype = any".
> 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?
That would solve the immediate issue, but you might want to look a
little further ahead. The real problem here is that a zero typeid is
(mis) used for several different purposes in the existing backend code.
In this context we see two of them: zero representing "any input
datatype is accepted by this function" and zero representing "opaque
type". When you look at the uses of "opaque" you find that that has
several different meanings as well. Eventually I would like to clean
this up by inventing distinct typeids for each shade of meaning ---
we already have one special typeid of this sort (UNKNOWN) and it seems
like having several of them would be a cleaner way to proceed than
overloading zero with such wild abandon.
I'm not entirely sure what that means for pg_dump; maybe it means that
the problem goes away and just printing the format_type output will
work in all contexts. But for now you should consider that there are
several different possible interpretations of typeid zero. In short,
pass an enum not a boolean...
>> No, the known problem is that ALTER TABLE on a inheritance hierarchy
>> screws up the column ordering of the child tables:
> 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.
No, that is a kluge that would allow pg_dump to work around ALTER
TABLE's fundamental inadequacy. It's not a fix unless you think it's OK
to expect every application forevermore to take special care with column
orders.
regards, tom lane
At 22:43 13/09/00 -0400, Tom Lane wrote: > >> 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. > >No, that is a kluge that would allow pg_dump to work around ALTER >TABLE's fundamental inadequacy. It's not a fix unless you think it's OK >to expect every application forevermore to take special care with column >orders. > I suppose from an application programming point of view, I am used to having to specify column order in my 'select' statements (ie. I don't tend to let 'select * from...' into production code, and usually consider it bad form to do so), so I thought the issue was confined to COPY & pg_dump. And in the case of pg_dump, I would just explicitly set the column order when they are dumped/restored. ---------------------------------------------------------------- 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 |/