[GENERAL] bayesian classification over tsvector
I was wondering if there is some apps, technique, tool to get inspiration or just use to classify rows according to a weighted tsvector. Each row contain a tsvector obtained concatenating 4 rows with different weight. I've a corpus for each group. I'd like to classify the rows that haven't been assigned to a group already. thanks -- 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] 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/
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. 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
[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] 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
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] question about /etc/init.d/postgresql in PGDG
Scott Marlowe 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] Postgre RAISE NOTICE and PHP
On Thu, Aug 20, 2009 at 21:52, Jasen Betts wrote: > On 2009-08-19, Clemens Schwaighofer wrote: >> On Wed, Aug 19, 2009 at 02:11, Randal L. Schwartz >> wrote: "Andre" == Andre Lopes 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
[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: R: [GENERAL] Field's position in Table
On Thu, Aug 20, 2009 at 7:45 PM, Yaroslav Tykhiy 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
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: [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
Enc: Res: [GENERAL] Function for replace
- Mensagem encaminhada De: paulo matadr Para: Sam Mason 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 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] 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
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
[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/
[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] 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] 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: 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.
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
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] 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] ERROR: could not access file "$libdir/xxid": No such file or directory
On 20/08/2009 10:20 PM, Tom Lane wrote: Jorge Daine Quiambao 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
[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] unique index for periods
Greg Stark writes: > On Thu, Aug 20, 2009 at 3:14 PM, Tom Lane 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] 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 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] Generating random unique alphanumeric IDs
On Thu, 20 Aug 2009 13:34:51 +0100 Thom Brown 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 i<3 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 i<3 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
Re: [GENERAL] unique index for periods
On Thu, Aug 20, 2009 at 3:14 PM, Tom Lane 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
[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 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] 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
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] 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?
On Thu, Aug 20, 2009 at 8:28 AM, Scara Maccai 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
[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] ERROR: could not access file "$libdir/xxid": No such file or directory
Jorge Daine Quiambao 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
Re: [GENERAL] unique index for periods
Gerhard Heift 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
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 Quiambao 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] Temp table or normal table for performance?
On 2009-08-19, 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. 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] PL/PGSQL: why IF test the whole condition before failing or not?
On 2009-08-18, Suporte PK 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] Postgre RAISE NOTICE and PHP
On Thu, Aug 20, 2009 at 01:03:47PM +, Jasen Betts wrote: > On 2009-08-20, Randal L. Schwartz 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] Postgre RAISE NOTICE and PHP
On 2009-08-20, Randal L. Schwartz wrote: >> "Clemens" == Clemens Schwaighofer >> 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] Best database model for canvassing (and analysing) opinion
Quoth Sam Mason : [...] > 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] 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] Postgre RAISE NOTICE and PHP
On 2009-08-19, Clemens Schwaighofer wrote: > On Wed, Aug 19, 2009 at 02:11, Randal L. Schwartz > wrote: >>> "Andre" == Andre Lopes 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] 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] 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] 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] 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] 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
[GENERAL] Re: Unit conversion database (was: multiple paramters in aggregate function)
On 2009-08-19, Alban Hertroys 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 , >> 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] 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
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 Quiambao 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
[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] unique index for periods
In article <20090820065819.ga2...@gheift.kawo1.rwth-aachen.de>, Gerhard Heift 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
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] Generating random unique alphanumeric IDs
On Mon, 17 Aug 2009 12:37:33 +0200 "Daniel Verite" 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 i<3 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::bigint<<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 i<3 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::bigint<<16) + 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: [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
[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: R: [GENERAL] Field's position in Table
On Thu, 20 Aug 2009 09:21:25 + (GMT), Scara Maccai 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
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: [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
[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!
[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
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 unitid<>4; 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
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