Dependencies on the system view
Hi all. One of our programmer created a view based on the system view. I tried to explain him, that he created a dependency from the ordinary database to the system object and this is a bad idea. But he is not smart enough. So I need a guru opinion. Is this permissible or will here be a trouble with, for instance, pg_upgrade? CREATE OR REPLACE VIEW public.all_tables AS SELECT n.nspname AS schemaname, c.relname AS tablename, pg_get_userbyid(c.relowner) AS tableowner, c.reltuples AS num_rows, c.relkind, CASE c.relkind WHEN 'f'::"char" THEN 'Foreign table'::text WHEN 'r'::"char" THEN 'Relation'::text WHEN 'i'::"char" THEN 'Index'::text WHEN 'S'::"char" THEN 'Sequence'::text WHEN 't'::"char" THEN 'TOAST'::text WHEN 'v'::"char" THEN 'View'::text WHEN 'm'::"char" THEN 'Materialized view'::text WHEN 'c'::"char" THEN 'Composite type'::text WHEN 'p'::"char" THEN 'Partitioned table'::text WHEN 'I'::"char" THEN 'partitioned Index'::text ELSE NULL::text END AS rel_type, c.relpersistence, CASE c.relpersistence WHEN 'p'::"char" THEN 'permanent'::text WHEN 'u'::"char" THEN 'unlogged'::text WHEN 't'::"char" THEN 'temporary'::text WHEN 'c'::"char" THEN 'constant'::text ELSE NULL::text END AS persistence, t.spcname AS tablespace, c.relhasindex AS hasindexes, c.relhasrules AS hasrules, c.relhastriggers AS hastriggers FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace; -- Olleg
Dependencies on the system view
Hi all. One of our programmer created a view based on the system view. I tried to explain him, that he created a dependency from the ordinary database to the system object and this is a bad idea. But he is not smart enough. So I need a guru opinion. Is this permissible or here will be a troubles with, for instance, pg_upgrade? CREATE OR REPLACE VIEW public.all_tables AS SELECT n.nspname AS schemaname, c.relname AS tablename, pg_get_userbyid(c.relowner) AS tableowner, c.reltuples AS num_rows, c.relkind, CASE c.relkind WHEN 'f'::"char" THEN 'Foreign table'::text WHEN 'r'::"char" THEN 'Relation'::text WHEN 'i'::"char" THEN 'Index'::text WHEN 'S'::"char" THEN 'Sequence'::text WHEN 't'::"char" THEN 'TOAST'::text WHEN 'v'::"char" THEN 'View'::text WHEN 'm'::"char" THEN 'Materialized view'::text WHEN 'c'::"char" THEN 'Composite type'::text WHEN 'p'::"char" THEN 'Partitioned table'::text WHEN 'I'::"char" THEN 'partitioned Index'::text ELSE NULL::text END AS rel_type, c.relpersistence, CASE c.relpersistence WHEN 'p'::"char" THEN 'permanent'::text WHEN 'u'::"char" THEN 'unlogged'::text WHEN 't'::"char" THEN 'temporary'::text WHEN 'c'::"char" THEN 'constant'::text ELSE NULL::text END AS persistence, t.spcname AS tablespace, c.relhasindex AS hasindexes, c.relhasrules AS hasrules, c.relhastriggers AS hastriggers FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace; -- Olleg
PostgreSQL 10.3 and Ubuntu
There is PostgreSQL 10.3 already. I have Ubuntu 17.10 (artful) and there is not a pgdg repository for it. There is PostgreSQL 10.3 in 16.04 LTS (xenial) pgdg repository, but such packages can't be installed on 17.10 (artful) Ubuntu. And there is zesty-pgdg (for 17.04 Ubuntu), works fine for me (for 17.10 Ubuntu), but there are only old PostgreSQL 10+189.pgdg17.04+1. Can you rebuid packages for the zesty or create for the artful with modern 10.3 PostgreSQL?
Re: Locks analysis after-the-fact
On 2018-04-27 10:55, Olivier Macchioni wrote: > Does anyone have an idea on how to process in such a case? > Log statments too. :)
Re: Asynchronous Trigger?
Try to look at PGQ from SkyTools. On 2018-03-30 01:29, Cory Tucker wrote: Is it possible to have the execution of a trigger (or any function) not block the completion of the statement they are associated with? A pattern I had hoped to implement was to do a quick update of rows that signaled they needed attention, and then an async per-row trigger would come and do the maintenance (in this case, make an expensive materialized view). Any suggestions welcome. thanks! --Cory
Re: Using random() in update produces same random value for all
Yep, interesting. Checked with PostgreSQL 10.1. => select *,random() from generate_series(1,10); generate_series | random -+--- 1 | 0.308531506918371 2 | 0.126279713585973 3 | 0.984668150078505 4 | 0.884970095474273 5 | 0.692738385871053 6 | 0.290897831786424 7 | 0.914066118188202 8 | 0.031909613404423 9 | 0.574441066011786 10 | 0.631192437838763 (10 rows) => select *,(select * from random()) from generate_series(1,10); generate_series | random -+ 1 | 0.0718352268449962 2 | 0.0718352268449962 3 | 0.0718352268449962 4 | 0.0718352268449962 5 | 0.0718352268449962 6 | 0.0718352268449962 7 | 0.0718352268449962 8 | 0.0718352268449962 9 | 0.0718352268449962 10 | 0.0718352268449962 (10 rows) => select *,(select random()) from generate_series(1,10); generate_series | random -+--- 1 | 0.848611807450652 2 | 0.848611807450652 3 | 0.848611807450652 4 | 0.848611807450652 5 | 0.848611807450652 6 | 0.848611807450652 7 | 0.848611807450652 8 | 0.848611807450652 9 | 0.848611807450652 10 | 0.848611807450652 (10 rows) Looked like random() is "volatile", but in subselect it works like "stable".
Re: Using random() in update produces same random value for all
On 2018-01-22 23:15, Tom Lane wrote: > > It is honored as volatile: it will be re-evaluated every time the > sub-select is re-evaluated. It's just that there's no cause to > re-evaluate the sub-select. > > I poked through the SQL standard to see if it spells out the semantics > of uncorrelated subqueries anywhere, and couldn't find anything relevant > at all. But this is how Postgres has understood the behavior of > sub-selects for a very long time (~20 years). I'm pretty certain > that there are people depending on it to behave this way. > > regards, tom lane The cause exists, the function is volatile and according to definition it must be recalculated every time. But well, one more example. => select generate_series,(select random+generate_series from random()) from generate_series(1,10); generate_series | ?column? -+-- 1 | 1.94367738347501 2 | 2.94367738347501 3 | 3.94367738347501 4 | 4.94367738347501 5 | 5.94367738347501 6 | 6.94367738347501 7 | 7.94367738347501 8 | 8.94367738347501 9 | 9.94367738347501 10 | 10.943677383475 (10 rows) As you can see, sub-select is indeed recalculated, but not random(). And this is may be right, because random() is used as source off data. Another example. => select generate_series,(select random()+generate_series) from generate_series(1,10); generate_series | ?column? -+-- 1 | 1.37678202055395 2 | 2.5316761219874 3 | 3.33511888468638 4 | 4.0293406387791 5 | 5.69305071979761 6 | 6.33374964864925 7 | 7.14478175388649 8 | 8.1831739502959 9 | 9.4472619513981 10 | 10.2977624684572 (10 rows) Here random() is recalculated as sub-select. But in => select *,(select random()) from generate_series(1,10); generate_series | random -+--- 1 | 0.487761380150914 2 | 0.487761380150914 3 | 0.487761380150914 4 | 0.487761380150914 5 | 0.487761380150914 6 | 0.487761380150914 7 | 0.487761380150914 8 | 0.487761380150914 9 | 0.487761380150914 10 | 0.487761380150914 (10 rows) is not. IMHO all this behavior may be not bad, but it must be well documented in manual in section about sub-selects. All sub-select must be documented as "stable" in terms of function definition. And thus will not be surprise.
"trailing junk after numeric literal at or near ""512"""
Hi all. I have PostgreSQL 16.6. The log files are attached as partitions by file_fdw to the SQL table. Common practice. But after the error message "trailing junk after numeric literal at or near ""512""" (vim -b view) was wrote, the reading all log was stopped with error from file_fdw: ERROR: invalid byte sequence for encoding "UTF8": 0xd0 0x22 The reason is the error message in the log file (which must be utf-8) consist of: \x 22 35 31 32 d0 22 " 5 1 2 " and d0 is not a utf-8 character so it block reading whole log file by file_fdw. I think the correct behavior must be the error log function must mask the wrong bytes by the ESC sequences, so PostgreSQL will be able read such log file with help of file_fdw. -- Olleg
Re: Interesting case of IMMUTABLE significantly hurting performance
On 10.04.2025 01:08, Tom Lane wrote: Yeah. The assumption is that you had a reason for marking the function IMMUTABLE and you want the planner to treat it that way even if it isn't really. (There are valid use-cases for that, for instance if you want calls to the function to be constant-folded.) regards, tom lane Well, to_char(bigint, text) indeed not immutable, because in some pattern it uses get information from locale. For instance,'SLDG' patterns. But in case of CREATE OR REPLACE FUNCTION formatted_num_immutable(p_summa bigint) RETURNS text LANGUAGE sql IMMUTABLE STRICT RETURN ltrim(to_char(p_summa, '999 999 999 999 999 999 999 999')); to_char do not use locale information in this pattern. So it is correct conclude that to_char is immutable with this pattern and formatted_num_immutable too. I did not lie to the planner. So this is looked "strange", immutable function marked as immutable function can not be inlined, but exactly the same function marked as volatile do. -- Olleg
Interesting case of IMMUTABLE significantly hurting performance
PostgreSQL 17.4 CREATE OR REPLACE FUNCTION formatted_num_immutable(p_summa bigint) RETURNS text LANGUAGE sql IMMUTABLE STRICT RETURN ltrim(to_char(p_summa, '999 999 999 999 999 999 999 999')); CREATE OR REPLACE FUNCTION formatted_num_stable(p_summa bigint) RETURNS text LANGUAGE sql STABLE STRICT RETURN ltrim(to_char(p_summa, '999 999 999 999 999 999 999 999')); explain analyze select formatted_num_immutable(i) from generate_series(1,100) as i(i); QUERY PLAN -- Function Scan on generate_series i (cost=0.00..262500.00 rows=100 width=32) (actual time=56.892..1548.656 rows=100 loops=1) Planning Time: 0.039 ms JIT: Functions: 4 Options: Inlining false, Optimization false, Expressions true, Deforming true Timing: Generation 0.184 ms (Deform 0.040 ms), Inlining 0.000 ms, Optimization 0.115 ms, Emission 1.570 ms, Total 1.869 ms Execution Time: 1587.741 ms (7 rows) explain analyze select formatted_num_stable(i) from generate_series(1,100) as i(i); QUERY PLAN Function Scan on generate_series i (cost=0.00..17500.00 rows=100 width=32) (actual time=54.993..573.333 rows=100 loops=1) Planning Time: 0.056 ms Execution Time: 598.190 ms (3 rows) First interesting thing is immutable variant has cost in 15 time more, then stable. That's why jit compilation is tuned on. Second, immutable function is working much longer (3 times). And jit is not the reason. => set jit=off; SET => explain analyze select formatted_num_immutable(i) from generate_series(1,100) as i(i); QUERY PLAN -- Function Scan on generate_series i (cost=0.00..262500.00 rows=100 width=32) (actual time=54.888..1537.602 rows=100 loops=1) Planning Time: 0.052 ms Execution Time: 1575.985 ms (3 rows) -- Olleg
