Thread: 'SET LOCAL ROLE blah;' doesn't work?
Greetings, At http://www.postgresql.org/docs/8.2/static/sql-set-role.html, we claim that 'SESSION and LOCAL modifiers act the same as for the regular SET command', but I don't think that's actually right... networx=3D> set role postgres; SET networx*=3D# show role; role=20=20=20 ---------- postgres (1 row) networx*=3D# reset role; RESET networx*=3D> set session role postgres; SET networx*=3D# show role; role=20=20=20 ---------- postgres (1 row) networx*=3D# reset role; RESET networx*=3D> set local role postgres; SET networx*=3D> show role; role=20 ------ none (1 row) networx*=3D> reset role; RESET networx*=3D>=20 Seems like we ignore requests to 'SET LOCAL ROLE blah'. Note that above it's all in one transaction (indicated by the '*'). Might be something I'm misunderstanding tho, I suppose... :/ I checked, and after the 'set local role' I don't seem to have the permissions of that user (and still have the permissions of my prior user) too. My inclination is that the documentation is wrong, honestly... I was originally looking into this area of the documentation to suggest that we explicitly mention somehow under 'reset all' that 'reset role' isn't done, and similairly that the documentation under 'set' doesn't apply for 'set role'. Thanks, Stephen
Stephen Frost <sfrost@snowman.net> writes: > At http://www.postgresql.org/docs/8.2/static/sql-set-role.html, we > claim that 'SESSION and LOCAL modifiers act the same as for the > regular SET command', but I don't think that's actually right... I cannot duplicate the behavior you show, in either HEAD or 8.2. Are you *sure* you were inside a transaction block? regards, tom lane
* Tom Lane (tgl@sss.pgh.pa.us) wrote: > Stephen Frost <sfrost@snowman.net> writes: > > At http://www.postgresql.org/docs/8.2/static/sql-set-role.html, we > > claim that 'SESSION and LOCAL modifiers act the same as for the > > regular SET command', but I don't think that's actually right... >=20 > I cannot duplicate the behavior you show, in either HEAD or 8.2. > Are you *sure* you were inside a transaction block? Wow. Okay, this is more interesting. It has some interaction with: \set ON_ERROR_ROLLBACK 'on' in my .psqlrc. If I remove that, then it works as expected. My understanding of ON_ERROR_ROLLBACK is that it'll set a savepoint before each command and then will roll back to that savepoint on an error- but it's still all inside of one main transaction. Seems like ON_ERROR_ROLLBACK is rolling back on a non-error in this case.. Strikes me as very bizarre. Try setting ON_ERROR_ROLLBACK to 'on' and doing 'set local role'... This is on 8.2.4. Thanks, Stephen
Stephen Frost <sfrost@snowman.net> writes: > * Tom Lane (tgl@sss.pgh.pa.us) wrote: >> I cannot duplicate the behavior you show, in either HEAD or 8.2. > Wow. Okay, this is more interesting. It has some interaction with: > \set ON_ERROR_ROLLBACK 'on' > in my .psqlrc. If I remove that, then it works as expected. Ah. log_statement = all tells the tale: regression=# set log_statement='all'; SET regression=# \set ON_ERROR_ROLLBACK 'on' regression=# begin; BEGIN regression=# set local role tgl; SET regression=# show role; role ------ none (1 row) regression=# and the postmaster log has 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 So actually, ON_ERROR_ROLLBACK breaks *any* use of SET LOCAL, not just ROLE. Not sure that this is fixable :-( regards, tom lane
Tom Lane wrote: > and the postmaster log has > > 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 > > So actually, ON_ERROR_ROLLBACK breaks *any* use of SET LOCAL, not just > ROLE. Not sure that this is fixable :-( Maybe if psql sees "SET LOCAL" it shouldn't send the RELEASE command. But it seems a bit error prone to be finding each command that may be affected by RELEASE ... what other thing do we have that works at the level of subtransactions? -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
* 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
* Alvaro Herrera (alvherre@commandprompt.com) wrote: > Tom Lane wrote: > > 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_tem= porary_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_tempo= rary_savepoint > > 2007-06-28 22:02:57.545 EDT 2870 LOG: statement: SAVEPOINT pg_psql_tem= porary_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_tempo= rary_savepoint > >=20 > > So actually, ON_ERROR_ROLLBACK breaks *any* use of SET LOCAL, not just > > ROLE. Not sure that this is fixable :-( >=20 > Maybe if psql sees "SET LOCAL" it shouldn't send the RELEASE command. > But it seems a bit error prone to be finding each command that may be > affected by RELEASE ... what other thing do we have that works at the > level of subtransactions? At the very least, anything which does work at the subtransaction level and not the transaction level should be documented as such... I don't see anything (perhaps I've missed it) in the 'set local' or the 'release savepoint' documentation which describes this behavior... :/ Perhaps I've misunderstood, but generally when we say 'transaction', we're meaning the a 'full' one and not a savepoint/release in the documentation. The 'SET LOCAL' documentation even talks explicitly about COMMIT and ROLLBACK being what causes a reset, RELEASE isn't mentioned. Thanks, Stephen
Stephen Frost <sfrost@snowman.net> writes: >> 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. No, it's supposed to be per subtransaction. Or at least that's the interpretation we put on it when we made subtransactions; I fear it's too late to change that now, even if there were a good argument to. > 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. That assumption is broken anyway. As of 8.3 there's a DISCARD ALL command that actually does a full cleanup of connection-local state. regards, tom lane
[ see thread at http://archives.postgresql.org/pgsql-bugs/2007-06/msg00166.php ] Stephen Frost <sfrost@snowman.net> writes: > * Alvaro Herrera (alvherre@commandprompt.com) wrote: >> Tom Lane wrote: >>> So actually, ON_ERROR_ROLLBACK breaks *any* use of SET LOCAL, not just >>> ROLE. Not sure that this is fixable :-( >> >> Maybe if psql sees "SET LOCAL" it shouldn't send the RELEASE command. >> But it seems a bit error prone to be finding each command that may be >> affected by RELEASE ... what other thing do we have that works at the >> level of subtransactions? > At the very least, anything which does work at the subtransaction level > and not the transaction level should be documented as such... I don't > see anything (perhaps I've missed it) in the 'set local' or the 'release > savepoint' documentation which describes this behavior... :/ I came across this open issue by chance while looking through my mail folder, and realized that the recently proposed change to SET LOCAL's behavior would resolve Stephen's complaint. I believe that the end result of the discussion in this thread: http://archives.postgresql.org/pgsql-hackers/2007-09/msg00030.php was that we should make SET LOCAL's effects persist until the end of the current top transaction, unless reverted by subtransaction rollback or the save/restore action of a function-local SET option for the same GUC variable. With that change, psql's automatic RELEASEs for ON_ERROR_ROLLBACK mode won't affect the state of GUC variables. So this reinforces my feeling that we came to the right conclusion in last week's thread. I haven't done anything about revising the GUC code for that, but will get on it now. regards, tom lane