Re: Diffrent column ordering after dump/restore tables with INHERITS - Mailing list pgsql-bugs

From Oleg Serov
Subject Re: Diffrent column ordering after dump/restore tables with INHERITS
Date
Msg-id cec7c6df0907050228m2578ffc3w4acbe246a5bd5d1f@mail.gmail.com
Whole thread Raw
In response to Re: Diffrent column ordering after dump/restore tables with INHERITS  (Oleg Serov <serovov@gmail.com>)
List pgsql-bugs
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
>>
>

pgsql-bugs by date:

Previous
From: Oleg Serov
Date:
Subject: Re: Diffrent column ordering after dump/restore tables with INHERITS
Next
From: Andres Freund
Date:
Subject: Re: BUG #4899: Open parenthesis breaks query plan