Unable to start postgresql-14

[Johnathan Tiamoh]

Hello,

I am able to start postgresql because of the below error. Please is there
any help

[image: User]
You
FATAL:  xlog flush request 4392/164662F8 is not satisfied --- flushed only
to 4392/F58


[image: User]YouFATAL:  xlog flush request 4392/164662F8 is not satisfied
--- flushed only to 4392/F58

Re: How to generate random bigint

[Junwang Zhao]

On Sat, Dec 23, 2023 at 8:36 AM Phillip Diffley  wrote:
>
> Thank you for all the suggestions! I ended up using pgcrypto's
> pg_random_bytes() to build the random int. I haven't fully tested the
> function yet, but it looks like this works.
>
> CREATE EXTENSION IF NOT EXISTS pgcrypto;
> CREATE OR REPLACE FUNCTION gen_random_int() RETURNS INT8 AS $$
> DECLARE
> bytes bytea;
> BEGIN
> bytes := gen_random_bytes(8);
> RETURN
> (get_byte(bytes,0)::int8 << 8*0) |
> (get_byte(bytes,1)::int8 << 8*1) |
> (get_byte(bytes,2)::int8 << 8*2) |
> (get_byte(bytes,3)::int8 << 8*3) |
> (get_byte(bytes,4)::int8 << 8*4) |
> (get_byte(bytes,5)::int8 << 8*5) |
> (get_byte(bytes,6)::int8 << 8*6) |
> (get_byte(bytes,7)::int8 << 8*7);
> END;
> $$ LANGUAGE plpgsql;
>

postgres=# explain analyze select gen_random_int() from
generate_series(1, 100);
Time: 4794.352 ms (00:04.794)
postgres=# explain analyze select ('x'||encode(gen_random_bytes(8),
'hex'))::bit(64)::int8 from generate_series(1, 100);
Time: 2816.014 ms (00:02.816)
postgres=# explain analyze select
('x'||encode(pg_read_binary_file('/dev/urandom', 0, 8),
'hex'))::bit(64)::bigint from generate_series(1, 100);
Time: 18947.639 ms (00:18.948)
postgres=# explain analyze select (random() * 2147483648)::int8 *
4294967296 + (random() * 4294967296)::int8 from generate_series(1,
100);
Time: 728.368 ms

Peter's way has the best performance.

>
> On Thu, Dec 21, 2023 at 6:14 AM Peter J. Holzer  wrote:
> >
> > On 2023-12-21 00:06:39 -0600, Phillip Diffley wrote:
> > > Postgres's random() function generates a random double. That can be 
> > > converted
> > > to a random int for smaller integers, but a double can't represent all of 
> > > the
> > > values in a bigint. Is there a recommended way to generate a random 
> > > bigint in
> > > Postgres?
> >
> > Call random() twice and add the results?
> >
> > Like this:
> >
> > select (random() * 2147483648)::int8 * 4294967296
> >+ (random() * 4294967296)::int8;
> >
> > (This assumes that random() actually returns at least 32 random bits.
> > If that's not the case you'll need more calls to random())
> >
> > hp
> >
> > --
> >_  | Peter J. Holzer| Story must make more sense than reality.
> > |_|_) ||
> > | |   | h...@hjp.at |-- Charles Stross, "Creative writing
> > __/   | http://www.hjp.at/ |   challenge!"
>
>


-- 
Regards
Junwang Zhao

Re: How to generate random bigint

[Phillip Diffley]

Thank you for all the suggestions! I ended up using pgcrypto's
pg_random_bytes() to build the random int. I haven't fully tested the
function yet, but it looks like this works.

CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE OR REPLACE FUNCTION gen_random_int() RETURNS INT8 AS $$
DECLARE
bytes bytea;
BEGIN
bytes := gen_random_bytes(8);
RETURN
(get_byte(bytes,0)::int8 << 8*0) |
(get_byte(bytes,1)::int8 << 8*1) |
(get_byte(bytes,2)::int8 << 8*2) |
(get_byte(bytes,3)::int8 << 8*3) |
(get_byte(bytes,4)::int8 << 8*4) |
(get_byte(bytes,5)::int8 << 8*5) |
(get_byte(bytes,6)::int8 << 8*6) |
(get_byte(bytes,7)::int8 << 8*7);
END;
$$ LANGUAGE plpgsql;


On Thu, Dec 21, 2023 at 6:14 AM Peter J. Holzer  wrote:
>
> On 2023-12-21 00:06:39 -0600, Phillip Diffley wrote:
> > Postgres's random() function generates a random double. That can be 
> > converted
> > to a random int for smaller integers, but a double can't represent all of 
> > the
> > values in a bigint. Is there a recommended way to generate a random bigint 
> > in
> > Postgres?
>
> Call random() twice and add the results?
>
> Like this:
>
> select (random() * 2147483648)::int8 * 4294967296
>+ (random() * 4294967296)::int8;
>
> (This assumes that random() actually returns at least 32 random bits.
> If that's not the case you'll need more calls to random())
>
> hp
>
> --
>_  | Peter J. Holzer| Story must make more sense than reality.
> |_|_) ||
> | |   | h...@hjp.at |-- Charles Stross, "Creative writing
> __/   | http://www.hjp.at/ |   challenge!"

Re: Changing a schema's name with function1 calling function2

[Adrian Klaver]

On 12/22/23 15:09, Wilma Wantren wrote:

Thank you for your response!
No, this does not help me. I will try to explain better what I mean. Our 
customers use our program with a Postgres database, user and schema, which have 
the same name. The customer chooses the name. Let's say one customer calls the 
database, user and schema 'my_things'.
I want to create scripts for the customers with which they can export the 
schema and import it into another database with a new schema name, let's say 
one customer wants to import it as 'my_things1' (this schema belongs to 
database 'my_things1').
The export script calls pg_dump to export schema 'my_things'.
The import script calls pg_restore to restore schema 'my_things' in database 
'my_things1' and then calls psql to change the schema name to 'my_things1'.
Now there is function1 which its search_path set to 'my_things'. Because the 
search_path is still set to 'my_things' after renaming the schema the script 
must now call psql to change the function's search_path to 'my_things1'.
This is not just one line more in the import script.
It is a fact that I must know - if I did not know about function1's search_path 
then there would be an error in the schema after renaming the schema.
And imagine if one day a colleague of mine implements a new function which 
needs a search_path but the colleague forgets to adjust the import script then 
again there is an error in the schema after renaming the schema.
Therefore it would be great if there was a variable which I could set in a search_path (like the 
variable "$user") which denotes the function's schema and which is only evaluated when 
the function is executed, i.e. the variable would be the value of the function's search_path in the 
function's meta data. This variable, e.g. "$function_schema" would still denote the 
correct schema after renaming the schema.



There is no such variable. What you have available is what I showed before:

https://www.postgresql.org/docs/current/sql-alterfunction.html

"
configuration_parameter
value

 Add or change the assignment to be made to a configuration
parameter when the function is called. If value is DEFAULT or,
equivalently, RESET is used, the function-local setting is removed, so
that the function executes with the value present in its environment.
Use RESET ALL to clear all function-local settings. SET FROM CURRENT
saves the value of the parameter that is current when ALTER FUNCTION is

executed as the value to be applied when the function is entered.

 See SET and Chapter 20 for more information about allowed parameter

names and values.
"

The alternative is to create a migration process using scripts with 
variable substitution outside the database. I do something similar using 
Sqitch(https://sqitch.org/) and its template system:


https://sqitch.org/docs/manual/sqitch-add/

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

Re: Re: Changing a schema's name with function1 calling function2

[Wilma Wantren]

Thank you for your response! 
No, this does not help me. I will try to explain better what I mean. Our 
customers use our program with a Postgres database, user and schema, which have 
the same name. The customer chooses the name. Let's say one customer calls the 
database, user and schema 'my_things'. 
I want to create scripts for the customers with which they can export the 
schema and import it into another database with a new schema name, let's say 
one customer wants to import it as 'my_things1' (this schema belongs to 
database 'my_things1').
The export script calls pg_dump to export schema 'my_things'. 
The import script calls pg_restore to restore schema 'my_things' in database 
'my_things1' and then calls psql to change the schema name to 'my_things1'.
Now there is function1 which its search_path set to 'my_things'. Because the 
search_path is still set to 'my_things' after renaming the schema the script 
must now call psql to change the function's search_path to 'my_things1'. 
This is not just one line more in the import script.
It is a fact that I must know - if I did not know about function1's search_path 
then there would be an error in the schema after renaming the schema. 
And imagine if one day a colleague of mine implements a new function which 
needs a search_path but the colleague forgets to adjust the import script then 
again there is an error in the schema after renaming the schema. 
Therefore it would be great if there was a variable which I could set in a 
search_path (like the variable "$user") which denotes the function's schema and 
which is only evaluated when the function is executed, i.e. the variable would 
be the value of the function's search_path in the function's meta data. This 
variable, e.g. "$function_schema" would still denote the correct schema after 
renaming the schema.

--- Ursprüngliche Nachricht ---
Von: Adrian Klaver 
Datum: 21.12.2023 17:30:02
An: Wilma Wantren ,  
pgsql-general@lists.postgresql.org
Betreff: Re: Changing a schema's name with function1 calling function2

On 12/21/23 05:47, Wilma Wantren wrote:
> Now as text mail, sorry.
> I accidentally posted the following to the bugs mailing list first.
Sorry for this, now as a question here.
> The company I work for used to only use Oracle databases for their program
and now also supports PostgreSQL.
> With Postgres, we create a database, a user and a schema, which all
have the same name. The name is chosen by the customers.
> Sometimes, the customers would like to export the data and definitions
and import them under a new name.
> This works well by using the -O (no owner) option for pg_dump/pg_restore
and only exporting and importing the schema. After the import the schema
is renamed. So far so simple and easy!
> But there is one problem: we have a database function function1, which
calls another function function2. To ensure that the function call is safe,
we set a search_path for function1.
> Both functions are created in the main schema (the one that is named
by the customer), the search_path is therefore set to this schema:
> ALTER FUNCTION function1 SET SEARCH_PATH TO 
> Since the search_path of the function is not renamed when the schema
is renamed, I need to know that there is such a search_path, which I then
manually change to the new schema name.
> Would it be possible that there is a variable that designates the schema
in which a function is located? Like this, for example:
> ALTER FUNCTION function1 SET SEARCH_PATH TO $FUNCTION_SCHEMA;
> Since $FUNCTION_SCHEMA would change to denote the new schema when renaming
the schema I wouldn't have to change the search_path manually.

From

https://www.postgresql.org/docs/current/sql-alterfunction.html

"
configuration_parameter
value

 Add or change the assignment to be made to a configuration 
parameter when the function is called. If value is DEFAULT or, 
equivalently, RESET is used, the function-local setting is removed, so 
that the function executes with the value present in its environment. 
Use RESET ALL to clear all function-local settings. SET FROM CURRENT 
saves the value of the parameter that is current when ALTER FUNCTION is

executed as the value to be applied when the function is entered.

 See SET and Chapter 20 for more information about allowed parameter

names and values.
"

Not sure if that would meet your requirements.

I could see doing in a session:

SET search_path = 'main_schema';

ALTER FUNCTION function SET search_path FROM CURRENT;

> 
> Your E-Mail. Your Cloud. Your Office. eclipso Mail & Cloud. 
> https://www.eclipso.de

> 
> 
> 
> 

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




Your E-Mail. Your Cloud. Your Office. eclipso Mail & Cloud. 
https://www.eclipso.de

Re: Building Extension on Linux fails with relocation error

[Tom Lane]

"Peter J. Holzer"  writes:
> On 2023-12-22 10:31:14 -0500, Tom Lane wrote:
>> Binaek Sarkar  writes:
>>> Interestingly, the *build completes successfully when the .a file is
>>> smaller* (around 100 MB).

>> Pure luck I suspect.

> I seem to remember a 256MB limit for position independent code on x86.
> The current man-page for GCC doesn't mention such a limit, though, so I
> may be mistaken.

At least on x86, there is/was a shlib size boundary beyond which you
needed to generate different instructions with wider offset fields
in order to have position-independent code.  Thus the difference
between -fpic and -fPIC.  It's at least possible that the OP's Go
toolchain automatically uses -fpic, but I'm suspicious of that theory.
Usually you don't use either switch when building .a libraries.

regards, tom lane

Re: Building Extension on Linux fails with relocation error

[Peter J. Holzer]

On 2023-12-22 10:31:14 -0500, Tom Lane wrote:
> Binaek Sarkar  writes:
> > The issue arises with a Go module that is compiled into a .a archive file
> > using -buildmode=c-archive, leading to a *file size of approximately 393 
> > MB*.
[...]
> > Interestingly, the *build completes successfully when the .a file is
> > smaller* (around 100 MB).
> 
> Pure luck I suspect.

I seem to remember a 256MB limit for position independent code on x86.
The current man-page for GCC doesn't mention such a limit, though, so I
may be mistaken.

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: Building Extension on Linux fails with relocation error

[Tom Lane]

Binaek Sarkar  writes:
> The issue arises with a Go module that is compiled into a .a archive file
> using -buildmode=c-archive, leading to a *file size of approximately 393 MB*.

I am not sure exactly what "-buildmode=c-archive" does, but it doesn't
sound like it is doing what you need.  You need code that is built to
be dynamically relocatable, which as the error message suggests is
normally accomplished (with a C compiler) by specifying -fPIC.
You'll have to find out what is the equivalent incantation for Go;
but look for options for building shared libraries, not .a files.

> Interestingly, the *build completes successfully when the .a file is
> smaller* (around 100 MB).

Pure luck I suspect.

regards, tom lane

Re: what do you do after pg_walreset ?

[Johnathan Tiamoh]

Hello Laurenz,

Thank you very much for your reply.

On Fri, Dec 22, 2023 at 7:10 AM Laurenz Albe 
wrote:

> On Thu, 2023-12-21 at 18:53 -0500, Johnathan Tiamoh wrote:
> > Please I wish to find out what you do after running pg_walreset.
>
> You mean pg_resetwal.
>
> > I made a mistake and deleted my data directory with all its content.
> > I then restore with 1 day old backups, after that i could start
> > unless i did pg_walreset.
>
> Which means that the backup was broken.
>
> > 1, I wish to find out what to do after i have started postgresql ?
>
> pg_dumpall the cluster and restore the result to a new cluster
> to get rod of the data corruption introduced by pg_resetwal.
>
> > 2, How do I apply the wall files after the reset ?
>
> You cannot do that.
>
> > 3, How can I determine the amount of data lost ?
>
> Well, everything from after the "backup".
>
> Yours,
> Laurenz Albe
>

Re: Read write performance check

[Lok P]

As I mentioned your scenario looks generic one, but I don't have any sample
scripts/docs to share, sorry for that. Other people may suggest any sample
scripts etc if any. or you may post it on performance group, if someone has
done similar stuff in the past.

 But as per me, the performance test scripts will look like 1) row by row
insert/select in cursor loop as you were showing earlier in this thread and
another one with batch/bulk inserts. And then calling it through pgbench or
any other scheduler for creating concurrency.

On Thu, Dec 21, 2023 at 7:00 PM veem v  wrote:

> Can someone please guide me, if any standard scripting is available for
> doing such read/write performance test? Or point me to any available docs?
>
> On Wed, 20 Dec, 2023, 10:39 am veem v,  wrote:
>
>> Thank you.
>>
>> That would really be helpful if such test scripts or similar setups are
>> already available. Can someone please guide me to some docs or blogs or
>> sample scripts, on same please.
>>
>> On Wed, 20 Dec, 2023, 10:34 am Lok P,  wrote:
>>
>>> As Rob mentioned, the syntax you posted is not correct. You need to
>>> process or read a certain batch of rows like 1000 or 10k etc. Not all 100M
>>> at one shot.
>>>
>>> But again your uses case seems common one considering you want to
>>> compare the read and write performance on multiple databases with similar
>>> table structure as per your usecase. So in that case, you may want to use
>>> some test scripts which others must have already done rather reinventing
>>> the wheel.
>>>
>>>
>>> On Wed, 20 Dec, 2023, 10:19 am veem v,  wrote:
>>>
 Thank you.

 Yes, actually we are trying to compare and see what maximum TPS are we
 able to reach with both of these row by row and batch read/write test. And
 then afterwards, this figure may be compared with other databases etc with
 similar setups.

  So wanted to understand from experts here, if this approach is fine?
 Or some other approach is advisable?

 I agree to the point that , network will play a role in real world app,
 but here, we are mainly wanted to see the database capability, as network
 will always play a similar kind of role across all databases. Do you
 suggest some other approach to achieve this objective?


 On Wed, 20 Dec, 2023, 2:42 am Peter J. Holzer, 
 wrote:

> On 2023-12-20 00:44:48 +0530, veem v wrote:
> >  So at first, we need to populate the base tables with the necessary
> data (say
> > 100million rows) with required skewness using random functions to
> generate the
> > variation in the values of different data types. Then in case of row
> by row
> > write/read test , we can traverse in a cursor loop. and in case of
> batch write/
> > insert , we need to traverse in a bulk collect loop. Something like
> below and
> > then this code can be wrapped into a procedure and passed to the
> pgbench and
> > executed from there. Please correct me if I'm wrong.
>
> One important point to consider for benchmarks is that your benchmark
> has to be similar to the real application to be useful. If your real
> application runs on a different node and connects to the database over
> the network, a benchmark running within a stored procedure may not be
> very indicative of real performance.
>
> hp
>
> --
>_  | Peter J. Holzer| Story must make more sense than reality.
> |_|_) ||
> | |   | h...@hjp.at |-- Charles Stross, "Creative writing
> __/   | http://www.hjp.at/ |   challenge!"
>

Re: what do you do after pg_walreset ?

[Laurenz Albe]

On Thu, 2023-12-21 at 18:53 -0500, Johnathan Tiamoh wrote:
> Please I wish to find out what you do after running pg_walreset.

You mean pg_resetwal.

> I made a mistake and deleted my data directory with all its content.
> I then restore with 1 day old backups, after that i could start
> unless i did pg_walreset.

Which means that the backup was broken.

> 1, I wish to find out what to do after i have started postgresql ?

pg_dumpall the cluster and restore the result to a new cluster
to get rod of the data corruption introduced by pg_resetwal.

> 2, How do I apply the wall files after the reset ?

You cannot do that.

> 3, How can I determine the amount of data lost ?

Well, everything from after the "backup".

Yours,
Laurenz Albe

Building Extension on Linux fails with relocation error

[Binaek Sarkar]

I am new to PostgreSQL extension development and am currently working on
the *steampipe-postgres-fdw* project.

This is a *Foreign Data Wrapper (FDW) written in Go*, and while I have
successfully built it on Darwin systems, I am facing challenges when
building on Linux, particularly with *pgxs.mk * against
PostgreSQL 14.

The issue arises with a Go module that is compiled into a .a archive file
using -buildmode=c-archive, leading to a *file size of approximately 393 MB*.
During the build process using *pgxs.mk *, I encounter
several errors related to relocation and DWARF debug information, such as:

/usr/bin/ld: steampipe_postgres_fdw.a(go.o): relocation
R_AARCH64_ADR_PREL_PG_HI21 against symbol `type:string' which may bind
externally can not be used when making a shared object; recompile with -fPIC
/usr/bin/ld: DWARF error: line info data is bigger (0x65f0639) than the
space remaining in the section (0xe86ef9)
...

The log is for aarch64 - but I am getting similar errors for amd64 as well


Interestingly, the *build completes successfully when the .a file is
smaller* (around 100 MB).

As someone new to this area, I am a bit unclear on the following points:

   - Best practices for compiling Go code for PostgreSQL FDWs on Linux
with *pgxs.mk
   *, especially regarding *-fPIC* and managing large
   archive file sizes.
   - Understanding and resolving the relocation and DWARF errors in the
   context of PostgreSQL extension development.
   - Any known issues or special considerations for building Go-based FDWs
   for PostgreSQL 14 on Linux.

Any advice, insights, or pointers to relevant resources from the community
will be helpful.

Thank you for your time and assistance.

I am attaching a minimal piece of code to be able to reproduce - although
> the *go toolchain* is required


Best regards,
Binaek Sarkar
<>

Logical replication breaks: "unexpected duplicate for tablespace 0, relfilenode 2774069304"

[Kouber Saparev]

We are using logical replication in a quite busy environment. On the
publisher side temporary tables are created and dropped all the time (due
to some Zend Entity Framework extension "optimisation"), thus bloating
heavily the system catalogs (among others).

At some point all our logical replication subscribers / replication slots
drop, because of an error:

*"could not receive data from WAL stream: ERROR:  unexpected duplicate for
tablespace 0, relfilenode 2774069304"*

The table for this file node is not even included in any of the
publications we have. I've found a similar issue described [1] before, so I
was wondering whether this patch is applied? Our subscriber database is
PostgreSQL 16.1 and the publisher - PostgreSQL 15.4.

What quick solution would fix the replication? Repack of the table? Reload
of the database? Killing some backends?

We rely heavily on this feature in a production environment and cannot just
leave the subscriber side out of sync.

Regards,
--
Kouber Saparev

[1]
https://postgrespro.com/list/thread-id/2597009#tycpr01mb83731ade7fd7c7cf5d335bceed...@tycpr01mb8373.jpnprd01.prod.outlook.com