Thread: 'now' doesnt seem to work in stored procedure

hi all,
i am using postgreSQL 7.1.3 and i am new to postgres.

i have 2 queries

Query 1:
=========
I used the following commands to add pl/pgsql language to the database.

1) create function plpgsql_call_handler() returns opaque as
   '/usr/lib/pgsql/plpgsql.so' language 'c';

2) create language 'plpgsql' handler plpgsql_call_handler lancompiler
   'pl/pgsql';

I am able to execute stored procedures. But sometimes i get this message.

ERROR:  fmgr_info: cache lookup for language 1952725 failed

After which i have to drop the database and create again.

Can anybody tell me why this happens and the remedy.

Query 2:
========
Inside a stored procedure i am using 'now' for a datetime value.
Wat happens is when the stored procedure is loaded, the 'now' is
initialised and afterwards whenever i use this procedure, the same value
is being inserted.
Is my understanding correct and am i going wrong somewhere.

the procedure i am using is

create function login (int2) returns int2 as '
DECLARE
arguserid   alias for $1;

begin
        insert into useraccounting (userid, logintime) values
        (arguserid, 'now');

return 0;
end;
' language 'plpgsql';

Help me.

--
regards,
hari

HK wrote:
> hi all,
> i am using postgreSQL 7.1.3 and i am new to postgres.
>
> i have 2 queries
>
> Query 1:
> =========
> I used the following commands to add pl/pgsql language to the
> database.
>
> 1) create function plpgsql_call_handler() returns opaque as
>    '/usr/lib/pgsql/plpgsql.so' language 'c';
>
> 2) create language 'plpgsql' handler plpgsql_call_handler lancompiler
>    'pl/pgsql';
>
> I am able to execute stored procedures. But sometimes i get this
> message.
>
> ERROR:  fmgr_info: cache lookup for language 1952725 failed
>
> After which i have to drop the database and create again.
>
> Can anybody tell me why this happens and the remedy.
>
> Query 2:
> ========
> Inside a stored procedure i am using 'now' for a datetime value.
> Wat happens is when the stored procedure is loaded, the 'now' is
> initialised and afterwards whenever i use this procedure, the same
> value is being inserted.
> Is my understanding correct and am i going wrong somewhere.
>
> the procedure i am using is
>
> create function login (int2) returns int2 as '
> DECLARE
> arguserid   alias for $1;
>
> begin
>         insert into useraccounting (userid, logintime) values       
> (arguserid, 'now');
>
> return 0;
> end;
> ' language 'plpgsql';
>
> Help me.
>
> --
> regards,
> hari
>

Not mush idea of Query 1 (I always use the createlang script and haven't had a problem)
Query 2:
use now() or CURRENT_TIMESTAMP
Or, to get different times within a transaction: timeofday()
hth,
- Stuart

HK <harikrishnan@midascomm.com> writes:
> I am able to execute stored procedures. But sometimes i get this message.
> ERROR:  fmgr_info: cache lookup for language 1952725 failed

Sounds like you dropped the plpgsql language while there were still
functions that used it.  Not a good thing to do.

(7.3 will prevent you from making this class of mistake, btw)

> Inside a stored procedure i am using 'now' for a datetime value.
> Wat happens is when the stored procedure is loaded, the 'now' is
> initialised and afterwards whenever i use this procedure, the same value
> is being inserted.

Use now() or CURRENT_TIMESTAMP instead of 'now'.  The latter gets
reduced "on sight" to a timestamp constant.

            regards, tom lane

hi all,

thanx a lot tom, stuart.
I think using CURRENT_TIMESTAMP will be better, as it will not have the
overhead of a function call.

I have one more problem. I am using postgreSQL 7.1.3 from a C application.
I am using asyncronous query processing. If the postgreSQL server is shut
or crashed while the application is running, is there any way i can know
that something went bad at the backend.
help me.
--
regards,
hari

On Thu, 28 Nov 2002, Tom Lane wrote:

> HK <harikrishnan@midascomm.com> writes:
> > I am able to execute stored procedures. But sometimes i get this message.
> > ERROR:  fmgr_info: cache lookup for language 1952725 failed
>
> Sounds like you dropped the plpgsql language while there were still
> functions that used it.  Not a good thing to do.
>
> (7.3 will prevent you from making this class of mistake, btw)
>
> > Inside a stored procedure i am using 'now' for a datetime value.
> > Wat happens is when the stored procedure is loaded, the 'now' is
> > initialised and afterwards whenever i use this procedure, the same value
> > is being inserted.
>
> Use now() or CURRENT_TIMESTAMP instead of 'now'.  The latter gets
> reduced "on sight" to a timestamp constant.
>
>             regards, tom lane
>