Thread: CREATE USER
Hi! I have to create users via an ODBC connection, but I get this error: CREATE USER: may not be called in a transaction block With psql I have no problems. Any body knows what is happening? Thanks Diego Schvartzman Email: diego.schvartzman@usa.net ICQ# 1779434
> I have to create users via an ODBC connection, but I get this error:
> CREATE USER: may not be called in a transaction block
> With psql I have no problems. Any body knows what is happening?
Yeah. There are too many transaction block wrappers (BEGIN/COMMIT
statements) in the ODBC driver. I'm not sure if we have patches for it
yet...
- Thomas
> -----Original Message----- > From: pgsql-general-owner@hub.org [mailto:pgsql-general-owner@hub.org]On > Behalf Of Thomas Lockhart > > > I have to create users via an ODBC connection, but I get this error: > > CREATE USER: may not be called in a transaction block > > With psql I have no problems. Any body knows what is happening? > > Yeah. There are too many transaction block wrappers (BEGIN/COMMIT > statements) in the ODBC driver. I'm not sure if we have patches for it > yet... > Currently the following command sequence isn't allowed. begin; create user ... commit; This wouldn't be good for odbc/jdbc/ecpg etc. How about starting new transaction automatically after committing "create user ..." at backend side if "create user" is the first command of the transaction ? Regards. Hiroshi Inoue Inoue@tpf.co.jp
"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:
> How about starting new transaction automatically after committing
> "create user ..." at backend side if "create user" is the first command
> of the transaction ?
So then
begin;
create user ...;
rollback;
would do the wrong thing --- silently?
I don't think that's an improvement :-(
The only reason CREATE USER isn't rollbackable is that the flat password
file is updated immediately by a trigger, rather than at transaction
commit. The right fix would be to defer the update until commit (which
is certainly doable, though it might mean hardwired support for the
update instead of doing it in a generic trigger function).
If that seems like too much work, how about downgrading the "create
user not allowed in transaction" error to a "please don't abort now"
notice? It's pretty silly that CREATE USER is stiffnecked about this
when DROP TABLE is not --- the bad consequences of rolling back DROP
TABLE are a lot worse.
regards, tom lane
> -----Original Message----- > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > > "Hiroshi Inoue" <Inoue@tpf.co.jp> writes: > > How about starting new transaction automatically after committing > > "create user ..." at backend side if "create user" is the first command > > of the transaction ? > > So then > begin; > create user ...; > rollback; > > would do the wrong thing --- silently? > It seems not wrong that un-rollbackable command is never rolled back. At least it doesn't cause inconsistency. > I don't think that's an improvement :-( > > The only reason CREATE USER isn't rollbackable is that the flat password > file is updated immediately by a trigger, rather than at transaction > commit. The right fix would be to defer the update until commit (which > is certainly doable, though it might mean hardwired support for the > update instead of doing it in a generic trigger function). > > If that seems like too much work, I don't prefer flat file solution as I mentioned before. > how about downgrading the "create > user not allowed in transaction" error to a "please don't abort now" > notice? Sounds preferable. Regards. Hiroshi Inoue Inoue@tpf.co.jp
I tryed many variations of BEGIN, COMMINT, CREATE USER .... and ROLLBACK, but none of them worked, I always get the same error message (CREATE USER: may not be called in a transaction block). So, is possible to do what I want to? I think that in 6.5.3 I could, but now in 7.0 is not working, I'm right?? I'm running an application from win machines via ODBC, so is more difficult to execute a operating system command like CREATEUSER that would be a solution. So, would be perfect to me to do it via SQL commands. Sorry about my poor english! Diego Schvartzman Email: diego.schvartzman@usa.net ICQ# 1779434 ----- Original Message ----- From: Tom Lane <tgl@sss.pgh.pa.us> To: Hiroshi Inoue <Inoue@tpf.co.jp> Cc: Thomas Lockhart <lockhart@alumni.caltech.edu>; Lista PGSQL <pgsql-general@postgresql.org>; Diego Schvartzman <dschvar@yahoo.com> Sent: Thursday, June 01, 2000 3:45 AM Subject: Re: [GENERAL] CREATE USER > "Hiroshi Inoue" <Inoue@tpf.co.jp> writes: > > How about starting new transaction automatically after committing > > "create user ..." at backend side if "create user" is the first command > > of the transaction ? > > So then > begin; > create user ...; > rollback; > > would do the wrong thing --- silently? > > I don't think that's an improvement :-( > > The only reason CREATE USER isn't rollbackable is that the flat password > file is updated immediately by a trigger, rather than at transaction > commit. The right fix would be to defer the update until commit (which > is certainly doable, though it might mean hardwired support for the > update instead of doing it in a generic trigger function). > > If that seems like too much work, how about downgrading the "create > user not allowed in transaction" error to a "please don't abort now" > notice? It's pretty silly that CREATE USER is stiffnecked about this > when DROP TABLE is not --- the bad consequences of rolling back DROP > TABLE are a lot worse. > > regards, tom lane >