Changelog
Getting Started
Oxla Cloud
SQL Reference
- Overview
- SQL Statements
- SQL Clauses
- SQL Data Types
- SQL Functions
- Schema
- Comment Support
System Catalogs
Integrations
Access Control
Changelog
Oxla Beta Release 1.53.0 - 27/11/24
- Access Control (newly created Oxla Homes have it enabled by default)
- Support for C# Dapper via Npgsql connector
- Support for
DROP USERsyntax - Support for
pg_authid,pg_shadow,pg_rolesandpg_usertables - Limited
DISTINCTsupport in aggregation functions - Support for
FETCH NEXT N ROWS ONLYsyntax as a syntactic sugar forLIMIT
- Reported server Postgres version to
16.0
- Corrupted ORC files no longer crash Oxla.
COPY table(columns) FROMno longer leads to error-related null constraint - Issue with
COPY FROMstatements for ORC format. Files that contained null values, used to sporadically insert them in cells instead of the value - Crashes happening during JOIN operation when the query reached out of memory state
- Bug where a specific case of
UPDATEon indexed tables used to lead to data loss
- In case of virtual machine issues, users should configure
max_mem_countincreasing the memory map areas, in the following way:sysctl -w vm.max_map_count=262144 - On old Oxla Homes, access control is disabled by default and users have to turn it on explicitly
Oxla Beta Release 1.51.0 - 05/11/24
- Support for
WITHstatements - Support for Java Database Connectivity (JDBC) API
- New virtual tables:
pg_catalog.pg_auth_membersinformation_schema.role_usage_grantsinformation_schema.role_table_grants
- New
network.slots_portconfig option, that configures TCP port used for data exchange between nodes during query execution (defaults to5770)
- Oxla now uses two distinct TCP ports for internal cluster communication - one for cluster orchestration (
network.port) and one for data exchange (network.slots_port) /and.date separators are accepted in timestampsCOPY FROMfor an ORC format is now multithreaded, which comes with speed improvementsCOPY FROMfor an ORC format allows for skipping trailing nullable columns- Significant improvement of handling queries with
LIMITclause, for cases in which query can end before scanning all input - Increased PostgreSQL compatibility of returned error types during planning phase
- Improvements in timestamp parsing performance
- Bug where mutation could not be run, once the previous one has ran out of available memory
Oxla Beta Release 1.48.0 - 23/10/24
- Support for
INT4andINT8type aliases - Support for values of interval type in
SUM()function - Support for optional parentheses around values in the
INSERTstatements - Support for date, time and time with timezone types in
COPY FROMstatement with ORC files - Support for
VERSIONfunction - Faster parallel execution of
INSERTstatements
- Oxla logs regarding internode disconnections now have warning severity instead of an error
- Improved and compatible with PostgreSQL error messaging when schema / table does not exist
oxla_internal.oxla_home_filespaths are relative to Oxla home locationRESULTis no longer a keywordROUND()behavior is aligned with PostgreSQL, so it rounds to the nearest even number- Virtual table file path is relative to Oxla home location
- Oxla now returns error when
SELECTstatement contains multipleORDER BY,LIMITandOFFSETclauses
- Validation for
DROP TABLEin write-protected namespaces. For exampleDROP TABLE pg_catalog.pg_classused to crash Oxla, while now it returnspermission denied for schema pg_catalog - Out of bounds reads that used to happen if
GROUP BYwas used internally during execution of a query - Bug where specific case of
JOINused to fail in Oxla Planner - Copying from ORC. Loading files bigger than 8192 rows used to have wrong results
- Putting
'or;in comments no longer results in syntax error - Data race that occurred in internode communication
Oxla Beta Release 1.42.0 - 2/9/24
- Support for additional option with interval literal, which trims down the output. Here are the possible options:
YEAR,MONTH,DAY,HOUR,MINUTE,SECONDYEAR TO MONTH,DAY TO HOUR,DAY TO MINUTE,DAY TO SECOND,HOUR TO MINUTE,HOUR TO SECOND,MINUTE TO SECOND- Examples:
SELECT INTERVAL '1' HOUR-> 01:00:00SELECT INTERVAL '1 YEAR 2 MONTH' YEAR-> 1 yearSELECT INTERVAL '1-2 3 day 04:05:06.070809' DAY TO HOUR-> 1 year 2 months 3 days 04:00:00
- New
oxla_db_event_journal_sizePrometheus metric to probe OXLA events journal size - Support for
SIGN()mathematical function - Support for date with dash format
- Improved support for PostgreSQL
COPYsyntax:CSVis accepted as an import option withoutFORMATpreceding itHEADERwithout any other specification is treated asHEADER ONWITHis supported without parenthesisDELIMITERandNULLimport options can be followed byAS
- Extended support for interval literal, e.g.
SELECT INTERVAL '2-1'-> 2 years 1 month- Now supports unit abbreviations and short forms:
- Year:
y,yr,yrs - Day:
d - Hour:
h,hr,hrs - Minute:
min,mins,m - Second:
s,sec,secs - Millisecond:
ms
- Year:
- Added support for weeks, e.g.
1 week,2 weeks
- Now supports unit abbreviations and short forms:
- Minor improvements in readability of error messages regarding invalid operations on non-user tables and on write-protected namespaces and tables. For example:
INSERT INTO information_schema.tablesused to returnrelation "tables" does not existerror, while now it returnsrelation "information_schema.tables" does not exist or is not user tableINSERT INTO pg_catalog.pg_attrdef values...used to result inrelation, pg_catalog.pg_attrdef does not exist or is not user table error, while now it returnspermission denied for table pg_catalog.pg_attrdef
- The docker entry-point now changes ownership of
/oxla/data,/oxla/logsand/oxla/crashdirectories tooxla:oxla - Logs that print queries won’t expose confidential data like S3 keys anymore
- Issue which caused
InvalidPart Message: One or more of the specified parts could not be founderror when copying data from CSV - Bugs where
DELETE/UPDATEstatements would incorrectly returnother modification of table is in progresswhen restarting Oxla
Oxla Beta Release 1.38.0 - 9/8/24
- “+” Operator for
timestamptzandinterval - “+” Operator for
intervalandtimestamptz - “-” Operator for
timestamptzandinterval MINandMAXfunctions for text columns- TCP KeepAlive support for PostgreSQL protocol connections available through
network.postgresql_keepalive.*configuration - New PostgreSQL protocol connection metrics:
oxla_net_postgres_nonlocalhost_connections_count- number of active non-localhost connectionsoxla_net_postgres_last_nonlocalhost_connection- timestamp of last non-localhost connection event [seconds]oxla_net_postgres_last_nonlocalhost_disconnection- timestamp of last non-localhost disconnection event [seconds]oxla_net_postgres_last_query_started- timestamp of last client query started event [seconds]oxla_net_postgres_last_query_finished- timestamp of last client query finished event [seconds]oxla_net_postgres_queries_ongoing- number of currently running client queries
- Oxla images now handle new
OXLA_LOG_FILEenvironment variable. It redirects Oxla process logs to a given file inside the container - On startup, each Engine node checks access to
OXLA_HOME - Support for scientific notation in literals, e.g.,
SELECT 1.1e+3 - Support for
ORDER BYkeyword fortimestampz - Non-reserved keywords in parser, which can be used as database objects’ names
- Removed a new line in error logs with nested errors
- Changed object identifiers (OIDs) assignability mechanism. OIDs of user-created tables, types, and namespaces will start from 16384, similar to PostgreSQL. This applies only to Oxla instances with a newly created
OXLA_HOME; for existingOXLA_HOME, OIDs will be assigned as before - Removed
file_namecolumn fromoxla_home_files - Enabled
IF NOT EXISTSforCREATE TABLEandCREATE SCHEMA - Enabled
IF EXISTSforDROP SCHEMA - Bumped minor file format version. Oxla supports both new and old versions
- Changed default database name to ‘oxla’ (lowercase)
COPY TOCSV file will now terminate each row with Unix-style newline (“\n”) instead of Windows/MS-DOS carriage return/newline (“\r\n”)
- Oxla now discards extended query flow requests after any error until Sync is issued by the frontend
- Fixed unbounded memory growth after query failure during startup
- Fixed empty strings and random numbers output in Oxla home files
- Fixed error message when trying to access a nonexistent table: table identifier is now included in the message
- Fixed unexpected error with
SELECT *insideUNIONclause
- Oxla data directory must be writable to perform automatic data migration. The migration is needed due to the change of the default database name
Oxla Beta Release 1.32.0 - 24/6/24
- Faster
LIKEoperator - Experimental support for COPY FROM with ORC binary format to load data
- Equality operator support for JSON objects (key-order-sensitive, opposite to PostgreSQL)
- Experimental support for Azure Blob Storage
- Experimental support for Google Cloud Storage
- Metric showing per node internal bookkeeping tasks capacity
- New config parameter: insertion. large_copy_buffer_size_limit (default 128M). Increasing it can speed up COPY FROM statements pipeline at the cost of greater memory usage
receipts_received_totalmetrics- Support for binary serialization of query results in PostgreSQL protocol
- Date returned as string will always have two digits for month / day (with 0 prefix if needed). For example:
select '2024-5-13'::dateused to return2024-5-13, from now on it will return2024-05-13 - Improved stability of PostgreSQL protocol handling
- Several times faster LIKE operator
- Increased speed of the COPY FROM statements pipeline and reduced the number of storage requests 7 times
task_capacity_totalmetric
regex_replacewill work similar as in PostgreSQL, replacing all occurrences only with ‘g’ flag (otherwise replacing only one)- Small memory leaks in OpenSSL usage
- Issues when null was used as a parameter instead of a literal string
- Increased stability for large COPY FROM queries
- Fixed Issue that sometimes prevented internall bookkeeping tasks from finishing successfuly
Oxla Beta Release 1.27.0 - 12/5/24
- Faster
LIKEoperator - Faster
GROUP BYwhen at least one of the key columns has TEXT type - Verbose level logs generator by
pgbouncerwhen debug flag is added - Support for
TRUNCATEtable - Added parameters to initial response when postgres protocol client connects to Oxla:
TimeZoneandinteger_datetimes. This is needed for compatibility with some external tools - Faster scanning over TEXT and JSON columns in hot run
- Experimental support for Google Cloud Storage
- Incoming connections from localhost (127.0.0.1) will no longer be logged on info, which will remove internal healthcheck log spam
- Rare issues causing queries to hang when executing a high volume of
INSERT,UPDATEandDELETEqueries in rapid succession - Problem with setting up connection between nodes
- Error during planning query containing duplicating expressions in
UNION,INTERSECTorEXCEPT LEFTandFULL JOINreturning wrong results on multiple nodes in some situations- A bug where using
COUNTfunction without any parameters caused a crash - Some cases where
regexp_replaceworked incorrectly. For example selectregexp_replace(‘123x’, ’(\d+)’, ‘abc’) returned abcabcx, now it correctly returns abcx
Oxla Beta Release 1.20.0 - 4/4/24
pg_table_is_publishablefunction was added, takesregclassas parameter (table oid or name) and returns whether relation is publishable. Oxla doesn’t support publishing hence it always returnsfalse- Added support for
TIMEandJSONtypes inINexpressions - Added
pg_table_sizefunction takes one parameter which is table regclass and returns it’s size Quote_Identfunction mimicking Quote_Ident from postgres- Add function
POSITION(str IN substr), that works the same asSTRPOS - Parser accepts now both
LFandCRLFas line break - Updated metric for oxla internal tasks handling
- Very limited support for
pg_catalog.pg_proctable - Add function
pg_catalog.pg_get_constraintdefalthough descriptions are not supported by Oxla, so it always returns null - substring function (it works the same as substr)
pg_table_is_visiblefunction now also returning whether index associated to given oid is visible within current search_path- Function
format_type. It works similar as in Postgres (but only for data types supported in Oxla) pg_table_is_visiblefunction returning whether table associated to given oid is visible within current search_path- Added support for time zones in case, when the OXLA server does not have access to the internet
- Added flags to the oxla config enabling fast commits
- Functions
shobj_descriptionandcol_descriptionadded. They always return null, as Oxla does not support “comment on” - added empty tables for tools satisfaction:
pg_policy,pg_statistic_ext,pg_publication,pg_publication_rel,pg_publication_namespace,pg_inherits pg_get_userbyidfunction returning username associated to given oid- OID generated for index (visible in
pg_indexandpg_classmetatables) will be different for each new index. Previously it was always 0 (and it will remain 0 for indices created on older oxla version) set datestyle = 'ISO, MDY';is supported (or simply ‘ISO’), but does nothing as ISO, MDY is default value. This is required by some connectors/tools
- Removes arithmetic expression from
GROUP BYcolumns if they are neutral for aggregation (e. g. changes A + 4 into A) - Optimization, transforms
SUM(A + k)intoSUM(A) + k * COUNT(*) - Much faster
ORDER BY col LIMIT BY ...where col is a scalar column (integer,floating point,booleanorstring) - Faster
SUMoperation in aggregation over whole table overINTEGER,LONG,FLOAT,DOUBLEnon nullable columns - Faster
MIN,MAXoperation in aggregation over whole table overINTEGER,FLOAT,DOUBLEnon nullable columns - Faster <> operator comparing
INTEGERandLONGtypes with literal (e. g.my_col <> 53) - ~5x faster LENGTH function
- Operator <> works faster when comparing with constant
- Minor performance improvement of
WHEREandJOINoperations - Removed implementation implementation detail fields from defaultly generated config file
- Changed type names in
pg_catalog.pg_type, so they match the ones in Postgres. This change applies only to how type names are shown inpg_type(Oxla handles types exactly the same as before). Changed names: integer -> int4, real -> float4, boolean -> bool, bigint -> int8, double precision -> float8, timestamp without time zone -> timestamp, timestamp with time zone -> timestamptz, time without time zone -> time - Faster/cheaper CSV reading speed on S3 due to query count reduction
- Fixed
pg_total_relation_sizecrashing Oxla when provided with invalid literal oid - Fixed issue that caused queries to hang when executing a high volume of insert queries in rapid succession
STRPOSfunction is working- Fixed out of memory errors crashing Oxla for some queries
- Faster CSV reader initialization
- Fix unsynchronized memory access that theoretically could cause improper behavior
- Fixed a bug, where
length(<const value>)function would fail
Oxla Beta Release 1.14.0 - 21/2/24
- A sanity check on oxla startup for minimal
memory.maxandmemory.non_query_maxconfiguration information_schema.columns, which contain information about all columns in user namespacespg_languagetableto_char functionconverting timestamp/timestamptz to string using given format string- Support for int8 and int4 binary parameters in Postgres extended query protocol
Oxla Beta Release 1.13.0 – 16/2/24
- Add
information_schema.referential_constraintstable for tools integration purposes - Add
to_charfunction to convert the number to string using the given format string - Add
to_timestampfunction to convert the string to timestamp using given format string - Cast from time to
interval date_truncfunction working like Postgresdate_trunc - DISCARD ALL to reset all session variables
- Add support for
pg_catalog.has_database_privilege()function - Possibility to the hardcode leader of the cluster in config
- Postgres-like tables:
pg_enum,pg_range,information_schema.character_sets - Enabled optimization, taking advantage of complex expressions in filters
- Add
pg_encoding_to_charandpg_size_pretty functionfunction - Add
pg_catalog.obj_descriptionfunction although descriptions are not supported by Oxla, always returns null - Add table
tablesin the information_schema namespace - Add table
pg_catalog.pg_tablespace,pg_catalog.pg_database,information_schema.key_column_usage, andpg_catalog.pg_amsupport - Support for operator interval div number
- Reduced the amount of s3 requests when performing
COPY FORM (FORMAT CSV) - Improved the error messages in
INSERT INTO VALUESqueries
- Increased stability of
COPY FROMqueries with large inputs - Fixed the issue with
INSERT INTO SELECTqueries where data types were not cast to proper types automatically
Oxla Beta Release 1.8.0 - 12/1/24
- Optimization of HAVING clause when combined with WHERE clause in the same query
- Support for String Constants with C-Style Escapes
- Support for
COPY (table|select statement) TO filename.csv (options)query - Support for operator interval * float
- Fixed the instability issue when the whole table’s content has been deleted using
DELETE FROM <table_name> - Fixed the instability cluster issue when disconnecting a disconnected node bug in cluster monitoring
- Fixed a bug that appeared during conversion intervals when the interval used a date/time format
- Fixed the error handling response when the pipeline startup failed
- Fixed a bug with duplicated name detection caused the cluster to be inoperable after startup
- Fixed a bug on
INSERT INTO table SELECTstatement due to the lifetime issues
Oxla Beta Release 1.4.0 - 14/12/23
- Support for date - date operator
- Support for time - time operator
- Support for date + time operator
- Improved performance of query planning
- Fixed intermittent crashes in the GROUP BY
- Fixed a bug that happens when deleting the index table
- Fixed the Google Cloud Storage compatibility issue
- Fixed an issue that could result in a crash when running GROUP BY over columns with very high cardinality
- Fixed an issue that could result in deadlock upon cluster startup
- The default AWS S3 endpoint is used when no endpoint is specified in the COPY statement
Oxla Beta Release 1.3.0 - 06/12/23
- Fixed the degradation error failure when OXLA_HOME was an empty S3 folder created before Oxla started
- Fixed a bug where OXLA would not return an error when cast would overflow the integer type
- Fixed a bug where OXLA would return an error when casting values from Date type to Timestamp/Timestamptz
- Fixed the issue of improper read of storage_config.s3.http parameter
- Fixed a bug in an optimization causing some queries using aggregation functions as WHERE conditions to fail to plan and run on OXLA
- The COPY statement with unsupported FORMAT (tbl / binary / bin) will return error (”… format not supported”). Previously all these formats, despite their names, resulted in importing CSV. COPY without provided FORMAT will still use CSV as default format
- The format of internal messages passed between Oxla nodes for COPY CSV changed, so the new node and old node must not be in the same cluster when COPY is being executed
- We are working closely with our team to resolve this issue as soon as possible
Oxla Beta Release 1.2.0 - 27/11/23
- Handling columns specified in the INSERT INTO with SELECT. e.g. INSERT INTO tbl1(c2) SELECT col1 FROM tbl2
- Extended support for implicit conversions of timestamp-like types in INSERT and COPY queries
- Fixed the arithmetic operator was used to determine the output type
- Fixed the errors with storage communication
- Fixed the error handling when downcasting number types with losing data
- Fixed the issue that Oxla sometimes left files when dropping tables or deleting data
Oxla Beta Release 1.1.0 - 20/11/23
- Support for Basic INSERT INTO [table] SELECT [statement]
- Improved performance of most GROUP BY variants
- Slight performance improvement of GROUP BY over numeric columns
- Degraded node stays in the cluster
- Fixed the issue with the node connection to the cluster
- Disabled the empty string as a valid internal value
- Fixed the failures when reading timestamp values from CSV
On this page
- Oxla Beta Release 1.53.0 - 27/11/24
- Oxla Beta Release 1.51.0 - 05/11/24
- Oxla Beta Release 1.48.0 - 23/10/24
- Oxla Beta Release 1.42.0 - 2/9/24
- Oxla Beta Release 1.38.0 - 9/8/24
- Oxla Beta Release 1.32.0 - 24/6/24
- Oxla Beta Release 1.27.0 - 12/5/24
- Oxla Beta Release 1.20.0 - 4/4/24
- Oxla Beta Release 1.14.0 - 21/2/24
- Oxla Beta Release 1.13.0 – 16/2/24
- Oxla Beta Release 1.8.0 - 12/1/24
- Oxla Beta Release 1.4.0 - 14/12/23
- Oxla Beta Release 1.3.0 - 06/12/23
- Oxla Beta Release 1.2.0 - 27/11/23
- Oxla Beta Release 1.1.0 - 20/11/23