Обсуждение: A problem in inheritance
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
On Jun 20, 2007, at 12:53 PM, Talha Khan wrote: > 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: That's because you dropped the rule that would have affected that query. Remember that rules effectively work on the query strings themselves, so to impact that update you'd have to have a rule on account_login. No rule on a child table will matter. -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
>>to impact that update you'd have to have a rule on
>>account_login. No rule on a child table will matter.
Well i had the same perception.... In order to double check this i created an On insert do nothing rule on the child table and did an insertion to the master table i.e (account_login) but this time the On insert do nothing rule did affect and no insert was done to the child table...
Note: I did not have any on insert do nothing rule on the master table.
Regards,
Talha Amjad
>>account_login. No rule on a child table will matter.
Well i had the same perception.... In order to double check this i created an On insert do nothing rule on the child table and did an insertion to the master table i.e (account_login) but this time the On insert do nothing rule did affect and no insert was done to the child table...
Note: I did not have any on insert do nothing rule on the master table.
Regards,
Talha Amjad
On 6/23/07, Jim Nasby <decibel@decibel.org> wrote:
On Jun 20, 2007, at 12:53 PM, Talha Khan wrote:
> 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:
That's because you dropped the rule that would have affected that
query. Remember that rules effectively work on the query strings
themselves, so to impact that update you'd have to have a rule on
account_login. No rule on a child table will matter.
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
>>to impact that update you'd have to have a rule on
>>account_login. No rule on a child table will matter.
Well i had the same perception.... In order to double check this i created an On insert do nothing rule on the child table and did an insertion to the master table i.e (account_login) but this time the On insert do nothing rule did affect and no insert was done to the child table...
Note: I did not have any on insert do nothing rule on the master table.
Regards,
Talha Amjad
>>account_login. No rule on a child table will matter.
Well i had the same perception.... In order to double check this i created an On insert do nothing rule on the child table and did an insertion to the master table i.e (account_login) but this time the On insert do nothing rule did affect and no insert was done to the child table...
Note: I did not have any on insert do nothing rule on the master table.
Regards,
Talha Amjad
On 6/23/07, Jim Nasby <decibel@decibel.org> wrote:
On Jun 20, 2007, at 12:53 PM, Talha Khan wrote:
> 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:
That's because you dropped the rule that would have affected that
query. Remember that rules effectively work on the query strings
themselves, so to impact that update you'd have to have a rule on
account_login. No rule on a child table will matter.
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)