Обсуждение: [BUGS] pg_dump and pg_restore on inherited tables problem
Hello, i think there is a problem with some specify situation when source database was set different default value between main and inherited table. This test was created on:
OS: Windows 10 Pro
Machine:
- Intel(R) Core(TM) i5-2500K CPU @ 3.30 GHz 3.30 GHz
- RAM 8GB
- x64 architecture
Database: PostgreSQL 9.6.5, compiled by Visual C++ build 1800, 64-bit
What to do is in the todo.txt
Best regards,
Piotr
Вложения
Piotr S <stegiszn@gmail.com> writes: > Hello, i think there is a problem with some specify situation when source > database was set different default value between main and inherited table. The problem here is that your child table doesn't have a not-null constraint on the inh_level column, while the parent does. That's a logically inconsistent situation, and it's not really pg_dump's fault that it fails to reproduce it exactly. We've fixed that problem in v10: now, the child table inherits the not-null constraint when you do the ALTER ADD PRIMARY KEY. So your setup script fails to insert the inconsistent data: d1=# INSERT INTO test_otw(id, description) VALUES (1, 'some test row'); ERROR: null value in column "inh_level" violates not-null constraint DETAIL: Failing row contains (1, null, some test row). regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
On Tue, Sep 5, 2017 at 12:24 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > We've fixed that problem in v10: now, the child table inherits the > not-null constraint when you do the ALTER ADD PRIMARY KEY. Here is the result commit, with a reference to the thread: commit: c30f1770a93db1492755934048656ea809c1f569 author: Tom Lane <tgl@sss.pgh.pa.us> date: Fri, 4 Aug 2017 11:45:18 -0400 Apply ALTER ... SET NOT NULL recursively in ALTER ... ADD PRIMARY KEY. If you do ALTER COLUMN SET NOT NULL against an inheritance parent table, it will recurse to mark all the child columns as NOT NULL as well. This is necessary for consistency: if the column is labeled NOT NULL then reading it should never produce nulls. However, that didn't happen in the case where ALTER ... ADD PRIMARY KEY marks a target column NOT NULL that wasn't before. That was questionable from the beginning, and now Tushar Ahuja points out that it can lead to dump/restore failures in some cases. So let's make that case recurse too. Although this is meant to fix a bug, it's enough of a behavioral change that I'm pretty hesitant to back-patch, especially in view of the lack of similar field complaints. It doesn't seem to be too late to put it into v10 though. Michael Paquier, editorialized on slightly by me Discussion: https://postgr.es/m/b8794d6a-38f0-9d7c-ad4b-e85adf860fc9@enterprisedb.com -- Michael -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs