Re: Converting timestamps and IP addresses

Le Mercredi 11 Février 2004 12:12, Erwin Van de Velde a écrit :
> If anyone has built such functions already, I'd gladly accept, and you can
> win a line in my thank word ;-)

Dear Erwin,

I built a small centralised database built for Ulogd and ran into the same
questions. You can either use implicit or explicit CASTs:

SELECT '192.168.0.3'::inet AS myexplicitcast
will convert a string into an inet

Sometimes, when you do not have contol over the logging deamon sending queries
(which is the case for example of Ulogd), you may be obliged to use implicit
CASTs (a solution described in PostgreSQL bits):

CREATE OR REPLACE FUNCTION ulog_timecast(int4)
  RETURNS timestamp AS
'select "timestamp"($1::abstime);'
  LANGUAGE 'sql' VOLATILE;

CREATE CAST (int4 AS timestamp)
  WITH FUNCTION ulog_timecast(int4)
  AS IMPLICIT;

In the end, you may need to add fields to your table and compute the logs when
they are received, using triggers and procedures (better STABLE ones). But
this can slow down logging. You may also prefer to run cron jobs (my volume
is too small for such optimisations). Also, do not forget using partial
indexes.

Using PostgreSQL on a double-athlon server, I can log up to 1000 messages
every second, but I did not try to stress the server too long (it should not
be a problem with partial indexing).

If you are interested in my code, just drop me an email and I will send you
the dump (just a few functions and triggers).

Next week, I plan to use PLbash to be able to send IPTABLES scripts to my
firewall interactively. This could make PostgreSQL one of the only database
able to counter attack on the fly during data acquisition.

Also, in order to write fast server-side applications, do not hesitate to try
pgAdmin III from http://www.pgadmin.org. This will give you direct access to
the list of CASTs. pgAdmin III has a large number of very handy features to
write server-side applications.

Cheers,
Jean-Michel