Обсуждение: Help: Input Error Notice

I have been investigating this error message for several days now but am
unable to find anything useful--perhaps someone will be able to assist
me?

Thanks in advance for your assistance!

billmccormick@catholichealth.net


The details:

In version 7.2, I have created a table:

                       Table "bulkdata"
       Column       |           Type           |  Modifiers
--------------------+--------------------------+--------------
 processedby        | integer                  | default 0
 recordstatus       | integer                  | default 0
 datetime           | timestamp with time zone |
 sourceaddress      | inet                     |
 sourceport         | integer                  |
 destinationaddress | inet                     |
 destinationport    | integer                  |
 packetsize         | integer                  |
 direction          | integer                  | default 0
 category           | integer                  | default 2000
 associatedwith     | integer                  | default 5000


I am running the output of tcpdump through an awk script to generate
insert statements for psql like: insert into bulkData values(0, 0,
'05/13/2005 11:29:20', '10.250.7.54', 1063, '10.255.64.90', 139, 1500,
1, 1000, 5035);

(/usr/sbin/tcpdump -i eth1 -nnttttvv udp or tcp 2>/dev/null | awk -f
/root/awkScript | /usr/bin/psql -f- traffic>/dev/null)

Periodically, I receive messages like: psql:<stdin>:346920: ERROR:
dtoi: integer out of range

"McCormick, Bill" <BillMcCormick@Catholichealth.net> writes:
> Periodically, I receive messages like: psql:<stdin>:346920: ERROR:
> dtoi: integer out of range

You're trying to store a number that's too big to fit in an integer
field.  Perhaps something is wrong with the awk script that generates
the data?

            regards, tom lane

I had written a function that would recreate a static table. The function
called the "DROP TABLE" command and then proceeded to create the new table.
This used to work fine until I upgraded the database server to 8.x. Now when
I try to run the function I get the error message...

ERROR:  DROP TABLE is not allowed in a non-volatile function

What can I do now? Is there a way to redefine my function so that it is a
"non-volatile" function? Tried to find more about this online with little
luck. Please any suggestions would be welcome.

Alan




----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "McCormick, Bill" <BillMcCormick@Catholichealth.net>
Cc: <pgsql-novice@postgresql.org>
Sent: Tuesday, May 17, 2005 10:39 AM
Subject: Re: [NOVICE] Help: Input Error Notice


> "McCormick, Bill" <BillMcCormick@Catholichealth.net> writes:
>> Periodically, I receive messages like: psql:<stdin>:346920: ERROR:
>> dtoi: integer out of range
>
> You're trying to store a number that's too big to fit in an integer
> field.  Perhaps something is wrong with the awk script that generates
> the data?
>
> 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
>

On Tue, 17 May 2005 11:21:14 -0700, Alan T. Miller wrote
> I had written a function that would recreate a static table. The
> function called the "DROP TABLE" command and then proceeded to
> create the new table. This used to work fine until I upgraded the
> database server to 8.x. Now when I try to run the function I get the
> error message...
>
> ERROR:  DROP TABLE is not allowed in a non-volatile function
>
> What can I do now? Is there a way to redefine my function so that it
> is a "non-volatile" function? Tried to find more about this online
> with little luck. Please any suggestions would be welcome.
>
> Alan

Alan,

At the end of the function definition you may find a line that reads something
like:  LANGUAGE 'plpgsql' STABLE; simply change it to: LANGUAGE 'plpgsql'
VOLATILE;

Kind regards,
Keith