From: Tom Lane <tgl@sss.pgh.pa.us>
To: Anastasia Lubennikova <a.lubennikova@postgrespro.ru>
cc: Robert Haas <robertmhaas@gmail.com>,
        PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: Proposal: Automatic partition creation
In-reply-to: <fa51efc3-33a9-ec85-3dba-61dbda34be58@postgrespro.ru>
References: <7fec3abb-c663-c0d2-8452-a46141be6d4a@postgrespro.ru>
 <CA+TgmoZtcA+XvUpV_NCOieQ44SnCeprDhq_M0+bQ76E4Fxg5UQ@mail.gmail.com>
 <1378901.1594051837@sss.pgh.pa.us>
 <fa51efc3-33a9-ec85-3dba-61dbda34be58@postgrespro.ru>
Comments: In-reply-to Anastasia Lubennikova <a.lubennikova@postgrespro.ru>
	message dated "Mon, 13 Jul 2020 21:01:28 +0300"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2688627.1594666910.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Mon, 13 Jul 2020 15:01:50 -0400
Message-ID: <2688628.1594666910@sss.pgh.pa.us>
Precedence: bulk

Anastasia Lubennikova <a.lubennikova@postgrespro.ru> writes:
> On 06.07.2020 19:10, Tom Lane wrote:
>> Robert Haas <robertmhaas@gmail.com> writes:
>>> I think the big problem here is identifying the operator to use. We
>>> have no way of identifying the "plus" or "minus" operator associated
>>> with a datatype; indeed, that constant doesn't exist.

>> We did indeed solve this in connection with window functions, cf
>> 0a459cec9.  I may be misunderstanding what the problem is here,
>> but I think trying to reuse that infrastructure might help.

> Do we need to introduce a new support function? Is there a reason why we=
 =

> can not rely on '+' operator?

(1) the appropriate operator might not be named '+'
(2) even if it is, it might not be in your search_path
(3) you're vulnerable to security problems from someone capturing the
    '+' operator with a better match; since you aren't writing the
    operator explicitly, you can't fix that by qualifying it
(4) if the interval constant is written as an undecorated string
    literal, the parser may have trouble resolving a match at all

> I understand that the addition operator may lack or be overloaded for
> some complex datatypes, but I haven't found any examples that are useful
> for range partitioning.

"It works for all the built-in data types" isn't really a satisfactory
answer.  But even just in the built-in types, consider "date":

# select oid::regoperator from pg_operator where oprname =3D'+' and oprlef=
t =3D 'date'::regtype;
              oid               =

--------------------------------
 +(date,interval)
 +(date,integer)
 +(date,time without time zone)
 +(date,time with time zone)
(4 rows)

It's not that immediately obvious which of these would make sense to use.

But the short answer here is that we did not accept relying on '+' being
the right thing for window function ranges, and I don't see why it is more
acceptable for partitioning ranges.  The existing places where our parser
relies on implicit operator names are, without exception, problematic [1].

			regards, tom lane

[1] https://www.postgresql.org/message-id/flat/ffefc172-a487-aa87-a0e7-472=
bf29735c8%40gmail.com