Re: Select .... where id not in (....) returns 0 incorrectly

On Monday, April 4, 2022 10:47:51 PM CEST David Rowley wrote: > On Tue, 5 Apr 2022 at 01:21, J. Roeleveld wrote: > > Personally, I think NULL should be treated as a seperate value and not > > lead to strange behaviour. > > I think the rationale behind IN and NOT IN are that c IN(1,2,3) is > equiv

JSON question

Hi All I'm experimenting with PostgreSQL's JSON features and have a question. Say we have the following JSON (which will be stored in a table named 'resources' - in its 'schema' column ... { "name": { "type": "string", "label": "Name", "accessor": "name", "is_association": false }, "books": { "

Where is the best place to read for the paper around database?

Hello everyone, I am a student who are interest in database,can anyone recommend a website where can read and download the papers around database? Thanks in advance! Best wishes, Wen Yi.

Load Balancer with PostgreSQL

Hi guys. I was wondering if you guys could share what are you using for Load Balancing with PG? It has to be smart enough to understand when a server is too busy to get a request, so it sends it to the other slaves. I understand there is PGPool available for this, is there any solution tool you

Re: Select .... where id not in (....) returns 0 incorrectly

On Tue, 5 Apr 2022 at 01:21, J. Roeleveld wrote: > Personally, I think NULL should be treated as a seperate value and not lead to > strange behaviour. I think the rationale behind IN and NOT IN are that c IN(1,2,3) is equivalent of writing: c = 1 OR c = 2 OR c = 3, whereas NOT IN(1,2,3) would be

Re: gist segmentation fault

Thanks, Том Lane. We will wait for the release. Sent with ProtonMail secure email. --- Original Message --- On Monday, April 4th, 2022 at 17:12, Tom Lane wrote: > Arup Nanda arup.na...@protonmail.com writes: > > > We're preparing to upgrade postgresql from 12.8 to 14.2 but still we

Re: Transaction and SQL errors

David, Personally, I can see where it has significant value for psql in interactive mode because people make typos. Application code doesn't. That removes a whole class of problems where the feature provides benefit. Sure, application code must not have typos, but I prefer to let the DB engi

Re: Trigger functions and FDW tables

Thanks for confirming my suspicions, I'm working on a plan B to deal with this as best I can. -- Mike Nolan On Mon, Apr 4, 2022 at 9:23 AM Laurenz Albe wrote: > On Mon, 2022-04-04 at 09:18 -0500, Michael Nolan wrote: > > Is it not recommended to use a FDW table in a trigger function? > > Yes, I

Re: how to find out field size?

On 4/4/22 08:28, Shaozhong SHI wrote: Postgres documentation says:  maximum field size is 1 GB. PostgreSQL: Documentation: 12: Appendix K. PostgreSQL Limits So, how to find out the size of column to see how far it from the limit? Documentation

how to find out field size?

Postgres documentation says: maximum field size is 1 GB. PostgreSQL: Documentation: 12: Appendix K. PostgreSQL Limits So, how to find out the size of column to see how far it from the limit? Regards, David

Re: Trigger functions and FDW tables

On Mon, 2022-04-04 at 09:18 -0500, Michael Nolan wrote: > Is it not recommended to use a FDW table in a trigger function? Yes, I would say that is not recommended. While a lag of a few seconds, like you describe, should not be the normal case (you should investigate that), it is hard to exclude i

Trigger functions and FDW tables

Background: About two years ago the membership system I developed for a client was moved from our in-house postgresql app to civi-CRM, which uses MySQL. (I'm semi-retired, the move to civi-CRM is part of a long term technology change.) We have a FDW that simulates the old membership table that c

Re: gist segmentation fault

Arup Nanda writes: > We're preparing to upgrade postgresql from 12.8 to 14.2 but still we have > some bugs and issues. Most of them are our fault, but now we have problem > wedon't know how to play with it. > [ crash in copy_ltree for an upgraded GIST index ] I think this is the same problem al

Re: Transaction and SQL errors

On Mon, Apr 4, 2022 at 5:41 AM Sebastien Flaesch wrote: > > I know savepoints are supported by PostgreSQL, but what about > existing/legacy code that has tons of lines that rely on the behavior of > other DB engines? > > Would you ask programmers to put a savepoint / release savepoint around > ea

Re: Transaction and SQL errors

On Monday, April 4, 2022 1:44:40 PM CEST David Rowley wrote: > On Mon, 4 Apr 2022 at 23:33, Sebastien Flaesch > > wrote: > > Any arguments I could give to programmers? Maybe: > > "You better replay all SQL of the whole transaction... (and make them as > > short as possible!)" ? > > There are S

Re: Select .... where id not in (....) returns 0 incorrectly

On Monday, April 4, 2022 2:50:44 PM CEST Jeremy Smith wrote: > > Doesn't this usually happen if q.id contains NULL. That is as per ANSI > > standard. > > Yes, there's a good description of this here: > https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_NOT_IN > > It would be better to us

Re: Select .... where id not in (....) returns 0 incorrectly

> > Doesn't this usually happen if q.id contains NULL. That is as per ANSI > standard. > > Yes, there's a good description of this here: https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_NOT_IN It would be better to use NOT EXISTS: select count(*) from snapshotlist where NOT EXISTS (SELE

Re: Select .... where id not in (....) returns 0 incorrectly

select count(1) from snapshotlist where id not in (select id from q); count --- 0 (1 row) Doesn't this usually happen if q.id contains NULL. That is as per ANSI standard.

Re: Transaction and SQL errors

Hi David, I know savepoints are supported by PostgreSQL, but what about existing/legacy code that has tons of lines that rely on the behavior of other DB engines? Would you ask programmers to put a savepoint / release savepoint around each SQL statement inside a TX? Seb ___

Re: Transaction and SQL errors

Hi Karsten, No... see the doc: https://www.postgresql.org/docs/14/app-psql.html ON_ERROR_ROLLBACK When set to on, if a statement in a transaction block generates an error, the error is ignored and the transaction continues. Seb

Select .... where id not in (....) returns 0 incorrectly

Hi all, While trying to write some queries to clean up records I no longer need, I come up with a very strange situation where numbers literally don't add up as expected. If anyone can help me trace where this strange behaviour is coming from and how to resolve it, please let me know. In case

Re: Transaction and SQL errors

On Mon, 4 Apr 2022 at 23:33, Sebastien Flaesch wrote: > Any arguments I could give to programmers? Maybe: > "You better replay all SQL of the whole transaction... (and make them as > short as possible!)" > ? There are SAVEPOINTs [1]. David [1] https://www.postgresql.org/docs/current/sql-save

Re: Transaction and SQL errors

Am Mon, Apr 04, 2022 at 11:33:14AM + schrieb Sebastien Flaesch: > Is there any plan to have an equivalent of psql's > > set ON_ERROR_ROLLBACK on > > in the DB engine? That is already what happens. SQL fails, transaction rolls back. Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D

Transaction and SQL errors

Hello! Is there any plan to have an equivalent of psql's set ON_ERROR_ROLLBACK on in the DB engine? Most other DB engines have this behavior built-in, that makes migration to PostgreSQL more difficult when there are complex long transactions in the application code. I do often suggest progra

gist segmentation fault

Hello, We're preparing to upgrade postgresql from 12.8 to 14.2 but still we have some bugs and issues. Most of them are our fault, but now we have problem wedon't know how to play with it. -- dmesg message [Sat Apr  2 08:26:24 2022] postmaster[1939250]: segfault at 7f2998f2c000 ip 7f4a56c