ALTER TABLE, modifying columns

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

ALTER TABLE, modifying columns

Thomas Kurz
Dear all,

I don't know whether the behavior is intentional or a bug, so let me describe it (occurs since 3.25):

Due to the lack of ALTER TABLE MODIFY COLUMN, I use the following construction:

PRAGMA foreign_keys=0
BEGIN TRANSACTION
ALTER TABLE x RENAME TO x_old
CREATE TABLE IF NOT EXISTS x (... new declaration ...)
INSERT INTO x (...) SELECT ... FROM x_old
DROP TABLE x_old
.... more to do here ...
COMMIT
PRAGMA foreign_keys=1

Usually, this works fine, but now I have a VIEW that references table x, which leads to an error "error in view ...: no such table: main.x_old".

Of course, this happens because renaming x to x_old also changes the view's reference from x to x_old which is not intended in this case.

As a workaround, I have now added "PRAGMA legacy_alter_table" before and after the transaction.

The behavior makes modifying columns even more complicated, so I'd like to beg for an ALTER TABLE MODIFY COLUMN statement once more ;-))

Kind regards,
Thomas

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: ALTER TABLE, modifying columns

Shawn Wagner
You're using a workflow that https://www.sqlite.org/lang_altertable.html
explicitly calls out as incorrect and error prone...

Try to create a new table, copy data over, drop the original and then
rename the new one to see if that fixes the issue.

On Wed, Dec 12, 2018, 8:54 AM Thomas Kurz <[hidden email] wrote:

> Dear all,
>
> I don't know whether the behavior is intentional or a bug, so let me
> describe it (occurs since 3.25):
>
> Due to the lack of ALTER TABLE MODIFY COLUMN, I use the following
> construction:
>
> PRAGMA foreign_keys=0
> BEGIN TRANSACTION
> ALTER TABLE x RENAME TO x_old
> CREATE TABLE IF NOT EXISTS x (... new declaration ...)
> INSERT INTO x (...) SELECT ... FROM x_old
> DROP TABLE x_old
> .... more to do here ...
> COMMIT
> PRAGMA foreign_keys=1
>
> Usually, this works fine, but now I have a VIEW that references table x,
> which leads to an error "error in view ...: no such table: main.x_old".
>
> Of course, this happens because renaming x to x_old also changes the
> view's reference from x to x_old which is not intended in this case.
>
> As a workaround, I have now added "PRAGMA legacy_alter_table" before and
> after the transaction.
>
> The behavior makes modifying columns even more complicated, so I'd like to
> beg for an ALTER TABLE MODIFY COLUMN statement once more ;-))
>
> Kind regards,
> Thomas
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: ALTER TABLE, modifying columns

Thomas Kurz
This doesn't work either. The error now occurs in the "ALTER TABLE" line, which is correct as the table "x" being refered to doesn't exist that moment. Tested with both 3.25.2 and 3.26.

Btw, has the "correct vs. incorrect" table that you've cited already been there before release 3.25?


----- Original Message -----
From: Shawn Wagner <[hidden email]>
To: SQLite mailing list <[hidden email]>
Sent: Wednesday, December 12, 2018, 18:02:54
Subject: [sqlite] ALTER TABLE, modifying columns

You're using a workflow that https://www.sqlite.org/lang_altertable.html
explicitly calls out as incorrect and error prone...

Try to create a new table, copy data over, drop the original and then
rename the new one to see if that fixes the issue.

On Wed, Dec 12, 2018, 8:54 AM Thomas Kurz <[hidden email] wrote:

> Dear all,

> I don't know whether the behavior is intentional or a bug, so let me
> describe it (occurs since 3.25):

> Due to the lack of ALTER TABLE MODIFY COLUMN, I use the following
> construction:

> PRAGMA foreign_keys=0
> BEGIN TRANSACTION
> ALTER TABLE x RENAME TO x_old
> CREATE TABLE IF NOT EXISTS x (... new declaration ...)
> INSERT INTO x (...) SELECT ... FROM x_old
> DROP TABLE x_old
> .... more to do here ...
> COMMIT
> PRAGMA foreign_keys=1

> Usually, this works fine, but now I have a VIEW that references table x,
> which leads to an error "error in view ...: no such table: main.x_old".

> Of course, this happens because renaming x to x_old also changes the
> view's reference from x to x_old which is not intended in this case.

> As a workaround, I have now added "PRAGMA legacy_alter_table" before and
> after the transaction.

> The behavior makes modifying columns even more complicated, so I'd like to
> beg for an ALTER TABLE MODIFY COLUMN statement once more ;-))

> Kind regards,
> Thomas

> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: ALTER TABLE, modifying columns

Richard Hipp-3
On 12/12/18, Thomas Kurz <[hidden email]> wrote:
>
> Btw, has the "correct vs. incorrect" table that you've cited already been
> there before release 3.25?

The procedure description is unchanged for many years.  I added the
"Caution:" section recently, because a lot of people have been having
the same problem you are currently having.  Had I known in advance
that so many people were doing the "incorrect" procedure for making
schema modifications, I never would have allowed the recent
enhancements to ALTER TABLE that broke it.  But I didn't know.  And
that is now water under the bridge.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: ALTER TABLE, modifying columns

Richard Hipp-3
In reply to this post by Thomas Kurz
On 12/12/18, Thomas Kurz <[hidden email]> wrote:
> This doesn't work either. The error now occurs in the "ALTER TABLE" line,
> which is correct as the table "x" being refered to doesn't exist that
> moment. Tested with both 3.25.2 and 3.26.

Can you please post a script showing us exactly what you are trying to
do when you get the error?

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: ALTER TABLE, modifying columns

Thomas Kurz
In reply to this post by Richard Hipp-3
> I never would have allowed the recent
> enhancements to ALTER TABLE that broke it.

The enhancements made have been way overdue. Personally, I appreciate them very much and they are worth the "trouble". And I hope that the small problem does not prevent you from taking MODIFY COLUMN and DROP COLUMN into account. I think it should solve most problems as the quite complex procedure (which obviously seems to be improperly implemented quite often, not only from me *g*) would then be obsolete. And, in addition, SQLite could possibly even use a more efficient way for the modifications than just copying all the data. (In fact, a 13th step "VACUUM" may be required in many cases, as dropping the table in step 6 can lead to a large "hole" in the database.)

As far as the example is concerned, I will try to reproduce tomorrow.

Kind regards,
Thomas

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users