Re: [HACKERS] pg_upgrade failed with error - ERROR: column "a" inchild table must be marked NOT NULL
От | Ali Akbar |
---|---|
Тема | Re: [HACKERS] pg_upgrade failed with error - ERROR: column "a" inchild table must be marked NOT NULL |
Дата | |
Msg-id | CACQjQLrQn8QkEYnURfJ00zVu-CXtcsMBYFUKHadv1H5kdRz9MQ@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: [HACKERS] pg_upgrade failed with error - ERROR: column "a" inchild table must be marked NOT NULL (Michael Paquier <michael.paquier@gmail.com>) |
Ответы |
Re: [HACKERS] pg_upgrade failed with error - ERROR: column "a" inchild table must be marked NOT NULL
(Ali Akbar <the.apaan@gmail.com>)
Re: [HACKERS] pg_upgrade failed with error - ERROR: column "a" inchild table must be marked NOT NULL (Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>) |
Список | pgsql-hackers |
Just stumbled across the same issues while upgrading one of my cluster to Pg 10 with pg_upgrade. Finished the upgrade by fixing the old database(s) and re-running pg_upgrade. 2017-08-04 23:06 GMT+07:00 Michael Paquier <michael.paquier@gmail.com>: > > On Fri, Aug 4, 2017 at 5:50 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > > Michael Paquier <michael.paquier@gmail.com> writes: > >> So I think that the attached patch is able to do the legwork. > > > > I've pushed this into HEAD. It seems like enough of a behavioral > > change that we wouldn't want to back-patch, but IMO it's not too late > > to be making this type of change in v10. If we wait for the next CF > > then it will take another year for the fix to reach the field. > > Thanks for applying the fix. My intention when adding that in a CF is > not to see things lost. Thans for the fix. Just found some issues: 1. My old database schema becomes like that by accidental modification on the child table, and on HEAD, it still works: # create table parent (id serial PRIMARY KEY, name VARCHAR(52) NOT NULL); CREATE TABLE # create table child () inherits (parent); CREATE TABLE # alter table child alter column name drop not null; ALTER TABLE # \d parent Table "public.parent" Column | Type | Collation | Nullable | Default --------+-----------------------+-----------+----------+------------------------------------ id | integer | | not null | nextval('parent_id_seq'::regclass) name | character varying(52) | | not null | Indexes: "parent_pkey" PRIMARY KEY, btree (id) Number of child tables: 1 (Use \d+ to list them.) # \d child Table "public.child" Column | Type | Collation | Nullable | Default --------+-----------------------+-----------+----------+------------------------------------ id | integer | | not null | nextval('parent_id_seq'::regclass) name | character varying(52) | | | Inherits: parent 2. If we execute pg_dump manually, it silently corrects the schema: ..... (cut) -- -- Name: parent; Type: TABLE; Schema: public; Owner: - -- CREATE TABLE parent ( id integer NOT NULL, name character varying(52) NOT NULL ); -- -- Name: child; Type: TABLE; Schema: public; Owner: - -- CREATE TABLE child ( ) INHERITS (parent); ...... (cut) There is't any DROP NOT NULL there. For me, it's better to prevent that from happening. So, attempts to DROP NOT NULL on the child must be rejected. The attached patch does that. Unfortunately, pg_class has no "has_parent" attribute, so in this patch, it hits pg_inherits everytime DROP NOT NULL is attempted. Notes: - It looks like we could remove the parent partition checking above? Because the new check already covers what it does - If this patch will be applied, i will work on pg_upgrade to check for this problem before attempting to dump schema. In my case, because the cluster has many databases, the error arise much late in the process, it will be much better if pg_upgrade complains while performing pre-checks. Best Regards, Ali Akbar
Вложения
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Michael PaquierДата:
Сообщение: Re: proposal: alternative psql commands quit and exit
Следующее
От: Ali AkbarДата:
Сообщение: Re: [HACKERS] pg_upgrade failed with error - ERROR: column "a" inchild table must be marked NOT NULL