Re: Please recommend postgresql.conf improvements for osm2pgsql loading Europe
Thank you for the insights
Re: Please recommend postgresql.conf improvements for osm2pgsql loading Europe
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
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 28 GByte large europe-latest.osm.pbf > > Not specific conf file improvements, but for an initial data load > have you done things like turning off fsync(), deferring index > creating until after the data load finishes, and that kind of thing? > I will try the following commands in my Dockerfile then and later report back on any improvements: RUN set -eux && \ pg_ctl init && \ echo "shared_buffers = 1GB">> $PGDATA/postgresql.conf && \ echo "work_mem = 50MB" >> $PGDATA/postgresql.conf && \ echo "maintenance_work_mem = 10GB" >> $PGDATA/postgresql.conf && \ echo "autovacuum_work_mem = 2GB" >> $PGDATA/postgresql.conf && \ echo "wal_level = minimal" >> $PGDATA/postgresql.conf && \ echo "checkpoint_timeout = 60min" >> $PGDATA/postgresql.conf && \ echo "max_wal_size = 10GB" >> $PGDATA/postgresql.conf && \ echo "checkpoint_completion_target = 0.9" >> $PGDATA/postgresql.conf && \ echo "max_wal_senders = 0" >> $PGDATA/postgresql.conf && \ echo "random_page_cost = 1.0" >> $PGDATA/postgresql.conf && \ echo "password_encryption = scram-sha-256" >> $PGDATA/postgresql.conf && \ echo "fsync = off">> $PGDATA/postgresql.conf && \ pg_ctl start && \ createuser --username=postgres $PGUSER && \ createdb --username=postgres --encoding=UTF8 --owner=$PGUSER $PGDATABASE && \ psql --username=postgres $PGDATABASE --command="ALTER USER $PGUSER WITH PASSWORD '$PGPASSWORD';" && \ psql --username=postgres $PGDATABASE --command='CREATE EXTENSION IF NOT EXISTS postgis;' && \ psql --username=postgres $PGDATABASE --command='CREATE EXTENSION IF NOT EXISTS hstore;' && \ osm2pgsql --username=$PGUSER --database=$PGDATABASE --create --cache=6 --hstore --latlong /data/map.osm.pbf && \ rm -f /data/map.osm.pbf && \ pg_ctl stop && \ echo "fsync = on">> $PGDATA/postgresql.conf && \ echo '# TYPE DATABASE USER ADDRESS METHOD'> $PGDATA/pg_hba.conf && \ echo "local all postgres peer" >> $PGDATA/pg_hba.conf && \ echo "local $PGDATABASE $PGUSER scram-sha-256" >> $PGDATA/pg_hba.conf && \ echo "host $PGDATABASE $PGUSER 0.0.0.0/0 scram-sha-256" >> $PGDATA/pg_hba.conf The later fsync = on will override the former, right? Best regards Alex
Please recommend postgresql.conf improvements for osm2pgsql loading Europe
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">> $PGDATA/postgresql.conf && \ echo "work_mem = 50MB" >> $PGDATA/postgresql.conf && \ echo "maintenance_work_mem = 10GB" >> $PGDATA/postgresql.conf && \ echo "autovacuum_work_mem = 2GB" >> $PGDATA/postgresql.conf && \ echo "wal_level = minimal" >> $PGDATA/postgresql.conf && \ echo "checkpoint_timeout = 60min" >> $PGDATA/postgresql.conf && \ echo "max_wal_size = 10GB" >> $PGDATA/postgresql.conf && \ echo "checkpoint_completion_target = 0.9" >> $PGDATA/postgresql.conf && \ echo "max_wal_senders = 0" >> $PGDATA/postgresql.conf && \ echo "random_page_cost = 1.0" >> $PGDATA/postgresql.conf && \ echo "password_encryption = scram-sha-256" >> $PGDATA/postgresql.conf && \ And after/during osm2pgsql is loading the data into the database, the following messages are displayed by PostgreSQL: 2024-03-29T14:50:19.6909027Z 2024-03-29 14:50:19Processed 3355283586 nodes in 432s (7m 12s) - 7767k/s 2024-03-29T14:50:19.6909478Z 2024-03-29 14:50:19Processed 406646166 ways in 2720s (45m 20s) - 150k/s 2024-03-29T14:50:19.6909785Z 2024-03-29 14:50:19Processed 7265827 relations in 672s (11m 12s) - 11k/s 2024-03-29T14:50:20.0292733Z [0m [91m2024-03-29 14:50:20 node cache: stored: 3355283586(100.00%), storage efficiency: 56.37% (dense blocks: 268264, sparse nodes: 1877553733), hit rate: 100.00% 2024-03-29T14:50:20.0293430Z 2024-03-29 14:50:20 Clustering table 'planet_osm_line' by geometry... 2024-03-29T14:50:20.0293787Z 2024-03-29 14:50:20 Clustering table 'planet_osm_point' by geometry... 2024-03-29T14:50:20.1056438Z [0m [91m2024-03-29 14:50:20 Clustering table 'planet_osm_polygon' by geometry... 2024-03-29T14:50:20.1056819Z 2024-03-29 14:50:20 Clustering table 'planet_osm_roads' by geometry... 2024-03-29T15:15:27.3834184Z [0m [91m2024-03-29 15:15:24 Creating geometry index on table 'planet_osm_roads'... 2024-03-29T15:15:56.7850293Z [0m2024-03-29 15:15:56.784 UTC [344] LOG: skipping vacuum of "planet_osm_roads" --- lock not available 2024-03-29T15:16:56.7864755Z 2024-03-29 15:16:56.785 UTC [347] LOG: skipping vacuum of "planet_osm_roads" --- lock not available 2024-03-29T15:17:56.7891503Z 2024-03-29 15:17:56.788 UTC [350] LOG: skipping vacuum of "planet_osm_roads" --- lock not available 2024-03-29T15:18:16.5830638Z [91m2024-03-29 15:18:16 Analyzing table 'planet_osm_roads'... 2024-03-29T15:18:18.1485666Z [0m [91m2024-03-29 15:18:18 All postprocessing on table 'planet_osm_roads' done in 1678s (27m 58s). 2024-03-29T15:22:17.1463669Z [0m [91m2024-03-29 15:22:17 Creating geometry index on table 'planet_osm_point'... 2024-03-29T15:22:56.8860848Z [0m2024-03-29 15:22:56.885 UTC [365] LOG: skipping vacuum of "planet_osm_point" --- lock not available 2024-03-29T15:23:56.8890669Z 2024-03-29 15:23:56.888 UTC [368] LOG: skipping vacuum of "planet_osm_point" --- lock not available 2024-03-29T15:24:56.9521355Z 2024-03-29 15:24:56.951 UTC [371] LOG: skipping vacuum of "planet_osm_point" --- lock not available 2024-03-29T15:25:56.9166127Z 2024-03-29 15:25:56.915 UTC [374] LOG: skipping vacuum of "planet_osm_point" --- lock not available 2024-03-29T15:26:57.4606148Z 2024-03-29 15:26:57.460 UTC [377] LOG: skipping vacuum of "planet_osm_point" --- lock not available 2024-03-29T15:27:56.9555832Z 2024-03-29 15:27:56.955 UTC [380] LOG: skipping vacuum of "planet_osm_point" --- lock not available 2024-03-29T15:28:56.9718398Z 2024-03-29 15:28:56.971 UTC [383] LOG: skipping vacuum of "planet_osm_point" --- lock not available 2024-03-29T15:29:56.9912768Z 2024-03-29 15:29:56.990 UTC [386] LOG: skipping vacuum of "planet_osm_point" --- lock not available 2024-03-29T15:30:57.1482580Z 2024-03-29 15:30:57.147 UTC [389] LOG: skipping vacuum of "planet_osm_point" --- lock not available 2024-03-29T15:31:01.2674499Z [91m2024-03-29 15:31:01 Analyzing table 'planet_osm_point'... 2024-03-29T15:31:02.3310950Z [0m [91m2024-03-29 15:31:02 All postprocessing on table 'planet_osm_point' done in 2442s (40m 42s). 2024-03-29T15:41:13.1544481Z [0m [91m2024-03-29 15:41:13 Creating geometry index on table 'planet_osm_line'... 2024-03-29T15:41:57.2434689Z [0m2024-03-29 15:41:57.242 UTC [422] LOG: skipping vacuum of "planet_osm_line" --- lock not available 2024-03-29T15:42:57.2519273Z 2024-03-29 15:42:57.251 UTC [425] LOG: skipping vacuum of "planet_osm_line" --- lock not available 2024-03-29T15:43:57.2626514Z 2024-03-29 15:43:57.262 UTC [428] LOG: skipping vacuum of "planet_osm_line" --- lock not available 2024-03-29T15:44:57.2531401Z 2024-03-29 15:44:57.252 UTC [431] LOG: skipping vacuum of "planet_osm_line" --- lock not available
Re: [jetty-users] Run multiple instances of the same WAR servlet and pass it a config value
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 https://eclipse.dev/jetty/documentation/jetty-10/operations-guide/index.html#og-xml - how could I retrieve the value in the servlet? Best regards Alex ___ 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] Run multiple instances of the same WAR servlet and pass it a config value
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@eclipse.org
To unsubscribe from this list, visit
https://www.eclipse.org/mailman/listinfo/jetty-users
[jetty-users] Run multiple instances of the same WAR servlet and pass it a config value
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
src/main/resources/strings_de.properties
src/main/resources/strings_fr.properties
src/main/resources/strings_nl.properties
src/main/resources/strings_pl.properties
src/main/resources/strings_ru.properties
and then in the configure method I fetch the language value from the
"COUNTRY" env var:
@Override
public void configure(JettyWebSocketServletFactory factory) {
mLanguage = System.getenv("COUNTRY");
mBundle = ResourceBundle.getBundle("strings",
LOCALES.get(mLanguage));
factory.setIdleTimeout(Duration.ofSeconds(IDLE_TIMEOUT_SECONDS));
factory.setMaxBinaryMessageSize(0);
factory.setMaxTextMessageSize(64 * 1024);
factory.setCreator(new WordsCreator(this,
mBundle.getString(STR_DATABASE_URL)));
}
Then I have the following 6 scripts on my Rocky Linux 8.8 to start the
Jetty instances
/etc/systemd/system/jetty-de.service
/etc/systemd/system/jetty-en.service
/etc/systemd/system/jetty-fr.service
/etc/systemd/system/jetty-nl.service
/etc/systemd/system/jetty-pl.service
/etc/systemd/system/jetty-ru.service
And the only differences the scripts have is the port and the "COUNTRY" env
var:
[Unit]
Description=Jetty
After=network-online.target
[Service]
Environment=COUNTRY=de
Type=simple
User=jetty
Group=jetty
ExecStart=/usr/bin/java -Djdbc.drivers=org.postgresql.Driver -jar
/usr/share/java/jetty-home-10.0.16/start.jar
jetty.home=/usr/share/java/jetty-home-10.0.16
jetty.base=/var/www/jetty-base-de jetty.http.host=127.0.0.1
jetty.http.port=8081
SuccessExitStatus=143
Restart=always
RestartSec=180
PrivateTmp=true
[Install]
WantedBy=multi-user.target
In the /etc/haproxy/haproxy.cfg I look at the request path and forward the
request to one of the ports:
backend jetty_de
server domain 127.0.0.1:8081 send-proxy
backend jetty_en
server domain 127.0.0.1:8082 send-proxy
backend jetty_fr
server domain 127.0.0.1:8083 send-proxy
backend jetty_nl
server domain 127.0.0.1:8084 send-proxy
backend jetty_pl
server domain 127.0.0.1:8085 send-proxy
backend jetty_ru
server domain 127.0.0.1:8080 send-proxy
frontend wordsbyfarber_com
bind 95.216.113.90:80
bind 95.216.113.90:443 ssl crt
/etc/pki/tls/certs/wordsbyfarber.com.pem no-sslv3
redirect scheme https if !{ ssl_fc }
use_backend jetty_de if { path_beg /de }
use_backend jetty_en if { path_beg /en }
use_backend jetty_fr if { path_beg /fr }
use_backend jetty_nl if { path_beg /nl }
use_backend jetty_pl if { path_beg /pl }
use_backend jetty_ru if { path_beg /ru }
default_backend jetty_en
Finaly my config XML files look like this to "bind" the servlet to a
certain context path:
# cat /var/www/jetty-base-de/webapps/de.xml
http://www.eclipse.org/jetty/configure_9_0.dtd;>
/de
/var/www/words-5.0.war
Thank you for reading my mail, it is a bit longer, because I am trying to
provide enough details.
My question is, if it is possible to handle my task with a single Jetty
instance?
Could I set the env var in the /var/www/jetty-base-de/webapps/de.xml file?
Or maybe alternatively access the value of "contextPath" from the
configure() method?
Thank you for any suggestions
Alex
___
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] Setting text/javascript encoding to utf8 with Jetty 10.0.16
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 js text/javascript;charset=utf-8 Now I get the following response headers and the file is displayed with correct international characters in the Microsoft Edge browser: HTTP/1.1 200 OK last-modified: Tue, 03 Oct 2023 09:52:40 GMT content-type: text/javascript;charset=utf-8 accept-ranges: bytes vary: Accept-Encoding content-encoding: gzip server: Jetty(10.0.16) connection: close Best regards Alex ___ 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] Setting text/javascript encoding to utf8 with Jetty 10.0.16
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
> https://datatracker.ietf.org/doc/html/rfc9239#name-charset-parameter
>
> Essentially, if the charset is unspecified, then the encoding is UTF-8.
>
> > the encoding is unfortunately not set to utf8 (like it is for the served
> json files).
>
> For JSON, the charset parameter is not used.
> Per spec, JSON is always UTF-8.
> See: https://www.rfc-editor.org/rfc/rfc8259#section-8.1
>
> In Jetty, the json encoding is specified as an assumed UTF-8.
> See:
> https://github.com/eclipse/jetty.project/blob/jetty-10.0.16/jetty-http/src/main/resources/org/eclipse/jetty/http/encoding.properties
> This means the `charset` parameter is not produced when generating the
> `Content-Type` header, and is ignored when parsing the `Content-Type`
> header.
>
> > Is there a way to enforce that without compiling a custom version of
> Jetty?
>
> You can customize the in-place `MimeTypes` for a context.
>
> servletContextHandler.getMimeTypes().addMimeMapping("txt",
> "text/javascript;charset=UTF-8");
> or
> webappContext.getMimeTypes().addMimeMapping("txt",
> "text/javascript;charset=UTF-8");
>
> or, If you have a WEB-INF/web.xml in your webapp, you can add a
> `` entry.
>
>
> js
> text/javascript;charset=UTF-8
>
>
I have problems that my UTF8 file is displayed wrongly in the browser -
https://wordsbyfarber.com/Consts-ru.js
The browser displays pairs of junk characters instead of cyrillic letters
and from my (limited) experience having ";charset=utf8" in the header would
help it.
So I have followed your suggestion and have extended the
src/main/webapp/WEB-INF/web.xml with:
http://xmlns.jcp.org/xml/ns/javaee;
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance;
xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee
http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd;>
WordsServlet
de.afarber.WordsServlet
WordsServlet
/
js
text/javascript;charset=UTF-8
Unfortunately the URL still returns the headers without "UTF-8" in there:
HTTP/1.1 200 OK
last-modified: Tue, 03 Oct 2023 09:52:40 GMT
content-type: text/javascript
accept-ranges: bytes
vary: Accept-Encoding
content-encoding: gzip
server: Jetty(10.0.16)
connection: close
Best regards
Alex
___
jetty-users mailing list
jetty-users@eclipse.org
To unsubscribe from this list, visit
https://www.eclipse.org/mailman/listinfo/jetty-users
[jetty-users] Setting text/javascript encoding to utf8 with Jetty 10.0.16
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 have searched on the internet and also here: # find /usr/share/java/jetty-home-10.0.16 -type f -iname \*.prop\* /usr/share/java/jetty-home-10.0.16/modules/deprecated.properties /usr/share/java/jetty-home-10.0.16/modules/demo.d/demo-realm.properties /usr/share/java/jetty-home-10.0.16/modules/demo.d/demo-login.properties /usr/share/java/jetty-home-10.0.16/modules/sessions/infinispan/remote/resources/hotrod-client.properties /usr/share/java/jetty-home-10.0.16/modules/jolokia/jolokia-realm.properties /usr/share/java/jetty-home-10.0.16/modules/logging/jul/resources/java-util-logging-bridge.properties /usr/share/java/jetty-home-10.0.16/modules/logging/jul/resources/java-util-logging.properties /usr/share/java/jetty-home-10.0.16/modules/logging/jetty/resources/jetty-logging.properties /usr/share/java/jetty-home-10.0.16/etc/jdbcRealm.properties Thank you for any hints Alex ___ jetty-users mailing list jetty-users@eclipse.org To unsubscribe from this list, visit https://www.eclipse.org/mailman/listinfo/jetty-users
Should I add a new schema for my application?
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 "osm_schema" for that: # psql -p 5432 -U postgres CREATE DATABASE osm_database TEMPLATE=template_postgis; CREATE USER osm_username WITH ENCRYPTED PASSWORD 'osm_password'; GRANT ALL PRIVILEGES ON DATABASE osm_database TO osm_username; -- TODO read only # psql -p 5432 -U osm_username osm_database CREATE SCHEMA osm_schema AUTHORIZATION osm_username; ALTER ROLE osm_username SET search_path TO osm_schema, public; After loading OSM data with the osm2pgsql tool I have 3 tables in there: # psql -p 5432 -U osm_username osm_database psql (15.2) osm_database=> \dt List of relations Schema |Name| Type |Owner ++---+-- osm_schema | planet_osm_line| table | osm_username osm_schema | planet_osm_point | table | osm_username osm_schema | planet_osm_polygon | table | osm_username osm_schema | planet_osm_roads | table | osm_username public | spatial_ref_sys| table | postgres (5 rows) And now I need one more table, for the new mapping feature in my application, which would reference the osm_id column in the planet_osm_roads. I have not used database schemas at all until yet, I am looking for an advice if I should add a third schema here, just for my application? Would it be a good long term idea and what advantages/disadvantages would that bring with it? Thank you for any feedback and also I have to note, that I am really amazed by all these software products - PostgreSQL, PostGIS and also the OpenStreetMap - how smooth the installation has worked for me - just an hour spent and I have the whole planet at my fingertips - this is not coming for free, there are probably decades of effort by many people in the products. Best regards Alex
Re: Displaying chat by punished users only to themselves (db fiddle attached)
Thank you, that was it!
Re: Displaying chat by punished users only to themselves (db fiddle attached)
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, in_uid integer ) RETURNS TABLE ( out_mine integer, out_game text, out_msg text ) AS $func$ SELECT CASE WHEN c.uid = in_uid THEN 1 ELSE 0 END, 'game #' || c.gid, c.msg FROMwords_chat c JOINwords_games g USING (gid) JOINwords_users opponent ON (opponent.uid IN (g.player1, g.player2) AND opponent.uid <> in_uid) WHERE c.gid = in_gid -- always show myself my own chat messages AND c.uid = in_uid -- otherwise only show messages by not muted opponents OR NOT opponent.muted ORDER BY c.created ASC; $func$ LANGUAGE sql; I have tried making the JOIN words_users opponent even more restrictive with: JOINwords_users opponent ON (opponent.uid IN (g.player1, g.player2) AND in_uid IN (g.player1, g.player2) AND opponent.uid <> in_uid) but still messages from the game #20 are displayed, even though I pass in_gid = 10 Best regards Alex
Re: Displaying chat by punished users only to themselves (db fiddle attached)
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 = in_uid", but you don't have an in_uid to reference. Decide > how you want to do something equivalent. > > so I will rewrite the stored functions in my game to be like that, to separate auth from functionality - https://dbfiddle.uk/?rdbms=postgres_14=dbdf1a6b82f7232be45e23b8139a8f0e CREATE OR REPLACE FUNCTION words_get_uid( in_social integer, in_sidtext ) RETURNS integer AS $func$ SELECT uid FROM words_social WHERE social = in_social AND sid = in_sid; $func$ LANGUAGE sql IMMUTABLE; CREATE OR REPLACE FUNCTION words_get_chat( in_gid integer, in_uid integer ) RETURNS TABLE ( out_mine integer, out_msg text ) AS $func$ SELECT CASE WHEN c.uid = in_uid THEN 1 ELSE 0 END, c.msg FROMwords_chat c JOINwords_games g USING (gid) JOINwords_users opponent ON (opponent.uid IN (g.player1, g.player2) AND opponent.uid <> in_uid) WHERE c.gid = in_gid -- always show myself my own chat messages AND c.uid = in_uid -- otherwise only show messages by not muted opponents OR NOT opponent.muted ORDER BY c.created ASC; $func$ LANGUAGE sql; SELECT words_get_chat(10, words_get_uid(100, 'abc')) AS nice_user; SELECT words_get_chat(10, words_get_uid(200, 'def')) AS muted_user; Thanks Alex
Re: Displaying chat by punished users only to themselves (db fiddle attached)
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 myself.uid only appears once. > > I have applied your first 2 comments in https://dbfiddle.uk/?rdbms=postgres_14=c3982c2b5e71369f3c92ee0c06dc29bf WITH myself AS ( SELECT uid FROM words_social WHERE social = in_social AND sid = in_sid ) SELECT CASE WHEN c.uid = myself.uid THEN 1 ELSE 0 END, c.msg FROMwords_chat c JOINmyself ON TRUE JOINwords_games g USING (gid) JOINwords_users opponent ON (opponent.uid IN (g.player1, g.player2) AND opponent.uid <> myself.uid) WHERE c.gid = in_gid -- always show myself my own chat messages AND c.uid = myself.uid -- otherwise only show messages by not muted opponents OR NOT opponent.muted ORDER BY c.created ASC; but where to put the (SELECT uid FROM myself), I do not understand?
Re: Displaying chat by punished users only to themselves (db fiddle attached)
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 CASE WHEN c.uid = myself.uid THEN 1 ELSE 0 END, c.msg FROMmyself JOINwords_chat c ON TRUE JOINwords_games g USING (gid) JOINwords_users opponent ON (opponent.uid IN (g.player1, g.player2) AND opponent.uid <> myself.uid) WHERE c.gid = in_gid -- always show myself my own chat messages AND c.uid = myself.uid -- otherwise only show messages by not muted opponents OR NOT opponent.muted ORDER BY c.created ASC;
Re: Displaying chat by punished users only to themselves (db fiddle attached)
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 = in_social AND sid = in_sid LIMIT 1 ) SELECT CASE WHEN c.uid = myself.uid THEN 1 ELSE 0 END, c.msg FROMwords_chat c JOINwords_games g USING (gid) JOINwords_users opponent ON (opponent.uid IN (g.player1, g.player2) AND opponent.uid <> myself.uid) WHERE c.gid = in_gid -- always show myself my own chat messages AND c.uid = myself.uid -- otherwise only show messages by not muted opponents OR NOT opponent.muted ORDER BY c.CREATED ASC; The error message is: ERROR: missing FROM-clause entry for table "myself" LINE 64: ...uid IN (g.player1, g.player2) AND opponent.uid <> myself.uid... ^
Re: Displaying chat by punished users only to themselves (db fiddle attached)
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 1 ), opponent AS ( SELECT CASE WHEN player1 = myself.uid THEN player2 ELSE player1 END FROM words_games WHERE gid = in_gid ) SELECT CASE WHEN c.uid = myself.uid THEN 1 ELSE 0 END, c.msg FROM myself CROSS JOIN opponent WHERE (c.uid = myself.uid OR NOT opponent.muted) ORDER BY c.CREATED ASC; but the error is: ERROR: missing FROM-clause entry for table "myself" LINE 60: SELECT CASE WHEN player1 = myself.uid THEN play... ^
Re: Displaying chat by punished users only to themselves (db fiddle attached)
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 ) SELECT CASE WHEN c.uid = cte.uid THEN 1 ELSE 0 END, c.msg FROMwords_chat c JOINwords_games g USING (gid) JOINwords_users myself ON (myself.uid IN (g.player1, g.player2) AND myself.uid = cte.uid) JOINwords_users opponent ON (opponent.uid IN (g.player1, g.player2) AND myself.uid <> cte.uid) JOINcte WHERE c.gid= in_gid AND (c.uid = myself.uid OR NOT opponent.muted) ORDER BY c.CREATED ASC; ERROR: syntax error at or near "WHERE" LINE 67: WHERE c.gid= in_gid ^ And if I remove the "JOIN cte" line, then the error is: ERROR: missing FROM-clause entry for table "cte" LINE 64: ...elf.uid IN (g.player1, g.player2) AND myself.uid = cte.uid) ^ >
Re: Displaying chat by punished users only to themselves (db fiddle attached)
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)
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 <> u.uid) > > https://dbfiddle.uk/?rdbms=postgres_14=048b9b9c7c55256c1a478d7c90cd2667 SELECT CASE WHEN c.uid = s.uid THEN 1 ELSE 0 END, c.msg FROMwords_chat c JOINwords_games g USING (gid) JOINwords_users myself ON (myself.uid IN (g.player1, g.player2) AND myself.uid = s.uid) JOINwords_users opponent ON (opponent.uid IN (g.player1, g.player2) AND myself.uid <> s.uid) JOINwords_social s ON (s.uid = myself.uid) WHERE c.gid= in_gid AND s.social = in_social AND s.sid= in_sid AND (c.uid = myself.uid OR NOT opponent.muted) ORDER BY c.CREATED ASC; And get the syntax error which don't quite understand: ERROR: missing FROM-clause entry for table "s" LINE 57: ...yself.uid IN (g.player1, g.player2) AND myself.uid = s.uid) ^ Probably because "myself" needs "s" and vice versa?
Re: Displaying chat by punished users only to themselves (db fiddle attached)
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 own, but only if their > muted property is true. It makes it easier to understand if you join > words_users twice, defining one as "them" and one as "me". Then you can > say something like: WHERE (c.uid = me.uid) OR NOT(them.muted) > > like this: https://dbfiddle.uk/?rdbms=postgres_14=4ab6a09cddae26a11140202fdc41cf5c CREATE OR REPLACE FUNCTION words_get_chat( in_gidinteger, in_social integer, in_sidtext ) RETURNS TABLE ( out_mine integer, out_msg text ) AS $func$ SELECT CASE WHEN c.uid = s.uid THEN 1 ELSE 0 END, c.msg FROMwords_chat c JOINwords_games g USING (gid) JOINwords_users myself ON (myself.uid IN (g.player1, g.player2)) JOINwords_users opponent ON (opponent.uid IN (g.player1, g.player2)) JOINwords_social s ON (s.uid = myself.uid) WHERE c.gid= in_gid AND s.social = in_social AND s.sid= in_sid AND (c.uid = myself.uid OR NOT opponent.muted) ORDER BY c.CREATED ASC; $func$ LANGUAGE sql;
Re: Displaying chat by punished users only to themselves (db fiddle attached)
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)
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 game like mine, they give you a social network id, which is a string. Noone else gets that str. And then I (as game dev) use that str to id the user and when the user is visiting my for the 1st time, I give him a numeric id in my game. And an "auth" str generated by my game. Etc... it works ok. Regards Alex
Re: Displaying chat by punished users only to themselves (db fiddle attached)
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 by other users, by just replacing the numeric "uid"... So I try your suggestion with: https://dbfiddle.uk/?rdbms=postgres_14=48d4bef569d966021e94c72f86d9fce5 CREATE OR REPLACE FUNCTION words_get_chat( in_gidinteger, in_social integer, in_sidtext ) RETURNS TABLE ( out_mine integer, out_msg text ) AS $func$ SELECT CASE WHEN c.uid = s.uid THEN 1 ELSE 0 END, c.msg FROMwords_chat c JOINwords_games g USING (gid) JOINwords_users u1 ON (u1.uid = g.player1) JOINwords_users u2 ON (u2.uid = g.player2) JOINwords_social s ON (s.uid IN (u1.uid, u2.uid)) WHERE c.gid= in_gid AND s.social = in_social AND s.sid= in_sid ORDER BY c.CREATED ASC; $func$ LANGUAGE sql; ...but how to bring the u1.muted or u2.muted there? Best regards Alex
Displaying chat by punished users only to themselves (db fiddle attached)
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
misbehaving users.
However, to prevent the punished users from noticing it and registering new
game accounts, I would like to still show them all messages :->
So here are the 4 tables used in my reduced test case:
CREATE TABLE words_users (
uid SERIAL PRIMARY KEY,
muted BOOLEAN NOT NULL DEFAULT false
);
CREATE TABLE words_social (
sid text NOT NULL CHECK (sid ~ '\S'),
social integer NOT NULL CHECK (0 < social AND social <= 256),
given text NOT NULL CHECK (given ~ '\S'),
uid integer NOT NULL REFERENCES words_users ON DELETE CASCADE,
PRIMARY KEY(sid, social)
);
CREATE TABLE words_games (
gid SERIAL PRIMARY KEY,
player1 integer REFERENCES words_users(uid) ON DELETE CASCADE NOT NULL
CHECK (player1 <> player2),
player2 integer REFERENCES words_users(uid) ON DELETE CASCADE
);
CREATE TABLE words_chat (
cid BIGSERIAL PRIMARY KEY,
created timestamptz NOT NULL,
gid integer NOT NULL REFERENCES words_games ON DELETE CASCADE,
uid integer NOT NULL REFERENCES words_users ON DELETE CASCADE,
msg textNOT NULL
);
Then I put 2 users into the game #10 and they start chatting:
-- create 2 users: one is ok, while the other is muted (punished)
INSERT INTO words_users (uid, muted) VALUES (1, false), (2, true);
INSERT INTO words_social (sid, social, given, uid) VALUES ('abc', 100,
'Nice user', 1), ('def', 200, 'Bad user', 2);
-- put these 2 users into a game #10
INSERT INTO words_games (gid, player1, player2) VALUES (10, 1, 2);
-- both users in the game #10 start chatting
INSERT INTO words_chat (gid, uid, created, msg) VALUES
(10, 1, CURRENT_TIMESTAMP + INTERVAL '1 min', 'Hi how are you doing?'),
(10, 1, CURRENT_TIMESTAMP + INTERVAL '2 min', 'I am a nice user'),
(10, 2, CURRENT_TIMESTAMP + INTERVAL '3 min', 'F*** ***!!'),
(10, 2, CURRENT_TIMESTAMP + INTERVAL '4 min', 'I am a bad user'),
(10, 1, CURRENT_TIMESTAMP + INTERVAL '5 min','Are you there??');
Here is my custom stored function (in SQL, I would prefer not to switch to
PL/pgSQL):
CREATE OR REPLACE FUNCTION words_get_chat(
in_gidinteger,
in_social integer,
in_sidtext
) RETURNS TABLE (
out_mine integer,
out_msg text
) AS
$func$
SELECT
CASE WHEN c.uid = s.uid THEN 1 ELSE 0 END,
c.msg
FROMwords_chat c
JOINwords_games g USING (gid)
JOINwords_users u ON (u.uid IN (g.player1, g.player2)
-- The condition below is broken if both users are not muted
AND (u.muted OR (c.uid = u.uid AND NOT u.muted)))
JOINwords_social s ON (s.uid = u.uid)
WHERE c.gid= in_gid
AND s.social = in_social
AND s.sid= in_sid
ORDER BY c.CREATED ASC;
$func$ LANGUAGE sql;
For a chat of a bad and a nice user it seemingly works:
SELECT words_get_chat(10, 100, 'abc') AS nice_user;
SELECT words_get_chat(10, 200, 'def') AS muted_user;
But if you change both users to be not muted - it will break and they only
will see their own messages.
I have tinkered a lot with my db fiddle... but still cannot figure it out
Thank you!
Alex
Re: if not exists (SELECT 1... UNION SELECT 1...)
s/grumbling/wondering/
Re: if not exists (SELECT 1... UNION SELECT 1...)
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___' RETURN; > > where „return“ is a simple column name as in this select statement: > > select '___WRONG___' return; > > >
Re: if not exists (SELECT 1... UNION SELECT 1...)
Ah, I have to do RETURN '___WRONG___'; and not out_text := '___WRONG___' RETURN;
Re: if not exists (SELECT 1... UNION SELECT 1...)
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 error, like you are using a
> different search
> string or connect to a different database.
>
if you go to an online PostgreSQL editor like
https://extendsclass.com/postgresql-online.html
and just enter my simple test code below,
you will see that wrongly "__CORRECT__" is printed:
DROP TABLE words_nouns;
DROP TABLE words_verbs;
CREATE TABLE words_nouns (
word text PRIMARY KEY, -- CHECK is added below
hashed text NOT NULL,
expl text
);
CREATE TABLE words_verbs (
word text PRIMARY KEY, -- CHECK is added below
hashed text NOT NULL
);
ALTER TABLE words_nouns
ADD CONSTRAINT words_nouns_word_check
CHECK (
word ~ '^[А-Я]{2,}$' AND
word !~ '[ЖШ]Ы' AND
word !~ '[ЧЩ]Я'
);
ALTER TABLE words_verbs
ADD CONSTRAINT words_verbs_word_check
CHECK (
word ~ '^[А-Я]{2,}$' AND
word !~ '[ЖШ]Ы' AND
word !~ '[ЧЩ]Я' AND
word !~ 'Ц[ЮЯ]' AND
(word ~ '[ТЧ]ЬСЯ$' OR
word ~ '[ТЧ]Ь$' OR
word ~ 'ТИ$')
);
CREATE OR REPLACE FUNCTION words_guess_puzzle(
in_mid bigint,
in_guess text,
OUT out_text text
) RETURNS text AS
$func$
BEGIN
in_guess := UPPER(in_guess);
-- check if the in_guess is a valid word - WHY DOES THIS NEVER TRIGGER?
IF NOT EXISTS(SELECT 1 FROM words_nouns WHERE word = in_guess UNION
SELECT 1 FROM words_verbs WHERE word = in_guess) THEN
out_text := '___WRONG___'
RETURN;
END IF;
out_text := '___CORRECT___'
RETURN;
END
$func$ LANGUAGE plpgsql;
SELECT words_guess_puzzle(123, 'ABCDE');
if not exists (SELECT 1... UNION SELECT 1...)
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 same command in my stored function, then it goes through, as if the word would exist (and a new record is inserted into the words_puzzle table): CREATE OR REPLACE FUNCTION words_guess_puzzle( in_mid bigint, in_socialinteger, in_sid text, in_auth text, in_guess text, OUT out_text text ) RETURNS text AS $func$ DECLARE _uid integer; BEGIN IF NOT words_valid_user(in_social, in_sid, in_auth) THEN RAISE EXCEPTION 'Invalid user = % %', in_social, in_sid; END IF; _uid := (SELECT uid FROM words_social WHERE social = in_social AND sid = in_sid); in_guess := UPPER(in_guess); -- check if the in_guess is a valid word - WHY DOES THIS NEVER TRIGGER? IF NOT EXISTS(SELECT 1 FROM words_nouns WHERE word = in_guess UNION SELECT 1 FROM words_verbs WHERE word = in_guess) THEN out_text := '___WRONG___' RETURN; END IF; INSERT INTO words_puzzle (mid, uid, word, guessed) VALUES (in_mid, _uid, in_guess, CURRENT_TIMESTAMP); out_text := '___CORRECT___' RETURN; END $func$ LANGUAGE plpgsql; What could be the reason please? Best regards Alex
Re: [jetty-users] Migrating 9 to 10: what replaces WebSocketServlet and JSON.parse() ?
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 10.0.6.
Does it make any sense to add "servlet" module, when I already have
"deploy" module added?
I have tried with and without "--add-module=servlet" and could not see any
difference.
Also, I am curious that when I compile my WAR file against Jetty 10.0.6 and
then put it into webapps dir of the old Jetty 9.4.x installation on my
Linux server, then it fails with:
Sep 04 10:38:28 afarber.de java[206224]: 2021-09-04
10:38:28.833:WARN:oejw.WebAppContext:main: Failed startup of context
o.e.j.w.WebAppContext@3f56875e
{/en,file:///tmp/jetty-127_0_0_1-8082-words-4_0_war-_en-any-14171957377492967902
/webapp/,UNAVAILABLE}{/var/www/words-4.0.war}
Sep 04 10:38:28 afarber.de java[206224]: java.lang.RuntimeException:
java.lang.IllegalStateException: No Jetty ContextHandler, Jetty WebSocket
SCI unavailable
Sep 04 10:38:28 afarber.de java[206224]: at
org.eclipse.jetty.annotations.ServletContainerInitializersStarter.doStart(ServletContainerInitializersStarter.java:69)
Sep 04 10:38:28 afarber.de java[206224]: at
org.eclipse.jetty.util.component.AbstractLifeCycle.start(AbstractLifeCycle.java:73)
Shouldn't the WAR file have some standard APIs and thus run fine in both
deployments, Jetty 10 and Jetty 9?
Best regards
Alex
___
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] Migrating 9 to 10: what replaces WebSocketServlet and JSON.parse() ?
Thank you, Joakim, now my servlet runs, but -
On Fri, Sep 3, 2021 at 6:32 PM Joakim Erdfelt wrote:
> You are using a JettyWebSocketCreator.
>
> Get rid of factory.register(WordsListener.class);
>
Jetty 10 prints:
SLF4J: No SLF4J providers were found.
SLF4J: Defaulting to no-operation (NOP) logger implementation
SLF4J: See http://www.slf4j.org/codes.html#noProviders for further details.
And then I don't see any logs printed by my servlet with
Logger LOG = Log.getLogger(LOG_TAG);
LOG.info("");
And thus I cannot find out why there is no data returned from PostgreSQL...
Maybe this line does not really work?
factory.setCreator(new WordsCreator(this,
mBundle.getString(STR_DATABASE_URL)));
My servlet is multilanguage and thus I was passing differen DB credentials
with the line above.
What should I please 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.
>>
>> 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
>>
>> @SET JETTY_HOME=%~dp0\jetty-home-10.0.6
>> @SET JETTY_BASE=%~dp0\jetty.base
>> java -Djdbc.drivers=org.postgresql.Driver -jar %JETTY_HOME%\start.jar
>> jetty.home=%JETTY_HOME% jetty.base=%JETTY_BASE%
>>
>> Then Jetty 10 starts fine initially:
>>
>> 2021-09-03 17:59:51.475:INFO :oejsh.ContextHandler:main: Started
>> o.e.j.w.WebAppContext@42a9e5d1
>> {/en,file:///C:/Users/U299FAV/src/slova/webapp/target/words-4.0/,AVAILABLE}{C:\Users\U299FAV\src\slova\jetty.base/../webapp/target/words-4.0.war}
>> 2021-09-03 17:59:51.485:INFO :oejsh.ContextHandler:main: Started
>> o.e.j.s.h.ContextHandler@2c1b9e4b{/,null,AVAILABLE}
>> 2021-09-03 17:59:51.507:INFO :oejs.AbstractConnector:main: Started
>> ServerConnector@7e027a05{HTTP/1.1, (http/1.1)}{0.0.0.0:8080}
>> 2021-09-03 17:59:51.518:INFO :oejs.Server:main: Started
>> Server@624ea235{STARTING}[10.0.6,sto=5000]
>> @4326ms
>>
>> But when I try accessing the following path (I put the XML file under
>> jetty.base\webapps):
>>
>>
>> > "http://www.eclipse.org/jetty/configure_9_0.dtd;>
>>
>> /ru
>> > name="jetty.base"/>/../webapp/target/words-4.0.war
>>
>>
>> Then Jetty 10 reports the error:
>>
>> 2021-09-03 18:00:00.583:WARN :oejshC.ru:qtp1072410641-16: unavailable
>> java.lang.RuntimeException: java.lang.NoSuchMethodException:
>> de.afarber.WordsListener.()
>> at
>> org.eclipse.jetty.websocket.server.JettyWebSocketServlet$CustomizedWebSocketServletFactory.register(JettyWebSocketServlet.java:218)
>> at de.afarber.WordsServlet.configure(WordsServlet.java:646)
>> at
>> org.eclipse.jetty.websocket.server.JettyWebSocketServlet.init(JettyWebSocketServlet.java:168)
>> at de.afarber.WordsServlet.init(WordsServlet.java:652)
>> at javax.servlet.GenericServlet.init(GenericServlet.java:180)
>> at
>> org.eclipse.jetty.servlet.ServletHolder$Wrapper.init(ServletHolder.java:1305)
>> at
>> org.eclipse.jetty.servlet.ServletHolder.initServlet(ServletHolder.java:633)
>> at
>> org.eclipse.jetty.servlet.ServletHolder.getServlet(ServletHolder.java:486)
>> at
>> org.eclipse.jetty.servlet.ServletHolder.prepare(ServletHolder.java:731)
>> at
>> org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:503)
>> at
>> org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:131)
>> at
>> org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:580)
>> at
>> org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)
>> at
>> org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:223)
>> at
>> org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1571)
>> at
>> org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:221)
>> at
>> org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1372)
>> at
>> org.eclipse.jetty.server.hand
Re: [jetty-users] Migrating 9 to 10: what replaces WebSocketServlet and JSON.parse() ?
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
@SET JETTY_HOME=%~dp0\jetty-home-10.0.6
@SET JETTY_BASE=%~dp0\jetty.base
java -Djdbc.drivers=org.postgresql.Driver -jar %JETTY_HOME%\start.jar
jetty.home=%JETTY_HOME% jetty.base=%JETTY_BASE%
Then Jetty 10 starts fine initially:
2021-09-03 17:59:51.475:INFO :oejsh.ContextHandler:main: Started
o.e.j.w.WebAppContext@42a9e5d1
{/en,file:///C:/Users/U299FAV/src/slova/webapp/target/words-4.0/,AVAILABLE}{C:\Users\U299FAV\src\slova\jetty.base/../webapp/target/words-4.0.war}
2021-09-03 17:59:51.485:INFO :oejsh.ContextHandler:main: Started
o.e.j.s.h.ContextHandler@2c1b9e4b{/,null,AVAILABLE}
2021-09-03 17:59:51.507:INFO :oejs.AbstractConnector:main: Started
ServerConnector@7e027a05{HTTP/1.1, (http/1.1)}{0.0.0.0:8080}
2021-09-03 17:59:51.518:INFO :oejs.Server:main: Started
Server@624ea235{STARTING}[10.0.6,sto=5000]
@4326ms
But when I try accessing the following path (I put the XML file under
jetty.base\webapps):
http://www.eclipse.org/jetty/configure_9_0.dtd;>
/ru
/../webapp/target/words-4.0.war
Then Jetty 10 reports the error:
2021-09-03 18:00:00.583:WARN :oejshC.ru:qtp1072410641-16: unavailable
java.lang.RuntimeException: java.lang.NoSuchMethodException:
de.afarber.WordsListener.()
at
org.eclipse.jetty.websocket.server.JettyWebSocketServlet$CustomizedWebSocketServletFactory.register(JettyWebSocketServlet.java:218)
at de.afarber.WordsServlet.configure(WordsServlet.java:646)
at
org.eclipse.jetty.websocket.server.JettyWebSocketServlet.init(JettyWebSocketServlet.java:168)
at de.afarber.WordsServlet.init(WordsServlet.java:652)
at javax.servlet.GenericServlet.init(GenericServlet.java:180)
at
org.eclipse.jetty.servlet.ServletHolder$Wrapper.init(ServletHolder.java:1305)
at
org.eclipse.jetty.servlet.ServletHolder.initServlet(ServletHolder.java:633)
at
org.eclipse.jetty.servlet.ServletHolder.getServlet(ServletHolder.java:486)
at
org.eclipse.jetty.servlet.ServletHolder.prepare(ServletHolder.java:731)
at
org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:503)
at
org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:131)
at
org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:580)
at
org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)
at
org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:223)
at
org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1571)
at
org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:221)
at
org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1372)
at
org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:176)
at
org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:463)
at
org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1544)
at
org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:174)
at
org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1294)
at
org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:129)
at
org.eclipse.jetty.server.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:192)
at
org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:51)
at
org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)
at org.eclipse.jetty.server.Server.handle(Server.java:562)
at
org.eclipse.jetty.server.HttpChannel.lambda$handle$0(HttpChannel.java:406)
at
org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:663)
at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:398)
at
org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:282)
at
org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:319)
at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:100)
at
org.eclipse.jetty.io.SocketChannelEndPoint$1.run(SocketChannelEndPoint.java:101)
at
org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:894)
at
org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:1038)
at java.base/java.lang.Thread.run(Thread.java:834)
My WordsListener.java looks like:
import org.eclipse.jetty.util.ajax.JSON;
import org.eclipse.jetty.websocket.api.Session;
import org.eclipse.jetty.websocket.api.WebSocketListener;
import
Re: [jetty-users] Migrating 9 to 10: what replaces WebSocketServlet and JSON.parse() ?
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 myMap = (Map) json.fromJSON(str);
>
thank you for the helpful tips on JSON parsing.
Could you please help me with creating JSON?
In Jetty 9.4.x I had this method:
// this string is POSTed to Google FCM server
public String toFcmBody(String boardUrl, String gameNumber) {
Map root = new HashMap<>();
Map notif = new HashMap<>();
Map data = new HashMap<>();
root.put(KEY_TO, mFcm);
root.put(KEY_NOTIFICATION, notif);
root.put(KEY_DATA, data);
notif.put(KEY_TITLE, gameNumber + " " + mGid);
notif.put(KEY_BODY, mBody);
notif.put(KEY_IMAGE, boardUrl + mGid);
data.put(KEY_GID, mGid);
return JSON.toString(root);
}
So now I am trying the following, but what to use as the first param to
appendMap()?
// this string is POSTed to Google FCM server
public String toFcmBody(String boardUrl, String gameNumber) {
Map root = new HashMap<>();
Map notif = new HashMap<>();
Map data = new HashMap<>();
root.put(KEY_TO, mFcm);
root.put(KEY_NOTIFICATION, notif);
root.put(KEY_DATA, data);
notif.put(KEY_TITLE, gameNumber + " " + mGid);
notif.put(KEY_BODY, mBody);
notif.put(KEY_IMAGE, boardUrl + mGid);
data.put(KEY_GID, mGid);
// new code
JSON json = new JSON();
json.appendMap(???, root);
return json.toString();
}
And also Jetty 10 is not happy with the deprecated header() method here:
MultiMap postParams = new MultiMap<>();
postParams.put("code", code);
postParams.put("client_id",
mBundle.getString(STR_GOOGLE_ID));
postParams.put("client_secret",
mBundle.getString(STR_GOOGLE_SECRET));
postParams.put("redirect_uri",
String.format(GOOGLE_REDIRECT_URI, mLanguage));
postParams.put("grant_type","authorization_code");
String tokenStr = mHttpClient.POST(GOOGLE_TOKEN_URL)
.header(HttpHeader.ACCEPT, APPLICATION_JSON)
// what to use here please?
.header(HttpHeader.CONTENT_TYPE, APPLICATION_URLENCODED)
.content(new
StringContentProvider(UrlEncoded.encode(postParams, StandardCharsets.UTF_8,
false)))
.send().getContentAsString();
Map tokenMap = (Map)
mJson.fromJSON(tokenStr);
Best regards
Alex
___
jetty-users mailing list
jetty-users@eclipse.org
To unsubscribe from this list, visit
https://www.eclipse.org/mailman/listinfo/jetty-users
[jetty-users] Migrating 9 to 10: what replaces WebSocketServlet and JSON.parse() ?
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 WebSocketServlet {
@Override
public void configure(WebSocketServletFactory factory) {
mLanguage = System.getenv("COUNTRY");
mBundle = ResourceBundle.getBundle("strings",
LOCALES.get(mLanguage));
factory.getPolicy().setIdleTimeout(IDLE_TIMEOUT_SECONDS * 1000);
factory.getPolicy().setMaxBinaryMessageSize(0);
factory.getPolicy().setMaxTextMessageSize(64 * 1024);
factory.register(MyListener.class);
factory.setCreator(new MyCreator(this,
mBundle.getString(STR_DATABASE_URL)));
}
@Override
protected void doGet(HttpServletRequest httpReq, HttpServletResponse
httpResp) throws ServletException, IOException {
//
}
}
What is the class to use in Jetty 10 instead of WebSocketServlet?
Also, I use the following code in few spots to parse JSON strings:
Map myMap = (Map) JSON.parse(str);
but now I get the compile error: String cannot be converted to Source
Is there a new parser, should I maybe use AsyncJSON and how?
Best regards
Alex
___
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] Using WebAppContext at contextPath / but also serving images, sounds folders
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 jetty-users@eclipse.org To unsubscribe from this list, visit https://www.eclipse.org/mailman/listinfo/jetty-users
Re: [jetty-users] Using WebAppContext at contextPath / but also serving images, sounds folders
Yes, Jan - On Tue, Aug 10, 2021 at 4:20 AM Jan Bartel wrote: > I'm not sure what you are wanting to do? Do you want to keep your 2 > static content ContextHandlers (at context paths "/images" and "/sounds") > and your var/www/myservlet-3.0.war but move it to context 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: > >> >> 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;> >> >> >> /ws >> /var/www/myservlet-3.0.war >> >> >> >> >> And the two configs for 2 are (only the "resourceBase" differs): >> >> >> > "http://www.eclipse.org/jetty/configure_9_0.dtd;> >> >> /images >> >> >> /var/www/html/mydomain.com/images >> >> true >> >> >> >> >> >> It works well for several years already (and behind HAProxy 1.8.27), but >> I am not happy with having contextPath "/ws" as a prefix to my servlet. >> >> I would like to change it to "/" aka root, but wonder how to keep >> "/images" and "/sounds" also servable by Jetty. >> >> Would org.eclipse.jetty.fcgi.server.proxy.TryFilesFilter help me in this >> situation? >> >> If a file (a sound or image file) is found, it should be served. >> Otherwise the Websockets, GET or POST request should be forwarded to my >> servlet. >> >> In the Jetty doc for FastCGI there is an example: >> >> >> org.eclipse.jetty.fcgi.server.proxy.TryFilesFilter >> /* >> >> >> > class="javax.servlet.DispatcherType" /> >> >> >> >> files >> $path /index.php?p=$path >> >> >> >> but I am not sure how to apply it to my situation, how to replace the >> "index.php" by my servlet there? >> >> ___ jetty-users mailing list jetty-users@eclipse.org To unsubscribe from this list, visit https://www.eclipse.org/mailman/listinfo/jetty-users
[jetty-users] Using WebAppContext at contextPath / but also serving images, sounds folders
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;> /ws /var/www/myservlet-3.0.war And the two configs for 2 are (only the "resourceBase" differs): http://www.eclipse.org/jetty/configure_9_0.dtd;> /images /var/www/html/mydomain.com/images true It works well for several years already (and behind HAProxy 1.8.27), but I am not happy with having contextPath "/ws" as a prefix to my servlet. I would like to change it to "/" aka root, but wonder how to keep "/images" and "/sounds" also servable by Jetty. Would org.eclipse.jetty.fcgi.server.proxy.TryFilesFilter help me in this situation? If a file (a sound or image file) is found, it should be served. Otherwise the Websockets, GET or POST request should be forwarded to my servlet. In the Jetty doc for FastCGI there is an example: org.eclipse.jetty.fcgi.server.proxy.TryFilesFilter /* files $path /index.php?p=$path but I am not sure how to apply it to my situation, how to replace the "index.php" by my servlet there? Best regards Alex ___ jetty-users mailing list jetty-users@eclipse.org To unsubscribe from this list, visit https://www.eclipse.org/mailman/listinfo/jetty-users
Re: Stored function RETURNS table, but in some cases columns are missing - should I set them to NULL?
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 params explicitly to NULL? > > plpgsql initializes them to null by default, I believe, just like ordinary > local variables. > > > org.postgresql.util.PSQLException: ERROR: column "out_uid" does not > exist| > > This isn't related to what your function does internally. > > I think the issue is you renamed out_uid to uid in your SELECT: > > >String SQL_JOIN_GAME = > >"SELECT " + > >"out_uidAS uid," + > > thank you and sorry for my silly mistake
Stored function RETURNS table, but in some cases columns are missing - should I set them to NULL?
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)
out_uidinteger,
-- the id of the created game
out_gidinteger,
out_fcmtext,
out_apns text,
out_admtext,
out_hmstext,
-- the most recently used social network and the user id
there
out_social integer,
out_sidtext,
-- the push notification text: the opponent has joined
out_body text
) AS
$func$
$func$ LANGUAGE plpgsql;
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 params explicitly to NULL?
For example here:
-- case 1
SELECT gid
INTO out_gid
FROM words_games
WHERE finished IS NULL
ANDbid = in_bid
AND(
(player1 = in_uid AND played1 IS NULL) OR
(player2 = in_uid AND played2 IS NULL)
) LIMIT 1;
IF out_gid IS NOT NULL THEN
-- should I set all the other OUT params to NULL here?
<---
RETURN;
END IF;
I was expecting to check for out_uid, if it is a positive number in my Java
code with:
String SQL_JOIN_GAME =
"SELECT " +
"out_uidAS uid," +
// the id of the new game is never NULL, but the other
columns can be NULL
"out_gidAS gid," +
"out_fcmAS fcm," +
"out_apns AS apns, " +
"out_admAS adm," +
"out_hmsAS hms," +
"out_social AS social, " +
"out_sidAS sid," +
"out_body AS body" +
"FROM words_join_new_game(?::int, ?::int)";
int gid = 0;
try (Connection db = DriverManager.getConnection(mDatabaseUrl);
PreparedStatement st = db.prepareStatement(SQL_JOIN_GAME)) {
st.setInt(1, mUid);
st.setInt(2, bid);
ResultSet rs = st.executeQuery();
if (rs.next()) {
// get the id of the new game
gid = rs.getInt(KEY_GID);
// get the id of the opponent
int uid = rs.getInt(KEY_UID);
// send notification to the other player
if (uid > 0) {
Notification n = new Notification(
uid,
gid,
rs.getString(COLUMN_FCM),
rs.getString(COLUMN_APNS),
rs.getString(COLUMN_ADM),
rs.getString(COLUMN_HMS),
rs.getInt(COLUMN_SOCIAL),
rs.getString(COLUMN_SID),
rs.getString(COLUMN_BODY)
);
mServlet.sendNotification(n);
}
}
}
but I am getting the error:
org.postgresql.util.PSQLException: ERROR: column "out_uid" does not exist|
Position: 8
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323)
at
org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:481)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:401)
at
org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:164)
at
org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:114)
at de.afarber.WordsListener.handleNewGame(WordsListener.java:216)
at de.afarber.WordsListener.onWebSocketText(WordsListener.java:101)
Thank you
Alex
Re: Localizing stored functions by replacing placeholders in their body
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 bodys of my stored functions. After some more thinking yesterday I have decided to embed the SQL files with stored functions declarations in my servlet JAR file - that is where the rest and most of my translations are. I will just load them from servlet resources when the servlet start, replace the placeholder by Java and the execute them with CREATE OR REPLACE FUNCTION ...
Re: Localizing stored functions by replacing placeholders in their body
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
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
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 '$(placeholder)'; $func$ LANGUAGE sql IMMUTABLE; $localize$, '\$\(\w+\)', 'English word', 'g'); Unfortunately, I get the error: ERROR: prepared statement "regexp_replace" does not exist
Re: Localizing stored functions by replacing placeholders in their body
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
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 words_get_notification.sql \i ../words_common.sql \i words_valid_tile.sql \i words_get_moves.sql \i words_answer_puzzle.sql \i words_rare_letter_1.sql \i words_rare_letter_2.sql And then the ../words_common.sql creates tables and has 40 more "\i" calls. So I was hoping to have some SQL command to localize my stored functions. Best regards Alex
Localizing stored functions by replacing placeholders in their body
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 languages. This is not very comfortable and I would like to switch to using same source code (regardless of the human language) for all stored functions. And after deploying a database, just run few commands to replace placeholders in the stored functions. So I am trying: CREATE OR REPLACE FUNCTION localize_hello() RETURNS text AS $func$ SELECT '$(hello)'; $func$ LANGUAGE sql IMMUTABLE; And then: update pg_proc set prosrc = regexp_replace(prosrc, '$\(\w+\)','Hi english','g') where proname='localize_hello'; But the error is: ERROR: permission denied for table pg_proc So I connect as user "postgres" and then the command seemingly succeeds, but when I call it, the delivered string is still old: select * from localize_hello(); localize_hello $(hello) (1 row) Is this a right approach? Do you please have any advice here? Thanks Alex
Re: [jetty-users] Is static ConcurrentHashMap a reliable choice for WebSocketServlet?
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 session.getRemoteAddress().getHostString() + ":" + session.getRemoteAddress().getPort() - because they identify the multiple Sessions for a user (when she/he uses several browser tabs or maybe uses desktop and mobile versions at the same time): https://gist.github.com/afarber/4f82205881ddb0223130f74b4e87abda And yes, I had also the structure in the servlet before and passed it by a custom WebSocketCreator. Both variants work and of course Jetty has only 1 process (I can see it on my Linux machine). At the same I have followed Joakim's advice and decreased the idle timeout to 5 minutes. And additionally I track when there is no human actions at the remote end for 5 minutes - and close those sessions at my servlet (to handle the abandoned browser tabs). I didn't have to use ping in my code... I have impression that Jetty source code is very stable and overall excellent, thank you Sorry if my mail was a bit offtopic Best regards Alex ___ jetty-users mailing list jetty-users@eclipse.org To unsubscribe from this list, visit https://www.eclipse.org/mailman/listinfo/jetty-users
[jetty-users] Is static ConcurrentHashMap a reliable choice for WebSocketServlet?
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 reliably. Maybe Jetty starts several
Linux process and thus the static data structure is not shared among them?
Because in my custom WebSocketListener I have a code:
@Override
public void onWebSocketText(String str) {
// here the user is authenticated and mUid is found
Session oldSession = SESSIONS.put(mUid, mSession);
disconnect(oldSession);
}
private void disconnect(Session session) {
LOG.info("disconnect: session={}", session); // surprisingly often
session is null
try {
session.close();
session.disconnect();
} catch (Exception ex) {
// ignore
}
}
And often the old session printed by the above LOG is null, even though I
would expect it be non-null.
Best regards
Alex
___
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
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 and a dialog is displayed to the
user, offering to reconnect):
// called by the onWebSocketText Jetty-callback
private void handleLogin(String username, String password) throws
Exception {
// check username + password and then send the player her/his open
games
mSession.getRemote().sendStringByFuture(mServlet.getGames(mUid));
// if oldSession is not null and still open,
// then the player has several browser tabs open or
// uses the mobile and the desktop app at the same time
Session oldSession = SESSIONS.put(mUid, mSession);
try {
oldSession.close(4000, "another game session detected");
oldSession.disconnect();
} catch (Exception ex) {
// ignore
}
However this only works sporadically, I rarely see the code 4000 in the
browser.
Is this caused by the half open connections too?
Thank you
Alex
___
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
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);
factory.getPolicy().setMaxTextMessageSize(64 * 1024);
factory.register(MyListener.class);
factory.setCreator(new MyCreator(this)));
}
and have started implementing calling sendPing() every 4 minutes for the
connected clients, but then I have read your other message at
https://stackoverflow.com/a/54654700/165071
"Setting Max Idle Timeout and then causing the connection to not be idle by
sending ping/pong isn't ideal."
Do you mean by that, that if my custom WebSocketServlet will send PING
every 4 minutes to the browser clients, then the connection will never be
idle? Or what do you mean by "isn't ideal"? What else could be done here?
Thank you
Alex
___
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
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
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 game which requires a single connection only? What is the explanation? Some buggy browser (my players are from Russia) or too many tabs? qtp100555887-17: onWebSocketClose: hostname=77.111.247.123 port=13533 mUid=18555 statusCode=1006 reason=Disconnected qtp100555887-34: onWebSocketClose: hostname=77.111.247.123 port=55271 mUid=18555 statusCode=1006 reason=Disconnected qtp100555887-71: onWebSocketClose: hostname=77.111.247.123 port=33979 mUid=18555 statusCode=1006 reason=Disconnected qtp100555887-17: onWebSocketClose: hostname=77.111.247.123 port=58969 mUid=0 statusCode=1006 reason=Disconnected qtp100555887-54: onWebSocketClose: hostname=77.111.247.123 port=56049 mUid=18555 statusCode=1006 reason=Disconnected qtp100555887-34: onWebSocketClose: hostname=77.111.247.123 port=37309 mUid=0 statusCode=1006 reason=Disconnected qtp100555887-54: onWebSocketClose: hostname=77.111.247.123 port=52163 mUid=18555 statusCode=1006 reason=Disconnected qtp100555887-17: onWebSocketClose: hostname=77.111.247.123 port=59447 mUid=0 statusCode=1006 reason=Disconnected qtp100555887-66: onWebSocketClose: hostname=77.111.247.123 port=40691 mUid=0 statusCode=1006 reason=Disconnected qtp100555887-71: onWebSocketClose: hostname=77.111.247.123 port=42557 mUid=18555 statusCode=1006 reason=Disconnected qtp100555887-71: onWebSocketClose: hostname=77.111.247.123 port=57649 mUid=18555 statusCode=1006 reason=Disconnected qtp100555887-71: onWebSocketClose: hostname=77.111.247.123 port=15239 mUid=18555 statusCode=1006 reason=Disconnected Thank you Alex ___ jetty-users mailing list jetty-users@eclipse.org To unsubscribe from this list, visit https://www.eclipse.org/mailman/listinfo/jetty-users
[jetty-users] WebSocketListener.onWebSocketClose called numerous times
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= INFO:words:qtp100555887-756: onWebSocketClose: hostname=77.111.244.123 mUid=0 statusCode=1001 reason= INFO:words:qtp100555887-757: onWebSocketClose: hostname=77.111.244.123 mUid=0 statusCode=1001 reason= INFO:words:qtp100555887-929: onWebSocketClose: hostname=77.111.244.123 mUid=0 statusCode=1001 reason= INFO:words:qtp100555887-757: onWebSocketClose: hostname=77.111.244.123 mUid=0 statusCode=1001 reason= INFO:words:qtp100555887-938: onWebSocketClose: hostname=77.111.244.123 mUid=0 statusCode=1001 reason= INFO:words:qtp100555887-937: onWebSocketClose: hostname=77.111.244.123 mUid=0 statusCode=1001 reason= INFO:words:qtp100555887-938: onWebSocketClose: hostname=77.111.244.123 mUid=0 statusCode=1001 reason= INFO:words:qtp100555887-929: onWebSocketClose: hostname=77.111.244.123 mUid=0 statusCode=1001 reason= INFO:words:qtp100555887-953: onWebSocketClose: hostname=77.111.244.123 mUid=0 statusCode=1001 reason= INFO:words:qtp100555887-756: onWebSocketClose: hostname=77.111.244.123 mUid=0 statusCode=1001 reason= INFO:words:qtp100555887-929: onWebSocketClose: hostname=77.111.244.123 mUid=0 statusCode=1001 reason= INFO:words:qtp100555887-756: onWebSocketClose: hostname=77.111.244.123 mUid=0 statusCode=1001 reason= INFO:words:qtp100555887-757: onWebSocketClose: hostname=77.111.244.123 mUid=0 statusCode=1001 reason= INFO:words:qtp100555887-938: onWebSocketClose: hostname=77.111.244.123 mUid=0 statusCode=1001 reason= INFO:words:qtp100555887-953: onWebSocketClose: hostname=77.111.244.123 mUid=0 statusCode=1001 reason= INFO:words:qtp100555887-938: onWebSocketClose: hostname=77.111.244.123 mUid=0 statusCode=1001 reason= INFO:words:qtp100555887-937: onWebSocketClose: hostname=77.111.244.123 mUid=0 statusCode=1001 reason= INFO:words:qtp100555887-757: onWebSocketClose: hostname=77.111.244.123 mUid=0 statusCode=1001 reason= INFO:words:qtp100555887-953: onWebSocketClose: hostname=77.111.244.123 mUid=0 statusCode=1001 reason= INFO:words:qtp100555887-929: onWebSocketClose: hostname=77.111.244.123 mUid=0 statusCode=1001 reason= INFO:words:qtp100555887-938: onWebSocketClose: hostname=77.111.244.123 mUid=0 statusCode=1001 reason= INFO:words:qtp100555887-756: onWebSocketClose: hostname=77.111.244.123 mUid=0 statusCode=1001 reason= INFO:words:qtp100555887-937: onWebSocketClose: hostname=77.111.244.123 mUid=0 statusCode=1001 reason= INFO:words:qtp100555887-938: onWebSocketClose: hostname=77.111.244.123 mUid=0 statusCode=1001 reason= INFO:words:qtp100555887-756: onWebSocketClose: hostname=77.111.244.123 mUid=0 statusCode=1001 reason= INFO:words:qtp100555887-757: onWebSocketClose: hostname=77.111.244.123 mUid=0 statusCode=1001 reason= INFO:words:qtp100555887-938: onWebSocketClose: hostname=77.111.244.123 mUid=0 statusCode=1001 reason= INFO:words:qtp100555887-937: onWebSocketClose: hostname=77.111.244.123 mUid=0 statusCode=1001 reason= INFO:words:qtp100555887-929: onWebSocketClose: hostname=77.111.244.123 mUid=0 statusCode=1001 reason= INFO:words:qtp100555887-951: onWebSocketClose: hostname=77.111.244.123 mUid=0 statusCode=1001 reason= INFO:words:qtp100555887-757: onWebSocketClose: hostname=77.111.244.123 mUid=0 statusCode=1006 reason=Disconnected INFO:words:qtp100555887-951: onWebSocketClose: hostname=77.111.244.123 mUid=0 statusCode=1006 reason=Disconnected INFO:words:qtp100555887-938: onWebSocketClose: hostname=77.111.244.123 mUid=0 statusCode=1006 reason=Disconnected INFO:words:qtp100555887-951: onWebSocketClose: hostname=77.111.244.123 mUid=0 statusCode=1006 reason=Disconnected INFO:words:qtp100555887-937: onWebSocketClose: hostname=77.111.244.123 mUid=0 statusCode=1006 reason=Disconnected INFO:words:qtp100555887-938: onWebSocketClose: hostname=77.111.244.123 mUid=0 statusCode=1006 reason=Disconnected INFO:words:qtp100555887-951: onWebSocketClose: hostname=77.111.244.123 mUid=0 statusCode=1006 reason=Disconnected INFO:words:qtp100555887-953: onWebSocketClose: hostname=77.111.244.123 mUid=0 statusCode=1006 reason=Disconnected INFO:words:qtp100555887-756: onWebSocketClose: hostname=77.111.244.123 mUid=0 statusCode=1006 reason=Disconnected INFO:words:qtp100555887-757: onWebSocketClose: hostname=77.111.244.123 mUid=0 statusCode=1006 reason=Disconnected INFO:words:qtp100555887-929: onWebSocketClose: hostname=77.111.244.123 mUid=0 statusCode=1006 reason=Disconnected INFO:words:qtp100555887-756: onWebSocketClose: hostname=77.111.244.123 mUid=0 statusCode=1006 reason=Disconnected INFO:words:qtp100555887-951: onWebSocketClose: hostname=77.111.244.123 mUid=0 statusCode=1006 reason=Disconnected INFO:words:qtp100555887-938: onWebSocketClose: hostname=77.111.244.123 mUid=0 statusCode=1006 reason=Disconnected INFO:words:qtp100555887-951: onWebSocketClose:
Re: Deleting takes days, should I add some index?
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 > referencing tables and see what references those and index the > column(s) which are referencing. > >
Re: Deleting takes days, should I add some index?
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: >> >>> The question is why does the command take days (when I tried last time): >>> delete from words_games where created < now() - interval '12 month'; >>> >>> >> postgres=# explain analyze delete from words_games where gid = 44877; >> >> create index on words_scores(mid); >> > I have also added: create index on words_puzzles(mid); and then the result if finally good enough for my nightly cronjob: explain analyze delete from words_games where created < now() - interval '12 month'; QUERY PLAN -- Delete on words_games (cost=0.00..49802.33 rows=104022 width=6) (actual time=2121.475..2121.476 rows=0 loops=1) -> Seq Scan on words_games (cost=0.00..49802.33 rows=104022 width=6) (actual time=0.006..85.908 rows=103166 loops=1) Filter: (created < (now() - '1 year'::interval)) Rows Removed by Filter: 126452 Planning Time: 0.035 ms Trigger for constraint words_chat_gid_fkey on words_games: time=598.444 calls=103166 Trigger for constraint words_moves_gid_fkey on words_games: time=83745.244 calls=103166 Trigger for constraint words_scores_gid_fkey on words_games: time=30638.420 calls=103166 Trigger for constraint words_puzzles_mid_fkey on words_moves: time=15426.679 calls=3544242 Trigger for constraint words_scores_mid_fkey on words_moves: time=18546.115 calls=3544242 Execution Time: 151427.183 ms (11 rows) There is one detail I don't understand in the output of "explain analyze" - why do the lines "Trigger for constraint words_scores_mid_fkey on words_moves: time=1885.372 calls=4" completely disappear after adding the index? Are those the "ON DELETE CASCADE" triggers? Aren't they called after the index has been added? Best regards Alex
Re: Deleting takes days, should I add some index?
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 month';
Re: Deleting takes days, should I add some index?
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 |
Default
--+--+---+--+--
gid | integer | | not null |
nextval('words_games_gid_seq'::regclass)
created | timestamp with time zone | | not null |
finished | timestamp with time zone | | |
player1 | integer | | not null |
player2 | integer | | |
played1 | timestamp with time zone | | |
played2 | timestamp with time zone | | |
state1 | text | | |
state2 | text | | |
reason | text | | |
hint1| text | | |
hint2| text | | |
score1 | integer | | not null |
score2 | integer | | not null |
chat1| integer | | not null |
chat2| integer | | not null |
hand1| character(1)[] | | not null |
hand2| character(1)[] | | not null |
pile | character(1)[] | | not null |
letters | character(1)[] | | not null |
values | integer[]| | not null |
bid | integer | | not null |
diff1| integer | | |
diff2| integer | | |
open1| boolean | | not null | false
open2| boolean | | not null | false
Indexes:
"words_games_pkey" PRIMARY KEY, btree (gid)
"words_games_created_idx" btree (created)
"words_games_player1_coalesce_idx" btree (player1, COALESCE(finished,
'infinity'::timestamp with time zone))
"words_games_player2_coalesce_idx" btree (player2, COALESCE(finished,
'infinity'::timestamp with time zone))
Check constraints:
"words_games_chat1_check" CHECK (chat1 >= 0)
"words_games_chat2_check" CHECK (chat2 >= 0)
"words_games_check" CHECK (player1 <> player2)
"words_games_score1_check" CHECK (score1 >= 0)
"words_games_score2_check" CHECK (score2 >= 0)
Foreign-key constraints:
"words_games_bid_fkey" FOREIGN KEY (bid) REFERENCES words_boards(bid)
ON DELETE CASCADE
"words_games_player1_fkey" FOREIGN KEY (player1) REFERENCES
words_users(uid) ON DELETE CASCADE
"words_games_player2_fkey" FOREIGN KEY (player2) REFERENCES
words_users(uid) ON DELETE CASCADE
Referenced by:
TABLE "words_chat" CONSTRAINT "words_chat_gid_fkey" FOREIGN KEY (gid)
REFERENCES words_games(gid) ON DELETE CASCADE
TABLE "words_moves" CONSTRAINT "words_moves_gid_fkey" FOREIGN KEY (gid)
REFERENCES words_games(gid) ON DELETE CASCADE
TABLE "words_scores" CONSTRAINT "words_scores_gid_fkey" FOREIGN KEY
(gid) REFERENCES words_games(gid) ON DELETE CASCADE
You are probably talking about the section:
Foreign-key constraints:
"words_games_bid_fkey" FOREIGN KEY (bid) REFERENCES words_boards(bid)
ON DELETE CASCADE
"words_games_player1_fkey" FOREIGN KEY (player1) REFERENCES
words_users(uid) ON DELETE CASCADE
"words_games_player2_fkey" FOREIGN KEY (player2) REFERENCES
words_users(uid) ON DELETE CASCADE
The first table words_boards only has 4 records, so I ignore it.
The second table words_users already has an index on the uid, because that
column is the primary key:
# \d words_users
Table "public.words_users"
Column| Type | Collation | Nullable |
Default
-+--+---+--+--
uid | integer | | not null |
nextval('words_users_uid_seq'::regclass)
created | timestamp with time zone | | not null |
visited | timestamp with time zone | | not null |
ip | inet | | not null |
fcm | text | | |
apns| text | | |
adm | text | | |
motto | text | | |
vip_until | timestamp with time zone | | |
grand_until | timestamp with time zone | | |
elo | integer | | not null |
medals | integer | | not null |
Re: Deleting takes days, should I add some index?
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, Alexander Farber wrote:
>>
>> > Referenced by:
>> > TABLE "words_chat" CONSTRAINT "words_chat_gid_fkey" FOREIGN KEY
>> (gid)
>> > REFERENCES words_games(gid) ON DELETE CASCADE
>> > TABLE "words_moves" CONSTRAINT "words_moves_gid_fkey" FOREIGN KEY
>> (gid)
>> > REFERENCES words_games(gid) ON DELETE CASCADE
>> > TABLE "words_scores" CONSTRAINT "words_scores_gid_fkey" FOREIGN KEY
>> > (gid) REFERENCES words_games(gid) ON DELETE CASCADE
>>
>> Make sure you have indexes on the gid columns of these tables. Delete
>> needs to scan them in order to find the rows that are cascaded to.
>>
>>
> An index on words_games(finished) and words_moves(played) would help too.
>
>
and have now the following indices in my database:
CREATE INDEX ON words_games(player1, COALESCE(finished, 'INFINITY'));
CREATE INDEX ON words_games(player2, COALESCE(finished, 'INFINITY'));
CREATE INDEX ON words_games(created),
CREATE INDEX ON words_chat(created),
CREATE INDEX ON words_moves(uid, action, played);
CREATE INDEX ON words_moves(gid, played);
CREATE INDEX ON words_moves(played);
CREATE INDEX ON words_moves(uid);
CREATE INDEX ON words_moves(gid);
CREATE INDEX ON words_social(uid, stamp);
CREATE INDEX ON words_geoip USING SPGIST (block);
CREATE INDEX ON words_scores(LENGTH(word), mid);
-- CREATE INDEX ON words_scores(uid, LENGTH(word) desc);
CREATE INDEX ON words_scores(gid);
CREATE INDEX ON words_scores(uid);
CREATE INDEX ON words_chat(gid);
However the deletion still takes forever and I have to ctrl-c it:
# delete from words_games where created < now() - interval '12 month';
Do you please have any further suggestions?
When I try to prepend "explain analyze" to the above query, then in the
production database it also lasts forever.
In an empty dev database the output does not help much -
# explain analyze delete from words_games where created < now() - interval
'12 month';
QUERY PLAN
Delete on words_games (cost=0.00..40.34 rows=1 width=6) (actual
time=0.132..0.132 rows=0 loops=1)
-> Seq Scan on words_games (cost=0.00..40.34 rows=1 width=6) (actual
time=0.131..0.131 rows=0 loops=1)
Filter: (created < (now() - '1 year'::interval))
Rows Removed by Filter: 137
Planning Time: 0.150 ms
Execution Time: 0.143 ms
(6 rows)
Below are the words_games and the "referenced by" tables -
# \d words_games
Table "public.words_games"
Column | Type | Collation | Nullable |
Default
--+--+---+--+--
gid | integer | | not null |
nextval('words_games_gid_seq'::regclass)
created | timestamp with time zone | | not null |
finished | timestamp with time zone | | |
player1 | integer | | not null |
player2 | integer | | |
played1 | timestamp with time zone | | |
played2 | timestamp with time zone | | |
state1 | text | | |
state2 | text | | |
reason | text | | |
hint1| text | | |
hint2| text | | |
score1 | integer | | not null |
score2 | integer | | not null |
chat1| integer | | not null |
chat2| integer | | not null |
hand1| character(1)[] | | not null |
hand2| character(1)[] | | not null |
pile | character(1)[] | | not null |
letters | character(1)[] | | not null |
values | integer[]| | not null |
bid | integer | | not null |
diff1| integer | | |
diff2| integer | | |
open1| boolean | | not null | false
open2| boolean | | not null | false
Indexes:
"words_games_pkey" PRIMARY KEY, btree (gid)
&qu
Re: JSONB_AGG: aggregate function calls cannot be nested
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')) AS
completed,
count(*) filter (where reason = 'expired') AS expired
FROM words_games
WHERE finished > CURRENT_TIMESTAMP - INTERVAL '2 week'
GROUP BY day
) t
ORDER BY day) x;
row_to_json
--
--
{"day":["2021-02-16", "2021-02-20", "2021-02-10", "2021-02-09",
"2021-02-15", "2021-02-19", "2021-02-17", "2021-02-11", "2021-02-22",
"2021-02-08", "2021-02-
14", "2021-02-21", "2021-02-12", "2021-02-13",
"2021-02-18"],"completed":[744, 802, 864, 770, 767, 745, 837, 792, 751, 32,
843, 808, 838, 853, 751],"expired":
[237, 168, 230, 263, 203, 257, 206, 184, 337, 11, 231, 380, 231, 293, 196]}
(1 row)
Re: JSONB_AGG: aggregate function calls cannot be nested
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,
count(*) filter (where reason = 'expired') AS expired
FROM words_games
WHERE finished > CURRENT_TIMESTAMP - INTERVAL '2 week'
GROUP BY day
) t
GROUP BY day
ORDER BY day;
day | completed | expired
+---+-
2021-02-08 | [481] | [155]
2021-02-09 | [770] | [263]
2021-02-10 | [864] | [230]
2021-02-11 | [792] | [184]
2021-02-12 | [838] | [231]
2021-02-13 | [853] | [293]
2021-02-14 | [843] | [231]
2021-02-15 | [767] | [203]
2021-02-16 | [744] | [237]
2021-02-17 | [837] | [206]
2021-02-18 | [751] | [196]
2021-02-19 | [745] | [257]
2021-02-20 | [802] | [168]
2021-02-21 | [808] | [380]
2021-02-22 | [402] | [255]
(15 rows)
but how to get a JSON map of lists here? I am trying:
select row_to_json (x) FROM (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,
count(*) filter (where reason = 'expired') AS expired
FROM words_games
WHERE finished > CURRENT_TIMESTAMP - INTERVAL '2 week'
GROUP BY day
) t
GROUP BY day
ORDER BY day) x;
row_to_json
{"day":"2021-02-08","completed":[475],"expired":[155]}
{"day":"2021-02-09","completed":[770],"expired":[263]}
{"day":"2021-02-10","completed":[864],"expired":[230]}
{"day":"2021-02-11","completed":[792],"expired":[184]}
{"day":"2021-02-12","completed":[838],"expired":[231]}
{"day":"2021-02-13","completed":[853],"expired":[293]}
{"day":"2021-02-14","completed":[843],"expired":[231]}
{"day":"2021-02-15","completed":[767],"expired":[203]}
{"day":"2021-02-16","completed":[744],"expired":[237]}
{"day":"2021-02-17","completed":[837],"expired":[206]}
{"day":"2021-02-18","completed":[751],"expired":[196]}
{"day":"2021-02-19","completed":[745],"expired":[257]}
{"day":"2021-02-20","completed":[802],"expired":[168]}
{"day":"2021-02-21","completed":[808],"expired":[380]}
{"day":"2021-02-22","completed":[410],"expired":[255]}
(15 rows)
While I would actually need:
{
"day": [ "2021-02-08", "2021-02-09", ... ],
"completed": [ 475, 770, ...],
"expired": [ 155, 263 , ...]
}
And then I could feed the data into the Chart.js shown at the bottom of my
web page https://slova.de/top
Currently I do a simple SELECT query and construct the JSON map of list in
the Java code of my servlet
Thank you
Alex
Re: JSONB_AGG: aggregate function calls cannot be nested
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
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: aggregate functions are not allowed in GROUP BY LINE 2: JSONB_AGG(TO_CHAR(finished, '-MM-DD')) A... ^
JSONB_AGG: aggregate function calls cannot be nested
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 expired FROM words_games WHERE finished > CURRENT_TIMESTAMP - INTERVAL '2 week' GROUP BY day ORDER BY day; day | completed | expired +---+- 2021-02-06 | 167 | 71 2021-02-07 | 821 | 189 2021-02-08 | 816 | 323 2021-02-09 | 770 | 263 2021-02-10 | 864 | 230 2021-02-11 | 792 | 184 2021-02-12 | 838 | 231 2021-02-13 | 853 | 293 2021-02-14 | 843 | 231 2021-02-15 | 767 | 203 2021-02-16 | 744 | 237 2021-02-17 | 837 | 206 2021-02-18 | 751 | 196 2021-02-19 | 745 | 257 2021-02-20 | 654 | 135 (15 rows) It works well, but I would like to transform it into a JSONB map with 3 arrays. So I am trying: # SELECT JSONB_AGG(TO_CHAR(finished, '-MM-DD')) AS day, JSONB_AGG(SUM(CASE WHEN reason='regular' or reason='resigned' THEN 1 ELSE 0 END)::int) AS completed, JSONB_AGG(SUM(CASE WHEN reason='expired' THEN 1 ELSE 0 END)::int) AS expired FROM words_games WHERE finished > CURRENT_TIMESTAMP - INTERVAL '2 week' GROUP BY day ORDER BY day; ERROR: aggregate function calls cannot be nested LINE 3: JSONB_AGG(SUM(CASE WHEN reason='regular' or ... ^ Shouldn't I use JSONB_AGG here, to build the 3 JSON arrays? Or is the syntax error about being able to use JSONB_AGG only once per SELECT query? Greetings Alex
Re: Please help to improve SELECT JOIN performance, EXPLAIN ANALYZE attached
With shared_buffers=16GB, pg_top shows: last pid: 2613; load avg: 0.49, 0.45, 0.37; up 0+00:19:21 16:41:16 16 processes: 16 sleeping CPU states: 9.2% user, 0.0% nice, 0.2% system, 90.4% idle, 0.1% iowait Memory: 21G used, 42G free, 29M buffers, 18G cached DB activity: 39 tps, 0 rollbs/s, 0 buffer r/s, 100 hit%, 3625 row r/s, 11 row w/s DB I/O: 0 reads/s, 0 KB/s, 0 writes/s, 0 KB/s DB disk: 0.0 GB total, 0.0 GB free (100% used) Swap: 32G free PID USERNAME PRI NICE SIZE RES STATE TIME WCPUCPU COMMAND 2114 postgres 200 17G 6378M sleep 2:11 0.77% 44.80% postgres: words words_ru [local] idle 2012 postgres 200 17G 6005M sleep 0:55 0.45% 26.30% postgres: words words_ru [local] idle 2107 postgres 200 17G 6175M sleep 1:16 0.02% 0.97% postgres: words words_ru [local] idle 1184 postgres 200 17G 22M sleep 0:00 0.02% 0.97% postgres: walwriter 2030 postgres 200 17G 5948M sleep 1:18 0.00% 0.00% postgres: words words_ru [local] idle 2013 postgres 200 17G 5724M sleep 0:57 0.00% 0.00% postgres: words words_ru [local] idle 2103 postgres 200 17G 5823M sleep 0:52 0.00% 0.00% postgres: words words_ru [local] idle 2031 postgres 200 17G 5619M sleep 0:41 0.00% 0.00% postgres: words words_ru [local] idle 2029 postgres 200 17G 1128M sleep 0:00 0.00% 0.00% postgres: words words_ru [local] idle 2104 postgres 200 17G 948M sleep 0:00 0.00% 0.00% postgres: words words_ru [local] idle 2106 postgres 200 17G 1257M sleep 0:00 0.00% 0.00% postgres: words words_ru [local] idle 1182 postgres 200 17G 231M sleep 0:00 0.00% 0.00% postgres: checkpointer 1183 postgres 200 17G 135M sleep 0:00 0.00% 0.00% postgres: background writer 1185 postgres 200 17G 8696K sleep 0:00 0.00% 0.00% postgres: autovacuum launcher 2614 postgres 200 17G 22M sleep 0:00 0.00% 0.00% postgres: words words_ru [local] idle 1187 postgres 200 17G 6764K sleep 0:00 0.00% 0.00% postgres: 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
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
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 average show by top is 0.35-0.45. What could be the reason, does PostgreSQL 13.2 has some scheduled task maybe? I have only changed few lines in the postgresql.conf: # diff postgresql.conf.OLD postgresql.conf 64c64 < max_connections = 100 # (change requires restart) --- > max_connections = 120 # (change requires restart) 130c130 < #work_mem = 4MB # min 64kB --- > work_mem = 8MB# min 64kB 132c132 < #maintenance_work_mem = 64MB # min 1MB --- > maintenance_work_mem = 128MB # min 1MB 393c393 < #effective_cache_size = 4GB --- > effective_cache_size = 8GB 410,411c410,411 < #from_collapse_limit = 8 < #join_collapse_limit = 8 # 1 disables collapsing of explicit --- > from_collapse_limit = 24 > join_collapse_limit = 24 # 1 disables collapsing of explicit 469c469 < #log_min_messages = warning # values in order of decreasing detail: --- > log_min_messages = notice # values in order of decreasing detail: 497c497 < #log_min_duration_statement = -1 # -1 is disabled, 0 logs all statements --- > log_min_duration_statement = 1000 # -1 is disabled, 0 logs all statements And I have pgbouncer in front of the PostgreSQL: diff pgbouncer.ini.OLD pgbouncer.ini 12a13,15 > words_de = host=/tmp user=xxx password=xxx dbname=words_de > words_en = host=/tmp user=xxx password=xxx dbname=words_en > words_ru = host=/tmp user=xxx password=xxx dbname=words_ru 115a119 > ;; auth_type = md5 148c152 < ;server_reset_query = DISCARD ALL --- > server_reset_query = DISCARD ALL 156c160 < ;ignore_startup_parameters = extra_float_digits --- > ignore_startup_parameters = extra_float_digits 180c184 < ;max_client_conn = 100 --- > max_client_conn = 1000 185c189 < ;default_pool_size = 20 --- > default_pool_size = 100 Or is maybe everything ok...? The daily gzipped dump grows big: 939343358 Dec 31 01:33 words_ru-Dec.sql.gz 1221682336 Jan 31 01:33 words_ru-Jan.sql.gz 1423324283 Feb 20 01:34 words_ru-Feb.sql.gz and being an inexperienced pg admin I am a bit worried. Thank you Alex >
Please help to improve SELECT JOIN performance, EXPLAIN ANALYZE attached
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 particular
queries have increased, especially in the evenings:
2021-02-19 17:51:19.104 CET [68932] LOG: duration: 2356.723 ms execute
: SELECT words_stat_longest($1::int)
2021-02-19 17:55:23.290 CET [68602] LOG: duration: 2326.507 ms execute
: SELECT words_stat_longest($1::int)
2021-02-19 17:57:57.057 CET [68932] LOG: duration: 1257.773 ms execute
: SELECT out_uidAS uid, out_fcmAS fcm,out_apns AS
apns, out_admAS adm,out_hmsAS hms,out_social AS social,
out_sidAS sid,out_body AS bodyFROM words_play_game($1::int,
$2::int, $3::jsonb)
2021-02-19 18:02:51.359 CET [68603] LOG: duration: 2305.950 ms execute
: SELECT words_stat_longest($1::int)
2021-02-19 18:08:26.130 CET [68932] LOG: duration: 2375.713 ms execute
: SELECT words_stat_longest($1::int)
One of the queries is actually a simple SELECT ... LIMIT 15 to find the 15
longest words played by a user (they are displayed at the player profile
page).
I have run the EXPLAIN ANALYZE here: https://explain.depesz.com/s/IcqN
It is difficult for me to understand, what could be missing there, does
anybody please have an idea?
Thank you for any input
Alex
P.S. Here are the tables involved in the SELECT query, with the
words_moves and words_geoip holding the most records:
# \d words_scores
Table "public.words_scores"
Column | Type | Collation | Nullable | Default
+-+---+--+-
mid| bigint | | not null |
gid| integer | | not null |
uid| integer | | not null |
word | text| | not null |
score | integer | | not null |
Indexes:
"words_scores_gid_idx" btree (gid)
"words_scores_length_mid_idx" btree (length(word) DESC, mid DESC)
"words_scores_uid_idx" btree (uid)
Check constraints:
"words_scores_score_check" CHECK (score >= 0)
"words_scores_word_check" CHECK (word ~ '^[А-Я]{2,}$'::text)
Foreign-key constraints:
"words_scores_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid)
ON DELETE CASCADE
"words_scores_mid_fkey" FOREIGN KEY (mid) REFERENCES words_moves(mid)
ON DELETE CASCADE
"words_scores_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid)
ON DELETE CASCADE
# \d words_moves
Table "public.words_moves"
Column | Type | Collation | Nullable |
Default
-+--+---+--+--
mid | bigint | | not null |
nextval('words_moves_mid_seq'::regclass)
action | text | | not null |
gid | integer | | not null |
uid | integer | | not null |
played | timestamp with time zone | | not null |
tiles | jsonb| | |
score | integer | | |
str | text | | |
hand| text | | |
letters | character(1)[] | | |
values | integer[]| | |
Indexes:
"words_moves_pkey" PRIMARY KEY, btree (mid)
"words_moves_gid_played_idx" btree (gid, played DESC)
"words_moves_uid_action_played_idx" btree (uid, action, played)
"words_moves_uid_idx" btree (uid)
Check constraints:
"words_moves_score_check" CHECK (score >= 0)
Foreign-key constraints:
"words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON
DELETE CASCADE
"words_moves_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON
DELETE CASCADE
Referenced by:
TABLE "words_puzzles" CONSTRAINT "words_puzzles_mid_fkey" FOREIGN KEY
(mid) REFERENCES words_moves(mid) ON DELETE CASCADE
TABLE "words_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY
(mid) REFERENCES words_moves(mid) ON DELETE CASCADE
# \d words_geoip
Table "public.words_geoip"
Column | Type | Collation | Nullable | Default
+--+---+--+-
block | inet | | not null |
lat| double precision | | |
lng| double precision | | |
Indexes:
"words_geoip_pkey" PRIMARY KEY, btree (block)
# \d words_games
Table "public.words_games"
Column | Type | Collation | Nullable |
Default
--+--+---+--+--
gid | integer | | not
Re: How to return a jsonb list of lists (with integers)
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 1 ELSE 0
END)::integer,
SUM(CASE WHEN (player1 = in_viewer AND state1 =
'lost') OR (player2 = in_viewer AND state2 = 'lost') THEN 1 ELSE 0
END)::integer,
SUM(CASE WHEN (player1 = in_viewer AND state1 =
'draw') OR (player2 = in_viewer AND state2 = 'draw') THEN 1 ELSE 0
END)::integer
)
FROM words_games
WHERE finished IS NOT NULL
AND in_viewer IN (player1, player2)
AND in_uid IN (player1, player2)
INTO _versus;
IF FOUND THEN -- for some reason this is always
true
out_data := JSONB_INSERT(out_data, '{versus}',
_versus);
END IF;
What works for me is:
IF _versus <> '[null,null,null]'::jsonb THEN
out_data := JSONB_INSERT(out_data, '{versus}',
_versus);
END IF;
Greetings
Alex
Re: How to return a jsonb list of lists (with integers)
Hi Pavel, why would SELECT INTO be better here? Thanks Alex
Re: How to return a jsonb list of lists (with integers)
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 0
END)::integer,
SUM(CASE WHEN (player1 = in_uid AND state1 =
'lost') OR (player2 = in_uid AND state2 = 'lost') THEN 1 ELSE 0
END)::integer,
SUM(CASE WHEN (player1 = in_uid AND state1 =
'draw') OR (player2 = in_uid AND state2 = 'draw') THEN 1 ELSE 0
END)::integer
)
FROM words_games
WHERE finished IS NOT NULL
AND (
(player1 = in_uid AND player2 = in_opponent) OR
(player2 = in_uid AND player1 = in_opponent)
);
IF _versus <> '[null, null, null]'::jsonb THEN
out_data := JSONB_INSERT(out_data, '{versus}',
_versus);
END IF;
Re: How to return a jsonb list of lists (with integers)
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,
SUM(CASE WHEN (player1 = in_uid AND state1 =
'lost') OR (player2 = in_uid AND state2 = 'lost') THEN 1 ELSE 0
END)::integer,
SUM(CASE WHEN (player1 = in_uid AND state1 =
'draw') OR (player2 = in_uid AND state2 = 'draw') THEN 1 ELSE 0
END)::integer
)
FROM words_games
WHERE finished IS NOT NULL
AND (
(player1 = in_uid AND player2 = in_opponent) OR
(player2 = in_uid AND player1 = in_opponent)
);
IF FOUND THEN
out_data := JSONB_INSERT(out_data, '{versus}',
_versus);
END IF;
Re: How to return a jsonb list of lists (with integers)
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(
in_uid integer,
in_opponent integer, -- optional parameter, can be NULL
OUT out_data jsonb
) RETURNS jsonb AS
$func$
BEGIN
out_data := JSONB_BUILD_OBJECT();
-- add a JSON list with 7 integers
out_data := JSONB_INSERT(out_data, '{length}', JSONB_BUILD_ARRAY(
SUM(CASE WHEN LENGTH(word) = 2 THEN 1 ELSE 0 END)::integer,
SUM(CASE WHEN LENGTH(word) = 3 THEN 1 ELSE 0 END)::integer,
SUM(CASE WHEN LENGTH(word) = 4 THEN 1 ELSE 0 END)::integer,
SUM(CASE WHEN LENGTH(word) = 5 THEN 1 ELSE 0 END)::integer,
SUM(CASE WHEN LENGTH(word) = 6 THEN 1 ELSE 0 END)::integer,
SUM(CASE WHEN LENGTH(word) = 7 THEN 1 ELSE 0 END)::integer,
SUM(CASE WHEN LENGTH(word) > 7 THEN 1 ELSE 0 END)::integer
))
-- add a JSON list with 3 integers
FROM words_scores WHERE uid = in_uid;
out_data := JSONB_INSERT(out_data, '{results}', 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 = in_uid AND state1 = 'lost') OR
(player2 = in_uid AND state2 = 'lost') THEN 1 ELSE 0 END)::integer,
SUM(CASE WHEN (player1 = in_uid AND state1 = 'draw') OR
(player2 = in_uid AND state2 = 'draw') THEN 1 ELSE 0 END)::integer
))
FROM words_games
WHERE finished IS NOT NULL
AND in_uid IN (player1, player2);
-- add a JSON list with 3 integers, but only if in_opponent param
is supplied
IF in_opponent > 0 AND in_opponent <> in_uid THEN
out_data := JSONB_INSERT(out_data, '{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,
SUM(CASE WHEN (player1 = in_uid AND state1 =
'lost') OR (player2 = in_uid AND state2 = 'lost') THEN 1 ELSE 0
END)::integer,
SUM(CASE WHEN (player1 = in_uid AND state1 =
'draw') OR (player2 = in_uid AND state2 = 'draw') THEN 1 ELSE 0
END)::integer
))
FROM words_games
WHERE finished IS NOT NULL
AND (
(player1 = in_uid AND player2 = in_opponent) OR
(player2 = in_uid AND player1 = in_opponent)
);
END IF;
END
$func$ LANGUAGE plpgsql;
The function works well:
# select * from words_stat_charts(5, 6);
out_data
---
{"length": [2726, 2825, 2341, 1363, 394, 126, 68], "versus": [6, 3, 0],
"results": [298, 151, 0]}
(1 row)
Except when 2 players never played with each other - then I get [ null,
null, null ]:
# select * from words_stat_charts(5, 1);
out_data
{"length": [2726, 2825, 2341, 1363, 394, 126, 68], "versus": [null, null,
null], "results": [298, 151, 0]}
(1 row)
Is there maybe a nice trick to completely omit "versus" from the returned
JSONB map of lists when its [ null, null, null ]?
Thank you
Alex
Re: How to return a jsonb list of lists (with integers)
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 = in_uid AND state1 = 'lost') OR (player2 = in_uid AND state2 = 'lost') THEN 1 ELSE 0 END)::integer, SUM(CASE WHEN (player1 = in_uid AND state1 = 'draw') OR (player2 = in_uid AND state2 = 'draw') THEN 1 ELSE 0 END)::integer ) FROM words_games WHERE finished IS NOT NULL AND in_uid IN (player1, player2); But is it possible in SQL to combine all 3 queries, so that a JSONB list of lists is returned? I cannot use a UNION, because the first two queries return 3 columns, but the last query returns 7 columns. So I have to use PL/PgSQL, correct? Best regards Alex
Re: How to return a jsonb list of lists (with integers)
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, Michael, thanks - that is what I realised just after sending the mail. I don't have rows here, but a single row with several columns.
How to return a jsonb list of lists (with integers)
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 to 1 function, in SQL or if not possible, then PL/pgSQL. The new function should return a JSONB list containing 3 other lists, i.e. something like: [ [0,0,0], [0,0,0], [0,0,0,0,0,0,0] ] I think I should use the aggregate function jsonb_agg(). But I can't figure out how to apply it to the 3 queries below, could you please help me? CREATE OR REPLACE FUNCTION words_stat_charts( in_uid integer, in_opponent integer ) RETURNS jsonb AS $func$ -- how to return [ [0,0,0], [0,0,0], [0,0,0,0,0,0,0] ] ? SELECT 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 = in_uid AND state1 = 'lost') OR (player2 = in_uid AND state2 = 'lost') THEN 1 ELSE 0 END)::integer, SUM(CASE WHEN (player1 = in_uid AND state1 = 'draw') OR (player2 = in_uid AND state2 = 'draw') THEN 1 ELSE 0 END)::integer FROM words_games WHERE finished IS NOT NULL AND in_uid IN (player1, player2); SELECT 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 = in_uid AND state1 = 'lost') OR (player2 = in_uid AND state2 = 'lost') THEN 1 ELSE 0 END)::integer, SUM(CASE WHEN (player1 = in_uid AND state1 = 'draw') OR (player2 = in_uid AND state2 = 'draw') THEN 1 ELSE 0 END)::integer FROM words_games WHERE finished IS NOT NULL AND ( (player1 = in_uid AND player2 = in_opponent) OR (player2 = in_uid AND player1 = in_opponent) ); SELECT SUM(CASE WHEN LENGTH(word) = 2 THEN 1 ELSE 0 END)::integer, SUM(CASE WHEN LENGTH(word) = 3 THEN 1 ELSE 0 END)::integer, SUM(CASE WHEN LENGTH(word) = 4 THEN 1 ELSE 0 END)::integer, SUM(CASE WHEN LENGTH(word) = 5 THEN 1 ELSE 0 END)::integer, SUM(CASE WHEN LENGTH(word) = 6 THEN 1 ELSE 0 END)::integer, SUM(CASE WHEN LENGTH(word) = 7 THEN 1 ELSE 0 END)::integer, SUM(CASE WHEN LENGTH(word) > 7 THEN 1 ELSE 0 END)::integer FROM words_scores WHERE uid = in_uid; $func$ LANGUAGE sql STABLE; When I try simply wrapping the jsonb_agg() around the 3 columns in the first query I get the syntax error: SELECT JSONB_AGG( 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 = in_uid AND state1 = 'lost') OR (player2 = in_uid AND state2 = 'lost') THEN 1 ELSE 0 END)::integer, SUM(CASE WHEN (player1 = in_uid AND state1 = 'draw') OR (player2 = in_uid AND state2 = 'draw') THEN 1 ELSE 0 END)::integer ) FROM words_games WHERE finished IS NOT NULL AND in_uid IN (player1, player2); ERROR: function jsonb_agg(integer, integer, integer) does not exist LINE 8: JSONB_AGG( ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. Thank you for any hints Alex
Re: Select a column and then apply JSONB_ARRAY_ELEMENTS to it
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 bigint, in_uid int, in_guess text, OUT out_json jsonb ) RETURNS jsonb AS $func$ DECLARE _tilejsonb; _letter char; _value integer; BEGIN in_guess := UPPER(in_guess); FOR _tile IN SELECT * FROM JSONB_ARRAY_ELEMENTS((SELECT tiles FROM words_moves WHERE mid = in_mid)) LOOP _letter := _tile->>'letter'; _value := (_tile->>'value')::int; -- verify that all played tiles except wildcard are found in the suggested answer IF _value > 0 AND POSITION(_letter IN in_guess) = 0 THEN out_json := json_build_object( 'label',' Keep guessing!' ); RETURN; END IF; END LOOP; -- check if the in_guess is one of the played words in that move IF NOT EXISTS(SELECT 1 FROM words_scores WHERE mid = in_mid AND word = in_guess) THEN out_json := json_build_object( 'label',' Wrong!' ); RETURN; END IF; -- the solution already submitted, just ack, but do not award coins IF EXISTS (SELECT 1 FROM words_puzzles WHERE mid = in_mid AND uid = in_uid) THEN out_json := json_build_object( 'label',' Correct!', 'url', '/ws/puzzle2?mid=' || in_mid || '=' || MD5(in_mid || 'my secret') ); RETURN; END IF; -- save the puzzle solution and award coins to the user INSERT INTO words_puzzles (mid, uid, solved) VALUES (in_mid, in_uid, CURRENT_TIMESTAMP); UPDATE words_users SET coins = coins + 1 WHERE uid = in_uid; out_json := json_build_object( 'label',' Correct, +1 coin!', 'url', '/ws/puzzle2?mid=' || in_mid || '=' || MD5(in_mid || 'my secret') ); END $func$ LANGUAGE plpgsql; P.S. 'my secret' is not my real secret passphrase :-)
Select a column and then apply JSONB_ARRAY_ELEMENTS to it
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,
in_answertext,
OUT out_json jsonb
) RETURNS jsonb AS
$func$
DECLARE
_tilejsonb;
_letter char;
_value integer;
_answer text;
BEGIN
FOR _tile IN SELECT * FROM JSONB_ARRAY_ELEMENTS(SELECT tiles FROM
words_moves WHERE mid = in_mid)
LOOP
_letter := _tile->>'letter';
_value := (_tile->>'value')::int;
RAISE NOTICE 'Tile % letter % value', _tile, _letter,
_value;
END LOOP;
However this results in the error message -
ERROR: 42601: syntax error at or near "SELECT"
LINE 24: ... FOR _tile IN SELECT * FROM JSONB_ARRAY_ELEMENTS(SELECT til...
^
LOCATION: scanner_yyerror, scan.l:1180
Could you please help me, how to combine SELECT query and the LOOP
through JSONB_ARRAY_ELEMENTS here?
Thank you
Alex
P.S: Here the table:
words_de=> \d words_moves
Table "public.words_moves"
Column | Type | Collation | Nullable |
Default
-+--+---+--+--
mid | bigint | | not null |
nextval('words_moves_mid_seq'::regclass)
action | text | | not null |
gid | integer | | not null |
uid | integer | | not null |
played | timestamp with time zone | | not null |
tiles | jsonb| | |
score | integer | | |
str | text | | |
hand| text | | |
letters | character(1)[] | | |
values | integer[]| | |
Indexes:
"words_moves_pkey" PRIMARY KEY, btree (mid)
"words_moves_gid_played_idx" btree (gid, played DESC)
"words_moves_uid_action_played_idx" btree (uid, action, played)
"words_moves_uid_idx" btree (uid)
Check constraints:
"words_moves_score_check" CHECK (score >= 0)
Foreign-key constraints:
"words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON
DELETE CASCADE
"words_moves_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON
DELETE CASCADE
Referenced by:
TABLE "words_puzzles" CONSTRAINT "words_puzzles_mid_fkey" FOREIGN KEY
(mid) REFERENCES words_moves(mid) ON DELETE CASCADE
TABLE "words_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY
(mid) REFERENCES words_moves(mid) ON DELETE CASCADE
Re: SELECT but only if not present in another table
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 for me!
SELECT but only if not present in another table
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
-+--+---+--+--
mid | bigint | | not null |
nextval('words_moves_mid_seq'::regclass)
action | text | | not null |
gid | integer | | not null |
uid | integer | | not null |
played | timestamp with time zone | | not null |
tiles | jsonb| | |
score | integer | | |
str | text | | |
hand| text | | |
letters | character(1)[] | | |
values | integer[]| | |
Indexes:
"words_moves_pkey" PRIMARY KEY, btree (mid)
"words_moves_gid_played_idx" btree (gid, played DESC)
"words_moves_puzzle_idx" btree (puzzle)
"words_moves_uid_action_played_idx" btree (uid, action, played)
"words_moves_uid_idx" btree (uid)
Check constraints:
"words_moves_score_check" CHECK (score >= 0)
Foreign-key constraints:
"words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON
DELETE CASCADE
"words_moves_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON
DELETE CASCADE
Referenced by:
TABLE "words_puzzles" CONSTRAINT "words_puzzles_mid_fkey" FOREIGN KEY
(mid) REFERENCES words_moves(mid) ON DELETE CASCADE
TABLE "words_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY
(mid) REFERENCES words_moves(mid) ON DELETE CASCADE
Some of the moves can be "interesting" in the sense that the player have
used all 7 letter tiles or achieved a high score over 90 points,
I want to display those moves as "puzzles" and have prepared a table to
store, per-user, who has solved them:
# \d words_puzzles
Table "public.words_puzzles"
Column | Type | Collation | Nullable | Default
+--+---+--+-
mid| bigint | | not null |
uid| integer | | not null |
solved | timestamp with time zone | | not null |
Foreign-key constraints:
"words_puzzles_mid_fkey" FOREIGN KEY (mid) REFERENCES words_moves(mid)
ON DELETE CASCADE
"words_puzzles_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid)
ON DELETE CASCADE
Now I am trying to create a custom stored function which would return just
one mid (move id) which is not too new (1 year old) and the user has not
tackled it yet:
CREATE OR REPLACE FUNCTION words_daily_puzzle(
in_uid int
) RETURNS table (
out_mid bigint,
out_secret text
) AS
$func$
SELECT
mid,
MD5(mid ||'my little secret')
FROM words_moves
WHERE action = 'play'
AND (LENGTH(str) = 7 OR score > 90)
AND played BETWEEN CURRENT_TIMESTAMP - INTERVAL '51 week' AND
CURRENT_TIMESTAMP - INTERVAL '50 week'
-- the user has not solved this puzzle yet
AND NOT EXISTS (SELECT 1 FROM words_puzzles WHERE mid =
the_outer_mid AND uid = in_uid)
ORDER BY PLAYED ASC
LIMIT 1;
$func$ LANGUAGE sql;
As you can see I am missing 1 piece - how do I address the outer SELECT mid
from the EXISTS-SELECT?
I have written "the_outer_mid" there.
Should I use LEFT JOIN LATERAL here? I have difficulties wrapping my head
around this.
TLDR: how to return 1 mid from 1 year ago, which is not solved by the user
in_uid yet?
Thank you
Alex
Re: Using a boolean column with IF / THEN
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
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 wonder if this is the best way with PL/pgSQL - IF EXISTS (SELECT 1 FROM words_users WHERE uid = _uid AND muted) THEN RAISE EXCEPTION 'User % is muted', _uid; END IF; Or can this be done in a simpler way? Thanks Alex P.S. Here my entire stored function: CREATE OR REPLACE FUNCTION words_set_motto( in_social integer, in_sidtext, in_auth text, in_motto text ) RETURNS integer AS $func$ DECLARE _uid integer; BEGIN IF NOT words_valid_user(in_social, in_sid, in_auth) THEN RAISE EXCEPTION 'Invalid user = % %', in_social, in_sid; END IF; _uid := (SELECT uid FROM words_social WHERE social = in_social AND sid = in_sid); IF LENGTH(in_motto) > 250 THEN RAISE EXCEPTION 'Invalid motto by user %', _uid; END IF; IF EXISTS (SELECT 1 FROM words_users WHERE uid = _uid AND muted) THEN RAISE EXCEPTION 'User % is muted', _uid; END IF; IF (SELECT COUNT(NULLIF(nice, 0)) - COUNT(NULLIF(nice, 1)) FROM words_reviews WHERE uid = _uid) < -20 THEN RAISE EXCEPTION 'User % can not change motto', _uid; END IF; UPDATE words_users SET motto = in_motto WHERE uid = _uid; RETURN _uid; END $func$ LANGUAGE plpgsql;
[CentOS] CentOS 8.2: error running non-shared postrotate script for /var/log/mysql/mysqld.log
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 mysql-server-8.0.21-1.module_el8.2.0+493+63b41e36.x86_64 Then I have run mysql_secure_installation and among other things set the root password for MySQL As result I am greeted with the following anachron mail every morning: /etc/cron.daily/logrotate: mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: NO)' error: error running non-shared postrotate script for /var/log/mysql/mysqld.log of '/var/log/mysql/mysqld.log ' I understand that the reason is me having set the root password for MySQL. But my question is how to provide the password to postrotate without disclosing it too much? Other than that the MySQL works well, I use it to host 3 Wordpress websites at my CentOS 8 Linux server (haproxy -> Jetty x 3 -> FastCGI -> php-fpm -> Wordpress -> MySQL) Greetings from Germany Alex ___ CentOS mailing list CentOS@centos.org https://lists.centos.org/mailman/listinfo/centos
Deleting takes days, should I add some index?
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
--+--+---+--+--
gid | integer | | not null |
nextval('words_games_gid_seq'::regclass)
created | timestamp with time zone | | not null |
finished | timestamp with time zone | | |
player1 | integer | | not null |
player2 | integer | | |
played1 | timestamp with time zone | | |
played2 | timestamp with time zone | | |
state1 | text | | |
state2 | text | | |
reason | text | | |
hint1| text | | |
hint2| text | | |
score1 | integer | | not null |
score2 | integer | | not null |
chat1| integer | | not null |
chat2| integer | | not null |
hand1| character(1)[] | | not null |
hand2| character(1)[] | | not null |
pile | character(1)[] | | not null |
letters | character(1)[] | | not null |
values | integer[]| | not null |
bid | integer | | not null |
diff1| integer | | |
diff2| integer | | |
Indexes:
"words_games_pkey" PRIMARY KEY, btree (gid)
"words_games_player1_coalesce_idx" btree (player1, COALESCE(finished,
'infinity'::timestamp with time zone))
"words_games_player2_coalesce_idx" btree (player2, COALESCE(finished,
'infinity'::timestamp with time zone))
Check constraints:
"words_games_chat1_check" CHECK (chat1 >= 0)
"words_games_chat2_check" CHECK (chat2 >= 0)
"words_games_check" CHECK (player1 <> player2)
"words_games_score1_check" CHECK (score1 >= 0)
"words_games_score2_check" CHECK (score2 >= 0)
Foreign-key constraints:
"words_games_bid_fkey" FOREIGN KEY (bid) REFERENCES words_boards(bid)
ON DELETE CASCADE
"words_games_player1_fkey" FOREIGN KEY (player1) REFERENCES
words_users(uid) ON DELETE CASCADE
"words_games_player2_fkey" FOREIGN KEY (player2) REFERENCES
words_users(uid) ON DELETE CASCADE
Referenced by:
TABLE "words_chat" CONSTRAINT "words_chat_gid_fkey" FOREIGN KEY (gid)
REFERENCES words_games(gid) ON DELETE CASCADE
TABLE "words_moves" CONSTRAINT "words_moves_gid_fkey" FOREIGN KEY (gid)
REFERENCES words_games(gid) ON DELETE CASCADE
TABLE "words_scores" CONSTRAINT "words_scores_gid_fkey" FOREIGN KEY
(gid) REFERENCES words_games(gid) ON DELETE CASCADE
words_ru=> \d words_moves
Table "public.words_moves"
Column | Type | Collation | Nullable |
Default
-+--+---+--+--
mid | bigint | | not null |
nextval('words_moves_mid_seq'::regclass)
action | text | | not null |
gid | integer | | not null |
uid | integer | | not null |
played | timestamp with time zone | | not null |
tiles | jsonb| | |
score | integer | | |
str | text | | |
hand| text | | |
puzzle | boolean | | not null | false
letters | character(1)[] | | |
values | integer[]| | |
Indexes:
"words_moves_pkey" PRIMARY KEY, btree (mid)
"words_moves_gid_played_idx" btree (gid, played DESC)
"words_moves_puzzle_idx" btree (puzzle)
"words_moves_uid_action_played_idx" btree (uid, action, played)
"words_moves_uid_idx" btree (uid)
Check constraints:
"words_moves_score_check" CHECK (score >= 0)
Foreign-key constraints:
"words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON
DELETE CASCADE
"words_moves_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON
DELETE CASCADE
Referenced by:
TABLE "words_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY
(mid) REFERENCES words_moves(mid) ON DELETE CASCADE
My word game is published since beginning of 2018 and I have that many
entries there:
words_ru=> select
[jetty-users] Is it necessary to close session in WebSocketConnectionListener.onWebSocketError ?
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, do I have to call mSession.close() there myself? Or is it closed already? And another question please - is onWebSocketClose called, when I call mSession.close()? Best regards Alex ___ jetty-users mailing list jetty-users@eclipse.org To unsubscribe from this list, visit https://www.eclipse.org/mailman/listinfo/jetty-users
Re: [CentOS] certbot stopped working on CentOS 7: pyOpenSSL module missing required functionality
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
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/
raspasy.de/privkey.pem > /etc/letsencrypt/live/raspasy.de/haproxy.pem;
systemctl resstart haproxy"
(I run a post hook, because haproxy-1.5.18-9.el7.x86_64 from the CentOS
packages wants to have the cert and the key in one file).
Unfortunately, now certbot-1.7.0-1.el7.noarch has stopped working and the
error message is:
/usr/lib/python2.7/site-packages/josepy/util.py:9:
CryptographyDeprecationWarning: Python 2 is no longer supported by the
Python core team. Support for it is now deprecated in cryptography, and
will be removed in a future release.
from cryptography.hazmat.primitives.asymmetric import rsa
Traceback (most recent call last):
File "/usr/bin/certbot", line 9, in
load_entry_point('certbot==1.7.0', 'console_scripts', 'certbot')()
File "/usr/lib/python2.7/site-packages/pkg_resources.py", line 378, in
load_entry_point
return get_distribution(dist).load_entry_point(group, name)
File "/usr/lib/python2.7/site-packages/pkg_resources.py", line 2566, in
load_entry_point
return ep.load()
File "/usr/lib/python2.7/site-packages/pkg_resources.py", line 2260, in
load
entry = __import__(self.module_name, globals(),globals(), ['__name__'])
File "/usr/lib/python2.7/site-packages/certbot/main.py", line 2, in
from certbot._internal import main as internal_main
File "/usr/lib/python2.7/site-packages/certbot/_internal/main.py", line
20, in
from certbot._internal import account
File "/usr/lib/python2.7/site-packages/certbot/_internal/account.py",
line 18, in
from acme.client import ClientBase # pylint: disable=unused-import
File "/usr/lib/python2.7/site-packages/acme/client.py", line 39, in
requests.packages.urllib3.contrib.pyopenssl.inject_into_urllib3() #
type: ignore
File "/usr/lib/python2.7/site-packages/urllib3/contrib/pyopenssl.py",
line 118, in inject_into_urllib3
_validate_dependencies_met()
File "/usr/lib/python2.7/site-packages/urllib3/contrib/pyopenssl.py",
line 160, in _validate_dependencies_met
"'pyOpenSSL' module missing required functionality. "
ImportError: 'pyOpenSSL' module missing required functionality. Try
upgrading to v0.14 or newer.
I have the following python packages installed:
# rpm -qa | grep python2
python2-oauthlib-2.0.1-8.el7.noarch
python2-futures-3.1.1-5.el7.noarch
python2-cryptography-1.7.2-2.el7.x86_64
python2-pip-8.1.2-14.el7.noarch
python2-acme-1.7.0-1.el7.noarch
python2-psycopg2-2.8.6-1.rhel7.x86_64
python2-certbot-1.7.0-1.el7.noarch
python2-pyrfc3339-1.1-3.el7.noarch
python2-distro-1.2.0-3.el7.noarch
python2-configargparse-0.11.0-2.el7.noarch
python2-josepy-1.3.0-2.el7.noarch
python2-pyasn1-0.1.9-7.el7.noarch
python2-six-1.9.0-0.el7.noarch
python2-parsedatetime-2.4-6.el7.noarch
python2-future-0.18.2-2.el7.noarch
python2-requests-oauthlib-0.8.0-5.el7.noarch
python2-mock-1.0.1-10.el7.noarch
Does anybody please have an idea, what could I do?
I like to use the stock packages only (for easier maintenance), wonder if a
solution is still possible there...
Thank you for any suggestions
Alex
___
CentOS mailing list
CentOS@centos.org
https://lists.centos.org/mailman/listinfo/centos
Re: How to use avro-python3 on Windows 10 to parse files?
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 fastavro modules on a different avro file and both worked. I will look at PySpark as well.
Re: How to use avro-python3 on Windows 10 to parse files?
Hi Michael -
On Mon, Sep 28, 2020 at 8:19 PM Michael A. Smith
wrote:
> Where did you find that avro-python3 is recommended? I would like to
> update that.
>
here: https://stackoverflow.com/a/43606979/165071
> avro-python3 is deprecated. Your should use the avro library 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
>> the following script:
>>
>> import os, avro
>> from avro.datafile import DataFileReader, DataFileWriter
>> from avro.io import DatumReader, DatumWriter
>>
>> reader = DataFileReader(open("48.avro", "rb"), DatumReader())
>> for d in reader:
>> print(d)
>> reader.close()
>>
>> Unfortunately, nothing is printed by the script.
>>
>> https://stackoverflow.com/questions/64105500/how-to-use-avro-python3-on-windows-10-to-parse-files
>>
>
Michael, how could I debug this issue? I am an avro and python newbie
Greetings from Germany
Alex
How to use avro-python3 on Windows 10 to parse files?
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"), DatumReader())
for d in reader:
print(d)
reader.close()
Unfortunately, nothing is printed by the script.
Then I have noticed, that the avro-python3 is recommended and have
installed it with pip install avro-python3
Now the pip list shows both packages, but how to switch to using the newer
one?
I have tried "pip uninstall avro", but then import avro fails.
What is the correct module name for avro-python3, how to import it in my
script please?
I have also asked my question at Stackoverflow and there you can see my
screenshots:
https://stackoverflow.com/questions/64105500/how-to-use-avro-python3-on-windows-10-to-parse-files
Thank you for any hints
Alex
Re: How to call JSONB_INSERT with integer as the new to-be-inserted value?
Thank you!
How to call JSONB_INSERT with integer as the new to-be-inserted value?
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$
DECLARE
_user jsonb;
_uidinteger;
_banned boolean;
_removedboolean;
BEGIN
-- in_users must be a JSON array with at least 1 element
IF in_users IS NULL OR JSONB_ARRAY_LENGTH(in_users) = 0 THEN
RAISE EXCEPTION 'Invalid users = %', in_users;
END IF;
-- ensure that every record has a valid auth
FOR _user IN SELECT * FROM JSONB_ARRAY_ELEMENTS(in_users)
LOOP
IF NOT words_valid_user((_user->>'social')::int,
_user->>'sid',
_user->>'auth') THEN
RAISE EXCEPTION 'Invalid user = %', _user;
END IF;
IF out_user IS NULL THEN
SELECT
uid,
u.banned_until > CURRENT_TIMESTAMP,
u.removed
INTO STRICT
_uid,
_banned,
_removed
FROM words_social s
LEFT JOIN words_users u USING(uid)
WHERE s.social = (_user->>'social')::int
AND s.sid = _user->>'sid';
IF _banned THEN
RAISE EXCEPTION 'Banned user = %', _user;
END IF;
IF _removed THEN
RAISE EXCEPTION 'Removed user = %', _user;
END IF;
out_user := JSONB_INSERT(_user, '{uid}', _uid);
END IF;
END LOOP;
END
$func$ LANGUAGE plpgsql;
Unfortunately, when I run my stored function it fails:
words_en=> select out_user from
words_get_user('[{"given":"Abcde1","social":1,"auth":"xxx","stamp":1480237061,"sid":"a","photo":"
https://vk.com/images/camera_200.png
"},{"given":"Abcde2","social":2,"auth":"xxx","stamp":1477053188,"sid":"a"},{"given":"Abcde3","social":3,"auth":"xxx","stamp":1477053330,"sid":"a"}]'::jsonb);
ERROR: function jsonb_insert(jsonb, unknown, integer) does not exist
LINE 1: SELECT JSONB_INSERT(_user, '{uid}', _uid)
^
HINT: No function matches the given name and argument types. You might
need to add explicit type casts.
QUERY: SELECT JSONB_INSERT(_user, '{uid}', _uid)
CONTEXT: PL/pgSQL function words_get_user(jsonb) line 44 at assignment
What is missing here please?
Thank you
Alex
[jetty-users] Primary script unknown with 9.4.30.v20200611
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.http.port=8083 and often see the warnings in the logs: Jul 13 20:07:19 www java[28578]: 2020-07-13 20:07:19.314:INFO:oejfch.HttpConnectionOverFCGI:qtp870698190-12: Primary script unknown| Jul 13 20:07:19 www java[28578]: 2020-07-13 20:07:19.584:INFO:oejfch.HttpConnectionOverFCGI:qtp870698190-18: Primary script unknown| Jul 13 20:07:35 www java[28578]: 2020-07-13 20:07:35.494:INFO:oejfch.HttpConnectionOverFCGI:qtp870698190-20: Primary script unknown| Jul 13 20:07:35 www java[28578]: 2020-07-13 20:07:35.622:INFO:oejfch.HttpConnectionOverFCGI:qtp870698190-12: Primary script unknown| Jul 13 20:07:40 www java[28578]: 2020-07-13 20:07:40.965:INFO:oejfch.HttpConnectionOverFCGI:qtp870698190-13: Primary script unknown| Jul 13 20:07:41 www java[28578]: 2020-07-13 20:07:41.140:INFO:oejfch.HttpConnectionOverFCGI:qtp870698190-11: Primary script unknown| What could be the reason please? The https://serverfault.com/a/517327/61603 suggests that SCRIPT_FILENAME would be wrong - but I do not set it anywhere or even use FastCGI (it is just enabled, but I do not use it in the Jetty instance) Best regards Alex ___ jetty-users mailing list jetty-users@eclipse.org To unsubscribe from this list, visit https://www.eclipse.org/mailman/listinfo/jetty-users
[jetty-users] Setting Content-Type: text/html; charset=utf-8 for all html files
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 jetty.http.port=8080 Also I serve PHP files through FastCGI as suggested at https://www.eclipse.org/jetty/documentation/9.4.28.v20200408/configuring-fastcgi.html My XML config file is listed at the very bottom of this mail. However I have a problem: when I create a static HTML file in UTF-8 encoding, with no BOM, Russian language - it is misinterpreted by browsers and they do not display Russian letters. When I rename the file. from .html to .php then it works - because the following HTTP header is suddenly added: Content-Type: text/html;charset=utf-8 Is there please a way to configure Jetty through the XML file below to send the header for all HTML files? I have searched Stackoverflow and the question seems to be complicated because of the servlet spec. I would prefer finding an XML option, because it would suit my deployment and Jetty-upgrades procedure best (i.e. no editing the files distributed with new Jetty versions, but better just configuring it once in $JETTY_BASE/webapps) Thank you and happy Easter :-) Alex PS: below is my /var/www/jetty-base-ru/webapps/slova.de.xml, how to add the header there? ?xml version="1.0" encoding="UTF-8"?> http://www.eclipse.org/jetty/configure_9_3.dtd;> /var/www/html/slova.de / slova.de www.slova.de index.php index.html org.eclipse.jetty.fcgi.server.proxy.TryFilesFilter /* files $path /index.php?p=$path default org.eclipse.jetty.servlet.DefaultServlet dirAllowed false gzip true / org.eclipse.jetty.fcgi.server.proxy.FastCGIProxyServlet *.php proxyTo http://localhost:9000 prefix / scriptRoot scriptPattern (.+?\\.php) ___ 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] Configuring Jetty for FastCGI - and pass env. vars to PHP scripts
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? > > ___ 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] Configuring Jetty for FastCGI - and pass env. vars to PHP scripts
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) {
> super.customizeFastCGIHeaders(proxyRequest, fastCGIHeaders);
> fastCGIHeaders.put("COUNTRY", System.getenv("COUNTRY"));
> ...
> }
> }
Thank you - I have created
https://github.com/eclipse/jetty.project/issues/4735
Your suggestion for creating a custom FarberFCGIServlet - is it to set
$_SERVER['COUNTRY'] inside the PHP scripts? Because currently that variable
is not there. Or how would I be able to retrieve the fastCGIHeaders from
PHP?
And also - how to add the custom FarberFCGIServlet to the Jetty instances,
which are currently being run by the command below?
ExecStart=/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-en jetty.http.host=127.0.0.1
jetty.http.port=8082
Best regards
Alex
___
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] How to include another web page in a page served by Jetty?
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 https://www.eclipse.org/mailman/listinfo/jetty-users
[jetty-users] Configuring Jetty for FastCGI - and pass env. vars to PHP scripts
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 https://www.eclipse.org/jetty/documentation/current/configuring-fastcgi.html And the Jetty instances are started by /etc/systemd/system/jetty-en.service (and jetty-de.service at port 8081, jetty-ru.service at 8080): [Unit] Description=Jetty After=network-online.target [Service] Environment=COUNTRY=en Environment=DOMAIN=wordsbyfarber.com Environment=DATABASE_URL=jdbc:postgresql:// 127.0.0.1:6432/my_database_en?user=my_user_en=my_user_password Environment=FCM_SECRET= Environment=AMAZON_ID= Environment=AMAZON_SECRET= Environment=FACEBOOK_ID= Environment=FACEBOOK_SECRET= Type=simple User=jetty Group=jetty ExecStart=/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-en jetty.http.host=127.0.0.1 jetty.http.port=8082 SuccessExitStatus=143 Restart=always RestartSec=180 PrivateTmp=true [Install] WantedBy=multi-user.target As you can see above I pass some information through the env. variables to the custom Java servlet of my word game. The above stuff works well, but now I am also trying to pass some configuration information to the few PHP scripts I am also running - and unfortunately this does not work - when I print($_ENV['COUNTRY'); in the PHP scripts, the env. variable is not there. Is there please a way to pass the env. vars from Jetty process to the PHP scripts run through FastCGI? The reason why I am asking is that I am trying to reuse as much source code (Java, PHP, Javascript) while serving my word game in 3 different languages. Thank you Alex PS: Below is my Jetty config xml file: # cat /var/www/jetty-base-en/webapps/wordsbyfarber.com.xml http://www.eclipse.org/jetty/configure_9_3.dtd;> /var/www/html/wordsbyfarber.com / wordsbyfarber.com www.wordsbyfarber.com index.php index.html org.eclipse.jetty.fcgi.server.proxy.TryFilesFilter /* files $path /index.php?p=$path default org.eclipse.jetty.servlet.DefaultServlet dirAllowed false gzip true / org.eclipse.jetty.fcgi.server.proxy.FastCGIProxyServlet *.php proxyTo http://localhost:9000 prefix / scriptRoot scriptPattern (.+?\\.php) ___ 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] How to include another web page in a page served by Jetty?
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 generating?From the servlet, > it should be easy enough to get a RequestDispatcher and call it directly. > If you want to do this from static content, then you will need to use > something like JSP to process that content and allow dynamic components to > be added. For wordpress... either do it in wordpress... or do something > very complex about intercepting responses and rewriting with the new > content - possible but a lot of work. > then I will better use jQuery in my test server pages to fetch and insert the web page content Regards Alex ___ jetty-users mailing list jetty-users@eclipse.org To unsubscribe from this list, visit https://www.eclipse.org/mailman/listinfo/jetty-users
