head 1.3; access; symbols netbsd-11-0-RC5:1.3 netbsd-11-0-RC4:1.3 netbsd-11-0-RC3:1.3 netbsd-11-0-RC2:1.3 netbsd-11-0-RC1:1.3 perseant-exfatfs-base-20250801:1.3 netbsd-11:1.3.0.2 netbsd-11-base:1.3 netbsd-10-1-RELEASE:1.2 perseant-exfatfs-base-20240630:1.2 perseant-exfatfs:1.2.0.54 perseant-exfatfs-base:1.2 netbsd-8-3-RELEASE:1.2 netbsd-9-4-RELEASE:1.2 netbsd-10-0-RELEASE:1.2 netbsd-10-0-RC6:1.2 netbsd-10-0-RC5:1.2 netbsd-10-0-RC4:1.2 netbsd-10-0-RC3:1.2 netbsd-10-0-RC2:1.2 netbsd-10-0-RC1:1.2 netbsd-10:1.2.0.52 netbsd-10-base:1.2 netbsd-9-3-RELEASE:1.2 cjep_sun2x-base1:1.2 cjep_sun2x:1.2.0.50 cjep_sun2x-base:1.2 cjep_staticlib_x-base1:1.2 netbsd-9-2-RELEASE:1.2 cjep_staticlib_x:1.2.0.48 cjep_staticlib_x-base:1.2 netbsd-9-1-RELEASE:1.2 phil-wifi-20200421:1.2 phil-wifi-20200411:1.2 is-mlppp:1.2.0.46 is-mlppp-base:1.2 phil-wifi-20200406:1.2 netbsd-8-2-RELEASE:1.2 netbsd-9-0-RELEASE:1.2 netbsd-9-0-RC2:1.2 netbsd-9-0-RC1:1.2 phil-wifi-20191119:1.2 netbsd-9:1.2.0.44 netbsd-9-base:1.2 phil-wifi-20190609:1.2 netbsd-8-1-RELEASE:1.2 netbsd-8-1-RC1:1.2 pgoyette-compat-merge-20190127:1.2 pgoyette-compat-20190127:1.2 pgoyette-compat-20190118:1.2 pgoyette-compat-1226:1.2 pgoyette-compat-1126:1.2 pgoyette-compat-1020:1.2 pgoyette-compat-0930:1.2 pgoyette-compat-0906:1.2 netbsd-7-2-RELEASE:1.2 pgoyette-compat-0728:1.2 netbsd-8-0-RELEASE:1.2 phil-wifi:1.2.0.42 phil-wifi-base:1.2 pgoyette-compat-0625:1.2 netbsd-8-0-RC2:1.2 pgoyette-compat-0521:1.2 pgoyette-compat-0502:1.2 pgoyette-compat-0422:1.2 netbsd-8-0-RC1:1.2 pgoyette-compat-0415:1.2 pgoyette-compat-0407:1.2 pgoyette-compat-0330:1.2 pgoyette-compat-0322:1.2 pgoyette-compat-0315:1.2 netbsd-7-1-2-RELEASE:1.2 pgoyette-compat:1.2.0.40 pgoyette-compat-base:1.2 netbsd-7-1-1-RELEASE:1.2 matt-nb8-mediatek:1.2.0.38 matt-nb8-mediatek-base:1.2 perseant-stdc-iso10646:1.2.0.36 perseant-stdc-iso10646-base:1.2 netbsd-8:1.2.0.34 netbsd-8-base:1.2 prg-localcount2-base3:1.2 prg-localcount2-base2:1.2 prg-localcount2-base1:1.2 prg-localcount2:1.2.0.32 prg-localcount2-base:1.2 pgoyette-localcount-20170426:1.2 bouyer-socketcan-base1:1.2 pgoyette-localcount-20170320:1.2 netbsd-7-1:1.2.0.30 netbsd-7-1-RELEASE:1.2 netbsd-7-1-RC2:1.2 netbsd-7-nhusb-base-20170116:1.2 bouyer-socketcan:1.2.0.28 bouyer-socketcan-base:1.2 pgoyette-localcount-20170107:1.2 netbsd-7-1-RC1:1.2 pgoyette-localcount-20161104:1.2 netbsd-7-0-2-RELEASE:1.2 localcount-20160914:1.2 netbsd-7-nhusb:1.2.0.26 netbsd-7-nhusb-base:1.2 pgoyette-localcount-20160806:1.2 pgoyette-localcount-20160726:1.2 pgoyette-localcount:1.2.0.24 pgoyette-localcount-base:1.2 netbsd-7-0-1-RELEASE:1.2 netbsd-7-0:1.2.0.22 netbsd-7-0-RELEASE:1.2 netbsd-7-0-RC3:1.2 netbsd-7-0-RC2:1.2 netbsd-7-0-RC1:1.2 netbsd-6-0-6-RELEASE:1.2 netbsd-6-1-5-RELEASE:1.2 netbsd-7:1.2.0.20 netbsd-7-base:1.2 yamt-pagecache-base9:1.2 yamt-pagecache-tag8:1.2 netbsd-6-1-4-RELEASE:1.2 netbsd-6-0-5-RELEASE:1.2 tls-earlyentropy:1.2.0.18 tls-earlyentropy-base:1.2 riastradh-xf86-video-intel-2-7-1-pre-2-21-15:1.2 riastradh-drm2-base3:1.2 netbsd-6-1-3-RELEASE:1.2 netbsd-6-0-4-RELEASE:1.2 netbsd-6-1-2-RELEASE:1.2 netbsd-6-0-3-RELEASE:1.2 netbsd-6-1-1-RELEASE:1.2 riastradh-drm2-base2:1.2 riastradh-drm2-base1:1.2 riastradh-drm2:1.2.0.12 riastradh-drm2-base:1.2 netbsd-6-1:1.2.0.16 netbsd-6-0-2-RELEASE:1.2 netbsd-6-1-RELEASE:1.2 netbsd-6-1-RC4:1.2 netbsd-6-1-RC3:1.2 agc-symver:1.2.0.14 agc-symver-base:1.2 netbsd-6-1-RC2:1.2 netbsd-6-1-RC1:1.2 yamt-pagecache-base8:1.2 netbsd-6-0-1-RELEASE:1.2 yamt-pagecache-base7:1.2 matt-nb6-plus-nbase:1.2 yamt-pagecache-base6:1.2 netbsd-6-0:1.2.0.10 netbsd-6-0-RELEASE:1.2 netbsd-6-0-RC2:1.2 tls-maxphys:1.2.0.8 tls-maxphys-base:1.2 matt-nb6-plus:1.2.0.6 matt-nb6-plus-base:1.2 netbsd-6-0-RC1:1.2 yamt-pagecache-base5:1.2 yamt-pagecache-base4:1.2 netbsd-6:1.2.0.4 netbsd-6-base:1.2 yamt-pagecache-base3:1.2 yamt-pagecache-base2:1.2 yamt-pagecache:1.2.0.2 yamt-pagecache-base:1.2; locks; strict; comment @# @; 1.3 date 2025.05.19.06.16.24; author andvar; state Exp; branches; next 1.2; commitid qr695EjjDE1BUtVF; 1.2 date 2011.10.12.16.24.39; author yamt; state Exp; branches 1.2.54.1; next 1.1; 1.1 date 2011.10.12.01.05.00; author yamt; state Exp; branches; next ; 1.2.54.1 date 2025.08.02.05.55.09; author perseant; state Exp; branches; next ; commitid 23j6GFaDws3O875G; desc @@ 1.3 log @spelling and grammar fixes in comments. @ text @-- $NetBSD: fix.sql,v 1.2 2011/10/12 16:24:39 yamt Exp $ -- Copyright (c)2011 YAMAMOTO Takashi, -- All rights reserved. -- -- Redistribution and use in source and binary forms, with or without -- modification, are permitted provided that the following conditions -- are met: -- 1. Redistributions of source code must retain the above copyright -- notice, this list of conditions and the following disclaimer. -- 2. Redistributions in binary form must reproduce the above copyright -- notice, this list of conditions and the following disclaimer in the -- documentation and/or other materials provided with the distribution. -- -- THIS SOFTWARE IS PROVIDED BY THE AUTHOR AND CONTRIBUTORS ``AS IS'' AND -- ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE -- IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE -- ARE DISCLAIMED. IN NO EVENT SHALL THE AUTHOR OR CONTRIBUTORS BE LIABLE -- FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL -- DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS -- OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) -- HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT -- LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY -- OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF -- SUCH DAMAGE. -- remove orphaned files unless there's mount_pgfs connected to this db BEGIN; SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; SET search_path TO pgfs; WITH pgfs_clients AS (SELECT -count(*) FROM pg_stat_activity WHERE application_name = 'pgfs' AND datid IN (SELECT oid FROM pg_database WHERE datname = current_database())), files_to_remove AS (DELETE FROM file WHERE nlink IN (SELECT * FROM pgfs_clients) RETURNING fileid), removed_files AS (DELETE FROM datafork WHERE fileid IN (SELECT * FROM files_to_remove) RETURNING fileid, loid) SELECT fileid AS "orphaned files" FROM removed_files WHERE lo_unlink(loid) = 1; COMMIT; @ 1.2 log @tweak some sql statements to improve chances to use the index. @ text @d1 1 a1 1 -- $NetBSD: fix.sql,v 1.1 2011/10/12 01:05:00 yamt Exp $ d27 1 a27 1 -- remove orphaned files unless there's mount_pgfs connectd this db @ 1.2.54.1 log @Sync with HEAD @ text @d1 1 a1 1 -- $NetBSD: fix.sql,v 1.3 2025/05/19 06:16:24 andvar Exp $ d27 1 a27 1 -- remove orphaned files unless there's mount_pgfs connected to this db @ 1.1 log @puffs file system server backed by postgresql @ text @d1 1 a1 1 -- $NetBSD$ d38 3 a40 4 removed_files AS (DELETE FROM datafork WHERE CASE WHEN fileid IN (SELECT * FROM files_to_remove) THEN lo_unlink(loid) = 1 ELSE false END RETURNING fileid) SELECT fileid AS "orphaned files" from removed_files; @