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.
>>
>>

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.

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".

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

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

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

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

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

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

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

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

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.

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

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

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.

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.

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;