Changeset: 10389dfbf769 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/10389dfbf769
Modified Files:
sql/backends/monet5/sql_upgrades.c
sql/test/emptydb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out
sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.int128
sql/test/emptydb-previous-upgrade-hge/Tests/upgrade.stable.out.int128
sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out
sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out.int128
sql/test/testdb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
sql/test/testdb-previous-upgrade-chain/Tests/upgrade.stable.out
sql/test/testdb-previous-upgrade-chain/Tests/upgrade.stable.out.int128
sql/test/testdb-previous-upgrade-hge/Tests/upgrade.stable.out.int128
sql/test/testdb-previous-upgrade/Tests/upgrade.stable.out
sql/test/testdb-previous-upgrade/Tests/upgrade.stable.out.int128
Branch: Mar2025
Log Message:
Fixed Mar2025-SP1 upgrade.
It turns out, you cannot set a table to READ WRITE and insert into it
in the same transaction. The insert is silently(!) not logged in the
WAL.
Fixed by making them separate transactions.
diffs (truncated from 364 to 300 lines):
diff --git a/sql/backends/monet5/sql_upgrades.c
b/sql/backends/monet5/sql_upgrades.c
--- a/sql/backends/monet5/sql_upgrades.c
+++ b/sql/backends/monet5/sql_upgrades.c
@@ -4768,93 +4768,101 @@ sql_update_mar2025_sp1(Client c, mvc *sq
const char query1[] = "select table_type_name from sys.table_types
where table_type_name = 'LOCAL TEMPORARY VIEW';";
err = SQLstatementIntern(c, query1, "update", true, false, &output);
if (err == MAL_SUCCEED && (b = BBPquickdesc(output->cols[0].b)) &&
BATcount(b) == 0) {
- const char stmt2[] =
+ const char stmt2a[] =
"ALTER TABLE sys.table_types SET READ WRITE;\n"
- "INSERT INTO sys.table_types VALUES (31, 'LOCAL
TEMPORARY VIEW');\n";
- printf("Running database upgrade commands:\n%s\n", stmt2);
+ "COMMIT;\n";
+ printf("Running database upgrade commands:\n%s\n", stmt2a);
fflush(stdout);
- err = SQLstatementIntern(c, stmt2, "update", true, false, NULL);
+ err = SQLstatementIntern(c, stmt2a, "update", true, false,
NULL);
if (err == MAL_SUCCEED) {
- const char stmt3[] = "ALTER TABLE sys.table_types SET
READ ONLY;\n";
- printf("Running database upgrade commands:\n%s\n",
stmt3);
+ const char stmt2b[] =
+ "INSERT INTO sys.table_types VALUES (31, 'LOCAL
TEMPORARY VIEW');\n"
+ "COMMIT;\n";
+ printf("Running database upgrade commands:\n%s\n",
stmt2b);
fflush(stdout);
- err = SQLstatementIntern(c, stmt3, "update", true,
false, NULL);
- }
+ err = SQLstatementIntern(c, stmt2b, "update", true,
false, NULL);
+ if (err == MAL_SUCCEED) {
+ const char stmt3[] = "ALTER TABLE
sys.table_types SET READ ONLY;\n";
+ printf("Running database upgrade
commands:\n%s\n", stmt3);
+ fflush(stdout);
+ err = SQLstatementIntern(c, stmt3, "update",
true, false, NULL);
+ }
- /* 91_information_schema.sql */
- /* correct definitions of views: information_schema.tables and
information_schema.views */
- if (err == MAL_SUCCEED) {
- const char query[] =
- "DROP VIEW information_schema.views CASCADE;\n"
- "DROP VIEW information_schema.tables CASCADE;\n"
+ /* 91_information_schema.sql */
+ /* correct definitions of views:
information_schema.tables and information_schema.views */
+ if (err == MAL_SUCCEED) {
+ const char query[] =
+ "DROP VIEW information_schema.views
CASCADE;\n"
+ "DROP VIEW information_schema.tables
CASCADE;\n"
- "CREATE VIEW INFORMATION_SCHEMA.TABLES AS
SELECT\n"
- " cast(NULL AS varchar(1)) AS TABLE_CATALOG,\n"
- " s.\"name\" AS TABLE_SCHEMA,\n"
- " t.\"name\" AS TABLE_NAME,\n"
- " tt.\"table_type_name\" AS TABLE_TYPE,\n"
- " cast(NULL AS varchar(1)) AS
SELF_REFERENCING_COLUMN_NAME,\n"
- " cast(NULL AS varchar(1)) AS
REFERENCE_GENERATION,\n"
- " cast(NULL AS varchar(1)) AS
USER_DEFINED_TYPE_CATALOG,\n"
- " cast(NULL AS varchar(1)) AS
USER_DEFINED_TYPE_SCHEMA,\n"
- " cast(NULL AS varchar(1)) AS
USER_DEFINED_TYPE_NAME,\n"
- " cast(sys.ifthenelse((t.\"type\" IN (0, 3, 7,
20, 30) AND t.\"access\" IN (0, 2)), 'YES', 'NO') AS varchar(3)) AS
IS_INSERTABLE_INTO,\n"
- " cast('NO' AS varchar(3)) AS IS_TYPED,\n"
- " cast((CASE t.\"commit_action\" WHEN 1 THEN
'DELETE' WHEN 2 THEN 'PRESERVE' WHEN 3 THEN 'DROP' ELSE NULL END) AS
varchar(10)) AS COMMIT_ACTION,\n"
- " -- MonetDB column extensions\n"
- " t.\"schema_id\" AS schema_id,\n"
- " t.\"id\" AS table_id,\n"
- " t.\"type\" AS table_type_id,\n"
- " st.\"count\" AS row_count,\n"
- " t.\"system\" AS is_system,\n"
- " sys.ifthenelse(t.\"type\" IN (1, 11, 21,
31), TRUE, FALSE) AS is_view,\n"
- " t.\"query\" AS query_def,\n"
- " cm.\"remark\" AS comments\n"
- " FROM sys.\"tables\" t\n"
- " INNER JOIN sys.\"schemas\" s ON
t.\"schema_id\" = s.\"id\"\n"
- " INNER JOIN sys.\"table_types\" tt ON
t.\"type\" = tt.\"table_type_id\"\n"
- " LEFT OUTER JOIN sys.\"comments\" cm ON
t.\"id\" = cm.\"id\"\n"
- " LEFT OUTER JOIN (SELECT DISTINCT \"schema\",
\"table\", \"count\" FROM sys.\"statistics\"()) st ON (s.\"name\" =
st.\"schema\" AND t.\"name\" = st.\"table\")\n"
- " ORDER BY s.\"name\", t.\"name\";\n"
- "GRANT SELECT ON TABLE
INFORMATION_SCHEMA.TABLES TO PUBLIC WITH GRANT OPTION;\n"
+ "CREATE VIEW INFORMATION_SCHEMA.TABLES
AS SELECT\n"
+ " cast(NULL AS varchar(1)) AS
TABLE_CATALOG,\n"
+ " s.\"name\" AS TABLE_SCHEMA,\n"
+ " t.\"name\" AS TABLE_NAME,\n"
+ " tt.\"table_type_name\" AS
TABLE_TYPE,\n"
+ " cast(NULL AS varchar(1)) AS
SELF_REFERENCING_COLUMN_NAME,\n"
+ " cast(NULL AS varchar(1)) AS
REFERENCE_GENERATION,\n"
+ " cast(NULL AS varchar(1)) AS
USER_DEFINED_TYPE_CATALOG,\n"
+ " cast(NULL AS varchar(1)) AS
USER_DEFINED_TYPE_SCHEMA,\n"
+ " cast(NULL AS varchar(1)) AS
USER_DEFINED_TYPE_NAME,\n"
+ " cast(sys.ifthenelse((t.\"type\" IN
(0, 3, 7, 20, 30) AND t.\"access\" IN (0, 2)), 'YES', 'NO') AS varchar(3)) AS
IS_INSERTABLE_INTO,\n"
+ " cast('NO' AS varchar(3)) AS
IS_TYPED,\n"
+ " cast((CASE t.\"commit_action\" WHEN
1 THEN 'DELETE' WHEN 2 THEN 'PRESERVE' WHEN 3 THEN 'DROP' ELSE NULL END) AS
varchar(10)) AS COMMIT_ACTION,\n"
+ " -- MonetDB column extensions\n"
+ " t.\"schema_id\" AS schema_id,\n"
+ " t.\"id\" AS table_id,\n"
+ " t.\"type\" AS table_type_id,\n"
+ " st.\"count\" AS row_count,\n"
+ " t.\"system\" AS is_system,\n"
+ " sys.ifthenelse(t.\"type\" IN (1, 11,
21, 31), TRUE, FALSE) AS is_view,\n"
+ " t.\"query\" AS query_def,\n"
+ " cm.\"remark\" AS comments\n"
+ " FROM sys.\"tables\" t\n"
+ " INNER JOIN sys.\"schemas\" s ON
t.\"schema_id\" = s.\"id\"\n"
+ " INNER JOIN sys.\"table_types\" tt ON
t.\"type\" = tt.\"table_type_id\"\n"
+ " LEFT OUTER JOIN sys.\"comments\" cm
ON t.\"id\" = cm.\"id\"\n"
+ " LEFT OUTER JOIN (SELECT DISTINCT
\"schema\", \"table\", \"count\" FROM sys.\"statistics\"()) st ON (s.\"name\" =
st.\"schema\" AND t.\"name\" = st.\"table\")\n"
+ " ORDER BY s.\"name\", t.\"name\";\n"
+ "GRANT SELECT ON TABLE
INFORMATION_SCHEMA.TABLES TO PUBLIC WITH GRANT OPTION;\n"
- "CREATE VIEW INFORMATION_SCHEMA.VIEWS AS
SELECT\n"
- " cast(NULL AS varchar(1)) AS TABLE_CATALOG,\n"
- " s.\"name\" AS TABLE_SCHEMA,\n"
- " t.\"name\" AS TABLE_NAME,\n"
- " t.\"query\" AS VIEW_DEFINITION,\n"
- " cast('NONE' AS varchar(10)) AS
CHECK_OPTION,\n"
- " cast('NO' AS varchar(3)) AS IS_UPDATABLE,\n"
- " cast('NO' AS varchar(3)) AS
INSERTABLE_INTO,\n"
- " cast('NO' AS varchar(3)) AS
IS_TRIGGER_UPDATABLE,\n"
- " cast('NO' AS varchar(3)) AS
IS_TRIGGER_DELETABLE,\n"
- " cast('NO' AS varchar(3)) AS
IS_TRIGGER_INSERTABLE_INTO,\n"
- " -- MonetDB column extensions\n"
- " t.\"schema_id\" AS schema_id,\n"
- " t.\"id\" AS table_id,\n"
- " t.\"type\" AS table_type_id,\n"
- " t.\"system\" AS is_system,\n"
- " cm.\"remark\" AS comments\n"
- " FROM sys.\"tables\" t\n"
- " INNER JOIN sys.\"schemas\" s ON
t.\"schema_id\" = s.\"id\"\n"
- " LEFT OUTER JOIN sys.\"comments\" cm ON
t.\"id\" = cm.\"id\"\n"
- " WHERE t.\"type\" IN (1, 11, 21, 31)\n"
- " ORDER BY s.\"name\", t.\"name\";\n"
- "GRANT SELECT ON TABLE INFORMATION_SCHEMA.VIEWS
TO PUBLIC WITH GRANT OPTION;\n"
- "\n"
- "UPDATE sys._tables SET system = true where
system <> true\n"
- " and schema_id = (select s.id from sys.schemas
s where s.name = 'information_schema')\n"
- " and name in ('tables','views');\n";
+ "CREATE VIEW INFORMATION_SCHEMA.VIEWS
AS SELECT\n"
+ " cast(NULL AS varchar(1)) AS
TABLE_CATALOG,\n"
+ " s.\"name\" AS TABLE_SCHEMA,\n"
+ " t.\"name\" AS TABLE_NAME,\n"
+ " t.\"query\" AS VIEW_DEFINITION,\n"
+ " cast('NONE' AS varchar(10)) AS
CHECK_OPTION,\n"
+ " cast('NO' AS varchar(3)) AS
IS_UPDATABLE,\n"
+ " cast('NO' AS varchar(3)) AS
INSERTABLE_INTO,\n"
+ " cast('NO' AS varchar(3)) AS
IS_TRIGGER_UPDATABLE,\n"
+ " cast('NO' AS varchar(3)) AS
IS_TRIGGER_DELETABLE,\n"
+ " cast('NO' AS varchar(3)) AS
IS_TRIGGER_INSERTABLE_INTO,\n"
+ " -- MonetDB column extensions\n"
+ " t.\"schema_id\" AS schema_id,\n"
+ " t.\"id\" AS table_id,\n"
+ " t.\"type\" AS table_type_id,\n"
+ " t.\"system\" AS is_system,\n"
+ " cm.\"remark\" AS comments\n"
+ " FROM sys.\"tables\" t\n"
+ " INNER JOIN sys.\"schemas\" s ON
t.\"schema_id\" = s.\"id\"\n"
+ " LEFT OUTER JOIN sys.\"comments\" cm
ON t.\"id\" = cm.\"id\"\n"
+ " WHERE t.\"type\" IN (1, 11, 21, 31)\n"
+ " ORDER BY s.\"name\", t.\"name\";\n"
+ "GRANT SELECT ON TABLE
INFORMATION_SCHEMA.VIEWS TO PUBLIC WITH GRANT OPTION;\n"
+ "\n"
+ "UPDATE sys._tables SET system = true
where system <> true\n"
+ " and schema_id = (select s.id from
sys.schemas s where s.name = 'information_schema')\n"
+ " and name in ('tables','views');\n";
- sql_schema *infoschema = mvc_bind_schema(sql,
"information_schema");
- sql_table *t;
- if ((t = mvc_bind_table(sql, infoschema, "tables")) !=
NULL)
- t->system = 0; /* make it non-system else the
drop view will fail */
- if ((t = mvc_bind_table(sql, infoschema, "views")) !=
NULL)
- t->system = 0;
- printf("Running database upgrade commands:\n%s\n",
query);
- fflush(stdout);
- err = SQLstatementIntern(c, query, "update", true,
false, NULL);
+ sql_schema *infoschema = mvc_bind_schema(sql,
"information_schema");
+ sql_table *t;
+ if ((t = mvc_bind_table(sql, infoschema,
"tables")) != NULL)
+ t->system = 0; /* make it non-system
else the drop view will fail */
+ if ((t = mvc_bind_table(sql, infoschema,
"views")) != NULL)
+ t->system = 0;
+ printf("Running database upgrade
commands:\n%s\n", query);
+ fflush(stdout);
+ err = SQLstatementIntern(c, query, "update",
true, false, NULL);
+ }
}
}
if (output != NULL) {
diff --git
a/sql/test/emptydb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
b/sql/test/emptydb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
---
a/sql/test/emptydb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
+++
b/sql/test/emptydb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
@@ -297,7 +297,11 @@ delete from sys.privileges where (obj_id
Running database upgrade commands:
ALTER TABLE sys.table_types SET READ WRITE;
+COMMIT;
+
+Running database upgrade commands:
INSERT INTO sys.table_types VALUES (31, 'LOCAL TEMPORARY VIEW');
+COMMIT;
Running database upgrade commands:
ALTER TABLE sys.table_types SET READ ONLY;
diff --git a/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out
b/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out
--- a/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out
+++ b/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out
@@ -297,7 +297,11 @@ delete from sys.privileges where (obj_id
Running database upgrade commands:
ALTER TABLE sys.table_types SET READ WRITE;
+COMMIT;
+
+Running database upgrade commands:
INSERT INTO sys.table_types VALUES (31, 'LOCAL TEMPORARY VIEW');
+COMMIT;
Running database upgrade commands:
ALTER TABLE sys.table_types SET READ ONLY;
diff --git
a/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.int128
b/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.int128
--- a/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.int128
+++ b/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.int128
@@ -380,7 +380,11 @@ delete from sys.privileges where (obj_id
Running database upgrade commands:
ALTER TABLE sys.table_types SET READ WRITE;
+COMMIT;
+
+Running database upgrade commands:
INSERT INTO sys.table_types VALUES (31, 'LOCAL TEMPORARY VIEW');
+COMMIT;
Running database upgrade commands:
ALTER TABLE sys.table_types SET READ ONLY;
diff --git
a/sql/test/emptydb-previous-upgrade-hge/Tests/upgrade.stable.out.int128
b/sql/test/emptydb-previous-upgrade-hge/Tests/upgrade.stable.out.int128
--- a/sql/test/emptydb-previous-upgrade-hge/Tests/upgrade.stable.out.int128
+++ b/sql/test/emptydb-previous-upgrade-hge/Tests/upgrade.stable.out.int128
@@ -297,7 +297,11 @@ delete from sys.privileges where (obj_id
Running database upgrade commands:
ALTER TABLE sys.table_types SET READ WRITE;
+COMMIT;
+
+Running database upgrade commands:
INSERT INTO sys.table_types VALUES (31, 'LOCAL TEMPORARY VIEW');
+COMMIT;
Running database upgrade commands:
ALTER TABLE sys.table_types SET READ ONLY;
diff --git a/sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out
b/sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out
--- a/sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out
+++ b/sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out
@@ -297,7 +297,11 @@ delete from sys.privileges where (obj_id
Running database upgrade commands:
ALTER TABLE sys.table_types SET READ WRITE;
+COMMIT;
+
+Running database upgrade commands:
INSERT INTO sys.table_types VALUES (31, 'LOCAL TEMPORARY VIEW');
+COMMIT;
Running database upgrade commands:
ALTER TABLE sys.table_types SET READ ONLY;
diff --git a/sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out.int128
b/sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out.int128
--- a/sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out.int128
+++ b/sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out.int128
@@ -380,7 +380,11 @@ delete from sys.privileges where (obj_id
Running database upgrade commands:
ALTER TABLE sys.table_types SET READ WRITE;
+COMMIT;
+
+Running database upgrade commands:
INSERT INTO sys.table_types VALUES (31, 'LOCAL TEMPORARY VIEW');
+COMMIT;
Running database upgrade commands:
ALTER TABLE sys.table_types SET READ ONLY;
diff --git
a/sql/test/testdb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
b/sql/test/testdb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
--- a/sql/test/testdb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
+++ b/sql/test/testdb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
@@ -297,7 +297,11 @@ delete from sys.privileges where (obj_id
Running database upgrade commands:
ALTER TABLE sys.table_types SET READ WRITE;
+COMMIT;
+
+Running database upgrade commands:
INSERT INTO sys.table_types VALUES (31, 'LOCAL TEMPORARY VIEW');
+COMMIT;
Running database upgrade commands:
ALTER TABLE sys.table_types SET READ ONLY;
diff --git a/sql/test/testdb-previous-upgrade-chain/Tests/upgrade.stable.out
b/sql/test/testdb-previous-upgrade-chain/Tests/upgrade.stable.out
--- a/sql/test/testdb-previous-upgrade-chain/Tests/upgrade.stable.out
+++ b/sql/test/testdb-previous-upgrade-chain/Tests/upgrade.stable.out
@@ -297,7 +297,11 @@ delete from sys.privileges where (obj_id
Running database upgrade commands:
ALTER TABLE sys.table_types SET READ WRITE;
+COMMIT;
+
+Running database upgrade commands:
INSERT INTO sys.table_types VALUES (31, 'LOCAL TEMPORARY VIEW');
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
