FWD » Core Development » Database

11/21/2020

03:19 PM Feature #1587: implement full support for word indexes

Bases on the Ovidiu's example (#1587-60) I've created a test with Postgres database.
* The 4GL table definition is:
... Igor Skornyakov

02:51 PM Feature #1587: implement full support for word indexes

Created @words@ Java UDFs for splitting a string into an array of (different) words. Committed to 1587b rev. 11833 Igor Skornyakov

09:49 AM Feature #4055: optimize temp-table output parameter copying

Eric Faulhaber wrote:
> Revision 10743 is quite old and I'm not sure to what that comment refers.
After #2134, th... Adrian Lungu

11/20/2020

05:22 PM Bug #5013: Collision of java names with SQL keywords

I encountered the following issue.
There is a (database) table named @order@. The conversion correctly identifies ... Ovidiu Maxiniuc

05:16 PM Bug #5013 (Test): Collision of java names with SQL keywords

Ovidiu Maxiniuc

03:59 PM Feature #4397: add database attrs, methods and options

Eric Faulhaber wrote:
> Ovidiu, based on the previous discussion in this task's history, my understanding is that th... Ovidiu Maxiniuc

12:23 PM Feature #4397: add database attrs, methods and options

Some more comments/questions on 4397a:
* Are there any performance implications to the @PropertyDefinition@ change... Eric Faulhaber

10:49 AM Feature #4397: add database attrs, methods and options

Ovidiu, based on the previous discussion in this task's history, my understanding is that the work is functionally co... Eric Faulhaber

01:51 PM Feature #1587: implement full support for word indexes

Committed changes from #1587-67 to 1587b rev.11832. Igor Skornyakov

12:59 PM Feature #1587: implement full support for word indexes

As per Eric's request created a task branch 1587b from 3821c rev. 11831. Igor Skornyakov

12:59 PM Feature #4055: optimize temp-table output parameter copying

Adrian Lungu wrote:
> Never mind, according to trunk/rev. 10743:
> [...]
> I guess this answers the questions arou... Eric Faulhaber

12:45 PM Feature #4055: optimize temp-table output parameter copying

Never mind, according to trunk/rev. 10743:... Adrian Lungu

12:38 PM Feature #4055: optimize temp-table output parameter copying

I might misunderstood. Only persistent tables can be de-normalized through schema hints individually. For temporary t... Adrian Lungu

12:28 PM Feature #4055: optimize temp-table output parameter copying

If I understand correctly, the de-normalization feature in #2134 adds the extent fields (eventually with custom names... Adrian Lungu

09:28 AM Feature #4055: optimize temp-table output parameter copying

Adrian, have you tested to ensure all of your recent changes around extent field copying work with both normalized (d... Eric Faulhaber

08:32 AM Feature #5003: Enhance performance of H2 sequences

The "method representation" has been introduced in 3821c/rev 11824 (@TemporaryBuffer@) and in 3821c/rev 11830 (@P2JH2... Adrian Lungu

08:25 AM Feature #4996: Integrate hash indexes in H2 temp-tables

I tried out changing tree indexed primary keys to hash indexed primary keys (recid). The modification doesn't change ... Adrian Lungu

11/18/2020

12:38 PM Feature #4055: optimize temp-table output parameter copying

Committed as 3821c/rev. 11824. the modifications partially described in #4055-64. This also contains the #5003-2 opti... Adrian Lungu

11/17/2020

06:39 AM Feature #5003: Enhance performance of H2 sequences

The @Sequence.DEFAULT_CACHE_SIZE@ optimization was committed in fwd-h2/rev. 4. On a singular testcase (one million @n... Adrian Lungu

04:25 AM Feature #4055: optimize temp-table output parameter copying

Eric Faulhaber wrote:
> I do think we should be consistent with H2's conventions, however, in case we decide to ask ... Adrian Lungu

11/16/2020

05:20 PM Feature #4055: optimize temp-table output parameter copying

Greg Shah wrote:
> Is this safe to push to our testing environments?
I have not done exhaustive testing, but I ha... Eric Faulhaber

05:18 PM Feature #4055: optimize temp-table output parameter copying

Code review @fwd-h2@ revions 4 & 5:
The changes seem functionally ok to me, though admittedly, I am less familiar ... Eric Faulhaber

05:50 AM Feature #4055: optimize temp-table output parameter copying

Is this safe to push to our testing environments? Greg Shah

03:13 AM Feature #4055: optimize temp-table output parameter copying

I was trying to optimize a basic temp-table copy testcase (simple copy with @INSERT INTO SELECT FROM@). I detected a ... Adrian Lungu

05:33 AM Feature #1587: implement full support for word indexes

Implemented conversion of the logical expression accepted by CONTAINS operator to the @Reverse Polish Notation@ (RPN)... Igor Skornyakov

11/14/2020

06:19 AM Feature #3574 (Review): finish implementation of temp-table XML support

From Ovidiu:
> I am now committing a stable revision (r11811) with all issues in #3574 fixed. Of course there migh... Greg Shah

11/13/2020

04:01 AM Feature #4996: Integrate hash indexes in H2 temp-tables

Not really. H2 prefers a more "general use" index for the primary key - a tree index. This is because it prefers fast... Adrian Lungu

11/12/2020

02:51 PM Feature #4996: Integrate hash indexes in H2 temp-tables

Adrian Lungu wrote:
> As Ovidiu mentioned, for now hash indexes can help in "find-by-rowid" queries, as we can add... Eric Faulhaber

02:24 PM Feature #4996: Integrate hash indexes in H2 temp-tables

Could a view be useful here? I know much depends on how the H2 implements views and how the query optimizer handles ... Greg Shah

12:42 PM Feature #4996: Integrate hash indexes in H2 temp-tables

Adrian Lungu wrote in #4996-1:
> * The hash index works with only one field according to the @NonUniqueHashIndex@ im... Ovidiu Maxiniuc

04:24 AM Feature #4996: Integrate hash indexes in H2 temp-tables

#4996-4 is quite on spot. I may add that H2 allows hash indexes for fields which are not unique, but the queried reco... Adrian Lungu

09:05 PM Feature #4996: Integrate hash indexes in H2 temp-tables

IMHO, we can not benefit much from the hash indexes. The way I understand the H2 documentation (@H2 Performance@ / @I... Ovidiu Maxiniuc

11/11/2020

05:19 PM Feature #4996: Integrate hash indexes in H2 temp-tables

Sorry, I re-read your post and it seems I had misread your conclusion on my first pass. Nevertheless, I expect we wou... Eric Faulhaber

03:01 PM Feature #4996: Integrate hash indexes in H2 temp-tables

Adrian Lungu wrote:
> In H2, if the where clause contains a @_multiplex@ equality, than this index with have a ver... Eric Faulhaber

11/10/2020

07:10 AM Feature #5003: Enhance performance of H2 sequences

There is a @Sequence.DEFAULT_CACHE_SIZE@ which defaults on 32. This refers to how often the sequence is flushed (and ... Adrian Lungu

07:08 AM Feature #5003 (WIP): Enhance performance of H2 sequences

Adrian Lungu

06:49 AM Feature #3574 (WIP): finish implementation of temp-table XML support

From Ovidiu:
> Here are the remaining known issues:
>
> * At this moment I am trying to find a solution for the ... Greg Shah

06:47 AM Feature #4397 (Test): add database attrs, methods and options

Greg Shah

02:48 AM Feature #1587: implement full support for word indexes

Ovidiu Maxiniuc wrote:
> I did a quick research and it looks like the H2 does not have any limitations related to ma... Igor Skornyakov

07:57 PM Feature #1587: implement full support for word indexes

I did a quick research and it looks like the H2 does not have any limitations related to maximum identifier length (t... Ovidiu Maxiniuc

11/07/2020

03:08 AM Feature #1587: implement full support for word indexes

Ovidiu Maxiniuc wrote:
> Are these helper tables specific to a table we already have? You might add a specific suffi... Igor Skornyakov

11/06/2020

04:26 PM Feature #1587: implement full support for word indexes

Are these helper tables specific to a table we already have? You might add a specific suffix, following the normalize... Ovidiu Maxiniuc

03:39 PM Feature #1587: implement full support for word indexes

A strange question. We need to generate unique names for the word tables. I would prefer to have them more or less me... Igor Skornyakov

03:29 PM Feature #1587: implement full support for word indexes

Ovidiu Maxiniuc wrote:
> Igor, here is a how I would tackle this issue.
> First, I would create a simple testcase l... Igor Skornyakov

03:08 PM Feature #1587: implement full support for word indexes

Igor, here is a how I would tackle this issue.
First, I would create a simple testcase like:... Ovidiu Maxiniuc

02:27 PM Feature #1587: implement full support for word indexes

Eric Faulhaber wrote:

> My question was about whether we needed to refactor the conversion of ABL WHERE clauses t... Igor Skornyakov

02:14 PM Feature #1587: implement full support for word indexes

Igor Skornyakov wrote:
> Greg Shah wrote:
> > Perhaps it is better to avoid changes at conversion time and instead ... Eric Faulhaber

01:44 PM Feature #1587: implement full support for word indexes

Greg Shah wrote:
> Perhaps it is better to avoid changes at conversion time and instead to implement the rewriting a... Igor Skornyakov

01:41 PM Feature #1587: implement full support for word indexes

Perhaps it is better to avoid changes at conversion time and instead to implement the rewriting at runtime. This wou... Greg Shah

01:33 PM Feature #1587: implement full support for word indexes

Eric Faulhaber wrote:
> Currently, we convert the ABL WHERE clause to FQL which uses the CONTAINS UDF. Is there any ... Igor Skornyakov

12:55 PM Feature #1587: implement full support for word indexes

Currently, we convert the ABL WHERE clause to FQL which uses the CONTAINS UDF. Is there any reason to refactor this? ... Eric Faulhaber

11:20 AM Feature #1587: implement full support for word indexes

Thank you. I will start with the conversion of the logical expression (in a format required by the @CONTAINS@ operato... Igor Skornyakov

11:02 AM Feature #1587 (WIP): implement full support for word indexes

Considering that you think it will take around a week, please go ahead with your proposed solution, Igor. Thanks. Eric Faulhaber

10:53 AM Feature #1587: implement full support for word indexes

Well, it looks that FTS support in PostgreSQL is flexible enough and it will be possible to use it for @CONTAINS@ sup... Igor Skornyakov

10:37 AM Feature #1587: implement full support for word indexes

Greg Shah wrote:
> What is your estimate of the time needed to implement your proposed solution? If it is small eno... Igor Skornyakov

10:14 AM Feature #1587: implement full support for word indexes

What is your estimate of the time needed to implement your proposed solution? If it is small enough, then it may mak... Greg Shah

04:22 AM Feature #1587: implement full support for word indexes

Eric Faulhaber wrote:
> That being said, before we go this route, I'd like to know if we are ignoring something whic... Igor Skornyakov

12:02 AM Feature #1587: implement full support for word indexes

Greg Shah wrote:
> The specific solution does not matter to me so long as it is compatible and fast. Anything (Igor... Eric Faulhaber

04:31 AM Feature #4996: Integrate hash indexes in H2 temp-tables

The goal of this task is to explore hash index potential uses for the H2 temp-tables. For example, this can boost the... Adrian Lungu

11/05/2020

10:02 AM Feature #1587: implement full support for word indexes

Greg Shah wrote:
> As of this moment, we don't need to implement our own word break tables. We can avoid this featu... Igor Skornyakov

09:53 AM Feature #1587: implement full support for word indexes

I like this Russian saying "shooting a canon at sparrows".
As of this moment, we don't need to implement our own w... Greg Shah

09:44 AM Feature #1587: implement full support for word indexes

Greg Shah wrote:
> > I have no experience with full-text search support in SQL databases, but after a quick look at ... Igor Skornyakov

09:14 AM Feature #1587: implement full support for word indexes

The specific solution does not matter to me so long as it is compatible and fast. Anything (Igor's idea or the built... Greg Shah

09:13 AM Feature #1587: implement full support for word indexes

> I have no experience with full-text search support in SQL databases, but after a quick look at the documentation, I... Greg Shah

08:15 AM Feature #1587: implement full support for word indexes

Greg Shah wrote:
> Some useful references:
>
> * "ElasticSearch":https://en.wikipedia.org/wiki/Elasticsearch - th... Igor Skornyakov

11/04/2020

12:43 PM Feature #4996 (WIP): Integrate hash indexes in H2 temp-tables

Adrian Lungu

09:24 AM Bug #4994: NPE in AdaptiveQuery when loading null data for an outer join

When a @CompoundQuery@ has a component using the OUTER JOIN option, we try to load data via the @AdaptiveQuery.load(O... Eric Faulhaber

09:13 AM Bug #4994 (Closed): NPE in AdaptiveQuery when loading null data for an outer join

Eric Faulhaber

03:53 AM Feature #4055: optimize temp-table output parameter copying

Regarding @optimize extent bulk copy using insert into select from@, I found an optimization which doesn't rely anymo... Adrian Lungu

10/30/2020

01:14 PM Feature #1587: implement full support for word indexes

The @TEMP-TABLE:ADD-NEW-INDEX@ method depends on this, as noted in #4397. When task is finished, the word indexes mus... Ovidiu Maxiniuc

01:05 PM Feature #4397: add database attrs, methods and options

>> TEMP-TABLE:ADD-NEW-INDEX() method
>
> Not touched. (It is currently marked as partial because it doesn't handle ... Greg Shah

11:53 AM Feature #4397: add database attrs, methods and options

Status of issues in 4397a/11776.
> * @BUFFER:LAST-BATCH@ attribute
Done 99%. There is one edge-case where a "look-a... Ovidiu Maxiniuc

07:12 AM Feature #4055: optimize temp-table output parameter copying

Directions for temp-table copy optimization:
* -extend the use of *insert into select from* for some cases of append... Adrian Lungu

07:08 AM Feature #4055: optimize temp-table output parameter copying

*Extent*: When in @insertBulk@ mode, the extents fields are copied one by one. This can be also optimized with a @in... Adrian Lungu

10/29/2020

12:11 PM Feature #4055: optimize temp-table output parameter copying

I will ignore #4055-60 for now, it doesn't fit my understanding of how @copy-temp-table@ is working. I will document ... Adrian Lungu

07:06 AM Feature #4055: optimize temp-table output parameter copying

I am a bit confused of two recently generated testcases. I tried to experiment with @copy-temp-table@ in the context ... Adrian Lungu

05:34 AM Feature #4055: optimize temp-table output parameter copying

Eric Faulhaber wrote:
> Each legacy temp-table index, whether it is unique or not in the legacy schema, is created w... Adrian Lungu

10/28/2020

02:02 PM Feature #4055: optimize temp-table output parameter copying

Insofar as this affects your design decisions, please note that we do not actually create unique indices for temp-tab... Eric Faulhaber

10/27/2020

08:42 AM Feature #4055: optimize temp-table output parameter copying

I have experimented some scenarios both in 4GL and FWD and I wanted to point out some hot-spots and maybe some optimi... Adrian Lungu

10/26/2020

09:46 AM Feature #1587: implement full support for word indexes

Creates task branch 1587a
Igor Skornyakov

10/23/2020

11:22 AM Feature #4980 (New): Investigate the impact of TOAST manipulation in PotgreSQL engine

PostgreSQL has a manager (named TOAST) which ensures that all texts wider than TOAST_TUPLE_THRESHOLD (usually 2kB) ar... Adrian Lungu