Postgresql – do to monitor “out of shared memory” issues

I usually prefer to start with lower shared buffers on both Linux and Windows, and keep work mem low also. Maintenance_work_mem can be left generally the default too. Keep in mind that too low a maintenance_work_mem may slow down db restores but it won't stop it. Generally you are better off starting low and then tweaking upward to find sweet spots than starting too high and working your way down.

Secondly you are reserving half of the memory on your box for PostgreSQL and only PostgreSQL. This can have very unfortunate side effects if you are doing anything else. Since this is Windows XP I am assuming it is not a dedicated server, and you are going to do far better with conservative settings here.

Your comment about intensive dropping and creating and the notice you received regarding increasing max_locks_per_transaction hint that you're dropping and creating many objects in the same transaction. Each of those results in a lock, which each requires a small amount of shared memory. Because of this, max_locks_per_transaction limits the number of locks you can hold within a transaction (to prevent any one transaction from using all of shared memory).

You could either increase that limit a bit (I would recommend against setting it arbitrarily large or you will run in to a separate situation of actually running out of total shared memory) or do your drops and creates either in batches of transactions or as one drop/create per transaction.

Edit: Apparently I was wrong about how max_locks_per_transaction works. From the documentation, the total number of locks available is max_locks_per_transaction * (max_connections + max_prepared_transactions) - any one transaction may hold more than max_locks_per_transaction, as long as the number of locks held everywhere is less than this total value.