New answers tagged postgresql
1
Standard PostgreSQL runs on a file system and depends on the file system cache in addition to the PostgreSQL buffer cache. Aurora PostgreSQL runs on top of Aurora Storage, which is not a file system and doesn't have a separate cache on the instance. So, with Aurora PostgreSQL we set the default for shared_buffers to 75% of the total memory on the instance. ...
0
Is this what you require (fiddle)?
Create table j (tid serial, nom text, jid text);
Insert into j (nom, mid) values
('Sam', 'a'),
('Sam', 'b'),
('Ali', 'b'),
('Tim', 'c'),
('Tim', 'd'),
('Finn', 'a'),
('Mona', 'c'),
('Bill', 'z'),
('Sam', 'x'),
('Sam', 'y'),
('Finn', 'w');
Then:
With dup_jobs AS
(
Select jid from j
Group by jid
Having count(1) &...
1
I'm not sure I understand, but perhaps:
WITH non_duplicate_jobs (job) AS (
select jobid from T
group by name
having count(1) = 1
)
select t.name, count(1)
from t
join non_duplicate_jobs x
on t.jobid = x.jobid
group by t.name;
2
In general no. A relation (a table or the result of a query) consists of a header and a set of tuples. Once the header is declared, all tuples must conform to that declaration. That said, null is the bottom of all types, so you can use that instead of a value:
SELECT FIRST, CASE WHEN variable <> 'something' then SECOND else NULL END
NULL is the ...
1
I solved my problem executing an anonymous code block.
-- Make sure nothing exists in the current session
DROP TABLE IF EXISTS temporary_flow;
DO LANGUAGE plpgsql $$ DECLARE
BEGIN
IF 'potato' != 'potato' THEN
EXECUTE ' CREATE TEMP TABLE temporary_flow AS SELECT NOW() AS first ';
ELSE
EXECUTE ' CREATE TEMP TABLE temporary_flow AS SELECT NOW() AS ...
1
No, not possible. The number of columns of a query must be known to the database before starting to run the query - essentially after it has been parsed and analyzed. - a-horse-with-no-name
Statement output structure must be deterministic. So the aim which you want is not relational. I think there is some global task, and you decide to solve it by described ...
0
You can simply do another subquery:
SELECT
u.id,
(select r2.id from public."role" as r2 where r2."roleDescription"='private_account') as missing_role_id
FROM public."user" AS u
WHERE u.id NOT IN (
SELECT ur.user
FROM public."userroles" as ur
INNER JOIN public."role" as r
ON ur.role=r.id
WHERE r."roleDescription"='...
1
Some assumptions, it is not allowed to manipulate the log, so I'll just handle the insert. id in itself is monotonically increasing, so what need to check is whether there exists a ts bigger than the current ts:
create or replace function validate_total_order()
returns trigger as $$ ...
1
This sounds like an occurrence of: can the database enforce constraints across rows?
(I assume that the X sign in the formula means "such that")
As a general rule, the answer is no, because when a row is being modified by a transaction, the state of the other rows is always uncertain due to possible modifications by not-yet-committed concurrent transactions....
0
Create tables:
postgres=# create table "user" (
id int primary key,
name text unique);
postgres=# create table "role" (
id int primary key,
name text unique);
postgres=# create table "userroles" (
"user" int references "user",
"role" int references "role",
primary key("user", "role")
);
Populate test data
postgres=# insert into ...
1
1) Log into postgres
pgsql -U <DB_USERNAME>
2) After you are connected, switch to the DB you want to install the extension for:
\c <DB_NAME>
3) Then install the extension as answered previously:
CREATE EXTENSION pg_trgm;
Installing the extension initially gave me issues because I was not doing step 2. I thought the installation was a ...
-1
According to the link https://docs.bitnami.com/aws/apps/noalyss/administration/configure-pgadmin/.
For PgAdmin 4 on Windows. I added these lines below
pg_hba.config:
host all all all md5
and modify postgresql.config:
listen_addresses = '*'
1
It's a slightly belated answer, but it might be useful for future readers
Now days (in 10,11,12 versions) we don't need to store same data twice (in WAL by PG and manually). We can use Postgre Logical Decoding mechanics (same as logical replication) to track all or some changes to our data (or send those events to some queue like kafka to analyze later)
0
I have accepted an answer, but I would like to illustrate how I have put the answer into practice.
WITH data AS (SELECT count(*)/10.0 AS bin FROM prints WHERE price<20)
SELECT
id,title,price,
row_number() OVER(ORDER BY price) AS row_number,
ntile(10) OVER(ORDER BY price) AS decile,
floor((row_number() OVER(ORDER BY price)-1)/bin)+1 AS ...
0
You cannot do that with a view, because a view is parsed when it is created, and the parse tree is stored. That will lead to an error if a table used in the view does not exist.
What you could do is to write a table function that searches for tables, constructs a query string for each and uses RETURN QUERY EXECUTE to add the results to the output. That ...
1
It is the LC_CTYPE setting that defines character classification, case conversion, and other character attributes. So this is what determines if a character is printable or not.
ñ is not printable in the C locale:
SHOW lc_ctype;
lc_ctype
----------
C
(1 row)
select regexp_replace('niño', '[^[:print:]]', '[X]','g');
regexp_replace
----------------
...
2
Normally you never have to run VACUUM (FULL). It is only necessary if the tables have become unduly bloated, either because of autovacuum being too slow or because of mass updates.
I would examine the tables you suspect to be bloated with pgstattuple to see how big free_percent is.
20% bloat is normal. Consider VACUUM (FULL) only if you have much more ...
2
That is just as it should be.
If you return a significant part of the table, the overhead of the bitmap index scan part is not worth paying, because you have to visit most of the heap blocks anyway.
So PostgreSQL just skips that part and goes to the heap scan directly. The result is a sequential scan.
1
I don't think there is any built-in way to do this. I think it would usually be handled by spell checking the search phrase before it ever gets submitted to the database (or by spell checking each word in it after it had been submitted and found no results).
You can use pg_trgm to do fuzzy searching which will be likely to find misspellings, but it doesn't ...
0
You've solved your problem, that's great. Recently, I got turned on to the benefits of getting estimated counts rather than "perfect" counts, in cases where it's slow, despite any cleanup efforts you might take. Here's a function adapted ("stolen") from a fine blog post by Laurenz Albe:
DROP FUNCTION IF EXISTS api.row_count_estimate (text);
CREATE OR ...
0
TL;DR I have solved by running this command:
vacuum analyze subscriptions;
After that command the queries take only ~1s instead of ~17s. For a detailed explanation see @Laurenz answer.
Now I run the autovacuum more frequently using these settings in postgresql.conf:
autovacuum_vacuum_scale_factor = 0.01
autovacuum_analyze_scale_factor = 0.01
1
The reason vacuuming your table worked is that it enabled an index-only scan to be used efficiently. Unfortunately, autovacuum's scheduler was only designed to handle space-reuse and wraparound prevention, it never got updated to handle the needs of index-only scans when that feature was added. It isn't really clear how to update it in a generic way to ...
3
Fetching and counting 5 million rows is slow business.
There are two problems:
The bitmap heap scan is taking longer than necessary, because work_mem is so small that it cannot contain a bitmap with one bit per table row.
It then degrades to storing a bit per 8KB block, which leads to more heap fetches in the bitmap heap scan phase to sort out the false ...
1
PostgreSQL doesn't have an in-core natural language identifier. I'm not aware of direct PostgreSQL extensions to do it either. But there are lot of language identifiers out there, and it shouldn't be too hard to map one of them into PostgreSQL.
I would do that using pl/perl or pl/perlu to wrap an existing Perl module, like in this answer. But pl/pythonu ...
0
In my case the source is Filemaker ODBC which also treats long text as LOB datatype. My package used to hang for a long time due to the extreme decrease in performance for Row by Row fetch method is enforced because the table has LOB column(s). Thus, while being deployed it used to timeout after a long time and fail eventually.
I am sharing the actual ...
1
Hmm, good question.
I suggest that you rename it and fill it with zeros at the end, for example using
dd if=/dev/zero of=000000010000000C00000080 oflag=append conv=notrunc bs=1024 count=16416
This could be part of your restore_command.
3
The table structure you show looks fine from a database point of view, and it should work fine for an OLTP workload. Using nested loop joins, it is no problem to fetch a few rows by joining a number of tables.
If the schema is properly normalized, data modifications will be as localized as possible, and consistency is guaranteed.
Things become different ...
1
You can simulate WIDTH_BUCKET function:
CREATE FUNCTION dbo.width_bucket(@val decimal, @min_val decimal, @max_val decimal, @groups int)
RETURNS int
AS
BEGIN
DECLARE @res int;
IF @val = @max_val RETURN @groups;
RETURN CAST((@val - @min_val) / ((@max_val - @min_val) / @groups) AS int) + 1;
END
SELECT
id, title, price, dbo.width_bucket(price,...
1
A plain VACUUM will make the free space available for new data (i.e., other TOASTed JSON data).
If you really want to give the free space back to the OS, and you cannot afford the exclusive lock, try pg_repack:
To perform a full-table repack, pg_repack will:
create a log table to record changes made to the original table
add a trigger onto the ...
5
You could use rank() and do integer division with number of rows for each bin.
declare @T table(id int, title varchar(100), price int);
insert into @T(id, title, price) values
(19, 'Deux fillettes, fond jaune et rouge ', 11),
(17, 'Flowers in a Pitcher ', 12),
(5 , 'Composition with Red, Yellow and Blue ', 12),
(18, 'La ...
0
with sum as (select ud.id,sum(data_volume) total
from mediation.udr ud
inner join mediation.meter_reading mr on ud.cdr_id = mr.id
group by ud.id)
(select total from sum)
This is the complete CTE but needs formatting. Your code below is a complete different query. You need to rewrite the step 2
WITH - create a "list" step 1
with list as (
select ud.id,...
0
Make an after-update trigger on "customer" which does a dummy update on "project", which will then fire the trigger on project to do its thing:
CREATE OR REPLACE FUNCTION customer_depend_trigger() RETURNS trigger AS $$
BEGIN
update project set customer_id=NEW.id where customer_id=NEW.id;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
create ...
0
I have encountered the same issue while trying to restore some dumps, in -Fc custom format. I'm not sure the problem will or can occur with any of the other dump formats.
However, I found that the "out of memory" error that pg_restore laconically drops on exit, is program-internal and not actually an OOM. It also occured without any DB interaction, when '...
1
Yes, it does:
SELECT last_vacuum,
last_autovacuum
FROM pg_stat_all_tables
WHERE schemaname = 'myschema'
AND relname = 'mytable';
1
Select all columns ... such that I don't have to type out all of the column names
But that is exactly what you should be doing!
Never use select * in production code.
Why? Two reasons.
The database has to work harder, first to work out which columns exist, construct the query and then actually retrieve the data for you. OK, the amount of extra work ...
1
No, COPY does not need a lot of memory.
There are two things to speed up COPY:
Remove any indexes and constraints on the table before you COPY into it.
Increase max_wal_size so that you don't get more checkpoints than necessary.
Of course, if you COPY into an UNLOGGED table, it will be even faster.
1
The problem is that WAL files only get archived when they are full (or at the end of a PostgreSQL backup).
So it is likely that there was no WAL switch since you finished the backup, and the WAL archiver probably died before it could archive the final WAL file with the additional actions in it (check the original server's log file).
You can use the ...
0
Before Upgrade;
Dump globals;
pg_dumpall --globals-only --file=globals.sql
Dump pre-data;
pg_dump --format=plain --create --section=pre-data --file=pre-data.sql db_name
Dump post-data;
pg_dump --format=custom --section=post-data --file=post-data.custom db_name
pg_dump --format=plain --section=post-data --file=post-data.sql db_name
Restore globals, pre-...
2
This is a generic (and slightly academic) answer that shows how this problem can be solved in general by defining your own type and operator class.
I'll use strings (type text) as an example.
First, we have to define our own type invtext exactly like text:
/* create a shell type */
CREATE TYPE invtext;
/* copy type functions from "text" */
CREATE ...
1
The fact that 4 out of the 5 queries are in the BIND state make me think you have a problem with probing the endpoints of an index. This can often be problem if the planner is deciding if it wants to use a merge join. Note that even if it decides against the merge join, it has still called get_actual_variable_range and if that is slow then it still suffers ...
0
Managed to resolve the problem, I was using an md5 sum of my Postgres password in the user list. I did this because I saw it in 3 different guides but upon looking at the official docs I saw you need to put your password in plain text while having the auth type still md5. This makes sense now but why 3 different guides on different sites all gave wrong info ...
2
According to the same Postgres documentation
The following environment variables can be used to select default connection parameter values, which will be used by PQconnectdb, PQsetdbLogin and PQsetdb if no value is directly specified by the calling code[...]
PGDATABASE behaves the same as the dbname connection parameter.
If you want psql to ...
1
Correct, PostgreSQL does not make every optimization which is theoretically possible.
You can try to force it into doing what you want with a UNION ALL query:
select * from (
(SELECT * FROM stuff
WHERE foo = 543210 AND bar < 1234 ORDER BY foo ASC, bar desc LIMIT 100)
union all
(SELECT * FROM stuff
WHERE foo > 543210 ...
0
You could
CREATE INDEX ON stuff (foo, (-bar));
and query like
SELECT * FROM stuff
WHERE (foo, (-bar)) > (543210, -1234)
ORDER BY foo, -bar;
0
Things that have helped so far (from ~10-15s to ~2-4s):
Upgrading Postgres from 9.5 to 11.5 (allowed for paralelisation, possibly other benefits too)
Removing unnecessary DISTINCT
A couple of further ideas:
Work out how to allow Postgres to choose Index-only scans in place of the slowest of the Index scans taking the most time (note, the column order of ...
0
There are several possibilities:
you ran CREATE EXTENSION in a different database (postgres?)
you installed the extension in a schema that is not on the user's search_path.
Use
\dx "uuid-ossp"
in psql to see where the extension is installed.
2
You need a table alias for both occurrences of the table and then prefix every column with the corresponding table alias to make the column references unambiguous.
select t1.*
from the_table t1
where exists (select *
from the_table t2
where t2.a = 7
and t2.b = t1.b + 1);
Note that you probably want something ...
1
I don't have enough points to comment, so an answer.
OMG. I feel your pain ;-)
Yeah, people find foreign key constraints a real pain. But without them, you have to then audit the data for widows and orphans, otherwise you're entirely likely to have bad summary results. At the very least, you don't really know the validity of your results. Are they off by 0....
3
Without messing with the catalogs (which is not commendable), the only option I can think of would require you to do without foreign keys for a while:
You can define a second UNIQUE index on the table that contains the appropriate INCLUDE clause.
If you use the CONCURRENTLY clause of CREATE INDEX, that shouldn't be disruptive.
Then you can delete the ...
1
When trying to connect via pgadminIII i am prompted for a password when using the same user name.
pgAdminIII is probably connecting over TCP (as opposed to unix domain sockets) and so is subject to the rules of a 'host*' line from pg_hba.conf, rather than a 'local' line. If that line says a password is needed, then it will ask for a password--even if no ...
Top 50 recent answers are included
