[jira] Created: (DDLUTILS-199) Postgress AutoIncrement fails

Postgress AutoIncrement fails
-----------------------------

                 Key: DDLUTILS-199
                 URL: https://issues.apache.org/jira/browse/DDLUTILS-199
             Project: DdlUtils
          Issue Type: Bug
          Components: Core - PostgreSql
    Affects Versions: 1.0
         Environment: Ubuntu 7.10, PostgreSQL 8.2.6
            Reporter: Rijk van Haaften
            Assignee: Thomas Dudziak
             Fix For: 1.1


class org.apache.ddlutils.platform.postgresql.PostgreSqlBuilder

writeColumnAutoIncrementStmt(Table, Column)
encloses the parameter of nextval in single quotes.
print("UNIQUE DEFAULT nextval('");
...
print("')");
which in my case generates
UNIQUE DEFAULT nextval('Entity_id_seq')

The underscore is a 'special' character, so the string Entity_id_seq needs to be in double quotes. The fix is simple but tricky: the single quotes MUST remain! My local fix (notice the escaped double quote \" twice):

/**
* {@inheritDoc}
*/
protected void writeColumnAutoIncrementStmt(Table table, Column column) throws IOException
{
    print("UNIQUE DEFAULT nextval('\"");
    print(getConstraintName(null, table, column.getName(), "seq"));
    print("\"')");
}


--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

    [ https://issues.apache.org/jira/browse/DDLUTILS-199?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12579886#action_12579886 ]

Rijk van Haaften commented on DDLUTILS-199:
-------------------------------------------

This (of course) also applies to the currval function in getSelectLastIdentityValues():

result.append("currval('\"");
result.append(getConstraintName(null, table, columns[idx].getName(), "seq"));
result.append("\"') AS ");
result.append(getDelimitedIdentifier(columns[idx].getName()));

> Postgress AutoIncrement fails
> -----------------------------
>
>                 Key: DDLUTILS-199
>                 URL: https://issues.apache.org/jira/browse/DDLUTILS-199
>             Project: DdlUtils
>          Issue Type: Bug
>          Components: Core - PostgreSql
>    Affects Versions: 1.0
>         Environment: Ubuntu 7.10, PostgreSQL 8.2.6
>            Reporter: Rijk van Haaften
>            Assignee: Thomas Dudziak
>             Fix For: 1.1
>
>   Original Estimate: 0.17h
>  Remaining Estimate: 0.17h
>
> class org.apache.ddlutils.platform.postgresql.PostgreSqlBuilder
> writeColumnAutoIncrementStmt(Table, Column)
> encloses the parameter of nextval in single quotes.
> print("UNIQUE DEFAULT nextval('");
> ...
> print("')");
> which in my case generates
> UNIQUE DEFAULT nextval('Entity_id_seq')
> The underscore is a 'special' character, so the string Entity_id_seq needs to be in double quotes. The fix is simple but tricky: the single quotes MUST remain! My local fix (notice the escaped double quote \" twice):
> /**
> * {@inheritDoc}
> */
> protected void writeColumnAutoIncrementStmt(Table table, Column column) throws IOException
> {
>     print("UNIQUE DEFAULT nextval('\"");
>     print(getConstraintName(null, table, column.getName(), "seq"));
>     print("\"')");
> }

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

    [ https://issues.apache.org/jira/browse/DDLUTILS-199?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12590815#action_12590815 ]

Thomas Dudziak commented on DDLUTILS-199:
-----------------------------------------

The underscore is not a special character - in fact it is usually the only non alphanumeric character that is allowed in table/column/... names when not in delimited identifier mode.

Could you post the database version, the exact model or even SQL that you have problems with, and the errors that you're getting ?

Could you also try this SQL:

CREATE SEQUENCE roundtrip_avalue_seq;

CREATE TABLE roundtrip
(
    pk INTEGER NOT NULL,
    avalue INTEGER UNIQUE DEFAULT nextval('roundtrip_avalue_seq'),
    PRIMARY KEY (pk)
);

INSERT INTO roundtrip (pk) VALUES (1);

This works fine for me when executed in SQuirrel SQL 2.6.1. against a PostgreSQL 8.2 database and the 8.2-506 JDBC driver.

> Postgress AutoIncrement fails
> -----------------------------
>
>                 Key: DDLUTILS-199
>                 URL: https://issues.apache.org/jira/browse/DDLUTILS-199
>             Project: DdlUtils
>          Issue Type: Bug
>          Components: Core - PostgreSql
>    Affects Versions: 1.0
>         Environment: Ubuntu 7.10, PostgreSQL 8.2.6
>            Reporter: Rijk van Haaften
>            Assignee: Thomas Dudziak
>             Fix For: 1.1
>
>   Original Estimate: 0.17h
>  Remaining Estimate: 0.17h
>
> class org.apache.ddlutils.platform.postgresql.PostgreSqlBuilder
> writeColumnAutoIncrementStmt(Table, Column)
> encloses the parameter of nextval in single quotes.
> print("UNIQUE DEFAULT nextval('");
> ...
> print("')");
> which in my case generates
> UNIQUE DEFAULT nextval('Entity_id_seq')
> The underscore is a 'special' character, so the string Entity_id_seq needs to be in double quotes. The fix is simple but tricky: the single quotes MUST remain! My local fix (notice the escaped double quote \" twice):
> /**
> * {@inheritDoc}
> */
> protected void writeColumnAutoIncrementStmt(Table table, Column column) throws IOException
> {
>     print("UNIQUE DEFAULT nextval('\"");
>     print(getConstraintName(null, table, column.getName(), "seq"));
>     print("\"')");
> }

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

    [ https://issues.apache.org/jira/browse/DDLUTILS-199?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12601591#action_12601591 ]

Rijk van Haaften commented on DDLUTILS-199:
-------------------------------------------

This indeed works fine for me too. I will have to look further into what was going wrong. Maybe case-sensitivity?

> Postgress AutoIncrement fails
> -----------------------------
>
>                 Key: DDLUTILS-199
>                 URL: https://issues.apache.org/jira/browse/DDLUTILS-199
>             Project: DdlUtils
>          Issue Type: Bug
>          Components: Core - PostgreSql
>    Affects Versions: 1.0
>         Environment: Ubuntu 7.10, PostgreSQL 8.2.6
>            Reporter: Rijk van Haaften
>            Assignee: Thomas Dudziak
>             Fix For: 1.1
>
>   Original Estimate: 0.17h
>  Remaining Estimate: 0.17h
>
> class org.apache.ddlutils.platform.postgresql.PostgreSqlBuilder
> writeColumnAutoIncrementStmt(Table, Column)
> encloses the parameter of nextval in single quotes.
> print("UNIQUE DEFAULT nextval('");
> ...
> print("')");
> which in my case generates
> UNIQUE DEFAULT nextval('Entity_id_seq')
> The underscore is a 'special' character, so the string Entity_id_seq needs to be in double quotes. The fix is simple but tricky: the single quotes MUST remain! My local fix (notice the escaped double quote \" twice):
> /**
> * {@inheritDoc}
> */
> protected void writeColumnAutoIncrementStmt(Table table, Column column) throws IOException
> {
>     print("UNIQUE DEFAULT nextval('\"");
>     print(getConstraintName(null, table, column.getName(), "seq"));
>     print("\"')");
> }

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

    [ https://issues.apache.org/jira/browse/DDLUTILS-199?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12601890#action_12601890 ]

Rijk van Haaften commented on DDLUTILS-199:
-------------------------------------------

Without double quotes, case insensitivity seems to work by translating everything to lowercase. My table names are case-sensitive however ('Entity') and thus cannot be found if not using double quotes:

xldoc=# SELECT nextval('Entity_id_seq');
ERROR:  relation "entity_id_seq" does not exist

xldoc=# SELECT nextval('"Entity_id_seq"');
 nextval
---------
       2
(1 row)

> Postgress AutoIncrement fails
> -----------------------------
>
>                 Key: DDLUTILS-199
>                 URL: https://issues.apache.org/jira/browse/DDLUTILS-199
>             Project: DdlUtils
>          Issue Type: Bug
>          Components: Core - PostgreSql
>    Affects Versions: 1.0
>         Environment: Ubuntu 7.10, PostgreSQL 8.2.6
>            Reporter: Rijk van Haaften
>            Assignee: Thomas Dudziak
>             Fix For: 1.1
>
>   Original Estimate: 0.17h
>  Remaining Estimate: 0.17h
>
> class org.apache.ddlutils.platform.postgresql.PostgreSqlBuilder
> writeColumnAutoIncrementStmt(Table, Column)
> encloses the parameter of nextval in single quotes.
> print("UNIQUE DEFAULT nextval('");
> ...
> print("')");
> which in my case generates
> UNIQUE DEFAULT nextval('Entity_id_seq')
> The underscore is a 'special' character, so the string Entity_id_seq needs to be in double quotes. The fix is simple but tricky: the single quotes MUST remain! My local fix (notice the escaped double quote \" twice):
> /**
> * {@inheritDoc}
> */
> protected void writeColumnAutoIncrementStmt(Table table, Column column) throws IOException
> {
>     print("UNIQUE DEFAULT nextval('\"");
>     print(getConstraintName(null, table, column.getName(), "seq"));
>     print("\"')");
> }

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

    [ https://issues.apache.org/jira/browse/DDLUTILS-199?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12601890#action_12601890 ]

cordeo edited comment on DDLUTILS-199 at 6/3/08 6:57 AM:
-------------------------------------------------------------------

Without double quotes, case insensitivity seems to work by translating everything to lowercase. My table names are case-sensitive however ('Entity') and thus cannot be found if not using double quotes:

xldoc=# SELECT nextval('Entity_id_seq');
ERROR:  relation "entity_id_seq" does not exist

xldoc=# SELECT nextval('"Entity_id_seq"');
 nextval
---------
       2
(1 row)



On an empty database (case sensitivity turned on), I get this debug output:

0    [Timer-3] DEBUG org.apache.ddlutils.platform.postgresql.PostgreSqlPlatform - About to execute SQL
-- -----------------------------------------------------------------------
-- Entity
-- -----------------------------------------------------------------------

CREATE SEQUENCE "Entity_id_seq"
10   [Timer-3] DEBUG org.apache.ddlutils.platform.postgresql.PostgreSqlPlatform  - After execution, 0 row(s) have been changed
10   [Timer-3] DEBUG org.apache.ddlutils.platform.postgresql.PostgreSqlPlatform  - About to execute SQL CREATE TABLE "Entity"
(
    "id" BIGINT NOT NULL UNIQUE DEFAULT nextval('Entity_id_seq'),
...
)
failed with: ERROR: relation "entity_id_seq" does not exist

The sequence is created using the case-sensitive "Entity_id_seq", but subsequently accessed using the case-insensitive nextval('Entity_id_seq'). Because insensitivity is implemented by translating everything to lowercase, the sequence is not found. As far as I can see, this is a bug that can be fixed by the proposed code change.


      was (Author: cordeo):
    Without double quotes, case insensitivity seems to work by translating everything to lowercase. My table names are case-sensitive however ('Entity') and thus cannot be found if not using double quotes:

xldoc=# SELECT nextval('Entity_id_seq');
ERROR:  relation "entity_id_seq" does not exist

xldoc=# SELECT nextval('"Entity_id_seq"');
 nextval
---------
       2
(1 row)

 

> Postgress AutoIncrement fails
> -----------------------------
>
>                 Key: DDLUTILS-199
>                 URL: https://issues.apache.org/jira/browse/DDLUTILS-199
>             Project: DdlUtils
>          Issue Type: Bug
>          Components: Core - PostgreSql
>    Affects Versions: 1.0
>         Environment: Ubuntu 7.10, PostgreSQL 8.2.6
>            Reporter: Rijk van Haaften
>            Assignee: Thomas Dudziak
>             Fix For: 1.1
>
>   Original Estimate: 0.17h
>  Remaining Estimate: 0.17h
>
> class org.apache.ddlutils.platform.postgresql.PostgreSqlBuilder
> writeColumnAutoIncrementStmt(Table, Column)
> encloses the parameter of nextval in single quotes.
> print("UNIQUE DEFAULT nextval('");
> ...
> print("')");
> which in my case generates
> UNIQUE DEFAULT nextval('Entity_id_seq')
> The underscore is a 'special' character, so the string Entity_id_seq needs to be in double quotes. The fix is simple but tricky: the single quotes MUST remain! My local fix (notice the escaped double quote \" twice):
> /**
> * {@inheritDoc}
> */
> protected void writeColumnAutoIncrementStmt(Table table, Column column) throws IOException
> {
>     print("UNIQUE DEFAULT nextval('\"");
>     print(getConstraintName(null, table, column.getName(), "seq"));
>     print("\"')");
> }

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

    [ https://issues.apache.org/jira/browse/DDLUTILS-199?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12579886#action_12579886 ]

cordeo edited comment on DDLUTILS-199 at 6/3/08 7:03 AM:
-------------------------------------------------------------------

Even better:
        print("UNIQUE DEFAULT nextval('"); // unchanged
        printIdentifier(getConstraintName(null, table, column.getName(), "seq"));
        print("')"); // unchanged

This (of course) also applies to the currval function in getSelectLastIdentityValues():

result.append("currval('"); // unchanged
result.append(getDelimitedIdentifier(getConstraintName(null, table, columns[idx].getName(), "seq")));
result.append("') AS "); // unchanged
result.append(getDelimitedIdentifier(columns[idx].getName())); // unchanged


      was (Author: cordeo):
    This (of course) also applies to the currval function in getSelectLastIdentityValues():

result.append("currval('\"");
result.append(getConstraintName(null, table, columns[idx].getName(), "seq"));
result.append("\"') AS ");
result.append(getDelimitedIdentifier(columns[idx].getName()));

 

> Postgress AutoIncrement fails
> -----------------------------
>
>                 Key: DDLUTILS-199
>                 URL: https://issues.apache.org/jira/browse/DDLUTILS-199
>             Project: DdlUtils
>          Issue Type: Bug
>          Components: Core - PostgreSql
>    Affects Versions: 1.0
>         Environment: Ubuntu 7.10, PostgreSQL 8.2.6
>            Reporter: Rijk van Haaften
>            Assignee: Thomas Dudziak
>             Fix For: 1.1
>
>   Original Estimate: 0.17h
>  Remaining Estimate: 0.17h
>
> class org.apache.ddlutils.platform.postgresql.PostgreSqlBuilder
> writeColumnAutoIncrementStmt(Table, Column)
> encloses the parameter of nextval in single quotes.
> print("UNIQUE DEFAULT nextval('");
> ...
> print("')");
> which in my case generates
> UNIQUE DEFAULT nextval('Entity_id_seq')
> The underscore is a 'special' character, so the string Entity_id_seq needs to be in double quotes. The fix is simple but tricky: the single quotes MUST remain! My local fix (notice the escaped double quote \" twice):
> /**
> * {@inheritDoc}
> */
> protected void writeColumnAutoIncrementStmt(Table table, Column column) throws IOException
> {
>     print("UNIQUE DEFAULT nextval('\"");
>     print(getConstraintName(null, table, column.getName(), "seq"));
>     print("\"')");
> }

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

    [ https://issues.apache.org/jira/browse/DDLUTILS-199?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12609487#action_12609487 ]

Rijk van Haaften commented on DDLUTILS-199:
-------------------------------------------

Seems to be related to https://issues.apache.org/jira/browse/DDLUTILS-49

> Postgress AutoIncrement fails
> -----------------------------
>
>                 Key: DDLUTILS-199
>                 URL: https://issues.apache.org/jira/browse/DDLUTILS-199
>             Project: DdlUtils
>          Issue Type: Bug
>          Components: Core - PostgreSql
>    Affects Versions: 1.0
>         Environment: Ubuntu 7.10, PostgreSQL 8.2.6
>            Reporter: Rijk van Haaften
>            Assignee: Thomas Dudziak
>             Fix For: 1.1
>
>   Original Estimate: 0.17h
>  Remaining Estimate: 0.17h
>
> class org.apache.ddlutils.platform.postgresql.PostgreSqlBuilder
> writeColumnAutoIncrementStmt(Table, Column)
> encloses the parameter of nextval in single quotes.
> print("UNIQUE DEFAULT nextval('");
> ...
> print("')");
> which in my case generates
> UNIQUE DEFAULT nextval('Entity_id_seq')
> The underscore is a 'special' character, so the string Entity_id_seq needs to be in double quotes. The fix is simple but tricky: the single quotes MUST remain! My local fix (notice the escaped double quote \" twice):
> /**
> * {@inheritDoc}
> */
> protected void writeColumnAutoIncrementStmt(Table table, Column column) throws IOException
> {
>     print("UNIQUE DEFAULT nextval('\"");
>     print(getConstraintName(null, table, column.getName(), "seq"));
>     print("\"')");
> }

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

    [ https://issues.apache.org/jira/browse/DDLUTILS-199?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12609487#action_12609487 ]

cordeo edited comment on DDLUTILS-199 at 9/30/08 6:11 AM:
--------------------------------------------------------------------

Seems to be related to https://issues.apache.org/jira/browse/DDLUTILS-49
But rather single quotes AND double quotes are needed

      was (Author: cordeo):
    Seems to be related to https://issues.apache.org/jira/browse/DDLUTILS-49
 

> Postgress AutoIncrement fails
> -----------------------------
>
>                 Key: DDLUTILS-199
>                 URL: https://issues.apache.org/jira/browse/DDLUTILS-199
>             Project: DdlUtils
>          Issue Type: Bug
>          Components: Core - PostgreSql
>    Affects Versions: 1.0
>         Environment: Ubuntu 7.10, PostgreSQL 8.2.6
>            Reporter: Rijk van Haaften
>            Assignee: Thomas Dudziak
>             Fix For: 1.1
>
>   Original Estimate: 0.17h
>  Remaining Estimate: 0.17h
>
> class org.apache.ddlutils.platform.postgresql.PostgreSqlBuilder
> writeColumnAutoIncrementStmt(Table, Column)
> encloses the parameter of nextval in single quotes.
> print("UNIQUE DEFAULT nextval('");
> ...
> print("')");
> which in my case generates
> UNIQUE DEFAULT nextval('Entity_id_seq')
> The underscore is a 'special' character, so the string Entity_id_seq needs to be in double quotes. The fix is simple but tricky: the single quotes MUST remain! My local fix (notice the escaped double quote \" twice):
> /**
> * {@inheritDoc}
> */
> protected void writeColumnAutoIncrementStmt(Table table, Column column) throws IOException
> {
>     print("UNIQUE DEFAULT nextval('\"");
>     print(getConstraintName(null, table, column.getName(), "seq"));
>     print("\"')");
> }

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.

     [ https://issues.apache.org/jira/browse/DDLUTILS-199?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Thomas Dudziak resolved DDLUTILS-199.
-------------------------------------

    Resolution: Fixed

> Postgress AutoIncrement fails
> -----------------------------
>
>                 Key: DDLUTILS-199
>                 URL: https://issues.apache.org/jira/browse/DDLUTILS-199
>             Project: DdlUtils
>          Issue Type: Bug
>          Components: Core - PostgreSql
>    Affects Versions: 1.0
>         Environment: Ubuntu 7.10, PostgreSQL 8.2.6
>            Reporter: Rijk van Haaften
>            Assignee: Thomas Dudziak
>             Fix For: 1.1
>
>   Original Estimate: 0.17h
>  Remaining Estimate: 0.17h
>
> class org.apache.ddlutils.platform.postgresql.PostgreSqlBuilder
> writeColumnAutoIncrementStmt(Table, Column)
> encloses the parameter of nextval in single quotes.
> print("UNIQUE DEFAULT nextval('");
> ...
> print("')");
> which in my case generates
> UNIQUE DEFAULT nextval('Entity_id_seq')
> The underscore is a 'special' character, so the string Entity_id_seq needs to be in double quotes. The fix is simple but tricky: the single quotes MUST remain! My local fix (notice the escaped double quote \" twice):
> /**
> * {@inheritDoc}
> */
> protected void writeColumnAutoIncrementStmt(Table table, Column column) throws IOException
> {
>     print("UNIQUE DEFAULT nextval('\"");
>     print(getConstraintName(null, table, column.getName(), "seq"));
>     print("\"')");
> }

--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.