public inbox for [email protected]help / color / mirror / Atom feed
RE: Index (primary key) corrupt? 4+ messages / 2 participants [nested] [flat]
* RE: Index (primary key) corrupt? @ 2026-02-13 16:27 Wim Rouquart <[email protected]> 2026-02-13 17:32 ` Re: Index (primary key) corrupt? Adrian Klaver <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Wim Rouquart @ 2026-02-13 16:27 UTC (permalink / raw) To: Adrian Klaver <[email protected]>; Greg Sabino Mullane <[email protected]>; +Cc: [email protected] <[email protected]> Internal 1) ) It won't be included with the CREATE TABLE statement per:- Yes, let's keep it at: it's not in the dumpfile anywhere. > 2) The issue seems to be not the dump, but the non-functional state of the index on the source database. >Is there any indication of why that is happening? Not as far as I know. > Also what error do you get on the source database that tells you the PK is not working? None, only noticed the issue because of the datarefresh to another instance where it turned out the primary key was not created in the target (because it was not in the dumpfile). -----Original Message----- From: Adrian Klaver <[email protected]> Sent: vrijdag 13 februari 2026 17:09 To: Wim Rouquart <[email protected]>; Greg Sabino Mullane <[email protected]> Cc: [email protected] Subject: Re: Index (primary key) corrupt? The real sender of this external email is [email protected] On 2/13/26 00:08, Wim Rouquart wrote: > Internal > > Ok, to do a small recap because indeed this thread has been extended for a while now. > > - The issue with the specific index was noted on a production database (after a datarefresh that partly failed because of the missing index). > > - To reproduce and experiment with the issue, a pg_basebackup was taken from that prod instance and restored to a test instance. Every single test step is executed on this test instance, the prod database is no longer involved, pg_basebackup is no longer involved, everything is pg_dump based from here on onwards. > > - So this means the test pg_dumps where done with the index in a 'non-fuctional state'. As expected, the create statement of the index does NOT show up in the generated .sql scripts (neither 'loose' nor in the create statement of the table). 1) It won't be included with the CREATE TABLE statement per:- https://www.postgresql.org/docs/current/app-pgdump.html "--section=sectionname Only dump the named section. The section name can be pre-data, data, or post-data. This option can be specified more than once to select multiple sections. The default is to dump all sections. The data section contains actual table data, large-object contents, sequence values, and statistics for tables, materialized views, and foreign tables. Post-data items include definitions of indexes, triggers, rules, statistics for indexes, and constraints other than validated check and not-null constraints. Pre-data items include all other data definition items. " 2) The issue seems to be not the dump, but the non-functional state of the index on the source database. Is there any indication of why that is happening? Also what error do you get on the source database that tells you the PK is not working? > > I hope this clears out any confusion. > > -----Original Message----- Adrian Klaver [email protected] Disclaimer <https://www.kbc.com/KBCmailDisclaimer; ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Index (primary key) corrupt? 2026-02-13 16:27 RE: Index (primary key) corrupt? Wim Rouquart <[email protected]> @ 2026-02-13 17:32 ` Adrian Klaver <[email protected]> 2026-03-05 13:23 ` RE: Index (primary key) corrupt? Wim Rouquart <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Adrian Klaver @ 2026-02-13 17:32 UTC (permalink / raw) To: Wim Rouquart <[email protected]>; Greg Sabino Mullane <[email protected]>; +Cc: [email protected] <[email protected]> On 2/13/26 8:27 AM, Wim Rouquart wrote: > Internal > > 1) ) It won't be included with the CREATE TABLE statement per:- > > Yes, let's keep it at: it's not in the dumpfile anywhere. > >> 2) The issue seems to be not the dump, but the non-functional state of the index on the source database. > >> Is there any indication of why that is happening? > > Not as far as I know. So the REINDEX on the source PK is prompted by it not showing up on the target? > >> Also what error do you get on the source database that tells you the PK is not working? > > None, only noticed the issue because of the datarefresh to another instance where it turned out the primary key was not created in the target (because it was not in the dumpfile). On the test instance you set up with pg_basebackup and that has the corrupted index what happens if you try to INSERT a record with a duplicate id? > >> I hope this clears out any confusion. >> >> -----Original Message----- > Adrian Klaver > [email protected] > > Disclaimer <https://www.kbc.com/KBCmailDisclaimer; ^ permalink raw reply [nested|flat] 4+ messages in thread
* RE: Index (primary key) corrupt? 2026-02-13 16:27 RE: Index (primary key) corrupt? Wim Rouquart <[email protected]> 2026-02-13 17:32 ` Re: Index (primary key) corrupt? Adrian Klaver <[email protected]> @ 2026-03-05 13:23 ` Wim Rouquart <[email protected]> 2026-03-05 16:04 ` Re: Index (primary key) corrupt? Adrian Klaver <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Wim Rouquart @ 2026-03-05 13:23 UTC (permalink / raw) To: Adrian Klaver <[email protected]>; Greg Sabino Mullane <[email protected]>; +Cc: [email protected] <[email protected]> Internal > So the REINDEX on the source PK is prompted by it not showing up on the target? That's how we noticed the initial issue yes, we got errors during the datarefresh on the target database where foreign keys wanted to reference the non-existing index (because it wasn't imported). > On the test instance you set up with pg_basebackup and that has the corrupted index what happens if you try to INSERT a record with a duplicate id? Good question, as I kind of expected, it doesn't complain at all when I do an insert with a duplicate id (and the row actually gets inserted). If I consecutively try to do the reindex, then I get the error that it can't because of doubles... -----Original Message----- From: Adrian Klaver <[email protected]> Sent: vrijdag 13 februari 2026 18:33 To: Wim Rouquart <[email protected]>; Greg Sabino Mullane <[email protected]> Cc: [email protected] Subject: Re: Index (primary key) corrupt? The real sender of this external email is [email protected] On 2/13/26 8:27 AM, Wim Rouquart wrote: > Internal > > 1) ) It won't be included with the CREATE TABLE statement per:- > > Yes, let's keep it at: it's not in the dumpfile anywhere. > >> 2) The issue seems to be not the dump, but the non-functional state of the index on the source database. > >> Is there any indication of why that is happening? > > Not as far as I know. So the REINDEX on the source PK is prompted by it not showing up on the target? > >> Also what error do you get on the source database that tells you the PK is not working? > > None, only noticed the issue because of the datarefresh to another instance where it turned out the primary key was not created in the target (because it was not in the dumpfile). On the test instance you set up with pg_basebackup and that has the corrupted index what happens if you try to INSERT a record with a duplicate id? > >> I hope this clears out any confusion. >> >> -----Original Message----- > Adrian Klaver > [email protected] > > Disclaimer <https://www.kbc.com/KBCmailDisclaimer; Disclaimer <https://www.kbc.com/KBCmailDisclaimer; ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Index (primary key) corrupt? 2026-02-13 16:27 RE: Index (primary key) corrupt? Wim Rouquart <[email protected]> 2026-02-13 17:32 ` Re: Index (primary key) corrupt? Adrian Klaver <[email protected]> 2026-03-05 13:23 ` RE: Index (primary key) corrupt? Wim Rouquart <[email protected]> @ 2026-03-05 16:04 ` Adrian Klaver <[email protected]> 0 siblings, 0 replies; 4+ messages in thread From: Adrian Klaver @ 2026-03-05 16:04 UTC (permalink / raw) To: Wim Rouquart <[email protected]>; Greg Sabino Mullane <[email protected]>; +Cc: [email protected] <[email protected]> On 3/5/26 5:23 AM, Wim Rouquart wrote: > Internal > >> So the REINDEX on the source PK is prompted by it not showing up on the target? > > That's how we noticed the initial issue yes, we got errors during the datarefresh on the target database where foreign keys wanted to reference the non-existing index (because it wasn't imported). > >> On the test instance you set up with pg_basebackup and that has the corrupted index what happens if you try to INSERT a record with a duplicate id? > > Good question, as I kind of expected, it doesn't complain at all when I do an insert with a duplicate id (and the row actually gets inserted). If I consecutively try to do the reindex, then I get the error that it can't because of doubles... > > > -----Original Message----- > From: Adrian Klaver <[email protected]> > Sent: vrijdag 13 februari 2026 18:33 > To: Wim Rouquart <[email protected]>; Greg Sabino Mullane <[email protected]> > Cc: [email protected] > Subject: Re: Index (primary key) corrupt? > > > > The real sender of this external email is [email protected] > > > > > > > On 2/13/26 8:27 AM, Wim Rouquart wrote: >> Internal >> >> 1) ) It won't be included with the CREATE TABLE statement per:- >> >> Yes, let's keep it at: it's not in the dumpfile anywhere. >> >>> 2) The issue seems to be not the dump, but the non-functional state of the index on the source database. >> >>> Is there any indication of why that is happening? >> >> Not as far as I know. > > So the REINDEX on the source PK is prompted by it not showing up on the target? > >> >>> Also what error do you get on the source database that tells you the PK is not working? >> >> None, only noticed the issue because of the datarefresh to another instance where it turned out the primary key was not created in the target (because it was not in the dumpfile). > > On the test instance you set up with pg_basebackup and that has the corrupted index what happens if you try to INSERT a record with a duplicate id? Alright, so the corrupt index is transferred by the binary pg_basebackup, but not in logical backups done via pg_dump/pg_restore. The issue then is on the source database with whatever process is corrupting the index and causing no error to be thrown when the table is dumped. Just to be clear we are talking about this table: CREATE TABLE bcf_work_type ( id bigserial NOT NULL, aml_score int8 NOT NULL, CONSTRAINT idx_376814_primary PRIMARY KEY (id) ); What is the use pattern for this table? As I recall this is not a large table, but for completeness what is it's average size? What are the Postgres log settings, on the source database, for?: log_min_messages log_min_error_statement log_error_verbosity log_statement Are there any entries in the Postgres log that reference this table? > >> > >>> I hope this clears out any confusion. >>> >>> -----Original Message----- >> Adrian Klaver >> [email protected] >> >> Disclaimer <https://www.kbc.com/KBCmailDisclaimer; > > > Disclaimer <https://www.kbc.com/KBCmailDisclaimer; -- Adrian Klaver [email protected] ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2026-03-05 16:04 UTC | newest] Thread overview: 4+ messages (download: mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2026-02-13 16:27 RE: Index (primary key) corrupt? Wim Rouquart <[email protected]> 2026-02-13 17:32 ` Adrian Klaver <[email protected]> 2026-03-05 13:23 ` Wim Rouquart <[email protected]> 2026-03-05 16:04 ` Adrian Klaver <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox