Oracle ROWID 機能を AWS の PostgreSQL に移行

Amazon Web Services、Rakesh Raghav、Ramesh Pathuri

概要

このパターンでは、Oracle データベースの ROWID 疑似列機能を、Amazon Aurora PostgreSQL のAmazon Relational Database Service (Amazon RDS)、Amazon Aurora PostgreSQL　互換バージョン、またはAmazon Elastic Compute Cloud (Amazon EC2)に移行するオプションについて説明しています。

Oracle データベースでは、ROWID 疑似列がテーブル内の行の物理アドレスです。この疑似列は、プライマリキーがテーブルに存在しない場合でも行を一意に識別するために使用されます。PostgreSQL にも ctid と呼ばれる同様の疑似列がありますが、ROWID としては使用できません。「PostgreSQLのドキュメント」 で説明されているように、更新される場合、またはctid がすべての VACUUM のプロセスの後に変更される可能性があります。

PostgreSQLで ROWID 疑似列機能を作成する方法が3つあります：

このパターンでは、3 つの実装について順を追って説明し、各オプションの利点と欠点を説明します。

前提条件と制限

前提条件

制限事項

製品バージョン

アーキテクチャ

ソーステクノロジースタック

ターゲットテクノロジースタック

実装オプション

PostgreSQLで ROWID のサポート不足を回避するには、テーブルにプライマリキーまたはユニークインデックス、論理的プライマ的キー、またはID属性があるかどうかに応じて、3つの選択肢があります。どれを選択するかは、プロジェクトのスケジュール、現在の移行フェーズ、アプリケーションとデータベースコードへの依存関係によって異なります。

ツール

エピック

select dt.*
from dba_tables dt
where not exists (select 1
                  from all_constraints ct
                  where ct.owner = Dt.owner
                    and ct.table_name = Dt.table_name
                    and ct.constraint_type = 'P'
                  )
and dt.owner = '{schema}'

関連リソース

追加情報

以下のセクションでは、Oracle と PostgreSQL のコード例を示して、これらの 3 つのアプローチを説明します。

「シナリオ 1: プライマリユニークキーを使用」

以下の例では、testrowid_s1 テーブルを、emp_id をプライマリキーとして作成します。

Orackel コード:

create table testrowid_s1 (emp_id integer, name varchar2(10), CONSTRAINT testrowid_pk PRIMARY KEY (emp_id));
INSERT INTO testrowid_s1(emp_id,name) values (1,'empname1');
INSERT INTO testrowid_s1(emp_id,name) values (2,'empname2');
INSERT INTO testrowid_s1(emp_id,name) values (3,'empname3');
INSERT INTO testrowid_s1(emp_id,name) values (4,'empname4');
commit;

SELECT rowid,emp_id,name FROM testrowid_s1;
ROWID                  EMP_ID NAME
------------------ ---------- ----------
AAAF3pAAAAAAAMOAAA          1 empname1
AAAF3pAAAAAAAMOAAB          2 empname2
AAAF3pAAAAAAAMOAAC          3 empname3
AAAF3pAAAAAAAMOAAD          4 empname4

UPDATE testrowid_s1 SET name = 'Ramesh' WHERE rowid = 'AAAF3pAAAAAAAMOAAB' ;
commit;

SELECT rowid,emp_id,name FROM testrowid_s1;
ROWID                  EMP_ID NAME
------------------ ---------- ----------
AAAF3pAAAAAAAMOAAA          1 empname1
AAAF3pAAAAAAAMOAAB          2 Ramesh
AAAF3pAAAAAAAMOAAC          3 empname3
AAAF3pAAAAAAAMOAAD          4 empname4

PostgreSQL コード:

CREATE TABLE public.testrowid_s1
(
    emp_id integer,
    name character varying,
    primary key (emp_id)
);

insert into public.testrowid_s1 (emp_id,name) values 
(1,'empname1'),(2,'empname2'),(3,'empname3'),(4,'empname4');

select emp_id,name from testrowid_s1;
 emp_id |   name   
--------+----------
      1 | empname1
      2 | empname2
      3 | empname3
      4 | empname4

update testrowid_s1 set name = 'Ramesh' where emp_id = 2 ;

select emp_id,name from testrowid_s1;
 emp_id |   name   
--------+----------
      1 | empname1
      3 | empname3
      4 | empname4
      2 | Ramesh

シナリオ 2: 論理プライマリキーを使用

以下の例では、 testrowid_s2 テーブルを emp_id を配して、論理プライマリキーとして作成します。

Orackel コード:

create table testrowid_s2 (emp_id integer, name varchar2(10) );
INSERT INTO testrowid_s2(emp_id,name) values (1,'empname1');
INSERT INTO testrowid_s2(emp_id,name) values (2,'empname2');
INSERT INTO testrowid_s2(emp_id,name) values (3,'empname3');
INSERT INTO testrowid_s2(emp_id,name) values (4,'empname4');
commit;

SELECT rowid,emp_id,name FROM testrowid_s2;
ROWID                  EMP_ID NAME
------------------ ---------- ----------
AAAF3rAAAAAAAMeAAA          1 empname1
AAAF3rAAAAAAAMeAAB          2 empname2
AAAF3rAAAAAAAMeAAC          3 empname3
AAAF3rAAAAAAAMeAAD          4 empname4

UPDATE testrowid_s2 SET name = 'Ramesh' WHERE rowid = 'AAAF3rAAAAAAAMeAAB' ;
commit;

SELECT rowid,emp_id,name FROM testrowid_s2;
ROWID                  EMP_ID NAME
------------------ ---------- ----------
AAAF3rAAAAAAAMeAAA          1 empname1
AAAF3rAAAAAAAMeAAB          2 Ramesh
AAAF3rAAAAAAAMeAAC          3 empname3
AAAF3rAAAAAAAMeAAD          4 empname4

PostgreSQL コード:

CREATE TABLE public.testrowid_s2
(
    emp_id integer,
    name character varying
);

insert into public.testrowid_s2 (emp_id,name) values 
(1,'empname1'),(2,'empname2'),(3,'empname3'),(4,'empname4');

select emp_id,name from testrowid_s2;
 emp_id |   name   
--------+----------
      1 | empname1
      2 | empname2
      3 | empname3
      4 | empname4

update testrowid_s2 set name = 'Ramesh' where emp_id = 2 ;

select emp_id,name from testrowid_s2;
 emp_id |   name   
--------+----------
      1 | empname1
      3 | empname3
      4 | empname4
      2 | Ramesh

シナリオ 3: アイデンティティ属性を使用

以下の例では、プライマリキーなしで、またアイデンティティ属性を使用して、テーブル testrowid_s3 を作成します。

Oracle コード:

create table testrowid_s3 (name varchar2(10));
INSERT INTO testrowid_s3(name) values ('empname1');
INSERT INTO testrowid_s3(name) values ('empname2');
INSERT INTO testrowid_s3(name) values ('empname3');
INSERT INTO testrowid_s3(name) values ('empname4');
commit;

SELECT rowid,name FROM testrowid_s3;
ROWID              NAME
------------------ ----------
AAAF3sAAAAAAAMmAAA empname1
AAAF3sAAAAAAAMmAAB empname2
AAAF3sAAAAAAAMmAAC empname3
AAAF3sAAAAAAAMmAAD empname4

UPDATE testrowid_s3 SET name = 'Ramesh' WHERE rowid = 'AAAF3sAAAAAAAMmAAB' ;
commit;

SELECT rowid,name FROM testrowid_s3;
ROWID              NAME
------------------ ----------
AAAF3sAAAAAAAMmAAA empname1
AAAF3sAAAAAAAMmAAB Ramesh
AAAF3sAAAAAAAMmAAC empname3
AAAF3sAAAAAAAMmAAD empname4

PostgreSQL コード:

CREATE TABLE public.testrowid_s3
(
    rowid_seq bigint generated always as identity,
    name character varying
);

insert into public.testrowid_s3 (name) values 
('empname1'),('empname2'),('empname3'),('empname4');

select rowid_seq,name from testrowid_s3;
 rowid_seq |   name   
-----------+----------
         1 | empname1
         2 | empname2
         3 | empname3
         4 | empname4

update testrowid_s3 set name = 'Ramesh' where rowid_seq = 2 ;

select rowid_seq,name from testrowid_s3;
 rowid_seq |   name   
-----------+----------
         1 | empname1
         3 | empname3
         4 | empname4
         2 | Ramesh