RE: Creating users

> -----Original Message-----
> From: Miguel Gonzalez [mailto:jffernandez(at)usuarios(dot)retecal(dot)es]
> Sent: 11 August 2001 17:10
> To: pgsql-odbc(at)postgresql(dot)org
> Subject: [ODBC] Creating users
>
>
> dear all,
>
> I am developing a client application with C++ Builder 3 to
> access a Database designed with PostgreSQL 7.0.4 using a BDE
> alias through the ODBC driver.
>
> I would like to have a form where I can add users to my
> users table of my database and also create the corresponding
> entry to the postgres pg_user table.
>
> What I have tried through the Database Explorer is the
> following transaction (I use begin and commit, otherwise the
> parser complains):
>
> BEGIN;
> CREATE USER user1 WITH PASSWORD 'passwd_user1' IN GROUP
> users_invui; COMMIT;
>
> I check in the pg_user table but no entry is added. I
> execute without the begin and the commit comands the same
> query in the backend (psql) and it works perfectly. What am I
> doing wrong?

It used to be the case that you couldn't execute certain queries within a
transaction - specifically CREATE/ALTER/DROP USER/DATA. I haven't noticed
that it's changed, but the ODBC driver also wraps queries in a transaction
if they're not already so you can't just drop the BEGIN; COMMIT;

In pgAdmin, the workaround we use is to place a single space before the
CREATE/ALTER/DROP eg.

CREATE USER dpage

becomes

CREATE USER dpage

This works currently but may not in the future releases of PostgreSQL if the
behaviour is changed.

> Many thanks in advance and sorry for my English

Why? It sounds better than mine and I've lived in .uk all my life!

Regards, Dave.