Re: Is there a guide to use PostgresSQL as alternative to MariaDB/MySQL in OpenStack?

[Larry Sevilla]

thx...

But it's a different item.  fyi...

On Sat, Nov 5, 2022 at 2:50 PM Tony Shelver  wrote:

>
> On Sat, 5 Nov 2022 at 01:15, Larry Sevilla 
> wrote:
>
>> Hi,
>>
>> I'm studying OpenStack Zed using Ubuntu 22.04 with the following guides:
>>
>> https://docs.openstack.org/install-guide/
>>
>> And I have successfully installed OpenStack services and tested Horizon.
>>
>>
>> But the guide uses MariaDB/mysql.
>>
>> https://docs.openstack.org/install-guide/environment-sql-database-ubuntu.html
>>
>>
>> Is there a guide for PostgreSQL?
>>
>>
> Look at
> https://docs.openstack.org/security-guide/databases/database-backend-considerations.html.
> No experience with OpenStack
>
>

server process (PID 2964738) was terminated by signal 11: Segmentation fault

[Stefan Froehlich]

This was starting with an invalid memory alloc request size, like in
.

I followed the suggestion to trace down the faulty record, found and
fixed it. Now I can access that record again, but if I try to dump
the table I get:

| 2022-11-06 11:52:36.367 CET [2098-35] LOG:  server process (PID 2964738) was 
terminated by signal 11: Segmentation fault
| [...]
| 2022-11-06 11:53:46.229 CET [2964744-2] LOG:  database system was not 
properly shut down; automatic recovery in progress
| 2022-11-06 11:53:46.263 CET [2964744-3] LOG:  redo starts at 7D/E53B6440
| 2022-11-06 11:53:46.265 CET [2964744-4] LOG:  invalid record length at 
7D/E5412958: wanted 24, got 0
| 2022-11-06 11:53:46.265 CET [2964744-5] LOG:  redo done at 7D/E5412920
| 2022-11-06 11:53:46.749 CET [2098-39] LOG:  database system is ready to 
accept connections

Whatever was "done" does not prevent the problem from being
reproducible. I tried to track down this faulty record as well, but
binary proves difficult with 25*10^6 rows if the server crashes and
rebuilds after each try.

Is there any reasonable way to proceed from here?

The system seems to be working fine (the currupted table contains
textual information only), but right now I cannot even make proper
backups which is not a good thing.

Regards,

Stefan

Re: an difficult SQL

[Rafal Pietrak]

Hi Thiemo,

Thank you for suggestions.

W dniu 5.11.2022 o 19:05, Thiemo Kellner pisze:

Hi Rafal

You first could select the three users with the most recent entries with 
a windowing function 
(https://www.postgresql.org/docs/15/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS) 


surely I'm missing something crucial here:
select row_number() over w,* from eventlog where row_number() over w < 5 
 window w as (partition by user);

ERROR:  window functions are not allowed in WHERE

So I'm unable to pick a limited number of rows within the user 
"group-window" ranges.


Without that, I cannot proceed.

Any suggestions?

-R
PS: regarding "my sets background", yes I do "think in sets" ... as 
opposed to thinking "in functions" (like iterating procedures). I do 
prefer solutions based on set definitions.


putting it into a with query 
(https://www.postgresql.org/docs/15/sql-select.html), in following with 
queries I would select 2.1 to 2.3 with each a constant column with each 
a different value you later sort by. In a next with query you can select 
all the rest (except all 
https://www.postgresql.org/docs/15/sql-select.html#SQL-EXCEPT) the 
results of 2.1 to 2.3 for 2.4 also with the notorious sort column. In a 
last with query you can put together the partial results for 2.1 to 2.4 
with a union all 
(https://www.postgresql.org/docs/15/sql-select.html#SQL-UNION) and 
selecting sort by the sort column and the timestamp in the final select.


I do not know your background, however, sql is about data sets end it is 
not always easy to get ones head around thinking in sets. I hope you 
could follow my suggestions. It might not be the most efficient way but 
should work.


Kind regards

Thiemo


Am 05.11.22 um 16:10 schrieb Rafal Pietrak:

Hi Everybody,

I was wondering if anybody here could help me cook up a query:

1. against a list of events (like an activity log in the database). 
The list is a single table: create table events (tm timestamp, user 
int, description text).


2. of which the output would be sorted in such a way, that:
2.1 most recent event would "select" most recent events of that same 
user, and displayed in a group (of say 10) of them (in "tm" order).


2.2 going through the events back in time, first event of ANOTHER user 
selects next group, where (say 10) most recent events of that OTHER 
user is presented.


2.3 next most recent event of yet another user selects yet another 
group to display and this selection process goes on, up to a maximum 
of (say 20) users/groups-of-their-events.


2.4 after that, all other events are selected in tm order.

This is to present most recent telephone activities grouped by most 
recent subscribers so that the dashboard doesn't get cluttered with 
information but allows for an overview of other activity of most 
recent users.


I tend to think, that it's a problem for a window function ... but 
I've stumbled on the problem how to limit the window "frame" to just a 
few (say 10) events within the "window" and have all the rest returned 
as "tail" of the query.


BTW: the eventlog table is big. (and partitioned).

Any help appreciated.

-R

Re: server process (PID 2964738) was terminated by signal 11: Segmentation fault

[Tom Lane]

Stefan Froehlich  writes:
> I followed the suggestion to trace down the faulty record, found and
> fixed it. Now I can access that record again, but if I try to dump
> the table I get:
> | 2022-11-06 11:52:36.367 CET [2098-35] LOG:  server process (PID 2964738) 
> was terminated by signal 11: Segmentation fault

> Whatever was "done" does not prevent the problem from being
> reproducible. I tried to track down this faulty record as well, but
> binary proves difficult with 25*10^6 rows if the server crashes and
> rebuilds after each try.
> Is there any reasonable way to proceed from here?

contrib/amcheck might help to identify the faulty data (at this
point there's reason to fear multiple corruptions ...).  If
you're running v14 or v15 there's a frontend for that called
pg_amcheck.

regards, tom lane

Re: an difficult SQL

[Erik Wienhold]

> On 06/11/2022 13:48 CET Rafal Pietrak  wrote:
>
> W dniu 5.11.2022 o 19:05, Thiemo Kellner pisze:
> >
> > You first could select the three users with the most recent entries with
> > a windowing function
> > (https://www.postgresql.org/docs/15/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS)
>
> surely I'm missing something crucial here:
> select row_number() over w,* from eventlog where row_number() over w < 5
>   window w as (partition by user);
> ERROR:  window functions are not allowed in WHERE
>
> So I'm unable to pick a limited number of rows within the user
> "group-window" ranges.
>
> Without that, I cannot proceed.
>
> Any suggestions?

Windows functions are only permitted in SELECT and ORDER BY because they are
executed after WHERE, GROUP BY, and HAVING[1].

You need a derived table to filter on row_number:

with
ranked as (
select *, row_number() over w
from eventlog
window w as (partition by user)
)
select *
from ranked
where row_number < 5;

[1] https://www.postgresql.org/docs/15/tutorial-window.html

--
Erik

Re: server process (PID 2964738) was terminated by signal 11: Segmentation fault

[Stefan Froehlich]

On Sun, Nov 06, 2022 at 09:13:08AM -0500, Tom Lane wrote:
> > | 2022-11-06 11:52:36.367 CET [2098-35] LOG:  server process (PID 2964738) 
> > was terminated by signal 11: Segmentation fault
 
> contrib/amcheck might help to identify the faulty data (at this
> point there's reason to fear multiple corruptions ...).  If you're
> running v14 or v15 there's a frontend for that called pg_amcheck.

I am using v13, but well:

| # create extension amcheck;
| # select oid, relname from pg_class where relname ='faultytablename_pkey';
| [returns oid 537203]
| # select bt_index_check(537203, true);
| server closed the connection unexpectedly
| This probably means the server terminated abnormally
| before or while processing the request.
| The connection to the server was lost. Attempting reset: Failed.

This seems to be quite fucked up ("how" would be a good question,
too, but for the moment priority is to work around the problem).

If I have to delete 1k records in this table, I'll do and survive,
but first I have to find out which ones.

Bye,
Stefan

Re: server process (PID 2964738) was terminated by signal 11: Segmentation fault

[Tom Lane]

Stefan Froehlich  writes:
> I am using v13, but well:

> | # create extension amcheck;
> | # select oid, relname from pg_class where relname ='faultytablename_pkey';
> | [returns oid 537203]
> | # select bt_index_check(537203, true);
> | server closed the connection unexpectedly

Oh ... up through v13, amcheck lacks any functions to check heaps,
only indexes, so I guess it won't help you much.  You could try
reindexing, but if pg_dump is failing that's a good sign you have
heap corruption not (or at least not only) index corruption.

Another idea is to try using contrib/pageinspect to examine each
page of the table.  Its output is just gobbledegook to most people,
but there's a good chance it'd fail visibly on the corrupted page(s).

Also, please read
https://wiki.postgresql.org/wiki/Corruption
if you didn't already.

regards, tom lane

Re: an difficult SQL

[Rafal Pietrak]

Great, with a little tweaking (to get the remaining rows ordered 
correctly), this did the job.


Thank you Erik.

BR

-R

W dniu 6.11.2022 o 15:23, Erik Wienhold pisze:

On 06/11/2022 13:48 CET Rafal Pietrak  wrote:

W dniu 5.11.2022 o 19:05, Thiemo Kellner pisze:


You first could select the three users with the most recent entries with
a windowing function
(https://www.postgresql.org/docs/15/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS)


surely I'm missing something crucial here:
select row_number() over w,* from eventlog where row_number() over w < 5
   window w as (partition by user);
ERROR:  window functions are not allowed in WHERE

So I'm unable to pick a limited number of rows within the user
"group-window" ranges.

Without that, I cannot proceed.

Any suggestions?


Windows functions are only permitted in SELECT and ORDER BY because they are
executed after WHERE, GROUP BY, and HAVING[1].

You need a derived table to filter on row_number:

 with
 ranked as (
 select *, row_number() over w
 from eventlog
 window w as (partition by user)
 )
 select *
 from ranked
 where row_number < 5;

[1] https://www.postgresql.org/docs/15/tutorial-window.html

--
Erik

Re: Some questions about Postgres

[Siddharth Jain]

Thanks Laurenz.

On Fri, Nov 4, 2022 at 1:55 AM Laurenz Albe 
wrote:

> On Thu, 2022-11-03 at 10:42 -0700, Christophe Pettus wrote:
> > > On Nov 3, 2022, at 10:38, Siddharth Jain  wrote:
> > > I read an old article comparing MySQL to Postgres and wanted to get
> answers to following questions.
> >
> > Given your questions, I suspect that you read the technical note from
> Uber about why they switched back
> > to MySQL from PostgreSQL.  There are quite a few responses out there to
> it, including:
> >
> > https://thebuild.com/presentations/uber-perconalive-2017.pdf
>
> This is also insightful:
> https://use-the-index-luke.com/blog/2016-07-29/on-ubers-choice-of-databases
>
> Yours,
> Laurenz Albe
>

Re: AW: Reducing bandwidth usage of database replication

[Kyotaro Horiguchi]

At Fri, 4 Nov 2022 09:25:44 +, Sascha Zenglein  wrote 
in 
> I have set the status interval to 600s and it still sends a status message 
> every 30s.
> I also tried setting every available option higher, with no success:

I guess you are not setting wal_sender_timeout on the primary side.

Status messages are also sent in response to sender pings that is
controlled by that variable.  Wal sender sends a ping after a half of
that setting since the last status message to request walreceiver to
send a response.

> checkpoint_warning = 93s
> max_standby_streaming_delay = 130s
> max_standby_archive_delay = 45s
> wal_receiver_status_interval = 600s
> wal_receiver_timeout = 1200s
> wal_receiver_timeout = 65s
> recovery_min_apply_delay = 600s
> 
> The random values were to see which setting is limiting if I got above the 
> 30s limit.

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center