Tag Info

Hot answers tagged

2

You can set seq_page_cost and random_page_cost per tablespace via ALTER TABLESPACE without restarting Postgres.


2

I suggest your first option, with two improvements and some simplifications. ( SELECT 1 -- irrelevant what you select here FROM client_category_price WHERE sellable_id = '9bc202ca-f7c1-11e2-a751-062b1fc90460' LIMIT 1 -- may be redundant ) UNION ALL -- not just UNION ... UNION ALL ( SELECT 1 FROM work_order_item WHERE sellable_id = ...


2

So I found the answer for Debian at least. Hunspell is the successor of Myspell, and they ship with your distribution (in my case Debian). These are the dictionaries you can use. You can install these by executing apt-get install hunspell-en-us apt-get install hunspell-de-de These dictionaries are installed in /usr/share/hunspell or ...


1

You are not limited to just $$ you can use anything enclosed in $, e.g. $_alex_delimiter_$ insert into foo (some_column) values ($_alex_delimiter_$this is a long string that contains a single $ and two $$ and quotes '''''$_alex_delimiter_$); Just pick something that won't be part of the text.


1

I'd suspect you have standard_conforming_strings set to OFF on the 9.1 instance (meaning it was changed explicitly, since the default is ON since 9.1), and the opposite in 9.2. Demo: test=> select version(); version ...


1

If you want to condense it into a single SELECT, this would work: SELECT DISTINCT ON (coalesce(t2_id, t1_id), t2_id) t1_id, t2_id FROM t0 ORDER BY coalesce(t2_id, t1_id), t2_id, t1_id; Equivalent, except for sort order. SQL Fiddle. If you want this to be fast, I'd try a functional index: CREATE INDEX t0_func_idx ON t0 (coalesce(t2_id, t1_id), ...


1

You can put the database into recovery mode by forcing an unclean shutdown of any of the backend processes, other than the postmaster itself. For example, with kill -9. Yes, PostgreSQL restarts. That is why it going into recovery mode. Recovery mode is a part of starting up again after an unclean shutdown. If the database was idle when it crashed, then ...


1

If you want to force PostgreSQL into recovery, you can create a recovery.conf with no restore_command or streaming replication configuration set. Leave hot_standby set to off in postgresql.conf. Personally I recommend taking a base backup (pg_basebackup -X stream) and using that, rather than doing it to the original DB.


1

I now believe my question to be a duplicate of Slow ORDER BY with LIMIT. I simplified the query (taking out the complication of a join table) to what matters. Fast query explain (analyze, buffers) SELECT * FROM big_table where small_table_id = 822573 ORDER BY sort_key_1, sort_key_2, sort_key_3 Sort (cost=24642.79..24686.64 rows=17540 width=113) ...


1

For the simple case, I can only think of minor improvements to the query: ( SELECT DISTINCT ON (t2_id) t1_id, t2_id FROM t0 WHERE t2_id IS NOT NULL ORDER BY t2_id, t1_id -- to get consistent results ) UNION ALL ( SELECT DISTINCT ON (t1_id) t1_id, NULL -- cheaper FROM t0 WHERE t2_id IS NULL -- if you retrieve ...


1

It's a case-sensitivity issue. "dateType" including the quotes is a quoted identifier, which implies that it retains its case (as opposed to an unquoted identifier which is implicitly converted to lower case). Quoted identifiers allows other columns of the same table named for instance "DateType" or "datetype" or other variants that differ only by case (not ...


1

With REVOKE e.g.: REVOKE postgres FROM tester;


1

It can be done, but it's not necessarily fun, or fast. Your best bet for fuzzy matching is "Soft TFIDF" (pdf), probably using Jaro Winkler similarity. Jaro Winkler is similar to Levenshtein but weights letters more heavily at the beginning of a string. It's available in the pg_similarity extension. Here's a function I wrote a while ago, which I haven't ...



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