Bug 1079368 - Postgres Plugin - Create New Database Table With Array columns Fails if length is specified
Summary: Postgres Plugin - Create New Database Table With Array columns Fails if lengt...
Status: CLOSED CURRENTRELEASE
Alias: None
Product: JBoss Operations Network
Classification: JBoss
Component: Plugin -- Other
Version: JON 3.2
Hardware: Unspecified
OS: Unspecified
high
high
Target Milestone: ER02
: JON 3.3.0
Assignee: Michael Burman
QA Contact: Mike Foley
URL:
Whiteboard:
Keywords: Triaged
Depends On:
Blocks: 1082792
TreeView+ depends on / blocked
 
Reported: 2014-03-21 13:38 UTC by Matt Mahoney
Modified: 2014-12-11 14:00 UTC (History)
5 users (show)

(edit)
Clone Of:
(edit)
Last Closed: 2014-12-11 14:00:27 UTC


Attachments (Terms of Use)
JON 3.3 DR01 - Server.log (892.05 KB, text/plain)
2014-07-31 21:11 UTC, Matt Mahoney
no flags Details

Description Matt Mahoney 2014-03-21 13:38:59 UTC
Description of problem:
Creating a new database table with columns fails with the following error:
"org.postgresql.util.PSQLException:ERROR: syntax error at or near "("
  Position: 43[SQLException=ERROR: syntax error at or near "("
  Position: 43]"

Version-Release number of selected component (if applicable):
3.2.2

How reproducible:
Always

Steps to Reproduce:

1. Click "Create Child", and select Table
2. Enter "New Resource Name"
3. Click "Next" button
4. Enter "Table Name" "Value"
    Add a Column, click "+":
     Column Name: <column Name>
     Type: char[]
     Length: 100
5. Click "OK", and then click "Finish"
6. Click "Child History", and then double click on new record to evaluate Status and error message.

Actual results:
Creating new tables with column(s) fails.

Expected results:
Creating new tables with columns should not fail.

Additional info:

Comment 2 Michael Burman 2014-07-04 11:18:23 UTC
Adding columns works in current master, this issue is because of used datatype. Arrays have different syntax when length is specified, however PostgreSQL does not use length information at all when creating arrays (as of 9.5 devel), they're all threated as unlimited.

Comment 3 Michael Burman 2014-07-04 13:00:42 UTC
Fixed in master 153804c (plugin will ignore precision and length inputs if type is an array)

Comment 4 Thomas Segismont 2014-07-04 15:30:36 UTC
I think you should:
* check org.rhq.plugins.postgres.PostgresTableComponent#updateResourceConfiguration and detect if the user is trying to modify an array column type or alter the column to make it an array column
* document in the plugin descriptor that this field has no effect when the column is an array

Other than that, the changes look good to me

Comment 5 Michael Burman 2014-07-07 10:09:24 UTC
I think the description is pretty good already:

"if data_type identifies a character or bit string type, the declared maximum length; null for all other data types or if no maximum length was declared"

As I understand it, it already says don't use it with arraytypes. For altering purposes, changing column types to an array is not going to usually work, as PostgreSQL doesn't know how to convert data from original format to an array, unless user creates a function that does not wanted conversion. So this will fail in any case when using the user interface.

However, it should have the same behaviour when adding a new column to existing table.

Comment 6 Thomas Segismont 2014-07-08 09:26:52 UTC
(In reply to Michael Burman from comment #5)
> I think the description is pretty good already:
> 
> "if data_type identifies a character or bit string type, the declared
> maximum length; null for all other data types or if no maximum length was
> declared"

OK.

> 
> As I understand it, it already says don't use it with arraytypes. For
> altering purposes, changing column types to an array is not going to usually
> work, as PostgreSQL doesn't know how to convert data from original format to
> an array, unless user creates a function that does not wanted conversion. So
> this will fail in any case when using the user interface.
> 
> However, it should have the same behaviour when adding a new column to
> existing table.

I think we should no longer allow the column type to be altered. Can you please make it read-only and clean the part of the component code related to that?

Comment 7 Michael Burman 2014-07-10 11:50:03 UTC
Commit 1079368 in master makes the behaviour consistent between update and create. I will create another ticket for removing the column altering.

Comment 8 Michael Burman 2014-07-10 11:50:49 UTC
Copy-paste mistake, 29c49dd is the correct commit.

Comment 9 Heiko W. Rupp 2014-07-31 15:24:18 UTC
Setting to modified as it is in release/jon3.3.x

Comment 10 Simeon Pinder 2014-07-31 15:52:34 UTC
Moving to ON_QA as available to test with brew build of DR01: https://brewweb.devel.redhat.com//buildinfo?buildID=373993

Comment 11 Matt Mahoney 2014-07-31 21:11:05 UTC
Created attachment 923033 [details]
JON 3.3 DR01 - Server.log

Comment 12 Matt Mahoney 2014-07-31 21:12:03 UTC
Failed to verify against JON 3.3 DR01

UI Error details:

java.lang.NullPointerException
	at org.rhq.plugins.postgres.PostgresTableComponent$ColumnDefinition.<init>(PostgresTableComponent.java:412)
	at org.rhq.plugins.postgres.PostgresDatabaseComponent.createResource(PostgresDatabaseComponent.java:219)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:606)
	at org.rhq.core.pc.inventory.ResourceContainer$ComponentInvocation.call(ResourceContainer.java:759)
	at java.util.concurrent.FutureTask.run(FutureTask.java:262)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
	at java.lang.Thread.run(Thread.java:744)

Comment 13 Michael Burman 2014-08-01 08:59:41 UTC
Fixed in master:

commit a6a94893c4136e02b082829e15b87de4f950c3ec
Author: burmanm <miburman@redhat.com>
Date:   Fri Aug 1 11:57:23 2014 +0300

    [BZ 1079368] Fix potential NPE in nullable column

Comment 14 Thomas Segismont 2014-08-25 08:47:25 UTC
Cherry-picked over to release/jon3.3.x

commit 2c87ecbf363c63f1135606507507b3000b085885
Author: burmanm <miburman@redhat.com>
Date:   Fri Aug 1 11:57:23 2014 +0300

    [BZ 1079368] Fix potential NPE in nullable column
    
    (cherry picked from commit a6a94893c4136e02b082829e15b87de4f950c3ec)
    Signed-off-by: Thomas Segismont <tsegismo@redhat.com>

Comment 15 Simeon Pinder 2014-09-03 20:31:55 UTC
Moving to ON_QA as available for test with the following brew build:
https://brewweb.devel.redhat.com//buildinfo?buildID=381194


Note You need to log in before you can comment on or make changes to this bug.