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 1pxtye-0001be-8U for pgsql-general@arkaria.postgresql.org; Sat, 13 May 2023 18:28:20 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pxtyb-0004l0-Ci for pgsql-general@arkaria.postgresql.org; Sat, 13 May 2023 18:28:17 +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 1pxtyb-0004kq-0l for pgsql-general@lists.postgresql.org; Sat, 13 May 2023 18:28:17 +0000 Received: from mail-yb1-xb35.google.com ([2607:f8b0:4864:20::b35]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1pxtyY-002SS0-7i for pgsql-general@lists.postgresql.org; Sat, 13 May 2023 18:28:16 +0000 Received: by mail-yb1-xb35.google.com with SMTP id 3f1490d57ef6-ba6fa8be5f7so3308194276.0 for ; Sat, 13 May 2023 11:28:14 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1684002492; x=1686594492; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=X2HN6TBZRmmzzvwWdrVmzddfBH6EHluODIElfjs+umI=; b=nP6bIMrohLhfdCUtAUijDBPJYlReAubQIrmgnBMWk/fxe/Kzbs37X6lDswO0/IBSwb BvK3o7ykwZcsL0qq4akreTr4J0JLuvzzycFJziFQ+Po3VKUcQE7Zz6A0KV5augxYsXZ1 /8q58jNC49N3hjddG/Rga4yXwjx7rV7JfY8FofQuhEXBmf25fLQa1gYKi12/D/cGFwHG 4DPP7c3VxJC1mhLiCcPubNHrX3WZGdlwnIVcm74A+eU7rBs98Hoq/ojVmz3i5WXHphEF LgGyIHzWhwi06m3WsrNcth8RceBCDey8SQJaelcPPdDHuv5lRNnAbTg3D5SFw5j5m5sY exng== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1684002492; x=1686594492; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=X2HN6TBZRmmzzvwWdrVmzddfBH6EHluODIElfjs+umI=; b=K8da3tHnre+8sduQ+73XaJQHr3DFxrS9ejpMJ1iciMluORLQKnXHsfayJrYNGjN+Ww bmtoSE8888JJ4ffvz3YWI1egrJ4tP9kA6CKwFZyXhJeMDM22Zs6cJhItxB0AeGWrdR+T j+NC4+C28J/9ZOW8xNe74RwJ/RTwKZV4iaozYqwTb+VZuVHz00CYgNckJNsNFSeqtT9h +z+vZm5+DwaQAChz636iI5PFnj4oU89OxFX5g5/CO7NEhgR6zLsnHY0XcVrtzOMrl+4N P6TPwaH4kUDG09ETuQv1X7iK05+aHNlbWHKGtnx/tnqyp5U9cYZFxIMO6qQGm9lzoYcm f3Lw== X-Gm-Message-State: AC+VfDxTFFhHa2RWgq/eYT/gwkGVPH3bsMQUH+KgK306trCLcmOD9kbA kEq5nwv5l6k26ZyBBsg1CHt/O+oDahA= X-Google-Smtp-Source: ACHHUZ4dmSR87vF0+KuL3UOBru3a9hR1KS4JGNFRJ1LSPg/jOSi468siPa3f0fdoJkknpbdHdvCpIA== X-Received: by 2002:a25:2488:0:b0:b8f:1fd3:56df with SMTP id k130-20020a252488000000b00b8f1fd356dfmr27292334ybk.11.1684002492279; Sat, 13 May 2023 11:28:12 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:517:b6a:fc7c:c06b? ([2600:1700:bd2c:b810:517:b6a:fc7c:c06b]) by smtp.googlemail.com with ESMTPSA id b2-20020a251b02000000b00b7767ca7485sm5953484ybb.34.2023.05.13.11.28.11 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Sat, 13 May 2023 11:28:11 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------uWjFk8NqyAjoYsyU8kcJv0yO" Message-ID: Date: Sat, 13 May 2023 13:28:11 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.10.0 Subject: Re: Adding SHOW CREATE TABLE Content-Language: en-US To: pgsql-general@lists.postgresql.org References: <7a5d6750-84fa-59b0-33c1-7a5d26b9bc74@evolvis.org> <1565715.1683923271@sss.pgh.pa.us> <1aac01a1-d9db-8048-9bfa-1e4f9bb69a03@gmail.com> From: Ron In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------uWjFk8NqyAjoYsyU8kcJv0yO Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 5/13/23 02:25, Kirk Wolak wrote: > On Sat, May 13, 2023 at 1:03 AM Ron wrote: > > On 5/12/23 18:00, Kirk Wolak wrote: > [snip] >> Where do we draw the lines? > > At other tables. > >> Does Table DDL include all indexes? > > Absolutely! > >> It should include constraints, clearly.  I would not think it should >> have triggers. > > Definitely triggers.  And foreign keys. > >> Literally everything within the <>.  (ie, no >> ALTER .. OWNER TO...) >> > > ALTER statements, too.  If CREATE TABLE ... LIKE ... { INCLUDING | > EXCLUDING } { COMMENTS | COMPRESSION | CONSTRAINTS | DEFAULTS | > GENERATED | IDENTITY | INDEXES | STATISTICS | STORAGE | ALL } can do > it, then so should SHOW CREATE TABLE. > > -- > Born in Arizona, moved to Babylonia. > > > I can see the ALTER statements now.  Which is why I asked. > I don't like the idea of including the trigger DDL, because that would > never execute in a clean environment. I would not be grumpy if trigger statements weren't included. > (I've never used a tool that tried to do that when I've wanted the DDL) > I can go either way on index creation. > > Does this imply SQL SYNTAX like: > > SHOW CREATE TABLE >   [ INCLUDING { ALL | INDEXES |  SEQUENCES | ??? }] >   [EXCLUDING { PK | FK | COMMENTS | STORAGE | } ] >   [FOR {V11 | V12 | V13 | V14 | V15 }] ?? > ? "FOR {V...}" is a complication too far, IMO.  No one expects "pg_dump --schema-only" to have a --version= option, so one should not expect SHOW CREATE TABLE to have a "FOR {V...}" clause. > The goal for me  is to open the discussion, and then CONSTRAIN the focus. > > Personally, the simple syntax: > SHOW CREATE TABLE table1; > > Should give me a create table command with the table attributes and the > column attributes, FKs, PKs, Defaults. Etc. > But I would not expect it to generate index commands, etc. -- Born in Arizona, moved to Babylonia. --------------uWjFk8NqyAjoYsyU8kcJv0yO Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit On 5/13/23 02:25, Kirk Wolak wrote:
On Sat, May 13, 2023 at 1:03 AM Ron <ronljohnsonjr@gmail.com> wrote:
On 5/12/23 18:00, Kirk Wolak wrote:
[snip]
Where do we draw the lines?

At other tables.

Does Table DDL include all indexes?

Absolutely!

It should include constraints, clearly.  I would not think it should have triggers.

Definitely triggers.  And foreign keys.

Literally everything within the <<CREATE TABLE X(...);>>.  (ie, no ALTER .. OWNER TO...)


ALTER statements, too.  If CREATE TABLE ... LIKE ... { INCLUDING | EXCLUDING } { COMMENTS | COMPRESSION | CONSTRAINTS | DEFAULTS | GENERATED | IDENTITY | INDEXES | STATISTICS | STORAGE | ALL } can do it, then so should SHOW CREATE TABLE.

--
Born in Arizona, moved to Babylonia.

I can see the ALTER statements now.  Which is why I asked.
I don't like the idea of including the trigger DDL, because that would never execute in a clean environment.

I would not be grumpy if trigger statements weren't included.

(I've never used a tool that tried to do that when I've wanted the DDL)
I can go either way on index creation.

Does this imply SQL SYNTAX like:

SHOW CREATE TABLE <table_name> 
  [ INCLUDING { ALL | INDEXES |  SEQUENCES | ??? }] 
  [EXCLUDING { PK | FK | COMMENTS | STORAGE | } ] 
  [FOR {V11 | V12 | V13 | V14 | V15 }] ??
?

"FOR {V...}" is a complication too far, IMO.  No one expects "pg_dump --schema-only" to have a --version= option, so one should not expect SHOW CREATE TABLE to have a "FOR {V...}" clause.

The goal for me  is to open the discussion, and then CONSTRAIN the focus.

Personally, the simple syntax:
SHOW CREATE TABLE table1;

Should give me a create table command with the table attributes and the column attributes, FKs, PKs, Defaults.  Etc.
But I would not expect it to generate index commands, etc.

--
Born in Arizona, moved to Babylonia.
--------------uWjFk8NqyAjoYsyU8kcJv0yO--