Support for generated columns in postgres and mysql

[CaselIT]

Add support for specifying a column as generated by the database with GENERATED ALWAYS

Currently postgres and mysql support it (the list may not be complete)
https://www.postgresql.org/docs/12/ddl-generated-columns.html
https://dev.mysql.com/doc/refman/8.0/en/create-table-generated-columns.html

I've tried to search in the documentation and it does not seems to be a way to specify a generated column in sqlalchemy.
I've found a stack overflow answer https://stackoverflow.com/questions/57596603/sqlalchemy-mysql-column-ddl-with-generated-always-as but it does not seem to be the cleanest way to deal with it.

maybe a Generated construct used in the column with the code would be appropriate?
Something like

sa.Table('foo', meta,
  sa.Column('quantity', sa.Integer, nullable=False),
  sa.Column('price', sa.Float, nullable=False),
  sa.Column('revenue', sa.Float, Computed('quantity * price', always=True))
)

edit: updated example following #4894 (comment)

Current support and syntax seems to be:

• Postgress: supports only stored
  GENERATED ALWAYS AS ( generation_expr ) STORED
  https://www.postgresql.org/docs/12/ddl-generated-columns.html
  https://www.postgresql.org/docs/12/sql-createtable.html

• mysql | mariad db: supports both stored and virtual
  col_name data_type [GENERATED ALWAYS] AS (expr) [VIRTUAL | STORED]
  (maridadb supports also PERSISTENT, but is an alias to STORED
  https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html
  https://mariadb.com/kb/en/library/generated-columns/

• mssql: support both stored and virtual
  <computed_column_definition> ::= column_name AS computed_column_expression [ PERSISTED [ NOT NULL ] ]
  https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-2017

• oracle: support only virtual
  column_name [datatype] [GENERATED ALWAYS] AS (expression) [VIRTUAL]
  https://oracle-base.com/articles/11g/virtual-columns-11gr1

[zzzeek]

there's not and I would need PR contributions for this to happen.

is GENERATED part of the SQL standard? if not, then the keyword arguments need to be mysql_generated, postgresql_generated

[zzzeek]

if this is more like a SQL standard I might favor a real Generated construct that delivers keywords like ALWAYS and STORED based on flags

[zzzeek]

i.e. it works like CheckConstraint a bit

[CaselIT]

From wikipedia (hardly the best source) it seems that is not sql standard
https://en.wikipedia.org/wiki/Virtual_column

it seems to be supported by other db other than postgres and mysql though

[zzzeek]

so mysql and PG are..using the same syntax ? did one get it from the other?

[zzzeek]

OK per this article, oracle and SQL Server have something too.

this is enough for us to make a real construct out of this that's in Core. but, we need to work out all the syntaxes.

what we dont want to do, is implement for MySQL and PG but then not take into account Oracle or SQL Server and then we have to rework the whole thing to accomodate for whatever weird thing they want to do.

in the worst case, one or more of these DBs is doing it so differently that it just can't work. that's the problem we had with for example MERGE and also for a long time when Oracle had its own recursive query thing that was not a CTE.

[CaselIT]

I'm not sure regarding the syntax.
PG just added support in version 12, so it may have adopted the mysql / mariadb syntax

[zzzeek]

well here's oracle, this is looking like a standard syntax:

CREATE TABLE employees (
  id          NUMBER,
  first_name  VARCHAR2(10),
  last_name   VARCHAR2(10),
  salary      NUMBER(9,2),
  comm1       NUMBER(3),
  comm2       NUMBER(3),
  salary1     AS (ROUND(salary*(1+comm1/100),2)),
  salary2     NUMBER GENERATED ALWAYS AS (ROUND(salary*(1+comm2/100),2)) VIRTUAL,
  CONSTRAINT employees_pk PRIMARY KEY (id)
);

[zzzeek]

SQL Server, different, but not terrible

CREATE TABLE dbo.Products
   (
      ProductID int IDENTITY (1,1) NOT NULL
      , QtyAvailable smallint
      , UnitPrice money
      , InventoryValue AS QtyAvailable * UnitPrice
    )
;

[CaselIT]

if this is more like a SQL standard I might favor a real Generated construct that delivers keywords like ALWAYS and STORED based on flags

Yes that is probably best since there seems to be stored/virtual option (and maybe other)

I'll update the initial issue

[zzzeek]

I'd like a new construct for this..

officially, it would be Virtual(), but that seems a little awkward. Computed() seems a lot better.

Behaviorally, the construct acts a lot like a server_onupdate, except it accommodate for server_default too. this has to be worked out. when this construct is present, both of those have to be implicitly set up, as this impacts the ORM knowing it has to fetch these (e.g. server_onupdate=FetchedValue()).

Syntax would be like a CheckConstraint() to make it somewhat succinct:

class Model(Base):

    id = Column(Integer, primary_key=True)
    quantity = Column(Integer, nullable=False)
    unit_price_taxed = Column(DECIMAL(20, 4), nullable=False)
    line_price_taxed = Column(
        DECIMAL(20, 4),
        Computed("quantity * unit_price_taxed", always=True),
        index=True,
    )

above, it would raise an error to also set server_default or server_onupdate because Computed is present. sqlalchemy.schema.Computed would extend sqlalchemy.schema.FetchedValue and would be automatically assigned to server_default and server_onupdate when used.

tests go into test/sql/test_defaults.py, or alternatively a new suite test/sql/test_computed.py. these should be round trip tests for those databases that support comptued columns. then each backend dialect gets compilation tests.

dialects to support first, Postgresql and MySQL, and very hopefully Oracle and MSSQL. I can accept a PR for just the first two. For Firebird, I don't really care, that's mostly a not-supported dialect right now. This is enough of a feature that I can take over a PR and finish it up if someone wants to just put some effort into getting it started.

I didn't realize that many DBs support this by now so this is actually kind of a high priority. if there's no authors for a PR I might find time to get around to this alone.

[CaselIT]

Computed("quantity * unit_price_taxed", always=True),

I'm not sure if always if best to convey if they should be stored or not.
Maybe stored would be cleared (from what I understand a stored computed column is computed on insert/update, while a not stored one is computed in the select, like in a normal view)

As an alternative persistent as used in maria db may be clearer

There are two types of generated columns:
PERSISTENT (a.k.a. STORED): This type's value is actually stored in the table.
VIRTUAL: This type's value is not stored at all. Instead, the value is generated dynamically when the table is queried. This type is the default.

[zzzeek]

Sure, basically we need a matrix of what behaviors are selectable among these four vendors and then come up with flags + sensibele defaults

[zzzeek]

five vendors sigh...mariadb mysql postgresql oracle mssql

[zzzeek]

oracle: https://oracle-base.com/articles/11g/virtual-columns-11gr1

https://docs.microsoft.com/en-us/sql/relational-databases/tables/specify-computed-columns-in-a-table?view=sql-server-2017

[CaselIT]

seems to be:

• Postgress: STORED is required: PostgreSQL currently implements only stored generated columns.
• mysql | mariad db: [VIRTUAL | STORED] (maridadb supports also PERSISTENT, but is an alias to STORED
• mssql: PERSISTED is optional
• oracle: seems to not support stored

[zzzeek]

oracle: "If the datatype is omitted, it is determined based on the result of the expression. The GENERATED ALWAYS and VIRTUAL keywords are provided for clarity only." e.g. they dont support "persistent" . SQL Server seems to support "persisted" as an option

[zzzeek]

so what is the word "ALWAYS"? that's just boilerplate?

[CaselIT]

so what is the word "ALWAYS"? that's just boilerplate?

not sure. bot generated and always are optional in oracle, while seem required in PG

[zzzeek]

oh and PG / Oracle have mutually exclusive storage implementations. So we likely should support that too, for people who don't care if its stored or not based on backend, some kind of "auto" option

[zzzeek]

OK so it's basically like what MySQL says: "AS (expr) indicates that the column is generated and defines the expression used to compute column values. AS may be preceded by GENERATED ALWAYS to make the generated nature of the column more explicit." no biggie

[CaselIT]

also in mysql maria db generated always seems to optional

[zzzeek]

weird this is not SQL standard? it is sure acting like it

[CaselIT]

regarding PG it seems that it will eventually support virtual columns
https://www.postgresql.org/message-id/57c70b3e-b29a-ad08-cdce-746f6d561e3d%402ndquadrant.com

should sqlalchemy raise an error if persisted=False or suppose that PG will eventually add it and let the db raise the error in case the user uses a version that does not support it?

[zzzeek]

so SQL 2003 includes "GENERATED AS" in the spec specifically for identity columns. so it's in the spec, but the Wikipedia entry refers to "virtual columns" which aren't really stored, so that's the part that's not in the spec.

anyway we are good to go on this thanks for being involved!

<column definition> ::=
<column name> [ <data type or domain name> ]
[ <default clause> | <identity column specification> | <generation clause> ]
[ <column constraint definition>... ]
[ <collate clause> ]
<data type or domain name> ::=
<data type>
| <domain name>
<column constraint definition> ::=
[ <constraint name definition> ] <column constraint> [ <constraint characteristics> ]
<column constraint> ::=
NOT NULL
| <unique specification>
| <references specification>
| <check constraint definition>
<identity column specification> ::=
GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY
[ <left paren> <common sequence generator options> <right paren> ]
<generation clause> ::= <generation rule> AS <generation expression>
<generation rule> ::= GENERATED ALWAYS
<generation expression> ::= <left paren> <value expression> <right paren>

[CaselIT]

It seems that mysql supports not null with a generated column while mariadb does not

should I add checks in the dialect or demand them to the db?

[CaselIT]

mssql does not accept the type on a computed colum. does not allow null but allows not null on persisted columns.

[zzzeek]

mssql's type is likely..derived from the expression? I think for MSSQL we just omit the type when we render. for null/not null, again if we can get by without rendering anything for "null" / "not null" by default, that would be best.

I saw you mention you were calling it "Computed". Should we call it "Generated"? since that's what the SQL looks like, it might be clearest.

[CaselIT]

already renamed in the pr. I can revert it
for me its equally clear. maybe generated is more like generate of sql?

regarding the not null there the default (ie omitting the nullable should always work)

[zzzeek]

it's a hard call because Computed describes it better but Generated is more what the SQL looks like. just leave it for now whichever way you have it

[CaselIT]

mssql's type is likely..derived from the expression? I think for MSSQL we just omit the type when we render

Yes it infers it. But it is strange that it errors if the type is specified. Oracle also infers it but does not error if the type is specified (maybe if it is not compatible?).
I have omitted the type in mssql when the colum is computed

[CaselIT]

I've done a fist implementation. I'm not sure if the inspect module needs to be updated or how to update it.

[sqla-tester]

CaselIT has proposed a fix for this issue in the master branch:

Support for generated columns https://gerrit.sqlalchemy.org/1539

[CaselIT]

I'm not sure if we want to close this after #4928 is merged or if it should stay open since the orm support is still missing

[zzzeek]

I'm adding the ORM test, that just tests that the unit of work recognizes the Computed as a server / on update default, to the current gerrit. The only thing I see beyond that is the flag that gets into "this is a read only attribute" and I think as that is more of a behavioral change that should also impact any expression level column_property(), it should likely be a separate issue and maybe targeted at 1.4. the basic ability for you to use computed columns including that the ORM does the right thing (except for if you write to the column in Python) will be there in 1.3.x with the current PR.

[CaselIT]

I started something regarding the read only attributes in #4896 if you want to take a look.

I don't remember if I also added orm tests there.

I agree that it could be done in a separate issue

[zzzeek]

well next bad news, Oracle RETURNING does not work for computed columns. easiest approach right now is add another supports to the tests and eager_defaults with Oracle + Computed just won't work.

[zzzeek]

though need to see what PG does

[zzzeek]

pg 12 works. OK

[CaselIT]

I guess changing oracle to the MySQL behavior in case of computed columns would not be simple

[zzzeek]

oracle is really bad. the RETURNING returns the value before the UPDATE happens. so it's not only wrong it's misleading. im having it emit a warning.

[CaselIT]

that seems like it was implemented to intentionally cause problems!

[zzzeek]

the latest gerrit has a lot of oracle additions if you want to look.l

[CaselIT]

Nice, since the orm test from the #4896 are in this issue, I think we can probably close that and open a new pr with only the read only attributes parts in it

[sqla-tester]

CaselIT has proposed a fix for this issue in the rel_1_3 branch:

Support for generated columns https://gerrit.sqlalchemy.org/1559