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

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.