Re: Can we get rid of repeated queries from pg_dump?

[hubert depesz lubaczewski]

On Wed, Oct 20, 2021 at 05:46:01PM -0400, Tom Lane wrote:
> I wrote:
> > Anyway, it doesn't look like there's much hope of improving this
> > aspect without a significant rewrite.
> 
> Just to close out this thread: I've now posted such a rewrite at
> https://www.postgresql.org/message-id/2273648.1634764485%40sss.pgh.pa.us

That looks amazing. Thanks a lot.

Best regards,

depesz

Re: Can db user change own password?

[Toomas]

Hi,

Basically the case was, when session_user != current_user then command 
\password failed with error message “ERROR:  permission denied”. All was good 
when session_user == current_user.

In terms of statement “session_user user was set as the owner of the database 
automatically” - I have a setup where session_user is changed automatically as 
database owner when user logs into database.

BR,
Toomas

> On 20. Oct 2021, at 18:43, Vijaykumar Jain  
> wrote:
> 
> 
> 
> On Wed, 20 Oct 2021 at 20:52, Adrian Klaver  > wrote:
> On 10/20/21 08:07, Toomas wrote:
> > Hi Adrian,
> > 
> > Thank you for your help. The issue was that when user logged into database 
> > his session_user user was set as owner of database automatically. User had 
> > success to change password when session_user = current_user was set before.
> 
> I'm not understanding. You will need to sketch this out:
> 
> 1) Connection parameters for log in with .
> 
> 2) On log in the output from: select session_user, current_user;
> 
> 3) Define '...set as owner of database automatically'.
> 
> 
> Toomas, 
> things work for me as expected. 
> 
> I guess as asked, you may want to show an example for your below statement to 
> help understand better.
> "The issue was that when a user logged into the database his session_user 
> user was set as the owner of the database automatically."
> 
> 
> postgres@u1:~$ psql
> psql (12.8 (Ubuntu 12.8-0ubuntu0.20.04.1))
> Type "help" for help.
> 
> postgres=# \du
>List of roles
>  Role name | Attributes | 
> Member of
> ---++---
>  postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
> 
> postgres=# create role vijay login nosuperuser password '1234';
> CREATE ROLE
> postgres=# grant CONNECT on database postgres to vijay;
> GRANT
> postgres=# \q
> postgres@u1:~$ psql -U vijay -p 5432 -d postgres -h 127.0.0.1
> Password for user vijay:
> psql (12.8 (Ubuntu 12.8-0ubuntu0.20.04.1))
> SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, 
> compression: off)
> Type "help" for help.
> 
> postgres=> \password
> Enter new password:
> Enter it again:
> postgres=> \q
> postgres@u1:~$ psql -U vijay -p 5432 -d postgres -h 127.0.0.1  # old password
> Password for user vijay:
> psql: error: FATAL:  password authentication failed for user "vijay"
> FATAL:  password authentication failed for user "vijay"
> postgres@u1:~$ psql -U vijay -p 5432 -d postgres -h 127.0.0.1 # new password
> Password for user vijay:
> psql (12.8 (Ubuntu 12.8-0ubuntu0.20.04.1))
> SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, 
> compression: off)
> Type "help" for help.
> 
> postgres=> \q
>  
> postgres@u1:~$ psql -U vijay -p 5432 -d postgres -h 127.0.0.1
> Password for user vijay:
> psql (12.8 (Ubuntu 12.8-0ubuntu0.20.04.1))
> SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, 
> compression: off)
> Type "help" for help.
> 
> postgres=> select session_user, current_user;
>  session_user | current_user
> --+--
>  vijay| vijay
> (1 row)
> 
> postgres=> \password
> Enter new password:
> Enter it again:
> postgres=> alter role vijay password '666'; -- trying both ways, works
> ALTER ROLE
> postgres=> \q
> postgres@u1:~$ psql -U vijay -p 5432 -d postgres -h 127.0.0.1
> Password for user vijay:
> psql (12.8 (Ubuntu 12.8-0ubuntu0.20.04.1))
> SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, 
> compression: off)
> Type "help" for help.
> 
> postgres=> select session_user, current_user;
>  session_user | current_user
> --+--
>  vijay| vijay
> (1 row)
> 
> 
> postgres@u1:~$ psql
> psql (12.8 (Ubuntu 12.8-0ubuntu0.20.04.1))
> Type "help" for help.
> 
> postgres=# create database vijay owner vijay;
> CREATE DATABASE
> postgres=# \q
> postgres@u1:~$ psql -U vijay -p 5432 -d vijay -h 127.0.0.1
> Password for user vijay:
> psql (12.8 (Ubuntu 12.8-0ubuntu0.20.04.1))
> SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, 
> compression: off)
> Type "help" for help.
> 
> vijay=> select session_user, current_user;
>  session_user | current_user
> --+--
>  vijay| vijay
> (1 row)
> 
> vijay=> alter role vijay password '999'; -- trying both ways, works
> ALTER ROLE
> vijay=> \q
> postgres@u1:~$ psql -U vijay -p 5432 -d vijay -h 127.0.0.1
> Password for user vijay:
> psql (12.8 (Ubuntu 12.8-0ubuntu0.20.04.1))
> SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, 
> compression: off)
> Type "help" for help.
> 
> vijay=> \q

Re: Can db user change own password?

[Adrian Klaver]

On 10/21/21 08:39, Toomas wrote:

Hi,

Basically the case was, when *session_user != current_user* then command 
\password failed with error message “ERROR:  permission denied”. All was 
good when session_user == current_user.


In terms of statement “session_user user was set as the owner of the 
database automatically” - I have a setup where session_user is changed 
automatically as database owner when user logs into database.


What I am looking for is a step by step outline that approximates:

1) psql -U 

2) The command(s) that set session_user as database owner.

3) The command(s) that make session_user != current_user.

4) Output of:
 select session_user, current_user;

5) The output of \du for the users involved



BR,
Toomas






--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Can db user change own password?

[Tom Lane]

Adrian Klaver  writes:
> On 10/21/21 08:39, Toomas wrote:
>> Basically the case was, when *session_user != current_user* then command 
>> \password failed with error message “ERROR:  permission denied”. All was 
>> good when session_user == current_user.
>> 
>> In terms of statement “session_user user was set as the owner of the 
>> database automatically” - I have a setup where session_user is changed 
>> automatically as database owner when user logs into database.

> What I am looking for is a step by step outline that approximates:

It's fairly obvious what's happening here: psql sends ALTER USER xxx
PASSWORD ..., where it gets xxx from PQuser(), so that is the role name
that was logged in with.  There are any number of reasons why that might
not be the currently active role.

The psql man page says

\password [ username ]
Changes the password of the specified user (by default, the
current user).

So I'd say this is not doing what the documentation says.

With server versions >= 9.5 we could dodge the issue by sending
ALTER USER CURRENT_USER PASSWORD   For older servers,
I suppose we could do "SELECT CURRENT_USER" first.

I'm not sure if we want to change a security-relevant behavior
in released branches.  But if we don't, we probably need to
change the docs to something like "(by default, the logged-in
user)".

regards, tom lane

Re: Can db user change own password?

[Adrian Klaver]

On 10/21/21 09:53, Tom Lane wrote:

Adrian Klaver  writes:



It's fairly obvious what's happening here: psql sends ALTER USER xxx
PASSWORD ..., where it gets xxx from PQuser(), so that is the role name
that was logged in with.  There are any number of reasons why that might
not be the currently active role.

The psql man page says

 \password [ username ]
 Changes the password of the specified user (by default, the
 current user).

So I'd say this is not doing what the documentation says.


Oops. That is where I got hung up.



With server versions >= 9.5 we could dodge the issue by sending
ALTER USER CURRENT_USER PASSWORD   For older servers,
I suppose we could do "SELECT CURRENT_USER" first.

I'm not sure if we want to change a security-relevant behavior
in released branches.  But if we don't, we probably need to
change the docs to something like "(by default, the logged-in
user)".


I would suggest session(_)user to make it match with the rest of 
documentation.




regards, tom lane




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Can db user change own password?

[Toomas]

Hi,

DB user has definition

GRANT db_owner TO db_user;
ALTER ROLE db_user IN DATABASE db SET role TO ‘db_owner’;

and user logs in with command

1) $ psql -U db_user -d db

2) db=> select current_user, session_user;
 current_user | session_user
--+--
 db_owner  | db_user
(1 row)

3) db=> \du db_user
   List of roles
 Role name | Attributes | Member of
---++---
 db_user   || {db_owner}

4) db=> \password
Enter new password:
Enter it again:
ERROR:  permission denied

5) db=> SET SESSION AUTHORIZATION db_user;
SET

6) db=> select current_user, session_user;
 current_user | session_user
--+--
 db_user  | db_user
(1 row)

7) db=> \password
Enter new password:
Enter it again:
db=>

I hope this explains.

BR,
Toomas

> On 21. Oct 2021, at 19:13, Adrian Klaver  wrote:
> 
> On 10/21/21 08:39, Toomas wrote:
>> Hi,
>> Basically the case was, when *session_user != current_user* then command 
>> \password failed with error message “ERROR:  permission denied”. All was 
>> good when session_user == current_user.
>> In terms of statement “session_user user was set as the owner of the 
>> database automatically” - I have a setup where session_user is changed 
>> automatically as database owner when user logs into database.
> 
> What I am looking for is a step by step outline that approximates:
> 
> 1) psql -U 
> 
> 2) The command(s) that set session_user as database owner.
> 
> 3) The command(s) that make session_user != current_user.
> 
> 4) Output of:
>select session_user, current_user;
> 
> 5) The output of \du for the users involved
> 
>> BR,
>> Toomas
> 
> 
> 
> 
> -- 
> Adrian Klaver
> adrian.kla...@aklaver.com

Re: Can db user change own password?

[Tom Lane]

Adrian Klaver  writes:
> On 10/21/21 09:53, Tom Lane wrote:
>> I'm not sure if we want to change a security-relevant behavior
>> in released branches.  But if we don't, we probably need to
>> change the docs to something like "(by default, the logged-in
>> user)".

> I would suggest session(_)user to make it match with the rest of 
> documentation.

But that's not right either.

regression=# select session_user;
 session_user 
--
 postgres
(1 row)

regression=# create user joe;
CREATE ROLE
regression=# set session authorization joe;
SET
regression=> select session_user;
 session_user 
--
 joe
(1 row)

regression=> \password
Enter new password: 
Enter it again: 
ERROR:  must be superuser to alter superuser roles or change superuser attribute
regression=> 

Another angle to this: even without SET SESSION AUTHORIZATION, the
existence of username mapping options in the pg_hba machinery means that
the role name that psql thought it logged in with might have nothing to do
with the role name that the server thinks is the authenticated user.
There might be no SQL role by that name at all.  So what psql is doing
here is flat-out wrong.  I'm still hesitant about changing the behavior in
the back branches, though, especially given the lack of prior complaints.

regards, tom lane

Re: Can db user change own password?

[Tom Lane]

Toomas  writes:
> 2) db=> select current_user, session_user;
>  current_user | session_user
> --+--
>  db_owner  | db_user
> (1 row)

Given that setup, I wonder which role you expected \password to change.

If we target the current_user, we can expect the command to succeed.
I'm just wondering if people will find that surprising.
Targeting the session_user might be less surprising (or not?)
but as this example shows, it can fail.

One thing that would help, regardless of which definition we think
is most appropriate, is to have \password explicitly say which role
it's intending to set the password for:

db=> \password
Enter new password for role "dbowner":
Enter it again:

regards, tom lane

Re: Connection queuing by connection pooling libraries

[Saurav Sarkar]

Thanks a lot Vijay for your valuable inputs.

Best Regards,
Saurav

On Wed, Oct 20, 2021 at 1:06 AM Vijaykumar Jain <
vijaykumarjain.git...@gmail.com> wrote:

> On Tue, 19 Oct 2021 at 23:20, Vijaykumar Jain <
> vijaykumarjain.git...@gmail.com> wrote:
>
>>
>> On Tue, 19 Oct 2021 at 23:09, Vijaykumar Jain <
>> vijaykumarjain.git...@gmail.com> wrote:
>>
>>>
>>> On Tue, 19 Oct 2021 at 22:45, Saurav Sarkar 
>>> wrote:
>>>
 Hi All,


 A basic question on handling large number of concurrent requests on DB.

 I have a cloud service which can get large of requests which will
 obviously trigger the db operations.

 Every db will have some max connection limit which can get exhausted on
 large number of requests.

 I know db connection pooling can be used to reuse the connections but
 it will not help when there are large number of active concurrent
 connections. My queries are already optimised and short living.

 For that i need some queuing mechanism like pgbouncer for postgres
 https://www.percona.com/blog/2021/02/26/connection-queuing-in-pgbouncer-is-it-a-magical-remedy/

 pgbounder i understand is a proxy which needs to be separately
 installed on the web or db server.

 I was thinking if the normal client side db connection pooling
 libraries like Apache DBCP , can also provide similar connection queuing
 while running in the application runtime.

>>>
>>>
>>
> also pls checkout, i forgot to link early on
> Number Of Database Connections - PostgreSQL wiki
> 
> it explains the reasons, too many direct connections may result in
> performance issues.
>
>

Re: Can db user change own password?

[Adrian Klaver]

On 10/21/21 10:51, Tom Lane wrote:

Toomas  writes:

2) db=> select current_user, session_user;
  current_user | session_user
--+--
  db_owner  | db_user
(1 row)


Given that setup, I wonder which role you expected \password to change.

If we target the current_user, we can expect the command to succeed.
I'm just wondering if people will find that surprising.
Targeting the session_user might be less surprising (or not?)
but as this example shows, it can fail.


Well from here:

https://www.postgresql.org/docs/current/sql-set-session-authorization.html

'The current user identifier is relevant for permission checking.'

To me current_user would be the less surprising choice.



One thing that would help, regardless of which definition we think
is most appropriate, is to have \password explicitly say which role
it's intending to set the password for:

db=> \password
Enter new password for role "dbowner":
Enter it again:


Yes, that would be helpful in untangling who you are actually pointing at.



regards, tom lane




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Can db user change own password?

[Adrian Klaver]

On 10/21/21 10:44, Tom Lane wrote:

Adrian Klaver  writes:

On 10/21/21 09:53, Tom Lane wrote:



I would suggest session(_)user to make it match with the rest of
documentation.


But that's not right either.

regression=# select session_user;
  session_user
--
  postgres
(1 row)

regression=# create user joe;
CREATE ROLE
regression=# set session authorization joe;
SET
regression=> select session_user;
  session_user
--
  joe
(1 row)

regression=> \password
Enter new password:
Enter it again:
ERROR:  must be superuser to alter superuser roles or change superuser attribute
regression=>


Hmm, I'm striking out on this one. Just now grasped that PQuser() is 
grabbing a user/role from the connection itself and that the effective 
role could be something entirely different.




Another angle to this: even without SET SESSION AUTHORIZATION, the
existence of username mapping options in the pg_hba machinery means that
the role name that psql thought it logged in with might have nothing to do
with the role name that the server thinks is the authenticated user.
There might be no SQL role by that name at all.  So what psql is doing
here is flat-out wrong.  I'm still hesitant about changing the behavior in
the back branches, though, especially given the lack of prior complaints.

regards, tom lane




--
Adrian Klaver
adrian.kla...@aklaver.com

Looking for a doc section that presents the overload selection rules

[Bryn Llewellyn]

I fear that I'm about to embarrass myself again. So I'll just ask for 
forgiveness in advance.

Here's a simple test to get started. (All tests are done in a session where I 
set the timezone to 'UTC'.)

drop function if exists f(text)cascade;
drop function if exists f(timestamp)   cascade;
drop function if exists f(timestamptz) cascade;

create function f(t in text)
  returns text
  language plpgsql
as $body$
begin
  return 'plain "text" overload: '||t;
end;
$body$;

select f('2021-03-15'::date);

This causes the 42883 error, "function f(date) does not exist". I might've 
expected the system to have done an implicit conversion to "text" because this 
conversion is supported, thus:

select f(('2021-03-15'::date)::text);

This succeeds with this result:

plain "text" overload: 2021-03-15

There's clearly a rule at work here. For some reason, the implicit conversion 
from "date" to "text" is not considered to be acceptable.

Now add a plain "timestamp" overload and repeat the test:

create function f(t in timestamp)
  returns text
  language plpgsql
as $body$
begin
  return 'plain "timestamp" overload: '||t::text;
end;
$body$;

select f('2021-03-15'::date);

This succeeds with this result:

plain "timestamp" overload: 2021-03-15 00:00:00

So there's a different rule at work here.  For some reason, the implicit 
conversion from "date" to plain "timestamp" _is_ considered to be acceptable.

Now add a "timesatmptz" overload and repeat the test:

create function f(t in timestamptz)
  returns text
  language plpgsql
as $body$
begin
  return '"timestamptz" overload: '||t::text;
end;
$body$;

select f('2021-03-15'::date);

This succeeds with this result:

"timestamptz" overload: 2021-03-15 00:00:00+00

For some reason, the implicit conversion from "date" to "timestamptz" _is_ 
considered to be preferable to the implicit conversion from "date" to plain 
"timestamp".

I started with "38.6. Function Overloading", followed the link to "Chapter 10. 
Type Conversion" and started with "10.3. Functions". I read "If no exact match 
is found, see if the function call appears to be a special type conversion 
request…" as far as "Note that the “best match” rules are identical for 
operator and function type resolution." So I went to "10.2. Operators" and 
searched in the page for "timestamp". No hits.

Where, in the PG doc, can I find a statement of the rules that allow me to 
predict the outcome of my tests?

PGConf.NYC: Call for Volunteers

[Andreas 'ads' Scherbaum]

PGconf.NYC 2021 is just around the corner, and you can help us make it a
success. If you've frequented previous PostgreSQL conferences, you've seen
our enthusiastic volunteers who have helped us make the event a pleasant
experience for all our visitors. If you want to be a part of this great
team, here's your chance to sign up!

More specifically, we need help with the following tasks:


   - Setting up the venue
   - Staffing Registration and the Infodesk
   - Moderating and hosting the talks
   - Making sure everyone has a good time


If any or all of these sound like your cup of tea, you're more than welcome
to lend a hand during the event. All you have to do is send us a mail at
cont...@pgconf.nyc.
Note: We will not be able to cover travel or accommodation costs.


See you in NYC!

-- 
Andreas 'ads' Scherbaum
German PostgreSQL User Group
European PostgreSQL User Group - Board of Directors
Volunteer Regional Contact, Germany - PostgreSQL Project

Re: Looking for a doc section that presents the overload selection rules

[David G. Johnston]

On Thursday, October 21, 2021, Bryn Llewellyn  wrote:

>
> This causes the 42883 error, "function f(date) does not exist". I might've
> expected the system to have done an implicit conversion to "text" because
> this conversion is supported, thus:
>

Yes, implicit casting to text is bad.


>
> For some reason, the implicit conversion from "date" to "timestamptz" _is_
> considered to be preferable to the implicit conversion from "date" to plain
> "timestamp".
>
> Where, in the PG doc, can I find a statement of the rules that allow me to
> predict the outcome of my tests?
>

I do not believe the relevant metadata is maintained in the documentation.
You would have to, at minimum, consult the system catalogs; as documented
here:

https://www.postgresql.org/docs/current/typeconv-overview.html

Note that page does discuss the concept of “preference” that you’ve
observed.

David J.

WAL streaming and dropping a large table

[Rory Falloon]

Hi,

My postgres server setup is thus: a production database, which is using WAL
streaming (hot standby) to four other hosts of various latencies. I Have a
table that is ~200GB which has been backed up, and now I Want to remove it.
The table is not in use, it is a child table to a parent table that _is_ in
use but I foresee no issues here. The total DB size is roughly 300GB.  The
main reason for needing to remove it is to recover the space back on the
partition which is humming away at 88% usage, and the table I wish to drop
is better off in an archive somewhere.

I just removed around 10% of it with  'delete from', which of course
cascaded to the replication hosts. This increased the size of my pg_xlog
folder (from ~3GB to ~6.5GB) which of course increased my partition usage.
Obviously this is expected, but I wonder what would happen if I had issued
the 'drop table'?

I expect the nature of enabling max_replication_slots would mean the
database would retain the wal segments until all have caught up; it could
take quite a long time to 'catch up' after the 'drop table' command? It
took about 10 minutes before the pg_xlog folder size had 'settled down' to
what I normally see as default which is ~3GB.

> wal_keep_segments is defined as 128,
> wal_max_size is not defined,
> max_replication_slots is enabled

I'd prefer to use 'drop table' as it would recover the data immediately,
but not if it will impact the production database in a way that the
partition will become full, which defeats the purpose. Is it advisable to
move the pg_xlog folder to another volume on the system with more space
(which I have) and symlink - obviously a stop & start of the db required -
and then let the WAL archives fill up as need be? Or am I missing something
obvious (likely)

Thanks

Re: Looking for a doc section that presents the overload selection rules

[Adrian Klaver]

On 10/21/21 12:52, Bryn Llewellyn wrote:
I fear that I'm about to embarrass myself again. So I'll just ask for 
forgiveness in advance.


Here's a simple test to get started. (All tests are done in a session 
where I set the timezone to 'UTC'.)


*drop function if exists f(text)        cascade;
**drop function if exists f(timestamp)   cascade;
**drop function if exists f(timestamptz) cascade;
**
*
*create function f(t in text)
   returns text
   language plpgsql
as $body$
begin
   return 'plain "text" overload: '||t;
end;
$body$;

**select f('2021-03-15'::date);
*
This causes the 42883 error, "function f(date) does not exist". I 
might've expected the system to have done an implicit conversion to 
"text" because this conversion is supported, thus:


*select f(('2021-03-15'::date)::text);*

This succeeds with this result:

*plain "text" overload: 2021-03-15
*
There's clearly a rule at work here. For some reason, the implicit 
conversion from "date" to "text" is not considered to be acceptable.


From 10.3
"
Look for the best match.

Discard candidate functions for which the input types do not match 
and cannot be converted (using an implicit conversion) to match. unknown 
literals are assumed to be convertible to anything for this purpose. If 
only one candidate remains, use it; else continue to the next step.

"

See cast query below.


For some reason, the implicit conversion from "date" to "timestamptz" 
_is_ considered to be preferable to the implicit conversion from "date" 
to plain "timestamp".


https://www.postgresql.org/docs/current/catalog-pg-type.html

select oid from pg_type where typname = 'date';
 oid
--
 1082

https://www.postgresql.org/docs/current/catalog-pg-cast.html

select * from pg_cast where castsource = 1082;
  oid  | castsource | casttarget | castfunc | castcontext | castmethod
---+++--+-+
 11421 |   1082 |   1114 | 2024 | i   | f
 11422 |   1082 |   1184 | 1174 | i   | f

Note castcontext of 'i'(implicit) and only to timestamp types per below.


select typname, typispreferred from pg_type where oid in (1114, 1184);
   typname   | typispreferred
-+
 timestamp   | f
 timestamptz | t


typispreferred  has timestmaptz as preferred cast.



I started with "38.6. Function Overloading", followed the link to 
"Chapter 10. Type Conversion" and started with "10.3. Functions". I read 
"If no exact match is found, see if the function call appears to be a 
special type conversion request…" as far as "Note that the “best 
match” rules are identical for operator and function type resolution." 
So I went to "10.2. Operators" and searched in the page for "timestamp". 
No hits.


Where, in the PG doc, can I find a statement of the rules that allow me 
to predict the outcome of my tests?





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Looking for a doc section that presents the overload selection rules

[Bryn Llewellyn]

> Adrian Klaver wrote:
> 
>> Bryn wrote:
>> 
>> I fear that I'm about to embarrass myself again. So I'll just ask for 
>> forgiveness in advance. Here's a simple test to get started. (All tests are 
>> done in a session where I set the timezone to 'UTC'.)
>> 
>> drop function if exists f(text)cascade;
>> drop function if exists f(timestamp)   cascade;
>> drop function if exists f(timestamptz) cascade;
>> 
>> create function f(t in text)
>>   returns text
>>   language plpgsql
>> as $body$
>> begin
>>   return 'plain "text" overload: '||t;
>> end;
>> $body$;
>> 
>> select f('2021-03-15'::date);
>> 
>> This causes the 42883 error, "function f(date) does not exist". I might've 
>> expected the system to have done an implicit conversion to "text" because 
>> this conversion is supported, thus:
>> 
>> select f(('2021-03-15'::date)::text);
>> 
>> This succeeds with this result:
>> 
>> plain "text" overload: 2021-03-15
>> 
>> There's clearly a rule at work here. For some reason, the implicit 
>> conversion from "date" to "text" is not considered to be acceptable.
> 
> From 10.3
> "
> Look for the best match.
> 
> Discard candidate functions for which the input types do not match and cannot 
> be converted (using an implicit conversion) to match. unknown literals are 
> assumed to be convertible to anything for this purpose. If only one candidate 
> remains, use it; else continue to the next step.
> "
> 
> See cast query below.
> 
> For some reason, the implicit conversion from "date" to "timestamptz" _is_ 
> considered to be preferable to the implicit conversion from "date" to plain 
> "timestamp".
> 
> 52.62. pg_type — https://www.postgresql.org/docs/current/catalog-pg-type.html
> 
> select oid from pg_type where typname = 'date';
> 
> oid
> --
> 1082
> 
> 52.10. pg_cast — https://www.postgresql.org/docs/current/catalog-pg-cast.html
> 
> select * from pg_cast where castsource = 1082;
> 
>  oid  | castsource | casttarget | castfunc | castcontext | castmethod
> ---+++--+-+
> 11421 |   1082 |   1114 | 2024 | i   | f
> 11422 |   1082 |   1184 | 1174 | i   | f
> 
> Note castcontext of 'i'(implicit) and only to timestamp types per below.
> 
> select typname, typispreferred from pg_type where oid in (1114, 1184);
> 
>   typname   | typispreferred
> -+
> timestamp   | f
> timestamptz | t
> 
> typispreferred  has timestmaptz as preferred cast.
> 
>> I started with "38.6. Function Overloading", followed the link to "Chapter 
>> 10. Type Conversion" and started with "10.3. Functions". I read "If no exact 
>> match is found, see if the function call appears to be a special type 
>> conversion request…" as far as "Note that the “best match” rules are 
>> identical for operator and function type resolution." So I went to "10.2. 
>> Operators" and searched in the page for "timestamp". No hits. Where, in the 
>> PG doc, can I find a statement of the rules that allow me to predict the 
>> outcome of my tests?

Thank you very much, Adrian. This is just what I needed.

"10.3. Functions" doesn't mention "pg_type". And "52.62. pg_type" doesn't 
mention "pg_cast". So it's no wonder that I couldn't find what you showed me 
here. (At least, that's my excuse.)

Thanks, too, to David Johnston for your reply. Yes, I see now that the "10.1. 
Overview" page that starts the "Type Conversion" chapter does have lots of 
inflexions of the verb "prefer". And close to one of these there's a link to 
"Table 52.63" on the "52.62. pg_type" page. But I failed to spot that.

You said "implicit casting to text is bad". Yes, all implicit casting is, at 
best, potentially confusing for human code readers. I aim religiously to avoid 
this and always aim to use an explicit typecast instead.

And this brings me to what started me on this path today. "\df to_char" shows 
that while it has overloads for both plain "timestamp" and "timestamptz" 
date-time inputs, it has no "date" overload. Here's a contrived test:

deallocate all;
prepare s as
with c as (
  select
'2021-06-15'::date as d,
'dd-Mon- TZH:TZM' as fmt)
select
  rpad(current_setting('timezone'), 20)  as "timezone",
  to_char(d,  fmt)   as "implicit cast to timestamptz",
  to_char(d::timestamptz, fmt)   as "explicit cast to timestamptz",
  to_char(d::timestamp,   fmt)   as "explicit cast to plain timestamp"
from c;

\t on
set timezone = 'Europe/Helsinki';
execute s;

set timezone = 'America/Los_Angeles';
execute s;
\t off

It gives the result that I'd expect:

 Europe/Helsinki  | 15-Jun-2021 +03:00   | 15-Jun-2021 +03:00   
| 15-Jun-2021 +00:00
 America/Los_Angeles  | 15-Jun-2021 -07:00   | 15-Jun-2021 -07:00   
| 15-Jun-2021 +00:00

And, given that nobody would include "TZH:TZM" in the template for rendering a 
date (except in this contrived test), then all three text renderings 

Re: Looking for a doc section that presents the overload selection rules

[Adrian Klaver]

On 10/21/21 15:45, Bryn Llewellyn wrote:

/Adrian Klaver wrote:/


/Bryn wrote:/



Thanks, too, to David Johnston for your reply. Yes, I see now that the 
"10.1. Overview" page that starts the "Type Conversion" chapter does 
have lots of inflexions of the verb "prefer". And close to one of these 
there's a link to "Table 52.63" on the "52.62. pg_type" page. But I 
failed to spot that.


You said "implicit casting to text is bad". Yes, all implicit casting 
is, at best, potentially confusing for human code readers. I aim 
religiously to avoid this and always aim to use an explicit typecast 
instead.


This was explicitly dealt with in the Postgres 8.3 release:

https://www.postgresql.org/docs/8.3/release-8-3.html

E.24.2.1. General

Non-character data types are no longer automatically cast to TEXT 
(Peter, Tom)




And this brings me to what started me on this path today. "\df to_char" 
shows that while it has overloads for both plain "timestamp" and 
"timestamptz" date-time inputs, it has no "date" overload. Here's a 


That is because:

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

to_char ( timestamp, text ) → text
to_char ( timestamp with time zone, text ) → text
Converts time stamp to string according to the given format.
to_char(timestamp '2002-04-20 17:31:12.66', 'HH12:MI:SS') → 05:31:12

to_char() expects a timestamp and per my previous post the preferred 
cast for a date to a timestamp is to timestamptz.




contrived test:

deallocate all;
prepare s as
with c as (
   select
     '2021-06-15'::date as d,
     'dd-Mon- TZH:TZM' as fmt)
select
   rpad(current_setting('timezone'), 20)  as "timezone",
   to_char(d,              fmt)           as "implicit cast to timestamptz",
   to_char(d::timestamptz, fmt)           as "explicit cast to timestamptz",
   to_char(d::timestamp,   fmt)           as "explicit cast to plain 
timestamp"

from c;

\t on
set timezone = 'Europe/Helsinki';
execute s;

set timezone = 'America/Los_Angeles';
execute s;
\t off

It gives the result that I'd expect:

  Europe/Helsinki      | 15-Jun-2021 +03:00           | 15-Jun-2021 
+03:00           | 15-Jun-2021 +00:00
  America/Los_Angeles  | 15-Jun-2021 -07:00           | 15-Jun-2021 
-07:00           | 15-Jun-2021 +00:00


And, given that nobody would include "TZH:TZM" in the template for 
rendering a date (except in this contrived test), then all three text 
renderings in this test would be identical.


However, it seems to me that the proper practice must be not to rely on 
intellectual analysis and the implicit cast. Rather, you must say that 
"date" is more like plain "timestamp" than it's like "timestamptz" (in 
that it knows nothing about timezones), and to write the explicit cast 
to plain "timestamp". But this leads to nastily cluttered code.


*Why is there no "date" overload of "to_char()"?*




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Looking for a doc section that presents the overload selection rules

[Bryn Llewellyn]

> On 21-Oct-2021, at 17:15, Adrian Klaver  wrote:
> 
> On 10/21/21 15:45, Bryn Llewellyn wrote:
>>> /Adrian Klaver wrote:/
>>> 
 /Bryn wrote:/
 
> 
>> Thanks, too, to David Johnston for your reply. Yes, I see now that the 
>> "10.1. Overview" page that starts the "Type Conversion" chapter does have 
>> lots of inflexions of the verb "prefer". And close to one of these there's a 
>> link to "Table 52.63" on the "52.62. pg_type" page. But I failed to spot 
>> that.
>> You said "implicit casting to text is bad". Yes, all implicit casting is, at 
>> best, potentially confusing for human code readers. I aim religiously to 
>> avoid this and always aim to use an explicit typecast instead.
> 
> This was explicitly dealt with in the Postgres 8.3 release:
> 
> https://www.google.com/url?q=https://www.postgresql.org/docs/8.3/release-8-3.html&source=gmail-imap&ust=163546656100&usg=AOvVaw1Cm9kd4XZPydsVQ0qGU2a-
> 
> E.24.2.1. General
> 
>Non-character data types are no longer automatically cast to TEXT (Peter, 
> Tom)
> 
>> And this brings me to what started me on this path today. "\df to_char" 
>> shows that while it has overloads for both plain "timestamp" and 
>> "timestamptz" date-time inputs, it has no "date" overload. Here's a 
> 
> That is because:
> 
> https://www.google.com/url?q=https://www.postgresql.org/docs/14/functions-formatting.html&source=gmail-imap&ust=163546656100&usg=AOvVaw1VLjGNdZOaBaaAolnnrXtx
> 
> to_char ( timestamp, text ) → text
> to_char ( timestamp with time zone, text ) → text
> Converts time stamp to string according to the given format.
> to_char(timestamp '2002-04-20 17:31:12.66', 'HH12:MI:SS') → 05:31:12
> 
> to_char() expects a timestamp and per my previous post the preferred cast for 
> a date to a timestamp is to timestamptz.
> 
> 
>> contrived test:
>> deallocate all;
>> prepare s as
>> with c as (
>>   select
>> '2021-06-15'::date as d,
>> 'dd-Mon- TZH:TZM' as fmt)
>> select
>>   rpad(current_setting('timezone'), 20)  as "timezone",
>>   to_char(d,  fmt)   as "implicit cast to timestamptz",
>>   to_char(d::timestamptz, fmt)   as "explicit cast to timestamptz",
>>   to_char(d::timestamp,   fmt)   as "explicit cast to plain 
>> timestamp"
>> from c;
>> \t on
>> set timezone = 'Europe/Helsinki';
>> execute s;
>> set timezone = 'America/Los_Angeles';
>> execute s;
>> \t off
>> It gives the result that I'd expect:
>>  Europe/Helsinki  | 15-Jun-2021 +03:00   | 15-Jun-2021 +03:00
>>| 15-Jun-2021 +00:00
>>  America/Los_Angeles  | 15-Jun-2021 -07:00   | 15-Jun-2021 -07:00
>>| 15-Jun-2021 +00:00
>> And, given that nobody would include "TZH:TZM" in the template for rendering 
>> a date (except in this contrived test), then all three text renderings in 
>> this test would be identical.
>> However, it seems to me that the proper practice must be not to rely on 
>> intellectual analysis and the implicit cast. Rather, you must say that 
>> "date" is more like plain "timestamp" than it's like "timestamptz" (in that 
>> it knows nothing about timezones), and to write the explicit cast to plain 
>> "timestamp". But this leads to nastily cluttered code.
>> *Why is there no "date" overload of "to_char()"?*

You've lost me entirely here, I'm afraid.

My question was simple: why is there no "to_char ( date, text ) → text" 
overload?

Without this, and as long as the good practice rule is followed to code so that 
implicit conversion is never invoked, then using "to_char()" on a "date" value 
requires writing an explicit typecast. There are only two possible choices: 
cast to plain "timestamp" or cast to "timestamptz". And for reasons that I'm 
sure you'd explain better than I would, the choice makes no actual difference 
to the outcome when a template is used that's natural for a "date" value.

So the consequence is that you have to write cluttered code and a fairly 
elaborate comment to say what your intention is.

What would you do here? Would you break the rule of practice so that you simply 
invoke "to_char()" on a "date" value _without writing a typecast_ and then 
letting the implicit conversion (which we know is to "timestamptz") have its 
innocent effect?