Re: pg_amcheck option to install extension

On 4/17/21 3:43 PM, Mark Dilger wrote:
>
>> On Apr 16, 2021, at 11:06 AM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>>
>>
>> Hi,
>>
>> Peter Geoghegan suggested that I have the cross version upgrade checker
>> run pg_amcheck on the upgraded module. This seemed to me like a good
>> idea, so I tried it, only to find that it refuses to run unless the
>> amcheck extension is installed. That's fair enough, but it also seems to
>> me like we should have an option to have pg_amcheck install the
>> extension is it's not present, by running something like 'create
>> extension if not exists amcheck'. Maybe in such a case there could also
>> be an option to drop the extension when pg_amcheck's work is done - I
>> haven't thought through all the implications.
>>
>> Given pg_amcheck is a new piece of work I'm not sure if we can sneak
>> this in under the wire for release 14. I will certainly undertake to
>> review anything expeditiously. I can work around this issue in the
>> buildfarm, but it seems like something other users are likely to want.
> We cannot quite use a "create extension if not exists amcheck" command, as we clear the search path and so must specify the schema to use. Should we instead avoid clearing the search path for this? What are the security implications of using the first schema of the search path?
>
> When called as `pg_amcheck --install-missing`, the implementation in the attached patch runs per database being checked a "create extension if not exists amcheck with schema public". If called as `pg_amcheck --install-missing=foo`, it instead runs "create extension if not exists amcheck with schema foo` having escaped "foo" appropriately for the given database. There is no option to use different schemas for different databases. Nor is there any option to use the search path. If somebody needs that, I think they can manage installing amcheck themselves.

how about specifying pg_catalog as the schema instead of public?

>
> Does this meet your needs for v14? I'd like to get this nailed down quickly, as it is unclear to me that we should even be doing this so late in the development cycle.

I'm ok with or without - I'll just have the buildfarm client pull a list
of databases and install the extension in all of them.

>
> I'd also like your impressions on whether we're likely to move contrib/amcheck into core anytime soon. If so, is it worth adding an option that we'll soon need to deprecate?

I think if it stays as an extension it will stay in contrib. But it sure
feels very odd to have a core bin program that relies on a contrib
extension. It seems one or the other is misplaced.

cheers

andrew

--
Andrew Dunstan
EDB: https://www.enterprisedb.com