Date: Mon, 5 Jun 2017 13:44:53 -0700 (MST)
From: anand086 <anand086@gmail.com>
To: pgsql-sql@postgresql.org
Message-ID: <1496695493471-5964882.post@n3.nabble.com>
Subject: Delete failing with -- permission denied
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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 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@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.


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql