Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wNh6z-000zmQ-1I for pgsql-admin@arkaria.postgresql.org; Fri, 15 May 2026 01:13:09 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1wNh6y-00Fkw1-19 for pgsql-admin@arkaria.postgresql.org; Fri, 15 May 2026 01:13:08 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wNh6y-00Fkvt-02 for pgsql-admin@lists.postgresql.org; Fri, 15 May 2026 01:13:08 +0000 Received: from mail-yx1-xb129.google.com ([2607:f8b0:4864:20::b129]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1wNh6w-00000000ffJ-0cQQ for pgsql-admin@lists.postgresql.org; Fri, 15 May 2026 01:13:07 +0000 Received: by mail-yx1-xb129.google.com with SMTP id 956f58d0204a3-65c0bda7f15so10813375d50.0 for ; Thu, 14 May 2026 18:13:05 -0700 (PDT) ARC-Seal: i=1; a=rsa-sha256; t=1778807584; cv=none; d=google.com; s=arc-20240605; b=hVn3ggQfF9aRLR/WIzSv1Y9PHT8DBMwB6J/uUSo0kvg1ZKlhJz6cyyB09HWA6/qI1D lrt9Ni0Q6lyo2J9bFoO8hsgHm1xwa3yc+h8Nunxgfqzj0goY34QgQRHDT40Ro6RUvHP0 2JZLy/Ile0sbVQ9zSWAtW5XkHSGDr1ruFmIuRlO2X7jHVrniCt+8Lv73yR2/kiADBQF5 Tt4138nVWYrKMsHT95t37uVP3p9yYiAZNGoTwDSJSw6tuMa26S2FRVpxbNrj/W7g1No2 shLKcDwD3j0yJ8ERmC+t6dXXw6POS3m8Q7Mmnl42v7ph1/ZovYRCNgZ6SM/LiUoSjlnc rxBA== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=arc-20240605; h=cc:to:subject:message-id:date:from:references:in-reply-to :mime-version:dkim-signature; bh=M2iUgY+f4QkYjF/GrJfyEpOy2anyVFQn4lpffoYzQS0=; fh=YVqAsyQLaqkSOJ1zvqJdlUnoB6bSkwHNHq4/MX0Vk/s=; b=Q+i734BtmAHycN/k9k1K5vjJbEGl8/jYWp9h0F4tZIsCbCPqL2YXLVab/pmBW/QgyG fZoYNm2EBWAPAuSbhDqhtRVGRyC08PRgJQ82EDsI0lpbXo0TLGYyqjCu3p63e8EEawqR sYQDCFH0rw963kEsYAvPBMCUeWpG6Vl8iKN67T1SRmvWr4DTbuFyFQqJuiuzUmEp2HLg AXPq0D2WTZn873l1hVijbjwNbprWPc1wvMZDFAJigC3OKU2K2KXbqjVoFm+0NOS1nNI0 Z5aZk7qZFegbfCj0QK9/eScUZvyQdr2e7MCWzPa13UNRKr5SN1Tfu4XFk7+6cIqFxDoO /6/Q==; darn=lists.postgresql.org ARC-Authentication-Results: i=1; mx.google.com; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20251104; t=1778807584; x=1779412384; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:references:in-reply-to :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=M2iUgY+f4QkYjF/GrJfyEpOy2anyVFQn4lpffoYzQS0=; b=ErJkByvtmyU7jQ2DKJfnNe2YkO6yFcYZHy7Ph2R00xqVWYnsxIP4rlJg6vyUqVpnBz 2dbp/0am44FTXKg+OY98MyoIYe8kZtpxxkL9mOQ0yFRbj52Dbk50nWO7FNthMfLLLqNp wDEPMz2qnpv6czSqjMoImKQhRwP7nhPktcXeb+Ng4xD6vSszS04/bveu2vou+XQ31TTm /ZyWx1++NEOV0CLazFPO5XskJItLvhuFcxNvs2/qlNsVhlDltrLK5RCqnS0lu7S99jJl Rv/7Hd+p7zYsyFsoXftz9pgo9SmsplZFjuss6QV+2MxzJ2W2yBSZRT4coP/z35kVvlFb jfHw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1778807584; x=1779412384; h=cc:to:subject:message-id:date:from:references:in-reply-to :mime-version:x-gm-gg:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=M2iUgY+f4QkYjF/GrJfyEpOy2anyVFQn4lpffoYzQS0=; b=bGzWEWwrtuas78Jj1BQrkF/ttjo/b5y7sXUwltVoebEjeBruBczsyUVvyMEfzRC1kp BbAKTHOTqA3jCA5e4wKnIMtzBIQ6FmazfMP2UHGZUH1s4T8ILax9Gtu1aIoS9UeesJID m2k1VmN41xe6Y3x8Jq42Ok1nbC5omPp9fO0MIwYPkvm7uRuFTdbuftzx9wjEECscV3RO FljlIn/UvaBqKWaQnc9rBWqvKV8AvlEUauRf2uPT5wS6tzB/z1A0IWH9VaF0Gezmwp4M GMTqDMUc82OjyjiCGUbzW9+ki8qJnrILgjS4183mNlhy9G7vNAz8ZW5DfFT1zVmnF4ZQ UAzA== X-Gm-Message-State: AOJu0YyQnWUqjIKsZs2KtSOWbzGutu4gMz7GlDVHkEJwzNt+6tdoUJFE jgW3bL3jVGltNKzN9u8aJ1S/wYV6IcfBcR/20An+QIBNeqlgRonULNdtwQXFXUhFO1oMkXx1v80 GboL5FGFPIO0nVQQiGHwghNvN5XGWF0g= X-Gm-Gg: Acq92OHYyQPpxUVfSGimYuokEQhZn87bm29MyKUWkt+Zc36zd55PKvcH0U/eSuU3Yz9 gTSVSup+GaZwyfFZ1RsEwF9OwuHJ5YL1uZiDDOMj5CfTG9kMc5KJbNquEQKTTEijydp5sI7+jUX ih2KYj/Y5COiT+zovZQYR2gmL5TUNFvCxLJxGsbh9XQeg687P0mShTeZ+PjGvjsK8DZsHCl+0yg fRfUIMcozVaZ8Kza95MHIEFxSAQ/4nGo7+tpdp7yy6jgJGVkbFrz/T3yuMRlg8QIs+gaQMfJx7j JepJBWPC9CJS45ccjNsk+fn5yIhr X-Received: by 2002:a53:d043:0:10b0:65c:2ac8:ec3d with SMTP id 956f58d0204a3-65e2275d6bamr1559828d50.21.1778807584009; Thu, 14 May 2026 18:13:04 -0700 (PDT) MIME-Version: 1.0 Received: by 2002:a05:7011:84c5:b0:515:94fb:edef with HTTP; Thu, 14 May 2026 18:13:03 -0700 (PDT) In-Reply-To: References: From: "David G. Johnston" Date: Thu, 14 May 2026 18:13:03 -0700 X-Gm-Features: AVHnY4Lvoo3qZwngDyS0j8B8SJjIjJT3fp1Q091co-CF-Dw-PAMIwOGbpzQzkBA Message-ID: Subject: Re: Are these metadata ALTER TABLE commands transactionally safe? To: Ron Johnson Cc: Pgsql-admin Content-Type: multipart/alternative; boundary="0000000000002b32580651d0eaad" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000002b32580651d0eaad Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Thursday, May 14, 2026, Ron Johnson wrote: > On Thu, May 14, 2026 at 5:26=E2=80=AFPM David G. Johnston < > david.g.johnston@gmail.com> wrote: > >> On Thursday, May 14, 2026, Ron Johnson wrote: >> >>> PG 17.9 >>> >>> BEGIN; >>> ALTER TABLE x RENAME TO x_hold; >>> ALTER TABLE y.x SET SCHEMA a; >>> ALTER TABLE x OWNER TO foo; >>> COMMIT; >>> >>> >> Define =E2=80=9Ctransactionally safe=E2=80=9D. >> > > Any open transactions will still see the original tables. > > That would involve locking, most DDL simply won=E2=80=99t happen while open transactions are using the objects. Normal visibility semantics then apply beyond that - which depend on isolation level. David J. --0000000000002b32580651d0eaad Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Thursday, May 14, 2026, Ron Johnson <ronljohnsonjr@gmail.com> wrote:
On Thu, May 14, 2026 at 5:26=E2= =80=AFPM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Thu= rsday, May 14, 2026, Ron Johnson <ronljohnsonjr@gmail.com> wrote:
PG 17.9
=

BEGIN;
ALTER TABLE x RENAME TO x_hold;
ALTER TABLE y.x SET S= CHEMA a;
ALTER TABLE x OWNER TO foo;
COMMIT;


Define =E2=80=9Ctransactionally safe=E2=80=9D.

Any open transactions will still see the original tables.
=C2=A0

That woul= d involve locking, most DDL simply won=E2=80=99t happen while open transact= ions are using the objects.=C2=A0 Normal visibility semantics then apply be= yond that - which depend on isolation level.

David= J.

--0000000000002b32580651d0eaad--