Add support for async lambda in RegisterTableFunction

[mldisibio]

(Discussed in Discord. Formalizing as an Issue so someone can work on it. Is a nice-to-have.)

The following code using an async lambda within RegisterTableFunction does not compile under v1.1.3:

void TestTableFn()
{
	using var duckDBConnection = new DuckDBConnection("Data Source=:memory:");
	duckDBConnection.Open();
	
#pragma warning disable
	duckDBConnection.RegisterTableFunction<string, int>("my_people",
	async (parameters) => 
	{
		string startingName = parameters[0].GetValue<string>();
		int startingAge = parameters[1].GetValue<int>();
		var result = await GetRandomPersons(startingName, startingAge).ConfigureAwait(false);

                // needs to be:
		//var result = GetRandomPersons(startingName, startingAge).ConfigureAwait(false).GetAwaiter().GetResult();

		return new TableFunction([new ColumnInfo("PersonName", typeof(string)), new ColumnInfo("PersonAge", typeof(int))], result);
	},
	(item, writers, rowIndex) =>
	{
		var person = item as Person;
		writers[0].WriteValue(person?.Name, rowIndex);
		writers[1].WriteValue(person?.Age, rowIndex);
	});
#pragma warning restore

	using var cmd = duckDBConnection.CreateCommand();
	cmd.CommandText = "SELECT * FROM my_people('Alice', 25);";
	var reader = cmd.ExecuteReader();
}

public record Person(string Name, int Age);

public static async Task<List<Person>> GetRandomPersons(string startingName, int startingAge)
{
	Random random = new Random();
	var data = new List<Person>();
	for (int i = 0; i < 10; i++)
	{
		data.Add(new Person($"{startingName}{i + 1:D2}", startingAge + random.Next(1, 50)));
		await Task.Delay(100); // Simulate asynchronous operation
	}
	return data;
}

The error is

CS4010 Cannot convert async lambda expression 
to delegate type 'Func<IReadOnlyList<IDuckDBValueReader>, TableFunction>'. 
An async lambda expression may return void, Task or Task<T>, 
none of which are convertible to 'Func<IReadOnlyList<IDuckDBValueReader>, TableFunction>'.

Giorgi mentioned it is a limitation of the C api within which the callback is executed and maybe that is a hard limit. But perhaps someone can find an implementation that allows for non-blocking awaits.

[Giorgi]

Fixing compilation errors is easy: https://github.com/Giorgi/DuckDB.NET/tree/Table-Function-Async-Callback

It doesn't work at runtime though. DuckDB throws this error:

INTERNAL Error: Failed to bind "github_search": Table function must return at least one column
This error signals an assertion failure within DuckDB. This usually occurs due to unexpected conditions or errors in the program's logic.

The reason it's happening is that Bind callback is now async void so it returns control back to the native code when it hits first await:

DuckDB.NET/DuckDB.NET.Data/DuckDBConnection.TableFunction.cs

Line 122 in 3902db8

var tableFunctionData = await functionInfo.Bind(parameters);

One workaround that I can think of (but I don't like it) is making the Bind callback void and using the same GetAwaiter().GetResult() mechanism to invoke the resultCallback. This feels like hiding the problem under the rug and creating a false impression that the resultCallback is truly async.

[mldisibio]

I haven't looked at the source code yet. I keep forgetting this is interop with C, not C++.

In the meantime I wrote a demo of how to use the legacy Asynchronous Programming Model (APM) (pre async) to invoke an otherwise Task Asynchronous Pattern (async/await) signature.

Gist: asyncTvhDemo

So this is at the consuming side rather than your api side. I'm sure you remember how we were all told not to block on asynchronous calls when async/await first came out. David Fowler
(But if you did, they said .GetAwaiter().GetResult() was the safest pattern.)

That said, and I am no internals expert, but I think the IAsyncResult interface is designed so that after BeginInvoke is called, the threads are managed in such a way that the the main thread is not blocked, whereas I think .GetAwaiter().GetResult() is pure blocking. Maybe it's no different, dunno. In any case, the demo is one possible pattern for consumers to invoke async table-value-functions until the api can be sorted out.

[VitaliyMF]

Adding my 2 cents, I've also trying to utilize RegisterTableFunction and an interface of my data sources is also async. By the nature of DuckDB library I guess true async handlers are not possible.

In fact my case is even more complicated because data reader is pushed to a callback, smth like

Task GetRecordsAsync(Func<ITableReader,Task> readHandler, CancellationToken cancel);

To use this API with synchronous RegisterTableFunction - which is divided on 2 synchronous handlers - I'll have to run an additional (concurrent) task to simulate async readHandler that communicates with these 2 TVF handlers using several TaskCompletionSource instances. It seems this Frankenstein even works ^_^

[Giorgi]

By the nature of DuckDB library I guess true async handlers are not possible.

I have come to the same conclusion.