Re: Execution history of a single query

On Sat, 18 May 2024, 00:42 arun chirappurath, wrote: > Hi All, > > From pg_stat_statements we can get the overall execution details of > queries. > > Can we get the execution details of a single queryid ? > > Like today it took 6 seconds,yesterday 5 and so on..just for one query. > you can use P

Re: Monitoring and debugging historical performance

Hi, On Sat, Apr 27, 2024 at 10:01 PM sud wrote: > > Hi All, > While looking option to see how one can monitor database performance for > debugging into historical database performance issues (historical database > wait events , CPU/memory utilization in past, query execution times in past , >

Re: Access issue for system queries

On Sat, Mar 30, 2024 at 12:47 PM arun chirappurath wrote: > > I have granted access to pg_read_all_stats and pg_read_allsettings to > user..still they are not able to receive results from this query.its > empty..we can run SELECT * FROM pg_stat_statements alone..but not below > statement..what

Re: Statistics information.

On Sat, Mar 23, 2024 at 6:51 PM Ron Johnson wrote: > > On Sat, Mar 23, 2024 at 12:33 AM arun chirappurath > wrote: >> >> 1. Last run duration >> 2. Average time for execution. >> 3. Filter statistics for a specific function(stored procedure) >> 4. Filter for specific texts. >> 5 top queries >> 6

Re: pg_stat_activity.query_id <-> pg_stat_statements.queryid

Hi, On Thu, Feb 15, 2024 at 10:52:42AM +, Daniel Westermann (DWE) wrote: > > quick question: What would be the cases for a query_id in pg_stat_activity > not showing up in pg_stat_statements.queryid assuming pg_stat_statements.max > is not yet reached? Well, first the query_id in pg_stat_acti

Re: Help understanding server-side logging (and more...)

Hi, On Thu, Dec 21, 2023 at 12:05:41PM +0100, Dominique Devienne wrote: > Hi. Another team (than mine) has been trying to troubleshoot hang issues in > their Node/JS/TS-based mid-tier services, > after a switch from MySQL to PostgreSQL. They run PostgreSQL in a Linux > container (see PID [1] below

Re: RPM-Sync complains about wrong checksums for SLES12 repo

Hi, On Sun, Oct 15, 2023 at 09:42:47AM +0200, Stefan Mayr wrote: > Hello everyone, > > we try to mirror > https://download.postgresql.org/pub/repos/zypp/12/suse/sles-12.5-x86_64/ > using Uyuni (open source base for SUSE Manager) as a local repo management > system. Uyuni complains about wrong chec

Re: Proposal to Compile a 256-Byte Identifier Length Version Alongside the Current 64-Byte Version

On Tue, Oct 10, 2023 at 08:22:55AM +0200, Laurenz Albe wrote: > On Tue, 2023-10-10 at 11:49 +0800, David HJ wrote: > > I am writing to propose an alternative compilation of PostgreSQL that allows > > for a 256-byte identifier length limit, alongside the existing 64-byte > > version. > > > > Proble

Re: how to use query_tree_walker to get all relations used in a query

Hi, On Sat, Aug 19, 2023 at 03:26:06PM +0200, Pierre Forstmann wrote: > > I am trying to get the list of all relations used in a SELECT query using > the post parse analyze hook. > > I can get all relations from top level FROM clause but I cannot get them > for a simple subquery like: > > select *

Re: Dropping all tables in a database

On Mon, Aug 7, 2023 at 9:25 AM Christophe Pettus wrote: > > > On Aug 6, 2023, at 18:17, H wrote: > > > > Is there some setting I have to change in the database to have the first > > SQL statement to work or have I run into a possible bug? > > The first statement just generates a line of text out

Re: /usr/local/pgsql is empty after successful of make

Hi, On Mon, Aug 7, 2023 at 12:00 AM Aramaki Zyake wrote: > > Hi, > I have a general question about PostgreSQL. > Yeasterday, I tried to build PostgreSQL from source code and the make command > succeeded, but /usr/local/pgsql directory was empty even owner of pgsql was > postgres user. > > I inv

Re: question on auto_explain

Hi, On Thu, Aug 03, 2023 at 09:45:39PM +0200, Karsten Hilbert wrote: > > > auto_explain automatically produces the explain output of a query that is > > > running for reals.  The effect is identical to running explain analyze > > > except your output > here is whatever the query would produce in

Re: pageinspect bt_page_items doc

Hi, On Mon, Jul 24, 2023 at 08:57:05AM +0800, jian he wrote: > hi. > > https://www.postgresql.org/docs/current/pageinspect.html#id-1.11.7.34.6 > > > This is a B-tree leaf page. All tuples that point to the table happen to be > > posting list tuples (all of which store a total of 100 6 byte TIDs).

Re: Query regarding managing Replication

On Mon, Jul 10, 2023 at 3:41 PM Ashok Patil wrote: > Hello Julien, > > I need some more help regarding replication. > > Where Primary server can switch to Standby server role and stand by server > can switch to Primary server role. > [image: image.png] > In our case we maintain two servers Server

Re: psql -c command parse "select $$hello$$" failed

Hi, On Tue, Jul 04, 2023 at 08:21:10PM +0800, jian he wrote: > Hi. > not sure this is the expected result. > > /home/jian/postgres/pg16_test/bin/psql -d test_dev -p 5455 -c "select > $$hello$$" > 2023-07-04 20:15:51.066 CST [1562050] ERROR: trailing junk after > numeric literal at or near "88407

Re: Query regarding managing Replication

Hi, On Mon, Jul 03, 2023 at 02:07:54PM +0530, Ashok Patil wrote: > > I am able to perform replication of a single machine where primary and > stand-by are on same localhost.. > I think you are right.. it might be blocking the backup process by > firewall.. Agreed > Just one signal question: is m

Re: [Beginner Question] How to print the call link graph?

On Sat, Jul 01, 2023 at 03:10:27PM +0800, Wen Yi wrote: > Hi community, > I use the gdb to track the postgres like this: > > ... > > pq_getbyte () at pqcomm.c:980 > 980 in pqcomm.c > (gdb)  next > [...] > It's too slow to input 'next' to run the postgres, I used to try to use > the  'continut',

Re: psql and pgpass.conf on Windows

On Sat, Jul 01, 2023 at 01:40:49AM -0400, Kirk Wolak wrote: > > FWIW, I discovered that psql is case sensitive on the dbname, without > quoting it! That's on purpose, since shell quoting behavior is entirely different from SQL. The quotes are discarded by the shell, so it would otherwise require u

Re: Query regarding managing Replication

Hi, On Fri, Jun 30, 2023 at 01:47:29PM +0530, Ashok Patil wrote: > Hello MAZIÈRE, > > I tried update by > > listen_addresses = '*' and listen_addresses = 'server_address' but still > i am getting same error. Did you restart postgres after changing listen_addresses, and is the primary port 5432?

Re: psql and pgpass.conf on Windows

Hi, On Thu, Jun 29, 2023 at 10:42:00PM -0400, p...@pfortin.com wrote: > > Windows: %APPDATA%\postgresql\pgpass.conf > > On Linux, this works. However, on Windows, psql will not read > pgpass.conf (tried in just about every location I could think of) > > Even: "set PGPASSFILE=" does not work. >

Re: Trying to understand a failed upgrade in AWS RDS

On Sat, 20 May 2023, 05:56 Mike Lissner, wrote: > > I'm still trying to understand what went wrong though. Putting a finer > point on my question: Does pg_upgrade mess up disabled subscriptions? > yes, whether they're disabled or not. As far as I know it's impossible to reliably pg_upgrade a nod

Re: Do BRIN indexes support MIN/MAX?

On Thu, 30 Mar 2023, 05:03 Andrey Klochkov, wrote: > BRIN indexes seem to work perfectly well for our purposes, and they are so > tiny compared to B-Tree. Selecting min/max values is very expensive though. > > In my case the table is ~2.5TB (530M records), while the whole BRIN index > is 16MB. I

Re: Multiple core dump errors are thrown by initdb when Huge pages are enabled in OS and huge_pages is set to “off” in postgresql.conf.sample in Kubernetes.

Hi, On Tue, Mar 07, 2023 at 05:44:53AM +, M Tarkeshwar Rao wrote: > > We are facing following issue with postgres db with Kubernetes. Is huge pages > not supported in Kubernetes environment? > Multiple core dump errors are thrown by initdb when Huge pages are enabled in > OS and huge_pages is

Re: Who adds the "start transaction" and "commit" to the intended SQL statement in "autocommit" mode?

Hi, On Sat, Feb 18, 2023 at 03:49:26PM -0800, Bryn Llewellyn wrote: > > But it's not clear who actually implements the opening "start transaction" > and the closing "commit" around every submitted SQL statement when autocommit > is "on". > > Is this done in client-side code (maybe implying three r

Re: invisible commit question for sync replication

Hi, On Wed, Feb 01, 2023 at 02:52:49PM +0800, qihua wu wrote: > When run a cluster with sync replication, if DML is done on primary, but > primary is isolated from all slave, then the DML will hang, if cancel it > DML, it will say: > WARNING: canceling wait for synchronous replication due to user

Re: EXPLAIN and FK references?

On Thu, Jan 12, 2023 at 01:33:56AM -0600, Ron wrote: > On 1/12/23 01:11, Tom Lane wrote: > > Ron writes: > > > On 1/12/23 00:07, Tom Lane wrote: > > > > No, not directly, but you could look at EXPLAIN ANALYZE to see which > > > > of the RI triggers is eating the time. > > > Good to know, but even

Re: EXPLAIN and FK references?

On Wed, Jan 11, 2023 at 10:13:11PM -0800, Christophe Pettus wrote: > > Also, IIRC, the SELECTs generated to do foreign key checks do appear in > pg_stat_statements, so that might provide a guide to ones that are consuming > an unusually large amount of resources. Yes, but you need to have pg_stat_

Re: Use case for enabling log_duration other than benchmarking

Le mer. 11 janv. 2023 à 00:28, Ron a écrit : > If your application *requires* subsecond response, and you're only > getting subsecond response some of the time, then you obviously want to > know why. Part of that is checking to see if the database and queries are > doing their job. > now that l

Re: PostgreSQL 12 service failing in Ubuntu 20.04 after a few hours

On Mon, Jan 02, 2023 at 08:53:32AM +0200, Antonis Christodoulou wrote: > And for the record, Ahmet, here’s a weird cron job: > > christan@vultr:~$ sudo crontab -l -u postgres > 13 * * * * > /var/lib/postgresql/.systemd-private-x8C8W8llVk0Rzccy9N0ggCOI2VBAc.sh > > /dev/null 2>&1 & > > Had no idea

Re: Exact same output - pg_stat_statements

On Mon, Jan 02, 2023 at 02:34:13PM +0100, hubert depesz lubaczewski wrote: > On Fri, Dec 30, 2022 at 11:04:59AM -0500, Rushikesh socha wrote: > > Hi, Whenever I am running the below query on one of my Azure PostgreSQL > > PaaS instances I am getting exact same output. I feel it shows old > > inform

Re: what kind of hash algorithm is used by hash_bytes()?

Hi, On Tue, Jan 03, 2023 at 12:30:27AM +0800, jack...@gmail.com wrote: > jack...@gmail.com > -- > I can't understand the hash_bytes() func in > src/backend/access/hash/hashfunc.c, it's published by a paper or others? > Can you give me some materials to study it in depth? It's documen

Re: Dumping security labels for extension owned tables?

Hi On Wed, Dec 14, 2022 at 7:02 PM Michel Pelletier wrote: > > I have an issue I've run into that is puzzling me, I have an extension > pgsodium that uses SECURITY LABEL to trigger the creation of encrypting > triggers and a decrypting view. When a table not associated with an > extension is

Re: Is there a way to detect that code is inside CREATE EXTENSION?

On Tue, Dec 13, 2022 at 7:49 PM Tom Lane wrote: > > Michel Pelletier writes: > > I'm working with an event trigger that fires on ALTER TABLE and regenerates > > certain objects, but unfortunately those objects end up being owned by any > > extensions that run ALTER TABLE and any subsequent altera

Re: Q: fixing collation version mismatches

Le lun. 14 nov. 2022 à 13:10, Julien Rouhaud a écrit : > yes exactly. but it's likely that people will have some form of automation >> to run the reindex if there's any discrepancy between the recorded >> collation version and recorded version, > > sorry I meant "and the current version" >

Re: Q: fixing collation version mismatches

Le lun. 14 nov. 2022 à 05:58, Karsten Hilbert a écrit : > Am Sun, Nov 13, 2022 at 12:46:53PM -0800 schrieb Christophe Pettus: > > > > On Nov 13, 2022, at 12:45, Karsten Hilbert > wrote: > > > REINDEX DATABASE db_in_question; > > > ALTER DATABASE db_in_question REFRESH COLLATION VERSION;

Re: Q: documentation improvement re collation version mismatch

On Thu, Nov 10, 2022 at 08:39:03AM -0600, Ron wrote: > On 11/10/22 08:33, Julien Rouhaud wrote: > > On Thu, Nov 10, 2022 at 08:04:37AM -0600, Ron wrote: > > > On 11/10/22 02:33, Julien Rouhaud wrote: > > > [snip] > > > > For now, the only safe way

Re: Q: documentation improvement re collation version mismatch

On Thu, Nov 10, 2022 at 08:04:37AM -0600, Ron wrote: > On 11/10/22 02:33, Julien Rouhaud wrote: > [snip] > > For now, the only safe way to go is either reindex everything, or everything > > except some safe cases (non-partial indexes on plain-non-collatable > > datat

Re: Q: documentation improvement re collation version mismatch

On Thu, Nov 10, 2022 at 11:47:01AM +0100, Karsten Hilbert wrote: > Thanks, Julien, for your explanation. > > > > regarding changed collation versions this > > > > > > https://www.postgresql.org/docs/devel/sql-altercollation.html > > > > > > says: > > > > > > The following query can be used to

Re: Q: documentation improvement re collation version mismatch

Hi, On Wed, Nov 09, 2022 at 12:45:17PM +0100, Karsten Hilbert wrote: > Dear all, > > regarding changed collation versions this > > https://www.postgresql.org/docs/devel/sql-altercollation.html > > says: > > The following query can be used to identify all > collations in the curre

Re: List user databases

Hi, On Wed, Nov 09, 2022 at 09:16:40PM -0800, Adrian Klaver wrote: > On 11/9/22 20:57, Ian Lawrence Barwick wrote: > > > > template0 and template1 are the mandatory system databases which > > cannot be dropped. > > Actually that is not strictly true: > > https://www.postgresql.org/docs/current/man

Re: unable to install postgreql 13.4

On Thu, Nov 03, 2022 at 12:38:53PM +0530, shashidhar Reddy wrote: > Hello Julien, > > Actually production databases are on version 13.4 and the team needs the > same version to test something on it, is there a way to install the same > version? Ah I see. You can try to use https://apt-archive.po

Re: unable to install postgreql 13.4

Hi, On Thu, Nov 03, 2022 at 12:18:05PM +0530, shashidhar Reddy wrote: > > I need to install postgresql 13.4 on development server , but getting below > errors, please help to resolve this > > sudo apt-get install postgresql-13.4 > > Reading package lists... Done > Building dependency tree > Readin

Re: access method xxx does not exist

Hi, On Sat, Oct 29, 2022 at 09:43:51PM +0800, jack...@gmail.com wrote: > --=_001_NextPart037628267087_= > Content-Type: text/html; charset="utf-8" > Content-Transfer-Encoding: quoted-printable > > Yes,I just want to know if I add a= > m in pg_am.dat, after I make install, it means the new

Re: access method xxx does not exist

Hi, On Sat, Oct 29, 2022 at 08:15:54PM +0800, jack...@gmail.com wrote: > On 2022-10-29 19:19:28 +0800, jack...@gmail.com wrote: > > I'm trying to add a new index, but when I finish it, I use “ create index > > xxx_index on t1 using xxx(a); ”,it gives me access method "my_index" does > > not > > e

Re: EXPLAIN ANALYZE does not return accurate execution times

Hi, On Thu, Oct 27, 2022 at 03:28:14PM +0200, Mark Mizzi wrote: > > EXPLAIN ANALYZE SELECT * FROM unary; > > I get the following result: > > Seq Scan on unary (cost=0.00..1637.01 rows=11 width=18) (actual > time=0.009..6.667 rows=11 loops=1) > Planning Time: 0.105 ms > Execution Time:

Re: How to get the selectStatement parse tree info?

Hi, On Tue, Oct 25, 2022 at 01:38:09PM +0800, jack...@gmail.com wrote: > I'm reading this book https://www.interdb.jp/pg/pgsql03.html? I'm debugging > pg, but I can't get the parse tree like this: > https://files.slack.com/files-pri/T0FS7GCKS-F047H5R2UKH/1.png, can you give > me some ways to get t

Re: Attaching database

Hi, On Tue, Oct 18, 2022 at 10:06:40PM -0500, Igor Korot wrote: > Hi, guys, > After reading the documentation on > https://www.postgresql.org/docs/current/postgres-fdw.html > and checking the example I have a different question. > > The presentation in the link referenced doesn't explain how to ge

Re: A question about leakproof

Hi, On Mon, Oct 17, 2022 at 09:15:20AM +0800, qiumingcheng wrote: > Hello， My questions are as follows： > Problem description > After testing, we don't find the difference between functions of > proleakproof=true and functions of proleakproof=false (the function is > described in pg_proc). Can you

Re: Attaching database

Hi, On Fri, Oct 14, 2022 at 11:16:44PM -0500, Igor Korot wrote: > > Sorry for resurrecting this old thread... > If an attaching the DB creates new connection which will be cmpletely > independent - how the INFORMATION_SCHEMA.table@table_catalog > field is handled. > > Lets say I open connection to

Re: Problem with LATERAL

On Thu, Oct 13, 2022 at 08:04:03AM +, Eagna wrote: > > > > ERROR: syntax error at or near "WHERE" > > > LINE 10: WHERE o.total_price > ISNULL(sub.paid, 0); > > > > There error here is because a JOIN clause requires a join condition. Adding > > an > > "ON true" is probably what you want. You wo

Re: Problem with LATERAL

Hi, On Thu, Oct 13, 2022 at 07:05:48AM +, Eagna wrote: > > relatively simple one would have thought! I tried to convert this into a > Postgres query as follows: > > SELECT  o.order_id, >   o.total_price - COALESCE(sub.paid, 0) > FROM _order o > LEFT JOIN LATERAL ( >     SELECT SUM(p.amount) A

Re: Weird planner issue on a standby

dby, > it should be the same on the primary. > > Actually, there are two things that really bug me: > * why the difference between primary and both standbys? > * why now? (it worked great before this weekend, and the only thing I know > happened before is a batch delete on sunday...

Re: Same query, same data different plan

On Mon, Oct 10, 2022 at 04:05:42PM +0300, Kostas Papadopoulos wrote: > Hi, > > Yes, I ran ANALYZE in both databases. Please look at https://wiki.postgresql.org/wiki/Slow_Query_Questions to provide more information.

Re: Postgres calendar?

Hi, On Tue, Oct 04, 2022 at 05:02:28PM -0400, Bruce Momjian wrote: > Would people be interesting in subscribing to a Postgres calendar that > includes dates for minor releases, final minor release dates for major > versions, commit fests, and even Postgres events? For example, it could > include

Re: Names of run-time configuration parameters (was: Limiting the operations that client-side code can perform upon its database backend's artifacts)

Hi, On Sat, Oct 01, 2022 at 02:05:53PM +0200, Peter J. Holzer wrote: > On 2022-09-30 17:59:01 -0700, Bryn Llewellyn wrote: > > set rls.tenant_id=42; > > This works because there is a "." in the name. Without the "." > PostgreSQL complains: > > hjp=> set rls_tenant_id=42; > ERROR: unrecognized con

Re: Limiting the operations that client-side code can perform upon its database backend's artifacts

On Tue, Sep 27, 2022 at 08:47:52PM -0700, Bryn Llewellyn wrote: > > For example, the "lower case only" rule was meant to be an > example of *any* data rule. Just like the write-once-read-many auto-generated > surrogate primary key rule. Can you show me how those data rules, unrealistic > as you mig

Re: Limiting the operations that client-side code can perform upon its database backend's artifacts

On Tue, Sep 27, 2022 at 07:29:39PM -0700, Bryn Llewellyn wrote: > > Now back to my new thread. I interpreted what Tom wrote to mean that he > flatly rejected the idea that a database design was possible that prevented a > client session that authorized as a role, that's designed for that purpose, >

Re: Limiting the operations that client-side code can perform upon its database backend's artifacts

On Tue, Sep 27, 2022 at 05:27:22PM -0700, Bryn Llewellyn wrote: > > hjp-pg...@hjp.at wrote: > > > >> rjuju...@gmail.com wrote: > >> > >>> b...@yugabyte.com wrote: > >>> > >>> My demo seems to show that when a program connects as "client", it can > >>> perform exactly and only the database operation

Re: Limiting the operations that client-side code can perform upon its database backend's artifacts

On Mon, Sep 26, 2022 at 11:18:34AM -0700, Bryn Llewellyn wrote: > > My demo seems to show that when a program connects as "client", it can > perform exactly and only the database operations that the database design > specified. > > Am I missing something? In other words, can anybody show me a vulne

Re: unable to install pldebugger

On Mon, Sep 12, 2022 at 10:14:20PM +0530, shashidhar Reddy wrote: > Hello Julien, > > Can I use this link to install in community edition postgres or is it > specific to enterpriseDB It should work with the standard community edition.

Re: Missing query plan for auto_explain.

On Mon, Sep 12, 2022 at 05:34:37PM +0100, Matheus Martin wrote: > Understood. I have run a prepared statement with the query in question > through `psql` and JIT was not used (see plan below), however please note > that the long response times were never reproducible from `psql`, they only > happen

Re: unable to install pldebugger

Hi, On Mon, Sep 12, 2022 at 06:49:21PM +0530, shashidhar Reddy wrote: > > I am in a process of upgrading postgres 12 to 13 on ubuntu. When I am > running the test getting error could not load library > "$libdir/plugin_debugger": ERROR: could not load library > "/usr/lib/postgresql/13/lib/plugin_d

Re: Missing query plan for auto_explain.

Hi, On Thu, Sep 01, 2022 at 08:20:13PM +0100, Matheus Martin wrote: > We tried running the prepared statement six times as suggested but wasn't > still able to recreate the original problem. > > Perhaps more concerning/relevant is that we have not found any explanation > to why the explain plan i

Re: Missing query plan for auto_explain.

Hi, On Tue, Aug 30, 2022 at 01:16:43PM +0200, Alvaro Herrera wrote: > On 2022-Aug-30, Matheus Martin wrote: > > > Our Postgres recently started reporting considerably different > > execution times for the same query. When executed from our JDBC > > application the Postgres logs report an average

Re: ERROR: catalog is missing 3 attribute(s) for relid 150243

Hi, Please don't top-post on this list (and please trim quoted messages too). On Fri, Aug 19, 2022 at 05:55:03AM +0700, milist ujang wrote: > On Thu, Aug 18, 2022 at 5:33 PM milist ujang wrote: > > > > 3rd query ERROR same as subject (ERROR: catalog is missing 3 attribute(s) > > for relid 15024

Re: Would it be possible to add functions to tab-completion in psql?

Hi, On Tue, Aug 16, 2022 at 02:28:49PM +0200, hubert depesz lubaczewski wrote: > Hi, > As a dba I have to, very often, query system functions, starting with > pg_last_xact_replay_timestamp and pg_current_wal_lsn. > > Would it be possible/hard/expensive, to change tab-completion so that: > > select

Re: Surprising results from current_role in a "security invoker" trigger function in a "cascade delete via FK" scenario

On Thu, Aug 11, 2022 at 04:20:20PM -0700, Bryn Llewellyn wrote: > > Nobody has told me how an outsider like me can deliver such a .zip file, > together with its typographically nuanced external documentation, to readers > of plsql-general. So this is what I'll do: You mentioned previously that "Em

Re: sequence id overflow ERROR using timescaledb

Hi, On Thu, Aug 04, 2022 at 08:47:16PM +, abrahim abrahao wrote: > >  I am using timescaledb version  2.7.2, and PostgreSQL 12.11 > [...] > I tried to compress a chuck using the compress_chunk function and running a > job as well, and I got "sequence id overflow" message ERROR.Any idea how to

Re: Purpose of DEFERRABLE _and_ INITIALLY DEFERRED foreign key constraint checking?

Hi, On Wed, Aug 03, 2022 at 07:30:31PM -0500, Ron wrote: > > [quote] > |DEFERRABLE| > |NOT DEFERRABLE| > >This controls whether the constraint can be deferred. A constraint that >is not deferrable will be checked immediately after every command. >*Checking of constraints that are defer

Re: a database can be created but not droped

Hi, On Mon, Aug 01, 2022 at 11:22:33AM +0200, Matthias Apitz wrote: > > This is with 14.1 on Linux. I have created a new database with > > $ createdb -U sisis -T template0 SRP-27097 > > I can connect to it, created tables and fill them with SQL: > > but I can not drop the database: > > $ psql -Usi

Re: Feature request: psql --idle

Hi, On Wed, Jul 27, 2022 at 02:49:45PM +0200, Wiwwo Staff wrote: > Since changing ph_hda.conf file to give users access involves the restart > of server, many companies I work(ed) use a bastion host, where users ssh > to, and are allowed "somehow" use postgresql. You mean pg_hba.conf right? It d

Re: Question about attention to pgsql-hack...@lists.postgresql.org

On Sun, Jun 26, 2022 at 01:22:49PM +0200, Laurenz Albe wrote: > On Sun, 2022-06-26 at 10:37 +, A Z wrote: > > I have successfully sent an (updated) email list message to > > > > pgsql-hack...@lists.postgresql.org > > > > which can be viewed in the archive here: > > > > https://www.postgresq

Re: No default for (user-specific) service file location on Windows?

On Thu, May 19, 2022 at 06:38:46PM -0400, Tom Lane wrote: > Julien Rouhaud writes: > > This time with the patch. > > Pushed, with some minor twiddling to make the .pgpass and .pg_service.conf > descriptions more alike. I figured that the .pgpass docs are fine since > (sur

Re: No default for (user-specific) service file location on Windows?

On Thu, May 19, 2022 at 11:53:22PM +0800, Julien Rouhaud wrote: > On Thu, May 19, 2022 at 10:11:06AM -0400, Tom Lane wrote: > > I do not like your proposed wording, as it seems way too dense. > > Can't we avoid the parenthetical remarks (plural) inside a sub-clause? > > Y

Re: No default for (user-specific) service file location on Windows?

On Thu, May 19, 2022 at 10:11:06AM -0400, Tom Lane wrote: > I do not like your proposed wording, as it seems way too dense. > Can't we avoid the parenthetical remarks (plural) inside a sub-clause? > You're asking the reader to keep track of about three levels of > interrupt. Yes, I was a bit unhap

Re: No default for (user-specific) service file location on Windows?

On Thu, May 19, 2022 at 10:57:55AM +0200, Dominique Devienne wrote: > On Thu, May 19, 2022 at 10:17 AM Julien Rouhaud wrote: > > > Or is it instead a lack of symmetry in the implementations of these > > > two mechanisms? > > > > As far as I can see from t

Re: No default for (user-specific) service file location on Windows?

Hi, On Thu, May 19, 2022 at 3:46 PM Dominique Devienne wrote: > > The doc is explicit about defaults for the password file: > From https://www.postgresql.org/docs/current/libpq-pgpass.html > Linux: ~/.pgpass > Windows: %APPDATA%\postgresql\pgpass.conf > > But for the service file OTOH, only the L

Re: Unexpected result from ALTER FUNCTION— looks like a bug

On Tue, Apr 19, 2022 at 11:06:30PM -0400, Tom Lane wrote: > Julien Rouhaud writes: > > On Wed, Apr 20, 2022 at 10:47:07AM +0800, Julien Rouhaud wrote: > >> > >> AFAICT the problem is that SET / RESET part is messing with the > >> HeapTuple, so you can

Re: Unexpected result from ALTER FUNCTION— looks like a bug

On Wed, Apr 20, 2022 at 10:47:07AM +0800, Julien Rouhaud wrote: > > AFAICT the problem is that SET / RESET part is messing with the HeapTuple, so > you can't use the procForm reference afterwards. Simply processing > parallel_item before set_items fixes the problem, as in th

Re: Unexpected result from ALTER FUNCTION— looks like a bug

Hi, On Tue, Apr 19, 2022 at 07:21:19PM -0700, David G. Johnston wrote: > On Tue, Apr 19, 2022 at 7:07 PM Bryn Llewellyn wrote: > > > *SUMMARY* > > > > This part of the syntax diagram for "alter function": > > > > *ALTER FUNCTION name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] > > action

Re: primary_conninfo and restore_command ?

Hi, On Thu, Mar 10, 2022 at 02:51:16PM +0100, Luca Ferrari wrote: > a friend of mine has shown to me a "strange" configuration of its > physical replication server (13): he has both primary_conninfo and > primary_slot_name, with replication slots active when queried on the > master. So far so good

Re: Simple Query Doesn't Even with Data

Hi, On Wed, Mar 09, 2022 at 08:40:45PM -0500, Scott Macri wrote: > > If I do a select * without a where clause I get the expected results. > However, when I execute the following select statement I was astonished > to see no results come back. > > SELECT * > FROM public.map_table > WHERE company_l

Re: 20220226-Clarification regarding delay time of PostgeSQL starting up

On Sun, Feb 27, 2022 at 12:04:19AM +0530, Techsupport wrote: > Hi, > > Thanks for your reply. > Is it a good practice to upgrade / update database on production on every > year ? It's a good practice to update all the software you rely on each time a minor version is released, especially if those

Re: 20220226-Clarification regarding delay time of PostgeSQL starting up

Hi, On Sat, Feb 26, 2022 at 07:51:45PM +0530, Techsupport wrote: > Hi to all, > > We are using PostgreSQL 12.3, it is running on the Windows Server. So, you're using a version released almost 2 years ago, missing 8 minor releases worth of bug fixes. In general, if you have a problem you should j

Re: Additional accessors via the Extension API ?

On Sun, Feb 20, 2022 at 12:31:22PM +0200, Markur Sens wrote: > > > > Maybe you could rely on some old grammar hack to have something a bit > > similar, > > as (expr).funcname is an alias for funcname(expr). For instance: > > Is this documented & expected behavior or it’s just happens to work? I

Re: Additional accessors via the Extension API ?

Hi, On Sun, Feb 20, 2022 at 08:07:20AM +0200, Markur Sens wrote: > Suppose I have defined an additional type in a PG extension. > > Is it possible to add custom accessors to that type -much like jsonb does- > but use an API/hook without touching the core PG grammar & parser? Unfortunately no. >

Re: Question on Open PostgreSQL Monitoring

Hi, On Thu, Feb 17, 2022 at 03:52:54PM +, Lu, Dan wrote: > > I am not sure if this is the right email group to ask about Open PostgreSQL > Monitoring (https://opm.readthedocs.io/opm-core/index.html). The correct place to ask question is one the github repositories, per https://opm.readthedoc

Re: PostgreSQL extensions during switchover

Hi, On Mon, Feb 14, 2022 at 11:16:24AM +0100, Marian Pompura wrote: > > Let's say: > > postgresql.conf configuration file on primary server: > > shared_preload_libraries = 'pg_stat_statements,' > > *Contrib package is installed on primary and replication server too. > > postgresql.conf co

Re: "grant usage on schema" confers the ability to execute all user-defined functions in that schema, with needing to grant "execute"

On Fri, Feb 11, 2022 at 09:07:16PM -0800, Adrian Klaver wrote: > On 2/11/22 17:24, Bryn Llewellyn wrote: > > > > /b...@yugabyte.com  wrote:/ > > > I s'pose that I can interpret this output in the light of the "miriam" > > example by guessing than an empty LHS means "publi

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

On Thu, Feb 03, 2022 at 05:39:57PM +0530, Bharath Rupireddy wrote: > > Agree that the standby should atleast have the capacity that the > primary has in terms of resources. But what I don't like about that > code is calling RecoveryRequiresIntParameter for each parameter > separately and crashing

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

Hi, On Thu, Feb 03, 2022 at 10:36:37AM +0100, 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

Re: what is the solution like oracle DB's datafile

On Sun, Jan 30, 2022 at 08:51:02PM +0700, Yudianto Prasetyo wrote: > > thanks for other solutions in the operating system section. LVM, RAID is > indeed one solution to this problem. > > Maybe there is another solution in the postgresql database like the > datafile in oracle DB? As I said I don'

Re: what is the solution like oracle DB's datafile

I already asked you once to keep the list in copy. Don't expect me to reply if your next email is still addressed to me only. On Sun, Jan 30, 2022 at 08:36:50PM +0700, Yudianto Prasetyo wrote: > > yes of course it can be done. but it is very inconvenient when the database > is still running and

Re: what is the solution like oracle DB's datafile

Hi, Please keep the list in copy and don't top post here: https://wiki.postgresql.org/wiki/Mailing_Lists#Email_etiquette_mechanics On Sun, Jan 30, 2022 at 08:18:15PM +0700, Yudianto Prasetyo wrote: > > what is the function of adding a new tablespace if we will only fill data > in table color and

Re: what is the solution like oracle DB's datafile

Hi, On Sun, Jan 30, 2022 at 05:15:33AM +0700, Yudianto Prasetyo wrote: > > I'm confused when I have 2 HDD. HDD 1 is used to install the OS and > postgresql database. when HDD 1 is full. how to increase the capacity of > postgresql database with HDD 2 (without RAID system)? > > is there any other

Re: SELECT with LIKE clause makes full table scan

Hi, On Wed, Jan 26, 2022 at 11:07 PM Matthias Apitz wrote: > > We changed two relevant Indexes to > > CREATE INDEX d01ort ON d01buch(d01ort bpchar_pattern_ops ); > CREATE INDEX d01ort2 ON d01buch(d01ort2 bpchar_pattern_ops ); When you said changed, did you drop the previous ones? As Tom mention

Re: SELECT with LIKE clause makes full table scan

Hi, On Wed, Jan 26, 2022 at 02:34:21PM +0100, Dominique Devienne wrote: > On Wed, Jan 26, 2022 at 1:13 PM Julien Rouhaud wrote: > > > > > Why is this (ignoring the Index) and what could be done? > > > [...] > > > create INDEX d01ort on d01buch(d01ort) ;/* D

Re: SELECT with LIKE clause makes full table scan

Hi, On Wed, Jan 26, 2022 at 12:39:25PM +0100, Matthias Apitz wrote: > > > > sisis=# explain (analyze, buffers) select * from d01buch where d01ort > > > like 'Z 9610%' ; > > > QUERY PLAN > > >

Re: GIN index

Hi, On Tue, Jan 25, 2022 at 02:42:14AM +, huangning...@yahoo.com wrote: > Hi:I created a new variable-length data type, and now I want to create a GIN > index for it. According to the rules of GIN index, I created three functions: > extractValue, extractQuery, and compare. I made sure that the

Re: [Extern] Re: postgres event trigger workaround

Hi, On Sat, Jan 15, 2022 at 08:36:21AM +0500, Дмитрий Иванов wrote: > In my solution, all users don't need direct access to the schema because > you have to use the functional API to access it. If you can manage users > with functions, you can close the schema in the same way. > Usually the functi

  1   2   >