tag:blogger.com,1999:blog-88395743672902887242024-03-15T18:10:48.162-07:00Pavel Stehule's blogSome notes about PostgreSQLPavel Stěhulehttp://www.blogger.com/profile/01996484227228696817noreply@blogger.comBlogger234125tag:blogger.com,1999:blog-8839574367290288724.post-73487204962849915012024-03-05T03:33:00.000-08:002024-03-05T03:33:16.474-08:00How fast is plpgsql?<p>Ten years ago I did some <a href="http://okbob.blogspot.com/2014/05/a-speed-of-pl-languages-for-atypical.html">speed tests of plpgsql</a>. I did same tests on same computer, and now, the plpgsql is about 3-4 times faster. There is still significant overhead against native (without plpgsql function) query, but it is reduced (little bit). I would to compare again with Python</p>
<pre>create table foo(a int, b int, c int, d int, e int);
insert into foo select random()*100, random()*100, random()*100, random()*100, random()*100 from generate_series(1,100000);
CREATE OR REPLACE FUNCTION public.myleast1(VARIADIC integer[])
RETURNS integer LANGUAGE plpgsql IMMUTABLE STRICT
AS $function$
declare result int;
a int;
begin
foreach a in array $1
loop
if result is null then
result := a;
elseif a < result then
result := a;
end if;
end loop;
return result;
end;
$function$;
CREATE OR REPLACE FUNCTION public.myleast6(VARIADIC a integer[])
RETURNS integer LANGUAGE plpython3u IMMUTABLE STRICT
AS $function$
r = None
for x in a:
if r is None or x < r:
r = x
return r
$function$;
(2024-03-05 12:25:10) postgres=# select count(*) filter (where a = least(a,b,c,d,e)) from foo;
┌───────┐
│ count │
╞═══════╡
│ 20544 │
└───────┘
(1 row)
Time: 15,265 ms
(2024-03-05 12:25:53) postgres=# select count(*) filter (where a = myleast1(a,b,c,d,e)) from foo;
┌───────┐
│ count │
╞═══════╡
│ 20544 │
└───────┘
(1 row)
Time: 221,726 ms
(2024-03-05 12:25:59) postgres=# select count(*) filter (where a = myleast6(a,b,c,d,e)) from foo;
┌───────┐
│ count │
╞═══════╡
│ 20544 │
└───────┘
(1 row)
Time: 282,732 ms
</pre>
<p>Ten years ago plpgsql was about 30% slower, now it is about 20% faster.</p>
<p>Attention - this benchmark is pretty unrealistic - the best benefit of stored procedures is when they are used like glue of SQL statements. On second hand, can be interesting to know the overhead of stored procedures against native C implementation - this is like worst case.</p>Pavel Stěhulehttp://www.blogger.com/profile/01996484227228696817noreply@blogger.com0tag:blogger.com,1999:blog-8839574367290288724.post-32068494556320789832024-03-03T23:32:00.000-08:002024-03-04T01:55:28.516-08:00using jq for processing PostgreSQL logs in json format<p>PostgreSQL supports logging in json format. From my perspective json logs are badly readable, but allows machine processing, and with good tools, it is beautifully simple.</p><p>There are more tools for json processing - I use <a href="https://jqlang.github.io/jq/tutorial/">jq</a>.</p><p>For simple analyze of errors in log, I can use sequence of commands:</p><p><a aria-label="Today at 7:38:48 AM" class="c-link c-timestamp" data-sk="tooltip_parent" data-stringify-requires-siblings="true" data-stringify-text="[7:38 AM]" data-stringify-type="replace" data-ts="1709534328.052409" href="https://aukroworkspace.slack.com/archives/C04D40T685B/p1709534328052409"><span class="c-timestamp__label" data-qa="timestamp_label"></span></a></p><div class="c-message_kit__gutter__right" data-qa="message_content" role="presentation"><div class="c-message_kit__blocks c-message_kit__blocks--rich_text"><div class="c-message__message_blocks c-message__message_blocks--rich_text" data-qa="message-text"><div class="p-block_kit_renderer" data-qa="block-kit-renderer"><div class="p-block_kit_renderer__block_wrapper p-block_kit_renderer__block_wrapper--first"><div class="p-rich_text_block" dir="auto"><pre class="c-mrkdwn__pre" data-stringify-type="pre"><div class="p-rich_text_block--no-overflow">cat postgresql-Sun.json | jq 'select(.error_severity=="ERROR").message'|sort -n | uniq -c
1 "canceling statement due to user request"
1 "column \"de.id\" must appear in the GROUP BY clause or be used in an aggregate function"
1 "column reference \"modify_time\" is ambiguous"
3 "column \"us.show_name\" must appear in the GROUP BY clause or be used in an aggregate function"
24 "current transaction is aborted, commands ignored until end of transaction block"
3 "deadlock detected"</div></pre></div></div></div></div></div></div><p>For transformation to csv and viewing it in <a href="https://github.com/okbob/pspg">pspg</a>:</p><p></p><pre class="c-mrkdwn__pre" data-stringify-type="pre">cat postgresql-Sun.json | \<br /> jq -r 'select(.error_severity=="ERROR") | [.timestamp, .user, .ps, .error_severity, .message ] | @csv' \<br />| pspg --csv</pre><p></p><p>With these tools the work with log is "almost" effective and friendly (pspg supports sorting, searching, clipboard).<br /></p><p> <br /></p>Pavel Stěhulehttp://www.blogger.com/profile/01996484227228696817noreply@blogger.com0tag:blogger.com,1999:blog-8839574367290288724.post-70769215043703731652024-02-08T10:19:00.000-08:002024-02-08T10:20:18.775-08:00new extension pgmeminfo<p>I wrote very simple extension <a href="https://github.com/okbob/pgmeminfo"><span style="font-family: courier;">pgmeminfo</span></a> for PostgreSQL12+, that should to help with investigation of memory usage by Postgres. Function <span style="font-family: courier;">pgmeminfo</span> returns glibc information about memory usage provided by function <span style="font-family: courier;">mallinfo().</span> It is much more precious than usage top or htop.</p><p>Second function <span style="font-family: courier;">pgmeminfo_contexts</span> returns data about memory contexts - this is PostgreSQL internal memory management. Same data can be showed by view
<span style="font-family: courier;">pg_get_backend_memory_contexts</span>. The function <span style="font-family: courier;">pgmeminfo_contexts</span> can simply accumulate data, and can simply limit deep of iteration over memory contexts. It is little bit faster than recursive query over view (but it is not too important). <br /></p><p><br /></p>
<pre>(2024-02-08 19:16:12) postgres=# SELECT * FROM pgmeminfo();
┌─────────┬─────────┬────────┬───────┬────────┬─────────┬─────────┬──────────┬──────────┬──────────┐
│ arena │ ordblks │ smblks │ hblks │ hblkhd │ usmblks │ fsmblks │ uordblks │ fordblks │ keepcost │
╞═════════╪═════════╪════════╪═══════╪════════╪═════════╪═════════╪══════════╪══════════╪══════════╡
│ 1118208 │ 7 │ 0 │ 2 │ 401408 │ 0 │ 0 │ 1056160 │ 62048 │ 56368 │
└─────────┴─────────┴────────┴───────┴────────┴─────────┴─────────┴──────────┴──────────┴──────────┘
(1 row)
(2024-02-08 19:18:44) postgres=# select * from pgmeminfo_contexts(deep => 1, accum_mode=>'off');<br />┌──────────────────────────────┬──────────────────────────┬──────────────────┬───────┬─────────────┬───────────────┬────────────┬────────────┐<br />│ name │ ident │ parent │ level │ total_bytes │ total_nblocks │ free_bytes │ used_bytes │<br />╞══════════════════════════════╪══════════════════════════╪══════════════════╪═══════╪═════════════╪═══════════════╪════════════╪════════════╡<br />│ TopMemoryContext │ │ │ 0 │ 97696 │ 5 │ 9840 │ 87856 │<br />│ TopTransactionContext │ │ TopMemoryContext │ 1 │ 8192 │ 1 │ 7760 │ 432 │<br />│ dynahash │ CFuncHash │ TopMemoryContext │ 1 │ 8192 │ 1 │ 576 │ 7616 │<br />│ dynahash │ Record information cache │ TopMemoryContext │ 1 │ 8192 │ 1 │ 1600 │ 6592 │<br />│ dynahash │ TableSpace cache │ TopMemoryContext │ 1 │ 8192 │ 1 │ 2112 │ 6080 │<br />│ RegexpCacheMemoryContext │ │ TopMemoryContext │ 1 │ 1024 │ 1 │ 784 │ 240 │<br />│ dynahash │ Type information cache │ TopMemoryContext │ 1 │ 24384 │ 2 │ 2640 │ 21744 │<br />│ dynahash │ Operator lookup cache │ TopMemoryContext │ 1 │ 24576 │ 2 │ 10776 │ 13800 │<br />│ RowDescriptionContext │ │ TopMemoryContext │ 1 │ 8192 │ 1 │ 6912 │ 1280 │<br />│ MessageContext │ │ TopMemoryContext │ 1 │ 32768 │ 3 │ 10392 │ 22376 │<br />│ search_path processing cache │ │ TopMemoryContext │ 1 │ 8192 │ 1 │ 5616 │ 2576 │<br />│ dynahash │ Operator class cache │ TopMemoryContext │ 1 │ 8192 │ 1 │ 576 │ 7616 │<br />│ dynahash │ smgr relation table │ TopMemoryContext │ 1 │ 32768 │ 3 │ 16848 │ 15920 │<br />│ PgStat Shared Ref Hash │ │ TopMemoryContext │ 1 │ 7232 │ 2 │ 704 │ 6528 │<br />│ PgStat Shared Ref │ │ TopMemoryContext │ 1 │ 8192 │ 4 │ 2952 │ 5240 │<br />│ PgStat Pending │ │ TopMemoryContext │ 1 │ 16384 │ 5 │ 15984 │ 400 │<br />│ TransactionAbortContext │ │ TopMemoryContext │ 1 │ 32768 │ 1 │ 32528 │ 240 │<br />│ dynahash │ Portal hash │ TopMemoryContext │ 1 │ 8192 │ 1 │ 576 │ 7616 │<br />│ TopPortalContext │ │ TopMemoryContext │ 1 │ 8192 │ 1 │ 7680 │ 512 │<br />│ dynahash │ Relcache by OID │ TopMemoryContext │ 1 │ 16384 │ 2 │ 3528 │ 12856 │<br />│ CacheMemoryContext │ │ TopMemoryContext │ 1 │ 1048576 │ 8 │ 399064 │ 649512 │<br />│ WAL record construction │ │ TopMemoryContext │ 1 │ 49976 │ 2 │ 6384 │ 43592 │<br />│ dynahash │ PrivateRefCount │ TopMemoryContext │ 1 │ 8192 │ 1 │ 2640 │ 5552 │<br />│ MdSmgr │ │ TopMemoryContext │ 1 │ 8192 │ 1 │ 7016 │ 1176 │<br />│ dynahash │ LOCALLOCK hash │ TopMemoryContext │ 1 │ 16384 │ 2 │ 4616 │ 11768 │<br />│ GUCMemoryContext │ │ TopMemoryContext │ 1 │ 24576 │ 2 │ 12000 │ 12576 │<br />│ dynahash │ Timezones │ TopMemoryContext │ 1 │ 104128 │ 2 │ 2640 │ 101488 │<br />│ ErrorContext │ │ TopMemoryContext │ 1 │ 8192 │ 1 │ 7952 │ 240 │<br />└──────────────────────────────┴──────────────────────────┴──────────────────┴───────┴─────────────┴───────────────┴────────────┴────────────┘<br />(28 rows)<br /><br />(2024-02-08 19:18:46) postgres=# select * from pgmeminfo_contexts();<br />┌──────────────────┬───────┬────────┬───────┬─────────────┬───────────────┬────────────┬────────────┐<br />│ name │ ident │ parent │ level │ total_bytes │ total_nblocks │ free_bytes │ used_bytes │<br />╞══════════════════╪═══════╪════════╪═══════╪═════════════╪═══════════════╪════════════╪════════════╡<br />│ TopMemoryContext │ │ │ 0 │ 1945816 │ 248 │ 690856 │ 1254960 │<br />└──────────────────┴───────┴────────┴───────┴─────────────┴───────────────┴────────────┴────────────┘<br />(1 row)<br />
</pre>Pavel Stěhulehttp://www.blogger.com/profile/01996484227228696817noreply@blogger.com0tag:blogger.com,1999:blog-8839574367290288724.post-7324709998358165592023-12-29T22:12:00.021-08:002024-01-02T22:59:41.202-08:002023 resume<ul style="text-align: left;"><li>rewriting dbms_pipe and dbms_alert in orafce. Now the conditions variables are used (for synchronization) instead timeouts, and the lags in the communication are significantly reduced,</li><li>fix pspg and PDCursesMod so pspg can be used with other curses than ncurses. Originally pdcurses didn't support stream redirection. Still only VT environment is supported (far target is support pspg on new Microsoft Windows terminal),</li><li>Rewriting background of profiler, tracer routines in plpgsql_check. Now related code is significantly more readable and more robust. I introduced new <a href="https://github.com/okbob/plpgsql_check/blob/master/src/pldbgapi2.c">pldbgapi2</a> - it is based on old plpgsqlapi and fmgr hook. The advantage against plpgsqlapi2 is possibility to simply handle an exception,</li><li>tracing constants in plpgsql_check (for some simple cases) - it reduces some false alarms related to dynamic SQL, and related assert pragmas: assert-schema, assert-table and assert-column,<br /></li><li>reorganization and minor refactoring long patches for postgres - mainly for support of session variables,</li><li>my patch of `filter` option for pg_dump was <a href="https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=a5cf808be55bcc68c3917c380f95122436af1be1">committed</a> to upstream, <br /></li><li>detection of opened cursors in plpgsql_check <br /></li></ul><p><br /></p>Pavel Stěhulehttp://www.blogger.com/profile/01996484227228696817noreply@blogger.com0256 01 Benešov-Benešov u Prahy, Česko49.7837821 14.687369821.473548263821158 -20.4688802 78.094015936178849 49.8436198tag:blogger.com,1999:blog-8839574367290288724.post-40552863059605105462023-12-06T00:17:00.000-08:002023-12-06T00:17:55.017-08:00Close cursors<p>One my customer reported very problematic memory issues of currently tested code freshly ported from Oracle's PL/SQL to PL/pgSQL. He rewrites very big application based on usage of thousands stored procedures and functions, and views. This application is ported to Postgres by ora2pg. It is working pretty well, but some patterns has different overhead, and some patterns can be fatal.</p><p>In your application he often (100 000x) call code (in long transaction):</p><p><code>OPEN qNAJUPOSPL FOR EXECUTE
mSqNAJUPOSPL;<br />LOOP<br /> FETCH qNAJUPOSPL INTO mID_NAJVUPOSPL , mID_NAJDATSPLT ,
mID_PREDPIS;<br />
EXIT WHEN NOT FOUND; /* apply on qNAJUPOSPL */<br />END LOOP;</code></p><p><code></code></p><p>The problem is undisclosed cursor there. PLpgSQL cursors are just references to SQL cursors. The lifecycle of cursor variable is defined by scope, but lifecycle of SQL cursor is limited by transaction. Any active cursor can have allocated lot of resources (it is "snapshot" of active query). Unfortunately, after leaving of scope of cursor's variable, the related cursor is not closed automatically (and there is not some garbage collector too). Inside large application is not easy to find this issue, mainly when you don't know what you should to find. So, I wrote new warning to <a href="https://github.com/okbob/plpgsql_check">plpgsql_check</a> (2.7.0). This warning is enabled by default. <br /></p><p>Default check is designed to check previously opened cursor when cursor is opening. <br /></p><p></p>
<pre>CREATE OR REPLACE FUNCTION public.test()
RETURNS void
LANGUAGE plpgsql
AS $function$
declare
c refcursor;
q text;
r record;
begin
q := 'select * from pg_class limit 10';
open c for execute q;
loop
fetch c into r;
exit when not found;
raise notice '%', r;
end loop;
end;
$function$
do $$
begin
perform test();
perform test();
end;
$$;
NOTICE: (2619,pg_statistic,11,10029,0,10,2,2619,0,19,410,19,2840,t,f,p,r,31,0,f,f,f,f,f,t,n,f,0,728,1,{postgres=arwdDxt/postgres},,)
NOTICE: (2619,pg_statistic,11,10029,0,10,2,2619,0,19,410,19,2840,t,f,p,r,31,0,f,f,f,f,f,t,n,f,0,728,1,{postgres=arwdDxt/postgres},,)
DO
</pre>
When plpgsql_check is loaded, then the warning is displayed:
<pre>(2023-12-06 09:07:32) postgres=# load 'plpgsql_check';
LOAD
(2023-12-06 09:07:51) postgres=# do $$
begin
perform test();
perform test();
end;
$$;
NOTICE: (2619,pg_statistic,11,10029,0,10,2,2619,0,19,410,19,2840,t,f,p,r,31,0,f,f,f,f,f,t,n,f,0,728,1,{postgres=arwdDxt/postgres},,)
WARNING: cursor is not closed
DETAIL: PL/pgSQL function test() line 8 at OPEN
SQL statement "SELECT test()"
PL/pgSQL function inline_code_block line 4 at PERFORM
NOTICE: (2619,pg_statistic,11,10029,0,10,2,2619,0,19,410,19,2840,t,f,p,r,31,0,f,f,f,f,f,t,n,f,0,728,1,{postgres=arwdDxt/postgres},,)
DO
</pre>
There is possible to set strict mode, and then the cursors are checked at the function's exit:
<pre>(2023-12-06 09:07:52) postgres=# set plpgsql_check.strict_cursors_leaks to on;
SET
(2023-12-06 09:09:56) postgres=# do $$
begin
perform test();
perform test();
end;
$$;
NOTICE: (2619,pg_statistic,11,10029,0,10,2,2619,0,19,410,19,2840,t,f,p,r,31,0,f,f,f,f,f,t,n,f,0,728,1,{postgres=arwdDxt/postgres},,)
WARNING: cursor is not closed
DETAIL: PL/pgSQL function test() during function exit
SQL statement "SELECT test()"
PL/pgSQL function inline_code_block line 3 at PERFORM
NOTICE: (2619,pg_statistic,11,10029,0,10,2,2619,0,19,410,19,2840,t,f,p,r,31,0,f,f,f,f,f,t,n,f,0,728,1,{postgres=arwdDxt/postgres},,)
WARNING: cursor is not closed
DETAIL: PL/pgSQL function test() during function exit
SQL statement "SELECT test()"
PL/pgSQL function inline_code_block line 4 at PERFORM
DO
</pre>Pavel Stěhulehttp://www.blogger.com/profile/01996484227228696817noreply@blogger.com1tag:blogger.com,1999:blog-8839574367290288724.post-41749850635800691482023-10-13T01:00:00.000-07:002023-10-13T01:00:35.188-07:00compiled dll of plpgsql_check 2.5.4 and Orafce 4.7.0 for PostgreSQL 15 and 16<p>I compiled and uploaded zip files with latest <a href="https://github.com/orafce/orafce">orafce</a> and <a href="https://github.com/okbob/plpgsql_check">plpgsql_check</a> for PostgreSQL 15 and PostgreSQL 16.</p><p>Setup:</p><ol style="text-align: left;"><li>download <a href="https://pgsql.cz/files/orafce-4.0.1.zip"><span style="font-family: courier;"></span></a><span style="font-family: courier;"><a href="https://pgsql.cz/files/orafce-4.7.0-x64.zip">orafce-4.7.0-x64.zip</a></span> or <a href="https://pgsql.cz/files/plpgsql_check-2.5.4-x64.zip"><span style="font-family: courier;"></span></a><span style="font-family: courier;"><a href="https://pgsql.cz/files/plpgsql_check-2.5.4-x64.zip">plpgsql_check-2.5.4-x64.zip</a></span> and extract files</li><li>copy related dll file to PostgreSQL lib directory <i>(NN is number of pg release)</i><br /><span style="font-family: courier;">orafce-NN.dll</span> -> <span style="font-family: courier;">"c:\Program Files\PostgreSQL\NN\lib"</span></li><li>remove suffix "-15" or "-16" from dll file<br /><span style="font-family: courier;">orafce-NN.dll</span> -> <span style="font-family: courier;">orafce.dll</span></li><li>copy <span style="font-family: courier;">*.sql </span>and <span style="font-family: courier;">*.control</span> files to extension directory<br /><span style="font-family: courier;">*.sql</span>, <span style="font-family: courier;">*.control</span> -> <span style="font-family: courier;">"c:\Program Files\PostgreSQL\NN\share\extension"</span></li><li>execute with super user rights SQL command <span style="font-family: courier;">CREATE EXTENSION</span><br /><span style="font-family: courier;">CREATE EXTENSION orafce;</span><br /></li></ol>Pavel Stěhulehttp://www.blogger.com/profile/01996484227228696817noreply@blogger.com0tag:blogger.com,1999:blog-8839574367290288724.post-78146716790774705242022-12-02T11:03:00.002-08:002022-12-02T11:03:32.403-08:00prepared dll of orafce 4.0.1 and plpgsql_check 2.2.5 for PostgreSQL 14 and PostgreSQL 15<p>I compiled and uploaded zip files with latest <a href="https://github.com/orafce/orafce">orafce</a> and <a href="https://github.com/okbob/plpgsql_check">plpgsql_check</a> for PostgreSQL 14 and PostgreSQL 15.</p><p>Setup:</p><ol style="text-align: left;"><li>download <a href="https://pgsql.cz/files/orafce-4.0.1.zip"><span style="font-family: courier;">orafce-4.0.1.zip</span></a> or <a href="https://pgsql.cz/files/plpgsql_check-2.2.5.zip"><span style="font-family: courier;">plpgsql_check-2.2.5.zip</span></a> and extract files</li><li>copy related dll file to PostgreSQL lib directory <i>(NN is number of pg release)</i><br /><span style="font-family: courier;">orafce-NN.dll</span> -> <span style="font-family: courier;">"c:\Program Files\PostgreSQL\NN\lib"</span></li><li>remove suffix "-14" or "-15" from dll file<br /><span style="font-family: courier;">orafce-NN.dll</span> -> <span style="font-family: courier;">orafce.dll</span></li><li>copy <span style="font-family: courier;">*.sql </span>and <span style="font-family: courier;">*.control</span> files to extension directory<br /><span style="font-family: courier;">*.sql</span>, <span style="font-family: courier;">*.control</span> -> <span style="font-family: courier;">"c:\Program Files\PostgreSQL\NN\share\extension"</span></li><li>execute with super user rights SQL command <span style="font-family: courier;">CREATE EXTENSION</span><br /><span style="font-family: courier;">CREATE EXTENSION orafce;</span><br /></li></ol>Pavel Stěhulehttp://www.blogger.com/profile/01996484227228696817noreply@blogger.com7tag:blogger.com,1999:blog-8839574367290288724.post-44711121543504863812022-11-28T08:07:00.003-08:002022-11-28T08:08:36.525-08:00pspg 5.6.0<p>I released <a href="https://github.com/okbob/pspg/releases/tag/5.6.0">pspg 5.6.0</a>. There is only one (not too visible change). It allows to use true color themes in "konsole" terminal (when <span style="font-family: courier;">TERM</span>) is <span style="font-family: courier;">xterm-direct</span>.<br /></p>Pavel Stěhulehttp://www.blogger.com/profile/01996484227228696817noreply@blogger.com0tag:blogger.com,1999:blog-8839574367290288724.post-82403051177689083022022-11-20T23:55:00.003-08:002022-11-21T10:25:31.301-08:00pspg and nushell<p>I very like the concept of <a href="https://www.nushell.sh/">nushell</a>. The concept of Unix pipes is famous (still), but little bit aged (we have more resources than was possible 40 years ago). Working with multi-column data with classical Unix tools is fragile and really not very friendly. More time I asked why conceptual development stopped at 80 years. nushell allows to work well with relations. There is similar project <a href="https://relational-pipes.globalcode.info/v_0/index.xhtml">relational pipes</a>. In old times the Microsoft has something similar based on DAO<br /></p><p>I didn't find C API of nushell (it is 100% rust oriented), so I cannot to support nushell in <a href="https://github.com/okbob/pspg">pspg</a> directly, but support via CSV format works perfectly. </p><p> </p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgJyNL3FF1yVqFEVMh3HKOBaWG4Hj_kT-qMaO0SdTb1qN7YA1N2sjtMHi-IbSC8nFFSR3tpQ_Ma8q_0xHMEdGvbbSc42AJLvbVtaNOba2oPpwWg8xfwWEV3WZGHVnAUVcqR-asRNWEktws-g4GdM2iNDYJmIpsPdoJuEDrRajfPSJlpaqWxi-lAgw_xPA/s1076/Sn%C3%ADmek%20obrazovky%20z%202022-11-21%2008-49-20.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="661" data-original-width="1076" height="197" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgJyNL3FF1yVqFEVMh3HKOBaWG4Hj_kT-qMaO0SdTb1qN7YA1N2sjtMHi-IbSC8nFFSR3tpQ_Ma8q_0xHMEdGvbbSc42AJLvbVtaNOba2oPpwWg8xfwWEV3WZGHVnAUVcqR-asRNWEktws-g4GdM2iNDYJmIpsPdoJuEDrRajfPSJlpaqWxi-lAgw_xPA/s320/Sn%C3%ADmek%20obrazovky%20z%202022-11-21%2008-49-20.png" width="320" /></a></div><br /> <p></p><p>and same result displayed in pspg:</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi0RwjC0NzBtSInpGoJNBtWuUSO96VcKaQ5s0TdEAsoVfM1y68fGBDz1yzYKZ_Adz1N83ynJGrJz8sA8VJkekL3vvVSiZTYi-EkxQ_0hidCAtDWurSLg17o9_DbYVSXJKsRN4z6BId8wbXwNn89KU1YztLzNo4YKjgVkVgWZOYLZJr-MNdiaOBRpZ8IDg/s1076/Sn%C3%ADmek%20obrazovky%20z%202022-11-21%2008-49-38.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="661" data-original-width="1076" height="197" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi0RwjC0NzBtSInpGoJNBtWuUSO96VcKaQ5s0TdEAsoVfM1y68fGBDz1yzYKZ_Adz1N83ynJGrJz8sA8VJkekL3vvVSiZTYi-EkxQ_0hidCAtDWurSLg17o9_DbYVSXJKsRN4z6BId8wbXwNn89KU1YztLzNo4YKjgVkVgWZOYLZJr-MNdiaOBRpZ8IDg/s320/Sn%C3%ADmek%20obrazovky%20z%202022-11-21%2008-49-38.png" width="320" /></a></div><br /><p>Update: pspg 5.5.10 supports nushell formats (table_mode) <span style="font-family: courier;">rounded</span> (default) and <span style="font-family: courier;">heavy</span>. The conversion to csv is not necessary. <br /></p><p><br /></p><p><br /></p>Pavel Stěhulehttp://www.blogger.com/profile/01996484227228696817noreply@blogger.com0tag:blogger.com,1999:blog-8839574367290288724.post-8690990829700877482022-11-02T10:15:00.003-07:002022-11-02T10:15:54.141-07:00orafce 4.0.0<p>Today I released <a href="https://github.com/orafce/orafce/releases/tag/VERSION_4_0_0">orafce 4.0.0</a></p><p>The big change is merging <a href="https://github.com/orafce/orafce_sql">orafce_sql</a> project - so now directly in orafce is possible to use dbms_sql package.</p><p>Second change is code cleaning - support for PostgreSQL 9.6 and 10 was removed.<br /></p>Pavel Stěhulehttp://www.blogger.com/profile/01996484227228696817noreply@blogger.com0tag:blogger.com,1999:blog-8839574367290288724.post-11097157335382918222022-10-07T22:41:00.002-07:002022-10-07T22:41:39.366-07:00pspg 5.5.8<p>I released new version of <a href="https://github.com/okbob/pspg">pspg </a>. This release can be interesting for users that uses some BSD platforms (like FreeBSD). Unfortunately, these platforms <a href="https://unix.stackexchange.com/questions/235635/alternate-screen-on-freebsds-sc-and-vt">doesn't support alternate screen</a>, and then can users can see some unwanted visual effects, when pspg is closed. Most unwanted are chars of bottom menu in command line.</p><p>New pspg introduces set of options, that can be used in this situation: <span style="font-family: courier;">--on-exit-reset</span> (reset terminal), <span style="font-family: courier;">--on-exit-clean</span> (clean terminal) and <span style="font-family: courier;">--on-exit-erase-line</span> (erase last (bottom line)). <br /></p>Pavel Stěhulehttp://www.blogger.com/profile/01996484227228696817noreply@blogger.com0tag:blogger.com,1999:blog-8839574367290288724.post-19171285278848820252022-04-15T23:04:00.000-07:002022-04-15T23:04:11.645-07:00Orafce 3.21.0 <p>I released <a href="https://github.com/orafce/orafce/releases/tag/VERSION_3_21_0">Orafce 3.21.0</a>. In this release, the Oracle regexp functions regexp_instr and regexp_replace was backported (and little bit modified) from PostgreSQL 15. The C implementation is more faster, and much more robust than PLpgSQL implementation. The problem was not in just PL/pgSQL, but in some missing possibilities of PostgreSQL regexp API available from SQL or PL/pgSQL.</p><p>Attention, although the code was backported from Postgres, the behavior is little bit different for making higher compatibility with Oracle. There is little bit different handling NULL values.<br /></p>Pavel Stěhulehttp://www.blogger.com/profile/01996484227228696817noreply@blogger.com0tag:blogger.com,1999:blog-8839574367290288724.post-37202461948280786932022-04-05T23:04:00.002-07:002022-04-05T23:04:29.833-07:00New releases Orafce and plpgsql_check<p> I released bugfix releases of <a href="https://github.com/okbob/plpgsql_check/releases/tag/v2.1.3">plpgsql_check</a> and <a href="https://github.com/orafce/orafce/releases/tag/VERSION_3_20_0">Orafce</a>.</p><p>Attention - In had to change signature of nvl2 function in Orafce. With this change, the behave is much more close to Oracle's nvl2 function. Unfortunately, this change can break extension update when some users use nvl2 function in views. The related views should be dropped first, and after extension update should be created again.<br /></p><p><br /></p>Pavel Stěhulehttp://www.blogger.com/profile/01996484227228696817noreply@blogger.com0tag:blogger.com,1999:blog-8839574367290288724.post-25263255784986910872021-12-20T11:17:00.003-08:002021-12-29T02:08:11.193-08:002021 Resume<p>This year was not too good. Fortunately, I lost nobody from my family, but (unfortunately) my friends yes. I had not too much trainings, so there was more time to write code. </p><h4 style="text-align: left;"><a href="https://github.com/okbob/pspg">pspg</a><br /></h4><div style="text-align: left;"><ul style="text-align: left;"><li>clipboard support with CSV, text and INSERT formats</li><li>better handling mouse in xterm mode 1002</li><li>vertical scrollbar</li><li>possibility to select range for export</li><li>query stream mode</li><li>backslash commands supports</li><li>code re-factoring, and significant flickering reduction</li><li>export to program</li><li>switch to primary screen by ^o</li><li>progressive data loading</li><li>pretty printing of help texts</li><li>support for custom themes</li><li>new visual effects and possibility to read SQLcl output</li><li>kqueue (BSD) support</li><li>smooth horizontal scrolling</li></ul><h4 style="text-align: left;"><a href="https://github.com/okbob/plpgsql_check">plpgsql_check</a> <br /></h4><div style="text-align: left;"><ul style="text-align: left;"><li>short syntax for pragma, pragmas TABLE and TYPE</li><li>little bit smarter check of format string used by format function in EXECUTE command</li><li>possibility to save execution's profile of aborted on cancelled function.<br /></li></ul><h4 style="text-align: left;">orafce</h4><div style="text-align: left;"><ul style="text-align: left;"><li><a href="https://github.com/okbob/orafce_mail">orafce_mail</a> - implementation of dbms_mail and utl_mail</li></ul><h4 style="text-align: left;">PostgreSQL</h4><div style="text-align: left;"><ul style="text-align: left;"><li>unistr in PostgreSQL 14</li><li>performance optimization of CALL command in PostgreSQL 14<br /></li><li>possibility to use pspg together with \watch command (PSQL_WATCH_PAGER) in PostgreSQL 15</li><li>lot of revisions of patch for possibility to read options from file for pg_dump</li><li>significant refactoring of patch of implementation session variables<br /></li></ul></div><p>Some projects are very (too) long. But I hope, so they are near to final stage, and so next year will be more time for more usual life, and less time for coding. <br /></p></div></div><p> <br /></p></div><p style="text-align: left;"> <br /></p>Pavel Stěhulehttp://www.blogger.com/profile/01996484227228696817noreply@blogger.com0tag:blogger.com,1999:blog-8839574367290288724.post-14469153288475252932021-11-02T09:42:00.001-07:002021-11-07T21:47:20.511-08:00pspg 5.5.0<p> Today I released <a href="https://github.com/okbob/pspg/releases/tag/5.5.1">pspg 5.5</a>. There are few new interesting features:</p><ul style="text-align: left;"><li>stream mode based on continuous reading from file (uses kqueue) is supported on BSD Unix,</li><li>There are two new visual effects - possibility to hide border line, and possibility to highlight odd rows</li><li>pspg can be used in Oracle's SQLcl client too. It should to work with default format and with ANSICONSOLE format,</li><li>one char vertical scrolling is supported too. </li></ul><div class="separator" style="clear: both; text-align: center;"><a href="https://1.bp.blogspot.com/-6r_z4yNTIfU/YYFqQ5fc36I/AAAAAAAAHIs/MQzozghbDGsd9L0sv9AvN3YXJ0IYjZ2lACLcBGAsYHQ/s881/pspg-5.5-hideheaderline.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="607" data-original-width="881" height="220" src="https://1.bp.blogspot.com/-6r_z4yNTIfU/YYFqQ5fc36I/AAAAAAAAHIs/MQzozghbDGsd9L0sv9AvN3YXJ0IYjZ2lACLcBGAsYHQ/s320/pspg-5.5-hideheaderline.png" width="320" /></a></div><br /><div class="separator" style="clear: both; text-align: center;"><a href="https://1.bp.blogspot.com/-Sc-VXNNcfgQ/YYFqRVVau2I/AAAAAAAAHI0/NONkeEzpcKkLMo_uKC6n8ft0xmfTmXBygCLcBGAsYHQ/s881/pspg-5.5-oddrechighl-1.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="607" data-original-width="881" height="220" src="https://1.bp.blogspot.com/-Sc-VXNNcfgQ/YYFqRVVau2I/AAAAAAAAHI0/NONkeEzpcKkLMo_uKC6n8ft0xmfTmXBygCLcBGAsYHQ/s320/pspg-5.5-oddrechighl-1.png" width="320" /></a></div><br /><div class="separator" style="clear: both; text-align: center;"><a href="https://1.bp.blogspot.com/-_tgh1qHtQWk/YYFqRMvYaBI/AAAAAAAAHIw/dqYRL92cBqkGG4mDacy13NY2UD3LWnXMgCLcBGAsYHQ/s881/pspg-5.5-oddrechighl-2.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="607" data-original-width="881" height="220" src="https://1.bp.blogspot.com/-_tgh1qHtQWk/YYFqRMvYaBI/AAAAAAAAHIw/dqYRL92cBqkGG4mDacy13NY2UD3LWnXMgCLcBGAsYHQ/s320/pspg-5.5-oddrechighl-2.png" width="320" /></a></div><br /><div class="separator" style="clear: both; text-align: center;"><a href="https://1.bp.blogspot.com/-JHilOOdaZKQ/YYFqRplAtII/AAAAAAAAHI4/JSz4f4k3BAw-ioDZGF7JhN8YYBm7_DftQCLcBGAsYHQ/s881/pspg-5.5-oddrechighl-3.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="607" data-original-width="881" height="220" src="https://1.bp.blogspot.com/-JHilOOdaZKQ/YYFqRplAtII/AAAAAAAAHI4/JSz4f4k3BAw-ioDZGF7JhN8YYBm7_DftQCLcBGAsYHQ/s320/pspg-5.5-oddrechighl-3.png" width="320" /></a></div><br /> <br />Pavel Stěhulehttp://www.blogger.com/profile/01996484227228696817noreply@blogger.com0tag:blogger.com,1999:blog-8839574367290288724.post-55625940048795486832021-10-31T10:05:00.003-07:002021-10-31T10:05:35.803-07:00Orafce 3.17.0I released <a href="https://github.com/orafce/orafce/releases/tag/VERSION_3_17_0">Orafce 3.17.0</a>. This is bugfix only release. Giles Darold wrote a patch that fixes orafce's regexp functions for NULL arguments.
Pavel Stěhulehttp://www.blogger.com/profile/01996484227228696817noreply@blogger.com2tag:blogger.com,1999:blog-8839574367290288724.post-41060035238422682182021-10-12T23:54:00.005-07:002021-10-12T23:54:51.273-07:00pspg 5.4.1 releasedI released bugfix release of <a href="https://github.com/okbob/pspg"><code>pspg</code></a>. The problem with copy to clipboard on macos (using by <code>pbcopy</code>) is fixed in <a href="https://github.com/okbob/pspg/releases/tag/5.4.1">this release</a>.Pavel Stěhulehttp://www.blogger.com/profile/01996484227228696817noreply@blogger.com0tag:blogger.com,1999:blog-8839574367290288724.post-2359509760845008882021-10-11T23:46:00.000-07:002021-10-11T23:46:03.579-07:00Orafce 3.16.2 for Postgres 12, 13 and 14 for Microsoft WindowsI made dll of orafce 3.16.2. These dll are available from archive <a href="https://postgres.cz/files/orafce-3.16.2-x64-win.zip">orafce-3.16.2-x64-win.zip</a>
Instalation is simple. Copy related dll file to directory <code>Program files/Postgres/version/lib</code> and rename this dll just to <code>orafce</code>. Next, copy
<code>orafce.control</code> and <code>orafce--3.16.sql</code> to <code>Program files/Postgres/version/share/extension</code>. Last step is executing command
<code>CREATE EXTENSION orafce</code> in the database, where you want to use <code>orafce</code>. It can require redistributable runtime for Visual Studio 2019.
Usually an good idea is set <code>SEARCH_PATH</code> to include schema <code>oracle</code>.
Pavel Stěhulehttp://www.blogger.com/profile/01996484227228696817noreply@blogger.com0tag:blogger.com,1999:blog-8839574367290288724.post-71953759338930998682021-10-11T12:16:00.001-07:002021-10-11T12:35:50.677-07:00plpgsql_check 2.0.4 for Microsoft Windows and Postgres 12, 13 and 14.I made dll of plpgsql_check 2.0.4. These dll are available from archive <a href="https://postgres.cz/files/plpgsql_check-2.0.4-x64-win.zip">plpgsql_check-2.0.4-x64-win.zip</a>
Instalation is simple. Copy related dll file to directory <code>Program files/Postgres/version/lib</code> and rename this dll just to <code>plpgsql_check</code>. Next, copy
<code>plpgsql_check.control</code> and <code>plpgsql_check-2.0.sql</code> to <code>Program files/Postgres/version/share/extension</code>. Last step is executing command
<code>CREATE EXTENSION plpgsql_check</code> in the database, where you want to use <code>plpgsql_check</code>. It can require redistributable runtime for Visual Studio 2019.Pavel Stěhulehttp://www.blogger.com/profile/01996484227228696817noreply@blogger.com0tag:blogger.com,1999:blog-8839574367290288724.post-36372438600505361852021-10-10T00:07:00.001-07:002021-10-10T00:07:48.637-07:00pspg 5.4.0I released <a href="https://github.com/okbob/pspg"><code>pspg</code></a> <a href="https://github.com/okbob/pspg/releases/tag/5.4.0">version 5.4.0.</a>.
In this release there is an possibility to customize buildin color themes:
<pre><span style="background-color: #eeeeee;">template = 1
template_menu = 3
background = black, white
data = black, white
label = black, white, italic, bold
border = #000000, white
footer = lightgray, white
cursor_data = blue, white, italic, bold, dim, reverse
cursor_border = blue, blue , italic, bold, dim, reverse
cursor_label = blue, white, italic, bold, dim, reverse
cursor_footer = blue, white, italic, bold, dim, reverse
cursor_bookmark = red, white, italic, bold, dim, reverse
cross_cursor = white, blue, italic, bold
cross_cursor_border = brightblue, blue
status_bar = black, lightgray
title = black, lightgray
scrollbar_arrows = black, white
scrollbar_background = lightgray, white
scrollbar_slider = white, gray </span></pre>
<div class="separator" style="clear: both;"><a href="https://1.bp.blogspot.com/-eakxrSSY0Dw/YWKQ-ROPWpI/AAAAAAAAHHo/jdK8V8ptCrQXROEdLluriWZTQ6HXnPVbgCLcBGAsYHQ/s818/Sn%25C3%25ADmek%2Bz%2B2021-10-08%2B08-33-21.png" style="display: block; padding: 1em 0px; text-align: center;"><img alt="" border="0" data-original-height="565" data-original-width="818" src="https://1.bp.blogspot.com/-eakxrSSY0Dw/YWKQ-ROPWpI/AAAAAAAAHHo/jdK8V8ptCrQXROEdLluriWZTQ6HXnPVbgCLcBGAsYHQ/s320/Sn%25C3%25ADmek%2Bz%2B2021-10-08%2B08-33-21.png" width="320" /></a></div>Pavel Stěhulehttp://www.blogger.com/profile/01996484227228696817noreply@blogger.com0tag:blogger.com,1999:blog-8839574367290288724.post-44924763683174928432021-09-28T10:59:00.005-07:002021-09-29T02:14:28.911-07:00plpgsql_check 2.0.1I released new significant version of <a href="https://github.com/okbob/plpgsql_check"><code>plpgsql_check</code></a> - <a href="https://github.com/okbob/plpgsql_check/releases/tag/v2.0.1">plpgsql_check 2.0.1</a>. Although there are only two new features (and few bugfixes), these two features are important.
<p>I wrote about benefits of <code>plpgsql_check</code> for <code>PL/pgSQL</code> language developers in my blog <a href="http://okbob.blogspot.com/2019/08/why-you-need-plpgsqlcheck-if-you-write.html">Why you need plpgsql_check (if you write procedures in PLpgSQL)</a>. The <code>plpgsql_check</code> is PostgreSQL extensions, that does static analyze of <code>PL/pgSQL</code> code. It can detect lot of possible runtime bugs before execution, it can detect some performance or security isues too. More <code>plpgsql_check</code> can do coverage analyze, and has integrated profiler and tracer.
<p>The <code>PL/pgSQL</code> language is relative static type strict language, and then the static analyze is working well. But there are two limits. The statis analyze cannot to work with objects and values that are created (calculated) at runtime. These objects are local temporary tables (PostgreSQL doesn't support global temporary tables yet) and the results of dynamic SQL:
<pre>postgres=# \sf+ fx1
CREATE OR REPLACE FUNCTION public.fx1(tablename text)
RETURNS void
LANGUAGE plpgsql
1 AS $function$
2 DECLARE r record;
3 BEGIN
4 EXECUTE format('SELECT * FROM %I', tablename) INTO r;
5 RAISE NOTICE 'id=%', r.id;
6 END;
7 $function$
postgres=# SELECT * FROM plpgsql_check_function('fx1');
┌──────────────────────────────────────────────────────────────────────────────────────┐
│ plpgsql_check_function │
╞══════════════════════════════════════════════════════════════════════════════════════╡
│ warning:00000:4:EXECUTE:cannot determinate a result of dynamic SQL │
│ Detail: There is a risk of related false alarms. │
│ Hint: Don't use dynamic SQL and record type together, when you would check function. │
│ error:55000:5:RAISE:record "r" is not assigned yet │
│ Detail: The tuple structure of a not-yet-assigned record is indeterminate. │
│ Context: SQL expression "r.id" │
└──────────────────────────────────────────────────────────────────────────────────────┘
(6 rows)
postgres=# \sf+ fx2
CREATE OR REPLACE FUNCTION public.fx2()
RETURNS void
LANGUAGE plpgsql
1 AS $function$
2 BEGIN
3 CREATE TEMP TABLE IF NOT EXISTS ltt(a int);
4 DELETE FROM ltt;
5 INSERT INTO ltt VALUES(10);
6 END;
7 $function$
postgres=# SELECT * FROM plpgsql_check_function('fx2');
┌───────────────────────────────────────────────────────────┐
│ plpgsql_check_function │
╞═══════════════════════════════════════════════════════════╡
│ error:42P01:4:SQL statement:relation "ltt" does not exist │
│ Query: DELETE FROM ltt │
│ -- ^ │
└───────────────────────────────────────────────────────────┘
(3 rows)
</pre>
In <code>plpgsql_check 2.0.1</code> I can use pragmas <code>TYPE</code> and <code>TABLE</code> (note: an syntax of pragma in plpgsql_check is little bit strange, because the language <code>PL/pgSQL</code> doesn't support native syntax for pragma (custom compiler directive) (like ADA language or PL/SQL language):
<pre> CREATE OR REPLACE FUNCTION public.fx1(tablename text)
RETURNS void
LANGUAGE plpgsql
1 AS $function$
2 DECLARE r record;
3 BEGIN
4 PERFORM plpgsql_check_pragma('TYPE: r (id int)');
5 EXECUTE format('SELECT * FROM %I', tablename) INTO r;
6 RAISE NOTICE 'id=%', r.id;
7 END;
8 $function$
postgres=# SELECT * FROM plpgsql_check_function('fx1');
┌────────────────────────┐
│ plpgsql_check_function │
╞════════════════════════╡
└────────────────────────┘
(0 rows)
postgres=# \sf+ fx2
CREATE OR REPLACE FUNCTION public.fx2()
RETURNS void
LANGUAGE plpgsql
1 AS $function$
2 BEGIN
3 CREATE TEMP TABLE IF NOT EXISTS ltt(a int);
4 PERFORM plpgsql_check_pragma('TABLE: ltt (a int)');
5 DELETE FROM ltt;
6 INSERT INTO ltt VALUES(10);
7 END;
8 $function$
postgres=# SELECT * FROM plpgsql_check_function('fx2');
┌────────────────────────┐
│ plpgsql_check_function │
╞════════════════════════╡
└────────────────────────┘
(0 rows)
</pre>
Note: if you use <code>plpgsql_check 2.0.2</code>, then you can use shorter form for PRAGMA:
<pre>
BEGIN
CREATE TEMP TABLE IF NOT EXISTS ltt(a int);
PERFORM 'PRAGMA:TABLE: ltt (a int)';
...
</pre>
Pavel Stěhulehttp://www.blogger.com/profile/01996484227228696817noreply@blogger.com0tag:blogger.com,1999:blog-8839574367290288724.post-43189956517765674932021-09-12T22:14:00.001-07:002021-09-12T22:14:04.735-07:00bugfix release of pspg - 5.3.5you can download source code from <a href="https://github.com/okbob/pspg/releases/tag/5.3.5">https://github.com/okbob/pspg/releases/tag/5.3.5</a>Pavel Stěhulehttp://www.blogger.com/profile/01996484227228696817noreply@blogger.com0tag:blogger.com,1999:blog-8839574367290288724.post-88630145784219637922021-08-21T00:35:00.003-07:002021-08-21T00:35:51.508-07:00orafce_mail<p>I wrote new Postgres's extension - <a href="https://github.com/okbob/orafce_mail"><code>orafce_mail</code></a>. This extension enhancing an <a href="https://github.com/orafce/orafce"><code>orafce</code></a> about possibility to send mail from Postgres. I tested this extension against my gmail account, and it is work well. Just you need to generate special application's password, and that is all. This extension use library <a href="https://curl.se/libcurl/"><code>libcurl</code></a>, and the features and possibilities of this extension is limitted by this library. It uses relativly new features of this library, so it cannot be linked with older versions of libcurl. The usage is simple:
<p>You have to set configure variables:
<pre>
set orafce_mail.smtp_server_url to 'smtps://smtp.gmail.com:465';
set orafce_mail.smtp_server_userpwd to 'pavel.stehule@gmail.com:yourgoogleapppassword';
</pre>
<p>After that you can send an mail without an attachment:
<pre>
call utl_mail.send(sender => 'pavel.stehule@gmail.com',
recipients => 'pavel.stehule@gmail.com',
subject => 'ahoj, nazdar, žlutý kůň',
message => e'test, \nžlutý kůň');
</pre>
<p>or mail with an attachment:
<pre>
do $$
declare
myimage bytea = (select img from foo limit 1);
begin
call utl_mail.send_attach_raw(sender => 'pavel.stehule@gmail.com',
recipients => 'pavel.stehule@gmail.com',
subject => 'mail with picture',
message => 'I am sending some picture',
attachment => myimage,
att_mime_type => 'image/png',
att_filename => 'screenshot.png');
end
$$;
</pre>
<p>The implemented API is almost compatible with Oracle's package <code>utl_mail</code> and <code>dbms_mail</code>. This extension requires PostgreSQL 10 and higher (with procedures support).Pavel Stěhulehttp://www.blogger.com/profile/01996484227228696817noreply@blogger.com0tag:blogger.com,1999:blog-8839574367290288724.post-70516502338110164402021-07-26T13:39:00.001-07:002021-07-26T13:41:36.781-07:00pspg 5.2.0 released<p><a href="https://github.com/okbob/pspg/releases/tag/5.2.0">https://github.com/okbob/pspg/releases/tag/5.2.0</a> </p><p>There are only two, but I hope important, features.</p><p>First feature is "progressive data load". Before this release, pspg loaded all rows before first print to screen. Now, with progressive data load, only 500 rows are loaded, these rows are printed to screen, and repeatedly next 2000 rows are loaded. Although the load should not be complete, almost all pspg commands can be used. </p><p>pspg is designed for browsing tabular data. But it can be used for plain text too. This is important - psql can produce lot of data in plain text format - (\? \h). Now, pspg can highlight some parts of these documents.</p><div class="separator" style="clear: both; text-align: center;"><a href="https://1.bp.blogspot.com/-etLwCvqgCcE/YP8dTDU_6II/AAAAAAAAHE0/Idd1PQQcjs8_OpzBucrfXf1OK3_8GwNZwCLcBGAsYHQ/s909/Sn%25C3%25ADmek%2Bz%2B2021-07-26%2B22-27-38.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="579" data-original-width="909" src="https://1.bp.blogspot.com/-etLwCvqgCcE/YP8dTDU_6II/AAAAAAAAHE0/Idd1PQQcjs8_OpzBucrfXf1OK3_8GwNZwCLcBGAsYHQ/s320/Sn%25C3%25ADmek%2Bz%2B2021-07-26%2B22-27-38.png" width="320" /></a></div><br /><div class="separator" style="clear: both; text-align: center;"><a href="https://1.bp.blogspot.com/-YSb_7qGwQFo/YP8dTLvKqEI/AAAAAAAAHEw/GGnvsid5f28YZZiKKZHqsOQdK7H9H-HCACLcBGAsYHQ/s909/Sn%25C3%25ADmek%2Bz%2B2021-07-26%2B22-28-09.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="579" data-original-width="909" src="https://1.bp.blogspot.com/-YSb_7qGwQFo/YP8dTLvKqEI/AAAAAAAAHEw/GGnvsid5f28YZZiKKZHqsOQdK7H9H-HCACLcBGAsYHQ/s320/Sn%25C3%25ADmek%2Bz%2B2021-07-26%2B22-28-09.png" width="320" /></a></div><br /><div class="separator" style="clear: both; text-align: center;"><a href="https://1.bp.blogspot.com/-9v2FZ_8GqNA/YP8dS5csrVI/AAAAAAAAHEs/ZvQf7suwED0gtHZhDgQ9tf_IAziPqQMGgCLcBGAsYHQ/s909/Sn%25C3%25ADmek%2Bz%2B2021-07-26%2B22-28-50.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="579" data-original-width="909" src="https://1.bp.blogspot.com/-9v2FZ_8GqNA/YP8dS5csrVI/AAAAAAAAHEs/ZvQf7suwED0gtHZhDgQ9tf_IAziPqQMGgCLcBGAsYHQ/s320/Sn%25C3%25ADmek%2Bz%2B2021-07-26%2B22-28-50.png" width="320" /></a></div><br /><p><br /></p>Pavel Stěhulehttp://www.blogger.com/profile/01996484227228696817noreply@blogger.com0tag:blogger.com,1999:blog-8839574367290288724.post-65717896895946996432021-07-16T03:48:00.004-07:002021-07-20T12:33:22.177-07:00pspg 5.1.2 was released, psql \watch command now supports pspgToday I released <a href="https://github.com/okbob/pspg/releases/tag/5.1.2" target="_blank">pspg 5.1.2</a>. Mostly this is bugfix and refactoring release, but there is one, I hope, interesting function. You can try to press <code>Ctrl o</code> for temporal switch to terminal's primary screen. In primary screen you can see <code>psql</code> session. After pressing any key, the terminal switch to alternative screen with <code>pspg</code>.
<p>Thanks to Tomas Munro <a href="https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=7c09d2797ecdf779e5dc3289497be85675f3d134" target="_blank">work</a>, the <code>psql</code> <code>\watch</code> command will supports pagers (in PostgreSQL 15). In this time only <code>pspg</code> can do this work (in streaming mode). When you set environment variable <code>PSQL_WATCH_PAGER</code>, the <code>\watch</code> command redirects otputs to specified pager (for <code>pspg</code> <code>export PSQL_WATCH_PAGER="pspg --stream"</code>. Next you can run command:
<pre>
select * from pg_stat_database \watch 5
</pre>
or
<pre>
select * from pg_stat_activity where state='active' \watch 1
</pre>Pavel Stěhulehttp://www.blogger.com/profile/01996484227228696817noreply@blogger.com0