statistic target and sample rate

[Luca Ferrari]

Hi all,
I've a doubt about the sampling rate about the statistic target.
Documentation states that:

"The largest statistics target among the columns being analyzed
determines the number of table rows sampled to prepare the statistics.
Increasing the target causes a proportional increase in the time and
space needed to do ANALYZE."

My doubt is about how the "number of table rows sampled" is computed,
so let's do by example a single column table example:


testdb=> create table stat as select v from generate_series( 1, 100 ) v;
SELECT 100
testdb=> analyze verbose stat;
INFO:  analyzing "public.stat"
INFO:  "stat": scanned 4425 of 4425 pages, containing 100 live
rows and 0 dead rows; 3 rows in sample, 100 estimated total
rows
ANALYZE


The table starts with one million tuples, and statistic target at
default value (100). It samples 30k rows, that is roughly 1/3 pf the
table, while I was expecting 100k tuples (since I assume that the max
value for statistics target, 1, correspnds to the whole table).

I can get a more close number of sampled rows considering the sampling
on the pages of the relation, but I'm not sure this is the right way
it operates:

testdb=> alter table stat alter column v set statistics 500;
ALTER TABLE
testdb=> analyze verbose stat;
INFO:  analyzing "public.stat"
INFO:  "stat": scanned 4425 of 4425 pages, containing 100 live
rows and 0 dead rows; 15 rows in sample, 100 estimated total
rows
ANALYZE
testdb=> select reltuples / relpages::real  * attstattarget from
pg_class c join pg_attribute a on a.attrelid = c.oid where c.relname =
'stat' and a.attname = 'v';
  ?column?

 112994.34661865234
(1 riga)


Therefore my question is about how the statistic collectore decides
about the number of tuples to be sampled.

Thanks,
Luca
-

Re: pg_wal lifecycle

[Luca Ferrari]

On Tue, Jul 13, 2021 at 3:43 PM Peter Eisentraut
 wrote:
> Recycling in this context just means that instead of creating a new WAL
> file for new WAL traffic, it reuses an old file.  So if you have WAL
> files 5, 6, 7, 8, 9, and you know that you don't need 5 and 6 anymore,
> when you need to start WAL file 10, instead of creating a new file "10",
> the system just renames "5" to "10" and starts overwriting what was in
> there.  This is just an optimization to use the file system better; it
> doesn't affect the logical principles of what is going on.

Yes, I know about recycling and the problem it can cause (or solve),
like the setting wal_recycle.
However it is still not clear to me when the database triggers a wal
deletion or a wal recycling. I suspect the recycling could be driven
by checkpoint_completion_target: if the wal is still in the window of
the writing checkpoint it cannot be recycled. But what about deletion?


% sudo du -hs $PGDATA/pg_wal
977M/postgres/13/data/pg_wal

... some work
% psql -U postgres -c 'checkpoint;' testdb
% sudo du -hs $PGDATA/pg_wal
929M/postgres/13/data/pg_wal

and in the logs I see:

LOG:  checkpoint complete: wrote 4425 buffers (13.5%); 0 WAL file(s)
added, 3 removed, 0 recycled; write=0.263 s, sync=0.297 s, total=1.617
s; sync files=2, longest=0.255 s, average=0.149 s; distance=62683 kB,
estimate=62683 kB

So the system was still under the max_wal_size (1 GB), it did not
write any new WAL file but decided to remove three of them ( 977 - 3 *
16 = 929 MB).
I do agree that being near max_wal_size, deleting wal files could be
good to avoid growing pg_wal too much due to long transactions, but
still I cannot predict the behavior.

Luca

returning setof from insert ?

[Laura Smith]

Hi,

A bit of pl/pgsql writer's block going on here ...

Postgres complains "RETURN cannot have a parameter in function returning set" 
in relation to the below. I don't really want to have to "RETURNS TABLE" 
because that means I have to enumerate all the table columns.

I'm sure I'm missing something simple here !

CREATE OR REPLACE FUNCTION foobar(foo text,bar text) RETURNS SETOF bar AS $$
DECLARE
v_row bar%ROWTYPE;
BEGIN
insert into bar(f,b) values(foo,bar) returning * into v_row;
return v_row;
END;
$$ language plpgsql;

Re: returning setof from insert ?

[Magnus Hagander]

On Wed, Jul 14, 2021 at 1:22 PM Laura Smith
 wrote:
>
> Hi,
>
> A bit of pl/pgsql writer's block going on here ...
>
> Postgres complains "RETURN cannot have a parameter in function returning set" 
> in relation to the below. I don't really want to have to "RETURNS TABLE" 
> because that means I have to enumerate all the table columns.
>
> I'm sure I'm missing something simple here !
>
> CREATE OR REPLACE FUNCTION foobar(foo text,bar text) RETURNS SETOF bar AS $$
> DECLARE
> v_row bar%ROWTYPE;
> BEGIN
> insert into bar(f,b) values(foo,bar) returning * into v_row;
> return v_row;
> END;
> $$ language plpgsql;

You can write that either as:

RETURN NEXT v_row;

(the NEXT being the missing keyword)


Or just the whole thing as
RETURN QUERY INSERT INTO ... RETURNING *

and get rid of the variable completely, if the function is that trivial.

-- 
 Magnus Hagander
 Me: https://www.hagander.net/
 Work: https://www.redpill-linpro.com/

Re: returning setof from insert ?

[Thomas Kellerer]

Laura Smith schrieb am 14.07.2021 um 13:22:
> A bit of pl/pgsql writer's block going on here ...
>
> Postgres complains "RETURN cannot have a parameter in function returning set" 
> in relation to the below. I don't really want to have to "RETURNS TABLE" 
> because that means I have to enumerate all the table columns.
>
> I'm sure I'm missing something simple here !
>
> CREATE OR REPLACE FUNCTION foobar(foo text,bar text) RETURNS SETOF bar AS $$
> DECLARE
> v_row bar%ROWTYPE;
> BEGIN
> insert into bar(f,b) values(foo,bar) returning * into v_row;
> return v_row;
> END;
> $$ language plpgsql;
>

You need to use RETURN NEXT:

CREATE OR REPLACE FUNCTION foobar(foo text,bar text)
  RETURNS SETOF bar
AS $$
DECLARE
  v_row bar%ROWTYPE;
BEGIN
  insert into bar(f,b) values(foo,bar) returning * into v_row;
  return next v_row;
END;
$$
language plpgsql;

But you don't need PL/pgSQL for this or store the result in a variable:

CREATE OR REPLACE FUNCTION foobar(foo text,bar text)
  RETURNS SETOF bar
AS $$
  insert into bar(f,b) values(foo,bar)
  returning *;
$$
language sql;

Re: returning setof from insert ?

[Laura Smith]

‐‐‐ Original Message ‐‐‐

On Wednesday, July 14th, 2021 at 1:14 PM, Thomas Kellerer  
wrote:

> Laura Smith schrieb am 14.07.2021 um 13:22:
>
> > A bit of pl/pgsql writer's block going on here ...
> >
> > Postgres complains "RETURN cannot have a parameter in function returning 
> > set" in relation to the below. I don't really want to have to "RETURNS 
> > TABLE" because that means I have to enumerate all the table columns.
> >
> > I'm sure I'm missing something simple here !
> >
> > CREATE OR REPLACE FUNCTION foobar(foo text,bar text) RETURNS SETOF bar AS $$
> >
> > DECLARE
> >
> > v_row bar%ROWTYPE;
> >
> > BEGIN
> >
> > insert into bar(f,b) values(foo,bar) returning * into v_row;
> >
> > return v_row;
> >
> > END;
> >
> > $$ language plpgsql;
>
> You need to use RETURN NEXT:
>
> CREATE OR REPLACE FUNCTION foobar(foo text,bar text)
>
> RETURNS SETOF bar
>
> AS $$
>
> DECLARE
>
> v_row bar%ROWTYPE;
>
> BEGIN
>
> insert into bar(f,b) values(foo,bar) returning * into v_row;
>
> return next v_row;
>
> END;
>
> $$
>
> language plpgsql;
>
> But you don't need PL/pgSQL for this or store the result in a variable:
>
> CREATE OR REPLACE FUNCTION foobar(foo text,bar text)
>
> RETURNS SETOF bar
>
> AS $$
>
> insert into bar(f,b) values(foo,bar)
>
> returning *;
>
> $$
>
> language sql;


Thank you for the tip on making it cleaner with sql. As you say, no real need 
for PL/pgSQL in this instance, but I'll try to remember RETURN NEXT in the 
future !

Re: statistic target and sample rate

[Tom Lane]

Luca Ferrari  writes:
> Therefore my question is about how the statistic collectore decides
> about the number of tuples to be sampled.

It's basically 300 times the largest statistics target:

https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/commands/analyze.c;h=0c9591415e4b97dd5c5e693af1860294284a1575;hb=HEAD#l1919

Per that comment, there is good math backing this choice for the task
of making a histogram.  It's a little shakier for other sorts of
statistics --- notably, for n_distinct estimation, the error can still
be really bad.

regards, tom lane

ERROR: cannot freeze committed xmax

[Sasha Aliashkevich]

Hi,

Few weeks ago at one of the databases we started to observe the following error 
in Postgresql logs:

ERROR: cannot freeze committed xmax 572

For the note it's Postgresql 10.14 running on RHEL:

 version
  

--
 PostgreSQL 10.14 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 
20150623 (Red Hat 4.8.5-39), 64-bit
(1 row)


Apparently it happens during the autovacuum and leads to the failing autovacuum 
freeze progress.
We have detected two dictionary tables where manual VACUUM FREEZE fails: 
pg_proc and pg_depend.
Below you can see the page_inspect results for the appropriate blocks as well 
as the status of xid.
Am I understand correctly that for some reason the transaction is marked as 
commited while on the page level it's rolled back so the inconsistency is 
detected?
Could you advise how to fix it and make autovacuum work again?


SELECT txid_status(572);

 txid_status 
-
 committed
(1 row)


SELECT ctid, xmin, xmax FROM pg_proc WHERE xmax=572;

  ctid   | xmin | xmax 
-+--+--
 (75,19) |  571 |  572
(1 row)

SELECT ctid, xmin, xmax FROM pg_depend WHERE xmax=572;

  ctid   | xmin | xmax 
-+--+--
 (55,76) |  571 |  572
 (55,77) |  571 |  572
 (55,78) |  571 |  572
 (55,79) |  571 |  572
 (55,80) |  571 |  572
(5 rows)


SELECT lp, 
   t_ctid AS ctid,
   t_xmin AS xmin,
   t_xmax AS xmax,
   (t_infomask & 128)::boolean AS xmax_is_lock,
   (t_infomask & 1024)::boolean AS xmax_committed,
   (t_infomask & 2048)::boolean AS xmax_rolled_back,
   (t_infomask & 4096)::boolean AS xmax_multixact
FROM heap_page_item_attrs(
get_raw_page('pg_proc', 75), 
'pg_proc'
 );

 lp |  ctid   |   xmin| xmax | xmax_is_lock | xmax_committed | 
xmax_rolled_back | xmax_multixact 
+-+---+--+--++--+
  1 | (75,1)  |   564 |0 | f| f  | t
| f  
  2 | (75,2)  |   564 |0 | f| f  | t
| f  
  3 | (75,3)  |   564 |0 | f| f  | t
| f  
  4 | (75,4)  |   564 |0 | f| f  | t
| f  
  5 | (75,5)  |   564 |0 | f| f  | t
| f  
  6 | (75,6)  |   564 |0 | f| f  | t
| f  
  7 | (75,7)  |   564 |0 | f| f  | t
| f  
  8 | (75,8)  |   564 |0 | f| f  | t
| f  
  9 | (75,9)  |   564 |0 | f| f  | t
| f  
 10 | (75,10) |   566 |0 | f| f  | t
| f  
 11 | (75,11) |   566 |0 | f| f  | t
| f  
 12 | |   |  |  ||  
|
 13 | |   |  |  ||  
|
 14 | (75,14) | 212156121 |0 | f| f  | t
| f  
 15 | (75,15) | 212156121 |0 | f| f  | t
| f  
 16 | (75,16) | 212156121 |0 | f| f  | t
| f  
 17 | (75,17) | 212156121 |0 | f| f  | t
| f  
 18 | (75,18) | 212156121 |0 | f| f  | t
| f  
 19 | (75,21) |   571 |  572 | f| f  | t
| f  
 20 | (75,20) | 212156121 |0 | f| f  | t
| f  
 21 | (75,21) |   572 |0 | f| f  | t
| f  
 22 | (75,22) |  97113057 |0 | f| f  | t
| f  
 23 | (75,23) | 212156121 |0 | f| f  | t
| f  
 24 | (75,24) | 212156121 |0 | f| f  | t
| f  
 25 | (75,25) | 212156121 |0 | f| f  | t
| f  
 26 | (75,26) | 212156121 |0 | f| f  | t
| f  
 27 | (75,27) | 212156121 |0 | f| f  | t
| f  
(27 rows)


SELECT lp, 
   t_ctid AS ctid,
   t_xmin AS xmin,
   t_xmax AS xmax,
   (t_i

Re: Why can't I drop a tablespace?

[Phil Endecott]

Tom Lane wrote:

"Phil Endecott"  writes:

Thanks Laurenz. I was looking at the source for "alter table set
tablespace" yesterday trying to work out what is supposed to happen.
There is a comment at tablecmds.c line 3989: "Thanks to the magic of
MVCC, an error anywhere along the way rolls back the whole operation;
we don't have to do anything special to clean up." But I guess that
creating an entirely new file on a different filesystem is an
exception to that.


No, but PANIC'ing during commit is :-(.  Ordinarily the files created by a
failed transaction would be removed during transaction cleanup, but we
did not reach that code.  So these were left behind, but the table's
original files in the original tablespace should be undamaged.


OK, I've removed them - fingers crossed!

Thanks to everyone who replied for your advice.


Regards, Phil.

How to display stored image as bytea or as large objects?

[Csanyi Pal]

Hi,

I am a novice in postgresql language.

By reading the documentation about storing binary data in postgresql 
database, I realize that that one can store images as binary data by 
using bytea or BLOB data types.


I am running postgresql on Ubuntu 20.04.2 LTS and using pgAdmin4 in 
Desktop mode.


I have stored an image in one of my table with this command:


insert into leckek
(az, a_tanitas_nyelve, mely_negyedevben, mely_osztalyban, 
mely_tagozaton, mely_orakon, a_tanitas_temaja,
 a_lecke_neve, a_lecke_gyakorlati_reszenek_neve, 
a_lecke_gyakorlati_reszenek_dokumentuma,
 a_lecke_elmeleti_reszenek_neve, a_lecke_elmeleti_reszenek_dokumentuma, 
a_lecke_elmeleti_reszenek_tesztje)

values (default, 'Hu', 1, 5, 'b', '1-2', 'Élet és mukakörnyezet',
'A technika és technológia fogalma, szerepe és jelentősége',
'másolás',
default,
'A lényeg',
	 
lo_import('/home/pali/Irataim/Iskola/IskolaEv_2020_2021/MuszakiOraim/Digitalis_Tananyagok_Orakra/Hu/5/5_osztaly_Tananyag_01_02_orara.png'),

default)


so I have now in this table the first row:
1	"Hu"	1	5	"b"	"1-2  "	"Élet és mukakörnyezet"	"A technika és 
technológia fogalma, szerepe és jelentősége"	"másolás"		"A lényeg"	"16800"	


Here the 16800 is the OID of the binary data, the image.

I would like to know how can one display this stored image somehow by 
using some IDE for manage databases?


In applications like pgAdmin4, Libreoffice Base, pgModeler, DBeaver one 
can't display this stored image.


In SQLiteBrowser (this is an IDE for SQLite databases) one can display 
with easy the stored image.


So is there an easy way to display a stored image in postgresql?

--
Best,
from Paul

looping over multirange segments?

[Ben Chobot]

I'm really, really liking the multirange types in PG14. Thank you for 
making them! Unfortunately I am struggling with how to loop over the 
segments of a multirange. There doesn't seem to be a way to convert them 
to arrays, and I can't just use plpgsql's FOREACH on one. Am I missing 
something obvious? It seems like a reasonable thing to want to do.


FWIW, my actual end goal is to take something like this:

select int8range(1,10)::int8multirange - int8range(4,6)::int8multirange;
    ?column?

 {[1,4),[6,10)}

...and turn it into this:

select ???;
 int8range │ ?column?
───┼──
 [1,4)     │    0
 [6,10)    │    0

Re: How to display stored image as bytea or as large objects?

[Csanyi Pal]

2021. 07. 14. 22:17 keltezéssel, Ho John Lee írta:
If you have the binary data for the image in the bytea field, DBeaver 
will display the image when you select that field in table view of the data.
The field will display something like "ÿØÿà  JFIF   [n]" in the row 
(first part of data as unicode, and data length in bytes), you won't see 
the image unless you put focus on it.


I will try this, but do not know which SQL code will store an image in a 
bytea data type column?


Could you give such SQL code please?

At the moment I have stored image in an oid data type column.



.--hjl


On Wed, Jul 14, 2021 at 12:57 PM Csanyi Pal > wrote:


Hi,

I am a novice in postgresql language.

By reading the documentation about storing binary data in postgresql
database, I realize that that one can store images as binary data by
using bytea or BLOB data types.

I am running postgresql on Ubuntu 20.04.2 LTS and using pgAdmin4 in
Desktop mode.

I have stored an image in one of my table with this command:


insert into leckek
(az, a_tanitas_nyelve, mely_negyedevben, mely_osztalyban,
mely_tagozaton, mely_orakon, a_tanitas_temaja,
   a_lecke_neve, a_lecke_gyakorlati_reszenek_neve,
a_lecke_gyakorlati_reszenek_dokumentuma,
   a_lecke_elmeleti_reszenek_neve,
a_lecke_elmeleti_reszenek_dokumentuma,
a_lecke_elmeleti_reszenek_tesztje)
values (default, 'Hu', 1, 5, 'b', '1-2', 'Élet és mukakörnyezet',
                 'A technika és technológia fogalma, szerepe és
jelentősége',
                 'másolás',
                 default,
                 'A lényeg',


lo_import('/home/pali/Irataim/Iskola/IskolaEv_2020_2021/MuszakiOraim/Digitalis_Tananyagok_Orakra/Hu/5/5_osztaly_Tananyag_01_02_orara.png'),
                 default)


so I have now in this table the first row:
1       "Hu"    1       5       "b"     "1-2  " "Élet és
mukakörnyezet" "A technika és
technológia fogalma, szerepe és jelentősége"    "másolás"   
    "A lényeg"      "16800"


Here the 16800 is the OID of the binary data, the image.

I would like to know how can one display this stored image somehow by
using some IDE for manage databases?

In applications like pgAdmin4, Libreoffice Base, pgModeler, DBeaver one
can't display this stored image.

In SQLiteBrowser (this is an IDE for SQLite databases) one can display
with easy the stored image.

So is there an easy way to display a stored image in postgresql?

--
Best,
from Paul