Re: 'SET LOCAL ROLE blah;' doesn't work?

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Ah.  log_statement = all tells the tale:
[...]
> 2007-06-28 22:02:14.418 EDT 2870 LOG:  statement: begin;
> 2007-06-28 22:02:26.044 EDT 2870 LOG:  statement: SAVEPOINT pg_psql_temporary_savepoint
> 2007-06-28 22:02:26.045 EDT 2870 LOG:  statement: set local role tgl;
> 2007-06-28 22:02:26.047 EDT 2870 LOG:  statement: RELEASE pg_psql_temporary_savepoint
> 2007-06-28 22:02:57.545 EDT 2870 LOG:  statement: SAVEPOINT pg_psql_temporary_savepoint
> 2007-06-28 22:02:57.546 EDT 2870 LOG:  statement: show role;
> 2007-06-28 22:02:57.548 EDT 2870 LOG:  statement: RELEASE pg_psql_temporary_savepoint

Hrmpf.

> So actually, ON_ERROR_ROLLBACK breaks *any* use of SET LOCAL, not just
> ROLE.  Not sure that this is fixable :-(

That's not the behavior which is intended though, is it?  SET LOCAL
should be for an entire transaction, not for subtransactions/savepoints.
At the very least we should mention this issue in the 'release
savepoint' and 'set local' documentation, and it seems like it'd be very
nice to have fixed, if it can be... :/

Additionally, as I mentioned before, we should really make it clear that
'reset all' doesn't apply to roles.  The reason I bring it up is that,
in at least the PG PHP driver, when using persistant connections it's
expected that a 'reset all' handles cleaning things up entirely between
page loads, and that's not the case for roles.  I'm not a very trusting
person so I made sure our application always reset the role and was in
general careful but it's something some people might get caught by.
Honestly, it seems likely the driver maintainer will end up adjusting
things to, at least optionally, reset the role explicitly as well.

Of course, alternatively, we could have reset all apply to roles.  I
don't honestly feel very strongly either way on that one.

    Thanks,

        Stephen