Thread: [QUESTIONS] slow "select lo_unlink(..) where ...;" ?

Hi Hackers,

    When I record ~ 1000 records && blobs  and try to remove, it takes
forever!
e.g.

create table PNT (
        id              int not null,   -- database record number
        exp             int not null,           -- experiment number
        run             int not null,           -- run
        run_to          int not null,           -- run to(valid range)
        version         int not null,           -- version number
        datatype        text default 'blob',    -- data type
        created         timestamp default current_timestamp,    --
creation time
        modified        timestamp default current_timestamp,    --
modification time
        owner           name default getpgusername(),   -- owner
        loid            oid default 0,          -- reference to pnt bank

constraint PNT_con check(run>0 AND run<=run_to AND version>0)
);

... and deposit ~ 1000 blobs(large objects with size ~ 10k), it takes ~
2 sec/record seems to be reonable.
But, deleting with

   result = PQexec(conn,
     "DECLARE pntcur CURSOR FOR "
     "SELECT count(lo_unlink(int4(oid_text(loid)))) "
     "FROM PNT"
     ";");
   PQclear(result);
   result = PQexec(conn, "FETCH 1 IN pntcur;");

OR

   sprintf(cmd,
    "DECLARE pntcur CURSOR FOR "
      "SELECT count(lo_unlink(int4(oid_text(loid)))) "
        "FROM PNT WHERE exp = %d"
    ";"
    , exp);
   result = PQexec(conn, cmd);
   PQclear(result);
   result = PQexec(conn, "FETCH 1 IN pntcur;");

takes forever!  "destroydb" also... something to do with inefficient
"inv-tree"?

is there any plan to recall "simple unix file" based blobs?  I guess
that "inv" based blob seems to be
really inefficient... any comment?

best regards, cs