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

Forum related to PostgreSQL

Moderators: gto, cipto_kh, EgonHugeist, olehs

User avatar
mdaems
Zeos Project Manager
Zeos Project Manager
Posts: 2766
Joined: 20.09.2005, 15:28
Location: Brussels, Belgium
Contact:

Postby 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
Image

Ðerek wildstar
Junior Boarder
Junior Boarder
Posts: 35
Joined: 30.08.2005, 16:30
Location: Olinda / PE
Contact:

...

Postby Ð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?

User avatar
mdaems
Zeos Project Manager
Zeos Project Manager
Posts: 2766
Joined: 20.09.2005, 15:28
Location: Brussels, Belgium
Contact:

Postby 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
Image

Ðerek wildstar
Junior Boarder
Junior Boarder
Posts: 35
Joined: 30.08.2005, 16:30
Location: Olinda / PE
Contact:

Postby Ðerek wildstar » 30.12.2011, 17:09

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

Wild_Pointer
Expert Boarder
Expert Boarder
Posts: 159
Joined: 18.03.2008, 13:03
Contact:

Postby 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
Junior Boarder
Junior Boarder
Posts: 35
Joined: 30.08.2005, 16:30
Location: Olinda / PE
Contact:

Postby Ð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
Expert Boarder
Expert Boarder
Posts: 159
Joined: 18.03.2008, 13:03
Contact:

Postby 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
You do not have the required permissions to view the files attached to this post.

Ðerek wildstar
Junior Boarder
Junior Boarder
Posts: 35
Joined: 30.08.2005, 16:30
Location: Olinda / PE
Contact:

...

Postby Ðerek wildstar » 02.01.2012, 17:35

No problem.

I'll do it as soon as possible.

Ðerek wildstar
Junior Boarder
Junior Boarder
Posts: 35
Joined: 30.08.2005, 16:30
Location: Olinda / PE
Contact:

Postby Ð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
Fresh Boarder
Fresh Boarder
Posts: 2
Joined: 02.02.2012, 22:55

Postby 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
Junior Boarder
Junior Boarder
Posts: 35
Joined: 30.08.2005, 16:30
Location: Olinda / PE
Contact:

Postby Ð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
Fresh Boarder
Fresh Boarder
Posts: 6
Joined: 09.03.2007, 22:40

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

Postby 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
Gold Boarder
Gold Boarder
Posts: 581
Joined: 17.01.2011, 14:17

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

Postby 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


Return to “PostgreSQL”

Who is online

Users browsing this forum: No registered users and 1 guest