Re: Proposed HTML Documentation Styles

[Pavel Stehule]

čt 4. 10. 2018 v 17:50 odesílatel Jonathan S. Katz 
napsal:

> Hi,
>
> As part of the effort to modernize the look and feel of PostgreSQL.org
> and associated web projects, Sarah & I have worked on applying the new
> styles to the documentation. The main goals of the project were:
>
> - To have the documentation styles match that of the main website
> - To make the documentation easier to view on mobile devices
> - To set up the web-based documentation for future usability changes and
> improvements
>
> Other than reversing how the versions are display at the top of the
> website, we did not change any of the documentation structure and do not
> intend to as part of this version of the project.
>
> We have created a prototype of the new styles that can be viewed here,
> with the credentials below:
>
> http://174.138.60.30/docs/
> pgdocs / newstyles
>
> When browsing through this site, please note:
>
> - All interactions are confined to the "/docs" folder
> - Not all of the docs are loaded and they are not all the latest
> versions - this is just for beta testing purposes
> - Search in the prototype does not work
>
> We are looking for feedback in the following areas:
>
> - Things that may have degraded the user experience, e.g. things that
> are difficult to read
> - Visual bugs and errors
>

When I am going to document, then I see (about 0.5 sec) big PostgreSQL
logo. It is not pleasant effect.

I don't like table style - middle vertical line is too black

Used colour palette is maybe too red based.

All my notes are subjective, just my feeling

Regards

Pavel



> Our goal is to launch these changes with the upcoming major release, so
> we appreciate your diligence in providing helpful feedback so we can
> provide the best possible experience.
>
> Thanks,
>
> Jonathan
>
>

Re: Example 42.4. A PL/pgSQL Trigger Function for Auditing

[Pavel Stehule]

po 16. 12. 2019 v 12:12 odesílatel PG Doc comments form <
nore...@postgresql.org> napsal:

> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/12/plpgsql-trigger.html
> Description:
>
> I'm wondering if it would be worthwhile to put a totally generic auditing
> function into the documentation e.g.
>
> CREATE OR REPLACE FUNCTION zz_audit_() RETURNS TRIGGER AS $nothing$
>
> -- This function is intended to be used by a delete/insert/update trigger
> for any table.
> -- It relies on the existence of a table named zz_audit_XXX (where XXX is
> the table being audited) that contains the
> -- same columns as the table XXX except that two additional columns must
> exist prior to the columns from XXX
> --operation character(1) NOT NULL,
> --tstamptimestamp with time zone NOT NULL,
> --...   remaining columns per table XXX
>
> DECLARE audit_table_name NAME := CONCAT('zz_audit_', TG_TABLE_NAME);
> BEGIN
>
> IF (TG_OP = 'DELETE') THEN
> EXECUTE 'INSERT INTO ' || audit_table_name || ' SELECT ''D'',
> now(), ' || ' $1.*' USING OLD;
> ELSIF (TG_OP = 'UPDATE') THEN
> EXECUTE 'INSERT INTO ' || audit_table_name || '
> SELECT ''U'', now(), ' ||
> ' $1.*' USING NEW;
> ELSIF (TG_OP = 'INSERT') THEN
> EXECUTE 'INSERT INTO ' || audit_table_name || '
> SELECT ''I'', now(), ' ||
> ' $1.*' USING NEW;
> END IF;
>
> RETURN NULL; -- result is ignored since this is an AFTER trigger
> END;
> $nothing$ LANGUAGE plpgsql;
>

Just few points to this code

1. bad, useless brackets in IF .. ELSIF expressions - plpgsql is not C or
Java
2. unescaped identifiers in dynamic SQL - EXECUTE
3. there is not reason for INSERT SELECT.

Regards

Pavel

Re: Example 42.4. A PL/pgSQL Trigger Function for Auditing

[Pavel Stehule]

Dne po 16. 12. 2019 20:28 uživatel Tom Lane  napsal:

> Pavel Stehule  writes:
> > po 16. 12. 2019 v 12:12 odesílatel PG Doc comments form <
> > nore...@postgresql.org> napsal:
> >> I'm wondering if it would be worthwhile to put a totally generic
> auditing
> >> function into the documentation e.g.
> >> [ snip ]
>
> > Just few points to this code
>
> I agree this code could have better style, but maybe that is just more
> evidence that a well-written example would be helpful?
>

+1

there is not too much examples for trigger parameters.



> regards, tom lane
>

Re: explanation for random_page_cost is outdated

[Pavel Stehule]

ne 26. 4. 2020 v 21:25 odesílatel yigong hu  napsal:

> Sorry to hijack the thread, I also recently have similar observation that
> the statement about random_page_cost on SSD is ambiguous. The current
> document says that
>
> > Storage that has a low random read cost relative to sequential, e.g.
> solid-state drives, might also be better modeled with a lower value for
> random_page_cost.
>
> However, this statement does not clarify what values might be good. For
> some workload, the default value 4.0 would cause bad performance and
> lowering random_page_cost to a value 3.0 or 2.0 does not solve the
> performance problem. Only when the random_page_cost is lowered to below 1.2
> will the bad performance be mitigated. Thus, I would suggest elaborating on
> this description further as:
>
>  >  Storage that has a low random read cost relative to sequential, e.g.
> solid-state drives, might also be better modeled with a value that is close
> to 1 for random_page_cost.
>

I depends on estimation. Lot of people use random_page_cost as fix of
broken estimation. Then configures this value to some strange values. Lot
of other queries with good estimation can be worse then.



> Detail:
>
> I run the PostgreSQL 11 on an SSD hardware. The database has two small
> tables with 6MB and 16MB separately. The pgbench runs a select join query
> in 1 min. The result shows that when the random_page_cost is 1, the average
> latency is 14ms. When the random_page_cost is 1.5, 2, 3 or 4, the average
> latency is 26ms. This result suggests that setting random_page_cost to a
> value larger than 1.5 would cause almost 2x latency. If I increase the 6MB
> table to 60MB and rerun the sysbench, the result shows that when the
> random_page_cost is 1, the average latency is 13ms. When the
> random_page_cost is 1.5,2,3 or 4, the average latency is 17ms.
>
> I attached my testing script, the postgresql configuration file, and
> planner output.
>
> On Mon, Apr 27, 2020 at 3:19 AM Олег Самойлов  wrote:
>
>> Yep. Unclear. What parameter is recommended for SSD? Lower? 3? 2? 1?
>>
>> Much better will be write: if you use SSD set 1.
>>
>> Олег
>>
>> > 19 марта 2020 г., в 23:56, Bruce Momjian  написал(а):
>> >
>> > On Thu, Feb 27, 2020 at 02:48:44PM +, PG Doc comments form wrote:
>> >> The following documentation comment has been logged on the website:
>> >>
>> >> Page: https://www.postgresql.org/docs/12/runtime-config-query.html
>> >> Description:
>> >>
>> >> Explanation for random_page_cost is rather outdated, because it did
>> only for
>> >> case of mechanical hdd. But all modern database servers, which I know,
>> made
>> >> upon SSD. Do or not do default value for random_page_cost equal to 1
>> is the
>> >> question, but, IMHO, at list in the documentation  about
>> random_page_cost
>> >> need to add in a speculation about SSD.
>> >>
>> >> It's important because a business programming now is mostly web
>> programming.
>> >> Most database is poorly designed by web programmer, tables looked like
>> a
>> >> primary key and a huge json (containing all) with large gin index upon
>> it.
>> >> Now I am seeing a table with a GIN index 50% of the table size. The
>> database
>> >> is on SSD, of cause.  With default random_page_cost=4 GIN index don't
>> used
>> >> by planner, but with random_page_cost=1 the result may be not
>> excellent, but
>> >> acceptable for web programmers.
>> >
>> > Does this sentence in the random_page_cost docs unclear or not have
>> enough
>> > visibility:
>> >
>> >
>> https://www.postgresql.org/docs/12/runtime-config-query.html#RUNTIME-CONFIG-QUERY-CONSTANTS
>> >
>> >Storage that has a low random read cost relative to sequential, e.g.
>> >solid-state drives, might also be better modeled with a lower value
>> for
>> >random_page_cost.
>> >
>> > --
>> >  Bruce Momjian  https://momjian.us
>> >  EnterpriseDB https://enterprisedb.com
>> >
>> > + As you are, so once was I.  As I am, so you will be. +
>> > +  Ancient Roman grave inscription +
>>
>>
>>
>>
>>
>>

Re: docs: psql and variable interpolation

[Pavel Stehule]

po 13. 7. 2020 v 15:09 odesílatel Philippe Beaudoin <
philippe.beaud...@dalibo.com> napsal:

> Hi all,
>
> I recently used the nice variable capabilities in psql, after having read
> the "SQL Interpolation" chapter in the psql page (
> https://www.postgresql.org/docs/12/app-psql.html).
>
> But I spent a lot of time trying to understand why using a variable in a
> \copy command failed ... until a colleague of mine showed me this was
> actually written elsewhere in the documentation (formerly in the \copy
> chapter).
>
> The first sentence of this SQL interpolation chapter says "A key feature
> of psql variables is that you can substitute (“interpolate”) them into
> regular SQL statements, as well as the arguments of meta-commands." But
> nothing in this chapter indicates that there are exceptions.
>
> May be we could have a wording adjustment with something like : "A key
> feature of psql variables is that you can substitute (“interpolate”) them
> into regular SQL statements, as well as the arguments of meta-commands
> (unless specifically noted)."
>
+1

or maybe better - if it is possible reduce a exceptions

Pavel


> Regards. Philippe.
> --
> 
> *DALIBO*
> *L'expertise PostgreSQL*
> 43, rue du Faubourg Montmartre
> 75009 Paris *Philippe Beaudoin*
> *Consultant Avant-Vente*
> +33 (0)1 84 72 76 11
> +33 (0)7 69 14 67 21
> philippe.beaud...@dalibo.com
> Valorisez vos compétences PostgreSQL, certifiez-vous chez Dalibo
>  !
>

Re: Procedures

[Pavel Stehule]

po 24. 8. 2020 v 17:01 odesílatel Bruce Momjian  napsal:

> On Sat, Aug 22, 2020 at 01:21:43PM -0700, David G. Johnston wrote:
> > On Sat, Aug 22, 2020 at 10:14 AM Bruce Momjian  wrote:
> >
> > On Fri, Aug 21, 2020 at 07:42:35PM -0700, David G. Johnston wrote:
> >
> > > [...] the CALL command.  If the CALL command is not part of an
> explicit
> > > transaction a procedure can also manage multiple transactions
> during its
> > > execution.
> >
> > OK, how is this updated patch?
> >
> >
> > Looks good.  I felt "begin and commit" was a bit wordy but it works.
>
> So, I was worried that "manage multiple transactions" could imply
> something like savepoints, which can be managed by functions.  It is
> really the top-level begin/commit that is unique for procedures.
>

Functions is executed under outer transaction every time - rollback to save
point hasn't impact on outer transaction. Inside procedures (in special
case) can be transactions ended (by statements COMMIT or ROLLBACK).
Immediately is started new transaction.



>
> --
>   Bruce Momjian  https://momjian.us
>   EnterpriseDB https://enterprisedb.com
>
>   The usefulness of a cup is in its emptiness, Bruce Lee
>
>
>
>

Re: Procedures

[Pavel Stehule]

po 24. 8. 2020 v 17:38 odesílatel Bruce Momjian  napsal:

> On Mon, Aug 24, 2020 at 11:35:57AM -0400, Bruce Momjian wrote:
> > On Mon, Aug 24, 2020 at 05:24:19PM +0200, Pavel Stehule wrote:
> > >
> > >
> > > po 24. 8. 2020 v 17:01 odesílatel Bruce Momjian 
> napsal:
> > >
> > > On Sat, Aug 22, 2020 at 01:21:43PM -0700, David G. Johnston wrote:
> > > > On Sat, Aug 22, 2020 at 10:14 AM Bruce Momjian 
> wrote:
> > > >
> > > > On Fri, Aug 21, 2020 at 07:42:35PM -0700, David G. Johnston
> wrote:
> > > >
> > > > > [...] the CALL command.  If the CALL command is not part
> of an
> > > explicit
> > > > > transaction a procedure can also manage multiple
> transactions
> > > during its
> > > > > execution.
> > > >
> > > > OK, how is this updated patch?
> > > >
> > > >
> > > > Looks good.  I felt "begin and commit" was a bit wordy but it
> works.
> > >
> > > So, I was worried that "manage multiple transactions" could imply
> > > something like savepoints, which can be managed by functions.  It
> is
> > > really the top-level begin/commit that is unique for procedures.
> > >
> > > Functions is executed under outer transaction every time - rollback to
> save
> > > point hasn't impact on outer transaction. Inside procedures (in
> special case)
> > > can be transactions ended (by statements COMMIT or ROLLBACK).
> Immediately is
> > > started new transaction.
> >
> > Well, savepoints control what commands are considered _part_ of the
> > outer transaction, so in a way you are managing what is in the outer
> > transaction.  This is why begin/commit was clearer for me.  Maybe "start
> > and commit" is clearer?
>
> Should the new text be?
>
> a procedure can commit and begin new transactions during its
> execution.
>

sure. Maybe enhancing about sentence like "it is not possible in a
function."

and

"a procedure can commit (or rollback) and begin new transactions during its
execution"


> --
>   Bruce Momjian  https://momjian.us
>   EnterpriseDB https://enterprisedb.com
>
>   The usefulness of a cup is in its emptiness, Bruce Lee
>
>

Re: Procedures

[Pavel Stehule]

po 24. 8. 2020 v 18:00 odesílatel Bruce Momjian  napsal:

> On Mon, Aug 24, 2020 at 05:51:29PM +0200, Pavel Stehule wrote:
> > sure. Maybe enhancing about sentence like "it is not possible in a
> function."
> >
> > and
> >
> > "a procedure can commit (or rollback) and begin new transactions during
> its
> > execution"
>
> OK, updated patch.
>

it is clean for me


> --
>   Bruce Momjian  https://momjian.us
>   EnterpriseDB https://enterprisedb.com
>
>   The usefulness of a cup is in its emptiness, Bruce Lee
>
>

Re: Version 13 documentation layout is harder to read than version 12

[Pavel Stehule]

út 29. 9. 2020 v 15:52 odesílatel Tom Lane  napsal:

> PG Doc comments form  writes:
> > Compare:
> > * https://www.postgresql.org/docs/13/functions-json.html
> > * https://www.postgresql.org/docs/12/functions-json.html
>
> > I know it is just my opinion, but the version 12 layout is easier to read
> > than version 13.
>
> IMO, table 9.47 (JSON Processing Functions) is pretty much the poster
> child for why we had to redesign the table layout.  In the old layout,
> it's close to unreadable in any normal-size browser window, despite
> wasting lots of whitespace and having large amounts of important info
> shuffled off into "Note" blocks.  The new layout is far more adaptable
> to viewing windows that aren't full-screen-width; and the "Note" text has
> all been merged into the table, so that you don't have to scroll down and
> back to find all the info about a function.
>
> I'll concede that it's a bit of a shock at first if you are used to the
> old layout.  But the developer community has been looking at this format
> for six months or so now, and I think people grew accustomed to it fairly
> quickly.
>

the new layout is probably better, but I miss some gentle separation of
these three parts - maybe using a different font?

Regards

Pavel



> regards, tom lane
>
>
>

Re: Version 13 documentation layout is harder to read than version 12

[Pavel Stehule]

út 29. 9. 2020 v 16:29 odesílatel Tom Lane  napsal:

> Pavel Stehule  writes:
> > the new layout is probably better, but I miss some gentle separation of
> > these three parts - maybe using a different font?
>
> Hm?  If you mean synopsis vs. description vs. examples, the description
> already is a different font from the other two.
>

I have firefox and if there are different fonts, then for me it is
invisible.

see attached screenshot


> I recall that we did experiment with extra vertical whitespace to
> separate, but abandoned that, possibly because controlling it was too
> painful with DocBook.
>
> regards, tom lane
>

Re: Version 13 documentation layout is harder to read than version 12

[Pavel Stehule]

út 29. 9. 2020 v 16:32 odesílatel Pavel Stehule 
napsal:

>
>
> út 29. 9. 2020 v 16:29 odesílatel Tom Lane  napsal:
>
>> Pavel Stehule  writes:
>> > the new layout is probably better, but I miss some gentle separation of
>> > these three parts - maybe using a different font?
>>
>> Hm?  If you mean synopsis vs. description vs. examples, the description
>> already is a different font from the other two.
>>
>
> I have firefox and if there are different fonts, then for me it is
> invisible.
>

one is proportional and second not, but this text looks really very similar
- it is hard to find differences for me.


> see attached screenshot
>
>
>> I recall that we did experiment with extra vertical whitespace to
>> separate, but abandoned that, possibly because controlling it was too
>> painful with DocBook.
>>
>> regards, tom lane
>>
>

Re: Wrong parameter names for make_interval (Postgres 13)

[Pavel Stehule]

po 5. 10. 2020 v 15:56 odesílatel Thomas Kellerer  napsal:

> There is a typo in the parameter names of the make_interval() function.
>
> The parameter names are all defined with plural, not singular as it is
> shown
> in the Postgres 13 manual.
>
> So instead of
>
> make_interval ( [ year int [, month int [, week int [, day int [, hour
> int [, min int [, sec double precision ]]] )
>
> it should be
>
> make_interval ( [ years int [, months int [, weeks int [, days int
> [,hours int [, mins int [, secs double precision ]]] )
>

this syntax is not correct too

It should be

make_interval( years int default 0, month int default 0, days int
default 0, hours int default 0, secs double precision default 0)

Regards

Pavel



> Thomas
>
>
>

Re: Wrong parameter names for make_interval (Postgres 13)

[Pavel Stehule]

po 5. 10. 2020 v 17:53 odesílatel Tom Lane  napsal:

> Pavel Stehule  writes:
> > po 5. 10. 2020 v 15:56 odesílatel Thomas Kellerer 
> napsal:
> >> So instead of
> >> make_interval ( [ year int [, month int [, week int [, day int [, hour
> >> int [, min int [, sec double precision ]]] )
> >> it should be
> >> make_interval ( [ years int [, months int [, weeks int [, days int
> >> [,hours int [, mins int [, secs double precision ]]] )
>
> Right, fixed.
>
> > this syntax is not correct too
> > It should be
> > make_interval( years int default 0, month int default 0, days int
> > default 0, hours int default 0, secs double precision default 0)
>
> IIRC, I intentionally changed that in v13; the existence of the defaults
> is sufficiently covered by the text "... fields, each of which can default
> to zero".  I think that was partly motivated by trying to get the function
> signature to fit into limited space.  The final docs-table design we ended
> up with might allow undoing it, but I don't see any real reason to.  The
> other way is more verbose and not any clearer.
>

I don't understand,

the syntax [ a [, b]] means

so a and b are optional, but b can be used only when a is used. But for
make_interval I can use "months" arguments without specification of "years"
argument.

I don't know the correct BNF for arguments with default values, but using
this doesn't look correct.

Regards

Pavel



> I spent a little bit of time scanning for other discrepancies between
> func.sgml and pg_proc.proargnames, and found several, mostly though
> not exclusively in the JSON functions.  In these other cases, though,
> I think there might be a good argument for making pg_proc fit the docs
> not the other way around.  In the JSON functions, for example, pg_proc
> randomly has some functions calling the main JSON[B] input "target"
> while others call it "from_json" or "json_in".  I'm not real sure
> which of those names is preferable, but inconsistency is not preferable.
>
> regards, tom lane
>

Re: Wrong parameter names for make_interval (Postgres 13)

[Pavel Stehule]

po 5. 10. 2020 v 18:48 odesílatel Pavel Stehule 
napsal:

>
>
> po 5. 10. 2020 v 17:53 odesílatel Tom Lane  napsal:
>
>> Pavel Stehule  writes:
>> > po 5. 10. 2020 v 15:56 odesílatel Thomas Kellerer 
>> napsal:
>> >> So instead of
>> >> make_interval ( [ year int [, month int [, week int [, day int [, hour
>> >> int [, min int [, sec double precision ]]] )
>> >> it should be
>> >> make_interval ( [ years int [, months int [, weeks int [, days int
>> >> [,hours int [, mins int [, secs double precision ]]] )
>>
>> Right, fixed.
>>
>> > this syntax is not correct too
>> > It should be
>> > make_interval( years int default 0, month int default 0, days int
>> > default 0, hours int default 0, secs double precision default 0)
>>
>> IIRC, I intentionally changed that in v13; the existence of the defaults
>> is sufficiently covered by the text "... fields, each of which can default
>> to zero".  I think that was partly motivated by trying to get the function
>> signature to fit into limited space.  The final docs-table design we ended
>> up with might allow undoing it, but I don't see any real reason to.  The
>> other way is more verbose and not any clearer.
>>
>
> I don't understand,
>
> the syntax [ a [, b]] means
>
> so a and b are optional, but b can be used only when a is used. But for
> make_interval I can use "months" arguments without specification of "years"
> argument.
>
> I don't know the correct BNF for arguments with default values, but using
> this doesn't look correct.
>

I forgot the behavior of positional  arguments. So this syntax is correct.
I am sorry for the noise.

Regards

Pavel


> Regards
>
> Pavel
>
>
>
>> I spent a little bit of time scanning for other discrepancies between
>> func.sgml and pg_proc.proargnames, and found several, mostly though
>> not exclusively in the JSON functions.  In these other cases, though,
>> I think there might be a good argument for making pg_proc fit the docs
>> not the other way around.  In the JSON functions, for example, pg_proc
>> randomly has some functions calling the main JSON[B] input "target"
>> while others call it "from_json" or "json_in".  I'm not real sure
>> which of those names is preferable, but inconsistency is not preferable.
>>
>> regards, tom lane
>>
>

Re: Change JOIN tutorial to focus more on explicit joins

[Pavel Stehule]

čt 22. 10. 2020 v 15:32 odesílatel Jürgen Purtz  napsal:

> On 22.10.20 01:40, David G. Johnston wrote:
>
> On Fri, Sep 4, 2020 at 2:36 AM Jürgen Purtz  wrote:
>
>> On 04.09.20 08:52, Peter Eisentraut wrote:
>> >
>> > For the remaining patch I have a couple of concerns:
>>
>
> This patch should not be changing the formatting choices for these
> queries, just the addition of a JOIN clause and modification of the WHERE
> clause.  Specifically, SELECT is left-aligned while all subsequent clauses
> indent under it.  Forced alignment by adding extra spaces isn't done here
> either.  I have not altered those in the attached.
>
> Did some word-smithing on the first paragraph.  The part about the
> cross-join was hurt by "in some way" and "may be" is not needed.
>
> Pointing out that values from both tables doesn't seem like an improvement
> when the second item covers that and it is more specific in noting that the
> city name that is joined on appears twice - once from each table.
>
> ON expression is more precise and the reader should be ok with the term.
>
> Removal of the exercise is good.  Not the time to discuss cross join
> anyway.  Given that "ON true" works the cross join form isn't even required.
>
> In the FROM clause form I would not add table prefixes to the column
> names.  They are not part of the form changing.  If discussion about table
> prefixing is desired it should be done explicitly and by itself.  They are
> used later on, I didn't check to see whether that was covered or might be
> confusing.
>
> I suggested a wording for why to use join syntax that doesn't involve
> legacy and points out its merit compared to sticking a join expression into
> the where clause.
>
> The original patch missed having the syntax for the first left outer join
> conform to the multi-line query writing standard you introduced.  I did not
> change.
>
> The "AND" ON clause should just go with (not changed):
>
> ON (w1.temp_lo < w2.temp_lo
> AND w1.temp_hi > w2.temp_high);
>
> Attaching my suggestions made on top of the attached original
> 0002-query.patch
>
> David J.
>
> (Hopefully) I have integrated all of David's suggestions as well as the
> following rules:
>
> - Syntax formatting with the previously used 4 spaces plus newline for JOIN
>
> - Table aliases only when necessary or explicitly discussed
>
> The discussion about the explicit vs. implicit syntax is added to the "As
> join expressions serve a specific purpose ... " sentence and creates a
> paragraph of its own.
>
> The patch is build on top of master.
>

Why do you use parenthesis for ON clause?  It is useless. SQL is not C or
JAVA.

Regards

Pavel

--
> J. Purtz
>
>
>

Re: Change JOIN tutorial to focus more on explicit joins

[Pavel Stehule]

čt 22. 10. 2020 v 18:27 odesílatel David G. Johnston <
david.g.johns...@gmail.com> napsal:

> On Thu, Oct 22, 2020 at 8:14 AM Pavel Stehule 
> wrote:
>
>> Why do you use parenthesis for ON clause?  It is useless. SQL is not C or
>> JAVA.
>>
>>
> At this point in my career it's just a personal habit.  I never programmed
> C, done most of my development in Java so maybe that's a subconscious
> influence?
>
> I suspect it is partly because I seldom need to use "ON" but instead join
> with "USING" which does require the parentheses, so when I need to use ON I
> just keep them.
>
> I agree they are unnecessary in the example and should be removed to be
> consistent.
>

:)



> David J.
>
>

Re: Change JOIN tutorial to focus more on explicit joins

[Pavel Stehule]

pá 23. 10. 2020 v 11:14 odesílatel Jürgen Purtz  napsal:

> On 22.10.20 17:14, Pavel Stehule wrote:
> >
> > Why do you use parenthesis for ON clause?  It is useless. SQL is not C
> > or JAVA.
>
>
> Two more general answers:
> - Why do people use tabs, spaces, and newlines to format their code even
> though it's not necessary? SQL is a language to develop applications.
> And what are the main costs of an application? It's not the time which
> it takes to develop them. It's the time for their maintenance. During
> the course of one or more decades, different persons will have to read
> the code, add additional features, and fix bugs. They need some time to
> read and understand the existing code. This task can be accelerated if
> the code is easy to read. Therefore, it's a good habit of developers to
> sometimes spend some extra characters to the code than is required -
> not only comments. An example: there are clear precedence rules for
> Boolean operators NOT/AND/OR. In an extensive statement it may be
> helpful - for the developer himself as well as for anybody else -to use
> newlines and parentheses at places where they are not necessary to keep
> an overview of the intention of the statement. In such cases,
> code-optimization is the duty of the compiler, not of the developer.
> - In my professional life as a software developer, I have seen about 15
> different languages. But only in rare cases, they have offered new
> features or concepts. To overcome this Babylonian linguistic diversity I
> tend to use such syntactical constructs which are common to many of them
> even, even if they are not necessary for the concrete language.
>
> And the concrete answer: Omitting the parentheses for the join condition
> raises the danger that its Boolean operators are mixed with the Boolean
> operators of the WHERE condition. The result at runtime is the same, but
> a reader will understand the intention of the statement faster if the
> parentheses exists.
>

I strongly disagree.

If there are some boolean predicates, then parenthesis has sense. Without
these predicates the parenthesis decrease readability. This is the sense of
JOIN syntax to separate predicates.

I have a different problem - when I see parentheses where they should not
be, I am searching for a reason, and It is unfriendly where there is not
any reason. I can understand if somebody uses useless parentheses in their
product, but we talk about official documentation, and then we should
respect the character of language.

Regards

Pavel



> --
>
> J. Purtz
>
>
>

Re: INNER JOIN syntax is not commonly used?

[Pavel Stehule]

ne 7. 3. 2021 v 12:45 odesílatel Vik Fearing 
napsal:

> On 3/7/21 10:33 AM, PG Doc comments form wrote:
> > The following documentation comment has been logged on the website:
> >
> > Page: https://www.postgresql.org/docs/13/tutorial-join.html
> > Description:
> >
> > This page shows a WHERE syntax to join tables.
> >
> > After that it shows an example of the INNER JOIN syntax:
> > SELECT *
> > FROM weather INNER JOIN cities ON (weather.city = cities.name);
> >
> > And after that it says the following:
> > "This syntax is not as commonly used as the one above, "
> >
> > So, this documentation claims that the INNER JOIN syntax is not as
> common as
> > WHERE syntax.
> > Is this really the case?
>
> I don't remember the last time I saw a new query use the  reference list> syntax.  In my experience, the  syntax is
> much more common.  I vote to change the wording on that page.
>

+1, modern syntax is more common today.


-- 
> Vik Fearing
>
>
>

Re: pl/pgsql errors when multi-dimensional arrays are used

[Pavel Stehule]

Hi

čt 29. 4. 2021 v 12:33 odesílatel Rafal Dabrowa 
napsal:

> This also returns ERROR:  invalid input syntax for type integer:
> "[221,222,223,224]"
>
> On 4/29/2021 9:48 AM, KraSer wrote:
> > try:
> > a.fld1[1] = '[221,222,223,224]';
> >
> > чт, 29 апр. 2021 г. в 10:35, PG Doc comments form
> > mailto:nore...@postgresql.org>>:
> >
> > The following documentation comment has been logged on the website:
> >
> > Page: https://www.postgresql.org/docs/13/arrays.html
> > Description:
> >
> > I have PostgreSQL 13. Let's declare the type below, then use it in
> > pl/pgsql:
> >
> > create type typ1 as (
> > fld1 int[][]
> > );
> >
> > do $$
> > declare
> >a typ1;
> > begin
> > a.fld1 = '{{121,122,123,124}}'; -- OK
> >  (1)
> > a.fld1[1] = '{221,222,223,224}';   -- fails
> > (2)
> > a.fld1[1][1] = 321;  -- OK
> >(3)
> > a.fld1[1][2] = 322;  -- OK unless line (1)
> > is removed
> > end;
> > $$;
> >
> > In line (2) the plql reports ERROR:  invalid input syntax for type
> > integer:
> > "{221,222,223,224}"
> > When lines (1) and (2) are removed, psql reports ERROR:  array
> > subscript out
> > of range
> >
> > Is this expected behavior? Why?
> >
> > Rafal
> >
>

 Postgres's arrays don't allow any modification that creates some gap in
the array. Next - Postgres's arrays are multidimensional arrays, and these
arrays are not an arrays or arrays.

So your line (1) cannot work.

You can write

a.fld1[1][1:4] :=  '{221,222,223,224}';

Case (3) fails, because this operation on empty array creates gap on
position 1,1.

you can use an function array_fill

DO
postgres=# do $$
declare a int[];
begin
  a := array_fill(null::int, array[2,2]);
  a[1][2] := 322;

  raise notice 'a=%', a;
end;
$$;
NOTICE:  a={{NULL,322},{NULL,NULL}}


Regards

Pavel

Re: getting table name from partition

[Pavel Stehule]

Hi

pá 30. 7. 2021 v 0:24 odesílatel Bruce Momjian  napsal:

> On Wed, Jul 14, 2021 at 01:45:12PM +, PG Doc comments form wrote:
> > The following documentation comment has been logged on the website:
> >
> > Page: https://www.postgresql.org/docs/13/ddl-partitioning.html
> > Description:
> >
> > I would like to add a sentence like this into the description of
> > partitions:
> >
> > You receive the name of the partition table data is from using
> > tableoid::regclass as column name in the select.
> >
> > to tell the option of getting "real" tablename, eg when partitions are
> based
> > on locality.
>
> We already have this sentence in the partition docs:
>
> Since a partition hierarchy consisting of the partitioned table
> and its partitions is still an inheritance hierarchy, all the
> normal
> rules of inheritance apply as described in Section 5.10, with a
> few exceptions:
>
> but it doesn't make it clear that tableoid, which is mentioned in
> Section 5.10 also works, so here is a patch to add a mention of
> tableoid.
>

I  think Michal's example is good and very practical, and although formally
it is documented well,
can be useful for a lot of people to see a practical example. This is very
nice usage of tableoid

Regards

Pavel




> --
>   Bruce Momjian  https://momjian.us
>   EDB  https://enterprisedb.com
>
>   If only the physical world exists, free will is an illusion.
>
>

Re: Array functions, array_length behavior given an empty array

[Pavel Stehule]

Hi

po 8. 11. 2021 v 13:48 odesílatel PG Doc comments form <
nore...@postgresql.org> napsal:

> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/13/functions-array.html
> Description:
>
> For whom it may concern,
>
> I'd like to propose an extension for `array_length` function description.
>
> Currently, it does not cover the case of an empty array:
>
> https://www.postgresql.org/docs/13/functions-array.html
> Table 9.52. Array Functions. Row 5
>
> The description reads as `Returns the length of the requested array
> dimension.`
>
> For me, an expected result for an empty array would be `0`, but it is
> `NULL`.
> Tested on `PostgreSQL 13.4` with query `SELECT
> ARRAY_LENGTH(ARRAY[]::CHARACTER VARYING[],1);`
>
> I believe, such case deserves to be mentioned in the description and/or in
> examples.
>

you can read related discussion
https://pgsql-hackers.postgresql.narkive.com/lZJQ64dp/array-length-anyarray

Use instead function cardinality

Regards

Pavel Stehule



>
> With best regards,
> Oleg Rekin
>

Re: Array functions, array_length behavior given an empty array

[Pavel Stehule]

po 8. 11. 2021 v 18:11 odesílatel David G. Johnston <
david.g.johns...@gmail.com> napsal:

> On Monday, November 8, 2021, Pavel Stehule 
> wrote:
>
>> Hi
>>
>> po 8. 11. 2021 v 13:48 odesílatel PG Doc comments form <
>> nore...@postgresql.org> napsal:
>>
>>> The following documentation comment has been logged on the website:
>>>
>>> I believe, such case deserves to be mentioned in the description and/or
>>> in
>>> examples.
>>>
>>
>> you can read related discussion
>> https://pgsql-hackers.postgresql.narkive.com/lZJQ64dp/array-length-anyarray
>>
>> Use instead function cardinality
>>
>
> I believe the OP has a point that we could do better with the
> documentation though.  Especially now that we’ve performed the function
> table reformatting.
>

+1

Pavel


> David J.
>

Re: Does postgres have Equivalent range C range Ty​pe for Built-in SQL range Types

[Pavel Stehule]

Hi


ne 28. 8. 2022 v 10:10 odesílatel jian he 
napsal:

>
> Equivalent C Types for Built-in SQL Types
> https://www.postgresql.org/docs/15/xfunc-c.html#XFUNC-C-TYPE-TABLE
> Does postgres have Equivalent  C range Type for Built-in SQL range Types?
>
>
No, there is only common ancestor - RangeType

Ranges are containers like arrays or records - so has not own type specific
type

Regards

Pavel


>  I recommend David Deutsch's <>
>
>   Jian
>
>
>

Re: Does postgres have Equivalent range C range Ty​pe for Built-in SQL range Types

[Pavel Stehule]

ne 28. 8. 2022 v 12:36 odesílatel Pavel Stehule 
napsal:

> Hi
>
>
> ne 28. 8. 2022 v 10:10 odesílatel jian he 
> napsal:
>
>>
>> Equivalent C Types for Built-in SQL Types
>> https://www.postgresql.org/docs/15/xfunc-c.html#XFUNC-C-TYPE-TABLE
>> Does postgres have Equivalent  C range Type for Built-in SQL range Types?
>>
>>
> No, there is only common ancestor - RangeType
>
> Ranges are containers like arrays or records - so has not own type
> specific type
>

look
https://github.com/postgres/postgres/blob/master/src/backend/utils/adt/rangetypes.c

>
> Regards
>
> Pavel
>
>
>>  I recommend David Deutsch's <>
>>
>>   Jian
>>
>>
>>

Re: Does postgres have Equivalent range C range Ty​pe for Built-in SQL range Types

[Pavel Stehule]

ne 28. 8. 2022 v 18:36 odesílatel jian he 
napsal:

>
>
> On Sun, Aug 28, 2022 at 4:07 PM Pavel Stehule 
> wrote:
>
>>
>>
>> ne 28. 8. 2022 v 12:36 odesílatel Pavel Stehule 
>> napsal:
>>
>>> Hi
>>>
>>>
>>> ne 28. 8. 2022 v 10:10 odesílatel jian he 
>>> napsal:
>>>
>>>>
>>>> Equivalent C Types for Built-in SQL Types
>>>> https://www.postgresql.org/docs/15/xfunc-c.html#XFUNC-C-TYPE-TABLE
>>>> Does postgres have Equivalent  C range Type for Built-in SQL range
>>>> Types?
>>>>
>>>>
>>> No, there is only common ancestor - RangeType
>>>
>>> Ranges are containers like arrays or records - so has not own type
>>> specific type
>>>
>>
>> look
>> https://github.com/postgres/postgres/blob/master/src/backend/utils/adt/rangetypes.c
>>
>>>
>>> Regards
>>>
>>> Pavel
>>>
>>>
>>>>  I recommend David Deutsch's <>
>>>>
>>>>   Jian
>>>>
>>>>
>>>>
> technically, Can i use jsonbor range  or array in C-language function?
> If that's possible, for range type  I need #include rangetypes.h in the c
> file(used to create c function) ?
>

Surely, you can. everything you can do in SQL you can do in C. It needs
much more very precious work, but the result can be significantly faster
(depends on context).

Regards

Pavel

Re: PL/pgSQL casing

[Pavel Stehule]

pá 2. 9. 2022 v 11:38 odesílatel Daniel Gustafsson  napsal:

> There are a few instances of PL/PgSQL in the docs, the attached changes the
> casing on those to PL/pgSQL which we use consistently otherwise.
>

+1

Pavel


> --
> Daniel Gustafsson   https://vmware.com/
>
>

Re: Mention the default io_method?

[Pavel Stehule]

čt 3. 4. 2025 v 13:26 odesílatel Daniel Westermann (DWE) <
daniel.westerm...@dbi-services.com> napsal:

> st 2. 4. 2025 v 12:28 odesílatel Daniel Westermann (DWE) <
> daniel.westerm...@dbi-services.com> napsal:
>
> Hi,
>
>
> https://www.postgresql.org/docs/devel/runtime-config-resource.html#GUC-IO-METHOD
>
> Shouldn't we mention that "worker" is the default?
>
>
> >+1
>
> >Pavel
>
> ... attached a small patch for this.
>

looks ok

Regards

Pavel

>
> Regards
> Daniel
>

Re: Mention the default io_method?

[Pavel Stehule]

st 2. 4. 2025 v 12:28 odesílatel Daniel Westermann (DWE) <
daniel.westerm...@dbi-services.com> napsal:

> Hi,
>
>
> https://www.postgresql.org/docs/devel/runtime-config-resource.html#GUC-IO-METHOD
>
> Shouldn't we mention that "worker" is the default?
>

+1

Pavel


> Regards
> Daniel
>
>