Delete failing with -- permission denied

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(at)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(at)testdbpg #begin;
BEGIN
Time: 21.895 ms
user_test(at)testdbpg # delete from test.attributes where
(attribute_type_id)=(1220010);
DELETE 1
Time: 24.145 ms
user_test(at)testdbpg # DELETE FROM ONLY "test"."attribute_types" where
(attribute_type_id)=(1220010);
DELETE 1
Time: 35.638 ms
user_test(at)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(at)testdbpg # rollback;
ROLLBACK
Time: 28.266 ms
user_test(at)testdbpg #

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

CREATE OR REPLACE FUNCTION test."attribute_types_history$attribute_types"()
RETURNS trigger
LANGUAGE 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(at)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.