BUG #15950: pg_freespace.avail is 0

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

BUG #15950: pg_freespace.avail is 0

PG Bug reporting form
The following bug has been logged on the website:

Bug reference:      15950
Logged by:          Daniel Adeniji
Email address:      [hidden email]
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

Reply | Threaded
Open this post in threaded view
|

Re: BUG #15950: pg_freespace.avail is 0

Jeff Janes
On Mon, Aug 12, 2019 at 2:08 PM PG Bug reporting form <[hidden email]> wrote:
The following bug has been logged on the website:

Bug reference:      15950
Logged by:          Daniel Adeniji
Email address:      [hidden email]
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
Reply | Threaded
Open this post in threaded view
|

Re: BUG #15950: pg_freespace.avail is 0

Daniel Adeniji
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


Best,

Daniel Adeniji


From: Daniel Adeniji <[hidden email]>
Sent: Monday, August 12, 2019 2:04 PM
To: Jeff Janes <[hidden email]>; [hidden email] <[hidden email]>
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


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 <[hidden email]>
Sent: Monday, August 12, 2019 1:38 PM
To: [hidden email] <[hidden email]>; [hidden email] <[hidden email]>
Subject: Re: BUG #15950: pg_freespace.avail is 0
 
On Mon, Aug 12, 2019 at 2:08 PM PG Bug reporting form <[hidden email]> wrote:
The following bug has been logged on the website:

Bug reference:      15950
Logged by:          Daniel Adeniji
Email address:      [hidden email]
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