pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedAdding same column to several tables.
3+ messages / 3 participants
[nested] [flat]
* Adding same column to several tables.
@ 2016-06-16 08:19 Łukasz Brodziak <l.brodziak@outlook.com>
0 siblings, 2 replies; 3+ messages in thread
From: Łukasz Brodziak @ 2016-06-16 08:19 UTC (permalink / raw)
To: pgsql-admin
Hello,
I have a function to add column to all tables from the list:do$$declare relnam pg_class.relname%TYPE; kur cursor for select c.relname from pg_class as c inner join pg_attribute as a on a.attrelid = c.oid where a.attname = 'some_id' and c.relkind = 'r';begin open kur; LOOP Fetch kur into relnam; exit when not found; EXECUTE 'ALTER TABLE '|| relnam|| ' add column another_id integer default -1'; END LOOP; close kur;END;$$
I have 22 table names returned by the query used by cursor. Whe I run the function it executes for 1.5 minutes. Is there a way of doing it faster? =
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Adding same column to several tables.
@ 2016-06-16 20:28 David G. Johnston <david.g.johnston@gmail.com>
parent: Łukasz Brodziak <l.brodziak@outlook.com>
1 sibling, 0 replies; 3+ messages in thread
From: David G. Johnston @ 2016-06-16 20:28 UTC (permalink / raw)
To: Łukasz Brodziak <l.brodziak@outlook.com>; +Cc: pgsql-admin
On Thu, Jun 16, 2016 at 4:19 AM, Łukasz Brodziak <l.brodziak@outlook.com>
wrote:
> Hello,
>
> I have a function to add column to all tables from the list:
> do
> $$
> declare
> relnam pg_class.relname%TYPE;
> kur cursor for select c.relname
> from pg_class as c
> inner join pg_attribute as a on a.attrelid = c.oid
> where a.attname = 'some_id' and c.relkind = 'r';
> begin
> open kur;
> LOOP
> Fetch kur into relnam;
> exit when not found;
> EXECUTE 'ALTER TABLE '|| relnam|| ' add column another_id integer
> default -1';
> END LOOP;
> close kur;
> END;
> $$
>
> I have 22 table names returned by the query used by cursor. Whe I run the
> function it executes for 1.5 minutes. Is there a way of doing it faster?
>
I suspect your default is the cause of the problem.
David J.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Adding same column to several tables.
@ 2016-06-17 07:58 hubert depesz lubaczewski <depesz@depesz.com>
parent: Łukasz Brodziak <l.brodziak@outlook.com>
1 sibling, 0 replies; 3+ messages in thread
From: hubert depesz lubaczewski @ 2016-06-17 07:58 UTC (permalink / raw)
To: Łukasz Brodziak <l.brodziak@outlook.com>; +Cc: pgsql-admin
On Thu, Jun 16, 2016 at 10:19:10AM +0200, Łukasz Brodziak wrote:
> Hello,
> I have a function to add column to all tables from the
> list:do$$declare relnam pg_class.relname%TYPE; kur cursor for
> select c.relname from pg_class as c inner join pg_attribute as
> a on a.attrelid = c.oid where a.attname = 'some_id' and c.relkind
> = 'r';begin open kur; LOOP Fetch kur into relnam; exit when
> not found; EXECUTE 'ALTER TABLE '|| relnam|| ' add column
> another_id integer default -1'; END LOOP; close kur;END;$$
> I have 22 table names returned by the query used by cursor. Whe I run
> the function it executes for 1.5 minutes. Is there a way of doing it
> faster?
Two problems:
1. you're adding column with default value, which means it has to
rewrite the table, to set value for this column is all rows to -1.
2. you're adding column to 22 tables, in single transaction. this means
it will be prone to deadlocks.
it would be better to write simple shell script to add these columns,
one per connection, in parallel.
Best regards,
depesz
--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2016-06-17 07:58 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-06-16 08:19 Adding same column to several tables. Łukasz Brodziak <l.brodziak@outlook.com>
2016-06-16 20:28 ` David G. Johnston <david.g.johnston@gmail.com>
2016-06-17 07:58 ` hubert depesz lubaczewski <depesz@depesz.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox