MerginMaps / MerginMaps/db-sync

can't init dbsync when table has an index on id column?

Open
#148 3 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Dominant language
Python
Stars
53
Forks
24
PR merge metrics
No merged PRs in 30d

Description

I can't seem to start dbsync , initialised from the database when my table has a btree index on the id column.

```sql
CREATE TABLE my_polygon_table (
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
comment text,
date date,
recorded_by text,
validated boolean,
validated_by text,
geom geometry(Polygon,4326)
);

CREATE INDEX IF NOT EXISTS my_polygon_table_geom_gist
ON my_polygon_table USING gist (geom);

CREATE INDEX IF NOT EXISTS my_polygon_table_id_btree
ON my_polygon_table USING btree (id);
```

I get this error in dbsync :

```
Error: postgres cmd error: ERROR: column "id" specified more than once
|
|
| SQL:
| CREATE TABLE "z_for_mergin_use_only_db_sync"."my_polygon_table" ("id" integer NOT NULL, "id" integer NOT NULL, "comment" text, "date" date, "recorded_by" text, "validated" boolean, "validated_by" text, "geom" geometry(Polygon,4326), PRIMARY KEY ("id"));
```

However if I do the same without creating an index it works fine.

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Reproduce the issue using the SQL schema in the report, with and without the btree index, then trace dbsync's database-initialization path that generates the CREATE TABLE statement. Done means initialization succeeds when the id column has the additional btree index and no duplicate id column appears in the generated SQL.

Written by the indexing model from the issue text.

Assessment

Tech stack
postgresql, python
Domain
backend, databases
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Quiet
Clarity
Mostly clear
Newbie friendliness
52/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.