Hi Dremians,
when I issue CREATE TABLE XXX twice from different sessions, first finishes successfully,
but second fails with the following unexpected error:
PLAN ERROR: pdfs:/opt/dremio/data/pdfs/scratch/prdel/b278a139a6a0@0_0_0.parquet is not a Parquet file (too small) SQL Query create table $scratch.test as select * from pg_local_docker.tpch.nation [Error Id: 165c1b01-7074-4f27-9a46-b4c2701e5c92 on b278a139a6a0:310 ...
When I re-execute the second query after the first one finishes, expected error is returned:
VALIDATION ERROR: A table or view with given name ["$scratch".test] already exists. [Error Id: bc1965e6-9c5f-4bd6-90fe-402cd176230c on b278a139a6a0:31010]
I am testing everything on my laptop with Docker edition.
Is it a bug? If yes, should I register it anywhere? If not, is there a way how to determine that such error is always “already exists” like error?
create table $scratch.test as select * from pg_local_docker.tpch.nation
If yes then the second statement failing with the below error is expected
VALIDATION ERROR: A table or view with given name ["$scratch".test] already exists. [Error Id: bc1965e6-9c5f-4bd6-90fe-402cd176230c on b278a139a6a0:31010]
If scratch.test never got created in the first place then it is a bug
The second statement does not fail with “already exists” error, that is what I am reporting.
Let me document the reproducer more clear:
Time T, Session 1
CREATE TABLE $scratch.test executed and is running
Time T+1, Session 2
CREATE TABLE $scratch.test executed
Fails immediately with unexpected error: PLAN ERROR: pdfs:/opt/dremio/data/pdfs/scratch/prdel/b278a139a6a0@0_0_0.parquet is not a Parquet file (too small) SQL Query create table $scratch.test as select * from pg_local_docker.tpch.nation [Error Id: 165c1b01-7074-4f27-9a46-b4c2701e5c92 on b278a139a6a0:310 ...
Time T + 15, Session 1
CREATE TABLE $scratch.test finishes successfully
Time T + 16, Session 2
CREATE TABLE $scratch.test executed
Fails immediately with expected error VALIDATION ERROR: A table or view with given name ["$scratch".test] already exists. [Error Id: bc1965e6-9c5f-4bd6-90fe-402cd176230c on b278a139a6a0:31010]
Hi Balaji,
yes, Time T+1, Session 2, produces the unexpected error.
The reason of creating 2 tables with the same name is the following:
Our distributed component generates SQL queries representing materialization of cache tables, which can be reused by other queries.
It may happen that multiple replicas issue creation of the same cache table.
Here we have two solution options:
Register cache table (sort of lock, e.g. in Redis), do not allow the second creation of the same table
Create table and handle “already exists” error accordingly
The first option is more complex to implement, esp. because the following race condition -
When the first execution fails, the lock in Redis would block another execution forever.
We would have to setup TTL for such records, it would lead to unnecessary waiting.
That is why we are considering the option of handling “already exists” error.
Note: after successful population of a cache table we anyway want to register such event in Redis.
This will significantly reduce number of “already exists” events - once registered, creation of the table is no longer issued.
I am still not able to completely follow your expectation
If a CTAS table exists and you try to create another one we will error says “Table already exists”, any concerns with this?
If the CTAS is still in progress and you create a second one you want to handle this with a lock and saying “Table with same name creation already in progress” and not an error like “Parquet file (too small)”?
CTAS was a feature implemented specifically to save query outputs back to the lake as Parquet to be consumed back in Dremio or by an external tool, sometimes to move data from say Oracle and CTAS to S3. We typically do not see multiple CTAS on the same table