psql variables in the DO command
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
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
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
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
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
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
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
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
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: psql \du no more showing "member of" column
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
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
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
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: how to implement add using upsert and trigger?
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: v16 roles, SET FALSE, INHERIT FALSE, ADMIN FALSE
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
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
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
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
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
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
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: cannot find PGXS file when installing an extension?
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
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
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
Strange behavior of function date_trunc
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
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
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
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
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
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
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?
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
