Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Support GENERATED ALWAYS identity columns and OVERRIDE clause [CORE5463] #5733

Closed
firebird-automations opened this issue Jan 19, 2017 · 11 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @asfernandes

Is related to CORE5460

Attachments:
overriding-system-value-error-message-is-cuted.png

Quoting SQL standard of INSERT command:

7) If the <insert column list> is omitted, then an <insert column list>
that identifies all columns of T in the
ascending sequence of their ordinal positions within T is implicit.

10) If <contextually typed table value constructor> CTTVC is specified, then every <contextually typed row
value constructor element> simply contained in CTTVC whose positionally corresponding <column name>
in <insert column list> references a column of which some underlying column is a generated column shall
be a <default specification>.

11) Case:

b) If for some n, some underlying column of the column referenced by the <column name> CN contained
in the n-th ordinal position in <insert column list> is an identity column, system-time period start
column, or system-time period end column whose descriptor includes an indication that values are
always generated, then

Case:
i) If <from subquery> is specified, then <override clause> shall be specified.

iii) If the n-th <contextually typed row value constructor element> simply contained in any <contextually
typed row value constructor> simply contained in the <contextually typed table value
constructor> is not a <default specification>, then <override clause> shall be specified.

NOTE 516 — The preceding subrule does not cover all possibilities. The remaining possibilities are where <default
specification> is specified for every identity column, or for a system-time period start column or system-time period
end column, in which case it is immaterial whether <override clause> is specified or not.

c) If for some n, some underlying column of the column referenced by the <column name> CN contained
in the n-th ordinal position in <insert column list> is an identity column whose descriptor includes an
indication that values are generated by default, then if <override clause> is specified, then <override
clause> shall specify OVERRIDING USER VALUE.

e) Otherwise, <override clause> shall not be specified.

Commits: 9517964 3ca6fc1

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

Link: This issue is related to CORE5460 [ CORE5460 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

summary: Support GENERATED ALWAYS identity columns => Support GENERATED ALWAYS identity columns and OVERRIDE clause

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 4.0 Alpha 1 [ 10731 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

Note at line after 'Statement failed, SQLSTATE = 42000' in the following test:

C:\TEMP>isql /:e40
Database: /:e40, User: SYSDBA
SQL> recreate table test(id int generated ALWAYS as identity);
SQL> commit;
SQL> insert into test(id) values(1);
Statement failed, SQLSTATE = 42000
OVERRIDING SYSTEM VALUE should be used to override the value of an identity column defined as 'GENERATED ALWAYS' in ta
SQL> show version;
ISQL Version: WI-T4.0.0.546 Firebird 4.0 Unstable
Server version:
Firebird/Windows/Intel/i386 (access method), version "WI-T4.0.0.546 Firebird 4.0 Unstable"
...

Message "OVERRIDING SYSTEM VALUE should be used <. . .> in ta" is cuted. See also attached screen.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

Attachment: overriding-system-value-error-message-is-cuted.png [ 13074 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: No test => Done successfully

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

It's reported in the feature commit message. It seems a general problem for longer messages and I didn't looked at it yet.

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

$FB_HOME/doc\/sql.extensions/README.identity_columns.txt says:

Implementation:
Two columns have been inserted in RDB$RELATION_FIELDS: RDB$GENERATOR_NAME and RDB$IDENTITY_TYPE.
RDB$GENERATOR_NAME stores the automatically created generator for the column <...>

Consider following:

SQL> set list on;
SQL> recreate table test(id int generated BY DEFAULT as identity unique);
SQL> insert into test default values returning id;

ID 1 -- Ok, expected

SQL> insert into test(id) values(100);
SQL> insert into test(id) values(101);
SQL> insert into test(id) values(102);
SQL> insert into test default values returning id;

ID 5 -- Why ??

For what engine touches automatically created generator of field ID when value for it was explicitly provided ?

Beside that this is unexpected result (at least for me) it means that generators page will be involved in low-level locking every time we do bulk inserts in such table.
Currently this can be avoided by calling gen_id( some_gen, 1000 ) and then giving (inside loop) value-by-value but from already reserved serie rather than call gen_id() 1000 times.

PS. For those who do not know: all (or almost all) generators are stored in one, single database page ==> each gen_id() for each generator requires low-level serialization.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

> ID 5 -- Why ??

Fixed.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants