Re: Whole Database or Table AES encryption
On Fri, 29 Apr 2022 at 00:32, Bruce Momjian wrote: > > On Thu, Apr 28, 2022 at 11:55:09PM +0100, Aaron Gray wrote: > > I would be very interested in this, are there any further pointers as > > to where to look at this ? > > Is there a repo branch in place working on this or any documentation ? > > https://wiki.postgresql.org/wiki/Transparent_Data_Encryption Many thanks, Aaron
Re: Whole Database or Table AES encryption
On Fri, 29 Apr 2022 at 00:00, Mladen Gogala wrote: > > There is a commercial product which supports database encryption, including > PostgreSQL, it's called Voltage: > > https://www.techtarget.com/searchsecurity/feature/HP-Security-Voltages-SecureData-Enterprise-Product-overview > > The company that I work for uses it to encrypt data in both Oracle and > PostgreSQL databases. I was looking for open source solutions, but thanks anyway. Aaron
Re: Replication with Patroni not working after killing secondary and starting again
> What does `patronictl list` show during that interval? Well. I can't repeat the situation anymore. Now the replication starts immediately after starting the patroni on secondary. I did several switchover commands meanwhile though Meanwhile I did another test where I run a Java app with a large number of *short* transactions (inserts) and during execution of this app I do the patroni switchover command: patronictl -c /etc/patroni/patroni.yml switchover It turned out the records were not replicated to the secondary and when I tried to execute the switchover command on the primary I got the following error: Error: This cluster has no master When I tried to execute the switchover command on the secondary it worked but because there was a discrepancy between the primary and secondary the records on the old primary were rolled back (the number of records on primary and secondary became the same - the same as it was on the old secondary) Apparently there is something wrong with my cluster. How to debug i?. Do I need to configure anything so the replication is synchronous? pt., 29 kwi 2022 o 22:33 Peter J. Holzer napisał(a): > On 2022-04-28 11:09:12 +0200, Zb B wrote: > > > When the secondary starts up it should continue replicating from where > > > it stopped. However, it can only do this if the necessary information > is > > > still available. If WAL files have been deleted in the mean time. it > > > can't replay them. There should be error messages in your logs on what > > > went wrong > > > > I did another test using different wal_sender_timeout parameter, as the > time of > > the secondary being shut down was longer than the default 60s for this > > parameter. > > I don't think this will help. It will just make the primary slower in > noticing that the secondary is gone. > > > > I was hoping it would help but the result was the same (records were not > > replicated to the secondary after the patroni start). Well, I just > verified > > again that the records were replicated after about 15 minutes to the > secondary, > > so probably the timeout setting helped, or I was not patient enough > before. > > The latter, I suspect. Although I'm surprised that it takes so long. In > my experience, that takes only a few seconds, certainly less than a > minute for replication to start (how long it takes to finish depends on > the amount of data, of course). > > Patroni can nuke the secondary database and create a fresh copy > (using basebackup). That might take 15 minutes (depending on the > database size). I don't think it does that automatically, though. Also I > think you would have noticed that. > > What does `patronictl list` show during that interval? > > > > Is it normal to wait so long for the replication? (the original > > transaction in primary took about 5 minutes and was about 3000 small > > records). I am providing more details for completeness below: > > > > I get the following errors on the primary DB: > > 2022-04-28 04:36:50.544 EDT [13794] WARNING: archive_mode enabled, yet > > archive_command is not set > > 2022-04-28 04:37:34.893 EDT [14755] ERROR: replication slot > "xyzd3riardb05" > > does not exist > > 2022-04-28 04:37:34.893 EDT [14755] STATEMENT: START_REPLICATION SLOT > > "xyzd3riardb05" 0/700 TIMELINE 18 > ... > > and after some time such errors stop to appear. > > So the replication slot is probably created after some time and then > replication starts to work. > > I think that replication slot is managed by Patroni. So the question > would be: Why does Patroni take so long to create it? Did it log > anything? > > hp > > -- >_ | Peter J. Holzer| Story must make more sense than reality. > |_|_) || > | | | h...@hjp.at |-- Charles Stross, "Creative writing > __/ | http://www.hjp.at/ | challenge!" >
Completely wrong queryplan
Hi,
I'm stumbling on an issue which seems like this one:
https://www.postgresql.org/message-id/20170719152038.19353.71475%40wrigleys.postgresql.org,
but I hope someone can shed some light on my specific case.
Software:
POSTGIS="3.1.4 ded6c34" [EXTENSION] PGSQL="140" GEOS="3.8.0-CAPI-1.13.1 "
PROJ="6.3.1" LIBXML="2.9.10" LIBJSON="0.13.1" LIBPROTOBUF="1.3.3"
WAGYU="0.5.0 (Internal)"
PostgreSQL 14.0 (Ubuntu 14.0-1.pgdg20.04+1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit
2 relevant tables and indices:
CREATE TABLE osm_current.planet_osm_point
(
osm_id bigint NOT NULL,
tags hstore,
way geometry(Point,3857)
);
CREATE INDEX planet_osm_poi_bigfunc_geo
ON osm_current.planet_osm_point
USING gist
(bigfunc(tags), way)
WHERE bigfunc(tags) <= 14;
CREATE TABLE osm_current.planet_osm_polygon
(
osm_id bigint NOT NULL,
tags hstore,
way geometry(Polygon,3857)
);
CREATE INDEX planet_osm_polygon_bigfunc_geo
ON osm_current.planet_osm_polygon
USING gist
(bigfunc(tags), way)
WHERE bigfunc(tags) <= 14;
Query:
SELECT *
FROM osm_current.planet_osm_polygon
WHERE bigfunc(tags) <= 7
AND NOT EXISTS(
SELECT *
FROM osm_current.planet_osm_point
WHERE bigfunc(planet_osm_point.tags) <= 7
AND ST_Intersects(planet_osm_point.way,planet_osm_polygon.way)
AND bigfunc2(planet_osm_point.tags) =
bigfunc2(planet_osm_polygon.tags)
)
AND ST_Intersects(
'SRID=3857;POLYGON((15012477.510296581
3741379.0533562037,15012477.510296581 4398859.837299369,15669958.252794353
4398859.837299369,15669958.252794353 3741379.0533562037,15012477.510296581
3741379.0533562037))'::geometry,
way)
Normal execution: I canceled it after 1 hour...
Explain gives:
Gather (cost=22998304.12..81977433.81 rows=2628686 width=262)
Workers Planned: 2
-> Parallel Hash Anti Join (cost=22997304.12..81713565.21 rows=1095286
width=262)
Hash Cond: (bigfunc2(planet_osm_polygon.tags) =
bigfunc2(planet_osm_point.tags))
Join Filter: st_intersects(planet_osm_point.way,
planet_osm_polygon.way)
-> Parallel Bitmap Heap Scan on planet_osm_polygon
(cost=51152.38..30790214.58 rows=1096787 width=262)
Recheck Cond: (bigfunc(tags) <= 7)
Filter:
st_intersects('010320110F010005007F5954B04FA26C414760D486618B4C417F5954B04FA26C41185096F5C2C750412FE416C858E36D41185096F5C2C750412FE416C858E36D414760D486618B4C417F5954B04FA26C414760D486618B4C41'::geometry,
way)
-> Bitmap Index Scan on planet_osm_polygon_bigfunc_geo
(cost=0.00..50494.31 rows=2632289 width=0)
Index Cond: ((bigfunc(tags) <= 7) AND (way &&
'010320110F010005007F5954B04FA26C414760D486618B4C417F5954B04FA26C41185096F5C2C750412FE416C858E36D41185096F5C2C750412FE416C858E36D414760D486618B4C417F5954B04FA26C414760D486618B4C41'::geometry))
-> Parallel Hash (cost=22204690.21..22204690.21 rows=23875962
width=126)
-> Parallel Bitmap Heap Scan on planet_osm_point
(cost=309564.90..22204690.21 rows=23875962 width=126)
Recheck Cond: (bigfunc(tags) <= 7)
-> Bitmap Index Scan on planet_osm_poi_bigfunc_geo
(cost=0.00..295239.32 rows=57302310 width=0)
Index Cond: (bigfunc(tags) <= 7)
When setting enable_hashjoin to false it gets radically different:
Gather (cost=52152.79..169588182414.71 rows=2628686 width=262) (actual
time=11.162..1037.116 rows=5381 loops=1)
Output: planet_osm_polygon.osm_id, planet_osm_polygon.tags,
planet_osm_polygon.way
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=380500 read=5531
-> Nested Loop Anti Join (cost=51152.79..169587918546.11 rows=1095286
width=262) (actual time=2.867..1015.295 rows=1794 loops=3)
Output: planet_osm_polygon.osm_id, planet_osm_polygon.tags,
planet_osm_polygon.way
Buffers: shared hit=380500 read=5531
Worker 0: actual time=1.168..1011.822 rows=1834 loops=1
Buffers: shared hit=129515 read=1663
Worker 1: actual time=1.236..1010.438 rows=1858 loops=1
Buffers: shared hit=129837 read=1632
-> Parallel Bitmap Heap Scan on osm_current.planet_osm_polygon
(cost=51152.38..30790214.58 rows=1096787 width=262) (actual
time=1.846..23.809 rows=1853 loops=3)
Output: planet_osm_polygon.osm_id, planet_osm_polygon.tags,
planet_osm_polygon.way
Recheck Cond: (bigfunc(planet_osm_polygon.tags) <= 7)
Filter:
st_intersects('010320110F010005007F5954B04FA26C414760D486618B4C417F5954B04FA26C41185096F5C2C750412FE416C858E36D41185096F5C2C750412FE416C858E36D414760D486618B4C417F5954B04FA26C414760D486618B4C41'::geometry,
planet_osm_polygon.way)
Heap Blocks: exact=1235
Buffers: shared hit=9 read=4104
Worker 0: actual time=0.135..22.343 rows=1902 loops=1
Buffers: shared hit=2 read=1317
Worker 1:
Re: Replication with Patroni not working after killing secondary and starting again
On 2022-05-04 10:21:56 +0200, Zb B wrote: > Apparently there is something wrong with my cluster. How to debug i?. > Do I need to configure anything so the replication is synchronous? Does https://patroni.readthedocs.io/en/latest/replication_modes.html help? hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" signature.asc Description: PGP signature
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&fiddle=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: Displaying chat by punished users only to themselves (db fiddle attached)
On Wed, May 4, 2022 at 5:48 AM Alexander Farber wrote: > > 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. > Optimize for performance second. I would move the test regarding muted to a where clause I'm not understanding how a given user can see anything but their own messages where you have the condition s.social = in_social. 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) Me: u.uid in (player...) and (s.uid = u.uid) Them: u.uid in (player...) and (s.uid <> u.uid) Hopefully you get the idea, your "social" dynamic makes this more challenging. If you can just pass "my uid" into the function then figuring out which uid is "me" and which is "not me" becomes quite a bit easier. David J.
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&fiddle=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
Re: Displaying chat by punished users only to themselves (db fiddle attached)
On 5/4/22 09:40, Alexander Farber wrote: Thank you for replying, David! The "social dynamic" is needed, because I cannot pass real user id (via HTTP) to SQL queries. How do other web sites know to present only "my" data, even though they don't encode "my" user id in the URL? -- Angular momentum makes the world go 'round.
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)
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)
On Wed, May 4, 2022 at 7:40 AM Alexander Farber wrote: > > 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&fiddle=48d4bef569d966021e94c72f86d9fce5 > > CREATE OR REPLACE FUNCTION words_get_chat( > in_gidinteger, > in_social integer, > in_sidtext > ) > I suppose it depends on how you call this function - I would personally separate external authentication and identity from internal business logic. i.e., look up the uid given the social information in one place and then write queries like this one against u_id. AFAICS, the social table provides no benefit to this query that cannot be gotten via uid. It serves to map social info to uid. If you must keep that logic here I strongly suggest you place it into a CTE to call out its purpose in mapping social to user for purposes of figuring out who "me" is. "them" is just going to be a join against user since you won't have any relevant social information for them anyway. > 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)) > That wasn't my suggestion - you still don't know whether u1 is "me" or "them", you've just put player1 into the u1 slot. > ...but how to bring the u1.muted or u2.muted there? > > You can always write something like: CASE WHEN ... THEN u1.muted ELSE u2.muted END if you don't want to pre-define "me" and "them" David J.
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&fiddle=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)
On Wed, May 4, 2022 at 8:21 AM Alexander Farber wrote: > David, I am trying your suggestion: > > On Wed, May 4, 2022 at 4:27 PM David G. Johnston < > david.g.johns...@gmail.com> 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) >> >> > 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) In particular, the IN expression causes two rows to be returned, one for them and one for me - but for each join you only want one or the other. David J.
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&fiddle=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)
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)
On Wed, May 4, 2022 at 8:36 AM Alexander Farber wrote: > David, I try then the following - > > On Wed, May 4, 2022 at 5:28 PM David G. Johnston < > david.g.johns...@gmail.com> 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&fiddle=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? > > 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. David J.
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&fiddle=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)
On Wed, May 4, 2022 at 8:53 AM Alexander Farber wrote: > > 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) > Try "CROSS JOIN cte" - that variant doesn't require a join condition. >
Re: Backing up a DB excluding certain tables
Hello, I just found the solution (or 1 solution). It seems that the problem resides in tables with names containing characters other than lowercase letters. I want to exclude the following tables from the backup: - AspNetRoleClaims - AspNetRoles - AspNetUserClaims - AspNetUserLogins - AspNetUserRoles - AspNetUserTokens - AspNetUsers - __EFMigrationsHistory One pg_dump command that worked correctly is as follows: pg_dump -f c:/temp/respaldo.backup -n riopoderoso -F p -h localhost -p 5433 -U postgres -W -s *-T *.?sp?et* -T *.*igrations?istory* riopoderoso As you can see, uppercase letters and underscore characters were avoided in both -T arguments. In this way, the dumped file was generated successfully without the files listed above. It was not even necessary to issue the command *cmd.exe /c chcp 1252 *in the command prompt before running pg_dump. I also tried different combinations of single quotes and double quotes but none worked. This behavior of characters other than lowercase letters in table names is present no matter if the database is originally created with UTF8 encoding. The problem persists in such a case too. Regards, Jorge Maldonado On Tue, May 3, 2022 at 11:12 AM Adrian Klaver wrote: > On 5/3/22 10:26 AM, JORGE MALDONADO wrote: > > I included the space in chcp 1252, it was a typo in the last message I > sent. > > This is what I just did as per your suggestion in a command prompt with > > Lucida Console font. > > > > C:\Users\JorgeMal>chcp > > Active code page: 437 > > > > C:\Users\JorgeMal>cmd.exe /c chcp 1252 > > Active code page: 1252 > > > > C:\Users\JorgeMal>chcp > > Active code page: 1252 > > > > > > > The result always included tables with *AspNet* in the name. > > I am at a loss for an answer. I just don't use Windows enough to know > where to go from here. > > > > > Regards, > > Jorge Maldonado > > > > > > > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
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&fiddle=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)
On Wed, May 4, 2022 at 9:12 AM Alexander Farber wrote: > I have tried CROSS JOIN and CASE WHEN (why be greedy, right?): > > > https://dbfiddle.uk/?rdbms=postgres_14&fiddle=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... > > What exactly are you trying to do in the "opponent" cte - and why do you think the myself cte is visible to it? David J.
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&fiddle=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: Backing up a DB excluding certain tables
Jorge:
On Wed, 4 May 2022 at 18:12, JORGE MALDONADO wrote:
> I just found the solution (or 1 solution).
> It seems that the problem resides in tables with names containing characters
> other than lowercase letters.
> I want to exclude the following tables from the backup:
> AspNetRoleClaims
> AspNetRoles
> AspNetUserClaims
> AspNetUserLogins
> AspNetUserRoles
> AspNetUserTokens
> AspNetUsers
> __EFMigrationsHistory
>
> One pg_dump command that worked correctly is as follows:
>
> pg_dump -f c:/temp/respaldo.backup -n riopoderoso -F p -h localhost -p 5433
> -U postgres -W -s -T *.?sp?et* -T *.*igrations?istory riopoderoso
>
> As you can see, uppercase letters and underscore characters were avoided in
> both -T arguments. In this way, the dumped file was generated successfully
> without the files listed above.
> It was not even necessary to issue the command cmd.exe /c chcp 1252 in the
> command prompt before running pg_dump.
> I also tried different combinations of single quotes and double quotes but
> none worked.
> This behavior of characters other than lowercase letters in table names is
> present no matter if the database is originally created with UTF8 encoding.
> The problem persists in such a case too.
When I initially saw your question talking about chcp and similar
stuff I skipped it, not having used windows for more than a decade.
The problem you are seeing may be due more to your shell/OS combo than
to other things. In unix, where psql is easier to work with,
to execute a program the OS passes it the arguments vector properly
separated. The different shells are responsible for building these
arguments,
process wildcards and other things an have very well documented
quoting rules to let the user generate exactly what he wants passed to
the
programs. This is why in unix we have to quote * whenever it appears
in a table name and similar stuff.
IIRC In MSDOS mode, whith windows inherited, the shell does some
substitutiton to the command line and then passes whole to the
process, which,
if it is a C program, is then responsible of reparsing it,
reprocessing quotes, expand wildcards and call main. Quoting is poorly
documented and
processing may vary for each program using different CRTs ( that is C
runtime, not cathode ray tube ).
Now for the thing. I'm going to use braces for quoting... If you need
to send a table name with uppercase letters to postgres related
programs you
normally need to insure it receives it in argument with double-quotes,
i.e. {"AspNetRoles"}. In unix this easy, in the bash shell I use the
easy way is to surround the
double quotes with single quotes, {'"AspNetRoles"'} ( you may need a
non proportional font to read that), or just escape the quotes
{\"AspNetRoles\"}
or several other variations. But in windows the quoting rules are
difficult to master, and I'm not sure if you can do that easily ( back
in the days I had
a program which dumped the arguments it received to check what the
shell was doing to my command line ).
At the end of https://www.postgresql.org/docs/14/app-pgdump.html there
is a sample double quoting, but I do not know if this is for a windows
shell. It would
work with unix shells, but is a little convoluted, so may be it is the
thing to try ( try it with an schema only dump redirected to dev/null
( I do not remember
it is windows name, I think it was NUL: )).
An I remember pg_dump had a catalog mode, but it seems to have
dissapeared in recent versions ( you gave it a switch, it wrote a
commented list
of IDs which you could edit ( normally avoiding reorders ) and feed
back with another switch to make partial backups, it was really useful
for selective
backups of complex stuff ).
Francisco Olarte.
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&fiddle=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: Backing up a DB excluding certain tables
Replying to self... On Wed, 4 May 2022 at 19:19, Francisco Olarte wrote: > An I remember pg_dump had a catalog mode, but it seems to have > dissapeared in recent versions ( you gave it a switch, it wrote a > commented list > of IDs which you could edit ( normally avoiding reorders ) and feed > back with another switch to make partial backups, it was really useful > for selective > backups of complex stuff ). Got it wrong, after consulting some old notes it is pg_restore which has the -l/-L switches, what we did, was full-dump a db for upgrading, write TOC, edit TOC to restore only schema on some tables, restore it faster, go live, then use rest of the TOC to restore the missing tables ( they where huge logs, just affected reports which could wait ). Francisco Olarte.
Re: Displaying chat by punished users only to themselves (db fiddle attached)
On Wed, May 4, 2022 at 10:23 AM Alexander Farber wrote: > Is that the right way to do it? > > > https://dbfiddle.uk/?rdbms=postgres_14&fiddle=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; > Assuming it provides the correct result, yes. It's a bit odd to see "from myself" - listing words_chat first makes much more sense. You've defined (social,sid) as a primary key, your LIMIT 1 just makes you look like you don't know or trust that and leaves the reader wondering. 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. David J.
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&fiddle=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?
WIN1252 vs UTF8 database encoding
Hi, I am currently working with a PostgreSQL database created with *WIN1252 *encoding because the data will be in Spanish language. So, both *Collation* and *Character type* are set to *Spanish_Mexico.1252* After reading a bit more about encoding, it seems to me that I can use *UTF8* encoding so characters for all languages are available and keep *Collation* and *Character type* as *Spanish_Mexico.1252*. I guess that using *UTF8* as the encoding method will keep databases more general. I think that using *WIN1252* for encoding is restrictive. Does it make sense? Regards, Jorge Maldonado
Re: WIN1252 vs UTF8 database encoding
On 5/4/22 14:51, JORGE MALDONADO wrote: Hi, I am currently working with a PostgreSQL database created with *WIN1252 *encoding because the data will be in Spanish language. So, both *Collation* and *Character type* are set to *Spanish_Mexico.1252* After reading a bit more about encoding, it seems to me that I can use *UTF8* encoding so characters for all languages are available and keep *Collation* and *Character type* as *Spanish_Mexico.1252*. I guess that using *UTF8* as the encoding method will keep databases more general. I think that using *WIN1252* for encoding is restrictive. Does it make sense? Yes: https://www.postgresql.org/docs/current/multibyte.html "On Windows, however, UTF-8 encoding can be used with any locale." Regards, Jorge Maldonado -- Adrian Klaver adrian.kla...@aklaver.com
