[SQL] Delete failing with -- permission denied - Mailing list pgsql-sql

Delete from table test.entities_all is failing with "permission denied for
relation". The table from which row has to be deleted, is referenced by
another table "attribute_types" with ON DELETE CASCADE.

I tried deleting the row from attribute_types table and then deleting from
test.entities_all succeed.

I am not able to understand why this delete sql is failing.

######################
Delete from table is failing with 
######################

user_test@testdbpg # delete from test.entities_all where entity_type_id =
254 AND entity_id = 20043093223;
ERROR:  permission denied for relation current_change$tmp
CONTEXT:  PL/pgSQL function test.current_change() line 11 at RETURN QUERY
SQL statement "SELECT       change_num                                          FROM test.current_change"
PL/pgSQL function test."changes_package$get_change_num"() line 5 at SQL
statement
PL/pgSQL function test."attribute_types_history$attribute_types"() line 6 at
assignment
SQL statement "DELETE FROM ONLY "test"."attribute_types" WHERE $1
OPERATOR(pg_catalog.=) "attribute_type_entity_id""
Time: 65.536 ms

#############################
Table test.entities_all has below 
#############################

Referenced by:   TABLE "test.entities_all" CONSTRAINT "entities_all_fk2" FOREIGN KEY
(owner_id) REFERENCES test.entities_all(entity_id) ON DELETE SET NULL   TABLE "test.attribute_types" CONSTRAINT
"fkey_at_attribute_entity"
FOREIGN KEY (attribute_type_entity_id) REFERENCES
test.entities_all(entity_id) ON DELETE CASCADE   TABLE "test.requests" CONSTRAINT "requests_fk2" FOREIGN KEY
(requester_entity_id) REFERENCES test.entities_all(entity_id) ON DELETE
CASCADE

#######################################
Table test.attribute_types has
#######################################

Referenced by:   TABLE "test.attributes" CONSTRAINT "fkey_attribute_type" FOREIGN KEY
(attribute_type_id) REFERENCES test.attribute_types(attribute_type_id)

###############################
Deleting each row manually 
###############################

user_test@testdbpg #begin;
BEGIN
Time: 21.895 ms
user_test@testdbpg # delete from test.attributes where
(attribute_type_id)=(1220010);
DELETE 1
Time: 24.145 ms
user_test@testdbpg # DELETE FROM ONLY "test"."attribute_types" where
(attribute_type_id)=(1220010);
DELETE 1
Time: 35.638 ms
user_test@testdbpg # delete from test.entities_all where  ( entity_type_id =
254::INTEGER AND entity_id = 20043093223::NUMERIC);
DELETE 1
Time: 56.104 ms
user_test@testdbpg # rollback;
ROLLBACK
Time: 28.266 ms
user_test@testdbpg #


###############################
Function
##############################

CREATE OR REPLACE FUNCTION test."attribute_types_history$attribute_types"()RETURNS triggerLANGUAGE plpgsql
AS $function$
DECLARE   new_change_num numeric;
BEGIN   PERFORM test.changes_package$init();   new_change_num := test.changes_package$get_change_num();   INSERT INTO
test.attribute_types_history(attribute_type_id,
 
for_entity_type_id, attribute_name, attribute_description,
attribute_type_entity_id, last_modified_by, last_modified_date,
creation_change_num, deletion_change_num)   VALUES (OLD.attribute_type_id, OLD.for_entity_type_id,
OLD.attribute_name, OLD.attribute_description, OLD.attribute_type_entity_id,
OLD.last_modified_by, OLD.last_modified_date, OLD.creation_change_num,
new_change_num);   RETURN OLD;
END;
$function$


######################################################
Able to select from changes_package$get_change_num
######################################################

user_test@testdbpg # select * from test.changes_package$get_change_num();
+--------------------------------+
| changes_package$get_change_num |
+--------------------------------+
|                      100759111 |
+--------------------------------+
(1 row)

Time: 27.829 ms



--
View this message in context: http://www.postgresql-archive.org/Delete-failing-with-permission-denied-tp5964882.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.