Data model not showing PostgreSQL tables when they are partitioned

[mjasikowski]

Describe the bug
If a table is partitioned, it's not being visible in the Data Model section of the admin panel and can't be subsequently accessed when creating a question using the "simple question" method.

Example: I've got a table called "order" partitioned by year with a default partition that stores all the data not fitting the partition. In the earlier versions I could just select the "order" table, in the current I only see the partitions (as per screenshot below):

Logs
No errors in console.

To Reproduce
Create a partitioned PostgreSQL table and try accessing it via Admin -> Data Model.

Expected behavior
You should be able to see and select the whole table, not just its partitions.

Screenshots

Data Model (no Order table):

Simple question (no Order table):

Actual table structure in DataGrip (Order table exists):

Information about your Metabase Installation:

{
  "browser-info": {
    "language": "en",
    "platform": "Win32",
    "userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:87.0) Gecko/20100101 Firefox/87.0",
    "vendor": ""
  },
  "system-info": {
    "file.encoding": "UTF-8",
    "java.runtime.name": "OpenJDK Runtime Environment",
    "java.runtime.version": "11.0.10+9",
    "java.vendor": "AdoptOpenJDK",
    "java.vendor.url": "https://adoptopenjdk.net/",
    "java.version": "11.0.10",
    "java.vm.name": "OpenJDK 64-Bit Server VM",
    "java.vm.version": "11.0.10+9",
    "os.name": "Linux",
    "os.version": "4.19.0-14-cloud-amd64",
    "user.language": "en",
    "user.timezone": "GMT"
  },
  "metabase-info": {
    "databases": [
      "postgres"
    ],
    "hosting-env": "unknown",
    "application-database": "postgres",
    "application-database-details": {
      "database": {
        "name": "PostgreSQL",
        "version": "13.0 (Debian 13.0-1.pgdg100+1)"
      },
      "jdbc-driver": {
        "name": "PostgreSQL JDBC Driver",
        "version": "42.2.18"
      }
    },
    "run-mode": "prod",
    "version": {
      "date": "2021-02-19",
      "tag": "v0.38.0.1",
      "branch": "release-x.38.x",
      "hash": "0635914"
    },
    "settings": {
      "report-timezone": null
    }
  }
}

Severity
Blocking some users - the tables can still be accessed using an SQL query, but people not familiar with SQL aren't able to use Metabase.

Additional context
Did work properly in a previous version about two months ago (last time I've checked).

[flamber]

Hi @yasikovsky
It would be very helpful if you knew which version you were using before.
Do you see any errors in the log (Admin > Troubleshooting > Logs), when you force a sync (Admin > Databases > [db] > Sync database schema now) ?
If not, then you’ll probably need to enable debug logging on sync - I’m guessing you’re using Docker - add this environmental variable to the docker run ... command:
… -e JAVA_OPTS="-Dlog4j.configurationFile=https://log4j.us/v2/templates/metabase?trace=metabase.sync" …

[mjasikowski]

hi @flamber, thanks for your reply!

There's no errors in the logs with debug logging enabled - aside of a few warnings: Don't know how to map column type '_uuid' to a Field base_type, falling back to :type/*., but I assume it's unrelated, as other tables do show up correctly and also use UUIDs.

I've tried a few different versions now (thanks to Docker), trying to sync schema every time after switching versions and here are the results:

version	status
0.38.0.1	not working
0.38.0	not working
0.37.9	working
0.37.8	working
0.37.5	working
0.37.1	working
0.37.0.1	working

So it seems this bug was introduced in 0.38.0. All running fine on 0.37.9.

[flamber]

@yasikovsky Can you include the full sync log? It doesn't say anything about the order table in the logs at all?
I'm having difficulties reproducing on 0.38.1 (released yesterday).

[jpbrookes]

same for us, was working in 1.37.7 and 0.37.8 but not in 1.38.1

[bjeanes]

Same for us. We've had to downgrade to 1.37.x

[jeff303]

@jpbrookes , @bjeanes , can you capture the CREATE TABLE statement from the DB for such a problematic table, please?

[bjeanes]

This is on Postgres 11.8

CREATE TABLE public.ahoy_events (
    id bigint NOT NULL,
    visit_id bigint,
    user_id bigint,
    name character varying,
    properties jsonb,
    "time" timestamp without time zone
)
PARTITION BY RANGE ("time");

CREATE SEQUENCE public.ahoy_events_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER SEQUENCE public.ahoy_events_id_seq OWNED BY public.ahoy_events.id;
ALTER TABLE ONLY public.ahoy_events ALTER COLUMN id SET DEFAULT nextval('public.ahoy_events_id_seq'::regclass);

CREATE TABLE public.ahoy_events_20201001 (
    id bigint DEFAULT nextval('public.ahoy_events_id_seq'::regclass) NOT NULL,
    visit_id bigint,
    user_id bigint,
    name character varying,
    properties jsonb,
    "time" timestamp without time zone
);
ALTER TABLE ONLY public.ahoy_events ATTACH PARTITION public.ahoy_events_20201001 FOR VALUES FROM ('2020-10-01 00:00:00') TO ('2020-10-02 00:00:00');

CREATE TABLE public.ahoy_events_20201002 (
    id bigint DEFAULT nextval('public.ahoy_events_id_seq'::regclass) NOT NULL,
    visit_id bigint,
    user_id bigint,
    name character varying,
    properties jsonb,
    "time" timestamp without time zone
);
ALTER TABLE ONLY public.ahoy_events ATTACH PARTITION public.ahoy_events_20201002 FOR VALUES FROM ('2020-10-02 00:00:00') TO ('2020-10-03 00:00:00');

-- etc

[jeff303]

I'm unclear on what change in 0.38 caused this to not work, but I have a fix for it and we will backport to 0.38 in the next bugfix release.

[bjeanes]

Awesome. I take it that means my SQL was enough for you to reproduce then. Thank you!

[jeff303]

@bjeanes , indeed, thanks. Should be fixed in the next 0.38 bugfix release.

[dpsutton]

tldr about what broke: version bump of postgres driver from 42.2.8 -> 42.2.18. In 42.2.11 they added the ability for the driver to distinguish between tables and partitioned tables. As seen in the fix we now ask for "PARTITIONED TABLE" in addition to "TABLE" and others.

[jeff303]

tldr about what broke: version bump of postgres driver from 42.2.8 -> 42.2.18. In 42.2.11 they added the ability for the driver to distinguish between tables and partitioned tables. As seen in the fix we now ask for "PARTITIONED TABLE" in addition to "TABLE" and others.

Dang, nice detective work.