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

2016-01-26 7:35 GMT+01:00 Vladimir Bilyak <v_bilyak@mail.ru>:

> Hi,
>
> thank you for promt reply. Actually we didn't use -c option. We used
> command line as follows:
>
> psql.exe --username=3Dpostgres --file=3D<path to problem script>
> --log-file=3D<path to log file> --host=3Dlocalhost --output=3D<path to ou=
tput 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@dhcppc2 ~]$ psql -f test.sql postgres
Debug assertions "on"
psql:test.sql:1: ERROR:  3D000: database "bubu" does not exist
CREATE DATABASE
[pavel@dhcppc2 ~]$ psql -f test.sql postgres
DROP DATABASE
CREATE DATABASE

this have to work without any problem.

regards

Pavel



>
> --
> Sincerely,
> Vladimir Bilyak
>
> =D0=9F=D0=BE=D0=BD=D0=B5=D0=B4=D0=B5=D0=BB=D1=8C=D0=BD=D0=B8=D0=BA, 25 =
=D1=8F=D0=BD=D0=B2=D0=B0=D1=80=D1=8F 2016, 21:47 +01:00 =D0=BE=D1=82 Pavel =
Stehule <
> pavel.stehule@gmail.com>:
>
>
> Hi
>
> 2016-01-25 18:09 GMT+01:00 <v_bilyak@mail.ru
> <https://e.mail.ru/compose/?mailto=3Dmailto%3av_bilyak@mail.ru>>:
>
> The following bug has been logged on the website:
>
> Bug reference:      13889
> Logged by:          Vladimir
> Email address:      v_bilyak@mail.ru
> <https://e.mail.ru/compose/?mailto=3Dmailto%3av_bilyak@mail.ru>
> PostgreSQL version: 9.5.0
> Operating system:   Windows 10
> Description:
>
> Hello. I faced the following issue: psql.exe does not execute the followi=
ng
> script
>
> DROP DATABASE "demo_crash";
> CREATE DATABASE "demo_crash";
>
> aborting with error:
>
> CREATE DATABASE cannot be executed from a function or multi-command strin=
g
>
>
> 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 a=
ny
> 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@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@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@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@postgresql.org
> <https://e.mail.ru/compose/?mailto=3Dmailto%3apgsql%2dbugs@postgresql.org=
>)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>
>
>
>