Consistency of DELETE statement?

SQL++
1

Scenario:

  1. Insert 100 docs to bucket
  2. Exec query: DELETE FROM default to delete all docs
  3. Exec query: SELECT COUNT(*) FROM default to check number of docs in bucket

In step 3, how to ensure that query will return 0, try use consistency mode RequestPlus but not work?

Code test in golang:

import "fmt"
import gocb "gopkg.in/couchbase/gocb.v1"

func main() {
	cluster, _ := gocb.Connect("couchbase://localhost")
	bucket, _ := cluster.OpenBucket("default", "")

	for j := 0; j < 100; j++ {
		bucket.Insert(fmt.Sprintf("%v", j), j, 0)
	}

	// count number of documents in bucket
	count := func() int {
		q := gocb.NewN1qlQuery("SELECT COUNT(*) AS total FROM default")
		q = q.Consistency(gocb.RequestPlus)
		res, _ := bucket.ExecuteN1qlQuery(q, nil)
		var i map[string]int
		res.One(&i)
		return i["total"]
	}

	println("Total: ", count()) // 100

	// DELETE all docs
	q := gocb.NewN1qlQuery("DELETE FROM default")
	q = q.Consistency(gocb.RequestPlus)
	bucket.ExecuteN1qlQuery(q, nil)

	println("Count: ", count())  // 100

	time.Sleep(time.Second)  // sleep 1s
	println("Count: ", count())  // 70

	time.Sleep(time.Second) // sleep 1s
	println("Count: ", count()) // 36

	time.Sleep(time.Second) // sleep 1s
	println("Count: ", count()) // 4
}

Output;

Total:  100
Count:  100
Count:  70
Count:  36
Count:  4
2

Can you try this from the cbq shell? That will tell us if it’s a server issue or a golang SDK issue.

3

Try to test from cbq and result seems the same:

cbq> \set -scan_consistency "REQUEST_PLUS";
cbq> -- insert 100 docs
cbq> INSERT INTO default(KEY, VALUE) VALUES('0', 0), VALUES('1', 1), VALUES('2', 2), VALUES('3', 3), VALUES('4', 4), VALUES('5', 5), VALUES('6', 6), VALUES('7', 7), VALUES('8', 8), VALUES('9', 9), VALUES('10', 10), VALUES('11', 11), VALUES('12', 12), VALUES('13', 13), VALUES('14', 14), VALUES('15', 15), VALUES('16', 16), VALUES('17', 17), VALUES('18', 18), VALUES('19', 19), VALUES('20', 20), VALUES('21', 21), VALUES('22', 22), VALUES('23', 23), VALUES('24', 24), VALUES('25', 25), VALUES('26', 26), VALUES('27', 27), VALUES('28', 28), VALUES('29', 29), VALUES('30', 30), VALUES('31', 31), VALUES('32', 32), VALUES('33', 33), VALUES('34', 34), VALUES('35', 35), VALUES('36', 36), VALUES('37', 37), VALUES('38', 38), VALUES('39', 39), VALUES('40', 40), VALUES('41', 41), VALUES('42', 42), VALUES('43', 43), VALUES('44', 44), VALUES('45', 45), VALUES('46', 46), VALUES('47', 47), VALUES('48', 48), VALUES('49', 49), VALUES('50', 50), VALUES('51', 51), VALUES('52', 52), VALUES('53', 53), VALUES('54', 54), VALUES('55', 55), VALUES('56', 56), VALUES('57', 57), VALUES('58', 58), VALUES('59', 59), VALUES('60', 60), VALUES('61', 61), VALUES('62', 62), VALUES('63', 63), VALUES('64', 64), VALUES('65', 65), VALUES('66', 66), VALUES('67', 67), VALUES('68', 68), VALUES('69', 69), VALUES('70', 70), VALUES('71', 71), VALUES('72', 72), VALUES('73', 73), VALUES('74', 74), VALUES('75', 75), VALUES('76', 76), VALUES('77', 77), VALUES('78', 78), VALUES('79', 79), VALUES('80', 80), VALUES('81', 81), VALUES('82', 82), VALUES('83', 83), VALUES('84', 84), VALUES('85', 85), VALUES('86', 86), VALUES('87', 87), VALUES('88', 88), VALUES('89', 89), VALUES('90', 90), VALUES('91', 91), VALUES('92', 92), VALUES('93', 93), VALUES('94', 94), VALUES('95', 95), VALUES('96', 96), VALUES('97', 97), VALUES('98', 98), VALUES('99', 99);
{
    "requestID": "fdf48802-49f8-4655-ad14-46342d7dae90",
    "signature": null,
    "results": [
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "31.044278ms",
        "executionTime": "31.0084ms",
        "resultCount": 0,
        "resultSize": 0,
        "mutationCount": 100
    }
}
cbq> -- delete all
cbq> DELETE FROM default;
{
    "requestID": "31896531-0585-4aec-9c2f-f623228b2bd9",
    "signature": null,
    "results": [
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "67.294026ms",
        "executionTime": "67.226459ms",
        "resultCount": 0,
        "resultSize": 0,
        "mutationCount": 100
    }
}
cbq> -- count
cbq> SELECT COUNT(*) AS total FROM default;
{
    "requestID": "ede32491-922e-4428-8295-b05eb4f0e44d",
    "signature": {
        "total": "number"
    },
    "results": [
        {
            "total": 100
        }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "11.727531ms",
        "executionTime": "11.690882ms",
        "resultCount": 1,
        "resultSize": 36
    }
}
cbq> SELECT COUNT(*) AS total FROM default;
{
    "requestID": "91d383d7-5acb-4e66-a06c-e7d41f67f43c",
    "signature": {
        "total": "number"
    },
    "results": [
        {
            "total": 96
        }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "12.737386ms",
        "executionTime": "12.701095ms",
        "resultCount": 1,
        "resultSize": 35
    }
}
cbq> SELECT COUNT(*) AS total FROM default;
{
    "requestID": "71d81e74-934d-4e54-a0c3-ab9894901b6a",
    "signature": {
        "total": "number"
    },
    "results": [
        {
            "total": 96
        }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "9.670446ms",
        "executionTime": "9.637623ms",
        "resultCount": 1,
        "resultSize": 35
    }
}
cbq> SELECT COUNT(*) AS total FROM default;
{
    "requestID": "16350f69-e154-4669-8a07-6e237cb01927",
    "signature": {
        "total": "number"
    },
    "results": [
        {
            "total": 92
        }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "9.67858ms",
        "executionTime": "9.645474ms",
        "resultCount": 1,
        "resultSize": 35
    }
}
cbq> SELECT COUNT(*) AS total FROM default;
{
    "requestID": "0b1e7f1e-02de-4af5-b385-155ddd9274e5",
    "signature": {
        "total": "number"
    },
    "results": [
        {
            "total": 88
        }
    ],
    "status": "success",
    "metrics": {
        "elapsedTime": "9.451264ms",
        "executionTime": "9.413842ms",
        "resultCount": 1,
        "resultSize": 35
    }
}
cbq>
4

hi @pipo,
what is the version you are using? I am not able to repro this with my local 4.5.1 setup. Tried with 50000 docs.

5

I’m using 4.5.1 too, run from docker image.