CODEWITHSUNDEEP
gotimescaledbpgx
Yura
Yura

Reputation: 1174

Create TimescaleDB hypertable using golang batch query

My golang service needs to create a hypertable dynamically in Timescale DB. I use pgx driver. My code is the following (I removed an error handling):

func (s *Storage) CreateHyperTableBatch(ctx context.Context, tableName string) error {

    indexName := "idx_" + tableName

    conn, _ := s.pool.Acquire(ctx)
    defer conn.Release()

    b := pgx.Batch{}

    b.Queue(fmt.Sprintf(`create table if not exists public.%s (
    ts timestamptz primary key not null,
    data jsonb not null
);
    `, tableName))

    b.Queue(fmt.Sprintf(`create index if not exists public.%s on public.%s using gin (data);`, indexName, tableName))

    b.Queue(fmt.Sprintf(`select create_hypertable('%s', 'ts', if_not_exists => true);`, tableName))

    batchResult := conn.SendBatch(ctx, &b)
    defer func() { _ = batchResult.Close() }()

    _, _ = batchResult.Exec()

    return nil
}

Exec() returns an error

failed to create tableTwo: failed to run query: ERROR: relation "tabletwo" does not exist (SQLSTATE 42P01)

I added schema name to the query (as you see) but this doesn't help. If I split it into three queries everything works fine

func (s *Storage) CreateHyperTable(ctx context.Context, tableName string) error {

    indexName := "idx_" + tableName

    conn, _ := s.pool.Acquire(ctx)
    defer conn.Release()

    _, _ := conn.Exec(ctx, fmt.Sprintf(`create table if not exists %s (
    ts timestamptz primary key not null,
    data jsonb not null
);
`, tableName))

     _, _ := conn.Exec(ctx, fmt.Sprintf(`create index if not exists %s on %s using gin (data);`, indexName, tableName))

    _, _ := conn.Exec(ctx, fmt.Sprintf(`select create_hypertable('%s', 'ts', if_not_exists => true);`, tableName))

    return nil
}

I suppose that the problem is that timescale db needs a plain table to be created and committed to create a hyper table. Is it right or what's the problem? Did anyone encountered the issue and how did you solve it?

Upvotes: 0

Views: 642

Answers (1)

Mike Freedman
Mike Freedman

Reputation: 1912

Can't give a direct answer on your pgx question, but yes, you need to create a table first before trying to create a hypertable. So I'd try to do the former as a separate transaction and see if that succeeeds.

Upvotes: 1

Related Questions