Re: Mixed Locales and Upgrading

[Peter Eisentraut]

On 2020-04-07 18:41, Don Seiler wrote:
Follow-up question, the locale setting on the host would still be set to 
en_US (as would the postgres and template0 databases). Should I look to 
change that locale on the system to en_US.UTF-8, or even just for the 
postgres user that the DB cluster runs as? What are the ramification for 
doing (or not doing) so?


I think the only place where this would really matter is that psql by 
default sets the client encoding based on the current OS locale setting. 
 So if you don't change the setting to en_US.UTF-8, then you might get 
encoding errors when selecting data that is not representable as LATIN1 
or whatever.  However, if you change the setting, that doesn't mean your 
terminal setup will actually display Unicode correctly.  You said you're 
dealing with mostly ASCII-ish data anyway, so it will probably not make 
a difference.


--
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Estimated resources for a 500 connections instance (VM)

[Laurenz Albe]

On Tue, 2020-04-07 at 15:51 -0400, David Gauthier wrote:
> But for my edification, is it roughly true that 2 connections working with 
> the DB 100%
> of the time is equivalent to 20 connections @ 10% = 200 connections @ 1 % (if 
> you know what I mean) ?

Roughly, yes.
There is a certain overhead, as each query has to take a snapshot of the
database, which has to consider all active connections.

But the main problem is that you have no way to ensure that all those
idle connections stay idle.

Imagine that most of these sessions issue short statements
against "table1".  There are also some longer SELECTs.
No problem so far.
Now along comes a statement that blocks the table for a very short
time, like a CREATE INDEX CONCURRENTLY.
Unfortunately, that ACCESS EXCLUSIVE lock has to wait for one of
the longer SELECTs to finish.
Now all those moderately short statements cannot run any more, but
they have to queue behind the ACCESS EXCLUSIVE lock. For a
while, nothing happens.  Then, as soon as the CREATE INDEX CONCURRENTLY
has got its lock, done its work and finished, all hell breaks loose,
because all sessions start executing their statements at the same time.

I have seen databases servers go down because of such events, and
the problem is the high number of connections, even if they are idle
most of the time.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: Performance degradation if query returns no rows and column expression is used after upgrading to 12

[Pavel Stehule]

Hi

út 7. 4. 2020 v 23:56 odesílatel Andrus  napsal:

> Hi!
>
> >It is really strange why it is too slow. Can you prepare test case? Looks
> like bug (maybe not Postgres's bug)
>
> Testcase is below.
> With  jit on it takes 3.3 sec  and with jit off 1.5 sec.
>
> Andrus.
>
> create temp table toode ( toode char(20), ribakood char(20),
>  nimetus char(50), markused char(50), engnimetus
> char(50) ) on commit drop;
> insert into toode (toode) select generate_series(1,14400);
> CREATE INDEX ON toode USING gin
> (to_tsvector('english'::regconfig, nimetus::text));
> CREATE UNIQUE INDEXON toode (ribakood )
> WHERE ribakood IS NOT NULL AND btrim(ribakood::text) <> ''::text;
> CREATE INDEX ON toode (toode);
> CREATE UNIQUE INDEXON toode (upper(toode::text) );
> create temp table dok ( dokumnr serial  primary key ) on commit drop;
> insert into dok  select generate_series(1,14400);
>
> create temp table rid ( dokumnr int, taitmata numeric, toode char(20)  )
> on commit drop;
> insert into rid  select generate_series(1,144);
>
> CREATE INDEX rid_dokumnr_idxON rid (dokumnr );
> -- jit on: 3.3 sec  jit off: 1.5 sec
> set jit to off;
> select
> (select sum(taitmata) from rid join dok using (dokumnr)  where
> toode=toode.toode ) ,
> (select sum(taitmata) from rid join dok using (dokumnr)  where
> toode=toode.toode ) ,
> (select sum(taitmata) from rid join dok using (dokumnr)  where
> toode=toode.toode ) ,
> (select sum(taitmata) from rid join dok using (dokumnr)  where
> toode=toode.toode ) ,
> (select sum(taitmata) from rid join dok using (dokumnr)  where
> toode=toode.toode ) ,
> (select sum(taitmata) from rid join dok using (dokumnr)  where
> toode=toode.toode ) ,
> (select sum(taitmata) from rid join dok using (dokumnr)  where
> toode=toode.toode ) ,
> (select sum(taitmata) from rid join dok using (dokumnr)  where
> toode=toode.toode ) ,
> (select sum(taitmata) from rid join dok using (dokumnr)  where
> toode=toode.toode ) ,
>   (select sum(taitmata) from rid join dok using (dokumnr)  where
> toode=toode.toode ) ,
> (select sum(taitmata) from rid join dok using (dokumnr)  where
> toode=toode.toode ) ,
> (select sum(taitmata) from rid join dok using (dokumnr)  where
> toode=toode.toode ) ,
> (select sum(taitmata) from rid join dok using (dokumnr)  where
> toode=toode.toode ) ,
> (select sum(taitmata) from rid join dok using (dokumnr)  where
> toode=toode.toode ) ,
> (select sum(taitmata) from rid join dok using (dokumnr)  where
> toode=toode.toode ) ,
> (select sum(taitmata) from rid join dok using (dokumnr)  where
> toode=toode.toode ) ,
> (select sum(taitmata) from rid join dok using (dokumnr)  where
> toode=toode.toode ) ,
> (select sum(taitmata) from rid join dok using (dokumnr)  where
> toode=toode.toode )
>
> from toode
> where toode.ribakood='testmiin'::text
>or toode.nimetus ilike '%'||'testmiin'||'%' escape '!'
>or toode.toode ilike '%'||'testmiin'||'%' escape '!'
>or toode.markused ilike '%'||'testmiin'||'%' escape '!'
>
> or to_tsvector('english',toode.nimetus) @@ plainto_tsquery('testmiin')
>  or to_tsvector('english',toode.engnimetus) @@
>  plainto_tsquery('testmiin')
>

this query is little bit strange - it has pretty big cost, and because
returns nothing, then it's pretty fast against cost.

there is 18 subqueries, but jit_above_cost is ralated just to one query.
This is probably worst case for JIT.

This query is pretty slow and expensive (and then the cost of JIT is
minimal), but when the query returns some rows, then JIT start to helps.

So maybe if you find some queries that returns some rows, then the speed
will be better with active JIT than with disabled JIT.

The situation when the query returns no rows, then JIT is significant
bottleneck - but it looks like corner case.

Regards

Pavel

Re: Mixed Locales and Upgrading

[Peter J. Holzer]

On 2020-03-30 17:30:32 -0400, Tom Lane wrote:
> Don Seiler  writes:
> > Actually, would I need to re-index on text columns that we know contain
> > UUID strings? UUID characters seem to be pretty basic alphanumeric ASCII
> > characters.
> 
> I think you're all right with respect to those, since they're the
> same under any encoding.  It's columns containing non-ASCII characters
> that you'd want to worry about reindexing.

I'm not so sure whether all locales agree on whether to sort digits
before or after letters. However there are only two possibilities and
that's easy to check.

hp

-- 
   _  | Peter J. Holzer| Story must make more sense than reality.
|_|_) ||
| |   | h...@hjp.at |-- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |   challenge!"


signature.asc
Description: PGP signature

Re: Performance degradation if query returns no rows and column expression is used after upgrading to 12

[Andrus]

Hi


>this query is little bit strange - it has pretty big cost, and because returns 
>nothing, then it's pretty fast against cost.  
>there is 18 subqueries, but jit_above_cost is ralated just to one query. This 
>is probably worst case for JIT.
>This query is pretty slow and expensive (and then the cost of JIT is minimal), 
>but when the query returns some rows, then JIT start to helps. 
>So maybe if you find some queries that returns some rows, then the speed will 
>be better with active JIT than with disabled JIT.

Below is modified testcase which returns one row.
In Debian 10 VPS with jit on it takes 2.5 sec  and with jit off  0.4 s

jit is still many times slower in Debian even if data is returned.

In Windows 10 workstation there is no difference.

>The situation when the query returns no rows, then JIT is significant 
>bottleneck - but it looks like corner case.

Both testcases simulate search queries in typical e-shop. 
Users can use any search term and expect that query returns fast.

Modified testcase which returns one row:

create temp table toode ( toode char(20) primary key, ribakood char(20), 
 nimetus char(50), markused char(50), engnimetus char(50) ) on 
commit drop;
insert into toode (toode) select generate_series(1,14400);
insert into toode (toode,nimetus) 
select 'TEST'|| generate_series, 'This is testmiin item'
  from generate_series(1,1);

CREATE INDEX ON toode USING gin(to_tsvector('english'::regconfig, 
nimetus::text));
CREATE UNIQUE INDEXON toode (ribakood )
WHERE ribakood IS NOT NULL AND btrim(ribakood::text) <> ''::text;
create temp table dok ( dokumnr serial  primary key ) on commit drop;
insert into dok  select generate_series(1,1);

create temp table rid (id serial primary key, 
   dokumnr int references dok, taitmata numeric, toode 
char(20) references toode ) on commit drop;
insert into rid  (dokumnr,toode)
select generate_series % 1+1, 1
from  generate_series(1,1);

CREATE INDEX ON rid(dokumnr );
CREATE INDEX ON rid(toode);
-- jit on: 2.5 sec  jit off:  0.4 s
set jit to off;
select 
(select sum(taitmata) from rid join dok using (dokumnr)  where 
toode=toode.toode ) ,
(select sum(taitmata) from rid join dok using (dokumnr)  where 
toode=toode.toode ) ,
(select sum(taitmata) from rid join dok using (dokumnr)  where 
toode=toode.toode ) ,
(select sum(taitmata) from rid join dok using (dokumnr)  where 
toode=toode.toode ) ,
(select sum(taitmata) from rid join dok using (dokumnr)  where 
toode=toode.toode ) ,
(select sum(taitmata) from rid join dok using (dokumnr)  where 
toode=toode.toode ) ,
(select sum(taitmata) from rid join dok using (dokumnr)  where 
toode=toode.toode ) ,
(select sum(taitmata) from rid join dok using (dokumnr)  where 
toode=toode.toode ) ,
(select sum(taitmata) from rid join dok using (dokumnr)  where 
toode=toode.toode ) ,
  (select sum(taitmata) from rid join dok using (dokumnr)  where 
toode=toode.toode ) ,
(select sum(taitmata) from rid join dok using (dokumnr)  where 
toode=toode.toode ) ,
(select sum(taitmata) from rid join dok using (dokumnr)  where 
toode=toode.toode ) ,
(select sum(taitmata) from rid join dok using (dokumnr)  where 
toode=toode.toode ) ,
(select sum(taitmata) from rid join dok using (dokumnr)  where 
toode=toode.toode ) ,
(select sum(taitmata) from rid join dok using (dokumnr)  where 
toode=toode.toode ) ,
(select sum(taitmata) from rid join dok using (dokumnr)  where 
toode=toode.toode ) ,
(select sum(taitmata) from rid join dok using (dokumnr)  where 
toode=toode.toode ) ,
(select sum(taitmata) from rid join dok using (dokumnr)  where 
toode=toode.toode ) 

from toode 
where toode.ribakood='testmiin'::text
   or toode.nimetus ilike '%'||'testmiin'||'%' escape '!' 
   or toode.toode ilike '%'||'testmiin'||'%' escape '!' 
   or toode.markused ilike '%'||'testmiin'||'%' escape '!'

or to_tsvector('english',toode.nimetus) @@ plainto_tsquery('testmiin') 
 or to_tsvector('english',toode.engnimetus) @@
 plainto_tsquery('testmiin')  

Andrus.

Best method to display table information in predefined formats

[Mark Bannister]

I am converting an application to postgresql.  On feature I have is
functions that return custom displays of a table row.  For instance the
company display function might display just the company name, or company
name + other information.  It may also call other displays, for
instance, address or phone numbers which in turn have display
functions.  What is returned depends on context and other parameters. 
The return value is typically displayed the ui in views of the table. 
For instance a listing of purchase orders could list PO number, and use
the company display function to list the company information.  Depending
on the display it could show just the company name or complete details
of the company (in the context of the PO).

There is also a generic function that will display per-defined default
fields so that a custom function is not required for each table.

What is the best way to reproduce this and take advantage of postgresql
caching?  These functions can be called a lot.

-- 

Mark B

Re: Performance degradation if query returns no rows and column expression is used after upgrading to 12

[Pavel Stehule]

st 8. 4. 2020 v 15:34 odesílatel Andrus  napsal:

> Hi
>
> >this query is little bit strange - it has pretty big cost, and because
> returns nothing, then it's pretty fast against cost.
> >there is 18 subqueries, but jit_above_cost is ralated just to one query.
> This is probably worst case for JIT.
> >This query is pretty slow and expensive (and then the cost of JIT is
> minimal), but when the query returns some rows, then JIT start to helps.
> >So maybe if you find some queries that returns some rows, then the speed
> will be better with active JIT than with disabled JIT.
>
> Below is modified testcase which returns one row.
> In Debian 10 VPS with jit on it takes 2.5 sec  and with jit off  0.4 s
>
> jit is still many times slower in Debian even if data is returned.
>
> In Windows 10 workstation there is no difference.
>

if I know it well, then there JIT is disabled


> >The situation when the query returns no rows, then JIT is significant
> bottleneck - but it looks like corner case.
>
> Both testcases simulate search queries in typical e-shop.
> Users can use any search term and expect that query returns fast.
>
> Modified testcase which returns one row:
>
> create temp table toode ( toode char(20) primary key, ribakood char(20),
>  nimetus char(50), markused char(50), engnimetus char(50)
> ) on commit drop;
> insert into toode (toode) select generate_series(1,14400);
> insert into toode (toode,nimetus)
> select 'TEST'|| generate_series, 'This is testmiin item'
>   from generate_series(1,1);
>

one row is probably too less - the overhead of JIT is fix, but benefit of
JIT is linear


> CREATE INDEX ON toode USING gin(to_tsvector('english'::regconfig,
> nimetus::text));
> CREATE UNIQUE INDEXON toode (ribakood )
> WHERE ribakood IS NOT NULL AND btrim(ribakood::text) <> ''::text;
> create temp table dok ( dokumnr serial  primary key ) on commit drop;
> insert into dok  select generate_series(1,1);
>
> create temp table rid (id serial primary key,
>dokumnr int references dok, taitmata numeric, toode
> char(20) references toode ) on commit drop;
> insert into rid  (dokumnr,toode)
> select generate_series % 1+1, 1
> from  generate_series(1,1);
>
> CREATE INDEX ON rid(dokumnr );
> CREATE INDEX ON rid(toode);
> -- jit on: 2.5 sec  jit off:  0.4 s
> set jit to off;
> select
> (select sum(taitmata) from rid join dok using (dokumnr)  where
> toode=toode.toode ) ,
> (select sum(taitmata) from rid join dok using (dokumnr)  where
> toode=toode.toode ) ,
> (select sum(taitmata) from rid join dok using (dokumnr)  where
> toode=toode.toode ) ,
> (select sum(taitmata) from rid join dok using (dokumnr)  where
> toode=toode.toode ) ,
> (select sum(taitmata) from rid join dok using (dokumnr)  where
> toode=toode.toode ) ,
> (select sum(taitmata) from rid join dok using (dokumnr)  where
> toode=toode.toode ) ,
> (select sum(taitmata) from rid join dok using (dokumnr)  where
> toode=toode.toode ) ,
> (select sum(taitmata) from rid join dok using (dokumnr)  where
> toode=toode.toode ) ,
> (select sum(taitmata) from rid join dok using (dokumnr)  where
> toode=toode.toode ) ,
>   (select sum(taitmata) from rid join dok using (dokumnr)  where
> toode=toode.toode ) ,
> (select sum(taitmata) from rid join dok using (dokumnr)  where
> toode=toode.toode ) ,
> (select sum(taitmata) from rid join dok using (dokumnr)  where
> toode=toode.toode ) ,
> (select sum(taitmata) from rid join dok using (dokumnr)  where
> toode=toode.toode ) ,
> (select sum(taitmata) from rid join dok using (dokumnr)  where
> toode=toode.toode ) ,
> (select sum(taitmata) from rid join dok using (dokumnr)  where
> toode=toode.toode ) ,
> (select sum(taitmata) from rid join dok using (dokumnr)  where
> toode=toode.toode ) ,
> (select sum(taitmata) from rid join dok using (dokumnr)  where
> toode=toode.toode ) ,
> (select sum(taitmata) from rid join dok using (dokumnr)  where
> toode=toode.toode )
>
> from toode
> where toode.ribakood='testmiin'::text
>or toode.nimetus ilike '%'||'testmiin'||'%' escape '!'
>or toode.toode ilike '%'||'testmiin'||'%' escape '!'
>or toode.markused ilike '%'||'testmiin'||'%' escape '!'
>
> or to_tsvector('english',toode.nimetus) @@ plainto_tsquery('testmiin')
>  or to_tsvector('english',toode.engnimetus) @@
>  plainto_tsquery('testmiin')
>
> Andrus.
>

Re: Best method to display table information in predefined formats

[Adrian Klaver]

On 4/8/20 6:39 AM, Mark Bannister wrote:
I am converting an application to postgresql.  On feature I have is 
functions that return custom displays of a table row.  For instance the 
company display function might display just the company name, or company 
name + other information.  It may also call other displays, for 
instance, address or phone numbers which in turn have display 
functions.  What is returned depends on context and other parameters.  
The return value is typically displayed the ui in views of the table.  
For instance a listing of purchase orders could list PO number, and use 
the company display function to list the company information.  Depending 
on the display it could show just the company name or complete details 
of the company (in the context of the PO).


There is also a generic function that will display per-defined default 
fields so that a custom function is not required for each table.


What is the best way to reproduce this and take advantage of postgresql 
caching?  These functions can be called a lot.


I am not understanding what you are after, especially this part:

"The return value is typically displayed the ui in views of the table"

Are you saying the functions are called to fill in fields in a UI form 
or to populate a database side view?




--

Mark B




--
Adrian Klaver
adrian.kla...@aklaver.com

Unexpected behavior sorting strings

[Jimmy Thrasher]

I'm seeing some unexpected behavior when sorting some strings, and it indicates 
I don't fully understand how postgresql string sorting works.

As I understand it, postgresql sorts strings roughly like strcmp does: 
character by character based on encoding value.

In particular, I'm seeing the following. I would expect "< S" to come first, 
because "<" (0x3c) is less than ">" (0x3e).

```
supercatdev=# select unnest(array['> N', '< S']) as s order by s;
  s
-
 > N
 < S
(2 rows)
```

I've broken this down further:
```
supercatdev=# select '> N' < '< S';
 ?column?
--
 t
(1 row)
```

Am I missing something about how sorting works?

Metadata:
- postgresql 9.5.19, running on Ubuntu 16LTS
- encoding, collate, and ctype are all UTF8 or en_US.UTF-8, as appropriate

Thanks!

Jimmy

Re: Best method to display table information in predefined formats

[Mark Bannister]

On 4/8/2020 10:28 AM, Adrian Klaver wrote:
> On 4/8/20 6:39 AM, Mark Bannister wrote:
>> I am converting an application to postgresql.  On feature I have is
>> functions that return custom displays of a table row.  For instance
>> the company display function might display just the company name, or
>> company name + other information.  It may also call other displays,
>> for instance, address or phone numbers which in turn have display
>> functions.  What is returned depends on context and other
>> parameters.  The return value is typically displayed the ui in views
>> of the table.  For instance a listing of purchase orders could list
>> PO number, and use the company display function to list the company
>> information.  Depending on the display it could show just the company
>> name or complete details of the company (in the context of the PO).
>>
>> There is also a generic function that will display per-defined
>> default fields so that a custom function is not required for each table.
>>
>> What is the best way to reproduce this and take advantage of
>> postgresql caching?  These functions can be called a lot.
>
> I am not understanding what you are after, especially this part:
>
> "The return value is typically displayed the ui in views of the table"
>
> Are you saying the functions are called to fill in fields in a UI form
> or to populate a database side view?
>
>>
>> -- 
>>
>> Mark B
>>
>
>

Yes.  That's correct.

-- 

Mark B

Re: Best method to display table information in predefined formats

[Adrian Klaver]

On 4/8/20 8:39 AM, Mark Bannister wrote:


On 4/8/2020 10:28 AM, Adrian Klaver wrote:

On 4/8/20 6:39 AM, Mark Bannister wrote:
I am converting an application to postgresql.  On feature I have is 
functions that return custom displays of a table row.  For instance 
the company display function might display just the company name, or 
company name + other information.  It may also call other displays, 
for instance, address or phone numbers which in turn have display 
functions.  What is returned depends on context and other 
parameters.  The return value is typically displayed the ui in views 
of the table.  For instance a listing of purchase orders could list 
PO number, and use the company display function to list the company 
information.  Depending on the display it could show just the company 
name or complete details of the company (in the context of the PO).


There is also a generic function that will display per-defined 
default fields so that a custom function is not required for each table.


What is the best way to reproduce this and take advantage of 
postgresql caching?  These functions can be called a lot.


I am not understanding what you are after, especially this part:

"The return value is typically displayed the ui in views of the table"

Are you saying the functions are called to fill in fields in a UI form 
or to populate a database side view?




--

Mark B






Yes.  That's correct.


Which is correct filling in fields, populating a view or both?



--

Mark B




--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Unexpected behavior sorting strings

[Tom Lane]

"Jimmy Thrasher"  writes:
> As I understand it, postgresql sorts strings roughly like strcmp does: 
> character by character based on encoding value.

Only if you're using C locale.  Other locales such as en_US have
completely different rules, which most hackers tend to find pretty
unintelligible and inconsistent :-(.  In your example, I think
the first-pass sort is on just the letters, and only if those are
the same will it consider the punctuation.

regards, tom lane

Re: Unexpected behavior sorting strings

[Adrian Klaver]

On 4/8/20 7:35 AM, Jimmy Thrasher wrote:

I'm seeing some unexpected behavior when sorting some strings, and it indicates 
I don't fully understand how postgresql string sorting works.

As I understand it, postgresql sorts strings roughly like strcmp does: 
character by character based on encoding value.

In particular, I'm seeing the following. I would expect "< S" to come first, because "<" 
(0x3c) is less than ">" (0x3e).

```
supercatdev=# select unnest(array['> N', '< S']) as s order by s;
   s
-
  > N
  < S
(2 rows)
```

I've broken this down further:
```
supercatdev=# select '> N' < '< S';
  ?column?
--
  t
(1 row)
```

Am I missing something about how sorting works?


I believe you are looking for 'C' collation:

test=# select unnest(array[('> N' collate "C") , ('< S' COLLATE "C")]) 
as s order by s;


  s
-
 < S
 > N
(2 rows)


For more information see:

https://www.postgresql.org/docs/12/collation.html



Metadata:
- postgresql 9.5.19, running on Ubuntu 16LTS
- encoding, collate, and ctype are all UTF8 or en_US.UTF-8, as appropriate

Thanks!

Jimmy





--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Best method to display table information in predefined formats

[melvin6925]

Sounds like you should design a function to call custom views based on what is 
needed.Sent via the Samsung Galaxy S® 6, an AT&T 4G LTE smartphone
 Original message From: Mark Bannister 
 Date: 4/8/20  11:39  (GMT-05:00) To: Adrian 
Klaver , pgsql-general@lists.postgresql.org Subject: 
Re: Best method to display table information in predefined formats 


On 4/8/2020 10:28 AM, Adrian Klaver
  wrote:

On
  4/8/20 6:39 AM, Mark Bannister wrote:
  
  I am converting an application to
postgresql.  On feature I have is functions that return custom
displays of a table row.  For instance the company display
function might display just the company name, or company name +
other information.  It may also call other displays, for
instance, address or phone numbers which in turn have display
functions.  What is returned depends on context and other
parameters.  The return value is typically displayed the ui in
views of the table.  For instance a listing of purchase orders
could list PO number, and use the company display function to
list the company information.  Depending on the display it could
show just the company name or complete details of the company
(in the context of the PO).


There is also a generic function that will display per-defined
default fields so that a custom function is not required for
each table.


What is the best way to reproduce this and take advantage of
postgresql caching?  These functions can be called a lot.

  
  
  I am not understanding what you are after, especially this part:
  
  
  "The return value is typically displayed the ui in views of the
  table"
  
  
  Are you saying the functions are called to fill in fields in a UI
  form or to populate a database side view?
  
  
  

-- 

Mark B


  
  
  



Yes.  That's correct.



-- 
  Mark B 
   

  

Re: PostgreSQL native multi-master

[Stephen Frost]

Greetings,

* Vano Beridze (vanua...@gmail.com) wrote:
> What are the plans to support multi-master natively?
> What solution would you recommend at this point? preferably free.

You probably want to look at logical replication, which allows you to
replicate data from one PG server to another (with both allowing
writes).  This doesn't have any conflict resolution today, so you have
to make sure there aren't any conflicts or you might end up breaking the
replication.

Having conflict resolution included in core would certainly be nice but
I'm not aware of anyone currently working on it.  I'm sure there are
organizations out there who are committed to open source and who would
be happy to work with you to add that capability to PG though.

Thanks,

Stephen


signature.asc
Description: PGP signature

Re: Using of --data-checksums

[Stephen Frost]

Greetings,

* BGoebel (b.goe...@prisma-computer.de) wrote:
> initdb --data-checksums "... help to detect corruption by the I/O system"
> There is an (negligible?) impact on performance, ok. 
>  
> Is there another reason NOT to use this feature ?

Not in my view.

> Has anyone had good or bad experience with the use of  --data-checksums?

Have had good experience with it.  We should really make it the default
already.

Thanks,

Stephen


signature.asc
Description: PGP signature

full text

[Roberto Della Pasqua]

Please sorry because I'm newbie of PGSQL

I need the best performing and overall quality full-text search, can be 
possible to have the data stored in pgsql and the index to elasticsearch? Can 
be in sync between?

Thank you

Btw. Do you suggest another engine than elastic?

Roberto Della Pasqua
www.dellapasqua.com

Re: Unexpected behavior sorting strings

[Jimmy Thrasher]

Many thanks! That clarifies things well.

Jimmy

On Wed, Apr 8, 2020, at 11:49 AM, Adrian Klaver wrote:
> On 4/8/20 7:35 AM, Jimmy Thrasher wrote:

> > Am I missing something about how sorting works?
> 
> I believe you are looking for 'C' collation:
> 
> test=# select unnest(array[('> N' collate "C") , ('< S' COLLATE "C")]) 
> as s order by s;
> 
>s
> -
>   < S
>   > N
> (2 rows)
> 
> 
> For more information see:
> 
> https://www.postgresql.org/docs/12/collation.html

Re: full text

[Joshua Drake]

Roberto,

I would look at ZomboDB: https://www.zombodb.com/ . Which is exactly what
you suggest.

JD

On Wed, Apr 8, 2020 at 10:28 AM Roberto Della Pasqua <
roberto.dellapas...@live.com> wrote:

> Please sorry because I’m newbie of PGSQL
>
>
>
> I need the best performing and overall quality full-text search, can be
> possible to have the data stored in pgsql and the index to elasticsearch?
> Can be in sync between?
>
>
>
> Thank you
>
>
>
> Btw. Do you suggest another engine than elastic?
>
>
>
> Roberto Della Pasqua
>
> www.dellapasqua.com
>
>
>