RE: Question about array_to_string()'s behavior and the behavior might be changed in the future or not

[Egashira, Yusuke]

Hi, Andrew

Thank you for immediate replying.
I'm relieved.

Regards.
--
Yusuke, Egashira

-Original Message-
From: Andrew Gierth [mailto:and...@tao11.riddles.org.uk] 
Sent: Thursday, January 17, 2019 3:56 PM
To: Egashira, Yusuke/江頭 勇佑 
Cc: 'pgsql-gene...@postgresql.org' 
Subject: Re: Question about array_to_string()'s behavior and the behavior might 
be changed in the future or not

> "Egashira" == Egashira, Yusuke  writes:

 Egashira> Hi, 
 Egashira> I have a question about array_to_string(). 

 Egashira> I think array_to_string() concatenates always array elements
 Egashira> from the "beginning" of the array elements and this rule will
 Egashira> not be changed on the future minor releases.

 Egashira> Is my understanding correct? 

The order of elements in the original array will always be preserved in
the string output, yes.

 Egashira> For example, I understand that array_to_string() "never"
 Egashira> output like following result now and in the future, is my
 Egashira> understanding correct?

Yes.

-- 
Andrew (irc:RhodiumToad)

Re: Weird behaviour of ROLLUP/GROUPING

[Andrew Gierth]

> "Guillaume" == Guillaume Lelarge  writes:

 >> I will see about fixing this, somehow.

 Guillaume> Thanks a lot.

I've committed a fix (to all supported branches, since this bug actually
precedes the addition of GROUPING SETS and can be triggered with a
simple GROUP BY if you try hard enough). The regression test says it
works now, but it'd be good if you could try it again on REL_11_STABLE
(at commit e74d8c5085 or later) to check that it fixes your case.

-- 
Andrew (irc:RhodiumToad)

Re: Question about array_to_string()'s behavior and the behavior might be changed in the future or not

[Andrew Gierth]

> "Egashira" == Egashira, Yusuke  writes:

 Egashira> Hi, 
 Egashira> I have a question about array_to_string(). 

 Egashira> I think array_to_string() concatenates always array elements
 Egashira> from the "beginning" of the array elements and this rule will
 Egashira> not be changed on the future minor releases.

 Egashira> Is my understanding correct? 

The order of elements in the original array will always be preserved in
the string output, yes.

 Egashira> For example, I understand that array_to_string() "never"
 Egashira> output like following result now and in the future, is my
 Egashira> understanding correct?

Yes.

-- 
Andrew (irc:RhodiumToad)

Question about array_to_string()'s behavior and the behavior might be changed in the future or not

[Egashira, Yusuke]

Hi, 

I have a question about array_to_string(). 
I think array_to_string() concatenates always array elements from the 
"beginning" of the array elements and this rule will not be changed on the 
future minor releases. 
Is my understanding correct? 

For example, I understand that array_to_string() "never" output like following 
result now and in the future, is my understanding correct? 

[Example] -> array_to_string(ARRAY[1, 2, 3, NULL, 5], ',', '*')
[Result]  -> 2,1,3,*,5

Currently I use PostgreSQL 9.5. 

Regards,

--
Yusuke, Egashira

Re: Can anyone please provide me list of customers using postgreSQL

[Brent Wood]

One comment I thought valuable after a large New Zealand Govt agency 
implemented a national GIS suite. This was their first install using Postgis 
instead of  the usual MS SQL Server.
I asked how they found Postgis/Postgres as a platform. I got a very succinct 
reply. 

"It's so easy to connect to, and sh** it's fast!"
In general, you don't need to know much more...

Brent Wood

  From: "Ramamoorthi, Meenakshi" 
 To: pgsql-general  
 Sent: Wednesday, January 16, 2019 8:42 AM
 Subject: Can anyone please provide me list of customers using postgreSQL
   
 Dear folks:    1)Can someone please send me a link 
of all companies using PostgreSQL ? 2)Both government and private companies 
using PostgreSQL 3)Any security issues found earlier and the steps taken 
for resolution or how it was mitigated. 4)Advantages of PostgreSQL compared 
to other databases like Oracle, MySQL etc.       Thanks and best regards 
Meenakshi Ramamoorthi             From: Alexander Farber 

Sent: Tuesday, January 15, 2019 11:24 AM
Cc: pgsql-general 
Subject: Re: aggregate functions are not allowed in UPDATE    And I should 
better change the avg_time column from TEXT to TIMESTAMPTZ (and use TO_CHAR on 
it later down the road) so that I can compare my players    Regards Alex 

   

Re: Varlena with recursive data structures?

[George Neuner]

On Wed, 16 Jan 2019 22:08:35 +, Sam Patterson
 wrote:

>Hi all,
>
>I've recently started developing an extension for Postgres for which I'll
>need to create a new variable-length base type. The type will require a
>tree-like structure in order to parse sufficiently, which of course
>probably means having some sort of recursive data structure, like a struct
>that has members which are pointers to itself for child nodes. After doing
>some research, specifically looking at how other variable-length data types
>store their data, it seems almost all of them store the data in a binary
>representation, using bit masks and offsets etc in order to store/access
>the data whilst having an in-memory representation that's used to
>manipulate the data.
>
>I presume the purpose for using this approach is because all the data in a
>varlena type has to be contiguous, and the moment you start using pointers
>this is no longer possible. So my question is, given a structure that looks
>something like this,
>
>typedef struct Node
>{
>char *data;
>Node *left;
>Node *right;
>} Node;
>
>am I right in saying that I wouldn't be able to store that representation
>on-disk, but instead I'd have to transform it into some binary
>representation and back again when writing/reading respectively, are there
>any alternatives?
>
>Regards,
>
>Karl


You might want to consider using a linear tree representation that can
be searched directly.  See:
https://en.wikipedia.org/w/index.php?title=Binary_tree=9#Arrays


E.g.,

Make the tree nodes:  { unsigned data, left, right }
Store the nodes contiguously as an array, and represent the left/right
branches using simple array indexing.

Write the node data contiguously into a separate array/stream, keeping
an offset [or index] to each data element in its correponding tree
node.

And then write the node array and the data array/stream contiguously
onto the disk.


For reading/searching this structure can be used directly: read in the
homogenous node array, and suck the variable length data stream into a
separate buffer.

If it isn't important that the tree be optimally ordered, then insert
is simple: just append a new node to the array, append its data to the
data stream, and update branch indexes to link the new node at the
correct spot in the tree.

If you want the data to be as small as possible on disk, then update
or delete of a node requires repacking the data and adjusting the data
offsets of the "unchanged" nodes.
https://en.wikipedia.org/wiki/Mark-compact_algorithm

[Obviously, you can delete without repacking by just unlinking the
tree node and rewriting the node array.  The "deleted" node data is
left in place on disk but it becomes unreachable.]

If you can afford to (temporarily) sort the tree nodes by their data
offset, repacking the data can be done in the same buffer by sliding
each element forward to be contiguous with the previous.  If you need
to take the nodes in whatever order they occur, then to pack the data
you need to copy it to a new buffer.


Hope this helps.
George

Re: Varlena with recursive data structures?

[Tom Lane]

Sam Patterson  writes:
> I presume the purpose for using this approach is because all the data in a
> varlena type has to be contiguous, and the moment you start using pointers
> this is no longer possible.

Yup.

> So my question is, given a structure that looks
> something like this,

> typedef struct Node
> {
> char *data;
> Node *left;
> Node *right;
> } Node;

> am I right in saying that I wouldn't be able to store that representation
> on-disk, but instead I'd have to transform it into some binary
> representation and back again when writing/reading respectively, are there
> any alternatives?

Yes, yes, no.  Any elementary datatype has to be able to be flattened into
a "blob of bytes" to be stored on disk.

However, you might be able to avoid working with the flattened
representation all the time.  There's an API for "expanded datums"
whereby functions can pass around an in-memory data structure that
doesn't have to look like a blob of bytes, and only flatten it out
when it's due to be stored somewhere.  I'm not sure how much that'd
help you, but it's possibly worth looking at.  See

src/include/utils/expandeddatum.h
src/backend/utils/adt/expandeddatum.c

for the basic APIs and

src/backend/utils/adt/array_expanded.c
src/backend/utils/adt/expandedrecord.c

for two examples of use.

regards, tom lane

Varlena with recursive data structures?

[Sam Patterson]

Hi all,

I've recently started developing an extension for Postgres for which I'll
need to create a new variable-length base type. The type will require a
tree-like structure in order to parse sufficiently, which of course
probably means having some sort of recursive data structure, like a struct
that has members which are pointers to itself for child nodes. After doing
some research, specifically looking at how other variable-length data types
store their data, it seems almost all of them store the data in a binary
representation, using bit masks and offsets etc in order to store/access
the data whilst having an in-memory representation that's used to
manipulate the data.

I presume the purpose for using this approach is because all the data in a
varlena type has to be contiguous, and the moment you start using pointers
this is no longer possible. So my question is, given a structure that looks
something like this,

typedef struct Node
{
char *data;
Node *left;
Node *right;
} Node;

am I right in saying that I wouldn't be able to store that representation
on-disk, but instead I'd have to transform it into some binary
representation and back again when writing/reading respectively, are there
any alternatives?

Regards,

Karl

problem in regard to hot standby

[aaaaaa]

Hi all,
I have encounted the problem with the hot standby, The master node was suddenly 
powered off,meanwhile,both two standby node had shut down end printed logs as 
followed:


PG version:9.6.8


2019-01-13 09:35:42.831 CST,,,28904,,5c332ceb.70e8,5,,2019-01-07 18:41:47 
CST,,0,LOG,0,"replication terminated by primary server","End of WAL reached 
on timeline 3 at 1/D49ADFF0.",,,"WalReceiverMain,walreceiver.c:456",""
2019-01-13 09:35:42.831 CST,,,28904,,5c332ceb.70e8,6,,2019-01-07 18:41:47 
CST,,0,FATAL,XX000,"could not send end-of-streaming message to primary:no COPY 
in progress""libpqrcv_endstreaming,libpqwalreceiver.c:282",""
2019-01-13 09:35:42.876 CST,,,28904,,5c332ceb.70e8,7,,2019-01-07 18:41:47 
CST,1/0,0,FATAL,XX000,"invalid memory alloc request size 
1701675008""palloc_extended,mcxt.c:961",""
2019-01-13 09:35:42.945 CST,,,28771,,5c332ceb.7063,3,,2019-01-07 18:41:47 
CST,,0,LOG,0,"startup process(PID 28883) exited with exit code 
1""LogChildExit,postmaster.c:3552",""
2019-01-13 09:35:42.945 CST,,,28771,,5c332ceb.7063,4,,2019-01-07 18:41:47 
CST,,0,LOG,0,"terminating any other active server 
processes""HandleChildCrash,postmaster.c:3294",""
2019-01-13 09:35:43.119 CST,,,28771,,5c332ceb.7063,5,,2019-01-07 18:41:47 
CST,,0,LOG,0,"database system is shut 
down""UnlinkLockFiles,miscinit.c:755",""




Can anyone please tell me why standby nodes shut down and what i suppose to do 
make the cluster back to normal again?


Regards

Re: Weird behaviour of ROLLUP/GROUPING

[Guillaume Lelarge]

Le mer. 16 janv. 2019 à 17:40, Andrew Gierth 
a écrit :

> > "Andrew" == Andrew Gierth  writes:
> > "Guillaume" == Guillaume Lelarge  writes:
>
>  Guillaume> CASE grouping(to_char(b, 'MM'))
>
>  Guillaume> ERROR:  arguments to GROUPING must be grouping expressions of
> the
>  Guillaume> associated query level
>  Guillaume> LINE 3: CASE grouping(to_char(b, 'MM')) WHEN 1 THEN 'some
> date' ...
>
>  Guillaume> AFAICT, both queries should behave the same, though their
>  Guillaume> actual behaviours are quite opposite. Working fine for the
>  Guillaume> first, erroring out on the second.
>
>  Guillaume> Does anyone has any idea what's going on here?
>
>  Andrew> Not yet. But I will find out, since it seems to be a bug.
>
> It is definitely a bug, to do with assignment of collations. It
> specifically happens when you use GROUPING which contains any
> subexpression of a collatable type, inside a CASE expr WHEN clause,
> since that assigns collations to the expression much earlier in parsing
> than the rest of the query, so the code that validates GROUPING ends up
> trying to compare an expression which has had collations assigned to it
> to one which has not, and so it thinks they differ.
>
> I will see about fixing this, somehow.
>
>
Thanks a lot.


-- 
Guillaume.

Re: Weird behaviour of ROLLUP/GROUPING

[Andrew Gierth]

> "Andrew" == Andrew Gierth  writes:
> "Guillaume" == Guillaume Lelarge  writes:

 Guillaume> CASE grouping(to_char(b, 'MM'))

 Guillaume> ERROR:  arguments to GROUPING must be grouping expressions of the
 Guillaume> associated query level
 Guillaume> LINE 3: CASE grouping(to_char(b, 'MM')) WHEN 1 THEN 'some date' 
...

 Guillaume> AFAICT, both queries should behave the same, though their
 Guillaume> actual behaviours are quite opposite. Working fine for the
 Guillaume> first, erroring out on the second.

 Guillaume> Does anyone has any idea what's going on here?

 Andrew> Not yet. But I will find out, since it seems to be a bug.

It is definitely a bug, to do with assignment of collations. It
specifically happens when you use GROUPING which contains any
subexpression of a collatable type, inside a CASE expr WHEN clause,
since that assigns collations to the expression much earlier in parsing
than the rest of the query, so the code that validates GROUPING ends up
trying to compare an expression which has had collations assigned to it
to one which has not, and so it thinks they differ.

I will see about fixing this, somehow.

-- 
Andrew (irc:RhodiumToad)

Re: Array_agg and dimensions in Array

[Alexey Bashtanov]

Hi Mike,

I have come across a problem which I cant seem to solve in a nice way
Basically I have a (small) table of tags
What I need to is combine two concatenated fields with a literal value 
as an array element.

You can create a custom aggregate function like this:

alexey@[local]/alexey=# create aggregate array_cat_agg(anyarray) (SFUNC 
= array_cat, STYPE = anyarray, COMBINEFUNC = array_cat, PARALLEL = SAFE);

CREATE AGGREGATE

And use it like this:

alexey@[local]/alexey=# select grp, array_cat_agg(array['--foo', bar || 
'=' || baz]) from (values ('g1', 'a', 'b'), ('g1', 'c', 'd'), ('g2', 
'e', 'f')) _ (grp, bar, baz) group by grp;

┌─┬───┐
│ grp │ array_cat_agg │
├─┼───┤
│ g2  │ {--foo,e=f}   │
│ g1  │ {--foo,a=b,--foo,c=d} │
└─┴───┘
(2 rows)

Is that what you need?

Best, Alex

Re: Weird behaviour of ROLLUP/GROUPING

[Andrew Gierth]

> "Guillaume" == Guillaume Lelarge  writes:

 Guillaume>   CASE grouping(to_char(b, 'MM'))

 Guillaume> ERROR:  arguments to GROUPING must be grouping expressions of the
 Guillaume> associated query level
 Guillaume> LINE 3: CASE grouping(to_char(b, 'MM')) WHEN 1 THEN 'some date' 
...

 Guillaume> AFAICT, both queries should behave the same, though their
 Guillaume> actual behaviours are quite opposite. Working fine for the
 Guillaume> first, erroring out on the second.

 Guillaume> Does anyone has any idea what's going on here?

Not yet. But I will find out, since it seems to be a bug.

-- 
Andrew (irc:RhodiumToad)

Re: Weird behaviour of ROLLUP/GROUPING

[Pavel Stehule]

st 16. 1. 2019 v 13:51 odesílatel Guillaume Lelarge 
napsal:

> Hi,
>
> One of my customers found something quite weird on his 9.6 cluster. Here
> is a quick demo showing the issue:
>
> -- quick demo table
> CREATE TABLE t1 (a integer, b timestamp, c integer);
>
> -- a working query
> SELECT
>   CASE grouping(a) WHEN 1 THEN 'some text' ELSE a::text END AS new_a,
>   CASE WHEN grouping(to_char(b, 'MM')) = 1
> THEN 'some date'
> ELSE to_char(b, 'MM') END AS new_b,
>   sum(c)
> FROM t1
> GROUP BY ROLLUP(a, to_char(b,'MM'));
>
> -- the non-working query
> SELECT
>   CASE grouping(a) WHEN 1 THEN 'some text' ELSE a::text END AS new_a,
>   CASE grouping(to_char(b, 'MM'))
> WHEN 1 THEN 'some date'
> ELSE to_char(b, 'MM') END AS new_b,
>   sum(c)
> FROM t1
> GROUP BY ROLLUP(a, to_char(b,'MM'));
> ERROR:  arguments to GROUPING must be grouping expressions of the
> associated query level
> LINE 3: CASE grouping(to_char(b, 'MM')) WHEN 1 THEN 'some date' ...
>   ^
>
> AFAICT, both queries should behave the same, though their actual
> behaviours are quite opposite. Working fine for the first, erroring out on
> the second.
>
> Does anyone has any idea what's going on here?
>
> BTW, it shows the same issue on 11.1 (actually HEAD on REL_11_STABLE).
>

looks like PostgreSQL bug - it cannot to work with subqueries correctly

Pavel


>
> Thanks.
>
> Regards.
>
>
> --
> Guillaume.
>

Weird behaviour of ROLLUP/GROUPING

[Guillaume Lelarge]

Hi,

One of my customers found something quite weird on his 9.6 cluster. Here is
a quick demo showing the issue:

-- quick demo table
CREATE TABLE t1 (a integer, b timestamp, c integer);

-- a working query
SELECT
  CASE grouping(a) WHEN 1 THEN 'some text' ELSE a::text END AS new_a,
  CASE WHEN grouping(to_char(b, 'MM')) = 1
THEN 'some date'
ELSE to_char(b, 'MM') END AS new_b,
  sum(c)
FROM t1
GROUP BY ROLLUP(a, to_char(b,'MM'));

-- the non-working query
SELECT
  CASE grouping(a) WHEN 1 THEN 'some text' ELSE a::text END AS new_a,
  CASE grouping(to_char(b, 'MM'))
WHEN 1 THEN 'some date'
ELSE to_char(b, 'MM') END AS new_b,
  sum(c)
FROM t1
GROUP BY ROLLUP(a, to_char(b,'MM'));
ERROR:  arguments to GROUPING must be grouping expressions of the
associated query level
LINE 3: CASE grouping(to_char(b, 'MM')) WHEN 1 THEN 'some date' ...
  ^

AFAICT, both queries should behave the same, though their actual behaviours
are quite opposite. Working fine for the first, erroring out on the second.

Does anyone has any idea what's going on here?

BTW, it shows the same issue on 11.1 (actually HEAD on REL_11_STABLE).

Thanks.

Regards.


-- 
Guillaume.

Re: lost "left join"

[Andrew Gierth]

> "Олег" == Олег Самойлов  writes:

 Олег> Hi, all.
 Олег> I got some mystic behaviour of PostgreSQL, perhaps this is a bug.

Feature, actually.

 Олег> But things begin be strange if I add validation by time.

 Олег> => explain select * from node as parent left join link on
 Олег> parent.node_id=link.parent left join node as child on
 Олег> link.child=child.node_id where parent.node_id=1 and current_date
 Олег> <@ parent.valid and current_date <@ link.valid and current_date
 Олег> <@ child.valid;

The problem here is that (for example) child.valid is null if there was
no matching child row in the join, and the planner knows that x <@ NULL
is not true (since the chosen <@ operator is defined as strict), and
therefore it knows that the left join is unnecessary and can be reduced
to an inner join.

At least 90% of the time when you refer to values from the nullable side
of a join in a WHERE clause, you're making a mistake (e.g. the condition
should have been in the ON clause instead). The other 10% or less of the
time, you have to make sure you use non-strict conditions, i.e. take
account of the fact that the values might be null.

-- 
Andrew (irc:RhodiumToad)

Re: Can anyone please provide me list of customers using postgreSQL

[Andreas Kretschmer]

Am 15.01.19 um 20:42 schrieb Ramamoorthi, Meenakshi:


Dear folks:

1)Can someone please send me a link of all companies using PostgreSQL ?



you can find some case studies here: 
https://www.2ndquadrant.com/en/about/case-studies/


We have a lot more customers, but i can't list them here, due to some 
reasons ...


Btw.: don't hijack other threads, please.


Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

Sv: lost "left join"

[Andreas Joseph Krogh]

På onsdag 16. januar 2019 kl. 11:54:21, skrev Олег Самойлов mailto:spl...@ya.ru>>:
Hi, all.

 I got some mystic behaviour of PostgreSQL, perhaps this is a bug.
 
[snip]

 But things begin be strange if I add validation by time.

 => explain select * from node as parent left join link on 
parent.node_id=link.parent left join node as child on link.child=child.node_id 
where parent.node_id=1 and current_date <@ parent.valid and current_date <@ 
link.valid and current_date <@ child.valid;
                                         QUERY PLAN
 
--
  Nested Loop  (cost=4.50..32.35 rows=1 width=112)
    ->  Nested Loop  (cost=4.35..21.88 rows=1 width=76)
          ->  Index Scan using node_pkey on node parent  (cost=0.15..8.18 
rows=1 width=36)
                Index Cond: (node_id = 1)
                Filter: (CURRENT_DATE <@ valid)
          ->  Bitmap Heap Scan on link  (cost=4.20..13.70 rows=1 width=40)
                Recheck Cond: (parent = 1)
                Filter: (CURRENT_DATE <@ valid)
                ->  Bitmap Index Scan on link_pkey  (cost=0.00..4.20 rows=6 
width=0)
                      Index Cond: (parent = 1)
    ->  Index Scan using node_pkey on node child  (cost=0.15..8.18 rows=1 
width=36)
          Index Cond: (node_id = link.child)
          Filter: (CURRENT_DATE <@ valid)
 (13 rows)

 «Left Join»’s are lost. And in the result too:

 => select * from node as parent left join link on parent.node_id=link.parent 
left join node as child on link.child=child.node_id where parent.node_id=1 and 
current_date <@ parent.valid and current_date <@ link.valid and current_date <@ 
child.valid;
  node_id | valid | parent | child | valid | node_id | valid
 -+---++---+---+-+---
 (0 rows)
 
The moment you involve columns on "left joined" relations this way in the 
WHERE-clause, it effectively becomes a right join.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andr...@visena.com 
www.visena.com 
 


 

lost "left join"

[Олег Самойлов]

Hi, all.

I got some mystic behaviour of PostgreSQL, perhaps this is a bug.

Tables is a model of a simple graph where parents and children related as «many 
to many». And there is a field «valid» to validate for current moment.

set search_path = 'left_join';
begin;
drop schema if exists left_join cascade;
create schema left_join;
create table node (
   node_id integer primary key generated always as identity,
   valid daterange not null
);
create table link (
   parent integer not null references node(node_id),
   child integer not null references node(node_id),
   valid daterange not null,
   primary key (parent,child)
);
insert into node (node_id,valid) values 
(default,daterange('2019-01-01','2020-01-01'));
commit;

Now I want to get a one node and all children, if they exists:

=> explain select * from node as parent left join link on 
parent.node_id=link.parent left join node as child on link.child=child.node_id 
where parent.node_id=1;
QUERY PLAN
--
 Nested Loop Left Join  (cost=4.50..26.76 rows=1 width=112)
   ->  Nested Loop Left Join  (cost=4.35..21.91 rows=1 width=76)
 Join Filter: (parent.node_id = link.parent)
 ->  Index Scan using node_pkey on node parent  (cost=0.15..8.17 rows=1 
width=36)
   Index Cond: (node_id = 1)
 ->  Bitmap Heap Scan on link  (cost=4.20..13.67 rows=6 width=40)
   Recheck Cond: (parent = 1)
   ->  Bitmap Index Scan on link_pkey  (cost=0.00..4.20 rows=6 
width=0)
 Index Cond: (parent = 1)
   ->  Index Scan using node_pkey on node child  (cost=0.15..4.84 rows=1 
width=36)
 Index Cond: (link.child = node_id)
(11 rows)

All fine, there is «Left Join» in the planner. And the result is exactly what I 
want:

=> select * from node as parent left join link on parent.node_id=link.parent 
left join node as child on link.child=child.node_id where parent.node_id=1;
 node_id |  valid  | parent | child | valid | node_id | valid
-+-++---+---+-+---
   1 | [2019-01-01,2020-01-01) ||   |   | |
(1 row)

But things begin be strange if I add validation by time.

=> explain select * from node as parent left join link on 
parent.node_id=link.parent left join node as child on link.child=child.node_id 
where parent.node_id=1 and current_date <@ parent.valid and current_date <@ 
link.valid and current_date <@ child.valid;
QUERY PLAN
--
 Nested Loop  (cost=4.50..32.35 rows=1 width=112)
   ->  Nested Loop  (cost=4.35..21.88 rows=1 width=76)
 ->  Index Scan using node_pkey on node parent  (cost=0.15..8.18 rows=1 
width=36)
   Index Cond: (node_id = 1)
   Filter: (CURRENT_DATE <@ valid)
 ->  Bitmap Heap Scan on link  (cost=4.20..13.70 rows=1 width=40)
   Recheck Cond: (parent = 1)
   Filter: (CURRENT_DATE <@ valid)
   ->  Bitmap Index Scan on link_pkey  (cost=0.00..4.20 rows=6 
width=0)
 Index Cond: (parent = 1)
   ->  Index Scan using node_pkey on node child  (cost=0.15..8.18 rows=1 
width=36)
 Index Cond: (node_id = link.child)
 Filter: (CURRENT_DATE <@ valid)
(13 rows)

«Left Join»’s are lost. And in the result too:

=> select * from node as parent left join link on parent.node_id=link.parent 
left join node as child on link.child=child.node_id where parent.node_id=1 and 
current_date <@ parent.valid and current_date <@ link.valid and current_date <@ 
child.valid;
 node_id | valid | parent | child | valid | node_id | valid
-+---++---+---+-+---
(0 rows) 

Re: Read consistency when using synchronous_commit=off

[Ron]

A crash-unsafe database is for data you don't care about.


On 1/16/19 2:27 AM, pshadangi wrote:
Thanks Ravi for the clarification, we will go ahead with 
"synchronous_commit=off".


On Wed, Jan 16, 2019 at 10:47 AM Ravi Krishna > wrote:


Sorry I misunderstood.  The term "read consistency" is generally used
either in the context of isolation level or in the context of slaves.


We don't have standby instance, as I have mentioned we are using just
one instance of postgres serving local clients running on the same
machine, do you know in this case what is the behavior ?



You are good.  All transactions update buffer cache too, along with
WAL buffer and hence other sessions can immediately see the changes.
synchronous_commit=off will only reduce the fsync calls, which makes
them less crash safe, but the database consistency is not compromised.




--
Angular momentum makes the world go 'round.

Re: Read consistency when using synchronous_commit=off

[pshadangi]

Thanks Ravi for the clarification, we will go ahead with
"synchronous_commit=off".

On Wed, Jan 16, 2019 at 10:47 AM Ravi Krishna 
wrote:

> Sorry I misunderstood.  The term "read consistency" is generally used
> either in the context of isolation level or in the context of slaves.
>
> We don't have standby instance, as I have mentioned we are using just one
> instance of postgres serving local clients running on the same machine, do
> you know in this case what is the behavior ?
>
>
> You are good.  All transactions update buffer cache too, along with WAL
> buffer and hence other sessions can immediately see the changes.
> synchronous_commit=off will only reduce the fsync calls, which makes them
> less crash safe, but the database consistency is not compromised.
>
>
>
>
>