1-- $NetBSD: fix.sql,v 1.2 2011/10/12 16:24:39 yamt 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 connectd this db 28 29BEGIN; 30SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; 31SET search_path TO pgfs; 32WITH 33pgfs_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())), 36files_to_remove AS (DELETE FROM file WHERE nlink IN (SELECT * FROM pgfs_clients) 37 RETURNING fileid), 38removed_files AS (DELETE FROM datafork WHERE fileid IN (SELECT * FROM 39 files_to_remove) RETURNING fileid, loid) 40SELECT fileid AS "orphaned files" FROM removed_files WHERE lo_unlink(loid) = 1; 41COMMIT; 42