Migrieren Sie die Oracle ROWID-Funktionalität zu PostgreSQL auf AWS

Erstellt von Rakesh Raghav (AWS) und Ramesh Pathuri (AWS)

Übersicht

Dieses Muster beschreibt Optionen für die Migration der ROWID Pseudospaltenfunktionalität in Oracle Database zu einer PostgreSQL-Datenbank in Amazon Relational Database Service (Amazon RDS) for PostgreSQL, Amazon Aurora PostgreSQL-Compatible Edition oder Amazon Elastic Compute Cloud (Amazon). EC2

In einer Oracle-Datenbank ist die Pseudospalte eine physische Adresse einer Zeile in einer TabelleROWID. Diese Pseudospalte wird verwendet, um eine Zeile eindeutig zu identifizieren, auch wenn der Primärschlüssel in einer Tabelle nicht vorhanden ist. PostgreSQL hat eine ähnliche Pseudospalte namensctid, aber sie kann nicht als verwendet werden. ROWID Wie in der PostgreSQL-Dokumentation erklärt, ctid kann es sich ändern, wenn es aktualisiert wird oder nach jedem VACUUM Prozess.

Es gibt drei Möglichkeiten, die ROWID Pseudospaltenfunktionalität in PostgreSQL zu erstellen:

• Verwenden Sie eine Primärschlüsselspalte, anstatt eine Zeile in einer Tabelle ROWID zu identifizieren.

• Verwenden Sie einen logischen Primär-/Einzelschlüssel (der ein zusammengesetzter Schlüssel sein kann) in der Tabelle. 

• Fügen Sie eine Spalte mit automatisch generierten Werten hinzu und machen Sie sie zu einem primären/eindeutigen Schlüssel, der nachgeahmt werden soll. ROWID

Dieses Muster führt Sie durch alle drei Implementierungen und beschreibt die Vor- und Nachteile der einzelnen Optionen.

Voraussetzungen und Einschränkungen

Voraussetzungen

• Ein aktives AWS-Konto

• Fachwissen in der prozeduralen Language/PostgreSQL (PL/pgSQL () Programmierung

• Quelle: Oracle-Datenbank

• Ein Amazon RDS for PostgreSQL- oder Aurora PostgreSQL-kompatibler Cluster oder eine EC2 Instance zum Hosten der PostgreSQL-Datenbank

Einschränkungen

• Dieses Muster bietet Behelfslösungen für die Funktionalität. ROWID PostgreSQL bietet kein Äquivalent zu ROWID in Oracle Database.

Produktversionen

• PostgreSQL 11.9 oder höher

Architektur

Quelltechnologie-Stack

• Oracle Database

Zieltechnologie-Stack

• Aurora PostgreSQL-kompatibel, Amazon RDS für PostgreSQL oder eine Instance mit einer PostgreSQL-Datenbank EC2

Implementierungsoptionen

Es gibt drei Optionen, um die mangelnde ROWID Unterstützung in PostgreSQL zu umgehen, je nachdem, ob Ihre Tabelle einen Primärschlüssel oder einen eindeutigen Index, einen logischen Primärschlüssel oder ein Identitätsattribut hat. Ihre Wahl hängt von Ihren Projektzeitplänen, Ihrer aktuellen Migrationsphase und den Abhängigkeiten von Anwendungs- und Datenbankcode ab.

Option	Beschreibung	Vorteile	Nachteile
Primärschlüssel oder eindeutiger Index	Wenn Ihre Oracle-Tabelle über einen Primärschlüssel verfügt, können Sie die Attribute dieses Schlüssels verwenden, um eine Zeile eindeutig zu identifizieren.	Keine Abhängigkeit von proprietären Datenbankfunktionen. Minimale Auswirkungen auf die Leistung, da Primärschlüsselfelder indexiert werden.	Erfordert Änderungen am Anwendungs- und Datenbankcode, der ROWID für den Wechsel zu Primärschlüsselfeldern erforderlich ist.
Logischer Primär-/Einzelschlüssel	Wenn Ihre Oracle-Tabelle über einen logischen Primärschlüssel verfügt, können Sie die Attribute dieses Schlüssels verwenden, um eine Zeile eindeutig zu identifizieren. Ein logischer Primärschlüssel besteht aus einem Attribut oder einer Gruppe von Attributen, die eine Zeile eindeutig identifizieren können, aber dieser Wert wird in der Datenbank nicht durch eine Einschränkung erzwungen.	Keine Abhängigkeit von proprietären Datenbankfunktionen.	Erfordert Änderungen am Anwendungs- und Datenbankcode, der ROWID für den Wechsel zu Primärschlüsselfeldern erforderlich ist. Erhebliche Auswirkungen auf die Leistung, wenn die Attribute des logischen Primärschlüssels nicht indexiert werden. Sie können jedoch einen eindeutigen Index hinzufügen, um Leistungsprobleme zu vermeiden.
Identitätsattribut	Wenn Ihre Oracle-Tabelle keinen Primärschlüssel hat, können Sie ein zusätzliches Feld als erstellenGENERATED ALWAYS AS IDENTITY. Dieses Attribut generiert immer dann einen eindeutigen Wert, wenn Daten in die Tabelle eingefügt werden, sodass es verwendet werden kann, um eine Zeile für DML-Operationen (Data Manipulation Language) eindeutig zu identifizieren.	Keine Abhängigkeit von proprietären Datenbankfunktionen. Die PostgreSQL-Datenbank füllt das Attribut auf und behält seine Einzigartigkeit bei.	Erfordert Änderungen am Anwendungs- und Datenbankcode, der für den Wechsel ROWID zum Identitätsattribut erforderlich ist. Erhebliche Auswirkung auf die Leistung, wenn das zusätzliche Feld nicht indexiert wird. Sie können jedoch einen Index hinzufügen, um Leistungsprobleme zu vermeiden.

Tools

• Amazon Relational Database Service (Amazon RDS) für PostgreSQL unterstützt Sie bei der Einrichtung, dem Betrieb und der Skalierung einer relationalen PostgreSQL-Datenbank in der AWS-Cloud.

• Amazon Aurora PostgreSQL-Compatible Edition ist eine vollständig verwaltete, ACID-konforme relationale Datenbank-Engine, die Sie bei der Einrichtung, dem Betrieb und der Skalierung von PostgreSQL-Bereitstellungen unterstützt.

• AWS Command Line Interface (AWS CLI) ist ein Open-Source-Tool, mit dem Sie über Befehle in Ihrer Befehlszeilen-Shell mit AWS-Services interagieren können. In diesem Muster können Sie die AWS-CLI verwenden, um SQL-Befehle über pgAdmin auszuführen.

• pgAdmin ist ein Open-Source-Verwaltungstool für PostgreSQL. Es bietet eine grafische Oberfläche, mit der Sie Datenbankobjekte erstellen, verwalten und verwenden können.

• Das AWS Schema Conversion Tool (AWS SCT) unterstützt heterogene Datenbankmigrationen, indem das Quelldatenbankschema und ein Großteil des benutzerdefinierten Codes automatisch in ein Format konvertiert werden, das mit der Zieldatenbank kompatibel ist.

Epen

Identifizieren Sie die Quelltabellen

Aufgabe	Beschreibung	Erforderliche Fähigkeiten
Identifizieren Sie Oracle-Tabellen, die das ROWID Attribut verwenden.	Verwenden Sie das AWS Schema Conversion Tool (AWS SCT), um Oracle-Tabellen zu identifizieren, die über ROWID Funktionen verfügen. Weitere Informationen finden Sie in der AWS SCT-Dokumentation. –oder– Verwenden Sie in Oracle die DBA_TAB_COLUMNS Ansicht, um Tabellen zu identifizieren, die ein ROWID Attribut haben. Diese Felder können zum Speichern von alphanumerischen 10-Byte-Zeichen verwendet werden. Ermitteln Sie die Verwendung und konvertieren Sie diese gegebenenfalls in ein VARCHAR Feld.	DBA oder Entwickler
Identifizieren Sie den Code, der auf diese Tabellen verweist.	Verwenden Sie AWS SCT, um einen Migrationsbewertungsbericht zu erstellen, um die Verfahren zu identifizieren, die betroffen sind vonROWID. Weitere Informationen finden Sie in der AWS SCT-Dokumentation. –oder– Verwenden Sie in der Oracle-Quelldatenbank das Textfeld der dba_source Tabelle, um Objekte zu identifizieren, die ROWID Funktionen verwenden.	DBA oder Entwickler

Identifizieren Sie die Quelltabellen

Aufgabe	Beschreibung	Erforderliche Fähigkeiten
Identifizieren Sie Oracle-Tabellen, die das ROWID Attribut verwenden.	Verwenden Sie das AWS Schema Conversion Tool (AWS SCT), um Oracle-Tabellen zu identifizieren, die über ROWID Funktionen verfügen. Weitere Informationen finden Sie in der AWS SCT-Dokumentation. –oder– Verwenden Sie in Oracle die DBA_TAB_COLUMNS Ansicht, um Tabellen zu identifizieren, die ein ROWID Attribut haben. Diese Felder können zum Speichern von alphanumerischen 10-Byte-Zeichen verwendet werden. Ermitteln Sie die Verwendung und konvertieren Sie diese gegebenenfalls in ein VARCHAR Feld.	DBA oder Entwickler
Identifizieren Sie den Code, der auf diese Tabellen verweist.	Verwenden Sie AWS SCT, um einen Migrationsbewertungsbericht zu erstellen, um die Verfahren zu identifizieren, die betroffen sind vonROWID. Weitere Informationen finden Sie in der AWS SCT-Dokumentation. –oder– Verwenden Sie in der Oracle-Quelldatenbank das Textfeld der dba_source Tabelle, um Objekte zu identifizieren, die ROWID Funktionen verwenden.	DBA oder Entwickler

Ermitteln Sie die Verwendung von Primärschlüsseln

Aufgabe	Beschreibung	Erforderliche Fähigkeiten
Identifizieren Sie Tabellen, die keine Primärschlüssel haben.	Verwenden Sie dies in der Oracle-Quelldatenbank, DBA_CONSTRAINTS um Tabellen zu identifizieren, die keine Primärschlüssel haben. Anhand dieser Informationen können Sie die Strategie für jede Tabelle festlegen. Zum Beispiel: 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}'	DBA oder Entwickler

Ermitteln Sie die Verwendung von Primärschlüsseln

Aufgabe	Beschreibung	Erforderliche Fähigkeiten
Identifizieren Sie Tabellen, die keine Primärschlüssel haben.	Verwenden Sie dies in der Oracle-Quelldatenbank, DBA_CONSTRAINTS um Tabellen zu identifizieren, die keine Primärschlüssel haben. Anhand dieser Informationen können Sie die Strategie für jede Tabelle festlegen. Zum Beispiel: 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}'	DBA oder Entwickler

Identifizieren Sie die Lösung und wenden Sie sie an

Aufgabe	Beschreibung	Erforderliche Fähigkeiten
Wenden Sie Änderungen für Tabellen an, die einen definierten oder logischen Primärschlüssel haben.	Nehmen Sie die im Abschnitt Zusätzliche Informationen aufgeführten Änderungen am Anwendungs- und Datenbankcode vor, um einen eindeutigen Primärschlüssel oder einen logischen Primärschlüssel zur Identifizierung einer Zeile in Ihrer Tabelle zu verwenden.	DBA oder Entwickler
Fügen Sie Tabellen, die keinen definierten oder logischen Primärschlüssel haben, ein zusätzliches Feld hinzu.	Fügen Sie ein Attribut des Typs hinzuGENERATED ALWAYS AS IDENTITY. Nehmen Sie die Änderungen an der Anwendung und dem Datenbankcode vor, die im Abschnitt Zusätzliche Informationen aufgeführt sind.	DBA oder Entwickler
Fügen Sie bei Bedarf einen Index hinzu.	Fügen Sie dem zusätzlichen Feld oder dem logischen Primärschlüssel einen Index hinzu, um die SQL-Leistung zu verbessern.	DBA oder Entwickler

Identifizieren Sie die Lösung und wenden Sie sie an

Aufgabe	Beschreibung	Erforderliche Fähigkeiten
Wenden Sie Änderungen für Tabellen an, die einen definierten oder logischen Primärschlüssel haben.	Nehmen Sie die im Abschnitt Zusätzliche Informationen aufgeführten Änderungen am Anwendungs- und Datenbankcode vor, um einen eindeutigen Primärschlüssel oder einen logischen Primärschlüssel zur Identifizierung einer Zeile in Ihrer Tabelle zu verwenden.	DBA oder Entwickler
Fügen Sie Tabellen, die keinen definierten oder logischen Primärschlüssel haben, ein zusätzliches Feld hinzu.	Fügen Sie ein Attribut des Typs hinzuGENERATED ALWAYS AS IDENTITY. Nehmen Sie die Änderungen an der Anwendung und dem Datenbankcode vor, die im Abschnitt Zusätzliche Informationen aufgeführt sind.	DBA oder Entwickler
Fügen Sie bei Bedarf einen Index hinzu.	Fügen Sie dem zusätzlichen Feld oder dem logischen Primärschlüssel einen Index hinzu, um die SQL-Leistung zu verbessern.	DBA oder Entwickler

Zugehörige Ressourcen

• PostgreSQL CTID (PostgreSQL-Dokumentation)

• Generierte Spalten (PostgreSQL-Dokumentation)

• ROWID-Pseudospalte (Oracle-Dokumentation)

Zusätzliche Informationen

Die folgenden Abschnitte enthalten Oracle- und PostgreSQL-Codebeispiele, um die drei Ansätze zu veranschaulichen.

Szenario 1: Verwendung eines eindeutigen Primärschlüssels

In den folgenden Beispielen erstellen Sie die Tabelle testrowid_s1 mit emp_id als Primärschlüssel.

Oracle-Code:

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-Code:

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

Szenario 2: Verwendung eines logischen Primärschlüssels

In den folgenden Beispielen erstellen Sie die Tabelle testrowid_s2 mit emp_id dem logischen Primärschlüssel.

Oracle-Code:

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-Code:

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

Szenario 3: Verwendung eines Identitätsattributs

In den folgenden Beispielen erstellen Sie die Tabelle testrowid_s3 ohne Primärschlüssel und mithilfe eines Identitätsattributs.

Oracle-Code:

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-Code:

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