Thread: function defined (or not), more worries on version 10->14 upgrade

barnard=# \df genome_threshold_mono
                                                 List of functions
 Schema |         Name          | Result data type |                  Argument data types                   | Type
--------+-----------------------+------------------+--------------------------------------------------------+------
 public | genome_threshold_mono | uuid             | pbs_name text, genome_name text                        | func
 public | genome_threshold_mono | uuid             | pbs_name text, genome_name text, conf double precision | func
(2 rows)

barnard=# \df genome_threshold_mono(pbs_name text, genome_name text, conf double precision);
ERROR:  invalid regular expression: parentheses () not balanced

barnard=# set search_path = shoc,base,public, bulk;
SET
barnard=# begin;
BEGIN
barnard=# select genome_threshold_mono('asdf', 'qewq');
NOTICE:  group id is <NULL>
ERROR:  Could not find markerset for qewq
CONTEXT:  PL/pgSQL function threshold_process(uuid,text,boolean) line 11 at RAISE
SQL statement "select threshold_process(tid, genome_name, false)"
PL/pgSQL function genome_threshold_mono(text,text,double precision) line 22 at SQL statement
SQL statement "select genome_threshold_mono(pbs_name, genome_name, 1.96)"
PL/pgSQL function genome_threshold_mono(text,text) line 5 at SQL statement
barnard=# rollback;
ROLLBACK
barnard=# \df genome_threshold_mono(pbs_name text, genome_name text, conf double precision);
ERROR:  invalid regular expression: parentheses () not balanced

On 4/15/22 9:53 AM, Rob Sargent wrote:
> The function in question is working but I'm having trouble getting at 
> it's current definition and the possibility that there are more v10->v14 
> issues in this database.  The last ERROR: below seems to me "a system 
> thing"(tm)
> 
> 
> 
>     barnard=# \df genome_threshold_mono
>                                                       List of functions
>       Schema |         Name          | Result data type
>     |                  Argument data types                   | Type
>
--------+-----------------------+------------------+--------------------------------------------------------+------
>       public | genome_threshold_mono | uuid             | pbs_name text,
>     genome_name text                        | func
>       public | genome_threshold_mono | uuid             | pbs_name text,
>     genome_name text, conf double precision | func
>     (2 rows)
> 
>     barnard=# \df genome_threshold_mono(pbs_name text, genome_name text,
>     conf double precision);
>     ERROR:  invalid regular expression: parentheses () not balanced

Don't use the column names just the data types:

\df genome_threshold_mono(text, text, double precision);

> The supplied arguments to the function in question are obviously bogus, 
> but the reaction is correct, including call to nested functions.


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 4/15/22 9:53 AM, Rob Sargent wrote:
> The function in question is working but I'm having trouble getting at 
> it's current definition and the possibility that there are more v10->v14 
> issues in this database.  The last ERROR: below seems to me "a system 
> thing"(tm)

>     barnard=# \df genome_threshold_mono(pbs_name text, genome_name text,
>     conf double precision);
>     ERROR:  invalid regular expression: parentheses () not balanced

Previous post left out the documentation:

https://www.postgresql.org/docs/current/app-psql.html

See **...** portion, my addition.

\df[anptwS+] [ pattern [ arg_pattern ... ] ]

     Lists functions, together with their result data types, argument 
data types, and function types, which are classified as “agg” 
(aggregate), “normal”, “procedure”, “trigger”, or “window”. To display 
only functions of specific type(s), add the corresponding letters a, n, 
p, t, or w to the command. If pattern is specified, only functions whose 
names match the pattern are shown. **Any additional arguments are 
type-name patterns, which are matched to the type names of the first, 
second, and so on arguments of the function. (Matching functions can 
have more arguments than what you specify. To prevent that, write a dash 
- as the last arg_pattern.)** By default, only user-created objects are 
shown; supply a pattern or the S modifier to include system objects. If 
the form \df+ is used, additional information about each function is 
shown, including volatility, parallel safety, owner, security 
classification, access privileges, language, source code and description.



-- 
Adrian Klaver
adrian.klaver@aklaver.com

Rob Sargent <robjsargent@gmail.com> writes:
> The function in question is working but I'm having trouble getting at 
> it's current definition and the possibility that there are more v10->v14 
> issues in this database.  The last ERROR: below seems to me "a system 
> thing"(tm)

Hmm, it looks like \df does not cope well with spaces in the argument
list:

regression=# \df genome_threshold_mono(text,text,double)
                       List of functions
 Schema | Name | Result data type | Argument data types | Type 
--------+------+------------------+---------------------+------
(0 rows)

regression=# \df genome_threshold_mono(text,text, double)
ERROR:  invalid regular expression: parentheses () not balanced

It's sending the server a bogus pattern in the second case.
I've not looked at the code yet, but this does seem like a
psql (not server) bug.

As Adrian noted, you're not supposed to use parameter names
in \df, only their types.  It seems like whitespace ought
to be allowed though.

            regards, tom lane

> On Apr 15, 2022, at 11:14 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Rob Sargent <robjsargent@gmail.com> writes:
>> The function in question is working but I'm having trouble getting at
>> it's current definition and the possibility that there are more v10->v14
>> issues in this database.  The last ERROR: below seems to me "a system
>> thing"(tm)
>
> Hmm, it looks like \df does not cope well with spaces in the argument
> list:
>
> regression=# \df genome_threshold_mono(text,text,double)
>                       List of functions
> Schema | Name | Result data type | Argument data types | Type
> --------+------+------------------+---------------------+------
> (0 rows)
>
> regression=# \df genome_threshold_mono(text,text, double)
> ERROR:  invalid regular expression: parentheses () not balanced
>
> It's sending the server a bogus pattern in the second case.
> I've not looked at the code yet, but this does seem like a
> psql (not server) bug.
>
> As Adrian noted, you're not supposed to use parameter names
> in \df, only their types.  It seems like whitespace ought
> to be allowed though.
>
>            regards, tom lane
Thank you. Much relieved.

The no-space-no-names thing is suspicious. Away from desk just now but sure I’ve successfully cutpasted the arg list
into\df calls   Will test shortly 

On 4/15/22 10:14, Tom Lane wrote:
> Rob Sargent <robjsargent@gmail.com> writes:
>> The function in question is working but I'm having trouble getting at
>> it's current definition and the possibility that there are more v10->v14
>> issues in this database.  The last ERROR: below seems to me "a system
>> thing"(tm)
> 
> Hmm, it looks like \df does not cope well with spaces in the argument
> list:
> 
> regression=# \df genome_threshold_mono(text,text,double)
>                         List of functions
>   Schema | Name | Result data type | Argument data types | Type
> --------+------+------------------+---------------------+------
> (0 rows)
> 
> regression=# \df genome_threshold_mono(text,text, double)
> ERROR:  invalid regular expression: parentheses () not balanced
> 
> It's sending the server a bogus pattern in the second case.
> I've not looked at the code yet, but this does seem like a
> psql (not server) bug.
> 

Seems it also has another white space problem:

\df upc_check_digit()
                               List of functions
  Schema |      Name       | Result data type  |  Argument data types  | 
Type
--------+-----------------+-------------------+-----------------------+------
  public | upc_check_digit | character varying | upc character varying | 
func

\df upc_check_digit ()
                        List of functions
  Schema | Name | Result data type | Argument data types | Type
--------+------+------------------+---------------------+------
(0 rows)

\df upc_check_digit(varchar)
                        List of functions
  Schema | Name | Result data type | Argument data types | Type
--------+------+------------------+---------------------+------
(0 rows)

\df upc_check_digit (varchar)
                               List of functions
  Schema |      Name       | Result data type  |  Argument data types  | 
Type
--------+-----------------+-------------------+-----------------------+------
  public | upc_check_digit | character varying | upc character varying | 
func
(1 row)


> 
>             regards, tom lane
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 4/15/22 10:14, Tom Lane wrote:

Rob Sargent <robjsargent@gmail.com> writes:

The function in question is working but I'm having trouble getting at
it's current definition and the possibility that there are more v10->v14
issues in this database.  The last ERROR: below seems to me "a system
thing"(tm)

Hmm, it looks like \df does not cope well with spaces in the argument
list:

regression=# \df genome_threshold_mono(text,text,double)
                        List of functions
  Schema | Name | Result data type | Argument data types | Type
--------+------+------------------+---------------------+------
(0 rows)

regression=# \df genome_threshold_mono(text,text, double)
ERROR:  invalid regular expression: parentheses () not balanced

It's sending the server a bogus pattern in the second case.
I've not looked at the code yet, but this does seem like a
psql (not server) bug.

Seems it also has another white space problem:

\df upc_check_digit()
                              List of functions
 Schema |      Name       | Result data type  |  Argument data types  | Type
--------+-----------------+-------------------+-----------------------+------
 public | upc_check_digit | character varying | upc character varying | func

\df upc_check_digit ()
                       List of functions
 Schema | Name | Result data type | Argument data types | Type
--------+------+------------------+---------------------+------
(0 rows)

\df upc_check_digit(varchar)
                       List of functions
 Schema | Name | Result data type | Argument data types | Type
--------+------+------------------+---------------------+------
(0 rows)

\df upc_check_digit (varchar)
                              List of functions
 Schema |      Name       | Result data type  |  Argument data types  | Type
--------+-----------------+-------------------+-----------------------+------
 public | upc_check_digit | character varying | upc character varying | func
(1 row)

barnard=# \df genome_threshold_mono(text,text,double precision);
ERROR:  invalid regular expression: parentheses () not balanced
barnard=# \df genome_threshold_mono(text,text,double precision);
ERROR:  invalid regular expression: parentheses () not balanced
barnard=# \df genome_threshold_mono(text,text,float);
                       List of functions
 Schema | Name | Result data type | Argument data types | Type
--------+------+------------------+---------------------+------
(0 rows)

barnard=# \df genome_threshold_mono(text,text,'double precision');
                       List of functions
 Schema | Name | Result data type | Argument data types | Type
--------+------+------------------+---------------------+------
(0 rows)

barnard=# \df genome_threshold_mon*
                                                 List of functions
 Schema |         Name          | Result data type |                  Argument data types                   | Type
--------+-----------------------+------------------+--------------------------------------------------------+------
 public | genome_threshold_mono | uuid             | pbs_name text, genome_name text                        | func
 public | genome_threshold_mono | uuid             | pbs_name text, genome_name text, conf double precision | func
(2 rows)

barnard=# \df genome_threshold_mono (text,text,double precision);
                                                 List of functions
 Schema |         Name          | Result data type |                  Argument data types                   | Type
--------+-----------------------+------------------+--------------------------------------------------------+------
 public | genome_threshold_mono | uuid             | pbs_name text, genome_name text                        | func
 public | genome_threshold_mono | uuid             | pbs_name text, genome_name text, conf double precision | func
(2 rows)

\df: extra argument "(text,text,double" ignored
\df: extra argument "precision);" ignored
barnard=# \df genome_threshold_mono (text,text,float);
                                                 List of functions
 Schema |         Name          | Result data type |                  Argument data types                   | Type
--------+-----------------------+------------------+--------------------------------------------------------+------
 public | genome_threshold_mono | uuid             | pbs_name text, genome_name text                        | func
 public | genome_threshold_mono | uuid             | pbs_name text, genome_name text, conf double precision | func
(2 rows)

\df: extra argument "(text,text,float);" ignored

barnard=# \sf shoc.pvr(shoc.segment, float)
CREATE OR REPLACE FUNCTION shoc.pvr(seg shoc.segment, plus double precision DEFAULT 0.0)
 RETURNS double precision
 LANGUAGE sql
AS $function$
  select ((1.0*seg.events_equal)+seg.events_greater+plus)/((1.0*seg.events_less)+seg.events_equal+seg.events_greater+plus)::float as result;
$function$

            regards, tom lane

Rob Sargent <robjsargent@gmail.com> writes:
> I'm about to unleash new versions of the above and related functions 
> (args will change), so a drops are imminent. Any reason to hold off on that?

While I've not yet looked at the code, I've got no reason to think
this is anything except fragile argument parsing in \df and \sf.

            regards, tom lane

On Fri, Apr 15, 2022 at 2:04 PM Rob Sargent <robjsargent@gmail.com> wrote:

but have not found a combination of name/args for genome_threshold_mono(text,text,double precision)

Per the fine documentation:

\df[anptwS+] [ pattern [ arg_pattern ... ] ]

So the correct formulation is:

\df genome_threshold_mono text text 'double precision'

You need to quote the fourth argument to protect the embedded space as a character and not an argument separator.

David J.

barnard=# \df public.genome_threshold_mono text,text
                                                 List of functions
 Schema |         Name          | Result data type |                  Argument data types                   | Type
--------+-----------------------+------------------+--------------------------------------------------------+------
 public | genome_threshold_mono | uuid             | pbs_name text, genome_name text                        | func
 public | genome_threshold_mono | uuid             | pbs_name text, genome_name text, conf double precision | func
(2 rows)

barnard=# \sf public.genome_threshold_mono text,text
ERROR:  invalid name syntax
barnard=# \sf+ public.genome_threshold_mono text,text
ERROR:  invalid name syntax
barnard=# \sf+ public.genome_threshold_mono(text,text)
        CREATE OR REPLACE FUNCTION public.genome_threshold_mono(pbs_name text, genome_name text)
         RETURNS uuid
         LANGUAGE plpgsql
1       AS $function$
2       declare
3         tid uuid;
4       begin
5         select genome_threshold_mono(pbs_name, genome_name, 1.96) into tid;
6         return tid;
7       end;
8       $function$

On Fri, Apr 15, 2022 at 2:30 PM Rob Sargent <robjsargent@gmail.com> wrote:

On 4/15/22 15:18, David G. Johnston wrote:

On Fri, Apr 15, 2022 at 2:04 PM Rob Sargent <robjsargent@gmail.com> wrote:

but have not found a combination of name/args for genome_threshold_mono(text,text,double precision)

Per the fine documentation:

\df[anptwS+] [ pattern [ arg_pattern ... ] ]

So the correct formulation is:

\df genome_threshold_mono text text 'double precision'

You need to quote the fourth argument to protect the embedded space as a character and not an argument separator.

David J.

Wow.  An old bad habit of using parens, I guess?

Do you consider this correct behaviour?

barnard=# \df public.genome_threshold_mono text,text
                                                 List of functions
 Schema |         Name          | Result data type |                  Argument data types                   | Type
--------+-----------------------+------------------+--------------------------------------------------------+------
 public | genome_threshold_mono | uuid             | pbs_name text, genome_name text                        | func
 public | genome_threshold_mono | uuid             | pbs_name text, genome_name text, conf double precision | func
(2 rows)

I cannot reproduce that on head:

\df test_function text

                                List of functions
 Schema |     Name      | Result data type |     Argument data types      | Type
--------+---------------+------------------+------------------------------+------
 public | test_function | text             | text, double precision       | func
 public | test_function | text             | text, text, double precision | func
(2 rows)

\df test_function text,text
                       List of functions
 Schema | Name | Result data type | Argument data types | Type
--------+------+------------------+---------------------+------
(0 rows)

i.e, is it automatically wildcarded?

No, easy enough to prove, just mis-spell "text" and "text' in the first example

 

And then, I dare say, this is just plain inconsistent, vis parens

barnard=# \sf public.genome_threshold_mono text,text
ERROR:  invalid name syntax

So what?  \df and \sf have different goals in life; it should be expected they behave differently.  Specifically, \sf doesn't use patterns while \df does.

David J.

 

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Fri, Apr 15, 2022 at 2:20 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> While I've not yet looked at the code, I've got no reason to think
>> this is anything except fragile argument parsing in \df and \sf.

> I'm unsure about the "extra argument ignored" bit but the rest of "not
> found" issues are due to operator error; specifying a pattern that tries to
> match the name and arguments at the same time when that isn't how the
> meta-command is defined to be used.

Ah, right, and indeed the code is just parsing off space-separated
arguments one at a time.  It's unfortunate that this is done so
much differently from \sf.

One could imagine, perhaps, checking for parentheses in the arguments
and parsing differently depending on whether they're there.  But that's
getting into the realm of a new definition rather than a bug fix.

            regards, tom lane

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> ... And if I know something exists
> in the DB and I want details, doing a name-only search followed by \sf
> seems like it would work well enough.

It could work better though.  For example,

regression=# \df *backup*
                                                                    List of functions
   Schema   |      Name       | Result data type |                                      Argument data types
                         | Type  

------------+-----------------+------------------+------------------------------------------------------------------------------------------------+------
 pg_catalog | pg_backup_start | pg_lsn           | label text, fast boolean DEFAULT false
                         | func 
 pg_catalog | pg_backup_stop  | record           | wait_for_archive boolean DEFAULT true, OUT lsn pg_lsn, OUT labelfile
text,OUT spcmapfile text | func 
(2 rows)

regression=# \sf pg_backup_start (label text, fast boolean DEFAULT false)
ERROR:  syntax error at or near "text"

It seems annoying that I can't copy-and-paste \df's output to invoke \sf.

Moreover, once I peel that down to what will work:

regression=# \sf pg_backup_start (text,  boolean )
CREATE OR REPLACE FUNCTION pg_catalog.pg_backup_start(label text, fast boolean DEFAULT false)
 RETURNS pg_lsn
 LANGUAGE internal
 PARALLEL RESTRICTED STRICT
AS $function$pg_backup_start$function$

it's apparent that \sf isn't even consistent with *itself*.
So I agree that there's room for some polishing here.

I'm not sure how far we can go without breaking backwards compatibility.
OTOH, it looks like \df's ability to consider parameters at all is new
as of v14, so maybe the details don't have a huge constituency yet.

            regards, tom lane