[GENERAL] unique index for periods
Hello,
I try to create an unique index for a (time)period, and my goal is to
prevent two overlapping periods in a row.
For this I created a type with following command:
CREATE TYPE period AS
(first timestamp with time zone,
next timestamp with time zone);
To use the btree index I added a compare function:
CREATE OR REPLACE FUNCTION period_compare(period, period)
RETURNS integer AS $BODY$
begin
raise info 'compare % = % = %', $1, $2,
CASE
WHEN $1.next = $2.first THEN -1
WHEN $2.next = $1.first THEN 1
ELSE 0
END;
return
CASE
WHEN $1.next = $2.first THEN -1
WHEN $2.next = $1.first THEN 1
ELSE 0
END;
end
$BODY$ LANGUAGE 'plpgsql' IMMUTABLE STRICT COST 1;
After this I created a operator class:
CREATE OPERATOR CLASS period_overlap
DEFAULT FOR TYPE period USING btree AS
FUNCTION 1 period_compare(period, period);
To test everything I use this table:
CREATE TABLE p (
p period NOT NULL,
CONSTRAINT p_pkey PRIMARY KEY (p)
);
Now I fill the table with data:
DELETE FROM p;
-- clean up
VACUUM p;
INSERT INTO p VALUES (('-infinity', 'today')::period);
-- this one fails
-- INSERT INTO p VALUES (('-infinity', 'infinity')::period);
DELETE FROM p;
-- the index tree is still there, why?
INSERT INTO p VALUES (('-infinity', 'infinity')::period);
-- intersects with the deleted value, so compare returns 0
-- and the data goes to the left side of the tree
-- this one should fail
INSERT INTO p VALUES (('today', 'infinity')::period);
-- but this one is bigger than the deleted value, goes to
-- the right side of the tree and is not compared to the
-- entry inserted above.
What do I do wrong? Is there another solution to solve my problem?
Thanks,
Gerhard
signature.asc
Description: Digital signature
Re: [GENERAL] multiple paramters in aggregate function
That would be true if all units were always convertible to mm, but we have volume also, we also have feet etc.. So that the easiest and How did you plan on solving that in your multiple-argument aggregate? Fake their value by adding 0? That's no different for my suggested solution. Wow. I didn't think this would become such a monster. Awesome work on your unit conversion system, that will be tons of help. I apologize for being slow on the response, I am way over-busy right now. However, getting back to where I was, there are only 2 differences between what I want to do and what you are suggesting: 1) You need 2 functions, a sum and a conversion, while I wrote the conversion function in the sum. 2) You need to know before hand which measurement you want in the end and I don't. I either need 2 sum functions, one which will just return the final value and the other will return the unit used, or I need my aggregate to return a composite type, which is less desirable in my case as I want my results to be include one value per field (qty, unitid) In other words, I have a conversion table of all different units. If there is no conversion between 2 units (such as volume and area) then the sum returns null. My unit sum function works now. It takes 2 arguments, a numeric and a unitid. The state variable is of composite type, with a numeric and an int. In the aggregate function, it first converts one of the values to the other (according to the business rules, such that I always go to the lower measurement, if comparing mm and m, it will convert to mm, in and cm it will convert to cm). My query contains select .., sum_unitvalues(qty,unitid),sum_units(unitid),... then the units returned do not have to be known in advance, which is important in this specific project. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] multiple paramters in aggregate function
My query contains select .., sum_unitvalues(qty,unitid),sum_units(unitid),... then the units returned do not have to be known in advance, which is important in this specific project. To give an example of my required result set: unitid 1 = mm 2 = inch 3 = ft 4 = gram create table test(id serial primary key, qty numeric(12,4), unitid int); insert into test(qty,unitid) values(100,2),(200,2),(5,3),(20,1),(800,4) select sum_unitvalues(qty,unitid) as qty, sum_units(unitid) as unitid from test where unitid4; qty | unitid 9124 | 1 select sum_unitvalues(qty,unitid) as qty, sum_units(unitid) as unitid from test where unitid not in (1,4); qty | unitid 360| 2 select sum_unitvalues(qty,unitid) as qty, sum_units(unitid) as unitid from test; qty | unitid NULL | NULL -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Field's position in Table
Hi , I have a problem in Postgres. When adding a new field in the existing table, i want to add the filed in a particular position. Is there any way to change the fields position in the table. Any Solution to this problem would be much appreciated. Thanks Softlinne
[GENERAL] ERROR: could not access file $libdir/xxid: No such file or directory
Hi, I keep getting... ERROR: could not access file $libdir/xxid: No such file or directory whenever I create a new cluster. I've checked the pg directory and the xxid files are in shared folder. I've installed PG 8.4 and Slony-I 2.0.2-1 properly using one-click-installer and stack builder. I've checked Options.. in PgAdmin and the Slony-I path is in /share directory under Pg installation. I have previous installation of Pg 8.3 but I uninstalled it properly using the bundled uninstaller. After doing all this. I'm still getting the error, any help will be highly appreciated! Thanks!
Re: [GENERAL] Field's position in Table
Kalai R wrote: Hi , I have a problem in Postgres. When adding a new field in the existing table, i want to add the filed in a particular position. Is there any way to change the fields position in the table. Any Solution to this problem would be much appreciated. don't use * in SELECT statements, and its not a problem. SQL relations should have neither field order nor row order unless such is explicitly given -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
R: [GENERAL] Field's position in Table
When adding a new field in the existing table, i want to add the filed in a particular position. I'm afraid the only way would be re-writing the whole table (pseudo sql): BEGIN; create table newtable as select field1, 'newfield default value', field2 from old_table; create_all_indexes on newtable; drop old_table; commit; things get complicated if you have foreign keys pointing to old_table... -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: R: [GENERAL] Field's position in Table
On Thu, 20 Aug 2009 09:21:25 + (GMT), Scara Maccai m_li...@yahoo.it wrote: When adding a new field in the existing table, i want to add the filed in a particular position. I'm afraid the only way would be re-writing the whole table (pseudo sql): BEGIN; create table newtable as select field1, 'newfield default value', field2 from old_table; create_all_indexes on newtable; drop old_table; commit; things get complicated if you have foreign keys pointing to old_table... Which is why you might be better off putting the new field at the end of the table and using an administrative view to make your viewing easier. I can't really think of any real reason to put the field at a particular position, applications don't reallty care about the order of fields. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Generate LaTeX doc from PostgreSQL (autodoc)?
Hello, what would be the easiest way to generate documentation for a PostgreSQL database in LaTeX/LyX format? Autodoc doesn't seem to support LaTeX output directly... TIA, Sincerely, Wolfgang -- NO Courtesy Copies PLEASE! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Temp table or normal table for performance?
On Wed, Aug 19, 2009 at 08:10:14PM -0400, Stephen Cook wrote: I've decided on some type of table storage because basically I'm combining information from several different tables (some of which need to recursively get other rows) and massaging it and sorting it in ways far too convoluted to use a single query with UNION and ORDER BY, and then returning the results. Sounds like you want a temp table to keep things in; you can add an ON COMMIT DROP which should help keep things tidy. If you're on 8.4 the WITH clause may make this use case easier. -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Generating random unique alphanumeric IDs
On Mon, 17 Aug 2009 12:37:33 +0200 Daniel Verite dan...@manitou-mail.org wrote: http://archives.postgresql.org/pgsql-general/2009-07/msg00194.php As an exercise I wrote the decrypt version create or replace function feistel_encrypt(value int) returns int as $$ declare l1 int; l2 int; r1 int; r2 int; i int:=0; begin l1:= (value 16) 65535; r1:= value 65535; while i3 loop l2:=r1; r2:=l1 # 1366.0 * r1+150889)%714025)/714025.0)*32767)::int; l1:=l2; r1:=r2; i:=i+1; end loop; return ((l1::bigint16) + r1); end; $$ language plpgsql strict immutable; create or replace function feistel_decrypt(value int) returns int as $$ declare l1 int; l2 int; r1 int; r2 int; i int:=0; begin l2:= (value 16) 65535; r2:= value 65535; while i3 loop r1=l2; l1:=r2#1366.0*l2+150889)%714025)/714025.0)*32767)::int; l2:=l1; r2:=r1; i:=i+1; end loop; return ((l2::bigint16) + r2); end; $$ language plpgsql strict immutable; so that 10 = feistel_decrypt(feistel_encrypt(10)) Since I'm then converting to_hex to shorten the string I was thinking to add some more bits of randomness since eg. to_hex(10) = 'a' In the line of select lpad( to_hex(feistel_encrypt(10)),7 , to_hex((rand()*2^31)::int) ); I was wondering if there is any better way to get alphanumeric random string quickly. Since uniqueness is assured by passing a sequence to fesitel_encrypt, I just need turning into to alphanumeric quickly. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: R: [GENERAL] Field's position in Table
On Thu, Aug 20, 2009 at 11:24:49AM +0200, vinny wrote: I can't really think of any real reason to put the field at a particular position, applications don't reallty care about the order of fields. Because it's very convenient for ad-hoc queries! PG currently assumes that the column order is the same as when it was created but there are (unimplemented) suggestions about how to fix this. See for example: http://archives.postgresql.org/pgsql-hackers/2006-12/msg00983.php -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] unique index for periods
In article 20090820065819.ga2...@gheift.kawo1.rwth-aachen.de, Gerhard Heift ml-postgresql-20081012-3...@gheift.de writes: Hello, I try to create an unique index for a (time)period, and my goal is to prevent two overlapping periods in a row. ... Is there another solution to solve my problem? Have a look at http://pgfoundry.org/projects/temporal -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] ownership of sequences, pseudo random unique id
I've
create table pr(
code varchar(16) primary key,
...
);
create sequence pr_code_seq owned by pr.code; -- uh!
pr.code will *mostly* be obtained as
to_hex(feistel_encrypt(nextval('pr_code')))
and sometimes 'manually' inserting unique codes.
actually stuff like:
alter table pr drop column code;
or just
drop table pr
seems to work as expected (they drop the sequence too).
Should I be concerned of anything since it looks like a hack?
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: could not access file $libdir/xxid: No such file or directory
On Thu, Aug 20, 2009 at 3:08 AM, Jorge Daine Quiambaocyb3rjo...@yahoo.com wrote: Hi, I keep getting... ERROR: could not access file $libdir/xxid: No such file or directory whenever I create a new cluster. I've checked the pg directory and the xxid files are in shared folder. I've installed PG 8.4 and Slony-I 2.0.2-1 properly using one-click-installer and stack builder. I've checked Options.. in PgAdmin and the Slony-I path is in /share directory under Pg installation. I have previous installation of Pg 8.3 but I uninstalled it properly using the bundled uninstaller. After doing all this. I'm still getting the error, any help will be highly appreciated! If you're o windows,uninstall (don't just disable) it and see if the problem goes away -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: could not access file $libdir/xxid: No such file or directory
Yes, that's how I did it uninstall everything first then install. I use Windows Vista Hi, I keep getting... ERROR: could not access file $libdir/xxid: No such file or directory whenever I create a new cluster. I've checked the pg directory and the xxid files are in shared folder. I've installed PG 8.4 and Slony-I 2.0.2-1 properly using one-click-installer and stack builder. I've checked Options.. in PgAdmin and the Slony-I path is in /share directory under Pg installation. I have previous installation of Pg 8.3 but I uninstalled it properly using the bundled uninstaller. After doing all this. I'm still getting the error, any help will be highly appreciated! If you're o windows,uninstall (don't just disable) it and see if the problem goes away
[GENERAL] Re: Unit conversion database (was: multiple paramters in aggregate function)
On 2009-08-19, Alban Hertroys dal...@solfertje.student.utwente.nl wrote: On 19 Aug 2009, at 19:20, Karsten Hilbert wrote: Alban, I think having an installable schema for units of measure with definitions and functions would be a great addition to PostgreSQL. Karsten, Thanks for the praise and the links. I for one know we would use it in GNUmed (wiki.gnumed.de). A few points: Would these guys be of use as a source for reference data ? http://unitsofmeasure.org/ That looks certainly interesting, especially the fact that they provide a source of units and conversions in an XML format. Although their conversion formulas don't look all that easy to parse. I've run into a few of the problems they mention already; for example the slight differences between imperial and US units of measurement with the same names and abbreviations... You may want to think about whether there's use in combining units with tagged types: http://svana.org/kleptog/pgsql/taggedtypes.html Yes, I've been thinking the same thing. I had it bookmarked already for the very purpose of checking it out and see how I could use tagged types with units. There's also a Debian package which comes with a text format units database: http://packages.debian.org/source/sid/units The original source for that: This package was put together by me, James Troup ja...@nocrew.org, from the GNU sources, which I obtained from sunsite.doc.ic.ac.uk:/pub/gnu/units-1.54.tar.gz. I don't consider that tool very reliable[1]. A number of their concepts are probably usable though. I have it's source in my source tree (FreeBSD), so ample opportunity to peek. the licence is GPL2 though so that may restrict it's use in some contexts. [1] It doesn't correctly convert °C to °F or vv, that was one of the first things I tried. what is vv for °C to °F RTFM: units 'tempC(37)' 'tempF' it handles units (and arbitrary derived units) that are linked by a ratio It does that very well. units mi water/kWh mm hg/btu Offset units like centigrade and farenheit pose a problem in many contexts. if the temperature just dropped 9 °F what's that in °C ? yet the answer can be coerced from units. units 'tempF(0)-tempF(9)+tempC(0)' 'tempC' -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] multiple paramters in aggregate function
On Thu, Aug 20, 2009 at 10:36:37AM +0300, Sim Zacks wrote: In other words, I have a conversion table of all different units. If there is no conversion between 2 units (such as volume and area) then the sum returns null. Shouldn't that return NULL IOW unknown ? Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Generating random unique alphanumeric IDs
Since I'm then converting to_hex to shorten the string I was thinking to add some more bits of randomness since eg. to_hex(10) = 'a' In the line of select lpad( to_hex(feistel_encrypt(10)),7 , to_hex((rand()*2^31)::int) ); I was wondering if there is any better way to get alphanumeric random string quickly. Since uniqueness is assured by passing a sequence to fesitel_encrypt, I just need turning into to alphanumeric quickly. This appears a lot more tricky than I had originally anticipated! I may be misunderstanding your example, but by alphanumeric, I mean beyond hex (i.e. a-z and possibly uppcase too). I've looked into LFSR, but I'm afraid it goes over my head. But what Jason Betts said seems to summarise what I'm after: for the OP's problem this means building a LFSR with n=5c (where c is the number of charactes in the serial code, and n is the number of bits in the LFSR state) and then taking a single LFSR result and peeling off 5 bits at a time and using each 5 to make each charcter in the result. If this results in an unpredictable and non-duplicating loop of generated sets of characters, that would be ideal. Would a parallel for this be a 5-character code possibly transcoded from a 6-character GUID/UUID? (a-h + j+n + p-z + A-H + J-N + P+Z + 2-9 = 56 possible characters, 56^5 = 550,731,776, 550,731,776 / 16 (hex character set) ^ 6 (characters) = just over 32.), so wouldn't actually use up all possible combinations. :/ Thom
Re: [GENERAL] multiple paramters in aggregate function
On Thu, Aug 20, 2009 at 03:30:00PM +0300, Sim Zacks wrote: In other words, I have a conversion table of all different units. If there is no conversion between 2 units (such as volume and area) then the sum returns null. Shouldn't that return NULL IOW unknown ? I am not familiar with returning unknown. I know that according to the spec an aggregate Oh, OK, I forgot about the aggregate part. should only return null if all of its values of the aggregate are null. If there is a way to return unknown in a non-NULL way, then that would be preferred. I don't know of any. However, it seems patently wrong to let an integer sum()-something return 0 when some of its inputs are NULL. After all, the sum could truly have been 0. This should either throw an error, return NULL, or let me choose to ignore NULL input and return the sum of non-NULL input. But I have a feeling I am tip-toeing into a Holy War situation. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgre RAISE NOTICE and PHP
On 2009-08-19, Clemens Schwaighofer clemens_schwaigho...@e-gra.co.jp wrote:
On Wed, Aug 19, 2009 at 02:11, Randal L. Schwartzmer...@stonehenge.com
wrote:
Andre == Andre Lopes lopes80an...@gmail.com writes:
Andre I'm developing a function with some checks, for example... to check
if the
Andre e-mail is valid or not.
How are you hoping to do this? The regex to validate an email
address syntactically is pretty large:
http://ex-parrot.com/~pdw/Mail-RFC822-Address.html
And no, I'm not kidding. If your regex is smaller than that, you aren't
validating email... you're validating something kinda like email.
Just in my opinion, this regex is completely too large. For basic
validating something like:
^[A-Za-z0-9!#$%'*+-\/=?^_`{|}~][A-Za-z0-9!#$%'*+-\/=?^_`{|}~\.]{0,6...@[a-za-z0-9-]+(\.[a-zA-Z0-9-]{1,})*\.([a-zA-Z]{2,4}){1}$
works very well
not good: eg:
fails this valid address* : ad...@xxx.museum
accepts this invalid one : y...@gmail..com
musedoma replaced with several x to protect the innocent from spam
in some contexts email adrresses with no domain part are valid
addresses with [bracketed] mx servers instead of a domain and/or bang
paths are also allowed (but not in common use and often not desirable)
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Re: Unit conversion database (was: multiple paramters in aggregate function)
On Thu, Aug 20, 2009 at 12:06:19PM +, Jasen Betts wrote: what is vv Vice versa, I'd assume. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Best database model for canvassing (and analysing) opinion
Quoth Sam Mason s...@samason.me.uk: [...] The only table that's really needed to solve your original problem would be the last one, but the others provide all the checks that the data is actually going in correctly and may or may not be useful depending on your problem. The main thing to notice is lots of tables with few columns, the reason being is that the database normally takes care of the rows and you, the DBA/programmer, take care of the columns. Thus the more work you can give to the database the better. [...] Hope that gives you some ideas! More than enough ideas. Thank you _very_ much. Presenting this kind of 'distributed' data in a useful way is more difficult (at least for me) but I can see now that this is what _relational_ databses are all about, and that once you've grasped how to do this, the advantages are legion. Many thanks once again. Sebastian -- Emacs' AlsaPlayer - Music Without Jolts Lightweight, full-featured and mindful of your idyllic happiness. http://home.gna.org/eap -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] multiple paramters in aggregate function
In other words, I have a conversion table of all different units. If there is no conversion between 2 units (such as volume and area) then the sum returns null. Shouldn't that return NULL IOW unknown ? Karsten I am not familiar with returning unknown. I know that according to the spec an aggregate should only return null if all of its values of the aggregate are null. If there is a way to return unknown in a non-NULL way, then that would be preferred.
Re: [GENERAL] Postgre RAISE NOTICE and PHP
On 2009-08-20, Randal L. Schwartz mer...@stonehenge.com wrote: Clemens == Clemens Schwaighofer clemens_schwaigho...@e-gra.co.jp writes: Clemens I am not going to defend any regex here, but in my opinion it helps on Clemens what I want to see in email addresses. Clemens Yes it fails on mobile, but I have not yet seen one. And that's the problem. You get near-sighted if you put up a strong validation for only things that *you* have seen. Because, guess what, nobody outside your narrow view can sign up or be a customer. Bad for business. Clemens Probably the best Clemens thing is to test nothing at all. Just accept it ... Exactly! If you don't want to use the 950-character regex, DON'T DO ANYTHING AT ALL. Far simpler. Or do an MX lookup on the domain part (or a partial attempt to route mail*) before sending it to the database. *contact the domains MX and in SMTP go as far as RCPT TO: ... and then send QUIT after it is accepted or refused. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unit conversion database (was: multiple paramters in aggregate function)
On Wed, Aug 19, 2009 at 08:31:17PM +0200, Alban Hertroys wrote: He he, all right then! There certainly are some things left to improve. One thing I noticed from the links you sent is that I ignored a few units used in medicine assuming they were deprecated ages ago - apparently not... Ah, tell you what. There's *weird* unit usage in medicine ! There's units which omit parts of themselves (they are assumed to be known to be implicit). There a units used for measurments which are only equivalent to each other by some weird empirical formula. Then again, encouraging their usage may not be the best thing to do, but who am I to decide what units people use eh? No chance weaning doctors from mmHg, for example :-)) Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgre RAISE NOTICE and PHP
On Thu, Aug 20, 2009 at 01:03:47PM +, Jasen Betts wrote: On 2009-08-20, Randal L. Schwartz mer...@stonehenge.com wrote: Exactly! If you don't want to use the 950-character regex, DON'T DO ANYTHING AT ALL. Far simpler. Or do an MX lookup on the domain part (or a partial attempt to route mail*) before sending it to the database. *contact the domains MX and in SMTP go as far as RCPT TO: ... and then send QUIT after it is accepted or refused. Why not just go the whole way and send an email asking for confirmation? When you get a response you know the email address is actually useful for contacting the user, rather than it being a typo and going somewhere else. -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PL/PGSQL: why IF test the whole condition before failing or not?
On 2009-08-18, Suporte PK fkno...@gmail.com wrote: Hi list, I'm having trouble with - believe me! - the IF operator on a PL/PGSQL function used by a trigger. I'm using one unique function to process the three triggers events (delete, update and insert), but when I reference OLD or NEW on a IF CONDITION, I get an error even when testing BEFORE if it's a UPDATE event or not. example: IF TG_OP = 'UPDATE' AND OLD.field != NEW.field THEN ... END IF; The question is: if the trigger was not fired by an UPDATE event, shouldn't it make the first test and then ignore the rest of the condition? I know that some languages work like this (testing the whole condition) while others don't, but I searched for an alternative without success. Any advice would be much appreciated! as you see a few more error messages (or unlike me get as far as http://www.postgresql.org/docs/8.4/static/plpgsql-expressions.html in the manual and understand it) you'll come to understand that every /expression/ in plpgsql is translated into a select, this can be exploited to simplifiy code. avariable = somecolumn FROM atable WHERE someother=foo; but has its down sides too: SELECT TG_OP = 'UPDATE' AND OLD.field != NEW.field; doesn't pass go with NEW undefined. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Temp table or normal table for performance?
On 2009-08-19, Stephen Cook scli...@gmail.com wrote: Let's say I have a function that needs to collect some data from various tables and process and sort them to be returned to the user. plpgsql functions don't play well with temp tables IME. there are work-arounds and they are ugly. if you caus use a different language it could work. In general, would it be better to create a temporary table in that function, do the work and sorting there, and return it... or keep a permanent table for pretty much the same thing, but add a user session field and return the relevant rows from that and then delete them? Sorry this is vague, I know it most likely depends on the workload and such, but I'm just putting this together now. I could go either way, and also switch it up in the future if necessary. Is there a rule of thumb on this one? I'm a bit biased against temporary tables, but then again if the normal table gets a lot of action it might not be the optimal choice. temp tables are usually worth the effort. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: could not access file $libdir/xxid: No such file or directory
Whoops, I meant if you're on windows, uninstall any antivirus then see if the problem goes away Too early in the morning apparently. On Thu, Aug 20, 2009 at 6:03 AM, Jorge Daine Quiambaocyb3rjo...@yahoo.com wrote: Yes, that's how I did it uninstall everything first then install. I use Windows Vista Hi, I keep getting... ERROR: could not access file $libdir/xxid: No such file or directory whenever I create a new cluster. I've checked the pg directory and the xxid files are in shared folder. I've installed PG 8.4 and Slony-I 2.0.2-1 properly using one-click-installer and stack builder. I've checked Options.. in PgAdmin and the Slony-I path is in /share directory under Pg installation. I have previous installation of Pg 8.3 but I uninstalled it properly using the bundled uninstaller. After doing all this. I'm still getting the error, any help will be highly appreciated! If you're o windows,uninstall (don't just disable) it and see if the problem goes away -- When fascism comes to America, it will be intolerance sold as diversity. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] unique index for periods
Gerhard Heift ml-postgresql-20081012-3...@gheift.de writes: I try to create an unique index for a (time)period, and my goal is to prevent two overlapping periods in a row. To use the btree index I added a compare function: return CASE WHEN $1.next = $2.first THEN -1 WHEN $2.next = $1.first THEN 1 ELSE 0 END; This does not work as a btree compare function, because it fails to satisfy the basic requirements of a total order. In particular it doesn't satisfy the transitive law that A=B and B=C must imply A=C. I don't believe it is possible to use a btree index for this purpose, because there just isn't a way to express overlaps as a total order. It'd be really nice to have uniqueness support in gist indexes someday ... regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: could not access file $libdir/xxid: No such file or directory
Jorge Daine Quiambao cyb3rjo...@yahoo.com writes: I keep getting... ERROR: could not access file $libdir/xxid: No such file or directory whenever I create a new cluster. I've checked the pg directory and the xxid files are in shared folder. The no such file complaint might refer to some library needed by the xxid DLL, rather than that DLL itself. On Linux I'd suggest using ldd to check xxid's dependencies, but I dunno what incantation to use on Windows. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Clustering with enough work_mem: copy heap in mem?
Hi, I have a table with 15M rows. Table is around 5GB on disk. Clustering the table takes 5 minutes. A seq scan takes 20 seconds. I guess clustering is done using a seq scan on the index and then fetching the proper rows in the heap. If that's the case, fetching random rows on disk is the cause of the enormous time it takes to cluster the table. Since I can set work_mem 5GB. couldn't postgres do something like: - read the whole table in memory - access the table in memory instead of the disk when reading the indexed data ? I mean: there's access exclusive lock on the table while clustering, so I don't see any problem in doing it... this way you could - avoid sorting (which is what is used in the method create newtable as select * from oldtable order by mycol, and can be slow with 15M rows, plus in my case uses 8GB of ram...) - avoid random-reading on disk Am I missing something or it's just that hasn't been done yet? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Clustering with enough work_mem: copy heap in mem?
On Thu, Aug 20, 2009 at 8:28 AM, Scara Maccaim_li...@yahoo.it wrote: Hi, I have a table with 15M rows. Table is around 5GB on disk. Clustering the table takes 5 minutes. A seq scan takes 20 seconds. I guess clustering is done using a seq scan on the index and then fetching the proper rows in the heap. If that's the case, fetching random rows on disk is the cause of the enormous time it takes to cluster the table. Yep. Since I can set work_mem 5GB. couldn't postgres do something like: - read the whole table in memory - access the table in memory instead of the disk when reading the indexed data I've found it easier to select everything into another table, truncate the original table, then insert the rows as: insert into orig_table select * from mytemptable order by field1,field2; If needs be you can lock the original table to prevent modifications while doing this. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Clustering with enough work_mem: copy heap in mem?
I've found it easier to select everything into another table, truncate the original table, then insert the rows as: that takes 50 seconds of pure sorting and 8GB of ram to sort; my method doesn't require more memory than the size of the heap table, and no sorting, since the index is already sorted. Basically the cluster operation would be: A) time it takes to do a full scan of the heap + B) time it takes to do a full scan of the index + C) time it takes to rewrite ordered heap and index of course C) is no different than any other method I guess. plus: with the create as method indexes, foreign keys etc have to be recreated on the tab (I'm not talking about timing: it's just that you have to remember to re-create whatever was in the old table...). Plus: if a table has a foreign key to the table you're clustering, I guess the create as method doesn't work (I guess you can't drop a table that is the foreign key of another one). -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Clustering with enough work_mem: copy heap in mem?
Scara Maccai wrote: I mean: there's access exclusive lock on the table while clustering, so I don't see any problem in doing it... this way you could - avoid sorting (which is what is used in the method create newtable as select * from oldtable order by mycol, and can be slow with 15M rows, plus in my case uses 8GB of ram...) - avoid random-reading on disk Am I missing something or it's just that hasn't been done yet? The actual CLUSTER implementation is do an indexscan, insert the rows in the new heap in that order. It's pretty stupid. There was an attempt to fix it (for example so that it could try to do a seqscan+sort instead of indexscan), but it stalled. http://archives.postgresql.org/message-id/87vdxg6a3d@oxford.xeocode.com http://archives.postgresql.org/message-id/20080901072147.gb16...@svana.org -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Monitoring the sequence cycles
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 I'm looking for a way to track the wrap arounds of cycle sequences. I also couldn't find the view/catalog where sequence definitions are kept; this could be a good place to store at least the timestamp of the last wrap around. You can query: select * from sequence_name; I'm looking for a way to log all wrap arounds, thought I honestly doubt such a feature would be added to the system catalog, but it certainly might be a nice thing to output to the logs. You might want to add your request to the wiki so it doesn't get lost: http://wiki.postgresql.org/wiki/Todo One thing you can do at the moment is track how close the sequence is getting near its maxvalue, and thus have an idea of when it is about to cycle. The check_postgres program can do this for you: http://bucardo.org/check_postgres/check_postgres.pl.html#sequence - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation PGP Key: 0x14964AC8 200908201120 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkqNadIACgkQvJuQZxSWSsg9cQCcDm9HzBzBcQL6Yj8rKKoFDtc8 100AnikYzW4quROeThMCsBjfd7aungm2 =5uI0 -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Connection name on ECPGdeallocate ?
Hi guys, What is the reason for the ecpglib function ECPGdeallocate() (with call generated by the ecpg program) to have a connection_name parameter, when the ecpg program generates an error message, but generates 'correct' code ? This is if you put a connection name on the DEALLOCATE PREPARE stmt statement. Is that a planned future feature ? At least for Postgresql-8.3.5, Postgresql-8.4.0 Leif -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] unique index for periods
On Thu, Aug 20, 2009 at 3:14 PM, Tom Lanet...@sss.pgh.pa.us wrote: I don't believe it is possible to use a btree index for this purpose, because there just isn't a way to express overlaps as a total order. That's true for the general case of indexing ranges but I don't think that's true for the case where overlaps are illegal. In such a case you could just, sorting by the start point, compare the previous entry's end point with your start point and the next entry with your end point. However that's not the way unique indexes work in Postgres so supporting that would require a lot of new abstractions and code, not just a new opclass. -- greg http://mit.edu/~gsstark/resume.pdf -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Generating random unique alphanumeric IDs
On Thu, 20 Aug 2009 13:34:51 +0100 Thom Brown thombr...@gmail.com wrote: Correcting myself. a) it is a bad idea to pad an hex with an hex... so I should still find a quick way to change representation to [g-z] for the padding characters... or just pad with a constant string. select lpad( to_hex(feistel_encrypt(10)),8 , 'mjkitlh') ); b) this if from int (signed) to int (signed). begin; create or replace function feistel_encrypt(value int) returns int as $$ declare l1 int; l2 int; r1 int; r2 int; i int:=0; begin l1:= (value 16) 65535; r1:= value 65535; while i3 loop l2:=r1; r2:=l1#1366.0 *r1+150889)%714025)/714025.0)*32767)::int; l1:=l2; r1:=r2; i:=i+1; end loop; return ((l1 16) | r1); end; $$ language plpgsql strict immutable; create or replace function feistel_decrypt(value int) returns int as $$ declare l1 int; l2 int; r1 int; r2 int; i int:=0; begin l2:= (value 16) 65535; r2:= value 65535; while i3 loop r1=l2; l1:=r2#1366.0*l2+150889)%714025)/714025.0)*32767)::int; l2:=l1; r2:=r1; i:=i+1; end loop; return ((l2 16) | r2); end; $$ language plpgsql strict immutable; commit; select * from feistel_decrypt(feistel_encrypt((2^31-1)::int)) union select * from feistel_decrypt(feistel_encrypt((-2^31)::int)) union select * from feistel_decrypt(feistel_encrypt((0)::int)) union select * from feistel_decrypt(feistel_encrypt((-1)::int)) ; This appears a lot more tricky than I had originally anticipated! I may be misunderstanding your example, but by alphanumeric, I mean beyond hex (i.e. a-z and possibly uppcase too). me too... but to_hex was there and a quick trick to shorten the string and get rid of a sign. I've looked into LFSR, but I'm afraid it goes over my head. But There is too much dust on my copy of Concrete Mathematics still by popular culture (read wikipedia) it is said that LFSR are not cryptographically safe, while making 4 loops and choosing a suitable F, Feistel cypher is. Then it is just a problem of shrinking the string or representing it in another base... and that may result in some waste. 5 bits are 32 char... you actually have more chars available even just considering a subset of ASCII. Picking 5 bits from LFSR algo isn't that different than converting to hex feistel cipher as I see it. The main advantage of hex over ASCII is that ints map very well to hex (no waste) and that to_hex has good chance to perform better than any plpgsql function. Since I'm generating gift codes It wouldn't look nice if I present the user with A as a gift code... And that's going to happen as soon as I'll have generated 232798491 gift codes. (/me wondering which is the smaller number with a corresponding one digit hex(fiestel()) transform.)[1]. So just to make gift codes look nicer I thought about padding them with some furter random noise... but the way initially described is not going to work. Variants could be to concat with something [^a-f0-9] (eg '-') and then padding with hex random noise A - -A - (random noise)-A I don't know if it is worth since it is another round of lpad. Even if I'm currently not overly concerned by performances I'm working with plpgsql and while I think that writing something to change base representation to an int can be done... it will be slow and ugly. If I was working with pgperl (?) I'd just google for some perl receipt. Given the premises I'll just embellish the hex with some padding. But if you really need to use letters and be compact and such... I think you're just looking for changing the base of your wathever-pseudo-random algorithm. That's a common problem you may just have to adapt to plpgsql. [1] select s.i, feistel_decrypt(s.i) from generate_series(0,16) as s(i) order by feistel_decrypt(s.i) did it in a hurry... didn't check -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] German ispell dictionary: error parsing affix file
Hi,
I'm trying to get a German ispell dictionary to work with
postgresql 8.3.7 which supports compound words. I tried
the following three dictionaries:
-
http://ftp.services.openoffice.org/pub/OpenOffice.org/contrib/dictionaries/de_DE_frami.zip
(for OpenOffice 2),
- http://extensions.services.openoffice.org/project/dict-de_DE_frami
(for OpenOffice 3) and
-
http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/dicts/ispell/ispell-german-compound.tar.gz.
Each file was converted to UTF-8 via iconv. I created the
dictionary with the following command:
CREATE TEXT SEARCH DICTIONARY german_ispell (
Template = ispell,
DictFile = de_de_frami,
AffFile = de_de_frami,
StopWords = german
);
Then I test it via:
SELECT ts_lexize('german_ispell', 'haustür');
which should result in 'haus' and 'tür'. The first two
dictionaries return nothing at all. Compound words don't seem
to work with those two.
The third one works if I remove all lines containing any umlauts
from de_de_frami.affix and returns 'haus' and 'tür'. If I do not
remove all lines containing umlauts from the affix file I get a
syntax error during parsing:
ERROR: syntax error
CONTEXT: line 224 of configuration file
/usr/local/share/postgresql/tsearch_data/de_de_frami.affix:ABE
-ABE,äBIN
Problem seems to be that postgresql runs on OpenBSD, which
does not support any locale but C. The affix file contains umlauts
and is encoded in UTF-8 as required by postgresql. But the
parsing fails probably due to the method parse_affentry in spell.c
and the method t_isalpha used within that function.
In t_isalpha there is:
if (clen == 1 || lc_ctype_is_c())
return isalpha(TOUCHAR(ptr))
which fails for the umlauts in the affix file. is there any reason to
check for a lc_ctype of C here. The affix file is in UTF-8 and each line
is converted to the encoding used by the database. Why is there
a check for the C locale?
Or am I completly wrong and this is not the reason, the parsing of
the affix file fails.
Thanks for your help.
Christof
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Temp table or normal table for performance?
On Wed, Aug 19, 2009 at 03:03:28AM -0400, Stephen Cook wrote: Let's say I have a function that needs to collect some data from various tables and process and sort them to be returned to the user. In general, would it be better to create a temporary table in that function, do the work and sorting there, and return it... or keep a permanent table for pretty much the same thing, but add a user session field and return the relevant rows from that and then delete them? The big difference between temp tables and normal tables is that temp tables are not WAL logged, are not stored in shared_buffers and generally don't require any of the usual transaction guarentees or worrying about concurrent accesses between backends. As such they're useful for dumping data only needed for single transactions/backends. pl/pgsql had some serious warts w.r.t. temp tables prior to 8.4 so be sure to test whatever you do thoughly. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [GENERAL] unique index for periods
Greg Stark gsst...@mit.edu writes: On Thu, Aug 20, 2009 at 3:14 PM, Tom Lanet...@sss.pgh.pa.us wrote: I don't believe it is possible to use a btree index for this purpose, because there just isn't a way to express overlaps as a total order. That's true for the general case of indexing ranges but I don't think that's true for the case where overlaps are illegal. Uh, no, the question is not about whether there are expected to be any overlapping entries in the index. The point is that overlaps simply does not fit the semantic model of a btree-processable equality relationship. In such a case you could just, sorting by the start point, compare the previous entry's end point with your start point and the next entry with your end point. Even if you hacked the code to work like that, it'll fail completely for deferred unique constraints, not to mention deleted entries that haven't yet been purged from the index. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ERROR: could not access file $libdir/xxid: No such file or directory
On 20/08/2009 10:20 PM, Tom Lane wrote: Jorge Daine Quiambaocyb3rjo...@yahoo.com writes: I keep getting... ERROR: could not access file $libdir/xxid: No such file or directory whenever I create a new cluster. I've checked the pg directory and the xxid files are in shared folder. The no such file complaint might refer to some library needed by the xxid DLL, rather than that DLL itself. On Linux I'd suggest using ldd to check xxid's dependencies, but I dunno what incantation to use on Windows. Dependency Walker (depends.exe) from www.dependencywalker.com (free). Yet another tool the OS and the Windows standard dev tools fail to include. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] unique index for periods
On Thu, 2009-08-20 at 13:35 +0200, Harald Fuchs wrote: Have a look at http://pgfoundry.org/projects/temporal The temporal project on pgfoundry only provides the time period type, which is (hopefully) useful, but it does not help with a non-overlapping constraint. Please see my other project here: https://commitfest.postgresql.org/action/patch_view?id=132 I have a working patch already, and I plan to get it cleaned up so that it can make it into 8.5. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Clustering with enough work_mem: copy heap in mem?
There was an attempt to fix it (for example so that it could try to do a seqscan+sort instead of indexscan), but it stalled. Actually I read that, but it's complicated... it involves planning and a lot of other stuff I don't even know about... My solution I guess would be easier (but, of course, can't be used if you don't have enough work_mem): if heap table = work_mem copy heap table in mem use that region as it was the real file else do the regular (slow!) cluster I guess this can be worse than the current way of doing it only when the table contains a lot of dead rows; in all other cases I can't see how cluster could ever become faster than a simple table + index scans. Of course, I'm not saying it's very easy to implement... but given the tons of ram a lot of people use in the servers, and the fact that work_mem can be set on a per-connection basis, I think it would be nice... -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] ownership of sequences, pseudo random unique id
Ivan Sergio Borgonovo wrote:
I've
create table pr(
code varchar(16) primary key,
...
);
create sequence pr_code_seq owned by pr.code; -- uh!
pr.code will *mostly* be obtained as
to_hex(feistel_encrypt(nextval('pr_code')))
and sometimes 'manually' inserting unique codes.
actually stuff like:
alter table pr drop column code;
or just
drop table pr
seems to work as expected (they drop the sequence too).
Should I be concerned of anything since it looks like a hack?
You need to ensure you have a retry loop in your insertion code, because
if the generated code conflicts with a manually inserted code, it will
cause an error. Other than that, seems like it should work ...
--
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Out of memory on pg_dump
Hi all - We are using Postgres 8.2.3 as our Confluence backing store and when trying to backup the database at night we are seeing this in the logs: snip pg_amop_opc_strat_index: 1024 total in 1 blocks; 216 free (0 chunks); 808 used pg_aggregate_fnoid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used MdSmgr: 8192 total in 1 blocks; 6376 free (0 chunks); 1816 used LOCALLOCK hash: 24576 total in 2 blocks; 14112 free (4 chunks); 10464 used Timezones: 49432 total in 2 blocks; 5968 free (0 chunks); 43464 used ErrorContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used 2009-08-19 22:35:42 ERROR: out of memory 2009-08-19 22:35:42 DETAIL: Failed on request of size 536870912. 2009-08-19 22:35:42 STATEMENT: COPY public.attachmentdata (attachmentdataid, attversion, data, attachmentid) TO stdout; Is there an easy way to give pg_dump more memory? I don't see a command line option for it and I'm not a Postgres expert by any means. This is the script we are using to backup our DB (backup.cmd): @ECHO OFF SET BACKUPS_DIR=C:\backups SET PGPASSWORD=*** REM Set the backup file name SET prefix=confluence_dbbackup_ SET basename=%prefix%%date:~-4,4%%date:~-10,2%%date:~-7,2%.%time:~-11,2%.%ti me:~-8,2%.%time:~-5,2% SET confluence_backup_path=%BACKUPS_DIR%\%basename%.dump pg_dump --username=confluence --file=%confluence_backup_path% --blobs --format c confluence Thanks, Chris THIS MESSAGE IS INTENDED FOR THE USE OF THE PERSON TO WHOM IT IS ADDRESSED. IT MAY CONTAIN INFORMATION THAT IS PRIVILEGED, CONFIDENTIAL AND EXEMPT FROM DISCLOSURE UNDER APPLICABLE LAW. If you are not the intended recipient, your use of this message for any purpose is strictly prohibited. If you have received this communication in error, please delete the message and notify the sender so that we may correct our records.
[GENERAL] locking/waiting queries
Hi all ; were seeing a backlog of queries in pg_stat_activity. The system has slowed big time. I see many many queries where waiting = 't' I want to find out for each query which query they are waiting on (who's doing the blocking). What's the best way to find this, I looked at pg_locks for rows where granted = 'f' but its not helping me determine which query is doing the blocking. Thanks in advance... -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Regarding installing starting Postgres on Windows
Hello , I am trying to find a way to install PostgreSQL through my script/code . I don't want to use installer provided on websites. I compiled source and and I installed in one directory , Now I am trying to register it as a service or start database server directly. I read in FAQs that I can start database server by revoking administrative rights. But how I can do that is not mentioned there. Basically I am trying to create similar experience like installer . Please provide some idea on this issue. Thank you very much in advance . Regards, Vikram
[GENERAL] Function for replace
Hi all, In my database recently change backslash_quote to safe_enconding, now a need replace characters blocked to space or nothing. I think create a function to scan every table in database, anybody make a similar work or have this made? thanks __ Fale com seus amigos de graça com o novo Yahoo! Messenger http://br.messenger.yahoo.com/
Re: [GENERAL] Function for replace
On Thu, Aug 20, 2009 at 01:50:42PM -0700, paulo matadr wrote: In my database recently change backslash_quote to safe_enconding, now a need replace characters blocked to space or nothing. Why do you want to do that? Nothing is blocked now, it just changes the default format for literals that appear in your SQL code. Sam -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Regarding installing starting Postgres on Windows
Vikram Patil wrote: Hello , I am trying to find a way to install PostgreSQL through my script/code . I don’t want to use installer provided on websites. I compiled source and and I installed in one directory , Now I am trying to register it as a service or start database server directly. I read in FAQs that I can start database server by revoking administrative rights. But how I can do that is not mentioned there. create a Windows account specifically to run the server. this account needs RunAsService privilege (or whatever thats called), and needs 'full control' over the postgres DATA directory and its contents. you can create the service something like this from a CMD shell, so I'm sure you can migrate this to your installer's setup script methods... NET USER Postgres xyzzy \path\to\pg_ctl register -N PGSQL-8.3 -U Postgres -P xyzzy -D \oath\to\pg\data runas /user:Postgres \path\to\initdb -D \path\to\data -E locale note that this Postgres user account needs to have full access to the data directory (which it will create if it doesn't exist, in which case, it needs write access to the parent dir) I don't quite know the command line magic to grant that process 'Log On As a Service' privileges, however. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Enc: Res: [GENERAL] Function for replace
- Mensagem encaminhada De: paulo matadr saddon...@yahoo.com.br Para: Sam Mason s...@samason.me.uk Enviadas: Quinta-feira, 20 de Agosto de 2009 21:57:29 Assunto: Res: [GENERAL] Function for replace because my application give error when try to reregister some features. I have a many cases that use this feature De: Sam Mason s...@samason.me.uk Para: pgsql-general@postgresql.org Enviadas: Quinta-feira, 20 de Agosto de 2009 18:09:03 Assunto: Re: [GENERAL] Function for replace On Thu, Aug 20, 2009 at 01:50:42PM -0700, paulo matadr wrote: In my database recently change backslash_quote to safe_enconding, now a need replace characters blocked to space or nothing. Why do you want to do that? Nothing is blocked now, it just changes the default format for literals that appear in your SQL code. Sam -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general Veja quais são os assuntos do momento no Yahoo! + Buscados: Top 10 - Celebridades - Música - Esportes Veja quais são os assuntos do momento no Yahoo! +Buscados http://br.maisbuscados.yahoo.com
Re: [GENERAL] Could not open relation XXX: No such file or directory
Hi there, On 19/08/2009, at 8:38 PM, Craig Ringer wrote: On 19/08/2009 6:26 PM, Alan Millington wrote: 2009-08-19 03:06:45 ERROR: could not read block 0 of relation 1663/52752/52896: No such file or directory Clearly something is amiss, but I don't know what. I should be grateful for any suggestions as to what I should check. Got a virus scanner installed? If so, remove it (do not just disable it) and see if you can reproduce the problem. Ditto anti-spyware software. You should also `chkdsk' your file system(s) and use a SMART diagnostic tool to test your hard disk (assuming it's a single ATA disk). By the way, `chkdsk' in Windows or `fsck' in Unix can, in a way, be a _source_ of file loss if the file metadata got damaged badly, e.g., by a system crash, and the file node has to be cleared. So I've always been curious if there is a way to retrieve surviving records from a PostgreSQL database damaged by file loss. Do you know any? (Of course, the only true solution is to have been making backups beforehand, but...) Thanks! Yar -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: R: [GENERAL] Field's position in Table
On 20/08/2009, at 7:24 PM, vinny wrote: I can't really think of any real reason to put the field at a particular position, applications don't reallty care about the order of fields. ... unless an application is brain-damaged by its using a wildcard select, which is a well-known no-no even for home-made scripts, as it has already been pointed out here. My point here being that applications' robustness to apparent field order, like liberty, shouldn't be taken for granted: it needs to be explicitly minded, protected and sometimes fought for. :-) Yar -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: R: [GENERAL] Field's position in Table
On Thu, Aug 20, 2009 at 7:45 PM, Yaroslav Tykhiyy...@barnet.com.au wrote: On 20/08/2009, at 7:24 PM, vinny wrote: I can't really think of any real reason to put the field at a particular position, applications don't reallty care about the order of fields. ... unless an application is brain-damaged by its using a wildcard select, which is a well-known no-no even for home-made scripts, as it has already been pointed out here. My point here being that applications' robustness to apparent field order, like liberty, shouldn't be taken for granted: it needs to be explicitly minded, protected and sometimes fought for. :-) And if you're going to write some simplified application that depends on column order, then you should be willing to accept the responsibility of maintain that order. I don't want or need such code in pgsql really, so would rather not have someone playing with the guts in pgsql to make this happen and breaking anything else. And it IS non-trivial to implement in pgsql -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] join from array or cursor
Suppose I have an integer array (or cursor with one integer column) which represents primary keys of some table. Is there a simple and efficient way to return the rows of the table corresponding to the primary key values and keep them in the same order as the array (or cursor)? Seems like it should be easy, but I'm not seeing it. Thanks, John DeSoi, Ph.D. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgre RAISE NOTICE and PHP
On Thu, Aug 20, 2009 at 21:52, Jasen Bettsja...@xnet.co.nz wrote:
On 2009-08-19, Clemens Schwaighofer clemens_schwaigho...@e-gra.co.jp wrote:
On Wed, Aug 19, 2009 at 02:11, Randal L. Schwartzmer...@stonehenge.com
wrote:
Andre == Andre Lopes lopes80an...@gmail.com writes:
Andre I'm developing a function with some checks, for example... to check
if the
Andre e-mail is valid or not.
How are you hoping to do this? The regex to validate an email
address syntactically is pretty large:
http://ex-parrot.com/~pdw/Mail-RFC822-Address.html
And no, I'm not kidding. If your regex is smaller than that, you aren't
validating email... you're validating something kinda like email.
Just in my opinion, this regex is completely too large. For basic
validating something like:
^[A-Za-z0-9!#$%'*+-\/=?^_`{|}~][A-Za-z0-9!#$%'*+-\/=?^_`{|}~\.]{0,6...@[a-za-z0-9-]+(\.[a-zA-Z0-9-]{1,})*\.([a-zA-Z]{2,4}){1}$
works very well
not good: eg:
fails this valid address* : ad...@xxx.museum
yes it does, but all I need to change is {2,4}, to {2,6} or {2,}
accepts this invalid one : y...@gmail..com
and not it does not. I just tested it here.
The regex helps to avoid stuff like this:
f...@bar.com
foo@@bar.com
f...@.bar.com
f...@bar
etc
musedoma replaced with several x to protect the innocent from spam
in some contexts email adrresses with no domain part are valid
addresses with [bracketed] mx servers instead of a domain and/or bang
paths are also allowed (but not in common use and often not desirable)
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
★ Clemens 呉 Schwaighofer
★ IT Engineer/Web Producer/Planning
★ E-Graphics Communications SP Digital
★ 6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN
★ Tel: +81-(0)3-3545-7706
★ Fax: +81-(0)3-3545-7343
★ http://www.e-gra.co.jp
This e-mail is intended only for the named person or entity to which it is
addressed and contains valuable business information that is privileged,
confidential and/or otherwise protected from disclosure. If you received this
e-mail in error, any review, use, dissemination, distribution or copying of
this e-mail is strictly prohibited. Please notify us immediately of the error
via e-mail to disclai...@tbwaworld.com and please delete the e-mail from your
system, retaining no copies in any media.We appreciate your cooperation.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] question about /etc/init.d/postgresql in PGDG
Scott Marlowe scott.marl...@gmail.com writes: In the init script from the PGDG rpms there's this block of code: PGDATA=/var/lib/pgsql/data if [ -f $PGDATA/PG_VERSION ] [ -d $PGDATA/base/template1 ] then echo Using old-style directory structure else PGDATA=/var/lib/pgsql/data fi Is it just me, or is the else extra noise? Just wondering if there's a reason a config variable is in two places at once. In the original coding, the first assignment was PGDATA=/var/lib/pgsql and thus the if-test did indeed do something useful with setting PGDATA differently in the two cases. However, there is no reason whatsoever for this initscript to be prepared to work with postmaster versions that would be old enough for the if-test to succeed. I took the whole if-block out of the just-updated Fedora RPMs, and would recommend the same for PGDG. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Could not open relation XXX: No such file or directory
Yaroslav Tykhiy wrote: By the way, `chkdsk' in Windows or `fsck' in Unix can, in a way, be a _source_ of file loss if the file metadata got damaged badly, e.g., by a system crash, and the file node has to be cleared. So I've always been curious if there is a way to retrieve surviving records from a PostgreSQL database damaged by file loss. Do you know any? (Of course, the only true solution is to have been making backups beforehand, but...) The Ubuntu Linux site has this page on data recovery (also applicable to other Linux flavors): https://help.ubuntu.com/community/DataRecovery I assume that a database file, because of its structure, is harder to recover after it becomes corrupt than, say, an XML file. But any port in a storm, right? Thanks! Yar -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] join from array or cursor
John DeSoi wrote: Suppose I have an integer array (or cursor with one integer column) which represents primary keys of some table. Is there a simple and efficient way to return the rows of the table corresponding to the primary key values and keep them in the same order as the array (or cursor)? Seems like it should be easy, but I'm not seeing it. Thanks, John DeSoi, Ph.D. Matching the rows is easy. Getting them in the same order as the items in the array will require an ORDER BY. The contrib package _int has an idx() that you can use for that. SELECT * FROM foo WHERE foo.id = ANY(myPkArray) ORDER BY idx(myPkArray, id) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] DB Design Advice
Hey all,My company is designing a database in which we intend to store data for several customers. We are trying to decide if,A: we want to store all customer data in one set of tables with customer_id fields separating the data or,B: storing each customers data in a separate schema.I'd like to get some opinions on the pros and cons of these methods concerning maintainability, scalability, and performance. I appreciate all the help.Thanks,Ted Handyman Franchises. Click Here. Handyman Franchise http://tagline.excite.com/fc/FgElN1g4lyVXaeM0oABNlMShUDnVduYo52NIx5VGWtaOyzgTn2qDGh2FoNi/
Re: [GENERAL] DB Design Advice
stone...@excite.com wrote: Hey all, My company is designing a database in which we intend to store data for several customers. We are trying to decide if, A: we want to store all customer data in one set of tables with customer_id fields separating the data or, B: storing each customers data in a separate schema. I'd like to get some opinions on the pros and cons of these methods concerning maintainability, scalability, and performance. IMHO, that would depend on what this data is and how you use it.Is this your company's data on your business with these customers, like AR/AP transactions and so forth? Or is this data you're storing for these companies, data thats really 'theirs', and that won't be used together, such as their websites that you host ? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Could not open relation XXX: No such file or directory
On 21/08/2009, at 12:40 PM, Seth Gordon wrote: Yaroslav Tykhiy wrote: By the way, `chkdsk' in Windows or `fsck' in Unix can, in a way, be a _source_ of file loss if the file metadata got damaged badly, e.g., by a system crash, and the file node has to be cleared. So I've always been curious if there is a way to retrieve surviving records from a PostgreSQL database damaged by file loss. Do you know any? (Of course, the only true solution is to have been making backups beforehand, but...) The Ubuntu Linux site has this page on data recovery (also applicable to other Linux flavors): https://help.ubuntu.com/community/DataRecovery I assume that a database file, because of its structure, is harder to recover after it becomes corrupt than, say, an XML file. But any port in a storm, right? Excuse me, but my curiosity was about a somewhat different thing. Let's assume we did file system level data recovery but lost just a couple of files from $PGDATA/base that were damaged hopelessly. Now, if we start pgsql and try accessing the database, pgsql will fail as soon as it hits a missing file. So I wondered if there was a way to tell pgsql to ignore such errors at the cost of returning possibly inconsistent and corrupted data. It has just occurred to me that recreating the files zero-filled is another option to try. As long as the objects stored in the database are small and/or uncompressed, screwing up a few pages shouldn't affect data from the other pages, right? Yar -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] DB Design Advice
stone...@excite.com wrote: Hey all, My company is designing a database in which we intend to store data for several customers. We are trying to decide if, A: we want to store all customer data in one set of tables with customer_id fields separating the data or, B: storing each customers data in a separate schema. I'd like to get some opinions on the pros and cons of these methods concerning maintainability, scalability, and performance. MHO, that would depend on what this data is and how you use it. Yeah, I figured you might say that. Id this your company's data on your business with these customers, like AR/AP transactions and so forth? Or is this data you're storing for these companies, data thats really 'theirs', and that won't be used together, such as their websites that you host ? I'd say that the data is more 'theirs' then ours. Customers' data shouldn't be used together but we may occasionally compare customer data. I'll also add that each customer should have a fairly significant amount of data. Thanks again for the help. Ted Click here for to find products that will help grow your small business. Small Business Tools http://tagline.excite.com/fc/FgElN1gzL0niqPjd1mHomvN0Xtfz5dmDbmfxeHGZEqyRy5wuc3qHX737QJa/
