CTAS. Replication stops (issuing error infinitely in replication.log) when 'WITH DATA' clause is used and source is not empty

[Pavel Zotov]

CTAS stops replication if 'WITH DATA' is used (and source is not empty).

0) create  directories  and  two databases (master & replica):

=========

    $(dir_sampleDb)/qa_replication/

    $(dir_sampleDb)/qa_replication/db_main.journal/

    $(dir_sampleDb)/qa_replication/db_main.archive/

    $(dir_sampleDb)/qa_replication/db_main.fdb

    $(dir_sampleDb)/qa_replication/db_repl.fdb

=========

(in my case " $(dir_sampleDb)"  =  C:\FB\60SS\EXAMPLES\EMPBUILD\ )

1) connect to master DB and run:
    alter database enable publication;
    alter database include all to publication;

2) prepare replication.conf:

=========

database
{
  verbose_logging = true
}

database = $(dir_sampleDb)/qa_replication/db_main.fdb
{

    journal_directory = "$(dir_sampleDb)/qa_replication/db_main.journal"
    journal_archive_directory = "$(dir_sampleDb)/qa_replication/db_main.archive"
}

database = $(dir_sampleDb)/qa_replication/db_repl.fdb
{
    journal_source_directory = "$(dir_sampleDb)/qa_replication/db_main.archive"
}
=========

(restart FB after this step)

3) add two aliases:

=========

    db_main_alias = $(dir_sampleDb)/qa_replication/db_main.fdb
    db_repl_alias = $(dir_sampleDb)/qa_replication/db_repl.fdb

=========

4) connect to master DB and run script:

=========

create table tbase(id int primary key, f01 int);
insert into tbase(id, f01) select row_number()over(), rand()*100 from rdb$types;
commit;
create table ctas_test as (select * from tbase) WITH DATA;
commit;
delete from ctas_test order by id rows 10;
commit;

=========

After short time replication.log will be fulfilled with:

=========

PZ (replica) Sat Jul 18 02:28:40 2026
     Database: C:\FB\60SS\EXAMPLES\EMPBUILD\QA_REPLICATION\DB_REPL.FDB
     ERROR: Table "PUBLIC"."CTAS_TEST" has no unique key
     At segment 4, offset 48
PZ (replica) Sat Jul 18 02:28:40 2026
     Database: C:\FB\60SS\EXAMPLES\EMPBUILD\QA_REPLICATION\DB_REPL.FDB
     VERBOSE: Disconnecting and suspending

=========

[Vlad Khorsun]

18.07.2026 11:41, Pavel Zotov:

> CTAS stops replication if 'WITH DATA' is used (and source is not empty).

...

> 4) connect to master DB and run script:
> =========
> create table tbase(id int primary key, f01 int);
> insert into tbase(id, f01) select row_number()over(), rand()*100 from rdb$types;
> commit;

> create table ctas_test as (select * from tbase) *WITH DATA*;

> commit;
> delete from ctas_test order by id rows 10;
> commit;
> =========
>
> After short time replication.log will be fulfilled with:
> =========
> PZ (replica) Sat Jul 18 02:28:40 2026
>      Database: C:\FB\60SS\EXAMPLES\EMPBUILD\QA_REPLICATION\DB_REPL.FDB
>      ERROR: Table "PUBLIC"."CTAS_TEST" has no unique key
>      At segment 4, offset 48
> PZ (replica) Sat Jul 18 02:28:40 2026
>      Database: C:\FB\60SS\EXAMPLES\EMPBUILD\QA_REPLICATION\DB_REPL.FDB
>      VERBOSE: Disconnecting and suspending
> =========

This is kind of expected as you create no unique key (index) on source table before
start to issue UPDATE\DELETE with it.

The problem in current CTAS implementation I see - it is not supported ENABLE|DISABLE
PUBLICATION clause.

Regards,
Vlad

[Adriano dos Santos Fernandes]

On 7/18/26 10:09, Vlad Khorsun wrote:
>
>   The problem in current CTAS implementation I see - it is not supported
> ENABLE|DISABLE
> PUBLICATION clause.

Do you think this syntax is good?

CREATE [{GLOBAL | LOCAL} TEMPORARY] TABLE [IF NOT EXISTS] <table name>
[ (<column name> [, <column name> ...]) ]
[SQL SECURITY {DEFINER | INVOKER}]
[{ENABLE | DISABLE} PUBLICATION]
AS { (<query expression>) | <query expression> }
[WITH [NO] DATA]
[ON COMMIT {DELETE | PRESERVE} ROWS]

The place for table_attributes was chosen to avoid grammar conflicts.


Adriano

[Vlad Khorsun]

27.07.2026 14:30, Adriano dos Santos Fernandes:

If I'm not mistaken, this is differs from "regular" CREATE TABLE where
table_attributes goes after table_elements.

I prefer (and implemented in HQ5) an SQL standard way (shortened a bit):

>>>>>

CREATE [ <table scope> ] TABLE <table name> <table contents source>
[ ON COMMIT <table commit action> ROWS ]

<table contents source> ::=
<table element list>
| <as subquery clause>

<as subquery clause> ::=
[ <left paren> <column name list> <right paren> ] AS <table subquery>
<with or without data>

<<<<<

this is more consistent, imho, and creates less shift/reduce conflicts.

In standard syntax optional "<column name list>" is not separated from "AS <table subquery>"
clause - and it looks natural. While in your offer there could be some table attributes
between column names and subquery.


Regards,
Vlad

PS I've already wrote about it in thread "CTAS. Can not use recursive query as source for a table to be created"

[Adriano dos Santos Fernandes]

27.07.2026 14:30, Adriano dos Santos Fernandes:
> On 7/18/26 10:09, Vlad Khorsun wrote:
>>
>>    The problem in current CTAS implementation I see - it is not supported
>> ENABLE|DISABLE
>> PUBLICATION clause.
>
> Do you think this syntax is good?
>
> CREATE [{GLOBAL | LOCAL} TEMPORARY] TABLE [IF NOT EXISTS] <table name>
>    [ (<column name> [, <column name> ...]) ]
>    [SQL SECURITY {DEFINER | INVOKER}]
>    [{ENABLE | DISABLE} PUBLICATION]
>    AS { (<query expression>) | <query expression> }
>    [WITH [NO] DATA]
>    [ON COMMIT {DELETE | PRESERVE} ROWS]
>
> The place for table_attributes was chosen to avoid grammar conflicts.


   If I'm not mistaken, this is differs from "regular" CREATE TABLE where
table_attributes goes after table_elements.

But table elements in CREATE TABLE is kind of equivalent column list of CTAS...

   I prefer (and implemented in HQ5) an SQL standard way (shortened a bit):

 >>>>>

CREATE [ <table scope> ] TABLE <table name> <table contents source>
        [ ON COMMIT <table commit action> ROWS ]

<table contents source> ::=
        <table element list>
        | <as subquery clause>

<as subquery clause> ::=
        [ <left paren> <column name list> <right paren> ] AS <table subquery>
        <with or without data>

<<<<<

You are nit listing thr attributes (replication/ SQL security).

this is more consistent, imho, and creates less shift/reduce conflicts.

My proposal with it in another place is just to avoid 5 shift/reduce conflicts with the current syntax or we would need to rethink on it again.

Adriano

[Vlad Khorsun]

27.07.2026 18:39, Adriano dos Santos Fernandes:

>
>
> 27.07.2026 14:30, Adriano dos Santos Fernandes:
> > On 7/18/26 10:09, Vlad Khorsun wrote:
> >>
> >>    The problem in current CTAS implementation I see - it is not supported
> >> ENABLE|DISABLE
> >> PUBLICATION clause.
> >
> > Do you think this syntax is good?
> >
> > CREATE [{GLOBAL | LOCAL} TEMPORARY] TABLE [IF NOT EXISTS] <table name>
> >    [ (<column name> [, <column name> ...]) ]
> >    [SQL SECURITY {DEFINER | INVOKER}]
> >    [{ENABLE | DISABLE} PUBLICATION]
> >    AS { (<query expression>) | <query expression> }
> >    [WITH [NO] DATA]
> >    [ON COMMIT {DELETE | PRESERVE} ROWS]
> >
> > The place for table_attributes was chosen to avoid grammar conflicts.
>
>
>    If I'm not mistaken, this is differs from "regular" CREATE TABLE where
> table_attributes goes after table_elements.
>
>
> But table elements in CREATE TABLE is kind of equivalent column list of CTAS...

In SQL standard, column list is not separated from query expression. It is a single
entity. And pretty logical for me.

>    I prefer (and implemented in HQ5) an SQL standard way (shortened a bit):
>
>  >>>>>
>
> CREATE [ <table scope> ] TABLE <table name> <table contents source>
>         [ ON COMMIT <table commit action> ROWS ]
>
> <table contents source> ::=
>         <table element list>
>         | <as subquery clause>
>
> <as subquery clause> ::=
>         [ <left paren> <column name list> <right paren> ] AS <table subquery>
>         <with or without data>
>
> <<<<<
>
>
> You are nit listing thr attributes (replication/ SQL security).

Because above I've cited standard. It have no table attributes :)
I believe, our custom table attributes should be placed after all standard clauses when possible.

> this is more consistent, imho, and creates less shift/reduce conflicts.
>
>
> My proposal with it in another place is just to avoid 5 shift/reduce conflicts with the current syntax or we would need to rethink
> on it again.

I'd put logical consistency of syntax at higher priority.
What kind of rethink you means here ?

Regards,
Vlad