Panayotis Cosmas ([EMAIL PROTECTED]) reports a bug with a severity of 2
The lower the number the more severe it is.
Short Description
delete rules false report
Long Description
I've just upgraded my database to postgre 7.2.1 from version 7.0.1.
I noticed that ON DELETE rules for views reports always DELETE 0 where it should
report DELETE 1.
For example:
I type,
orders_db=# delete from view_orders_source where orders_id = 6533;
and postgre says,
orders_db=# DELETE 0
although the row has deleted succesfully.
When I try this on postgre 7.0.1 the reply is ok (DELETE 1).
Sample Code
CREATE TABLE "clients" (
"clients_id" int4 DEFAULT nextval('"clients_clients_id_seq"'::text) NOT NULL,
"clients_code" varchar(20) NOT NULL,
"clients_sales_group" varchar(30) NOT NULL,
"clients_name" varchar(50) NOT NULL,
"clients_address" varchar(100),
"clients_postal" varchar(7),
"clients_city" varchar(30),
"clients_main_tel" varchar(25),
"clients_other_tel" varchar(50),
"clients_fax" varchar(25),
"clients_email" varchar(50),
"clients_contact" varchar(50),
CONSTRAINT "clients_pkey" PRIMARY KEY ("clients_id")
) WITH OIDS;
GRANT ALL ON "clients" TO PUBLIC;
CREATE TABLE "orders" (
"orders_id" int4 DEFAULT nextval('"orders_orders_id_seq"'::text) NOT NULL,
"clients_id" int4 NOT NULL,
"orders_agency_name" varchar(50),
"orders_agency_address" varchar(50),
"orders_agency_city" varchar(20),
"orders_agency_tel" varchar(20),
"orders_input_date" date NOT NULL,
"orders_required_date" date,
"orders_shipment_client" varchar(50),
"orders_shipment_address" varchar(100),
"orders_shipment_city" varchar(30),
"orders_shipment_tel" varchar(25),
"orders_clients_orderid" varchar(15),
"orders_salesman" varchar(50) NOT NULL,
"orders_notes" varchar(200),
"orders_validated" bool DEFAULT '0' NOT NULL,
"orders_input_time" time,
CONSTRAINT "orders_pkey" PRIMARY KEY ("orders_id"),
CONSTRAINT "client_to_orders" FOREIGN KEY ("clients_id") REFERENCES "clients"
("clients_id") ON DELETE NO ACTION ON UPDATE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE
) WITH OIDS;
GRANT ALL ON "orders" TO PUBLIC;
CREATE VIEW "view_orders_source" AS SELECT
orders.clients_id,
clients.clients_code,
clients.clients_name,
clients.clients_address,
clients.clients_postal,
clients.clients_city,
clients.clients_main_tel,
clients.clients_fax,
clients.clients_contact,
clients.clients_sales_group,
orders.orders_id,
orders.orders_input_date,
orders.orders_input_time,
orders.orders_required_date,
orders.orders_shipment_client,
orders.orders_shipment_address,
orders.orders_shipment_city,
orders.orders_shipment_tel,
orders.orders_clients_orderid,
orders.orders_salesman, orders.
orders_notes, orders.orders_validated,
orders.orders_agency_name,
orders.orders_agency_address,
orders.orders_agency_city,
orders.orders_agency_tel
FROM (clients JOIN orders ON ((clients.clients_id = orders.clients_id)))
WHERE clients.clients_sales_group like '%'
AND
((SELECT db_user_location.db_user_place FROM db_user_location WHERE
(db_user_location.db_user_name =
current_user)) = 'aigaleo')
and orders_id between (select max(orders_id)-20 from orders) and (select
max(orders_id) from orders)
OR
clients.clients_sales_group = (SELECT db_user_location.db_user_place FROM
db_user_location WHERE
(db_user_location.db_user_name = current_user))
and orders_id between (select max(orders_id)-20 from orders) and (select
max(orders_id) from orders)
ORDER BY orders_id;
GRANT ALL ON "view_orders_source" TO PUBLIC;
create rule view_orders_source_rule_del as on delete to view_orders_source do instead
delete from orders where orders_id = OLD.orders_id;
No file was uploaded with this report
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
