Skip site navigation (1) Skip section navigation (2)

Problem with RULE system with condition between 3 tables.

From: "Vadim I(dot) Passynkov" <vip(at)axxent(dot)ca>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Problem with RULE system with condition between 3 tables.
Date: 2000-11-14 22:24:58
Message-ID: 3A11BBBA.7BAE69B2@axxent.ca (view raw or flat)
Thread:
Lists: pgsql-bugs
Hi All,

I create rule with condition between 2 different tables, it's working
OK.
sysname - pkey in routers_snmp and routers_name tables. 
sysname,index - pkey in interfaces_name table.

CREATE RULE "send_signal_if_update_interfaces_name" AS ON UPDATE TO
interfaces_name
WHERE
old.monitor_ov_enable <> new.monitor_ov_enable AND
old.sysname = routers_snmp.sysname AND
routers_snmp.monitor_ov_enable = 't'
DO SELECT send_signal ();

EXPLAIN UPDATE interfaces_name SET monitor_ov_enable = 't' where sysname
= 'virgin.tor' and index = 1 ;
NOTICE:  QUERY PLAN:

Nested Loop  (cost=0.00..10.21 rows=4 width=24)
  ->  Index Scan using interfaces_name_pkey on interfaces_name 
(cost=0.00..2.02 rows=1 width=12)
  ->  Index Scan using routers_snmp_pkey on routers_snmp 
(cost=0.00..8.13 rows=4 width=12)

NOTICE:  QUERY PLAN:

Nested Loop  (cost=0.00..30.74 rows=836 width=95)
  ->  Index Scan using interfaces_name_pkey on interfaces_name 
(cost=0.00..2.02 rows=1 width=91)
  ->  Seq Scan on routers_snmp (cost=0.00..20.36 rows=836 width=4)

But if create same RULE between 3 different tables, it will huge timeout
with high postgres cpu load.
Nested Loop rows in 1 case - 836 in 2 case - 701404 !!!!!!!! that the
reason.
Regular SELECT, UPDATE, DELETE operations between this three tables
working fine.
What the problem?

CREATE RULE "send_signal_if_update_interfaces_name" AS ON UPDATE TO
interfaces_name
WHERE
old.monitor_ov_enable <> new.monitor_ov_enable AND
old.sysname = routers_name.sysname AND ----------------------\
routers_name.admin_status = 10 AND----------------------------- two new
with query to third table
old.sysname = routers_snmp.sysname AND
routers_snmp.monitor_ov_enable = 't'
DO SELECT send_signal ();


EXPLAIN UPDATE interfaces_name SET monitor_ov_enable = 't' where sysname
= 'virgin.tor' and index = 1 ;
NOTICE:  QUERY PLAN:

Nested Loop  (cost=0.00..17.35 rows=1 width=36)
  ->  Nested Loop  (cost=0.00..9.17 rows=1 width=24)
        ->  Index Scan using interfaces_name_pkey on interfaces_name 
(cost=0.00..2.02 rows=1 width=12)
        ->  Index Scan using routers_name_pkey on routers_name 
(cost=0.00..7.13 rows=1 width=12)
  ->  Index Scan using routers_snmp_pkey on routers_snmp 
(cost=0.00..8.13 rows=4 width=12)

NOTICE:  QUERY PLAN:

Nested Loop  (cost=0.00..21582.82 rows=701404 width=99)
  ->  Nested Loop  (cost=0.00..30.74 rows=836 width=95)
        ->  Index Scan using interfaces_name_pkey on interfaces_name 
(cost=0.00..2.02 rows=1 width=91)
        ->  Seq Scan on routers_snmp  (cost=0.00..20.36 rows=836
width=4)
  ->  Seq Scan on routers_name  (cost=0.00..17.39 rows=839 width=4)

EXPLAIN


-- 

 Vadim I. Passynkov, Axxent Corp.
 mailto:vip(at)axxent(dot)ca

pgsql-bugs by date

Next:From: Jessica YanDate: 2000-11-14 22:33:57
Subject: Fail to restore index table from pg_dumpall
Previous:From: Tom LaneDate: 2000-11-14 19:03:20
Subject: Re: Fail to restore index tables by pg_dumpall

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group