SubmitUpdates() ordering?

[ingted]

Description

When using SetColumn/SetColumnOption with null/None, not sure why recently it bumps to "Parameter {ParameterName} must be set" Exception in Npgsql.

https://github.com/npgsql/npgsql/blob/dev/src/Npgsql/NpgsqlParameter.cs
Near line 628

Repro steps

Please provide the steps required to reproduce the problem

match value with
| null ->
se.SetColumnOption(idx, None)
| _ ->
se.SetColumn(idx, value)

After I use (from reflected method which I need to keep IDbConnection opened for batch transaction)
something like:

isdc.SubmitChangedEntity(se)
isdc.SubmitPendingChangesNoClose(dbConn)

Exception raised...

Expected behavior

I remembered I don't need to specified DBNull.Value before...
(In the fact, even I specified DBNull.Value, it still not works)
And null/None value should work.

Actual behavior

But recently when it doesn't work...

Known workarounds

#320

Maybe what I bumped into was caused by my missed reflection...
If so, please just ignore me...

[Thorium]

You would need a ReflectionTypeProvider...

[ingted]

Hi Thorium,

I know it is really ugly to use reflection too much. omg
Please just forget the reflection things...

Just want to know if SetColumnOption with None for the latest Npgsql is ok...

How do you think about it?@@"
If yes, let's close this...^o^

[Thorium]

I hope that SubmitUpdates() writes all in one batch. If it doesn't can we fix that?

I don't use Postgres myself. Ping @janno-p he fixed some None behaviours.

[ingted]

Hi Thorium,

Because the ProcessUpdates function doesn't sequentially process each SqlEntity:

entities.Keys
|> Seq.iter(fun e ->

So if the order of each invocation of SubmitChangedEntity have meanings,
then some database level errors would happen.

e.g.
insert into table values (1,2) -> delete from table where id = 1;
is different from
delete from table where id = 1; insert into table values (1,2);

So far my way to come over this is extracting ProcessUpdates outside ISqlProvider.
And change the invocation to

    member this.ProcessUpdates(con, entities) =
        ProcessUpdatesOrig con entities true this
    member this.ProcessUpdatesNoClose(con, entities) =
        ProcessUpdatesOrig con entities false this

At last using reflection to do the submit.

I have a full picture to do what you mentioned. (of course, not use any reflection)
And this would provide the advantage to do order-meaningful batch processing within rollback-able transaction.

How do you think about it?

Our production applied the modification to the postgres and mysql part.

[ingted]

match ifClose with
| false -> ()
| true ->
match con.State with
| ConnectionState.Open -> con.Close()
| _ -> ()
con.Open()

[ingted]

Then SubmitUpdates() could provide (in one batch) what I need to do outside with lots of code:

let dwConn = mapPGConn ISPfromSQP.dW statConnStr.dW
let ptx = new CommittableTransaction(new TimeSpan(5, 0, 0))
dwConn.EnlistTransaction(ptx)              
isdcDw.ClearPendingChanges()
try
    typDimTablesArray.ChangedDimApplication <- true
    dbSubmit isdcDw dwConn eosf 6

    ptx.Commit()
with
| ex ->
    printfn "%s" ex.Message
    typDimTablesArray.ChangedDimApplication <- false
    ptx.Rollback()
ptx.Dispose()
dwConn.Close()
dwConn.Dispose()

[ingted]

dbSubmit loop do
loop-all batch

(following is a batch....)

loop-all SqlEntity

isdc.SubmitChangedEntity(ee)

then

isdc.SubmitPendingChangesNoClose(dbConn)

---

I hope SubmitUpdates() could do this loop-loop...

[Thorium]

Got it. But... There is already a transaction.

"Keys" are the entities and values are timestamps that the data was inserted.

I think IEnumerable won't actually mess the order by default so it should work already. We should be able to save these in order. Can you create an unit test where it goes wrong?

The thing is that I really don't want to open and close the connections between the entities:
If your database server is on a network server, and f you have 1500 entities, one insert clause is taking maybe 1ms, but opening and closing the connection takes 60ms. So all in one go is 1sec while one-by-one is 90sec (and DTC transaction timeout is 60sec so the transaction will fail).