IPC/MultixactCreation on the Standby server

[Dmitry]

/build/../src/backend/postmaster/postmaster.c:1374
    #24 0x57c4402bcd2d in main (argc=17, argv=0x57c4747fb140) at /usr/src/postgresql-17-17.5-1.pgdg24.04+1/build/../src/backend/main/main.c:199

Could you please help me to fix the problem of stuck 'client backend' processes?

I kindly ask you for any ideas and recommendations!

Best regards,
Dmitry
 

IPC/MultixactCreation on the Standby server

[Dmitry]

c:199 Could you please help me to fix the problem of stuck 'client backend' processes? I kindly ask you for any ideas and recommendations! Best regards,Dmitry  

Re: IPC/MultixactCreation on the Standby server

[Dmitry]

On 23.06.2025 16:33, Dmitry wrote:

Hi,

The problem is as follows.
A replication cluster includes a primary server and one hot-standby replica.
The workload on the primary server is represented by multiple requests 
generating multixact IDs, while the hot-standby replica performs reading 
requests.

After some time, all requests on the hot-standby are stuck and never get 
finished.

The `pg_stat_activity` view on the replica reports that processes are stuck 
waiting for IPC/MultixactCreation,
pg_cancel_backend and pg_terminate_backend cannot cancel the request, SIGQUIT 
is the only way to stop it.

We tried:
* changing the `autovacuum_multixact_freeze_max_age` parameters,
* increasing `multixact_member_buffers` and `multixact_offset_buffers`,
* disabling `hot_standby_feedback`,
* switching the replica to synchronous and asynchronous mode,
* and much more.
But nothing helped.

We ran the replica in recovery mode from WAL archive, i.e. as warm-standby, the 
result is the same.

We tried to build from the sources based on REL_17_5 branch with the default 
configure settings
     ./configure
     make
     make install
But got no luck.

Here is an example with a synthetic workload reproducing the problem.

Test system
===

-   Architecture: x86_64
-   OS: Ubuntu 24.04.2 LTS (Noble Numbat)
-   Tested postgres version(s):
     -   latest 17 (17.5)
     -   latest 18 (18-beta1)

Steps to reproduce
==

     postgres=# create table tbl (
         id int primary key,
         val int
     );
     postgres=# insert into tbl select i, 0 from generate_series(1,5) i;


The first and second scripts execute queries on the master server
-

     pgbench --no-vacuum --report-per-command -M prepared -c 200 -j 200 -T 300 -P 1 
--file=/dev/stdin <<'EOF'
     \set id random(1, 5)
     begin;
     select * from tbl where id = :id for key share;
     commit;
     EOF

     pgbench --no-vacuum --report-per-command -M prepared -c 100 -j 100 -T 300 -P 1 
--file=/dev/stdin <<'EOF'
     \set id random(1, 5)
     begin;
     update tbl set val = val+1 where id = :id;
     \sleep 10 ms
     commit;
     EOF


The following script is executed on the replica
---

     pgbench --no-vacuum --report-per-command -M prepared -c 100 -j 100 -T 300 -P 1 
--file=/dev/stdin <<'EOF'
     begin;
     select sum(val) from tbl;
     \sleep 10 ms
     select sum(val) from tbl;
     \sleep 10 ms
     commit;
     EOF

     pgbench (17.5 (Ubuntu 17.5-1.pgdg24.04+1))
     progress: 1.0 s, 2606.8 tps, lat 33.588 ms stddev 13.316, 0 failed
     progress: 2.0 s, 3315.0 tps, lat 30.174 ms stddev 5.933, 0 failed
     progress: 3.0 s, 3357.0 tps, lat 29.699 ms stddev 5.541, 0 failed
     progress: 4.0 s, 3350.0 tps, lat 29.911 ms stddev 5.311, 0 failed
     progress: 5.0 s, 3206.0 tps, lat 30.999 ms stddev 6.343, 0 failed
     progress: 6.0 s, 3264.0 tps, lat 30.828 ms stddev 6.389, 0 failed
     progress: 7.0 s, 3224.0 tps, lat 31.099 ms stddev 6.197, 0 failed
     progress: 8.0 s, 3168.0 tps, lat 31.486 ms stddev 6.940, 0 failed
     progress: 9.0 s, 3118.0 tps, lat 32.004 ms stddev 6.546, 0 failed
     progress: 10.0 s, 3017.0 tps, lat 33.183 ms stddev 7.971, 0 failed
     progress: 11.0 s, 3157.0 tps, lat 31.697 ms stddev 6.624, 0 failed
     progress: 12.0 s, 3180.0 tps, lat 31.415 ms stddev 6.310, 0 failed
     progress: 13.0 s, 3150.9 tps, lat 31.591 ms stddev 6.280, 0 failed
     progress: 14.0 s, 3329.0 tps, lat 30.189 ms stddev 5.792, 0 failed
     progress: 15.0 s, 3233.6 tps, lat 30.852 ms stddev 5.723, 0 failed
     progress: 16.0 s, 3185.4 tps, lat 31.378 ms stddev 6.383, 0 failed
     progress: 17.0 s, 3035.0 tps, lat 32.920 ms stddev 7.390, 0 failed
     progress: 18.0 s, 3173.0 tps, lat 31.547 ms stddev 6.390, 0 failed
     progress: 19.0 s, 3077.0 tps, lat 32.427 ms stddev 6.634, 0 failed
     progress: 20.0 s, 3266.1 tps, lat 30.740 ms stddev 5.842, 0 failed
     progress: 21.0 s, 2990.9 tps, lat 33.353 ms stddev 7.019, 0 failed
     progress: 22.0 s, 3048.1 tps, lat 32.933 ms stddev 6.951, 0 failed
     progress: 23.0 s, 3148.0 tps, lat 31.769 ms stddev 6.077, 0 failed
     progress: 24.0 s, 1523.2 tps, lat 30.029 ms stddev 5.093, 0 failed
     progress: 25.0 s, 0.0 tps, lat 0.000 ms stddev 0.000, 0 failed
     progress: 26.0 s, 0.0 tps, lat 0.000 ms stddev 0.000, 0 failed
     progress: 27.0 s, 0.0 tps, lat 0.000 ms stddev 0.000, 0 failed
     progress: 28.0 s, 0.0 tps, lat 0.000 ms stddev 0.000, 0 failed
     progress: 29.0 s, 0.0 tps, lat 0.000 ms stddev 0.000, 0 failed
     progress: 30.0 s, 0.0 tps, lat 0.000 ms stddev 0.000, 0 failed
     progress: 31.0 s, 0.0 tps, lat 0.000 ms stddev 0.000, 0 failed
     progress: 32.0 s, 0.0 tps, lat 0.000 ms stddev 0.000, 0 failed
     progress: 33.0 s, 0.0 tps, lat 0.000 ms stddev 0.000, 0 failed
     progress: 34.0

Announcement of a new C++ API to PostgreSQL.

[Dmitry Igrishin]

Hello everyone,

I would like to announce the initial beta release of Pgfe, a
modern C++ API to PostgreSQL that simplifies working with
PostgreSQL in C++.

The page on GitHub - https://github.com/dmitigr/pgfe
The documentation - http://dmitigr.ru/pgfe/doc

To build Pgfe the C++17 compiler, such as GNU GCC 8+ or
Microsoft Visual C++ 15.7+ is required.

Please, take a look. Any feedback is very important to me.

Do we need yet another IDE (SQL development assistant) for PostgreSQL?

[Dmitry Igrishin]

Hello all,

Colleagues. There is an idea to develop a commercial IDE for PostgreSQL
under Windows.
At the initial stage, not so much an IDE, as an assistant for the server
side development.
What features would you like to see in such an instrument? Thanks.

Re: Do we need yet another IDE (SQL development assistant) for PostgreSQL?

[Dmitry Igrishin]

Thank you all for your responses!

Okay, if I decide to start this project, the Linux platform will be
supported.

As I see it, pgspa (PostgreSQL Server Programming Assistant) should have
both the command line interface,
and the GUI for visualizing some aspects of the work. The tool should be
friendly for your favorite editors (Emacs,
VSCode etc) for easy integration to the daily workflow. (There is no plans
to write feature rich text editor inside
the IDE. It's more rational to provide the integration with the existing
text editors for professional programmers.)
What do you think?

Re: Do we need yet another IDE (SQL development assistant) for PostgreSQL?

[Dmitry Igrishin]

вс, 15 июл. 2018 г. в 22:42, Chuck Davis :

> If you decide to proceed on this project there's no need to reinvent the
> wheel.
>
> I use Netbeans for my development.  it has quite a good facility for
> working with databases and I use it regularly with Postgres.  Since
> Netbeans is now licensed under Apache 2 you might find useful code
> there.  Be assured it uses JDBC for access but JDBC is universally
> available and the folks at Postgresql have done quite a nice job with
> JDBC drivers.  Of course, this already works on all platforms.  The
> implementation is basic but very useful:  i.e. a good starting point.
>
Thank you for the point. I'm the C++ programmer and I'm author of the
C++ client library for PostgreSQL - Pgfe and I'm going to use it in this
project. But I'm not sure about the cross-platform GUI toolkit.

Re: Do we need yet another IDE (SQL development assistant) for PostgreSQL?

[Dmitry Igrishin]

вс, 15 июл. 2018 г. в 23:05, Ravi Krishna :

> What would this new IDE offer which a product like dbeaver does not have.
>
AFAIK, DBeaver:
  - covers many DBMS (I want to focus on PostgreSQL);
  - full fledged IDE with feature rich editor (I want a lightweight tool
that can be used from
command line or from GUI to help SQL code refactoring in your favorite
editor);
  - written in Java (I develop in C++).

This is what I can currently say about my plans on this project.

Re: Do we need yet another IDE (SQL development assistant) for PostgreSQL?

[Dmitry Igrishin]

вс, 15 июл. 2018 г. в 23:51, Ravi Krishna :

> 1. dbeaver covers many DBMS and even nosql.  Many shops are not one
> product specific.  That is definitely a plus.
>
On the other hand it is hard to support unique features of PostgreSQL in a
tool that attempts to cover all the world.
I can't say about DBeaver, but as a rule,
cool-abstracted-cross-DBMS-drivers are often limited and don't support many
unique features of a concrete DBMS.

> 2. Lightweight tool which can run even from command line will be a plus
> for the product you are thinking to develop.
>
Yes. In fact, I have a prototype of such a tool, and use it from command
line are useful. For example,
if I run it in *compilation* buffer of Emacs, I can easily navigate to the
place of error occured at the time of
database/schema deployment.

> 3. Who cares in what language it is developed?
>
Many people, in fact. The reasons are different.

Re: Do we need yet another IDE (SQL development assistant) for PostgreSQL?

[Dmitry Igrishin]

пн, 16 июл. 2018 г. в 1:14, Tim Cross :

>
> Dmitry Igrishin  writes:
>
> > вс, 15 июл. 2018 г. в 22:42, Chuck Davis :
> >
> >> If you decide to proceed on this project there's no need to reinvent the
> >> wheel.
> >>
> >> I use Netbeans for my development.  it has quite a good facility for
> >> working with databases and I use it regularly with Postgres.  Since
> >> Netbeans is now licensed under Apache 2 you might find useful code
> >> there.  Be assured it uses JDBC for access but JDBC is universally
> >> available and the folks at Postgresql have done quite a nice job with
> >> JDBC drivers.  Of course, this already works on all platforms.  The
> >> implementation is basic but very useful:  i.e. a good starting point.
> >>
> > Thank you for the point. I'm the C++ programmer and I'm author of the
> > C++ client library for PostgreSQL - Pgfe and I'm going to use it in this
> > project. But I'm not sure about the cross-platform GUI toolkit.
>
> The cross-platform GUI toolkit will be the challenge.
>
This is why I've consider GUI for the Windows only. And if I'll not find an
adequate GUI
toolkit (at reasonable price and/or license), there is an option to make
the GUI available
on Windows only and provide the Linux version without a GUI (at least at
the first time).

>
> Your idea to make it integrate with user's preferred editor is a good
> idea as editors are like opinions and certain anatomical parts -
> everyone has one! Finding an appropriate API to do this will be a
> challenge.
>
I see two options here: the core of the tool acts as a long-lived server or
as a short-lived
console application which communicates with the editor's plugin via
stdin/stdout.
Btw, what the text editor do you prefer? :-)

>
> I seem to remember reading somewhere that Oracle was going to remove
> swing from the core java library. I've always been a little disappointed
> with Java UIs and found they don't give the cross-platform support that
> Java originally promised, plus OSX/macOS has not made Java as welcome as
> it use to be. If you do choose Java, it will need to work under openJDK
> as this is what most Linux users will have installed.
>
For now, the possible options for the GUI part are Qt, wxWidgets or FLTK,
or even Electron.

Re: Do we need yet another IDE (SQL development assistant) for PostgreSQL?

[Dmitry Igrishin]

пн, 16 июл. 2018 г. в 2:15, Christophe Pettus :

>
> > On Jul 15, 2018, at 16:06, Dmitry Igrishin  wrote:
> >
> > The cross-platform GUI toolkit will be the challenge.
> > This is why I've consider GUI for the Windows only. And if I'll not find
> an adequate GUI
> > toolkit (at reasonable price and/or license), there is an option to make
> the GUI available
> > on Windows only and provide the Linux version without a GUI (at least at
> the first time).
>
> I'm not sure I quite understand an PostgreSQL IDE without a GUI.  Isn't
> that psql, to a first approximation?
>
In general, with psql the one can execute arbitrary SQL query either
interactively or by calling
it from another program. But there is no advanced refactoring features out
of the box. For
example, suppose, the one have a file with the following DDL commands:

  create table foo(id integer, data text, extra_data text);
  create view v1 as select * from foo;

To make this DDL file usabe with psql(1) it must be reentrant - that is
the one need to provide the DROP commands in reverse order:

  drop view v1;
  drop table foo;

  create table foo(id integer, data text, extra_data text);
  create view v1 as select * from foo;

With a large code base it's can be tedious. It's possible to automate such
a tasks and don't
worry about the reentrance at all.


> I'm also curious how you see this IDE comparing to, say, pgAdmin4.
> There's no reason we can't have multiple IDEs, of course, but when I think
> of an "integrated development environment," I think of something (along the
> lines of the JetBrains family) that handles the full stack, including
> debugging.

It is possible to create the full fledged IDE with a convenient debugging
features. But to create
such an instrument, we need financial support.

Re: Do we need yet another IDE (SQL development assistant) for PostgreSQL?

[Dmitry Igrishin]

пн, 16 июл. 2018 г. в 12:41, Albrecht Dreß :

> Am 16.07.18 00:14 schrieb(en) Tim Cross:
> >> Thank you for the point. I'm the C++ programmer and I'm author of the
> C++ client library for PostgreSQL - Pgfe and I'm going to use it in this
> project. But I'm not sure about the cross-platform GUI toolkit.
> >
> > The cross-platform GUI toolkit will be the challenge.
>
> Try Qt .  It uses c++, comes
> with a dual license (LGPL/commercial) and supports all relevant platforms:
> - Linux: will work ootb for all distos I know, without the need to ship it
> with libraries
> - macOS: includes support to create the usual bundles which go into the
> Applications folder
> - Winbloze: works fine there, too, if you insist on a broken os ;-)
>
Qt looks the best and I would like to go with it. But althought it may be a
good candidate for an
open source project, but may be too expensive for developing proprietary
software. (Recenlty,
it has become even more expensive.)

Re: Do we need yet another IDE (SQL development assistant) for PostgreSQL?

[Dmitry Igrishin]

пн, 16 июл. 2018 г. в 13:41, Tim Clarke :

> +1 for not re-inventing the wheel - building on Netbeans or the Eclipse
> project would save you heaps of time and effort and provide
> cross-platform out of the box. I use Eclipse all the time.
>
I agree and don't want to waste my time for reinventing the wheel. And I'm
also
considering Visual Studio Code as the base.

Re: Do we need yet another IDE (SQL development assistant) for PostgreSQL?

[Dmitry Igrishin]

пн, 16 июл. 2018 г. в 14:26, :

> We – and the majority of our customers - are mainly focused on Windows. We
> use pgadmin iii and our own assistants. pgadmin iv ist still too slow on
> Windows compared to pgadmin iii. That is one reason why we still use
> PostgreSQL 9.6.
>
For performance reasons I mostly use the C++ language. Thus, I think the
performance
should not be a problem here.


>
>
> That said, one requirement on a commercial tool for us would be royalty
> free distribution to our customers. It should however provide the functions
> of pgadmin iii.
>
Do you need an administration tool or an assistant for database
development? I conceived Pgspa as a
development tool, which works with source files organized in the usual way.
For example, the sources
of the schema "foo" could be organized as:
  foo/functions/*.sql
   /views/*.sql
   /triggers/*.sql
   ...
The developer works with files rather than objects retrieved from the
database and loaded
into the tree view of the GUI (like in pgAdmin and most of other similar
tools). Though, the
database browser GUI is a useful feature of course, and should be
implemented.


>
> Regards Klaus
>
>
>
>
>
> *Von:* Dmitry Igrishin 
> *Gesendet:* Sonntag, 15. Juli 2018 18:59
> *An:* pgsql-gene...@postgresql.org
> *Betreff:* Do we need yet another IDE (SQL development assistant) for
> PostgreSQL?
>
>
>
> Hello all,
>
>
>
> Colleagues. There is an idea to develop a commercial IDE for PostgreSQL
> under Windows.
>
> At the initial stage, not so much an IDE, as an assistant for the server
> side development.
>
> What features would you like to see in such an instrument? Thanks.
>

Re: Do we need yet another IDE (SQL development assistant) for PostgreSQL?

[Dmitry Igrishin]

пн, 16 июл. 2018 г. в 15:01, Pavel Stehule :

>
>
> 2018-07-16 13:52 GMT+02:00 Dmitry Igrishin :
>
>>
>>
>> пн, 16 июл. 2018 г. в 14:26, :
>>
>>> We – and the majority of our customers - are mainly focused on Windows.
>>> We use pgadmin iii and our own assistants. pgadmin iv ist still too slow on
>>> Windows compared to pgadmin iii. That is one reason why we still use
>>> PostgreSQL 9.6.
>>>
>> For performance reasons I mostly use the C++ language. Thus, I think the
>> performance
>> should not be a problem here.
>>
>>
>>>
>>>
>>> That said, one requirement on a commercial tool for us would be royalty
>>> free distribution to our customers. It should however provide the functions
>>> of pgadmin iii.
>>>
>> Do you need an administration tool or an assistant for database
>> development? I conceived Pgspa as a
>> development tool, which works with source files organized in the usual
>> way. For example, the sources
>> of the schema "foo" could be organized as:
>>   foo/functions/*.sql
>>/views/*.sql
>>/triggers/*.sql
>>...
>> The developer works with files rather than objects retrieved from the
>> database and loaded
>> into the tree view of the GUI (like in pgAdmin and most of other similar
>> tools). Though, the
>> database browser GUI is a useful feature of course, and should be
>> implemented.
>>
>
> Few years I am thinking about new IDE for stored procedures. Probably It
> should not be written from scratch, but It should to be multiplatform.
>
Me too :-) I have a command line prototype of the tool with the basic
functional. It's written
in C++ by using the Pgfe client library and in PL/pgSQL as the PostgreSQL
extension.


> what can be nice
>
> 1. source should be in files with GIT support
>
+1. It's the main feature. Already done.

> 2. integration with developer databese + well autocomplete support
>
It's the most hard part and could be implemented later.

> 3. formatting - SQL, PL, ..
>
 Good feature for future releases.

> 4. online code validation
>
Not sure I understand. Can you please elaborate what do you mean?

> 5. The should not be strong relation between files and schemas. Now is not
> too hard to have information what content is in some file. There can be
> physical organization (by files), and logical (by schemas, functions,
> views, ...)
>
I agree and there is no problems with it. But logical organization would be
a bit simpler
to implement, and would be suitable for the most users. Also it can be even
helpful when someone
working with foreign project since the database objects are arranged in
shelves.

> 6. good performance is important - but Java is good enough today - DBeaver
> is has good speed
>
My primary (and favorite) language still C++ :-)

>
> Regards
>
> Good luck - can be pretty hard to write it.
>
Thank you, Pavel! But I haven't decided about starting this project, since
I'm not sure about
the interest from the community.

>
> p.s. IDE for developers is some different than admin tool for
> administrators. Should be decided what is target.
>
Yeah, I'm talking about the tool for developers here.

Re: Do we need yet another IDE (SQL development assistant) for PostgreSQL?

[Dmitry Igrishin]

пн, 16 июл. 2018 г. в 16:00, Pavel Stehule :

>
>
> 2018-07-16 14:28 GMT+02:00 Dmitry Igrishin :
>
>>
>>
>> пн, 16 июл. 2018 г. в 15:01, Pavel Stehule :
>>
>>>
>>>
>>> 2018-07-16 13:52 GMT+02:00 Dmitry Igrishin :
>>>
>>>>
>>>>
>>>> пн, 16 июл. 2018 г. в 14:26, :
>>>>
>>>>> We – and the majority of our customers - are mainly focused on
>>>>> Windows. We use pgadmin iii and our own assistants. pgadmin iv ist still
>>>>> too slow on Windows compared to pgadmin iii. That is one reason why we
>>>>> still use PostgreSQL 9.6.
>>>>>
>>>> For performance reasons I mostly use the C++ language. Thus, I think
>>>> the performance
>>>> should not be a problem here.
>>>>
>>>>
>>>>>
>>>>>
>>>>> That said, one requirement on a commercial tool for us would be
>>>>> royalty free distribution to our customers. It should however provide the
>>>>> functions of pgadmin iii.
>>>>>
>>>> Do you need an administration tool or an assistant for database
>>>> development? I conceived Pgspa as a
>>>> development tool, which works with source files organized in the usual
>>>> way. For example, the sources
>>>> of the schema "foo" could be organized as:
>>>>   foo/functions/*.sql
>>>>/views/*.sql
>>>>/triggers/*.sql
>>>>...
>>>> The developer works with files rather than objects retrieved from the
>>>> database and loaded
>>>> into the tree view of the GUI (like in pgAdmin and most of other
>>>> similar tools). Though, the
>>>> database browser GUI is a useful feature of course, and should be
>>>> implemented.
>>>>
>>>
>>> Few years I am thinking about new IDE for stored procedures. Probably It
>>> should not be written from scratch, but It should to be multiplatform.
>>>
>> Me too :-) I have a command line prototype of the tool with the basic
>> functional. It's written
>> in C++ by using the Pgfe client library and in PL/pgSQL as the PostgreSQL
>> extension.
>>
>>
>>> what can be nice
>>>
>>> 1. source should be in files with GIT support
>>>
>> +1. It's the main feature. Already done.
>>
>>> 2. integration with developer databese + well autocomplete support
>>>
>> It's the most hard part and could be implemented later.
>>
>
> The basic autocomplete is necessary - table names, column names, .. It
> should not be too intelligent - but this is main benefit again generic
> already available IDE.
>
Suppose the one write
  create table foo (id integer default n
and the autocomplete shows all it knows that starts with "n". Would you be
satisfied with such an autocomplete? :-)
Me - not. (Although it is relatively easy to implement.)

>
>
>> 3. formatting - SQL, PL, ..
>>>
>>  Good feature for future releases.
>>
> 4. online code validation
>>>
>> Not sure I understand. Can you please elaborate what do you mean?
>>
>
> For PLpgSQL simple (press one key) send source code to server and
> highlight errors (it can be integrated with plpgsql_check). For SQL using
> not existing identifier, ..
>
Wow, cool! With plpgsql_check it's possible to achieve the user experience
similar to the SLIME - the IDE for Common Lisp.

>
>
>
>> 5. The should not be strong relation between files and schemas. Now is
>>> not too hard to have information what content is in some file. There can be
>>> physical organization (by files), and logical (by schemas, functions,
>>> views, ...)
>>>
>> I agree and there is no problems with it. But logical organization would
>> be a bit simpler
>> to implement, and would be suitable for the most users. Also it can be
>> even helpful when someone
>> working with foreign project since the database objects are arranged in
>> shelves.
>>
>
> I cannot to estimate the cost of these variants - I use mapping - one
> schema - one or more files, but the objects to files are divided by
> dependency - some objects can be simply updated, other not.
>
The prototype I already have can deal with DDL commands organized as the
user wish. No
need to create the objects in the order of dependency. This is a very
convenient.

>
> Very specific kind of DB objects are views. The IDE can helps with changes
> of views. I

SQL Programming Assistant for PostgreSQL

[Dmitry Igrishin]

Hello all,

I've released the first version of a simple tool, - pgspa, that can
help one to develop in SQL for PostgreSQL.
The primary goal is to provide the functional for creating the
database objects from the SQL files of
arbitrary directory hierarchy without worrying about both the
existence of these objects in the database
and about the order of SQL statements in the source files. Also by
using the extension for PostgreSQL it is
possible to remove the bunch of objects from the database schemas in
non-casade mode.

pgspa is written in C++ and absolutely free. Any feedback are welcome. Thanks.

Please, see the repository on Github for more details:
https://github.com/dmitigr/pgspa

Re: Add column with If Not Exists

[Dmitry Igrishin]

сб, 4 авг. 2018 г. в 18:57, Simon White :
>
> Hi
>
> I would like to suggest the addition of the "If not exists" to the Add
> Column feature of Postgres.  There are quite common situations where
> ensuring a column exists is important so that an update to remote
> devices will not fail but it is not so important that deprecated fields
> be removed.  This is often the case with backward compatibility.  New
> columns will not affect old systems but allows all remote devices
> running older software to be updated using the same process as new
> devices.  Once the hardware reaches end of life it will be replaced and
> the new hardware will use the new columns.  So having the ability to
> Alter the table with a series of Add Column commands ensures that the
> new records included in the update are added to the table.  Having the
> "if not exists" would remove all of this potential complexity and allows
> a quick and easy method to ensure the column exists in the table using
> simple SQL commands that will not cause errors if the column is already
> present.
Hi Simon !

Recently I've released a tool that can be used to bypass errors like
("object already exists")
Please, take a look at https://github.com/dmitigr/pgspa
Feel free to contact me for any question about this tool.

Re: PostgreSQL C Language Extension with C++ Code

[Dmitry Igrishin]

вс, 12 авг. 2018 г. в 21:40, TalGloz :
>
> I did it with the macros
>
> extern "C" {
> Datum sum_of_numbers(PG_FUNCTION_ARGS);
> PG_FUNCTION_INFO_V1(sum_of_numbers);
> }
>
> But now my compiler throws some other errors when running make:
>
> g++ --std=c++17 -fPIC -Wall -Werror -g3 -O0 -o myfunc.o -c myfunc.cpp
> -I/usr/pgsql-10/include/server -L"/usr/local/lib/libseal.a"
> -I"/usr/local/include/seal" -I"/usr/local/include/cppcodec"
> myfunc.cpp:29:10: error: conflicting declaration of C function ‘int64_t
> sum_of_numbers()’
>   int64_t sum_of_numbers(){
>   ^~
> In file included from /usr/pgsql-10/include/server/utils/array.h:64,
>  from /usr/pgsql-10/include/server/utils/acl.h:38,
>  from
> /usr/pgsql-10/include/server/catalog/objectaddress.h:18,
>  from
> /usr/pgsql-10/include/server/catalog/pg_publication.h:21,
>  from /usr/pgsql-10/include/server/utils/rel.h:21,
>  from myfunc.cpp:3:
> myfunc.cpp:25:21: note: previous declaration ‘Datum
> sum_of_numbers(FunctionCallInfo)’
>  PG_FUNCTION_INFO_V1(sum_of_numbers);
>  ^~
> /usr/pgsql-10/include/server/fmgr.h:374:14: note: in definition of macro
> ‘PG_FUNCTION_INFO_V1’
>  extern Datum funcname(PG_FUNCTION_ARGS); \
>   ^~~~
> make: *** [Makefile:16: myfunc.o] Error 1
>
> Why is it so hard to use C++ with PostgerSQL for a C extension? :)
It is not.

Re: vPgSql

[Dmitry Igrishin]

Hey Vlad
пт, 17 авг. 2018 г. в 15:31, Vlad Alexeenkov :
>
> Maybe will be useful for someone
>
> Very simple Postgres SQL client vPgSql:
>
> https://vsdev.ru
Looking nice! Thank you. But I unable to start it on Ubuntu, because
there is no bash(1) in /usr/bin.
Also, it is open source?

Re: vPgSql

[Dmitry Igrishin]

пт, 17 авг. 2018 г. в 21:19, Joshua D. Drake :
>
> On 08/17/2018 05:45 AM, Dmitry Igrishin wrote:
> > Hey Vlad
> > пт, 17 авг. 2018 г. в 15:31, Vlad Alexeenkov :
> >> Maybe will be useful for someone
> >>
> >> Very simple Postgres SQL client vPgSql:
> >>
> >> https://vsdev.ru
> > Looking nice! Thank you. But I unable to start it on Ubuntu, because
> > there is no bash(1) in /usr/bin.
>
> Bash is in /bin not /usr/bin. You should be able to edit the first line
> of the start file to fix that.
I know that, thanks :-) I just pointed out the issue to the OP.

Re: Linker errors while creating a PostgreSQL C extension function.

[Dmitry Igrishin]

вс, 19 авг. 2018 г. в 13:56, TalGloz :
>
> Hello,
>
> *I have this code for my C extension function.
> *
>
> xtern "C" { // C Headers must be inside exter "C" { } block.
> #include 
> #include 
> #include 
> #include 
> #include 
> #include 
> #include 
> #include 
>
> PG_MODULE_MAGIC;
> }
>
> // CPP Header must be outside extern "C" { } block.
> #include 
> #include 
> #include 
> #include 
> #include 
> #include  // For the ostream_iterator
>
> // External projects c++ libraries compiled and linked on running 'make'.
> #include 
> #include 
> #include 
>
> std::stringstream dec(std::string st){
>
> // Decode the base64 string into a stringstream
> auto decodeBase64 = cppcodec::base64_rfc4648::decode(st);
> std::stringstream decodeBase64SS;
> std::move(decodeBase64.begin(), decodeBase64.end(),
> std::ostream_iterator(decodeBase64SS));
>
> return decodeBase64SS;
> }
>
> std::string enc(std::string st){
>
> // Create a vector to hold the raw data
> std::vector encodeStream;
>
> // Push all the characters from the raw data string into the vector
> for (auto &ch : st){
> encodeStream.push_back((unsigned char&&)(ch));
> }
>
> // Encode the vector as base64 string
> std::string encodeBase64 =
> cppcodec::base64_rfc4648::encode(encodeStream);
> encodeStream.clear();
> return encodeBase64;
>
> }
>
> std::string seal_diff_operation(std::string decodedLocalEncParamTmp,
> std::string decodedLocalTmp1, std::string decodedLocalTmp2){
>
> std::stringstream decodedLocalEncParam;
> decodedLocalEncParam.str(decodedLocalEncParamTmp);
> std::stringstream decodedLocalT1;
> decodedLocalT1.str(decodedLocalTmp1);
> std::stringstream decodedLocalT2;
> decodedLocalT2.str(decodedLocalTmp2);
>
> // Execute seal library operations
> // Load the ecryption parameters
> seal::EncryptionParameters IntegerEncryptorParms;
> IntegerEncryptorParms.load(decodedLocalEncParam);
> // Set Context and evaluator objects
> seal::SEALContext context(IntegerEncryptorParms);
> seal::Evaluator evaluator(context);
> // Set the Encoder parameters
> seal::IntegerEncoder encoder(context.plain_modulus());
>
> // Create Ciphertexts and load Chipertext information into them
> seal::Ciphertext number1Encoded;
> seal::Ciphertext number2Encoded;
> seal::Ciphertext diffEncodedResult;
> number1Encoded.load(decodedLocalT1);
> number2Encoded.load(decodedLocalT2);
>
> // Do the diff operation on the Ciphertexts and prepare the result
> for output
> evaluator.sub(number1Encoded, number2Encoded, diffEncodedResult);
> std::stringstream encResult;
> diffEncodedResult.save(encResult);
>
> std::string output = enc(encResult.str());
> return output;
>
> }
>
> extern "C" { // Usage of CPP functions in the module must be inside extern
> "C" { } block.
> Datum seal_diff_cpp(PG_FUNCTION_ARGS){
>
> // Get the inputs
> text *t1 = PG_GETARG_TEXT_PP(0);
> text *t2 = PG_GETARG_TEXT_PP(1);
> text *encParam = PG_GETARG_TEXT_PP(2);
> std::string localT1;
> std::string localT2;
> std::string localEncParam;
> localT1 = text_to_cstring(t1);
> localT2 = text_to_cstring(t2);
> localEncParam = text_to_cstring(encParam);
>
> // Decode the parameters
> std::stringstream decodedLocalT1 = dec(localT1);
> std::stringstream decodedLocalT2 = dec(localT2);
> std::stringstream decodedLocalEncParam = dec(localEncParam);
>
> // Encode the parameters
> std::string encodedLocalT1 = enc(decodedLocalT1.str());
> std::string encodedLocalT2 = enc(decodedLocalT2.str());
> std::string outputParam =
> seal_diff_operation(decodedLocalEncParam.str(), decodedLocalT1.str(),
> decodedLocalT2.str());
>
> // Return the result
> PG_RETURN_TEXT_P(cstring_to_text_with_len(localT1.c_str(),
> localT1.size()));
> };
>
> PG_FUNCTION_INFO_V1(seal_diff_cpp);
> }
>
>
> *And I use this Makefile to create the seal_diff_cpp.so file:
> *
>
> MODULES = seal_diff_cpp
>
> PG_CONFIG = /usr/pgsql-10/bin/pg_config
> PGXS = $(shell $(PG_CONFIG) --pgxs)
> INCLUDEDIR = $(shell $(PG_CONFIG) --includedir-server)
> INCLUDE_SEAL = /usr/local/include/seal
> INCLUDE_SEAL_LIB = /usr/local/lib
> INCLUDE_CPPCODEC = /usr/local/include/cppcodec
> CXX = g++
> CXXFLAGS = -std=c++17 -fPIC -Wall -Iinclude -Werror -g -O0 -pthread \
>-I$(INCLUDEDIR) -I$(INCLUDE_SEAL) -I$(INCLUDE_CPPCODEC)
> LDFLAGS = -L$(INCLUDE_SEAL_LIB) -llibseal.a -lpthread
> include $(PGXS)
> seal_diff_cpp.so: seal_diff_cpp.o
> $(CXX) -Wl,--no-undefined -shared -o seal_diff_cpp.so
> seal_diff_cpp.o $(LDFLAGS)
>
> seal_diff_cpp.o: seal_diff_cpp.cpp
>  $(CXX) $(CXXFL

Re: Linker errors while creating a PostgreSQL C extension function.

[Dmitry Igrishin]

вс, 19 авг. 2018 г. в 15:07, TalGloz :
>
> Do you mean this command:
>
> seal_diff_cpp.so: seal_diff_cpp.o
> $(CXX) -Wl,--no-undefined -shared -o seal_diff_cpp.so
> seal_diff_cpp.o $(LDFLAGS)
>
> If yes then the -lseal is added with the $(LDFLAGS) at the end of the
> command.
You've defined LDFLAGS as:
LDFLAGS = -L$(INCLUDE_SEAL_LIB) -llibseal.a -lpthread

I mean it should be defined as:
LDFLAGS = -L$(INCLUDE_SEAL_LIB) -lseal -lpthread

Also, AFAIK, the command should be specified in one line, like that:
seal_diff_cpp.so: seal_diff_cpp.o
$(CXX) -Wl,--no-undefined -shared -o seal_diff_cpp.so
seal_diff_cpp.o $(LDFLAGS)

Re: Linker errors while creating a PostgreSQL C extension function.

[Dmitry Igrishin]

вс, 19 авг. 2018 г. в 15:46, TalGloz :
>
> OK, I've changed my line to
> LDFLAGS = -L$(INCLUDE_SEAL_LIB) -lseal -lpthread
And? Is these flags appeared in the g++ invoking command line? I mean
the following:

g++ -Wl,--no-undefined -shared -o seal_diff_cpp.so seal_diff_cpp.o
-L/usr/pgsql-10/lib   -L/usr/lib64 -Wl,--as-needed
-Wl,-rpath,'/usr/pgsql-10/lib',--enable-new-dtags

>
> In my Makefile this command is written like this (line numbers are just for
> orientation)
>
> 1: seal_diff_cpp.so: seal_diff_cpp.o
> 2:$(CXX) -Wl,--no-undefined -shared -o seal_diff_cpp.so
> seal_diff_cpp.o $(LDFLAGS)
> 3:
It's correct.

Re: Linker errors while creating a PostgreSQL C extension function.

[Dmitry Igrishin]

вс, 19 авг. 2018 г. в 15:59, TalGloz :
>
> No, they still don't appear there and same errors are shown.
Try to refactor your Makefile like this:
LDFLAGS=-L$(INCLUDE_SEAL_LIB)
LDLIBS=-lseal -lpthread

seal_diff_cpp.so: seal_diff_cpp.o
$(CXX) -Wl,--no-undefined -shared -o seal_diff_cpp.so
$(LDFLAGS) $(LDLIBS) seal_diff_cpp.o

Re: Linker errors while creating a PostgreSQL C extension function.

[Dmitry Igrishin]

вс, 19 авг. 2018 г. в 16:20, TalGloz :
>
> I'll try it later when I'm home. Is there a reason that you are linking like
> this
>
> $(CXX) -Wl,--no-undefined -shared -o seal_diff_cpp.so $(LDFLAGS) $(LDLIBS)
> seal_diff_cpp.o
>
> And not like this?
>
> $(CXX) -Wl,--no-undefined -shared -o seal_diff_cpp.so seal_diff_cpp.o
> $(LDFLAGS) $(LDLIBS)
If I recall correctly there were some problems with ordering arguments
of the linker that are specified upon calling g++.
So it is better to specify such arguments in the rest of the command.
But probably this problem is already solved and there is no difference
anymore.

Re: Linker errors while creating a PostgreSQL C extension function.

[Dmitry Igrishin]

вс, 19 авг. 2018 г. в 17:54, Tom Lane :
>
> Dmitry Igrishin  writes:
> > вс, 19 авг. 2018 г. в 16:20, TalGloz :
> >> I'll try it later when I'm home. Is there a reason that you are linking 
> >> like
> >> this
> >> $(CXX) -Wl,--no-undefined -shared -o seal_diff_cpp.so $(LDFLAGS) $(LDLIBS)
> >> seal_diff_cpp.o
> >> And not like this?
> >> $(CXX) -Wl,--no-undefined -shared -o seal_diff_cpp.so seal_diff_cpp.o
> >> $(LDFLAGS) $(LDLIBS)
>
> > If I recall correctly there were some problems with ordering arguments
> > of the linker that are specified upon calling g++.
>
> You're creating such problems, not solving them, if you put the library
> first.  Particularly with .a-style libraries, you *must* list the
> referencing .o file first, or the linker will deem the library
> unreferenced and unnecessary.
Ah, I thought just the opposite :-) ! Since I use CMake I forget this
subtlety. Thank you for pointing this out!

Re: very slow largeobject transfers through JDBC

[Dmitry Igrishin]

пт, 31 авг. 2018 г. в 16:35, Mate Varga :
>
> Hi,
>
> we're fetching binary data from pg_largeobject table. The data is not very 
> large, but we ended up storing it there. If I'm copying the data to a file 
> from the psql console, then it takes X time (e.g. a second), fetching it 
> through the JDBC driver takes at least 10x more. We don't see this difference 
> between JDBC and 'native' performance for anything except largeobjects (and 
> bytea columns, for the record).
>
> Does anyone have any advice about whether this can be tuned or what the cause 
> is?
I don't know what a reason of that, but I think it's reasonable and
quite simple to call lo_import()/lo_export() via JNI.

Re: C++ classes as result of selection in postgresql database

[Dmitry Igrishin]

пт, 14 сент. 2018 г. в 17:23, Yuriy Rusinov :
>
> Dear colleagues !
>
> I have to calculate road graph from coordinates and save it to postgresql 
> database. Is it possible to construct my classes using libpqxx API and return 
> it to client or I need to develop these API on another layer after data 
> selection ?
With the Pgfe C++ API (https://github.com/dmitigr/pgfe) it's possible
to define any class and specialize the structure template Conversions
to convert the data nicely from the database to this class and back.

Re: ORM

[Dmitry Igrishin]

сб, 29 сент. 2018 г. в 1:50, marcelo :
>
> For a new big and convoluted project I (am/was) using Devart´s
> LinqConnect as ORM.
> But today I experienced some inexplicable "object reference not set to
> an instance of an object" exceptions or other more specific to this
> libraries.
> I would wish to change the ORM.
The best ORM is vanila SQL.

Re: libpq.dll question

[Dmitry Igrishin]

пн, 1 окт. 2018 г. в 11:15, :
>
>
> Hi,
>
> admitting that windows is quite alien to me...
> I have installed a postgresql server on linux and now want to allow windows 
> clients access through a tcl gui.
> Activestate tcl is installed, and when I try to
> package require tdbc::postgresql
> I get a complaint about libpq.dll.5 missing. The postgres install kit 
> contains just a libpq.dll, and renaming it
> does not seem to help
While I have no idea what is "libpq.dll.5", but have you tryed to make
a symbolic link "libpq.dll.5" to "libpq.dll" with "mklink" command to
workaround?

Unexpected plan width with foreign data wrapper

[Dmitry Bogatov]

Hello.

I work on fixing test suite (make installcheck) failures in Multicorn
project[1]. The purpose of project is to make it possible to write
Foreign Data Wrappers[2] in Python, not in C[3].

Test suite executes SQL statements with psql and compares output with
expected. If they are different, test is considered failed.

Major source of test failures is width estimation in query plan
explaination. For example, for following statements:

CREATE EXTENSION multicorn;
CREATE server multicorn_srv foreign data wrapper multicorn options (
wrapper 'multicorn.testfdw.TestForeignDataWrapper'
);
CREATE user mapping for postgres server multicorn_srv options 
(usermapping 'test');

CREATE foreign table testmulticorn (
test1 date,
test2 timestamp
) server multicorn_srv options (
option1 'option1',
test_type 'date'
);

-- Test sort pushdown asked
EXPLAIN SELECT * FROM testmulticorn ORDER BY test1 DESC;

I have mismatch, described by this `diff expected actual`:

- Foreign Scan on testmulticorn  (cost=10.00..400.00 rows=20 width=20)
+ Foreign Scan on testmulticorn  (cost=10.00..400.00 rows=20 width=12)

Width 20 is understandable: this is value assigned to
`baserel->reltarget->width` in GetForeignRelSize callback function.
Origin of value `12' is unknown to me. I have some observations on how
width estimate propagates through callback functions, though.

Behaviour differs on different versions of postgresql:

1. Postgresql-11

 * Value assigned to `baserel->reltarget->width` in GetForeignRelSize
   callback stays same in GetForeignPaths, but in GetForeignPlan it is
   changed. In this test, from 20 to 12.

 * If I assign it in GetForeignPlan function back to value 20 (value can be
   transfered via `baserel->fdw_private` field), expected value 20 will
   appear in output of `psql`. Test fixed, but with no understanding
   why.

2. Postgresql-9.6

 * Value assigned to `baserel->reltarget->width` in GetForeignRelSize
   stays the same in GetForeignPaths and GetForeignPlan, but `psql`
   still outputs another value. Test failed.

>From what I can grep, psql output is generated in
`src/backend/commands/explain.c:1206`:

appendStringInfo(es->str, "  (cost=%.2f..%.2f rows=%.0f width=%d)",
 plan->startup_cost, plan->total_cost,
 plan->plan_rows, plan->plan_width);

But I do not understand, where these values flow from.

Questions are:

 * Why do postgresql overrides width estimate, provided by foreign data
   wrapper?
 * How to fix tests in presence of such behaviour?

By the way, documentation in [2] is outdated: it refers to
`baserel->width`, which is replaced by `baserel->reltarget->width` in
commit 19a5411, and not present in 9.6.

 [1] https://github.com/Kozea/Multicorn
 [2] https://www.postgresql.org/docs/11/fdw-callbacks.html
 [3] https://multicorn.org

Re: JSON vs. JSONB storage size

[Dmitry Dolgov]

> On Fri, Oct 11, 2019 at 1:40 PM Thomas Kellerer  wrote:
>
> I am a bit surprised by this (not because the jsonb sizes are generally
> bigger, but that the string value takes less space)
>
> Is this caused by the fact that a string value compresses better internally?

Those jsonb objects are quite small, so it could be that an alignment kicks in,
since as far as I remember, jsonb header and data should be aligned by 4 byte
boundary.

Re: jsonb_set() strictness considered harmful to data

[Dmitry Dolgov]

> On Sat, Oct 19, 2019 at 1:08 PM Tomas Vondra  
> wrote:
>
> >Here is how other implementations handle this case:
> >
> >MySQL/MariaDB:
> >
> >select json_set('{"a":1,"b":2,"c":3}', '$.a', NULL) results in:
> >   {"a":null,"b":2,"c":3}
> >
> >Microsoft SQL Server:
> >
> >select json_modify('{"a":1,"b":2,"c":3}', '$.a', NULL) results in:
> >   {"b":2,"c":3}
> >
> >Both of these outcomes make sense, given the nature of JSON objects.
> >I am actually more in favor of what MSSQL does however, I think that
> >makes the most sense of all.
> >
>
> I do mostly agree with this. The json[b]_set behavior seems rather
> surprising, and I think I've seen a couple of cases running into exactly
> this issue. I've solved that with a simple CASE, but maybe changing the
> behavior would be better. That's unlikely to be back-patchable, though,
> so maybe a better option is to create a non-strict wrappers. But that
> does not work when the user is unaware of the behavior :-(

Agree, that could be confusing. If I remember correctly, so far I've seen four
or five such complains in mailing lists, but of course number of people who
didn't reach out hackers is probably bigger.

If we want to change it, the question is where to stop? Essentially we have:

update table set data = some_func(data, some_args_with_null);

where some_func happened to be jsonb_set, but could be any strict function.

I wonder if in this case it makes sense to think about an alternative? For
example, there is generic type subscripting patch, that allows to update a
jsonb in the following way:

update table set jsonb_data[key] = 'value';

It doesn't look like a function, so it's not a big deal if it will handle NULL
values differently. And at the same time one can argue, that people, who are
not aware about this caveat with jsonb_set and NULL values, will most likely
use it due to a bit simpler syntax (more similar to some popular programming
languages).

RPC via WebSockets.

[Dmitry Igrishin]

Hello!

Who needs to talk to Postgres right from a WEB-browser via WebSockets? For
example, conveniently call storable functions/procedures?

Yes, i know about PostgREST. But I want to focus on RPC via WebSockets
without all these REST or "give me contents of that table by this URL"
stuff...

I'm thinking about writing an application which is simple to
install/deploy, simple to configure, with a connection pool built-in.
Ideally, one binary and one config in JSON format.

I need some feedback before start. So please don't hesitate to share any of
your thoughts/ideas/comments/scepticism here :-)

Thanks.

Re: RPC via WebSockets.

[Dmitry Igrishin]

On Sat, 18 Jan 2020, 05:54 Nikolay Samokhvalov, 
wrote:

> This is a very common task, definitely.
>
> As for WebSockets, there is an addition to PostgREST, postgrest-websockets
> https://github.com/diogob/postgres-websockets#readme
>
Yep, but as it's addition, it's not first class citizen and requires extra
steps to install it. While this is possible it's not user friendly. I would
like to fix this :-)


> PostgREST is not only for "give me contents", you can write functions (in
> any PL) and call them using POST /rpc/functionname.
>
This is what I want to emphasize on at first: calling functions, written in
any PL via JSON-RPC. There's also plans to execute any named queries
conveniently stored in text files, for example, foo.sql might contains:

-- @id my-query1
select :name::text

-- @id my-query2
select :age::integer

And then the caller can call these queries by ID, providing the parameter
values as simple as regular RPC call.


> There is also an attempt to build similar tool in Go (although I'm not
> sure how popular and powerful it is): pREST https://github.com/prest/prest
>
> Finally, there is a whole line of trendy and quite popular tools for
> having GraphQL on top of Postgres, including but not limited to:
> - PostGraphile https://github.com/graphile/postgraphile
> - Hasura https://github.com/hasura/graphql-engine
> - Prisma https://github.com/prisma/prisma
>
Thanks for the links!


> On Fri, Jan 17, 2020 at 13:18 Dmitry Igrishin  wrote:
>
>> Hello!
>>
>> Who needs to talk to Postgres right from a WEB-browser via WebSockets?
>> For example, conveniently call storable functions/procedures?
>>
>> Yes, i know about PostgREST. But I want to focus on RPC via WebSockets
>> without all these REST or "give me contents of that table by this URL"
>> stuff...
>>
>> I'm thinking about writing an application which is simple to
>> install/deploy, simple to configure, with a connection pool built-in.
>> Ideally, one binary and one config in JSON format.
>>
>> I need some feedback before start. So please don't hesitate to share any
>> of your thoughts/ideas/comments/scepticism here :-)
>>
>> Thanks.
>>
>>

Natural sort order extension.

[Dmitry Igrishin]

Hi,

I've implemented a PostgreSQL extension for natural sort order. I.e.
strings like "z20", "z0004", "z11", "z2" sorted in ascending order as
"z2", "z0004", "z11", "z20".

Currently it implements the type textnso which is binary-coercible
to/from the text type. It's possible to declare table columns of type
textnso and create indexes on them.

Details are here https://github.com/dmitigr/pgnso

Any feedback are welcome!

Thanks.

Re: Natural sort order extension.

[Dmitry Igrishin]

On Wed, 12 Feb 2020, 19:55 Laurenz Albe,  wrote:

> On Wed, 2020-02-12 at 18:45 +0300, Dmitry Igrishin wrote:
> > I've implemented a PostgreSQL extension for natural sort order. I.e.
> > strings like "z20", "z0004", "z11", "z2" sorted in ascending order as
> > "z2", "z0004", "z11", "z20".
> >
> > Currently it implements the type textnso which is binary-coercible
> > to/from the text type. It's possible to declare table columns of type
> > textnso and create indexes on them.
> >
> > Details are here https://github.com/dmitigr/pgnso
> >
> > Any feedback are welcome!
>
> I don't want to detract from this, but from PostgreSQL v10 on you can use
> ICU collations with the "kn-true" variant to have natural sort order.
>
Thanks for the point. But my extension works with default collation and
UTF-8 encoding. And ICU is not required.

>
>

Re: libpq and escaping array string literals

[Dmitry Igrishin]

Hey Ted,

libpq has no such a facility. If you're on C++ you may want to use Pgfe
library to work with the database arrays easy.

On Fri, 6 Mar 2020, 22:11 Ted Toth,  wrote:

> I've got so C code that interacting with a table containing a field of
> type text[]. Strings I've got to put in the array may be unicode and or
> contain single or double quotes etc ... What's the best way to escape these
> strings?
>
> Ted
>

Re: PostgreSQL 13: native JavaScript Procedural Language support ?

[Dmitry Igrishin]

чт, 26 мар. 2020 г. в 10:08, Ivan E. Panchenko :
>
>
> On 26.03.2020 03:50, Bruce Momjian wrote:
> > On Wed, Mar 25, 2020 at 05:46:27PM +0200, Marius Andreiana wrote:
> >> Thanks Tom, that makes sense. Appreciate your time to explain the context.
> >>
> >> I'll followup with Heroku.
> > Also, I have heard PL/V8 is very hard to build for packagers (because of
> > changes by Google in the way V8 is packaged), which has decreased PL/V8
> > adoption.
>
> True.
>
> Btw, Nginx made their own JS implementation
>
> https://nginx.org/en/docs/njs/index.html
>
> It might be easier in integration than V8.
Yet more lightweight JavaScript engine - https://bellard.org/quickjs/
"Small and easily embeddable: just a few C files, no external
dependency, 210 KiB of x86 code for a simple hello world program."
"Almost complete ES2019 support including modules, asynchronous
generators and full Annex B support (legacy web compatibility)."

Practical usage of large objects.

[Dmitry Igrishin]

Hello all,

As you know, PostgreSQL has a large objects facility [1]. I'm curious
are there real systems which are use this feature? I'm asking because
and I'm in doubt should the Pgfe driver [2] provide the convenient API
for working with large objects or not.

Thanks!

[1] https://www.postgresql.org/docs/12/largeobjects.html
[2] https://github.com/dmitigr/pgfe

Re: Which is the setup with lowest resources you know Postgres is used in?

[Dmitry Igrishin]

ср, 7 окт. 2020 г. в 10:51, Thorsten Schöning :
>
> Hi all,
>
> I'm regularly reading that Postgres is often used with containers and
> in cloud environments these days, even on some not too powerful NAS.
>
> What are the lowest resource setups you know of or even host Postgres
> successfully with yourself? It's especially about RAM and CPU, if you
> needed to e.g. configure anything special to make things somewhat work
> in your stripped down environment etc.
AFAIK the default configuration of Postgres is fairly conservative and
may be a good starting point for such cases.

>
> Is there any point at which one is most likely forced to switch to
> more specialized embedded databases like SQLite? E.g. because
> Postgres requires a higher amount of resources because of it's
> architecture? Or could Postgres in theory be used everywhere where
> SQLite is used as well, as long as one is allowed to e.g. start an
> additional process?
For example, when you need to INSERT tens of thousands rows per second
on your low-cost device SQLite is a choice. Postgres is a
client-server with related overheads. Postgres requires deployment and
configuration while SQLite just works with zero-configuration (which
is a big advantage in case of IoT).
Sure, in theory Postgres can be used instead of SQLite (and vice-versa).

>
> I would like to know if there's any realistic chance to use Postgres
> in a low resources environment with little amount of RAM and somewhat
> slow CPU like the following:
>
> http://ww1.microchip.com/downloads/en/DeviceDoc/ATSAMA5D27-WLSOM1-Datasheet-60001590b.pdf
> http://ww1.microchip.com/downloads/en/DeviceDoc/SAMA5D2-Series-Data-sheet-ds60001476F.pdf
>
> One point is that I most likely need somewhat concurrent access to the
> data, because of having web services exposing that data to clients,
> daemons storing data locally only etc. OTOH, the number of concurrent
> accessed won't be too high, there won't be too much load most of the
> time. Things heavily depend on actual users of the device. Postgres'
> architecture seems to better fit that use case than e.g. SQLite.
In many cases concurrency is not a problem and in fact SQLite may
handle concurrent requests faster than Postgres. Since SQLite is
server-less and access overhead is near to zero (compared to Postgres)
each writer does its work quickly and no lock lasts for more than a
few dozen milliseconds.
On the other hand, Postgres is better in cases of really high concurrency.

Re: Which is the setup with lowest resources you know Postgres is used in?

[Dmitry Igrishin]

чт, 8 окт. 2020 г. в 00:14, raf :
>
> On Wed, Oct 07, 2020 at 01:53:44PM +0300, Dmitry Igrishin  
> wrote:
>
> > In many cases concurrency is not a problem and in fact SQLite may
> > handle concurrent requests faster than Postgres. Since SQLite is
> > server-less and access overhead is near to zero (compared to Postgres)
> > each writer does its work quickly and no lock lasts for more than a
> > few dozen milliseconds.
> > On the other hand, Postgres is better in cases of really high concurrency.
>
> Presumably, this is no longer a problem, but many years
> ago (between 14 and 10 years ago) I was using sqlite
> for a low traffic website (probably no more than 40
> users at a time), and the database became corrupted so
> often that I had had to automate rebuilding it from the
> latest backup and my own sql logs. I was very silly.
> Switching to postgres was the real solution.
As for now SQLite is a very robust solution if used properly.

Major release of advanced PostgreSQL driver written in C++

[Dmitry Igrishin]

Hello,

I'm excited to announce Pgfe 2.0.0, an advanced and feature rich
PostgreSQL driver written in C++.

Features:

- fast and robust;
- can be used as either header-only, static or shared library;
- works with database connections in both blocking and non-blocking IO manner;
- supports prepared statements with both positional and named parameters;
- provides first-class support for calling functions and procedures;
- supports advanced features of PostgreSQL, such as pipeline, COPY and
large objects;
- supports advanced error handling via exceptions and error
conditions: provides enum entry for each predefined SQLSTATE;
- provides advanced support for the client/server data conversion:
even multidimensional PostgreSQL arrays to/from any combinations of
STL containers can be performed with easy;
- provides a support of dynamic construction of SQL queries;
- allows to separate SQL queries and C++ code on the client side;
- provides simple, robust and thread-safe connection pool.

Please, see https://github.com/dmitigr/pgfe

Happy Cosmonautics Day!

Re: Major release of advanced PostgreSQL driver written in C++

[Dmitry Igrishin]

вт, 12 апр. 2022 г. в 13:59, Dominique Devienne :
>
> On Tue, Apr 12, 2022 at 12:17 PM Dmitry Igrishin  wrote:
> > I'm excited to announce Pgfe 2.0.0, an advanced and feature rich
> > PostgreSQL driver written in C++.
>
> Congrats on the release. I've been following your progress, and saw
> good stuff in your commits.
Thank you! :-)

>
> Could you please more clearly state in the project's README.md the
> minimal version
> of C++ required, and whether the interface or impl changes with higher
> versions of std?
Okay, I've added the Requirements section -
https://github.com/dmitigr/pgfe#requirements

>
> Your hello-world example (extracted below), uses lambda-auto (C++14),
> probably variadic templates (C++11 and up, C++17 for
> fold-expressions), etc... So if you require C++20,
> that's important to know (since out of bounds to me, for example).
Pgfe 2 requires C++17 compiler.

>
> In the same vein, what platforms / compilers / versions it's built and
> tested on is also useful to know upfront.
> (again, as an example, I need to be cross-platform and cross-compiler,
> including Windows and MS VS).
I've successfully tested Pgfe 2 with either GCC 11.2.0 and MSVC
19.30.30709 (VS 2022). (Not tested with a cross-compiler though.)

>
> My $0.02. And thanks for sharing your work. --DD
Thank you very much again :-)

Does PostgreSQL use atomic file creation of FS?

[Dmitry Lazurkin]

Hello.

Does PostgreSQL use atomic file creation on FS? How does PostgreSQL
catch situation when system crashes between open call and write call? I
am interesting in this because I would like use PostgreSQL on network
file system.

Thank you.

Re: Does PostgreSQL use atomic file creation of FS?

[Dmitry Lazurkin]

Thank you. But I have read this. I said about network file system only
for example. I would like to known how PostgreSQL handles this specific
case (of course if someone knowns a answer):

fd = open(file, "w");
write(fd, data);
// crash and now I have empty file which isn't correct
fsync(fd);

PS. I think PostgreSQL doesn't have this problem.

On 12/12/18 15:37, Karsten Hilbert wrote:
> On Wed, Dec 12, 2018 at 02:48:12PM +0300, Dmitry Lazurkin wrote:
>
>> Does PostgreSQL use atomic file creation on FS? How does PostgreSQL
>> catch situation when system crashes between open call and write call? I
>> am interesting in this because I would like use PostgreSQL on network
>> file system.
> I doubt we can get more certainty than this:
>
>   
> https://www.postgresql.org/docs/devel/creating-cluster.html#CREATING-CLUSTER-NFS
>
> Best,
> Karsten

Re: libpq PQexecParams & value placeholders

[Dmitry Igrishin]

пт, 14 дек. 2018 г. в 14:33, Oleg :
>
> Hi, all.
>
> Do we really need a numeric value placeholders like $1 in command string?
It's a syntax defined at the backend side.
(https://www.postgresql.org/docs/current/sql-prepare.html)

> Construction of such string for complex non-static queries is very annoying.
That's why there are libraries like Pgfe
(https://github.com/dmitigr/pgfe) or libpqtypes
(http://libpqtypes.esilo.com/)

> Why do we can't simply use $$ placeholder, which take the next value from an
> array of values?
Because $$ means a dollar-quoted opening tag
(https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING)

Re: Problem linking to libpq.lib on Windows

[Dmitry Igrishin]

чт, 14 февр. 2019 г. в 12:54, Roberto de Figueiredo Ribeiro
:
>
> Hi list,
>
>
>
> I’m building a platform on C++ that uses parts of the libpq-fe.h in it. I had 
> been previously compiling it in linux, and all was fine. Switched to windows, 
> now during linking it accuses an error, unable to find an object file.
>
>
>
> This is the line I’m compiling with:
>
>
>
> >: cl /I path\to\include main.cpp /link /libpath path\to\lib –opt:ref 
> >libpq.lib
Try this (not the colons):
cl /I "path\to\include" main.cpp /link /libpath:"path\to\lib" /opt:ref libpq.lib

Re: Long running query - connection and keepalives enabled but query not canceled

[Dmitry Vasiliev]

Tomasz Ostrowski wrote:

> The database would get an error on the connection socket only after it tries 
> to read or write to it next time. But it does not try to do this - it's busy 
> counting those quarks until statement_timeout is reached.
> 


It is unfortunate that PostgreSQL does not take into account that the client 
has disconnected and does not need to process query.
I know only one non-stable utility that can solve your problem: 
https://github.com/yandex/odyssey <https://github.com/yandex/odyssey> 
(unfortunately pgbouncer can't do that).

-- 
Dmitry Vasiliev

Re: Long running query - connection and keepalives enabled but query not canceled

[Dmitry Vasiliev]

> Tomasz Ostrowski wrote:
> 
>> The database would get an error on the connection socket only after it tries 
>> to read or write to it next time. But it does not try to do this - it's busy 
>> counting those quarks until statement_timeout is reached.
>> 
> 

I found relevant patch from postgrespro on commitfest: 
https://commitfest.postgresql.org/21/1882/

--
Dmitry Vasiliev 

Suboptimal plan when IN(...), ORDER BY and LIMIT are used (no JOINs)

[Dmitry Koterov]

Hi.

I'm trying to understand the logic which the planner uses in "WHERE x IN
(IDS) ORDER BY y LIMIT N" queries when the correct index exists in the
database.

I expected that, if IDS list is small and N is small too, the planner
should've done the following: for each element in IDS, query first N items
from the index, then union the results (up to IDS*N elements, thus small)
and limit it by N items.

Instead, the planner decides to run a bitmap index scan, fetch thousands of
rows, and then post-filter most of them. Why? Is it possible to somehow
tell the planner to use individual first-N fetches?

(SET STATISTICS to 1 for both columns doesn't change anything; also I
don't see how cardinality of any of these fields can theoretically affect
the plan: we still need first N items from each of the index sub-parts.)

CREATE TABLE roles(
  id bigint NOT NULL,
  id1 bigint,
  created_at timestamptz NOT NULL
);
CREATE INDEX ON roles(id1, created_at DESC);

EXPLAIN ANALYZE SELECT * FROM roles
WHERE id1 IN(
  '1001361878439251615', '1001349402553202617', '1001329448424677858',
  '1001348457743394950', '1001361706624116300', '1001338330225145648',
  '1001363186688934748', '1001366841628692013'
)
ORDER BY created_at DESC LIMIT 50

Limit  (cost=50171.99..50177.83 rows=50 width=42) (actual
time=206.056..208.865 rows=50 loops=1)
   ->  Gather Merge  (cost=50171.99..57802.99 rows=65404 width=42)
(actual time=206.055..208.857 rows=50 loops=1)
 Workers Planned: 2
 Workers Launched: 2
 ->  Sort  (cost=49171.97..49253.73 rows=32702 width=42)
(actual time=198.944..198.948 rows=40 loops=3)
   Sort Key: created_at DESC
   Sort Method: top-N heapsort  Memory: 31kB
   Worker 0:  Sort Method: top-N heapsort  Memory: 30kB
   Worker 1:  Sort Method: top-N heapsort  Memory: 32kB
   ->  Parallel Bitmap Heap Scan on roles
(cost=4209.08..48085.63 rows=32702 width=42) (actual
time=78.119..180.352 rows=60563 loops=3)
 Recheck Cond: (id1 = ANY
('{1001361878439251615,1001349402553202617,1001329448424677858,1001348457743394950,1001361706624116300,1001338330225145648,1001363186688934748,1001366841628692013}'::bigint[]))
 Filter: (cred_id = '1001344096118566254'::bigint)
 Rows Removed by Filter: 526
 Heap Blocks: exact=5890
 ->  Bitmap Index Scan on roles_asset_created_desc
 (cost=0.00..4189.46 rows=182139 width=0) (actual time=73.761..73.761
rows=183934 loops=1)
   Index Cond: (id1 = ANY
('{1001361878439251615,1001349402553202617,1001329448424677858,1001348457743394950,1001361706624116300,1001338330225145648,1001363186688934748,1001366841628692013}'::bigint[]))
 Planning Time: 0.590 ms
 Execution Time: 208.935 ms

Re: Why is writing JSONB faster than just JSON?

[Dmitry Dolgov]

> On Tue, Apr 13, 2021 at 11:38:04PM -0700, Mitar wrote:
> Hi!
>
> I have a project where we among other data want to store static JSON
> objects which can get pretty large (10-100 KB). I was trying to
> evaluate how it would work if we simply store it as an additional
> column in a PostgreSQL database. So I made a benchmark [1]. The
> results surprised me a bit and I am writing here because I would like
> to understand them. Namely, it looks like writing into a jsonb typed
> column is 30% faster than writing into a json typed column. Why is
> that? Does not jsonb require parsing of JSON and conversion? That
> should be slower than just storing a blob as-is?
>
> [1] https://gitlab.com/mitar/benchmark-pg-json

Interesting. I've tried to reproduce the schema and queries from the repository
above (with a different generated data though and without the app itself) and
indeed got a bit slower (not 30%, but visibly) execution for json column
instead of jsonb. There are couple of important points:

* Parsing is happening in both cases, for json it's done for validation
  purposes.

* Compression of json data is actually dominates the runtime load for large
  json objects, making other factors less visible and reducing difference in
  size (jsonb also should be a bit bigger, that's why I would think it would be
  slower).

* At the same time perf shows that json compression spends a bit more time in
  pglz_find_match (mostly around accessing and checking history entries), which
  is compression optimization via history table. So probably due to differences
  in data layout this optimization works slightly worse for plain json than for
  jsonb?

Re: Suboptimal plan when IN(...), ORDER BY and LIMIT are used (no JOINs)

[Dmitry Koterov]

Yeah, that was a plan for a query before its simplification. But effect is
still the same, and also the question is still the same - why a bitmap scan
is preferred over a number of individual index scans with fetching first 50
elements from each. (Also, replacing LIMIT 50 to LIMIT 2 doesn't seem to
change anything, although having 2 here should logically make it prefer 8
index scans with selecting 2 first elements from each over selecting 186051
rows in one bitmap index scan.)

Here is the plan for the exact query with LIMIT=2:

CREATE TABLE roles(
  id bigint NOT NULL,
  id1 bigint,
  created_at timestamptz NOT NULL
);
CREATE INDEX roles_asset_created_desc ON roles(id1, created_at DESC);

EXPLAIN ANALYZE SELECT * FROM roles
WHERE id1 IN(
  '1001361878439251615', '1001349402553202617', '1001329448424677858',
  '1001348457743394950', '1001361706624116300', '1001338330225145648',
  '1001363186688934748', '1001366841628692013'
)
ORDER BY created_at DESC LIMIT 2;

 Limit  (cost=49712.75..49712.99 rows=2 width=42) (actual
time=82.611..83.462 rows=2 loops=1)
   ->  Gather Merge  (cost=49712.75..67421.89 rows=151782 width=42) (actual
time=82.611..83.459 rows=2 loops=1)
 Workers Planned: 2
 Workers Launched: 2
 ->  Sort  (cost=48712.73..48902.46 rows=75891 width=42) (actual
time=70.404..70.404 rows=2 loops=3)
   Sort Key: created_at DESC
   Sort Method: top-N heapsort  Memory: 25kB
   Worker 0:  Sort Method: top-N heapsort  Memory: 25kB
   Worker 1:  Sort Method: top-N heapsort  Memory: 25kB
   ->  Parallel Bitmap Heap Scan on roles
 (cost=4266.99..47953.82 rows=75891 width=42) (actual time=7.854..57.664
rows=61255 loops=3)
 Recheck Cond: (id1 = ANY
('{1001361878439251615,1001349402553202617,1001329448424677858,1001348457743394950,1001361706624116300,1001338330225145648,1001363186688934748,1001366841628692013}'::bigint[]))
 Heap Blocks: exact=6886
 ->  Bitmap Index Scan on roles_asset_created_desc
 (cost=0.00..4221.46 rows=182139 width=0) (actual time=14.031..14.031
rows=186051 loops=1)
   Index Cond: (id1 = ANY
('{1001361878439251615,1001349402553202617,1001329448424677858,1001348457743394950,1001361706624116300,1001338330225145648,1001363186688934748,1001366841628692013}'::bigint[]))
 Planning Time: 0.074 ms
 Execution Time: 83.491 ms

On Wed, Apr 14, 2021 at 5:41 PM Michael Lewis  wrote:

> Your query and explain analyze output do not seem to match.
>
> Filter: (cred_id = '1001344096118566254'::bigint)
>
> I don't see anything like that in your query, nor an index that would
> support accomplishing that without filtering after fetching the 184k rows
> initially like the planner does.
>
>>

Re: Why is writing JSONB faster than just JSON?

[Dmitry Dolgov]

> On Wed, Apr 14, 2021 at 10:26:23AM -0400, Tom Lane wrote:
> Dmitry Dolgov <9erthali...@gmail.com> writes:
> >> On Tue, Apr 13, 2021 at 11:38:04PM -0700, Mitar wrote:
> >> ... Namely, it looks like writing into a jsonb typed
> >> column is 30% faster than writing into a json typed column. Why is
> >> that? Does not jsonb require parsing of JSON and conversion? That
> >> should be slower than just storing a blob as-is?
>
> > * Parsing is happening in both cases, for json it's done for validation
> >   purposes.
>
> Right.
>
> > * Compression of json data is actually dominates the runtime load for large
> >   json objects, making other factors less visible and reducing difference in
> >   size (jsonb also should be a bit bigger, that's why I would think it 
> > would be
> >   slower).
> > * At the same time perf shows that json compression spends a bit more time 
> > in
> >   pglz_find_match (mostly around accessing and checking history entries), 
> > which
> >   is compression optimization via history table. So probably due to 
> > differences
> >   in data layout this optimization works slightly worse for plain json than 
> > for
> >   jsonb?
>
> Interesting.  I recall that we made some last-minute changes in the JSONB
> physical representation after finding that the original choices resulted
> in sucky compression behavior.  But I think we were only looking at the
> resultant compressed size, not time-to-compress.
>
> My own guess about this, without having tried to reproduce it, is that
> JSONB might end up physically smaller than JSON, resulting in less work
> to push the toasted datum out to disk.  This'd depend a lot on your
> formatting habits for JSON, of course.  But in any case, it'd be worth
> comparing pg_column_size() results to see what's up with that.

Oh, of course I've missed that the input I was using was indeed
formatted, without formatting both cases perform equally well and I
can't reproduce the issue. Although if I understand correctly the
original code in question doesn't actually do any formatting.

Re: Why is writing JSONB faster than just JSON?

[Dmitry Dolgov]

> On Thu, Apr 15, 2021 at 12:47:25PM -0400, Tom Lane wrote:
> Dmitry Dolgov <9erthali...@gmail.com> writes:
> > On Wed, Apr 14, 2021 at 10:26:23AM -0400, Tom Lane wrote:
> >> My own guess about this, without having tried to reproduce it, is that
> >> JSONB might end up physically smaller than JSON, resulting in less work
> >> to push the toasted datum out to disk.  This'd depend a lot on your
> >> formatting habits for JSON, of course.  But in any case, it'd be worth
> >> comparing pg_column_size() results to see what's up with that.
>
> > Oh, of course I've missed that the input I was using was indeed
> > formatted, without formatting both cases perform equally well and I
> > can't reproduce the issue. Although if I understand correctly the
> > original code in question doesn't actually do any formatting.
>
> My point was that for JSON, after validating that the input is
> syntactically correct, we just store it as-received.  So in particular
> the amount of whitespace in the value would depend on how the client
> had chosen to format the JSON.  This'd affect the stored size of
> course, and I think it would have an effect on compression time too.

Yes, I got it and just wanted to confirm you were right - this was the
reason I've observed slowdown trying to reproduce the report.

Re: Why is writing JSONB faster than just JSON?

[Dmitry Dolgov]

> On Fri, Apr 23, 2021 at 01:56:57AM -0700, Mitar wrote:
> Hi!
> 
> On Thu, Apr 15, 2021 at 12:11 PM Dmitry Dolgov <9erthali...@gmail.com> wrote:
> > > My point was that for JSON, after validating that the input is
> > > syntactically correct, we just store it as-received.  So in particular
> > > the amount of whitespace in the value would depend on how the client
> > > had chosen to format the JSON.  This'd affect the stored size of
> > > course, and I think it would have an effect on compression time too.
> >
> > Yes, I got it and just wanted to confirm you were right - this was the
> > reason I've observed slowdown trying to reproduce the report.
> 
> Thank you for trying to reproduce the report. I did a bit more digging
> myself and I am still confused.
>
> ...
>
> So I do not know what is happening and why you cannot reproduce it.

Could you maybe get a profile with perf for both cases? Since they're
executed within a single backend, you can profile only a single pid.
Having a reasonable profiling frequency, --call-graph dwarf and probably
limit events to only user space with precise tagging (cycles:uppp)
should give an impression what's going on.

Re: Question about how to handle numeric (decimal) data types while using libpq

[Dmitry Igrishin]

On Tue, Jul 6, 2021, 14:04 Sudheer H R  wrote:

> Hello,
>
> I am trying to use libpq for interfacing with PostgreSQL from a C/C++
> based application.
>
> I have tried to use binary format of data for both sending and receiving
> data to and from server (resultFormat = 1).
>
> As I understand most binary types, int, float etc… are encoded in
> bing-endian byte order and they can be converted to appropriate host
> specific memory representations.
>
> However NUMERIC datatype is a more complex structure with multiple ints
> and pointers.
>
> I would like to know if there are published library functions that convert
> (to and from) NUMERIC field to say, long double datatype
>
>
> Any pointers in this regard is appreciated.
>
Why not just use text format for NUMERIC?

>

Re: develop a extension with cpp?

[Dmitry Igrishin]

вт, 2 нояб. 2021 г. в 20:12, huangning...@yahoo.com :
>
> Hi
> if i can develop a extension with cpp language?
Sure, you can. Please, see example -- https://github.com/dmitigr/pgnso

Re: Max connections reached without max connections reached

[Dmitry Dolgov]

> On Fri, Dec 03, 2021 at 10:32:03AM -0500, Tom Lane wrote:
> Dilip Kumar  writes:
> > On Thu, Dec 2, 2021 at 9:35 AM Dilip Kumar  wrote:
> >> I think there is no such view or anything which tells about which
> >> backend or transaction has more than 64 sub transaction.  But if we
> >> are ready to modify the code then we can LOG that information in
> >> GetNewTransactionId(), when first time we are marking it overflown.
>
> > I have prepared a small patch to log this information.
>
> Putting an elog call into GetNewTransactionId seems like a completely
> horrid idea from a performance standpoint.  Especially if you put it
> inside the XidGenLock hold, where it can block the entire system not just
> the one process.  But even without that, this seems like a performance
> penalty with basically no real-world benefit.  People who have issues
> like this are not going to want to trawl the postmaster log for such
> messages.

Maybe it's worth a trace point then, to allow collect such info sort of
on demand?

Re: LibPQ: Lifetime of PGresult passed in to PQnoticeReceiver callback

[Dmitry Igrishin]

пн, 13 дек. 2021 г. в 11:43, Dominique Devienne :
>
> Hi,
>
> The doc at 
> https://www.postgresql.org/docs/current/libpq-notice-processing.html is not 
> clear to me on whether it should be PQclear'd or not. Who manages the 
> lifetime of that PGresult?
libpq. That PGresult cleared by libpq right after the calling back to
the notice receiver.

>
> Also, the "could be called as long as either the PGconn or PGresult exist" 
> mention,
> implies we do not need to clear the notice callback before PQfinish'ing the 
> connection.
> (and that does not seem possible, given passing in a NULL pointer is just a 
> *get* it seems).
> Is that correct?
There is no need to reset neither the notice receiver nor notice
processor before the call of PQfinish().

>
> Yet we are seeing our notice callback being called with an invalid PGresult 
> (i.e. crash accessing it).
> What could be the reasons for that? I'm assuming user-error in our code, but 
> I don't see why yet.
Please, ensure that there's no call of PQfinish() while either the
notice receiver or notice processor is runned.
Also, if the notice receiver delegates processing to another thread
then the PGresult must be copied and cleared by the application after
done.

PS. Please, avoid the notice processor.

Re: How to write such a query?

[Dmitry Igrishin]

On Thu, Jan 6, 2022, 09:40 Igor Korot  wrote:

> Hi, ALL,
> In SQLite you can write:
>
> SELECT a, b, c FROM foo WHERE id = :id;
>
> where ":id" is the named parameter.
>
> The query above is similar to
>
> SELECT a,b,c FROM foo WHERE id = ?;
>
> except that the parameter has a name.
>
> Is there a way to write such a SELECT statement with the
> named parameter in PostgreSQL?
>
Named parameters of prepared statements are implemented in my C++ library
Pgfe.

Implementation of gtrgm_out for gevel

[Dmitry Lazurkin]

Hello.

I want use gevel (http://www.sai.msu.su/~megera/wiki/Gevel) for investigation 
of pg_trgm gist. But for gist_print i need implement gtrgm_out for pg_trgm gist 
node. And i have a problem here.

-

My version:

PostgreSQL 9.6.6 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 
5.4.0-6ubuntu1~16.04.5) 5.4.0 20160609, 64-bit

-

Function:

Datum
gtrgm_out(PG_FUNCTION_ARGS)
{
    GISTENTRY  *entry = (GISTENTRY *) PG_GETARG_POINTER(0); /* I think entry is 
pointer to bad memory */
    char       *result;
    char    buf[1024];

    if (entry->leafkey) /* Why leafkey is always true and has strange value? */
    {   
/* This is from gtrgm_compress */ 

/* text *val = DatumGetTextPP(entry->key); <-- Segfault here */
        /* result = strndup(VARDATA_ANY(val), VARSIZE_ANY_EXHDR(val)); */
        sprintf(buf, "leafkey %c", entry->leafkey);
    }
    else
    {
        sprintf(buf, "leafkey %c", entry->leafkey);
    }

    result = pstrdup(buf);
    PG_RETURN_CSTRING(result);
}

-

Call gtrgm_out:

select * from gist_print('test1_trgm') as t(level int, valid bool, a gtrgm);
 level | valid |  a
---+---+--
 1 | t | leafkey \x7F
 2 | t | leafkey \x7F
 ...
 2 | t | leafkey v
 ...
 2 | t | leafkey \x7F
 ...
 2 | t | leafkey v
 2 | t | leafkey \x7F
 ...

Can anyone give me some advice? 

Thanks.

Re: Implementation of gtrgm_out for gevel

[Dmitry Lazurkin]

On 23.11.2017 21:58, Tom Lane wrote:
> Dmitry Lazurkin  writes:
>> Datum
>> gtrgm_out(PG_FUNCTION_ARGS)
>> {
>>     GISTENTRY  *entry = (GISTENTRY *) PG_GETARG_POINTER(0); /* I think entry 
>> is pointer to bad memory */
> The argument is TRGM *, I think ... certainly not GISTENTRY *.
>
>   regards, tom lane

Hmm. I will try to debug gist_print.

Re: Implementation of gtrgm_out for gevel

[Dmitry Lazurkin]

On 11/23/2017 09:58 PM, Tom Lane wrote:
> Dmitry Lazurkin  writes:
>> Datum
>> gtrgm_out(PG_FUNCTION_ARGS)
>> {
>>     GISTENTRY  *entry = (GISTENTRY *) PG_GETARG_POINTER(0); /* I think entry 
>> is pointer to bad memory */
> The argument is TRGM *, I think ... certainly not GISTENTRY *.
>
>   regards, tom lane

You are right. Thank you.

something weird happened - can select by column value although column value exist

[Dmitry O Litvintsev]

Hi, 

Today the following happened:

Found this error in my production log:

< 2018-10-11 13:31:52.587 CDT >ERROR:  insert or update on table "file" 
violates foreign key constraint "$1"
< 2018-10-11 13:31:52.587 CDT >DETAIL:  Key (volume)=(155303) is not present in 
table "volume".
< 2018-10-11 13:31:52.587 CDT >STATEMENT:  
INSERT INTO file 
(sanity_crc,uid,pnfs_id,crc,deleted,cache_mod_time,drive,volume,sanity_size,cache_status,gid,location_cookie,cache_location,original_library,bfid,pnfs_path,size)
 VALUES (
4002229874,0,'',256655919,'u','2018-10-11 13:31:52','',(SELECT 
id FROM volume where label='A'),65536,'B',0,'C','D','E','F','',197243) 
RETURNING *

file table references volume table on file.volume = volume.id and file.volume 
is FK to volume.id. I doctored the query for privacy replacing string values 
with 'A', 'B'. ... 

(Queries similar to the above quoted  are executed by an application and run 
thousand of times every day for years) 

So, the problem:

SELECT id FROM volume where label='A';
   id   

 155303
(1 row)

BUT:

select * from volume where id = 155303;
 ...
(0 rows)

?!

id is a sequence:

id| integer | not null default 
nextval(('volume_seq'::text)::regclass)


This entry id = 155303 has existed for some time and has a lot of existing file 
entries holding 
FK reference to volume id = 155303

I "fixed" the issue just by:

update volume set id  = 155303 where label='A';

BUT It did not work  right away. Meaning I did this once:

update volume set id  = 155303 where label='A';

no effect. 

I did it again, I also did it;

update volume set id = (select id from volume where  label='A');

and then again 

update volume set id  = 155303 where label='A';

eventually it worked. Now, 


select count(*) from volume where label='A';
 count 
---
 1
(1 row)


What is this? Version 9.3.9,. running on Linux RH6.

Thanks,
Dmitry

ownership of "/var/run/postgresql"

[Dmitry O Litvintsev]

Hi, 

we run 9.6 postgresql DB on Linux box. We run as different user than postgres. 
To get it to work we had to chown /var/run/postgresql to be owned by the 
process user. 

Upgraded to 11 and now we see that file 

/var/run/postgresql changes ownership to postgres:postgres on reboot , even 
though postgresql-11.service is disabled. 

What is doing it and is it possible to disable it?

Thanks, 
Dmitry

how to check that recovery is complete

[Dmitry O Litvintsev]

Hi, 

I have a workflow where I recover from PITR backup and run a query on it. The 
program that runs query 
checks that it can connect to database in a loop, until it can, and then runs 
the query. 
This has worked fine far. Recently I upgraded to 11 and I see that I can 
connect to DB while recovery is 
not complete yet. See this:

< 2020-11-05 03:34:36.114 CST  >LOG:  starting archive recovery
< 2020-11-05 03:34:36.590 CST  >LOG:  restored log file 
"000102EF00F9" from archive
< 2020-11-05 03:34:36.641 CST  >LOG:  redo starts at 2EF/F928
...
< 2020-11-05 03:34:46.392 CST  >LOG:  restored log file 
"000102F8" from archive
< 2020-11-05 03:34:46.658 CST 127.0.0.1 >FATAL:  the database system is 
starting up
< 2020-11-05 03:34:47.028 CST  >LOG:  restored log file 
"000102F9" from archive

You can see above fail to connect , but sometime into recover I see;

< 2020-11-05 04:07:51.987 CST  >LOG:  restored log file 
"000102F20029" from archive
< 2020-11-05 04:08:23.195 CST 127.0.0.1 >ERROR:  canceling statement due to 
conflict with recovery
< 2020-11-05 04:08:23.195 CST 127.0.0.1 >DETAIL:  User query might have needed 
to see row versions that must be removed.
< 2020-11-05 04:08:23.195 CST 127.0.0.1 >STATEMENT:  select count(*) from file
< 2020-11-05 04:08:23.195 CST  >FATAL:  terminating connection due to 
administrator command
< 2020-11-05 04:08:23.195 CST  >STATEMENT:  select count(*) from file
< 2020-11-05 04:08:23.195 CST  >FATAL:  terminating connection due to 
administrator command
< 2020-11-05 04:08:23.195 CST  >STATEMENT:  select count(*) from file
< 2020-11-05 04:08:23.232 CST  >LOG:  background worker "parallel worker" (PID 
13577) exited with exit code 1
< 2020-11-05 04:08:23.244 CST  >LOG:  background worker "parallel worker" (PID 
13578) exited with exit code 1
< 2020-11-05 04:08:23.244 CST 127.0.0.1 >FATAL:  terminating connection due to 
conflict with recovery
< 2020-11-05 04:08:23.244 CST 127.0.0.1 >DETAIL:  User query might have needed 
to see row versions that must be removed.
< 2020-11-05 04:08:23.244 CST 127.0.0.1 >HINT:  In a moment you should be able 
to reconnect to the database and repeat your command.
< 2020-11-05 04:08:25.354 CST  >LOG:  restored log file 
"000102F2002A" from archive
< 2020-11-05 04:08:55.555 CST 127.0.0.1 >ERROR:  canceling statement due to 
conflict with recovery
< 2020-11-05 04:08:55.555 CST 127.0.0.1 >DETAIL:  User query might have needed 
to see row versions that must be removed.
< 2020-11-05 04:08:55.555 CST 127.0.0.1 >STATEMENT:  select count(*) from file
< 2020-11-05 04:08:55.556 CST  >FATAL:  terminating connection due to 
administrator command
< 2020-11-05 04:08:55.556 CST  >STATEMENT:  select count(*) from file
< 2020-11-05 04:08:55.561 CST  >FATAL:  terminating connection due to 
administrator command
< 2020-11-05 04:08:55.561 CST  >STATEMENT:  select count(*) from file
< 2020-11-05 04:08:55.640 CST  >LOG:  background worker "parallel worker" (PID 
13683) exited with exit code 1
< 2020-11-05 04:08:55.653 CST  >LOG:  background worker "parallel worker" (PID 
13684) exited with exit code 1
< 2020-11-05 04:08:55.653 CST 127.0.0.1 >FATAL:  terminating connection due to 
conflict with recovery
< 2020-11-05 04:08:55.653 CST 127.0.0.1 >DETAIL:  User query might have needed 
to see row versions that must be removed.
< 2020-11-05 04:08:55.653 CST 127.0.0.1 >HINT:  In a moment you should be able 
to reconnect to the database and repeat your command.
< 2020-11-05 04:09:00.307 CST  >LOG:  restored log file 
"000102F2002B" from archive

As you can see a query "select count(*) from file" failed due to table not 
being restored yet. BUT connection was allowed 
before DB was ready Only few hours after ;

< 2020-11-05 09:31:30.319 CST  >LOG:  archive recovery complete
< 2020-11-05 09:34:51.729 CST  >LOG:  database system is ready to accept 
connections

After which the query runs fine without errors. This is bad because I see that 
select count(*) takes progressively 
longer to execute and the count(*) is not what I am interested in. I run the 
"real" query after that. As a result I add
hours to program execution time. 

Is there a more robust method to it? Ideally I do not want to be able to 
connect to db until :

< 2020-11-05 09:31:30.319 CST  >LOG:  archive recovery complete
< 2020-11-05 09:34:51.729 CST  >LOG:  database system is ready to accept 
connections


And I believe this was the behavior before upgrade. If connection can't be 
disabled, how can I detect 
condition "database system is ready to accept connections"

I believe "pg_isready" utility would succeed once it can connect. And as can 
see I could 
connect way before DB is really ready. 

Thanks!
Dmitry

postgresql11-devel RPM is missing from "Direct RPM downloads"

[Dmitry O Litvintsev]

Hi,

Where can I find postgresq11-devel RPM ?

It is missing from direct RPM download page :

https://yum.postgresql.org/11/redhat/rhel-7-x86_64/repoview/

or

https://yum.postgresql.org/11/redhat/rhel-7-x86_64/repoview/postgresqldbserver11.group.html

Thanks,
Dmitry

Re: postgresql11-devel RPM is missing from "Direct RPM downloads"

[Dmitry O Litvintsev]

D'oh. Thanks!

/--\
| Tel:   (630) 840 5005|
| FAX:   (630) 840 2968|
|(630) 840 2783|
| office:FCC 240   |
| E-mail:litvi...@fnal.gov |
\--/

On Wed, 5 Jun 2019, Jakub Olczyk wrote:

> Hi Dimiry,
>
> On 04/06/2019 23:49, Dmitry O Litvintsev wrote:
>> Where can I find postgresq11-devel RPM ?
>> [...]
>> https://urldefense.proofpoint.com/v2/url?u=https-3A__yum.postgresql.org_11_redhat_rhel-2D7-2Dx86-5F64_repoview_&d=DwICaQ&c=gRgGjJ3BkIsb5y6s49QqsA&r=7PHi3TDlwkvpc07MjENbOxVFl0u_sEurf250JnUFWCU&m=x0FsGjpFkd074Owr0TbLitPaA8Y3iFdjJIgsUZQKx4Q&s=64-qFF0vgct9WdlN14qDUr3Dx58YQv0sWwCFLg2D8M4&e=
>
> have you looked under the letter "P" in your first link?
> There you can find the -devel package.
>
> https://urldefense.proofpoint.com/v2/url?u=https-3A__yum.postgresql.org_11_redhat_rhel-2D7-2Dx86-5F64_repoview_letter-5Fp.group.html&d=DwICaQ&c=gRgGjJ3BkIsb5y6s49QqsA&r=7PHi3TDlwkvpc07MjENbOxVFl0u_sEurf250JnUFWCU&m=x0FsGjpFkd074Owr0TbLitPaA8Y3iFdjJIgsUZQKx4Q&s=9BjL2HUlWfddhgpV1vnwDsRjRXNO4d1DNN0USk3J1uE&e=
>
> Cheers!
> Jakub
>
>

Seeing new stuff in log after upgrading from 11 to 15

[Dmitry O Litvintsev]

Hi, 

After upgrade 11 -> 15 started to see these messages in hot standby log:

< 2024-05-15 17:20:24.164 CDT  151879 > LOG:  restartpoint complete: wrote 
296338 buffers (28.3%); 0 WAL file(s) added, 134 removed, 0 recycled; 
write=1619.469 s, sync=0.022 s, total=1619.539 s; sync files=1492, 
longest=0.002 s, average=0.001 s; distance=2195446 kB, estimate=4171347 kB
< 2024-05-15 17:20:24.164 CDT  151879 > LOG:  recovery restart point at 
17AB8/47000140
< 2024-05-15 17:20:24.164 CDT  151879 > DETAIL:  Last completed transaction was 
at log time 2024-05-15 17:20:24.138362-05.
< 2024-05-15 17:20:24.167 CDT  151879 > LOG:  restartpoint starting: wal

while running 11 the log was almost empty, and I did not modify verbosity 
settings... 
Queston : are above harmless and just informational?
I checked the data and the data is definitely being replicated.

Thank you, 
Dmitry

Re: Seeing new stuff in log after upgrading from 11 to 15

[Dmitry O Litvintsev]

Thanks for the quick reply! NP, I was just being extra cautious after upgrade.



From: Tom Lane 
Sent: Wednesday, May 15, 2024 6:50 PM
To: Dmitry O Litvintsev
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Seeing new stuff in log after upgrading from 11 to 15

[EXTERNAL] – This message is from an external sender

Dmitry O Litvintsev  writes:
> After upgrade 11 -> 15 started to see these messages in hot standby log:

> < 2024-05-15 17:20:24.164 CDT  151879 > LOG:  restartpoint complete: wrote 
> 296338 buffers (28.3%); 0 WAL file(s) added, 134 removed, 0 recycled; 
> write=1619.469 s, sync=0.022 s, total=1619.539 s; sync files=1492, 
> longest=0.002 s, average=0.001 s; distance=2195446 kB, estimate=4171347 kB
> < 2024-05-15 17:20:24.164 CDT  151879 > LOG:  recovery restart point at 
> 17AB8/47000140
> < 2024-05-15 17:20:24.164 CDT  151879 > DETAIL:  Last completed transaction 
> was at log time 2024-05-15 17:20:24.138362-05.
> < 2024-05-15 17:20:24.167 CDT  151879 > LOG:  restartpoint starting: wal

> while running 11 the log was almost empty, and I did not modify verbosity 
> settings...
> Queston : are above harmless and just informational?

This is checkpoint logging, which is on by default now (a decision
I didn't particularly approve of).  Feel free to set
"log_checkpoints = off" if you don't want it.

regards, tom lane

Confusing error message in 15.6

[Dmitry O Litvintsev]

Hi, 

I am observing the following error which confuses me:

#  psql -U postgres template1 -c "checkpoint; select 
pg_backup_start('${dest}.tar.Z', true)"
CHECKPOINT
 pg_backup_start 
-
 17BF7/3009498
(1 row)

#  psql -U postgres template1 -c "select pg_backup_stop(true)" 
ERROR:  backup is not in progress
HINT:  Did you call pg_backup_start()?

This is postgresql 15.6 running on RH 7. 

This is not just amusing, it is breaking my backup script after update from 11 
to 15 (and change from pg_{start,stop}_backup to pg_backup_{start_stop})

Thanks,
Dmitry

Re: Confusing error message in 15.6

[Dmitry O Litvintsev]

Oops. This means major rewrite of my backup procedure.  Thanks for pointing 
this out. I will likely just switch to pg_basebackup. What I have is something 
old and gnarly from the days of psql version 8.


From: David G. Johnston 
Sent: Tuesday, May 21, 2024 6:42 PM
To: Dmitry O Litvintsev
Cc: pgsql-generallists.postgresql.org
Subject: Re: Confusing error message in 15.6

[EXTERNAL] – This message is from an external sender


On Tue, May 21, 2024, 17:29 Dmitry O Litvintsev 
mailto:litvi...@fnal.gov>> wrote:
Hi,

I am observing the following error which confuses me:

#  psql -U postgres template1 -c "checkpoint; select 
pg_backup_start('${dest}.tar.Z', true)"
CHECKPOINT
 pg_backup_start
-
 17BF7/3009498
(1 row)

#  psql -U postgres template1 -c "select pg_backup_stop(true)"
ERROR:  backup is not in progress
HINT:  Did you call pg_backup_start()?

This is postgresql 15.6 running on RH 7.

This is not just amusing, it is breaking my backup script after update from 11 
to 15 (and change from pg_{start,stop}_backup to pg_backup_{start_stop})


Yep, nowadays you must keep the transaction where you issued backup start open 
until you issue backup end.  Using -c isn't going to cut it.

David J.

fail to install postgresql15 on Alma9

[Dmitry O Litvintsev]

Hello, 

I am foillowing instructions on 

https://www.postgresql.org/download/linux/redhat/

I select version "15"
I select "Red Hat Enterprise, Rocky, AlmaLinux or or Oracle 9"

I get this command to run:

"dnf install -y 
https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm";

But then I fail to install postgersql15:

# yum install postgresql15-server 
Last metadata expiration check: 0:22:07 ago on Tue 18 Jun 2024 12:16:52 PM CDT.
No match for argument: postgresql15-server
Error: Unable to find a match: postgresql15-server

I can install postgresql16-server sussessfully but not 15. 
I need 15. What am I doing wrong?

Thank you, 
Dmitry

Re: fail to install postgresql15 on Alma9

[Dmitry O Litvintsev]

Yes I did. Sorry did not mention that. I ran the

dnf -qy module sisable postgresql

prior to running.

dnf install postgresql15-server

(so that did not help)



From: Adrian Klaver 
Sent: Tuesday, June 18, 2024 12:44 PM
To: Dmitry O Litvintsev; pgsql-generallists.postgresql.org
Subject: Re: fail to install postgresql15 on Alma9

[EXTERNAL] – This message is from an external sender

On 6/18/24 10:40 AM, Dmitry O Litvintsev wrote:
> Hello,
>
> I am foillowing instructions on
>
> https://urldefense.proofpoint.com/v2/url?u=https-3A__www.postgresql.org_download_linux_redhat_&d=DwIFaQ&c=gRgGjJ3BkIsb5y6s49QqsA&r=7PHi3TDlwkvpc07MjENbOxVFl0u_sEurf250JnUFWCU&m=mN1kv7hRIfX0oHlFSSc1RH4ouENojXHa2_3698E0kc58mIkDJKb2bf8RjnP6ZWob&s=I2ltoVawe3G4fDFhBnctZ1KL0SOhQ6aSbVqMSepdekg&e=
>
> I select version "15"
> I select "Red Hat Enterprise, Rocky, AlmaLinux or or Oracle 9"
>
> I get this command to run:
>
> "dnf install -y 
> https://urldefense.proofpoint.com/v2/url?u=https-3A__download.postgresql.org_pub_repos_yum_reporpms_EL-2D9-2Dx86-5F64_pgdg-2Dredhat-2Drepo-2Dlatest.noarch.rpm&d=DwIFaQ&c=gRgGjJ3BkIsb5y6s49QqsA&r=7PHi3TDlwkvpc07MjENbOxVFl0u_sEurf250JnUFWCU&m=mN1kv7hRIfX0oHlFSSc1RH4ouENojXHa2_3698E0kc58mIkDJKb2bf8RjnP6ZWob&s=pe18CD7xIVa4f2Npl1shD05CkZ2fTQSJQEWbhzzB8lI&e=
>  "
>
> But then I fail to install postgersql15:
>
> # yum install postgresql15-server
> Last metadata expiration check: 0:22:07 ago on Tue 18 Jun 2024 12:16:52 PM 
> CDT.
> No match for argument: postgresql15-server
> Error: Unable to find a match: postgresql15-server
>
> I can install postgresql16-server sussessfully but not 15.
> I need 15. What am I doing wrong?

I don't use RH, still the instructions here:

https://urldefense.proofpoint.com/v2/url?u=https-3A__www.postgresql.org_download_linux_redhat_&d=DwIFaQ&c=gRgGjJ3BkIsb5y6s49QqsA&r=7PHi3TDlwkvpc07MjENbOxVFl0u_sEurf250JnUFWCU&m=mN1kv7hRIfX0oHlFSSc1RH4ouENojXHa2_3698E0kc58mIkDJKb2bf8RjnP6ZWob&s=I2ltoVawe3G4fDFhBnctZ1KL0SOhQ6aSbVqMSepdekg&e=

Mention doing:

# Disable the built-in PostgreSQL module:
sudo dnf -qy module disable postgresql

Did you do the above?

Then:

# Install PostgreSQL:
sudo dnf install -y postgresql15-server


>
> Thank you,
> Dmitry
>
>

--
Adrian Klaver
adrian.kla...@aklaver.com

Re: fail to install postgresql15 on Alma9

[Dmitry O Litvintsev]

dnf -qy module sisable postgresql -> "dnf -qy module disable postgresql". 
Proper syntax was used. :)

____
From: Dmitry O Litvintsev 
Sent: Tuesday, June 18, 2024 12:48 PM
To: Adrian Klaver; pgsql-generallists.postgresql.org
Subject: Re: fail to install postgresql15 on Alma9

[EXTERNAL] – This message is from an external sender

Yes I did. Sorry did not mention that. I ran the

dnf -qy module sisable postgresql

prior to running.

dnf install postgresql15-server

(so that did not help)



From: Adrian Klaver 
Sent: Tuesday, June 18, 2024 12:44 PM
To: Dmitry O Litvintsev; pgsql-generallists.postgresql.org
Subject: Re: fail to install postgresql15 on Alma9

[EXTERNAL] – This message is from an external sender

On 6/18/24 10:40 AM, Dmitry O Litvintsev wrote:
> Hello,
>
> I am foillowing instructions on
>
> https://urldefense.proofpoint.com/v2/url?u=https-3A__www.postgresql.org_download_linux_redhat_&d=DwIFaQ&c=gRgGjJ3BkIsb5y6s49QqsA&r=7PHi3TDlwkvpc07MjENbOxVFl0u_sEurf250JnUFWCU&m=mN1kv7hRIfX0oHlFSSc1RH4ouENojXHa2_3698E0kc58mIkDJKb2bf8RjnP6ZWob&s=I2ltoVawe3G4fDFhBnctZ1KL0SOhQ6aSbVqMSepdekg&e=
>
> I select version "15"
> I select "Red Hat Enterprise, Rocky, AlmaLinux or or Oracle 9"
>
> I get this command to run:
>
> "dnf install -y 
> https://urldefense.proofpoint.com/v2/url?u=https-3A__download.postgresql.org_pub_repos_yum_reporpms_EL-2D9-2Dx86-5F64_pgdg-2Dredhat-2Drepo-2Dlatest.noarch.rpm&d=DwIFaQ&c=gRgGjJ3BkIsb5y6s49QqsA&r=7PHi3TDlwkvpc07MjENbOxVFl0u_sEurf250JnUFWCU&m=mN1kv7hRIfX0oHlFSSc1RH4ouENojXHa2_3698E0kc58mIkDJKb2bf8RjnP6ZWob&s=pe18CD7xIVa4f2Npl1shD05CkZ2fTQSJQEWbhzzB8lI&e=
>  "
>
> But then I fail to install postgersql15:
>
> # yum install postgresql15-server
> Last metadata expiration check: 0:22:07 ago on Tue 18 Jun 2024 12:16:52 PM 
> CDT.
> No match for argument: postgresql15-server
> Error: Unable to find a match: postgresql15-server
>
> I can install postgresql16-server sussessfully but not 15.
> I need 15. What am I doing wrong?

I don't use RH, still the instructions here:

https://urldefense.proofpoint.com/v2/url?u=https-3A__www.postgresql.org_download_linux_redhat_&d=DwIFaQ&c=gRgGjJ3BkIsb5y6s49QqsA&r=7PHi3TDlwkvpc07MjENbOxVFl0u_sEurf250JnUFWCU&m=mN1kv7hRIfX0oHlFSSc1RH4ouENojXHa2_3698E0kc58mIkDJKb2bf8RjnP6ZWob&s=I2ltoVawe3G4fDFhBnctZ1KL0SOhQ6aSbVqMSepdekg&e=

Mention doing:

# Disable the built-in PostgreSQL module:
sudo dnf -qy module disable postgresql

Did you do the above?

Then:

# Install PostgreSQL:
sudo dnf install -y postgresql15-server


>
> Thank you,
> Dmitry
>
>

--
Adrian Klaver
adrian.kla...@aklaver.com

Help. The database was created using collation version 2.17, but the operating system provides version 2.34.

[Dmitry O Litvintsev]

Hello, 

I am in the process of migrating DB to Alma9 host. The databse 
is rather large - few TBs. 

I have run pg_basebackup on Alma9 host and established replication from 
production to it. The idea is to quickly switch from master to this new host 
during downtime. 

Establishing replication went fine. Source postgresql version is 15.6, 
destination is 15.7

When I psql into replica I get:

WARNING:  database "xxx" has a collation version mismatch
DETAIL:  The database was created using collation version 2.17, but the 
operating system provides version 2.34.
HINT:  Rebuild all objects in this database that use the default collation and 
run ALTER DATABASE xxx REFRESH COLLATION VERSION, or build PostgreSQL with the 
right library version.

Looking up the issue the solution seems to be 

  REINDEX database xxx 
  ALTER DATABASE xxx REFRESH COLLATION VERSION

But this defeats the whole idea of having short downtime because REINDEX will 
take forever. 

What is this "or build PostgreSQL with the right library version"?
Is this about 15.7 vs 15.6 or is it about different glibc version between RH7 
and Alma9?

Is there a better way to handle it? I cannot afford long downtime. 
This came up rather unexpectedly and I am now in a tight situation having to 
find solution fast. I do not recall having similar issue when going from RH6 to 
RH7. 

Thank you for your help.

Re: Help. The database was created using collation version 2.17, but the operating system provides version 2.34.

[Dmitry O Litvintsev]

Thank you very much for help and pointers to useful information.

Just want to make clear (sorry I am slow on uptake). I should first REINDEX and 
then ALTER DATABASE xxx REFRESH COLLATION VERSION, or first ALTER and then 
REINDEX or does the order of these action matter at all?

Thank you,
Dmitry


From: Daniel Verite 
Sent: Thursday, June 20, 2024 7:02 AM
To: Dmitry O Litvintsev
Cc: pgsql-generallists.postgresql.org
Subject: Re: Help. The database was created using collation version 2.17, but 
the operating system provides version 2.34.

[EXTERNAL] – This message is from an external sender

Dmitry O Litvintsev wrote:

> WARNING:  database "xxx" has a collation version mismatch
> DETAIL:  The database was created using collation version 2.17, but the
> operating system provides version 2.34.
> HINT:  Rebuild all objects in this database that use the default collation
> and run ALTER DATABASE xxx REFRESH COLLATION VERSION, or build PostgreSQL
> with the right library version.

This upgrade comprises the major change in GNU libc 2.28,
so indeed text indexes created by 2.17 are very likely unsafe to use
on your new server.
See 
https://urldefense.proofpoint.com/v2/url?u=https-3A__wiki.postgresql.org_wiki_Locale-5Fdata-5Fchanges&d=DwIFaQ&c=gRgGjJ3BkIsb5y6s49QqsA&r=7PHi3TDlwkvpc07MjENbOxVFl0u_sEurf250JnUFWCU&m=NhYMQQwCXq5TXHFVjyJeB1eB9KkyHC8FU3lyswgVpUMpAzADq2cffu-xjSUeNCND&s=WKdD4hr8nBJTkQtIcLMagxuGK1yAPTyU2VOmQARksl8&e=

>  REINDEX database xxx
> ALTER DATABASE xxx REFRESH COLLATION VERSION
> But this defeats the whole idea of having short downtime because REINDEX
> will take forever.

The indexes that don't involve collatable types (text,varchar), and those
that use the C collation don't need to be reindexed.
Maybe you can reduce significantly the downtime by including only
the ones that matter.

The wiki page gives the query to obtain the list of affected indexes:

SELECT DISTINCT indrelid::regclass::text, indexrelid::regclass::text,
collname, pg_get_indexdef(indexrelid)
FROM (SELECT indexrelid, indrelid, indcollation[i] coll FROM pg_index,
generate_subscripts(indcollation, 1) g(i)) s
  JOIN pg_collation c ON coll=c.oid
WHERE collprovider IN ('d', 'c') AND collname NOT IN ('C', 'POSIX');

> I do not recall having similar issue when going from RH6 to RH7.

This warning was added relatively recently, in Postgres 15 (october 2022).


Best regards,
--
Daniel Vérité
https://urldefense.proofpoint.com/v2/url?u=https-3A__postgresql.verite.pro_&d=DwIFaQ&c=gRgGjJ3BkIsb5y6s49QqsA&r=7PHi3TDlwkvpc07MjENbOxVFl0u_sEurf250JnUFWCU&m=NhYMQQwCXq5TXHFVjyJeB1eB9KkyHC8FU3lyswgVpUMpAzADq2cffu-xjSUeNCND&s=yED6Nru4eGTULRzJymNtMgJjXhgirkjOuDzCQnae9Go&e=
Twitter: @DanielVerite

Re: Help. The database was created using collation version 2.17, but the operating system provides version 2.34.

[Dmitry O Litvintsev]

Hello,

Thank you to all who responded.
There is a follow up question.
Our admin tried the following:

  A host that wad been running postgresql11 was upgraded to Alma9 (from SL7) and
  postgresql15. They then built postgresql11 on that host from sources.
  Then they run pg_upgrade from 11 to 15. It worked and psql to db is not
  accompanied by "collation version" warning.

This was unexpected to me based on my experience that I related on this thread.
Is this a legit procedure?

To remind, what did no work:

  - upgrade to 15 on SL7 host, setup stream, replication to Alma9 host. psql 
top replica
 complains about "The database was created using collation ..."

Advice is appreciated

P.S.: where I can still find postgresql11 RPMs for Alma9? Buiding from sourcres 
is OK, but a bit of a hassle.


From: Daniel Verite 
Sent: Monday, June 24, 2024 3:48 AM
To: Dmitry O Litvintsev
Cc: pgsql-generallists.postgresql.org
Subject: Re: Help. The database was created using collation version 2.17, but 
the operating system provides version 2.34.

[EXTERNAL] – This message is from an external sender

Dmitry O Litvintsev wrote:

> Just want to make clear (sorry I am slow on uptake). I should first
> REINDEX and then ALTER DATABASE xxx REFRESH COLLATION VERSION, or
> first ALTER and then REINDEX or does the order of these action
> matter at all?

The order does not matter. The ALTER DATABASE command will simply
update the pg_database.datcollversion field with the current version
of libc. That will stop the warning being issued, but it doesn't have
any other concrete effect.

Best regards,
--
Daniel Vérité
https://urldefense.proofpoint.com/v2/url?u=https-3A__postgresql.verite.pro_&d=DwIFaQ&c=gRgGjJ3BkIsb5y6s49QqsA&r=7PHi3TDlwkvpc07MjENbOxVFl0u_sEurf250JnUFWCU&m=F7VKeBFcE7ctVYy8fHvYvWPu4XkawA0hCuQOkYZk28e1uHpd_pb21GOrRMy9JB7a&s=M6qlhocjLWWgy8tVbTGTDEewC5JWHAfVztgV_XTx8Lg&e=
Twitter: @DanielVerite