On 12/18/2016 05:28 AM, Petr Jelinek wrote:
On 17/12/16 18:34, Steve Singer wrote:
On 12/16/2016 07:49 AM, Petr Jelinek wrote:
Yeah subscriptions are per database. I don't want to make v14 just
for these 2 changes as that would make life harder for anybody
code-reviewing the v13 so attached is diff with above fixes that
applies on top of v13.
Thanks that fixes those issues.
A few more I've noticed
pg_dumping subscriptions doesn't seem to work
./pg_dump -h localhost --port 5441 --include-subscriptions test
pg_dump: [archiver (db)] query failed: ERROR: missing FROM-clause entry
for table "p"
LINE 1: ...LECT rolname FROM pg_catalog.pg_roles WHERE oid = p.subowner...
^
pg_dump: [archiver (db)] query was: SELECT s.tableoid, s.oid,
s.subname,(SELECT rolname FROM pg_catalog.pg_roles WHERE oid =
p.subowner) AS rolname, s.subenabled, s.subconninfo, s.subslotname,
s.subpublications FROM pg_catalog.pg_subscription s WHERE s.subdbid =
(SELECT oid FROM pg_catalog.pg_database WHERE datname
= current_database())
I have attached a patch that fixes this.
pg_dump is also generating warnings
pg_dump: [archiver] WARNING: don't know how to set owner for object type
SUBSCRIPTION
I know that the plan is to add proper ACL's for publications and
subscriptions later. I don't know if we want to leave the warning in
until then or do something about it.
Also the tab-competion for create subscription doesn't seem to work as
intended.
I've attached a patch that fixes it and patches to add tab completion
for alter publication|subscription
>From 3ed2ad471766490310b1102d8c9166a597ac11af Mon Sep 17 00:00:00 2001
From: Steve Singer <st...@ssinger.info>
Date: Sun, 18 Dec 2016 12:37:29 -0500
Subject: [PATCH 3/4] Fix tab complete for CREATE SUBSCRIPTION
---
src/bin/psql/tab-complete.c | 4 +++-
1 file changed, 3 insertions(+), 1 deletion(-)
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 8816f6c..6dd47f6 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -2298,8 +2298,10 @@ psql_completion(const char *text, int start, int end)
/* CREATE SUBSCRIPTION */
else if (Matches3("CREATE", "SUBSCRIPTION", MatchAny))
COMPLETE_WITH_CONST("CONNECTION");
+ else if (Matches5("CREATE", "SUBSCRIPTION", MatchAny, "CONNECTION",MatchAny))
+ COMPLETE_WITH_CONST("PUBLICATION");
/* Complete "CREATE SUBSCRIPTION <name> ... WITH ( <opt>" */
- else if (Matches2("CREATE", "SUBSCRIPTION") && TailMatches2("WITH", "("))
+ else if (HeadMatches2("CREATE", "SUBSCRIPTION") && TailMatches2("WITH", "("))
COMPLETE_WITH_LIST5("ENABLED", "DISABLED", "CREATE SLOT",
"NOCREATE SLOT", "SLOT NAME");
--
2.1.4
>From 36a0d4382f7ffd2b740298a2bafd49471766bdad Mon Sep 17 00:00:00 2001
From: Steve Singer <st...@ssinger.info>
Date: Sun, 18 Dec 2016 12:51:14 -0500
Subject: [PATCH 2/4] Add tab-complete for ALTER SUBSCRIPTION
---
src/bin/psql/tab-complete.c | 11 ++++++++++-
1 file changed, 10 insertions(+), 1 deletion(-)
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 4cbb848..8816f6c 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1410,7 +1410,7 @@ psql_completion(const char *text, int start, int end)
"EVENT TRIGGER", "EXTENSION", "FOREIGN DATA WRAPPER", "FOREIGN TABLE", "FUNCTION",
"GROUP", "INDEX", "LANGUAGE", "LARGE OBJECT", "MATERIALIZED VIEW", "OPERATOR",
"POLICY", "PUBLICATION", "ROLE", "RULE", "SCHEMA", "SERVER", "SEQUENCE",
- "SYSTEM", "TABLE", "TABLESPACE", "TEXT SEARCH", "TRIGGER", "TYPE",
+ "SUBSCRIPTION", "SYSTEM", "TABLE", "TABLESPACE", "TEXT SEARCH", "TRIGGER", "TYPE",
"USER", "USER MAPPING FOR", "VIEW", NULL};
COMPLETE_WITH_LIST(list_ALTER);
@@ -1446,6 +1446,15 @@ psql_completion(const char *text, int start, int end)
COMPLETE_WITH_LIST6("PUBLISH INSERT", "NOPUBLISH INSERT", "PUBLISH UPDATE",
"NOPUBLISH UPDATE", "PUBLISH DELETE", "NOPUBLISH DELETE");
}
+ /* ALTER SUBSCRIPTION <name> ... */
+ else if (Matches3("ALTER","SUBSCRIPTION",MatchAny))
+ {
+ COMPLETE_WITH_LIST5("WITH", "CONNECTION", "SET PUBLICATION", "ENABLE", "DISABLE");
+ }
+ else if (HeadMatches3("ALTER", "SUBSCRIPTION", MatchAny) && TailMatches2("WITH", "("))
+ {
+ COMPLETE_WITH_CONST("SLOT NAME");
+ }
/* ALTER SCHEMA <name> */
else if (Matches3("ALTER", "SCHEMA", MatchAny))
COMPLETE_WITH_LIST2("OWNER TO", "RENAME TO");
--
2.1.4
>From d4685b991245270221a33e0cf8e61d00fb0bf67e Mon Sep 17 00:00:00 2001
From: Steve Singer <st...@ssinger.info>
Date: Sun, 18 Dec 2016 12:47:15 -0500
Subject: [PATCH 1/4] Add tab-complete for ALTER PUBLICATION
---
src/bin/psql/tab-complete.c | 16 +++++++++++++---
1 file changed, 13 insertions(+), 3 deletions(-)
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 6ad05b7..4cbb848 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1409,8 +1409,8 @@ psql_completion(const char *text, int start, int end)
{"AGGREGATE", "COLLATION", "CONVERSION", "DATABASE", "DEFAULT PRIVILEGES", "DOMAIN",
"EVENT TRIGGER", "EXTENSION", "FOREIGN DATA WRAPPER", "FOREIGN TABLE", "FUNCTION",
"GROUP", "INDEX", "LANGUAGE", "LARGE OBJECT", "MATERIALIZED VIEW", "OPERATOR",
- "POLICY", "ROLE", "RULE", "SCHEMA", "SERVER", "SEQUENCE", "SYSTEM", "TABLE",
- "TABLESPACE", "TEXT SEARCH", "TRIGGER", "TYPE",
+ "POLICY", "PUBLICATION", "ROLE", "RULE", "SCHEMA", "SERVER", "SEQUENCE",
+ "SYSTEM", "TABLE", "TABLESPACE", "TEXT SEARCH", "TRIGGER", "TYPE",
"USER", "USER MAPPING FOR", "VIEW", NULL};
COMPLETE_WITH_LIST(list_ALTER);
@@ -1435,7 +1435,17 @@ psql_completion(const char *text, int start, int end)
else
COMPLETE_WITH_FUNCTION_ARG(prev2_wd);
}
-
+ /* ALTER PUBLICATION <name> ...*/
+ else if (Matches3("ALTER","PUBLICATION",MatchAny))
+ {
+ COMPLETE_WITH_LIST4("WITH","ADD TABLE","SET TABLE","DROP TABLE");
+ }
+ /* ALTER PUBLICATION <name> .. WITH ( ... */
+ else if (HeadMatches3("ALTER", "PUBLICATION",MatchAny) && TailMatches2("WITH", "("))
+ {
+ COMPLETE_WITH_LIST6("PUBLISH INSERT", "NOPUBLISH INSERT", "PUBLISH UPDATE",
+ "NOPUBLISH UPDATE", "PUBLISH DELETE", "NOPUBLISH DELETE");
+ }
/* ALTER SCHEMA <name> */
else if (Matches3("ALTER", "SCHEMA", MatchAny))
COMPLETE_WITH_LIST2("OWNER TO", "RENAME TO");
--
2.1.4
>From 106d0af41363b49e25d1756adb09b8ace6100145 Mon Sep 17 00:00:00 2001
From: Steve Singer <st...@ssinger.info>
Date: Sun, 18 Dec 2016 12:57:04 -0500
Subject: [PATCH 4/4] Fix typo for column name when dumping subscriptions with
pg_dump
---
src/bin/pg_dump/pg_dump.c | 2 +-
1 file changed, 1 insertion(+), 1 deletion(-)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 4968c27..536366a 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -3605,7 +3605,7 @@ getSubscriptions(Archive *fout)
/* Get the subscriptions in current database. */
appendPQExpBuffer(query,
"SELECT s.tableoid, s.oid, s.subname,"
- "(%s p.subowner) AS rolname, s.subenabled, "
+ "(%s s.subowner) AS rolname, s.subenabled, "
" s.subconninfo, s.subslotname, s.subpublications "
"FROM pg_catalog.pg_subscription s "
"WHERE s.subdbid = (SELECT oid FROM pg_catalog.pg_database"
--
2.1.4
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers