Querying XML columns from MS SQL server via FreeTDS consumes too much memory.

[GoogleCodeExporter]

What steps will reproduce the problem?
1. Create an XML column in an MS SQL server database.
2. Query the column.

What is the expected output? What do you see instead?

It should work with out allocating GBs from the heap. Instead it allocates a 
2GB []byte in NewBindableColumn

What version of the product are you using? On what operating system?

See below.

Please provide any additional information below.

Here is a stack trace copied from /debug/pprof/heap?debug=1 from my application 
shortly before it crashed with an out of memory error.

1: 2147483648 [1: 2147483648] @ 0x42a4d5 0x429fb8 0x42b701 0x42b78a 0x42ecbd 
0x42ebf3 0x4d23a5 0x4d24d4 0x4d1248 0x4d579e 0x4d7a05 0x43fff7 0x43caf5 
0x43c655 0x43c4ad 0x43ce27 0x4af37a 0x402b2c 0x4cf9ea 0x4cf047 0x4024c2 
0x4174da 0x419ca0
#   0x42a4d5    profilealloc+0xb5                       /usr/local/go/src/pkg/runtime/malloc.goc:258
#   0x429fb8    runtime.mallocgc+0x218                      /usr/local/go/src/pkg/runtime/malloc.goc:
197
#   0x42b701    cnew+0xc1                           /usr/local/go/src/pkg/runtime/malloc.goc:836
#   0x42b78a    runtime.cnewarray+0x3a                      /usr/local/go/src/pkg/runtime/malloc.goc:
849
#   0x42ecbd    makeslice1+0x4d                         /usr/local/go/src/pkg/runtime/slice.goc:55
#   0x42ebf3    runtime.makeslice+0xb3                      /usr/local/go/src/pkg/runtime/slice.goc:3
6
#   0x4d23a5    code.google.com/p/odbc.NewBindableColumn+0xd5           /home/chines/go/src/c
ode.google.com/p/odbc/column.go:189
#   0x4d24d4    code.google.com/p/odbc.NewVariableWidthColumn+0xf4      /home/chines/go/s
rc/code.google.com/p/odbc/column.go:211
#   0x4d1248    code.google.com/p/odbc.NewColumn+0x958              /home/chines/go/src/code.go
ogle.com/p/odbc/column.go:89
#   0x4d579e    code.google.com/p/odbc.(*ODBCStmt).BindColumns+0x22e        /home/chines/go
/src/code.google.com/p/odbc/odbcstmt.go:134
#   0x4d7a05    code.google.com/p/odbc.(*Stmt).Query+0x2e5          /home/chines/go/src/code
.google.com/p/odbc/stmt.go:91
#   0x43fff7    database/sql.rowsiFromStatement+0x317               /usr/local/go/src/pkg/databa
se/sql/sql.go:1439
#   0x43caf5    database/sql.(*DB).queryConn+0x465              /usr/local/go/src/pkg/database/
sql/sql.go:969
#   0x43c655    database/sql.(*DB).query+0x105                  /usr/local/go/src/pkg/database/sql
/sql.go:929
#   0x43c4ad    database/sql.(*DB).Query+0xad                   /usr/local/go/src/pkg/database/sql/
sql.go:915
#   0x43ce27    database/sql.(*DB).QueryRow+0x67                /usr/local/go/src/pkg/database/sq
l/sql.go:993
# <snip>

Also, I added this line at the top of NewVariableWidthColumn():
    fmt.Printf("col: %+v  type: %v  width: %v  stack: %v\n", b, ctype, colWidth, stack.Callers())

and it printed this for an XML column on Linux with FreeTDS v0.91:
        col: &{name:INFOXML CType:0}  type: 1  width: 2147483647

but it printed this for the same column on a Windows machine with MS drivers:
        col: &{name:INFOXML CType:0}  type: -8  width: 0

Finally this appears to be a known issue in FreeTDS and I can't find any 
evidence that it's been fixed: 
http://lists.ibiblio.org/pipermail/freetds/2012q3/028050.html

Is there anything that can or should be done to handle this better at the Go 
layer? Do you have any suggested work arounds?

Original issue reported on code.google.com by chris.cs...@gmail.com on 17 Oct 2014 at 4:11

[GoogleCodeExporter]

I didn't look at your problem properly, but it sounds like it works fine on 
Windows. If that is true, you can achieve similar behaviour on Linux by 
changing NewVariableWidthColumn function. Just go for NonBindableColumn not 
only when colWidth is 0, but also when it is “very large”. Pick a number 
for a “very large” - maybe 2MB or something - it is up to you.

I will think about this more, but I don’t see any better solution for it. If 
it is not too much trouble, please provide small example program for me to try. 
Thank you.

Alex

Original comment by alex.bra...@gmail.com on 18 Oct 2014 at 12:13

• Changed state: Accepted

[GoogleCodeExporter]

I cannot reproduce your problem here. I made changes similar to yours:

diff -r 78314be168c8 column.go
--- a/column.go Tue Sep 02 11:28:47 2014 +1000
+++ b/column.go Mon Oct 20 12:43:03 2014 +1100
@@ -192,6 +192,7 @@
 }

 func NewVariableWidthColumn(b *BaseColumn, ctype api.SQLSMALLINT, colWidth api.SQLULEN) Column {
+   fmt.Printf("col: %+v  type: %v  width: %v\n", b, ctype, colWidth)
    if colWidth == 0 {
        b.CType = ctype
        return &NonBindableColumn{b}
diff -r 78314be168c8 mssql_test.go
--- a/mssql_test.go Tue Sep 02 11:28:47 2014 +1000
+++ b/mssql_test.go Mon Oct 20 12:43:03 2014 +1100
@@ -1344,3 +1344,21 @@

    exec(t, db, "drop table dbo.temp")
 }
+
+func TestALEX(t *testing.T) {
+   db, sc, err := mssqlConnect()
+   if err != nil {
+       t.Fatal(err)
+   }
+   defer closeDB(t, db, sc, sc)
+
+   query := fmt.Sprintf("select cast('hello' as xml)")
+   var s string
+   err = db.QueryRow(query).Scan(&s)
+   if err != nil {
+       t.Fatal(err)
+   }
+   if s != "hello" {
+       t.Errorf("expected \"hello\", but received %v", s)
+   }
+}

and new test prints this on both linux and windows:

# go test -mssrv=xxx -msdb=xxx -msuser=xxxx -mspass=xxx -v -run=ALEX
=== RUN TestALEX
col: &{name: CType:0}  type: -8  width: 0
--- PASS: TestALEX (0.01s)
PASS
ok      code.google.com/p/odbc  0.011s

I use freetds-0.91 and unixODBC-2.3.1 on linux.

Alex

Original comment by alex.bra...@gmail.com on 20 Oct 2014 at 1:47

• Changed state: WaitingForReply

[GoogleCodeExporter]

I copied your test into my clone and got this:

$ TDSVER=7.2 go test -run TestALEX -mssrv=xxx -msdb=xxx -msuser=xxx -mspass=xxx
col: &{name: CType:0}  type: 1  width: 2147483647
PASS
ok      code.google.com/p/odbc  0.009s

The client is running CentOS release 6.4 (Final) with FreeTDS 0.91 and 
unixODBC-2.3.1 both installed from source. Note that I have to set the TDSVER 
to get the test to work at all. I tried to set the default TDSVER during 
FreeTDS install, but it didn't seem to take.

The DB server is running MS SQL Server 2012.

Original comment by chris.cs...@gmail.com on 20 Oct 2014 at 3:46