4.23 psycopg2.ProgrammingError阻塞经历 #280

vieyahn2017 · 2019-04-23T14:25:48Z

psycopg2.ProgrammingError: subprogram body is not ended correctly at end of input
LINE 1: BEGIN

vieyahn2017 · 2019-04-23T14:26:11Z

import psycopg2
conn = psycopg2.connect(database='testXXX', user='iXXX', password='BiXXXX', host='8.XXXXX', port=25308)
cursor = conn.cursor()
sql = """SELECT 1 AS foo;"""
cursor.execute(sql)

这么简单的代码，报错

vieyahn2017 · 2019-04-23T14:27:03Z

问题是postgres数据库版本导致的，因为这个错，研究了postgres和psycopg2很多底层的东西

vieyahn2017 · 2019-04-23T14:28:20Z

使用libpq调用PostgreSQL的自定义函数

https://www.cnblogs.com/yishuyuan/p/6519069.html

vieyahn2017 · 2019-04-24T01:40:18Z

业务需求：pg中有个照片表，需要将照片信息及数据插入到该表中。并可能伴随增删改查动作。本节只处理插入操作。

照片表创建：

CREATE TABLE ist_image
(
   isc_imgid bigint NOT NULL,
   isc_imgname character varying(64),
   isc_updatetime timestamp without time zone,
   isc_id bigint, -- 插入顺序编号(由序列生成)
   isc_imgdata bytea,
   CONSTRAINT ist_image_pkey PRIMARY KEY (isc_imgid)
)

自定义函数创建：

create or replace function func_insertimage(in nImgID bigint, in szImgName varchar, in pImgData bytea) 
returns integer     --必须有返回值
as 
$$
begin
    insert into ist_image(isc_imgid, isc_imgname, isc_updatetime, isc_id, isc_imgdata) 
    values(nImgID, szImgName, now()::timestamp(0), nextval('iss_seq_isc_id'), pImgData);
    return 0;
end;
$$
language plpgsql;

照片数据结构：

class ImageInfo {
public:
    __int64        nImgNumber;
    char        szImgName[64];
    int            nImgDataLen;
    unsigned char*    pImgData;
}

插入函数：

//注意该函数是有BUG的
unsigned long long htonll(unsigned long long val) {  
    return (((unsigned long long )htonl((int)((val << 32) >> 32))) << 32) | (unsigned int)htonl((int)(val >> 32));   
}

int GP_TestInsert(deque<ImageInfo>& deqImages, char* szConnStr) {
    int nParamNum = 3;                    //参数个数
    int paramLens[3] = {0};                //参数长度
    int paramFormats[3] = {1, 1, 1};    //参数是二进制格式(1表示二进制格式 0表示文本格式)
    int nReturnForm = 0;                //返回值是文本格式
    unsigned long long ullHID = 0, ullNID = 0;

    //函数调用语句
    TCHAR szSQL[1024] = {0};
    _stprintf_s(szSQL, 1024, _T("select func_insertimage($1::bigint, $2::character varying, $3::bytea)"));
    
    //连接GP数据库
    PGConn* pConn = PQconnectdb(szConnStr);
    if (CONNECTION_OK != PQstatus(pConn)) {
        printf("GP_TestInsert Connect failed. ErrMsg: %s", PQerrorMessage(pConn));
        return -1;
    }

    //开始批量插入事务(显式BEGIN会开始一个事务)
    PGresult* pRes = PQexec(pConn, "BEGIN");
    if (PQresultStatus(pRes) != PGRES_COMMAND_OK) {
        printf("GP_TestInsert Exec BEGIN command failed. ErrMsg: %s", PQerrorMessage(pConn));
        PQclear(pRes);
        return -1;
    }
    PQclear(pRes);    //任何时候不再需要 PGresult 时，应该PQclear它来避免内存泄露
    
    //循环插入数据
    for (deque<ImageInfo>::iterator it = deqImages.begin(); it != deqImages.end(); ++it) 
    {
        ullHID = (unsigned long long)(it->nImgNumber);
        ullNID = htonll(ullHID);
        //value
        const char* const pszParamValue[3] = { (char*)&ullNID, it->szImgName, (char*)it->pImgData };

        //Length
        paramLens[0] = sizeof(__int64);
        paramLens[1] = (int)strlen(it->szImgName);    //字符串类型长度要注意
        paramLens[2] = it->nImgDataLen;

        //execse
        pRes = PQexecParams(pConn, szSQL, 3, NULL, (const char**)pszParamValue, paramLens, paramFormats, nReturnForm);
        ExecStatusType resState = PQresultStatus(pRes);
        if (PGRES_TUPLES_OK != resState || strcmp(PQgetvalue(pRes, 0, 0), "0") != 0) {
            printf("GP_TestInsert Exec function failed. ErrCode: %d ErrInfo: %s ErrDesc: %s"), 
                resState, PQresStatus(resState), PQresultErrorMessage(pRes));
            return -1;
        }

        PQclear(pRes);
    }

    //结束事务(做完此步才会进行commit)
    pRes = PQexec(pConn, "END");
    PQclear(pRes);

    return 0;
}

这里面有几个要注意的地方：

1.每次连接、执行SQL语句要注意检查状态，判断是否执行成功；

2.对于整形数据的插入，要进行字节序转换，并根据整形结构的长度区别使用不同的字节序转换函数；

3.PostgreSQL数据库好像是默认开启AutoCommit的，若不想其自动commit，可以采用以下两种办法：

1）关闭数据库的自动commit属性，不过这种方式会导致管理时，出现一些烦人的提示；

2）每次显式调用BEGIN命令，一个事务(很多次条语句执行过后)结束后，执行END命令来commit，这种方式更灵活，个人更喜欢这种方式；

4.任何时候不再需要PGresult时，应该调用PQclear来清理它，避免内存泄露，具体请参见libpq说明文档；

当然，使用insert方式插入数据，对于PostgreSQL来说，并不是高效的方式，这里只是用这个例子来简单说明使用libpq库调用自定义函数的方式，后续再补充使用更高效方式来插入数据的例子，对于PostgreSQL来说，我也仅仅是初学者，若文中有错误烦请指正。

照片表创建：

CREATE TABLE ist_image
(
   isc_imgid bigint NOT NULL,
   isc_imgname character varying(64),
   isc_updatetime timestamp without time zone,
   isc_id bigint, -- 插入顺序编号(由序列生成)
   isc_imgdata bytea,
   CONSTRAINT ist_image_pkey PRIMARY KEY (isc_imgid)
)

自定义函数创建：

create or replace function func_insertimage(in nImgID bigint, in szImgName varchar, in pImgData bytea) 
returns integer     --必须有返回值
as 
$$
begin
    insert into ist_image(isc_imgid, isc_imgname, isc_updatetime, isc_id, isc_imgdata) 
    values(nImgID, szImgName, now()::timestamp(0), nextval('iss_seq_isc_id'), pImgData);
    return 0;
end;
$$
language plpgsql;

照片数据结构：

class ImageInfo {
public:
    __int64        nImgNumber;
    char        szImgName[64];
    int            nImgDataLen;
    unsigned char*    pImgData;
}

插入函数：

//注意该函数是有BUG的
unsigned long long htonll(unsigned long long val) {  
    return (((unsigned long long )htonl((int)((val << 32) >> 32))) << 32) | (unsigned int)htonl((int)(val >> 32));   
}

int GP_TestInsert(deque<ImageInfo>& deqImages, char* szConnStr) {
    int nParamNum = 3;                    //参数个数
    int paramLens[3] = {0};                //参数长度
    int paramFormats[3] = {1, 1, 1};    //参数是二进制格式(1表示二进制格式 0表示文本格式)
    int nReturnForm = 0;                //返回值是文本格式
    unsigned long long ullHID = 0, ullNID = 0;

    //函数调用语句
    TCHAR szSQL[1024] = {0};
    _stprintf_s(szSQL, 1024, _T("select func_insertimage($1::bigint, $2::character varying, $3::bytea)"));
    
    //连接GP数据库
    PGConn* pConn = PQconnectdb(szConnStr);
    if (CONNECTION_OK != PQstatus(pConn)) {
        printf("GP_TestInsert Connect failed. ErrMsg: %s", PQerrorMessage(pConn));
        return -1;
    }

    //开始批量插入事务(显式BEGIN会开始一个事务)
    PGresult* pRes = PQexec(pConn, "BEGIN");
    if (PQresultStatus(pRes) != PGRES_COMMAND_OK) {
        printf("GP_TestInsert Exec BEGIN command failed. ErrMsg: %s", PQerrorMessage(pConn));
        PQclear(pRes);
        return -1;
    }
    PQclear(pRes);    //任何时候不再需要 PGresult 时，应该PQclear它来避免内存泄露
    
    //循环插入数据
    for (deque<ImageInfo>::iterator it = deqImages.begin(); it != deqImages.end(); ++it) 
    {
        ullHID = (unsigned long long)(it->nImgNumber);
        ullNID = htonll(ullHID);
        //value
        const char* const pszParamValue[3] = { (char*)&ullNID, it->szImgName, (char*)it->pImgData };

        //Length
        paramLens[0] = sizeof(__int64);
        paramLens[1] = (int)strlen(it->szImgName);    //字符串类型长度要注意
        paramLens[2] = it->nImgDataLen;

        //execse
        pRes = PQexecParams(pConn, szSQL, 3, NULL, (const char**)pszParamValue, paramLens, paramFormats, nReturnForm);
        ExecStatusType resState = PQresultStatus(pRes);
        if (PGRES_TUPLES_OK != resState || strcmp(PQgetvalue(pRes, 0, 0), "0") != 0) {
            printf("GP_TestInsert Exec function failed. ErrCode: %d ErrInfo: %s ErrDesc: %s"), 
                resState, PQresStatus(resState), PQresultErrorMessage(pRes));
            return -1;
        }

        PQclear(pRes);
    }

    //结束事务(做完此步才会进行commit)
    pRes = PQexec(pConn, "END");
    PQclear(pRes);

    return 0;
}

这里面有几个要注意的地方：

1.每次连接、执行SQL语句要注意检查状态，判断是否执行成功；

2.对于整形数据的插入，要进行字节序转换，并根据整形结构的长度区别使用不同的字节序转换函数；

3.PostgreSQL数据库好像是默认开启AutoCommit的，若不想其自动commit，可以采用以下两种办法：

1）关闭数据库的自动commit属性，不过这种方式会导致管理时，出现一些烦人的提示；

2）每次显式调用BEGIN命令，一个事务(很多次条语句执行过后)结束后，执行END命令来commit，这种方式更灵活，个人更喜欢这种方式；

4.任何时候不再需要PGresult时，应该调用PQclear来清理它，避免内存泄露，具体请参见libpq说明文档；

当然，使用insert方式插入数据，对于PostgreSQL来说，并不是高效的方式，这里只是用这个例子来简单说明使用libpq库调用自定义函数的方式，后续再补充使用更高效方式来插入数据的例子，对于PostgreSQL来说，我也仅仅是初学者，若文中有错误烦请指正。

vieyahn2017 · 2019-04-24T01:46:07Z

postgresql从入门到菜鸟（六）libpq连接postgres数据库

https://blog.csdn.net/s465689853/article/details/81452568
上一期讲了一些如何通过jdbc连接postgresql数据库，这一期准备说一说如何通过libpq连接postgresql数据库。
上一期的jdbc是postgresql的java语言应用程序接口，这一期的libpq对应的是postgresql的C语言应用程序接口。

这里依旧分为三个模块来讲：

一.获取连接
二.执行select语句
三.执行insert，delete，update语句

第一部分：获取连接

要使用libpq连接postgresql数据库首先要引用libpq的头文件libpq-fe.h

然后创建连接字符串和PGconn结构体，设定连接字符串后就可以通过连接字符串获取连接了

const char *conninfo;
PGconn     *conn;
conninfo = "host=127.0.0.1 port=5433 dbname=smoondb user=postgres";
conn = PQconnectdb(conninfo);

注意如果这里缺省连接关键字的话，libpq将使用缺省关键字的默认值进行连接，连接关键字的默认值可以参考官方手册。

在jdbc中，我们可以通过try catch的方式进行连接错误捕获，在libpq中也提供了判断连接是否成功建立的接口：

 if (PQstatus(conn) != CONNECTION_OK)
 {
        fprintf(stderr, "Connection to database failed: %s",
                PQerrorMessage(conn));
        exit_nicely(conn);//关闭连接并执行清理操作
 }

这里如果连接不成功的话会返回服务端返回的错误message，如果成功的话后面就可以通过conn操作数据库了。

第二部分：执行select语句

在获取到PGconn实例后，我们就可利用PGconn实例进行对数据库的操作了，和jdbc不同，jdbc中查询语句和更新语句会分别调用executeQuery()和executeUpdate()，而libpq中使用的是同一个接口。

Lipq执行sql命令的核心函数为PQexec(PGconn *conn, const char *command);其中第一个参数为连接。第二个为执行的命令，其中command字符串可以包含多条执行命令，需要注意的是如果不加入begin或者commit的关键字，command字符串中的所有命令将在一个事务中执行，并且只要其中一条失败，就会导致整个command执行失败。

PQexec函数的返回类型为PGresult，如果返回值为null，说明执行失败，可以通过PQerrorMessage()方法查看错误消息。

在libpg中，查询语句和更新语句都是通过PQexec函数执行，但是很明显对于这两种语句我们需要的返回值肯定不同，所以在解析PGresult时，libpq提供了不同的解析函数，这部分先说说解析查询结果用到的几个函数

PQnfields(PGresult *res)：用于获取结果集中列的数目
PQfname(PGresult *res,int i)：用于获取结果集中列的名称
PQntuples(PGresult *res)：用于获取结果集中行的数目
PQgetvalue(PGresult *res,int i,int j)：用于获取结果集中i行j列的值

比如下面这一段：

    res = PQexec(conn, "DECLARE myportal CURSOR FOR select * from pg_database");//为查询语句声明一个游标
    if (PQresultStatus(res) != PGRES_COMMAND_OK)//判断游标生成是否成功
    {
        fprintf(stderr, "DECLARE CURSOR failed: %s", PQerrorMessage(conn));
        PQclear(res);
        exit_nicely(conn);
    }
    //这里需要注意不再使用的PGresult需要及时释放，否则可能会造成内存泄漏
    PQclear(res);

    res = PQexec(conn, "FETCH ALL in myportal");//FETCH ALL表示从结果中取回全部数据
    if (PQresultStatus(res) != PGRES_TUPLES_OK)
    {
        fprintf(stderr, "FETCH ALL failed: %s", PQerrorMessage(conn));
        PQclear(res);
        exit_nicely(conn);
    }

    // 首先，打印出列名
    nFields = PQnfields(res);
    for (i = 0; i < nFields; i++)
        printf("%-15s", PQfname(res, i));
    printf("\n\n");

    // 接下来，打印出行 
    for (i = 0; i < PQntuples(res); i++)
    {
        for (j = 0; j < nFields; j++)
            printf("%-15s", PQgetvalue(res, i, j));//打印值
        printf("\n");
    }

    PQclear(res);

第三部分：执行insert，delete，update语句

对于select语句，我们在解析PGresult时需要的是结果集，所以在上面调用了以上几个函数，但是修改语句我们更关心的受影响的行数，所以对于修改语句可以通过PQntuples(const PGresult *res)获取受影响函数，其中对于PGresult而言它的成员中包含了所有执行的结果，不同的解析函数只是用来取得PGresult中相应的成员而已，有兴趣的同学可以去看一看PGresult结构体的实现，里面对于所有的成员都有比较清楚的描述。
下面贴一段执行插入语句的例子：

    res = PQexec(conn, "delete from student where sid=4;");
    if (PQresultStatus(res) != PGRES_COMMAND_OK)
    {
        fprintf(stderr, "delete failed: %s", PQerrorMessage(conn));
        PQclear(res);
        exit_nicely(conn);
    }
    printf("updatelin:%s",PQcmdTuples(res));//解析并打印受影响行数
    PQclear(res);

完整的例子程序可以在官方手册中找到这里就不再贴出了。

最后说说libpq程序的编译，对于写好的.c文件，可以执行以下命令生成可执行文件

gcc -L $libpath -I $includepath -lpq -o testlipq testlipq.c

vieyahn2017 · 2019-04-24T01:47:16Z

官方文档

PostgreSQL 9.6.12 Documentation
Chapter 32. libpq - C Library

https://www.postgresql.org/docs/9.6/libpq-exec.html#LIBPQ-EXEC-SELECT-INFO

vieyahn2017 closed this as completed Mar 27, 2020

vieyahn2017 mentioned this issue Apr 7, 2020

1.29 【postgresql】收集tips #253

Open

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

4.23 psycopg2.ProgrammingError阻塞经历 #280

4.23 psycopg2.ProgrammingError阻塞经历 #280

vieyahn2017 commented Apr 23, 2019

vieyahn2017 commented Apr 23, 2019

vieyahn2017 commented Apr 23, 2019

vieyahn2017 commented Apr 23, 2019

vieyahn2017 commented Apr 24, 2019

vieyahn2017 commented Apr 24, 2019

vieyahn2017 commented Apr 24, 2019

4.23 psycopg2.ProgrammingError阻塞经历 #280

4.23 psycopg2.ProgrammingError阻塞经历 #280

Comments

vieyahn2017 commented Apr 23, 2019

vieyahn2017 commented Apr 23, 2019

vieyahn2017 commented Apr 23, 2019

vieyahn2017 commented Apr 23, 2019

使用libpq调用PostgreSQL的自定义函数

vieyahn2017 commented Apr 24, 2019

vieyahn2017 commented Apr 24, 2019

postgresql从入门到菜鸟（六）libpq连接postgres数据库

这里依旧分为三个模块来讲：

第一部分：获取连接

第二部分：执行select语句

第三部分：执行insert，delete，update语句

vieyahn2017 commented Apr 24, 2019