Re: pg_trgm vs. Solr ngram
Le ven. 10 févr. 2023, 03:20, Chris a écrit : > Hello list > > I'm pondering migrating an FTS application from Solr to Postgres, just > because we use Postgres for everything else. > > The application is basically fgrep with a web frontend. However the > indexed documents are very computer network specific and contain a lot > of hyphenated hostnames with dot-separated domains, as well as IPv4 and > IPv6 addresses. In Solr I was using ngrams and customized the > TokenizerFactories until more or less only whitespace was as separator, > while [.:-_\d] remains part of the ngrams. This allows to search for > ".12.255/32" or "xzy-eth5.example.org" without any false positives. > > It looks like a straight conversion of this method is not possible since > the tokenization in pg_trgm is not configurable afaict. Is there some > other good method to search for a random substring including all the > punctuation using an index? Or a pg_trgm-style module that is more > flexible like the Solr/Lucene variant? > > Or maybe hacking my own pg_trgm wouldn't be so hard and could be fun, do > I pretty much just need to change the emitted tokens or will this lead > to significant complications in the operators, indexes etc.? > > thanks for any hints & cheers > Christian > In Solr you used FTS so I suggest that you do the same in Postgres and look at the full text search functions. You can create a tsvector yourself in many different ways or use one of the provided functions. So you could add complete IP adresses to your index and then search for them using something like phrase search. You can also create text search configurations or just use the "simple" one if you just need something like fgrep. Of course, the end result will be more like Solr and less like fgrep. https://www.postgresql.org/docs/current/textsearch.html
Re: Primary keys and composite unique keys(basic question)
On Fri, Apr 2, 2021 at 8:57 AM Hemil Ruparel wrote: > I used uuid4 for customer ids because i needed to interface with payment > providers. Is that wrong? All other places except transaction ids, i have > used serial ints > > On Fri 2 Apr, 2021, 8:56 AM Rob Sargent, wrote: > >> On 4/1/21 8:28 PM, Merlin Moncure wrote: >> > >> > This is one of the great debates in computer science and it is not >> > settled. There are various tradeoffs around using a composite key >> > derived from the data (aka natural key) vs generated identifiers. It's >> > a complex topic with many facets: performance, organization, >> > validation, and correctness are all relevant considerations. I would >> > never use UUIDS for keys though. >> > >> > merlin >> > >> > >> And, pray tell, for what exactly would you use universally unique >> identifiers. >> > ULID perform better than UUID with btree indexes. The first part is based on a timestamp, so if you don't mind exposing this information, they are a better choice. There is an implementation for postgres here: https://github.com/geckoboard/pgulid Maybe there are others.
Re: ERROR wrong varnullingrels (b 3) (expected (b)) with LEFT JOIN LATERAL and CTE
On Thu, Nov 28, 2024 at 11:35 PM Tom Lane wrote: > I wrote: > > Thanks for the test case! A quick "git bisect" says I broke it at > > cb8e50a4a09fe541e32cd54ea90a97f2924121a1 is the first bad commit > > commit cb8e50a4a09fe541e32cd54ea90a97f2924121a1 > > Author: Tom Lane > > Date: Fri Aug 30 12:42:12 2024 -0400 > > Avoid inserting PlaceHolderVars in cases where pre-v16 PG did not. > > Apparently that change was less safe than I thought. Looking ... > > Fixed here: > > > https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=85990e2fd5610576635c65db9292297b1730c947 > > Thanks for the report! > Excellent ! Thank you very much !!! Bertrand
ERROR wrong varnullingrels (b 3) (expected (b)) with LEFT JOIN LATERAL and CTE
Hello, My query has been working fine in 16.4 and before, but not anymore in 16.5, 16.6 and 17. It is a query with multiple CTE, some of which are using values of the previous ones, and the end of the query sort of make a mix of found values with aggregation from a LATERAL JOIN. Something like this : WITH taxrules AS (...) , defaultprices AS (...) , baseprices AS (...) , currentprices AS (...) , discountedprices AS (...) SELECT discountedprices.variants_id, discountedprices.products_id, sum(COALESCE(taxes.tax_price, 0)) FROM discountedprices LEFT JOIN LATERAL ( SELECT products_taxrules.products_id, round(discountedprices.price * taxrules.rate_percent, 4) - discountedprices.price AS tax_price FROM taxrules INNER JOIN products_taxrules ON taxrules.id = products_taxrules.taxrules_id ) AS taxes ON taxes.products_id = discountedprices.products_id WHERE discountedprices.variants_id = ANY(ARRAY[12345]) GROUP BY discountedprices.variants_id, discountedprices.products_id, discountedprices.price ; I get this error in PG16.5, 16.6 and 17 : wrong varnullingrels (b 3) (expected (b)) for Var 1/19 The query works again if I add a COALESCE on the line in the LATERAL JOIN query like this : round(discountedprices.price * COALESCE(taxrules.rate_percent, 0), 4) - discountedprices.price AS tax_price The query also works if I use an INNER JOIN LATERAL instead of a LEFT JOIN LATERAL. But the taxrules.rate_percent cannot be null anyway. It comes from the result of this calculation : (1 + t.rate_percent / 100) AS rate_percent in the taxrules CTE. So now I wonder if my sql was wrong and should be fixed or if since 16.5 Postgresql has a bug in the way it deals with values in a LEFT JOIN LATERAL ? Thanks for your help, Bertrand Mansion Mamasam
Re: ERROR wrong varnullingrels (b 3) (expected (b)) with LEFT JOIN LATERAL and CTE
On Thu, Nov 28, 2024 at 5:15 PM Tom Lane wrote:
> Bertrand Mamasam writes:
> > I get this error in PG16.5, 16.6 and 17 :
> > wrong varnullingrels (b 3) (expected (b)) for Var 1/19
>
> Please provide a self-contained test case. A fragmentary query
> with no underlying tables is useless for investigation.
>
> https://wiki.postgresql.org/wiki/Guide_to_reporting_problems
>
Ok thank you, here is a short version of the query that works before 16.5
but not in 16.5+ anymore.
```sql
CREATE TABLE testrules (
id text PRIMARY KEY,
rate_percent numeric(10,3) NOT NULL DEFAULT 0.000
);
INSERT INTO "testrules"("id","rate_percent") VALUES ('EU-FR-20', 20);
CREATE TABLE products_testrules (
products_id bigint,
testrules_id text REFERENCES testrules(id) ON DELETE CASCADE ON UPDATE
CASCADE,
CONSTRAINT products_testrules_pkey PRIMARY KEY (products_id,
testrules_id)
);
INSERT INTO "public"."products_testrules"("products_id","testrules_id")
VALUES (52, 'EU-FR-20');
CREATE TABLE testvariants (
id BIGSERIAL PRIMARY KEY,
products_id bigint
);
INSERT INTO "public"."testvariants"("id","products_id") VALUES (20, 52);
CREATE TABLE testprices (
id BIGSERIAL PRIMARY KEY,
variants_id bigint NOT NULL,
price numeric(10,4) NOT NULL
);
INSERT INTO "public"."testprices"("id","variants_id","price")
VALUES
(645046,20,120.833);
WITH tr AS (
SELECT
t.id,
(1 + t.rate_percent / 100) AS rate_percent
FROM testrules t
)
, vd AS (
SELECT
v.id AS variants_id,
v.products_id,
p.price
FROM testvariants v
JOIN testprices p ON p.variants_id = v.id
WHERE
v.id = 20
)
SELECT
vd.variants_id,
vd.products_id,
vd.price + (sum(COALESCE(taxes.tax_price, 0) )) as price_tax,
min(COALESCE(taxes.tax_price, 0))
FROM
vd
LEFT JOIN LATERAL (
SELECT
pt.products_id,
tr.id,
round(vd.price * tr.rate_percent, 4) - vd.price AS tax_price
FROM tr
INNER JOIN products_testrules pt ON tr.id = pt.testrules_id
) AS taxes ON taxes.products_id = vd.products_id
GROUP BY
vd.variants_id,
vd.products_id,
vd.price
;
```
In 16.4, it returns :
| 20 | 52 | 144,9996 | 24,1666 |
In 16.6, it throws :
ERROR: wrong varnullingrels (b 3) (expected (b)) for Var 5/3
Thanks for your help.
Bertrand
