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 1oT417-0005DK-8e for pgsql-sql@arkaria.postgresql.org; Tue, 30 Aug 2022 16:23:09 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oT414-00070v-UV for pgsql-sql@arkaria.postgresql.org; Tue, 30 Aug 2022 16:23:06 +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 1oT414-00070l-GT for pgsql-sql@lists.postgresql.org; Tue, 30 Aug 2022 16:23:06 +0000 Received: from mx0a-00520701.pphosted.com ([205.220.164.226]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oT40z-0001IB-0s for pgsql-sql@lists.postgresql.org; Tue, 30 Aug 2022 16:23:05 +0000 Received: from pps.filterd (m0282146.ppops.net [127.0.0.1]) by mx0b-00520701.pphosted.com (8.17.1.5/8.17.1.5) with ESMTP id 27UDLcjJ005695 for ; Tue, 30 Aug 2022 12:22:53 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=moodys.com; h=from : to : subject : date : message-id : content-type : mime-version; s=mdydkim1; bh=MYz4SYf3BL3D+kt+0K+avI0k1LJfzBhN7nDCiXEllJg=; b=AOaZdlausH06GDHJVC1z7ehrG6zn6SujuVCQDmXNuu67rnJLrlqwmhGaA2Mv/ZCHskRR Xh8/Ku559N1nP7wUSsp4g73CgEA6UWzYHZx9XDv1LhSiutgQ5aRUre7diNuptYXZOr5E Wd3hJpDZOCAdIL5R6M3jjZALl05H/GX/asgrF5ixbeDTNvdbh+ottdH9bGv3xpEILKA2 1VnNMwhva+4VNMafmgjWwEYJhRwu0IBWFaC1JxmsaHvaFycZJbGCwykpRLSelW0uvwN2 cbrCSTxOTYJMlsghnQCl2MWKq7yk4a6wFBpkX2Zc0LRy5xxDDG/B+cyo3gvkU6goSguv rQ== Received: from nam10-bn7-obe.outbound.protection.outlook.com (mail-bn7nam10lp2102.outbound.protection.outlook.com [104.47.70.102]) by mx0b-00520701.pphosted.com (PPS) with ESMTPS id 3j7fetvtsu-1 (version=TLSv1.2 cipher=ECDHE-RSA-AES256-GCM-SHA384 bits=256 verify=NOT) for ; Tue, 30 Aug 2022 12:22:52 -0400 ARC-Seal: i=1; a=rsa-sha256; s=arcselector9901; d=microsoft.com; cv=none; b=DCQwyIspu+9FhQtjIkaRQGAlSsL6IEv0OLzMHCh+Eed9Lk+pezKj9Yl6UkMEMhyfVcwEtBY9sG/09FYDzlWoutKOc43rUZ6gHjvaTYP0k17T+5DzOV8poqnKvkvN+ki8WsdfA2mxqVdHguOeqeWoVSyJPt77b7cFm/oP3KdfMjuffia42G8GVbh4N3JRNVUOw7dBzrbMuPNhe0B2/aPwSe94aMI+YxoRnCuAgvYAtWAis2ahyXPO66qXwhCQu0kxFTECS6zLpEZnuj2RyTv+JSvR1y0hWM4OY7sisjcdMai8923a58DPINA1Stn7WKcttoleP3A3tbzdtutziFEw4A== 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=SvnGWPr4SMr2EWXw7pC/xKYwMK7R0jUnONTRZiL3+pg=; b=etOeCR/U2xwSrAuvme3kS9gmAgCf5mzGF+bAKvmF1bQckYCb4byXOGVmsFOjSi7u3t/ZSapmlUA8PBJ4IkWwGTWJEkpra2s8pj/8ES6zdHaH66LnhqmsjaETpDgUzhQCJgsDS5apm4ooZrLcKo05208JVmQuK9N9EqLHMxerFxNYpQhbISQXFUdAUgXU0ylXfpnVOeWcH5fo5wZDocgLiTbC4iuo63+ZPy0uF8KsRw/2EpK5aTkp0kTZhg65Bxk8VdqH+zqZ+N/R4r2xfseb4hZsm0Gu6JifGuEkz1UBm3Dp6Yk7VPgg8WVhvvJyomp9ePU+qU1DTul0UM75BalVpQ== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=pass smtp.mailfrom=moodys.com; dmarc=pass action=none header.from=moodys.com; dkim=pass header.d=moodys.com; arc=none Received: from MN2PR20MB2735.namprd20.prod.outlook.com (2603:10b6:208:f6::11) by CY4PR2001MB1735.namprd20.prod.outlook.com (2603:10b6:910:65::22) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.5566.21; Tue, 30 Aug 2022 16:22:46 +0000 Received: from MN2PR20MB2735.namprd20.prod.outlook.com ([fe80::2586:89c7:5f6f:ac92]) by MN2PR20MB2735.namprd20.prod.outlook.com ([fe80::2586:89c7:5f6f:ac92%7]) with mapi id 15.20.5566.021; Tue, 30 Aug 2022 16:22:45 +0000 From: "Voillequin, Jean-Marc" To: "pgsql-sql@lists.postgresql.org" Subject: execution id Thread-Topic: execution id Thread-Index: Adi8iE7utexUdNq+Tz+Qo8WwBsftJA== Date: Tue, 30 Aug 2022 16:22:45 +0000 Message-ID: Accept-Language: fr-FR, en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-ms-publictraffictype: Email x-ms-office365-filtering-correlation-id: 95d03b0a-3094-464e-b5c2-08da8aa3df68 x-ms-traffictypediagnostic: CY4PR2001MB1735:EE_ x-ms-exchange-senderadcheck: 1 x-ms-exchange-antispam-relay: 0 x-microsoft-antispam: BCL:0; x-microsoft-antispam-message-info: fZOJ9jPFXprhiOHUdFYV1/iy3WO8tCycMwU2wZimvfjJKd7uNPdYG/adtjKrXseOixq/x9Xy+tAhzQ2x5mVtvbYm/8GXPR6B68h3tEPDjnFqFKXt7IqoePJj2xFdKCafhUYUBWpOCsSsvynvW/ciXSMaHb0MdjUOvjGENnmznYYVKPBFlTK7zcss6Pi5NhX0ltqsEpIelhdepsQzA1Sp+EtNyXbNALAtM1MhCbjKfAwAROVOVWB0gUBex9h1MHQeAYhLDj4GgE/M35QGqpWzwCnzs77+ozNCB/KHYAteGZVupkBaOkhY6tfi9okJkjngP7k1EF7liIMkiALnukR/JfcD4cjQxC+FzOL85s8wkv6ya1X9VYrXQp4+ard8aCEzls7fHOKM2YDRjQe7a1qGdw/nTUFJjxIailte2UiNa89byrWP4HaZ0hh0ZNWJUouCGGW1IJTS55E5PwQDoyyYrh2LecdmviQTzI6MkBzyYaFaH6RM3OplYfYBTDHAP5xTpgz0gQMXzyhuouPFQCwRQl3LJcawSYcNYimiPaXY4M3XanpSj08DOk/6MfcqhI70OGIYwYw8V4XlW/CFy3pVQE58sQchwDnyRlUY0vZXZA1r0VpSGVY4t2TKgRPTblnZCd9G9Z+Ypfz0D1NeiAwmkF0CWnbDjJWepsQHoZp9OvDiFQArAjufl2e/gxFdLT05qJTRNrVOX24G58jD6jpdNprXGIorSxbeSkvpLWY3T5nEsP0a5O09h+z12wdTpXeS4rrF7pFmYb4it8nQe0SqmQ== x-forefront-antispam-report: CIP:255.255.255.255;CTRY:;LANG:en;SCL:1;SRV:;IPV:NLI;SFV:NSPM;H:MN2PR20MB2735.namprd20.prod.outlook.com;PTR:;CAT:NONE;SFS:(13230016)(4636009)(396003)(376002)(136003)(39860400002)(346002)(366004)(2906002)(26005)(9686003)(38100700002)(4744005)(122000001)(6506007)(7696005)(55016003)(83380400001)(33656002)(186003)(3480700007)(66556008)(6916009)(66446008)(64756008)(66476007)(66946007)(316002)(8676002)(76116006)(52536014)(38070700005)(41300700001)(8936002)(86362001)(5660300002)(71200400001)(7116003)(478600001);DIR:OUT;SFP:1101; x-ms-exchange-antispam-messagedata-chunkcount: 1 x-ms-exchange-antispam-messagedata-0: =?us-ascii?Q?QCE8yr2v9a1LTFx+pnjt+Zn0SR/UAAHpWWITVnuHK714uId1ZmWH9SElECA6?= =?us-ascii?Q?Bp0/zwLy0XWtiCH7D2gy2GTBABenHVeKuNNiASlAWQoTGRNzlA0C410Odmoh?= =?us-ascii?Q?1nl/dJLKql1z5JdzU1yNeV7Wt1SflPg+RXY941oDmwnWwRiZtZcv9ZebGZC7?= =?us-ascii?Q?ffvCj32ocik+xehO8nR1xxM4IacRi+T5Wwj7asFCUwsfMkBZwk1AMMYTUHiN?= =?us-ascii?Q?uVuHFrouCZqs+B3SNQ/dHhPq4OgLmqbX8Rwp3l8QD4QdYsl7GuNgt4T8gcwg?= =?us-ascii?Q?HyfQ6tjQJAAt4QsQSDY/GOqSBcI7cq9svlkD+g/X7F9wR+mepSBQrR97WOms?= =?us-ascii?Q?OXweQ+euTHk+TrbYCadz8fJrhnqqO8b/38pfZtN898m8qhAfENJwklbhWdBC?= =?us-ascii?Q?JVrcmuhp0D6/3J/4//B5qFu/PrVbHY6mIF9M/n8H2wL+GvMh5qy/tvjNqvHH?= =?us-ascii?Q?dRXnnHXSNL4wkTAoHnrwlAuq8A5OzlJ6fsdtJb1Fup1sDIJWPn00NQ7nSCso?= =?us-ascii?Q?7IRESmg2JiG6ZLNgnqXe8yH1E7FV0UzKgs2syDTCqRTc7nUMC514yD4zNmiP?= =?us-ascii?Q?CClZUbtvUAWJPg/Au3FjeKsXSK49KsR5wzL57VRbqzYQ5bOASd+RjfFInsm2?= =?us-ascii?Q?xBJApRiSHNmg3Vgg8NMAJdZnOn8NNEroYFrJiCqhaGG5S9n47Q8eTAl9QHbW?= =?us-ascii?Q?dghRqv5zjrZcRV+yD2EjT6rybNgrxYpCXUoHry6Uxr9bkDIF3MjUd3Q+guyA?= =?us-ascii?Q?YJkegIiojOkthXC2J7KV8kRMqe5CLOR+mt78hctxOfj36zl/1ySRCC+5WPl+?= =?us-ascii?Q?njfqmFmg0b1vM2OERExCcItgfOr5lVC+tLocFozXTow6+i9u201omhB5zsOa?= =?us-ascii?Q?5GpzLQ5wkKXaUBy69tjNIZllcw0bZY0N0BWYO1WI465soGlLkLHrrU+/uueI?= =?us-ascii?Q?EU7N3AjUh8nSWKTiT/VYmFZ/KbBw/celOAHL5dKb5Pn+wTpJFyOgaZp10Kdw?= =?us-ascii?Q?W0O1haLGggKsXsB61wnPWuqd2aSlh66RcRrfmlo1zd9HO2zMnZqYpk50DGdP?= =?us-ascii?Q?osqKt/hqtOrL3h3IvzwaxazNV81AqzpQFnod5ImFGbn7SnqWOScwehbmXD8q?= =?us-ascii?Q?fmqnJJR1wmhM/vKm/g6B3sfAUCitugk7jpkdaSvLJwQbYY03vjBsxwM49Pud?= =?us-ascii?Q?++F3TT3k6Ar2M+FsG8DmmJyFOdq+hA36ECNW0Y7NlRSBgxpZ9z4nZeNqCawP?= =?us-ascii?Q?kGaikxnEkOM0ELUAlSiRVNUpbsiejaHK58hy0XPn63pZpbJUwXO/PlaGwwS4?= =?us-ascii?Q?ThokSpp58CVHdcOJqczzLpYeghALlzK14jHclb2BJ7MakH/2PwwmgFODKg7K?= =?us-ascii?Q?scZSRMqmdlOWIHnxtVkFSUhTVl7C3MqQbx4sVNBIDhrZIIkMBqOB3Gn/VujN?= =?us-ascii?Q?KhKDqJGwEEQwQf8lL4NKeG4yr6SMuWjakkXgjC9xCZ/RwTgkTtuW/AKjRuOC?= =?us-ascii?Q?03LoxncyIyYOI2XLWRxEzOv7OQgIDhiNG+4CjcNAEXTU1C6lAopYiWnwXWqX?= =?us-ascii?Q?fEVJkLlDZhl/bCgHqTFqVWRKCKi3WRXtg9VWnA+U?= Content-Type: multipart/alternative; boundary="_000_MN2PR20MB27356F937609C465A9FD9B2EBE799MN2PR20MB2735namp_" MIME-Version: 1.0 X-OriginatorOrg: moodys.com X-MS-Exchange-CrossTenant-AuthAs: Internal X-MS-Exchange-CrossTenant-AuthSource: MN2PR20MB2735.namprd20.prod.outlook.com X-MS-Exchange-CrossTenant-Network-Message-Id: 95d03b0a-3094-464e-b5c2-08da8aa3df68 X-MS-Exchange-CrossTenant-originalarrivaltime: 30 Aug 2022 16:22:45.8674 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: 1061a8b8-b1ee-4249-bb84-9a2cd2792fae X-MS-Exchange-CrossTenant-mailboxtype: HOSTED X-MS-Exchange-CrossTenant-userprincipalname: HPkwhRkaWdiv6ibrhGbLs86WMG6xribfZDiGe+7BK7CKAgrCQP4AjC4WqIDuRN09xUV2RQdhZlXRpy1EijhNJA== X-MS-Exchange-Transport-CrossTenantHeadersStamped: CY4PR2001MB1735 X-Proofpoint-GUID: HBgH0YrVNOZz_MgjqXFwiTmHC4YvXtZh X-Proofpoint-ORIG-GUID: HBgH0YrVNOZz_MgjqXFwiTmHC4YvXtZh X-Proofpoint-Virus-Version: vendor=baseguard engine=ICAP:2.0.205,Aquarius:18.0.895,Hydra:6.0.517,FMLib:17.11.122.1 definitions=2022-08-30_10,2022-08-30_01,2022-06-22_01 X-Proofpoint-Spam-Details: rule=outbound_notspam policy=outbound score=0 impostorscore=0 suspectscore=0 clxscore=1011 priorityscore=1501 malwarescore=0 mlxscore=0 phishscore=0 bulkscore=0 lowpriorityscore=0 spamscore=0 adultscore=0 mlxlogscore=549 classifier=spam adjust=0 reason=mlx scancount=1 engine=8.12.0-2207270000 definitions=main-2208300077 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_MN2PR20MB27356F937609C465A9FD9B2EBE799MN2PR20MB2735namp_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Hello guys, Is there a kind of magical system administration function that returns a un= ique id each time a select/insert/update/delete statement is launched? Assuming x is a table with several rows: select pg_magical_exec_id() from x; * returns 1 for each row insert into x select pg_magical_exec_id() from x; * pg_magical_exec_id()=3D2 Select 1 from x where pg_magical_exec_id()>0; * pg_magical_exec_id=3D3 Select pg_magical_exec_id() from x; * returns 4 for each row And even, in a transaction: Begin; delete from x where ...; * pg_magical_exec_id=3D5 update x set ... ; * pg_magical_exec_id=3D6 End; Select pg_magical_exec_id() from x; * returns 7 for each row I can understand it is a strange question! There is a txid_current() function that could fit, but the transaction id d= oes not change within a transaction. ---------------------------------------------------------------------- Moody's monitors email communications through its networks for regulatory c= ompliance purposes and to protect its customers, employees and business and= where allowed to do so by applicable law. The information contained in thi= s e-mail message, and any attachment thereto, is confidential and may not b= e disclosed without our express permission. If you are not the intended rec= ipient or an employee or agent responsible for delivering this message to t= he intended recipient, you are hereby notified that you have received this = message in error and that any review, dissemination, distribution or copyin= g of this message, or any attachment thereto, in whole or in part, is stric= tly prohibited. If you have received this message in error, please immediat= ely notify us by telephone, fax or e-mail and delete the message and all of= its attachments. Every effort is made to keep our network free from viruse= s. You should, however, review this e-mail message, as well as any attachme= nt thereto, for viruses. We take no responsibility and have no liability fo= r any computer virus which may be transferred via this e-mail message. --_000_MN2PR20MB27356F937609C465A9FD9B2EBE799MN2PR20MB2735namp_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hello guys,

 

Is there a kind of magical system administration fun= ction that returns a unique id each time a select/insert/update/delete stat= ement is launched?

 

Assuming x is a table with several rows:<= /p>

 

select pg_magical_exec_id() from x;

  • returns 1 for each row

 

insert into x select pg_magical_exec_id() from x;

  • pg_magical_exec_id()=3D2

 

Select 1 from x where pg_magical_exec_id()>0;

  • pg_magical_exec_id=3D3

 

Select pg_magical_exec_id() from x;

  • returns 4 for each row

 

And even, in a transaction:

 

Begin;

delete from x where …;

  • pg_magical_exec_id=3D5

update x set … ;

  • pg_magical_exec_id=3D6

End;

 

Select pg_magical_exec_id() from x;

  • returns 7 for each row

 

I can understand it is a strange question!

There is a txid_current() function that could fit, b= ut the transaction id does not change within a transaction.

 

 

 

Moody's monitors email communications through its networks for regu= latory compliance purposes and to protect its customers, employees and busi= ness and where allowed to do so by applicable law. The information containe= d in this e-mail message, and any attachment thereto, is confidential and m= ay not be disclosed without our express permission. If you are not the inte= nded recipient or an employee or agent responsible for delivering this mess= age to the intended recipient, you are hereby notified that you have receiv= ed this message in error and that any review, dissemination, distribution o= r copying of this message, or any attachment thereto, in whole or in part, = is strictly prohibited. If you have received this message in error, please = immediately notify us by telephone, fax or e-mail and delete the message an= d all of its attachments. Every effort is made to keep our network free fro= m viruses. You should, however, review this e-mail message, as well as any = attachment thereto, for viruses. We take no responsibility and have no liab= ility for any computer virus which may be transferred via this e-mail messa= ge.
--_000_MN2PR20MB27356F937609C465A9FD9B2EBE799MN2PR20MB2735namp_--