This is my proposal
CREATE OR REPLACE FUNCTION acs_permission__grant_permission(
grant_permission__object_id integer,
grant_permission__grantee_id integer,
grant_permission__privilege varchar
) RETURNS integer AS $$
DECLARE
exists_p boolean;
BEGIN
-- lock table acs_permissions_lock;
select count(*) > 0 into exists_p
from acs_permissions
where object_id = grant_permission__object_id
and grantee_id = grant_permission__grantee_id
and privilege = grant_permission__privilege;
if not exists_p then
insert into acs_permissions
(object_id, grantee_id, privilege)
values
(grant_permission__object_id, grant_permission__grantee_id,
grant_permission__privilege);
end if;
return 0;
-- handle exception, rather than lock table
EXCEPTION
when unique_violation then
return 0;
END;
$$ LANGUAGE plpgsql;
As you can see I've just put exception handling translating from 'dup_val_on_index', Oracle flavour, to 'unique_violation', which is Postgres's. This came from:
http://www.postgresql.org/docs/9.0/static/plpgsql-porting.html
This translation seems valid from our actual Postgres 8.2 to devel version, looking at doc.
This should fix permission granting. While testing, I've found out the same problem happens when we call acs_permission__revoke_permission, as also this proc locks permissions exclusively.
In this case solution is simpler as it is sufficient to comment lock and go on with our lives.
CREATE OR REPLACE FUNCTION acs_permission__revoke_permission(
revoke_permission__object_id integer,
revoke_permission__grantee_id integer,
revoke_permission__privilege varchar
) RETURNS integer AS $$
DECLARE
BEGIN
-- lock table acs_permissions_lock;
delete from acs_permissions
where object_id = revoke_permission__object_id
and grantee_id = revoke_permission__grantee_id
and privilege = revoke_permission__privilege;
return 0;
END;
$$ LANGUAGE plpgsql;
It is important to notice that Oracle codebase already behaves in this way, that is, in Oracle we don't lock any table and just handle exception/ignore the case
This solves our issue. If you think this solution is ok in general I can commit to cvs.
All the best
Antonio
