: sectnumlevels: 5
Read-Only View
1. Purpose
Oracle database supports using the WITH READ ONLY clause when creating views to set them as read-only, preventing any data modification operations (INSERT, UPDATE, DELETE, MERGE) on the view. To provide Oracle compatibility, IvorySQL implements the read-only view feature. Once a view is marked as read-only, any DML operations attempting to modify the view’s data will be rejected, ensuring behavioral consistency for Oracle applications running on IvorySQL.
2. Implementation Details
2.1. Syntax and Parsing
2.1.1. Syntax Rule Extension
The READ_ONLY_OPTION enum value was added in the ora_gram.y file, and the opt_check_option syntax rule was extended:
opt_check_option:
WITH CHECK OPTION { $$ = CASCADED_CHECK_OPTION; }
| WITH CASCADED CHECK OPTION { $$ = CASCADED_CHECK_OPTION; }
| WITH LOCAL CHECK OPTION { $$ = LOCAL_CHECK_OPTION; }
| WITH READ ONLY { $$ = READ_ONLY_OPTION; } /* New */
| /* EMPTY */ { $$ = NO_CHECK_OPTION; }
;2.1.2. ViewStmt Structure Extension
In the parsenodes.h file, the ViewCheckOption enum was extended with READ_ONLY_OPTION, and the ViewStmt structure was extended with a readOnly field:
typedef enum ViewCheckOption {
NO_CHECK_OPTION,
LOCAL_CHECK_OPTION,
CASCADED_CHECK_OPTION,
READ_ONLY_OPTION, /* WITH READ ONLY (Oracle compat) */
} ViewCheckOption;
typedef struct ViewStmt {
// ... other fields
bool readOnly; /* WITH READ ONLY (Oracle compat) */
} ViewStmt;The readOnly flag is set during parsing:
n->readOnly = ($10 == READ_ONLY_OPTION);
n->withCheckOption = n->readOnly ? NO_CHECK_OPTION : $10;Note that WITH READ ONLY and WITH CHECK OPTION are mutually exclusive and cannot be used together.
2.2. Relation Options System
2.2.1. read_only Relation Option
The read_only view relation option was defined in the reloptions.c file:
/* reloptions.c */
{
{"read_only",
"Prevents INSERT, UPDATE, and DELETE on this view (Oracle compatibility)",
RELOPT_KIND_VIEW,
AccessExclusiveLock},
false
},
/* in view_reloptions() function */
{"read_only", RELOPT_TYPE_BOOL,
offsetof(ViewOptions, read_only)},2.2.2. ViewOptions Structure Extension
In the rel.h file, the ViewOptions structure was extended with a read_only field:
typedef struct ViewOptions {
// ... other fields
bool read_only; /* WITH READ ONLY (Oracle compat) */
} ViewOptions;2.2.3. RelationIsReadOnlyView Macro
The RelationIsReadOnlyView macro allows quick determination of whether a view is read-only:
#define RelationIsReadOnlyView(relation) \
(AssertMacro(relation->rd_rel->relkind == RELKIND_VIEW), \
(relation)->rd_options && \
((ViewOptions *) (relation)->rd_options)->read_only)2.3. View Creation Processing
The WITH READ ONLY option is handled in the DefineView function in the view.c file:
/* Handle WITH READ ONLY in DefineView() */
if (stmt->readOnly)
{
/* Set read_only relation option */
options = transformViewOptions(RelationGetRelid(newRelationView),
stmt->options, true);
setRelOptions(newRelationView, options, true, AccessExclusiveLock);
}WITH READ ONLY is also supported in the compile_force_view_internal function to ensure FORCE VIEW can properly use the read-only attribute.
2.4. DML Execution Interception
2.4.1. Execution Layer Interception
In the CheckValidResultRel() function in execMain.c, DML operations on read-only views are intercepted:
/* execMain.c */
if (RelationIsReadOnlyView(resultRel))
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
errmsg("cannot modify view \"%s\"",
RelationGetRelationName(resultRel)),
errhint("The view is defined as read-only.")));When executing INSERT, UPDATE, DELETE, or MERGE statements, an error is thrown if the target view is marked as read-only.
2.4.2. Rewrite Layer Interception
The same check was added in the rewriteTargetView() function in rewriteHandler.c:
/* rewriteHandler.c */
if (RelationIsReadOnlyView(view))
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
errmsg("cannot modify view \"%s\"",
RelationGetRelationName(view)),
errhint("The view is defined as read-only.")));This ensures that modifications to read-only views are also blocked at the query rewrite layer.
2.5. pg_dump Support
Support for exporting and restoring the WITH READ ONLY attribute was added in pg_dump.c:
/* pg_dump.h - TableInfo structure new field */
typedef struct _tableInfo {
// ... other fields
bool readOnly; /* WITH READ ONLY (Oracle compat) */
// ... other fields
} _tableInfo;
/* pg_dump.c - extract read_only option */
if (strcmp(options[i].defname, "read_only") == 0)
info->readOnly = defGetBoolean(options[i].def);During export, the dumpTableSchema() and dumpRule() functions output the WITH READ ONLY clause in the appropriate location, ensuring the read-only attribute is preserved during database migration.
3. Usage Examples
3.1. Creating a Read-Only View
-- Create a view with WITH READ ONLY
CREATE VIEW emp_view AS
SELECT * FROM employees
WITH READ ONLY;
-- DML on read-only view will be rejected
INSERT INTO emp_view VALUES (1, 'John', 5000);
-- ERROR: cannot modify view "emp_view"
-- HINT: The view is defined as read-only.
UPDATE emp_view SET salary = 6000 WHERE id = 1;
-- ERROR: cannot modify view "emp_view"
-- HINT: The view is defined as read-only.
DELETE FROM emp_view WHERE id = 1;
-- ERROR: cannot modify view "emp_view"
-- HINT: The view is defined as read-only.3.2. Create or Replace Read-Only View
-- Use OR REPLACE to preserve read-only attribute
CREATE OR REPLACE VIEW emp_view AS
SELECT id, name, department FROM employees
WITH READ ONLY;