Re: tcp keepalives not sent during long query
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
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
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
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
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
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?
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?
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?
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
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
> 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
> 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
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
> 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
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?
