Re: Can we get SQL Server-like cross database queries

On Thu, 2020-06-04 at 16:41 +1200, Thomas Munro wrote: > There's no doubt it's useful, and it's also part of the SQL spec, > which says you can do catalog.schema.table. I would guess that we > might get that as a byproduct of any project to make PostgreSQL > multithreaded. That mountain moving

Re: Can we get SQL Server-like cross database queries

On Thu, Jun 4, 2020 at 4:26 PM Ron wrote: > On 6/3/20 2:57 PM, Rob Sargent wrote: > >> On Jun 3, 2020, at 1:46 PM, Tom Lane wrote: > >> Guyren Howe writes: > >>> Is it practical to provide the SQL Server-like feature in Postgres? > >> No. > > That got me chuckling. > > I had just decided not to

Re: Can we get SQL Server-like cross database queries

On 6/3/20 2:57 PM, Rob Sargent wrote: On Jun 3, 2020, at 1:46 PM, Tom Lane wrote: Guyren Howe writes: Is it practical to provide the SQL Server-like feature in Postgres? No. regards, tom lane That got me chuckling. I had just decided not to bother posting, but

Re: Oracle vs. PostgreSQL - a comment on Mysql

On 2020-Jun-03, Martin Mueller wrote: > On the topic of what other databases do better: I much prefer Postgres to > Mysql because it has better string functions and better as well as very > courteous error messages. But MySQL has one feature that sometimes makes me > want to return it: it

Re: pg_dump of database with numerous objects

On 2020-May-31, t...@exquisiteimages.com wrote: > I am now needing to upgrade to a new version of PostgreSQL and I am running > into problems when pg_upgrade calls pg_dump. pg_dump stalled at: "pg_dump: > saving database definition" for 24 hours before I killed the process. > > My pg_class table

Re: A parsing question

Michael Nolan writes: > Recently I was typing in a query in PG 10.4. > What I MEANT to type was: Where xyz >= 2400 > What I actually typed was: Where xyz >- 2400 > The latter was interpreted as 'where xyz > -2400', but I'm wondering if it > shouldn't have thrown an error on an unrecognized

Re: A parsing question

On Wed, Jun 3, 2020 at 3:41 PM Michael Nolan wrote: > Recently I was typing in a query in PG 10.4. > > What I MEANT to type was: Where xyz >= 2400 > > What I actually typed was: Where xyz >- 2400 > > The latter was interpreted as 'where xyz > -2400', but I'm wondering if it > shouldn't have

Re: Oracle vs. PostgreSQL - a comment on Mysql

On Wed, Jun 3, 2020 at 5:21 PM Martin Mueller < martinmuel...@northwestern.edu> wrote: > On the topic of what other databases do better: I much prefer Postgres to > Mysql because it has better string functions and better as well as very > courteous error messages. > Martin, I definitely

Re: Oracle vs. PostgreSQL - a comment

On Sun, May 31, 2020 at 11:26:07PM +1000, Tim Cross wrote: > Yes, even after longer time doing Oracle, I still never felt as > comfortable or across things as much as I do with PG. Started with > Oracle 7 and stayed until 11g and each year, it got worse rather than better. > > After working as a

A parsing question

Recently I was typing in a query in PG 10.4. What I MEANT to type was: Where xyz >= 2400 What I actually typed was: Where xyz >- 2400 The latter was interpreted as 'where xyz > -2400', but I'm wondering if it shouldn't have thrown an error on an unrecognized operator '>-' Thoughts? -- Mike

Re: Oracle vs. PostgreSQL - a comment on Mysql

On 6/3/20 3:21 PM, Martin Mueller wrote: On the topic of what other databases do better: I much prefer Postgres to Mysql because it has better string functions and better as well as very courteous error messages. But MySQL has one feature that sometimes makes me want to return it: it stores

Re: Oracle vs. PostgreSQL - a comment on Mysql

On the topic of what other databases do better: I much prefer Postgres to Mysql because it has better string functions and better as well as very courteous error messages. But MySQL has one feature that sometimes makes me want to return it: it stores the most important metadata about tables in

Re: GPG signing

On Wed, May 27, 2020 at 10:14:46AM +1000, Tim Cross wrote: > > Marc Munro writes: > > > I need to be able to cryptographically sign objects in my database > > using a public key scheme. > > > > Is my only option to install plpython or some such thing? Python > > generally makes me unhappy as

Re: Fine grained permissions on User Mapping

On 6/3/20 4:11 AM, Paul Bonaud wrote: Hi Tom, Thank you very much for your answer. I was worried to get this kind of solution, i.e. “don't be so miserly as not to create a separate one for each privilege level you need.”, however in the case of a remote database **you have no control

Re: Can we get SQL Server-like cross database queries

> On Jun 3, 2020, at 1:46 PM, Tom Lane wrote: > > Guyren Howe writes: >> Is it practical to provide the SQL Server-like feature in Postgres? > > No. > > regards, tom lane > > That got me chuckling. I had just decided not to bother posting, but well, here goes. I

Re: Can we get SQL Server-like cross database queries

Guyren Howe writes: > Is it practical to provide the SQL Server-like feature in Postgres? No. regards, tom lane

Can we get SQL Server-like cross database queries

One of the few really useful features of SQL Server that Postgres doesn’t have is straightforward cross-database queries. You can reference any table in any database on the same server you’re on as database.schema.table. With Postgres, it is necessary to set up a FDW connection between every

RE: Oracle vs. PostgreSQL - a comment

I manage database clusters where the number of databases is a reason not to do logical replication based upgrades, where pg_upgrade is far preferred instead. If this were to be the case, I would be very concerned that a bunch of things would have to change: 1. Shared catalogs would have

Re: Oracle vs. PostgreSQL - a comment

På onsdag 03. juni 2020 kl. 20:07:24, skrev Chris Travers < chris.trav...@gmail.com >: [...] Regardless of what Oracle does, I agree this would be a huge step in the right direction for pg-DBAs. I have absolutely no clue about how much work is required etc.,

Re: Shared memory error

Updated jdbc version also On Wed, Jun 3, 2020, 11:46 PM Sonam Sharma wrote: > > > On Wed, Jun 3, 2020, 11:29 PM Adrian Klaver > wrote: > >> On 6/3/20 7:58 AM, Sonam Sharma wrote: >> > Hi Team, >> > >> > Getting below error when any query is running from jdbc side : >> > >> > Error : could not

Re: Shared memory error

On Wed, Jun 3, 2020, 11:29 PM Adrian Klaver wrote: > On 6/3/20 7:58 AM, Sonam Sharma wrote: > > Hi Team, > > > > Getting below error when any query is running from jdbc side : > > > > Error : could not open memory segment "/PostgreSQL.1511679208" : No such > > file or directory > > > > Yesterday

Re: Oracle vs. PostgreSQL - a comment

On Wed, Jun 3, 2020 at 7:45 PM Andreas Joseph Krogh wrote: > På onsdag 03. juni 2020 kl. 18:50:12, skrev Jeremy Schneider < > schnj...@amazon.com>: > > > On 6/2/20 1:30 PM, Stephen Frost wrote: > >> No, nothing does as PG doesn't support it as we have one WAL stream for > >> the entire cluster.

Re: Shared memory error

On 6/3/20 7:58 AM, Sonam Sharma wrote: Hi Team, Getting below error when any query is running from jdbc side : Error : could not open memory segment "/PostgreSQL.1511679208" : No such file or directory Yesterday I have extended the /dev/shm filesystem and the query ran. But today again the

Re: Shared memory error

I have set the dynamic_shared_memory_type to none which was set as POSIX. For now we are not seeing the error... Can someone please explain what is the impact of setting it as none . On Wed, Jun 3, 2020, 8:28 PM Sonam Sharma wrote: > Hi Team, > > Getting below error when any query is running

Re: Oracle vs. PostgreSQL - a comment

På onsdag 03. juni 2020 kl. 18:50:12, skrev Jeremy Schneider < schnj...@amazon.com >: > On 6/2/20 1:30 PM, Stephen Frost wrote: >> No, nothing does as PG doesn't support it as we have one WAL stream for >> the entire cluster. On 6/2/20 11:38, Ron wrote: > Right.

Shared memory error

Hi Team, Getting below error when any query is running from jdbc side : Error : could not open memory segment "/PostgreSQL.1511679208" : No such file or directory Yesterday I have extended the /dev/shm filesystem and the query ran. But today again the issue is repeating. Can someone please

Re: Unable to find the details of bug fix in 9.6.x minor version.

Sameer Malve writes: > I am seeing in postgresql release notes that below bugs are fix in 9.6.x > version but unable to find the details for about that bug . If you want more detail than is in the release notes, look at the SGML source for the notes to see the commit references, for example

Re: Unable to find the details of bug fix in 9.6.x minor version.

On 6/3/20 2:32 AM, Sameer Malve wrote: Hi Team, I am seeing in postgresql release notes that below bugs are fix in 9.6.x version but unable to find the details for about that bug . Easiest way I found is to go here: https://git.postgresql.org/gitweb/?p=postgresql.git Then go to the tags

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, > > > confl_deadlock > > > FROM

Re: Replication conflicts despite hot_standby_feedback = on?

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, > > confl_deadlock > > FROM pg_stat_database_conflicts > > WHERE datname = 'something' \gx > > > > -[ RECORD 1

Re: Fine grained permissions on User Mapping

Hi Tom, Thank you very much for your answer. I was worried to get this kind of solution, i.e. “don't be so miserly as not to create a separate one for each privilege level you need.”, however in the case of a remote database **you have no control over** it sounds pretty impossible to do. If I

Re: When to use PARTITION BY HASH?

(sticking to pgsql-general) On Tue, Jun 2, 2020 at 7:45 PM Michel Pelletier wrote: > > On Tue, Jun 2, 2020 at 10:17 AM Oleksandr Shulgin < > oleksandr.shul...@zalando.de> wrote: > >> >> I was reading up on declarative partitioning[1] and I'm not sure what >> could be a possible application of

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 ]+-- >

Replication conflicts despite hot_standby_feedback = on?

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 ]+-- confl_tablespace | 0 confl_lock | 0 confl_snapshot | 84990

select count(id) on RDS replica causing high CPU load on RDS master

Hi sweeties, I'm a bit confused about this one, and could use some help from you PG overlords, so I have a RDS 9.5 master 16vcpu/64GB RAM/5000 IOPS which is RDS replicating to a replica with the same specs. The load on the master is roughly 20/30% cpu and there's only some batch queries running

Unable to find the details of bug fix in 9.6.x minor version.

Hi Team, I am seeing in postgresql release notes that below bugs are fix in 9.6.x version but unable to find the details for about that bug . *Fixed in 9.6.11* Fix unexpected timeouts when using wal_sender_timeout on a slow server (Noah Misch) . *Fixed in 9.6.12* 1. A new server parameter

Re: canceling statement due to conflict with recovery after pg_basebackup

At Wed, 3 Jun 2020 10:07:14 +0300, "Andrus" wrote in > Hi! Hi. > Async binary replication hot standby was started after pg_basebackup. > Running query in slave throws error > > ERROR: canceling statement due to conflict with recovery > > Why ? As written in the messages. > 2020-06-03

Re: When to use PARTITION BY HASH?

On Tue, Jun 2, 2020 at 7:47 PM Stephen Frost wrote: > > Please don't cross post to multiple lists without any particular reason > for doing so- pick whichever list makes sense and post to that. > Sorry for the trouble, I should've checked it more carefully. When posting I did think it may be

canceling statement due to conflict with recovery after pg_basebackup

Hi! Async binary replication hot standby was started after pg_basebackup. Running query in slave throws error ERROR: canceling statement due to conflict with recovery Why ? Query should return table and other sizes in decreasing order. How to improve it so that this error does not occur.