Re: [GENERAL] psql question: aborting a "script"

[Francisco Olarte]

Hi Melvin:

On Tue, Mar 15, 2016 at 3:57 PM, Melvin Davidson  wrote:
> What you really want is
> "ON_ERROR_STOP
...
> So just
> SET ON_ERROR_STOP = ON
> before any other statements

IIRC you are right with the variable ... BUT .. it is a psql setting,
not a session setting, so he'll probably need to use the \set psql
mettacommand:

\set ON_ERROR_STOP on

and also, use on as suggested on the docs, not ON, I'm not sure wether
PSQL is case sensitive.

Francisco Olarte.


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

Re: [GENERAL] psql question: aborting a "script"

[Albe Laurenz]

John McKown wrote:
> I'm likely abusing the psql program. What I have is an awk program which 
> reads a file and produces a
> number of INSERT INTO commands. I then feed these commands into psql to 
> execute them. Yes, a Perl
> program would be a better idea. Anyway, sometimes the commands are rejected 
> due to some problem, such
> as duplicate primary key. What I wish is that the psql command had a switch, 
> or control command, which
> would say "exit from psql if anything fails". To me, this is like the BASH 
> "set -e" command for a
> shell script. Does this sound like a useful addition. Or am I just missing 
> where it already exists?

Did you try "psql -v ON_ERROR_STOP=on"?

Yours,
Laurenz Albe

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

Re: [GENERAL] psql question: aborting a "script"

[John McKown]

On Tue, Mar 15, 2016 at 9:57 AM, Melvin Davidson 
wrote:

>
>
> On Tue, Mar 15, 2016 at 10:49 AM, John McKown <
> john.archie.mck...@gmail.com> wrote:
>
>> On Tue, Mar 15, 2016 at 9:38 AM, Adrian Klaver > > wrote:
>>
>>> On 03/15/2016 07:33 AM, John McKown wrote:
>>>
 I'm likely abusing the psql program. What I have is an awk program which
 reads a file and produces a number of INSERT INTO commands. I then feed
 these commands into psql to execute them. Yes, a Perl program would be a
 better idea. Anyway, sometimes the commands are rejected due to some
 problem, such as duplicate primary key. What I wish is that the psql
 command had a switch, or control command, which would say "exit from
 psql if anything fails". To me, this is like the BASH "set -e" command
 for a shell script. Does this sound like a useful addition. Or am I just
 missing where it already exists?

>>>
>>> Would this help?:
>>>
>>
>> ​Well, actually, no. It does force a ROLLBACK, but the individual INSERT
>> INTO commands are still being read and rejected, one by one. And there are
>> literally _thousands_ of them. It is not a "problem", per se. It's just
>> that it is "wasting" time and effort on the part of the system.
>>
>> If you're wonder why I do it this way, it is because the commands that I
>> generate are simple SQL standard commands. And they can be fed into
>> programs which update different SQL data bases, such as Postgresql (psql
>> command), MariaDB (mysql command), SQLite3 (sqlite3 command), and so forth.
>> Basically, I'm lazy and don't want to code multiple RDMS-oriented commands,
>> or have a single command which can interface with multiple RDMS systems. ​
>>
>>
>>
>>>
>>> http://www.postgresql.org/docs/9.5/interactive/app-psql.html
>>> "-1
>>> --single-transaction
>>>
>>> When psql executes a script, adding this option wraps BEGIN/COMMIT
>>> around the script to execute it as a single transaction. This ensures that
>>> either all the commands complete successfully, or no changes are applied.
>>>
>>> If the script itself uses BEGIN, COMMIT, or ROLLBACK, this option
>>> will not have the desired effects. Also, if the script contains any command
>>> that cannot be executed inside a transaction block, specifying this option
>>> will cause that command (and hence the whole transaction) to fail.
>>>
>>> "
>>> --
>>> Adrian Klaver
>>> adrian.kla...@aklaver.com
>>>
>>
>>
>>
>> --
>> A fail-safe circuit will destroy others. -- Klipstein
>>
>> Maranatha! <><
>> John McKown
>>
>
>
> What you really want is
> "ON_ERROR_STOP
>
> By default, command processing continues after an error. When this
> variable is set to on, processing will instead stop immediately. In
> interactive mode, psql will return to the command prompt; otherwise, psql
> will exit, returning error code 3 to distinguish this case from fatal error
> conditions, which are reported using error code 1. In either case, any
> currently running scripts (the top-level script, if any, and any other
> scripts which it may have in invoked) will be terminated immediately. If
> the top-level command string contained multiple SQL commands, processing
> will stop with the current command.
> "
> So just
> SET ON_ERROR_STOP = ON
> before any other statements
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.
>

​We have a winner! I knew I was overlooking something. Thanks.​


-- 
A fail-safe circuit will destroy others. -- Klipstein

Maranatha! <><
John McKown

Re: [GENERAL] psql question: aborting a "script"

[Melvin Davidson]

On Tue, Mar 15, 2016 at 10:49 AM, John McKown 
wrote:

> On Tue, Mar 15, 2016 at 9:38 AM, Adrian Klaver 
> wrote:
>
>> On 03/15/2016 07:33 AM, John McKown wrote:
>>
>>> I'm likely abusing the psql program. What I have is an awk program which
>>> reads a file and produces a number of INSERT INTO commands. I then feed
>>> these commands into psql to execute them. Yes, a Perl program would be a
>>> better idea. Anyway, sometimes the commands are rejected due to some
>>> problem, such as duplicate primary key. What I wish is that the psql
>>> command had a switch, or control command, which would say "exit from
>>> psql if anything fails". To me, this is like the BASH "set -e" command
>>> for a shell script. Does this sound like a useful addition. Or am I just
>>> missing where it already exists?
>>>
>>
>> Would this help?:
>>
>
> ​Well, actually, no. It does force a ROLLBACK, but the individual INSERT
> INTO commands are still being read and rejected, one by one. And there are
> literally _thousands_ of them. It is not a "problem", per se. It's just
> that it is "wasting" time and effort on the part of the system.
>
> If you're wonder why I do it this way, it is because the commands that I
> generate are simple SQL standard commands. And they can be fed into
> programs which update different SQL data bases, such as Postgresql (psql
> command), MariaDB (mysql command), SQLite3 (sqlite3 command), and so forth.
> Basically, I'm lazy and don't want to code multiple RDMS-oriented commands,
> or have a single command which can interface with multiple RDMS systems. ​
>
>
>
>>
>> http://www.postgresql.org/docs/9.5/interactive/app-psql.html
>> "-1
>> --single-transaction
>>
>> When psql executes a script, adding this option wraps BEGIN/COMMIT
>> around the script to execute it as a single transaction. This ensures that
>> either all the commands complete successfully, or no changes are applied.
>>
>> If the script itself uses BEGIN, COMMIT, or ROLLBACK, this option
>> will not have the desired effects. Also, if the script contains any command
>> that cannot be executed inside a transaction block, specifying this option
>> will cause that command (and hence the whole transaction) to fail.
>>
>> "
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com
>>
>
>
>
> --
> A fail-safe circuit will destroy others. -- Klipstein
>
> Maranatha! <><
> John McKown
>


What you really want is
"ON_ERROR_STOP

By default, command processing continues after an error. When this variable
is set to on, processing will instead stop immediately. In interactive
mode, psql will return to the command prompt; otherwise, psql will exit,
returning error code 3 to distinguish this case from fatal error
conditions, which are reported using error code 1. In either case, any
currently running scripts (the top-level script, if any, and any other
scripts which it may have in invoked) will be terminated immediately. If
the top-level command string contained multiple SQL commands, processing
will stop with the current command.
"
So just
SET ON_ERROR_STOP = ON
before any other statements
-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] psql question: aborting a "script"

[John McKown]

On Tue, Mar 15, 2016 at 9:38 AM, Adrian Klaver 
wrote:

> On 03/15/2016 07:33 AM, John McKown wrote:
>
>> I'm likely abusing the psql program. What I have is an awk program which
>> reads a file and produces a number of INSERT INTO commands. I then feed
>> these commands into psql to execute them. Yes, a Perl program would be a
>> better idea. Anyway, sometimes the commands are rejected due to some
>> problem, such as duplicate primary key. What I wish is that the psql
>> command had a switch, or control command, which would say "exit from
>> psql if anything fails". To me, this is like the BASH "set -e" command
>> for a shell script. Does this sound like a useful addition. Or am I just
>> missing where it already exists?
>>
>
> Would this help?:
>

​Well, actually, no. It does force a ROLLBACK, but the individual INSERT
INTO commands are still being read and rejected, one by one. And there are
literally _thousands_ of them. It is not a "problem", per se. It's just
that it is "wasting" time and effort on the part of the system.

If you're wonder why I do it this way, it is because the commands that I
generate are simple SQL standard commands. And they can be fed into
programs which update different SQL data bases, such as Postgresql (psql
command), MariaDB (mysql command), SQLite3 (sqlite3 command), and so forth.
Basically, I'm lazy and don't want to code multiple RDMS-oriented commands,
or have a single command which can interface with multiple RDMS systems. ​



>
> http://www.postgresql.org/docs/9.5/interactive/app-psql.html
> "-1
> --single-transaction
>
> When psql executes a script, adding this option wraps BEGIN/COMMIT
> around the script to execute it as a single transaction. This ensures that
> either all the commands complete successfully, or no changes are applied.
>
> If the script itself uses BEGIN, COMMIT, or ROLLBACK, this option will
> not have the desired effects. Also, if the script contains any command that
> cannot be executed inside a transaction block, specifying this option will
> cause that command (and hence the whole transaction) to fail.
>
> "
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>



-- 
A fail-safe circuit will destroy others. -- Klipstein

Maranatha! <><
John McKown

Re: [GENERAL] psql question: aborting a "script"

[Adrian Klaver]

On 03/15/2016 07:33 AM, John McKown wrote:

I'm likely abusing the psql program. What I have is an awk program which
reads a file and produces a number of INSERT INTO commands. I then feed
these commands into psql to execute them. Yes, a Perl program would be a
better idea. Anyway, sometimes the commands are rejected due to some
problem, such as duplicate primary key. What I wish is that the psql
command had a switch, or control command, which would say "exit from
psql if anything fails". To me, this is like the BASH "set -e" command
for a shell script. Does this sound like a useful addition. Or am I just
missing where it already exists?


Would this help?:

http://www.postgresql.org/docs/9.5/interactive/app-psql.html
"-1
--single-transaction

When psql executes a script, adding this option wraps BEGIN/COMMIT 
around the script to execute it as a single transaction. This ensures 
that either all the commands complete successfully, or no changes are 
applied.


If the script itself uses BEGIN, COMMIT, or ROLLBACK, this option 
will not have the desired effects. Also, if the script contains any 
command that cannot be executed inside a transaction block, specifying 
this option will cause that command (and hence the whole transaction) to 
fail.

"


--
A fail-safe circuit will destroy others. -- Klipstein

Maranatha! <><
John McKown



--
Adrian Klaver
adrian.kla...@aklaver.com


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

[GENERAL] psql question: aborting a "script"

[John McKown]

I'm likely abusing the psql program. What I have is an awk program which
reads a file and produces a number of INSERT INTO commands. I then feed
these commands into psql to execute them. Yes, a Perl program would be a
better idea. Anyway, sometimes the commands are rejected due to some
problem, such as duplicate primary key. What I wish is that the psql
command had a switch, or control command, which would say "exit from psql
if anything fails". To me, this is like the BASH "set -e" command for a
shell script. Does this sound like a useful addition. Or am I just missing
where it already exists?

-- 
A fail-safe circuit will destroy others. -- Klipstein

Maranatha! <><
John McKown

Re: [GENERAL] psql question

[Little, Douglas]

Thanks to steve and Al for the suggestions.

I did get the variable  concatenation solution to work.
\set env `echo $TARGETSERVER`
\echo env :env
\set envfile 'P1GP1_ETL_STAGE_TBLS_BIO6113_100.':env'.sql'
\echo envfile :envfile
  -- envfile P1GP1_ETL_STAGE_TBLS_BIO6113_100.DEV.sql

However it seems that I can't use the psql variables within sql.
Without quotes the variable name is used in the sql and the table is created, 
but since it's created with the variable name and not value it won't execute.
With quotes,  the variable  gets resolved but the syntax is invalid.  The 
syntax requires that the  file location be in quotes.

I'm using greenplum's external table feature and the external filename needs to 
change by environment.

Create external table  (
Col_a 
)
LOCATION (
'gphdfs://':filepath'/DimSiteVisit/part*'
)


Runtime log
ERROR:  syntax error at or near ":"
LINE 44: 'gphdfs://':filepath'/DimSiteVisit/part*'

I think I'm going to shift down to using shell features.


Thanks again for the help.

From: Steve Crawford [mailto:scrawf...@pinpointresearch.com]
Sent: Wednesday, January 30, 2013 4:49 PM
To: Little, Douglas
Cc: PostgreSQL General (pgsql-general@postgresql.org)
Subject: Re: [GENERAL] psql question

On 01/30/2013 01:51 PM, Little, Douglas wrote:
I'm looking for a way where I can tailor DDL scripts for deployment with 
environment variables.
Support I have a requirement to prefix table names with dev_ , fqa_, or prod_

I'd like to have a file for each env with their own unique settings - host, 
dbname
Dev.sql
\set env dev
Fqa
\set env fqa

prod
\set env prod

and then

my deployment script would have
ddl.sql
\i :env.sql
Create table schema.:env_tablename


I tried it and didn't work.
p1gp1=> \set env dev
p1gp1=> \i P1GP1_ETL_STAGE_TBLS_BIO6113_100.:env.sql
P1GP1_ETL_STAGE_TBLS_BIO6113_100.:env.sql: No such file or directory

Any thoughts on how I might get this to work?

Perhaps try concatenating variables then executing the result. For example, 
given a file "foo.psql" containing "select now();" and "bar.psql" containing 
"select 'Hello world';"

steve@[local] => \set env foo
steve@[local] => \set scriptname :env '.psql'
steve@[local] => \i :scriptname
  now
---
 2013-01-30 14:45:36.423836-08

steve@[local] => \set env bar
steve@[local] => \set scriptname :env '.psql'
steve@[local] => \i :scriptname
  ?column?
-
 Hello world

Cheers,
Steve

Re: [GENERAL] psql question

[Tom Lane]

Steve Crawford  writes:
> On 01/30/2013 01:51 PM, Little, Douglas wrote:
>> p1gp1=> \set env dev
>> p1gp1=> \i P1GP1_ETL_STAGE_TBLS_BIO6113_100.:env.sql
>> P1GP1_ETL_STAGE_TBLS_BIO6113_100.:env.sql: No such file or directory
>> 
>> Any thoughts on how I might get this to work?

> Perhaps try concatenating variables then executing the result.

FWIW, Douglas' original coding works for me in 9.2 and HEAD:

regression=# \set env dev
regression=# \i P1GP1_ETL_STAGE_TBLS_BIO6113_100.:env.sql
P1GP1_ETL_STAGE_TBLS_BIO6113_100.dev.sql: No such file or directory

I think we fixed some bugs associated with expansion of variable names
embedded within larger words, but didn't back-patch for fear of breaking
existing apps in a minor release.  Your alternative looks to me like
it's basically dodging those bugs ...

regards, tom lane


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

Re: [GENERAL] psql question

[Steve Crawford]

On 01/30/2013 01:51 PM, Little, Douglas wrote:


I'm looking for a way where I can tailor DDL scripts for deployment 
with environment variables.


Support I have a requirement to prefix table names with dev_ , fqa_, 
or prod_


I'd like to have a file for each env with their own unique settings -- 
host, dbname


Dev.sql

\set env dev

Fqa

\set env fqa

prod

\set env prod

and then

my deployment script would have

ddl.sql

\i :env.sql

Create table schema.:env_tablename

I tried it and didn't work.

p1gp1=> \set env dev

p1gp1=> \i P1GP1_ETL_STAGE_TBLS_BIO6113_100.:env.sql

P1GP1_ETL_STAGE_TBLS_BIO6113_100.:env.sql: No such file or directory

Any thoughts on how I might get this to work?



Perhaps try concatenating variables then executing the result. For 
example, given a file "foo.psql" containing "select now();" and 
"bar.psql" containing "select 'Hello world';"


steve@[local] => \set env foo
steve@[local] => \set scriptname :env '.psql'
steve@[local] => \i :scriptname
  now
---
 2013-01-30 14:45:36.423836-08

steve@[local] => \set env bar
steve@[local] => \set scriptname :env '.psql'
steve@[local] => \i :scriptname
  ?column?
-
 Hello world

Cheers,
Steve

[GENERAL] psql question

[Little, Douglas]

I'm looking for a way where I can tailor DDL scripts for deployment with 
environment variables.
Support I have a requirement to prefix table names with dev_ , fqa_, or prod_

I'd like to have a file for each env with their own unique settings - host, 
dbname
Dev.sql
\set env dev
Fqa
\set env fqa

prod
\set env prod

and then

my deployment script would have
ddl.sql
\i :env.sql
Create table schema.:env_tablename


I tried it and didn't work.
p1gp1=> \set env dev
p1gp1=> \i P1GP1_ETL_STAGE_TBLS_BIO6113_100.:env.sql
P1GP1_ETL_STAGE_TBLS_BIO6113_100.:env.sql: No such file or directory

Any thoughts on how I might get this to work?

Thanks




Doug Little

Sr. Data Warehouse Architect | Business Intelligence Architecture | Orbitz 
Worldwide
500 W. Madison, Suite 1000  Chicago IL 60661| Office 312.260.2588 | Fax 
312.894.5164 | Cell 847-997-5741
douglas.lit...@orbitz.com
 [cid:image001.jpg@01CDFF01.6452A380]   orbitz.com | 
ebookers.com | 
hotelclub.com | 
cheaptickets.com | 
ratestogo.com | 
asiahotels.com

<>

[GENERAL] psql question on echo output

[John DeSoi]

I have output set to go to a file with \o. Now I want to process a file 
with \i. With ECHO set to all, I would like the statement to be echoed 
in the file before the output, but instead it comes to the screen 
(standard output). Is there some setting or trick I missed to 
accomplish this? I realize I can get all output in the file with 
something like psql -f file > out, but I want to use the \i option so 
the connection state and psql settings can be persisted across 
different file executions.

Thanks,
John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match

Re: [GENERAL] Psql Question

[Peter Eisentraut]

Danny writes:

> mydb=# INSERT INTO Customer 
>(Customer_ID,Customer_Name,Customer_Address,Customer_Email)
> mydb-# VALUES ('1','Danny Ho','99 Second Ave, Kingswood','[EMAIL PROTECTED]'),
> mydb-# ('2','Randal Handel','54 Oxford Road, Cambridge','[EMAIL PROTECTED]')
> mydb-# ;
> 
> -and I get the following errors : 
> 
> ERROR:  parser: parse error at or near ","

PostgreSQL doesn't support this syntax (yet). You need to split it into
two INSERT commands.

-- 
Peter Eisentraut  [EMAIL PROTECTED]   http://yi.org/peter-e/

Re: [GENERAL] Psql Question

[He Weiping (Laser Henry)]

Danny wrote:

> - Hello
> - I had previous experience with Access and MySQL.
>
> -Situation
>
> - I am trying to create the equvilant of the following which is a mysql
> command.
>
> - Queston
> - But I cannot figure out how to do this is postgresql
>
> "mysql -u root -p mydb < mydb.dump"
>

I think:
psql -u somebody -d template1 < yourdb.dump
would work.

>
> - I was trying to create a test database using the following commands using a
> very cliche example . This command works on mySQL and should be part of the
> ANSI SQL standard
>
> mydb=# INSERT INTO Customer 
>(Customer_ID,Customer_Name,Customer_Address,Customer_Email)
> mydb-# VALUES ('1','Danny Ho','99 Second Ave, Kingswood','[EMAIL PROTECTED]'),
> mydb-# ('2','Randal Handel','54 Oxford Road, Cambridge','[EMAIL PROTECTED]')
> mydb-# ;
>

you can't insert two values at the same time, you would have to use two INSERT.

>
> -and I get the following errors :
>
> ERROR:  parser: parse error at or near ","
>
> Looking forwrd to your feedback.,
>
> dannyh
>
> [EMAIL PROTECTED]

[GENERAL] Psql Question

[Danny]

- Hello
- I had previous experience with Access and MySQL.

-Situation

- I am trying to create the equvilant of the following which is a mysql
command. 

- Queston 
- But I cannot figure out how to do this is postgresql 

"mysql -u root -p mydb < mydb.dump"

- I was trying to create a test database using the following commands using a
very cliche example . This command works on mySQL and should be part of the
ANSI SQL standard


mydb=# INSERT INTO Customer (Customer_ID,Customer_Name,Customer_Address,Customer_Email)
mydb-# VALUES ('1','Danny Ho','99 Second Ave, Kingswood','[EMAIL PROTECTED]'),
mydb-# ('2','Randal Handel','54 Oxford Road, Cambridge','[EMAIL PROTECTED]')
mydb-# ;

-and I get the following errors : 

ERROR:  parser: parse error at or near ","

Looking forwrd to your feedback.,

dannyh

[EMAIL PROTECTED]