Could not load completions #867

dfpolo · 2023-12-14T21:24:47Z

You can use this template for reporting bugs.
Feel free to remove parts that are not relevant, or just write something free-form if you prefer.

What did you do?

I typed an schema or table alias name
Then I typed the dot symbol expecting the completion help list

What did you expect to happen?

I expected to see de popup list with the table names or field names

What actually happened?

I got an error

What software versions are you using?

Postico version:
Version 2.1 dev (9670)

macOS version:
Sonoma 14.2 (23C64)

PostgreSQL version:
PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.60353

jakob · 2023-12-20T11:54:54Z

It looks like there is a problem with the catalog queries on Redshift. Autocomplete works fine on my test cluster, so there must be something about your database that breaks it.

I've improved the error message to show more details. Can you update to the latest build, and try using autocomplete again? The error message should now have a "Show Details" button. Please submit the full error details!

dfpolo · 2023-12-20T14:24:38Z

@jakob the full error msg:

PG_DIAG_SEVERITY: ERROR
PG_DIAG_SQLSTATE: 0A000
PG_DIAG_MESSAGE_PRIMARY: null array elements not supported
PG_DIAG_SOURCE_FILE: ../src/pg/src/backend/utils/adt/arrayfuncs.c
PG_DIAG_SOURCE_LINE: 3491
PG_DIAG_SOURCE_FUNCTION: accumArrayResult

SELECT
    pg_namespace.nspname
    ,rel.relname
    ,rel.relkind
    ,rel.oid
    ,NULL as extname
    
     ,obj_description(rel.oid, 'pg_class') AS comment 
    
        
        	,COALESCE(
        		array(SELECT attname              FROM pg_attribute WHERE (attnum>0 OR attname='oid') AND attrelid=rel.oid AND NOT attisdropped
			 ORDER BY attnum)
        		,array(SELECT col_name 			 FROM pg_get_late_binding_view_cols() cols(view_schema name, view_name name, col_name name, col_type varchar, col_num int)
			 WHERE view_schema = pg_namespace.nspname and view_name = rel.relname)
        		,'{}' -- late binding views may have no columns
        	) AS column_names
        	,COALESCE(
        		array(SELECT format_type(atttypid, atttypmod)              FROM pg_attribute WHERE (attnum>0 OR attname='oid') AND attrelid=rel.oid AND NOT attisdropped
			 ORDER BY attnum)
        		,array(SELECT col_type 			 FROM pg_get_late_binding_view_cols() cols(view_schema name, view_name name, col_name name, col_type varchar, col_num int)
			 WHERE view_schema = pg_namespace.nspname and view_name = rel.relname)
        		,'{}'
        	) AS column_types
        	
        		,COALESCE(
        			array(SELECT COALESCE(col_description(attrelid, attnum), '')              FROM pg_attribute WHERE (attnum>0 OR attname='oid') AND attrelid=rel.oid AND NOT attisdropped
			 ORDER BY attnum)
        			,array(SELECT '' 			 FROM pg_get_late_binding_view_cols() cols(view_schema name, view_name name, col_name name, col_type varchar, col_num int)
			 WHERE view_schema = pg_namespace.nspname and view_name = rel.relname)
        			,'{}'
        	) AS column_comments
            
        
    
    
FROM pg_class rel
    
    JOIN pg_namespace ON (rel.relnamespace = pg_namespace.oid )
WHERE rel.relkind IN ('r','v','m','f','p')  GROUP BY rel.relname, rel.relkind, rel.oid

,pg_namespace.nspname
;SELECT
    schemaname AS nspname
    ,tablename AS relname
    ,'redshift_external_table' AS relkind
    , NULL AS oid
     ,NULL AS comment 
    
    	,ARRAY['unsupported'] AS column_names
    	,ARRAY['unsupported'] AS column_types
    	
    	    ,ARRAY['unsupported'] AS column_comments
    	
    
    
FROM svv_external_tables ;SELECT DISTINCT
	nspname
	,proname
	
		,'f' AS prokind
	
	
	, proname || '(' || oidvectortypes(proargtypes) || ')' AS name_and_argtypes
	
		
			
				,ARRAY( SELECT format_type(proargtypes[i], NULL)
				FROM generate_series(0,pronargs-1) indexes(i) ) AS argtypes
			
		
	
	
	,proargnames AS argnames
	
	
		
			,ARRAY( SELECT 'i'::text FROM generate_series(0,pronargs-1) indexes(i) ) AS argmodes
		
	
	
		
			, NULL AS argdefaults
			, pronargs AS defaultless_input_arg_count
		
	
	
		,format_type(pg_type.oid, NULL) AS returntype
		,pg_proc.proretset AS returnsset
	
	
	,NULL AS extname
	
		,obj_description(pg_proc.oid, 'pg_proc') AS comment
	
	
	
	
	
	
	
FROM pg_proc
JOIN pg_namespace ON pronamespace = pg_namespace.oid


	LEFT JOIN pg_description on pg_proc.oid = pg_description.objoid


	LEFT JOIN pg_type on pg_proc.prorettype = pg_type.oid;

jakob · 2023-12-20T14:44:26Z

Thanks a lot. There are three queries. Let's try to narrow down which of them is failing. I'm pretty sure it's not the second one. Can you check which of the two queries is failing?

Query 1 (fetching tables)

SELECT
  pg_namespace.nspname
  ,rel.relname
  ,rel.relkind
  ,rel.oid
  ,NULL as extname
  ,obj_description(rel.oid, 'pg_class') AS comment 
  ,COALESCE(
    array(SELECT attname FROM pg_attribute WHERE (attnum>0 OR attname='oid') AND attrelid=rel.oid AND NOT attisdropped ORDER BY attnum)
    ,array(SELECT col_name FROM pg_get_late_binding_view_cols() cols(view_schema name, view_name name, col_name name, col_type varchar, col_num int) WHERE view_schema = pg_namespace.nspname and view_name = rel.relname)
    ,'{}' -- late binding views may have no columns
  ) AS column_names
  ,COALESCE(
    array(SELECT format_type(atttypid, atttypmod) FROM pg_attribute WHERE (attnum>0 OR attname='oid') AND attrelid=rel.oid AND NOT attisdropped ORDER BY attnum)
    ,array(SELECT col_type FROM pg_get_late_binding_view_cols() cols(view_schema name, view_name name, col_name name, col_type varchar, col_num int) WHERE view_schema = pg_namespace.nspname and view_name = rel.relname)
    ,'{}'
  ) AS column_types
  ,COALESCE(
    array(SELECT COALESCE(col_description(attrelid, attnum), '') FROM pg_attribute WHERE (attnum>0 OR attname='oid') AND attrelid=rel.oid AND NOT attisdropped ORDER BY attnum)
    ,array(SELECT '' FROM pg_get_late_binding_view_cols() cols(view_schema name, view_name name, col_name name, col_type varchar, col_num int) WHERE view_schema = pg_namespace.nspname and view_name = rel.relname)
    ,'{}'
  ) AS column_comments
FROM pg_class rel  
  JOIN pg_namespace ON (rel.relnamespace = pg_namespace.oid )
WHERE rel.relkind IN ('r','v','m','f','p')
GROUP BY rel.relname, rel.relkind, rel.oid, pg_namespace.nspname;

or Query 3 (fetching procedures):

SELECT DISTINCT
	nspname,
	proname,
	'f' AS prokind,
	proname || '(' || oidvectortypes(proargtypes) || ')' AS name_and_argtypes,
	ARRAY ( SELECT format_type(proargtypes[i], NULL) FROM generate_series(0, pronargs - 1) indexes (i)) AS argtypes,
	proargnames AS argnames,
	ARRAY ( SELECT 'i'::text FROM generate_series(0, pronargs - 1) indexes (i)) AS argmodes,
	NULL AS argdefaults,
	pronargs AS defaultless_input_arg_count,
	format_type(pg_type.oid, NULL) AS returntype,
	pg_proc.proretset AS returnsset,
	NULL AS extname,
	obj_description(pg_proc.oid, 'pg_proc') AS comment
FROM
	pg_proc
	JOIN pg_namespace ON pronamespace = pg_namespace.oid
	LEFT JOIN pg_description ON pg_proc.oid = pg_description.objoid
	LEFT JOIN pg_type ON pg_proc.prorettype = pg_type.oid;

dfpolo · 2023-12-20T14:46:34Z

@jakob the Query 3 (fetching procedures) is failing

ERROR: null array elements not supported

dfpolo · 2023-12-20T14:48:21Z

@jakob this line:

ARRAY ( SELECT format_type(proargtypes[i], NULL) FROM generate_series(0, pronargs - 1) indexes (i)) AS argtypes

jakob · 2023-12-20T15:35:33Z

Thank you! It looks like either pronargs doesn't match the proargtypes array, or format_type return NULL for some type.

Can you execute the following queries? One of them should find the problematic procedure:

select proname, oidvectortypes(proargtypes), array_lower(proargtypes,1), array_upper(proargtypes,1), pronargs from pg_proc where array_upper(proargtypes,1) != pronargs - 1;

with procs_args as (
	select proname, oidvectortypes(proargtypes), unnest(proargtypes) as proargtype from pg_proc
)
select * from procs_args where format_type(proargtype, NULL) IS NULL;

If it's a custom function that's causing the problem, could you share the function definition with me? I don't need the full SQL, just the part with the arguments. I'd love to understand what's going on, and maybe be able to find a way to reproduce the problem.

jakob · 2023-12-20T15:45:02Z

Oops, those queries both don't work on Redshift. Sorry. I keep forgetting that Redshift is so limited....

jakob · 2023-12-20T15:52:21Z

This query should find the problematic procedure (and I've tested it on Redshift):

select proname, oidvectortypes(proargtypes), proargtypes from pg_proc where (SELECT bool_or(format_type(proargtypes[i], NULL) IS NULL) FROM generate_series(0, pronargs - 1) indexes (i) );

dfpolo · 2023-12-20T15:57:22Z

@jakob the definitions:

fn_test_model_prediction_function_prob

integer, integer, character varying, integer, integer, timestamp with time zone, bigint, integer, integer, integer, integer, integer, integer, double precision, double precision, integer, bigint, timestamp with time zone, timestamp with time zone, bigint, timestamp with time zone, timestamp with time zone, timestamp with time zone, bigint, bigint, bigint, bigint, bigint, bigint, bigint, bigint, integer

23 23 1043 23 23 1184 20 23 23 23 23 23 23 701 701 23 20 1184 1184 20 1184 1184 1184 20 20 20 20 20 20 20 20 23

phil_test_model_prediction_function_v2_prob

integer, integer, character varying, integer, integer, timestamp with time zone, bigint, integer, integer, integer, integer, integer, integer, double precision, double precision, integer, bigint, timestamp with time zone, timestamp with time zone, bigint, timestamp with time zone, timestamp with time zone, timestamp with time zone, bigint, bigint, bigint, bigint, bigint, bigint, bigint, bigint, integer

23 23 1043 23 23 1184 20 23 23 23 23 23 23 701 701 23 20 1184 1184 20 1184 1184 1184 20 20 20 20 20 20 20 20 23

jakob · 2023-12-22T22:22:26Z

Thanks for the feedback. It could be that there's something funny going on with these functions -- they seem to have the maximum number of arguments (32), it's possible that they actually have more arguments but pg_proc only supports 32.

I haven't been able to create a function with more than 32 arguments, so I'm not sure how this could happen.

I'll try to make Postico more resilient to problems like this. While looking at this bug, I discovered that Redshift support procedures now, and there are some more compatibility issues I need to fix. I'll follow up when I know more.

dfpolo · 2023-12-23T17:18:15Z

@jakob great thanks! About how, both are func/proc created by CREATE MODEL sentence

jakob · 2023-12-24T07:11:44Z

Oh, that is interesting, thank you! I wasn't aware of the CREATE MODEL command.

While investigating this issue, I found out that Redshift has a catalog view named pg_proc_info that contains more data than pg_proc. It's possible that this view correctly handles functions with more arguments.

I've updated Postico to use pg_proc_info on Redshift. Please update to the latest version (Build 9676) and let me know if Autocomplete works again for you!

Some of my unit tests are still failing for Redshift, so there are more things I need to fix, but the new build should be an improvement over the current status in all scenarios.

dfpolo · 2023-12-24T17:10:17Z

@jakob it is still failing (Build 9676)

PG_DIAG_SEVERITY: ERROR
PG_DIAG_SQLSTATE: 0A000
PG_DIAG_MESSAGE_PRIMARY: null array elements not supported
PG_DIAG_SOURCE_FILE: ../src/pg/src/backend/utils/adt/arrayfuncs.c
PG_DIAG_SOURCE_LINE: 3491
PG_DIAG_SOURCE_FUNCTION: accumArrayResult

SELECT
    pg_namespace.nspname
    ,rel.relname
    ,rel.relkind
    ,rel.oid
    ,NULL as extname
    
     ,obj_description(rel.oid, 'pg_class') AS comment 
    
        
        	,COALESCE(
        		array(SELECT attname              FROM pg_attribute WHERE (attnum>0 OR attname='oid') AND attrelid=rel.oid AND NOT attisdropped
			 ORDER BY attnum)
        		,array(SELECT col_name 			 FROM pg_get_late_binding_view_cols() cols(view_schema name, view_name name, col_name name, col_type varchar, col_num int)
			 WHERE view_schema = pg_namespace.nspname and view_name = rel.relname)
        		,'{}' -- late binding views may have no columns
        	) AS column_names
        	,COALESCE(
        		array(SELECT format_type(atttypid, atttypmod)              FROM pg_attribute WHERE (attnum>0 OR attname='oid') AND attrelid=rel.oid AND NOT attisdropped
			 ORDER BY attnum)
        		,array(SELECT col_type 			 FROM pg_get_late_binding_view_cols() cols(view_schema name, view_name name, col_name name, col_type varchar, col_num int)
			 WHERE view_schema = pg_namespace.nspname and view_name = rel.relname)
        		,'{}'
        	) AS column_types
        	
        		,COALESCE(
        			array(SELECT COALESCE(col_description(attrelid, attnum), '')              FROM pg_attribute WHERE (attnum>0 OR attname='oid') AND attrelid=rel.oid AND NOT attisdropped
			 ORDER BY attnum)
        			,array(SELECT '' 			 FROM pg_get_late_binding_view_cols() cols(view_schema name, view_name name, col_name name, col_type varchar, col_num int)
			 WHERE view_schema = pg_namespace.nspname and view_name = rel.relname)
        			,'{}'
        	) AS column_comments
            
        
    
    
FROM pg_class rel
    
    JOIN pg_namespace ON (rel.relnamespace = pg_namespace.oid )
WHERE rel.relkind IN ('r','v','m','f','p')  GROUP BY rel.relname, rel.relkind, rel.oid

,pg_namespace.nspname
;SELECT
    schemaname AS nspname
    ,tablename AS relname
    ,'redshift_external_table' AS relkind
    , NULL AS oid
     ,NULL AS comment 
    
    	,ARRAY['unsupported'] AS column_names
    	,ARRAY['unsupported'] AS column_types
    	
    	    ,ARRAY['unsupported'] AS column_comments
    	
    
    
FROM svv_external_tables ;SELECT DISTINCT
	nspname
	,proname
	
		,prokind
	
	
	, proname || '(' || oidvectortypes(proargtypes) || ')' AS name_and_argtypes
	
		
			
				,(CASE WHEN
					proallargtypes IS NULL
				THEN
					ARRAY( SELECT format_type(proargtypes[i], NULL)
					FROM generate_series(0,pronargs-1) indexes(i) )
				ELSE
					ARRAY( SELECT format_type(proallargtypes[i], NULL)
					FROM generate_series(array_lower(proallargtypes, 1), array_upper(proallargtypes, 1)) indexes(i) )
				END) AS argtypes
			
		
	
	
	,proargnames AS argnames
	
	
		
			,proargmodes AS argmodes
		
	
	
		
			, NULL AS argdefaults
			, pronargs AS defaultless_input_arg_count
		
	
	
		,format_type(prorettype, NULL) AS returntype
		,proretset AS returnsset
	
	
	,NULL AS extname
	
		,obj_description(prooid, 'pg_proc') AS comment
	
	
	
	
	
	
	
FROM pg_proc_info
JOIN pg_namespace ON pronamespace = pg_namespace.oid;

jakob · 2023-12-26T12:07:43Z

Hmm, seems I was wrong about the cause of the problem.

I've made another attempt to fix autocomplete in Build 9677.

I've now wrapped the format_type function with COALESCE to prevent NULLs in the ARRAY. This should fix autocomplete, but the affected functions may appear with unknown argument types in Postico.

dfpolo · 2023-12-26T15:23:51Z

@jakob it is working now, thank you!!!

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Could not load completions #867

Could not load completions #867

dfpolo commented Dec 14, 2023

jakob commented Dec 20, 2023

dfpolo commented Dec 20, 2023 •

edited by jakob

Loading

jakob commented Dec 20, 2023 •

edited

Loading

dfpolo commented Dec 20, 2023

dfpolo commented Dec 20, 2023

jakob commented Dec 20, 2023

jakob commented Dec 20, 2023

jakob commented Dec 20, 2023

dfpolo commented Dec 20, 2023

jakob commented Dec 22, 2023

dfpolo commented Dec 23, 2023

jakob commented Dec 24, 2023

dfpolo commented Dec 24, 2023 •

edited by jakob

Loading

jakob commented Dec 26, 2023

dfpolo commented Dec 26, 2023

Could not load completions #867

Could not load completions #867

Comments

dfpolo commented Dec 14, 2023

What did you do?

What did you expect to happen?

What actually happened?

What software versions are you using?

jakob commented Dec 20, 2023

dfpolo commented Dec 20, 2023 • edited by jakob Loading

jakob commented Dec 20, 2023 • edited Loading

dfpolo commented Dec 20, 2023

dfpolo commented Dec 20, 2023

jakob commented Dec 20, 2023

jakob commented Dec 20, 2023

jakob commented Dec 20, 2023

dfpolo commented Dec 20, 2023

jakob commented Dec 22, 2023

dfpolo commented Dec 23, 2023

jakob commented Dec 24, 2023

dfpolo commented Dec 24, 2023 • edited by jakob Loading

jakob commented Dec 26, 2023

dfpolo commented Dec 26, 2023

dfpolo commented Dec 20, 2023 •

edited by jakob

Loading

jakob commented Dec 20, 2023 •

edited

Loading

dfpolo commented Dec 24, 2023 •

edited by jakob

Loading