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

[Ðerek wildstar]

Hello Everybody!

I'll be direct on my post...

I'm using now the PostgreSQL database. I'm using BIGSERIAL columns, to take advantage of sequences to primary keys. This is very usual, i guess.

So, I've done a little application to test this behaviour. Everything was fine, except that my primary key column is being filled with values like: 2, 4, 6, 8, 10... 2 on 2.

When i turn off the "doCalcDefaults" option, the sequence works like a charm, but, eventual blank fields with default values aren't being automatically filled after insert because the state of doCalcDefaults is "off"

Doing a debug i found that TZGenericCachedResolver.FormCalculateStatement is generating a SQL with the default value of my bigserial column. Something like "SELECT NEXTVAL(COLUMNNAME)". On postgres, the default value of a column is used to make sequences.

Well. I do not want to change this source code. You, commiters/developers of ZeosLib, have some plan to fix this?

[Ðerek wildstar]

Sorry, but UP!

[mdaems]

Ðerek wildstar,
I could say 'DOWN', but that would be rude, I suppose.

I don't really understand the nature of your problem. Maybe because I'm not a postgres user.
Do I understand correctly that you're using a simple TZQuery component not combined with TZSequence component? Or are you combining both components AND setting a default value on your database column which also calculates using the same sequence?

In the first case there's probably a zeoslib issue.
But when you're combining the 2 components that's a bad idea. Zeoslib is smart enough to fill the data field with your database default value with doCalcDefaults, so you should not use the same sequence to overwrite the value again.

Concerning

You, commiters/developers of ZeosLib, have some plan to fix this?

Unfortunately, I don't have the time nor the knowledge to fix this when it turns out to be a real zeoslib problem. You might have noticed we're really looking for commiters. Just come forward with your sourceforge username and I give you commit rights so the problem can be fixed by you.

Mark

[Ðerek wildstar]

Sorry for UP...

Well I'm not using two components only one. The last paragraph is very explanatory. I will complete it:

Doing a debug i found that TZGenericCachedResolver.FormCalculateStatement is generating a SQL with the default value of my bigserial column. Something like "SELECT NEXTVAL(COLUMNNAME)". On postgres, the default value of a column is used to make sequences.

This way every time Zeos get the default value for a column with this "autoincrement" then the autoincrement value increases, but this is not to be done. On execute "SELECT NEXTVAL(COLUMNNAME)" the autoincrement value increases, and because of this the autogenerated keys are being generated "2 on 2".

Sorry my very bad english.

[mdaems]

Does that mean a BIGSERIAL column ALWAYS increments, even if it gets a value passd in the insert statement?
Why do you need a DEFAULT value in the column definition then? Or is that something that's generated automatically by postgres in the special case of a bigserial? If that's true, the somebody should disable this behaviour of getting a default value for bigserial columns (and similar datatypes, of course) AND make sure the autoincremented value is retrieved in a way that's similar to the method used for mysql autoincrements.

So please can you confirm this special BIGSERIAL behaviour works the way I understand and
- direct us to the documentation that proves this
- think if you could be the one who fixes this

Mark

[Ðerek wildstar]

Hello Mark, thanks for the answer. Here comes more informations...

In PostGres the BIGSERIAL type is lika an alias for a BIGINT column with a automatically generated default value of something like "nextval('sequencename'::regclass)". More detailed:

After execute the following DDL

Code: Select all

create table TEST (
  ID BIGSERIAL NOT NULL,
  CONSTRAINT PK_TEST PRIMARY KEY (ID)
)

The real table definition turns

Code: Select all

CREATE TABLE test
(
  id bigint NOT NULL DEFAULT nextval('test_id_seq'::regclass),
  column_name character varying(10),
  CONSTRAINT pk_test PRIMARY KEY (id )
)

When doCalcDefaults are "ON" the ZeosLib PostGres implementation in "TZGenericCachedResolver.FormCalculateStatement" does "SELECT DEFAULT VALUE". If i have an integer or varchar default value, this approach is perfect, however on this special case the generated select will be

Code: Select all

SELECT nextval('test_id_seq'::regclass)

This command does not raise any exception. In fact it's correct by itself, but, on execute it, the 'test_id_seq' will be incremented on database server, so, a subsequent insert will not fill the ID column with the next value but with the next next value, ie, one for the insert and another for the "get default value", triggered when doCalcDefaults = true

For this special case, the following command would be used

Code: Select all

SELECT CURRVAL('test_id_seq')

This command gets the current value of the sequence, more or less like the MySQL LAST_INSERT_ID()

Sorry, I have no time now to do this fix on Zeos. I do not even have a login to do it also . And, I guess, I'm not the correct person to do that becouse I do not know the possible impacts of change something on Zeos Trunk.

[papelhigienico]

Can you post a small example of your problem (with database) here? A example will make my life more easy

[Ðerek wildstar]

Can you post a small example of your problem (with database) here? A example will make my life more easy

Oi "papelhigienico" (!)

Na verdade já coloquei toda informação aqui. Não há mais nada para exemplificar. A funcionalidade ativada com a opção "doCalcDefaults" faz com que a sequencia do PostGres associada com o campo chave seja executada uma vez mais, após um registro ter sido salvo (post).

Dessa forma, postagens de inserts subsequentes sempre terão o valor da chave +2 ao invés de +1.

Isso acontece porque esta funcionalidade (doCalcDefaults) não é suficientemente inteligente para distinguir entre o valor de um campo default ser um simples varchar ou integer de uma função de sequencia (nextval).

Code: Select all

ID INTEGER NOT NULL DEFAULT 1

O campo acima funcionaria bem com a atual implementação do Zeos, pois ele iria executar "SELECT 1" que retorna 1, que é o valo do campo default. Correto! No entanto...

Code: Select all

ID INTEGER NOT NULL DEFAULT NEXTVAL('SEQUENCIA'::regclass)

Gera este SQL

Code: Select all

SELECT NEXTVAL('SEQUENCIA'::regclass)

O qual incrementa o valor da sequencia, quando o que queríamos na verdade era apenas obter seu valor e neste caso o que deveria ser executado era

Code: Select all

SELECT CURRVAL('SEQUENCIA')

O ZeosLib não faz esta conversão. Ele executa o que tem depois do "DEFAULT" diretamente, e este é o problema.

In English (Google Translator)

Hi "papelhigienico" (!)

In fact I already put all the information here. There's nothing else to exemplify. The functionality enabled with the "doCalcDefaults" makes Postgres sequence associated with the key field to run again after a record has been saved (post).

Thus, subsequent postings will always inserts the key value +2 instead of +1.

This is because this feature (doCalcDefaults) is not smart enough to distinguish between the value of a default to be a simple integer or varchar from a sequence function (nextval).

Code: Select all

ID INTEGER NOT NULL DEFAULT 1

The field above work well with the current implementation of Zeos, because running "SELECT 1" returns 1, which is the default value of the field. Correct! However ...

Code: Select all

ID INTEGER NOT NULL DEFAULT nextval ('sequence':: regclass)

Generates this SQL

Code: Select all

SELECT NEXTVAL ('sequence':: regclass)

Which increases the value of the sequence, when what was really just wanted to get its value and in this case what should be executed was

Code: Select all

CURRVAL SELECT ('sequence')

The ZeosLib don't make this conversion. It run the piece after the "DEFAULT" directly, and this is the problem.

[papelhigienico]

Vou verificar isto. Você está usando Lazarus, Delphi, Zeos 7 ou 6?

I'll check this. Are you using Lazarus, Delphi, Zeos 7 or 6?

[papelhigienico]

This occurs only with the first record inserted, right?

Isto ocorre somente com o primeiro registro inserido, certo?

[Ðerek wildstar]

Não percebi se isso acontece sempre ou só da primeira vez.

Estou usando Delphi XE

----

I do not know if this happens only first time or always.

I'm using Delphi XE

[trupka]

Ðerek wildstar,
I tried to reproduce your problem with D2007/2010 with Zeos 7 Alpha but no success.
Can you provide some code sample?

[Ðerek wildstar]

Since the day of first post of this thread i could not reproduce the error anymore, but I guess it's simple

1 TZConnection (autocommit = true)
1 TZQuery
1 TDataSource
1 TZUpdateSQL
1 TDBGrid
1 TDBNavigator
1 Postgres Database

1. Configure and link everything
2. Keep the doCalcDefaults of the TZQuery activated (default)
3. Create a postgres table with only two columns, one of them is a bigserial colum. The other can be anything.
4. Configure the TZReadOnlyQuery with the three SQLs. On Insert SQL use only the second column; as the first is BigSerial you do not need to care about it
5. Configure the TDBGrid to see all columns, including the BIGSERIAL one
6. Run the application
7. Insert a new field
8. Post and press refresh
9. Insert another new field
10. Post and press refresh
11. See the BIGSERIAL column

If the test is successful the first record has 1 on the bigserial column and the second has 3, not two. I'm not sure about the first and second values but do not matter. What only matter is that the bigserial column is being incremented two on two

[papelhigienico]

I found another issue related with bigserial. If you try insert, and something on the record inserted is wrong (like key duplicity) the value of SERIAL/BIGSERIAL is incremented 2 or more times.

[Ðerek wildstar]

Yes. That's the problem. On error, the persistent field try to get the default value, and so the sequence is executed! SELECT DEFAULTVALUE translates into SELECT NEXTVAL('SEQUENCENAME') but would be SELECT CURRVAL('SEQUENCENAME'). This is a special case for the postgres database and auto increment fields (sequences)

I found another issue related with bigserial. If you try insert, and something on the record inserted is wrong (like key duplicity) the value of SERIAL/BIGSERIAL is incremented 2 or more times.