[Gambas-user] Gambas & PostgreSQL cannot create index. relation XXX does not exist.

[Randy]

DIM hTable AS Table

  hTable = Global.$hConn.Tables.Add("glcoa")
  WITH hTable
    .Fields.Add("company", db.String, 3)
    .Fields.Add("account", db.String, 12)
    .Fields.Add("description", gb.String, 32)
    .Fields.Add("gltype", gb.String, 1)
    .Fields.Add("glsort", gb.Integer)
    .Fields.Add("status", gb.String, 1)
    .PrimaryKey = ["account"]
    .Update
   END WITH

Here's the debug output for above code. Take notice of the CREATE TABLE SQL. This work without any error.

postgresql: 0x8159de8: select substring(version(),12,5)
postgresql: 0x8159de8: show client_encoding
postgresql: 0x8159de8: select relname from pg_class where (relkind = 'r' 
or relkind = 'v') and (relname = 'glcoa') and (relnamespace not in 
(select oid from pg_namespace where nspname = 'information_schema'))
postgresql: 0x8159de8: CREATE TABLE "glcoa" ( "company" VARCHAR(3), 
"account" VARCHAR(12) NOT NULL , "description" VARCHAR(32), "gltype" 
VARCHAR(1), "glsort" INT, "status" VARCHAR(1), PRIMARY KEY (account) )
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 
"glcoa_pkey" for table "glcoa"



Change code to make index:


  DIM hTable AS Table

  hTable = Global.$hConn.Tables.Add("glcoa")
  WITH hTable
    .Fields.Add("company", db.String, 3)
    .Fields.Add("account", db.String, 12)
    .Fields.Add("description", gb.String, 32)
    .Fields.Add("gltype", gb.String, 1)
    .Fields.Add("glsort", gb.Integer)
    .Fields.Add("status", gb.String, 1)
    .PrimaryKey = ["account"]
    .Indexes.Add("glcoa_sort", "glsort", TRUE)
    .Update
   END WITH

Here's the debug output with index. Their is NO CREATE TABLE SQL when you try make table with indexes. It's missing! It looks like this is a bug in Gambas when creating table with indexes.

postgresql: 0x8159aa8: select substring(version(),12,5)
postgresql: 0x8159aa8: show client_encoding
postgresql: 0x8159aa8: select relname from pg_class where (relkind = 'r' 
or relkind = 'v') and (relname = 'glcoa') and (relnamespace not in 
(select oid from pg_namespace where nspname = 'information_schema'))
postgresql: 0x8159aa8: select pg_class.relname from pg_class, pg_index, 
pg_class pg_class2 where pg_class2.relname = 'glcoa' and 
(pg_class2.relnamespace not in (select oid from pg_namespace where 
nspname = 'information_schema')) and pg_index.indrelid = pg_
class2.oid and pg_index.indexrelid = pg_class.oid and pg_class.relname = 
'glcoa_sort'
postgresql: 0x8159aa8: CREATE UNIQUE INDEX "glcoa_sort" ON glcoa ( glsort )



Randy :-)

BTW; I'm using Gambas 2.10.2

>
> I just take a look in the source code
> ---------------------
> BEGIN_METHOD(CINDEX_add, GB_STRING name; GB_STRING fields; GB_BOOLEAN unique)
>
>   CTABLE *table = GB.SubCollection.Container(THIS);
>   char *name = GB.ToZeroString(ARG(name));
>   DB_INDEX info;
>
>   if (DB_CheckName(name, "index"))
>     return;
>
>   if (check_index(table, name, FALSE))
>     return;
>
>   info.name = name;
>   info.fields = GB.ToZeroString(ARG(fields));
>   info.unique = VARGOPT(unique, FALSE);
>
>   table->driver->Index.Create(&table->conn->db, table->name, name, &info);
>
> END_METHOD
>
>
> ----------------------------
>   if (check_index(table, name, FALSE))
>     return;
>
> 'name' here is your 'glcoa_sort'
>
> My comment here is that it _looks_ as the required name 'glcoa_sort' must 
> exist/notexist also as field name in the table.
> It should be not exists of course.
>
> In the above source the last line starting with table looks to me
> as Create index ('thedatabse', table->'glcoa_sort', 'glcoa_sort' , DB_INDEX)
>
> what results in: "glcoa_sort" ON glcoa (glsort) 
> Anyway here happens something strange to me.
>
>
>
> Did you Try it with 'glcoaaccount' and 'glcoasort' (without the underscore) ???
> and what happens then?
>
> In your reply:
> postgresql: 0x8159aa8: CREATE UNIQUE INDEX "glcoa_sort" ON glcoa ( glsort )
>
> it look as it try to use table glcoa, the name after ON !!
>
> BTW do you have a table named glcoa?
>
>
> @benoit
>
> in CIndex.h
>
> void *CINDEX_get(CTABLE *table, const char *name);
> int CINDEX_exist(CTABLE *table, const char *name);
> void CINDEX_list(CTABLE *table, char ***list);
> void CINDEX_release(CTABLE *table, void *_object);
>
> I missing CINDEX_create ????
> Second why the first is void *CIndex and the two last void CIndex ??
>
>
>
>
> Best regards,
>
> Ron_1st
>
>   


=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Scanned with Copfilter Version 0.84beta3a (ProxSMTP 1.6)
AntiSpam:  SpamAssassin 3.2.3
AntiVirus: ClamAV 0.91.2/8899 - Sat Jan 24 08:06:33 2009
by Markus Madlener @ http://www.copfilter.org