Re: set time zone
On Tue, Aug 16, 2022 at 08:41:05PM -0400, Bruce Momjian wrote: > On Tue, Dec 7, 2021 at 02:28:09PM +, PG Doc comments form wrote: > > TIME ZONE > > SET TIME ZONE timezone_name is an alias for SET timezone TO timezone_name. > > I ended up changing the syntax from 'timezone' to 'value' and added > quoted and non-quoted syntax options, again in the attached patch. Patch applied back to PG 10. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Indecision is a decision. Inaction is an action. Mark Batterson
Re: SQL-standard function bodies and creating SECURITY DEFINER routines securely
On Tue, Aug 16, 2022 at 03:38:13PM -0400, Bruce Momjian wrote: > On Tue, Aug 16, 2022 at 03:34:22PM -0400, Tom Lane wrote: > > Bruce Momjian writes: > > > I have written the attached patch to mention this issue about sql_body > > > functions. > > > > Spell-check, please. Seems OK otherwise. > > Just when I think I didn't add enough text to warrant a spell check. > :-( Updated patch attached. Patch applied back to PG 10. Thanks. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Indecision is a decision. Inaction is an action. Mark Batterson
Re: nicer examples for aggregate calls
On Wed, Aug 17, 2022 at 12:20:10PM -0400, Bruce Momjian wrote: > On Sat, Oct 23, 2021 at 01:51:48PM +, PG Doc comments form wrote: > > The following documentation comment has been logged on the website: > > > > Page: https://www.postgresql.org/docs/14/tutorial-agg.html > > Description: > > > > currently, all of the examples are very simple, like > > > > SELECT city, max(temp_lo) > > FROM weather > > WHERE city LIKE 'S%'-- (1) > > GROUP BY city > > HAVING max(temp_lo) < 40; > > > > this example would be more complex and would allow users to search for > > clause "filter": > > > > Finally, if we only care about cities whose names begin with “S” and we want > > to calculate the number of observations in each city with temp_lo over 30, > > we might do: > > > > SELECT city, max(temp_lo), count(*) filter (temp_lo>30), > > FROM weather > > WHERE city LIKE 'S%'-- (1) > > GROUP BY city > > HAVING max(temp_lo) < 40; > > Good idea. We didn't support FILTER at the time this query was added. > Here is a patch which adds it. Patch applied back to PG 10. Thanks. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Indecision is a decision. Inaction is an action. Mark Batterson
Re: CREATE INDEX...USING
On Thu, Aug 18, 2022 at 12:32:28PM -0400, Bruce Momjian wrote: > On Wed, Aug 17, 2022 at 11:21:58PM -0400, Bruce Momjian wrote: > > However, CREATE INDEX just lists the parameters, not the keywords, so it > > was already there as 'method', as you mentioned above. I will just > > remove the USING section I recently added. > > I wrote the attached patch to address the issues above. Patch applied back to PG 10. Thanks for the tip. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Indecision is a decision. Inaction is an action. Mark Batterson
Re: CREATE STATISTICS and partitoins/inheritance
On Wed, Aug 17, 2022 at 03:30:11PM -0400, Bruce Momjian wrote: > In looking at CREATE STATISTICS, I was confused how inheritance and > partisions were handled, so I confirmed with Tomas that the ANALYZE > manual page accurately describes how extended statistics are handled > since PG 15, so I plan to apply this patch to the CREATE STATISTICS > docs. Turns out analyze has information about parent table processing all the way back to PG 10, so I applied the patch back to that version. I want to clarify the ANALYZE docs related to parent tables, but that will be done in another email. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Indecision is a decision. Inaction is an action. Mark Batterson
Duplicate text in ANALYZE related to inheritance/parents
I noticed that our ANALYZE documentation has confusing duplicate text related to the analyzing of inheritance trees. This patch fixes it. -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Indecision is a decision. Inaction is an action. Mark Batterson diff --git a/doc/src/sgml/ref/analyze.sgml b/doc/src/sgml/ref/analyze.sgml index b968f740cb..2ba115d1ad 100644 --- a/doc/src/sgml/ref/analyze.sgml +++ b/doc/src/sgml/ref/analyze.sgml @@ -250,12 +250,13 @@ ANALYZE [ VERBOSE ] [ table_and_columns -If the table being analyzed has one or more children, -ANALYZE will gather statistics twice: once on the -rows of the parent table only, and a second time on the rows of the -parent table with all of its children. This second set of statistics -is needed when planning queries that traverse the entire inheritance -tree. The autovacuum daemon, however, will only consider inserts or +If the table being analyzed has inheritance children, +ANALYZE gathers two sets of statistics: one on the rows +of the parent table only, and a second including rows of both the parent +table and all of its children. This second set of statistics is needed when +planning queries that process the inheritance tree as a whole. The child +tables themselves are not individually analyzed in this case. +The autovacuum daemon, however, will only consider inserts or updates on the parent table itself when deciding whether to trigger an automatic analyze for that table. If that table is rarely inserted into or updated, the inheritance statistics will not be up to date unless you @@ -271,15 +272,6 @@ ANALYZE [ VERBOSE ] [ table_and_columns - -By contrast, if the table being analyzed has inheritance children, -ANALYZE gathers two sets of statistics: one on the rows -of the parent table only, and a second including rows of both the parent -table and all of its children. This second set of statistics is needed when -planning queries that process the inheritance tree as a whole. The child -tables themselves are not individually analyzed in this case. - - The autovacuum daemon does not process partitioned tables, nor does it process inheritance parents if only the children are ever modified.
Re: CREATE STATISTICS and partitoins/inheritance
On Wed, Aug 31, 2022 at 11:15:41PM -0400, Bruce Momjian wrote: > On Wed, Aug 17, 2022 at 03:30:11PM -0400, Bruce Momjian wrote: > > In looking at CREATE STATISTICS, I was confused how inheritance and > > partisions were handled, so I confirmed with Tomas that the ANALYZE > > manual page accurately describes how extended statistics are handled > > since PG 15, so I plan to apply this patch to the CREATE STATISTICS > > docs. > > Turns out analyze has information about parent table processing all the > way back to PG 10, so I applied the patch back to that version. I didn't see this until now, but you're probably referring to the docs added at 61fa6ca79b3c566f44831a33bb226f7358ed4511. > I want to clarify the ANALYZE docs related to parent tables, but that > will be done in another email. Would you copy me on that ? -- Justin
