Re: Problem with recreation of session_info table

["Alexey Parshin" <alexeyp@gmail.com>]

The following test works like a charm:

void loginTest(CDatabase& db) {
    CQuery loginQuery(&db,"SELECT login('alexey','password',1)");
    CQuery logoutQuery(&db,"SELECT logout()");
    loginQuery.exec();
    cout << "Logged in" << endl;
    logoutQuery.exec();
    cout << "Logged out" << endl;
    
    loginQuery.exec();
    cout << "Logged in" << endl;
    logoutQuery.exec();
    cout << "Logged out" << endl;
    
    cout << endl << "Reopening database" << endl;
    db.close();
    db.open();
    
    loginQuery.exec();
    cout << "Logged in" << endl;
    logoutQuery.exec();
    cout << "Logged out" << endl;
}

2007/3/19, Alexey Parshin < alexeyp@gmail.com>:

Can you send me the code that has that problem, as a tar archive?

2007/3/19, sergey@total-knowledge.com < sergey@total-knowledge.com>:

After I used logout() stored procedure I'm unable to recreate session_info
table.
Here is the error message which I get after
select login ('sergey', 'password12', 1);
-----
ERROR:  relation with OID 37226 does not exist
CONTEXT:  SQL statement "INSERT INTO session_info SELECT pl_id,
pl_first_name || ' ' || pl_last_name,  $1  FROM person_list WHERE pl_login
=  $2 "
PL/pgSQL function "login" line 27 at SQL statement
-----

I did some research and I found out that it could be related to PL/PgSQL
caching of function scripts. The solution is to use EXECUTE for temporary
table access. This should cause the query to be reparsed every time.
Here is what I did:

prepare testplan (varchar, varchar, int) as select login($1, $2, $3);
execute testplan('sergey', 'password12', 1);

but got exactly the same error message as above.

I guess it could be a mismatch between tables in DB b/c of 'drop' that
logout() proc performs.
Do we need to use prepared statements in stored procs to avoid this problem?

Another thing, I need session_info table with si_session column. I'll pass
session id as a parameter, as we agreed.

--
Alexey Parshin,
http://www.sptk.net

-- 
Alexey Parshin,
http://www.sptk.net