> From: p...@cmicdo.com
> Sent: 22 November 2022 15:30
> To: Alastair McKinley ;
> pgsql-general@lists.postgresql.org ; Erik
> Wienhold
> Subject: Re: copying json data and backslashes
>
> >
> > On Tuesday, November 22, 2022 at 10:16:1
Hi all,
I have come across this apparently common issue COPY-ing json and wondering if
there is potentially a better solution.
I am copying data into a jsonb column originating from a 3rd party API. The
data may have literal \r,\t,\n and also double backslashes.
I discovered that I can cast t
>
> From: Adrian Klaver
> Sent: 29 May 2022 21:47To: Alastair McKinley
> ; pgsql-general@lists.postgresql.org
> Subject: Re: psql 15beta1 does not print
> notices on the console until transaction completes
>
> On 5/29/22 13:11, Alastair McKinley wrote:
> > Hi all
Hi all,
I notice this change in behaviour with psql in 15beta1 when testing an existing
codebase.
I didn't see any mention of this change in the release notes and it surprised
me.
Using this test function:
create or replace function test_notice() returns void as
$$
begin
> From: Tom Lane
> Sent: 29 May 2022 18:43
> To: Alastair McKinley
> Cc: Andrew Dunstan ; pgsql-general@lists.postgresql.org
>
> Subject: Re: Function definition regression in 15beta1 when specific
> parameter name (string) is used
>
> Alastair McKinley writes:
Hi all,
I was testing an existing codebase with 15beta1 and ran into this issue.
I reduced the test case to an example with works in 14.3, and fails in 15beta1.
The following function definition fails in 15beta1 (ok in 14.3):
create or replace function regexp_match_test(string text,pattern
Hi all,
I recently came across a subtle performance issue when working with some
compiled UDFs to optimise a workload.
These UDFs accidently had "set search_path = 'public'" in their definition.
When profiling with perf, I noticed a large amount of elapsed time spent in the
function
void
Hello everyone,
I am working with jsonpaths heavily and was wondering if there is any method
for debugging a jsonpath expression in a similar method to ts_debug() for text
search?
Essentially what I would like to do is debug the splitting of a path into
tokens or logical components using the s
$$
select * from tableX where column @@ $1 and
has_table_read_permission(tableX.column);
$$ language sql security definer;
Best regards,
Alastair
From: Alastair McKinley
Sent: 31 March 2020 22:09
To: Tom Lane
Cc: pgsql-general@lists.postgresql.org
Subject
From: Tom Lane
Sent: 31 March 2020 20:18
To: Alastair McKinley
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Index selection issues with RLS using expressions
Alastair McKinley writes:
> I am running in to an issue with RLS and index selection in my queries. I
> created
Hi all,
I am running in to an issue with RLS and index selection in my queries. I
created a toy example to try to illustrate the issue below. Postgres version
is PostgreSQL 12.2 (Debian 12.2-2.pgdg100+1) on x86_64-pc-linux-gnu, compiled
by gcc (Debian 8.3.0-6) 8.3.0, 64-bit.
Is there some su
Sent: 21 March 2020 20:50
To: Alastair McKinley
Cc: Adrian Klaver ;
pgsql-general@lists.postgresql.org
Subject: Re: Explain says 8 workers planned, only 1 executed
Unfortunately, return query will never use parallel workers. See:
https://stackoverflow.com/q/58079898/895640 and
https
Sent: 21 March 2020 17:38
To: Alastair McKinley ;
pgsql-general@lists.postgresql.org
Subject: Re: Explain says 8 workers planned, only 1 executed
On 3/21/20 10:25 AM, Alastair McKinley wrote:
> Hi all,
>
> I have a long running query that I have tweaked along with config (e.g.
> min_
Hi all,
I have a long running query that I have tweaked along with config (e.g.
min_parallel_table_scan_size) to execute nicely and very fast in parallel which
works as expected executed directly from psql client. The query is then
embedded in a psql function like "return query select * from
using my jsonb indexes, even though it seems
like a related issue.
Is there another effect potentially going on here or incomplete debugging
messages?
Best regards,
Alastair
From: Tom Lane
Sent: 04 March 2020 04:22
To: Alastair McKinley
Cc: pgsql-general@lists
testing where (jsonb_select(data,'{value}')) =
to_jsonb(10) and type_id = 10 and latest is true;
Is this an approach that could fundamentally work?
Best regards,
Alastair
From: Tom Lane
Sent: 04 March 2020 00:04
To: Alastair McKinley
Cc: pgsql-general@lists.
Hi all,
I have recently encountered a strange poor query plan choice after implementing
RLS.
My table has a number of partial indexes on a jsonb column and the query went
from low number of milliseconds to several seconds as the planner chose a
different index.
Simply stated, in the jsonb col
Hi all,
I have recently discovered an unexpected difference in behaviour using
websearch_to_tsquery() with quoted strings containing ampersands.
These two queries are equivalent without surrounding double quotes
select websearch_to_tsquery('something and another') =
websearch_to_tsquery('somet
Sent: 10 October 2019 14:35
To: Alastair McKinley
Cc: pgsql-general@lists.postgresql.org ;
teo...@sigaev.ru
Subject: Re: websearch_to_tsquery() and apostrophe inside double quotes
Alastair McKinley writes:
> I am a little confused about what us being generated by
> websearch_to_tsquery(
Hi all,
I am a little confused about what us being generated by websearch_to_tsquery()
in the case of an apostrophe inside double quotes.
Here is an example of searching for a name containing an apostrophe.
The following works as expected:
select to_tsvector('peter o''toole') @@ websearch_to_t
Hi all,
I noticed this strange behaviour whilst trying to write a function for Postgres
11.5 (PostgreSQL 11.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
20150623 (Red Hat 4.8.5-36), 64-bit) and reduced it to this minimal example.
Using a function parameter in the window frame definiti
fused me.
Best regards,
Alastair
From: Andrew Gierth
Sent: 19 May 2019 03:48
To: Alastair McKinley
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Strange performance degregation in sql function (PG11.1)
>>>>> "Alastair" == Alast
Hi all,
I recently experienced a performance degradation in an operational system that
I can't explain. I had a function wrapper for a aggregate query that was
performing well using the expected indexes with the approximate structure as
shown below.
create or replace function example_function
ynamically
generated query approach in the interim.
Best regards,
Alastair
Get Outlook for Android<https://aka.ms/ghei36>
From: David Rowley
Sent: Sunday, 14 April, 14:57
Subject: Re: Planner can't seem to use partial function indexes with parameter
from join
To: Alastair McKin
Hello all,
I am having quite an interesting problem trying to get the planner to use my
indexes as intended in my setup.
I am using partial functional indexes that have a different function parameter
based on the record type.
A dynamically generated query using unions that are more explicit abo
Hi,
I have a table that stores a location identifier per person which will be
appended to many times.
However, for many queries in this system we only need to know the most recent
location per person, which is limited to about 1000 records.
Is the following trigger/index strategy a reasonabl
26 matches
Mail list logo
