mORMot Open Source

chapa

Member

Registered: 2012-04-30

Posts: 117

ZEOS+Postgresql CreateMultiIndex

Hi ab,

CreateMultiIndex() in case of ZEOS external Postgresql does not make multi index, but only with first column.
Also, it does not check if index name already exists (including the case when index name looks like some hash).

EgonHugeist

Member

From: Germany

Registered: 2013-02-15

Posts: 190

Re: ZEOS+Postgresql CreateMultiIndex

An example please... So i can help from Zeos-side!

chapa

Member

Registered: 2012-04-30

Posts: 117

Re: ZEOS+Postgresql CreateMultiIndex

I do not think it is related to ZEOS, but to mORMotDB.pas:

1582: function TSQLRestStorageExternal.CreateSQLMultiIndex()
1619:     for i := 0 to n-1 do begin
1626:         SetLength(ExtFieldNames,i); // <- here I suppose all fields right are trimmed

In other case I would provide example, but here I think there is no need.

chapa

Member

Registered: 2012-04-30

Posts: 117

Re: ZEOS+Postgresql CreateMultiIndex

ok, to be more precise:

here is the case when length(FieldNames) <> 1 in TSQLRestStorageExternal.CreateSQLMultiIndex():

  if not (fProperties.DBMS in DB_HANDLEINDEXONBLOBS) then
    // BLOB fields cannot be indexed (only in SQLite3)
    for i := 0 to n-1 do begin
      extfield := fFieldsInternalToExternal[IntFieldIndex[i]+1];
      if (extfield>=0) and
         (fFieldsExternal[extfield].ColumnType in [ftBlob,ftUTF8]) and
         (fFieldsExternal[extfield].ColumnLength<=0) then begin
        if i=0 then
          exit; // impossible to create an index with no field!
        //SetLength(ExtFieldNames,i); // truncate index to the last indexable field   // commend by chapa
        break;
      end;
    end;

Imagine we index on "integer,text,text"
In cycle we pass i=0 as condition fFieldsExternal[extfield].ColumnType in [ftBlob,ftUTF8] I think should not met.
Than, on next iteration, when i=1 I guess above if condition is met, we get into begin ... end, but i<>0 and we do not exit, but break the cycle setting length of the ExtFieldNames to 1 (leaving only first column to be indexed).
Later on "SQL := fProperties.SQLAddIndex(fTableName,ExtFieldNames,Unique,Descending,IndexName);" here ExtFieldNames point only on index "integer", but not "integer,text,text".

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,658

Website

Re: ZEOS+Postgresql CreateMultiIndex

???
This sounds clear enough in the comment:

// BLOB fields cannot be indexed (only in SQLite3)

So this is the expected behavior.

If there are other DB engine which allow to index BLOB fields, please report to us so that we can update DB_HANDLEINDEXONBLOBS constant.

chapa

Member

Registered: 2012-04-30

Posts: 117

Re: ZEOS+Postgresql CreateMultiIndex

indexing on columns defined as "a:integer; b:rawutf8; c: rawutf8" is indexing by blob fields?
if yes, would like to report it.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,658

Website

Re: ZEOS+Postgresql CreateMultiIndex

If the rawutf8 field has no index attribute, it is stored as a clob in the external db.

chapa

Member

Registered: 2012-04-30

Posts: 117

Re: ZEOS+Postgresql CreateMultiIndex

thanks, and please excuse me.
will try tomorrow limiting text size with limit attribute, making it varchar I guess, in order to be involved in multi index.
than will try  create multi field index again.

chapa

Member

Registered: 2012-04-30

Posts: 117

Re: ZEOS+Postgresql CreateMultiIndex

Ok, here come example:

program testmultiindex;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  SynCommons,
  SynDB,
  SynDBZEOS,
  SynSQLite3,
  SynSQLite3Static,
  mORMot,
  mORMotDB,
  mORMotSQLite3,
  System.SysUtils;

type
  TSQLTestRec = class (TSQLRecord)
  private
    fTestInt: Integer;
    fTestRawUTF: RawUTF8;
  public
  published
    property TestInt: Integer index 50 read fTestInt write fTestInt;
    property TestRawUTF: RawUTF8 index 50 read fTestRawUTF write fTestRawUTF;
  end;

var
  props: TSQLDBConnectionPropertiesThreadSafe;
  model: TSQLModel;
  server: TSQLRestServerDB;

begin
  {$WARN SYMBOL_PLATFORM OFF}
  ReportMemoryLeaksOnShutdown := True;
  {$WARN SYMBOL_PLATFORM ON}

  props := TSQLDBZEOSConnectionProperties.Create(
    TSQLDBZEOSConnectionProperties.URI(dPostgreSQL,'remoteip:port'), 'dbname','user,'pass');
  try
    props.ThreadingMode := tmMainConnection;
    props.ForcedSchemaName := 'raw';

    model := TSQLModel.Create([TSQLTestRec], 'raw');
    try
      VirtualTableExternalRegister(model, TSQLTestRec, props, '');
      model.SetCustomCollationForAllRawUTF8('BINARY');

      server := TSQLRestServerDB.Create(model, SQLITE_MEMORY_DATABASE_NAME);
      try
        server.CreateMissingTables;
        server.CreateSQLMultiIndex(TSQLTestRec, ['TestInt', 'TestRawUTF'], True);
      finally
        server.Free;
      end;
    finally
      model.Free;
    end;
  finally
    props.Free;
  end;

  ReadLn;
end.

With or without "index 50" attribute same table structure generated which can be described as:

CREATE TABLE raw.testrec (
  id INTEGER NOT NULL,
  testint BIGINT,
  testrawutf TEXT,
  CONSTRAINT testrec_pkey PRIMARY KEY(id)
) 
WITH (oids = false);

CREATE UNIQUE INDEX ndxtestrectestint ON raw.testrec USING btree (testint);

There is no difference if "index 50" property attribute is set or not, seem same "testrawutf TEXT" definition used, instead of "testrawutf VARCHAR(50)" in index case.
All the time same single column index created, instead of two column multi index over  ['TestInt', 'TestRawUTF']

Hope this will convenience you to look into this problem.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,658

Website

Re: ZEOS+Postgresql CreateMultiIndex

For PostgreSQL, TEXT is the fastest possible way of handling any kind of text.

But it may be better to be defined explicitly as a VARCHAR(50).
Even if it would not make any difference in the DB.

Try to add dPostgreSQL in the DB_HANDLEINDEXONBLOBS constant.

chapa

Member

Registered: 2012-04-30

Posts: 117

Re: ZEOS+Postgresql CreateMultiIndex

Indeed, TEXT is preffered and recommended.
There are many other "on the fly" methods in postgresql to limit the length later if needed at almost no penalty.

Adding dPostgreSQL in DB_HANDLEINDEXONBLOBS did the trick, now multi indexes are created right.
Next problem is that CreateSQLMultiIndex throws an exception on second program run.
As far as I know, there is no statement IF NOT EXISTS for index creation in Postgresql, hope I am wrong.

But ZEOS should have information about current table indexes, and by its name mormot can determine whenever to try create the index or not?
Or maybe do as described here http://dba.stackexchange.com/questions/ … -not-exist for Postgresql 9.0+

volax

Member

Registered: 2015-01-20

Posts: 3

Re: ZEOS+Postgresql CreateMultiIndex

Hi, all,

Next problem is that CreateSQLMultiIndex throws an exception on second program run.
As far as I know, there is no statement IF NOT EXISTS for index creation in Postgresql, hope I am wrong.

The problem with CreateSQLMultiIndex is in call to TSQLDBConnectionProperties.SQLAddIndex function, which generates index name.
TSQLDBConnectionProperties.SQLAddIndex contains a bug:

    if length(FieldsCSV)+length(Table)>27 then
      // sounds like if some DB limit the identifier length to 32 chars
      IndexName := IndexName+'INDEX'+crc32cUTF8ToHex(Table)+
        crc32cUTF8ToHex(FieldsCSV)+CardinalToHex(GetTickCount64) // BUG! GetTickCount64 randomizes index name!
    else
      IndexName := IndexName+'NDX'+Table+FieldsCSV;

Call to GetTickCount64 "randomizes" index name, so new indexes created again and again at every server restart, caused by failed index existence checks.
Removing "+CardinalToHex(GetTickCount64)" part of IndexName will do the trick:

    if length(FieldsCSV)+length(Table)>27 then
      // sounds like if some DB limit the identifier length to 32 chars
      IndexName := IndexName+'INDEX'+crc32cUTF8ToHex(Table)+
        crc32cUTF8ToHex(FieldsCSV)
    else
      IndexName := IndexName+'NDX'+Table+FieldsCSV;

Arnaud, is it possible to fix?

Thank you!
Best regards, Alexander.

Last edited by volax (2015-03-16 11:57:56)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,658

Website

Re: ZEOS+Postgresql CreateMultiIndex

Should be included by http://synopse.info/fossil/info/cd92f7983f

Thanks for the feedback!