Adding new collations after pg_upgrade?

[rihad]

Hi. on a freshly installed 10.6 I can see all ICU collations added in 
pg_collation schema. Is there a way to have them for an existing 
database cluster?

Is it impolite to dump a message

[Thiemo Kellner]

Hi all

I posted a question and did not get a reaction. Now I wonder whether  
no one took interest (no offence meant) no one has an answer or  
whether the point in time I posted was just rubbish. In the latter  
case I would be inclined to dump/repost my question but only if this  
was not impolite. Is there a policy?


Kind regards

Thiemo
--
Signal: +49 1578 7723737
Handys: +41 78 947 36 21 | +49 1578 772 37 37
Tox-Id:  
B7282A9E31D944DDAD1ECC5B33C36AAE80B32D119AB7391D602C937A65E8CA0625C495C5322B

Re: Is there something wrong with my test case?

[Peter J. Holzer]

On 2018-12-25 11:54:11 +, Thiemo Kellner wrote:
[three different but functionally equivalent queries]

> Explain analyze verbose showed for:
> A (cost=264.72..626.97 rows=31 width=90) (actual time=1.117..1.117 rows=0
> loops=1)
> B (cost=264.72..974.25 rows=31 width=58) (actual time=1.508..1.508 rows=0
> loops=1)
> C (cost=0.42..611.19 rows=31 width=52) (actual time=2.217..2.217 rows=0
> loops=1)
> 
> I am very surprised that the cost of A is (much) higher than that of C which

626.97 doesn't seem "much higher" to me than 611.19. I would call that
"about the same".

> I suspected to be the most inefficient. I was that much fixed on the costs
> that I initially ignored the actual time where my assumptions on efficiency
> are reflected. Funny though is that the subjective impression when waiting
> for the update queries to complete was that C was fastest by far, followed
> by B and only at the end was update A.

This is weird. C takes almost exactly twice as long as A, and while
humans aren't very good at estimating times, One second should feel
faster than two, not slower, and certainly not slower by far. Is it
possible that your subjective impression wasn't based on the executions
you posted but on others? Caching and load spikes can cause quite large
variations in run time, so running the same query again may not take the
same time (usually the second time is faster - sometimes much faster).

hp

-- 
   _  | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophisticated
| |   | h...@hjp.at | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson 


signature.asc
Description: PGP signature

Re: Is it impolite to dump a message

[Stephen Frost]

Greetings,

* Thiemo Kellner (thi...@gelassene-pferde.biz) wrote:
> I posted a question and did not get a reaction. Now I wonder whether no one
> took interest (no offence meant) no one has an answer or whether the point
> in time I posted was just rubbish. In the latter case I would be inclined to
> dump/repost my question but only if this was not impolite. Is there a
> policy?

The most that can really be drawn is "no one responded".  I wouldn't
read more into it than that- any of the above are possible and it's most
likely some combination of those and other factors across the various
people on the list.

The way to address that, in my view at least, would be to respond to the
email you sent to the list, after waiting a while- don't bombard the
list, but after a week is probably alright.  In that response, ideally,
you'd include what updates you have, things you've done to try and
figure out the answer yourself, etc.

Ultimately, though, these lists are essentially all monitored and
responded to on a volunteer basis and your question may continue to go
unanswered indefinitely.  If you'd like a higher level of service, then
there are support companies available for PostgreSQL who are happy to
provide it.

Thanks!

Stephen


signature.asc
Description: PGP signature

Re: Adding new collations after pg_upgrade?

[Tom Lane]

rihad  writes:
> Hi. on a freshly installed 10.6 I can see all ICU collations added in 
> pg_collation schema. Is there a way to have them for an existing 
> database cluster?

There's a function called something like pg_import_system_collations.
See documentation.

regards, tom lane

Re: Adding new collations after pg_upgrade?

[rihad]

On 01/06/2019 07:51 PM, Tom Lane wrote:

rihad  writes:

Hi. on a freshly installed 10.6 I can see all ICU collations added in
pg_collation schema. Is there a way to have them for an existing
database cluster?

There's a function called something like pg_import_system_collations.
See documentation.


Thanks a lot!

postgres=# select pg_import_system_collations('pg_catalog');
 pg_import_system_collations
-
 798
(1 row)

postgres=# select pg_import_system_collations('pg_catalog');
 pg_import_system_collations
-
   0
(1 row)




regards, tom lane
.

Re: Adding new collations after pg_upgrade?

[rihad]

On 01/06/2019 07:57 PM, rihad wrote:

On 01/06/2019 07:51 PM, Tom Lane wrote:

rihad  writes:

Hi. on a freshly installed 10.6 I can see all ICU collations added in
pg_collation schema. Is there a way to have them for an existing
database cluster?

There's a function called something like pg_import_system_collations.
See documentation.


Thanks a lot!

postgres=# select pg_import_system_collations('pg_catalog');
 pg_import_system_collations
-
 798
(1 row)

postgres=# select pg_import_system_collations('pg_catalog');
 pg_import_system_collations
-
   0
(1 row)


Oops, I meant schema pg_collation )





    regards, tom lane
.

Re: Adding new collations after pg_upgrade?

[rihad]

On 01/06/2019 08:05 PM, rihad wrote:

On 01/06/2019 07:57 PM, rihad wrote:

On 01/06/2019 07:51 PM, Tom Lane wrote:

rihad  writes:

Hi. on a freshly installed 10.6 I can see all ICU collations added in
pg_collation schema. Is there a way to have them for an existing
database cluster?

There's a function called something like pg_import_system_collations.
See documentation.


Thanks a lot!

postgres=# select pg_import_system_collations('pg_catalog');
 pg_import_system_collations
-
 798
(1 row)

postgres=# select pg_import_system_collations('pg_catalog');
 pg_import_system_collations
-
   0
(1 row)


Oops, I meant schema pg_collation )


Phew, sorry for my confusion. Schema pg_catalog. Table pg_collation. So 
I did it right )







    regards, tom lane
.

About SSL connection

[John Mikel]

Hi,
i was trying to enable SSL on postgresql 10 and 11 Servers after searching
on postregsql documentation i found this :

ALTER SYSTEM SET *configuration_parameter* { TO | = } { *value* |
'*value*' | DEFAULT }

on https://www.postgresql.org/docs/10/sql-altersystem.html
i used

ALTER SYSTEM SET SSL = ON , querry executed successfully

after that i entred this Select *  from pg_settings , i saw that ssl
value is off,

after  stoping the service of postgresql it wont start again

i tried this on both servers 11 and 10 the same problem (the server
wont start again)

I'm using widonws 7 x64.

my question is  how to enbale SSL ?

ps : i created all require cert files.

sorry for my bad english.

Re: Is it impolite to dump a message

[Ron]

On 1/6/19 9:33 AM, Stephen Frost wrote:

Greetings,

* Thiemo Kellner (thi...@gelassene-pferde.biz) wrote:

I posted a question and did not get a reaction. Now I wonder whether no one
took interest (no offence meant) no one has an answer or whether the point
in time I posted was just rubbish. In the latter case I would be inclined to
dump/repost my question but only if this was not impolite. Is there a
policy?

The most that can really be drawn is "no one responded".  I wouldn't
read more into it than that- any of the above are possible and it's most
likely some combination of those and other factors across the various
people on the list.


Especially posts that were sent on Christmas morning.


--
Angular momentum makes the world go 'round.

Re: About SSL connection

[Ron]

On 1/6/19 8:10 AM, John Mikel wrote:


Hi,
i was trying to enable SSL on postgresql 10 and 11 Servers after searching
on postregsql documentation i found this :
ALTER SYSTEM SET/|configuration_parameter|/  { TO | = } {/|value|/  | 
'/|value|/' | DEFAULT }
on https://www.postgresql.org/docs/10/sql-altersystem.html
i used
ALTER SYSTEM SET SSL = ON , querry executed successfully
after that i entred this Select *  from pg_settings , i saw that ssl value is 
off,
after  stoping the service of postgresql it wont start again
i tried this on both servers 11 and 10 the same problem (the server wont start 
again)
I'm using widonws 7 x64.
my question is  how to enbale SSL ?
ps : i created all require cert files.


What errors in the log files?

--
Angular momentum makes the world go 'round.

Re: Adding LEFT JOIN to a query has increased execution time 10 times

[Alexander Farber]

Good evening, thank you for the useful hints!

With the further help of the IRC folks the query has been optimized (7-10
seconds -> 0.3 second) by adding the following indices:

CREATE INDEX ON words_games (player1, COALESCE(finished, 'INFINITY'));
CREATE INDEX ON words_games (player2, COALESCE(finished, 'INFINITY'));
CREATE INDEX ON words_moves (gid, played DESC);
CREATE INDEX ON words_social (uid, stamp DESC);
CREATE INDEX ON words_geoip USING SPGIST (block);

and switching to LEFT JOIN LATERAL for finding the most recent records in
words_moves and words_social tables:

SELECT
...
-- find move record with the most recent timestamp
LEFT JOIN LATERAL (SELECT * FROM words_moves m WHERE m.gid = g.gid ORDER BY
m.played DESC LIMIT 1) AS m ON TRUE
LEFT JOIN words_geoip i2 ON (CASE WHEN g.player1 = in_uid THEN u2.ip ELSE
u1.ip END) <<= i2.block
LEFT JOIN LATERAL (SELECT * FROM words_social AS s1 WHERE s1.uid = u1.uid
ORDER BY s1.stamp DESC LIMIT 1) AS s1 ON TRUE
LEFT JOIN LATERAL (SELECT * FROM words_social AS s2 WHERE s2.uid = u2.uid
ORDER BY s2.stamp DESC LIMIT 1) AS s2 ON TRUE
...
WHERE in_uid IN (g.player1, g.player2)
AND COALESCE(g.finished, 'INFINITY') > (CURRENT_TIMESTAMP - INTERVAL '1
day')

Also I have increased the following parameters  in postgresql.conf -

from_collapse_limit = 24
join_collapse_limit = 24

Now the whole query looks as following and the EXPLAIN output pasted is
below -

Query - http://dpaste.com/1AQM800
Explain - https://explain.depesz.com/s/pq79

EXPLAIN ANALYZE SELECT
g.gid,
EXTRACT(EPOCH FROM g.created)::int AS created,
EXTRACT(EPOCH FROM g.finished)::int AS finished,
g.letters AS letters,
g.values AS values,
g.bid AS bid,
CARDINALITY(g.pile) AS pilelen,
m.tiles AS tiles,
m.score AS score,
CASE WHEN g.player1 = 5 THEN g.player1 ELSE
g.player2 END AS player1,
CASE WHEN g.player1 = 5 THEN g.player2 ELSE
g.player1 END AS player2,
CASE WHEN g.player1 = 5 THEN g.score1  ELSE
g.score2  END AS score1,
CASE WHEN g.player1 = 5 THEN g.score2  ELSE
g.score1  END AS score2,
CASE WHEN g.player1 = 5 THEN g.state1  ELSE
g.state2  END AS state1,
CASE WHEN g.player1 = 5 THEN g.hint1   ELSE g.hint2
  END AS hint1,
CASE WHEN g.player1 = 5 THEN g.chat1   ELSE g.chat2
  END AS chat1,
u1.elo AS elo1,
u2.elo AS elo2,
i2.lat AS lat2,
i2.lng AS lng2,
s1.given AS given1,
s2.given AS given2,
s1.photo AS photo1,
s2.photo AS photo2,
EXTRACT(EPOCH FROM CASE WHEN g.player1 = 5 THEN
g.played1 ELSE g.played2 END)::int AS played1,
EXTRACT(EPOCH FROM CASE WHEN g.player1 = 5 THEN
g.played2 ELSE g.played1 END)::int AS played2,
ARRAY_TO_STRING(CASE WHEN g.player1 = 5 THEN
g.hand1 ELSE g.hand2 END, '') AS hand1,
CASE
WHEN g.finished IS NOT NULL THEN NULL
WHEN g.player2 IS NULL THEN NULL
WHEN g.player1 = 5 AND g.played1 <
g.played2
THEN EXTRACT(EPOCH FROM g.played2 +
interval '24 hour' - CURRENT_TIMESTAMP)::int
WHEN g.player2 = 5 AND (g.played2 IS NULL
OR g.played2 < g.played1)
THEN EXTRACT(EPOCH FROM g.played1 +
interval '24 hour' - CURRENT_TIMESTAMP)::int
ELSE NULL
END AS left1,
CASE
WHEN g.finished IS NOT NULL THEN NULL
WHEN g.player2 IS NULL THEN NULL
WHEN g.player1 = 5 AND (g.played2 IS NULL
OR g.played2 < g.played1)
THEN EXTRACT(EPOCH FROM g.played1 +
interval '24 hour' - CURRENT_TIMESTAMP)::int
WHEN g.player2 = 5 AND g.played1 <
g.played2
THEN EXTRACT(EPOCH FROM g.played2 +
interval '24 hour' - CURRENT_TIMESTAMP)::int
ELSE NULL
END AS left2
FROM words_games g
LEFT JOIN LATERAL (select tiles, score from words_moves m
where m.gid = g.gid order by m.played desc limit 1) as m on true
LEFT JOIN words_users u1 ON u1.uid = 5
LEFT JOIN words_users u2 ON u2.uid = (CASE WHEN g.player1 =
5 THEN g.player2 ELSE g.player1 END)
LEFT JOIN word

Re: About SSL connection

[Adrian Klaver]

On 1/6/19 6:10 AM, John Mikel wrote:


Hi,
i was trying to enable SSL on postgresql 10 and 11 Servers after searching
on postregsql documentation i found this :

ALTER SYSTEM SET/|configuration_parameter|/  { TO | = } {/|value|/  | 
'/|value|/' | DEFAULT }

on https://www.postgresql.org/docs/10/sql-altersystem.html
i used

ALTER SYSTEM SET SSL = ON , querry executed successfully

after that i entred this Select *  from pg_settings , i saw that ssl value is 
off,

after  stoping the service of postgresql it wont start again


In addition to what Ron said about checking the logs, you could change 
the ssl setting back to 'off' in the postgresql.conf file.




i tried this on both servers 11 and 10 the same problem (the server wont start 
again)

I'm using widonws 7 x64.

my question is  how to enbale SSL ?

ps : i created all require cert files.

sorry for my bad english.




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

Re: Adding LEFT JOIN to a query has increased execution time 10 times

[legrand legrand]

Alexander Farber wrote
> Good evening, thank you for the useful hints!
> 
> With the further help of the IRC folks the query has been optimized (7-10
> seconds -> 0.3 second) by adding the following indices:
> 
> CREATE INDEX ON words_games (player1, COALESCE(finished, 'INFINITY'));
> CREATE INDEX ON words_games (player2, COALESCE(finished, 'INFINITY'));
> CREATE INDEX ON words_moves (gid, played DESC);
> CREATE INDEX ON words_social (uid, stamp DESC);
> CREATE INDEX ON words_geoip USING SPGIST (block);
> 
> and switching to LEFT JOIN LATERAL for finding the most recent records in
> words_moves and words_social tables:
> 
> [...]
> 
>  Planning time: 0.587 ms
>  Execution time: 0.367 ms
> (36 rows)
> 
> I was told that it still could be improved (by rearranging WHERE clauses?)
> 
> Regards
> Alex

Hi Alexander,

It seems that you have done a very nice tuning exercise with this query, 
that finishes now in less than 1 ms !!!

and I have learned about LEFT JOIN LATERAL syntax too !

As you didn't spoke about DML activity ... May I suggest you to take some
time to monitor the
application before to continue optimizing this query ?

Take time to check that:
- the result is ok,
- performances are stable,
- there is no regression on other queries,
- inserts,updates, deletes, copy are still working fast,
- size of added objects are coherent and stable,
- query complexity stay manageable,
- there is no other application part to optimize,
- ...

Regards
PAscal




--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Adding LEFT JOIN to a query has increased execution time 10 times

[Andrew Gierth]

> "Alexander" == Alexander Farber  writes:

 Alexander> Good evening, thank you for the useful hints!

 Alexander> With the further help of the IRC folks the query has been
 Alexander> optimized (7-10 seconds -> 0.3 second)

0.3 MILLIseconds, actually.

(You chanced not to catch me around on IRC, but I see that didn't
matter.)

 Alexander> by adding the following indices:

 Alexander> CREATE INDEX ON words_games (player1, COALESCE(finished, 
'INFINITY'));
 Alexander> CREATE INDEX ON words_games (player2, COALESCE(finished, 
'INFINITY'));

 Alexander> CREATE INDEX ON words_moves (gid, played DESC);
 Alexander> CREATE INDEX ON words_social (uid, stamp DESC);

I'm not a big fan of using DESC on indexes; it's almost never needed,
because any btree index can be scanned in reverse. (You only actually
need it if you're mixing ASC/DESC orderings in an ORDER BY and want an
index that matches it.)

 Alexander> Also I have increased the following parameters in
 Alexander> postgresql.conf -

 Alexander> from_collapse_limit = 24
 Alexander> join_collapse_limit = 24

Ironically, I think these settings don't affect the query now since
removing the EXISTS conditions (which count as joins) means there are
now less than 8 joined tables. But keeping them high is probably a good
idea so that you don't get problems if you ever add another join or two.

 Alexander> Now the whole query looks as following and the EXPLAIN
 Alexander> output pasted is below -

Just for future reference, when you include explain output in your email
(which you should, for the benefit of the archives - paste sites and
explain.depesz.com have limited lifetimes), it's best if you can make
sure your email client doesn't word-wrap them into near-unreadability.

 Alexander> I was told that it still could be improved (by rearranging
 Alexander> WHERE clauses?)

Maybe, but once you're down to sub-millisecond execution times, further
optimization is usually only worthwhile for very heavily executed
queries.

-- 
Andrew (irc:RhodiumToad)

Re: Is there something wrong with my test case?

[David Rowley]

On Wed, 26 Dec 2018 at 00:54, Thiemo Kellner
 wrote:
> Explain analyze verbose showed for:
> A (cost=264.72..626.97 rows=31 width=90) (actual time=1.117..1.117
> rows=0 loops=1)
> B (cost=264.72..974.25 rows=31 width=58) (actual time=1.508..1.508
> rows=0 loops=1)
> C (cost=0.42..611.19 rows=31 width=52) (actual time=2.217..2.217
> rows=0 loops=1)
>
> I am very surprised that the cost of A is (much) higher than that of C
> which I suspected to be the most inefficient. I was that much fixed on
> the costs that I initially ignored the actual time where my
> assumptions on efficiency are reflected. Funny though is that the
> subjective impression when waiting for the update queries to complete
> was that C was fastest by far, followed by B and only at the end was
> update A.

While the times mentioned in "actual time" are for execution only and
don't account for the time taken to plan the query, the results you
put in [1] disagree entirely with your claim that 'C' was faster. 'A'
comes out fastest with the explain analyzes you've listed.

A:
Planning TIME: 0.423 ms
Execution TIME: 1.170 ms

C:
Planning TIME: 0.631 ms
Execution TIME: 2.281 ms

Have you confused each of the results, perhaps because they're in a
different order as to your cases above?

I'd certainly expect 'A' to be the fastest of the bunch since it's
both less effort for the planner and also the executor.  I didn't look
at why the cost is estimated to be slightly higher, but the planner
wouldn't consider rewriting the queries to one of the other cases
anyway, so it's likely not that critical that the costings are
slightly out from reality.

> where U.KEY_U in ({correlated subquery 3})

This is not correlated in [1].

[1]  https://pastebin.com/W2HsTBwi

-- 
 David Rowley   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: Watching for view changes

[Mitar]

Hi!

I have a followup to this thread.

George, thanks for all the help. I spend some time now investigating
various approaches here and I am reporting here some findings, so that
they might help others as well.

First, I have tried the approach with MATERIALIZED VIEWs to hold the
cached contents of the query. I attached triggers to all source tables
which then notified (using LISTEN/NOTIFY) the client about changes.
Client throttled those notifications and eventually triggered a
REFRESH MATERIALIZED VIEW. Because I also attached triggers on the
materialized view, I got notifications (using LISTEN/NOTIFY) of what
rows have changed. Client might decided to fetch also rows themselves.
For this to work well I made two patches. A patch to allow creation of
TEMPORARY MATERIALIZED VIEWs [1] and a patch to allow attaching
triggers on materialized views [2]. In the second patch I also changed
the REFRESH MATERIALIZED VIEW CONCURRENTLY logic to issue not just
REMOVEs and INSERTs for changed rows, but to compute which rows have
changed and issue UPDATEs for them as well. This makes it easier for
the client to know what changed.

This worked well but it was slower than some other packages I have
found on the web which were trying to provide a similar functionality.
I made a benchmark to compare them [3] and found out that there was
room for improvement.

First, instead of sending updates of a MATERIALIZED VIEW using
LISTEN/NOTIFY and then fetching rows, I instead used a trigger to copy
changes to another temporary table, and then just used DELETE FROM
temp_table RETURNING * to get all results from a table and returning
it to the client, all inside same transaction, so data in that
temporary table was never committed.

This made things a bit better, mostly latency between a change and
getting it to the client became more predictable. Before there were
sometimes quite large spikes. Once I did that I realized that in fact
MATERIALIZED VIEW is not really necessary. All I need is a place to
cache previous results of the query, but I do not really care about
updates to the MATERIALIZED VIEW. So I decided to do the following. If
I already have a temporary table with previous results, when I want to
refresh my query, I create a new temporary table using CREATE TABLE AS
using the PREPAREd query, I compute diff between those two tables in
the same way as REFRESH MATERIALIZED VIEW CONCURRENTLY does, in one
query, and I return those results to the client. Then I just DROP
TABLE old cache table, and rename new cache table to old cache name.
So instead of computing a diff, updating materialized view, running
triggers, and copying to the table, I just compute a diff and this is
it.

This works now very well. I made and published a package doings this [4].

Of course, the main problem is still that for every change in source
table I have to eventually refresh the query. And without more logic
this can really become problematic. Ideally, we could reuse some
ideas/code from Incremental View Maintenance [5] to compute what
changes to the query results should happen based on changes to source
tables. Then we could just push those to the client (and update
temporary table). We would then do full refresh only when things could
not be inferred.

Thanks again for everything.

[1] https://commitfest.postgresql.org/21/1951/
[2] https://commitfest.postgresql.org/21/1948/
[3] https://github.com/mitar/node-pg-reactivity-benchmark
[4] https://github.com/tozd/node-reactive-postgres
[5] 
https://www.postgresql.org/message-id/flat/20181227215726.4d166b4874f8983a641123f5%40sraoss.co.jp


Mitar

-- 
http://mitar.tnode.com/
https://twitter.com/mitar_m

Optimizing the same PREPAREd static query (without parameters)

[Mitar]

Hi!

If I have a PREPAREd query without parameters (static) and I EXECUTE
it repeatedly in the same session, does PostgreSQL learn/optimize
anything across those runs?


Mitar

-- 
http://mitar.tnode.com/
https://twitter.com/mitar_m

Re: Use bytearray for blobs or not?

[Achilleas Mantzios]

On 4/1/19 6:02 μ.μ., Rob Sargent wrote:


On 1/4/19 4:48 AM, Achilleas Mantzios wrote:

On 4/1/19 1:41 μ.μ., Thomas Güttler wrote:

Some months ago I wrote a little application with Python+Django which stores
blob data in bytearrays.

It works.

In the future there will be a lot more traffic, and I am unsure
if this is really a good solution. I know this is more FUD than
concrete PG issue.

What do you think?


Performance (at least for JDBC) is known to be better with blobs.


Do you have any details on which part is slower with bytea? Original insert, read back to client, general (p)sql access.  I'm moving towards bytea but still have time to change my mind if I cannot 
afford the performance hit.

No since I never used them (blobs) in production. Basically the java driver 
does not support streaming. So for very large data (e.g. videos, movies, etc) 
people use blobs.



However, with bytea life is just easier for many reasons (backups, logical 
replication, other types of replication, sys management, etc).

Yes, and in my case I even get a cheap compression from the original file.




Which alternatives could be useful?

Regards,
  Thomas












--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt