New answers tagged postgresql
0
What is the full version of PostgreSQL on both master and replica?
That looks like it probably was corruption, and taking a new base backup is all you could do (and investigate your hardware, to see if you can figure out why there was corruption).
I assume you removed WAL from the replica, not from the master. So it would re-fetch the same WAL files, and ...
0
This is what max_slot_wal_keep_size was implemented for in PostgreSQL v13. It sacrifices the replica when it demands too much WAL be retained on the master. I have never used it on RDS, but I do see it is available for users to set via the RDS control panel, so presumably it works.
1
You can use conditional aggregation for this.
In PostgreSQL you can use the FILTER clause.
In other DBMSs you can use a condition inside the aggregation
avg(case when ... then value end)
SELECT
time_bucket_gapfill('30s', time, start => '2021-07-19T09:06:26.605Z', finish => '2021-07-19T20:11:12.340Z') AS "time",
max(value) FILTER (...
0
If the error
ERROR: permission denied for relation migrations
pops up when trying to do a GRANT ACTION,
example:GRANT SELECT ON ALL TABLES IN SCHEMA schema_name to ro_user;
ensure the GRANT is performed by the user that is the owner of the respective tables.
Owner of schemas might vary within a database. Even postgres user will not be allowed to provide ...
1
The logic in the query is backwards.
You are trying to delete 10,000 rows from some_other_table, not from the CTE, so there is no need to LIMIT the CTE.
Ideally, you need this kind of query, but I don't think it is supported in PostgreSQL.
WITH cte_table AS (
SELECT t1.id
FROM my_table t1
INNER JOIN another_table t2
ON t1.id = t2.id
)
...
0
While the application is lock-free (no explicit lock), UPDATE statement locks the row. Using the wiki pointed in the problem along with helpful tips from this community, a long running background process was identified as the culprit.
The background process, using SQLAlchemy ORM, was expected to make changes to the hundreds of records in memory. After all ...
0
@bbaird gave a working solution in this DbFiddle.
As per my comment to my question I have decided to return the day durations and do the nested summation in code as it will be a very few simple lines.
1
You are probably using a method to return data which needs unique column names, for example because it uses the column names as keys in a hash. You can add aliases to make the column names unique.
This is the first line of your query with aliases:
SELECT certifications.*, users.name as user_name, company.name as company_name, certstype.name as ...
1
-D and/or PGDATA are really the directory of the config file, despite the misleading names.
You need to change the postgresql.service file (used by systemctl) to point to the new directory of the config file, then change the data_directory line in config file to point back to the new/old location of the "real" data directory.
0
No, for three reasons:
You cannot have foreign key references to multiple tables
It assumes the actions are the same (or will remain the same) for all entities
It assumes the same key structure for all entities and will not permit any deviation from that
The easiest solution is to have individual event tables for each entity and individual actions for each ...
1
As mustaccio mentions in the comments, you need to move the predicates from the WHERE clause to the joins:
SELECT
*
FROM
categories cat
LEFT JOIN
category_user_relations cat_user
ON cat_user.category_id = cat.id
AND cat_user.user_id = < user_id >
LEFT JOIN
questions qst
ON qst.category_id = cat.id
LEFT JOIN
answers ans
ON ...
0
With your current indexes, there is no efficient way to reverse the nested loop. The index on (ts desc, event_id desc) cannot be used to efficiently find rows with a specific event_id, because it would have to scan through all the different ts in the given range (which is large) in order to identify them.
With an appropriate index, such as one on either (...
1
The client seems to have issued a LISTEN command, but is not actually listening. That is, it is not reading the data it is being sent. (That is what ClientWrite means--the server is trying to send it more data, but the send buffer is full.) Once it processes the notices (or other data waiting to be sent), the xmin should advance automatically.
Could ...
0
I have same setup and successfully extracted data with
SELECT encode(data, 'escape')::jsonb->'name'
FROM foo
0
The same happened to me and, after a shower-insight, I realized that the table I'm trying to dump is partitioned. In this case, I needed to also specify in the pg_dump command the name of the inheriting (children) tables where the data is actually stored. Once done, the dump contains all the data I was looking for.
2
Assuming all involved table columns NOT NULL.
Your added clarifications make it a much simpler problem.
This only counts the first hour of each passage:
SELECT waterway_id, date_trunc('hour', ts), count(*) AS count
FROM (
SELECT waterway_id, ts -- , boat_id
, lag(waterway_id, 1, '') OVER (PARTITION BY boat_id ORDER BY ts) <> waterway_id AS ...
0
Can you give this a whirl?
SELECT items.*
FROM items
WHERE item_id in (
SELECT item_id
FROM items_categories
where category_id = 626
Intersect
SELECT item_id
FROM items
Where active = TRUE
)
ORDER BY modified_at DESC
LIMIT 10
0
The query planner can't get good row estimates to adequately plan this type of query. If you can't tolerate bad plans, you will have to do two queries. There are a variety of ways you could arrange that, and it sounds like you already found one. You could create a set-returning plpgsql function which wraps up both queries into a single function call, if ...
0
The parentheses around the right-hand-side of the IN argument are mandatory. However, what goes in those parentheses must be a list. An array doesn't work (unless the thing on the left-hand-side is also an array, then you are testing for an array in a list-of-arrays). To test array membership, you can use the 'aborted'=ANY(...) construct instead of IN. ...
2
You have a number of syntax errors in your query:
Missing comma after model
The base column is called status not statuses
You can't write in statuses, you need to unnest the array like this:
in (select * from unnest(statuses))
Or this:
in any (unnest(statuses))
So your query becomes:
select
*
from (
select
model_id,
model,
array_agg(id) as jobs,...
6
Typically, the best solution is to not store the functionally dependent value notify_trigger_date in the table at all. Just bloats the table. For timestamp or timestamptz, use the (very cheap!) expression instead:
expire_date - make_interval(days => days_before_trigger)
Or the equivalent (and equally cheap):
expire_date - interval '1 day' * ...
2
You need to include INTERVAL in your calculation:
ALTER TABLE D ADD COLUMN notify_trigger_date timestamp
GENERATED ALWAYS AS (expire_date - INTERVAL '1 day') STORED;
If the amount of days is stored in a column, you can create an interval of that and then subtract it. Here is one example:
ALTER TABLE D ADD COLUMN notify_trigger_date date
GENERATED ...
4
Editing to address this (emphasis mine) which was not the case with the original request:
In a table (waterway-hour combinations with 0 passages do not have to
be included in the result):
Primary keys are important
But before we get into that, we need to make sure you have the right primary key defined on your data, which is (Boat_Id,Timestamp). Creating ...
0
Now that we have the real requirement (return a page of results for the user) we can suggest ways to make that achievable.
Your current method of pagination is visiting all the rows in the table that match your filters, then sorting them, then returning a range of 50 depending on what page the user is on.
This is very possible to do fast. You would have an ...
0
You are only allowed to have 25+5 = 30 connections to the real database at once. You are trying to have 1000 connections at once. The rest have to wait a while. They aren't willing to wait long enough.
It isn't clear what you expect to happen here. Do you think the sessions will be shared, even though you are in session-pooling mode and not one of the ...
-2
This works for me
grant usage on schema <schema-name> to <user>;
grant create on schema <schema-name> to <user>;
2
You can use an alias (or a number referencing the position in the SELECT list) only when it stands alone and is not used inside an expression.
The documentation is not quite clear about that; all it says is
In case of ambiguity, a GROUP BY name will be interpreted as an input-column name rather than an output column name.
0
The problem is thats you want to use a values of the json, but you can access his value at that position, what causes the error message
If you need need this, you need top
SELECT a FROM (select jsonb_build_object('x', 1) as a) t1 group by a#>>'{x}';
As the other option for using select columns in a Group by, doesn't allow you to select a value of the ...
6
ALTER TABLE t ADD CONSTRAINT c
CHECK ( url NOT LIKE '%/');
As suggested by Vérace in comment, an alternative would be a generated column as:
ALTER TABLE T ADD COLUMN proper_url TEXT
GENERATED ALWAYS AS ( regexp_replace(url, '/$', '')) STORED;
0
Was your language set to en_US when you installed it? Some people claim it has issues with language settings for similar errors. See here: https://stackoverflow.com/questions/45897521/postgresql-utf8-codec-cant-decode-byte-0xe9-in-position-42-invalid-continua
2
Based on your comment, you have to find the duplicate then delete those records first and then do the update.
create table rss_sub_source (
id int,
sub_url varchar(100)
);
insert into rss_sub_source
select 1,'https://www.google.com/'
union all
select 2,'https://www.msn.com/'
union all
select 3,'https://www.google.com/'
union all
...
2
You can check if such a sub_url already exists:
UPDATE rss_sub_source
SET sub_url = SUBSTRING(sub_url, 1, CHAR_LENGTH(sub_url) - 1)
WHERE sub_url LIKE '%/'
AND NOT EXISTS
(
SELECT 1 FROM rss_sub_source
WHERE sub_url = SUBSTRING
(
sub_url, 1, CHAR_LENGTH(sub_url) - 1
)
)
1
You could make something like this
But a Limit without an ORDER BY makes no sense, so you must choose one that gets you the correct 10 rows
UPDATE rss_sub_source t1
SET t1.sub_url = SUBSTRING(t1.sub_url, 1, CHAR_LENGTH(t1.sub_url) - 1)
FROM (SELECT id FROM rss_sub_source WHERE sub_url LIKE '%/' ORDER BY id LIMIT 10) t2
WHERE t2.id = t1.id
1
LIMIT isn't a valid keyword in an UPDATE statement according to the official PostgreSQL documentation:
[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]
SET { column_name = { expression | DEFAULT } |
( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
( column_name [, ...] ...
0
I agree with author that there should be verbosity option per log level and by default should be enabled (like CONTEXT) above WARNING. Usually when I log a notice I'm interested only with my message. If I want a context or a statement there should be an option / command to generate such.
If there is option to disable them globally then even when context is ...
1
This will work if you create the trigger as a deferrable initially deferred constraint trigger. So instead of
CREATE TRIGGER matViewRefresh
AFTER INSERT OR UPDATE OR DELETE
ON table1 FOR EACH STATEMENT
EXECUTE PROCEDURE matViewRefresh();
do
CREATE CONSTRAINT TRIGGER matViewRefresh
AFTER INSERT OR DELETE OR UPDATE
ON table1
DEFERRABLE ...
0
Running a major upgrade with pg_upgrade without building the standby from scratch is tricky.
Assuming that you use the --link option for speed, there is a way to speed up upgrading the standby, as described in the documentation. For that, you run rsync in such a way that the corresponding data files on the upgraded standby are hard linked to original standby....
1
Your data are not cached, and your index seems bloated. Besides, you didn't show the complete execution plan.
Besides, an index to filter 1.8 million from 8 million will speed up things, but probably not very much.
You should VACUUM the materialized view and set work_mem high.
It seems like most of the time is spent reading the many blocks from disk. If you ...
2
This is part of a class of problems known as Tabibito-san - well worth getting to know! This answer has been highly revised now that I think I've grasped your issue.
I changed your schema slightly - I removed the quoted identifiers - they are normally unnecessary and merely add complexity and make the queries less legible.
I also changed the field named ...
0
Your time is spent in the many repetitions of the subquery. Try to write that smarter using DISTINCT ON:
SELECT DISTINCT ON (ta.code)
ta.code, tta.price
FROM test_transaction_acts AS tta
JOIN test_transactions AS tt USING (test_transaction_id)
JOIN test_invoice_items AS tii USING (test_invoice_item_id)
JOIN test_acts AS ta USING (test_act_id)
...
0
There may be a substantial impact on the first few queries after the rows were deleted, because autovacuum hasn't finished processing the table yet and queries need to dig through a lot of index entries that belong to deleted rows.
However, these index scans will mark the index entries as "dead", and subsequent index scans will ignore them.
After ...
1
The way to identity a table row is the primary key, and you can use that here if both tables have the same primary key.
If the primary keys are automatically generated and different, use another unique constraint. After all, there should be a way to relate rows between those two tables.
0
Deleting rows won't shrink a table, so a sequential scan can take almost as long as before.
Rewrite and shrink the table with
VACUUM (FULL) appointment_timeslot;
0
Please specify the version of PostgreSQL you are using, either by a tag or by updating your question.
A CTE avoids building the whole join for each code.
I made these changes to your query:
Switched to using explicit joins instead of using the comma join operator. Remember that comma joins are implicitly CROSS JOIN, Cartesian products of the tables.
...
0
You can't do that with an IN list. You would have to use a VALUES list(s) and LEFT JOIN that to A. Something like:
with b(team_id, factor_id) as (select * from (
values ('7f4743df-5194-4ba4-8490-4b36ae63e27a'), ('8a4743df-5194-4ba4-8490-4b36ae63e27a'))t
cross join (
values ('0a577004-b283-410a-b245-bff019a8e70d'), ('0f577004-b283-410a-b245-...
1
The easiest thing would be to just get rid of the GiST index. It can't be misused if it doesn't exist. Assuming you created that index for a good (but un-shown) reason and still need it, then if your goal is not to change the original query, I think the best shot is to create a new index:
USING gist (date_range, product_id);
Or maybe
USING gist (...
1
Is there a way to make Postgres not try to re-sort all the data but just apply "unique" filter to it as they are already sorted?
No. The existing "Unique" node only removes adjacent duplicates. So its input must be already ordered on the columns used to determine uniqueness, or it would not behave as expected. PostgreSQL provides no ...
0
I don't think this requires defining a custom aggregation function. Instead, flatten your data so you have a single "seconds" column, with all the values that appear in any of the intervals of your sub-arrays. Then this simplifies into gaps-and-islands (which is well-explored over in that answer, I'm not going to dive into it here).
To flatten your ...
1
It may be that all you need is to avoid using the date-range index. I would have done it like this on Oracle:
select
*
from
product_reservations l1_
inner join product_occupancy_items l0_ on l1_.id = l0_.id
inner join products l2_ on l0_.product_id = l2_.id
where
l2_.customer_id = 'a19917c2-5ee8-47c2-a757-7799c0e54b0d'
and l0_.date_range + 0 ...
1
The problem was my file path. Even though its a windows host I needed to specify the directory as D:/pg-test/
Top 50 recent answers are included
