Thread: Problems with pg_upgrade after change of unix user running db.
I got this error trying to upgrade one of our database clusters (happily in testing) from 9.2 to 9.4:
Old and new cluster install users have different values for pg_authid.oid
Important background here is that we used to run the database as the postgres unix user, but recently we had changed it to run as a different user (because we have several different databases all running as the postgres user on different machines and we wanted each logically separate database to run as a different extra for that purpose unix user -- this simplified internal administration management).
We had done this by adding a new superuser to the database (with the name of the unix user it will run as in the future). turning off the database, chown -R <new-user> databasedir, starting the database
(and adapting the startup scripts accordingly).
What I wasn't aware of is that postgres knows which user was used to run pg_init.
So my first attempt at upgrading by running the below as the new user
old_loc=/mnt/dbc03-d1/proddb/postgres
new_loc=/mnt/dbc03-d1/proddb-94/postgres
rm -rf $new_loc/*
/usr/pgsql-9.4/bin/initdb $new_loc
self-service stop postgresql-9.2
/usr/pgsql-9.4/bin/pg_upgrade \
-k \
-j 8 \
--old-datadir $old_loc \
--new-datadir $new_loc \
--old-bindir /usr/pgsql-9.2/bin \
--new-bindir /usr/pgsql-9.4/bin
Failed with the above "Old and new cluster ..."
In my next attempt I tried adding the bold to the initdb command
/usr/pgsql-9.4/bin/initdb $new_loc -U postgres
But that eventually fails during pg_upgrade with:
connection to database failed: FATAL role "<new-user>" does not exist
could not connect to new postmaster started with the command:
/usr/pgsql-9.4/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/mnt/dbc03-d1/proddb-94/...
last thing it prints before that is
Creating dump of database schemas
<databases>
ok
So I would love to know what the recommended way to go forward is. Ideally it avoids using the old postgres unix
and database user (we want to completely get rid of it eventually, but if I have to do some additional one off work this
time to get past this hurdle using the user that is of course an acceptable trade off).
Thanks in advance,
Bene
On 11/23/15 5:12 AM, Benedikt Grundmann wrote: > So I would love to know what the recommended way to go forward is. > Ideally it avoids using the old postgres unix > and database user (we want to completely get rid of it eventually, but > if I have to do some additional one off work this > time to get past this hurdle using the user that is of course an > acceptable trade off). Can you provide a test script that demonstrates the problem? The unix user that runs the database processes is pretty inconsequential to Postgres (as long as it's not root). There's generally no need for any correlation between unix accounts and database accounts. initdb uses the unix account running initdb for the initial database account, but as you've seen you can over-ride that. pg_upgrade does care about unix accounts though (since it's dealing directly with the file system). It also needs to be able to connect to both databases, so it needs valid database login credentials as well. -- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com
On Mon, Nov 23, 2015 at 11:12:25AM +0000, Benedikt Grundmann wrote:
> I got this error trying to upgrade one of our database clusters (happily in
> testing) from 9.2 to 9.4:
>
> Old and new cluster install users have different values for pg_authid.oid
>
> Important background here is that we used to run the database as the postgres
> unix user, but recently we had changed it to run as a different user (because
> we have several different databases all running as the postgres user on
> different machines and we wanted each logically separate database to run as a
> different extra for that purpose unix user -- this simplified internal
> administration management).
>
> We had done this by adding a new superuser to the database (with the name of
> the unix user it will run as in the future). turning off the database, chown -R
> <new-user> databasedir, starting the database
Your description is very clear. In 9.4 and earlier, Postgres checks
that the user running upgrade has the same pg_authid.oid in the old and
new clusters. In 9.5 we check that the user is the
BOOTSTRAP_SUPERUSERID (10) on both the old and new cluster.
Therefore, what I suggest you do, before running pg_upgrade, is to
rename the pg_authid.oid = 10 row to be your new install user instead of
'postgres', and make your new user row equal 'postgres', e.g. something
like:
-- You already did this first one
--> test=> create user my_new_install_user;
--> CREATE ROLE
select oid from pg_authid where rolname = 'my_new_install_user';
oid
-------
16385
(1 row)
select oid from pg_authid where rolname = 'postgres';
oid
-----
10
(1 row)
-- 'XXX' prevents duplicate names
update pg_authid set rolname = 'XXX' where oid = 10;
UPDATE 1
update pg_authid set rolname = 'postgres' where oid = 16385;
UPDATE 1
update pg_authid set rolname = 'my_new_install_user' where oid = 10;
UPDATE 1
What this does it to make your new install user the bootstrap user,
which is a requirement for 9.5 pg_upgrade. You would do this _before_
running pg_upgrade as my_new_install_user. However, keep in mind that
once you do this, everthing owned by my_new_install_user and postgres
are now swapped. This is basically what you need to do after changing
the ownership of the Postgres file system files.
You can see the 9.5 requirements in the pg_upgrade function
check_is_install_user(). You might as well just honor what that
requires as you will eventually be moving to 9.5.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Roman grave inscription +
On Tue, Nov 24, 2015 at 8:04 PM, Bruce Momjian <bruce@momjian.us> wrote:
On Mon, Nov 23, 2015 at 11:12:25AM +0000, Benedikt Grundmann wrote:
> I got this error trying to upgrade one of our database clusters (happily in
> testing) from 9.2 to 9.4:
>
> Old and new cluster install users have different values for pg_authid.oid
>
> Important background here is that we used to run the database as the postgres
> unix user, but recently we had changed it to run as a different user (because
> we have several different databases all running as the postgres user on
> different machines and we wanted each logically separate database to run as a
> different extra for that purpose unix user -- this simplified internal
> administration management).
>
> We had done this by adding a new superuser to the database (with the name of
> the unix user it will run as in the future). turning off the database, chown -R
> <new-user> databasedir, starting the database
Your description is very clear. In 9.4 and earlier, Postgres checks
that the user running upgrade has the same pg_authid.oid in the old and
new clusters. In 9.5 we check that the user is the
BOOTSTRAP_SUPERUSERID (10) on both the old and new cluster.
Therefore, what I suggest you do, before running pg_upgrade, is to
rename the pg_authid.oid = 10 row to be your new install user instead of
'postgres', and make your new user row equal 'postgres', e.g. something
like:
-- You already did this first one
--> test=> create user my_new_install_user;
--> CREATE ROLE
select oid from pg_authid where rolname = 'my_new_install_user';
oid
-------
16385
(1 row)
select oid from pg_authid where rolname = 'postgres';
oid
-----
10
(1 row)
-- 'XXX' prevents duplicate names
update pg_authid set rolname = 'XXX' where oid = 10;
UPDATE 1
update pg_authid set rolname = 'postgres' where oid = 16385;
UPDATE 1
update pg_authid set rolname = 'my_new_install_user' where oid = 10;
UPDATE 1
What this does it to make your new install user the bootstrap user,
which is a requirement for 9.5 pg_upgrade. You would do this _before_
running pg_upgrade as my_new_install_user. However, keep in mind that
once you do this, everthing owned by my_new_install_user and postgres
are now swapped. This is basically what you need to do after changing
the ownership of the Postgres file system files.
You can see the 9.5 requirements in the pg_upgrade function
check_is_install_user(). You might as well just honor what that
requires as you will eventually be moving to 9.5.
Thanks I'll try this in one of the next days. Sorry for the radio silence in the last 2 days. We have been quite busy at work. I don't think I understand yet why this restriction exists (Neither the old nor the new). Is there some doc somewhere that explains what's going on? I tried to find something in the otherwise excellent postgres docs but failed.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Roman grave inscription +
On Wed, Nov 25, 2015 at 08:04:49AM +0000, Benedikt Grundmann wrote: > You can see the 9.5 requirements in the pg_upgrade function > check_is_install_user(). You might as well just honor what that > requires as you will eventually be moving to 9.5. > > > Thanks I'll try this in one of the next days. Sorry for the radio silence in > the last 2 days. We have been quite busy at work. I don't think I understand Sure, no problem. I would have liked to reply to this sooner too, but had to do some research. > yet why this restriction exists (Neither the old nor the new). Is there some > doc somewhere that explains what's going on? I tried to find something in the > otherwise excellent postgres docs but failed. The comments at the top of pg_upgrade.c do explain this: * To simplify the upgrade process, we force certain system values to be * identical between old and new clusters: * * We control all assignments of pg_class.oid (and relfilenode) so toast * oids are the same between old and new clusters. This is important * because toast oids are stored as toast pointers in user tables. * * While pg_class.oid and pg_class.relfilenode are initially the same * in a cluster, they can diverge due to CLUSTER, REINDEX, or VACUUM * FULL. In the new cluster, pg_class.oid and pg_class.relfilenode will * be the same and will match the old pg_class.oid value. Because of * this, old/new pg_class.relfilenode values will not match if CLUSTER, * REINDEX, or VACUUM FULL have been performed in the old cluster. * * We control all assignments of pg_type.oid because these oids are stored * in user composite type values. * * We control all assignments of pg_enum.oid because these oids are stored * in user tables as enum values. * * We control all assignments of pg_authid.oid because these oids are stored <--- * in pg_largeobject_metadata. <--- I never expected users to care, but based on what you did, you obviously did need to care. The good news is that the system generated an error message that helped diagnose the problem, and the 9.5 error message is much clearer. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Roman grave inscription +
On Wed, Nov 25, 2015 at 2:43 PM, Bruce Momjian <bruce@momjian.us> wrote:
On Wed, Nov 25, 2015 at 08:04:49AM +0000, Benedikt Grundmann wrote:
> You can see the 9.5 requirements in the pg_upgrade function
> check_is_install_user(). You might as well just honor what that
> requires as you will eventually be moving to 9.5.
>
>
> Thanks I'll try this in one of the next days. Sorry for the radio silence in
> the last 2 days. We have been quite busy at work. I don't think I understand
Sure, no problem. I would have liked to reply to this sooner too, but
had to do some research.
That worked (I also swapped the password columns so that I don't have to change pgpass entries). But I then ran into a different problem a little later on. I thought I quickly mention it here in case somebody can point me into the right direction:
...
Performing Upgrade
------------------
Analyzing all rows in the new cluster ok
Freezing all rows on the new cluster ok
Deleting files from new pg_clog ok
Copying old pg_clog to new server ok
Setting next transaction ID and epoch for new cluster ok
Deleting files from new pg_multixact/offsets ok
Setting oldest multixact ID on new cluster ok
Resetting WAL archives ok
Setting frozenxid and minmxid counters in new cluster ok
Restoring global objects in the new cluster ok
Adding support functions to new cluster ok
Restoring database schemas in the new cluster
*failure*
Consult the last few lines of "pg_upgrade_dump_16416.log" for
the probable cause of the failure.
child worker exited abnormally: Invalid argument
*failure*
Consult the last few lines of "pg_upgrade_server.log" for
the probable cause of the failure.
[as-proddb@nyc-dbc-001 upgrade-logs]$ tail pg_upgrade_dump_16416.log
pg_restore: creating CHECK CONSTRAINT seqno_not_null
pg_restore: creating CHECK CONSTRAINT seqno_not_null
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 8359; 2606 416548282 CHECK CONSTRAINT seqno_not_null postgres_prod
pg_restore: [archiver (db)] could not execute query: ERROR: constraint "seqno_not_null" for relation "js_activity_2011" already exists
Command was: ALTER TABLE "js_activity_2011"
ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT VALID;
Now js_activity is the parent table and js_activity_* are all child tables (for partitioning):
postgres_prod@proddb_testing=# select c.conname, c.conislocal, c.coninhcount, c.convalidated as valid, (select relname from pg_class where oid = c.conrelid) from pg_constraint c where conname = 'seqno_not_null' order by relname;
conname │ conislocal │ coninhcount │ valid │ relname
────────────────┼────────────┼─────────────┼───────┼──────────────────
seqno_not_null │ t │ 0 │ f │ js_activity
seqno_not_null │ t │ 1 │ f │ js_activity_2009
seqno_not_null │ t │ 1 │ f │ js_activity_2010
seqno_not_null │ t │ 1 │ f │ js_activity_2011
seqno_not_null │ f │ 1 │ f │ js_activity_2012
seqno_not_null │ f │ 1 │ t │ js_activity_2013
seqno_not_null │ f │ 1 │ t │ js_activity_2014
seqno_not_null │ f │ 1 │ f │ js_activity_tip
[as-proddb@nyc-dbc-001 upgrade-logs]$ pg_restore pg_upgrade_dump_16416.custom | fgrep seqno_not_null -B 1
-- For binary upgrade, set up inherited constraint.
ALTER TABLE ONLY "js_activity_2013" ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL));
--
SET conislocal = false
WHERE contype = 'c' AND conname = 'seqno_not_null'
--
-- For binary upgrade, set up inherited constraint.
ALTER TABLE ONLY "js_activity_2014" ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL));
--
SET conislocal = false
WHERE contype = 'c' AND conname = 'seqno_not_null'
--
--
-- Name: seqno_not_null; Type: CHECK CONSTRAINT; Schema: public; Owner: postgres_prod
--
ALTER TABLE "js_activity"
ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT VALID;
--
--
-- Name: seqno_not_null; Type: CHECK CONSTRAINT; Schema: public; Owner: postgres_prod
--
ALTER TABLE "js_activity_2011"
ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT VALID;
--
--
-- Name: seqno_not_null; Type: CHECK CONSTRAINT; Schema: public; Owner: postgres_prod
--
ALTER TABLE "js_activity_2010"
ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT VALID;
--
--
-- Name: seqno_not_null; Type: CHECK CONSTRAINT; Schema: public; Owner: postgres_prod
--
ALTER TABLE "js_activity_2009"
ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT VALID;
Again thanks in advance,
Bene
On Fri, Nov 27, 2015 at 09:38:54AM +0000, Benedikt Grundmann wrote:
> That worked (I also swapped the password columns so that I don't have to change
> pgpass entries). But I then ran into a different problem a little later on. I
> thought I quickly mention it here in case somebody can point me into the right
> direction:
>
...
> Restoring database schemas in the new cluster
>
> *failure*
> Consult the last few lines of "pg_upgrade_dump_16416.log" for
> the probable cause of the failure.
> child worker exited abnormally: Invalid argument
>
> *failure*
> Consult the last few lines of "pg_upgrade_server.log" for
> the probable cause of the failure.
>
> [as-proddb@nyc-dbc-001 upgrade-logs]$ tail pg_upgrade_dump_16416.log
> pg_restore: creating CHECK CONSTRAINT seqno_not_null
> pg_restore: creating CHECK CONSTRAINT seqno_not_null
> pg_restore: [archiver (db)] Error while PROCESSING TOC:
> pg_restore: [archiver (db)] Error from TOC entry 8359; 2606 416548282 CHECK
> CONSTRAINT seqno_not_null postgres_prod
> pg_restore: [archiver (db)] could not execute query: ERROR: constraint
> "seqno_not_null" for relation "js_activity_2011" already exists
> Command was: ALTER TABLE "js_activity_2011"
> ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT VALID;
I have no idea, but this is a pg_dump bug or inconsistent system tables,
rather than a pg_upgrade-specific bug.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Roman grave inscription +
On Fri, Nov 27, 2015 at 4:04 PM, Bruce Momjian <bruce@momjian.us> wrote:
On Fri, Nov 27, 2015 at 09:38:54AM +0000, Benedikt Grundmann wrote:
> That worked (I also swapped the password columns so that I don't have to change
> pgpass entries). But I then ran into a different problem a little later on. I
> thought I quickly mention it here in case somebody can point me into the right
> direction:
>
...
> Restoring database schemas in the new cluster
>
> *failure*
> Consult the last few lines of "pg_upgrade_dump_16416.log" for
> the probable cause of the failure.
> child worker exited abnormally: Invalid argument
>
> *failure*
> Consult the last few lines of "pg_upgrade_server.log" for
> the probable cause of the failure.
>
> [as-proddb@nyc-dbc-001 upgrade-logs]$ tail pg_upgrade_dump_16416.log
> pg_restore: creating CHECK CONSTRAINT seqno_not_null
> pg_restore: creating CHECK CONSTRAINT seqno_not_null
> pg_restore: [archiver (db)] Error while PROCESSING TOC:
> pg_restore: [archiver (db)] Error from TOC entry 8359; 2606 416548282 CHECK
> CONSTRAINT seqno_not_null postgres_prod
> pg_restore: [archiver (db)] could not execute query: ERROR: constraint
> "seqno_not_null" for relation "js_activity_2011" already exists
> Command was: ALTER TABLE "js_activity_2011"
> ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT VALID;
I have no idea, but this is a pg_dump bug or inconsistent system tables,
rather than a pg_upgrade-specific bug.
Any recommendation on how to proceed?
On Fri, Nov 27, 2015 at 04:05:46PM +0000, Benedikt Grundmann wrote:
> > [as-proddb@nyc-dbc-001 upgrade-logs]$ tail pg_upgrade_dump_16416.log
> > pg_restore: creating CHECK CONSTRAINT seqno_not_null
> > pg_restore: creating CHECK CONSTRAINT seqno_not_null
> > pg_restore: [archiver (db)] Error while PROCESSING TOC:
> > pg_restore: [archiver (db)] Error from TOC entry 8359; 2606 416548282
> CHECK
> > CONSTRAINT seqno_not_null postgres_prod
> > pg_restore: [archiver (db)] could not execute query: ERROR: constraint
> > "seqno_not_null" for relation "js_activity_2011" already exists
> > Command was: ALTER TABLE "js_activity_2011"
> > ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT
> VALID;
>
> I have no idea, but this is a pg_dump bug or inconsistent system tables,
> rather than a pg_upgrade-specific bug.
>
>
> Any recommendation on how to proceed?
My guess is you are sharing the constraint name "seqno_not_null" with
multiple tables. I think you are going to have to dig into the system
tables to see where that is referenced and fix it.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Roman grave inscription +
On 11/27/2015 08:05 AM, Benedikt Grundmann wrote:
>
>
> On Fri, Nov 27, 2015 at 4:04 PM, Bruce Momjian <bruce@momjian.us
> <mailto:bruce@momjian.us>> wrote:
>
> On Fri, Nov 27, 2015 at 09:38:54AM +0000, Benedikt Grundmann wrote:
> > That worked (I also swapped the password columns so that I don't have to change
> > pgpass entries). But I then ran into a different problem a little later on. I
> > thought I quickly mention it here in case somebody can point me into the right
> > direction:
> >
> ...
> > Restoring database schemas in the new cluster
> >
> > *failure*
> > Consult the last few lines of "pg_upgrade_dump_16416.log" for
> > the probable cause of the failure.
> > child worker exited abnormally: Invalid argument
> >
> > *failure*
> > Consult the last few lines of "pg_upgrade_server.log" for
> > the probable cause of the failure.
> >
> > [as-proddb@nyc-dbc-001 upgrade-logs]$ tail pg_upgrade_dump_16416.log
> > pg_restore: creating CHECK CONSTRAINT seqno_not_null
> > pg_restore: creating CHECK CONSTRAINT seqno_not_null
> > pg_restore: [archiver (db)] Error while PROCESSING TOC:
> > pg_restore: [archiver (db)] Error from TOC entry 8359; 2606 416548282 CHECK
> > CONSTRAINT seqno_not_null postgres_prod
> > pg_restore: [archiver (db)] could not execute query: ERROR: constraint
> > "seqno_not_null" for relation "js_activity_2011" already exists
> > Command was: ALTER TABLE "js_activity_2011"
> > ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT VALID;
>
> I have no idea, but this is a pg_dump bug or inconsistent system tables,
> rather than a pg_upgrade-specific bug.
>
>
> Any recommendation on how to proceed?
>
Not sure that it matters, from one of your previous posts:
"
*failure*
Consult the last few lines of "pg_upgrade_server.log" for
the probable cause of the failure."
What do the last lines in pg_upgrade_server.log show?
--
Adrian Klaver
adrian.klaver@aklaver.com
On 11/27/2015 08:15 AM, Bruce Momjian wrote:
> On Fri, Nov 27, 2015 at 04:05:46PM +0000, Benedikt Grundmann wrote:
>> > [as-proddb@nyc-dbc-001 upgrade-logs]$ tail pg_upgrade_dump_16416.log
>> > pg_restore: creating CHECK CONSTRAINT seqno_not_null
>> > pg_restore: creating CHECK CONSTRAINT seqno_not_null
>> > pg_restore: [archiver (db)] Error while PROCESSING TOC:
>> > pg_restore: [archiver (db)] Error from TOC entry 8359; 2606 416548282
>> CHECK
>> > CONSTRAINT seqno_not_null postgres_prod
>> > pg_restore: [archiver (db)] could not execute query: ERROR: constraint
>> > "seqno_not_null" for relation "js_activity_2011" already exists
>> > Command was: ALTER TABLE "js_activity_2011"
>> > ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT
>> VALID;
>>
>> I have no idea, but this is a pg_dump bug or inconsistent system tables,
>> rather than a pg_upgrade-specific bug.
>>
>>
>> Any recommendation on how to proceed?
>
> My guess is you are sharing the constraint name "seqno_not_null" with
> multiple tables. I think you are going to have to dig into the system
> tables to see where that is referenced and fix it.
>
In the post below the OP shows the tables involved(they where inherited):
http://www.postgresql.org/message-id/CADbMkNM_y9ewdaWdQ_8DJ1mUC0Z_FGwTyAD2RwCHgExj2jvOHQ@mail.gmail.com
Still I did not think there was a restriction that constraint names be unique
across a database or cluster:
test=> create table tbl_a (fld_1 int CONSTRAINT fld_1_chk CHECK(fld_1 > 0) );
CREATE TABLE
test=> create table tbl_b (fld_1 int CONSTRAINT fld_1_chk CHECK(fld_1 > 0) );
CREATE TABLE
test=> \d tbl_*
Table "public.tbl_a"
Column | Type | Modifiers
--------+---------+-----------
fld_1 | integer |
Check constraints:
"fld_1_chk" CHECK (fld_1 > 0)
Table "public.tbl_b"
Column | Type | Modifiers
--------+---------+-----------
fld_1 | integer |
Check constraints:
"fld_1_chk" CHECK (fld_1 > 0)
--
Adrian Klaver
adrian.klaver@aklaver.com
Adrian Klaver <adrian.klaver@aklaver.com> writes: > On 11/27/2015 08:15 AM, Bruce Momjian wrote: >> My guess is you are sharing the constraint name "seqno_not_null" with >> multiple tables. I think you are going to have to dig into the system >> tables to see where that is referenced and fix it. > In the post below the OP shows the tables involved(they where inherited): > http://www.postgresql.org/message-id/CADbMkNM_y9ewdaWdQ_8DJ1mUC0Z_FGwTyAD2RwCHgExj2jvOHQ@mail.gmail.com Inherited eh? Maybe related to 074c5cfbf. regards, tom lane
On 11/27/2015 06:07 PM, Tom Lane wrote: > Adrian Klaver <adrian.klaver@aklaver.com> writes: >> On 11/27/2015 08:15 AM, Bruce Momjian wrote: >>> My guess is you are sharing the constraint name "seqno_not_null" with >>> multiple tables. I think you are going to have to dig into the system >>> tables to see where that is referenced and fix it. > >> In the post below the OP shows the tables involved(they where inherited): >> http://www.postgresql.org/message-id/CADbMkNM_y9ewdaWdQ_8DJ1mUC0Z_FGwTyAD2RwCHgExj2jvOHQ@mail.gmail.com > > Inherited eh? Maybe related to 074c5cfbf. From the OP's post: The error: pg_restore: creating CHECK CONSTRAINT seqno_not_null pg_restore: creating CHECK CONSTRAINT seqno_not_null pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 8359; 2606 416548282 CHECK CONSTRAINT seqno_not_null postgres_prod pg_restore: [archiver (db)] could not execute query: ERROR: constraint "seqno_not_null" for relation "js_activity_2011" already exists Command was: ALTER TABLE "js_activity_2011" ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT VALID; The setup: postgres_prod(at)proddb_testing=# select c.conname, c.conislocal, c.coninhcount, c.convalidated as valid, (select relname from pg_class where oid = c.conrelid) from pg_constraint c where conname = 'seqno_not_null' order by relname; conname │ conislocal │ coninhcount │ valid │ relname ────────────────┼────────────┼─────────────┼───────┼────────────────── seqno_not_null │ t │ 0 │ f │ js_activity seqno_not_null │ t │ 1 │ f │ js_activity_2009 seqno_not_null │ t │ 1 │ f │ js_activity_2010 seqno_not_null │ t │ 1 │ f │ js_activity_2011 seqno_not_null │ f │ 1 │ f │ js_activity_2012 seqno_not_null │ f │ 1 │ t │ js_activity_2013 seqno_not_null │ f │ 1 │ t │ js_activity_2014 seqno_not_null │ f │ 1 │ f │ js_activity_tip [as-proddb(at)nyc-dbc-001 upgrade-logs]$ pg_restore pg_upgrade_dump_16416.custom | fgrep seqno_not_null -B 1 -- For binary upgrade, set up inherited constraint. ALTER TABLE ONLY "js_activity_2013" ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)); -- SET conislocal = false WHERE contype = 'c' AND conname = 'seqno_not_null' -- -- For binary upgrade, set up inherited constraint. ALTER TABLE ONLY "js_activity_2014" ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)); -- SET conislocal = false WHERE contype = 'c' AND conname = 'seqno_not_null' -- -- -- Name: seqno_not_null; Type: CHECK CONSTRAINT; Schema: public; Owner: postgres_prod -- ALTER TABLE "js_activity" ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT VALID; -- -- -- Name: seqno_not_null; Type: CHECK CONSTRAINT; Schema: public; Owner: postgres_prod -- ALTER TABLE "js_activity_2011" ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT VALID; -- -- -- Name: seqno_not_null; Type: CHECK CONSTRAINT; Schema: public; Owner: postgres_prod -- ALTER TABLE "js_activity_2010" ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT VALID; -- -- -- Name: seqno_not_null; Type: CHECK CONSTRAINT; Schema: public; Owner: postgres_prod -- ALTER TABLE "js_activity_2009" ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT VALID; > > regards, tom lane > -- Adrian Klaver adrian.klaver@aklaver.com
On Sat, Nov 28, 2015 at 2:39 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 11/27/2015 06:07 PM, Tom Lane wrote:
> Adrian Klaver <adrian.klaver@aklaver.com> writes:
>> On 11/27/2015 08:15 AM, Bruce Momjian wrote:
>>> My guess is you are sharing the constraint name "seqno_not_null" with
>>> multiple tables. I think you are going to have to dig into the system
>>> tables to see where that is referenced and fix it.
>
>> In the post below the OP shows the tables involved(they where inherited):
>> http://www.postgresql.org/message-id/CADbMkNM_y9ewdaWdQ_8DJ1mUC0Z_FGwTyAD2RwCHgExj2jvOHQ@mail.gmail.com
>
> Inherited eh? Maybe related to 074c5cfbf.
I forgot to mention this earlier. This cluster is running 9.2.6 and I'm attempting to upgrade to the latest 9.4.5
From the OP's post:
The error:
pg_restore: creating CHECK CONSTRAINT seqno_not_null
pg_restore: creating CHECK CONSTRAINT seqno_not_null
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 8359; 2606 416548282 CHECK
CONSTRAINT seqno_not_null postgres_prod
pg_restore: [archiver (db)] could not execute query: ERROR: constraint
"seqno_not_null" for relation "js_activity_2011" already exists
Command was: ALTER TABLE "js_activity_2011"
ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT VALID;
The setup:
postgres_prod(at)proddb_testing=# select c.conname, c.conislocal,
c.coninhcount, c.convalidated as valid, (select relname from pg_class where
oid = c.conrelid) from pg_constraint c where conname = 'seqno_not_null'
order by relname;
conname │ conislocal │ coninhcount │ valid │ relname
────────────────┼────────────┼─────────────┼───────┼──────────────────
seqno_not_null │ t │ 0 │ f │ js_activity
seqno_not_null │ t │ 1 │ f │ js_activity_2009
seqno_not_null │ t │ 1 │ f │ js_activity_2010
seqno_not_null │ t │ 1 │ f │ js_activity_2011
seqno_not_null │ f │ 1 │ f │ js_activity_2012
seqno_not_null │ f │ 1 │ t │ js_activity_2013
seqno_not_null │ f │ 1 │ t │ js_activity_2014
seqno_not_null │ f │ 1 │ f │ js_activity_tip
[as-proddb(at)nyc-dbc-001 upgrade-logs]$ pg_restore
pg_upgrade_dump_16416.custom | fgrep seqno_not_null -B 1
-- For binary upgrade, set up inherited constraint.
ALTER TABLE ONLY "js_activity_2013" ADD CONSTRAINT "seqno_not_null" CHECK
(("seqno" IS NOT NULL));
--
SET conislocal = false
WHERE contype = 'c' AND conname = 'seqno_not_null'
--
-- For binary upgrade, set up inherited constraint.
ALTER TABLE ONLY "js_activity_2014" ADD CONSTRAINT "seqno_not_null" CHECK
(("seqno" IS NOT NULL));
--
SET conislocal = false
WHERE contype = 'c' AND conname = 'seqno_not_null'
--
--
-- Name: seqno_not_null; Type: CHECK CONSTRAINT; Schema: public; Owner:
postgres_prod
--
ALTER TABLE "js_activity"
ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT VALID;
--
--
-- Name: seqno_not_null; Type: CHECK CONSTRAINT; Schema: public; Owner:
postgres_prod
--
ALTER TABLE "js_activity_2011"
ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT VALID;
--
--
-- Name: seqno_not_null; Type: CHECK CONSTRAINT; Schema: public; Owner:
postgres_prod
--
ALTER TABLE "js_activity_2010"
ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT VALID;
--
--
-- Name: seqno_not_null; Type: CHECK CONSTRAINT; Schema: public; Owner:
postgres_prod
--
ALTER TABLE "js_activity_2009"
ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT VALID;
>
> regards, tom lane
>
--
Adrian Klaver
adrian.klaver@aklaver.com
On Mon, Nov 30, 2015 at 08:08:50AM +0000, Benedikt Grundmann wrote: > > > On Sat, Nov 28, 2015 at 2:39 AM, Adrian Klaver <adrian.klaver@aklaver.com> > wrote: > > On 11/27/2015 06:07 PM, Tom Lane wrote: > > Adrian Klaver <adrian.klaver@aklaver.com> writes: > >> On 11/27/2015 08:15 AM, Bruce Momjian wrote: > >>> My guess is you are sharing the constraint name "seqno_not_null" with > >>> multiple tables. I think you are going to have to dig into the system > >>> tables to see where that is referenced and fix it. > > > >> In the post below the OP shows the tables involved(they where > inherited): > >> http://www.postgresql.org/message-id/ > CADbMkNM_y9ewdaWdQ_8DJ1mUC0Z_FGwTyAD2RwCHgExj2jvOHQ@mail.gmail.com > > > > Inherited eh? Maybe related to 074c5cfbf. > > > I forgot to mention this earlier. This cluster is running 9.2.6 and I'm > attempting to upgrade to the latest 9.4.5 Well, 9.4.5 we released on October 8, 2015, and the commit mentioned happened on November 20, 2015, so that fix is not in 9.4.5: commit 074c5cfbfb4923158be9ccdb77420d6522d77538 Author: Tom Lane <tgl@sss.pgh.pa.us> Date: Fri Nov 20 14:55:28 2015 -0500 Fix handling of inherited check constraints in ALTER COLUMN TYPE (again). The previous way of reconstructing check constraints was to do a separate "ALTER TABLE ONLY tab ADD CONSTRAINT" for each table in an inheritance hierarchy. However, that way has no hope of reconstructing the check constraints' own inheritance properties correctly, as pointed out in bug #13779 from Jan Dirk Zijlstra. What we should do instead is to do a regular "ALTER TABLE", allowing recursion, at the topmost table that has a particular constraint, and then suppress the work queue entries for inherited instances of the constraint. Annoyingly, we'd tried to fix this behavior before, in commit 5ed6546cf, but we failed to notice that it wasn't reconstructing the pg_constraint field values correctly. As long as I'm touching pg_get_constraintdef_worker anyway, tweak it to always schema-qualify the target table name; this seems like useful backup to the protections installed by commit 5f173040. In HEAD/9.5, get rid of get_constraint_relation_oids, which is now unused. (I could alternatively have modified it to also return conislocal, but that seemed like a pretty single-purpose API, so let's not pretend it has some other use.) It's unused in the back branches as well, but I left it in place just in case some third-party code has decided to use it. In HEAD/9.5, also rename pg_get_constraintdef_string to pg_get_constraintdef_command, as the previous name did nothing to explain what that entry point did differently from others (and its comment was equally useless). Again, that change doesn't seem like material for back-patching. I did a bit of re-pgindenting in tablecmds.c in HEAD/9.5, as well. Otherwise, back-patch to all supported branches. Are you able to compile from 9.4 git head and test that? It seems dumping inheriting contraints from parents has not worked properly for some time. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Roman grave inscription +
On Mon, Nov 30, 2015 at 4:29 PM, Bruce Momjian <bruce@momjian.us> wrote:
On Mon, Nov 30, 2015 at 08:08:50AM +0000, Benedikt Grundmann wrote:
>
>
> On Sat, Nov 28, 2015 at 2:39 AM, Adrian Klaver <adrian.klaver@aklaver.com>
> wrote:
>
> On 11/27/2015 06:07 PM, Tom Lane wrote:
> > Adrian Klaver <adrian.klaver@aklaver.com> writes:
> >> On 11/27/2015 08:15 AM, Bruce Momjian wrote:
> >>> My guess is you are sharing the constraint name "seqno_not_null" with
> >>> multiple tables. I think you are going to have to dig into the system
> >>> tables to see where that is referenced and fix it.
> >
> >> In the post below the OP shows the tables involved(they where
> inherited):
> >> http://www.postgresql.org/message-id/
> CADbMkNM_y9ewdaWdQ_8DJ1mUC0Z_FGwTyAD2RwCHgExj2jvOHQ@mail.gmail.com
> >
> > Inherited eh? Maybe related to 074c5cfbf.
>
>
> I forgot to mention this earlier. This cluster is running 9.2.6 and I'm
> attempting to upgrade to the latest 9.4.5
Well, 9.4.5 we released on October 8, 2015, and the commit mentioned happened
on November 20, 2015, so that fix is not in 9.4.5:
commit 074c5cfbfb4923158be9ccdb77420d6522d77538
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date: Fri Nov 20 14:55:28 2015 -0500
Fix handling of inherited check constraints in ALTER COLUMN TYPE (again).
The previous way of reconstructing check constraints was to do a separate
"ALTER TABLE ONLY tab ADD CONSTRAINT" for each table in an inheritance
hierarchy. However, that way has no hope of reconstructing the check
constraints' own inheritance properties correctly, as pointed out in
bug #13779 from Jan Dirk Zijlstra. What we should do instead is to do
a regular "ALTER TABLE", allowing recursion, at the topmost table that
has a particular constraint, and then suppress the work queue entries
for inherited instances of the constraint.
Annoyingly, we'd tried to fix this behavior before, in commit 5ed6546cf,
but we failed to notice that it wasn't reconstructing the pg_constraint
field values correctly.
As long as I'm touching pg_get_constraintdef_worker anyway, tweak it to
always schema-qualify the target table name; this seems like useful backup
to the protections installed by commit 5f173040.
In HEAD/9.5, get rid of get_constraint_relation_oids, which is now unused.
(I could alternatively have modified it to also return conislocal, but that
seemed like a pretty single-purpose API, so let's not pretend it has some
other use.) It's unused in the back branches as well, but I left it in
place just in case some third-party code has decided to use it.
In HEAD/9.5, also rename pg_get_constraintdef_string to
pg_get_constraintdef_command, as the previous name did nothing to explain
what that entry point did differently from others (and its comment was
equally useless). Again, that change doesn't seem like material for
back-patching.
I did a bit of re-pgindenting in tablecmds.c in HEAD/9.5, as well.
Otherwise, back-patch to all supported branches.
Are you able to compile from 9.4 git head and test that? It seems
dumping inheriting contraints from parents has not worked properly for
some time.
Do I need to get the latest/head 9.2 or the latest/head 9.4 or both? For what it is worth I just tried after upgrading to the latest released 9.2 (and same 9.45) and that didn't work :-(
I should certainly be able to compile from source. But the upgrade to 9.4 is by far not high on my priority stack (other than maybe some speed wins there is nothing in 9.4 that we are eager for, there are some niceties but I can happily live without all of them for years) and has already consumed way more time than I had scheduled for it. So I'll return to focus on other work for at least this week and maybe more depending on how that work goes.
Thanks to everyone I'll certainly update this thread if / when I have more time to devote to this.
cheers,
Bene
On Mon, Nov 30, 2015 at 04:51:15PM +0000, Benedikt Grundmann wrote: > Are you able to compile from 9.4 git head and test that? It seems > dumping inheriting constraints from parents has not worked properly for > some time. > > > Do I need to get the latest/head 9.2 or the latest/head 9.4 or both? For what > it is worth I just tried after upgrading to the latest released 9.2 (and same > 9.45) and that didn't work :-( You actually need non-released 9.4.X code that is in pg_dump, and we use 9.4 pg_dump to dump the 9.2 database. > I should certainly be able to compile from source. But the upgrade to 9.4 is > by far not high on my priority stack (other than maybe some speed wins there is > nothing in 9.4 that we are eager for, there are some niceties but I can happily > live without all of them for years) and has already consumed way more time than > I had scheduled for it. So I'll return to focus on other work for at least > this week and maybe more depending on how that work goes. > > Thanks to everyone I'll certainly update this thread if / when I have more time > to devote to this. The simplest solution is to wait for 9.4.6 to be released and test that. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Roman grave inscription +
On 30 November 2015 at 17:01, Bruce Momjian <bruce@momjian.us> wrote:
On Mon, Nov 30, 2015 at 04:51:15PM +0000, Benedikt Grundmann wrote:
> Are you able to compile from 9.4 git head and test that? It seems
> dumping inheriting constraints from parents has not worked properly for
> some time.
>
>
> Do I need to get the latest/head 9.2 or the latest/head 9.4 or both? For what
> it is worth I just tried after upgrading to the latest released 9.2 (and same
> 9.45) and that didn't work :-(
You actually need non-released 9.4.X code that is in pg_dump, and we use
9.4 pg_dump to dump the 9.2 database.
> I should certainly be able to compile from source. But the upgrade to 9.4 is
> by far not high on my priority stack (other than maybe some speed wins there is
> nothing in 9.4 that we are eager for, there are some niceties but I can happily
> live without all of them for years) and has already consumed way more time than
> I had scheduled for it. So I'll return to focus on other work for at least
> this week and maybe more depending on how that work goes.
>
> Thanks to everyone I'll certainly update this thread if / when I have more time
> to devote to this.
The simplest solution is to wait for 9.4.6 to be released and test that.
I just tried this again. This time from 9.2.17 to 9.5.4 and pg_upgrade chokes with this:
[root@igm-dbc-001 upgrade-logs]# tail pg_upgrade_dump_16416.log
pg_restore: [archiver (db)] could not execute query: ERROR: syntax error at or near "=>"
LINE 1: CREATE OPERATOR => (
^
Command was: CREATE OPERATOR => (
PROCEDURE = "tconvert",
LEFTARG = "text",
RIGHTARG = "text"
);
-- For binary upgrade, hand...
Any pointers are appreciated.
Thanks,
Bene
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Roman grave inscription +
Benedikt Grundmann <bgrundmann@janestreet.com> writes:
> I just tried this again. This time from 9.2.17 to 9.5.4 and pg_upgrade
> chokes with this:
>
> [root@igm-dbc-001 upgrade-logs]# tail pg_upgrade_dump_16416.log
> pg_restore: [archiver (db)] could not execute query: ERROR: syntax error
> at or near "=>"
> LINE 1: CREATE OPERATOR => (
> ^
> Command was: CREATE OPERATOR => (
> PROCEDURE = "tconvert",
> LEFTARG = "text",
> RIGHTARG = "text"
> );
You're going to need to manually drop that operator from the source
database, as "=>" isn't a legal operator name anymore. This appears
to be left over from a pre-9.0 version of hstore.
regards, tom lane
On 3 October 2016 at 14:12, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Benedikt Grundmann <bgrundmann@janestreet.com> writes:
> I just tried this again. This time from 9.2.17 to 9.5.4 and pg_upgrade
> chokes with this:
>
> [root@igm-dbc-001 upgrade-logs]# tail pg_upgrade_dump_16416.log
> pg_restore: [archiver (db)] could not execute query: ERROR: syntax error
> at or near "=>"
> LINE 1: CREATE OPERATOR => (
> ^
> Command was: CREATE OPERATOR => (
> PROCEDURE = "tconvert",
> LEFTARG = "text",
> RIGHTARG = "text"
> );
You're going to need to manually drop that operator from the source
database, as "=>" isn't a legal operator name anymore. This appears
to be left over from a pre-9.0 version of hstore.
regards, tom lane
Thanks for the quick reply. How do I do that however? Without dropping the extension itself that is:
postgres=# select * from pg_operator where oprname = '=>';
(0 rows)
postgres=# \c proddb_testing
psql (9.5.4, server 9.2.17)
You are now connected to database "proddb_testing" as user "as-proddb".
proddb_testing=# select * from pg_operator where oprname = '=>';
-[ RECORD 1 ]+---------
oprname | =>
oprnamespace | 2200
oprowner | 10
oprkind | b
oprcanmerge | f
oprcanhash | f
oprleft | 25
oprright | 25
oprresult | 144920
oprcom | 0
oprnegate | 0
oprcode | tconvert
oprrest | -
oprjoin | -
proddb_testing=# drop operator => (text, text);
ERROR: cannot drop operator =>(text,text) because extension hstore requires it
HINT: You can drop extension hstore instead.
Is it possible that I need to do some form of hstore extension upgrade dance?
Benedikt Grundmann <bgrundmann@janestreet.com> writes:
> On 3 October 2016 at 14:12, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> You're going to need to manually drop that operator from the source
>> database, as "=>" isn't a legal operator name anymore. This appears
>> to be left over from a pre-9.0 version of hstore.
> Thanks for the quick reply. How do I do that however? Without dropping
> the extension itself that is:
>
> proddb_testing=# drop operator => (text, text);
> ERROR: cannot drop operator =>(text,text) because extension hstore requires it
> HINT: You can drop extension hstore instead.
>
> Is it possible that I need to do some form of hstore extension upgrade
> dance?
Ah, I'd been guessing that the operator was "loose", but if you still
have hstore 1.0 installed then yes that's the behavior I'd expect.
You need to do "alter extension hstore update". In a 9.2 database
that should bring it to 1.1 which will get rid of the operator.
regards, tom lane
On 3 October 2016 at 15:30, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Benedikt Grundmann <bgrundmann@janestreet.com> writes:
> On 3 October 2016 at 14:12, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> You're going to need to manually drop that operator from the source
>> database, as "=>" isn't a legal operator name anymore. This appears
>> to be left over from a pre-9.0 version of hstore.
> Thanks for the quick reply. How do I do that however? Without dropping
> the extension itself that is:
>
> proddb_testing=# drop operator => (text, text);
> ERROR: cannot drop operator =>(text,text) because extension hstore requires it
> HINT: You can drop extension hstore instead.
>
> Is it possible that I need to do some form of hstore extension upgrade
> dance?
Ah, I'd been guessing that the operator was "loose", but if you still
have hstore 1.0 installed then yes that's the behavior I'd expect.
You need to do "alter extension hstore update". In a 9.2 database
that should bring it to 1.1 which will get rid of the operator.
regards, tom lane
And it looks like now I'm back to the error that stopped me last time:
[root@igm-dbc-001 ~]# cd /usr/local/home/as-proddb/upgrade-logs/
[root@igm-dbc-001 upgrade-logs]# tail pg_upgrade_dump_16416.log
pg_restore: creating CHECK CONSTRAINT "public.seqno_not_null"
pg_restore: creating CHECK CONSTRAINT "public.seqno_not_null"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 8425; 2606 416548282 CHECK CONSTRAINT seqno_not_null postgres_prod
pg_restore: [archiver (db)] could not execute query: ERROR: constraint "seqno_not_null" for relation "js_activity_20110101" already exists
Command was: ALTER TABLE "js_activity_20110101"
ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT VALID;
I figured it might be because I'm on 9.2.17 so I upgraded to 9.2.18 and tried again but no luck:
[root@igm-dbc-001 upgrade-logs]# tail pg_upgrade_dump_16416.log
pg_restore: creating CHECK CONSTRAINT "public.seqno_not_null"
pg_restore: creating CHECK CONSTRAINT "public.seqno_not_null"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 8425; 2606 416548282 CHECK CONSTRAINT seqno_not_null postgres_prod
pg_restore: [archiver (db)] could not execute query: ERROR: constraint "seqno_not_null" for relation "js_activity_20110101" already exists
Command was: ALTER TABLE "js_activity_20110101"
ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT VALID;
What do you need from me to debug this?
Benedikt Grundmann <bgrundmann@janestreet.com> writes:
> And it looks like now I'm back to the error that stopped me last time:
> pg_restore: [archiver (db)] Error from TOC entry 8425; 2606 416548282 CHECK
> CONSTRAINT seqno_not_null postgres_prod
> pg_restore: [archiver (db)] could not execute query: ERROR: constraint
> "seqno_not_null" for relation "js_activity_20110101" already exists
> Command was: ALTER TABLE "js_activity_20110101"
> ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT VALID;
Hm. I'm guessing that table is a child table, and this has something to
do with improper constraint inheritance. Could we see psql \d+ output
for both this table and its parent? And for good measure, maybe the
output of
SELECT conname,convalidated,conislocal,coninhcount,connoinherit
FROM pg_constraint WHERE conrelid = 'js_activity_20110101'::regclass;
and likewise for the parent table.
regards, tom lane
On 3 October 2016 at 15:54, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Benedikt Grundmann <bgrundmann@janestreet.com> writes:
> And it looks like now I'm back to the error that stopped me last time:
> pg_restore: [archiver (db)] Error from TOC entry 8425; 2606 416548282 CHECK
> CONSTRAINT seqno_not_null postgres_prod
> pg_restore: [archiver (db)] could not execute query: ERROR: constraint
> "seqno_not_null" for relation "js_activity_20110101" already exists
> Command was: ALTER TABLE "js_activity_20110101"
> ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT VALID;
Hm. I'm guessing that table is a child table, and this has something to
do with improper constraint inheritance. Could we see psql \d+ output
for both this table and its parent? And for good measure, maybe the
output of
SELECT conname,convalidated,conislocal,coninhcount, connoinherit
FROM pg_constraint WHERE conrelid = 'js_activity_20110101'::regclass;
and likewise for the parent table.
regards, tom lane
Sure this is not going to be very pretty but here you are:
proddb_testing=# \d+ js_activity
Table "public.js_activity"
Column | Type | Modifiers
| Storage | Stats target | Description
---------------------------+-----------------------------+-------------------------------------------------------------
--------+----------+--------------+-------------
id | text | not null default nextval('pnl_fr_imported_trades_id_seq'::re
gclass) | extended | |
tid | text | not null default currval('pnl_fr_imported_trades_id_seq'::re
gclass) | extended | |
entry_time | timestamp without time zone | not null default now()
| plain | |
pnl_date | date | not null
| plain | |
activity_date | date | not null
| plain | |
activity_time | time without time zone |
| plain | |
projected_settlement_date | date | not null
| plain | |
clearing_firm | text | not null
| extended | |
currency | text | not null
| extended | |
account | text | not null
| extended | |
security_code | text | not null
| extended | |
trade_type | text | not null
| extended | |
quantity | numeric(28,8) | not null
| main | |
gross_price | numeric(28,8) | not null default 0
| main | |
net_price | numeric(28,8) | not null default 0
| main | |
net_cash | numeric(28,8) | not null default 0
| main | |
accrual | numeric(28,8) | not null default 0
| main | |
commission_amount | numeric(28,8) | not null default 0
| main | |
commission_rate | numeric(28,8) | not null default 0
| main | |
narrative | text | not null default ''::text
| extended | |
counterparty | text | not null
| extended | |
executing_exchange | text | not null default ''::text
| extended | |
route | text | not null default ''::text
| extended | |
source | text | not null default ''::text
| extended | |
tags | hstore | not null default ''::hstore
| extended | |
booking_type | character(1) | not null default 'B'::bpchar
| extended | |
is_canceled | text |
| extended | |
executing_broker | text | not null default ''::text
| extended | |
version_tags | hstore | not null default ''::hstore
| extended | |
discretionary_agent | text | not null default ''::text
| extended | |
shape_id | text | not null default ''::text
| extended | |
capture_date | date |
| plain | |
order_id | text |
| extended | |
order_mark | text |
| extended | |
seqno | bigint | default strict_sequence()
| plain | |
system | text |
| extended | |
tplus | integer |
| plain | |
trade_date | date |
| plain | |
trader | text |
| extended | |
username | text |
| extended | |
cancel_username | text |
| extended | |
cancel_entry_time | timestamp without time zone |
| plain | |
cancel_narrative | text |
| extended | |
cancel_seqno | bigint |
| plain | |
settlement_system | text |
| extended | |
Check constraints:
"at_most_one_clears_on_bk" CHECK (at_most_one_key_like(tags, 'bk:Clears_on_%'::text)) NOT VALID
"at_most_one_riskless_principal_for_bk" CHECK (at_most_one_key_like(tags, 'bk:Riskless_principal_for_%'::text)) NOT
VALID
"cancel_consistency" CHECK ((cancel_username IS NULL) = (cancel_entry_time IS NULL) AND (cancel_entry_time IS NULL)
= (cancel_narrative IS NULL) AND (cancel_narrative IS NULL) = (cancel_seqno IS NULL))
"capture_date_matches_date_in_tid" CHECK (NOT tid ~~ '(2%)'::text OR "substring"(tid, 2, 10)::date = capture_date)
NOT VALID
"contracts_is_an_integer" CHECK ((tags -> 'contracts'::text) ~ '^[0-9]+$'::text)
"flow_tag" CHECK ((tags -> 'flow'::text) ~ '^[0-9]+$'::text) NOT VALID
"js_activity_booking_type_check" CHECK (booking_type = 'B'::bpchar OR booking_type = 'A'::bpchar)
"js_activity_check" CHECK (is_canceled = id)
"js_activity_discretionary_agent_matches_tag" CHECK (discretionary_agent = (tags -> 'agent'::text))
"js_activity_no_suspense_accounts_check" CHECK (account !~ '^SUSPENSE-.+$'::text)
"js_activity_pnl_date_on_weekday" CHECK (date_part('dow'::text, pnl_date) >= 1::double precision AND date_part('dow
'::text, pnl_date) <= 5::double precision)
"js_activity_security_code_non_blank" CHECK (security_code <> ''::text)
"nothing_to_see_here" CHECK (pnl_date = '1000-01-01'::date) NO INHERIT
"otc_settlement_system_consistency" CHECK (NOT executing_exchange ~~ 'OTC_%'::text OR settlement_system = "substrin
g"(executing_exchange, 5)) NOT VALID
"price_extra_info_tags_come_together" CHECK ((NOT tags ? 'not_final_price'::text OR (tags -> 'not_final_price'::tex
t) = ''::text) AND (tags ? 'price_extra_data'::text) = (tags ? 'price_kind'::text) AND (NOT tags ? 'not_final_price'::t
ext OR tags ? 'price_kind'::text)) NOT VALID
"security_code_not_blank_on_either_end" CHECK (security_code !~~ '% '::text AND security_code !~~ ' %'::text) NOT V
ALID
"seqno_not_null" CHECK (seqno IS NOT NULL) NOT VALID
"trades_have_times" CHECK (trade_type <> 'Trade'::text OR activity_time IS NOT NULL) NOT VALID
"valid_counterparty" CHECK (counterparty !~~ '% '::text AND counterparty !~~ ' %'::text) NOT VALID
"valid_executing_broker" CHECK (executing_broker !~~ '% '::text AND executing_broker !~~ ' %'::text) NOT VALID
"valid_order_mark" CHECK (order_mark = ANY (ARRAY['T'::text, 'X'::text, 'S'::text])) NOT VALID
"valid_settlement_system" CHECK (settlement_system ~ '^[A-Z]+$'::text) NOT VALID
"valid_terms_tag" CHECK ((tags -> 'terms'::text) ~ '^[A-Z0-9]{4}$'::text) NOT VALID
Foreign-key constraints:
"js_activity_clearing_firm_fkey1" FOREIGN KEY (clearing_firm) REFERENCES lu_clearing_firms(mnemonic)
"js_activity_currency_fkey1" FOREIGN KEY (currency) REFERENCES lu_currencies(currency_code)
"js_activity_no_rows" FOREIGN KEY (id) REFERENCES js_activity_no_rows_constraint(id)
"js_activity_trade_type_fkey1" FOREIGN KEY (trade_type) REFERENCES lu_trade_types(mnemonic)
Triggers:
insert_js_activity BEFORE INSERT ON js_activity FOR EACH ROW EXECUTE PROCEDURE js_activity_insert_trigger()
mv_js_equity AFTER UPDATE ON js_activity FOR EACH STATEMENT EXECUTE PROCEDURE mv_js_equity_js_activity_was_changed(
)
Child tables: js_activity_20090101,
js_activity_20100101,
js_activity_20110101,
js_activity_20120101,
js_activity_20130101,
js_activity_20140101,
js_activity_20150101,
js_activity_20150701,
js_activity_20160101,
js_activity_tip
Has OIDs: no
proddb_testing=# \d+ js_activity_20110101
Table "public.js_activity_20110101"
Column | Type | Modifiers
| Storage | Stats target | Description
---------------------------+-----------------------------+-------------------------------------------------------------
--------+----------+--------------+-------------
id | text | not null default nextval('pnl_fr_imported_trades_id_seq'::re
gclass) | extended | |
tid | text | not null default currval('pnl_fr_imported_trades_id_seq'::re
gclass) | extended | |
entry_time | timestamp without time zone | not null default now()
| plain | |
pnl_date | date | not null
| plain | |
activity_date | date | not null
| plain | |
activity_time | time without time zone |
| plain | |
projected_settlement_date | date | not null
| plain | |
clearing_firm | text | not null
| extended | |
currency | text | not null
| extended | |
account | text | not null
| extended | |
security_code | text | not null
| extended | |
trade_type | text | not null
| extended | |
quantity | numeric(28,8) | not null
| main | |
gross_price | numeric(28,8) | not null default 0
| main | |
net_price | numeric(28,8) | not null default 0
| main | |
net_cash | numeric(28,8) | not null default 0
| main | |
accrual | numeric(28,8) | not null default 0
| main | |
commission_amount | numeric(28,8) | not null default 0
| main | |
commission_rate | numeric(28,8) | not null default 0
| main | |
narrative | text | not null default ''::text
| extended | |
counterparty | text | not null
| extended | |
executing_exchange | text | not null default ''::text
| extended | |
route | text | not null default ''::text
| extended | |
source | text | not null default ''::text
| extended | |
tags | hstore | not null default ''::hstore
| extended | |
booking_type | character(1) | not null default 'B'::bpchar
| extended | |
is_canceled | text |
| extended | |
executing_broker | text | not null default ''::text
| extended | |
version_tags | hstore | not null default ''::hstore
| extended | |
discretionary_agent | text | not null default ''::text
| extended | |
shape_id | text | not null default ''::text
| extended | |
capture_date | date |
| plain | |
order_id | text |
| extended | |
order_mark | text |
| extended | |
seqno | bigint | default strict_sequence()
| plain | |
system | text |
| extended | |
tplus | integer |
| plain | |
trade_date | date |
| plain | |
trader | text |
| extended | |
username | text |
| extended | |
cancel_username | text |
| extended | |
cancel_entry_time | timestamp without time zone |
| plain | |
cancel_narrative | text |
| extended | |
cancel_seqno | bigint |
| plain | |
settlement_system | text |
| extended | |
Indexes:
"js_activity_2011_cancel_seqno" UNIQUE, btree (cancel_seqno) WHERE cancel_seqno IS NOT NULL
"js_activity_2011_id" UNIQUE, btree (id)
"js_activity_2011_seqno" UNIQUE, btree (seqno)
"js_activity_2011_activity_date" btree (activity_date)
"js_activity_2011_cancel_entry_time" btree (cancel_entry_time) WHERE cancel_entry_time IS NOT NULL
"js_activity_2011_discretionary_agent" btree (discretionary_agent)
"js_activity_2011_entry_time" btree (entry_time)
"js_activity_2011_pnl_date" btree (pnl_date)
"js_activity_2011_projected_settlement_date" btree (projected_settlement_date)
"js_activity_2011_shape_id" btree (shape_id)
"js_activity_2011_tid" btree (tid)
"js_activity_2011_ts" btree ((activity_date + activity_time)) WHERE activity_time IS NOT NULL
Check constraints:
"activity_date_lower_bound" CHECK (activity_date >= '0111-12-28'::date)
"activity_date_upper_bound" CHECK (activity_date <= '2012-02-10'::date)
"activity_time_lower_bound" CHECK ((activity_date + activity_time) >= '2010-01-05 10:44:48'::timestamp without time
zone)
"activity_time_upper_bound" CHECK ((activity_date + activity_time) <= '2011-12-30 23:59:59'::timestamp without time
zone)
"at_most_one_clears_on_bk" CHECK (at_most_one_key_like(tags, 'bk:Clears_on_%'::text)) NOT VALID
"at_most_one_riskless_principal_for_bk" CHECK (at_most_one_key_like(tags, 'bk:Riskless_principal_for_%'::text)) NOT
VALID
"cancel_consistency" CHECK ((cancel_username IS NULL) = (cancel_entry_time IS NULL) AND (cancel_entry_time IS NULL)
= (cancel_narrative IS NULL) AND (cancel_narrative IS NULL) = (cancel_seqno IS NULL))
"cancel_entry_time_lower_bound" CHECK (cancel_entry_time >= '2011-01-04 05:47:48.463365'::timestamp without time zo
ne)
"cancel_entry_time_upper_bound" CHECK (cancel_entry_time <= '2012-02-08 16:07:37.714466'::timestamp without time zo
ne)
"cancel_seqno_lower_bound" CHECK (cancel_seqno >= 80417910::bigint)
"cancel_seqno_upper_bound" CHECK (cancel_seqno <= 291703135::bigint)
"capture_date_matches_date_in_tid" CHECK (NOT tid ~~ '(2%)'::text OR "substring"(tid, 2, 10)::date = capture_date)
NOT VALID
"capture_date_not_populated" CHECK (capture_date IS NULL)
"contracts_is_an_integer" CHECK ((tags -> 'contracts'::text) ~ '^[0-9]+$'::text)
"entry_time_lower_bound" CHECK (entry_time >= '2011-01-03 11:40:34.825558'::timestamp without time zone)
"entry_time_upper_bound" CHECK (entry_time <= '2012-02-08 16:07:37.714466'::timestamp without time zone)
"flow_tag" CHECK ((tags -> 'flow'::text) ~ '^[0-9]+$'::text) NOT VALID
"js_activity_2011_partition_constraint" CHECK (pnl_date >= '2011-01-01'::date AND pnl_date <= '2011-12-31'::date)
"js_activity_booking_type_check" CHECK (booking_type = 'B'::bpchar OR booking_type = 'A'::bpchar)
"js_activity_check" CHECK (is_canceled = id)
"js_activity_discretionary_agent_matches_tag" CHECK (discretionary_agent = (tags -> 'agent'::text))
"js_activity_no_suspense_accounts_check" CHECK (account !~ '^SUSPENSE-.+$'::text)
"js_activity_pnl_date_on_weekday" CHECK (date_part('dow'::text, pnl_date) >= 1::double precision AND date_part('dow
'::text, pnl_date) <= 5::double precision)
"js_activity_security_code_non_blank" CHECK (security_code <> ''::text)
"otc_settlement_system_consistency" CHECK (NOT executing_exchange ~~ 'OTC_%'::text OR settlement_system = "substrin
g"(executing_exchange, 5)) NOT VALID
"price_extra_info_tags_come_together" CHECK ((NOT tags ? 'not_final_price'::text OR (tags -> 'not_final_price'::tex
t) = ''::text) AND (tags ? 'price_extra_data'::text) = (tags ? 'price_kind'::text) AND (NOT tags ? 'not_final_price'::t
ext OR tags ? 'price_kind'::text)) NOT VALID
"security_code_not_blank_on_either_end" CHECK (security_code !~~ '% '::text AND security_code !~~ ' %'::text) NOT V
ALID
"seqno_lower_bound" CHECK (seqno >= 79792315::bigint)
"seqno_not_null" CHECK (seqno IS NOT NULL) NOT VALID
"seqno_upper_bound" CHECK (seqno <= 291695496::bigint)
"trades_have_times" CHECK (trade_type <> 'Trade'::text OR activity_time IS NOT NULL) NOT VALID
"valid_counterparty" CHECK (counterparty !~~ '% '::text AND counterparty !~~ ' %'::text) NOT VALID
"valid_executing_broker" CHECK (executing_broker !~~ '% '::text AND executing_broker !~~ ' %'::text) NOT VALID
"valid_order_mark" CHECK (order_mark = ANY (ARRAY['T'::text, 'X'::text, 'S'::text])) NOT VALID
"valid_settlement_system" CHECK (settlement_system ~ '^[A-Z]+$'::text) NOT VALID
"valid_terms_tag" CHECK ((tags -> 'terms'::text) ~ '^[A-Z0-9]{4}$'::text) NOT VALID
Foreign-key constraints:
"js_activity_2011_account_matches_cf" FOREIGN KEY (account, clearing_firm) REFERENCES lu_pnl_fr_accounts(account_co
de, clearing_firm)
Triggers:
js_activity_is_frozen AFTER INSERT OR DELETE OR UPDATE ON js_activity_20110101 FOR EACH ROW EXECUTE PROCEDURE js_ac
tivity_is_frozen()
Inherits: js_activity
Has OIDs: no
proddb_testing=# SELECT conname,convalidated,conislocal,coninhcount,connoinherit
proddb_testing-# FROM pg_constraint WHERE conrelid = 'js_activity_20110101'::regclass;
conname | convalidated | conislocal | coninhcount | connoinherit
---------------------------------------------+--------------+------------+-------------+--------------
price_extra_info_tags_come_together | f | f | 1 | f
cancel_entry_time_upper_bound | t | t | 0 | f
cancel_seqno_upper_bound | t | t | 0 | f
activity_time_upper_bound | t | t | 0 | f
seqno_upper_bound | t | t | 0 | f
entry_time_upper_bound | t | t | 0 | f
activity_date_upper_bound | t | t | 0 | f
cancel_entry_time_lower_bound | t | t | 0 | f
cancel_seqno_lower_bound | t | t | 0 | f
activity_time_lower_bound | t | t | 0 | f
seqno_lower_bound | t | t | 0 | f
entry_time_lower_bound | t | t | 0 | f
activity_date_lower_bound | t | t | 0 | f
capture_date_not_populated | t | t | 0 | f
capture_date_matches_date_in_tid | f | f | 1 | f
flow_tag | f | f | 1 | f
at_most_one_riskless_principal_for_bk | f | f | 1 | f
at_most_one_clears_on_bk | f | f | 1 | f
valid_terms_tag | f | f | 1 | f
valid_settlement_system | f | f | 1 | f
otc_settlement_system_consistency | f | f | 1 | f
security_code_not_blank_on_either_end | f | t | 1 | f
trades_have_times | f | t | 1 | f
js_activity_pnl_date_on_weekday | t | t | 1 | f
js_activity_no_suspense_accounts_check | t | t | 1 | f
js_activity_discretionary_agent_matches_tag | t | t | 1 | f
js_activity_check | t | t | 1 | f
js_activity_booking_type_check | t | t | 1 | f
contracts_is_an_integer | t | t | 1 | f
valid_order_mark | f | t | 1 | f
valid_executing_broker | f | t | 1 | f
valid_counterparty | f | t | 1 | f
cancel_consistency | t | t | 1 | f
js_activity_security_code_non_blank | t | t | 1 | f
seqno_not_null | f | t | 1 | f
js_activity_2011_partition_constraint | t | t | 0 | f
js_activity_2011_account_matches_cf | t | t | 0 | t
(37 rows)
proddb_testing=# SELECT conname,convalidated,conislocal,coninhcount,connoinherit
FROM pg_constraint WHERE conrelid = 'js_activity'::regclass;
conname | convalidated | conislocal | coninhcount | connoinherit
---------------------------------------------+--------------+------------+-------------+--------------
price_extra_info_tags_come_together | f | t | 0 | f
capture_date_matches_date_in_tid | f | t | 0 | f
flow_tag | f | t | 0 | f
at_most_one_riskless_principal_for_bk | f | t | 0 | f
at_most_one_clears_on_bk | f | t | 0 | f
valid_terms_tag | f | t | 0 | f
valid_settlement_system | f | t | 0 | f
otc_settlement_system_consistency | f | t | 0 | f
trades_have_times | f | t | 0 | f
security_code_not_blank_on_either_end | f | t | 0 | f
cancel_consistency | t | t | 0 | f
valid_counterparty | f | t | 0 | f
valid_executing_broker | f | t | 0 | f
valid_order_mark | f | t | 0 | f
seqno_not_null | f | t | 0 | f
nothing_to_see_here | t | t | 0 | t
contracts_is_an_integer | t | t | 0 | f
js_activity_booking_type_check | t | t | 0 | f
js_activity_check | t | t | 0 | f
js_activity_discretionary_agent_matches_tag | t | t | 0 | f
js_activity_no_suspense_accounts_check | t | t | 0 | f
js_activity_pnl_date_on_weekday | t | t | 0 | f
js_activity_security_code_non_blank | t | t | 0 | f
js_activity_clearing_firm_fkey1 | t | t | 0 | t
js_activity_currency_fkey1 | t | t | 0 | t
js_activity_no_rows | t | t | 0 | t
js_activity_trade_type_fkey1 | t | t | 0 | t
(27 rows)
Benedikt Grundmann <bgrundmann@janestreet.com> writes:
> proddb_testing=# SELECT
> conname,convalidated,conislocal,coninhcount,connoinherit
> proddb_testing-# FROM pg_constraint WHERE conrelid =
> 'js_activity_20110101'::regclass;
> conname | convalidated | conislocal |
> coninhcount | connoinherit
> ---------------------------------------------+--------------+------------+-------------+--------------
> seqno_not_null | f | t |
> 1 | f
After some tracing through the code, I think it's the combination of all
three of coninhcount>0, conislocal, and !convalidated that is producing
the problem, and even then possibly only in binary-upgrade mode. pg_dump
is jumping through some hoops to try to restore that state, and evidently
not getting it entirely right.
Is there a reason you've left all these constraints in NOT VALID state?
They're kinda useless that way. Probably if you updated them to be valid
(see ALTER TABLE ... VALIDATE CONSTRAINT), the upgrade would go through
without difficulty.
I'll look into fixing this, but depending on how messy it turns out to be,
it might be something we choose to fix only in HEAD.
regards, tom lane
On 3 October 2016 at 21:01, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Benedikt Grundmann <bgrundmann@janestreet.com> writes:
> proddb_testing=# SELECT
> conname,convalidated,conislocal,coninhcount, connoinherit
> proddb_testing-# FROM pg_constraint WHERE conrelid =
> 'js_activity_20110101'::regclass;
> conname | convalidated | conislocal |
> coninhcount | connoinherit
> ---------------------------------------------+-------------- +------------+-------------+-- ------------
> seqno_not_null | f | t |
> 1 | f
After some tracing through the code, I think it's the combination of all
three of coninhcount>0, conislocal, and !convalidated that is producing
the problem, and even then possibly only in binary-upgrade mode. pg_dump
is jumping through some hoops to try to restore that state, and evidently
not getting it entirely right.
Is there a reason you've left all these constraints in NOT VALID state?
They're kinda useless that way.
Not at all. I consider the ability to add constraints in not validated form one of the 10 best things that happened in postgres in recent years. They helped us a lot when slowly improving our schemas.
Often just preventing any new or modified rows to validate the constraint is really all we need or most that is needed. Which is the only thing I really care about in this case. And given the size of these tables and their importance validating the constraints during production hours is tricky. Which means to validate them one of us has to sacrifice part of their Saturday to do these and the marginal utility of having the constraint validated was just never worth it. But if that is what's required to do the upgrade we will do so (the upgrade itself we will have to do on a Saturday anyway).
Probably if you updated them to be valid
(see ALTER TABLE ... VALIDATE CONSTRAINT), the upgrade would go through
without difficulty.
I'm running all the upgrade attempts on our testing instance (which is nightly restored from the latest backup), it's not a problem to run the validate command there so I'll do that now and find out if you are right.
I'll look into fixing this, but depending on how messy it turns out to be,
it might be something we choose to fix only in HEAD.
regards, tom lane
On 4 October 2016 at 08:17, Benedikt Grundmann <bgrundmann@janestreet.com> wrote:
On 3 October 2016 at 21:01, Tom Lane <tgl@sss.pgh.pa.us> wrote:Benedikt Grundmann <bgrundmann@janestreet.com> writes:
> proddb_testing=# SELECT
> conname,convalidated,conislocal,coninhcount,connoinherit
> proddb_testing-# FROM pg_constraint WHERE conrelid =
> 'js_activity_20110101'::regclass;
> conname | convalidated | conislocal |
> coninhcount | connoinherit
> ---------------------------------------------+-------------- +------------+-------------+-- ------------
> seqno_not_null | f | t |
> 1 | f
After some tracing through the code, I think it's the combination of all
three of coninhcount>0, conislocal, and !convalidated that is producing
the problem, and even then possibly only in binary-upgrade mode. pg_dump
is jumping through some hoops to try to restore that state, and evidently
not getting it entirely right.
Is there a reason you've left all these constraints in NOT VALID state?
They're kinda useless that way.Not at all. I consider the ability to add constraints in not validated form one of the 10 best things that happened in postgres in recent years. They helped us a lot when slowly improving our schemas.Often just preventing any new or modified rows to validate the constraint is really all we need or most that is needed. Which is the only thing I really care about in this case. And given the size of these tables and their importance validating the constraints during production hours is tricky. Which means to validate them one of us has to sacrifice part of their Saturday to do these and the marginal utility of having the constraint validated was just never worth it. But if that is what's required to do the upgrade we will do so (the upgrade itself we will have to do on a Saturday anyway).Probably if you updated them to be valid
(see ALTER TABLE ... VALIDATE CONSTRAINT), the upgrade would go through
without difficulty.I'm running all the upgrade attempts on our testing instance (which is nightly restored from the latest backup), it's not a problem to run the validate command there so I'll do that now and find out if you are right.
It looks like you might be right but I don't know for sure yet. And it will take me a long time to find out. Rationale: After validating seqno_not_null I could proceed a bit further but failed at another constraint like that (valid_counterparty). However that constraint actually is violated by lots of rows in the past and we had no plans (or easy way) to fix this. The constraint was put in like this to prevent future rows.
I guess I could drop the constraint do the restore and then put the constraint in again. Sigh. This is all relatively sad.
I'll look into fixing this, but depending on how messy it turns out to be,
it might be something we choose to fix only in HEAD.
regards, tom lane
On 4 October 2016 at 09:28, Benedikt Grundmann <bgrundmann@janestreet.com> wrote:
On 4 October 2016 at 08:17, Benedikt Grundmann <bgrundmann@janestreet.com> wrote:On 3 October 2016 at 21:01, Tom Lane <tgl@sss.pgh.pa.us> wrote:Benedikt Grundmann <bgrundmann@janestreet.com> writes:
> proddb_testing=# SELECT
> conname,convalidated,conislocal,coninhcount,connoinherit
> proddb_testing-# FROM pg_constraint WHERE conrelid =
> 'js_activity_20110101'::regclass;
> conname | convalidated | conislocal |
> coninhcount | connoinherit
> ---------------------------------------------+-------------- +------------+-------------+-- ------------
> seqno_not_null | f | t |
> 1 | f
After some tracing through the code, I think it's the combination of all
three of coninhcount>0, conislocal, and !convalidated that is producing
the problem, and even then possibly only in binary-upgrade mode. pg_dump
is jumping through some hoops to try to restore that state, and evidently
not getting it entirely right.
Is there a reason you've left all these constraints in NOT VALID state?
They're kinda useless that way.Not at all. I consider the ability to add constraints in not validated form one of the 10 best things that happened in postgres in recent years. They helped us a lot when slowly improving our schemas.Often just preventing any new or modified rows to validate the constraint is really all we need or most that is needed. Which is the only thing I really care about in this case. And given the size of these tables and their importance validating the constraints during production hours is tricky. Which means to validate them one of us has to sacrifice part of their Saturday to do these and the marginal utility of having the constraint validated was just never worth it. But if that is what's required to do the upgrade we will do so (the upgrade itself we will have to do on a Saturday anyway).Probably if you updated them to be valid
(see ALTER TABLE ... VALIDATE CONSTRAINT), the upgrade would go through
without difficulty.I'm running all the upgrade attempts on our testing instance (which is nightly restored from the latest backup), it's not a problem to run the validate command there so I'll do that now and find out if you are right.It looks like you might be right but I don't know for sure yet. And it will take me a long time to find out. Rationale: After validating seqno_not_null I could proceed a bit further but failed at another constraint like that (valid_counterparty). However that constraint actually is violated by lots of rows in the past and we had no plans (or easy way) to fix this. The constraint was put in like this to prevent future rows.I guess I could drop the constraint do the restore and then put the constraint in again. Sigh. This is all relatively sad.
Yep I can confirm that after dropping a few more constraints and then doing the checkpoint_segments vs min_wal_size/max_wal_size foo in postgresql.conf I got the database up. So far everything seems otherwise fine.
I'll look into fixing this, but depending on how messy it turns out to be,
it might be something we choose to fix only in HEAD.
regards, tom lane