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

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

From
Dave Page
Date:
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.


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

From
"Heikki Linnakangas"
Date:
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


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

From
Dave Page
Date:
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.


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

From
Andrew Dunstan
Date:
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
>


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

From
Dave Page
Date:
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
isimplicitly 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.