[GENERAL] Problem with COPY
Hi everyone, I have a table with a surrogate key which is an integer sequence. Is there a way to load a file using COPY and tell postgresql not to insert into the primary key column? Thanks! Chris -- Christian Rengstl M.A. Klinik und Poliklinik für Innere Medizin II Kardiologie - Forschung Universitätsklinikum Regensburg B3 1.388 Franz-Josef-Strauss-Allee 11 93053 Regensburg Tel.: +49-941-944-7230 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] VACUUM and fsm_max_pages
Hi Scott, hi List Thank you for your answer. I will try to launch one VACUUM FULL the next time, and I will continue to execute VACUUM between two tests. I increased max_fsm_pages until 100, but I think it's not a good solution... Regards, Alexandra Scott Marlowe wrote: On Fri, 2006-07-07 at 01:57, DANTE Alexandra wrote: Good morning List, I have seen several posts on this concept but I don’t find a complete response. I’m using BenchmarkSQL to evaluate PostgreSQL in transaction processing and I work with PostgreSQL 8.1.3 on RHEL4-AS, Itanium-2 processor, 8GB RAM. The database, generated via BenchmarkSQL and used, is a 200-warehouses database and its size is about 20GB. The parameter “max_fsm_pages” is equal to 2 and “max_fsm_relations” to 1000. Between two benchs, I launch a VACUUM but at the end of it, I see that PostgreSQL asks me to increase the “max_fsm_pages” parameters and the value proposed grows with the number of VACUUM launched… Could someone explain me why ? This is an example of the message I have : Free space map contains 20576 pages in 17 relations A total of 2 page slots are in use (including overhead) 128512 page slots are required to track all free space Current limits are : 2 page slots, 1000 relations, using 223 KB Number of page slots needed (128512) exceeds max_fsm_pages (2) HINT : Consider increasing the config parameter “max_fsm_pages” to a value over 128512. In order not to launch a VACUUM FULL, I increase the value of “max_fsm_pages” but is it correct ? Man, I'm really wishing I'd make the time to revamp the vacuum docs like I promised. anyway... Anytime you see a constantly growing need for fsm pages, it's a sign that the fsm isn't big enough and / or the vacuums aren't frequent enough. If they are both big enough and often enough, then it's possible your I/O bandwidth isn't great enough for your load and vacuum needs. in which case the growth of the dead tuples in your store is outrunning your ability to reclaim them. Can you schedule ONE vacuum full to get the system back to something small enough? It may be that you've got so much bloat that your I/O system is now transferring way too much data and vacuum (plain, not full) can't keep up. What did you increase max_fsm_pages to? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Problem with COPY
am 10.07.2006, um 10:21:59 +0200 mailte Christian Rengstl folgendes: Hi everyone, I have a table with a surrogate key which is an integer sequence. Is there a way to load a file using COPY and tell postgresql not to insert into the primary key column? Yes: test=# create table foobar (id serial primary key, name text); NOTICE: CREATE TABLE will create implicit sequence foobar_id_seq for serial column foobar.id NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index foobar_pkey for table foobar CREATE TABLE test=*# copy foobar (name) from stdin; Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself. name1 name2 name3 \. test=*# select * from foobar; id | name +--- 1 | name1 2 | name2 3 | name3 (3 rows) HTH, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47215, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Aim of --enable-thread-safety ?
Hello List, I wonder if this compilation option is really taken into account as PostgreSQL is not multi-threading but multi-processing. I have read that without this option, the libpq won't know anything about threads and may indeed have problems, but could you explain me how this option runs ? It is not clear for me the aim of this option in an multi-processing environment... Is it possible to force PostgreSQL to be multi-threaded ? Thank you for your help. Regards, Alexandra DANTE ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Aim of --enable-thread-safety ?
On Mon, Jul 10, 2006 at 11:47:06AM +0200, DANTE Alexandra wrote: Hello List, I wonder if this compilation option is really taken into account as PostgreSQL is not multi-threading but multi-processing. I have read that without this option, the libpq won't know anything about threads and may indeed have problems, but could you explain me how this option runs ? It enables thread-safety in client libraries. If you don't do this then the client libraries will behave strangly when used in multi-threaded programs. As an example, consider the errno variable. It's a global variable, which obviously doesn't work well in multi-threaded programs. When you enable thread-safety it becomes a thread-local variable. The C library has a number of things like that (locale, timezone, strtok, etc). In modern systems it should always be on for libraries. There's is no way to guarentee your library won't be used in a multithreaded program and the cost is marginal. Really, the option should be changed to default to on. Hope this helps, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
[GENERAL] How to restrict select from table with external validation?
Hello! I have table like CREATE TABLE stats ( username varchar(256) NOT NULL, time int8 NOT NULL, duration int4 NOT NULL, phonenumber varchar(20) NOT NULL, and so on ... ) I have function like CREATE OR REPLACE FUNCTION auth(varchar, varchar) RETURNS bool AS ' ... ' LANGUAGE 'plperlu' VOLATILE; where first argument is username and second is password. This function returns true if username and password validated successfully or false otherwise. I have a user which must do only selects from table stats. My questions is how to restrict access on table stats to this user in way where this user will be able to select only limited set of columns from table stats and only rows with usernames for which this user knows correct passwords validated via auth() function call. Any help will be appreciated. -- Vladimir A. Petrov (aka vap)phone: (+7 8482) 420069 Infopac JSC. Head of the exploitation department. http://infopac.ru ___ ^[:wq ...sed libera nos a malo. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Is OpenFTS dead?
On Jul 9, 2006, at 23:43 , Joshua D. Drake wrote: OpenFTS sits on top of Tsearch2. All it is is some high level apis (perl/python) to create a search engine using PostgreSQL and Tsearch2. Thanks for the clarification, Joshua. In response to the OT's question, do you know if OpenFTS is still an active project? Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Version/Change Management of functions?
On Fri, Jul 07, 2006 at 02:08:08PM -0600, Michael Loftis [EMAIL PROTECTED] wrote a message of 28 lines which said: Since there's no way to directly control whats in the DB via a VCS, further, how do you verify that what is in the DB is also in the VCS, etc? This is not a PostgreSQL-specific problem, not even a DB-specific problem. How to you verify that the code installed in /usr/local/bin is also in the VCS? You rely on procedures, automated procedures (make, scons, ant), firing of the violators, etc. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Aim of --enable-thread-safety ?
Hello Martijn, Thank you for your answer. Just to be sure, the --enable-thread-safety option allows client libraries, which connect via libpq to a PostgreSQL database, to be multi-threaded, but each postgres process launched to etablish the connection is not multi-threaded. Consequently, PostgreSQL can not execute a query in several CPU, the source code of PostgreSQL is not multi-threaded. Is it correct ? Thank you very much for your help. Regards, Alexandra Martijn van Oosterhout wrote: On Mon, Jul 10, 2006 at 11:47:06AM +0200, DANTE Alexandra wrote: Hello List, I wonder if this compilation option is really taken into account as PostgreSQL is not multi-threading but multi-processing. I have read that without this option, the libpq won't know anything about threads and may indeed have problems, but could you explain me how this option runs ? It enables thread-safety in client libraries. If you don't do this then the client libraries will behave strangly when used in multi-threaded programs. As an example, consider the errno variable. It's a global variable, which obviously doesn't work well in multi-threaded programs. When you enable thread-safety it becomes a thread-local variable. The C library has a number of things like that (locale, timezone, strtok, etc). In modern systems it should always be on for libraries. There's is no way to guarentee your library won't be used in a multithreaded program and the cost is marginal. Really, the option should be changed to default to on. Hope this helps, ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Background Writer and performances
Hello List, I’m using BenchmarkSQL to evaluate, characterize and optimize PostgreSQL in transaction processing. I work with PostgreSQL 8.1.3 on RHEL4-AS, Itanium-2 processor, 8GB RAM. The database, generated via BenchmarkSQL and used, is a 200-warehouses database and its size is about 20GB. The best performances are obtained with 9 terminals. My configuration is (I can’t do better…) : - one file system created from a LUN of 34GB, in which all the postmaster logs, WAL files and BenchmarkSQL reports are stored = this corresponds to the disk “sdr” that appears in the charts - one file system created from 4 LUNS of 33GB and a stripped logical volume, on which the data (tables and index) are stored = this corresponds to the disk “sdz”, “sdy”, “sdx”, and “sdw” that appears in the charts My question concerns the Background Writer. The Background process sleeps during “bgwriter_delay” and when it wakes, it scans the shared buffers looking for modified pages. Then it writes these modified pages to disk and evicts those pages from the shared buffers. By doing this, the Background Writer decreases the effects of a CHECKPOINT. I got performances problems with the Background Writer : if I let the default value for the parameters “bgwriter_delay”, “bgwriter_lru_percent”, “bgwriter_lru_maxpages”, “bgwriter_all_percent”, “bgwriter_all_maxpages” or if I set them to the maximum value possible, I don’t see significant differences on the disks activities, disks IO/s, disks write throughput. The tests have been done with one processor, during 10 minutes and with a checkpoint each 5 minutes. Here are my settings from the “postgresql.conf” file: - max_connections = 100 - shared_buffers = 5 - work_mem = 1024 - max_fsm_pages= 2 - max_fsm_relations = 1000 - fsync = on - wal_sync_method = fsync - wal_buffers = 16 - checkpoint_segments = 125 - checkpoint_timeout = 300 - effective_cache_size = 218750 - defaults_statistics_target = 1000 ** I would like to send charts to show you exactly what happens on the server but, with the pictures, this e-mail is not posted on the mailing list. I can send charts to a personal e-mail adress if needed. ** The tests were made by setting : - bgwriter_delay = 200 - bgwriter_lru_percent = 1.0 - bgwriter_lru_maxpages = 5 - bgwriter_all_percent = 0.333 - bgwriter_all_maxpages = 5 and then, the opposite : - bgwriter_delay = 50 - bgwriter_lru_percent = 100 - bgwriter_lru_maxpages = 1000 - bgwriter_all_percent = 100 - bgwriter_all_maxpages = 1000 By comparing the charts, I can see that the checkpoints are less expensive in term of Disk activity, IO/s and disk write throughput when the parameters are set to the maximum values but I don’t not reach to have constant disk IO/s, disk activity, disk write throughput before and after a checkpoint. I was expecting to see more activity on the disks during the bench (and not only a peak during the checkpoint) when the parameters are set to the maximum values. Is it possible ? Have you already experimented the Background Writer ? What results could I obtain by setting properly the parameters “bgwriter_xxx” ? Could somenone explain me how I can used it ? Thank you very much for your help. Regards, Alexandra DANTE ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Aim of --enable-thread-safety ?
[EMAIL PROTECTED] (DANTE Alexandra) wrote: I wonder if this compilation option is really taken into account as PostgreSQL is not multi-threading but multi-processing. I have read that without this option, the libpq won't know anything about threads and may indeed have problems, but could you explain me how this option runs ? It is not clear for me the aim of this option in an multi-processing environment... Is it possible to force PostgreSQL to be multi-threaded ? This option is all about allowing you to have multi-threaded *client* applications. That is, applications that many have multiple threads where threads can hold onto database connections. -- (format nil [EMAIL PROTECTED] cbbrowne linuxfinances.info) http://cbbrowne.com/info/internet.html Q: How many Newtons does it take to change a light bulb? A: Faux! There to eat lemons, axe gravy soup! ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Background Writer and performances
On Mon, Jul 10, 2006 at 02:56:48PM +0200, DANTE Alexandra wrote: ** I would like to send charts to show you exactly what happens on the server but, with the pictures, this e-mail is not posted on the mailing list. I can send charts to a personal e-mail adress if needed. ** The best idea is to upload them to a website. By comparing the charts, I can see that the checkpoints are less expensive in term of Disk activity, IO/s and disk write throughput when the parameters are set to the maximum values but I don?t not reach to have constant disk IO/s, disk activity, disk write throughput before and after a checkpoint. I was expecting to see more activity on the disks during the bench (and not only a peak during the checkpoint) when the parameters are set to the maximum values. Is it possible ? I have very little experience with the bgwriter, but on the whole, I don't think the bgwriter will change the total number of I/Os. Rather, it changes the timing to make them more consistant and the load more even. However, reading the descriptions, there's always going to be an unavoidable spike at checkpoint time because the checkpoint actually needs to sync() the data to disk, whereas the bgwriter merely queues it to the kernel. Have you already experimented the Background Writer ? What results could I obtain by setting properly the parameters ?bgwriter_xxx? ? Could somenone explain me how I can used it ? Looking arond I mostly see that people are still testing. I'm not really in a position to know however... Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] Background Writer and performances
On Mon, Jul 10, 2006 at 05:06:56PM +0200, DANTE Alexandra wrote: Hello List, I have uploaded charts on a ftp server. You can access to these 6 graphs by doing ftp visibull.frec.bull.fr Or more easily, by putting this in your web-browser: ftp://visibull.frec.bull.fr/PGS_bgwriter/ I'm presuming these graphs are traffic to the disks, right? Do you have a measurement of the requests from postgres? I'm not sure how you'd get that but it'd probably help with understanding the graphs. Maybe there's a way to get the kernel to be more aggressive with pushing pages out to disk? It has a bgwriter too... Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] Background Writer and performances
Hello List, I have uploaded charts on a ftp server. You can access to these 6 graphs by doing ftp visibull.frec.bull.fr login : ftp password : ftp You are under the / directory and with ls command you see the directory PGS_bgwriter. In this directory, the 3 charts joined to this e-mail and called “DiskActivity_defaultBgwriter.png”, “DiskIO_defaultBgwriter.png”, “DiskWriteThroughput_defaultBgwriter.png” are obtained by setting : - bgwriter_delay = 200 - bgwriter_lru_percent = 1.0 - bgwriter_lru_maxpages = 5 - bgwriter_all_percent = 0.333 - bgwriter_all_maxpages = 5 and the 3 charts joined to this e-mail and called “DiskActivity_maxBgwriter.png”, “DiskIO_maxBgwriter.png”, “DiskWriteThroughput_maxBgwriter.png” are obtained by setting : - bgwriter_delay = 50 - bgwriter_lru_percent = 100 - bgwriter_lru_maxpages = 1000 - bgwriter_all_percent = 100 - bgwriter_all_maxpages = 1000 I hope someone will give me explanations on the background writer. Thank you very much for your help. Regards, Alexandra DANTE Martijn van Oosterhout wrote: On Mon, Jul 10, 2006 at 02:56:48PM +0200, DANTE Alexandra wrote: ** I would like to send charts to show you exactly what happens on the server but, with the pictures, this e-mail is not posted on the mailing list. I can send charts to a personal e-mail adress if needed. ** The best idea is to upload them to a website. By comparing the charts, I can see that the checkpoints are less expensive in term of Disk activity, IO/s and disk write throughput when the parameters are set to the maximum values but I don?t not reach to have constant disk IO/s, disk activity, disk write throughput before and after a checkpoint. I was expecting to see more activity on the disks during the bench (and not only a peak during the checkpoint) when the parameters are set to the maximum values. Is it possible ? I have very little experience with the bgwriter, but on the whole, I don't think the bgwriter will change the total number of I/Os. Rather, it changes the timing to make them more consistant and the load more even. However, reading the descriptions, there's always going to be an unavoidable spike at checkpoint time because the checkpoint actually needs to sync() the data to disk, whereas the bgwriter merely queues it to the kernel. Have you already experimented the Background Writer ? What results could I obtain by setting properly the parameters ?bgwriter_xxx? ? Could somenone explain me how I can used it ? Looking arond I mostly see that people are still testing. I'm not really in a position to know however... Have a nice day, ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Background Writer and performances
These graphs represent traffic to the disks, and have been generated from a home-made tool based on top, vmstat and iostat. Only PostgreSQL accesses to them, a JVM is launched via BenchmarkSQL but does not access to the disks on which are stored the data. BenchmarkSQL stores its reports on the sdr disk. Concerning the logs of the postmaster, I let the defaults values, so I do not have the queries, timing, statements, ... The fsync is activated and the wal_method_fsync is fsync (by default). Is there a way to be more aggressive with pushing pages out to disk via PostgreSQL ? Regards, Alexandra Martijn van Oosterhout wrote: On Mon, Jul 10, 2006 at 05:06:56PM +0200, DANTE Alexandra wrote: Hello List, I have uploaded charts on a ftp server. You can access to these 6 graphs by doing ftp visibull.frec.bull.fr Or more easily, by putting this in your web-browser: ftp://visibull.frec.bull.fr/PGS_bgwriter/ I'm presuming these graphs are traffic to the disks, right? Do you have a measurement of the requests from postgres? I'm not sure how you'd get that but it'd probably help with understanding the graphs. Maybe there's a way to get the kernel to be more aggressive with pushing pages out to disk? It has a bgwriter too... Have a nice day, ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] VACUUM FULL versus CLUSTER ON
On Sat, 2006-07-08 at 10:20, Joshua D. Drake wrote: Unfortunately it would appear that I cannot vacuum full either as I get an out of memory error: # - Memory - shared_buffers = 5000 # min 16, at least max_connections*2, 8KB each work_mem = 131072 # min 64, size in KB maintenance_work_mem = 524288 # min 1024, size in KB max_stack_depth = 4096 # min 100, size in KB You could decrease your maintenance_work_mem, But honestly, at this point I would do the backup restore method. Also, this kind of points out that you might not have enough swap space. On most database servers there's enough hard drive space laying about to have as large a swap space as you'd like, and I can't count the number of times a large swap has given me enough to time to catch runaway processes and keep an ailing server up and running, albeit hobbling along, rather than having to worry about running out of virtual memory. Unless the memory being allocated here just has to be real memory. But I'm guessing not. Sure, swapping is slow, but at least it will let some memory hungry processes finish. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] VACUUM FULL versus CLUSTER ON
On Mon, 2006-07-10 at 10:50 -0500, Scott Marlowe wrote: On Sat, 2006-07-08 at 10:20, Joshua D. Drake wrote: Unfortunately it would appear that I cannot vacuum full either as I get an out of memory error: # - Memory - shared_buffers = 5000 # min 16, at least max_connections*2, 8KB each work_mem = 131072 # min 64, size in KB maintenance_work_mem = 524288 # min 1024, size in KB max_stack_depth = 4096 # min 100, size in KB You could decrease your maintenance_work_mem, But honestly, at this point I would do the backup restore method. Also, this kind of points out that you might not have enough swap space. On most database servers there's enough hard drive space laying about to have as large a swap space as you'd like, and I can't count the number of times a large swap has given me enough to time to catch runaway processes and keep an ailing server up and running, albeit hobbling along, rather than having to worry about running out of virtual memory. Unless the memory being allocated here just has to be real memory. But I'm guessing not. Sure, swapping is slow, but at least it will let some memory hungry processes finish. The box has 8G of RAM and 10G swap space available to it (almost none of which touched). The problem was that the VACUUM FULL process never released any memory. With maintenance work mem set to 512MB, I would think that it would be enforced such that any given connection would only be allowed 512MB for maintenance. Why it it growing beyond the 1.5G I allow system-wide for any given process eludes me right now (and why I suspect a bad memory leak). As per the other suggestions, I will end up doing a pg_dump/restore to reclaim the lost space. Sven ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] VACUUM FULL versus CLUSTER ON
The box has 8G of RAM and 10G swap space available to it (almost none of which touched). The problem was that the VACUUM FULL process never released any memory. With maintenance work mem set to 512MB, I would think that it would be enforced such that any given connection would only be allowed 512MB for maintenance. Why it it growing beyond the 1.5G I allow system-wide for any given process eludes me right now (and why I suspect a bad memory leak). My understanding is that the maintenance_work_mem is the amount of RAM that can be used before PostgreSQL swaps out to disk. This is not the amount of memory that vacuum full will use. Vacuum full is going to use whatever it needs to get the job done, and on the table your dealing with, its going to be alot. As per the other suggestions, I will end up doing a pg_dump/restore to reclaim the lost space. You already know my opinion on that ;) Sincerely, Joshua D. Drake Sven -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] VACUUM FULL versus CLUSTER ON
At 10:50 AM 7/10/2006 -0500, Scott Marlowe wrote: On Sat, 2006-07-08 at 10:20, Joshua D. Drake wrote: Unfortunately it would appear that I cannot vacuum full either as I get an out of memory error: Also, this kind of points out that you might not have enough swap space. On most database servers there's enough hard drive space laying about to have as large a swap space as you'd like, and I can't count the number of times a large swap has given me enough to time to catch runaway processes and keep an ailing server up and running, albeit hobbling along, rather than having to worry about running out of virtual memory. In my opinion, there is not enough real memory, or postgresql (or something else) is using more memory than it should. Because I prefer the reverse - processes die rather than the entire server hobble along while your ssh connection attempts (or other arguably important stuff) keep timing out. Of course some O/Ses appear to randomly kill processes when out of memory. BTW, I regard any O/S that kills critical processes such as the disk syncing processes or swap, or initd in _typical_ out-of-memory scenarios as a product of shoddy workmanship. I'd prefer just enough swap[1] that when maxed out the server is just slowed enough to be noticeable, rather than effectively dead and continuously running like a drum memory computer. If a normal userland program cannot handle being killed because there is not enough memory, then I think something is wrong somewhere (e.g. the O/S is doing stuff like SIGKILLing postgresql, or postgresql is not handling SIGTERM properly). Link. [1] How much is enough? My guess is that the suitable size would be related to the random read/write throughput from/to the swap, and the largest worst case amount of memory that would have to be continuously read and written, and how long you would be willing to wait. If you have programs that allocate tons of memory but don't ever actually use the full amount, you can adjust your swap accordingly. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Aim of --enable-thread-safety ?
Christopher Browne wrote: [EMAIL PROTECTED] (DANTE Alexandra) wrote: I wonder if this compilation option is really taken into account as PostgreSQL is not multi-threading but multi-processing. I have read that without this option, the libpq won't know anything about threads and may indeed have problems, but could you explain me how this option runs ? It is not clear for me the aim of this option in an multi-processing environment... Is it possible to force PostgreSQL to be multi-threaded ? This option is all about allowing you to have multi-threaded *client* applications. That is, applications that many have multiple threads where threads can hold onto database connections. Uh, the documentation is very clear on the purpose of this option: termoption--enable-thread-safety/option/term listitem para Make the client libraries thread-safe. This allows concurrent threads in applicationlibpq/application and applicationECPG/application programs to safely control their private connection handles. This option requires adequate threading support in your operating system. Is there something unclear about it? -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] US Telephone Number Type
Hello, How would one go about creating a US telephone type in the format of (555)-555- ? I am at a loss on how it could be accomplished in the most correct way possible while not going into the various different country styles e.g. +01 (555) 555-. Is the difficulty of creating a telephone type the reason it is not in postgresql already? Should the telephone type be able to do something such as: SELECT * from tableFOO where telephone.areacode = 555; Or would regex be better? regards, ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] encoding bug or feature?
folks select ucase( 'ñ'); - 'ñ' i want this statemen return 'Ñ' any ideas? best regards mdc _ Horóscopos, Salud y belleza, Chistes, Consejos de amor: el contenido más divertido para tu celular está en Yahoo! Móvil. Obtenelo en http://movil.yahoo.com.ar ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Aim of --enable-thread-safety ?
On Mon, Jul 10, 2006 at 01:39:44PM -0400, Bruce Momjian wrote: Uh, the documentation is very clear on the purpose of this option: termoption--enable-thread-safety/option/term listitem para Make the client libraries thread-safe. This allows concurrent threads in applicationlibpq/application and applicationECPG/application programs to safely control their private connection handles. This option requires adequate threading support in your operating system. Is there something unclear about it? Not sure. The way I read it, it doesn't say that it's also important even if the multithreaded program only has one connection (it uses the word handles). Someone posted a problem not so long ago where he'd figured he didn't need thread-safety because he was only using one connection, but it still broke if libpq was called from anything other than the main thread. If there is any possibility libpq is going to come in contact with a multithreaded program (even via a loaded plugin or whatever) you must have thread-safety enabled or things are likely to break. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] encoding bug or feature?
marcelo Cortez [EMAIL PROTECTED] writes: folks select ucase( 'ñ'); - 'ñ' i want this statemen return 'Ñ' any ideas? test=# select upper( 'ñ'); upper --- Ñ (1 record) test=# Maybe your locale settings are wrong... -- Jorge Godoy [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] US Telephone Number Type
On Mon, Jul 10, 2006 at 10:33:52AM -0700, Karen Hill wrote: Hello, How would one go about creating a US telephone type in the format of (555)-555- ? I am at a loss on how it could be accomplished in the most correct way possible while not going into the various different country styles e.g. +01 (555) 555-. How do you mean styles. The above number is, when printed in standard international format +155. The number needed to dial international is not relevent. What makes it tricky is that people don't agree on how numbers should be formatted. Is the difficulty of creating a telephone type the reason it is not in postgresql already? It wouldn't be hard, it's just not clear what the advantage is over just having a string and some functions to display the number. Should the telephone type be able to do something such as: SELECT * from tableFOO where telephone.areacode = 555; Maybe, but is that useful? Maybe America is different, but my experience in NL and AU is that you rarely care about the areacode anyway, so why would you want to pull it out? Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] US Telephone Number Type
On Jul 10, 2006, at 1:33 PM, Karen Hill wrote: Is the difficulty of creating a telephone type the reason it is not in postgresql already? Should the telephone type be able to do something such as: SELECT * from tableFOO where telephone.areacode = 555; Or would regex be better? makes more sense to store them in a a canonical format and then find things with pattern matches. smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] US Telephone Number Type
Karen Hill [EMAIL PROTECTED] writes: Hello, How would one go about creating a US telephone type in the format of (555)-555- ? I am at a loss on how it could be accomplished in the most correct way possible while not going into the various different country styles e.g. +01 (555) 555-. Is the difficulty of creating a telephone type the reason it is not in postgresql already? The above mask wouldn't be correct for Brazilian phone numbers, for example. Our prefix has four digits here, and our area code has only two digits, so we'd need something like +55 (55) -. So, I believe that there's no phone type because type differs from country to country. IIRC, in Germany there's a lot more difference from old numbers to new ones, making it annoying to even define something for localizing phone numbers for them. Should the telephone type be able to do something such as: SELECT * from tableFOO where telephone.areacode = 555; Or would regex be better? It depends on how far into normalization you're willing to go and what kind of information you're willing to retrieve. Here we can guarantee that the same prefix grants that the numbers are phisically near one to the other, so it might be interesting to map it to make some geographic assumption on data (it is not accurate since one switch can have several prefixes, but it gives a rough idea anyway). In one project we did model our phone table as: - country code - inside the country table - area code - city table - prefix - number But in a latter project I denormalized this and went with: - country code - country table - area code- city table - prefix + number We don't want to manipulate individual phone numbers -- they are a property of a person's data and we manipulate it like that. Also, think about storing numbers not the formatted output. This will make it easier to work with and if you need to change something it looks easier. Writing a function or view to retrieve the information the way you need it is also an option. Be seeing you, -- Jorge Godoy [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] unsubscribe
-- Harshal Shah
Re: [GENERAL] US Telephone Number Type
Is the difficulty of creating a telephone type the reason it is not in postgresql already? Should the telephone type be able to do something such as: SELECT * from tableFOO where telephone.areacode = 555; Or would regex be better? makes more sense to store them in a a canonical format and then find things with pattern matches. Also, due to the problem of keeping area codes segregated in large growing population centers, there is strong talk about allowing overlapping area codes. Dialing locally will require 11 digits instead of the usual 7. I know that this is already the case in the state of Georgia and there is talk about adopting it in California. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] US Telephone Number Type
On Jul 10, 2006, at 3:46 PM, Richard Broersma Jr wrote: Also, due to the problem of keeping area codes segregated in large growing population centers, there is strong talk about allowing overlapping area codes. Dialing locally will require 11 digits instead of the usual 7. around here every local call is 10 digits due to ovelapping area codes (been this way for over 10 years now.) why would you need to dial 1 first for a local call? and how would this make a phone number format different if stored in a canonical form already? =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D.Khera Communications, Inc. Internet: khera@kciLink.com Rockville, MD +1-301-869-4449 x806 smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] US Telephone Number Type
Karen Hill [EMAIL PROTECTED] writes: How would one go about creating a US telephone type in the format of (555)-555- ? Are you sure that's what you want? Even within the US there's the issue of extension numbers; I'm not sure how useful it is to have a datatype that refuses anything but the basic 10-digit format. It doesn't seem particularly hard to make a type that stores just the digits (applying whatever amount of error-checking seems appropriate on the non-digit stuff it's throwing away) and on output regurgitates a standardized format. Minimum support would just be an input function and an output function, and it doesn't seem like you need too many other functions besides them ... do you need indexing support? Should the telephone type be able to do something such as: SELECT * from tableFOO where telephone.areacode = 555; It'd be syntactically easier as a function: areacode(telephone) = 555 regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
timestamp with definable accuracy, was: Re: [GENERAL] empty text fields
On Thu, Jun 29, 2006 at 12:02:40PM +0200, Alban Hertroys wrote: This kind of reeks like a begin/end date and an accuracy quantifier, though that wouldn't account for option 6. Your cases 0 to 5 and 7 would be transformed into something like: ... Where I defined '5' as being accurate, and lower values less accurate. You may want to use values with a wider spread, it'll allow more fuzziness about how sure you are about a certain date. Just for your information: In our Python implementation of a fuzzy timestamp type we used accuracy values ranging from 1 to 7 denoting the precision of a complete timestamp definition: 7 - full subsecond accuracy (7 digits precision) 6 - seconds 5 - minutes 4 - hours 3 - days 2 - months 1 - years Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] US Telephone Number Type
On Jul 10, 2006, at 11:07 AM, Martijn van Oosterhout wrote: On Mon, Jul 10, 2006 at 10:33:52AM -0700, Karen Hill wrote: Hello, How would one go about creating a US telephone type in the format of (555)-555- ? I am at a loss on how it could be accomplished in the most correct way possible while not going into the various different country styles e.g. +01 (555) 555-. How do you mean styles. The above number is, when printed in standard international format +155. The number needed to dial international is not relevent. What makes it tricky is that people don't agree on how numbers should be formatted. Is the difficulty of creating a telephone type the reason it is not in postgresql already? It wouldn't be hard, it's just not clear what the advantage is over just having a string and some functions to display the number. Should the telephone type be able to do something such as: SELECT * from tableFOO where telephone.areacode = 555; Maybe, but is that useful? Maybe America is different, but my experience in NL and AU is that you rarely care about the areacode anyway, so why would you want to pull it out? Strong correlation to geographical area - very useful for sales campaigns or geolocation. Also, free numbers (aka 800 numbers in the US) have distinctive area codes. Of course, identifying the area code is easy in the US, but much harder (or even meaningless) elsewhere. In other bits of the world area codes allow you to identify mobile numbers. A general phone number type would have a country, an area code, a local number and an optional extension. Possibly a type (tel, fax, modem) too, possibly not. Possibly an optional alternate format, so that you can store 1-800-MY-APPLE, but also be able to treat it as +18006927753. And probably all the other weirdnesses in RFC 2806 too. Outputs might be E.164, RFC 2806 URL or (country-specific) human-readable. Doing it right would be very complex, and overkill for most applications. Doing a simplistic version that only supported something like E.164 or only supported US formating would be easy - but so application space specific, why bother? Just use a text field or three. Cheers, Steve ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] encoding bug or feature?
Jorge the postgreslq.conf say lc_messages = 'es_AR' # locale for system error message # strings lc_monetary = 'es_AR' # locale for monetary formatting lc_numeric = 'es_AR'# locale for number formatting lc_time = 'es_AR' # locale for time formatting but the upper function fail for me. what is your settings? BTW the database is SQL_ASCII any pointer be appreciated best regards MDC --- Jorge Godoy [EMAIL PROTECTED] escribió: marcelo Cortez [EMAIL PROTECTED] writes: folks select ucase( 'ñ'); - 'ñ' i want this statemen return 'Ñ' any ideas? test=# select upper( 'ñ'); upper --- Ñ (1 record) test=# Maybe your locale settings are wrong... -- Jorge Godoy [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ___ 1GB gratis, Antivirus y Antispam Correo Yahoo!, el mejor correo web del mundo http://correo.yahoo.com.ar ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Procedural language functions across servers
Michael Fuhr wrote: dbi-link is an alternative to dblink that uses Perl/DBI: http://pgfoundry.org/projects/dbi-link/ is this the only way available if additional procedural languages are installed? With the untrusted version of a language you can do essentially anything that language supports. For example, with plperlu, you could use DBI to open a connection to another database (even another DBMS like Oracle, MySQL, etc.), issue a query, fetch the results, and do whatever you want with those results. Example: CREATE FUNCTION remote_version(text, text, text) RETURNS text AS $$ use DBI; As a related question, assume I have PostgreSQL on an application server X with functions a() and b() defined to use some method (dblink or whatever) to return a result set from a remote backend server. If X runs a complex query including references to a() and b() which themselves initiate complex queries on backend servers A and B, can I tell X's planner to run a(A) and b(B) simultaneously, or is the only way to have these in distinct sessions storing their results in tables on X? -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Getting Primary Key Value After Insert
As a alternative way, you may forget about sequence name and create rule for you table - simple rule that will make 'SELECT idColumnName' on every INSERT action. After trying several approaches I've chosen this way in my projects. It's better way if you have some framework (R2O layer or smth). On 7/9/06, Adam [EMAIL PROTECTED] wrote: I'm inserting data into two tables, the second table has a forigen key that points to the primary key of the first table. After I insert a row into the first table, I need to take the primary key value created in SERIAL column and store it so I can insert it as the forigen key value on the second table. What is the best way to get the value of the primary key ( SERIAL data type ) of the row I inserted? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- Best regards, Nikolay ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] US Telephone Number Type
In California, we definitely care about the area code, as there are several area codes (at least 4) in San Diego County. I have to use 1+area code to dial home from work, and vice-versa. Susan Martijn van Oosterhout [EMAIL PROTECTED] To gKaren Hill [EMAIL PROTECTED] Sent by: cc pgsql-general-own pgsql-general@postgresql.org [EMAIL PROTECTED] Subject Re: [GENERAL] US Telephone Number Type 07/10/2006 11:07 AM |---| | [ ] Expand Groups | Please respond to|---| Martijn van Oosterhout [EMAIL PROTECTED] g On Mon, Jul 10, 2006 at 10:33:52AM -0700, Karen Hill wrote: Hello, - snip --- Maybe, but is that useful? Maybe America is different, but my experience in NL and AU is that you rarely care about the areacode anyway, so why would you want to pull it out? Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. (See attached file: signature.asc) -- Simply protected storage solutions ensure that your information is automatically safe, readily available and always there, visit us at http://www.overlandstorage.com -- signature.asc Description: Binary data ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] US Telephone Number Type
Steve Atkins wrote: ... Should the telephone type be able to do something such as: SELECT * from tableFOO where telephone.areacode = 555; Maybe, but is that useful? Maybe America is different, but my experience in NL and AU is that you rarely care about the areacode anyway, so why would you want to pull it out? Strong correlation to geographical area - very useful for sales campaigns or geolocation. Also, free numbers (aka 800 numbers in the US) have distinctive area codes. Of course, identifying the area code is easy in the US, but much harder (or even meaningless) elsewhere. In other bits of the world area codes allow you to identify mobile numbers. It's actually quite useful to separate out both the NPA (area-code) and NXX (prefix) in US numbers. We subscribe to data that lets us determine lots of things for a given NPA/NXX (MSA, PMSA, lat/lon, ratecenter, zip-codes covered, time-zone, observes daylight-saving?, wireless/wireline, etc.) Of course with number portability you can't rely on just the NPA and NXX to determine whether the number is wireless but you can subscribe to other data that lists all the numbers that have been ported from wireless to wireline or vice-versa to fix that issue. Cheers, Steve ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] encoding bug or feature?
marcelo Cortez [EMAIL PROTECTED] writes: Jorge the postgreslq.conf say lc_messages = 'es_AR' # locale for system error message # strings lc_monetary = 'es_AR' # locale for monetary formatting lc_numeric = 'es_AR'# locale for number formatting lc_time = 'es_AR' # locale for time formatting but the upper function fail for me. what is your settings? BTW the database is SQL_ASCII any pointer be appreciated best regards I believe you didn't get my reply to your personal message... Here's its body again: My settings are all pt_BR.UTF-8. # These settings are initialized by initdb -- they might be changed lc_messages = 'pt_BR.UTF-8' # locale for system error message # strings lc_monetary = 'pt_BR.UTF-8' # locale for monetary formatting lc_numeric = 'pt_BR.UTF-8' # locale for number formatting lc_time = 'pt_BR.UTF-8' # locale for time formatting My database is also in UTF-8. test=# \set VERSION = 'PostgreSQL 8.1.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.0 (SUSE Linux)' AUTOCOMMIT = 'on' VERBOSITY = 'default' PROMPT1 = '%/%R%# ' PROMPT2 = '%/%R%# ' PROMPT3 = ' ' DBNAME = 'test' USER = 'godoy' PORT = '5432' ENCODING = 'UTF8' HISTSIZE = '500' test=# Try creating a database with an encoding other than SQLASCII and see if it works as you expect. -- Jorge Godoy [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Notes on converting from MySQL 5.0.x to PostgreSQL
On 6/30/2006 11:12 AM, Scott Marlowe wrote: I agree with Tom, nice notes. I noted a few minor issues that seem to derive from a familiarity with MySQL. I'll put my corrections below... On Fri, 2006-06-30 at 08:17, Jason McManus wrote: On Converting from MySQL 5.0.x to PostgreSQL 8.1.x -- Major differences I have noted: --- MySQL 5.0.x: * Easy, built-in and extensive replication support. Not sure how extensive it is. It's basically synchronous single master single slave, right? It is quite easy though. Last thing I heard was that MySQL still had only statement based replication and that it doesn't work together with some of the new enterprise features like triggers and stored procedures. Row level replication is on their TODO list and this major feature will probably appear in some minor 5.2.x release. Jan PostgreSQL 8.1.x: * Embedded procedures in multiple native languages (stored procedures and functions can be written in native Perl, Python, Tcl, SQL, PL/PgSQL) Note that there are a dozen or more other languages as well. Just FYI. Off the top of my head, plPHP, plJ (java there's two different java implementations, I think) and plR (R is the open source equivalent of the S statistics language) * Replication support still rudimentary. H. I think that's an overly simplistic evaluation. The slony replication engine is actually VERY advanced, but the administrative tools consist mostly of your brain. hehe. That said, once you've learned how to drive it, it's quite amazing. Keep in mind, slony can be applied to a living database while it's running, and can run between different major versions of postgresql. That's a pretty advanced feature. Plus, if the replication daemons die (kill -9ed or whatever) you can restart replication and slony will come right back where it was and catch up. Pointers, tips, quick facts and gotchas for other people converting: * MySQL combines the concepts of 'database' and 'schema' into one. PostgreSQL differentiates the two. While the hierarchy in MySQL is database.table.field, PostgreSQL is roughly: database.schema.table.field. A schema is a 'logically grouped set of tables but still kept within a particular database.' This could allow separate applications to be built that still rely upon the same database, but can be kept somewhat logically separated. The default schema in each database is called 'public', and is the one referred to if no others are specified. This can be modified with 'SET search_path TO ...'. This is a VERY good analysis of the difference between the two databases. * Pg uses a 'template1' pseudo-database that can be tailored to provide default objects for new database creation, if you should desire. It obviously also offers a 'template0' database that is read-only and offers a barebones database, more equivalent to the empty db created with mysql's CREATE DATABASE statement. This isn't quite right. template0 is a locked and pure copy of the template database. It's there for break glass in case of emergency use. :) template1, when you first initdb, is exactly the same as template0, but you can connect to it, and alter it. Both of these are real postgresql databases. template1 is the database that gets copied by default when you do create database. Note that you can also define a different template database when running create database, which lets you easily clone any database on your machine. create database newdb with template olddb * Pg uses the 'serial' column type instead of AUTO_INCREMENT. This allows more than one independent sequence to be specified per table (though the utility of this may be of dubious value). These are closer to Oracle's concept of sequence generators, and they can be manipulated with the currval(), nextval(), setval(), and lastval() functions. Don't forget 64bit bigserials too. * Pg requires its tables and databases be 'vacuumed' regularly to remove completed transaction snapshots and optimize the tables on disk. It is necessary because the way that PostgreSQL implements true MVCC is by writing all temporary transactions to disk and setting a visibility flag for the record. Vacuuming can be performed automatically, and in a deferred manner by using vacuum_cost settings to limit it to low-load periods or based upon numerous other criteria. See the manual for more information. Interestingly enough, MySQL's innodb tables do almost the exact same thing, but their vacuum process is wholly automated. Generally, this means fewer issues pop up for the new dba, but when they do, they can be a little harder to deal with. It's about a wash. Of course, as you mentioned earlier, most mysql folks aren't using innodb. * While MySQL supports transactions with the InnoDB
Re: [GENERAL] US Telephone Number Type
Tom Lane wrote: It doesn't seem particularly hard to make a type that stores just the digits (applying whatever amount of error-checking seems appropriate on the non-digit stuff it's throwing away) and on output regurgitates a standardized format. Minimum support would just be an input function and an output function, and it doesn't seem like you need too many other functions besides them I did a quick google and someone mentioned that input and output functions need to be written in C. Is that still the case? Anyway, there could be multiple number types to choose from such as: telephone-us-basic : (555) 555- telephone-us-extention : (555) 555- ext 1234 Other locals (EU, etc) could create their own to their local specifications. This would seem like a nice contrib package. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] US Telephone Number Type
kleptog@svana.org (Martijn van Oosterhout) writes: What makes it tricky is that people don't agree on how numbers should be formatted. There is a relevant standard, E.164b, where US/Canadian telnos are formatted like: +1.4166734124 It should be quite clear how *any* phone number in those countries would be formatted, given that example... Is the difficulty of creating a telephone type the reason it is not in postgresql already? It wouldn't be hard, it's just not clear what the advantage is over just having a string and some functions to display the number. Unfortunately, the above represents something of a lowest common denominator, which, for those that are exchange/area code-happy, is woefully insufficient. Mind you, I'd argue that attempts to use more data are quite likely to be doomed to failure... Should the telephone type be able to do something such as: SELECT * from tableFOO where telephone.areacode = 555; Maybe, but is that useful? Maybe America is different, but my experience in NL and AU is that you rarely care about the areacode anyway, so why would you want to pull it out? At one time, it was a pretty meaningful determinant of location. But it is growing increasingly useless, as it is increasingly common for there to be numerous somewhat-overlapping area codes for any given metropolitan region. The Toronto region (in Canada, albeit, but under much the same rules) includes area codes 416, 905, and 647. The Dallas/Fort Worth region includes area codes 214, 972, 817, 469, and 682. NYC includes area codes 212, 347, 516, 631, 646, 718, 917. Attempts to evaluate terribly much based on area codes are increasingly likely to fail... -- select 'cbbrowne' || '@' || 'acm.org'; http://cbbrowne.com/info/unix.html Don't be so open-minded that your brains fall out. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] US Telephone Number Type
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Chris Browne wrote: kleptog@svana.org (Martijn van Oosterhout) writes: [snip] Attempts to evaluate terribly much based on area codes are increasingly likely to fail... Especially with VoIP and number portability. - -- Ron Johnson, Jr. Jefferson LA USA Is common sense really valid? For example, it is common sense to white-power racists that whites are superior to blacks, and that those with brown skins are mud people. However, that common sense is obviously wrong. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.3 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFEsvSkS9HxQb37XmcRAqcnAJ4kFcr2zAzxpzlAxQVLcZiU8f6niQCg31R4 dwujmmYgvC9WM4guS1VrABU= =+u53 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] US Telephone Number Type
[EMAIL PROTECTED] (Richard Broersma Jr) writes: Is the difficulty of creating a telephone type the reason it is not in postgresql already? Should the telephone type be able to do something such as: SELECT * from tableFOO where telephone.areacode = 555; Or would regex be better? makes more sense to store them in a a canonical format and then find things with pattern matches. Also, due to the problem of keeping area codes segregated in large growing population centers, there is strong talk about allowing overlapping area codes. Dialing locally will require 11 digits instead of the usual 7. Ten digit dialing (where the country code is deemed implicit) has been de rigeur in most of the highly populated metropolitan regions that have 3 or more area codes for many years now. I know that this is already the case in the state of Georgia and there is talk about adopting it in California. It has been true for a decade or more in Toronto and north Texas... -- (format nil [EMAIL PROTECTED] cbbrowne ntlug.org) http://www.ntlug.org/~cbbrowne/ Signs of a Klingon Programmer - 13. Our users will know fear and cower before our software! Ship it! Ship it and let them flee like the dogs they are! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] US Telephone Number Type
[EMAIL PROTECTED] writes: In California, we definitely care about the area code, as there are several area codes (at least 4) in San Diego County. I have to use 1+area code to dial home from work, and vice-versa. In what way do you care about them? The area code is NOT an accurate way of determining locality, as there are frequently cases where depending on where you are, different sets of numbers roll in and out of one's local calling area. You can't necessarily determine, based on area code, whether another number will be deemed local or not. And that tendancy is getting worse, over time. Fortunately LD rates have been tending to fall... -- output = reverse(gro.mca @ enworbbc) http://www3.sympatico.ca/cbbrowne/languages.html Microsoft builds product loyalty on the part of network administrators and consultants, [these are] the only people who really count in the Microsoft scheme of things. Users are an expendable commodity. -- Mitch Stone 1997 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Notes on converting from MySQL 5.0.x to PostgreSQL
http://dev.mysql.com/doc/refman/5.1/en/replication-row-based.html5.1AlexOn 7/10/06, Jan Wieck [EMAIL PROTECTED] wrote: On 6/30/2006 11:12 AM, Scott Marlowe wrote: I agree with Tom, nice notes.I noted a few minor issues that seem to derive from a familiarity with MySQL.I'll put my corrections below... On Fri, 2006-06-30 at 08:17, Jason McManus wrote: On Converting from MySQL 5.0.x to PostgreSQL 8.1.x -- Major differences I have noted: --- MySQL 5.0.x: * Easy, built-in and extensive replication support. Not sure how extensive it is.It's basically synchronous single master single slave, right?It is quite easy though. Last thing I heard was that MySQL still had only statement basedreplication and that it doesn't work together with some of the newenterprise features like triggers and stored procedures. Row levelreplication is on their TODO list and this major feature will probably appear in some minor 5.2.x release.Jan PostgreSQL 8.1.x: * Embedded procedures in multiple native languages (stored procedures and functions can be written in native Perl, Python, Tcl, SQL, PL/PgSQL) Note that there are a dozen or more other languages as well.Just FYI. Off the top of my head, plPHP, plJ (java there's two different java implementations, I think) and plR (R is the open source equivalent of the S statistics language) * Replication support still rudimentary. H.I think that's an overly simplistic evaluation.The slony replication engine is actually VERY advanced, but the administrative tools consist mostly of your brain.hehe.That said, once you've learned how to drive it, it's quite amazing.Keep in mind, slony can be applied to a living database while it's running, and can run between different major versions of postgresql.That's a pretty advanced feature.Plus, if the replication daemons die (kill -9ed or whatever) you can restart replication and slony will come right back where it was and catch up. Pointers, tips, quick facts and gotchas for other people converting: * MySQL combines the concepts of 'database' and 'schema' into one.PostgreSQL differentiates the two.While the hierarchy in MySQL is database.table.field, PostgreSQL is roughly: database.schema.table.field. A schema is a 'logically grouped set of tables but still kept within a particular database.'This could allow separate applications to be built that still rely upon the same database, but can be kept somewhat logically separated.The default schema in each database is called 'public', and is the one referred to if no others are specified.This can be modified with 'SET search_path TO ...'. This is a VERY good analysis of the difference between the two databases. * Pg uses a 'template1' pseudo-database that can be tailored to provide default objects for new database creation, if you should desire.It obviously also offers a 'template0' database that is read-only and offers a barebones database, more equivalent to the empty db created with mysql's CREATE DATABASE statement. This isn't quite right. template0 is a locked and pure copy of the template database.It's there for break glass in case of emergency use. :) template1, when you first initdb, is exactly the same as template0, but you can connect to it, and alter it.Both of these are real postgresql databases.template1 is the database that gets copied by default when you do create database.Note that you can also define a different template database when running create database, which lets you easily clone any database on your machine.create database newdb with template olddb * Pg uses the 'serial' column type instead of AUTO_INCREMENT.This allows more than one independent sequence to be specified per table (though the utility of this may be of dubious value).These are closer to Oracle's concept of sequence generators, and they can be manipulated with the currval(), nextval(), setval(), and lastval() functions. Don't forget 64bit bigserials too. * Pg requires its tables and databases be 'vacuumed' regularly to remove completed transaction snapshots and optimize the tables on disk.It is necessary because the way that PostgreSQL implements true MVCC is by writing all temporary transactions to disk and setting a visibility flag for the record.Vacuuming can be performed automatically, and in a deferred manner by using vacuum_cost settings to limit it to low-load periods or based upon numerous other criteria.See the manual for more information. Interestingly enough, MySQL's innodb tables do almost the exact same thing, but their vacuum process is wholly automated.Generally, this means fewer issues pop up for the new dba, but when they do, they can be a little harder to deal with.It's about a wash.Of course, as you mentioned earlier, most mysql folks aren't using innodb. * While MySQL supports transactions with the InnoDB databases, many MySQL users
[GENERAL] A function which returns all rolname from pg_roles.
How do I make this function work? I am trying to get all the rolnames from pg_roles. CREATE OR REPLACE FUNCTION test() SETOF name AS $$ DECLARE rrol name; BEGIN SELECT rolname INTO rrol FROM pg_roles; RETURN setof rrol; END; $$ LANGUAGE plpgsql; regards, ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] US Telephone Number Type
Karen Hill [EMAIL PROTECTED] writes: I did a quick google and someone mentioned that input and output functions need to be written in C. Is that still the case? Yeah, pretty much. The main problem is that such functions need to deal with whatever physical on-disk format you've chosen for the datatype, and most of our PLs don't offer bit-level memory access. If C functions seem like more work than you want to go to for this problem, I concur with using a domain over text with a regex check constraint ... regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] A function which returns all rolname from pg_roles.
Karen Hill wrote: How do I make this function work? I am trying to get all the rolnames from pg_roles. Why not just do SELECT rolname FROM pg_roles;? Anyway, in PL/pgSQL: CREATE OR REPLACE FUNCTION test() RETURNS SETOF name AS $$ DECLARE rec record; BEGIN FOR rec IN SELECT rolname FROM pg_roles LOOP RETURN NEXT rec.rolname; END LOOP; END; $$ LANGUAGE plpgsql; And simpler as an SQL function (although, again, why bother?): CREATE OR REPLACE FUNCTION test_sql() RETURNS SETOF name AS $$ SELECT rolname FROM pg_roles $$ LANGUAGE sql; HTH, Joe ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] A function which returns all rolname from pg_roles.
am 10.07.2006, um 19:27:38 -0700 mailte Karen Hill folgendes: How do I make this function work? I am trying to get all the rolnames from pg_roles. CREATE OR REPLACE FUNCTION test() SETOF name AS $$ DECLARE rrol name; BEGIN SELECT rolname INTO rrol FROM pg_roles; RETURN setof rrol; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION get_all_roles() returns setof text as $$ declare rrolrecord; begin for rrol in select rolname from pg_roles loop return next rrol.rolname; end loop; end; $$ language plpgsql; select * from get_all_roles(); HTH, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47215, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] US Telephone Number Type
Tom Lane wrote: Karen Hill [EMAIL PROTECTED] writes: I did a quick google and someone mentioned that input and output functions need to be written in C. Is that still the case? Yeah, pretty much. The main problem is that such functions need to deal with whatever physical on-disk format you've chosen for the datatype, and most of our PLs don't offer bit-level memory access. Is bit-level memory access the actual problem? I would think that at least PL/Perl can offer that pretty easily using pack() or something like that. Not that I've actually tried using it. But I think the main problem may be getting our calling conventions right. I mean, how would you do a PG_GETARG_BOOL() or stuff like that? Maybe if we offered PG_GETARG_DATUM and PG_RETURN_DATUM equivalents in PL/Perl we could offer I/O functions there. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
