Oid is a hidden column of the system. Direct modification is not acceptable.
Mysql = # update pg_class set oid = 99999 where oid = 73728;
ERROR: cannot assign to system column "oid"
LINE 1: update pg_class set oid = 99999 where oid = 73728;
But we can delete it.
Mysql = # delete from pg_class where oid = 73728;
DELETE 1
There is a copy command in postgresql, which has a parameter with oids, which can be used to import the oid together. with this feature, we can achieve the oid modification function.
1. Create a new file and put the data into it. The new oid has been modified.
[Mysql @ pttest4 cxf] $ cat 2.dat
73740 | test | 2200 | 73729 | 10 | 0 | 73740 | 0 | 0 | 0 | 0 | f | r | 1 | 0 | 0 | 0 | 0 | 0 | 0 | f | 814 |/N
2. Use the copy command to import data
Copy pg_class from '/home/mysql/cxf/2. dat 'null as E' // n' csv delimiter' | 'oids;
3. modify corresponding oid data in other tables
Update pg_attribute set attrelid = '000000' where attrelid = '000000 ';
Update pg_depend set refobjid = '000000' where refobjid = '000000 ';
Update pg_type set typrelid = '000000' where typrelid = '000000 ';
Ps: if other data dictionaries are involved, they should be modified together.
4. the query shows that the oid has been changed.
Mysql = # select * from test;
ERROR: cocould not open relation 1663/16386/73740: No such file or directory
Mysql = # select oid from pg_class where relname = 'test ';
Oid
-------
73740
(1 row)
5. Rename the data file as a new oid.
[Mysql @ pttest4 16386] $ mv 73728 73740
Check the database again. The original data can still be accessed.
Mysql = # select * from test;
A
---
1
(1 row)
Now, the oid is successfully modified.
Other problems caused by oid modification. Because the oid of the current system is 73735, and after the oid (73740) We changed, the oid will be reused when we create a table, which may cause oid disorder. As follows:
Mysql = # create table helloworld (B varchar (1 ));
CREATE TABLE
Mysql = # select oid from pg_class where relname = 'helloworld ';
Oid
-------
73735
(1 row)
Mysql = # create table helloworld1 (B varchar (1 ));
CREATE TABLE
Mysql = # create table helloworld2 (B varchar (1 ));
CREATE TABLE
Mysql = # create table helloworld3 (B varchar (1 ));
CREATE TABLE
Mysql = # select oid from pg_class where relname like 'helloworld % ';
Oid
-------
73735
73737
73739
73741
(4 rows)
Mysql = # select * from pg_type where oid = '000000 ';
Typname | typnamespace | typowner | typlen | percent | typtype | percent | typelem | typinput | percent | typreceive | typsend | typanalyze | typalign | typstorage | percent | typbasetype | percent | typndims | typdefaultbin | typdefault
Accept + accept + ---------- + -------- + ---------- + --------- + accept + ------------ + ---------- + ------------ + ----------- ---------- + --------------- + ------------
Helloworld2 | 2200 | 10 |-1 | f | c | t |, | 73739 | 0 | record_in | record_out | record_recv | record_send |-| d | x | f | 0 |-1 | 0 |
(1 row)
We can see that the new pg_type oid uses 73740.
To avoid this problem, we can use pg_resetxlog to reset the oid.
For example, pg_resetxlog-o 80000 ~ /Postgresql/data/
In this way, when the sub-table is created, the oid starts to weigh 80000 to avoid the problem.