Re: CREATE/REFRESH MATERIALIZED VIEW planner difference?

ok, so Tom ran on pg14 it seems. :) On Wed, 2 Jun 2021 at 00:53, Thomas Munro wrote: > On Wed, Jun 2, 2021 at 7:15 AM Vijaykumar Jain > wrote: > > i only get workers to create mv, but refresh mv plan does not use > workers for the same conf params. > > Yeah, this change

Re: Is consistent (deterministic) ordering possible in our case?

PostgreSQL: Documentation: 13: 9.27. System Administration Functions I am not an expert here, but if you can make use of pg_try_advisory_lock to ensure the same rows in the same function are not modified by two

Re: max_connections

com/t5/azure-database-for-postgresql/analyzing-the-limits-of-connection-scalability-in-postgres/ba-p/1757266> <https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/analyzing-the-limits-of-connection-scalability-in-postgres/ba-p/1757266> On Sun, 30 May 2021 at 20:19, Vij

Re: max_connections

at 00:14, Vijaykumar Jain < vijaykumarjain.git...@gmail.com> wrote: > ok, running too many connections without recycling has an overhead. > no wonder pgbouncer is all over as a sidecar :) > > In case anyone is interested, this blog is a great read. > Analyzing the Limits o

Re: EXPLAIN (ANALYZE, BUFFERS) - puzzling numbers for a simple query.

how is cost calculated? postgres/costsize.c at master · postgres/postgres (github.com) row estimation PostgreSQL: Documentation: 13: 70.1. Row Estimation Examples

Re: strange behavior of WAL files

I have not seen this, so cannot comment, but when I am trying to simulate i do not see issues. One thing to note, It seems your wal is on nfs mount , can you rule out any nfs errors if it is nfs. On Fri, Jun 4, 2021, 6:24 PM Atul Kumar wrote: > Hi, > > > archive_command is 'cp %p /nfslogs/wal/%

Re: strange behavior of WAL files

20/08/28/who-is-spending-wal-crazily/> to see what is in the WAL, and if you see any issues. On Fri, 4 Jun 2021 at 18:45, Vijaykumar Jain < vijaykumarjain.git...@gmail.com> wrote: > I have not seen this, so cannot comment, but when I am trying to simulate > i do not see issues. >

Re: autovacuum on pg_catalog tables

ok, what i am sharing, *DO NOT DO IT.* it is just to answer why it is not working :) PostgreSQL: Documentation: 13: 19.17. Developer Options postgres=# alter table pg_catalog.pg_largeobject_metadata set (AUTOVACUUM_VACUUM_CO

Re: strange behavior of WAL files

it gets cleaned up for me. turn archiving on, simulate success using /bin/true turn archiving off, simulate success using /bin/false generate wals by some DMLS. postgres@go:~/pgsql/data/pg_wal$ grep wal_size ../postgresql.conf max_wal_size = 100MB min_wal_size = 80MB postgres@go:~/pgsql/data/pg_

Re: Ideas for building a system that parses medical research publications/articles

http://tika.apache.org/ To get started with collecting doc metadata. It looks this tool can help you started. postgres does support fuzzy text search, so I do think dumping meta data /abstract in postgresql and then using trigram tsearch etc like extensions it should work well for a POC. this bein

Re: Symbolic link breaks for postgresql.auto.conf

Yes, I learnt it the hard way as well :) I made changes via the alter system, and did a pg_restore, and the changes were lost :) use the alter system for ad hoc changes, but make sure those changes are added back to the postgresql.conf file immediately (or however the main conf file is managed).

bottom / top posting

I hear a lot of suggestions wrt bottom / top posting. only when i read this, PostgreSQL - general - Top posting | Threaded View (postgresql-archive.org) I got a feeling it sounded rude to the top post, despite me not even ha

Re: JDBC error: Unexpected packet type: 25

" java.io.IOException: Unexpected packet type: 25" char 25 is EM. ( End of Medium) I do not see it being part of the wire protocol, as for the same reason, not in pgjdbc handling i think. PostgreSQL: Documentation: 13: 52.7. Message Formats

Re: PostgreSQL replication lag - Suggestions and questions

> My current architecture is: master (AZ1) --> read slave (AZ2) --> hot-standby (AZ2) - They are all using streaming replication. I am not sure of the difference between read replica and a hot standby. At least with later versions hot standby allows read queries. I mean unless you do not want quer

Re: order by

> Any hint? you can run an explain analyze to check what is going on, when you provide a table in query in the order by clause, it is ordered by cols of that table in that order. create table t(id int, value int); postgres=# explain (analyze,verbose) select * from t order by t;

Re: Implicit table removal from logical replication publication

Wow, the drop table silently removes entry from publication without any logs. I could not find any monitoring view to help me figure out if the publication is broken due to ddl change. pg_stat_replication on publisher, and pg_stat_subscription on subscriber only help with lsn based lag. unless thi

Re: Implicit table removal from logical replication publication

pg_subscription_rel pg_publication_rel have the relation part of the publication concerned. OP has an issue to figure out if publication has a list of tables not in sync in subscription and has subscription broken. there may be ways to query tables on subscriber dbs via dblink or fdw, but there is

Re: How to generate file from postgres data

test=# create table t(id int, value text); CREATE TABLE test=# insert into t select x, x::text from generate_series(1, 1000) x; INSERT 0 1000 test=# COPY (select * from t where id < 50 order by id desc) TO '/tmp/report.csv' DELIMITER ',' CSV HEADER; copy out the results via a view or sql d

Re: How to generate file from postgres data

correction, i kind of recollected this as my own problem of a client wanting 100k rows worth data downloadable. we finally decided with csv dump and upload to object store, from where the client would download the file. all other options like web ui with pagination etc were resulting in memory iss

immutable function querying table for partitioning

hi, I was playing around with a setup of having a lookup table for partitioning. Basically, I wanted to be able to rebalance partitions based on my lookup table. -- create a lookup and assign shard nos to ids test=# create table pt(id int, sh int); CREATE TABLE test=# insert into pt select x, 1

Re: immutable function querying table for partitioning

On Tue, 15 Jun 2021 at 18:21, David G. Johnston wrote: > You probably avoid the complications by doing the above, but the amount of bloat you are causing seems excessive. > > I’d suggest an approach where you use the table data to build DDL in a form that does adhere to the limitations described

Re: clear cache in postgresql

On Wed, 16 Jun 2021 at 12:09, Julien Rouhaud wrote: > > On Wed, Jun 16, 2021 at 12:02:52PM +0530, Atul Kumar wrote: > > > > Sometimes I run a Postgres query it takes 30 seconds. Then, I > > immediately run the same query and it takes 2 seconds. It appears that > > Postgres has some sort of caching

Re: Streaming replication: PANIC on tertiary when secondary promoted

What is your recovery_target_timeline set to on replicas ? I just did a primary -> replica -> cascading replica setup. and then promoted replica as new primary. cascading replica was working fine, no restarts required. for me recovery_target_timeline was set to 'latest' i have pg14beta installed

compute_query_id

hi, I noticed this new param compute_query_id in pg14beta. it is interesting as I was long wanting to identify a query with a unique id like we have for http requests etc so that we can trace the query all the way to shards via FDW etc. but i cannot see them in the logs even after setting compute

Re: compute_query_id

On Thu, 17 Jun 2021 at 20:20, Julien Rouhaud wrote: > > On Thu, Jun 17, 2021 at 08:09:54PM +0530, Vijaykumar Jain wrote: > > how is the compute_query_id actually calculated? > > > why does it show 0 in logs for random sql queries. > > log_line_prefix = '%Q :'

Re: pgTAP installation

On Sun, 20 Jun 2021 at 17:18, Ray O'Donnell wrote: > > > gfc_booking6_dev=# select * from noplan(); > ERROR: function noplan() does not exist > LINE 1: select * from noplan(); >^ > HINT: No function matches the given name and argument types. You might > need to add expli

Re: pgTAP installation

test psql (14beta1) Type "help" for help. test=# create extension pgtap; CREATE EXTENSION test=# SELECT * FROM no_plan(); no_plan - (0 rows) On Sun, 20 Jun 2021 at 18:36, Vijaykumar Jain < vijaykumarjain.git...@gmail.com> wrote: > > I think you need to load the

Re: pgTAP installation

> > > This was a wrong suggestion. sorry. > i'll try to reproduce what resulted in functions not getting loaded in > your case. > > ok i guess you have a typo there. the function name is *no_plan()*, but you called *noplan() * tt=# select * from noplan(); ERROR: function noplan() does not exist

Re: pg_dump/pg_restore vs default_transaction_read_only under PG 13.2

On Sun, 20 Jun 2021 at 22:17, Karsten Hilbert wrote: > Dear all, > > I am testing the pg_restore of a database with > default_transaction_read_only=on. > > It would seem the restore script lacks a > > SET default_transaction_read_only TO 'off'; > > in the setup section after re-connecting

Re: pg_dump/pg_restore vs default_transaction_read_only under PG 13.2

On Sun, 20 Jun 2021 at 22:49, Vijaykumar Jain < vijaykumarjain.git...@gmail.com> wrote: > > > On Sun, 20 Jun 2021 at 22:17, Karsten Hilbert > wrote: > >> Dear all, >> >> I am testing the pg_restore of a database with >> default_transaction_read_on

Re: pg_dump/pg_restore vs default_transaction_read_only under PG 13.2

> > Hm. It's intentional that we reconnect after applying the database > properties, so that they are in effect during the restore. It seems > likely that failing to do so could result in misbehaviors. > > Hence, the only way to make this scenario work would be for the > restore script to explici

Re: Postgres PANIC when it could not open file in pg_logical/snapshots directory

On Tue, 22 Jun 2021 at 13:32, Mike Yeap wrote: > Hi all, > > I have a Postgres version 11.11 configured with both physical replication > slots (for repmgr) as well as some logical replication slots (for AWS > Database Migration Service (DMS)). This morning, the server went panic with > the follow

Re: second CTE kills perf

On Tue, 22 Jun 2021 at 13:50, Nicolas Seinlet wrote: > Hello, > > oversimplified example: > 10 seconds version: > | WITH cte1 AS (SELECT x,y,z FROM table) SELECT row_number() over(),x,y,z > FROM cte1 WHERE x=32; > > 10 minutes version: > | WITH cte1 AS (SELECT x,y,z FROM table), cte2 AS (SELECT r

www.postgresql-archive.org content deleted

Ok, i am not sure if this is the right place to post this, I am seeing a lot of messages from archives, shown as deleted. PostgreSQL - performance - Estimating wal_keep_size | Threaded View (postgresql-archive.org) if i

Re: www.postgresql-archive.org content deleted

On Tue, 22 Jun 2021 at 23:31, Magnus Hagander wrote: This site is not affiliated with the PostgreSQL project in any way, > it's an independent third party. > > The official PostgreSQL archives are on https://www.postgresql.org/list/ > > coola, many thanks. that's good to hear.

Re: www.postgresql-archive.org content deleted

i get it now, thanks., anyways, it does seem to be under serious attack.

Re: Postgres PANIC when it could not open file in pg_logical/snapshots directory

On Tue, 22 Jun 2021 at 14:34, Vijaykumar Jain < vijaykumarjain.git...@gmail.com> wrote: > > On Tue, 22 Jun 2021 at 13:32, Mike Yeap wrote: > >> Hi all, >> >> I have a Postgres version 11.11 configured with both physical replication >> slots (for repmgr) as

Re: Is there something similar like flashback query from Oracle planned for PostgreSQL

On Thu, 24 Jun 2021 at 00:24, Dirk Krautschick < dirk.krautsch...@trivadis.com> wrote: > Hi, > Is there something planned to get a behaviour like Oracle's flashback > query based on the old values > before deleted by vacuum? > > So a feature to recreate old versions of rows if still there? > > Or

Re: Postgres PANIC when it could not open file in pg_logical/snapshots directory

On Thu, Jun 24, 2021, 9:28 AM Mike Yeap wrote: > Hi Vijay, thanks for the tests, it's very helpful. > > Just that, isn't it too extreme when one of the processes having problems > accessing a snap file, it causes all other processes to be terminated? > After all, most of the other processes do no

Re: Logical Replication - Single Destination Table With Multiple Source Tables - How to Handle Structure Changes

On Sun, 4 Jul 2021 at 15:53, Avi Weinberg wrote: > I'm using logical replication to copy data from multiple tables to a > single destination table. At times the structure of the source table needs > to change. However, not all source table will have their structure updated > at the same time.

Re: Logical Replication - Single Destination Table With Multiple Source Tables - How to Handle Structure Changes

On Mon, 5 Jul 2021 at 14:29, Avi Weinberg wrote: > Thanks for the reply, > > > > My question was, what will happen if I have one destination table which > gets data from many source tables. What is the best way to handle changes > in the structure of SOME of the source tables, while other source

Re: How to debug a connection that's "active" but hanging?

On Sat, 10 Jul 2021 at 00:29, Jurrie Overgoor < postgresql-mailingl...@jurr.org> wrote: > Hi everyone, > > We are in the process of upgrading from PostgreSQL 9.6 to 13. When our > database gets created in our regression tests, we run some unit tests > first. We see one of those tests hang. > > It

Re: How to debug a connection that's "active" but hanging?

On Mon, 12 Jul 2021 at 23:16, Tom Lane wrote: > > The backtraces you captured look like the query is not "hung", it's > just computing away. > > He mentioned earlier that the query was hung as 'active' for 8 hours and on. incase this is due to bad plan, @Jurrie Overgoor is it also possible for

Re: Obsolete or dead serverconnections after reboot

> Is there a way to avoid this (without restarting the service after every > reboot). Is this a bug or a normal behavior? > I have less knowledge of windows. https://www.enterprisedb.com/blog/postgresql-shutdown Do you see shutdown/termination messages in the db logs or windows event logs when

Re: Obsolete or dead serverconnections after reboot

etc. That would also mean, all table stats would be off or not updated too? Is that so? Or analyse works fine on tables without a restart? On Wed, Jul 21, 2021, 6:27 PM Vijaykumar Jain < vijaykumarjain.git...@gmail.com> wrote: > > > >> Is there a way to avoid this (without r

Re: Obsolete or dead serverconnections after reboot

On Thu, 22 Jul 2021 at 12:41, WR wrote: > Hello Vijaykumar Jain, > > at first: select pg_stat_reset(); doesn't help, the pg_stat_activity is > the same after it. > one thing, i forgot to mention. After a pg_stat_reset(), I would run, *vacuum analyze* on the dbs, so that sta

Re: regarding sql password auth

> I have just downloaded the postgresql .In the SQLshell i am not able to go > ahead as password is not authenticated . > I am not too well versed with windows installer, but I know during installation it asks for admin username and password. The same cred should work with initial login. https://

Re: pg_restore (fromuser -> touser)

On Sun, 25 Jul 2021 at 21:09, Mayan wrote: > Thanks for your reply. Is this something that I can request as a feature > add? I don't think it should be too much of effort (based on my limited > source code knowledge), but I'm not familiar with the process to request a > feature. > although there

Re: pg-audit extension

On Mon, Jul 26, 2021, 11:54 PM Olagoke Akinyemi wrote: > Hello, > > > Could someone please, give me a quick guide? I am trying to install > pgaudit extension on an existing PostgreSQL instance but I want to place it > on a different this mount. How can i do this? > > Are you trying to save pgaudi

Re: I need another option.

On Wed, Jul 28, 2021, 11:10 AM obi reddy wrote: > Hello Everyone . "c --clean (drop) database objects before recreating", > is there any other option for this. please let me know > You'll have to give more information on what you see and what you want or does not work the way you would want. On

DO like block for for anonymous procedures

Hi, PostgreSQL: Documentation: 13: DO Is it possible to run a DO block for multiple transactions ? I am not sure if i'll be able explain it more verbally, but -- the entire DO block like a function block is a single tx postgres=# do $$ declare

Re: DO like block for for anonymous procedures

please ignore, i overlooked the obvious. truncate table t; TRUNCATE TABLE postgres=# do $$ declare valuelist int[] := ARRAY[1,2,3,4,5,1]; -- purposely inserting duplicate that would rollback everything declare i int; begin for i in select k from unnest(valuelist) p(k) loop insert into t values(i);

Re: Postgres 9.6 to 12.7 pg_upgrade error - terminating connection due to administrator command

On Sat, Jul 31, 2021, 4:00 AM Dhanush D wrote: > I am currently upgrading a Postgres cluster running on a Centos machine > from version 9.6.22 to 12.7. Post the install of 12.7 binaries, I am > running the pg_upgrade command: > > /usr/pgsql-12/bin/pg_upgrade -b /usr/pgsql-9.6/bin/ -B /usr/pgsql-1

Re: postgres vacuum memory limits

On Sun, 1 Aug 2021 at 10:27, Ayub M wrote: > > Hello, when maintenance_work_mem and autovacuum_work_mem are set, my understanding is that the vacuum and autovacuum sessions should be limited to use the memory limits set by these parameters. But I am seeing more memory being used than these limits

Re: postgres vacuum memory limits

On Sun, 1 Aug 2021 at 20:04, Vijaykumar Jain < vijaykumarjain.git...@gmail.com> wrote: > > On Sun, 1 Aug 2021 at 10:27, Ayub M wrote: > > > > Hello, when maintenance_work_mem and autovacuum_work_mem are set, my > understanding is that the vacuum and autovacuum sessions

Re: postgres vacuum memory limits

https://rhaas.blogspot.com/2012/01/linux-memory-reporting.html?m=1 I think this awesome blog will clear a lot of 'understanding of top' output in postgresql context of memory growth.

Re: Lazy View's Column Computing

On Mon, 2 Aug 2021 at 19:53, Tom Lane wrote: > Avi Weinberg writes: > > Is there a way to compute a column in a view only if it is referenced in > the query? I have a view's column that its value is computed by a > function. If in the query that column is not used at all, can Postgres > "skip"

Re: Unexpected block ID found when reading data

On Tue, 3 Aug 2021 at 08:19, Gilar Ginanjar wrote: > Hi, Adrian > > Thanks in advance. > > pdgump command: > pg_dump -U myuser -Fc -Z3 -d mydb > dbdump.backup > > I'm not sure which pg_dump version did i use before, but I used psql 12.5 > to dump and the db version is postgresql 9.6. > > pgrestor

Re: Unexpected block ID found when reading data

On Tue, 3 Aug 2021 at 20:37, Gilar Ginanjar wrote: > I’m not sure which patch version i used to dump, but i was using postgre > 12.5 for pg_dump back then. > > I’m running pg_restore -f dbdump.backup right now, I think it will take > some times because it has a large size (around 9 GB). There are

Re: autovacuum worker started without a worker entry

I am attempting to dive into code using english, not c, if i am misguiding, pls ignore. On Thu, 5 Aug 2021 at 11:38, Luca Ferrari wrote: > Hi all, > I occasionally see the message "WARNING: autovacuum worker started > without a worker entry" in the logs. > From what I can see here > < > https:/

Re: PostgreSQL general set of Questions.

On Mon, 9 Aug 2021 at 12:14, A Z wrote: > 1) Are there free scripts for CREATE TYPE (native type), more advanced, > or sorts of types out there, online, free for commercial > use? With function support, too? Can someone reply with a link or a > suggestion? > > PostgreSQL: Documentation: 13: Cha

Re: Partitioning a table by integer value (preferably in place)

> > > > I have a 400GB joining table (one SMALLINT and the other INTEGER - > Primary Keys on other tables) with 1000 fields on one side and 10M on > the other, so 10,000M (or 10Bn) records all told. > My queries: > > Do you have any explain analyze,buffers results with the existing setup? Does

Re: Getting pg_stat_database data takes significant time

Just taking a shot, as I have seen in some previous issues? Ignore is not relevant. Can you run vacuum on pg_class and check the query again , or do you see pg_class bloated ? The other option would be gdb backtrace I think that would help.

Re: Getting pg_stat_database data takes significant time

On Wed, 11 Aug 2021 at 18:59, hubert depesz lubaczewski wrote: > On Wed, Aug 11, 2021 at 06:52:15PM +0530, Vijaykumar Jain wrote: > > Just taking a shot, as I have seen in some previous issues? Ignore is > not > > relevant. > > > > Can you run vacuum on pg_class a

Re: Getting pg_stat_database data takes significant time

On Wed, 11 Aug 2021 at 19:12, Vijaykumar Jain < vijaykumarjain.git...@gmail.com> wrote: > >> ok my guess here was, since pg_class is updated every now and then with > stats, it might require some lock while adding the data. > so if it were bloated, that would block t

Re: (13.1) pg_basebackups ./. pg_verifybackup

> > > Could some kind sol acknowledge me that this is the correct procedure to > use pg_verifybackup? Thanks in advance > > postgres@db:~/playground/demo$ initdb -D db 2>/dev/null 1>&2 postgres@db:~/playground/demo$ pg_ctl -D db -l logfile start 2>/dev/null 1>&2 postgres@db:~/playground/demo$ psql

Re: (13.1) pg_basebackups ./. pg_verifybackup

btw, you can also use pgbackrest for backups. i'll let docs do the talking, pgBackRest - Reliable PostgreSQL Backup & Restore but it can help you manage incremental and differential and full backups along with parallel support. (so fast and less bandwidth) also a quick on

Re: (13.1) pg_basebackups ./. pg_verifybackup

On Thu, 12 Aug 2021 at 01:48, Matthias Apitz wrote: > This is exactly the point of my question (and I figured it out too): > Where is this explained that «pg_wal.tar.gz file has to uncompressed in > pg_wal dir»? > > indeed, I am not able to find or search relative reference in docs (i never used

Re: Partitioning a table by integer value (preferably in place)

On Fri, 13 Aug 2021 at 14:13, Pól Ua Laoínecháin wrote: > Thanks again for your questions - they gave me pause for thought and I > will try to apply them in future partitioning scenarios. (Unfortunatly > :-) ) there is no magic number of partitions for, say, a given size of > table - otherwise it

Re: Multi-master replication

On Fri, Aug 13, 2021, 5:05 PM Zahir Lalani wrote: > Confidential > > Thx Laura > > So here is the dilemma - everything in the cloud world tends toward > horizontal scaling. We do that with PG using single master and multiple > slaves. But we are write heavy and of course the load on the master is

Re: Partitioning a table by integer value (preferably in place)

On Fri, 13 Aug 2021 at 21:07, Michael Lewis wrote: > On Fri, Aug 13, 2021 at 3:02 AM Vijaykumar Jain < > vijaykumarjain.git...@gmail.com> wrote: > >> ... use binary split for large partitions, to avoid large row movements. >> > > Would you expound on this? >

Re: Single mater replica setup for an existing DB

On Mon, 16 Aug 2021 at 00:47, Digimer wrote: > Hi all, > > Could I get recommendations on how to convert an existing database to a > single-master replica setup? > > I'm running on RHEL 8 (pgsql version 10.17). I'd like to replicate the > existing database to 1 (maybe 2) other hosts, synchron

Re: PostgreSQL Automatic Failover Windows Server

> > > I want to know in detail about PostgreSQL Automatic Failover solutions > without third party tools. > > There is no automatic failover solution provided with core postgresql. (maybe EDB provides in windows but ...) there are third party OSS solutions, dhamaniasad/awesome-postgres: A curated

Re: user creation time for audit

On Mon, 30 Aug 2021 at 14:39, Julien Rouhaud wrote: > > The easy way around that is to track those events yourself with the > rules that suit your needs, which can be done easily using an event > trigger. > Please correct me if I am missing anything, but the doc said, event triggers are not allo

Re: Pg stuck at 100% cpu, for multiple days

On Mon, 30 Aug 2021 at 19:15, hubert depesz lubaczewski wrote: > Hi, > We hit a problem with Pg 12.6 (I know, we should upgrade, but that will > take long time to prepare). > > > The other end of the connection was something in kubernetes, and it no > longer exists. > > related? i was kind of exp

vacuum full

Just keeping it in a separate email, incase this is thrashed down. vacuum full has a lot of problem stories, not just because the db gets locked, but also because it is mostly (mis)used when there are space issues. of course, there are strong warnings in docs and wiki about using a vacuum full, bu

Re: vacuum full

On Mon, 30 Aug 2021 at 23:12, Vijaykumar Jain < vijaykumarjain.git...@gmail.com> wrote: > Just keeping it in a separate email, incase this is thrashed down. > vacuum full has a lot of problem stories, not just because the db gets > locked, but also because it is mostly (mis)used

Re: Grant select for all tables of the 12 schemas of my one db ?

something like this ? do $$ declare sch text; stmt text; begin for sch in select nspname from pg_namespace where nspname not like 'pg\_%' and nspname not like 'information%' loop -- use what you want, filter out rest stmt = 'GRANT USAGE ON SCHEMA ' || sch || ' TO readonlyuser_role'; raise notice

Re: Grant select for all tables of the 12 schemas of my one db ?

On Wed, 13 Oct 2021 at 16:30, hubert depesz lubaczewski wrote: > On Wed, Oct 13, 2021 at 03:33:20PM +0530, Vijaykumar Jain wrote: > > something like this ? > > Like, but not exactly. > > Consider what will happen if you have schema named "whatever something >

Re: Connection queuing by connection pooling libraries

On Tue, 19 Oct 2021 at 22:45, Saurav Sarkar wrote: > Hi All, > > > A basic question on handling large number of concurrent requests on DB. > > I have a cloud service which can get large of requests which will > obviously trigger the db operations. > > Every db will have some max connection limit

Re: Connection queuing by connection pooling libraries

On Tue, 19 Oct 2021 at 23:09, Vijaykumar Jain < vijaykumarjain.git...@gmail.com> wrote: > > On Tue, 19 Oct 2021 at 22:45, Saurav Sarkar > wrote: > >> Hi All, >> >> >> A basic question on handling large number of concurrent requests on DB. >>

Re: Connection queuing by connection pooling libraries

On Tue, 19 Oct 2021 at 23:20, Vijaykumar Jain < vijaykumarjain.git...@gmail.com> wrote: > > On Tue, 19 Oct 2021 at 23:09, Vijaykumar Jain < > vijaykumarjain.git...@gmail.com> wrote: > >> >> On Tue, 19 Oct 2021 at 22:45, Saurav Sarkar >> wrote: >

Re: Can db user change own password?

On Wed, 20 Oct 2021 at 20:52, Adrian Klaver wrote: > On 10/20/21 08:07, Toomas wrote: > > Hi Adrian, > > > > Thank you for your help. The issue was that when user logged into > database his session_user user was set as owner of database automatically. > User had success to change password when se

Re: Need recommendation on PARALLEL INDEX SCAN and PARALLEL APPEND

On Tue, 26 Oct 2021 at 11:39, Vivekk P wrote: > Hi Team, > > Please have a look on the below problem statement and suggest us if there > are any ways to make the planner pick PARALLEL INDEX SCAN and PARALLEL > APPEND > > > 1. We have tried fine-tuning the below parameters with all possible values

Re: Debugging features needed

On Fri, Nov 5, 2021, 4:58 PM Boboc Cristi wrote: > Hello! > I need a feature that would me allow to debug "live" the work of an > application together with a PostgreSQL database. > > I think that if I would be able to define a session variable that is > visible in pg_stat_activity (or in othe

Re: Debugging features needed

On Fri, Nov 5, 2021, 7:46 PM Michael Lewis wrote: > For my purposes, app name isn't long enough so we put a comment at the > start of every SQL that has a unique ID generated in the application. This > ensures that we can tell one connection apart from another even when both > are coming from the

Re: How to confirm the pg_hba.conf service is correctly working

On Thu, 23 Dec 2021 at 15:45, shing dong wrote: > I have tested this feature , only had >> > > host VJ VJ_USER 10.10.10.1/32 md5 > > in the pg_hba.conf file > I may be a bit off , but can you try a couple of things, other than a fresh install, incase you have time to debug more. is it

Re: Query on postgres_fdw extension

On Thu, 20 Jan 2022 at 21:29, Duarte Carreira wrote: > Hello everyone. > > I don't know... realistically what do you guys see as a best/simple > approach? > We implemented a custom sharding (directory sharding with lookup tables) layer of 10 shards, but it was write local, read global. the api w

Re: Subscription stuck at initialize state

On Thu, Feb 3, 2022, 10:32 AM Abhishek Bhola wrote: > So far I figured out that the problem is on the subscriber side. > The same publication, when subscribed to on another DB, works fine. > Also noticed that the remote_lsn value on the target DB is still 0/0. > > targetdb=# select * from pg_repl

Re: Subscription stuck at initialize state

On Thu, 3 Feb 2022 at 12:44, Abhishek Bhola wrote: > Hi Vijaykumar, > > I checked the pg_subscription_rel and all the tables in that subscription > are in the state - i (initialize). > I also tried creating a new publication on the source DB with just one > table and tried to subscribe it, it doe

Re: max_connections different between primary and standby: is it possible?

On Thu, Feb 3, 2022, 3:07 PM Luca Ferrari wrote: > Hi all, > running PostgreSQL 14, physical replication with slot, after changing > (increasing) the max_connections on the primary, I had this message at > a restart from the standby: > > DETAIL: max_connections = 100 is a lower setting than on t

Re: Subscription stuck at initialize state

| 0/20001B8 (5 rows) postgres=# select count(1) from t_bytea; count --- 1000 (1 row) so two things, can you check logs for warning messages of out of *logical replication worker slots *on subscribers ? can you try bumping them, and check if it catches up (srsubstate should either be *r(READY)* or *d (INITIAL COPY))* also monitor logs for both publisher and subscriber. I also figured out all the debugging steps I requested earlier were useless. -- Thanks, Vijay LinkedIn - Vijaykumar Jain <https://www.linkedin.com/in/vijaykumarjain/>

Re: Using the indexing and sampling APIs to realize progressive features

On Thu, Feb 3, 2022, 8:55 PM wrote: > Hi, > > > > I have some questions regarding the indexing and sampling API. > > My aim is to implement a variant of progressive indexing as seen in this > paper (link). To summarize, > > I want to implement a variant of online aggregation, where an aggregate >

Re: increasing effective_cache_size slows down join queries by a factor of 4000x

On Fri, 4 Feb 2022 at 01:03, A Shaposhnikov wrote: > I made a mistake yesterday claiming that the created statistics > changed the row counts in the estimates - it did not - I looked at > the wrong query yesterday. In the correct query plan the row estimate > still differs from the actual by man

how to use trace_lock_oidmin config correctly

ae78) id(5,16401,0,0,0,1) grantMask(0) req(0,0,0,0,0,0,0)=0 grant(0,0,0,0,0,0,0)=0 wait(0) type(INVALID) ALTER TABLE */ -- Thanks, Vijay Open to work Resume - Vijaykumar Jain <https://github.com/cabecada>

Re: how to use trace_lock_oidmin config correctly

gclass::oid; LOG: LockReleaseAll: lockmethod=1 LOG: LockReleaseAll done oid --- 16401 (1 row) postgres=# drop table t; LOG: LockReleaseAll: lockmethod=1 LOG: LockReleaseAll done DROP TABLE */ On Mon, 14 Oct 2024 at 16:30, Vijaykumar Jain < vijaykumarjain.git...@gmail.com> wrote: >

Re: how to know if the sql will run a seq scan

On Wed, 16 Oct 2024 at 02:59, Adrian Klaver wrote: > On 10/15/24 13:50, Vijaykumar Jain wrote: > > Sorry top posting, coz Gmail app on phone. > > > > Yeah, my point was for example we have a large table and we are > > attaching a table as a partition. Now it w

explain vs auto_explain

-> Index Scan using t_pkey on public.t (cost=0.42..8.50 rows=4 width=6) (actual time=0.006..0.008 rows=4 loops=1) Output: ctid Index Cond: (t.col1 < 5) Buffers: shared hit=4 Trigger RI_ConstraintTrigger_a_16475 for constraint r_col1_fkey: time=46.274 calls=4 */ -- Thanks, Vijay Open to work Resume - Vijaykumar Jain <https://github.com/cabecada>

Re: explain vs auto_explain

On Sat, 19 Oct 2024 at 23:48, Vijaykumar Jain < vijaykumarjain.git...@gmail.com> wrote: > > > ok, it makes sense for the reason of having auto_explain. but maybe i did > ask correctly, > why do we not have the extended flags in auto_explain , in , explain wrt > nested_

Re: explain vs auto_explain

ng rows, thereby killing the console session or something ? i tried to check the code for auto_explain , there is nothing that helps understand why it was provided as a separate . -- Thanks, Vijay Open to work Resume - Vijaykumar Jain <https://github.com/cabecada>

<    1   2   3   >