Introduction

This dCache work consists of:

pnfs_dir('dir'), a stored procedure returning the Chimera namespace starting from an arbitrary 'dir' ( in the dir name please remove the initial / e.g. 'pnfs' and not '/pnfs')
v_pnfs, the materialized view of pnfs_dir('pnfs') ; you can use it to run selects like select * from v_pnfs where path like '/pnfs/psi.ch/cms/trivcat/user/bstomumu%';
pnfs_dirs('/dir'), a Python stored procedure returning all the Chimera dirs together with their sizes, starting from an arbitrary '/dir'
v_pnfs_top_dirs, the materialized view of pnfs_dirs('/pnfs'), already ordered by size; LIVE DEMO
dc_find, an optional Python 2.6 tool to easily search in v_pnfs by the Linux command line ; it's a user tool, a dCache admin will use SQL instead

Installing the SQL code in your Chimera DB is very simple ; in my case I assign the SQL code to the DB user 'nagios' ; you can simply erase that instruction or safely ignore the related Postgresql errors :

#!bash
psql -U postgres -d chimera -e -f ./pnfs_dir.sql
psql -U postgres -d chimera -e -f ./v_pnfs.sql
yum install python-ordereddict
yum install postgresql93-plpython # or yum install postgresql94-plpython
psql -U postgres -d chimera -e -f ./pnfs_dirs.sql 
psql -U postgres -d chimera -e -f ./v_pnfs_top_dirs.sql

Requirements:

dCache [ 2.2 - 2.6 - 2.10 - 2.13 ] Chimera
PostgreSQL >= 8.4, because of the WITH Queries
PostgreSQL >= 9.3 Materialized Views ; the usage of the Materialized Views is highly recommended but not strictly required ; they will significantly speed up the queries execution; if you're still running a PG version < 9.3 then simply erase the 'MATERIALIZED' infix from the SQL code, but it would be simpler and better to upgrade to PG 9.3.
To run dc_find:
- Python 2.6 module psycopg2
- Python 2.6 module PrettyTable

Nice article to understand the with recursive queries executed on a tree modelled in SQL, like the Chimera tree.

** v_pnfs ****

#!sql
psql (9.4.1)
Type "help" for help.

chimera=# \dS+ v_pnfs
                           Materialized view "public.v_pnfs"
 Column  |           Type           | Modifiers | Storage  | Stats target | Description 
---------+--------------------------+-----------+----------+--------------+-------------
 depth   | integer                  |           | plain    |              | 
 itype   | integer                  |           | plain    |              | 
 iio     | integer                  |           | plain    |              | 
 ipnfsid | character varying        |           | extended |              | 
 path    | text                     |           | extended |              | 
 iname   | character varying        |           | extended |              | 
 isize   | bigint                   |           | plain    |              | 
 iuid    | integer                  |           | plain    |              | 
 igid    | integer                  |           | plain    |              | 
 ictime  | timestamp with time zone |           | plain    |              | 
 iatime  | timestamp with time zone |           | plain    |              | 
 imtime  | timestamp with time zone |           | plain    |              | 
 icrtime | timestamp with time zone |           | plain    |              | 
 imode   | integer                  |           | plain    |              | 
 adler32 | character varying(128)   |           | extended |              | 
 pools   | text                     |           | extended |              | 
View definition:
 WITH partial_v_pnfs AS (
         SELECT pnfs_dir.depth,
            pnfs_dir.itype,
            pnfs_dir.iio,
            pnfs_dir.ipnfsid,
            pnfs_dir.path,
            pnfs_dir.iname,
            pnfs_dir.isize,
            pnfs_dir.iuid,
            pnfs_dir.igid,
            pnfs_dir.ictime,
            pnfs_dir.iatime,
            pnfs_dir.imtime,
            pnfs_dir.icrtime,
            t_level_2.imode
           FROM pnfs_dir('pnfs'::character varying) pnfs_dir(depth, itype, iio, ipnfsid, path, iname, isize, iuid, igid, ictime, iatime, imtime, icrtime)
             LEFT JOIN t_level_2 ON pnfs_dir.ipnfsid::text = t_level_2.ipnfsid::text
        ), t_locationinfo_aggregated AS (
         SELECT t_locationinfo.ipnfsid,
            string_agg(t_locationinfo.ilocation::text, ' '::text) AS ilocation_aggregated
           FROM t_locationinfo
          GROUP BY t_locationinfo.ipnfsid
        ), partial_v_pnfs2 AS (
         SELECT partial_v_pnfs.depth,
            partial_v_pnfs.itype,
            partial_v_pnfs.iio,
            partial_v_pnfs.ipnfsid,
            partial_v_pnfs.path,
            partial_v_pnfs.iname,
            partial_v_pnfs.isize,
            partial_v_pnfs.iuid,
            partial_v_pnfs.igid,
            partial_v_pnfs.ictime,
            partial_v_pnfs.iatime,
            partial_v_pnfs.imtime,
            partial_v_pnfs.icrtime,
            partial_v_pnfs.imode,
            t_locationinfo_aggregated.ilocation_aggregated
           FROM partial_v_pnfs
             LEFT JOIN t_locationinfo_aggregated ON partial_v_pnfs.ipnfsid::text = t_locationinfo_aggregated.ipnfsid::text
          ORDER BY partial_v_pnfs.path DESC
        )
 SELECT partial_v_pnfs2.depth,
    partial_v_pnfs2.itype,
    partial_v_pnfs2.iio,
    partial_v_pnfs2.ipnfsid,
    partial_v_pnfs2.path,
    partial_v_pnfs2.iname,
    partial_v_pnfs2.isize,
    partial_v_pnfs2.iuid,
    partial_v_pnfs2.igid,
    partial_v_pnfs2.ictime,
    partial_v_pnfs2.iatime,
    partial_v_pnfs2.imtime,
    partial_v_pnfs2.icrtime,
    partial_v_pnfs2.imode,
    t_inodes_checksum.isum AS adler32,
    partial_v_pnfs2.ilocation_aggregated AS pools
   FROM partial_v_pnfs2
     LEFT JOIN t_inodes_checksum ON t_inodes_checksum.ipnfsid::text = partial_v_pnfs2.ipnfsid::text;

** v_pnfs_top_dirs ****

#!sql
chimera=> \dS+ v_pnfs_top_dirs
                        Materialized view "public.v_pnfs_top_dirs"
 Column |            Type             | Modifiers | Storage  | Stats target | Description 
--------+-----------------------------+-----------+----------+--------------+-------------
 totmb  | integer                     |           | plain    |              | 
 depth  | integer                     |           | plain    |              | 
 iuid   | integer                     |           | plain    |              | 
 igid   | integer                     |           | plain    |              | 
 ictime | timestamp without time zone |           | plain    |              | 
 iatime | timestamp without time zone |           | plain    |              | 
 path   | character varying           |           | extended |              | 
View definition:
 SELECT pnfs_dirs.totmb,
    pnfs_dirs.depth,
    pnfs_dirs.iuid,
    pnfs_dirs.igid,
    pnfs_dirs.ictime,
    pnfs_dirs.iatime,
    pnfs_dirs.path
   FROM pnfs_dirs('/pnfs'::character varying) pnfs_dirs(totmb, depth, ipnfsid, path, iuid, igid, ictime, iatime, imtime)
  ORDER BY pnfs_dirs.totmb DESC, pnfs_dirs.path;

Wiki

v_pnfs / Home

Introduction