Beego: got 1516473 parameters but PostgreSQL only supports 65535 parameters when invokes Delete()

[microyahoo]

Please answer these questions before submitting your issue. Thanks!

1. What version of Go and beego are you using (bee version)?

(ENV) 🍺 /Users/xsky/go/src/xsky-demon ☞ git:(test) ✗ bee version
______
| ___ \
| |_/ /  ___   ___
| ___ \ / _ \ / _ \
| |_/ /|  __/|  __/
\____/  \___| \___| v1.10.0

├── Beego     : 1.10.1
├── GoVersion : go1.14.3
├── GOOS      : darwin
├── GOARCH    : amd64
├── NumCPU    : 4
├── GOPATH    : /Users/xsky/go
├── GOROOT    : /usr/local/go
├── Compiler  : gc
└── Date      : Monday, 8 Jun 2020

1. What operating system and processor architecture are you using (go env)?

(ENV) 🍺 /Users/xsky/go/src/xsky-demon ☞ git:(test) ✗ go env
GO111MODULE=""
GOARCH="amd64"
GOBIN=""
GOCACHE="/Users/xsky/Library/Caches/go-build"
GOENV="/Users/xsky/Library/Application Support/go/env"
GOEXE=""
GOFLAGS=""
GOHOSTARCH="amd64"
GOHOSTOS="darwin"
GOINSECURE=""
GONOPROXY=""
GONOSUMDB=""
GOOS="darwin"
GOPATH="/Users/xsky/go"
GOPRIVATE=""
GOPROXY="https://proxy.golang.org,direct"
GOROOT="/usr/local/Cellar/go/1.14.3"
GOSUMDB="sum.golang.org"
GOTMPDIR=""
GOTOOLDIR="/usr/local/Cellar/go/1.14.3/pkg/tool/darwin_amd64"
GCCGO="gccgo"
AR="ar"
CC="clang"
CXX="clang++"
CGO_ENABLED="1"
GOMOD=""
CGO_CFLAGS="-g -O2"
CGO_CPPFLAGS=""
CGO_CXXFLAGS="-g -O2"
CGO_FFLAGS="-g -O2"
CGO_LDFLAGS="-g -O2"
PKG_CONFIG="pkg-config"
GOGCCFLAGS="-fPIC -m64 -pthread -fno-caret-diagnostics -Qunused-arguments -fmessage-length=0 -fdebug-prefix-map=/var/folders/pb/rc1txhc12_x0jt1tmld1bn300000gn/T/go-build782939608=/tmp/go-build -gno-record-gcc-switches -fno-common"

1. What did you do?
   If possible, provide a recipe for reproducing the error.
   A complete runnable program is good.
   The program invokes Delete() in the file of github.com/astaxie/beego/orm/types.go, then calls Delete() in file of github.com/astaxie/beego/orm/orm_queryset.go, the actual invocation is DeleteBatch()in file of github.com/astaxie/beego/orm/db.go

178 // execute delete
179 func (o *querySet) Delete() (int64, error) {
180     return o.orm.alias.DbBaser.DeleteBatch(o.orm.db, o, o.mi, o.cond, o.orm.alias.TZ)
181 }

DeleteBatch first queries all the records, then justify the count of records and delete specific records using IN (...) statement, but it has limitation of no larger than 65535.
https://github.com/astaxie/beego/blob/3e30f37172671150e8903828e955c3ee5de1793e/orm/db.go#L885
https://github.com/lib/pq/blob/d408f9c9485706b60bb2b9b9718f3c54f077b506/conn.go#L1342

1. What did you expect to see?
   Succeed to delete records.

2. What did you see instead?
   time=2020-06-08T11:18:03+08:00 level=error module=controllers/base.go:477 msg="pq: got 1516473 parameters but PostgreSQL only supports 65535 parameters

[flycash]

I think we should provide some configurable options. For example, we define the configure named batchMaxSize, if batchMaxSize<0, it means no limitation and we will try our best to finish the query;
And if the batchMaxSize>0, we will use Min(DBLimitation, batchMaxSize) as the batch size In this case, we can split the query into several queries and finish the job, or return an error.

[gmelodie]

@flycash I could try my hand at this :)

[flycash]

Thank you, now you own this

[gmelodie]

@flycash I was going through the code at #4104 and realized it SELECTs all the data before calling the actual DELETE statement. Here's more or less how it works right now (if I'm not mistaken):

1. SELECT ... WHERE [condition]
2. Get the primary keys from the 1516473 previously SELECTed rows
3. DELETE ... IN (? ? ? ? ? .... ?) (1516473 ?s) and then call a delete statement.
4. Deal with table relationships (deleteRels).

This doesn't seem like a good solution even, and especially, if we batch the delete statements. Batching the delete statements means we will have to send multiple queries to the database only to do one simple delete. It's also why we got #4010 in the first place, because SQL is not made for you to have 1516473 parameters in one single query.

My question/proposal is: can we get rid of all that and simply do something like DELETE ... WHERE [condition]? Because (1) we shouldn't need to care about the relationships on the database, it should do that for us. And (2) This would be one single small query going through the network instead of one SELECT and probably several DELETEs.

[flycash]

@gmelodie I agree with you!!!