Обсуждение: 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


>       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
>
> 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