CREATE DATABASE cannot be executed from a function or multi-command string

I get the above error message when creating a database in pgAdmin now:

CREATE DATABASE demo
WITH ENCODING='SQL_ASCII'
TABLESPACE=pg_default;
COMMENT ON DATABASE demo IS 'This is the demo database';
GRANT ALL ON DATABASE demo TO public;
ALTER DATABASE demo SET search_path=demo;

I understand what the message is telling me to do, but what is the
reason for this change, and is it really *required*? The way pgAdmin is
designed, a change to accomodate firing everything off in seperate
queries would be a significant one which would most likely require us to
effectively restart our whole beta process and may well mean we don't
have a release ready for 8.3 in fact :-(

The only other option I have at the moment is to force the user to do
the above operation in two stages which I'm sure you'll agree is not
very friendly.

Regards, Dave.

Dave Page wrote:
> I get the above error message when creating a database in pgAdmin now:
>
> CREATE DATABASE demo
> WITH ENCODING='SQL_ASCII'
> TABLESPACE=pg_default;
> COMMENT ON DATABASE demo IS 'This is the demo database';
> GRANT ALL ON DATABASE demo TO public;
> ALTER DATABASE demo SET search_path=demo;
>
> I understand what the message is telling me to do, but what is the
> reason for this change, and is it really *required*?

This is the commit that changed it:

http://archives.postgresql.org/pgsql-committers/2007-03/msg00270.php

It was in fact never supposed to work, but we failed to detect it. I had
to modify my test scripts that did something like psql -c "VACUUM foo;
SELECT ..." because of that as well. It's highly likely that it'll brake
other people's scripts as well, but I don't think there's much we can do
about it :(.

> The way pgAdmin is
> designed, a change to accomodate firing everything off in seperate
> queries would be a significant one which would most likely require us to
> effectively restart our whole beta process and may well mean we don't
> have a release ready for 8.3 in fact :-(

I'm surprised this hasn't been noticed before, the change was made back
in March. Are you sure there's more queries like that that need to be
modified?

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

Heikki Linnakangas wrote:
> Dave Page wrote:
>> I get the above error message when creating a database in pgAdmin now:
>>
>> CREATE DATABASE demo
>> WITH ENCODING='SQL_ASCII'
>> TABLESPACE=pg_default;
>> COMMENT ON DATABASE demo IS 'This is the demo database';
>> GRANT ALL ON DATABASE demo TO public;
>> ALTER DATABASE demo SET search_path=demo;
>>
>> I understand what the message is telling me to do, but what is the
>> reason for this change, and is it really *required*?
>
> This is the commit that changed it:
>
> http://archives.postgresql.org/pgsql-committers/2007-03/msg00270.php
>
> It was in fact never supposed to work, but we failed to detect it. I had
> to modify my test scripts that did something like psql -c "VACUUM foo;
> SELECT ..." because of that as well. It's highly likely that it'll brake
> other people's scripts as well, but I don't think there's much we can do
> about it :(.

Yeah, I found that just after I mailed.

>> The way pgAdmin is
>> designed, a change to accomodate firing everything off in seperate
>> queries would be a significant one which would most likely require us to
>> effectively restart our whole beta process and may well mean we don't
>> have a release ready for 8.3 in fact :-(
>
> I'm surprised this hasn't been noticed before, the change was made back
> in March. Are you sure there's more queries like that that need to be
> modified?

It's not the query, but the way it's passed around in internally from
the dialogue to the code the executes it and updates the browser. It all
assumes every update is a single atomic statement - and in fact relies
on that assumption in a number of classes. After thinking about it some
more I may have a less-invasive solution in which we embed a marker in
the SQL generated to denote that the statement should be split at that
point and executed as a seperate block - but it seems somewhat hacky for
my tastes :-(

I agree that this is likely to break a lot of folks scripts.

Regards, Dave.

src/bin/psql/common.c has a routine that lets psql get round this, by
not sending a BEGIN in the case of the offending statements. I have no
idea if this might be helpful for pgadmin though.

cheers

andrew

Dave Page wrote:
> Heikki Linnakangas wrote:
>> Dave Page wrote:
>>> I get the above error message when creating a database in pgAdmin now:
>>>
>>> CREATE DATABASE demo
>>> WITH ENCODING='SQL_ASCII'
>>> TABLESPACE=pg_default;
>>> COMMENT ON DATABASE demo IS 'This is the demo database';
>>> GRANT ALL ON DATABASE demo TO public;
>>> ALTER DATABASE demo SET search_path=demo;
>>>
>>> I understand what the message is telling me to do, but what is the
>>> reason for this change, and is it really *required*?
>>
>> This is the commit that changed it:
>>
>> http://archives.postgresql.org/pgsql-committers/2007-03/msg00270.php
>>
>> It was in fact never supposed to work, but we failed to detect it. I had
>> to modify my test scripts that did something like psql -c "VACUUM foo;
>> SELECT ..." because of that as well. It's highly likely that it'll brake
>> other people's scripts as well, but I don't think there's much we can do
>> about it :(.
>
> Yeah, I found that just after I mailed.
>
>>> The way pgAdmin is
>>> designed, a change to accomodate firing everything off in seperate
>>> queries would be a significant one which would most likely require
>>> us to
>>> effectively restart our whole beta process and may well mean we don't
>>> have a release ready for 8.3 in fact :-(
>>
>> I'm surprised this hasn't been noticed before, the change was made back
>> in March. Are you sure there's more queries like that that need to be
>> modified?
>
> It's not the query, but the way it's passed around in internally from
> the dialogue to the code the executes it and updates the browser. It
> all assumes every update is a single atomic statement - and in fact
> relies on that assumption in a number of classes. After thinking about
> it some more I may have a less-invasive solution in which we embed a
> marker in the SQL generated to denote that the statement should be
> split at that point and executed as a seperate block - but it seems
> somewhat hacky for my tastes :-(
>
> I agree that this is likely to break a lot of folks scripts.
>
> Regards, Dave.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

Andrew Dunstan wrote:
>
> src/bin/psql/common.c has a routine that lets psql get round this, by
> not sending a BEGIN in the case of the offending statements. I have no
> idea if this might be helpful for pgadmin though.

Yeah, unfortunately it's not that we wrap the statement in a begin/end -
we rely on the fact that each call to PQexec is implicitly a single
transaction.

We're aware of course that CREATE DATABASE can't run in a transaction
block but just turned a blind eye to that on the grounds that it was
much more likely to fail than any of the other statements following it
in the multi-statement and we weren't about to start rolling it back
ourselves anyway.

Thanks, Dave.