Forum OpenACS Q&A: sec_rotate_last

Posted by Charles Cruise on 02/08/02 07:29 PM

I just installed the rpms for aolserver-3.2-4, aolserver- postgresql-2.3.1, openacs-3.2.5, postgresql-7.2-0.7, postgres-server- 7.2-0.7, postgres-tcl-7.2-0.7, and postgres-libs-7.2-0.7 onto my Red Hat 7.2 machine.

After installing, I started up the postgres server and the aol server. There were no errors upon starting the aolserver. When I tried to view the index.tcl page in IE 5.50 it says The page cannot be displayed. It generates the following into the server logs:

[08/Feb/2002:11:35:35][386.19461][-conn3-] Notice: 
Querying 'select nextval('sec_id_seq') from dual;'
[08/Feb/2002:11:35:35][386.19461][-conn3-] Notice: dbinit: sql
(localhost::acs): 'select nextval('sec_id_seq') from dual'
[08/Feb/2002:11:35:35][386.19461][-conn3-] Notice: Querying '
        select password
        from users
        where user_id = 1
        and user_state = 'authorized';'
[08/Feb/2002:11:35:35][386.19461][-conn3-] Notice: dbinit: sql
(localhost::acs): '
        select password
        from users
        where user_id = 1
        and user_state = 'authorized'
        '
[08/Feb/2002:11:35:35][386.19461][-conn3-] Notice: Querying '
    insert into sec_sessions(session_id, user_id, token, 
secure_token, browser_id,
    last_ip, last_hit)
    values(2494, 1, 'lR/qJ43ZaQ3xWv0SBFGw6uaurImiPXWi', '', 1,
    '209.19.21.15', 1013193335);'
[08/Feb/2002:11:35:35][386.19461][-conn3-] Notice: dbinit: sql
(localhost::acs): '
    insert into sec_sessions(session_id, user_id, token, 
secure_token, browser_id,
    last_ip, last_hit)
    values(2494, 1, 'lR/qJ43ZaQ3xWv0SBFGw6uaurImiPXWi', '', 1,
    '209.19.21.15', 1013193335)
    '
[08/Feb/2002:11:35:35][386.19461][-conn3-] Notice: Querying 'select 
sec_rotate_last_visit(1, 1013193335);'
NOTICE:  Error occurred while executing PL/pgSQL function 
sec_rotate_last_visit
NOTICE:  line 11 at SQL statement
[08/Feb/2002:11:35:35][386.19461][-conn3-] Error: Ns_PgExec: result 
status: 7 message: ERROR:  value too long for type character(1)

[08/Feb/2002:11:35:35][386.19461][-conn3-] Error: dbinit: error
(localhost::acs,ERROR:  value too long for type character(1)
): 'select sec_rotate_last_visit(1, 1013193335)'
[08/Feb/2002:11:35:35][386.19461][-conn3-] Error: Filter 
sec_read_security_info returned error #1: Database operation "1row" 
failed (exception NSDB, "Query was not a statement returning rows.")
[08/Feb/2002:11:35:35][386.19461][-conn3-] Error: tclop: invalid 
return code from filter proc 'Critical filter sec_read_security_info 
failed.': must be filter_ok, filter_return, or filter_break

I've done a few searches and haven't really found a complete answer yet.

Thanks in advance for any help.

2: Response to sec_rotate_last_visit (response to 1)

Posted by Jonathan Ellis on 02/08/02 08:44 PM

I got this on PG 7.2 as well. Haven't had a chance to track it down yet, but you'll probably be fine on 7.1.3.

3: Response to sec_rotate_last_visit (response to 1)

Posted by Charles Cruise on 02/08/02 08:50 PM

I started with Postgres 7.1.3 which was included with Redhat 7.2, but I kept getting an error when I tried to add the pltcl language.

4: Response to sec_rotate_last_visit (response to 1)

Posted by Jonathan Ellis on 02/08/02 08:51 PM

you might try Marsden's rpms... I've always built from source, no problems there.

5: Response to sec_rotate_last_visit (response to 1)

Posted by Don Baccus on 02/08/02 09:47 PM

It is possible there's a char(1) column that's being used as a varchar, and that previous to PG 7.2 the length wasn't checked. That's a WAG, only, I haven't looked at the code, but that's where I'd start given the error message.

6: Response to sec_rotate_last_visit (response to 1)

Posted by Charles Cruise on 02/08/02 09:58 PM

I'm new to all this stuff. So, I should check in the database for a column being defined as a char(1)? I found where the function is defined in the security.sql file. The function expects integers to be passed to it and returns an integer. The only thing defined in the table sec_browser_properties is secure_p as a char(1).

Also, looking at the function v_time is defined as an integer, but in the sql statement where it inserts it into the values, it is entered as "v_time::char" Should it be this way?

7: Response to sec_rotate_last_visit (response to 1)

Posted by Jonathan Ellis on 02/09/02 12:15 AM

the ::char is to coerce pg to insert the int into the property_value field which is defined as a varchar(4000)... I'd guess this is the problem and you need to cast it to a varchar(foo). Dunno if this is strictly kosher in pg or not.

8: Response to sec_rotate_last_visit (response to 1)

Posted by Charles Cruise on 02/09/02 02:04 AM

Thank you to all who replied. I ended up having to drop the function in postgresql and re-create the function substituting v_time::char with v_time::varchar since I couldn't figure out a way to modify the function.

9: Response to sec_rotate_last_visit (response to 1)

Posted by Jonathan Ellis on 02/09/02 02:07 AM

drop & recreate is really the only way to modify stored procs in any rdbms...

10: Response to sec_rotate_last_visit (response to 1)

Posted by Daryl Biberdorf on 02/09/02 03:18 AM

drop & recreate is really the only way to modify stored procs in any rdbms...

Not, strictly speaking, true, since Oracle can do a CREATE OR REPLACE PROCEDURE x. The "OR REPLACE" is nice in that it preserves any permissions granted on the procedure.

11: Response to sec_rotate_last_visit (response to 1)

Posted by Don Baccus on 02/09/02 03:19 AM

Brand-new in PG 7.2 is "create or replace function". This has the advantage of not breaking other compiled functions which refer to your function. Oracle's had this forever (for the same reason).

When we finally ditch PG 7.1 support (later this year, not in the near term, of course!) we'll get someone to write a little sed script to replace all of the "create function" statements with "create and replace".

Anyway, yes, the ::char was a mistake, uncaught by PG 7.1 and earlier. In reality it was treating it like a varchar but it was still an error.

If someone wants to gather up a patch and put it into the SDM it would be deeply appreciated ...

12: Response to sec_rotate_last_visit (response to 1)

Posted by Jonathan Ellis on 02/09/02 03:51 AM

these are really syntactic sugar... you're still recreating the proc, not modifying it in the sense of changing the bytecodes or whatever the internal representation is. So I think my original statement was, "strictly speaking, true." :-b

13: Trezzo's recompile_all_objects for Oracle (response to 1)

Posted by Andrew Piskorski on 02/14/02 07:46 PM

That is very cool about PG 7.2 having "create or replace". Just one note though, I don't know how smart PG is, but when you "create or replace" a function or view or whatever in Oracle, it will invalidate all other objects that depend on the thing you just replaced!

If you're lucky, you will never notice, because Oracle will attempt to recompile the invalidated objects in place the next time they are used. But if, say, you changed the interface to the function you just replaced, anything calling it will now break, and if replacing the function is all you do, you will probably not find out things are broken until runtime!

The solution to this (for Oracle) is Trezzo's super-useful recompile_all_objects PL/SQL procedure. After you make you changes, just do "exec recompile_all_objects" in sqlplus.

14: Response to sec_rotate_last_visit (response to 1)

Posted by Don Baccus on 02/14/02 07:58 PM

Hmmm...in PG if the param signature's the same it does what you want, that's the point of CREATE OR REPLACE. If you change the param signature then existing objects will call the old function (which won't be REPLACE'd because it's a different function due to the different param list). Maybe or maybe not what you want ... if you change the signature you should drop the old function if you're really meaning to replace the old with the new.

We don't have packages, which probably makes the problem a lot worse in Oracle ...

15: Response to sec_rotate_last_visit (response to 1)

Posted by Jonathan Ellis on 02/14/02 08:08 PM

man, I need a PG recompile_all_objects...

16: Response to sec_rotate_last_visit (response to 1)

Posted by Don Baccus on 02/14/02 09:19 PM

Have you played with PG 7.2's CREATE OR REPLACE? I think it's implemented for views, too. It should get rid of just about all the hassles involved in replacing the body of a function or the select-clause of a view.

17: Response to sec_rotate_last_visit (response to 1)

Posted by Andrew Piskorski on 02/15/02 05:10 PM

I believe Oracle looks only at the function name when replacing functions, not the signature. Yep, that seems to be true, e.g.:

SQL> create or replace function atp_test
return integer
is
begin
  return 0;
end;
/
show errors
  2    3    4    5    6    7  
Function created.
SQL> No errors.

SQL> select atp_test from dual;
  ATP_TEST
----------
	 0

SQL> select atp_test(1) from dual;
select atp_test(1) from dual
       *
ERROR at line 1:
ORA-06553: PLS-306: wrong number or types of arguments in call to 'ATP_TEST'

SQL> create or replace function atp_test (
  v_id  in  integer
) return integer
is
begin
  return v_id;
end;
/
show errors
  2    3    4    5    6    7    8  
Function created.
SQL> No errors.

SQL> select atp_test from dual;
select atp_test from dual
       *
ERROR at line 1:
ORA-06553: PLS-306: wrong number or types of arguments in call to 'ATP_TEST'

SQL> select atp_test(1) from dual;
ATP_TEST(1)
-----------
	  1

18: Response to sec_rotate_last_visit (response to 1)

Posted by Andrew Piskorski on 08/02/02 11:08 PM

FYI, Tom Kyte also gives and discusses a solution similar to Trezzo's recompile_all_objects.

Back to OpenACS Q&A

Forum OpenACS Q&A: sec_rotate_last_visit