t 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
.
For example they can modify or drop object or grant/revoke access to object.
--
Pavel Luzanov
Postgres Professional:https://postgrespro.com
vienne@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
: 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
or 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
tinct | 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
eturns $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
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
); /* -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.postgrespro.com
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
- 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
. 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
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
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.
-
Pavel Luzanov
'var'; end;$$;
^
--
-----
Pavel Luzanov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
25 matches
Mail list logo
