Обсуждение: Forward declaration of table
with PostgreSQL 9.5.3 I am using the following table to store 2-player games:
DROP TABLE IF EXISTS words_games;
CREATE TABLE words_games (
gid SERIAL PRIMARY KEY,
created timestamptz NOT NULL,
player1 integer REFERENCES words_users(uid) ON DELETE CASCADE NOT NULL,
player2 integer REFERENCES words_users(uid) ON DELETE CASCADE,
played1 timestamptz,
played2 timestamptz,
-- mid1 integer REFERENCES words_moves(mid) ON DELETE CASCADE,
-- mid2 integer REFERENCES words_moves(mid) ON DELETE CASCADE,
score1 integer NOT NULL CHECK(score1 >= 0),
score2 integer NOT NULL CHECK(score2 >= 0),
hand1 varchar[7] NOT NULL,
hand2 varchar[7] NOT NULL,
pile varchar[116] NOT NULL,
letters varchar[15][15] NOT NULL,
values integer[15][15] NOT NULL,
bid integer NOT NULL REFERENCES words_boards ON DELETE CASCADE
);
DROP TABLE IF EXISTS words_moves;
DROP TYPE IF EXISTS words_action;
However the 2 added columns do not work:
ERROR: relation "words_moves" does not existERROR: relation "words_games" does not existERROR: relation "words_moves" does not exist
Here are all tables of my game for more context:
On 08/23/2016 10:10 AM, Alexander Farber wrote: > Good evening, > > with PostgreSQL 9.5.3 I am using the following table to store 2-player > games: > > DROP TABLE IF EXISTS words_games; > CREATE TABLE words_games ( > gid SERIAL PRIMARY KEY, > created timestamptz NOT NULL, > player1 integer REFERENCES words_users(uid) ON DELETE > CASCADE NOT NULL, > player2 integer REFERENCES words_users(uid) ON DELETE CASCADE, > played1 timestamptz, > played2 timestamptz, > -- mid1 integer REFERENCES words_moves(mid) ON DELETE CASCADE, > -- mid2 integer REFERENCES words_moves(mid) ON DELETE CASCADE, > score1 integer NOT NULL CHECK(score1 >= 0), > score2 integer NOT NULL CHECK(score2 >= 0), > hand1 varchar[7] NOT NULL, > hand2 varchar[7] NOT NULL, > pile varchar[116] NOT NULL, > letters varchar[15][15] NOT NULL, > values integer[15][15] NOT NULL, > bid integer NOT NULL REFERENCES words_boards ON DELETE CASCADE > ); > > > This has worked well for me (when a user connects to the game server, I > send her all games she is taking part in), but then I have decided to > add another table to act as a "logging journal" for player moves: > > DROP TABLE IF EXISTS words_moves; > > DROP TYPE IF EXISTS words_action; > > CREATE TABLE words_moves ( > mid SERIAL PRIMARY KEY, > action words_action NOT NULL, > gid integer NOT NULL REFERENCES words_games ON DELETE CASCADE, > uid integer NOT NULL REFERENCES words_users ON DELETE CASCADE, > played timestamptz NOT NULL, > tiles jsonb, > score integer CHECK(score > 0) > ); > > Also, in the former table words_games I wanted to add references to the > latest moves performed by players: > > -- mid1 integer REFERENCES words_moves(mid) ON DELETE CASCADE, > -- mid2 integer REFERENCES words_moves(mid) ON DELETE CASCADE, > > The intention is: whenever a player connects to the server, sent her all > active games and status updates on the recent opponent moves. > > However the 2 added columns do not work: > > ERROR: relation "words_moves" does not exist > ERROR: relation "words_games" does not exist > ERROR: relation "words_moves" does not exist > > > So my question is if I can somehow "forward declare" the words_moves table? Off the top of my head: Change this: --mid1 integer REFERENCES words_moves(mid) ON DELETE CASCADE, --mid2 integer REFERENCES words_moves(mid) ON DELETE CASCADE, to mid1 integer mid2 integer and then after CREATE TABLE words_moves ... use ALTER TABLE ADD table_constraint : https://www.postgresql.org/docs/9.5/static/sql-altertable.html to add the FK references to word_games. > > Here are all tables of my game for more context: > https://gist.github.com/afarber/c40b9fc5447335db7d24 > > Thank you > Alex > -- Adrian Klaver adrian.klaver@aklaver.com
So my question is if I can somehow "forward declare" the words_moves table?
use ALTER TABLE ADD table_constraint :
https://www.postgresql.org/docs/9.5/static/sql-altertable. html
to add the FK references to word_games.
On 08/23/2016 10:29 AM, David G. Johnston wrote: > On Tue, Aug 23, 2016 at 1:24 PM, Adrian Klaver > <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>wrote: > > > use ALTER TABLE ADD table_constraint : > > https://www.postgresql.org/docs/9.5/static/sql-altertable.html > <https://www.postgresql.org/docs/9.5/static/sql-altertable.html> > > to add the FK references to word_games. > > > Hadn't considered "ALTER TABLE" but I'd be afraid of dump-restore > hazards here. Maybe pg_dump is smart enough to handle this correctly, > though - maybe by adding constraint definitions after all tables and > columns are present. It does. Though the usual caveats about doing partial dumps apply, eg if I had only specified -t fk_child below I would not get fk_parent automatically: postgres@test=# create table fk_child(id int, fk_id int); CREATE TABLE postgres@test=# create table fk_parent(id int, some_id int UNIQUE); CREATE TABLE postgres@test=# alter table fk_child ADD CONSTRAINT fk_constraint FOREIGN KEY (fk_id) REFERENCES fk_parent(some_id); ALTER TABLE pg_dump -d test -U postgres -t fk_parent -t fk_child -f test.sql -- -- Name: fk_child; Type: TABLE; Schema: public; Owner: postgres -- CREATE TABLE fk_child ( id integer, fk_id integer ); ALTER TABLE fk_child OWNER TO postgres; -- -- Name: fk_parent; Type: TABLE; Schema: public; Owner: postgres -- CREATE TABLE fk_parent ( id integer, some_id integer ); ALTER TABLE fk_parent OWNER TO postgres; -- -- Data for Name: fk_child; Type: TABLE DATA; Schema: public; Owner: postgres -- COPY fk_child (id, fk_id) FROM stdin; \. -- -- Data for Name: fk_parent; Type: TABLE DATA; Schema: public; Owner: postgres -- COPY fk_parent (id, some_id) FROM stdin; \. -- -- Name: fk_parent_some_id_key; Type: CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY fk_parent ADD CONSTRAINT fk_parent_some_id_key UNIQUE (some_id); -- -- Name: fk_constraint; Type: FK CONSTRAINT; Schema: public; Owner: postgres -- ALTER TABLE ONLY fk_child ADD CONSTRAINT fk_constraint FOREIGN KEY (fk_id) REFERENCES fk_parent(some_id); > > David J. > -- Adrian Klaver adrian.klaver@aklaver.com
Regards,
Igor
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Alexander Farber
Sent: Tuesday, August 23, 2016 1:11 PM
To: pgsql-general <pgsql-general@postgresql.org>
Subject: [GENERAL] Forward declaration of table
Good evening,
with PostgreSQL 9.5.3 I am using the following table to store 2-player games:
DROP TABLE IF EXISTS words_games;
CREATE TABLE words_games (
gid SERIAL PRIMARY KEY,
created timestamptz NOT NULL,
player1 integer REFERENCES words_users(uid) ON DELETE CASCADE NOT NULL,
player2 integer REFERENCES words_users(uid) ON DELETE CASCADE,
played1 timestamptz,
played2 timestamptz,
-- mid1 integer REFERENCES words_moves(mid) ON DELETE CASCADE,
-- mid2 integer REFERENCES words_moves(mid) ON DELETE CASCADE,
score1 integer NOT NULL CHECK(score1 >= 0),
score2 integer NOT NULL CHECK(score2 >= 0),
hand1 varchar[7] NOT NULL,
hand2 varchar[7] NOT NULL,
pile varchar[116] NOT NULL,
letters varchar[15][15] NOT NULL,
values integer[15][15] NOT NULL,
bid integer NOT NULL REFERENCES words_boards ON DELETE CASCADE
);
This has worked well for me (when a user connects to the game server, I send her all games she is taking part in), but then I have decided to add another table to act as a "logging journal" for player moves:
DROP TABLE IF EXISTS words_moves;
DROP TYPE IF EXISTS words_action;
CREATE TABLE words_moves (
mid SERIAL PRIMARY KEY,
action words_action NOT NULL,
gid integer NOT NULL REFERENCES words_games ON DELETE CASCADE,
uid integer NOT NULL REFERENCES words_users ON DELETE CASCADE,
played timestamptz NOT NULL,
tiles jsonb,
score integer CHECK(score > 0)
);
Also, in the former table words_games I wanted to add references to the latest moves performed by players:
-- mid1 integer REFERENCES words_moves(mid) ON DELETE CASCADE,
-- mid2 integer REFERENCES words_moves(mid) ON DELETE CASCADE,
The intention is: whenever a player connects to the server, sent her all active games and status updates on the recent opponent moves.
However the 2 added columns do not work:
ERROR: relation "words_moves" does not exist
ERROR: relation "words_games" does not exist
ERROR: relation "words_moves" does not exist
So my question is if I can somehow "forward declare" the words_moves table?
Here are all tables of my game for more context:
Thank you
Alex
Alex,
I think, you’ve got this reference “backwards”.
Certain MOVE exists only within particular GAME: no GAME -> no MOVE (on delete cascade).
So, you don’t need mid1, mid2 columns in WORD_GAMES table.
What you need is this column in WORD_MOVES table:
gid integer REFERENCES WORD_GAMES ON DELETE CASCADE
Am right/wrong?
Regards,
Igor
mailto:pgsql-general-owner@
postgresql.org] On Behalf Of Alexander Farber
Certain MOVE exists only within particular GAME: no GAME -> no MOVE (on delete cascade).
So, you don’t need mid1, mid2 columns in WORD_GAMES table.
What you need is this column in WORD_MOVES table:
gid integer REFERENCES WORD_GAMES ON DELETE CASCADE
If I don't store the recent moves in mid1, mid2 then I'd have to retrieve them every time dynamically with
WITH last_moves AS (SELECT *FROM words_moves wm1WHEREplayed = (SELECT max(played)FROM words_moves wm2WHERE wm1.gid = wm2.gid))SELECT *FROM words_games wgLEFT JOIN last_moves lmON (wg.gid = lm.gid)WHEREplayer1 = 1 ORplayer2 = 1;
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Alexander Farber
Sent: Tuesday, August 23, 2016 3:33 PM
Cc: pgsql-general <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] Forward declaration of table
Hi Igor,
On Tue, Aug 23, 2016 at 8:15 PM, Igor Neyman <ineyman@perceptron.com> wrote:
mailto:pgsql-general-owner@postgresql.org] On Behalf Of Alexander Farber
Certain MOVE exists only within particular GAME: no GAME -> no MOVE (on delete cascade).
So, you don’t need mid1, mid2 columns in WORD_GAMES table.
What you need is this column in WORD_MOVES table:
gid integer REFERENCES WORD_GAMES ON DELETE CASCADE
you are correct, but I need to send most recent move in each game together with the other game data.
If I don't store the recent moves in mid1, mid2 then I'd have to retrieve them every time dynamically with
WITH last_moves AS (
SELECT *
FROM words_moves wm1
WHERE
played = (SELECT max(played)
FROM words_moves wm2
WHERE wm1.gid = wm2.gid))
SELECT *
FROM words_games wg
LEFT JOIN last_moves lm
ON (wg.gid = lm.gid)
WHERE
player1 = 1 OR
player2 = 1;
Regards
Alex
Or, for the last moves you could probably have the third table LAST_MOVES maintained through triggers on WORDS_MOVES table.
Then, you just join WORDS_GAMES and LAST_MOVES tables.
Regards,
Igor
I have went the ALTER TABLE route to add my 2 "cyclic" FKs:
https://gist.github.com/afarber/c40b9fc5447335db7d24
And now I have these 2 tables in my 9.5.3 database:
#TABLE words_moves;
mid | action | gid | uid | played | tiles | score
-----+--------+-----+-----+-------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------
2 | play | 1 | 1 | 2016-08-24 20:36:39.888224+02 | [{"col": 7, "row": 8, "value": 2, "letter": "Р"}, {"col": 7, "row": 7, "value": 1, "letter": "Е"}, {"col": 7, "row": 6, "value": 10, "letter": "Ф"}] | 13
3 | play | 2 | 1 | 2016-08-24 21:48:14.448361+02 | [{"col": 7, "row": 12, "value": 5, "letter": "Ь"}, {"col": 7, "row": 10, "value": 1, "letter": "Е"}, {"col": 7, "row": 9, "value": 1, "letter": "О"}, {"col": 7, "row": 11, "value": 10, "letter": "Ш"}, {"col": 7, "row": 8, "value": 2, "letter": "Р"}, {"col": 7, "row": 7, "value": 2, "letter": "П"}] | 31
4 | play | 1 | 2 | 2016-08-24 21:50:55.231266+02 | [{"col": 8, "row": 8, "value": 2, "letter": "Й"}, {"col": 8, "row": 7, "value": 1, "letter": "А"}, {"col": 8, "row": 6, "value": 2, "letter": "Р"}, {"col": 8, "row": 5, "value": 2, "letter": "С"}] | 33
(3 rows)
# SELECT gid, EXTRACT(EPOCH FROM created)::int AS created, player1, COALESCE(player2, 0) AS player2, COALESCE(EXTRACT(EPOCH FROM played1)::int, 0) AS played1, COALESCE(EXTRACT(EPOCH FROM played2)::int, 0) AS played2, ARRAY_TO_STRING(hand1, '') AS hand1, ARRAY_TO_STRING(hand2, '') AS hand2, bid FROM words_games WHERE player1 = 1 OR player2 = 1;
gid | created | player1 | player2 | played1 | played2 | hand1 | hand2 | bid
-----+------------+---------+---------+------------+------------+---------+---------+-----
2 | 1472068074 | 1 | 0 | 1472068094 | 0 | ЫТОВЕРЛ | ЕНХЯЭАК | 1
1 | 1472063658 | 1 | 2 | 1472063800 | 1472068255 | ВГЦЕСИУ | ННДНСВТ | 1
(2 rows)
Then I am trying to perform the LEFT JOIN to return active games and recent moves for player 1, but for some reason the first two columns are empty:
# SELECT m.tiles, m.score, g.gid, EXTRACT(EPOCH FROM g.created)::int AS created, g.player1, COALESCE(g.player2, 0) AS player2, COALESCE(EXTRACT(EPOCH FROM g.played1)::int, 0) AS played1, COALESCE(EXTRACT(EPOCH FROM g.played2)::int, 0) AS played2, ARRAY_TO_STRING(g.hand1, '') AS hand1, ARRAY_TO_STRING(g.hand2, '') AS hand2, g.bid FROM words_games g LEFT JOIN words_moves m
tiles | score | gid | created | player1 | player2 | played1 | played2 | hand1 | hand2 | bid
-------+-------+-----+------------+---------+---------+------------+------------+---------+---------+-----
| | 2 | 1472068074 | 1 | 0 | 1472068094 | 0 | ЫТОВЕРЛ | ЕНХЯЭАК | 1
| | 1 | 1472063658 | 1 | 2 | 1472063800 | 1472068255 | ВГЦЕСИУ | ННДНСВТ | 1
(2 rows)
Alex
Why aren't m.tiles and m.score returned please?
Why aren't m.tiles and m.score returned please?How about you output g.mid1 and g.mid2 in the first query and confirm that the rows being returned from words_games actually have a value in the set {2,3,4} in one of those columns.
On 08/24/2016 01:27 PM, Alexander Farber wrote: > Hello again, > > I have went the ALTER TABLE route to add my 2 "cyclic" FKs: > > https://gist.github.com/afarber/c40b9fc5447335db7d24 > > And now I have these 2 tables in my 9.5.3 database: > > Why aren't m.tiles and m.score returned please? Reformatted your LEFT JOIN query(courtesy of http://sqlformat.darold.net/) : SELECT m.tiles, m.score, g.gid, extract ( EPOCH FROM g.created ) ::INT AS created, g.player1, COALESCE ( g.player2, 0 ) AS player2, COALESCE ( extract ( EPOCH FROM g.played1 ) ::INT, 0 ) AS played1, COALESCE ( extract ( EPOCH FROM g.played2 ) ::INT, 0 ) AS played2, array_to_string ( g.hand1, '' ) AS hand1, array_to_string ( g.hand2, '' ) AS hand2, g.bid FROM words_games g LEFT JOIN words_moves m ON ( g.mid1 = m.mid OR g.mid2 = m.mid ) WHERE g.player1 = 1 OR g.player2 = 1; Looking at your tables I would start with something like: SELECT wm.tiles, wm.score FROM word_games AS wg JOIN word_moves AS wm ON wg.gid = wm.gid WHERE (wg.player1 = 1 OR wg.player2 = 1) > > Regards > Alex -- Adrian Klaver adrian.klaver@aklaver.com