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

[Randy]

Benoit Minisini wrote:
> On jeudi 22 janvier 2009, Randy wrote:
>   
>> Hello.
>>
>> New to Gambas and having problems with following code:
>>
>> STATIC PUBLIC FUNCTION CreateGLTables()
>>
>>   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_account", "account", TRUE)
>>     .Indexes.Add("glcoa_sort", "glsort", TRUE)
>>     .Update
>>    END WITH
>>
>> CATCH
>>
>>   Message.Error("CreateGLTables Function: " & DConv(Error.Text))
>> END
>>
>>
>> The error is "Cannot create index. relation glcoa does not exist." Why?
>> Can not find any information on the internet about this error.
>>
>> Help :-(
>> Randy
>>
>>     
>
> Finally, this is not a bug. Just that the database component can only create 
> indexes on already existing tables. 
>
> And the table you are creating does not exist until you call the "Update" 
> method. So you just have to move the ".Update" line just before 
> the ".Indexes..." lines.
>
> Regards,
>
> Benoit Minisini

Hello Benoit,

The above code creates an SQL statement for the index, but not for the 
table. See debug information:

No CREATE TABLE SQL when you try make table with indexes. It's missing! Why make the index sql? It should be the table first!

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 )


Remove the index and you get the CREATE TABLE sql!

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"


I think this is a bug, because gambas should make the created table sql. 
Why do the index sql? What happen to the table? Why can you make a table 
and indexes?


:-(
Randy

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Scanned with Copfilter Version 0.84beta3a (ProxSMTP 1.6)
AntiSpam:  SpamAssassin 3.2.3
AntiVirus: ClamAV 0.91.2/8935 - Sun Feb  1 10:43:52 2009
by Markus Madlener @ http://www.copfilter.org