Re: tcp keepalives not sent during long query

2022-12-15 Thread Laurenz Albe
On Thu, 2022-12-15 at 08:31 +0100, Willy-Bas Loos wrote:
> On Wed, Dec 14, 2022 at 6:38 PM Tom Lane  wrote:
> > It'd be worth doing
> > 
> > show tcp_keepalives_idle;
> 
> Wow, you're right! It's in the postgresql.conf but it isn't set when I reload 
> the server
> A restart also doesn't do it and even doing SET tcp_keepalives_idle=120; 
> doesn't work.
> It gives me a confirmation, but then when I SHOW the value, it gives me 0.
> 
> wbloos=# set tcp_keepalives_idle=120;
> SET
> wbloos=# show tcp_keepalives_idle;
>  tcp_keepalives_idle 
> -
>  0
> (1 row)

One good way to debug this is

  SELECT setting, source, sourcefile, sourceline
  FROM pg_settings
  WHERE name = 'tcp_keepalives_idle';

That will tell you from where you get the parameter value.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com




Re: tcp keepalives not sent during long query

2022-12-15 Thread Willy-Bas Loos
Nice query, i keep learning new stuff here.
Anyway, that shows the correct line (80) in the config file, but the wrong
value.
Namely 0, where the config file has 120

On Thu, Dec 15, 2022 at 12:37 PM Laurenz Albe 
wrote:

> On Thu, 2022-12-15 at 08:31 +0100, Willy-Bas Loos wrote:
> > On Wed, Dec 14, 2022 at 6:38 PM Tom Lane  wrote:
> > > It'd be worth doing
> > >
> > > show tcp_keepalives_idle;
> >
> > Wow, you're right! It's in the postgresql.conf but it isn't set when I
> reload the server
> > A restart also doesn't do it and even doing SET tcp_keepalives_idle=120;
> doesn't work.
> > It gives me a confirmation, but then when I SHOW the value, it gives me
> 0.
> >
> > wbloos=# set tcp_keepalives_idle=120;
> > SET
> > wbloos=# show tcp_keepalives_idle;
> >  tcp_keepalives_idle
> > -
> >  0
> > (1 row)
>
> One good way to debug this is
>
>   SELECT setting, source, sourcefile, sourceline
>   FROM pg_settings
>   WHERE name = 'tcp_keepalives_idle';
>
> That will tell you from where you get the parameter value.
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>


-- 
Willy-Bas Loos


Re: tcp keepalives not sent during long query

2022-12-15 Thread Geoff Winkless
On Thu, 15 Dec 2022 at 07:31, Willy-Bas Loos  wrote:
>
> wbloos=# set tcp_keepalives_idle=120;
> SET
> wbloos=# show tcp_keepalives_idle;
>  tcp_keepalives_idle
> -
>  0

Are you connected in this psql session via tcp or unix domain socket?

"In sessions connected via a Unix-domain socket, this parameter is
ignored and always reads as zero."

Geoff




Re: tcp keepalives not sent during long query

2022-12-15 Thread Willy-Bas Loos
On Thu, Dec 15, 2022 at 2:04 PM Geoff Winkless  wrote:

>
> Are you connected in this psql session via tcp or unix domain socket?
>
> Right, got me again. That was a Unix-domain socket.
When I do SHOW tcp_keepalives_idle; from pgAdmin it shows me 120, which is
correct.
Thanks for clarifying that.

So that means I still don't know why the connections are breaking.
I know that this could be anything, in any case not due to the postgres
server.

Our ISP has inspected the network traffic and indeed found empty TCP ACK
packages being sent back and forth to/from the user's IP, supposedly
keepalives.
I contacted the user and doublechecked their statement that they only have
the issue when running long queries. Turns out that this is not the case.
The connection also breaks on idle query windows only then they just
reconnect so it's not a problem.

The user now indicated that they can work around the issue by creating a
table as a result, instead of simply selecting the data to be displayed in
the client.
So we decided to cease our efforts to fix the issue.
Thanks a lot for your help!

-- 
Willy-Bas Loos


Re: tcp keepalives not sent during long query

2022-12-15 Thread Tom Lane
Willy-Bas Loos  writes:
> It gives me a confirmation, but then when I SHOW the value, it gives me 0.

> wbloos=# set tcp_keepalives_idle=120;
> SET
> wbloos=# show tcp_keepalives_idle;
>  tcp_keepalives_idle
> -
>  0
> (1 row)

That's the behavior I'd expect on a local (Unix-socket) connection
... you sure you're doing this from one of the problematic clients?

regards, tom lane




Re: tcp keepalives not sent during long query

2022-12-15 Thread Willy-Bas Loos
Yes exactly, Geoff Winkless pointed that out too.
I thought I'd found a cause for the breaking connections, but I hadn't.
Thanks a lot for your help!


On Thu, Dec 15, 2022 at 3:48 PM Tom Lane  wrote:

> Willy-Bas Loos  writes:
> > It gives me a confirmation, but then when I SHOW the value, it gives me
> 0.
>
> > wbloos=# set tcp_keepalives_idle=120;
> > SET
> > wbloos=# show tcp_keepalives_idle;
> >  tcp_keepalives_idle
> > -
> >  0
> > (1 row)
>
> That's the behavior I'd expect on a local (Unix-socket) connection
> ... you sure you're doing this from one of the problematic clients?
>
> regards, tom lane
>


-- 
Willy-Bas Loos


pg_dumpall renders ALTER TABLE for a view?

2022-12-15 Thread Michel Pelletier
Hello,

We found the root cause for an issue we encountered restoring a saved
database stored with pg_dumpall, but during this investigation we realized
that pg_dump/all renders `ALTER TABLE` statements for views, for example to
convey ownership.

I get that this is synonymous in most cases, except when there is an event
trigger for `ALTER TABLE`, it ends up firing the event trigger for the
views, and any event triggers expected to fire on `ALTER VIEW` statements
do not fire.

Is this something we can contribute a fix for, or is this some kind of
necessary b/w compat issue that must remain?  Looking at the history of
ALTER VIEW it looks like it has existed since at least 9.0, so it seems
safe to me from a b/w compat standpoint to render the correct statement.

Thanks!

-Michel


Re: pg_dumpall renders ALTER TABLE for a view?

2022-12-15 Thread Tom Lane
Michel Pelletier  writes:
> We found the root cause for an issue we encountered restoring a saved
> database stored with pg_dumpall, but during this investigation we realized
> that pg_dump/all renders `ALTER TABLE` statements for views, for example to
> convey ownership.

> I get that this is synonymous in most cases, except when there is an event
> trigger for `ALTER TABLE`, it ends up firing the event trigger for the
> views, and any event triggers expected to fire on `ALTER VIEW` statements
> do not fire.

> Is this something we can contribute a fix for, or is this some kind of
> necessary b/w compat issue that must remain?

We're not likely to change the fact that you're allowed to write ALTER
TABLE for this, so if your event triggers get broken by doing that
you'd best fix the event triggers.

I don't have any great objection to making pg_dump emit the more modern
spelling (I think ... you'd need to look into pg_restore to make sure
it's not assuming something in this area).  But doing that won't
really remove the hazard.

regards, tom lane




Re: pg_dumpall renders ALTER TABLE for a view?

2022-12-15 Thread Michel Pelletier
On Thu, Dec 15, 2022 at 9:15 AM Tom Lane  wrote:

> Michel Pelletier  writes:
> > I get that this is synonymous in most cases, except when there is an
> event
> > trigger for `ALTER TABLE`, it ends up firing the event trigger for the
> > views, and any event triggers expected to fire on `ALTER VIEW` statements
> > do not fire.
>
> > Is this something we can contribute a fix for, or is this some kind of
> > necessary b/w compat issue that must remain?
>
> We're not likely to change the fact that you're allowed to write ALTER
> TABLE for this, so if your event triggers get broken by doing that
> you'd best fix the event triggers.
>

For sure, and we're working on fixing the triggers, I should have been more
clear that I'm not suggesting any changes to the server behavior, just to
the dump/restore process.


> I don't have any great objection to making pg_dump emit the more modern
> spelling (I think ... you'd need to look into pg_restore to make sure
> it's not assuming something in this area).  But doing that won't
> really remove the hazard.
>

Agree it won't remove the hazard, but should reduce the kind of collateral
damage we encountered.  Will examine pg_restore as you suggested to scope
the work better.

Thank you!

-Michel


Get size of variable-length attribute as stored on disk

2022-12-15 Thread Christophe Pettus
Hi,

Is there a way from SQL to get the size of a particular attribute (field in a 
row) with a variable-length typed as stored on disk (including compression)?  
The use case is to determine the size distribution for setting 
TOAST_TUPLE_TARGET and making other decisions about what to store in that field.

Thanks!



Re: Get size of variable-length attribute as stored on disk

2022-12-15 Thread Christophe Pettus



> On Dec 15, 2022, at 10:23, Christophe Pettus  wrote:
> Is there a way from SQL to get the size of a particular attribute (field in a 
> row) with a variable-length typed as stored on disk (including compression)?  
> The use case is to determine the size distribution for setting 
> TOAST_TUPLE_TARGET and making other decisions about what to store in that 
> field.

Like, for instance, pg_column_size.  :-)  Never mind!



Re: Test if a database has any privilege granted to public

2022-12-15 Thread Bryn Llewellyn
> t...@sss.pgh.pa.us wrote:
> 
>> ronljohnso...@gmail.com writes:
>> 
>> Off-topic, but you don't need all those text casts.
> 
> Indeed.  Something like this ought to do it:
> 
> select datname from pg_database where 0::oid = any(select 
> (aclexplode(datacl)).grantee);
> 
>  datname   
> 
> template1
> template0
> regression

Thanks, both, for the lightning-fast replies. Yes, I see it now. (I got myself 
confused about the requirements for using parentheses.) I should have slept on 
it before sending to the list.

There's still a little snag though. I created a brand-new cluster (with 
bootstrap superuser called "postgres"), started a session as "postgres", and 
did this:

create database d1;
revoke all on database d1 from postgres;
revoke all on database d1 from public;

create database d2;
revoke all on database d2 from postgres;

create database d3;

select
  datname::text   as name,
  case
when datacl is null then ''
else datacl::text
  end as datacl,
  (0::oid = any(select (aclexplode(datacl)).grantee))::text   as "public has a 
priv"
from pg_database
where datname in ('d1', 'd2', 'd3')
order by 1;

It produced this result:

 name | datacl | public has a priv 
--++---
 d1   | {} | false
 d2   | {=Tc/postgres} | true
 d3   |  | false

This seems to imply that this wording from "5.7. Privileges" 
(https://www.postgresql.org/docs/current/ddl-priv.html) is a little sketchy:

«
For other types of objects, the default privileges granted to PUBLIC are as 
follows: CONNECT and TEMPORARY (create temporary tables) privileges for 
databases…
»

The effect of  a NULL "datacl" is as if CONNECT and TEMPORARY have been granted 
to public. But even so, these privileges are not shown to have been actually 
granted.

In my test, I simply revoked "all" on "d2" from postgres. And this produced a 
not null "datacl" that did then show the documented default regime.

The following test:

create role r with login password 'p';
\c d1 r
\c d2 r
\c d3 r

Showed that "public has a priv" (as I coded it) doesn't tell the whole story 
because "\c d3 r" (as well as "\c d2 r") succeeds. Of course, "\c d1 r" fails.

I do see that, in a strict "legal sense", the doc that I quoted is not (quite) 
wrong. But to implement the test that I want robustly, I need to extend the 
logic thus:

select datname::text
from pg_database
where 0::oid = any(select (aclexplode(datacl)).grantee)
or datacl is null;

That's easy if you know that you need to write this. But the need to do so 
seems to depend on pretty arcane knowledge that, as far as I can see, isn't 
documented.

Anyway, my immediate requirement is solved. Thanks again!



Re: Test if a database has any privilege granted to public

2022-12-15 Thread David G. Johnston
On Thu, Dec 15, 2022 at 12:51 PM Bryn Llewellyn  wrote:

>
>
>
>
>
> *select datname::textfrom pg_databasewhere 0::oid = any(select
> (aclexplode(datacl)).grantee)or datacl is null;*
> That's easy if you know that you need to write this. But the need to do so
> seems to depend on pretty arcane knowledge that, as far as I can see, isn't
> documented.
>
>
The last paragraph of the privileges documentation says this explicitly:

If the “Access privileges” column is empty for a given object, it means the
object has default privileges (that is, its privileges entry in the
relevant system catalog is null). Default privileges always include all
privileges for the owner, and can include some privileges for PUBLIC
depending on the object type, as explained above.

https://www.postgresql.org/docs/current/ddl-priv.html

Or, you know, just use the provided functions that have been
programmed with knowledge of how the system works.

https://www.postgresql.org/docs/current/functions-info.html#FUNCTIONS-INFO-ACCESS-TABLE

select has_database_privilege(0,current_database(),'CONNECT');

David J.


Re: Test if a database has any privilege granted to public

2022-12-15 Thread Bryn Llewellyn
> david.g.johns...@gmail.com wrote:
> 
>> b...@yugabyte.com wrote:
>> 
>> select datname::text
>> from pg_database
>> where 0::oid = any(select (aclexplode(datacl)).grantee)
>> or datacl is null;
>> 
>> That's easy if you know that you need to write this. But the need to do so 
>> seems to depend on pretty arcane knowledge that, as far as I can see, isn't 
>> documented.
> 
> The last paragraph of the privileges documentation says this explicitly:
> 
> If the “Access privileges” column is empty for a given object, it means the 
> object has default privileges (that is, its privileges entry in the relevant 
> system catalog is null). Default privileges always include all privileges for 
> the owner, and can include some privileges for PUBLIC depending on the object 
> type, as explained above.
> 
> https://www.postgresql.org/docs/current/ddl-priv.html
> 
> Or, you know, just use the provided functions that have been programmed with 
> knowledge of how the system works.
> 
> https://www.postgresql.org/docs/current/functions-info.html#FUNCTIONS-INFO-ACCESS-TABLE
> 
> select has_database_privilege(0,current_database(),'CONNECT');

Ah yes, thanks. I’d missed that at the bottom of the page. I find the "aclitem" 
base type a bit confusing. I understand that, as a base type, its structure is 
hidden. However, its text typecast, exemplified by this:

calvin=r*w/hobbes

is conventional and rather informally doc'd. For example, what is a field? You 
have to guess.

There's no mention on the "Privileges" page of the "has_database_privilege()" 
function. Nor of "aclexplode()".

Even now, I haven't managed a linear start to finish read of the entire PG 
docs. And I found "has_database_privilege()" and "aclexplode()" by Internet 
search rather than x-refs within the PG doc.

The account of "has_database_privilege()" has this:

has_database_privilege ( [ user name or oid, ] database text or oid, privilege 
text ) → boolean

but that's the only mention of the function on the "System Information 
Functions and Operators" page. So nothing says what it means to use the (text, 
text) or (oid, text) overloads.

Moreover, nothing says that "0" denotes "public". (Nor does anything that I've 
found say that it's the same for "0" in the first field of what "aclexplode()" 
produces for each element of its "aclitem[]" argumemt. Internet search for 
"postgres oid of public" gets no useful hits.

But experiment shows that you can use this reserved name (in single quotes) 
with the same effect as "0".

I suppose that it all boils down to this:

…where
  has_database_privilege('public', datname, 'connect') or
  has_database_privilege('public', datname, 'create') or
  has_database_privilege('public', datname, 'temp');

versus this:

…where 0::oid = any(select (aclexplode(datacl)).grantee) or datacl is null;

Yes, I prefer the version that uses "has_database_privilege()" (even though 
it's longer) because it says more clearly what it means.

Thanks!






postgres_fdw does not push down DISTINCT

2022-12-15 Thread Christophe Pettus
While working on a different FDW, I discovered that postgres_fdw doesn't push a 
DISTINCT clause to the foreign server, unless it's part of an aggregate 
function:

k=# explain verbose select distinct i from tf;
QUERY PLAN
--
 HashAggregate  (cost=193.20..195.20 rows=200 width=8)
   Output: i
   Group Key: tf.i
   ->  Foreign Scan on public.tf  (cost=100.00..186.80 rows=2560 width=8)
 Output: i, f
 Remote SQL: SELECT i FROM public.tf
(6 rows)

Of course, the same effect can be achieved with GROUP BY, but I'm curious why 
it doesn't push it down?