Re: dsa_allocate() faliure

[Thomas Munro]

On Tue, Jan 30, 2018 at 5:37 AM, Tom Lane  wrote:
> Rick Otten  writes:
>> I'm wondering if there is anything I can tune in my PG 10.1 database to
>> avoid these errors:
>
>> $  psql -f failing_query.sql
>> psql:failing_query.sql:46: ERROR:  dsa_allocate could not find 7 free pages
>> CONTEXT:  parallel worker
>
> Hmm.  There's only one place in the source code that emits that message
> text:
>
> /*
>  * Ask the free page manager for a run of pages.  This should always
>  * succeed, since both get_best_segment and make_new_segment should
>  * only return a non-NULL pointer if it actually contains enough
>  * contiguous freespace.  If it does fail, something in our backend
>  * private state is out of whack, so use FATAL to kill the process.
>  */
> if (!FreePageManagerGet(segment_map->fpm, npages, _page))
> elog(FATAL,
>  "dsa_allocate could not find %zu free pages", npages);
>
> Now maybe that comment is being unreasonably optimistic, but it sure
> appears that this is supposed to be a can't-happen case, in which case
> you've found a bug.

This is probably the bug fixed here:

https://www.postgresql.org/message-id/E1eQzIl-0004wM-K3%40gemulon.postgresql.org

That was back patched, so 10.2 will contain the fix.  The bug was not
in dsa.c itself, but in the parallel query code that mixed up DSA
areas, corrupting them.  The problem comes up when the query plan has
multiple Gather nodes (and a particular execution pattern) -- is that
the case here, in the EXPLAIN output?  That seems plausible given the
description of a 50-branch UNION.  The only workaround until 10.2
would be to reduce max_parallel_workers_per_gather to 0 to prevent
parallelism completely for this query.

-- 
Thomas Munro
http://www.enterprisedb.com

Re: dsa_allocate() faliure

[Tom Lane]

Rick Otten  writes:
> I'm wondering if there is anything I can tune in my PG 10.1 database to
> avoid these errors:

> $  psql -f failing_query.sql
> psql:failing_query.sql:46: ERROR:  dsa_allocate could not find 7 free pages
> CONTEXT:  parallel worker

Hmm.  There's only one place in the source code that emits that message
text:

/*
 * Ask the free page manager for a run of pages.  This should always
 * succeed, since both get_best_segment and make_new_segment should
 * only return a non-NULL pointer if it actually contains enough
 * contiguous freespace.  If it does fail, something in our backend
 * private state is out of whack, so use FATAL to kill the process.
 */
if (!FreePageManagerGet(segment_map->fpm, npages, _page))
elog(FATAL,
 "dsa_allocate could not find %zu free pages", npages);

Now maybe that comment is being unreasonably optimistic, but it sure
appears that this is supposed to be a can't-happen case, in which case
you've found a bug.

cc'ing the DSA authors for comment.

regards, tom lane

PostgreSQL 10.1 partitions and indexes

[Mariel Cherkassky]

Hi,
I'm currently migrating an oracle schema to postgresql. In the oracle`s
schema there is a table partition that has partitions by range(date - for
every day) and each partition has a sub partition by list(some values..).
Moreover, the data is loaded from a csv in a bulk. One important thing is
that some data might be imported twice therefore there must but a unique
index on the table.

On PostgreSQL 10.1 I created the main table partitioned by range(date) and
I created all the sub partitions. I have 2 problems :

1)In the oracle main table there are global indexes for selects that
involve columns that arent part of the range or list partitions. According
to the documentation I need to create the indexes on each leaf. I have
partition for every day in the year so I'll have about 6(num of global
indexes in oracle)*365(days of year)*7(number of sub partitions) = 15330
indexes created every year. I guess that the performance that I will have
when I select columns that arent part of the partitions order will be
pretty bad. Any idea ?

2)Regarding the uniqueness, the only solution is to create a unique index
for every subpartition ?

3)Any suggestions how to improve queries that involve columns that arent
part of the paritions order ?

Thanks , Mariel.