I need to optimize our web service, but don't know where to begin. We're running GWT, PHP and PostgreSQL. Without even having peaked at any performance data, I'm guessing that the major optimizations are going to happen in the database. I don't know anything about restructuring the DB, nor indexing. (Don't know anything 'bout DBs really...
Hi, Is there a way to update a table in database1 from a function in database2 (both databases are on the same server)? Basically cross database update in PostgreSQL. Function is executed by a trigger but it shouldn't matter. -= edit =- I know I can make it using DBLink but I would like to modify the source database as little as possi...
In my application I have a table of application events that are used to generate a user-specific feed of application events. Because it is generated using an OR query, I'm concerned about performance of this heavily used query and am wondering if I'm approaching this wrong. In the application, users can follow both other users and group...
I am trying to run an application which has worked fine for awhile, and now when I try and run it on Mac OS X, I get SIGBUS. I can't figure out why. This is also printed on the console: Invalid access of stack red zone 0x100401730 rip=0x115ae088e If I turn off PostgreSQL, it fails with connection failed. Current thread (0x000...
I'm looking for a library based on Postgis and pgRouting that will provide driving directions as well as a route between any two given points. The second part works fine using pgRouting but can't seem to find anything that'll provide driving directions from the route output by pgRouting. Does anyone have any idea where I can find such a ...
I'm running a process that does a lot of updates (> 100,000) to a table. I have the choice between putting all the updates in a single transaction or committing transactions every 1000 or so. Ignore for the moment the case where a transaction fails and is aborted. I'm interested in the best size of transaction for memory and speed effi...
I'm getting the error message: "Invalid byte sequence for encoding "UTF8": 0x9f Ok, now I know somewhere my php app is trying to query using that 0x9f character. But I have no idea WHERE. I checked postgresql.conf but I didn't find anything like "log_on_error". There's only the log_statement parameter which causes postgres to log all s...
We are undertaking an Android project but need to connect to postgreSQL server which we use throughout our other projects. Any suggestions on a starting point for this? Is there anything out there (paid or unpaid) that will allow us to achieve this? I realize that this is a pretty general question, and although we have extensive experie...
Hi, I have a table partitioned by two columns, expired and type_id. expired is a boolean and type_id partitions by range. Table examples: mos_active_1 ... mos_active_15 mos_expired_1 ... mos_expired_15 My constraints are set up like this: ADD CONSTRAINT mos_active_1_check CHECK (expired = false AND type_id < 100 ) ... ADD CONSTRAIN...
In my Database, the images(jpeg,bmp format) are stored in bytea datatype, showing up in binary code in the database. Now I want to retrieve the images from the database. But i could not get the image in the web page. When i retrieve using this code given below, it shows the binary code value.(ie combination of numbers,characters,symbols)...
Running the following code: $preCallMatch = pg_prepare($dbcp, 'callMatch', "SELECT duration FROM voip_calls WHERE system_id = $1 AND call_start => $2 ...
I'm not sure what kind of join I need as I'm not familiar with trying to overlap data in such a way or if it's even feasible. I have two tables which both share a similar set of data and are both related to a 3rd parent table thru Room_id. I have a table called Room_rates which stores average prices for each room (room_id) +-------+--...
I have a server application, and a database. Multiple instances of the server can run at the same time, but all data comes from the same database (on some servers it is postgresql, in other cases ms sql server). In my application, there is a process that is performed which can take hours. I need to ensure that this process is only execu...
Hello, I want to know if open a transaction inside another is safe and encouraged? I have a method: def foo(): session.begin try: stuffs except Exception, e: session.rollback() raise e session.commit() and a method that calls the first one, inside a transaction: def bar(): stuffs t...
I spent over an hour today puzzling myself over a query plan that I couldn't understand. The query was an UDPATE and it just wouldn't run AT ALL. Totally deadlocked: pg_locks showed it wasn't waiting for anything either. Now, I don't consider myself the best or worst query plan reader guy, but I find this one exceptionally difficult. I'm...
I have been working on porting some Oracle DB code to Postgres, and I am noticing some nomenclature differences (Schemas vs Databases) etc. What are some good resources for dealing with these gotchas? Any tips? What needs to be kept in mind? ...
I'm trying to get my head around with the group by command, basically I'm trying to select all messages of a user, group them by subject then show them - just like how face book does it. The result should have the latest message id, the sender's id, the date, and the total count of the messages in that subject. The message table can lo...
Hello, I need to compile libpq with 10.5 for using later in the simulator. I can use the libpq in the device without problems. I'm forcing with this: make clean && ./configure CFLAGS="-arch i386 -isysroot /Developer/SDKs/MacOSX10.5.sdk" && make -C src/interfaces/libpq but I'm getting the error: ld: library not found for - This ...
In our database we have users: A, B, C. Each user has its own corresponding schema: A, B, C. Normally if I wanted to select from a table in one of the schemas I would have to do: select * from A.table; My question is: Is there a way to make: select * from table go to the correct schema based on the user that is logged in? ...
I'm in the process of creating a table and it made me wonder. If I store, say cars that has a make (fx BMW, Audi ect.), will it make any difference on the query speed if I store the make as an int or varchar. So is SELECT * FROM table WHERE make = 5 AND ...; Faster/slower than SELECT * FROM table WHERE make = 'audi' AND ...; or w...