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 2021/08/26 20:31:25 UTC
[GitHub] [trafficcontrol] zrhoffman opened a new pull request #6143: Use PostgreSQL 9.6-compatible syntax in create_tables.sql and migrations
zrhoffman opened a new pull request #6143:
URL: https://github.com/apache/trafficcontrol/pull/6143
<!--
Thank you for contributing! Please be sure to read our contribution guidelines: https://github.com/apache/trafficcontrol/blob/master/CONTRIBUTING.md
If this closes or relates to an existing issue, please reference it using one of the following:
Closes: #ISSUE
Related: #ISSUE
If this PR fixes a security vulnerability, DO NOT submit! Instead, contact
the Apache Traffic Control Security Team at security@trafficcontrol.apache.org and follow the
guidelines at https://apache.org/security regarding vulnerability disclosure.
-->
This PR fixes PostgreSQL errors a user would encounter if trying to use Traffic Ops from the master or 6.0.x branches with a PostgreSQL 9.6 database. By using these fixes, users will have the option of upgrading Traffic Ops before upgrading the TO database, rather than needing to upgrade the database before upgrading Traffic Ops.
<!-- **^ Add meaningful description above** --><hr>
## Which Traffic Control components are affected by this PR?
<!-- Please delete all components from this list that are NOT affected by this PR.
Feel free to add the name of a tool or script that is affected but not on the list.
-->
- Traffic Ops
## What is the best way to verify this PR?
<!-- Please include here ALL the steps necessary to test your PR.
If your PR has tests (and most should), provide the steps needed to run the tests.
If not, please provide step-by-step instructions to test the PR manually and explain why your PR does not need tests. -->
- Run the Traffic Ops postinstall against a PostgreSQL 9.6 database
- Run the TO API tests against a PostgreSQL 9.6 database
For convenience, I have downgraded PostgreSQL in the GitHub Actions, the t3c integration tests, and CDN in a Box on the [pg96-compat-9.6](https://github.com/zrhoffman/trafficcontrol/commit/pg96-compat-9.6) branch on my fork.
## If this is a bugfix, which Traffic Control versions contained the bug?
<!-- Delete this section if the PR is not a bugfix, or if the bug is only in the master branch.
Examples:
- 5.1.2
- 5.1.3 (RC1)
-->
- 6.0.x
- master branch
## PR submission checklist
- [x] This PR has tests <!-- If not, please delete this text and explain why this PR does not need tests. -->
- [ ] This PR has documentation <!-- If not, please delete this text and explain why this PR does not need documentation. -->
- [ ] This PR has a CHANGELOG.md entry <!-- A fix for a bug from an ATC release, an improvement, or a new feature should have a changelog entry. -->
- [ ] This PR **DOES NOT FIX A SERIOUS SECURITY VULNERABILITY** (see [the Apache Software Foundation's security guidelines](https://apache.org/security) for details)
<!--
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.
To unsubscribe, e-mail: issues-unsubscribe@trafficcontrol.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [trafficcontrol] zrhoffman commented on pull request #6143: Use PostgreSQL 9.6-compatible syntax in create_tables.sql and migrations
Posted by GitBox <gi...@apache.org>.
zrhoffman commented on pull request #6143:
URL: https://github.com/apache/trafficcontrol/pull/6143#issuecomment-906796363
It needs to be `FOR EACH ROW`, not `FOR EACH STATEMENT`. Fixed in 31a6bb3e7b.
--
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.
To unsubscribe, e-mail: issues-unsubscribe@trafficcontrol.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [trafficcontrol] ocket8888 commented on a change in pull request #6143: Use PostgreSQL 9.6-compatible syntax in create_tables.sql and migrations
Posted by GitBox <gi...@apache.org>.
ocket8888 commented on a change in pull request #6143:
URL: https://github.com/apache/trafficcontrol/pull/6143#discussion_r696958120
##########
File path: traffic_ops/app/db/migrations/2021061100000000_ds_tls_versions.up.sql
##########
@@ -39,20 +39,18 @@ BEGIN
SET last_updated=now()
WHERE id IN (
SELECT deliveryservice
- FROM old_table
+ FROM deliveryservice_tls_version
);
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER update_ds_timestamp_on_tls_version_insertion
AFTER INSERT ON public.deliveryservice_tls_version
- REFERENCING NEW TABLE AS new_table
FOR EACH STATEMENT EXECUTE PROCEDURE update_ds_timestamp_on_insert();
CREATE TRIGGER update_ds_timestamp_on_tls_version_delete
AFTER DELETE ON public.deliveryservice_tls_version
- REFERENCING OLD TABLE AS old_table
Review comment:
I think the changes to this migration will cause **all** Delivery Services to have their timestamps updated whenever any of them has a TLS version change, won't it?
--
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.
To unsubscribe, e-mail: issues-unsubscribe@trafficcontrol.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [trafficcontrol] ocket8888 commented on a change in pull request #6143: Use PostgreSQL 9.6-compatible syntax in create_tables.sql and migrations
Posted by GitBox <gi...@apache.org>.
ocket8888 commented on a change in pull request #6143:
URL: https://github.com/apache/trafficcontrol/pull/6143#discussion_r696988447
##########
File path: traffic_ops/app/db/migrations/2021061100000000_ds_tls_versions.up.sql
##########
@@ -39,20 +39,18 @@ BEGIN
SET last_updated=now()
WHERE id IN (
SELECT deliveryservice
- FROM old_table
+ FROM deliveryservice_tls_version
);
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER update_ds_timestamp_on_tls_version_insertion
AFTER INSERT ON public.deliveryservice_tls_version
- REFERENCING NEW TABLE AS new_table
FOR EACH STATEMENT EXECUTE PROCEDURE update_ds_timestamp_on_insert();
CREATE TRIGGER update_ds_timestamp_on_tls_version_delete
AFTER DELETE ON public.deliveryservice_tls_version
- REFERENCING OLD TABLE AS old_table
Review comment:
I didn't realize you could just use `OLD` and `NEW` - I probably would've just done that in the first place if I had.
Thx
--
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.
To unsubscribe, e-mail: issues-unsubscribe@trafficcontrol.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [trafficcontrol] zrhoffman commented on a change in pull request #6143: Use PostgreSQL 9.6-compatible syntax in create_tables.sql and migrations
Posted by GitBox <gi...@apache.org>.
zrhoffman commented on a change in pull request #6143:
URL: https://github.com/apache/trafficcontrol/pull/6143#discussion_r696986729
##########
File path: traffic_ops/app/db/migrations/2021061100000000_ds_tls_versions.up.sql
##########
@@ -39,20 +39,18 @@ BEGIN
SET last_updated=now()
WHERE id IN (
SELECT deliveryservice
- FROM old_table
+ FROM deliveryservice_tls_version
);
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER update_ds_timestamp_on_tls_version_insertion
AFTER INSERT ON public.deliveryservice_tls_version
- REFERENCING NEW TABLE AS new_table
FOR EACH STATEMENT EXECUTE PROCEDURE update_ds_timestamp_on_insert();
CREATE TRIGGER update_ds_timestamp_on_tls_version_delete
AFTER DELETE ON public.deliveryservice_tls_version
- REFERENCING OLD TABLE AS old_table
Review comment:
Right you are. Changed to `OLD` and `NEW` in the trigger functions in 7e3ced29a0.
--
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.
To unsubscribe, e-mail: issues-unsubscribe@trafficcontrol.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [trafficcontrol] ocket8888 merged pull request #6143: Use PostgreSQL 9.6-compatible syntax in create_tables.sql and migrations
Posted by GitBox <gi...@apache.org>.
ocket8888 merged pull request #6143:
URL: https://github.com/apache/trafficcontrol/pull/6143
--
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.
To unsubscribe, e-mail: issues-unsubscribe@trafficcontrol.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org