`DESOPER` was restructured to remove `attnum`, `reqnum`, and `unitname` in favor of `pfw_attempt_id`. I was building catalog `filename` from these quantities (since it is one of the few indexed quantities in the `se_object` table), but I've been told that I shouldn't do this.

What is the preferred way to select a filename leaving a CCDNUM wildcard? My previous formulation was:

  1. Erin Sheldon

    At the same time, it would also be good to have an official explanation of the path parameters.  In other words all elements of the path.

    There is an email to the release list from Robert explaining some of it, and how the path cannot be reconstructed piece by piece in the database, but rather one must use the "path" column.

    I think that is important information that is not documented anywhere I know of, and is I think unexpected from a user standpoint.


  2. Robert Gruendl

    The official explanation is that the current paths are what they needed to be at the time they were made and that future paths will be what they are based on what they need to be at the time they are made (and the same is true for filenames).  That said there is not a plan to makes substantive changes (at the moment).  

    However... an example from Y3A1_FINALCUT.  

    The file: "D00376744_Y_c37_r2262p01_immasked.fits"

    select * from file_archive_info where filename='D00376744_Y_c37_r2262p01_immasked.fits';


              FILENAME: D00376744_Y_c37_r2262p01_immasked.fits

    ARCHIVE_NAME: desar2home

                       PATH:  OPS/finalcut/Y2A1/Y2-2262/20141111/D00376744/p01/red/immask

      COMPRESSION: .fz

            DESFILE_ID: 155591895

    For this filetype (red_immask):

                    FILENAME:  (unitname)_(band)_c(ccdnum)_r(reqnum)p(attnum)_immasked.fits

         ARCHIVE_NAME:   desar2home is the storage condo

            PATH: (project)/(pipeline)/(campaign)/(subelement)/(nite)/(unit name)/p(attnum)/red/immask

          COMPRESSION (this file is compressed with FPACK, the actual filename in the archive has this suffix appended)

            DESFILE_ID: (ID for cross_reference in the table DESFILE)

    The biggest problem you are running into is to grok the (subelement).  The subelement was written the way it was because the operator was juggling chunks of 2,000 exposures when submitting each portion of this campaign.  Each carries its own request.  The year and request number were deemed sufficient by the operator to keep track of what was going on.  Operationally we were often encountering a high failure rate (problems on FermiGrid, iForge, BlueWaters, DB, StorageCondo etc...).  Adding the subelement piece was operationally expedient and we went with it.  If a different/better solution were to arise at some other point we would go with it.

    I suppose you can sum this all up with "Pray I don't alter the deal further!"  I don't mean it to be that way but there is an element here that "we are doing what we are doing because we need to do it that way" (and we have made the system flexible enough to handle it so you don't have to).

CommentAdd your comment...

2 answers


    select c.filename from catalog c, proctag t where t.tag='Y3A1_FINALCUT' and t.pfw_attempt_id=c.pfw_attempt_id and c.filetype='cat_finalcut'

    If you need to constrain on expnum then you can do so for most catalog types (an example where expnum would not be present are catalogs associated with a COADD).

    1. Alex Drlica-Wagner

      Thanks, this is very close to what I need to get the catalog filename. A few follow-up questions:

      • Why is the selection on tag necessary? Doesn't the pfw_attempt_id uniquely specify the tag? I guess there can be multiple tags applied to each processing attempt (i.e. Y2A1_FINALCUT and Y3A1_FINALCUT), but does this duplicate the returned filenames?
      • I'm guessing that doing some mangling to the filename is unavoidable since I want to grab objects from SE_OBJECT on an exposure basis and not a CCD basis.

      For the record, the way to access the unitname, reqnum, and attnum from the pfw_attempt_id is:

      select unitname, reqnum, attnum from prod.pfw_attempt_id where id = <PFW_ATTEMPT_ID>;
    2. Robert Gruendl

      Selection on the tag was not necessary I was simply constraining the query to some specific set... you can equally well constrain to an individual PFW_ATTEMPT_ID... 


      An attempt can be placed in multiple TAGs.  There is no difference between the Y2A1_FINALCUT and Y3A1_FINALCUT tags... they are simply there so that users can get to the data either way.


      You absolutely do not need to mangle the filename (IMAGE, CATALOG, MISCFILE contain many useful bits of metadata... expnum, ccdnum, and band are high on that list of useful... Admittedly, they are only as good as what the pipelines produced at runtime (and what we have been striving to backfill).  To get objects with ccdnum in the same query is simple.  This will work for all Y3A1 and later... (I think it now works for Y2N and Y3N but have not checked explicitly)


      select c.filename,c.expnum,c.ccdnum,o.{whatever you want}

      from catalog c, se_object o

      where c.pfw_attempt_id={the ID you care about}

         and c.filename=o.filename

         and o.{some_other_object_constraint}


    CommentAdd your comment...

    Hi Alex,

    Not sure whether this is what you are looking for, but for a given pfw_attempt_id you can look up the image table with

    DESDB ~> select FILENAME,CCDNUM from image where PFW_ATTEMPT_ID=23083 and FILETYPE = 'red_immask';

    That will give you the filename for all ccd with that filetype,

      CommentAdd your comment...