Tag Info

New answers tagged

1

You cannot use a parameter name (_t) as return type. Use polymorphic types instead: CREATE OR REPLACE FUNCTION vsr_versioning_at_time(_t anyelement, _d timestamp) RETURNS SETOF anyelement AS $func$ BEGIN RETURN QUERY EXECUTE format(' SELECT DISTINCT ON (gid) * FROM %s WHERE vrs_start_time <= $1 ORDER BY gid, ...


0

Use COALESCE to catch and replace NULL values: SELECT f.name AS foo , 'Bazzes: ' || COALESCE(string_agg(b.baz, ', '), '') AS bazzes FROM foo f LEFT JOIN bar b ON b.fooid = f.id GROUP BY 1; concat() is another convenient option as you found yourself, in particular to concatenate multiple values. I suggest the variant concat_ws() ("with ...


1

The simplest method I have found to accomplish this is to swap out the string concatenation operator for the string concatenation function concat(). For some reason the former apparently coerces the entire result to NULL if one operand is null, as opposed to the latter which effectively casts any NULL arguments to ''. So this query performs as desired: ...


0

Perhaps you are looking for this: select issues.id, array_agg(journal.notes) from issues left outer join journal on (issues.id = journal.issue_id and journal.notes != '') group by issues.id Please check this http://sqlfiddle.com/#!1/24db9/2


1

Either you run an additional UPDATE (best in one transaction): UPDATE site_stats s SET total_users = 0 , monthly_users = 0 , weekly_users = 0 WHERE NOT EXISTS (SELECT 1 FROM users WHERE local_site = s.site_id) Or use this instead (I would do that): UPDATE site_stats s SET total_users = COALESCE(u.total, 0) , monthly_users = ...


1

For this query: select distinct date from mybigtable; or its twin: select date from mybigtable group by 1; ... the whole table has to be read. Postgres is not going to use any index, except, possibly, a covering index that is substantially smaller than the table itself. Postgres Wiki on slow counting. Also, to be precise, that's not a count. If you ...


3

This would do what you desire: WITH p AS ( INSERT INTO parent_table (column_1) SELECT $1 RETURNING id) INSERT INTO child_table (parent_table_id, column_a, column_b) SELECT p.id, t.a, t.b FROM p, (SELECT unnest($2::text[]) AS a, unnest($3::bigint[]) AS b) t The subtle difference here is that unnest() calls in the same SELECT list are ...


1

This page in the manual has detailed instructions how to deal with SHMMAX and SHMALL. It depends on your OS. For Linux (example from the manual, to allow 16 GB): $ sysctl -w kernel.shmmax=17179869184 $ sysctl -w kernel.shmall=4194304 Your number is very high: 52033798400 translates to 48 GB. Do you have that much RAM available? Also, more quotes: ...


0

Your options are: Use PDO, which properly supports binary parameters; or Use pg_escape_bytea with string substitution into the query text. See http://stackoverflow.com/q/17121791/398670 .


1

You've left out so much that your chances of recovering useful data are negligible. pg_clog contains the commit/rollback logs. Without these, the system doesn't know which parts of the database files are valid and which are not. (gross oversimplification, but hey). pg_xlog, the write-ahead logs. Without these, the database can't handle incomplete writes, ...


1

I created your scenario with COLLATE = "C", and both queries use a bitmap index scan index on other_names_lower_trgm_gin as expected. SQL Fiddle with a table of ~ 10k rows, Postgres 9.2.4, COLLATE = "C". There is probably something wrong in your setup that is not in your question. Run (takes some time for big tables and an exclusive lock!): VACUUM FULL ...


0

Got some feedback on this question from this Postgres listserv, so thought I'd share it here: Jeff: I'd go with a small shared_buffers, like 128MB, and let the OS cache as much as possible. This minimizes the amount of double buffering. And set work_mem to about 6GB, then bump it up if that doesn't seem to cause problems. In the scenario you ...


2

An array representing the path from the root up to the leaf should achieve the desired sort order: WITH RECURSIVE node_rec AS ( (SELECT 1 AS depth, ARRAY[node] AS path, * FROM nodes WHERE parent IS NULL LIMIT 10 ) UNION ALL SELECT r.depth + 1, r.path || n.node, n.* FROM node_rec r JOIN nodes n ON n.parent = ...


0

I ended up simply using the numeric type as suggested and doing the hex/numeric conversion in my application code.


1

I suggest you to check out MindArray IPM. It provides much deeper insight of Postgresql performance by rendering you a customizable performance metrics for monitoring key performance indicators. All the performance issues related to slow SQL, table locks and user sessions are reported to DBA/IT admins in real-time. IPM allows you to specify your own ...


4

You have to first set expectations - a screen that does such and such activities should complete each action in 1 second and all actions in 5 seconds and so on. For example, a search screen should retrieve results in 3 seconds, the booking actions (ticket booking) should be completed in 30 seconds etc. Then work towards meeting those targets. That is the ...


2

If you are grouping you don't need a distinct and move the conditions from the having to the where select a, group_a, sum(quantity) from cdr where type = 'DATA' and group_a is not null group by a, group_a;


2

Start with the manual page on Write Ahead Log wal_writer_delay (integer) Specifies the delay between activity rounds for the WAL writer. In each round the writer will flush WAL to disk. It then sleeps for wal_writer_delay milliseconds, and repeats. The default value is 200 milliseconds (200ms). Note that on many systems, the effective resolution of ...


4

Much of this is a matter of taste and style. And more importantly: specific requirements and consistent conventions. However, there are good reasons for this generic advice: CREATE TABLE item ( item_id serial PRIMARY KEY, grp_id integer NOT NULL REFERENCES grp(grp_id) ); If you have an item_id, better make it unique and ideally a surrogate ...


2

There is a big difference. Insert as many rows as possible at once. Best in a single INSERT statement, but at least in a single transaction. Inserting individual rows takes much longer. Quoting the chapter Populating a Database: If you allow each insertion to be committed separately, PostgreSQL is doing a lot of work for each row that is added. An ...


1

there is another way to reduce your bloat space when you can remove tuples in heap pages, this method use less adding space to reduce your bloat table. LIKE exp : pg93@db-172-16-3-150-> psql psql (9.3.3) Type "help" for help. digoal=# create table t_bloat(id int primary key, info text, crt_time timestamp); CREATE TABLE digoal=# insert into t_bloat ...


1

If I guessed the missing pieces correctly, this query using a crosstab() function should do the job (and much faster than the original - except for small cardinalities where not much can be gained to begin with): It requires that you install the additional module tablefunc first (once per database. Detailed instructions in this related answer on SO: ...


2

You don't need window functions: UPDATE site_stats SET total_users = totalUsers, monthly_users = monthlyUsers, weekly_users = weeklyUsers FROM ( SELECT u.local_site AS site, count (u.*) AS totalUsers, count(CASE WHEN u.last_visit >= now()::DATE - 30 THEN 1 END) AS monthlyUsers, count(CASE WHEN u.last_visit ...


1

If your environment has disk space more than this table's "real data(after reduced)" size. you can use pg_reorg reduce this table's bloat space. or you can use londiste3 increment copy this table, and use less time to exchange. but if your env has no more space than this table, you can't reduce.


3

If you use anything but CLUSTER / VACUUM FULL / pg_repack you need to make sure there are no concurrent writes to the table. Take an exclusive lock on the table and do everything in a single transaction or, better yet, shut out all connections to avoid concurrent changes. TABLESPACE Yes, your last idea could work. Create a new tablespace on the other disk. ...


2

RETURN QUERY EXECUTE was introduced with Postgres 8.4. Your version is just too old and unsupported by now. Upgrade to a more recent version. Also, dynamic column names in the result are very hard to come by. It's a principle of SQL that it wants to know the return type - including the names - up front. Returning anonymous records without a column ...


2

This is just guesswork: SELECT t.id, t.position, y.position AS y_position, t.created_at FROM tbl t JOIN tbl y USING (id) WHERE y.created_at = t.created_at - 1; Basically, you seem to be after a self-join. Using table aliases you can join the same table multiple times in one query. You can just subtract an integer from a date - assuming it is a date, ...


1

bigserial is just shorthand for creating a sequence: CREATE TABLE tablename ( colname BIGSERIAL ); Equals CREATE SEQUENCE tablename_colname_seq; CREATE TABLE tablename ( colname biginteger DEFAULT nextval('tablename_colname_seq') NOT NULL ); Also, if you these are your primary keys, then they are already indexed, so you don't need to index ...


0

Alex, The following will work for SQL Server. Hopefully you'll be able to translate it to Postgresql. select t.id, t.position, y.position as y_position, t.Created_at from MyData t inner join MyData y on y.id = t.id and y.Created_date = DATEADD(day, -1, t.Created_date)


0

The issue is resolved. Someone pointed out that reducing the shared buffer can help. It was set to 12 GB. I changed it to 1 GB. After that I can see the postgres startup is utilizing alot of resources in iotop. Previously it was writing in few hundred KB/s but now it is varying from 2 MB/s to 30 MB/s. Postgres startup process cpu utilization which was around ...


1

You were close. Your last idea is actually the way to go: log_statement = none log_min_duration_statement = 10000 Then no statement will be logged, except those running longer than 10 seconds - including the query string itself. Logging may have seemed to stop because 10 seconds is a high threshold. I am using 2 seconds normally, but YMMV. This related ...


3

Failover - automatic or otherwise - isn't provided directly by PostgreSQL. You'll need external tools like repmgr. The newly released repmgr 2.0 supports autofailover. However, I recommend thinking very hard about whether you should actually use it. Automated manually-triggered failover is usually a LOT safer. If you do choose to use autofailover, you ...


1

It's the data at the start of the command for an entire database. According to the manpage: It makes consistent backups even if the database is being used concurrently. pg_dump does not block other users accessing the database (readers or writers). and in SQL Dump: Dumps created by pg_dump are internally consistent, meaning, the dump ...


5

Q: Does PostgreSQL uses a hash function for checking equality of integer arrays or does it perform a brute-force algorithm comparing one-by-one the elements of the array? Not according to Array Functions and Operators in the doc: Array comparisons compare the array contents element-by-element, using the default B-tree comparison function for the ...


2

1) as you already have discovered, you can't use b-tree as the index size is bigger than the page size 2) given: As a rule of thumb, a GIN index is faster to search than a GiST index, but slower to build or update; so GIN is better suited for static data and GiST for often-updated data. You would have to use GIN. And no, GIN doesn't use hash functions ...


3

This error message is new in postgres and is indeed related to the replication bug fixed in 9.3.3 As mentioned in the git commit from Mon, 17 Mar 2014: The recently-fixed bug in WAL replay could result in not finding a parent tuple for a heap-only tuple. The existing code would either Assert or generate an invalid index entry, neither of which is ...


2

Current (true at least in PostgreSQL 9.4 and older) PostgreSQL releases have single-threaded WAL recovery. This means that replay of the write-ahead log occurs in only one recovery worker, and is thus able to benefit less from I/O concurrency than a normal running master. This can result in WAL replay lagging behind in cases where the replica and master ...


3

It's an implementation limitation. It's theoretically possible, of course, but nobody's written the code to handle it yet. To cope with column removals or type changes, PostgreSQL would have to scan every view that references the view being modified (using pg_catalog.pg_depend) to see if any of them relied on the column. It'd also need to look for whole-row ...


1

Well, if you need a way to check if all the foreign keys in your table are valid, this might help ( it just validates all foreign keys in your schema ) do $$ declare r record; BEGIN FOR r IN ( SELECT 'ALTER TABLE '|| tc.table_name||' VALIDATE CONSTRAINT '||tc.constraint_name||';' X FROM information_schema.table_constraints AS tc JOIN ...


7

This answer applies to modifying the PostgreSQL source code by applying a "diff" or "patch". It's not about installing minor version updates; to do that, just download and run the installer. To alter the PostgreSQL server its self or its procedural language runtimes, you will generally need to recompile PostgreSQL from scratch. On Windows that's a bit of ...


4

Main problem is the missing index. But there is more. SELECT user_id, count(*) AS ct FROM treenode WHERE project_id = 1 GROUP BY user_id; You have many bigint columns. Probably overkill. Typically, integer is more than enough for columns like project_id and user_id. This would also help the next item. While optimizing the table definition, consider ...


1

I want to group records that have the same combination of class, ip_address, and hostname, and keep the highest timestamp for each day from each group. Not using the column name timestamp (like you shouldn't either). It's a reserved word in SQL and a basic type name in Postgres. Using ts instead. The query is surprisingly simple with DISTINCT ON: ...


2

I'm assuming that you want to create this extension in another server. I'm guessing that the server didn't get the "postgresql contrib" package installed. This package contains the "standard" extensions that are available from the PostgreSQL source code. I think you need to get the system administrator to install the required package for your OS. For ...


0

I know that you are not asking about database security per se, but you can do what you want using database security. You can even use this in a web app. If you don't want to use database security, then the schemas still apply. You want column-level security, row-level security, and probably hierarchical role management. Role-Based security is much easier ...


5

In Postgres, this is simpler with DISTINCT ON: SELECT * FROM ( SELECT DISTINCT ON (sec) id, sec FROM tbl ORDER BY sec, id DESC ) sub ORDER BY id DESC LIMIT 4; Detailed explanation in this related answer on SO: Select first row in each GROUP BY group? For a big table and small LIMIT, neither this nor @a_horse's solution are very ...


5

SELECT id, sec FROM ( SELECT id, sec, row_number() OVER (PARTITION BY sec ORDER BY id DESC) AS rn FROM my_table ) t WHERE rn = 1 ORDER BY id DESC LIMIT 4; SQLFiddle example: http://sqlfiddle.com/#!15/1ca01/1


2

A manual vacuumdb should only be needed for special circumstances, like after running pg_upgrade or after doing some kind of bulk load or major maintenance. Otherwise, just let autovacuum do its thing. If your database is mostly idle at night, you can still do vacuumdb and it will concentrate the maintenance into the off hours so that autovacuum is less ...


4

You don't need to include the table activities in the query at all. Only messages: SELECT DISTINCT activity_id FROM messages m WHERE sender_id <= receiver_id -- reduce rows AND EXISTS ( SELECT 1 FROM messages WHERE activity_id = m.activity_id AND sender_id = m.receiver_id AND receiver_id = m.sender_id ); ...


2

This query is checking if the (sender, receiver) pair is there for the same activity: SELECT DISTINCT activity_id FROM activities a WHERE EXISTS ( SELECT 1 FROM activities WHERE activity_id = a.activity_id AND sender_id = a.receiver_id AND receiver_id = a.sender_id ...


1

There is a probably more efficient but surely less complicated way: SELECT activity_id FROM messages GROUP BY activity_id HAVING MIN(sender_id) < MAX(sender_id) ; An index on (activity_id, sender_id) would surely improve efficiency.



Top 50 recent answers are included