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 networ

Re: pg_trgm vs. Solr ngram

Chris writes: > 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.? See KEEPONLYALNUM in pg_trgm/trgm.h ... Now, using a custom-modified

Re: pg_trgm vs. Solr ngram

On Fri, 2023-02-10 at 03:20 +0100, Chris wrote: > 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 a

pg_trgm vs. Solr ngram

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 wi

Re: ERROR: posting list tuple with 2 items cannot be split at offset 17

On Thu, Feb 9, 2023 at 3:55 PM Paul McGarry wrote: > Will the amcheck reliably identify all issues that may arise from a collation > change? Theoretically it might not. In practice I'd be very surprised if it ever failed to detect such an inconsistency. If you want to be extra careful, and can a

Re: Quoting issue from ODBC

On Thu, Feb 9, 2023 at 5:10 PM Adrian Klaver wrote: > On 2/9/23 14:43, Brad White wrote: > > On Tue, Feb 7, 2023 at 10:20 PM Brad White > > wrote: > > > > On 2/7/2023 6:19 PM, Adrian Klaver wrote: > >> On 2/7/23 16:10, Brad White wrote: > >>> Front end: Acc

Re: ERROR: posting list tuple with 2 items cannot be split at offset 17

Hi Peter, Thanks for your input, I'm feeling more comfortable that I correctly understand the problem now and it's "just" a collation related issue. > I recommend running amcheck on all indexes, or at least all > possibly-affected text indexes. > > Will the amcheck reliably identify all issues t

Re: Quoting issue from ODBC

On 2/9/23 14:43, Brad White wrote: On Tue, Feb 7, 2023 at 10:20 PM Brad White > wrote: On 2/7/2023 6:19 PM, Adrian Klaver wrote: On 2/7/23 16:10, Brad White wrote: Front end: Access 365 Back end: Postgres 9.4 (I know, we are in the process of upgr

Re: Quoting issue from ODBC

>> Where these preexisting queries or where they created today? > These queries are decades old but I don't view this log file very often, so I don't know how long. > I'll review when I get back on site Thursday and see if I can find any users that are not getting the error or when it started. G

Re: Quoting issue from ODBC

On Tue, Feb 7, 2023 at 10:20 PM Brad White wrote: > On 2/7/2023 6:19 PM, Adrian Klaver wrote: > > On 2/7/23 16:10, Brad White wrote: > > Front end: Access 365 > Back end: Postgres 9.4 > (I know, we are in the process of upgrading) > > I'm getting some cases where the SQL sent from MS-Access is fa

Re: Concurrency issue with DROP INDEX CONCURRENTLY

On Thu, 2023-02-09 at 10:45 -0500, Kiriakos Georgiou wrote: > I have an interesting concurrency issue with DROP INDEX CONCURRENTLY that I > can summarize with this test scenario: > > /**/ > > — suppose we have this table and index > create table tes

Re: Concurrency issue with DROP INDEX CONCURRENTLY

My apologies - there is no issue with DROP INDEX CONCURRENTLY. It’s just brain fade on my part (I dropped the existing index before creating the new UNIQUE index, causing TPS on this table to go to zero *facepalm*). Regards, Kiriakos > On Feb 9, 2023, at 10:45 AM, Kiriakos Georgiou > wrote:

delta.io foreign data wrapper

Hi, Postgres already has a foreign data wrapper (FDW) for external tables stored in parquet format on S3 https://www.postgresql.org/about/news/parquet-s3-fdw-030-released-2474/. Delta https://delta.io/ is an evolution of parquet to a more cloud-friendly/ACID table format also allowing for mutatio

Re: Using PostgreSQL for service discovery and health-check

On 2/9/23 09:40, Dominique Devienne wrote: On Thu, Feb 9, 2023 at 5:51 PM Adrian Klaver > wrote: On 2/9/23 08:16, Dominique Devienne wrote: > On Thu, Feb 9, 2023 at 5:05 PM Adrian Klaver mailto:adrian.kla...@aklaver.com> The flip side of that is

Re: Sequence vs UUID

On Wed, Feb 8, 2023 at 5:33 AM Peter J. Holzer wrote: > On 2023-02-08 14:48:03 +0530, veem v wrote: > > So wanted to know from experts here, is there really exists any scenario > in > > which UUID really cant be avoided? > > Probably not. The question is usually not "is this possible" but "does >

Re: Using PostgreSQL for service discovery and health-check

On 2/9/23 09:40, Dominique Devienne wrote: On Thu, Feb 9, 2023 at 5:51 PM Adrian Klaver > wrote: On 2/9/23 08:16, Dominique Devienne wrote: > On Thu, Feb 9, 2023 at 5:05 PM Adrian Klaver mailto:adrian.kla...@aklaver.com> The flip side of that is

Re: Using PostgreSQL for service discovery and health-check

On Thu, Feb 9, 2023 at 5:51 PM Adrian Klaver wrote: > On 2/9/23 08:16, Dominique Devienne wrote: > > On Thu, Feb 9, 2023 at 5:05 PM Adrian Klaver The flip side of that is that with known ports it would it easier to > have a process on the Postgres machine or in the database that checks > the por

Re: WHERE col = ANY($1) extended to 2 or more columns?

On Thu, Feb 9, 2023 at 5:37 PM David G. Johnston wrote: > On Thu, Feb 9, 2023 at 9:28 AM Alban Hertroys wrote: > >> > On 9 Feb 2023, at 16:41, Dominique Devienne >> wrote: >> > Now we'd like to do the same for composite keys, and I don't know how >> to do that. >> >> This works: >> => select (1

Re: ERROR: posting list tuple with 2 items cannot be split at offset 17

On Wed, Feb 8, 2023 at 11:54 PM Paul McGarry wrote: > But if it is the problem, why did the update start working after I recreated > the other index? There is no reason why reindexing another index ought to have had that effect. The likely explanation is that subsequent updates used a successor

Re: ERROR: posting list tuple with 2 items cannot be split at offset 17

On 2/8/23 23:53, Paul McGarry wrote: I have three databases, two of databases where I am experiencing the issue below. The first database was created from a dump in Feb 2022 (a few weeks after the time period for which I seem to have problematic indexes, maybe). The second database was then cl

Re: Using PostgreSQL for service discovery and health-check

On 2/9/23 08:16, Dominique Devienne wrote: On Thu, Feb 9, 2023 at 5:05 PM Adrian Klaver > wrote: Is there more then one server providing the same service? Yes. That was my PS: basically. The client can connect to any one, randomly. We need at least one

Re: WHERE col = ANY($1) extended to 2 or more columns?

On Thu, Feb 9, 2023 at 9:28 AM Alban Hertroys wrote: > > > On 9 Feb 2023, at 16:41, Dominique Devienne wrote: > > > > Hi. We are implementing an API which takes a list of row keys, and must > return info about those rows. To implement that efficiently, in as few > round-trips as possible, we bin

Re: WHERE col = ANY($1) extended to 2 or more columns?

> On 9 Feb 2023, at 16:41, Dominique Devienne wrote: > > Hi. We are implementing an API which takes a list of row keys, and must > return info about those rows. To implement that efficiently, in as few > round-trips as possible, we bind a (binary) array of keys (ints, uuids, or > strings) an

Re: WHERE col = ANY($1) extended to 2 or more columns?

On Thu, Feb 9, 2023 at 9:09 AM Dominique Devienne wrote: > On Thu, Feb 9, 2023 at 5:03 PM Dominique Devienne > wrote: > >> On Thu, Feb 9, 2023 at 4:51 PM David G. Johnston < >> david.g.johns...@gmail.com> wrote: >> >>> On Thu, Feb 9, 2023 at 8:41 AM Dominique Devienne >>> wrote: >>> Now we

Re: Using PostgreSQL for service discovery and health-check

On Thu, Feb 9, 2023 at 5:05 PM Adrian Klaver wrote: > On 2/9/23 07:30, Dominique Devienne wrote: > > In a normally 2-tier system, where "desktop" native apps connect > > directly to PostgreSQL to access data, some things must be mediated via > > a middle-tier service (i.e. 3-tier architecture). T

Re: Concurrency issue with DROP INDEX CONCURRENTLY

On 2/9/23 07:45, Kiriakos Georgiou wrote: Hello, I have an interesting concurrency issue with DROP INDEX CONCURRENTLY that I can summarize with this test scenario: /**/ — suppose we have this table and index create table test(x int); create inde

Re: WHERE col = ANY($1) extended to 2 or more columns?

On Thu, Feb 9, 2023 at 5:03 PM Dominique Devienne wrote: > On Thu, Feb 9, 2023 at 4:51 PM David G. Johnston < > david.g.johns...@gmail.com> wrote: > >> On Thu, Feb 9, 2023 at 8:41 AM Dominique Devienne >> wrote: >> >>> Now we'd like to do the same for composite keys, and I don't know how to >>>

Re: Using PostgreSQL for service discovery and health-check

On 2/9/23 07:30, Dominique Devienne wrote: Hi. I'm requesting advice on something I'm about to start on. In a normally 2-tier system, where "desktop" native apps connect directly to PostgreSQL to access data, some things must be mediated via a middle-tier service (i.e. 3-tier architecture). Th

Re: WHERE col = ANY($1) extended to 2 or more columns?

On Thu, Feb 9, 2023 at 4:51 PM David G. Johnston wrote: > On Thu, Feb 9, 2023 at 8:41 AM Dominique Devienne > wrote: > >> Now we'd like to do the same for composite keys, and I don't know how to >> do that. >> > > An array-of-composites is simply: > SELECT ARRAY[ ROW(1, 'one'), ROW(2, 'two') ]::

Re: Using PostgreSQL for service discovery and health-check

On Thu, Feb 9, 2023 at 4:46 PM David G. Johnston wrote: > On Thu, Feb 9, 2023 at 8:30 AM Dominique Devienne > wrote: > >> That's basically my plan. Now come the questions: >> 1) will updating a row every second (for example) create issues? >> 2) if yes to #1 above, what would be good mitigation

Re: WHERE col = ANY($1) extended to 2 or more columns?

On Thu, Feb 9, 2023 at 8:41 AM Dominique Devienne wrote: > Hi. We are implementing an API which takes a list of row keys, and must > return info about those rows. To implement that efficiently, in as few > round-trips as possible, we bind a (binary) array of keys (ints, uuids, or > strings) and t

Re: Using PostgreSQL for service discovery and health-check

On Thu, Feb 9, 2023 at 8:30 AM Dominique Devienne wrote: > > That's basically my plan. Now come the questions: > 1) will updating a row every second (for example) create issues? > 2) if yes to #1 above, what would be good mitigation tactics? Use > different table for service vs heartbeat? Special

Concurrency issue with DROP INDEX CONCURRENTLY

Hello, I have an interesting concurrency issue with DROP INDEX CONCURRENTLY that I can summarize with this test scenario: /**/ — suppose we have this table and index create table test(x int); create index idx1 on test(x); — now suppose with the d

Re: PostgreSQL

On Thu, 2023-02-09 at 09:54 +0100, Joseph Kennedy wrote: > As I wtote, I would like restrict access to sensitive or restricted > information > for some users (eg. hide data of one or more clients for some database users). > > My question is: do you know some other solution to do that ?  It is ea

WHERE col = ANY($1) extended to 2 or more columns?

Hi. We are implementing an API which takes a list of row keys, and must return info about those rows. To implement that efficiently, in as few round-trips as possible, we bind a (binary) array of keys (ints, uuids, or strings) and that works great, but only if the key is a scalar one. Now we'd lik

Using PostgreSQL for service discovery and health-check

Hi. I'm requesting advice on something I'm about to start on. In a normally 2-tier system, where "desktop" native apps connect directly to PostgreSQL to access data, some things must be mediated via a middle-tier service (i.e. 3-tier architecture). That service is HTTP based thus clients (the "des

Re: Question regarding UTF-8 data and "C" collation on definition of field of table

On Sun, 5 Feb 2023 17:14:44 -0800 Peter Geoghegan wrote: ... > The OP should see the Postgres ICU docs for hints on how to use these > facilities to make a custom collation that matches whatever their > requirements are: > > https://www.postgresql.org/docs/current/collation.html#COLLATION-MANAGI

Re: PostgreSQL

As I wtote, I would like restrict access to sensitive or restricted information for some users (eg. hide data of one or more clients for some database users). My question is: do you know some other solution to do that ? Some 3rd party solution eg. similar to pgpool or something else ? Maybe RL