然后说一下这个，通过psql脚本返回值判断Greenplum任务是否执行成功

说明

本文描述问题及解决方法同样适用于 腾讯云 云数据仓库 PostgreSQL（CDWPG）。

背景

由于psql在执行sql脚本文件的过程中，默认是遇到错误继续执行不停止，所以导致我们无法通过其执行的最终返回值来判断该脚本是否有发生错误。

  • 用来测试的SQL文件
[gpadmincloud@mdw-snova-90g4jkrm ~]$ cat test.sql 
DROP TABLE IF EXISTS test_demo;
CREATE TABLE test_demo(name varchar, time timestamp);
INSERT INTO test_timestamp VALUES('test',now());
INSERT INTO test_timestamp VALUES('test',111);
SELECT * FROM test_timestamp;
[gpadmincloud@mdw-snova-90g4jkrm ~]$

测试文件的第4条SQL，对time字段写入了一个非法时间戳，所以该条SQL一定会执行出错。

[gpadmincloud@mdw-snova-90g4jkrm ~]$ psql -d dy_test -U dy1 -h 10.0.38.133 -f test.sql
psql:test.sql:1: NOTICE:  table "test_demo" does not exist, skipping
DROP TABLE
psql:test.sql:2: NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'name' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE TABLE
INSERT 0 1
psql:test.sql:4: ERROR:  column "time" is of type timestamp without time zone but expression is of type integer
LINE 1: INSERT INTO test_timestamp VALUES('test',111);
                                                 ^
HINT:  You will need to rewrite or cast the expression.
 name |            time            
------+----------------------------
 test | 2021-03-17 19:00:29.765707
 test | 2021-03-17 19:47:04.543494
(2 rows)
 
[gpadmincloud@mdw-snova-90g4jkrm ~]$ echo $?
0
[gpadmincloud@mdw-snova-90g4jkrm ~]$

经过测试，确实是报错了，但是执行并没有中断，而是继续执行了下去。相应的，最终返回值也是不符合预期的0（true），这样则无法根据最终返回值来判断SQL脚本是否执行成功了。所以，这里我们介绍两个方案来实现该需求。

解决方案

方案一：使用psql -c来代替psql -f

[gpadmincloud@mdw-snova-90g4jkrm ~]$ cat test.sh 
#!/bin/bash
 
psql -d dy_test -U dy1 -h 10.0.38.133 -c "
DROP TABLE IF EXISTS test_demo;
CREATE TABLE test_demo(name varchar, time timestamp);
INSERT INTO test_timestamp VALUES('test',now());
INSERT INTO test_timestamp VALUES('test',111);
SELECT * FROM test_timestamp;"
[gpadmincloud@mdw-snova-90g4jkrm ~]$ bash test.sh 
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'name' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
ERROR:  column "time" is of type timestamp without time zone but expression is of type integer
LINE 5: INSERT INTO test_timestamp VALUES('test',111);
                                                 ^
HINT:  You will need to rewrite or cast the expression.
[gpadmincloud@mdw-snova-90g4jkrm ~]$ echo $?
1
[gpadmincloud@mdw-snova-90g4jkrm ~]$

可以看到，psql -c的方式，遇到错误会立刻中断，并返回一个非0（false）的值，可以满足我们通过返回值来判断SQL是否全部执行成功的需求。只是这种方式需要嵌套一层shell，不太优雅，介意慎入。

方案二：使用psql参数来指定执行遇错时中断

[gpadmincloud@mdw-snova-90g4jkrm ~]$ psql -d dy_test -U dy1 -h 10.0.38.133 -v ON_ERROR_STOP=1 -f test.sql
DROP TABLE
psql:test.sql:2: NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'name' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.
CREATE TABLE
INSERT 0 1
psql:test.sql:4: ERROR:  column "time" is of type timestamp without time zone but expression is of type integer
LINE 1: INSERT INTO test_timestamp VALUES('test',111);
                                                 ^
HINT:  You will need to rewrite or cast the expression.
[gpadmincloud@mdw-snova-90g4jkrm ~]$ echo $?
3
[gpadmincloud@mdw-snova-90g4jkrm ~]$

相对方案一，这种方式就显得更加灵活了，堪称完美。

特别注意

不管是使用方案一还是方案二，如果想确认SQL最终是否执行成功，那么一定要要判断返回值是否为0。因为与其他语言不同的是，bash shell的返回值为0才是成功，非0则是失败。再次注意这里，失败是非0，非0，非0，而不是1，重要的话说三遍。

正文完
2021-10-09