Re: [GENERAL] [Q] parsing out String array
Thank you for your recommendation
I was able to get this to work
(using PG SQL parser to parse out two dimentional PG array
where the array data came from my client program instead of
a DB value)
select ('{{A,B,C},{D,E,F}}'::text[][])[i][j] from
generate_series(1, array_upper('{{A,B,C},{D,E,F}}'::text[][], 1)) i
cross join
generate_series(1, array_upper('{{A,B,C},{D,E,F}}'::text[][], 2)) j
On Sat, 15 Aug 2009 21:17 +0100, Sam Mason s...@samason.me.uk wrote:
On Sat, Aug 15, 2009 at 01:41:12PM -0400, V S P wrote:
but running into a problem as I cannot typecast correctly
select V[i][j]
FROM
(select '{{A,B,C},{D,E,F}}') as V
CROSS JOIN generate_series(1, 3) i
CROSS JOIN generate_series(1,2) j
Does not work, because V is not an array (it is a string)
and I do not know where to put the typecast
::text[][]
Not sure what you tried, but the following does the right thing for
me:
SELECT ('{{A,B,C},{D,E,F}}'::text[])[1][1];
The brackets are needed so that PG doesn't get confused between the type
declaration and the array indexing.
--
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
--
Vlad P
author of C++ ORM http://github.com/vladp/CppOrm/tree/master
--
http://www.fastmail.fm - The professional email service
--
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] [Q] parsing out String array
On Sun, Aug 16, 2009 at 05:55:08AM -0400, torea...@fastmail.fm wrote:
Thank you for your recommendation
I was able to get this to work
select ('{{A,B,C},{D,E,F}}'::text[][])[i][j] from
generate_series(1, array_upper('{{A,B,C},{D,E,F}}'::text[][], 1)) i
cross join
generate_series(1, array_upper('{{A,B,C},{D,E,F}}'::text[][], 2)) j
I've just noticed that you mentioned using 8.4 previously; there's a new
function called unnest in 8.4 that does the above. In your example it
would be:
SELECT unnest('{{A,B,C},{D,E,F}}'::text[]);
and you'd get back your six rows. It appears to do the correct thing
with arrays of higher dimensionality to me, but I only tried up to five.
--
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
[GENERAL] Generating random unique alphanumeric IDs
Does anyone know a way to generate a random and unique lowercase alphanumeric ID (preferably without using 0, 1, o or i to prevent problems with users manually typing the ID) using SQL without resorting to a prerendered table or using GUIDs. For example, if I were to ask for an ID of 5 characters, something like the following would be returned: hn21o 8sp2j 9wwun m7z02 Notice that I don't mean hexadecimal values either. This would preferrably not resort to trying to generate the ID, then checking for a clash, and if there is one, do it again, although that could do as I can't think of how the ideal solution of a ID hashing algorithm would be possible. Any ideas? Thanks Thom
Re: [GENERAL] A history procedure that prevents duplicate entries
On 16 Aug 2009, at 4:24, Madison Kelly wrote: Hi all, ... CREATE FUNCTION history_radical() RETURNS trigger AS $$ DECLARE hist_radical RECORD; BEGIN SELECT INTO hist_radical * FROM public.radical WHERE rad_id=new.rad_id; I assume you mean to only select an existing record here in case the trigger is fired on an update? You are in fact always selecting at least one record here because this is called from an AFTER INSERT OR UPDATE trigger; the record has already been inserted or updated, so the select statement will find the new (version of) the record. I'm also not entirely sure what the value is of calling your procedure on INSERT. If I interpreted you correctly the same data would be added to the history the first time it gets updated (except for the different timestamp and history id of course). I'd probably just call this procedure on UPDATE, and on DELETE too. If you do want to fire on INSERT I'd make it clear there was no data before that history entry, for example by filling the record with NULL values or by adding a column for the value of TG_OP to the history table. Besides that, you don't need the SELECT statement or the RECORD-type variable as the data you need is already in the NEW and OLD records. But, you only have an OLD record when your trigger fired from an UPDATE, so you need to check whether your trigger fired from INSERT or UPDATE. So, what you need is something like: IF TG_OP = 'INSERT' THEN hist_radical := NEW; ELSE -- TG_OP = 'UPDATE' hist_radical := OLD; END IF; INSERT INTO history.radical (rad_id, rad_char, rad_name) VALUES (hist_radical.rad_id, hist_radical.rad_char, hist_radical.rad_name); INSERT INTO history.radical (rad_id, rad_char, rad_name) VALUES (hist_radical.rad_id, hist_radical.rad_char, hist_radical.rad_name); RETURN NULL; END;$$ LANGUAGE plpgsql; CREATE TRIGGER trig_radical AFTER INSERT OR UPDATE ON radical FOR EACH ROW EXECUTE PROCEDURE history_radical(); Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4a87e8d010131556343596! -- 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 Sun, Aug 16, 2009 at 12:07:27PM +0100, Thom Brown wrote:
Does anyone know a way to generate a random and unique lowercase
alphanumeric ID
If you want it to be unique then it's not going to be random. The
easiest way to keep it from producing duplicates is to have some
monotonically increasing component. If you're OK with code/people
retrying the occasional duplicate then you're going to be relying on
statistical guarantees and you should look at birthday attacks to see
how often this is going to happen.
Notice that I don't mean hexadecimal values either. This would preferrably
not resort to trying to generate the ID, then checking for a clash, and if
there is one, do it again, although that could do as I can't think of how
the ideal solution of a ID hashing algorithm would be possible.
The following is the obvious PGSQL code, you'd obviously need something
else to stop duplicates.
SELECT array_to_string(array((
SELECT SUBSTRING('abcdefghjklmnpqrstuvwxyz23456789'
FROM mod((random()*32)::int, 32)+1 FOR 1)
FROM generate_series(1,5))),'');
As this only generates five characters and each character can be one of
32 values, you've got about 33554432 choices and you'd have a 50% chance
of getting a duplicate after 7240 values. This assumes I wrote the
above code correctly. It's also not amazing because PG's random number
generator is defined to return a value between 0 and 1 inclusive, it's
generally much more useful if it runs from 0 to less than 1 and would
mean that I wouldn't need the mod above and would remove the (slight)
biasing towards choosing 'a'.
--
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
The following is the obvious PGSQL code, you'd obviously need something
else to stop duplicates.
SELECT array_to_string(array((
SELECT SUBSTRING('abcdefghjklmnpqrstuvwxyz23456789'
FROM mod((random()*32)::int, 32)+1 FOR 1)
FROM generate_series(1,5))),'');
As this only generates five characters and each character can be one of
32 values, you've got about 33554432 choices and you'd have a 50% chance
of getting a duplicate after 7240 values. This assumes I wrote the
above code correctly. It's also not amazing because PG's random number
generator is defined to return a value between 0 and 1 inclusive, it's
generally much more useful if it runs from 0 to less than 1 and would
mean that I wouldn't need the mod above and would remove the (slight)
biasing towards choosing 'a'.
That does actually work! I'm not sure why you're saying that there's a 50%
chance of duplication after 7240 values though. With 33 million
combinations, I would have thought that duplications would become equally
likely at the 16,777,216 mark.
I hadn't thought of coding it the way you did, which is an interesting way
of approaching it!
Thom
Re: [GENERAL] A history procedure that prevents duplicate entries
Hi Madi,
I think you want to use foreign keys which can give you these checks. So
add a foreign key to create a link between rad_id of both tables.
regards,
Bastiaan
Madison Kelly wrote:
Hi all,
I've been using a procedure to make a copy of data in my public
schema into a history schema on UPDATE and INSERTs.
To prevent duplicate entries in the history, I have to lead in the
current data, compare it in my program and then decided whether
something has actually changed or not before doing an update. This
strikes me as wasteful coding and something I should be able to do in
my procedure.
Given the following example tables and procedure, how could I go
about changing it to prevent duplicate/unchanged entries being saved
to the history schema? Even a pointer to a relevant section of the
docs would be appreciated... My knowledge of procedures is pretty
weak. :)
Madi
CREATE TABLE radical
(
rad_idintegerprimary key
default(nextval('id_seq')),
rad_chartextnot null,
rad_nametext
);
CREATE TABLE history.radical
(
rad_idintegernot null,
rad_chartextnot null,
rad_nametext,
hist_id integer not null
default(nextval('hist_seq')),
modified_date timestamp default now()
);
CREATE FUNCTION history_radical() RETURNS trigger
AS $$
DECLARE
hist_radical RECORD;
BEGIN
SELECT INTO hist_radical * FROM public.radical WHERE
rad_id=new.rad_id;
INSERT INTO history.radical
(rad_id, rad_char, rad_name)
VALUES
(hist_radical.rad_id, hist_radical.rad_char,
hist_radical.rad_name);
RETURN NULL;
END;$$
LANGUAGE plpgsql;
CREATE TRIGGER trig_radical AFTER INSERT OR UPDATE ON radical FOR
EACH ROW EXECUTE PROCEDURE history_radical();
Re: [GENERAL] Generating random unique alphanumeric IDs
On Sun, 16 Aug 2009 12:48:39 +0100 Sam Mason s...@samason.me.uk wrote: On Sun, Aug 16, 2009 at 12:07:27PM +0100, Thom Brown wrote: Does anyone know a way to generate a random and unique lowercase alphanumeric ID If you want it to be unique then it's not going to be random. The easiest way to keep it from producing duplicates is to have some monotonically increasing component. If you're OK with code/people retrying the occasional duplicate then you're going to be relying on statistical guarantees and you should look at birthday attacks to see how often this is going to happen. Notice that I don't mean hexadecimal values either. This would preferrably not resort to trying to generate the ID, then checking for a clash, and if there is one, do it again, although that could do as I can't think of how the ideal solution of a ID hashing algorithm would be possible. Sometimes ago Daniel Verite posted an implementation of a fiestel cipher in plpgsql. I'm happily using it to generate pseudo-random hex strings. -- 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] Generating random unique alphanumeric IDs
On Sun, Aug 16, 2009 at 12:57:34PM +0100, Thom Brown wrote:
SELECT array_to_string(array((
SELECT SUBSTRING('abcdefghjklmnpqrstuvwxyz23456789'
FROM mod((random()*32)::int, 32)+1 FOR 1)
FROM generate_series(1,5))),'');
I've just had a look and PG does actually seem to be returning values as
I'd expect, i.e. 0 = n 1. So the following would work:
floor(random()*32)::int+1
instead of the mod hack. Distribution looks reasonably flat (this is
good):
char %occurances
1 3.1222
2 3.1329
3 3.1269
4 3.1236
5 3.1233
6 3.1310
7 3.1226
8 3.1298
9 3.1229
10 3.1294
11 3.1192
12 3.1249
13 3.1267
14 3.1236
15 3.1190
16 3.1279
17 3.1232
18 3.1218
19 3.1314
20 3.1091
21 3.1337
22 3.1239
23 3.1184
24 3.1347
25 3.1205
26 3.1160
27 3.1219
28 3.1344
29 3.1118
30 3.1256
31 3.1408
32 3.1255
I'm not sure why you're saying that there's a 50%
chance of duplication after 7240 values though. With 33 million
combinations, I would have thought that duplications would become equally
likely at the 16,777,216 mark.
No, that's why I pointed out birthday attacks---collisions happen much
more often than you'd expect. Get 23 people in a room and you have a
50% chance of two people having the same birthday--not 150 people. This
is why it's called the birthday attack and it's one of the basic tests
for hash functions--any bias in their output will shrink this number
even further.
--
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
[GENERAL] Rapid Seek Devices (feature request)
Hi all I have some idea/feature request. Now, there are several devices available, that can be called rapid seek devices (RSD in future text). I mean SSD disks, some devices like gigabyte I-RAM and other (semi)profesional ram disk like solutions for example Acard ANS-9010 . Rapid seek because there are not moving parts, thus non-sequential access (seeks) have no penalty (no moving heads time). I think it would be cool to have explicitly support for that kind of devices. 1: Postgresql has its tablespaces, so creating a tablespce on RSD and put some speed critical indexes/tables there, is optimal from the costs point (RSD are not cheap, so use it for the speed critical tasks within single database is reasonable) but it is only a half of the needed. Query planner act strongly pro sequentional reads while it try to guess best query plan. This bias is fine for classic disk based storage, but is not necesery in RSD. So, for tables/indexes on RSD, a suboptimal query plan is likely to be generated (fix me if I am wrong). So I suggest we should have random_page_cost and Sequential_page_cost configurable on per tablespace basis. And query planner that is aware of it, include situation where tables and corresponding indexes are on different speed tablespaces. Imagine this scenario: Default tablespace on sata disk, random_page_cost=8, sequential_page_cost=3 (from config file), CREATE TABLESPACE fast /path/to/multiple_15k_rpm_SCSI_hw_RAID random_page_cost=4 sequential_page_cost=2 CREATE TABLESPACE lightspeed /path/to/SSD_OR_I-RAM random_page_cost=1 sequential_page_cost=1.2 and now scatter your tables / indexes around new tablespaces :-) 2: Many of that RSD devices are not so much reliable (power outage in ramdisk, weak auxillarity battery in i-ram like devices, block wear out in SSD). While moving only an indexes to this device ( I found this article showing there IS a big difference having only an indexes on SSD - http://linux.com/archive/feature/142658.) may be appropriate, and just reindex in worst case, this is not suitable in high availability enviroment. So I suggest to have something like this to solve reliability problems on some RSD: CREATE TABLESPACE lightspeed /path/to/SSD_OR_i-RAM random_page_cost=1 sequential_page_cost=1.2 TEE name_of_the_slow_tablespace ie read from fast tablespace, write to both fast and slow, reconstruct fast from slow if appropriate. Thanx for your attention. PS: Execuse my wrong english -- 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] A history procedure that prevents duplicate entries
Alban Hertroys wrote: On 16 Aug 2009, at 4:24, Madison Kelly wrote: Hi all, ... CREATE FUNCTION history_radical() RETURNS trigger AS $$ DECLARE hist_radical RECORD; BEGIN SELECT INTO hist_radical * FROM public.radical WHERE rad_id=new.rad_id; I assume you mean to only select an existing record here in case the trigger is fired on an update? You are in fact always selecting at least one record here because this is called from an AFTER INSERT OR UPDATE trigger; the record has already been inserted or updated, so the select statement will find the new (version of) the record. I'm also not entirely sure what the value is of calling your procedure on INSERT. If I interpreted you correctly the same data would be added to the history the first time it gets updated (except for the different timestamp and history id of course). I'd probably just call this procedure on UPDATE, and on DELETE too. If you do want to fire on INSERT I'd make it clear there was no data before that history entry, for example by filling the record with NULL values or by adding a column for the value of TG_OP to the history table. The INSERT is there mainly for my convenience. If I am going to the history schema to get data, it's convenient to know that is has a complete copy of the data in the public schema, too. Besides that, you don't need the SELECT statement or the RECORD-type variable as the data you need is already in the NEW and OLD records. But, you only have an OLD record when your trigger fired from an UPDATE, so you need to check whether your trigger fired from INSERT or UPDATE. So, what you need is something like: IF TG_OP = 'INSERT' THEN hist_radical := NEW; ELSE -- TG_OP = 'UPDATE' hist_radical := OLD; END IF; INSERT INTO history.radical (rad_id, rad_char, rad_name) VALUES (hist_radical.rad_id, hist_radical.rad_char, hist_radical.rad_name); Alban Hertroys To help me improve my understanding of procedures, how would this prevent an UPDATE from creating a new entry in the history schema when all the column values are the same as the last entry in history? Thanks!! Madi -- 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
Thom Brown wrote: I'm not sure why you're saying that there's a 50% chance of duplication after 7240 values though. With 33 million combinations, I would have thought that duplications would become equally likely at the 16,777,216 mark. Basic probability. http://en.wikipedia.org/wiki/Birthday_attack http://en.wikipedia.org/wiki/Birthday_problem Sam Mason wrote: No, that's why I pointed out birthday attacks---collisions happen much more often than you'd expect. Get 23 people in a room and you have a 50% chance of two people having the same birthday--not 150 people. This is why it's called the birthday attack and it's one of the basic tests for hash functions--any bias in their output will shrink this number even further. Taking the birthday example, the chance that no two people in a group of size n 365 have the same birthday (irrespective of year) is (365-0)/365 x (365-1)/365 x (365-2)/365 x (365-3)/365 ... x (365 - (n-1))/365 As each term in the expression is less than one, their multiplication together rapidly approaches zero. That means the probability of two or more people having the same birthday rapidly approaches one. The 50% point is a bit under n=23. Substitute 33 million for 365 for the OP's problem. -- Lew -- 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
I would have thought that duplications would become equally likely at the 16,777,216 mark. Yes, at that point you're as likely to get a duplicate as a unique one--every time you do it. You're likely to see your first duplicate long before that point. In fact, it would be extremely unlikely to get to that point without having generated any duplicates. -- Scott Ribe scott_r...@killerbytes.com http://www.killerbytes.com/ (303) 722-0567 voice -- 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] bigint to ip
Mindaugas G. mindau...@biovela.lt writes: for example I have db with ip_src etc ant its data type bigint, how must look select query what I can see ip address (int) instead of bigint? You might be interrested into the ip4r project: http://pgfoundry.org/projects/ip4r/ Regards, -- dim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] plpgsql function to validate e-mail
Hi, I need a plpgsql function to validade e-mail addresses. I have google but I can't find any. My question: Anyone have a function to validate e-mails? Best Regards, André.
Re: [GENERAL] plpgsql function to validate e-mail
On 16/08/2009 21:10, Andre Lopes wrote: I need a plpgsql function to validade e-mail addresses. I have google but I can't find any. My question: Anyone have a function to validate e-mails? There are lots of regular expressions which Google will find for you, which you can then use with one of the built-in functions and operators that can use them. Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 8.4 rpm packaging problem?
Two problems installing the postgresql doc package,,, I just tried to install the 8.4 postgresql-docs and postgresql-contrib packages from the pgdg84 Fedora-11 yum repository and yum tried to install postresql-docs-8.4rc1 (rather than 8.4.0) from the pgdg repo. The exact sequence of events was to install postresql-8.3.7 from Fedora's repo, download and install the pgdg84 repo rpm, do a yum upgrade to postgresql to 8.4.0 from the pgdg repo (this worked fine), and then do yum install postgresql-docs postgresql-contrib. Is the pgdg84 yum repo messed up? Or did I do something wrong? I'm not very knowledgeable about yum. Second, after getting the postgresql-docs package installed by just downloading and installing the right rpm, I notice the FAQ subdirectory contains the FAQ in many languages, but not english. Intentional? -- 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] 8.4 rpm packaging problem?
Hi, On Sun, 2009-08-16 at 14:59 -0600, Stuart McGraw wrote: I just tried to install the 8.4 postgresql-docs and postgresql-contrib packages from the pgdg84 Fedora-11 yum repository and yum tried to install postresql-docs-8.4rc1 (rather than 8.4.0) from the pgdg repo. Fixed it now. The reason was that I am using two separate servers to build Fedora 10 and 11 packages. One of them tags packages with fc11, the other one tags with f11-- and yum picks up f11. In your case, f11 ones were messed up, and yum failed over to rc packages. Second, after getting the postgresql-docs package installed by just downloading and installing the right rpm, I notice the FAQ subdirectory contains the FAQ in many languages, but not english. Intentional? English FAQ moved to wiki -- it is not even in 8.4.0 tarball. That's why RPM's don't ship it. Regards, -- Devrim GÜNDÜZ, RHCE Command Prompt - http://www.CommandPrompt.com devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org signature.asc Description: This is a digitally signed message part
Re: [GENERAL] 8.4 rpm packaging problem?
Devrim GÜNDÜZ wrote: On Sun, 2009-08-16 at 14:59 -0600, Stuart McGraw wrote: Second, after getting the postgresql-docs package installed by just downloading and installing the right rpm, I notice the FAQ subdirectory contains the FAQ in many languages, but not english. Intentional? English FAQ moved to wiki -- it is not even in 8.4.0 tarball. That's why RPM's don't ship it. Huh, but the tarball does not contain the FAQs in other languages either. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- 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] 8.4 rpm packaging problem?
On Sun, 2009-08-16 at 18:35 -0400, Alvaro Herrera wrote: Huh, but the tarball does not contain the FAQs in other languages either. See doc/src/FAQ directory in 8.4.0 tarball. -- Devrim GÜNDÜZ, RHCE Command Prompt - http://www.CommandPrompt.com devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org signature.asc Description: This is a digitally signed message part
Re: [GENERAL] Generating random unique alphanumeric IDs
On Aug 16, 2009, at 5:07 AM, Thom Brown wrote: Does anyone know a way to generate a random and unique lowercase alphanumeric ID (preferably without using 0, 1, o or i to prevent problems with users manually typing the ID) using SQL without resorting to a prerendered table or using GUIDs. For example, if I were to ask for an ID of 5 characters, something like the following would be returned: hn21o 8sp2j 9wwun m7z02 Notice that I don't mean hexadecimal values either. This would preferrably not resort to trying to generate the ID, then checking for a clash, and if there is one, do it again, although that could do as I can't think of how the ideal solution of a ID hashing algorithm would be possible. One way is to use a LFSR (linear feedback shift register function). I haven't used one in a long time but I recall generating pseudo random numbers that are guaranteed not to repeat after billions of iterations. It's very fast as well. Then translate the resulting integer into the character sequence of your choosing. Here is a reference: http://en.wikipedia.org/wiki/Linear_feedback_shift_register Bob Gobeille Hewlett Packard Open Source Program Office (and http://fossology.org)
Re: [GENERAL] 8.4 rpm packaging problem?
Devrim GÜNDÜZ wrote: On Sun, 2009-08-16 at 18:35 -0400, Alvaro Herrera wrote: Huh, but the tarball does not contain the FAQs in other languages either. See doc/src/FAQ directory in 8.4.0 tarball. Hmm, this is strange -- the directory is not there in CVS ... /me checks CVS history Oh, I see -- the files were removed after the release of 8.4.0 apparently :-( -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- 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
Ivan Sergio Borgonovo escribió: Sometimes ago Daniel Verite posted an implementation of a fiestel cipher in plpgsql. It's in the wiki, in the Snippets area. wiki.postgresql.org/wiki/Snippets (pseudo encrypt or something like that I think it's called) -- 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] plpython return setof and yield
Hi,
I am kind of new into python, and I have been trying to port some plperl
functions into plpython, but I've run up into a problem.
Imagine the following plpython code.
CREATE OR REPLACE FUNCTION greet (how text)
RETURNS SETOF greeting
AS $$
rv = plpy.execute(SELECT 1)
for article in range(10) :
for other in range(10) :
if (other == 1) : continue
yield([article,other])
$$LANGUAGE plpythonu;
When executing the function on the psql console I always the this error.
netbo-dev=# select * from greet('Nuno');
ERROR: error fetching next item from iterator
If I comment the first line:
rv = plpy.execute(select 1)
Turning the code into this:
CREATE OR REPLACE FUNCTION greet (how text)
RETURNS SETOF greeting
AS $$
#rv = plpy.execute(SELECT 1)
for article in range(10) :
for other in range(10) :
if (other == 1) : continue
yield([article,other])
$$LANGUAGE plpythonu;
The code works:
netbo-dev=# select * from greet('Nuno');
how | who
-+-
0 | 0
0 | 2
0 | 3
0 | 4
0 | 5
0 | 6
I know the example code is not the best, but What I was tryng to do is
execute some SQL then process it and return it back.
I also know I could just generate the rows and place them in a list and then
return it, but I would like to know why the yield function does'nt work in
this case.
This was tried on with:
PostgreSQL 8.3.7 and Python 2.5.1
Thanks,
Nuno Mota
Re: [GENERAL] plpgsql function to validate e-mail
Andre Lopes lopes80an...@gmail.com writes: My question: Anyone have a function to validate e-mails? Check the PG archives --- this has been discussed before. IIRC you can't *really* validate them, short of actually sending mail. But there are partial solutions in the archives. 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] Generating random unique alphanumeric IDs
On Sun, Aug 16, 2009 at 04:53:01PM -0600, Bob Gobeille wrote: One way is to use a LFSR (linear feedback shift register function). I haven't used one in a long time but I recall generating pseudo random numbers that are guaranteed not to repeat after billions of iterations. It's very fast as well. Then translate the resulting integer into the character sequence of your choosing. Here is a reference: http://en.wikipedia.org/wiki/Linear_feedback_shift_register Not sure if this is very applicable; LFSRs can have a very long period, or interval before they repeat (i.e. their internal state is the same as it was before) but individual numbers *will* be repeated. The performance claims tend only to apply to hardware implementations, there are much faster pseudo-random number generators available for software. The fastest one I found recently is a SIMD implementation of the Mersenne Twister called SFMT[1]. -- Sam http://samason.me.uk/ [1] http://www.math.sci.hiroshima-u.ac.jp/~m-mat/MT/SFMT/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Failover, Wal Logging, and Multiple Spares
Assuming we are running a Postgres instance that is shipping log files to 2 or more warm spares, is there a way I can fail over to one of the spares, and have the second spare start receiving updates from the new master without missing a beat? I can live with losing the old master, and at least at the moment it would be a controlled failover, but I would like to to know if it's possible during an uncontrolled failover as well (catastrophic hardware failure). Right now, we have just that setup, but every time I've failed over to the new master, we've had to rebuild our spares from scratch and unfortunately this is a multi-hour long process. We can't afford the risk of not having a warm spare for that length of time. We're planning to move entirely to a slony cluster, but I'd like to fail over to a more powerful machine before we begin the slony migration as the current server is already overloaded. Thanks, Bryan
[GENERAL] Database Security
hi, i am near to PostgreSql. I create a database by the superuser. Then i create another logon user. Now how can i protect my database from the new user. i want to ban the access and view the database to the new user.It can done by an entry in pg_hba file. Is there any other way to secure my database(like setting password)?. Please help to solve my problem.
[GENERAL] Function Logging
Hi All, We have recently upgraded postgres from 7.2 to 8.3. I am struggling with the logging options when it comes to functions. How do log statements from within a Function? Thanks Andrew Bartley
Re: [GENERAL] Rapid Seek Devices (feature request)
On 16/08/2009 9:06 PM, NTPT wrote: So I suggest we should have random_page_cost and Sequential_page_cost configurable on per tablespace basis. That strikes me as a REALLY good idea, personally, though I don't know enough about the planner to factor in implementation practicalities and any cost for people _not_ using the feature. 2: Many of that RSD devices are not so much reliable (power outage in ramdisk, weak auxillarity battery in i-ram like devices, block wear out in SSD). [snip] ie read from fast tablespace, write to both fast and slow, reconstruct fast from slow if appropriate. This can probably be done as well or better at the OS block layer, using device-mapper or the `md' driver (on Linux). What'd be interesting, though, would be if Pg had support for auto-rebuilding indexes and (if/when explicit support is added) materialized views if it finds the backing files are missing. This would be helpful for such transient devices as RAM disks if you didn't want to bother having physical storage backing it. -- 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] plpgsql function to validate e-mail
Tom Lane wrote: Andre Lopes lopes80an...@gmail.com writes: My question: Anyone have a function to validate e-mails? Check the PG archives --- this has been discussed before. IIRC you can't *really* validate them, short of actually sending mail. And getting a reply. But there are partial solutions in the archives. Even a valid email can be invalid. I periodically receive emails meant for a parent of a ten-year child who has signed up for a kids' site, but who misspelled their parent's email address and put mine in by mistake. I also get emails meant for a contractor in New England whose email address is similar to mine, but has a zero where mine has an o (letter oh). In both cases the email address is valid in and of itself, but is not valid for the purpose intended. -- Lew -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Function Logging
Hi All, We have recently upgraded postgres from 7.2 to 8.3. I am struggling with the logging options when it comes to functions. How do log statements from within a Function? Thanks Andrew Bartley
Re: [GENERAL] plpgsql function to validate e-mail
Hello
2009/8/16 Andre Lopes lopes80an...@gmail.com:
Hi,
I need a plpgsql function to validade e-mail addresses. I have google but I
can't find any.
My question: Anyone have a function to validate e-mails?
Best Regards,
André.
You don't need plpgsql. Important is only an using of regular expression.
very strong validation should be done via plperlu
CREATE OR REPLACE FUNCTION check_email(varchar)
RETURNS boolean AS $$
use strict;
use Email::Valid;
my $address = $_[0];
my $checks = {
-address = $address,
-mxcheck = 1,
-tldcheck = 1,
-rfc822 = 1,
};
if (defined Email::Valid-address( %$checks )) {
return 'true'
}
elog(WARNING, address failed $Email::Valid::Details check.);
return 'false';
$$ LANGUAGE plperlu IMMUTABLE STRICT;
postgres=# CREATE DOMAIN email AS varchar CHECK(check_email(value));
CREATE DOMAIN
postgres=# SELECT 'pavel@'::email;
WARNING: address failed rfc822 check.
postgres=# select 'steh...@kix.fsv.cvut.cz'::email;
email
-
steh...@kix.fsv.cvut.cz
(1 row)
regards
Pavel Stehule
--
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] Function Logging
On 17/08/2009 10:32 AM, Andrew Bartley wrote: Hi All, We have recently upgraded postgres from 7.2 to 8.3. I am struggling with the logging options when it comes to functions. How do log statements from within a Function? A PL/PgSQL function? You don't, generally. Perhaps we should back up a step or two. Did you have statement logging within PL/PgSQL functions in 7.2? If so, how? Why do you need statement logging within functions? What are you trying to achieve with it? -- 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] Function Logging
Thanks Craig fro you reply, 7.2 does have statement logging from within functions. How? these are the settings we use in our production environments. (7.2) debug_level = 2 log_connections = off log_pid = on log_timestamp = on syslog = 0 Why, we use logging fro production problem resolution. We have been using Postgres for 8 years, hundreds of functions have been written, we have become reliant on logging for production support. Thanks Andrew Bartley PS on this note, how do we log PIDS? This is another need to have for our production systems. We log user activity and trace DB activity through the PIDS. 2009/8/17 Craig Ringer cr...@postnewspapers.com.au On 17/08/2009 10:32 AM, Andrew Bartley wrote: Hi All, We have recently upgraded postgres from 7.2 to 8.3. I am struggling with the logging options when it comes to functions. How do log statements from within a Function? A PL/PgSQL function? You don't, generally. Perhaps we should back up a step or two. Did you have statement logging within PL/PgSQL functions in 7.2? If so, how? Why do you need statement logging within functions? What are you trying to achieve with it? -- 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] Generating random unique alphanumeric IDs
On Sun, Aug 16, 2009 at 6:12 PM, Alvaro
Herreraalvhe...@commandprompt.com wrote:
It's in the wiki, in the Snippets area.
wiki.postgresql.org/wiki/Snippets
(pseudo encrypt or something like that I think it's called)
Here's a simple 255 value linear feedback shift register. It's
nothing fancy, but works as an example. It's not any kind of a secure
sequence, but can be handy for generating pseudo random codes for
things like identifiers that need to not be sequential.
create table lfsr (b bit(8));
insert into lfsr values ('10100011');
create or replace function lf() returns bit(8) language sql as $$
update lfsr set b=(select
((substring(b,1,1)#substring(b,3,1)#substring(b,4,1)#substring(b,5,1)))::bit(8)7|(b1)
from lfsr) ;
select b from lfsr $$;
create table l (b bit(8), i int);
insert into l select lf(),generate_series(1,255);
select count(distinct(b)) from l;
select b, count(b) from l group by b having count(b) 1;
insert into l select lf(),generate_series(1,1);
select b, count(b) from l group by b having count(b) 1;
--
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] plpgsql function to validate e-mail
On Mon, Aug 17, 2009 at 06:43:54AM +0200, Pavel Stehule wrote:
Hello
2009/8/16 Andre Lopes lopes80an...@gmail.com:
Hi,
I need a plpgsql function to validade e-mail addresses. I have google but I
can't find any.
My question: Anyone have a function to validate e-mails?
Best Regards,
André.
You don't need plpgsql. Important is only an using of regular expression.
very strong validation should be done via plperlu
CREATE OR REPLACE FUNCTION check_email(varchar)
RETURNS boolean AS $$
use strict;
use Email::Valid;
my $address = $_[0];
my $checks = {
-address = $address,
-mxcheck = 1,
-tldcheck = 1,
-rfc822 = 1,
};
if (defined Email::Valid-address( %$checks )) {
return 'true'
}
elog(WARNING, address failed $Email::Valid::Details check.);
return 'false';
$$ LANGUAGE plperlu IMMUTABLE STRICT;
If the network interface can ever be down, this function is not in
fact immutable, as it will fail on data that it passed before.
Cheers,
David.
--
David Fetter da...@fetter.org http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fet...@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
--
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] plpgsql function to validate e-mail
2009/8/17 David Fetter da...@fetter.org:
On Mon, Aug 17, 2009 at 06:43:54AM +0200, Pavel Stehule wrote:
Hello
2009/8/16 Andre Lopes lopes80an...@gmail.com:
Hi,
I need a plpgsql function to validade e-mail addresses. I have google but I
can't find any.
My question: Anyone have a function to validate e-mails?
Best Regards,
André.
You don't need plpgsql. Important is only an using of regular expression.
very strong validation should be done via plperlu
CREATE OR REPLACE FUNCTION check_email(varchar)
RETURNS boolean AS $$
use strict;
use Email::Valid;
my $address = $_[0];
my $checks = {
-address = $address,
-mxcheck = 1,
-tldcheck = 1,
-rfc822 = 1,
};
if (defined Email::Valid-address( %$checks )) {
return 'true'
}
elog(WARNING, address failed $Email::Valid::Details check.);
return 'false';
$$ LANGUAGE plperlu IMMUTABLE STRICT;
If the network interface can ever be down, this function is not in
fact immutable, as it will fail on data that it passed before.
This is your code, If I remember well :). I am not sure, if immutable
is incorrect flag. Maybe STABLE is better. This check should be used
very carefully. But it's really strong, much more exact than only
regular expression.
Pavel
Cheers,
David.
--
David Fetter da...@fetter.org http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fet...@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
