BUG #15446: Crash on ALTER TABLE
The following bug has been logged on the website:
Bug reference: 15446
Logged by: Dmitry Molotkov
Email address: aldarund@gmail.com
PostgreSQL version: 11.0
Operating system: docker
Description:
postgres via docker.
latest tag dont work.
2018-10-19 23:17:31.272 UTC [1] LOG: listening on IPv4 address "0.0.0.0",
port 5432
2018-10-19 23:17:31.272 UTC [1] LOG: listening on IPv6 address "::", port
5432
2018-10-19 23:17:31.280 UTC [1] LOG: listening on Unix socket
"/var/run/postgresql/.s.PGSQL.5432"
2018-10-19 23:17:31.299 UTC [62] LOG: database system was shut down at
2018-10-19 23:17:31 UTC
2018-10-19 23:17:31.306 UTC [1] LOG: database system is ready to accept
connections
2018-10-19 23:17:53.903 UTC [1] LOG: server process (PID 69) was terminated
by signal 11: Segmentation fault
2018-10-19 23:17:53.903 UTC [1] DETAIL: Failed process was running: ALTER
TABLE "admin_interface_theme" ALTER COLUMN
"related_modal_background_opacity" TYPE varchar(5) USING
"related_modal_background_opacity"::varchar(5)
2018-10-19 23:17:53.903 UTC [1] LOG: terminating any other active server
processes
2018-10-19 23:17:53.905 UTC [66] WARNING: terminating connection because of
crash of another server process
2018-10-19 23:17:53.905 UTC [66] DETAIL: The postmaster has commanded this
server process to roll back the current transaction and exit, because
another server process exited abnormally and possibly corrupted shared
memory.
2018-10-19 23:17:53.905 UTC [66] HINT: In a moment you should be able to
reconnect to the database and repeat your command.
2018-10-19 23:17:53.905 UTC [1] LOG: all server processes terminated;
reinitializing
2018-10-19 23:17:53.931 UTC [70] LOG: database system was interrupted; last
known up at 2018-10-19 23:17:31 UTC
2018-10-19 23:17:53.940 UTC [71] FATAL: the database system is in recovery
mode
2018-10-19 23:17:54.139 UTC [70] LOG: database system was not properly shut
down; automatic recovery in progress
2018-10-19 23:17:54.145 UTC [70] LOG: redo starts at 0/1651600
2018-10-19 23:17:54.151 UTC [70] LOG: invalid record length at 0/16C9FE8:
wanted 24, got 0
2018-10-19 23:17:54.151 UTC [70] LOG: redo done at 0/16C9FC0
2018-10-19 23:17:54.151 UTC [70] LOG: last completed transaction was at log
time 2018-10-19 23:17:53.888672+00
2018-10-19 23:17:54.228 UTC [1] LOG: database system is ready to accept
connections
Happens every time with 100% reproducibility.
Work fine on postgres:10.5 docker tag.
It is just migration from
https://github.com/fabiocaccamo/django-admin-interface package.
Hi,
On 2018-10-19 23:20:10 +0000, PG Bug reporting form wrote:
The following bug has been logged on the website:
Bug reference: 15446
Logged by: Dmitry Molotkov
Email address: aldarund@gmail.com
PostgreSQL version: 11.0
Operating system: docker
Description:postgres via docker.
latest tag dont work.2018-10-19 23:17:53.903 UTC [1] LOG: server process (PID 69) was terminated
by signal 11: Segmentation fault
2018-10-19 23:17:53.903 UTC [1] DETAIL: Failed process was running: ALTER
TABLE "admin_interface_theme" ALTER COLUMN
"related_modal_background_opacity" TYPE varchar(5) USING
"related_modal_background_opacity"::varchar(5)
Unfortunately this is not enough information to reproduce the
problem. It'd be very helpful if you could provide a reproducible set of
commands that trigger the crash. Alternatively, should that prove hard,
the very least we're going to need the table definition of that
admin_interface_theme table, so we can attempt to reproduce the issue
ourselves.
Greetings,
Andres Freund
Hi, that was fast answer, thanks :)
Reproduce is easy if you can install python 3.
Here is empty django project that will reproduce it
https://www.dropbox.com/s/4rw2dlzxvxxb5xf/djangotestpgbug.zip?dl=0
install requirements from requirements file.
in djangotestpgbug\settings.py - set postgres user/password/database.
python manage.py migrate - it will run migration and it will crash
postgres:latest from docker. Didnt tried without docker. But i dont think
docker has anything to do with it.
Here is failing migration
https://github.com/fabiocaccamo/django-admin-interface/blob/master/admin_interface/migrations/0008_change_related_modal_background_opacity_type.py
сб, 20 окт. 2018 г. в 2:27, Andres Freund <andres@anarazel.de>:
Show quoted text
Hi,
On 2018-10-19 23:20:10 +0000, PG Bug reporting form wrote:
The following bug has been logged on the website:
Bug reference: 15446
Logged by: Dmitry Molotkov
Email address: aldarund@gmail.com
PostgreSQL version: 11.0
Operating system: docker
Description:postgres via docker.
latest tag dont work.2018-10-19 23:17:53.903 UTC [1] LOG: server process (PID 69) was
terminated
by signal 11: Segmentation fault
2018-10-19 23:17:53.903 UTC [1] DETAIL: Failed process was running:ALTER
TABLE "admin_interface_theme" ALTER COLUMN
"related_modal_background_opacity" TYPE varchar(5) USING
"related_modal_background_opacity"::varchar(5)Unfortunately this is not enough information to reproduce the
problem. It'd be very helpful if you could provide a reproducible set of
commands that trigger the crash. Alternatively, should that prove hard,
the very least we're going to need the table definition of that
admin_interface_theme table, so we can attempt to reproduce the issue
ourselves.Greetings,
Andres Freund
On 2018-Oct-20, Dmitry Molotkov wrote:
Reproduce is easy if you can install python 3.
Here is empty django project that will reproduce it
https://www.dropbox.com/s/4rw2dlzxvxxb5xf/djangotestpgbug.zip?dl=0
install requirements from requirements file.
in djangotestpgbug\settings.py - set postgres user/password/database.
python manage.py migrate - it will run migration and it will crash
postgres:latest from docker. Didnt tried without docker. But i dont think
docker has anything to do with it.Here is failing migration
https://github.com/fabiocaccamo/django-admin-interface/blob/master/admin_interface/migrations/0008_change_related_modal_background_opacity_type.py
I think the easiest is to turn DDL logging in the server
(log_statement=ddl) and then report exactly what is being sent to the
server, then see if you can construct a reproducer SQL script based on
that.
--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Here is query that make it crash
BEGIN;
--
-- Alter field related_modal_background_opacity on theme
--
ALTER TABLE "admin_interface_theme" ALTER COLUMN
"related_modal_background_opacity" TYPE varchar(5) USING
"related_modal_background_opacity"::varchar(5);
COMMIT;
And here all queries that was run before it
https://gist.github.com/aldarund/60c259a4011a5925b46bfcde47df7cb2
And here is table structure before failing migration
CREATE TABLE public.admin_interface_theme
(
id integer NOT NULL DEFAULT
nextval('admin_interface_theme_id_seq'::regclass),
name character varying(50) COLLATE pg_catalog."default" NOT NULL,
active boolean NOT NULL,
title character varying(50) COLLATE pg_catalog."default" NOT NULL,
title_visible boolean NOT NULL,
logo character varying(100) COLLATE pg_catalog."default" NOT NULL,
logo_visible boolean NOT NULL,
css_header_background_color character varying(18) COLLATE
pg_catalog."default" NOT NULL,
title_color character varying(18) COLLATE pg_catalog."default" NOT NULL,
css_header_text_color character varying(18) COLLATE
pg_catalog."default" NOT NULL,
css_header_link_color character varying(18) COLLATE
pg_catalog."default" NOT NULL,
css_header_link_hover_color character varying(18) COLLATE
pg_catalog."default" NOT NULL,
css_module_background_color character varying(18) COLLATE
pg_catalog."default" NOT NULL,
css_module_text_color character varying(18) COLLATE
pg_catalog."default" NOT NULL,
css_module_link_color character varying(18) COLLATE
pg_catalog."default" NOT NULL,
css_module_link_hover_color character varying(18) COLLATE
pg_catalog."default" NOT NULL,
css_module_rounded_corners boolean NOT NULL,
css_generic_link_color character varying(18) COLLATE
pg_catalog."default" NOT NULL,
css_generic_link_hover_color character varying(18) COLLATE
pg_catalog."default" NOT NULL,
css_save_button_background_color character varying(18) COLLATE
pg_catalog."default" NOT NULL,
css_save_button_background_hover_color character varying(18) COLLATE
pg_catalog."default" NOT NULL,
css_save_button_text_color character varying(18) COLLATE
pg_catalog."default" NOT NULL,
css_delete_button_background_color character varying(18) COLLATE
pg_catalog."default" NOT NULL,
css_delete_button_background_hover_color character varying(18) COLLATE
pg_catalog."default" NOT NULL,
css_delete_button_text_color character varying(18) COLLATE
pg_catalog."default" NOT NULL,
css text COLLATE pg_catalog."default" NOT NULL,
list_filter_dropdown boolean NOT NULL,
related_modal_active boolean NOT NULL,
related_modal_background_color character varying(18) COLLATE
pg_catalog."default" NOT NULL,
related_modal_background_opacity double precision NOT NULL,
related_modal_rounded_corners boolean NOT NULL,
logo_color character varying(18) COLLATE pg_catalog."default" NOT NULL,
recent_actions_visible boolean NOT NULL,
favicon character varying(100) COLLATE pg_catalog."default" NOT NULL,
CONSTRAINT admin_interface_theme_pkey PRIMARY KEY (id)
)
WITH (
OIDS = FALSE
)
пн, 22 окт. 2018 г. в 20:23, Alvaro Herrera <alvherre@2ndquadrant.com>:
Show quoted text
On 2018-Oct-20, Dmitry Molotkov wrote:
Reproduce is easy if you can install python 3.
Here is empty django project that will reproduce it
https://www.dropbox.com/s/4rw2dlzxvxxb5xf/djangotestpgbug.zip?dl=0
install requirements from requirements file.
in djangotestpgbug\settings.py - set postgres user/password/database.
python manage.py migrate - it will run migration and it will crash
postgres:latest from docker. Didnt tried without docker. But i dont think
docker has anything to do with it.Here is failing migration
I think the easiest is to turn DDL logging in the server
(log_statement=ddl) and then report exactly what is being sent to the
server, then see if you can construct a reproducer SQL script based on
that.--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 2018-Oct-27, Dmitry Molotkov wrote:
Here is query that make it crash
BEGIN;
--
-- Alter field related_modal_background_opacity on theme
--
ALTER TABLE "admin_interface_theme" ALTER COLUMN
"related_modal_background_opacity" TYPE varchar(5) USING
"related_modal_background_opacity"::varchar(5);
COMMIT;
Hmm, that works fine for me :-( Can't debug further right now ...
--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
I just checked with that statement and pgadmin and it still crash the db.
Its not related to my pc, since i initially encountered it on CI with
totally different os and setup, and then reproduced it locally.
https://i.imgur.com/qn1cyOz.png here what happen if i execute that alter
query in pgadmin
сб, 27 окт. 2018 г. в 4:11, Alvaro Herrera <alvherre@2ndquadrant.com>:
Show quoted text
On 2018-Oct-27, Dmitry Molotkov wrote:
Here is query that make it crash
BEGIN;
--
-- Alter field related_modal_background_opacity on theme
--
ALTER TABLE "admin_interface_theme" ALTER COLUMN
"related_modal_background_opacity" TYPE varchar(5) USING
"related_modal_background_opacity"::varchar(5);
COMMIT;Hmm, that works fine for me :-( Can't debug further right now ...
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 2018-Oct-27, Dmitry Molotkov wrote:
I just checked with that statement and pgadmin and it still crash the db.
Its not related to my pc, since i initially encountered it on CI with
totally different os and setup, and then reproduced it locally.
https://i.imgur.com/qn1cyOz.png here what happen if i execute that alter
query in pgadmin
It probably depends on other things in the table -- maybe indexes, or
foreign keys, or something else. If you can reproduce in psql or
pgadmin starting from an empty database and some DDL, please submit
that.
If not, can you attach a debugger to the process before it crashes, and
get a core file? There are some instructions here:
https://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Linux/BSD
Thanks
--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
I tried to reproduce it with DDL but no luck. I even tried to backup with
the state before crash and then restore but there wasn't error in such case
too.
I`m using windows, so i cant really follow your instruction for taking dump.
I guess the easiest would be to recreate it via that python .
сб, 27 окт. 2018 г. в 17:46, Alvaro Herrera <alvherre@2ndquadrant.com>:
Show quoted text
On 2018-Oct-27, Dmitry Molotkov wrote:
I just checked with that statement and pgadmin and it still crash the db.
Its not related to my pc, since i initially encountered it on CI with
totally different os and setup, and then reproduced it locally.
https://i.imgur.com/qn1cyOz.png here what happen if i execute that alter
query in pgadminIt probably depends on other things in the table -- maybe indexes, or
foreign keys, or something else. If you can reproduce in psql or
pgadmin starting from an empty database and some DDL, please submit
that.If not, can you attach a debugger to the process before it crashes, and
get a core file? There are some instructions here:https://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Linux/BSD
Thanks
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
That still happens in postgresql 11.1
сб, 27 окт. 2018 г. в 18:39, Dmitry Molotkov <aldarund@gmail.com>:
Show quoted text
I tried to reproduce it with DDL but no luck. I even tried to backup with
the state before crash and then restore but there wasn't error in such case
too.
I`m using windows, so i cant really follow your instruction for taking
dump.
I guess the easiest would be to recreate it via that python .сб, 27 окт. 2018 г. в 17:46, Alvaro Herrera <alvherre@2ndquadrant.com>:
On 2018-Oct-27, Dmitry Molotkov wrote:
I just checked with that statement and pgadmin and it still crash the
db.
Its not related to my pc, since i initially encountered it on CI with
totally different os and setup, and then reproduced it locally.
https://i.imgur.com/qn1cyOz.png here what happen if i execute thatalter
query in pgadmin
It probably depends on other things in the table -- maybe indexes, or
foreign keys, or something else. If you can reproduce in psql or
pgadmin starting from an empty database and some DDL, please submit
that.If not, can you attach a debugger to the process before it crashes, and
get a core file? There are some instructions here:https://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Linux/BSD
Thanks
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Hi,
On 2019-01-05 00:01:15 +0300, Dmitry Molotkov wrote:
That still happens in postgresql 11.1
Well, it's hard for us to fix that problem if we don't have a reliable
reproducer.
Greetings,
Andres Freund
I provided a reliable reproducer that reproduce it in 100% cases. But it
just involve python script..
сб, 5 янв. 2019 г. в 00:17, Andres Freund <andres@anarazel.de>:
Show quoted text
Hi,
On 2019-01-05 00:01:15 +0300, Dmitry Molotkov wrote:
That still happens in postgresql 11.1
Well, it's hard for us to fix that problem if we don't have a reliable
reproducer.Greetings,
Andres Freund
Hi,
Please don't top-quote on postgresql lists.
On 2019-01-05 00:46:54 +0300, Dmitry Molotkov wrote:
сб, 5 янв. 2019 г. в 00:17, Andres Freund <andres@anarazel.de>:
On 2019-01-05 00:01:15 +0300, Dmitry Molotkov wrote:
That still happens in postgresql 11.1
Well, it's hard for us to fix that problem if we don't have a reliable
reproducer.I provided a reliable reproducer that reproduce it in 100% cases. But it
just involve python script..
Having to download and run code from dropbox is neither trust-inspiring (I'd
have review all the included code before running it), nor low friction... You
might get somebody else to distil that down, but in all likelihood it'll take
longer. We all have more work than we can handle.
Greetings,
Andres Freund
сб, 5 янв. 2019 г. в 01:25, Andres Freund <andres@anarazel.de>:
Hi,
Please don't top-quote on postgresql lists.
On 2019-01-05 00:46:54 +0300, Dmitry Molotkov wrote:
сб, 5 янв. 2019 г. в 00:17, Andres Freund <andres@anarazel.de>:
On 2019-01-05 00:01:15 +0300, Dmitry Molotkov wrote:
That still happens in postgresql 11.1
Well, it's hard for us to fix that problem if we don't have a reliable
reproducer.I provided a reliable reproducer that reproduce it in 100% cases. But it
just involve python script..Having to download and run code from dropbox is neither trust-inspiring
(I'd
have review all the included code before running it), nor low friction...
You
might get somebody else to distil that down, but in all likelihood it'll
take
longer. We all have more work than we can handle.Greetings,
Andres Freund
Code is pretty much default empty django project with just with added
package that cause error.
I can create a github repo if dropbox not trust-inspiring or low friction
:)
On Sat, Jan 05, 2019 at 01:41:43AM +0300, Dmitry Molotkov wrote:
Code is pretty much default empty django project with just with added
package that cause error.
I can create a github repo if dropbox not trust-inspiring or low friction
:)
If you are able to create a reproducer which is made only of Python,
has only light dependencies with say psycopg2, and can be run on a
box, I am ready to buy it as a reproducer. If you cannot get a simple
thing, you may want to log the queries generated and extract a test
case from that because there should be only SQL involved in this
problem.
Then, please post the reproducer on this mailing list or just attach
it. This way, it can be seen and fetched again easily just using the
PostgreSQL community mail archives. If you post it only on github or
dropbox and if the source disappears (because companies go bankrupt
all the time), then we would lose it. I hope this makes sense.
--
Michael
Dmitry Molotkov <aldarund@gmail.com> writes:
сб, 5 янв. 2019 г. в 01:25, Andres Freund <andres@anarazel.de>:
Having to download and run code from dropbox is neither trust-inspiring
(I'd have review all the included code before running it), nor low friction...
Code is pretty much default empty django project with just with added
package that cause error.
It's the "django" part of that that is outside my comfort zone, and I'd
guess Andres' as well. I don't run django, and I'm not interested in
learning about it just to reproduce a bug report. As Andres said,
this would get looked at a lot quicker if the requirements to reproduce
it were low --- like, say, a small shell or perl or python script.
regards, tom lane
On Fri, Jan 4, 2019 at 11:54 PM Dmitry Molotkov <aldarund@gmail.com> wrote:
сб, 5 янв. 2019 г. в 01:25, Andres Freund <andres@anarazel.de>:
Hi,
Please don't top-quote on postgresql lists.
On 2019-01-05 00:46:54 +0300, Dmitry Molotkov wrote:
сб, 5 янв. 2019 г. в 00:17, Andres Freund <andres@anarazel.de>:
On 2019-01-05 00:01:15 +0300, Dmitry Molotkov wrote:
That still happens in postgresql 11.1
Well, it's hard for us to fix that problem if we don't have a reliable
reproducer.I provided a reliable reproducer that reproduce it in 100% cases. But it
just involve python script..Having to download and run code from dropbox is neither trust-inspiring
(I'd
have review all the included code before running it), nor low friction...
You
might get somebody else to distil that down, but in all likelihood it'll
take
longer. We all have more work than we can handle.Greetings,
Andres Freund
Code is pretty much default empty django project with just with added
package that cause error.
I can create a github repo if dropbox not trust-inspiring or low friction
:)
If all you are running is the django migrations and no other code, you
should be able to use the sqlmigrate command in django to turn it into a
series plain SQL files. Those will make it a lot easier for people here to
reproduce.
--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/>
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>
On Sat, 5 Jan 2019 at 00:38, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Dmitry Molotkov <aldarund@gmail.com> writes:
сб, 5 янв. 2019 г. в 01:25, Andres Freund <andres@anarazel.de>:
Having to download and run code from dropbox is neither trust-inspiring
(I'd have review all the included code before running it), nor low friction...Code is pretty much default empty django project with just with added
package that cause error.It's the "django" part of that that is outside my comfort zone, and I'd
guess Andres' as well. I don't run django, and I'm not interested in
learning about it just to reproduce a bug report. As Andres said,
this would get looked at a lot quicker if the requirements to reproduce
it were low --- like, say, a small shell or perl or python script.
Boiling down the queries from the links provided, I can reproduce this
with the following simple test case:
DROP TABLE IF EXISTS foo;
CREATE TABLE foo (a int);
ALTER TABLE foo ADD COLUMN b double precision DEFAULT 0.2;
ALTER TABLE foo ALTER COLUMN b TYPE varchar(5) USING b::varchar(5);
which crashes with the following:
#0 0x00007fca1c77515e in __memcpy_sse2_unaligned () from /lib64/libc.so.6
#1 0x0000000000921a6c in datumCopy (value=35278072, typByVal=false, typLen=-1)
at datum.c:159
#2 0x0000000000a2a56d in RelationBuildTupleDesc (relation=0x7fca1343bdc8)
at relcache.c:620
#3 0x0000000000a2b7a0 in RelationBuildDesc (targetRelId=16395, insertIt=false)
at relcache.c:1157
#4 0x0000000000a2df1c in RelationClearRelation (relation=0x7fca13439c88,
rebuild=true) at relcache.c:2446
#5 0x0000000000a2e460 in RelationFlushRelation (relation=0x7fca13439c88)
at relcache.c:2584
#6 0x0000000000a2e572 in RelationCacheInvalidateEntry (relationId=16395)
at relcache.c:2636
#7 0x0000000000a20fac in LocalExecuteInvalidationMessage (msg=0x2161710)
at inval.c:587
#8 0x0000000000a20d0c in ProcessInvalidationMessages (hdr=0x21612a0,
func=0xa20ea9 <LocalExecuteInvalidationMessage>) at inval.c:458
#9 0x0000000000a217dd in CommandEndInvalidationMessages () at inval.c:1093
#10 0x00000000005585c0 in AtCCI_LocalCache () at xact.c:1373
#11 0x0000000000557fd9 in CommandCounterIncrement () at xact.c:955
#12 0x000000000069f819 in ATExecAlterColumnType (tab=0x21a2370,
rel=0x7fca13439c88, cmd=0x21a34d0, lockmode=8) at tablecmds.c:9642
#13 0x0000000000693af6 in ATExecCmd (wqueue=0x7ffcb60097a8, tab=0x21a2370,
rel=0x7fca13439c88, cmd=0x21a34d0, lockmode=8) at tablecmds.c:4181
#14 0x00000000006933b1 in ATRewriteCatalogs (wqueue=0x7ffcb60097a8, lockmode=8)
at tablecmds.c:4025
#15 0x0000000000692b9e in ATController (parsetree=0x21a00c8,
rel=0x7fca13439c88, cmds=0x21a3528, recurse=true, lockmode=8)
at tablecmds.c:3691
#16 0x00000000006928db in AlterTable (relid=16395, lockmode=8, stmt=0x21a00c8)
at tablecmds.c:3365
It looks like the problem was introduced in PG11 by 16828d5c02 (Fast
ALTER TABLE ADD COLUMN with a non-NULL default). The first ALTER TABLE
adds a new column with a non-null default, setting atthasmissing and
attmissingval. Then the second ALTER TABLE changes the type of the new
column, but it fails to update attmissingval to match, and thus it
falls over when trying to re-open the relation because the value in
attmissingval is no longer compatible with the attribute type.
Regards,
Dean
Hi,
On 2019-01-05 12:11:45 +0000, Dean Rasheed wrote:
It looks like the problem was introduced in PG11 by 16828d5c02 (Fast
ALTER TABLE ADD COLUMN with a non-NULL default). The first ALTER TABLE
adds a new column with a non-null default, setting atthasmissing and
attmissingval. Then the second ALTER TABLE changes the type of the new
column, but it fails to update attmissingval to match, and thus it
falls over when trying to re-open the relation because the value in
attmissingval is no longer compatible with the attribute type.
Paging Dr Dunstan.
Greetings,
Andres Freund
On 1/5/19 12:09 PM, Andres Freund wrote:
Hi,
On 2019-01-05 12:11:45 +0000, Dean Rasheed wrote:
It looks like the problem was introduced in PG11 by 16828d5c02 (Fast
ALTER TABLE ADD COLUMN with a non-NULL default). The first ALTER TABLE
adds a new column with a non-null default, setting atthasmissing and
attmissingval. Then the second ALTER TABLE changes the type of the new
column, but it fails to update attmissingval to match, and thus it
falls over when trying to re-open the relation because the value in
attmissingval is no longer compatible with the attribute type.Paging Dr Dunstan.
Investigating with this test case:
DROP TABLE IF EXISTS foo;
CREATE TABLE foo (a int);
ALTER TABLE foo ADD COLUMN b double precision DEFAULT 0.2;
ALTER TABLE foo ALTER COLUMN b TYPE varchar(5) USING b::varchar(5);
cheers
andrew
--
Andrew Dunstan https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 1/8/19 4:48 PM, Dean Rasheed wrote:
On Tue, 8 Jan 2019 at 19:34, Andrew Dunstan
<andrew.dunstan@2ndquadrant.com> wrote:Here's a patch that I think cures the problem.
Hmm, that doesn't quite work because the table might not actually be
rewritten as a result of the type change. For example:DROP TABLE IF EXISTS foo;
CREATE TABLE foo (a int);
INSERT INTO foo VALUES (1);
ALTER TABLE foo ADD COLUMN b varchar(10) DEFAULT 'xxx';
ALTER TABLE foo ALTER COLUMN b SET DEFAULT 'yyy';
INSERT INTO foo VALUES (2);
SELECT * FROM foo;
a | b
---+-----
1 | xxx
2 | yyy
(2 rows)ALTER TABLE foo ALTER COLUMN b TYPE varchar(20) USING b::varchar(20);
SELECT * FROM foo;
a | b
---+-----
1 |
2 | yyy
(2 rows)
Ouch, OK, looks like we need something more complex.
cheers
andrew
--
Andrew Dunstan https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Import Notes
Reply to msg id not found: CAEZATCVs0qAggyb7MGtab_OQ6LWiGW8gx00wQuuZqqVwASrQ@mail.gmail.com
On 1/8/19 7:41 PM, Andrew Dunstan wrote:
On 1/8/19 4:48 PM, Dean Rasheed wrote:
On Tue, 8 Jan 2019 at 19:34, Andrew Dunstan
<andrew.dunstan@2ndquadrant.com> wrote:Here's a patch that I think cures the problem.
Hmm, that doesn't quite work because the table might not actually be
rewritten as a result of the type change. For example:DROP TABLE IF EXISTS foo;
CREATE TABLE foo (a int);
INSERT INTO foo VALUES (1);
ALTER TABLE foo ADD COLUMN b varchar(10) DEFAULT 'xxx';
ALTER TABLE foo ALTER COLUMN b SET DEFAULT 'yyy';
INSERT INTO foo VALUES (2);
SELECT * FROM foo;
a | b
---+-----
1 | xxx
2 | yyy
(2 rows)ALTER TABLE foo ALTER COLUMN b TYPE varchar(20) USING b::varchar(20);
SELECT * FROM foo;
a | b
---+-----
1 |
2 | yyy
(2 rows)Ouch, OK, looks like we need something more complex.
Here's another attempt. For the rewrite case it kept the logic of the
previous patch to clear all the missing attributes, but if we're not
rewriting we reconstruct the missing value according to the new type
settings.
cheers
andrew
--
Andrew Dunstan https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Attachments:
fix-alter-column-type-missing-value-3.patchtext/x-patch; name=fix-alter-column-type-missing-value-3.patchDownload
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index c8c50e8c98..e72d0b43ed 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -9285,6 +9285,21 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel,
HeapTuple depTup;
ObjectAddress address;
+ /*
+ * Clear all the missing values if we're rewriting the table, since this
+ * renders them pointless.
+ */
+ if (tab->rewrite)
+ {
+ Relation newrel;
+
+ newrel = heap_open(RelationGetRelid(rel), NoLock);
+ RelationClearMissing(newrel);
+ relation_close(newrel, NoLock);
+ /* make sure we don't conflict with later attribute modifications */
+ CommandCounterIncrement();
+ }
+
attrelation = heap_open(AttributeRelationId, RowExclusiveLock);
/* Look up the target column */
@@ -9601,7 +9616,69 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel,
/*
* Here we go --- change the recorded column type and collation. (Note
* heapTup is a copy of the syscache entry, so okay to scribble on.)
+ * First fix up the missing value if any.
*/
+ if (attTup->atthasmissing)
+ {
+ Datum missingval;
+ bool missingNull;
+
+ /* if rewrite is true the missing value should already be cleared */
+ Assert(tab->rewrite == 0);
+
+ /* Get the missing value datum */
+ missingval = heap_getattr(heapTup,
+ Anum_pg_attribute_attmissingval,
+ attrelation->rd_att,
+ &missingNull);
+
+ /* if it's a null array there is nothing to do */
+
+ if (! missingNull)
+ {
+ /*
+ * Get the datum out of the array and repack it in a new array
+ * built with the new type data. We assume that since the table
+ * doesn't need rewriting, the actual Datum doesn't need to be
+ * changed, only the array metadata.
+ */
+
+ int one = 1;
+ bool isNull;
+ Datum valuesAtt[Natts_pg_attribute];
+ bool nullsAtt[Natts_pg_attribute];
+ bool replacesAtt[Natts_pg_attribute];
+
+ MemSet(valuesAtt, 0, sizeof(valuesAtt));
+ MemSet(nullsAtt, false, sizeof(nullsAtt));
+ MemSet(replacesAtt, false, sizeof(replacesAtt));
+
+ missingval = array_get_element(missingval,
+ 1,
+ &one,
+ 0,
+ attTup->attlen,
+ attTup->attbyval,
+ attTup->attalign,
+ &isNull);
+ missingval = PointerGetDatum(
+ construct_array(&missingval,
+ 1,
+ targettype,
+ tform->typlen,
+ tform->typbyval,
+ tform->typalign));
+
+ valuesAtt[Anum_pg_attribute_attmissingval - 1] = missingval;
+ replacesAtt[Anum_pg_attribute_attmissingval - 1] = true;
+ nullsAtt[Anum_pg_attribute_attmissingval - 1] = false;
+
+ heapTup = heap_modify_tuple(heapTup, RelationGetDescr(attrelation),
+ valuesAtt, nullsAtt, replacesAtt);
+ attTup = (Form_pg_attribute) GETSTRUCT(heapTup);
+ }
+ }
+
attTup->atttypid = targettype;
attTup->atttypmod = targettypmod;
attTup->attcollation = targetcollid;
diff --git a/src/test/regress/expected/fast_default.out b/src/test/regress/expected/fast_default.out
index 1c1924cd5c..40a15bd2d6 100644
--- a/src/test/regress/expected/fast_default.out
+++ b/src/test/regress/expected/fast_default.out
@@ -735,7 +735,44 @@ INSERT INTO leader VALUES (1, 1), (2, 2);
ALTER TABLE leader ADD c int;
ALTER TABLE leader DROP c;
DELETE FROM leader;
+-- check that ALTER TABLE ... ALTER TYPE does the right thing
+CREATE TABLE vtype( a integer);
+INSERT INTO vtype VALUES (1);
+ALTER TABLE vtype ADD COLUMN b DOUBLE PRECISION DEFAULT 0.2;
+ALTER TABLE vtype ADD COLUMN c BOOLEAN DEFAULT true;
+SELECT * FROM vtype;
+ a | b | c
+---+-----+---
+ 1 | 0.2 | t
+(1 row)
+
+ALTER TABLE vtype
+ ALTER b TYPE text USING b::text,
+ ALTER c TYPE text USING c::text;
+NOTICE: rewriting table vtype for reason 4
+SELECT * FROM vtype;
+ a | b | c
+---+-----+------
+ 1 | 0.2 | true
+(1 row)
+
+-- also check the case that doesn't rewrite the table
+CREATE TABLE vtype2 (a int);
+INSERT INTO vtype2 VALUES (1);
+ALTER TABLE vtype2 ADD COLUMN b varchar(10) DEFAULT 'xxx';
+ALTER TABLE vtype2 ALTER COLUMN b SET DEFAULT 'yyy';
+INSERT INTO vtype2 VALUES (2);
+ALTER TABLE vtype2 ALTER COLUMN b TYPE varchar(20) USING b::varchar(20);
+SELECT * FROM vtype2;
+ a | b
+---+-----
+ 1 | xxx
+ 2 | yyy
+(2 rows)
+
-- cleanup
+DROP TABLE vtype;
+DROP TABLE vtype2;
DROP TABLE follower;
DROP TABLE leader;
DROP FUNCTION test_trigger();
diff --git a/src/test/regress/sql/fast_default.sql b/src/test/regress/sql/fast_default.sql
index 344b5841d7..0f65a79c7f 100644
--- a/src/test/regress/sql/fast_default.sql
+++ b/src/test/regress/sql/fast_default.sql
@@ -481,7 +481,33 @@ ALTER TABLE leader ADD c int;
ALTER TABLE leader DROP c;
DELETE FROM leader;
+-- check that ALTER TABLE ... ALTER TYPE does the right thing
+
+CREATE TABLE vtype( a integer);
+INSERT INTO vtype VALUES (1);
+ALTER TABLE vtype ADD COLUMN b DOUBLE PRECISION DEFAULT 0.2;
+ALTER TABLE vtype ADD COLUMN c BOOLEAN DEFAULT true;
+SELECT * FROM vtype;
+ALTER TABLE vtype
+ ALTER b TYPE text USING b::text,
+ ALTER c TYPE text USING c::text;
+SELECT * FROM vtype;
+
+-- also check the case that doesn't rewrite the table
+
+CREATE TABLE vtype2 (a int);
+INSERT INTO vtype2 VALUES (1);
+ALTER TABLE vtype2 ADD COLUMN b varchar(10) DEFAULT 'xxx';
+ALTER TABLE vtype2 ALTER COLUMN b SET DEFAULT 'yyy';
+INSERT INTO vtype2 VALUES (2);
+
+ALTER TABLE vtype2 ALTER COLUMN b TYPE varchar(20) USING b::varchar(20);
+SELECT * FROM vtype2;
+
+
-- cleanup
+DROP TABLE vtype;
+DROP TABLE vtype2;
DROP TABLE follower;
DROP TABLE leader;
DROP FUNCTION test_trigger();
On Wed, 9 Jan 2019 at 23:24, Andrew Dunstan
<andrew.dunstan@2ndquadrant.com> wrote:
Here's another attempt. For the rewrite case it kept the logic of the
previous patch to clear all the missing attributes, but if we're not
rewriting we reconstruct the missing value according to the new type
settings.
Looks good to me.
Regards,
Dean