Problem with sequences in Postgres 7.4, Bug ?

[niztor]

PostgreSQL 7.4.8

Zeos 6.5.1 alpha CVS as of 13/10/2005

My english is terrible, so i'll try to explain this by example.

I've got a table defined like

CREATE TABLE blah (
nid serial ,
cod integer ,
txt char(30)
);

'nid' field is defined as a sequence , now if you insert records
with just a dbgrid (and leaves nid field blank ,with Required=false)
zeos will try to fill nid field querying the next value in the sequence;
i saw this log line using TZSQLMonitor

...
2005-11-07 13:16:10 cat: Execute, proto: postgresql-7.4, msg: SELECT
nextval('public.blah_nid_seq'::text) FROM public.blah
...

That statement is fired internally by Zeos and increments the sequence a
time by each row in the table. So, the next sequence value is almost
unpredictable and the time to complete the statement will be long on big
tables, i think.
I think it needs an adicional 'LIMIT 1' . Please, correct me if i'm wrong.

I was looking into the source and tried to fix it patching a little in
ZDbcGenericResolver.pas

{scope}

Code: Select all

procedure TZGenericCachedResolver.CalculateDefaults(
  Sender: IZCachedResultSet; RowAccessor: TZRowAccessor);
var
  I: Integer;
  SQL: string;
  SQLParams: TObjectList;
  Statement: IZStatement;

...

Patch

Code: Select all

    DefineCalcColumns(SQLParams, RowAccessor);
    SQL := FormCalculateStatement(SQLParams);
    if SQL = '' then Exit;
    SQL := SQL + ' LIMIT 1 ';		// Line added

I tested it on Postgres 7.4 and it worked ok, i don't know how this can affect others drivers.

niztor.-

[niztor]

Sorry, i'm answering myself. the 'LIMIT 1' line , does not work in tables without records.
So the problem extends to empty tables in a diferent way.
Any other posible solutions ?

niztor.-