[FR] alter index active must make every effort on unique/primary key index with dup values

[basid-irk]

Sometimes (in unclear cases) dup values may appear in unique/primary key index.
Now alter index active DDL are stop on first problematic record and and show only one report.
alter index active must make every effort on unique/primary key index with dup values and show them all. With dbkey (sub)list for each problematic record, if possible.

[livius2]

Why not running query to find duplicates?
SELECT ID+0 FROM MY_TABLE GROUP BY ID+0 HAVING COUNT(*)>1

[hvlad]

I see no value in such feature. More, it is not clear how to report thousands of duplicates via status-vector and why it is needed at all in general use case.
If one need to know complete list of duplicates, he could query table as @livius2 shows above.

[AlexPeshkoff]

On 8/24/22 13:40, Vlad Khorsun wrote: I see no value in such feature. More, it is not clear how to report thousands of duplicates via status-vector

Since FB4 theoretically possible, but IMHO that (if ever implemented) should go into log file or stdout (like verbose output). There is even no conflict with transfer of backup to client even when gbak runs as service cause it's all @ restore time.

and why it is needed at all in general use case. If one need to know complete list of duplicates, he could query table as @livius2 <https://github.com/livius2> shows above.

Definitely. Useless feature. Just waste of time in gbak after error anyway happened.

[hvlad]

Indices could be built not only by gbak ;)

[basid-irk]

Engine may write info about all dup's in firebird.log and save in status vector only two counter - total problematics records and count of problematics group (dup's itself).

[livius2]

@basid-irk but why do you need such info if you can query database to see it?

[basid-irk]

If possible save full info about all problems - do it. Current case not required excessive CPU/IO load.
If unique index fully built, but definitely contain duplicates - such index still prevent new duplicates and, may be, can be used in queries.

[livius2]

I do suppose that crating invalid index is no go in database world.
And about CPU/IO, index creation is no magic, during creation engine must scan whole data pages for values and sort them. It is "the same" what is done by above query.

[basid-irk]

On fully index (re)build all dups should logged in firebird.log and after should return (only) last error.
Other aspects may retain as currently - only return last (not first) error.