FirebirdSQL logo
 Data Definition Language (DDL)Procedural SQL (PSQL) 

SKIP LOCKED clause

Adriano dos Santos Fernandes

Tracker ticket: #7350

SKIP LOCKED can be used with SELECT …​ WITH LOCK, UPDATE and DELETE statements. It makes the engine skip records locked by other transactions instead of waiting on them or raise conflict errors.

This is very useful to implement work queues where one or more processes post work to a table and issue an event, while workers listen for events and read/delete items from the table. Using SKIP LOCKED multiple workers can get exclusive work items from the table without conflicts.

Syntax
SELECT
  [FIRST ...]
  [SKIP ...]
  FROM <sometable>
  [WHERE ...]
  [PLAN ...]
  [ORDER BY ...]
  [{ ROWS ... } | {OFFSET ...} | {FETCH ...}]
  [FOR UPDATE [OF ...]]
  [WITH LOCK [SKIP LOCKED]]

UPDATE <sometable>
  SET ...
  [WHERE ...]
  [PLAN ...]
  [ORDER BY ...]
  [ROWS ...]
  [SKIP LOCKED]
  [RETURNING ...]

DELETE FROM <sometable>
  [WHERE ...]
  [PLAN ...]
  [ORDER BY ...]
  [ROWS ...]
  [SKIP LOCKED]
  [RETURNING ...]
Note

If a statement has both SKIP LOCKED and OFFSET/SKIP/ROWS subclauses, locked rows may be skipped before OFFSET/SKIP/ROWS subclause can account for them, thus skipping more rows than specified in OFFSET/SKIP/ROWS.

Examples:

  • Prepare metadata

    create table emails_queue (
    subject varchar(60) not null,
    text blob sub_type text not null
);

set term !;

create trigger emails_queue_ins after insert on emails_queue
as
begin
    post_event('EMAILS_QUEUE');
end!

set term ;!

  • Sender application or routine

    insert into emails_queue (subject, text)
  values ('E-mail subject', 'E-mail text...');
commit;

  • Client application

    -- Client application can listen to event `EMAILS_QUEUE` to actually send e-mails using this query:

delete from emails_queue
  rows 10
  skip locked
  returning subject, text;

    More than one instance of the application may be running, for example to load balance work.

Support for WHEN NOT MATCHED BY SOURCE in the MERGE statement

Adriano dos Santos Fernandes

Tracker ticket: #6681

Syntax
<merge when> ::=
		<merge when matched> |
		<merge when not matched by target> |
		<merge when not matched by source>

<merge when not matched by target> ::=
		WHEN NOT MATCHED [ BY TARGET ] [ AND <condition> ] THEN
			INSERT [ <left paren> <column list> <right paren> ]
				VALUES <left paren> <value list> <right paren>

<merge when not matched by source> ::=
		WHEN NOT MATCHED BY SOURCE [ AND <condition> ] THEN
			{ UPDATE SET <assignment list> | DELETE }

<merge when not matched by target> is called when a source record matches no record in target. INSERT will change the target table.

<merge when not matched by source> is called when a target record matches no record in source. UPDATE or DELETE will change the target table.

Example
MERGE
	INTO customers c
	USING new_customers nc
	ON (c.id = nc.id)
	WHEN MATCHED THEN
		UPDATE SET name = nc.name
	WHEN NOT MATCHED BY SOURCE THEN
		DELETE

Support multiple rows for DML RETURNING

Adriano dos Santos Fernandes

Tracker ticket: #6815

In DSQL, the RETURNING clause is now able to return multiple rows for DML statements than can affect multiple rows.

See compatibility notes on RETURNING for more information.

Allow parenthesized query expressions

Adriano dos Santos Fernandes

Tracker ticket: #6740

The DML syntax was extended to allow a parenthesized query expression (select including order by, offset and fetch clauses, but without with clause) to occur where previously only a query specification (select without with, order by, offset and fetch clauses) was allowed.

This allows more expressive queries, especially in UNION statements, and offers more compatibility with statements generated by certain ORMs.

Note

Using parenthesized query expressions comes at a cost, as they consume an additional query context compared to a plain query specification. The maximum number of query contexts in a statement is 255.
Example
(
  select emp_no, salary, 'lowest' as type
  from employee
  order by salary asc
  fetch first row only
)
union all
(
  select emp_no, salary, 'highest' as type
  from employee
  order by salary desc
  fetch first row only
);

Support PLAN and ORDER BY on MERGE

The MERGE statement now supports the PLAN and ORDER BY clauses.

Syntax
MERGE INTO target [[AS] target_alias]
  USING <source> [[AS] source_alias]
  ON <join_condition>
  <merge_when> [<merge_when> ...]
  [PLAN <plan-expr>]
  [ORDER BY <ordering-list>]
  [RETURNING <returning_list> [INTO <variables>]]

Support PLAN, ORDER BY and ROWS on UPDATE OR INSERT

The UPDATE OR INSERT statement now supports the PLAN, ORDER BY and ROWS clauses.

Syntax
UPDATE OR INSERT INTO
  target [(<column_list>)]
  [<override_opt>]
  VALUES (<value_list>)
  [MATCHING (<column_list>)]
  [PLAN <plan-expr>]
  [ORDER BY <ordering-list>]
  [ROWS <m> [TO <n>]]
  [RETURNING <returning_list> [INTO <variables>]]

OPTIMIZE FOR Clause

Dmitry Yemanov

SELECT statements now support the OPTIMIZE FOR clause.

Syntax
SELECT
  ...
  [WITH LOCK [SKIP LOCKED]]
  [OPTIMIZE FOR {FIRST | LAST} ROWS]

The OPTIMIZE FOR clause can only occur on a top-level SELECT.

This feature allows the optimizer to consider another (hopefully better) plan if only a subset or rows is fetched initially by the user application (with the remaining rows being fetched on demand), thus improving the response time.

It can also be specified at the session level using the SET OPTIMIZE management statement.

The default behaviour can be specified globally using the OptimizeForFirstRows setting in firebird.conf or databases.conf.

Changes to literals

Full SQL standard character string literal syntax

Adriano dos Santos Fernandes

Tracker ticket: #5589

The syntax of character string literals was changed to support the full SQL standard syntax. This means a literal can be “interrupted” by whitespace or a comment. This can be used, for example, to break up a long literal over several lines, or provide inline comments.

<character string literal> ::=
  [ <introducer> <character set specification> ]
    <quote> [ <character representation>... ] <quote>
    [ { <separator> <quote> [ <character representation>... ] <quote> }... ]

<separator> ::=
  { <comment> | <white space> }...
— ISO/IEC 9075-2:2016 SQL - Part 2: Foundation
Examples
-- whitespace between literal
select 'ab'
       'cd'
from RDB$DATABASE;
-- output: 'abcd'

-- comment and whitespace between literal
select 'ab' /* comment */ 'cd'
from RDB$DATABASE;
-- output: 'abcd'

Full SQL standard binary string literal syntax

Adriano dos Santos Fernandes

Tracker ticket: #5588

The syntax of binary string literals was changed to support the full SQL standard syntax. This means a literal can contain spaces to separate hexadecimal characters, and it can be “interrupted” by whitespace or a comment. This can be used, for example, to make the hex string more readable by grouping characters, or to break up a long literal over several lines, or provide inline comments.

<binary string literal> ::=
  X <quote> [ <space>... ] [ { <hexit> [ <space>... ] <hexit> [ <space>... ] }... ] <quote>
    [ { <separator> <quote> [ <space>... ] [ { <hexit> [ <space>... ]
    <hexit> [ <space>... ] }... ] <quote> }... ]
— ISO/IEC 9075-2:2016 SQL - Part 2: Foundation
Examples
-- Group per byte (whitespace inside literal)
select _win1252 x'42 49 4e 41 52 59'
from RDB$DATABASE;
-- output: BINARY

-- whitespace between literal
select _win1252 x'42494e'
                 '415259'
from RDB$DATABASE;
-- output: BINARY
Note

The usage of the _win1252 introducer in above example is a non-standard extension and equivalent to an explicit cast to a CHAR of appropriate length with character set WIN1252.

Improvements to IN

Dmitry Yemanov

Processing of IN <list> predicates is now linear rather than recursive, thus no runtime stack limitations. The limit of 1500 items has been raised to 65535 items.

Lists that are known to be constant are pre-evaluated as invariants and cached as a binary search tree, making comparisons faster if the condition needs to be tested for many rows or if the value list is long.

If the list is very long or if the IN predicate is not selective, the index scan supports searching groups using the sibling pointer (i.e. horizontally) rather than searching every group from the root (i.e. vertically), thus utilizing a single index scan for the whole IN list.

New Expressions and Built-in Functions

UNICODE_CHAR and UNICODE_VAL

Adriano dos Santos Fernandes

UNICODE_CHAR

Returns the Unicode character with the specified code point.

Syntax
UNICODE_CHAR( <number> )
Note

The argument to UNICODE_CHAR must be a valid Unicode code point and not in the range of high/low surrogates (0xD800 to 0xDFFF), otherwise it throws an error.
Example
select unicode_char(x) from y;

UNICODE_VAL

Returns the Unicode code point of the first character of the specified string, or zero if the string is empty.

Syntax
UNICODE_VAL( <string> )
Example
select unicode_val(x) from y;

QUARTER added to EXTRACT, FIRST_DAY and LAST_DAY

The scalar functions EXTRACT, FIRST_DAY and LAST_DAY now support the date part QUARTER.

Example
select
  extract(quarter from date '2023-09-21') as Q,
  first_day(of quarter from date '2023-09-21') as Q_START,
  last_day(of quarter from date '2023-09-21') as Q_END
from rdb$database;

      Q     Q_START       Q_END
======= =========== ===========
      3 2023-07-01  2023-09-30