delete operation with "where XXX in"

"Peter Alberer" <h9351252@obelix.wu-wien.ac.at> writes:

The following query takes quite long:
delete from lr_object_usage where lr_object_usage_id in (
select lr_object_usage_id from lr_locked_objects where timeout_time
< now() and context is not null
);

to get the rows I want to delete into a select query I can simply use

select * from lr_object_usage lrou inner join lr_locked_objects llo
on llo.lr_object_usage_id = lrou.lr_object_usage_id
where llo.timeout_time < now() ;

But how can i rephrase the delete operation to get a fast delete
operation?

If you don't mind a nonstandard query you can write

delete from lr_object_usage where
lr_locked_objects.lr_object_usage_id = lr_object_usage_id
and lr_locked_objects.timeout_time < now() ;

A difficulty with this is you can't use any alias names, but
except for cases involving self-joins you don't really need 'em.

regards, tom lane

Hello all...

I am developing a databasing system that will be used localy, but in tandem with a hosted web server.

As such, I will be implementing a local PostgreSQL server and connecting it to the internet. However, this machine ( unfortunately ) will probably also have to run the firewall as well, but that's all it will be more than likely.. database and firewall.

Ideally, I would be able to send a "REJECT" message ( via iptables ) if the connection is refused because the Database is down or somesuch, instead of just "DROP"ing the connection. This would speed up things for the web scripts when the DB is unreachable locally. However, port scans will then be able to easily figure out that I am running PostgreSQL on the standard port, presumably.

Is there a way to run Postgre on some other non-standard port? Does it do well in this regard? How would i go about doing that?

I know it won't "hack proof" anything really, just make it a bit more confusing for anyone doing port scans on my machine.

ideas?

regards,
dan

If you don't mind a nonstandard query you can write

delete from lr_object_usage where
lr_locked_objects.lr_object_usage_id = lr_object_usage_id
and lr_locked_objects.timeout_time < now() ;

Wow, i have not seen a query like that.

Thanks a lot Tom! No i dont mind as long it is that fast compared to the
other ("in") way :)

Hi Dan,

If you're going to be using PostgreSQL 7.2.2 (the latest recommended
version), then the port number is a setting you can change in your
postgresql.conf file. This file is created inside the PostgreSQL "data"
directory when you first initialise the database with "initdb".

This file is also very well commented and easy to figure out, so you'll
be fine to just open it with a text editor and make the required change,
then restart PostgreSQL. As a precaution, it's a good idea to make a
backup of your postgresql.conf file too, just in case something goes
wrong while you're editing (unlikely though).

:-)

Regards and best wishes,

Justin Clift

--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi

wouldn't it make sense to use a lower end system as your iptables gw / fw? i mean hardware is cheap, and iptables has no problems forwarding web traffic to a httpd on the iternal network that where postgres lives. why even open the database up to the general internet population when the httpd only needs to talk to it.

dan

* Dan Ostrowski (dan@triad-dev.com) wrote:

Wonderful! Thanks very much.

dan
----- Original Message -----
From: "Justin Clift" <justin@postgresql.org>
To: "Dan Ostrowski" <dan@triad-dev.com>
Cc: <pgsql-general@postgresql.org>
Sent: Monday, September 23, 2002 1:36 PM
Subject: Re: [GENERAL] Different Port for PostgreSQL?

Ones got to question why you'd have the PostgreSQL port completely open on the
external interface at all. You must know the IP address(es) of the external web
servers so just enable traffic for them.

As for doing the reject message:
1) if you haven't got a listener on a port the kernel's going to reject the
connection attempt pretty quickly
2) wrap your DB starting and stopping commands with iptable manipulation to
enable/disable the web server's traffic as appropiate

On the whole the best solution is Dan's response. You'd manipulate the firewall
rules separately to the DB scripts of course but then if you're starting and
stopping the DB I see no reason to not require manual intervention in the
firewall.

--
Nigel J. Andrews

On Mon, 23 Sep 2002, dan radom wrote:

Yes, and I replied to him. I will try to pump them to get the extra firewall
machine, but I am not sure if they will go for it. running PostgreSQL on
the firewall machine is NOT my preferred measure for sure.

But yes, that was a silly question to ask in retrospect. I will just pipe
ONLY source IPs from the webhost to the DB. Easily done. Sorry. Sometimes
you get a brain lock and you have to ask a dumb question to realize what you
are thinking of wrong.

Thanks for the responces. =)

regards,
dan
----- Original Message -----
From: "Nigel J. Andrews" <nandrews@investsystems.co.uk>
To: "dan radom" <dan@radom.org>
Cc: <pgsql-general@postgresql.org>
Sent: Monday, September 23, 2002 2:03 PM
Subject: Re: [GENERAL] Different Port for PostgreSQL?

Ones got to question why you'd have the PostgreSQL port completely open on

the

external interface at all. You must know the IP address(es) of the

external web

servers so just enable traffic for them.

As for doing the reject message:
1) if you haven't got a listener on a port the kernel's going to reject

the

connection attempt pretty quickly
2) wrap your DB starting and stopping commands with iptable manipulation

to

enable/disable the web server's traffic as appropiate

On the whole the best solution is Dan's response. You'd manipulate the

firewall

rules separately to the DB scripts of course but then if you're starting

and

stopping the DB I see no reason to not require manual intervention in the
firewall.

--
Nigel J. Andrews

On Mon, 23 Sep 2002, dan radom wrote:

wouldn't it make sense to use a lower end system as your iptables gw /

fw? i mean hardware is cheap, and iptables has no problems forwarding web
traffic to a httpd on the iternal network that where postgres lives. why
even open the database up to the general internet population when the httpd
only needs to talk to it.

dan

* Dan Ostrowski (dan@triad-dev.com) wrote:

Hello all...

I am developing a databasing system that will be used localy, but in

tandem with a hosted web server.

As such, I will be implementing a local PostgreSQL server and

connecting it to the internet. However, this machine ( unfortunately ) will
probably also have to run the firewall as well, but that's all it will be
more than likely.. database and firewall.

Ideally, I would be able to send a "REJECT" message ( via iptables )

if the connection is refused because the Database is down or somesuch,
instead of just "DROP"ing the connection. This would speed up things for the
web scripts when the DB is unreachable locally. However, port scans will
then be able to easily figure out that I am running PostgreSQL on the
standard port, presumably.

Is there a way to run Postgre on some other non-standard port? Does it

do well in this regard? How would i go about doing that?

I know it won't "hack proof" anything really, just make it a bit more

confusing for anyone doing port scans on my machine.

Hello!

On Mon, 23 Sep 2002, Dan Ostrowski wrote:

Yes, and I replied to him. I will try to pump them to get the extra firewall
machine, but I am not sure if they will go for it. running PostgreSQL on
the firewall machine is NOT my preferred measure for sure.

But yes, that was a silly question to ask in retrospect. I will just pipe
ONLY source IPs from the webhost to the DB. Easily done. Sorry. Sometimes
you get a brain lock and you have to ask a dumb question to realize what you
are thinking of wrong.

BTW, there's a cool trick: make use UNIX socket, turn off TCP port at all.
This solution is quite good for those popular solution "all-in-one" where
httpd and DBMS will reside on the same computer and has faster
performance (proven). Drawback: you cannot do pg_dump.

--
WBR, Yury Bokhoncovich, Senior System Administrator, NOC of F1 Group.
Phone: +7 (3832) 106228, ext.140, E-mail: byg@center-f1.ru.
Unix is like a wigwam -- no Gates, no Windows, and an Apache inside.