Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

[YSQL] Prepared statements not invalidated after schema change #1502

Open
srhickma opened this issue Jun 7, 2019 · 0 comments
Open

[YSQL] Prepared statements not invalidated after schema change #1502

srhickma opened this issue Jun 7, 2019 · 0 comments
Assignees
Labels
area/ysql Yugabyte SQL (YSQL) kind/bug This issue is a bug priority/medium Medium priority issue
Projects

Comments

@srhickma
Copy link
Contributor

srhickma commented Jun 7, 2019

Jira Link: DB-1355
Summary: In some cases, if a statement is prepared on one node, and a table used in the statement is altered from another node, the prepared statement will not be invalidated. This issue can happen even if enough time has passed for the t-server to heartbeat to the master and receive the updated catalog version number.

Test Case:

 @Test public void testConsistentPreparedStatements() throws Exception {
    try (Statement statement1 = createConnection(0).createStatement();
         Statement statement2 = createConnection(1).createStatement()) {
      // Create table from connection 1.
      statement1.execute("CREATE TABLE test_table(id int, PRIMARY KEY (id))");

      // Wait for heartbeat.
      Thread.sleep(1000);

      // Force a cache refresh on connection 2.
      statement2.execute("SELECT * FROM test_table");

      // Prepare from connection 2.
      statement2.execute("PREPARE plan (int) AS SELECT * FROM test_table where id=$1");

      // Alter table from connection 1.
      statement1.execute("ALTER TABLE test_table ADD COLUMN b int");
      statement1.execute("INSERT INTO test_table(id, b) VALUES (1, 2), (2, 3)");

      // Wait for heartbeat.
      Thread.sleep(1000);

      // Only one column is returned.
      assertQuery(statement2, "EXECUTE plan(1)", new Row(1));
    }
  }

Expected Result: The final query in the test should fail with error "ERROR: cached plan must not change result type". This is the error produced if all queries are executed using the same statement (and connection).

Actual Result: The test passes.

Note: This problem also occurs if the statement is prepared after the alter table statement, but before the next t-server heartbeat.

@srhickma srhickma added kind/bug This issue is a bug area/ysql Yugabyte SQL (YSQL) labels Jun 7, 2019
@srhickma srhickma added this to To do in YSQL via automation Jun 7, 2019
@srhickma srhickma changed the title [YSQL] Prepared statements not invalidated after schema change. [YSQL] Prepared statements not invalidated after schema change Jun 8, 2019
@ndeodhar ndeodhar moved this from To do to Backlog in YSQL Aug 5, 2020
@yugabyte-ci yugabyte-ci added the priority/medium Medium priority issue label Jun 8, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
area/ysql Yugabyte SQL (YSQL) kind/bug This issue is a bug priority/medium Medium priority issue
Projects
Status: No status
YSQL
  
Backlog
Development

No branches or pull requests

4 participants