New answers tagged

0

Look at this fiddle. SELECT price, "time", LAST_VALUE(price) OVER (ORDER BY "time" ASC RANGE BETWEEN CURRENT ROW AND '5 second' FOLLOWING) last_price, LAST_VALUE("time") OVER (ORDER BY "time" ASC RANGE BETWEEN CURRENT ROW ...


2

You can set old_snapshot_threshold and then wait out that amount of time before retrying the VACUUM. (Note that this is a last resort. First resort would be just to identify the long running connections, and making them go away if they are not really needed, or downgrading their isolation level).


1

You can write stored procedures in plpgsql to get access to procedural behaviour in SQL, but you will probably get the best use of PostgreSQL if you come to terms with the set-based relational agebra which is the foundation of SQL. First of all, you have to understand that a table (a.k.a. relation) is fundamentally an unordered set of tuples/records, not an ...


2

As Akina has pointed out in comments LAG() function requires an ORDER BY clause to show the properly values. Given the next example: CREATE TABLE t (id int, bar int); INSERT INTO t VALUES (1, 7), (2, 8), (3, 30), (4, 25), (5, 24), (6, 24), (7, 35), (8, 40); You can get the previous bar value ordering by id, (if you don't set an order, what is the ...


0

We are now looking at docker + logs as having caused the issue. It's not the database.


0

You are fundamentally doing a lot of work, and doing a lot work takes a lot of time. It is not clear you can do much better here. One possibility is that you pre-compute the counts for the whole table: select bar_id, count(bar_id) from api.foo group by bar_id And join to that rather than running the subselect inside the "sqrt". I see no reason to think ...


1

The command you show generates a text backup file. You can use "head" or "less" on it to verify that it looks like a PostgreSQL text backup file, same as mysql. I'd also run "tail", just to make sure it didn't get truncated. It should end with a sign-off like: -- -- PostgreSQL database dump complete -- -- -- PostgreSQL database cluster dump complete -- ...


0

partition by hash was causing the problem. It causes "random" inserts that do not take advantage of caching/buffers. Changing it to sequential/range partitions solved the problem.


1

That just means that your query is bogus. age(xid) compares the age to the current transaction ID, so it is useless to call it with a multixact ID. Moreover, running that query will return nonsense for anything except for regular tables (relkind = 'r'). You could try something like SELECT cc.next_multixact_id::text::bigint - t.relminmxid::text::bigint ...


2

That would mean that at least one of hll_cardinality and hll_union_agg must not be parallel safe. Try \df+ hll_cardinality \df+ hll_union_agg in psql and see what it says under Parallel. If the functions are parallel safe, you can use ALTER TABLE to mark them as such. If in doubt, ask the author of the functions. In addition, the aggregate function must ...


0

You can use jsonb_set to replace a deep-in part of a jsonb structure. ...jsonb_set(thing,'{0,comments,1,message}','"something"'); To put that together with setting a specific element of a PostgreSQL array, it would be: update tasks set comments[1]=jsonb_set(comments[1],'{0,comments,1,message}','"something"') where id=1; Figuring out that '0' and '1' ...


1

That's what you get by following tutorials. It is better to understand the process (as explained in the documentation), then problems like this won't happen. You have to place recovery.conf in the data directory. The data directory is the directory where postgresql.conf is, unless the data_directory parameter in postgresql.conf says differently.


1

pg_total_relation_size is definitely the way to go. It gets the size of the involved files from the file system, which is fast and not invasive. If my monitoring system would regularly run SELECT count(*) on by big tables, I would kill it. It does a sequential scan and uses lots of resources. For monitoring, I would not use pg_size_pretty. That is good for ...


3

This will be fixed in version 12, which will be released soon. I think the summary here is that we are only willing to do so much work to try to prove a partial index can be used, because all queries have to go through that work even if they don't end up using the partial index. In this change, they just found a more efficient way to do that work in this ...


Top 50 recent answers are included