Partial indexes, PR help

[Mads Jensen]

Hi,

I've opened a PR to add support for partial indexes in Django. It's on
the right track, but I bumped into an issue that I need help with. On
PostgreSQL, the cast that's introduced when doing something like:

Q(published__gt=datetime.date(2017, 10, 1))
=>
"table"."published" > '2017-10-01'::timestamp;

is unfortunate because it turns the function mutable, and the index can
therefore not be created (exact error message can be seen in the Jenkins
output). I think that's a plausible use-case for a partial index to
limit on dates. Locally, I have two tests that filters on the "id" (I
didn't see any reason to introduce a new integer column in either of the
existing models; using "pk" instead raised a warning about the column
not being present) and one that filters on a boolean. Both were
suggestions from Markus Holtermann on what he had seen used in
production. There was already something for text strings.

My problem is that I'm a bit in the wild how to battle the lookup-cast
issue on PostgreSQL. I've already tried to study the Lookup-flow. I see
two ways:

1. Describe this short-coming well in the documentation, and leave it
like this.
2. Introduce a flag that can trigger the cast in this special case. I
didn't check to see how much of this that's public API and how much that
isn't. It's what I'd favour, but also more difficult.

There's enough time to the feature freeze of Django 2.2 to have the
first option as a last resort.

The tests are still failing, and Oracle had some transaction problem,
that I'm also very oblivious about. Again, error messages are in the
Jenkins output.

Link to the PR: https://github.com/django/django/pull/10140
--
Med venlig hilsen / Kind regards,
Mads Jensen

Saajan Fernandes: I think we forget things if there is nobody to
tell them.
-- The Lunchbox (2013)

[Christophe Pettus]

> On Jul 23, 2018, at 12:20, Mads Jensen <m...@inducks.org> wrote:
>
> Q(published__gt=datetime.date(2017, 10, 1))
> =>
> "table"."published" > '2017-10-01'::timestamp;
>
> is unfortunate because it turns the function mutable, and the index can therefore not be created (exact error message can be seen in the Jenkins output).

I think the issue is that you are mixing TIMESTAMP and TIMESTAMPTZ here

xof=# create table i (p timestamptz);
CREATE TABLE
xof=# create index on i(p) where p > '2011-01-01'::timestamp;
ERROR: functions in index predicate must be marked IMMUTABLE
xof=# create index on i(p) where p > '2011-01-01'::timestamptz;
CREATE INDEX

xof=# create table i (p timestamp);
CREATE TABLE
xof=# create index on i(p) where p > '2011-01-01'::timestamptz;
ERROR: functions in index predicate must be marked IMMUTABLE
xof=# create index on i(p) where p > '2011-01-01'::timestamp;
CREATE INDEX

--
-- Christophe Pettus
x...@thebuild.com

[Christophe Pettus]

> On Jul 23, 2018, at 13:05, Christophe Pettus <x...@thebuild.com> wrote:
>
>
>> On Jul 23, 2018, at 12:20, Mads Jensen <m...@inducks.org> wrote:
>>
>> Q(published__gt=datetime.date(2017, 10, 1))
>> =>
>> "table"."published" > '2017-10-01'::timestamp;
>>
>> is unfortunate because it turns the function mutable, and the index can therefore not be created (exact error message can be seen in the Jenkins output).
>

> I think the issue is that you are mixing TIMESTAMP and TIMESTAMPTZ here [...]

To be a bit clearer, this seems to be in effect a mixing-aware-and-unaware timestamp issue, just pushed down into PostgreSQL.