Horton

Re: Converting contrib SQL functions to new style

Mark Dilger <mark.dilger@enterprisedb.com>

View thread View on postgr.es
From: Mark Dilger <mark.dilger@enterprisedb.com>
To: Vik Fearing <vik@postgresfriends.org>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, Peter Eisentraut <peter.eisentraut@enterprisedb.com>, Robert Haas <robertmhaas@gmail.com>
Date: 2021-04-14T22:18:48Z
Lists: pgsql-hackers 

> On Apr 14, 2021, at 2:47 PM, Vik Fearing <vik@postgresfriends.org> wrote:
> 
> On 4/14/21 7:36 PM, Tom Lane wrote:
>> Mark Dilger <mark.dilger@enterprisedb.com> writes:
>>>> On Apr 13, 2021, at 3:26 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>>> However I think we may still need an assumption that earthdistance
>>>> and cube are in the same schema --- any comments on that?
>> 
>>> This is probably not worth doing, and we are already past feature
>>> freeze, but adding syntax to look up the namespace of an extension might
>>> help.
>> 
>> Yeah, that idea was discussed before (perhaps only in private
>> security-team threads, though).  We didn't do anything about it because
>> at the time there didn't seem to be pressing need, but in the context
>> of SQL function bodies there's an obvious use-case.
>> 
>>> We could get something like this working just inside the CREATE EXTENSION command if we expanded on the @extschema@ idea a bit.  At first I thought this idea would suffer race conditions with concurrent modifications of pg_extension or pg_namespace, but it looks like we already have a snapshot when processing the script file, so:
>> 
>>> -CREATE DOMAIN earth AS cube
>>> +CREATE DOMAIN @@earthdistance@@::earth AS @@cube@@::cube
>> 
>> Right, extending the @extschema@ mechanism is what was discussed,
>> though I think I'd lean towards something like @extschema:cube@
>> to denote the schema of a referenced extension "cube".
>> 
>> I'm not sure this is useful enough to break feature freeze for,
>> but I'm +1 for investigating it for v15.
> Just like we have a pseudo "$user" schema, could we have a pseudo
> "$extension" catalog?  That should avoid changing grammar rules too much.
> 
> CREATE TABLE unaccented_words (
>    word "$extension".citext.citext,
>    CHECK (word = "$extension".unaccent.unaccent(word)
> );

Having a single variable $extension might help in many cases, but I don't see how to use it to handle the remaining cross-extension references, such as earthdistance needing to reference cube.

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Commits

  1. Use @extschema:name@ notation in contrib transform modules.

  2. pg_freespacemap: Fix declaration of pg_freespace(regclass)

  3. contrib/pageinspect: Use SQL-standard function bodies.

  4. contrib/xml2: Use SQL-standard function bodies.

  5. contrib/citext: Use SQL-standard function bodies.

  6. contrib/earthdistance: Use SQL-standard function bodies.

  7. contrib/lo: Use SQL-standard function bodies

  8. xml2: Add tests for functions xpath_nodeset() and xpath_list()

  9. contrib/lo: Add test for function lo_oid()

  10. pg_freespacemap: Use SQL-standard function bodies

  11. Add @extschema:name@ and no_relocate options to extensions.

  12. Make contrib modules' installation scripts more secure.