A problem in inheritance
| От | Talha Khan |
|---|---|
| Тема | A problem in inheritance |
| Дата | |
| Msg-id | f80885fc0706201053p5cf697cbhb0c97b04abdd5c4f@mail.gmail.com обсуждение исходный текст |
| Ответы |
Re: A problem in inheritance
(Jim Nasby <decibel@decibel.org>)
|
| Список | pgsql-general |
Hi Guyz,
I need some help in an inheritance issue .
The scenario is as follows :
THE SAMPLE DDL:
CREATE TABLE account_login
(
account_id int4 NOT NULL,
account_login_time timestamptz NOT NULL DEFAULT now(),
ip_address varchar(32) NOT NULL,
originating_source varchar(32) NOT NULL DEFAULT 'game'::character
varying
)
WITHOUT OIDS;
CREATE OR REPLACE RULE account_login_no_delete AS ON DELETE TO
account_login DO INSTEAD NOTHING;
CREATE OR REPLACE RULE account_login_no_update AS ON UPDATE TO
account_login DO INSTEAD NOTHING;
-- child partition
CREATE TABLE account_login_200705_2
(
-- Inherited: account_id int4 NOT NULL,
-- Inherited: account_login_time timestamptz NOT NULL DEFAULT now(),
-- Inherited: ip_address varchar(32) NOT NULL,
-- Inherited: originating_source varchar(32) NOT NULL DEFAULT
'game'::character varying,
) INHERITS (account_login)
WITHOUT OIDS;
CREATE OR REPLACE RULE account_login_no_delete_200705_2 AS ON DELETE
TO account_login_200705_2 DO INSTEAD NOTHING;
CREATE OR REPLACE RULE account_login_no_update_200705_2 AS ON UPDATE
TO account_login_200705_2 DO INSTEAD NOTHING;
-- set up the redirection to the partition
CREATE OR REPLACE RULE account_login_insert_200705_2 AS
ON INSERT TO account_login
WHERE new.account_login_time >= '2007-05-16 00:00:00+00'::timestamp
with time zone AND new.account_login_time < '2007-06-01
00:00:00+00'::timestamp with time zone DO INSTEAD INSERT INTO
account_login_200705_2 (account_id, account_login_time, ip_address,
originating_source)
VALUES (new.account_id, new.account_login_time, new.ip_address,
new.originating_source);
-- seed the partition table with rows
insert into account_login values (1, '20070522 5:00+00', '1.1.1.1',
'developer');
insert into account_login values (2, '20070522 6:00+00', '1.1.1.1',
'developer');
insert into account_login values (3, '20070522 7:00+00', '1.1.1.1',
'developer');
insert into account_login values (4, '20070522 8:00+00', '1.1.1.1',
'developer');
THE ACTUAL TEST:
DROP RULE account_login_no_update ON account_login;
UPDATE account_login set originating_source = 'xxx';
Now the update should not effect the child table but it does, evident
from the output of the following query:
SELECT * FROM account_login_200705_2;
TEST # 2:
I replicated the same scenario and created just one more rule on the
child table
CREATE OR REPLACE RULE account_login_no_insert_200705_2 AS ON INSERT
TO account_login_200705_2 DO INSTEAD NOTHING;
Based on the output from the previous scenario i thought that an
insert into the parent table i.e account_login should go into the
child table i.e account_login_200705_2 but the insert does not go and
the on insert do nothing rule on the child table does affect.
The basic problem is that the on update do nothing rule is not working
on the child table when an update is done to the parent table and an
on insert do nothing rule is working on the child table.
Please an guidance in this regard would be really appreciated.
Regards,
Talha Amjad
I need some help in an inheritance issue .
The scenario is as follows :
THE SAMPLE DDL:
CREATE TABLE account_login
(
account_id int4 NOT NULL,
account_login_time timestamptz NOT NULL DEFAULT now(),
ip_address varchar(32) NOT NULL,
originating_source varchar(32) NOT NULL DEFAULT 'game'::character
varying
)
WITHOUT OIDS;
CREATE OR REPLACE RULE account_login_no_delete AS ON DELETE TO
account_login DO INSTEAD NOTHING;
CREATE OR REPLACE RULE account_login_no_update AS ON UPDATE TO
account_login DO INSTEAD NOTHING;
-- child partition
CREATE TABLE account_login_200705_2
(
-- Inherited: account_id int4 NOT NULL,
-- Inherited: account_login_time timestamptz NOT NULL DEFAULT now(),
-- Inherited: ip_address varchar(32) NOT NULL,
-- Inherited: originating_source varchar(32) NOT NULL DEFAULT
'game'::character varying,
) INHERITS (account_login)
WITHOUT OIDS;
CREATE OR REPLACE RULE account_login_no_delete_200705_2 AS ON DELETE
TO account_login_200705_2 DO INSTEAD NOTHING;
CREATE OR REPLACE RULE account_login_no_update_200705_2 AS ON UPDATE
TO account_login_200705_2 DO INSTEAD NOTHING;
-- set up the redirection to the partition
CREATE OR REPLACE RULE account_login_insert_200705_2 AS
ON INSERT TO account_login
WHERE new.account_login_time >= '2007-05-16 00:00:00+00'::timestamp
with time zone AND new.account_login_time < '2007-06-01
00:00:00+00'::timestamp with time zone DO INSTEAD INSERT INTO
account_login_200705_2 (account_id, account_login_time, ip_address,
originating_source)
VALUES (new.account_id, new.account_login_time, new.ip_address,
new.originating_source);
-- seed the partition table with rows
insert into account_login values (1, '20070522 5:00+00', '1.1.1.1',
'developer');
insert into account_login values (2, '20070522 6:00+00', '1.1.1.1',
'developer');
insert into account_login values (3, '20070522 7:00+00', '1.1.1.1',
'developer');
insert into account_login values (4, '20070522 8:00+00', '1.1.1.1',
'developer');
THE ACTUAL TEST:
DROP RULE account_login_no_update ON account_login;
UPDATE account_login set originating_source = 'xxx';
Now the update should not effect the child table but it does, evident
from the output of the following query:
SELECT * FROM account_login_200705_2;
TEST # 2:
I replicated the same scenario and created just one more rule on the
child table
CREATE OR REPLACE RULE account_login_no_insert_200705_2 AS ON INSERT
TO account_login_200705_2 DO INSTEAD NOTHING;
Based on the output from the previous scenario i thought that an
insert into the parent table i.e account_login should go into the
child table i.e account_login_200705_2 but the insert does not go and
the on insert do nothing rule on the child table does affect.
The basic problem is that the on update do nothing rule is not working
on the child table when an update is done to the parent table and an
on insert do nothing rule is working on the child table.
Please an guidance in this regard would be really appreciated.
Regards,
Talha Amjad
В списке pgsql-general по дате отправления: