Re: Memory issues with PostgreSQL 15

[Francisco Olarte]

On Thu, 30 May 2024 at 09:37, Christian Schröder
 wrote:
> Unfortunately, all disks have plenty of free space, so this can be ruled out 
> as a reason.
> I will follow up on the other suggestions from the list.

Do not forget to check all mounted filesystems, not only disks.
Specially /dev/shm, IIRC its mounted size is one limit for posix
shared memory.

Francisco Olarte.

RE: Memory issues with PostgreSQL 15

[Christian Schröder]

Hi Francisco,
Unfortunately, all disks have plenty of free space, so this can be ruled out as 
a reason.
I will follow up on the other suggestions from the list.

Best,
Christian

-Original Message-
From: Francisco Olarte 
Sent: Wednesday, May 29, 2024 10:24 AM
To: Christian Schröder 
Cc: pgsql-general@lists.postgresql.org; Eric Wong 
Subject: Re: Memory issues with PostgreSQL 15

[EXTERNAL]

Hi Christian:

On Wed, 29 May 2024 at 00:59, Christian Schröder  
wrote:
> Thank you for your advice. I used "ipcs" to get more readable information 
> about the shared memory:
...
> As far as I understand, there is no upper limit to the size of the shared 
> memory. The database only holds a single shared memory segment, which doesn't 
> seem to have a relevant size.

Seems the same to me, so I will disregard that.

> I am surprised to see this since I would have expected much more shared 
> memory to be used by the database. Is there anything in the configuration 
> that prevents the shared memory from being used?

I am not too current with postgres, that one was a thing which happened to me 
when I did more administration, and is one you always want to check. I assume 
you have all checked, but I would follow by insuring every mounted partition in 
your system has space. I am not current on the details, but I know Pg can be 
mmaping things and doing other stuff. Your problem seems more of resource 
exhaustion, so I would follow by checking that, both disk, tmpfs and all the 
other stuff. I cannot give you advice on that as it depends a lot on your 
server configuration and from the age in the mssages I suspect you have the 
usual suspects debugged. But as you have a configuration crahsing in minutes 
and it seems to be a dev server you could do it easily.

Sorry.

   Francisco Olarte.


--
SUPPORT:
For any issues, inquiries, or assistance, please contact our support team at 
supp...@wsd.com. Our dedicated team is available to help you and provide prompt 
assistance.

CONFIDENTIALITY NOTICE:
This email and any attachments are confidential and intended solely for the use 
of the individual or entity to whom it is addressed. If you have received this 
email in error, please notify the sender immediately and delete it from your 
system.

RE: Memory issues with PostgreSQL 15

[Christian Schröder]

Hi Salahuddin,
I had already checked most of your points, but I double checked them now.

# free -m
  totalusedfree  shared  buff/cache   available
Mem:  158821466 2692110   14147   11976
Swap:  1999 2541745

Free memory seems to be low, which is normal because most of the memory is used 
by buffers and caches. As you can see, the available memory is almost 12 GB.

shared_buffers, work_mem, etc.
Our initial setting for “shared_buffers” was 4 GB, which is roughly 25% of the 
system memory; however, I tried different values (see my original message), but 
none of them seemed to work. We also played around with the other settings but 
couldn’t find any combination that worked.

Shared memory limits look good to me:
# sudo sysctl -a | grep kernel.shm
kernel.shmall = 18446744073692774399
kernel.shmmax = 18446744073692774399
kernel.shmmni = 4096

Thanks,
Christian

From: Muhammad Salahuddin Manzoor 
Sent: Wednesday, May 29, 2024 11:41 AM
To: Christian Schröder 
Cc: pgsql-general ; Eric Wong 

Subject: Re: Memory issues with PostgreSQL 15

[EXTERNAL]
Greetings,

The error message you encountered, "could not fork autovacuum worker process: 
Cannot allocate memory," indicates that your PostgreSQL server attempted to 
start an autovacuum worker process but failed because the system ran out of 
memory.

Steps to verify.
1 Check system available memory with commands.
free -m
top
2. Check PG configurations.
shared_buffers --Typically 25% of total mem.
work_mem
maintenance_work_mem--For maintenance op like autovaccume create index etc. 
Increase it to 64MB or appropriate to your requirement.
max_connections

Monitor /var/log/messages file for errors.

2024-05-21 11:34:46 CEST - mailprocessor> ERROR:  could not resize shared 
memory segment "/PostgreSQL.2448337832" to 182656 bytes: No space left on device

Check  share memory limits.
/etc/sysctl.conf
kernel.shmmax = 68719476736  # Example value, adjust as needed
kernel.shmall = 16777216 # Example value, adjust as needed

Restart system and db

Ensure you have enough disk space available check and monitor disk space with 
command
df -h

Reduce  max_parallel_workers_per_gather = 2;
If it is set to high value.

I think setting up OS parameter.
Increasing maintenance mem value and reducing max paralell workers xan help in 
solution.

Regards,
Salahuddin.

On Tue, 28 May 2024, 21:40 Christian Schröder, 
mailto:christian.schroe...@wsd.com>> wrote:
Hi all,
We migrated from PostgreSQL 9.4 to PostgreSQL 15 a while ago. Since then, we 
have a lot of memory issues in our QA environment (which is a bit tense in 
resources). We did not have these problems before the migration, and we do not 
have them in our production environment, which has a lot more memory. So, it is 
not super critical for us, but I would still like to understand better how we 
can improve our configuration.

Our PostgreSQL version is "PostgreSQL 15.5 on x86_64-pc-linux-gnu, compiled by 
gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit". The database server is a 
dedicated server with 15 GB RAM (and 4 cores, if this matters).
We used the following settings:
shared_buffers = 4GB
work_mem = 4MB

After a while, we saw the following error in the logs:

<2024-05-20 12:01:03 CEST - > LOG:  could not fork autovacuum worker process: 
Cannot allocate memory

However, according to "free", a lot of memory was available:

# free -m
  totalusedfree  shared  buff/cache   available
Mem:  158824992 4634195   104276365
Swap:  1999 2711728

Our Grafana charts showed a slow increase in memory consumption until it 
plateaus at 4.66 GB.
We also found the following error:

<2024-05-21 11:34:46 CEST - mailprocessor> ERROR:  could not resize shared 
memory segment "/PostgreSQL.2448337832" to 182656 bytes: No space left on device

I thought this could all be related to our "shared_buffers" setting, so I 
increased it to 8 GB. This almost immediately (after a few minutes) gave me 
these errors:

<2024-05-27 11:45:59 CEST - > ERROR:  out of memory
<2024-05-27 11:45:59 CEST - > DETAIL:  Failed on request of size 201088574 in 
memory context "TopTransactionContext".
...
<2024-05-27 11:58:02 CEST - > ERROR:  out of memory
<2024-05-27 11:58:02 CEST - > DETAIL:  Failed while creating memory context 
"dynahash".
<2024-05-27 11:58:02 CEST - > LOG:  background worker "parallel worker" (PID 
21480) exited with exit code 1
...
<2024-05-27 12:01:02 CEST - > LOG:  could not fork new process for connection: 
Cannot allocate memory
<2024-05-27 12:01:03 CEST - > LOG:  could not fork autovacuum worker process: 
Cannot allocate memory
<2024-05-27 12:02:02 CEST - > LOG:  could not fork new process for connection: 
Cannot allocate memory

Since this seemed worse than before, I changed the setting back to 4 GB. I 
noticed that "free" now reports even m

Re: Memory issues with PostgreSQL 15

[Francisco Olarte]

Hi christian:

On Thu, 30 May 2024 at 12:51, Christian Schröder
 wrote:
...
> I had already checked most of your points, but I double checked them now.
...
> Shared memory limits look good to me:
> # sudo sysctl -a | grep kernel.shm
> kernel.shmall = 18446744073692774399
> kernel.shmmax = 18446744073692774399
> kernel.shmmni = 4096

Bear in mind this is SysV shared memory. IIRC Pg uses POSIX shared
memory for shared buffers, which I think is backed normally in Linux
by files in a tmpfs mounted on /dev/shm. It still uses some amount of
SysV due to some special properties lacking from POSIX, for control
purposes, but only a little.

You could try "df -h /dev/shm" and "ls -lhR /dev/shm/" to see if you
have problems there.

Francisco Olarte.

Re: Dll libpq.dll 32 bits

[José Mello Júnior]

No, I can´t use ODBC in this case, because I only access a new server. I
need the DLL in 32 bits.

Atte
Mello

Em qua., 29 de mai. de 2024 às 23:45, Juan Rodrigo Alejandro Burgos Mella <
rodrigoburgosme...@gmail.com> escreveu:

> you can use ODBC with Windev, so the best is the Postgresql connector
> https://www.postgresql.org/ftp/odbc/releases/
>
> Atte
> JRBM
>
> El mié, 29 may 2024 a las 14:46, José Mello Júnior (<
> jose.mello.jun...@gmail.com>) escribió:
>
>>
>> Em qua., 29 de mai. de 2024, 15:41, Adrian Klaver <
>> adrian.kla...@aklaver.com> escreveu:
>>
>>> On 5/29/24 11:18, José Mello Júnior wrote:
>>>
>>> Again reply to list by using Reply All.
>>> Ccing list.
>>> > For my projects in 32 bits, the language of PC-SOFT (wlanguage) use.
>>>
>>> Are you talking about this?:
>>>
>>> https://windev.com/pcsoft/index.html
>>>
>>> >
>>> > Em qua., 29 de mai. de 2024, 15:09, Adrian Klaver
>>> > mailto:adrian.kla...@aklaver.com>>
>>> escreveu:
>>> >
>>> >
>>> >
>>> > On 5/29/24 10:59 AM, José Mello Júnior wrote:
>>> >
>>> > Reply to list also.
>>> > Ccing list.
>>> >
>>> >  > Sorry, for windows
>>> >
>>> > That is the OS, what I am after is what client on Windows do you
>>> need
>>> > the DLL for?
>>> >
>>> >  >
>>> >  >
>>> >  >
>>> >  > Em qua., 29 de mai. de 2024, 14:53, Adrian Klaver
>>> >  > mailto:adrian.kla...@aklaver.com>
>>> > >> > >> escreveu:
>>> >  >
>>> >  >
>>> >  >
>>> >  > On 5/29/24 10:49 AM, José Mello Júnior wrote:
>>> >  >  > Where do i found this dll for acess postgresql 15?
>>> >  >
>>> >  > Access Postgres using what client?
>>> >  >
>>> >  > --
>>> >  > Adrian Klaver
>>> >  > adrian.kla...@aklaver.com 
>>> > > adrian.kla...@aklaver.com>>
>>> >  >
>>> >
>>> > --
>>> > Adrian Klaver
>>> > adrian.kla...@aklaver.com 
>>> >
>>>
>>> --
>>> Adrian Klaver
>>> adrian.kla...@aklaver.com
>>>
>>>

-- 
Mello Júnior
41.3252-3555

Rules and Command Status - update/insert/delete rule with series of commands in action

[johnlu...@hotmail.com]

The RULE infrastructure permits the programmer to specify a series of 
commands in the DO action


from the syntax diagram in the manual :


CREATE [ OR REPLACE ] RULE name AS ON event
    TO table [ WHERE condition ]
    DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) }


The manual described the series of commands as the "rule action" ,  
implying (to me) that there is a sense in which the entire series 
comprising the action is one operation.



I am specifically interested in the case of update and an example of an 
unconditional rule such as


CREATE or REPLACE RULE multi-action AS ON UPDATE TO my_view
    DO INSTEAD (
    UPDATE my_table_a
    SET a_column = value
   WHERE OLD.keycolumn = keyvalue;
    UPDATE my_table_b
    SET b_column = value
   WHERE OLD.keycolumn = keyvalue;
  );

where my intention is that one and only one of the action commands 
should update any row.



This all works except for one thing :   the final status, including the 
(for me) all-important number of rows updated.



It turns out that in this example, if the UPDATE my_table_b updates 
one (or more) rows,    the status shows that number,   but if the UPDATE 
my_table_b updates no rows,    the status shows 0 rows updated,   even 
if one (or more) rows of my_table_a were successfully updated by the 
first command. This is not what I want.



The chapter entitled "Rules and Command Status "  (approximately chap 
number 41.6 depending on version) says


"If there is any unconditional|INSTEAD|rule for the query, then the 
original query will not be executed at all. In this case, the server 
will return the command status for the last *query* that was inserted by 
an|INSTEAD|rule (conditional or unconditional) and is of the same 
command type (|INSERT|,|UPDATE|, or|DELETE|) as the original query." 
(my bold of the word query).



But what is a query in this context?    In my example,    is the 
last *query* the


. *action* of the last unconditional RULE which executed  (only one in 
my example but there could be other applicable rules for update of my_view)


OR

.   last *command* of the series of commands comprising theaction of the 
last unconditional RULE which executed


?


Well, I assume what postgresql actually does is the latter, but surely 
there is a case for it to be the former, where the rows_updated of the 
action would be the sum of all rows updated by all commands in that 
action's series.  .   In my example,    postgresql is telling the 
application that no rows were updated when actually one (or more) row 
was updated, and the sum of all rows updated is one.



Any thoughts?  Any rationales one way or the other?    Any interest 
in perhaps providing a choice via a configuration parameter?



Cheers, John Lumby

Re: Dll libpq.dll 32 bits

[Juan Rodrigo Alejandro Burgos Mella]

postgresql odbc has 32 and 64 bits, and you make a System DSN and that's it
I have made implementations for PHP, .Net, C#, Python; even ASP Classic.

Atte.
JRBM

El jue, 30 may 2024 a las 13:27, José Mello Júnior (<
jose.mello.jun...@gmail.com>) escribió:

> No, I can´t use ODBC in this case, because I only access a new server. I
> need the DLL in 32 bits.
>
> Atte
> Mello
>
> Em qua., 29 de mai. de 2024 às 23:45, Juan Rodrigo Alejandro Burgos Mella <
> rodrigoburgosme...@gmail.com> escreveu:
>
>> you can use ODBC with Windev, so the best is the Postgresql connector
>> https://www.postgresql.org/ftp/odbc/releases/
>>
>> Atte
>> JRBM
>>
>> El mié, 29 may 2024 a las 14:46, José Mello Júnior (<
>> jose.mello.jun...@gmail.com>) escribió:
>>
>>>
>>> Em qua., 29 de mai. de 2024, 15:41, Adrian Klaver <
>>> adrian.kla...@aklaver.com> escreveu:
>>>
 On 5/29/24 11:18, José Mello Júnior wrote:

 Again reply to list by using Reply All.
 Ccing list.
 > For my projects in 32 bits, the language of PC-SOFT (wlanguage) use.

 Are you talking about this?:

 https://windev.com/pcsoft/index.html

 >
 > Em qua., 29 de mai. de 2024, 15:09, Adrian Klaver
 > mailto:adrian.kla...@aklaver.com>>
 escreveu:
 >
 >
 >
 > On 5/29/24 10:59 AM, José Mello Júnior wrote:
 >
 > Reply to list also.
 > Ccing list.
 >
 >  > Sorry, for windows
 >
 > That is the OS, what I am after is what client on Windows do you
 need
 > the DLL for?
 >
 >  >
 >  >
 >  >
 >  > Em qua., 29 de mai. de 2024, 14:53, Adrian Klaver
 >  > mailto:adrian.kla...@aklaver.com>
 >  >> escreveu:
 >  >
 >  >
 >  >
 >  > On 5/29/24 10:49 AM, José Mello Júnior wrote:
 >  >  > Where do i found this dll for acess postgresql 15?
 >  >
 >  > Access Postgres using what client?
 >  >
 >  > --
 >  > Adrian Klaver
 >  > adrian.kla...@aklaver.com 
 > >> adrian.kla...@aklaver.com>>
 >  >
 >
 > --
 > Adrian Klaver
 > adrian.kla...@aklaver.com 
 >

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


>
> --
> Mello Júnior
> 41.3252-3555
>

ERROR: found xmin from before relfrozenxid; MultiXactid does no longer exist -- apparent wraparound

[Alanoly Andrews]

Hi,

We have a postgres 10.7 database which reports a number of issues on 
user-created tables as well as system tables. Most errors are one of the 
following:
-- ERROR:  found xmin 1888159934 from before relfrozenxid 1998177448
-- ERROR:  MultiXactId 613819197 does no longer exist -- apparent wraparound
-- ERROR:  could not access status of transaction 1927393975
   DETAIL:  Could not open file "pg_xact/072E": No such file or directory.

I have tried several of the workarounds suggested online and in the web 
discussion groups:
1. vacuumdb of the entire database fails with the "found xmin from before 
relfrozenxid" error
2. pg_dump fails with the same error
3. SELECT sql on the affected tables fails with the error. So I cannot save the 
table, drop it and re-create it.
4. Removed the "global/pg_internal.init" file and re-started the cluster. Still 
the same errors.

The database is up and running and most of the tables are accessible. But any 
kind of SQL on the 4 or 5 affected tables throws the error.

Is there a way to repairing the corruption in this database?
Postgres Version 10.7 on Linux(Ubuntu).

Thanks.

Alanoly Andrews
(alano...@invera.com)


This e-mail may be privileged and/or confidential, and the sender does not 
waive any related rights and obligations. Any distribution, use or copying of 
this e-mail or the information it contains by other than an intended recipient 
is unauthorized. If you received this e-mail in error, please advise me (by 
return e-mail or otherwise) immediately.


Ce courriel est confidentiel et protégé. L'expéditeur ne renonce pas aux droits 
et obligations qui s'y rapportent. Toute diffusion, utilisation ou copie de ce 
message ou des renseignements qu'il contient par une personne autre que le 
(les) destinataire(s) désigné(s) est interdite. Si vous recevez ce courriel par 
erreur, veuillez m'en aviser immédiatement, par retour de courriel ou par un 
autre moyen.'.

Re: Dll libpq.dll 32 bits

[José Mello Júnior]

Don't have more?

Em qui., 30 de mai. de 2024, 16:14, Juan Rodrigo Alejandro Burgos Mella <
rodrigoburgosme...@gmail.com> escreveu:

> postgresql odbc has 32 and 64 bits, and you make a System DSN and that's it
> I have made implementations for PHP, .Net, C#, Python; even ASP Classic.
>
> Atte.
> JRBM
>
> El jue, 30 may 2024 a las 13:27, José Mello Júnior (<
> jose.mello.jun...@gmail.com>) escribió:
>
>> No, I can´t use ODBC in this case, because I only access a new server. I
>> need the DLL in 32 bits.
>>
>> Atte
>> Mello
>>
>> Em qua., 29 de mai. de 2024 às 23:45, Juan Rodrigo Alejandro Burgos Mella
>>  escreveu:
>>
>>> you can use ODBC with Windev, so the best is the Postgresql connector
>>> https://www.postgresql.org/ftp/odbc/releases/
>>>
>>> Atte
>>> JRBM
>>>
>>> El mié, 29 may 2024 a las 14:46, José Mello Júnior (<
>>> jose.mello.jun...@gmail.com>) escribió:
>>>

 Em qua., 29 de mai. de 2024, 15:41, Adrian Klaver <
 adrian.kla...@aklaver.com> escreveu:

> On 5/29/24 11:18, José Mello Júnior wrote:
>
> Again reply to list by using Reply All.
> Ccing list.
> > For my projects in 32 bits, the language of PC-SOFT (wlanguage) use.
>
> Are you talking about this?:
>
> https://windev.com/pcsoft/index.html
>
> >
> > Em qua., 29 de mai. de 2024, 15:09, Adrian Klaver
> > mailto:adrian.kla...@aklaver.com>>
> escreveu:
> >
> >
> >
> > On 5/29/24 10:59 AM, José Mello Júnior wrote:
> >
> > Reply to list also.
> > Ccing list.
> >
> >  > Sorry, for windows
> >
> > That is the OS, what I am after is what client on Windows do you
> need
> > the DLL for?
> >
> >  >
> >  >
> >  >
> >  > Em qua., 29 de mai. de 2024, 14:53, Adrian Klaver
> >  > mailto:adrian.kla...@aklaver.com>
> >  > >> escreveu:
> >  >
> >  >
> >  >
> >  > On 5/29/24 10:49 AM, José Mello Júnior wrote:
> >  >  > Where do i found this dll for acess postgresql 15?
> >  >
> >  > Access Postgres using what client?
> >  >
> >  > --
> >  > Adrian Klaver
> >  > adrian.kla...@aklaver.com 
> > >
> >  >
> >
> > --
> > Adrian Klaver
> > adrian.kla...@aklaver.com 
> >
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>
>
>>
>> --
>> Mello Júnior
>> 41.3252-3555
>>
>

Re: Rules and Command Status - update/insert/delete rule with series of commands in action

[David G. Johnston]

On Thu, May 30, 2024, 12:32 johnlu...@hotmail.com 
wrote:

> Any thoughts?
>
Very little interest exists in working on user-specified rules.  They are
practically deprecated.

>
> Any interest in perhaps providing a choice via a configuration
> parameter?
>

Almost certainly not.  Configuration should not affect query behavior.  If
anything is done it would have to be new syntax.  Though I haven't explored
the use case presented.  See the first point.  I sure don't have the
requisite familiarity here.

David J.

Re: Rules and Command Status - update/insert/delete rule with series of commands in action

[Adrian Klaver]

On 5/30/24 11:32, johnlu...@hotmail.com wrote:

Any thoughts?  Any rationales one way or the other?    Any interest 
in perhaps providing a choice via a configuration parameter?


1) As to using rules, stop now before you invest the time to find out is 
a road you don't want to go down.


2) Use INSTEAD OF triggers:

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






Cheers, John Lumby





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

How to delete column level Stats/Histogram

[Wong, Kam Fook (TR Technology)]

  1.  Is there a way to delete a specific column level stats/histogram.  The 
following approach does not work.



alter table abc alter column bg_org_partner set statistics 0;

analyze   abc;



select *

FROM pg_stats where tablename in ('abc' ) and attname = 'bg_org_partner';



the most_common_vals remain and most_common_freqs remain the same.



  1.  Any other ways or workaround such as wiping out a specific table level 
stat, then restore the stats but minus 1 column.  And the future vacuum 
analyze/analyze will not be updating that specific column stat.

Thank you
This e-mail is for the sole use of the intended recipient and contains 
information that may be privileged and/or confidential. If you are not an 
intended recipient, please notify the sender by return e-mail and delete this 
e-mail and any attachments. Certain required legal entity disclosures can be 
accessed on our website: 
https://www.thomsonreuters.com/en/resources/disclosures.html

Re: Rules and Command Status - update/insert/delete rule with series of commands in action

[johnlu...@hotmail.com]

On 5/30/24 4:56 PM, David G. Johnston wrote:


Very little interest exists in working on user-specified rules.  They 
are practically deprecated.



Ah  -   pity  -   see my last comment to Adrian's




Any interest in perhaps providing a choice via a configuration
parameter?


 If anything is done it would have to be new syntax.



A much bigger task surely.


On 5/30/24 5:19 PM, Adrian Klaver wrote:


2) Use INSTEAD OF triggers:




Unfortunately the same functionality as in my example with the RULE is 
not supported for triggers on views :   from the manual


|INSTEAD OF| triggers may only be defined on views, *and only at row level*;


A RULE is essentially a statement-level operation which is what I need 
for this particular case.  A row-level trigger would not work 
because it cannot "see" the query causing it to be fired, and also ,  
(most importantly) is not fired at all if no rows match the original 
query, whereas a RULE is always in effect regardless of which rows 
are involved. before.  I should add that the RULE I showed in my 
example is not the only RULE being used on this view  -    there are 
other conditional RULEs,   and the combined effect is of being able to 
change the effect of the original statement into a set of new 
statements,   one of which does what is needed.



And if you are now inclined to say "well,    maybe the application 
itself is poorly written and should be changed"  -   I would have to 
agree,    but that is not mine to change.



But I suppose that my next question,   given what you both say about the 
RULE system being a dead-end,  is whether there is any likelihood of 
supporting an INSTEAD OF trigger on a view at statement level?   Maybe 
that stands more chance of going somewhere?




Cheers, John Lumby

Re: How to delete column level Stats/Histogram

[David Rowley]

On Fri, 31 May 2024 at 09:28, Wong, Kam Fook (TR Technology)
 wrote:
> Is there a way to delete a specific column level stats/histogram.  The 
> following approach does not work.
>
> alter table abc alter column bg_org_partner set statistics 0;
> analyze   abc;

You'd have to:

DELETE FROM pg_statistic WHERE starelid = 'abc'::regclass and
staattnum = (SELECT attnum FROM pg_attribute WHERE attrelid =
'abc'::regclass AND attname = 'bg_org_partner');

to get rid of it.

David

Re: Dll libpq.dll 32 bits

[Juan Rodrigo Alejandro Burgos Mella]

What type of project are you developing at Windev? As a proof of concept,
maybe using libpq.lib we can do something

Atte
JRBM

El jue, 30 may 2024 a las 16:37, José Mello Júnior (<
jose.mello.jun...@gmail.com>) escribió:

> Don't have more?
>
> Em qui., 30 de mai. de 2024, 16:14, Juan Rodrigo Alejandro Burgos Mella <
> rodrigoburgosme...@gmail.com> escreveu:
>
>> postgresql odbc has 32 and 64 bits, and you make a System DSN and that's
>> it
>> I have made implementations for PHP, .Net, C#, Python; even ASP Classic.
>>
>> Atte.
>> JRBM
>>
>> El jue, 30 may 2024 a las 13:27, José Mello Júnior (<
>> jose.mello.jun...@gmail.com>) escribió:
>>
>>> No, I can´t use ODBC in this case, because I only access a new server. I
>>> need the DLL in 32 bits.
>>>
>>> Atte
>>> Mello
>>>
>>> Em qua., 29 de mai. de 2024 às 23:45, Juan Rodrigo Alejandro Burgos
>>> Mella  escreveu:
>>>
 you can use ODBC with Windev, so the best is the Postgresql connector
 https://www.postgresql.org/ftp/odbc/releases/

 Atte
 JRBM

 El mié, 29 may 2024 a las 14:46, José Mello Júnior (<
 jose.mello.jun...@gmail.com>) escribió:

>
> Em qua., 29 de mai. de 2024, 15:41, Adrian Klaver <
> adrian.kla...@aklaver.com> escreveu:
>
>> On 5/29/24 11:18, José Mello Júnior wrote:
>>
>> Again reply to list by using Reply All.
>> Ccing list.
>> > For my projects in 32 bits, the language of PC-SOFT (wlanguage) use.
>>
>> Are you talking about this?:
>>
>> https://windev.com/pcsoft/index.html
>>
>> >
>> > Em qua., 29 de mai. de 2024, 15:09, Adrian Klaver
>> > mailto:adrian.kla...@aklaver.com>>
>> escreveu:
>> >
>> >
>> >
>> > On 5/29/24 10:59 AM, José Mello Júnior wrote:
>> >
>> > Reply to list also.
>> > Ccing list.
>> >
>> >  > Sorry, for windows
>> >
>> > That is the OS, what I am after is what client on Windows do
>> you need
>> > the DLL for?
>> >
>> >  >
>> >  >
>> >  >
>> >  > Em qua., 29 de mai. de 2024, 14:53, Adrian Klaver
>> >  > mailto:adrian.kla...@aklaver.com
>> >
>> > > > >> escreveu:
>> >  >
>> >  >
>> >  >
>> >  > On 5/29/24 10:49 AM, José Mello Júnior wrote:
>> >  >  > Where do i found this dll for acess postgresql 15?
>> >  >
>> >  > Access Postgres using what client?
>> >  >
>> >  > --
>> >  > Adrian Klaver
>> >  > adrian.kla...@aklaver.com 
>> >  adrian.kla...@aklaver.com>>
>> >  >
>> >
>> > --
>> > Adrian Klaver
>> > adrian.kla...@aklaver.com 
>> >
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com
>>
>>
>>>
>>> --
>>> Mello Júnior
>>> 41.3252-3555
>>>
>>

Re: Rules and Command Status - update/insert/delete rule with series of commands in action

[Tom Lane]

"johnlu...@hotmail.com"  writes:
> But I suppose that my next question,   given what you both say about the 
> RULE system being a dead-end,  is whether there is any likelihood of 
> supporting an INSTEAD OF trigger on a view at statement level?   Maybe 
> that stands more chance of going somewhere?

Perhaps, but I don't know of anyone working on it.

IIRC, the row-level-only restriction dates from before we had
implemented transition tables, so that at the time there was
nothing very meaningful that a statement-level trigger could do.
Maybe that problem has gone away, though I've not thought
about it very hard.

regards, tom lane

Re: [EXT] Re: How to delete column level Stats/Histogram

[David Rowley]

(please keep communication on the list)

On Fri, 31 May 2024 at 13:43, Wong, Kam Fook (TR Technology)
 wrote:
>
> Silly question why did I run into this problem below?  Will the autovacuum 
> analyze abc reset it back which I don't want it to.
>
> DELETE FROM pg_statistic WHERE starelid = 'abc'::regclass and staattnum = 
> (SELECT attnum FROM pg_attribute WHERE attrelid = 'abc'::regclass AND attname 
> = 'bg_org_partner');
>
> SQL Error [42P01]: ERROR: relation "abc" does not exist
>   Position: 52

The schema for the abc table will need to be in your search_path.
You'll need to be connected to the correct database too.

David

Re: [pgpool-general: 9106] Postgres/pgpool HA failover process

[Mukesh Tanuku]

BTW here is the network configuration we set as

UTOCONNECT_PRIORITY=120
BOOTPROTO=dhcp
DEVICE=eth0
DHCPV6C=yes
HWADDR=
IPV6INIT=yes
ONBOOT=yes
TYPE=Ethernet
USERCTL=no

Regards
Mukesh Tanuku

On Wed, May 29, 2024 at 12:53 PM Mukesh Tanuku 
wrote:

> Thanks Bo for your response to my email.
>
> Below is the parameters:
>
>
>
> * if_up_cmd = '/usr/bin/sudo /sbin/ip addr add $_IP_$/24 dev enp0s8 label
> enp0s8:0'  if_down_cmd = '/usr/bin/sudo /sbin/ip addr del $_IP_$/24 dev
> enp0s8'  arping_cmd = '/usr/bin/sudo /usr/sbin/arping -U $_IP_$ -w 1 -I
> enp0s8'*
>
> The above commands are working on Leader node. and once the VIP is up
> successfully then we can run the pcp commands via VIP host work only from
> the leader node locally. if we try to execute the PCP comands via VIP
> throws an error
> ERROR: connection to host "" failed with error "No route to host"
>
> What's the possible cause in this case?
>
> Regards
> Mukesh Tanuku
>
>
> On Tue, May 28, 2024 at 5:30 AM Bo Peng  wrote:
>
>> Hi,
>>
>> > Hello Everyone,
>> >
>> > We are working on to setup a High available solution for postgres and
>> > running some test cases
>> > based on that we need some clarification on the HA setup as per the same
>> > configuration given in this example
>> > 
>>
>> If you want to configure a new cluster,
>> we recommend using the latest version.
>>
>> https://www.pgpool.net/docs/latest/en/html/example-cluster.html
>>
>> > Question - 01
>> > Regarding the active client connections that are connecting via delegate
>> > VIP, what happens to those connections during the failover process? If
>> > either the primary DB goes down (gracefully/crash) then the
>> > active connection will get hung till the new primary DB is available or
>> > those active connections get terminated?
>>
>> The existing connections will be disconnected if primary DB is down.
>> However, the subsequent connections will be routed to the new primary.
>>
>> > What if the same case for Leader pgpool service as well?
>>
>> Yes.
>>
>> > Questions - 02
>> > Configuring the delegate IP:
>> > a. Are there any standards/limitations for choosing the unused IP as
>> > delegate IP? I mean like the IP must be within the same subnet range of
>> all
>> > the nodes in the cluster?
>>
>> No limitations in Pgpool-II, it depends on the OS.
>> Pgpool-II using "ip addr ..." command to assign or release a delegate IP.
>>
>> Below is the parameters:
>>
>>   if_up_cmd = '/usr/bin/sudo /sbin/ip addr add $_IP_$/24 dev enp0s8 label
>> enp0s8:0'
>>   if_down_cmd = '/usr/bin/sudo /sbin/ip addr del $_IP_$/24 dev enp0s8'
>>   arping_cmd = '/usr/bin/sudo /usr/sbin/arping -U $_IP_$ -w 1 -I enp0s8'
>>
>> You need to make sure the commands work.
>>
>> > b. Any challenges we see for non-root user to work with VIP? because we
>> run
>> > the pgpool with non-root user.
>>
>> You need to make sure the non-root user can execute ip and arping command
>> with sudo
>> without a password.
>>
>> --
>> Bo Peng 
>> SRA OSS LLC
>> TEL: 03-5979-2701 FAX: 03-5979-2702
>> URL: https://www.sraoss.co.jp/
>>
>