[GENERAL] Returning values from an array of JSONB objects.

Hi, please excuse either my stupidity or naivety regarding this but I'm a bit confused. Give the following basic table structure : TABLE Data ( ID INT NOT NULL, Markers jsonb NOT NULL ); And the following data: INSERT INTO Data (ID, Markers) VALUES(1, '[ {"idle": true, "items":

Re: [GENERAL] Returning values from an array of JSONB objects.

On 4/13/16, sighup wrote: > Hi, please excuse either my stupidity or naivety regarding this but I'm > a bit confused. Give the following basic table structure : > > TABLE Data ( > ID INT NOT NULL, > Markers jsonb NOT NULL > ); > > And the following data: > > INSERT INTO Data (ID, Marke

Re: [GENERAL] Returning values from an array of JSONB objects.

On 4/13/16, Vitaly Burovoy wrote: > On 4/13/16, sighup wrote: >> Hi, please excuse either my stupidity or naivety regarding this but I'm >> a bit confused. Give the following basic table structure : >> >> TABLE Data ( >> ID INT NOT NULL, >> Markers jsonb NOT NULL >> ); >> >> And the fol

Re: [GENERAL] Returning values from an array of JSONB objects.

On 13/04/2016 11:53, Vitaly Burovoy wrote: On 4/13/16, Vitaly Burovoy wrote: On 4/13/16, sighup wrote: Hi, please excuse either my stupidity or naivety regarding this but I'm a bit confused. Give the following basic table structure : TABLE Data ( ID INT NOT NULL, Markers json

Re: [GENERAL] Freezing localtimestamp and other time function on some value

On 12.04.2016 20:50, Tom Lane wrote: Alex Ignatov writes: You always should keep in mind that your application may run in test mode (future/past time) and maintain this code. While with my proposal you can always use some time function(now or localtimestamp or whatever) which you can freez

Re: [GENERAL] Freezing localtimestamp and other time function on some value

On 12.04.2016 21:05, Tom Lane wrote: I wrote: 3. While I can see the point of wanting to, say, test weekend behavior on a weekday, I do not see how a value of now() that doesn't advance between transactions would represent a realistic test environment for an app with time-dependent behavior.

Re: [GENERAL] Returning values from an array of JSONB objects.

## sighup (r...@sighup.eu): > How can I extract the value of the 'items' key either as two rows and or > a sum of both. select id, jsonb_array_elements(markers)->>'items' from data; And as jsonb_array_elements() returns a setof jsonb: with tab(id, items) as ( select id, (jsonb_array_elements(m

[GENERAL] Why is the comparison between timestamp and date so much slower then between two dates

I came across something strange today. Consider the following table: CREATE TABLE price_history ( product_id integer, valid_from date, valid_to date, price integer ); CREATE INDEX i1 ON price_history (product_id, valid_from, valid_to); The table c

Re: [GENERAL] Why is the comparison between timestamp and date so much slower then between two dates

Thomas Kellerer writes: > So my question is: why is comparing a timestamp to a date so much slower? The date has to be up-converted to a timestamptz (not timestamp). I think the expensive part of that is determining what timezone applies, in particular whether DST is active. You could try it wit

Re: [GENERAL] Fastest way to duplicate a quite large database

Em 12/04/2016 12:53, Edson Richter escreveu: *De: *Adrian Klaver *Enviado:*terça-feira, 12 de abril de 2016 12:04 *Para: *Edson Richter ; pgsql-general@postgresql.org *Assunto: *Re: [GENER

Re: [GENERAL] Fastest way to duplicate a quite large database

On 04/13/2016 06:58 AM, Edson Richter wrote: Another trouble I've found: I've used "pg_dump" and "pg_restore" to create the new CustomerTest database in my cluster. Immediately, replication started to replicate the 60Gb data into slave, causing big trouble. Does mark it as "template" avoids re

Re: [GENERAL] Freezing localtimestamp and other time function on some value

On 04/13/2016 04:16 AM, Alex Ignatov wrote: On 12.04.2016 20:50, Tom Lane wrote: Alex Ignatov writes: You always should keep in mind that your application may run in test mode (future/past time) and maintain this code. While with my proposal you can always use some time function(now or loca

Re: [GENERAL] Why is the comparison between timestamp and date so much slower then between two dates

On 13 April 2016 at 15:45, Tom Lane wrote: > Thomas Kellerer writes: >> So my question is: why is comparing a timestamp to a date so much slower? > > The date has to be up-converted to a timestamptz (not timestamp). > I think the expensive part of that is determining what timezone > applies, in p

Re: [GENERAL] Fastest way to duplicate a quite large database

Em 13/04/2016 11:18, Adrian Klaver escreveu: On 04/13/2016 06:58 AM, Edson Richter wrote: Another trouble I've found: I've used "pg_dump" and "pg_restore" to create the new CustomerTest database in my cluster. Immediately, replication started to replicate the 60Gb data into slave, causing big

Re: [GENERAL] Fastest way to duplicate a quite large database

On 04/13/2016 07:46 AM, Edson Richter wrote: Em 13/04/2016 11:18, Adrian Klaver escreveu: On 04/13/2016 06:58 AM, Edson Richter wrote: Another trouble I've found: I've used "pg_dump" and "pg_restore" to create the new CustomerTest database in my cluster. Immediately, replication started to r

Re: [GENERAL] Fastest way to duplicate a quite large database

On 04/13/2016 08:46 AM, Edson Richter wrote: Em 13/04/2016 11:18, Adrian Klaver escreveu: On 04/13/2016 06:58 AM, Edson Richter wrote: Another trouble I've found: I've used "pg_dump" and "pg_restore" to create the new CustomerTest database in my cluster. Immediately, replication started to

[GENERAL] SET prepared statement

Hello, We currently use prepared statements for most of the work an app does, as an SQL injection protection and for other reasons. There's one statement which can't be prepared: SET LOCAL myprefix.mysetting = 'my setting value'; Ideally, I'd like to be able to do PREPARE test(text) as SET LO

Re: [GENERAL] Freezing localtimestamp and other time function on some value

On 13.04.2016 17:26, Adrian Klaver wrote: On 04/13/2016 04:16 AM, Alex Ignatov wrote: On 12.04.2016 20:50, Tom Lane wrote: Alex Ignatov writes: You always should keep in mind that your application may run in test mode (future/past time) and maintain this code. While with my proposal you

Re: [GENERAL] SET prepared statement

On Wed, Apr 13, 2016 at 8:38 AM, Oliver Kohll wrote: > Hello, > > We currently use prepared statements for most of the work an app does, as > an SQL injection protection and for other reasons. > > There's one statement which can't be prepared: > > SET LOCAL myprefix.mysetting = 'my setting value'

Re: [GENERAL] Freezing localtimestamp and other time function on some value

On 13.04.2016 18:40, Alex Ignatov wrote: On 13.04.2016 17:26, Adrian Klaver wrote: On 04/13/2016 04:16 AM, Alex Ignatov wrote: On 12.04.2016 20:50, Tom Lane wrote: Alex Ignatov writes: You always should keep in mind that your application may run in test mode (future/past time) and main

Re: [GENERAL] Freezing localtimestamp and other time function on some value

On Wed, Apr 13, 2016 at 10:14 AM, Alex Ignatov wrote: > Some quick and dirty issue resolution is simple: > set search_path = my_time_schema on db layer. After that you dont need to > change any code. And can take for example freeze.fixed_date from config =) > where my_time_schema contains all tim

[GENERAL] sign function with INTERVAL?

Hi all, Is there a good reason why the SIGN() function does not work with the INTERVAL type? (It is only defined for numeric types.) (http://www.postgresql.org/docs/9.5/static/functions-math.html) select sign(-3); -- okay select sign(interval '4 years'); -- ERROR: function sign(interval) does not

Re: [GENERAL] sign function with INTERVAL?

On 2016-04-13 Daniel Lenski wrote: > Hi all, > I'm trying to find a straightforward and reliable way to differentiate > positive, negative, and zero time intervals while handling NULL in the > same way as the SIGN() function. I'm not sure that "positive time interval" is a thing. Witness: dakka

Re: [GENERAL] [HACKERS] sign function with INTERVAL?

On 4/13/16 1:36 PM, Daniel Lenski wrote: Hi all, Is there a good reason why the SIGN() function does not work with the INTERVAL type? (It is only defined for numeric types.) (http://www.postgresql.org/docs/9.5/static/functions-math.html) The only thing that comes to mind is you can get some str

Re: [GENERAL] [HACKERS] sign function with INTERVAL?

Jim Nasby writes: > Actually, after looking at the code for interval_lt, all that needs to > happen to add this support is to expose interval_cmp_internal() as a > strict function. It already does exactly what you want. interval_cmp() is already SQL-accessible. regards,

Re: [GENERAL] SET prepared statement

> On 13 Apr 2016, at 16:48, David G. Johnston > wrote: > > On Wed, Apr 13, 2016 at 8:38 AM, Oliver Kohll > wrote: > Hello, > > We currently use prepared statements for most of the work an app does, as an > SQL injection protection and for other reasons. > > Th

[GENERAL] I/O - Increase RAM

Hi all, At the moment I'm having 100% I/O during the day. My server has SATA HDs, and it can't be changed now. So, to solve the problem (or at least try) I was thinking about double the RAM, and by doing that, increasing the cache. [image: Inline images 1] The server has 128GB today: shared_buf

Re: [GENERAL] I/O - Increase RAM

On 04/13/2016 01:43 PM, drum.lu...@gmail.com wrote: Question: I know that might not be the best option, but by increasing the RAM and the CACHE would help, right? might, not necessarily would. JD -- Command Prompt, Inc. http://the.postgres.company/ +

Re: [GENERAL] I/O - Increase RAM

On 4/13/2016 1:43 PM, drum.lu...@gmail.com wrote: At the moment I'm having 100% I/O during the day. My server has SATA HDs, and it can't be changed now. So, to solve the problem (or at least try) I was thinking about double the RAM, and by doing that, increasing the cache. depends on if its r

Re: [GENERAL] I/O - Increase RAM

On 4/13/2016 1:52 PM, John R Pierce wrote: ... will speed that up is faster disks I left out faster RPM disks... faster sequential transfer speeds are generally of little impact to write-bound database servers as most of the writes are random, so its an issue of IOPS rather than MB/se

Re: [GENERAL] I/O - Increase RAM

On 14 April 2016 at 08:52, Joshua D. Drake wrote: > On 04/13/2016 01:43 PM, drum.lu...@gmail.com wrote: > > Question: >> >> I know that might not be the best option, but by increasing the RAM and >> the CACHE would help, right? >> > > might, not necessarily would. > > > Would be nice if you could

Re: [GENERAL] I/O - Increase RAM

On 04/13/2016 01:59 PM, drum.lu...@gmail.com wrote: On 14 April 2016 at 08:52, Joshua D. Drake mailto:j...@commandprompt.com>> wrote: On 04/13/2016 01:43 PM, drum.lu...@gmail.com wrote: Question: I know that might not be the best option,

Re: [GENERAL] I/O - Increase RAM

|From: John R Pierce Sent: Wednesday, April 13, 2016 1:53 PM | |On 4/13/2016 1:43 PM, drum.lu...@gmail.com wrote: |> At the moment I'm having 100% I/O during the day. My server has SATA |> HDs, and it can't be changed now. |> So, to solve the problem (or at least try) I was thinking about double |

[GENERAL] Trying to setup pglogical with no success

Here's what I have done to try to set up pglogical. What am I doing wrong? 2 nodes provider - 10.241.0.10 subscriber - 10.241.0.12 svp2=# select version(); version -

Re: [GENERAL] I/O - Increase RAM

It sounds as if you have *one* server with 128GB of memory. Therefore the probabilities are that you have too many task running at the same time. Flushing the disk drives’ read/write cache. Increasing the memory probably will not solve the problem. The most effective solution would be t

Re: [GENERAL] [HACKERS] sign function with INTERVAL?

On Wed, Apr 13, 2016 at 12:35 PM, Tom Lane wrote: > Jim Nasby writes: >> Actually, after looking at the code for interval_lt, all that needs to >> happen to add this support is to expose interval_cmp_internal() as a >> strict function. It already does exactly what you want. > > interval_cmp() is

Re: [GENERAL] pgpool-II: cannot use serializable mode in a hot standby

On Wed, Apr 13, 2016 at 1:30 AM, Alexander Pyhalov wrote: > Tatsuo Ishii писал 13.04.2016 02:36: >>> >>> On 04/12/2016 16:50, Adrian Klaver wrote: On 04/12/2016 01:06 AM, Alexander Pyhalov wrote: >>> >>> I understand. I mean perhaps pgpool shouldn't forward these statements >>> to slaves

Re: [GENERAL] pgpool-II: cannot use serializable mode in a hot standby

On Wed, Apr 13, 2016 at 5:54 PM, Kevin Grittner wrote: > See this example, and imagine that > the transaction generating the list of receipts for the closed > batch is run on the standby before the transaction adding the last > receipt commits. Or test it. https://wiki.postgresql.org/wiki/SSI#D

Re: [GENERAL] I/O - Increase RAM

Sorry, by the first solution I meant to separate the drive for your db files from other tasks. / It sounds as if you have *one* server with 128GB of memory. Therefore the probabilities are that you have too many tasks running at the same time. Flushing the disk drives’ r

Re: [GENERAL] pgpool-II: cannot use serializable mode in a hot standby

> It should not forward transactions which are requested to be > SERIALIZABLE to standbys. If you just suppress the SET statement > (or substitute REPEATABLE READ), queries in that transaction can > return incorrect results. Yes. Once "SET default_transaction_isolation to serializable" (or its va

Re: [GENERAL] [HACKERS] sign function with INTERVAL?

On Wed, Apr 13, 2016 at 3:48 PM, Daniel Lenski wrote: > On Wed, Apr 13, 2016 at 12:35 PM, Tom Lane wrote: > > Jim Nasby writes: > >> Actually, after looking at the code for interval_lt, all that needs to > >> happen to add this support is to expose interval_cmp_internal() as a > >> strict funct

Re: [GENERAL] Trying to setup pglogical with no success

On 04/13/2016 03:16 PM, cchee-ob wrote: > Here's what I have done to try to set up pglogical. What am I doing wrong? Not sure. Did you do this?: http://2ndquadrant.com/en/resources/pglogical/pglogical-docs/ "The pglogical_output extension needs to be installed on both provider and subscriber.

Re: [GENERAL] Trying to setup pglogical with no success

On 4/13/2016 5:30 PM, Adrian Klaver wrote: "pg_hba.conf has to allow replication connections from localhost, by a user with replication privilege." Not sure if: host replication postgres 10.241.0.10/32 trust host replication postgres 10.241.0.12/32 trust I would add lines with 127.0.0.1/32

Re: [GENERAL] I/O - Increase RAM

On Wed, Apr 13, 2016 at 2:43 PM, drum.lu...@gmail.com wrote: > > Hi all, > > At the moment I'm having 100% I/O during the day. My server has SATA HDs, > and it can't be changed now. > So, to solve the problem (or at least try) I was thinking about double the > RAM, and by doing that, increasing t

Re: [GENERAL] I/O - Increase RAM

I'm using a MASTER server and a SLAVE as read-only as well. The results I'm posting here is related to the *master* server. > We're gonna need better stats. iostat, iotop, vmstat etc will all break > down your io between reads and writes, random vs sequential etc. > I'll try to get more data du

Re: [GENERAL] I/O - Increase RAM

On 4/13/2016 9:12 PM, drum.lu...@gmail.com wrote: I know.. but unfortunately the bosses don't want to spend money :( Time Actually Is Money. -- john r pierce, recycling bits in santa cruz -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscr

Re: [GENERAL] Multimaster

On 1 April 2016 at 19:50, Konstantin Knizhnik wrote: Right now the main problem is parallel apply: we need to apply changes > concurrently to avoid unintended dependencies causing deadlocks and provide > reasonable performance. > How do you intend to approach that? You should be able to do para

Re: [GENERAL] Multimaster

On 2 April 2016 at 02:15, Moreno Andreo wrote: > Actually we have to improve what our replicator is doing: it's only > replicating the single user's database. The improvement should that we can > put it on the "server" (in some cases there are groups of users sharing a > dedicated server) and, g

[GENERAL] Re: Why is the comparison between timestamp and date so much slower then between two dates

Tom Lane schrieb am 13.04.2016 um 15:45: >> So my question is: why is comparing a timestamp to a date so much slower? > > The date has to be up-converted to a timestamptz (not timestamp). > I think the expensive part of that is determining what timezone > applies, in particular whether DST is acti

[GENERAL] Re: Why is the comparison between timestamp and date so much slower then between two dates

Alban Hertroys schrieb am 13.04.2016 um 16:39: >>> So my question is: why is comparing a timestamp to a date so much slower? > > The reason that the other way around is so much more expensive is that > the database needs to do that conversion twice for every row in the > table. When down-converting