Обсуждение: 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.
Re: CREATE DATABASE cannot be executed from a function or multi-command string
От
"Heikki Linnakangas"
Дата:
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.
Re: CREATE DATABASE cannot be executed from a function or multi-command string
От
Andrew Dunstan
Дата:
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 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.