Re: calling stored procedures

From: tnaki..ofthome.net
Date: Mon Apr 11 2005 - 07:01:55 EDT

  • Next message: Tore Halset: "Re: transactions, row locking, race condition?"

    Andrus Adamchik wrote:

    > Recently I discovered and fixed some problems in handling stored
    > procedures by PostgresAdapter -
    > http://objectstyle.org/jira/secure/ViewIssue.jspa?key=CAY-299
    >
    > However your issue doesn't seem related. The error is coming from
    > PostgreSQL side, not Cayenne (PostgreSQL has an annoying habit of
    > calling all database objects "relations" adding to confusion).
    >
    > A random suggestion - can you try removing schema part from procedure
    > definition. "public" schema should resolve implicitly.

    I worked arround the problem so everything (kind of) works.
    This is what I did...
    My java code with some fairly ugly patchwork:
    ----------------------
            Person person;
            ProcedureQuery query= new ProcedureQuery("anketirani()");
            HashMap result;
            Integer id_person = new Integer(-1);
            int safety_count= 50; // if it doesn't work 50 times, it
    probably won't work the 51st, either
            do {
                result= (HashMap) kontekst.performQuery(query).get(0);
                if (result != null)
                    id_person = (Integer)(result).get("id");
            } while (id_person == null && safety_count-- > 0);
            if (result != null) {
                person = (Person) DataObjectUtils.objectForPK(kontekst,
    Person.class, id_person);
            }
    -----------------------
    First of all, notice the do-while loop: during concurrent runs, it
    sometimes (once in several attempts) happens that rezultat.get("sifra")
    returns null even though most of the time it works! Is this the
    doesn't-return-first-row-bug you were referring to, Andrus?
    Also,notice that I use "anketirani()" instead of "anketirani" as the
    procedure name. In the postgre logs I see a "SELECT * FROM anketirani()
    AS result" query which now retrieves the row I need becouse of the added
    braces. I made the same modifications in the map file:
    ----------------------
    [...] <procedure name="anketirani()" returningValue="true">
            <procedure-parameter name="id" type="INTEGER" direction="out"/>
        </procedure> [...]
    ----------------------

    My stored procedure:
    -----------------------
    CREATE OR REPLACE FUNCTION anketirani()
      RETURNS int4 AS
    $BODY$
    DECLARE
        id integer := -1;
    BEGIN

        SELECT INTO id [...] LIMIT 1 FOR UPDATE;
        UPDATE person SET poll_time = now() WHERE id_person = id;
        RETURN id;
    END;
    $BODY$
      LANGUAGE 'plpgsql' VOLATILE;
    ----------------------

    It's all one big workarround: I hate having to use the do-while loop
    like that instead of really solving the problem, but it works, and more
    importantly, works very reliably from what I was able to test here.
    Any suggestions are very welcome, of course. Oh, right: specifying the
    "public" schema or specifying no schema at all makes no difference at all.

    Tomislav



    This archive was generated by hypermail 2.0.0 : Mon Apr 11 2005 - 06:57:59 EDT