vacuum freeze wait_event BufferPin

2024-01-31 Thread abrahim abrahao
 There is a three-day vacuum freeze on a partition table with wait_event = 
BufferPin, no transactions (active or idle) older than it that are not blocked, 
but at least one select query is executing at all times related of this 
partition table. 
 This behaviour happens with some partitions, but not always. Running simply 
vacuum works because it does not wait for BufferPin. I just expect to see 
vacuum freeze wait_event = BufferPin if there is any old transaction related to 
the table, am I wrong? Is there a wait to figure out which session vacuum 
freeze to wait for?
 Below is a list of all sessions at a particular moment in time; at this point, 
there are only Select queries with query starts older than the vacuum freeze, 
and no cursor, transaction block, or zombie sessions.


  SELECT pg.query_start,  now() -  query_start AS duration, pg.pid pg_pid, 
pg.state,  pg.wait_event, backend_type  ,pg.wait_event_type, 
pg_blocking_pids(pid) as blocked_by,SPLIT_PART(trim(pg.query),' ', 1)FROM 
pg_stat_activity pg order by now() -  query_start desc;          query_start    
                        |        duration              | pg_pid | state  |      
wait_event               |         backend_type               | wait_event_type 
| blocked_by | 
split_part--++---++--+--+---++
                                                        |                       
            |    249    |           | AutoVacuumMain        | autovacuum 
launcher           | Activity                | {}         |                     
                                   |                                   |    252 
   |           | LogicalLauncherMain  | logical replication launcher   | 
Activity                | {}         |                                          
               |                                   |    247    |           | 
BgWriterMain               | background writer                | Activity        
        | {}         |                                                         
|                                   |    250    |           | ArchiverMain      
          | archiver                                | Activity                | 
{}         |                                                         |          
                         |    246    |           | CheckpointWriteDelay | 
checkpointer                         | Timeout              | {}         |      
                                                   |                            
       |    248    |           | WalWriterMain             | walwriter          
                     | Activity        | {}         | 2024-01-27 
23:10:54.159577+00 | 3 days 18:55:48.         | 271039 | active| BufferPin      
                | client backend                      | BufferPin             | 
{}         | vacuum 2024-01-30 21:33:50.262265+00 | 20:32:52.490309        | 
558810 | idle     | ClientRead                  | client backend                
      | Client          | {}         | SELECT 2024-01-31 
16:53:17.695929+00 | 01:13:25.056645        | 641053 | idle     | ClientRead    
              | client backend                      | Client          | 
{}         | SELECT 2024-01-31 18:04:48.929097+00 | 00:01:53.823477        | 
645812 | active |                                     | client backend          
            |                            | {}         | SELECT 2024-01-31 
18:05:02.221272+00 | 00:01:40.531302        | 645814 | active |                 
                    | client backend                      |             
    | {}         | SELECT 2024-01-31 18:05:04.927131+00 | 00:01:37.825443   
     | 645925 | active |                                     | client backend   
                   |                 | {}         | SELECT 
2024-01-31 18:05:12.088875+00 | 00:01:30.663699        | 645109 | active |      
                               | client backend                      |          
       | {}         | SELECT 2024-01-31 18:05:30.202305+00 | 
00:01:12.550269        | 645695 | active |                                     
| client backend                      |                 | {}        
 | SELECT 2024-01-31 18:05:47.872601+00 | 00:00:54.879973        | 645924 | 
active |                                     | client backend                   
   |                 | {}         | SELECT 2024-01-31 
18:06:16.425286+00 | 00:00:26.327288        | 645593 | active |                 
                    | client backend                      |         
        | {}         | SELECT 2024-01-31 18:06:18.286905+00 | 00:00:24.465669   
     | 646174 | idle     | ClientRead                  | client backend         
             | Client       

Postgresql BUG / Help Needed

2024-01-31 Thread Johnathan Tiamoh
Hello,

I am having the  following issue below.  Please, I need help to fix it.

[image: User]

*ERROR:  could not access status of transaction 756525298*
*Could not open file "pg_xact/02C8": No such file or directory.*


Thank You
Johnathan T


Query to fetch the list of tables that have cascade constraints enabled

2024-01-31 Thread Satalabaha Postgres
Hi All,

I am trying to fetch the list of tables that have cascade constraints
enabled. Can someone help me with the query related to this?

Regards,

Satalabha


Re: Query to fetch the list of tables that have cascade constraints enabled

2024-01-31 Thread David G. Johnston
On Wednesday, January 31, 2024, Satalabaha Postgres <
satalabaha.postg...@gmail.com> wrote:

>
> I am trying to fetch the list of tables that have cascade constraints
> enabled. Can someone help me with the query related to this?
>
>
 https://www.postgresql.org/docs/current/catalog-pg-constraint.html

David J.


Issue in Postgres Client 14.9 with OpenSSL 3.2.0

2024-01-31 Thread M Tarkeshwar Rao
Hi,

We are facing issue while trying to connect securely(ssl) from postgres c 
client(libpq) with postgresql server.
We have compiled postgreClient 14.9(postgresql-14.9.tar.gz) using OpenSSL 
version 3.2.0.

Backtrace of the issue is mentioned below :
  #0  0x009a71f8 in _shi_removeFromFreeList ()
  #1  0x009a7be8 in _shi_freeVar ()
  #2  0x009a913c in MemFreePtr ()
  #3  0x7f407b378f21 in freePGconn () from 
/opt/mediation/CXC1742082_R27D//lib/libs/libpq.so.5
  #4  0x7f407b3c9144 in PostgreSQLClient::connect  at 
PostgreSQLClient.cc:170
  #5  0x7f407b3d9089 in PostgreSQLClient::beginTransaction

We also facing same issue when running from commandline using psql:

  ./psql "dbname=drrf_db_emtdrrf sslmode=require host=X.X.X.X 
user=XYZABC port=5434 sslcert=tls.crt sslkey=tls.key"
  psql: error: connection to server at "X.X.X.X", port 5434 failed: 
FATAL:  no PostgreSQL user name specified in startup packet
  free(): double free detected in tcache 2
  Aborted (core dumped)

We also find similar type of issues on internet and one link from postgres 
website as well.

https://www.postgresql.org/message-id/cx9su44gh3p4.17x6zzuj5d...@neon.tech
NOTE: When compiles postgreClient with openSSL version ???, then secure 
connection working properly.

Kindly suggest on which release and on what version (14.x/16.x) we will get 
this fix. Also is there any workaround or formal patch which can be applied on 
14.9 to fix this issue.

Thanks,
Tarkeshwar


Issue in Postgres Client 14.9 with OpenSSL 3.2.0

2024-01-31 Thread David G. Johnston
On Wednesday, January 31, 2024, M Tarkeshwar Rao <
m.tarkeshwar@ericsson.com> wrote:
>
> We also find similar type of issues on internet and one link from postgres
> website as well.
>
>
>
> https://www.postgresql.org/message-id/cx9su44gh3p4.17x6zzuj5d...@neon.tech
>
> NOTE: When compiles postgreClient with openSSL version ???, then
> secure connection working properly.
>
>
>
> Kindly suggest on which release and on what version (14.x/16.x) we will
> get this fix. Also is there any workaround or formal patch which can be
> applied on 14.9 to fix this issue.
>
>
>
Next weeks releases (14.11; 16.2) include the patch.

https://www.postgresql.org/message-id/flat/E1r81yU-007giA-5z%40gemulon.
postgresql.org

David J.


Re: Postgresql BUG / Help Needed

2024-01-31 Thread Laurenz Albe
On Thu, 2024-02-01 at 00:48 -0500, Johnathan Tiamoh wrote:
> I am having the  following issue below.  Please, I need help to fix it.
> 
> ERROR:  could not access status of transaction 756525298
> Could not open file "pg_xact/02C8": No such file or directory.

That is data corruption, and you should restore your backup.

Yours,
Laurenz Albe




support fix query_id for temp table

2024-01-31 Thread ma lz
session 1:
create temp table ttt ( a int );
insert into ttt values(3); -- query_id is XXX  from 
pg_stat_activity


session 2:
create temp table ttt ( a int );
insert into ttt values(3);-- query_id is YYY  from 
pg_stat_activity



I know temp table has different oid, so query_id is different, is there a way 
to use table name for temp table  instead of oid?