Re: [GENERAL] Oddball data distribution giving me planner headaches
Jeff Amiel writes: >> At the moment I think the only way to work around this is >> to denormalize >> your schema a bit. > And I feared as much. > It's biting me in other areas as well...this unusual distribution of > data...certain types of customers have completely different data patterns > than others. > Back to the drawing board...thanks! I find your table structure anyway somewhat strange. For an ordinary parent/child relationship a parent_id column in the customer table would be enough. Do you really have an m:n relationship between parents and children? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Shared memory usage in PostgreSQL 9.1
For a PostgreSQL 9.1.1 instance, I have used the following postgresql.conf settings: max_connections = 100 shared_buffers = 400MB wal_buffers = 16MB All the other parameters have been left as default values. When I startup the instance, I get an error message saying that the shared memory does not suffice and 451837952 Bytes would be used. However, this is not what I expect when calculating the needs according to the documentation, Table 17-2 at http://www.postgresql.org/docs/current/static/kernel-resources.html According to that table the usage would be: Connections: 1908000 Bytes Autovac workers: 57240 Bytes Prepared transactions: 0 Bytes Shared disk buffers: 400MB WAL buffers: 16MB Fixed space: 788480 Bytes Sum: 435145336 This is about 16MB less than what is really requested. How can this substantial discrepancy be explained? For PostgreSQL 9.1, some important item must be missing in Table 17-2, or some values are wrong. -- Christoph -- 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 + corrupted disk = data loss. (Need help for database recover)
On 2.12.2011 09:16, Oleg Serov wrote: > Hello! > > i've don't try to do reindex. There was enough space. Not sure whether you tried to reindex or not. And what do you mean by 'there was enough space'? For example with ext2 (and ext3/ext4) it was rather simple to exhaust inodes long before the device was actually full. What filesystem are you using, anyway? This seems like a I/O issue, you should check the hardware and the settings (e.g. what caches are enabled etc.). Post more details, if possible. Have you checked S.M.A.R.T. info from the drives? > And i have a full data-directory backup, when i've stop server, before > start. Good. Have you moved it to a different machine? Otherwise you don't have a backup, just a copy. Tomas -- 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] Shared memory usage in PostgreSQL 9.1
On 3.12.2011 13:39, Christoph Zwerschke wrote: > For a PostgreSQL 9.1.1 instance, > I have used the following postgresql.conf settings: > > max_connections = 100 > shared_buffers = 400MB > wal_buffers = 16MB > > All the other parameters have been left as default values. > > When I startup the instance, I get an error message > saying that the shared memory does not suffice > and 451837952 Bytes would be used. > > However, this is not what I expect when calculating > the needs according to the documentation, Table 17-2 at > http://www.postgresql.org/docs/current/static/kernel-resources.html > > According to that table the usage would be: > Connections: 1908000 Bytes > Autovac workers: 57240 Bytes > Prepared transactions: 0 Bytes > Shared disk buffers: 400MB > WAL buffers: 16MB > Fixed space: 788480 Bytes > Sum: 435145336 > > This is about 16MB less than what is really requested. > > How can this substantial discrepancy be explained? > > For PostgreSQL 9.1, some important item must be missing > in Table 17-2, or some values are wrong. Hi, the documentation is not exact, in this area. It's rather an overview than exhaustive description, but I admit it's a bit confusing. There are internal pieces that are not accounted for in the docs, and part of the discrepancy probably comes from 32bit vs. 64bit differences. Do you need to know an exact value or are you just interested why the values in docs are not exact? If you want to see what exactly needs how much memory, check the src/backend/storage/ipc/ipci.c file in sources. I've added some log messages, and this is the result on my 64bit machine (using the values you've posted): WARNING: initial = 10 WARNING: hash estimate = 12368 WARNING: buffers = 424669472 WARNING: locks = 2509584 WARNING: predicate locks = 2278566 WARNING: proc global = 70237 WARNING: xlogs = 16803120 WARNING: clogs = 131360 WARNING: subtrans = 263040 WARNING: two-phase commits = 16 WARNING: multi-xacts = 198224 WARNING: lwlocks = 3282376 WARNING: proc array = 864 WARNING: backend status = 141440 WARNING: sinval = 67224 WARNING: pm signal = 872 WARNING: proc signal = 3960 WARNING: bgwriter = 1228840 WARNING: autovacuum = 216 WARNING: wal sender = 28 WARNING: wal receiver = 1072 WARNING: btree = 1260 WARNING: sync scan = 656 WARNING: async = 67112 WARNING: final size = 451985408 Tomas -- 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] How to convert HEX to ASCII?
Marti Raudsepp schrieb: On Fri, Dec 2, 2011 at 16:16, Torsten Zuehlsdorff wrote: But i clearly have a missunderstanding of other chars, like umlauts or utf-8 chars. This, for example, should return a 'ö': # SELECT chr(x'C3B6'::int); chr - 쎶 (1 row) That gives you the Unicode codepoint C3B6, but %C3%B6 is UTF-8-encoded and actually decodes to the codepoint 00F6. There is a fundamental problem that a decoded URL may actually be a binary string -- it might not have a textual representation at all. But if text is what you want, RFC3986 strongly suggests using UTF-8 for encoding text strings in URLs, and that works almost always in the real world. Text is what i want. :) I've created a highly specialiced CMS, which handle a bunch of big sites (in meaning of a great numbers of users and content). It has a build-in traffic-analyze and with this function it creates a real time analyze of the keywords, a user used to find the sites in search engines. This is very needful if you try to do SEO for websites with more than 20.000 unique content-pages. :) CREATE OR REPLACE FUNCTION url_decode(input text) RETURNS text LANGUAGE plpgsql IMMUTABLE STRICT AS $$ DECLARE bin bytea = ''; byte text; BEGIN FOR byte IN (select (regexp_matches(input, '(%..|.)', 'g'))[1]) LOOP IF length(byte) = 3 THEN bin = bin || decode(substring(byte, 2, 2), 'hex'); ELSE bin = bin || byte::bytea; END IF; END LOOP; RETURN convert_from(bin, 'utf8'); END $$; Hey, this function looks similar to my encoding function :) Thank you very munch! This will break for binary-encoded data in URLs, though. Thats no problem, i just have text. Big thanks to all of you, Torsten -- 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 + corrupted disk = data loss. (Need help for database recover)
I think, the main problem is that, postgres reads wrong xlog-s file.
I lunched strace to postgres process, and then i grep the log:
# cat /tmp/strace-log | fgrep xlog
5546 stat("pg_xlog", {st_mode=S_IFDIR|0700, st_size=4096, ...}) = 0
5546 stat("pg_xlog/archive_status", {st_mode=S_IFDIR|0700, st_size=4096,
...}) = 0
5546 open("pg_xlog/0001.history", O_RDONLY) = -1 ENOENT (No such file
or directory)
5546 open("pg_xlog/0001000F0052", O_RDONLY) = 4
5546 open("pg_xlog/0001000F0052", O_RDONLY) = 4
There is only one file.
But i have a lot of files (i parsed filenames for more readable format):
0001001000D2tli:1 log:16 seg:210
0001001000EEtli:1 log:16 seg:238
0001001000D3tli:1 log:16 seg:211
0001001000E2tli:1 log:16 seg:226
0001001000D5tli:1 log:16 seg:213
0001001000E8tli:1 log:16 seg:232
0001001000F7tli:1 log:16 seg:247
0001001000DFtli:1 log:16 seg:223
0001001000DCtli:1 log:16 seg:220
0001001000E7tli:1 log:16 seg:231
0001001000EAtli:1 log:16 seg:234
0001001000D1tli:1 log:16 seg:209
0001001000DDtli:1 log:16 seg:221
0001001000F5tli:1 log:16 seg:245
0001001000E0tli:1 log:16 seg:224
0001001000EBtli:1 log:16 seg:235
0001001000D0tli:1 log:16 seg:208
0001001000F4tli:1 log:16 seg:244
0001001000F6tli:1 log:16 seg:246
0001001000D7tli:1 log:16 seg:215
0001001000DBtli:1 log:16 seg:219
0001001000E4tli:1 log:16 seg:228
0001001000DEtli:1 log:16 seg:222
0001001000E9tli:1 log:16 seg:233
0001001000D4tli:1 log:16 seg:212
0001001000D9tli:1 log:16 seg:217
0001001000F3tli:1 log:16 seg:243
0001001000E5tli:1 log:16 seg:229
0001001000DAtli:1 log:16 seg:218
0001001000ECtli:1 log:16 seg:236
0001001000D6tli:1 log:16 seg:214
0001001000EFtli:1 log:16 seg:239
0001001000E6tli:1 log:16 seg:230
0001001000E1tli:1 log:16 seg:225
0001001000F0tli:1 log:16 seg:240
0001001000D8tli:1 log:16 seg:216
0001001000CFtli:1 log:16 seg:207
0001001000EDtli:1 log:16 seg:237
0001001000E3tli:1 log:16 seg:227
0001001000F1tli:1 log:16 seg:241
0001001000F2tli:1 log:16 seg:242
0001001000F8tli:1 log:16 seg:248
So, main problem i think, is that pg_control file is corrupted (i guess).
So xlogreset-n sais:
bash-3.2$ pg_resetxlog -n /var/lib/pgsql/data
could not change directory to "/root"
pg_control values:
*First log file ID after reset:16*
First log file segment after reset: 249
pg_control version number:843
Catalog version number: 200904091
Database system identifier: 5592178670599662815
Latest checkpoint's TimeLineID: 1
Latest checkpoint's NextXID: 0/7760685
Latest checkpoint's NextOID: 2556003
Latest checkpoint's NextMultiXactId: 3925
Latest checkpoint's NextMultiOffset: 7901
Maximum data alignment: 8
Database block size: 8192
Blocks per segment of large relation: 131072
WAL block size: 8192
Bytes per WAL segment:16777216
Maximum length of identifiers:64
Maximum columns in an index: 32
Maximum size of a TOAST chunk:1996
Date/time type storage: 64-bit integers
Float4 argument passing: by value
Float8 argument passing: by value
*And main question, how to fo
Re: [GENERAL] Shared memory usage in PostgreSQL 9.1
Am 03.12.2011 15:34, schrieb Tomas Vondra: > Do you need to know an exact value or are you just interested why the > values in docs are not exact? Both. I'm writing an installation script that calculates the necessary IPC memory and increases the limit on the OS level (kernel.shmmax) if needed. I want to increase the limit only as much as really necessary, so I need to calculate the IPC usage as exactly as possible. (Btw, what negative consequences - if any - does it have if I set kernel.shmmax higher as necessary, like all available memory? Does this limit serve only as a protection against greedy applications?) > If you want to see what exactly needs how much memory, check the > src/backend/storage/ipc/ipci.c file in sources. I've added some log > messages, and this is the result on my 64bit machine (using the values > you've posted): Thanks a lot, that was helpful. So it seems the values in the docs are only correct for a 32 bit server. But I still don't understand this: In our example, we have set shared_buffers to: 400 MB = 419430400 Bytes but according to your log the used memory is: buffers = 424669472 Bytes This is a discrepancy of 1.25%. The difference could be explained by taking credit for the descriptors which may not be comprised in the shared_buffers setting, even if the shared_buffers value is set in memory units. But according to the docs, the descriptors should use 208/8192 = 2.5%. And on a 64bit machine, the descriptors should use even more memory, i.e. up to 5%. So I'm still a bit confused. -- Christoph -- 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] Shared memory usage in PostgreSQL 9.1
Christoph Zwerschke writes: > ... This is a discrepancy of 1.25%. > The difference could be explained by taking credit for the descriptors > which may not be comprised in the shared_buffers setting, even if the > shared_buffers value is set in memory units. But according to the docs, > the descriptors should use 208/8192 = 2.5%. And on a 64bit machine, the > descriptors should use even more memory, i.e. up to 5%. > So I'm still a bit confused. The long and the short of it is those numbers aren't meant to be exact. If they were, we'd have to complicate the table to distinguish 32 vs 64 bit and possibly other factors, and we'd have to remember to re-measure the values after any code change, neither of which seems worth the trouble. Please note that the table itself says that (a) the values are approximate, and (b) nobody has bothered to update the numbers since 8.3. Personally, I'm thrilled if you're seeing a discrepancy of only 1.25%. 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] returning results from plsql function to plpythonu function
Hello friends,
I am checking some logic which includes two functions. One is plsql
function which returns text. Second function is plpythonu and contains one
statement which outputs notice
e.g.
results = plpy.execute("select * from software.func1();")
plpy.notice("%s" % col for col in results[0])
The same result is returned as a final return as notice. But it does not
outputs the correct result which is returned from func1 (text in this
case). Rather it outputs notice as
NOTICE: at 0xb578cb6c>
It doesn't prints the correct notice even by using
plpy.notice("%s" %results)
What can be the problem here?
Thanks for help.
C P Kulkarni
[GENERAL] Re: [ADMIN] returning results from plsql function to plpythonu function
CP - You're missing a line in the middle; one which will extract specific
content(s) from $results.
Because, yes, without such a line, python will only return the object
reference.
Lou Picciano
- Original Message -
From: "c k"
To: "pgsql-admin" , pgsql-general@postgresql.org
Sent: Saturday, December 3, 2011 12:50:16 PM
Subject: [ADMIN] returning results from plsql function to plpythonu function
Hello friends,
I am checking some logic which includes two functions. One is plsql function
which returns text. Second function is plpythonu and contains one statement
which outputs notice
e.g.
results = plpy.execute("select * from software.func1();")
plpy.notice("%s" % col for col in results[0])
The same result is returned as a final return as notice. But it does not
outputs the correct result which is returned from func1 (text in this case).
Rather it outputs notice as
NOTICE: at 0xb578cb6c>
It doesn't prints the correct notice even by using
plpy.notice("%s" %results)
What can be the problem here?
Thanks for help.
C P Kulkarni
Re: [GENERAL] [ADMIN] returning results from plsql function to plpythonu function
can you please give me the example
I am not a python programmer.
C P Kulkarni
On Sat, Dec 3, 2011 at 11:25 PM, Lou Picciano wrote:
> CP - You're missing a line in the middle; one which will extract specific
> content(s) from $results.
>
> Because, yes, without such a line, python will only return the object
> reference.
>
> Lou Picciano
>
> --
> *From: *"c k"
> *To: *"pgsql-admin" ,
> pgsql-general@postgresql.org
> *Sent: *Saturday, December 3, 2011 12:50:16 PM
> *Subject: *[ADMIN] returning results from plsql function to plpythonu
> function
>
>
> Hello friends,
> I am checking some logic which includes two functions. One is plsql
> function which returns text. Second function is plpythonu and contains one
> statement which outputs notice
>
> e.g.
> results = plpy.execute("select * from software.func1();")
> plpy.notice("%s" % col for col in results[0])
>
> The same result is returned as a final return as notice. But it does not
> outputs the correct result which is returned from func1 (text in this
> case). Rather it outputs notice as
> NOTICE: at 0xb578cb6c>
> It doesn't prints the correct notice even by using
> plpy.notice("%s" %results)
>
> What can be the problem here?
> Thanks for help.
>
>
> C P Kulkarni
>
Re: [GENERAL] [ADMIN] returning results from plsql function to plpythonu function
when I modified the notice statement as
plpy.notice('%s' %results[0])
it prints notice as
NOTICE: {'func1': 'function return text'}
I didn't understand why it is showing 'func1' in the results[0] ?
C P Kulkarni
On Sat, Dec 3, 2011 at 11:31 PM, c k wrote:
> can you please give me the example
> I am not a python programmer.
>
> C P Kulkarni
>
>
>
> On Sat, Dec 3, 2011 at 11:25 PM, Lou Picciano wrote:
>
>> CP - You're missing a line in the middle; one which will extract specific
>> content(s) from $results.
>>
>> Because, yes, without such a line, python will only return the object
>> reference.
>>
>> Lou Picciano
>>
>> --
>> *From: *"c k"
>> *To: *"pgsql-admin" ,
>> pgsql-general@postgresql.org
>> *Sent: *Saturday, December 3, 2011 12:50:16 PM
>> *Subject: *[ADMIN] returning results from plsql function to plpythonu
>> function
>>
>>
>> Hello friends,
>> I am checking some logic which includes two functions. One is plsql
>> function which returns text. Second function is plpythonu and contains one
>> statement which outputs notice
>>
>> e.g.
>> results = plpy.execute("select * from software.func1();")
>> plpy.notice("%s" % col for col in results[0])
>>
>> The same result is returned as a final return as notice. But it does not
>> outputs the correct result which is returned from func1 (text in this
>> case). Rather it outputs notice as
>> NOTICE: at 0xb578cb6c>
>> It doesn't prints the correct notice even by using
>> plpy.notice("%s" %results)
>>
>> What can be the problem here?
>> Thanks for help.
>>
>>
>> C P Kulkarni
>>
>
>
Re: [GENERAL] Shared memory usage in PostgreSQL 9.1
Am 03.12.2011 18:02, schrieb Christoph Zwerschke: The difference could be explained by taking credit for the descriptors which may not be comprised in the shared_buffers setting, even if the shared_buffers value is set in memory units. Looked a bit more into this - the shared_buffers setting indeed only determines the memory for he actual shared buffer blocks, even if given in memory units. It does not include the descriptors and other freelist related stuff that is also needed to build the shared buffer pool. When I increased the shared_buffers by 1, the shared memory usage increased by 8372.4 Bytes, this is about 2.2% more than 1 blocks would use, close to the 2.5% which are documented. -- Christoph -- 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] Shared memory usage in PostgreSQL 9.1
Am 03.12.2011 18:39, schrieb Tom Lane: The long and the short of it is those numbers aren't meant to be exact. If they were, we'd have to complicate the table to distinguish 32 vs 64 bit and possibly other factors, and we'd have to remember to re-measure the values after any code change, neither of which seems worth the trouble. Please note that the table itself says that (a) the values are approximate, and (b) nobody has bothered to update the numbers since 8.3. Personally, I'm thrilled if you're seeing a discrepancy of only 1.25%. Understood. Btw, the 1.25% did not refer to the discrepancy between calculated and measured value, but to the memory overhead Tomas Vondra measured for the shared buffer pool, while I measured an overhead of about 2.5%, which should be also expected according to the docs. Another thing that's a bit confusing in Table 17.2 is that it is not immediately clear what size the shared disk buffers and wal buffers have when shared_buffers and wal_buffers are specified in memory units, not as integers as the table implies. The answer is, as I found out, in order to get the "real" values for shared_buffers and wal_buffers, the memory values must be divided by block_size resp. wal_block_size; the formula then stays the same. -- Christoph -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: [ADMIN] returning results from plsql function to plpythonu function
# Assuming your query here returns col1, col2, etc.:
results = plpy.execute("select * from software.func1();")
# these lines will return the results --
# - (where the first member of the array is the 'row number' of the result:)
value1 = results[0][ "col1" ]
value2 = results[0][ "col2" ]
# --- Wanna see?
plpy.notice("-- value1: ",value1)
plpy.notice("-- value2: ",value2)
can you please give me the example
I am not a python programmer.
Nor am I!
Looks like your example, btw, is attempting string operations on something not
yet quite a string!
Pythons are dangerous. Be careful!
Lou Picciano
- Original Message -
From: "c k"
To: "Lou Picciano"
Cc: "pgsql-admin" , pgsql-general@postgresql.org
Sent: Saturday, December 3, 2011 1:01:39 PM
Subject: Re: [ADMIN] returning results from plsql function to plpythonu
function
can you please give me the example
I am not a python programmer.
C P Kulkarni
On Sat, Dec 3, 2011 at 11:25 PM, Lou Picciano < loupicci...@comcast.net >
wrote:
CP - You're missing a line in the middle; one which will extract specific
content(s) from $results.
Because, yes, without such a line, python will only return the object
reference.
Lou Picciano
From: "c k" < shreeseva.learn...@gmail.com >
To: "pgsql-admin" < pgsql-ad...@postgresql.org >, pgsql-general@postgresql.org
Sent: Saturday, December 3, 2011 12:50:16 PM
Subject: [ADMIN] returning results from plsql function to plpythonu function
Hello friends,
I am checking some logic which includes two functions. One is plsql function
which returns text. Second function is plpythonu and contains one statement
which outputs notice
e.g.
results = plpy.execute("select * from software.func1();")
plpy.notice("%s" % col for col in results[0])
The same result is returned as a final return as notice. But it does not
outputs the correct result which is returned from func1 (text in this case).
Rather it outputs notice as
NOTICE: at 0xb578cb6c>
It doesn't prints the correct notice even by using
plpy.notice("%s" %results)
What can be the problem here?
Thanks for help.
C P Kulkarni
Re: [GENERAL] Shared memory usage in PostgreSQL 9.1
Am 03.12.2011 13:39, schrieb Christoph Zwerschke: According to that table the usage would be: Connections: 1908000 Bytes Autovac workers: 57240 Bytes Prepared transactions: 0 Bytes Shared disk buffers: 400MB WAL buffers: 16MB Fixed space: 788480 Bytes Sum: 435145336 This is about 16MB less than what is really requested. Just so that this summation does not stay uncorrected: The major discrepancy accrued because my values for shared disk buffers and WAL buffers were wrong. They must be calculated as Shared disk buffers = (1 + 208/8192) * 400MB = 43008 Bytes WAL buffers = (1 + 8/8192) * 16MB = 16793600 Bytes Then, the corrected sum is 449627320 Bytes, which is only about 2MB less than was requested. This remaining discrepancy can probably be explained by additional overhead for a PostgreSQL 9.1 64bit server vs. a PostgreSQL 8.3 32bit server for which the table was valid. -- Christoph -- 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] [ADMIN] returning results from plsql function to plpythonu function
thanks.
it solved my problem.
C P Kulkarni
On Sun, Dec 4, 2011 at 12:21 AM, Lou Picciano wrote:
> # Assuming your query here returns col1, col2, etc.:
>
> results = plpy.execute("select * from software.func1();")
>
> # these lines will return the results --
> # - (where the first member of the array is the 'row number' of the
> result:)
>
> value1 = results[0][ "col1" ]
> value2 = results[0][ "col2" ]
>
> # --- Wanna see?
>
> plpy.notice("-- value1: ",value1)
> plpy.notice("-- value2: ",value2)
>
>
> can you please give me the example
> I am not a python programmer.
>
> Nor am I!
> Looks like your example, btw, is attempting string operations on something
> not yet quite a string!
>
> Pythons are dangerous. Be careful!
>
>
> Lou Picciano
>
> --
> *From: *"c k"
> *To: *"Lou Picciano"
> *Cc: *"pgsql-admin" ,
> pgsql-general@postgresql.org
> *Sent: *Saturday, December 3, 2011 1:01:39 PM
> *Subject: *Re: [ADMIN] returning results from plsql function to plpythonu
> function
>
>
> can you please give me the example
> I am not a python programmer.
>
> C P Kulkarni
>
>
> On Sat, Dec 3, 2011 at 11:25 PM, Lou Picciano wrote:
>
>> CP - You're missing a line in the middle; one which will extract specific
>> content(s) from $results.
>>
>> Because, yes, without such a line, python will only return the object
>> reference.
>>
>> Lou Picciano
>>
>> --
>> *From: *"c k"
>> *To: *"pgsql-admin" ,
>> pgsql-general@postgresql.org
>> *Sent: *Saturday, December 3, 2011 12:50:16 PM
>> *Subject: *[ADMIN] returning results from plsql function to plpythonu
>> function
>>
>>
>> Hello friends,
>> I am checking some logic which includes two functions. One is plsql
>> function which returns text. Second function is plpythonu and contains one
>> statement which outputs notice
>>
>> e.g.
>> results = plpy.execute("select * from software.func1();")
>> plpy.notice("%s" % col for col in results[0])
>>
>> The same result is returned as a final return as notice. But it does not
>> outputs the correct result which is returned from func1 (text in this
>> case). Rather it outputs notice as
>> NOTICE: at 0xb578cb6c>
>> It doesn't prints the correct notice even by using
>> plpy.notice("%s" %results)
>>
>> What can be the problem here?
>> Thanks for help.
>>
>>
>> C P Kulkarni
>>
>
>
Re: [GENERAL] How to get Place Names from Lat Lon
On Thu, Dec 1, 2011 at 6:46 AM, Adarsh Sharma wrote: > I have a position table that contains the lat lon of an entity from time to > time. > Now I want to get the place names from the respective lat lon. You might want to try the PostGIS lists instead. Josh -- 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] Shared memory usage in PostgreSQL 9.1
Am 03.12.2011 20:31, schrieb Christoph Zwerschke: Then, the corrected sum is 449627320 Bytes, which is only about 2MB less than was requested. This remaining discrepancy can probably be explained by additional overhead for a PostgreSQL 9.1 64bit server vs. a PostgreSQL 8.3 32bit server for which the table was valid. And this additional overhead obviously is created per max_connections, not per shared_buffers. While the docs suggest there should be 19kB per connection, we measured about 45kB per connection. This explains the about 2MB difference when max_connections is 100. -- Christoph -- 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] Shared memory usage in PostgreSQL 9.1
Am 03.12.2011 18:02, schrieb Christoph Zwerschke: 400 MB = 419430400 Bytes but according to your log the used memory is: buffers = 424669472 Bytes This is a discrepancy of 1.25%. The difference could be explained by taking credit for the descriptors which may not be comprised in the shared_buffers setting, even if the shared_buffers value is set in memory units. But according to the docs, the descriptors should use 208/8192 = 2.5%. And on a 64bit machine, the descriptors should use even more memory, i.e. up to 5%. Just to clear up that last unexplained discrepancy, the problem is that I wrongly assumed the descriptors were the only overhead to the shared buffers. In reality it is more complex, e.g. additional memory for locks is reserved for each shared buffer. The 208 Bytes in the docs refer to the total overhead a shared buffer creates, while the value in Tomas' log contained only the overhead caused by the descriptors. Sorry for creating the noise and confusion. -- Christoph -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
