Error Occurred when run function. How to solve it?
Error Occurred when run function. Please give me some advices. Thanks.
The function:
-- Function: usf_annatest(varchar, varchar)
CREATE FUNCTION usf_annatest(varchar, varchar) RETURNS varchar AS '
DECLARE
curs_userdetail refcursor;
table_name ALIAS for $1;
identity ALIAS for $2;
query_where VARCHAR;
BEGIN
IF table_name = "ot_customer" THEN
RETURN table_name;
ELSE
RETURN identity;
END IF;
END;' LANGUAGE 'plpgsql';
I run it :
SELECT usf_annatest('ot_test', 'anna');
Error :
NOTICE: Error occurred while executing PL/pgSQL function usf_annatest
NOTICE: line 8 at if
ERROR : Attribute 'ot_customer' not found.
So, when I need to user "ot_customer" and 'ot_customer'.
Thanks a lot.
Hi,
--On Montag, 25. November 2002 01:17 +0800 annachau <annachau@hongkong.com>
wrote:
Error Occurred when run function. Please give me some advices. Thanks.
The function:
-- Function: usf_annatest(varchar, varchar)
CREATE FUNCTION usf_annatest(varchar, varchar) RETURNS varchar AS '
DECLARE
curs_userdetail refcursor;
table_name ALIAS for $1;
identity ALIAS for $2;
query_where VARCHAR;BEGIN
IF table_name = "ot_customer" THEN
^^^^^^^^^^^^
(as the error tells you) You have to write ''ot_customer'' note the
double single ' instead of one double ".
Regards
Tino
Show quoted text
RETURN table_name;
ELSE
RETURN identity;
END IF;
END;' LANGUAGE 'plpgsql';I run it :
SELECT usf_annatest('ot_test', 'anna');Error :
NOTICE: Error occurred while executing PL/pgSQL function usf_annatest
NOTICE: line 8 at if
ERROR : Attribute 'ot_customer' not found.So, when I need to user "ot_customer" and 'ot_customer'.
Thanks a lot.
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
Hi all,
I am having trouble with clustering tables at the moment.
I cluster certain tables during housekeeping before I vacuum full analyse
the whole DB.
This error pops up every few days during clustering
ERROR: Cannot insert a duplicate key into unique index pg_class_oid_index
Can some one please advise.
PG version:
PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.95.4
Thanks
Andrew
"Andrew Bartley" <abartley@evolvosystems.com> writes:
This error pops up every few days during clustering
ERROR: Cannot insert a duplicate key into unique index pg_class_oid_index
Hmm, is it possible that your OID counter has wrapped around? Try
creating a new table, and then look to see if its OID is the largest one
in pg_class or not.
regards, tom lane
Thanks Tom
create table fish
(
fish char(4)
)
select oid, * from pg_class where relname = 'fish'
4289092798
select max(oid) from pg_class
4289092798
Looks like it has not wrapped. Should I have the housekeeping cluster the
tables after I vacuum?
Thanks
Andrew
----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Andrew Bartley" <abartley@evolvosystems.com>
Cc: <pgsql-general@postgresql.org>
Sent: Monday, November 25, 2002 9:07 AM
Subject: Re: [GENERAL] Cluster problem
"Andrew Bartley" <abartley@evolvosystems.com> writes:
This error pops up every few days during clustering
ERROR: Cannot insert a duplicate key into unique index
pg_class_oid_index
Show quoted text
Hmm, is it possible that your OID counter has wrapped around? Try
creating a new table, and then look to see if its OID is the largest one
in pg_class or not.regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
"Andrew Bartley" <abartley@evolvosystems.com> writes:
select oid, * from pg_class where relname = 'fish'
4289092798
select max(oid) from pg_class
4289092798
Looks like it has not wrapped.
... but you're within hailing distance of a wrap; that's very nearly 4G.
I wonder whether you are consuming OIDs fast enough that you already
wrapped, and are approaching your second (or Nth) wraparound.
How many tables do you actually have (select count(*) from pg_class)?
It could be that this is just the expected post_wrap behavior:
occasional OID conflicts due to regeneration of the same OID value.
However, unless you have a heckuva lot of pg_class entries I'd expect
the probability of a conflict to be mighty small, so I'm surprised that
you are seeing conflicts often enough to complain about it.
regards, tom lane