Performance Test fails

moctes · 2013-12-21 02:48:12

Hi Arnaud,

I took a break from mORMot because I had another project to finish, I'm trying to get up to date with mORMot, downloaded "mORMot and Open Source friends-98387083f90b05a3.zip" and latest development branch from ZEOS (7.2) then I proceeded to run sample "15 - External db performance" and I'm getting the following error message :

TSQLRecordSample: external table creation public.SampleRecord failed: GetFields() returned nil - SQL="CREATE TABLE public.SampleRecord (ID  INTEGER PRIMARY KEY, FirstName VARCHAR(40),LastName VARCHAR(40),Amount NUMERIC(19,4),BirthDate TIMESTAMP,LastChange BIGINT,CreatedAt BIGINT)"

I can see the table is created in the database with the 7 fields but the test doesn't continue, the problem is on the following line :

DB.Execute(Model.GetSQLCreate(t)); // don't catch exception in constructor

This is the Execute procedure

procedure TSQLRequest.Execute(aDB: TSQLite3DB; const aSQL: RawUTF8);
begin
  try
    Prepare(aDB,aSQL); // will raise an ESQLite3Exception on error
    Execute;  <--- Fail
  finally
    Close; // always release statement, even if done normaly in Execute
  end;
end;

The aSQL parameter has the following string assigned :

'CREATE VIRTUAL TABLE Sample USING External(FirstName TEXT COLLATE SYSTEMNOCASE, LastName TEXT COLLATE SYSTEMNOCASE, Amount FLOAT, BirthDate TEXT COLLATE ISO8601, LastChange INTEGER, CreatedAt INTEGER);'

Execute fails on the next function :

function TSQLRequest.Step: integer;
begin
  if Request=0 then
    raise ESQLite3Exception.Create(RequestDB,SQLITE_MISUSE);
  result := sqlite3_check(RequestDB,sqlite3.step(Request));  <-- Here is generating some kind of error which in turn generates a Rollback
end;

Don't know if this info is enough for you to help me, I ran before this sample with Zeos 7.2 and PostgreSQL 9.3 and this problem did not exist then, Do you remember some change on mORMot side that can cause this behavior? What can I do to have the sample running again?

TIA
Mocte

ab · 2013-12-21 13:03:47

Sounds like if in your case the problem is not about creating the table (it is already there), but retrieving the field layout (GetField method).

I do not have PostGreSQL here - maybe something is not working with the Zeos 7.2 metadata process or SynDBZeos use of it.
We did not find any problem with Sqlite3, FireBird or Oracle, and even tuned Zeos integration thanks to Zeos core programmers support in this forum!

May you investigate in this direction?

moctes · 2013-12-22 00:24:03

I will, altough I'm short of time, I'm afraid I will have to revert to 7.1 if it is taking much time to determine the cause :-(

EgonHugeist · 2013-12-22 09:10:38

Hi moctes,

i'm affraid too. We did add Schema+Table-Name (and it's selected columns, of course) determination from PostgreSQL via OID. The prozess is cached too. It was a patch of a Zeos-user who was running into trouble with case-in/sensitive selects. Actually i can't see more changes. So i've tryed to reproduce your issue:

CREATE VIRTUAL TABLE Sample USING External(FirstName TEXT COLLATE SYSTEMNOCASE, LastName TEXT COLLATE SYSTEMNOCASE, Amount FLOAT, BirthDate TEXT COLLATE ISO8601, LastChange INTEGER, CreatedAt INTEGER)

Fails on execution. The syntax is wrong or is it something related to mormot? Is that really the table you did play with in previous episodes?

Execute fails on the next function :
function TSQLRequest.Step: integer;
begin
if Request=0 then
raise ESQLite3Exception.Create(RequestDB,SQLITE_MISUSE);
result := sqlite3_check(RequestDB,sqlite3.step(Request)); <-- Here is generating some kind of error which in turn generates a Rollback
end;

Next surprice: Are you sure about this fail? IFAICS in mormot is this a direct sqlite call. I wouldn't start from the premise this is right. AB? Am i wrong?

OK i wanna support you too. We are short of time too. So give me an exact example to play with(a clear create table for me to work with ZDBC directly). It might be possible the latest patch makes trouble with virtual tables.

ab · 2013-12-22 09:15:29

This CREATE statement is on ORM side, not on Zeos side.

The problem is not here, but in the GetField method, when accessing the Zeos metadata: it returns no field even if the table is there, as far as I understand it.

Could anyone using PostgreSQL put a debugger break point in TSQLDBZEOSConnectionProperties.GetFields() and check what is wrong, i.e. why meta.GetColumns() returns no colum information, even if there should be some returned rows.

EgonHugeist · 2013-12-22 15:23:05

AB, i'm NOT @home yet.

just two quick suggestions:

We did introduce the IdentifierQuoting. If you're decomposing the CREATE command, do you quote the Identifier "Sample"? If not than we've got the trouble maker IF your selecting "Sample".

So try using:

CREATE VIRTUAL TABLE "Sample" USING External(FirstName TEXT COLLATE SYSTEMNOCASE, LastName TEXT COLLATE SYSTEMNOCASE, Amount FLOAT, BirthDate TEXT COLLATE ISO8601, LastChange INTEGER, CreatedAt INTEGER)

if it doesn't break your string decomposing.

Or just keep track you're selecting a lower OR uppercased table name. Otherwise, if mixed cases are used, Zeos quotes the strings and PG doen't retrieve any meta informations.

Hope it helps.

Last edited by EgonHugeist (2013-12-22 15:24:31)

ab · 2013-12-22 16:24:36

As I wrote already TWICE above, this CREATE VIRTUAL TABLE is not the problem!!!
The problem is in GetFields itself.

moctes · 2013-12-22 23:50:16

I'm late to the party huh?

mORMDebug

Arnaud, will this screenshot help you?

Regards

EgonHugeist · 2013-12-23 00:28:01

Moctes,

could you please replace this function by:

procedure TSQLDBZEOSConnectionProperties.GetFields(
  const aTableName: RawUTF8; var Fields: TSQLDBColumnDefineDynArray);
var meta: IZDatabaseMetadata;
    res: IZResultSet;
    n, i: integer;
    Schema, TableName: RawUTF8;
    sSchema, sTableName: string;
    F: TSQLDBColumnDefine;
    FA: TDynArray;
begin
  if GetDatabaseMetadata(meta) then begin
    SQLSplitTableName(aTablename, Schema,TableName);
    sSchema := UTF8ToString(Schema);
    sTableName := meta.GetIdentifierConvertor.Quote(UTF8ToString(TableName));
    res := meta.GetColumns('',sSchema,sTableName,'');
    FA.InitSpecific(TypeInfo(TSQLDBColumnDefineDynArray),Fields,djRawUTF8,@n,true);
    FillChar(F,sizeof(F),0);
    while res.Next do begin
      F.ColumnName := res.GetUTF8String(4));
      F.ColumnTypeNative := res.GetUTF8String(6));
      F.ColumnType := TZSQLTypeToTSQLDBFieldType(TZSQLType(res.GetInt(5)));
      F.ColumnLength := res.GetInt(7);
      F.ColumnPrecision := res.GetInt(9);
      FA.Add(F);
    end;
    if n>0 then begin
      res := meta.GetIndexInfo('',sSchema,sTableName,false,true);
      while res.Next do begin
        F.ColumnName := res.GetUTF8String(9));
        i := FA.Find(F);
        if i>=0 then
          Fields[i].ColumnIndexed := true;
      end;
    end;
    SetLength(Fields,n);
    exit;
  end;
  inherited; // if ZDBC metadata failed -> fall back to generic SQL-based code
end;

as i alredy suggested here: http://synopse.info/forum/viewtopic.php?id=1508
IMO the uppercasing makes trouble. And we finally can use faster Getters for the strings...

moctes · 2013-12-23 01:08:22

Done,

Still failing, I did put a breakpoint on unit ZDbcPostgreSqlMetadata.pas, function :

TZPostgreSQLDatabaseMetadata.UncachedGetColumns

The query on line :

  with GetConnection.CreateStatement.ExecuteQuery(SQL) do

SQL has the following select assigned :

SELECT n.nspname,c.relname,a.attname,a.atttypid,a.attnotnull,a.atttypmod,a.attlen,a.attnum,pg_get_expr(def.adbin, def.adrelid) as adsrc,dsc.description  FROM pg_catalog.pg_namespace n  JOIN pg_catalog.pg_class c ON (c.relnamespace = n.oid)  JOIN pg_catalog.pg_attribute a ON (a.attrelid=c.oid)  LEFT JOIN pg_catalog.pg_attrdef def ON (a.attrelid=def.adrelid AND a.attnum = def.adnum) LEFT JOIN pg_catalog.pg_description dsc ON (c.oid=dsc.objoid AND a.attnum = dsc.objsubid)  LEFT JOIN pg_catalog.pg_class dc ON (dc.oid=dsc.classoid AND dc.relname='pg_class') LEFT JOIN pg_catalog.pg_namespace dn ON (dc.relnamespace=dn.oid AND dn.nspname='pg_catalog')  WHERE a.attnum > 0 AND NOT a.attisdropped AND n.nspname = E'public' AND c.relname = E'SampleRecord'

doesn't return data, neither running it on pgAdmin

Anything else? I'll be glad to give you anything you need :-D

moctes · 2013-12-23 01:19:32

If I run the query on pgAdmin changing the case on the table name :

SELECT n.nspname,c.relname,a.attname,a.atttypid,a.attnotnull,a.atttypmod,a.attlen,a.attnum,pg_get_expr(def.adbin, def.adrelid) as adsrc,dsc.description  FROM pg_catalog.pg_namespace n  JOIN pg_catalog.pg_class c ON (c.relnamespace = n.oid)  JOIN pg_catalog.pg_attribute a ON (a.attrelid=c.oid)  LEFT JOIN pg_catalog.pg_attrdef def ON (a.attrelid=def.adrelid AND a.attnum = def.adnum) LEFT JOIN pg_catalog.pg_description dsc ON (c.oid=dsc.objoid AND a.attnum = dsc.objsubid)  LEFT JOIN pg_catalog.pg_class dc ON (dc.oid=dsc.classoid AND dc.relname='pg_class') LEFT JOIN pg_catalog.pg_namespace dn ON (dc.relnamespace=dn.oid AND dn.nspname='pg_catalog')  WHERE a.attnum > 0 AND NOT a.attisdropped AND n.nspname = E'public' AND c.relname = E'samplerecord'

This is changing: 'SampleRecord' to 'samplerecord'

The query runs successfully and returns the list of fields, note that the code creating the table is all from mORMot's sample, I'm only deleting the table before each run; I don't know it the table name should be on upper CamelCase or it should be on lowercase, and who determines that mORMot or ZEOS, but it seems the problem is there.

Thoughts?

EgonHugeist · 2013-12-23 01:55:39

moctes wrote:

This is changing: 'SampleRecord' to 'samplerecord'

PostgreSQL is lowercasing all identifiers by default while the most other providers using uppercased syntax for non-quoted identifiers. That's the reason why Zeos doesn't return any data.
Removing the IdentifierQuoting could resolve your issue but could lead to pain for others again. Let's wait for reply of Aranud. He wrote TWICE the table creation is not the proplem. Of course it isn't if selecting data from these tables happen in same way (i tryed to mention before).

moctes · 2013-12-23 02:09:52

Removing the IdentifierQuoting could resolve your issue but could lead to pain for others again. Let's wait for reply of Aranud.

Agreed, anyway is sunday night over here and I think is enough for today.

THANK YOU BOTH

moctes · 2013-12-26 14:41:23

Hi Arnaud,

What do you think? What can be done with this casing issue?

ab · 2013-12-27 14:02:19

I suspect ZDbcPostgreSqlMetadata.pas should be fixed to force lowercase, don't you think?
IMHO this should be fixed at Zeos/ZDBC level, and let the general SynDBZeos code be as generic as possible, e.g. using GetIdentifierConvertor.Quote() and such high-level functions, without any explicit workaround for PostgreSQL.

In the meanwhile, we have let TSQLDBZEOSConnectionProperties.GetFields() to fall-back to default SynDB based implementation, using raw SQL if ZDBC metadata failed to return any column description.
See http://synopse.info/fossil/info/22893f395c

What do you think?

mORMot Open Source

#1 2013-12-21 02:48:12

Performance Test fails

#2 2013-12-21 13:03:47

Re: Performance Test fails

#3 2013-12-22 00:24:03

Re: Performance Test fails

#4 2013-12-22 09:10:38

Re: Performance Test fails

#5 2013-12-22 09:15:29

Re: Performance Test fails

#6 2013-12-22 15:23:05

Re: Performance Test fails

#7 2013-12-22 16:24:36

Re: Performance Test fails

#8 2013-12-22 23:50:16

Re: Performance Test fails

#9 2013-12-23 00:28:01

Re: Performance Test fails

#10 2013-12-23 01:08:22

Re: Performance Test fails

#11 2013-12-23 01:19:32

Re: Performance Test fails

#12 2013-12-23 01:55:39

Re: Performance Test fails

#13 2013-12-23 02:09:52

Re: Performance Test fails

#14 2013-12-26 14:41:23

Re: Performance Test fails

#15 2013-12-27 14:02:19

Re: Performance Test fails

Board footer