Re: [GENERAL] duplicate key violates unique constraint

and update it to the highest value in use if it's too low. You should only need to do that once. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World

Re: [GENERAL] vista

too. It is not much harder to say We currently don't have the resources to look into that, if you could be so kind to experiment a bit and see if you can get it to work It might even invite other readers of this ML to look into it instead. Regards, -- Alban Hertroys [EMAIL PROTECTED

Re: [GENERAL] unique key issue

your problem by creating 2 unique constraints: CREATE UNIQUE INDEX idx1 ON table (col1, col2) WHERE col2 IS NOT NULL; CREATE UNIQUE INDEX idx2 ON table (col1) WHERE col2 IS NULL; Regards, -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I

Re: [GENERAL] After Trigger

the trigger. An alternative approach would be to use a permanent table, fill it within your transaction and trunk it eventually. To other transactions there'll never be any data in it, and you lose the overhead of creating and dropping the table (replacing it by trunking...). -- Alban

Re: [GENERAL] in failed sql transaction

thing; something went wrong, queries after the error may very well depend on that data - you can't rely on the current state. And it's what the SQL specs say too, of course... [1] I'm not trying to imply that what PostgreSQL does is (in general). -- Alban Hertroys [EMAIL PROTECTED

Re: [GENERAL] postgresql ddl scripts - drop object fails entire script

is available, maybe wrapping your DDL statements in a pl/pgsql SP will work. pl/pgsql has exceptions... I haven't tried this, but I expect it will work. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK

reply-to address broken (Was: Re: [GENERAL] postgresql ddl scripts - drop object fails entire script)

[EMAIL PROTECTED] wrote: On the one hand I like how the schema scripts fail when there is a single problem with a DDL statement. Your mail address bounces. Unfortunately my Trash is broken, so I can't show you the error; I was a bit quick deleting it. -- Alban Hertroys [EMAIL PROTECTED

Re: [GENERAL] Dead Lock problem with 8.1.3

Kai Hessing wrote: Alban Hertroys wrote: SELECT s.sid FROM stud s, stud_vera v WHERE s.sid = v.sid AND v.veraid = 34 AND s.sid NOT IN ( SELECT sid FROM stud_vera WHERE veraid = 2 ); I'm pretty sure it's not a deadlock. It probably takes very long for some reason; maybe an explain of that query

[GENERAL] test

Mail from this ML doesn't seem to arrive at our office anymore (you haven't been silent for 4 days, have you?). Hence a small test. Sorry for the inconvenience. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl

Re: [GENERAL] Intentionally produce Errors

function and putting that in a recognizable place in your exception text. Not pretty, but it should do the job. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your

Re: [GENERAL] plpgsql handling a set of values

structures, and probably makes your problem much simpler. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast

Re: [GENERAL] Can a function determine whether a primary key constraint

: ON DELETE CASCADE; UPDATE x SET x_id = DEFAULT; COMMIT; -- after you checked the results How to determine whether a table has a PK was already explained. Regards, -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416

Re: [GENERAL] A query planner that learns

-data, like the version of PostgreSQL, is probably required as well. The current statistics contain some of this information, but from reading this list I know that that's rarely enough information to determine an error made by the planner. Regards, -- Alban Hertroys [EMAIL PROTECTED

Re: [GENERAL] more anti-postgresql FUD

problem (the transaction is gone after the first page). I believe, as a result of this, it is not uncommon to pass the primary key id's of all results on in a hidden field, so they are available for quick querying on proceeding pages. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T

[GENERAL] Replicating changes

. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 5: don't forget to increase your

Re: [GENERAL] Replicating changes

), but that's something beyond our control. Regards, -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast

Re: [GENERAL] Trouble with plpgsql generic trigger function using

') AND (TG_WHEN = 'BEFORE')) THEN IF (SELECT COUNT(*) FROM text(TG_RELNAME)) 4 You'll want to DECLARE an integer variable and use SELECT INTO with it. And EXECUTE, as mentioned. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0

Re: [GENERAL] postgres import

it (at least I couldn't make it to). To load 1,5M rows (~230MB of INSERT statements), INSERT statements? You dumped with the -d flag, didn't you? Otherwise you'd have seen COPY statements instead, which are much faster (and of which much fewer are necessary, usually). -- Alban Hertroys [EMAIL PROTECTED

Re: [GENERAL] stored procedure / Function

variable an alias though: DECLARE TypeArt ALIAS FOR TypeOfArticle; You could also move the assignment into the body of the function. Although I wonder why you don't just use the IN parameter. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0

Re: [GENERAL] FOR ... IN

(TypeOfArticle varchar) RETURNS SETOF public.active_articles AS $body$ DECLARE TypeArt VARCHAR := $1; rec RECORD; res active_articles; /**/ BEGIN -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I

Re: [GENERAL] FOR ... IN

count comments, if not, i consists of last line of my SELECT command == AND articles.validity_period_end now() Line 17 is your first (faulty) assignment from myrec. Line 1 is the line containing 'DECLARE'. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31

Re: [GENERAL] planer picks a bad plan (seq-scan instead of index)

of the indices but rather chooses to do 2 seq-scans. SELECT * FROM shop.dvds LEFT JOIN oldtables.movies ON mov_id = dvd_mov_id LEFT JOIN shop.data_soundmedia ON sm_info_ean = dvd_ean Make sure you have indexes on both sm_info_ean and dvd_ean. Regards, -- Alban Hertroys [EMAIL

Re: [GENERAL] Table design - unknown number of column

of the flags can be grouped together? -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 6

Re: [GENERAL] Why overlaps is not working

whether it is inclusive or exclusive (the latter apparently). How to make overlaps to return correct result? select 1 where ('2006-10-30'::date, '-12-31'::date) OVERLAPS ('2006-10-16'::DATE, '2006-10-31':: DATE); ?column? -- 1 (1 row) -- Alban Hertroys [EMAIL PROTECTED

Re: [GENERAL] Why overlaps is not working

the boundary dates? Like so; select 1 where ('2006-10-31'::date -1, '-12-31'::date +1) OVERLAPS ('2006-10-16'::DATE -1, '2006-10-31':: DATE +1) Regards, -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus

[GENERAL] ROWTYPE initialization question

this, but I'd like to be sure. IF previous IS NOT NULL THEN -- Compare previous and current column values END IF previous := current; END LOOP; END; Thanks in advance, -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F

Re: [GENERAL] Why overlaps is not working

infinity, hence the requirement to cast to timestamps. That'd mean something along the lines of: where (a::timestamp, coalesce(b, 'infinity')::timestamp) overlaps (c::timestamp, coalesce(d, 'infinity')::timestamp) -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F

Re: [GENERAL] Why overlaps is not working

'20060102'::timestamp, coalesce(NULL, 'infinity'::timestamp)) Which returns true. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World

Overlap flags (Was: Re: [GENERAL] Why overlaps is not working)

? -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ

Re: [GENERAL] ROWTYPE initialization question

Jim C. Nasby wrote: On Thu, Nov 09, 2006 at 04:37:23PM +0100, Alban Hertroys wrote: 'lo list, I have a plpgsql SP where I loop through a cursor. I have an internal variable that keeps the previous row, so that I can compare it with the current row in the cursor. Like so; DECLARE

Re: [GENERAL] copy template X - Y

to generate your schema contents. Don't forget to set your search_path before executing it though. Something like: BEGIN; CREATE SCHEMA test; SET search_path TO test; \i schema_template.sql -- And if you're satisfied with the results COMMIT; -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T

Re: [GENERAL] SQL subquery question

Rick Schumeyer wrote: foreach f in tsubset update tsubset set k=(select k from t, tsubset where t.f=f); end Can this be done with one SQL statement? I think you mean update tsubset set k = t.k from t where t.f = f; -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0

Re: [GENERAL] ORDER BY

order: select 1 AS sort_key, * from foo where name != 'Other' union all select 9 AS sort_key, * from foo where name = 'Other' order by sort_key; Regards, -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416

Re: [GENERAL] Multiple currencies in a application

think you'll need any functions, unless to retrieve real-time conversion rates somehow. Otherwise a cron job will do nicely. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede

Re: [GENERAL] ISO week dates

remember much of the original discussion (yeah, I know, archives...). -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast

Re: [GENERAL] ISO week dates

Brendan Jurd wrote: On 11/23/06, Alban Hertroys [EMAIL PROTECTED] wrote: Bruce Momjian wrote: Peter Eisentraut wrote: Brendan Jurd wrote: * add an ISO day format pattern to to_char() called 'ID', which starts at Monday = 1, and * add an ISO year field to extract() called 'isoyear

Re: [GENERAL] IN clause

by C.J.Date. As an example, NULL = NULL and NULL IS NULL; have two different results (NULL and true respectively). You'll also find that concatenation 'a' || NULL results in NULL. The same goes for IN (...). -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0

Re: [GENERAL] indexes

a handfull of people modifying data, while there are many people requesting it. Regards, -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World

Re: [GENERAL] Extract between year *and* month

','2006-12-31'::timestamp) order by recall_date Fantastic. Thank you to Matthias and Russell - everything is working perfectly! -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede

Re: [GENERAL] Why overlaps is not working

Jim Nasby wrote: I know that numeric supports +/- infinity; I don't remember off-hand if timestamps have that as well. timestamps do, but dates don't. -- Alban Hertroys [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our

Re: [GENERAL] sequence help

could probably use generate_series(). Regards, -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast

Re: [GENERAL] Stripping kernel FreeBSD - postgres

a few FreeBSD users among the people here and I've always kinda disliked system administration, so I suspect I'm not the best candidate for answering your question :P -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl

Re: [GENERAL] sudden drop in delete performance

will help a bit. but I am doing the same here also (i mean with Postgres 8.1.5) -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World

Re: [GENERAL] PG Admin

the construction phase to order by, - generate the real numbering in your client application based on that order, - store the generated numbers instead of the sequence values once the construction phase is finished. PS. please don't top post, I had to reconstruct your message to let it make sense. -- Alban

Re: [GENERAL] From Clause

; Bob Pawley -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 6: explain

Re: [GENERAL] dynamic SQL - variable substitution in plpgsql

:= 40; END IF; -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 1: if posting

Re: [GENERAL] How to use outer join in update

t1 set f1=t2.f3 from t2 where f2 = t2.f4 -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast

Re: FW: [GENERAL] Male/female

H.J. Sanders wrote: We have done it with a integer whereby 0 = woman 1 = man also self-documenting :-) Why not use unicode symbols 0x2640 and 0x2642? -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus

Re: [GENERAL] A VIEW mimicing a TABLE

. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 3: Have you checked our

Re: [GENERAL] dynamic plpgsql question

of columns. You can use that string in your dynamic query. Cheers. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast

Re: [GENERAL] MySQL drops support for most distributions

Joshua D. Drake wrote: FreeBSD (Stable releases only) I suppose you meant stable _and_ releases? ;) -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World

Re: [GENERAL] Stored Procedure and Trigger they puzzle me

is your friend -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 3: Have you

Re: [GENERAL] temp tables and function performance

. That'd take away the need for this SP (which I think is actually STABLE instead of VOLATILE). -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World

Re: [GENERAL] Stored Procedure and Trigger they puzzle me

. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede

Re: [GENERAL] Autovacuum Improvements

performance after the maintenance window will probably be better. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast

Re: [GENERAL] select union with table name

a good excuse for not using UNION ALL here ;) -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast

Re: [GENERAL] [ADMIN] OUTER JOIN IS SLOW

on a date field; only 366 records). As mentioned, ANALYZE helps, but you may want to check: 1) Are you comparing integers as text strings? You may not want to. 2) Do you have an index on cusip? You probably have, just in case. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0

[GENERAL] Operator performance question

Opteron64x2, 4G RAM and some kind of RAID setup (software, don't know what type) running in a Xen host - it's our development DB-server. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede

Re: [GENERAL] Operator performance question

don't think you can have multiple HAVING clauses... The crux of the query is that I get all kinds of conditions counted - with a single query, output as a single record. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl

Re: [GENERAL] Operator performance question

Tom Lane wrote: Alban Hertroys [EMAIL PROTECTED] writes: My conclusion is that this query time is mostly limited to the somewhat complex COUNT expressions. Is there any way to do this more efficiently? Offhand I would bet on the bitstring-AND operations being the bottleneck; you could test

Re: [GENERAL] TRIGGER BEFORE INSERT

order. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 4: Have you searched our

Re: [GENERAL] Optimize expresiions.

your functions can be marked STABLE or even IMMUTABLE? That should help the planner evaluate them less often. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World

Re: [GENERAL] error in open cursor

foreign_keys where pk_column=columnname); var_str1 varchar(300); BEGIN open curforeign(par_colname); Postgres doesn't know what curforeign is, it does however know what curfroeign is ;) -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0

Re: [GENERAL] Performance with very large tables

records) in 5 usec average, so that looks reasonable to me (apples and oranges, I know). -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World

Re: [GENERAL] check table existence...

. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ

Re: [GENERAL] check table existence...

and schemaname = $2; RETURN FOUND; END; $$ LANGUAGE 'plpgsql' I'm pretty sure that should work. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World

Re: [GENERAL] Autovacuum Improvements

the treshold into the direction of the still running queue if the other queue finishes before the still running one? This would achieve some kind of auto-tuning, but that is usually tricky. For example, what if one of the queues got stuck on a lock? -- Alban Hertroys [EMAIL PROTECTED] magproductions

Re: [GENERAL] Installing Postegres side-by-side with M$ SQL server

/supported-platforms.html) I only know the name and that it's supposedly rather reliable. Is it much different from your average UNIX? -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede

Re: [GENERAL] Installing PostgreSQL under Cpanel

start from your interactive shell. Using cmd.exe, you probably need to set your PATH to where createdb.exe(?) and createuser.exe(?) were installed. creating users, testing things out? http://www.postgresql.org/docs/8.2/static/app-createuser.html See above. -- Alban Hertroys [EMAIL PROTECTED

Re: [GENERAL] Loop plpgsql recordset

sulyozas_futamido sf WHERE sf.termekfajta_id= a_termekfajta_id AND sf.marka_id=a_marka_id; -- Work here with the old recordset END LOOP; DELETE FROM sulyozas_futamido; END; -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0

Re: [GENERAL] sequence skips 30 values, how?

inserted - or more accurately, if nextval() was called 30+ times on that particular sequence - of course the sequence skips 30+ values. Nothing strange about that. The backend crashes from something you do in that session. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0

Re: [GENERAL] PG Email Client

in the right box from my client. The quarantine box could work similarly. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end

Re: [GENERAL] Defining and Using variables in a postgres function

fired on table xyz. Yes indeed, like this: DECLARE x int; BEGIN SELECT INTO x MAX(id) + 1 FROM xyz; INSERT INTO tbl_email(option_public, agency , id) VALUES ($1,$2, x) ; -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I

Re: [GENERAL] Defining and Using variables in a postgres function

a sequence? Regards, -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 3: Have you

Re: [GENERAL] a JOIN on same table, but 'slided over'

.id influences subquery (with t3), and the result influences back the selection of t1 set? Will greatly apreciate that. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede

Re: [GENERAL] AutoVacuum Behaviour Question

that way (ON COMMIT DROP). -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 9

Re: [GENERAL] query to match '\N'

looking for NULLs it may be better to query for col2 IS NULL. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast

Re: [GENERAL] query to match '\N'

Nis Jørgensen wrote: Alban Hertroys skrev: Presumably he wanted col2 like E'%N%'. But doesn't \N mean NULL, or would the OP be looking for literal '\N' strings in his data? Because if he's looking for NULLs it may be better to query for col2 IS NULL. My guess is that this string

[GENERAL] psql 8.2 client vs pg 8.1 server problem

database select version(); version - PostgreSQL 8.1.4 on x86_64-pc-linux-gnu, compiled by GCC cc (GCC) 3.3.5 (Debian 1:3.3.5-13) (1 row) -- Alban Hertroys [EMAIL

Re: [GENERAL] psql 8.2 client vs pg 8.1 server problem

Dave Page wrote: Alban Hertroys wrote: We have psql 8.2 clients on our workstations, while we still have pg 8.1 on our development and production servers. This causes problems like the following: database \d table ERROR: column i.indisvalid does not exist database We can log

Re: [GENERAL] What do people like to monitor (or in other words, what might be nice in pgsnmpd)?

an arbitraty amount of time to complete. Maybe per user/database? I suppose this number is only interesting on an uncongested database server. Otherwise there will be queries passing that treshold that normally wouldn't, because they have to wait for the real troublemakers to finish. -- Alban

Re: [GENERAL] Modified FIFO queue and insert rule

use a trigger (a before one maybe) instead of a rule. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast

Re: [GENERAL] LIKE conditions in PGSQL very, very slow!

a sequential scan will be faster. You probably want to become acquainted with autovacuum. Another possibility is that most of your customers names start with 'eri', in which case a seq scan is actually faster... In that case you should probably do something about your customer base ;) Regards, -- Alban

Re: [GENERAL] Moving to postgresql and some ignorant questions

(or rollback) if you like the end result (or not). I believe the only exception to that rule is CREATE DATABASE. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World

Re: [GENERAL] [RESEND] Transaction auto-abort causes grief with Spring Framework

of try {...} catch (...) {...}, but it shouldn't be too hard to wrap it somehow for exceptions in database code. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your

Re: [GENERAL] [RESEND] Transaction auto-abort causes grief with Spring Framework

application will need twice as many connections that way, though... In that case you shouldn't commit records on the 'normal' connection before the audit records have been committed I suppose? -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I

Re: [GENERAL] Repeat posts

else has noticed it. I just finished going through my new mail since this morning, which contained several fresh duplicates of messages I already read. So yes, it happens to me too. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I

Re: [GENERAL] Converting non-null unique idx to pkey

as well try replicating to a newer release and swap them around once it's done. I've seen that method of upgrading mentioned on this list a few times. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416

Re: [GENERAL] PostgreSQL vs Firebird feature comparison finished

FreeBSD should be added to that list as well... They've been bench marking their threading support using multi-threading in MySQL (not for the db, mind you - just for load ;), and it performs really well. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0

Re: [GENERAL] PostgreSQL vs Firebird feature comparison finished

Dave Page wrote: Alban Hertroys wrote: Joshua D. Drake wrote: I agree with you on the multi-threaded. I think I will add a note saying the the multi-threaded architecture is only advantageous on Windows. And Solaris. I'm not entirely sure what makes multi-threading be advantageous

Re: [GENERAL] SQL Diff ?

that have been changed, modified in table1 since the initial laod from table1 into table2? I think you could get smart having a few rules for insert/update/delete on 'table' that keep track of what happens during your work on table2. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T

Re: [HACKERS] [GENERAL] Undetected corruption of table files

mechanisms. In the worst case (all the above mechanisms fail), you have backups. IMHO the problem is covered quite adequately. The operating system and the hardware cover for the database, as they should; it's _their_ job. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0

[GENERAL] Obtaining random rows from a result set

volunteer for at least looking into this, but I'm working on three projects simultaneously already. Alas... Regards, Alban Hertroys. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede

Re: [GENERAL] Obtaining random rows from a result set

On Aug 31, 2007, at 15:54, Martijn van Oosterhout wrote: On Fri, Aug 31, 2007 at 02:42:18PM +0200, Alban Hertroys wrote: Examples: * random(maxrows) would return random rows from the resultset. * median() would return the rows in the middle of the result set (this would require ordering

Re: [GENERAL] JOIN issues (Left vs Right for sorting), and Nested Loop problem

, not touching their position among other records because they can't know how to compare them. If you want certain behaviour from NULL values you'll need to specify what you want or expect surprises ;) -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I

Re: [GENERAL] Obtaining random rows from a result set

On Sep 1, 2007, at 12:44, Alban Hertroys wrote: It would be possible to write an aggregate that returns a single random value from a set. The algorithm is something like: n = 1 v = null for each row if random() 1/n: v = value of row n = n + 1 return v Doesn't this always return

Re: [GENERAL] JOIN issues (Left vs Right for sorting), and Nested Loop problem

On Sep 1, 2007, at 14:48, Phoenix Kiula wrote: On 01/09/07, Alban Hertroys [EMAIL PROTECTED] wrote: On Sep 1, 2007, at 11:46, Phoenix Kiula wrote: . ..snip However, there's a nested loop in there as the EXPLAIN ANALYZE shows below. What is causing this nested loop? It looks like

Re: [GENERAL] Obtaining random rows from a result set

On Sep 1, 2007, at 14:44, Martijn van Oosterhout wrote: On Sat, Sep 01, 2007 at 02:24:25PM +0200, Alban Hertroys wrote: Oh, now I see... The first time guarantees that v has a value (as random() 1/1), and after that there is a decreasing chance that a new row gets re-assigned to v. That means

Re: [GENERAL] Statistics collection question

check that you're not connecting through the internet and getting a DNS timeout? Regards, -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World

Re: [GENERAL] Statistics collection question

on this list a few times) and may thus be exaggerating the problem. Good luck! -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end

  1   2   3   4   5   6   7   8   9   10   >