[GENERAL] Drop table

2004-12-06 Thread Nageshwar Rao
Title: Message Not able to drop a table,though nobody is accessing the table.I am able to insert the records and delete the records.When I give drop table it just hangs there .No error message. any specific reasons thx  

[GENERAL] Index on geometry and timestamp

2004-12-06 Thread Werdin Jens
Title: Index on geometry and timestamp Hi, I need an index on a postgis-point and a timestamp. I'm using an GiST index on the geometry. But the creation of an GiST index on geometry and timestamp seems to be impossible, because GiST doesn't support Timestamps. Is there a possibility to solv

Re: [GENERAL] Triggers don't activate when dropping table

2004-12-06 Thread cheng shan
Note: forwarded message attached. Do You Yahoo!? 注册世界一流品质的雅虎免费电邮--- Begin Message --- The reason I drop the table directly is that:   I am developing a graphical information system. There are many tables inherit from object in the system, such as "map", "line", "rect", "circle", etc. In most tim

Re: [GENERAL] Performance tuning on RedHat Enterprise Linux 3

2004-12-06 Thread Tom Lane
Neil Conway <[EMAIL PROTECTED]> writes: > As a quick hack, what about throwing away the constructed hash table and > switching to hashing for sorting if we exceed sort_mem by a significant > factor? (say, 200%) We might also want to print a warning message to the > logs. If I thought that a 200% e

[GENERAL] migrating from informix

2004-12-06 Thread Jaime Casanova
Hi, someone has successfully migrated a database from informix to postgresql? there are any tools that helps or maybe an script? regards, Jaime Casanova _ Do You Yahoo!? Información de Estados Unidos y América Latina, en Yahoo! Noticias. V

Re: [GENERAL] Performance tuning on RedHat Enterprise Linux 3

2004-12-06 Thread Neil Conway
On Mon, 2004-12-06 at 23:55 -0500, Tom Lane wrote: > Bear in mind that the price of honoring sort_mem carefully is > considerably far from zero. I'll do some thinking about disk-based spilling for hashed aggregation for 8.1 > The issue with the hash code is that it sets size parameters on the > b

Re: [GENERAL] Performance tuning on RedHat Enterprise Linux 3

2004-12-06 Thread Tom Lane
Paul Tillotson <[EMAIL PROTECTED]> writes: > Does postgres actually do multiple concurrent sorts within a single > backend? Certainly. Consider for example a merge join with each input being sorted by an explicit sort step. DISTINCT, ORDER BY, UNION, and related operators require their own sort

Re: [GENERAL] hooks for supporting third party blobs?

2004-12-06 Thread Tom Lane
Eric Davies <[EMAIL PROTECTED]> writes: > A recent project of ours involved storing/fetching some reasonably large > datasets in a home-brew datatype. The datasets tended to range from a few > megabytes, to several gigabytes. We were seeing some nonlinear slowness > with using native large obje

Re: [GENERAL] Performance tuning on RedHat Enterprise Linux 3

2004-12-06 Thread Tom Lane
Neil Conway <[EMAIL PROTECTED]> writes: > On Mon, 2004-12-06 at 22:19 -0300, Alvaro Herrera wrote: >> AFAIK this is indeed the case with hashed aggregation, which uses the >> sort_mem (work_mem) parameter to control its operation, but for which it >> is not a hard limit. > Hmmm -- I knew we didn't

Re: [GENERAL] Rules

2004-12-06 Thread Michael Fuhr
On Mon, Dec 06, 2004 at 04:15:54PM +1100, Jamie Deppeler wrote: > i have a join table eg > > primarykey > field1 > field2 > > and based on SQL Select have the values of field1 inserted into a new > table which will scroll which will launch a trigger but im having > problems getting this to wor

Re: [GENERAL] Triggers don't activate when dropping table

2004-12-06 Thread Michael Fuhr
On Tue, Dec 07, 2004 at 11:44:58AM +0800, cheng shan wrote: > I have one table named "object" and i many tables that are related > to the table "object". When a record in "object" is deleted, the > relative records in other tables should also be erased. To implement > the above goal, i define ma

Re: [GENERAL] When to encrypt

2004-12-06 Thread Christopher Browne
Why do you think that's useful in limiting vulnerability? In order for the system to mount the filesystem, the key has got to be there. If it's a "highly available" system, it's not acceptable for the system to have to wait for a sysadmin to type in a decryption key, so the key has to be sitting

Re: [GENERAL] More problems

2004-12-06 Thread Michael Fuhr
On Tue, Dec 07, 2004 at 09:44:44AM +1100, Jamie Deppeler wrote: > I have a result set returned through a function or rule, i know i will > have process the result set through a for loop, my question is how can i > get length of result set? Do you want to know how many rows a query will return?

Re: [GENERAL] When to encrypt

2004-12-06 Thread Christopher Browne
The world rejoiced as [EMAIL PROTECTED] (Martijn van Oosterhout) wrote: > On Mon, Dec 06, 2004 at 04:07:25PM -0500, Greg Stark wrote: >> By contrast, encryption is useful for non-live data such as >> database backups. This lets you take them off-site and store them >> someplace without worrying ab

Re: [GENERAL] Index bloat in 7.2

2004-12-06 Thread Christopher Browne
Quoth [EMAIL PROTECTED] (Alvaro Herrera): > On Mon, Dec 06, 2004 at 08:48:04AM -, Julian Scarfe wrote: > >> b) Only a dump-restore major version upgrade (which we'll do next time we >> can take the system out for long enough) will avoid the issue. > > "Long enough" could be a minutes or seconds

Re: [GENERAL] Performance tuning on RedHat Enterprise Linux 3

2004-12-06 Thread Paul Tillotson
Alvaro Herrera wrote: On Tue, Dec 07, 2004 at 12:02:13PM +1100, Neil Conway wrote: On Mon, 2004-12-06 at 19:37 -0500, Paul Tillotson wrote: I seem to remember hearing that the memory limit on certain operations, such as sorts, is not "enforced" (may the hackers correct me if I am wrong);

Re: [GENERAL] Detecting Temporary Tables

2004-12-06 Thread Michael Fuhr
On Tue, Dec 07, 2004 at 09:22:51AM +1100, Jamie Deppeler wrote: > Is it possible to detect the instance of a Temporary through a function? What are you trying to do? Temporary tables are stored in the pg_temp_NNN schema (e.g., pg_temp_1). See the "System Catalogs" chapter in the PostgreSQL docu

[GENERAL] Triggers don't activate when dropping table

2004-12-06 Thread cheng shan
I have one table named "object" and i many tables that are related to the table "object". When a record in "object" is deleted, the relative records in other tables should also be erased.  To implement the above goal,  i define many triggers on table "object" to make operations automatically.   But

Re: [GENERAL] Index scan vs. Seq scan on timestamps

2004-12-06 Thread Andrew - Supernews
On 2004-12-07, Stephen Frost <[EMAIL PROTECTED]> wrote: > Is there a way to say "just take the value of this function at the start > of the transaction and then have it be constant" in a query? Why not use CURRENT_TIMESTAMP, etc., which do exactly that? -- Andrew, Supernews http://www.supernews.

Re: [GENERAL] Index scan vs. Seq scan on timestamps

2004-12-06 Thread Stephen Frost
* Stephan Szabo ([EMAIL PROTECTED]) wrote: > On Mon, 6 Dec 2004, Per Jensen wrote: > > select count(*) > > from accesslog > > where time between (timeofday()::timestamp - INTERVAL '30 d') and > > timeofday()::timestamp; > > Besides the type issue, timeofday() is volatile and thus is not allowed t

Re: [GENERAL] Index scan vs. Seq scan on timestamps

2004-12-06 Thread Stephan Szabo
On Mon, 6 Dec 2004, Per Jensen wrote: > select count(*) > from accesslog > where time between (timeofday()::timestamp - INTERVAL '30 d') and > timeofday()::timestamp; Besides the type issue, timeofday() is volatile and thus is not allowed to be turned into a constant in order to do an index sca

Re: [GENERAL] hooks for supporting third party blobs?

2004-12-06 Thread Alvaro Herrera
On Mon, Dec 06, 2004 at 05:11:21PM -0800, Eric Davies wrote: > Is anyone aware of any hooks to support schemes such as ours, or has solved > a similar problem? There's RegisterXactCallback() and RegisterSubXactCallback() functions that may be what you want. They are called whenever a transactio

Re: [GENERAL] Performance tuning on RedHat Enterprise Linux 3

2004-12-06 Thread Neil Conway
On Mon, 2004-12-06 at 22:19 -0300, Alvaro Herrera wrote: > AFAIK this is indeed the case with hashed aggregation, which uses the > sort_mem (work_mem) parameter to control its operation, but for which it > is not a hard limit. Hmmm -- I knew we didn't implement disk-spilling for hashed aggregation

Re: [GENERAL] Performance tuning on RedHat Enterprise Linux 3

2004-12-06 Thread Alvaro Herrera
On Tue, Dec 07, 2004 at 12:02:13PM +1100, Neil Conway wrote: > On Mon, 2004-12-06 at 19:37 -0500, Paul Tillotson wrote: > > I seem to remember hearing that the memory limit on certain operations, > > such as sorts, is not "enforced" (may the hackers correct me if I am > > wrong); rather, the plan

[GENERAL] hooks for supporting third party blobs?

2004-12-06 Thread Eric Davies
A recent project of ours involved storing/fetching some reasonably large datasets in a home-brew datatype.  The datasets tended to range from a few megabytes, to several gigabytes. We were seeing some nonlinear slowness with using native large objects with larger datasets, presumably due to the i

Re: [GENERAL] Performance tuning on RedHat Enterprise Linux 3

2004-12-06 Thread Neil Conway
On Mon, 2004-12-06 at 19:37 -0500, Paul Tillotson wrote: > I seem to remember hearing that the memory limit on certain operations, > such as sorts, is not "enforced" (may the hackers correct me if I am > wrong); rather, the planner estimates how much a sort might take by > looking at the statist

Re: [GENERAL] Network authentication

2004-12-06 Thread Doug McNaught
Bob Parnes <[EMAIL PROTECTED]> writes: > I am having trouble connecting to a database on a debian server from a > client system, also debian. Acccording to the documentation, this is > possible without a password and offers the following example, > > hosttemplate1 all 192.168.93.0

Re: [GENERAL] Performance tuning on RedHat Enterprise Linux 3

2004-12-06 Thread Paul Tillotson
... under the periods of heavy swapping, one or more of the postgres processes would be way up there (between 500MB and 1000MB (which would easily explain the swapping)) ... the question is: why aren't all of the processes sharing the same pool of shared memory since I thought that's what I'm doin

Re: [GENERAL] 3rd RFD: comp.databases.postgresql (was: comp.databases.postgresql.*)

2004-12-06 Thread Brian {Hamilton Kelly}
On Saturday, in article <[EMAIL PROTECTED]> [EMAIL PROTECTED] "Robert McClenon" wrote: > I think that the term that is occasionally used is that the hierarchy > has a hierarchy czar. That is the most straightforward way to manage > a hierarchy. I did not say that it was the best or the

[GENERAL] Network authentication

2004-12-06 Thread Bob Parnes
I am having trouble connecting to a database on a debian server from a client system, also debian. Acccording to the documentation, this is possible without a password and offers the following example, hosttemplate1 all 192.168.93.0 255.255.255.0 \ ident sameuser My pg_h

Re: [GENERAL] SSL confirmation - (could not accept SSL connection:

2004-12-06 Thread Tom Lane
Andrew M <[EMAIL PROTECTED]> writes: > below is a breakdown of the problems i am experiencing with SSL > ... > Caused by: javax.net.ssl.SSLHandshakeException: > sun.security.validator.ValidatorException: No truste > d certificate found It would appear that either you didn't put a root certificat

Re: [GENERAL] When to encrypt

2004-12-06 Thread Greg Stark
Martijn van Oosterhout <[EMAIL PROTECTED]> writes: > Actually, hard disk encryption is useful for one thing: so if somebody > kills the power and takes the hard disk/computer, the data is safe. > While it's running it's vulnerable though... Where do you plan to keep the key? -- greg -

Re: [GENERAL] SSL confirmation - (could not accept SSL connection:

2004-12-06 Thread Andrew M
Hi Tom, below is a breakdown of the problems i am experiencing with SSL Connection String: jdbc:postgresql://localhost:5432/mydatabase?ssl my Driver is: DriverVersion PostgreSQL 8.0devel JDBC3 with SSL (build 308) The error message i'm getting is: Code: 14:12:56,779 WARN [SettingsFactory] Could n

[GENERAL] More problems

2004-12-06 Thread Jamie Deppeler
Hi, Newbie question I have a result set returned through a function or rule, i know i will have process the result set through a for loop, my question is how can i get length of result set? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postma

Re: [GENERAL] SSL confirmation - (could not accept SSL connection:

2004-12-06 Thread Tom Lane
Andrew M <[EMAIL PROTECTED]> writes: > the information I base my statements on is from the mail-list: > http://www.mail-archive.com/pgsql-bugs@postgresql.org/msg10008.html AFAICT that thread describes pilot error compounded by poor error messages from our SSL code. The error message problem, at

[GENERAL] Detecting Temporary Tables

2004-12-06 Thread Jamie Deppeler
Is it possible to detect the instance of a Temporary through a function? ---(end of broadcast)--- TIP 8: explain analyze is your friend

Re: [GENERAL] When to encrypt

2004-12-06 Thread Martijn van Oosterhout
On Mon, Dec 06, 2004 at 04:07:25PM -0500, Greg Stark wrote: > By contrast, encryption is useful for non-live data such as database backups. > This lets you take them off-site and store them someplace without worrying > about someone walking off with your entire database. Or to discard the tapes > w

Re: [GENERAL] Index scan vs. Seq scan on timestamps

2004-12-06 Thread Per Jensen
Andrew - Supernews wrote: On 2004-12-06, Per Jensen <[EMAIL PROTECTED]> wrote: Why does PG not use the index on the time column in the second select, timeofday() has been cast to a timestamp after all. "timestamp" is "timestamp without time zone" (not the most useful type in the world). Your colu

Re: [GENERAL] Auditing with shared username

2004-12-06 Thread Eric E
Well, upon further reflection, I came to this conclusion: In order to do trigger-based auditing that logs a homegrown user, you need to hand the database some token or identifier for the user that it can use to record the user into the audit log. That part is pretty straightforward: if you're n

Re: [GENERAL] Index scan vs. Seq scan on timestamps

2004-12-06 Thread Andrew - Supernews
On 2004-12-06, Per Jensen <[EMAIL PROTECTED]> wrote: > Why does PG not use the index on the time column in the second select, > timeofday() has been cast to a timestamp after all. "timestamp" is "timestamp without time zone" (not the most useful type in the world). Your column is of type "timesta

Re: [GENERAL] select single entry and its neighbours using direct-acess to index?

2004-12-06 Thread Greg Stark
Pierre-Frédéric Caillaud <[EMAIL PROTECTED]> writes: > One could always use ARRAY[name,name2] > ARRAY['b','a'] > But it is NOT index-friendly... It won't use an existing two-column index but you can create an expression index on array[name,name2] and this expression will use it. It w

Re: [GENERAL] When to encrypt

2004-12-06 Thread Greg Stark
Derek Fountain <[EMAIL PROTECTED]> writes: > On Monday 06 December 2004 12:31, you wrote: > > Derek Fountain <[EMAIL PROTECTED]> writes: > > > If another SQL Injection vulnerability turns up (which it might, given > > > the state of the website code), > > > > You will never see another SQL injecti

[GENERAL] Index scan vs. Seq scan on timestamps

2004-12-06 Thread Per Jensen
List, PG version is 7.4.2 I log apache hits to a postgres server. The table layout is as follows: apachelog=# \d accesslog Table "public.accesslog" Column | Type | Modifiers --+--+--- id | integer | n

Re: [GENERAL] SSL confirmation - (could not accept SSL connection:

2004-12-06 Thread Andrew M
Kris, the information I base my statements on is from the mail-list: http://www.mail-archive.com/pgsql-bugs@postgresql.org/msg10008.html I can confirm that Jboss does not requested a keystore, and that it is the driver which is having a problem with the format of the certificate. I can confirm th

Re: [GENERAL] SSL confirmation - (could not accept SSL connection:

2004-12-06 Thread Kris Jurka
On Mon, 6 Dec 2004, Andrew M wrote: > after much research to various mail-lists, it seems that there is a bug > in the beta versions of postgreSQL 8 which is causing the problem. Is > there anyway to create a SSLv3 specific certificate? > How about sharing a little of that research with us.

Re: [GENERAL] reclaiming diskspace bloat w/near-zero downtime

2004-12-06 Thread Richard Ellis
On Fri, Dec 03, 2004 at 09:24:48AM -0700, Ed L. wrote: > Sounds like 24x7x365 operations on a single cluster is maybe too > tall of an order under these loads. Maybe time for slony & friends > for maintenance switchovers. 24x7x365 was never the true operation point. All systems have/need regular

Re: [GENERAL] SSL confirmation - (could not accept SSL connection: sslv3 alert certificate unknown )

2004-12-06 Thread Andrew M
Hi, after much research to various mail-lists, it seems that there is a bug in the beta versions of postgreSQL 8 which is causing the problem. Is there anyway to create a SSLv3 specific certificate? regards Andrew On 6 Dec 2004, at 12:28, Kris Jurka wrote: On Mon, 6 Dec 2004, Andrew M wrote: jd

Re: [GENERAL] Performance tuning on RedHat Enterprise Linux 3

2004-12-06 Thread David Esposito
Thanks for the replies guys ... The copy of Bruce's book I was reading is at: http://www.postgresql.org/docs/aw_pgsql_book/hw_performance/node8.html and I was mistaken, it recommends 25% of physical memory be allocated to the shared cache .. Is there a better resource (even a commercial publication

Re: [GENERAL] immutable stable volatile

2004-12-06 Thread Tom Lane
"PostgreSQL general mail list" <[EMAIL PROTECTED]> writes: > if a function does insert/update/delete it needs to be stable or volatile ? > if a immutable function executes 'nextval' should itself be also volatile ? A function that has side-effects must be marked volatile; there are no exceptions.

Re: [GENERAL] When to encrypt

2004-12-06 Thread Jan Wieck
On 12/6/2004 1:33 AM, Derek Fountain wrote: On Monday 06 December 2004 12:31, you wrote: Derek Fountain <[EMAIL PROTECTED]> writes: > If another SQL Injection vulnerability turns up (which it might, given > the state of the website code), You will never see another SQL injection vulnerability if yo

[GENERAL] immutable stable volatile

2004-12-06 Thread PostgreSQL general mail list
hello, I have read the documentation couple of times and I still can not figure out the following aspects. if a function does insert/update/delete it needs to be stable or volatile ? if a immutable function executes 'nextval' should itself be also volatile ? thanks, Razvan Radu

Re: [GENERAL] Auditing with shared username

2004-12-06 Thread Eric E
Hi Ian, Thanks for the quick reply. What I'm confused about is how I let the trigger function etc. know which homegrown user it was that touched the record. Any advice? Thanks, Eric Ian Harding wrote: I have a homegrown userid/password system in a database table, and on tables I audit, I ke

Re: [GENERAL] Auditing with shared username

2004-12-06 Thread Ian Harding
I have a homegrown userid/password system in a database table, and on tables I audit, I keep the id of the last person to touch that record, and have a trigger write the changed values out to an audit table. It works fine, but of course there is some overhead involved. You can't involve postgres

[GENERAL] immutable stable volatile

2004-12-06 Thread pgsql-general
hello, I have read the documentation couple of times and I still can not figure out the following aspects. if a function does insert/update/delete it needs to be stable or volatile ? if a immutable function executes 'nextval' should itself be also volatile ? thanks, Razvan Radu ---

[GENERAL] Auditing with shared username

2004-12-06 Thread Eric E
Hi all, Like many folks who use three-tier design, I would like to create an audit trail in my Postgres database, and I would like to do so without having to create a database user for each audit. As I see it, there are two ways to do this, and I can't see a clear way to do either of them.

Re: [GENERAL] Index bloat in 7.2

2004-12-06 Thread Julian Scarfe
On Mon, Dec 06, 2004 at 08:48:04AM -, Julian Scarfe wrote: b) Only a dump-restore major version upgrade (which we'll do next time we can take the system out for long enough) will avoid the issue. On 6 Dec 2004, at 16:18, Alvaro Herrera wrote: "Long enough" could be a minutes or seconds issue i

Re: [GENERAL] pgFoundary?

2004-12-06 Thread Robert Treat
Probably just an oversight, but will be linked when we roll the new website layout with 8.0 (crosses fingers) Robert Treat On Fri, 2004-12-03 at 02:07, Jim C. Nasby wrote: > Then I guess my next question is: why isn't it linked to from > http://postgresql.org ? > > On Fri, Dec 03, 2004 at 12:35:

[GENERAL] unsubscribe

2004-12-06 Thread mjmayfield
unsubscribe

Re: [GENERAL] Performance tuning on RedHat Enterprise Linux 3

2004-12-06 Thread Alvaro Herrera
On Mon, Dec 06, 2004 at 09:08:02AM -0500, David Esposito wrote: > According to Bruce Momjian's performance tuning guide, he recommends roughly > half the amount of physical RAM for the shared_buffers ... Does he? The guide I've seen from him AFAIR states that you should allocate around 10% of ph

Re: [GENERAL] Index bloat in 7.2

2004-12-06 Thread Alvaro Herrera
On Mon, Dec 06, 2004 at 08:48:04AM -, Julian Scarfe wrote: > b) Only a dump-restore major version upgrade (which we'll do next time we > can take the system out for long enough) will avoid the issue. "Long enough" could be a minutes or seconds issue if you use Slony-I, I've heard ... (Of cou

Re: [GENERAL] triggers, transactions and locks

2004-12-06 Thread C G
> Is there a way to use locks within a trigger? My example below gives the > error: It's not the lock that's the problem I think. The begin work is failing because you can't start a transaction inside the function. Were you trying to release the lock at the commit in the function? Yes I was using

Re: [GENERAL] triggers, transactions and locks

2004-12-06 Thread Stephan Szabo
On Mon, 6 Dec 2004, C G wrote: > Is there a way to use locks within a trigger? My example below gives the > error: It's not the lock that's the problem I think. The begin work is failing because you can't start a transaction inside the function. Were you trying to release the lock at the commit

Re: [GENERAL] Performance tuning on RedHat Enterprise Linux 3

2004-12-06 Thread Tom Lane
"David Esposito" <[EMAIL PROTECTED]> writes: > New Box: > shared_buffers = 131072 (roughly 1GB) This setting is an order of magnitude too large. There is hardly any evidence that it's worth setting shared_buffers much above 1. regards, tom lane

Re: [GENERAL] select single entry and its neighbours using direct-acess to index?

2004-12-06 Thread Pierre-Frédéric Caillaud
I thought the planner had an automatic rewriter for these situations. No. There was a prior discussion of this, saying that we really ought to support the SQL-spec row comparison syntax: What I meant was that I thought the planner could rewrite : (A and C) or (A AND B) as A and (B or

[GENERAL] triggers, transactions and locks

2004-12-06 Thread C G
Dear All, Is there a way to use locks within a trigger? My example below gives the error: ERROR: unexpected error -8 in EXECUTE of query "BEGIN" CONTEXT: PL/pgSQL function "insert_into_t1" line 6 at execute statement Thanks Colin CREATE FUNCTION insert_into_t1() RETURNS trigger AS' DECLA

Re: [GENERAL] select single entry and its neighbours using direct-acess to index?

2004-12-06 Thread Tom Lane
=?iso-8859-15?Q?Pierre-Fr=E9d=E9ric_Caillaud?= <[EMAIL PROTECTED]> writes: > SELECT * FROM test WHERE (name='b' and name2>'a') OR (name>'b') ORDER BY > name,name2 ASC LIMIT 1; >> Write that WHERE clause instead as: >> WHERE name>='b' AND (name>'b' OR (name='b' AND name2>'a')) >> This is logically e

Re: [GENERAL] Performance tuning on RedHat Enterprise Linux 3

2004-12-06 Thread Martijn van Oosterhout
On Mon, Dec 06, 2004 at 09:08:02AM -0500, David Esposito wrote: > shared_buffers = 131072 (roughly 1GB) > max_fsm_relations = 1 > max_fsm_pages = 1000 > sort_mem = 4096 > vacuum_mem = 262144 > Roughly 25 - 30 connections open (mos

Re: [GENERAL] select single entry and its neighbours using direct-acess to index?

2004-12-06 Thread Pierre-Frédéric Caillaud
SELECT * FROM test WHERE (name='b' and name2>'a') OR (name>'b') ORDER BY name,name2 ASC LIMIT 1; Write that WHERE clause instead as: WHERE name>='b' AND (name>'b' OR (name='b' AND name2>'a')) This is logically equivalent, but it gives the planner a better handle on how to use an index scan to sat

Re: [GENERAL] select single entry and its neighbours using direct-acess to index?

2004-12-06 Thread Andrew - Supernews
On 2004-12-06, Pierre-Frédéric Caillaud <[EMAIL PROTECTED]> wrote: > SELECT * FROM test WHERE (name='b' and name2>'a') OR (name>'b') ORDER BY > name,name2 ASC LIMIT 1; Write that WHERE clause instead as: WHERE name>='b' AND (name>'b' OR (name='b' AND name2>'a')) This is logically equivalent,

Re: [GENERAL] When to encrypt

2004-12-06 Thread Daniel Martini
Hi, Citing Derek Fountain <[EMAIL PROTECTED]>: > Indeed, but I'm still interested in the general answer. There is no general answer. Depends on how deep you get into trouble, if the data is compromised. > The server I have been looking at was hopelessly insecure and SQL > injection is only one o

[GENERAL] Performance tuning on RedHat Enterprise Linux 3

2004-12-06 Thread David Esposito
Executive summary: We just did a cutover from a RedHat 8.0 box to a RedHat Enterprise Linux 3 box and we're seeing a lot more swapping on the new box than we ever did on the old box ... this is killing performance ... Background: Old Box: RedHat 8.0 2GB Memory Dual PIII 60

Re: [GENERAL] Delete function

2004-12-06 Thread Richard Huxton
Secrétariat wrote: Hello ! I create a delete function : CREATE FUNCTION delalpha(varchar, integer) RETURNS boolean AS 'DELETE FROM public.params WHERE soc = $1 AND numpar = $2 ; SELECT TRUE ;' LANGUAGE sql ; Is there a way to return the number of deleted row ? Thanks. If you rewr

Re: [GENERAL] select single entry and its neighbours using direct-acess to index?

2004-12-06 Thread Pierre-Frédéric Caillaud
sorry for being unclear. but you guessed right. ID is UNIQUE and and I want to select a row by its ID and also get the previous and next ones in the name, name2-order. For the selected row I need all datafields and for the next and previous I need only the ID (to have it referenced on the dat

Re: [GENERAL] SSL confirmation - No trusted certificate found

2004-12-06 Thread Andrew M
Ok, I have just looked at my postgreSQL terminal window and seen the following message: sslv3 alert certificate unknown Could this mean that the certificate is of the wrong type?? regards Andrew On 6 Dec 2004, at 12:50, Andrew M wrote: Kriss, I have implemented your earlier suggestion: sslfactory

[GENERAL] Delete function

2004-12-06 Thread Secrétariat
Hello !   I create a delete function : CREATE FUNCTION delalpha(varchar, integer) RETURNS boolean    AS 'DELETE FROM public.params WHERE soc = $1 AND numpar = $2 ;  SELECT  TRUE ;'    LANGUAGE sql ;Is there a way to return the number of deleted row ? Thanks.   Luc

Re: [GENERAL] SSL confirmation - No trusted certificate found

2004-12-06 Thread Kris Jurka
On Mon, 6 Dec 2004, Andrew M wrote: > I have implemented your earlier suggestion: > > sslfactory=org.postgresql.ssl.NonValidatingFactory > > and no error are generated, so I presume that the connection to the > database is now ssl'd. So why bother going through the headache of > creating a c

Re: [GENERAL] SSL confirmation - No trusted certificate found

2004-12-06 Thread Andrew M
Kriss, I have implemented your earlier suggestion: sslfactory=org.postgresql.ssl.NonValidatingFactory and no error are generated, so I presume that the connection to the database is now ssl'd. So why bother going through the headache of creating a certificate if I can do it like mentioned above?

Re: [GENERAL]

2004-12-06 Thread Oleg Bartunov
On Mon, 6 Dec 2004, Konstantin Danilov wrote: Hello List! PostgreSQL 8 does not correctly sort words written in cyrillic. It is a bug I suppose :) Probably, it's a problem of your setup. Here is what I have: regression=# select version(); version -

Re: [GENERAL]

2004-12-06 Thread Tino Wildenhain
Hi, Am Montag, den 06.12.2004, 15:07 +0300 schrieb Konstantin Danilov: > Hello List! > > PostgreSQL 8 does not correctly sort words written in cyrillic. It is a bug I > suppose :) No, its not a bug ;) You have to make sure you used the correct locale while you set up your database cluster (init

Re: [GENERAL] SSL confirmation - No trusted certificate found

2004-12-06 Thread Kris Jurka
On Mon, 6 Dec 2004, Andrew M wrote: > jdbc:postgresql://localhost:5432/mydatabase?ssl > > When I launch Jboss, which handles the connection to postgresql, I get > the following error: > > javax.net.ssl.SSLHandshakeException: > sun.security.validator.ValidatorException: No trusted certificate

[GENERAL]

2004-12-06 Thread Konstantin Danilov
Hello List! PostgreSQL 8 does not correctly sort words written in cyrillic. It is a bug I suppose :) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org

Re: [GENERAL] [HACKERS] DBD::PgSPI 0.02

2004-12-06 Thread Mike Rylander
On Mon, 6 Dec 2004 00:27:18 -0500 (EST), [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > Hello, > > A short note that I've updated DBD::PgSPI version 0.02 to CPAN. > > There are no new features - but the code now expects (and works with) > reasonably decent versions of perl (5.8.x) and pgsql (8.x)

Re: [GENERAL] When to encrypt

2004-12-06 Thread Geoffrey
Greg Stark wrote: Derek Fountain <[EMAIL PROTECTED]> writes: If another SQL Injection vulnerability turns up (which it might, given the state of the website code), You will never see another SQL injection vulnerability if Never say never.. -- Until later, Geoffrey ---(end

Re: [GENERAL] SSL confirmation - No trusted certificate found

2004-12-06 Thread Andrew M
Ok, spoke to quickly! I am following the example here: http://archives.postgresql.org/pgsql-jdbc/2003-08/msg00110.php for ssl support in postgreSQL jdbc (DriverVersion:PostgreSQL 8.0devel JDBC3 with SSL (build 308)). When I get to the final stage, copying the certificate into the java keystore:

Re: [GENERAL] select single entry and its neighbours using direct-acess

2004-12-06 Thread peter pilsl
Pierre-Frédéric Caillaud wrote: select id from mytable where id=45 order by name,name2; Why do you want to select id if you already know it ? Do you not want to specify a starting value for name and name2 ? I'll presume you want to select a row by its 'id' and then get the previous

Re: [GENERAL] select single entry and its neighbours using direct-acess to index?

2004-12-06 Thread Pierre-Frédéric Caillaud
select id from mytable where id=45 order by name,name2; Why do you want to select id if you already know it ? Do you not want to specify a starting value for name and name2 ? I'll presume you want to select a row by its 'id' and then get the previous and next ones in the name, name2

Re: [GENERAL] When to encrypt

2004-12-06 Thread Dominic Mitchell
On Sun, Dec 05, 2004 at 11:31:34PM -0500, Greg Stark wrote: > Derek Fountain <[EMAIL PROTECTED]> writes: > > If another SQL Injection vulnerability turns up (which it might, given the > > state of the website code), > > You will never see another SQL injection vulnerability if you simply switch

[GENERAL] select single entry and its neighbours using direct-acess to index?

2004-12-06 Thread peter pilsl
Is there an easy solution for this? I'd like to select a single entry from a table and the entries that would be previous and next given to a certain order. like select id from mytable where id=45 order by name,name2; and then I'd like to select the two entries that would come before and after a

Re: [GENERAL] Index bloat in 7.2

2004-12-06 Thread Julian Scarfe
From: "Christopher Browne" <[EMAIL PROTECTED]> > The "empty pages not reclaimed" problem is something that did indeed > get fixed in the post-7.2 days. I _think_ it was 7.4, but it might > have been 7.3. > In short, 7.4.x is indeed a good resolution to your issue. From: "Tom Lane" <[EMAIL PROTE

Re: [GENERAL] Older Windows versions

2004-12-06 Thread Richard Huxton
Leonardo Mateo wrote: Hi, I need an older version of PostgreSQL for windows since I, unfortunatelly, have to develope a Win32 application with Qt-Non Commercial, and the available drivers for Postgre are for Versions 6.x and 7.x. I went to the downloads section on postgresql.org but I couldn't find

Re: [GENERAL] When to encrypt

2004-12-06 Thread gnari
From: "Derek Fountain" <[EMAIL PROTECTED]> > [snip discussion about encrypting data] > Indeed, but I'm still interested in the general answer. The server I have been > looking at was hopelessly insecure and SQL injection is only one of its > problems. There were several other ways in! Assume, for