[src/trunk]: src/share/examples/puffs/pgfs tweak some sql statements to impro...

[yamt <yamt%NetBSD.org@localhost>]

details:   https://anonhg.NetBSD.org/src/rev/650f1c5f3ca5
branches:  trunk
changeset: 770324:650f1c5f3ca5
user:      yamt <yamt%NetBSD.org@localhost>
date:      Wed Oct 12 16:24:39 2011 +0000

description:
tweak some sql statements to improve chances to use the index.

diffstat:

 share/examples/puffs/pgfs/fix.sql     |   9 ++++-----
 share/examples/puffs/pgfs/pgfs_subs.c |  22 ++++++++++++++--------
 2 files changed, 18 insertions(+), 13 deletions(-)

diffs (70 lines):

diff -r b111a5db9871 -r 650f1c5f3ca5 share/examples/puffs/pgfs/fix.sql
--- a/share/examples/puffs/pgfs/fix.sql Wed Oct 12 15:33:38 2011 +0000
+++ b/share/examples/puffs/pgfs/fix.sql Wed Oct 12 16:24:39 2011 +0000
@@ -1,4 +1,4 @@
--- $NetBSD: fix.sql,v 1.1 2011/10/12 01:05:00 yamt Exp $
+-- $NetBSD: fix.sql,v 1.2 2011/10/12 16:24:39 yamt Exp $
 
 -- Copyright (c)2011 YAMAMOTO Takashi,
 -- All rights reserved.
@@ -35,8 +35,7 @@
        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 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;
+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;
diff -r b111a5db9871 -r 650f1c5f3ca5 share/examples/puffs/pgfs/pgfs_subs.c
--- a/share/examples/puffs/pgfs/pgfs_subs.c     Wed Oct 12 15:33:38 2011 +0000
+++ b/share/examples/puffs/pgfs/pgfs_subs.c     Wed Oct 12 16:24:39 2011 +0000
@@ -1,4 +1,4 @@
-/*     $NetBSD: pgfs_subs.c,v 1.1 2011/10/12 01:05:00 yamt Exp $       */
+/*     $NetBSD: pgfs_subs.c,v 1.2 2011/10/12 16:24:39 yamt Exp $       */
 
 /*-
  * Copyright (c)2010,2011 YAMAMOTO Takashi,
@@ -46,7 +46,7 @@
 
 #include <sys/cdefs.h>
 #ifndef lint
-__RCSID("$NetBSD: pgfs_subs.c,v 1.1 2011/10/12 01:05:00 yamt Exp $");
+__RCSID("$NetBSD: pgfs_subs.c,v 1.2 2011/10/12 16:24:39 yamt Exp $");
 #endif /* not lint */
 
 #include <assert.h>
@@ -795,18 +795,24 @@
 
        if (va->va_type == VREG || va->va_type == VLNK) {
                static struct cmd *c_datafork;
+               int32_t ret;
                int error;
 
-               /*
-                * use CASE instead of AND to preserve the evaluation ordering.
-                */
                CREATECMD(c_datafork,
-                       "DELETE FROM datafork WHERE CASE WHEN fileid = $1 "
-                       "THEN lo_unlink(loid) = 1 ELSE false END", INT8OID);
-               error = simplecmd(xc, c_datafork, fileid);
+                       "WITH loids AS (DELETE FROM datafork WHERE fileid = $1 "
+                       "RETURNING loid) SELECT lo_unlink(loid) FROM loids",
+                       INT8OID);
+               error = sendcmd(xc, c_datafork, fileid);
                if (error != 0) {
                        return error;
                }
+               error = simplefetch(xc, INT4OID, &ret);
+               if (error != 0) {
+                       return error;
+               }
+               if (ret != 1) {
+                       return EIO; /* lo_unlink failed */
+               }
        }
        CREATECMD(c, "DELETE FROM file WHERE fileid = $1", INT8OID);
        return simplecmd(xc, c, fileid);