40

When you create a new database, any role is allowed to create objects in the public schema. To remove this possibility, you may issue immediately after the database creation: REVOKE ALL ON schema public FROM public; Edit: after the above command, only a superuser may create new objects inside the public schema, which is not practical. Assuming a non-...


35

PostgreSQL will not try to insert duplicate values on its own, it is you (your application, ORM included) who does. It can be either a sequence feeding the values to the PK set to the wrong position and the table already containing the value equal to its nextval() - or simply that your application does the wrong thing. The first one is easy to fix: ...


32

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.


29

To return space to the OS, use VACUUM FULL. While being at it, I suppose you run VACUUM FULL ANALYZE. I quote the manual: FULL Selects "full" vacuum, which can reclaim more space, but takes much longer and exclusively locks the table. This method also requires extra disk space, since it writes a new copy of the table and doesn't release the old ...


27

You have a few options. The best option is to run a batch delete so that triggers are not hit. Disable the triggers before deleting, then re-enable them. This saves you a very large amount of time. For example: ALTER TABLE tablename DISABLE TRIGGER ALL; DELETE ...; ALTER TABLE tablename ENABLE TRIGGER ALL; A major key here is you want to minimize the ...


19

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: ...


19

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 ...


15

Answer is yes. :) CREATE OR REPLACE FUNCTION create_table_type1(t_name varchar(30)) RETURNS VOID AS $func$ BEGIN EXECUTE format(' CREATE TABLE IF NOT EXISTS %I ( id serial PRIMARY KEY, customerid int, daterecorded date, value double precision )', 't_' || t_name); END $func$ LANGUAGE plpgsql; I am using format() with %I to sanitize ...


14

If you intend to use PostgreSQL's built-in streaming replication, then you seed the replica with a base backup - a file-system level copy of the original database. So it already has all the tables and data from the master. pg_basebackup is usually used for this. Streaming replication reads changes at the block level from the write-ahead logs, so the ...


14

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 ...


13

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:...


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

The postmaster.pid should be inside the previous version's usr/local/var folder. Simply renaming this file should solve the problem.


11

Like almost everything else, it depends on your hardware, configuration and workload. If you're running few expensive statements it'll make little difference; if you're running lots of tiny simple statements it'll make more difference. If your logs are on the same file system or even just the same disk as the database, the logging will make more ...


10

I would prefer the latter solution. You can collect the schema names into a file (one schema per line) in psql: \o change_schema.sql \t on SELECT n.nspname FROM pg_catalog.pg_namespace n WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'; -- reset the output \o \t off Then you can easily do the following: Have a DDL changing script (...


10

Use CREATE DOMAIN with a CHECK constraint. This works in PostgreSQL 9.1. It's documented to work in at least 8.0+. "A partial workaround is to use domain types as members of composite types." create domain angle as float check (value between -90 and 90); create type axis as ( major_axis float, minor_axis float, angle angle ); create table ...


10

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 "group"; Note ...


10

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 ...


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 + ...


10

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 ...


9

The userlist.txt file contains the accounts for connecting from your client to PgBouncer. The [databases] section contains the login information for connection from PgBouncer to the backend PostgreSQL server. You appear to be mixing these two up. When you connect from your client to PgBouncer you need to provide a user name and password from userlist.txt ...


9

In addition to what @swasheck already explained, you'll probably get better performance with LIKE (~~) and ILIKE (~~*) in combination with a trigram GiST or GIN index. You'll have to install the additional module pg_trgm for that. Find details under these related questions: How is LIKE implemented? Pattern matching with LIKE, SIMILAR TO or regular ...


9

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.


9

Just for completeness, another approach is to loop over all schemas and run the change with dynamic SQL in PL/PgSQL, eg: DO $$ DECLARE schemaname name; BEGIN FOR schemaname IN SELECT nspname FROM pg_namespace WHERE nspname NOT LIKE 'pg_%' AND nspname <> 'information_schema' LOOP EXECUTE format('ALTER TABLE %I.my_table ADD COLUMN blah ...


9

There's no exact equivalent to what you want. Options include: Atomic file-system snapshot If you're using an atomic file system snapshot you don't need to freeze the database. It might make recovery a little faster if you force a CHECKPOINT first, but that's about it. When you take a filesystem snapshot and copy it, then start the copy, to PostgreSQL it's ...


9

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....


9

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).


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

Taken from http://www.depesz.com/2013/03/30/waiting-for-9-3-add-new-json-processing-functions-and-parser-api/ per dezso's comment: Given current limitations for both datatypes, I would say that they should be used in different scenarios: if you want indexable searches, and you’re fine with “no-nesting” limitation: use hstore. if you need nested, ...


8

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.


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