装oracle报temp无权限,华为云rds-PostgreSQL增强版替换Oracle常见问题以及解决方案

华为云rds-PostgreSQL增强版提供兼容oracle功能,目前已经做到兼容oracle 90%的语法,在使用PostgreSQL增强版替换oracle的过程中,可能遇到一些问题,这里总结一下,并给出解决方案。

1.1JDBC驱动

基于oracle数据库的应用本身使用oracle的JDBC驱动,换成postgresql增强版需要将JDBC驱动换成postgresql的驱动

1.2事务处理

Oracle数据库,在同一个事务中的多个语句,如某个语句执行出错,该语句不影响其他语句的执行,如事务提交,则执行成功语句会持久化到DB中。PG数据库,在同一个事务中的多个语句,如某个SQL语句执行出错,则就算在其后执行commit,事务也会回滚。如在该出错语句之后执行其他DML语句,则会报错。因此Oracle捕获异常后是重做异常sql,而PG是先回滚事务,然后重做整个事务。

1.3查询结果预期有序一定要加order by

oracle数据库在数据量少,查询结果集小,并且数据没有更新时,结果集顺序相对稳定,但是不能绝对保证顺序,当结果集、数据量增大、数据频繁更新时,结果集一定会紊乱,PostgreSQL同理,在一定情况下PG这个情况更容易出现,因此查询结果有顺序的预期一定要加order by

1.4数据库对象大小写

oracle数据字典默认大写,PG数据字典默认小写,因此创建和引用对象时不加引号,如果涉及特殊字符,PG创建数据库对象时要小写,这样才不区分SQL的大小写

例如:oracle

SQL>   drop table test;

create   table "TEST"(ID varchar(20));

insert   into test(id) values('a');

insert   into "TEST"(id) values('b');

Table   dropped.

例如:postgresqltest=>   drop table test;

DROP TABLE

test=> create   table "TEST"(ID varchar(20));

CREATE   TABLE

test=>   insert into test(id) values('a');

ERROR:  relation "test" does not exist

LINE 1:   insert into test(id) values('a');

^

test=>   insert into "TEST"(id) values('b');

INSERT 0 1

1.5列(别)名为关键字

Oracle和PG的数据字典关键字集合是有差异、有交集的。Oracle中比如text这样的关键字可以直接作为列的别名,比如:select xx text from t,目前我们已经做了很多关键字直接作为别名的兼容,但是目前还是没有完全支持,因此建议写sql或者改问题过程中逐步加as,比如select xx as text from t

1.6sequence范围不一致

修改sequence maxvalue的值

PG范围为:1到9223372036854775807

oracle范围为:1到9.99...9 x 10125

1.7同义词

PG中没有同义词,自动创建为视图,转换过来的视图名称与存在的表名相同,需要修改视图名称。

1.8float类型精度

oracle支持float(126),PG最大支持到float(53),单在一般情况下PG的精度已经够用了,开发中遇到此类问题可以直接修改。

1.9primary等关键字不能用做列名

oracle支持primary作为列名,PG中要用primary作为列名的话需要加上双引号,例如:create   table t1(“primary” int);

建议最好不用用primary等关键字做为列名,可以修改为primary_key就没有问题了。

1.10不能modify带有view的列

Oracle可以直接modify带有view的列,PG中必须删除view才可以modify表,建议用户先删除view再modify表,最后重建view

1.11在view上创建primary key

Oracle可以在view上直接创建primary key等约束,PG中不能在view上创建约束,建议用户直接在表上创建约束。

1.12Data类型差值不一致问题

Oracle执行以下语句返回结果SQL>   select to_date('2019-06-06 11:41:07','yyyy-MM-dd HH24:mi:ss')-to_date('   2018-06-06 11:06:08','yyyy-MM-dd HH24:mi:ss') from dual;

TO_DATE('2019-06-0611:41:07','YYYY-MM-DDHH24:MI:SS')-TO_DATE('2018-06-0611:06:08

--------------------------------------------------------------------------------

365.024294

PG执行以下语句返回结果:postgres=#   select to_date('2019-06-06 11:41:07','yyyy-MM-dd HH24:mi:ss')-to_date('   2018-06-06 11:06:08','yyyy-MM-dd HH24:mi:ss') from dual;

?column?

-------------------

365 days 00:34:59

(1 row)

PG解决方案,在结果上加上一个0,让数据不做转换postgres=#   select to_date('2019-06-06 11:41:07','yyyy-MM-dd HH24:mi:ss')-to_date('   2018-06-06 11:06:08','yyyy-MM-dd HH24:mi:ss') + 0from   dual;

?column?

-------------------

365.024293981481

(1 row)

1.13使用pg_dump导出的线下库,在导入到PG线上库时报错“must member of role eas850pgnew”

原因分析:用户线下导出库是用用户eas850pgnew,查看了导出dump出的脚本,每个对象在创建完后都会有alter table xxx owner to eas850pgnew。正是这句报的错,因为用户使用了root账户创建了database,并导入到owner为root的database中,所有表默认owner都是root,而root不是超级权限,在执行alter table xxx owner to eas850pgnew时报错。

解决方法:

a.使用root创建数据库test

create database test;

b.连接上test库创建新用户eas850pgnew

create role eas850pgnew createdb createrole login password 'Perftest1!'

c.使用新用户eas850pgnew创建目标数据库eas850pgnew_ora

crete database eas850pgnew_ora;

d.使用pg_admin通过用户eas850pgnew导入库到目标库eas850pgnew_ora中

1.14时间格式不一致问题

可以按照用户使用的时间格式来设置nls_timestamp_format或则nls_date_format参数

1.15PG增强版不能在postgres库中创建role问题

a.连接数据库postgres,创建一个test库

b.连接test库,在test库中执行创建用户操作

1.16更改数据库中所有对象的owner

从oracle中迁移过来的数据库默认owner是root,需要将owner修改为用户自己新建的用户,修改方法如下:假设要将root下的test库中所有对象owner从root改为用户post

c.用post账户登录test库执行:grant post to root;

d.用root账户登录test库执行:reassign owned by root to post;

1.17PG增强版中定长字符串长度不够会补充空格

例如:char(10)类型的列,在插入数据后,长度不够会补充空格,再如,以下存储过程中用到了定长字符串,在增强版上不能返回正确的值

CREATE OR   REPLACE FUNCTION dateadd(difftype character varying, incrementvalue numeric,   inputdate timestamp with time zone)

RETURNS timestamp without time zone AS

$BODY$

DECLARE

YEAR_CONST Char(15) := 'year';

MONTH_CONST Char(15) := 'month';

WEEK_CONST Char(15) := 'week';

DAY_CONST Char(15) := 'day';

HOUR_CONST Char(15) := 'hour';

MIN_CONST Char(15) :='minute';

SECOND_CONST Char(15) :='second';

dateTemp timestamp;

intervals interval;

BEGIN

if inputdate is null then return null ;   end if;

IF lower($1) = lower(YEAR_CONST) THEN

select cast(cast(incrementvalue as   character varying) || ' year' as interval) into intervals;

ELSEIF lower($1) = lower(MONTH_CONST)   THEN

select cast(cast(incrementvalue as   character varying) || ' month' as interval) into intervals;

ELSEIF lower($1) = lower(WEEK_CONST)   THEN

select cast(cast(incrementvalue as   character varying) || ' week' as interval) into intervals;

ELSEIF lower($1) = lower(DAY_CONST)   THEN

select cast(cast(incrementvalue as   character varying) || ' day' as interval) into intervals;

ELSEIF lower($1) = lower(HOUR_CONST)   THEN

select cast(cast(incrementvalue as   character varying) || ' hour' as interval) into intervals;

ELSEIF lower($1) = lower(MIN_CONST)   THEN

select cast(cast(incrementvalue as   character varying) || ' minute' as interval) into intervals;

ELSEIF lower($1) = lower(SECOND_CONST)   THEN

select cast(cast(incrementvalue as   character varying) || ' second' as interval) into intervals;

END IF;

dateTemp:= inputdate + intervals;

RETURN dateTemp;

END;

$BODY$

LANGUAGE   PLPGSQL;

在PG增强版上执行会返回NULL,原因是char(10)=’year’其实会在year后面补充6个空格,直到长度为10,所以在PG增强版上可以使用变长字符串来解决这个问题,如下:CREATE OR   REPLACE FUNCTION dateadd(difftype character varying, incrementvalue numeric,   inputdate timestamp with time zone)

RETURNS timestamp without time zone AS

$BODY$

DECLARE

YEAR_CONST varchar(15) := 'year';

MONTH_CONST varchar(15) := 'month';

WEEK_CONST varchar(15) := 'week';

DAY_CONST varchar(15) := 'day';

HOUR_CONST varchar(15) := 'hour';

MIN_CONST varchar(15) :='minute';

SECOND_CONST varchar(15) :='second';

dateTemp timestamp;

intervals interval;

BEGIN

if inputdate is null then return null ;   end if;

IF lower($1) = lower(YEAR_CONST) THEN

select cast(cast(incrementvalue as   character varying) || ' year' as interval) into intervals;

ELSEIF lower($1) = lower(MONTH_CONST)   THEN

select cast(cast(incrementvalue as   character varying) || ' month' as interval) into intervals;

ELSEIF lower($1) = lower(WEEK_CONST)   THEN

select cast(cast(incrementvalue as   character varying) || ' week' as interval) into intervals;

ELSEIF lower($1) = lower(DAY_CONST)   THEN

select cast(cast(incrementvalue as   character varying) || ' day' as interval) into intervals;

ELSEIF lower($1) = lower(HOUR_CONST)   THEN

select cast(cast(incrementvalue as   character varying) || ' hour' as interval) into intervals;

ELSEIF lower($1) = lower(MIN_CONST)   THEN

select cast(cast(incrementvalue as   character varying) || ' minute' as interval) into intervals;

ELSEIF lower($1) = lower(SECOND_CONST)   THEN

select cast(cast(incrementvalue as   character varying) || ' second' as interval) into intervals;

END IF;

dateTemp:= inputdate + intervals;

RETURN dateTemp;

END;

$BODY$

LANGUAGE   PLPGSQL;

1.18Date类型相减返回值为interval类型的问题

例如:PG增强版下执行以下语句:glow=>   SELECT CAST('2019-06-02' AS DATE) - CAST('2019-06-01' AS DATE);

?column?

----------

1 day

(1 row)

可以看到,执行结果是一个interval类型,而在PG社区版上执行结果是一个数字glow=>   SELECT CAST('2019-06-02' AS DATE) - CAST('2019-06-01' AS DATE);

?column?

----------

1

(1 row)

如果想要在PG增强版上得到和PG社区版一样的结果,可以做如下修改:

方案1:+ 0glow=>   select cast('2019-08-08' as date) - cast('2019-08-01' as date) +0;

?column?

----------

7

(1 row)

方案2:date加上双引号glow=>   select cast('2019-08-08' as "date") - cast('2019-08-01' as   "date");

?column?

----------

7

(1 row)

1.19加密方式password_encryption=scram-sha-256导致用户无法连接

目前默认的加密方式为scram-sha-256,这种加密方式会导致部分用户客户端无法连接到服务器的问题(客户端不支持这种加密方式),一般会报如下错误:An error   occurred while establishing the connection:

Long Message:

不支援10验证类型。请核对您已经组态pg_hba.conf文件包含客户端的IP位址或网路区段,以及驱动程序所支援的验证架构模式已被支援。

Details:

Type: org.postgresql.util.PSQLException

解决方法:后台修改password_encryption=md5,然后在控制台上修改rds的root密码,密码设置可以和原来一样。

为什么要修改密码呢?

答:因为加密方式从scram-sha-256变为md5后,原来的密码用md5方式无法解开,所以需要修改密码,修改密码的过程即重新对密码进行md5加密过程。

1.20PG增强版安装插件的方式

为了安全考虑,目前不允许roor以外的用户安装插件,并且在安装插件的时候不能使用社区版的create extension ‘插件名字’的方式,我们提供了插件安装接口

Root无权限直接用create extension安装插件glow=> create   extension postgis;

ERROR:  permission denied to create extension   "postgis"

HINT:  Must be superuser to create this extension.

使用control_extension()来安装卸载插件glow=> select   control_extension('create','postgis');

control_extension

------------------------------

create postgis successfully.

(1 row)

glow=> select control_extension('drop','postgis');

control_extension

----------------------------

drop postgis successfully.

(1 row)

表情包
插入表情
评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符
相关推荐
©️2020 CSDN 皮肤主题: 深蓝海洋 设计师:CSDN官方博客 返回首页