Thread: delete operation with "where XXX in"
Hi,
is there a way to speed up a delete operation that currently uses a „where XX in” clause?
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? I tried also to use “exists” instead of “in” but that did not help either.
Many TIA,
peter
"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 noproblems forwarding web traffic to a httpd on the iternal network that where postgres lives. why even open the databaseup 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.. databaseand firewall. > > Ideally, I would be able to send a "REJECT" message ( via iptables ) if the connection is refused because the Databaseis down or somesuch, instead of just "DROP"ing the connection. This would speed up things for the web scripts whenthe DB is unreachable locally. However, port scans will then be able to easily figure out that I am running PostgreSQLon 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 doingthat? > > 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
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? > 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 > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html
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 hasno problems forwarding web traffic to a httpd on the iternal network that where postgres lives. why even open the databaseup 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.. databaseand firewall. > > > > Ideally, I would be able to send a "REJECT" message ( via iptables ) if the connection is refused because the Databaseis down or somesuch, instead of just "DROP"ing the connection. This would speed up things for the web scripts whenthe DB is unreachable locally. However, port scans will then be able to easily figure out that I am running PostgreSQLon 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 aboutdoing 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 > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >
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. > > > > > > ideas? > > > > > > > > > regards, > > > dan > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster
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.