"NewbieDoc Docbook Guide" link broken

[jian he]

hi.

in https://www.postgresql.org/docs/current/docguide-docbook.html
the link (https://newbiedoc.sourceforge.net/metadoc/docbook-guide.html)
for (NewbieDoc Docbook Guide) is broken.

google around I found this [2]
[2]: 
https://ftp.sun.ac.za/ftp/pub/documentation/newbiedoc/newbiedoc-html/docbook-guide.en/index-docbook-guide.html

Re: SEQUENCE keyword is option on GRANT

[jian he]

On Mon, Mar 31, 2025 at 9:55 PM David G. Johnston
 wrote:
>
>
> On Monday, March 31, 2025, PG Doc comments form  
> wrote:
>>
>> The following documentation comment has been logged on the website:
>>
>> Page: https://www.postgresql.org/docs/17/sql-grant.html
>> Description:
>>
>> The documentation indicates that they keyword SEQUENCE must precede the
>> sequence_name in a GRANT, but in my experience it is optional:
>>
>> colin@adinkra:~$ psql
>> psql (17.4 (Debian 17.4-1.pgdg120+2))
>> Type "help" for help.
>>
>> colin@[local]:colin=> create sequence x;
>> CREATE SEQUENCE
>> colin@[local]:colin=> grant select on x to public; -- should fail but
>> works
>>
>
> Sequences are relations (see pg_class) so the [TABLE] variant is able to 
> target them, which is what you’ve written here.
>

but

 ON { SEQUENCE sequence_name [, ...]

need change to

 ON { [SEQUENCE] sequence_name [, ...]

in grant.sgml

but play around more.

src6=# create table y();
src6=# create sequence ys;

grant select on table ys to public; --ok
grant usage on table ys to public; --ok
grant update on table ys to public; ---ok.

 grant insert  on table ys to public; ---ok.
WARNING:  sequence "ys" only supports USAGE, SELECT, and UPDATE privileges

grant update on sequence y to public;
ERROR:  "y" is not a sequence

grant insert  on sequence y to public;
ERROR:  invalid privilege type INSERT for sequence


so
GRANT { { USAGE | SELECT | UPDATE }
[, ...] | ALL [ PRIVILEGES ] }
ON { SEQUENCE sequence_name [, ...]

really should be

GRANT { { USAGE | SELECT | UPDATE }
[, ...] | ALL [ PRIVILEGES ] }
 ON { [{SEQUENCE | TABLE}] sequence_name [, ...]

Re: substring synopsis section, third argument is optional doc didn't show that

[jian he]

On Tue, Feb 18, 2025 at 7:06 AM David G. Johnston
 wrote:
>
> On Tue, Jan 21, 2025 at 11:29 PM jian he  wrote:
>>
>> in 9.7.2 do you think it's worthwhile changing it to
>>
>> ""
>> As with SIMILAR TO, substring(string, pattern, escape-character)
>> the specified pattern must match the entire data string, or else the
>> function fails and returns null.
>> ""
>> ?
>>
>
> Making reference to any one of the three listed function signatures here 
> doesn't seem to provide value.  If anything I'd write:
>
> "As with SIMILAR TO, substring matches the specified pattern to the entire 
> data string, returning null otherwise."
>
> I would avoid saying that the function fails in any situation that doesn't 
> produce an actual error.  The transition of "match everything or returns 
> null" can be bike-shedded though.
>

thinking about it.
I think the current wording
"As with SIMILAR TO, the specified pattern must match the entire data
string, or else the function fails and returns null"
is fine.

I guess my complaint is that the above sentence is not as explicit as
the 9.7.3 section description.
"
The substring function with two parameters, substring(string from
pattern), provides extraction of a substring that matches a POSIX
regular expression pattern.
It returns null if there is no match, otherwise the first portion of
the text that matched the pattern.
"

Re: substring synopsis section, third argument is optional doc didn't show that

[jian he]

On Wed, Jan 22, 2025 at 12:53 AM Tom Lane  wrote:
>
> jian he  writes:
> > https://www.postgresql.org/docs/current/functions-matching.html#FUNCTIONS-SIMILARTO-REGEXP
>
> > """
> > or as a plain three-argument function:
> > substring(string, pattern, escape-character)
> > """
>
> > but here "escape-character" is optional.
>
> > substring(string, pattern [,escape-character])
> > would be more accurate.
>
> No, the text is correct as written.  substring(text, text) is a
> completely different function that implements POSIX regular
> expressions, not SQL regular expressions.  It's described in
> the next section (9.7.3).  For example,
>
> regression=# select substring('foobar', 'o.b');
>  substring
> ---
>  oob
> (1 row)
>
> regression=# select substring('foobar', 'o.b', '');
>  substring
> ---
>
> (1 row)
>
> because '.' is a metacharacter in POSIX but not SQL regexps.
>

Thanks for the explanation.

in section 9.7.2,
substring(string, pattern, escape-character)
the pattern must match the entire data string. (SQL standard)

in section 9.7.3.
substring(string, pattern)
the pattern only needs part of the data string. (POSIX)

I think the above is the main/big difference?


in 9.7.2 do you think it's worthwhile changing it to
""
As with SIMILAR TO, substring(string, pattern, escape-character)
the specified pattern must match the entire data string, or else the
function fails and returns null.
""
?

substring synopsis section, third argument is optional doc didn't show that

[jian he]

hi.
https://www.postgresql.org/docs/current/functions-matching.html#FUNCTIONS-SIMILARTO-REGEXP

"""
or as a plain three-argument function:
substring(string, pattern, escape-character)
"""

but here "escape-character" is optional.


substring(string, pattern [,escape-character])
would be more accurate.
then we may also need to rephrase
"or as a plain three-argument function:"

Re: LC_COLLATE is visible in the documentation for PostgreSQL 17, but it is not supported

[jian he]

On Sat, Jan 4, 2025 at 11:53 PM PG Doc comments form
 wrote:
>
> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/17/sql-show.html
> Description:
>
> On the docs page for SHOW
> (https://www.postgresql.org/docs/17/sql-show.html), LC_COLLATE is listed as
> a parameter which can be shown, but trying to run SHOW LC_COLLATE on PG 17

i think, it was fixed in 2024-Dec-07 at
https://git.postgresql.org/cgit/postgresql.git/commit/?h=REL_17_STABLE&id=ad485171c703915b09a9f498fad69e89f75c9568

https://www.postgresql.org/docs/17/sql-show.html will be updated in
the next minor release, I think.

restart-footnote-numbering-in-each-sect1

[jian he]

hi.
https://www.postgresql.org/docs/current/tutorial-window.html#id-1.4.5.6.9.5
"""
[5] There are options to define the window frame in other ways, but
this tutorial does not cover them. See Section 4.2.8 for details.
"""

Why is "[5]"?
I think generally we want to restart the footnote numbering in each sect1?

related post I found:
https://stackoverflow.com/questions/8520254/restart-footnote-numbering-in-each-sect1-in-docbook

Re: Document how to run only specific regress tests

[jian he]

hi.

maybe verbose, the following is the my intended changes based on your patch.
what do you think?


  
   Running Specific Tests
   
  You can run a subset of the regression tests through the TESTS
  environment variable.
  One way to do it is set TESTS environment variable beforehand:

export TESTS="test_setup boolean char"

You can also specify it while invoking the regress tests command,
one of example:

make check-tests TESTS="test_setup boolean char"

If the TESTS environment variable is set when the regression
tests are run, it overrides the original TESTS environment
variable.


All the regression tests are stored in src/test/regress/sql.  Each
individual regression test
name is the corresponding file name omit the file name extension
(.sql).  For example, the regression test name of
src/test/regress/sql/partition_join.sql is
partition_join.  Tests specified by the
TESTS
environment variable will be run in a sequential order.
TESTS="test_setup copy'  will first run
test_setup then copy.  Note that
sometimes one test may depend on other tests, so you might get unexpected
failures if the dependent test is not specified.  For example, to run
select_parallel test, to avoid failure, you can use

make check-tests TESTS="test_setup create_misc create_index select_parallel"

   
  

Re: Tip box on Adding a Column

[jian he]

On Fri, Nov 1, 2024 at 5:06 PM Marcelo Fernandes  wrote:
>
> Hi folks,
>
> We have this Tip box under the "Adding a Column" header here:
>
> - 
> https://www.postgresql.org/docs/current/ddl-alter.html#DDL-ALTER-ADDING-A-COLUMN
>
> That says:
>
> > From PostgreSQL 11, adding a column with a constant default value no longer
> > means that each row of the table needs to be updated when the ALTER TABLE
> > statement is executed. Instead, the default value will be returned the next
> > time the row is accessed, and applied when the table is rewritten, making 
> > the
> > ALTER TABLE very fast even on large tables.
>
> I'm just seeking clarification if this advice is true **even for** new columns
> declared with NOT NULL?
>
you can use event_trigger to test it.

https://www.postgresql.org/docs/current/event-trigger-definition.html

The table_rewrite event occurs just before a table is rewritten by
some actions of the commands ALTER TABLE and ALTER TYPE. While other
control statements are available to rewrite a table, like
CLUSTER and VACUUM, the table_rewrite event is not triggered by them.

---
following tests copy from
https://github.com/postgres/postgres/blob/d893a299ce68f56522073a1b43d65764a552ae0d/src/test/regress/sql/fast_default.sql#L47


CREATE OR REPLACE FUNCTION log_rewrite() RETURNS event_trigger
LANGUAGE plpgsql as
$func$
declare
   this_schema text;
begin
RAISE NOTICE 'rewriting table % for reason %',
pg_event_trigger_table_rewrite_oid()::regclass,
pg_event_trigger_table_rewrite_reason();
end;
$func$;
CREATE EVENT TRIGGER has_rewrite ON table_rewrite EXECUTE PROCEDURE
log_rewrite();

create table t1(a int);
insert into t1 select 1;
alter table t1 add column b double precision not null default 11;
alter table t1 add column c double precision not null default random();
alter table t1 add column d timestamptz not null default now();
alter table t1 add column e timestamptz not null default current_timestamp;
alter table t1 ADD column f int DEFAULT (random() * 1)::int;
alter table t1 ADD column g int not null DEFAULT (random() * 1)::int;

Re: Document how to run only specific regress tests

[jian he]

On Tue, Oct 29, 2024 at 12:40 PM Paul A Jungwirth
 wrote:
>
> I never knew this was possible, but I ran across a note on the meson
> wiki page[1]. I wish I knew about it years ago!
>
> This patch adds a note to
> https://www.postgresql.org/docs/current/regress-run.html so that other
> hackers and potential hackers will know about it.
>
> [1] https://wiki.postgresql.org/wiki/Meson

Thanks for your patch!
you can also check meson TESTS patch in
https://postgr.es/m/CAN55FZ3dSdvujT8yup1C=yoftatrbrwclthmtxupcbog7va...@mail.gmail.com

Since this is mostly for postgres development, slightly expanding it
would be better.
I think we can also mention that:
1. all the available regress tests are on src/test/regress/sql.
2. regress test name is the same as these files omit file format.
For example "src/test/regress/sql/partition_join.sql" corresponding
regression test name is partition_join
3. individual tests will be executed in that specific order.
TESTS="test_setup copy'
will first execute 'test_setup' then 'copy'
4. Many tests depend on test_setup, some tests depend on create_index.
5. specify duplicate tests may make the tests fail.

SET NAMES value value should be single quoted

[jian he]

NAMES
SET NAMES value is an alias for SET client_encoding TO value.

it should be:
SET NAMES 'value' is an alias for SET client_encoding TO value.


https://www.postgresql.org/docs/current/sql-set.html

Re: Undocumented := alternative in using option of raise statement

[jian he]

On Mon, Jul 22, 2024 at 8:38 PM PG Doc comments form
 wrote:
>
> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/16/plpgsql-errors-and-messages.html
> Description:
>
> The using option is documented as follows for all variants of the raise
> statement:
>
> USING option = expression [, ... ]
>
> However, since "do $$ begin raise notice using message := 'foo'; end $$;"
> works and the alternative to "=" is documented in
> https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-ASSIGNMENT
> I suggest to change the grammar to
>
> USING option { := | = } expression [, ... ]
>
> for the raise statement.

I think this has been committed.
https://git.postgresql.org/cgit/postgresql.git/commit/?id=56c6be57af6bd1c7eb7dff50e5f169ced4ed3045

you can check latest doc at:
https://www.postgresql.org/docs/devel/plpgsql-errors-and-messages.html

Re: Add small detail to RAISE statement descripton

[jian he]

On Wed, May 22, 2024 at 11:35 PM Igor Gnatyuk  wrote:
>
> Hi,
>
> thanks for the response and for your corrections to the patch.
> But I checked my changes by running make html, make postgres-A4.pdf,
> make postgres-US.pdf and viewing
> the results: everything is OK.
>
> Message
> 'old mode 100644
> new mode 100755'
> this means, imho, that access rights were changed during changes to
> the file. I fixed it in the patch
> fix_doc_raise_v3-bios.patch. Please check it out.
>

looks good to me.

Re: Add small detail to RAISE statement descripton

[jian he]

On Wed, May 22, 2024 at 4:18 PM Igor Gnatyuk  wrote:
>
> Hi.
>
> Thanks for the comments. I fixed my mistakes by specifying sqlcode
> instead of sqlstate and changed the phrase
> with an explicit listing of options in USING. In addition, I moved the
> paragraph with it below.
> Please check out the new patch version in the attachment.
>

after git apply

jian@jian:~/Desktop/pg_src/src7/postgres$ git apply
$PATCHES/fix_doc_raise_v3.patch
jian@jian:~/Desktop/pg_src/src7/postgres$ git diff
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
old mode 100644
new mode 100755

-
i guess, that means your patch (fix_doc_raise_v3) has some structural problem.

so I wrote it based on fix_doc_raise_v2.patch.
please check attached.


Feel free to change it.
From 28f32f22095b262654ab204327678c7f027a5d36 Mon Sep 17 00:00:00 2001
From: jian he 
Date: Wed, 22 May 2024 19:07:26 +0800
Subject: [PATCH v4 1/1] fix plpgsql raise command doc issue.

1. fix plpgsql raise command synopsis.
2. explain condition_name
 and sqlstate,
 also add a example for it.
---
 doc/src/sgml/plpgsql.sgml | 29 -
 1 file changed, 24 insertions(+), 5 deletions(-)

diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index 6f880b70..e48e9d10 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -3805,10 +3805,10 @@ CALL transaction_test2();
 raise errors.
 
 
-RAISE  level  'format' , expression , ...   USING option = expression , ...  ;
-RAISE  level  condition_name  USING option = expression , ...  ;
-RAISE  level  SQLSTATE 'sqlstate'  USING option = expression , ...  ;
-RAISE  level  USING option = expression , ... ;
+RAISE  level  'format' , expression , ...   USING option { = | := } expression , ...  ;
+RAISE  level  condition_name  USING option { = | := } expression , ...  ;
+RAISE  level  SQLSTATE 'sqlstate'  USING option { = | := } expression , ...  ;
+RAISE  level  USING option { = | := } expression , ... ;
 RAISE ;
 
 
@@ -3852,11 +3852,30 @@ RAISE NOTICE 'Calling cs_create_job(%)', v_job_id;
 

 
+   
+The condition_name and
+sqlstate specifies
+error condition name and the five-character SQLSTATE error code respectively.
+See  for more information.
+   
+
+   
+The examples below shows condition_name
+and sqlstate options usage:
+
+RAISE division_by_zero;
+RAISE SQLSTATE '22012';
+
+   
+

 You can attach additional information to the error report by writing
 USING followed by option = expression items.  Each
+class="parameter">expression or
+option :=
+expression
+items, where
 expression can be any
 string-valued expression.  The allowed option key words are:
-- 
2.34.1

Re: Add small detail to RAISE statement descripton

[jian he]

On Fri, May 17, 2024 at 4:39 PM Igor Gnatyuk  wrote:
>
> Hi.
>
> Thank you for your letter. I tried to take your comments into account:
> I added explanations to the condition_name, sqlstate options and
> slightly changed
> the explanations for USING. Attached you will find new patch version.
> Thank you for your help.
>
> Ragards, Igor Gnatyuk

 there occurrence of sqlcode:
+ sqlcode
should be:
 sqlstate


+
+ In the syntax of RAISE command with the
+ condition_name or
+ sqlcode options
+ USING clause can be used to supply a custom
error message,
+ detail, or hint. A variation of the example above:
+ 
+ RAISE unique_violation USING MESSAGE = 'Duplicate user ID: ' || user_id;
+ 
+

>>  options
>> USING clause can be used to supply a custom error 
>> message,
>> detail, or hint.

"options" should be "optional"?
I am not sure we need to explicitly say, "error message, error detail,
error hint"?

Re: Add small detail to RAISE statement descripton

[jian he]

On Tue, May 14, 2024 at 11:09 PM Igor Gnatyuk  wrote:
>
> Hi,
>
> There is a slight syntax inaccuracy in the description of the RAISE
> statement - assignments of parameter values in USING.
> (Chapter 43. PL/pgSQL — SQL Procedural Language / 43.9 Errors and
> Messages / 43.9.1 Reporting Errors and Messages):
>
> "...You can attach additional information to the error report by
> writing USING followed by option = expression items..."
> It should, apparently, be like this: "...option { = | := } expression..."
>
> The patch corrects this little omission. Attached: fix_doc_raise.patch
>
> Regards, Igor Gnatyuk


while at it,
I found out there is no brief explanation of:
condition_name
and
sqlstate
should we add it?

one more minor thing.

You can attach additional information to the error report by writing
USING followed by option { = | := } expression items.  Each

we are not in , maybe

You can attach additional information to the error report by writing
USING followed by option = expression or
 option :=
expression
items

will make it more clear.

Re: A typo?

[jian he]

On Sun, Apr 7, 2024 at 6:30 PM PG Doc comments form
 wrote:
>
> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/16/plpgsql-declarations.html
> Description:
>
> Under 43.3.1, "Notice that we omitted RETURNS real — we could have included
> it, but it would be redundant."
> Should that be "RETURNS tax" instead of "RETURNS real"?

I think it's related to the plpgsql "RETURNS" and "RETURN" confusion.
RETURN  can appear between "begin", "end".
RETURNS need to specify before "AS".

not omit "RETURNS real" would be:

CREATE FUNCTION sales_tax(subtotal real,  OUT tax real) returns real AS $$
BEGIN
tax := subtotal * 0.06;
END;
$$ LANGUAGE plpgsql;

Re: create table explicitly mention that unique|primary key constraint will create an

[jian he]

On Mon, Nov 27, 2023 at 10:30 AM Laurenz Albe  wrote:
>
> What do you think of the attached patch?
>
> Yours,
> Laurenz Albe

looks good to me.

create table explicitly mention that unique|primary key constraint will create an

[jian he]

Hi. minor doc issue.
create table s1(a int, constraint s2 PRIMARY key (a));
create table s2(a int);
ERROR:  relation "s2" already exists

https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-PARMS-UNIQUE
maybe for the following 2 sentence
"Adding a unique constraint will automatically create a unique btree
index on the column or group of columns used in the constraint."
"Adding a PRIMARY KEY constraint will automatically create a unique
btree index on the column or group of columns used in the constraint."

maybe we can mention that: the unique btree index name will be the
constraint name.
also is "a unique" or "an unique"?
I personally thought this part is obscure.

doc pg_describe_object description add a url link to pg_depend catalog.

[jian he]

Hi.
made some changes in the pg_describe_object function description section.

add a link to pg_depend.

-pg_depend catalog. This function returns
+pg_depend
catalog. This function returns
From 84c5ed57a270a5d916e89b747711a05daebd3dfa Mon Sep 17 00:00:00 2001
From: pgaddict 
Date: Sun, 22 Oct 2023 12:39:59 +0800
Subject: [PATCH v1 1/1]  add pg_depend url link in pg_describe_object's
 description.

---
 doc/src/sgml/func.sgml | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 24ad87f9..64990af7 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -25141,7 +25141,7 @@ SELECT collation for ('foo' COLLATE "de_DE");
 object).  This description is intended to be human-readable, and might
 be translated, depending on server configuration.  This is especially
 useful to determine the identity of an object referenced in the
-pg_depend catalog. This function returns
+pg_depend catalog. This function returns
 NULL values for undefined objects.

   
-- 
2.34.1

opclass. See below for details. cannot found the "below".

[jian he]

https://www.postgresql.org/docs/current/sql-createindex.html
<<
opclass
The name of an operator class. See below for details.
<<

there is no "below"?
Browser search, there are only four appearances of keyword "opclass".

-- 
 I recommend David Deutsch's <>

  Jian

Re: No explanation of other options

[jian he]

On Tue, Oct 17, 2023 at 7:00 PM PG Doc comments form
 wrote:
>
> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/16/sql-move.html
> Description:
>
> A few more examples with the other options would be helpful.
>
> MOVE LAST for example shows NULL,
> What does MOVE ALL do?

I found out demo in
https://git.postgresql.org/cgit/postgresql.git/tree/src/test/regress/expected/subselect.out#n1665

You can try it:
begin;
declare c1 scroll cursor for
 select * from generate_series(1,4) i
  where i <> all (values (2),(3));
move forward all in c1;
fetch  all in c1;
commit;

move the default direction is forward. so "move all" will act the same
as "move forward all".

Re: Bug in documentation: https://www.postgresql.org/docs/current/spi-examples.html

[jian he]

On Tue, Jul 18, 2023 at 8:26 AM David G. Johnston
 wrote:
>
> No INFO messages appear because you did not include a returning clause.  The 
> 1 you passed to the call is immaterial if the query you supply doesn't 
> produce a result set.
>
> David J.
>

indeed. https://www.postgresql.org/docs/current/spi-spi-execute.html


-- 
 I recommend David Deutsch's <>

  Jian

Re: Confusing reference to MERGE use inside WITH

[jian he]

On Sun, Jul 16, 2023 at 8:48 PM PG Doc comments form
 wrote:
>
> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/15/queries-with.html
> Description:
>
> Hi.
>
> In the queries WITH documentation
> https://www.postgresql.org/docs/15/queries-with.html it is a bit confusing
> that MERGE is referenced in several places as being something you can use
> inside with, even though it can't.
>
> If you search for MERGE on the page, you will find the 2 first results is a
> reference saying MERGE can be used inside WITH, the same as SELECT, INSERT,
> UPDATE, or DELETE. The 3rd result then correctly states that MERGE cannot be
> used inside WITH.
>
> If I'm misunderstanding the meaning of the first 2 results, then I apologise
> :)
>
> Greetings
> Rasmus Porsager


I agree. The second one is kind of confusing.

> WITH w AS (
> SELECT key, very_expensive_function(val) as f FROM some_table
> )
> SELECT * FROM w AS w1 JOIN w AS w2 ON w1.f = w2.f;
> Here, materialization of the WITH query ensures that very_expensive_function 
> is evaluated only once per table row, not twice.
> The examples above only show WITH being used with SELECT, but it can be 
> attached in the same way to INSERT, UPDATE, DELETE, > or MERGE.

There are two "SELECT".   " only show WITH being used with SELECT",
not mention of "SELECT" refer to the auxiliary statements or primary
statements.


-- 
 I recommend David Deutsch's <>

  Jian

9.7. Pattern Matching only a few links.

[jian he]

https://www.postgresql.org/docs/current/functions-matching.html
9.7. Pattern Matching is around 20 A4 pages, but there are only a few
links.

It would be better to have more links to some common functions like
regexp_replace.

wiki postgresql Window_function some todo items is done?

[jian he]

Hi,

https://wiki.postgresql.org/wiki/Todo#Window_Functions


Implement full support for window framing clauses

In addition to done clauses described in the latest doc
> ,
> these clauses are not implemented yet.
>
>- RANGE BETWEEN ... PRECEDING/FOLLOWING
>- EXCLUDE
>
>

I think this TODO item is done?
aslo
The "latest doc" binded link (
developer.postgresql.org/pgdocs/postgres/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS)
is not working.

-- 
 I recommend David Deutsch's <>

  Jian

pg_relation_is_updatable, pg_column_is_updatable not documented

[jian he]

Hi,

seems pg_column_is_updatable, pg_relation_is_updatable not documented.

example:
create view test_v_tenk1 AS
select abs(unique1),unique1 from tenk1;
select pg_catalog.pg_column_is_updatable('test_v_tenk1'::regclass,
2::smallint, false)
select events & 4 != 0 AS upd,
   events & 8 != 0 AS ins,
   events & 16 != 0 AS del
from pg_catalog.pg_relation_is_updatable('test_v_tenk1'::regclass, true)
t(events);
---

I am not sure what the third parameter is doing in pg_column_is_updatable.
True or false. The result is the same.
similarly, pg_relation_is_updatable, the second parameter true or false the
result is the same.




-- 
 I recommend David Deutsch's <>

  Jian

https://wiki.postgresql.org/wiki/Meson add info about meson and ninja required version.

[jian he]

Hi, I'm trying to build with meson.
---
rm -rf builddir
meson setup \
--prefix=/usr/local/pg_master \
-Ddebug=true \
--bindir=/usr/local/pg_master \
--datadir=/usr/local/pg_master \
-Dpgport=1600 \
builddir
cd builddir
ninja
ninja install
---
--error part-
builddir$ninja install
[0/1] Installing files.
Installing subdir
/home/jian/Desktop/pg_sources/main/postgres/src/include/access to
/usr/local/pg_master/include/postgresql/server/access
Installation failed due to insufficient permissions.
Attempting to use polkit to gain elevated privileges...

ERROR: Build directory has been generated with Meson version 0.63.2, which
is incompatible with the current version 0.61.2.
FAILED: meson-internal__install
/home/jian/.local/bin/meson install --no-rebuild
ninja: build stopped: subcommand failed.

--
Quote from meson.build:

>   # We want < 0.56 for python 3.5 compatibility on old platforms. EPEL for
>   # RHEL 7 has 0.55. < 0.54 would require replacing some uses of the fs
>   # module, < 0.53 all uses of fs. So far there's no need to go to >=0.56.
>   meson_version: '>=0.54',
>

Well the error is quite intuitive. Then I tried to install meson 0.61.2.
Then all things work as intended.
would be better mentioned the meson and ninja required version in
https://wiki.postgresql.org/wiki/Meson


-- 
 I recommend David Deutsch's <>

  Jian

sql-select.html ordinal number of an output column.

[jian he]

quote from https://www.postgresql.org/docs/current/sql-select.html

The elements of the PARTITION BY list are interpreted in much the same
> fashion as elements of a GROUP BY
> 
> clause, except that they are always simple expressions and never the name
> or number of an output column.
>


I think "number of an output column" refers to "ordinal number of an output
column".


-- 
 I recommend David Deutsch's <>

  Jian

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

[jian he]

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) ?

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

[jian he]

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?


-- 
 I recommend David Deutsch's <>

  Jian

Re: pg_stat_database view column xact_commit description should be more descriptive?

[jian he]

Sorry.
It refers to all the transactions.

On Fri, Jun 3, 2022 at 12:30 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:

>
>
> On Thursday, June 2, 2022, jian he  wrote:
>
>>
>>
>> https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-DATABASE-VIEW
>>
>>> xact_commit bigint
>>>
>>> Number of transactions in this database that have been committed
>>>
>> In https://www.postgresql.org/docs/current/sql-begin.html
>>
>> BEGIN initiates a transaction block, that is, all statements after a
>>> BEGIN command will be executed in a single transaction until an
>>> explicit COMMIT
>>> <https://www.postgresql.org/docs/current/sql-commit.html> or ROLLBACK
>>> <https://www.postgresql.org/docs/current/sql-rollback.html> is given. By
>>> default (without BEGIN), PostgreSQL executes transactions in “autocommit
>>> ” mode, that is, each statement is executed in its own transaction and
>>> a commit is implicitly performed at the end of the statement (if
>>> execution was successful, otherwise a rollback is done).
>>>
>>
>> I guess the pg_stat_database view column *xact_commit *refers to
>> 'non-autocommit' transactions?
>> If so, should we say something like "Number of  autocommit
>> transactions"
>>
>
> My guess is that it doesn’t matter if it’s implicit or explicit and thus
> the documentation is correct and adequate.  It does seem easy enough to
> prove one way or the other if you think it might be incorrect and thus to
> warrant a change to the docs.   If it does vary I’d have reason to suspect
> that a pure select query would exhibit different behavior than an insert or
> delete query - i.e., whether a new xid is issued makes a difference.
>
> I may experiment myself when I’m back at a computer but as you are raising
> the potential issue the research seems like something that should be done
> to support the suggestion.  It isn’t like this will require source code
> reading to discern.
>
> David J.
>
>

-- 
 I recommend David Deutsch's <>

  Jian

pg_stat_database view column xact_commit description should be more descriptive?

[jian he]

https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-DATABASE-VIEW

> xact_commit bigint
>
> Number of transactions in this database that have been committed
>
In https://www.postgresql.org/docs/current/sql-begin.html

BEGIN initiates a transaction block, that is, all statements after a BEGIN
> command will be executed in a single transaction until an explicit COMMIT
>  or ROLLBACK
>  is given. By
> default (without BEGIN), PostgreSQL executes transactions in “autocommit”
> mode, that is, each statement is executed in its own transaction and a
> commit is implicitly performed at the end of the statement (if execution
> was successful, otherwise a rollback is done).
>

I guess the pg_stat_database view column *xact_commit *refers to
'non-autocommit' transactions?
If so, should we say something like "Number of  autocommit
transactions"

-- 
 I recommend David Deutsch's <>

  Jian

Re: CREATE EXTENSION not adding extension on second SCHEMA

[jian he]

quote from manual:

> CREATE EXTENSION loads a new extension into the current database. There
> must not be an extension of the same name already loaded.
>

You can try to alter[1] the extension to make it located to another schema.

[1]: https://www.postgresql.org/docs/14/sql-alterextension.html


On Tue, May 31, 2022 at 8:39 PM PG Doc comments form 
wrote:

> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/14/sql-createextension.html
> Description:
>
> I'm trying to add an extension to two schemas that I have in my DB.
>
> steps:
>
> 1)
>
> CREATE EXTENSION unaccent with SCHEMA public;
>
> response: OK.
>
> 2)
>
> CREATE EXTENSION unaccent with SCHEMA public_test;
>
> response: SQL Error [42710]: ERROR: extension "unaccent" already exists
>
> I tryed:
>
> SET search_path = public_test;
> CREATE EXTENSION unaccent;
>
>  and the response is: response: SQL Error [42710]: ERROR: extension
> "unaccent" already exists
>


-- 
 I recommend David Deutsch's <>

  Jian

collation.html natural sort

[jian he]

https://www.postgresql.org/docs/15/collation.html

> Numeric ordering, sorts sequences of digits by their numeric value, for
> example: A-21 < A-123 (also known as natural sort).
>

I guess maybe it should be  'A-21' < 'A-123' ?


-- 
 I recommend David Deutsch's <>

  Jian

43.10 Trigger Functions one sentences kind of not easy to understand.

[Jian He]

https://www.postgresql.org/docs/current/plpgsql-trigger.html

>
> Row-level triggers fired BEFORE can return null to signal the trigger
> manager to skip the rest of the operation for this row (i.e., subsequent
> triggers are not fired, and the INSERT/UPDATE/DELETE does not occur for
> this row). If a nonnull value is returned then the operation proceeds with
> that row value. *Returning a row value different from the original value
> of NEW alters the row that will be inserted or updated. *Thus, if the
> trigger function wants the triggering action to succeed normally without
> altering the row value, NEW (or a value equal thereto) has to be
> returned. To alter the row to be stored, it is possible to replace single
> values directly in NEW and return the modified NEW, or to build a
> complete new record/row to return. In the case of a before-trigger on
> DELETE, the returned value has no direct effect, but it has to be nonnull
> to allow the trigger action to proceed. Note that NEW is null in DELETE
> triggers, so returning that is usually not sensible. The usual idiom in
> DELETE triggers is to return OLD.
>

I just want confirm the highlighted sentence  is equivalent as the
following sql code:

begin;
> CREATE TABLE documents (
> docdesc text,
> misc text,
> modification_time timestamp with time zone DEFAULT now()
> );
> CREATE FUNCTION documents_update_mod_time() RETURNS trigger
> AS $$
> begin
> new.modification_time := now();
>
return new;
>
end
> $$
> LANGUAGE plpgsql;
> CREATE TRIGGER documents_modification_time
> BEFORE INSERT OR UPDATE ON documents
> FOR EACH ROW
> EXECUTE PROCEDURE documents_update_mod_time();
> commit;
>

as a non-native english speaker, I kind of feel this sentence quite hard to
understand

stxkind only explain two elements. left two unexplained.

[Jian He]

PostgreSQL: Documentation: 10: 51.51. pg_statistic_ext


> stxkind : An array containing codes for the enabled statistics kinds;
> valid values are: d for n-distinct statistics, f for functional
> dependency statistics


select distinct stxkind from pg_statistic_ext ;
return:

 stxkind
> -
>  {d}
>  {m}
>  {f}
>  {e}
> (4 rows)


 I guess *e *for expression statistics. Since I create the following
statistics object.

create statistics flight_expr1 on(
extract
(month from scheduled_departure at time zone 'Asia/Kolkata')
) from flights;

select stxkind from pg_statistic_ext where stxname =  'flight_expr';

and it return *e.*
But I am not sure what does *m *mean?

It would be better to full explain all these four elements in the doc.

list all the Boolean flags in pg_class that are maintained lazily.

[Jian He]

PostgreSQL: Documentation: 14: 52.11. pg_class


> Several of the Boolean flags in pg_class are maintained lazily: they are
> guaranteed to be true if that's the correct state, but may not be reset to
> false immediately when the condition is no longer true. For example,
> relhasindex is set by CREATE INDEX
> , but it is
> never cleared by DROP INDEX
> . Instead,
> VACUUM  clears
> relhasindex if it finds the table has no indexes. This arrangement avoids
> race conditions and improves concurrency.


9 boolean flags, 1 already mentioned  maintained lazily.
Is possible to list all the flags that maintained  lazily. or just mention This
is only an estimate like *relpage *flags in the same page.

range data type size not mention in docs.

[Jian He]

range type seem not mention their size in the docs.
multi range size is variable.
But range size seems fixed.
why it's 17 byte?

select  pg_column_size('[10,20)' ::int4range ); --17 byte
select  pg_column_size('[-2147483648,2147483647)' ::int4range ); --17 byte.

Re: Data Type Size Calculation

[Jian He]

https://www.depesz.com/2022/02/13/how-much-disk-space-you-can-save-by-using-int4-int-instead-of-int8-bigint/

Hope this link is useful.


 create table testb as select 'true'::bool as b from
generate_series(1,100) i;SELECT 100

$ \dt+ testb
  List of relations
 Schema │ Name  │ Type  │ Owner  │ Persistence │ Access method │ Size
│ Description
┼───┼───┼┼─┼───┼───┼─
 public │ testb │ table │ depesz │ permanent   │ heap  │ 35 MB │


Why is that, though? From what I gather the answer is: performance. I don't
> know low-level details, but based on what I understand, processors process
> data in arch-dependent block sizes. 64bit processor works on 64 bits. And
> this means that if you want to do something on int4 value, that is part of
> 8 byte block, you have to add operation to zero the other 32 bits.
>

On Wed, Feb 16, 2022 at 12:26 AM Troy Frericks 
wrote:

> For now, yes... I'm suggesting that the documentation be completed by
> adding a few sentences few extra sentences.
> Troy.
> #
>
>
> On Mon, Feb 14, 2022, 12:51 Bruce Momjian  wrote:
>
>> On Fri, Feb 11, 2022 at 08:12:08PM +, PG Doc comments form wrote:
>> > The following documentation comment has been logged on the website:
>> >
>> > Page: https://www.postgresql.org/docs/13/datatype-numeric.html
>> > Description:
>> >
>> > > The actual storage requirement is two bytes for each group of four
>> decimal
>> > digits, plus three to eight bytes overhead.
>> >
>> > Please describe what 'overhead' means.
>> >
>> > I'd like to be able to calculate the data size of NUMBER(19,4). I can
>> > calculate 2 bytes per 4 digits... with 19 digits, I have 5 groups of 4
>> > digits,
>> >
>> > so the data length I seek is 5 bytes + overhead... then I'm left
>> hanging.
>> > :(
>>
>> Well, you can create it and then call pg_column_size():
>>
>> CREATE TABLE test (x NUMERIC(19,4));
>>
>> SELECT pg_column_size('test.x');
>>  pg_column_size
>> 
>>   7
>>
>> If you want more details, you will need to look at the source code.
>>
>> --
>>   Bruce Momjian  https://momjian.us
>>   EDB  https://enterprisedb.com
>>
>>   If only the physical world exists, free will is an illusion.
>>
>>