Re: [GENERAL] Verison 8.3 PL/pgSQL debugger Question
On Sat, 2007-11-10 at 15:34 -0600, Tony Caduto wrote: Is there any documentation for developers on how to use the new debugger in 8.3? Specifically on how it works and general guidelines on integration into 3rd party GUI applications. The debugger is a plug-in, available from pgfoundry. Postgres just provides the hooks to allow it to work, nothing else. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(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] Regression in 8.3?
On Mon, Nov 12, 2007 at 12:46:58AM +0100, Patric Bechtel wrote: select * from bla where a like '8%' Patrick, I'm not sure what you expect to come back: 8 ? 0.08 ? 8% of each of bla.a's values ? do 7% or 9% count, too ? (a like '8%' seems to say well, about 8% of a, or close, anyways). IOW, it is not entirely clear and thus the server forces us to make a choice and not second-guess our intentions. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Regression in 8.3?
Hi, On Montag, 12. November 2007, Karsten Hilbert wrote: | On Mon, Nov 12, 2007 at 12:46:58AM +0100, Patric Bechtel wrote: | select * from bla where a like '8%' | | Patrick, I'm not sure what you expect to come back: | | 8 ? 0.08 ? | | 8% of each of bla.a's values ? | | do 7% or 9% count, too ? (a like '8%' seems to say well, | about 8% of a, or close, anyways). | | IOW, it is not entirely clear and thus the server forces us | to make a choice and not second-guess our intentions. The %-sign in conjunction with a like operator in a string doesn't leave much room for interpretation IMO, regardless if the a column is of type bigint or not. Ciao, Thomas -- Thomas Pundt [EMAIL PROTECTED] http://rp-online.de/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Verison 8.3 PL/pgSQL debugger Question
Tony, On Nov 10, 2007, at 4:34 PM, Tony Caduto wrote: Is there any documentation for developers on how to use the new debugger in 8.3? Specifically on how it works and general guidelines on integration into 3rd party GUI applications. The API documentation is in the source file pldbgapi.c. Korry posted a more detailed example here (which was to be added to the readme file): http://archives.postgresql.org/pgsql-hackers/2007-09/msg00241.php John DeSoi, Ph.D. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Regression in 8.3?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Karsten Hilbert schrieb am 12.11.2007 13:07: On Mon, Nov 12, 2007 at 12:46:58AM +0100, Patric Bechtel wrote: select * from bla where a like '8%' Patrick, I'm not sure what you expect to come back: 8 ? 0.08 ? 8% of each of bla.a's values ? do 7% or 9% count, too ? (a like '8%' seems to say well, about 8% of a, or close, anyways). IOW, it is not entirely clear and thus the server forces us to make a choice and not second-guess our intentions. Karsten Just guess: We have bigint id's through the system, so I want to give the users the convenience of typing only the last 4-5 digits (which most of the time is enough). So the query we issue really is ... like %$userinput Makes sense, or? Patric -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: GnuPT 2.5.2 iD8DBQFHOFLZfGgGu8y7ypARAn0sAJ968gHCSICQtHH6ZkNHCXbvR2fTvACg6pU4 Bf9HiTiyB5UEvLbun4kGARs= =YbyY -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Regression in 8.3?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Thomas Pundt schrieb am 12.11.2007 13:18: Hi, On Montag, 12. November 2007, Karsten Hilbert wrote: | On Mon, Nov 12, 2007 at 12:46:58AM +0100, Patric Bechtel wrote: | select * from bla where a like '8%' | | Patrick, I'm not sure what you expect to come back: | | 8 ? 0.08 ? | | 8% of each of bla.a's values ? | | do 7% or 9% count, too ? (a like '8%' seems to say well, | about 8% of a, or close, anyways). | | IOW, it is not entirely clear and thus the server forces us | to make a choice and not second-guess our intentions. The %-sign in conjunction with a like operator in a string doesn't leave much room for interpretation IMO, regardless if the a column is of type bigint or not. Ciao, Thomas Second that. A like operator is for Strings. So I was, well *cough*, surprised as it didn't. I mean it's unambiguous as it can be. Patric -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: GnuPT 2.5.2 iD8DBQFHOFNWfGgGu8y7ypARAlrdAKCUyLWH2IfIO5ckGItL8NjHBup32wCcDQmy Rqn5BtzSClTziJbjhubCm5Y= =felz -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] Regression in 8.3?
On Mon, Nov 12, 2007 at 02:19:21PM +0100, Patric Bechtel wrote: Just guess: We have bigint id's through the system, so I want to give the users the convenience of typing only the last 4-5 digits (which most of the time is enough). So the query we issue really is ... like %$userinput Makes sense, or? where id % 1000 = $userinput; (for 4-digit-long-userinput) will do the same, and at the very least will not bail out on 8.3 on the other hand. while i know and understand why there can't be = operator for text and int, i think that like could be readded as it is really clear about how it works. depesz -- quicksil1er: postgres is excellent, but like any DB it requires a highly paid DBA. here's my CV! :) http://www.depesz.com/ - blog dla ciebie (i moje CV) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Usúario Conectados
Oi Rapazeada, Existe alguma função do PostgreSQL para eu listar os usúarios que estão conectados ao banco, minha aplicação autentica com os usúarios diretamente do banco. Cris ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Duplicating a table row while honouring key constraints
* Gordon [EMAIL PROTECTED] wrote:
I'm developing a web application in PHP and Postgres that will
basically serve as a CMS. I want to implement a feature to allow
users to make copies of documents or folders, so this will require the
appropriate rows to be duplicated.
If possible I'd like to do this with SQL queries and avoid SELECTing
the row, munging it in PHP and INSERTING it back. I suspect that this
is probably the way I'll have to go, but if it could be done entirely
in SQL that would be nice.
At first I thought INSERT INTO table_name SELECT * from table_name
where primary_key = unique_value would do it, but that would obviously
violate the primary key uniqueness constraint. I'm wondering if
there's a way to do this where I only grab the data to be copied and
let the database work out the new primary key itself.
If your primary key is a column named 'id' of type 'serial', you can
copy a record like this:
INSERT INTO my_table
SELECT nextval('table_name_id_seq'), foo, bar, baz, ...
FROM my_table
WHERE id = id of original record to be copied
--
Lars Haugseth
If anyone disagrees with anything I say, I am quite prepared not only to
retract it, but also to deny under oath that I ever said it. -Tom Lehrer
---(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] Regression in 8.3?
On Mon, Nov 12, 2007 at 02:19:21PM +0100, Patric Bechtel wrote: Just guess: We have bigint id's through the system, so I want to give the users the convenience of typing only the last 4-5 digits (which most of the time is enough). So the query we issue really is ... like %$userinput Makes sense, or? It surely makes sense - in your environment - but it's not the only interpretation so PG tries to be impartial and makes both of us say clearly what we want. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Regression in 8.3?
On Mon, Nov 12, 2007 at 02:46:50PM +0100, Karsten Hilbert wrote: On Mon, Nov 12, 2007 at 02:19:21PM +0100, Patric Bechtel wrote: Just guess: We have bigint id's through the system, so I want to give the users the convenience of typing only the last 4-5 digits (which most of the time is enough). So the query we issue really is ... like %$userinput Makes sense, or? It surely makes sense - in your environment - but it's not the only interpretation so PG tries to be impartial and makes both of us say clearly what we want. If people want it they can add the automatic cast back in, it just isn't dfault anymore. Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Those who make peaceful revolution impossible will make violent revolution inevitable. -- John F Kennedy signature.asc Description: Digital signature
Re: [GENERAL] Usúario Conectados
Hi, you could use select usename from pg_stat_activity; PS. this is an english-speaking list, so for the future please write your mails in english. 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 On Mon, Nov 12, 2007 at 2:42 PM, in message [EMAIL PROTECTED], Cristiano Panvel [EMAIL PROTECTED] wrote: Oi Rapazeada, Existe alguma função do PostgreSQL para eu listar os usúarios que estão conectados ao banco, minha aplicação autentica com os usúarios diretamente do banco. Cris --- (end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ ---(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] Accessing a db with pgAdmin
Background - I am having a problem accessing a remote postgreSQL db from my laptop using an ssh tunnel and pgAdmin. I had postgreSQL loaded on my computer for use as a test db, but removed it. Including deleting the postgeSQL, and pgadmin directories after using the windows applications removal tool. When I reinstall pgAdmin it somehow remembered the previous entries I had for pgadmin? From the registry? I have a previously setup computer with ssh and pgAdmin that can access this remote db just fine. For some reason I cannot get my laptop to access the remote db. I have tried this after shutting down the windows firewall, and norton antivirus (including the worm protection). The pgAdmin tool on my laptop can access a local postgreSQL db just fine. Both my laptop and the computer which can access the remote db are on the same lan, and are passing through the same network firewall. I am not sure what else to try? Any assistance would be greatly appreciated! The error message I get is could not connect to server: Connection refused (0x274D/10061) Is the server running on host 127.0.0.1 and accepting TCP/IP connections on port 5432? ---(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] strange infinite loop in plpgsql
Isn't EXTRACT(dow FROM day) IN (0,6) always true thus making select return a row every time? On 11/10/07, rihad [EMAIL PROTECTED] wrote: I've been reading the online docs, but... code like this somehow ends up in an indefinite loop: CREATE OR REPLACE FUNCTION foo() RETURNS int AS $$ DECLARE timeout int; day date; BEGIN day := current_date + 1; LOOP SELECT date+1 INTO day FROM days WHERE date=day OR EXTRACT(dow FROM day) IN (0,6); EXIT WHEN NOT FOUND; timeout := timeout + 86400; END LOOP; END; $$ LANGUAGE plpgsql; It's Saturday at our place, and the days table has only one record for tomorrow's date. I hope it's been a very very long day for me :-) Thank you for your help. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] INSERT performance deteriorates quickly during a large import
El jue, 08-11-2007 a las 13:01 -0500, Tom Lane escribió: Krasimir Hristozov \(InterMedia Ltd\) [EMAIL PROTECTED] writes: We need to import data from a relatively large MySQL database into an existing PostgreSQL database, using a PHP5 script that SELECTs from MySQL and INSERTs in PostgreSQL. A part of the import involves moving about 1,300,000 records from one MySQL table to one of our PostgreSQL tables. The problem is that the insert performance inevitably deteriorates as the number of inserts increases. Are you *certain* you've gotten rid of all the indexes and foreign keys? A simple insert ought to be pretty much constant-time in Postgres, so it seems to me that you've missed something. It also seems possible that you are wrong to disregard PHP as a possible source of the problem. Have you tried watching the PHP and PG backend processes with top (or similar tool) to see who's consuming CPU time and/or memory space? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings Hi for all, Why better try use PERL DBI, may be better than. -- Regards, Julio Cesar Sánchez González. -- Ahora me he convertido en la muerte, destructora de mundos. Soy la Muerte que se lleva todo, la fuente de las cosas que vendran. www.sistemasyconectividad.com.mxhttp://darkavngr.blogspot.com/ ---(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] PQexec(), what should I do for the NULL in command problem?
Hello:
As you know the interface of PQexec():
PGresult *PQexec(PGconn *conn, const char *command);
command contains some SQL statements.
But there exist \0 in some data filed, and I found PQexec() failed
on such situation.
Something like this:
PQexec(conn, INSERT INTO ('t', 'abc\0abc');
Does anyone know something about this?
How can I insert a record which has some NULLs in data filed?
Best Regards,
Jason
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
[GENERAL] Duplicating a table row while honouring key constraints
I'm developing a web application in PHP and Postgres that will basically serve as a CMS. I want to implement a feature to allow users to make copies of documents or folders, so this will require the appropriate rows to be duplicated. If possible I'd like to do this with SQL queries and avoid SELECTing the row, munging it in PHP and INSERTING it back. I suspect that this is probably the way I'll have to go, but if it could be done entirely in SQL that would be nice. At first I thought INSERT INTO table_name SELECT * from table_name where primary_key = unique_value would do it, but that would obviously violate the primary key uniqueness constraint. I'm wondering if there's a way to do this where I only grab the data to be copied and let the database work out the new primary key itself. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Open Source GIS System
Hi, I am preparing to build up a GIS system as part of my University Masters Thesis. My final aim is to build a system where the user inputs (or via GPS) inputs the location where he is and the location where he want to go and gets the route planned. My main concern will be that the driver can decide which route to take, if the shortest distance, least time consuming or else least fuel consumption. Also the route planned has to be aware of certain problems that the network can find, for example road blocks, or some other type of incidents / accidents. May I point out that I need to use only Open Source Software. I am think to use this algorithm to work with : 1. User inputs starting and ending 2. A script checks the options sent by the user 3. The streets which are blocked by something are mentioned as inactive 4. A route is planned 5. The route is sent as an image to user 6. Wait 20 seconds 7. If start = end goto 10 Else : 8. Reread position (maybe using GPS.. or random new coordinates from the map) 9. Go back to step 3 10. Send a msg : Thanks for driving safely or some other msg :) Now, from the research I have done, it seems I need these Open Source software, and this is what I got the presumption they will suffice for : PostgreSQL + PostGIS : Basically where to store the data GRASS : For data Management QGis (with GRASS extension) : Frontend for viewing the data pgRouting : To create the route OpenLayers : To create the image to be sent to the user's browser. I want a static image which then will be updated every lets say 20 seconds. PHP : To write the scripts to communicate with the PostGIS / User + HTML 4.0 What do you think about my setup please? Is there some software you think I should replace or rethink about using? Following my algorithm and the software I am looking at, I think that I need to do like this : a. A script in PHP which captures the data and then echo the image using OpenLayers to the user's browser b. QGIS (with the save file of the base-map and the roads marked) which allows the Road Network Admin to disable any Road, and another PHP Script that can read the starting, ending coordinates of the road and allow a User to report that there are problems in the road. Any comments / suggestions / change of plans would be extremely appreciated at this stage, Whilst thanking all of you for taking time to read this post, I really wish to hear your opinions. Thanks and Regards Matthew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Linux v.s. Mac OS-X Performance
Our developers run on MacBook Pros w/ 2G memory and our production hardware is dual dual-Core Opterons w/ 8G memory running CentOS 5. The Macs perform common and complex Postgres operations in about half the time of our unloaded production hardware. We've compared configurations and the production hardware is running a much bigger configuration and faster disk. What are we missing? Is there a trick to making AMDs perform? Does Linux suck compared to BSD? Thanks. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] float to int
how can i get the int value using the sql language? like this, select cost from my_money_table , the data type of cost is float. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] How to uninstall the geometry package?
While installing PostgreSQL (8.1) recently, I was offered the option to install the geometry functions. Why not? I said to myself, and to the installer I said yes. Well, the answer to Why not? is that I have a couple hundred functions that I'll probably never use junking up my installation. The install was from the Win installer package postgresql-8.1.msi. My question is: Is there any setup routine or something that I can run to get rid of all the geometry objects, or is the only way to do this to completely uninstall and reinstall PostgreSQL? ~ TIA ~ Ken
Re: [GENERAL] float to int
Charles.Hou ha scritto: how can i get the int value using the sql language? like this, select cost from my_money_table , the data type of cost is float. take a look at http://www.postgresql.org/docs/8.2/static/sql-expressions.html CAST ( expression AS type ) expression::type Edoardo -- Jabber: [EMAIL PROTECTED] tel: 075 9142766 ---(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] PQexec(), what should I do for the NULL in command problem?
On Thu, Nov 08, 2007 at 06:39:23PM -0800, jason wrote: As you know the interface of PQexec(): PGresult *PQexec(PGconn *conn, const char *command); command contains some SQL statements. But there exist \0 in some data filed, and I found PQexec() failed on such situation. Two ways: - out of line parameters - escape the nulls, like \0 Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Those who make peaceful revolution impossible will make violent revolution inevitable. -- John F Kennedy signature.asc Description: Digital signature
Re: [GENERAL] Linux v.s. Mac OS-X Performance
On Nov 9, 2007 10:55 PM, Mark Niedzielski [EMAIL PROTECTED] wrote: Our developers run on MacBook Pros w/ 2G memory and our production hardware is dual dual-Core Opterons w/ 8G memory running CentOS 5. The Macs perform common and complex Postgres operations in about half the time of our unloaded production hardware. We've compared configurations and the production hardware is running a much bigger configuration and faster disk. What are we missing? Is there a trick to making AMDs perform? Does Linux suck compared to BSD? It's quite possible that either you've got some issue with poor hardware / OS integration (think RAID controllers that have bad drivers, etc) or that you've de-tuned postgresql on your CentOS machines when you thought you were tuning it. A common mistake is to set work_mem or shared_buffers so high that they are slower than they would be if they were smaller. Also, if your data sets in production are hundreds of millions of rows, and the test set on your lap top is 100,000 rows, then of course the laptop is going to be faster, it has less data to wade through. So, the key question is what, exactly, is different between your dev laptops and your production machines. ---(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] Filter tables
Hi all. I have this sample setup: CREATE table t1 ( t text, id int ); CREATE TABLE f1 ( t text ); INSERT INTO t1 VALUES ( 'field1',1 ), ( 'field2',1 ), ( 'field3',1 ), ( 'field1',2 ), ( 'field3',3 ) ; INSERT INTO f1 VALUES ( 'field1' ), ( 'field2' ) ; What I'd need to do is to filter t1 against f1 to get only the rows ( 'field1',1 ) and ( 'field2',1 ). Of course both t1 and f1 don't have a defined number of rows, though usually t1 should be much bigger that f1. I have a rather complex solution in mind with loops in a plpgsql function and am wondering whether there is one simpler. Thanks a lot. -- Reg me Please ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] reverse strpos?
Is there a function that'll return the position of the last occurance of a char in a string? For Example, in the string 'abc/def/ghi' I want the position of the 2nd '/'. Thanks in Advance.
Re: [GENERAL] Filter tables
Hi,
Le lundi 12 novembre 2007, Reg Me Please a écrit :
What I'd need to do is to filter t1 against f1 to get only the rows
( 'field1',1 ) and ( 'field2',1 ).
select * from t1 natural join f1 where t1.id = 1;
t| id
+
field1 | 1
field2 | 1
(2 lignes)
I'm not sure about how you wanted to filter out the ('field1', 2) row of table
t1, so used the where t1.id = 1 restriction.
Hope this helps,
--
dim
signature.asc
Description: This is a digitally signed message part.
Re: [GENERAL] pg_tables and temp tables
Gauthier, Dave [EMAIL PROTECTED] writes: How user specific is pg_tables when it comes to temporary tables? regression=# \d pg_tables ... View definition: SELECT n.nspname AS schemaname, c.relname AS tablename, pg_get_userbyid(c.relowner) AS tableowner, t.spcname AS tablespace, c.relhasindex AS hasindexes, c.relhasrules AS hasrules, c.reltriggers 0 AS hastriggers FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace WHERE c.relkind = 'r'::char; Not at all, apparently. This looks to me like it will pick up every table in the database, temporary or otherwise. Perhaps that's not a good idea ... I can live with this as long as there's a way that I can determine if the table it found does not belong to the current session. You need to pay attention to the schema it was found in. has_schema_privilege(schemaname, 'USAGE') might be a suitable filter. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Filter tables
On Nov 12, 2007, at 9:43 AM, Reg Me Please wrote: Hi all. I have this sample setup: CREATE table t1 ( t text, id int ); CREATE TABLE f1 ( t text ); INSERT INTO t1 VALUES ( 'field1',1 ), ( 'field2',1 ), ( 'field3',1 ), ( 'field1',2 ), ( 'field3',3 ) ; INSERT INTO f1 VALUES ( 'field1' ), ( 'field2' ) ; What I'd need to do is to filter t1 against f1 to get only the rows ( 'field1',1 ) and ( 'field2',1 ). Of course both t1 and f1 don't have a defined number of rows, though usually t1 should be much bigger that f1. I have a rather complex solution in mind with loops in a plpgsql function and am wondering whether there is one simpler. You're really going to need to go into some more detail about what you're actually trying to do here. The following query will get your requested results, but I'm not sure it's really what you want: SELECT t1.t, t1.id FROM t1, f1 WHERE t1.t = f1.t and t1.id = 1; Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Regression in 8.3?
Martijn van Oosterhout [EMAIL PROTECTED] writes: On Mon, Nov 12, 2007 at 02:46:50PM +0100, Karsten Hilbert wrote: It surely makes sense - in your environment - but it's not the only interpretation so PG tries to be impartial and makes both of us say clearly what we want. If people want it they can add the automatic cast back in, it just isn't dfault anymore. I wouldn't recommend that, as it'd re-open all the gotchas that we took out the implicit cast to prevent. However, if you want the behavior for LIKE only, you can make an operator: regression=# select 84 like '8%'; ERROR: operator does not exist: integer ~~ unknown LINE 1: select 84 like '8%'; ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. regression=# create function anylike(anyelement, text) returns bool as $$ regression$# select $1::text like $2 regression$# $$ language sql; CREATE FUNCTION regression=# create operator ~~ ( procedure = anylike, regression(# leftarg = anyelement, rightarg = text ); CREATE OPERATOR regression=# select 84 like '8%'; ?column? -- t (1 row) regression=# regards, tom lane ---(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] reverse strpos?
am Mon, dem 12.11.2007, um 10:54:53 -0500 mailte Gauthier, Dave folgendes: Is there a function that?ll return the position of the last occurance of a char in a string? For Example, in the string ?abc/def/ghi? I want the position of the 2^nd ?/?. write a function to revert the string and use strpos(). create or replace function rev(varchar) returns varchar as $$ declare _temp varchar; _count int; begin _temp := ''; for _count in reverse length($1)..1 loop _temp := _temp || substring($1 from _count for 1); end loop; return _temp; end; $$ language plpgsql immutable; Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(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] Regression in 8.3?
On Nov 12, 2007, at 10:18 AM, Tom Lane wrote: Martijn van Oosterhout [EMAIL PROTECTED] writes: On Mon, Nov 12, 2007 at 02:46:50PM +0100, Karsten Hilbert wrote: It surely makes sense - in your environment - but it's not the only interpretation so PG tries to be impartial and makes both of us say clearly what we want. If people want it they can add the automatic cast back in, it just isn't dfault anymore. I wouldn't recommend that, as it'd re-open all the gotchas that we took out the implicit cast to prevent. However, if you want the behavior for LIKE only, you can make an operator: regression=# select 84 like '8%'; ERROR: operator does not exist: integer ~~ unknown LINE 1: select 84 like '8%'; ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. regression=# create function anylike(anyelement, text) returns bool as $$ regression$# select $1::text like $2 regression$# $$ language sql; CREATE FUNCTION regression=# create operator ~~ ( procedure = anylike, regression(# leftarg = anyelement, rightarg = text ); CREATE OPERATOR regression=# select 84 like '8%'; ?column? -- t (1 row) Does this change in implicit conversions also apply to what I've previously seen recommended on the lists wrt filtering dates? i.e WHERE date_field LIKE '2007-11-12%'? Just curious... Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate market in style. Visit us online at http://www.myemma.com ---(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] Regression in 8.3?
hubert depesz lubaczewski [EMAIL PROTECTED] writes: on the other hand. while i know and understand why there can't be = operator for text and int, i think that like could be readded as it is really clear about how it works. Really? regression=# select '8.12345678901234567890' ~~ '%67890' ; ?column? -- t (1 row) regression=# select '8.12345678901234567890'::numeric ~~ '%67890' ; ?column? -- t (1 row) regression=# select '8.12345678901234567890'::float8 ~~ '%67890' ; ?column? -- f (1 row) regression=# select '8.12345678901234567890'::char(50) ~~ '%67890' ; ?column? -- f (1 row) Yup, the behavior of LIKE before 8.3 was just as datatype-independent as could be. regards, tom lane ---(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] Filter tables
Il Monday 12 November 2007 17:05:18 Dimitri Fontaine ha scritto:
Hi,
Le lundi 12 novembre 2007, Reg Me Please a écrit :
What I'd need to do is to filter t1 against f1 to get only the rows
( 'field1',1 ) and ( 'field2',1 ).
select * from t1 natural join f1 where t1.id = 1;
t| id
+
field1 | 1
field2 | 1
(2 lignes)
I'm not sure about how you wanted to filter out the ('field1', 2) row of
table t1, so used the where t1.id = 1 restriction.
Hope this helps,
I think surely I've not been clean enough.
The rows in t1 should be seen as grouped by the field id. A group of such
rouws matches the filter f1 (made by two rows in my example) if I can find
all the values of f1 in the field t of that group.
So, in my example, in t1 the group of rows with id=2 (actually made by only
one row in my example) doesn't match the filter because it's lacking a row
with t='field2'.
In the same way the group of rows with id=3 won't match as they lack both
values that are in f1.
What I'd like to see as an output of the query/function is
id
1
as only the group with id=1 has both the values.
Of course, f1 could have any number of different values.
--
Reg me Please
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Strange error while working with derived table
Hi, Working on postgres8.1 in PHP, I came in the situation I needed to create a derived table. Consider the following query: SELECT tempwordstable.zoekwoord, CV.categoryvalueid, CV.categoryid, CV.shortdesc FROM tblcategoryvalues AS CV, ( (SELECT 'mark' AS zoekwoord) UNION (SELECT 'test' AS zoekwoord)) AS tempwordstable WHERE ( CV.shortdesc ILIKE '%'||tempwordstable.zoekwoord||'%'); The part with the UNION simple fills tempwordstable. This query runs fine. However, if I have only 1 in tempwordstable (so no UNION), like this: SELECT tempwordstable.zoekwoord, CV.categoryvalueid, CV.categoryid, CV.shortdesc FROM tblcategoryvalues AS CV, ( (SELECT 'mark' AS zoekwoord) ) AS tempwordstable WHERE ( CV.shortdesc ILIKE '%'||tempwordstable.zoekwoord||'%'); I get the error: ERROR: failed to find conversion function from unknown to text If I DO cast 'mark' to TEXT like: SELECT tempwordstable.zoekwoord, CV.categoryvalueid, CV.categoryid, CV.shortdesc FROM tblcategoryvalues AS CV, ( (SELECT 'mark'::TEXT AS zoekwoord) ) AS tempwordstable WHERE ( CV.shortdesc ILIKE '%'||tempwordstable.zoekwoord||'%'); all works fine. I understand the ::TEXT part. Since I fill this 'table' on the fly, I should tell WHAT I am using. What I don't understand is why Postgres8.1 need the cast to TEXT for 1 entry in my derived table, and NOT when I use more (using UNION)?? Any clues much appriciated. :-) Regards and TIA, Erwin Moller -- --- Erwin Moller Darwine BV Groenendaal 25f 3011 SK Rotterdam tel 010-2133996 --- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] update record with two-column primary key
Using PG 8.0 on Windows, I have a table 'business_list' with a two column primary key. It's a linking table and it's primary keys are the keys from the two tables I am linking: item_id and business. Should I be able to update one of those primary key fields? Here is the SQL: UPDATE projectdata.business_list SET business = 13 Where item_id = 1 and business = 7; It causes the following error: ERROR: duplicate key violates unique constraint data_business_list_pkey I need the 'business' value in the WHERE to locate the proper record to update but it also seems to be preventing the update. Is something wrong or is it just not possible to update a two-column primary key? I didn't see any directly relevant info in the forum or documentation. Should I add a surrogate key? That would be disappointing since the existing 2 column PK does satisfy 3rd Normal Form. Thanks in advance for any suggestions! Charles Mortell Advanced Planning Technologies, Inc. Mississippi River port of La Crosse, WI cmortell at apt-cafm dot com ---(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] plperl and regexps with accented characters - incompatible?
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 hubert depesz lubaczewski writes: ... return (shift =~ /[a-z0-9_-]+/i) || 0; ... 'require' trapped by operation mask at line 15. it looks strange - what require? As you guessed, it's trying to do load the utf8 pragma, and failing as 'require' (and 'use') are not allowed by default: plperl uses the Safe module to disallow things like 'require Module;'. Unfortunately, the only way around it on your end is to use plperlu - something I recommend anyway (for other reasons). also - perhaps loading of this particular module should be allowed even in plperl? otherwise it requires me to use plperlu for even the simple task of regexp matching. Yes, we might want to consider making utf8 come pre-loaded for plperl. There is no direct or easy way to do it (we don't have finer-grained control than the 'require' opcode), but we could probably dial back restrictions, 'use' it, and then reset the Safe container to its defaults. Not sure what other problems that may cause, however. CCing to hackers for discussion there. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200711121139 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFHOIJPvJuQZxSWSsgRA10hAJ996hZYM8KiuziJb/R2QX0HY754bwCg+xZN kePHNNZbLtRXj6ko8j51waw= =fw0v -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] reverse strpos?
On Mon, Nov 12, 2007 at 05:19:25PM +0100, A. Kretschmer wrote: am Mon, dem 12.11.2007, um 10:54:53 -0500 mailte Gauthier, Dave folgendes: Is there a function that?ll return the position of the last occurance of a char in a string? For Example, in the string ?abc/def/ghi? I want the position of the 2^nd ?/?. write a function to revert the string and use strpos(). create or replace function rev(varchar) returns varchar as $$ declare _temp varchar; _count int; begin _temp := ''; for _count in reverse length($1)..1 loop _temp := _temp || substring($1 from _count for 1); end loop; return _temp; end; $$ language plpgsql immutable; Andreas PL/Perl might be easier: CREATE OR REPLACE FUNCTION rev(TEXT) RETURNS TEXT IMMUTABLE LANGUAGE plperl AS $$ return reverse($_[0]); $$; You could also write wrappers around perl functions if you're taking that route. If you want to guarantee the thing runs on any modern Postgres instance--one where you don't control the environment at all--you could do: CREATE OR REPLACE FUNCTION rev(TEXT) RETURNS TEXT IMMUTABLE LANGUAGE SQL AS $$ SELECT array_to_string( ARRAY( SELECT substr($1,i,1) FROM generate_series(length($1),1,-1) AS i ), '' ); $$; Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] reverse strpos?
Great suggestions (I have just GOT to get the IS people around here to install plperl). Leveraging what Andreas sent, I created my own strrpos create or replace function strrpos(varchar,varchar) returns int as $$ declare _count int; begin for _count in reverse length($1)..1 loop if(substring($1 from _count for 1) = $2) then return _count; end if; end loop; return 0; end; $$ language plpgsql immutable; -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of David Fetter Sent: Monday, November 12, 2007 11:48 AM To: A. Kretschmer Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] reverse strpos? On Mon, Nov 12, 2007 at 05:19:25PM +0100, A. Kretschmer wrote: am Mon, dem 12.11.2007, um 10:54:53 -0500 mailte Gauthier, Dave folgendes: Is there a function that?ll return the position of the last occurance of a char in a string? For Example, in the string ?abc/def/ghi? I want the position of the 2^nd ?/?. write a function to revert the string and use strpos(). create or replace function rev(varchar) returns varchar as $$ declare _temp varchar; _count int; begin _temp := ''; for _count in reverse length($1)..1 loop _temp := _temp || substring($1 from _count for 1); end loop; return _temp; end; $$ language plpgsql immutable; Andreas PL/Perl might be easier: CREATE OR REPLACE FUNCTION rev(TEXT) RETURNS TEXT IMMUTABLE LANGUAGE plperl AS $$ return reverse($_[0]); $$; You could also write wrappers around perl functions if you're taking that route. If you want to guarantee the thing runs on any modern Postgres instance--one where you don't control the environment at all--you could do: CREATE OR REPLACE FUNCTION rev(TEXT) RETURNS TEXT IMMUTABLE LANGUAGE SQL AS $$ SELECT array_to_string( ARRAY( SELECT substr($1,i,1) FROM generate_series(length($1),1,-1) AS i ), '' ); $$; Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ ---(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: [HACKERS] [GENERAL] plperl and regexps with accented characters - incompatible?
Greg Sabino Mullane wrote: Yes, we might want to consider making utf8 come pre-loaded for plperl. There is no direct or easy way to do it (we don't have finer-grained control than the 'require' opcode), but we could probably dial back restrictions, 'use' it, and then reset the Safe container to its defaults. Not sure what other problems that may cause, however. CCing to hackers for discussion there. UTF8 is automatically on for strings passed to plperl if the db encoding is UTF8. That includes the source text. Please be more precise about what you want. BTW, the perl docs say this about the utf8 pragma: Do not use this pragma for anything else than telling Perl that your script is written in UTF-8. There should be no need to do that - we will have done it for you. So any attempt to use the utf8 pragma in plperl code is probably broken anyway. cheers andrew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] update record with two-column primary key
On Nov 12, 2007 10:41 AM, Charles Mortell [EMAIL PROTECTED] wrote: Using PG 8.0 on Windows, I have a table 'business_list' with a two column primary key. It's a linking table and it's primary keys are the keys from the two tables I am linking: item_id and business. Should I be able to update one of those primary key fields? Here is the SQL: UPDATE projectdata.business_list SET business = 13 Where item_id = 1 and business = 7; It causes the following error: ERROR: duplicate key violates unique constraint data_business_list_pkey This is not a complete example. What are the two fields in your pkey? I'm guessing business and list. If list is one, what are the values for list in your table where business=13 and business=7. If you look those up you should see one there with 13 for business and whatever for list that is causing this problem. Is something wrong or is it just not possible to update a two-column primary key? Good lord no. You're just making a simple mistake is all. Here, look: create table test (a int, b int, c text, primary key (a,b)) insert into test values (1,2,'abc'); insert into test values (2,2,'abc'); -- Now I update a without checking on b... update test set a=1 where a=2; ERROR: duplicate key violates unique constraint test_pkey ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] reverse strpos?
am Mon, dem 12.11.2007, um 8:48:29 -0800 mailte David Fetter folgendes: Is there a function that?ll return the position of the last occurance of a char in a string? write a function to revert the string and use strpos(). create or replace function rev(varchar) returns varchar as $$ declare ... $$ language plpgsql immutable; Andreas PL/Perl might be easier: CREATE OR REPLACE FUNCTION rev(TEXT) RETURNS TEXT IMMUTABLE LANGUAGE plperl AS $$ return reverse($_[0]); $$; heh, the PERL-Guru ;-) CREATE OR REPLACE FUNCTION rev(TEXT) RETURNS TEXT IMMUTABLE LANGUAGE SQL AS $$ SELECT array_to_string( ARRAY( SELECT substr($1,i,1) FROM generate_series(length($1),1,-1) AS i ), '' ); $$; Nice. The generate_series()-function are really great. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Linux v.s. Mac OS-X Performance
On Fri, 9 Nov 2007, Mark Niedzielski wrote: The Macs perform common and complex Postgres operations in about half the time of our unloaded production hardware. Are they write intensive? If so, it may be possible that the Macs are buffering disk writes while production server isn't. It's often the case that desktop systems will cheat at writes while servers don't. Is there a trick to making AMDs perform? One problem you can run into is that the default configuration on some Linux+AMD systems will include aggressive power management that throttles the CPU clock down. Take a look at /proc/cpuinfo on your server and see what the cpu MHz reads; if it's 1000.00 or otherwise doesn't match what you expect, you may need to turn off or otherwise tune power management to keep the system running at full speed. My home AMD dual-core system was positively sluggish until I fixed that. Does Linux suck compared to BSD? Not the Mac OS BSD. Last time I looked into this OS X was still dramatically slower than Linux on things like process creation. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] reverse strpos?
On 12/11/2007, Gauthier, Dave [EMAIL PROTECTED] wrote: Is there a function that'll return the position of the last occurance of a char in a string? Hello simply install and use orafce http://www.pgsql.cz/index.php/Oracle_functionality_%28en%29 Regards Pavel Stehule For Example, in the string 'abc/def/ghi' I want the position of the 2nd '/'. Thanks in Advance. ---(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] Filter tables
Reg Me Please escreveu:
Il Monday 12 November 2007 17:05:18 Dimitri Fontaine ha scritto:
Hi,
Le lundi 12 novembre 2007, Reg Me Please a écrit :
What I'd need to do is to filter t1 against f1 to get only the rows
( 'field1',1 ) and ( 'field2',1 ).
select * from t1 natural join f1 where t1.id = 1;
t| id
+
field1 | 1
field2 | 1
(2 lignes)
I'm not sure about how you wanted to filter out the ('field1', 2) row of
table t1, so used the where t1.id = 1 restriction.
Hope this helps,
I think surely I've not been clean enough.
The rows in t1 should be seen as grouped by the field id. A group of such
rouws matches the filter f1 (made by two rows in my example) if I can find
all the values of f1 in the field t of that group.
So, in my example, in t1 the group of rows with id=2 (actually made by only
one row in my example) doesn't match the filter because it's lacking a row
with t='field2'.
In the same way the group of rows with id=3 won't match as they lack both
values that are in f1.
What I'd like to see as an output of the query/function is
id
1
as only the group with id=1 has both the values.
Of course, f1 could have any number of different values.
Try:
SELECT DISTINCT t1.id FROM t1
WHERE NOT EXISTS (SELECT f1.t FROM f1
WHERE NOT EXISTS (SELECT x1.t FROM t1 x1
WHERE f1.t = x1.t
AND t1.id = x1.id));
Osvaldo
---(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] Linux v.s. Mac OS-X Performance
On Fri, 9 Nov 2007, Mark Niedzielski wrote: The Macs perform common and complex Postgres operations in about half the time of our unloaded production hardware. Also, what kernel are you using with CentOS 5 - a 32-bit (with hugemem to support the 8GB) or a 64-bit? And which was PostgreSQL compiled for? -- Steve Wampler -- [EMAIL PROTECTED] The gods that smiled on your birth are now laughing out loud. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Linux v.s. Mac OS-X Performance
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Fri, 09 Nov 2007 23:55:59 -0500 Mark Niedzielski [EMAIL PROTECTED] wrote: Our developers run on MacBook Pros w/ 2G memory and our production hardware is dual dual-Core Opterons w/ 8G memory running CentOS 5. The Macs perform common and complex Postgres operations in about half the time of our unloaded production hardware. We've compared configurations and the production hardware is running a much bigger configuration and faster disk. What are we missing? Likely alot. Are you performing any maintenance? What are your postgresql.conf settings? Are you running 64bit on the Linux machine? Is there a trick to making AMDs perform? Does Linux suck compared to BSD? No. Sincerely, Joshua D. Drake Thanks. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHOIs5ATb/zqfZUUQRAo/3AJ9RLcHedTPvl1qVrOgp3Iz6jPJ4wgCfTRe+ tlLJCa1Y8Y9vZDfuxwTG/Bw= =5hHV -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Filter tables
Il Monday 12 November 2007 18:10:40 Osvaldo Rosario Kussama ha scritto: Try: SELECT DISTINCT t1.id FROM t1 WHERE NOT EXISTS (SELECT f1.t FROM f1 WHERE NOT EXISTS (SELECT x1.t FROM t1 x1 WHERE f1.t = x1.t AND t1.id = x1.id)); Osvaldo Nice, it seems to work. But I fear it won't with a longer f1 filter table. Let me think about it. -- Reg me Please ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Linux v.s. Mac OS-X Performance
On Mon, Nov 12, 2007 at 10:14:46AM -0700, Steve Wampler wrote: Also, what kernel are you using with CentOS 5 - a 32-bit (with hugemem to support the 8GB) or a 64-bit? And which was PostgreSQL compiled for? You don't need a 32bit kernel to support 8GB of memory should you? As long as the kernel supports PAE that should be enough to make use of it. You only need a 64bit address space when each process wants to see more than ~3GB of RAM. Sam ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Linux v.s. Mac OS-X Performance
On Nov 12, 2007 11:29 AM, Sam Mason [EMAIL PROTECTED] wrote: On Mon, Nov 12, 2007 at 10:14:46AM -0700, Steve Wampler wrote: Also, what kernel are you using with CentOS 5 - a 32-bit (with hugemem to support the 8GB) or a 64-bit? And which was PostgreSQL compiled for? You don't need a 32bit kernel to support 8GB of memory should you? As long as the kernel supports PAE that should be enough to make use of it. You only need a 64bit address space when each process wants to see more than ~3GB of RAM. There's a performance hit for using PAE. Not sure what it is, but I recall it being the in the 5 to 10% range. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Linux v.s. Mac OS-X Performance
On Mon, Nov 12, 2007 at 11:31:59AM -0600, Scott Marlowe wrote: On Nov 12, 2007 11:29 AM, Sam Mason [EMAIL PROTECTED] wrote: You don't need a 32bit kernel to support 8GB of memory should you? As long as the kernel supports PAE that should be enough to make use of it. You only need a 64bit address space when each process wants to see more than ~3GB of RAM. There's a performance hit for using PAE. Not sure what it is, but I recall it being the in the 5 to 10% range. And what's the performance hit of using native 64bit code? I'd guess similar, moving twice as much data around with each pointer has got to affect things. Sam ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Linux v.s. Mac OS-X Performance
Scott Marlowe wrote: On Nov 12, 2007 11:29 AM, Sam Mason [EMAIL PROTECTED] wrote: You don't need a 32bit kernel to support 8GB of memory should you? As long as the kernel supports PAE that should be enough to make use of it. You only need a 64bit address space when each process wants to see more than ~3GB of RAM. There's a performance hit for using PAE. Not sure what it is, but I recall it being the in the 5 to 10% range. Also, using PAE *used* to require the (OS-internal) use of 'bounce-buffers' to copy data from processes high-up in memory down to i/o devices low-down in memory. I don't know if that's still an issue or not with 2.6 kernels, but I could see it still being the case and, if so, seems like it would have a significant impact on I/O bound tasks (like most DB processing...) -- Steve Wampler -- [EMAIL PROTECTED] The gods that smiled on your birth are now laughing out loud. ---(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] Linux v.s. Mac OS-X Performance
On Nov 12, 2007 11:37 AM, Sam Mason [EMAIL PROTECTED] wrote: On Mon, Nov 12, 2007 at 11:31:59AM -0600, Scott Marlowe wrote: On Nov 12, 2007 11:29 AM, Sam Mason [EMAIL PROTECTED] wrote: You don't need a 32bit kernel to support 8GB of memory should you? As long as the kernel supports PAE that should be enough to make use of it. You only need a 64bit address space when each process wants to see more than ~3GB of RAM. There's a performance hit for using PAE. Not sure what it is, but I recall it being the in the 5 to 10% range. And what's the performance hit of using native 64bit code? I'd guess similar, moving twice as much data around with each pointer has got to affect things. That's not been my experience. It's not like everything you do requires 64 bits to be moved where in 32 bit code only 32 were moved. The performance gain of the 64 bit machine doing 64 bit operations over the 32 bit machine doing them (i.e. floating point etc...) is so much more that it more than makes up for the overhead of running in 64 bit mode. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Linux v.s. Mac OS-X Performance
Sam Mason wrote: And what's the performance hit of using native 64bit code? I'd guess similar, moving twice as much data around with each pointer has got to affect things. That's probably difficult to predict. Since the architecture is 64-bits, it shouldn't cost any more to move a 64-bit pointer around as a 32-bit one. (Plus, I *think* you get more registers in 64-bit mode.) However, a good optimizer might figure out it can move two 32-bit pointers with one 64-bit transfer. -- Steve Wampler -- [EMAIL PROTECTED] The gods that smiled on your birth are now laughing out loud. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Linux v.s. Mac OS-X Performance
Scott Marlowe [EMAIL PROTECTED] writes: On Nov 12, 2007 11:37 AM, Sam Mason [EMAIL PROTECTED] wrote: And what's the performance hit of using native 64bit code? I'd guess similar, moving twice as much data around with each pointer has got to affect things. That's not been my experience. It's not like everything you do requires 64 bits to be moved where in 32 bit code only 32 were moved. The performance gain of the 64 bit machine doing 64 bit operations over the 32 bit machine doing them (i.e. floating point etc...) is so much more that it more than makes up for the overhead of running in 64 bit mode. Plus, 64-bit mode gives you twice as many CPU registers, which is a huge win for some algorithms, though in many cases it doesn't make much of a difference. -Doug ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [GENERAL] plperl and regexps with accented characters - incompatible?
Andrew Dunstan wrote: Greg Sabino Mullane wrote: Yes, we might want to consider making utf8 come pre-loaded for plperl. There is no direct or easy way to do it (we don't have finer-grained control than the 'require' opcode), but we could probably dial back restrictions, 'use' it, and then reset the Safe container to its defaults. Not sure what other problems that may cause, however. CCing to hackers for discussion there. UTF8 is automatically on for strings passed to plperl if the db encoding is UTF8. That includes the source text. Please be more precise about what you want. BTW, the perl docs say this about the utf8 pragma: Do not use this pragma for anything else than telling Perl that your script is written in UTF-8. There should be no need to do that - we will have done it for you. So any attempt to use the utf8 pragma in plperl code is probably broken anyway. Ugh, in testing I see some nastiness here without any explicit require. It looks like there's an implicit require if the text contains certain chars. I'll see what I can do to fix the bug, although I'm not sure if it's possible. cheers andrew ---(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] Linux v.s. Mac OS-X Performance
On Mon, Nov 12, 2007 at 11:46:12AM -0600, Scott Marlowe wrote: On Nov 12, 2007 11:37 AM, Sam Mason [EMAIL PROTECTED] wrote: And what's the performance hit of using native 64bit code? I'd guess similar, moving twice as much data around with each pointer has got to affect things. That's not been my experience. It's not like everything you do requires 64 bits to be moved where in 32 bit code only 32 were moved. The performance gain of the 64 bit machine doing 64 bit operations over the 32 bit machine doing them (i.e. floating point etc...) is so much more that it more than makes up for the overhead of running in 64 bit mode. OK, I'm willing to believe you. It used to be a big misunderstanding that moving to 64bits automatically speed things up, things like this change though. Sam ---(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] Linux v.s. Mac OS-X Performance
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Mon, 12 Nov 2007 10:47:29 -0700 Steve Wampler [EMAIL PROTECTED] wrote: Sam Mason wrote: And what's the performance hit of using native 64bit code? I'd guess similar, moving twice as much data around with each pointer has got to affect things. That's probably difficult to predict. Since the architecture is 64-bits, it shouldn't cost any more to move a 64-bit pointer around as a 32-bit one. (Plus, I *think* you get more registers in 64-bit mode.) It's all about the registers man... all extra 8 of them. Unless of course you are running with 8GB of ram, then it is all about the ability to use more than 2GB of shared memory. Joshua D. Drake - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHOJndATb/zqfZUUQRAjsLAJ4tzk65jzGRGMv33/voxCrqq7O/UACfQR6R jO/YsOG+4Opq4y8QgoXrnQg= =/dNT -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [GENERAL] Is query a reserved word in 8.3 plpgsql?
Tom Lane wrote: It turned out to be a very easy change, so it's done: QUERY isn't a reserved word anymore. Thanks for your help. :) -- todd ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] WAL segments size
Hi, One of our PostgreSQL databases is generating archive logs too frequently, since the number of transactions/s (inserts updates) is very high. I was looking for a parameter on postgresql.conf to increase the WAL segments size, but according to documentation this can only be changed while building PostgreSQL. Some questions came up to my mind when I read that: 1. For databases with this kind of behavior (high number of inserts updates), wouldn't be better (from the performance point of view) to have larger WAL segments? 2. Is there a special reason to not allow the WAL size to be changed on postgresql.conf (version 8.2 or 8.3)? Best regards, Bruno Lago ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Open Source GIS System
have you had a chance to look at University of Minn Mapping Server? http://www.esri.com/software/arcgis/arcgismobile/index.html M-- - Original Message - Wrom: OQKEDOTWFAOBUZXUWLSZLKBRNVW To: pgsql-general@postgresql.org Sent: Thursday, November 08, 2007 3:14 PM Subject: [GENERAL] Open Source GIS System Hi, I am preparing to build up a GIS system as part of my University Masters Thesis. My final aim is to build a system where the user inputs (or via GPS) inputs the location where he is and the location where he want to go and gets the route planned. My main concern will be that the driver can decide which route to take, if the shortest distance, least time consuming or else least fuel consumption. Also the route planned has to be aware of certain problems that the network can find, for example road blocks, or some other type of incidents / accidents. May I point out that I need to use only Open Source Software. I am think to use this algorithm to work with : 1. User inputs starting and ending 2. A script checks the options sent by the user 3. The streets which are blocked by something are mentioned as inactive 4. A route is planned 5. The route is sent as an image to user 6. Wait 20 seconds 7. If start = end goto 10 Else : 8. Reread position (maybe using GPS.. or random new coordinates from the map) 9. Go back to step 3 10. Send a msg : Thanks for driving safely or some other msg :) Now, from the research I have done, it seems I need these Open Source software, and this is what I got the presumption they will suffice for : PostgreSQL + PostGIS : Basically where to store the data GRASS : For data Management QGis (with GRASS extension) : Frontend for viewing the data pgRouting : To create the route OpenLayers : To create the image to be sent to the user's browser. I want a static image which then will be updated every lets say 20 seconds. PHP : To write the scripts to communicate with the PostGIS / User + HTML 4.0 What do you think about my setup please? Is there some software you think I should replace or rethink about using? Following my algorithm and the software I am looking at, I think that I need to do like this : a. A script in PHP which captures the data and then echo the image using OpenLayers to the user's browser b. QGIS (with the save file of the base-map and the roads marked) which allows the Road Network Admin to disable any Road, and another PHP Script that can read the starting, ending coordinates of the road and allow a User to report that there are problems in the road. Any comments / suggestions / change of plans would be extremely appreciated at this stage, Whilst thanking all of you for taking time to read this post, I really wish to hear your opinions. Thanks and Regards Matthew ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(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] Regression in 8.3?
On Mon, Nov 12, 2007 at 11:26:09AM -0500, Tom Lane wrote: hubert depesz lubaczewski [EMAIL PROTECTED] writes: on the other hand. while i know and understand why there can't be = operator for text and int, i think that like could be readded as it is really clear about how it works. Really? yes. i still hold my opinion: *int* like *text* is clear. depesz -- quicksil1er: postgres is excellent, but like any DB it requires a highly paid DBA. here's my CV! :) http://www.depesz.com/ - blog dla ciebie (i moje CV) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] WAL segments size
On Mon, Nov 12, 2007 at 05:14:24PM -0300, Bruno Almeida do Lago wrote: I was looking for a parameter on postgresql.conf to increase the WAL segments size, but according to documentation this can only be changed while building PostgreSQL. You mean this: http://www.postgresql.org/docs/8.2/static/runtime-config-wal.html#GUC-CHECKPOINT-SEGMENTS ? That doesn't say you have to set it at compile time. You can't change it without _restarting_ Postgres. That's because it's something that has to be initialized at start up. A -- Andrew Sullivan Old sigs will return after re-constitution of blue smoke ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] WAL segments size
On Nov 12, 2007 2:14 PM, Bruno Almeida do Lago [EMAIL PROTECTED] wrote: Hi, One of our PostgreSQL databases is generating archive logs too frequently, since the number of transactions/s (inserts updates) is very high. I was looking for a parameter on postgresql.conf to increase the WAL segments size, but according to documentation this can only be changed while building PostgreSQL. There's no great need to change the SIZE, just increase the number of WAL segments. More importantly, look into tuning your bgwriter so it keeps up with the write load and you don't have a lot of checkpoints. There was a post here last week: http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm that had a very good tutorial on bgwriting and how to configure it. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] reverse strpos?
On Mon, Nov 12, 2007 at 10:54:53AM -0500, Gauthier, Dave wrote:
Is there a function that'll return the position of the last occurance of
a char in a string?
For Example, in the string 'abc/def/ghi' I want the position of the 2nd
'/'.
# select length(substring('abc/def/ghi' from '^(.*/)'));
length
8
(1 row)
depesz
--
quicksil1er: postgres is excellent, but like any DB it requires a
highly paid DBA. here's my CV! :)
http://www.depesz.com/ - blog dla ciebie (i moje CV)
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [GENERAL] plperl and regexps with accented characters - incompatible?
Andrew Dunstan wrote: Ugh, in testing I see some nastiness here without any explicit require. It looks like there's an implicit require if the text contains certain chars. I'll see what I can do to fix the bug, although I'm not sure if it's possible. Looks like it's going to be very hard, unless someone has some brilliant insight I'm missing :-( Maybe we need to consult the perl coders. cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] WAL segments size
On Mon, 12 Nov 2007, Bruno Almeida do Lago wrote: One of our PostgreSQL databases is generating archive logs too frequently, since the number of transactions/s (inserts updates) is very high. If the problem is that the archive logs are too frequent, then suggestions so far like increasing checkpoint_segments aren't going to help you; you've asked the right question. I was looking for a parameter on postgresql.conf to increase the WAL segments size, but according to documentation this can only be changed while building PostgreSQL. You have to edit src/include/pg_config_manual.h and adjust XLOG_BLCKSZ, which defaults to 8192. Note the warning in there about direct I/O, which you may be using depending on your wal_sync_method. 1. For databases with this kind of behavior (high number of inserts updates), wouldn't be better (from the performance point of view) to have larger WAL segments? Sure. There are commercial PostgreSQL distributions that increase some of these block sizes for reasons like this. 2. Is there a special reason to not allow the WAL size to be changed on postgresql.conf (version 8.2 or 8.3)? When you change this number, you actually have to re-create the database using initdb in order to rebuild it that way. So it's impractical to change it at run time, and playing with the value can be dangerous. That's why it doesn't make sense for there to be an easy interface to make this change. If you wanted to do this for your application, you'll have to go through a dump, initdb with the change, then reload on your database. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Regression in 8.3?
Martijn van Oosterhout [EMAIL PROTECTED] writes:
On Mon, Nov 12, 2007 at 02:46:50PM +0100, Karsten Hilbert wrote:
It surely makes sense - in your environment - but it's not
the only interpretation so PG tries to be impartial and
makes both of us say clearly what we want.
If people want it they can add the automatic cast back in, it just
isn't dfault anymore.
Any recommendations on how one might go about generating a list of all of the
removed implicit casts?
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
---(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] Duplicating a table row while honouring key constraints
Gordon wrote: At first I thought INSERT INTO table_name SELECT * from table_name where primary_key = unique_value would do it, but that would obviously violate the primary key uniqueness constraint. I'm wondering if there's a way to do this where I only grab the data to be copied and let the database work out the new primary key itself. Well, try INSERT INTO table_name SELECT col1, col2, ... FROM table_name WHERE primary_key = unique_value where the colX list excludes the primary key columns. Perhaps add DEFAULT specification for those so that they are generated from a sequence or whatever default generator you have. -- Alvaro Herrera Valdivia, Chile ICBM: S 39º 49' 18.1, W 73º 13' 56.4 A wizard is never late, Frodo Baggins, nor is he early. He arrives precisely when he means to. (Gandalf, en LoTR FoTR) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Linux v.s. Mac OS-X Performance
On Nov 12, 2007, at 12:29 PM, Sam Mason wrote: You only need a 64bit address space when each process wants to see more than ~3GB of RAM. And how exactly do you get that on a 32-bit CPU? Even with PAE (shudders from memories of expanded/extended RAM in the DOS days), you still have a 32-bit address space per-process. ---(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] Linux v.s. Mac OS-X Performance
On Nov 12, 2007, at 12:01 PM, Greg Smith wrote: Not the Mac OS BSD. Last time I looked into this OS X was still dramatically slower than Linux on things like process creation. On MacOS X, that's the Mach kernel doing process creation, not anything BSD-ish at all. The BSD flavor of MacOS X is mostly just the userland experience. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] (Never?) Kill Postmaster?
=?ISO-8859-1?Q?Christian_Schr=F6der?= [EMAIL PROTECTED] writes: [ ongoing saga ] Hah, I've got it. (Should have searched Red Hat's bugzilla sooner.) What you are hitting is a glibc bug, as explained here: http://sources.redhat.com/ml/libc-hacker/2007-10/msg00010.html If libpthread is loaded after first use of dcgettext, then subsequent uses are at risk of hanging because they start to use a mutex lock that was never initialized. And libperl brings in libpthread. I've confirmed that the test program given in the above page hangs on your machine, and does not hang on mine (with latest Fedora 6 glibc), which no doubt explains my lack of success in reproducing the problem. So you need to pester SuSE for a glibc with that fix in it ... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Regression in 8.3?
hubert depesz lubaczewski [EMAIL PROTECTED] writes: On Mon, Nov 12, 2007 at 11:26:09AM -0500, Tom Lane wrote: hubert depesz lubaczewski [EMAIL PROTECTED] writes: on the other hand. while i know and understand why there can't be = operator for text and int, i think that like could be readded as it is really clear about how it works. Really? yes. i still hold my opinion: *int* like *text* is clear. regression=# select '00123'::text like '0%'; ?column? -- t (1 row) regression=# select '00123'::int4 like '0%'; ?column? -- f (1 row) regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] update record with two-column primary key
Please keep replies on the list, it's more likely to find an answer with many eyes on the problem. On Nov 12, 2007 4:13 PM, Charles Mortell [EMAIL PROTECTED] wrote: Thanks for your response, Scott. The primary keys of projectdata.business_list are item_id and business. They are from the two tables I am linking. Item_id is unchanged. With this query: UPDATE projectdata.business_list SET business = 13 Where item_id = 1 and business = 7; And this View and rule: CREATE TABLE projectdata.data_business_list ( item_id int4 NOT NULL, business int4 NOT NULL, comments varchar(256), CONSTRAINT data_business_list_pkey PRIMARY KEY (item_id, business) ) CREATE OR REPLACE VIEW projectdata.business_list AS SELECT t.item_id, t.business, t.comments FROM projectdata.data_business_list t; CREATE OR REPLACE RULE update_buslistview AS ON UPDATE TO projectdata.business_list DO INSTEAD UPDATE projectdata.data_business_list SET item_id = new.item_id, business = new.business, comments = new.comments WHERE data_business_list.item_id = old.item_id; What is the item_id going to get set to by the update up there? I'm thinking with that query, item_id will be null. We've tried several variations of the update rule: with and without the item_id, etc. The 'comments' column does update correctly through the view. Have you tried this: UPDATE projectdata.business_list SET business = 13, item_id=item_id where item_id = 1 and business = 7; ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Regression in 8.3?
Robert Treat [EMAIL PROTECTED] writes: Any recommendations on how one might go about generating a list of all of the removed implicit casts? Compare 8.2 and 8.3 contents of pg_cast? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] update record with two-column primary key
OK, a followup. The problem is that your where clause in your update rule isn't selective enough, so you're actually trying to update all the rows that match just the one column in your where clause. Here's an example: Note that I've disabled your pk so you can see what's happening: drop table projectdata.data_business_list cascade; CREATE TABLE projectdata.data_business_list ( item_id int4 NOT NULL, business int4 NOT NULL, comments varchar(256) -- , CONSTRAINT data_business_list_pkey PRIMARY KEY (item_id, business) ); CREATE OR REPLACE VIEW projectdata.business_list AS SELECT t.item_id, t.business, t.comments FROM projectdata.data_business_list t; CREATE OR REPLACE RULE update_buslistview AS ON UPDATE TO projectdata.business_list DO INSTEAD UPDATE projectdata.data_business_list SET business = new.business, item_id=new.item_id, comments = new.comments WHERE item_id = old.item_id; -- and business=old.business; insert into projectdata.data_business_list (item_id, business, comments) values (1,2,'abc'), (1,3,'xyz'); UPDATE projectdata.business_list SET business = 13 Where item_id = 1 and business = 2; select * from projectdata.business_list ; You'll see the output is this: item_id | business | comments -+--+-- 1 | 13 | abc 1 | 13 | abc Note that even the comments are the same. However, if we make your where clause in your rule more selective, by removing the ; and -- in the middle of it, and it looks like this: WHERE item_id = old.item_id and business=old.business; and run the query again, we get: select * from projectdata.business_list ; item_id | business | comments -+--+-- 1 |3 | xyz 1 | 13 | abc Now we test it with a real primary key and it also works the same. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Using generate_series to create a unique ID in a query?
Hello, I am trying to create a view that will contain a generated sequence (unique ID), and am running into problems doing so. For some context, I am trying to create a report that provides a list of client charges and payments and a running balance after each transaction. Because we often have multiple charges and/or payments on the same day, we can't use the transaction date to calculate this balance. Instead, I want to calculate our running balance by assigning a transaction ID to each transaction a d then having the query sum up transaction amounts for all transactions with an equal or lower ID. I can use generate_series to produce a set of IDs, but can't get it to join properly to the rest of my query. For example, if I had 10 rows in my query, I would get a series of 1 to 10, but would then get 100 rows (10x10) in my result. Ultimately the results of this query are going to be used as a view, so I'd like to avoid creating a temp table, sequence, etc. Does anyone know how to use generate_series in this manner, or know of some other way I can go about this? Thanks in advance! To recap with an example, the query below works fine, but how do I add a series to it? SELECT * FROM ( SELECT client_id, effective_date AS transaction_date, amount AS charge_amount, 0 AS payment_amount FROMcharge UNION SELECT client_id, payment_date AS transaction_date, 0 as charge_amount, amount AS payment_amount FROM payment ) AS tmp ORDER BY transaction_date, charge_amount0 /* order charges before payments */ Thanks, Sarah Dougherty begin:vcard fn:Sarah Dougherty n:Dougherty;Sarah org:Downtown Emergency Service Center;Information Services email;internet:[EMAIL PROTECTED] title:Data/Reports Specialist x-mozilla-html:TRUE version:2.1 end:vcard ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Linux v.s. Mac OS-X Performance
On Mon, Nov 12, 2007 at 05:02:52PM -0500, Vivek Khera wrote: On Nov 12, 2007, at 12:29 PM, Sam Mason wrote: You only need a 64bit address space when each process wants to see more than ~3GB of RAM. And how exactly do you get that on a 32-bit CPU? I didn't mean to suggest you could. You can actually hack around it by performing various kernel specific tricks (mmap()ing different parts of a large file works under some Unixes) but it's a lot of work and tends to be difficult and brittle. Even with PAE (shudders from memories of expanded/extended RAM in the DOS days), you still have a 32-bit address space per-process. Yes, if you've got several clients connected they can each have their 3GB address space in RAM and not swapped out, or you have have lots of disk cache. Other people can probably comment on what life is actually on a box like this, I've not had much experience. Sam ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] PITR and warm standby setup questions
I am setting up a warm standby configuration as described here: http://www.postgresql.org/docs/8.2/static/warm-standby.html Using PostgreSql 8.2.5 My production server is archiving 16MB wal segment files at a rate of 1 every 5 to 10 seconds My standby server is processing the wal segment files at a rate of 1 every 10 to 40 seconds At this rate the standby will never keep up with the production server. The production server has a 10 disk RAID 1+0 configuration and 32GB RAM The standby server has a 4 disk RAID 1+0 configuration and 16GB RAM, with an extra disk to hold the wal archive files (separate from the RAID) otherwise they are identically configured First question: Can anyone suggest a configuration change that might speed up processing of the wal segment files on my standby server? Second question: I have the standby server running in perpetual recovery mode. After the wal segment file is copied by the restore_command script, is it safe delete it from my archive? I assume so, but I haven't seen deletion addressed in any of the documentation or examples I've managed to find online. Thanks in advance, Mason
[GENERAL] FreeBSD portupgrade of 8.1 - 8.2
I've got 8.1 running fine. I want to upgrade to 8.2. Problem is, FreeBSD's portupgrade utility only wants to upgrade my existing 8.1 installation. So I grabbed the latest ports collection, which includes postgresql82-client and postgresql82-server. Running 'make install' on postgresql82-client gives me: === Installing for postgresql-client-8.2.5_1 === postgresql-client-8.2.5_1 conflicts with installed package(s): postgresql-client-8.1.10 They install files into the same place. Please remove them first with pkg_delete(1). *** Error code 1 So I ran: pkg_delete postgresql-client-8.1.10 ... and got this error: pkg_delete: package 'postgresql-client-8.1.10' is required by these other packages and may not be deinstalled: dovecot-1.0.0 kde-3.5.6_1 koffice-1.6.2_3,2 php5-extensions-1.1 php5-pgsql-5.2.3 postgresql-libpqxx-2.6.9 postgresql-plperl-8.1.9 postgresql-server-8.1.10_2 I seem to have hit a brick wall. I tried installing postgresql82-server first but it wouldn't do that without the 8.2 client library installed. What's the portupgrade process in FreeBSD?? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PITR and warm standby setup questions
On Nov 12, 2007 6:59 PM, Mason Hale [EMAIL PROTECTED] wrote: I am setting up a warm standby configuration as described here: http://www.postgresql.org/docs/8.2/static/warm-standby.html Using PostgreSql 8.2.5 My production server is archiving 16MB wal segment files at a rate of 1 every 5 to 10 seconds My standby server is processing the wal segment files at a rate of 1 every 10 to 40 seconds At this rate the standby will never keep up with the production server. The production server has a 10 disk RAID 1+0 configuration and 32GB RAM The standby server has a 4 disk RAID 1+0 configuration and 16GB RAM, with an extra disk to hold the wal archive files (separate from the RAID) otherwise they are identically configured your i/o must be really random to be seeing numbers that lousy (10 seconds to replay a file is 1.6 megabytes/sec), or there is some other unexplained problem with your server. is your raid controller properly caching wites? have you benchmarked the volume with bonnie++ or similar tool (pay close attention to seeks). merlin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Linux v.s. Mac OS-X Performance
On Fri, 2007-11-09 at 23:55 -0500, Mark Niedzielski wrote: Our developers run on MacBook Pros w/ 2G memory and our production hardware is dual dual-Core Opterons w/ 8G memory running CentOS 5. The Macs perform common and complex Postgres operations in about half the time of our unloaded production hardware. We've compared configurations and the production hardware is running a much bigger configuration and faster disk. What are we missing? Is there a trick to making AMDs perform? Does Linux suck compared to BSD? that was an awful lot of discussion without any empirical evidence to support the original claim. my understanding was that the lack of threading on OSX made it especially poor for a DB server (but if I recall correctly, that information was on MySQL). Do I smell a plant? Craig ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] FreeBSD portupgrade of 8.1 - 8.2
Steve Manes wrote: What's the portupgrade process in FreeBSD?? (Fixed. The answer is to use pg_delete -f on the old package to force the delete) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Updated .vim file
On Oct 31, 2007, at 9:33 AM, Filip Rembiałkowski wrote: 2007/10/30, Decibel! [EMAIL PROTECTED]: Does anyone have a .vim file that takes dollar quoting into account? I've tried the one mentioned at http://archives.postgresql.org/pgsql-general/2006-04/ msg01266.php , but it doesn't appear to understand dollar quotes. dollar quoting is mostly used for function bodies. would you like to have them all in StringConstantColor? :) I like it more as it is now in Vim... Ugh. Yeah, good point. What I actually want is dollar quoting except for functions. Just catching '$$' would suffice for most of it... -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] FreeBSD portupgrade of 8.1 - 8.2
You can force the pkg_delete with -f (or maybe -F). You'll want to delete all the postgresql (ie: postgresql-*) packages as well, and then re-install them after you install 8.2. On Nov 12, 2007, at 5:39 PM, Steve Manes wrote: I've got 8.1 running fine. I want to upgrade to 8.2. Problem is, FreeBSD's portupgrade utility only wants to upgrade my existing 8.1 installation. So I grabbed the latest ports collection, which includes postgresql82-client and postgresql82-server. Running 'make install' on postgresql82-client gives me: === Installing for postgresql-client-8.2.5_1 === postgresql-client-8.2.5_1 conflicts with installed package(s): postgresql-client-8.1.10 They install files into the same place. Please remove them first with pkg_delete(1). *** Error code 1 So I ran: pkg_delete postgresql-client-8.1.10 ... and got this error: pkg_delete: package 'postgresql-client-8.1.10' is required by these other packages and may not be deinstalled: dovecot-1.0.0 kde-3.5.6_1 koffice-1.6.2_3,2 php5-extensions-1.1 php5-pgsql-5.2.3 postgresql-libpqxx-2.6.9 postgresql-plperl-8.1.9 postgresql-server-8.1.10_2 I seem to have hit a brick wall. I tried installing postgresql82- server first but it wouldn't do that without the 8.2 client library installed. What's the portupgrade process in FreeBSD?? ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] PQexec(), what should I do for the NULL in command problem?
On Nov 12, 2007 11:13 PM, Martijn van Oosterhout [EMAIL PROTECTED] wrote: On Thu, Nov 08, 2007 at 06:39:23PM -0800, jason wrote: As you know the interface of PQexec(): PGresult *PQexec(PGconn *conn, const char *command); command contains some SQL statements. But there exist \0 in some data filed, and I found PQexec() failed on such situation. Thanks for reply :-) But would you please give me more details or some references? Two ways: - out of line parameters How to? Is there any function for this? I mean the C interface. - escape the nulls, like \0 Do you mean this function? #unsigned char *PQescapeBytea(const unsigned char *from, #size_t from_length, #size_t *to_length); But after the escape, the data is changed, and it cannot automatically change back when insert them into database. I have to unescape it when query. Is this necessary? Best Regards, Jason ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PITR and warm standby setup questions
your i/o must be really random to be seeing numbers that lousy (10 seconds to replay a file is 1.6 megabytes/sec), or there is some other unexplained problem with your server. is your raid controller properly caching wites? have you benchmarked the volume with bonnie++ or similar tool (pay close attention to seeks). Here's the bonnie++ output (two runs): Version 1.03 --Sequential Output-- --Sequential Input- --Random- -Per Chr- --Block-- -Rewrite- -Per Chr- --Block-- --Seeks-- MachineSize K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP /sec %CP dev-db-232G 43174 99 87421 24 45614 12 48302 97 164574 23 205.3 0 --Sequential Create-- Random Create -Create-- --Read--- -Delete-- -Create-- --Read--- -Delete-- files /sec %CP /sec %CP /sec %CP /sec %CP /sec %CP /sec %CP 16 + +++ + +++ + +++ + +++ + +++ + +++ Having never used bonnie++ before, I don't have a baseline to compare this against, but that looks like 87MB/s writes and 164MB/s reads to me. Am I reading this correctly? It looks pretty good to me. Here is some output from iostat Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 0.00 0.00 0.00 0 0 sdb 1.00 0.0055.72 0112 sdc 1.00 0.0063.68 0128 sdd 101.49 1699.50 0.00 3416 0 avg-cpu: %user %nice %system %iowait %steal %idle 0.060.000.06 12.370.00 87.51 Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 2.50 0.0024.00 0 48 sdb 0.00 0.00 0.00 0 0 sdc 42.50 0.00 8288.00 0 16576 sdd 101.50 1688.00 0.00 3376 0 avg-cpu: %user %nice %system %iowait %steal %idle 0.120.000.06 12.350.00 87.46 Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 0.00 0.00 0.00 0 0 sdb 0.00 0.00 0.00 0 0 sdc 0.00 0.00 0.00 0 0 sdd 112.44 1787.06 0.00 3592 0 avg-cpu: %user %nice %system %iowait %steal %idle 0.120.000.06 12.360.00 87.45 Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 0.00 0.00 0.00 0 0 sdb 4.50 0.0048.00 0 96 sdc 0.50 0.00 4.00 0 8 sdd 97.50 1752.00 0.00 3504 0 In the above: sdb holds the pg_xlog directory, sdc holds the wal archive, and sdd is the 4 disk RAID 1+0 where the pgdata directory is stored. All these disks are ext3 with noatime,data=writeback mount options. The RAID controller is an Adaptec 3805 with 128MB battery backed cache (only option offered by our hosting provider for this server class). Does any of this shed any light on how to boost my restore performance? thanks, Mason
Re: [GENERAL] PITR and warm standby setup questions
On Mon, 12 Nov 2007, Mason Hale wrote: After the wal segment file is copied by the restore_command script, is it safe to delete it from my archive? While I believe you can toss them immediately, you should considering keeping those around for a bit regardless as an additional layer of disaster recovery resources. I try to avoid deleting them until a new base backup is made, because if you have the last backup and all the archived segments it gives you another potential way to rebuild the database in case of a large disaster damages both the primary and the secondary. You can never have too many ways to try and recover from such a situation. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Regression in 8.3?
On Mon, Nov 12, 2007 at 05:18:28PM -0500, Tom Lane wrote: regression=# select '00123'::text like '0%'; ?column? -- t (1 row) regression=# select '00123'::int4 like '0%'; ?column? -- f (1 row) i think it's definitelly ok - '00123'::text is *not equal* to '00123'::int4. so result of like'ing it is not necessarily the same. what's more - in case patric showed - when he had int column there was no danger of '00123'::int4. best regards, depesz -- quicksil1er: postgres is excellent, but like any DB it requires a highly paid DBA. here's my CV! :) http://www.depesz.com/ - blog dla ciebie (i moje CV) ---(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] PITR and warm standby setup questions
On Nov 12, 2007 11:03 PM, Mason Hale [EMAIL PROTECTED] wrote: your i/o must be really random to be seeing numbers that lousy (10 seconds to replay a file is 1.6 megabytes/sec), or there is some other unexplained problem with your server. is your raid controller properly caching wites? have you benchmarked the volume with bonnie++ or similar tool (pay close attention to seeks). Here's the bonnie++ output (two runs): Version 1.03 --Sequential Output-- --Sequential Input- --Random- -Per Chr- --Block-- -Rewrite- -Per Chr- --Block-- --Seeks-- MachineSize K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP /sec %CP dev-db-232G 43174 99 87421 24 45614 12 48302 97 164574 23 205.3 your seeks are low, especially if these are 15k drives. The sequential numbers are ok but not very important in the scheme of things. Interestingly, your 'sdd' device is not doing any writing in the iostat samples you sent...is that a typical sample? how long are your iostat intervals? your iowait numbers are also remarkably stable. did you iostat the device when doing bonnie? (an iostat during wal replay is much more interesting) here are some random suggestions: * play with partial wal writes setting and see if that helps * double check raid controller is configured for writeback (it should, with a bbu) * experiment with xfs on data volume which may help compensate for lousy seeking hardware * try and describe with a little more detail your workload on the primary merlin ---(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] PITR and warm standby setup questions
On Tuesday 13 November 2007 00:07, Greg Smith wrote:
On Mon, 12 Nov 2007, Mason Hale wrote:
After the wal segment file is copied by the restore_command script, is
it safe to delete it from my archive?
While I believe you can toss them immediately, you should considering
keeping those around for a bit regardless as an additional layer of
disaster recovery resources. I try to avoid deleting them until a new
base backup is made, because if you have the last backup and all the
archived segments it gives you another potential way to rebuild the
database in case of a large disaster damages both the primary and the
secondary. You can never have too many ways to try and recover from such
a situation.
Actually I'd more strongly recommend you keep around the last 2 segments you
have processed. Coming out of replay mode (for example, during a failover
scenario) the server often has a desire to reread the last file you
processed, and if you dont have it complains.
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Using generate_series to create a unique ID in a query?
Hello
use temporary sequence instead.
postgres=#create temp sequence a;
CREATE SEQUENCE
postgres=# select nextval('a'), b FROM (values(1),(2),(10),(20)) b(b);
nextval | b
-+
1 | 1
2 | 2
3 | 10
4 | 20
(4 rows)
Regards
Pavel Stehule
On 13/11/2007, Sarah Dougherty [EMAIL PROTECTED] wrote:
Hello,
I am trying to create a view that will contain a generated sequence
(unique ID), and am running into problems doing so.
For some context, I am trying to create a report that provides a list of
client charges and payments and a running balance after each
transaction. Because we often have multiple charges and/or payments on
the same day, we can't use the transaction date to calculate this
balance. Instead, I want to calculate our running balance by assigning
a transaction ID to each transaction a d then having the query sum up
transaction amounts for all transactions with an equal or lower ID.
I can use generate_series to produce a set of IDs, but can't get it to
join properly to the rest of my query. For example, if I had 10 rows in
my query, I would get a series of 1 to 10, but would then get 100 rows
(10x10) in my result. Ultimately the results of this query are going to
be used as a view, so I'd like to avoid creating a temp table, sequence,
etc. Does anyone know how to use generate_series in this manner, or know
of some other way I can go about this? Thanks in advance!
To recap with an example, the query below works fine, but how do I add a
series to it?
SELECT * FROM (
SELECT
client_id,
effective_date AS transaction_date,
amount AS charge_amount,
0 AS payment_amount
FROMcharge
UNION
SELECT
client_id,
payment_date AS transaction_date,
0 as charge_amount,
amount AS payment_amount
FROM payment
) AS tmp
ORDER BY
transaction_date,
charge_amount0 /* order charges before payments */
Thanks,
Sarah Dougherty
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] Accessing a db with pgAdmin
Kent Miller wrote: Background - I am having a problem accessing a remote postgreSQL db from my laptop using an ssh tunnel and pgAdmin. I had postgreSQL loaded on my computer for use as a test db, but removed it. Including deleting the postgeSQL, and pgadmin directories after using the windows applications removal tool. When I reinstall pgAdmin it somehow remembered the previous entries I had for pgadmin? From the registry? Yes, from the registry. I have a previously setup computer with ssh and pgAdmin that can access this remote db just fine. For some reason I cannot get my laptop to access the remote db. I have tried this after shutting down the windows firewall, and norton antivirus (including the worm protection). The pgAdmin tool on my laptop can access a local postgreSQL db just fine. Both my laptop and the computer which can access the remote db are on the same lan, and are passing through the same network firewall. I am not sure what else to try? Any assistance would be greatly appreciated! The error message I get is could not connect to server: Connection refused (0x274D/10061) Is the server running on host 127.0.0.1 and accepting TCP/IP connections on port 5432? This error message means: You are trying to connect to port 5432 on your local computer and nobody is listening there. It seems that you made a connectio attempt to your local computer despite your claim that you are trying to connect to a remote machine. In the pgAdmin III connection properties, you'll have to put the name of the remote computer into the 'Host' field. Can you ping the remote computer? Can you connect to the remote database with psql? Yours, Laurenz Albe ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Calculation for Max_FSM_pages : Any rules of thumb?
On Thu, 2007-11-08 at 12:02 -0500, Bill Moran wrote: Frequently, when people ask for help because they've exceed max_fsm*, it's because they're not paying attention to their systems, and therefore the problem has been occurring for a while before it got so bad that they couldn't ignore it. As a result, a full vacuum is frequently a necessity. Folks who are monitoring their databases closely don't hit this problem nearly as often. How does one monitor it closely anyway? the warning comes when one does a vacuum verbose and with autovacuum turned on, I don't even see it anywhere. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PQexec(), what should I do for the NULL in command problem?
On Tue, Nov 13, 2007 at 10:52:09AM +0800, yang zhenyu wrote: But there exist \0 in some data filed, and I found PQexec() failed on such situation. Thanks for reply :-) But would you please give me more details or some references? Two ways: - out of line parameters How to? Is there any function for this? I mean the C interface. PQexecParams. - escape the nulls, like \0 Do you mean this function? #unsigned char *PQescapeBytea(const unsigned char *from, # size_t from_length, # size_t *to_length); But after the escape, the data is changed, and it cannot automatically change back when insert them into database. I have to unescape it when query. Is this necessary? True. You should realise that the text data type does not handle embedded nulls, that's why the bytea datatype exists. If you don't want the full conversion, you'll need to handle your own escaping. PostgreSQL is not going return you strings with embedded NULLs... Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ Those who make peaceful revolution impossible will make violent revolution inevitable. -- John F Kennedy signature.asc Description: Digital signature
