Intermittent "could not open file" error upon SELECT from view

Hello, I am a beginner with Yugabyte DB and am running it on a single node. Via ysqlsh I have defined some tables and views. When I perform SELECT from the views, normally I get the results I expect, but a couple of times during the past several hours I got an error such as the following:

yuga_mini=# select * from ename;
ERROR: could not open file “base/23268/23421”: No such file or directory

(ename being the name of one of the views I defined)

I looked in the PostgreSQL logs but did not find any additional information other than the error itself.

I do not know if this matters or not, but these views are “updatable” (i.e., I have defined rules to enable deletion).

I would like to know why this occurred, and if there is something I need to do so that such problem will never appear (for example, if there is something I need to avoid when defining views/tables/etc.)

Your input is greatly appreciated.

Thank you.

Hi @vlst

What version are you using ? Can you paste some steps to reproduce this issue?

I am using version 2.11.1.0

I am trying to port database schema from PostgreSQL to YugabyteDB, and in our PostgreSQL database schema we use table inheritance for several tables. Since YugabyteDB does not support table inheritance, I am trying to emulate it by defining views.

For example, suppose that in our PostgreSQL schema we have the “abstract” table A, and that the “concrete” tables B and C inherit from table A. Suppose also that the “abstract” table D inherits from table A, and that the “concrete” table E inherits from table D. So we have an inheritance tree:
A
/ |
B C D
|
E
In YugabyteDB I have implemented the “concrete” tables B, C and E using their original definitions, except that I also had to add (via copy-paste) the inherited fields (from A for the tables B and C, and from A and D for the table E). I have implemented the “abstract” tables A and D as views in YugabyteDB (for example, the definition of the view A involves UNIONs). Since in our original application we supported deletion operation from the “abstract” tables, I also defined RULEs on the views A and D for DELETE.

It is precisely when I performed SELECT from the views A and D when I saw the intermittent issue that I mentioned.

Can you paste the exact DDL schema so we can reproduce it? You can remove/rename any columns/tables you don’t like to expose.

Hello,

Here is the SQL for the DDL of the schema:
/*** START OF DDL SQL ***/
CREATE EXTENSION IF NOT EXISTS pg_trgm WITH SCHEMA public;
CREATE TABLE table_z (
name character varying NOT NULL,
attr1 character varying NOT NULL,
attr2 character varying,
PRIMARY KEY ((name, attr1) HASH)
);
CREATE INDEX table_z_attr1_idx ON table_z (attr1);
CREATE INDEX table_z_attr2_idx ON table_z (attr2);
CREATE INDEX table_z_attr1_partial_idx ON table_z (attr1)
WHERE (attr1 NOT IN (‘X’, ‘Y’, ‘Z’));
CREATE INDEX table_z_name_gin_idx ON table_z USING YBGIN (name gin_trgm_ops);
CREATE INDEX table_z_name_idx ON table_z (name);
CREATE INDEX table_z_name_like ON table_z (name varchar_pattern_ops);
CREATE TABLE table_x (
name character varying NOT NULL,
comm1 character varying NOT NULL,
comm2 character varying,
comm3 character varying,
comm4 character varying,
attr1 CHARACTER VARYING NOT NULL,
attr2 CHARACTER VARYING,
attr3 CHARACTER VARYING,
attr4 CHARACTER VARYING,
PRIMARY KEY (name HASH)
);
CREATE TABLE table_y (
name character varying NOT NULL,
comm1 character varying NOT NULL,
comm2 character varying,
comm3 character varying,
comm4 character varying,
attr1 character varying NOT NULL,
attr2 character varying,
attr3 character varying,
attr4 character varying,
attr5 numeric,
attr6 character(1),
attr7 character varying,
attr8 numeric(9,6),
attr9 numeric(8,6),
attr10 numeric(2,0),
attr11 character varying,
attr12 character varying,
attr13 character varying,
attr14 timestamp(3) with time zone,
attr15 boolean,
PRIMARY KEY(name HASH),
CONSTRAINT table_y_unique UNIQUE (comm1, attr1)
);
CREATE INDEX table_y_comm1 ON table_y (comm1);
CREATE INDEX table_y_attr1 ON table_y (attr1);
CREATE INDEX table_y_attr9 ON table_y (attr9);
CREATE INDEX table_y_attr9_in_rads ON table_y (radians(attr9));
CREATE INDEX table_y_attr10 ON table_y (attr10);
CREATE INDEX table_y_attr10_in_rads ON table_y (radians(attr10));
CREATE INDEX table_y_attr12 ON table_y (attr12);
CREATE INDEX table_y_comm2 ON table_y (comm2);

CREATE OR REPLACE VIEW view_w AS
SELECT name, comm1, comm2, comm3, comm4
FROM table_x
UNION ALL
SELECT name, comm1, comm2, comm3, comm4
FROM table_y;

CREATE OR REPLACE VIEW view_v AS
SELECT name, comm1
FROM view_w;

CREATE OR REPLACE VIEW ename AS
SELECT name
FROM table_z
UNION ALL
SELECT name
FROM view_v;

CREATE RULE view_w_delete_rule AS ON DELETE TO view_w
DO INSTEAD (
DELETE FROM table_x WHERE name = old.name;
DELETE FROM table_y WHERE name = old.name;
);

CREATE RULE view_v_delete_rule AS ON DELETE TO view_v
DO INSTEAD DELETE FROM view_w WHERE name = old.name;

CREATE RULE ename_delete_rule AS ON DELETE TO ename
DO INSTEAD (
DELETE FROM table_z WHERE name = old.name;
DELETE FROM view_v WHERE name = old.name;
);

/*** END OF DDL SQL ***/

Here is the SQL that inserts the sample data I had when the problem occurred:
INSERT INTO table_x (name, comm1, comm2, comm3, comm4, attr1) VALUES (‘name_2’, ‘comm_1’, ‘comm_2_2’, ‘comm_3_2’, ‘comm_4_2’, ‘attr_1_2’);
INSERT INTO table_y (name, comm1, comm2, comm3, comm4, attr1, attr3, attr4, attr5, attr6, attr7, attr8, attr9, attr10, attr11, attr14)
VALUES (‘name_1’, ‘comm_1’, ‘comm_2_1’, ‘comm_3_1’, ‘comm_4_1’, ‘a’, ‘b’, ‘c’, 0, 1, ‘d’, -199.000000, -99.000000, 1, ‘x’, ‘2022-01-13 15:28:08.28+02’);
INSERT INTO table_z (name, attr1, attr2) VALUES (‘name_1’, ‘mars’, ‘10’);
INSERT INTO table_z (name, attr1, attr2) VALUES (‘name_1’, ‘venus’, ‘empty’);
INSERT INTO table_z (name, attr1, attr2) VALUES (‘name_1’, ‘jupiter’, ‘full’);
INSERT INTO table_z (name, attr1, attr2) VALUES (‘name_1’, ‘X’, ‘X_1’);
INSERT INTO table_z (name, attr1, attr2) VALUES (‘name_2’, ‘X’, ‘X_2’);

The problem had occurred at some point when I was performing SELECT from ename view after deleting data from the views. (I was deleting data, then re-inserting it later on in the tables, and deleting it again, and so forth, for some tests I was doing.)

Your input is greatly appreciated.

Thank you

Just now this problem occurred once again. This time, I dropped my original database, I re-created it, and I ran the data definition SQL (similar to what I had posted above, but with some more tables). Without inserting any data in any table, I ran from within ysqlsh:

yuga_mini=# select * from ename;
ERROR: could not open file “base/24197/24692”: No such file or directory

As long as I was in that ysqlsh session, if I repeated the same query I always got the same error. When I exited that ysqlsh session and started another one, the query returned the expected result (0 rows) without any error.