function_returns error (plus comparisons between v1.1 and v1.3)

[kbrannen]

Just started working on a project to upgrade from Pg v13 to v18, including Pgtap v1.1 to v1.3. Mostly stuff is just working, but I've got 15 Pgtap tests failing (in 3 categories), which I find surprising. Of course, it can be hard to tell if the problem is revving Pg or Pgtap or both. :-/ To be clear, no errors with Pg v13 + Pgtap v1.1, while errors with Pg v18 + Pgtap v1.3.

Prob 1:
table col definition:
insert_ts | timestamp with time zone | | | CURRENT_TIMESTAMP
pgtap test:
SELECT col_default_is('alarm_queue', 'insert_ts', 'now()');
output:

   # Failed test 12: "Column alarm_queue.insert_ts should default to 'now()'"
   #         have: CURRENT_TIMESTAMP
   #         want: now()

Thoughts:
Honestly, I think the newer stuff is more correct, but I find it interesting/strange that CT used to equal now() but no longer does. Not a problem to solve in Pgtap but in my tests, but I wanted to highlight the change.

Prob 2:
table col def:
when_modified | timestamp(0) with time zone | | not null | CURRENT_TIMESTAMP
pgtap test:
SELECT col_default_is( 'device_software', 'when_modified', CURRENT_TIMESTAMP );
output:

   psql:device_software.pg:50: ERROR:  function is(text, timestamp with time zone, text) does not exist
LINE 1: is( $1, $3, $4 )
        ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:  is( $1, $3, $4 )
CONTEXT:  PL/pgSQL function _def_is(text,text,anyelement,text) line 7 at RETURN
PL/pgSQL expression "_def_is(
        pg_catalog.pg_get_expr(d.adbin, d.adrelid),
        pg_catalog.format_type(a.atttypid, a.atttypmod),
        $3, $4
    )
      FROM pg_catalog.pg_class c, pg_catalog.pg_attribute a, pg_catalog.pg_attrdef d
     WHERE c.oid = a.attrelid
       AND pg_table_is_visible(c.oid)
       AND a.atthasdef
       AND a.attrelid = d.adrelid
       AND a.attnum = d.adnum
       AND c.relname = $1
       AND a.attnum > 0
       AND NOT a.attisdropped
       AND a.attname = $2"
PL/pgSQL function _cdi(name,name,anyelement,text) line 13 at RETURN

thoughts:
The problem here seems to be that I needed to quote the CT value to make it a string. I have a vague memory of this happening back at Pgtap v1.0 or so (or somewhere back in time), but I find it interesting that it's rearing it's head again. So I can fix the test but FYI this is happening and the error message is a bit scary. :)

Prob 3:
pgtap test:
SELECT function_returns( 'public', 'remove_old_alarms', ARRAY[''], 'void' );
-- and a bunch more like it with the same arg3
output:

psql:Functions/alarm_funcs.pg:7: ERROR:  invalid type name ""
CONTEXT:  PL/pgSQL function _funkargs(name[]) line 3 at RETURN
SQL function "_returns" statement 1

thoughts:
I have no idea what that's trying to tell me, but if I comment out the line above then the test works overall. That tells me that the array is somehow a problem, none of the other tests have that as arg 3. I have other test files with the same issue.
More investigating shows the function is defined as:
CREATE OR REPLACE FUNCTION public.remove_old_alarms() RETURNS void ...
According to the Pgtap docs which say "(pass an empty array to specify a function with an empty signature)", I should make arg3 ARRAY[ ], when I do that I get:

alarm_funcs.pg .. psql:alarm_funcs.pg:7: ERROR:  cannot determine type of empty array
LINE 1: ..._returns( 'public', 'remove_old_alarms', ARRAY[], '...
                                                    ^
HINT:  Explicitly cast to the desired type, for example ARRAY[]::integer[]

Hmm, except I have nothing, so how do I specify that? The docs don't say and if I use any normal type that's an error too. At the least this is a doc error, but maybe also a coding error also.
--Yes, this really should be a procedure, but I'd think it'd have the same problem.

So I can fix prob 1 & 2, but I'm going to need help on prob 3 since I'm doing what docs say (I think) but that's not working.

Thanks, Kevin

[kbrannen]

I've just found 1 solution to prob 3, but I don't know if it's the correct one or not.

I can do ARRAY[]::varchar[] and the error goes away. I don't understand that though because if I use any other type like ARRAY[]::integer[] it will fail. Is varchar special in this case?

No matter if my "guess" is correct or not, we need a doc change here to show an example so people don't have to guess.

[theory]

1. Interesting. Postgres must have changed this.
2. col_default_is() expects the default value to be a string representing the default expression; what you've done here is called CURRENT_TIMESTAMP, which returns the current TIMESTAMPTZ, which is a value, not an expression. Make it SELECT col_default_is( 'device_software', 'when_modified', 'CURRENT_TIMESTAMP' );
3. When you pass ARRAY[''], it's a saying it's a single argument with the type ''. of course there is no such type; hence ERROR: invalid type name "". If it takes no arguments, you need to pass an empty array. The argument is not actual types, but type names, so must be a string (or, rather, name. So if there are no arguments of any type, pass, ARRAY[]::NAME (or '{}'::NAME[]).