az postgres flexible-server execute has different results than psql

[fgarcia-cnb]

Describe the bug

running psql 14.17
az cli version 2.69.0

running the query below on postgres database, i get different results. 30 records with az postgres flexible-server execute, 35 with psql

$Query = @"
        --- server level (run on postgres database)
 		SELECT DISTINCT
            pg_catalog.pg_get_userbyid(d.datdba) AS principal_name,
            NULL AS role_name,
            d.datname AS database_name,
            NULL AS collection_name,
            NULL AS schema_name,
            NULL AS table_name,
            NULL AS column_name,
            NULL AS view_name,
            NULL AS procedure_name,
            NULL AS function_name,
            NULL AS other_object,
            '0' AS is_inheritable,
            '0' AS is_grantable,
            'OWNER' AS privilege
        FROM 
            pg_catalog.pg_database d
        
        UNION ALL
        
        SELECT DISTINCT
            CASE
                WHEN pg_get_userbyid(acl.grantee) = 'unknown (OID=0)' THEN 'PUBLIC'
                ELSE pg_get_userbyid(acl.grantee)
                END AS principal_name,
            NULL AS role_name,
            d.datname  AS database_name,
            NULL AS collection_name,
            NULL AS schema_name,
            NULL AS table_name,
            NULL AS column_name,
            NULL AS view_name,
            NULL AS procedure_name,
            NULL AS function_name,
            NULL AS other_object,
            '0' AS is_inheritable,
            CASE
            	WHEN acl.grantee = d.datdba THEN '1'
				WHEN CAST(acl.is_grantable AS integer) = 1 THEN '1'
            	ELSE '0'
            	END AS is_grantable,
            acl.privilege_type AS privilege
        FROM
            pg_database d,
            LATERAL aclexplode(d.datacl) acl
		
		UNION ALL
		
		SELECT DISTINCT
		    pg_get_userbyid(member) AS principal_name,
		    pg_get_userbyid(roleid) AS role_name,
		    NULL AS database_name,
		    NULL AS collection_name,
		    NULL AS schema_name,
		    NULL AS table_name,
		    NULL AS column_name,
		    NULL AS view_name,
		    NULL AS procedure_name,
		    NULL AS function_name,
		    NULL AS other_object,
		    COALESCE(
				(
				   	SELECT '0'
				   	FROM pg_catalog.pg_roles r
				   	WHERE m.member = r.oid and r.rolinherit is false
				   	LIMIT 1
				), '1'
			) AS is_inheritable,
		    CASE
		        WHEN admin_option = true then '1'
		        ELSE '0'
		    END AS is_grantable,
		    'MEMBER' AS privilege
		FROM
		    pg_auth_members m

		UNION ALL
		
 		SELECT DISTINCT
            r.rolname AS principal_name,
            NULL AS role_name,
            NULL AS database_name,
            NULL AS collection_name,
            NULL AS schema_name,
            NULL AS table_name,
            NULL AS column_name,
            NULL AS view_name,
            NULL AS procedure_name,
            NULL AS function_name,
            NULL AS other_object,
            '0' AS is_inheritable,
            '1' AS is_grantable,
            'CREATEROLE' AS privilege
        FROM 
            pg_catalog.pg_roles r
		WHERE 
            r.rolcreaterole = true
		
		UNION ALL
		
 		SELECT DISTINCT
            r.rolname AS principal_name,
            NULL AS role_name,
            NULL AS database_name,
            NULL AS collection_name,
            NULL AS schema_name,
            NULL AS table_name,
            NULL AS column_name,
            NULL AS view_name,
            NULL AS procedure_name,
            NULL AS function_name,
            NULL AS other_object,
            '0' AS is_inheritable,
            CASE
		        WHEN r.rolcreaterole = true then '1'
				WHEN r.rolsuper = true then '1'
		        ELSE '0'
		    END AS is_grantable,
            'CREATEDB' AS privilege
        FROM 
            pg_catalog.pg_roles r
		WHERE 
            r.rolcreatedb = true
        
		UNION ALL
		
 		SELECT DISTINCT
            r.rolname AS principal_name,
            NULL AS role_name,
            NULL AS database_name,
            NULL AS collection_name,
            NULL AS schema_name,
            NULL AS table_name,
            NULL AS column_name,
            NULL AS view_name,
            NULL AS procedure_name,
            NULL AS function_name,
            NULL AS other_object,
            '0' AS is_inheritable,
            '1' AS is_grantable,
            'SUPERUSER' AS privilege
        FROM 
            pg_catalog.pg_roles r
		WHERE 
            r.rolsuper = true
"@

Related command

n/a

Errors

n/a

Issue script & Debug output

n/a

Expected behavior

should have same record count

Environment Summary

running psql 14.17
az cli version 2.69.0
az postgres flexible-server extension

Additional context

No response

[yonzhan]

Thank you for opening this issue, we will look into it.

[fgarcia-cnb]

ran another query where psql returns 4720 records, and az postgres flexible-server extension still only returns 30. seems like its maxing out at 30

[fgarcia-cnb]

i was correct. running az cli in verbose i see the following:

Retrieving first 30 rows of query output, if applicable.

is there a way to get all results?

[microsoft-github-policy-service]

Thanks for the feedback! We are routing this to the appropriate team for follow-up. cc @gbowerman, @jasomaning, @nachoalonsoportillo.

[peterneave]

It's currently a hard coded value