Postgresql – How to apply patch on postgresql 9.2

Sorry, I don't have enough rep to participate in the comments yet, but I use PostgreSQL a lot on Linux and Windows.

I ran into this same problem the other day. I ran the 9.1.7 installer and it broke my clusters. As Mike Christensen said, the key is running pg_ctl START in a command window to get a useful error message.

I got the command to use from the Services panel. When you view properties on your cluster definition, you'll see the pg_ctl command to start the service. Copy that command and paste it into the command window, then change the "runservice" parameter to "start", and use runas.exe to launch the command as the postgres user (9.1 and below) or LOCALSERVICE account (later versions).

It should already contain the path to your cluster data directory. This is what mine looks like:

C:/PROGRA~1/PostgreSQL/9.1/bin/pg_ctl.exe runservice -N "PostgreSQL Dev Cluster" -D "C:/PROGRA~1/PostgreSQL/9.1/data"

Check the error message you get from that command, and check the database logs also.

In my case I had two problems. First, I couldn't get the installer to work until I ran it under postgres local admin account. But when I did that, my domain account no longer had privileges. I had to re-grant myself full control on PostgreSQL directory tree. ( Edit CR: Don't mess with permissions on the PostgreSQL data directory unless you know exactly what you are doing, you are likely to make any problem much worse not better.)

Then I tried starting it again and got a different message indicating port 5432 was already in use. This turned out to be true. Somehow the installer had left an orphaned postgres.exe process open on port 5432, even though the Services panel showed that service as not started.

I had to use netstat -ano to find the PID and kill the orphan from task manager. Then everything started normally.

Hope that helps.

Brian

First off, LIMIT / OFFSET without ORDER BY are of limited usefulness, since the order is arbitrary and can change any time (when VACUUM runs or when the table is manipulated in at any way). It is only somewhat reliable with read-only tables. That's fine if you don't care which rows you get back, but it may break paging.

You may be able to solve your conundrum with the good old EXISTS. PostgreSQL can stop searching for more hits as soon as the first is found - as opposed to your query with DISTINCT, where it tries to collect all matches.

It's hard to be more specific without knowing the table structure, cardinalities, index definitions and what's behind your peculiar WHERE expression. But this might just do it:

SELECT s.chem_compound_id AS chem1_0_ 
       -- 10 more columns all short varchar or date fields
FROM   simple_compound s
WHERE  EXISTS (
   SELECT 1
   FROM   compound_composition sc
   JOIN   chemical_structure   c  ON sc.chemical_structure_id = c.structure_id 
   WHERE  c.structure_id  @ ('CCNc1ccccc1', '')::bingo.sub
   AND    sc.chem_compound_id = s.chem_compound_id
   )
LIMIT  5 
OFFSET 5

This also assumes you are only interested in columns from simple_compound in the output.