Обсуждение: Does npgsql have a bunch of bugs with DB enums?

This is probably not the right forum for this question, but maybe
someone can help me out or redirect me.

I'm running into a lot of problems with npgsql and enum types.  There
seems to be very little support or testing in this area.  The issue
right now is I have a lot of SQL functions that have OUT parameters of
an enum type.  Such as:

CREATE TYPE UnitTypeEnum AS ENUM ('Unit', 'Volume', 'Weight');


CREATE OR REPLACE FUNCTION readformsforingredient(IN _ingredient text,
OUT UnitType UnitTypeEnum)
  RETURNS SETOF record AS
...

When I call that function using the npgsql driver, I get all sorts of
errors.  The data table simple has no ProviderType (it's blank), even
though the DataType is System.String.  If I try to read the data
reader, I get exceptions.  The solution I found is to cast the enum to
a text, such as:

CREATE OR REPLACE FUNCTION readformsforingredient(IN _ingredient text,
OUT UnitType text)
...
select UnitType::text from foo;

However, this becomes a pain if I'm not using OUT parameters and just
returning a set of a row type.  Plus, having to cast ENUMs is kind of
a hack.

Are the people working on npgsql aware of this problem, and can we
expect to get real enum support in future versions?  It seems they
should just magically cast to either a string or an int and I
shouldn't have to worry about that.  Thanks!!

Mike

Ok I did more investigation on this and traced the issue down to a
singe npgsql bug.  Enums actually work fine, as long as you're using
an IDataReader to get at the data.  Once you attempt to load the
reader into a DataSet, it blows up.  I'll log this bug..

Mike

On Mon, Jun 7, 2010 at 4:43 AM, Mike Christensen <mike@kitchenpc.com> wrote:
> This is probably not the right forum for this question, but maybe
> someone can help me out or redirect me.
>
> I'm running into a lot of problems with npgsql and enum types.  There
> seems to be very little support or testing in this area.  The issue
> right now is I have a lot of SQL functions that have OUT parameters of
> an enum type.  Such as:
>
> CREATE TYPE UnitTypeEnum AS ENUM ('Unit', 'Volume', 'Weight');
>
>
> CREATE OR REPLACE FUNCTION readformsforingredient(IN _ingredient text,
> OUT UnitType UnitTypeEnum)
>  RETURNS SETOF record AS
> ...
>
> When I call that function using the npgsql driver, I get all sorts of
> errors.  The data table simple has no ProviderType (it's blank), even
> though the DataType is System.String.  If I try to read the data
> reader, I get exceptions.  The solution I found is to cast the enum to
> a text, such as:
>
> CREATE OR REPLACE FUNCTION readformsforingredient(IN _ingredient text,
> OUT UnitType text)
> ...
> select UnitType::text from foo;
>
> However, this becomes a pain if I'm not using OUT parameters and just
> returning a set of a row type.  Plus, having to cast ENUMs is kind of
> a hack.
>
> Are the people working on npgsql aware of this problem, and can we
> expect to get real enum support in future versions?  It seems they
> should just magically cast to either a string or an int and I
> shouldn't have to worry about that.  Thanks!!
>
> Mike
>

Thank you very much, Mike!

We'll have a look at that.


On Tue, Jun 8, 2010 at 00:03, Mike Christensen <mike@kitchenpc.com> wrote:
> Ok I did more investigation on this and traced the issue down to a
> singe npgsql bug.  Enums actually work fine, as long as you're using
> an IDataReader to get at the data.  Once you attempt to load the
> reader into a DataSet, it blows up.  I'll log this bug..
>
> Mike
>
> On Mon, Jun 7, 2010 at 4:43 AM, Mike Christensen <mike@kitchenpc.com> wrote:
>> This is probably not the right forum for this question, but maybe
>> someone can help me out or redirect me.
>>
>> I'm running into a lot of problems with npgsql and enum types.  There
>> seems to be very little support or testing in this area.  The issue
>> right now is I have a lot of SQL functions that have OUT parameters of
>> an enum type.  Such as:
>>
>> CREATE TYPE UnitTypeEnum AS ENUM ('Unit', 'Volume', 'Weight');
>>
>>
>> CREATE OR REPLACE FUNCTION readformsforingredient(IN _ingredient text,
>> OUT UnitType UnitTypeEnum)
>>  RETURNS SETOF record AS
>> ...
>>
>> When I call that function using the npgsql driver, I get all sorts of
>> errors.  The data table simple has no ProviderType (it's blank), even
>> though the DataType is System.String.  If I try to read the data
>> reader, I get exceptions.  The solution I found is to cast the enum to
>> a text, such as:
>>
>> CREATE OR REPLACE FUNCTION readformsforingredient(IN _ingredient text,
>> OUT UnitType text)
>> ...
>> select UnitType::text from foo;
>>
>> However, this becomes a pain if I'm not using OUT parameters and just
>> returning a set of a row type.  Plus, having to cast ENUMs is kind of
>> a hack.
>>
>> Are the people working on npgsql aware of this problem, and can we
>> expect to get real enum support in future versions?  It seems they
>> should just magically cast to either a string or an int and I
>> shouldn't have to worry about that.  Thanks!!
>>
>> Mike
>>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



--
Regards,

Francisco Figueiredo Jr.
Npgsql Lead Developer
http://www.npgsql.org
http://fxjr.blogspot.com
http://twitter.com/franciscojunior