[done] SIGSEGV by use Zeos 7.1.3a / Postgres 9.3 / Lazarus

[Michl]

Hi there,

I got a SIGSEGV, that I can't explain. The best way is, if someone has installed Lazarus, Postgres 9.3, Zeos to download my little example, there you can see it. You just have to insert your Postgres-Password and Path in Class-header, then it should run.

The bug comes, when you delete Connection.Disconnect; and Connection.Connect; in row 193 and 194:

Code: Select all

  function SpalteAnlegen: Boolean;                          //Legt die Spalte in der Tabelle an
  var
    i: Int32;
  begin
    Connection.ExecuteDirect('ALTER TABLE tab1 ADD COLUMN '+Spalte+' INTEGER;');
 
 
//    Connection.Disconnect;                                  
//    Connection.Connect;

Why do I have to disconnect and connect here?

There is a crosspost to german-Lazarus-Forum: http://www.lazarusforum.de/viewtopic.php?f=17&t=7691

Thank you, for your attention!

[Michl]

Hello again,

I am now fairly sure that this a bug in Zeos. The same minimalistic example with SQLdb as Connection and Query don't show this behavior. I've also tested with Zeos-trunc-7.2, the bug is also there.

Maybe I have to tell you something more:

This works:

1. test whether the column of table exists
2. if not, insert that column
3. Connection.Disconnect
4. Connection.Connect
5. test whether the ID exists
6. insert data, Update/Insert in case of exists (a function UpSert I can't use, cause if there are different data, I want to give it back to proof it)
next 1

This works not:

1. test whether the column of table exists
2. if not, insert that column
3. test whether the ID exists
4. insert data
next
1. test whether the column of table exists
2. if not insert that column
3. test whether the ID exists <- here it comes the SIGSEGV, why???

These are my SQL-commands:

Code: Select all

//1. test whether the column of table exists
  Query.SQL.Text:='SELECT column_name FROM information_schema.COLUMNS WHERE table_name = ''tab1'';';
  Query.Open;
     
//2. if not insert that column
  Connection.ExecuteDirect('ALTER TABLE tab1 ADD COLUMN '+Spalte+' INTEGER;');    
//or
  Query.SQL.Text:='ALTER TABLE tab1 ADD COLUMN '+Spalte+' INTEGER;';
  Query.ExecSQL;
     
//3. test whether the ID exists
  Query.SQL.Text:='SELECT * FROM tab1 WHERE id = :id;';
  Query.ParamByName('id').AsInteger:=aID;
  Query.Open;  
     
//4. Insert
  Query.SQL.Text:='INSERT INTO tab1 (id, '+Spalte+') VALUES (:id, :wert);';
  Query.ParamByName('id').AsInteger:=aID;
  Query.ParamByName('wert').AsInteger:=aI;
  Query.ExecSQL;

there is nothing special - isn't it?

[Michl]

Its a bug/feature by caching tables.

I clear that cache not fine but its useable for me:

Code: Select all

function TZPGTableInfoCache.GetTableInfo(const TblOid: Oid): PZPGTableInfo;
var Idx: Integer;
begin
  SetLength(FTblInfo, 0);  //Clear cache here
  Idx := GetTblPos(TblOid);
  if (Idx = -1) then
  begin
    if (TblOid <> InvalidOid) and (LoadTblInfo(TblOid, Idx)) then
      Result := @FTblInfo[Idx]
    else
      Result := nil;
  end
  else
    Result := @FTblInfo[Idx];
end;

thank you