6,730 reputation
31437
bio website
location Berlin, Germany
age 38
visits member for 2 years, 8 months
seen 5 hours ago

Jul
11
comment Pgpooladmin pcp commands does not work
It clearly says AuthorizationError. Are you sure you give the right password (compared to pcp.conf)?
Jul
11
comment Is there a way I can use currval() without selecting it more than once?
Thanks, I keep forgetting about lastval()...
Jul
10
comment Recursive calls in stored procedure and temporary tables
Alternatively, one can simply check if the table exists and create it only if necessary. Of course, all depends on if the data inserted from the outer calls should persist for the inner calls or not.
Jul
10
comment How to log DML statements executed by a pl/pgsql function?
If you need it only in this function, you can RAISE LOG '%', your_statement; Also, I find the FOR dataset_1_row IN SELECT ... LOOP construct much smoother.
Jul
10
comment Is select * still a big no-no on SQL Server 2012?
@Lie: in our world, at least in theory (but the practice is not much behind), DBAs meet all the queries applications may issue. (Partly because about 99% of these are in PostgreSQL functions.) There is virtually no SELECT * inside, be it a simple or a very complex query.
Jul
10
comment postgres explain plan with giant gaps between operations
@JaeRae here you are: explain.depesz.com/s/Vu0 watch for the times in the 'exclusive' column and reread jjanes's and my comment.
Jul
8
comment postgres explain plan with giant gaps between operations
Try to paste your plan into explain.depesz.com It will help you understand what the times are showing - basically the time between to subsequent nodes are spent on the upper of the two.
Jul
3
comment Postgres table growing on massive updates
Actually, with any modern PostgreSQL version, the autovacuum daemon can handle this. Tuning it to match the speed of changes and being low-key enough at the same time may not be exactly straightforward, though.
Jul
3
comment How do I select the value with SERIAL data type in postgres
dba.stackexchange.com/questions/47098/… (self plug!)
Jul
3
comment Will removing a foreign key constraint affect performance?
Well, with bigger tables we more or less automatically (as in 'after getting up, I automatically drink a coffee') create indexes to support the joins and DML. This way it can be considered as a performance win :) Also, the performance difference you mention is applicable to DML only.
Jul
3
comment Postgres : Executing SELECT within a transaction does not return most recent rows
I think your problem is simply having a BEFORE trigger - it fires before the row is inserted which cannot appear in the view yet. Change it to AFTER and (after considering @CraigRinger's suggestion) you are done.
Jun
30
comment Advice to choose the best tool
Well, in 30 seconds PostgreSQL can return you all the 10M rows, joined between the two tables, I guess. There is something really bad going on. So, you have to figure out where exactly you loose all that time and do something to it. It may be the DB schema design, the lack of indexes, slow/unreliable network connection, bad application design and about a million other things.
Jun
26
comment Inner joins take too long
I guess I'd try to push the query into a CTE (WITH query), without the join on order_item_attribute, then join that table in the main query. Also, a multicolumn index on order_header (order_id, status_id) may help.
Jun
25
comment use domains in multiple schemas
Basically you have to decide the same for every object. When you don't specify the schema, the system will call search_path to help and pick the first schema from there (more or less).
Jun
25
comment use domains in multiple schemas
Are you looking for your_schema.your_domain?
Jun
25
comment Slow search times with functions despite index?
Do you actually have a reason for using a cursor? Why not simply return the results from RETURN QUERY (or a SQL function)? Google for RBAR.
Jun
20
comment Issue working with Postgres on Windows
Try to open the dump in query editor and run from there or really do it from the command line. I'm not sure what that console can or cannot do.
Jun
20
comment Issue working with Postgres on Windows
Which OS, exactly what command issued? What do you see in the dump if you visit it with an editor?
Jun
20
comment Issue working with Postgres on Windows
Do you try to run psql from pgAdmin? At least what you write suggests that. It won't work. You should run it from the command line or open the dump in the pgAdmin query tool and run it from there.
Jun
19
comment Postgres resource management
Nobody will able to answer you without knowing about your databases some details, like how many connections they get, how much memory/temp disk space they use, how they are interconnected by queries/application logic and so on. From a pure resource consumption the best thing to have is one physical machine for every single database but there are other factors, too.