FWD » Core Development » Database

11/26/2020

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

Branch 1587b was rebased to 3821c revision 11839.
Committed revision 11844.
Igor Skornyakov

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

I've implemented the fix that doesn't allow re-use of cached but re-written queries. This fixes the issue with the qu... Igor Skornyakov

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

Well, the re-written query parameters are overridden in the @Persistence.getQuery()@. The query is retrieved from the... Igor Skornyakov

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

Ovidiu Maxiniuc wrote:
> At first look the queries look OK to me, nice work!
>
> The queries will try to fetch ro... Igor Skornyakov

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

At first look the queries look OK to me, nice work!
The queries will try to fetch rows incrementally, in increasin... Ovidiu Maxiniuc

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

One more example.
Consider the 4GL program:... Igor Skornyakov

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

In addition to the previous note. Without @CONTAINS@ re-writing the PostgreSQL log looks like the following:... Igor Skornyakov

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

I've finished the @CONTAINS@ UDF call re-writing as per Ovidiu's advice.
Committed to 1587b revision 11835.
However... Igor Skornyakov

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

I committed some changes for signature building and matching to 3821c/rev. 11839:
* *Enhanced unique constraints c... Adrian Lungu

11/25/2020

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

Greg Shah wrote:
> I understand. But we are optimizing performance here and we will spend extra development time an... Igor Skornyakov

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

I understand. But we are optimizing performance here and we will spend extra development time and make conversion le... Greg Shah

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

Greg Shah wrote:
> > Well, since we decided do not to touch conversion at this moment
>
> I think you've already ... Igor Skornyakov

07:01 AM Feature #1587: implement full support for word indexes

> Well, since we decided do not to touch conversion at this moment
I think you've already resolved this. But I wa... Greg Shah

06:21 AM Feature #1587: implement full support for word indexes

Ovidiu Maxiniuc wrote:
> Igor,
> if you work at AST level, you do not need to parse the full SQL query as it is al... Igor Skornyakov

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

Igor,
if you work at AST level, you do not need to parse the full SQL query as it is already represented as a tree.... Ovidiu Maxiniuc

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

Ovidiu Maxiniuc wrote:
> The way @containsQuery@ extracts the @tblName@ does not seem sound to me. The @"__impl"@ mi... Igor Skornyakov

07:14 AM Feature #3958 (WIP): BUFFER-COPY optimization

Adrian Lungu

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

I committed the current implementation of DMO signature to 3821c/rev. 11835, as I reached a stable implementation, wh... Adrian Lungu

11/24/2020

06:17 PM Feature #1587: implement full support for word indexes

The way @containsQuery@ extracts the @tblName@ does not seem sound to me. The @"__impl"@ might not be part of the @tb... Ovidiu Maxiniuc

05:54 PM Feature #1587: implement full support for word indexes

Ovidiu Maxiniuc wrote:
> Indeed, intercepting the @contains@ function with regular expressions is not the best solut... Igor Skornyakov

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

Greg Shah wrote:
> I see. Yes, that is weird. Do you get the same result with @some_other_word_index_field contain... Igor Skornyakov

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

I see. Yes, that is weird. Do you get the same result with @some_other_word_index_field contains if1@? In other wor... Greg Shah

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

Greg Shah wrote:
> This is the documented limitation in the 4GL. @CONTAINS@ can only search word indexes. It canno... Igor Skornyakov

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

> In addition, if I use a field name as the CONTAINS argument, 4GL reports a syntax error, although a little bit stra... Greg Shah

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

Ovidiu Maxiniuc wrote:
> Probably this is possible but I do not think it is justified. If the function node is repla... Igor Skornyakov

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

Igor, I did a quick review of 1587b/11834.
Igor Skornyakov wrote:
> Implemented SQL re-write for @CONTAINS@. Acco... Ovidiu Maxiniuc

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

Eric Faulhaber wrote:
> It may also be helpful to review the specific diffs in:
>
> * rev 10173: this added the i... Igor Skornyakov

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

It may also be helpful to review the specific diffs in:
* rev 10173: this added the initial conversion infrastruct... Eric Faulhaber

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

Eric Faulhaber wrote:
> Igor Skornyakov wrote:
>
> > At this moment I do not understand the details of the @CONTA... Igor Skornyakov

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

Igor Skornyakov wrote:
> At this moment I do not understand the details of the @CONTAINS@ operator conversion. Whe... Eric Faulhaber

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

Implemented SQL re-write for @CONTAINS@. According to Ovidiu, this is done not in the right place, but it should work... Igor Skornyakov

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

Eric Faulhaber wrote:
> Agreed. Note that for temp-tables, the MANDATORY attribute can be dropped from the signature... Adrian Lungu

11/23/2020

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

Adrian Lungu wrote:
> These are the first steps in introducing the DMO structure signature according to #4011-774. I... Eric Faulhaber

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

These are the first steps in introducing the DMO structure signature according to #4011-774. I posted a diff containi... Adrian Lungu

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

Ovidiu Maxiniuc wrote:
> No, it is probably OK, too. My idea was keep FqlToSql converter cleaner (only to do convers... Igor Skornyakov

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

No, it is probably OK, too. My idea was keep FqlToSql converter cleaner (only to do conversion, not enhancements of c... Ovidiu Maxiniuc

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

Ovidiu Maxiniuc wrote:
> (continued, I pressed Submit instead of Preview)
> If this is done at this stage, you real... Igor Skornyakov

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

(continued, I pressed Submit instead of Preview)
If this is done at this stage, you really do not care about the suf... Ovidiu Maxiniuc

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

I think the best moment to intercept the @contain@ function and replace it with a subtree is in @HQLPreprocessor@. At... Ovidiu Maxiniuc

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

From the mails:
Igor:
> BTW: what is the rule for generating the aliases? I would like to avoid parsing the FROM cl... Igor Skornyakov

10:51 AM Bug #5013 (WIP): Collision of java names with SQL keywords

I have fixed the issue by filtering through @NameConverter.resolvePossibleKeywordConflict()@ the result of @P2OAccess... Ovidiu Maxiniuc

11/22/2020

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

Well, the last section of #1587-71 regarding SQL re-writing is wrong, It works only for some simple queries and does ... Igor Skornyakov

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