Proposal for an "Age" PostgreSQL ORM function

182 views
Skip to first unread message

Niccolò Mineo

unread,
Jan 17, 2023, 11:11:37 AM1/17/23
to Django developers (Contributions to Django itself)
How would you see adding the "AGE" function to the current set of PostgreSQL ORM functions?

Jason Johns

unread,
Jan 19, 2023, 3:14:43 PM1/19/23
to Django developers (Contributions to Django itself)
the AGE function takes in two timestamps and returns an interval.  You can do this in python by subtracting two date/datetime objects and getting a timedelta.  what would the difference be to kick this out to the db?

Jörg Breitbart

unread,
Jan 20, 2023, 7:44:57 AM1/20/23
to django-d...@googlegroups.com


Am 19.01.23 um 21:14 schrieb Jason Johns:
> the AGE function takes in two timestamps and returns an interval.  You
> can do this in python by subtracting two date/datetime objects and
> getting a timedelta.  what would the difference be to kick this out to
> the db?

I'd say thats mainly about performance - the DB can almost almost always
calculate things much more efficiently, eg. for AGE with table data:
- less datapoints to be moved to client side (should run ~2x faster
here, if the mangling of datetime and range type is equally expensive)
- Ideally the db has a fast impl for the function, thus the calculation
loop would also run faster than in python / on django side.

Since I have not benchmarked AGE this is all speculative (TM), but there
is a good chance to get at least a 2x faster processing.

Furthermore this is also about SQL realms vs. client roundtrips &
deduplication - a value calculated on python side cannot be used in SQL
directly anymore, you either need another python roundtrip to calc the
values and put it back to DB side or some data duplication on a table
(eww, now you've violated the normalization rules).

Still all that is not yet a good indicator, whether AGE should be
supported natively, as there is more to it from ORM perspective:
- Is AGE a vivid use case, does it help django users to get things done?
- Whats the testing/maintenance burden of AGE?
- How about other DB vendors?

Now to the downsides of AGE in postgres itself - it creates a "symbolic"
range string in years|months|days, rather than in postgres' standard
interval repr. This might create frictions at the interval to timedelta
translation in psycopg*, at least needs extensive testing for edge
cases, that might occur in that weird "symbolic" notation. That
uncertainty is a very bad startpoint for an ORM to get reliable
functionality and def. raises the test/maintenance needs just for that
AGE function.

An in-depth analysis of all these points might reveal, that its more
reliable to just use datetime substraction at SQL level. Which is
already easy doable and prolly also works with many other DB vendors.
Which again somewhat questions the idea for native postgres AGE support.

In summary - I am not against an ORM age thingy, still think that a more
general impl covering other vendors as well might be more helpful.

Cheers,
Jerch


[*] Again speculative (TM) - I know that Daniele Varrazzo does a really
great job in maintaining psycopg and it prolly will just work as
expected. It still creates a dependency on a less often used/tested code
path, so needs more caution on ORM side.

Carlton Gibson

unread,
Jan 20, 2023, 8:35:56 AM1/20/23
to Django developers (Contributions to Django itself)
From the maintenance perspective, it's not that any one individual function is too hard to maintain — it's clearly not. 

Rather it's about not adding a million of them — which would add up — and in so doing essentially duplicating the entire API offered by databases. 

* Each one we add makes the docs longer, and less fun to read. Every user has to answer (for each one) "What's this?" and "Is it relevant to me?"
* Once you get beyond the core functions they impose that cost with less and less likelihood of being relevant. 
* And the function wrappers are in the main really quite trivial to add to your own project if you want them. 

Previous discussions have more or less pointed to the core cases being covered, and not adding a whole lot more. 
(There's always the possibility that one extra **is** worth adding, but …) 

One question:

> the function wrappers are in the main really quite trivial to add to your own project if you want them. 

Can we make the docs point this out more clearly? 

Like custom fields, custom functions shouldn't be something folks shy away from. 🤔

Kind Regards,

Carlton

Adam Johnson

unread,
Jan 21, 2023, 4:53:35 AM1/21/23
to django-d...@googlegroups.com
+1 to what Carlton said. You can make this function in your project like so:

class Age(Func):
    function = "AGE"

A Django wrapper would actually be more complicated. You’d need to understand both the PostgreSQL and Django function docs, and you might not be sure Django does anything special besides call the SQL function.

--
You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-develop...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/a46517dd-a03f-47e6-bdd6-4a052e6bdaecn%40googlegroups.com.

Jörg Breitbart

unread,
Jan 24, 2023, 5:08:30 AM1/24/23
to django-d...@googlegroups.com


Am 21.01.23 um 10:53 schrieb 'Adam Johnson' via Django developers
(Contributions to Django itself):
> A Django wrapper would actually be more complicated. You’d need to
> understand both the PostgreSQL and Django function docs, and you might
> not be sure Django does anything special besides call the SQL function.

Well since this was posted in dev mailing list, thats exactly my
argument - if datetime range calculations like done with AGE is seen as
useful in general, it should be done for all db vendors.
But thats much more involved and prolly needs as_db_xy() overloads with
correct type coersion, so not so easy to extend on individual project
basis (except for postgres, where it is 2 lines away).
Reply all
Reply to author
Forward
0 new messages