At 00:11 2/10/2008, Set wrote:

> what about GRANT/REVOKE, CREATE/DROP VIEW, ALTER INDEX INACTIVE etc? Are there any guidelines or is 'never mix DDL and DML' the generally accepted answer?

It is the generally accepted answer...especially where Blind Ignorance is a factor. But GRANT/REVOKE are not in the same league as CREATE/DROP VIEW, ALTER INDEX INACTIVE. If you stuff up permissions, they can be fixed without corrupting user data or breaking data protection...although the temporary loss of functionality for one or more users might be intolerable according to the software requirements...

>It kind of feels silly to force every existing user to exit whenever a new user has to be given access to a table.

GRANT and REVOKE do create and destroy system objects, which translates to operations and dependencies involving system tables during the COMMIT. Always keep DDL and DML in separate transactions and ensure that the DDL transactions are committed before attempting to reference the objects they affect. Whether GRANT and REVOKE call for exclusive access entirely depends on what you're granting and revoking. Revoking a role from one user affects only that user. Revoking a permission from a role may affect most of your users. You might have PSQL modules that refer to CURRENT_ROLE...and so on.

ALTER INDEX INACTIVE has the potential to allow corruption if access is not exclusive - consider a unique index, for example...

./heLen