openGauss碰到的坑以及解决方法
主要针对opengauss现有的一些问题和坑，以及如何修改才能解决
·
下载地址以及编译方法
postgis for opengauss下载地址以及编译方法
openGauss-third_party: openGauss third party libraries - Gitee.com
编译注意的问题
按照官网上步骤安装postgis是可以的，但是装好之后create extension postgis会报autonomous transaction failed to create autonomous session 错误，原因就是默认无法免密登录。
为啥要免密登录呢，主要是opengauss会针对每个创建的plpgsql在plpgsql_validator函数中进行验证的过程中会InsertGsSource，将plpgsql 的源码记录到opengauss的系统表中， 这个过程走的是declare autonomous transaction，因此要走AttachSession函数，后面的就不在描述了。
当然这个InsertGsSource可以通过设置behavior_compat_options= 'skip_insert_gs_source'参数忽略。
1. 由于opengauss只有初始用户并且初始用户名字为运行openguass的操作系统名字才可以免密
控制这个的代码位置在（以3.0.2为例）：
hba.cpp的check_hba函数中控制
if (hba->conntype == ctLocal) {
/*
* For initdb user, we should check if the connection request from
* the installation enviroment.
*/
if (roleid == INITIAL_USER_ID) {
uid_t uid = 0;
gid_t gid = 0;
/* get the user id of the system, where client in */
if (getpeereid(port->sock, &uid, &gid) != 0) {
pfree_ext(hba);
/* Provide special error message if getpeereid is a stub */
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("Could not get user information on this platform")));
}
/*
* For connections from another coordinator, we could not
* get the userid. This case may also exist for other cases,
* like tools. (Usually happed when login with -h localhost
* or 127.0.0.1)
*/
if ((long)uid == USER_NULL_MASK) {
continue;
}
/*
* If the system username equals to the database username, continue
* the authorization without checking the usermap. If not, we should
* pay attentation to 'lateral ultra vires', which means, password is
* needed when login in in the non-installation enviroment under the
* same system user group.
*/
isUsernameSame= IsSysUsernameSameToDB(uid, port->user_name);
if (!isUsernameSame && hba->auth_method == uaTrust) {
hba->auth_method = get_default_auth_method(port->user_name);
}可以通过注释掉hba->auth_method = get_default_auth_method(port->user_name)来越过这个难问题。
（不加localhost的免密，+localhost或者127.0.0.1也无法免密）
2. localhost或者127.0.0.1 无法免密登录，导致create extension 创建失败，原因是创建扩展的语句会让gaussdb后台调用pqconnect函数链接数据库，失败的位置为
autonomoustransaction.cpp::AttachSession
void AutonomousSession::AttachSession(void)
{
if (m_conn != NULL) { // create session alike singleton
return;
}
/* create a connection info with current database info */
char connInfo[MAX_CONNINFO_SIZE];
char userName[NAMEDATALEN];
const char* dbName = get_database_name(u_sess->proc_cxt.MyDatabaseId);
if (dbName == NULL) {
ereport(ERROR, (errcode(ERRCODE_UNDEFINED_DATABASE), errmsg("database with OID %u does not exist",
u_sess->proc_cxt.MyDatabaseId)));
}
errno_t ret = snprintf_s(connInfo, sizeof(connInfo), sizeof(connInfo) - 1,
"dbname=%s port=%d host='localhost' application_name='autonomoustransaction' user=%s",
dbName, g_instance.attr.attr_network.PostPortNumber,
(char*)GetSuperUserName((char*)userName));
securec_check_ss_c(ret, "\0", "\0");
/* do the actual create session */
bool old = t_thrd.int_cxt.ImmediateInterruptOK;
t_thrd.int_cxt.ImmediateInterruptOK = true;
/* Allow cancel/die interrupts */
CHECK_FOR_INTERRUPTS();
AddSessionCount();
m_conn = PQconnectdb(connInfo);
PQsetNoticeProcessor(m_conn, ProcessorNotice, NULL);
t_thrd.int_cxt.ImmediateInterruptOK = old;
if (PQstatus(m_conn) != CONNECTION_OK) {
ereport(ERROR, (errcode(ERRCODE_PLPGSQL_ERROR),
errmsg("autonomous transaction failed to create autonomous session"),
errdetail("%s", PQerrorMessage(m_conn))));
}
}
当然可以通过把host='localhost'去掉来越过这个问题。
还有另外一个方法，就是用pgpassword机制来解决免密功能,默认pgpassword使用的是$HOME/.pgpas文件，其内容如下
其格式如下
hostname:port:database:username:password
localhost:15432:postgres:postgres:Esri@123
其中database地方必须是具体的库名，不支持all写法
但是opengauss3.0.2默认编译还不支持pgpassword 功能，需要启用SUPPORT_PGPASSFILE 预编译条件控制
可以用一下语句把该编译条件打开
./configure --prefix=/home/opengauss_302/opengGauss-server/dest --gcc-version=7.3.0 CC=g++ CFLAS='-O0' --3rd=/home/opengauss_302/binarylibs --enable-debug --enable-thread-safety --without-readline --without-zlib --with-libxml CPPFLAGS="-DSUPPORT_PGPASSFILE"由于3.0.2可能没有测试这部分代码，这部分代码还有编译错误，位置为
fe-connect.cpp:getPgPassFilename
static bool getPgPassFilename(char* pgpassfile)
{
char* passfile_env = gs_getenv_r("PGPASSFILE");
if (check_client_env(passfile_env) != NULL
/* use the literal path from the environment, if set */
check_strncpy_s(strncpy_s(pgpassfile, MAXPGPATH, passfile_env, strlen(passfile_env)));
else {
char homedir[MAXPGPATH];
if (!pqGetHomeDirectory(homedir, sizeof(homedir)))
return false;
check_sprintf_s(sprintf_s(pgpassfile, MAXPGPATH, "%s/%s", homedir, PGPASSFILE));
}
return true;
}
修改成
static bool getPgPassFilename(char* pgpassfile)
{
char* passfile_env = gs_getenv_r("PGPASSFILE");
if (check_client_env(passfile_env) != NULL)
{
/* use the literal path from the environment, if set */
check_strncpy_s(strncpy_s(pgpassfile, MAXPGPATH, passfile_env, strlen(passfile_env)));
}
else {
char homedir[MAXPGPATH];
if (!pqGetHomeDirectory(homedir, sizeof(homedir)))
return false;
check_sprintf_s(sprintf_s(pgpassfile, MAXPGPATH, "%s/%s", homedir, PGPASSFILE));
}
return true;
}
否则有编译错误，但是修改后，可以正常运行。
编译postgis的坑
为啥初始话用户非要免密登录呢，因为创建function和extension的时候后台会以初始话用户执行AttachSession函数登录后创建，如果不能免密登录，AttachSession会登录失败，然后就没有然后了。
编译postgis基本上按照链接上编译就可以了
但是编译postgis的时候需要将instrument 文件夹拷贝一份到dest/include/postgresql/server目录中一份，要不然会报找不到头文件的错误。
另外编译5.0.0版本上的postgis需要提前安装
yum install libtool-ltdl-devel ,否则编译gdal的时候会提示找不到ltdl.so的错误
坑1：
DECLARE
edit_session_info RECORD;
current_state_id BIGINT;
default_version_set BOOL default FALSE;
C_get_state_id CURSOR FOR
SELECT v.state_id
FROM sde.sde_versions v
WHERE v.name = 'DEFAULT' AND
UPPER(v.owner) = 'SDE';
BEGIN
return 1;
END;
不支持declare cursorname CURSOR FOR|IS 语法（可能是为了兼容oracle吧），其只支持
CURSOR cursorname FOR|IS . 如果要支持上面的语法，需要将/src/common/pl/plpgsql/src/gram.y 中的这部分代码修改成如下内容
坑2：
针对于这种plpgsql会报错
SELECT INTO l_entity (CASE WHEN l_domain_name IS NULL OR
l_domain_name = 'ST_GEOMETRY' THEN 'GEOMETRY'
WHEN l_domain_name = 'ST_GEOMCOLLECTION' THEN 'COLLECTION'
WHEN l_domain_name = 'ST_POINT' THEN 'POINT'
WHEN l_domain_name = 'ST_LINESTRING' THEN 'LINESTRING'
WHEN l_domain_name = 'ST_POLYGON' THEN 'POLYGON'
WHEN l_domain_name = 'ST_MULTIPOINT' THEN 'MULTIPOINT'
WHEN l_domain_name = 'ST_MULTILINESTRING' THEN 'MULTILINESTRING'
WHEN l_domain_name = 'ST_MULTIPOLYGON' THEN 'MULTIPOLYGON'
ELSE NULL
END);全部函数内容
CREATE OR REPLACE FUNCTION sde.geometry_type(i_schema character varying, i_table_name character varying, i_column_name character varying)
RETURNS text
LANGUAGE plpgsql
AS $function$
DECLARE
l_schema VARCHAR(128) DEFAULT NULL;
l_table VARCHAR(128) DEFAULT NULL;
l_tableName VARCHAR(128) DEFAULT NULL;
l_column VARCHAR(128) DEFAULT NULL;
l_udt_name VARCHAR(128) DEFAULT NULL;
l_domain_name VARCHAR(128) DEFAULT NULL;
l_zm VARCHAR(4) DEFAULT NULL;
l_multi_prefix VARCHAR(6) DEFAULT NULL;
l_result TEXT DEFAULT NULL;
l_entity TEXT DEFAULT NULL;
l_coordDim INTEGER DEFAULT NULL;
l_point BOOL DEFAULT FALSE;
l_line BOOL DEFAULT FALSE;
l_sline BOOL DEFAULT FALSE;
l_poly BOOL DEFAULT FALSE;
l_multi BOOL DEFAULT FALSE;
l_z BOOL DEFAULT FALSE;
l_m BOOL DEFAULT FALSE;
l_has_3d BOOL DEFAULT FALSE;
l_has_measure BOOL DEFAULT FALSE;
l_cad BOOL DEFAULT FALSE;
l_geometry BOOL DEFAULT FALSE;
l_collection BOOL DEFAULT FALSE;
l_is_st_geometry BOOL DEFAULT FALSE;
l_is_pg_geometry BOOL DEFAULT FALSE;
l_is_pg_geography BOOL DEFAULT FALSE;
l_is_empty BOOL DEFAULT FALSE;
SE_OGC_GEOMETRY CONSTANT INTEGER NOT NULL DEFAULT 0;
SE_OGC_POINT CONSTANT INTEGER NOT NULL DEFAULT 1;
SE_OGC_CURVE CONSTANT INTEGER NOT NULL DEFAULT 2;
SE_OGC_LINESTRING CONSTANT INTEGER NOT NULL DEFAULT 3;
SE_OGC_SURFACE CONSTANT INTEGER NOT NULL DEFAULT 4;
SE_OGC_POLYGON CONSTANT INTEGER NOT NULL DEFAULT 5;
SE_OGC_COLLECTION CONSTANT INTEGER NOT NULL DEFAULT 6;
SE_OGC_MULTIPOINT CONSTANT INTEGER NOT NULL DEFAULT 7;
SE_OGC_MULTICURVE CONSTANT INTEGER NOT NULL DEFAULT 8;
SE_OGC_MULTILINESTRING CONSTANT INTEGER NOT NULL DEFAULT 9;
SE_OGC_MULTISURFACE CONSTANT INTEGER NOT NULL DEFAULT 10;
SE_OGC_MULTIPOLYGON CONSTANT INTEGER NOT NULL DEFAULT 11;
BEGIN
IF i_schema IS NULL THEN
l_schema := session_user;
ELSE
l_schema := LOWER(i_schema);
END IF;
l_table := LOWER(i_table_name);
l_column := LOWER(i_column_name);
-- Check if the object is a multiversioned view.
BEGIN
SELECT INTO l_tableName table_name FROM sde.sde_table_registry
WHERE owner = l_schema AND imv_view_name = l_table;
IF FOUND THEN
l_table := l_tableName;
END IF;
EXCEPTION WHEN undefined_table THEN
NULL;
END;
-- Check if the object is registered with geodatabase
BEGIN
SELECT ((a.eflags::bit(32) & (1 << 1)::bit(32)) != 0::bit(32))::BOOL AS has_point,
((a.eflags::bit(32) & (1 << 2)::bit(32)) != 0::bit(32))::BOOL AS has_line,
((a.eflags::bit(32) & (1 << 3)::bit(32)) != 0::bit(32))::BOOL AS has_sline,
((a.eflags::bit(32) & (1 << 4)::bit(32)) != 0::bit(32))::BOOL AS has_poly,
((a.eflags::bit(32) & (1 << 18)::bit(32)) != 0::bit(32))::BOOL AS is_multi,
((a.eflags::bit(32) & (1 << 16)::bit(32)) != 0::bit(32))::BOOL AS is_3d,
((a.eflags::bit(32) & (1 << 19)::bit(32)) != 0::bit(32))::BOOL AS is_measure,
((a.eflags::bit(32) & (1 << 22)::bit(32)) != 0::bit(32))::BOOL AS is_CAD,
((a.eflags::bit(32) & (1 << 26)::bit(32)) != 0::bit(32))::BOOL AS is_ST_Geometry,
((a.eflags::bit(32) & (1 << 27)::bit(32)) != 0::bit(32))::BOOL AS is_PG_Geometry,
((a.eflags::bit(32) & (1 << 15)::bit(32)) != 0::bit(32))::BOOL AS is_PG_Geography
INTO l_point,l_line,l_sline,l_poly,l_multi,l_has_3d,l_has_measure,
l_cad,l_is_st_geometry,l_is_pg_geometry,l_is_pg_geography
FROM sde.sde_layers a
WHERE a.owner = l_schema
AND a.table_name = l_table
AND a.spatial_column = l_column;
EXCEPTION WHEN undefined_table THEN
NULL;
END;
IF FOUND THEN
-- Registered with geodatabase
IF l_is_st_geometry = TRUE THEN
l_udt_name := 'ST_GEOMETRY';
ELSIF l_is_pg_geometry = TRUE THEN
l_udt_name := 'GEOMETRY';
ELSIF l_is_pg_geography = TRUE THEN
l_udt_name := 'GEOGRAPHY';
END IF;
ELSE
-- Not registered with geodatabase
-- Check spatial column storage type
SELECT upper(UDT_NAME), UPPER(DOMAIN_NAME) INTO l_udt_name, l_domain_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_schema = l_schema
AND table_name = l_table
AND column_name = l_column
AND DATA_TYPE = 'USER-DEFINED'
AND (UDT_NAME = 'st_geometry' OR
UDT_NAME = 'geometry' OR
UDT_NAME = 'geography');
IF NOT FOUND THEN
RETURN NULL::TEXT;
END IF;
IF l_udt_name IS NULL THEN
RETURN NULL::TEXT;
END IF;
-- Query column metadata
IF l_udt_name = 'ST_GEOMETRY' THEN
-- Check if column is registered in sde.sde_geometry_columns
SELECT INTO l_entity, l_has_3d, l_has_measure, l_coordDim
(CASE WHEN b.geometry_type = SE_OGC_GEOMETRY THEN 'GEOMETRY'
WHEN b.geometry_type = SE_OGC_COLLECTION THEN 'COLLECTION'
WHEN b.geometry_type = SE_OGC_POINT THEN 'POINT'
WHEN b.geometry_type = SE_OGC_LINESTRING THEN 'LINESTRING'
WHEN b.geometry_type = SE_OGC_POLYGON THEN 'POLYGON'
WHEN b.geometry_type = SE_OGC_MULTIPOINT THEN 'MULTIPOINT'
WHEN b.geometry_type = SE_OGC_MULTILINESTRING THEN 'MULTILINESTRING'
WHEN b.geometry_type = SE_OGC_MULTIPOLYGON THEN 'MULTIPOLYGON'
ELSE NULL
END)::varchar AS geom_type,
(CASE WHEN b.coord_dimension = 4 THEN TRUE
WHEN b.coord_dimension = 3 THEN NULL -- ambiguous Z|M
ELSE FALSE
END)::bool AS is_3d,
(CASE WHEN b.coord_dimension = 4 THEN TRUE
WHEN b.coord_dimension = 3 THEN NULL -- ambiguous Z|M
ELSE FALSE
END)::bool AS is_measure,
coord_dimension
FROM sde.sde_geometry_columns AS b
WHERE f_table_schema = l_schema AND
f_table_name = l_table AND
f_geometry_column = l_column;
IF NOT FOUND THEN
-- Table not registered in sde.sde_geometry_columns - use UDT column metadata
-- Unregistered & unpopulated st_geometry - allow ZM
l_coordDim := 4;
l_has_measure := TRUE;
l_has_3d := TRUE;
SELECT INTO l_entity (CASE WHEN l_domain_name IS NULL OR
l_domain_name = 'ST_GEOMETRY' THEN 'GEOMETRY'
WHEN l_domain_name = 'ST_GEOMCOLLECTION' THEN 'COLLECTION'
WHEN l_domain_name = 'ST_POINT' THEN 'POINT'
WHEN l_domain_name = 'ST_LINESTRING' THEN 'LINESTRING'
WHEN l_domain_name = 'ST_POLYGON' THEN 'POLYGON'
WHEN l_domain_name = 'ST_MULTIPOINT' THEN 'MULTIPOINT'
WHEN l_domain_name = 'ST_MULTILINESTRING' THEN 'MULTILINESTRING'
WHEN l_domain_name = 'ST_MULTIPOLYGON' THEN 'MULTIPOLYGON'
ELSE NULL
END);
END IF;
ELSIF l_udt_name = 'GEOMETRY' THEN
SELECT INTO l_entity, l_has_3d, l_has_measure, l_coordDim
(CASE WHEN b.type = 'GEOMETRY' THEN 'GEOMETRY'
WHEN b.type = 'GEOMETRYCOLLECTION' THEN 'COLLECTION'
WHEN b.type = 'POINT' THEN 'POINT'
WHEN b.type = 'LINESTRING' THEN 'LINESTRING'
WHEN b.type = 'POLYGON' THEN 'POLYGON'
WHEN b.type = 'MULTIPOINT' THEN 'MULTIPOINT'
WHEN b.type = 'MULTILINESTRING' THEN 'MULTILINESTRING'
WHEN b.type = 'MULTIPOLYGON' THEN 'MULTIPOLYGON'
WHEN b.type = 'GEOMETRYCOLLECTIONM' THEN 'COLLECTION'
WHEN b.type = 'POINTM' THEN 'POINT'
WHEN b.type = 'LINESTRINGM' THEN 'LINESTRING'
WHEN b.type = 'POLYGONM' THEN 'POLYGON'
WHEN b.type = 'MULTIPOINTM' THEN 'MULTIPOINT'
WHEN b.type = 'MULTILINESTRINGM' THEN 'MULTILINESTRING'
WHEN b.type = 'MULTIPOLYGONM' THEN 'MULTIPOLYGON'
ELSE NULL
END)::varchar AS geom_type,
(CASE WHEN b.coord_dimension = 4 THEN TRUE
WHEN b.coord_dimension = 3 AND (
b.type = 'GEOMETRY' OR
b.type = 'GEOMETRYCOLLECTION' OR
b.type = 'POINT' OR
b.type = 'LINESTRING' OR
b.type = 'POLYGON' OR
b.type = 'MULTIPOINT' OR
b.type = 'MULTILINESTRING' OR
b.type = 'MULTIPOLYGON'
) THEN TRUE
ELSE FALSE
END)::bool AS is_3d,
(CASE WHEN b.coord_dimension = 4 THEN TRUE
WHEN b.type = 'GEOMETRYCOLLECTIONM' THEN TRUE
WHEN b.type = 'POINTM' THEN TRUE
WHEN b.type = 'LINESTRINGM' THEN TRUE
WHEN b.type = 'POLYGONM' THEN TRUE
WHEN b.type = 'MULTIPOINTM' THEN TRUE
WHEN b.type = 'MULTILINESTRINGM' THEN TRUE
WHEN b.type = 'MULTIPOLYGONM' THEN TRUE
ELSE FALSE
END)::bool AS is_measure,
coord_dimension
FROM geometry_columns AS b
WHERE f_table_schema = l_schema AND
f_table_name = l_table AND
f_geometry_column = l_column;
IF NOT FOUND THEN
-- Table not registered in GEOMETRY_COLUMNS - incomplete type setup
RETURN NULL::TEXT;
END IF;
ELSIF l_udt_name = 'GEOGRAPHY' THEN
SELECT INTO l_entity, l_has_3d, l_has_measure, l_coordDim
(CASE WHEN b.type = 'GEOMETRY' THEN 'GEOMETRY'
WHEN b.type = 'GEOMETRYCOLLECTION' THEN 'COLLECTION'
WHEN b.type = 'POINT' THEN 'POINT'
WHEN b.type = 'LINESTRING' THEN 'LINESTRING'
WHEN b.type = 'POLYGON' THEN 'POLYGON'
WHEN b.type = 'MULTIPOINT' THEN 'MULTIPOINT'
WHEN b.type = 'MULTILINESTRING' THEN 'MULTILINESTRING'
WHEN b.type = 'MULTIPOLYGON' THEN 'MULTIPOLYGON'
WHEN b.type = 'GEOMETRYM' THEN 'GEOMETRY'
WHEN b.type = 'GEOMETRYCOLLECTIONM' THEN 'COLLECTION'
WHEN b.type = 'POINTM' THEN 'POINT'
WHEN b.type = 'LINESTRINGM' THEN 'LINESTRING'
WHEN b.type = 'POLYGONM' THEN 'POLYGON'
WHEN b.type = 'MULTIPOINTM' THEN 'MULTIPOINT'
WHEN b.type = 'MULTILINESTRINGM' THEN 'MULTILINESTRING'
WHEN b.type = 'MULTIPOLYGONM' THEN 'MULTIPOLYGON'
WHEN b.type = 'POINTZ' THEN 'POINT'
WHEN b.type = 'GEOMETRYZ' THEN 'GEOMETRY'
WHEN b.type = 'GEOMETRYCOLLECTIONZ' THEN 'COLLECTION'
WHEN b.type = 'LINESTRINGZ' THEN 'LINESTRING'
WHEN b.type = 'POLYGONZ' THEN 'POLYGON'
WHEN b.type = 'MULTIPOINTZ' THEN 'MULTIPOINT'
WHEN b.type = 'MULTILINESTRINGZ' THEN 'MULTILINESTRING'
WHEN b.type = 'MULTIPOLYGONZ' THEN 'MULTIPOLYGON'
WHEN b.type = 'GEOMETRYZM' THEN 'GEOMETRY'
WHEN b.type = 'GEOMETRYCOLLECTIONZM' THEN 'COLLECTION'
WHEN b.type = 'POINTZM' THEN 'POINT'
WHEN b.type = 'LINESTRINGZM' THEN 'LINESTRING'
WHEN b.type = 'POLYGONZM' THEN 'POLYGON'
WHEN b.type = 'MULTIPOINTZM' THEN 'MULTIPOINT'
WHEN b.type = 'MULTILINESTRINGZM' THEN 'MULTILINESTRING'
WHEN b.type = 'MULTIPOLYGONZM' THEN 'MULTIPOLYGON'
ELSE NULL
END)::varchar AS geom_type,
(CASE WHEN b.coord_dimension = 4 THEN TRUE
WHEN b.coord_dimension = 3 AND (
b.type = 'GEOMETRY' OR
b.type = 'GEOMETRYCOLLECTION' OR
b.type = 'POINT' OR
b.type = 'LINESTRING' OR
b.type = 'POLYGON' OR
b.type = 'MULTIPOINT' OR
b.type = 'MULTILINESTRING' OR
b.type = 'MULTIPOLYGON' OR
b.type = 'GEOMETRYZ' OR
b.type = 'GEOMETRYCOLLECTIONZ' OR
b.type = 'POINTZ' OR
b.type = 'LINESTRINGZ' OR
b.type = 'POLYGONZ' OR
b.type = 'MULTIPOINTZ' OR
b.type = 'MULTILINESTRINGZ' OR
b.type = 'MULTIPOLYGONZ'
) THEN TRUE
ELSE FALSE
END)::bool AS is_3d,
(CASE WHEN b.coord_dimension = 4 THEN TRUE
WHEN b.type = 'GEOMETRYM' THEN TRUE
WHEN b.type = 'GEOMETRYCOLLECTIONM' THEN TRUE
WHEN b.type = 'POINTM' THEN TRUE
WHEN b.type = 'LINESTRINGM' THEN TRUE
WHEN b.type = 'POLYGONM' THEN TRUE
WHEN b.type = 'MULTIPOINTM' THEN TRUE
WHEN b.type = 'MULTILINESTRINGM' THEN TRUE
WHEN b.type = 'MULTIPOLYGONM' THEN TRUE
ELSE FALSE
END)::bool AS is_measure,
b.coord_dimension
FROM ( SELECT UPPER(type) AS type, coord_dimension
FROM geography_columns
WHERE f_table_schema = l_schema AND
f_table_name = l_table AND
f_geography_column = l_column) AS b;
IF NOT FOUND THEN
-- Geography type column does not exist
RETURN NULL::TEXT;
END IF;
END IF;
END IF;
-- Assemble type string
IF l_entity = 'GEOMETRY' THEN
l_geometry := TRUE;
ELSIF l_entity = 'COLLECTION' THEN
l_collection := TRUE;
ELSIF l_entity = 'POINT' THEN
l_point := TRUE;
ELSIF l_entity = 'LINESTRING' THEN
l_line := TRUE;
ELSIF l_entity = 'POLYGON' THEN
l_poly := TRUE;
ELSIF l_entity = 'MULTIPOINT' THEN
l_multi := TRUE;
l_point := TRUE;
ELSIF l_entity = 'MULTILINESTRING' THEN
l_multi := TRUE;
l_line := TRUE;
ELSIF l_entity = 'MULTIPOLYGON' THEN
l_multi := TRUE;
l_poly := TRUE;
END IF;
IF l_point = FALSE AND l_poly = FALSE AND l_line = FALSE AND l_sline = FALSE THEN
l_is_empty := TRUE;
END IF;
IF l_coordDim = 3 THEN
IF l_has_measure IS NULL AND l_has_3d IS NULL THEN
-- st_geometry 3d coord_dimension without any other context is ambiguous - for now, assume Z
l_zm := ' Z ';
ELSIF l_has_measure THEN
l_zm := ' M ';
ELSIF l_has_3d THEN
l_zm := ' Z ';
END IF;
ELSIF l_coordDim = 4 THEN
l_zm := ' ZM ';
ELSE
l_zm := ' ';
END IF;
IF l_multi = TRUE THEN
l_multi_prefix := 'MULTI';
ELSE
l_multi_prefix := '';
END IF;
IF l_is_empty = TRUE THEN
-- Registered with geodatabase, but no layer type eflags
l_result := 'EMPTY';
ELSIF l_collection = TRUE THEN
l_result := 'COLLECTION' || l_zm;
ELSIF l_geometry = TRUE THEN
-- Return all types
l_result := 'POINT' || l_zm;
l_result := l_result || 'LINESTRING' || l_zm;
l_result := l_result || 'POLYGON' || l_zm;
l_result := l_result || 'MULTIPOINT' || l_zm;
l_result := l_result || 'MULTILINESTRING' || l_zm;
l_result := l_result || 'MULTIPOLYGON' || l_zm;
ELSE
l_result := '';
IF l_point THEN
l_result := l_result || l_multi_prefix || 'POINT' || l_zm;
END IF;
IF l_sline OR l_line THEN
l_result := l_result || l_multi_prefix || 'LINESTRING' || l_zm;
END IF;
IF l_poly THEN
l_result := l_result || l_multi_prefix || 'POLYGON' || l_zm;
END IF;
END IF;
RETURN trim(both l_result);
END;
$function$openGauss会把l_entity ( 当成array类型的var进行处理，然后把后面的所有内容当成array var消耗掉
opengauss的plpgsql的gram.y中的read_into_array_table_scalar_list函数会到else分支，因此报错
read_into_array_table_scalar_list(char *initial_name,
PLpgSQL_datum *initial_datum,
int initial_dno,
int initial_location,
bool bulk_collect)
{
int nfields = 0;
char *fieldnames[1024] = {NULL};
int varnos[1024] = {0};
PLpgSQL_row *row;
int tok;
int toktmp = 0;
int tmpdno = 0;
int type_flag = -1;
bool isarrayelem = false;
char* nextname = NULL;
check_assignable(initial_datum, initial_location);
tmpdno = initial_dno;
tok = yylex();
get_datum_tok_type(initial_datum, &type_flag);
if (type_flag == PLPGSQL_TOK_TABLE_VAR) {
isarrayelem = read_into_using_add_tableelem(fieldnames, varnos, &nfields, tmpdno, &tok);
} else{
isarrayelem = read_into_using_add_arrayelem(fieldnames, varnos, &nfields, tmpdno, &tok);
}
.......
}
需要修改成如下内容
read_into_array_table_scalar_list(char *initial_name,
PLpgSQL_datum *initial_datum,
int initial_dno,
int initial_location,
bool bulk_collect)
{
int nfields = 0;
char *fieldnames[1024] = {NULL};
int varnos[1024] = {0};
PLpgSQL_row *row;
int tok;
int toktmp = 0;
int tmpdno = 0;
int type_flag = -1;
bool isarrayelem = false;
char* nextname = NULL;
check_assignable(initial_datum, initial_location);
tmpdno = initial_dno;
tok = yylex();
get_datum_tok_type(initial_datum, &type_flag);
if (type_flag == PLPGSQL_TOK_TABLE_VAR) {
isarrayelem = read_into_using_add_tableelem(fieldnames, varnos, &nfields, tmpdno, &tok);
} else if(type_flag == PLPGSQL_TOK_VARRAY_VAR) {
isarrayelem = read_into_using_add_arrayelem(fieldnames, varnos, &nfields, tmpdno, &tok);
}
else
{
}
坑3:
存储过程执行报return no rows错误
具体测试过程如下：
CREATE TABLE sde.sde_states (
state_id serial NOT NULL,
owner character varying(128) NOT NULL,
creation_time timestamp without time zone NOT NULL,
closing_time timestamp without time zone,
parent_state_id bigint NOT NULL,
lineage_name bigint NOT NULL
);
CREATE TABLE sde.sde_versions (
name character varying(64) NOT NULL,
owner character varying(128) NOT NULL,
version_id integer NOT NULL,
status integer NOT NULL,
state_id bigint NOT NULL,
description character varying(65),
parent_name character varying(64),
parent_owner character varying(128),
parent_version_id integer,
creation_time timestamp without time zone NOT NULL
);
CREATE TABLE sde.sde_state_lineages (
lineage_name bigint NOT NULL,
lineage_id bigint NOT NULL
);
CREATE OR REPLACE FUNCTION sde.sde_state_def_insert(stateidval bigint, schemaval character varying, pstateidval bigint, plineagenameval bigint, sdeidval integer, openorcloseval integer)
RETURNS bigint
LANGUAGE plpgsql
SECURITY DEFINER
AS $function$
DECLARE
newStateIdVal BIGINT;
selStateIdVal BIGINT;
new_lineage_name BIGINT;
crTimeVal TIMESTAMP;
clTimeVal TIMESTAMP;
BEGIN
new_lineage_name := pLineageNameVal;
Select to_char(CURRENT_TIMESTAMP,'YYYY-MM-DD HH24:MI:SS') INTO crTimeVal;
-- close state
IF OpenOrCloseVal = 2 THEN
clTimeVal := crTimeVal;
ELSE
clTimeVal := NULL;
END IF;
-- Generate next state_id.
SELECT INTO newStateIdVal nextval('sde.sde_states_state_id_seq');
-- Check to see if we need to create a new lineage.
SELECT INTO selStateIdVal state_id FROM sde.sde_states
WHERE parent_state_id = pStateIdVal
AND lineage_name = pLineageNameVal;
IF NOT FOUND THEN
-- Insert new state into existing lineage.
INSERT INTO sde.sde_states (state_id, owner, creation_time,
closing_time, parent_state_id, lineage_name)
VALUES (newStateIdVal, LOWER(schemaVal), crTimeVal,
clTimeVal, pStateIdVal, pLineageNameVal);
ELSE
-- Parent state & lineage exist - split off a new lineage.
new_lineage_name := newStateIdVal;
INSERT INTO sde.sde_states (state_id, owner, creation_time,
closing_time, parent_state_id, lineage_name)
VALUES (newStateIdVal, LOWER(schemaVal), crTimeVal,
clTimeVal, pStateIdVal, new_lineage_name);
END IF;
-- If we created a new lineage, insert it into the STATE_LINEAGE
-- table in normalized form.
IF new_lineage_name != pLineageNameVal THEN
INSERT INTO sde.sde_state_lineages
SELECT new_lineage_name, l.lineage_id FROM sde.sde_state_lineages l
WHERE l.lineage_name = pLineageNameVal
AND l.lineage_id <= pStateIdVal;
END IF;
-- We also insert a row for this state, as if it were in its own
-- state lineage.
INSERT INTO sde.sde_state_lineages (lineage_name, lineage_id)
VALUES (new_lineage_name, newStateIdVal);
-- Place a mark on the new state so that it does not get cleaned up
-- by compress. Do it before the commit so it will never be both
-- visible and unmarked at the same time.
PERFORM sde.SDE_state_lock_def_insert(sdeIdVal, newStateIdVal, 'Y', 'M');
RETURN newStateIdVal;
END;
$function$
Select sde_state_def_insert(0,’sde’,1,1,15,1);
原因：
gram.y
read_into_target(PLpgSQL_variable **target, bool *strict)
{
int tok;
/* Set default results */
*target = NULL;
if (strict)
*strict = false;
tok = yylex();
if (strict && tok == K_STRICT)
{
*strict = true;
tok = yylex();
}
该函数默认将strict设置为true，这点跟PostgreSQL不一样。需要和PostgreSQL一致，修改成false
这么为啥这么设计，那还要select into strict 语法干啥呢。。。。
pl_exec.cpp
make_execsql_stmt
if (stmt->into) {
if (!stmt->mod_stmt & !stmt->bulk_collect) {
stmt->strict = true;
}
if (stmt->bulk_collect) {
tcount = 0;
} else if (stmt->strict || stmt->mod_stmt) {
tcount = 2;
} else {
tcount = 1;
}
} else {
tcount = 0;
}
上述代码默认将select into 设置成strict=true了，也需要修改过来该问题在openGauss5.0通过behavior_compat_options=‘select_into_return_null' 参数进行修复，具体可以查看
坑4：
WHERE CURRENT OF clause not yet supported
"UPDATE sde.i8 SET base_id = base_id + 400, last_id = 1 WHERE CURRENT OF这部分代码在
src/gausskernel/runtime/executor/nodeTidscan.cpp
static TupleTableSlot* TidFetchTuple(TidScanState* node, bool b_backward, Relation heap_relation)
{
int num_tids = node->tss_NumTids;
ItemPointerData* tid_list = node->tss_TidList;
Snapshot scanSnap;
TupleTableSlot* slot = node->ss.ss_ScanTupleSlot;
/*
* Choose user-specified snapshot if TimeCapsule clause exists, otherwise
* estate->es_snapshot instead.
*/
scanSnap = TvChooseScanSnap(heap_relation, (Scan *)node->ss.ps.plan, &node->ss);
InitTidPtr(node, b_backward);
while (node->tss_TidPtr >= 0 && node->tss_TidPtr < num_tids) {
ItemPointerData tid = tid_list[node->tss_TidPtr];
/* WHERE CURRENT OF is disabled at transformCurrentOfExpr, so no need to support it here. */
Assert (node->tss_isCurrentOf == false);
if (node->tss_isCurrentOf) {
ereport(ERROR, (errcode(ERRCODE_STATEMENT_TOO_COMPLEX),
(errmsg("WHERE CURRENT OF clause not yet supported"))));
}
if (tableam_tops_tuple_fetch_row_version(node, heap_relation, &tid, scanSnap, slot)) {
return slot;
}
/* Bad TID or failed snapshot qual; try next */
Move
}
src/common/backend/parser/parse_expr.cpp
static Node* transformCurrentOfExpr(ParseState* pstate, CurrentOfExpr* cexpr)
{
int sublevels_up;
#ifdef PGXC
ereport(ERROR, (errcode(ERRCODE_STATEMENT_TOO_COMPLEX), (errmsg("WHERE CURRENT OF clause not yet supported"))));
#endif
/* CURRENT OF can only appear at top level of UPDATE/DELETE */
AssertEreport(pstate->p_target_rangetblentry != NULL, MOD_OPT, "");
cexpr->cvarno = RTERangeTablePosn(pstate, pstate->p_target_rangetblentry, &sublevels_up);
AssertEreport(sublevels_up == 0, MOD_OPT, "");
。。。。
}坑5：
存储过程中调用过程中找不到public下的sequence
脚本如下:
CREATE TABLE public.sde_spatial_references (
srid integer NOT NULL,
sr_name character varying(128),
description character varying(256),
falsex numeric NOT NULL,
falsey numeric NOT NULL,
xyunits numeric NOT NULL,
falsez numeric NOT NULL,
zunits numeric NOT NULL,
falsem numeric NOT NULL,
munits numeric NOT NULL,
xycluster_tol numeric,
zcluster_tol numeric,
mcluster_tol numeric,
object_flags integer DEFAULT 1 NOT NULL,
srtext character varying(1024) NOT NULL,
min_x numeric,
min_y numeric,
min_z numeric,
min_m numeric,
max_x numeric,
max_y numeric,
max_z numeric,
max_m numeric,
cs_id integer,
cs_name character varying(128),
cs_type character varying(128),
organization character varying(128),
org_coordsys_id integer,
CONSTRAINT spatial_ref_xyunits CHECK ((xyunits >= (1)::numeric)),
CONSTRAINT spatial_ref_zunits CHECK ((zunits >= (1)::numeric))
);
ALTER TABLE public.sde_spatial_references OWNER TO sde;
CREATE SEQUENCE public.sde_spatial_references_srid_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE public.sde_spatial_references_srid_seq OWNER TO sde;
CREATE OR REPLACE FUNCTION sde.sde_sref_def_insert(i_srid integer, i_falsex numeric, i_falsey numeric, i_xyunits numeric, i_falsez numeric, i_zunits numeric, i_falsem numeric, i_munits numeric, i_srtext character varying, i_description character varying, i_organization character varying, i_cs_id integer, i_object_flags integer, i_xycluster_tol numeric, i_zcluster_tol numeric, i_mcluster_tol numeric)
RETURNS integer
LANGUAGE plpgsql
SECURITY DEFINER
AS $function$
DECLARE
sql_code INTEGER;
srid_val INTEGER;
BEGIN
sql_code := -1;
IF i_srid = 0 THEN
SELECT nextval(‘sde_spatial_references_srid_seq') into srid_val;
ELSE IF i_srid = -1 THEN
srid_val = 0;
ELSE
srid_val = i_srid;
END IF;
END IF;
INSERT INTO public.sde_spatial_references (srid, falsex, falsey, xyunits,
falsez, zunits, falsem, munits,
srtext, description, organization,
cs_id, object_flags,
xycluster_tol, zcluster_tol, mcluster_tol)
VALUES (srid_val, i_falsex, i_falsey, i_xyunits,
i_falsez, i_zunits, i_falsem, i_munits,
i_srtext, i_description, i_organization,
i_cs_id, i_object_flags,
i_xycluster_tol, i_zcluster_tol, i_mcluster_tol);
IF NOT FOUND THEN
RAISE EXCEPTION 'Could not insert Spatial Reference entry.';
END IF;
sql_code := srid_val;
RETURN sql_code;
END;
$function$
SELECT sde.SDE_sref_def_insert ('0', '-400'::numeric, '-400'::numeric, '1111948722.222220000000000000000'::numeric, '0'::numeric, '1'::numeric, '0'::numeric, '1'::numeric, 'GEOGCS[""GCS_WGS_1984"",DATUM[""D_WGS_1984"",SPHEROID[""WGS_1984"",6378137.0,298.257223563]],PRIMEM[""Greenwich"",0.0],UNIT[""Degree"",0.0174532925199433]]', NULL, 'EPSG', '4326', '1', '.00000000898315284119522000000000'::numeric, '.00100000000000000000000000000000'::numeric, '.00100000000000000000000000000000'::numeric);
执行结果如下
解决方法：
通过设置下面的参数解决
behavior_compat_options='bind_procedure_searchpath'
华为开发者空间，是为全球开发者打造的专属开发空间，汇聚了华为优质开发资源及工具，致力于让每一位开发者拥有一台云主机，基于华为根生态开发、创新。
更多推荐
0
0- 0
已为社区贡献5条内容
所有评论(0)