Postgres + doCalcDefaults + Bigserial Column = 2 on 2 seq.!

Post by **mdaems** » 30.12.2011, 16:11

This gap 'on error' isn't exactly wrong behaviour, just an inconvenience which you could indeed code around with lots of effort. It's just normal to lose a sequence number now and then. Not sure how pg stored procedure language reacts when you insert and rollback immediately. In Oracle pl/sql you'll certainly have a missing value.

Of course, the general bigserial problem is a real bug. And it would be nice if somebody codes around it.

Mark

Ðerek wildstar · Post by **Ðerek wildstar** » 30.12.2011, 16:35

Sorry for the english:

Hello Mark, how are you?

It seems that you do not understand the problem completely. I totally agree that it is more an inconvenience than a bug, but it is an inconvenience that can be repaired.

The sequences in Postgres work as any sequence of any database, ie, increasing 1 on 1 and obviously never reusing a previously generated sequence number. Postgres works just like the Oracle, after performing an insert and then roll back the last number generated will no longer be used in a subsequent insert.

Well, as the source code to be repaired just about to Postgres, I would be happy to repair, thus contributing to the community that uses the ZeosLib with Postgres, but for that I need his permission (login and password) to make commit to the ZeosLib repository.

If you are willing to give me this permission so that I can fix just that I also like to know how my upgrade would be tested, that is, it would be tested by someone else or just me?

Post by **mdaems** » 30.12.2011, 16:48

Of course I'm willing to give you this permission. The only thing I need is your sourceforge username (or you'll have to create a name there). If you want to keep it confidential, please send it to my private email account mdaems (ad) advalvas (point) be

BTW, I do understand the problem exactly. My last message referred to the special case after an error occurred. I totally agree that an automatic double sequence increment for every insert is totally useless and therefore a bug.

Mark

Ðerek wildstar · Post by **Ðerek wildstar** » 30.12.2011, 17:09

Hi Thank you. I'll register to sourceforge and give you my info by PM!!

Wild_Pointer · Post by **Wild_Pointer** » 02.01.2012, 12:30

Hello, all

in my company we work with Postgresql and Zeos7. We use serial attributes as keys for many tables and we have a lot of forms with TZQuery components and id fields have AutoGenerateValue = arNone. This works fine for us - sequence is incremented by 1.
If I understand you correctly after you will fix the "bug" our soft will stop working... I doubt we are the only ones relying on this "buggy" behavior and I think this behavior is true for Zeos v6.6.6 too. Are we ready to fix this and brake the soft that worked with earlier versions of Zeos?

Ðerek wildstar · Post by **Ðerek wildstar** » 02.01.2012, 14:01

This patch is only for Postgres. This applies to you? If yes, I do not know why it would break your software, since it only applies to option "doCalcDefaults " and not "AutoGenerateValues".

Even so, the patch is applied to testing branch and only after this the correction will be placed on a branch "production"

Wild_Pointer · Post by **Wild_Pointer** » 02.01.2012, 15:21

Hello,

I must have misunderstood your intentions. If you say nothing will brake - its good to hear. Please tell when can I checkout the testing branch to test.

Tried inserting to tables with bigserials - everything seems to work ok with my version of Zeos. Adding test program with DB backup file (db name should be test).

my environment: Win Xp, Delphi 2006, Zeos 7, Postgres 8.3

Ðerek wildstar · Post by **Ðerek wildstar** » 02.01.2012, 17:35

No problem.

I'll do it as soon as possible.

Ðerek wildstar · Post by **Ðerek wildstar** » 28.01.2012, 01:44

Now I am ready to correct this behavior, I'm not sure if it really is necessary. A default value is usually a static value. For a column associated with a sequence in PostGres what should be returned? If I return the NEXTVAL (currently happening) I get a unique number, however, this affects the value of the sequence, which is unacceptable. If I return CURRVAL (which was my original proposal of correction), however, I would be returning a number that has been used and possibly to generate a key violation. I'm at an impasse. What is the best solution to this problem? Another way to fix that I thought was simply returning null, or zero, since it is not possible to determine a default value for this column type in this situation.

So what to do? Suggestions are very welcome!

enix · Post by **enix** » 03.02.2012, 00:11

I know exactly this problem. First - You are using ZEOS 6.xxx, it's behavior is that after insert record default value is obtained from server, after post record default value is obtained from server again. Thats why value of field increases by 2. I suggest to install ZEOS 7 - all works fine, default vaule is obtained from server only once when record i posted.

Ðerek wildstar · Post by **Ðerek wildstar** » 03.02.2012, 03:27

Hello!

My tests were done on the test branch. This branch has the problem or not? I'm using Delphi XE

osvaldotcf · Post by **osvaldotcf** » 29.09.2015, 22:49

Lazarus 1.5
FPC 3.1.1
Zeoslib 7.1.4

How do I fix this?

I have not found the property "AutoGenerateValue"

How do I set for "arNone" ?

Thanks.

marsupilami · Post by **marsupilami** » 01.10.2015, 09:33

Hello osvaldotcf,

hmmm - is it possible for you to use TZSequence and a bigint column as a workaround? Or do you have to stick with the bigserial column?
With best regards,

Jan

Zeoslib Portal

Postgres + doCalcDefaults + Bigserial Column = 2 on 2 seq.!

...

...

Re: Postgres + doCalcDefaults + Bigserial Column = 2 on 2 seq.!

Re: Postgres + doCalcDefaults + Bigserial Column = 2 on 2 seq.!