Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ph5dZ-0002XJ-65 for pgsql-general@arkaria.postgresql.org; Tue, 28 Mar 2023 09:29:05 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ph5dX-00012q-H8 for pgsql-general@arkaria.postgresql.org; Tue, 28 Mar 2023 09:29:03 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ph5dW-00012h-W3 for pgsql-general@lists.postgresql.org; Tue, 28 Mar 2023 09:29:03 +0000 Received: from mx0d-001a4c01.pphosted.com ([67.231.151.23]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ph5dT-0003JD-Id for pgsql-general@lists.postgresql.org; Tue, 28 Mar 2023 09:29:01 +0000 Received: from pps.filterd (m0210020.ppops.net [127.0.0.1]) by mx0c-001a4c01.pphosted.com (8.17.1.19/8.17.1.19) with ESMTP id 32S77MsZ027906 for ; Tue, 28 Mar 2023 05:28:57 -0400 Received: from eur04-vi1-obe.outbound.protection.outlook.com (mail-vi1eur04lp2055.outbound.protection.outlook.com [104.47.14.55]) by mx0c-001a4c01.pphosted.com (PPS) with ESMTPS id 3phta9mhxf-1 (version=TLSv1.2 cipher=ECDHE-RSA-AES256-GCM-SHA384 bits=256 verify=NOT) for ; Tue, 28 Mar 2023 05:28:56 -0400 ARC-Seal: i=1; a=rsa-sha256; s=arcselector9901; d=microsoft.com; cv=none; b=oevbDVGum8pYEt4GNYu4ey5dA8eFSc7/jAQmmfWN/9P7JQJRQfkLrK2uc0WL4gknBVFbOXC6Y7u8etO7H2Oys1GU5en4C1psyj6112n3ffcAR9PzKCOvzSnLNW9+faR+P6VXU2DO8wAHt7knCn81oq0BcKbHe7BrWL6ze/z16Jgy/bEzVpb3iKPUsnSyauJPIKJPBQW72sfwgfK5/BgjwpHTplUUDnJfeiP2zoew920kNfPboVrRk97WPPvyavDSFEpXyC6y6PJo0xtBE/UXoPONr66K7ZManFibjhMbnVh3xf4iUUK7Q9aNrTnILNctfXYjr+zAbhRc/ddzj8pJLQ== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com; s=arcselector9901; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-AntiSpam-MessageData-ChunkCount:X-MS-Exchange-AntiSpam-MessageData-0:X-MS-Exchange-AntiSpam-MessageData-1; bh=skwH804yk1ZO+ltTOU+5r8qFL/+Ig/873jfOdBuzs30=; b=jTFy2q5FcqdGjK7sjTDXzj0oGV8JbTotb12OUboRFig2fez+6XX/fzQOkxKA4ep3GQ9dI6aOo0L99snoKhYgrWL3m60UyCmhi+rzZ3jFaHCJog/W0O48CD/z0pi3e7X77TSIlgLSPM+Wy3ajiUJ2G/lJllg2q48nRBayd2tRQyUMo34LiQ8pEe14aMsaGhfRLuabTqzbXszf1bkJuKV+TMGeUqpJS9FqC+9QwRwTAp1UlS83uxedz3nX78J8rqPEUOrYNNFFvpdydvU9Q8wbzpT2cOD2xCDZ+p/oYhZxzHI0IbQT4ESkkRNN2u85pLvxjLl9SYSD6fXEee5ONhSovA== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=pass smtp.mailfrom=4js.com; dmarc=pass action=none header.from=4js.com; dkim=pass header.d=4js.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ourvolaris.onmicrosoft.com; s=selector2-ourvolaris-onmicrosoft-com; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=skwH804yk1ZO+ltTOU+5r8qFL/+Ig/873jfOdBuzs30=; b=rOTgtuizu89WT+qEms+qzOAdjxfrZLyjaFR1IMwVX81qeD9k7Hs1V6L/xvekM3nEFbjLLn1Vy5LiOK20EUw06SvqOTiBgApcLNwuy3O5DUjY3dLLy4+49DF1a1xMolPirhHSsI7CIQbdBGeUG532QRWBEtXt8v1htzGZDyLxvzk= Received: from DBAP191MB1289.EURP191.PROD.OUTLOOK.COM (2603:10a6:10:1c9::19) by AM9P191MB1143.EURP191.PROD.OUTLOOK.COM (2603:10a6:20b:1fd::21) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.6222.29; Tue, 28 Mar 2023 09:28:54 +0000 Received: from DBAP191MB1289.EURP191.PROD.OUTLOOK.COM ([fe80::28e8:7564:6d5c:2579]) by DBAP191MB1289.EURP191.PROD.OUTLOOK.COM ([fe80::28e8:7564:6d5c:2579%6]) with mapi id 15.20.6222.031; Tue, 28 Mar 2023 09:28:53 +0000 From: Sebastien Flaesch To: pgsql-general Subject: Using CTID system column as a "temporary" primary key Thread-Topic: Using CTID system column as a "temporary" primary key Thread-Index: AQHZYVZ01wVqNWW64Uy2W8xqK9Rs7Q== Date: Tue, 28 Mar 2023 09:28:53 +0000 Message-ID: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: msip_labels: x-ms-publictraffictype: Email x-ms-traffictypediagnostic: DBAP191MB1289:EE_|AM9P191MB1143:EE_ x-ms-office365-filtering-correlation-id: 07ecc6ca-a821-44d7-8c28-08db2f6ed902 x-volarisppoutbound: true x-ms-exchange-senderadcheck: 1 x-ms-exchange-antispam-relay: 0 x-microsoft-antispam: BCL:0; x-microsoft-antispam-message-info: pSEM7IMzoED2JQnll6g6KBCjiosegbH0EZ2uiwLFxJ93OJUVoq1TYXJvCqYnWXofSjdPZNZ53Xraj4AeKZrHxrweeb1g8bX+59nEFax/WE//gCCXQ2vU72Y3xtoGTnQ9GrpAdg3Rc79Z5VUVFjWI4IeVQTYamksG0HTVZeFL5j/AoyP517g9GnqFUhtCr1BKvMUJBkBJ7UBontv6EIcjogfAb11XcEqnUlxZtmrXdVykyQvcsC98Wi1ST0bQTdK2PGYfEMLmTC6zL0PwP6IXT+HW/1kNkSE8JkL5zSlkrWuUsdg3+VBSqyrVA5RhwyAnNodkalhpXEylR4E1KdC6Afu6CxZj92edL+LOirWQNRHXlBvAy5H3TBRTgSiwMRCm3DBqLYHve7SWgc4Fs7A5tEiaL+m/M8jhJvQ7m+t+UsNBJ6vZ1UdjApyZi5fmKfMA2ekAEVAYrlqC49wE/+d08JngReCLblwG3nSge0CiYBGEacJPDJUdiacc7E1Mnq1+UvNg0jIjY+8vEMRLlAInQEsyzIhVpsH0XZsX6BsO5tGkgIanp1F1JhjiZmEV7vHnmC6ugwx4g1CistDJJvY95sQSkIhM82WK8/gG8k/erx2sCqT5fmRvmkaRuvvDWuBu x-forefront-antispam-report: CIP:255.255.255.255;CTRY:;LANG:en;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:DBAP191MB1289.EURP191.PROD.OUTLOOK.COM;PTR:;CAT:NONE;SFS:(13230028)(4636009)(39860400002)(346002)(366004)(136003)(396003)(376002)(451199021)(91956017)(83380400001)(41300700001)(52536014)(44832011)(86362001)(19627405001)(33656002)(38100700002)(5660300002)(122000001)(66446008)(8936002)(478600001)(71200400001)(76116006)(7696005)(66946007)(66556008)(64756008)(2906002)(8676002)(6916009)(66476007)(4744005)(55016003)(6506007)(186003)(38070700005)(316002)(9686003)(26005);DIR:OUT;SFP:1102; x-ms-exchange-antispam-messagedata-chunkcount: 1 x-ms-exchange-antispam-messagedata-0: =?iso-8859-1?Q?6pa8k6eZlT3QEvpaayvPM2MN6SIQzUBT+8OwEPyyxenvSwa/6bGPbgrvud?= =?iso-8859-1?Q?+NeCFjmFsDbf2CLkeNIkbcjqk4z7KlwWlCaSsloFFdBBTGeNiQAJ4SPvom?= =?iso-8859-1?Q?EME/J9Y7nvBvWEcEaqxRWCtknuqpXLx9+e6gTbDCydL9qSVcMWN1qCfJBA?= =?iso-8859-1?Q?NUBQAugBr3oJyhXOu3DsthoNVvahI5/5wHmHgN/bYf2F6qUUOCM4u0/mK2?= =?iso-8859-1?Q?V1Fl+cKuVk2Gv/Jd0AkTiohHbTrWROVcEDXIMPoupmFbVvpflSNrAvL3dq?= =?iso-8859-1?Q?Y7kyICMJRFjdtW+9puFJouDWMotdPWmXRXRuPgJUtqsGHZdnq478suK+i+?= =?iso-8859-1?Q?sPFnfgqhtVsycR9FLp3Ju9IHc1ZvIv6SQhJGYM/SPHv0z83pI/m/kIwLSb?= =?iso-8859-1?Q?dnEpB8/inf+menr/wG2AJ1FEFtTf3MPCvwjweyTtjTgEnOPAwMhoGD3c7K?= =?iso-8859-1?Q?iPfyje3H1cp6UtmNEAP5nUblEXieDunGg1Rf8eMJNASOdNVF6NGgXn7896?= =?iso-8859-1?Q?zonaRpOfH1sIf7sjqTgGATCWotjv2V+pndGg44cGzW5SC05mKd8Pyqq2Di?= =?iso-8859-1?Q?0+MaRgoMz0995IWrEs58lP2G4o6LzIdbcgbClC53qK/e37bCqs0Emu6dv7?= =?iso-8859-1?Q?+OrRwqaER50upQxbJSa4noQFPB3ir9QI4vL4/E8Dp4peSuHENxeDTN6paE?= =?iso-8859-1?Q?F0ww0r88Or+uRj9qaXzIrgL0VZF1soR7+Iq0SnPYCDU9JBG2FleWqUzATj?= =?iso-8859-1?Q?ti+GEgJFL5r4Ziq2JqKtavLZFUgbgczys4Kw+I0tfg1Bb8LIgMo44wpI8D?= =?iso-8859-1?Q?sI5Sz1I4pcMsmDPAJn0a4Gs+744dPPmrbHEGRCDItXInJk/4Wk4Ht2or3/?= =?iso-8859-1?Q?9iH7Fgbe5MIPRDUOocuQHThftv53kXgSql4Eu3ccvWdytISxvbAGqrtvvK?= =?iso-8859-1?Q?cFmS6hM22hmwOq0GZWEf/FeHiRXYNey51Iw897lIma3ZX+cFFSEExabi+J?= =?iso-8859-1?Q?vxgWAzsZpfne5vbpmLsYNSm6zvjAfuvy45NSQ4fs0sG5/ZLICO9EC9rtN7?= =?iso-8859-1?Q?pKlI+C1Uu4IkmGHeEqS6GvvDL5hsIQZVgoWsMrYx5UGW4hYfELJq1GnJg5?= =?iso-8859-1?Q?IqKw6xxjzFMtVNZx8/uxwK/OxXuXi6prdxYZOg2Jxzj5HW5H+RIxxkdke1?= =?iso-8859-1?Q?Y6opv3Rmz3oyIgtJVf2mALs7y/ZqDCgzMM1jkChHdDUb3yWxa9i719Wj6i?= =?iso-8859-1?Q?EEhAS08w9kgJ3CnfOyFyNFVqx39RLKoKj8KyGQnlRpu2L/YAjwUnUoZqzS?= =?iso-8859-1?Q?Zg2iDm2XdidN0TG8jxyV2h/ugWOXgbtVDDUzpl1XYle0q5LR7HQxIWYSG/?= =?iso-8859-1?Q?hSPllHCITAN+Yb9waDVUxjxthOk0hQ/W96wW/xNVb9jvRBouDEuJXJO0UP?= =?iso-8859-1?Q?40PRR9LoKeFoVMc6D4pUeEiEB6BdM4klIydU0FN89do0dXxqGw+inUAR4x?= =?iso-8859-1?Q?OQVfAX62ChDMGTkbamHlMfcqqT+myJ4UrU2lA1SuPEcU4ktL1dv9l0ctup?= =?iso-8859-1?Q?TXb3YgkL2e/6vx6DLl2gWC3otPPvgrgtLQvHZB/OPbQ2pbjbDfeiusdQQ6?= =?iso-8859-1?Q?wM495bIjPtTX9vm9aaiQNovLX5teI66+oT?= Content-Type: multipart/alternative; boundary="_000_DBAP191MB12891F47802C3674F77BF751B0889DBAP191MB1289EURP_" MIME-Version: 1.0 X-MS-Exchange-AntiSpam-ExternalHop-MessageData-ChunkCount: 1 X-MS-Exchange-AntiSpam-ExternalHop-MessageData-0: AZm5TcDJfqrv3ZK8Qs0KP4hyw0ICFOwhY2ONoWR5W0VcO9ic3lowJenOfaK+leSKUqasS0/QyQP1WbQ3dU/oTs+m0WIkNOT3M6LpqRxWZ1T3rMHADdOoFmUOUC6wtL4GvHdIlht2vQHoDunIFU9VjHJc3j1rscFuk3jfCpRCQFi9TRyKNmkUsXvmhOHa12kE7levfmWmEVK8fj+Got7JFIfftFCxBFoKjf6cuuCO43PwipRFafIq/R25G6LNjDlxnNVp/x9KCwN6+swMF7d1D9GVJzcu6s3DqQLsFs0g6MBbJjs02fF93eARUOIEI0zPvefHyg2smYWqbDf+mN1KuS9SMcceHu1Wca4cFDnqmVq7pZljOOOh/COnrrErDiYFKrqw9zgRCHF1avLGZS2g6n7UIjbzyRZ0hbTpodlt9yMbf6ApD7iLULd4t75R+RDXX3E89GwDPLIT+mMND0jm6eB4uJm+zBPezm2XSQom6J0n/WS9NHt627y2pXG9OYFHkhwODtHuXRvrMv7nKcXTIbbPxSAMiLY0vNbsR9rYC5zZeotqnkVhVQeYFgxthpRZFOdUeeO6JMm6dK2968Ro3NYhTiMuK2NCXkjexEorqmKzdTeO5TEnlD+FmOiUEqEe4hypwAh9puuwYwS/LMGGaQr/G7uxig2lSjMiTFWny1zYvtdyHvz+3XveeDX137581UIm6tzqULOrZ0BBDIe+vLhKyDJnKxilLidk/RgEZhwqjHy+S8QD1cBHlveyEeKdgPfwywnjL7LFx81WXk8WfgYyhbmfo+eJcmSR5e0JShjHG77UcTLs4UFulawB3Txt X-OriginatorOrg: 4js.com X-MS-Exchange-CrossTenant-AuthAs: Internal X-MS-Exchange-CrossTenant-AuthSource: DBAP191MB1289.EURP191.PROD.OUTLOOK.COM X-MS-Exchange-CrossTenant-Network-Message-Id: 07ecc6ca-a821-44d7-8c28-08db2f6ed902 X-MS-Exchange-CrossTenant-originalarrivaltime: 28 Mar 2023 09:28:53.6630 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: 75c696ec-5bfb-4892-9a0c-9187a9061cd6 X-MS-Exchange-CrossTenant-mailboxtype: HOSTED X-MS-Exchange-CrossTenant-userprincipalname: XWeM4AMqq8HX9EEECx7rpcyu4qRwC6pNoaO5IWdqrORy4wruKMJZs3XB2HTJY0wwI9SMyQv1k2lGCMD68r8xMD66900CbXFysKEEK4xPb6g= X-MS-Exchange-Transport-CrossTenantHeadersStamped: AM9P191MB1143 X-Proofpoint-GUID: F2fyrfu86BqxpbT7YU4uxr7jU6c2Etff X-Proofpoint-ORIG-GUID: F2fyrfu86BqxpbT7YU4uxr7jU6c2Etff X-ProofpointHeader: Yes X-Proofpoint-Spam-Details: rule=outbound_notspam policy=outbound score=0 clxscore=1011 mlxlogscore=880 mlxscore=0 phishscore=0 adultscore=0 impostorscore=0 suspectscore=0 priorityscore=1501 bulkscore=0 spamscore=0 malwarescore=0 lowpriorityscore=0 classifier=spam adjust=0 reason=mlx scancount=1 engine=8.12.0-2303200000 definitions=main-2303280077 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_DBAP191MB12891F47802C3674F77BF751B0889DBAP191MB1289EURP_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello! We are looking for an equivalent of Informix ROWID or Oracle's ROWNUM. Is the CTID a good choice? I assume it must be used in a specific context, and of course not considere= d as permanent primary key. I understand that if the row is updated, the CTID may change. Where can we find details about the validity and lifetime of the value such= column? Will CTID be supported long term or is there any plan to remove it or hide = it some day? Of course, one should use a real primary key definition. However, we have l= egacy code to adapt to PostgreSQL, and in some cases, tables have a composi= te primary key. A first SELECT uses that primary key, but it also fetches t= he ROWID, and will use that one in a subsequent SELECT, UPDATE or DELETE, i= nstead of carrying the composite pkey values. Seb --_000_DBAP191MB12891F47802C3674F77BF751B0889DBAP191MB1289EURP_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hello!

We are looking for an equivalent of Informix ROWID or Oracle's ROWNUM.

Is the CTID a good choice?

I assume it must be used in a specific context, and of course not considere= d as permanent primary key.

I understand that if the row is updated, the CTID may change.

Where can we find details about the validity and lifetime = of the value such column?

Will CTID be supported long term or is there any plan to remove it or hide = it some day?
<= /div>

Of course, one should use a real primary key definition. However, we have l= egacy code to adapt to PostgreSQL, and in some cases, tables have a composi= te primary key. A first SELECT uses that primary key, but it also fetches t= he ROWID, and will use that one in a subsequent SELECT, UPDATE or DELETE, instead of carrying the composit= e pkey values.

Seb

--_000_DBAP191MB12891F47802C3674F77BF751B0889DBAP191MB1289EURP_--