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

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
>