Re: Empty materialized view

[Adrian Klaver]

On 3/24/24 14:27, Thiemo Kellner wrote:

Feeling quite dumb now. But then, there neither is data visible in 
the install session.


insert data into TASK_DEPENDENCY⠒V
INSERT 0 34


The above says the data was inserted.


But not into the MV but into TASK_DEPENDENCY⠒V.


Where and when was the count query run?






Excerpt of the according protocol:

## tenth level ##
Set materialised view QUERY_PER_TASK⠒MV up
psql:views/QUERY_PER_TASK⠒MV.pg_sql:3: HINWEIS:  materialisierte Sicht 
»query_per_task⠒mv« existiert nicht, wird übersprungen

DROP MATERIALIZED VIEW
SELECT 0
REFRESH MATERIALIZED VIEW
COMMENT
COMMIT


At above you have not entered the data into the tables the MV depends on 
so SELECT 0 is reasonable.



# insert data #
## first level ##
insert data into CENTRICITY
INSERT 0 2
COMMIT
insert data into DIRECTION
INSERT 0 8
COMMIT
insert data into GOOD_CLASS
INSERT 0 15
COMMIT
insert data into NODE_TYPE
INSERT 0 3
COMMIT
insert data into REGION
INSERT 0 15
COMMIT
insert data into TASK_TYPE
INSERT 0 5
COMMIT
## second level ##
insert data into AREA
INSERT 0 16
COMMIT
insert data into DISTANCE⠒V
INSERT 0 3
COMMIT
insert data into GOOD⠒V
INSERT 0 164
COMMIT
insert data into MAP⠒V
INSERT 0 41
COMMIT
## third level ##
insert data into DIRECT_NEIGHBOUR
INSERT 0 8
INSERT 0 16
COMMIT
### Scandinavia ###
insert data into NODE⠒V
INSERT 0 112
COMMIT
insert data into PRODUCTION⠒V
INSERT 0 11
COMMIT
insert data into TASK⠒V
INSERT 0 56
COMMIT
## forth level ##
Scandinavia
insert data into DROP_OFF⠒V
INSERT 0 91
COMMIT
insert data into PICK_UP⠒V
INSERT 0 73
COMMIT
insert data into TASK_DEPENDENCY⠒V
INSERT 0 34
COMMIT
  count
---
     66
(1 row)

  count
---
  0
(1 row)


The 0 count above represents the below correct? :

select count(*) from SNOWRUNNER.QUERY_PER_TASK⠒MV;

If so, again that is reasonable as I don't see anywhere you refresh 
QUERY_PER_TASK⠒MV after the underlying tables have data entered. At this 
point it is still at the state you left it at here:


## tenth level ##
Set materialised view QUERY_PER_TASK⠒MV up
psql:views/QUERY_PER_TASK⠒MV.pg_sql:3: HINWEIS:  materialisierte Sicht 
»query_per_task⠒mv« existiert nicht, wird übersprungen

DROP MATERIALIZED VIEW
SELECT 0
REFRESH MATERIALIZED VIEW
COMMENT
COMMIT



COMMIT





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Empty materialized view

[Thiemo Kellner]

Am 24.03.2024 um 22:06 schrieb Adrian Klaver:
The view session is on auto commit. (It's sole purpose to query stuff 
and not to have explicitly terminate transactions do to syntax errors 
and so on.)


Autocommit will only affect actions in that session, it will not make 
the other sessions actions visible. That depends on the other sessions 
committing actions.


See:

https://www.postgresql.org/docs/current/transaction-iso.html


I am under the impression that (higher transaction number in my example 
means more recent transaction) if my autocommit session's last 
transaction is 1 (some select), then the install session installs the 
objects and data with a series of transactions (2 - 20), i.e. several 
commits, and I afterwards use the autocommit session to check on the 
content of the objects installed in transactions 2 to 20, I should see 
the data having a transaction in the autocommit > 20. It works like this 
at least for the tables. But not for this mv.




Feeling quite dumb now. But then, there neither is data visible in the 
install session.


insert data into TASK_DEPENDENCY⠒V
INSERT 0 34


The above says the data was inserted.


But not into the MV but into TASK_DEPENDENCY⠒V.


Where and when was the count query run?


Excerpt of the install script

\echo ## tenth level ##
\ir views/QUERY_PER_TASK⠒MV.pg_sql


-- \echo # functions relying on other objects #


\echo # insert data #
\echo ## first level ##
\ir insert_data/CENTRICITY.pg_sql
\ir insert_data/DIRECTION.pg_sql
\ir insert_data/GOOD_CLASS.pg_sql
\ir insert_data/NODE_TYPE.pg_sql
\ir insert_data/REGION.pg_sql
\ir insert_data/TASK_TYPE.pg_sql

\echo ## second level ##
\ir insert_data/AREA.pg_sql
\ir insert_data/DISTANCE⠒V.pg_sql
\ir insert_data/GOOD⠒V.pg_sql
\ir insert_data/MAP⠒V.pg_sql

\echo ## third level ##
\ir insert_data/DIRECT_NEIGHBOUR.pg_sql
\echo ### Scandinavia ###
\ir insert_data/Scandinavia/NODE⠒V.pg_sql
\ir insert_data/Scandinavia/PRODUCTION⠒V.pg_sql
\ir insert_data/Scandinavia/TASK⠒V.pg_sql

\echo ## forth level ##
\echo Scandinavia
\ir insert_data/Scandinavia/DROP_OFF⠒V.pg_sql
\ir insert_data/Scandinavia/PICK_UP⠒V.pg_sql
\ir insert_data/Scandinavia/TASK_DEPENDENCY⠒V.pg_sql

-- \echo ## fifth level ##
-- \ir insert_data/Scandinavia/NODE_GOOD⠒V.pg_sql

select count(*) from SNOWRUNNER.TASK_DEPENDENCY⠒V;
select count(*) from SNOWRUNNER.QUERY_PER_TASK⠒MV;

commit;



Excerpt of the according protocol:

## tenth level ##
Set materialised view QUERY_PER_TASK⠒MV up
psql:views/QUERY_PER_TASK⠒MV.pg_sql:3: HINWEIS:  materialisierte Sicht 
»query_per_task⠒mv« existiert nicht, wird übersprungen

DROP MATERIALIZED VIEW
SELECT 0
REFRESH MATERIALIZED VIEW
COMMENT
COMMIT
# insert data #
## first level ##
insert data into CENTRICITY
INSERT 0 2
COMMIT
insert data into DIRECTION
INSERT 0 8
COMMIT
insert data into GOOD_CLASS
INSERT 0 15
COMMIT
insert data into NODE_TYPE
INSERT 0 3
COMMIT
insert data into REGION
INSERT 0 15
COMMIT
insert data into TASK_TYPE
INSERT 0 5
COMMIT
## second level ##
insert data into AREA
INSERT 0 16
COMMIT
insert data into DISTANCE⠒V
INSERT 0 3
COMMIT
insert data into GOOD⠒V
INSERT 0 164
COMMIT
insert data into MAP⠒V
INSERT 0 41
COMMIT
## third level ##
insert data into DIRECT_NEIGHBOUR
INSERT 0 8
INSERT 0 16
COMMIT
### Scandinavia ###
insert data into NODE⠒V
INSERT 0 112
COMMIT
insert data into PRODUCTION⠒V
INSERT 0 11
COMMIT
insert data into TASK⠒V
INSERT 0 56
COMMIT
## forth level ##
Scandinavia
insert data into DROP_OFF⠒V
INSERT 0 91
COMMIT
insert data into PICK_UP⠒V
INSERT 0 73
COMMIT
insert data into TASK_DEPENDENCY⠒V
INSERT 0 34
COMMIT
 count
---
66
(1 row)

 count
---
 0
(1 row)

COMMIT

Re: Not able to purge partition

[veem v]

On Sun, 24 Mar 2024 at 20:29, Laurenz Albe  wrote:

> On Sun, 2024-03-24 at 00:37 +0530, veem v wrote:
> > > Instead, use foreign keys between the partitions.
> >
> > I am struggling to understand how to maintain those partitions then?
> > As because we were planning to use pg_partman for creating and dropping
> > partitions automatically without much hassle.
>
> I understand.
>
> But do you want to pursue a way that is not working well, just because
> the tool you chose cannot handle it?
>
> Yours,
> Laurenz Albe



Thank you so much Laurenz.

Can you please suggest some docs which shows the way we should do the
partition maintenance (if not using pg_partman)?

Actually , I am not able to visualize the steps here. Do you mean to say ,
we will just create the partition tables without any foreign key
constraints first. Then create parent table future partitions first (say 10
days partitions) and then child table future 10 days partitions manually
and during that define the foreign key constraints , indexes etc. Here ,
how to exactly find the exact matching parent partitions so as to create
the foreign keys one to one?

Re: Empty materialized view

[Adrian Klaver]

On 3/24/24 13:58, Thiemo Kellner wrote:

Am 24.03.2024 um 21:50 schrieb Adrian Klaver:

On 3/24/24 13:36, Thiemo Kellner wrote:
It does depending on the order of viewing. Namely if you viewed the 
'old' empty MV in the outside session before you dropped/created the 
'new' MV and committed the changes.


Something like the viewing session is in a transaction before the 
(re-)creation of the mv?


The view session is on auto commit. (It's sole purpose to query stuff 
and not to have explicitly terminate transactions do to syntax errors 
and so on.)


Autocommit will only affect actions in that session, it will not make 
the other sessions actions visible. That depends on the other sessions 
committing actions.


See:

https://www.postgresql.org/docs/current/transaction-iso.html




Feeling quite dumb now. But then, there neither is data visible in the 
install session.


insert data into TASK_DEPENDENCY⠒V
INSERT 0 34


The above says the data was inserted.


COMMIT
  count
---
  0



Where and when was the count query run?


(1 row)


Thanks for taking care.


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Empty materialized view

[Thiemo Kellner]

Am 24.03.2024 um 21:50 schrieb Adrian Klaver:

On 3/24/24 13:36, Thiemo Kellner wrote:
It does depending on the order of viewing. Namely if you viewed the 
'old' empty MV in the outside session before you dropped/created the 
'new' MV and committed the changes.


Something like the viewing session is in a transaction before the 
(re-)creation of the mv?


The view session is on auto commit. (It's sole purpose to query stuff 
and not to have explicitly terminate transactions do to syntax errors 
and so on.)




Excerpt of the installation protocol:
…
## tenth level ##
Set materialised view QUERY_PER_TASK⠒MV up
psql:views/QUERY_PER_TASK⠒MV.pg_sql:3: HINWEIS:  materialisierte Sicht 
»query_per_task⠒mv« existiert nicht, wird übersprungen

DROP MATERIALIZED VIEW
SELECT 0
REFRESH MATERIALIZED VIEW
COMMENT
COMMIT
# insert data #
…





select count(*) from SNOWRUNNER.TASK_DEPENDENCY⠒V;


That is not the view you showed in your attached SQL in your previous 
post nor what is mentioned above. Also if I am following your naming 
scheme it is a regular view not a materialized view.


Feeling quite dumb now. But then, there neither is data visible in the 
install session.


insert data into TASK_DEPENDENCY⠒V
INSERT 0 34
COMMIT
 count
---
 0
(1 row)


Thanks for taking care.

Re: Empty materialized view

[Adrian Klaver]

On 3/24/24 13:36, Thiemo Kellner wrote:

Am 24.03.2024 um 21:30 schrieb Adrian Klaver:

On 3/24/24 13:11, Thiemo Kellner wrote:
Confirmed in the same session that created it or in a different session?


Different session, not knowing what that mattered


It does depending on the order of viewing. Namely if you viewed the 
'old' empty MV in the outside session before you dropped/created the 
'new' MV and committed the changes.




Excerpt of the installation protocol:
…
## tenth level ##
Set materialised view QUERY_PER_TASK⠒MV up
psql:views/QUERY_PER_TASK⠒MV.pg_sql:3: HINWEIS:  materialisierte Sicht 
»query_per_task⠒mv« existiert nicht, wird übersprungen

DROP MATERIALIZED VIEW
SELECT 0
REFRESH MATERIALIZED VIEW
COMMENT
COMMIT
# insert data #
…





select count(*) from SNOWRUNNER.TASK_DEPENDENCY⠒V;


That is not the view you showed in your attached SQL in your previous 
post nor what is mentioned above. Also if I am following your naming 
scheme it is a regular view not a materialized view.








--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Empty materialized view

[Thiemo Kellner]

Am 24.03.2024 um 21:30 schrieb Adrian Klaver:

On 3/24/24 13:11, Thiemo Kellner wrote:
Confirmed in the same session that created it or in a different session?


Different session, not knowing what that mattered.

Excerpt of the installation protocol:
…
## tenth level ##
Set materialised view QUERY_PER_TASK⠒MV up
psql:views/QUERY_PER_TASK⠒MV.pg_sql:3: HINWEIS:  materialisierte Sicht 
»query_per_task⠒mv« existiert nicht, wird übersprungen

DROP MATERIALIZED VIEW
SELECT 0
REFRESH MATERIALIZED VIEW
COMMENT
COMMIT
# insert data #
…

Check was done by DbVisualizer.

I was not able to create a test case. I tried

drop table if exists TEST_T cascade;
create table TEST_T (ID smallint);
insert into TEST_T (ID) values (1);
commit;

drop materialized view if exists TEST_MV_ON_TABLE;
create materialized view TEST_MV_ON_TABLE as select * from TEST_T with 
data; -- on table

commit;

select * from TEST_MV_ON_TABLE;

commit;
create or replace view VIEW_LEVEL_1 as select * from TEST_T;
create or replace view VIEW_LEVEL_2 as select v.id from VIEW_LEVEL_1 v 
cross join TEST_T;
create or replace view VIEW_LEVEL_3 as select v.id from VIEW_LEVEL_2 v 
cross join VIEW_LEVEL_1;
create or replace view VIEW_LEVEL_4 as select v.id from VIEW_LEVEL_3 v 
cross join VIEW_LEVEL_2;
create or replace view VIEW_LEVEL_5 as select v.id from VIEW_LEVEL_4 v 
cross join VIEW_LEVEL_3;
create or replace view VIEW_LEVEL_6 as select v.id from VIEW_LEVEL_5 v 
cross join VIEW_LEVEL_4;
create or replace view VIEW_LEVEL_7 as select v.id from VIEW_LEVEL_6 v 
cross join VIEW_LEVEL_5;
create or replace view VIEW_LEVEL_8 as select v.id from VIEW_LEVEL_7 v 
cross join VIEW_LEVEL_6;
create or replace view VIEW_LEVEL_9 as select v.id from VIEW_LEVEL_8 v 
cross join VIEW_LEVEL_7;
create or replace view VIEW_LEVEL_10 as select v.id from VIEW_LEVEL_9 v 
cross join VIEW_LEVEL_8;

commit;

drop materialized view if exists TEST_MV_ON_VIEWS;
create materialized view TEST_MV_ON_VIEWS as select * from VIEW_LEVEL_10 
with data; -- on views

commit;

select * from TEST_MV_ON_VIEWS;



But this works as expected.

Ok, I just added the following to my install script and there the data 
is visible.


select count(*) from SNOWRUNNER.TASK_DEPENDENCY⠒V;

Re: Empty materialized view

[Adrian Klaver]

On 3/24/24 13:11, Thiemo Kellner wrote:



Am 24.03.2024 um 20:56 schrieb Erik Wienhold:

Maybe you executed REFRESH in a transaction but did not commit it?


While I can see the point for the refresh (but there actually is a 
commit), I cannot hold it valid for a create with data when the mv 
actually is created (confirmed by being empty).


Confirmed in the same session that created it or in a different session?




I can't find any materialized view in your archive.


Oh sh*. That is the file, I forgot to commit. Please find it attached now.


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Empty materialized view

[Thiemo Kellner]

Am 24.03.2024 um 20:56 schrieb Erik Wienhold:

Maybe you executed REFRESH in a transaction but did not commit it?


While I can see the point for the refresh (but there actually is a 
commit), I cannot hold it valid for a create with data when the mv 
actually is created (confirmed by being empty).



I can't find any materialized view in your archive.


Oh sh*. That is the file, I forgot to commit. Please find it attached now.\echo Set materialised view QUERY_PER_TASK⠒MV up

drop materialized view if exists QUERY_PER_TASK⠒MV;

create materialized view QUERY_PER_TASK⠒MV as
   select TDP.TOP_LEVEL_TASK_TYPE⠒NAME,
  TDP.TOP_LEVEL_TASK⠒NAME,
  TDP.TASK_TREE⠒HIGHEST_PRIORITY,
  TDP.TASK⠒NAME,
  TDP.TASK_TYPE⠒NAME,
  TDP.TASK⠒PRIORITY,
  TDP.TASK⠒DESCRIPTION,
  TDP.GOOD⠒NAME,
  TDP.GOOD_4_THIS_TASK,
  TDP.QUANTITY⠒DROP_OFF,
  TDP.QUANTITY⠒NEEDED_4_THIS_REGION,
  TDP.QUANTITY⠒NEEDED_4_THIS_TASK_TREE,
  TDP.QUANTITY⠒PICK_UP,
  TDP.QUANTITY⠒READY_4_THIS_REGION,
  TDP.GOOD⠒SIZE,
  TDP.GOOD⠒SIZE * TDP.QUANTITY⠒DROP_OFF
  as NEEDED_TRANSPORT_CAPACITY_4_THIS_TASK,
  TDP.NEEDED_TRANSPORT_CAPACITY_4_THIS_REGION,
  TDP.NEEDED_TRANSPORT_CAPACITY_4_THIS_TASK_TREE,
  case TDP.NODE_TYPE⠒NAME⠒PICK_UP
  when 'Crafting zone' then R.GOOD⠒RESOURCE⠒NAME
  else null
  end as GOOD⠒RESOURCE⠒NAME,
  P.QUANTITY⠒READY_4_THIS_REGION
  as RESOURCE_QUANTITY⠒READY_4_THIS_REGION,
  TDP.NODE⠒MAP⠒NAME⠒DROP_OFF,
  TDP.NODE⠒NAME⠒DROP_OFF,
  TDP.NODE⠒MAP⠒NAME⠒PICK_UP,
  TDP.NODE⠒NAME⠒PICK_UP,
  P.NODE⠒MAP⠒NAME as NODE⠒MAP⠒NAME⠒PICK_UP_RESOURCE,
  P.NODE⠒NAME as NODE⠒NAME⠒PICK_UP_RESOURCE,
  TDP.DIRECTION⠒CODE⠒DROP_OFF,
  TDP.CENTRICITY⠒NAME⠒DROP_OFF,
  TDP.DIRECTION⠒CODE⠒PICK_UP,
  TDP.CENTRICITY⠒NAME⠒PICK_UP,
  TDP.NODE⠒DESCRIPTION⠒DROP_OFF,
  TDP.PICK_UP⠒DESCRIPTION,
  TDP.NODE⠒DESCRIPTION⠒PICK_UP,
  TDP.DIRECTION⠒DESCRIPTION⠒DROP_OFF,
  TDP.CENTRICITY⠒DESCRIPTION⠒DROP_OFF,
  TDP.DIRECTION⠒DESCRIPTION⠒PICK_UP,
  TDP.CENTRICITY⠒DESCRIPTION⠒PICK_UP,
  P.DIRECTION⠒DESCRIPTION
  as DIRECTION⠒DESCRIPTION⠒PICK_UP_RESOURCE,
  P.CENTRICITY⠒DESCRIPTION
  as 
CENTRICITY⠒DESCRIPTION⠒PICK_UP_RESOURCE,
  TDP.GOOD⠒DESCRIPTION,
  TDP.GOOD⠒WEIGHT,
  TDP.PATH,
  TDP.REGION⠒NAME,
  TDP.TOP_LEVEL_TASK⠒PRIORITY,
  TDP.REGION⠒ID,
  TDP.TASK_PREDECESSOR⠒ID,
  TDP.TASK⠒ID,
  TDP.LEVEL,
  TDP.GOOD⠒ID
 from SNOWRUNNER.TASK_HIERARCHY_DROP_OFF_PICK_UP_AVAIL⠒V TDP
  left outer join SNOWRUNNER.PRODUCTION⠒V R
   on 1 = 1
  and TDP.GOOD⠒ID = R.GOOD⠒PRODUCT⠒ID
  and TDP.REGION⠒ID = R.REGION⠒ID
  left outer join SNOWRUNNER.PICK_UP⠒V P
   on 1 = 1
  and R.GOOD⠒RESOURCE⠒ID = P.GOOD⠒ID
  and R.REGION⠒ID = P.REGION⠒ID
where 1 = 1
 order by TDP.REGION⠒NAME asc,
  TASK_TREE⠒HIGHEST_PRIORITY asc,
  NODE⠒MAP⠒NAME⠒DROP_OFF asc,
  NODE⠒MAP⠒NAME⠒PICK_UP asc,
  NODE⠒MAP⠒NAME⠒PICK_UP asc,
  NODE⠒MAP⠒NAME⠒PICK_UP_RESOURCE asc,
  NODE⠒NAME⠒PICK_UP_RESOURCE asc,
  GOOD⠒NAME asc,
  NODE⠒MAP⠒NAME⠒DROP_OFF asc,
  PATH asc
  with data;

refresh materialized view QUERY_PER_TASK⠒MV
  with data;


comment on materialized view QUERY_PER_TASK⠒MV is
  '
$Header$';


commit;

Re: Empty materialized view

[Adrian Klaver]

On 3/24/24 11:12, Thiemo Kellner wrote:

Hi

I have created a materialized view with "with data". And I refreshed it 
with "with data". The query of the mv returns records when executed 
outside the mv. I would appreciate help with respect to what I miss that 
my mv is empty. You might want to have a look at the code attached.


That does not contain the statements mentioned above. Provide a simple 
test case as code inline to your reply.




Kind regards

Thiemo


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Is this a buggy behavior?

[Peter J. Holzer]

On 2024-03-24 11:23:22 -0700, David G. Johnston wrote:
> On Sun, Mar 24, 2024 at 11:14 AM Peter J. Holzer  wrote:
> It doesn't. Your statement
> 
> > CREATE TABLE test1
> > (
> > c1 numeric   NULL ,
> > c2 varchar(36)  NOT NULL ,
> > CONSTRAINT test1_PK PRIMARY KEY (c1,c2)
> > ) ;
> 
> creates the table with both columns being defined as NOT NULL:
> 
> 
> 
> The request is a warning when defining a multi-column table constraint primary
> key if any of the columns comprising said PK are not defined already to be NOT
> NULL.
> 
> Personally, green field at least, I find that to be reasonable.

Frankly, I don't. I see no reason why I should have declare a column
in a PK explicitely as NOT NULL. 

Something like

CREATE TABLE test1
(
c1 numeric,
c2 varchar(36),
CONSTRAINT test1_PK PRIMARY KEY (c1,c2)
) ;

or

create table test2 (
id serial primary key,
...
);

should be totally fine. An explicit NOT NULL here is just noise and
doesn't add value.

I have some sympathy for the idea that an explicit NULL in a column
definition should cause a warning if the resulting column would not in
fact be nullable. But since writing NULL is otherwise exactly equivalent
to writing nothing, even that seems a bit inconsistent and might be
more confusing than helpful. In any case it seems like a very low-value
change to me which should only be done if it's very little effort
(which apparently it isn't).

hp


-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: Empty materialized view

[Erik Wienhold]

On 2024-03-24 19:12 +0100, Thiemo Kellner wrote:
> I have created a materialized view with "with data". And I refreshed it with
> "with data". The query of the mv returns records when executed outside the
> mv. I would appreciate help with respect to what I miss that my mv is empty.

Maybe you executed REFRESH in a transaction but did not commit it?

> You might want to have a look at the code attached.

I can't find any materialized view in your archive.

-- 
Erik

Re: Is this a buggy behavior?

[David G. Johnston]

On Sun, Mar 24, 2024 at 11:14 AM Peter J. Holzer  wrote:

>
> It doesn't. Your statement
>
> > CREATE TABLE test1
> > (
> > c1 numeric   NULL ,
> > c2 varchar(36)  NOT NULL ,
> > CONSTRAINT test1_PK PRIMARY KEY (c1,c2)
> > ) ;
>
> creates the table with both columns being defined as NOT NULL:
>
>
The request is a warning when defining a multi-column table
constraint primary key if any of the columns comprising said PK are not
defined already to be NOT NULL.

Personally, green field at least, I find that to be reasonable.  Especially
if we are altering catalog metadata to define the columns to be not null,
as opposed to say the case when a check constraint has a "col is not null"
condition that could never pass even though the column itself is null-able.

David J.

Re: Is this a buggy behavior?

[Peter J. Holzer]

On 2024-03-24 21:05:04 +0530, sud wrote:
> Do you specifically mean that 'null'  keyword is just not making any sense 
> here
> in postgres. But even if that is the case , i tried inserting nothing (hoping
> "nothing" is "null" in true sense),

This is a strange hope.

> but then too it failed in the first statement while inserting which is
> fine as per the PK. 
> 
> But don't you think,in the first place it shouldn't have been allowed to 
> create
> the table with one of the composite PK columns being defined as NULL.

It doesn't. Your statement

> CREATE TABLE test1
> (
> c1 numeric   NULL ,
> c2 varchar(36)  NOT NULL ,
> CONSTRAINT test1_PK PRIMARY KEY (c1,c2)
> ) ;

creates the table with both columns being defined as NOT NULL:

hjp=> CREATE TABLE test1
(
c1 numeric   NULL ,
c2 varchar(36)  NOT NULL ,
CONSTRAINT test1_PK PRIMARY KEY (c1,c2)
) ;
CREATE TABLE
Time: 16.815 ms
hjp=> \d test1
 Table "hjp.test1"
╔╤═══╤═══╤══╤═╗
║ Column │ Type  │ Collation │ Nullable │ Default ║
╟┼───┼───┼──┼─╢
║ c1 │ numeric   │   │ not null │ ║
║ c2 │ character varying(36) │   │ not null │ ║
╚╧═══╧═══╧══╧═╝
Indexes:
"test1_pk" PRIMARY KEY, btree (c1, c2)


> And then , while inserting the null record, it should say that the PK
> constraint is violated but not the "not null constraint" violated.

That may just be an artifact of the implementation. You can check
whether a value to be inserted is null or not without searching the
table, so that is done first. Only then you have to check the index for
a possible duplicate value, so that's done later.

But as a user I actually prefer it that way. The more precisely the
database can tell me why the insert failed, the better.

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Empty materialized view

[Thiemo Kellner]

Hi

I have created a materialized view with "with data". And I refreshed it 
with "with data". The query of the mv returns records when executed 
outside the mv. I would appreciate help with respect to what I miss that 
my mv is empty. You might want to have a look at the code attached.


Kind regards

Thiemo

snowrunner.7z
Description: application/compressed

Re: Is this a buggy behavior?

[Thiemo Kellner]

Am 24.03.2024 um 17:43 schrieb Christophe Pettus:

The situation is much more like the customer saying, "I understand that the standard 
paint for this car is red, but I wish it painted blue instead."


Not in the least. Declaring the column to be NULL is explicitly 
requesting the car be blue. And declaring, in the same statement, there 
be a pk on that column is implicitly requesting the car be red.



Again, you can argue that PostgreSQL should remember that you explicitly asked 
for a NULL and generate a warning in that case, but that's not a trivial amount 
of work, since right now, that NULL is thrown away very early in statement 
processing.


Only, if PG is not aware of being in the process of creating a table.

Well, I do not feel, I can make myself understood.

Re: Is this a buggy behavior?

[Christophe Pettus]

> On Mar 24, 2024, at 09:32, Thiemo Kellner  wrote:
> Am 24.03.2024 um 17:15 schrieb Christophe Pettus:
>> I think the point is that it's not really doing anything "silently."  You 
>> are asking for a PRIMARY KEY constraint on a column, and it's giving it to 
>> you.  One of the effects (not even really a side-effect) of that request is 
>> that the column is then declared NOT NULL.
> 
> But don't you also request the database to have the column being nullable? 
> So, PG, at this point silently prioritises the request for the PK over the 
> request of the nullability. Does it not?

No.  The NULL is noise and is discarded.  PostgreSQL instantly forgets that you 
explicitly said NULL.  The difference between:

CREATE TABLE t (i int NULL); -- and
CREATE TABLE t (i int);

... doesn't make it to the point that the constraint is actually created.

>> The reason it doesn't give you a warning is that by the time it would be in 
>> a position to, it's forgotten that you explicitly said NULL.
> 
> How can that be forgotten? This information ends up in the data catalogue 
> eventually!

See above.  The fact that the column can contains nulls is retained, but that 
you explicitly said NULL is not.

> I would agree if you had two separate statements there, but in the example it 
> were not two different statements but one single contradictory statement.

The answer to all of these is the same: NULL is noise.  It has no more effect 
than if you said:

CREATE TABLE t (i int); -- Remember, "i" can contain NULLs!

> The issue however arose, because the statement said. "Please order me a blue 
> car painted in red." Hopefully, any single salesman should respond with 
> something like. "Dear customer, all very well, but it contradictory to have a 
> blue car painted in red. Do you want a red car or a blue one?"

The situation is much more like the customer saying, "I understand that the 
standard paint for this car is red, but I wish it painted blue instead."

Again, you can argue that PostgreSQL should remember that you explicitly asked 
for a NULL and generate a warning in that case, but that's not a trivial amount 
of work, since right now, that NULL is thrown away very early in statement 
processing.

Re: Is this a buggy behavior?

[Thiemo Kellner]

Am 24.03.2024 um 17:15 schrieb Christophe Pettus:

I think the point is that it's not really doing anything "silently."  You are 
asking for a PRIMARY KEY constraint on a column, and it's giving it to you.  One of the 
effects (not even really a side-effect) of that request is that the column is then 
declared NOT NULL.


But don't you also request the database to have the column being 
nullable? So, PG, at this point silently prioritises the request for the 
PK over the request of the nullability. Does it not?




The reason it doesn't give you a warning is that by the time it would be in a 
position to, it's forgotten that you explicitly said NULL.


How can that be forgotten? This information ends up in the data 
catalogue eventually!



It does see that the column in nullable, but that in itself isn't worth 
emitting a warning over, since you are explicitly telling it that now the 
column shouldn't be null.


I would agree if you had two separate statements there, but in the 
example it were not two different statements but one single 
contradictory statement.



It wouldn't make much more sense to emit a warning there than it would be in 
this situation:

CREATE TABLE t (i int NULL);
ALTER TABLE t ALTER i SET NOT NULL;


Again, these are two separate statements.

Maybe an example can help.

You are describing the situation when one goes to a car salesman and 
orders a car painted in blue. The car gets manufactured and the salesman 
hands you over the key. Then you say to the salesman. Now, please, 
re-paint it in red.


The issue however arose, because the statement said. "Please order me a 
blue car painted in red." Hopefully, any single salesman should respond 
with something like. "Dear customer, all very well, but it contradictory 
to have a blue car painted in red. Do you want a red car or a blue one?"



Dunkel war's, der Mond schien helle,
Als ein Wagen blitze schnelle,
langsam um die runde Ecke fuhr…

Re: Is this a buggy behavior?

[Christophe Pettus]

On 3/24/24 08:28, Thiemo Kellner wrote:
> Sure, my example has lots more side effect than silently do the right thing.

I think the point is that it's not really doing anything "silently."  You are 
asking for a PRIMARY KEY constraint on a column, and it's giving it to you.  
One of the effects (not even really a side-effect) of that request is that the 
column is then declared NOT NULL.

The reason it doesn't give you a warning is that by the time it would be in a 
position to, it's forgotten that you explicitly said NULL.  It does see that 
the column in nullable, but that in itself isn't worth emitting a warning over, 
since you are explicitly telling it that now the column shouldn't be null.  It 
wouldn't make much more sense to emit a warning there than it would be in this 
situation:

CREATE TABLE t (i int NULL);
ALTER TABLE t ALTER i SET NOT NULL;

-- 
Adrian Klaver
adrian.kla...@aklaver.com

Re: Is this a buggy behavior?

[Thiemo Kellner]

Am 24.03.2024 um 16:44 schrieb Andreas Kretschmer:

postgres=# create table bla(i int null primary key);
CREATE TABLE
postgres=# \d bla
     Table "public.bla"
  Column |  Type   | Collation | Nullable | Default
+-+---+--+-
  i  | integer |   | not null |
Indexes:
     "bla_pkey" PRIMARY KEY, btree (i)

postgres=# drop table bla;
DROP TABLE
postgres=# create table bla(i int not null primary key);
CREATE TABLE
postgres=# \d bla
     Table "public.bla"
  Column |  Type   | Collation | Nullable | Default
+-+---+--+-
  i  | integer |   | not null |
Indexes:
     "bla_pkey" PRIMARY KEY, btree (i)

postgres=#


as you can see, there is no difference.  the PK-Constraint is the 
important thing here.


This describes the END state perfectly. But while creating the table, 
that is the question.


I am thinking along the lines that a table is being created by "first" 
(1) the columns in their default state. That is, Nullable would be true. 
And after that (2), all the constraints get created. Because the not 
null constraint is not present in the column definition, there is no 
change. After that (3), the primary gets created, requiring an 
additional not null constraint. Assuming such a creation would lead to 
an error when one already exists, I suppose there is a check on the 
presence for the constraint.
If (2) and (3) is swapped, then in the step creating the not null 
constraint, one had to go through ALL the column definitions to retrieve 
on which one such a constraint is defined. At this point, one also could 
check whether the nullability of a column that has already been created 
is the one as defined, being explicitly using "null"/"not null" or the 
default.

Re: Is this a buggy behavior?

[Adrian Klaver]

On 3/24/24 08:28, Thiemo Kellner wrote:


Am 24.03.2024 um 16:17 schrieb Tom Lane:


To do that, we'd have to remember that you'd said NULL, which we
don't: the word is just discarded as a noise clause.  Considering
that this usage of NULL isn't even permitted by the SQL standard,
that seems like a bit too much work.


If I understood correctly, only the NOT NULL expression gets remembered, 
but the NULL gets discarded. No, I do not quite get it. Somehow, it has 
to be decided whether to create a "check constraint" or not, but this 
information is not available any more when creating the primary key? Not 
even in some kind of intermediary catalogue?


"Considering that this usage of NULL isn't even permitted by the SQL 
standard" is in my opinion a strange argument. To me, it is similar as 
to say, well a column has a not null constraint and that must be enough, 
we do not check whether the data complies when inserting or updating. 
Sure, my example has lots more side effect than silently do the right 
thing.


That is sort of the point the OPs example was for a CREATE TABLE and 
hence had no data. The OP also wanted a PK and per:


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

"PRIMARY KEY enforces the same data constraints as a combination of 
UNIQUE and NOT NULL. "


they got a compound PK with the specified constraints.

If they had being doing a ALTER TABLE to add a PK over the columns after 
null values where added they result would be different:


CREATE TABLE test1
(
c1 varchar(36)   NULL ,
c2 varchar(36)  NOT NULL
) ;

insert into test1 values (null, 'test');

alter table test1 add constraint test_pk PRIMARY KEY(c1,c2);
ERROR:  column "c1" of relation "test1" contains null values



Please do not get me wrong. I can totally understand that something 
needs to much work to implement. I am just puzzled.





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Is this a buggy behavior?

[Erik Wienhold]

On 2024-03-24 16:28 +0100, Thiemo Kellner wrote:
> Am 24.03.2024 um 16:17 schrieb Tom Lane:
> 
> > To do that, we'd have to remember that you'd said NULL, which we
> > don't: the word is just discarded as a noise clause.  Considering
> > that this usage of NULL isn't even permitted by the SQL standard,
> > that seems like a bit too much work.
> 
> "Considering that this usage of NULL isn't even permitted by the SQL
> standard" is in my opinion a strange argument.

I don't know if the SQL standard ever allowed the NULL "constraint", but
the 2003 revision (the oldest one that I've got) does not allow it:

>From Part 2, 11.4 :

 ::=
NOT NULL
  | 
  | 
  | 

Postgres only accepts it to be compatible with other RDBMS. [1]

[1] 
https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-PARMS-NULL

-- 
Erik

Re: Is this a buggy behavior?

[Andreas Kretschmer]

Am 24.03.24 um 16:41 schrieb Thiemo Kellner:



Am 24.03.2024 um 16:36 schrieb Andreas Kretschmer:
the null-able constraint addition to a column is pointless because by 
default all columns are nullable. definition as a primary key adds 
the not null constraint.


While this is certainly true, I do not see why the information that a 
not null constraint is to be created or has been created is not 
available.





postgres=# create table bla(i int null primary key);
CREATE TABLE
postgres=# \d bla
    Table "public.bla"
 Column |  Type   | Collation | Nullable | Default
+-+---+--+-
 i  | integer |   | not null |
Indexes:
    "bla_pkey" PRIMARY KEY, btree (i)

postgres=# drop table bla;
DROP TABLE
postgres=# create table bla(i int not null primary key);
CREATE TABLE
postgres=# \d bla
    Table "public.bla"
 Column |  Type   | Collation | Nullable | Default
+-+---+--+-
 i  | integer |   | not null |
Indexes:
    "bla_pkey" PRIMARY KEY, btree (i)

postgres=#


as you can see, there is no difference.  the PK-Constraint is the 
important thing here.


Andreas



--
Andreas Kretschmer
CYBERTEC PostgreSQL Services and Support

Re: Is this a buggy behavior?

[Thiemo Kellner]

Am 24.03.2024 um 16:35 schrieb sud:
On Sun, Mar 24, 2024 at 8:47 PM Tom Lane > wrote:
Do you specifically mean that 'null'  keyword is just not making any 
sense here in postgres. But even if that is the case , i tried inserting 
nothing (hoping "nothing" is "null" in true sense), but then too it 
failed in the first statement while inserting which is fine as per the PK.


To the best of my knowledge, your assumption is correct. And therefore 
the insert must fail because a pk never must contain null values.


But don't you think,in the first place it shouldn't have been allowed to 
create the table with one of the composite PK columns being defined as 
NULL. And then , while inserting the null record, it should say that the 
PK constraint is violated but not the "not null constraint" violated.


CREATE TABLE test1
(
c1 numeric   NULL ,
c2 varchar(36)  NOT NULL ,
CONSTRAINT test1_PK PRIMARY KEY (c1,c2)
) ;

insert into test1(c2) values('123');

/*ERROR: null value in column "c1" of relation "test1" violates not-null 
constraint DETAIL: Failing row contains (null, 123).*/


I feel largely the same way. The definition is contradictory but there 
is no message to tell you so.

Re: Is this a buggy behavior?

[Thiemo Kellner]

Am 24.03.2024 um 16:39 schrieb Erik Wienhold:

And that's also possible in Postgres with UNIQUE constraints if you're
looking for that behavior.


Sort of the distinction between PK and UQ.

Re: Is this a buggy behavior?

[Thiemo Kellner]

Am 24.03.2024 um 16:36 schrieb Andreas Kretschmer:
the null-able constraint addition to a column is pointless because by 
default all columns are nullable. definition as a primary key adds the 
not null constraint.


While this is certainly true, I do not see why the information that a 
not null constraint is to be created or has been created is not available.

Re: Is this a buggy behavior?

[Erik Wienhold]

I wrote:
> Do you come from sqlite?  That allows NULL in primary key columns
> without an explicit NOT NULL constraint.

And that's also possible in Postgres with UNIQUE constraints if you're
looking for that behavior.

-- 
Erik

Re: Is this a buggy behavior?

[Andreas Kretschmer]

Am 24.03.24 um 16:28 schrieb Thiemo Kellner:


Am 24.03.2024 um 16:17 schrieb Tom Lane:


To do that, we'd have to remember that you'd said NULL, which we
don't: the word is just discarded as a noise clause. Considering
that this usage of NULL isn't even permitted by the SQL standard,
that seems like a bit too much work.


If I understood correctly, only the NOT NULL expression gets 
remembered, but the NULL gets discarded. No, I do not quite get it. 
Somehow, it has to be decided whether to create a "check constraint" 
or not, but this information is not available any more when creating 
the primary key? Not even in some kind of intermediary catalogue?


the null-able constraint addition to a column is pointless because by 
default all columns are nullable. definition as a primary key adds the 
not null constraint.


Andreas

--
Andreas Kretschmer
CYBERTEC PostgreSQL Services and Support

Re: Is this a buggy behavior?

[sud]

On Sun, Mar 24, 2024 at 8:47 PM Tom Lane  wrote:

> Thiemo Kellner  writes:
> > Am 24.03.2024 um 15:54 schrieb Erik Wienhold:
> >> This is required by the SQL standard: columns of a primary key must be
> >> NOT NULL.  Postgres automatically adds the missing NOT NULL constraints
> >> when defining a primary key.  You can verify that with \d test1 in psql.
>
> > To me, this behaviour, while correct, is not too concise. I wished, that
> > PG issued a warning about a definition conflict. In PostgreSQL, a PK
> > must always be not nullable, so explicitly defining on of a PK's columns
> > as nullable is contradictory, one should get notified of.
>
> To do that, we'd have to remember that you'd said NULL, which we
> don't: the word is just discarded as a noise clause.  Considering
> that this usage of NULL isn't even permitted by the SQL standard,
> that seems like a bit too much work.
>

Do you specifically mean that 'null'  keyword is just not making any sense
here in postgres. But even if that is the case , i tried inserting nothing
(hoping "nothing" is "null" in true sense), but then too it failed in the
first statement while inserting which is fine as per the PK.

But don't you think,in the first place it shouldn't have been allowed to
create the table with one of the composite PK columns being defined as
NULL. And then , while inserting the null record, it should say that the PK
constraint is violated but not the "not null constraint" violated.

CREATE TABLE test1
(
c1 numeric   NULL ,
c2 varchar(36)  NOT NULL ,
CONSTRAINT test1_PK PRIMARY KEY (c1,c2)
) ;

insert into test1(c2) values('123');

*ERROR: null value in column "c1" of relation "test1" violates not-null
constraint DETAIL: Failing row contains (null, 123).*

Re: Is this a buggy behavior?

[Thiemo Kellner]

Am 24.03.2024 um 16:17 schrieb Tom Lane:


To do that, we'd have to remember that you'd said NULL, which we
don't: the word is just discarded as a noise clause.  Considering
that this usage of NULL isn't even permitted by the SQL standard,
that seems like a bit too much work.


If I understood correctly, only the NOT NULL expression gets remembered, 
but the NULL gets discarded. No, I do not quite get it. Somehow, it has 
to be decided whether to create a "check constraint" or not, but this 
information is not available any more when creating the primary key? Not 
even in some kind of intermediary catalogue?


"Considering that this usage of NULL isn't even permitted by the SQL 
standard" is in my opinion a strange argument. To me, it is similar as 
to say, well a column has a not null constraint and that must be enough, 
we do not check whether the data complies when inserting or updating. 
Sure, my example has lots more side effect than silently do the right thing.


Please do not get me wrong. I can totally understand that something 
needs to much work to implement. I am just puzzled.

Re: Is this a buggy behavior?

[Tom Lane]

Thiemo Kellner  writes:
> Am 24.03.2024 um 15:54 schrieb Erik Wienhold:
>> This is required by the SQL standard: columns of a primary key must be
>> NOT NULL.  Postgres automatically adds the missing NOT NULL constraints
>> when defining a primary key.  You can verify that with \d test1 in psql.

> To me, this behaviour, while correct, is not too concise. I wished, that 
> PG issued a warning about a definition conflict. In PostgreSQL, a PK 
> must always be not nullable, so explicitly defining on of a PK's columns 
> as nullable is contradictory, one should get notified of.

To do that, we'd have to remember that you'd said NULL, which we
don't: the word is just discarded as a noise clause.  Considering
that this usage of NULL isn't even permitted by the SQL standard,
that seems like a bit too much work.

regards, tom lane

Re: Is this a buggy behavior?

[Thiemo Kellner]

Am 24.03.2024 um 15:54 schrieb Erik Wienhold:


This is required by the SQL standard: columns of a primary key must be
NOT NULL.  Postgres automatically adds the missing NOT NULL constraints
when defining a primary key.  You can verify that with \d test1 in psql.


To me, this behaviour, while correct, is not too concise. I wished, that 
PG issued a warning about a definition conflict. In PostgreSQL, a PK 
must always be not nullable, so explicitly defining on of a PK's columns 
as nullable is contradictory, one should get notified of.


The two dimes of Thiemo

Re: Not able to purge partition

[Laurenz Albe]

On Sun, 2024-03-24 at 00:37 +0530, veem v wrote:
> > Instead, use foreign keys between the partitions.
> 
> I am struggling to understand how to maintain those partitions then?
> As because we were planning to use pg_partman for creating and dropping
> partitions automatically without much hassle.

I understand.

But do you want to pursue a way that is not working well, just because
the tool you chose cannot handle it?

Yours,
Laurenz Albe

Re: Is this a buggy behavior?

[Erik Wienhold]

On 2024-03-24 15:25 +0100, sud wrote:
> Create a table and composite primary key. But to my surprise it allowed me
> to have the composite primary key created even if one of the columns was
> defined as nullable. But then inserting the NULL into that column erroring
> out at the first record itself , stating "not null constraint" is violated.
> 
> CREATE TABLE test1
> (
> c1 varchar(36)   NULL ,
> c2 varchar(36)  NOT NULL ,
> CONSTRAINT test1_PK PRIMARY KEY (c1,c2)
> ) ;
> 
> -- Table created without any error even one of the columns in the PK was
> defined as NULL.
> 
> insert into test1 values(null,'123');
> 
> 
> *ERROR:  null value in column "c1" of relation "test1" violates not-null
> constraintDETAIL:  Failing row contains (null, 123).*
> 
> insert into test1 values('123','123');
> 
> --works fine as expected

This is required by the SQL standard: columns of a primary key must be
NOT NULL.  Postgres automatically adds the missing NOT NULL constraints
when defining a primary key.  You can verify that with \d test1 in psql.

Do you come from sqlite?  That allows NULL in primary key columns
without an explicit NOT NULL constraint.

-- 
Erik

Re: Not able to purge partition

[Lok P]

On Sun, Mar 24, 2024 at 12:38 AM veem v  wrote:

> On Sat, 23 Mar 2024 at 23:08, Laurenz Albe 
> wrote:
>
>> On Sat, 2024-03-23 at 22:41 +0530, veem v wrote:
>> > 1)As we see having foreign key defined is making the detach partition
>> run
>> >   for minutes(in our case 5-10minutes for 60 million rows partition), so
>> >   how to make the parent table partition detach and drop work fast in
>> such
>> >   a scenario while maintaining the foreign key intact?
>>
>> I told you: don't do it.
>> Instead, use foreign keys between the partitions.
>>
>
> I am struggling to understand how to maintain those partitions then? As
> because we were planning to use pg_partman for creating and dropping
> partitions automatically without much hassle. So do you mean to say do the
> partition maintenance(create/drop) by creating our own jobs and not to use
> the pg_partman extension for this.
>
> Say for example in our case the parent table has 3-4 child table and all
> are partitioned on same keys/columns, so how we can identify the child
> partitions and then create all foreign keys to the respective parent table
> partitions and attach those partitions to parent table and also make this
> process automated? Appreciate any guidance on this.
>
> Actually, using pg_partman was taking care of everything starting from
> creating partitions with different names and creating respective indexes,
> constraints also with different names for each partitions without us being
> worrying anything about those.
>

This appears to be a major issue , if it's taking minutes for dropping the
parent table partitions and not allowing read operation during that time on
the child table by taking locks on them. We have many databases in Oracle
with such referential key constraints existing on partitioned tables and we
were planning to move those to postgres. I think in Oracle, they were by
default created partition to partition without need to check the whole
table or all the child table partitions while dropping the parent
partitions .

Is this a buggy behavior?

[sud]

Hello All,
Create a table and composite primary key. But to my surprise it allowed me
to have the composite primary key created even if one of the columns was
defined as nullable. But then inserting the NULL into that column erroring
out at the first record itself , stating "not null constraint" is violated.

CREATE TABLE test1
(
c1 varchar(36)   NULL ,
c2 varchar(36)  NOT NULL ,
CONSTRAINT test1_PK PRIMARY KEY (c1,c2)
) ;

-- Table created without any error even one of the columns in the PK was
defined as NULL.

insert into test1 values(null,'123');


*ERROR:  null value in column "c1" of relation "test1" violates not-null
constraintDETAIL:  Failing row contains (null, 123).*

insert into test1 values('123','123');

--works fine as expected


Regards
Sud

Re: Statistics information.

[Julien Rouhaud]

On Sat, Mar 23, 2024 at 6:51 PM Ron Johnson  wrote:
>
> On Sat, Mar 23, 2024 at 12:33 AM arun chirappurath  
> wrote:
>>
>> 1. Last run duration
>> 2. Average time for execution.
>> 3. Filter statistics for a specific function(stored procedure)
>> 4. Filter for specific texts.
>> 5 top queries
>> 6. query plans...
>
>
> I see 1, 2, 5 & 6 via AppDynamics, after installing the pg_stat_statements 
> extension.  Vanilla Postgresql does not retain those statistics.

You can use PoWA for that (https://powa.readthedocs.io/en/latest/)