Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hEGZM-0005kr-0u for pgsql-hackers@arkaria.postgresql.org; Wed, 10 Apr 2019 16:59:28 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hEGZK-0003nh-MR for pgsql-hackers@arkaria.postgresql.org; Wed, 10 Apr 2019 16:59:26 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hEGZK-0003na-A5 for pgsql-hackers@lists.postgresql.org; Wed, 10 Apr 2019 16:59:26 +0000 Received: from mail-it1-x12a.google.com ([2607:f8b0:4864:20::12a]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hEGZG-00012Z-Tb for pgsql-hackers@lists.postgresql.org; Wed, 10 Apr 2019 16:59:25 +0000 Received: by mail-it1-x12a.google.com with SMTP id 139so4606323ita.4 for ; Wed, 10 Apr 2019 09:59:22 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=telsasoft-com.20150623.gappssmtp.com; s=20150623; h=date:from:to:cc:subject:message-id:references:mime-version :content-disposition:in-reply-to:user-agent; bh=UKuxAL3DCMJXmmRrbGcyjgqA2zBz1ToVUgz751eSS+s=; b=fyADYpI9fOAYl4dZ5kj2H25aHKvCIBbgRBtXz2u9TRcOVM2eGB1H0Y0kSUqLNRpr+z 6hvVLupa5m61MbI0OFY7nMM5ceDAAvadxvayMXskAQnwndVOOeV2w3QSQ75A/ssFeGB/ u672tBN8QbPBaixbElHkDkQneZUlnbL9zxI7YuZEgyMP8eQ2Rpm09A5MAvDg01OE/79z i4e8O4neKwWfZu9W4WQu537DYMnv4RMJTxqN+qluojg3IvKU/4n8Z7GBBWE5GtLKaHVj mZed0VbNsD9R4iRJAirEZH1BQ2lCXSVLW8TI22YnKa94ycslkVfoCRqmDIK5CyoY2U9q g3Ag== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:date:from:to:cc:subject:message-id:references :mime-version:content-disposition:in-reply-to:user-agent; bh=UKuxAL3DCMJXmmRrbGcyjgqA2zBz1ToVUgz751eSS+s=; b=IUedv63R8QzifiOChaMZoUIKZJ23+9hq67hkAKlLcLHeYoiP2oGdcImqu6qkV9qFCH 1Kc3Y2cw/TVtyi0X4tSUeD2uSf47i5APnkirDv3fvwiDBpCYvu0Vt31nRrir1lZu95hK ClMrBGhQNxcW6e8RAkxAzB9qeZC4HJuchRmCrjsCasBJUHREVIFrWYjQ2rrGxXuHMOUE YQHGr6GnnqCVPSlmqsIXdE60VtXvqJP++vFrpHJrovPLV0EQSDWxJW1prOpLkWJJwd+b x+kqlHA0/5wMr9O5hU6MNCQCDqMHKlvHfXH2Gag+eNzX2OBeAzbcePIUc7cKwEsElnF1 LzNw== X-Gm-Message-State: APjAAAU65N4Nm4mCBxW+Z64JEDIivJrQBQlp8xKGXYZkN5WensSAnHIZ bt6wcCo/IsVTS13thG1MDjCVlbOUMC0= X-Google-Smtp-Source: APXvYqz8smkteaPpxyJN+FB8YheCnuZw4EXV9cTZEXLuSPtALGhNbr1AaaQnfu7YWY8QyTbbfb1/Xw== X-Received: by 2002:a02:828c:: with SMTP id t12mr32160567jag.18.1554915560682; Wed, 10 Apr 2019 09:59:20 -0700 (PDT) Received: from pryzbyj (charmander.telsasoft.com. [50.244.222.1]) by smtp.gmail.com with ESMTPSA id o17sm13273518iob.62.2019.04.10.09.59.19 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Wed, 10 Apr 2019 09:59:19 -0700 (PDT) Received: by pryzbyj (Postfix, from userid 1000) id 26012800FD3; Wed, 10 Apr 2019 11:59:18 -0500 (CDT) Date: Wed, 10 Apr 2019 11:59:18 -0500 From: Justin Pryzby To: Daniel Verite Cc: pgsql-hackers@lists.postgresql.org Subject: Re: Cleanup/remove/update references to OID column Message-ID: <20190410165917.GD10080@telsasoft.com> References: <20190408002847.GA904@telsasoft.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="dFWYt1i2NyOo1oI9" Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.5.23 (2014-03-12) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --dFWYt1i2NyOo1oI9 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline On Wed, Apr 10, 2019 at 06:32:35PM +0200, Daniel Verite wrote: > Justin Pryzby wrote: > > > Cleanup/remove/update references to OID column... > > Just spotted a couple of other references that need updates: > #1. In catalogs.sgml: > #2. In ddl.sgml, when describing ctid: I found and included fixes for a few more references: doc/src/sgml/catalogs.sgml | 2 +- doc/src/sgml/ddl.sgml | 3 +-- doc/src/sgml/information_schema.sgml | 4 ++-- doc/src/sgml/ref/create_trigger.sgml | 2 +- doc/src/sgml/spi.sgml | 2 +- Justin --dFWYt1i2NyOo1oI9 Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v2-0001-Cleanup-remove-update-references-to-OID-column.patch" From d77f3d95b8cbb6bbc4addaaf4d9b1bcc11f10f10 Mon Sep 17 00:00:00 2001 From: Justin Pryzby Date: Tue, 2 Apr 2019 19:13:55 -0500 Subject: [PATCH v2] Cleanup/remove/update references to OID column... ..in wake of 578b229718e8f. See also 93507e67c9ca54026019ebec3026de35d30370f9 1464755fc490a9911214817fe83077a3689250ab Reviewed-by: Daniel Verite --- doc/src/sgml/catalogs.sgml | 2 +- doc/src/sgml/ddl.sgml | 12 +++++------- doc/src/sgml/information_schema.sgml | 4 ++-- doc/src/sgml/ref/create_trigger.sgml | 2 +- doc/src/sgml/ref/insert.sgml | 12 +++++------- doc/src/sgml/ref/psql-ref.sgml | 3 +++ doc/src/sgml/spi.sgml | 2 +- 7 files changed, 18 insertions(+), 19 deletions(-) diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 40ddec4..d544e60 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -1047,7 +1047,7 @@ The number of the column. Ordinary columns are numbered from 1 - up. System columns, such as oid, + up. System columns, such as ctid, have (arbitrary) negative numbers. diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 9e761db..244d5ce 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -1202,8 +1202,7 @@ CREATE TABLE circles ( ctid will change if it is updated or moved by VACUUM FULL. Therefore ctid is useless as a long-term row - identifier. The OID, or even better a user-defined serial - number, should be used to identify logical rows. + identifier. A primary key should be used to identify logical rows. @@ -3672,11 +3671,10 @@ VALUES ('Albany', NULL, NULL, 'NY'); Partitions cannot have columns that are not present in the parent. It is not possible to specify columns when creating partitions with - CREATE TABLE, nor is it possible to add columns to - partitions after-the-fact using ALTER TABLE. Tables may be - added as a partition with ALTER TABLE ... ATTACH PARTITION - only if their columns exactly match the parent, including any - oid column. + CREATE TABLE, to add columns to + partitions after-the-fact using ALTER TABLE, nor to + add a partition with ALTER TABLE ... ATTACH PARTITION + if its columns would not exactly match those of the parent. diff --git a/doc/src/sgml/information_schema.sgml b/doc/src/sgml/information_schema.sgml index 1321ade..9c618b1 100644 --- a/doc/src/sgml/information_schema.sgml +++ b/doc/src/sgml/information_schema.sgml @@ -1312,8 +1312,8 @@ The view columns contains information about all table columns (or view columns) in the database. System columns - (oid, etc.) are not included. Only those columns are - shown that the current user has access to (by way of being the + (ctid, etc.) are not included. The only columns shown + are those to which the current user has access (by way of being the owner or having some privilege). diff --git a/doc/src/sgml/ref/create_trigger.sgml b/doc/src/sgml/ref/create_trigger.sgml index 6456105..3339a4b 100644 --- a/doc/src/sgml/ref/create_trigger.sgml +++ b/doc/src/sgml/ref/create_trigger.sgml @@ -465,7 +465,7 @@ UPDATE OF column_name1 [, column_name2NEW row seen by the condition is the current value, as possibly modified by earlier triggers. Also, a BEFORE trigger's WHEN condition is not allowed to examine the - system columns of the NEW row (such as oid), + system columns of the NEW row (such as ctid), because those won't have been set yet. diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgml index 62e142f..3e1be4c 100644 --- a/doc/src/sgml/ref/insert.sgml +++ b/doc/src/sgml/ref/insert.sgml @@ -552,13 +552,11 @@ INSERT INTO table_name [ AS oid count The count is the - number of rows inserted or updated. If count is exactly one, and the - target table has OIDs, then oid is the OID - assigned to the inserted row. The single row must have been - inserted rather than updated. Otherwise oid is zero. + number of rows inserted or updated. + 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/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml index 08f4bab..0e6e792 100644 --- a/doc/src/sgml/ref/psql-ref.sgml +++ b/doc/src/sgml/ref/psql-ref.sgml @@ -3794,6 +3794,9 @@ bar command. This variable is only guaranteed to be valid until after the result of the next SQL command has been displayed. + PostgreSQL servers since version 12 do not + support OID system columns in user tables, and LASTOID will always be 0 + following INSERT. diff --git a/doc/src/sgml/spi.sgml b/doc/src/sgml/spi.sgml index 9b2f516..66eced6 100644 --- a/doc/src/sgml/spi.sgml +++ b/doc/src/sgml/spi.sgml @@ -3105,7 +3105,7 @@ int SPI_fnumber(TupleDesc rowdesc, const char * If colname refers to a system column (e.g., - oid) then the appropriate negative column number will + ctid) then the appropriate negative column number will be returned. The caller should be careful to test the return value for exact equality to SPI_ERROR_NOATTRIBUTE to detect an error; testing the result for less than or equal to 0 is -- 2.1.4 --dFWYt1i2NyOo1oI9--