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 1wZ27L-000iZy-2u for pgsql-hackers@arkaria.postgresql.org; Mon, 15 Jun 2026 07:52:24 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1wZ27K-00AWw2-2n for pgsql-hackers@arkaria.postgresql.org; Mon, 15 Jun 2026 07:52:22 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wZ27K-00AWvt-1K for pgsql-hackers@lists.postgresql.org; Mon, 15 Jun 2026 07:52:22 +0000 Received: from mail-ej1-x630.google.com ([2a00:1450:4864:20::630]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1wZ27H-00000000RWj-440M for pgsql-hackers@lists.postgresql.org; Mon, 15 Jun 2026 07:52:20 +0000 Received: by mail-ej1-x630.google.com with SMTP id a640c23a62f3a-bec49f7e35eso370531066b.2 for ; Mon, 15 Jun 2026 00:52:19 -0700 (PDT) ARC-Seal: i=1; a=rsa-sha256; t=1781509938; cv=none; d=google.com; s=arc-20240605; b=HQ2r4hBgi2C9X1xbQ+8+SaOO+dDR+zWdv7bg2CF1clyDCQ8jBz7qgS3gBBMqzir1p9 60HmmQpk+xGfK5jq5pKOMCaaCy9opIkRvQeQvnhqSPUvQxl3UliQxoU6s7a6kY1WM49v EPl/d0ZBy1DwwcZigIfRKZg9Q3AVizNdNyeFTL7ZBp56EI9nQosFErHflqe6fnRL18EC XpXuK48BrvTqNKR5wCW7HTcOcJ8bpKkGoDX5tr1kdZeZg4W8O7yORc+HdzPeuwPJFwrg xrgoFzTmFCvjZq0lo+TOueA/B0hZ3s3TkX21l1xGyAc2YQ9l4TtGJfmfnVY2pqZP99AE 1Xgg== 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:in-reply-to:references :mime-version:dkim-signature; bh=bjVvYEMIB69WetHk5d8375dJM5CBE6Gwn0tQe4Dt5xk=; fh=7JxNg5mK4/Q6Gi7/bIEs9YN7R9KqjblGSQFU3WqQx4Y=; b=S/gJGcdF8SvYyJJd4CqglqwpMb8jrgtmFKwOFmup5vFpiWKMi2KGBpspBie4fpQqk5 eslZVQDwi5tvyL9BTVuu1nvEqmBv/Br3efASPZYW6/mgJSjwJbzwRrXDe+cf9s/yoc32 O5qc1rmVwUnVB51piLYxR9BkrSdCPGNeLHxQMYroM0cnj0/UHH1gvMSHb7s3+myTJ2G3 7KbXv6GmhMlPybEiASGrY+XPbOvmegPw6Bh5eFO2m18X3eFQ1asYK/kiwVoglxWc7ncJ nefCZUJYYeDg3OBzMJMgtCG6M2yl1jeADz1pl7jbJdJQARTt+krqR70hpAIuKoq9bz0E Qk1g==; 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=enterprisedb.com; s=google; t=1781509938; x=1782114738; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=bjVvYEMIB69WetHk5d8375dJM5CBE6Gwn0tQe4Dt5xk=; b=NSNA/trgwPT4TMoH/69P66CDijD3LGismJbI03RSW+ygLDoX6EXhTHeQi6j7oT9+4J S6gvRX2VPvEkPl15f3b42MnhGQAgBEWkWXGDQsymOjqQZMbHXbPMT/aJkWrEzUMM7uQV ihXZFGAjBBIVJE5fTX8EUnoIm8WXKsTpWQ5xiWIMjVAtXpiTdkCbNtu+j6oTPW/JBabJ fqsrDkPvnxL8pWHuGj8Vg2o1j7LM8O+dIJNmqfdTdOyrxgao8Ic+pOh7nKd5ZBFWFRq1 fUD9fvdK8Y982yMZMYTmcpvNfNn3OqVjFRQdc6VHWLVrBqkJkIshTWGd0AV2ClAYcXpJ a3eg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1781509938; x=1782114738; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-gg:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=bjVvYEMIB69WetHk5d8375dJM5CBE6Gwn0tQe4Dt5xk=; b=YexcpRrw+NTKx+P+WE3Q67h/2YYWbveMIjjHU2Cf8cnn9rDwKHstjQ03yx4GUtP7w4 sT9Cvs9ukN3n0z5vnnNZFgOnjz1Vtg/ZJkhs3BNXhIUTdt7KKn+X5eetOOxwSnnk8Jfv I9fMH2h6pg65ois02KkEnrimbUSqg2sStcpbr8ZtQHj/5b6fzvCeoV44UoH2bXOqxNuq DtDEkiw1O/Q/4kdiP22yU63mgzR6OULHSSfIkW0OwiIRACg2GTgLjAg06MNdaaDd+q/Z Ft+JZGfZmW79wBnLmSj8jAkxNwNmJbk17rRJ4qukE2UMvgT+iv0H6ZlYTNrmXNrYmaW5 s9iQ== X-Forwarded-Encrypted: i=1; AFNElJ9Po1xq4UYbB9bxL6XBmKGg1/Sj3nAldz401OdNCK3VrvDnihJS56qBR2P6+WA7+a+hIgKaMy3H23Any7SI@lists.postgresql.org X-Gm-Message-State: AOJu0YxJeFBTl55e1sreIuKOAu46hlxhUBjAZm/A7D+xFCAGZM34CUbv DntsfOK9n0iZ/Je+pAden+IwPfZmjlSV5gFItvm1H/fN1CUtVu/HcZTXiXekWn8rvH5WF6V9wRO /qr5Io1YllMJmVAH5Xf47Cjd/9coP1fK6I6qaUFzE X-Gm-Gg: Acq92OFkJtSXh7/nkCs3FiM+KQNZEq5z2VZeGihAmdzy03hjs8N+qjIBPBhaHGfaz0D e62auTOR2xxqNgvblphKX1WDKneUnPNbCmGvn1T/Bwh/xhdS81Iz5ijeVsQ+X5sCGAOmY7AxtZt HM4/wgQkOQpGo3q0Pe+Cpls5mDgQP9OKHLCJ34oFGdmz9QNAYVGB2CuPEvKrglTqwbNBtTeaaIv 0VokI0UygEz7Ss13gvbta1sXHEp61xuGgnCVNXpPeSF+JIWDeq7mfIX2yxLlIuNiLTDwpW3VPSu gI+2iFJ2 X-Received: by 2002:a17:906:c149:b0:bfe:ed06:5a16 with SMTP id a640c23a62f3a-bff4cddc4c2mr440896566b.52.1781509937696; Mon, 15 Jun 2026 00:52:17 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Akshay Joshi Date: Mon, 15 Jun 2026 13:22:03 +0530 X-Gm-Features: AVVi8CdXdN5Ph-obW8M-v1-lX0vfOwlfppL2M_w4XFqULYkeJ5_7Ri_Ncd10Ox0 Message-ID: Subject: Re: [PATCH] Add pg_get_table_ddl() to reconstruct CREATE TABLE statements To: Marcos Pegoraro Cc: Zsolt Parragi , pgsql-hackers@lists.postgresql.org Content-Type: multipart/alternative; boundary="00000000000000280d0654461b3f" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000000280d0654461b3f Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Thu, Jun 11, 2026 at 6:38=E2=80=AFPM Marcos Pegoraro = wrote: > Em qui., 11 de jun. de 2026 =C3=A0s 04:48, Akshay Joshi < > akshay.joshi@enterprisedb.com> escreveu: > >> Fixed the issue above. The v5 patch is ready for review/testing. >> > > One thing I noticed, though I'm not sure if it's the point here, is that > it's not possible to extract only the foreign keys or only the triggers > from the table. So if we want to extract the objects independently by typ= e, > we would need to have all the return types as optional, and we could have > more granularity in the return types. > > Just like you have... > if (!ctx->include_indexes) > > You could have too > + if (!ctx->include_create_table) > + if (!ctx->include_foreign_keys) > + if (!ctx->include_primary_keys) > > Because only in this way can we more or less execute the dump behavior > here, which is to create all the tables beforehand, then primary keys, th= en > foreign keys, then triggers. > > I repeat, sorry if this is not the function's intended purpose. > I don't think per-contype flags are the right shape, though. The existing toggles group by catalog (indexes, constraints, rules, ...); splitting constraints into PK/FK/CHECK/UNIQUE/EXCLUDE/NOT NULL adds six flags on a second axis, and the function already carries nine. Only FKs have the cross-table dependency-ordering problem; the rest only reference the same table, so splitting them unlocks nothing new. On include_create_table, we are reconstructing the DDL for the table itself, so I don't think we should skip the CREATE TABLE statement. I'd rather always emit CREATE TABLE. > regards > Marcos > > --00000000000000280d0654461b3f Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


On Thu, Jun 11, 2026= at 6:38=E2=80=AFPM Marcos Pegoraro <marcos@f10.com.br> wrote:
Em qui., 1= 1 de jun. de 2026 =C3=A0s 04:48, Akshay Joshi <akshay.joshi@enterprisedb.com= > escreveu:
=C2=A0= Fixed the issue above. The v5 patch is ready for review/testing.

One thing I noticed, though I'm not sure if it's the point he= re, is that it's not possible to extract only the foreign keys or only = the triggers from the table. So if we want to extract the objects independe= ntly by type, we would need to have all the return types as optional, and w= e could have more granularity in the return types.

Just like yo= u have...
if (!ctx->include_indexes)

You could have too
+ if (!ctx->= include_create_table)
+ if (!ctx->include_foreign_keys)
+ if (!ctx->i= nclude_primary_keys)

Because only in this way can we more or less = execute the dump behavior here, which is to create all the tables beforehan= d, then primary keys, then foreign keys, then triggers.=C2=A0
<= div>
I repeat, sorry if this is not the function's intended pur= pose.

I don't t= hink per-contype flags are the right shape, though. The existing toggles gr= oup by catalog (indexes, constraints, rules, ...); splitting constraints in= to PK/FK/CHECK/UNIQUE/EXCLUDE/NOT NULL adds six flags on a second axis, and= the function already carries nine. Only FKs have the cross-table dependenc= y-ordering problem; the rest only reference the same table, so splitting th= em unlocks nothing new.

On=C2=A0include_create_table,=C2=A0we are = reconstructing the DDL for the table itself, so I don't think we should= skip the CREATE TABLE statement. I'd rather always emit CREATE TABLE.<= br>

regards
Marcos

=
--00000000000000280d0654461b3f--