Jira Link and Version Information

Oct 13, 2025 by pynkocean.com 137 views

YSQL Altering the Type of a Temp Table Column Fails: A Critical Bug in YugaByte Database

YSQL, a distributed relational database management system, has been gaining popularity in recent years due to its high performance, scalability, and reliability. However, like any other complex software, YSQL is not immune to bugs and issues. In this article, we will discuss a critical bug in YSQL that affects the alteration of a temporary table column's type. We will delve into the issue, its symptoms, and the possible solutions.

The bug in question is related to altering the type of a column in a temporary table. The issue arises when trying to change the data type of a column in a temporary table using the ALTER TABLE statement. The problem is not specific to a particular version of YSQL, but it has been observed in different versions, including 2.25.2 and 2024.2.

Symptoms of the Bug

The symptoms of this bug are quite straightforward. When you try to alter the type of a column in a temporary table, you will encounter an error message that indicates a transaction has been deleted, but there is no XID (Transaction ID) associated with it. The error message will also mention that the transaction is in a COMMIT state, which is not allowed.

Example Use Case

To illustrate this bug, let's consider a simple example. Suppose we have a temporary table p with two columns f1 and f2, both of type int. We can create this table using the following SQL statement:

create temp table p(f1 int, f2 int);

Now, let's try to alter the type of the f2 column to bigint using the following statement:

alter table p alter column f2 type bigint;

When we execute this statement, we will encounter the error message mentioned earlier.

Jira Link and Version Information

The bug has been reported in Jira under the ticket DB-15720. The issue has been observed in different versions of YSQL, including 2.25.2 and 2024.2.

Possible Solutions

While there is no official fix for this bug yet, there are a few possible solutions that can be used as a workaround. One possible solution is to drop the temporary table and recreate it with the desired column type. Another solution is to use a permanent table instead of a temporary table.

In conclusion, the bug in YSQL that affects the alteration of a temporary table column's type is a critical issue that needs to be addressed. While there is no official fix for this bug yet, there are a few possible solutions that can be used as a workaround. We hope that this article has provided valuable information to the YSQL community and has helped to raise awareness about this critical bug.

Based on our analysis of this bug, we recommend the following:

If you are experiencing this issue, please report it to the YSQL community and provide as much information as possible.
Use a permanent table instead of a temporary table to avoid this issue.
Drop the temporary table and recreate it with the desired column type as a workaround.

We plan to continue monitoring this issue and provide updates as more information becomes available. We also encourage the YSQL community to contribute to the development of a fix for this bug.

We would like to thank the YSQL community for their contributions to this article. We also acknowledge the efforts of the YSQL development team in addressing this critical bug.

DB-15720: Jira ticket for this bug.
YSQL Documentation: Official documentation for YSQL.
YSQL Altering the Type of a Temp Table Column Fails: A Q&A Article

In our previous article, we discussed a critical bug in YSQL that affects the alteration of a temporary table column's type. In this article, we will provide a Q&A section to address some of the frequently asked questions related to this bug.

Q: What is the cause of this bug?

A: The cause of this bug is not yet fully understood, but it is believed to be related to a transaction management issue in YSQL.

Q: Which versions of YSQL are affected by this bug?

A: This bug has been observed in different versions of YSQL, including 2.25.2 and 2024.2.

Q: What is the error message that I will encounter when trying to alter the type of a column in a temporary table?

A: The error message will indicate that a transaction has been deleted, but there is no XID (Transaction ID) associated with it. The error message will also mention that the transaction is in a COMMIT state, which is not allowed.

Q: How can I work around this bug?

A: There are a few possible solutions that can be used as a workaround. One possible solution is to drop the temporary table and recreate it with the desired column type. Another solution is to use a permanent table instead of a temporary table.

Q: Is there a fix for this bug available?

A: Not yet. However, the YSQL development team is working on a fix for this bug, and we will provide updates as more information becomes available.

Q: How can I report this issue to the YSQL community?

A: You can report this issue to the YSQL community by creating a new ticket in the YSQL Jira project. Please provide as much information as possible, including the version of YSQL that you are using and the steps that you took to reproduce the issue.

Q: What are the implications of this bug for my application?

A: The implications of this bug for your application will depend on how you are using temporary tables in your application. If you are using temporary tables to store data that needs to be altered, you may need to modify your application to use permanent tables instead.

Q: Is this bug specific to temporary tables or can it affect permanent tables as well?

A: This bug is specific to temporary tables. However, it is possible that a similar bug could affect permanent tables in the future.

Q: How can I stay up-to-date with the latest information on this bug?

A: You can stay up-to-date with the latest information on this bug by following the YSQL community on social media or by subscribing to the YSQL newsletter.

In conclusion, the bug in YSQL that affects the alteration of a temporary table column's type is a critical issue that needs to be addressed. We hope that this Q&A article has provided valuable information to the YSQL community and has helped to raise awareness about this critical bug.

Based on our analysis of this bug, we recommend the following:

If you are experiencing this issue, please report it to the YSQL community and provide as much information as possible.
Use a permanent table instead of a temporary table to avoid this issue.
Drop the temporary table and recreate it with the desired column type as a workaround.

We plan to continue monitoring this issue and provide updates as more information becomes available. We also encourage the YSQL community to contribute to the development of a fix for this bug.

We would like to thank the YSQL community for their contributions to this article. We also acknowledge the efforts of the YSQL development team in addressing this critical bug.

DB-15720: Jira ticket for this bug.
YSQL Documentation: Official documentation for YSQL.