Re: [SQL] Delete failing with -- permission denied

On 06/05/2017 01:44 PM, anand086 wrote:
> 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

You seem to have a chain of triggers/functions on this table. It would 
be nice to see how those cascade. In particular from above:

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""

which leads me to believe this is the problem:

CREATE OR REPLACE FUNCTION test."attribute_types_history$attribute_types"()

...

new_change_num := test.changes_package$get_change_num();

...

which then leads to what is in test.changes_package$get_change_num(), 
though I suspect it includes:

SQL statement "DELETE FROM ONLY "test"."attribute_types" WHERE $1
OPERATOR(pg_catalog.=) "attribute_type_entity_id""

Also would be nice to know what user(s) the functions are running as?



-- 
Adrian Klaver
adrian.klaver@aklaver.com