Re: Adding SHOW CREATE TABLE
On Sun, May 14, 2023 at 2:20 AM Kirk Wolak wrote: > On Sat, May 13, 2023 at 3:34 PM Jeremy Smith > wrote: > >> >> >> On Sat, May 13, 2023, 3:25 AM Kirk Wolak wrote: >> >>> Does this imply SQL SYNTAX like: >>> >>> SHOW CREATE TABLE >>> [ INCLUDING { ALL | INDEXES | SEQUENCES | ??? }] >>> [EXCLUDING { PK | FK | COMMENTS | STORAGE | } ] >>> [FOR {V11 | V12 | V13 | V14 | V15 }] ?? >>> ? >>> >> >> Personally, I would expect a function, like pg_get_tabledef(oid), to >> match the other pg_get_*def functions instead of overloading SHOW. To me, >> this also argues that we shouldn't include indexes because we already have >> a pg_get_indexdef function. >> >> -Jeremy >> > +1 > > In fact, making it a function will make my life easier for testing, that's > for certain. I don't need to involve the parser,etc. Others can help with > that after the function works. > Thanks for the suggestion! > I am moving this over to the Hackers Group. My approach for now is to develop this as the \st command. After reviewing the code/output from the 3 sources (psql, fdw, and pg_dump). This trivializes the approach, and requires the smallest set of changes (psql is already close with existing queries, etc). And frankly, I would rather have an \st feature that handles 99% of the use cases then go 15yrs waiting for a perfect solution. Once this works well for the group. Then, IMO, that would be the time to discuss moving it. Support Or Objections Appreciated. Kirk...
Re: Adding SHOW CREATE TABLE
On Fri, May 12, 2023 at 8:37 PM Stephen Frost wrote: > Greetings, > ... > Yes, it's a large effort, no doubt. > > Stephen, I started looking at the code. And I have the queries from \set SHOW_HIDDEN that psql uses. And also the pg_dump output. My first table was an ID bigint NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY pg_dump puts the decorations on the SEQUENCE \dt puts that text as the "Default" value But the STRANGE part for me is the query I Assembled from the FDW returns nothing for extra attributes. And only seems to care about the "GENERATED AS (%s) STORED" syntax. For me, generating the INLINE syntax will produce the SEQUENCE automatically, so this is my preference. Let me know if I am missing anything... Please. Finally, I cannot GRASP this additional syntax: appendStringInfo(&buf, "\n) SERVER %s\nOPTIONS (", This is at the end of the create table syntax: CREATE TABLE %s ( ... ) SERVER %s\n OPTIONS (" ..."); Is this special "FDW" Decorations because I don't see those on the create table documentation? It's easy enough to ignore, but I don't want to miss something. Kirk...
Re: Adding SHOW CREATE TABLE
On Sat, May 13, 2023 at 3:34 PM Jeremy Smith wrote: > > > On Sat, May 13, 2023, 3:25 AM Kirk Wolak wrote: > >> Does this imply SQL SYNTAX like: >> >> SHOW CREATE TABLE >> [ INCLUDING { ALL | INDEXES | SEQUENCES | ??? }] >> [EXCLUDING { PK | FK | COMMENTS | STORAGE | } ] >> [FOR {V11 | V12 | V13 | V14 | V15 }] ?? >> ? >> > > Personally, I would expect a function, like pg_get_tabledef(oid), to match > the other pg_get_*def functions instead of overloading SHOW. To me, this > also argues that we shouldn't include indexes because we already have a > pg_get_indexdef function. > > -Jeremy > +1 In fact, making it a function will make my life easier for testing, that's for certain. I don't need to involve the parser,etc. Others can help with that after the function works. Thanks for the suggestion!
Re: Adding SHOW CREATE TABLE
On Sat, May 13, 2023, 3:25 AM Kirk Wolak wrote: > Does this imply SQL SYNTAX like: > > SHOW CREATE TABLE > [ INCLUDING { ALL | INDEXES | SEQUENCES | ??? }] > [EXCLUDING { PK | FK | COMMENTS | STORAGE | } ] > [FOR {V11 | V12 | V13 | V14 | V15 }] ?? > ? > Personally, I would expect a function, like pg_get_tabledef(oid), to match the other pg_get_*def functions instead of overloading SHOW. To me, this also argues that we shouldn't include indexes because we already have a pg_get_indexdef function. -Jeremy > >
Re: Adding SHOW CREATE TABLE
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.
Re: Adding SHOW CREATE TABLE
On Fri, May 12, 2023 at 8:37 PM Stephen Frost wrote: > Greetings, > .. > 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. > I will start with postgres_fdw then, but gladly review the other source... Just thinking about the essence of the syntax. SHOW CREATE TABLE abc(LIKE real_table); -- Output CREATE TABLE abc(); using real_table? I'd look at the IMPORT FOREIGN SCHEMA stuff in postgres_fdw. We're > already largely answering these questions by what options that takes. > Will do. > > 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. > At least there is a base of code to start with. I see a strong need to come up with a shell script to that could: FOR DO psql -c "SHOW... \g | cat > " pg_dump -- only | remove_comments_normalize | cat DIFF Of course, since our tests are usually all perl, a perl version. But I would clearly want some heavy testing/validation.
Re: Adding SHOW CREATE TABLE
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'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 }] ?? ? 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.
Re: Adding SHOW CREATE TABLE
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.
Re: Adding SHOW CREATE TABLE
Greetings, * Kirk Wolak (wol...@gmail.com) wrote: > On Fri, May 12, 2023 at 4:37 PM Stephen Frost wrote: > > * Tom Lane (t...@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. > > 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 news > is that at a certain point the resulting DDL should be "comparable" against > a ton of test tables. > > 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 .. > 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. > 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 from > being accepted, please let me know. I don't think we can say one way or the other on this ... Thanks, Stephen signature.asc Description: PGP signature
Re: Adding SHOW CREATE TABLE
On Fri, May 12, 2023 at 4:37 PM Stephen Frost wrote: > Greetings, > > * Tom Lane (t...@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. Also, if it's on the backend (or an extension), then it's available to everything. > Agreed- someone needs to have a fair bit of time and willingness to push > on this to make it happen. > 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". 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 news is that at a certain point the resulting DDL should be "comparable" against a ton of test tables. 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 .. OWNER TO...) Next, I would want psql \st to simply call this? 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. But it's a big commitment. I don't mind if it has a reasonable chance of being accepted. 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 from being accepted, please let me know. Kirk...
Re: Adding SHOW CREATE TABLE
Greetings, * Tom Lane (t...@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. > > Note that this is hardly new ground: we've heard more-or-less the same > proposal many times before. I think the reason it's gone nowhere is > that most of the existing infrastructure is either in pg_dump or designed > to support pg_dump, and pg_dump is *extremely* opinionated about what > it wants and how it wants the data sliced up, for very good reasons. > Reconciling those requirements with a typical user's "just give me a > reconstructed CREATE TABLE command" request seems fairly difficult. Yet we're already duplicating much of this in postgres_fdw. If we don't want to get involved in pg_dump's feelings on the subject, we could look to postgres_fdw's independent implementation which might be more in-line with what users are expecting. Having two separate copies of code that does this and continuing to refuse to give users a way to ask for it themselves seems at the least like an odd choice. > Also, since pg_dump will still need to support old servers, it's hard > to believe we'd accept any proposal to move that functionality into > the server side, which in turn means that it's not going to be an easy > SQL command. 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. > These issues probably could be surmounted with enough hard work, but > please understand that just coming along with a request is not going > to cause it to happen. People have already done that. (Searching > the mailing list archives might be edifying.) Agreed- someone needs to have a fair bit of time and willingness to push on this to make it happen. Thanks, Stephen signature.asc Description: PGP signature
Re: Adding SHOW CREATE TABLE
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. Note that this is hardly new ground: we've heard more-or-less the same proposal many times before. I think the reason it's gone nowhere is that most of the existing infrastructure is either in pg_dump or designed to support pg_dump, and pg_dump is *extremely* opinionated about what it wants and how it wants the data sliced up, for very good reasons. Reconciling those requirements with a typical user's "just give me a reconstructed CREATE TABLE command" request seems fairly difficult. Also, since pg_dump will still need to support old servers, it's hard to believe we'd accept any proposal to move that functionality into the server side, which in turn means that it's not going to be an easy SQL command. These issues probably could be surmounted with enough hard work, but please understand that just coming along with a request is not going to cause it to happen. People have already done that. (Searching the mailing list archives might be edifying.) regards, tom lane
Re: Adding SHOW CREATE TABLE
Greetings, * Nathaniel Sabanski (sabansk...@gmail.com) wrote: > > Perhaps a bit more discussion about what exactly the use-case is would > > be helpful- what would you use this feature for? > > App writers: To facilitate table creation and simplify schema verification, > without relying on a GUI tool or ORM (or system calls out to pg_dump). Not sure how it would simplify schema verification? > Tool writers: Would drastically cut down the implementation time and > complexity to support Postgres. I am one of the devs of Piccolo ORM (Python > lib supporting Postgres) and we have a lot of code dedicated to > re-generating the CREATE TABLE statements (creation, during migrations, > etc) that could be done better by Postgres itself. I'm curious- have you compared what you're doing to pg_dump's output? Are you confident that there aren't any distinctions between those that, for whatever reason, need to exist? > Moving to Postgres: It would help ease migrations for developers wanting to > move from MySQL / Percona / MariaDB to Postgres. Also it's a nice developer > experience to see how Postgres generates X table without extra tooling. Seems unlikely that this would actually be all that helpful there- tools like ora2pg and similar know how to query other database systems and write appropriate CREATE TABLE statements for PostgreSQL. > The intention of SHOW CREATE TABLE is not to replace the existing suite of > \d in psql but rather to be a developer friendly complement within SQL > itself. Sure, I get that. 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. Another possible option would be to add this to libpq, which is used by postgres_fdw, psql, pg_dump, and lots of other drivers and client utilities. If it's all broadly the same, personally I'd prefer it to be in the common library and available as a backend SQL command too, but perhaps there's reasons that it would be easier to implement in libpq instead. Thanks, Stephen signature.asc Description: PGP signature
Re: Adding SHOW CREATE TABLE
> Perhaps a bit more discussion about what exactly the use-case is would > be helpful- what would you use this feature for? App writers: To facilitate table creation and simplify schema verification, without relying on a GUI tool or ORM (or system calls out to pg_dump). Tool writers: Would drastically cut down the implementation time and complexity to support Postgres. I am one of the devs of Piccolo ORM (Python lib supporting Postgres) and we have a lot of code dedicated to re-generating the CREATE TABLE statements (creation, during migrations, etc) that could be done better by Postgres itself. Ecosystem cohesion: SHOW CREATE TABLE has already been implemented in CockroachDB, a popular Postgres derivative. Moving to Postgres: It would help ease migrations for developers wanting to move from MySQL / Percona / MariaDB to Postgres. Also it's a nice developer experience to see how Postgres generates X table without extra tooling. The intention of SHOW CREATE TABLE is not to replace the existing suite of \d in psql but rather to be a developer friendly complement within SQL itself.
Re: Adding SHOW CREATE TABLE
On Fri, 12 May 2023 at 09:12, David G. Johnston wrote: > > > On Fri, May 12, 2023, 08:35 Thorsten Glaser wrote: > >> On Fri, 12 May 2023, Nathaniel Sabanski wrote: >> >> >I believe most users would anticipate a CREATE TABLE statement that >> aligns >> >with the currently installed version- this is the practical solution for >> >> The currently installed version of what, the server or the client? >> > > It's an SQL Command, no specific client can/should be presumed. > > David J. > >>
Re: Adding SHOW CREATE TABLE
On Fri, May 12, 2023, 08:35 Thorsten Glaser wrote: > On Fri, 12 May 2023, Nathaniel Sabanski wrote: > > >I believe most users would anticipate a CREATE TABLE statement that aligns > >with the currently installed version- this is the practical solution for > > The currently installed version of what, the server or the client? > It's an SQL Command, no specific client can/should be presumed. David J. >
Re: Adding SHOW CREATE TABLE
Nathaniel Sabanski schrieb am 12.05.2023 um 13:29: HN had a thread regarding the challenges faced by new users during the adoption of Postgres in 2023. One particular issue that garnered significant votes was the lack of a "SHOW CREATE TABLE" command, and seems like it would be an easy one to implement: https://news.ycombinator.com/item?id=35908991 Considering the popularity of this request and its potential ease of implementation, I wanted to bring it to your attention, as it would likely enhance the user experience and alleviate some of the difficulties encountered by newcomers. While it would be nice to have something like that, I don't think it isn't really necessary. Pretty much every (GUI) SQL client provides a way to see the DDL for objects in the database. For psql fans \d typically is enough, and they would probably not mind running pg_dump to get the full DDL. I would think that especially newcomers start with a GUI client that can do this. If you check the source of any of the popular SQL clients that generates the DDL for a table, then you will also quickly realize that this isn't a trivial thing to do. Thomas
Re: Adding SHOW CREATE TABLE
On Fri, 12 May 2023, Nathaniel Sabanski wrote: >I believe most users would anticipate a CREATE TABLE statement that aligns >with the currently installed version- this is the practical solution for The currently installed version of what, the server or the client? bye, //mirabilos -- 15:41⎜ Somebody write a testsuite for helloworld :-)
Re: Adding SHOW CREATE TABLE
Greetings, Please don't top-post on these lists. * Nathaniel Sabanski (sabansk...@gmail.com) wrote: > I believe most users would anticipate a CREATE TABLE statement that aligns > with the currently installed version- this is the practical solution for > the vast majority. Perhaps a bit more discussion about what exactly the use-case is would be helpful- what would you use this feature for? > In situations where a CREATE TABLE statement compatible with an older > version of Postgres is required, users can opt for an additional step of > using tools like pg_dump or an older version of Postgres itself. This > allows them to ensure compatibility without compromising the practicality > of the process. The issue is really both older and newer versions, not just older ones and not just newer ones. To the extent you're interested in this, I pointed out where you could go look at the existing code as well as an idea for how to move this forward. Thanks, Stephen signature.asc Description: PGP signature
Re: Adding SHOW CREATE TABLE
I believe most users would anticipate a CREATE TABLE statement that aligns with the currently installed version- this is the practical solution for the vast majority. In situations where a CREATE TABLE statement compatible with an older version of Postgres is required, users can opt for an additional step of using tools like pg_dump or an older version of Postgres itself. This allows them to ensure compatibility without compromising the practicality of the process. On Fri, 12 May 2023 at 06:47, Stephen Frost wrote: > Greetings, > > * Nathaniel Sabanski (sabansk...@gmail.com) wrote: > > HN had a thread regarding the challenges faced by new users during the > > adoption of Postgres in 2023. > > > > One particular issue that garnered significant votes was the lack of a > > "SHOW CREATE TABLE" command, and seems like it would be an easy one to > > implement: https://news.ycombinator.com/item?id=35908991 > > > > Considering the popularity of this request and its potential ease of > > implementation, I wanted to bring it to your attention, as it would > likely > > enhance the user experience and alleviate some of the difficulties > > encountered by newcomers. > > This isn't as easy as it seems actually ... > > Note that using pg_dump for this purpose works quite well and also works > to address cross-version issues. Consider that pg_dump v15 is able to > connect to v14, v13, v12, v11, and more, and produce a CREATE TABLE > command that will work with *v15*. If you connected to a v14 database > and did a SHOW CREATE TABLE, there's no guarantee that the CREATE TABLE > statement returned would work for PG v15 due to keyword changes and > other differences that can cause issues between major versions of PG. > > Now, that said, we have started ending up with some similar code between > pg_dump and postgres_fdw in the form of IMPORT FOREIGN SCHEMA and maybe > we should consider if that code could be moved into the common library > and made available to pg_dump, postgres_fdw, and as a SHOW CREATE TABLE > command with the caveat that the produced CREATE TABLE command may not > work with newer versions of PG. There's an interesting question around > if we'd consider it a bug worthy of fixing if IMPORT FOREIGN SCHEMA in > v14 doesn't work when connecting to a v15 PG instance. Not sure if > anyone's contemplated that. There's certainly going to be cases that we > wouldn't accept fixing (we wouldn't add some new partitioning strategy > to v14 just because it's in v15, for example, to make IMPORT FOREIGN > SCHEMA work...). > > Thanks, > > Stephen >
Re: Adding SHOW CREATE TABLE
Greetings, * Nathaniel Sabanski (sabansk...@gmail.com) wrote: > HN had a thread regarding the challenges faced by new users during the > adoption of Postgres in 2023. > > One particular issue that garnered significant votes was the lack of a > "SHOW CREATE TABLE" command, and seems like it would be an easy one to > implement: https://news.ycombinator.com/item?id=35908991 > > Considering the popularity of this request and its potential ease of > implementation, I wanted to bring it to your attention, as it would likely > enhance the user experience and alleviate some of the difficulties > encountered by newcomers. This isn't as easy as it seems actually ... Note that using pg_dump for this purpose works quite well and also works to address cross-version issues. Consider that pg_dump v15 is able to connect to v14, v13, v12, v11, and more, and produce a CREATE TABLE command that will work with *v15*. If you connected to a v14 database and did a SHOW CREATE TABLE, there's no guarantee that the CREATE TABLE statement returned would work for PG v15 due to keyword changes and other differences that can cause issues between major versions of PG. Now, that said, we have started ending up with some similar code between pg_dump and postgres_fdw in the form of IMPORT FOREIGN SCHEMA and maybe we should consider if that code could be moved into the common library and made available to pg_dump, postgres_fdw, and as a SHOW CREATE TABLE command with the caveat that the produced CREATE TABLE command may not work with newer versions of PG. There's an interesting question around if we'd consider it a bug worthy of fixing if IMPORT FOREIGN SCHEMA in v14 doesn't work when connecting to a v15 PG instance. Not sure if anyone's contemplated that. There's certainly going to be cases that we wouldn't accept fixing (we wouldn't add some new partitioning strategy to v14 just because it's in v15, for example, to make IMPORT FOREIGN SCHEMA work...). Thanks, Stephen signature.asc Description: PGP signature