Re: [GENERAL] How to search a string inside a json structure

Hi, Thank you for reply! I am using version 9.4.5. Unfortunately I could not figure out how to select rows which, for example, contain following json: '{"a":"world","c":{"b":"helloworld"}}' by search with "hello" string. I am trying to create a query which looks values in any field in JSON,

[GENERAL] Deadlock detected after pg_repack receives SIGINT

I'm running a pg_repack from a bash script with timeout of 10 minutes like so (simplified version): timeout -s SIGINT 10m pg_repack --table=frequently_updated_table After the 10 min timeout, the OS sends SIGINT to pg_repack process so the pg_repack calls: SELECT repack.repack_drop($1, $2) and it

Re: [GENERAL] How to search a string inside a json structure

On Tue, Nov 3, 2015 at 10:07 AM, Sami Pietilä wrote: > Unfortunately I could not figure out how to select rows which, for > example, contain following json: '{"a":"world","c":{"b":"helloworld"}}' by > search with "hello" string. > cast the field to a text: select * from

Re: [GENERAL] Drop or alter column under load give ERROR #42804 structure of query does not match function result type:

Victor Blomqvist writes: > In case any of you are interested of recreating this problem, I today had > the time to create a short example that reproduce the error every time I > try. Hmm. If you just do that serially: regression=# select * from select_a() ; id | x +--- (0

Re: [GENERAL] Taking lot time

On 11/03/2015 07:42 AM, Ramesh T wrote: I have a Query it taking a lot of time to fetch results so,explain query gave "Hash Join (cost=55078.00..202405.95 rows=728275 width=418)" " Hash Cond: (itd.tran_id = iad._adj__id)" " -> Seq Scan on inv_detail itd (cost=0.00..40784.18 rows=731029

[GENERAL] Taking lot time

I have a Query it taking a lot of time to fetch results so,explain query gave "Hash Join (cost=55078.00..202405.95 rows=728275 width=418)" " Hash Cond: (itd.tran_id = iad._adj__id)" " -> Seq Scan on inv_detail itd (cost=0.00..40784.18 rows=731029 width=95)" "Filter: (event_type = ANY

Re: [GENERAL] Taking lot time

And in addition to providing the actual query, the structure (and indexes) of all tables involved) are needed. On Tue, Nov 3, 2015 at 5:59 PM, Rob Sargent wrote: > On 11/03/2015 07:42 AM, Ramesh T wrote: > > I have a Query it taking a lot of time to fetch results >

Re: [GENERAL] Taking lot time

On 11/3/2015 6:42 AM, Ramesh T wrote: I have a Query it taking a lot of time to fetch results so,explain query gave "Hash Join (cost=55078.00..202405.95 rows=728275 width=418)" " Hash Cond: (itd.tran_id = iad._adj__id)" " -> Seq Scan on inv_detail itd (cost=0.00..40784.18 rows=731029

[GENERAL] BDR: SSL error: bad write retry

BDR-0.9.3 and PG-9.4.4 on Ubuntu 14.04 Two nodes, BDR replication. Cluster is newly created, no nodes have been removed from it. Making/deleting small tables works well across the cluster. Now I'm trying to pg_restore a larger database from another system (pg_dump output file is 3.1 GB

Re: [GENERAL] Taking lot time

On 11/03/2015 06:42 AM, Ramesh T wrote: I have a Query it taking a lot of time to fetch results so,explain query gave "Hash Join (cost=55078.00..202405.95 rows=728275 width=418)" " Hash Cond: (itd.tran_id = iad._adj__id)" " -> Seq Scan on inv_detail itd (cost=0.00..40784.18 rows=731029

Re: [GENERAL] BDR: name conflict when joining a rebuilt node

Still having issues with this with BDR-0.9.3 This is how I join a new node to the cluster: su - postgres psql pgmirror -- fire up BDR extensions CREATE EXTENSION btree_gist; CREATE EXTENSION bdr; -- join BDR group via an existing node there SELECT

[GENERAL] Check constraint and at least two rows

Hello, I have a design/modelling puzzle/problem. I'm trying to model a series of events. So I have two tables w/ a parent child relationship. The child table has the rule/constraint/etc that for every row in the parent table there must be at least 2 rows in the child because a series must have at

Re: [GENERAL] BDR: SSL error: bad write retry

Nevermind, this was fixed with: ssl_renegotiation_limit = 0 -- Florin Andrei http://florin.myip.org/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Check constraint and at least two rows

On Tue, Nov 3, 2015 at 7:09 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Tue, Nov 3, 2015 at 4:55 PM, Dane Foster wrote: > >> Hello, >> >> I have a design/modelling puzzle/problem. I'm trying to model a series of >> events. So I have two tables w/ a parent

[GENERAL] MinGW-W64 compile error

Hi, I'm trying to build the client library of PostgreSql 9.3.x using this version of MinGW's gcc: gcc.exe (i686-posix-dwarf-rev0, Built by MinGW-W64 project) 5.2.0 I did a ./configure --without-zlib then make (mingw32-make.exe), and got this: $ /e/MinGW/mingw32/bin/mingw32-make.exe

Re: [GENERAL] Check constraint and at least two rows

On Tue, Nov 3, 2015 at 4:55 PM, Dane Foster wrote: > Hello, > > I have a design/modelling puzzle/problem. I'm trying to model a series of > events. So I have two tables w/ a parent child relationship. The child > table has the rule/constraint/etc that for every row in the

Re: [GENERAL] Check constraint and at least two rows

On 11/03/2015 04:23 PM, Dane Foster wrote: On Tue, Nov 3, 2015 at 7:09 PM, David G. Johnston > wrote: On Tue, Nov 3, 2015 at 4:55 PM, Dane Foster >wrote: Hello,

Re: [GENERAL] Deadlock detected after pg_repack receives SIGINT

On 11/3/15 7:44 AM, Michael Paquier wrote: I doubt there is anything involving Postgres here. It seems that some process is still holding a lock on a relation that is being dropped, caused by a race condition in pg_repack code. >PS: I was trying a mailing list of pg_repack

Re: [GENERAL] MinGW-W64 compile error

"=?UTF-8?Q?Leonardo_M._Ram=c3=a9?=" writes: > Hi, I'm trying to build the client library of PostgreSql 9.3.x using > this version of MinGW's gcc: > ... > g++ -DFRONTEND -I../../src/include -I./src/include/port/win32 > -DEXEC_BACKEND "-I../../src/include/port/win32"

Re: [GENERAL] pg_archivecleanup not deleting anything?

On Tue, Nov 3, 2015 at 11:29 PM, Albe Laurenz wrote: > Michael Paquier wrote: >>> So, as Albe posted pg_archivecleanup is only cleaning up the WAL files, not >>> the auxiliary files. The WAL files would be the ones with no extension and a >>> size of 16 MB(unless someone

Re: [GENERAL] How to search a string inside a json structure

Hi, Thank you for reply. I think I need to do some more research of means to implement searching for json databases. I'll look the module. Thanks 2015-11-03 18:43 GMT+02:00 Merlin Moncure : > On Tue, Nov 3, 2015 at 9:57 AM, Vick Khera wrote: > > > > On

Re: [GENERAL] pg_archivecleanup not deleting anything?

Michael Paquier wrote: >> So, as Albe posted pg_archivecleanup is only cleaning up the WAL files, not >> the auxiliary files. The WAL files would be the ones with no extension and a >> size of 16 MB(unless someone changed the compile settings). > > The docs mention that "all WAL files" preceding

Re: [GENERAL] pg_archivecleanup not deleting anything?

On Tue, Nov 3, 2015 at 1:33 AM, Adrian Klaver wrote: > On 11/02/2015 08:17 AM, Paul Jungwirth wrote: >>> >>> Is there anything else beside *.backup files in the directory? >> >> >> There were a few *.history files, and a few files with no extension, >> like this:

Re: [GENERAL] Deadlock detected after pg_repack receives SIGINT

On Tue, Nov 3, 2015 at 9:51 PM, Jiří Hlinka wrote: > After the 10 min timeout, the OS sends SIGINT to pg_repack process so the > pg_repack calls: > SELECT repack.repack_drop($1, $2) > and it causes a deadlock with other process which is INSERTing into >

Re: [GENERAL] Approach to extract top records from table based upon aggregate

On Mon, Nov 2, 2015 at 4:14 PM, droberts wrote: > Hi, I have a table that contains call records. I'm looking to get only > records for users who made the most calls over a particular time duration in > an efficient way. > > calls() > > time, duration,

Re: [GENERAL] How to search a string inside a json structure

On Tue, Nov 3, 2015 at 9:57 AM, Vick Khera wrote: > > On Tue, Nov 3, 2015 at 10:07 AM, Sami Pietilä > wrote: >> >> Unfortunately I could not figure out how to select rows which, for >> example, contain following json: