Re: SET or STRICT modifiers on function affect planner row estimates

Hi Tom, > On 30 Sep 2024, at 21:24, Tom Lane wrote: > > =?utf-8?Q?Micha=C5=82_K=C5=82eczek?= writes: >>> > >> The table structure is as follows: > >> CREATE TABLE tbl (…) PARTITION BY RANGE year(col02_date) > > You're still expecting people to magically intuit what all those > "..."s are.

Re: SET or STRICT modifiers on function affect planner row estimates

Hi, Thanks for taking a look. > On 30 Sep 2024, at 14:14, Ashutosh Bapat wrote: > > Hi Michal, > It is difficult to understand the exact problem from your description. > Can you please provide EXPLAIN outputs showing the expected plan and > the unexpected plan; plans on the node where the query

SET or STRICT modifiers on function affect planner row estimates

Hi Hackers, I am not sure if this is a bug or I am missing something: There is a partitioned table with partitions being a mix of foreign and regular tables. I have a function: report(param text) RETURNS TABLE(…) STABLE LANGUAGE sql AS $$ SELECT col1, expr1(col2), expr2(col2), sum(col3) FROM tb

Re: DRAFT: Pass sk_attno to consistent function

> On 26 Jul 2024, at 10:10, Michał Kłeczek wrote: > > > >> On 26 Jul 2024, at 01:28, Matthias van de Meent >> wrote: >> >> All in all, this still seems like a very (very) specific optimization, >> of which I'm not sure that it is gen

Re: DRAFT: Pass sk_attno to consistent function

> On 26 Jul 2024, at 01:28, Matthias van de Meent > wrote: > > All in all, this still seems like a very (very) specific optimization, > of which I'm not sure that it is generalizable. However, array > introspection and filtering for SAOP equality checks feel like a > relatively easy (?) push-d

Re: DRAFT: Pass sk_attno to consistent function

Hi Tom, Thanks for looking at it. > On 24 Jul 2024, at 22:19, Tom Lane wrote: > > =?utf-8?Q?Micha=C5=82_K=C5=82eczek?= writes: >> I understand extensibility of GIST makes many operators opaque to the >> planner and it is the “consistent” function that can perform optimisations >> (or we can

Re: allow sorted builds for btree_gist

> On 17 May 2024, at 21:41, Tomas Vondra wrote: > > Hi, > > I've been looking at GiST to see if there could be a good way to do > parallel builds - and there might be, if the opclass supports sorted > builds, because then we could parallelize the sort. > > But then I noticed we support this

Re: Is it safe to cache data by GiST consistent function

> On 3 Apr 2024, at 19:02, Tom Lane wrote: > > =?utf-8?Q?Micha=C5=82_K=C5=82eczek?= writes: > >> pg_trgm consistent caches tigrams but it has some logic to make sure cached >> values are recalculated: > >> cache = (gtrgm_consistent_cache *) fcinfo->flinfo->fn_extra; >> if (cache =

Re: Is it safe to cache data by GiST consistent function

Thanks for taking your time to answer. Not sure if I understand though. > On 3 Apr 2024, at 16:27, Tom Lane wrote: > > =?utf-8?Q?Micha=C5=82_K=C5=82eczek?= writes: >> When implementing a GiST consistent function I found the need to cache >> pre-processed query across invocations. >> I am not s

Is it safe to cache data by GiST consistent function

Hello, When implementing a GiST consistent function I found the need to cache pre-processed query across invocations. I am not sure if it is safe to do (or I need to perform some steps to make sure cached info is not leaked between rescans). The comment in gistrescan says: /*

Re: DRAFT: Pass sk_attno to consistent function

> On 22 Mar 2024, at 10:11, Michał Kłeczek wrote: > >> >> On 22 Mar 2024, at 01:29, Michał Kłeczek wrote: >> >>  >> >>> On 21 Mar 2024, at 23:42, Matthias van de Meent >>> wrote: >>> >>> >>> You seem to

Re: DRAFT: Pass sk_attno to consistent function

> On 22 Mar 2024, at 01:29, Michał Kłeczek wrote: > >  > >> On 21 Mar 2024, at 23:42, Matthias van de Meent >> wrote: >> >>> On Tue, 19 Mar 2024 at 17:00, Michał Kłeczek wrote: >>> >>> With this operator we can write our queries l

Re: DRAFT: Pass sk_attno to consistent function

> On 21 Mar 2024, at 23:42, Matthias van de Meent > wrote: > > On Tue, 19 Mar 2024 at 17:00, Michał Kłeczek wrote: > >> With this operator we can write our queries like: >> >> account_number ||= [list of account numbers] AND >> account_number = ANY

Re: DRAFT: Pass sk_attno to consistent function

Hi All, Since it looks like there is not much interest in the patch I will try to provide some background to explain why I think it is needed. We are in the process of migration from an old db platform to PostgreSQL. Our database is around 10TB big and contains around 10 billion financial trans

Re: DRAFT: Pass sk_attno to consistent function

Wrong file in the previous message - sorry for the noise.Attached is a fixed patch.Thanks,Michal 0001-Pass-key-sk_attno-to-consistent-function.patch Description: Binary data On 18 Mar 2024, at 15:14, Michał Kłeczek wrote:I realised it might be enough to pass sk_attno to consistent function as

DRAFT: Pass sk_attno to consistent function

I realised it might be enough to pass sk_attno to consistent function as that should be enough to lookup metadata if needed.Attached is a draft patch that does this.—Michal 0001-Pass-key-sk_attno-to-consistent-function.patch Description: Binary data On 18 Mar 2024, at 14:31, Michał Kłeczek

Re: Alternative SAOP support for GiST

Hi All, > On 11 Mar 2024, at 18:58, Michał Kłeczek wrote: > > Hi All, > > During my journey of designing Pg based solution at my work I was severely > hit by several shortcomings in GiST. > The most severe one is the lack of support for SAOP filters as it makes it

Alternative SAOP support for GiST

Hi All, During my journey of designing Pg based solution at my work I was severely hit by several shortcomings in GiST. The most severe one is the lack of support for SAOP filters as it makes it difficult to have partition pruning and index (only) scans working together. To overcome the difficu

Invalid query generated by postgres_fdw with UNION ALL and ORDER BY

The following query: SELECT * FROM ( SELECT 2023 AS year, * FROM remote_table_1 UNION ALL SELECT 2022 AS year, * FROM remote_table_2 ) ORDER BY year DESC; yields the following remote query: SELECT [columns] FROM remote_table_1 ORDER BY 2023 DESC and subsequently fails remote execution.

Re: Segmentation fault on FreeBSD with GSSAPI authentication

> On 27 Jan 2024, at 15:43, Tom Lane wrote: > > =?utf-8?Q?Micha=C5=82_K=C5=82eczek?= writes: >> I am trying to use GSSAPI (Kerberos auth) on FreeBSD. > >> After several attempts I was able to build PostgreSQL 16 from FreeBSD ports >> with GSSAPI support. The steps were: >> 1. Uninstall Heim

Segmentation fault on FreeBSD with GSSAPI authentication

Hi All, I am trying to use GSSAPI (Kerberos auth) on FreeBSD. After several attempts I was able to build PostgreSQL 16 from FreeBSD ports with GSSAPI support. The steps were: 1. Uninstall Heimdal 2. Install krb5 (MIT Kerberos) 3. Build Pg with GSSAPI support I configured pg_hba.conf to require

Re: New Window Function: ROW_NUMBER_DESC() OVER() ?

> On 16 Jan 2024, at 16:51, Maiquel Grassi wrote: > > > Imagine I have a dataset that is returned to my front-end, and I want to > reverse enumerate them (exactly the concept of Math enumerating integers). > The row_number does the ascending enumeration, but I need the descending > enumerat

Re: SET ROLE x NO RESET

> On 3 Jan 2024, at 18:22, Jelte Fennema-Nio wrote: > > >> In my case I have scripts that I want to execute with limited privileges >> and make sure the scripts cannot escape the sandbox via RESET ROLE. > > Depending on the desired workflow I think that could work for you too. > Because it a

Re: SET ROLE x NO RESET

> On 2 Jan 2024, at 18:36, Robert Haas wrote: > > On Sun, Dec 31, 2023 at 2:20 PM Joe Conway wrote: >> On 12/30/23 17:19, Michał Kłeczek wrote: >>>> On 30 Dec 2023, at 17:16, Eric Hanson wrote: >>>> >>>> What do you think of adding a NO RE

Re: SET ROLE x NO RESET

> On 30 Dec 2023, at 17:16, Eric Hanson wrote: > > What do you think of adding a NO RESET option to the SET ROLE command? What I proposed some time ago is SET ROLE … GUARDED BY ‘password’, so that you could later: RESET ROLE WITH ‘password' https://www.postgresql.org/message-id/F9428C6E-4CCC-

Re: DRAFT GIST support for ORDER BY

> On 30 Oct 2023, at 13:31, Matthias van de Meent > wrote: > >> The solution is not ideal as it requires registering “<“ and “>” operators >> as ordering operators in opfamily >> (which in turn makes it possible to issue somewhat meaningless “ORDER BY a < >> ‘constant’) > > I don't quite u

DRAFT GIST support for ORDER BY

ble.It would be great if someone could take a look at it.Thanks,Michal  DRAFT-gist-orderby.patch Description: Binary data On 24 Oct 2023, at 13:22, Michał Kłeczek wrote:Hi,Some time ago I’ve provided some details with the issues we face when trying to use GIST and partitioning at the same time in t

A case for GIST supporting ORDER BY

Hi, Some time ago I’ve provided some details with the issues we face when trying to use GIST and partitioning at the same time in the postgresql-general mailing list: https://www.postgresql.org/message-id/3FA1E0A9-8393-41F6-88BD-62EEEA1EC21F%40kleczek.org GIST index and ORDER BY postgresql.org

Re: Draft LIMIT pushdown to Append and MergeAppend patch

> On 9 Oct 2023, at 15:04, Ashutosh Bapat wrote: > > On Mon, Oct 9, 2023 at 4:33 PM David Rowley > wrote: >> >> What are there benefits if the paths are already ordered? e.g if it's >> an index scan then we'll only pull the tuples we need from it. >> > > postgr

Re: Draft LIMIT pushdown to Append and MergeAppend patch

Thanks for the feedback. > On 8 Oct 2023, at 03:33, Andy Fan wrote: > > > > On Sun, Oct 8, 2023 at 5:04 AM Michał Kłeczek <mailto:mic...@kleczek.org>> wrote: >> Hi All, >> >> Attached is a second version of the patch. >> >>

Re: Draft LIMIT pushdown to Append and MergeAppend patch

Description: Binary data > On 7 Oct 2023, at 12:01, Michał Kłeczek wrote: > > Hi All, > > Attached is a draft patch implementing LIMIT pushdown to Append and > MergeAppend nodes. > > This patch eliminates the need to resort to subqueries to optimise UNIONs. > It also enable

Draft LIMIT pushdown to Append and MergeAppend patch

Hi All, Attached is a draft patch implementing LIMIT pushdown to Append and MergeAppend nodes. This patch eliminates the need to resort to subqueries to optimise UNIONs. It also enables more aggressive partition pruning. Not sure if it causes LIMIT pushdown to foreign partitions though. Applyin

Re: FDW LIM IT pushdown

Sorry, I wasn’t precise - my question is about foreign partitions - LIMIT on ordinary tables is supported. Thanks, Michal > On 6 Oct 2023, at 18:02, Michał Kłeczek wrote: > > Hello hackers, > > First timer here with a question: > > I’ve searched through various e-mail

FDW LIM IT pushdown

Hello hackers, First timer here with a question: I’ve searched through various e-mail threads and documents and could not find if pushdown of LIMIT clauses to FDW is implemented. Seen some conversation about that a couple of years ago when that was treated as a feature request but nothing sinc

Re: Preventing non-superusers from altering session authorization

Hi, I’ve just stumbled upon this patch and thread and thought I could share an idea of adding an optional temporary secret to SET SESSION AUTHORIZATION so that it is only possible to RESET SESSION AUTHORIZATION by providing the same secret ,like: SET SESSION AUTHORIZATION [role] GUARDED BY ‘[s

Re: Removing unneeded self joins

Hi All, I just wanted to ask about the status and plans for this patch. I can see it being stuck at “Waiting for Author” status in several commit tests. I think this patch would be really beneficial for us as we heavily use views to structure out code. Each view is responsible for providing some