Opened 4 years ago

Closed 8 months ago

Last modified 7 months ago

#31300 closed New feature (fixed)

Add function-based virtual fields.

Reported by: Dulmandakh Owned by: Jeremy Nauta
Component: Database layer (models, ORM) Version: dev
Severity: Normal Keywords: field, database, generated
Cc: Petr Přikryl, elonzh, Lily Foote, Paolo Melchiorre, julian@pinabausch.org Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

PostgreSQL 12 added support for Generated Columns, see https://www.postgresql.org/docs/12/ddl-generated-columns.html. And I find it very interesting and useful, for example with SearchVectorField.

I imagine it would be called GeneratedField and accept base_field as ArrayField, then expression to generate a value for the field. For example,

class Album(models.Model):
  ...
  title = models.CharField(max_length=120)
  search = GeneratedField(
    SearchVectorField(),
    F('title')
  )

then generate SQL something like below.

CREATE TABLE album (
    ...
    title char(120),
    search tsvector GENERATED ALWAYS AS title STORED
);

I would like to work on this feature, but don't know how to pass expression and generate migration using the expression.

Change History (23)

comment:1 by Mariusz Felisiak, 4 years ago

Resolution: wontfix
Status: newclosed
Summary: Add Generated Columns (PostgreSQL) supportAdd function-based virtual fields on PostgreSQL and Oracle.

We have an accepted ticket #30511 about identity columns on PostgreSQL, and I think we should stay with this. Generated/Function-based virtual columns are a huge topic that would require many changes and have many caveats, e.g. functions must be deterministic. They are feasible also on Oracle. This kind of features require few weeks (even months) of works, a precise plan, and should be preceded by a discussion on DevelopersMailingList and even DEP.

comment:2 by Petr Přikryl, 3 years ago

Cc: Petr Přikryl added

comment:3 by Mariusz Felisiak, 17 months ago

Resolution: wontfix
Status: closednew
Summary: Add function-based virtual fields on PostgreSQL and Oracle.Add function-based virtual fields.
Triage Stage: UnreviewedAccepted

Tentatively accepted, based on the feedback from the mailing list.

comment:4 by Mariusz Felisiak, 17 months ago

Has patch: set
Owner: changed from nobody to Jeremy Nauta
Patch needs improvement: set
Status: newassigned

comment:5 by elonzh, 16 months ago

Cc: elonzh added

comment:6 by Paolo Melchiorre, 14 months ago

Patch needs improvement: unset

comment:7 by Mariusz Felisiak, 14 months ago

Patch needs improvement: set

comment:8 by Jeremy Nauta, 14 months ago

Patch needs improvement: unset

comment:9 by Lily Foote, 11 months ago

Cc: Lily Foote added

comment:10 by Paolo Melchiorre, 11 months ago

Cc: Paolo Melchiorre added
Keywords: field database generated added

comment:11 by Jeremy Nauta, 10 months ago

Triage Stage: AcceptedReady for checkin

comment:12 by Mariusz Felisiak, 10 months ago

Triage Stage: Ready for checkinAccepted

Jeremy, you cannot mark your own patches as RFC, check out docs.

comment:13 by Paolo Melchiorre, 10 months ago

Triage Stage: AcceptedReady for checkin

Mariusz if I have interpreted the documentation correctly, having just reviewed it again and not being the author of the PR, I have marked it as an RFC.

comment:14 by Lily Foote, 10 months ago

One thing that might help this land is review of the Oracle support I added in https://github.com/django/django/pull/16860. There's a few open questions that could use thought from more people.

comment:15 by Paolo Melchiorre, 10 months ago

I personally can't help with Oracle testing, as I believe many others here, but if there weren't other people able to help, as Adam has already suggested [1] in the PR comments, I wouldn't block the merge of this feature because it lacks the Oracle support given how little it is used.

[1] https://github.com/django/django/pull/16417#issuecomment-1508222534

comment:16 by julian@pinabausch.org, 9 months ago

Cc: julian@pinabausch.org added

comment:17 by Mariusz Felisiak, 9 months ago

Patch needs improvement: set
Triage Stage: Ready for checkinAccepted

comment:18 by Mariusz Felisiak, 8 months ago

Patch needs improvement: unset
Triage Stage: AcceptedReady for checkin

comment:19 by Mariusz Felisiak <felisiak.mariusz@…>, 8 months ago

Resolution: fixed
Status: assignedclosed

In f333e35:

Fixed #31300 -- Added GeneratedField model field.

Thanks Adam Johnson and Paolo Melchiorre for reviews.

Co-Authored-By: Lily Foote <code@…>
Co-Authored-By: Mariusz Felisiak <felisiak.mariusz@…>

comment:20 by GitHub <noreply@…>, 8 months ago

In 0b506bf:

Refs #31300 -- Added example to GeneratedField release notes.

comment:21 by Mariusz Felisiak <felisiak.mariusz@…>, 8 months ago

In 7e4c1e8:

[5.0.x] Refs #31300 -- Added example to GeneratedField release notes.

Backport of 0b506bfe1ab9f1c38e439c77b3c3f81c8ac663ea from main

comment:22 by Mariusz Felisiak <felisiak.mariusz@…>, 7 months ago

In 208870b:

Refs #31300 -- Allowed testing GeneratedField without collation.

CockroachDB and Snowflake don't support it.

comment:23 by Mariusz Felisiak <felisiak.mariusz@…>, 7 months ago

In b735b90:

[5.0.x] Refs #31300 -- Allowed testing GeneratedField without collation.

CockroachDB and Snowflake don't support it.

Backport of 208870b6122c4cbc39c3b2432e13db54c920db51 from main

Note: See TracTickets for help on using tickets.
Back to Top