Announcement

**mdarcy** · 20 Apr 2013, 13:44

Hate to bump myself, but any comment on this? I would really like to upgrade our application from 3.0 to 3.1, but this issue is preventing that.

Mike

**Isomorphic** · 20 Apr 2013, 20:42

Please show your datasource

**mdarcy** · 20 Apr 2013, 20:58

Thanks for responding. The datasource is using "autoDeriveSchema". I am pasting below the datasource, table schema as output by Postgres, and associated sequence name as created implicitly by Postgres when the table is created.

Data Source:

Code:

<DataSource 
	schema="beari_application_beari_test_2013_01_15"
	dbName="v3_sandbox"
	tableName="schema_state_history"
	ID="schema_state_history"
	serverType="sql"
    autoDeriveSchema="true"
/>

Table creation schema:

Code:

-- DROP TABLE beari_application_beari_test_2013_01_15.schema_state_history;

CREATE TABLE beari_application_beari_test_2013_01_15.schema_state_history
(
  history_id serial NOT NULL,
  state_code integer NOT NULL,
  update_time timestamp without time zone NOT NULL DEFAULT now(),
  updated_by text,
  comments text,
  CONSTRAINT schema_state_history_pkey PRIMARY KEY (history_id ),
  CONSTRAINT schema_state_history_state_code_fkey FOREIGN KEY (state_code)
      REFERENCES beari_applications.schema_state_codes (state_code) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE beari_application_beari_test_2013_01_15.schema_state_history
  OWNER TO beariuser;
GRANT ALL ON TABLE beari_application_beari_test_2013_01_15.schema_state_history TO beariuser;
GRANT SELECT ON TABLE beari_application_beari_test_2013_01_15.schema_state_history TO beari_application_reader;
GRANT INSERT ON TABLE beari_application_beari_test_2013_01_15.schema_state_history TO beari_history_updater;
GRANT SELECT, UPDATE, INSERT ON TABLE beari_application_beari_test_2013_01_15.schema_state_history TO beari_test;

Sequence creation schema:

Code:

CREATE SEQUENCE beari_application_beari_test_2013_01_15.schema_state_history_history_id_seq
  INCREMENT 0
  MINVALUE 0
  MAXVALUE 0
  START 0
  CACHE 0;
ALTER TABLE beari_application_beari_test_2013_01_15.schema_state_history_history_id_seq
  OWNER TO beariuser;
GRANT ALL ON TABLE beari_application_beari_test_2013_01_15.schema_state_history_history_id_seq TO beariuser;
GRANT UPDATE ON TABLE beari_application_beari_test_2013_01_15.schema_state_history_history_id_seq TO beari_history_updater;

Please let me know if you would like any more information.

Thanks,
Mike

**Isomorphic** · 20 Apr 2013, 21:08

Ok, we'll take a look and update this thread when we have more information.

In the meantime, you can try adding a <fields> block to your DS and specifying just the sequence field, marking it as type sequence and providing the sequenceName along with it

**Isomorphic** · 23 Apr 2013, 03:28

We have made some changes to sequence handling to properly cope with both "traditional" sequence use in Postgres (where you create a sequence object and manually call nextval() on it), and the kind of semi-automatic sequence usage that happens when you use the "serial" column type, which we guess is what you are using here?

The change will be present in today's 4.0d and 3.1p builds (dated 4/23 or later).

**mdarcy** · 23 Apr 2013, 14:57

Thank you very much for looking into this. I downloaded today's 4/23 build of 3.1p Power, but unfortunately I do not see any difference after updating my app to use the new code. The generated insert statement is identical to the previous version (i.e., still using nextval()).

If I read your last post correctly, the intended behavior is that nextval() will not be inserted into the statement when the field type is "serial", but it doesn't seem like it is triggering in my case.

Unfortunately, I cannot use the workaround of adding just the sequence name to a <fields> block because I use a custom data source generator to generate these DS descriptors at runtime by querying a view in our database to discover what tables to load.

So, since I don't know what all the data sources are ahead of time, and I don't know which of those data sources have sequence-based fields in them, it is important that "autoDeriveSchema" figures out the proper course of action for dealing with serial fields.

This is basically all my custom DS generator does:

Code:

        try 
        {
            String xmlString =
		"<DataSource\r" +
		"    ID=\"" + id + "\"\r" +
                "    schema=\"" + schemaName + "\"\r" + 
                "    tableName=\"" + tableName + "\"\r" +
                "    serverType=\"sql\"\r" +
                "    autoDeriveSchema=\"true\"\r" +
                "    progressiveLoading=\"true\"\r" +                  
		"/>\r";
            
            return DataSource.fromXML(xmlString);
        } catch (Exception e) {
            log.error(e);
            return null;
        }

Note that this custom DS generator works fine and I don't suspect it has anything to do with this issue, but I wanted to include the code fragment for posterity.

I will try to update again with tomorrow's build in case somehow the change didn't make it into today's. I can also create a standalone test case for this if you think it will help.

Thanks again and best regards,
Mike

**Isomorphic** · 24 Apr 2013, 00:07

The changes didn't make it into yesterday's nightly because of a change in our build system whereby we build from a snapshot of version control at a known point in time, rather than just the current state of the branch.

Sorry about that - please try today's build.

**mdarcy** · 24 Apr 2013, 14:11

Hi,

Just tried the new version and works fine! Thanks for resolving this.

I noticed that immediately after the INSERT it now does a SELECT for the currval() of the sequence, followed by another SELECT for the record that matches the result of the currval(). This is not a problem for me, but I do not recall it doing that in 3.0p and was curious about the change in behavior.

My application does not need to know this value or select this record after the insert -- I am just presenting all of the table data in a databound ListGrid. Is this behavior something that the ListGrid is causing, or is it in the SQLDataSource code (or some related part). Just curious...

Code:

=== 2013-04-24 14:44:58,694 [c-30] INFO  IDACall - Performing 1 operation(s)
=== 2013-04-24 14:44:58,695 [c-30] INFO  SQLDataSource - [builtinApplication.beviewer_beari_application_beari_test_2013_01_15:schema_state_history_add] Performing add operation with
	criteria: {state_code:1,comments:null,updated_by:"mdarcy"}	values: {state_code:1,comments:null,updated_by:"mdarcy"}
=== 2013-04-24 14:44:58,827 [c-30] INFO  SQLDriver - [builtinApplication.beviewer_beari_application_beari_test_2013_01_15:schema_state_history_add] Executing SQL update on 'v3_sandbox': INSERT INTO beari_application_beari_test_2013_01_15.schema_state_history (comments, state_code, updated_by) VALUES (NULL, 1, 'mdarcy')
=== 2013-04-24 14:44:58,833 [c-30] INFO  SQLDriver - [builtinApplication.beviewer_beari_application_beari_test_2013_01_15:schema_state_history_add] Executing SQL query on 'v3_sandbox': SELECT currval('beari_application_beari_test_2013_01_15.schema_state_history_history_id_seq')
=== 2013-04-24 14:44:58,867 [c-30] INFO  SQLDataSource - [builtinApplication.beviewer_beari_application_beari_test_2013_01_15:schema_state_history_add] primaryKeys: {history_id=108}
=== 2013-04-24 14:44:58,868 [c-30] INFO  SQLDataSource - [builtinApplication.beviewer_beari_application_beari_test_2013_01_15:schema_state_history_add, builtinApplication.null] Performing fetch operation with
	criteria: {history_id:108}	values: {history_id:108}
=== 2013-04-24 14:44:58,868 [c-30] INFO  SQLDataSource - [builtinApplication.beviewer_beari_application_beari_test_2013_01_15:schema_state_history_add, builtinApplication.null] derived query: SELECT $defaultSelectClause FROM $defaultTableClause WHERE $defaultWhereClause
=== 2013-04-24 14:44:58,916 [c-30] INFO  SQLDataSource - [builtinApplication.beviewer_beari_application_beari_test_2013_01_15:schema_state_history_add, builtinApplication.null] Executing SQL query on 'v3_sandbox': SELECT schema_state_history.comments, schema_state_history.history_id, schema_state_history.state_code, schema_state_history.update_time, schema_state_history.updated_by FROM beari_application_beari_test_2013_01_15.schema_state_history WHERE (schema_state_history.history_id=108)
=== 2013-04-24 14:44:58,916 [c-30] INFO  SQLDriver - [builtinApplication.beviewer_beari_application_beari_test_2013_01_15:schema_state_history_add, builtinApplication.null] Executing SQL query on 'v3_sandbox': SELECT schema_state_history.comments, schema_state_history.history_id, schema_state_history.state_code, schema_state_history.update_time, schema_state_history.updated_by FROM beari_application_beari_test_2013_01_15.schema_state_history WHERE (schema_state_history.history_id=108)
=== 2013-04-24 14:44:58,967 [c-30] INFO  DSResponse - [builtinApplication.beviewer_beari_application_beari_test_2013_01_15:schema_state_history_add, builtinApplication.null] DSResponse: List with 1 items

Anyway it is not a big deal for me at this time, and I am very glad that we can now upgrade our app to 3.1p! Thanks again for knocking this out so quick.

Cheers,
Mike

**Isomorphic** · 24 Apr 2013, 14:15

That's a SQLDataSource behavior. We are selecting the record-as-saved from the DB so that it can be returned back to the browser to update caches. The UI will need the generated primary key in order to do subsequent updates against the same record, will also need to get values for any other server-generated columns that are displayed in the UI (eg columns that store the time of the last update).

**mdarcy** · 24 Apr 2013, 14:18

Understood. Thank you for clarifying.

Announcement

Problem with Postgres 9.x and getNextSequenceValue on 3.1p

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment