Syntax error on npgsql

Hi,

I’ve been trying to get an insecure local CockroachDB with npgsql to work with NET Core. So far, I’ve been getting

syntax error at or near “discard” DISCARD ALL

Both from Entity Framework and plain SQL methods. Have anyone got into this issue? Any help is appreciated.

Thanks

Hi @pii,

We don’t currently have support for the DISCARD family of statements. I’ve opened https://github.com/cockroachdb/cockroach/issues/16587 to address that shortcoming.

In the meantime, another way to get around this problem would be to convince npgsql to not send DISCARD ALL during session initialization.

1 Like

Thank you Jordan! But this leaves me wonder that somehow the SQL command that I wanted to execute end up in errors so that npgsql called a DISCARD ALL to roll back the changes?

Hmm, it’s possible. At first glance (although I’m not a C# expert) it looks like npgsql does invoke DISCARD ALL on connection reset: https://github.com/npgsql/npgsql/blob/84e00282a1042ce4147a8f5b5c1583877122a3fc/src/Npgsql/NpgsqlConnector.cs#L1426

On the other hand, according to the comment above that function, Reset is called whenever a connection is returned to the pool - and that could be during normal operation.

If you want to be sure, you can run cockroach with the additional flag --vmodule=executor=2 and try your program again - that will add every SQL statement executed to the log, as well as any errors encountered during execution.

1 Like

Thank you Jordan!

Here’s a partial dump of the log produced by EF from .NET Core prior to ‘DISCARD ALL’

I170619 18:56:59.621852 2353 sql/executor.go:1110 [client=[::1]:63510,user=maxroach,n1] SELECT ns.nspname, a.typname, a.oid, a.typrelid, a.typbasetype, CASE WHEN pg_proc.proname = ‘array_recv’ THEN ‘a’ ELSE a.typtype END AS type, CASE WHEN pg_proc.proname = ‘array_recv’ THEN a.typelem WHEN a.typtype = ‘r’ THEN rngsubtype ELSE 0 END AS elemoid, CASE WHEN pg_proc.proname IN (‘array_recv’, ‘oidvectorrecv’) THEN 3 WHEN a.typtype = ‘r’ THEN 2 WHEN a.typtype = ‘d’ THEN 1 ELSE 0 END AS ord FROM pg_type AS a JOIN pg_namespace AS ns ON (ns.oid = a.typnamespace) JOIN pg_proc ON pg_proc.oid = a.typreceive LEFT JOIN pg_type AS b ON (b.oid = a.typelem) LEFT JOIN pg_range ON (pg_range.rngtypid = a.oid) WHERE ((a.typtype IN (‘b’, ‘r’, ‘e’, ‘d’)) AND ((b.typtype IS NULL) OR (b.typtype IN (‘b’, ‘r’, ‘e’, ‘d’)))) OR ((a.typname IN (‘record’, ‘void’)) AND (a.typtype = ‘p’)) ORDER BY ord
I170619 18:56:59.622844 2353 sql/mon/mem_usage.go:520 [client=[::1]:63510,user=maxroach,n1] txn: memory usage increases to 17 KiB (+1512)
I170619 18:56:59.623346 2353 sql/mon/mem_usage.go:520 [client=[::1]:63510,user=maxroach,n1] txn: memory usage increases to 32 KiB (+170)
I170619 18:56:59.623346 2353 sql/mon/mem_usage.go:520 [client=[::1]:63510,user=maxroach,n1] txn: memory usage increases to 64 KiB (+4392)
I170619 18:56:59.623847 2353 sql/mon/mem_usage.go:520 [client=[::1]:63510,user=maxroach,n1] txn: memory usage increases to 128 KiB (+10248)
I170619 18:56:59.624348 2353 sql/mon/mem_usage.go:520 [client=[::1]:63510,user=maxroach,n1] txn: memory usage increases to 256 KiB (+166)
I170619 18:56:59.624851 2353 sql/executor.go:1512 [client=[::1]:63510,user=maxroach,n1] query not supported for distSQL: only inner join supported
I170619 18:56:59.625864 2353 sql/mon/mem_usage.go:520 [client=[::1]:63510,user=maxroach,n1] txn: memory usage increases to 518 KiB (+17568)
I170619 18:56:59.626353 2353 sql/executor.go:1110 [client=[::1]:63510,user=maxroach,n1] SELECT 28 done
I170619 18:56:59.629343 2353 sql/executor.go:1110 [client=[::1]:63510,user=maxroach,n1] preparing: SELECT EXISTS (SELECT 1 FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON n.oid=c.relnamespace WHERE c.relname=’__EFMigrationsHistory’)
I170619 18:56:59.629844 2353 sql/executor.go:582 [client=[::1]:63510,user=maxroach,n1] execRequest: SELECT EXISTS (SELECT 1 FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON n.oid=c.relnamespace WHERE c.relname=’__EFMigrationsHistory’)
I170619 18:56:59.629844 2353 sql/executor.go:928 [client=[::1]:63510,user=maxroach,n1] executing 1/1: SELECT EXISTS (SELECT 1 FROM pg_catalog.pg_class AS c JOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace WHERE c.relname = ‘__EFMigrationsHistory’)
I170619 18:56:59.629844 2353 sql/executor.go:1110 [client=[::1]:63510,user=maxroach,n1] SELECT EXISTS (SELECT 1 FROM pg_catalog.pg_class AS c JOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace WHERE c.relname = ‘__EFMigrationsHistory’)
I170619 18:56:59.630354 2353 sql/mon/mem_usage.go:520 [client=[::1]:63510,user=maxroach,n1] txn: memory usage increases to 17 KiB (+2544)
I170619 18:56:59.630847 2353 sql/executor.go:1512 [client=[::1]:63510,user=maxroach,n1] query not supported for distSQL: subqueries not supported yet
I170619 18:56:59.630847 2353 sql/executor.go:1110 [client=[::1]:63510,user=maxroach,n1] SELECT 1 done
I170619 18:56:59.719219 2353 sql/executor.go:582 [client=[::1]:63510,user=maxroach,n1] execRequest: DISCARD ALL
I170619 18:56:59.719219 2353 sql/executor.go:598 [client=[::1]:63510,user=maxroach,n1] execRequest: error: syntax error at or near "discard"
DISCARD ALL

I’m not exactly sure what’s happening :joy:

Thanks @pii! Yeah the logs can be a little bit verbose. This reminds me that we should really get around to adding a more user-friendly query log.

It looks to me like there are no errors in your queries - the “query not supported for distSQL” messages are benign and simply mean that they get executed via the single-node SQL execution engine because our query planner doesn’t know how to distribute those queries yet.

So the only error occurs from DISCARD ALL itself.

Thank you @jordan for your clarifications.

Apart from being stuck on npgsql + .NET Core EF stack. I’ve managed to get an INSERT SQL Command working on a npgsql + .NET Core.

It turns out that I’ve made a mistake with my SQL Command last time. CockroachDB working on .NET Core confirmed!