Re: [GENERAL] Interesting streaming replication issue

(sorry for the top post, bitchy K9 Mail) James, are you sure you're scp'ing from the archive, not from pg_xlog? Regards, Gunnar "Nick" Bluth Am 27. Juli 2017 05:00:17 MESZ schrieb James Sewell : >Hi all, > >I've got two servers (A,B) which are part of a

Re: [GENERAL] Row based permissions: at DB or at Application level?

Am 25.07.2017 um 12:59 schrieb vinny: On 2017-07-25 11:40, Thomas Güttler wrote: I would like to reduce the "ifing and elsing" in my python code (less conditions, less bugs, more SQL, more performance) Regards, Thomas Güttler A quick brainstorm: You could, probably... but you'd have to

Re: [GENERAL] Row based permissions: at DB or at Application level?

On 2017-07-27 10:27, Thomas Güttler wrote: Am 25.07.2017 um 12:59 schrieb vinny: On 2017-07-25 11:40, Thomas Güttler wrote: I would like to reduce the "ifing and elsing" in my python code (less conditions, less bugs, more SQL, more performance) When I look at an example policy from the

[GENERAL] Constraint exclusion involving joins

Hi, I understand that the constraints exclusion will work only on constant values. But in my case, I will never pass a constant value to the partitioning key when we query the partition table. Will the partition be still be beneficial in this case. If yes, can you please explain Thanks

Re: [GENERAL] Developer GUI tools for PostgreSQL

On 26.07.17 - 18:41, Tiffany Thang wrote: > Hi, > I'm new to PostgreSQL. I'm looking for a developer tool that works > similarly to TOAD or SQL Developer for Oracle/MySQL which would allow me to > view and make DDL changes to database objects and create data models. It > would be a plus if I can

Re: [GENERAL] Question about paritioning

So is date_trunc better than to_char ? I'm thinking it probably is as for the number of partitions, well we don't plan on deleting anything, but from my reading as long as the queries stay on a small amount of parts that we should be okay. A On 27 July 2017 at 15:33, John R Pierce

Re: [GENERAL] Interesting streaming replication issue

On Thu, Jul 27, 2017 at 4:41 PM, Gunnar "Nick" Bluth < gunnar.bl...@pro-open.de> wrote: > > are you sure you're scp'ing from the archive, not from pg_xlog? > Yes: restore_command = 'scp -o StrictHostKeyChecking=no 10.154.19.30:/archive/xlog//%f %p' Although you are right - that would almost

Re: [GENERAL] Question about paritioning

On 7/27/2017 12:43 AM, Alex Samad wrote: ... as long as the queries stay on a small amount of parts that we should be okay. thats true as long as the planner can restrict the queries to the correct partition... but there's lots of queries that end up having to hit all partitions because

[GENERAL] upsert: is there a shortcut?

Hello, it seems not, but I feel like asking. Is there a way to express a statement like "if you have a conflict on insert replace all the values in the conflicting record" without specifying all the fields explicitly? I.e. in a replication system (where occasional accidents mean replication may

Re: [GENERAL] Interesting streaming replication issue

On Wed, Jul 26, 2017 at 11:55 PM, James Sewell wrote: > On Thu, Jul 27, 2017 at 4:41 PM, Gunnar "Nick" Bluth < > gunnar.bl...@pro-open.de> wrote: > >> >> are you sure you're scp'ing from the archive, not from pg_xlog? >> > > Yes: > > restore_command = 'scp -o

Re: [GENERAL] RETURNS SETOF RECORD with 1 column

Vincenzo Romano writes: > CREATE OR REPLACE FUNCTION afun1( OUT ot TEXT ) > RETURNS SETOF RECORD > The error message for afun1() reads: > ERROR: function result type must be text because of OUT parameters Indeed. It's trying to protect you from simple typos. If

[GENERAL] RETURNS SETOF RECORD with 1 column

Hi all. Please, see this fragment: DROP TABLE IF EXISTS atable; CREATE TABLE atable ( atext TEXT, anint INT ); TRUNCATE atable; INSERT INTO atable VALUES ( 'one',1 ), ( 'two',2 ), ( 'three',3 ), ( 'four',4 ) ; CREATE OR REPLACE FUNCTION afun1( OUT ot TEXT ) RETURNS SETOF RECORD LANGUAGE

Re: [GENERAL] Developer GUI tools for PostgreSQL

On 2017-07-27 00:41, Tiffany Thang wrote: Hi, I'm new to PostgreSQL. I'm looking for a developer tool that works similarly to TOAD or SQL Developer for Oracle/MySQL which would allow me to view and make DDL changes to database objects and create data models. It would be a plus if I can use the

Re: [GENERAL] RETURNS SETOF RECORD with 1 column

2017-07-27 16:03 GMT+02:00 Tom Lane : Thanks a lot for your reply with valuable details. > Vincenzo Romano writes: >> CREATE OR REPLACE FUNCTION afun1( OUT ot TEXT ) >> RETURNS SETOF RECORD > >> The error message for afun1() reads: >> ERROR:

Re: [GENERAL] Developer GUI tools for PostgreSQL

Thanks everyone for your input! On Thu, Jul 27, 2017 at 3:48 AM, Thore Boedecker wrote: > On 26.07.17 - 18:41, Tiffany Thang wrote: > > Hi, > > I'm new to PostgreSQL. I'm looking for a developer tool that works > > similarly to TOAD or SQL Developer for Oracle/MySQL which would

Re: [GENERAL] Indexes being ignored after upgrade to 9.5

On Tue, Jul 25, 2017 at 10:34 PM, Nick Brennan wrote: > Hi, > > We have recently promoted our Prod DB slave (2TB) to migrate to new > hardware, and upgraded from v9.2.9.21 to 9.5.1.6 using pg_upgrade. > > > The upgrade went without incident and we have been running for a

Re: [GENERAL] Interesting streaming replication issue

> > >> >>> are you sure you're scp'ing from the archive, not from pg_xlog? >>> >> >> Yes: >> >> restore_command = 'scp -o StrictHostKeyChecking=no >> 10.154.19.30:/archive/xlog//%f >> %p' >> >> Although you are right - that would almost make sense if I had done that! >> > > Sounds a lot like a

Re: [GENERAL] Monitoring of a hot standby with a largely idle master

On Wed, Jul 26, 2017 at 1:44 AM, Michael Paquier wrote: > On Mon, Jul 24, 2017 at 9:08 PM, Jeff Janes wrote: > > On Sun, Jul 16, 2017 at 8:47 AM, Michael Paquier < > michael.paqu...@gmail.com> > > wrote: > >> What do you think about the patch

Re: [GENERAL] Indexes being ignored after upgrade to 9.5

Hi Peter, Many thanks for your response. I tried to cancel the thread, it was unfortunately stupidity that was the issue. We'd been forced to manually analyze our tables due to time constraints, and one of the table partitions read in the query was missed. It was reporting a bitmap index scan

Re: [GENERAL] Index Only Scan and Heap Fetches

On Tue, Jul 18, 2017 at 7:21 AM, Mikhail wrote: > Hi guys, > > I'm running the process, that executes "select * from sr where sr.id=210 > for update;", then some calculations and finally "update sr set usage = > where sr.id = 210;". That operation is done in a loop. > > In

Re: [GENERAL] Question about paritioning

so I have a pgsql function that will create tables based on MD for x years in advance and inherits of base table. with this CHECK (_received >= '2013-01-01 00:00:00+11'::timestamp with time zone AND _received < '2013-02-01 01:00:00+11'::timestamp with time zone) now for the insert, do I

[GENERAL] Why am I getting doubles?

Hi, The query below should get foreign keys for a specific table: draft=# SELECT DISTINCT kcu.ordinal_position AS ordinal, kcu.position_in_unique_constraint AS position, tc.constraint_name AS name, tc.constraint_schema AS schema, tc.table_name AS table, kcu.column_name AS column, ccu.table_name

Re: [GENERAL] Why am I getting doubles?

Igor Korot writes: > Is there a reason I'm seeing duplicate records on the query above? Your example isn't complete, but I think the problem is your WHERE clause isn't equating enough columns. For instance, if I do db=# create table pp(f1 int, f2 int, primary key (f1,f2));

Re: [GENERAL] Developer GUI tools for PostgreSQL

I think it's funny that after all these years pgadmin3 is still the most comprehensive GUI for postgres. Even though it's prone to crashing on my machine and I have paid for datagrip I still reach for it first. On Fri, Jul 28, 2017 at 2:46 AM, vinny wrote: > On 2017-07-27