Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost when extracting epoch

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com>
Cc: Vik Fearing <vik(at)postgresfriends(dot)org>, Thomas Munro <thomas(dot)munro(at)gmail(dot)com>, Petr Fedorov <petr(dot)fedorov(at)phystech(dot)edu>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost when extracting epoch
Date: 2020-11-04 00:22:14
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-bugs pgsql-hackers

I wrote:
> * I notice that this will sometimes transform non-SQL-spec syntax
> into SQL-spec, for example ...
> I'm not sure that that satisfies the POLA. This particular case is
> especially not great, because this is really textregexsubstr() which
> is *not* SQL compatible, so the display is more than a bit misleading.

Actually, the problem there is that I made ruleutils.c willing to
reverse-list textregexsubstr() in SQL syntax, which it really shouldn't
since there is no such function per SQL. So deleting that "case" value
is enough to fix most of the problem. Still:

> ... In fact, I'd sort of argue
> that we should not force the function to be sought in pg_catalog in such
> a case either. The comments in substr_list claim that we're trying to
> allow extension functions named substring(), but using SystemFuncName is
> 100% hostile to that.

... this seems like a reasonable argument. However, in the attached
I only did that for SUBSTRING and OVERLAY. I had thought of doing
it for POSITION and TRIM, but both of those are weird enough that
allowing a "normal function call" seems error-prone. For example,
the fact that TRIM(expr_list) works out as a call to btrim() is a mess,
but I don't think we can change it. (But of course you can still call a
user-defined trim() function if you double-quote the function name.)

I did get rid of the empty variant for position_list, which AFAICS
has no value except adding confusion: there are no zero-argument
functions named "position" in pg_catalog.

I feel like this is committable at this point --- any objections?

regards, tom lane

Attachment Content-Type Size
reverse-list-special-sql-syntaxes-3.patch text/x-diff 39.2 KB

In response to


Browse pgsql-bugs by date

  From Date Subject
Next Message Liu, Huailing 2020-11-04 02:42:54 segfault with Parallel Scan
Previous Message David G. Johnston 2020-11-03 20:46:06 Re: BUG #16698: Create extension and search path

Browse pgsql-hackers by date

  From Date Subject
Next Message Soumyadeep Chakraborty 2020-11-04 00:35:00 Re: Delay of standby shutdown
Previous Message Alvaro Herrera 2020-11-03 23:56:06 Re: ALTER TABLE .. DETACH PARTITION CONCURRENTLY