Invoke-DBOQuery - unhandled datatype aclitem[]

[petervandivier]

Summary

Using Invoke-DBOQuery, queries returning non-null data of type aclitem[] (for example pg_database.datacl) throw an error. This data should be gracefully returned

Repro

Given Set-DBODefaultSetting -Name rdbms.Type -Value PostgreSQL,

PS > Invoke-DBOQuery 'select cast(null as aclitem[]);'                                                          

aclitem
-------

PS > Invoke-DBOQuery "select cast(null as aclitem[]), cast('{=c/postgres}' as aclitem[]);" -Verbose              
VERBOSE: [17:50:25][Initialize-ExternalLibrary] Npgsql was found among the loaded libraries, assuming that the library is fully loaded
VERBOSE: [17:50:25][Invoke-DBOQuery] Establishing connection with PostgreSQL localhost
VERBOSE: Performing the operation "Executing query select cast(null as aclitem[]), cast('{=c/postgres}' as aclitem[]);" on target "localhost".
VERBOSE: [17:50:25][Invoke-DBOQuery] Executing sub-query select cast(null as aclitem[]), cast('{=c/postgres}' as aclitem[]);
[17:50:25][Invoke-DBOQuery] 42883: no binary output function available for type aclitem
Exception calling "GetResult" with "0" argument(s): "42883: no binary output function available for type aclitem"
At /Users/pvandivier/.local/share/powershell/Modules/PSFramework/1.1.59/PSFramework.psm1:3304 char:23
+         if (-not $Cmdlet) { throw $records[0] }
+                             ~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], Exception
    + FullyQualifiedErrorId : dbops_Invoke-DBOQuery
PS > 

Full Error

PS > Invoke-DBOQuery "select cast(null as aclitem[]), cast('{=c/postgres}' as aclitem[]);"
[17:53:06][Invoke-DBOQuery] 42883: no binary output function available for type aclitem
Exception calling "GetResult" with "0" argument(s): "42883: no binary output function available for type aclitem"
At /Users/pvandivier/.local/share/powershell/Modules/PSFramework/1.1.59/PSFramework.psm1:3304 char:23
+         if (-not $Cmdlet) { throw $records[0] }
+                             ~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], Exception
    + FullyQualifiedErrorId : dbops_Invoke-DBOQuery
PS > $error[0] | select *         

PSMessageDetails      : 
Exception             : System.Exception: Exception calling "GetResult" with "0" argument(s): "42883: no binary output function available for type aclitem" ---> 
                        System.Management.Automation.MethodInvocationException: Exception calling "GetResult" with "0" argument(s): "42883: no binary output function available for type 
                        aclitem" ---> Npgsql.PostgresException: 42883: no binary output function available for type aclitem
                           at Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.<<ReadMessage>g__ReadMessageLong|0>d.MoveNext() in C:\projects\npgsql\src\Npgsql\NpgsqlConnector.cs:line 1034
                        --- End of stack trace from previous location where exception was thrown ---
                           at Npgsql.NpgsqlConnector.<>c__DisplayClass161_0.<<ReadMessage>g__ReadMessageLong|0>d.MoveNext() in C:\projects\npgsql\src\Npgsql\NpgsqlConnector.cs:line 1034
                        --- End of stack trace from previous location where exception was thrown ---
                           at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming) in C:\projects\npgsql\src\Npgsql\NpgsqlDataReader.cs:line 526
                           at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken) in 
                        C:\projects\npgsql\src\Npgsql\NpgsqlCommand.cs:line 1231
                           at CallSite.Target(Closure , CallSite , Object )
                           --- End of inner exception stack trace ---
                           at System.Management.Automation.ExceptionHandlingOps.CheckActionPreference(FunctionContext funcContext, Exception exception)
                           at lambda_method(Closure , Object[] , StrongBox`1[] , InterpretedFrame )
                           --- End of inner exception stack trace ---
TargetObject          : 
CategoryInfo          : NotSpecified: (:) [], Exception
FullyQualifiedErrorId : dbops_Invoke-DBOQuery
ErrorDetails          : 
InvocationInfo        : System.Management.Automation.InvocationInfo
ScriptStackTrace      : at Stop-PSFFunction, /Users/pvandivier/.local/share/powershell/Modules/PSFramework/1.1.59/PSFramework.psm1: line 3304
                        at Invoke-DBOQuery<Process>, /Users/pvandivier/repos/git/dbops/functions/Invoke-DBOQuery.ps1: line 386
                        at <ScriptBlock>, <No file>: line 1
PipelineIterationInfo : {}

PS > 

[petervandivier]

Possible background context here

Don't use [aclitem], it's slated to disappear soon.

... dated 2000-10-31 ... 🤔

Might explain lack of support in the lib though

[nvarscar]

This type is not the only one that is not supported by npgsql, check out this FAQ section. The only thing that works is this suggestion:

using (var cmd = new NpgsqlCommand(...)) {
  cmd.AllResultTypesAreUnknown = true;
  var reader = cmd.ExecuteReader();
  // Read everything as strings
}

It requests all the types to be returned as text, and, although it's not optimal, this is the only solution for now. I'm testing a branch that will add a -ReturnAsText switch to handle such situations.

[nvarscar]

Resolved in #101