Обсуждение: BUG #13889: psql doesn't exequte correct script

The following bug has been logged on the website:

Bug reference:      13889
Logged by:          Vladimir
Email address:      v_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 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!

Sincerely,
Vladimir

Hi

2016-01-25 18:09 GMT+01:00 <v_bilyak@mail.ru>:

> The following bug has been logged on the website:
>
> Bug reference:      13889
> Logged by:          Vladimir
> Email address:      v_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 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@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)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>

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
>
>
>
>

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.

--
Sincerely,
Vladimir Bilyak

Понедельник, 25 января 2016, 21:47 +01:00 от Pavel Stehule <pavel.stehule@gmail.com>: