05-08-08
--------
- Precision in PostgreSQL pg_dump 
- Notes from discussion on SUMS garbage collection
- New delete_series problem
- drms_log stage option

- Curious problems with SUMS


Precision in PostgreSQL pg_dump 
-------------------------------

It turned out that pg_dump -Fc does not do any good in preserving
precision for floats. This is true for both 8.3 and 8.1. We have to
change the source.

Only two changes are needed:

src/backend/utils/misc/guc.c - change

        &extra_float_digits,
        0, -15, 2, NULL, NULL
to
        &extra_float_digits,
        0, -15, 3, NULL, NULL

src/bin/pg_dump/pg_dump.c - change 

        do_sql_command(g_conn, "SET extra_float_digits TO 2");
to
        do_sql_command(g_conn, "SET extra_float_digits TO 3");

My tests worked on l5-m5 for 8.1 and 8.3 and in between two versions.
must use server with modified code. Otherwise pg_dump does not have
enough precision. Need to stop db server on hmidb. Possible scheduled
downtime? Friday night?

Speed test: takes about 3 minutes to dump a 1G table, total db size
17G.

Questions/Issues:
- name hmidb2
- hmidb2 disk layout
  db on /d, wal on /c? or both on /d?
- sums and drms in separate db servers
  where to sums?

Notes from discussion on SUMS garbage collection
------------------------------------------------

To remove records whose unarchived SUs have been removed from
disks. Jim has special flag in sum_main to mark a SU if it's been
deleted through delete_series(SUM_drop_series() call). This removes
the ambiguity between records with the same recnums before and after
delete_series and create_series.

- add comments on Record.txt (done)
- add string "permanent" after series name in Record.txt for series that
should not go away when their unarchived SUs expire.

- add sumsadmin in drms db how to grant delete to all tables (done)
create user sumsadmin ;
alter user sumsadmin with password '....';
grant jsoc to sumsadmin;
grant delete on su_karen.client to sumsadmin
(did so for every existing series, modified insert_series for future series)
sums db is separate from drms db)

New delete_series problem
-------------------------

I discovered a problem when completing delete_serie using the new
SUM_drop_series() function that Jim provided. The order of execution
matters, i.e., must drop table first before asking sums to drop su
entries.  problem with creating a temporary table for sums, it's not
visible until commit. can't commit in the middle, since delete_series
can be called from a client module through a socket connection,
potentially sharing a db connection with other clients, e.g., used in
create_series with -f flag

possible solution:
- asynchronous solution, notify sums, but don't wait for sums to
  finish marking dead SUs
  (can't sums_main direcly, not enough privilege.)

drms_log stage option
---------------------

added to drms_log: stage -p, staging if off-line

Background:
want to take advange of a module program structure, don't want to
open SUMS explicitly. drms sessions do not conform to regular series
standard.

use drms_su_getsudir()
- create a su data structure. 
  . no su->seriesinfo available.
  . must set env->retention for tdays as required by sum_get call
- compilation problem:
  drms_su_getsudir() is only available for direct connection.
  our modules compile to one single object file for both direct and
  socket connection
  . to add compile flag DRMS_CLIENT to differentiate between socket and
 
Curious problems with SUMS
---------------------------
1. tape failure in staging 
sunum = 536520 from the
following command:

> show_info ds=hmi_ground.lev0[:#10159] -p
Error on position tape to file #757 in drive #3

2. can't stage any of the SUs from the following query
 select ds_index, owning_series, online_loc from public.sum_main where
 owning_series = 'drms_logs' and online_status = 'N' order by ds_index
 desc limit 10000;

All of them returned empty string from SUM_get and returned
instantly. It turned out that those entries are not arhived and have
since timed out.

Lesson learned: It's not safe to assume if an entry exists in sum_main
then there is a valid su behind it.