Re: Incorrect/confusing information about timetz
On Thu, Sep 7, 2023 at 05:00:59PM -0400, Bruce Momjian wrote: > On Thu, Sep 7, 2023 at 04:41:48PM -0400, Tom Lane wrote: > > Bruce Momjian writes: > > > Uh, yes to the storage part, no to the output part. ;-) Postgres does > > > store the timetz time zone offset, but it doesn't adust it once it is > > > stored so doesn't adjust for the session time zone: > > > > Right, it just stores a numeric UTC offset. > > > > > Do we want to document this? > > > > Section 8.5.1.2. Times already says "The appropriate time zone offset > > is recorded in the time with time zone value." Maybe that could be > > made a little more precise, say "The resolved numeric offset from UTC > > is recorded in the time with time zone value." > > Yeah, there is no mention of it odd output behavior. Here is a patch to > add that. Patch applied back to Postgres 11. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Re: group by can use alias from select list
On Thu, Sep 7, 2023 at 10:11:00AM -0400, Bruce Momjian wrote: > We do farther down the docs in the GROUP BY section: > > https://www.postgresql.org/docs/devel/sql-select.html > > GROUP BY will condense into a single row all selected rows that share > the same values for the grouped expressions. An expression used inside a > grouping_element can be an input column name, or the name or ordinal > number of an output column (SELECT list item), or an arbitrary > expression formed from input-column values. In case of ambiguity, a > > GROUP BY name will be interpreted as an input-column name rather than an > output column name. > > It says > > * input column name > * output column name or ordinal number > * expression formed from input-column values > > Is this too subtle? Patch applied back to PG 11. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Re: pg_upgrade doc uses inconsistent versions within the doc.
On Tue, Sep 26, 2023 at 10:56:27AM -0700, David G. Johnston wrote: > On Tue, Sep 26, 2023 at 10:35 AM Bruce Momjian wrote: > > On Mon, Sep 18, 2023 at 08:14:04PM +, PG Doc comments form wrote: > > The following documentation comment has been logged on the website: > > > > Page: https://www.postgresql.org/docs/16/pgupgrade.html > > Description: > > > > I feel like for readability the pg_upgrade doc should use the same > > old_version and new_version examples when showing examples throughout > the > > doc page. > > > > as an exmaple I'm looking at : > > https://www.postgresql.org/docs/current/pgupgrade.html > > > > in some places the example commands use v9.6 and v16. (see step 8) > > > > but in other places it mixes versions v9.5 and v9.6 (see step 11 sub > step > 8) > > Good point. I came up with the attached patch. It uses 9.6 and current > > > I would get rid of any mentions of our old pre-v10 versioning scheme in the > current documentation. Good point, how is this attached patch? -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you. diff --git a/doc/src/sgml/ref/pgupgrade.sgml b/doc/src/sgml/ref/pgupgrade.sgml index bea0d1b93f..5dd2c446f7 100644 --- a/doc/src/sgml/ref/pgupgrade.sgml +++ b/doc/src/sgml/ref/pgupgrade.sgml @@ -40,9 +40,9 @@ PostgreSQL documentation pg_upgrade (formerly called pg_migrator) allows data stored in PostgreSQL data files to be upgraded to a later PostgreSQL major version without the data dump/restore typically required for - major version upgrades, e.g., from 9.5.8 to 9.6.4 or from 10.7 to 11.2. - It is not required for minor version upgrades, e.g., from 9.6.2 to 9.6.3 - or from 10.1 to 10.2. + major version upgrades, e.g., from 12.14 to 13.10 or from 14.9 to 15.5. + It is not required for minor version upgrades, e.g., from 12.7 to 12.8 + or from 14.1 to 14.5. @@ -390,14 +390,14 @@ make prefix=/usr/local/pgsql.new install Make sure both database servers are stopped using, on Unix, e.g.: -pg_ctl -D /opt/PostgreSQL/9.6 stop +pg_ctl -D /opt/PostgreSQL/12 stop pg_ctl -D /opt/PostgreSQL/ stop or on Windows, using the proper service names: -NET STOP postgresql-9.6 +NET STOP postgresql-12 NET STOP postgresql- @@ -473,9 +473,9 @@ SET PATH=%PATH%;C:\Program Files\PostgreSQL\\bin; pg_upgrade.exe ---old-datadir "C:/Program Files/PostgreSQL/9.6/data" +--old-datadir "C:/Program Files/PostgreSQL/12/data" --new-datadir "C:/Program Files/PostgreSQL//data" ---old-bindir "C:/Program Files/PostgreSQL/9.6/bin" +--old-bindir "C:/Program Files/PostgreSQL/12/bin" --new-bindir "C:/Program Files/PostgreSQL//bin" @@ -605,8 +605,8 @@ rsync --archive --delete --hard-links --size-only --no-inc-recursive old_cluster remote directory, e.g., -rsync --archive --delete --hard-links --size-only --no-inc-recursive /opt/PostgreSQL/9.5 \ - /opt/PostgreSQL/9.6 standby.example.com:/opt/PostgreSQL +rsync --archive --delete --hard-links --size-only --no-inc-recursive /opt/PostgreSQL/12 \ + /opt/PostgreSQL/ standby.example.com:/opt/PostgreSQL You can verify what the command will do using @@ -635,8 +635,8 @@ rsync --archive --delete --hard-links --size-only --no-inc-recursive /opt/Postgr rsync command for each tablespace directory, e.g.: -rsync --archive --delete --hard-links --size-only --no-inc-recursive /vol1/pg_tblsp/PG_9.5_201510051 \ - /vol1/pg_tblsp/PG_9.6_201608131 standby.example.com:/vol1/pg_tblsp +rsync --archive --delete --hard-links --size-only --no-inc-recursive /vol1/pg_tblsp/PG_12_201909212 \ + /vol1/pg_tblsp/PG__202307071 standby.example.com:/vol1/pg_tblsp If you have relocated pg_wal outside the data
Re: correct documentation in set role
On Tue, Sep 26, 2023 at 11:44:42AM -0400, Bruce Momjian wrote: > On Mon, Sep 25, 2023 at 07:46:18AM +, PG Doc comments form wrote: > > The following documentation comment has been logged on the website: > > > > Page: https://www.postgresql.org/docs/16/sql-set-role.html > > Description: > > > > it is good to mention that: > > The specified role_name must be a role that the current session user is a > > member of "and has SET True option on it." > > The second part missed in documentation for set role command. > > Good point. How is the attached patch? > diff --git a/doc/src/sgml/ref/set_role.sgml b/doc/src/sgml/ref/set_role.sgml > index 13bad1bf66..a2661945aa 100644 > --- a/doc/src/sgml/ref/set_role.sgml > +++ b/doc/src/sgml/ref/set_role.sgml > @@ -42,7 +42,8 @@ RESET ROLE > > > The specified role_name > - must be a role that the current session user is a member of. > + must be a role that the current session user is a member of and > + is granted WITH SET TRUE. > (If the session user is a superuser, any role can be selected.) > Someone is already working on this text addition from a post yesterday: https://www.postgresql.org/message-id/20230925220945.GB3172533%40nathanxps13 Please ignore my patch and I will be sure the patch at this URL gets committed. Thanks. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you.
Re: pg_upgrade doc uses inconsistent versions within the doc.
On Tue, Sep 26, 2023 at 10:35 AM Bruce Momjian wrote: > On Mon, Sep 18, 2023 at 08:14:04PM +, PG Doc comments form wrote: > > The following documentation comment has been logged on the website: > > > > Page: https://www.postgresql.org/docs/16/pgupgrade.html > > Description: > > > > I feel like for readability the pg_upgrade doc should use the same > > old_version and new_version examples when showing examples throughout the > > doc page. > > > > as an exmaple I'm looking at : > > https://www.postgresql.org/docs/current/pgupgrade.html > > > > in some places the example commands use v9.6 and v16. (see step 8) > > > > but in other places it mixes versions v9.5 and v9.6 (see step 11 sub > step 8) > > Good point. I came up with the attached patch. It uses 9.6 and current > I would get rid of any mentions of our old pre-v10 versioning scheme in the current documentation. David J.
Re: pg_upgrade doc uses inconsistent versions within the doc.
On Mon, Sep 18, 2023 at 08:14:04PM +, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/16/pgupgrade.html > Description: > > I feel like for readability the pg_upgrade doc should use the same > old_version and new_version examples when showing examples throughout the > doc page. > > as an exmaple I'm looking at : > https://www.postgresql.org/docs/current/pgupgrade.html > > in some places the example commands use v9.6 and v16. (see step 8) > > but in other places it mixes versions v9.5 and v9.6 (see step 11 sub step 8) Good point. I came up with the attached patch. It uses 9.6 and current --- the only problem is that the target directory is now: /vol1/pg_tblsp/PG__202307071 - and since the catalog version is not accessible from SGML, it will usually not match the major version's catalog number. I would apply this only to Postgres 17 since it isn't really a fix. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you. diff --git a/doc/src/sgml/ref/pgupgrade.sgml b/doc/src/sgml/ref/pgupgrade.sgml index bea0d1b93f..dc9e52a684 100644 --- a/doc/src/sgml/ref/pgupgrade.sgml +++ b/doc/src/sgml/ref/pgupgrade.sgml @@ -605,8 +605,8 @@ rsync --archive --delete --hard-links --size-only --no-inc-recursive old_cluster remote directory, e.g., -rsync --archive --delete --hard-links --size-only --no-inc-recursive /opt/PostgreSQL/9.5 \ - /opt/PostgreSQL/9.6 standby.example.com:/opt/PostgreSQL +rsync --archive --delete --hard-links --size-only --no-inc-recursive /opt/PostgreSQL/9.6 \ + /opt/PostgreSQL/ standby.example.com:/opt/PostgreSQL You can verify what the command will do using @@ -635,8 +635,8 @@ rsync --archive --delete --hard-links --size-only --no-inc-recursive /opt/Postgr rsync command for each tablespace directory, e.g.: -rsync --archive --delete --hard-links --size-only --no-inc-recursive /vol1/pg_tblsp/PG_9.5_201510051 \ - /vol1/pg_tblsp/PG_9.6_201608131 standby.example.com:/vol1/pg_tblsp +rsync --archive --delete --hard-links --size-only --no-inc-recursive /vol1/pg_tblsp/PG_9.6_201608131 \ + /vol1/pg_tblsp/PG__202307071 standby.example.com:/vol1/pg_tblsp If you have relocated pg_wal outside the data
Re: JSON type unsupported
Vik Fearing writes: > Thank you for pointing this out. Peter's blog is slightly misleading in > that we do have the JSON data type, but the T801 feature is far from > complete. > For example, we do not have the JSON_SERIALIZE(), JSON()[1], > JSON_SCALAR() functions. We don't have the IS JSON predicate, or a few > other things hiding behind T801. > All in all, I would not say we support the JSON data type the way the > standard intends, and therefore we should not claim to support T801. Agreed, but should we say "partial support", as we do for some other feature identifiers? regards, tom lane
Re: correct documentation in set role
On Mon, Sep 25, 2023 at 07:46:18AM +, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/16/sql-set-role.html > Description: > > it is good to mention that: > The specified role_name must be a role that the current session user is a > member of "and has SET True option on it." > The second part missed in documentation for set role command. Good point. How is the attached patch? -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you. diff --git a/doc/src/sgml/ref/set_role.sgml b/doc/src/sgml/ref/set_role.sgml index 13bad1bf66..a2661945aa 100644 --- a/doc/src/sgml/ref/set_role.sgml +++ b/doc/src/sgml/ref/set_role.sgml @@ -42,7 +42,8 @@ RESET ROLE The specified role_name - must be a role that the current session user is a member of. + must be a role that the current session user is a member of and + is granted WITH SET TRUE. (If the session user is a superuser, any role can be selected.)
Re: JSON type unsupported
On 9/26/23 11:33, Erki Eessaar wrote: Hello According to Appendix D. SQL Conformance https://www.postgresql.org/docs/current/unsupported-features-sql-standard.html the feature T801 JSON data type is unsupported. However, PostgreSQL has data types JSON and JSONB. Here (http://peter.eisentraut.org/blog/2023/04/18/postgresql-and-sql-2023) it is also said that T801 is supported. Perhaps there are some technical details that I am not aware of. However, I wanted to point this out. Thank you for pointing this out. Peter's blog is slightly misleading in that we do have the JSON data type, but the T801 feature is far from complete. For example, we do not have the JSON_SERIALIZE(), JSON()[1], JSON_SCALAR() functions. We don't have the IS JSON predicate, or a few other things hiding behind T801. All in all, I would not say we support the JSON data type the way the standard intends, and therefore we should not claim to support T801. [1] This is a fully specified parsing function, not just a cast like we have today. -- Vik Fearing
JSON type unsupported
Hello According to Appendix D. SQL Conformance https://www.postgresql.org/docs/current/unsupported-features-sql-standard.html the feature T801 JSON data type is unsupported. However, PostgreSQL has data types JSON and JSONB. Here (http://peter.eisentraut.org/blog/2023/04/18/postgresql-and-sql-2023) it is also said that T801 is supported. Perhaps there are some technical details that I am not aware of. However, I wanted to point this out. Best regards Erki Eessaar
