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

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 14:22, Adrian Klaver wrote:

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)

Perhaps beating a dead horse, but

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)

space after function name

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

I can get some definitions, eg

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$

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

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?

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.

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:20 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

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.

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.

David J.

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.e, is it automatically wildcarded?

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

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$

But I'm taking it all as not an upgrade problem, getting back to work
Thanks to all,
rjs

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.

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

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)

Isn't the above example exactly my case?  It seems to work as "match at
least these leading args" which in my mind appends a silent ",*".  But
that's just me.

\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.

Do you take it as inconsequential that they both deal with specifics of
named functions and both make use of that functions argument list and
are part of the same meta-function world within psql?

"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

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

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

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)

Isn't the above example exactly my case? It seems to work as "match at
least these leading args" which in my mind appends a silent ",*". But
that's just me.

name = pattern AND
arg1 = pattern

Now, if you simply omit the pattern for arg2 a two-arg function with the
correct first argument will still match, no wildcards needed - absence of a
third test is how it works.

I get you want this to behave like a single pattern match:
function_signature ~ some_pattern

But it doesn't.

This way is arguably easier for someone who doesn't know regexes well to
use.

Maybe there is value in implementing something like what you are expecting,
which is a bit closer in spirit to what \sf and \ef do, here: but I
wouldn't wager on it happening.

Do you take it as inconsequential that they both deal with specifics of

named functions and both make use of that functions argument list and are
part of the same meta-function world within psql?

I find this particular inconsistency to be a reasonable one. I'm not
against improving usability here but also am not going to fight for it
either. Partially because I tend to find doing work in text files under
version control is much better than "\ef". 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.

David J.

"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