Re: Postgres Database Service Interruption

[Laurenz Albe]

On Tue, 2024-01-16 at 21:59 +0530, Bablu Kumar Nayak wrote:
> I am writing to inform you that our PostgreSQL database service is currently 
> down.
> We are experiencing an unexpected interruption, and we are seeking your 
> expertise
> to help us resolve this issue promptly.
> We would greatly appreciate your immediate attention to this matter. If there 
> are
> specific steps we should follow or additional information you require, please 
> let
> us know as soon as possible.
> Your assistance in resolving this issue is crucial, and we are confident in 
> your
> expertise to help us bring the PostgreSQL database back online.
>  
> Here are some details about the current situation:
> 
> 2) Starting the server
> /apps/postgresdb/pgsql/bin/pg_ctl start -D /apps/postgresdb/pgsql/data
> waiting for server to start2024-01-15 11:15:08.010 GMT [] LOG:  listening 
> on IPv4 address "0.0.0.0", port 
> LOG:  listening on IPv6 address "::", port 
> LOG:  listening on Unix socket "/tmp/.s.PGSQL."
> LOG:  database system was interrupted while in recovery at 2024-01-15 
> 10:51:44 GMT
> HINT:  This probably means that some data is corrupted and you will have to 
> use the last backup for recovery.
> FATAL:  the database system is starting up
> LOG:  database system was not properly shut down; automatic recovery in 
> progress
> LOG:  redo starts at 0/
> FATAL:  could not access status of transaction
> DETAIL:  Could not read from file "pg_xact/0001" at offset 204800: Success.
> CONTEXT:  WAL redo at 0/7A845458 for Transaction/COMMIT: 2023-12-30 
> 23:26:16.017062+00
> LOG:  startup process (PID 2731458) exited with exit code 1
> LOG:  aborting startup due to startup process failure
> LOG:  database system is shut down
>  stopped waiting
> pg_ctl: could not start server
> Examine the log output.

You are suffering from data corruption.  What did you do to get into this state?

The canonical solution is to restore your backup.

If you have no backup, and the data are important for you (which is a 
contradiction
in terms), you should hire a consultant with extensive PostgreSQL knowledge in
the hope that he or she can salvage some of your data.

Yours,
Laurenz Albe

Re: Moving to Postgresql database

[Dominique Devienne]

On Tue, Jan 16, 2024 at 10:59 PM Tom Lane  wrote:

> Dominique Devienne  writes:
> > But sure, I take your point, you can emulate statement-level (implicit)
> > rollback via an explicit SAVEPOINT, and ROLLBACK to the savepoint
> instead.
>
> > But my point remains, that something like what that extension does should
> > be an option of PostgreSQL itself, not an extension. --DD
>
> > PS: I'd also be happy to hear why it's not, or won't be, on technical
> terms.
>
> The reason it's not going to happen is that the community (or at least
> the more senior developers) still remembers what happened the last
> time we tried it.
>
> We did implement server-side auto-rollback years ago in PG 7.3,
> and it was enough of a disaster that we took it out again in 7.4.
>

Thanks Tom. That's insightful, and obviously something I didn't know.


> The problem is that now you have a switch somewhere (whether a GUC
> or something else, still a switch) that fundamentally changes the
> transactional semantics seen by applications.  Run an application
> in the wrong mode and you have a broken app.  Worse, there is an
> awful lot of client-side code that now has to cope with both
> behaviors.  We thought that would be okay ... well, it wasn't.
> It was a mess.  It would be a bigger mess now if we were to try it
> again, because there would be even more broken client code.
>

OK. That speaks against making it the default for sure.
But what if the client-code explicitly opts-in to that mode/switch?

Is the pg_statement_rollback technically wrong? Can't what it does be done
better and more efficiently if it was in the core itself? Is it a lot of
code?

Basically implicit-statement-level-rollback is the norm, AFAIK, and
PostgreSQL is the exception here.
This creates frictions for ports to PostrgeSQL, and cross-RDBMBS apps in
general.
Thus if it was at least possible to opt-in to it, that would be a great
advance IMHO.

Client backend processes are per-user-per-DB. Would such a switch be
applied to the DB?
DBs are typically tailored to specific applications, this something like
this would work.
Thus all backends accessing a DB that opted-in to
statement-implicit-rollback would use it (by default)?
Or could that be decided on a per-client-backend basis?

I know the discussion will probably stop here. It's unlikely to happen, I
get that.
I think that's a pity, especially since there's a proof of concept, which I
assume if technically valid.

Thanks, --DD

Initiate backup from routine?

[Troels Arvin]

Hello,

I would like to allow a co-worker to perform a backup of a database, 
such that the backup is saved to the database server itself. One use 
case is that (s)he would like an extra backup of a database, just before 
an application update is deployed. The co-worker doesn't have shell 
access on the DB server (so no sudo option), and we would like to allow 
this to happen without having to involve a DBA.


Is it possible to call pg_dump (or equivalent action) through a 
procedure/function?


--
Regards,
Troels Arvin

Re: Initiate backup from routine?

[David G. Johnston]

On Wednesday, January 17, 2024, Troels Arvin  wrote:
>
> Is it possible to call pg_dump (or equivalent action) through a
> procedure/function?
>

Are you able to install an untrusted language handler into the database?
They are untrusted because they can basically get shell on the  server.

David J.

Re: Initiate backup from routine?

[Troels Arvin]

Hello,

David wrote:


/  Are you able to install an untrusted language handler into the
database?/


Yes, if need be.

--
Regards,
Troels Arvin

Re: Moving to Postgresql database

[Tom Lane]

Dominique Devienne  writes:
> On Tue, Jan 16, 2024 at 10:59 PM Tom Lane  wrote:
>> The problem is that now you have a switch somewhere (whether a GUC
>> or something else, still a switch) that fundamentally changes the
>> transactional semantics seen by applications.  Run an application
>> in the wrong mode and you have a broken app.  Worse, there is an
>> awful lot of client-side code that now has to cope with both
>> behaviors.  We thought that would be okay ... well, it wasn't.
>> It was a mess.  It would be a bigger mess now if we were to try it
>> again, because there would be even more broken client code.

> OK. That speaks against making it the default for sure.
> But what if the client-code explicitly opts-in to that mode/switch?

It wasn't default in 7.3, either.

The key point here is that "the client code" isn't monolithic:
there are frequently 3 or 4 layers involved, all maintained by
different sets of people.  If any one of them chooses to flip the
switch, all of them have to cope with the results (possibly without
even having observed the change).  Like I said, it was a mess.

Perhaps we could have got away with changing this back around 1997.
By the time we tried (7.3 was released in 2002), it was already
too late because of the amount of client-side code that needed to
change and couldn't change in a timely fashion.  Twenty years
later, that situation has to be many times worse.

> Basically implicit-statement-level-rollback is the norm, AFAIK, and
> PostgreSQL is the exception here.

I'm well aware of that.  It doesn't matter.

regards, tom lane

pg_basebackup Restore problem

[Johnathan Tiamoh]

Hello,

I am trying to restore a pg_basebackup and have the following errors.

nohup: ignoring input
tar: /tnt/backup/current/7400.tar.gz: Not found in archive
tar: /tnt/backup/current/7401.tar.gz: Not found in archive
tar: /tnt/backup/current/7402.tar.gz: Not found in archive
tar: /tnt/backup/current/base.tar.gz: Not found in archive
tar: /tnt/backup/current/pg_wal.tar.gz: Not found in archive
tar: Exiting with failure status due to previous errors
PG_14_202107181/
tar: PG_14_202107181: Cannot mkdir: Permission denied
PG_14_202107181/137502/
tar: PG_14_202107181: Cannot mkdir: Permission denied
tar: PG_14_202107181/137502: Cannot mkdir: No such file or directory
PG_14_202107181/137502/3222926016
tar: PG_14_202107181: Cannot mkdir: Permission denied

The files exist in the specified directory.

Thank you
Johnathan T.

Re: pg_basebackup Restore problem

[Ron Johnson]

On Wed, Jan 17, 2024 at 11:11 AM Johnathan Tiamoh 
wrote:

> Hello,
>
> I am trying to restore a pg_basebackup and have the following errors.
>
>
You need to tell us the PG version number *and* show us the full command
you ran.


> nohup: ignoring input
> tar: /tnt/backup/current/7400.tar.gz: Not found in archive
>
tar: /tnt/backup/current/7401.tar.gz: Not found in archive
> tar: /tnt/backup/current/7402.tar.gz: Not found in archive
> tar: /tnt/backup/current/base.tar.gz: Not found in archive
> tar: /tnt/backup/current/pg_wal.tar.gz: Not found in archive
> tar: Exiting with failure status due to previous errors
> PG_14_202107181/
> tar: PG_14_202107181: Cannot mkdir: Permission denied
>

What user are you running pg_basebackup as?


> PG_14_202107181/137502/
> tar: PG_14_202107181: Cannot mkdir: Permission denied
> tar: PG_14_202107181/137502: Cannot mkdir: No such file or directory
> PG_14_202107181/137502/3222926016
> tar: PG_14_202107181: Cannot mkdir: Permission denied
>
> The files exist in the specified directory.
>

/tnt???  Or /mnt?

Re: Initiate backup from routine?

[Pyrote]

On Wednesday, January 17, 2024, Troels Arvin  wrote:
Is it possible to call pg_dump (or equivalent action) through a 
procedure/function?


You could create a new schema and table that holds a flag column or a 
simple queue.
Then setup a script/app on a 5 minute cron that checks the status of the 
flag or queue to see if it needs to start a backup.
The script/app could update the table to show the user that the backup 
has started and when its finished.
The user account on the DB server that runs the code would have their 
shell set to /bin/false so they cannot login. The DB account used by the 
code would have the minimum privileges to perform the backup.


Or if it needs to be more real-time, the app could use LISTEN/NOTIFY to 
trigger the backup process. Then use cron on some interval to make sure 
the app is always running.


Then you just need the procedure to update the flag/queue or send a 
NOTIFY message.

Re: Initiate backup from routine?

[Ron Johnson]

On Wed, Jan 17, 2024 at 9:41 AM Troels Arvin  wrote:

> Hello,
>
> I would like to allow a co-worker to perform a backup of a database, such
> that the backup is saved to the database server itself. One use case is
> that (s)he would like an extra backup of a database, just before an
> application update is deployed. The co-worker doesn't have shell access on
> the DB server (so no sudo option), and we would like to allow this to
> happen without having to involve a DBA.
>
> Is it possible to call pg_dump (or equivalent action) through a
> procedure/function?
>
An alternative is continuous (aka PITR) backups using something like
PgBackRest.  Weekly full backups, incremental backups on the other six
days, and WAL files that keep you up to date.

CHECKPOINT; and SELECT pg_switch_wal(); are all that's needed before she
deploys the update.

Re: pg_basebackup Restore problem

[Johnathan Tiamoh]

You need to tell us the PG version number *and* show us the full command
you ran. ?

Postgresql Version 14.10

tar -h  -zxvf   $PATH_FOLDER/*  .tar.gz

ls $PATH_FOLDER/*.tar.gz | xargs -I  {} tar -h  -zxvf  {}

What user are you running pg_basebackup as?

I ran it as postgres and now I'm restoring as postgres

/tnt???  Or /mnt?
/tnt/backup/current

This is where the backup files are.





On Wed, Jan 17, 2024 at 11:15 AM Ron Johnson 
wrote:

> On Wed, Jan 17, 2024 at 11:11 AM Johnathan Tiamoh <
> johnathantia...@gmail.com> wrote:
>
>> Hello,
>>
>> I am trying to restore a pg_basebackup and have the following errors.
>>
>>
> You need to tell us the PG version number *and* show us the full command
> you ran.
>
>
>> nohup: ignoring input
>> tar: /tnt/backup/current/7400.tar.gz: Not found in archive
>>
> tar: /tnt/backup/current/7401.tar.gz: Not found in archive
>> tar: /tnt/backup/current/7402.tar.gz: Not found in archive
>> tar: /tnt/backup/current/base.tar.gz: Not found in archive
>> tar: /tnt/backup/current/pg_wal.tar.gz: Not found in archive
>> tar: Exiting with failure status due to previous errors
>> PG_14_202107181/
>> tar: PG_14_202107181: Cannot mkdir: Permission denied
>>
>
> What user are you running pg_basebackup as?
>
>
>> PG_14_202107181/137502/
>> tar: PG_14_202107181: Cannot mkdir: Permission denied
>> tar: PG_14_202107181/137502: Cannot mkdir: No such file or directory
>> PG_14_202107181/137502/3222926016
>> tar: PG_14_202107181: Cannot mkdir: Permission denied
>>
>> The files exist in the specified directory.
>>
>
> /tnt???  Or /mnt?
>
>

Re: pg_basebackup Restore problem

[Ron Johnson]

1. What's in $PATH_FOLDER?
2. What pg_basebackup command did you use?
3. Why aren't you letting pg_basebackup maintain the WAL files it needs?

On Wed, Jan 17, 2024 at 11:27 AM Johnathan Tiamoh 
wrote:

> You need to tell us the PG version number *and* show us the full command
> you ran. ?
>
> Postgresql Version 14.10
>
> tar -h  -zxvf   $PATH_FOLDER/*  .tar.gz
>
> ls $PATH_FOLDER/*.tar.gz | xargs -I  {} tar -h  -zxvf  {}
>
> What user are you running pg_basebackup as?
>
> I ran it as postgres and now I'm restoring as postgres
>
> /tnt???  Or /mnt?
> /tnt/backup/current
>
> This is where the backup files are.
>
>
>
>
>
> On Wed, Jan 17, 2024 at 11:15 AM Ron Johnson 
> wrote:
>
>> On Wed, Jan 17, 2024 at 11:11 AM Johnathan Tiamoh <
>> johnathantia...@gmail.com> wrote:
>>
>>> Hello,
>>>
>>> I am trying to restore a pg_basebackup and have the following errors.
>>>
>>>
>> You need to tell us the PG version number *and* show us the full command
>> you ran.
>>
>>
>>> nohup: ignoring input
>>> tar: /tnt/backup/current/7400.tar.gz: Not found in archive
>>>
>> tar: /tnt/backup/current/7401.tar.gz: Not found in archive
>>> tar: /tnt/backup/current/7402.tar.gz: Not found in archive
>>> tar: /tnt/backup/current/base.tar.gz: Not found in archive
>>> tar: /tnt/backup/current/pg_wal.tar.gz: Not found in archive
>>> tar: Exiting with failure status due to previous errors
>>> PG_14_202107181/
>>> tar: PG_14_202107181: Cannot mkdir: Permission denied
>>>
>>
>> What user are you running pg_basebackup as?
>>
>>
>>> PG_14_202107181/137502/
>>> tar: PG_14_202107181: Cannot mkdir: Permission denied
>>> tar: PG_14_202107181/137502: Cannot mkdir: No such file or directory
>>> PG_14_202107181/137502/3222926016
>>> tar: PG_14_202107181: Cannot mkdir: Permission denied
>>>
>>> The files exist in the specified directory.
>>>
>>
>> /tnt???  Or /mnt?
>>
>>

RE: Initiate backup from routine?

[Kamil ADEM]

Hello Troels,

You can use the following statements in a procedure/function:
CREATE TEMPORARY TABLE tt_cmdout(cmdoutput text);
COPY tt_cmdout(cmdoutput) FROM PROGRAM 'pg_dump.exe ';

But note that the user must have SUPERUSER or BYPASSRLS privileges or must be 
table owner.

Ragards,
Kamil Adem


From: Troels Arvin 
Sent: Wednesday, January 17, 2024 5:41 PM
To: pgsql-general@lists.postgresql.org
Subject: Initiate backup from routine?


Hello,

I would like to allow a co-worker to perform a backup of a database, such that 
the backup is saved to the database server itself. One use case is that (s)he 
would like an extra backup of a database, just before an application update is 
deployed. The co-worker doesn't have shell access on the DB server (so no sudo 
option), and we would like to allow this to happen without having to involve a 
DBA.

Is it possible to call pg_dump (or equivalent action) through a 
procedure/function?

--
Regards,
Troels Arvin

unbale to list schema

[Atul Kumar]

Hi,

I am not able to find any solution to list all schemas in all databases at
once, to check the structure of the whole cluster.

As I need to give a few privileges to a user to all databases, their
schemas and schemas' objects (tables sequences etc.).

Please let me know if there is any solution/ query that will serve the
purpose.


Regards.

Re: pg_basebackup Restore problem

[Johnathan Tiamoh]

1. What's in $PATH_FOLDER?

/tnt/backup/current

2. What pg_basebackup command did you use?

pg_basebackup -D "$baseback_dir" --format=tar   \
"${comp_opts[@]}" --wal-method=stream --no-password
--verbose "${PG_DUMP_OPTS[@]}"

/bin/mv "$baseback_dir"/* "/enf/backup/current/"
/bin/rm -r "$baseback_dir"


3. Why aren't you letting pg_basebackup maintain the WAL files it needs?


On Wed, Jan 17, 2024 at 11:46 AM Ron Johnson 
wrote:

> 1. What's in $PATH_FOLDER?
> 2. What pg_basebackup command did you use?
> 3. Why aren't you letting pg_basebackup maintain the WAL files it needs?
>
> On Wed, Jan 17, 2024 at 11:27 AM Johnathan Tiamoh <
> johnathantia...@gmail.com> wrote:
>
>> You need to tell us the PG version number *and* show us the full command
>> you ran. ?
>>
>> Postgresql Version 14.10
>>
>> tar -h  -zxvf   $PATH_FOLDER/*  .tar.gz
>>
>> ls $PATH_FOLDER/*.tar.gz | xargs -I  {} tar -h  -zxvf  {}
>>
>> What user are you running pg_basebackup as?
>>
>> I ran it as postgres and now I'm restoring as postgres
>>
>> /tnt???  Or /mnt?
>> /tnt/backup/current
>>
>> This is where the backup files are.
>>
>>
>>
>>
>>
>> On Wed, Jan 17, 2024 at 11:15 AM Ron Johnson 
>> wrote:
>>
>>> On Wed, Jan 17, 2024 at 11:11 AM Johnathan Tiamoh <
>>> johnathantia...@gmail.com> wrote:
>>>
 Hello,

 I am trying to restore a pg_basebackup and have the following errors.


>>> You need to tell us the PG version number *and* show us the full
>>> command you ran.
>>>
>>>
 nohup: ignoring input
 tar: /tnt/backup/current/7400.tar.gz: Not found in archive

>>> tar: /tnt/backup/current/7401.tar.gz: Not found in archive
 tar: /tnt/backup/current/7402.tar.gz: Not found in archive
 tar: /tnt/backup/current/base.tar.gz: Not found in archive
 tar: /tnt/backup/current/pg_wal.tar.gz: Not found in archive
 tar: Exiting with failure status due to previous errors
 PG_14_202107181/
 tar: PG_14_202107181: Cannot mkdir: Permission denied

>>>
>>> What user are you running pg_basebackup as?
>>>
>>>
 PG_14_202107181/137502/
 tar: PG_14_202107181: Cannot mkdir: Permission denied
 tar: PG_14_202107181/137502: Cannot mkdir: No such file or directory
 PG_14_202107181/137502/3222926016
 tar: PG_14_202107181: Cannot mkdir: Permission denied

 The files exist in the specified directory.

>>>
>>> /tnt???  Or /mnt?
>>>
>>>

Re: unbale to list schema

[Erik Wienhold]

On 2024-01-17 19:46 +0100, Atul Kumar wrote:
> I am not able to find any solution to list all schemas in all databases at
> once, to check the structure of the whole cluster.

You have to connect to each database and run the necessary statements
per database.

> As I need to give a few privileges to a user to all databases, their
> schemas and schemas' objects (tables sequences etc.).
> 
> Please let me know if there is any solution/ query that will serve the
> purpose.

What is your environment?  You can do that with psql and shell.

For example, in Bash I use the following pattern: select the relevant
database names and loop over the psql output to connect to each
database to run the relevant statements (\dn in this case to list all
schemas).

psql postgres --no-psqlrc -A -t -c "select datname from pg_database 
where datname <> 'template0'" |
while IFS= read -r dbname
do
psql -d "$dbname" -c '\dn'
done

-- 
Erik

Re: pg_basebackup Restore problem

[Ron Johnson]

Wait a minute... *tar* is throwing the errors, not pg_basebackup, no?

On Wed, Jan 17, 2024 at 3:27 PM Johnathan Tiamoh 
wrote:

> 1. What's in $PATH_FOLDER?
>
> /tnt/backup/current
>
> 2. What pg_basebackup command did you use?
>
> pg_basebackup -D "$baseback_dir" --format=tar   \
> "${comp_opts[@]}" --wal-method=stream --no-password
> --verbose "${PG_DUMP_OPTS[@]}"
>
> /bin/mv "$baseback_dir"/* "/enf/backup/current/"
> /bin/rm -r "$baseback_dir"
>
>
> 3. Why aren't you letting pg_basebackup maintain the WAL files it needs?
>
>
> On Wed, Jan 17, 2024 at 11:46 AM Ron Johnson 
> wrote:
>
>> 1. What's in $PATH_FOLDER?
>> 2. What pg_basebackup command did you use?
>> 3. Why aren't you letting pg_basebackup maintain the WAL files it needs?
>>
>> On Wed, Jan 17, 2024 at 11:27 AM Johnathan Tiamoh <
>> johnathantia...@gmail.com> wrote:
>>
>>> You need to tell us the PG version number *and* show us the full
>>> command you ran. ?
>>>
>>> Postgresql Version 14.10
>>>
>>> tar -h  -zxvf   $PATH_FOLDER/*  .tar.gz
>>>
>>> ls $PATH_FOLDER/*.tar.gz | xargs -I  {} tar -h  -zxvf  {}
>>>
>>> What user are you running pg_basebackup as?
>>>
>>> I ran it as postgres and now I'm restoring as postgres
>>>
>>> /tnt???  Or /mnt?
>>> /tnt/backup/current
>>>
>>> This is where the backup files are.
>>>
>>>
>>>
>>>
>>>
>>> On Wed, Jan 17, 2024 at 11:15 AM Ron Johnson 
>>> wrote:
>>>
 On Wed, Jan 17, 2024 at 11:11 AM Johnathan Tiamoh <
 johnathantia...@gmail.com> wrote:

> Hello,
>
> I am trying to restore a pg_basebackup and have the following errors.
>
>
 You need to tell us the PG version number *and* show us the full
 command you ran.


> nohup: ignoring input
> tar: /tnt/backup/current/7400.tar.gz: Not found in archive
>
 tar: /tnt/backup/current/7401.tar.gz: Not found in archive
> tar: /tnt/backup/current/7402.tar.gz: Not found in archive
> tar: /tnt/backup/current/base.tar.gz: Not found in archive
> tar: /tnt/backup/current/pg_wal.tar.gz: Not found in archive
> tar: Exiting with failure status due to previous errors
> PG_14_202107181/
> tar: PG_14_202107181: Cannot mkdir: Permission denied
>

 What user are you running pg_basebackup as?


> PG_14_202107181/137502/
> tar: PG_14_202107181: Cannot mkdir: Permission denied
> tar: PG_14_202107181/137502: Cannot mkdir: No such file or directory
> PG_14_202107181/137502/3222926016
> tar: PG_14_202107181: Cannot mkdir: Permission denied
>
> The files exist in the specified directory.
>

 /tnt???  Or /mnt?

Re: pg_basebackup Restore problem

[Johnathan Tiamoh]

Yes.

I am trying to restore the backups on a standby

On Wed, Jan 17, 2024 at 4:04 PM Ron Johnson  wrote:

> Wait a minute... *tar* is throwing the errors, not pg_basebackup, no?
>
> On Wed, Jan 17, 2024 at 3:27 PM Johnathan Tiamoh <
> johnathantia...@gmail.com> wrote:
>
>> 1. What's in $PATH_FOLDER?
>>
>> /tnt/backup/current
>>
>> 2. What pg_basebackup command did you use?
>>
>> pg_basebackup -D "$baseback_dir" --format=tar   \
>> "${comp_opts[@]}" --wal-method=stream --no-password
>> --verbose "${PG_DUMP_OPTS[@]}"
>>
>> /bin/mv "$baseback_dir"/* "/enf/backup/current/"
>> /bin/rm -r "$baseback_dir"
>>
>>
>> 3. Why aren't you letting pg_basebackup maintain the WAL files it needs?
>>
>>
>> On Wed, Jan 17, 2024 at 11:46 AM Ron Johnson 
>> wrote:
>>
>>> 1. What's in $PATH_FOLDER?
>>> 2. What pg_basebackup command did you use?
>>> 3. Why aren't you letting pg_basebackup maintain the WAL files it needs?
>>>
>>> On Wed, Jan 17, 2024 at 11:27 AM Johnathan Tiamoh <
>>> johnathantia...@gmail.com> wrote:
>>>
 You need to tell us the PG version number *and* show us the full
 command you ran. ?

 Postgresql Version 14.10

 tar -h  -zxvf   $PATH_FOLDER/*  .tar.gz

 ls $PATH_FOLDER/*.tar.gz | xargs -I  {} tar -h  -zxvf  {}

 What user are you running pg_basebackup as?

 I ran it as postgres and now I'm restoring as postgres

 /tnt???  Or /mnt?
 /tnt/backup/current

 This is where the backup files are.





 On Wed, Jan 17, 2024 at 11:15 AM Ron Johnson 
 wrote:

> On Wed, Jan 17, 2024 at 11:11 AM Johnathan Tiamoh <
> johnathantia...@gmail.com> wrote:
>
>> Hello,
>>
>> I am trying to restore a pg_basebackup and have the following errors.
>>
>>
> You need to tell us the PG version number *and* show us the full
> command you ran.
>
>
>> nohup: ignoring input
>> tar: /tnt/backup/current/7400.tar.gz: Not found in archive
>>
> tar: /tnt/backup/current/7401.tar.gz: Not found in archive
>> tar: /tnt/backup/current/7402.tar.gz: Not found in archive
>> tar: /tnt/backup/current/base.tar.gz: Not found in archive
>> tar: /tnt/backup/current/pg_wal.tar.gz: Not found in archive
>> tar: Exiting with failure status due to previous errors
>> PG_14_202107181/
>> tar: PG_14_202107181: Cannot mkdir: Permission denied
>>
>
> What user are you running pg_basebackup as?
>
>
>> PG_14_202107181/137502/
>> tar: PG_14_202107181: Cannot mkdir: Permission denied
>> tar: PG_14_202107181/137502: Cannot mkdir: No such file or directory
>> PG_14_202107181/137502/3222926016
>> tar: PG_14_202107181: Cannot mkdir: Permission denied
>>
>> The files exist in the specified directory.
>>
>
> /tnt???  Or /mnt?
>
>

Re: pg_basebackup Restore problem

[Ron Johnson]

Then you've got a bug somewhere in:
tar -h  -zxvf   $PATH_FOLDER/*  .tar.gz
ls $PATH_FOLDER/*.tar.gz | xargs -I  {} tar -h  -zxvf  {}

On Wed, Jan 17, 2024 at 4:07 PM Johnathan Tiamoh 
wrote:

> Yes.
>
> I am trying to restore the backups on a standby
>
> On Wed, Jan 17, 2024 at 4:04 PM Ron Johnson 
> wrote:
>
>> Wait a minute... *tar* is throwing the errors, not pg_basebackup, no?
>>
>> On Wed, Jan 17, 2024 at 3:27 PM Johnathan Tiamoh <
>> johnathantia...@gmail.com> wrote:
>>
>>> 1. What's in $PATH_FOLDER?
>>>
>>> /tnt/backup/current
>>>
>>> 2. What pg_basebackup command did you use?
>>>
>>> pg_basebackup -D "$baseback_dir" --format=tar   \
>>> "${comp_opts[@]}" --wal-method=stream --no-password
>>> --verbose "${PG_DUMP_OPTS[@]}"
>>>
>>> /bin/mv "$baseback_dir"/* "/enf/backup/current/"
>>> /bin/rm -r "$baseback_dir"
>>>
>>>
>>> 3. Why aren't you letting pg_basebackup maintain the WAL files it needs?
>>>
>>>
>>> On Wed, Jan 17, 2024 at 11:46 AM Ron Johnson 
>>> wrote:
>>>
 1. What's in $PATH_FOLDER?
 2. What pg_basebackup command did you use?
 3. Why aren't you letting pg_basebackup maintain the WAL files it needs?

 On Wed, Jan 17, 2024 at 11:27 AM Johnathan Tiamoh <
 johnathantia...@gmail.com> wrote:

> You need to tell us the PG version number *and* show us the full
> command you ran. ?
>
> Postgresql Version 14.10
>
> tar -h  -zxvf   $PATH_FOLDER/*  .tar.gz
>
> ls $PATH_FOLDER/*.tar.gz | xargs -I  {} tar -h  -zxvf  {}
>
> What user are you running pg_basebackup as?
>
> I ran it as postgres and now I'm restoring as postgres
>
> /tnt???  Or /mnt?
> /tnt/backup/current
>
> This is where the backup files are.
>
>
>
>
>
> On Wed, Jan 17, 2024 at 11:15 AM Ron Johnson 
> wrote:
>
>> On Wed, Jan 17, 2024 at 11:11 AM Johnathan Tiamoh <
>> johnathantia...@gmail.com> wrote:
>>
>>> Hello,
>>>
>>> I am trying to restore a pg_basebackup and have the following errors.
>>>
>>>
>> You need to tell us the PG version number *and* show us the full
>> command you ran.
>>
>>
>>> nohup: ignoring input
>>> tar: /tnt/backup/current/7400.tar.gz: Not found in archive
>>>
>> tar: /tnt/backup/current/7401.tar.gz: Not found in archive
>>> tar: /tnt/backup/current/7402.tar.gz: Not found in archive
>>> tar: /tnt/backup/current/base.tar.gz: Not found in archive
>>> tar: /tnt/backup/current/pg_wal.tar.gz: Not found in archive
>>> tar: Exiting with failure status due to previous errors
>>> PG_14_202107181/
>>> tar: PG_14_202107181: Cannot mkdir: Permission denied
>>>
>>
>> What user are you running pg_basebackup as?
>>
>>
>>> PG_14_202107181/137502/
>>> tar: PG_14_202107181: Cannot mkdir: Permission denied
>>> tar: PG_14_202107181/137502: Cannot mkdir: No such file or directory
>>> PG_14_202107181/137502/3222926016
>>> tar: PG_14_202107181: Cannot mkdir: Permission denied
>>>
>>> The files exist in the specified directory.
>>>
>>
>> /tnt???  Or /mnt?
>>
>>

Re: pg_basebackup Restore problem

[Johnathan Tiamoh]

Ok.

I'm a little confused because has always work

On Wed, Jan 17, 2024 at 4:11 PM Ron Johnson  wrote:

> Then you've got a bug somewhere in:
> tar -h  -zxvf   $PATH_FOLDER/*  .tar.gz
> ls $PATH_FOLDER/*.tar.gz | xargs -I  {} tar -h  -zxvf  {}
>
> On Wed, Jan 17, 2024 at 4:07 PM Johnathan Tiamoh <
> johnathantia...@gmail.com> wrote:
>
>> Yes.
>>
>> I am trying to restore the backups on a standby
>>
>> On Wed, Jan 17, 2024 at 4:04 PM Ron Johnson 
>> wrote:
>>
>>> Wait a minute... *tar* is throwing the errors, not pg_basebackup, no?
>>>
>>> On Wed, Jan 17, 2024 at 3:27 PM Johnathan Tiamoh <
>>> johnathantia...@gmail.com> wrote:
>>>
 1. What's in $PATH_FOLDER?

 /tnt/backup/current

 2. What pg_basebackup command did you use?

 pg_basebackup -D "$baseback_dir" --format=tar   \
 "${comp_opts[@]}" --wal-method=stream --no-password
 --verbose "${PG_DUMP_OPTS[@]}"

 /bin/mv "$baseback_dir"/* "/enf/backup/current/"
 /bin/rm -r "$baseback_dir"


 3. Why aren't you letting pg_basebackup maintain the WAL files it needs?


 On Wed, Jan 17, 2024 at 11:46 AM Ron Johnson 
 wrote:

> 1. What's in $PATH_FOLDER?
> 2. What pg_basebackup command did you use?
> 3. Why aren't you letting pg_basebackup maintain the WAL files it
> needs?
>
> On Wed, Jan 17, 2024 at 11:27 AM Johnathan Tiamoh <
> johnathantia...@gmail.com> wrote:
>
>> You need to tell us the PG version number *and* show us the full
>> command you ran. ?
>>
>> Postgresql Version 14.10
>>
>> tar -h  -zxvf   $PATH_FOLDER/*  .tar.gz
>>
>> ls $PATH_FOLDER/*.tar.gz | xargs -I  {} tar -h  -zxvf  {}
>>
>> What user are you running pg_basebackup as?
>>
>> I ran it as postgres and now I'm restoring as postgres
>>
>> /tnt???  Or /mnt?
>> /tnt/backup/current
>>
>> This is where the backup files are.
>>
>>
>>
>>
>>
>> On Wed, Jan 17, 2024 at 11:15 AM Ron Johnson 
>> wrote:
>>
>>> On Wed, Jan 17, 2024 at 11:11 AM Johnathan Tiamoh <
>>> johnathantia...@gmail.com> wrote:
>>>
 Hello,

 I am trying to restore a pg_basebackup and have the following
 errors.


>>> You need to tell us the PG version number *and* show us the full
>>> command you ran.
>>>
>>>
 nohup: ignoring input
 tar: /tnt/backup/current/7400.tar.gz: Not found in archive

>>> tar: /tnt/backup/current/7401.tar.gz: Not found in archive
 tar: /tnt/backup/current/7402.tar.gz: Not found in archive
 tar: /tnt/backup/current/base.tar.gz: Not found in archive
 tar: /tnt/backup/current/pg_wal.tar.gz: Not found in archive
 tar: Exiting with failure status due to previous errors
 PG_14_202107181/
 tar: PG_14_202107181: Cannot mkdir: Permission denied

>>>
>>> What user are you running pg_basebackup as?
>>>
>>>
 PG_14_202107181/137502/
 tar: PG_14_202107181: Cannot mkdir: Permission denied
 tar: PG_14_202107181/137502: Cannot mkdir: No such file or directory
 PG_14_202107181/137502/3222926016
 tar: PG_14_202107181: Cannot mkdir: Permission denied

 The files exist in the specified directory.

>>>
>>> /tnt???  Or /mnt?
>>>
>>>

Re: pg_basebackup Restore problem

[Ron Johnson]

Perfectly understandable, but tar *did* fail.  Time to start debugging your
shell script.

On Wed, Jan 17, 2024 at 4:26 PM Johnathan Tiamoh 
wrote:

> Ok.
>
> I'm a little confused because has always work
>
> On Wed, Jan 17, 2024 at 4:11 PM Ron Johnson 
> wrote:
>
>> Then you've got a bug somewhere in:
>> tar -h  -zxvf   $PATH_FOLDER/*  .tar.gz
>> ls $PATH_FOLDER/*.tar.gz | xargs -I  {} tar -h  -zxvf  {}
>>
>> On Wed, Jan 17, 2024 at 4:07 PM Johnathan Tiamoh <
>> johnathantia...@gmail.com> wrote:
>>
>>> Yes.
>>>
>>> I am trying to restore the backups on a standby
>>>
>>> On Wed, Jan 17, 2024 at 4:04 PM Ron Johnson 
>>> wrote:
>>>
 Wait a minute... *tar* is throwing the errors, not pg_basebackup, no?

 On Wed, Jan 17, 2024 at 3:27 PM Johnathan Tiamoh <
 johnathantia...@gmail.com> wrote:

> 1. What's in $PATH_FOLDER?
>
> /tnt/backup/current
>
> 2. What pg_basebackup command did you use?
>
> pg_basebackup -D "$baseback_dir" --format=tar   \
> "${comp_opts[@]}" --wal-method=stream --no-password
> --verbose "${PG_DUMP_OPTS[@]}"
>
> /bin/mv "$baseback_dir"/* "/enf/backup/current/"
> /bin/rm -r "$baseback_dir"
>
>
> 3. Why aren't you letting pg_basebackup maintain the WAL files it
> needs?
>
>
> On Wed, Jan 17, 2024 at 11:46 AM Ron Johnson 
> wrote:
>
>> 1. What's in $PATH_FOLDER?
>> 2. What pg_basebackup command did you use?
>> 3. Why aren't you letting pg_basebackup maintain the WAL files it
>> needs?
>>
>> On Wed, Jan 17, 2024 at 11:27 AM Johnathan Tiamoh <
>> johnathantia...@gmail.com> wrote:
>>
>>> You need to tell us the PG version number *and* show us the full
>>> command you ran. ?
>>>
>>> Postgresql Version 14.10
>>>
>>> tar -h  -zxvf   $PATH_FOLDER/*  .tar.gz
>>>
>>> ls $PATH_FOLDER/*.tar.gz | xargs -I  {} tar -h  -zxvf  {}
>>>
>>> What user are you running pg_basebackup as?
>>>
>>> I ran it as postgres and now I'm restoring as postgres
>>>
>>> /tnt???  Or /mnt?
>>> /tnt/backup/current
>>>
>>> This is where the backup files are.
>>>
>>>
>>>
>>>
>>>
>>> On Wed, Jan 17, 2024 at 11:15 AM Ron Johnson <
>>> ronljohnso...@gmail.com> wrote:
>>>
 On Wed, Jan 17, 2024 at 11:11 AM Johnathan Tiamoh <
 johnathantia...@gmail.com> wrote:

> Hello,
>
> I am trying to restore a pg_basebackup and have the following
> errors.
>
>
 You need to tell us the PG version number *and* show us the full
 command you ran.


> nohup: ignoring input
> tar: /tnt/backup/current/7400.tar.gz: Not found in archive
>
 tar: /tnt/backup/current/7401.tar.gz: Not found in archive
> tar: /tnt/backup/current/7402.tar.gz: Not found in archive
> tar: /tnt/backup/current/base.tar.gz: Not found in archive
> tar: /tnt/backup/current/pg_wal.tar.gz: Not found in archive
> tar: Exiting with failure status due to previous errors
> PG_14_202107181/
> tar: PG_14_202107181: Cannot mkdir: Permission denied
>

 What user are you running pg_basebackup as?


> PG_14_202107181/137502/
> tar: PG_14_202107181: Cannot mkdir: Permission denied
> tar: PG_14_202107181/137502: Cannot mkdir: No such file or
> directory
> PG_14_202107181/137502/3222926016
> tar: PG_14_202107181: Cannot mkdir: Permission denied
>
> The files exist in the specified directory.
>

 /tnt???  Or /mnt?

Re: pg_basebackup Restore problem

[Johnathan Tiamoh]

Thank You very much for your time.

On Wed, Jan 17, 2024 at 4:51 PM Ron Johnson  wrote:

> Perfectly understandable, but tar *did* fail.  Time to start debugging
> your shell script.
>
> On Wed, Jan 17, 2024 at 4:26 PM Johnathan Tiamoh <
> johnathantia...@gmail.com> wrote:
>
>> Ok.
>>
>> I'm a little confused because has always work
>>
>> On Wed, Jan 17, 2024 at 4:11 PM Ron Johnson 
>> wrote:
>>
>>> Then you've got a bug somewhere in:
>>> tar -h  -zxvf   $PATH_FOLDER/*  .tar.gz
>>> ls $PATH_FOLDER/*.tar.gz | xargs -I  {} tar -h  -zxvf  {}
>>>
>>> On Wed, Jan 17, 2024 at 4:07 PM Johnathan Tiamoh <
>>> johnathantia...@gmail.com> wrote:
>>>
 Yes.

 I am trying to restore the backups on a standby

 On Wed, Jan 17, 2024 at 4:04 PM Ron Johnson 
 wrote:

> Wait a minute... *tar* is throwing the errors, not pg_basebackup, no?
>
> On Wed, Jan 17, 2024 at 3:27 PM Johnathan Tiamoh <
> johnathantia...@gmail.com> wrote:
>
>> 1. What's in $PATH_FOLDER?
>>
>> /tnt/backup/current
>>
>> 2. What pg_basebackup command did you use?
>>
>> pg_basebackup -D "$baseback_dir" --format=tar   \
>> "${comp_opts[@]}" --wal-method=stream --no-password
>> --verbose "${PG_DUMP_OPTS[@]}"
>>
>> /bin/mv "$baseback_dir"/* "/enf/backup/current/"
>> /bin/rm -r "$baseback_dir"
>>
>>
>> 3. Why aren't you letting pg_basebackup maintain the WAL files it
>> needs?
>>
>>
>> On Wed, Jan 17, 2024 at 11:46 AM Ron Johnson 
>> wrote:
>>
>>> 1. What's in $PATH_FOLDER?
>>> 2. What pg_basebackup command did you use?
>>> 3. Why aren't you letting pg_basebackup maintain the WAL files it
>>> needs?
>>>
>>> On Wed, Jan 17, 2024 at 11:27 AM Johnathan Tiamoh <
>>> johnathantia...@gmail.com> wrote:
>>>
 You need to tell us the PG version number *and* show us the full
 command you ran. ?

 Postgresql Version 14.10

 tar -h  -zxvf   $PATH_FOLDER/*  .tar.gz

 ls $PATH_FOLDER/*.tar.gz | xargs -I  {} tar -h  -zxvf  {}

 What user are you running pg_basebackup as?

 I ran it as postgres and now I'm restoring as postgres

 /tnt???  Or /mnt?
 /tnt/backup/current

 This is where the backup files are.





 On Wed, Jan 17, 2024 at 11:15 AM Ron Johnson <
 ronljohnso...@gmail.com> wrote:

> On Wed, Jan 17, 2024 at 11:11 AM Johnathan Tiamoh <
> johnathantia...@gmail.com> wrote:
>
>> Hello,
>>
>> I am trying to restore a pg_basebackup and have the following
>> errors.
>>
>>
> You need to tell us the PG version number *and* show us the full
> command you ran.
>
>
>> nohup: ignoring input
>> tar: /tnt/backup/current/7400.tar.gz: Not found in archive
>>
> tar: /tnt/backup/current/7401.tar.gz: Not found in archive
>> tar: /tnt/backup/current/7402.tar.gz: Not found in archive
>> tar: /tnt/backup/current/base.tar.gz: Not found in archive
>> tar: /tnt/backup/current/pg_wal.tar.gz: Not found in archive
>> tar: Exiting with failure status due to previous errors
>> PG_14_202107181/
>> tar: PG_14_202107181: Cannot mkdir: Permission denied
>>
>
> What user are you running pg_basebackup as?
>
>
>> PG_14_202107181/137502/
>> tar: PG_14_202107181: Cannot mkdir: Permission denied
>> tar: PG_14_202107181/137502: Cannot mkdir: No such file or
>> directory
>> PG_14_202107181/137502/3222926016
>> tar: PG_14_202107181: Cannot mkdir: Permission denied
>>
>> The files exist in the specified directory.
>>
>
> /tnt???  Or /mnt?
>
>

Re: postgres sql assistance

[Jim Nasby]

On 1/16/24 10:04 PM, arun chirappurath wrote:

Architect is pressing for a native procedure to data load.


It's possible to write a loader in pl/pgsql but it would be easily twice 
as complex as where you got on your first attempt. It would also never 
perform anywhere near as well as a dedicated loader, because there's no 
way to avoid the temp table (which a native loader doesn't need to use).

--
Jim Nasby, Data Architect, Austin TX

Re: Moving to Postgresql database

[Jim Nasby]

On 1/17/24 5:31 AM, Dominique Devienne wrote:

Is the pg_statement_rollback technically wrong? Can't what it does be done
better and more efficiently if it was in the core itself? Is it a lot of 
code?


I took a quick look at that extension, and it's doing pretty much what 
you'd do if this was baked into Postgres. The performance penaltiy that 
you'll suffer here is that you're going to assign a new transaction ID 
for every statement, which can be significantly more expensive than 
using one XID per BEGIN/COMMIT (depending of course on how many 
statements you have inside a BEGIN/COMMIT).


By the way, you might take a look at Babelfish[1] since it has to solve 
this issue as well due to some of the error handling modes that T-SQL 
supports.


Basically implicit-statement-level-rollback is the norm, AFAIK, and 
PostgreSQL is the exception here.


I'm really curious what other databases you've seen that have this 
behavior, because the only time I've ever seen it was T-SQL. Way back in 
Sybase 11 days it was the only behavior you had, but at some point SQL 
Server (and maybe Sybase) added additional options.


Frankly, this paradigm has always seemed completely broken to me. The 
entire point of having transactions is so you have all-or-nothing 
behavior: either everything works or the transaction aborts. I realize 
that automatically rolling a statement back doesn't technically violate 
ACID, but IMO it definitely violates the spirit of it. While there are 
certainly *some* legitimate uses for rolling a statement back on error, 
in 30 years I've seen maybe one scenario where you'd want to roll a 
statement back on *any* error, and even then it was only on a specific 
statement - not every statement that might get sent to the server.


1: https://babelfishpg.org/
--
Jim Nasby, Data Architect, Austin TX

Re: undefined symbol when installing pgcrypto on 16.1

[Michael Nolan]

Sorry for the delay in responding, network issues kept me offline for
several days.

These are the openssl packages installed from the Almalinux 9 repositories:

apr-util-openssl.x86_64   1.6.1-23.el9   @appstream
openssl.x86_641:3.0.7-24.el9 @anaconda
openssl-devel.x86_64  1:3.0.7-24.el9 @appstream
openssl-libs.x86_64   1:3.0.7-24.el9 @anaconda

I reinstalled the devel package, still get the same unresolved symbol error.

Mike Nolan

Re: undefined symbol when installing pgcrypto on 16.1

[Daniel Gustafsson]

> On 18 Jan 2024, at 00:24, Michael Nolan  wrote:
> 
> Sorry for the delay in responding, network issues kept me offline for
> several days.
> 
> These are the openssl packages installed from the Almalinux 9 repositories:
> 
> apr-util-openssl.x86_64   1.6.1-23.el9   
> @appstream
> openssl.x86_641:3.0.7-24.el9 @anaconda
> openssl-devel.x86_64  1:3.0.7-24.el9 
> @appstream
> openssl-libs.x86_64   1:3.0.7-24.el9 @anaconda
> 
> I reinstalled the devel package, still get the same unresolved symbol error.

My memory is failing me, but isn't CAST5 only available when loading the legacy
provider in OpenSSL 3?  Which providers are loaded in your openssl config?

--
Daniel Gustafsson

Re: undefined symbol when installing pgcrypto on 16.1

[Michael Nolan]

On Wed, Jan 17, 2024 at 5:32 PM Daniel Gustafsson  wrote:
>
> > On 18 Jan 2024, at 00:24, Michael Nolan  wrote:
> >
> > Sorry for the delay in responding, network issues kept me offline for
> > several days.
> >
> > These are the openssl packages installed from the Almalinux 9 repositories:
> >
> > apr-util-openssl.x86_64   1.6.1-23.el9   
> > @appstream
> > openssl.x86_641:3.0.7-24.el9 
> > @anaconda
> > openssl-devel.x86_64  1:3.0.7-24.el9 
> > @appstream
> > openssl-libs.x86_64   1:3.0.7-24.el9 
> > @anaconda
> >
> > I reinstalled the devel package, still get the same unresolved symbol error.
>
> My memory is failing me, but isn't CAST5 only available when loading the 
> legacy
> provider in OpenSSL 3?  Which providers are loaded in your openssl config?
>
> --
> Daniel Gustafsson

The legacy providers were enabled, disabling them worked.  (The build
took a lot longer, too, but there were no obvious messages about
problems with the legacy providers enabled untilI ran the check.)

Thanks, Daniel.

Re: Help needed for the resolution of memory leak

[Merlin Moncure]

On Wed, Jan 17, 2024 at 1:14 PM Sasmit Utkarsh 
wrote:

> Hi Merlin et al.
>
> Below are some couple of observations attached as an "overview_of_code"
> and other attachments "function_def_other_details" and leak sanitizer
> report. Please assist with some clarifications given in overview_of_code
> with (***). Let me know if you need any more information
>

***How do we handle for the case clearing when PGresult object is
assigned a pointer to the data of the specified field within the
existing PGresult object?
i.e when SQL_get_tpf_rw() actually completes in each iteration?



It is your responsibility to close PGResult and PGConn objects.  Each
one created must be cleaned up.  This is basic libpq usage.  I suggest
studying the documentation.



Start here: https://www.postgresql.org/docs/current/libpq-exec.html

Also Study here: https://www.postgresql.org/docs/current/libpq-example.html


You should not reuse a pointer unless you have cleared the object first.


Is the leak reported due to improper handling of the above case ?
or is it due to some other flow

Your leaks look mostly due to not cleaning PGResult.  However, the
real issue here is you need to learn basic libpq usage a little
better...try writing a smaller program and see when it starts to
complain about leaks.


merlin

Re: Moving to Postgresql database

[Merlin Moncure]

On Tue, Jan 16, 2024 at 11:05 AM Dominique Devienne 
wrote:

> On Tue, Jan 16, 2024 at 5:07 PM Adrian Klaver 
> wrote:
>
>> On 1/16/24 00:06, Dominique Devienne wrote:
>> > On Mon, Jan 15, 2024 at 5:17 AM veem v > > > wrote:
>> > Is any key design/architectural changes should the app development
>> > team [...], should really aware about
>> > Hi. One of the biggest pitfall of PostgreSQL, from the app-dev
>> perspective,
>> > is the fact any failed statement fails the whole transaction, with
>> > ROLLBACK as the only recourse.
>>
>> "SAVEPOINT establishes a new savepoint within the current transaction.
>>
>
> I wish it was that easy.
> I've been scared away from using them, after reading a few articles...
> Also, that incurs extra round trips to the server, from the extra commands.
>

Er, *every* statement incurs a round trip to the server.   Candidly, I'm
not sure your point here is entirely thought through, unless you are taking
it to mean when writing ad hoc sql written to the console rather than
generally. The savepoint infrastructure directly implements transaction
control, and does it really well.  It's both classic, broadly implemented,
and standard.

If you are concerned about round trips in general, you'd want to move to a
function or a procedure, where you have classic exception handling, if/else
blocks, etc, and there are no round trips.  postgres really rewards mastery
of server side development practices.

merlin

>

Re: Help needed for the resolution of memory leak

[Sasmit Utkarsh]

ok Thanks Merlin, I will go through the above specified doc and get back in
case of further questions

Regards,
Sasmit Utkarsh
+91-7674022625


On Thu, Jan 18, 2024 at 6:36 AM Merlin Moncure  wrote:

> On Wed, Jan 17, 2024 at 1:14 PM Sasmit Utkarsh 
> wrote:
>
>> Hi Merlin et al.
>>
>> Below are some couple of observations attached as an "overview_of_code"
>> and other attachments "function_def_other_details" and leak sanitizer
>> report. Please assist with some clarifications given in overview_of_code
>> with (***). Let me know if you need any more information
>>
>
> ***How do we handle for the case clearing when PGresult object is assigned a 
> pointer to the data of the specified field within the existing PGresult 
> object?
> i.e when SQL_get_tpf_rw() actually completes in each iteration?
>
>
>
> It is your responsibility to close PGResult and PGConn objects.  Each one 
> created must be cleaned up.  This is basic libpq usage.  I suggest studying 
> the documentation.
>
>
>
> Start here: https://www.postgresql.org/docs/current/libpq-exec.html
>
> Also Study here: https://www.postgresql.org/docs/current/libpq-example.html
>
>
> You should not reuse a pointer unless you have cleared the object first.
>
>
> Is the leak reported due to improper handling of the above case ? or is 
> it due to some other flow
>
> Your leaks look mostly due to not cleaning PGResult.  However, the real issue 
> here is you need to learn basic libpq usage a little better...try writing a 
> smaller program and see when it starts to complain about leaks.
>
>
> merlin
>
>