Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitea14d83

Browse files
committed
Days 80-85
1 parent87efa6a commitea14d83

11 files changed

+740
-0
lines changed
Lines changed: 112 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,112 @@
1+
Originally from:[tweet](https://twitter.com/samokhvalov/status/1735925615185002701),[LinkedIn post]().
2+
3+
---
4+
5+
#How to find int4 PKs with out-of-range risks in a large database
6+
7+
>I post a new PostgreSQL "howto" article every day. Join me in this
8+
>journey –[subscribe](https://twitter.com/samokhvalov/), provide feedback, share!
9+
10+
Modern ORMs like Rails or Django use int8 (bigint) primary keys (PKs) today. However, in older projects, there may be
11+
old tables with int4 (integer, int, serial) PKs, that have grown and have risks of int4 overflow –
12+
[max value for int4 is 2,147,483,647](https://postgresql.org/docs/current/datatype-numeric.html),
13+
and PK conversion int4->int8 without downtime is not a trivial task (TODO: cover it in another howto).
14+
15+
Here is how we can quickly check if there are tables with int2 or int4 PKs and how much of the "capacity" has been used
16+
in each case (postgres-checkup query):
17+
18+
```sql
19+
do $$
20+
declare
21+
min_relpages int8 :=0;-- in very large DBs, skip small tables by setting this to 100
22+
rec record;
23+
outtext :='';
24+
out1 json;
25+
inumeric :=0;
26+
val int8;
27+
rationumeric;
28+
sqltext;
29+
begin
30+
for recin
31+
select
32+
c.oid,
33+
spcnameas tblspace,
34+
nspnameas schema_name,
35+
relnameas table_name,
36+
t.typname,
37+
pg_get_serial_sequence(format('%I.%I', nspname, relname), attname)as seq,
38+
min(attname)as attname
39+
from pg_index i
40+
join pg_class conc.oid=i.indrelid
41+
left join pg_tablespace tspontsp.oid= reltablespace
42+
left join pg_namespace nonn.oid=c.relnamespace
43+
join pg_attribute aon
44+
a.attrelid=i.indrelid
45+
anda.attnum= any(i.indkey)
46+
join pg_type tont.oid= atttypid
47+
where
48+
i.indisprimary
49+
and (
50+
c.relpages>= min_relpages
51+
or pg_get_serial_sequence(format('%I.%I', nspname, relname), attname)is not null
52+
)andt.typnamein ('int2','int4')
53+
and nspname<>'pg_toast'
54+
group by1,2,3,4,5,6
55+
havingcount(*)=1-- skip PKs with 2+ columns (TODO: analyze them too)
56+
loop
57+
raise debug'table: %',rec.table_name;
58+
59+
ifrec.seq isnull then
60+
sql := format('select max(%I) from %I.%I;',rec.attname,rec.schema_name,rec.table_name);
61+
else
62+
sql := format('select last_value from %s;',rec.seq);
63+
end if;
64+
65+
raise debug'sql: %', sql;
66+
execute sql into val;
67+
68+
ifrec.typname='int4' then
69+
ratio := (val::numeric/2^31)::numeric;
70+
elsifrec.typname='int2' then
71+
ratio := (val::numeric/2^15)::numeric;
72+
else
73+
assert false,'unreachable point';
74+
end if;
75+
76+
if ratio>0.1 then-- report only if > 10% of capacity is reached
77+
i := i+1;
78+
79+
out1 := json_build_object(
80+
'table', (
81+
coalesce(nullif(quote_ident(rec.schema_name),'public')||'.','')
82+
|| quote_ident(rec.table_name)
83+
),
84+
'pk',rec.attname,
85+
'type',rec.typname,
86+
'current_max_value', val,
87+
'capacity_used_percent', round(100* ratio,2)
88+
);
89+
90+
raise debug'cur: %', out1;
91+
92+
if out<>'' then
93+
out := out|| e',\n';
94+
end if;
95+
96+
out := out|| format(' "%s": %s',rec.table_name, out1);
97+
end if;
98+
end loop;
99+
100+
raise info e'{\n%\n}', out;
101+
end;
102+
$$ language plpgsql;
103+
```
104+
105+
Output example:
106+
107+
```json
108+
INFO: {
109+
"oldbig": {"table" :"oldbig","pk" :"id","type" :"int4","current_max_value" :2107480000,"capacity_used_percent" :98.14},
110+
"oldbig2": {"table" :"oldbig2","pk" :"id","type" :"int4","current_max_value" :1107480000,"capacity_used_percent" :51.57}
111+
}
112+
```
Lines changed: 79 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,79 @@
1+
Originally from:[tweet](https://twitter.com/samokhvalov/status/1736301721155191218),[LinkedIn post]().
2+
3+
---
4+
5+
#How to plot graphs right in psql on macOS (iTerm2)
6+
7+
>I post a new PostgreSQL "howto" article every day. Join me in this
8+
>journey –[subscribe](https://twitter.com/samokhvalov/), provide feedback, share!
9+
10+
If you, like me, work with Postgres mostly in psql, you might want to be able to plot a simple graph not leaving psql.
11+
12+
The recipe below was originally described by
13+
[Alexander Korotkov](https://akorotkov.github.io/blog/2016/06/09/psql-graph/),
14+
I[slightly adjusted it](https://gist.github.com/NikolayS/d5f1af808f7275dc1491c37fb1e2dd11) to work with Python3. The
15+
recipe works thanks to[iTerm2's Inline Images Protocol](https://iterm2.com/documentation-images.html). For Linux, there
16+
are various ways to achieve similar results
17+
(see, for
18+
example,[How do I make my terminal display graphical pictures?](https://askubuntu.com/questions/97542/how-do-i-make-my-terminal-display-graphical-pictures)).
19+
20+
1) Get the plotting script and install matplotlib:
21+
22+
```bash
23+
wget \
24+
-O~/pg_graph.py \
25+
https://gist.githubusercontent.com/NikolayS/d5f1af808f7275dc1491c37fb1e2dd11/raw/4f19a23222a6f7cf66eead3cae9617dd39bf07a5/pg_graph
26+
27+
pip install matplotlib
28+
```
29+
30+
2) Define a macro in~/.psqlrc (this line should work in bash, zsh, and csh):
31+
32+
```bash
33+
printf"%s %s %s %s %s %s\n"\\set graph\'\\\\g\|
34+
python3$(pwd)/pg_graph.py\' \
35+
>>~/.psqlrc
36+
```
37+
38+
3) Start psql and try it out
39+
40+
```sql
41+
nik=# with avg_temp(month, san_diego, vancouver, london) as (
42+
values
43+
('Jan',15,4,5),
44+
('Feb',16,5,6),
45+
('Mar',17,7,8),
46+
('Apr',18,10,11),
47+
('May',19,14,15),
48+
('Jun',21,17,17),
49+
('Jul',24,20,19),
50+
('Aug',25,21,20),
51+
('Sep',23,18,17),
52+
('Oct',21,12,13),
53+
('Nov',18,8,8),
54+
('Dec',16,5,6)
55+
)
56+
select*from avg_temp;
57+
58+
month | san_diego | vancouver | london
59+
-------+-----------+-----------+--------
60+
Jan |15 |4 |5
61+
Feb |16 |5 |6
62+
Mar |17 |7 |8
63+
Apr |18 |10 |11
64+
May |19 |14 |15
65+
Jun |21 |17 |17
66+
Jul |24 |20 |19
67+
Aug |25 |21 |20
68+
Sep |23 |18 |17
69+
Oct |21 |12 |13
70+
Nov |18 |8 |8
71+
Dec |16 |5 |6
72+
(12 rows)
73+
74+
nik=# :graph
75+
```
76+
77+
Result:
78+
79+
![Graph result](files/0081_01.png)
Lines changed: 124 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,124 @@
1+
Originally from:[tweet](https://twitter.com/samokhvalov/status/1736637759073517684),[LinkedIn post]().
2+
3+
---
4+
5+
#How to draw frost patterns using SQL ❄️
6+
7+
>I post a new PostgreSQL "howto" article every day. Join me in this
8+
>journey –[subscribe](https://twitter.com/samokhvalov/), provide feedback, share!
9+
10+
This original idea and implementation was done by Kirill Borovikov (kilor) – I just reformatted and slightly tuned it,
11+
extending the character set.
12+
13+
Here is the query:
14+
15+
```sql
16+
with recursive tas (
17+
select
18+
0as x,
19+
0as y,
20+
'{"{0,0}"}'::text[]as c,
21+
0as i
22+
23+
union all
24+
25+
(
26+
with zas (
27+
select
28+
dn.x,
29+
dn.y,
30+
t.c,
31+
t.i
32+
from t,
33+
lateral (
34+
select
35+
((random()*2-1)*100)::integeras x,
36+
((random()*2-1)*100)::integeras y
37+
)as p,
38+
lateral (
39+
select*
40+
from (
41+
select
42+
(unnest::text[])[1]::integeras x,
43+
(unnest::text[])[2]::integeras y
44+
from unnest(t.c::text[])
45+
)as t
46+
order by sqrt((x-p.x) ^2+ (y-p.y) ^2)
47+
limit1
48+
)as n,
49+
lateral (
50+
select
51+
n.x+ dxas x,
52+
n.y+ dyas y
53+
from
54+
generate_series(-1,1)as dx,
55+
generate_series(-1,1)as dy
56+
where (dx, dy)<> (0,0)
57+
order by
58+
case
59+
when (p.x,p.y)= (n.x,n.y) then0
60+
else abs(
61+
acos(
62+
((p.x-n.x)* dx+ (p.y-n.y)* dy)
63+
/ sqrt((p.x-n.x) ^2+ (p.y-n.y) ^2)
64+
/ sqrt(dx ^2+ dy ^2)
65+
)
66+
)
67+
end
68+
limit1
69+
)as dn
70+
)
71+
select
72+
z.x,
73+
z.y,
74+
z.c|| array[z.x,z.y]::text,
75+
z.i+1
76+
from z
77+
wherez.i< (1<<10)
78+
)
79+
),
80+
mapas (
81+
select
82+
gxas x,
83+
gyas y,
84+
(
85+
select sqrt((gx-T.x) ^2+ (gy-T.y) ^2) v
86+
from t
87+
order by v
88+
limit1
89+
)as v
90+
from
91+
generate_series(-40,40)as gx,
92+
generate_series(-30,30)as gy
93+
),
94+
gras (
95+
select regexp_split_to_array('@%#*+=-:.','')as s
96+
)
97+
select
98+
string_agg(
99+
coalesce(s[(v* (array_length(s,1)-1))::integer+1],''),
100+
''
101+
order by x
102+
)as frozen
103+
from
104+
(
105+
select
106+
x,
107+
y,
108+
v::double precision/max(v) over()as v
109+
from map
110+
)as t,
111+
gr
112+
group by y
113+
order by y;
114+
```
115+
116+
Every time it draws a new frozen pattern, here are several examples:
117+
118+
![frozen pattern 1](files/0082_01.png)
119+
120+
![frozen pattern 2](files/0082_02.png)
121+
122+
![frozen pattern 3](files/0082_03.png)
123+
124+
Happy holiday season 🎅

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp