Обсуждение: Adding same column to several tables.

Поиск
Список
Период
Сортировка

Adding same column to several tables.

От
Łukasz Brodziak
Дата:
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?

Re: Adding same column to several tables.

От
"David G. Johnston"
Дата:
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.
​

Re: Adding same column to several tables.

От
hubert depesz lubaczewski
Дата:
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/