Home | History | Annotate | Line # | Download | only in pgfs 
      1 -- $NetBSD: fix.sql,v 1.3 2025/05/19 06:16:24 andvar Exp $
      2 
      3 -- Copyright (c)2011 YAMAMOTO Takashi,
      4 -- All rights reserved.
      5 --
      6 -- Redistribution and use in source and binary forms, with or without
      7 -- modification, are permitted provided that the following conditions
      8 -- are met:
      9 -- 1. Redistributions of source code must retain the above copyright
     10 --    notice, this list of conditions and the following disclaimer.
     11 -- 2. Redistributions in binary form must reproduce the above copyright
     12 --    notice, this list of conditions and the following disclaimer in the
     13 --    documentation and/or other materials provided with the distribution.
     14 --
     15 -- THIS SOFTWARE IS PROVIDED BY THE AUTHOR AND CONTRIBUTORS ``AS IS'' AND
     16 -- ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE
     17 -- IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE
     18 -- ARE DISCLAIMED.  IN NO EVENT SHALL THE AUTHOR OR CONTRIBUTORS BE LIABLE
     19 -- FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL
     20 -- DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS
     21 -- OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION)
     22 -- HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT
     23 -- LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY
     24 -- OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF
     25 -- SUCH DAMAGE.
     26 
     27 -- remove orphaned files unless there's mount_pgfs connected to this db
     28 
     29 BEGIN;
     30 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
     31 SET search_path TO pgfs;
     32 WITH
     33 pgfs_clients AS (SELECT -count(*) FROM pg_stat_activity WHERE
     34 	application_name = 'pgfs' AND datid IN (SELECT oid FROM pg_database
     35 	WHERE datname = current_database())),
     36 files_to_remove AS (DELETE FROM file WHERE nlink IN (SELECT * FROM pgfs_clients)
     37 	RETURNING fileid),
     38 removed_files AS (DELETE FROM datafork WHERE fileid IN (SELECT * FROM
     39 	files_to_remove) RETURNING fileid, loid)
     40 SELECT fileid AS "orphaned files" FROM removed_files WHERE lo_unlink(loid) = 1;
     41 COMMIT;
     42