From 578b229718e8f15fa779e20f086c4b6bb3776106 Mon Sep 17 00:00:00 2001 From: Andres Freund Date: Tue, 20 Nov 2018 15:36:57 -0800 Subject: Remove WITH OIDS support, change oid catalog column visibility. Previously tables declared WITH OIDS, including a significant fraction of the catalog tables, stored the oid column not as a normal column, but as part of the tuple header. This special column was not shown by default, which was somewhat odd, as it's often (consider e.g. pg_class.oid) one of the more important parts of a row. Neither pg_dump nor COPY included the contents of the oid column by default. The fact that the oid column was not an ordinary column necessitated a significant amount of special case code to support oid columns. That already was painful for the existing, but upcoming work aiming to make table storage pluggable, would have required expanding and duplicating that "specialness" significantly. WITH OIDS has been deprecated since 2005 (commit ff02d0a05280e0). Remove it. Removing includes: - CREATE TABLE and ALTER TABLE syntax for declaring the table to be WITH OIDS has been removed (WITH (oids[ = true]) will error out) - pg_dump does not support dumping tables declared WITH OIDS and will issue a warning when dumping one (and ignore the oid column). - restoring an pg_dump archive with pg_restore will warn when restoring a table with oid contents (and ignore the oid column) - COPY will refuse to load binary dump that includes oids. - pg_upgrade will error out when encountering tables declared WITH OIDS, they have to be altered to remove the oid column first. - Functionality to access the oid of the last inserted row (like plpgsql's RESULT_OID, spi's SPI_lastoid, ...) has been removed. The syntax for declaring a table WITHOUT OIDS (or WITH (oids = false) for CREATE TABLE) is still supported. While that requires a bit of support code, it seems unnecessary to break applications / dumps that do not use oids, and are explicit about not using them. The biggest user of WITH OID columns was postgres' catalog. This commit changes all 'magic' oid columns to be columns that are normally declared and stored. To reduce unnecessary query breakage all the newly added columns are still named 'oid', even if a table's column naming scheme would indicate 'reloid' or such. This obviously requires adapting a lot code, mostly replacing oid access via HeapTupleGetOid() with access to the underlying Form_pg_*->oid column. The bootstrap process now assigns oids for all oid columns in genbki.pl that do not have an explicit value (starting at the largest oid previously used), only oids assigned later by oids will be above FirstBootstrapObjectId. As the oid column now is a normal column the special bootstrap syntax for oids has been removed. Oids are not automatically assigned during insertion anymore, all backend code explicitly assigns oids with GetNewOidWithIndex(). For the rare case that insertions into the catalog via SQL are called for the new pg_nextoid() function can be used (which only works on catalog tables). The fact that oid columns on system tables are now normal columns means that they will be included in the set of columns expanded by * (i.e. SELECT * FROM pg_class will now include the table's oid, previously it did not). It'd not technically be hard to hide oid column by default, but that'd mean confusing behavior would either have to be carried forward forever, or it'd cause breakage down the line. While it's not unlikely that further adjustments are needed, the scope/invasiveness of the patch makes it worthwhile to get merge this now. It's painful to maintain externally, too complicated to commit after the code code freeze, and a dependency of a number of other patches. Catversion bump, for obvious reasons. Author: Andres Freund, with contributions by John Naylor Discussion: https://postgr.es/m/20180930034810.ywp2c7awz7opzcfr@alap3.anarazel.de --- doc/src/sgml/bki.sgml | 39 +++++------- doc/src/sgml/catalogs.sgml | 75 ++++++++++------------ doc/src/sgml/config.sgml | 29 --------- doc/src/sgml/datatype.sgml | 23 +++---- doc/src/sgml/ddl.sgml | 58 ----------------- doc/src/sgml/file-fdw.sgml | 5 +- doc/src/sgml/plpgsql.sgml | 8 --- doc/src/sgml/pltcl.sgml | 18 ------ doc/src/sgml/protocol.sgml | 9 +-- doc/src/sgml/ref/alter_foreign_table.sgml | 28 +------- doc/src/sgml/ref/alter_table.sgml | 37 +---------- doc/src/sgml/ref/copy.sgml | 32 +++------- doc/src/sgml/ref/create_materialized_view.sgml | 5 +- doc/src/sgml/ref/create_table.sgml | 88 ++++++-------------------- doc/src/sgml/ref/create_table_as.sgml | 35 ++++------ doc/src/sgml/ref/pg_dump.sgml | 14 ---- doc/src/sgml/ref/select_into.sgml | 7 -- doc/src/sgml/release-9.1.sgml | 2 +- doc/src/sgml/release-9.2.sgml | 2 +- doc/src/sgml/release-9.3.sgml | 2 +- 20 files changed, 116 insertions(+), 400 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/bki.sgml b/doc/src/sgml/bki.sgml index 0fb309a1bd9..786abb95d4d 100644 --- a/doc/src/sgml/bki.sgml +++ b/doc/src/sgml/bki.sgml @@ -89,7 +89,7 @@ The CATALOG line can also be annotated, with some other BKI property macros described in genbki.h, to define other properties of the catalog as a whole, such as whether - it has OIDs (by default, it does). + it is a shared relation. @@ -354,7 +354,7 @@ also needed if the row's OID must be referenced from C code. If neither case applies, the oid metadata field can be omitted, in which case the bootstrap code assigns an OID - automatically, or leaves it zero in a catalog that has no OIDs. + automatically. In practice we usually preassign OIDs for all or none of the pre-loaded rows in a given catalog, even if only some of them are actually cross-referenced. @@ -387,15 +387,16 @@ through the catalog headers and .dat files to see which ones do not appear. You can also use the duplicate_oids script to check for mistakes. - (genbki.pl will also detect duplicate OIDs + (genbki.pl will assign OIDs for any rows that + didn't get one hand-assigned to them and also detect duplicate OIDs at compile time.) The OID counter starts at 10000 at the beginning of a bootstrap run. - If a catalog row is in a table that requires OIDs, but no OID was - preassigned by an oid field, then it will - receive an OID of 10000 or above. + If a row from a source other than postgres.bki + is inserted into a table that requires OIDs, then it will receive an + OID of 10000 or above. @@ -714,7 +715,6 @@ $ perl rewrite_dat_with_prokind.pl pg_proc.dat tableoid bootstrap shared_relation - without_oids rowtype_oid oid (name1 = type1 @@ -766,7 +766,6 @@ $ perl rewrite_dat_with_prokind.pl pg_proc.dat The table is created as shared if shared_relation is specified. - It will have OIDs unless without_oids is specified. The table's row type OID (pg_type OID) can optionally be specified via the rowtype_oid clause; if not specified, an OID is automatically generated for it. (The rowtype_oid @@ -805,7 +804,7 @@ $ perl rewrite_dat_with_prokind.pl pg_proc.dat - insert OID = oid_value ( value1 value2 ... ) + insert ( oid_value value1 value2 ... ) @@ -813,11 +812,7 @@ $ perl rewrite_dat_with_prokind.pl pg_proc.dat Insert a new row into the open table using value1, value2, etc., for its column - values and oid_value for its OID. If - oid_value is zero - (0) or the clause is omitted, and the table has OIDs, then the - next available OID is assigned. + values. @@ -985,16 +980,16 @@ $ perl rewrite_dat_with_prokind.pl pg_proc.dat BKI Example - The following sequence of commands will create the - table test_table with OID 420, having two columns - cola and colb of type - int4 and text, respectively, and insert - two rows into the table: + The following sequence of commands will create the table + test_table with OID 420, having three columns + oid, cola and colb + of type oid, int4 and text, + respectively, and insert two rows into the table: -create test_table 420 (cola = int4, colb = text) +create test_table 420 (oid = oid, cola = int4, colb = text) open test_table -insert OID=421 ( 1 "value1" ) -insert OID=422 ( 2 _null_ ) +insert ( 421 1 "value1" ) +insert ( 422 2 _null_ ) close test_table diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 8b7f169d50c..c134bca8096 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -693,7 +693,7 @@ oid oid - Row identifier (hidden attribute; must be explicitly selected) + Row identifier @@ -836,7 +836,7 @@ oid oid - Row identifier (hidden attribute; must be explicitly selected) + Row identifier @@ -926,7 +926,7 @@ oid oid - Row identifier (hidden attribute; must be explicitly selected) + Row identifier @@ -1312,7 +1312,7 @@ oid oid - Row identifier (hidden attribute; must be explicitly selected) + Row identifier @@ -1538,7 +1538,7 @@ SCRAM-SHA-256$<iteration count>:&l oid oid - Row identifier (hidden attribute; must be explicitly selected) + Row identifier @@ -1661,7 +1661,7 @@ SCRAM-SHA-256$<iteration count>:&l oid oid - Row identifier (hidden attribute; must be explicitly selected) + Row identifier @@ -1853,15 +1853,6 @@ SCRAM-SHA-256$<iteration count>:&l - - relhasoids - bool - - - True if we generate an OID for each row of the relation - - - relhasrules bool @@ -2055,7 +2046,7 @@ SCRAM-SHA-256$<iteration count>:&l oid oid - Row identifier (hidden attribute; must be explicitly selected) + Row identifier @@ -2193,7 +2184,7 @@ SCRAM-SHA-256$<iteration count>:&l oid oid - Row identifier (hidden attribute; must be explicitly selected) + Row identifier @@ -2460,7 +2451,7 @@ SCRAM-SHA-256$<iteration count>:&l oid oid - Row identifier (hidden attribute; must be explicitly selected) + Row identifier @@ -2560,7 +2551,7 @@ SCRAM-SHA-256$<iteration count>:&l oid oid - Row identifier (hidden attribute; must be explicitly selected) + Row identifier @@ -2790,7 +2781,7 @@ SCRAM-SHA-256$<iteration count>:&l oid oid - Row identifier (hidden attribute; must be explicitly selected) + Row identifier @@ -3188,7 +3179,7 @@ SCRAM-SHA-256$<iteration count>:&l oid oid - Row identifier (hidden attribute; must be explicitly selected) + Row identifier @@ -3351,7 +3342,7 @@ SCRAM-SHA-256$<iteration count>:&l oid oid - Row identifier (hidden attribute; must be explicitly selected) + Row identifier @@ -3454,7 +3445,7 @@ SCRAM-SHA-256$<iteration count>:&l oid oid - Row identifier (hidden attribute; must be explicitly selected) + Row identifier @@ -3553,7 +3544,7 @@ SCRAM-SHA-256$<iteration count>:&l oid oid - Row identifier (hidden attribute; must be explicitly selected) + Row identifier @@ -4106,7 +4097,7 @@ SCRAM-SHA-256$<iteration count>:&l oid oid - Row identifier (hidden attribute; must be explicitly selected) + Row identifier @@ -4313,7 +4304,7 @@ SCRAM-SHA-256$<iteration count>:&l oid oid - Row identifier (hidden attribute; must be explicitly selected) + Row identifier @@ -4373,7 +4364,7 @@ SCRAM-SHA-256$<iteration count>:&l oid oid - Row identifier (hidden attribute; must be explicitly selected) + Row identifier @@ -4448,7 +4439,7 @@ SCRAM-SHA-256$<iteration count>:&l oid oid - Row identifier (hidden attribute; must be explicitly selected) + Row identifier @@ -4553,7 +4544,7 @@ SCRAM-SHA-256$<iteration count>:&l oid oid - Row identifier (hidden attribute; must be explicitly selected) + Row identifier @@ -4710,7 +4701,7 @@ SCRAM-SHA-256$<iteration count>:&l oid oid - Row identifier (hidden attribute; must be explicitly selected) + Row identifier @@ -5120,7 +5111,7 @@ SCRAM-SHA-256$<iteration count>:&l oid oid - Row identifier (hidden attribute; must be explicitly selected) + Row identifier @@ -5458,7 +5449,7 @@ SCRAM-SHA-256$<iteration count>:&l oid oid - Row identifier (hidden attribute; must be explicitly selected) + Row identifier @@ -5734,7 +5725,7 @@ SCRAM-SHA-256$<iteration count>:&l oid oid - Row identifier (hidden attribute; must be explicitly selected) + Row identifier @@ -6616,7 +6607,7 @@ SCRAM-SHA-256$<iteration count>:&l oid oid - Row identifier (hidden attribute; must be explicitly selected) + Row identifier @@ -6797,7 +6788,7 @@ SCRAM-SHA-256$<iteration count>:&l oid oid - Row identifier (hidden attribute; must be explicitly selected) + Row identifier @@ -6940,7 +6931,7 @@ SCRAM-SHA-256$<iteration count>:&l oid oid - Row identifier (hidden attribute; must be explicitly selected) + Row identifier @@ -7149,7 +7140,7 @@ SCRAM-SHA-256$<iteration count>:&l oid oid - Row identifier (hidden attribute; must be explicitly selected) + Row identifier @@ -7295,7 +7286,7 @@ SCRAM-SHA-256$<iteration count>:&l oid oid - Row identifier (hidden attribute; must be explicitly selected) + Row identifier @@ -7378,7 +7369,7 @@ SCRAM-SHA-256$<iteration count>:&l oid oid - Row identifier (hidden attribute; must be explicitly selected) + Row identifier @@ -7475,7 +7466,7 @@ SCRAM-SHA-256$<iteration count>:&l oid oid - Row identifier (hidden attribute; must be explicitly selected) + Row identifier @@ -7549,7 +7540,7 @@ SCRAM-SHA-256$<iteration count>:&l oid oid - Row identifier (hidden attribute; must be explicitly selected) + Row identifier @@ -8061,7 +8052,7 @@ SCRAM-SHA-256$<iteration count>:&l oid oid - Row identifier (hidden attribute; must be explicitly selected) + Row identifier diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 5d76862f461..d1dab355590 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -7942,35 +7942,6 @@ dynamic_library_path = 'C:\tools\postgresql;H:\my_project\lib;$libdir' - - default_with_oids (boolean) - - default_with_oids configuration parameter - - - - - This controls whether CREATE TABLE and - CREATE TABLE AS include an OID column in - newly-created tables, if neither WITH OIDS - nor WITHOUT OIDS is specified. It also - determines whether OIDs will be included in tables created by - SELECT INTO. The parameter is off - by default; in PostgreSQL 8.0 and earlier, it - was on by default. - - - - The use of OIDs in user tables is considered deprecated, so - most installations should leave this variable disabled. - Applications that require OIDs for a particular table should - specify WITH OIDS when creating the - table. This variable can be enabled for compatibility with old - applications that do not follow this behavior. - - - - escape_string_warning (boolean) stringsescape warning diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml index 8c38dde8fb8..cae3fa95a89 100644 --- a/doc/src/sgml/datatype.sgml +++ b/doc/src/sgml/datatype.sgml @@ -4497,25 +4497,22 @@ INSERT INTO mytable VALUES(-1); -- fails Object identifiers (OIDs) are used internally by PostgreSQL as primary keys for various - system tables. OIDs are not added to user-created tables, unless - WITH OIDS is specified when the table is - created, or the - configuration variable is enabled. Type oid represents - an object identifier. There are also several alias types for - oid: regproc, regprocedure, - regoper, regoperator, regclass, - regtype, regrole, regnamespace, - regconfig, and regdictionary. - shows an overview. + system tables. + + Type oid represents an object identifier. There are also + several alias types for oid: regproc, + regprocedure, regoper, regoperator, + regclass, regtype, regrole, + regnamespace, regconfig, and + regdictionary. shows an + overview. The oid type is currently implemented as an unsigned four-byte integer. Therefore, it is not large enough to provide database-wide uniqueness in large databases, or even in large - individual tables. So, using a user-created table's OID column as - a primary key is discouraged. OIDs are best used only for - references to system tables. + individual tables. diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index c8268222af7..61c4a254603 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -938,24 +938,6 @@ CREATE TABLE circles ( - - oid - - - - OID - column - - The object identifier (object ID) of a row. This column is only - present if the table was created using WITH - OIDS, or if the - configuration variable was set at the time. This column is of type - oid (same name as the column); see for more information about the type. - - - - tableoid @@ -1056,46 +1038,6 @@ CREATE TABLE circles ( - - OIDs are 32-bit quantities and are assigned from a single - cluster-wide counter. In a large or long-lived database, it is - possible for the counter to wrap around. Hence, it is bad - practice to assume that OIDs are unique, unless you take steps to - ensure that this is the case. If you need to identify the rows in - a table, using a sequence generator is strongly recommended. - However, OIDs can be used as well, provided that a few additional - precautions are taken: - - - - - A unique constraint should be created on the OID column of each - table for which the OID will be used to identify rows. When such - a unique constraint (or unique index) exists, the system takes - care not to generate an OID matching an already-existing row. - (Of course, this is only possible if the table contains fewer - than 232 (4 billion) rows, and in practice the - table size had better be much less than that, or performance - might suffer.) - - - - - OIDs should never be assumed to be unique across tables; use - the combination of tableoid and row OID if you - need a database-wide identifier. - - - - - Of course, the tables in question must be created WITH - OIDS. As of PostgreSQL 8.1, - WITHOUT OIDS is the default. - - - - - Transaction identifiers are also 32-bit quantities. In a long-lived database it is possible for transaction IDs to wrap diff --git a/doc/src/sgml/file-fdw.sgml b/doc/src/sgml/file-fdw.sgml index 955a13ab7d9..19be824b039 100644 --- a/doc/src/sgml/file-fdw.sgml +++ b/doc/src/sgml/file-fdw.sgml @@ -174,9 +174,8 @@ - COPY's OIDS and - FORCE_QUOTE options are currently not supported by - file_fdw. + COPY's FORCE_QUOTE options is + currently not supported by file_fdw. diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index beb7e03bbcf..60b0c72ff32 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -1485,14 +1485,6 @@ GET DIAGNOSTICS integer_var = ROW_COUNT; the number of rows processed by the most recent SQL command - - RESULT_OID - oid - the OID of the last row inserted by the most - recent SQL command (only useful after - an INSERT command into a table having - OIDs) - PG_CONTEXT text diff --git a/doc/src/sgml/pltcl.sgml b/doc/src/sgml/pltcl.sgml index 4dd6fe434fa..7ff828de700 100644 --- a/doc/src/sgml/pltcl.sgml +++ b/doc/src/sgml/pltcl.sgml @@ -470,24 +470,6 @@ $$ LANGUAGE pltcl; - - - spi_lastoid - - spi_lastoid - in PL/Tcl - - - - - Returns the OID of the row inserted by the last - spi_exec or spi_execp, if the - command was a single-row INSERT and the modified - table contained OIDs. (If not, you get zero.) - - - - subtransaction command diff --git a/doc/src/sgml/protocol.sgml b/doc/src/sgml/protocol.sgml index f0b21452084..9f7fb0c1d04 100644 --- a/doc/src/sgml/protocol.sgml +++ b/doc/src/sgml/protocol.sgml @@ -3837,10 +3837,11 @@ CommandComplete (B) INSERT oid rows, where rows is the number of rows - inserted. oid is the object ID - of the inserted row if rows is 1 - and the target table has OIDs; - otherwise oid is 0. + inserted. oid used to be the object ID + of the inserted row if rows was 1 + and the target table had OIDs, but OIDs system columns are + not supported anymore; therefore oid + is always 0. diff --git a/doc/src/sgml/ref/alter_foreign_table.sgml b/doc/src/sgml/ref/alter_foreign_table.sgml index f266be0c37b..b27eb6f2aae 100644 --- a/doc/src/sgml/ref/alter_foreign_table.sgml +++ b/doc/src/sgml/ref/alter_foreign_table.sgml @@ -50,7 +50,6 @@ ALTER FOREIGN TABLE [ IF EXISTS ] nametrigger_name | ALL | USER ] ENABLE REPLICA TRIGGER trigger_name ENABLE ALWAYS TRIGGER trigger_name - SET WITH OIDS SET WITHOUT OIDS INHERIT parent_table NO INHERIT parent_table @@ -223,34 +222,13 @@ ALTER FOREIGN TABLE [ IF EXISTS ] name - - SET WITH OIDS - - - This form adds an oid system column to the - table (see ). - It does nothing if the table already has OIDs. - Unless the table's foreign-data wrapper supports OIDs, this column - will simply read as zeroes. - - - - Note that this is not equivalent to ADD COLUMN oid oid; - that would add a normal column that happened to be named - oid, not a system column. - - - - SET WITHOUT OIDS - This form removes the oid system column from the - table. This is exactly equivalent to - DROP COLUMN oid RESTRICT, - except that it will not complain if there is already no - oid column. + Backward compatibility syntax for removing the oid + system column. As oid system columns cannot be added anymore, this never + has an effect. diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index f13a6cd944d..be1647937dc 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -72,7 +72,6 @@ ALTER TABLE [ IF EXISTS ] name NO FORCE ROW LEVEL SECURITY CLUSTER ON index_name SET WITHOUT CLUSTER - SET WITH OIDS SET WITHOUT OIDS SET TABLESPACE new_tablespace SET { LOGGED | UNLOGGED } @@ -613,32 +612,13 @@ WITH ( MODULUS numeric_literal, REM - - SET WITH OIDS - - - This form adds an oid system column to the - table (see ). - It does nothing if the table already has OIDs. - - - - Note that this is not equivalent to ADD COLUMN oid oid; - that would add a normal column that happened to be named - oid, not a system column. - - - - SET WITHOUT OIDS - This form removes the oid system column from the - table. This is exactly equivalent to - DROP COLUMN oid RESTRICT, - except that it will not complain if there is already no - oid column. + Backward compatibility syntax for removing the oid + system column. As oid system columns cannot be added anymore, this never + has an effect. @@ -704,17 +684,6 @@ WITH ( MODULUS numeric_literal, REM effective_io_concurrency, parallel_workers, seq_page_cost, random_page_cost, n_distinct and n_distinct_inherited. - - - - While CREATE TABLE allows OIDS to be specified - in the WITH (storage_parameter) syntax, - ALTER TABLE does not treat OIDS as a - storage parameter. Instead use the SET WITH OIDS - and SET WITHOUT OIDS forms to change OID status. - - diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml index 9f3c85bf7f9..411941ed31f 100644 --- a/doc/src/sgml/ref/copy.sgml +++ b/doc/src/sgml/ref/copy.sgml @@ -33,7 +33,6 @@ COPY { table_name [ ( where option can be one of: FORMAT format_name - OIDS [ boolean ] FREEZE [ boolean ] DELIMITER 'delimiter_character' NULL 'null_string' @@ -203,18 +202,6 @@ COPY { table_name [ ( - - OIDS - - - Specifies copying the OID for each row. (An error is raised if - OIDS is specified for a table that does not - have OIDs, or in the case of copying a query.) - - - - FREEZE @@ -549,8 +536,6 @@ COPY count place of columns that are null. COPY FROM will raise an error if any line of the input file contains more or fewer columns than are expected. - If OIDS is specified, the OID is read or written as the first column, - preceding the user data columns. @@ -824,7 +809,9 @@ only one flag bit is defined, and the rest must be zero: Bit 16 - if 1, OIDs are included in the data; if 0, not + If 1, OIDs are included in the data; if 0, not. Oid system columns + are not supported in PostgreSQL + anymore, but the format still contains the indicator. @@ -895,10 +882,9 @@ distribution). If OIDs are included in the file, the OID field immediately follows the -field-count word. It is a normal field except that it's not included -in the field-count. In particular it has a length word — this will allow -handling of 4-byte vs. 8-byte OIDs without too much pain, and will allow -OIDs to be shown as null if that ever proves desirable. +field-count word. It is a normal field except that it's not included in the +field-count. Note that oid system columns are not supported in current +versions of PostgreSQL. @@ -1001,7 +987,6 @@ COPY table_name [ ( filename' | STDIN } [ [ WITH ] [ BINARY ] - [ OIDS ] [ DELIMITER [ AS ] 'delimiter' ] [ NULL [ AS ] 'null string' ] [ CSV [ HEADER ] @@ -1013,7 +998,6 @@ COPY { table_name [ ( filename' | STDOUT } [ [ WITH ] [ BINARY ] - [ OIDS ] [ DELIMITER [ AS ] 'delimiter' ] [ NULL [ AS ] 'null string' ] [ CSV [ HEADER ] @@ -1032,12 +1016,12 @@ COPY { table_name [ ( -COPY [ BINARY ] table_name [ WITH OIDS ] +COPY [ BINARY ] table_name FROM { 'filename' | STDIN } [ [USING] DELIMITERS 'delimiter' ] [ WITH NULL AS 'null string' ] -COPY [ BINARY ] table_name [ WITH OIDS ] +COPY [ BINARY ] table_name TO { 'filename' | STDOUT } [ [USING] DELIMITERS 'delimiter' ] [ WITH NULL AS 'null string' ] diff --git a/doc/src/sgml/ref/create_materialized_view.sgml b/doc/src/sgml/ref/create_materialized_view.sgml index eed4273c4b4..7f31ab4d26d 100644 --- a/doc/src/sgml/ref/create_materialized_view.sgml +++ b/doc/src/sgml/ref/create_materialized_view.sgml @@ -45,8 +45,7 @@ CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] table_name CREATE TABLE AS, except that it also remembers the query used to initialize the view, so that it can be refreshed later upon demand. A materialized view has many of the same properties as a table, but there - is no support for temporary materialized views or automatic generation of - OIDs. + is no support for temporary materialized views. @@ -95,7 +94,7 @@ CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] table_name endterm="sql-createtable-storage-parameters-title"/> for more information. All parameters supported for CREATE TABLE are also supported for CREATE MATERIALIZED - VIEW with the exception of OIDS. + VIEW. See for more information. diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index 4b9c8a78017..50d55970020 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -29,7 +29,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI ] ) [ INHERITS ( parent_table [, ... ] ) ] [ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ] -[ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ] +[ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ] [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ] [ TABLESPACE tablespace_name ] @@ -40,7 +40,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI [, ... ] ) ] [ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ] -[ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ] +[ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ] [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ] [ TABLESPACE tablespace_name ] @@ -51,7 +51,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI [, ... ] ) ] { FOR VALUES partition_bound_spec | DEFAULT } [ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ] -[ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ] +[ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ] [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ] [ TABLESPACE tablespace_name ] @@ -531,17 +531,13 @@ WITH ( MODULUS numeric_literal, REM A partition must have the same column names and types as the partitioned - table to which it belongs. If the parent is specified WITH - OIDS then all partitions must have OIDs; the parent's OID - column will be inherited by all partitions just like any other column. - Modifications to the column names or types of a partitioned table, or - the addition or removal of an OID column, will automatically propagate - to all partitions. CHECK constraints will be inherited - automatically by every partition, but an individual partition may specify - additional CHECK constraints; additional constraints with - the same name and condition as in the parent will be merged with the - parent constraint. Defaults may be specified separately for each - partition. + table to which it belongs. Modifications to the column names or types of + a partitioned table will automatically propagate to all partitions. + CHECK constraints will be inherited automatically by + every partition, but an individual partition may specify additional + CHECK constraints; additional constraints with the + same name and condition as in the parent will be merged with the parent + constraint. Defaults may be specified separately for each partition. @@ -1145,46 +1141,21 @@ WITH ( MODULUS numeric_literal, REM This clause specifies optional storage parameters for a table or index; see for more - information. The WITH clause for a - table can also include OIDS=TRUE (or just OIDS) - to specify that rows of the new table - should have OIDs (object identifiers) assigned to them, or - OIDS=FALSE to specify that the rows should not have OIDs. - If OIDS is not specified, the default setting depends upon - the configuration parameter. - (If the new table inherits from any tables that have OIDs, then - OIDS=TRUE is forced even if the command says - OIDS=FALSE.) - - - - If OIDS=FALSE is specified or implied, the new - table does not store OIDs and no OID will be assigned for a row inserted - into it. This is generally considered worthwhile, since it - will reduce OID consumption and thereby postpone the wraparound - of the 32-bit OID counter. Once the counter wraps around, OIDs - can no longer be assumed to be unique, which makes them - considerably less useful. In addition, excluding OIDs from a - table reduces the space required to store the table on disk by - 4 bytes per row (on most machines), slightly improving performance. - - - - To remove OIDs from a table after it has been created, use . + information. For backward-compatibility the WITH + clause for a table can also include OIDS=FALSE to + specify that rows of the new table should not contain OIDs (object + identifiers), OIDS=TRUE is not supported anymore. - WITH OIDS WITHOUT OIDS - These are obsolescent syntaxes equivalent to WITH (OIDS) - and WITH (OIDS=FALSE), respectively. If you wish to give - both an OIDS setting and storage parameters, you must use - the WITH ( ... ) syntax; see above. + This is backward-compatible syntax for declaring a table + WITHOUT OIDS, creating a table WITH + OIDS is not supported anymore. @@ -1528,29 +1499,6 @@ WITH ( MODULUS numeric_literal, REM Notes - - - Using OIDs in new applications is not recommended: where - possible, using an identity column or other sequence - generator as the table's primary key is preferred. However, if - your application does make use of OIDs to identify specific - rows of a table, it is recommended to create a unique constraint - on the oid column of that table, to ensure that - OIDs in the table will indeed uniquely identify rows even after - counter wraparound. Avoid assuming that OIDs are unique across - tables; if you need a database-wide unique identifier, use the - combination of tableoid and row OID for the - purpose. - - - - - The use of OIDS=FALSE is not recommended - for tables with no primary key, since without either an OID or a - unique data key, it is difficult to identify specific rows. - - - PostgreSQL automatically creates an index for each unique constraint and primary key constraint to @@ -2089,7 +2037,7 @@ CREATE TABLE cities_partdef The WITH clause is a PostgreSQL - extension; neither storage parameters nor OIDs are in the standard. + extension; storage parameters are not in the standard. diff --git a/doc/src/sgml/ref/create_table_as.sgml b/doc/src/sgml/ref/create_table_as.sgml index 527138e7872..679e8f521ed 100644 --- a/doc/src/sgml/ref/create_table_as.sgml +++ b/doc/src/sgml/ref/create_table_as.sgml @@ -23,7 +23,7 @@ PostgreSQL documentation CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name [ (column_name [, ...] ) ] - [ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ] + [ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ] [ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ] [ TABLESPACE tablespace_name ] AS query @@ -127,25 +127,22 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI This clause specifies optional storage parameters for the new table; see for more - information. The WITH clause - can also include OIDS=TRUE (or just OIDS) - to specify that rows of the new table - should have OIDs (object identifiers) assigned to them, or - OIDS=FALSE to specify that the rows should not have OIDs. - See for more information. + information. For backward-compatibility the WITH + clause for a table can also include OIDS=FALSE to + specify that rows of the new table should contain no OIDs (object + identifiers), OIDS=TRUE is not supported anymore. + OIDs. - WITH OIDS WITHOUT OIDS - These are obsolescent syntaxes equivalent to WITH (OIDS) - and WITH (OIDS=FALSE), respectively. If you wish to give - both an OIDS setting and storage parameters, you must use - the WITH ( ... ) syntax; see above. + This is backward-compatible syntax for declaring a table + WITHOUT OIDS, creating a table WITH + OIDS is not supported anymore. @@ -245,14 +242,6 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI TABLE AS offers a superset of the functionality offered by SELECT INTO. - - - The CREATE TABLE AS command allows the user to - explicitly specify whether OIDs should be included. If the - presence of OIDs is not explicitly specified, - the configuration variable is - used. - @@ -281,12 +270,12 @@ CREATE TABLE films2 AS Create a new temporary table films_recent, consisting of only recent entries from the table films, using a - prepared statement. The new table has OIDs and will be dropped at commit: + prepared statement. The new table will be dropped at commit: PREPARE recentfilms(date) AS SELECT * FROM films WHERE date_prod > $1; -CREATE TEMP TABLE films_recent WITH (OIDS) ON COMMIT DROP AS +CREATE TEMP TABLE films_recent ON COMMIT DROP AS EXECUTE recentfilms('2002-01-01'); @@ -325,7 +314,7 @@ CREATE TEMP TABLE films_recent WITH (OIDS) ON COMMIT DROP AS The WITH clause is a PostgreSQL - extension; neither storage parameters nor OIDs are in the standard. + extension; storage parameters are not in the standard. diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml index b5fa4fb85cc..2015410a421 100644 --- a/doc/src/sgml/ref/pg_dump.sgml +++ b/doc/src/sgml/ref/pg_dump.sgml @@ -438,20 +438,6 @@ PostgreSQL documentation - - - - - - Dump object identifiers (OIDs) as part of the - data for every table. Use this option if your application references - the OID - columns in some way (e.g., in a foreign key constraint). - Otherwise, this option should not be used. - - - - diff --git a/doc/src/sgml/ref/select_into.sgml b/doc/src/sgml/ref/select_into.sgml index 6c1a25f5ed5..462e3723819 100644 --- a/doc/src/sgml/ref/select_into.sgml +++ b/doc/src/sgml/ref/select_into.sgml @@ -104,13 +104,6 @@ SELECT [ ALL | DISTINCT [ ON ( expressionCREATE TABLE AS offers a superset of the functionality provided by SELECT INTO. - - - To add OIDs to the table created by SELECT INTO, - enable the configuration - variable. Alternatively, CREATE TABLE AS can be - used with the WITH OIDS clause. - diff --git a/doc/src/sgml/release-9.1.sgml b/doc/src/sgml/release-9.1.sgml index e6ce80032fd..3407d8ad739 100644 --- a/doc/src/sgml/release-9.1.sgml +++ b/doc/src/sgml/release-9.1.sgml @@ -3654,7 +3654,7 @@ Branch: REL9_0_STABLE [9d6af7367] 2015-08-15 11:02:34 -0400 Prevent foreign tables from being created with OIDS - when is true + when default_with_oids" is true (Etsuro Fujita) diff --git a/doc/src/sgml/release-9.2.sgml b/doc/src/sgml/release-9.2.sgml index 3494ddb5cef..1e5f4e04c33 100644 --- a/doc/src/sgml/release-9.2.sgml +++ b/doc/src/sgml/release-9.2.sgml @@ -5650,7 +5650,7 @@ Branch: REL9_2_STABLE [6b700301c] 2015-02-17 16:03:00 +0100 Prevent foreign tables from being created with OIDS - when is true + when default_with_oids" is true (Etsuro Fujita) diff --git a/doc/src/sgml/release-9.3.sgml b/doc/src/sgml/release-9.3.sgml index 0c1498015ba..23868d65d8e 100644 --- a/doc/src/sgml/release-9.3.sgml +++ b/doc/src/sgml/release-9.3.sgml @@ -9237,7 +9237,7 @@ Branch: REL9_1_STABLE [dd1a5b09b] 2014-06-24 13:30:41 +0300 Prevent foreign tables from being created with OIDS - when is true + when default_with_oids" is true (Etsuro Fujita) -- cgit v1.2.3