[GENERAL] upgrading from 8.3 to 9.0
Hi, I'm on the verge of upgrading a server (Fedora 8 ehehe) running postgresql 8.3 It also has postgis 1.3 installed. Thinking of using pgadmin3 to perform the backup and then restore it after I've upgraded the server to fedora 15/16 and thus upgrading postgresql to 9.0. I seem to remember problems with restoring from a pgadmin's .backup file in the past... :S Any pitfalls I should be aware of? Btw: it's a reasonably large DB with 30mil+ rows... Already thankful for any insight, -- Pedro Doria Meunier GSM: +351 91 581 88 23 Skype: pdoriam signature.asc Description: This is a digitally signed message part.
Re: [GENERAL] need some advanced books on Postgres
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I hope postgres will be as popular as linux one day , :) Where have you been all these years?!?!? Postgresql is THE database! humph! On 03/05/2010 10:01 AM, Thomas wrote: Good advice ,tks both of you . For database books ,I found so many good books on Oracle,some on mysql,but db2 and postgres, so few. I have to read some books on Oracle for some advanced topics,although oracle and postgres are different ,I also get some useful info from it . I hope postgres will be as popular as linux one day , :) -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org/ iEYEARECAAYFAkuRi9YACgkQ2FH5GXCfxAuzUwCdEbjESNYg08+VsmC0a0HwAJ24 ASIAn370V4scMH2B4SqemTNLlgEc4pS8 =IpaD -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Cannot login, with C/C++ program
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Did you check you pg_hba.conf ?
another thing: although port defaults to 5432 you *should* specify
that in the connection string.
also... is sysdba *really* an authorized user? check your permissions...
BR,
Pedro.
On 12/02/2009 09:42 PM, Tom Lane wrote:
Daniel danwgr...@gmail.com writes:
I have written a C/C++ program that trys to log into a local
PostgreSQL database. Here is the code:
pg_conn = PQconnectdb(
hostaddr = '127.0.0.1' port = '' dbname = 'TBDB' user = 'sysdba'
password = 'stelmo777' connect_timeout = '10');
if (!pg_conn)
{
return false;
}
if (PQstatus(pg_conn) != CONNECTION_OK)
{
return false; // - execution reaches here.
I checked the database, user and password by logging in with psql.
What could be wrong?
If you print out the connection object's error message, you might
find out. It's hard to guess about it without that information.
regards, tom lane
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org/
iEYEARECAAYFAksW6nMACgkQ2FH5GXCfxAuOBgCeMdssoz+fE26lFX9StTCksUyp
tAkAoKKbnjPrE4fk8HpEbLYJHq3wq7UB
=e3vc
-END PGP SIGNATURE-
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] obtaining ARRAY position for a given match
Regarding this thread... I've been away for a while... But thank you to all who replied! :) BR, Pedro On 11/19/2009 07:03 PM, Sam Mason wrote: On Thu, Nov 19, 2009 at 10:47:02AM -0800, Scott Bailey wrote: Sam Mason wrote: Is idx really the best name for this? Well I used idx() because there was already a idx(int[], int) function with the _int contrib module. I don't remember ever using that before, hence my question. In other languages, it is assumed you are looking for the first index. Huh, they seem to don't they, even my old stalwart of pedantry, Haskell, follows form here. Not sure why I'd never noticed before, idx is looking more and more sensible! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] C++ and Postgis
Hi All, First off sorry for cross-posting. I'm hoping for a bigger audience... :] As for my question: How does one deal with Postgis geom fields in C++ ? Google hasn't offered much help so far. I know I can make a SELECT astext(my_geom) FROM my_table and after parsing the resulting text. This seems a bit awkward and time-consuming... Is there a better way to deal with it? Already thankful for any ideas, BR, Pedro. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] obtaining ARRAY position for a given match
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, I'm trying to get the array position for a given match as thus: This gets me the record for a given match: SELECT * FROM garmin_units WHERE 'L' = ANY (protocol_tag); Ok. so far so good... But what about getting the array position at which 'L' is stored? Searching the Postgresql docs gives me no answer... :( Am I on a wild goose chase? Any insight highly appreciated ;) BR, Pedro Doria Meunier. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org/ iEYEARECAAYFAksFP6IACgkQ2FH5GXCfxAuasgCgu/d68fkg16r1OF/2QSLnmwhW gjYAniyQ1Mn/72323NSznxgakF4dn98k =tWbI -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [pgeu-general] pgday.eu
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Ok a *bit* off-topic here... ;-) But who's this? He reminds me after a programming maraton! :-D Or after a cognac after red wine! :-P Whoever your are... Cheers Mate! ;) BR, Pedro On 11/12/2009 05:24 PM, Dave Page wrote: 2009/11/12 Alvaro Herrera alvhe...@commandprompt.com: Devrim GÜNDÜZ escribió: On Thu, 2009-11-12 at 08:41 +, Dave Page wrote: (whose idea was it to have Cognac after beer, red wine and scotch anyway?) **Perfect** idea -- but it was not me ;) Maybe JD? Hahahahaha. He went back to his hotel early, unable to cope with a European party :-p http://img132.yfrog.com/i/3e9o.jpg/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org/ iEYEARECAAYFAkr8SIoACgkQ2FH5GXCfxAtxpwCgrN7s4a1hGMa2MfBeYQoYREYR ixEAnRWuvs6D1KIB+96ZPUTVDgOzpZ5q =BZI1 -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Posgresql to MSsql encoding problem
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi All, I have this PHP script that simply dumps (inserts) data from a Postgresql server to a customer's M$SQL server. The database in the PGSQL server is UTF-8 encoded where the M$SQL database uses LATIN1. Accents, as expected, turn to gibberish... :( I cannot simply use Set Client Encoding to LATIN1 as the client itself is the script ... :) Is there a simple way to circumvent this? Already thankful for any info, Best regards, Pedro Doria Meunier -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org/ iEYEARECAAYFAkrPEukACgkQ2FH5GXCfxAv9LwCeIsLcr7wwcBTzvXqM/ufYysUK yQ8AniNYuAjQAEktUt7OCS7lZl4kz92f =qBrj -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Posgresql to MSsql encoding problem
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi Sam, Thanks for your answer! :) The matter of fact is that the script itself is the connected client where the postgresql server is concerned. It then connects to another server (the M$SQL one) and, on a row basis, inserts data coming from the aforementioned connection... So, to all effects, the script is *the* client... :) Best regards, Pedro Doria Meunier On 10/09/2009 02:38 PM, Sam Mason wrote: On Fri, Oct 09, 2009 at 11:39:47AM +0100, Pedro Doria Meunier wrote: I cannot simply use Set Client Encoding to LATIN1 as the client itself is the script ... :) What does this mean? surely if it's just a script you can put another line at the beginning of the script that says this? If you really can't do this, how about using ALTER USER to change the the user that they use latin1 by default? -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org/ iEYEARECAAYFAkrPTYQACgkQ2FH5GXCfxAvzSQCfV1tdIq3NSQVN0O8gNfQS1Iok wxMAoIyGe2pQeWz4k3HSaeIEOaT4r49G =/gqA -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [EDIT] Timestamp indicies not being used!
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Thank you Alban for your considerations. The issue has already been marked as [SOLVED] following Sam's suggestion. And FYI the issue *was* urgent and the wolf *was* biting my leg! :] BR, Pedro Doria Meunier GSM: +351 96 17 20 188 Skype: pdoriam Alban Hertroys wrote: On 19 Jul 2009, at 12:39, Pedro Doria Meunier wrote: Hash: SHA1 Hi All, I *really* need some help with this one... I have a table ~18M rows with a 'timestamp with time zone' column. It's indexed thus: CREATE INDEX my_table_timestamp_idx ON my_table USING btree (zulu_timestamp); whenever I issue a command like: SELECT speed, digital_input_1, digital_input_2, digital_input_3, digital_input_4, priority FROM my_table WHERE id='123456789012345' AND zulu_timestamp '2009-07-10 15:24:45+01' ORDER BY zulu_timestamp DESC LIMIT 1 it takes FOREVER in instances where there's only 1 row or 0 rows in the table EDITfor a date equal to the one being compared/EDIT the column id is also indexed. this is the query plan: Limit (cost=0.00..83.88 rows=1 width=20) (actual time=810784.212..810784.212 rows=0 loops=1) - Index Scan Backward using my_table_timestamp_idx on my_table (cost=0.00..3706639.95 rows=44192 width=20) (actual time=810784.210..810784.210 rows=0 loops=1) Index Cond: (zulu_timestamp '2009-07-10 15:24:45+01'::timestamp with time zone) Filter: (id = '123456789012345'::bpchar) Total runtime: 810808.298 ms From this plan it appears the planner statistics aren't up to date or the statistics size on the timestamp column is too small, as the expected number of rows (44192) doesn't match the actual number (0) at all. Some experimenting with ANALYSE and column statistics should tell whether this is indeed the problem. That said statistics are most useful for common cases, they're usually not very accurate for exceptions so playing around with those may not give the desired results. What happens in above query plan is that the planner scans a large part of rows referred to from the timestamp index (namely all those before the specified timestamp) to find any rows matching the id. There are a few things you could do about that: 1) You could specify a lower boundary for the timestamps. The way you're going about it the longer your application runs the more rows will match your zulu_timestamp '2009-07-10 15:24:45+01' expression. It seems likely that you know that the timestamp is at least in 2009-10 for example, reducing the matching rows by a lot once your application is running for several months. 2) You could define a multi-column index instead of two separate indexes. Which column should be first depends on which column you query on most frequently, but I expect it to be (id, zulu_timestamp). With such an index the matching rows are known to be in the index and thus looking them up should be a lot faster. Finally, sending every message as urgent is not going to help you. It's like this: http://en.wikipedia.org/wiki/The_boy_who_cried_wolf Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4a6437be10131991414558! -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (GNU/Linux) Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org iD8DBQFKZDtl2FH5GXCfxAsRAq4BAKCz6J8+ellx1DsaXLeznV6E4z7OkACgqwjK RbZ0c+jvNYD+vxJi2ucneCg= =D6re -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Timestamp indicies not being used!
Hi All, I *really* need some help with this one... I have a table ~18M rows with a 'timestamp with time zone' column. It's indexed thus: CREATE INDEX my_table_timestamp_idx ON my_table USING btree (zulu_timestamp); whenever I issue a command like: SELECT speed, digital_input_1, digital_input_2, digital_input_3, digital_input_4, priority FROM my_table WHERE id='123456789012345' AND zulu_timestamp '2009-07-10 15:24:45+01' ORDER BY zulu_timestamp DESC LIMIT 1 it takes FOREVER in instances where there's only 1 row or 0 rows in the table the column id is also indexed. this is the query plan: Limit (cost=0.00..83.88 rows=1 width=20) (actual time=810784.212..810784.212 rows=0 loops=1) - Index Scan Backward using my_table_timestamp_idx on my_table (cost=0.00..3706639.95 rows=44192 width=20) (actual time=810784.210..810784.210 rows=0 loops=1) Index Cond: (zulu_timestamp '2009-07-10 15:24:45+01'::timestamp with time zone) Filter: (id = '123456789012345'::bpchar) Total runtime: 810808.298 ms Since there are hundreds of devices delivering their data payload to the my_table these queries effectively block other and take postgresql to a screeching halt ... :-( Could someone PLEASE tell me how can I solve this? Thanks in advance, -- Pedro Doria Meunier GSM: +351 96 17 20 188 Skype: pdoriam -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] [EDIT] Timestamp indicies not being used!
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi All, I *really* need some help with this one... I have a table ~18M rows with a 'timestamp with time zone' column. It's indexed thus: CREATE INDEX my_table_timestamp_idx ON my_table USING btree (zulu_timestamp); whenever I issue a command like: SELECT speed, digital_input_1, digital_input_2, digital_input_3, digital_input_4, priority FROM my_table WHERE id='123456789012345' AND zulu_timestamp '2009-07-10 15:24:45+01' ORDER BY zulu_timestamp DESC LIMIT 1 it takes FOREVER in instances where there's only 1 row or 0 rows in the table EDITfor a date equal to the one being compared/EDIT the column id is also indexed. this is the query plan: Limit (cost=0.00..83.88 rows=1 width=20) (actual time=810784.212..810784.212 rows=0 loops=1) - Index Scan Backward using my_table_timestamp_idx on my_table (cost=0.00..3706639.95 rows=44192 width=20) (actual time=810784.210..810784.210 rows=0 loops=1) Index Cond: (zulu_timestamp '2009-07-10 15:24:45+01'::timestamp with time zone) Filter: (id = '123456789012345'::bpchar) Total runtime: 810808.298 ms Since there are hundreds of devices delivering their data payload to the my_table these queries effectively block other and take postgresql to a screeching halt ... :-( Could someone PLEASE tell me how can I solve this? Thanks in advance, - -- Pedro Doria Meunier GSM: +351 96 17 20 188 Skype: pdoriam -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (GNU/Linux) Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org iD8DBQFKYvfr2FH5GXCfxAsRAvvxAKCYDq+diq1IhXVFDaB/N3RLR+VNUACfWDnR H7SXWGXR0yAoXezLpKTJddk= =XH5b -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[SOLVED] Re: [GENERAL] Timestamp indicies not being used!
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 First of all *thank* you very much to all that replied! :) Sam's suggestion actually did the trick! I created the multi-column index and the stalling went away. Yay! Best regards, Pedro Doria Meunier GSM: +351 96 17 20 188 Skype: pdoriam Tom Lane wrote: Andreas Kretschmer akretsch...@spamfence.net writes: Sam Mason s...@samason.me.uk wrote: Based on your query, I think you want a multi-column index---probably on (id,zulu_timestamp). The problem with just having an index on either column is that it's difficult to combine them and PG hence just thinks that it will be Since 8.1 PG can do an bitmap index scan using both indexes... ... which produces an unordered result, so it's pretty useless for this problem. I concur with Sam's suggestion. regards, tom lane -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (GNU/Linux) Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org iD8DBQFKY2hd2FH5GXCfxAsRAjj7AJ4lGNmNuwPUuTv6EwZ8s2faZcOASACgka8P aE4vwzcy3cIdbEyFtYWTjAw= =I5Jv -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] c++ program to connect to postgre database
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi Rey,
If you give QT4 a try I might be able to help you there ;-)
Regards
Pedro Doria Meunier
GSM: +351 96 17 20 188
Skype: pdoriam
ramon rhey serrano wrote:
!-- DIV {margin:0px;} --
Hello,
I'm thinking of making a program in C++ (API) that will connect,
read, and write to a PostgreSQL database. I honestly have no idea
where to start and how to implement it in C++ using Dev C++ IDE.
Any help would be greatly appreciated.
Thanks.
Sincerely,
Rhey
--
New Email addresses available on Yahoo!
http://sg.rd.yahoo.com/aa/mail/domainchoice/mail/signature/*http://mail.promotions.yahoo.com/newdomains/aa/
Get the Email name you've always wanted on the new @ymail and
@rocketmail.
Hurry before someone else does!
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.7 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org
iD4DBQFKVbp+2FH5GXCfxAsRAi0WAKC1LxYxa9TVzOy6/MN7+UA5mYfcfQCYsCvz
LdGheJTQJcWApVisqDeS+w==
=yx4r
-END PGP SIGNATURE-
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] sslv3 alert illegal parameter [SOLVED]
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi Albe, Txs for replying. I did all the necessary mods to the postgresql.conf and put as much info as I could in the logs. The 'culprit' was localhost with no discernible reason whatsoever ... :O Anyway, after exausting all possbilities and learning that openssl 0.9.8b as memory leaks issues :-( the only thing left for me was restarting the server and go figure: it went way on its on! :O So as much as I'd love to know the reason I guess I'll just be happy that the thing resolved itself with a sort of a sword-dangling-over-my-head feeling ... Best regards, Pedro Doria Meunier GSM: +351 96 17 20 188 Skype: pdoriam Albe Laurenz wrote: Pedro Doria Meunier wrote: My server started spitting this out every second! LOG: could not accept SSL connection: sslv3 alert illegal parameter PostgreSQL 8.3.7 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20070925 (Red Hat 4.1.2-33) Server key in place.. What can I do about it?! Several ideas: You say started spitting: that suggests that this is a new phenomenon. What changes have there been since before it started? Every second: Try to find out what tries to connect every second. Set log_connections to on and find out the IP address. Once you have found the culprit, figure out how it tries to connect. Yours, Laurenz Albe -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (GNU/Linux) Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org iD8DBQFKVb6a2FH5GXCfxAsRAmBfAJ46fR1jOrSphHPxL5icYVum/EU1xQCgvTew INQgVjKv4fOXvw7Ic82XNM4= =oR5w -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] sslv3 alert illegal parameter
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi all, My server started spitting this out every second! LOG: could not accept SSL connection: sslv3 alert illegal parameter PostgreSQL 8.3.7 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20070925 (Red Hat 4.1.2-33) Server key in place.. What can I do about it?! TIA, - -- Pedro Doria Meunier GSM: +351 96 17 20 188 Skype: pdoriam -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (GNU/Linux) Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org iD8DBQFKVHAz2FH5GXCfxAsRAixyAJ9m3TxToFE1sNdeDz9SsB9IoU/Q9wCePy3T Yicl2v5RsJ+D424U9L7bKvw= =1j89 -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pgAdmin - no_spool
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Thanks Simon, Actually Dave has already made a patch that overcomes this issue and it's already been committed! BR, Pedro Doria Meunier GSM: +351 96 17 20 188 Skype: pdoriam Simon Riggs wrote: On Tue, 2009-06-30 at 09:27 +0100, Pedro Doria Meunier wrote: Could someone please tell me why when I click on a cluster pgAdmin exhibits a dialog stating: Column not found in pgSet: no_spool ? - - pgAdmin version 1.10.0 Beta2 rev. 7749 - - Master on a remote server - - Slony-I not installed on localhost You need to ask this on the pgAdmin list. http://www.pgadmin.org/support/list.php -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (GNU/Linux) Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org iD8DBQFKTO6X2FH5GXCfxAsRAnKNAKCAo3XAieGNTXPoRpCh27Bnetm1GQCdE18M d0EjQ5jjuFBS0FX1kuWl3/g= =vNuh -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pgAdmin - no_spool
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi All, Could someone please tell me why when I click on a cluster pgAdmin exhibits a dialog stating: Column not found in pgSet: no_spool ? - - pgAdmin version 1.10.0 Beta2 rev. 7749 - - Master on a remote server - - Slony-I not installed on localhost TIA, - -- Pedro Doria Meunier GSM: +351 96 17 20 188 Skype: pdoriam -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (GNU/Linux) Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org iD8DBQFKScxm2FH5GXCfxAsRArT8AJ0cOQok2fGxBjlZkgnY0DKMlUwivwCdHx89 u0d+m9gCvCQJMyo7Yo2hDX8= =vEzG -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Slony-I timezone setting
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hi All,
When configuring a Slony cluster I get the infamous 'ERROR: invalid
input syntax for type timestamp: Mon Jun 29 13:00:36.628805 2009 WEST'
I know that this is a timezone setting issue.
In my case I have my system set to 'Atlantic/Madeira' and UTC.
My postgresql.conf has the same setting ('Atlantic/Madeira')
Postgresql ver. 8.3.7 on Fedora 8
I feel reluctant to follow Slony's 'Best practices' depicted in the
(cough) fine manual as it would mean losing any DST awareness ...
Any thoughts appreciated,
TIA,
- --
Pedro Doria Meunier
GSM: +351 96 17 20 188
Skype: pdoriam
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.7 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org
iD8DBQFKSLaI2FH5GXCfxAsRAkPpAJ48qThWwTWwwIRK802T/Tyn9ztyvgCfWjw3
kkUnMNb1hmKNYZ5dmM04C7U=
=3XOr
-END PGP SIGNATURE-
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Slony-I timezone setting
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Thanks Tom for your thoughts :) I tried what you suggested to no avail :-( Looking at this more closely I see this format when the error arises: Mon Jun 29 15:28:10.952151 2009 WEST Curiously enough this is what the following command throws out of /etc/localtime: zdump -v /etc/localtime | grep 2009 /etc/localtime Sun Mar 29 00:59:59 2009 UTC = Sun Mar 29 00:59:59 2009 WET isdst=0 gmtoff=0 /etc/localtime Sun Mar 29 01:00:00 2009 UTC = Sun Mar 29 02:00:00 2009 WEST isdst=1 gmtoff=3600 /etc/localtime Sun Oct 25 00:59:59 2009 UTC = Sun Oct 25 01:59:59 2009 WEST isdst=1 gmtoff=3600 /etc/localtime Sun Oct 25 01:00:00 2009 UTC = Sun Oct 25 01:00:00 2009 WET isdst=0 gmtoff=0 Even though I'm *sure* that the system's timezone is set to 'Atlantic/Madeira'... This is what's defined in postgresql.conf datestyle = 'iso, ymd' timezone = 'Atlantic/Madeira' BR, Pedro Doria Meunier GSM: +351 96 17 20 188 Skype: pdoriam Tom Lane wrote: Pedro Doria Meunier pdo...@netmadeira.com writes: When configuring a Slony cluster I get the infamous 'ERROR: invalid input syntax for type timestamp: Mon Jun 29 13:00:36.628805 2009 WEST' You need to make the timezone_abbreviations configuration on the slave match that on the master. Alternatively, set datestyle to ISO so that a less ambiguous timestamp format is used. regards, tom lane -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (GNU/Linux) Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org iD8DBQFKSNJs2FH5GXCfxAsRAmCJAKC/WBdfkUmkKUgvBvrSwD0dLOoGmwCcCPb7 UJRvPCTdSKCUkiOoEXf/WmU= =QEO6 -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Slony-I timezone setting
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Tom, This is what I have in '/usr/share/pgsql/timezonesets/Atlantic.txt': WEST 3600 D # Western Europe Summer Time # (Atlantic/Canary) # (Atlantic/Faeroe) # (Atlantic/Madeira) # (Europe/Lisbon) I copied this portion into '/usr/share/pgsql/timezonesets/Default' and restarted the service. Still no go :( I even tried alter user user-slony set timezone='WEST'; After the mods this what the query gives: ERROR: unrecognized time zone name: WEST And this is when I ran out of ideas... btw: do you happen to know of a Slony mailing list? TIA, Pedro Doria Meunier GSM: +351 96 17 20 188 Skype: pdoriam Tom Lane wrote: Pedro Doria Meunier pdo...@netmadeira.com writes: This is what's defined in postgresql.conf datestyle = 'iso, ymd' timezone = 'Atlantic/Madeira' Hmm. WET/WEST are the zone abbreviations for that zone, all right, but I don't understand why they're being emitted if you have that datestyle setting. Maybe something is overriding the datestyle for some dumb reason? Anyway, a look at the default timezone abbrevs file shows that it recognizes WETDST not WEST. You might care to add WEST as an accepted abbrev too. regards, tom lane -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (GNU/Linux) Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org iD8DBQFKSOBE2FH5GXCfxAsRAr+8AJsHvnlpWWZw7rVb2Kp9A70Q4/DJPwCfXbrb L+n1Km17aMA7AzhUe7IqvPs= =THOj -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Optimal values for 500 connections
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi all, I'm wondering what are the correct values in postgresql.conf for max_connections = 500 ... The values I had in 8.2.13 conf file worked without a problem but with the new 8.3.7 postmaster simply refuses to start. Any pointers would be highly appreciated ;-) BR, - -- Pedro Doria Meunier GSM: +351 96 17 20 188 Skype: pdoriam -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (GNU/Linux) Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org iD8DBQFKRRhy2FH5GXCfxAsRAt7KAJ0eiq6dcDyuvK5JpcfLb8atGd9FUACgo1TG G2dZFIB7BzU3bWcGBhikQaE= =YSCi -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Optimal values for 500 connections
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Thank you Scott! As always your advice is right to the point ;-) That solved it. Best regards, Pedro Doria Meunier GSM: +351 96 17 20 188 Skype: pdoriam Scott Marlowe wrote: On Fri, Jun 26, 2009 at 12:52 PM, Pedro Doria Meunierpdo...@netmadeira.com wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi all, I'm wondering what are the correct values in postgresql.conf for max_connections = 500 ... The values I had in 8.2.13 conf file worked without a problem but with the new 8.3.7 postmaster simply refuses to start. Any pointers would be highly appreciated ;-) You likely need to increase max shared mem. With linux that's easy to do with sysctl -a|grep shm to get a list, and then copy and paste with changes to /etc/sysctl.conf. Look them up on the google, or in our docs, some good examples both places. Note that anytime the postmaster doesn't startup it tries to log it somewhere. Look for postmaster logs and such. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (GNU/Linux) Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org iD8DBQFKRR2E2FH5GXCfxAsRAlisAJ940HUpld/VCn8acq773jNQq6WXoACfV1EK lUC1q1tYr0U3g3FT7bii1ZE= =BYKl -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Postgresql 8.3.7 SSL support
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi (again :] ) PGSQL 8.3.7 rpms for x86_64 aren't compiled with ssl support?! I've my server.* files in the data folder but this is what I get: Error connecting to the server: server does not support SSL, but SSL was required BR, - -- Pedro Doria Meunier GSM: +351 96 17 20 188 Skype: pdoriam -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (GNU/Linux) Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org iD8DBQFKRSFT2FH5GXCfxAsRAoCzAJ0aW7ZHXNa/0apD2nWJxULU9m4LTACfZKtb gYPZ6ux5GKd798wGjy53uzI= =HzV1 -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgresql 8.3.7 SSL support
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 (blush) (blush) Sorry for the noise I forgot to ssl=on in postgresql.conf (blush) (blush) Pedro Doria Meunier GSM: +351 96 17 20 188 Skype: pdoriam Pedro Doria Meunier wrote: Hi (again :] ) PGSQL 8.3.7 rpms for x86_64 aren't compiled with ssl support?! I've my server.* files in the data folder but this is what I get: Error connecting to the server: server does not support SSL, but SSL was required BR, -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (GNU/Linux) Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org iD8DBQFKRSOA2FH5GXCfxAsRAssiAKCeK7wOHTbvkcttumHsQk77uGg8gwCfYV99 DpcQ2tGx0WI9HJiCv9ZbfMA= =bJJ2 -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Upgrade
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi all, I've got a somewhat 'hairy' problem in my hands... One of my servers is still Fedora Core (cough) (cough) 6 :] It's running Postgresql 8.2.4 I want it to serve as a last resort, end of the spectrum, backup/slave for another server running Postgresql 8.2.9 via Slony-I. The other server is running Postgresql 8.2.9 over Fedora Core 8. First challenge: I need to upgrade to postgresql 8.3 if I'm to use Slony-I 2.0.2 on both servers. At a glance I feel that upgrading the FC6 server is the hairiest of tasks to be accomplished. Regarding the FC8 (a.k.a. master wannabe): would an upgrade from 8.2.9 to 8.3 require a dump/reload ? The DB size on this server is not that big (~6GB), although a single table has 8.3M rows, meaning that if a dump/reload is involved I'm looking at several hours of out of service (sweat)... Insight on how to tackle the upgrade for these two servers (especially the FC6 one) would be much appreciated! ;-) Thanks in advance. BR. - -- Pedro Doria Meunier GSM: +351 96 17 20 188 Skype: pdoriam -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (GNU/Linux) Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org iD8DBQFKQ2eq2FH5GXCfxAsRAvqkAJwOrlUSI37IdbiaFmnWfkBo4hakkwCeMugm NbYacw6nox6xubq3llNR0xk= =f862 -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Upgrade
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 First of all, thank you all who have replied so far :) The FC8 server has been upgraded to 8.2.13 with minor glitches :) I'm one step shy of upgrading it to 8.3 :P Btw: I'm using the http://yum.pgsqlrpms.org/ repository as the source for the upgrade. After dumping the DB and stopping the server, should I remove all PGSQL rpms via yum and then install the pgdg-fedora-8.3-6.noarch http://yum.pgsqlrpms.org/reporpms/8.3/pgdg-fedora-8.3-6.noarch.rpm and continue on to the upgrade? As far as the older FC6 server is concerned to my great frustration this arch is EOL and there are no upgrade packages available... :-( Perhaps someone is able to conjure up some sort of solution for this? TY, Pedro Doria Meunier GSM: +351 96 17 20 188 Skype: pdoriam Greg Stark wrote: On Thu, Jun 25, 2009 at 1:28 PM, Guy Flahertynaosh...@gmail.com wrote: One of my servers is still Fedora Core (cough) (cough) 6 :] It's running Postgresql 8.2.4 I want it to serve as a last resort, end of the spectrum, backup/slave for another server running Postgresql 8.2.9 via Slony-I. Before doing anything else you should upgrade both of these to 8.2.13. There's no point in running software with known bugs and security holes when there are updates available. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (GNU/Linux) Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org iD8DBQFKQ5fa2FH5GXCfxAsRAhllAJ4rLBABOm0eX8NdYGhNgLn8rybvJQCgoV/Q W1y8JfZlkmrGs72YOPCawZI= =2gPK -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 10 TB database
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello Arthur, We have a database that has a table growing ~1,5M rows each month. The overall growth for the db is ~1GB/month. PostgreSQL 8.2.9 on x86_64 - a very modest Dell R200 with 4GB of ram. Although poor planning was made in the beginning (i.e. no clustering, no partitioning...) - we weren't expecting the boom :] - that particular server runs like clockwork with hundreds of queries per minute and still doing so without any noticeable speed loss. We're, of course, planning for load balancing in the beginning of next year ... :) BR, Pedro Doria Meunier GSM: +351 96 17 20 188 Skype: pdoriam Michelle Konzack wrote: Hi Artur, I am owner of a database about War, Worcrime and Terroism with more then 1,6 TByte and I am already fscked... Am 2009-06-15 14:00:05, schrieb Artur: Hi! We are thinking to create some stocks related search engine. It is experimental project just for fun. The problem is that we expect to have more than 250 GB of data every month. I have only 500 MByte per month... This data would be in two tables. About 50.000.000 new rows every month. arround 123.000 new rows per month We want to have access to all the date mostly for generating user requesting reports (aggregating). We would have about 10TB of data in three years. Do you think is it possible to build this with postgresql and have any idea how to start? :) You have to use a physical cluster like me. Searches in a Database of more then 1 TByte even under using tablespace and tablepartitioning let you run into performance issues... I have now splited my Database in chunks of 250 GByte using a Cluster of 1U Servers from Sun Microsystems. Currently I run 8 servers with one proxy. Each server cost me 2.300 Euro. Note: On Friday I have a meeting with a Sun Partner in Germany because a bigger project... where I have to increase the performance of my database servers. I have to calculate with 150.000 customers. Thanks, Greetings and nice Day/Evening Michelle Konzack Systemadministrator Tamay Dogan Network Debian GNU/Linux Consultant -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (GNU/Linux) Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org iD8DBQFKN13P2FH5GXCfxAsRAkIiAJ95GvbQhBrOglzK2d57F5Qv7E5NdgCfcKga bFpRiWf2vSY0oMOD40PgSsg= =4OB3 -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Average connections
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi all I've googled for an answer but couldn't find one. Is there anyway to monitor the average connections to a database? Thank you in advance for any insights. BR, - -- Pedro Doria Meunier GSM: +351 96 17 20 188 Skype: pdoriam -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (GNU/Linux) Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org iD8DBQFKL4ph2FH5GXCfxAsRAqOBAJwOJ2H4FVKAuIMxFtWasZpdI0cdggCgv2qM 5+JF4AV8VBobs1lVqeIQw9o= =Am3y -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Average connections
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Thank you Bill for your tip. As far as the table's name is concerned the only one I can find is 'pg_statistic' (under pg_catalog). I'm using PGSQL 8.2.9 on x86_64. BR, Pedro Doria Meunier GSM: +351 96 17 20 188 Skype: pdoriam Bill Moran wrote: In response to Pedro Doria Meunier pdo...@netmadeira.com: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi all I've googled for an answer but couldn't find one. Is there anyway to monitor the average connections to a database? Thank you in advance for any insights. The pg_stat_activity table holds 1 row for each connection with information on what that connection is doing. It wouldn't be very difficult to write a passthrough script for something like MRTG to graph this data. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (GNU/Linux) Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org iD8DBQFKL8ND2FH5GXCfxAsRAu/XAJ43UGqlzv5gfzg1YgECbhvL2MaPzwCdEnt3 GfewITsorV/t7cfpq3WxVqM= =84cI -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Average connections
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi all, First of all thank you very much to everyone that shared their views on the task at hand! I've actually accomplished it using MRTG :) BR, Pedro Doria Meunier GSM: +351 96 17 20 188 Skype: pdoriam Bill Moran wrote: In response to Pedro Doria Meunier pdo...@netmadeira.com: Thank you Bill for your tip. As far as the table's name is concerned the only one I can find is 'pg_statistic' (under pg_catalog). I'm using PGSQL 8.2.9 on x86_64. Can't imagine why you're not seeing it. It existed in 8.2, and I don't know of any config setting that would make it not show up. Bill Moran wrote: In response to Pedro Doria Meunier pdo...@netmadeira.com: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi all I've googled for an answer but couldn't find one. Is there anyway to monitor the average connections to a database? Thank you in advance for any insights. The pg_stat_activity table holds 1 row for each connection with information on what that connection is doing. It wouldn't be very difficult to write a passthrough script for something like MRTG to graph this data. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (GNU/Linux) Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org iD8DBQFKL/fv2FH5GXCfxAsRAiFmAKCP5i4lH7g0F/aldAM3+jzRO5KfdACgsU73 2ui5R+4Gb8HPCt+4uO7SH8w= =GTGK -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Internationalization
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Thank you Sam for the valuable input! Best regards, Pedro Doria Meunier GSM: +351 96 17 20 188 Skype: pdoriam Sam Mason wrote: On Sat, Apr 11, 2009 at 02:47:53AM +0100, Pedro Doria Meunier wrote: Actually what I have is a fully internationalized site by means of getttext. *Some* of the content comes from the PGSQL database where 2 tables relation with others (namely for sensor data description). Why not continue using gettext? These tables have the simplest arrangement: id, description :] I wondered if there was some sort of pgsql extension providing a text replacement mechanism of sorts in order to achieve something like gettext ... As with any specialised problem, PG doesn't solve it directly but provides various tools for you to solve it in which ever way is best for you. I guess I'll have to resort to what I've previously thought of ... If, by this, you mean having a column for each language, I'd recommend against doing this. Normalisation is normally the thing to aim for in databases, so something like: CREATE TABLE descriptions ( id INTEGER, lang TEXT, PRIMARY KEY (id,lang) description TEXT ); would generally be considered better. The reason being that this way you don't need to change the database every time somebody wants to translate the software into a new language. If you wanted to maintain compatibility with the rest of the existing code, you could create a view like: CREATE VIEW descriptions AS SELECT id, description FROM descriptions WHERE lang = 'en'; Or whatever language your messages are already in and call the table above something else. Your code can carry on thinking there's a descriptions table (or whatever you call the view) and doesn't need to know that there are other languages available. You can then slowly move the code across to the new version of the table and get rid of the view when you're done. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (GNU/Linux) Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org iD8DBQFJ4LhQ2FH5GXCfxAsRAqKwAJ98yZXXpFyVrQCqa5vMEA40UMDsXQCfcQXB nlxeWHPba/Ab35F2gko4OVs= =YGTU -END PGP SIGNATURE- begin:vcard fn:Pedro Doria Meunier n:Doria Meunier;Pedro org:MGPS - MADEIRA GPS, LDA. adr:;;Portugal email;internet:pdo...@netmadeira.com title:CEO tel;home:+351291603721 tel;cell:+351961720188 version:2.1 end:vcard -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Internationalization
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi all, I'm wondering how to internationalize contents of a table, short of having a column for each language string ... Anyone with some experience to share? :) Regards, Pedro Doria Meunier -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (GNU/Linux) Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org iD8DBQFJ34pd2FH5GXCfxAsRAlbLAJ95Yk0Ab5Zak5B7sRl6ux2ybgrR5ACgl+ZH wB8tgw7sotlCE/bCakW+OC4= =ATNN -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Internationalization
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi Justin, First of all thank you for your input. :) Actually what I have is a fully internationalized site by means of getttext. *Some* of the content comes from the PGSQL database where 2 tables relation with others (namely for sensor data description). These tables have the simplest arrangement: id, description :] I wondered if there was some sort of pgsql extension providing a text replacement mechanism of sorts in order to achieve something like gettext ... I guess I'll have to resort to what I've previously thought of ... Regards, Pedro Doria Meunier. Justin wrote: Pedro Doria Meunier wrote: Hi all, I'm wondering how to internationalize contents of a table, short of having a column for each language string ... Anyone with some experience to share? :) Regards, Pedro Doria Meunier How about parent child table layout. The child table has one record for translation for each document. something like this Create table ParentDoc ( docid serial, description text ) Create table ChildDoc ( docid integer, doc_text text, short_description text, language text ) -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (GNU/Linux) Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org iD8DBQFJ3/bA2FH5GXCfxAsRAlZ0AKCnP9LDOJlOrDSF+Ci4G3CpdX8AlgCdFJqQ 5CrOIuWvgVBXCmyZwhjR8r8= =DmmH -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Accent insensitive search?
Hi, use ILIKE HTH, Pedro Doria Meunier GSM: +351961720188 Skype: pdoriam On Wednesday 18 March 2009 04:29:24 pm cifroes wrote: Hi, I have a DB in utf-8 and postgres 8.3.x. How can I do an accent insensitive search (like ...) ? TIA signature.asc Description: This is a digitally signed message part.
Re: [GENERAL] Accent insensitive search?
Ooops! Silly me! I should have read more carefully ... (blush) sorry! Pedro Doria Meunier GSM: +351961720188 Skype: pdoriam On Wednesday 18 March 2009 04:46:16 pm Pedro Doria Meunier wrote: Hi, use ILIKE HTH, Pedro Doria Meunier GSM: +351961720188 Skype: pdoriam On Wednesday 18 March 2009 04:29:24 pm cifroes wrote: Hi, I have a DB in utf-8 and postgres 8.3.x. How can I do an accent insensitive search (like ...) ? TIA signature.asc Description: This is a digitally signed message part.
Re: [GENERAL] PostgreSQL versus MySQL for GPS Data
Hi Juan, First of all congratulations on you project :) We, at MADEIRA GPS, use Postgresql and PostGIS as the corner stone of our fleet management solution and have tens of *millions* of records in a single vehicles history table without any visible performance problem (we do however clean it every year). A thought, however, regarding your plans for gps data acquisition/storage: every second... isn't that a bit too much? We, for most of our customers, offer minute-by-minute tracking and, this is important, *optimize* the vehicles' history table when writing data into it by means of comparing the data from the last record - i.e. if the info is the same *don't* write it! This will surely save you space ;-) About simultaneous queries: Last we checked we had ~200 of them with PGSQL still pumping at full speed... ;-) As a final note, IMHO, PGSQL/PostGIS is better than MySQL for a number of reasons: - proven robustness - tight integration with PostGIS - large user base (an always friendly bunch willing to help out each other ;-) ) - ... Regards, Pedro Doria Meunier GSM: +351961720188 Skype: pdoriam On Tuesday 17 March 2009 11:25:08 am Juan Pereira wrote: Hello, I'm currently developing a program for centralizing the vehicle fleet GPS information -http://openggd.sourceforge.net-, written in C++. The database should have these requirements: - The schema for this kind of data consists of several arguments -latitude, longitude, time, speed. etc-, none of them is a text field. - The database also should create a table for every truck -around 100 trucks-. - There won't be more than 86400 * 365 rows per table -one GPS position every second along one year-. - There won't be more than 10 simultaneously read-only queries. The question is: Which DBMS do you think is the best for this kind of application? PostgreSQL or MySQL? Thanks in advance Juan Karlos. signature.asc Description: This is a digitally signed message part.
[GENERAL] to_hex leading zeroes
Hi All, I'm in a bit of a pickle here with the to_hex function not returning leading zeroes ... For instance if I: SELECT to_hex(10); it returns 'a' not '0a' . Any ideas on how to overcome this? TIA, Pedro Doria Meunier GSM: +351961720188 Skype: pdoriam signature.asc Description: This is a digitally signed message part.
[GENERAL] COPY ... FROM Permission denied ...
Hi All, This is a bit embarassing ... but ... I have a partial set of data that I want to restore via COPY ... FROM command I have created a public folder for the effect and chown'ed both the folder and the file to be fed into COPY to a+rw ... I switched users with su - postgres and connected to the DB with the psql command All I'm getting is a Permission denied upon issuing the COPY command from within psql interactive terminal! :O So: a) The Christmas *spirits* killed a billion of my brain cells b) I need to go to postgresql kindergarden c) I'm missing something very basic Could someone please lend a hand? Thanks in advance, Pedro Doria Meunier GSM: +351961720188 Skype: pdoriam signature.asc Description: This is a digitally signed message part.
Re: [GENERAL] COPY ... FROM Permission denied ...
Hi Scott Txs for replying. Anyway I've found the problem (silly me... (blush) ) It had to do (of course) with the forest perms in the folder tree ... As soon as I moved the file into the data/ folder and executed the COPY ... FROM feeding it the file from that location everything worked as expected. :] Thanks again. Pedro Doria Meunier GSM: +351961720188 Skype: pdoriam On Tuesday 06 January 2009 06:48:47 pm Scott Marlowe wrote: On Tue, Jan 6, 2009 at 11:41 AM, Pedro Doria Meunier pdo...@netmadeira.com wrote: Hi All, This is a bit embarassing ... but ... I have a partial set of data that I want to restore via COPY ... FROM command I have created a public folder for the effect and chown'ed both the folder and the file to be fed into COPY to a+rw ... I switched users with su - postgres and connected to the DB with the psql command All I'm getting is a Permission denied upon issuing the COPY command from within psql interactive terminal! :O What is the exact error you're getting? It's better to usually use copy from stdin which has none of these problems. It's the same syntax tat pg_dump uses when it creates a backup. For example: COPY b (a_i, b) FROM stdin; 1 moreabc \. signature.asc Description: This is a digitally signed message part.
Re: [GENERAL] Hi iam the beginner
(sorry all, I *couldn't* resist!) Scott ... In all these years that's the 1st time I've ever 'heard' the term massage you data !!! All sorts of deviant thoughts come to your mind! LOL BR, Pedro Doria Meunier GSM: +351961720188 Skype: pdoriam On Friday 19 December 2008 12:03:26 am Scott Marlowe wrote: On Mon, Dec 15, 2008 at 11:02 PM, sai srujan creativesru...@gmail.com wrote: Hi This is srujan and I am beginner of postgresql 8.1 and I have just stopped using mysql and i would like to use the dump (.sql file) created in it. I would like transfer the databases in my sql to postgresql 8.1. Please do reply. I am waiting for it. If possible, go ahead and jump right into 8.3.5. There's been quite a few improvements over 8.1 in it. And unlike Oracle, when a new pg version is released, it's considered to really be production ready right away, or at least within a month or so. 8.3.5 is a year old now, and we've been running 8.3 in production since 8.3.3 came out and it's fantastic. The easiest way to transfer your database over is to dump the schema first, and get that working on pgsql. Beware mysqlisms, like quoting identifiers with back ticks (worst idea ever really) and odd type names, like tinyint or unsigned ints. None of that stuff is standard. Once you've got your schema to import, then dump the data from mysql as either CSV or insert statements, whichever is easier, and import that. You might need to whip up some skills in perl, bash, php, awk, or sed to massage your data to go in right. And if you get stuck, post here... Best of luck. signature.asc Description: This is a digitally signed message part.
[GENERAL] pgadmin3
Hi guys, What happened to pgadmin3 rpms for fedora core 10? Aren't they built yet? BR, Pedro Doria Meunier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Copying data from a table to another database
Dears Craig, Roberts, Reece and Shane Thank you very much for your thoughts. They're helpful! ;-) I've chosen the dump approach to another table in the migration server and then INSERTing the different recs with a WHERE clause for every non-existing timestamp for a deviceID. As far as the pkey was concerned (entry_nbr) it didn't cause me any problems as what's important were the timestamps in the relevant column. Again thanks! Best regards, -- Pedro Doria Meunier [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Copying data from a table to another database
Hi all, This is perhaps a very 'newbie' question but for the life of me, I can't see a way to do it without resorting to programming... (blush) The thing is: I have a table that acts as a historic for some devices activity. Now I've migrated the entire 'shebang' to another server and need to update the history table on the new server with data from the old server for every different record. As the table on the new server grows exponentially (already nearing 1M records) I need to do this asap unless I want the entire thing to take forever... (sweat) Could someone please lend me a hand here? Already thankful for any insight, -- Pedro Doria Meunier [EMAIL PROTECTED] signature.asc Description: This is a digitally signed message part
[GENERAL] default value based on select
Hi, Is it possible to have a table's column default value set to some form of select? e.g. picking another table's value for a given SELECT ... WHERE ... Already thankful for any insight, -- Pedro Doria Meunier Ips. da Olaria, Edf. Jardins do Garajau, 4, r/c Y 9125-162 Caniço Madeira - Portugal -- Skype : pdoriam Mobile: +351961720188 signature.asc Description: This is a digitally signed message part.
Re: [GENERAL] default value based on select
Pavel and Volkan Thank you for your kind (and prompt) replies. Let me try to better explain my needs: The record being created has a column with an Id that has to be passed as an argument to the function that, in turn, fills the other column in the same record. Does this even make sense? :) Kind regards, -- Pedro Doria Meunier Ips. da Olaria, Edf. Jardins do Garajau, 4, r/c Y 9125-162 Caniço Madeira - Portugal -- Skype : pdoriam Mobile: +351961720188 signature.asc Description: This is a digitally signed message part.
Re: [GENERAL] Very slow catalog query
On Monday 31 March 2008 15:13:25 Just Someone wrote: Hi, I have a DB with a large number schemas (around 10K) and a large number of tables (400K). The app became slow lately, and logging the slow queries, I see more than a few like this: SELECT: LOG: duration: 169547.424 ms statement: SELECT attr.attname, name.nspname, seq.relname FROM pg_class seq, pg_attribute attr, pg_depend dep, pg_namespace name, pg_constraint cons WHERE seq.oid = dep.objid AND seq.relnamespace = name.oid AND seq.relkind = 'S' AND attr.attrelid = dep.refobjid AND attr.attnum = dep.refobjsubid AND attr.attrelid = cons.conrelid AND attr.attnum = cons.conkey[1] AND cons.contype = 'p' AND dep.refobjid = 'activities'::regclass Almost all slow queries are of this type, though most of those do finish really fast. From time to time it gets really slow. Some details on the setup: Dual Opteron with 4GB RAM RAID1 for WAL on 10K SCSI RAID10 over 6 x 10K scsi drives for main the rest for the DB files Auto vaccum is on, and in addition I do some vacuuming for specific high use tables nightly Any ideas how to start finding the culprit? Bye, Guy. -- Family management on rails: http://www.famundo.com My development related blog: http://devblog.famundo.com Hi 'Just Someone' I'm wondering... just 4GB of ram? What's the normal hammering -- a.k.a. user access -- to all of this? PG, as expected, launches a separate process for each connection. this eats up resources quite quickly Did you check your system processes with 'top' ? how's it looking for swap usage? Regards, -- Pedro Doria Meunier Ips. da Olaria, Edf. Jardins do Garajau, 4, r/c Y 9125-162 Caniço Madeira - Portugal -- Skype : pdoriam Mobile: +351961720188 signature.asc Description: This is a digitally signed message part.
Re: [GENERAL] thank you
I second that! A BIG THANK YOU for you Postgresql folks out there and since the season is up: Merry Christmas and a Happy New Year! On Tue, 2007-12-18 at 21:26 -0500, Kevin Hunter wrote: Hullo List, This is aimed at everyone in this community who contributes to the Postgres project, but especially at the core folks who continually make this community great through energy, time, money, responses, and what-have-you. I see lots of Thank yous go by for this problem or that conundrum solved, but I don't think I've seen a general thank you for all that all y'all do. (If I've missed them in the blur of emails I get, I do apologize.) Thank you for all that you do. Thank you for the countless times you've saved my butt from my own stupidity. Thank you for the absolute awesome help you've been while tracking down bugs. Thank you for signing the NDA without fuss and searching my DB for a bug that I may have caused. Thank you for answering my frustrated emails at some ungodly hour of the morning (do you sleep?!). Thank you for engaging others in discussion, sometimes beyond the point of annoyance ... your enthusiasm and love for the project is duly noted. Thank you for constantly preaching the right way. Thank you . . . well you get the drift. I'll stop before this turns into some chain-letter type ordeal. The point is that I hope you realize just how much you all mean to the community. Kevin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ -- Pedro Doria Meunier Ips da Olaria Edf. Jardins do Garajau, 4 r/c Y 9125-163 Caniço Madeira Portugal GSM: +351 96 17 20 188 Skype: pdoriam http://www.madeiragps.com signature.asc Description: This is a digitally signed message part
[GENERAL] Slony replication
Hi All, I would like to implement DB replication with Slony but with a slave that will not be always available. The master would have to check first of the slave's availability and then start syncing... Is this at all possible, or am I just raving? : ) Kind regards, -- Pedro Doria Meunier Ips da Olaria Edf. Jardins do Garajau, 4 r/c Y 9125-163 Caniço Madeira Portugal GSM: +351 96 17 20 188 Skype: pdoriam http://www.madeiragps.com signature.asc Description: This is a digitally signed message part
[GENERAL] test message
Sorry People, this is a test message as it seems mail I'm writing to this list isn't going through-- I just want to see if I get a rebound of it... Please apologize. -- Pedro Doria Meunier Ips da Olaria Edf. Jardins do Garajau, 4 r/c Y 9125-163 Caniço Madeira Portugal GSM: +351 96 17 20 188 Skype: pdoriam http://www.madeiragps.com signature.asc Description: This is a digitally signed message part
[GENERAL] problems with email...
Hi. I seem to have *extreme* problems sending messages to this list... :-( Could one of the people responsible for the list see if anything is wrong with my account, please? Thanks! -- Pedro Doria Meunier Ips da Olaria Edf. Jardins do Garajau, 4 r/c Y 9125-163 Caniço Madeira Portugal GSM: +351 96 17 20 188 Skype: pdoriam http://www.madeiragps.com
[GENERAL] Help needed optimizing query
Hi People. I need some help optimizing this query: SELECT u.friendly_name, distance(transform(u.curr_location,32628), rc.agg_geometry) AS thedistance, u.mobile FROM (SELECT transform(collect(r.geometry),32628) AS agg_geometry, t.county FROM pt_madeira_roads r, pt_madeira_toponymy t WHERE r.name ILIKE '%salão são roque%' AND t.county='Funchal' AND r.geometry t.geometry GROUP BY t.county) rc, units AS u WHERE expand(rc.agg_geometry,1000) transform(u.curr_location,32628) AND u.customer_id='14' AND (u.even_code='A' OR u.even_code='B') ORDER BY thedistance LIMIT 10; Total query runtime: 1578 ms. 16 rows retrieved. this is the explain analyze output: Limit (cost=93.92..93.93 rows=1 width=78) - Sort (cost=93.92..93.93 rows=1 width=78) Sort Key: distance(transform(u.curr_location, 32628), rc.agg_geometry) - Nested Loop (cost=85.59..93.91 rows=1 width=78) Join Filter: (expand(rc.agg_geometry, 1000::double precision) transform(u.curr_location, 32628)) - HashAggregate (cost=85.59..85.60 rows=1 width=226) - Nested Loop (cost=0.00..85.58 rows=1 width=226) - Seq Scan on pt_madeira_toponymy t (cost=0.00..2.74 rows=10 width=15770) Filter: (county = 'Funchal'::text) - Index Scan using pt_madeira_roads_idx on pt_madeira_roads r (cost=0.00..8.27 rows=1 width=213) Index Cond: (r.geometry t.geometry) Filter: ((r.name ~~* '%salão são roque %'::text) AND (r.geometry t.geometry)) - Index Scan using units_customerID_idx on units u (cost=0.00..8.28 rows=1 width=46) Index Cond: (customer_id = 14::bigint) Filter: (((even_code)::text = 'A'::text) OR ((even_code)::text = 'B'::text)) I still get Seq Scans although all used fields are indexed, hence the time used... :-( Although this is almost 'targeted' to the beautiful SQL head of Regina's any input would be extremely appreciated... ;-) Thank you! -- Pedro Doria Meunier Ips da Olaria Edf. Jardins do Garajau, 4 r/c Y 9125-163 Caniço Madeira Portugal GSM: +351 96 17 20 188 Skype: pdoriam http://www.madeiragps.com signature.asc Description: This is a digitally signed message part
Re: [GENERAL] [pgadmin-support] Problem editing tables (geom columns)
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 (First of all sorry for cross-posting but I feel this is a matter that interests all recipients) Thread on pgadmin support: http://www.pgadmin.org/archives/pgadmin-support/2007-06/msg00046.php Hello Dave, This behavior (trying to show the entire geometry field) in Fedora Core 6, pgAdmin3 1.6.2, doesn't happen... In that scenario the geom field is simply shown blank. Nevertheless, if I recall it correctly, proj4, geos, postgis versions, in the above scenario, were older than the ones I'm using... So I wonder... could it be that pgadmin's code changed for showing large fields? Could one of proj4, geos, postgis code changed that really interferes with pgadmin3? IMHO, regardless of the scenario involved, the output for large geom fields should be suppressed in table edition... (as seen in the past) The current behavior hogs way too much processor time. Dave I've tested with a micro-subset of my data (only one record with a small parish geometry) and it shows although the slowness is immediately apparent... /Dave Kind regards, Pedro Doria Meunier Dave Page wrote: Dave Page wrote: Pedro Doria Meunier wrote: I've seen in the known issues for ver 1.6.3 that there's a problem with editing tables with long fields... Where do you see that? Strangely enough I've used this version before with Fedora Core 6 and there wasn't a problem with postgis tables :$ A long work has already been done setting up Fedora 7 to my taste so I'm really not too keen on downgrading to FC6... :-( So, I'm a bit lost here... Is this a GTK issue? What can I do to fix this? Can you supply me a sample table and data to test with please? I've been playing with the data that Pedro supplied me offlist, and the problem is basically that he has a geometry value (basically a string) in a column of around 4MB. I think it's fairly safe to say that we'd be lucky to find that any of the grid controls on any of the platforms we support were happy with this amount of data in a single cell - in testing on Windows for example, whilst it works, it does slow to a crawl. I think the only sensible option would be to add an additional tab to the sort/filter dialog to allow the data to be vertically partitioned to exclude such columns. This isn't going to happen for the next release though I'm afraid. Thoughts? Regards, Dave. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (GNU/Linux) Comment: Using GnuPG with Remi - http://enigmail.mozdev.org iD8DBQFGeWFf2FH5GXCfxAsRArySAKCeVIK5uzDEs+Q6ZS0A2Jye6c5h0ACeKlkf MqNA+rBORwi5Ko2x/rRV+Cc= =rOET -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] pgadmin3 1.6.3 problem with geom fields
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi All, I've installed pgadmin3 1.6.3 from rpm, under Fedora 7 It runs ok, *except* when the tables have geometry fields! :O When one tries to open/view the table it takes **forever** to display the table's records! (?) This is what I get when ps aux: doria 21930 89.7 20.9 672256 434116 pts/2 R+ 20:28 1:53 pgadmin3 ^ | Look at this processor hogging! It's becoming apparent that besides (perhaps) having something to do with (either) geos, proj, postgis, it might also something to do with gtk+, wxWidgets btw: I compiled geos-3.0.0rc4, proj-4.5.0, postgis-1.2.1 from source My system has wxGTK-2.8.3-2.fc7 installed. Some testing: Some tables (with geom fields) do show after some time. trying to scroll the list of records freezes pgadmin, however... I've tried to downgrade pgadmin3 1.6.3 to 1.6.2 and it exhibits the same behaviour... Any thoughts highly appreciated since I rely heavily on pgadmin3 for my work! :) Kind regards, Pedro Doria Meunier -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (GNU/Linux) Comment: Using GnuPG with Remi - http://enigmail.mozdev.org iD8DBQFGdVZW2FH5GXCfxAsRAsDvAJ9P8QnzN/ebOzV+aQWVzIEZqPy+8QCcDZBh GRrKkEP91pUtu3pd2XeZCrU= =XMIa -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] help with a query
Hi all! This is most certainly a lame question but perhaps someone is gracious enough to lend me a hand ;-) I have the following setup in a table: The first record which is to be found (ok easy enough :D) with a timestamp meets a certain condition (ignition off) The following record is for the event of ignition on again with a timestamp. So the question here is: how can I compute the time difference between these two records in a single query? Better yet: finding all the records that meet the first condition (ignition off) and the immediately following records as to compute the time difference. ;-) Thanks in advance! Pedro Doria Meunier (351) 91 302 49 72 - (351) 96 247 99 12 [EMAIL PROTECTED] MSN - [EMAIL PROTECTED] ICQ - 308-182-126 Skype: pdoriam
