Re: Issue with timestamp without time zone datatype with default value as now()/localtimestamp

> On Jul 11, 2020, at 3:17 AM, Deepika S Gowda wrote: > >  > Hi Adrian, > > System timezone. > [postgres@pvodcdbst0001uk ~]$ timedatectl > Local time: Fri 2020-07-10 15:44:37 BST > Universal time: Fri 2020-07-10 14:44:37 UTC > RTC time: Fri 2020-07-10 14:44:37 >Time zo

Re: 2 million queries against a table

On 7/15/20 9:38 AM, Tom Lane wrote: Adam Sanchez writes: I need to run 2 million queries against a three columns table t (s,p,o) which size is 10 billions rows. The data type of each column is string. The server has 512G RAM, 32 cores and 14T SSD (RAID 0) Only two types of queries: se

Re: Errors after cloning OS to new disk under Hyper-V

On 7/17/20 8:51 PM, ertan.kucuko...@1nar.com.tr wrote: Hello, This is PostgreSQL v10.10 64Bit, running on Windows 10 64Bit. One user, by himself, without asking for help, cloned his OS from one disk to Hyper-V disk. Old disk was not accessed, OS was shutdown while he is doing the cloning.

Re: Slow or Cannot Connect to PostgreSQL Instance Service on Windows 10

> On Jul 22, 2020, at 4:58 PM, Surya Widyanto wrote: > > On 23-07-2020 05:26, Adrian Klaver wrote: >> On 7/22/20 10:24 AM, Surya Widyanto wrote: >>> Hi, >>> >>> I have PostgreSQL v12.3 (x64) installed on my Laptop running Windows 10 >>> Home Single Language (64-bit). >>> In two or three ti

Re: Need a depth information about bloat

> On Aug 9, 2020, at 9:01 PM, Rama Krishnan wrote: > >  > Hi Mentors, > > > I am searching for an understanding of bloat in PostgreSQL > > What is bloat? > > How does it work? > > What is the use of bloat? > > Limitations of bloat? > > Thanks > > Rama Krishnan Bloat is not a feature

Re: UUID or auto-increment

On 8/10/20 10:53 AM, Stephen Frost wrote: Greeitngs, * Ron (ronljohnso...@gmail.com) wrote: On 8/10/20 11:38 AM, Ravi Krishna wrote: Finally UUID results in write amplication in wal logs.  Keep that in mind if your app does lot of writes. Because UUID is 32 bytes, while SERIAL is 4 bytes?

Re: How is PG replication typically used to create a High Availability (HA) config ?

On 8/11/20 2:57 PM, David Gauthier wrote: Thanks for the response Paul :-) Our code is actually perl which uses DBI which has functions to ping a DB on a specific server and connect to it. But my question was more along the lines  of whether or not the onus to do this sort of thing typicall

Re: How to write such a query

> On Sep 18, 2020, at 1:45 PM, Ken Tanzer wrote: > > > How to find what the primary key (or UNIQUE identifier) value is > > for row 5 in the recordset? > > You're missing the point: as mentioned before, there is no "row 5". To > update the 5th record that you've fetched, you increment a count

Re: Can I get some PostgreSQL developer feedback on these five general issues I have with PostgreSQL and its ecosystem?

That would entail building an AI into the code that would deal with all the possible OS(versions), Postgres(versions), hardware permutations. I... guess. If "AI" means "a series of ifs". Which is what software... is? I doubt that people who can make the world's most advanced open

Re: DOMAIN/composite TYPE vs. base TYPE

> On Sep 28, 2020, at 3:14 PM, Joe Abbate wrote: > > Hello, > > I'm considering creating a TYPE for what may be called a "possibly imprecise > date" (pidate). The most obvious use is for recording dates such as births > or deaths of historical individuals, where we may know that someone di

Re: Gurjeet Singh Index Adviser User Interface

On 9/29/20 3:46 PM, Yessica Brinkmann wrote: I will greatly appreciate a help with this topic please. I really need to use that interface to be able to test my thesis. And I am not being able to use. Best regards, Yessica Brinkmann I didn't see any reported error messages in your post. An

Re: Restoring a database problem

> On Sep 30, 2020, at 6:11 PM, Bruce Momjian wrote: > > On Thu, Oct 1, 2020 at 01:00:21PM +1300, Glen Eustace wrote: >> I have had to do this so rarely and it has almost always been in a bit of a >> panic so may well be missing something really obvious. >> >> What I want to know is how to qu

Re: Doubt in pgbouncer

> On Oct 1, 2020, at 5:42 PM, Rama Krishnan wrote: > > Hi Friends, > > By using pg bouncer can we split read and queries > > Thank > Rk Did you mean reads vs writes?

Re: Profile Creation

On 10/2/20 2:45 PM, David G. Johnston wrote: On Fri, Oct 2, 2020 at 1:43 PM Brajendra Pratap Singh mailto:singh.bpratap...@gmail.com>> wrote: How can we create a user profile in open postgresql db? ? CREATE TABLE user_profile (...); INSERT INTO user_profile VALUES (...); David J. I'

Re: [SOLVED] Re: UUID generation problem

On 10/6/20 9:35 AM, James B. Byrne wrote: Thank you all for the help. This is what ultimate resolved the issue for me: [root@accounting-2 ~ (master)]# psql -E --dbname=idempiere --username=postgres --host=localhost Password for user postgres: psql (11.8) Type "help" for help. idempiere(54

Re: [SOLVED] Re: UUID generation problem

On 10/7/20 3:28 PM, Adrian Klaver wrote: On 10/7/20 2:24 PM, Rob Sargent wrote: On 10/6/20 9:35 AM, James B. Byrne wrote: Thank you all for the help.  This is what ultimate resolved the issue for me: [root@accounting-2 ~ (master)]#  psql -E --dbname=idempiere --username=postgres

Re: Any interest in adding match_recognize?

> On Oct 9, 2020, at 10:25 AM, Guyren Howe wrote: > > I just became aware of the SQL 2016 standard match_recognize feature, which > allows for rich pattern matching across groups of rows. It’s a great feature, > currently only supported in Oracle. > > I can find no evidence it’s ever been di

Re: Multi-row insert: error at terminal row.

On 10/29/20 9:43 AM, Rich Shepard wrote: I'm loading data into tables with the 'insert' statement. There are many rows containing values and each is bracketed by parentheses except for the last row. That's terminated with a semicolon, but psql reports an error there: psql:organizations.sql:19

Re: Multi-row insert: error at terminal row. [RESOLVED]

On 10/29/20 2:07 PM, Rich Shepard wrote: On Thu, 29 Oct 2020, Adrian Klaver wrote: Is it just the 50 line version? Adrian, Nope. If it is, what happens if you go back to original 1925 line version and correct the NULL issue in the line 26 and run it again? I'm finding typos and column

Re: JSONB order?

> On Nov 5, 2020, at 8:45 AM, Tony Shelver wrote: > > > > -- Forwarded message - > From: Tony Shelver mailto:tshel...@gmail.com>> > Date: Thu, 5 Nov 2020 at 17:45 > Subject: Re: JSONB order? > To: Christophe Pettus mailto:x...@thebuild.com>> > > > Thanks Christophe, that's w

Re: autovacuum recommendations for Large tables

> > If it helps, I put together a few thoughts and own experience on a blog: > https://sites.google.com/gautherot.net/postgresql/vacuum > > > Hope you find it useful. > -- That URL does not work for me (not even https://sites.google.

Re: pg_upgrade from 12 to 13 failes with plpython2

> On Nov 18, 2020, at 9:29 PM, Bruce Momjian wrote: > > On Wed, Nov 18, 2020 at 10:06:17PM +, Devrim Gunduz wrote: >> Hi, >> >> On Wed, 2020-11-18 at 14:54 -0500, Tom Lane wrote: >>> Uh-huh, so there you have it. These must be leftovers from some >>> pre-extension incarnation of plpython

Re: pg_upgrade from 12 to 13 failes with plpython2

> On Nov 18, 2020, at 9:39 PM, Bruce Momjian wrote: > > On Wed, Nov 18, 2020 at 09:35:20PM -0700, Rob Sargent wrote: >>> pg_upgrade does have some code to handle plpython call handlers in >>> function.c:get_loadable_libraries(); >>> >>>

Re: PL/java

On 12/7/20 8:25 AM, Adrian Klaver wrote: On 12/7/20 7:18 AM, Hemil Ruparel wrote: I want to say this. I never liked any extension language. It's like Java is not meant to interact with databases. The JDBC folks might disagree. That interaction is strictly sql to db, data to app.  As it sh

Re: PL/java

rectly from the app (middle-ware). I write relatively large blocks (millions of records) to naked temporary tables and then let straight sql read from there and insert into final tables in chunks. Un Saludo m -------- *From:* Rob

Re: Extracting data from jsonb array?

OK, let me try asking again.  (I'm trying to actually get something that works.)  So given an example like this: CREATE TEMP TABLE foo (   id INTEGER,   js  JSONB ); INSERT INTO foo (id,js) VALUES (1, '[ {"key":"r1kval","key2":"r1k2val"}, {"key":"r1kval2","key2":"r1k2val2"} ]'); INSERT INTO

Re: Extracting data from jsonb array?

On 12/7/20 6:17 PM, David G. Johnston wrote: On Mon, Dec 7, 2020 at 6:13 PM Rob Sargent <mailto:robjsarg...@gmail.com>> wrote: postgres=# select id, array_agg(fa) from (select id, (jsonb_array_elements(js)->'key') as fa from foo) g group by id

Re: User input to queries

On 12/9/20 10:03 AM, Rich Shepard wrote: While I develop the application's GUI I use the database from the command line (psql). While some queries are self-contained others need user input. I've not found a search term that locates this information in the postgres-12 user manual and my web sea

Re: User input to queries

On 12/9/20 11:10 AM, Rich Shepard wrote: On Wed, 9 Dec 2020, Paul Förster wrote: maybe you're looking for this? https://stackoverflow.com/a/7389606 Paul, That looks very useful and I'll try the provided answers. Looking at the postgres-12 doc I cannot find a command 'PROMPT' anywhere in

Re: SQL group by help

What > On Dec 11, 2020, at 10:24 AM, Chris Stephens wrote: > >  > I'm trying to create a visual representation of a 6x8 grid of samples on a > rack using the following SQL format: > > with rack_display as ( > select sr.ts rack_ts > , sr.rack_id > , r.rack_barcode >

Re: Improving performance of select query

> On Dec 14, 2020, at 4:47 AM, Thomas Kellerer wrote: > > Karthik Shivashankar schrieb am 14.12.2020 um 12:38: >> I have a postgres(v9.5) table named customer holding 1 billion rows. >> It is not partitioned but it has an index against the primary key >> (integer). I need to keep a very few re

Re: Improving performance of select query

> On Dec 14, 2020, at 10:37 AM, Muhammad Bilal Jamil > wrote: > > I think you can also increase the query performance by creating indexes? > > > OP said there was a key on the target (large) table. I’m not sure there’s much of a win in indexing 10K ids. > >

Re: Possible trigger bug? function call argument literalised

> On Jan 3, 2021, at 10:08 AM, Thiemo Kellner > wrote: > > Hi > > I created a table with trigger and according trigger and trigger function as > > drop table if exists CALCULATION_METHOD cascade; > create table CALCULATION_METHOD (ID > uuid >

Re: Is there a good discussion of optimizations?

On 1/7/21 2:48 PM, Guyren Howe wrote: On Jan 7, 2021, 13:42 -0800, Florents Tselai , wrote: Apologies for the shameless self-promotion :) Around a year ago I collected my thoughts on this topic. You can read the post here Modern Data Practice and the SQL Tradition

Re: Define hash partition for certain column values

On 1/12/21 8:51 AM, Голубева Яна wrote: Values for the key partitioning column are generated randomly and I can't predict their distribution between ranges. If I just create some ranges I won't have any guarantee that partitions will have similar amount of data. It is possible that I will hav

Re: Do we need a way to moderate mailing lists?

I watched this sadness play out.  It had nothing to do with the OP's original question. Rather someone with feelings hurt from a separate thread (table correlation) felt the need drag that squabble over to the focal issue here (tools).  I was tempted bring up the COC but I'm not really a believ

Re: Do we need a way to moderate mailing lists?

you are not entitled to shit. You are probably being paid to work on that project. We are not. Your problem. Fix it yourself. Or at least have to courtesy to google it. On Sat, Jan 16, 2021 at 12:06 PM Rob Sargent <mailto:robjsarg...@gmail.com>> wrote: I watched this sadness pl

Re: Do we need a way to moderate mailing lists?

> On Jan 16, 2021, at 11:00 AM, Rich Shepard wrote: > > On Sat, 16 Jan 2021, Paul Förster wrote: > >> Or use duckduckgo or some other search engine. > > +1 > > Rich > And I apologize for a couple of toppers in this thread. Recent changes to mailer >

Re: Do we need a way to moderate mailing lists?

> On Jan 16, 2021, at 12:26 PM, Rich Shepard wrote: > > On Sat, 16 Jan 2021, Bruce Momjian wrote: > >> Bruce, > > This has become most common over the past few years. It may be the result of > people using their pocket computers (sold as 'mobile phones') for email and > they don't take the

Re: Do we need a way to moderate mailing lists?

Top/Bottom points um, er elided. Mail has always been well threaded, retaining which message lead to which replies.  How did we get away from relying on that (naked posting)?

Re: Do we need a way to moderate mailing lists?

> Yes it’s unfortunately highly probable that someone asking (yet again) how to tune postgres will not first search for how to formulate a question. Not to say such info as you, David, and others propose should not be made available as it certainly should but that we may have to accept such nu

Re: Do we need a way to moderate mailing lists?

Has that ever been a thing? Quoting (and trimming) the message you are replying to has been normal since at least the late 1980's (when I started to use E-Mail). Peter, My memory of email spans only a slightly larger period but no doubt yours is better than mine.  My main reader was emac's rma

localhost ssl

I will need to enforce ssl/tls in my production environment so I thought I would try setting things up on localhost to see how that went. Then I noticed that my successful connections from "/usr/lib/postgresql/12/bin/psql -U postgres -h localhost -P pager=off postgres" report: psql (12.

Re: localhost ssl

> Also I'm guessing you have ssl = on in postgresql.conf and server cert setup. Sorry, here's a likely explaination from postgresql.conf ssl = on #ssl_ca_file = '' ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem' #ssl_crl_file = '' ssl_key_file = '/etc/ssl/private/ssl-cert-sna

Re: localhost ssl

No, as I really have no idea what: "In production I hope to name the role with each connection as I want the search_path set by the connecting role. ..." means? My apologies: I rely on the search_path being set according to the role (--user). Perhaps what I was missing is that the connec

Re: localhost ssl

Check out this section: https://www.postgresql.org/docs/12/ssl-tcp.html#SSL-CLIENT-CERTIFICATES "...  the cn (Common Name) in the certificate matches the user name or an applicable mapping." This section spells out what is needed for the various forms of client cert SSL authentication.

Re: localhost ssl

On 1/22/21 3:54 PM, Adrian Klaver wrote: On 1/22/21 2:48 PM, Rob Sargent wrote: Honest, I've been reading 18.9 but as you can see it uses CN for host and then 20.12 suggests using CN for role. Difference between server certificate and client certificate. To get a handle on this is

Re: count(*) vs count(id)

On 2/1/21 4:53 PM, Matt Zagrabelny wrote: Greetings, Is there a preferred method of counting rows? count(*) vs count(field_name) I have used count(*) for a long time and am hitting an inconsistency. At least it is an inconsistency to me (I'm sure there is an explanation)... INCORRECT OUT

Re: vacuumdb not letting me connect to db

On 2/5/21 9:11 AM, Ron wrote: Obviously... don't use 300 threads. No, no Ron. Clearly the answer is more CPUs

Re: Encryption of Data Specific to a Tenant in PostgreSQL database | General Idea

> On Feb 10, 2021, at 6:45 PM, Tim Cross wrote: > > > Jagmohan Kaintura mailto:jagmo...@tecorelabs.com>> > writes: > >> HI All, >> >> For POstgreSQL database to store data for multiple tenants, the approach >> decided was to have >> Shared Database (Holding data for all tenants) >> =>

Re: Encryption of Data Specific to a Tenant in PostgreSQL database | General Idea

DBA=~super user If your clients have dba privs you need separate (vertical)servers

certs in connection string

I’m confused, as usual, about using a cert in a connection string. I wish to connect form a “middle ware” piece to PG on be half of various clients. Does each client need a corresponding cert/key or is the certification intended to say the sending machine is who it says it is (thereby needing on

Re: certs in connection string

On 2/15/21 8:23 AM, Laurenz Albe wrote: On Sat, 2021-02-13 at 09:57 -0700, Rob Sargent wrote: I’m confused, as usual, about using a cert in a connection string. I wish to connect form a “middle ware” piece to PG on be half of various clients. Does each client need a corresponding

Re: Insert into on conflict, data size upto 3 billion records

On 2/15/21 11:41 AM, Karthik K wrote: exactly, for now, what I did was, as the table is already partitioned, I created 50 different connections and tried updating the target table by directly querying from the source partition tables. Are there any other techniques that I can use to speed th

Re: Insert into on conflict, data size upto 3 billion records

On 2/15/21 12:22 PM, Karthik K wrote: yes, I'm using \copy to load the batch table, with the new design that we are doing, we expect updates to be less going forward and more inserts, one of the target columns I'm updating is indexed, so I will drop the index and try it out, also from your

Re: Error with pg_dump (of data), with --role

On 2/18/21 6:18 PM, Ken Tanzer wrote: Hi.  I'm trying to do a data dump with pg_dump using RLS and --set-role, but am getting an error, and I'm not understanding why. With this command, run as postgres: pg_dump -p 5433 -O --role=rcafe_TACOMA --enable-row-security --column-inserts -a -f ~/a

Re: Error with pg_dump (of data), with --role

> On Feb 18, 2021, at 8:00 PM, Ken Tanzer wrote: > >  > > >> On Thu, Feb 18, 2021 at 5:23 PM Rob Sargent wrote: >> >> > >> > There is only one schema, public. >> > >> > >> >> I suspect it is because "

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

On 2/21/21 4:18 PM, Ron wrote: Postgresql 12.5 I've got scripts which can run on multiple database (dev, test, QA, Integration, Training, etc, etc), so of course I've got to run them like "psql my_db_name -f script.sql". Of course, I sometimes forget to specify the database name, and so

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

Take it up a notch?  Write a script which takes the dbname and the script name: /pcode/ #!/bin/bash -e if [[ $# -ne 2 ]]; then echo "missing arg(s)"; exit 2; fi dbn=$1; shift; sql=$1; shift; psql --dbname $dbn --file $sql /pcode/ I thought of that, yet so earnestly want avoid Yet Anoth

Re: Postgres Analog of Oracle APPEND hint

On 2/25/21 9:26 AM, Rumpi Gravenstein wrote: Unfortunately, I am not looking to load from an external source.  My process is moving data from source PostgreSQL tables to target PostgreSQL tables. Are you trying to duplicate the source tables in the target tables? If so, there are replication

Re: PostgreSQL Licensing Question for pg_crypto and tablefunc extensions

On 2/26/21 11:39 AM, Rumpi Gravenstein wrote: Tom Thanks for the quick reply.  What you stated is what I was expecting. I've searched high and low for the documentation that proves that point -- something I need to do to satisfy our legal team.  Any thoughts on under which rock that license

self-made certs not quite right

I'm trying to follow instrux in V12:18.9.5 Creating Certificates. [1] I'm stuck in my basement so all references to "/CN=FQN" have been set to $(hostname), just the hostname, because $(domainname) returns "(none)" which I presume is akin to null. With my newly minted certs and keys using psql

Re: ERROR: extra data after last expected column

On 3/7/22 16:48, scott macri wrote: On Mon, Mar 7, 2022, 6:42 PM Rob Sargent wrote: On 3/7/22 16:33, scott macri wrote: No luck Bummer.  Best to bottom post or in-line comment on this forum. On Mon, Mar 7, 2022, 4:58 AM Rob Sargent wrote: On 3/7/22 02:08

Re: [EXTERNAL] Re: Can you install/run postgresql on a FIPS enabled host?

On 3/21/22 16:55, McDermott, Becky wrote: It looks like jdbc-1.15.2.jar Named in the stack trace: postgresql-42.2.5.jar

Re: [EXTERNAL] Re: Can you install/run postgresql on a FIPS enabled host?

On 3/21/22 17:02, McDermott, Becky wrote: Thank you. I should deploying a very simple container that has psql installed and make sure I can connect in a simple way with a username/password. If this works, then the database is fine and it would definitely point to a java issue. Thanks! I li

Re: Leading comments and client applications

As far as the comparison behavior goes, psql's parser strips comments that start with double dashes, for $obscure_reasons. That story aught to be worth a $beer or two The server is perfectly capable of ignoring those by itself, though. (Awhile back I tried to remove that psql behavior, but

Re: Leading comments and client applications

On 3/25/22 13:30, Tom Lane wrote: Rob Sargent writes: As far as the comparison behavior goes, psql's parser strips comments that start with double dashes, for $obscure_reasons. That story aught to be worth a $beer or two Hmm. The original reasoning is lost in the mists of time; I dug i

Re: How long does iteration over 4-5 million rows usually take?

> On Apr 1, 2022, at 10:18 PM, Ron wrote: > >  On 4/1/22 20:34, Shaozhong SHI wrote: >> >> I have a script running to iterate over 4-5 million rows. It keeps showing >> up in red in PgAdmin. It remains active. >> >> How long does iteration over 4-5 million rows usually take? 4-5 million

Re: Resources on modeling ordered hierachies?

On 4/7/22 10:55, Peter J. Holzer wrote: On 2022-04-07 17:45:49 +1200, Tim Uckun wrote: There a tons of articles about how to model hierarchies in SQL but I haven't seen any about dealing with hierarchies where the order of children is important. The canonical example is a simple outline 1. 1.1

How easy is it to lose permissions in 'public' schema?

I'm using postgres 14 in a database recently brought over from postgres 10.  (I did not do the bringing over.) In my set-up, I partition datasets by schema, create a role per schema and part of that is this explicit permission granting (from superuser): p\g grant all on all tables in sc

Re: How easy is it to lose permissions in 'public' schema?

> On Apr 11, 2022, at 6:51 PM, Adrian Klaver wrote: > > On 4/11/22 17:34, Tom Lane wrote: >> Adrian Klaver writes: >>>> On 4/11/22 16:10, Rob Sargent wrote: >>>>> I've just bumped into this. >>>>> >>>>> ba

function defined (or not), more worries on version 10->14 upgrade

The function in question is working but I'm having trouble getting at it's current definition and the possibility that there are more v10->v14 issues in this database.  The last ERROR: below seems to me "a system thing"(tm) barnard=# \df genome_threshold_mono  

Re: function defined (or not), more worries on version 10->14 upgrade

> On Apr 15, 2022, at 11:14 AM, Tom Lane wrote: > > Rob Sargent writes: >> The function in question is working but I'm having trouble getting at >> it's current definition and the possibility that there are more v10->v14 >> issues in this databas

Re: function defined (or not), more worries on version 10->14 upgrade

On 4/15/22 14:22, Adrian Klaver wrote: On 4/15/22 10:14, Tom Lane wrote: Rob Sargent writes: The function in question is working but I'm having trouble getting at it's current definition and the possibility that there are more v10->v14 issues in this database.  The last ERROR

Re: function defined (or not), more worries on version 10->14 upgrade

On 4/15/22 15:18, David G. Johnston wrote: On Fri, Apr 15, 2022 at 2:04 PM Rob Sargent wrote: but have not found a combination of name/args for genome_threshold_mono(text,text,double precision) Per the fine documentation: \df[anptwS+] [ pattern [ arg_pattern ... ] ] So the correct

Re: function defined (or not), more worries on version 10->14 upgrade

On 4/15/22 15:52, David G. Johnston wrote: On Fri, Apr 15, 2022 at 2:30 PM Rob Sargent wrote: On 4/15/22 15:18, David G. Johnston wrote: On Fri, Apr 15, 2022 at 2:04 PM Rob Sargent wrote: but have not found a combination of name/args for genome_threshold_mono

Re: Help with large delete

On 4/16/22 07:25, Perry Smith wrote: Currently I have one table that mimics a file system. Each entry has a parent_id and a base name where parent_id is an id in the table that must exist in the table or be null with cascade on delete. I’ve started a delete of a root entry with about 300,000

Re: Help with large delete

On Apr 16, 2022, at 12:24 PM, Perry Smith wrote:On Apr 16, 2022, at 12:57, Jan Wieck wrote:Make your connection immune to disconnects by using something like the screen utility.Exactly… I’m using emacs in a server (daemon) mode so it stays alive.  Then I do “shell” within it.I u

Re: oldest xmin is far in the past :: BUT xmin is not available in system

On 4/19/22 00:06, David G. Johnston wrote: On Monday, April 18, 2022, bhargav kamineni wrote: Hi Team, It seems vacuum is behaving somewhat weird on postgres database , observing below HINTS on the vacuum logs WARNING:  oldest xmin is far in the past HINT:  Close open t

psql timeout: who's waiting for whom

I'll be asking 'those in charge' to reset some timeout/keep-alive setting /somewhere/. I need help in hopefully naming the correct setting.  The server is running version 14 Am I dealing with a client issue, a server issue or a mis-match in host/postgres configuration values?  If I cannot hav

Re: Are stored procedures/triggers common in your industry

On 4/20/22 13:18, Guyren Howe wrote: I’ve really only ever worked in web development. 90+% of web developers regard doing anything at all clever in the database with suspicion. I’m considering working on a book about implementing business logic in Postgres, and I’m curious about how common th

Re: Fresh eyeballs needed: input into error

> On Apr 27, 2022, at 5:43 AM, Rich Shepard wrote: > > On Wed, 27 Apr 2022, Laurenz Albe wrote: > > > From time to time I mis-type a character or make another error and psql > responds with a different prompt and no explicit explanation. I'll then type > C-c and get the default => prompt ba

list tablespaces named in custom format dump

Using pg 14, is it easy (possible) to list the tablespaces used in a custom format dump. Are the definitions included? Thanks,

Re: list tablespaces named in custom format dump

On 5/23/22 10:50, Adrian Klaver wrote: On 5/23/22 9:45 AM, Rob Sargent wrote: Using pg 14, is it easy (possible) to list the tablespaces used in a custom format dump. Are the definitions included? https://www.postgresql.org/docs/14/app-pgdump.html "pg_dump only dumps a single databas

Re: list tablespaces named in custom format dump

On 5/23/22 11:00, Adrian Klaver wrote: On 5/23/22 9:54 AM, Rob Sargent wrote: On 5/23/22 10:50, Adrian Klaver wrote: Roger that, but does the dump of a database name the tablespaces from which the objects were retrieved? And if so, is that accessible? https://www.postgresql.org/docs/14

Re: list tablespaces named in custom format dump

On 5/23/22 11:07, Adrian Klaver wrote: On 5/23/22 10:01 AM, Rob Sargent wrote: On 5/23/22 11:00, Adrian Klaver wrote: On 5/23/22 9:54 AM, Rob Sargent wrote: On 5/23/22 10:50, Adrian Klaver wrote: Roger that, but does the dump of a database name the tablespaces from which the objects were

Re: list tablespaces named in custom format dump

On 5/23/22 11:29, Tom Lane wrote: AFAICS, the output produced by -l doesn't mention objects' tablespaces. You could see them by extracting the plain text archive and grepping for object comments that include a tablespace name: pg_restore -s -f - archive_file | grep Tablespace: It looks to me l

Re: list tablespaces named in custom format dump

On 5/23/22 11:51, Adrian Klaver wrote: On 5/23/22 10:19 AM, Rob Sargent wrote: On 5/23/22 11:07, Adrian Klaver wrote: On 5/23/22 10:01 AM, Rob Sargent wrote: On 5/23/22 11:00, Adrian Klaver wrote: On 5/23/22 9:54 AM, Rob Sargent wrote: On 5/23/22 10:50, Adrian Klaver wrote: This is

Re: list tablespaces named in custom format dump

On 5/23/22 12:15, Tom Lane wrote: Rob Sargent writes: Thanks Adrian.  There's enough wiggle room in there for me to at least try the restore. Linus file command tells me db.dump: PostgreSQL custom database dump - v1.13-0 That dump was from a version 10 server.  Any guess at how

Re: list tablespaces named in custom format dump

> On May 23, 2022, at 12:59 PM, Adrian Klaver wrote: > > On 5/23/22 11:31, Rob Sargent wrote: >> On 5/23/22 12:15, Tom Lane wrote: > >>> >>> pg_restore -f - db.dump | head >>> >>> which should provide some comments telling you

Re: list tablespaces named in custom format dump

On 5/23/22 13:47, Adrian Klaver wrote: On 5/23/22 12:26, Rob Sargent wrote: As to older version, how are you installing Postgres? Much appreciated, rjs Yeah, that’s part of the problem. /I’m/ not installing postgres, another group has that control.  I can get the answer for version

connect permission based on database name

Just wondering if I've bumped into some security issue. I'm somewhat surprised that "grant connect to database   to " appears to be stored "by name"? I have the luxury of dropping/recreate databases at will (within limits).  My script for creating a new db also creates a role and grants that

Re: connect permission based on database name

On 5/25/22 08:20, Tom Lane wrote: Rob Sargent writes: Just wondering if I've bumped into some security issue. I'm somewhat surprised that "grant connect to database   to " appears to be stored "by name"? I think you are forgetting that databases have a default

Re: connect permission based on database name

On 5/25/22 08:20, Tom Lane wrote: Rob Sargent writes: Just wondering if I've bumped into some security issue. I'm somewhat surprised that "grant connect to database   to " appears to be stored "by name"? I think you are forgetting that databases have a default

Re: connect permission based on database name

On 5/25/22 08:44, David G. Johnston wrote: On Wednesday, May 25, 2022, Rob Sargent wrote: On 5/25/22 08:20, Tom Lane wrote: Rob Sargent <mailto:robjsarg...@gmail.com> writes: Just wondering if I've bumped into some security issue. I'm somewhat surprised tha

Re: psql 15beta1 does not print notices on the console until transaction completes

On 5/29/22 18:00, Adrian Klaver wrote: On 5/29/22 15:03, Tom Lane wrote: Adrian Klaver writes: On 5/29/22 13:59, Alastair McKinley wrote: I think Tom was able to reproduce this by the sounds of his response? I have not received that post yet. I do see it in the archives. I re-addressed i

Re: Seems to be impossible to set a NULL search_path

On 7/6/22 14:47, Bryn Llewellyn wrote: The problem was my stupid typo: writing « set search_path = 'pg_catalog, pg_temp'; » when I should *not* have typed those single quotes. Now the demo that I'd intended gets the outcome that I'd expected: *select count(*) from pg_class; --<< 39

Re: Get the table creation DDL

> On Jul 10, 2022, at 6:16 PM, Igor Korot wrote: > > Hi, > >> On Sun, Jul 10, 2022 at 7:02 PM Mladen Gogala >> wrote: >> >> On 7/10/22 17:00, Igor Korot wrote: >> >> I understand. >> The Problem is that I need to put this inside the C/ODBC interface for >> my project. >> >> I'm sure it

Re: Get the table creation DDL

Do it properly. Make it part of your coding infrastructure so it’s available in the next environment. You’ll thank yourself How should I do that? Thank you. I don't know what build system you are using.  How do get to a running programme?  Are you alone or on a team? The instruction above o

Re: PostgreSQL 14.4 ERROR: out of memory issues

> On Jul 11, 2022, at 2:50 AM, Aleš Zelený wrote: > >  > Hello, > > we are experiencing out-of-memory issues after Postygres upgrade from 14.3 to > 14.4 on CentOS7. > > OS: CenotOS7 > > No OOM killer messages in the syslog. SWAP is disabled. > > -bash-4.2$ sysctl -q vm.overcommit_memory

<    1   2   3   4   5   6   7   >