Re: List all tables from a specific database

Hi, On Fri, Jan 14, 2022 at 12:19:28PM +, Flaviu2 wrote: > Thanks a lot. > Maybe I am not far from a solving solution. So, if I create a database, lets > say (SQL script): > CREATE database mydb3; > How can I create a table under mydb3 ? Because, if I run: > SELECT relname FROM pg_class

Re: [Extern] Re: postgres event trigger workaround

Hi, On Fri, Jan 14, 2022 at 09:01:12AM +, Zwettler Markus (OIZ) wrote: > > We have the need to separate user (role) management from infrastructure > (database) management. > > Granting CREATEROLE to any role also allows this role to create other roles > having CREATEDB privileges and

Re: postgres event trigger workaround

Hi, On Wed, Jan 12, 2022 at 11:57:45AM +, Zwettler Markus (OIZ) wrote: > > PG event triggers are not firing on CREATE ROLE, CREATE DATABASE, CREATE > TABLESPACE by definition (would be nice if they do). > > Is there any workaround to react with ddl_command_start behavior on such an > event?

Re: pg_stat_statements

On Wed, Jan 12, 2022 at 10:22:38AM +, Simon Riggs wrote: > On Wed, 12 Jan 2022 at 03:03, Julien Rouhaud wrote: > > > > Unfortunately this is a known limitation. > > I see this as a beneficial feature. > > If the same SQL is executed against different sets of ta

Re: pg_stat_statements

Hi, On Tue, Jan 11, 2022 at 03:04:14PM +, Dirschel, Steve wrote: > > I'm not sure if this is the correct distribution list for this type of > question but I'll try anyways. We have an app that uses multiple schemas. > It will do a set schema 'schema_name' and execute queries. The queries >

Re: String comparison fails for some cases after migration

Hi, On Wed, Oct 27, 2021 at 2:12 PM Jayadevan M wrote: > > We moved our PostgreSQL database from one hosting provider to another using > pgbackrest. In the new environment, some comparison operations were > failing. The issue was fixed by running an update. But I am trying to find > out

Re: jsonb Indexing

Hi, On Fri, Sep 17, 2021 at 9:55 PM wrote: > > I was wondering what I'm doing wrong. There are steps what I've tried: > > CREATE TABLE api ( > jdoc jsonb > ); > > INSERT INTO api (jdoc) > VALUES ('{ > "guid": "9c36adc1-7fb5-4d5b-83b4-90356a46061a", > "name": "Angela Barton",

Re: The tragedy of SQL

On Wed, Sep 15, 2021 at 8:31 AM Raymond Brinzer wrote: > > So, on a practical note: I'd like it if PostgreSQL supported > alternate languages for queries, as it does for stored procedures. I agree, and actually sent a patch some time ago to allow usage of third-party parser(s). They can

Re: Idempotent DDL Updates

On Mon, Aug 30, 2021 at 11:56 PM Miles Elam wrote: > > Not sure that querying the catalogs is strictly necessary though… Could you > say more? I meant for anything that doesn't have an IF [NOT] EXISTS, including cases where such a clause wouldn't be possible. For instance if you have to

Re: user creation time for audit

On Mon, Aug 30, 2021 at 5:24 PM Vijaykumar Jain wrote: > > 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 >&

Re: user creation time for audit

On Mon, Aug 30, 2021 at 4:43 PM Boyapalli, Kousal wrote: > > We are looking for the user creation date > > Tried from both from pg admin and psql by using > > select * from pg_catalog.pg_user and /du, /du+ we were able to get the > users but is there a way where we can get the creation date

Re: Idempotent DDL Updates

On Sat, Aug 28, 2021 at 2:19 AM Miles Elam wrote: > > What is the general consensus within the community on idempotent DDL scripts, > ie. consistent usage of IF EXISTS/IF NOT EXISTS/OR REPLACE for SQL init files > that get checked into source control? > [...] > The drawbacks I've run across are

Re: log_statement setting

On Thu, Aug 19, 2021 at 1:55 PM Jayadevan M wrote: > > Thank you. Follow up question- If it is set to different values for different > users/databases, how can I get those values? For example, > I have a server with 4 databases. If I just query pg_settings, I get only one > value. The value

Re: EXTERNAL: Re: Could not startup new database - Oracle to Postgres Migration error.

On Mon, Aug 16, 2021 at 5:16 PM Pansara, Jiten wrote: > > Thanks a lot for quick response. I can see below after executing the Perl > command given by you. > > PS C:\ora2pg-22.1> perl -e "use DBD::Pg" > PS C:\ora2pg-22.1> It means that you already have DBD::Pg installed.

Re: Could not startup new database - Oracle to Postgres Migration error.

On Mon, Aug 16, 2021 at 4:46 PM Pansara, Jiten wrote: > > I was following Ora2Pg : Migrates Oracle to PostgreSQL (darold.net) to > migrate the db and am stuck when trying to execute below command. > > perl -MCPAN -e 'install DBD::Pg' It would be better to send the error in plain text rather

Re: Fwd: Postgres.exe crashes and tears down all apps, recovers and is running again

On Thu, Jul 22, 2021 at 09:21:56AM -0500, Ron wrote: > On 7/22/21 4:52 AM, Beat Hoedl wrote: > [snip] > > BTW: There is no virus scanner on the postgres folder and it's a > > productive system, I cant just update. > > > > There's *always* a maintenance window, even if it's just once a year on >

Re: Fwd: Postgres.exe crashes and tears down all apps, recovers and is running again

On Thu, Jul 22, 2021 at 11:52:55AM +0200, Beat Hoedl wrote: > > Have a bunch of processes connected to Postgres (10.0) [...] > > So the problem leaves me puzzled. > Would be great if somebody has some ideas how to continue investigation. The first thing you need to do is to update to 10.17 and

Re: What is the difference between context of "sighup", "super-backend", and "backend" in pg_settings?

On Sat, Jun 26, 2021 at 12:56:21PM +0800, 周书林 wrote: > Hi, > > I am interested in the configuration parameter of Postgres. I found that > the configuration parameters in context of "sighup", "super-backend", and > "backend" are all required to send "SIGHUP" signal to postmaster for > rereading

Re: compute_query_id

On Thu, Jun 17, 2021 at 08:57:02PM +0530, Vijaykumar Jain wrote: > > test=# show log_line_prefix; > log_line_prefix > > [timestamp=%t] [query_id=%Q] : > (1 row) > > test=# show compute_query_id; > compute_query_id > -- > on > (1 row) >

Re: compute_query_id

On Thu, Jun 17, 2021 at 08:09:54PM +0530, Vijaykumar Jain wrote: > how is the compute_query_id actually calculated? It's the exact same implementation that was extracted from pg_stat_statements. You have some implementation details at https://www.postgresql.org/docs/current/pgstatstatements.html.

Re: clear cache in postgresql

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. Can I somehow see what that cache > is holding? You can use

Re: pg_basebackup fails with "COPY stream ended"

On Tue, Jun 15, 2021 at 09:53:45PM -0700, Dipanjan Das wrote: > > I am running "pg_basebackup -h -U postgres -D -X stream". It > fails with either of the following two error messages: > [...] > WARNING: terminating connection because of crash of another server process > DETAIL: The postmaster

Re: Struggling with EXCLUDE USING gist

On Sat, Jun 5, 2021 at 12:48 AM Laura Smith wrote: > > All the examples I've seen around the internet make this sound so easy. > > But I seem to be missing some important step because all I'm getting are > messages such as "DETAIL: Key (t_val, t_version)=(def, [-infinity,infinity)) > conflicts

Re: Empty tablespace directory after restore with psql -d dbname -f dump.sql

Le sam. 15 mai 2021 à 01:14, frank picabia a écrit : > > We cancelled a web application upgrade part way through when it was > realized > Postgres needed a version upgrade first. Using the dump file extracted > from > a full dump with pg_dumpall the DB was restored with a line like : > > psql

Re: server process exited with code 1

On Fri, Apr 23, 2021 at 03:58:32PM +, Eric Hill wrote: > Hey, > > We are experiencing a periodic PostgreSQL crash. It happens overnight when > automated processes are updating content on our website. My PostgreSQL > version information is: > > PostgreSQL 11.10, compiled by Visual C++

Re: How to recover data from 9.3 data directory

Le dim. 14 mars 2021 à 00:17, Adrian Klaver a écrit : > On 3/13/21 7:25 AM, Andrus wrote: > > Hi! > > > > >Debian does not offer to install Postgres 9.3 into it. How to install > > postgres 9.3 in new Debian ? > > > >> > >> >You'll have to compile it. > > > > Enterpisedb offers 9.3 download for

Re: How to recover data from 9.3 data directory

Le sam. 13 mars 2021 à 22:02, Andrus a écrit : > Hi! > > > that won't work on windows. pg_upgrade only works if source and target > environment are binary compatible, which isn't the case for windows / GNU > Linux. > > wal archiving works between Linux and Windows. So I expected those > commands

Re: How to recover data from 9.3 data directory

Le sam. 13 mars 2021 à 21:29, Andrus a écrit : > 2. Tried to run old server using > C:\Program Files\PostgreSQL\9.3\bin\pg_ctl.exe" start -D > "D:/Centos93Data/data" > got error > > FATAL: database files are incompatible with server > DETAIL: The database cluster was initialized with

Re: Different query result, maybe bad index

Hi, On Wed, Feb 24, 2021 at 7:50 PM Václav Steiner wrote: > > We have three servers running postgres 9.6, master and two slaves feeded by > streaming replication. > On of those slaves we are getting different query results. The replica was > recreated from scratch, but problem persists. > >

Re: Script checking to see what database it's connected to

On Mon, Feb 22, 2021 at 9:00 AM Ron wrote: > > On 2/21/21 5:26 PM, Julien Rouhaud wrote: > > On Mon, Feb 22, 2021 at 7:19 AM Ron wrote: > > Thus, I want to add a bit to the top of the script, something like this: > > \if :DBNAME = postgres > echo "must

Re: Script checking to see what database it's connected to

On Mon, Feb 22, 2021 at 7:19 AM Ron wrote: > > Thus, I want to add a bit to the top of the script, something like this: > > \if :DBNAME = postgres > echo "must not run in postgres" > exit > \endif > > However, I can't seem to find the magic sauce. You have to use a dedicated variable.

Re: ubuntu 18: PostgreSQL does not start. how can I totally remove and reinstall it

On Sun, Jan 24, 2021 at 2:58 PM rob...@redo2oo.ch wrote: > > root@elfero-test:~/scripts# pg_lsclusters > Ver Cluster Port Status OwnerData directory Log file > 10 main5433 online postgres /var/lib/postgresql/10/main > /var/log/postgresql/postgresql-10-main.log > [...] >

Re: PG 13 trusted extensions and pg_available_extensions

On Sat, Sep 26, 2020 at 10:11 PM Tom Lane wrote: > > Julien Rouhaud writes: > > So, apparently pg_available_extension_versions already had those > > fields so all the required infrastructure was already there. I just > > added the exact same fields to pg_available_

Re: PG 13 trusted extensions and pg_available_extensions

On Fri, Sep 25, 2020 at 2:51 PM Daniel Westermann (DWE) wrote: > > On Thu, Sep 24, 2020 at 10:58 AM Michael Paquier wrote: > >> > >> On Wed, Sep 23, 2020 at 03:28:45PM +, Daniel Westermann (DWE) wrote: > >> > I was playing a bit with trusted extensions and wondered if there is > >> > a

Re: PG 13 trusted extensions and pg_available_extensions

On Thu, Sep 24, 2020 at 10:58 AM Michael Paquier wrote: > > On Wed, Sep 23, 2020 at 03:28:45PM +, Daniel Westermann (DWE) wrote: > > I was playing a bit with trusted extensions and wondered if there is > > a reason that the "trusted" flag is not exposed in pg_available_extensions. > > I

Re: Track pgsql steps

On Wed, Jul 29, 2020 at 7:58 PM Adrian Klaver wrote: > > On 7/29/20 8:44 AM, Olivier Leprêtre wrote: > > Hi, > > > > I have a rather long pgsql procedure and I would like to detect which > > step is currently executing (subscript 1,2,3…). Due to transaction > > isolation, it’s not possible to

Re: survey: psql syntax errors abort my transactions

On Fri, Jul 3, 2020 at 7:46 PM Ron wrote: > > On 7/3/20 1:54 AM, Laurenz Albe wrote: > > This is my favorite example why I like the way PostgreSQL does things: > > > > /* poor man's VACUUM (FULL) */ > > BEGIN; > > CREATTE TABLE t2 AS SELECT * FROM t1; > > DROP TABLE t1; > > ALTER TABLE t2 RENAME

Re: Replication conflicts despite hot_standby_feedback = on?

On Wed, Jun 3, 2020 at 2:05 PM Laurenz Albe wrote: > > On Wed, 2020-06-03 at 13:41 +0200, Julien Rouhaud wrote: > > > I'm seeing the following at a customer site: > > > > > > SELECT confl_tablespace, confl_lock, confl_snapshot, confl_bufferpin,

Re: Replication conflicts despite hot_standby_feedback = on?

On Wed, Jun 3, 2020 at 1:07 PM Laurenz Albe wrote: > > I'm seeing the following at a customer site: > > SELECT confl_tablespace, confl_lock, confl_snapshot, confl_bufferpin, > confl_deadlock > FROM pg_stat_database_conflicts > WHERE datname = 'something' \gx > > -[ RECORD 1 ]+-- >

Re: Vanishing unique constraint

On Mon, Jun 1, 2020 at 1:23 PM Bernhard Beroun wrote: > > Am Montag, Juni 01, 2020 12:56 CEST, schrieb Julien Rouhaud > : > > On Mon, Jun 1, 2020 at 11:15 AM Bernhard Beroun wrote: > > > > Hello, > > > > I am experiencing a strange thing on my product

Re: Vanishing unique constraint

Hello, On Mon, Jun 1, 2020 at 11:15 AM Bernhard Beroun wrote: > > Hello, > > I am experiencing a strange thing on my production database server, which I > can't explain. > > On my production database server, there is a table called "label_suggestion" > which has a unique constraint on the

Re: increase of xact_commit vs txid_current

On Thu, May 7, 2020 at 7:01 PM wrote: > > Hi, > > I am confused, the documentation says for pg_stat_database > xact_commit Number of transactions in this database that have > been committed > and somewhere else > txid_current()get current transaction ID, assigning a new one > if

Re: Replication issue

On Fri, Apr 17, 2020 at 4:02 PM Adrian Klaver wrote: > > On 4/17/20 6:31 AM, Sonam Sharma wrote: > > I have setup db replication. And added below parameter in jdbc file . > > The connection is failing and it's throwing error like db doesn't exist > > on secondary server. > > > >

Re: Recursive Queries

On Thu, Apr 16, 2020 at 2:49 PM Rob Northcott wrote: > > From: Alex Magnum > > What I would like to do is to create a table as shown below that displays the > counts per our for the past n dates. > > > > I can do this with a function but is there an easy way to use recursive > queries? > >

Re: Log Unique Queries without Params?

On Sun, Apr 12, 2020 at 6:51 AM Chris Morris wrote: > > I have a local script I've written that will scan a log of PG queries to > extract out unique queries without any specific parameter data. For example, > if these 2 queries are actually run: > > SELECT * FROM foo where bar = 1; > SELECT *

Re: Why there is 30000 rows is sample

Hi, On Sat, Apr 04, 2020 at 10:07:51AM +0300, Andrus wrote: > Hi! > > vacuumdb output: > > vacuumdb: vacuuming database "mydb" > INFO: analyzing "public.mytable" > INFO: "mytable": scanned 2709 of 2709 pages, containing 10834 live rows and > 0 dead rows; 10834 rows in sample, 10834 estimated

Re: PG12 autovac issues

On Fri, Mar 27, 2020 at 02:12:04PM +0900, Michael Paquier wrote: > On Thu, Mar 26, 2020 at 09:46:47AM -0500, Justin King wrote: > > Nope, it was just these tables that were looping over and over while > > nothing else was getting autovac'd. I'm happy to share the full log > > if you'd like. > >

Re: PG12 autovac issues

Hi, On Mon, Mar 23, 2020 at 09:23:03AM -0700, Andres Freund wrote: > Hi, > > On 2020-03-23 16:22:47 +0100, Julien Rouhaud wrote: > > On Fri, Mar 20, 2020 at 12:03:17PM -0700, Andres Freund wrote: > > > Hi, > > > > > > On 2020-03-20 12:42:31 -05

Re: PG12 autovac issues

On Fri, Mar 20, 2020 at 12:03:17PM -0700, Andres Freund wrote: > Hi, > > On 2020-03-20 12:42:31 -0500, Justin King wrote: > > When we get into this state again, is there some other information > > (other than what is in pg_stat_statement or pg_stat_activity) that > > would be useful for folks

Re: strange locks on PG 11 with Golang programs

On Mon, Mar 09, 2020 at 10:21:23AM +0100, Josef Machytka wrote: > On Mon, 9 Mar 2020 at 09:58, Julien Rouhaud wrote: > > > The query displayed is just the query currently executing, but if the > > connection is in a transaction the problematic lock could have been >

Re: strange locks on PG 11 with Golang programs

On Mon, Mar 09, 2020 at 09:51:21AM +0100, Josef Machytka wrote: > We are lately experiencing very strange locks on PostgreSQL 11.7 when we > process ETL tasks using our programs in Go 1.13.8 using standard libraries > sql and pq. > > ETL task has to rename tables but PostgreSQL shows that this

Re: Enabling extensions on a compiled instance of postgresql 12.1

On Thu, Feb 13, 2020 at 01:34:55PM +, Sterpu Victor wrote: > Hello > > I compiled from source postgresql 12.1 and all went fine but when I try to > restore my DB I can see that I have 3 extensions missing: uuid-ossp, > btree_gist, tablefunc. > I tried to run: CREATE EXTENSION "uuid-ossp"; and

Re: Query returns no results until REINDEX

On Tue, Feb 11, 2020 at 8:33 PM Peter Geoghegan wrote: > > On Sun, Feb 9, 2020 at 12:50 PM Colin Adler wrote: > > Looks like it found something. I checked out the contrib/pageinspect docs > > but > > wasn't too sure what to run. Are incompatible libc versions causing btree > > corruption

Re: Question on upgrading postgresql from 10.7 to 11.5

On Wed, Dec 25, 2019 at 3:42 PM Lu, Dan wrote: > > Hello, > > I am trying to upgrade my PG instance from 10.7 to 11.5. > > I got an error in doing so. Any idea what I am missing? > > Example: pg_upgrade -d /hostname/pg/dpoc/data -D /hostname/pg/dpoc115/data -b > /hostname/pg/PostgreSQL-10.7/bin

Re: Linux Mint - cannot find auto_explain extension

Hi, On Thu, Aug 1, 2019 at 7:53 PM Keith Roberts wrote: > > postgres=# CREATE EXTENSION auto_explain; > ERROR: could not open extension control file > "/usr/share/postgresql/9.5/extension/auto_explain.control": No such file > or directory > postgres=# > > Does anyone have any idea what package

Re: Default ordering option

On Fri, Jul 26, 2019 at 9:53 AM Cyril Champier wrote: > > Adrian: > >> Are you really looking for a pseudo-random name? > > > No, the code I pasted was an existing production bug: the last_name should > have been unique, so the selected patient would always be the same. > This should have been

Re: Query not producing expected result

On Wed, May 1, 2019 at 6:27 PM Chuck Martin wrote: > > I need help figuring out why a query is not returning the records I expect it > to. I'm searching on a DateTime column (timestamp without time zone - not > nullable). The query includes: > > AND event.Primaryresp_fkey = 511 AND

Re: ERROR: XX000: cannot update SecondarySnapshot during a parallel operation

On Thu, Mar 14, 2019 at 4:59 PM Paul Ramsey wrote: > > On Thu, Mar 14, 2019 at 8:43 AM Julien Rouhaud wrote: > > > > On Thu, Mar 14, 2019 at 3:25 PM Adrian Klaver > > wrote: > > > > > > On 3/14/19 6:14 AM, Julien Rouhaud wrote: > >

Re: ERROR: XX000: cannot update SecondarySnapshot during a parallel operation

On Thu, Mar 14, 2019 at 3:25 PM Adrian Klaver wrote: > > On 3/14/19 6:14 AM, Julien Rouhaud wrote: > > On Thu, Mar 14, 2019 at 1:20 PM fuzk wrote: > >> > >> Dear Adrian, > >> > >> My setting is as following. > >> > >> max_parall

Re: Re: ERROR: XX000: cannot update SecondarySnapshot during a parallel operation

On Thu, Mar 14, 2019 at 1:20 PM fuzk wrote: > > Dear Adrian, > > My setting is as following. > > max_parallel_workers_per_gather=32 > > I am looking forward to hearing from you. What version of postgres and what version of postgis are you using ? > At 2019-03-13 22:31:11, "Adrian Klaver"

Re: xmin and very high number of concurrent transactions

On Wed, Mar 13, 2019 at 9:50 AM Laurenz Albe wrote: > > Vijaykumar Jain wrote: > > I was asked this question in one of my demos, and it was interesting one. > > > > we update xmin for new inserts with the current txid. > > now in a very high concurrent scenario where there are more than 2000 > >

Re: why not using a mountpoint as PGDATA?

On Wed, Feb 27, 2019 at 12:22 PM Luca Ferrari wrote: > > What's wrong with using a mountpoint? You can see most obvious reasons at https://bugzilla.redhat.com/show_bug.cgi?id=1247477

Re: ora2pg and invalid command \N

On Fri, Mar 16, 2018 at 8:28 PM, Adrian Klaver wrote: > On 03/16/2018 11:36 AM, Charlin Barak wrote: >> >> Thanks for your response. >> >> the NULL values from Oracle were indeed replaced by \N in the data output. >> How do I go about loading this file? I hope I do not

<    1   2