DELETE and JOIN

Alexander Farber <alexander.farber@gmail.com> writes:

...
However, before saving a review, I would like to delete all previous
reviews coming from the same IP in the past 24 hours:
...
I have the feeling that the _author_ip variable is not really necessary and
I could use some kind of "DELETE JOIN" here, but can not figure it out.

Sure, see the USING clause in DELETE. Although your example seems a
bit confused, since you're not actually referring to _author_ip anywhere.
And if you meant "_author_ip" where you wrote "u._author_ip", that's in
a sub-SELECT, where you could just add a join to words_users without
needing any nonstandard DELETE syntax.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 03/13/2017 09:39 AM, Alexander Farber wrote:

Good evening,

In a 9.5 database I would like players to rate each other and save the
reviews in the table:

CREATE TABLE words_reviews (
uid integer NOT NULL CHECK (uid <> author) REFERENCES
words_users ON DELETE CASCADE,
author integer NOT NULL REFERENCES words_users(uid) ON DELETE
CASCADE,
nice integer NOT NULL CHECK (nice = 0 OR nice = 1),
review varchar(255),
updated timestamptz NOT NULL,
PRIMARY KEY(uid, author)
);

while user names and IP addresses are saved in the other database:

CREATE TABLE words_users (
uid SERIAL PRIMARY KEY,
ip inet NOT NULL,
..........
);

However, before saving a review, I would like to delete all previous
reviews coming from the same IP in the past 24 hours:

CREATE OR REPLACE FUNCTION words_review_user(
in_uid integer, /* this user is being rated */
in_author integer, /* by the in_author user */
in_nice integer,
in_review varchar
) RETURNS void AS
$func$
DECLARE
_author_rep integer;
_author_ip integer;
BEGIN

/* find the current IP address of the author */

SELECT ip
INTO _author_ip
FROM words_users
WHERE uid = in_author;

/* try to prevent review fraud - how to improve this query
please? */

DELETE FROM words_reviews
WHERE uid = in_uid
AND AGE(updated) < INTERVAL '1 day'
AND EXISTS (
SELECT 1
FROM words_reviews r INNER JOIN words_users u USING(uid)
WHERE u.ip = u._author_ip
AND r.author = in_author
);

UPDATE words_reviews set
author = in_author,
nice = in_nice,
review = in_review,
updated = CURRENT_TIMESTAMP
WHERE uid = in_uid AND author = in_author;

IF NOT FOUND THEN
INSERT INTO words_reviews (
author,
nice,
review,
updated
) VALUES (
in_author,
in_nice,
in_review,
CURRENT_TIMESTAMP
);
END IF;

END
$func$ LANGUAGE plpgsql;

I have the feeling that the _author_ip variable is not really necessary
and I could use some kind of "DELETE JOIN" here, but can not figure it out.

The USING clause?:

https://www.postgresql.org/docs/9.5/static/sql-delete.html

"PostgreSQL lets you reference columns of other tables in the WHERE
condition by specifying the other tables in the USING clause. For
example, to delete all films produced by a given producer, one can do:

DELETE FROM films USING producers
WHERE producer_id = producers.id AND producers.name = 'foo';
"

Please advise a better query if possible

Best regards
Alex

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Mon, Mar 13, 2017 at 9:39 AM, Alexander Farber <
alexander.farber@gmail.com> wrote:

Good evening,

In a 9.5 database I would like players to rate each other and save the
reviews in the table:

CREATE TABLE words_reviews (
uid integer NOT NULL CHECK (uid <> author) REFERENCES words_users
ON DELETE CASCADE,
author integer NOT NULL REFERENCES words_users(uid) ON DELETE
CASCADE,
nice integer NOT NULL CHECK (nice = 0 OR nice = 1),
review varchar(255),
updated timestamptz NOT NULL,
PRIMARY KEY(uid, author)
);

while user names and IP addresses are saved in the other database:

CREATE TABLE words_users (
uid SERIAL PRIMARY KEY,
ip inet NOT NULL,
..........
);​

​[...]
all previous reviews coming from the same IP in the past 24 hours:

​SELECT (uid, author) -- locate reviews
FROM word_reviews
JOIN words_users USING (u_id)
WHERE u_id IN ( -- from each of the following users...
SELECT wu.u_id
FROM words_users wu
WHERE wu.ip = (SELECT wui.ip FROM words_users wui WHERE wui,uid = in_uid)
-- find all users sharing the ip address of this supplied user
)​
AND updated >= [...] -- but only within the specified time period

David J.

Good morning and thank you for the replies.

I've ended up with the following DELETE USING (in order to delete reviews
coming from different user id, but same IP address in the last 24 hours):

DELETE FROM words_reviews r
USING words_users u
WHERE r.uid = u.uid
AND r.uid = in_uid
AND AGE(r.updated) < INTERVAL '1 day'
AND u.ip = (SELECT ip FROM words_users WHERE uid = in_author);

Regards
Alex

PS: Here is my custom function:

CREATE OR REPLACE FUNCTION words_review_user(
in_uid integer, /* the player in_uid... */
in_author integer, /* ... is reviewed by player in_author */
in_nice integer,
in_review varchar
) RETURNS void AS
$func$
BEGIN
DELETE FROM words_reviews r
USING words_users u
WHERE r.uid = u.uid
AND r.uid = in_uid
AND AGE(r.updated) < INTERVAL '1 day'
AND u.ip = (SELECT ip FROM words_users WHERE uid = in_author);

UPDATE words_reviews SET
author = in_author,
nice = in_nice,
review = in_review,
updated = CURRENT_TIMESTAMP
WHERE uid = in_uid AND author = in_author;

IF NOT FOUND THEN
INSERT INTO words_reviews (
uid,
author,
nice,
review,
updated
) VALUES (
in_uid,
in_author,
in_nice,
in_review,
CURRENT_TIMESTAMP
);
END IF;
END
$func$ LANGUAGE plpgsql;

And here are the tables in question:

CREATE TABLE words_reviews (
uid integer NOT NULL CHECK (uid <> author) REFERENCES words_users
ON DELETE CASCADE,
author integer NOT NULL REFERENCES words_users(uid) ON DELETE
CASCADE,
nice integer NOT NULL CHECK (nice = 0 OR nice = 1),
review varchar(255),
updated timestamptz NOT NULL,
PRIMARY KEY(uid, author)
);

CREATE TABLE words_users (
uid SERIAL PRIMARY KEY,
ip inet NOT NULL,
..........
);