starred/netbox
1
0
Fork 0
mirror of https://github.com/netbox-community/netbox.git synced 2026-01-11 21:10:29 +01:00
Code Issues 273 Packages Projects Releases 10 Wiki Activity

Beta: CachedValue search values may (and do) exceed maximum PostgreSQL B-Tree index row size #7299

New Issue
Closed
opened 2025-12-29 20:21:29 +01:00 by adam · 3 comments
adam commented 2025-12-29 20:21:29 +01:00
Owner
Copy Link

Originally created by @moonrail on GitHub (Nov 29, 2022).

Originally assigned to: @jeremystretch on GitHub.

NetBox version

v3.4-beta1

Python version

3.10

Steps to Reproduce

  1. Have a NetBox instance with a lot of real-life data
  2. Try to migrate data from NetBox 3.3.8 to 3.4-beta1

Expected Behavior

Migrations succeed

Observed Behavior

Migration extras.0083_search fails.

Log:

$ NETBOX_DELETE_LEGACY_DATA=1 netbox/manage.py migrate
netbox.denormalized: DEBUG    Registering denormalized field <class 'dcim.models.cables.CableTermination'>._device
netbox.denormalized: DEBUG    Registering denormalized field <class 'dcim.models.cables.CableTermination'>._rack
netbox.denormalized: DEBUG    Registering denormalized field <class 'dcim.models.cables.CableTermination'>._location
Operations to perform:
  Apply all migrations: admin, auth, circuits, contenttypes, dcim, django_rq, extras, ipam, sessions, social_django, taggit, tenancy, users, virtualization, wireless
Running migrations:
  Applying extras.0083_search...Reindexing 63 models.
Clearing cached values... 0 entries deleted.
Indexing models
  circuits.circuit... 153 entries cached.
  circuits.circuittermination... 234 entries cached.
  circuits.circuittype... 20 entries cached.
  circuits.provider... 17 entries cached.
  circuits.providernetwork... 2 entries cached.
  dcim.cable... 13284 entries cached.
  dcim.consoleport... 6478 entries cached.
  dcim.consoleserverport... 1378 entries cached.
  dcim.device... 39290 entries cached.
  dcim.devicebay... 422 entries cached.
  dcim.devicerole... 68 entries cached.
  dcim.devicetype... 1022 entries cached.
  dcim.frontport... 21296 entries cached.
  dcim.interface... 805260 entries cached.
  dcim.inventoryitem... None found.
  dcim.location... 469 entries cached.
  dcim.manufacturer... 144 entries cached.
  dcim.module... 40 entries cached.
  dcim.modulebay... 136 entries cached.
  dcim.moduletype... 24 entries cached.
  dcim.platform... 212 entries cached.
  dcim.powerfeed... 48 entries cached.
  dcim.poweroutlet... 464 entries cached.
  dcim.powerpanel... 3 entries cached.
  dcim.powerport... 33112 entries cached.
  dcim.rack... 7741 entries cached.
  dcim.rackreservation... 223 entries cached.
  dcim.rackrole... 33 entries cached.
  dcim.rearport... 17705 entries cached.
  dcim.region... 39 entries cached.
  dcim.site... 270 entries cached.
  dcim.sitegroup... 18 entries cached.
  dcim.virtualchassis... 347 entries cached.
  dcim.virtualdevicecontext... None found.
  ipam.aggregate... 647 entries cached.
  ipam.asn... 18 entries cached.
  ipam.fhrpgroup... None found.
  ipam.ipaddress... 159974 entries cached.
  ipam.iprange... None found.
  ipam.l2vpn... None found.
  ipam.prefix... 43320 entries cached.
  ipam.rir... 12 entries cached.
  ipam.role... 9 entries cached.
  ipam.routetarget... 129 entries cached.
  ipam.service... None found.
  ipam.servicetemplate... None found.
  ipam.vlan... 30219 entries cached.
  ipam.vlangroup... 298 entries cached.
  ipam.vrf... 7301 entries cached.
  extras.journalentry... 23594 entries cached.
  tenancy.contact... 24 entries cached.
  tenancy.contactgroup... 2 entries cached.
  tenancy.contactrole... 6 entries cached.
  tenancy.tenant... 14755 entries cached.
  tenancy.tenantgroup... 9 entries cached.
  virtualization.cluster... 257 entries cached.
  virtualization.clustergroup... 161 entries cached.
  virtualization.clustertype... 14 entries cached.
  virtualization.virtualmachine... 25878 entries cached.
  virtualization.vminterface... 13559 entries cached.
  wireless.wirelesslan... None found.
  wireless.wirelesslangroup... None found.
  wireless.wirelesslink... None found.
Completed. Total entries: 1270138
Traceback (most recent call last):
  File "/var/dev/netbox/venv/lib64/python3.10/site-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
psycopg2.errors.ProgramLimitExceeded: FEHLER:  Indexzeile benötigt 122744 Bytes, Maximalgröße ist 8191


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/var/dev/netbox/netbox/manage.py", line 10, in <module>
    execute_from_command_line(sys.argv)
  File "/var/dev/netbox/venv/lib64/python3.10/site-packages/django/core/management/__init__.py", line 446, in execute_from_command_line
    utility.execute()
  File "/var/dev/netbox/venv/lib64/python3.10/site-packages/django/core/management/__init__.py", line 440, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/var/dev/netbox/venv/lib64/python3.10/site-packages/django/core/management/base.py", line 402, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/var/dev/netbox/venv/lib64/python3.10/site-packages/django/core/management/base.py", line 448, in execute
    output = self.handle(*args, **options)
  File "/var/dev/netbox/venv/lib64/python3.10/site-packages/django/core/management/base.py", line 96, in wrapped
    res = handle_func(*args, **kwargs)
  File "/var/dev/netbox/venv/lib64/python3.10/site-packages/django/core/management/commands/migrate.py", line 349, in handle
    post_migrate_state = executor.migrate(
  File "/var/dev/netbox/venv/lib64/python3.10/site-packages/django/db/migrations/executor.py", line 135, in migrate
    state = self._migrate_all_forwards(
  File "/var/dev/netbox/venv/lib64/python3.10/site-packages/django/db/migrations/executor.py", line 167, in _migrate_all_forwards
    state = self.apply_migration(
  File "/var/dev/netbox/venv/lib64/python3.10/site-packages/django/db/migrations/executor.py", line 249, in apply_migration
    with self.connection.schema_editor(
  File "/var/dev/netbox/venv/lib64/python3.10/site-packages/django/db/backends/base/schema.py", line 164, in __exit__
    self.execute(sql)
  File "/var/dev/netbox/venv/lib64/python3.10/site-packages/django/db/backends/base/schema.py", line 199, in execute
    cursor.execute(sql, params)
  File "/var/dev/netbox/venv/lib64/python3.10/site-packages/django/db/backends/utils.py", line 103, in execute
    return super().execute(sql, params)
  File "/var/dev/netbox/venv/lib64/python3.10/site-packages/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(
  File "/var/dev/netbox/venv/lib64/python3.10/site-packages/django/db/backends/utils.py", line 80, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/var/dev/netbox/venv/lib64/python3.10/site-packages/django/db/backends/utils.py", line 84, in _execute
    with self.db.wrap_database_errors:
  File "/var/dev/netbox/venv/lib64/python3.10/site-packages/django/db/utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/var/dev/netbox/venv/lib64/python3.10/site-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.OperationalError: FEHLER:  Indexzeile benötigt 122744 Bytes, Maximalgröße ist 8191

which translates to: ERROR: index row requires 122744 bytes, maximum size is 8191

This seems to be the issue from the perspective of the migration:

As I am not a database engineer, I'm somewhat guessing my way through here, so in the case, I've missed an obvious configuration value, please have that in mind.

Django seems to use B-Tree indexes in PostgreSQL by default: https://docs.djangoproject.com/en/4.1/ref/models/indexes/#index-options

B-Tree indexes are described in PostgreSQL-docs with a size limit:

"The only limitation is that an index entry cannot exceed approximately one-third of a page (after TOAST compression, if applicable)."

From my understanding of the matter, the size is exceeded as a CachedValue.value may be too large for a B-Tree index row.

Originally created by @moonrail on GitHub (Nov 29, 2022). Originally assigned to: @jeremystretch on GitHub. ### NetBox version v3.4-beta1 ### Python version 3.10 ### Steps to Reproduce 1. Have a NetBox instance with a lot of real-life data 2. Try to migrate data from NetBox 3.3.8 to 3.4-beta1 ### Expected Behavior Migrations succeed ### Observed Behavior Migration `extras.0083_search` fails. Log: ```bash $ NETBOX_DELETE_LEGACY_DATA=1 netbox/manage.py migrate netbox.denormalized: DEBUG Registering denormalized field <class 'dcim.models.cables.CableTermination'>._device netbox.denormalized: DEBUG Registering denormalized field <class 'dcim.models.cables.CableTermination'>._rack netbox.denormalized: DEBUG Registering denormalized field <class 'dcim.models.cables.CableTermination'>._location Operations to perform: Apply all migrations: admin, auth, circuits, contenttypes, dcim, django_rq, extras, ipam, sessions, social_django, taggit, tenancy, users, virtualization, wireless Running migrations: Applying extras.0083_search...Reindexing 63 models. Clearing cached values... 0 entries deleted. Indexing models circuits.circuit... 153 entries cached. circuits.circuittermination... 234 entries cached. circuits.circuittype... 20 entries cached. circuits.provider... 17 entries cached. circuits.providernetwork... 2 entries cached. dcim.cable... 13284 entries cached. dcim.consoleport... 6478 entries cached. dcim.consoleserverport... 1378 entries cached. dcim.device... 39290 entries cached. dcim.devicebay... 422 entries cached. dcim.devicerole... 68 entries cached. dcim.devicetype... 1022 entries cached. dcim.frontport... 21296 entries cached. dcim.interface... 805260 entries cached. dcim.inventoryitem... None found. dcim.location... 469 entries cached. dcim.manufacturer... 144 entries cached. dcim.module... 40 entries cached. dcim.modulebay... 136 entries cached. dcim.moduletype... 24 entries cached. dcim.platform... 212 entries cached. dcim.powerfeed... 48 entries cached. dcim.poweroutlet... 464 entries cached. dcim.powerpanel... 3 entries cached. dcim.powerport... 33112 entries cached. dcim.rack... 7741 entries cached. dcim.rackreservation... 223 entries cached. dcim.rackrole... 33 entries cached. dcim.rearport... 17705 entries cached. dcim.region... 39 entries cached. dcim.site... 270 entries cached. dcim.sitegroup... 18 entries cached. dcim.virtualchassis... 347 entries cached. dcim.virtualdevicecontext... None found. ipam.aggregate... 647 entries cached. ipam.asn... 18 entries cached. ipam.fhrpgroup... None found. ipam.ipaddress... 159974 entries cached. ipam.iprange... None found. ipam.l2vpn... None found. ipam.prefix... 43320 entries cached. ipam.rir... 12 entries cached. ipam.role... 9 entries cached. ipam.routetarget... 129 entries cached. ipam.service... None found. ipam.servicetemplate... None found. ipam.vlan... 30219 entries cached. ipam.vlangroup... 298 entries cached. ipam.vrf... 7301 entries cached. extras.journalentry... 23594 entries cached. tenancy.contact... 24 entries cached. tenancy.contactgroup... 2 entries cached. tenancy.contactrole... 6 entries cached. tenancy.tenant... 14755 entries cached. tenancy.tenantgroup... 9 entries cached. virtualization.cluster... 257 entries cached. virtualization.clustergroup... 161 entries cached. virtualization.clustertype... 14 entries cached. virtualization.virtualmachine... 25878 entries cached. virtualization.vminterface... 13559 entries cached. wireless.wirelesslan... None found. wireless.wirelesslangroup... None found. wireless.wirelesslink... None found. Completed. Total entries: 1270138 Traceback (most recent call last): File "/var/dev/netbox/venv/lib64/python3.10/site-packages/django/db/backends/utils.py", line 89, in _execute return self.cursor.execute(sql, params) psycopg2.errors.ProgramLimitExceeded: FEHLER: Indexzeile benötigt 122744 Bytes, Maximalgröße ist 8191 The above exception was the direct cause of the following exception: Traceback (most recent call last): File "/var/dev/netbox/netbox/manage.py", line 10, in <module> execute_from_command_line(sys.argv) File "/var/dev/netbox/venv/lib64/python3.10/site-packages/django/core/management/__init__.py", line 446, in execute_from_command_line utility.execute() File "/var/dev/netbox/venv/lib64/python3.10/site-packages/django/core/management/__init__.py", line 440, in execute self.fetch_command(subcommand).run_from_argv(self.argv) File "/var/dev/netbox/venv/lib64/python3.10/site-packages/django/core/management/base.py", line 402, in run_from_argv self.execute(*args, **cmd_options) File "/var/dev/netbox/venv/lib64/python3.10/site-packages/django/core/management/base.py", line 448, in execute output = self.handle(*args, **options) File "/var/dev/netbox/venv/lib64/python3.10/site-packages/django/core/management/base.py", line 96, in wrapped res = handle_func(*args, **kwargs) File "/var/dev/netbox/venv/lib64/python3.10/site-packages/django/core/management/commands/migrate.py", line 349, in handle post_migrate_state = executor.migrate( File "/var/dev/netbox/venv/lib64/python3.10/site-packages/django/db/migrations/executor.py", line 135, in migrate state = self._migrate_all_forwards( File "/var/dev/netbox/venv/lib64/python3.10/site-packages/django/db/migrations/executor.py", line 167, in _migrate_all_forwards state = self.apply_migration( File "/var/dev/netbox/venv/lib64/python3.10/site-packages/django/db/migrations/executor.py", line 249, in apply_migration with self.connection.schema_editor( File "/var/dev/netbox/venv/lib64/python3.10/site-packages/django/db/backends/base/schema.py", line 164, in __exit__ self.execute(sql) File "/var/dev/netbox/venv/lib64/python3.10/site-packages/django/db/backends/base/schema.py", line 199, in execute cursor.execute(sql, params) File "/var/dev/netbox/venv/lib64/python3.10/site-packages/django/db/backends/utils.py", line 103, in execute return super().execute(sql, params) File "/var/dev/netbox/venv/lib64/python3.10/site-packages/django/db/backends/utils.py", line 67, in execute return self._execute_with_wrappers( File "/var/dev/netbox/venv/lib64/python3.10/site-packages/django/db/backends/utils.py", line 80, in _execute_with_wrappers return executor(sql, params, many, context) File "/var/dev/netbox/venv/lib64/python3.10/site-packages/django/db/backends/utils.py", line 84, in _execute with self.db.wrap_database_errors: File "/var/dev/netbox/venv/lib64/python3.10/site-packages/django/db/utils.py", line 91, in __exit__ raise dj_exc_value.with_traceback(traceback) from exc_value File "/var/dev/netbox/venv/lib64/python3.10/site-packages/django/db/backends/utils.py", line 89, in _execute return self.cursor.execute(sql, params) django.db.utils.OperationalError: FEHLER: Indexzeile benötigt 122744 Bytes, Maximalgröße ist 8191 ``` which translates to: `ERROR: index row requires 122744 bytes, maximum size is 8191` This seems to be the issue from the perspective of the migration: * https://github.com/netbox-community/netbox/blob/v3.4-beta1/netbox/extras/migrations/0083_search.py#L12 * causes run of: https://github.com/netbox-community/netbox/blob/v3.4-beta1/netbox/extras/management/commands/reindex.py * causes run of: https://github.com/netbox-community/netbox/blob/v3.4-beta1/netbox/netbox/search/backends.py#L138 As I am not a database engineer, I'm somewhat guessing my way through here, so in the case, I've missed an obvious configuration value, please have that in mind. Django seems to use B-Tree indexes in PostgreSQL by default: https://docs.djangoproject.com/en/4.1/ref/models/indexes/#index-options B-Tree indexes are described in PostgreSQL-docs with a size limit: > ["The only limitation is that an index entry cannot exceed approximately one-third of a page (after TOAST compression, if applicable)."](https://www.postgresql.org/docs/current/btree-intro.html) From my understanding of the matter, the size is exceeded as a [CachedValue.value](https://github.com/netbox-community/netbox/blob/v3.4-beta1/netbox/extras/models/search.py#L39) may be too large for a B-Tree index row.
adam added the type: bugstatus: acceptedbeta labels 2025-12-29 20:21:29 +01:00
adam closed this issue 2025-12-29 20:21:30 +01:00
adam commented 2025-12-29 20:21:30 +01:00
Author
Owner
Copy Link

@kkthxbye-code commented on GitHub (Nov 29, 2022):

I'm not sure how we would solve this properly other then truncating the value at some arbitrary length. The alternative would be switching to the actual full text search functionality in postgres (tsvector), but not sure how well that fits most of the data as it tends to be geared towards searching natural languages.

@kkthxbye-code commented on GitHub (Nov 29, 2022): I'm not sure how we would solve this properly other then truncating the value at some arbitrary length. The alternative would be switching to the actual full text search functionality in postgres (tsvector), but not sure how well that fits most of the data as it tends to be geared towards searching natural languages.
adam commented 2025-12-29 20:21:30 +01:00
Author
Owner
Copy Link

@jeremystretch commented on GitHub (Nov 29, 2022):

Seems like a fairly niche scenario to have 8KB of data in a searchable field. IMO truncation is an acceptable compromise.

@jeremystretch commented on GitHub (Nov 29, 2022): Seems like a fairly niche scenario to have 8KB of data in a searchable field. IMO truncation is an acceptable compromise.
adam commented 2025-12-29 20:21:30 +01:00
Author
Owner
Copy Link

@jeremystretch commented on GitHub (Dec 1, 2022):

By the way @moonrail thank you for the detailed analysis!

@jeremystretch commented on GitHub (Dec 1, 2022): By the way @moonrail thank you for the detailed analysis!
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
beta
breaking change
complexity: high
complexity: low
complexity: medium
needs milestone
netbox
pending closure
plugin candidate
pull-request
Mirrored from GitHub Pull Request
 severity: high
severity: low
severity: medium
status: accepted
status: backlog
status: blocked
status: duplicate
status: needs owner
status: needs triage
status: revisions needed
status: under review
topic: GraphQL
topic: Internationalization
topic: OpenAPI
topic: UI/UX
topic: cabling
topic: event rules
topic: htmx navigation
topic: industrialization
topic: migrations
topic: plugins
topic: scripts
topic: templating
topic: testing
type: bug
type: deprecation
type: documentation
type: feature
type: housekeeping
type: translation
No Label beta status: accepted type: bug
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
adam
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: starred/netbox#7299
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?