I'm researching strategies for incrementally updating aggregate/rollup
tables. The problem is how to do so without double-counting changes, and
not skipping changes. I know enough about concurrency issues to ask the
question, but do *not* know enough about the features and details of
Postgres' conc
Tatsuo,
Thank you for your response, I have followed the discussion on Hackers with
interest. I hope that your efforts are a great success! In my case, I need
to find a solution available in shipping versions of Postgres. But, since
you've joined in, I'm curious: What is the advantage of a materia
Thanks Steven, nice suggestions. I should have mentioned that the
deployment setup is on RDS on PG 11.x, which rules out those extensions.
I've looked at TimescaleDB several times, and it looks pretty great.
I've now read through some of the archives from years back when
pg_xact_commit_timestamp w
Your tributaries and fish master tables make sense. If I read your code
right, you're grouping by too many columns. I flattened the data into a
survey table for this simple example:
select tributary,
common_name,
scientific_name,
sum(count_value) as fish_seen,
count(cou
I see that you've already been pointed at citext, but I don't think a CHECK
constraint has been mentioned. In case it hasn't, what about something like
this?
ADD CONSTRAINT check_activity_status
CHECK (activity_status = 'ACTIVE' OR activity_status = 'INACTIVE');
I'm kind of allergic to ENU
citext is an extension, so you have to install it:
CREATE EXTENSION citext;
That's the simplest form. you can install it into a specific schema, test
for existence, etc. Check out the CREATE EXTENSION docs here:
https://www.postgresql.org/docs/current/sql-createextension.html
Not sure about best practices, but what I'm going is like this:
* Create a schema named extensions.
* Install extensions in this special schema only. I don't put anything else
in there.
* Put the extensions schema early (left) in the search_path for each role.
* Grant execute access permissivel
For clarification, what do you mean by "phone number"? I'm not being dense,
I'm wondering if you're looking for a type that handles only numbers from
one country, or that can deal with the rules for a variety of countries.
Sigh. I despair of "16x faster" and "20x faster" headlines that ignore the
raw numbers. *The worst numbers in there are far below the threshold of
user perception*. Unless these results are compounded by running in a loop,
they are meaningless. Not immeasurable, just meaningless.
https://www.nngro
fore the heat death of the universe. As such, I've developed
an allergy to context-free performance comparisons.
On Mon, Sep 30, 2019 at 10:25 AM Ron wrote:
> On 9/29/19 7:01 PM, Morris de Oryx wrote:
>
> Sigh. I despair of "16x faster" and "20x faster" headlines that i
As I understand it, custom collation are not applied globally. Meaning, you
have to associate a collation with a column or en expression with COLLATE.
I've been experimenting with the FTS features in Postgres over the past few
days. Mind blow.
We're deployed on RDS, which does not give you any file system to access.
I'd love to be able to create a custom thesaurus dictionary for our
situation, which seems like it is impossible in a setup like ou
Fair.
Given that Amazon is bragging this week about turning off Oracle, it seems
like they could kick some resources towards contributing something to the
Postgres project. With that in mind, is the idea of defining dictionaries
within a table somehow meritless, or unexpectedly difficult?
Nope, no custom C installs. RDS is super convenient in many ways, but also
limited. You can't, for example, run TimeScale, install RUM indexes (if
those still work), or any novel plugins. And you can't do anything at all
requiring a file reference. The backup features are outstanding. But, yeah,
so
I've been experimenting with CREATE STATISTICS to declare some functionally
dependent columns. Right now, I'm working with a local copy of Postgres
running on this version:
PostgreSQL 12.5 on x86_64-apple-darwin16.7.0, compiled by Apple LLVM
version 8.1.0 (clang-802.0.42), 64-bit
We deploy on RDS
nt of keyboard...my understanding is that you
shouldn't mess with the catalog data directly.
On Sun, Nov 15, 2020 at 6:08 PM Tom Lane wrote:
> Morris de Oryx writes:
> > And here's the error that I get back:
> > ERROR: could not find tuple for statistics object 147574.
>
n Mon, Nov 16, 2020 at 3:34 AM Tom Lane wrote:
> Morris de Oryx writes:
> > After posting, I realized that this is likely a Stupid User Error. I was
> > mucking around, and did something along the lines of
> > delete from pg_statistic_ext;
> > or
> > delete from
Professor Mueller! I believe that we met, long ago. I graduated from your
department in 1984 where I worked closely with the wonderful, late Prof.
Dipple.
Postgres.app is a very easy way to work with Postgres, and it does include
support for tablefunc. If you ever want to check which extensions ar
The original question has already been answered really well, but it reminds
me to mention that *Postgres text/varchar values are case-sensitive*.
Here's a list of the times when I would like a case-sensitive text field:
Never
Now here's the list of times I would like a case-blind text field:
We definitely *store* data case-sensitively, we just never want to *search*
on it case-sensitively. That's what citext gives us. Many databases
perform this way as a default. Postgres does not, but it offers
alternatives. The OP is coming from MySQL which, if I remember correctly,
treated non-bina
Sounds like I may have touched a nerve with some. If so, no offense
intended!
There are cases where case-sensitivity is required or desirable, it would
be silly to argue otherwise. Where you have such cases, then case-sensitive
queries are great. Some RDBMS systems default to case-sensitive search
of fuzzy string matching and n-grams, but that's an unrelated topic.
On Mon, Mar 18, 2019 at 10:18 PM Morris de Oryx
wrote:
> Sounds like I may have touched a nerve with some. If so, no offense
> intended!
>
> There are cases where case-sensitivity is required or desirable, it wou
Good question, and there are some excellent thoughts and cautionary tales
in the thread already. I've faced this question several times down the
years and, as others have said, the best answer depends. Roughly speaking,
I can think of three obvious places to store documents:
* A database.
* A fil
Jeremy's question is *great*, and really well presented. I can't answer his
questions, but I am keenly interested in this subject as well. The links he
provides lead to some really interesting and well-though-out pieces, well
worth reading.
I'm going to try restating things in my own way in hopes
Scan using abbr_hash on state_test (cost=0.00..4872.25
rows=17100 width=0)
Index Cond: (abbr = 'MA'::text)
No index
Finalize Aggregate (cost=10696.37..10696.38 rows=1 width=8)
-> Gather (cost=10696.15..10696.36 rows=2 width=8)
Workers Planned: 2
->
On Sat, Jun 1, 2019 at 6:24 PM Gavin Flower
wrote:
> On 01/06/2019 14:52, Morris de Oryx wrote:
>
> I'd expect the distribution of values to be closer to a power law than
> the Normal distribution -- at very least a few states would have the
> most lookups. But this is my gu
Sat, Jun 1, 2019 at 11:53 PM Peter J. Holzer wrote:
> On 2019-06-01 17:44:00 +1000, Morris de Oryx wrote:
> > Since I've been wondering about this subject, I figured I'd take a bit
> of time
> > and try to do some tests. I'm not new to databases or coding, but have
that this is all in my head a bit, I'm hoping for more feedback and
real-world observations. Any commentary appreciated.
On Sun, Jun 2, 2019 at 9:10 AM Morris de Oryx
wrote:
> Peter, thanks a lot for picking up on what I started, improving it, and
> reporting back. I *thought *I wa
Peter,
Thanks a lot for the remedial help on EXPLAIN and timing results.
Thanks to Tom Lane and Jeff Janes for chiming in with the level of detail
they're able to provide.
As an outsider-who-now-loves-Postgres, I don't know the history or deep
details of all of the various index types. (Obviously.) As a long-time
database programmer, I can say that low-cardinality fiel
I didn't notice Bloom filters in the conversation so far, and have been
waiting for *years* for a good excuse to use a Bloom filter. I ran into
them years back in Splunk, which is a distributed log store. There's an
obvious benefit to a probabalistic tool like a Bloom filter there since
remote look
I'm digging into GiST indexes again, and ran into a helpful script here:
https://medium.com/postgres-professional/indexes-in-postgresql-5-gist-86e19781b5db
(This piece has shown up in many places in various versions.) I've adapted
the search a little, as I'd like to make it easier to explore avai
Thanks for the confirmation. And, I'd say that this feature would go under
"nice to have" rather than anything more important. Although, it *would *be
nice.
On Thu, Aug 22, 2024 at 5:42 PM Tom Lane wrote:
> Morris de Oryx writes:
> > What I'm
I'm trying to determine if an ltree GiST index search *ever *needs to load
a row out of heap for a recheck, of if the index entry itself includes
enough information for a definitive answer. I believe that this is
controlled by the recheck flag in the consistency function.
>From what I've seen in t
As always, thanks very much for the confirmation.
On Fri, Aug 30, 2024 at 12:18 PM Tom Lane wrote:
> Morris de Oryx writes:
> > From what I've seen in the wild, and can sort out from the source, I
> think
> > that ltree does *not* need to load rows from h
35 matches
Mail list logo
