Re: 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(at)lain(dot)pl> wrote:
> On Sat, 04 Jul 2009 22:10:55 +0200, Oleg Serov <serovov(at)gmail(dot)com> wrote:
>>
>> 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..
>
> 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 = 0;
for (j = 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(at)gmail(dot)com> wrote:
> 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(at)lain(dot)pl> wrote:
>> On Sat, 04 Jul 2009 22:10:55 +0200, Oleg Serov <serovov(at)gmail(dot)com> wrote:
>>>
>>> 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..
>>
>> 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(at)gmail(dot)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(at)sss(dot)pgh(dot)pa(dot)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(at)mit(dot)edu> wrote:
> On Sun, Jul 5, 2009 at 4:28 PM, Tom Lane<tgl(at)sss(dot)pgh(dot)pa(dot)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(at)mit(dot)edu> writes:
> On Sun, Jul 5, 2009 at 4:28 PM, Tom Lane<tgl(at)sss(dot)pgh(dot)pa(dot)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(at)mit(dot)edu> wrote:
> On Sun, Jul 5, 2009 at 4:28 PM, Tom Lane<tgl(at)sss(dot)pgh(dot)pa(dot)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(at)gmail(dot)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(at)mit(dot)edu> wrote:
> > On Sun, Jul 5, 2009 at 4:28 PM, Tom Lane<tgl(at)sss(dot)pgh(dot)pa(dot)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>
> >
>

--
С уважением

Олег Серов

2010/2/26 Oleg Serov <serovov(at)gmail(dot)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(at)mit(dot)edu> wrote:

> 2010/2/26 Oleg Serov <serovov(at)gmail(dot)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
>

--
С уважением

Олег Серов

Oleg Serov <serovov(at)gmail(dot)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(at)gmail(dot)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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Oleg Serov <serovov(at)gmail(dot)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
>
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
>

--
С уважением

Олег Серов

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(at)commandprompt(dot)com
> wrote:

> Tom Lane escribió:
> > Oleg Serov <serovov(at)gmail(dot)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
>

--
С уважением

Олег Серов

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(at)gmail(dot)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(at)commandprompt(dot)com>
wrote:
>
> Tom Lane ...
--
С уважением

Олег Серов