[GENERAL] Cannot store special chars using c lib
Hi, I need to be able to store special chars, German Umlaute, in my tables. This works when using pgPHPAdmin to store the same value to the same field. But when using the c-library it doesn't, fields stored are garbled. I checked using \l to see what encoding the database is which is UTF8, UTF8 is what's required. List of databases Name| Owner | Encoding | Collation | Ctype | Access privileges ---+--+--+---+---+--- MyDB | postgres | UTF8 | C | C | alltypes | postgres | UTF8 | de_DE | C | postgres | postgres | UTF8 | C | C | template0 | postgres | UTF8 | C | C | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | C | C | =c/postgres + | | | | | postgres=CTc/postgres As a way to store things I use PGSQLKit, which in turn uses const char *cString = [sql cStringUsingEncoding:defaultEncoding]; if (cString == NULL) res = PQexec(pgconn, cString); to store things. The defaultEncoding I also changed explicitly from defaultEncoding to UTF8 to try, but got the same result. As far as I can see this is not an error on part of the PGSQLKit. From what I read there is no table specific encoding. The collation is set to C, but that's something only relevant to sorting as far s I understand. So, I am at a loss as to where things go wrong. Any ideas? Thanks
Re: [GENERAL] Cannot store special chars using c lib
Sorry, there is a copy-paste error, actually the code really is:
const char *cString = [sql cStringUsingEncoding:defaultEncoding];
if (cString == NULL) {
blablabla
//This just catches cases where cString failed to encode.
}
res = PQexec(pgconn, cString);
Am 22.03.2012 um 09:02 schrieb Alexander Reichstadt:
Hi,
I need to be able to store special chars, German Umlaute, in my tables. This
works when using pgPHPAdmin to store the same value to the same field. But
when using the c-library it doesn't, fields stored are garbled.
I checked using \l to see what encoding the database is which is UTF8, UTF8
is what's required.
List of databases
Name| Owner | Encoding | Collation | Ctype | Access privileges
---+--+--+---+---+---
MyDB | postgres | UTF8 | C | C |
alltypes | postgres | UTF8 | de_DE | C |
postgres | postgres | UTF8 | C | C |
template0 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
As a way to store things I use PGSQLKit, which in turn uses
const char *cString = [sql cStringUsingEncoding:defaultEncoding];
if (cString == NULL)
res = PQexec(pgconn, cString);
to store things. The defaultEncoding I also changed explicitly from
defaultEncoding to UTF8 to try, but got the same result. As far as I can see
this is not an error on part of the PGSQLKit.
From what I read there is no table specific encoding.
The collation is set to C, but that's something only relevant to sorting as
far s I understand.
So, I am at a loss as to where things go wrong.
Any ideas?
Thanks
Re: [GENERAL] Slow information_schema.views
Oliver Kohll - Mailing Lists wrote: I'm doing some SELECTs from information_schema.views to find views with dependencies on other views, i.e. SELECT table_name FROM information_schema.views WHERE view_definition ILIKE '%myviewname%'; and each is taking about 1/2 a second, which is getting a bit slow for my use. There are 1213 views listed in information_schema.views Doing an explain analyze, it looks like the issue is likely to be the pg_get_viewdef function or one of the privilege check functions. I'm not worried about privilege checks and I don't need a nicely formatted definition. Is there a way of finding out how pg_get_viewdef works so I can perhaps do a lower level query? I've previously used pg_catalog.pg_views which performs similarly. Or is there a better way of finding view dependencies? I see there's a pg_catalog entry for tables that a view depends on but that's not what I'm after. You can use pg_depend and pg_rewrite as follows: SELECT DISTINCT r.ev_class::regclass FROM pg_depend d JOIN pg_rewrite r ON (d.objid = r.oid) WHERE d.classid = 'pg_rewrite'::regclass AND d.refclassid = 'pg_class'::regclass AND r.ev_class d.refobjid AND d.refobjid::regclass::text LIKE '%myviewname%'; I didn't test it very much, so play around with it a little before you trust it. I don't know if it will perform better in your case, but it should return more appropriate results (you don't want to find VIEW dummy AS SELECT * FROM t WHERE a = 'myviewname'). Yours, Laurenz Albe -- 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] Slow information_schema.views
On 22 Mar 2012, at 10:17, Albe Laurenz wrote: Or is there a better way of finding view dependencies? I see there's a pg_catalog entry for tables that a view depends on but that's not what I'm after. You can use pg_depend and pg_rewrite as follows: SELECT DISTINCT r.ev_class::regclass FROM pg_depend d JOIN pg_rewrite r ON (d.objid = r.oid) WHERE d.classid = 'pg_rewrite'::regclass AND d.refclassid = 'pg_class'::regclass AND r.ev_class d.refobjid AND d.refobjid::regclass::text LIKE '%myviewname%'; I didn't test it very much, so play around with it a little before you trust it. I don't know if it will perform better in your case, but it should return more appropriate results (you don't want to find VIEW dummy AS SELECT * FROM t WHERE a = 'myviewname'). Yours, Laurenz Albe Thank you - I did come to a similar method yesterday following some pointers from previous messages but I'm glad to have some confirmation it's the right direction. It does perform an order of magnitude faster for me, from 500ms down to under 20ms. My exact query is SELECT distinct dependent.relname FROM pg_depend JOIN pg_rewrite ON pg_depend.objid = pg_rewrite.oid JOIN pg_class as dependent ON pg_rewrite.ev_class = dependent.oid JOIN pg_class as dependee ON pg_depend.refobjid = dependee.oid WHERE dependee.relname = 'myviewname' AND dependent.relname != 'myviewname' Haven't tested this much yet either. I'll compare yours to mine and check the differences. Regards Oliver www.agilebase.co.uk
Re: [GENERAL] Cannot store special chars using c lib
Hi,
I found out that apparently in PGSQLKit there is an error in PQescapeStringConn
or the way it is being used.
From the docu I take it this is to prevent SQL injection attacks. I removed
any processing and it turned out the issue ceases, all works fine.
The call is here:
-(NSString *)sqlEncodeString:(NSString *)toEncode
{
return toEncode;
//size_t result;
int error;
char *sqlEncodeCharArray = malloc(1 + ([toEncode length] * 2)); // per
the libpq doc.
const char *sqlCharArrayToEncode = [toEncode
cStringUsingEncoding:defaultEncoding];
size_t length = strlen(sqlCharArrayToEncode);
PQescapeStringConn ((PGconn *)pgconn, sqlEncodeCharArray,
(const char *)[toEncode
cStringUsingEncoding:defaultEncoding],
length, error);
NSString *encodedString = [[[NSString alloc]
initWithFormat:@%s,sqlEncodeCharArray] autorelease];
free(sqlEncodeCharArray);
return encodedString;
}
This indicates that the problem is in PGSQLKit and not in any settings for
tables or the database itself. So I take it to the Cocoa list at Apple.
Thanks and regards
Alex
Am 22.03.2012 um 09:06 schrieb Alexander Reichstadt:
Sorry, there is a copy-paste error, actually the code really is:
const char *cString = [sql cStringUsingEncoding:defaultEncoding];
if (cString == NULL) {
blablabla
//This just catches cases where cString failed to encode.
}
res = PQexec(pgconn, cString);
Am 22.03.2012 um 09:02 schrieb Alexander Reichstadt:
Hi,
I need to be able to store special chars, German Umlaute, in my tables. This
works when using pgPHPAdmin to store the same value to the same field. But
when using the c-library it doesn't, fields stored are garbled.
I checked using \l to see what encoding the database is which is UTF8, UTF8
is what's required.
List of databases
Name| Owner | Encoding | Collation | Ctype | Access privileges
---+--+--+---+---+---
MyDB | postgres | UTF8 | C | C |
alltypes | postgres | UTF8 | de_DE | C |
postgres | postgres | UTF8 | C | C |
template0 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
As a way to store things I use PGSQLKit, which in turn uses
const char *cString = [sql cStringUsingEncoding:defaultEncoding];
if (cString == NULL)
res = PQexec(pgconn, cString);
to store things. The defaultEncoding I also changed explicitly from
defaultEncoding to UTF8 to try, but got the same result. As far as I can see
this is not an error on part of the PGSQLKit.
From what I read there is no table specific encoding.
The collation is set to C, but that's something only relevant to sorting as
far s I understand.
So, I am at a loss as to where things go wrong.
Any ideas?
Thanks
Re: [GENERAL] Cannot store special chars using c lib
Alexander Reichstadt wrote: I need to be able to store special chars, German Umlaute, in my tables. This works when using pgPHPAdmin to store the same value to the same field. But when using the c-library it doesn't, fields stored are garbled. I checked using \l to see what encoding the database is which is UTF8, UTF8 is what's required. List of databases Name| Owner | Encoding | Collation | Ctype | Access privileges ---+--+--+---+---+--- MyDB | postgres | UTF8 | C | C | alltypes | postgres | UTF8 | de_DE | C | postgres | postgres | UTF8 | C | C | template0 | postgres | UTF8 | C | C | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | C | C | =c/postgres + | | | | | postgres=CTc/postgres As a way to store things I use PGSQLKit, which in turn uses const char *cString = [sql cStringUsingEncoding:defaultEncoding]; if (cString == NULL) res = PQexec(pgconn, cString); to store things. The defaultEncoding I also changed explicitly from defaultEncoding to UTF8 to try, but got the same result. As far as I can see this is not an error on part of the PGSQLKit. From what I read there is no table specific encoding. The collation is set to C, but that's something only relevant to sorting as far s I understand. So, I am at a loss as to where things go wrong. Any ideas? I know nothing about PGSQLKit, but you should check what client_encoding is set to. If it is set to something else than UTF8, say for example LATIN1, then PostgreSQL will happily interpret the bytes in your UTF8 string as LATIN1 and convert them to UTF8, resulting in things like 'schöne ScheiÃ\u009Fe'. Yours, Laurenz Albe -- 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 store special chars using c lib
Thanks, Albe, I had checked this, too, and it was ok. I already posted the solution to the board. It was an error due to an incorrect conversion between an object-instance and a char. It works now. Am 22.03.2012 um 12:50 schrieb Albe Laurenz: Alexander Reichstadt wrote: I need to be able to store special chars, German Umlaute, in my tables. This works when using pgPHPAdmin to store the same value to the same field. But when using the c-library it doesn't, fields stored are garbled. I checked using \l to see what encoding the database is which is UTF8, UTF8 is what's required. List of databases Name| Owner | Encoding | Collation | Ctype | Access privileges ---+--+--+---+---+--- MyDB | postgres | UTF8 | C | C | alltypes | postgres | UTF8 | de_DE | C | postgres | postgres | UTF8 | C | C | template0 | postgres | UTF8 | C | C | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | C | C | =c/postgres + | | | | | postgres=CTc/postgres As a way to store things I use PGSQLKit, which in turn uses const char *cString = [sql cStringUsingEncoding:defaultEncoding]; if (cString == NULL) res = PQexec(pgconn, cString); to store things. The defaultEncoding I also changed explicitly from defaultEncoding to UTF8 to try, but got the same result. As far as I can see this is not an error on part of the PGSQLKit. From what I read there is no table specific encoding. The collation is set to C, but that's something only relevant to sorting as far s I understand. So, I am at a loss as to where things go wrong. Any ideas? I know nothing about PGSQLKit, but you should check what client_encoding is set to. If it is set to something else than UTF8, say for example LATIN1, then PostgreSQL will happily interpret the bytes in your UTF8 string as LATIN1 and convert them to UTF8, resulting in things like 'schöne ScheiÃ\u009Fe'. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Help in Parsing PG log usings CSV format
Help needed in parsing PostgreSQL CSV Log Hello friends, I am working an a section of application which needs to Parse CSV Logs generated by PostgreSql server. - The Logs are stored C:\Program Files\PostgreSQL\9.0\data\pg_log - The Server version in 9.0.4 - The application is developed in C Sharp * The basic utility after Parse the Log is to show contents in a DataGridView. * There are other filter options like to view log contents for a particular range of Time for a Day. **However the main problem that is, the Log format is not readable** A Sample Log data line 2012-03-21 11:59:20.640 IST,postgres,stock_apals,3276,localhost:1639,4f697540.ccc,10,idle,2012-03-21 11:59:20 IST,2/163,0,LOG,0,statement: SELECT version()exec_simple_query, .\src\backend\tcop\postgres.c:900, As you can see the columns in the Log are comma separated , But however individual values are not Quote Enclosed. For instance the 1st,4rth,6th .. columns **Is there a setting in PostgreSQL configuration to place quotes around all columns in a Logfili I just want to update the columns so that all are within Quotes what happens wrong is when it reaches the column where sql statement is place. it also has commas set for table columns. The log line is a mix bunch of quote-enclosed and non-quote-enclosed column. is there is a configuration or utility to convert the non-quoted column to quoted column PS : the copy utility or any other utility cannot be used , as we have to parse the log within a C Sharp application Thanks for any advice and help
[GENERAL] Very high memory usage on restoring dump (with plain pqsl) on pg 9.1.2
Hey! On a host that I'm currently in the process of migrating, I'm experiencing massive memory usage when importing the dump (generated using a plain pg_dump without options) using psql. The massive memory usage happens when the CREATE INDEX commands are executed, and for a table with about 4G of data (traffic rows in pmacct format) in it, I'm seeing the respective (single!) PostgreSQL-process jump to around 40GB (VIRT, RES stays at 24GB, which is the systems actual memory), before the process is duly killed by the OOM-killer of the system (due to running out of swap...). I've checked the corresponding parameters (maintenance_work_mem) which I guess influences the process growth in this case, and they are default (i.e., 16MB). Is this expected/known behaviour? Does this have anything to do with the fact that the dump comes from a PostgreSQL 8.3.x? Thanks for any hint! -- --- Heiko. -- 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] Help in Parsing PG log usings CSV format
On Thu, 2012-03-22 at 09:32 +, Arvind Singh wrote: Help needed in parsing PostgreSQL CSV Log Hello friends, I am working an a section of application which needs to Parse CSV Logs generated by PostgreSql server. - The Logs are stored C:\Program Files\PostgreSQL\9.0\data\pg_log - The Server version in 9.0.4 - The application is developed in C Sharp * The basic utility after Parse the Log is to show contents in a DataGridView. * There are other filter options like to view log contents for a particular range of Time for a Day. **However the main problem that is, the Log format is not readable** A Sample Log data line 2012-03-21 11:59:20.640 IST,postgres,stock_apals,3276,localhost:1639,4f697540.ccc,10,idle,2012-03-21 11:59:20 IST,2/163,0,LOG,0,statement: SELECT version()exec_simple_query, .\src\backend\tcop\postgres.c:900, As you can see the columns in the Log are comma separated , But however individual values are not Quote Enclosed. For instance the 1st,4rth,6th .. columns Thats fairly standard. A good CSV parser only requires a field to be quoted if it contains commas or quotes. In the latter case the internal quotes should be doubled, i.e the three fields in the following: unquoted field,contains commas, etc.,Fred said Cool!. should be handled correctly by a decent CSV parser. Martin -- 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] Large PostgreSQL servers
On Wed, Mar 21, 2012 at 2:31 PM, Kjetil Nygård polpo...@gmail.com wrote: Hi, We are considering to migrate some of our databases to PostgreSQL. We wonder if someone could give some hardware / configuration specs for large PostgreSQL installations. We're interested in: - Number of CPUs - Memory on the server - shared_buffers - Size of the database on disk PS: I have read in PosgreSQL 9.0 High Performance that one should not use more than 8GB for shared_buffers. But Robert Haas and comments say that one can use a lot more. http://rhaas.blogspot.com/2012/03/tuning-sharedbuffers-and-walbuffers.html If your database (or at least, the portion of it that sees regular activity) fits completely in shared_buffers, it's a win because they are faster than the o/s filesystem cache and they don't have to get paged in and out. OTOH, if your database does not fit, you can get performance issues relating to them getting pushed in and out. Another disadvantage of large shared buffers settings is it reduces the amount of memory for other things, like temporary demands (sorts, large result sets) or cached structures like plpgsql plans. Once you go over 50% memory into shared, it's pretty easy to overcommit your server and burn yourself. Of course, 50% of 256GB server is a very different animal than 50% of a 4GB server. Here's the takeaway for shared_buffers. *) it's a nuanced setting. for single user workloads its affects are usually undetectable *) it's more important for high write activity workloads. for low user high read olap type workloads, I usually set it lower, perhaps even to 256mb -- it doesn't help all that much and i'd rather have that memory be on demand for the o/s *) don't be afraid to buck the conventional wisdom if you're not seeing the performance you think you should be getting (especially on writes). higher or lower shared_buffers can work *) lots of other variables are at play -- o/s page flush policy for example. *) it's unclear right now what the upcoming revolution in faster storage means for database configuration and tuning. my gut feeling is that it's going to be generally less important as databases become primarily cpu,lock, and algorithm (query plan) bound. *) beware memory over commit. merlin -- 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] Very high memory usage on restoring dump (with plain pqsl) on pg 9.1.2
Heiko Wundram modeln...@modelnine.org writes: On a host that I'm currently in the process of migrating, I'm experiencing massive memory usage when importing the dump (generated using a plain pg_dump without options) using psql. The massive memory usage happens when the CREATE INDEX commands are executed, What PG version are we talking about, and what exactly is the problematic index? There was a memory leak in the last-but-one releases for index operations on inet and cidr datatypes, so I'm wondering if that explains your problem ... regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Large PostgreSQL servers
On Thu, Mar 22, 2012 at 8:46 AM, Merlin Moncure mmonc...@gmail.com wrote: large result sets) or cached structures like plpgsql plans. Once you go over 50% memory into shared, it's pretty easy to overcommit your server and burn yourself. Of course, 50% of 256GB server is a very different animal than 50% of a 4GB server. There's other issues you run into with large shared_buffers as well. If you've got a large shared_buffers setting, but only regularly hit a small subset of your db (say 32GB shared_buffers but only hit 4G or so regularly in your app) then it's quite possible that older shared_buffer segments will get swapped out because they're not being used. Then, when the db goes to hit a page in shared_buffers, the OS will have to swap it back in. What was supposed to make your db much faster has now made it much slower. With Linux, the OS tends to swap out unused memory to make room for file buffers. While you can change the swappiness settings to 0 to slow it down, the OS will eventually swap out the least used segments anyway. The only solution on large memory servers is often to just turn off swap. -- 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] Large PostgreSQL servers
On Thu, Mar 22, 2012 at 10:02 AM, Scott Marlowe scott.marl...@gmail.com wrote: On Thu, Mar 22, 2012 at 8:46 AM, Merlin Moncure mmonc...@gmail.com wrote: large result sets) or cached structures like plpgsql plans. Once you go over 50% memory into shared, it's pretty easy to overcommit your server and burn yourself. Of course, 50% of 256GB server is a very different animal than 50% of a 4GB server. There's other issues you run into with large shared_buffers as well. If you've got a large shared_buffers setting, but only regularly hit a small subset of your db (say 32GB shared_buffers but only hit 4G or so regularly in your app) then it's quite possible that older shared_buffer segments will get swapped out because they're not being used. Then, when the db goes to hit a page in shared_buffers, the OS will have to swap it back in. What was supposed to make your db much faster has now made it much slower. With Linux, the OS tends to swap out unused memory to make room for file buffers. While you can change the swappiness settings to 0 to slow it down, the OS will eventually swap out the least used segments anyway. The only solution on large memory servers is often to just turn off swap. Right -- but my take on that is that hacking the o/s to disable swap is dealing with symptoms of problem related to server misconfiguration. In particular it probably means shared_buffers is set too high...the o/s thinks it needs that memory more than you do and it may very well be right. The o/s doesn't swap for fun -- it does so when there are memory pressures and things are under stress. Generally, unused memory *should* get swapped out...of course there exceptions for example if you want zero latency access to an important table that is only touched once a day. But those cases are pretty rare. On systems with very fast storage (ssd), removing swap is even more unreasonable -- the penalty for going to storage is less and the server could use that memory for other things. merlin -- 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] Help in Parsing PG log usings CSV format
Arvind Singh wrote: Help needed in parsing PostgreSQL CSV Log [...] **However the main problem that is, the Log format is not readable** A Sample Log data line 2012-03-21 11:59:20.640 IST,postgres,stock_apals,3276,localhost:1639,4f697540.ccc,10,idle ,2012-03-21 11:59:20 IST,2/163,0,LOG,0,statement: SELECT version()exec_simple_query, .\src\backend\tcop\postgres.c:900, As you can see the columns in the Log are comma separated , But however individual values are not Quote Enclosed. For instance the 1st,4rth,6th .. columns **Is there a setting in PostgreSQL configuration to place quotes around all columns in a Logfili I just want to update the columns so that all are within Quotes what happens wrong is when it reaches the column where sql statement is place. it also has commas set for table columns. The log line is a mix bunch of quote-enclosed and non-quote-enclosed column. is there is a configuration or utility to convert the non-quoted column to quoted column The columns that are not quoted are guaranteed not to contain a comma. So it shouldn't be a problem to parse them. In fact, it is quite easy. As an example, see here: http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/pgreplay/pgreplay/parse.c?re v=1.14content-type=text/x-cvsweb-markup In the function parse_csvlog_entry, after the comment read next line after start timestamp from log file you can find code that parses such a line. The code is in C, so it should be easy to port it to C#, which is essentially Java, which has C-like syntax. Yours, Laurenz Albe -- 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] Large PostgreSQL servers
On Thu, Mar 22, 2012 at 9:29 AM, Merlin Moncure mmonc...@gmail.com wrote: On Thu, Mar 22, 2012 at 10:02 AM, Scott Marlowe scott.marl...@gmail.com wrote: There's other issues you run into with large shared_buffers as well. If you've got a large shared_buffers setting, but only regularly hit a small subset of your db (say 32GB shared_buffers but only hit 4G or so regularly in your app) then it's quite possible that older shared_buffer segments will get swapped out because they're not being used. Then, when the db goes to hit a page in shared_buffers, the OS will have to swap it back in. What was supposed to make your db much faster has now made it much slower. With Linux, the OS tends to swap out unused memory to make room for file buffers. While you can change the swappiness settings to 0 to slow it down, the OS will eventually swap out the least used segments anyway. The only solution on large memory servers is often to just turn off swap. Right -- but my take on that is that hacking the o/s to disable swap is dealing with symptoms of problem related to server misconfiguration. You can configure a big memory linux server anyway you want. After a while, they seem to go crazy anyway and start swapping even when you've told them not to. In particular it probably means shared_buffers is set too high...the o/s thinks it needs that memory more than you do and it may very well be right. I've had machines with 128GB RAM and a 4G shared_buffers start swapping for no apparent reason and just fall over. There's no memory pressure etc, just kswapd decides to go nuts and start swapping. This was on Ubuntu 10.04 and 12.04 and RHEL 5.2 through 5.latest with all updates. These machines typically had ~90GB+ of kernel cache and zero memory pressure. -- 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] Parsing PG log usings CSV format
Thank you sir, i have sorted out the problem on The columns that are not quoted are guaranteed not to contain a comma. But i have another query, the structure of PG Log CSV as mentioned in manual and as below has 24 columns http://www.postgresql.org/docs/9.1/static/runtime-config-logging.html --- log_time timestamp, user_name text, database_name text, process_id integer, connection_from text, session_id text, session_line_num bigint, command_tag text, session_start_time timestamp, virtual_transaction_id text, transaction_id bigint, error_severity text, sql_state_code text, message text, detail text, hint text, internal_query text, internal_query_pos integer, context text, query text, query_pos integer, location text, application_name text, PRIMARY KEY (session_id, session_line_num) --- However every Log line added contains only 22 columns, a sample log line is given below --- 2012-03-22 15:29:53.546 IST,postgres,stock_apals,2396,localhost:2766,4f6af819.95c,9,SELECT,2012-03-22 15:29:53 IST,3/0,0,LOG,0,QUERY STATISTICS,! system usage stats: ! 0.015000 elapsed 0.00 user 0.015625 system sec ! [0.078125 user 0.062500 sys total],SELECT SUBSTR(itemname, 1, 15) as Product, avg(rate) as Avgrate FROM issue_stock WHERE extract(year from issue_stock.edate) = '2011' GROUP BY itemname order by itemname,,ShowUsage, .\src\backend\tcop\postgres.c:4305, --- the last column of the Log is not the Primarykey ? the last column as of yet is unknown because at all occurances it is a empty quoted column ? the column numbers dont match with the generated log Is this is a different Log format , can you guide us to a page where the column specifications can be matched. Yrs truly arvind pal singh Subject: RE: [GENERAL] Help in Parsing PG log usings CSV format Date: Thu, 22 Mar 2012 16:47:11 +0100 From: laurenz.a...@wien.gv.at To: arvin...@hotmail.com; pgsql-general@postgresql.org Arvind Singh wrote: Help needed in parsing PostgreSQL CSV Log [...] **However the main problem that is, the Log format is not readable** A Sample Log data line 2012-03-21 11:59:20.640 IST,postgres,stock_apals,3276,localhost:1639,4f697540.ccc,10,idle ,2012-03-21 11:59:20 IST,2/163,0,LOG,0,statement: SELECT version()exec_simple_query, .\src\backend\tcop\postgres.c:900, As you can see the columns in the Log are comma separated , But however individual values are not Quote Enclosed. For instance the 1st,4rth,6th .. columns **Is there a setting in PostgreSQL configuration to place quotes around all columns in a Logfili I just want to update the columns so that all are within Quotes what happens wrong is when it reaches the column where sql statement is place. it also has commas set for table columns. The log line is a mix bunch of quote-enclosed and non-quote-enclosed column. is there is a configuration or utility to convert the non-quoted column to quoted column The columns that are not quoted are guaranteed not to contain a comma. So it shouldn't be a problem to parse them. In fact, it is quite easy. As an example, see here: http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/pgreplay/pgreplay/parse.c?re v=1.14content-type=text/x-cvsweb-markup In the function parse_csvlog_entry, after the comment read next line after start timestamp from log file you can find code that parses such a line. The code is in C, so it should be easy to port it to C#, which is essentially Java, which has C-like syntax. Yours, Laurenz Albe
Re: [GENERAL] Large PostgreSQL servers
On Thu, Mar 22, 2012 at 10:57 AM, Scott Marlowe scott.marl...@gmail.com wrote: On Thu, Mar 22, 2012 at 9:29 AM, Merlin Moncure mmonc...@gmail.com wrote: On Thu, Mar 22, 2012 at 10:02 AM, Scott Marlowe scott.marl...@gmail.com wrote: There's other issues you run into with large shared_buffers as well. If you've got a large shared_buffers setting, but only regularly hit a small subset of your db (say 32GB shared_buffers but only hit 4G or so regularly in your app) then it's quite possible that older shared_buffer segments will get swapped out because they're not being used. Then, when the db goes to hit a page in shared_buffers, the OS will have to swap it back in. What was supposed to make your db much faster has now made it much slower. With Linux, the OS tends to swap out unused memory to make room for file buffers. While you can change the swappiness settings to 0 to slow it down, the OS will eventually swap out the least used segments anyway. The only solution on large memory servers is often to just turn off swap. Right -- but my take on that is that hacking the o/s to disable swap is dealing with symptoms of problem related to server misconfiguration. You can configure a big memory linux server anyway you want. After a while, they seem to go crazy anyway and start swapping even when you've told them not to. In particular it probably means shared_buffers is set too high...the o/s thinks it needs that memory more than you do and it may very well be right. I've had machines with 128GB RAM and a 4G shared_buffers start swapping for no apparent reason and just fall over. There's no memory pressure etc, just kswapd decides to go nuts and start swapping. This was on Ubuntu 10.04 and 12.04 and RHEL 5.2 through 5.latest with all updates. These machines typically had ~90GB+ of kernel cache and zero memory pressure. hm, that's interesting -- noted. I'll keep an eye out for that. merlin -- 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] Very high memory usage on restoring dump (with plain pqsl) on pg 9.1.2
Am 22.03.2012 15:48, schrieb Tom Lane: What PG version are we talking about, and what exactly is the problematic index? Index is on (inet, integer, smallint, timestamp w/o timezone), btree and a primary key. There was a memory leak in the last-but-one releases for index operations on inet and cidr datatypes, so I'm wondering if that explains your problem ... As written in the title, I'm experiencing this when migrating a dump-file (created on PostgreSQL 8.3.x) to an installation of PostgreSQL 9.1.2 (plain, comes from Gentoo). But it sure sounds as though this memory leak might be the culprit (and I'll have to do the update anyway, from what I gather, simply because I know have empty tables which do have the inet key). I'll be updating pgsql now and then recheck the import. Thanks for the hint! -- --- Heiko. -- 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] Index on System Table
2012/3/21 Tom Lane t...@sss.pgh.pa.us BTW, I experimented with that a little bit and found that the relmapper is not really the stumbling block, at least not after applying this one-line patch: http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=f70f095c9096d5e2689e8d79172b37b57a84e51b Can we back-patch it on previous versions like 9.0 and 9.1? Or exist some reason to don't do that? Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [GENERAL] Large Databases redux
On Thu, Mar 22, 2012 at 00:20, Martijn van Oosterhout klep...@svana.org wrote: That, and a good RAID controller with BBU cache will go a long way to relieving the pain of fsync. Well a BBU cache RAID is helpful, but fsyncs are a minor problem in data warehouse workloads, since inserts are done in large bulks and commits are rare. And you can run with synchronous_commit=off, since it's always possible to reload the last batch after a power failure. On Wed, Mar 21, 2012 at 23:18, Jason Herr jah...@gmail.com wrote: Single selects on tables need to be 3ms You've set yourself an impossible target, that's below the average seek time of 15kRPM disks. For indexed single-row selects on non-cached data, expect at least a few index page fetches and a heap fetch, and potentially file system block map lookups. 20ms seems a more plausible target. But with competing I/O activity, especially other OLAP/DW queries and bulk data loads, that's still quite optimistic. If you have a high cached access correlation and lots of RAM, it might be possible to keep the *average* below 3ms, but I don't know if you can bet on that with 2TB of storage. Regards, Marti -- 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] Index on System Table
=?ISO-8859-1?Q?Fabr=EDzio_de_Royes_Mello?= fabriziome...@gmail.com writes: 2012/3/21 Tom Lane t...@sss.pgh.pa.us BTW, I experimented with that a little bit and found that the relmapper is not really the stumbling block, at least not after applying this one-line patch: http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=f70f095c9096d5e2689e8d79172b37b57a84e51b Can we back-patch it on previous versions like 9.0 and 9.1? Or exist some reason to don't do that? Given that the whole thing is entirely unsupported anyway, I see no need to back-patch. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Question about warning: invalid resource manager ID 128 at ... on hot stanby
Hi all. Database version used 9.0.4 on FreeBSD 7.3. Today ,after restart of replica db, I got the next warning in log: 2012-03-23 03:10:08.221 MSK 55096 @ from [vxid:1/0 txid:0] []LOG: invalid resource manager ID 128 at 44E/4E7303B0 I searched over mailing lists but I still not sure is it harmless or no... Full log looks like: 2012-03-23 03:08:46.465 MSK 38622 @ from [vxid: txid:0] []LOG: received fast shutdown request 2012-03-23 03:08:46.465 MSK 38622 @ from [vxid: txid:0] []LOG: aborting any active transactions 2012-03-23 03:08:46.465 MSK 38627 @ from [vxid: txid:0] []FATAL: terminating walreceiver process due to administrator command 2012-03-23 03:08:46.693 MSK 38718 @ from [vxid: txid:0] []LOG: shutting down 2012-03-23 03:08:46.696 MSK 38718 @ from [vxid: txid:0] []LOG: database system is shut down 2012-03-23 03:08:53.850 MSK 55096 @ from [vxid: txid:0] []LOG: database system was shut down in recovery at 2012-03-23 03:08:46 MSK 2012-03-23 03:08:53.874 MSK 55096 @ from [vxid: txid:0] []LOG: entering standby mode 2012-03-23 03:08:53.905 MSK 55096 @ from [vxid:1/0 txid:0] []LOG: redo starts at 44E/114060E8 2012-03-23 03:10:08.221 MSK 55096 @ from [vxid:1/0 txid:0] []LOG: consistent recovery state reached at 44E/4E7303B0 2012-03-23 03:10:08.221 MSK 55096 @ from [vxid:1/0 txid:0] []LOG: invalid resource manager ID 128 at 44E/4E7303B0 2012-03-23 03:10:08.222 MSK 55093 @ from [vxid: txid:0] []LOG: database system is ready to accept read only connections 2012-03-23 03:10:08.239 MSK 56317 @ from [vxid: txid:0] []LOG: streaming replication successfully connected to primary Is that warning harmless on 9.0.4 or should I start to worry about? -- Maxim Boguk Senior Postgresql DBA. Phone RU: +7 910 405 4718 Phone AU: +61 45 218 5678 Skype: maxim.boguk Jabber: maxim.bo...@gmail.com LinkedIn profile: http://nz.linkedin.com/in/maximboguk If they can send one man to the moon... why can't they send them all? МойКруг: http://mboguk.moikrug.ru/ People problems are solved with people. If people cannot solve the problem, try technology. People will then wish they'd listened at the first stage.
[GENERAL] Is record handle available to a check constraint stored procedure call?
v8.3.4 on Linux I have a check constraint on a column. The constraint decides pass/fail based on the returned status of a stored procedure call that returns either OK or NO. So when the stored procedure is called, there's a living attempt to insert or update a record. Question: Is there a handle on the record being inserted or updated? Something like what's available in a trigger function (new.col1, old.col2, etc...)? If so, what does it look like? Thanks in Advance
[GENERAL] Re: postgresql 8.2 security definer is a built-in function. very confused??
I am very confused after I read the guide as follows. It means I only need to set the search_path to make the pg_temp as the last entry. or I need configure search_path and at the same time, I need create the security definer? Is thers anybody help me? Thank you very much. I really appreciate it. The following is what I got from the postgresql 8.2 guide. Writing SECURITY DEFINER Functions Safely Because a SECURITY DEFINER function is executed with the privileges of the user that created it, care is needed to ensure that the function cannot be misused. For security, search_path should be set to exclude any schemas writable by untrusted users. This prevents malicious users from creating objects that mask objects used by the function. Particularly important in this regard is the temporary-table schema, which is searched first by default, and is normally writable by anyone. A secure arrangement can be had by forcing the temporary schema to be searched last. To do this, write pg_temp as the last entry in search_path. This function illustrates safe usage: CREATE FUNCTION check_password(uname TEXT, pass TEXT) RETURNS BOOLEAN AS $$ DECLARE passed BOOLEAN; old_path TEXT; BEGIN -- Save old search_path; notice we must qualify current_setting -- to ensure we invoke the right function old_path := pg_catalog.current_setting(’search_path’); -- Set a secure search_path: trusted schemas, then ’pg_temp’. -- We set is_local = true so that the old value will be restored -- in event of an error before we reach the function end. PERFORM pg_catalog.set_config(’search_path’, ’admin, pg_temp’, true); -- Do whatever secure work we came for. SELECT (pwd = $2) INTO passed FROM pwds WHERE username = $1; -- Restore caller’s search_path PERFORM pg_catalog.set_config(’search_path’, old_path, true); RETURN passed; END; $$ LANGUAGE plpgsql SECURITY DEFINER; -- View this message in context: http://postgresql.1045698.n5.nabble.com/postgresql-8-2-security-definer-is-a-built-in-function-very-confused-tp5588409p5588420.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- 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.2 security definer is a built-in function. very confused??
the security definer is built-in function, or I need create security definer first, then user call it. How it works? I am pretty new in Postgresql. Please help. Thanks. -- View this message in context: http://postgresql.1045698.n5.nabble.com/postgresql-8-2-security-definer-is-a-built-in-function-very-confused-tp5588409p5588409.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- 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 64 Bit XIDs - Transaction IDs
Hello, With a database admin of a commercial database system I've discussed that they have to provide and they also achieve 2^31 transactions per SECOND! As PostgreSQL uses transaction IDs (XIDs) in the range of 2^31 they would turn around in about one second. How can one achieve this with PostgreSQL? What is the status and plan of 64 Bits XIDs? I saw that 64 Bit XIDs were already discussed in 2005 but never found into the code: http://postgresql.1045698.n5.nabble.com/A-couple-of-patches-for-PostgreSQL-64bit-support-td2214264.html Thnx. Ciao, Gerhard -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
