haskelldb-users

[Haskelldb-users] Problem with hdb and postgresql

[Gregory W. <gw...@co...>]

Hi,

I've been using HaskellDB to interface to a postgresql database and
have come across a problem with "SERIAL" fields.
In postgresql, a SERIAL column is normally never written to but
is automatically assigned the next integer.  When read, the
column is never empty, so it is assigned the "non-null" attribute. (And
therefore DBDirect classifies it as not nullable.)

However, in normal use the column is never written to. So "non-null"
in this case does not have its usual meaning of "must be present"
in an insert. In haskelldb however, failing to write the column causes
a type error, as positional parameters seem to be misaligned.

It is possible to work around this, but very inefficient. (The maximum
value of the SERIAL column can be read, and the next entry can
be written with max() + 1 into that field. But this involves scanning
the whole table for every insertion. It's not possible to maintain the
max
value in the haskell program because it may not be the only one
to access the database.)

Is there some kind of simple way to fix this? I can imagine a record
constructor that expands to nothing, allowing one to leave "non-null"
fields empty in insertions. This could cause run time errors, but
that's no worse than the situation now: if you write a non-unique
value to a SERIAL, you get a run time error. Or should I just hack
up the output of DBDirect by hand, to make SERIAL fields nullable?
This has the drawback of returning them as (Maybe ...) in queries,
when they are really guaranteed to be non-null.

I admit that the use of many "SERIAL" fields is an ugly way to structure
the database (I didn't write the schema). But it seems that schema that
depend on autoincrementing values use them everywhere, and they
are very awkward to handle under the current haskelldb arrangement.

Any ideas as to how I might proceed?

Best Wishes,
Greg

Gregory Wright
Antiope Associates LLC
gw...@an...

Re: [Haskelldb-users] Problem with hdb and postgresql

[Gregory W. <gw...@co...>]

(Ungraciously replying to his own mail):

Having looked at the list archive I see that in March, Jeremy Shaw
had a similar issue with mySQL and solved it using something like
a fake nullable field. Does mySQL return non-null as an attribute of
its auto-increment fields? If so maybe there is a generic way to
fix this problem.

Best Wishes,
Greg


On May 7, 2004, at 11:31 AM, Gregory Wright wrote:

>
> Hi,
>
> I've been using HaskellDB to interface to a postgresql database and
> have come across a problem with "SERIAL" fields.
> In postgresql, a SERIAL column is normally never written to but
> is automatically assigned the next integer.  When read, the
> column is never empty, so it is assigned the "non-null" attribute. (And
> therefore DBDirect classifies it as not nullable.)
>
> However, in normal use the column is never written to. So "non-null"
> in this case does not have its usual meaning of "must be present"
> in an insert. In haskelldb however, failing to write the column causes
> a type error, as positional parameters seem to be misaligned.
>
> It is possible to work around this, but very inefficient. (The maximum
> value of the SERIAL column can be read, and the next entry can
> be written with max() + 1 into that field. But this involves scanning
> the whole table for every insertion. It's not possible to maintain the
> max
> value in the haskell program because it may not be the only one
> to access the database.)
>
> Is there some kind of simple way to fix this? I can imagine a record
> constructor that expands to nothing, allowing one to leave "non-null"
> fields empty in insertions. This could cause run time errors, but
> that's no worse than the situation now: if you write a non-unique
> value to a SERIAL, you get a run time error. Or should I just hack
> up the output of DBDirect by hand, to make SERIAL fields nullable?
> This has the drawback of returning them as (Maybe ...) in queries,
> when they are really guaranteed to be non-null.
>
> I admit that the use of many "SERIAL" fields is an ugly way to 
> structure
> the database (I didn't write the schema). But it seems that schema that
> depend on autoincrementing values use them everywhere, and they
> are very awkward to handle under the current haskelldb arrangement.
>
> Any ideas as to how I might proceed?
>
> Best Wishes,
> Greg
>
> Gregory Wright
> Antiope Associates LLC
> gw...@an...
>
>
>
>
>
>
>
>
>
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by Sleepycat Software
> Learn developer strategies Cisco, Motorola, Ericsson & Lucent use to 
> deliver higher performing products faster, at low TCO.
> http://www.sleepycat.com/telcomwpreg.php?From=osdnemail3
> _______________________________________________
> Haskelldb-users mailing list
> Has...@li...
> https://lists.sourceforge.net/lists/listinfo/haskelldb-users
>

Re: [Haskelldb-users] Problem with hdb and postgresql

[Bjorn B. <d00...@dt...>]

Gregory Wright wrote:
> I've been using HaskellDB to interface to a postgresql database and 
> have come across a problem with "SERIAL" fields. In postgresql, a
> SERIAL column is normally never written to but is automatically
> assigned the next integer.  When read, the column is never empty, so
> it is assigned the "non-null" attribute. (And therefore DBDirect
> classifies it as not nullable.)
> 
> However, in normal use the column is never written to. So "non-null" 
> in this case does not have its usual meaning of "must be present" in
> an insert. In haskelldb however, failing to write the column causes a
> type error, as positional parameters seem to be misaligned.
> 
> It is possible to work around this, but very inefficient. (The
> maximum value of the SERIAL column can be read, and the next entry
> can be written with max() + 1 into that field. But this involves
> scanning the whole table for every insertion. It's not possible to
> maintain the max value in the haskell program because it may not be
> the only one to access the database.)
> 
> Is there some kind of simple way to fix this? I can imagine a record 
> constructor that expands to nothing, allowing one to leave "non-null"
>  fields empty in insertions. This could cause run time errors, but 
> that's no worse than the situation now: if you write a non-unique 
> value to a SERIAL, you get a run time error. Or should I just hack up
> the output of DBDirect by hand, to make SERIAL fields nullable? This
> has the drawback of returning them as (Maybe ...) in queries, when
> they are really guaranteed to be non-null.
> 
> I admit that the use of many "SERIAL" fields is an ugly way to
> structure the database (I didn't write the schema). But it seems that
> schema that depend on autoincrementing values use them everywhere,
> and they are very awkward to handle under the current haskelldb
> arrangement.
> 
> Any ideas as to how I might proceed?

We added the Query._default construct to deal with default values and
auto-increment colums. I belive that it works for SERIAL columns in 
PostgreSQL.
It can be used as an expression, for example:

insert db my_table (my_serial_col << _default # other_col << constant 
"foobar")

Let us know if it works for you.

/Bjorn

Re: [Haskelldb-users] Problem with hdb and postgresql

[Gregory W. <gw...@co...>]

Hi Bjorn,

This is in 0.8 or a snapshot?

Thanks,
Greg


On May 7, 2004, at 8:45 PM, Bjorn Bringert wrote:

> Gregory Wright wrote:
>> I've been using HaskellDB to interface to a postgresql database and 
>> have come across a problem with "SERIAL" fields. In postgresql, a
>> SERIAL column is normally never written to but is automatically
>> assigned the next integer.  When read, the column is never empty, so
>> it is assigned the "non-null" attribute. (And therefore DBDirect
>> classifies it as not nullable.)
>> However, in normal use the column is never written to. So "non-null" 
>> in this case does not have its usual meaning of "must be present" in
>> an insert. In haskelldb however, failing to write the column causes a
>> type error, as positional parameters seem to be misaligned.
>> It is possible to work around this, but very inefficient. (The
>> maximum value of the SERIAL column can be read, and the next entry
>> can be written with max() + 1 into that field. But this involves
>> scanning the whole table for every insertion. It's not possible to
>> maintain the max value in the haskell program because it may not be
>> the only one to access the database.)
>> Is there some kind of simple way to fix this? I can imagine a record 
>> constructor that expands to nothing, allowing one to leave "non-null"
>>  fields empty in insertions. This could cause run time errors, but 
>> that's no worse than the situation now: if you write a non-unique 
>> value to a SERIAL, you get a run time error. Or should I just hack up
>> the output of DBDirect by hand, to make SERIAL fields nullable? This
>> has the drawback of returning them as (Maybe ...) in queries, when
>> they are really guaranteed to be non-null.
>> I admit that the use of many "SERIAL" fields is an ugly way to
>> structure the database (I didn't write the schema). But it seems that
>> schema that depend on autoincrementing values use them everywhere,
>> and they are very awkward to handle under the current haskelldb
>> arrangement.
>> Any ideas as to how I might proceed?
>
> We added the Query._default construct to deal with default values and
> auto-increment colums. I belive that it works for SERIAL columns in 
> PostgreSQL.
> It can be used as an expression, for example:
>
> insert db my_table (my_serial_col << _default # other_col << constant 
> "foobar")
>
> Let us know if it works for you.
>
> /Bjorn
>
>
>
> -------------------------------------------------------
> This SF.Net email is sponsored by Sleepycat Software
> Learn developer strategies Cisco, Motorola, Ericsson & Lucent use to 
> deliver higher performing products faster, at low TCO.
> http://www.sleepycat.com/telcomwpreg.php?From=osdnemail3
> _______________________________________________
> Haskelldb-users mailing list
> Has...@li...
> https://lists.sourceforge.net/lists/listinfo/haskelldb-users
>

Re: [Haskelldb-users] Problem with hdb and postgresql

[Bjorn B. <d00...@dt...>]

Gregory Wright wrote:
> This is in 0.8 or a snapshot?

It's in 0.8 (I think it was first introduced in 0.7).

/Bjorn

> On May 7, 2004, at 8:45 PM, Bjorn Bringert wrote:
>> We added the Query._default construct to deal with default values and
>> auto-increment colums. I belive that it works for SERIAL columns in 
>> PostgreSQL.
>> It can be used as an expression, for example:
>>
>> insert db my_table (my_serial_col << _default # other_col << constant 
>> "foobar")
>>
>> Let us know if it works for you.