Re: attempting to migrate from postgres to derby

[Peter]

Interesting, thanks!

Derby comes indeed without many surprises and zero administration (at
least for us :)). We run it in production since years and never had a
problem and this durability was the main reason we chose it in the first
place. Still as our requirements are a bit special performance was an
issue (in the beginning) and we had to do a lot of caching, and so the
resulting main workload for derby is relative simple.

(btw: We run postgres for a different workload and have made also good
experience with it and nothing similar to what you reported.)

Regards
Peter

Am 15.11.18 um 22:53 schrieb Alex O'Ree:
> While postgres has been a good database, i've recently ran into a
> number of issues that i haven't been able to resolve and/or understand
> so i'm doing some experiments to see if other vendors have the same
> issue. Having derby be embedded also has advantages as i can remove
> installing postgres as a installation step and remove a lot of
> overhead when working with the database. 
>
> 1) randomly corrupted indexes, this causes all inserts to fail which
> causes data loss until the indexes are either dropped and recreated or
> the "reindex" command is issued.
> 2) the app in question is primarily large amounts of inserts. At
> higher volumes of data ingest, postgres periodically takes long
> pauses. inserts go from single digit ms to 30secs+. I think it's some
> kind of checkpoint or flushing a transaction log, or perhaps it's
> related to auto vacuum. Despite configuration tweaks, i was unable to
> work around it.
>
>
>
> On Thu, Nov 15, 2018 at 4:38 PM Peter  > wrote:
>
> Hello Alex,
>
> May I ask why are you moving to Derby? What are your pain points with
> Postgres?
>
> Kind Regards
> Peter
>
>
> Am 14.11.18 um 22:22 schrieb Alex O'Ree:
> > Greetings. I'm looking for some kind of migration guide and for
> things
> > to watch out for when migration an application to derby.
> >
> > Since i haven't found one yet, i decide to write down and share some
> > of my notes on the things I've ran into so far:
> >
> > DDL - From postgres, there's lots of differences.
> > - Postgres 'text' becomes 'long varchar'
> > - Can't insert from 'text literal' into a blob without some
> quick code
> > and a function to convert it
> > - Postgres gives you the option to select the index type, derby does
> > not appear to. have this function. Not really sure what kind of
> index
> > it is either. btree?
> >
> > JDBC clients
> > - limit and offset has a bit of a strange syntax. most rdbs will
> > access just the literal limit 10 offset 1 syntax. Derby appears to
> > need to wrap this in { }, so select * from table { limit 10
> offset 10}
> > - from a JDBC client, don't include semicolons in your sql code.
> >
> > For the last two, is this "normal"? I have a large code base and
> > refactoring it would be painful. I'm thinking it may be easier
> to hack
> > up the jdbc driver to "fix" the sql statements on the fly. Any
> > thoughts on this? maybe there is some kind of configuration
> setting to
> > make this easier?
>
>

Re: attempting to migrate from postgres to derby

[Alex O'Ree]

While postgres has been a good database, i've recently ran into a number of
issues that i haven't been able to resolve and/or understand so i'm doing
some experiments to see if other vendors have the same issue. Having derby
be embedded also has advantages as i can remove installing postgres as a
installation step and remove a lot of overhead when working with the
database.

1) randomly corrupted indexes, this causes all inserts to fail which causes
data loss until the indexes are either dropped and recreated or the
"reindex" command is issued.
2) the app in question is primarily large amounts of inserts. At higher
volumes of data ingest, postgres periodically takes long pauses. inserts go
from single digit ms to 30secs+. I think it's some kind of checkpoint or
flushing a transaction log, or perhaps it's related to auto vacuum. Despite
configuration tweaks, i was unable to work around it.



On Thu, Nov 15, 2018 at 4:38 PM Peter  wrote:

> Hello Alex,
>
> May I ask why are you moving to Derby? What are your pain points with
> Postgres?
>
> Kind Regards
> Peter
>
>
> Am 14.11.18 um 22:22 schrieb Alex O'Ree:
> > Greetings. I'm looking for some kind of migration guide and for things
> > to watch out for when migration an application to derby.
> >
> > Since i haven't found one yet, i decide to write down and share some
> > of my notes on the things I've ran into so far:
> >
> > DDL - From postgres, there's lots of differences.
> > - Postgres 'text' becomes 'long varchar'
> > - Can't insert from 'text literal' into a blob without some quick code
> > and a function to convert it
> > - Postgres gives you the option to select the index type, derby does
> > not appear to. have this function. Not really sure what kind of index
> > it is either. btree?
> >
> > JDBC clients
> > - limit and offset has a bit of a strange syntax. most rdbs will
> > access just the literal limit 10 offset 1 syntax. Derby appears to
> > need to wrap this in { }, so select * from table { limit 10 offset 10}
> > - from a JDBC client, don't include semicolons in your sql code.
> >
> > For the last two, is this "normal"? I have a large code base and
> > refactoring it would be painful. I'm thinking it may be easier to hack
> > up the jdbc driver to "fix" the sql statements on the fly. Any
> > thoughts on this? maybe there is some kind of configuration setting to
> > make this easier?
>
>
>

Re: attempting to migrate from postgres to derby

[Peter]

Hello Alex,

May I ask why are you moving to Derby? What are your pain points with
Postgres?

Kind Regards
Peter


Am 14.11.18 um 22:22 schrieb Alex O'Ree:
> Greetings. I'm looking for some kind of migration guide and for things
> to watch out for when migration an application to derby.
>
> Since i haven't found one yet, i decide to write down and share some
> of my notes on the things I've ran into so far:
>
> DDL - From postgres, there's lots of differences.
> - Postgres 'text' becomes 'long varchar'
> - Can't insert from 'text literal' into a blob without some quick code
> and a function to convert it
> - Postgres gives you the option to select the index type, derby does
> not appear to. have this function. Not really sure what kind of index
> it is either. btree?
>
> JDBC clients
> - limit and offset has a bit of a strange syntax. most rdbs will
> access just the literal limit 10 offset 1 syntax. Derby appears to
> need to wrap this in { }, so select * from table { limit 10 offset 10}
> - from a JDBC client, don't include semicolons in your sql code.
>
> For the last two, is this "normal"? I have a large code base and
> refactoring it would be painful. I'm thinking it may be easier to hack
> up the jdbc driver to "fix" the sql statements on the fly. Any
> thoughts on this? maybe there is some kind of configuration setting to
> make this easier?

Re: attempting to migrate from postgres to derby

[Rick Hillegas]

LEFT OUTER and RIGHT OUTER joins are supported but not FULL OUTER.

Also, when it comes to migrating your data, you might want to take a 
look at the foreignViews optional tool: 
http://db.apache.org/derby/docs/10.14/tools/rtoolsoptforeignviews.html


Cheers,
-Rick

On 11/15/18 9:18 AM, Alex O'Ree wrote:

Also is "full outer join" statements supported?

On Thu, Nov 15, 2018 at 11:09 AM Alex O'Ree > wrote:


Thanks Rick

I also noticed that the wording and ordering of limit and offset
for select statements is way different in derby.
Postgres style: select * from table limit 3 offset 5
Derby: select * from table offset 5 rows fetch next 3 rows only

Next issue I ran into was that I have tons of insert statements
that read like this (postgres style)
insert into table (column1, column2) values ('asd', 'xyz') on
conflict do nothing;

An insert statement like this is used in a batched prepared
statement. Overall goal is to insert everything and when there is
a primary key collision, just ignore it. In postgres, any failure
will cause the whole batch to abort. Is there a derby equivalent
to this? I did run across this merge jira which may solve the
problem. https://issues.apache.org/jira/browse/DERBY-3155 but is
that the only solution?


On Wed, Nov 14, 2018 at 7:59 PM Rick Hillegas
mailto:rick.hille...@gmail.com>> wrote:

Hi Alex,

Thanks for compiling this list of issues. Some comments inline...

On 11/14/18 1:22 PM, Alex O'Ree wrote:
> Greetings. I'm looking for some kind of migration guide and
for things
> to watch out for when migration an application to derby.
>
> Since i haven't found one yet, i decide to write down and
share some
> of my notes on the things I've ran into so far:
>
> DDL - From postgres, there's lots of differences.
> - Postgres 'text' becomes 'long varchar'
Sounds like LONG VARCHAR wasn't long enough for you and you
needed CLOB
instead.
> - Can't insert from 'text literal' into a blob without some
quick code
> and a function to convert it
BLOB sounds like an odd analog for TEXT. Do you mean CLOB?
> - Postgres gives you the option to select the index type,
derby does
> not appear to. have this function. Not really sure what kind
of index
> it is either. btree?
All Derby indexes are btrees. They can be unique or non-unique.
>
> JDBC clients
> - limit and offset has a bit of a strange syntax. most rdbs
will
> access just the literal limit 10 offset 1 syntax. Derby
appears to
> need to wrap this in { }, so select * from table { limit 10
offset 10}
Derby supports the SQL Standard OFFSET and FETCH clauses. See
http://db.apache.org/derby/docs/10.14/ref/rrefsqljoffsetfetch.html
> - from a JDBC client, don't include semicolons in your sql code.
Again, Derby supports SQL Standard syntax. The semicolons are
not part
of the Standard grammar, although they are used by command line
interpreters (like Derby own ij CLI) to mark the end of
statements. I
agree that rototilling your code to remove non-Standard
semicolons
sounds like a drag.
>
> For the last two, is this "normal"? I have a large code base
and
> refactoring it would be painful. I'm thinking it may be
easier to hack
> up the jdbc driver to "fix" the sql statements on the fly. Any
> thoughts on this? maybe there is some kind of configuration
setting to
> make this easier?
The place to hack this would be in the parsing layer, below
the embedded
JDBC layer. You might also want to take a look at the code for
the ij
tool, which has to deal with semicolons.

Hope this helps,
-Rick

Re: attempting to migrate from postgres to derby

[Alex O'Ree]

Also is "full outer join" statements supported?

On Thu, Nov 15, 2018 at 11:09 AM Alex O'Ree  wrote:

> Thanks Rick
>
> I also noticed that the wording and ordering of limit and offset for
> select statements is way different in derby.
> Postgres style: select * from table limit 3 offset 5
> Derby: select * from table offset 5 rows fetch next 3 rows only
>
> Next issue I ran into was that I have tons of insert statements that read
> like this (postgres style)
> insert into table (column1, column2) values ('asd', 'xyz') on conflict do
> nothing;
>
> An insert statement like this is used in a batched prepared statement.
> Overall goal is to insert everything and when there is a primary key
> collision, just ignore it. In postgres, any failure will cause the whole
> batch to abort. Is there a derby equivalent to this? I did run across this
> merge jira which may solve the problem.
> https://issues.apache.org/jira/browse/DERBY-3155 but is that the only
> solution?
>
>
> On Wed, Nov 14, 2018 at 7:59 PM Rick Hillegas 
> wrote:
>
>> Hi Alex,
>>
>> Thanks for compiling this list of issues. Some comments inline...
>>
>> On 11/14/18 1:22 PM, Alex O'Ree wrote:
>> > Greetings. I'm looking for some kind of migration guide and for things
>> > to watch out for when migration an application to derby.
>> >
>> > Since i haven't found one yet, i decide to write down and share some
>> > of my notes on the things I've ran into so far:
>> >
>> > DDL - From postgres, there's lots of differences.
>> > - Postgres 'text' becomes 'long varchar'
>> Sounds like LONG VARCHAR wasn't long enough for you and you needed CLOB
>> instead.
>> > - Can't insert from 'text literal' into a blob without some quick code
>> > and a function to convert it
>> BLOB sounds like an odd analog for TEXT. Do you mean CLOB?
>> > - Postgres gives you the option to select the index type, derby does
>> > not appear to. have this function. Not really sure what kind of index
>> > it is either. btree?
>> All Derby indexes are btrees. They can be unique or non-unique.
>> >
>> > JDBC clients
>> > - limit and offset has a bit of a strange syntax. most rdbs will
>> > access just the literal limit 10 offset 1 syntax. Derby appears to
>> > need to wrap this in { }, so select * from table { limit 10 offset 10}
>> Derby supports the SQL Standard OFFSET and FETCH clauses. See
>> http://db.apache.org/derby/docs/10.14/ref/rrefsqljoffsetfetch.html
>> > - from a JDBC client, don't include semicolons in your sql code.
>> Again, Derby supports SQL Standard syntax. The semicolons are not part
>> of the Standard grammar, although they are used by command line
>> interpreters (like Derby own ij CLI) to mark the end of statements. I
>> agree that rototilling your code to remove non-Standard semicolons
>> sounds like a drag.
>> >
>> > For the last two, is this "normal"? I have a large code base and
>> > refactoring it would be painful. I'm thinking it may be easier to hack
>> > up the jdbc driver to "fix" the sql statements on the fly. Any
>> > thoughts on this? maybe there is some kind of configuration setting to
>> > make this easier?
>> The place to hack this would be in the parsing layer, below the embedded
>> JDBC layer. You might also want to take a look at the code for the ij
>> tool, which has to deal with semicolons.
>>
>> Hope this helps,
>> -Rick
>>
>>
>>

Re: attempting to migrate from postgres to derby

[Rick Hillegas]

More responses inline...

On 11/15/18 8:09 AM, Alex O'Ree wrote:

Thanks Rick

I also noticed that the wording and ordering of limit and offset for 
select statements is way different in derby.

Postgres style: select * from table limit 3 offset 5
Derby: select * from table offset 5 rows fetch next 3 rows only
Right. Derby uses the SQL Standard syntax. PostgreSQL uses the popular 
limit/offset syntax which never made it into the Standard. The JDBC 
limit/offset escape syntax is supposed to paper over this divergence of 
PostgreSQL and MySQL from the Standard. But it sounds as though you had 
some difficulty getting Derby's JDBC escape syntax to work for you.


Next issue I ran into was that I have tons of insert statements that 
read like this (postgres style)
insert into table (column1, column2) values ('asd', 'xyz') on conflict 
do nothing;


An insert statement like this is used in a batched prepared statement. 
Overall goal is to insert everything and when there is a primary key 
collision, just ignore it. In postgres, any failure will cause the 
whole batch to abort. Is there a derby equivalent to this? I did run 
across this merge jira which may solve the problem. 
https://issues.apache.org/jira/browse/DERBY-3155 but is that the only 
solution?
Right. PostgreSQL and MySQL implemented their own, idiosyncratic UPSERT 
syntax. Derby uses the Standard MERGE syntax for this problem.


MERGE is a complicated statement and Derby's implementation is not 
complete. See 
http://db.apache.org/derby/docs/10.14/ref/rrefsqljmerge.html. 
Unfortunately, Derby does not support VALUES clauses in the USING clause 
of the MERGE statement. The following, admittedly awkward workarounds 
may help you:


o Use a temporary table as the driving relation of the MERGE statement.

o Use a table function as the driving relation of the MERGE statement. 
See http://db.apache.org/derby/docs/10.14/devguide/cdevspecialtabfuncs.html.


Here is a script which shows these workarounds in action:

connect 'jdbc:derby:memory:db;create=true';

-- using a temp table

create table t1(a varchar(10), b varchar(10));

declare global temporary table session.s1 (a varchar(10), b varchar(10)) not 
logged;

autocommit off;

insert into session.s1 values ('abc', 'def');

merge into t1 t

using session.s1 s

on t.a = s.a

when not matched then insert (a, b) values (s.a, s.b)

;

commit;

select * from t1;

-- using a table function

create function twoStringPassthrough(a varchar(32672), b varchar (32672))

returns table (a varchar(32672), b varchar (32672))

language java

parameter style derby_jdbc_result_set

no sql

external name 'TwoStringArgPassthrough.passthrough';

merge into t1 t

using table(twoStringPassthrough('ghi', 'jkl')) s

on t.a = s.a

when not matched then insert (a, b) values (s.a, s.b)

;

commit;

select * from t1;



Here is the source code for the table function:

import java.sql.SQLException;

import org.apache.derby.vti.StringColumnVTI;

public class TwoStringArgPassthrough extends StringColumnVTI

{

  private static final String[] COLUMN_NAMES = new String[] {"A", "B"};

  


  private final String _A;

  private final String _B;

  private boolean _hasMoreRows;

  


  private TwoStringArgPassthrough(String A, String B)

  {

    super(COLUMN_NAMES);

    _A = A;

    _B = B;

    _hasMoreRows = true;

  }

  /** Entry point bound to the table function */

  public static TwoStringArgPassthrough passthrough(String A, String B)

  {

    return new TwoStringArgPassthrough(A, B);

  }

  /** ResultSet overloads */

  public boolean next()

  {

    try

    {

  return _hasMoreRows;

    }

    finally

    {

  _hasMoreRows = false;

    }

  }

  public void close() { _hasMoreRows = false; }

  /** StringColumnVTI implementation */

  protected String getRawColumn(int columnNumber)

    throws SQLException

  {

    switch (columnNumber)

    {

    case 1: return _A;

    case 2: return _B;

    default: throw new SQLException("Unsupported column number: " + 
columnNumber);

    }

  }

}

Hope this helps,
-Rick




On Wed, Nov 14, 2018 at 7:59 PM Rick Hillegas > wrote:


Hi Alex,

Thanks for compiling this list of issues. Some comments inline...

On 11/14/18 1:22 PM, Alex O'Ree wrote:
> Greetings. I'm looking for some kind of migration guide and for
things
> to watch out for when migration an application to derby.
>
> Since i haven't found one yet, i decide to write down and share
some
> of my notes on the things I've ran into so far:
>
> DDL - From postgres, there's lots of differences.
> - Postgres 'text' becomes 'long varchar'
Sounds like LONG VARCHAR wasn't long enough for you and you needed
CLOB
instead.
> - Can't insert from 'text literal' into a blob without some
quick code
> and a function to convert it
BLOB sounds like an odd analog for TEXT. Do you mean CLOB?
> - Postgres gives you the opti

Re: attempting to migrate from postgres to derby

[Alex O'Ree]

Thanks Rick

I also noticed that the wording and ordering of limit and offset for select
statements is way different in derby.
Postgres style: select * from table limit 3 offset 5
Derby: select * from table offset 5 rows fetch next 3 rows only

Next issue I ran into was that I have tons of insert statements that read
like this (postgres style)
insert into table (column1, column2) values ('asd', 'xyz') on conflict do
nothing;

An insert statement like this is used in a batched prepared statement.
Overall goal is to insert everything and when there is a primary key
collision, just ignore it. In postgres, any failure will cause the whole
batch to abort. Is there a derby equivalent to this? I did run across this
merge jira which may solve the problem.
https://issues.apache.org/jira/browse/DERBY-3155 but is that the only
solution?


On Wed, Nov 14, 2018 at 7:59 PM Rick Hillegas 
wrote:

> Hi Alex,
>
> Thanks for compiling this list of issues. Some comments inline...
>
> On 11/14/18 1:22 PM, Alex O'Ree wrote:
> > Greetings. I'm looking for some kind of migration guide and for things
> > to watch out for when migration an application to derby.
> >
> > Since i haven't found one yet, i decide to write down and share some
> > of my notes on the things I've ran into so far:
> >
> > DDL - From postgres, there's lots of differences.
> > - Postgres 'text' becomes 'long varchar'
> Sounds like LONG VARCHAR wasn't long enough for you and you needed CLOB
> instead.
> > - Can't insert from 'text literal' into a blob without some quick code
> > and a function to convert it
> BLOB sounds like an odd analog for TEXT. Do you mean CLOB?
> > - Postgres gives you the option to select the index type, derby does
> > not appear to. have this function. Not really sure what kind of index
> > it is either. btree?
> All Derby indexes are btrees. They can be unique or non-unique.
> >
> > JDBC clients
> > - limit and offset has a bit of a strange syntax. most rdbs will
> > access just the literal limit 10 offset 1 syntax. Derby appears to
> > need to wrap this in { }, so select * from table { limit 10 offset 10}
> Derby supports the SQL Standard OFFSET and FETCH clauses. See
> http://db.apache.org/derby/docs/10.14/ref/rrefsqljoffsetfetch.html
> > - from a JDBC client, don't include semicolons in your sql code.
> Again, Derby supports SQL Standard syntax. The semicolons are not part
> of the Standard grammar, although they are used by command line
> interpreters (like Derby own ij CLI) to mark the end of statements. I
> agree that rototilling your code to remove non-Standard semicolons
> sounds like a drag.
> >
> > For the last two, is this "normal"? I have a large code base and
> > refactoring it would be painful. I'm thinking it may be easier to hack
> > up the jdbc driver to "fix" the sql statements on the fly. Any
> > thoughts on this? maybe there is some kind of configuration setting to
> > make this easier?
> The place to hack this would be in the parsing layer, below the embedded
> JDBC layer. You might also want to take a look at the code for the ij
> tool, which has to deal with semicolons.
>
> Hope this helps,
> -Rick
>
>
>