Re: Mysteries of the future

[Tom Lane]

"David G. Johnston"  writes:
> The request is to fix our documentation to use a valid date for the example
> in the paragraph that describes the separator requirement for years greater
> than 4 digits.

Oh!  Got it, that should be fixed.

regards, tom lane

Re: Mysteries of the future

[David G. Johnston]

On Thu, Apr 11, 2024 at 7:20 AM Tom Lane  wrote:

> PG Doc comments form  writes:
> > SELECT to_date('2-1131', '-MMDD');
> > ERROR:  22008: date/time field value out of range: "2-1131"
>
> What exactly do you find wrong with that?  November doesn't have
> 31 days.
>
> Sure, we could have a discussion about the probability of the
> Gregorian calendar still being in use 18000 years from now,
> but it doesn't seem very profitable.  What else do you want
> to use?
>

The request is to fix our documentation to use a valid date for the example
in the paragraph that describes the separator requirement for years greater
than 4 digits.

In to_timestamp and to_date, the  conversion has a restriction when
processing years with more than 4 digits. You must use some non-digit
character or template after , otherwise the year is always interpreted
as 4 digits. For example (with the year 2): to_date('21131',
'MMDD') will be interpreted as a 4-digit year; instead use a non-digit
separator after the year, like to_date('2-1131', '-MMDD') or
to_date('2Nov31', 'MonDD').

David J.

Re: Mysteries of the future

[Tom Lane]

PG Doc comments form  writes:
> SELECT to_date('2-1131', '-MMDD');
> ERROR:  22008: date/time field value out of range: "2-1131"

What exactly do you find wrong with that?  November doesn't have
31 days.

Sure, we could have a discussion about the probability of the
Gregorian calendar still being in use 18000 years from now,
but it doesn't seem very profitable.  What else do you want
to use?

regards, tom lane

Postgres_FDW doc doesn't specify TYPE support in Remote Execution Options

[PG Doc comments form]

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/16/postgres-fdw.html
Description:

Hi,
The
https://www.postgresql.org/docs/current/postgres-fdw.html#POSTGRES-FDW-OPTIONS-REMOTE-EXECUTION
section mentions for `extensions` configuration option:
> Functions and operators that are immutable and belong to a listed
extension will be considered shippable to the remote server.

But doesn't mention that extensions' custom data types will be pushed down
as well! I feel it's important and worth mentioning.
I noticed that the lookup_shippable() function in shippable.c file does
mention that:
> Returns true if given object (operator/function/type) is shippable
according to the server options.
Src:
https://github.com/postgres/postgres/blob/master/contrib/postgres_fdw/shippable.c#L108


Can you please update the official docs to call out about types? Thank you!
:)

Re: Postgres_FDW doc doesn't specify TYPE support in Remote Execution Options

[Rajan Pandey]

Hi, please ignore my last email about the patch. :/

I have determined that pushdown of custom data types through fdw in fact
does make sense.

Consider this query:
SELECT * FROM customers WHERE 
 ;

To push down the WHERE clause to the foreign servers, both local and
foreign server must have the user_defined database objects (else the query
won't work on the foreign servers).
For built-in types, operators, functions, Postgres knows that pushdown is
safe as the foreign servers the db objects. But for the user_defined db
objects, postgres won't know. Hence, user_defined_type_col needs to be
pushed down explicitly.


On Tue, Apr 9, 2024 at 8:06 PM Rajan Pandey 
wrote:

> Hi team .
>
> In the
> https://www.postgresql.org/docs/current/postgres-fdw.html#POSTGRES-FDW-OPTIONS-REMOTE-EXECUTION
>  doc, it
> mentions that Immutable Functions and Hi can be pushed down using
> `extensions` option for foreign server.
>
> But it does not mention TYPE. In the shippable.c
> /lookup_shippable()
> function, I found a comment that indicates that type is also pushed down.
> Hece, I have removed TYPE from the comments, assuming that it does not make
> sense to push down a data type, and only functions and operations can be
> pushed down. Please let me know if my assumption is incorrect. 
>
> I have added my patch file with the mail. Thanks!
>
> On Tue, Apr 9, 2024 at 4:06 PM Laurenz Albe 
> wrote:
>
>> On Tue, 2024-04-09 at 15:49 +0530, Rajan Pandey wrote:
>> > I was reading
>> https://www.postgresql.org/docs/current/postgres-fdw.html#POSTGRES-FDW-OPTIONS-REMOTE-EXECUTION
>> > and found that it mentions that Immutable Functions and Operators can
>> > be pushed down using `extensions` option for foreign server.
>> >
>> > But it does not mention about TYPE. In
>> the shippable.c/lookup_shippable()
>> > function, I found that type is also pushed down.
>>
>> The comment only says that data types may be shippable, but not that
>> they are actually shipped.  Can you think of a case where a data type
>> would be shipped to a foreign server?  I wrote a foreign data wrapper,
>> and I cannot think of such a case.
>>
>> Perhaps the function comment should be adjusted by removing the
>> parenthesis
>> or changing it to "(operator/function/...)".
>>
>> > Does this require updating the docs? Can I raise a PR to do so? Thank
>> you! :)
>>
>> You would send a patch against the "master" branch to the pgsql-docs list
>> for that.
>>
>> Yours,
>> Laurenz Albe
>>
>
>
> --
> Regards
> Rajan Pandey
>


-- 
Regards
Rajan Pandey

Mysteries of the future

[PG Doc comments form]

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/16/functions-formatting.html
Description:

I'm not sure that there will be 31 days in 11 months; although postgresql
probably knows better what will happen in 2 

For example (with the year 2): to_date('21131', 'MMDD') will be
interpreted as a 4-digit year; instead use a non-digit separator after the
year, like to_date('2-1131', '-MMDD') or to_date('2Nov31',
'MonDD').

https://www.postgresql.org/docs/current/functions-formatting.html

```sql
SELECT to_date('2-1131', '-MMDD');

ERROR:  22008: date/time field value out of range: "2-1131"
LOCATION:  DateTimeParseError, datetime.c:4021
```