We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

CLUSTER statement fails with 'server closed the connection unexpectedly'

Mike Hagan
Mike Hagan asked
on
Medium Priority
571 Views
Last Modified: 2010-08-05
The following command use to work:

wts1=# CLUSTER inv_search4_idx ON Invoice ;

But now I am getting the following error:

server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

OS Info:
  Postrgesql 7.4.7
  Fedora Core 2

The actual script looks like:

# Knock off any users.  They shouldn't be up this late!

echo
/sbin/service postgresql restart

echo
date
echo -e "\n-->Re-Clustering Orderdate Index on Invoice Table"

/usr/bin/psql -h 192.168.2.144 wts1 <<EOF
CLUSTER inv_search4_idx ON Invoice ;
VACUUM FULL ANALYZE Invoice ;
EOF

echo
date
echo -e "\nDone"

Thanks in advance

Comment
Watch Question

Top Expert 2015

Commented:
Your firewall(or NAT router) in the middle closed connection...
CERTIFIED EXPERT
Commented:
Maybe you have found a bug in PostgreSQL 7.4.7

Try instead

CREATE TABLE newtable AS
    SELECT columnlist FROM table ORDER BY columnlist;

which uses the PostgreSQL sorting code in the ORDER BY clause to create the desired order; this is usually much faster than an index scan for unordered data. You then drop the old table, use ALTER TABLE ... RENAME to rename newtable to the old name, and recreate the table's indexes. However, this approach does not preserve OIDs, constraints, foreign key relationships, granted privileges, and other ancillary properties of the table — all such items must be manually recreated.

Maybe you could upgrade to the latest version of PostgreSQL and the problem MAY go away.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Mike HaganConsultant

Author

Commented:
To gheist:

I thought of this.  The sql fails on the local machine.
Mike HaganConsultant

Author

Commented:
To earthman2:

I am getting ready to upgrade to Fedora Core 4 ... I'm not quite ready to jump to 5.  I have scripts that create my index and reset the OID.  I can use your solution in the interim without much effort.

Thanks
Top Expert 2015

Commented:
If you can get new machine then try any distribution with 8.x.y, if it works prepare to jump.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.