proposal: multiple psql option -c

On Thu, Jul 16, 2015 at 4:42 PM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

Hi

can we support multiple "-c" option?

Why? Because some statements like VACUUM cannot be used together with any

other statements with single -c option. The current solution is using echo
and pipe op, but it is a complication in some complex scripts - higher
complication when you run psql via multiple sudo statement.

Example:

psql -c "select pg_stat_reset()" -c "vacuum full analyze" dbname

or on all db

psql -At -c "select datname from pg_databases" postgres | \
xargs -n 1 -P 3 psql -c "..." -c "..."

Ideas, notes, comments?

Why you want it if we already have the -f option that cover this use case?

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL

2015-07-16 22:07 GMT+02:00 Fabrízio de Royes Mello <fabriziomello@gmail.com>
:

On Thu, Jul 16, 2015 at 4:42 PM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

Hi

can we support multiple "-c" option?

Why? Because some statements like VACUUM cannot be used together with

any other statements with single -c option. The current solution is using
echo and pipe op, but it is a complication in some complex scripts - higher
complication when you run psql via multiple sudo statement.

Example:

psql -c "select pg_stat_reset()" -c "vacuum full analyze" dbname

or on all db

psql -At -c "select datname from pg_databases" postgres | \
xargs -n 1 -P 3 psql -c "..." -c "..."

Ideas, notes, comments?

Why you want it if we already have the -f option that cover this use case?

It doesn't help me - we would to run script or remote script (via ssh)
without necessity to create (and later drop) files on production servers.

remote execution of scripts is much more simple if you don't need to create
any script files.

Regards

Pavel

On Thu, Jul 16, 2015 at 1:44 PM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

2015-07-16 22:07 GMT+02:00 Fabrízio de Royes Mello <
fabriziomello@gmail.com>:

Why you want it if we already have the -f option that cover this use case?

It doesn't help me - we would to run script or remote script (via ssh)
without necessity to create (and later drop) files on production servers.

Does piping a series of commands into psql work in your scenario? You can
even say things like:

cat $local_file | ssh $production_server 'psql $database'

--
:wq

2015-07-16 23:10 GMT+02:00 Rosser Schwarz <rosser.schwarz@gmail.com>:

On Thu, Jul 16, 2015 at 1:44 PM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

2015-07-16 22:07 GMT+02:00 Fabrízio de Royes Mello <
fabriziomello@gmail.com>:

Why you want it if we already have the -f option that cover this use
case?

It doesn't help me - we would to run script or remote script (via ssh)
without necessity to create (and later drop) files on production servers.

Does piping a series of commands into psql work in your scenario? You can
even say things like:

cat $local_file | ssh $production_server 'psql $database'

probably not - the first remote command is sudo su - due security reasons

On Thu, Jul 16, 2015 at 12:42 PM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

Hi

can we support multiple "-c" option?

Why? Because some statements like VACUUM cannot be used together with any
other statements with single -c option. The current solution is using echo
and pipe op, but it is a complication in some complex scripts - higher
complication when you run psql via multiple sudo statement.

Example:

psql -c "select pg_stat_reset()" -c "vacuum full analyze" dbname

or on all db

psql -At -c "select datname from pg_databases" postgres | \
xargs -n 1 -P 3 psql -c "..." -c "..."

Ideas, notes, comments?

IMO, rather having multiple -c args, it would be good to have another flag
like "-C" which do accept and execute multiple SQL statements in sequential.

Best Regards,
Dinesh
manojadinesh.blogspot.com

2015-07-17 0:03 GMT+02:00 dinesh kumar <dineshkumar02@gmail.com>:

On Thu, Jul 16, 2015 at 12:42 PM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

Hi

can we support multiple "-c" option?

Why? Because some statements like VACUUM cannot be used together with any
other statements with single -c option. The current solution is using echo
and pipe op, but it is a complication in some complex scripts - higher
complication when you run psql via multiple sudo statement.

Example:

psql -c "select pg_stat_reset()" -c "vacuum full analyze" dbname

or on all db

psql -At -c "select datname from pg_databases" postgres | \
xargs -n 1 -P 3 psql -c "..." -c "..."

Ideas, notes, comments?

IMO, rather having multiple -c args, it would be good to have another flag
like "-C" which do accept and execute multiple SQL statements in sequential.

it is one possible solution too

multiple -c option has advantage of simple evaluation of backslash
statements .. -c "\l" -c "\dt" - but this advantage is not high important.

Pavel

it is one possible solution too

multiple -c option has advantage of simple evaluation of backslash
statements .. -c "\l" -c "\dt" - but this advantage is not high important.

Or just properly understand the ; ?

-c "select * from foo; update bar set baz = 'bing'; vacuum bar;"

JD

Pavel

Best Regards,
Dinesh
manojadinesh.blogspot.com <http://manojadinesh.blogspot.com&gt;

Regards

Pavel

--
Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Announcing "I'm offended" is basically telling the world you can't
control your own emotions, so everyone else should do it for you.

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

2015-07-17 6:26 GMT+02:00 Joshua D. Drake <jd@commandprompt.com>:

it is one possible solution too

multiple -c option has advantage of simple evaluation of backslash
statements .. -c "\l" -c "\dt" - but this advantage is not high important.

Or just properly understand the ; ?

-c "select * from foo; update bar set baz = 'bing'; vacuum bar;"

there is a risk of compatibility issues - all statements runs under one
transaction implicitly

Pavel

On Thu, Jul 16, 2015 at 12:42 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
Hi
can we support multiple "-c" option?
Why? Because some statements like VACUUM cannot be used together with any other statements with single -c option. The current solution is using echo and pipe op, but it is a complication in some complex scripts - higher complication when you run psql via multiple sudo statement.
Example:
psql -c "select pg_stat_reset()" -c "vacuum full analyze" dbname
or on all db
psql -At -c "select datname from pg_databases" postgres | \
xargs -n 1 -P 3 psql -c "..." -c "..."
Ideas, notes, comments?

Hi,
Can't you handle this with a script on the target server ?

I have this one due to a profile issue:

cat cicrunpsql.sh
#!/bin/sh

# set the isdbx environment before calling psql with the passed arguments.
# required for remote calls with ssh

#example
# cicrunpsql.sh -Uisdb3 -c "select1"
# ssh isdb3@localhost cicrunpsql.sh -Uisdb3 -c "select1"

# remote calls per ssh do not get the profile automatically...
. ~/.profile || exit 1

psql "$@"

On Fri, Jul 17, 2015 at 12:36 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

Or just properly understand the ; ?

-c "select * from foo; update bar set baz = 'bing'; vacuum bar;"

there is a risk of compatibility issues - all statements runs under one
transaction implicitly

So what?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

2015-07-23 17:52 GMT+02:00 Robert Haas <robertmhaas@gmail.com>:

On Fri, Jul 17, 2015 at 12:36 AM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

Or just properly understand the ; ?

-c "select * from foo; update bar set baz = 'bing'; vacuum bar;"

there is a risk of compatibility issues - all statements runs under one
transaction implicitly

So what?

[pavel@dhcppc2 ~]$ psql -c "insert into x
values(txid_current()::text);insert into x values(txid_current()::text)"
postgres
INSERT 0 1
[pavel@dhcppc2 ~]$ psql postgres -c "select * from x"
a
------
1888
1888
(2 rows)

I would to run -c command in separate transactions (when option
--single-transaction is not used).

Then is possible run

-c "select pg_reset ...()" -c "vacuum analyze ..."

Regards

Pavel

p.s.

the state string "INSERT 0 1" is buggy probably

On Saturday, July 25, 2015, Pavel Stehule <pavel.stehule@gmail.com> wrote:

2015-07-23 17:52 GMT+02:00 Robert Haas <robertmhaas@gmail.com>:

On Fri, Jul 17, 2015 at 12:36 AM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

Or just properly understand the ; ?

-c "select * from foo; update bar set baz = 'bing'; vacuum bar;"

there is a risk of compatibility issues - all statements runs under one
transaction implicitly

So what?

[pavel@dhcppc2 ~]$ psql -c "insert into x
values(txid_current()::text);insert into x values(txid_current()::text)"
postgres
INSERT 0 1
the state string "INSERT 0 1" is buggy probably

How do you figure? The last statement only inserted one record.

To that point would you expect each separate -c to output its results to
the console?

David J.

2015-07-25 10:33 GMT+02:00 David G. Johnston <david.g.johnston@gmail.com>:

On Saturday, July 25, 2015, Pavel Stehule <pavel.stehule@gmail.com> wrote:

2015-07-23 17:52 GMT+02:00 Robert Haas <robertmhaas@gmail.com>:

On Fri, Jul 17, 2015 at 12:36 AM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

Or just properly understand the ; ?

-c "select * from foo; update bar set baz = 'bing'; vacuum bar;"

there is a risk of compatibility issues - all statements runs under one
transaction implicitly

So what?

[pavel@dhcppc2 ~]$ psql -c "insert into x
values(txid_current()::text);insert into x values(txid_current()::text)"
postgres
INSERT 0 1
the state string "INSERT 0 1" is buggy probably

How do you figure? The last statement only inserted one record.

I understand now, it consistent with current design. So from this view it
is not error.

To that point would you expect each separate -c to output its results to
the console?

It will be nice side effect, but my primary problem was a impossibility to
combine VACUUM and any other statement to one simple psql call.

Pavel

On Sat, Jul 25, 2015 at 5:27 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

It will be nice side effect, but my primary problem was a impossibility to
combine VACUUM and any other statement to one simple psql call.

Seems like you can do that easily enough:

[rhaas pgsql]$ (echo 'SELECT 1;'; echo 'VACUUM;'; echo 'SELECT 2;') | psql
?column?
----------
1
(1 row)

VACUUM
?column?
----------
2
(1 row)

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

2015-07-27 20:32 GMT+02:00 Robert Haas <robertmhaas@gmail.com>:

On Sat, Jul 25, 2015 at 5:27 AM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

It will be nice side effect, but my primary problem was a impossibility

to

combine VACUUM and any other statement to one simple psql call.

Seems like you can do that easily enough:

[rhaas pgsql]$ (echo 'SELECT 1;'; echo 'VACUUM;'; echo 'SELECT 2;') | psql
?column?
----------
1
(1 row)

VACUUM
?column?
----------
2
(1 row)

how I can do it with xargs?

Regards

Pavel

On Mon, Jul 27, 2015 at 2:37 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

2015-07-27 20:32 GMT+02:00 Robert Haas <robertmhaas@gmail.com>:

On Sat, Jul 25, 2015 at 5:27 AM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

It will be nice side effect, but my primary problem was a impossibility
to
combine VACUUM and any other statement to one simple psql call.

Seems like you can do that easily enough:

[rhaas pgsql]$ (echo 'SELECT 1;'; echo 'VACUUM;'; echo 'SELECT 2;') | psql
?column?
----------
1
(1 row)

VACUUM
?column?
----------
2
(1 row)

how I can do it with xargs?

I don't specifically what you're trying to do, but I bet it's not that hard.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

2015-07-27 20:47 GMT+02:00 Robert Haas <robertmhaas@gmail.com>:

On Mon, Jul 27, 2015 at 2:37 PM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

2015-07-27 20:32 GMT+02:00 Robert Haas <robertmhaas@gmail.com>:

On Sat, Jul 25, 2015 at 5:27 AM, Pavel Stehule <pavel.stehule@gmail.com

wrote:

It will be nice side effect, but my primary problem was a

impossibility

to
combine VACUUM and any other statement to one simple psql call.

Seems like you can do that easily enough:

[rhaas pgsql]$ (echo 'SELECT 1;'; echo 'VACUUM;'; echo 'SELECT 2;') |

psql

?column?
----------
1
(1 row)

VACUUM
?column?
----------
2
(1 row)

how I can do it with xargs?

I don't specifically what you're trying to do, but I bet it's not that
hard.

I am trying to run parallel execution

psql -At -c "select datname from pg_database" postgres | xargs -n 1 -P 3
psql -c "select current_database()"

Pavel

On 07/27/2015 02:53 PM, Pavel Stehule wrote:

I am trying to run parallel execution

psql -At -c "select datname from pg_database" postgres | xargs -n 1 -P
3 psql -c "select current_database()"

I don't think it's going to be a hugely important feature, but I don't
see a problem with creating a new option (-C seems fine) which would
have the same effect as if the arguments were contatenated into a file
which is then used with -f. IIRC -c has some special characteristics
which means it's probably best not to try to extend it for this feature.

cheers

andrew

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

On Mon, Jul 27, 2015 at 2:53 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

I am trying to run parallel execution

psql -At -c "select datname from pg_database" postgres | xargs -n 1 -P 3
psql -c "select current_database()"

Put this in a shell script called run-psql:

#!/bin/bash

test $# = 0 && exit
for f in "${@:1:$(($#-1))}"; do
echo "$f" \;
done | psql "${@:$#}"

Then:

psql -At -c "select datname from pg_database" postgres | xargs -n 1 -P
3 ./run-psql "select current_database()" "vacuum" "select 1"

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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