Postgres Pro
Downloads
Home   >   mailing lists

Thread: Dump ALTER TABLE/SET STORAGE in pg_dump

Dump ALTER TABLE/SET STORAGE in pg_dump

From
"Christopher Kings-Lynne"
Date:
This patch make pg_dump properly dump ALTER TABLE/SET STORAGE statements.
It has been tested only against CVS backend, however.  Some checking of the
changes to the SQL to retrieve attributes for older versions of Postgres is
probably wise.  Also, please make sure that I have mapped the storage types
to the correct storage names, as this is relatively poorly documented.

I think that this patch might need to be considered for back-porting to
7.3.3 since at the moment, people will be losing valuable information after
upgrades.

Example output:

test=# create table test (a text, b text, c text, d text);
CREATE TABLE
test=# alter table test alter a set storage plain;
ALTER TABLE
test=# alter table test alter a set storage external;
ALTER TABLE
test=# alter table test alter a set storage plain;
ALTER TABLE
test=# alter table test alter b set storage extended;
ALTER TABLE
test=# alter table test alter c set storage external;
ALTER TABLE
test=# alter table test alter d set storage main;
ALTER TABLE
test=# alter table test alter b set statistics 1000;
ALTER TABLE
test=# alter table test alter a set statistics 55;
ALTER TABLE
test=# \q

Will dump:

CREATE TABLE test (
    a text,
    b text,
    c text,
    d text
);
ALTER TABLE ONLY test ALTER COLUMN a SET STATISTICS 55;
ALTER TABLE ONLY test ALTER COLUMN a SET STORAGE PLAIN;
ALTER TABLE ONLY test ALTER COLUMN b SET STATISTICS 1000;
ALTER TABLE ONLY test ALTER COLUMN c SET STORAGE EXTERNAL;
ALTER TABLE ONLY test ALTER COLUMN d SET STORAGE MAIN;

Chris

Re: Dump ALTER TABLE/SET STORAGE in pg_dump

From
Bruce Momjian
Date:
Your patch has been added to the PostgreSQL unapplied patches list at:

    http://momjian.postgresql.org/cgi-bin/pgpatches

I will try to apply it within the next 48 hours.

---------------------------------------------------------------------------


Christopher Kings-Lynne wrote:
> This patch make pg_dump properly dump ALTER TABLE/SET STORAGE statements.
> It has been tested only against CVS backend, however.  Some checking of the
> changes to the SQL to retrieve attributes for older versions of Postgres is
> probably wise.  Also, please make sure that I have mapped the storage types
> to the correct storage names, as this is relatively poorly documented.
>
> I think that this patch might need to be considered for back-porting to
> 7.3.3 since at the moment, people will be losing valuable information after
> upgrades.
>
> Example output:
>
> test=# create table test (a text, b text, c text, d text);
> CREATE TABLE
> test=# alter table test alter a set storage plain;
> ALTER TABLE
> test=# alter table test alter a set storage external;
> ALTER TABLE
> test=# alter table test alter a set storage plain;
> ALTER TABLE
> test=# alter table test alter b set storage extended;
> ALTER TABLE
> test=# alter table test alter c set storage external;
> ALTER TABLE
> test=# alter table test alter d set storage main;
> ALTER TABLE
> test=# alter table test alter b set statistics 1000;
> ALTER TABLE
> test=# alter table test alter a set statistics 55;
> ALTER TABLE
> test=# \q
>
> Will dump:
>
> CREATE TABLE test (
>     a text,
>     b text,
>     c text,
>     d text
> );
> ALTER TABLE ONLY test ALTER COLUMN a SET STATISTICS 55;
> ALTER TABLE ONLY test ALTER COLUMN a SET STORAGE PLAIN;
> ALTER TABLE ONLY test ALTER COLUMN b SET STATISTICS 1000;
> ALTER TABLE ONLY test ALTER COLUMN c SET STORAGE EXTERNAL;
> ALTER TABLE ONLY test ALTER COLUMN d SET STORAGE MAIN;
>
> Chris
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Dump ALTER TABLE/SET STORAGE in pg_dump

From
Bruce Momjian
Date:
Patch applied.  Thanks.

Backpatching seems too risky.  Other comments?

---------------------------------------------------------------------------


Christopher Kings-Lynne wrote:
> This patch make pg_dump properly dump ALTER TABLE/SET STORAGE statements.
> It has been tested only against CVS backend, however.  Some checking of the
> changes to the SQL to retrieve attributes for older versions of Postgres is
> probably wise.  Also, please make sure that I have mapped the storage types
> to the correct storage names, as this is relatively poorly documented.
>
> I think that this patch might need to be considered for back-porting to
> 7.3.3 since at the moment, people will be losing valuable information after
> upgrades.
>
> Example output:
>
> test=# create table test (a text, b text, c text, d text);
> CREATE TABLE
> test=# alter table test alter a set storage plain;
> ALTER TABLE
> test=# alter table test alter a set storage external;
> ALTER TABLE
> test=# alter table test alter a set storage plain;
> ALTER TABLE
> test=# alter table test alter b set storage extended;
> ALTER TABLE
> test=# alter table test alter c set storage external;
> ALTER TABLE
> test=# alter table test alter d set storage main;
> ALTER TABLE
> test=# alter table test alter b set statistics 1000;
> ALTER TABLE
> test=# alter table test alter a set statistics 55;
> ALTER TABLE
> test=# \q
>
> Will dump:
>
> CREATE TABLE test (
>     a text,
>     b text,
>     c text,
>     d text
> );
> ALTER TABLE ONLY test ALTER COLUMN a SET STATISTICS 55;
> ALTER TABLE ONLY test ALTER COLUMN a SET STORAGE PLAIN;
> ALTER TABLE ONLY test ALTER COLUMN b SET STATISTICS 1000;
> ALTER TABLE ONLY test ALTER COLUMN c SET STORAGE EXTERNAL;
> ALTER TABLE ONLY test ALTER COLUMN d SET STORAGE MAIN;
>
> Chris
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073