Re: [HACKERS] why not parallel seq scan for slow functions

[Amit Kapila]

On Thu, Jul 13, 2017 at 7:38 AM, Amit Kapila  wrote:
> On Wed, Jul 12, 2017 at 11:20 PM, Jeff Janes  wrote:
>>
>>
>>
>> Setting parallel_workers to 8 changes the threshold for the parallel to even
>> be considered from parellel_tuple_cost <= 0.0049 to <= 0.0076.  So it is
>> going in the correct direction, but not by enough to matter.
>>
>
> You might want to play with cpu_tuple_cost and or seq_page_cost.
>

I don't know whether the patch will completely solve your problem, but
this seems to be the right thing to do.  Do you think we should stick
this for next CF?


-- 
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

[Robert Haas]

On Sat, Jul 22, 2017 at 5:50 PM, Mark Rofail  wrote:
> so personally I don't think we should leave creating a GIN index up to the
> user, it should be automatically generated instead.

I can certainly understand why you feel that way, but trying to do
that in your patch is just going to get your patch rejected.  We don't
want array foreign keys to have different behavior than regular
foreign keys, and regular foreign keys don't do this automatically.
We could change that, but I suspect it would cause us some pretty
serious problems with upgrades from older versions with the existing
behavior to newer versions with the revised behavior.

There are other problems, too.  Suppose the user creates the foreign
key and then drops the associated index; then, they run pg_dump.  Will
restoring the dump recreate the index?  If so, then you've broken
dump/restore, because now it doesn't actually recreate the original
state of the database.  You might think of fixing this by not letting
the index be dropped, but that's problematic too, because a
fairly-standard way of removing index bloat is to create a new index
with the "concurrently" flag and then drop the old one.  Another
problem entirely is that the auto-generated index will need to have an
auto-generated name, and that name might happen to conflict with the
name of some other object that already exists in the database, which
doesn't initially seem like a problem because you can just generate a
different name instead; indeed, we already do such things.  But the
thorny point is that you have to preserve whatever name you choose --
and the linkage to the array foreign key that caused it to be created
-- across a dump/restore cycle; otherwise you'll have cases where
conflicting names cause failures.  I doubt this is a comprehensive
list of things that might go wrong; it's intended as an illustrative
list, not an exhaustive one.

This is a jumbo king-sized can of worms, and even a very experienced
contributor would likely find it extremely difficult to sort all of
the problems that would result from a change in this area.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Syncing sql extension versions with shared library versions

[Craig Ringer]

On 22 Jul. 2017 04:19, "Mat Arye"  wrote:

Hi All,

I am developing the TimescaleDB extension for postgres (
https://github.com/timescale/timescaledb) and have some questions about
versioning. First of all, I have to say that the versioning system on the
sql side is wonderful. It's really simple to write migrations etc.

However when thinking through the implications of having a database cluster
with databases having different extension versions installed, it was not
apparently clear to me how to synchronize the installed extension version
with a shared library version. For example, if I have timescaledb version
0.1.0 in one db and version 0.2.0 in another db, I'd like for
timescaledb-0.1.0.so and  timescaledb-0.2.0.so to be used, respectively. (I
want to avoid having to keep backwards-compatibility for all functions in
future shared-libraries). In our case, this is further complicated by the
fact that we need to preload the shared library since we are accessing the
planner hooks etc. Below, I'll describe some solutions I have been thinking
about, but wanted to hear if anyone else on this list has already solved
this problem and has some insight. It is also quite possible I am missing
something.

Issue 1: Preloading the right shared library.
When preloading libraries (either via local_preload_libraries, or
session_preload_libraries, shared_preload_libraries), it would be nice to
preload the shared_library according to it's version. But, I looked through
the code and found no logic for adding version numbers to shared library
names.


You can't do that for shared_preload_libraries, because at
shared_preload_libraries time we don't have access to the DB and can't look
up the installed extension version(s). There might be different ones in
different DBs too.



Solution 1: Set session_preload_libraries on the database via ALTER
DATABASE SET. This can be done in the sql and the sql version-migration
scripts can change this value as you change extensions versions. I think
this would work, but it seems very hack-ish and less-than-ideal.


Won't work for some hooks, right?

I've faces this issue with pglogical and BDR. If the user tries to update
the extension before a new enough .so is loaded we ERROR due to failure to
load missing C functions.

If the .so is updated first the old extension function definitions can fail
at runtime if funcs are removed or change signature, but won't fail at
startup or load.

So we let the C extension detect when it's newer than the loaded SQL ext
during its startup and run an ALTER EXTENSION to update it.

We don't attempt to support downgrades.

Re: [HACKERS] Buildfarm failure and dubious coding in predicate.c

[Tom Lane]

I wrote:
> And, while I'm looking at this ... isn't this "scratch target" logic
> just an ineffective attempt at waving a dead chicken?  It's assuming
> that freeing an entry in a shared hash table guarantees that it can
> insert another entry.  But that hash table is partitioned, meaning it has
> a separate freelist per partition.  So the extra entry only provides a
> guarantee that you can insert something into the same partition it's in,
> making it useless for this purpose AFAICS.

After further study I found the bit in get_hash_entry() about "borrowing"
entries from other freelists.  That makes all of this safe after all,
which is a good thing because I'd also found other assumptions that would
have been broken by the behavior I posited above.  But I think that that
code is desperately undercommented -- the reader could be forgiven for
thinking that it was an optional optimization, and not something that
is critical for correctness of several different callers.  I'm going to
go improve the comments.

Meanwhile, it's still pretty unclear what happened yesterday on
culicidae.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Syncing sql extension versions with shared library versions

[Tom Lane]

Robert Haas  writes:
> On Fri, Jul 21, 2017 at 4:17 PM, Mat Arye  wrote:
>> (I
>> want to avoid having to keep backwards-compatibility for all functions in
>> future shared-libraries).

> Are you sure that's a good idea?  It seems like swimming upstream
> against the design.  I mean, instead of creating a dispatcher library
> that loads either v1 or v2, maybe you could just put it all in one
> library, add a "v1" or "v2" suffix to the actual function names where
> appropriate, and then set up the SQL definitions to call the correct
> one.  I mean, it's the same thing, but with less chance of the dynamic
> loader ruining your day.

Worth noting also is that we have a fair amount of experience now with
handling API changes in contrib modules.  It's worth looking through
the update histories of the contrib modules that have shipped multiple
versions to see how they dealt with such issues.  As Robert suggests,
it's just not that hard; usually a few shim functions in the C code will
do the trick.

I'd also point out that while you may think you don't need to keep
backwards compatibility across versions, your users are probably
going to think differently.  The amount of practical freedom you'd
gain here is probably not so much as you're hoping.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Syncing sql extension versions with shared library versions

[Robert Haas]

On Fri, Jul 21, 2017 at 4:17 PM, Mat Arye  wrote:
> (I
> want to avoid having to keep backwards-compatibility for all functions in
> future shared-libraries).

Are you sure that's a good idea?  It seems like swimming upstream
against the design.  I mean, instead of creating a dispatcher library
that loads either v1 or v2, maybe you could just put it all in one
library, add a "v1" or "v2" suffix to the actual function names where
appropriate, and then set up the SQL definitions to call the correct
one.  I mean, it's the same thing, but with less chance of the dynamic
loader ruining your day.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers