Reputation
12,542
Next tag badge:
733/1000 score
223/200 answers
Badges
7 32 59
Newest
 sql
Impact
~665k people reached

Mar
10
comment how to get the start date and end date from a week number stored in a table
FYI, there are years with 53 weeks.
Mar
10
comment Why is index not used for IN expression with unnested array
Are you sure you need all columns in the query? sqlblog.com/blogs/aaron_bertrand/archive/2009/10/10/…
Mar
10
comment Where to look for possible deadlock causes?
What else is happening on the same transaction?
Mar
9
comment Where to look for possible deadlock causes?
How many rows match the WHERE condition? If more than one, you have to use an ORDER BY there - best done ordering along a unique column.
Mar
8
comment Where to look for possible deadlock causes?
So, my suggestions that you requested above: you can name your arguments directly in the argument list, this would remove the aliases from the DECLARE section. Then, as far as I can see, there is absolutely no need for using EXECUTE here - just use plain UPDATE and INSERT, referencing the values by the argument names. I'd also revisit the exception handling - unless you face concurrency, it is possibly not needed at all. The uniqueness can be checked in the INSERT itself, and the FK violation should possibly not happen at all.
Mar
8
comment Where to look for possible deadlock causes?
@AlvaroNeto what are you trying to say by that?
Mar
8
comment Where to look for possible deadlock causes?
Not to the point, but why do you use dynamic SQL there?
Mar
8
comment Where to look for possible deadlock causes?
Well, the primary way to avoid deadlocks is not to lock everything as early as possible, but to process things in a well-defined order. At least at this point Raghavendra is wrong, I think - it is easy to produce a deadlock by doing two SELECT ... FOR UPDATE statements in a different order.
Mar
3
comment PostgreSQL 9.4.6 under RHEL 7 - Extensive logfile because of log_statement_stats - Can't turn off
\drds [PATRN1 [PATRN2]] list per-database role settings - not sure why it did not show it. Currently, I have no better idea, but will probably check it later.
Mar
3
comment PostgreSQL 9.4.6 under RHEL 7 - Extensive logfile because of log_statement_stats - Can't turn off
From psql, please issue a \drds command. Do you see log_statement_stats listed?
Mar
1
comment Is there any reason to run VACUUM followed immediately by VACUUM FULL?
Please upgrade from that version to 9.3.11, especially if you have replication set up.
Feb
26
comment PostgreSQL Database not opening
Have you checked your memory settings?
Feb
26
comment Strange UTC offset time zone parsing in Postgres
Possible duplicate of "AT TIME ZONE" with zone name PostgreSQL bug?
Feb
25
comment Is it worth it to run VACUUM on a table that only receives INSERTs?
This is very interesting, and also explains some dirtied pages in certain EXPLAIN (ANALYZE, BUFFERS) outputs. But, if I understand things correctly, some of the hint bits (at least *COMMITTED` and *INVALID) can (could) already be set by the COMMIT or ROLLBACK, right?
Feb
24
comment How to deal with datetime ranges in a table with separate columns for date and time?
@AndriyM exactly. This is why I didn't delete that comment yet.
Feb
23
comment Postgresql server is running, but service appears stopped
Are you sure you are connecting to the DB you think you are connecting to?
Feb
22
comment PostgreSQL UNIQUE constraint error message
No, unless you consume them as shown in the linked page. This error message is produced by libpq, I believe.
Feb
22
comment PostgreSQL UNIQUE constraint error message
What you see as an error message is an example of a 'from the possibly-localized human-readable text of the message'. See postgresql.org/docs/current/static/…
Feb
22
comment Postgres query running faster, when running on more data
Is this always reproducible, or you see a speedup on the second run of the same query, too? If the latter, it is the effects of caching that you see.
Feb
21
comment aggregate column of nvarchar2 and obtain result in nvarchar2 too
Also, concatenating a number of potentially very long values can result in something longer than the length of the type - is it expected?