Add support for PostgreSQL database #1034
Comments
|
It doesn't start because one of migrations fails:
|
|
UPDATE: this error happens only when "test-data" context is active in Liquibase (that means that the |
|
After fixing the previous issue (by declaring NOW property properly), the new issue appear:
|
This change adds 661Kb to the WAR file. Addressed to #1034
The failure was:
liquibase.exception.DatabaseException: ERROR: syntax error at or near "$"
Position: 162 [Failed SQL: INSERT INTO public.categories
(id, name, name_ru, created_at, created_by, updated_at, updated_by)
VALUES ('1', 'Prehistoric animals', 'Доисторические животные', ${NOW}, '2', ${NOW}, '2')]
Addressed to #1034
|
The next error is:
|
|
The next error is:
|
|
One more:
|
|
Yet another:
|
|
The same but for categories:
|
|
Also there is another one in |
|
Now it fails with:
|
… field) instead of YEAR() on PostgreSQL. The failure was: liquibase.exception.DatabaseException: ERROR: function year(date) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts. Position: 35 [Failed SQL: UPDATE series SET release_year=YEAR(released_at) WHERE released_at IS NOT NULL] Addressed to #1034
…table alias in SET clause on PostgreSQL. The failure was: liquibase.exception.DatabaseException: ERROR: column "c" of relation "collections" does not exist Position: 29 [Failed SQL: UPDATE collections c SET c.slug = ( SELECT LOWER(REPLACE(u.login, ' ', '-')) FROM users u WHERE u.id = c.user_id) ] See also: https://stackoverflow.com/questions/11369757/postgres-wont-accept-table-alias-before-column-name Addressed to #1034
…teger on PostgreSQL. The failure was: liquibase.exception.DatabaseException: ERROR: column "image_id" is of type integer but expression is of type text Hint: You will need to rewrite or cast the expression. Position: 75 [Failed SQL: INSERT INTO series_images(series_id, image_id) SELECT id AS series_id, REPLACE(image_url, '/image/', '') AS image_id FROM series] Addressed to #1034
…on PostgreSQL. The failure was: liquibase.exception.DatabaseException: ERROR: syntax error at or near "MODIFY" Position: 22 [Failed SQL: ALTER TABLE users MODIFY COLUMN hash VARCHAR(60) NOT NULL] Addressed to #1034
…clause to fix precondition on PostgreSQL. The failures were like the following: MigrationFailedException: Migration failed for change set classpath:/liquibase/version/0.4/2016-01-14--unique_slug_in_countries.xml::add-unique-constraint-to-slug-in-countries-table::AleksSPb: Reason: classpath:/liquibase/changelog.xml : liquibase.precondition.core.SqlPrecondition@74bdc168 : ERROR: column "num" does not exist Position: 72 ... Caused by: liquibase.exception.DatabaseException: Error executing SQL SELECT COUNT(*) as num FROM countries GROUP BY slug HAVING num > 1 UNION SELECT 0: ERROR: column "num" does not exist Addressed to #1034
… id instead of providing our own. This change should fix 2017-01-06--top_categories.xml migration on PostgreSQL. Prior this commit, we were inserting 'Prehistoric animals' category with explicitly provided id=1 By doing this, we didn't update the sequence generator. When next time we were trying to add another category without id, the sequence generator gave us the value 1 and migration were failing as a category with such id already exist. See for details: https://stackoverflow.com/questions/44744365/liquibase-postgresql-spring-jpa-id-auto-increment-issue There were two ways to fix this: always specify id explicitly and adjust the sequence generator manually after that or never provide an id and let the generator do all the work for us. This change implements the second approach because the first one would be specific to PostgreSQL (as on MySQL we don't need to adjust generators at all). The failure was: MigrationFailedException: Migration failed for change set classpath:/liquibase/version/0.4/2017-01-06--top_categories.xml::add-sport-category::php-coder: Reason: liquibase.exception.DatabaseException: ERROR: duplicate key value violates unique constraint "pk_categories" Detail: Key (id)=(1) already exists. [Failed SQL: INSERT INTO public.categories (name, name_ru, slug, created_at, created_by, updated_at, updated_by) VALUES ('Sport', 'Спорт', 'sport', NOW(), (SELECT id FROM users WHERE role = 'ADMIN' ORDER by id LIMIT 1), NOW(), (SELECT id FROM users WHERE role = 'ADMIN' ORDER by id LIMIT 1))] Addressed to #1034
|
One more:
|
…migration on PostgreSQL. The failure was: MigrationFailedException: Migration failed for change set classpath:/liquibase/version/0.4/2017-05-29--test_image.xml::add-test-image::php-coder: Reason: liquibase.exception.DatabaseException: org.postgresql.util.PSQLException: ERROR: column "image_id" is of type integer but expression is of type character varying Hint: You will need to rewrite or cast the expression. Addressed to #1034
|
More errors, this time related to our usage of JdbcTemplate:
|
…essApiUsageException on PostgreSQL. See: https://stackoverflow.com/questions/17771306/spring-how-to-use-keyholder-with-postgresql The errors were like the following: org.springframework.dao.InvalidDataAccessApiUsageException: The getKey method should only be used when a single key is returned. The current key entry contains multiple keys: [{id=4, name=test, created_at=2019-06-09 21:14:26.889, created_by=2, updated_at=2019-06-09 21:14:26.889, updated_by=2, name_ru=null, slug=test}] Addressed to #1034
php-coder
added
the
impact/changelog
How ro run: $ cd docker $ docker-compose -f docker-compose.yml -f postgres.yml up -d Addressed to #1034 [skip ci]
|
@php-coder the puzzle #1039 is still not solved. |
|
The following sequences have to be fixed:
Here is how to test that it's OK: The Here how it looks when it doesn't work: |
|
Some of the fixes broke change checksums check: |
During adaptation for PostgreSQL, the modifications were made on the existing changesets. It has made them failing on existing data as the checksums were changed. In the new approach, we moved conditions from statements within a changeset to a changeset itself. Correction for the following commits: - c03b2cc - 6d296fd - 7282a7b - d767dab - 11c898b Addressed to #1034
|
@php-coder 2 puzzles #1039, #1054 are still not solved. |
|
All possible duplicate key violations should be fixed now. In order to detect them, I used the following script: $ cd docker
$ docker-compose -f docker-compose.yml -f postgres.yml down -v
$ docker-compose -f docker-compose.yml -f postgres.yml up -d
$ docker-compose -f docker-compose.yml -f postgres.yml logs -f
$ docker cp test-insert.sql mystamps_db_1:/
$ docker exec -it mystamps_db_1 psql -U mystamps -d mystamps -W -f test-insert.sqland here is the content of INSERT INTO categories(name,created_at,created_by,updated_at,updated_by,slug)
VALUES('Test',now(),1,now(),1,'test');
INSERT INTO categories_aliases(category_id,name)
VALUES(1,'Foo');
INSERT INTO collections(user_id,slug,updated_at,updated_by)
VALUES(1,'test',now(),1);
INSERT INTO countries(name,created_at,created_by,updated_at,updated_by,slug)
VALUES('Test',now(),1,now(),1,'test');
INSERT INTO countries_aliases(country_id,name)
VALUES(1,'Bar');
INSERT INTO gibbons_catalog(code)
VALUES('GIB');
INSERT INTO images(type)
VALUES('PNG');
INSERT INTO images_data(content,image_id,preview)
SELECT decode('DEADBEEF', 'hex'), 1, TRUE;
INSERT INTO michel_catalog(code)
VALUES('MIC');
INSERT INTO scott_catalog(code)
VALUES('SCO');
INSERT INTO series(quantity,perforated,created_at,created_by,updated_at,updated_by,category_id)
VALUES(10,TRUE,now(),1,now(),1,1);
INSERT INTO collections_series(collection_id,series_id,number_of_stamps)
VALUES(1,1,10);
INSERT INTO series_gibbons_catalog(series_id,gibbons_id)
VALUES(1,1);
INSERT INTO series_images(series_id,image_id)
VALUES(1,1);
INSERT INTO series_import_requests(url,status_id,requested_at,requested_by,updated_at)
VALUES('http://example.com',1,now(),1,now());
INSERT INTO series_import_parsed_data(request_id,created_at,updated_at)
VALUES(1,now(),now());
INSERT INTO series_import_raw_data(request_id,page_content,created_at,updated_at)
VALUES(1,'<html>TEST</html>',now(),now());
INSERT INTO series_import_request_statuses(name)
VALUES('New status');
INSERT INTO series_michel_catalog(series_id,michel_id)
VALUES(1,1);
INSERT INTO series_sales_import_parsed_data(request_id,created_at,updated_at)
VALUES(1,now(),now());
INSERT INTO series_scott_catalog(series_id,scott_id)
VALUES(1,1);
INSERT INTO solovyov_catalog(code)
VALUES('SOL');
INSERT INTO series_solovyov_catalog(series_id,solovyov_id)
SELECT 1, id FROM solovyov_catalog WHERE code = 'SOL';
INSERT INTO yvert_catalog(code)
VALUES('YVT');
INSERT INTO series_yvert_catalog(series_id,yvert_id)
VALUES(1,1);
INSERT INTO zagorski_catalog(code)
VALUES('ZAG');
INSERT INTO series_zagorski_catalog(series_id,zagorski_id)
SELECT 1, id FROM zagorski_catalog WHERE code = 'ZAG';
INSERT INTO similar_series(series_id,similar_series_id)
VALUES(1,1);
INSERT INTO site_parsers(name)
VALUES('test parser');
INSERT INTO site_parser_params(parser_id,name,value)
VALUES(1,'foo','bar');
INSERT INTO suspicious_activities(page,type_id,ip,occurred_at)
VALUES('/foo',1,'127.0.0.1',now());
INSERT INTO suspicious_activities_types(name)
VALUES('weird');
INSERT INTO top_categories(name,created_at,created_by,updated_at,updated_by)
VALUES('parent',now(),1,now(),1);
INSERT INTO transaction_participant_groups(name,created_at,created_by,updated_at,updated_by)
VALUES('group',now(),1,now(),1);
INSERT INTO transaction_participants(name,is_buyer,is_seller)
VALUES('seller',FALSE,TRUE);
INSERT INTO series_sales(series_id,seller_id,first_price,first_currency,created_at,created_by)
VALUES(1,1,10,'RUB',now(),1);
INSERT INTO users(login,role,name,email,hash,registered_at,activated_at)
VALUES('login','USER','Name','[email protected]','XXX',now(),now());
INSERT INTO users_activation(act_key,email,created_at,lang)
VALUES('1234','[email protected]',now(),'ru'); |
|
The application starts with PostgreSQL on test/prod profiles. The issue can be closed now. |
… to reduce duplication Relate to #1034
README.md/NEWS.txtfilesThe text was updated successfully, but these errors were encountered: