Alpha

These pages are built from main, turborel 0.0.0. Anything may change from one build to the next: SQL, API, protocol and files alike.

Appendix D: Limits

SQL sets few limits of its own. A statement past one of these is valid SQL Turborel does not process: it is refused with SQLCODE -900, SQLSTATE 0A000.

Lengths and precisions

What Limit
An identifier 128 characters
Levels of a partitioning tree below its root (extension) 16
Partitions of a tree with a global index, by their ids (extension) 16,777,216
Rows of a partition with a global index, by their ids (extension) 1,099,511,627,776
CHARACTER and CHARACTER VARYING 32,767 characters
BINARY and BINARY VARYING 32,767 octets
CHARACTER LARGE OBJECT and BINARY LARGE OBJECT 4G, 4,294,967,296 characters or octets
A LIKE pattern or escape, or a regular expression's pattern, flag or replacement, of a large object 262,144 octets, a chunk; a LIKE match value under UNICODE or a tailoring too
NUMERIC, DECIMAL and an exact literal 18 digits
FLOAT 53 binary digits; an approximate literal within the range of DOUBLE PRECISION
DECFLOAT 34 digits; an exponent of its leading digit from -6143 to 6144
A datetime's fraction of a second 6 digits
An interval's leading field 9 digits; its fraction of a second 6
An unsigned integer, as a length 4,294,967,295
An array's maximum cardinality 65,535 elements, also the maximum of an array type written without one
A multiset's elements 65,535; past it, SQLSTATE 2200Q
An XML value Elements nested 512 deep; 16 MiB of text and 1,048,576 nodes and namespace bindings in one tree; 16 MiB more of entities' text in one parse; 64 MiB of text XMLPARSE reads; a stored form of 64 MiB
An XQuery query Expressions nested 64 deep; operations and function calls nested 256 deep as it is evaluated, expressions 256 deep as it is statically typed, a static type 256 deep, 4,096 item types an axis's closure meets; 1,048,576 items in one sequence; 64 MiB of nodes one evaluation constructs
A number in JSON text or an SQL/JSON path an exact one within DECFLOAT's range, an approximate one within DOUBLE PRECISION's
The precision of an SQL/JSON item method decimal() 18 digits; time(), time_tz(), timestamp() and timestamp_tz() 6, and a string datetime() reads, or its template's FFn, 6 digits of a second's fraction
A FULLTEXT value or a full-text search pattern 32,767 characters; past it, SQLSTATE 22001
A full-text language's name 32 characters
The array a full-text tokenizing method gives 65,535 elements; past it, SQLSTATE 2202F
The relations of the thesauri a statement reads 65,535
A VECTOR's dimension count 1 to 65,535; of a sparse vector 2,147,483,647; of BINARY 8 to 65,528, a multiple of 8
A vector's text in a character large object 16,777,216 octets; past it, SQLSTATE 22003
The text EMBED and the chunking functions read of a character large object 16,777,216 octets; past it, SQLSTATE 22000

Nesting

A statement is at most 64 levels deep as it is read: about 30 nested parentheses, 60 AND, OR, + or UNION in a row, or 60 tables in a FROM clause. A deeper one is refused rather than run out of stack.

Triggers run within triggers at most 16 deep: a trigger whose action runs a 17th fails its statement with SQLSTATE 09000, SQLCODE -900.

A full-text search pattern nests at most 100 deep, and a deeper one is SQLSTATE 2FF01.

Array, multiset and row types nest at most 16 deep, an array of rows of arrays counting three. A deeper type is refused as it is read.

A regular expression compiles to at most 8,192 instructions, about as many characters, each counted repeat as many times as its count, so a{9000} is refused. Its groups nest at most 64 deep. A search of a pattern with a back-reference goes back to try each way and stops past 10,000,000 steps; one without any takes time linear in the string, with no limit. Each is SQLCODE -900.

JSON text nests arrays and objects at most 128 deep, and an SQL/JSON path expression is at most 64 deep, of parentheses, filters, subscripts and signs, or of accessors and operators in a row. A deeper one is SQLCODE -900, which ON ERROR does not replace. JSON text, or a PASSING argument, in a large object is read at most 262,144 octets long, a chunk.

A JSON object JSON_OBJECT or JSON_OBJECTAGG makes has at most 1,048,576 members, and an array JSON_ARRAY or JSON_ARRAYAGG makes at most 1,048,576 elements, those left out ABSENT ON NULL not counted; one more is SQLSTATE 2203E or 2203D. JSON_ARRAYAGG holds the rows of a group until it orders them, so it holds no more than that.

Grouping

A GROUP BY stands for at most 4,096 grouping sets: a CUBE of 12 columns or sets, or ROLLUPs and GROUPING SETS whose sets, multiplied, come to no more. GROUPING names at most 59 columns, since its value, up to 2 to their number less 1, has at most the 18 digits of its type.

Sizes

What Limit
An index's key of the default 8 KiB page, 2,007 bytes in a B-link tree, 8,116 in a hash index; of a 4 KiB page, 983 in a B-link tree; of a 64 KiB page, 16,343 in a B-link tree, 65,460 in a hash index
A word a full-text index keeps its first 256 characters, and of those its first 255 bytes in UTF-8; a longer word is found by that prefix and checked against the row
A document a full-text index reads none: it is read a word at a time
A GEOMETRY value 16 MiB of WKT or WKB, 100,000 components, rings and coordinates in all, and 16 levels of nesting, the value's own included; more is refused with SQLCODE -900
A GEOMETRY value's WKT or WKB in SQL 16 MiB: ST_ASTEXT returns a CHARACTER LARGE OBJECT and ST_ASBINARY a BINARY LARGE OBJECT, the constructors take one, each up to the limit above (Spatial Data)
A row none: its long values are kept out of line
A message to the server SERVER.MAX_MESSAGE_BYTES, 16 MiB by default
Connections at once SERVER.MAX_CONNECTIONS, 100 by default
Prepared statements of a connection SERVER.MAX_STATEMENTS, 1,024 by default
Savepoints of a transaction 1,024; past it, SAVEPOINT fails with SQLSTATE 3B002, SQLCODE -917

An index's key is compared whole in its page, so it is limited where a row is not; STORAGE.PAGE_BYTES, chosen as a database is made, raises the limit. A column of a collation is keyed by its sort key, about 16 bytes for each character under UNICODE and half as many again under a language's collation, so its index holds shorter text.