ed_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
notknowhowapplicablethisis for Dominique.Perhapsabettersolution is to review
andmakechangesto roles&grants systemby explicitlyintroducingand using a
thirdrole.
--
Pavel Luzanov
Postgres Professional:https://postgrespro.com
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
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 Luzano
full page images, 152080268 bytes
--
Pavel Luzanov
Postgres Professional:https://postgrespro.com
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
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
n't understand why it worked in v14.
Probablysomethinghas changed, butIcouldn't quicklyfindwhatexactly.
--
Pavel Luzanov
Postgres Professional:https://postgrespro.com
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
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
OLE to the owner.
It wasn't obvious to me when I came to postgres from oracle.
--
Pavel Luzanov
Postgres Professional:https://postgrespro.com
owner.
For example they can modify or drop object or grant/revoke access to object.
--
Pavel Luzanov
Postgres Professional:https://postgrespro.com
ole 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
/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
+---+-
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
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
ing 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
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
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
ution 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
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
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
anning 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
inct=-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
-> 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
;
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
(*) 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
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.postg
rate_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:
- 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
eters. 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 Pro
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:
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
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.
-----
Pav
E 1: do $$begin raise notice '%', :'var'; end;$$;
^
--
-
Pavel Luzanov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
35 matches
Mail list logo
