search

yugabyte / yugabyte-db

YugabyteDB - the cloud native distributed SQL database for mission-critical applications.
https://www.yugabyte.com
Other
8.99k stars 1.07k forks source link

[YSQL][LST] ERROR: duplicate key value violates unique constraint "pg_attribute_relid_attnum_index" #12449

Open def- opened 2 years ago

def- commented 2 years ago

Jira Link: [DB-289]

Description

This failure happened using the Long-running System Test framework I'm currently working on. Yugabyte-DB is on code state 817b50bd83a8590474b86741b8592504dc6edd7c, Release build on Almalinux 8:

$ git checkout 92ea26cdb35dad8c962aba03495c8353a28a24fa && python3.9 ./long_system_test.py --nodes=127.0.0.1:5433,127.0.0.2:5433,127.0.0.3:5433 --threads=8 --seed=813346 --complexity=full
[...]
2022-05-10 16:32:07,443 worker_4   ERROR    Unexpected query failure: UniqueViolation
Query: ALTER TABLE tg4_1 ADD c3_text TEXT;
  values: None
  runtime: 2022-05-10 16:32:07.409 - 2022-05-10 16:32:07.442
  supports explain: False
  supports rollback: False
  affected rows: None
Action: AddColumnAction
Error class: UniqueViolation
Error code: 23505
Error message: ERROR:  duplicate key value violates unique constraint "pg_attribute_relid_attnum_index"
CONTEXT:  Catalog Version Mismatch: A DDL occurred while processing this query. Try again.
Transaction isolation level: repeatable read
DB Node: host: 127.0.0.2, port: 5433
DB Backend PID: 127648

lst_2022-05-10_16:31:09_813346.zip Can not reproduce it reliably, but happens sporadically.

def- commented 2 years ago

Also happens with other pg attributes (4c08faee38cf8cb0affeff1785751cd93cabb9a5):

2022-06-29 10:30:40,061 worker_14  ERROR    Unexpected query failure: UniqueViolation
Query: ALTER TABLE t3 ADD c33_numrange NUMRANGE DEFAULT '(60.7317313429661,85.58827923606171]'::NUMRANGE;
  values: None
  runtime: 2022-06-29 10:30:39.891 - 2022-06-29 10:30:40.061
  supports explain: False
  supports rollback: False
  affected rows: None
Action: AddColumnAction
Error class: UniqueViolation
Error code: 23505
Error message: ERROR:  duplicate key value violates unique constraint "pg_attrdef_adrelid_adnum_index"
    @           0x997be8  errmsg
    @           0x9c31b8  HandleYBStatusAtErrorLevel
    @           0x55f347  ybc_getnext_heaptuple
    @           0x55fabe  ybc_systable_getnext
    @           0x979cc4  SearchCatCacheMiss
    @           0x978bd7  SearchCatCacheInternal
    @           0x9903fa  SearchSysCacheCopy
    @           0x572b09  StoreAttrDefault
    @           0x572eaa  AddRelationNewConstraints
    @           0x671171  ATExecAddColumn
    @           0x66990f  ATExecCmd
    @           0x662c17  ATController
    @           0x845b5f  ProcessUtilitySlow
    @           0x844888  standard_ProcessUtility
    @           0x843e5e  YBProcessUtilityDefaultHook
    @     0x7f7127e1de20  pgss_ProcessUtility
    @     0x7f7127e14b07  ybpgm_ProcessUtility
    @     0x7f7127df958e  pg_hint_plan_ProcessUtility
    @           0x9c6247  YBTxnDdlProcessUtility
    @           0x843aaa  PortalRunUtility
    @           0x843167  PortalRunMulti
    @           0x842906  PortalRun
    @           0x840106  yb_exec_simple_query_impl
    @           0x8406a6  yb_exec_query_wrapper_one_attempt
    @           0x83d728  PostgresMain
    @           0x7ad83c  BackendRun
    @           0x7acc90  ServerLoop
    @           0x7a92fb  PostmasterMain
    @           0x71302d  PostgresServerProcessMain
    @           0x7134d2  main
    @     0x7f712c50b825  __libc_start_main
    @           0x4b8a29  _start

    @           0x997be8  errmsg
    @           0x83e90b  YBPrepareCacheRefreshIfNeeded
    @           0x83d460  PostgresMain
    @           0x7ad83c  BackendRun
    @           0x7acc90  ServerLoop
    @           0x7a92fb  PostmasterMain
    @           0x71302d  PostgresServerProcessMain
    @           0x7134d2  main
    @     0x7f712c50b825  __libc_start_main
    @           0x4b8a29  _start

CONTEXT:  Catalog Version Mismatch: A DDL occurred while processing this query. Try again.
Transaction isolation level: repeatable read
DB Node: host: 127.0.0.1, port: 5433
DB Backend PID: 202670
archit-rastogi commented 2 months ago

Able to reproduce similar error via sample app, on local - 2.21.0.1-b1 

2024-09-02 15:00:43,236 [Thread-6] ERROR ExceptionsTracker - Failed adding column: ERROR: duplicate key value violates unique constraint "pg_attribute_relid_attnum_index"
  Where: Cat
com.yugabyte.util.PSQLException: ERROR: duplicate key value violates unique constraint "pg_attribute_relid_attnum_index"
  Where: Catalog Version Mismatch: A DDL occurred while processing this query. Try again.
    at com.yugabyte.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2675) ~[yb-stress-sample-apps.jar:?]
    at com.yugabyte.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2365) ~[yb-stress-sample-apps.jar:?]
    at com.yugabyte.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:355) ~[yb-stress-sample-apps.jar:?]
    at com.yugabyte.jdbc.PgStatement.executeInternal(PgStatement.java:490) ~[yb-stress-sample-apps.jar:?]
    at com.yugabyte.jdbc.PgStatement.execute(PgStatement.java:408) ~[yb-stress-sample-apps.jar:?]
    at com.yugabyte.jdbc.PgStatement.executeWithFlags(PgStatement.java:329) ~[yb-stress-sample-apps.jar:?]
    at com.yugabyte.jdbc.PgStatement.executeCachedSql(PgStatement.java:315) ~[yb-stress-sample-apps.jar:?]
    at com.yugabyte.jdbc.PgStatement.executeWithFlags(PgStatement.java:291) ~[yb-stress-sample-apps.jar:?]
    at com.yugabyte.jdbc.PgStatement.executeUpdate(PgStatement.java:265) ~[yb-stress-sample-apps.jar:?]
    at com.yugabyte.sample.apps.SqlDataLoadWithDDL.addColumn(SqlDataLoadWithDDL.java:574) [yb-stress-sample-apps.jar:?]
    at com.yugabyte.sample.apps.SqlDataLoadWithDDL.doWrite(SqlDataLoadWithDDL.java:347) [yb-stress-sample-apps.jar:?]
    at com.yugabyte.sample.apps.AppBase.performWrite(AppBase.java:1029) [yb-stress-sample-apps.jar:?]
    at com.yugabyte.sample.common.IOPSThread.run(IOPSThread.java:88) [yb-stress-sample-apps.jar:?]
2024-09-02 15:00:43,727 [Thread-4] ERROR ExceptionsTracker - Failed adding column: ERROR: duplicate key value violates unique constraint "pg_attribute_relid_attnum_index"
  Where: Cat
com.yugabyte.util.PSQLException: ERROR: duplicate key value violates unique constraint "pg_attribute_relid_attnum_index"
  Where: Catalog Version Mismatch: A DDL occurred while processing this query. Try again.
    at com.yugabyte.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2675) ~[yb-stress-sample-apps.jar:?]
    at com.yugabyte.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2365) ~[yb-stress-sample-apps.jar:?]
    at com.yugabyte.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:355) ~[yb-stress-sample-apps.jar:?]
    at com.yugabyte.jdbc.PgStatement.executeInternal(PgStatement.java:490) ~[yb-stress-sample-apps.jar:?]
    at com.yugabyte.jdbc.PgStatement.execute(PgStatement.java:408) ~[yb-stress-sample-apps.jar:?]
    at com.yugabyte.jdbc.PgStatement.executeWithFlags(PgStatement.java:329) ~[yb-stress-sample-apps.jar:?]
    at com.yugabyte.jdbc.PgStatement.executeCachedSql(PgStatement.java:315) ~[yb-stress-sample-apps.jar:?]
    at com.yugabyte.jdbc.PgStatement.executeWithFlags(PgStatement.java:291) ~[yb-stress-sample-apps.jar:?]
    at com.yugabyte.jdbc.PgStatement.executeUpdate(PgStatement.java:265) ~[yb-stress-sample-apps.jar:?]
    at com.yugabyte.sample.apps.SqlDataLoadWithDDL.addColumn(SqlDataLoadWithDDL.java:574) [yb-stress-sample-apps.jar:?]
    at com.yugabyte.sample.apps.SqlDataLoadWithDDL.doWrite(SqlDataLoadWithDDL.java:347) [yb-stress-sample-apps.jar:?]
    at com.yugabyte.sample.apps.AppBase.performWrite(AppBase.java:1029) [yb-stress-sample-apps.jar:?]
    at com.yugabyte.sample.common.IOPSThread.run(IOPSThread.java:88) [yb-stress-sample-apps.jar:?]

Sample App details: https://github.com/yugabyte/yb-stress-test/releases/tag/ssa_1.1.75

Run with below:

java -jar yb-stress-sample-apps.jar \
      --workload SqlDataLoadWithDDL \
      --nodes 127.0.0.1:5433 \
      --num_threads_write 6 \
      --num_foreign_keys 2 \
      --batch_size 20 \
      --num_threads_read 3 \
      --num_unique_keys 10000000000 \
      --default_postgres_database yugabyte \
      --ddl_operations "INSERT_ROW,ADD_COLUMN,CHANGE_TYPE" \
      --ddl_weights "100,150,230" \
      --num_foreign_key_table_rows 5000 \
      --num_consecutive_rows_with_same_fk 50 \
      --username yugabyte