Re: [GENERAL] Jsonb extraction very slow

What were you doing to "get ten keys out"? If those were ten separate JSON operators, they'd likely have done ten separate decompressions. You'd have saved something by having the TOAST data already fetched into shared buffers, but it'd still hardly be free. Now i got the point.

[GENERAL] pgbasebackup is failing after truncate

HI At present, I have some requirement to truncate the data base to reclaim disk space and at the same time I need to take basebackup. Seems, truncate is successfully reclaimed the space but pgbasebackup failed and reported the below error. "could not stat file or directory

Re: [GENERAL] Postgres Pain Points 2 ruby / node language drivers

On Thu, Aug 11, 2016 at 10:20 PM, Andreas Joseph Krogh wrote: > På torsdag 11. august 2016 kl. 19:13:08, skrev support-tiger < > supp...@tigernassau.com>: > > I cannot not comment on this. Saying that ORM seems dumb, and working with > PG using ORM does not fly, is a very

Re: [GENERAL] How to parse xml containing optional elements

Hi, Thank you. In "PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit" it returns two empty rows. How to make it work in this version ? I couldn't really believe this so I just installed a VM and a 9.1 postgresql just to test this for you.

Re: [GENERAL] Any reasons for 'DO' statement not returning result?

On Mon, Aug 8, 2016 at 7:25 PM, Xtra Coder wrote: > Hi, > > I'm just curious about the reasons of the design of 'DO' statement so that > it is not able to return result of the SELECT in its body. > > References: > https://www.postgresql.org/docs/current/static/sql-do.html

Re: [GENERAL] Postgres Pain Points: 1 pg_hba conf

On 8/11/2016 1:48 PM, Jeff Janes wrote: #1) pg_hba conf >Out of the box the md5 setting blocks access. That depends on which box you got it out of. If you compile the source yourself, its default settings are 'trust', not 'md5'. If you get it from a repository, it is up to the repository's

Re: [GENERAL] Postgres Pain Points 2 ruby / node language drivers

On Thu, Aug 11, 2016 at 4:20 PM, Andreas Joseph Krogh wrote: > På torsdag 11. august 2016 kl. 19:13:08, skrev support-tiger < > supp...@tigernassau.com>: > > A database is only as good as the ability to use it. Using ORM's with > Postgres seems dumb, like having a Ferrari

Re: [GENERAL] Postgres Pain Points: 1 pg_hba conf

On Thu, Aug 11, 2016 at 10:04 AM, support-tiger wrote: > We have always been impressed with the Postgres project and team. The whole > hybrid SQL / JSONB functionality rocks. The scalability rocks. The speed > and stability rock. At the command line, Postgres rocks.

Re: [GENERAL] Updated RUM-index and support for bigint as part of index

På torsdag 11. august 2016 kl. 19:13:10, skrev Artur Zakirov < a.zaki...@postgrespro.ru >: On 07.08.2016 11:05, Andreas Joseph Krogh wrote: > På søndag 07. august 2016 kl. 08:27:06, skrev Oleg Bartunov > >: > >   

Re: [GENERAL] Postgres Pain Points 2 ruby / node language drivers

På torsdag 11. august 2016 kl. 19:13:08, skrev support-tiger < supp...@tigernassau.com >: A database is only as good as the ability to use it.  Using ORM's with Postgres seems dumb, like having a Ferrari but only allowed to drive 25 kmh. Really, for getting data

Re: [GENERAL] Postgres Pain Points: 1 pg_hba conf

On Thu, Aug 11, 2016 at 11:04:37AM -0600, support-tiger wrote: > #1) pg_hba conf > Out of the box the md5 setting blocks access. Most "advice" say change to > "all all trust" and indeed that works. But that seems a big security issue. > Specifying a postgres role, password, and peer does not

Re: [GENERAL] pg_logical_slot_get_changes

After testing this more, maybe it does work ok just calling pg_logical_slot_get_changes(). I'm making the assumption that you'd like pg_replication_slots.restart_lsn to be close to pg_current_xlog_location(), correct? The further apart they are, the more pg_xlog you have to store, yes?

Re: [GENERAL] Postgres Pain Points 2 ruby / node language drivers

support-tiger wrote: > It would be great if the Ruby and Node drivers can be brought under the > Postgres team umbrella and make them as reliable and clearly documented as > the Python or jdbc drivers. Sadly, the PostgreSQL development group does not have the manpower to maintain or document

Re: [GENERAL] Postgres Pain Points 2 ruby / node language drivers

On 08/11/2016 10:43 AM, David G. Johnston wrote: On Thu, Aug 11, 2016 at 1:13 PM, support-tiger >wrote: It would be great if the Ruby and Node drivers can be brought under the Postgres team umbrella and make them as reliable and

Re: [GENERAL] Postgres Pain Points: 1 pg_hba conf

On Thu, Aug 11, 2016 at 7:04 PM, support-tiger wrote: > #1) pg_hba conf > Out of the box the md5 setting blocks access. Most "advice" say change to > "all all trust" and indeed that works. But that seems a big security issue. Indeed it is. I do not know where do

Re: [GENERAL] Postgres Pain Points 2 ruby / node language drivers

On Thu, Aug 11, 2016 at 1:13 PM, support-tiger wrote: > It would be great if the Ruby and Node drivers can be brought under the > Postgres team umbrella and make them as reliable and clearly documented as > the Python or jdbc drivers. What makes you say that the Python

Re: [GENERAL] Postgres Pain Points #3 postgres role

On Thu, Aug 11, 2016 at 1:22 PM, Adrian Klaver wrote: > > The only other thing I have seen is this: > > https://help.ubuntu.com/community/PostgreSQL > > and it shows: > > sudo -u postgres psql postgres > > ​The second postgres redundant - the default database to

Re: [GENERAL] Postgres Pain Points #3 postgres role

On 08/11/2016 10:15 AM, support-tiger wrote: Relatively minor. All docs say to use "su - postgres" - it doesn't You talking about this?: https://www.postgresql.org/docs/9.5/static/install-short.html That is building from source and shows creating an OS postgres user. The only other thing

[GENERAL] Postgres Pain Points #3 postgres role

Relatively minor. All docs say to use "su - postgres" - it doesn't seem to work right with linux. The difference in using the OS user postgres and the postgres user (role) postgres is confusing. Even if we set the postgres password to "postgres", it doesn't accept the password. With linux

Re: [GENERAL] Updated RUM-index and support for bigint as part of index

On 07.08.2016 11:05, Andreas Joseph Krogh wrote: På søndag 07. august 2016 kl. 08:27:06, skrev Oleg Bartunov >: [snip] have you considered <=| and |=> operators ? <=> in ORDER BY works like KNN. I don't get how these operators

[GENERAL] Postgres Pain Points 2 ruby / node language drivers

A database is only as good as the ability to use it. Using ORM's with Postgres seems dumb, like having a Ferrari but only allowed to drive 25 kmh. Really, for getting data in and out with Postgres, SQL is pretty simple. The ORM abstractions take as much effort as simply writing direct SQL

Re: [GENERAL] Postgres Pain Points: 1 pg_hba conf

On 08/11/2016 10:04 AM, support-tiger wrote: We have always been impressed with the Postgres project and team. The whole hybrid SQL / JSONB functionality rocks. The scalability rocks. The speed and stability rock. At the command line, Postgres rocks. But in applications we have had some real,

[GENERAL] Postgres Pain Points: 1 pg_hba conf

We have always been impressed with the Postgres project and team. The whole hybrid SQL / JSONB functionality rocks. The scalability rocks. The speed and stability rock. At the command line, Postgres rocks. But in applications we have had some real, and not improving pain points: #1)

Re: [GENERAL] pglogical cross subscribe

On 8/3/2016 8:27 PM, Andy Colson wrote: Hi all. I have a localdb and a remotedb. A) I'd like to send table1 and table2 from localdb to remotedb. B) I'd like to send table3 from remotedb to localdb. I have PG9.5 and pglogical setup, and (A) works fine. Well, worked. I then tried to set up

Re: [GENERAL] Jsonb extraction very slow

Jim Nasby writes: > I never dug into why. As Tom posited, decompression might explain the > time to get a single key out. Getting 10 keys instead of just 1 wasn't > 10x more expensive, but it was significantly more expensive than just > getting a single key. What

Re: [GENERAL] Jsonb extraction very slow

Please CC the list. On 8/11/16 2:19 AM, hari.prasath wrote: Actually I've done some testing with this and there is a *significant* overhead in getting multiple keys from a large document. There's a significant extra cost for the first key, but there's also a non-trivial cost for every key after

Re: [GENERAL] Serializable read and blocking

On Thu, Aug 11, 2016 at 7:11 AM, Rakesh Kumar wrote: > The question is re the following claim: > > - Readers do not block readers. > - Readers do not block writers. > - Writers do not block readers. > - Writers may block writers. > > > Are the above statements true

[GENERAL] Serializable read and blocking

As per this blog post http://bonesmoses.org/2016/07/29/pg-phriday-constipated-connections/ I have a question which I asked there too, but unfortunately did not receive any answer. The question is re the following claim: - Readers do not block readers. - Readers do not block writers. - Writers

Re: [GENERAL] upgrade to repmgr3

Hi, 2016-08-11 7:54 GMT-03:00 Pekka Rinne : >> >> Do you by chance have synchronous replication set? That ps output alone >> doesn't say much, but being stuck on COMMIT normally points to failure >> to sync the replication on a standby. >> > > Yeah, I learned that repmgr3

Re: [GENERAL] upgrade to repmgr3

hi 2016-08-09 15:39 GMT+03:00 Martín Marqués : > Hi, > > El 08/08/16 a las 05:57, Pekka Rinne escribió: > > > > Meanwhile I did some more testing with my environment using repmgr3 and > > noticed an issue with promoting standby node. Here is roughly what I did. > > > > 1.

Re: [GENERAL] How to parse xml containing optional elements

Hi! Thank you. In "PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit" it returns two empty rows. How to make it work in this version ? In "PostgreSQL 9.5.2, compiled by Visual C++ build 1800, 32-bit" it works. Andrus. -Algsõnum-

Re: [GENERAL] How to parse xml containing optional elements

Hi Andrus, SELECT unnest(xpath('/ns:Document/ns:BkToCstmrStmt/ns:Stmt/ns:Ntry/ns:Amt/text()', x,nsa))::text::numeric AS tasusumma , unnest(xpath('/ns:Document/ns:BkToCstmrStmt/ns:Stmt/ns:Ntry/ns:NtryDtls/ns:TxDtls/ns:Refs/ns:EndToEndId/text()', x,nsa))::text AS orderinr FROM t; You

Re: [GENERAL] RETURNS TABLE function: ERROR: column reference "word" is ambiguous

need to return query with alias *example:* create table emp (id integer, ename text); insert into emp values(1, 'aaa'); create or replace function f_sample1() returns table (id integer, ename text) as $$ declare begin return query select id, ename from emp; end$$ language plpgsql; select

[GENERAL] How to parse xml containing optional elements

SEPA ISO XML transactions file needs to be parsed into flat table in Postgres 9.1+ in ASP:NET 4.6 MVC controller. I tried code below but this produces wrong result: tasusumma orderinr 150.00 PV04131 0.38 PV04131 Since there is no EndToEnd in second row there should be null in