Tag Info

Hot answers tagged

9

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


3

Foreign key constraints are currently implemented with special internal triggers. All of them are run FOR EACH ROW. Note that these are implementation details that can change, so don't rely on it. But the basics have not changed over the last couple of major versions, so major changes are unlikely. I ran a quick test with a simple FK constraint from tbl to ...


3

It does execute N times, and the easiest way to see this is to execute the statement with an EXPLAIN ANALYZE prepended, i. e. EXPLAIN ANALYZE UPDATE a SET col = 1 WHERE othercol = 'foo'; This will give you information similar to this: Trigger for constraint t_col_fk on a: time=1.300 calls=9 (tested with 9.2)


3

initdb initializes the whole server data directory and every file will be in the same disk. With tablespaces you can distribute objects onto different disks when needed. The reasons usually are performance related. For example, you may want to have some rarely used tables on slower disks and high throughput tables on super fast disks. Another benefit is to ...


2

I Will answer my own question, From: http://stackoverflow.com/questions/13505785/postgresql-database-size-is-less-after-backup-load-on-heroku The reason of this is its MVCC system. Every time you UPDATE any record in a database it creates another "version" of this record instead of rewriting the previous. This "outdated" records will be deleted by VACUUM ...


2

The postmaster symlink appears to be historical. I didn't even know it existed until you pointed it out. The other two are a typical pattern for UNIX daemon applications. postgres provides the server functionality, and pg_ctl provides control client for it. It would be possible to bundle pg_ctl's functionality into postgres, but doing so would mean ...


1

I missed that you are already adjusting for maintenance_work_mem in my first read. I'll leave quote and advice in my answer for reference. Per documentation: maintenance_work_mem (integer) Specifies the maximum amount of memory to be used by maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY. It defaults to 64 ...


1

This was a bug in pgagent, it has been fixed in http://git.postgresql.org/gitweb/?p=pgagent.git;a=commitdiff;h=f9bf1ccb27ebcfce00e7a6d467bc0e1b5ee9555e.


1

Use PostgreSQL's built-in uuid data type, and create a regular b-tree index on it. There is no need to do anything special. This will result in an optimal index, and will also store the uuid field in as compact a form as is currently practical. (Hash indexes in PostgreSQL are not crash-safe and are really a historical relic that tend to perform no better ...


1

You can simply do UPDATE my_tbl SET (a1, a2) = (a1 || 123465, a2 || 132456789) WHERE id = 321; To obtain a function, you simply have to replace the integer constants with the parameters. You can safely do it in a query language (aka SQL) function, no need for pl/pgsql at all.


1

You're interpreting the semantics of the delete statement incorrectly. When a using clause is used, it doesn't mean that records will also be deleted from those tables. Instead, those tables are purely used to join to in order to determine which rows need to be deleted from Users. You basically have three choices: deleting child rows in a before delete on ...


1

Each pgAdmin query window has it's own session. You can run one transaction at a time in each session. It's not possible to run two transactions concurrently in the same session (at least not in pgAdmin or psql). You can open and bind more sessions to the same query window with the "connection bar" in the menu of the query window, then switch between ...


1

Unfortunately that's not that easy because there is no "select any view" privilege (as e.g. Oracle offers). If you can put all views into a single schema, then this could be achieved by granting the select privilege on that schema. Something like this: First you need to create a role that can be used for this create role view_reader; The role only ...


1

Some years ago I tested different solutions (amongst others some similar to those from @ErwinBrandstetter) for merging overlapping periods on a Teradata system and I found the following the most efficient one (using Analytical Functions, newer version of Teradata have built-in functions for that task). sort the rows by start date find the maximum end date ...


1

It's hard to tell what exactly your graphs say. But commit is designed to be slow. By default, Postgres waits for changes to be written to disk before it lets commit return. You can let commit complete before changes are written to disk with this option: synchronous_commit = off That should certainly help to spread the disk I/O required for commit over ...



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