Re: Appendix A. PostgreSQL Error Codes
The SQL standard sorts the SQLSTATE table in a first step according to the *textual representation* of class (first two byte) and in a second step to the textual representation of subclass (next 3 byte) - keeping the text '(no subclass)' for subclass '000' on top. Obviously for some people this 'textual' sort order is relevant. For my personal use I strongly prefer a pure 'numeric' sort order of SQLSTATE. Therefore I created a Perl program, which generates the SGML file out of errcode.txt and published it in https://www.postgresql.org/message-id/d69ce4f1-dd4e-61c1-070e-25f662e9c622%40purtz.de . This was a follow-up action after a discussion about illegal SQLSTATE values in PG, see: https://www.postgresql.org/message-id/11558.1513819695%40sss.pgh.pa.us. PG documentation uses a numeric sort order for class and a semantic sort order for subclass. Which one of the three is best? Here is my judgement: For technical oriented people the first approach is horrible because it sorts not only the subclass-text but also the class-text. This scrambles important and less important values to arbitrary positions. For me, sorting of the numeric value of class in a first step is essential. The third approach may be good for persons which work on the improvement of the PG source code. But they can refer to errcodes.txt. The primary readers of our documentation are users which work in roles as DBA or application developer. I doubt that they are interested in the semantic order of subclass. But I do not know whether they prefer numeric to text or text to numeric translation. Jürgen Purtz On 27.04.2018 17:16, Tom Lane wrote: Bruce Momjian writes: On Fri, Apr 27, 2018 at 10:08:12AM +, PG Doc comments form wrote: Would it be possible to present the codes in ascending sequence? Uh, I am guessing this is the order listed in the SQL standard. How would you order them differently? Have the letters only at the end? There was some discussion of this a couple months ago, which I'm too lazy to search the archives for right now, but it trailed off without any agreement on what to do. There are at least three different ordering principles that might be applied here: numeric by code (well, perhaps more like "ASCII sort order"); alphabetical by exception name (but probably still keeping the 000 category codes at the top); or semantic grouping (ie try to keep related errors together). It looks to me like all three of these have been applied in different places ;-). One idea that might help is to present two tables with different sort orders, say strict numeric and strict alphabetical. (If we did that, I'd be inclined to leave errcodes.txt alone and put the sorting responsibility on the script that converts it to SGML.) One question that has to be asked is what we think the use-case for this table is at all. Different use-cases result in different ideas about the best ordering. regards, tom lane
Documentation for CommandComplete is incorrect for CREATE TABLE with server version 10.2
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/protocol-message-formats.html Description: On this page: https://www.postgresql.org/docs/10/static/protocol-message-formats.html The documentation says: For a SELECT or CREATE TABLE AS command, the tag is SELECT rows where rows is the number of rows retrieved. But what I get back from the server is this packet: 43 00 00 00 11 43 52 45 41 54 45 20 54 41 42 4c CCREATE TABL 0010 45 00E. The query I sent to the server was: create table table1(i int)
Isn't "publication" wrongly defined here?
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/10/static/logical-replication-publication.html Description: In section 31.1 it says "A publication is a set of changes generated from a table or a group of tables"... that seems wrong. It's a set of relations, tables, whatevers. No the changes themselves. Right?
RE: Appendix A. PostgreSQL Error Codes
Hi The use case is pretty simple; my system has thrown an SQL error code, I want to know what it means. Having the list in 'order by sqlstate' sequence would save me from having to hunt up and down the list. And I can easily determine if the error code is missing; I can then raise another bug report. Regards Andy Dossett -Original Message- From: Tom Lane Sent: 27 April 2018 16:16 To: Bruce Momjian Cc: doss...@btinternet.com; pgsql-docs@lists.postgresql.org Subject: Re: Appendix A. PostgreSQL Error Codes Bruce Momjian writes: > On Fri, Apr 27, 2018 at 10:08:12AM +, PG Doc comments form wrote: >> Would it be possible to present the codes in ascending sequence? > Uh, I am guessing this is the order listed in the SQL standard. How > would you order them differently? Have the letters only at the end? There was some discussion of this a couple months ago, which I'm too lazy to search the archives for right now, but it trailed off without any agreement on what to do. There are at least three different ordering principles that might be applied here: numeric by code (well, perhaps more like "ASCII sort order"); alphabetical by exception name (but probably still keeping the 000 category codes at the top); or semantic grouping (ie try to keep related errors together). It looks to me like all three of these have been applied in different places ;-). One idea that might help is to present two tables with different sort orders, say strict numeric and strict alphabetical. (If we did that, I'd be inclined to leave errcodes.txt alone and put the sorting responsibility on the script that converts it to SGML.) One question that has to be asked is what we think the use-case for this table is at all. Different use-cases result in different ideas about the best ordering. regards, tom lane
Re: Documentation for CommandComplete is incorrect for CREATE TABLE with server version 10.2
PG Doc comments form wrote: > The documentation says: > > For a SELECT or CREATE TABLE AS command, the tag is SELECT rows where rows > is the number of rows retrieved. > > But what I get back from the server is this packet: > > 43 00 00 00 11 43 52 45 41 54 45 20 54 41 42 4c CCREATE TABL > 0010 45 00E. > > The query I sent to the server was: > > create table table1(i int) Yeah, this is not a CREATE TABLE AS. It's a plain CREATE TABLE. Different thing. alvherre=# create table dcm as select 'foo'; SELECT 1 -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: Documentation for CommandComplete is incorrect for CREATE TABLE with server version 10.2
=?utf-8?q?PG_Doc_comments_form?= writes: > The documentation says: > For a SELECT or CREATE TABLE AS command, the tag is SELECT rows where rows > is the number of rows retrieved. Yeah ... > But what I get back from the server is this packet: > 43 00 00 00 11 43 52 45 41 54 45 20 54 41 42 4c CCREATE TABL > 0010 45 00E. > The query I sent to the server was: > create table table1(i int) CREATE TABLE and CREATE TABLE AS are two different things. regression=# create table foo (x int); CREATE TABLE regression=# create table bar as select 1 as x; SELECT 1 regards, tom lane
Re: Appendix A. PostgreSQL Error Codes
"Andy Dossett" writes: > The use case is pretty simple; my system has thrown an SQL error code, I > want to know what it means. > Having the list in 'order by sqlstate' sequence would save me from having to > hunt up and down the list. And I can easily determine if the error code is > missing; I can then raise another bug report. Hm ... I'm not finding that to be a very compelling argument. Reading the message text is usually *far* more illuminating, because the error codes are (in many cases) rather broad categories rather than exact conditions. For the use case of "why did I get this error", I'd say that looking in Appendix A is quite unhelpful, and sorting it differently wouldn't make it any more helpful. regards, tom lane
Re: Isn't "publication" wrongly defined here?
On Friday, April 27, 2018, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/10/static/logical- > replication-publication.html > Description: > > In section 31.1 it says "A publication is a set of changes generated from a > table or a group of tables"... that seems wrong. It's a set of relations, > tables, whatevers. No the changes themselves. Right? > It's correct. The subscriber receives ongoing "events" which are the logical changes to all tables defined within the publication. The items of interest are the ongoing changes, the list of tables is just a table of contents for which change sets are present. David J.
Re: Documentation for CommandComplete is incorrect for CREATE TABLE with server version 10.2
Thanks for the clarification. Maybe CREATE TABLE should be added as it's own line, since it's a distinct case from the others in that table? best regards Alexander Kjäll Den lör 28 apr. 2018 kl 23:01 skrev Tom Lane : > =?utf-8?q?PG_Doc_comments_form?= writes: > > The documentation says: > > For a SELECT or CREATE TABLE AS command, the tag is SELECT rows where rows > > is the number of rows retrieved. > Yeah ... > > But what I get back from the server is this packet: > > 43 00 00 00 11 43 52 45 41 54 45 20 54 41 42 4c CCREATE TABL > > 0010 45 00E. > > The query I sent to the server was: > > create table table1(i int) > CREATE TABLE and CREATE TABLE AS are two different things. > regression=# create table foo (x int); > CREATE TABLE > regression=# create table bar as select 1 as x; > SELECT 1 > regards, tom lane
Re: Appendix A. PostgreSQL Error Codes
> On Apr 28, 2018, at 5:11 PM, Tom Lane wrote: > > "Andy Dossett" writes: >> The use case is pretty simple; my system has thrown an SQL error code, I >> want to know what it means. >> Having the list in 'order by sqlstate' sequence would save me from having to >> hunt up and down the list. And I can easily determine if the error code is >> missing; I can then raise another bug report. > > Hm ... I'm not finding that to be a very compelling argument. Reading the > message text is usually *far* more illuminating, because the error codes > are (in many cases) rather broad categories rather than exact conditions. > For the use case of "why did I get this error", I'd say that looking in > Appendix A is quite unhelpful, and sorting it differently wouldn't make > it any more helpful. Is this information we might want to add to the main part of the website? We could provide the list of error codes in Appendix A as is right now, reference to “see more info on "/support/sql-error-codes/“ or some URL. The table on such a page could contain features such as: - sortable by number vs. alphabetical - “searchable” if needed (eliminate results) - more information about what causes such errors, perhaps with an example of the detailed message. I don’t know if this would be helpful or not. Personally I’ve only looked at the error codes section a handful of times, mostly out of curiosity or experimenting with ORM/adapter type code. Jonathan
