Robert Fitzpatrick (l...@webtent.net)

Yes, this looks like it might work, thanks! But not sure which condition
to look for or if I'm doing this correctly. I tried syntax_error
condition, but I'm still receiving the same cast error trying this in a
trigger function...

begin
begin
  test := NEW.textfield::interval;
  EXCEPTION
   WHEN syntax_error THEN
    RAISE NOTICE 'Invalid Duration';
    return null;
end;
<snip other code>
return new;
end;

> Although: why do you want to generate your own error? It seems like it
> would probably be about the same as the error produced by the casting
> failure.

My application will display whatever I can return via raise exception,
hence, that's why I'm trying this. Looking for a way to translate to the
user...

update events set event_duration = '3ho' where event_id = 2;
ERROR: invalid input syntax for type interval: "3ho"

Been searching for a way to do this, but haven't found what I was hoping
to find. Is there any way in pl/pgsql to test a text value to see if it
would be a valid interval without having to try and store in a field? In
a trigger, I'd like to test a NEW text type field. Right now, I have
just the following to generate an error...

test := NEW.textfield::interval;

I'd like to test the field and RAISE EXCEPTION if not valid interval.

Well, all of it I guess from looking below? Again, just learning here...

maia=# select count(*) from pg_buffercache;
count
-------
64000
(1 row)
maia=# select count(*) from pg_buffercache where relfilenode is null;
count
-------
     0
(1 row)
maia=# SELECT c.relname, count(*) AS buffers
               FROM pg_class c INNER JOIN pg_buffercache b
               ON b.relfilenode = c.relfilenode INNER JOIN pg_database d
               ON (b.reldatabase = d.oid AND d.datname = current_database())
               GROUP BY c.relname
               ORDER BY 2 DESC LIMIT 10;
         relname         | buffers 
-------------------------+---------
 bayes_token             |   16684
 bayes_token_idx1        |   10264
 maia_sa_rules           |    8501
 pg_toast_70736          |    5898
 maia_mail               |    4361
 maia_sa_rules_triggered |    3913
 maia_mail_recipients    |    3603
 bayes_token_pkey        |    3199
 maia_stats              |    2545
 token_idx               |    2442
(10 rows)

Thanks again for any insight?

I have a couple of servers running Postfix with amavisd-maia+SA+clamav
on FreeBSD 6.2 which use a central db server running PgSQL 8.2.4. My
issue is the bayes database causing SA TIMED OUT in the logs and want to
make sure I am getting everything into memory. The disk activity is high
on the db server, this is the average systat status...

                    /0   /1   /2   /3   /4   /5   /6   /7   /8   /9   /10
     Load Average   |

          /0   /10  /20  /30  /40  /50  /60  /70  /80  /90  /100
cpu  user| 
     nice|
   system|
interrupt|
     idle|XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX 

          /0   /10  /20  /30  /40  /50  /60  /70  /80  /90  /100
aacd0 MB/s
      tps|XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

I know I'm running RAID-5 and that is set to change, right now I'm just
focusing on getting my tables into memory. I have 4GB of memory in the
db server now, but the server indicates about only 3GB in dmesg, not
sure why this is, FreeBSD warns a small amount of over 4GB will not be
used when booting...

real memory  = 3220635648 (3071 MB)
avail memory = 3150565376 (3004 MB)

Here is my conf...

mx1# cat postgresql.conf
listen_addresses = '*'          # what IP address(es) to listen on; 
max_connections = 250
shared_buffers = 500MB                  # min 128kB or max_connections*16kB
work_mem = 64MB                         # min 64kB
maintenance_work_mem = 256MB            # min 1MB
max_fsm_pages = 256000  # min max_fsm_relations*16, 6 bytes each
checkpoint_segments = 100               # (value * 2 + 1) * 16MB
effective_cache_size = 1000MB
log_destination = 'syslog'
silent_mode = on
stats_start_collector = on              # needed for block or row stats
stats_row_level = on
autovacuum = off                        # enable autovacuum subprocess?
datestyle = 'iso, mdy'
lc_messages = 'C'                       # locale for system error message 
lc_monetary = 'C'                       # locale for monetary formatting
lc_numeric = 'C'                        # locale for number formatting
lc_time = 'C'                           # locale for time formatting

I vacuum every night and expire my bayes db for SA. While I have plenty
of memory installed, it just does not seem to be using it considering my
disk status above? How can I tell if PgSQL is using memory or not and
how much? Excuse my ignorance on the matter, just learning how to
properly tune PostgreSQL.

My top 20 tables sizes are as follows...

maia=> SELECT relname, reltuples, relpages FROM pg_class ORDER BY relpages DESC limit 20;
                relname                |  reltuples  | relpages 
---------------------------------------+-------------+----------
 pg_toast_70736                        |      846647 |   257452
 maia_mail                             |      375574 |    63639
 maia_sa_rules_triggered               | 4.52118e+06 |    38526
 bayes_token                           |      447008 |    20033
 maia_sa_rules_triggered_pkey          | 4.52118e+06 |    17821
 bayes_token_idx1                      |      447008 |    11437
 maia_mail_recipients                  |      377340 |     9867
 maia_sa_rules                         |        1578 |     8501
 token_idx                             |      377340 |     8053
 envelope_to_received_date_idx         |      375574 |     7202
 pg_toast_70736_index                  |      846647 |     4719
 maia_mail_idx_received_date           |      375574 |     3703
 maia_mail_recipients_pkey             |      377340 |     3471
 bayes_token_pkey                      |      447008 |     3200
 awl_pkey                              |      189259 |     2965
 maia_mail_recipients_idx_recipient_id |      377340 |     2696
 awl                                   |      189259 |     2599
 maia_stats                            |         185 |     2545
 bayes_seen_pkey                       |      174501 |     2433
 bayes_seen                            |      174501 |     2238
(20 rows)

I have a PHP 5 app using pgsql 8.2 and a HTML table of clients now grown
to almost 10,000 loading 25 per page. There is a filter feature atop all
seven columns in the table listing (all varchar except one date column).
Also, sorting can be done by clicking any column header. Some complain
of speed during filtering or clearing the filter. I want to create some
indexes to see if this will help as I'm sure it will since there are
none currently.

Now my question, would it be better to create one index with all columns
in the table -or- a separate index for each column field? I was assuming
the latter, but would the index with all columns be beneficial as well?

Thanks in advance!