EXPLAIN BUFFERS and I/O timing accounting questions

Hello, I ran across an EXPLAIN plan and had some questions about some of its details. The BUFFERS docs say >The number of blocks shown for an upper-level node includes those used by all its child nodes. I initially assumed this would be cumulative, but I realized it's probably not because some o

Re: A question about sequences and backup/restore cycles

On 10/21/19 5:13 PM, stan wrote: I typically design a system with primary keys defined, like this: CREATE TABLE employee ( employee_key integer DEFAULT nextval('employee_key_serial') PRIMARY KEY , I use scripts to build the database structures and load the data. I am

A question about sequences and backup/restore cycles

I typically design a system with primary keys defined, like this: CREATE TABLE employee ( employee_key integer DEFAULT nextval('employee_key_serial') PRIMARY KEY , I use scripts to build the database structures and load the data. I am careful to get the dependencies i

existing dblinks

Hello, is there any way to find if there are any dblink exist on the 9.6 postgresql server? Thanks

Re: jsonb_set() strictness considered harmful to data

Steven Pousty wrote: > On Sun, Oct 20, 2019 at 4:31 PM raf wrote: > > > Steven Pousty wrote: > > > > > I would think though that raising an exception is better than a > > > default behavior which deletes data. > > > > I can't help but feel the need to make the point that > > the function is not

Re: Calling jsonb_array_elements 4 times in the same query

On 10/21/19 1:30 PM, Alexander Farber wrote: Apologies, I should have shown the JSON structure in my very first email - On Mon, Oct 21, 2019 at 4:45 PM Thomas Kellerer > wrote: Use ->> to return the value as text (not as JSONB) and you need to use the column

Re: jsonb_set() strictness considered harmful to data

On 10/21/19 12:50 PM, Tomas Vondra wrote: On Mon, Oct 21, 2019 at 08:06:46AM -0700, Adrian Klaver wrote: On 10/20/19 11:07 PM, Tomas Vondra wrote: On Sun, Oct 20, 2019 at 06:51:05PM -0400, Andrew Dunstan wrote: True. And AFAIK catching exceptions is not really possible in some code, e.g. in

Re: Calling jsonb_array_elements 4 times in the same query

Apologies, I should have shown the JSON structure in my very first email - On Mon, Oct 21, 2019 at 4:45 PM Thomas Kellerer wrote: > Use ->> to return the value as text (not as JSONB) and you need to use the > column alias, not the table alias: > > (t.tile ->> 'col')::int > > It is a JSON-arr

Re: jsonb_set() strictness considered harmful to data

On Mon, Oct 21, 2019 at 08:06:46AM -0700, Adrian Klaver wrote: On 10/20/19 11:07 PM, Tomas Vondra wrote: On Sun, Oct 20, 2019 at 06:51:05PM -0400, Andrew Dunstan wrote: True. And AFAIK catching exceptions is not really possible in some code, e.g. in stored procedures (because we can't do sub

Re: jsonb_set() strictness considered harmful to data

On 21/10/2019 17:39, Steven Pousty wrote:  Turning a JSON null into a SQL null  and thereby "deleting" the data is not the path of least surprises. In what situation does that happen? (If it's already been mentioned I missed it, long thread, sorry). Cheers,   Steve

Re: CPU SPIKE

You'll have to provide more data: On Mon, Oct 21, 2019 at 7:14 AM Pawan Sharma wrote: > Having real high CPU issue (95-98%), with SELECT statements and select > queries contains multiple AND operator, is it will cause any CPU Spike..??? If you do a query the only reason it is not hitting 100% c

Re: jsonb_set() strictness considered harmful to data

On Sun, Oct 20, 2019 at 4:31 PM raf wrote: > Steven Pousty wrote: > > > I would think though that raising an exception is better than a > > default behavior which deletes data. > > I can't help but feel the need to make the point that > the function is not deleting anything. It is just > returnin

Re: jsonb_set() strictness considered harmful to data

On Sun, Oct 20, 2019 at 3:51 PM Andrew Dunstan < andrew.duns...@2ndquadrant.com> wrote: > I'm not arguing against the idea of improving the situation. But I am > arguing against a minimal fix that will not provide much of value to a > careful app developer. i.e. I want to do more to support app de

Re: Postgres Point in time Recovery (PITR),

Hi > On 22/10/2019, at 4:14 AM, Adrian Klaver wrote: > > On 10/21/19 8:10 AM, Avinash Kumar wrote: >> Hi, >> On Mon, Oct 21, 2019 at 8:16 PM Alan Hodgson > > wrote: >>On Mon, 2019-10-21 at 16:40 +0530, Avinash Kumar wrote: >>> >>>We need to ensure that w

Re: Postgres Point in time Recovery (PITR),

On Mon, Oct 21, 2019 at 8:47 PM Alan Hodgson wrote: > On Mon, 2019-10-21 at 20:40 +0530, Avinash Kumar wrote: > > can't he destroy the offline backups and your database ? > This is not a right justification to encouraging Offline Backups over > Online Backups. > If you are worried about storing y

Re: Postgres Point in time Recovery (PITR),

On Mon, 2019-10-21 at 20:40 +0530, Avinash Kumar wrote: > can't he destroy the offline backups and your database ? > This is not a right justification to encouraging Offline Backups over > Online Backups. > If you are worried about storing your online backups through internet > on cloud (i do no

Re: Postgres Point in time Recovery (PITR),

On 10/21/19 8:10 AM, Avinash Kumar wrote: Hi, On Mon, Oct 21, 2019 at 8:16 PM Alan Hodgson > wrote: On Mon, 2019-10-21 at 16:40 +0530, Avinash Kumar wrote: We need to ensure that we have safe backup locations, for example, push them to AWS S3 and

Re: Postgres Point in time Recovery (PITR),

Hi, On Mon, Oct 21, 2019 at 8:16 PM Alan Hodgson wrote: > On Mon, 2019-10-21 at 16:40 +0530, Avinash Kumar wrote: > > > We need to ensure that we have safe backup locations, for example, push > them to AWS S3 and forget about redundancy. > Why do you think only Offline Backups are reliable today

Re: jsonb_set() strictness considered harmful to data

On 10/20/19 11:07 PM, Tomas Vondra wrote: On Sun, Oct 20, 2019 at 06:51:05PM -0400, Andrew Dunstan wrote: True. And AFAIK catching exceptions is not really possible in some code, e.g. in stored procedures (because we can't do subtransactions, so no exception blocks). Can you explain the ab

Re: Postgres Point in time Recovery (PITR),

On Mon, 2019-10-21 at 16:40 +0530, Avinash Kumar wrote: > We need to ensure that we have safe backup locations, for example, > push them to AWS S3 and forget about redundancy. > Why do you think only Offline Backups are reliable today ? There have been examples of hackers gaining control of an o

Re: Calling jsonb_array_elements 4 times in the same query

> I am trying to create the following strored function based on your suggestion > (and I have forgotten to mention, that I also need the board id aka bid from > another table, words_games), but hit the next problem: > > CREATE OR REPLACE FUNCTION words_get_move( >     in_mid inte

Re: Calling jsonb_array_elements 4 times in the same query

On 10/21/19 6:39 AM, Alexander Farber wrote: Hello, good afternoon! With PostgreSQL 10 I host a word game, which stores player moves as a JSON array of objects with properties: col, row, value, letter - CREATE TABLE words_moves (     mid BIGSERIAL PRIMARY KEY,     action  text N

Re: Calling jsonb_array_elements 4 times in the same query

Thank you Thomas - On Mon, Oct 21, 2019 at 4:24 PM Thomas Kellerer wrote: > Alexander Farber schrieb am 21.10.2019 um 15:39: > > I am trying to construct a query, which would draw a game board when > given a move id (aka mid): > > > > SELECT > > hand, > > JSONB_ARRAY_ELEMENTS

Re: Calling jsonb_array_elements 4 times in the same query

Alexander Farber schrieb am 21.10.2019 um 15:39: > I am trying to construct a query, which would draw a game board when given a > move id (aka mid): > >     SELECT >     hand, >     JSONB_ARRAY_ELEMENTS(tiles)->'col' AS col, >     JSONB_ARRAY_ELEMENTS(tiles)->'row' AS row, >     J

Calling jsonb_array_elements 4 times in the same query

Hello, good afternoon! With PostgreSQL 10 I host a word game, which stores player moves as a JSON array of objects with properties: col, row, value, letter - CREATE TABLE words_moves ( mid BIGSERIAL PRIMARY KEY, action text NOT NULL, gid integer NOT NULL REFERENCE

Re: jsonb_set() strictness considered harmful to data

On 10/21/19 2:07 AM, Tomas Vondra wrote: > On Sun, Oct 20, 2019 at 06:51:05PM -0400, Andrew Dunstan wrote: >> >>> I think the general premise of this thread is that the application >>> developer does not realize that may be necessary, because it's a bit >>> surprising behavior, particularly when

Re: Postgres Point in time Recovery (PITR),

On 21/10/2019 12:10, Avinash Kumar wrote: On Mon, Oct 21, 2019 at 4:19 PM Fabio Ugo Venchiarutti mailto:f.venchiaru...@ocado.com>> wrote: On 21/10/2019 09:52, Luca Ferrari wrote: > On Sat, Oct 19, 2019 at 7:46 PM Daulat Ram mailto:daulat@exponential.com>> wrote: >> One

Re: Postgres Point in time Recovery (PITR),

On Mon, Oct 21, 2019 at 4:19 PM Fabio Ugo Venchiarutti < f.venchiaru...@ocado.com> wrote: > On 21/10/2019 09:52, Luca Ferrari wrote: > > On Sat, Oct 19, 2019 at 7:46 PM Daulat Ram > wrote: > >> One more questions is, how backups are useful if we have streaming > replication . As I know, we can pr

Re: Postgres Point in time Recovery (PITR),

On 21/10/2019 09:52, Luca Ferrari wrote: On Sat, Oct 19, 2019 at 7:46 PM Daulat Ram wrote: One more questions is, how backups are useful if we have streaming replication . As I know, we can promote the standby as primary in case of disaster at primary side. Do we need to schedule backups if w

Re: Postgres Point in time Recovery (PITR),

On Sat, Oct 19, 2019 at 7:46 PM Daulat Ram wrote: > One more questions is, how backups are useful if we have streaming > replication . As I know, we can promote the standby as primary in case of > disaster at primary side. Do we need to schedule backups if we have streaming > replication? Let'