Hi,

When inserting or updating entity that has column with default value from sequence (nextval function in postgresql) value I pass for that column is ignored.

In update query, parameter has this value: <undefined value>.

In insert query, there is no parameter at all, in VALUES in place of value is: nextval('"public"."MySeq"').

When inserting new entity I would expect that only if I pass null, not if I pass value.

Is this intentionally? If so maybe field should be readonly in LLBLGen designer.

LLBLGen 4.2 PostgreSQL 94.

Thank you for your help, David.

It sounds like the table metadata is telling LLBLGenPro that the field is sequenced, so it's treated like that. How does the DDL script of that table look like?

BTW, you could remove the sequence from the field mappings tab of the entity in LLBLGen Pro Designer. That way, it will be used by your DB engine only if it's not specified in your .net code.

DDL:

ALTER TABLE "public"."MyTable" ALTER COLUMN "MyField" SET DEFAULT nextval('"MySeq"'::regclass);

When I remove sequence, on insert I can set value, but when I pass null sequence is not used. Update doesn't work, field is not in query, I can't update row, I'm using uow.AddUpdateEntitiesDirectlyCall.

The default sequence (the sequence in the default value) is used in inserts. When a sequence is set, like in your table, the value specified is ignored, as the sequence is to be used, this is by design. So you don't have to specify a value, when inserting, it will insert a value from the default sequence.

When updating, sequences are never used, a field is updated with the value you specify. So if that's null, null is the new value.

The sequence is read as metadata when you refresh the catalog in the designer, and when you create entities, the sequence should be set as the default sequence for the field, but it's only used for inserts.

I'm not entirely sure what the order of events is that you want to perform, could you give a small code example what you tried to and what failed? Thanks

For databases, they use default values on insert, not update. At least I never heard of a database which will use a default value if you update a field to null (it will instead simply give an error), they will only use defaults when a new row is inserted and the field is NULL

Scenario is following:

For records on import I have values and I want to keep them on insert, not to use new value generated by sequence.

For new record I want to use new value generated by sequence.

Sequence has offset so there is no overlapping.

Update doesn't work, I don't want to use sequence value on update I want to set value manually for records that I have values and can't set them on insert.

newEntity.SomeOtherField = "test";
newEntity.SequenceField = -100;
uow.AddUpdateEntitiesDirectlyCall(newEntity, bucket);

SomeOtherField is update and SequenceField is not, it keeps old value. In sql update statement I don't see SequenceField.

I don't see anything special about this field in LLBLGen designer, only sequence in field mappings.

Reproduced...

LLBLGen v.5.2.2 PostgreSQL v.10 Npgsql v.3.2.5

DDL

CREATE TABLE public."Customer"
(
    "Id" integer NOT NULL,
    "Name" text COLLATE pg_catalog."default" NOT NULL,
    "Code" integer DEFAULT nextval('"defaultCode"'::regclass),
    CONSTRAINT "CustomerPK" PRIMARY KEY ("Id")
)

Insert Succeed with default value set on the database:

var customer = new CustomerEntity();
customer.Name = "ABC";
customer.Id = 1;
adapter.SaveEntity(customer);

Then I removed the Read-Only flag on the "Code" field from the Designer. The following insert also succeeds

var customer = new CustomerEntity();
customer.Name = "DEF";
customer.Id = 2;
customer.Code = 2;
adapter.SaveEntity(customer);

The following update fails, whether the "Code" is set to null or to another int value:

var customer = new CustomerEntity(2);
adapter.FetchEntity(customer);

customer.Code = 3;
customer.Name = "DZY";
adapter.SaveEntity(customer);

Generated SQL:

UPDATE "public"."Customer" SET "Name"=:p1 WHERE ( "public"."Customer"."Id" = :p2)
    Parameter: :p1 : String. Length: 1073741824. Precision: 0. Scale: 0. Direction: Input. Value: "DZY".
    Parameter: :p2 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 2.

Looking into it.

The reason this happens is because a sequence is assigned to the field, and it's therefore seen as an 'identity' field. Even if it's not marked as 'readonly' it's still seen as an identity field, and the runtime skips changed identity fields (as it would cause errors in some databases). In general this isn't a problem as fields which have a sequence assigned are never written to, other than the value obtained from the sequence.

However, in your case this is a problem.

I understand from your last post you import data which is then written to the DB? Only for inserts you want to use the sequence, and updates should always use the value specified?

I don't know how many entity types you have this with, if it's a couple, you could try to map those entities again (but now without a sequence) and e.g. rename them with an 'ForUpdate' suffix (so CustomerForUpdate) so you can use that entity to update the underlying rows.

If all the entities in your project are affected then it's of course a bigger problem. It's not common to have a sequenced field which isn't a PK, and also if it's sequenced to update it. I guess you have this because of data which is e.g. 'synced' from a different location with a main database?

In theory, there's another way (modify the persistence info object when it's obtained from the persistence provider for a given scenario) but that's quite complex and as it hides things, it's not recommended, as it's not really clear from looking at your code what might happen. (it requires a class derived from DataAccessAdapter which overrides InsertPersistenceInfo )

When we import from old DB we want to keep old value, when we insert new we want to use sequence. We noticed it didn't use value we were passing on import so we tried update after that, but it didn't work. We end up using raw sql to update to old values after import, because it's just a few fields.

Thank you.

Otis wrote:

I don't know how many entity types you have this with, if it's a couple, you could try to map those entities again (but now without a sequence) and e.g. rename them with an 'ForUpdate' suffix (so CustomerForUpdate) so you can use that entity to update the underlying rows.

Brilliant idea for a workaround.

But as I know you can do better: May I kindly ask, whether we can have some kind of switch on the adapter or the entity to enable inserting and updating sequenced fields?

No offense taken, if you answer this with "No, you may not!".

What's your use case for this? the thing with a flag on adapter is that saves are recursive, so it then is for all entities in the graph. If the flag is on the entity, it's rather cumbersome though, as the sequence isn't tied to the entity but to the mapping. So all in all it's a bit cumbersome to fix this, and in general it's not common to have this, other than importing data.

Otis wrote:

What's your use case for this?

Currently using LLBLGen to replicate two identical PostgreSQL databases. Replicated DB is meant for readonly access and as backup. Was too lazy to dig deeper into postgress log shipping documentation and to actually set it up.

Currently using your above workaround. It's working fine if you remove the sequence and the readonly flag from the sequenced id fields in the "ForSync" version of the entity.

Some kind of switch still would be nice to have. (Perhaps in the DQE?) So it's just a "Nice to have" feature, not more. No worries, if it seems too difficult to get implemented.

I'll see what we can do in the future But it likely won't make it in, but who knows

It's all good.

Just a thought:

Another way of achieving at least identity insert:

If LLBLGen runtime gave us access to the sequences, user could set the currentval of the table's sequence in code before inserting a new record.

Does that sound a cunning plan? Not sure.

More cunning plans: http://blackadderquotes.com/i-have-a-cunning-plan

You can pull the currentvalue (or better, the NEXTVAL) for the sequence using a plainsql query of course, then assign that value to an entity field. Not that great I think, as it requires a lot of overhead to get that value inserted...