gregate-jsonb-in-postgres/
--
Seamus Abshere, SCEA
https://www.faraday.io
https://github.com/seamusabshere
https://linkedin.com/in/seamusabshere
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
> > On Mon, Oct 16, 2017, at 10:55 AM, Tom Lane wrote:
> > I'd bet on the last one, especially since you found that the problem
> > was a page-level lock. Did you look to see which relation the page
> > lock was in?
On Mon, Oct 16, 2017, at 12:34 PM, Seamus Abshere w
> Seamus Abshere writes:
> > * I do have some exotic indexes [2]. gist, gin, postgis, fillfactor...
> On Mon, Oct 16, 2017, at 10:55 AM, Tom Lane wrote:
> I'd bet on the last one, especially since you found that the problem
> was a page-level lock. Did you look to see
On 2017-10-14 16:32:33 Tom Lane wrote:
> More likely explanations for the OP's problem involve foreign key
> constraints that cause two different row updates to need to lock
> the same referenced row, or maybe he's using some index type that
> has greater locking demands than a btree, or he's using
> On Sat, Oct 14, 2017 at 10:30 AM, Seamus Abshere
> > UPDATE [...] WHERE id BETWEEN 'ff00----' AND
> > 'ff0f----'
> > and
> > UPDATE [...] WHERE id BETWEEN 'f8c0----000
' AND
'f8ff----'
Yet one blocks the other one. How is this possible?
Thanks,
Seamus
--
Seamus Abshere, SCEA
https://www.faraday.io
https://github.com/seamusabshere
https://linkedin.com/in/seamusabshere
--
Sent via pgsql-general mailing list (pgsql-ge
On Fri, Oct 13, 2017, at 03:16 PM, David G. Johnston wrote:
> implement a "system-managed-enum" type with many of the same properties
[...]
> TOAST does involved compression but the input to
> the compression algorithm is a single cell (row and column) in a table.
> As noted above I consider the T
> > On Fri, Oct 13, 2017 at 8:49 AM, Seamus Abshere wrote:
> >> Theoretically / blue sky, could there be a table or column type that
> >> transparently handles "shared strings" like this, reducing size on disk
> >> at the cost of lookup overhead for all q
> On Fri, Oct 13, 2017 at 8:49 AM, Seamus Abshere wrote
> > Theoretically / blue sky, could there be a table or column type that
> > transparently handles "shared strings" like this, reducing size on disk
> > at the cost of lookup overhead for all queries?
> >
mn type that
transparently handles "shared strings" like this, reducing size on disk
at the cost of lookup overhead for all queries?
(I guess maybe it's like TOAST, but content-hashed and de-duped and not
only for large objects?)
Thanks,
Seamus
--
Seamus Abshere, SCEA
https://www.fa
hi,
I've had an `INSERT INTO x SELECT FROM [...]` query running for more
then 2 days.
Is there a way to see how big x has gotten? Even a very rough estimate
(off by a gigabyte) would be fine.
Best,
Seamus
--
Seamus Abshere, SCEA
https://www.faraday.io
https://github.com/seamusabshere
.io/how-to-do-histograms-in-postgresql/ which gives
plpsql so you can do:
SELECT * FROM histogram($table_name_or_subquery, $column_name)
--
Seamus Abshere, SCEA
https://www.faraday.io
https://github.com/seamusabshere
https://linkedin.com/in/seamusabshere
--
Sent via pgsql-general mailing list (pgs
--
name | 300 | 100 | 200 | 0.66
Thanks!
Seamus
--
Seamus Abshere, SCEA
https://www.faraday.io
https://github.com/seamusabshere
https://linkedin.com/in/seamusabshere
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql
it help to "redundantly" add the id ranges everywhere? (e.g, in
the where clauses of old_data AND the final update)?
Thanks!
--
Seamus Abshere, SCEA
https://www.faraday.io
https://github.com/seamusabshere
https://linkedin.com/in/seamusabshere
--
Sent via pgsql-general mailing list (pgsql-ge
isn't referenced if it was for a SELECT, but
since it's an UPDATE, it will be run anyway)
Thanks!
Seamus
--
Seamus Abshere, SCEA
https://github.com/seamusabshere
https://linkedin.com/in/seamusabshere
https://www.faraday.io
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
think the race condition would go
away.
Did I analyze that right?
Thanks!
--
Seamus Abshere, SCEA
https://github.com/seamusabshere
https://linkedin.com/in/seamusabshere
https://www.faraday.io
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription
Seamus Abshere writes:
> That aggregates into an array. Our `jsonb_collect` aggregates into an object.
Postgres 9.6 has (per
https://www.postgresql.org/docs/9.6/static/functions-aggregate.html):
* jsonb_agg(expression)
* jsonb_object_agg(name, value)
In retrospect, I think what I am propos
hi,
# select '{"a":1}'::jsonb || null;
?column?
--
null
(1 row)
Is there a theoretical reason that this has to return null as opposed to
just {"a":1}?
Thanks,
Seamus
--
Seamus Abshere, SCEA
https://github.com/seamusabshere
https://linkedin.com/in/seamus
> Seamus Abshere writes:
> > We do this in our database:
>
> > CREATE AGGREGATE jsonb_collect(jsonb) (
> > SFUNC = 'jsonb_concat',
> > STYPE = jsonb,
> > INITCOND = '{}'
> > );
>
> > Is there some other built-i
.
Thanks for your advice,
Seamus
--
Seamus Abshere, SCEA
https://github.com/seamusabshere
https://linkedin.com/in/seamusabshere
https://www.faraday.io
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Seamus
PS. Here is the real query analyze:
https://gist.github.com/seamusabshere/b9d72132361fa598f7a431fa1bcb120f
--
Seamus Abshere, SCEA
http://faraday.io
https://github.com/seamusabshere
http://linkedin.com/in/seamusabshere
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.or
..@sss.pgh.pa.us
Is there any way to "update *" from a record?
Thanks!
Seamus
PS. Whether I **should** do this is another matter, I just want to know
if it's possible.
--
Seamus Abshere, SCEA
https://github.com/seamusabshere
http://linkedin.com/in/seamusabshere
--
Sent via pgsql
smallint, anyarray, integer,
> internal, internal, internal, internal),
> STORAGE uuid;
Is there a reason not to put this into postgres itself? This already
exists for text[].
Thanks,
Seamus
--
Seamus Abshere, SCEA
+598 99 54 99 54
https://github.com/seamusabshere
--
Sent via pgsql-general mai
On Mon, Feb 22, 2016, at 06:48 PM, David G. Johnston wrote:
> it would probably be more constructive to actually communicate the thoughts
> that provoked the question.
My company has a largish table - 250+ columns, 1 row for every household
in the US. It's read-only. We've gotten advice to conver
bility checks.
--
Seamus Abshere, SCEA
+598 99 54 99 54
https://github.com/seamusabshere
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
hi,
https://wiki.postgresql.org/wiki/ReadOnlyTables mentions the possibility
of `ALTER TABLE table SET READ ONLY`.
Would this mean that row visibility checks could be skipped and thus
index-only scans much more common?
Thanks,
Seamus
--
Seamus Abshere, SCEA
+598 99 54 99 54
https://github.com
On Mon, Feb 22, 2016, at 02:53 PM, Seamus Abshere wrote:
> On Mon, Feb 22, 2016, at 02:49 PM, Tom Lane wrote:
> > Seamus Abshere writes:
> > > Inspired, I changed cpu_index_tuple_cost to 0.1 (default: 0.005). It
> > > "fixed" my problem by preventing t
On Mon, Feb 22, 2016, at 02:49 PM, Tom Lane wrote:
> Seamus Abshere writes:
> > Inspired, I changed cpu_index_tuple_cost to 0.1 (default: 0.005). It
> > "fixed" my problem by preventing the BitmapAnd.
> > Is this dangerous?
>
> Use a gentle tap, man, don&
On Mon, Feb 22, 2016, at 02:30 PM, Jeff Janes wrote:
> It charges 0.1 CPU_operator_cost, while reality seemed to be more like 6
> CPU_operator_cost.
fdy=> select name, setting, boot_val from pg_settings where name ~
'cpu';
name | setting | boot_val
--+
my issue, at least?
--
Seamus Abshere, SCEA
+598 99 54 99 54
https://github.com/seamusabshere
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
41 loops=1)
> Index Cond: (phoneable = true)
I think this is why we originally set random_page_cost so "low"... it
was our way of "forcing" more index usage (we have a big, wide table).
Is there any other way to differentiate the 2 index scans? FWIW, 10% of
houses are
ue)
> Planning time: 0.709 ms
> Execution time: 4565.067 ms
> (13 rows)
On Postgres 9.4.4 with 244gb memory and SSDs
maintenance_work_mem 100
work_mem 50
random_page_cost 1
seq_page_cost 2
The "houses" table has been analyzed recently and has statistics s
On Wed, Jan 6, 2016, at 08:41 PM, Tom Lane wrote:
> Seamus Abshere writes:
> > -> Can a function like `LEFT()` use an index?
> Since the question makes little sense as stated, I'm going to assume
> you mean "can a query like SELECT ... WHERE left(foo, 3) = '
erage
indexes?)
Thanks!
Seamus
--
Seamus Abshere, SCEA
+598 99 54 99 54
https://github.com/seamusabshere
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
1st Ave S',
'\m(\d+(?:st|th))\M')) = (select * from regexp_matches('2033 20th
Ave S', '\m(\d+(?:st|th))\M'));
?column?
--
f
(1 row)
Is there a more elegant way to compare the results of
`regexp_matches()`?
Thanks,
Seamus
--
Seamu
hi,
I've got use cases like
array_remove(array_agg((a->>'b')::float), NULL)
It would be nice to replace (a->>'b')::float with something like
a->^'b'
that directly returned a numeric... is that in the cards?
Thanks,
Seamus
--
Sea
, 'b');
^
DETAIL: Token "b" is invalid.
CONTEXT: JSON data, line 1: b
That seems like the wrong error - shouldn't it be the equiv of "[...]
json and string cannot be matched"?
Thanks,
Seamus
--
Seamus Abshere, SCEA
On 7/23/14 7:45 PM, John R Pierce wrote:
On 7/23/2014 3:29 PM, Seamus Abshere wrote:
My argument lives and dies on the assumption that UPSERT would be
useful even if it was (when given with no options) just a macro for
UPDATE db SET b = data WHERE a = key;
IF NOT found THEN
INSERT
On 7/23/14 6:50 PM, David G Johnston wrote:
seamusabshere wrote
On 7/23/14 6:03 PM, John R Pierce wrote:
On 7/23/2014 1:45 PM, Seamus Abshere wrote:
What if we treat atomicity as optional?
atomicity is not and never will be optional in PostgreSQL.
I'm wondering what a minimal definiti
On 7/23/14 6:03 PM, John R Pierce wrote:
On 7/23/2014 1:45 PM, Seamus Abshere wrote:
What if we treat atomicity as optional?
atomicity is not and never will be optional in PostgreSQL.
I'm wondering what a minimal definition of upsert could be - possibly
separating concurrency handlin
On 7/23/14 3:40 PM, Tom Lane wrote:
John R Pierce writes:
On 7/23/2014 10:21 AM, Seamus Abshere wrote:
Upsert is usually defined [1] in reference to a violating a unique key:
Is this theoretically preferable to just looking for a row that
matches certain criteria, updating it if found or
/postgresql.uservoice.com/forums/21853-general/suggestions/245202-merge-upsert-replace
[2] http://docs.mongodb.org/manual/reference/method/db.collection.update/
--
Seamus Abshere, SCEA
https://github.com/seamusabshere
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make cha
ore dropping it I would like to run EXPLAIN
and do timing tests on the queries to see the impact of not having that
index available and rewrite the query to efficiently use other indexes
if necessary.
--
Seamus Abshere, SCEA
+598 99 54 99 54
https://github.com/seamusabshere
--
Sent via pgsql-
On 4/4/14, 12:58 PM, Tom Lane wrote:
Seamus Abshere writes:
Why is pg_restore trying to put stuff into the pg_catalog schema of all places?
Hm ... does myschema actually exist in the target database? [...] if myschema
doesn't exist, the creation target devolves to pg_catalog. This i
enied to create "pg_catalog.stuff_one"
DETAIL: System catalog modifications are currently disallowed.
Command was: CREATE TABLE stuff_one (
the_geom public.geometry
);
Thank you!
Seamus
--
Seamus Abshere, SCEA
https://github.com/seamusabshere
--
Sent via pgsql-general mailing list (pgsql-genera
ripts-available/CDB_JenksBins.sql
[3]
https://www.datadoghq.com/2013/08/100x-faster-postgres-performance-by-changing-1-line/
[4]
http://blog.heapanalytics.com/dont-iterate-over-a-postgres-array-with-a-loop/
--
Seamus Abshere, SCEA
https://github.com/seamusabshere
--
Sent via pgsql-general mailing list
46 matches
Mail list logo
