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-02 22:50:08
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-bugs pgsql-hackers

I wrote:
> Attached is a draft patch that does this. I'm fairly pleased with it,
> but there are some loose ends as described below. As the patch stands,
> it reverse-lists all our special-format function call syntaxes
> *except* EXTRACT. I left that out since I think we want to apply the
> reverse-listing change when we add the numeric-output extraction
> functions, as I said upthread.

> The main thing that's incomplete here is that the switch on function
> OID fails to cover some cases that ought to be covered, as a result
> of limitations of

Now that 8e1f37c07 fixed that, here's a complete version, with better
test coverage. (I still think we might want to rewrite those SQL
functions as C, but that can be an independent project now.)

Remaining open issues:

* I notice that this will sometimes transform non-SQL-spec syntax
into SQL-spec, for example

# explain verbose select substring(now()::text, 'foo');
Result (cost=0.00..0.02 rows=1 width=32)
Output: SUBSTRING((now())::text FROM 'foo'::text)
(2 rows)

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.
The reason this happens is that we've included expr_list as a variant of
substr_list, so that the func_expr_common_subexpr production has no idea
whether the argument list was really special syntax or not. What I'm
inclined to do, but have not done yet, is to split that apart into
separate variants so that when the SQL-spec decoration is not used we
just generate a perfectly vanilla FuncCall. 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.

* Still waiting for comments on whether to rename CoercionForm.

regards, tom lane

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

In response to


Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2020-11-02 23:55:20 Re: BUG #16695: pg_hba_file_rules NULL address and netmask
Previous Message Peter Vandivier 2020-11-02 22:26:25 Re: BUG #16695: pg_hba_file_rules NULL address and netmask

Browse pgsql-hackers by date

  From Date Subject
Next Message Tomas Vondra 2020-11-02 23:25:45 Re: WIP: BRIN multi-range indexes
Previous Message Peter Geoghegan 2020-11-02 21:05:04 Re: Delaying/avoiding BTreeTupleGetNAtts() call within _bt_compare()