Re: [GENERAL] BDR: ALTER statement hanging

Selim Tuvi wrote: > Hi, I am running a three node BDR cluster. BDR version is 0.9.3. Postgres > version is 9.4.5. > > With 0.9.2, I used to be able to issue ALTER statements using psql and it > would > go through. This time it is just hanging. The statement is this: for ddl-commands all nodes

Re: [GENERAL] ALTER statement hanging

I stopped the other two nodes and restarted the instance and pg_locks shows the following. deliver=# select * from pg_locks; locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fast

Re: [GENERAL] ALTER statement hanging

And I tried running the same statement on another node, while one node was running it and I got the following: ERROR: database is locked against ddl by another node HINT: Node (6223770712502831127,1,16389) in the cluster is already performing DDL Terminating the statement in one node and runn

[GENERAL] How to audit non LDAP connections?

Due to security/audits have moved most users to LDAP. Looking for a way to tell if a connection is/is not going through LDAP. Other than errors, such as bad password, have not found a way to tell if a connection is using LDAP or postgresql internal authentication in the logs. Tried going throu

Re: [GENERAL] fast refresh materialized view

On 12/3/15 4:17 AM, Nguyễn Trần Quốc Vinh wrote: We have build a tool that generates all triggers in C-language for all data-changing events on all tables underlying upon query. The generated triggers do synchronous incremental updates for MV. Awesome! The hope is to eventually support this int

[GENERAL] BDR: ALTER statement hanging

Hi, I am running a three node BDR cluster. BDR version is 0.9.3. Postgres version is 9.4.5. With 0.9.2, I used to be able to issue ALTER statements using psql and it would go through. This time it is just hanging. The statement is this: alter table pts alter column shot drop not null; I also t

Re: [GENERAL] Can row level security policies also be implemented for views?

On 12/3/15 7:17 AM, Caleb Meredith wrote: Yes it probably could be solved by this. Just a trigger which replaces SELECT right? It's not a trigger and I don't know what you mean by replacing select. An SRF can return anything you want it to. My suggestion was to run a standard select statemen

Re: [GENERAL] Pgbasebackup help

Maybe I'm just being picky here, but... On 12/3/15 3:04 PM, Andreas Kretschmer wrote: If you invoke pg_start_backup() before the copy, and pg_stop_backup() >after the copy, then a file system copy is consistent. The filesystem copy is not consistent, but because of how pg_start/stop_backup w

Re: [GENERAL] json indexing and data types

On Wed, Dec 2, 2015 at 8:04 PM, Tom Lane wrote: > Merlin Moncure writes: >> On Wed, Dec 2, 2015 at 12:03 AM, Kaare Rasmussen wrote: >>> As json essentially only has three basic data types, string, int, and >>> boolean, I wonder how much of this - to index, search, and sort on >>> unstructured da

Re: [GENERAL] JSONB performance enhancement for 9.6

On 11/29/15 9:30 AM, Arthur Silva wrote: Is this correct? I'm fairly sure jsonb supports lazily parsing objects and each object level is actually searched using binary search. The problem is there's no support for loading just part of a TOASTed field. Even if that existed, we'd still need a wa

Re: [GENERAL] loading data into cluster - can I daisy-chain streaming replication?

On 12/2/15 9:54 PM, Dennis wrote: What you can do with your current configuration is do a pg_dumpall or pg_dump of A and load that logical dump into B which would then replicate the data to C. IF you can't handle the downtime you could also setup londiste or Slony and logically replicate A to

Re: [GENERAL] json indexing and data types

On 12/2/15 10:38 PM, Kaare Rasmussen wrote: On 2015-12-03 01:04, Jim Nasby wrote: We have a client that has a similar (though also a bit different) need. Specifically, they get an XML document that has element attributes that tell you what data type the element should contain. We convert the XML

Re: [GENERAL] Pgbasebackup help

> John R Pierce hat am 3. Dezember 2015 um 21:38 > geschrieben: > > > On 12/2/2015 9:59 PM, Yelai, Ramkumar IN BLR STS wrote: > > I can't shutdown the database during the backup and unable to use file > > system copy of data folder as it creates inconsistency > > If you invoke pg_start_back

Re: [GENERAL] Pgbasebackup help

On 12/2/2015 9:59 PM, Yelai, Ramkumar IN BLR STS wrote: I can't shutdown the database during the backup and unable to use file system copy of data folder as it creates inconsistency If you invoke pg_start_backup() before the copy, and pg_stop_backup() after the copy, then a file system co

Re: [GENERAL] Can row level security policies also be implemented for views?

Yes it probably could be solved by this. Just a trigger which replaces SELECT right? On Wed, Dec 2, 2015 at 6:01 PM Jim Nasby wrote: > On 12/2/15 4:59 PM, Caleb Meredith wrote: > > What is an SRF? > > Set returning function. > > CREATE FUNCTION srf() RETURNS SETOF pg_class LANGUAGE sql AS 'SELECT

Re: [GENERAL] Pgbasebackup help

HI, Thanks David. What I wanted to achieve is simple copy of Data folder. I can't shutdown the database during the backup and unable to use file system copy of data folder as it creates inconsistency and don't want to use pg_dump. Hence I decided to use Pg_basebackup for copying the base back

Re: [GENERAL] json indexing and data types

On 2015-12-03 05:04, Tom Lane wrote: Yeah. The problem here is that a significant part of the argument for the JSON/JSONB datatypes was that they adhere to standards (RFC 7159 in particular). I can't see us accepting a patch that changes them into JSON-plus-some-PG-enhancements. Would be nice

Re: [GENERAL] Can row level security policies also be implemented for views?

What is an SRF? On Wed, Dec 2, 2015 at 5:46 PM Jim Nasby wrote: > On 11/25/15 7:40 AM, Stephen Frost wrote: > >> It seems easy conceptually, RLS just adds a WHERE clause to queries if > I'm > >> >not mistaken, and conceptually a view is just a query. The CURRENT_USER > >> >issue is valid, but pe

Re: [GENERAL] plperlu stored procedure seems to freeze for a minute

"Peter J. Holzer" writes: > Can those signals be safely ignored? Just blocking them (so that they > are delivered after the UDF finishes) might be safer. But even that may > be a problem: If the UDF then executes some SQL, could that rely on > signals being delivered? I have no idea. The minute

Re: [GENERAL] plperlu stored procedure seems to freeze for a minute

On 2015-12-02 19:07:55 -0600, Jim Nasby wrote: > On 12/2/15 9:26 AM, Peter J. Holzer wrote: > >As explained in backend/utils/misc/timeout.c, the timers are never > >cancelled: If a timeout is cancelled, postgres just sees that it has > >nothing to do and resumes whatever it is doing. > > Hrm, if t

Re: [GENERAL] json indexing and data types

On Wed, Dec 2, 2015 at 7:11 PM, Jim Nasby wrote: > On 12/2/15 7:06 PM, Merlin Moncure wrote: >> >> > The basics is, that I have a column with what is essentially json data; >> a >> > number of data structures of different depths. Perhaps 10 - 30 top >> levels, >> > and probably no more than 3,

Re: [GENERAL] json indexing and data types

On Wed, Dec 2, 2015 at 10:04 PM, Tom Lane wrote: > (Having said that, it sure looks to me like JSON's idea of a number is > float/numeric, not merely int. Are you sure you need more capability > in that department, and if so what exactly?) Numeric range searching is good, but the numeric case is

Re: [GENERAL] fast refresh materialized view

Dear Sir. I'm sorry. The attachment was too big. You can find the source code and the binary at *http://it.ued.udn.vn/myprojects/pgTriggerGen/PgSQLMVTriggersGenerator_v4._a_ * . Best regards. TS. Nguyễn Trần Quốc Vi

Re: [GENERAL] fast refresh materialized view

Dear Sir. I'm sorry. The attachment was too big. You can find the source code and the binary at http://it.ued.udn.vn/myprojects/pgTriggerGen/PgSQLMVTriggersGenerator v4._a_. Best regards. On Thu, Dec 3, 2015 at 5:17 PM, Nguyễn Trần Quốc Vinh wrote: > Dear Sirs. > > We have build a tool that ge

Re: [GENERAL] Comparing two postgres dump files.

2015-12-03 7:12 GMT+01:00 Kaushal Shriyan : > Hi, > > Are there any scripts which will diff two pg_dump files for t1 and t2 time > period. For example pg_dump taken on t1 -> 01/11/2015 and then on t2 -> > 30/11/2015. > > backup_01112015.dump (dump taken on 01/11/2015) > backup_30112015.dump (dump

Re: [GENERAL] how to import "where exists（subquery）" EXISTS CONDITION performance?

On 12/1/2015 11:51 PM, shili wrote: I had saw this sentence: SQL statements that use the EXISTS condition in PostgreSQL are very inefficient since the sub-query is RE-RUN for EVERY row in the outer query's table. There are more efficient ways to write most queries, that do not use the EXISTS co

[GENERAL] RE: [GENERAL] how to import "where exists（subquery）" EXISTS CONDITION performance?

shili wrote: > I had saw this sentence: SQL statements that use the EXISTS condition in > PostgreSQL are very inefficient > since the sub-query is RE-RUN for EVERY row in the outer query's table. There > are more efficient ways > to write most queries, that do not use the EXISTS condition. > So,I