Обсуждение: Diffrent column ordering after dump/restore tables with INHERITS
If i have tables with inherits, and the
n i adding a column in to the base table, the new column will be at
the on of column list in child table, but when i done dump->restore i
have surprise,
Column ordering was changed.
Some example:
CREATE TABLE test_base (
id INT
);
CREATE TABLE test_child_with_data (
t TEXT
) INHERITS (test_base);
ALTER TABLE test_base ADD COLUMN date DATE;
INSERT INTO test_child_with_data
SELECT 1, 'test', NOW(); -- works fine.
-- Dump/restore
INSERT INTO test_child_with_data
SELECT 1, 'test', NOW(); -- error..
So, column ordering after dump, restore must be the same!
I don't know how to live with it, now i have on production-server
small db, and i can use dump/restore. but on other project
dump/restore takes 8 hours.. so, any ideas ?
More detailed how i use it
in one procedure
INSERT INTO test_child_with_data
SELECT select_procedure();
select_procedure:
...
SELECT 1, 'test', NOW()
FROM...
No, we can't do like this, because we have something like this:
CREATE TABLE test_base (
id INT
);
CREATE TABLE test_child_with_data (
t TEXT
) INHERITS (test_base);
ALTER TABLE test_base ADD COLUMN date DATE;
INSERT INTO test_child_with_data VALUES (1, 'text', NOW());
CREATE FUNCTION some_magic() RETURNS SETOF test_child_with_data AS
$$
SELECT (SUM(id), t, MIN(date))::test_child_with_data
FROM test_child_with_data
GROUP BY t
$$ LANGUAGE SQL;
INSERT INTO test_child_with_data
SELECT * FROM some_magic(); -- Works fine.
-- Dump/restore
INSERT INTO test_child_with_data
SELECT * FROM some_magic(); -- Error.
On Sun, Jul 5, 2009 at 4:48 AM, toruvinn<toruvinn@lain.pl> wrote:
> On Sat, 04 Jul 2009 22:10:55 +0200, Oleg Serov <serovov@gmail.com> wrote:
>>
>> INSERT INTO test_child_with_data
>> =A0 =A0 =A0 =A0SELECT 1, 'test', NOW(); -- works fine.
>> -- Dump/restore
>> INSERT INTO test_child_with_data
>> =A0 =A0 =A0 =A0SELECT 1, 'test', NOW(); -- error..
>
> How about INSERT INTO test_child_with_data (id, t, date) SELECT 1, 'test',
> NOW(); ? You'll won't have to care about order of the columns anymore.
>
> Best regards,
> --
> ru
>
Here is the solution of this problem:
Patch
src/bin/pg_dump/pg_dump.c
appendPQExpBuffer(q, "CREATE TABLE %s (",
fmtId(tbinfo->dobj.name));
actual_atts =3D 0;
for (j =3D 0; j < tbinfo->numatts; j++)
{
/* Is this one of the table's own attrs, and not dropped ? */
- if (!tbinfo->inhAttrs[j] &&
- (!tbinfo->attisdropped[j] || binary_upgrade))
+ if (!tbinfo->attisdropped[j] || binary_upgrade)
{
/* Format properly if not first attr */
if (actual_atts > 0)
appendPQExpBuffer(q, ",");
appendPQExpBuffer(q, "\n ");
Before patch
CREATE TABLE "tmp"."stat_agg_all" (
"foo" BOOLEAN
) INHERITS ("prt4_stat"."stat_data", "prt4_stat"."stat_agg")
WITH OIDS;
After patch:
CREATE TABLE "tmp"."stat_agg_all" (
"foo" BOOLEAN,
"time" TIMESTAMP WITH TIME ZONE NOT NULL,
"banner_views" NUMERIC,
"ad_page_views" NUMERIC,
"phone_validate_views" NUMERIC,
"phone_validate_code_sended" NUMERIC,
"phone_validate_code_validate" NUMERIC,
"ad_form_views" NUMERIC,
"ad_form_submits" NUMERIC,
"ad_shop_select_views" NUMERIC,
"ad_emitted" NUMERIC,
"ad_redeemed" NUMERIC,
"client_payed" NUMERIC,
"mediapartner_charged" NUMERIC,
"ad_banner_views" NUMERIC,
"id" BIGINT NOT NULL,
"interval" "prt4_stat"."stat_enum_interval"
) INHERITS ("prt4_stat"."stat_data", "prt4_stat"."stat_agg")
WITH OIDS;
it well be okey!
On Sun, Jul 5, 2009 at 12:45 PM, Oleg Serov<serovov@gmail.com> wrote:
> No, we can't do like this, because we have something like this:
>
> CREATE TABLE test_base (
> =A0 =A0 =A0 =A0id INT
> );
> CREATE TABLE test_child_with_data (
> =A0 =A0 =A0 =A0t TEXT
> ) INHERITS (test_base);
>
> ALTER TABLE test_base ADD COLUMN date DATE;
>
> INSERT INTO test_child_with_data VALUES (1, 'text', NOW());
>
> CREATE FUNCTION some_magic() RETURNS SETOF test_child_with_data AS
> $$
> =A0 =A0 =A0 =A0SELECT (SUM(id), t, MIN(date))::test_child_with_data
> =A0 =A0 =A0 =A0FROM test_child_with_data
> =A0 =A0 =A0 =A0GROUP BY t
> $$ LANGUAGE SQL;
>
>
> INSERT INTO test_child_with_data
> =A0 =A0 =A0 =A0SELECT * FROM some_magic(); -- Works fine.
>
>
> -- Dump/restore
>
>
> INSERT INTO test_child_with_data
> =A0 =A0 =A0 =A0SELECT * FROM some_magic(); -- Error.
>
>
> On Sun, Jul 5, 2009 at 4:48 AM, toruvinn<toruvinn@lain.pl> wrote:
>> On Sat, 04 Jul 2009 22:10:55 +0200, Oleg Serov <serovov@gmail.com> wrote:
>>>
>>> INSERT INTO test_child_with_data
>>> =A0 =A0 =A0 =A0SELECT 1, 'test', NOW(); -- works fine.
>>> -- Dump/restore
>>> INSERT INTO test_child_with_data
>>> =A0 =A0 =A0 =A0SELECT 1, 'test', NOW(); -- error..
>>
>> How about INSERT INTO test_child_with_data (id, t, date) SELECT 1, 'test=
',
>> NOW(); ? You'll won't have to care about order of the columns anymore.
>>
>> Best regards,
>> --
>> ru
>>
>
Oleg Serov <serovov@gmail.com> writes:
> If i have tables with inherits, and the
> n i adding a column in to the base table, the new column will be at
> the on of column list in child table, but when i done dump->restore i
> have surprise,
> Column ordering was changed.
This is not a bug, it's the intended behavior.
regards, tom lane
On Sun, Jul 5, 2009 at 4:28 PM, Tom Lane<tgl@sss.pgh.pa.us> wrote: > when i done dump->restore i >> have surprise, >> Column ordering was changed. > > This is not a bug, it's the intended behavior. I thought that was a bug, just one that was too hard to fix for the problems it caused. It might be more fixable if we get around to the work that was discussed earlier where we separate attnum into three different values. Oleg: note that having the columns in the same position allows some optimizations in the executor so it's probably a good thing if it hasn't broken your application. -- greg http://mit.edu/~gsstark/resume.pdf
On Sun, Jul 5, 2009 at 5:10 PM, Greg Stark<gsstark@mit.edu> wrote: > On Sun, Jul 5, 2009 at 4:28 PM, Tom Lane<tgl@sss.pgh.pa.us> wrote: >> when i done dump->restore i >>> have surprise, >>> Column ordering was changed. >> >> This is not a bug, it's the intended behavior. > > I thought that was a bug Rereading the thread I'll also mention that your fix causes a different problem. Every column becomes marked as having being created in the child table which wasn't the case originally. If you later drop one of those columns from the parent table it's supposed to cascade to the children but it won't because now postgres thinks the child had those columns defined independently. In the current setup we can get either the shared vs local definition above correct or we can get the ordering correct. Getting both correct would require a rework of either the way inheritance works or the way column ordering works. -- greg http://mit.edu/~gsstark/resume.pdf
Greg Stark <gsstark@mit.edu> writes:
> On Sun, Jul 5, 2009 at 4:28 PM, Tom Lane<tgl@sss.pgh.pa.us> wrote:
>> This is not a bug, it's the intended behavior.
> I thought that was a bug, just one that was too hard to fix for the
> problems it caused. It might be more fixable if we get around to the
> work that was discussed earlier where we separate attnum into three
> different values.
Well, if you want to say there's a bug here, it's that ALTER TABLE ADD
COLUMN doesn't put the column in the "right" place to start with.
regards, tom lane
How about adding this patch to postgresql it will slove the problem? On Sun, Jul 5, 2009 at 8:10 PM, Greg Stark<gsstark@mit.edu> wrote: > On Sun, Jul 5, 2009 at 4:28 PM, Tom Lane<tgl@sss.pgh.pa.us> wrote: >> when i done dump->restore i >>> have surprise, >>> Column ordering was changed. >> >> This is not a bug, it's the intended behavior. > > I thought that was a bug, just one that was too hard to fix for the > problems it caused. It might be more fixable if we get around to the > work that was discussed earlier where we separate attnum into three > different values. > > Oleg: note that having the columns in the same position allows some > optimizations in the executor so it's probably a good thing if it > hasn't broken your application. > > -- > greg > http://mit.edu/~gsstark/resume.pdf >
Up! Anybody will answer about the patch? On Mon, Jul 6, 2009 at 11:20 AM, Oleg Serov <serovov@gmail.com> wrote: > How about adding this patch to postgresql it will slove the problem? > > On Sun, Jul 5, 2009 at 8:10 PM, Greg Stark<gsstark@mit.edu> wrote: > > On Sun, Jul 5, 2009 at 4:28 PM, Tom Lane<tgl@sss.pgh.pa.us> wrote: > >> when i done dump->restore i > >>> have surprise, > >>> Column ordering was changed. > >> > >> This is not a bug, it's the intended behavior. > > > > I thought that was a bug, just one that was too hard to fix for the > > problems it caused. It might be more fixable if we get around to the > > work that was discussed earlier where we separate attnum into three > > different values. > > > > Oleg: note that having the columns in the same position allows some > > optimizations in the executor so it's probably a good thing if it > > hasn't broken your application. > > > > -- > > greg > > http://mit.edu/~gsstark/resume.pdf<http://mit.edu/%7Egsstark/resume.pdf> > > > --=20 =F3 =D5=D7=C1=D6=C5=CE=C9=C5=CD =EF=CC=C5=C7 =F3=C5=D2=CF=D7
2010/2/26 Oleg Serov <serovov@gmail.com>: > Up! Anybody will answer about the patch? The patch causes the inheritance history to be lost. If you subsequently drop the column form the parent it'll be kept on the child because it was explicitly declared when you created the child. In the original structure if you dropped the column from the parent it would be dropped from the child because it was an inherited column. Inheritance is in a kind of no-mans land. It's not good enough to be an important feature anyone cares enough about to make it work properly and it's not shoddy enough that it's worth removing. I'm sure there are people using it effectively despite the caveats and rough edges. -- greg
So there are no simple way to do it right, and it will be not fixed? Will this bug appear in todo list? On Fri, Feb 26, 2010 at 6:13 PM, Greg Stark <gsstark@mit.edu> wrote: > 2010/2/26 Oleg Serov <serovov@gmail.com>: > > Up! Anybody will answer about the patch? > > The patch causes the inheritance history to be lost. If you > subsequently drop the column form the parent it'll be kept on the > child because it was explicitly declared when you created the child. > In the original structure if you dropped the column from the parent it > would be dropped from the child because it was an inherited column. > > Inheritance is in a kind of no-mans land. It's not good enough to be > an important feature anyone cares enough about to make it work > properly and it's not shoddy enough that it's worth removing. I'm sure > there are people using it effectively despite the caveats and rough > edges. > > -- > greg > --=20 =F3 =D5=D7=C1=D6=C5=CE=C9=C5=CD =EF=CC=C5=C7 =F3=C5=D2=CF=D7
Oleg Serov <serovov@gmail.com> writes:
> So there are no simple way to do it right, and it will be not fixed? Will
> this bug appear in todo list?
It's not a bug, it's just what happens when you make the parent and
child column orders inconsistent. Would you prefer that we restricted
ALTER TABLE to refuse to perform the alteration in the first place?
regards, tom lane
Tom Lane escribió: > Oleg Serov <serovov@gmail.com> writes: > > So there are no simple way to do it right, and it will be not fixed? Will > > this bug appear in todo list? > > It's not a bug, it's just what happens when you make the parent and > child column orders inconsistent. Would you prefer that we restricted > ALTER TABLE to refuse to perform the alteration in the first place? Possibly the right fix is to implement the oft-discussed idea about columns able to be moved around in table definitions. That would let ALTER TABLE ADD COLUMN to put the column in the right positions in children. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Fri, Feb 26, 2010 at 9:29 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Oleg Serov <serovov@gmail.com> writes: > > So there are no simple way to do it right, and it will be not fixed? Wi= ll > > this bug appear in todo list? > > It's not a bug, it's just what happens when you make the parent and > It is a bug. If i'm doing dump restore i must have _same_ database structure as i dumped. Yes? > child column orders inconsistent. Would you prefer that we restricted > ALTER TABLE to refuse to perform the alteration in the first place? > No! it is not right. We must think how to solve this problem correct. > regards, tom lane > --=20 =F3 =D5=D7=C1=D6=C5=CE=C9=C5=CD =EF=CC=C5=C7 =F3=C5=D2=CF=D7
I'm think you should add some abstract-layer for handling column ordering not as they stored at disk. It is possible? On Fri, Feb 26, 2010 at 10:32 PM, Alvaro Herrera <alvherre@commandprompt.com > wrote: > Tom Lane escribi=C3=B3: > > Oleg Serov <serovov@gmail.com> writes: > > > So there are no simple way to do it right, and it will be not fixed? > Will > > > this bug appear in todo list? > > > > It's not a bug, it's just what happens when you make the parent and > > child column orders inconsistent. Would you prefer that we restricted > > ALTER TABLE to refuse to perform the alteration in the first place? > > Possibly the right fix is to implement the oft-discussed idea about > columns able to be moved around in table definitions. That would let > ALTER TABLE ADD COLUMN to put the column in the right positions in > children. > > -- > Alvaro Herrera > http://www.CommandPrompt.com/ > PostgreSQL Replication, Consulting, Custom Development, 24x7 support > --=20 =D0=A1 =D1=83=D0=B2=D0=B0=D0=B6=D0=B5=D0=BD=D0=B8=D0=B5=D0=BC =D0=9E=D0=BB=D0=B5=D0=B3 =D0=A1=D0=B5=D1=80=D0=BE=D0=B2
yes, that's on the todo list.you can search back in the archives for discussions on how to do this without making the code difficult to debug. this is a bug but it's a longstanding known bug inan area where we have lots of known limitations and nobody is too excited about spending lots of effort to fix up. greg On 27 Feb 2010 14:16, "Oleg Serov" <serovov@gmail.com> wrote: I'm think you should add some abstract-layer for handling column ordering not as they stored at disk. It is possible? On Fri, Feb 26, 2010 at 10:32 PM, Alvaro Herrera <alvherre@commandprompt.co= m> wrote: > > Tom Lane ... --=20 =F3 =D5=D7=C1=D6=C5=CE=C9=C5=CD =EF=CC=C5=C7 =F3=C5=D2=CF=D7