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
