BUG #13889: psql doesn't exequte correct script

Started by Vladimir Bilyakover 10 years ago4 messagesbugs

v_bilyak@mail.ru

over 10 years ago

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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Pavel Stehule

pavel.stehule@gmail.com

over 10 years ago

In reply to: Vladimir Bilyak (#1)

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

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

Show quoted text

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

Vladimir Bilyak

v_bilyak@mail.ru

over 10 years ago

In reply to: Pavel Stehule (#2)

Re[2]: [BUGS] BUG #13889: psql doesn't exequte correct script

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

Show quoted text

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

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

Pavel Stehule

pavel.stehule@gmail.com

over 10 years ago

In reply to: Vladimir Bilyak (#3)

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

Show quoted text

--
Sincerely,
Vladimir Bilyak

Понедельник, 25 января 2016, 21:47 +01:00 от 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=mailto%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=mailto%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 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
<https://e.mail.ru/compose/?mailto=mailto%3apgsql%2dbugs@postgresql.org>)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs