1*fbce5794Syamt-- $NetBSD: fix.sql,v 1.2 2011/10/12 16:24:39 yamt Exp $ 249413e37Syamt 349413e37Syamt-- Copyright (c)2011 YAMAMOTO Takashi, 449413e37Syamt-- All rights reserved. 549413e37Syamt-- 649413e37Syamt-- Redistribution and use in source and binary forms, with or without 749413e37Syamt-- modification, are permitted provided that the following conditions 849413e37Syamt-- are met: 949413e37Syamt-- 1. Redistributions of source code must retain the above copyright 1049413e37Syamt-- notice, this list of conditions and the following disclaimer. 1149413e37Syamt-- 2. Redistributions in binary form must reproduce the above copyright 1249413e37Syamt-- notice, this list of conditions and the following disclaimer in the 1349413e37Syamt-- documentation and/or other materials provided with the distribution. 1449413e37Syamt-- 1549413e37Syamt-- THIS SOFTWARE IS PROVIDED BY THE AUTHOR AND CONTRIBUTORS ``AS IS'' AND 1649413e37Syamt-- ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE 1749413e37Syamt-- IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE 1849413e37Syamt-- ARE DISCLAIMED. IN NO EVENT SHALL THE AUTHOR OR CONTRIBUTORS BE LIABLE 1949413e37Syamt-- FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL 2049413e37Syamt-- DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS 2149413e37Syamt-- OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) 2249413e37Syamt-- HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT 2349413e37Syamt-- LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY 2449413e37Syamt-- OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF 2549413e37Syamt-- SUCH DAMAGE. 2649413e37Syamt 2749413e37Syamt-- remove orphaned files unless there's mount_pgfs connectd this db 2849413e37Syamt 2949413e37SyamtBEGIN; 3049413e37SyamtSET TRANSACTION ISOLATION LEVEL REPEATABLE READ; 3149413e37SyamtSET search_path TO pgfs; 3249413e37SyamtWITH 3349413e37Syamtpgfs_clients AS (SELECT -count(*) FROM pg_stat_activity WHERE 3449413e37Syamt application_name = 'pgfs' AND datid IN (SELECT oid FROM pg_database 3549413e37Syamt WHERE datname = current_database())), 3649413e37Syamtfiles_to_remove AS (DELETE FROM file WHERE nlink IN (SELECT * FROM pgfs_clients) 3749413e37Syamt RETURNING fileid), 38*fbce5794Syamtremoved_files AS (DELETE FROM datafork WHERE fileid IN (SELECT * FROM 39*fbce5794Syamt files_to_remove) RETURNING fileid, loid) 40*fbce5794SyamtSELECT fileid AS "orphaned files" FROM removed_files WHERE lo_unlink(loid) = 1; 4149413e37SyamtCOMMIT; 42