Re: Index Skip Scan - attempting to evalutate patch

On Wed, Jun 26, 2019 at 02:12:55PM +0000, pguser wrote:
>
>‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
>On Wednesday, June 26, 2019 2:55 PM, pguser <pguser@diorite.uk> wrote:
>
>> ‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
>> On Wednesday, June 26, 2019 1:07 PM, Dmitry Dolgov 9erthalion6@gmail.com wrote:
>>
>> > > On Wed, Jun 26, 2019 at 1:53 PM pguser pguser@diorite.uk wrote:
>> > > If I apply the latest patch (which says 1 of 2? - maybe I'm missing a part of the patch?), I apply with
>> >
>> > Hi,
>> > First of all, thanks for evaluation!
>>
>> No problem. I'd like to get involved in helping this patch mature as I think that we're suffering in a few areas of
performancedue to this.
 
>>
>> > Could you by any change provide also relations schema that were supposed to be
>> > described by this command?
>>
>> Okay for now, it's not much. I get the issue of the SIGSEGV on a brand new database with only one relation:
>>
>> This is with the 12beta2 as compiled from git sources by me:
>>
>> psql (12beta2)
>> Type "help" for help.
>>
>> db2=> \d
>>
>>        List of relations
>>
>>
>> Schema | Name | Type | Owner
>> --------+------+-------+-------
>> e5 | t1 | table | e5
>> (1 row)
>>
>> db2=> \d t1
>>
>>                         Table "e5.t1"
>>
>>
>> Column | Type | Collation | Nullable | Default
>> --------+-------------------+-----------+----------+---------
>> n1 | smallint | | |
>> n2 | smallint | | |
>> c1 | character varying | | |
>> c2 | character varying | | |
>> Indexes:
>> "i1" btree (n1, n2, c1)
>>
>> And with patch 20 applied:
>>
>> psql (12beta2)
>> Type "help" for help.
>>
>> db2=> \d
>> psql: server closed the connection unexpectedly
>> This probably means the server terminated abnormally
>> before or while processing the request.
>> The connection to the server was lost. Attempting reset: Failed.
>> !> \q
>> [postgres@ip-172-31-33-89 ~]$ . sql2
>> psql (12beta2)
>> Type "help" for help.
>>
>> db2=> \d t1
>> psql: server closed the connection unexpectedly
>> This probably means the server terminated abnormally
>> before or while processing the request.
>> The connection to the server was lost. Attempting reset: Failed.
>> !> \q
>>
>> In fact, if I do:
>>
>> createdb db3
>> psql -d db3
>> db3=# \d
>> psql: server closed the connection unexpectedly
>> This probably means the server terminated abnormally
>> before or while processing the request.
>> The connection to the server was lost. Attempting reset: Failed.
>>
>> I get this on empty database with no relations yet defined.
>>
>> I feel I have done something silly or missed something when applying patch
>
>
>I find that my patched installation can't create its own initdb either:
>
>initdb -D /pgd2
>The files belonging to this database system will be owned by user "postgres".
>This user must also own the server process.
>
>The database cluster will be initialized with locale "en_US.UTF-8".
>The default database encoding has accordingly been set to "UTF8".
>The default text search configuration will be set to "english".
>
>Data page checksums are disabled.
>
>fixing permissions on existing directory /pgd2 ... ok
>creating subdirectories ... ok
>selecting dynamic shared memory implementation ... posix
>selecting default max_connections ... 100
>selecting default shared_buffers ... 128MB
>selecting default timezone ... UTC
>creating configuration files ... ok
>running bootstrap script ... ok
>performing post-bootstrap initialization ... 2019-06-26 14:05:47.807 UTC [8120] FATAL:  could not open file
"base/1/2663.1"(target block 17353008): previous segment is only 4 blocks at character 65
 
>2019-06-26 14:05:47.807 UTC [8120] STATEMENT:  INSERT INTO pg_shdepend SELECT 0,0,0,0, tableoid,oid, 'p'  FROM
pg_authid;
>
>child process exited with exit code 1
>initdb: removing contents of data directory "/pgd2"
>

Well, there's something seriously wrong with your build or environment,
then. I've tried reproducing the issue, but it works just fine for me
(initdb, psql, ...).

>
>I was hoping to share the pgdata between 12beta2 without patch, and
>12beta2 with patch, for ease of side by side comparison.
>

That might be dangerous, if there may be differences in contents of
catalogs. I don't think the patch does that though, and for me it works
just fine. I can initdb database using current master, create table +
indexes, do \d. And I can do that with the patch applied too.

>Even more I feel that I'm missing something more than just this 20 patch
>from the Index Skip Scan thread.
>

Are you sure this is not some sort of OOM issue? That might also
demonstrate as a segfault, in various cases.

regards

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