Re: BUG #13889: psql doesn't exequte correct script

2016-01-26 7:35 GMT+01:00 Vladimir Bilyak <v_bilyak(at)mail(dot)ru>:

> Hi,
>
> thank you for promt reply. Actually we didn't use -c option. We used
> command line as follows:
>
> psql.exe --username=postgres --file=<path to problem script>
> --log-file=<path to log file> --host=localhost --output=<path to output log>
>
> Is this related to the issue "all statements executed are executed in one
> transaction" as well? If so, please point me solution to execute script
> with DROP DATABASE and CREATE DATABASE statements via psql.
>

probably you do

BEGIN
DROP DATABASE ...
COMMIT;

in your file. This is not supported. DROP DATABASE, CREATE DATABASE should
not be running under any outer transaction. There are no workaround.

but

echo "DROP DATABASE bubu; CREATE DATABASE bubu" > test.sql

[pavel(at)dhcppc2 ~]$ psql -f test.sql postgres
Debug assertions "on"
psql:test.sql:1: ERROR: 3D000: database "bubu" does not exist
CREATE DATABASE
[pavel(at)dhcppc2 ~]$ psql -f test.sql postgres
DROP DATABASE
CREATE DATABASE

this have to work without any problem.

regards

Pavel

>
> --
> Sincerely,
> Vladimir Bilyak
>
> Понедельник, 25 января 2016, 21:47 +01:00 от Pavel Stehule <
> pavel(dot)stehule(at)gmail(dot)com>:
>
>
> Hi
>
> 2016-01-25 18:09 GMT+01:00 <v_bilyak(at)mail(dot)ru
> <https://e.mail.ru/compose/?mailto=mailto%3av_bilyak(at)mail(dot)ru>>:
>
> The following bug has been logged on the website:
>
> Bug reference: 13889
> Logged by: Vladimir
> Email address: v_bilyak(at)mail(dot)ru
> <https://e.mail.ru/compose/?mailto=mailto%3av_bilyak(at)mail(dot)ru>
> PostgreSQL version: 9.5.0
> Operating system: Windows 10
> Description:
>
> Hello. I faced the following issue: psql.exe does not execute the following
> script
>
> DROP DATABASE "demo_crash";
> CREATE DATABASE "demo_crash";
>
> aborting with error:
>
> CREATE DATABASE cannot be executed from a function or multi-command string
>
>
> I searched in Google for solution but found only suggestion to rebuild
> Postgres with some fix in source code that is not acceptable in my case. Do
> you have another suggestion how to resolve it? Thank you in advance for any
> help!
>
>
> It isn't bug. You probably use "-c" option. All statements executed by
> this option are executed in one transaction. Some statements like CREATE
> DATABASE or DROP DATABASE cannot be called from transaction.
>
> [pavel(at)dhcppc2 ~]$ psql -c "DROP DATABASE demo_crash; CREATE DATABASE
> demo_crash" postgres
> Debug assertions "off"
> ERROR: 25001: DROP DATABASE cannot be executed from a function or
> multi-command string
> LOCATION: PreventTransactionChain, xact.c:3158
> Time: 0.488 ms
>
> The solution is in next release 9.6, that allows multiple -c options
>
> [pavel(at)dhcppc2 ~]$ psql -c "DROP DATABASE demo_crash" -c "CREATE DATABASE
> demo_crash" postgres
> Debug assertions "off"
> ERROR: 3D000: database "demo_crash" does not exist
> LOCATION: dropdb, dbcommands.c:797
> Time: 0.399 ms
> CREATE DATABASE
> Time: 711.694 ms
>
> For current release use echo and pipe
>
> [pavel(at)dhcppc2 ~]$ echo "DROP DATABASE demo_crash; CREATE DATABASE
> demo_crash" | psql postgres
> Debug assertions "off"
> DROP DATABASE
> Time: 224.953 ms
> CREATE DATABASE
> Time: 742.695 ms
>
> I have not windows, but it should work
>
> Regards
>
> Pavel
>
>
>
> Sincerely,
> Vladimir
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs(at)postgresql(dot)org
> <https://e.mail.ru/compose/?mailto=mailto%3apgsql%2dbugs(at)postgresql(dot)org>)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>
>
>
>