Description of OID and XID in PostgreSQL
- 2020-05-06 11:51:52
- OfStack
oid: the row object identifier (object ID). This field only appears when the table is created using WITH OIDS, or when the default_with_oids configuration parameter is set. The type of this field is oid (the same name as the field).
Example:
OID is a 32-bit quantity assigned on a common counter within the same cluster. For a large or long-running database, this counter may overlap. Therefore, it is very wrong to assume that OID are unique, unless you have taken steps to ensure that they are.
XID: transaction ID, also a 32-bit quantity. It may also overlap in long-running databases. When the database overlaps, errors may occur, so measures should be taken to solve them. As long as we take some proper maintenance steps, this is not a serious problem. So let's say we define vaccum. Or reset it manually.
Reset with the following command:
$ pg_resetxlog --help
The pg_resetxlog command resets the PostgreSQL transaction log.
Usage:
pg_resetxlog [OPTION]... DATADIR
Option parameter:
-e XIDEPOCH sets the next transaction ID EPOCH
-f implements mandatory updates to
-l TLI,FILE,SEG forces the minimum WAL starting position
for new transaction log to be set -m XID sets up the next MULTI transaction ID
-n does not update, just take out the current control value, the experimental command
-o OID sets the next OID, resets OID
-O OFFSET sets OFFSET
for the next MULTI transaction ID -x XID sets the next transaction ID, resets XID
Having written so much, I still feel confused about what's going on inside. Ha ha!!!!!
Example:
CREATE TABLE pg_language
(
lanname name NOT NULL,
lanowner oid NOT NULL,
lanispl boolean NOT NULL,
lanpltrusted boolean NOT NULL,
lanplcallfoid oid NOT NULL,
lanvalidator oid NOT NULL,
lanacl aclitem[]
)
WITH (
OIDS=TRUE
);
OID is a 32-bit quantity assigned on a common counter within the same cluster. For a large or long-running database, this counter may overlap. Therefore, it is very wrong to assume that OID are unique, unless you have taken steps to ensure that they are.
XID: transaction ID, also a 32-bit quantity. It may also overlap in long-running databases. When the database overlaps, errors may occur, so measures should be taken to solve them. As long as we take some proper maintenance steps, this is not a serious problem. So let's say we define vaccum. Or reset it manually.
Reset with the following command:
$ pg_resetxlog --help
The pg_resetxlog command resets the PostgreSQL transaction log.
Usage:
pg_resetxlog [OPTION]... DATADIR
Option parameter:
-e XIDEPOCH sets the next transaction ID EPOCH
-f implements mandatory updates to
-l TLI,FILE,SEG forces the minimum WAL starting position
for new transaction log to be set -m XID sets up the next MULTI transaction ID
-n does not update, just take out the current control value, the experimental command
-o OID sets the next OID, resets OID
-O OFFSET sets OFFSET
for the next MULTI transaction ID -x XID sets the next transaction ID, resets XID
Having written so much, I still feel confused about what's going on inside. Ha ha!!!!!