[GENERAL] Switching roles as an replacement of connection pooling tools

I have a feeling that slight enhancement to commands "SET ROLE" or "SET SESSION AUTHORIZATION" can obsolete and outperform external connection pooling tools in some use cases. Assume we are in the following situation: - There are a million schemas each owned by a distinct role. - Every role is no

Re: [GENERAL] After replication failover: could not read block X in file Y read only 0 of 8192 bytes

On Tue, May 31, 2016 at 04:49:26PM +1000, Venkata Balaji N wrote: > On Mon, May 30, 2016 at 11:37 PM, Brian Sutherland > wrote: > > > I'm running a streaming replication setup with PostgreSQL 9.5.2 and have > > started seeing these errors on a few INSERTs: > > > > ERROR: could not read block

[GENERAL] How to hide JDBC connection credentials from git?

Hello, I work on several small Java projects (using Maven+NetBeans) and store them in a public git repository. I would like to use PostgreSQL JDBC in some of the projects, but don't want to make the connection credentials of my database public. Surely there are other developers out there, who ha

Re: [GENERAL] How to hide JDBC connection credentials from git?

On 31 May 2016 at 11:32, Alexander Farber wrote: > Hello, > > I work on several small Java projects (using Maven+NetBeans) and store > them in a public git repository. > > I would like to use PostgreSQL JDBC in some of the projects, but don't > want to make the connection credentials of my databa

Re: [GENERAL] BDR to ignore table exists error

Thanks a lot Martin for your replies. On Sun, May 29, 2016 at 11:50 PM, Martín Marqués wrote: > Hi, > > El 29/05/16 a las 06:01, Nikhil escribió: > > > > *​Nik>> skip_ddl_locking is set to True in my configuration. As this > > was preventing single* > > > > *​node from doing DDL operatio

Re: [GENERAL] swarm of processes in BIND state?

On Mon, May 30, 2016 at 11:05:17AM -0700, Jeff Janes wrote: > So my theory is that you deleted a huge number of entries off from > either end of the index, that transaction committed, and that commit > became visible to all. Planning a mergejoin needs to dig through all > those tuples to probe the

Re: [GENERAL] Slides for PGCon2016; "FTS is dead ? Long live FTS !"

Hi Oleg 2016-05-29 19:54 GMT+02:00 Oleg Bartunov : > We chose RUM just because there are GIN and VODKA :) > But some people already suggested several meanings like Really Useful iMdex :) > We are open for suggestion. iMdex LOL :-) Ok. What's new about the index? * AFAIK it's using methods as ex

Re: [GENERAL] empty pg_stat_replication when replication works fine?

I've found the reason: inconsistent catalog data. This state did not disappear after restart of all postgres instances. pg_authid.oid does not match the one in pg_stat_get_activity(NULL::integer). I wonder how this inconsistency could happen.. Maybe some error during cloning database (binary data

Re: [GENERAL] Deleting a table file does not raise an error when the table is touched afterwards, why?

Hi: On Tue, May 31, 2016 at 8:58 AM, Daniel Westermann wrote: > for completeness: same issue with data checksums enabled: ... > => rm the table files > => select count(*) still works And ? I would vote for not doing anything on this, after all you are working outside the 'envelope' on this. Is l

[GENERAL] Drop/Re-Creating database extremely slow + doesn't lose data

Intention: to drop a database and recreate it. Expectation: the newly created db should be empty What happens: dropping is fast, creation is slow, and when I reconnect, all the data objects are still there. Commands (tried both through command line with dropdb/createdb and through psql) pgdba@tem

Re: [GENERAL] Switching roles as an replacement of connection pooling tools

On Tue, May 31, 2016 at 9:45 AM, CN wrote: ... > If command "SET SESSION AUTHORIZATION" is enhanced to accept two > additional arguments > PASSWORD ... > SET SESSION AUTHORIZATION user2 PASSWORD p2; > SET SEARCH_PATH TO schema2,pg_category; > Does my points make sense? It does, but I feel it mus

Re: [GENERAL] Switching roles as an replacement of connection pooling tools

On Tue, May 31, 2016 at 3:45 AM, CN wrote: > I have a feeling that slight enhancement to commands "SET ROLE" or "SET > SESSION AUTHORIZATION" can obsolete and outperform external connection > pooling tools in some use cases. > > Assume we are in the following situation: > > - There are a million

Re: [GENERAL] Drop/Re-Creating database extremely slow + doesn't lose data

From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Thalis Kalfigkopoulos Sent: Tuesday, May 31, 2016 9:49 AM To: pgsql-general@postgresql.org Subject: [GENERAL] Drop/Re-Creating database extremely slow + doesn't lose data Intention: to drop a databas

Re: [GENERAL] Drop/Re-Creating database extremely slow + doesn't lose data

Hi Thalis On Tue, May 31, 2016 at 3:49 PM, Thalis Kalfigkopoulos wrote: > Intention: to drop a database and recreate it. > Expectation: the newly created db should be empty > What happens: dropping is fast, creation is slow, and when I reconnect, > all the data objects are still there. > > Comma

Re: [GENERAL] Drop/Re-Creating database extremely slow + doesn't lose data

On Tue, May 31, 2016 at 9:49 AM, Thalis Kalfigkopoulos wrote: > Intention: to drop a database and recreate it. > Expectation: the newly created db should be empty > What happens: dropping is fast, creation is slow, and when I reconnect, > all the data objects are still there. > ​[...]​ Even wei

Re: [GENERAL] Drop/Re-Creating database extremely slow + doesn't lose data

Hi all. Ok, Igor nailed it. That was lame on my behalf. I apparently "contaminated" my template1 db at some point (restored into it instead of into the target "dafodb"). A simple \d confirmed this immediately. Apologies for the false alarm. @DavidJohnston, I don't know why, but yes, I am doing a

Re: [GENERAL] Switching roles as an replacement of connection pooling tools

Hi Melvin: On Tue, May 31, 2016 at 3:55 PM, Melvin Davidson wrote: > On Tue, May 31, 2016 at 3:45 AM, CN wrote: >> SET SESSION AUTHORIZATION user2 PASSWORD p2; > Your points make no sense. You can accomplish the same with: > GRANT ROLE user2 TO user1; I'm not discussing wether it makes sense,

Re: [GENERAL] Slides for PGCon2016; "FTS is dead ? Long live FTS !"

On Sun, May 29, 2016 at 8:53 PM, Andreas Joseph Krogh wrote: > På søndag 29. mai 2016 kl. 19:49:06, skrev Oleg Bartunov < > obartu...@gmail.com>: > > [snip] >> >> I want to run 9.6 beta in production right now because of this:-) >> > > wait-wait :) We'd be happy to have feedback from production,

Re: [GENERAL] Switching roles as an replacement of connection pooling tools

On 31/05/2016 10:45, CN wrote: I have a feeling that slight enhancement to commands "SET ROLE" or "SET SESSION AUTHORIZATION" can obsolete and outperform external connection pooling tools in some use cases. Assume we are in the following situation: - There are a million schemas each owned by a

Re: [GENERAL] Drop/Re-Creating database extremely slow + doesn't lose data

On Tue, May 31, 2016 at 10:06 AM, Thalis Kalfigkopoulos wrote: > @DavidJohnston, I don't know why, but yes, I am doing all operations > connected from template1. > >> >> Oddly, the notes on the aforementioned page state: "The principal >> limitation is that no other sessions can be connected to t

Re: [GENERAL] Switching roles as an replacement of connection pooling tools

Actually, you do not need to SWITCH, you just need permission to change to path and gain access to all user2 privs, which is exactly what SET ROLE user2 does. There is no need for a password, since user1 is already connected to the DB. Any superuser can give the GRANT ROLE to any other user. That

Re: [GENERAL] Switching roles as an replacement of connection pooling tools

CN writes: > If command "SET SESSION AUTHORIZATION" is enhanced to accept two > additional arguments > PASSWORD > , then a client simply establishes only one connection to server and do > jobs for a million roles. I'm pretty sure this has been proposed before, and rejected before. Two big proble

Re: [GENERAL] Slides for PGCon2016; "FTS is dead ? Long live FTS !"

På tirsdag 31. mai 2016 kl. 16:12:52, skrev Oleg Bartunov mailto:obartu...@gmail.com>>: [snip] He he, I reported 1st issue: https://github.com/postgrespro/rum/issues/1   Would be cool to see this fixed so I actually could have a sip of the rum:-)  

Re: [GENERAL] Switching roles as an replacement of connection pooling tools

On Tue, May 31, 2016 at 10:20 AM, Tom Lane wrote: > CN writes: > > If command "SET SESSION AUTHORIZATION" is enhanced to accept two > > additional arguments > > PASSWORD > > , then a client simply establishes only one connection to server and do > > jobs for a million roles. > > * Any session-l

Re: [GENERAL] Switching roles as an replacement of connection pooling tools

On 31/05/2016 17:23, Melvin Davidson wrote: Actually, you do not need to SWITCH, you just need permission to change to path and gain access to all user2 privs, which is exactly what SET ROLE user2 does. There is no need for a password, since user1 is already connected to the DB. Any superuser c

Re: [GENERAL] Switching roles as an replacement of connection pooling tools

"David G. Johnston" writes: > Is there a reason something "SET ROLE ... WITH SETTINGS" couldn't be > implemented? Unless there's something underlying that proposal that I'm not seeing, it only deals with one of the problems in this area. The security- related issues remain unsolved. AFAICS ther

Re: [GENERAL] Switching roles as an replacement of connection pooling tools

On Tue, May 31, 2016 at 10:48 AM, Tom Lane wrote: > "David G. Johnston" writes: > > Is there a reason something "SET ROLE ... WITH SETTINGS" couldn't be > > implemented? > > Unless there's something underlying that proposal that I'm not seeing, > it only deals with one of the problems in this ar

Re: [GENERAL] recordings of pgconf us 2016

Funny. I was just looking for that myself. I would expect it to go on their channel at https://www.youtube.com/channel/UCsJkVvxwoM7R9oRbzvUhbPQ but so far nothing from this year. PGCon has published their 2016 recordings on their channel: https://www.youtube.com/playlist?list=PLuJmmKtsV1dNE

Re: [GENERAL] recordings of pgconf us 2016

On Sun, May 29, 2016 at 12:36 AM, Johannes wrote: > I guess I have seen all video recording from pgconf us 2015 at youtube. > Are there any recording from this year available? > We are still waiting to have them edited by the video company. Hopefully it will be soon.

[GENERAL] Triggers not being fired with pglogical

Hi, I have a basic replica with pglogical 1.1 and postgres 9.5 on both servers publisher and subscriber. I have triggers on publisher and subscriber tables, but the trigger on subscriber table is not being fired. Is it possible to fire the trigger with pglogical replication? Thanks in advance

[GENERAL] Checkpoint Err on Startup of Rsynced System

I am trying to setup a 2nd, identical, db server (M2) for development and I've run into a problem with starting up the 2nd Postgres installation. Here's what I've done: 1) did a 'clone' of 1st (production) machine M1 (so both machines on Cent OS 7.2) 2) setup an rsync operation, did a comp

Re: [GENERAL] Checkpoint Err on Startup of Rsynced System

On Tue, May 31, 2016 at 1:13 PM, Jim Longwill wrote: > I am trying to setup a 2nd, identical, db server (M2) for development and > I've run into a problem with starting up the 2nd Postgres installation. > > Here's what I've done: > 1) did a 'clone' of 1st (production) machine M1 (so both machin

[GENERAL] Log Shipping

It is my understanding that if PostgeSQL has log shipping enabled, if for whatever reason it cannot ship the file the master server will hold it. But for how long? Secondly, I have 2 servers I ship log files to using the following script: #!/usr/local/bin/bash # Slave 1 rsync -a $1 pgi@192.168.1

Re: [GENERAL] Log Shipping

Joseph Kregloh wrote: > It is my understanding that if PostgeSQL has log shipping enabled, if for > whatever reason it cannot ship the file the master server will hold it. But > for how long? Forever (which means it dies because of running out of space in the partition containing pg_xlog). > Seco

Re: [GENERAL] Checkpoint Err on Startup of Rsynced System

On Tuesday, May 31, 2016 10:13:14 AM Jim Longwill wrote: > I am trying to setup a 2nd, identical, db server (M2) for development > and I've run into a problem with starting up the 2nd Postgres installation. > > Here's what I've done: >1) did a 'clone' of 1st (production) machine M1 (so both ma

Re: [GENERAL] Log Shipping

On Tue, May 31, 2016 at 4:12 PM, Alvaro Herrera wrote: > Joseph Kregloh wrote: > > It is my understanding that if PostgeSQL has log shipping enabled, if for > > whatever reason it cannot ship the file the master server will hold it. > But > > for how long? > > Forever (which means it dies because

[GENERAL] Row security policies documentation question

Hello, Section 5.7. on Row Security Policies (https://www.postgresql.org/docs/current/static/ddl-rowsecurity.html) for 9.5 says: As a simple example, here is how to create a policy on the account relation to allow only members of the managers role to access rows, and only rows of their accoun

Re: [GENERAL] Row security policies documentation question

On Tue, May 31, 2016 at 4:59 PM, Alexander M. Sauer-Budge < ambu...@alum.mit.edu> wrote: > Hello, > > Section 5.7. on Row Security Policies ( > https://www.postgresql.org/docs/current/static/ddl-rowsecurity.html) for > 9.5 says: > [...] > ​ > > CREATE POLICY user_policy ON users > USING (us

[GENERAL] plql and or clausule

hello this is my first question. I am new in postgres and using plsql. i am making this (bellow) i want to insert one copy of one record into the log table but if there is some change into the original recor to update into this record two fields but i have one rror can you help me please?

Re: [GENERAL] plql and or clausule

On Tue, May 31, 2016 at 4:18 PM, wrote: > ERROR: el operador no existe: character varying == character varying > LINE 1: SELECT OLD.Peticionario == NEW.Peticionario or OLD.interlocc... Perhaps you want the = operator? -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQ

Re: [GENERAL] plql and or clausule

Try this: CREATE OR REPLACE FUNCTION lst_tot_mytable_LOG() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN IF (TG_OP = 'UPDATE') THEN INSERT INTO lst_tot_mytable_log SELECT 'U', now(), OLD.*; IF OLD.Peticionario != NEW.Peticionario or OLD.interlocclte != NEW.interloc

Re: [GENERAL] plql and or clausule

On 05/31/2016 02:18 PM, car...@lpis.com wrote: hello this is my first question. I am new in postgres and using plsql. i am making this (bellow) i want to insert one copy of one record into the log table but if there is some change into the original recor to update into this record two fields

Re: [GENERAL] Checkpoint Err on Startup of Rsynced System

On Wed, Jun 1, 2016 at 3:13 AM, Jim Longwill wrote: > I am trying to setup a 2nd, identical, db server (M2) for development and > I've run into a problem with starting up the 2nd Postgres installation. > > Here's what I've done: > 1) did a 'clone' of 1st (production) machine M1 (so both machine

Re: [GENERAL] Row security policies documentation question

On 05/31/2016 01:59 PM, Alexander M. Sauer-Budge wrote: Hello, Section 5.7. on Row Security Policies (https://www.postgresql.org/docs/current/static/ddl-rowsecurity.html) for 9.5 says: As a simple example, here is how to create a policy on the account relation to allow only members of the ma

[GENERAL] postgres_fdw and Kerberos authentication

postgres_fdw is a great feature, but several organizations disallow to hold any kind of passwords as plain text. Providing the superuser role is not either an option. A nice way to meet security requirements would be to provide single sign on support for the postgres_fdw. As long as you have def

Re: [GENERAL] Checkpoint Err on Startup of Rsynced System

Scott, Thanks. If I understand you correctly.. Actually, we did have M1 shutdown when the inital clone was done (some weeks ago). That was done using the VMWare system, not rsync. My main problem is that I don't have WAL archiving setup yet (I've not changed the Postgres defaults on this

Re: [GENERAL] postgres_fdw and Kerberos authentication

Jean-Marc Lessard writes: > A nice way to meet security requirements would be to provide single sign on > support for the postgres_fdw. > As long as you have defined a user in the source and destination databases, > and configure the Kerberos authentication you should be able to use > postgres_

[GENERAL] Change in order of criteria - reg

Hi, In PostgreSQL , does the order in which the criteria is given matter ?? For example Query 1 : Select * from TABLE where a > 5 and b < 10; Query 2 : Select * from TABLE where b <10 and a > 5; Are query 1 and query 2 the same in PostgreSQL or different ?? If its different , WHY ?? Than

Re: [GENERAL] After replication failover: could not read block X in file Y read only 0 of 8192 bytes

Oops, i missed including pgsql-general in my earlier replies.. > > > I have data_checksums switched on so am suspecting a streaming > > > > > replication bug. Anyone know of a recent bug which could have > caused > > > > > this? > > > > > > > > > > > > > I cannot conclude at this point. I encoun