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:
https://gist.github.com/afarber/c40b9fc5447335db7d24
Thank you
Alex
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 existSo 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/c40b9fc5447335db7d24Thank you
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 Tue, Aug 23, 2016 at 1:10 PM, Alexander Farber <
alexander.farber@gmail.com> wrote:
So my question is if I can somehow "forward declare" the words_moves table?
A better way to phrase this is:
Is it possible to create circular foreign key dependencies between tables?
The answer is no.
You generally need to introduce a third table. I haven't delved deep
enough into your scenario to be more specific.
David J.
On Tue, Aug 23, 2016 at 1:24 PM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:
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.
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.
David J.
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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:
https://gist.github.com/afarber/c40b9fc5447335db7d24
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
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
https://gist.github.com/afarber/c40b9fc5447335db7d24
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
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<mailto:ineyman@perceptron.com>> wrote:
mailto:pgsql-general-owner@postgresql.org<mailto:pgsql-general-owner@postgresql.org>] On Behalf Of Alexander Farber
https://gist.github.com/afarber/c40b9fc5447335db7d24
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
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:
#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
ON (g.mid1 = m.mid OR g.mid2 = m.mid)
WHERE g.player1 = 1
OR g.player2 = 1;
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)
Why aren't m.tiles and m.score returned please?
Regards
Alex
On Wed, Aug 24, 2016 at 4:27 PM, Alexander Farber <
alexander.farber@gmail.com> wrote:
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.
David J.
On Wed, Aug 24, 2016 at 4:38 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:
On Wed, Aug 24, 2016 at 4:27 PM, Alexander Farber <
alexander.farber@gmail.com> wrote: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.
Also, NULL can be annoying here...might want to try IS DISTINCT FROM or
some similar.
Dave
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general