Обсуждение: Returning SELECTed rows immediately instead of all at the end?

I'm running amcheck on a set of indices (test machine, not prod) and want to 
track the progress.  Is there a SELECT clause that makes rows display as 
they are created, or do I have to explicitly call bt_index_check() from a 
shell script or SQL function in order to see the output as each index is 
checked?


postgres=# select version();
                     version
------------------------------------------------------
PostgreSQL 9.6.18 on [snip] (Red Hat 4.4.7-23), 64-bit
(1 row)

postgres=# \dx
                            List of installed extensions
      Name     | Version |   Schema   | Description
--------------+---------+------------+--------------------------------------------
amcheck_next | 2       | public     | functions for verifying relation integrity
plpgsql      | 1.0     | pg_catalog | PL/pgSQL procedural language

#!/bin/bash
echo `date +"%F %T, %a"` Starting
psql CDSLBXW -c \
"SELECT clock_timestamp(),
         bt_index_check(c.oid, i.indisunique),
         n.nspname,
         c.relname ,
         c.relpages
FROM pg_index i
        JOIN pg_opclass op ON i.indclass[0] = op.oid
        JOIN pg_am am ON op.opcmethod = am.oid
        JOIN pg_class c ON i.indexrelid = c.oid
        JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE am.amname = 'btree'
   AND c.relpersistence != 't'
   AND c.relkind = 'i'
   AND i.indisready
   AND i.indisvalid
ORDER BY c.relpages desc
;"

echo `date +"%F %T, %a"` Finished



-- 
Angular momentum makes the world go 'round.

I'm running amcheck on a set of indices (test machine, not prod) and want to track the progress.  Is there a SELECT clause that makes rows display as they are created,

or do I have to explicitly call bt_index_check() from a shell script or SQL function in order to see the output as each index is checked?

Ron <ronljohnsonjr@gmail.com> writes:
> I'm running amcheck on a set of indices (test machine, not prod) and want to
> track the progress.  Is there a SELECT clause that makes rows display as
> they are created, or do I have to explicitly call bt_index_check() from a
> shell script or SQL function in order to see the output as each index is
> checked?

If you're willing to code at the libpq level, see

https://www.postgresql.org/docs/current/libpq-single-row-mode.html

            regards, tom lane

On 6/13/20 12:10 AM, Tom Lane wrote:
> Ron <ronljohnsonjr@gmail.com> writes:
>> I'm running amcheck on a set of indices (test machine, not prod) and want to
>> track the progress.  Is there a SELECT clause that makes rows display as
>> they are created, or do I have to explicitly call bt_index_check() from a
>> shell script or SQL function in order to see the output as each index is
>> checked?
> If you're willing to code at the libpq level, see
>
> https://www.postgresql.org/docs/current/libpq-single-row-mode.html

An option (maybe "-r") for psql to put it in single row mode would be useful 
for DBAs.

-- 
Angular momentum makes the world go 'round.

On Friday, June 12, 2020, Ron <ronljohnsonjr@gmail.com> wrote:

I'm running amcheck on a set of indices (test machine, not prod) and want to track the progress.  Is there a SELECT clause that makes rows display as they are created,

No

 

or do I have to explicitly call bt_index_check() from a shell script or SQL function in order to see the output as each index is checked?

You could wrap the function call in a custom plpgsql function (or just do a plpgsql loop) and side channel output via notice but i’d probably just do a shell script wrapper absent any other constraint.