Reputation
32,625
Next tag badge:
95/100 score
27/20 answers
Badges
3 50 96
Newest
 Caucus
Impact
~1.3m people reached

3h
revised Row with latest timestamp
add tags
3h
comment Row with latest timestamp
@BasilBourque: Because you can use f_latest_when() in queries and the index definition alike and don't have to change either after changing f_latest_when(). (But you must reindex.)
6h
revised Row with latest timestamp
added 242 characters in body
6h
answered Row with latest timestamp
6h
comment Row with latest timestamp
The devil is always in the details with performance optimization. I do have general advice in the answer I am working on, too, but the best answer depends on the complete picture.
6h
comment Row with latest timestamp
I was thinking of a partial, covering index. But if your real SQL code selects a few of these columns. (being 1k - 20k in size) that option is out. A basic table definition is practically always relevant for performance questions like this. Either way, the additional info from your comment should be in the question.
8h
comment Row with latest timestamp
SELECT *? Do you really need all columns from the row? Or just a small subset? The table definition would be relevant to see width of the row and constraints and types of involved columns.
8h
revised Is it reasonable to mark all columns but one as primary key?
clean up
9h
comment Is it reasonable to mark all columns but one as primary key?
@rubik: Searching in arrays can be very efficient, but there are other concerns with (de-normalized) arrays. I added a chapter to my answer.
9h
revised Is it reasonable to mark all columns but one as primary key?
add uniqueness & arrays & search in arrays
12h
comment Simultaneous calls to the same function: how are deadlocks occurring?
Very interesting indeed. Makes sense that this works in plpgsql, too, as I remember similar plpgsql cases working as expected.
12h
comment Simultaneous calls to the same function: how are deadlocks occurring?
Fine point: ypercube actually tested a the lock in plain SQL in an explicit transaction outside a function, which is not the same as a plpgsql block.
15h
revised Is it reasonable to mark all columns but one as primary key?
added 9 characters in body
16h
comment Simultaneous calls to the same function: how are deadlocks occurring?
This is all interesting, but the message in the db logs would read something like: Process 28514 : select new_customer(decode($1::text, 'hex')); Process 28084 : BEGIN; INSERT INTO test VALUES(1); select new_customer(decode($1::text, 'hex')) While Jack just got: Process 12380: select new_customer(decode($1::text, 'hex')) Process 12379: select new_customer(decode($1::text, 'hex')) - indicating that the function call is the first command in both transactions (unless I am missing something).
16h
answered Is it reasonable to mark all columns but one as primary key?
17h
comment Simultaneous calls to the same function: how are deadlocks occurring?
I can't explain this. Daniel may have a point. Might be worth raising this on pgsql-general. Either way, are you aware of the UPSERT implementation in the upcoming Postgres 9.5? Depesz taking a look at it.
1d
awarded  Caucus
1d
comment How to cast to int array in Postgresql?
events.event_index_start and events.event_index_end must be defined as bigint accordingly.
1d
revised Is it reasonable to mark all columns but one as primary key?
edited tags
1d
comment Is it reasonable to mark all columns but one as primary key?
Closely related question (with answer) on SO: Do I need a primary key for my table, which has a UNIQUE (composite 4-columns), one of which can be NULL?. If any of the columns can be NULL, urgently consider this: dba.stackexchange.com/q/9759/3684.