Tag Info

New answers tagged

0

I was wondering about the state of this sql dump if the users are still using the system pg_dump relies on PostgreSQL's MVCC support to ask PostgreSQL to take a snapshot of the database state at a given point in time. Changes made by concurrent transactions after the point where pg_dump gets its snapshot do not affect pg_dump, whether they're committed ...


0

This problem has to do with write permission on folder. Create the folder / data into PostgreSQL \ 9.2 for example, and give full permission to the user. Try reinstalling again.


0

There is a trick with distinct to get it fast using index, that you can try. It involves creating a function looking like that: CREATE OR REPLACE FUNCTION small_distinct(IN tablename character varying, IN fieldname character varying, IN sample anyelement DEFAULT '1800-01-01'::date) RETURNS SETOF anyelement AS $BODY$ BEGIN EXECUTE 'SELECT ...


0

If those writes are simple inserts, your bottleneck is very likely to be disk io. PCI flash is out of your budget, so you need good, consistent SSDs in RAID10 (or RAID0 if you don't care about your data). You'll also need to tune your DB, eg with synchronous_commit, but you probably already know that.


2

There is no difference. Three quotes from the documentation: 1) These SQL-standard functions all return values based on the start time of the current transaction: ... CURRENT_TIMESTAMP ... 2) transaction_timestamp() is equivalent to CURRENT_TIMESTAMP, but is named to clearly reflect what it returns. 3) now() is a traditional ...


0

I would suggest downloading Postgres from EnterpriseDB (http://www.enterprisedb.com/downloads/postgres-postgresql-downloads). You get a GUI installer, the installer also configures Postgis and you get StackBuilder which you can use to update to minor version updates of Postgres and components of installation (e.g. PostGIS). Disclaimer: I am not affiliated ...


3

There are really several different things in play here: Recovery to the current state if the DB server is lost. Synchronous replication is your only option here, but it comes with some risks - if the replica goes down or fails the master will stall, and if there's much latency then performance will suffer due to the need for the replica to confirm before ...


3

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


2

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


2

In think your problem is that gourmet_id is declared as CHARACTER(30). If you change it to CHARACTER VARYING(30), it should work much faster. This is what happens: -> Seq Scan on counters c (cost=0.00..250342.85 rows=2725633 width=16) (actual time=0.009..1610.743 rows=2751732 loops=1) Filter: ((counter_name)::text = 'FnfHit'::text) Rows Removed by ...


1

How do I avoid sort in the explain result? Actuall I didn't ask any sort in the SQL Statement. You've asked for rows to be aggregated. One way to do this is to sort the data set and then scan it to collapse out duplicates. This can be faster than hash aggregation, which is the other way PostgreSQL knows how to do grouping. So while you didn't ...


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 .


2

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



Top 50 recent answers are included