Re: [HACKERS] Custom compression methods - Mailing list pgsql-hackers

Hi,

On 11/23/2017 10:38 AM, Ildus Kurbangaliev wrote:
> On Tue, 21 Nov 2017 18:47:49 +0100
> Tomas Vondra <tomas.vondra@2ndquadrant.com> wrote:
> 
>>>   
>>
>> Hmmm, it still doesn't work for me. See this:
>>
>>     test=# create extension pg_lz4 ;
>>     CREATE EXTENSION
>>     test=# create table t_lz4 (v text compressed lz4);
>>     CREATE TABLE
>>     test=# create table t_pglz (v text);
>>     CREATE TABLE
>>     test=# insert into t_lz4 select repeat(md5(1::text),300);
>>     INSERT 0 1
>>     test=# insert into t_pglz select * from t_lz4;
>>     INSERT 0 1
>>     test=# drop extension pg_lz4 cascade;
>>     NOTICE:  drop cascades to 2 other objects
>>     DETAIL:  drop cascades to compression options for lz4
>>     drop cascades to table t_lz4 column v
>>     DROP EXTENSION
>>     test=# \c test
>>     You are now connected to database "test" as user "user".
>>     test=# insert into t_lz4 select repeat(md5(1::text),300);^C
>>     test=# select * from t_pglz ;
>>     ERROR:  cache lookup failed for compression options 16419
>>
>> That suggests no recompression happened.
> 
> Should be fixed in the attached patch. I've changed your extension a
> little bit according changes in the new patch (also in attachments).
> 

Hmm, this seems to have fixed it, but only in one direction. Consider this:
   create table t_pglz (v text);   create table t_lz4 (v text compressed lz4);
   insert into t_pglz select repeat(md5(i::text),300)   from generate_series(1,100000) s(i);
   insert into t_lz4 select repeat(md5(i::text),300)   from generate_series(1,100000) s(i);
   \d+
    Schema |  Name  | Type  | Owner | Size  | Description   --------+--------+-------+-------+-------+-------------
public| t_lz4  | table | user  | 12 MB |    public | t_pglz | table | user  | 18 MB |   (2 rows)
 
   truncate t_pglz;   insert into t_pglz select * from t_lz4;
   \d+
    Schema |  Name  | Type  | Owner | Size  | Description   --------+--------+-------+-------+-------+-------------
public| t_lz4  | table | user  | 12 MB |    public | t_pglz | table | user  | 18 MB |   (2 rows)
 

which is fine. But in the other direction, this happens
   truncate t_lz4;   insert into t_lz4 select * from t_pglz;
    \d+                      List of relations    Schema |  Name  | Type  | Owner | Size  | Description
--------+--------+-------+-------+-------+-------------   public | t_lz4  | table | user  | 18 MB |    public | t_pglz
|table | user  | 18 MB |   (2 rows)
 

which means the data is still pglz-compressed. That's rather strange, I
guess, and it should compress the data using the compression method set
for the target table instead.

regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services