New answers tagged postgresql
0
It's OK for it to fail (and, for my logic, revert the whole transaction), but I don't want this failure to take 2ms instead of 40 seconds.
Running the actual DELETE query (with EXPLAIN ANALYZE wrapper or not) is considerably more expensive than checking with a SELECT whether any FK reference will prevent the operation.
Identify FK constraints pointing to ...
5
But running in Postgres, the query is glacially slow.
UPDATE foo
SET bar = t2.bar
FROM foo t1
JOIN foo2 t2 ON t1.id = t2.id;
There is no join condition between foo an t1, the implicit CROSS JOIN forces a Cartesian product, i.e. O(N²) (!) update operations instead of just O(N). And the result is non-deterministic nonsense. The effect also becomes ...
0
It is odd to see that it chooses (deleted_at) rather than (rejected_at, deleted_at), it must be that rejected_at is usually NULL, so the condition on that is expected remove few rows. The larger size of the index makes it thought to be not worth using to remove just a few more rows.
While a specialty index like the filtered functional index Laurenz ...
2
I don't see why you require a CROSS JOIN in this case! They are performance killers!
You can simplify this problem greatly by doing the following (and make it more general into the bargain!) - all code below is available from the fiddle here:
CREATE TABLE IF NOT EXISTS car_store
(
car_id INT GENERATED ALWAYS AS IDENTITY,
car_time TIMESTAMP(0) NOT ...
2
Your error was that Postgres interpreted the column as Integer and performs a Euclidean division, which leads to the zero encountered.
Changing the car_count_last_week to a decimal, will give you the rational numbers division
(car_count_last_week::decimal/100)
fixes the problem
CREATE TABLE IF NOT EXISTS car_store (
car_id INT GENERATED ALWAYS AS ...
2
The explanation is that in this context, user is treated as a function (without parentheses!) and is synonymous to the current_user function. So it will always return a single result when used in the FROM clause, and that result is your current user name.
1
The planner does weigh between the options and chooses the one that it thinks will be cheaper, of course based on the estimates it has at hand at the time.
When having the ANY in the "Index Cond", it needs to re-descend the index one time for each of the 19 members of your list. Each time it re-descends, it thinks it will land on a different index ...
3
Rewrite the condition on reserved_until to
WHERE COALESCE(reserved_until, TIMESTAMP '-infinity') <= '2021-05-14 14:23:16'
and create an index for it:
CREATE INDEX ON table_name (COALESCE(reserved_until, TIMESTAMP '-infinity'))
WHERE rejected_at IS NULL AND deleted_at IS NULL;
Then you should gather statistics:
ANALYZE table_name;
0
Only one index can be used to serve your example query, so currently your EXPLAIN ANALYZE is showing it's using the index on (deleted_at) with an Index Scan operation, which might not be the most optimal.
Why not test one index on all 3 fields like so: (reserved_until, rejected_at, deleted_at)
0
Unfortunately you'll probably have to handle the stored procedures by hand. If you're lucky, they're simple procedures following standard SQL syntax, and you'll be able to just copy the existing queries over to PostgreSQL, otherwise you'll have to handle any fallout on a case by case basis.
Side note: this is one benefit of some database agnostic ORMs like ...
0
is it normal disk read performance for PostgreSQL ?
PostgreSQL doesn't read your disk. It tells the kernel to read the disk, then waits for it to respond. You need to have a discussion with whoever is in charge of your SAN and networking about what should be considered normal for it.
Some times hitting the disk is still slow but only 8s here:
PostgreSQL ...
0
Reading 1124 blocks to get 1904 rows is unfortunate, but normal.
What is not normal is the I/O time: almost 20 ms for a single 8kB block. Either your I/O system is really overloaded, or the disk is amazingly slow.
2
With a large table, you won't be able to avoid I/O, and the query will never be fast. Very fast aggregates over huge amounts of data are not PostgreSQL's strong point. You want a column store for that (PostgreSQL has a column store extension).
If you VACUUM the table, PostgreSQL can avoid looking up the row by consulting the visibility map.
If 11 partitions ...
2
You need to run EXPLAIN (ANALYZE, BUFFERS).
Beware that that actually executes the DELETE, so run it in a transaction and ROLLBACK afterwards.
That will show you information about foreign key constraints unless they are deferred, in which case they are executed at commit time.
0
If the execution plan that you posted is for the query that is too slow for you, the answer would be to create an index on 'CaseProductUnderwritingPK".
1
Cannot do a restore using PgAdmin4 after a new install of PostgreSQL - pg_restore.exe file not found
I was able to fix (or patch) the problem by coping files.
Searched for pg_restore.exe from folder 'C:\Program Files\PostgreSQL\13'. I found the file in 'C:\Program Files\PostgreSQL\13\bin'.
Then I copied all the files from 'C:\Program Files\PostgreSQL\13\bin' folder to 'C:\Program Files\PostgreSQL\13\pgAdmin 4\runtime' where the error message says file is ...
1
Including the schema fixed it. The other tables were in the public schema but article_plain was in a schema of it's own:
postgres@server:~/bin$ pg_dump -s --clean clientportal -T otrs.article_plain -T callstats -T cdrviewer_cdr -T calls -T cdrviewer_datasource -T django_sesssion -T django_admin_log | grep article_plain
DROP SEQUENCE otrs.article_plain_id_seq;...
1
I’m in a similar situation right now, trying to make a decision and I’m kind paralyzed regarding going multi schema with one DB and shared schema. Anyway regarding your post, why have a separate table for each role? As you said the roles can increase which means you have to create additional table every time? I would go with one table and and a role column ...
0
In the first case it can look up in the stats to see how many records it thinks will have bank_account_id 'b12edcab-9ac5-4a09-a84c-475c8a73c964' and thinks that after combining it with the type in ... there will be 94, while in the second case it cannot do that since that specific value is not known at the time the query is planned. Instead it thinks there ...
1
Why DISTINCT ON is sorting the rows?
Distinct removes "duplicate" rows from the result set. That's a whole lot easier to do if the items being compared arrive neatly sorted into order.
Check the Explain Plan for this query - it will probably contain extra "Sort" and "Unique" steps. Guess what those are doing.
"select ...
-2
Can you explore a graph database for it?
Every user is an entity whose activity is a series of actions [connected node], can even maintain backtracking.
Even you can maintain the chronology easily.
Good read:
https://snowplowanalytics.com/blog/2014/07/31/using-graph-databases-to-perform-pathing-analysis-initial-experimentation-with-neo4j/
1
With =, the optimizer deduces that if m1.mdate = m2.mdate and m1.mdate = 'constant' , the same must hold for m2.mdate. No such inference takes place for inequality operators.
0
You can query pg_locks after the procedure is done, but before the transaction ends. Since locks are held until the end of the transaction, you should see all locks taken by statements in the procedure.
0
A query that only reads data will not block normal user activity. Readers don't block writers and vice versa.
However, it may consume substantial resouces on the database server, which could impair performance for the users.
1
select max(id) is the most reliable way.
It's also quite fast if you have a b-tree index on id (performance is virtually independent of the size of the table).
1
Quote from the manual
The database server must be shut down in order to get a usable backup.
Another option is to use pg_dump.
That quote is still valid today. If you want real incremental backups (not dumps) then you need you use WAL archiving or use a tool like barman, pgbackrest, or pg_probackup.
See What is a database backup.
2
Why? Because no one implemented it.
My guess is that since statement level triggers have been implemented recently, this feature was left for a later stage.
3
Stored procedures don't acquire locks. Queries do. But you can't really detect which query obtained a lock. You can only see which session/transaction is holding a lock.
0
Any decent rdbms can and -my opinion- should be backed up online.
One of the reasons is exactly this, going for a filesystem backup with the database down. What happens if during that backup the database is started? It invalidates the backup and you only detect the problem at restore time.
An other reason is because it gives more options to restore and ...
0
Do you really need that "distinct" on the top level select?
This is always a Red Flag to me, too-often added as a "quick and easy" way to "get rid" of "duplicate" rows that appear in the result set "for some reason".
Even worse ...
select distinct events.* ...
... has to perform that de-duplication across ...
1
Q: But still i am getting all rows at the end of the function and no
intermediate rows
Unfortunately these results are accumulated (as opposed to streamed) by the server-side implementation of RETURN QUERY. This is explicitly mentioned in the documentation:
Note
The current implementation of RETURN NEXT and RETURN QUERY stores the
entire result set before ...
11
Well, for starters look at this,
CREATE DOMAIN mydomain AS int;
CREATE TABLE foo(bar) AS SELECT 42::mydomain;
SELECT f1.bar AS f1, f2.bar AS f2, pg_typeof(f1.bar), pg_typeof(f2.bar)
FROM foo AS f1
LEFT JOIN foo AS f2
ON false;
That currently returns,
f1 | f2 | pg_typeof | pg_typeof
----+----+-----------+-----------
42 | | mydomain | mydomain
But ...
0
I converted @a_horse_with_no_name's answer above to pure bash, which may be useful for some.
I'm now using the below script to back up remote postgresql databases using rsnapshot, following the similar method here for msyql databases.
Here's the script:
backupdir=/my/backupdir
# backup globals
sudo -u postgres pg_dumpall --globals-only | gzip > $...
0
2021 Update:
In a Postgres 12.3 setup, this query now returns with units:
select name, setting, unit
from pg_settings
where name in ('min_wal_size', 'max_wal_size');
Result:
name | setting | unit
--------------+---------+------
max_wal_size | 1024 | MB
min_wal_size | 80 | MB
(2 rows)
Additionally, the documentation states that:
If ...
0
This must be the TCP keepalive timeout: if there is no traffic on a TCP connection, the kernel will automatically start sending TCP keepalive packages after a while. If there is no response to those, it determines that the other side has hung up without closing the connection and reports an error message. The default time until the kernel starts sending ...
2
because on partitioned tables on Postgres the partition key must belong always to the index
This is a misconception, and perhaps accidentally the cause of the problem. The partition key must be part of the primary key or part of a parental unique index, but it does not need to be part of other indexes. So you can create a index on (fk_x_orders_id) on the ...
0
First, let me add some more description of the environment where this problem was observed.
This excellent series of articles describes setting up kerberos and postgresql
PostgreSQL GSSAPI Authentication with Kerberos
Part-1
Part-2
Part-3
If you do this in AWS, you should also setup elastic-ip addresses (so you can stop and start your servers). I also ...
0
You can use time_bucket function to make it.
Example:
SELECT
time_bucket('1 hour', "time") AS hour,
metric,
avg(value)
FROM building_data
WHERE kv_tags->'equip_name' = '["35201"]' AND
m_tags IS NOT NULL
GROUP BY hour, metric
ORDER BY hour limit 1000
Note that I'm simplifying the query and if you want to include kv_tags and ...
0
You cannot avoid locking. Keep your transactions short and the number of rows locked reasonable by performing the update in batches, e.g. by primary key range. That reduces the danger of deadlocks.
0
You can grant as below instead of altering each table's owner.
I faced below error while updating similar table,
PG::InsufficientPrivilege: ERROR: must be owner of relation <my table name>
Suppose a table was created by a DB user named "createU" and you are trying to perform other operation using another DB user named "updateU" ...
0
The easiest way to do this would be to use Team Appir's jalali_utils extension for PostgreSQL. Then you could write your query like this:
SELECT COUNT(reserverow.id), EXTRACT(MONTH FROM reserverow.used_date) as "Month"
FROM reserverow
WHERE reserverow.used_date > '2000-09-28 01:00:00'
GROUP BY JALALI_PART('month', reserverow.used_date)
3
The dotless I is a special case. It's processed by the ICU collation service with rules that depend on the language.
If the locale refered to the Turkish or Azerbaijani languages, it would produce the result that speakers of these languages might expect (that is, i and ı are two different letters with İ and I being their respective uppercase counterparts; ...
2
psql has a flag that controls the way null is outputted -P 'null=\N'
2
It's the overhead that the SQL client has, in this case pgAdmin.
Use a different SQL client. pgAdmin isn't known to be the fastest. You will always have an overhead. Parsing and network communication (even if everything is local) can never be avoided. The client needs to receive and display the result - that is where pgAdmin seems to be the slowest option (I ...
2
The manual:
Note that you should also create an index with the default operator
class if you want queries involving ordinary <, <=, >, or >=
comparisons to use an index. Such queries cannot use the
xxx_pattern_ops operator classes. (Ordinary equality comparisons can
use these operator classes, however.) It is possible to create
multiple indexes ...
1
Nevermind. The long response time seems to be some aberration in the DB server. Running the same code a little later is doing the INSERTs is around 10msec pretty consistently. Sorry to bother you!
0
You can check if a foreign server exists with name 'my_server_name' using the following:
DECLARE
srvExists boolean DEFAULT false;
BEGIN
SELECT true INTO srvExists FROM pg_catalog.pg_foreign_server
WHERE lower(srvname) = lower( 'my_server_name' );
IF srvExists THEN
RAISE NOTICE 'foreign server my_server_name exists';
ELSE
CREATE ...
0
Use a database script instead of a csv:
with v (id,bo,c,url) as
(values
( 1,'some text here', 22, cast('[{ "id": "NfKA3", "u": "https://somewebsite.com"}]' as json)),
( 2,'some text here', 83, cast('[{ "id": "W3nAl", "u": "https://somewebsite.com"}]' as json))
)
insert into ...
0
JSON uses double quote " to delimite keys and strings:
[{"id": "NfKA3", "u": "https://somewebsite.com"}]
CSV uses double quote to escape double quotes "" means "
1,"some text here", 22, "[{""id"": ""NfKA3"", ""u"": "&...
0
Slightly different generic version - this returns true or false, depending on whether or not the value could be cast. Works with user-defined domains too. This works on PostgreSQL 12.
CREATE OR REPLACE FUNCTION appcode.try_cast (p_in TEXT, p_type VARCHAR(128))
RETURNS BOOLEAN
AS
$$
/*
Accepts a text value, and a data type; returns true if the
...
Top 50 recent answers are included
