Re: Please recommend postgresql.conf improvements for osm2pgsql loading Europe

2024-03-31 Thread Alexander Farber
Thank you for the insights

Re: Please recommend postgresql.conf improvements for osm2pgsql loading Europe

2024-03-30 Thread Alexander Farber
Turning fsync = off has resulted in no noticable build time reduction for my Dockerfile with OSM Europe data, but still thank you for the suggestion! >

Re: Please recommend postgresql.conf improvements for osm2pgsql loading Europe

2024-03-30 Thread Alexander Farber
Thank you, Justin - On Sat, Mar 30, 2024 at 4:33 AM Justin Clift wrote: > On 2024-03-30 05:53, Alexander Farber wrote: > > I use the following postgresql.conf in my Dockerfile > > ( the full version at https://stackoverflow.com/a/78243530/165071 ), > > when loading a

Please recommend postgresql.conf improvements for osm2pgsql loading Europe

2024-03-29 Thread Alexander Farber
Good evening, I use the following postgresql.conf in my Dockerfile ( the full version at https://stackoverflow.com/a/78243530/165071 ), when loading a 28 GByte large europe-latest.osm.pbf into PostgreSQL 16 with PostGIS extension: echo "shared_buffers = 1GB">>

Re: [jetty-users] Run multiple instances of the same WAR servlet and pass it a config value

2023-10-04 Thread Alexander Farber via jetty-users
Apologies, the getServletConfig().getContextPath() in the init() of my servlet works well, that probably had been a problem in my VS Code. Still I wonder, if I would set a "property" with a "name" and "value" as described in the "Jetty XML Syntax" section

Re: [jetty-users] Run multiple instances of the same WAR servlet and pass it a config value

2023-10-04 Thread Alexander Farber via jetty-users
I try to use the following but the method is not available: @Override public void init() throws ServletException { super.init(); mLanguage = getServletConfig().getContextPath(); } ___ jetty-users mailing list

[jetty-users] Run multiple instances of the same WAR servlet and pass it a config value

2023-10-04 Thread Alexander Farber via jetty-users
Good morning, I have an "overengineered" setup with Jetty 10.0.16 and haproxy 2.7.8, where I currently run 6 different Jetty instances to serve the same servlet - I have programmed that WAR servlet using Maven and it supports 6 languages: src/main/resources/strings.properties

Re: [jetty-users] Setting text/javascript encoding to utf8 with Jetty 10.0.16

2023-10-03 Thread Alexander Farber via jetty-users
Thank you, Joakim - your second suggestion has worked well for me: webapps/root.xml http://www.eclipse.org/jetty/configure_9_0.dtd;> / index.html /var/www/html/wordsbyfarber.com true

Re: [jetty-users] Setting text/javascript encoding to utf8 with Jetty 10.0.16

2023-10-03 Thread Alexander Farber via jetty-users
Thank you Joakim for the extensive answer, however - On Tue, Oct 3, 2023 at 2:23 PM Joakim Erdfelt via jetty-users < jetty-users@eclipse.org> wrote: > Per the javascript RFC, the `text/javascript` mime-type has an optional > `charset` parameter. > The behavior is documented at >

[jetty-users] Setting text/javascript encoding to utf8 with Jetty 10.0.16

2023-10-03 Thread Alexander Farber via jetty-users
Hello, I am using Jetty 10.0.16 and when it serves static JavaScript files (I have Consts-en.js, Consts-de.js, Consts-fr.js, ...) the encoding is unfortunately not set to utf8 (like it is for the served json files). Is there a way to enforce that without compiling a custom version of Jetty? I

Should I add a new schema for my application?

2023-04-15 Thread Alexander Farber
Good morning, I am seeking for a general advice if it would be a good idea for a PostgreSQL/PostGIS using application to add a third schema. I am using postgis/postgis Docker image which comes with "public" schema. Then I have loaded OpenStreetMap data into the database and have created

Re: Displaying chat by punished users only to themselves (db fiddle attached)

2022-05-05 Thread Alexander Farber
Thank you, that was it!

Re: Displaying chat by punished users only to themselves (db fiddle attached)

2022-05-05 Thread Alexander Farber
Good evening, I still have a problem with my JOIN expression - when I add more games, then messages from other games are displayed: https://dbfiddle.uk/?rdbms=postgres_14=e2ff211f59090d1eeab879498148f907 CREATE OR REPLACE FUNCTION words_get_chat( in_gid integer,

Re: Displaying chat by punished users only to themselves (db fiddle attached)

2022-05-05 Thread Alexander Farber
Good morning, this is a very insightful comment (among many) by you, David - On Wed, May 4, 2022 at 5:40 PM David G. Johnston wrote: > Well, that is basically why I was going on about the oddity of having > social be a part of the main query. Personally I would write it as > "myself.uid =

Re: Displaying chat by punished users only to themselves (db fiddle attached)

2022-05-04 Thread Alexander Farber
David, thanks but what do you mean by the last comment - On Wed, May 4, 2022 at 7:44 PM David G. Johnston wrote: > Using (SELECT uid FROM myself) provides the same result without the > from/join reference; the usage in the case and the where clause could be > rewritten to use opponent.uid so

Re: Displaying chat by punished users only to themselves (db fiddle attached)

2022-05-04 Thread Alexander Farber
Is that the right way to do it? https://dbfiddle.uk/?rdbms=postgres_14=7bd74243397da61ddc4c216ad919c7cc WITH myself AS ( SELECT uid FROM words_social WHERE social = in_social AND sid = in_sid LIMIT 1 ) SELECT

Re: Displaying chat by punished users only to themselves (db fiddle attached)

2022-05-04 Thread Alexander Farber
I think I am very close with the following CTE, but do not understand how to bring it into the main SELECT query: https://dbfiddle.uk/?rdbms=postgres_14=ee264dc98b44dee75aa4523164a327b3 WITH myself AS ( SELECT uid FROM words_social WHERE social =

Re: Displaying chat by punished users only to themselves (db fiddle attached)

2022-05-04 Thread Alexander Farber
I have tried CROSS JOIN and CASE WHEN (why be greedy, right?): https://dbfiddle.uk/?rdbms=postgres_14=43a33374d15a9330145007702138822c WITH myself AS ( SELECT uid FROM words_social WHERE social = in_social AND sid = in_sid LIMIT

Re: Displaying chat by punished users only to themselves (db fiddle attached)

2022-05-04 Thread Alexander Farber
I try with a CTE but cannot figure the syntax: https://dbfiddle.uk/?rdbms=postgres_14=acd6d06a7ea2efc73a0771530832d77e WITH cte AS ( SELECT uid FROM words_social WHERE social = in_social AND sid = in_sid LIMIT 1 )

Re: Displaying chat by punished users only to themselves (db fiddle attached)

2022-05-04 Thread Alexander Farber
I am probably needing LEFT JOIN LATERAL here (and am completely lost)? Or to switch to CTE as you suggest

Re: Displaying chat by punished users only to themselves (db fiddle attached)

2022-05-04 Thread Alexander Farber
David, I try then the following - On Wed, May 4, 2022 at 5:28 PM David G. Johnston wrote: > You missed quoting the part where I describe the on clauses you need to > distinguish between "them" and "me" > > Me: u.uid in (player...) and (s.uid = u.uid) > Them: u.uid in (player...) and (s.uid <>

Re: Displaying chat by punished users only to themselves (db fiddle attached)

2022-05-04 Thread Alexander Farber
David, I am trying your suggestion: On Wed, May 4, 2022 at 4:27 PM David G. Johnston wrote: > Assuming the base query is capable of returning all related chat messages > for both users (I'd probably place that portion into a CTE) the rows you > want to filter out are those whose c.uid is not my

Re: Displaying chat by punished users only to themselves (db fiddle attached)

2022-05-04 Thread Alexander Farber
My real SQL function has one more param, an "auth" string generated by my game, which complements the social network id "sid". I have just omitted it in my test case. >

Re: Displaying chat by punished users only to themselves (db fiddle attached)

2022-05-04 Thread Alexander Farber
Hi Ron, On Wed, May 4, 2022 at 4:56 PM Ron wrote: > > How do other web sites know to present only "my" data, even though they > don't encode "my" user id in the URL? > > that is the usual pattern with OAuth provided by: Facebook, Google, Amazon, Huawei, etc... After you auth with them in a

Re: Displaying chat by punished users only to themselves (db fiddle attached)

2022-05-04 Thread Alexander Farber
Thank you for replying, David! The "social dynamic" is needed, because I cannot pass real user id (via HTTP) to SQL queries. Instead I pass social network type "social" (like 100 is facebook, 200 is twitter) and the social network id "sid" returned by that network. This way noone can read chats

Displaying chat by punished users only to themselves (db fiddle attached)

2022-05-04 Thread Alexander Farber
Hello, I have developed a complete SQL fiddle for my question: https://dbfiddle.uk/?rdbms=postgres_14=dcf063ba1615b392cc3cfa347a32c97b The context is that I run an online game for two players using a PostgreSQL 14.2 backend. I would like to make my game more friendly by hiding chat messages of

Re: if not exists (SELECT 1... UNION SELECT 1...)

2022-02-16 Thread Alexander Farber
s/grumbling/wondering/

Re: if not exists (SELECT 1... UNION SELECT 1...)

2022-02-16 Thread Alexander Farber
Ah, thank you Ralf! That has explained it (because I was still grumbling...) On Wed, Feb 16, 2022 at 4:44 PM Ralf Schuchardt wrote: > You must rather end the assignment with a semicolon: > > out_text := '___WRONG___'; > RETURN; > > Otherwise what really happens is: > > out_text := '___WRONG___'

Re: if not exists (SELECT 1... UNION SELECT 1...)

2022-02-16 Thread Alexander Farber
Ah, I have to do RETURN '___WRONG___'; and not out_text := '___WRONG___' RETURN;

Re: if not exists (SELECT 1... UNION SELECT 1...)

2022-02-16 Thread Alexander Farber
Hi Laurenz, thanks for your reply, but I think it is wrong - On Wed, Feb 16, 2022 at 1:24 PM Laurenz Albe wrote: > One reason could be index corruption. If one query uses an index and the > other doesn't, > that could lead to different results. > > The other option is of course a trivial

if not exists (SELECT 1... UNION SELECT 1...)

2022-02-16 Thread Alexander Farber
Hello, when I search for a non existent word in the two tables hosted in PostgreSQL 14.1 then I get zero records as expected: words_en=> SELECT 1 FROM words_nouns WHERE word = 'ABCDE' UNION SELECT 1 FROM words_verbs WHERE word = 'ABCDE'; ?column? -- (0 rows) But when I try to use the

Re: [jetty-users] Migrating 9 to 10: what replaces WebSocketServlet and JSON.parse() ?

2021-09-04 Thread Alexander Farber
Apologies for having asked such simple question, I have only some experience with embedded Java... I have added the org.eclipse.jetty jetty-slf4j-impl ${jetty.version} to the pom.xml and now my WAR servlet finally works well with Jetty

Re: [jetty-users] Migrating 9 to 10: what replaces WebSocketServlet and JSON.parse() ?

2021-09-03 Thread Alexander Farber
se use instead instead of Log.getLogger()? There is @deprecated, but no replacement suggestion. Best regards Alex > On Fri, Sep 3, 2021 at 11:09 AM Alexander Farber < > alexander.far...@gmail.com> wrote: > >> Hi, thank you for the helpful hints, now my WAR compiles. >> >> H

Re: [jetty-users] Migrating 9 to 10: what replaces WebSocketServlet and JSON.parse() ?

2021-09-03 Thread Alexander Farber
Hi, thank you for the helpful hints, now my WAR compiles. However when I launch it by: 1) First running once on Win 10 / Java 11: java -jar jetty-home-10.0.6\start.jar jetty.home=jetty-home-10.0.6 jetty.base=jetty.base --add-module=http,servlet,webapp,deploy,resources,websocket 2) Then running

Re: [jetty-users] Migrating 9 to 10: what replaces WebSocketServlet and JSON.parse() ?

2021-09-01 Thread Alexander Farber
Hi Joakim and all - On Tue, Aug 31, 2021 at 6:19 PM Joakim Erdfelt wrote: > With that said, you appear to be using the Jetty implementation/apis. > So, switch to `org.eclipse.jetty.websocket.server.JettyWebSocketServlet` > instead. > > JSON json = new JSON(); > // configure json here > Map

[jetty-users] Migrating 9 to 10: what replaces WebSocketServlet and JSON.parse() ?

2021-08-31 Thread Alexander Farber
Hello, I am trying to migrate a war servlet from 9.4.43.v20210629 to 10.0.6 and have read https://www.eclipse.org/jetty/documentation/jetty-10/programming-guide/index.html#pg-migration-94-to-10 But unfortunately I still have questions - in my servlet I have: public class MyServlet extends

Re: [jetty-users] Using WebAppContext at contextPath / but also serving images, sounds folders

2021-08-10 Thread Alexander Farber
On Tue, Aug 10, 2021 at 12:42 PM wrote: > https://stackoverflow.com/a/68682598/458157 > > > Ah the longest match is used? I was assuming the folder order in the config files and maybe thus I was having problems ___ jetty-users mailing list

Re: [jetty-users] Using WebAppContext at contextPath / but also serving images, sounds folders

2021-08-10 Thread Alexander Farber
ntext path "/"? Or are > you asking can you combine all 3 into a single webapp? > the first option, how to do it please? Sorry if I am asking something basic, I am not experienced with using servlets Best regards Alex > On Mon, 9 Aug 2021 at 21:21, Alexander Farber > wrote:

[jetty-users] Using WebAppContext at contextPath / but also serving images, sounds folders

2021-08-09 Thread Alexander Farber
Hello dear Jetty users and developers, I am using 9.4.43.v20210629 for two things: 1) serve Websockets and GET, POST requests 2) serve files located in "images" and "sounds" folders My config for 1 is currently using contextPath "/ws": http://www.eclipse.org/jetty/configure_9_0.dtd;>

Re: Stored function RETURNS table, but in some cases columns are missing - should I set them to NULL?

2021-03-09 Thread Alexander Farber
Tom, you are so eagle eyed - On Mon, Mar 8, 2021 at 8:53 PM Tom Lane wrote: > Alexander Farber writes: > > However there are cases, when I only have the out_gid value, I do not > want > > to return any other values. > > My question is: do I have to set the other OUT p

Stored function RETURNS table, but in some cases columns are missing - should I set them to NULL?

2021-03-08 Thread Alexander Farber
Good evening, in PostgreSQL 13.2 I have a custom stored function: CREATE OR REPLACE FUNCTION words_join_new_game( in_uid integer, in_bid integer ) RETURNS table ( -- the player to be notified (sometimes there is no such user)

Re: Localizing stored functions by replacing placeholders in their body

2021-03-03 Thread Alexander Farber
Thanks for your input Actually, yes, that is what I have right now a translate() like stored function, with format %s sometimes. But that is "at runtime" and I would like to have a "at compile time"/"deploy once and forget" solution, that is why I have asked about approaches for modifying the

Re: Localizing stored functions by replacing placeholders in their body

2021-03-02 Thread Alexander Farber
I think I will try this approach: \set localized_declaration `sed 's/this/that/' my_func.sql` :localized_declaration Thank you for your input

Re: Localizing stored functions by replacing placeholders in their body

2021-03-02 Thread Alexander Farber
Ah, I understand, that was the wrong EXECUTE, thank you. Another idea: can't I use \set command for my purpose of localizing stored functions? \set my_func_declaration `sed 's/this/that/' my_func.sql` But how to execute the declaration? I can only echo it with select (:'my_func_declaration');

Re: Localizing stored functions by replacing placeholders in their body

2021-03-02 Thread Alexander Farber
Thank you for the \! hint, Pavel, didn't know about that! Is it possible to have a pure SQL solution? (To avoid having to install "sed" on my Win 10 PC) Maybe by using EXECUTE? EXECUTE REGEXP_REPLACE( $localize$ CREATE OR REPLACE FUNCTION my_func() RETURNS text AS $func$ SELECT

Re: Localizing stored functions by replacing placeholders in their body

2021-03-02 Thread Alexander Farber
Or is it possible to call external commands from an sql script, like \i "sed 's/this/that/' some.sql"

Re: Localizing stored functions by replacing placeholders in their body

2021-03-02 Thread Alexander Farber
Yes, good point about the '\$', thank you Tom. The reason I am trying not to use sed, is because I deploy my database by executing a single command: psql words_en < words_en.sql And the file words_en.sql has the contents: \i words_hash.sql \i words_all_letters.sql \i words_get_hint.sql \i

Localizing stored functions by replacing placeholders in their body

2021-03-02 Thread Alexander Farber
Hello, I have an app using PostgreSQL 13.2, in 6 different human languages (each using different database, but same source code). Currently to localize strings return/set by the stored functions I either get localized strings from a table or maintain stored function source code in 6 different

Re: [jetty-users] Is static ConcurrentHashMap a reliable choice for WebSocketServlet?

2021-03-01 Thread Alexander Farber
Hi Lachlan, thank you for your comments! Over the weekend I have rewritten the custom WebSocketListener in my little word game to have a static ConcurrentHashMap of ConcurrentHashMaps with the outer key being user ids and with the inner keys being strings made of

[jetty-users] Is static ConcurrentHashMap a reliable choice for WebSocketServlet?

2021-02-27 Thread Alexander Farber
Good evening, In a custom WebSocketServlet in Jetty 9.4.37.v20210219 I would like to maintain Session objects in a shared data structure. Is a static data structure like public final static Map SESSIONS = new ConcurrentHashMap<>(); a good choice for that? I have a feeling it does not work

Re: [jetty-users] WebSocketListener.onWebSocketClose called numerous times

2021-02-27 Thread Alexander Farber
On the browser side I had automatic reconnects implemented, whenever the Websockets connection was closed. But now I am trying to detect, that a player is already connected in another browser tab and then send a custom close status 4000 from Jetty (so that automatic reconnect is disabled there

Re: [jetty-users] WebSocketListener.onWebSocketClose called numerous times

2021-02-27 Thread Alexander Farber
Hi Joakim, I have changed my custom 9.4.37.v20210219 WebSocketServlet to @Override public void configure(WebSocketServletFactory factory) { factory.getPolicy().setIdleTimeout(5 * 60 * 1000); factory.getPolicy().setMaxBinaryMessageSize(0);

Re: [jetty-users] WebSocketListener.onWebSocketClose called numerous times

2021-02-27 Thread Alexander Farber
Thank you, Joakim! ___ jetty-users mailing list jetty-users@eclipse.org To unsubscribe from this list, visit https://www.eclipse.org/mailman/listinfo/jetty-users

Re: [jetty-users] WebSocketListener.onWebSocketClose called numerous times

2021-02-26 Thread Alexander Farber
Thank you for your suggestion, Joakim, I didn't think about it. So I have added the remote port to the log (and also upgraded to Jetty 9.4.37.v20210219) and yes - the ports are different (please see the log below)... but how can a single user have so many connections (over a dozen) open to my

[jetty-users] WebSocketListener.onWebSocketClose called numerous times

2021-02-26 Thread Alexander Farber
Hello, I am using Jetty 9.4.35.v20201120 for a Websockets word game with 2000 users and while it works pretty stable, every minute or so I observe the following bursts in the log: INFO:words:qtp100555887-757: onWebSocketClose: hostname=77.111.244.123 mUid=0 statusCode=1001 reason=

Re: Deleting takes days, should I add some index?

2021-02-25 Thread Alexander Farber
Thank you for the explanation, David On Thu, Feb 25, 2021 at 9:49 PM David Rowley wrote: > > Since your foreign keys perform a cascade delete on the tables > referencing the tables you're deleting from, any records in those > referencing tables will be deleted too. You must also look at those

Re: Deleting takes days, should I add some index?

2021-02-25 Thread Alexander Farber
Thank you, Pavel! I didn't even think about trying to "explain analyze" deletion of just 1 record - On Thu, Feb 25, 2021 at 10:04 PM Pavel Stehule wrote: > čt 25. 2. 2021 v 19:39 odesílatel Alexander Farber < >> alexander.far...@gmail.com> napsal: >> >>&

Re: Deleting takes days, should I add some index?

2021-02-25 Thread Alexander Farber
Pavel, thank you for asking! I have put the anonymized dump of my database at: http://wordsbyfarber.com/words_dev.sql.gz (beware, it is a 1.3 GB download) The question is why does the command take days (when I tried last time): delete from words_games where created < now() - interval '12

Re: Deleting takes days, should I add some index?

2021-02-25 Thread Alexander Farber
Hi Pavel, trying to follow your advice "You should check so all foreign keys have an index" I look at the table where I want to delete older records: # \d words_games Table "public.words_games" Column | Type | Collation | Nullable |

Re: Deleting takes days, should I add some index?

2021-02-25 Thread Alexander Farber
Hello, revisiting an older mail on the too long deletion times (in PostgreSQL 13.2)... I have followed the advices here, thank you - On Fri, Nov 27, 2020 at 4:15 PM Guillaume Lelarge wrote: > Le ven. 27 nov. 2020 à 16:05, Alvaro Herrera a > écrit : > >> On 2020-Nov-27, Alexand

Re: JSONB_AGG: aggregate function calls cannot be nested

2021-02-22 Thread Alexander Farber
Ahh, thank you all - select row_to_json (x) FROM( SELECT jsonb_agg(day) AS day, jsonb_agg(completed) AS completed, jsonb_agg(expired) AS expired from ( SELECT TO_CHAR(finished, '-MM-DD') AS day, count(*) filter (where reason in ('regular', 'resigned'))

Re: JSONB_AGG: aggregate function calls cannot be nested

2021-02-22 Thread Alexander Farber
Thank you Thomas, this results in select day AS day, jsonb_agg(completed) AS completed, jsonb_agg(expired) AS expired from ( SELECT TO_CHAR(finished, '-MM-DD') AS day, count(*) filter (where reason in ('regular', 'resigned')) AS completed,

Re: JSONB_AGG: aggregate function calls cannot be nested

2021-02-20 Thread Alexander Farber
Ah, thank you... JSON support in PostgreSQL is cool and seems to be extended with each release. But standard tasks of returning a JSON map of lists or JSON list of list seem to be difficult to use. Greetings Alex

Re: JSONB_AGG: aggregate function calls cannot be nested

2021-02-20 Thread Alexander Farber
Then I have to split the query in 3 similar ones (with same condition)? I try: SELECT JSONB_AGG(TO_CHAR(finished, '-MM-DD')) AS day FROM words_games WHERE finished > CURRENT_TIMESTAMP - INTERVAL '2 week' GROUP BY day ORDER BY day; ERROR:

JSONB_AGG: aggregate function calls cannot be nested

2021-02-20 Thread Alexander Farber
Good evening, I have the following query in 13.2: # SELECT TO_CHAR(finished, '-MM-DD') AS day, SUM(CASE WHEN reason='regular' or reason='resigned' THEN 1 ELSE 0 END)::int AS completed, SUM(CASE WHEN reason='expired' THEN 1 ELSE 0 END)::int AS

Re: Please help to improve SELECT JOIN performance, EXPLAIN ANALYZE attached

2021-02-20 Thread Alexander Farber
: logical replication launcher On Sat, Feb 20, 2021 at 4:15 PM Alexander Farber wrote: > Thank you all, I will try at first > > shared_buffers = 16GBand > > index on words_scores(uid, length(word) desc) > > >

Re: Please help to improve SELECT JOIN performance, EXPLAIN ANALYZE attached

2021-02-20 Thread Alexander Farber
Thank you all, I will try at first shared_buffers = 16GBand index on words_scores(uid, length(word) desc)

Re: Please help to improve SELECT JOIN performance, EXPLAIN ANALYZE attached

2021-02-20 Thread Alexander Farber
Yes, Michael, that I have noticed too, but should have written more in my original mail. The query when I try it does run in 15ms, but evening logs show the query (I think only small percentage of it) running 1-3s. At the same time my CentOS 8 server with 64 GB RAM is never loaded, the load

Please help to improve SELECT JOIN performance, EXPLAIN ANALYZE attached

2021-02-19 Thread Alexander Farber
Good evening, I have a word game which uses PostgreSQL 13.2 and 80% of the code is written as stored functions in PL/PgSQL or SQL. Recently I have purchased some traffic and the number of daily games increased from 100 to 700. In the PostgreSQL log I have noticed that the duration for 2

Re: How to return a jsonb list of lists (with integers)

2021-02-17 Thread Alexander Farber
I have tried switching to SELECT INTO, but IF FOUND is still always true, which gives me [ null, null, null ] for some users: SELECT JSONB_BUILD_ARRAY( SUM(CASE WHEN (player1 = in_viewer AND state1 = 'won') OR (player2 = in_viewer AND state2 = 'won') THEN

Re: How to return a jsonb list of lists (with integers)

2021-02-17 Thread Alexander Farber
Hi Pavel, why would SELECT INTO be better here? Thanks Alex

Re: How to return a jsonb list of lists (with integers)

2021-02-17 Thread Alexander Farber
I have ended up with the following (to avoid returning [null, null, null] for players who never played with each other): _versus := JSONB_BUILD_ARRAY( SUM(CASE WHEN (player1 = in_uid AND state1 = 'won') OR (player2 = in_uid AND state2 = 'won') THEN 1 ELSE

Re: How to return a jsonb list of lists (with integers)

2021-02-17 Thread Alexander Farber
I have tried the following, but IF FOUND is always false for some reason: _versus := JSONB_BUILD_ARRAY( SUM(CASE WHEN (player1 = in_uid AND state1 = 'won') OR (player2 = in_uid AND state2 = 'won') THEN 1 ELSE 0 END)::integer,

Re: How to return a jsonb list of lists (with integers)

2021-02-17 Thread Alexander Farber
Hello, thank you for the helpful replies. I have decided to go with PL/PgSQL for now and also switched from JSONB list of lists to map of lists. And the custom stored function below works mostly well, except for a special case - CREATE OR REPLACE FUNCTION words_stat_charts(

Re: How to return a jsonb list of lists (with integers)

2021-02-16 Thread Alexander Farber
Thank you, David, with json_build_array() it works for a single query - SELECT JSONB_BUILD_ARRAY( SUM(CASE WHEN (player1 = in_uid AND state1 = 'won') OR (player2 = in_uid AND state2 = 'won') THEN 1 ELSE 0 END)::integer, SUM(CASE WHEN (player1

Re: How to return a jsonb list of lists (with integers)

2021-02-16 Thread Alexander Farber
On Tue, Feb 16, 2021 at 7:52 PM Michael Lewis wrote: > Aggregate functions work on a single column to summarize many rows into > fewer rows. You seem to be wanting to combine multiple columns which would > be done by concatenation or array[column1,column2] or something like that. > Ah right,

How to return a jsonb list of lists (with integers)

2021-02-16 Thread Alexander Farber
Good evening, In 13.2 I have 3 SQL queries, which work well and return integer values. The values I feed to Google Charts (and currently I switch to Chart.js). Currently I use the queries by calling 3 different custom stored functions by my Java servlet. I would like to convert the functions

Re: Select a column and then apply JSONB_ARRAY_ELEMENTS to it

2021-01-09 Thread Alexander Farber
On Sat, Jan 9, 2021 at 3:49 PM David G. Johnston wrote: > Subqueries must be enclosed in parentheses. The parentheses that are part > of the function call do not count. > > Ah! Thank you David, this has worked now - CREATE OR REPLACE FUNCTION words_solve_puzzle( in_mid

Select a column and then apply JSONB_ARRAY_ELEMENTS to it

2021-01-09 Thread Alexander Farber
Hello, for a word puzzle using PostgreSQL 13.1: https://wortefarbers.de/ws/puzzle2?mid=138=c6f469786df7e8d44461381b62b2ce7d I am trying to improve a stored function - CREATE OR REPLACE FUNCTION words_solve_puzzle( in_mid bigint, in_uid int,

Re: SELECT but only if not present in another table

2020-12-06 Thread Alexander Farber
Thank you, Steve - On Sun, Dec 6, 2020 at 6:50 PM Steve Baldwin wrote: > Can't you just use table aliases? So, the outer word_moves would become > 'word_moves as wm', word_puzzles would become 'word_puzzles as wp', and the > where clause 'WHERE wp.mid = wm.mid' ? > table aliases have worked

SELECT but only if not present in another table

2020-12-06 Thread Alexander Farber
Good evening, in PostgreSQL 13.1 I save player moves in the table: # \d words_moves Table "public.words_moves" Column | Type | Collation | Nullable | Default

Re: Using a boolean column with IF / THEN

2020-12-06 Thread Alexander Farber
On Sat, Dec 5, 2020 at 9:00 PM David G. Johnston wrote: > Maybe not “simpler” but for all those checks you could write a single > query that pulls out all the data at once into a record variable and test > against the columns pf that instead of executing multiple queries. > Thank you!

Using a boolean column with IF / THEN

2020-12-05 Thread Alexander Farber
Good evening, hopefully my question is not too stupid, but - in a 13.1 database I have a words_users table with a boolean column: -- the user is not allowed to chat or change the motto muted boolean NOT NULL DEFAULT false, Currently I check the value as follows, but I

[CentOS] CentOS 8.2: error running non-shared postrotate script for /var/log/mysql/mysqld.log

2020-12-05 Thread Alexander Farber
Hello fellow CentOS users! I have installed CentOS 8.2.2004 with the following packages: mysql-common-8.0.21-1.module_el8.2.0+493+63b41e36.x86_64 mysql-8.0.21-1.module_el8.2.0+493+63b41e36.x86_64 mysql-errmsg-8.0.21-1.module_el8.2.0+493+63b41e36.x86_64

Deleting takes days, should I add some index?

2020-11-27 Thread Alexander Farber
Hello, I am using PostgreSQL 10.15 on CentOS 7 with 64 GB RAM, Intel i7 6700 and I have the following 2 tables there: words_ru=> \d words_games Table "public.words_games" Column | Type | Collation | Nullable | Default

[jetty-users] Is it necessary to close session in WebSocketConnectionListener.onWebSocketError ?

2020-10-08 Thread Alexander Farber
Good evening, the doc https://www.eclipse.org/jetty/javadoc/current/org/eclipse/jetty/websocket/api/WebSocketConnectionListener.html says, that when onWebSocketClose is called, then the session is closed automatically. But what about the other callback - the onWebSocketError? As a developer,

Re: [CentOS] certbot stopped working on CentOS 7: pyOpenSSL module missing required functionality

2020-10-05 Thread Alexander Farber
Yes, I had a typo in the mail, but not in the cronjob Still wondering how to get certbot-1.7.0-1.el7.noarch working on CentOS 7 again. ___ CentOS mailing list CentOS@centos.org https://lists.centos.org/mailman/listinfo/centos

[CentOS] certbot stopped working on CentOS 7: pyOpenSSL module missing required functionality

2020-10-05 Thread Alexander Farber
Hello fellow CentOS users, I had this cronjob working for many moons on CentOS 7.8.2003: #minute hourmdaymonth wdaycommand 6 6 * * 1 certbot renew --post-hook "cat /etc/letsencrypt/live/raspasy.de/fullchain.pem /etc/letsencrypt/live/

Re: How to use avro-python3 on Windows 10 to parse files?

2020-09-29 Thread Alexander Farber
Hello and thanks for your replies! I have to apologize - the avro file I was using did not contain any useful data. The reason for my confusion is that a colleague (in a screen sharing session) was using a different file with the same name while testing for me. Now I have tried both avro and

Re: How to use avro-python3 on Windows 10 to parse files?

2020-09-28 Thread Alexander Farber
rary instead. > > If the avro library for python doesn't work, please let me know. > Yes, it does not work for me with python 3.8.0 - please see below On Mon, Sep 28, 2020 at 12:43 Alexander Farber > wrote: > >> With python 3.8.5 and avro 1.10.0 installed via pip I have tried running >

How to use avro-python3 on Windows 10 to parse files?

2020-09-28 Thread Alexander Farber
Hello and good evening! With python 3.8.5 and avro 1.10.0 installed via pip I have tried running the following script: import os, avro from avro.datafile import DataFileReader, DataFileWriter from avro.io import DatumReader, DatumWriter reader = DataFileReader(open("48.avro", "rb"),

Re: How to call JSONB_INSERT with integer as the new to-be-inserted value?

2020-09-13 Thread Alexander Farber
Thank you!

How to call JSONB_INSERT with integer as the new to-be-inserted value?

2020-09-12 Thread Alexander Farber
Good evening, I am trying to take a JSONB object (comes from an HTTP cookie set by my app) and add a property "uid" to it, which should hold an integer: CREATE OR REPLACE FUNCTION words_get_user( in_users jsonb, OUT out_user jsonb ) RETURNS jsonb AS $func$

[jetty-users] Primary script unknown with 9.4.30.v20200611

2020-07-13 Thread Alexander Farber
Good evening, I run the following on CentOS 7: /usr/bin/java -Djdbc.drivers=org.postgresql.Driver -jar /usr/share/java/jetty-distribution-9.4.30.v20200611/start.jar jetty.home=/usr/share/java/jetty-distribution-9.4.30.v20200611 jetty.base=/var/www/jetty-base-raspasy jetty.http.host=127.0.0.1

[jetty-users] Setting Content-Type: text/html; charset=utf-8 for all html files

2020-04-11 Thread Alexander Farber
Good evening, on my CentOS 7 server I run Jetty by: # /usr/bin/java -Djdbc.drivers=org.postgresql.Driver -jar /usr/share/java/jetty-distribution-9.4.27.v20200227/start.jar jetty.home=/usr/share/java/jetty-distribution-9.4.27.v20200227 jetty.base=/var/www/jetty-base-ru jetty.http.host=127.0.0.1

Re: [jetty-users] Configuring Jetty for FastCGI - and pass env. vars to PHP scripts

2020-04-02 Thread Alexander Farber
Yes, Simone, I will try to test it, but that will take me few days :-) On Wed, Apr 1, 2020 at 3:03 PM Simone Bordet wrote: > > Implementing #4375 was trivial and I tested it. > Would you be able to test it on your configuration by building Jetty > yourself? > >

Re: [jetty-users] Configuring Jetty for FastCGI - and pass env. vars to PHP scripts

2020-04-01 Thread Alexander Farber
Hello Simone - On Tue, Mar 31, 2020 at 11:51 PM Simone Bordet wrote: > What you can do right now is to override this: > > class FarberFCGIServlet extends FastCGIProxyServlet { > protected void customizeFastCGIHeaders(Request proxyRequest, > HttpFields fastCGIHeaders) { >

Re: [jetty-users] How to include another web page in a page served by Jetty?

2020-04-01 Thread Alexander Farber
On Wed, Apr 1, 2020 at 8:32 AM Greg Wilkins wrote: > If client side is an option, there are always simple iframes as an option > > Right! Thank you for the advice ___ jetty-users mailing list jetty-users@eclipse.org To unsubscribe from this list, visit

[jetty-users] Configuring Jetty for FastCGI - and pass env. vars to PHP scripts

2020-03-31 Thread Alexander Farber
Hello and good evening, on a CentOS Linux server I run 3 instances of Jetty on 3 different IP addresses, serving a servlet and Wordpress in 3 different languages: English, German, Russian. The Wordpress installations are configured exactly as described at

Re: [jetty-users] How to include another web page in a page served by Jetty?

2020-03-31 Thread Alexander Farber
Thank you for your response Greg - On Mon, Mar 30, 2020 at 10:09 AM Greg Wilkins wrote: > > Jetty supports the Servlet API feature of RequestDispatcher.include, so > one URI/resource can be included in another page. The trick is how do you > trigger than in the content that you are

  1   2   3   4   5   6   7   8   9   10   >