[GENERAL] Choosing primary key type: 64 or 52 bit primary keys?
Hi all, I'd like to use an integer number for my primary key. I need it to be bigger than 32 bits. As far as I understand I have two options: a) use all the 64 bits of a 'bigint' b) use the 52 mantissa bits of a 'double precision' My question is, which would be faster for indexing? I assume the bigint wins here, right? Thanks in advance, Antonio -- 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] Choosing primary key type: 64 or 52 bit primary keys?
I think there is no difference in indexing int or floats. Only one difference is speed of comparison of this numbers. If you create normal system use 64bit ints. Regards, Radoslaw Smogura (mobile) -Original Message- From: Antonio Vieiro Sent: 22 lipca 2011 09:01 To: pgsql Subject: [GENERAL] Choosing primary key type: 64 or 52 bit primary keys? Hi all, I'd like to use an integer number for my primary key. I need it to be bigger than 32 bits. As far as I understand I have two options: a) use all the 64 bits of a 'bigint' b) use the 52 mantissa bits of a 'double precision' My question is, which would be faster for indexing? I assume the bigint wins here, right? Thanks in advance, Antonio -- 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
Re: [GENERAL] Maximum number of client connection supported by Postgres 8.4.6
Hi All, Exact Error Message is as follow.. [ERROR] Error getting DB connection: The connection attempt failed. [ERROR] Action commit error: Out of database connections. This is the output I am getting form application server, On database end I am getting nothing. plz suggest. If possible guide me how to calculate max_connections based on available hardware. Thanks regards, JENISH VYAS On Thu, Jul 21, 2011 at 4:28 PM, Albe Laurenz laurenz.a...@wien.gv.atwrote: Jenish Vyaswrote: please let me know what is the maximum number of concurrent client connection supported by Postgres 8.4.6 max_connections = For my database, If I am running the test for more then 1000 concurrent active user it is showing me error running out of connection I have set max_connections = 1200. There is no error message running out of connection in the code base. Could you quote the exact message? Maybe you are hitting a kernel resource limit, see http://www.postgresql.org/docs/8.4/static/kernel-resources.html In that case you might have to increase SEMMNS or SEMMNI. Yours, Laurenz Albe
Re: [GENERAL] Is there a way to 'unrestrict' drop view?
On Thu, Jul 21, 2011 at 3:20 PM, Thomas Pasch thomas.pa...@nuclos.de wrote: I would like to recreate/replace a view, but there are 'dependant objects' on it. Is there a way to 'unrestrict' the dependant check in the current transaction, like it could be done with certain constraints? Hi, Nice idea, but i think there isn't a way to do that. You will have to drop and re-create the objects in the correct order, best in a single transaction. I can imagine that that can be nasty, even apart from the hassle of cutting and pasting + testing that code. You might be needing those objects in a running system. But then what would it mean to to what you suggest? The dependent objects could never function while the view does not exist, so it ends up being much the same as drop+create. Except that you are changing the view, so you might also need to change the depending objects.. Cheers, WBL -- Patriotism is the conviction that your country is superior to all others because you were born in it. -- George Bernard Shaw -- 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] Maximum number of client connection supported by Postgres 8.4.6
Jenish Vyas wrote: [unexpectedly runs out of connections] Exact Error Message is as follow.. [ERROR] Error getting DB connection: The connection attempt failed. [ERROR] Action commit error: Out of database connections. This is the output I am getting form application server, On database end I am getting nothing. plz suggest. Please try not to top-post. Both of those error messages are not form PostgreSQL, so they don't help much. You might get more in the database log if you set log_connections = on in postgresql.conf and reload (make sure that log_min_messages is fatal or lower). It also wouldn't hurt to try and count the actual connections when you hit the problem (SELECT count(*) FROM pg_stat_activity) and check if that's close to max_connections. Have you considered the possibility that the limit and the error do not originate in that database, but in the application server? If possible guide me how to calculate max_connections based on available hardware. It is almost unlimited on any hardware. That does not mean that things will perform well beyond a certain limit. The limiting factor I mentioned is the operating system, and these limits can usually be adjusted. 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
[GENERAL] Tracing in Postgres
Hi, I am trying to integrate a tracing framework in the Postgres code. I need to know if elog.c under backend/utils/error is the place where the changes can be made. The tracing framework that I want to integrate has some additional capability. I want to replace the tracing and logging functionality in the existing Postgres framework with the APIs used in this framework without making changes in every file. If anybody has any inputs on this, please help me. Thanks, Harshitha
[GENERAL] Timestamp parsing with blanked time part
Hi, consider the following: select quote_literal(blank_hms) as quote_literal(blank_hms), blank_hms::timestamp as blank_hms::timestamp from (select unnest(array['2011-07-22 :', '2011-07-22 : ', '2011-07-22 : : ']::text[]) as blank_hms) a; select version(); quote_literal(blank_hms) | blank_hms::timestamp --+-- '2011-07-22 :' | 2011-07-22 00:00:00 '2011-07-22 : ' | 2011-07-22 00:00:00 '2011-07-22 : : '| 2011-07-22 00:00:00 (3 rows) Time: 0.264 ms version -- PostgreSQL 9.0.3 on x86_64-manual_install-freebsd8.x, compiled by GCC cc (GCC) 4.2.1 20070719 [FreeBSD], 64-bit (1 row) The result is what might be expected by the common sense means. Howewer, the input format of the example datetime strings is definitely wrong as far as I guess. Some other datetime parsers reject it, the Perl DateTime::Format::Pg is an example. Is this case a subject of eventual corrections in the future versions of postgres and it would start emit errors then? Thanks Irek. -- 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 parsing with blanked time part
Hi, consider the following: select quote_literal(blank_hms) as quote_literal(blank_hms), blank_hms::timestamp as blank_hms::timestamp from (select unnest(array['2011-07-22 :', '2011-07-22 : ', '2011-07-22 : : ']::text[]) as blank_hms) a; select version(); quote_literal(blank_hms) | blank_hms::timestamp --+-- '2011-07-22 :' | 2011-07-22 00:00:00 '2011-07-22 : ' | 2011-07-22 00:00:00 '2011-07-22 : : '| 2011-07-22 00:00:00 (3 rows) Time: 0.264 ms version -- PostgreSQL 9.0.3 on x86_64-manual_install-freebsd8.x, compiled by GCC cc (GCC) 4.2.1 20070719 [FreeBSD], 64-bit (1 row) The result is what might be expected by the common sense means. Howewer, the input format of the example datetime strings is definitely wrong as far as I guess. Some other datetime parsers reject it, the Perl DateTime::Format::Pg is an example. Is this case a subject of eventual corrections in the future versions of postgres and it would start emit errors then? Thanks Irek. -- 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] Maximum number of client connection supported by Postgres 8.4.6
On 22 Červenec 2011, 10:29, Albe Laurenz wrote: Have you considered the possibility that the limit and the error do not originate in that database, but in the application server? If the max_connections is 1200 and you get that error with 1000 of them, it's probably a problem with a connection pool in your application server (not such whit platform you're working on). If possible guide me how to calculate max_connections based on available hardware. It is almost unlimited on any hardware. That does not mean that things will perform well beyond a certain limit. The limiting factor I mentioned is the operating system, and these limits can usually be adjusted. Theoretically it's unlimited, in practice the optimal value is much lower. The general rule of thmub is usually max_connections = number of cores + number of drives so with a 4-core CPU and 10 drives you'll get about 14 connections. That's very rough - it might be a bit higher, but I don't expect to grow it above 30. So having 1200 connections is a bit extreme - if the connections are active all the time (not idle, doing something), the overhead of managing them will be severe. Don't forget each connection is equal to a separate process, so it's not exactly cheap. Do you really need that number of connections? What I'd suggest is to run a series of pgbench tests with various -c values (10, 20, 30, ...) to get some basic starting point. Then I'd set pgbouncer with this number of db connections and 1000 of client connections, and pool_mode=transaction. 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] Choosing primary key type: 64 or 52 bit primary keys?
bigint by all means. floating point arithmetic is somewhat more bloated/fuzzy/straight forward than integer, and even if postgresql was perfect regarding floating point comparisons, no one can claim the same for client languages. So define your PK as bigint. Στις Friday 22 July 2011 10:01:58 ο/η Antonio Vieiro έγραψε: Hi all, I'd like to use an integer number for my primary key. I need it to be bigger than 32 bits. As far as I understand I have two options: a) use all the 64 bits of a 'bigint' b) use the 52 mantissa bits of a 'double precision' My question is, which would be faster for indexing? I assume the bigint wins here, right? Thanks in advance, Antonio -- Achilleas Mantzios -- 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] COPY TO '|gzip /my/cool/file.gz'
On Wed, Jul 20, 2011 at 8:53 PM, Vibhor Kumar vibhor.ku...@enterprisedb.com wrote: You can use STDOUT to pipe output to a shell command and STDIN to read input from shell command. Something like given below: psql -c COPY mytable to STDOUT|gzip /home/tgl/mytable.dump.gz cat filename|psql -c COPY mytable from STDIN; OR psql -c COPY mytable from STDIN; filename nice one, that works great! (zcat instead of cat, though) -- Patriotism is the conviction that your country is superior to all others because you were born in it. -- George Bernard Shaw -- 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] Tracing in Postgres
On 22/07/2011 4:43 PM, Harshitha S wrote: Hi, I am trying to integrate a tracing framework in the Postgres code. I need to know if elog.c under backend/utils/error is the place where the changes can be made. It depends: what exactly are the kinds of events you want to trace? If you're looking to redirect the logging output Pg can already produce, you can just have your tracing system act as a syslog daemon and get postgresql to write to syslog. If you want something more detailed, elog will probably do much of what you need. If you want to trace things like actual row changes, which Pg never writes through elog, you won't be able to get them via that mechanism. -- Craig Ringer POST Newspapers 276 Onslow Rd, Shenton Park Ph: 08 9381 3088 Fax: 08 9388 2258 ABN: 50 008 917 717 http://www.postnewspapers.com.au/ -- 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] Choosing primary key type: 64 or 52 bit primary keys?
Στις Friday 22 July 2011 13:25:21 ο/η Achilleas Mantzios έγραψε: bigint by all means. floating point arithmetic is somewhat more bloated/fuzzy/straight forward than integer, ^^ oops sorry i mean less straight forward and even if postgresql was perfect regarding floating point comparisons, no one can claim the same for client languages. So define your PK as bigint. Στις Friday 22 July 2011 10:01:58 ο/η Antonio Vieiro έγραψε: Hi all, I'd like to use an integer number for my primary key. I need it to be bigger than 32 bits. As far as I understand I have two options: a) use all the 64 bits of a 'bigint' b) use the 52 mantissa bits of a 'double precision' My question is, which would be faster for indexing? I assume the bigint wins here, right? Thanks in advance, Antonio -- Achilleas Mantzios -- Achilleas Mantzios -- 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] Is there a way to 'unrestrict' drop view?
Hi, well, the reason I'm asking is that this *is* posible in Oracle DB. For me it looks like that the DB knows that the view is broken. You can't use it, *but* it is still there (and it will be usable again when the view query is valid again). I completely agree that the view should be usable again at the end of transaction (even thus Oracle DB doesn't impose that either), but drop and re-create the objects in correct order is painful. The heart of the my pain is that a program I use works like this. I would like to migrate the DB beneath it... Cheers, Thomas Am 22.07.2011 10:26, schrieb Willy-Bas Loos: On Thu, Jul 21, 2011 at 3:20 PM, Thomas Pasch thomas.pa...@nuclos.de wrote: I would like to recreate/replace a view, but there are 'dependant objects' on it. Is there a way to 'unrestrict' the dependant check in the current transaction, like it could be done with certain constraints? Hi, Nice idea, but i think there isn't a way to do that. You will have to drop and re-create the objects in the correct order, best in a single transaction. I can imagine that that can be nasty, even apart from the hassle of cutting and pasting + testing that code. You might be needing those objects in a running system. But then what would it mean to to what you suggest? The dependent objects could never function while the view does not exist, so it ends up being much the same as drop+create. Except that you are changing the view, so you might also need to change the depending objects.. Cheers, WBL -- 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] Timestamp parsing with blanked time part
Ireneusz Pluta ipl...@wp.pl writes: [ Postgres accepts timestamp input of the form '2011-07-22 :' ] Some other datetime parsers reject it, the Perl DateTime::Format::Pg is an example. Is this case a subject of eventual corrections in the future versions of postgres and it would start emit errors then? No, it isn't. If we tightened that up, it would inevitably break somebody else's application. And who's to say that DateTime::Format is the best authority on what should be considered valid? 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] Is there a way to 'unrestrict' drop view?
Thomas Pasch thomas.pa...@nuclos.de writes: well, the reason I'm asking is that this *is* posible in Oracle DB. For me it looks like that the DB knows that the view is broken. You can't use it, *but* it is still there (and it will be usable again when the view query is valid again). I completely agree that the view should be usable again at the end of transaction (even thus Oracle DB doesn't impose that either), but drop and re-create the objects in correct order is painful. Well, if the dependent objects don't need to be touched because the view's API (its output column set) isn't changing, then you can use CREATE OR REPLACE VIEW. If the output column set *is* changing, you need to redefine all the dependent objects anyway. Oracle may be willing to guess at what should happen to them, but Postgres isn't. 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] Is there a way to 'unrestrict' drop view?
Thomas Pasch wrote: well, the reason I'm asking is that this *is* posible in Oracle DB. For me it looks like that the DB knows that the view is broken. You can't use it, *but* it is still there (and it will be usable again when the view query is valid again). True, but Oracle pays a price for it. There is never a guarantee that all objects in the database are consistent, and in fact you're always likely to have a number of 'invalid' objects around that might fail or not if you use them. The heart of the my pain is that a program I use works like this. I would like to migrate the DB beneath it... I'd say that a program that changes views on the fly has a questionable design, but obviously that won't help you. You could automatically find out all dependent views (via pg_depend), get their DDL (with pg_get_viewdef()) and drop and recreate them in order. That's painful of course. 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
[GENERAL] interesting finding on order by behaviour
I ran the same tests in SQL Server 2008R2, Oracle10 and PostgreSQL
9.0.4 and found something interesting...
set up
=
drop table t1
create table t1 (f1 varchar(100))
insert into t1 (f1) values ('AbC')
insert into t1 (f1) values ('CdE')
insert into t1 (f1) values ('abc')
insert into t1 (f1) values ('ABc')
insert into t1 (f1) values ('cde')
test
===
select * from t1 order by f1
select min(f1) as min, max(f1) as max from t1
results
=
SQL Server 2008 R2 (with case insensitive data, the ordering follows
ASCII order)
f1
---
AbC
abc
ABc
cde
CdE
minmax
-- ---
AbC CdE
Oracle 10 (data is case-sensitive, the ordering follows ASCII order)
f1
---
ABc
AbC
CdE
abc
cde
minmax
-- ---
ABc cde
PostgreSQL 9.0.4 (data is case-sensitive, the ordering is ...
DIFFERENT)
f1
---
abc
AbC
ABc
cde
CdE
minmax
-- ---
abc CdE
--
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] interesting finding on order by behaviour
On Fri, 2011-07-22 at 10:11 -0700, Samuel Hwang wrote: I ran the same tests in SQL Server 2008R2, Oracle10 and PostgreSQL 9.0.4 and found something interesting... results = SQL Server 2008 R2 (with case insensitive data, the ordering follows ASCII order) Oracle 10 (data is case-sensitive, the ordering follows ASCII order) PostgreSQL 9.0.4 (data is case-sensitive, the ordering is ... DIFFERENT) perhaps http://www.postgresql.org/docs/9.1/static/charset.html will provide an answer
Re: [GENERAL] interesting finding on order by behaviour
On Jul 22, 2011, at 11:11 AM, Samuel Hwang wrote: results = SQL Server 2008 R2 (with case insensitive data, the ordering follows ASCII order) f1 --- AbC abc ABc cde CdE Well, if it's case insensitive, then AbC abc ABc are all equal, so any order for those 3 would be correct... -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Why do I have reading from the swap partition?
Hi Everyone, I am trying to debug a slowness that is happening on one of my production sites and I would like to ask you for some help. This is my environment: --- Dedicated server running: SUSE Linux Enterprise Server 11 (x86_64): VERSION = 11 PATCHLEVEL = 1 RAM = 16GB Postgres 9.0.3: shared_buffers = 4GB work_mem = 2MB maintenance_work_mem = 2GB wal_buffers = 1MB checkpoint_segments =16 effective_cache_size = 8GB And this is my scenario: I have a table with 16 million records and few indexes for that table. I also have a query from that table (few filters no joins) that returns 6.000 records. I have the proper indexes and the plan looks good. I don't think the query or the table structure are important that is why I did not post them. I reboot the server and start postgres: I run a query first time and it takes ~ 2.5 seconds I run the same query for the second time and it takes 1 sec (because it is cached) All good here. Now I reboot the server again and start postgres: I do a select * from a 8 GB table (a different one then the one used in the query). At a point it starts using swap space on disk. Once it starts swapping I still let it run for couple of minutes and the I stop it (CTRL+C). After that I have 14 GB free memory and in postgres I only have about 3 buffers used in pg_buffercache, the rest up to 524288 being empty. If I run my query again then the query takes 60 seconds and I notice reads from the swap partition. Now my question is why would I have a read from the swap partition when using a table that was not accessed since restart so it is not cached and a have a bunch of free memory and shared buffers? Could this be a postgres issue? Thank you in advance, Ioana -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Implementing thick/fat databases
I've been following a few blogs (http://database-programmer.blogspot.com/, http://thehelsinkideclaration.blogspot.com/) that make a very compelling argument, in my opinion, to move as much business/transactional logic as possible into the database, so that client applications become little more than moving data into and out of the database using a well-defined API, most commonly (but not necessarily) through the use of stored procedures. Although there seems to be a lot of discussion out there of the reasons why one might want to do this, I'm really at a loss for finding good, concrete examples of how to do it. Consequently, I'm hoping that somebody can share their experience(s), or point me to some examples, of doing this with PostgreSQL. I'd consider myself fairly well-versed in using the various features of PostgreSQL to enforce data integrity, but much less so for implementing transactional logic. To focus on a more concrete example, let's consider adding a financial transaction to the database. The traditional way to do this, with the business logic in the application layer, leaves us with two steps: insert the transaction header, then insert the line items: BEGIN; INSERT INTO transaction (id, date, description) VALUES (1, CURRENT_DATE, 'Transaction 1'); INSERT INTO line_item (transaction_id, account_id, amount) VALUES (1, 1, 50), (1, 2, -50); END; Now if we start moving this logic to the database, we'd have something like: BEGIN; SELECT create_transaction(1, current_date, 'Transaction 1'); SELECT create_line_item(1, 1, 50); SELECT create_line_item(1, 1, -50); END; But we've actually taken a step back, since we're making a round-trip to the database for each line item. That could be resolved by doing: BEGIN; SELECT create_transaction(1, current_date, 'Transaction 1'); SELECT create_line_item(transaction_id, account_id, amount) FROM (VALUES (1, 1, 50), (1, 2, -50)) AS line_item (transaction_id, account_id, amount); END; Better, but still not good, since we're invoking the function for each individual line item, which ultimately means separate INSERTs for each one. What we'd want is something like: BEGIN; SELECT create_transaction(1, current_date, 'Transaction 1'); SELECT create_line_items(((1, 1, 50), (1, 2, -50))); END; But this still falls short, since we're still basically managing the transaction in the application layer. The holy grail, so to speak, would be: SELECT create_transaction(1, current_date, 'Transaction 1', ((1, 50), (2, -50))); Perhaps I just need to spend more time digging through the documentation, but I really have no idea how to do something like this, or if it's even possible. I'm really hoping someone can provide an example, point me to some resources, or even just share their real-world experience of doing something like this. It would be very much appreciated. Thanks. Karl Nack Futurity, Inc 5121 N Ravenswood Ave Chicago, IL 60640 773-506-2007 -- 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] Why do I have reading from the swap partition?
On Fri, Jul 22, 2011 at 12:19 PM, Ioana Danes ioanasoftw...@yahoo.ca wrote: I do a select * from a 8 GB table (a different one then the one used in the query). At a point it starts using swap space on disk. Once it starts swapping I still let it run for couple of minutes and the I stop it (CTRL+C). Are you running psql on the same machine? My guess is that psql is what's swapping. Try running: select count(*) from (select * from mybigfreakingtable); and see if you start hitting swap like that. -- 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] Why do I have reading from the swap partition?
On Fri, 22 Jul 2011 11:19:13 -0700 (PDT), Ioana Danes wrote: Hi Everyone, I am trying to debug a slowness that is happening on one of my production sites and I would like to ask you for some help. This is my environment: --- Dedicated server running: SUSE Linux Enterprise Server 11 (x86_64): VERSION = 11 PATCHLEVEL = 1 RAM = 16GB Postgres 9.0.3: shared_buffers = 4GB work_mem = 2MB maintenance_work_mem = 2GB wal_buffers = 1MB checkpoint_segments =16 effective_cache_size = 8GB And this is my scenario: I have a table with 16 million records and few indexes for that table. I also have a query from that table (few filters no joins) that returns 6.000 records. I have the proper indexes and the plan looks good. I don't think the query or the table structure are important that is why I did not post them. I reboot the server and start postgres: I run a query first time and it takes ~ 2.5 seconds I run the same query for the second time and it takes 1 sec (because it is cached) All good here. Now I reboot the server again and start postgres: I do a select * from a 8 GB table (a different one then the one used in the query). At a point it starts using swap space on disk. Once it starts swapping I still let it run for couple of minutes and the I stop it (CTRL+C). After that I have 14 GB free memory and in postgres I only have about 3 buffers used in pg_buffercache, the rest up to 524288 being empty. If I run my query again then the query takes 60 seconds and I notice reads from the swap partition. Now my question is why would I have a read from the swap partition when using a table that was not accessed since restart so it is not cached and a have a bunch of free memory and shared buffers? Could this be a postgres issue? Thank you in advance, Ioana Is this big read. This what I can image you read big bunch of data, those data filled memory so other parts of applications ware swapped, when you execute next query, system need to revoke those, as well If I remember good SysV memory may be swapped to, so If you ate whole anonymous memory to keep query result then rest of SysV buffers ware swapped. Please bear in mind when You use sequence scan PG uses rings so You will not touch all shared buffers. Regards, Radek -- 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] Implementing thick/fat databases
Karl Nack wrote: I've been following a few blogs (http://database-programmer.blogspot.com/, http://thehelsinkideclaration.blogspot.com/) that make a very compelling argument, in my opinion, to move as much business/transactional logic as possible into the database, so that client applications become little more than moving data into and out of the database using a well-defined API, most commonly (but not necessarily) through the use of stored procedures. I strongly agree with that design philosophy. One principle is that the buck stops with the database and that regardless of what the application does, any business logic should be enforced by the database itself. Another principle is to treat the database like a code library, where the tables are its internal variables and its public API is stored procedures. Using stored procedures means you can interact with the database from your application in the same way your application interacts with itself, meaning with parameterized routine calls. snip To focus on a more concrete example, let's consider adding a financial transaction to the database. The traditional way to do this, with the business logic in the application layer, leaves us with two steps: insert the transaction header, then insert the line items: BEGIN; INSERT INTO transaction (id, date, description) VALUES (1, CURRENT_DATE, 'Transaction 1'); INSERT INTO line_item (transaction_id, account_id, amount) VALUES (1, 1, 50), (1, 2, -50); END; snip Anything intended to be a single transaction can be a single stored procedure. The code is something like this (out of my head, adjust to make it correct): FUNCTION financial_trans (trans_id, when, desc, dest_acct, src_acct, amt) BEGIN INSERT INTO transaction (id, date, description) VALUES (trans_id, when, desc); INSERT INTO line_item (transaction_id, account_id, amount) VALUES (trans_id, dest_acct, amt), (trans_id, src_acct, -amt); END; SELECT financial_trans( 1, CURRENT_DATE, 'Transaction 1', 1, 2, 50 ); But this still falls short, since we're still basically managing the transaction in the application layer. The holy grail, so to speak, would be: SELECT create_transaction(1, current_date, 'Transaction 1', ((1, 50), (2, -50))); Well, not quite, because specifying the number 50 twice would be ridiculous for such a non-generic function; you can calculate the -50 from it in the function. Perhaps I just need to spend more time digging through the documentation, but I really have no idea how to do something like this, or if it's even possible. I'm really hoping someone can provide an example, point me to some resources, or even just share their real-world experience of doing something like this. It would be very much appreciated. A general rule of thumb, however you would design a routine in a normal programming language, try to do it that way in PL/PgSQL, assuming that PL/PgSQL is a competent language, and then tweak to match what you actually can do. -- Darren Duncan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Dropping extensions
In postgres 9.1 I have created 2 extensions, veil and veil_demo. When I install veil, it creates a default (not very useful) version of a function: veil_init(). When I create veil_demo, it replaces this version of the function with it's own (useful) version. If I drop the extension veil_demo, I am left with the veil_demo version of veil_init(). Is this a feature or a bug? Is there a work-around? Thanks. __ Marc signature.asc Description: This is a digitally signed message part
[GENERAL] Update columns in same table from update trigger?
Here's what I want to do: I have a master table that has 2 columns: idcol1 and idcol2, where idcol2 is equivalent to idcol1 Table: color_eq idcol1 idcol2 1 1 2 2 2 3 Table: warehouse idcol qty 1 10 2 20 if I execute update warehouse set qty=10 where idcolor=3, I want the trigger to search table color_eq for idcol2=3, picks its corresponding idcol1 and update the table warehouse with idcol1. The problem I'm facing is that the trigger before update won't execute if there isn't a row with idcol=3 in the table warehouse. Here's my code: CREATE OR REPLACE FUNCTION update_warehouse() returns trigger AS ' declare idcolmaestro float:=0; BEGIN select into a idcolor1 from color_eq where idcolor2=old.idcolor; if a is null then a=old.idcolor; end if; new.idcolor=a; return new; END; ' LANGUAGE 'plpgsql' VOLATILE; CREATE TRIGGER update_warehouse_trigger before UPDATE ON warehouse FOR EACH ROW EXECUTE PROCEDURE update_warehouse(); Any help would be greatly appreciated!
Re: [GENERAL] interesting finding on order by behaviour
On Jul 22, 12:20 pm, scott_r...@elevated-dev.com (Scott Ribe) wrote: On Jul 22, 2011, at 11:11 AM, Samuel Hwang wrote: results = SQL Server 2008 R2 (with case insensitive data, the ordering follows ASCII order) f1 --- AbC abc ABc cde CdE Well, if it's case insensitive, then AbC abc ABc are all equal, so any order for those 3 would be correct... -- Scott Ribe scott_r...@elevated-dev.comhttp://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general Sorry I didn't make it clear, the interesting part is how PostgreSQL sorts data. The server encoding is set to UTF8 and collation is united states.1252 The client encoding is Unicode. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Locking several tables within one transaction
There are two places from which my database can be accessed: 1) PHP code, which only read data from db and sends it to users' browsers; 2) C++ code, which writes data to db one time per 15 minutes (one huge transaction which affects all the tables in db); Both pieces of code use local socket to access to Postgres db, more over, they both use completely the same connection string (same username etc). Goal is: during C++ code's transaction (duration is up to ~20 seconds) PHP code should not read ANY data from db. In other words, C++ code must have exclusive access. The solution I've found for a while (SQL commands, which C++ code should call): BEGIN; LOCK TABLE reports IN ACCESS EXCLUSIVE MODE; LOCK TABLE region_reports IN ACCESS EXCLUSIVE MODE; -- locking all the other tables here INSERT INTO reports (user_id, data) VALUES ($1::integer, $2:varchar); --now I get serial value 'id' from previous INSERT and use it as $1 below INSERT INTO region_reports (report_id, data) VALUES ($1::integer, $2:varchar); --inserting into all the other tables here COMMIT; So, my question is: is there guarantee no data will be read from region_reports table by PHP code between two 'LOCK TABLE' commands shown (i.e. before 'LOCK TABLE region_reports' command)? In other words: is there guarantee all the LOCK TABLE commands will be executed simultaneously (i.e. no other commands will be executed between them)? Actually, it is not so nice way to lock all the tables manually, so using single pg_advisory_lock() would be better solution, but if I understand correctly, advisory lock works within single session, but two places from which my database can be accessed will use different session. Am I right? Is there more laconic solution for my goal? Thank you very much. Ilia Lilov. -- 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] Implementing thick/fat databases
On 07/22/11 4:11 PM, Darren Duncan wrote: Karl Nack wrote: I've been following a few blogs (http://database-programmer.blogspot.com/, http://thehelsinkideclaration.blogspot.com/) that make a very compelling argument, in my opinion, to move as much business/transactional logic as possible into the database, so that client applications become little more than moving data into and out of the database using a well-defined API, most commonly (but not necessarily) through the use of stored procedures. I strongly agree with that design philosophy. One principle is that the buck stops with the database and that regardless of what the application does, any business logic should be enforced by the database itself. Another principle is to treat the database like a code library, where the tables are its internal variables and its public API is stored procedures. Using stored procedures means you can interact with the database from your application in the same way your application interacts with itself, meaning with parameterized routine calls. the alternative 'modern' architecture is to implement the business logic in a webservices engine that sits in front of the database, and only use stored procedures for things that get significant performance boost where that is needed to meet your performance goals.. Only this business logic is allowed to directly query the operational database. The business logic in this middle tier still relies on the database server for data integrity and such.The presentation layer is implemented either in a conventional client application or in a webserver (not to be confused with the webservices) so you have user - browser - webserver/presentation layer - webservices/business logic - database The main rationale for this sort of design pattern is that large complex business logic implemented in SQL stored procedures can be rather difficult to develop and maintain -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- 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] Locking several tables within one transaction
On Fri, Jul 22, 2011 at 9:45 AM, Ilia Lilov lilo...@gmail.com wrote: There are two places from which my database can be accessed: 1) PHP code, which only read data from db and sends it to users' browsers; 2) C++ code, which writes data to db one time per 15 minutes (one huge transaction which affects all the tables in db); Both pieces of code use local socket to access to Postgres db, more over, they both use completely the same connection string (same username etc). Goal is: during C++ code's transaction (duration is up to ~20 seconds) PHP code should not read ANY data from db. In other words, C++ code must have exclusive access. The solution I've found for a while (SQL commands, which C++ code should call): BEGIN; LOCK TABLE reports IN ACCESS EXCLUSIVE MODE; LOCK TABLE region_reports IN ACCESS EXCLUSIVE MODE; -- locking all the other tables here INSERT INTO reports (user_id, data) VALUES ($1::integer, $2:varchar); --now I get serial value 'id' from previous INSERT and use it as $1 below INSERT INTO region_reports (report_id, data) VALUES ($1::integer, $2:varchar); --inserting into all the other tables here COMMIT; So, my question is: is there guarantee no data will be read from region_reports table by PHP code between two 'LOCK TABLE' commands shown (i.e. before 'LOCK TABLE region_reports' command)? In other words: is there guarantee all the LOCK TABLE commands will be executed simultaneously (i.e. no other commands will be executed between them)? No, they are executed one after the other. It's possible for another connection to access the second table right before it's locked. Is it possible that running ALL your transactions in serializable mode would be a solution? I think we need a better explanation of what your business logic / case is here. -- 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] Implementing thick/fat databases
John R Pierce wrote: On 07/22/11 4:11 PM, Darren Duncan wrote: Karl Nack wrote: I've been following a few blogs (http://database-programmer.blogspot.com/, http://thehelsinkideclaration.blogspot.com/) that make a very compelling argument, in my opinion, to move as much business/transactional logic as possible into the database, so that client applications become little more than moving data into and out of the database using a well-defined API, most commonly (but not necessarily) through the use of stored procedures. I strongly agree with that design philosophy. One principle is that the buck stops with the database and that regardless of what the application does, any business logic should be enforced by the database itself. Another principle is to treat the database like a code library, where the tables are its internal variables and its public API is stored procedures. Using stored procedures means you can interact with the database from your application in the same way your application interacts with itself, meaning with parameterized routine calls. the alternative 'modern' architecture is to implement the business logic in a webservices engine that sits in front of the database, and only use stored procedures for things that get significant performance boost where that is needed to meet your performance goals.. Only this business logic is allowed to directly query the operational database. The business logic in this middle tier still relies on the database server for data integrity and such.The presentation layer is implemented either in a conventional client application or in a webserver (not to be confused with the webservices) so you have user - browser - webserver/presentation layer - webservices/business logic - database The main rationale for this sort of design pattern is that large complex business logic implemented in SQL stored procedures can be rather difficult to develop and maintain I should clarify that the primary thing I support, with respect to putting it in the database, is the business rules/constraints, because the buck stops there. It should not be possible for any database user lacking in data-definition privileges to circumvent any of the business rules. So one can not circumvent by using a generic SQL shell, for example. As for the rest, yes I agree with you that this doesn't have to actually be in the database, though from a standpoint of good design principles, all of the business logic should still be in one place, next to if not in the database, and that all database access should go through the business logic layer. All logic that is not specific to an application should go in a logic layer, so it is shared by multiple applications whether web or command-line or whatever, and so then the application is largely just a user interface. In other words, thinking in the Model-View-Controller paradigm, the Model should be fat and the Controller should be thin. -- Darren Duncan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
