Начальная страница › Форумы › PostgreSQL › Вопросы по SQL и другие аспекты работы PostgreSQL

Наследование и уникальные индексы

Posted Май 6th, 2009 by blow
in

Добрый день.
У меня следующая проблема: есть 3 таблицы, 1 родитель и 2 ее наследника.
Наследники содержат дополнительные поля. Родитель не хранит данные, а существует как бы для обобщения наследников.
В дочерних таблицах мне нужно сделать уникальный индекс по столбцу, который наследуется от родительской таблицы, однако сделать это не получается, т.к. индексы не наследуются.

Возможно ли сделать так, чтобы вставка происходила как бы в родительскую таблицу (INSERT INTO PARENT), а физически данные ложились в одну из дочерних по какому либо условию (например имя дочерней таблицы в несуществующем поле)?
Или написать триггер, который будет проверять уникальность?

‹ Не выполняется запрос COPY Загрузка КЛАДР в PostgreSQL ›

Опции просмотра комментариев

Выберите предпочитаемый вами способ показа комментариев и нажмите "Сохранить настройки" для активации изменений.

по поводу

On Май 6th, 2009 msr says:

по поводу вставки - родительская таблица тут не при чем, нужно сделать вьюшку и вставлять в нее, а уже в правиле будет определяться, в какую таблицу вставлять

А можно немного поподробнее?

On Май 7th, 2009 blow says:

Вот уж не знал, что во вьюшки можно вставлять данные )))
А можно немного поподробнее по поводу вставки - какие поля мне нужно выводить во вьюшке? Достаточно ли будет только тех полей, которые есть в родительской таблице или в нее нужно вынести абсолютно все поля из всех наследуемых таблиц? И собственно, достаточно ли будет в таком случае уникального индекса на родительской таблице (насколько я понимаю, вьюшка будет основываться на ней) ?

вот

On Май 7th, 2009 msr says:

вот например

CREATE TABLE "public"."foo_main" (
  "id" INTEGER
);
 
CREATE TABLE "public"."foo1" (
  "caption" TEXT, 
  "num" INTEGER
) INHERITS ("public"."foo_main");
 
CREATE TABLE "public"."foo2" (
  "caption" TEXT
) INHERITS ("public"."foo_main");
 
CREATE VIEW "public"."v_foo_main" (
    id,
    caption,
    num)
AS
SELECT foo_main.id, NULL::text AS caption, NULL::integer AS num
FROM foo_main;
 
 
CREATE RULE "fm1_rule" AS ON INSERT TO "public"."v_foo_main" 
WHERE NOT new.num IS NULL
DO (
INSERT INTO foo1 (id, caption, num) 
  VALUES (new.id, new.caption, new.num);
);
 
CREATE RULE "fm2_rule" AS ON INSERT TO "public"."v_foo_main" 
WHERE new.num IS NULL
DO (
INSERT INTO foo2 (id, caption) 
  VALUES (new.id, new.caption);
);
 
CREATE RULE "fm_rule" AS ON INSERT TO "public"."v_foo_main" 
DO INSTEAD NOTHING;
--------------------------------------
INSERT INTO v_foo_main VALUES(1,'sdfs',NULL);
INSERT INTO v_foo_main VALUES(2,'ddd',5);
 
SELECT * FROM foo1;
--id	caption	num
--2	ddd	5
SELECT * FROM foo2;
--id	caption
--1	sdfs
SELECT * FROM foo_main
--id
--2
--1

Спасибо!

On Май 8th, 2009 blow says:

Огромное спасибо за пример, после выходных попробую.
Т.е.,судя по примеру, все таки во вьюшку нужно вынести ВСЕ поля из ВСЕХ подчиненных таблиц? (Ну или по крайней мере те, которые будут использоваться при вставке.)

И обязательно ли использовать NULL для тех полей, которые не будут вставляться?
Я так понимаю, что не обязательно (к сожалению сейчас проверить не могу), а было добавлено для наглядности.

И м.б. более правильно было в правилах привязываться не на наличие-отсутствие поля (по мере развития проекта поля могут существенно поменяться), а напрямую указывать имя таблицы (м.б. это и несколько менее удобно, но ИМХО надежнее)?

эх, молодежь

On Май 9th, 2009 pwlad says:

Пример интересный, и концепция наследования кажется столь привлекательной. Надо будет попробовать на практике.
Но всё же думаю, что классику списывать со счетов рановато. Вот как эта задача решается классически (правда, не буквально для спрашивающего):

CREATE TABLE foo_main ( fooid INTEGER );
CREATE TABLE foo1 ( foo1id INTEGER, caption TEXT, num INTEGER );
CREATE TABLE foo2 ( foo2id INTEGER, caption TEXT );
ALTER TABLE foo_main ADD CONSTRAINT pk_foo_main PRIMARY KEY (fooid);
ALTER TABLE foo1 ADD CONSTRAINT fk_foo1_foo FOREIGN KEY (foo1id) REFERENCES foo_main(fooid);
ALTER TABLE foo2 ADD CONSTRAINT fk_foo2_foo FOREIGN KEY (foo2id) REFERENCES foo_main(fooid);

Красота! Интересно было бы потестировать по скорости-надёжности этих подходов.

P.S. насчет последнего "fm_rule" не понял. можно пояснить?

И Вам спасибо

On Май 11th, 2009 blow says:

И Вам спасибо за совет.
Я в принципе на данный момент и склоняюсь к этой идее, но всегда хочется пробовать что-то новое, развиваться.
Если бы Postgres предоставлял более гибкие возможности наследования, наверное я бы все-таки ими воспользовался. А так скорее всего прийдется работать "по старинке".
Загвоздка у меня в том, что на самом деле наследуемых таблиц будет не 1 и не 2 уровня, а кое-где будет доходить до 4х - 5ти, хочу полноценно реализовать "table-per-class", а при составлении схемы классов абстракции были вынесены по максимуму. Но это так, к слову.
Судя по всему оптимально будет последовать советам использовать классическую схему и использовать вьюшки для вставок. Т.е. создаю вьюшку, которая объединит всю иерархию в одну таблицу и производить уже вставку в нее, это позволит абстрагироваться от структуры БД и в случае каких-либо изменений (например появится возможность напрямую наследовать уникальные индексы, которые будут действовать для всей иерархии) не прийдется много переписывать.
Надеюсь этот подход не будет сильно ограничивать быстродействие. Или у кого-нибудь есть еще предложения?

P.S. А кто нибудь вообще пользовался наследованием? В данном виде оно подходит для решения каких-либо задач?

для правил есть

On Май 12th, 2009 msr says:

для правил есть такие прави... условия:
- если у вьюшки есть правила на изменение, то одно из них обязательно должно быть DO INSTEAD
- правило с условием не может быть DO INSTEAD
вот я и сделал 2 правила с условием и одно фиктивное правило с DO INSTEAD

Механизм наследования мне показался пока сыроват и немного недоразвит. Например внешний ключ на главную таблицу не учитывает наследованные записи.
И еще, в главной таблице первичный ключ при наследовании допускает повторение в значениях... очень странно.
Поэтому я тоже склонился в пользу "классики".

жалко

On Май 12th, 2009 pwlad says:

На самом деле я писал ответ не для того, чтоб "по рукам бить". Если задача эффективнее решается новыми методами, то ими надо воспользоваться. (и порадовать результатами сообщество).
Как разовьется механизм наследования в дальнейшем так же интересно. В данной же реализации никаких противоречий по поводу уникальности нет. Ведь родительская таблица и дочерние - всё таки разные таблицы, со своими данными (в т.ч. и уникальность своя).

я думаю, на эту

On Май 12th, 2009 msr says:

я думаю, на эту тему не мало было копий сломано.
В существующем виде механизм наследования представляется мне "вещью в себе", который существует не зависимо от собственного предназначения. Сам по себе он целостный и не противоречивый, но применить его негде.
Может быть я воспринимаю наследование в виде "наследование данных", а разработчики как "наследование атрибутов" - в последнем случае появляется хоть какой-то смысл, но тогда не надо наследовать данные или построить синтаксис команд как-то по-другому.

Не стал создавать новую тему,

On Июнь 14th, 2009 blow says:

Не стал создавать новую тему, т.к. проблема появилась при реализации предложенного выше решения.

Итого: имеем 2 таблицы (одна "наследуется" от другой) и 2 вьюшки (на родительскую таблицу и на ее наследника).

Postgres позволяет производить вставки/изменения прямо во вьюшки, и грех было бы этим не воспользоваться ))

Но возникла небольшая трабла. Банальная ошибка синтаксиса. Собственно - вот код (пример тестовый)

CREATE TABLE test_child_tab(
id bigint NOT NULL,
child_attr varchar(1) NOT NULL
);
 
ALTER TABLE ONLY test_child_tab ADD CONSTRAINT test_child_tab_pk PRIMARY KEY (id);
 
CREATE TABLE test_parent_tab(
id bigint NOT NULL,
parent_attr varchar(1) NOT NULL
);
 
ALTER TABLE ONLY test_parent_tab ADD CONSTRAINT test_parent_tab_pk PRIMARY KEY (id);
 
ALTER TABLE ONLY test_child_tab ADD CONSTRAINT test_child_tab_parent_fk FOREIGN KEY(id) REFERENCES test_parent_tab ON DELETE CASCADE;
 
CREATE OR REPLACE VIEW test_parent AS SELECT * FROM test_parent_tab;
CREATE OR REPLACE RULE test_parent_insert AS ON INSERT TO test_parent DO INSTEAD INSERT INTO test_parent_tab VALUES(NEW.id,NEW.parent_attr);
CREATE OR REPLACE RULE test_parent_delete AS ON DELETE TO test_parent DO INSTEAD DELETE FROM test_parent_tab WHERE id=OLD.id; 
 
CREATE OR REPLACE VIEW test_child AS SELECT v1.*,t1.child_attr FROM test_child_tab t1, test_parent v1 WHERE t1.id=v1.id;
CREATE OR REPLACE RULE test_child_insert AS ON INSERT TO test_child DO INSTEAD (
INSERT INTO test_parent VALUES(NEW.id,NEW.parent_attr);
INSERT INTO test_child_tab VALUES(NEW.id,NEW.child_attr);
); 
CREATE OR REPLACE RULE test_child_delete AS ON DELETE TO test_child DO INSTEAD DELETE FROM test_parent WHERE id=OLD.id;

Не создается правило

CREATE OR REPLACE RULE test_child_insert AS ON INSERT TO test_child DO INSTEAD (
INSERT INTO test_parent VALUES(NEW.id,NEW.parent_attr);
INSERT INTO test_child_tab VALUES(NEW.id,NEW.child_attr);
);

Вылетает с ошибкой: Ошибка синтаксиса в конце ввода.

Сразу скажу, что пробовал данное правило разнести на 2, правила создавались нормально. Но возникала проблема при вставке. Почему-то вставка сначала происходила в подчиненную таблицу и вылетало по констрэйнту foreign key. При чем независимо от порядка добавления правил.

Немножко сумбурно, но думаю понятно ))
заранее спасибо!

Ой, извиняюсь

On Июнь 14th, 2009 blow says:

Ой, извиняюсь )))

Оказывается, проблема не в Postgres. Если данный код ввести в консоли постгрес - все нормально. А ошибка возникает при попытке провести данный код из eclipse.

Опции просмотра комментариев

Выберите предпочитаемый вами способ показа комментариев и нажмите "Сохранить настройки" для активации изменений.

Back to top

(С) Виктор Вислобоков, 2008-2023