pgbouncer configuration

[Chris Stephens]

I'm trying to run pgbouncer but am having trouble with what looks like a
very simple configuration.

centos 7
postgres 12
pgbouncer 1.15

we are already using pam for database auth. pgbouncer was compiled with
--with-pam. there is a /etc/pam.d/pgbouncer config file copied from the one
currently being used for postgres auth.

i can list the whole config file if needed but i get the following when
trying to start pgbouncer up:

[postgres@lsst-pgsql02 ~]$ pgbouncer  -d /etc/pgbouncer/pgbouncer.ini
2021-04-27 19:37:34.256 CDT [10653] ERROR invalid value "pam" for parameter
auth_type in configuration (/etc/pgbouncer/pgbouncer.ini:118)
2021-04-27 19:37:34.256 CDT [10653] FATAL cannot load config file

[postgres@lsst-pgsql02 pgbouncer]$ egrep "auth_type" pgbouncer.ini
auth_type = pam

any ideas?

Re: very long secondary->primary switch time

[Tom Lane]

Tomas Pospisek  writes:
> I maintain a postgresql cluster that does failover via patroni. The 
> problem is that after a failover happens it takes the secondary too long 
> (that is about 35min) to come up and answer queries. The log of the 
> secondary looks like this:

> 04:00:29.777 [9679] LOG:  received promote request
> 04:00:29.780 [9693] FATAL:  terminating walreceiver process due to 
> administrator command
> 04:00:29.780 [9679] LOG:  invalid record length at 320/B95A1EE0: wanted 
> 24, got 0
> 04:00:29.783 [9679] LOG:  redo done at 320/B95A1EA8
> 04:00:29.783 [9679] LOG:  last completed transaction was at log time 
> 2021-03-03 03:57:46.466342+01

> 04:35:00.982 [9679] LOG:  selected new timeline ID: 15
> 04:35:01.404 [9679] LOG:  archive recovery complete
> 04:35:02.337 [9662] LOG:  database system is ready to accept connections

> The cluster is "fairly large" with thousands of DBs (sic!) and ~1TB of data.

Hm.  WAL replay is already done at the "redo done" entry.  There is a
checkpoint after that, I believe, and there may be some effort to search
for dead files as well.  Still, if your I/O subsystem is better than
a wet noodle, 35 minutes is a long time to finish that.

One thing I'm not sure about is whether we try to do the checkpoint
at maximum speed.  If you have set GUC options to throttle checkpoint
I/O hard, that could perhaps explain this.

You could possibly learn more by strace'ing the startup process to
see what it's doing.

Also, what PG version is that exactly?

regards, tom lane

Re: -1/0 virtualtransaction

[Vijaykumar Jain]

Hi,

I am just trying to jump in, but ignore if not relevant.

when you said*Eventually this results in an "out of shared memory"
error  *

Can you rule out the below two scenarios (wrt /dev/shm too low in docker or
query requesting for too many locks either due to parallellism/partition
involved)
There have been multiple cases of out of shared memory i have read earlier
for due to above.

PostgreSQL: You might need to increase max_locks_per_transaction
(cybertec-postgresql.com)

PostgreSQL at low level: stay curious! · Erthalion's blog


also, is this repeatable (given you mention it happens and eventually lead
to "out of shared memory")

I may be missing something, but i do not see a PID even though it has a
lock granted on a page, was the process terminated explicitly or
implicitly. ( and an orphan lingering ? )
ps auwwxx | grep postgres

I took the below from "src/test/regress/sql/tidscan.sql"  to simulate
SIReadLock with an orphan process (by killing the process), but it gets
reaped fine for me :(

postgres=# \d tidscan
  Table "public.tidscan"
 Column |  Type   | Collation | Nullable | Default
+-+---+--+-
 id | integer |   |  |

postgres=# INSERT INTO tidscan VALUES (1), (2), (3);

postgres=# BEGIN ISOLATION LEVEL SERIALIZABLE;
BEGIN
postgres=*# SELECT * FROM tidscan WHERE ctid = '(0,1)';
 id

  1
(1 row)

postgres=*# -- locktype should be 'tuple'
SELECT locktype, mode FROM pg_locks WHERE pid = pg_backend_pid() AND mode =
'SIReadLock';
 locktype |mode
--+
 tuple| SIReadLock
(1 row)

postgres=*# -- locktype should be 'tuple'
SELECT pid, locktype, mode FROM pg_locks WHERE mode = 'SIReadLock';
 pid  | locktype |mode
--+--+
 2831 | tuple| SIReadLock
(1 row)

i thought one could attach a gdb or strace to the pid to figure out what it
did before crashing.

As always, I have little knowledge on postgresql, feel free to ignore if
nothing relevant.

Thanks,
Vijay



On Tue, 27 Apr 2021 at 19:55, Mike Beachy  wrote:

> Hi Laurenz -
>
> On Tue, Apr 27, 2021 at 2:56 AM Laurenz Albe 
> wrote:
>
>> Not sure, but do you see prepared transactions in "pg_prepared_xacts"?
>>
>
> No, the -1 in the virtualtransaction (
> https://www.postgresql.org/docs/11/view-pg-locks.html) for
> pg_prepared_xacts was another clue I saw! But, it seems more or less a dead
> end as I have nothing in pg_prepared_xacts.
>
> Thanks for the idea, though.
>
> I still need to put more effort into Tom's idea about SIReadLock hanging
> out after the transaction, but some evidence pointing in this direction is
> that I've reduced the number of db connections and found that the '-1/0'
> locks will eventually go away! I interpret this as the db needing to find
> time when no overlapping read/write transactions are present. This doesn't
> seem completely correct, as I don't have any long lived transactions
> running while these locks are hanging out. Confusion still remains, for
> sure.
>
> Mike

very long secondary->primary switch time

[Tomas Pospisek]

Hello all,

I maintain a postgresql cluster that does failover via patroni. The 
problem is that after a failover happens it takes the secondary too long 
(that is about 35min) to come up and answer queries. The log of the 
secondary looks like this:



04:00:29.777 [9679] LOG:  received promote request
04:00:29.780 [9693] FATAL:  terminating walreceiver process due to 
administrator command
04:00:29.780 [9679] LOG:  invalid record length at 320/B95A1EE0: wanted 
24, got 0

04:00:29.783 [9679] LOG:  redo done at 320/B95A1EA8
04:00:29.783 [9679] LOG:  last completed transaction was at log time 
2021-03-03 03:57:46.466342+01


04:35:00.982 [9679] LOG:  selected new timeline ID: 15
04:35:01.404 [9679] LOG:  archive recovery complete
04:35:02.337 [9662] LOG:  database system is ready to accept connections

The cluster is "fairly large" with thousands of DBs (sic!) and ~1TB of data.

I would like to shorten the failover/startup time drastically. Why does 
it take the secondary that much time to switch to the primary state? 
There are no logs between 04:00 and 04:35. What is postgresql doing 
during those 35min?


I am *guessing* that postgresql *might* be doing some consistency check 
or replaying the WAL (max_wal_size: 16 GB, wal_keep_segments: 100). I am 
also *guessing* that startup time *might* have to do with the size of 
the data (~1T) or/and with the numbers of DBs (thousands). If that would 
be the case, then splitting the cluster into multiple clusters should 
allow for faster startup times?


I have tried to duckduck why the secondary takes that much time to 
switch to primary mode, but have failed to find information that would 
enlighten me. So any pointers to information, hints or help are very 
wellcome.


Thanks & greets,
*t

Re: Approach to creating users in Database

[Stephen Frost]

Greetings,

* Sanjay Minni (sanjay.mi...@gmail.com) wrote:
> what is the usual approach in creating users / role in a postgresql
> database serving as the data repository to a hosted multi-tenanted
> application with a large number of users.
> 
> the 2 approaches I can think of is
> A. The user stays and is controlled only in the application level, the
> application in turn connects with the postgresql database as a single user
> name. Any userid to be stored as part of the data is injected by the
> application
> B. Each user is also created in the postgresql database with grant or
> required rights to the tables.
> 
> what is the usual practice ?
> I am unable to see any benefit in (B) above - i.e. recreating each user in
> the postgres database

Both approaches are used pretty commonly.  The advantages of B include:

- The permissions can be managed using GRANT/REVOKE at the database
  level and you remove the risk, for the most part, of things like SQL
  injection attacks because the user you're connected to the database as
  has only the rights that they should have, unlike in approach A where
  the user connected to the database has rights to basically everything.

- Auditing is able to be done of who did what using the database rather
  than having to trust to application logs and auditing.

There's other up-sides and down-sides, of course.

Thanks,

Stephen


signature.asc
Description: PGP signature

Re: -1/0 virtualtransaction

[Mike Beachy]

Hi Laurenz -

On Tue, Apr 27, 2021 at 2:56 AM Laurenz Albe 
wrote:

> Not sure, but do you see prepared transactions in "pg_prepared_xacts"?
>

No, the -1 in the virtualtransaction (
https://www.postgresql.org/docs/11/view-pg-locks.html) for
pg_prepared_xacts was another clue I saw! But, it seems more or less a dead
end as I have nothing in pg_prepared_xacts.

Thanks for the idea, though.

I still need to put more effort into Tom's idea about SIReadLock hanging
out after the transaction, but some evidence pointing in this direction is
that I've reduced the number of db connections and found that the '-1/0'
locks will eventually go away! I interpret this as the db needing to find
time when no overlapping read/write transactions are present. This doesn't
seem completely correct, as I don't have any long lived transactions
running while these locks are hanging out. Confusion still remains, for
sure.

Mike

[UPDATE 1] How to Easily Set Up a Full-Featured Linux Mail Server on Ubuntu 18.04.5 LTS with iRedMail 1.4.0

[Turritopsis Dohrnii Teo En Ming]

Subject: [UPDATE 1] How to Easily Set Up a Full-Featured Linux Mail
Server on Ubuntu 18.04.5 LTS with iRedMail 1.4.0

Good day from Singapore,

I followed linuxbabe.com's Xiao Guoan's guide and successfully setup a
full featured Linux mail server on Ubuntu 18.04.5 LTS with IRedMail
1.4.0.

Author: Mr. Turritopsis Dohrnii Teo En Ming (TARGETED INDIVIDUAL)
Country: Singapore
Date: 25 April 2021 Sunday

Type of Publication: PDF Manual
Document Version: 20210425.01 (1st release)

***IMPORTANT NOTICE*** Please note that Turritopsis Dohrnii Teo En
Ming’s guide is based on Xiao Guoan’s guide at linuxbabe.com.

Reference Guide Used by Teo En Ming: How to Easily Set Up a
Full-Featured Mail Server on Ubuntu 18.04 with iRedMail
Link: https://www.linuxbabe.com/mail-server/ubuntu-18-04-iredmail-email-server
Original Author: Xiao Guoan

The following is a list of open-source software that will be
automatically installed and configured by iRedMail.

• Postfix SMTP server
• Dovecot IMAP server
• Nginx web server to serve the admin panel and webmail
• OpenLDAP, MySQL/MariaDB, or PostgreSQL for storing user information
• Amavised-new for DKIM signing and verification
• SpamAssassin for anti-spam
• ClamAV for anti-virus
• Roundcube webmail
• SOGo groupware, providing webmail, calendar (CalDAV), contacts
(CardDAV), tasks and ActiveSync services.
• Fail2ban for protecting SSH
• mlmmj mailing list manager
• Netdata server monitoring
• iRedAPD Postfix policy server for greylisting

In addition, you need to add MX, A and TXT records to your ISC BIND
DNS domain name server.

Redundant Download Links for Teo En Ming's PDF Manual:

[1] 
https://drive.google.com/file/d/1un8sLLmNSMIt7V6blWCvJEgwGvxMbd4B/view?usp=sharing

[2] 
https://drive.google.com/file/d/1i0vY7kfYkobu563qoI3_qCZg7G7BFoYR/view?usp=sharing

[3] 
https://drive.google.com/file/d/1U9MFN1EklLbA8TMweLV5ntiSJuBBVkpQ/view?usp=sharing

[4] https://www.docdroid.net/dW70KtS/iredmail-setup-1st-release-pdf

[5] 
https://www.mediafire.com/file/evar7j28knqyoj6/IRedMail+Setup+1st+Release.pdf/file

[6] https://www.scribd.com/document/504932780/IRedMail-Setup-1st-Release

Mr. Turritopsis Dohrnii Teo En Ming, 43 years old as of 27 April 2021,
is a TARGETED INDIVIDUAL living in Singapore. He is an IT Consultant
with a System Integrator (SI)/computer firm in Singapore. He is an IT
enthusiast.







-BEGIN EMAIL SIGNATURE-

The Gospel for all Targeted Individuals (TIs):

[The New York Times] Microwave Weapons Are Prime Suspect in Ills of
U.S. Embassy Workers

Link:
https://www.nytimes.com/2018/09/01/science/sonic-attack-cuba-microwave.html



Singaporean Targeted Individual Mr. Turritopsis Dohrnii Teo En Ming's
Academic Qualifications as at 14 Feb 2019 and refugee seeking attempts
at the United Nations Refugee Agency Bangkok (21 Mar 2017), in Taiwan
(5 Aug 2019) and Australia (25 Dec 2019 to 9 Jan 2020):

[1] https://tdtemcerts.wordpress.com/

[2] https://tdtemcerts.blogspot.sg/

[3] https://www.scribd.com/user/270125049/Teo-En-Ming

-END EMAIL SIGNATURE-

Re: DB size

[luis . roberto]

- Mensagem original -
> De: "Magnus Hagander" 
> Para: "Laurenz Albe" 
> Cc: "Alvaro Herrera" , "luis.roberto" 
> , "pgsql-general"
> 
> Enviadas: Terça-feira, 27 de abril de 2021 4:05:42
> Assunto: Re: DB size

> Yeah, you want to use pg_relation_filenode(oid) rather than looking
> directly at relfilenode.
> 
> When compared to the filesystem, it's probably even easier to use
> pg_relation_filepath(oid).
> 
> --
> Magnus Hagander
> Me: https://www.hagander.net/
> Work: https://www.redpill-linpro.com/

Thanks all! 

Looks like pg_orphaned is what I need! I'll use pg_relation_filepath too to 
double check.



Luis R. Weck 

Approach to creating users in Database

[Sanjay Minni]

Hi,

what is the usual approach in creating users / role in a postgresql
database serving as the data repository to a hosted multi-tenanted
application with a large number of users.

the 2 approaches I can think of is
A. The user stays and is controlled only in the application level, the
application in turn connects with the postgresql database as a single user
name. Any userid to be stored as part of the data is injected by the
application
B. Each user is also created in the postgresql database with grant or
required rights to the tables.

what is the usual practice ?
I am unable to see any benefit in (B) above - i.e. recreating each user in
the postgres database

So what am i missing. A change in approach later can mean a huge rework
later

with warm regards
Sanjay Minni
+91-9900-902902
http://planage.com
https://www.linkedin.com/in/sanjayminni/

Streaming replica failure

[Aleš Zelený]

Hello,

we are using PostgreSQL 12.4 on CentOS 7. The hot standby failed:

2021-04-24 09:19:27 CEST [20956]: [747-1] user=,db=,host=,app= LOG:
 recovery restart point at 3D8C/352B4CE8
2021-04-24 09:19:27 CEST [20956]: [748-1] user=,db=,host=,app= DETAIL:
 Last completed transaction was at log time 2021-04-24 09:19:27.221313+02.
2021-04-24 09:20:57 CEST [20956]: [749-1] user=,db=,host=,app= LOG:
 restartpoint starting: time
2021-04-24 09:24:27 CEST [20956]: [750-1] user=,db=,host=,app= LOG:
 restartpoint complete: wrote 171233 buffers (13.4%); 0 WAL file(s) added,
68 removed, 0 recycled; write
=209.663 s, sync=0.012 s, total=209.963 s; sync files=283, longest=0.001 s,
average=0.000 s; distance=684762 kB, estimate=684762 kB
2021-04-24 09:24:27 CEST [20956]: [751-1] user=,db=,host=,app= LOG:
 recovery restart point at 3D8C/5EF6B858
2021-04-24 09:24:27 CEST [20956]: [752-1] user=,db=,host=,app= DETAIL:
 Last completed transaction was at log time 2021-04-24 09:24:27.288115+02.
2021-04-24 09:25:15 CEST [20955]: [11-1] user=,db=,host=,app= WARNING:
 page 366603 of relation base/20955/10143636 is uninitialized
2021-04-24 09:25:15 CEST [20955]: [12-1] user=,db=,host=,app= CONTEXT:  WAL
redo at 3D8C/D79F6500 for Heap2/VISIBLE: cutoff xid 3806260577 flags 0x01
2021-04-24 09:25:15 CEST [20955]: [13-1] user=,db=,host=,app= PANIC:  WAL
contains references to invalid pages
2021-04-24 09:25:15 CEST [20955]: [14-1] user=,db=,host=,app= CONTEXT:  WAL
redo at 3D8C/D79F6500 for Heap2/VISIBLE: cutoff xid 3806260577 flags 0x01
2021-04-24 09:25:16 CEST [20953]: [11-1] user=,db=,host=,app= LOG:  startup
process (PID 20955) was terminated by signal 6: Neúspěšně ukončen (SIGABRT)
2021-04-24 09:25:16 CEST [20953]: [12-1] user=,db=,host=,app= LOG:
 terminating any other active server processes

The relation base/20955/10143636 is a standard table.

Version details:
postgres=# select version();
 version

-
 PostgreSQL 12.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
20150623 (Red Hat 4.8.5-39), 64-bit
(1 row)

Settings:
postgres=# select name, setting, category from pg_settings where category =
'Write-Ahead Log / Settings';
  name  |  setting  |  category
+---+
 commit_delay   | 0 | Write-Ahead Log / Settings
 commit_siblings| 5 | Write-Ahead Log / Settings
 fsync  | on| Write-Ahead Log / Settings
 full_page_writes   | on| Write-Ahead Log / Settings
 synchronous_commit | on| Write-Ahead Log / Settings
 wal_buffers| 2048  | Write-Ahead Log / Settings
 wal_compression| off   | Write-Ahead Log / Settings
 wal_init_zero  | on| Write-Ahead Log / Settings
 wal_level  | logical   | Write-Ahead Log / Settings
 wal_log_hints  | off   | Write-Ahead Log / Settings
 wal_recycle| on| Write-Ahead Log / Settings
 wal_sync_method| fdatasync | Write-Ahead Log / Settings
 wal_writer_delay   | 200   | Write-Ahead Log / Settings
 wal_writer_flush_after | 128   | Write-Ahead Log / Settings
(14 rows)

pg_waldump output:
-bash-4.2$ /usr/pgsql-12/bin/pg_waldump 00013D8C00D7 2>&1 | tail
rmgr: Btree   len (rec/tot): 64/64, tx: 3812802559, lsn:
3D8C/D7CF5A98, prev 3D8C/D7CF5A58, desc: INSERT_LEAF off 360, blkref #0:
rel 1663/20955/11280092 blk 19
509
rmgr: Transaction len (rec/tot): 46/46, tx: 3812802557, lsn:
3D8C/D7CF5AD8, prev 3D8C/D7CF5A98, desc: COMMIT 2021-04-24 09:25:16.160687
CEST
rmgr: Heaplen (rec/tot):159/   159, tx: 3812802559, lsn:
3D8C/D7CF5B08, prev 3D8C/D7CF5AD8, desc: INSERT off 8 flags 0x08, blkref
#0: rel 1663/20955/11280066 bl
k 165603
rmgr: Btree   len (rec/tot): 64/64, tx: 3812802559, lsn:
3D8C/D7CF5BA8, prev 3D8C/D7CF5B08, desc: INSERT_LEAF off 317, blkref #0:
rel 1663/20955/11280073 blk 15
340
rmgr: Btree   len (rec/tot): 64/64, tx: 3812802559, lsn:
3D8C/D7CF5BE8, prev 3D8C/D7CF5BA8, desc: INSERT_LEAF off 130, blkref #0:
rel 1663/20955/11280091 blk 22
003
rmgr: Heaplen (rec/tot): 80/80, tx: 3812802559, lsn:
3D8C/D7CF5C28, prev 3D8C/D7CF5BE8, desc: INSERT off 61 flags 0x08, blkref
#0: rel 1663/20955/11280082 b
lk 38917
rmgr: Btree   len (rec/tot): 64/64, tx: 3812802559, lsn:
3D8C/D7CF5C78, prev 3D8C/D7CF5C28, desc: INSERT_LEAF off 73, blkref #0: rel
1663/20955/11280088 blk 421
25
rmgr: Btree   len (rec/tot): 64/64, tx: 3812802559, lsn:
3D8C/D7CF5CB8, prev 3D8C/D7CF5C78, desc: INSERT_LEAF off 361, blkref #0:
rel 1663/20955/11280092 blk 19
509
rmgr: Transaction len (rec/tot): 46/46, tx: 3812802559, lsn:
3D8C/D7CF5CF8, prev 3D8C/D7CF5CB8, desc: COMMIT 2021-04-24 09:25:16.161036
CEST
pg_waldump: fatal: 

Re: Temporary files usage in explain

[Matteo Bonardi]

Okay, I had a misconception of the buffers option.
As I reread the documentation, I realized how stupid the question was.

I confirm that with the buffers option I now see:

Buffers: shared hit=9617011 read=1328356 dirtied=793 written=397, temp
read=2996659 written=5956399


Thank you both,
Matteo


Il giorno mar 27 apr 2021 alle ore 11:38 David Rowley 
ha scritto:

> On Tue, 27 Apr 2021 at 21:15, Matteo Bonardi  wrote:
> > Explain plan: https://explain.depesz.com/s/BXGT
> >
> > Usually I'm looking for "external merge Disk" to see temp files usage
> but, in this case, the only reference to that is 299,368kB in the last but
> one node of explain.
> > Can anyone help me understand where the 45GB are in that explain?
>
> If you do EXPLAIN (ANALYZE, BUFFERS) you might find that the recursive
> CTE is writing the rest of them out.  Something like "temp written =
> ", likely around 5 million or so, I imagine.
>
> David
>

Re: Temporary files usage in explain

[David Rowley]

On Tue, 27 Apr 2021 at 21:15, Matteo Bonardi  wrote:
> Explain plan: https://explain.depesz.com/s/BXGT
>
> Usually I'm looking for "external merge Disk" to see temp files usage but, in 
> this case, the only reference to that is 299,368kB in the last but one node 
> of explain.
> Can anyone help me understand where the 45GB are in that explain?

If you do EXPLAIN (ANALYZE, BUFFERS) you might find that the recursive
CTE is writing the rest of them out.  Something like "temp written =
", likely around 5 million or so, I imagine.

David

Re: Temporary files usage in explain

[Laurenz Albe]

On Tue, 2021-04-27 at 11:15 +0200, Matteo Bonardi wrote:
> I have a query that creates a large number of temporary files, in this 
> example ~ 45GB.
> Looking at the query plan I can't figure out where temporary files are being 
> generated.
> 
> Explain plan: https://explain.depesz.com/s/BXGT
> 
> Usually I'm looking for "external merge Disk" to see temp files usage but, in 
> this case, the only reference to that is 299,368kB in the last but one node 
> of explain.
> Can anyone help me understand where the 45GB are in that explain?

I see at first glance: Sort Method: external merge Disk: 299,368kB

To see all, use EXPLAIN (ANALYZE, BUFFERS).

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Temporary files usage in explain

[Matteo Bonardi]

Hi everybody,

I have a query that creates a large number of temporary files, in this
example ~ 45GB.
Looking at the query plan I can't figure out where temporary files are
being generated.

Explain plan: https://explain.depesz.com/s/BXGT

Usually I'm looking for "external merge Disk" to see temp files usage but,
in this case, the only reference to that is 299,368kB in the last but one
node of explain.
Can anyone help me understand where the 45GB are in that explain?

Thanks,
Matteo

Re: About to know the info of foreign table reference used in any of call ,views,functions

[Charles Clavadetscher]

Hi

On 2021-04-27 09:15, Durgamahesh Manne wrote:

Hi Team

By using the system view and function

"I need to get the info of foreign table reference used in any of call
,views,functions"

I found info of views and functions and sprocs that are executed
frequently through application using pg_stat_user_functions view

Please help  for the info i need

Thanks & Regards
Durgamahesh Manne


I am not sure how reliable that is, but as you mention you could query 
the system catalogs.


The definition of views and the code of functions are stored in pg_views 
and pg_proc.
So you can check if those contain the name of the tables you are 
interested in.


You can find a list of the foreign tables using:

SELECT relnamespace::regnamespace AS schema_name,
   relname AS table_name
FROM pg_class
WHERE relkind = 'f';

You can then use this information to query views defintions and function 
bodies:


SELECT schemaname,
   viewname
FROM pg_views
WHERE definition ~ '(schema_name\.)?table_name';

SELECT pronamespace::regnamespace,
   proname
FROM pg_proc
WHERE prosrc ~ '(schema_name\.)?table_name';

schema_name and table_name refer to the result of the first query.

If you have overloaded functions you may need to extract more 
information to identify them correctly, such as the list of parameters. 
The documentation is very helpful in this context.


Hope this helps.

Regards
Charles

--
Charles Clavadetscher
Spitzackerstrasse 9
CH - 8057 Zürich

https://www.swisspug.org

++
|     __  ___|
|  /)/  \/   \   |
| ( / ___\)  |
|  \(/ o)  ( o)   )  |
|   \_  (_  )   \ ) _/   |
| \  /\_/\)/ |
|  \/  |
|   _|  ||
|   \|_/ |
||
|Swiss PostgreSQL|
|   Users Group  |
++

About to know the info of foreign table reference used in any of call ,views,functions

[Durgamahesh Manne]

Hi Team

By using the system view and function

"I need to get the info of foreign table reference used in any of call
,views,functions"

I found info of views and functions and sprocs that are executed frequently
through application using pg_stat_user_functions view

Please help  for the info i need


Thanks & Regards
Durgamahesh Manne

Re: DB size

[Magnus Hagander]

On Tue, Apr 27, 2021 at 8:59 AM Laurenz Albe  wrote:
>
> On Mon, 2021-04-26 at 16:45 -0400, Alvaro Herrera wrote:
> > I would guess that there are leftover files because of those crashes you
> > mentioned.  You can probably look for files in the database subdir in
> > the data directory that do not appear in the pg_class.relfilenode
> > listing for the database.
>
> Not all tables have their "relfilenode" set:
>
> SELECT relfilenode FROM pg_class WHERE relname = 'pg_class';
>  relfilenode
> ═
>0
> (1 row)

Yeah, you want to use pg_relation_filenode(oid) rather than looking
directly at relfilenode.

When compared to the filesystem, it's probably even easier to use
pg_relation_filepath(oid).

-- 
 Magnus Hagander
 Me: https://www.hagander.net/
 Work: https://www.redpill-linpro.com/

Re: DB size

[Laurenz Albe]

On Mon, 2021-04-26 at 16:45 -0400, Alvaro Herrera wrote:
> I would guess that there are leftover files because of those crashes you
> mentioned.  You can probably look for files in the database subdir in
> the data directory that do not appear in the pg_class.relfilenode
> listing for the database.

Not all tables have their "relfilenode" set:

SELECT relfilenode FROM pg_class WHERE relname = 'pg_class';
 relfilenode 
═
   0
(1 row)

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: DB size

[Guillaume Lelarge]

Hi,

Le lun. 26 avr. 2021 à 22:59,  a écrit :

>
> - Mensagem original -
> > De: "Alvaro Herrera" 
> > Para: "luis.roberto" 
> > Cc: "pgsql-general" 
> > Enviadas: Segunda-feira, 26 de abril de 2021 17:45:34
> > Assunto: Re: DB size
>
> > I would guess that there are leftover files because of those crashes you
> > mentioned. You can probably look for files in the database subdir in
> > the data directory that do not appear in the pg_class.relfilenode
> > listing for the database.
>
> > --
> > Álvaro Herrera 39°49'30"S 73°17'W
> >  really, I see PHP as like a strange amalgamation of C, Perl,
> Shell
> >  inflex: you know that "amalgam" means "mixture with mercury",
> > more or less, right?
> >  i.e., "deadly poison"
>
>
> Thanks Alvaro!
>
> That's what I read in an old thread, back in the 8.3 days... Can you point
> me in the right direction as to where sould I look and how do I know which
> files exactly are safe to remove?
>
>
pg_orphaned is probably what you need. See
https://github.com/bdrouvot/pg_orphaned for details.


-- 
Guillaume.

Re: -1/0 virtualtransaction

[Laurenz Albe]

On Mon, 2021-04-26 at 17:45 -0400, Mike Beachy wrote:
> Does anyone have any pointers on what a virtualtransaction of '-1/0' means?
> 
> I'm using SSI and an example is 
> 
>  locktype | database | relation |  page   | tuple | virtualxid | 
> transactionid | classid | objid | objsubid | virtualtransaction | pid |
> mode| granted | fastpath
> --+--+--+-+---++---+-+---+--++-++-+--
>  page |16384 |   468238 |   19216 |   ||  
>  | |   |  | -1/0   | | SIReadLock | t 
>   | f
> 
>  This is incredibly hard to search for. 
> 
> I see these for page, tuple and (perhaps a clue?) one relation, and I'm 
> seeing the page and tuple locks accumulate over time.
>  Eventually this results in an "out of shared memory" error.

Not sure, but do you see prepared transactions in "pg_prepared_xacts"?

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com