You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@trafficcontrol.apache.org by GitBox <gi...@apache.org> on 2019/08/06 06:45:17 UTC
[GitHub] [trafficcontrol] mkrug1981 opened a new issue #3777: Creating large
text values on paramaters causes Postgres error -> exceeds maximum for
index 2712
mkrug1981 opened a new issue #3777: Creating large text values on paramaters causes Postgres error -> exceeds maximum for index 2712
URL: https://github.com/apache/trafficcontrol/issues/3777
<!--
************ STOP!! ************
If this issue identifies a security vulnerability, DO NOT submit it! Instead, contact
the Apache Software Foundation Security Team at security@trafficcontrol.apache.org and follow the
guidelines at https://www.apache.org/security/ regarding vulnerability disclosure.
-->
<!--
- For *SUPPORT QUESTIONS*, use the
[Traffic Control slack channels](https://traffic-control-cdn.slack.com) or [Traffic Control mailing lists](http://trafficcontrol.apache.org/mailing_lists/).
- Before submitting, please **SEARCH GITHUB** for a similar issue or PR. -->
## I'm submitting a ...
<!-- (check all that apply with "[x]") -->
<!--- security vulnerability (STOP!! - see above)-->
- [x] bug report
- [ ] new feature / enhancement request
- [ ] improvement request (usability, performance, tech debt, etc.)
- [ ] other <!--(Please do not submit support requests here - see above)-->
## Traffic Control components affected ...
<!-- (check all that apply with "[x]") -->
- [ ] CDN in a Box
- [ ] Documentation
- [ ] Grove
- [ ] Traffic Control Client
- [ ] Traffic Monitor
- [x] Traffic Ops
- [ ] Traffic Ops ORT
- [ ] Traffic Portal
- [ ] Traffic Router
- [ ] Traffic Stats
- [ ] Traffic Vault
- [ ] unknown
## Current behavior:
Creating large text values on paramaters causes following postgress database error.
```
ERROR: 54000: index row size 2728 exceeds maximum 2712 for index "unique_param"
HINT: Values larger than 1/3 of a buffer page cannot be indexed.
Consider a function index of an MD5 hash of the value, or use full text indexing.
LOCATION: _bt_findinsertloc, nbtinsert.c:583
STATEMENT: UPDATE
parameter SET
config_file=$1,
id=$2,
name=$3,
value=$4,
secure=$5
WHERE id=$6 RETURNING last_updated
```
## Expected / new behavior:
Possibility to store large text values, like complex lua code files as parameter without errors.
Not sure but disabling indexing for the text field might help?
## Minimal reproduction of the problem with instructions:
Create a parameter with a text value with more as 2800 characters will trigger the issue.
```
$ cat /dev/random | LC_CTYPE=C tr -dc "[:alpha:]" | head -c 2680
$ curl -vv -o /dev/null https://tp.random.com/api/1.4/parameters/3996 --cookie 'mojolicious=xxxxxxxxxxxxx' -H 'application/json' -d '{"configFile":"pdl_config.lua","id":3996,"name":"data","secure":false,"value":"CgfLkNHOjxGmFrXLxTDKHmEdGrkiMgyQPEQQuYRKuvCLtzuWKiiLveOLYdQZJMApYELhxIMhvkbBszzJeswNCOzedZiVAQlLQSYzLoCfJzVOUppseXePDjoteIGcDleiKZRyjxcirYMSNqwHfyDIElpjOGyZkvKoXVsDqyJAsQxYtMbJAiUcbOLibVWYRbflDjzNwPVinmkrHmWbwTfFIqKHjzFjmarNAkUMzwQZyoeqWikAAVvmroAzrKnODlWjIoewsgiYYiilUMVTcXXNLHxpBBgRsitHVPnnZUcdxMCDkykoTiTWdSHUbLRgUbFivjaorGRpxQtYvyjgkvUwShakpzyoTIolHeJmaBbHJBkGvczGsSRDPpuYtPSWECotFTOJShUAlailYJlndWcEvjqwxohRPMGNvsRZGhiVhGxvtghsBsXxdgsztQsFWVHzdFmSiystfXEjYILFYpmdjvBLtYWZkaRqhUwvYXwmmiEtmXESskxcAmuVPCPVlXgFfKixmAHcedksNaKiEIHXzJhUviZkFPewDcVwlQQhizEbwcEtJlMKNmHXxuynhRsgIcMuNUzwWMQjmFpLecGIWxCeclWBOnOpYjKsgyCxnfFzEVofpnLEWayxwlNrBXOcoXszlwahFzQfcIqpYmRGtMVBtAfRKcMjeokTXyOzeuqLibkWxdIkqOyYZDXMtQctocZDVMSCsvdHrrFLgjaIyvpEbeOMTFWqeKdkqzRIyVMwscUkqUkvsRQwYHuhcnMdcgzMgmbxNUOKdwnPFJskKmgryJnnLQIHMvElTCetupukcVSznkstrDcelRSTpiauKuVcSNSWLJLWSosaFCgsJSJqyrVOXKGOpZuKmEYzuzkiOrBiOjdHnRjsWDUfbvKYaXlHSRguaMwapLFGkTgCMGjWIXQTzuMUkbXKtlFEsDAJNOqQyaLduAbHzkLjeaqnpSKhnjydjGquDCFaYIykopTjTlaCnRyPmSgKJxIlqaKzbsLUslrNOSFcJdQXXajiAXBlsUKPWHIKMUEPlkUptfFfKRYPXGoSptvTrFQSDsMmqfevBHGqxJIThllVCoedfLkoftAcQGTMFBqqYJldfbjgkpeKEVyfXvVGVoWprGaCczUaBwDoQyUEKAFsoJakkeSJtuBdpbcXWFLOdLlYMXdAuEVmIjXFtJwnZFYaEKeFEFnswzoyywUPXOksDCgxmSeHBXskgixuVLQLIBeNFPDuzSwqTVXCMcdjzqcFCiKMcQuYlTfEWvZirgzVivIvYnchsOqOOQgnObDFCNmYblGTaMUJFeaHDRdwegZAMUXaNOCQGVyvPgjRVlRKravJxegEwAbsCyCTSKjHzqWgGLKswddRLMpFGrtKnWFwuwGUURNJXctszhEWDBmSNqHTkrvAwgssGtwSeRcRAlxVSJpCHVMpYrwgYLrtUSnbekPloEqGibVfKNhGNfRsniDbBRLrcGBkVwAqIrISIfbqQJBKIJFAGtXxxbcLsvgTvkvaSXZGoYHhIaZuiqISmLZgEbJiwHZblBhBXGjvypThZpCPHajotxAaKzhoXDaXclhyjlEPrjQTVPRGRxRVduvVfRVnMbBQYRmlDPQrcMHGPIOBRUuPEiBazastFLAWjAIWADsQGxlRxBfcWoScVXnUVTIXmXelqEWRyJdpRgIonkvcWudtsoNciFKbTHXtamwvavciASTRqdcKNinsAkyhXDizRrWzVtUHHoDXBSMKPkrjnLBTqgLNKeVKHRTjSnHHkbqcQGQfUhwHYbbgrHyNJfUWYPaDeITLMHKBPfxxAjDxiFScIhyVnQngIpvHsWyouUMXUWMOfMMDzrGioGleaEudkgcypjgvyLcsrDjoHWiJZbZbVMIYFcieRudOAMqyffOswOOxkcgGLALonwPOaaaDmcyruFnxVYhQzbcKmAxNqlkdZATgQKCotOGRqMqMulsKxDiivkdpBtmiSNHtnapdZyaOdAxqoUbYrcNySKuxmYMYKeGRotDqRwlaMxiWAZieMcLyfLrlNhMYtgOKcyPSPSPoFlBdwJYvLfjerSWBiiKneWDkfifJhcXXkIcDyNsojTSxDqnkaCGxwakfzyPSbEQroheMwRmEGevxFcoUvBhuBeBrcvOjSgWbMsxnLnsGzGRHlNFKaFmGLsKSCetsnEHAFYTtSfAJDUAwgUZaQnAfoqcVyolrFCwKqfQmNurwdqUJeSfbVdadGouoVYRmcWSacnJCYUmGIrfRJdohyKQlpTMqyxZGikKljRsVJFqPRwFcqZqXrdAgRemvNhzctkckDrPphxLXCBVluVrVcefmgviFLLmmolRCSBSRlMFxQrAhUQhneiyvwoTfWnsgXsfkugQpxDcqoUrSBFvXAfceULBhJnznbBesIUcpKOEVXIOiLQJQyLTKSkNcxFVDvpbKNKNTwwxmxOVxmyLqiTPHDcpxYKtuObSbmWmDNWaJtpoXGyfyJCWGuQawEyhrxOGFmbrRvkJLoIHcaniDbqYkgacKTsKamHpuxZdzDJaRYCddhpQUdHAgIUQSslyGattYgzqlrxqQwLsRpPBIkTJfINVUFHKykbTshfkrBiNZvTnzXHIYTpgyOSPPLLSIIMylqcjfzVxLuzzTQZNgloojQgOBHeTmFYHkNMoCqfghmxBLLaIQLB"}' -k -X PUT 2>&1 | egrep 'HTTP/'
> PUT /api/1.4/parameters/3996 HTTP/1.1
< HTTP/1.1 100 Continue
< HTTP/1.1 500 Internal Server Error
```
## Anything else:
Some database details output for the tables and parameter settings
```
$ psql -h x.x.x.x -U traffic_ops
Password for user traffic_ops:
psql (9.6.14, server 9.6.13)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
traffic_ops=#
traffic_ops=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-------------+-------------+----------+-------------+-------------+-----------------------
grafanadb | grafana | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
traffic_ops | traffic_ops | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(5 rows)
traffic_ops=#
postgres=# \c traffic_ops
psql (9.6.14, server 9.6.13)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
You are now connected to database "traffic_ops" as user "traffic_ops".
traffic_ops=#
traffic_ops=# \dt
List of relations
Schema | Name | Type | Owner
--------+---------------------------------+-------+-------------
public | api_capability | table | traffic_ops
public | asn | table | traffic_ops
public | cachegroup | table | traffic_ops
public | cachegroup_fallbacks | table | traffic_ops
public | cachegroup_localization_method | table | traffic_ops
public | cachegroup_parameter | table | traffic_ops
public | capability | table | traffic_ops
public | cdn | table | traffic_ops
public | coordinate | table | traffic_ops
public | deliveryservice | table | traffic_ops
public | deliveryservice_regex | table | traffic_ops
public | deliveryservice_request | table | traffic_ops
public | deliveryservice_request_comment | table | traffic_ops
public | deliveryservice_server | table | traffic_ops
public | deliveryservice_tmuser | table | traffic_ops
public | division | table | traffic_ops
public | federation | table | traffic_ops
public | federation_deliveryservice | table | traffic_ops
public | federation_federation_resolver | table | traffic_ops
public | federation_resolver | table | traffic_ops
public | federation_tmuser | table | traffic_ops
public | goose_db_version | table | traffic_ops
public | hwinfo | table | traffic_ops
public | job | table | traffic_ops
public | job_agent | table | traffic_ops
public | job_status | table | traffic_ops
public | log | table | traffic_ops
public | origin | table | traffic_ops
public | parameter | table | traffic_ops
public | phys_location | table | traffic_ops
public | profile | table | traffic_ops
public | profile_parameter | table | traffic_ops
public | regex | table | traffic_ops
public | region | table | traffic_ops
public | role | table | traffic_ops
public | role_capability | table | traffic_ops
public | server | table | traffic_ops
public | servercheck | table | traffic_ops
public | snapshot | table | traffic_ops
public | staticdnsentry | table | traffic_ops
public | stats_summary | table | traffic_ops
public | status | table | traffic_ops
public | steering_target | table | traffic_ops
public | tenant | table | traffic_ops
public | tm_user | table | traffic_ops
public | to_extension | table | traffic_ops
public | type | table | traffic_ops
public | user_role | table | traffic_ops
(48 rows)
traffic_ops=# \d+ parameter
Table "public.parameter"
Column | Type | Modifiers | Storage | Stats target | Description
--------------+--------------------------+--------------------------------------------------------+----------+--------------+-------------
id | bigint | not null default nextval('parameter_id_seq'::regclass) | plain | |
name | text | not null | extended | |
config_file | text | | extended | |
value | text | not null | extended | |
last_updated | timestamp with time zone | not null default now() | plain | |
secure | boolean | not null default false | plain | |
Indexes:
"idx_89644_primary" PRIMARY KEY, btree (id)
"unique_param" UNIQUE CONSTRAINT, btree (name, config_file, value)
"idx_89644_parameter_name_value_idx" btree (name, value)
Referenced by:
TABLE "profile_parameter" CONSTRAINT "fk_atsprofile_atsparameters_atsparameters1" FOREIGN KEY (parameter) REFERENCES parameter(id) ON DELETE CASCADE
TABLE "cachegroup_parameter" CONSTRAINT "fk_parameter" FOREIGN KEY (parameter) REFERENCES parameter(id) ON DELETE CASCADE
Triggers:
on_update_current_timestamp BEFORE UPDATE ON parameter FOR EACH ROW EXECUTE PROCEDURE on_update_current_timestamp_last_updated()
traffic_ops=#
```
<!--
Licensed to the Apache Software Foundation (ASF) under one
or more contributor license agreements. See the NOTICE file
distributed with this work for additional information
regarding copyright ownership. The ASF licenses this file
to you under the Apache License, Version 2.0 (the
"License"); you may not use this file except in compliance
with the License. You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing,
software distributed under the License is distributed on an
"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
KIND, either express or implied. See the License for the
specific language governing permissions and limitations
under the License.
-->
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services