Registry update to 1.1 on postgres fails

[mlegenhausen]

Installed version:

theory/sqitch/sqitch: stable 0.9996, HEAD
http://sqitch.org/
/usr/local/Cellar/sqitch/0.9996 (243 files, 2.4MB) *
  Built from source on 2017-07-19 at 16:26:16
From: https://github.com/theory/homebrew-sqitch/blob/master/Formula/sqitch.rb
==> Dependencies
Required: sqitch_dependencies ✔
==> Requirements
Required: perl510 ✔

Postgres 9.6 is used.

How to reproduce:

sqitch deploy
sqitch deploy

Error shown in command line:

Upgrading registry local to version 1.1
  * From 1 to 1.1
psql:/usr/local/lib/perl5/App/Sqitch/Engine/Upgrade/pg-1.1.sql:4: ERROR:  constraint "changes_script_hash_key" of relation "changes" does not exist
"psql" unexpectedly returned exit value 3

It seem that sqitch does not correctly detect the current version, cause when I look in sqitch.releases version 1.1 is correctly set.

[theory]

Can you tell me the output of this query?

SELECT MAX(version) FROM releases;

[mlegenhausen]

Result from

SELECT MAX(version) FROM sqitch.releases;

is

+-------+
| max   |
|-------|
| 1.1   |
+-------+

[theory]

Hrm. What happens if you run sqitch upgrade?

[mlegenhausen]

Upgrading registry local to version 1.1
  * From 1 to 1.1
psql:/usr/local/lib/perl5/App/Sqitch/Engine/Upgrade/pg-1.1.sql:4: ERROR:  constraint "changes_script_hash_key" of relation "changes" does not exist
"psql" unexpectedly returned exit value 3

[mlegenhausen]

If I comment out the line that causes the error and run again the following error occurs:

Upgrading registry local to version 1.1
  * From 1 to 1.1
ERROR:  duplicate key value violates unique constraint "releases_pkey"
DETAIL:  Key (version)=(1.1) already exists.

[theory]

Why does it think the version is 1???

Oh, crap, the data type is REAL. :-( Would you try this patch?

--- a/lib/App/Sqitch/Engine/pg.pm
+++ b/lib/App/Sqitch/Engine/pg.pm
@@ -153,6 +153,8 @@ sub _listagg_format {

 sub _regex_op { '~' }

+sub _version_query { 'SELECT MAX(version)::text FROM releases' }
+
 sub initialized {
     my $self = shift;
     return $self->dbh->selectcol_arrayref(q{

[mlegenhausen]

@theory thanks that fixed the problem 😄

[theory]

EEEENteresting!. I'll commit that, thanks.

[theory]

Oh, I'll bet your Perl is compiled with 32-bit floats. That would explain it.

[asaushkin]

I've installed the latest sqitch from CPAN (0.9996) and found this bug when was trying complete the tutorial: https://metacpan.org/pod/sqitchtutorial

The first command sqitch deploy db:pg:flipr_test had completed successfully but next sqitch verify db:pg:flipr_test finished with the same error:

$ sqitch verify db:pg:flipr_test                                                                                 
Registry is at version 1 but latest is 1.1. Please run the "upgrade" conmand

$ sqitch upgrade db:pg:flipr_test                                                                                
Upgrading registry db:pg:flipr_test to version 1.1
  * From 1 to 1.1
psql:/home/ags/perl5/lib/perl5/App/Sqitch/Engine/Upgrade/pg-1.1.sql:4: ERROR:  constraint "changes_script_hash_key" of relation "changes" does not exist
"psql" unexpectedly returned exit value 3

Summary of my perl5 (revision 5 version 18 subversion 2) configuration:

  Platform:
    osname=linux, osvers=3.13.0-79-generic, archname=x86_64-linux-gnu-thread-multi
    uname='linux lgw01-37 3.13.0-79-generic #123-ubuntu smp fri feb 19 14:27:58 utc 2016 x86_64 x86_64 x86_64 gnulinux '

Yes, I've applied the patch from current issue and this solve the problem but sqitch's current state make the tutorial unusable.

[theory]

I'll release a new version in the next week. Don't understand why this just started up; maybe it was this change? @turnstep?

[sanel]

Hi @theory,

This issue got back in 0.9997 for me. Postgres in question is 9.6.0 and perl version is version 22, subversion 2 (v5.22.2) built for x86_64-linux-thread-multi.

_version_query patch is already present.

Any ideas?

[sanel]

Short update: sqitch schema was created by previous deploy runs and sqitch.releases was present. I'm curious, should't script check for this?

pg-1.0.sql could have something like this:

CREATE TABLE IF NOT EXISTS :"registry".releases (

Although this works from postgres 9.1...

[theory]

I'm curious, should't script check for this?

No, if it gets to that point, something is wrong and it should fail.

Would you tell me what output you see with this patch, please?

--- a/lib/App/Sqitch/Engine.pm
+++ b/lib/App/Sqitch/Engine.pm
@@ -1007,6 +1007,7 @@ sub latest_change {
 
 sub needs_upgrade {
     my $self = shift;
+    say $self->registry_version, " != ", $self->registry_release;
     $self->registry_version != $self->registry_release;
 }
 

[sanel]

Now it returns 1.1 != 1.1, but can't reproduce my issue as before.

What I did in the mean time was to drop schema, recreated database and things started to work. Just tried to break things, hoping to reproduce it, but without luck :)

Let's ignore this then and if I get it back, I'll ping you again.

Thanks for prompt response.