pg_repack: WARNING: relation must have a primary key or not-null unique keys
Hello everyone!
eugene@dignus:/var/www/html/health$ psql xbox
Timing is on.
psql (12.3 (Ubuntu 12.3-1.pgdg20.04+1))
Type "help" for help.
xbox=> \d herostat
Table "public.herostat"
Column | Type | Collation | Nullable | Default
----------+------------------+-----------+----------+---------
xuid | bigint | | not null |
titleid | bigint | | not null |
value | text | | |
valfloat | double precision | | |
heroid | integer | | not null |
Indexes:
"herostat_pkey" PRIMARY KEY, btree (xuid, titleid, heroid) INCLUDE
(valfloat)
"herostat_heroid_idx" btree (heroid)
"herostat_titleid_idx" btree (titleid)
Triggers:
herostat_min_update BEFORE UPDATE ON herostat FOR EACH ROW EXECUTE
FUNCTION suppress_redundant_updates_trigger()
xbox=> \q
eugene@dignus:/var/www/html/health$ sudo -u postgres pg_repack -t herostat
-N -d xbox
INFO: Dry run enabled, not executing repack
WARNING: relation "public.herostat" must have a primary key or not-null
unique keys
eugene@dignus:/var/www/html/health$ pg_repack --version
pg_repack 1.4.5
eugene@dignus:/var/www/html/health$
What is wrong and what I can do to fix it?
--
Evgeny Pazhitnov
On Tue, Jun 16, 2020, 4:52 AM Eugene Pazhitnov <pazhitnov@gmail.com> wrote:
xbox=> \d herostat
Table "public.herostat"
Indexes:
"herostat_pkey" PRIMARY KEY, btree (xuid, titleid, heroid) INCLUDE
(valfloat)WARNING: relation "public.herostat" must have a primary key or not-null
unique keysWhat is wrong and what I can do to fix it?
I don't know, but I would guess that the "include" option is not supported
by pg_repack.
Show quoted text
Eugene Pazhitnov <pazhitnov@gmail.com> writes:
xbox=> \d herostat
...
"herostat_pkey" PRIMARY KEY, btree (xuid, titleid, heroid) INCLUDE (valfloat)
eugene@dignus:/var/www/html/health$ sudo -u postgres pg_repack -t herostat
-N -d xbox
INFO: Dry run enabled, not executing repack
WARNING: relation "public.herostat" must have a primary key or not-null
unique keys
Apparently pg_repack can't work with a primary key index that has INCLUDE
columns. I have no idea whether that's a fundamental limitation or it's
just that pg_repack hasn't been taught about the INCLUDE feature. In
either case, you need to go consult pg_repack's author(s), who may or
may not keep up on this list.
regards, tom lane
Ok, thanks a lot! Got it.
вт, 16 июн. 2020 г. в 17:12, Tom Lane <tgl@sss.pgh.pa.us>:
Eugene Pazhitnov <pazhitnov@gmail.com> writes:
xbox=> \d herostat
...
"herostat_pkey" PRIMARY KEY, btree (xuid, titleid, heroid) INCLUDE(valfloat)
eugene@dignus:/var/www/html/health$ sudo -u postgres pg_repack -t
herostat
-N -d xbox
INFO: Dry run enabled, not executing repack
WARNING: relation "public.herostat" must have a primary key or not-null
unique keysApparently pg_repack can't work with a primary key index that has INCLUDE
columns. I have no idea whether that's a fundamental limitation or it's
just that pg_repack hasn't been taught about the INCLUDE feature. In
either case, you need to go consult pg_repack's author(s), who may or
may not keep up on this list.regards, tom lane
--
Evgeny Pazhitnov