pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: your mail
5+ messages / 3 participants
[nested] [flat]
* Re: your mail
@ 2017-08-08 20:18 Andres Freund <andres@anarazel.de>
0 siblings, 1 reply; 5+ messages in thread
From: Andres Freund @ 2017-08-08 20:18 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Rick Otten <rottenwindfish@gmail.com>; pgsql-bugs; Amit Kapila <amit.kapila16@gmail.com>; Robert Haas <robertmhaas@gmail.com>
On 2017-08-08 16:15:15 -0400, Tom Lane wrote:
> Andres Freund <andres@anarazel.de> writes:
> > Perhaps, for v11, we should actually make sure there's no memory context
> > etc set during _PG_init() to catch such problems earlier?
>
> I don't see much of a way to do that in the "typical" case where
> the library load happens as a result of a SQL command. You can't
> just say "oh, we're not in a transaction" and then later "wait,
> yes we are".
Transaction seems hard, but setting CurrentMemoryContext = NULL during
library load seems quite possible. And that'll cause a lot of code that
assumes an in-progress transaction to fail.
> Maybe we should recommend that extension authors test their libraries
> in the preload scenario ...
That probably won't hurt. A quick search in the docs doesn't turn up any
evidence of us having documented that you shouldn't do database access
in _PG_init()...
Greetings,
Andres Freund
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: your mail
@ 2017-08-08 21:12 Tom Lane <tgl@sss.pgh.pa.us>
parent: Andres Freund <andres@anarazel.de>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2017-08-08 21:12 UTC (permalink / raw)
To: Andres Freund <andres@anarazel.de>; +Cc: Rick Otten <rottenwindfish@gmail.com>; pgsql-bugs; Amit Kapila <amit.kapila16@gmail.com>; Robert Haas <robertmhaas@gmail.com>
Andres Freund <andres@anarazel.de> writes:
> On 2017-08-08 16:15:15 -0400, Tom Lane wrote:
>> I don't see much of a way to do that in the "typical" case where
>> the library load happens as a result of a SQL command. You can't
>> just say "oh, we're not in a transaction" and then later "wait,
>> yes we are".
> Transaction seems hard, but setting CurrentMemoryContext = NULL during
> library load seems quite possible. And that'll cause a lot of code that
> assumes an in-progress transaction to fail.
And it would cause a lot of code that *doesn't* assume that to fail,
too. That has basically nothing to do with not being in a transaction,
so I don't think it would be helpful here.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: your mail
@ 2017-08-08 21:19 Andres Freund <andres@anarazel.de>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Andres Freund @ 2017-08-08 21:19 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Rick Otten <rottenwindfish@gmail.com>; pgsql-bugs; Amit Kapila <amit.kapila16@gmail.com>; Robert Haas <robertmhaas@gmail.com>
On 2017-08-08 17:12:14 -0400, Tom Lane wrote:
> Andres Freund <andres@anarazel.de> writes:
> > On 2017-08-08 16:15:15 -0400, Tom Lane wrote:
> >> I don't see much of a way to do that in the "typical" case where
> >> the library load happens as a result of a SQL command. You can't
> >> just say "oh, we're not in a transaction" and then later "wait,
> >> yes we are".
>
> > Transaction seems hard, but setting CurrentMemoryContext = NULL during
> > library load seems quite possible. And that'll cause a lot of code that
> > assumes an in-progress transaction to fail.
>
> And it would cause a lot of code that *doesn't* assume that to fail,
> too. That has basically nothing to do with not being in a transaction,
> so I don't think it would be helpful here.
Wouldn't mostsuch code be a bad idea anyway? If it's in transaction
context, you can't rely on the lifetime of allocated memory. If it's
TopMemoryContext, the code should be careful about not leaking. So
being aware of/explicit about the context used seems advisable.
- Andres
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: your mail
@ 2017-08-08 22:12 Tom Lane <tgl@sss.pgh.pa.us>
parent: Andres Freund <andres@anarazel.de>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2017-08-08 22:12 UTC (permalink / raw)
To: Andres Freund <andres@anarazel.de>; +Cc: Rick Otten <rottenwindfish@gmail.com>; pgsql-bugs; Amit Kapila <amit.kapila16@gmail.com>; Robert Haas <robertmhaas@gmail.com>
Andres Freund <andres@anarazel.de> writes:
> On 2017-08-08 17:12:14 -0400, Tom Lane wrote:
>> And it would cause a lot of code that *doesn't* assume that to fail,
>> too. That has basically nothing to do with not being in a transaction,
>> so I don't think it would be helpful here.
> Wouldn't mostsuch code be a bad idea anyway?
No, not really. You're right that a palloc appearing directly in a
_PG_init function is a bit dubious, but that doesn't mean that _PG_init
can't call anything that allocates memory. Also, since _PG_init is by
definition only called once per process, I do not think that authors
need to be rapped on the knuckles if they leak a small amount of
TopMemoryContext memory.
In any case, the result of doing that would only be that people would
throw in MemoryContextSwitchTo calls, it wouldn't discourage them from
trying to do catalog accesses for instance.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: your mail
@ 2022-07-02 13:49 Julien Rouhaud <rjuju123@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Julien Rouhaud @ 2022-07-02 13:49 UTC (permalink / raw)
To: Hung Nguyen <hungnq1989@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
Hi,
On Sat, Jul 02, 2022 at 03:15:55PM +0300, Hung Nguyen wrote:
> Hellom
>
> <https://dba.stackexchange.com/posts/314015/timeline;
>
> I've just upgraded my postgres instance from v11 to v14. There was an
> interesting problem because we have a trigram index on order_id column.
>
> This new feature makes our simple join query on that column very slow. For
> example:
>
> SELECT count(*) from order_rows o1 join order o2 on o1.order_id = o2.order_id
Oh, that's surprising. It's not clear to me why any index would be used with
such a query, especially if it's not compatible with index only scans. Is that
some simplification of some query or really one that exhibit the problem?
>
> To solve this problem the existing trigram index must be dropped and we
> cannot use ILIKE queries on this column. I just wonder if there is any way
> to tell postgres what index (in this case btree index) to use when doing
> the join operations?
There's no such capability builtin. However, trigram indexes should be way
more expensive that btree indexes in general, so the planner should be able to
make the correct decision, there must be something else going on.
>
> [Postgres 14] Allow GiST/GIN pg_trgm indexes to do equality lookups (Julien
> Rouhaud)
>
> I'm not sure if this is really a bug, but its' super weird if the query
> planner favors the trigram index over the b-tree index for joining is not
> optimal to me. Thank you so much.
Can you provide the full definition for both order and order_rows, and EXPLAIN
(ANALYZE, TIMING, BUFFERS) for the problematic query, with and without the trgm
index being used? Doing a "SET enable_bitmapscan = 0;" should be enough for
that. Do you have any usual settings configured, like enable_* or others?
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2022-07-02 13:49 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-08-08 20:18 Re: your mail Andres Freund <andres@anarazel.de>
2017-08-08 21:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2017-08-08 21:19 ` Andres Freund <andres@anarazel.de>
2017-08-08 22:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-07-02 13:49 Re: your mail Julien Rouhaud <rjuju123@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox