postgrespro/imgsmlr

Similar images search for PostgreSQL

imgsmlr's Issues

ERROR: function jpeg2pattern(bytea) does not exist

when i excute this sql:

CREATE TABLE pat AS (
SELECT
id,
shuffle_pattern(pattern) AS pattern,
pattern2signature(pattern) AS signature
FROM (
SELECT
id,
jpeg2pattern(data) AS pattern
FROM
image
) x
);

get this error msg:

ERROR: function jpeg2pattern(bytea) does not exist

does anybody know why?thank you.

postgresql restore database, pg_restore: [archiver (db)] COPY failed for table "pat": ERROR: pattern_in is not implemented

bash-4.1$ pg_restore -d test /home/backup/test4.sql.gz 
pg_restore: [archiver (db)] Error while PROCESSING TOC:

pg_restore: [archiver (db)] Error from TOC entry 2408; 0 666136 TABLE DATA pat postgres
pg_restore: [archiver (db)] COPY failed for table "pat": ERROR:  pattern_in is not implemented
CONTEXT:  COPY pat, line 1, column ptn: "((0.244171, 0.008272, 0.021515, -0.012174, 0.068765, 0.076409, 0.016820, 0.021921, 0.114738, 0.12366..."
pg_restore: [archiver (db)] Error from TOC entry 2445; 0 4877471 TABLE DATA pat_303816 postgres
pg_restore: [archiver (db)] COPY failed for table "pat_303816": ERROR:  pattern_in is not implemented
CONTEXT:  COPY pat_303816, line 1, column ptn: "((0.446228, -0.151998, -0.054444, -0.095391, 0.027121, 0.028324, 0.017388, 0.133117, 0.008461, 0.012..."
pg_restore: [archiver (db)] Error from TOC entry 2441; 0 4800536 TABLE DATA pat_402653 postgres
pg_restore: [archiver (db)] COPY failed for table "pat_402653": ERROR:  pattern_in is not implemented
CONTEXT:  COPY pat_402653, line 1, column ptn: "((0.505515, 0.040819, 0.057495, 0.044649, 0.065036, 0.021012, 0.006341, 0.045162, 0.061616, 0.085675..."
pg_restore: [archiver (db)] Error from TOC entry 2449; 0 4878143 TABLE DATA pat_452238 postgres
pg_restore: [archiver (db)] COPY failed for table "pat_452238": ERROR:  pattern_in is not implemented
CONTEXT:  COPY pat_452238, line 1, column ptn: "((0.124739, -0.015536, 0.045421, 0.013990, 0.000782, 0.013343, 0.018824, 0.002218, 0.009941, 0.01699..."
WARNING: errors ignored on restore: 4

jpeg2pattern return?

SQL: SELECT
id,
jpeg2pattern(image_data) AS pattern
FROM images where id = 11111;

but pattern return null？some image is no support?
the image URL: https://cbu01.alicdn.com/img/ibank/2016/724/363/2764363427_8489247.jpg

Hello, ask a question to install imgsmlr

[postgres@vtiger-prod-mysql1 imgsmlr]$ make USE_PGXS=1 installcheck
/usr/local/pgsql/lib/pgxs/src/makefiles/../../src/test/regress/pg_regress --inputdir=./ --psqldir='/usr/local/pgsql/bin' --dbname=contrib_regression imgsmlr
(using postmaster on Unix socket, default port)
============== dropping database "contrib_regression" ==============
DROP DATABASE
============== creating database "contrib_regression" ==============
CREATE DATABASE
ALTER DATABASE
============== running regression test queries ==============
test imgsmlr ... FAILED

======================
1 of 1 tests failed.

The differences that caused some tests to fail can be viewed in the
file "/usr/local/src/imgsmlr/regression.diffs". A copy of the test summary that you see
above is saved in the file "/usr/local/src/imgsmlr/regression.out".

[Issue] Why GiST index on signature not working

hi Akorotkov,

I followed the guide for https://github.com/postgrespro/imgsmlr and it works well, just one issue: when I explain the query, it shows Seq Scan instead of Index Scan, meaning it's not using the GiST index to query the data.

postgres=# explain select t1.sig <-> t2.sig from train2_pat_perf2 t1 join train2_pat_perf2 t2 on t1.id <> t2.id and t1.sig <-> t2.sig <0.5 order by t1.sig <-> t2.sig desc;
                                         QUERY PLAN
---------------------------------------------------------------------------------------------
 Sort  (cost=40289112.51..40547084.53 rows=103188810 width=128)
   Sort Key: ((t1.sig <-> t2.sig)) DESC
   ->  Nested Loop  (cost=0.00..6450626.41 rows=103188810 width=128)
         Join Filter: ((t1.id <> t2.id) AND ((t1.sig <-> t2.sig) < '0.5'::double precision))
         ->  Seq Scan on train2_pat_perf2 t1  (cost=0.00..464.95 rows=17595 width=72)
         ->  Materialize  (cost=0.00..552.93 rows=17595 width=72)
               ->  Seq Scan on train2_pat_perf2 t2  (cost=0.00..464.95 rows=17595 width=72)
(7 rows)

Would you help me? Thanks!

Best regards!
Jason

why installcheck error?

[postgres@localhost imgsmlr]$ **make USE_PGXS=1 installcheck**
/usr/pgsql-9.6/lib/pgxs/src/makefiles/../../src/test/regress/pg_regress --inputdir=./ --bindir='/usr/pgsql-9.6/bin' --dbname=contrib_regression imgsmlr

(using postmaster on Unix socket, default port)

============== dropping database "contrib_regression" ==============

DROP DATABASE

============== creating database "contrib_regression" ==============

CREATE DATABASE

ALTER DATABASE

============== running regression test queries ==============

test imgsmlr ... FAILED

======================

1 of 1 tests failed.

======================

The differences that caused some tests to fail can be viewed in the file "/soft/imgsmlr/regression.diffs". A copy of the test summary that you see

above is saved in the file "/soft/imgsmlr/regression.out".

---

------------------------------------parts of regression.diffs-----------------------------------------------
*** 59,175 ****

id | id | round

----+----+--------

1 | 1 | 0.0000

! 1 | 2 | 1.5429

! 1 | 3 | 1.6181

! 1 | 4 | 2.8538

! 1 | 5 | 3.1795

! 1 | 6 | 2.7229

! 1 | 7 | 3.7688

! 1 | 8 | 3.8711

! 1 | 9 | 3.8873

! 1 | 10 | 4.7480

! 1 | 11 | 4.3794

! 1 | 12 | 3.5524

! 2 | 1 | 1.5429

2 | 2 | 0.0000

2 | 3 | 1.2565

2 | 4 | 2.7438

2 | 5 | 2.8960

Compile error

python api

do you have python api

installcheck failed

Hello Alexander,

I have a problem with installcheck. It ends up with something like that :

============== running regression test queries        ==============
test imgsmlr                  ... FAILED

======================
 1 of 1 tests failed. 
======================

The differences that caused some tests to fail can be viewed in the
file "/home/phi001/nobackup/tmp/imgsmlr/regression.diffs".  A copy of the test summary that you see
above is saved in the file "/home/phi001/nobackup/tmp/imgsmlr/regression.out".

/home/phi001/install/postgresql-9.6.6/lib/pgxs/src/makefiles/pgxs.mk:272 : la recette pour la cible « installcheck » a échouée

I use the following attached script for installing/testing your imgsmlr extension :

imgsmlr.sh.zip

I have tried to select the 7ea9b21 commit you indicated in another message, but without success.

What should I do to in order to get imgsmlr working ?

Спасибо за помошь !

Philippe

How to Convert Image to Hex ?

Yeah i have problem with it

error: ‘FALSE’ undeclared (first use in this function)

pg 11 make error

root@pg11-test-> USE_PGXS=1 make
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O3 -fPIC -I. -I./ -I/usr/pgsql-11/include/server -I/usr/pgsql-11/include/internal  -D_GNU_SOURCE   -c -o imgsmlr_idx.o imgsmlr_idx.c
imgsmlr_idx.c:26:1: error: unknown type name ‘typedefenum’
 typedefenum __bool {false = 0,true =1, }bool;
 ^
imgsmlr_idx.c:26:20: error: expected ‘=’, ‘,’, ‘;’, ‘asm’ or ‘__attribute__’ before ‘{’ token
 typedefenum __bool {false = 0,true =1, }bool;
                    ^
imgsmlr_idx.c:26:20: warning: useless type name in empty declaration [enabled by default]
In file included from imgsmlr_idx.c:18:0:
imgsmlr_idx.c: In function ‘signature_compress’:
imgsmlr_idx.c:69:23: error: ‘FALSE’ undeclared (first use in this function)
        entry->offset, FALSE);
                       ^
/usr/pgsql-11/include/server/access/gist.h:171:37: note: in definition of macro ‘gistentryinit’
    (e).offset = (o); (e).leafkey = (l); } while (0)
                                     ^
imgsmlr_idx.c:69:23: note: each undeclared identifier is reported only once for each function it appears in
        entry->offset, FALSE);
                       ^
/usr/pgsql-11/include/server/access/gist.h:171:37: note: in definition of macro ‘gistentryinit’
    (e).offset = (o); (e).leafkey = (l); } while (0)
                                     ^
imgsmlr_idx.c: In function ‘signature_decompress’:
imgsmlr_idx.c:91:23: error: ‘FALSE’ undeclared (first use in this function)
        entry->offset, FALSE);
                       ^
/usr/pgsql-11/include/server/access/gist.h:171:37: note: in definition of macro ‘gistentryinit’
    (e).offset = (o); (e).leafkey = (l); } while (0)
                                     ^
make: *** [imgsmlr_idx.o] Error 1

add it to .h

#ifndef FALSE
#define FALSE   (0)
#endif


#ifndef TRUE
#define TRUE    (!FALSE)
#endif

make USE_PGXS=1 install,fail

[postgres@zys-spider-215 imgsmlr-master]$ make USE_PGXS=1 installcheck
/usr/local/pgsql/lib/pgxs/src/makefiles/../../src/test/regress/pg_regress --inputdir=./ --psqldir='/usr/local/pgsql/bin' --dbname=contrib_regression imgsmlr
(using postmaster on Unix socket, default port)
============== dropping database "contrib_regression" ==============
DROP DATABASE
============== creating database "contrib_regression" ==============
CREATE DATABASE
ALTER DATABASE
============== running regression test queries ==============
test imgsmlr ... FAILED

======================
1 of 1 tests failed.

The differences that caused some tests to fail can be viewed in the
file "/usr/local/src/imgsmlr-master/regression.diffs". A copy of the test summary that you see
above is saved in the file "/usr/local/src/imgsmlr-master/regression.out".

make: *** [installcheck] Error 1

Query speed is too slow.

I'm sorry to bother you.
I used this extension for 400 million img, and index on signature with gist. But I found that query on signature is very low (cost more than 30 seconds for query like: select sig <-> '(0.631970, 1.085420, 0.449103, 0.885163, 0.797899, 0.571842, 0.879878, 1.050410, 0.438902, 0.540778, 0.641945, 0.395742, 0.133787, 1.013711, 0.191292, 0.326252)' as dist from img_table order by dist limit 1;. By explain, I see it's using Index Scan).

I think the root cause is that: after we "Normalize" intensiveness in the pattern, the signatures becomes too concentrated in Gist index. It hits too many blocks in memory to get the result.

Is it possible to skip the normalization step when producing Pattern & Signature? Or other solution to solve the performance issue? Thanks!