Tag Info

Hot answers tagged

4

If possible, just store the first value It doesn't look like you need to store two numbers at all. If these are like tax years, i.e. "2014/2015 financial year", you only need to consistently store one part. If you store 2014, your app knows it's the academic year beginning in 2014. So it can display "2014/2015 Academic Year" with no ambiguity. If you ...


2

If I run your example with a cold cache for both queries, then the bitmap index scan really does win out. So in this sense, the planner is getting it correct. Since you are testing under a perfectly hot cache, the correct thing to do would be to lower the random_page_cost and the seq_page_cost both to zero, and if I do that it does select the index scan ...


2

Using @cha's schema, here's how to do it in PostgreSQL 9.4 using support for unnest ... with ordinality: SELECT array_agg(unnest_a.unnest_a + unnest_b.unnest_b ORDER BY unnest_a.ordinality) FROM t, LATERAL unnest(a) WITH ORDINALITY AS unnest_a INNER JOIN LATERAL unnest(b) WITH ORDINALITY AS unnest_b ON (unnest_a.ordinality = ...


2

I would use unnest together with array_agg, like this: SQL Fiddle PostgreSQL 9.3.1 Schema Setup: create table t ( A double precision[5], B double precision[5]); insert into t values ('{3,2,0,3,1}', '{1,0,3,2,5}'); Query 1: with c as( select unnest(a) a, unnest(b) b from t) select array_agg(a) a, array_agg(b) b, array_agg(a + b) c from c Results: | ...


2

You could use two smallint columns: create table foo ( from_year smallint not null, to_year smallint not null, check (from_year = to_year - 1 and from_year >= extract(year from current_date)), unique (from_year, to_year) ); insert into foo values (2014,2015);


2

Unless the DB is huge, your best bet will be to clone it periodically. Here's what I'd do if you're on PostgreSQL 9.3, which supports cascading replication: On the live server, add a streaming replica that keeps an up to date copy on the dev server. You don't use this DB directly, it's just there so you can copy the DB without disrupting the master. Use ...


1

Use a windowing function. select * from ( select person, date, priority, row_number() over(partition by person order by date desc,priority desc) as p from table ) as foo where p=1; SQL Fiddle link for you to play with.


1

This is a psql issue. psql is waiting for you to enter something on its command line. It only checks for notifications from the server when it has a reason to contact the server, and you haven't given it a reason. They could have implemented it differently, so that it uses a select loop to wait on either the keyboard or the server, whichever becomes ...


1

The usual solution is to authenticate the user within the web app, then issue a SET ROLE or SET SESSION AUTHORIZATION to "become" the user on a JDBC session that's already authenticated with the database using a fixed username. In both cases the DISCARD ALL command that should be run by any connection pool when returning connections to the pool will ...


1

VACUUM can only remove dead tuples which are long-dead, that is, dead to all possible uses. If you have long-lived transactions, they may prevent the recently-dead tuples from being removed. This is an example of a situation where a long-lived transaction prevented removal: INFO: "pgbench_accounts": found 0 removable, 2999042 nonremovable row versions in ...


1

I find that if I try to write out what my query is doing, it tends to illuminate the answer -- for everything that is in payment -- that doesn't have a refund date and doesn't have a deleted at -- And the payments have a subscription and a user -- give me their subscription id and the count of subscriptions SELECT COUNT(subscription.user_id) as cnt , ...


1

I ended up here with very same error, which was occuring rarely, and was hard to track, because I was loking for it not where I should. Fault was JS repetition which was doing the POST to the server twice! So sometimes it is worth to have a look not only on your django (or any other web framework) views and forms but also what happens on very front side.


1

This query will do. The trick is to use COUNT(DISTINCT city_id): SQL Fiddle PostgreSQL 8.3.20 Schema Setup: CREATE TABLE Table1 ("id" int, "name" varchar(6), "language" varchar(1), "dialect" varchar(2), "city_id" int) ; INSERT INTO Table1 ("id", "name", "language", "dialect", "city_id") VALUES (01, 'London', 'A', 'A1', 1), (02, 'London', ...


1

Your comment about intensive dropping and creating and the notice you received regarding increasing max_locks_per_transaction hint that you're dropping and creating many objects in the same transaction. Each of those results in a lock, which each requires a small amount of shared memory. Because of this, max_locks_per_transaction limits the number of locks ...


1

After research more about this topic the answer is, no, I can't, the repmgr way to work allows me to have pgbouncer connecting to master instance of postgres to handle writes and allowing to use slaves to perform reads. I order to archive the goal exactly like I asked I must use pgbouncer for connection pooling and use pgpool2 and its Statement-Based ...


1

You cannot revoke privileges that are not granted. The table (not database) owner implicitly has full rights on the table. They cannot be revoked, except by changing the owner of the table. What you probably want to do is connect with a user other than the owner of the database/tables, and GRANT that user only the rights it should have over the table(s).


1

Assuming the psql command-line tool, you may use \copy instead of copy. \copy opens the file and feeds the contents to the server, whereas copy tells the server the open the file itself and read it, which may be problematic permission-wise, or even impossible if client and server run on different machines with no file sharing in-between. Under the hood, ...



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