Thread: 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.
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.
David J.
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 6/13/20 12:04 AM, David G. Johnston wrote:
Yeah, a shell script was my next option. Thanks.
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,Noor 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.
Yeah, a shell script was my next option. Thanks.
--
Angular momentum makes the world go 'round.
Angular momentum makes the world go 'round.