Unexpected behaviour in select clause when using apline image

[janLo]

If you use the docker alpine image, String matches in the where clause seems not to work correctly. This was discovered by debugging a strangely behaving davical calendar server:

davical_default=# select dav_name from caldav_data where dav_name LIKE '/jan/addresses/cc9050c3-3ddf-4382-9d5e-a51218bb2d8b.vcf';                                         dav_name 
----------
(0 rows)

davical_default=# select dav_name from caldav_data where dav_name LIKE '/jan/addresses/cc9050c3-3ddf-4382-9d5e-a51218bb2d8b%vcf';                                                                dav_name                         
---------------------------------------------------------
 /jan/addresses/cc9050c3-3ddf-4382-9d5e-a51218bb2d8b.vcf
(1 row)

davical_default=# select dav_name = '/jan/addresses/cc9050c3-3ddf-4382-9d5e-a51218bb2d8b.vcf' from caldav_data where dav_name LIKE '/jan/addresses/cc9050c3-3ddf-4382-9d5e-a51218bb2d8b%vcf';
 ?column? 
----------
 t
(1 row)

davical_default=# select dav_name from caldav_data where dav_name LIKE '/jan/addresses/y5C35UAONe.vcf';                                        
           dav_name            
-------------------------------
 /jan/addresses/y5C35UAONe.vcf
(1 row)

davical_default=# select dav_name from caldav_data where dav_name LIKE '/jan/addresses/y5C35UAONe%vcf';
           dav_name            
-------------------------------
 /jan/addresses/y5C35UAONe.vcf
(1 row)

davical_default=# select dav_name = '/jan/addresses/y5C35UAONe.vcf' from caldav_data where dav_name LIKE '/jan/addresses/y5C35UAONe%vcf';
 ?column? 
----------
 t
(1 row)

davical_default=# \d caldav_data
                             Table "public.caldav_data"
    Column     |           Type           |                Modifiers                
---------------+--------------------------+-----------------------------------------
 user_no       | integer                  | not null
 dav_name      | text                     | not null
 dav_etag      | text                     | 
 created       | timestamp with time zone | 
 modified      | timestamp with time zone | 
 caldav_data   | text                     | 
 caldav_type   | text                     | 
 logged_user   | integer                  | 
 dav_id        | bigint                   | default nextval('dav_id_seq'::regclass)
 collection_id | bigint                   | 
 weak_etag     | text                     | 
Indexes:
    "caldav_data_pkey" PRIMARY KEY, btree (user_no, dav_name)
    "caldav_data_dav_id_key" UNIQUE CONSTRAINT, btree (dav_id)
    "caldav_data_collection_id_fkey" btree (collection_id)
    "caldav_data_dav_name_idx" btree (dav_name)
Foreign-key constraints:
    "caldav_data_collection_id_fkey" FOREIGN KEY (collection_id) REFERENCES collection(collection_id) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE
    "caldav_data_logged_user_fkey" FOREIGN KEY (logged_user) REFERENCES usr(user_no) ON UPDATE CASCADE ON DELETE SET DEFAULT DEFERRABLE
    "caldav_data_user_no_fkey" FOREIGN KEY (user_no) REFERENCES usr(user_no) ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE
Referenced by:
    TABLE "access_ticket" CONSTRAINT "access_ticket_target_resource_id_fkey" FOREIGN KEY (target_resource_id) REFERENCES caldav_data(dav_id) ON UPDATE CASCADE ON DELETE CASCADE
    TABLE "addressbook_address_adr" CONSTRAINT "addressbook_address_adr_dav_id_fkey" FOREIGN KEY (dav_id) REFERENCES caldav_data(dav_id) ON UPDATE CASCADE ON DELETE CASCADE
    TABLE "addressbook_address_email" CONSTRAINT "addressbook_address_email_dav_id_fkey" FOREIGN KEY (dav_id) REFERENCES caldav_data(dav_id) ON UPDATE CASCADE ON DELETE CASCADE
    TABLE "addressbook_address_tel" CONSTRAINT "addressbook_address_tel_dav_id_fkey" FOREIGN KEY (dav_id) REFERENCES caldav_data(dav_id) ON UPDATE CASCADE ON DELETE CASCADE
    TABLE "addressbook_resource" CONSTRAINT "addressbook_resource_dav_id_fkey" FOREIGN KEY (dav_id) REFERENCES caldav_data(dav_id) ON UPDATE CASCADE ON DELETE CASCADE
    TABLE "calendar_item" CONSTRAINT "caldav_exists" FOREIGN KEY (user_no, dav_name) REFERENCES caldav_data(user_no, dav_name) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE
    TABLE "calendar_alarm" CONSTRAINT "calendar_alarm_dav_id_fkey" FOREIGN KEY (dav_id) REFERENCES caldav_data(dav_id) ON UPDATE CASCADE ON DELETE CASCADE
    TABLE "calendar_attendee" CONSTRAINT "calendar_attendee_dav_id_fkey" FOREIGN KEY (dav_id) REFERENCES caldav_data(dav_id) ON UPDATE CASCADE ON DELETE CASCADE
Triggers:
    caldav_data_modified AFTER INSERT OR DELETE OR UPDATE ON caldav_data FOR EACH ROW EXECUTE PROCEDURE caldav_data_modified()
    caldav_data_sync_dav_id AFTER INSERT OR UPDATE ON caldav_data FOR EACH ROW EXECUTE PROCEDURE sync_dav_id()

Using the "normal" (non-alpine) image fixes the problem.

[tianon]

This sounds similar to #276 😞

I wonder if musl is doing something odd? Can it be reproduced with the Alpine package (if it can be, we'll have to punt to upstream, but otherwise we can look at what they're doing that we aren't 😄)?

[JCavallo]

Had the same problem, noticed it when unique constraints were no longer checked. Switched from the standard "big" image to the lightweight one using the same filesystem for the postgresql folder. Any information I can provide that would help ?

[yosifkit]

Can it be reproduced with the Alpine package (assuming you are using a similar enough version of PostgreSQL as the Alpine package, 9.6.2)? I don't have any data to test the problem.

$ docker run -it --rm -e PGDATA=/var/lib/postgresql/data -v /current-db/:/var/lib/postgresql/data alpine:3.6
$ apk add --no-cache su-exec postgresql
$ mkdir /run/postgresql/
$ chown postgres:postgres /run/postgresql/

$ # not required if you already have a db to test on
$ su-exec postgres:postgres initdb

$ su-exec postgres:postgres postgres &
$ psql -U postgres
$ # tests!

[yosifkit]

Unable to reproduce, closing old issue.