We're attempting to migrate our database from SQL Server 2012 to PostgreSQL 9.6 using LLBLGen Pro 5.3.4. We've been able to remap most things just fine, but there's at least one significant issue we've run into so far. We have a number of stored procedures in SQL Server that return table results. These were mapped to Stored Procedure Calls in LLBLGen, but they're being mapped to Table Valued Function Calls for our PostgreSQL database. I'm assuming this is because PostgreSQL doesn't really draw a distinction between stored procedures and functions (everything is a function), but it didn't do that for all of the stored procedures that were ported over. As far as I can tell, the stored procedures that return a single column (or value?) were mapped to Stored Procedure Calls and those that return more than one column were mapped to Table Valued Function Calls. Am I correct in assuming that's how it works for PostgreSQL/Npgsql? Is there anyway to force those functions to map to Stored Procedure Calls so that the SQL Server and PostgreSQL databases match?

Please try v.5.3.5 (hotfix build).

5.3.5 did not change how the PostgreSQL functions are recognized by the Sync Relational Model Data functionality. They are still listed as Table Valued Functions.

Which version of Npgsql are you using?

We're using Npgsql 3.2.7.

Ah I see what you mean. The fix in 5.3.5 made stored procs which use SETOF or return table to show up as TVFs, something which wasn't done before. What you want is the opposite.

The problem is that indeed in PostgreSQL there's no clear distinction between TVFs and a regular stored proc which returns a resultset, except for the situation where the stored proc returns a cursor.

Procs which return a cursor are seen as stored procs which return a resultset, all other procs which return a resultset (i.e. table or setof) are seen as TVFs (we have to, there's no other way to see them as TVF otherwise).

Your procs use SETOF or return table?

Yes, most of the migrated stored procedures are using return table. We did so in an attempt to make them more consistent with how they work in SQL Server. We also weren't sure how LLBLGen deals with PostgreSQL cursors since Npgsql removed automatic cursor dereferencing a few years ago (see https://github.com/npgsql/npgsql/issues/438). If we change the functions to return a cursor, will LLBLGen automatically handle dereferencing/row fetching?

Looking into this, I indeed see the procs which do work are setof/return table ones, not refcursor ones. We have a few tests which are indeed marked as ignore as the refcursor support is broken in npgsql (as it gives the 'unnamed portal' error). We rely on the DbDataAdapter support (so we fill a Datatable or dataset with the proc result and the ADO.NET provider's DbDataAdapter implementation should take care of the cursor usage.

I see your point though. While having the procs as a TVF isn't going to hurt, it doesn't help if your code is using datatable/set based stored proc resultsets: the procs then aren't available to you.

Currently, using SETOF in a proc will make it show up as a stored procedure and TVF. Using Return table, will make it show up as TVF.

It's easy for us to add all functions as stored procedures and add only the record/custom type returning functions as TVFs, you can then select them for the type you want: TVF or proc.

We'll make this change in the 5.3.5 hotfix (driver change for postgresql), which we'll release later today

5.3.5 hotfix build with this fix is now available.

I just tried things out with the new 5.3.5 hotfix build. The return table functions do now get listed as stored procedures. However, when I configure the mapping, it appears that all of the columns listed in the RETURN TABLE statement are being counted as parameters, so I get an error specifying that there's a mismatch between the number of parameters defined by the SP call and the target. For example, in this definition:

CREATE OR REPLACE FUNCTION public.samplesp(
    par_startindex integer DEFAULT '-1'::integer,
    par_endindex integer DEFAULT '-1'::integer,
    par_sampleid numeric DEFAULT NULL::numeric,
    par_filter citext DEFAULT NULL::citext)
    RETURNS TABLE(name citext, id citext, idx bigint)

it's counting par_startindex, par_endindex, par_sampleid, par_filter as parameters as well as the three columns being returned.

You did check the proc for resultset retrieval in the wizard? You have to do that. If you did, then we have a bug.

I did, although it appears most of these threw an error like this:

Error: 'Stored procedure 'documentcategoryusersorgroupstoassignpaged' caused an exception during resultset retrieval. Its resultsets (if any) probably aren't determined in full: 42883: function public.documentcategoryusersorgroupstoassignpaged(par_startindex => integer, par_endindex => integer, par_documentcategoryid => numeric, par_filter => text) does not exist'

Do I need to configure these so that they return some valid data for the default parameter values? If I did that and it succeeded, maybe the parameter issue wouldn't have happened? I guess what confused me is that even though some errors were thrown, the stored procedures showed up as items that could be mapped whereas before they didn't.

The parameter issue is indeed a bug. We changed the filter for the procs, but I think we forgot to add a filter on the parameter retrieval query for these. I missed this in testing, as I looked at the resultset whether that was coming through OK. We'll correct this.

Regarding the exception you run into, I can't reproduce that. Do you have a simple proc for me in DDL SQL which reproduces this behavior?

We corrected the parameter issue and will release a new hotfix build later today. If you could give us a proc which creates the exception you ran into we can try to fix that one in that same hotfix build as well

I think I got to the bottom of the exception that's being thrown. It looks like citext parameters are the cause. Here's an example configuration:

create table tempa (a int, b float, c citext); insert into tempa values (1, 2.2, 'hello');

CREATE OR REPLACE FUNCTION public.testfunc1( columnc text DEFAULT NULL::text) RETURNS TABLE(cola integer, colb double precision, colc citext) LANGUAGE 'plpgsql' AS $BODY$ BEGIN return query select a,b,c from tempa where c = columnc; END; $BODY$;

CREATE OR REPLACE FUNCTION public.testfunc2( columnc citext DEFAULT NULL::citext) RETURNS TABLE(cola integer, colb double precision, colc citext) LANGUAGE 'plpgsql' AS $BODY$ BEGIN return query select a,b,c from tempa where c = columnc; END; $BODY$;

In that example, testfunc1 works fine but testfunc2 throws the exception. That's probably something that would be easy to workaround since we could just make the parameters text and it would still be a case insensitive comparison because the column is citext, but a fix would be nice.

Hmm, citext should be picked up, but we'll have a look! I think it's likely due to the length being reported as -1 and it then fails to match a rule in the mapping table.

Reproduced. The citext parameter is typed 'text', not citext. Very odd. looking into it.

(edit) the problem is that the driver doesn't have a reference to the ADO.NET provider for its type enum. This means that the parameter's type is set by setting the DbParameter object's Value property to a string value, which makes the parameter type become 'text'. this works with all other databases without problems but with postgresql it's more strict.

So we have to use reflection to obtain the Npgsql type enum and its value and also set the property to the DbParameter object... We use IL generated code in the runtime to do that but that's quite complicated so we'll see what we can do here with reflection (it's not performance critical)

Fixed in next hotfix build, which also contains the fix I mentioned earlier. We have one linq issue to resolve and will publish the new build after that, likely later today.

Hotfix is now available

Both fixes appear to have resolved our issues so far. Thanks!