Problem with sequences in Postgres 7.4, Bug ?
Posted: 07.11.2005, 21:18
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}
...
Patch
I tested it on Postgres 7.4 and it worked ok, i don't know how this can affect others drivers.
niztor.-
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
niztor.-