Version 1.4.2 fails on pg10

[matijavizintin]

PG version: 10.3 on AWS RDS
pg_repack version: 1.4.2

fixed the repack.c file with: #169

Error log:

> LOG: (query) select repack.version(), repack.version_sql()
> LOG: (query) SET statement_timeout = 0
LOG: (query) SET search_path = pg_catalog, pg_temp, public
LOG: (query) SET client_min_messages = warning
LOG: (query) SELECT t.*, coalesce(v.tablespace, t.tablespace_orig) as tablespace_dest FROM repack.tables t,  (VALUES (quote_ident($1::text))) as v (tablespace) WHERE pkid IS NOT NULL ORDER BY t.relname, t.schemaname
LOG: 	(param:0) = (null)
INFO: repacking table "schema_migrations"
DEBUG: ---- repack_one_table ----
DEBUG: target_name       : schema_migrations
DEBUG: target_oid        : 16616
DEBUG: target_toast      : 0
DEBUG: target_tidx       : 0
DEBUG: pkid              : 16619
DEBUG: ckid              : 0
DEBUG: create_pktype     : CREATE TYPE repack.pk_16616 AS (version integer)
DEBUG: create_log        : CREATE TABLE repack.log_16616 (id bigserial PRIMARY KEY, pk repack.pk_16616, row schema_migrations)
DEBUG: create_trigger    : CREATE TRIGGER repack_trigger AFTER INSERT OR DELETE OR UPDATE ON schema_migrations FOR EACH ROW EXECUTE PROCEDURE repack.repack_trigger('INSERT INTO repack.log_16616(pk, row) VALUES( CASE WHEN $1 IS NULL THEN NULL ELSE (ROW($1.version)::repack.pk_16616) END, $2)')
DEBUG: enable_trigger    : ALTER TABLE schema_migrations ENABLE ALWAYS TRIGGER repack_trigger
DEBUG: create_table      : CREATE TABLE repack.table_16616 WITH (oids = false) TABLESPACE pg_default AS SELECT version FROM ONLY schema_migrations WITH NO DATA
DEBUG: copy_data         : INSERT INTO repack.table_16616 SELECT version FROM ONLY schema_migrations
DEBUG: alter_col_storage : (skipped)
DEBUG: drop_columns      : (skipped)
DEBUG: delete_log        : DELETE FROM repack.log_16616
DEBUG: lock_table        : LOCK TABLE schema_migrations IN ACCESS EXCLUSIVE MODE
DEBUG: sql_peek          : SELECT * FROM repack.log_16616 ORDER BY id LIMIT $1
DEBUG: sql_insert        : INSERT INTO repack.table_16616 VALUES ($1.*)
DEBUG: sql_delete        : DELETE FROM repack.table_16616 WHERE (version) = ($1.version)
DEBUG: sql_update        : UPDATE repack.table_16616 SET (version) = ($2.version) WHERE (version) = ($1.version)
DEBUG: sql_pop           : DELETE FROM repack.log_16616 WHERE id IN (
DEBUG: ---- setup ----
LOG: (query) SELECT pg_try_advisory_lock($1, CAST(-2147483648 + $2::bigint AS integer))
LOG: 	(param:0) = 16185446
LOG: 	(param:1) = 16616
LOG: (query) BEGIN ISOLATION LEVEL READ COMMITTED
LOG: (query) SET LOCAL statement_timeout = 100
LOG: (query) LOCK TABLE schema_migrations IN ACCESS EXCLUSIVE MODE
LOG: (query) RESET statement_timeout
LOG: (query) SELECT pg_get_indexdef(indexrelid) FROM pg_index WHERE indrelid = $1 AND NOT indisvalid
LOG: 	(param:0) = 16616
LOG: (query) SELECT indexrelid, repack.repack_indexdef(indexrelid, indrelid, $2, FALSE)  FROM pg_index WHERE indrelid = $1 AND indisvalid
LOG: 	(param:0) = 16616
LOG: 	(param:1) = (null)
ERROR: query failed: ERROR:  unexpected index definition: CREATE UNIQUE INDEX schema_migrations_pkey ON public.schema_migrations USING btree (version) TABLESPACE pg_default
DETAIL: query was: SELECT indexrelid, repack.repack_indexdef(indexrelid, indrelid, $2, FALSE)  FROM pg_index WHERE indrelid = $1 AND indisvalid
DEBUG: No workers to disconnect.
LOG: (query) SELECT repack.repack_drop($1, $2)
LOG: 	(param:0) = 16616
LOG: 	(param:1) = 0

Unfortunately can't upgrade to 1.4.3 due to aws.

[dvarrazzo]

What does it mean "you can't upgrade due to aws"?

[matijavizintin]

@dvarrazzo aws rds supports only 1.4.2

[dvarrazzo]

Yes, and what can we do about it?

[matijavizintin]

Well, if you know from the log what is wrong i can put a fix in place like is suggested here: #169

After applying the patch suggested by akissa, the version 1.4.2 was working with pg 9.6.1

[dvarrazzo]

This is not a new bug then, it's one already fixed, no need to open another one. If any, open a bug to Amazon and tell them to update.

[hartmut-nex]

https://forums.aws.amazon.com/thread.jspa?threadID=284193

[dvarrazzo]

@hartmut-nex yep, that makes more sense than opening bugs here. Thank you very much :)

[hartmut-nex]

Hi, quick update:
I haven't had a chance to actively test/verify yet - but AWS has upgraded pg_repack to version 1.4.3 along with PostgreSQL 10.4 💪
https://aws.amazon.com/about-aws/whats-new/2018/07/rds-postgresql-supports-minor-versions/

So ideally that would now allow the extension to be used with pg10!