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 1pxdG6-00061G-0P for pgsql-general@arkaria.postgresql.org; Sat, 13 May 2023 00:37:14 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pxdG3-0006Dq-8V for pgsql-general@arkaria.postgresql.org; Sat, 13 May 2023 00:37:11 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pxdG2-0006Dh-Ja for pgsql-general@lists.postgresql.org; Sat, 13 May 2023 00:37:10 +0000 Received: from tamriel.snowman.net ([2001:470:e38f::11]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1pxdFx-0021L2-EL for pgsql-general@lists.postgresql.org; Sat, 13 May 2023 00:37:07 +0000 Received: by tamriel.snowman.net (Postfix, from userid 1000) id AD4E65F7B7; Fri, 12 May 2023 20:36:59 -0400 (EDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=snowman.net; s=dkim; t=1683938219; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:mime-version:mime-version:content-type:content-type: in-reply-to:in-reply-to:references:references; bh=m3nUozg+7IKmT5ZP30CFl4sIOhDPkRLDkZ7Z29r1xHo=; b=UcpLFtry7pa4N8asRMUAO31o/fxozYR08ZSk0xKE4MQKhTmrPJHNOWeUlztKEVsMZwBL7r bTVzuGevCB2zbhNahXYn+kQXqWOcl7QcUKJp4m1XMLRkTJD6tP5+IMqaINJT7m9ZhNHk74 9bBm9tTnYKoNUd4ZKUyAghfDAhgfeC0= Date: Fri, 12 May 2023 20:36:59 -0400 From: Stephen Frost To: Kirk Wolak Cc: Tom Lane , Nathaniel Sabanski , pgsql-general@lists.postgresql.org Subject: Re: Adding SHOW CREATE TABLE Message-ID: References: <7a5d6750-84fa-59b0-33c1-7a5d26b9bc74@evolvis.org> <1565715.1683923271@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="HfV/zQhYryOFJje+" Content-Disposition: inline In-Reply-To: User-Agent: Mutt/2.1.4 (2021-12-11) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --HfV/zQhYryOFJje+ Content-Type: text/plain; charset=utf-8 Content-Disposition: inline Content-Transfer-Encoding: quoted-printable Greetings, * Kirk Wolak (wolakk@gmail.com) wrote: > On Fri, May 12, 2023 at 4:37=E2=80=AFPM Stephen Frost wrote: > > * Tom Lane (tgl@sss.pgh.pa.us) wrote: > > > Stephen Frost writes: > > > > Again, would be great to see someone actually work on this. There's > > > > already a good chunk of code in core in pg_dump and in the postgres= _fdw > > > > for doing exactly this and it'd be great to consolidate that and at= the > > > > same time expose it via SQL. > > ... > > No, it won't make sense to have yet another copy that's for the > > currently-running-server-only, which is why I suggested it go into > > either a common library or maybe into libpq. I don't feel it would > > be bad for the common code to have the multi-version understanding even > > if the currently running backend will only ever have the option to ask > > for the code path that matches its version. > > > Hmmm... What's wrong with only being for the currently running server? > That's all I would expect. Also, if it was there, it limits the > expectations to DDL that > works for that server version. I didn't say anything was wrong with that, merely pointing out that having the same set of code for these various use-cases would be better than having multiple copies of it. The existing code works just fine to answer the question of "when on v15, what is the v15 query?", it just happens to *also* answer "when on v15, what is the v14 query?" and we need that already for postgres_fdw and for pg_dump. > Also, if it's on the backend (or an extension), then it's available to > everything. I mean ... it's already in postgres_fdw, just not in a way that can be returned to the user. I don't think I'd want this functionality to depend on postgres_fdw or generally on an extension though, it should be part of core in some fashion. > > Agreed- someone needs to have a fair bit of time and willingness to push > > on this to make it happen. >=20 > If we can work through a CLEAR discussion of what it is, and is not. I > would be > happy to work on this. I like referencing the FDW. I also thought of > referencing > the CREATE TABLE xyz(LIKE abc INCLUDING ALL). While it's not doing DDL, > it certainly has to be checking options, etc. And pg_dump is the "gold > standard". I'd think the FDW code would be the best starting point, but, sure, look at all the options. > My approach would be to get a version working. Then figure out how to > generate "literally" all table options, and work the process. The good n= ews > is that at a certain point the resulting DDL should be "comparable" again= st > a ton of test tables. >=20 > Where do we draw the lines? Does Table DDL include all indexes? > It should include constraints, clearly. I would not think it should have > triggers. > Literally everything within the <>. (ie, no ALTER = =2E. > OWNER TO...) I'd look at the IMPORT FOREIGN SCHEMA stuff in postgres_fdw. We're already largely answering these questions by what options that takes. To some extent, the same is true of pg_dump, but at least postgres_fdw is already backend code and probably a bit simpler than the pg_dump code. Still, looking at both would be a good idea. > Next, I would want psql \st to simply call this? Eh, that's an independent discussion and effort, especially because people are possibly going to want that to generate the necessary ALTER TABLE commands from the result and not just a DROP/CREATE TABLE. =20 > FWIW, we parse our pg_dump output, and store the objects as individual DDL > files. > So, I have about 1,000 tables to play with, for which I already know the > DDL that pg_dump uses. Sure. > But it's a big commitment. I don't mind if it has a reasonable chance of > being accepted. Yes, it's a large effort, no doubt. > I accept that I will make a few mistakes (and learn) along the way. > If there are ANY deal killers that would prevent a reasonable solution fr= om > being accepted, please let me know. I don't think we can say one way or the other on this ... Thanks, Stephen --HfV/zQhYryOFJje+ Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQIzBAABCgAdFiEEwf6gbxKhD863zrx/7WyKOINHZFUFAmRe26kACgkQ7WyKOINH ZFUvcRAAg/25pPPbisxtFYqgnFRkrIKJDEcKna8hh732Mxq6Nwk+VBvl8UTVt9US i7X35SVq1J6th1te1GnVhyu872oAvzUmY+/XZuFNlm3jmYsr1EHxKb1K4tDdLCcw vGDd53gQwEUyInp/XueT3gvh+NS19dozwWOuYyDMSuYttCjXLkN+E4W3/3TB2UM7 8YUCnhIhtFjitCa1OVLl4duSy3U4scTPgEPhfL62kovIDzh3F8kss+W+lmDLSi/U rBaSx7kxY6uGmUu1nXWCnhehjHAyYHtwL9D8InSCWMrXrSpjFg5ygxOj+gz6Njze 74htWL+9x5Uf2EKn7lrJB+q/gKFbH+tvg4OLtnReQk2cNz8WWBFy6HLsRKUNQaVL /MlobZhB4FK3tp61ViCTZiIIujBq44yfxpPHGtLtjt4adhkWEsJ4oJXTcjt5a8Gk RWYSz+AgC2P7R7bot8ylL7NvrWTrSgDIn27TOrEl1iJhw9fkdVC4bBr/qbYHhEqp M7Gd7kUF4gBXxkUPdLckI2FIsSM3p0kmj6yML8Db0NpX9xHmdC6b98Eu4k03bNxh tHjHyzg831XiLTiunzx7s6suYofH378jLGrNy6SqqMpWHMWSDJccX+eBZfeJTw9d A9OjAY3Z8/Is9iWt2dXPO+iRE3bGjRxC3r294+hR8vu7SD9rNGU= =hiVI -----END PGP SIGNATURE----- --HfV/zQhYryOFJje+--