BUG #15950: pg_freespace.avail is 0
The following bug has been logged on the website:
Bug reference: 15950
Logged by: Daniel Adeniji
Email address: daniel_adeniji@hotmail.com
PostgreSQL version: 11.4
Operating system: Windows 10
Description:
Issued the query pasted below :-
===============================
with cteSetting
(
name
, setting
)
as
(
select
name
, setting::integer
from pg_catalog.pg_settings
where name = 'block_size'
)
SELECT
tblN.nspname
as "schema"
, tblC.relname
, avg(tblC.relpages)::int
as relpages
, avg(tblC.reltuples)::int
as reltuples
, count(*) as cnt
, pg_size_pretty
(
cast
(
avg(tblPGFS.avail) as bigint
)
)
as "Av. freespace size"
/*
max(cteSBS.setting)
as "blockSize"
*/
, round
(
100
* avg
(
tblPGFS.avail
)
/
(
avg(cteSBS.setting)::int
)
,2
) as "Av. freespace ratio"
FROM pg_catalog.pg_class tblC
join pg_catalog.pg_namespace tblN
on tblC.relnamespace = tblN.oid
inner join pg_freespace(tblC.oid) tblPGFS
on TRUE
inner join cteSetting cteSBS
on cteSBS.name = 'block_size'
where tblC.relkind = 'r'
and tblN.nspname not in
(
'information_schema'
, 'pg_catalog'
)
GROUP BY
tblN.nspname
, tblC.relname
ORDER BY
tblN.nspname
, tblC.relname
;
Result :-
=========
1) pg_freespace.avail is 0
On Mon, Aug 12, 2019 at 2:08 PM PG Bug reporting form <
noreply@postgresql.org> wrote:
The following bug has been logged on the website:
Bug reference: 15950
Logged by: Daniel Adeniji
Email address: daniel_adeniji@hotmail.com
PostgreSQL version: 11.4
Operating system: Windows 10
Description:Issued the query pasted below :-
...
Result :-
=========1) pg_freespace.avail is 0
Your query didn't even return pg_freespace.avail. It returned some
convoluted thing that might depend on it.
Why is this a bug? Why is 0 wrong? What should it be instead? If
pg_freespace.avail is wrong, why not how a query that shows that, rather
than something else tangentially related to it? What is the actual output
of the query you do show?
Cheers,
Jeff
Jeff :-
Sorry that I did not provide ore information.
I was hoping the issue will be easy to re-create.
Here are the steps to re create :-
1. I Created a table
2. Added records to it
3. Updated and deleted records, hoping to create free pages
I have documented same here :-
PostgreSQL :- Create free pages in Table
https://learningintheopen.org/2019/08/12/postgresql-create-freepages-in-table/.
Best,
Daniel Adeniji
________________________________
From: Daniel Adeniji <daniel_adeniji@hotmail.com>
Sent: Monday, August 12, 2019 2:04 PM
To: Jeff Janes <jeff.janes@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #15950: pg_freespace.avail is 0
Jeff :-
Sorry :-
Here are the steps to re create :-
1. I Created a table
2. Added records to it
3. Updated and deleted records, hoping to create free pages
I have documented same here.
PostgreSQL :- Create free pages in Table
https://learningintheopen.org/2019/08/12/postgresql-create-freepages-in-table/
Sorry it is my first time creating a bug/issue and I am not really used to the forum.
Best,
Daniel Adeniji
________________________________
From: Jeff Janes <jeff.janes@gmail.com>
Sent: Monday, August 12, 2019 1:38 PM
To: daniel_adeniji@hotmail.com <daniel_adeniji@hotmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #15950: pg_freespace.avail is 0
On Mon, Aug 12, 2019 at 2:08 PM PG Bug reporting form <noreply@postgresql.org<mailto:noreply@postgresql.org>> wrote:
The following bug has been logged on the website:
Bug reference: 15950
Logged by: Daniel Adeniji
Email address: daniel_adeniji@hotmail.com<mailto:daniel_adeniji@hotmail.com>
PostgreSQL version: 11.4
Operating system: Windows 10
Description:
Issued the query pasted below :-
...
Result :-
=========
1) pg_freespace.avail is 0
Your query didn't even return pg_freespace.avail. It returned some convoluted thing that might depend on it.
Why is this a bug? Why is 0 wrong? What should it be instead? If pg_freespace.avail is wrong, why not how a query that shows that, rather than something else tangentially related to it? What is the actual output of the query you do show?
Cheers,
Jeff
Import Notes
Reply to msg id not found: BYAPR20MB2456D8044394B3CF8864AE58FFD30@BYAPR20MB2456.namprd20.prod.outlook.com