Thread: Alter Table vs. Rename/Create/Drop table with plpgsql functions.
Perhaps I am just just a bit anal on this but some columns I
really like to have as the last columns of a table. (usually
last_modby, last_modtime, type fields) Thus when I need to add a
column to a table I am not happy just adding the column. Instead I
go a little insane and rename the table, create the new table, copy
the data into the new table (with any require manipulations), change
all foreign key constraints to point to the new table, rebuild
plpgsql functions the user that table and finally drop the old table.
The problem is the second to last. (rebuild plpgsql function)
These function are getting a little harder to find. This DB has
hundreds if not thousands of functions and some of my table names are
a bit common. (e.g. item) My first of all I would really like a way
to tell postgresql to un cache all functions and let the cache
rebuild as need be. I imagine restarting postgresql would do this,
but in my particular situation that has its' own problems associated
with it.
Secondly does anyone know a way to insert a column instead of
appending a column to a table. That way I could just avoid the
entire mess.
Thanks
Robert Perry
Robert,
If you have the luxury of taking the production db offline for a few minutes
or if you're just making changes in a test environment, this process works:
given:
create table test1 (
id integer,
txt1 text,
txt2 text
);
execute at command line:
pg_dump [dbname]> backups/renametable.sql
use your favorite text editor to change the above create table statement in
backups/renametable.sql to:
create table test1 (
id integer,
txt0 text,
txt1 text,
txt2 text
);
execute at command line:
dropdb [dbname]; createdb [dbname]; cat backups/renametable.sql | psql
it's not the most graceful of methods, but it works.
Mark
On Monday 01 August 2005 12:06 pm, Robert Perry wrote:
> Perhaps I am just just a bit anal on this but some columns I
> really like to have as the last columns of a table. (usually
> last_modby, last_modtime, type fields) Thus when I need to add a
> column to a table I am not happy just adding the column. Instead I
> go a little insane and rename the table, create the new table, copy
> the data into the new table (with any require manipulations), change
> all foreign key constraints to point to the new table, rebuild
> plpgsql functions the user that table and finally drop the old table.
>
> The problem is the second to last. (rebuild plpgsql function)
> These function are getting a little harder to find. This DB has
> hundreds if not thousands of functions and some of my table names are
> a bit common. (e.g. item) My first of all I would really like a way
> to tell postgresql to un cache all functions and let the cache
> rebuild as need be. I imagine restarting postgresql would do this,
> but in my particular situation that has its' own problems associated
> with it.
>
> Secondly does anyone know a way to insert a column instead of
> appending a column to a table. That way I could just avoid the
> entire mess.
>
> Thanks
> Robert Perry
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
> > Secondly does anyone know a way to insert a column instead of > appending a column to a table. That way I could just avoid the > entire mess. > http://archives.postgresql.org/pgsql-hackers/2004-09/msg00102.php -- Regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
Thanks for the suggestion. Dump/Reload the DB I am really not
going to be able to do. Also I am going to be doing this to several
copies of similar databases so vi is out, but that is nothing that a
nice script can't overcome. I think that the down time would just be
to much. Also because I am going to be running it on multiple
datbases I was hoping to limit it to a single script ran inside of
psql, as I already have a system in place to run given sql scripts on
each databases.
Other wise it is a very good suggestion that would same my much
trouble. I may still come back to it.
Thanks
Robert Perry
On Aug 1, 2005, at 12:48 PM, Mark R. Dingee wrote:
> Robert,
>
> If you have the luxury of taking the production db offline for a
> few minutes
> or if you're just making changes in a test environment, this
> process works:
>
> given:
> create table test1 (
> id integer,
> txt1 text,
> txt2 text
> );
>
> execute at command line:
> pg_dump [dbname]> backups/renametable.sql
>
> use your favorite text editor to change the above create table
> statement in
> backups/renametable.sql to:
>
> create table test1 (
> id integer,
> txt0 text,
> txt1 text,
> txt2 text
> );
>
> execute at command line:
> dropdb [dbname]; createdb [dbname]; cat backups/renametable.sql | psql
>
> it's not the most graceful of methods, but it works.
>
> Mark
>
>
> On Monday 01 August 2005 12:06 pm, Robert Perry wrote:
>
>> Perhaps I am just just a bit anal on this but some columns I
>> really like to have as the last columns of a table. (usually
>> last_modby, last_modtime, type fields) Thus when I need to add a
>> column to a table I am not happy just adding the column. Instead I
>> go a little insane and rename the table, create the new table, copy
>> the data into the new table (with any require manipulations), change
>> all foreign key constraints to point to the new table, rebuild
>> plpgsql functions the user that table and finally drop the old table.
>>
>> The problem is the second to last. (rebuild plpgsql function)
>> These function are getting a little harder to find. This DB has
>> hundreds if not thousands of functions and some of my table names are
>> a bit common. (e.g. item) My first of all I would really like a way
>> to tell postgresql to un cache all functions and let the cache
>> rebuild as need be. I imagine restarting postgresql would do this,
>> but in my particular situation that has its' own problems associated
>> with it.
>>
>> Secondly does anyone know a way to insert a column instead of
>> appending a column to a table. That way I could just avoid the
>> entire mess.
>>
>> Thanks
>> Robert Perry
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 9: In versions below 8.0, the planner will ignore your desire to
>> choose an index scan if your joining column's datatypes do not
>> match
>>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
Not that happiest of news, but very helpful to actually know it. Thanks On Aug 1, 2005, at 1:12 PM, Jaime Casanova wrote: >> >> Secondly does anyone know a way to insert a column instead of >> appending a column to a table. That way I could just avoid the >> entire mess. >> >> > > http://archives.postgresql.org/pgsql-hackers/2004-09/msg00102.php > > > -- > Regards, > Jaime Casanova > (DBA: DataBase Aniquilator ;) >
> > Secondly does anyone know a way to insert a column instead of > appending a column to a table. That way I could just avoid the > entire mess. > http://archives.postgresql.org/pgsql-hackers/2004-09/msg00102.php -- Regards, Jaime Casanova (DBA: DataBase Aniquilator ;) ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend