Re: PostgreSQL 15.10 update corrective action for ATTACH PARTITION/DETACH PARTITION

[Paul Foerster]

Hi Tom, hi Alvaro,

> On 27 Nov 2024, at 19:52, Tom Lane  wrote:
> 
> Okay, so I was able to reproduce this from scratch on HEAD:

great, thanks.

> I doubt that there's anything actually wrong with the catalog state at
> this point (perhaps Alvaro would confirm that).  That leads to the
> conclusion that what's wrong is the release notes' query for fingering
> broken constraints, and it needs some additional test to avoid
> complaining about (I suspect) self-reference cases.

In the meantime, I updated the whole company. The one test database actually 
was the only database that this was returned. I found no other occurrences.

As I understand it, the worst thing that could happen is that one or more rows 
end up in a detached partition table which should actually be in another 
partition, right? Since there were no rows, no harm could have been done. Also, 
since this is a self reference, the wrong table is also the right one.

Again, thanks very much for clarifying this.

Cheers
Paul

Re: ERROR wrong varnullingrels (b 3) (expected (b)) with LEFT JOIN LATERAL and CTE

[Tom Lane]

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!

regards, tom lane

Find out the version of the server

[Igor Korot]

Hi, ALL,

In April of 2017 I got the source code of the Postgresql, build the libpq
and then deleted everything else.

I was using MSVC 3010 at the time.

Now I moved to MSVC 2017 but unfortunately I can't reuse the library built,
since MSVC changed one of its headers.

Is it possible to find what version of the server was used for the build?

Im looking at the Windows Explorer timestamp, which gives me 4 September
2017.

Thank you.

P.s. please forgive for any typos as it's been written from the Android
phone.

Re: ERROR wrong varnullingrels (b 3) (expected (b)) with LEFT JOIN LATERAL and CTE

[Bertrand Mamasam]

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

[Bertrand Mamasam]

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

[Bertrand Mamasam]

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

Delete Query slowdown Slave database.

[Sreejith P]

Hello,

My doubt here is, in a master - slave (streaming replication) set up, when I 
run a long running delete query for deleting 80 million records, . All queries 
in slave started running 2- 5 mins all of a sudden. When I check SQL analyse, 
most of the queries, planner used to choose seq scan, instead of seq scan. 

What could be the possibility?

How delete query running in Master affect secondary(Slave) DB.

Is it related to shared memory ?


NB: Problem solved after restarting slave servers. 



Thanks
Sreejith


-- 




 

*Solutions for Care Anywhere*
*dWise HealthCare IT Solutions Pvt. 
Ltd.* | www.lifetrenz.com 
*Disclaimer*:
 The 
information and attachments contained in this email are intended 
for 
exclusive use of the addressee(s) and may contain confidential or 
privileged information. If you are not the intended recipient, please 
notify the sender immediately and destroy all copies of this message and
 
any attachments. The views expressed in this email are, unless 
otherwise 
stated, those of the author and not those of dWise HealthCare IT Solutions 
or its management.

Re: ERROR wrong varnullingrels (b 3) (expected (b)) with LEFT JOIN LATERAL and CTE

[Tom Lane]

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

regards, tom lane

Re: ERROR wrong varnullingrels (b 3) (expected (b)) with LEFT JOIN LATERAL and CTE

[Tom Lane]

Bertrand Mamasam  writes:
> Ok thank you, here is a short version of the query that works before 16.5
> but not in 16.5+ anymore.

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 ...

regards, tom lane