Richard Huxton wrote:
Richard Huxton wrote:
I'm putting together a small test case to see if I can reproduce your
behaviour here.
Does the attached small script misbehave in the same way as your real
data? From here it works fine when the fkey is ON ... RESTRICT.
I'm right in thinking that your "nodes" fkey is RESTRICT on update and
delete?
You script is worked fine on same database, where is difference?
-- output ---
psql xxxx xxxxx -f fkey_index_prob.sql
psql:fkey_index_prob.sql:2: NOTICE: CREATE TABLE / PRIMARY KEY will
create implicit index "testnode_pkey" for table "testnode"
CREATE TABLE
psql:fkey_index_prob.sql:3: NOTICE: CREATE TABLE will create implicit
sequence "traffic_id_seq" for serial column "traffic.id"
CREATE TABLE
INSERT 0 25
INSERT 0 9999999
ALTER TABLE
CREATE INDEX
BEGIN
QUERY
PLAN
--------------------------------------------------------------------------------------------------------------------------
Index Scan using testnode_pkey on testnode (cost=0.00..8.27 rows=1
width=6) (actual time=43.480..43.485 rows=1 loops=1)
Index Cond: (node = 9)
Trigger for constraint traffic_node_fkey: time=106.053 calls=1
Total runtime: 165.925 ms
~~~~~~~~~~~~~~~~~~~~~~~~~~ GOOD!
(4 rows)
ROLLBACK
BEGIN
psql:fkey_index_prob.sql:16: ERROR: update or delete on table
"testnode" violates foreign key constraint "traffic_node_fkey" on table
"traffic"
DETAIL: Key (node)=(11) is still referenced from table "traffic".
ROLLBACK
--- My database ---------------
CREATE TABLE nodes
(
id integer NOT NULL DEFAULT nextval('nodesidseq'::regclass),
description character varying(256),
identifier character varying(256) NOT NULL,
CONSTRAINT nodes_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);
ALTER TABLE nodes OWNER TO inms;
-- Index: "NodeIdentifierIndex"
-- DROP INDEX "NodeIdentifierIndex";
CREATE UNIQUE INDEX "NodeIdentifierIndex"
ON nodes
USING btree
(identifier);
CREATE TABLE sf_ipv4traffic
(
timeframe integer NOT NULL,
timemark timestamp with time zone NOT NULL,
node integer NOT NULL,
source_address bytea NOT NULL,
source_port integer NOT NULL,
destination_address bytea NOT NULL,
destination_port integer NOT NULL,
protocol_type integer NOT NULL,
octets_counter bigint,
packets_counter integer,
CONSTRAINT sf_ipv4traffic_pkey PRIMARY KEY (timeframe, timemark, node,
source_address, source_port, destination_address, destination_port,
protocol_type),
CONSTRAINT nodes FOREIGN KEY (node)
REFERENCES nodes (id) MATCH SIMPLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT sf_ipv4traffic_timeframe_fkey FOREIGN KEY (timeframe)
REFERENCES sf_timeframes (id) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE RESTRICT
)
WITH (OIDS=FALSE);
ALTER TABLE sf_ipv4traffic OWNER TO inms;
-- Index: fki_nodes
-- DROP INDEX fki_nodes;
CREATE INDEX fki_nodes
ON sf_ipv4traffic
USING btree
(node);
-- Index: sf_ipv4traffic_idx
-- DROP INDEX sf_ipv4traffic_idx;
CREATE INDEX sf_ipv4traffic_idx
ON sf_ipv4traffic
USING btree
(source_port, timeframe, source_address);
-- Index: sf_ipv4traffic_idx1
-- DROP INDEX sf_ipv4traffic_idx1;
CREATE INDEX sf_ipv4traffic_idx1
ON sf_ipv4traffic
USING btree
(timeframe, node, timemark);
-- Index: sf_ipv4traffic_idx3
-- DROP INDEX sf_ipv4traffic_idx3;
CREATE INDEX sf_ipv4traffic_idx3
ON sf_ipv4traffic
USING btree
(destination_address, destination_port, timeframe);
-- Index: sf_ipv4traffic_idx4
-- DROP INDEX sf_ipv4traffic_idx4;
CREATE INDEX sf_ipv4traffic_idx4
ON sf_ipv4traffic
USING btree
(protocol_type, timeframe);
Other tables definition skipped.....
--- test1.sql ---------
begin;
--set enable_seqscan to
off;
delete from decimalnodeattributes where
node=2007;
delete from stringnodeattributes where
node=2007;
delete from datenodeattributes where
node=2007;
delete from topology where fromnode=2007 or
tonode=2007;
explain analyze delete from nodes where
id=2007;
rollback;
--- output ---
inms=> \i test1.sql
BEGIN
DELETE 0
DELETE 1
DELETE 1
DELETE 2
QUERY
PLAN
-----------------------------------------------------------------------------------------------
Seq Scan on nodes (cost=0.00..1.29 rows=1 width=6) (actual
time=0.034..0.036 rows=1 loops=1)
Filter: (id = 2007)
Trigger for constraint booleannodeattributes_node_fkey: time=89.885 calls=1
Trigger for constraint datenodeattributes_node_fkey: time=0.466 calls=1
Trigger for constraint decimalnodeattributes_node_fkey: time=0.268 calls=1
Trigger for constraint node: time=369.983 calls=1
Trigger for constraint nodes: time=64278.862 calls=1
~~~~~~~~~~~~~~~~~~~~~~~~~~~ BAD.
Trigger for constraint snmp_nodes_access_nodeid_fkey: time=41.661 calls=1
Trigger for constraint stringnodeattributes_node_fkey: time=0.408 calls=1
Trigger for constraint topology_fromnode_fkey: time=0.308 calls=1
Trigger for constraint topology_tonode_fkey: time=0.277 calls=1
Total runtime: 64814.359 ms
~~~~~~~~~~~~~~~~~~~~~~~~~~~ BAD.
(12 rows)
ROLLBACK
--
__________________________________
WBR, Andrew Nesheret ICQ:10518066
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
