Re: [GENERAL] Variable LIMIT and OFFSET in SELECTs

2007-11-15 Thread Tom Lane
Reg Me Please <[EMAIL PROTECTED]> writes: > create table limoff( l int, o int ); > insert into limoff values ( 10,2 ); > select a.* from atable a,limoff limit l offset o; I am truly curious what you think the semantics of that ought to be. regards, tom lane --

[GENERAL] Chunk Delete

2007-11-15 Thread Abraham, Danny
Hi, I am wondering if I can do in PG Chunck Delete, like the Oracle example below. In Oracle we erase 50,000 records using the following: Delete where and ROWNUM < 5; Do we have such a feature in PG? Thanks Danny ---(end of broadcast)-

Re: [GENERAL] Path to top of tree

2007-11-15 Thread Merlin Moncure
On Nov 13, 2007 10:54 PM, Merlin Moncure <[EMAIL PROTECTED]> wrote: > maybe or maybe not, but here is one way to do it: > > create or replace function parent(foo) returns foo as > $$ > select parent(foo) from foo where id = ($1).parent_id > union all > select $1 >

[GENERAL] implicit casting bug or feature?

2007-11-15 Thread Kristo Kaiv
During development i stumbled upon a strange behaviour in 8.2.4 Here is the case: CREATE TYPE testretval AS (tval text); CREATE OR REPLACE FUNCTION test() RETURNS testretval AS $$ DECLARE _r record; retval testretval%ROWTYPE; BEGIN SELECT 'test'::character(20) as tc INTO retval;

Re: [GENERAL] stripping HTML, SQL injections ...

2007-11-15 Thread Ottavio Campana
Alvaro Herrera ha scritto: > Martin Gainty escribió: >> this is a very simple html tag strip routine >> I dont understand what security you had in mind .. >> >> so I take it you're not a fan of dojo or GWT? > > Let's say the user disables javascript on the browser? or more easily, an attacker can

Re: [GENERAL] Variable LIMIT and OFFSET in SELECTs

2007-11-15 Thread Pavel Stehule
Hello LIMIT has impact on execution plan, so there cannot be variables. Use SRF function and dynamic statements instead. Regards Pavel On 15/11/2007, Reg Me Please <[EMAIL PROTECTED]> wrote: > In any case, what'd be the benefit for not allowing "variables" as LIMIT and > OFFSET argument? > > --

Re: [GENERAL] Windows x64 Port

2007-11-15 Thread Magnus Hagander
Willem Buitendyk wrote: > Is there any plan to port Postgresql to windows x64? I can currently > run Postgresql as 32 bit inside Vista 64 - would I see better > performance if Postgresql was running under 64 bit. My biggest concern > is memory - at 32 bit is not Postgresql limited to 4GB in windo

Re: [GENERAL] Variable LIMIT and OFFSET in SELECTs

2007-11-15 Thread Trevor Talbot
On 11/15/07, Reg Me Please <[EMAIL PROTECTED]> wrote: > In any case, what'd be the benefit for not allowing "variables" as LIMIT and > OFFSET argument? When you can fully describe the semantics of your example, you'll probably be able to answer that question too :) ---(en

Re: [GENERAL] FW: Chunk Delete

2007-11-15 Thread Alexander Staubo
[Reposting accidental private response to list] On 11/15/07, Abraham, Danny <[EMAIL PROTECTED]> wrote: > How do I add a note to a thread? You add pgsql-general@postgresql.org to To: or Cc:. Your mail program probably has a "Reply to all" button that will accomplish this. > THE problem is that th

[GENERAL] Chunk Delete

2007-11-15 Thread Abraham, Danny
Say we have a FIFO of 800,000,000 records. No primary key is required - this is only audit information. We do not use it on Oracle too. Based on a condition, 2,000,000 records should be deleted daily. We have a background process that wakes up every X minutes and deletes Y records.

Re: [GENERAL] Chunk Delete

2007-11-15 Thread Alexander Staubo
On 11/15/07, Abraham, Danny <[EMAIL PROTECTED]> wrote: > With Oracle we do it with: delete ,tname> where and rownum < Y; You could create a temporary sequence: create temporary sequence foo_seq; delete from foos where nextval('foo_seq') < 5; I'm not sure how fast nextval() is, even o

Re: [GENERAL] Chunk Delete

2007-11-15 Thread Sam Mason
On Thu, Nov 15, 2007 at 04:18:27PM +0200, Abraham, Danny wrote: > The temporary sequence works perfectly for me. You may want to read my other message and test again. You are well into implementation details here and the trick that was suggested will (with the current version of PG delete the fir

Re: [GENERAL] Chunk Delete

2007-11-15 Thread Sam Mason
On Thu, Nov 15, 2007 at 09:43:52AM -0500, Andrew Sullivan wrote: > On Thu, Nov 15, 2007 at 03:33:04PM +0200, Abraham, Danny wrote: > > > > Based on a condition, 2,000,000 records should be deleted daily. > > Why not use that condition (which I presume is indexed) as the criterion for > your dele

Re: [GENERAL] Enforcing Join condition

2007-11-15 Thread சிவகுமார் மா
On Nov 15, 2007 8:17 PM, Sam Mason <[EMAIL PROTECTED]> wrote: > As Albe suggested, a view is about all that's going to help the poor > people who work with this. When I do this sort of thing, I tend to > find that there are very few queries that actually need everything all > together in one place

Re: [GENERAL] Chunk Delete

2007-11-15 Thread Andrew Sullivan
On Thu, Nov 15, 2007 at 03:33:04PM +0200, Abraham, Danny wrote: > > Based on a condition, 2,000,000 records should be deleted daily. Why not use that condition (which I presume is indexed) as the criterion for your delete? I mean, how else are you getting those row numbers in Oracle. They have

Re: [GENERAL] Compiled debugger plug in for windows, any one have it?

2007-11-15 Thread Dave Page
Tony Caduto wrote: > I dont' have a c/c++ compiler on my PCs as I am a Delphi guy. > > Anyone have it compiled for win32 and willing to share? > > Actually I do have CodeGear C++, but it's unlikely to work with that. Just download and install the win32 distro of 8.3beta2. Regards, Dave ---

Re: [GENERAL] Enforcing Join condition

2007-11-15 Thread Sam Mason
On Thu, Nov 15, 2007 at 01:24:04PM +0530, ??? ?? wrote: > Is there a way to force join conditions in queries i.e. When a join is > made to a table on a particular field, another column should also be > checked? > > CREATE TABLE test (info_type varchar(3), info_referenc

[GENERAL] moving from mysql to postgree

2007-11-15 Thread Pau Marc Munoz Torres
Hi I'm moving from mysql to postgresql just now i I'm a bit lost, could anyone tell me some place with a comparative between postdresql and mysql commands, i think than mostly is the same think but, any way, do anything change ? pau -- Pau Marc Muñoz Torres Laboratori de Biologia Computacional

[GENERAL] PostgreSQL 8.2 and Firebird 1.5 comparative

2007-11-15 Thread Tony Caduto
I submitted this to digg. http://digg.com/software/PostgreSQL_8_2_vs_Firebird_1_5_for_Enterprise_Use Needless to say PGSQL has a lot more yes entries :-) Please give it a digg if you want. Thanks, Tony ---(end of broadcast)--- TIP 5: don't forg

Re: [GENERAL] Variable LIMIT and OFFSET in SELECTs

2007-11-15 Thread Reg Me Please
In any case, what'd be the benefit for not allowing "variables" as LIMIT and OFFSET argument? -- Reg me Please ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joinin

Re: [GENERAL] moving from mysql to postgree

2007-11-15 Thread Tony Caduto
Pau Marc Munoz Torres wrote: Hi I'm moving from mysql to postgresql just now i I'm a bit lost, could anyone tell me some place with a comparative between postdresql and mysql commands, i think than mostly is the same think but, any way, do anything change ? pau -- Hi, You should check o

Re: [GENERAL] PLpgsql debugger question

2007-11-15 Thread Tom Lane
Andrew Sullivan <[EMAIL PROTECTED]> writes: > On Wed, Nov 14, 2007 at 02:03:50PM -0500, Tom Lane wrote: >> At some point it might get integrated, but right now it seems to need >> its own release schedule. We put the core hooks in for 8.2 but the >> thing didn't actually get published for many mon

[GENERAL] Chunk Delete

2007-11-15 Thread Abraham, Danny
THE problem is that the table does not have a primary key; Too expensive.

Re: [GENERAL] moving from mysql to postgree

2007-11-15 Thread Andrew Sullivan
On Thu, Nov 15, 2007 at 04:44:33PM +0100, Pau Marc Munoz Torres wrote: > I'm moving from mysql to postgresql just now i I'm a bit lost, could anyone > tell me some place with a comparative between postdresql and mysql commands, > i think than mostly is the same think but, any way, do anything chan

Re: [GENERAL] PLpgsql debugger question

2007-11-15 Thread Andrew Sullivan
On Thu, Nov 15, 2007 at 10:41:23AM -0600, Tony Caduto wrote: > > So if you are using Oracle do you have to go through the hassle of > finding it, then compiling and installing it? This canard comes up every time we discuss keeping the codebase lean, and I say the same thing every time: Oracle (a

Re: [GENERAL] Chunk Delete

2007-11-15 Thread Alexander Staubo
On 11/15/07, Abraham, Danny <[EMAIL PROTECTED]> wrote: > In Oracle we erase 50,000 records using the following: > > Delete where and ROWNUM < 5; > > Do we have such a feature in PG? Just delete with a subselect: delete from where in ( select from order by limit 5); For examp

Re: [GENERAL] PLpgsql debugger question

2007-11-15 Thread Joshua D. Drake
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Thu, 15 Nov 2007 10:41:23 -0600 Tony Caduto <[EMAIL PROTECTED]> wrote: > > > I would have to disagree. > :) > So if you are using Oracle do you have to go through the hassle of > finding it, then compiling and installing it? That is what t

Re: [GENERAL] Enforcing Join condition

2007-11-15 Thread சிவகுமார் மா
On Nov 15, 2007 7:32 PM, Albe Laurenz <[EMAIL PROTECTED]> wrote: > > You want to have a "conditional" foreign key reference that checks > against different tables depending on a "type" field, right? > > For complicated conditions like this, you could use a > BEFORE INSERT trigger that throws an err

Re: [GENERAL] Enforcing Join condition

2007-11-15 Thread சிவகுமார் மா
On Nov 15, 2007 5:52 PM, Sam Mason <[EMAIL PROTECTED]> wrote: > What I tend to do here, is something like: > > CREATE TABLE test ( > type INTEGER, > ref1 INTEGER REFERENCES table1 CHECK ((type = 1) = (ref1 IS NOT NULL)), > ref2 INTEGER REFERENCES table2 CHECK ((type = 2) = (ref2 IS N

Re: [GENERAL] Chunk Delete

2007-11-15 Thread Abraham, Danny
The temporary sequence works perfectly for me. Thanks ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match

Re: [GENERAL] PLpgsql debugger question

2007-11-15 Thread Joshua D. Drake
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Thu, 15 Nov 2007 11:57:28 -0500 Andrew Sullivan <[EMAIL PROTECTED]> wrote: > (None of this is an argument against Tom's point that deep-in-there > hooks at least have to be shipped with the core stuff. The same is > true for replication, for insta

Re: [GENERAL] Variable LIMIT and OFFSET in SELECTs

2007-11-15 Thread Reg Me Please
Il Thursday 15 November 2007 17:55:42 Sam Mason ha scritto: > On Thu, Nov 15, 2007 at 05:34:43PM +0100, Reg Me Please wrote: > > Il Thursday 15 November 2007 14:09:16 Trevor Talbot ha scritto: > > > On 11/15/07, Reg Me Please <[EMAIL PROTECTED]> wrote: > > > > In any case, what'd be the benefit for

Re: [GENERAL] [ADMIN] "global/pg_control": Permission denied

2007-11-15 Thread Alvaro Herrera
Richard Broersma Jr wrote: > --- On Thu, 11/15/07, Richard Broersma Jr <[EMAIL PROTECTED]> wrote: > > > My desktop (acting as a db-server for my discipline's > > group) has it power cycled this morning at 4:10 > >... > > C:\Program > > Files\PostgreSQL\8.2\bin>2007-11-15 06:36:13 > > PANIC: could

Re: [GENERAL] Variable LIMIT and OFFSET in SELECTs

2007-11-15 Thread Tom Lane
Sam Mason <[EMAIL PROTECTED]> writes: > On Thu, Nov 15, 2007 at 06:08:20PM +0100, Reg Me Please wrote: >> But then why allowing the LIMIT and the OFFSET as coming from function >> argument evaluations? > I believe the query is planned by ignoring the LIMIT and OFFSET. No, it still knows there wi

Re: [GENERAL] PostgreSQL 8.2.5 compile problem

2007-11-15 Thread Tom Lane
Madison Kelly <[EMAIL PROTECTED]> writes: > gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline > -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fpic > -shared -Wl,-soname,libplperl.so.0 plperl.o spi_internal.o SPI.o > -L/usr/local/lib -L/usr/lib/perl/5.8/CORE -L../

[GENERAL] 8.3 vs 8.2 sql compatiblity issue

2007-11-15 Thread Tony Caduto
Hi, Just running some queries that have worked from 7.4 through 8.2 and they don't seem to work on 8.3. select case when a.attnum = any(conkey) then true else false end from pg_constraint where contype = 'p' and conrelid = c.oid This one is puking on a.attnum = any(conkey) returns the fo

[GENERAL] Compiled debugger plug in for windows, any one have it?

2007-11-15 Thread Tony Caduto
I dont' have a c/c++ compiler on my PCs as I am a Delphi guy. Anyone have it compiled for win32 and willing to share? Actually I do have CodeGear C++, but it's unlikely to work with that. Thanks, Tony ---(end of broadcast)--- TIP 6: explain anal

Re: [GENERAL] [ADMIN] "global/pg_control": Permission denied

2007-11-15 Thread Richard Broersma Jr
--- On Thu, 11/15/07, Alvaro Herrera <[EMAIL PROTECTED]> wrote: > What is the problem with the file? I looked at the pg_control file, postgres had the proper permission to it. > Why is it not accessible to Postgres? > My guess is that something (antivirus?) was messing with > the file when the s

Re: [GENERAL] 8.3 vs 8.2 sql compatiblity issue

2007-11-15 Thread Joshua D. Drake
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Thu, 15 Nov 2007 12:03:27 -0600 Tony Caduto <[EMAIL PROTECTED]> wrote: > I am actually getting a lot of these operator does not exist errors > in 8.3 another one I get is operator does not exist for char=integer > This appears to be a classic ex

Re: [GENERAL] 8.3 vs 8.2 sql compatiblity issue

2007-11-15 Thread Tony Caduto
Tom Lane wrote: That's no doubt got something to do with it, but I think Tony is mighty confused about exactly what is failing. pg_constraint.conkey is not text, for instance; it's smallint[] and so the quoted bit should still work just fine. I'd suggest trying the query in some client that giv

Re: [GENERAL] implicit casting bug or feature?

2007-11-15 Thread Tom Lane
Kristo Kaiv <[EMAIL PROTECTED]> writes: > During development i stumbled upon a strange behaviour in 8.2.4 > ... > Why is this still blank padded? Shouldn't a character(20) -> text > conversion > happen implicitly when the value is selected into the return type > that is declared as text? No, t

Re: [GENERAL] Chunk Delete

2007-11-15 Thread Andrew Sullivan
On Thu, Nov 15, 2007 at 03:09:10PM +0200, Abraham, Danny wrote: > THE problem is that the table does not have a primary key; Too > expensive. If the table doesn't have a primary key, you've designed it wrong. But I'd like to see any evidence you have at all that having a primary key is "too expen

Re: [GENERAL] Variable LIMIT and OFFSET in SELECTs

2007-11-15 Thread Sam Mason
On Thu, Nov 15, 2007 at 06:08:20PM +0100, Reg Me Please wrote: > Your remarks make a lot of sense. Of course. good! it's interesting to see how things like this fit together. > But then why allowing the LIMIT and the OFFSET as coming from function > argument evaluations? I believe the query is

Re: [GENERAL] Variable LIMIT and OFFSET in SELECTs

2007-11-15 Thread Tom Lane
Sam Mason <[EMAIL PROTECTED]> writes: > In relational algebra terms, try thinking about what would happen if you > did something like: > SELECT * FROM foo LIMIT val; > Where the table foo has more than one row (and val had different values > for each row). Which row would the database use? I

Re: [GENERAL] PLpgsql debugger question

2007-11-15 Thread Steve Atkins
On Nov 15, 2007, at 8:57 AM, Andrew Sullivan wrote: On Thu, Nov 15, 2007 at 10:41:23AM -0600, Tony Caduto wrote: So if you are using Oracle do you have to go through the hassle of finding it, then compiling and installing it? This canard comes up every time we discuss keeping the codebase

Re: [GENERAL] ERROR: there is no parameter $1

2007-11-15 Thread Steve Manes
Steve Manes wrote: This one has me stumped. Does anyone know under which circumstances this error would be returned by PHP's pg_query_params() even if the procedure completed without an apparent error? The procedure and PHP API code haven't changed in weeks. I started getting this after I u

Re: [GENERAL] Enforcing Join condition

2007-11-15 Thread Albe Laurenz
சிவகுமார் மா wrote: >> You want to have a "conditional" foreign key reference that checks >> against different tables depending on a "type" field, right? >> >> For complicated conditions like this, you could use a >> BEFORE INSERT trigger that throws an error when the condition >> is violated. > >

Re: [GENERAL] Variable LIMIT and OFFSET in SELECTs

2007-11-15 Thread Sam Mason
On Thu, Nov 15, 2007 at 05:34:43PM +0100, Reg Me Please wrote: > Il Thursday 15 November 2007 14:09:16 Trevor Talbot ha scritto: > > On 11/15/07, Reg Me Please <[EMAIL PROTECTED]> wrote: > > > In any case, what'd be the benefit for not allowing "variables" as LIMIT > > > and OFFSET argument? > > >

Re: [GENERAL] ERROR: there is no parameter $1 --> PLEASE IGNORE!

2007-11-15 Thread Steve Manes
Steve Manes wrote: Steve Manes wrote: This one has me stumped. Does anyone know under which circumstances this error would be returned by PHP's pg_query_params() even if the procedure completed without an apparent error? The answer was right in front of me. An (*ahem*) associate was screwin

Re: [GENERAL] Compiled debugger plug in for windows, any one have it?

2007-11-15 Thread Martin Gainty
I came across this when installing mod_php under Apache be mindful that you would need to recompile for each environment e.g. Linux Cygwin Windows Keeping in mind My windows tools are at least 10 years old If you provide the link I will try and compile it here M-- - Original Message - Fr

Re: [GENERAL] PLpgsql debugger question

2007-11-15 Thread Joshua D. Drake
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Thu, 15 Nov 2007 12:34:36 -0500 Andrew Sullivan <[EMAIL PROTECTED]> wrote: > On Thu, Nov 15, 2007 at 09:20:33AM -0800, Steve Atkins wrote: > > packages, so there are an awful lot of versions of the postgresql > > application, some very different fr

Re: [GENERAL] Enforcing Join condition

2007-11-15 Thread Sam Mason
On Thu, Nov 15, 2007 at 07:46:46PM +0530, ??? ?? wrote: > On Nov 15, 2007 5:52 PM, Sam Mason <[EMAIL PROTECTED]> wrote: > > What I tend to do here, is something like: > > > > CREATE TABLE test ( > > type INTEGER, > > ref1 INTEGER REFERENCES table1 CHECK ((typ

Re: [GENERAL] 8.3 vs 8.2 sql compatiblity issue

2007-11-15 Thread Tom Lane
Tony Caduto <[EMAIL PROTECTED]> writes: > it was this line: > case when a.attnum as text IN( select array_to_string(conkey,',') from > pg_constraint where > which is fixed by adding a cast: > case when cast(a.attnum as text) IN( select array_to_string(conkey,',') > from pg_constraint where Su

[GENERAL] Postgresql and resource isolation

2007-11-15 Thread Garber, Mikhail
Hello, We are considering using Postgresql for rather large project and I have questions about where it stands in respect to the following. Consider these two features already found in major commercial products. A) "resource governor" - the ability to configure how much resources (IO per seco

Re: [GENERAL] Request for suggestion on replication methods

2007-11-15 Thread Andrew Sullivan
On Thu, Nov 15, 2007 at 01:22:34PM -0800, Kandy Wong wrote: > Hi, > > I'm new to PostgreSQL and I'd like to know what are the replication > methods available on PostgreSQL Start by answering what you mean by "replication". I would have a look at the section on high availability in the manual t

Re: [GENERAL] Variable LIMIT and OFFSET in SELECTs

2007-11-15 Thread Reg Me Please
Il Thursday 15 November 2007 20:28:17 hai scritto: > Reg Me Please wrote: > > Sorry but I don't understand. > > > > Either the LIMIT and OFFSET are to be definitely CONSTANT or not. > > They must be constant during the execution of the query. > > > In the SQL function body the LIMIT and the OFFSET

Re: [GENERAL] Postgresql and resource isolation

2007-11-15 Thread Andrew Sullivan
On Thu, Nov 15, 2007 at 10:12:51AM -0800, Garber, Mikhail wrote: > > A) "resource governor" - the ability to configure how much resources (IO > per second, CPU slices) particular users or sessions are allowed to use PostgreSQL is not like several other commercial systems, in that it doesn't "tak

[GENERAL] Cisco MDS support

2007-11-15 Thread Ray Stell
I was pleased to read that cisco's MDS management software supports having pg in the backend: http://www.cisco.com/univercd/cc/td/doc/product/sn5000/mds9000/3_0/fmcfg/part01/gs.htm#wp521101 Supported Software Databases Oracle Database 10g Express PostgreSQL 8.2 (Windows) PostgreSQL 8.1 (Solari

Re: [GENERAL] Variable LIMIT and OFFSET in SELECTs

2007-11-15 Thread Reg Me Please
Sorry but I don't understand. Either the LIMIT and OFFSET are to be definitely CONSTANT or not. In the SQL function body the LIMIT and the OFFSET *are definitely not* CONSTANT. And the planner can do its job at best as usual. As Sam says I should be able to "put an IMMUTABLE expression into a LI

Re: [GENERAL] 8.3 vs 8.2 sql compatiblity issue

2007-11-15 Thread Alvaro Herrera
Tom Lane wrote: > Tony Caduto <[EMAIL PROTECTED]> writes: > > case when cast(a.attnum as text) IN( select array_to_string(conkey,',') > > from pg_constraint where > > Surely that's the worst bit of SQL code I've seen in awhile. Wow, you really are lucky. -- Alvaro Herrera http

Re: [GENERAL] automating backups with windows scheduled tasks and pg_dumpall

2007-11-15 Thread Tom Hart
Tom Lane wrote: Tom Hart <[EMAIL PROTECTED]> writes: Do scheduled tasks have a problem writing to files with > ? I tried an "echo this is a test test.txt" and that didn't run either. I know zip about Windows, but try using pg_dump's -f switch instead of ">" and see if it gets

Re: [GENERAL] Tom thinks it's bad code was 8.3 vs 8.2 sql compatibility issue

2007-11-15 Thread Joshua D. Drake
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Thu, 15 Nov 2007 16:17:21 -0600 Tony Caduto <[EMAIL PROTECTED]> wrote: > You guys really should keep such kind words to yourself. > Not sure how in the hell you can say its bad code when it is just a > little piece. You pasted the entire SQL st

Re: [GENERAL] automating backups with windows scheduled tasks and pg_dumpall

2007-11-15 Thread SCassidy
[EMAIL PROTECTED] wrote on 11/15/2007 02:07:07 PM: > Hello everybody. I'm having a bit of trouble automating pg_dumpall to do > nightly backups. I have a batch file whose contents are below > > SET PGPASSFILE=C:\foo\bar\PG_BACKUP\PGPASSFILE\pgpass.conf > "C:\Program Files\PostgreSQL\8.2\bin\pg_

Re: [GENERAL] stripping HTML, SQL injections ...

2007-11-15 Thread Peter Eisentraut
madhtr wrote: > Quick question, are there any native functions in PostGreSQL 8.1.4 that > will strip HTML tags, escape chars, etc? Using an SQL function to circumvent SQL injections probably isn't the wisest of ideas. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---

Re: [GENERAL] automating backups with windows scheduled tasks and pg_dumpall

2007-11-15 Thread Tom Hart
[EMAIL PROTECTED] wrote: One other oddity I ran into recently on one system only, is that a perl program (.pl extension) worked fine from the command-line, double-clicked on, etc. but in the scheduled job, I had to put "perl xxx.pl" for it to work. Maybe if all else fails, try "cmd.exe xxx.ba

Re: [GENERAL] Postgresql and resource isolation

2007-11-15 Thread Joshua D. Drake
Trevor Talbot wrote: On 11/15/07, Joshua D. Drake <[EMAIL PROTECTED]> wrote: A) "resource governor" - the ability to configure how much resources (IO per second, CPU slices) particular users or sessions are allowed to use That is controlled from the operating system. What OSes support this

[GENERAL] Question about PostgreSQL from Delphi newsgroups

2007-11-15 Thread Tony Caduto
Hi, Someone asked me a question about PostgreSQL on the Delphi newsgroups and I was not sure how to answer them: "Do you know if using PostgreSQL a query or connection can have a priority set, so it can run quicker than other queries? For example, in a POS system the reporting queries should h

Re: [GENERAL] Question about PostgreSQL from Delphi newsgroups

2007-11-15 Thread Joshua D. Drake
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Thu, 15 Nov 2007 15:00:00 -0600 Tony Caduto <[EMAIL PROTECTED]> wrote: > Hi, > Someone asked me a question about PostgreSQL on the Delphi newsgroups > and I was not sure how to answer them: > > "Do you know if using PostgreSQL a query or connecti

[GENERAL] getting the number of rows affected by a query

2007-11-15 Thread Ow Mun Heng
I'm trying to do some periodic updates from another DB and would like to know the # of updates/inserts/deletes from that job. I usually write a function which gets/uses the GETS DIAGNOSTIC ROW COUNT parameter which will tell me how many rows were affected by the query. Now, for this case, I'm not

[GENERAL] XML schema

2007-11-15 Thread Sean Davis
Sorry for the cross-post, but I wasn't sure where this should go. I have a large XML file (about 7.5Gb) that is composed of about 17 million individual records. I have successfully loaded these records as individual XML records into a table with 17M rows. However, I looked around a bit and notic

Re: [GENERAL] Variable LIMIT and OFFSET in SELECTs

2007-11-15 Thread Richard Huxton
Reg Me Please wrote: Sorry but I don't understand. Either the LIMIT and OFFSET are to be definitely CONSTANT or not. They must be constant during the execution of the query. In the SQL function body the LIMIT and the OFFSET *are definitely not* CONSTANT. And the planner can do its job at bes

Re: [GENERAL] Question about PostgreSQL from Delphi newsgroups

2007-11-15 Thread Scott Marlowe
On Nov 15, 2007 3:00 PM, Tony Caduto <[EMAIL PROTECTED]> wrote: > Hi, > Someone asked me a question about PostgreSQL on the Delphi newsgroups > and I was not sure how to answer them: > > "Do you know if using PostgreSQL a query or connection can have a > priority set, so it can run quicker than oth

Re: [GENERAL] automating backups with windows scheduled tasks and pg_dumpall

2007-11-15 Thread Tom Lane
Tom Hart <[EMAIL PROTECTED]> writes: > Do scheduled tasks > have a problem writing to files with > ? I tried an "echo this is a test > > test.txt" and that didn't run either. I know zip about Windows, but try using pg_dump's -f switch instead of ">" and see if it gets happier.

[GENERAL] Windows Installer Problems/Stall Solution Error 2769

2007-11-15 Thread Willem Buitendyk
I've just gone through the better part of a day trying to resolve the windows installer stalling out for Postgresql version 8.2.5 DEBUG: Error 2769: The faq mentions a cygwin path problem, but I did not have cygwin installed. I tried turning off all antiv-virus, firewalls and reconfiguring r

Re: [GENERAL] XML schema

2007-11-15 Thread Peter Eisentraut
Sean Davis wrote: > Is the current XML datatype (in 8.3) the direction of the > future, or is something more akin to a dedicated XML schema (I think > this is the route that Oracle has gone) going to be important? An XML schema is a specification that describes required properties of an XML docum

Re: [GENERAL] PLpgsql debugger question

2007-11-15 Thread Andrew Sullivan
On Wed, Nov 14, 2007 at 02:03:50PM -0500, Tom Lane wrote: > > At some point it might get integrated, but right now it seems to need > its own release schedule. We put the core hooks in for 8.2 but the > thing didn't actually get published for many months after that. What I'd prefer, in any case,

Re: [GENERAL] automating backups with windows scheduled tasks and pg_dumpall

2007-11-15 Thread Tom Hart
Martin Gainty wrote: Hi Tom In windows you would need to include the runas command so your bat file needs to execute the command via runas as in runas /user:postgres "bin\pg_dumpall.exe -U foo_postgres" HTH Martin Actually, schtasks.exe and the windows task scheduler both have this functionali

[GENERAL] ERROR: cache lookup failed for type

2007-11-15 Thread Kevin Martins
Hi, I've been working with SRF and i want to return a setof record but i keep getting this error ´cache lookup failed for type 18867840´ . My question is why and how to fix it. I need to do this work as quick as possible so I need all the help i can get, pleeease .I am including postg

Re: [GENERAL] Chunk Delete

2007-11-15 Thread Sam Mason
On Thu, Nov 15, 2007 at 02:46:09PM +0100, Alexander Staubo wrote: > On 11/15/07, Abraham, Danny <[EMAIL PROTECTED]> wrote: > > With Oracle we do it with: delete ,tname> where and rownum < Y; > > You could create a temporary sequence: > > create temporary sequence foo_seq; > delete from fo

Re: [GENERAL] Enforcing Join condition

2007-11-15 Thread Albe Laurenz
சிவகுமார் மா wrote: > CREATE TABLE test (info_type varchar(3), info_reference integer); > (depending on info_type, info_reference will contain key values from > different tables) > > INSERT INTO test (info_type, info_reference) values ('abc','111'); --- > 111 from tableA > INSERT INTO test (info_t

[GENERAL] ERROR: there is no parameter $1

2007-11-15 Thread Steve Manes
This one has me stumped. Does anyone know under which circumstances this error would be returned by PHP's pg_query_params() even if the procedure completed without an apparent error? The procedure and PHP API code haven't changed in weeks. I started getting this after I upgraded the database

Re: [GENERAL] Postgresql and resource isolation

2007-11-15 Thread Trevor Talbot
On 11/15/07, Joshua D. Drake <[EMAIL PROTECTED]> wrote: > > A) "resource governor" - the ability to configure how much resources > > (IO per second, CPU slices) particular users or sessions are allowed > > to use > That is controlled from the operating system. What OSes support this? --

Re: [GENERAL] automating backups with windows scheduled tasks and pg_dumpall

2007-11-15 Thread Tom Hart
Tom Hart wrote: [EMAIL PROTECTED] wrote: One other oddity I ran into recently on one system only, is that a perl program (.pl extension) worked fine from the command-line, double-clicked on, etc. but in the scheduled job, I had to put "perl xxx.pl" for it to work. Maybe if all else fails, try

Re: [GENERAL] automating backups with windows scheduled tasks and pg_dumpall

2007-11-15 Thread Martin Gainty
Hi Tom In windows you would need to include the runas command so your bat file needs to execute the command via runas as in runas /user:postgres "bin\pg_dumpall.exe -U foo_postgres" HTH Martin - Original Message - From: "Tom Hart" <[EMAIL PROTECTED]> To: "Postgres General List" Sent: Thu

Re: [GENERAL] Postgresql and resource isolation

2007-11-15 Thread Trevor Talbot
On 11/15/07, Joshua D. Drake <[EMAIL PROTECTED]> wrote: > Well I know that FreeBSD through Jails and Linux through Xen can give > you fine grained control over how much resources a particular instance > can use. I hadn't seen the new limits in jail, the CPU limiting in particular is neat. I'm no

[GENERAL] Request for suggestion on replication methods

2007-11-15 Thread Kandy Wong
Hi, I'm new to PostgreSQL and I'd like to know what are the replication methods available on PostgreSQL and what version of PostgreSQL should I install for it. Thank you very much. Kandy ---(end of broadcast)--- TIP 4: Have you searched our lis

Re: [GENERAL] ERROR: cache lookup failed for type

2007-11-15 Thread Tom Lane
"Kevin Martins" <[EMAIL PROTECTED]> writes: > I've been working with SRF and i want to return a setof record but i > keep getting this error ´cache lookup failed for type 18867840´ That seems unlikely to be related to the code you've shown us. Have you tried back-tracing from errfinish() to find

[GENERAL] Common criteria evaluation?

2007-11-15 Thread Geoff
I'm working on a database project and we're starting to look at open source alternatives to Oracle. The group I'm working for is keen to find a suitable database that has passed the common criteria evaluation at some level. I know an older version of PostgreSQL for Linux was evaluated at EAL 1 in

[GENERAL] Tom thinks it's bad code was 8.3 vs 8.2 sql compatibility issue

2007-11-15 Thread Tony Caduto
Alvaro Herrera wrote: Tom Lane wrote: Tony Caduto <[EMAIL PROTECTED]> writes: case when cast(a.attnum as text) IN( select array_to_string(conkey,',') from pg_constraint where Surely that's the worst bit of SQL code I've seen in awhile. Wow, you really are lucky.

Re: [GENERAL] Bulk Load Ignore/Skip Feature

2007-11-15 Thread Willem Buitendyk
Damn - so the unqiue contraint is still an issue. What gives? Why is it so hard to implement this in Postgresql? sigh - if only I had more time. Ow Mun Heng wrote: On Wed, 2007-11-14 at 00:02 -0800, Willem Buitendyk wrote: Perfect - that appears to be exactly what I was looking for.

Re: [GENERAL] PLpgsql debugger question

2007-11-15 Thread Joshua D. Drake
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Thu, 15 Nov 2007 11:00:36 -0500 Tom Lane <[EMAIL PROTECTED]> wrote: > Something like that could work well for modules that are moderately > independent of the core database version. I'm afraid that won't be > true for the plpgsql debugger, unfort

Re: [GENERAL] PLpgsql debugger question

2007-11-15 Thread Tony Caduto
Joshua D. Drake wrote: Don't get me wrong the debugger is certainly useful but I see no technical argument (and I am sure you will correct me if I am wrong :)) that deems it needs to be in core. Joshua D. Drake I would have to disagree. So if you are using Oracle do you have to go throu

Re: [GENERAL] Tom thinks it's bad code was 8.3 vs 8.2 sql compatibility issue

2007-11-15 Thread Stephen Frost
Tony, * Tony Caduto ([EMAIL PROTECTED]) wrote: > You guys really should keep such kind words to yourself. Not sure how in > the hell you can say its bad code when it is just a little piece. You don't > even know what it does. Erm, I'm pretty confident Tom knows exactly what it does. It's not e

[GENERAL] PostgreSQL 8.2.5 compile problem

2007-11-15 Thread Madison Kelly
Hi all, I am trying to compile PgSQL 8.2.5 (on Debian Etch, in case it matters). This is a second install for a dedicated program, which is why I am not using the binaries in the apt repositories. My problem is, 'make' is failing with: make -C pl install make[2]: Entering directory `/ho

Re: [GENERAL] PostgreSQL 8.2.5 compile problem

2007-11-15 Thread Madison Kelly
Tom Lane wrote: Madison Kelly <[EMAIL PROTECTED]> writes: gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fpic -shared -Wl,-soname,libplperl.so.0 plperl.o spi_internal.o SPI.o -L/usr/local/lib -L/usr/lib/perl/5.8/

Re: [GENERAL] Common criteria evaluation?

2007-11-15 Thread Greg Smith
On Thu, 15 Nov 2007, Geoff wrote: I know an older version of PostgreSQL for Linux was evaluated at EAL 1 in Japan. Right, by NTT: http://www.nttdata.co.jp/services/postgreSQL/english.html Note that the certified version included some small modifications, it wasn't the regular release that m

Re: [GENERAL] PLpgsql debugger question

2007-11-15 Thread Andrew Sullivan
On Thu, Nov 15, 2007 at 09:20:33AM -0800, Steve Atkins wrote: > packages, so there are an awful lot of versions of the postgresql > application, some very different from a users point of view. > > I tend to think of that as feature as much as bug, but it does > cause some confusion while trying to

Re: [GENERAL] Common criteria evaluation?

2007-11-15 Thread Tom Lane
Greg Smith <[EMAIL PROTECTED]> writes: > Right, by NTT: http://www.nttdata.co.jp/services/postgreSQL/english.html > Note that the certified version included some small modifications, it > wasn't the regular release that made it. Were those mods ever submitted upstream? r

Re: [GENERAL] Variable LIMIT and OFFSET in SELECTs

2007-11-15 Thread Sam Mason
On Thu, Nov 15, 2007 at 07:28:17PM +, Richard Huxton wrote: > Reg Me Please wrote: > >As Sam says I should be able to "put an IMMUTABLE expression into > >a LIMIT or OFFSET". And under some circumstances (SQL function > >body) it's true even with VARIABLE expressions like function call > >argum

Re: [GENERAL] [ADMIN] "global/pg_control": Permission denied

2007-11-15 Thread Alvaro Herrera
Richard Broersma Jr wrote: > --- On Thu, 11/15/07, Alvaro Herrera <[EMAIL PROTECTED]> wrote: > > > What is the problem with the file? > > I looked at the pg_control file, postgres had the proper permission to it. > > > Why is it not accessible to Postgres? > > My guess is that something (antivir

  1   2   >