xref: /src/share/examples/puffs/pgfs/check.sql

-- $NetBSD: check.sql,v 1.1 2011/10/12 01:05:00 yamt Exp $

-- Copyright (c)2010,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.

-- filesystem consistency checks.  ie. something like "fsck -n"

BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION READ ONLY;
SET search_path TO pgfs;
SELECT count(*) AS "unreferenced files (dirent)"
	FROM file f LEFT JOIN dirent d
	ON f.fileid = d.child_fileid
	WHERE f.fileid <> 1 AND d.child_fileid IS NULL;
SELECT count(*) AS "unreferenced files (nlink)"
	FROM file f
	WHERE f.nlink = 0;
SELECT count(*) AS "regular files without datafork"
	FROM file f LEFT JOIN datafork df
	ON f.fileid = df.fileid
	WHERE df.fileid IS NULL AND f.type IN ('regular', 'link');
SELECT count(*) AS "broken datafork reference"
	FROM file f INNER JOIN datafork df
	ON f.fileid = df.fileid
	WHERE f.type NOT IN ('regular', 'link');
SELECT count(*) AS "unreferenced dataforks"
	FROM file f RIGHT JOIN datafork df
	ON f.fileid = df.fileid
	WHERE f.fileid IS NULL;
SELECT count(*) AS "dataforks without large object"
	FROM datafork df LEFT JOIN pg_largeobject_metadata lm
	ON df.loid = lm.oid
	WHERE lm.oid IS NULL;
SELECT count(*) AS "unreferenced large objects"
	FROM datafork df RIGHT JOIN pg_largeobject_metadata lm
	ON df.loid = lm.oid
	WHERE df.loid IS NULL;
SELECT count(*) AS "dirent broken parent_fileid references"
	FROM dirent d LEFT JOIN file f
	ON d.parent_fileid = f.fileid
	WHERE f.fileid IS NULL OR f.type <> 'directory';
SELECT count(*) AS "dirent broken child_fileid references"
	FROM dirent d LEFT JOIN file f
	ON d.child_fileid = f.fileid
	WHERE f.fileid IS NULL;
SELECT count(*) AS "dirent loops" FROM file f WHERE EXISTS (
	WITH RECURSIVE r AS
	(
			SELECT d.* FROM dirent d
				WHERE d.child_fileid = f.fileid
		UNION ALL
			SELECT d.* FROM dirent d INNER JOIN r
				ON d.child_fileid = r.parent_fileid
	)
	SELECT * FROM r WHERE r.parent_fileid = f.fileid);
SELECT count(*) AS "broken nlink"
	FROM
	(
	SELECT coalesce(fp.fileid, fc.fileid) AS fileid,
		coalesce(fp.nlink, 0) + coalesce(fc.nlink, 0) +
		CASE
			WHEN coalesce(fp.fileid, fc.fileid) = 1 THEN 1
			ELSE 0
		END
		AS nlink
		FROM
		(
		SELECT child_fileid AS fileid, count(*) AS nlink
			FROM dirent
			GROUP BY child_fileid
		) fp
		FULL JOIN
		(
		SELECT count(*) AS nlink, d.parent_fileid AS fileid
			FROM dirent d
			JOIN file f
			ON d.child_fileid = f.fileid
			WHERE f.type = 'directory'
			GROUP BY parent_fileid
		) fc
		ON fp.fileid = fc.fileid
	) d
	FULL JOIN file f
	ON d.fileid = f.fileid
	WHERE (d.nlink IS NULL AND (f.fileid <> 1 AND f.nlink <> 0))
	    OR f.nlink IS NULL
	    OR d.nlink <> f.nlink;
COMMIT;