psql variables in the DO command

[Pavel Luzanov]

Hello,

I can't use psql variable in the DO command. Is it intentional behavior?

postgres=# \set var 'Hello, World!'

postgres=# do $$begin raise notice '%', :'var'; end;$$;

ERROR:  syntax error at or near ":"

LINE 1: do $$begin raise notice '%', :'var'; end;$$;

 ^


--

-
Pavel Luzanov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Re: psql variables in the DO command

[Pavel Luzanov]

On 05.03.2018 16:42, Pavel Stehule wrote:


I can't use psql variable in the DO command. Is it intentional
behavior?


yes. psql variables living on client side, and are not accessible from 
server side . DO command is executed on server side.


But SELECT command also executed on a server side ))
I thought that the command is sent to the server after variable's 
replacement.




you can copy psql variables to GUC variables by set_config function, 
and then on server side use current_setting function for getting the 
content.

Yes, I know about workarounds.


-----
Pavel Luzanov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Re: psql variables in the DO command

[Pavel Luzanov]

On 05.03.2018 16:56, Pavel Stehule wrote:




I can't use psql variable in the DO command. Is it
intentional behavior?


yes. psql variables living on client side, and are not accessible
from server side . DO command is executed on server side.


But SELECT command also executed on a server side ))
I thought that the command is sent to the server after variable's
replacement.


The psql variables are injected into SQL string before execution 
(before SQL string is sent to server). But this injection is disabled 
inside strings - and body of DO command is passed as string.
Yes, now I understand this. But at first glance this is not an obvious 
behavior.


-----
Pavel Luzanov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Re: psql variables in the DO command

[Pavel Luzanov]

Another possible, but inconvenient workaround - constructing the right 
string before execution:


postgres=# \set var 'Hello, World!'
postgres=# \set cmd '$$begin raise notice ''%'', ' :'var' '; end;$$;'
postgres=# do :cmd;
NOTICE:  Hello, World!
DO

-
Pavel Luzanov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

On 05.03.2018 17:02, Pavel Luzanov wrote:

On 05.03.2018 16:56, Pavel Stehule wrote:




I can't use psql variable in the DO command. Is it
intentional behavior?


yes. psql variables living on client side, and are not
accessible from server side . DO command is executed on server side.


But SELECT command also executed on a server side ))
I thought that the command is sent to the server after variable's
replacement.


The psql variables are injected into SQL string before execution 
(before SQL string is sent to server). But this injection is disabled 
inside strings - and body of DO command is passed as string.
Yes, now I understand this. But at first glance this is not an obvious 
behavior.


-
Pavel Luzanov
Postgres Professional:http://www.postgrespro.com
The Russian Postgres Company

Re: psql variables in the DO command

[Pavel Luzanov]

On 05.03.2018 18:01, Pavel Stehule wrote:


It is most correct when you thinking about it.

1. :xx is out of SQL syntax, so can by safely used. There is not risk 
of unwanted usage.

But there is absence of wanted usage too.


2. but string literal can contain :xxx symbols and not necessary it 
means so it should be usage of psql variable - so additional syntax 
for disabling evaluation should be necessary
Yes and Oracle sqlplus (I khow than you know this tool) has special 
command to control this: set define ...




Unfortunately DO command is half baked - and doesn't support 
parameters. I am working on schema variables and I hope it will be a 
solution of this issue:


CREATE VARIABLE var as integer;

LET var = :psqlintvar;

DO $$
BEGIN
  RAISE NOTICE '%', var;
END;
$$;



It will be great. I already commented it in your blog.

-
Pavel Luzanov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Re: psql variables in the DO command

[Pavel Luzanov]

On 05.03.2018 18:35, Pavel Stehule wrote:
I am slowly working on prototype. The work is simple, when variables 
are just scalars. But it is much harder, when we allow composite 
variables. When prototype will be done, I invite any cooperation - 
there are lot of question - and one very hard - where and how the 
variable should be stored (values) - now I have workaround, but it is 
pretty ugly code.

I'm not a big expert on postgres internals, but ready to participate.

-
Pavel Luzanov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Re: functions with side effect

[Pavel Luzanov]

Very interesting question.

postgres=# create sequence s;
CREATE SEQUENCE
postgres=# select currval('s'), nextval('s');
ERROR:  currval of sequence "s" is not yet defined in this session
postgres=# select nextval('s'), currval('s');
 nextval | currval
-+-
   1 |   1

We see different result with different order of functions.
So the question is: in which order expressions evaluated.
And I don't think that we can rely on this order.

Moreover, according to SQL standard[1]:
"If there are multiple instances of s specifying 
the same sequence generator within a
single SQL-statement, all those instances return the same value for a 
given row processed by that SQL-statement."


But in fact nextval return new value each time:

postgres=# select nextval('s'), nextval('s') from generate_series (1,3);
 nextval | nextval
-+-
   2 |   3
   4 |   5
   6 |   7

[1] http://www.wiscorp.com/sql_2003_standard.zip
    5WD-02-Foundation-2003-09.pdf
        4.21.2 Operations involving sequence generators

-
Pavel Luzanov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

On 19.07.2018 19:43, Torsten Förtsch wrote:
On Thu, Jul 19, 2018 at 6:35 PM Adrian Klaver 
mailto:adrian.kla...@aklaver.com>> wrote:


On 07/19/2018 07:15 AM, Torsten Förtsch wrote:
> Hi,
>
> assuming
>
> SELECT nextval('s'), currval('s');
>
> or
>
> SELECT * FROM (VALUES (nextval('s'), currval('s'))) t;
>
> is there any guarantee that the 2 output values are the same?

Assuming you are only working in single session:

https://www.postgresql.org/docs/10/static/functions-sequence.html

"currval

     Return the value most recently obtained by nextval for this
sequence in the current session. (An error is reported if nextval has
never been called for this sequence in this session.) Because this is
returning a session-local value, it gives a predictable answer
whether
or not other sessions have executed nextval since the current
session did."


I know that. My question was about the execution order of f1 and f2 in 
"SELECT f1(), f2()". In theory they can be executed in any order. But 
since the side effect in nextval determines the result of currval, I 
am asking if that order is well-defined or considered an 
implementation detail like in C.

Runtime partition pruning with hash partitioning

[Pavel Luzanov]

Hello,

Runtime partition pruning don't work without index on a hash partitioned 
column.

Consider this test case on version 12:

create table data (
  key_id    integer not null,
  value real not null
) partition by hash(key_id);

create table data_0 partition of data for values with (modulus 3, 
remainder 0);
create table data_1 partition of data for values with (modulus 3, 
remainder 1);
create table data_2 partition of data for values with (modulus 3, 
remainder 2);


insert into data (key_id, value)
select floor(random() * 100), random()
from generate_series(0, 100) as g (i);

vacuum analyze data;

explain (analyze, settings, costs off, timing off, summary off)
with keys(id) as (
    values (1),(2)
) select * from data join keys on (data.key_id = keys.id);
 QUERY PLAN

 Gather (actual rows=19845 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Hash Join (actual rows=6615 loops=3)
 Hash Cond: (data_2.key_id = "*VALUES*".column1)
 ->  Parallel Append (actual rows=34 loops=3)
   ->  Parallel Seq Scan on data_2 (actual rows=126670 loops=3)
   ->  Parallel Seq Scan on data_1 (actual rows=160458 loops=2)
   ->  Parallel Seq Scan on data_0 (actual rows=299075 loops=1)
 ->  Hash (actual rows=2 loops=3)
   Buckets: 1024  Batches: 1  Memory Usage: 5kB
   ->  Values Scan on "*VALUES*" (actual rows=2 loops=3)

We see that all partitions scanned. But after creating index postgres 
actually looks only to selected partitions:


create index on data(key_id);

explain (analyze, settings, costs off, timing off, summary off)
with keys(id) as (
    values (1),(2)
) select * from data join keys on (data.key_id = keys.id);
 QUERY PLAN
-
 Nested Loop (actual rows=19845 loops=1)
   ->  Values Scan on "*VALUES*" (actual rows=2 loops=1)

   ->  Append (actual rows=9922 loops=2)
 ->  Bitmap Heap Scan on data_0 (actual rows=9926 loops=1)
   Recheck Cond: (key_id = "*VALUES*".column1)
   Heap Blocks: exact=1324
   ->  Bitmap Index Scan on data_0_key_id_idx (actual 
rows=9926 loops=1)

 Index Cond: (key_id = "*VALUES*".column1)
 ->  Bitmap Heap Scan on data_1 (never executed)
   Recheck Cond: (key_id = "*VALUES*".column1)
   ->  Bitmap Index Scan on data_1_key_id_idx (never executed)
 Index Cond: (key_id = "*VALUES*".column1)
 ->  Bitmap Heap Scan on data_2 (actual rows=9919 loops=1)
   Recheck Cond: (key_id = "*VALUES*".column1)
   Heap Blocks: exact=1679
   ->  Bitmap Index Scan on data_2_key_id_idx (actual 
rows=9919 loops=1)

     Index Cond: (key_id = "*VALUES*".column1)

Why runtime partition pruning needs index? Is it intended behavior?

--

-
Pavel Luzanov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Re: n_distinct off by a factor of 1000

[Pavel Luzanov]

Hello,

I got my first hint of why this problem occurs when I looked at the 
statistics.  For the column in question, "instrument_ref" the 
statistics claimed it to be:


The default_statistics_target=500, and analyze has been run.
select * from pg_stats where attname like 'instr%_ref'; -- Result: 
*40.000*
select count(distinct instrumentid_ref) from bigtable -- Result: *33 
385 922 (!!)*


That is an astonishing difference of almost a 1000X.


I have tried to increase the statistics target to 5000, and it helps, 
but it reduces the error to 100X.  Still crazy high.


As far as I know, increasing default_statistics_target will not help. [1]


I have considered these fixes:
- hardcode the statistics to a particular ratio of the total number of 
rows


You can hardcode the percentage of distinct values:
ALTER TABLE bigtable ALTER COLUMN instrument_ref SET ( n_distinct=-0.06 
); /* -1 * (33385922 / 5) */



[1] 
https://www.postgresql.org/message-id/4136ffa0812111823u645b6ec9wdca60b3da4b00499%40mail.gmail.com


-
Pavel Luzanov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Re: how to implement add using upsert and trigger?

[Pavel Luzanov]

Hi, I think you need:

    insert into stat_result(itemid,value,cnt) values(new.itemid, 
new.value, 1)

    on conflict(itemid) do update
    set value = stat_result.value + new.value, cnt = stat_result.cnt +1;

excluded.value and new.value is the same value from inserted record, but 
your need a current value from stat_result.


On 28.11.2022 15:37, yin@163.com wrote:

Hi, all：
  when I using upsert and trigger to update other table automatically：
create table stat_detail(itemid bigint not null, value bigint, captime 
bigint);
create table stat_result(itemid bigint primary key, value bigint, cnt 
bigint);

create or replace function inertfunc() returns trigger as $$
begin
insert into stat_result(itemid,value,cnt) values(new.itemid, new.value, 1)
on conflict(itemid) do update
set value = excluded.value + new.value, cnt = excluded.cnt +1 where 
excluded.itemid = new.itemid;

return new;
end;
$$
language plpgsql;
create trigger tri_insert after insert on stat_detail for each row 
execute function inertfunc();

postgres=# insert into stat_detail values(100,1,1);
INSERT 0 1
postgres=# select * from stat_result ;
 itemid | value | cnt
+---+-
100 | 1 | 1
(1 row)
postgres=# insert into stat_detail values(100,1,1);
INSERT 0 1
postgres=# select * from stat_result ;
 itemid | value | cnt
+---+-
100 | 2 | 2
(1 row)
postgres=# insert into stat_detail values(100,1,1);
INSERT 0 1
postgres=# select * from stat_result ;
 itemid | value | cnt
+---+-
100 | 2 | 2
(1 row)

But  I want it is "100 3 3". So how I can do?


yin@163.com


--
Pavel Luzanov
Postgres Professional:https://postgrespro.com
The Russian Postgres Company

Re: cannot find PGXS file when installing an extension?

[Pavel Luzanov]

Luca,

'sudo make install' command works in the environment of the superuser 
and most likely does not see pg_config utility .


Specify the location of pg_config explicitly:
sudo make install PG_CONFIG=/opt/pg11/bin/pg_config

-----
Pavel Luzanov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

On 02.11.2018 14:17, Luca Ferrari wrote:

I'm using 11.0 on FreeBSD:

testdb=> select version();
  version
-
  PostgreSQL 11.0 on x86_64-unknown-freebsd11.1, compiled by gcc
(FreeBSD Ports Collection) 6.4.0, 64-bit

I've a very basic extension made by my own:

cleopatra% ls
Makefilefluca--1.0.sql  fluca.control

cleopatra% cat Makefile
EXTENSION = fluca
DATA = fluca--1.0.sql

PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

but when I launch make I got an error about a "cannot find" on the
line of the include directive:

cleopatra% sudo make install
make: "/usr/home/luca/tmp/fluca/Makefile" line 6: Could not find
make: Fatal errors encountered -- cannot continue
make: stopped in /usr/home/luca/tmp/fluca


but seems to me everything is in place:

cleopatra% pg_config --pgxs
/opt/pg11/lib/postgresql/pgxs/src/makefiles/pgxs.mk


I've then tried to force the inclusion of the file adding it to the Makefile:

cleopatra% cat Makefile
EXTENSION = fluca
DATA = fluca--1.0.sql

PG_CONFIG = pg_config
include /opt/pg11/lib/postgresql/pgxs/src/makefiles/pgxs.mk


which produces a verbose error output:

cleopatra% sudo make install
make: "/opt/pg11/lib/postgresql/pgxs/src/makefiles/pgxs.mk" line 63:
Need an operator
make: "/opt/pg11/lib/postgresql/pgxs/src/makefiles/pgxs.mk" line 64:
Need an operator
make: Unknown modifier ' '
make: "/opt/pg11/lib/postgresql/pgxs/src/makefiles/pgxs.mk" line 65:
Need an operator
make: "/opt/pg11/lib/postgresql/pgxs/src/makefiles/pgxs.mk" line 66:
Need an operator
make: "/opt/pg11/lib/postgresql/pgxs/src/makefiles/pgxs.mk" line 67:
Need an operator
make: "/opt/pg11/lib/postgresql/pgxs/src/makefiles/pgxs.mk" line 70:
Need an operator
make: "/opt/pg11/lib/postgresql/pgxs/src/Makefile.global" line 44:
Need an operator
make: "/opt/pg11/lib/postgresql/pgxs/src/Makefile.global" line 49:
Need an operator
make: "/opt/pg11/lib/postgresql/pgxs/src/Makefile.global" line 51:
Need an operator
make: "/opt/pg11/lib/postgresql/pgxs/src/Makefile.global" line 52:
Missing dependency operator
make: "/opt/pg11/lib/postgresql/pgxs/src/Makefile.global" line 55:
Need an operator
make: "/opt/pg11/lib/postgresql/pgxs/src/Makefile.global" line 58:
Need an operator
make: "/opt/pg11/lib/postgresql/pgxs/src/Makefile.global" line 59:
Need an operator
make: "/opt/pg11/lib/postgresql/pgxs/src/Makefile.global" line 60:
Need an operator
make: "/opt/pg11/lib/postgresql/pgxs/src/Makefile.global" line 65:
Missing dependency operator
make: "/opt/pg11/lib/postgresql/pgxs/src/Makefile.global" line 68:
Need an operator
make: "/opt/pg11/lib/postgresql/pgxs/src/Makefile.global" line 72:
Need an operator
make: "/opt/pg11/lib/postgresql/pgxs/src/Makefile.global" line 73:
Need an operator
make: "/opt/pg11/lib/postgresql/pgxs/src/Makefile.global" line 93:
Need an operator
make: "/opt/pg11/lib/postgresql/pgxs/src/Makefile.global" line 105:
Need an operator
make: "/opt/pg11/lib/postgresql/pgxs/src/Makefile.global" line 106:
Need an operator
make: "/opt/pg11/lib/postgresql/pgxs/src/Makefile.global" line 108:
Need an operator
make: "/opt/pg11/lib/postgresql/pgxs/src/Makefile.global" line 109:
Need an operator
make: "/opt/pg11/lib/postgresql/pgxs/src/Makefile.global" line 112:
Need an operator
make: "/opt/pg11/lib/postgresql/pgxs/src/Makefile.global" line 113:
Need an operator
make: "/opt/pg11/lib/postgresql/pgxs/src/Makefile.global" line 115:
Need an operator
make: "/opt/pg11/lib/postgresql/pgxs/src/Makefile.global" line 116:
Need an operator
make: "/opt/pg11/lib/postgresql/pgxs/src/Makefile.global" line 121:
Need an operator
make: "/opt/pg11/lib/postgresql/pgxs/src/Makefile.global" line 122:
Need an operator
make: "/opt/pg11/lib/postgresql/pgxs/src/Makefile.global" line 124:
Need an operator
make: "/opt/pg11/lib/postgresql/pgxs/src/Makefile.global" line 125:
Need an operator
make: "/opt/pg11/lib/postgresql/pgxs/src/Makefile.global" line 130:
Need an operator
make: "/opt/pg11/lib/postgresql/pgxs/src/Makefile.global" line 131:
Need an operator
make: "/opt/pg11/lib/postgresql/pgxs/src/Makefile.global" line 133:
Need an operator
make: "/opt/pg11/lib/postgresql/pgxs/src/Makefile.global" line 134:
Need an operator
make: "/opt/pg11/lib/postgresql/pgxs/src/Makefile.global" line 139:
Need an operator


What am I missing here?

Thanks,
Luca

Re: conditionally terminate psql script

[Pavel Luzanov]

Hi


is there a way to stop execution of a psql script if a select returns some rows 
(or no rows)
The idea is to add a safety check on data, specifically to select all new rows 
that would conflict
on a bulk insert, show them and stop


Look at \if command in psql (since v10):

select count(*) as total from pg_class where 1 = 1\gset
select :total = 0 as notfound\gset
\if :notfound
   \echo Nothing found.
   \q
\endif
\echo :total records found.

-
Pavel Luzanov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Re: conditionally terminate psql script

[Pavel Luzanov]

On 17.12.2018 16:07, haman...@t-online.de wrote:

Hi, many thanks -- too bad I am still using 9.3


In this case you can try ON_ERROR_STOP psql variable.
Something like this:

\set ON_ERROR_STOP on

do $$
declare
    total bigint;
begin
    select count(*) into total from pg_class where 1=1;
    if total = 0 then
    raise exception 'Nothing found.';
    end if;

    raise notice '% records found.', total;
end;
$$ language plpgsql;

\echo Continue execution...

-
Pavel Luzanov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Re: psql \du no more showing "member of" column

[Pavel Luzanov]

Hi,

On 13.10.2023 16:06, Ian Lawrence Barwick wrote:

2023年10月13日(金) 20:47 Luca Ferrari :

Hi all,
in version 16 psql does not show anymore the "member of" group
information when showing users with \du.



FWIW the explanation is in commit 0a1d2a7df8:

   
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=0a1d2a7df852f16c452eef8a83003943125162c7

and also noted in the release notes:

   https://www.postgresql.org/docs/16/release-16.html#RELEASE-16-PSQL


Some explanation can be found at the beginning of this article:

    https://postgrespro.com/blog/pgsql/5970285

--
Pavel Luzanov
Postgres Professional: https://postgrespro.com

Re: Trouble with v16 new CREATEROLE semantic

[Pavel Luzanov]

Hi,

On 15.02.2024 20:07, Dominique Devienne wrote:

And now with V16.1 ===


ddevienne=> create role zowner nologin createrole; -- owner of app's 
schemas and manager of related roles

CREATE ROLE



ddevienne=> set role zowner;
ERROR:  permission denied to set role "zowner"
ddevienne=> select roleid::regrole::text, member::regrole::text, 
grantor::regrole::text, admin_option, set_option, inherit_option from 
pg_auth_members where roleid::regrole::text like 'z%' or 
member::regrole::text like 'z%';
 roleid |  member   | grantor  | admin_option | set_option | 
inherit_option

+---+--+--++
 zowner | ddevienne | postgres | t            | f          | f
(1 row)


You can use new psql command \drg for this query.


(2 rows)
ddevienne=> set role zowner;
SET
ddevienne=> create role zadmin nologin noinherit in role zowner; -- 
means to become zowner explicitly

ERROR:  permission denied to grant role "zowner"
DETAIL:  Only roles with the ADMIN option on role "zowner" may grant 
this role.

ddevienne=>

So first surprise in V16. Despite having admin_option, from being the 
creator of the zowner role, I can't SET ROLE to it. I have to 
explicitly add the SET privilege.


Yes, but you can automate it with setting new parameter createrole_self_grant.

postgres@demo=# create role ddevienne login createrole;
CREATE ROLE
postgres@demo=# alter role ddevienne set createrole_self_grant = 'INHERIT, SET';
ALTER ROLE
postgres@demo=# \c - ddevienne
You are now connected to database "demo" as user "ddevienne".
ddevienne@demo=> create role zowner nologin createrole;
CREATE ROLE
ddevienne@demo=> \drg ddevienne
   List of role grants
 Role name | Member of |   Options|  Grantor
---+---+--+---
 ddevienne | zowner| INHERIT, SET | ddevienne
 ddevienne | zowner| ADMIN| postgres
(2 rows)

ddevienne@demo=> set role zowner;
SET

And then, when ddevienne SET ROLE's to zowner, and tries to create 
zadmin *and* add it at the same time as a member of zowner (the 
current_role), it fails.


So it looks like, despite ddevienne having admin_option on zowner, 
because it is on a "different line" than the set_option, it still 
cannot add members in zowner???


Behaviorchanged  <https://github.com/postgres/postgres/commit/79de9842>  for 
security reasons in v15. From Release Notes:
    > Remove the default ADMIN OPTION privilege a login role has on its own 
role membership (Robert Haas)
    > Previously, a login role could add/remove members of its own role, even 
without ADMIN OPTION privilege.

Zowner can create zadmin, but no way to grant membership in itself.

What you can do is create a role zadmin by ddevienne:

ddevienne@demo=> reset role;
RESET
ddevienne@demo=> create role zadmin nologin noinherit;
CREATE ROLE
ddevienne@demo=> grant zowner to zadmin with inherit true, set true;
GRANT ROLE
ddevienne@demo=> \drg zadmin
   List of role grants
 Role name | Member of |   Options|  Grantor
---+---+--+---
 zadmin| zowner| INHERIT, SET | ddevienne
(1 row)

--
Pavel Luzanov
Postgres Professional:https://postgrespro.com

Re: Users and object privileges maintenance

[Pavel Luzanov]

On 18.02.2024 15:19, Dominique Devienne wrote:
On Sun, Feb 18, 2024 at 12:30 PM Laurenz Albe 
 wrote:


2. In PostgreSQL, there is the important concept of ownership,
which is not tied to the schema.
   The owner is the user who created the object.


Personally I find that confusing. I wouldn't mind schema objects all 
belonging to the one owner.
Or being to enforce that, as an opt-in option. Not sure what's the 
benefits of different owners for a schemas objects are.


The situation is even more confusing :-)
Roles with membership in object owner role acts as an owner.
For example they can modify or drop object or grant/revoke access to object.

--
Pavel Luzanov
Postgres Professional:https://postgrespro.com

Re: Users and object privileges maintenance

[Pavel Luzanov]

On 18.02.2024 17:40, Dominique Devienne wrote:
Well, membership in a role mean you can "become that role", no? Thus 
this seems logical,
and not confusing to me, that you can act as the owner, since you SET 
ROLE to the owner.


They may acts as the owner even without explicit SET ROLE to the owner.
It wasn't obvious to me when I came to postgres from oracle.

--
Pavel Luzanov
Postgres Professional:https://postgrespro.com

Re: v16 roles, SET FALSE, INHERIT FALSE, ADMIN FALSE

[Pavel Luzanov]

On 08.07.2024 22:22, Christophe Pettus wrote:

This is more curiosity than anything else.  In the v16 role system, is there 
actually any reason to grant membership in a role to a different role, but with 
SET FALSE, INHERIT FALSE, and ADMIN FALSE?  Does the role granted membership 
gain any ability it didn't have before in that case?


Looks like there is one ability.
Authentication in pg_hba.conf "USER" field via +role syntax.

--
Pavel Luzanov
Postgres Professional:https://postgrespro.com

Re: v16 roles, SET FALSE, INHERIT FALSE, ADMIN FALSE

[Pavel Luzanov]

On 09.07.2024 00:16, Tom Lane wrote:

Pavel Luzanov  writes:

On 08.07.2024 22:22, Christophe Pettus wrote:

This is more curiosity than anything else.  In the v16 role system, is there 
actually any reason to grant membership in a role to a different role, but with 
SET FALSE, INHERIT FALSE, and ADMIN FALSE?  Does the role granted membership 
gain any ability it didn't have before in that case?

Looks like there is one ability.
Authentication in pg_hba.conf "USER" field via +role syntax.

Hmm, if that check doesn't require INHERIT TRUE I'd say it's
a bug.

regards, tom lane


My test scenario:

postgres@demo(16.3)=# select * from pg_hba_file_rules ;
 rule_number |  file_name  | line_number | type  | 
database | user_name  | address | netmask | auth_method | options | error
-+-+-+---+--++-+-+-+-+---
   1 | /etc/postgresql/16/main/pg_hba.conf | 118 | local | 
{all}| {postgres} | | | trust   | |
   2 | /etc/postgresql/16/main/pg_hba.conf | 121 | local | 
{all}| {+bob} | | | trust   | |
   3 | /etc/postgresql/16/main/pg_hba.conf | 122 | local | 
{all}| {alice}| | | reject  | |
(3 rows)

postgres@demo(16.3)=# \drg
List of role grants
 Role name | Member of | Options | Grantor
---+---+-+--
 alice | bob   | | postgres
(1 row)

postgres@demo(16.3)=# \c - alice
You are now connected to database "demo" as user "alice".
alice@demo(16.3)=>


--
Pavel Luzanov
Postgres Professional:https://postgrespro.com

Re: insufficient privilege with pg_read_all_stats granted

[Pavel Luzanov]

On 20.08.2024 23:50, Costa Alexoglou wrote:
I run PostgreSQL v15.8 (docker official image), and there is an issue 
when reading pg_stat_staments table with a result of query most of the 
times having `` value.


I have created the user that I use to fetch the data with the 
following way:

```
CREATE USER abcd WITH NOSUPERUSER NOCREATEROLE NOINHERIT LOGIN;

GRANT pg_read_all_stats, pg_stat_scan_tables, pg_read_all_settings to 
abcd;


I think the problem is in the NOINHERIT attribute for the abcd role.
abcd does not inherit the privileges gained from being included in other roles.

In v15, to see the text of SQL commands in pg_stat_statements, you can either 
explicitly
switch from abcd role to the pg_read_all_stats role (SET ROLE pg_read_all_stats)
or set the INHERIT attribute for abcd role (alter role abcd inherit).

In v16, you can explicitly specify how to get privileges in the GRANT command:

grant pg_read_all_stats to abcd with inherit true, set false;




I also tried with PostgreSQL v14.13, and this was not the case, it was 
working fine as expected.
Then I tried v16.4 and v17beta3, and I faced the privilege> issue, so I guess something changed v15 onwards?


But I don't understand why it worked in v14.
Probablysomethinghas changed,  butIcouldn't quicklyfindwhatexactly.

--
Pavel Luzanov
Postgres Professional:https://postgrespro.com

Re: insufficient privilege with pg_read_all_stats granted

[Pavel Luzanov]

On 21.08.2024 10:50, Pavel Luzanov wrote:

But I don't understand why it worked in v14.
Probablysomethinghas changed,  butIcouldn't quicklyfindwhatexactly.


Ifoundit.
https://github.com/postgres/postgres/commit/6198420a

--
Pavel Luzanov
Postgres Professional:https://postgrespro.com

PG17 optimizations to vacuum

[Pavel Luzanov]

Hello,

While playing with optimizations to vacuum in v17 I can't understand
how to measure this one:
"Allow vacuum to more efficiently remove and freeze tuples".

My test script and results:

CREATE TABLE t(id integer) WITH (autovacuum_enabled = off);
INSERT INTO t SELECT gen.id FROM generate_series(1,3_500_000) gen(id);
CREATE INDEX t_id ON t(id);
SET maintenance_work_mem = '1MB';
UPDATE t SET id = id + 1;
VACUUM FREEZE VERBOSE t;


v16.4
INFO:  aggressively vacuuming "postgres.public.t"
INFO:  finished vacuuming "postgres.public.t": index scans: 21
pages: 0 removed, 30974 remain, 30974 scanned (100.00% of total)
tuples: 350 removed, 350 remain, 0 are dead but not yet removable
removable cutoff: 1675, which was 0 XIDs old when operation ended
new relfrozenxid: 1675, which is 4 XIDs ahead of previous value
frozen: 15488 pages from table (50.00% of total) had 350 tuples frozen
index scan needed: 15487 pages from table (50.00% of total) had 350 dead 
item identifiers removed
index "t_id": pages: 19196 in total, 0 newly deleted, 0 currently deleted, 0 
reusable
avg read rate: 473.207 MB/s, avg write rate: 92.511 MB/s
buffer usage: 212718 hits, 267930 misses, 52380 dirtied
WAL usage: 96585 records, 42819 full page images, 198029405 bytes
system usage: CPU: user: 3.17 s, system: 0.48 s, elapsed: 4.42 s
VACUUM

master
INFO:  aggressively vacuuming "postgres.public.t"
INFO:  finished vacuuming "postgres.public.t": index scans: 1
pages: 0 removed, 30974 remain, 30974 scanned (100.00% of total)
tuples: 350 removed, 350 remain, 0 are dead but not yet removable
removable cutoff: 950, which was 0 XIDs old when operation ended
new relfrozenxid: 950, which is 4 XIDs ahead of previous value
frozen: 15488 pages from table (50.00% of total) had 350 tuples frozen
index scan needed: 15487 pages from table (50.00% of total) had 350 dead 
item identifiers removed
index "t_id": pages: 19196 in total, 0 newly deleted, 0 currently deleted, 0 
reusable
avg read rate: 101.121 MB/s, avg write rate: 120.530 MB/s
buffer usage: 48900 hits, 47749 reads, 56914 dirtied
WAL usage: 125391 records, 46626 full page images, 330547751 bytes
system usage: CPU: user: 2.90 s, system: 0.27 s, elapsed: 3.68 s
VACUUM


I see a perfectly working TID-store optimization.
With reduced maintenance_work_mem it used only one 'vacuuming indexes'
phase instead of 21 in v16.
But I also expected to see a reduction in the number of WAL records
and the total size of the WAL. Instead, WAL numbers have significantly
degraded.

What am I doing wrong?

--
Pavel Luzanov
Postgres Professional:https://postgrespro.com

Re: PG17 optimizations to vacuum

[Pavel Luzanov]

On 02.09.2024 22:23, Melanie Plageman wrote:

For some reason I stopped being able to reproduce Pavel's case.


I repeated the test on another computer, but compared master with v15.
The results are the same. The test can be simplified as follows:

CREATE TABLE t(id integer) WITH (autovacuum_enabled = off);
INSERT INTO t SELECT gen.id FROM generate_series(1,3_500_000) gen(id);
CREATE INDEX t_id ON t(id);
VACUUM FREEZE t;
UPDATE t SET id = id + 1;
VACUUM FREEZE VERBOSE t;

My results (only line with WAL info from the last VACUUM command).

master:
WAL usage: 119583 records, 37231 full page images, 272631468 bytes

v15:
WAL usage: 96565 records, 47647 full page images, 217144602 bytes


If it helps, without creating index on id column, the numbers will be
much closer:

master:
WAL usage: 78502 records, 22090 full page images, 196215494 bytes

v15:
WAL usage: 77437 records, 30872 full page images, 152080268 bytes

--
Pavel Luzanov
Postgres Professional:https://postgrespro.com

Re: PG17 optimizations to vacuum

[Pavel Luzanov]

On 03.09.2024 00:11, Heikki Linnakangas wrote:
Pavel, did you test v17 with checksums enabled and v16 with checksums 
disabled, by any chance?


Exactly, You are right!

My v16 cluster comes from the default Ubuntu distribution.
I forgot that checksums disabled by default.
But when I initialize the master cluster, I automatically set -k option.

More accurate results for the test:

CREATE TABLE t(id integer) WITH (autovacuum_enabled = off);
INSERT INTO t SELECT gen.id FROM generate_series(1,3_500_000) gen(id);
VACUUM FREEZE t;
UPDATE t SET id = id + 1;
VACUUM FREEZE VERBOSE t;

checksums disabled
v16.4   WAL usage: 77437 records, 20832 full page images, 110654467 bytes
master  WAL usage: 61949 records, 20581 full page images, 92549229 bytes

checksums enabled
v16.4   WAL usage: 92848 records, 20985 full page images, 194863720 bytes
master  WAL usage: 76520 records, 20358 full page images, 181867154 bytes

This a great optimization!

Peter, Melanie, Heikki,Thankyouvery much foryour helpandtimespent!Sorryforthe 
noisebeforethe releaseof PG17.

=
I don't thinkit'snecessaryanymore.Butjust incase.

Non-default settings for v16

postgres@postgres(16.4)=# \dconfig
 List of non-default configuration parameters
 Parameter  |  Value
+-
 application_name   | psql
 client_encoding| UTF8
 cluster_name   | 16/main
 config_file| /etc/postgresql/16/main/postgresql.conf
 data_directory | /var/lib/postgresql/16/main
 DateStyle  | ISO, DMY
 default_text_search_config | pg_catalog.english
 external_pid_file  | /var/run/postgresql/16-main.pid
 hba_file   | /etc/postgresql/16/main/pg_hba.conf
 ident_file | /etc/postgresql/16/main/pg_ident.conf
 lc_messages| en_US.UTF-8
 lc_monetary| ru_RU.UTF-8
 lc_numeric | ru_RU.UTF-8
 lc_time| ru_RU.UTF-8
 log_line_prefix| %m [%p] %q%u@%d
 log_timezone   | Europe/Moscow
 port   | 5433
 ssl| on
 ssl_cert_file  | /etc/ssl/certs/ssl-cert-snakeoil.pem
 ssl_key_file   | /etc/ssl/private/ssl-cert-snakeoil.key
 TimeZone   | Europe/Moscow
(21 rows)

Building options and non-default settings for master:

./configure --silent --prefix=/home/pal/master --with-pgport=5401 --with-lz4 
--with-icu --with-zstd --enable-nls --with-libxml --with-llvm
make world --silent -j
make --silent install-world

initdb -k -U postgres

postgres@postgres(18.0)=# \dconfig
List of non-default configuration parameters
 Parameter  | Value
+---
 application_name   | psql
 client_encoding| UTF8
 config_file| /home/pal/master/data/postgresql.conf
 data_directory | /home/pal/master/data
 DateStyle  | ISO, DMY
 default_text_search_config | pg_catalog.english
 hba_file   | /home/pal/master/data/pg_hba.conf
 ident_file | /home/pal/master/data/pg_ident.conf
 lc_messages| en_US.UTF-8
 lc_monetary| ru_RU.UTF-8
 lc_numeric | ru_RU.UTF-8
 lc_time| ru_RU.UTF-8
 log_timezone   | Europe/Moscow
 TimeZone   | Europe/Moscow
(14 rows)

--
Pavel Luzanov
Postgres Professional:https://postgrespro.com

Re: Backward compat issue with v16 around ROLEs

[Pavel Luzanov]

On 11.09.2024 22:21, David G. Johnston wrote:


> ddevienne=> grant dd_owner to dd_admin with admin option; --
<<<<<<<<

I think this needs to be the other way around:

   grant dd_admin to dd_owner with admin option;

Best,

Wolfgang


Probably, intend to get those reversed and wasn't in a position to 
experiment.  In any case fixing the with admin error is the correct 
approach.


Unfortunately,itwon'twork. Dominiqueis right.Thiswill leadtocircularities. 
After this grant:

grant dd_owner to dd_admin;

reverse grant is not possible.
I thinkthisis a migrationissueforv16and it is not mentioned in release 
notes.


Ididn'tquiteunderstandthe exactpurposeof the roles dd_owner and dd_admin.  
But apossibleway is to use dd_admin to create roles. For example:


create role dd_admin login createrole;
\c - dd_admin
create role dd_owner noinherit;

create role dd_user login;
grant dd_owner to dd_user;

\c - dd_user
set role dd_owner;

--
Pavel Luzanov
Postgres Professional:https://postgrespro.com

Re: Backward compat issue with v16 around ROLEs

[Pavel Luzanov]

On 13.09.2024 00:11, Robert Haas wrote:


The prohibition against circular grants is really annoying in your use
case. If dd_owner creates dd_user, then dd_user is granted to
dd_owner, which means that dd_owner cannot be granted (directly or
indirectly) to dd_user.


In search of workaround...

So, now in v16 we need a third role to made this grants.
There is a not very nice way to use the third role implicitly,
through security definer stored routines.

-- run by superuser
create role dd_owner createrole;
CREATE ROLE

create role dd_admin noinherit;
CREATE ROLE

grant dd_owner to dd_admin;
GRANT ROLE

create procedure create_role (role text, member regrole)
language plpgsql security definer as $$
begin
execute (format('create role %I in role %I', role, member));
end;
$$;
CREATE PROCEDURE

revoke execute on procedure create_role from public;
REVOKE

grant execute on procedure create_role to dd_owner;
GRANT

set role dd_owner;
SET

call create_role('dd_user', 'dd_admin');
CALL

\du dd*
  List of roles
 Role name |  Attributes
---+--
 dd_admin  | No inheritance, Cannot login
 dd_owner  | Create role, Cannot login
 dd_user   | Cannot login

\drg
   List of role grants
 Role name | Member of |   Options| Grantor
---+---+--+--
 dd_admin  | dd_owner  | SET  | postgres
 dd_user   | dd_admin  | INHERIT, SET | postgres
(2 rows)

Ido notknowhowapplicablethisis for Dominique.Perhapsabettersolution is to review  
andmakechangesto roles&grants systemby explicitlyintroducingand using a 
thirdrole.

--
Pavel Luzanov
Postgres Professional:https://postgrespro.com

Strange behavior of function date_trunc

[Pavel Luzanov]

Hello,

It is very likely that the date_trunc function in the following example 
is executed for each line of the query. Although it marked as a STABLE 
and could only be called once.


EXPLAIN (ANALYZE)
SELECT * FROM generate_series('2021-01-01', '2021-06-01', '1 
s'::interval) AS g(x) WHERE g.x >= date_trunc('day', 
'2021-05-05'::timestamptz);

  QUERY PLAN
---
 Function Scan on generate_series g  (cost=0.00..15.00 rows=333 
width=8) (actual time=2801.884..3263.328 rows=2332801 loops=1)
   Filter: (x >= date_trunc('day'::text, '2021-05-05 
00:00:00+03'::timestamp with time zone))

   Rows Removed by Filter: 10713600
 Planning Time: 0.040 ms
 Execution Time: 3336.657 ms

When replacing date_trunc with now, the query is much faster:

EXPLAIN (ANALYZE)
SELECT * FROM generate_series('2021-01-01', '2021-06-01', '1 
s'::interval) AS g(x)

WHERE g.x >= now();
  QUERY PLAN
---
 Function Scan on generate_series g  (cost=0.00..15.00 rows=333 
width=8) (actual time=1648.777..1845.430 rows=2275325 loops=1)

   Filter: (x >= now())
   Rows Removed by Filter: 10771076
 Planning Time: 0.039 ms
 Execution Time: 1918.767 ms

The variant with now works almost as fast as with the constant. This 
suggests me that perhaps date_trunc is being executed for every line of 
the query:


EXPLAIN (ANALYZE)
SELECT * FROM generate_series('2021-01-01', '2021-06-01', '1 
s'::interval) AS g(x)

WHERE g.x >= '2021-05-05'::timestamptz;
  QUERY PLAN
---
 Function Scan on generate_series g  (cost=0.00..12.50 rows=333 
width=8) (actual time=1628.743..1826.841 rows=2332801 loops=1)

   Filter: (x >= '2021-05-05 00:00:00+03'::timestamp with time zone)
   Rows Removed by Filter: 10713600
 Planning Time: 0.033 ms
 Execution Time: 1901.680 ms

In this regard, I have two questions:
1. How can I find out exactly how many times the date_trunc function has 
been executed? So far, these are just my assumptions.

2. If date_trunc is indeed called multiple times, why is this happening?

--
Pavel Luzanov
Postgres Professional: https://postgrespro.com
The Russian Postgres Company

Re: Strange behavior of function date_trunc

[Pavel Luzanov]

Hello,

On 05.05.2021 16:55, Tomas Vondra wrote:
Well, it'd not like date_trunc is executed for each row while now() is 
executed only once. The functions are executed for each row in both 
cases, but now() is simply much cheaper - it just returns a value that 
is already calculated, while date_trunc has to parse and truncate the 
value, etc.




Thanks for the explanation.



You can use CTE to execute it just once, I think:

  with x as (select date_trunc('day', '2021-04-01'::timestamptz) as x)
  select * from t where a > (select x from x);



I think it could be even easier with scalar subquery:

EXPLAIN (ANALYZE)
SELECT * FROM generate_series('2021-01-01', '2021-06-01', '1 
s'::interval) AS g(x)

WHERE g.x >= (SELECT date_trunc('day', '2021-05-05'::timestamptz));
  QUERY PLAN
---
 Function Scan on generate_series g  (cost=0.02..12.51 rows=333 
width=8) (actual time=1615.436..1815.724 rows=2332801 loops=1)

   Filter: (x >= $0)
   Rows Removed by Filter: 10713600
   InitPlan 1 (returns $0)
 ->  Result  (cost=0.00..0.01 rows=1 width=8) (actual 
time=0.005..0.005 rows=1 loops=1)

 Planning Time: 0.051 ms
 Execution Time: 1889.434 ms

--
Pavel Luzanov
Postgres Professional: https://postgrespro.com
The Russian Postgres Company

Re: Strange behavior of function date_trunc

[Pavel Luzanov]

On 05.05.2021 17:11, Tom Lane wrote:

Tomas Vondra  writes:

On 5/5/21 3:23 PM, Pavel Luzanov wrote:
It is very likely that the date_trunc function in the following 
example is executed for each line of the query. Although it marked 
as a STABLE and could only be called once. 
It could, but that's just an option - the database may do that, but 
it's not required to do it. In this case it might be beneficial, but 
it'd make the planner more complex etc. 
Yeah, there simply is not any provision for caching the results of 
stable functions in the way Pavel seems to be imagining. People have 
played around with patches for that, but nothing's been accepted.


Thank you for the clarification. It's not very obvious, at least for me.

--
Pavel Luzanov
Postgres Professional: https://postgrespro.com
The Russian Postgres Company

Re: Strange behavior of function date_trunc

[Pavel Luzanov]

7;2021-01-01'::timestamptz;
   QUERY PLAN
-
 Seq Scan on t  (cost=0.00..259011.00 rows=13046480 width=31) (actual 
time=2.135..913.628 rows=13046401 loops=1)

   Filter: (x >= '2021-01-01 00:00:00+03'::timestamp with time zone)
 Settings: random_page_cost = '1.1'
 Planning Time: 0.140 ms
 JIT:
   Functions: 2
   Options: Inlining false, Optimization false, Expressions true, 
Deforming true
   Timing: Generation 0.392 ms, Inlining 0.000 ms, Optimization 0.163 
ms, Emission 1.837 ms, Total 2.391 ms

 Execution Time: 1195.985 ms


The statistics on t.x shows that the condition in the query is not 
selective and seq scan are preferred over index scan.

SELECT n_distinct, (histogram_bounds::text::text[])[1]
FROM pg_stats WHERE tablename = 't' AND attname = 'x';
 n_distinct |    histogram_bounds
+
 -1 | 2021-01-01 00:01:10+03

--
Pavel Luzanov
Postgres Professional: https://postgrespro.com
The Russian Postgres Company

Re: Strange behavior of function date_trunc

[Pavel Luzanov]

Hello,

On 06.05.2021 16:44, Tom Lane wrote:

Pavel Luzanov  writes:
Does having an index allow the function value to be cached? 
For an indexscan, the comparison value is evaluated once and used to 
search the index. The point of the "stable" marking is actually to 
promise that this will give the same result as the naive 
interpretation of a WHERE clause, ie that the WHERE expression is 
notionally evaluated at every row. This case is the reason we invented 
the "stable" attribute to begin with. People have since misinterpreted 
it as authorizing caching of function results, but that's not what it 
was intended for.


I think I'm starting to understand! ))

I knew that the STABLE mark was not a guarantee for the value to be 
cached. The planner has the right to execute the function once, but this 
is not required. Now it is clear under what conditions this happens. 
Stable functions can be executed once, when they are used in an index 
expression. In other cases (in a select list, expression for seq scan) 
they are evaluated for each row.


The second question. What is the reason for choosing an index scan? 
Probably the planner is picking that precisely to reduce the number of 
calls of the user-defined function. Since you left the function's cost 
as default, which for PL functions is 100x the default cost of a 
built-in function, that could well be a large enough number to change 
the plan choice. (You could experiment with altering the COST property 
to see where the plan changes.)


Yes, if the cost of the function is reduced to 3 (or less), than seq 
scan begins to be used. And the function is executed for each row.

It's clear now.

One thing remains unclear.
Why, if a scalar subquery is used to materialize the function value(even 
constant), then an inefficient index scan is chosen:


EXPLAIN (ANALYZE, SETTINGS)
SELECT * FROM t
WHERE t.x >= (SELECT '2021-01-01'::timestamptz);
    QUERY PLAN
--
 Index Scan using t_x_idx on t  (cost=0.45..194740.46 rows=4348742 
width=31) (actual time=2.831..26947.394 rows=13046401 loops=1)

   Index Cond: (x >= $0)
   InitPlan 1 (returns $0)
 ->  Result  (cost=0.00..0.01 rows=1 width=8) (actual 
time=0.005..0.005 rows=1 loops=1)

 Settings: random_page_cost = '1.1'
 Planning Time: 0.077 ms
 JIT:
   Functions: 4
   Options: Inlining false, Optimization false, Expressions true, 
Deforming true
   Timing: Generation 0.720 ms, Inlining 0.000 ms, Optimization 0.184 
ms, Emission 2.429 ms, Total 3.333 ms

 Execution Time: 27262.793 ms

--
Pavel Luzanov
Postgres Professional: https://postgrespro.com
The Russian Postgres Company

Re: Strange behavior of function date_trunc

[Pavel Luzanov]

I will try to summarize what was said before.

We have discussed the details of executing STABLE functions in queries 
of the form:

SELECT * FROM t WHERE col oper stable_func();

* Checking STABLE does not guarantee that the function will be executed 
only once. If the table is scanned sequentially, the function is 
executed for each row of the query.


* If the table has an index on the col column, the planner can choose to 
scan the index. In this case, the STABLE mark gives the right to 
calculate the function value once and use that value to search the index.


* In the case of a sequential scan, the total cost of the plan includes, 
among other things, the cost of the function multiplied by the number of 
rows.  For user-defined functions, the default cost is 100. It may be 
worth changing this value for a more adequate estimate. Decreasing the 
cost of a function will decrease the cost of a seq scan and vice versa. 
Refining the function cost estimate will enable the planner to make a 
more accurate choice between seq scan and index scan.


* If seq scan is preferred, you can avoid executing the function 
multiple times by materializing the result of the function.


* There are two ways to materialize the result: a scalar subquery and a CTE.
    SELECT * FROM t WHERE col oper (SELECT stable_func();
    WITH m AS MATERIALIZED (SELECT stable_func() AS f) SELECT * FROM t, 
m WHERE col oper m.f;


* When materializing a function result, the planner has no way to use 
the function value to build the plan. Therefore, it will not be able to 
use the statistics for the t.col to select the optimal plan.  The 
generic algorithm will be used.


Thank you very much for sharing.

--
Pavel Luzanov
Postgres Professional: https://postgrespro.com
The Russian Postgres Company

Re: Strange behavior of function date_trunc

[Pavel Luzanov]

David,


On 06.05.2021 17:28, David G. Johnston wrote:
On Thu, May 6, 2021 at 6:44 AM Tom Lane <mailto:t...@sss.pgh.pa.us>> wrote:


This case is the reason we invented the "stable" attribute to begin
with.  People have since misinterpreted it as authorizing caching of
function results, but that's not what it was intended for.


This is a good paragraph...if something like it gets added to the 
create function documentation mis-interpretations are likely to decrease.




I found additional details in the documentation. In particular about the 
index scanning for stable functions:

https://www.postgresql.org/docs/13/xfunc-volatility.html

The link to this section there is in the create function page. Maybe 
that's enough.


--
Pavel Luzanov
Postgres Professional: https://postgrespro.com
The Russian Postgres Company

Re: EXPLAIN with anonymous DO block?

[Pavel Luzanov]

Good day!

There's a query inside a DO block which -- because it's parameterized -- I'd
rather analyze while it's in the FOR loop of a DO block, instead of pulling
it out and hard-coding the parameters.
Is this possible?

No.


Why not to use auto_explain module?

postgres=# LOAD 'auto_explain';
LOAD
postgres=# SET auto_explain.log_min_duration = 0;
SET
postgres=# SET auto_explain.log_nested_statements = on;
SET
postgres=# SET auto_explain.log_analyze = on;
SET
postgres=# SET auto_explain.log_level = 'NOTICE';
SET
postgres=# DO $$BEGIN FOR i IN 112 .. 113 LOOP PERFORM * FROM pg_class 
WHERE oid = i::oid; END LOOP; END;$$;

NOTICE:  duration: 0.013 ms  plan:
Query Text: SELECT * FROM pg_class WHERE oid = i::oid
Index Scan using pg_class_oid_index on pg_class  (cost=0.27..8.29 rows=1 
width=265) (actual time=0.009..0.011 rows=1 loops=1)

  Index Cond: (oid = '112'::oid)
NOTICE:  duration: 0.016 ms  plan:
Query Text: SELECT * FROM pg_class WHERE oid = i::oid
Index Scan using pg_class_oid_index on pg_class  (cost=0.27..8.29 rows=1 
width=265) (actual time=0.008..0.009 rows=1 loops=1)

  Index Cond: (oid = '113'::oid)
DO

Pavel Luzanov
Postgres Professional: https://postgrespro.com
The Russian Postgres Company

Re: psql meta command

[Pavel Luzanov]

On 10.04.2025 15:29, Marc Millas wrote:
on a customer postgres db, using psql, if I post \  to initiate, for 
exemple \x

that psql immediately exits.
somewhat boring as it blocks all psql meta commands.
Is there a way to get the extended display without posting a \  ?


You can enable extended output when starting psql:

$ psql -x -c 'select 1,2,3'
-[ RECORD 1 ]
?column? | 1
?column? | 2
?column? | 3

Also, not sure but you can try hide \ in psql variable:

$ psql --set expanded_mode='\x'
psql (18devel)
Type "help" for help.

postgres@demo(18.0)=# :expanded_mode
Expanded display is on.
postgres@demo(18.0)=# select 1,2,3;
-[ RECORD 1 ]
?column? | 1
?column? | 2
?column? | 3

--
Pavel Luzanov
Postgres Professional:https://postgrespro.com

Re: does multi host connection string supports login event trigger exception

[Pavel Luzanov]

Hi, legrand!


On 19.06.2025 22:31, legrand legrand wrote:
I'm trying to build a login event trigger in conjonction with a multi 
host connectio string

to manager instances roles in a logical replication cluster.


According to docs 
<https://www.postgresql.org/docs/current/libpq-connect.html#:~:text=If%20a%20connection%20is%20established%20successfully>:
If a connection is established successfully, but authentication fails, 
the remaining hosts in the list are not tried.


So, I think that this is impossible to use multiple hosts feature of 
libpq with on login trigger to skip some host.


--
Pavel Luzanov
Postgres Professional:https://postgrespro.com