VPF::Список таблиц и sequences - Форум программистов

Temdegon

Дата 2.9.2009, 19:05 (ссылка)

(нет голосов)

Загрузка ...

Опытный

Профиль
Группа: Участник
Сообщений: 429
Регистрация: 11.10.2008
Где: Minsk

Репутация: нет
Всего: 9

Нужен запрос, который бы возвращал следующие поля:

схема,
таблица,
имя primary key поля,
Default Value для этого поля (типа nextval('table_id_seq'::regclass)),
Y - если в той же схеме есть sequence с именем talbename_pkname_seq или N, если нет такого.

В документации нифига не понятно. Кое-как написал запрос без последнего поля, но он получился просто огромный и выполняется очень долго.
Помогите плиз, кто хорошо разбирается во всех этих pg_class, pg_catalog, pg_attribute и т.п. страшных вещах.

Temdegon

Дата 3.9.2009, 00:43 (ссылка)

(нет голосов)

Загрузка ...

Опытный

Профиль
Группа: Участник
Сообщений: 429
Регистрация: 11.10.2008
Где: Minsk

Репутация: нет
Всего: 9

В общем прелюдия такая:
есть сервер с парой тысяч таблиц. Есть пара сотен точно таких же серверов, с такой же структурой БД. Все изменения в структуре прогоняются на всех серверах. Время от времени структура БД синхронизируется на всех серверах. Но не все идеально и не все делается добросовестно. Синхронизатор не корректно работает с сиквенсами. ПОставили задачу - написать ПХП-скрипт, который бы находил потенциальные проблемы с default values для primary keys.
Список таблиц получить не проблема. Имя поля-primary key для таблицы тоже кое-как разобрался как получать. Дефолт велью для поля тоже получил кое-как. Отдельно список сиквенсов получать умею. Но как связать его со списком таблиц - ума не приложу.
Еще столкнулся с проблемой удаления sequence. Убираю default value из поля, которое юзает сиквенс, а дропнуть сиквенс все равно не могу - пишет, что есть зависимость. Как отвязать сиквенс от таблицы, что бы дропнуть.
в синтаксисе ALTER SEQUENCE есть опция OWNED BY TABLE|NONE. Но вот поменять это свойство че-то не получается.

Temdegon

Дата 3.9.2009, 19:22 (ссылка)

(нет голосов)

Загрузка ...

Опытный

Профиль
Группа: Участник
Сообщений: 429
Регистрация: 11.10.2008
Где: Minsk

Репутация: нет
Всего: 9

Собственно вот, кое-как написал:

Код


SELECT nspname,
       relname,
       a.attname,
       t.typname,
       ss.t1,
       (
        SELECT adsrc
        FROM pg_attrdef d
        WHERE d.adrelid = a.attrelid AND
              d.adnum = a.attnum
       ) AS def
FROM pg_attribute a
     JOIN pg_type t ON a.atttypid = t.oid
     JOIN pg_class as c ON attrelid = c.oid
     JOIN pg_namespace as n ON n.oid = c.relnamespace
     LEFT JOIN 
     (
      SELECT relname AS t1,
             nspname AS t2
      FROM pg_class AS c
           JOIN pg_namespace AS n ON relnamespace = n.oid
      WHERE relkind = 'S'
     ) AS ss ON ss.t1 ILIKE(relname || '_' || attname || '_seq%') AND ss.t2 = nspname
WHERE NOT attisdropped AND
      relname NOT ILIKE('backup') AND
      attnum > 0 AND
      (
       SELECT indisprimary
       FROM pg_index i,
            pg_class ic,
            pg_attribute ia
       WHERE i.indrelid = a.attrelid AND
             i.indexrelid = ic.oid AND
             ic.oid = ia.attrelid AND
             ia.attname = a.attname AND
             indisprimary IS NOT NULL
       ORDER BY indisprimary DESC
       LIMIT 1
      ) = true AND
      attname NOT IN (
                      SELECT (
                              SELECT attname
                              FROM pg_attribute
                              WHERE pg_constraint.conrelid = pg_attribute.attrelid AND
                                    conkey [ 1 ] = attnum
                             )
                      FROM pg_constraint,
                           pg_class,
                           pg_namespace,
                           pg_class as pg_classf,
                           pg_namespace as pg_namespacef
                      WHERE pg_constraint.conrelid = pg_class.oid AND
                            pg_constraint.confrelid = pg_classf.oid AND
                            pg_classf.relnamespace = pg_namespacef.oid AND
                            pg_class.relnamespace = pg_namespace.oid AND
                            contype = 'f'
      )

Выводит все что нужно. Выглядит страшно, но работает.
Исполняется достаточно долго при большом кол-ве таблиц.
Если кто-то видит пути оптимизации этого запроса - буду очень признателен.

0 Пользователей читают эту тему (0 Гостей и 0 Скрытых Пользователей)
0 Пользователей:
« Предыдущая тема \| PostgreSQL \| Следующая тема »