Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

[Postgres] Sysntx error in database Script #22128

Open
aaujayasena opened this issue Jan 1, 2025 · 1 comment
Open

[Postgres] Sysntx error in database Script #22128

aaujayasena opened this issue Jan 1, 2025 · 1 comment
Assignees
Labels
Affected/7.1.0-m6 Issues that get reported against IS7.1.0 M6 pack will get this label DB/postgres postgres DB related Issues will be tracked under this label. Priority/Highest QA-Reported Issues reported by a QA Severity/Blocker Team/Identity Server Core Type/Bug

Comments

@aaujayasena
Copy link
Contributor

Description

PostgreSQL throws a syntax error due to the improper placement of the UNIQUE constraint inside the PRIMARY KEY declaration in IDN_RULE table creation query.

https://github.com/wso2/carbon-identity-framework/blob/master/features/identity-core/org.wso2.carbon.identity.core.server.feature/resources/dbscripts/postgresql.sql#L1674

Steps to Reproduce

Execute the following SQL script on a PostgreSQL database:

DROP TABLE IF EXISTS IDN_RULE;
DROP SEQUENCE IF EXISTS IDN_RULE_SEQ;
CREATE SEQUENCE IDN_RULE_SEQ;
CREATE TABLE IDN_RULE (
    ID INTEGER NOT NULL DEFAULT NEXTVAL('IDN_RULE_SEQ'),
    UUID CHAR(36) NOT NULL,
    CONTENT BYTEA NOT NULL,
    IS_ACTIVE BOOLEAN DEFAULT TRUE,
    TENANT_ID INTEGER NOT NULL,
    VERSION VARCHAR(15) NOT NULL,
    PRIMARY KEY (ID)
    UNIQUE (UUID)
);

Version

IS 7.1.0-M6

Environment Details (with versions)

Postgres 17

@aaujayasena aaujayasena added Severity/Blocker Priority/Highest QA-Reported Issues reported by a QA Affected/7.1.0-m6 Issues that get reported against IS7.1.0 M6 pack will get this label DB/postgres postgres DB related Issues will be tracked under this label. Team/Identity Server Core labels Jan 1, 2025
@aaujayasena
Copy link
Contributor Author

Correct syntax

DROP TABLE IF EXISTS IDN_RULE;
DROP SEQUENCE IF EXISTS IDN_RULE_SEQ;

-- Create sequence
CREATE SEQUENCE IDN_RULE_SEQ;

-- Create table
CREATE TABLE IDN_RULE (
    ID INTEGER NOT NULL DEFAULT NEXTVAL('IDN_RULE_SEQ'),
    UUID CHAR(36) NOT NULL,
    CONTENT BYTEA NOT NULL,
    IS_ACTIVE BOOLEAN DEFAULT TRUE,
    TENANT_ID INTEGER NOT NULL,
    VERSION VARCHAR(15) NOT NULL,
    PRIMARY KEY (ID),              -- Corrected PRIMARY KEY
    UNIQUE (UUID)                  -- Placed UNIQUE constraint properly
);

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Affected/7.1.0-m6 Issues that get reported against IS7.1.0 M6 pack will get this label DB/postgres postgres DB related Issues will be tracked under this label. Priority/Highest QA-Reported Issues reported by a QA Severity/Blocker Team/Identity Server Core Type/Bug
Projects
Status: In Progress
Development

No branches or pull requests

2 participants