69

I had a similar problem. As it turns out, those ON DELETE CASCADE triggers were slowing things down quite a bit, because those cascaded deletions were awfully slow. I solved the problem by creating indexes on the foreign key fields on the referencing tables, and I went from taking a bunch of hours for the deletion to a few seconds.


30

The easiest method to solve the problem is to query detailed timing from the PostgreSQL: EXPLAIN. For this you need to find at minimum a single query that does complete but takes longer than expected. Let's say that this line would look like delete from mydata where id='897b4dde-6a0d-4159-91e6-88e84519e6b6'; Instead of really running that command you can do ...


26

At this point, there's no right to grant, it's hardcoded to superuser. That's been discussed on the mailing list lately, and may change in 9.5 if someone finds the time to work on it. As a workaround, you can create a SECURITY DEFINER function that is owned by the superuser, and runs the query you want. This will allow non-superusers to see the contents of ...


25

The crucial thing to understand here is that privileges are not heirachical and are not inherited from containing objects. ALL means all privileges for this object not all privileges for this object and all contained objects. When you grant ALL on a database, you're granting CREATE, CONNECT, TEMP. These are actions on the database object its self: CONNECT: ...


16

Timestamps with B-tree index I suggest a third option: as long as your table holds two timestamp columns (which seem to be defined NOT NULL) I would use a single multicolumn index with opposed sort order (if no other considerations apply): CREATE INDEX reservations_range_idx ON reservations using gist(starts_at, ends_at DESC); More in these related answer: ...


14

You are most likely tying to insert a row in a table for which the serial column sequence value is not updated. Consider following column in your table which is primary key defined by Django ORM for postgres id serial NOT NULL Whose default value is set to nextval('table_name_id_seq'::regclass) The sequence is only evaluated when the id field is set as ...


14

You can use row_number, and the easiest way is to just add a row_number() OVER (PARTITION BY true) field into the view. You need the PARTITION BY and using the "true" expression is the most performant way (no need for sorting like in Fabrizio Mazzoni's answer).


14

Yes, there can be downsides. If another query looks at a different data segment not determined by the date, it might take a performance hit if rows are spread out over more data pages now. Just the same way as your first query profits. That completely depends on information not in your question. other queries using a PK of table (let say id_foo) That ...


13

I don't understand why you need 2 tables if they have only 1-1 relationship. But here it is (pk is the primary key of t3): WITH ins AS ( INSERT INTO t1 (col1) SELECT NULL FROM t3 RETURNING t1_id ) , r AS ( SELECT t1_id, ROW_NUMBER() OVER () AS rn FROM ins ) , t AS ( SELECT *, ROW_NUMBER() OVER () AS rn FROM t3 ) INSERT INTO t2 (col1, ...


12

Disabling triggers may be a threat to DB integrity and cannot be recommended; however if you are sure your operation is constraint-failure-proof, you can disable triggers, with the following: SET session_replication_role = replica; Run the DELETE here. To restore triggers, run: SET session_replication_role = DEFAULT; Source here.


12

group is a reserved word (and by is another reserved word) - it's not GROUP BY that is reserved. Because it is a reserved word, it cannot be used directly as an identifier. To use a reserved word or a name with "illegal" characters (such as a space) for an identifier, you need to quote the identifier. ALTER TABLE test RENAME COLUMN sum TO "...


12

I found several bug reports indicating that pg_dump has problems writing to a pipe on Windows: BUG #7794: pg_dump: errors when using pipes/streams BUG #6726: pg_dump - ftell mismatch when using -Fc and split Instead of writing to stdout and redirecting to a file, try using -f specify the output file: pg_dump -Fc -U "username" -w "dbname" -f "filename".dmp


12

First, use ST_SetSRID(ST_MakePoint(c.longitude, c.latitude),4326)::geography instead of ST_GeographyFromText('SRID=4326;POINT(' || c.longitude || ' ' || c.latitude || ')') Per documentation: ST_MakePoint while not being OGC compliant is generally faster and more precise than ST_GeomFromText and ST_PointFromText. It is also easier to use if you ...


12

This was posted to pgsql-hackers mailing list and I tried to answer in brief there. It seems if the target list (specified columns) matches the tuple descriptor of the relation exactly, that is, both in number of columns and order, then the underlying scan can return a tuple that's directly consumable by the enclosing Sort node. On the other hand, if the ...


11

I was having this issue on Windows 7 64-bit and the following solution is what worked for me (via http://igordcard.blogspot.com/2012/03/unable-to-write-inside-temp-environment.html): Why To understand why this error was happening, I went to my TEMP folder and read the log from the PostgreSQL installer: C:\Users\myuser\AppData\Local\Temp\bitrock_installer....


11

Short version: no. PostgreSQL doesn't support generating no WAL whatsoever, because changes to system catalog tables in the pg_catalog schema are always logged. There's also transaction ID logging (pg_clog), the multixact tracking data (pg_multixact), etc, in addition to the xlog, but they tend to be very small. As Daniel says, you can run with minimal WAL ...


11

You can see a rough progress using the TOC list. First, get the TOC list of objects to be restored: pg_restore -l -f list.toc db.dump Then, you can see the TOC list line by line and compare the output of verbose or query pg_stat_activity to see where in the TOC list is pg_restore in. It is just a rough estimate though. First because each item from the ...


10

From the prompt, it looks like you are not connecting as a superuser. To view the address information about the connection, you must be connected as superuser.


10

CREATE ROLE doe LOGIN INHERIT; CREATE ROLE sales NOINHERIT; CREATE ROLE marketing NOINHERIT; GRANT sales to doe; GRANT marketing to sales; If you connect to PostgreSQL as doe, you will have privileges of doe plus privileges granted to sales, because doe user role has INHERIT attribute. However, you do not have privileges of marketing because the NOINHERIT ...


10

If you have a serial column or an integer one that's automatically populated with a nextval (so that you are never supposed to insert new rows with an explicit value for that column), you could additionally check whether the value of that column is greater than a specific value: ( (("qb_id" IS NOT NULL) :: INTEGER + ("xero_id" IS NOT NULL) :: INTEGER + ...


9

If you only want to prevent new users from creating tables, you need to run the following command: REVOKE CREATE ON SCHEMA public FROM public; If you REVOKE ALL (as other answers suggest), you will also prevent users from having USAGE permissions. USAGE means that users can use the permissions assigned to them, so if you remove that then your users will ...


9

As @deszo said user OVER() create view foo as ( select row_number() over (order by field), field, field2, field3 from bar )


9

Why don't you create a new sequence: create sequence new_sequence; Then create the new table create table doki_data_item2 (like doki_data_item including defaults); Finally modify the column that is using the old sequence to use the new one: alter table doki_data_item2 alter column seq_column set default nextval('new_sequence'); You can obviously set ...


9

A PostgreSQL server connection dropping after 10-15 minutes is almost certainly being caused by a state-tracking firewall (possibly using Network Address Translation (NAT)) between the client and the server. Many such firewalls have default timeouts of 15 minutes (900 seconds). The three server-side parameters, tcp_keepalives_idle, tcp_keepalives_interval, ...


9

Just add the constraint as NOT VALID From the manual: If the constraint is marked NOT VALID, the potentially-lengthy initial check to verify that all rows in the table satisfy the constraint is skipped. The constraint will still be enforced against subsequent inserts or updates (that is, [...] and they'll fail unless the new row matches the specified ...


8

There are probably better ways to do this, but here are a few functions to return days or intervals from a tstzrange/tsrange input parameter. CREATE OR REPLACE FUNCTION extract_interval(TSTZRANGE) RETURNS interval AS $func$ select upper($1) - lower($1); $func$ LANGUAGE sql STABLE; CREATE OR REPLACE FUNCTION extract_interval(TSRANGE) RETURNS interval AS $...


8

You appear to be attempting to load uuid-ossp extension by loading the .sql file directly. You should not be doing that on any modern PostgreSQL, and it won't work on RDS. Use CREATE EXTENSION "uuid-ossp"; instead.


8

For each table that is created, Postgres creates a corresponding composite type: Whenever you create a table, a composite type is also automatically created, with the same name as the table, to represent the table's row type. When you use the table name in the select list, you are essentially using a single column of that composite type with several ...


8

This is what you need: SELECT * FROM ticketdata WHERE data_cadastro >= date_trunc('month', CURRENT_DATE);


7

Starting with PostgreSQL 10 you can grant the role pg_read_all_stats in order to achieve the desired result.


Only top voted, non community-wiki answers of a minimum length are eligible