[GENERAL] Partitioning on Table with Foreign Key
I am referring to http://www.postgresql.org/docs/current/static/ddl-partitioning.html I have the follow table : table lot = id | date | 1 2010-01-19 13:53:57.713 2 2010-01-20 11:34:11.856 table measurement = id | fk_lot_id | 12 22 32 42 52 62 72 82 table measurement will have a *lot* of row (millions). I want to speed up write and read access. Hence, I use partition technique. CREATE TABLE measurement_y2006m02 ( CHECK ( date = DATE '2006-02-01' AND date DATE '2006-03-01' ) ) INHERITS (measurement); Opps! But measurement do not have date column. How I can refer to measurement's lot's date? Thanks and Regards Yan Cheng CHEOK -- 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] About partitioning
http://www.pubbs.net/pgsql/201001/16503/ -- 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] About partitioning
2010/1/21 Grzegorz Jaśkiewicz gryz...@gmail.com: http://www.pubbs.net/pgsql/201001/16503/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general Grzegorz, Thanks for the reference, which officially is here: http://archives.postgresql.org/pgsql-general/2010-01/msg00331.php I confirm it wasn't easy to find that out. Maybe you can add the snippet into the Postgresql Wiki. One thing to add here is that I would advise against the CREATE TABLE...INHERITS. I find much more useful: -- code CREATE TABLE partition ( LIKE master INCLUDING INDEXES, CHECK( the condition ) ); ALTER TABLE partition INHERIT master; -- end code because very often I still need the indexes in the partitions. And in case you need the (other) constraints and the defaults, uyou can ask for it with the INCLUDING clause. And, BTW: EXECUTE 'INSERT INTO '||partition-table-name||' SELECT $1.*' USING NEW; -- Vincenzo Romano NotOrAnd Information Technologies NON QVIETIS MARIBVS NAVTA PERITVS -- 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] 64bits or 32 bits on ESX?
2010/1/21 Bjørn T Johansen b...@havleik.no: We are going to be setting up a PostgreSQL server on a guest under VMWare ESX 4... Is there any performance improvement by choosing 64bits Linux over 32bits Linux as the guest OS or is it almost the same? How much resources do you plan to give the machine? If you're setting up a very constrained machine with little memory, go with the 32-bit version. It'll use slightly less memory which can be important if you're constrained. If you're setting up a medium size or bigger machine (2Gb+ memory), use a 64-bit version. If you plan you may eventually need to increase the size of the machine, go with the 64-bit one from the beginning, because changing from 32 to 64 bit requires dump/reload. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- 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] About partitioning
2010/1/21 Vincenzo Romano vincenzo.rom...@notorand.it: And, BTW: EXECUTE 'INSERT INTO '||partition-table-name||' SELECT $1.*' USING NEW; won't work on 8.3 where I need it however :) -- GJ -- 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] 64bits or 32 bits on ESX?
On Thu, 21 Jan 2010 10:43:31 +0100 Magnus Hagander mag...@hagander.net wrote: 2010/1/21 Bjørn T Johansen b...@havleik.no: We are going to be setting up a PostgreSQL server on a guest under VMWare ESX 4... Is there any performance improvement by choosing 64bits Linux over 32bits Linux as the guest OS or is it almost the same? How much resources do you plan to give the machine? If you're setting up a very constrained machine with little memory, go with the 32-bit version. It'll use slightly less memory which can be important if you're constrained. If you're setting up a medium size or bigger machine (2Gb+ memory), use a 64-bit version. If you plan you may eventually need to increase the size of the machine, go with the 64-bit one from the beginning, because changing from 32 to 64 bit requires dump/reload. ok... we were thinking about 2GB RAM, so maybe we should try 64bit :) BTJ -- 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] 64bits or 32 bits on ESX?
In response to Magnus Hagander : 2010/1/21 Bjørn T Johansen b...@havleik.no: We are going to be setting up a PostgreSQL server on a guest under VMWare ESX 4... Is there any performance improvement by choosing 64bits Linux over 32bits Linux as the guest OS or is it almost the same? How much resources do you plan to give the machine? If you're setting up a very constrained machine with little memory, go with the 32-bit version. It'll use slightly less memory which can be important if you're constrained. If you're setting up a medium size or bigger machine (2Gb+ memory), use a 64-bit version. If you plan you may eventually need to increase Really? With ONLY 2Gb? Why? What is the performance improvement, with 64Bit all pointers and so on needs more memory so i'm expecting lesser memory for the data. I'm wrong? Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- 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] 64bits or 32 bits on ESX?
On Thu, Jan 21, 2010 at 12:45, A. Kretschmer andreas.kretsch...@schollglas.com wrote: In response to Magnus Hagander : 2010/1/21 Bjørn T Johansen b...@havleik.no: We are going to be setting up a PostgreSQL server on a guest under VMWare ESX 4... Is there any performance improvement by choosing 64bits Linux over 32bits Linux as the guest OS or is it almost the same? How much resources do you plan to give the machine? If you're setting up a very constrained machine with little memory, go with the 32-bit version. It'll use slightly less memory which can be important if you're constrained. If you're setting up a medium size or bigger machine (2Gb+ memory), use a 64-bit version. If you plan you may eventually need to increase Really? With ONLY 2Gb? Why? What is the performance improvement, with 64Bit all pointers and so on needs more memory so i'm expecting lesser memory for the data. I'm wrong? While the theoretical limit is a bit higher, the fact that you have to reinstall once you've realized you wanted to add a little bit more memory More important to me at least :-) -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- 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] 64bits or 32 bits on ESX?
On Thu, 21 Jan 2010 13:01:29 +0100 Magnus Hagander mag...@hagander.net wrote: On Thu, Jan 21, 2010 at 12:45, A. Kretschmer andreas.kretsch...@schollglas.com wrote: In response to Magnus Hagander : 2010/1/21 Bjørn T Johansen b...@havleik.no: We are going to be setting up a PostgreSQL server on a guest under VMWare ESX 4... Is there any performance improvement by choosing 64bits Linux over 32bits Linux as the guest OS or is it almost the same? How much resources do you plan to give the machine? If you're setting up a very constrained machine with little memory, go with the 32-bit version. It'll use slightly less memory which can be important if you're constrained. If you're setting up a medium size or bigger machine (2Gb+ memory), use a 64-bit version. If you plan you may eventually need to increase Really? With ONLY 2Gb? Why? What is the performance improvement, with 64Bit all pointers and so on needs more memory so i'm expecting lesser memory for the data. I'm wrong? While the theoretical limit is a bit higher, the fact that you have to reinstall once you've realized you wanted to add a little bit more memory More important to me at least :-) I also thought that the fact the 64bit system can move more data in parallell would also make the system faster BTJ -- 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] 64bits or 32 bits on ESX?
2010/1/21 Bjørn T Johansen b...@havleik.no: I also thought that the fact the 64bit system can move more data in parallell would also make the system faster That's true ad the chip level (registers and cache). Anything else depends on the surrounding hardware (design and implementation). A 32bit system with a good SAS subsystem can outperform a 64bit one with poor PATA disks. -- Vincenzo Romano NotOrAnd Information Technologies NON QVIETIS MARIBVS NAVTA PERITVS -- 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] 64bits or 32 bits on ESX?
On Thursday 21 January 2010 13.01:29 Magnus Hagander wrote: Really? With ONLY 2Gb? Why? What is the performance improvement, with 64Bit all pointers and so on needs more memory so i'm expecting lesser memory for the data. I'm not in any way a performance expert, but IIRC 32 bit Linux has to use some quirky logic (HIGHMEM) to address more than 2G physical memory, which might reduce performance somewhat. So 2G is fine, but ... Aside of memory: if performance is an issue, and assuming your db is bigger than available memory: have you thought about the physical disk layout? Having indices on different disks (physical disks matter, here!) from data, splitting WAL from tablespaces etc. might all be much more important for your performance than the 32bit vs. 64bit issue. cheers -- vbi signature.asc Description: This is a digitally signed message part.
[GENERAL] What locking mechanism is used for database backup and restore and Master-Slave Replication?
Hi, In reading the documentation it states that the SQL dump backup does not block other operations on the database while it is working. I presume that while a restore is occurring that no reads or updates are allowed against the restored database. What locking mechanism is used for Master-Slave Replication? Thanks, John J. Mitchell
Re: [GENERAL] What locking mechanism is used for database backup and restore and Master-Slave Replication?
On Thu, Jan 21, 2010 at 1:12 PM, John Mitchell mitchellj...@gmail.com wrote: Hi, In reading the documentation it states that the SQL dump backup does not block other operations on the database while it is working. yes, pg_dump opens serializable transaction thus guarantees data to be the exact snapshot (as opposed to the default isolation level, which is called 'read commited' not without reason). I presume that while a restore is occurring that no reads or updates are allowed against the restored database. nope, what restoring does, is just running all the commands in the pg_dump (whether it is binary or textual). So as soon as the database is created, it is treated just as any connection, thus allows you to connect and use it. What locking mechanism is used for Master-Slave Replication? master slave that's introduced in what's to be 9.0 (aka 8.5), uses WAL shipping. So it doesn't require any extra locking. -- GJ -- 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] What locking mechanism is used for database backup and restore and Master-Slave Replication?
So am I to presume that the current stable version of postgres (before 8.5) does require extra locking? John 2010/1/21 Grzegorz Jaśkiewicz gryz...@gmail.com On Thu, Jan 21, 2010 at 1:12 PM, John Mitchell mitchellj...@gmail.com wrote: Hi, In reading the documentation it states that the SQL dump backup does not block other operations on the database while it is working. yes, pg_dump opens serializable transaction thus guarantees data to be the exact snapshot (as opposed to the default isolation level, which is called 'read commited' not without reason). I presume that while a restore is occurring that no reads or updates are allowed against the restored database. nope, what restoring does, is just running all the commands in the pg_dump (whether it is binary or textual). So as soon as the database is created, it is treated just as any connection, thus allows you to connect and use it. What locking mechanism is used for Master-Slave Replication? master slave that's introduced in what's to be 9.0 (aka 8.5), uses WAL shipping. So it doesn't require any extra locking. -- GJ -- John J. Mitchell
Re: [GENERAL] 64bits or 32 bits on ESX?
I have seen no difference in performance. Now, if you want large memory for a DB server, and you should, 64 is the way to go. I'm currently running CentOS 5 64-Bit vm's for the SaaS app I support. Works great on ESX 4U1. -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 512-248-2683E-Mail: l...@lerctr.org US Mail: 430 Valona Loop, Round Rock, TX 78681-3893 -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Bjørn T Johansen Sent: Thursday, January 21, 2010 1:07 AM To: pgsql-general@postgresql.org Subject: [GENERAL] 64bits or 32 bits on ESX? We are going to be setting up a PostgreSQL server on a guest under VMWare ESX 4... Is there any performance improvement by choosing 64bits Linux over 32bits Linux as the guest OS or is it almost the same? Regards, BTJ -- --- Bjørn T Johansen b...@havleik.no --- Someone wrote: I understand that if you play a Windows CD backwards you hear strange Satanic messages To which someone replied: It's even worse than that; play it forwards and it installs Windows --- -- 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] What locking mechanism is used for database backup and restore and Master-Slave Replication?
depends on what sort of replication you are going to use really. Most are based on triggers. So they have a bit more impact on performance. As far as locking goes, postgresql is very conservative with locks, ie - it won't abuse them, unlike for instance mysql. So I don't know whether you are just worried about performance in general, or locking in particular. But impact of likes of slony isn't that big, but obviously once the new version is out - it will be even less of a problem, since postgresql already produces transactional log for recovering, and pretty much the same log is going to be used for replication. 2010/1/21 John Mitchell mitchellj...@gmail.com: So am I to presume that the current stable version of postgres (before 8.5) does require extra locking? John 2010/1/21 Grzegorz Jaśkiewicz gryz...@gmail.com On Thu, Jan 21, 2010 at 1:12 PM, John Mitchell mitchellj...@gmail.com wrote: Hi, In reading the documentation it states that the SQL dump backup does not block other operations on the database while it is working. yes, pg_dump opens serializable transaction thus guarantees data to be the exact snapshot (as opposed to the default isolation level, which is called 'read commited' not without reason). I presume that while a restore is occurring that no reads or updates are allowed against the restored database. nope, what restoring does, is just running all the commands in the pg_dump (whether it is binary or textual). So as soon as the database is created, it is treated just as any connection, thus allows you to connect and use it. What locking mechanism is used for Master-Slave Replication? master slave that's introduced in what's to be 9.0 (aka 8.5), uses WAL shipping. So it doesn't require any extra locking. -- GJ -- John J. Mitchell -- GJ -- 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: What locking mechanism is used for database backup and restore and Master-Slave Replication?
On 01/21/10 16:09, John Mitchell wrote: So am I to presume that the current stable version of postgres (before 8.5) does require extra locking? There is currently (before 8.5) no official replication mechanism in PostgreSQL. There are some 3rd party implementations, for which information can be gathered here: http://www.postgresql.org/docs/current/interactive/high-availability.html http://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling 2010/1/21 Grzegorz Jaśkiewicz gryz...@gmail.com mailto:gryz...@gmail.com On Thu, Jan 21, 2010 at 1:12 PM, John Mitchell mitchellj...@gmail.com mailto:mitchellj...@gmail.com wrote: Hi, In reading the documentation it states that the SQL dump backup does not block other operations on the database while it is working. yes, pg_dump opens serializable transaction thus guarantees data to be the exact snapshot (as opposed to the default isolation level, which is called 'read commited' not without reason). I presume that while a restore is occurring that no reads or updates are allowed against the restored database. nope, what restoring does, is just running all the commands in the pg_dump (whether it is binary or textual). So as soon as the database is created, it is treated just as any connection, thus allows you to connect and use it. What locking mechanism is used for Master-Slave Replication? master slave that's introduced in what's to be 9.0 (aka 8.5), uses WAL shipping. So it doesn't require any extra locking. -- GJ -- John J. Mitchell -- 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] Partitioning on Table with Foreign Key
Make sense to me. Thanks for the advice. I will try that out. Thanks and Regards Yan Cheng CHEOK --- On Thu, 1/21/10, Vick Khera vi...@khera.org wrote: From: Vick Khera vi...@khera.org Subject: Re: [GENERAL] Partitioning on Table with Foreign Key To: Yan Cheng Cheok ycch...@yahoo.com Cc: pgsql-general@postgresql.org Date: Thursday, January 21, 2010, 11:30 PM On Thu, Jan 21, 2010 at 3:46 AM, Yan Cheng Cheok ycch...@yahoo.com wrote: table measurement will have a *lot* of row (millions). I want to speed up write and read access. Hence, I use partition technique. CREATE TABLE measurement_y2006m02 ( CHECK ( date = DATE '2006-02-01' AND date DATE '2006-03-01' ) ) INHERITS (measurement); Opps! But measurement do not have date column. How I can refer to measurement's lot's date? Split on your lot ID number instead of date, using a modulo operation. I've done this by splitting a table 100 ways using the constraing (id % 100 == N) for N = 0 .. 99. We don't know what your data retention or distribution needs are, so it is hard to offer solid advice. -- 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] Partitioning on Table with Foreign Key
On Thu, Jan 21, 2010 at 3:46 AM, Yan Cheng Cheok ycch...@yahoo.com wrote: table measurement will have a *lot* of row (millions). I want to speed up write and read access. Hence, I use partition technique. CREATE TABLE measurement_y2006m02 ( CHECK ( date = DATE '2006-02-01' AND date DATE '2006-03-01' ) ) INHERITS (measurement); Opps! But measurement do not have date column. How I can refer to measurement's lot's date? Split on your lot ID number instead of date, using a modulo operation. I've done this by splitting a table 100 ways using the constraing (id % 100 == N) for N = 0 .. 99. We don't know what your data retention or distribution needs are, so it is hard to offer solid advice. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] \dt+ sizes don't include TOAST data
The sizes displayed by \dt+ in version 8.4.2 do not take TOAST tables into account, presumably because the pg_relation_size does not reflect that, either. I think this is a bit surprising. From a user perspective, these are part of the table storage (I understand that the indices might be a different story, but TOAST table are a fairly deep implementation detail and should perhaps be hidden here). -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Currently connected users...
I'm a beginner Postgres user, and need quick hint from someone. How could I know which users are currently connected to postgres instance? Thanks, DP.
Re: [GENERAL] Currently connected users...
Dhimant Patel drp4...@gmail.com wrote: I'm a beginner Postgres user, and need quick hint from someone. How could I know which users are currently connected to postgres instance? You can use pg_stat_activity: select * from pg_stat_activity Or, if you need only the usernames: select usename from pg_stat_activity ; Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) If I was god, I would recompile penguin with --enable-fly. (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- 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] Currently connected users...
Dhimant Patel, 21.01.2010 17:40: I'm a beginner Postgres user, and need quick hint from someone. How could I know which users are currently connected to postgres instance? http://www.postgresql.org/docs/current/static/monitoring.html More precisely: http://www.postgresql.org/docs/current/static/monitoring-stats.html#MONITORING-STATS-VIEWS Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Changing FS when full
Hi, I have a big database on FS1, now almost full. Have space on FS2, where I created a tablespace and moved every table and index to it. Still, lots of space used on FS1. The problem is not pg_xlog, but base: # du -hs base/105658651/* | fgrep G 1,0Gbase/105658651/106377323 1,0Gbase/105658651/106377323.1 1,0Gbase/105658651/106377323.2 1,0Gbase/105658651/106377323.3 1,0Gbase/105658651/106377323.4 1,0Gbase/105658651/125520217 1,0Gbase/105658651/127352052 1,0Gbase/105658651/127352052.1 1,0Gbase/105658651/127352052.2 1,0Gbase/105658651/127352052.3 1,0Gbase/105658651/127352052.4 1,0Gbase/105658651/127352052.5 Unfortunately no volume management is available, so I can't move disks from FS2 to FS1. I could play soft links tricks, but I'm afraid of paying the FS-traversal penalty on each file access (is that right?). So, any way of instructing PG (8.1 if that matters) to place those files elsewhere without an initdb? Thanks! Fernando. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] db cluster location
Hi all, Is there a query I can use to find the location of a db cluster? I've found a term that looks promising (\d+ pg_database;), but can't seem to tease a directory path from it. The docs list several common locations, but mine doesn't appear to be one of them. Searching my local file system for data yields too many hits for that approach to be useful. I installed pg via Macports. Thanks in advance! Scott -- 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] db cluster location
On Jan 21, 2010, at 10:00 AM, Scott Frankel wrote: Hi all, Is there a query I can use to find the location of a db cluster? I've found a term that looks promising (\d+ pg_database;), but can't seem to tease a directory path from it. The docs list several common locations, but mine doesn't appear to be one of them. show data_directory is probably what you're looking for. (also useful: show config_file, show hba_file, show ident_file). Cheers, Steve -- 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 me about postgreSql code
Elian Laura wrote: i understand, but why my teacher wrote in his paper..Probably the most obvious case is a database engine where the user defines, at run time, if a field is integer, char, float, etc. but, it is not necessary to compile the program again. All this felxibility must be . I have no idea why your teacher wrote that. You should ask them. in a relational database, all data fields are typed. the only user input that Postgres processes is the SQL language, and thats a full blown complex language parser, the internals of which I have little desire to take apart. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] db cluster location
Hi all, Is there a query I can use to find the location of a db cluster? I've found a term that looks promising (\d+ pg_database;), but can't seem to tease a directory path from it. The docs list several common locations, but mine doesn't appear to be one of them. Searching my local file system for data yields too many hits for that approach to be useful. I installed pg via Macports. Thanks in advance! Scott -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PgSQL problem: How to split strings into rows
I have a table X with some column K consisting of whitespace-separated words. Is there some SELECT query that will list all these words (for the entire table) so that there's one word per row in the returned table? E.g. If the table X is K - foo bar baz quux frobozz eeny meeny miny moe ...I want the result of this query to be foo bar baz quux frobozz eeny meeny miny moe How can I do this? (I have a slight preference for solutions that will work with version 8.2, but I'm interested in any solution to the problem.) TIA! ~K
Re: [GENERAL] db cluster location
Scott Frankel wrote on 21.01.2010 18:34: Hi all, Is there a query I can use to find the location of a db cluster? SELECT name, setting FROM pg_settings WHERE category = 'File Locations'; You need to be connected as the superuser (usually postgres) -- 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] PgSQL problem: How to split strings into rows
Kynn Jones wrote on 21.01.2010 19:49: I have a table X with some column K consisting of whitespace-separated words. Is there some SELECT query that will list all these words (for the entire table) so that there's one word per row in the returned table? E.g. If the table X is K - foo bar baz quux frobozz eeny meeny miny moe ...I want the result of this query to be foo bar baz quux frobozz eeny meeny miny moe How can I do this? (I have a slight preference for solutions that will work with version 8.2, but I'm interested in any solution to the problem.) Don't know if this will work with 8.3: select regexp_split_to_table(k, ' ') from x; Thomas -- 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] PgSQL problem: How to split strings into rows
Kynn Jones kyn...@gmail.com wrote:
I have a table X with some column K consisting of whitespace-separated words.
Is there some SELECT query that will list all these words (for the entire
table) so that there's one word per row in the returned table? E.g. If the
table X is
K
-
foo bar baz
quux frobozz
eeny meeny
miny moe
...I want the result of this query to be
foo
bar
baz
quux
frobozz
eeny
meeny
miny
moe
How can I do this? (I have a slight preference for solutions that will work
with version 8.2, but I'm interested in any solution to the problem.)
With 8.4:
test=*# select string_to_array('foo bar bartz', ' ');
string_to_array
-
{foo,bar,bartz}
(1 Zeile)
Zeit: 23,390 ms
test=*# select unnest(string_to_array('foo bar bartz', ' '));
unnest
foo
bar
bartz
(3 Zeilen)
With 8.2:
You have to create a function unnest:
CREATE OR REPLACE FUNCTION unnest(ANYARRAY) RETURNS SETOF ANYELEMENT
LANGUAGE SQL AS $$SELECT $1[i] FROM
generate_series(array_lower($1,1),array_upper($1,1)) i;$$;
string_to_array() should work in 8.2 (i'm not really sure, but i think,
8.2 contains this funtion)
Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
If I was god, I would recompile penguin with --enable-fly. (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
--
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] PgSQL problem: How to split strings into rows
On Thu, 21 Jan 2010 13:49:45 -0500
Kynn Jones kyn...@gmail.com wrote:
I have a table X with some column K consisting of
whitespace-separated words. Is there some SELECT query that will
list all these words (for the entire table) so that there's one
word per row in the returned table? E.g. If the table X is
K
-
foo bar baz
quux frobozz
eeny meeny
miny moe
...I want the result of this query to be
foo
bar
baz
quux
frobozz
eeny
meeny
miny
moe
http://www.postgresql.org/docs/current/static/functions-array.html
string_to_array
select (string_to_array('tano pino gino', ' '))[i] from
generate_series(1, 3) s(i);
You'd get the idea... to get the length of the array you've
array_length.
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Error invalid byte sequence for encoding UTF8 on insert into BYTEA column
I am running Postgres 8.4.1 on Windows XP Professional Service Pack 3. My database is UTF8. My program code is written in Python, and to interface to Postgres I use mxODBC 3.0 and the PostgreSQL Unicode driver PSQLODBCW.DLL version 8.01.02.00 dated 31/01/2006. Today for the first time since upgrading to Postgres 8.4.1 I tried out part of the code which inserts some binary data into a table. The insert failed with the error invalid byte sequence for encoding UTF8. That is odd, because the column into which the insert was made is of type bytea: the data is meant to be binary data, not UTF8-encoded Unicode. When I was using Postgres 8.1.4 the same code worked. My code, the mxODBC code and the driver are all unaltered: only the Postgres version has changed.. Is there something I can tweak to get it to work on Postgres 8.4.1? I confirm that the driver option bytea as LO is checked. That has always been necessary in the past.
Re: [GENERAL] db cluster location
Exactly what I was looking for. Thanks! On Jan 21, 2010, at 10:50 AM, Thomas Kellerer wrote: Scott Frankel wrote on 21.01.2010 18:34: Hi all, Is there a query I can use to find the location of a db cluster? SELECT name, setting FROM pg_settings WHERE category = 'File Locations'; You need to be connected as the superuser (usually postgres) -- 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] ISO guidelines/strategies to guard injection attacks
On Tue, Jan 19, 2010 at 4:49 PM, Andy Colson a...@squeakycode.net wrote:
On 1/19/2010 3:39 PM, Andy Colson wrote:
On 1/19/2010 3:23 PM, Kynn Jones wrote:
I have a Perl CGI script (using DBD::Pg) that interfaces with a
server-side Pg database. I'm looking for general
guidelines/tools/strategies that will help me guard against SQL
injection attacks.
Any pointers/suggestions would be much appreciated.
~K
prepare your queries:
my $q = $db-prepare('select something from table where key = $1');
$q-execute(42);
and..
$db-do('update table set field = $1 where key = $2', undef, 'key', 42);
(*guessed at the do(). I think there is an undef in there, or something*)
-Andy
Also, add to that, in general, use Taint Mode. Perl wont trust data until
its been sanitized... and neither should you.
I can't get this to work in any way. At the end of this email, I post a
complete script that runs fine under Taint Mode, even though it DBI is being
passed tainted variables in various places.
Do I need to do anything else to force a failure with tainted data?
Demo script below; to run it, it requires four command-line arguments: the
name of a database, the name of a table in that database, the name of an
integer-type column in that table, and some integer. E.g., a run may look
like this:
$ perl -T demo_script.pl mydb mytable mycolum 42
1
1
1
1
1
1
NB: you will need to modify the user and password parameters in the call to
DBI-connect.
The important thing to note is that the connect, prepare, and execute
methods all receive tainted arguments, but run without any problem.
Furthermore, the subsequent fetchall_arrayref also runs without any
problem.
Hence, at least in this example, -T was no protection against SQL injection
attacks. Note, in particular, that the way that the $sql variable is
initialized is an ideal opportunity for an SQL injection attack.
#!/usr/bin/perl
use strict;
use warnings FATAL = 'all';
use DBI;
my $dbname = shift;
my $tablename = shift;
my $colname = shift;
my $id = shift;
my $sql = qq(SELECT * FROM $tablename WHERE $colname = \$1;);
my $connection_string = dbi:Pg:dbname=$dbname;
# when this script is run under -T, the output from all the following
# print statements is 1; if the script is *not* run under -T, then
# they are all 0.
print +(is_tainted($dbname) ? 1 : 0), \n;
print +(is_tainted($tablename) ? 1 : 0), \n;
print +(is_tainted($colname) ? 1 : 0), \n;
print +(is_tainted($id) ? 1 : 0), \n;
print +(is_tainted($connection_string) ? 1 : 0), \n;
print +(is_tainted($sql) ? 1 : 0), \n;
my $dbh = DBI-connect($connection_string,
kynn, undef,
+{
RaiseError = 1,
PrintError = 0,
PrintWarn = 0,
});
my $sth = $dbh-prepare($sql);
$sth-execute($id);
my $fetched = $sth-fetchall_arrayref;
sub is_tainted {
# this sub is adapted from Programming Perl, 3rd ed., p. 561
my $arg = shift;
my $empty = do {
no warnings 'uninitialized';
substr($arg, 0, 0);
};
local $@;
eval { eval # $empty };
return length($@) != 0;
}
~K
[GENERAL] Old/New
Hi I am getting a strange result when using the following - Select fluid_id into fluidid from p_id.processes where new.pump1 = 'True' and old.pump1 = 'False' or old.pump1 is null; The fluid_id return is fine when there is a single row. However with two rows, and updating only one of the rows, I quite often get the fluid_id for the other row. I am using an After Update trigger. Bob
Re: [GENERAL] Old/New
Bob Pawley rjpaw...@shaw.ca writes: I am getting a strange result when using the following - Select fluid_id into fluidid from p_id.processes where new.pump1 = 'True' and old.pump1 = 'False' or old.pump1 is null; The fluid_id return is fine when there is a single row. However with two rows, and updating only one of the rows, I quite often get the fluid_id for the other row. That WHERE condition isn't constraining the SELECT at all; you're getting the result from the first row in the table. I think you have some fundamental confusion about how to work with OLD and NEW in triggers. They're just rowtype variables, you do not need to select from the table to examine them. 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] array element replace ?
Is there a clever way to replace a single element in an array with another value? E.g. x = array['a','b','c','d']; I want to replace 'b' with 'x'. Thanks for any suggestions!
Re: [GENERAL] array element replace ?
2010/1/22 Gauthier, Dave dave.gauth...@intel.com: Is there a clever way to replace a single element in an array with another value? E.g. x = array[‘a’,’b’,’c’,’d’]; I want to replace ‘b’ with ‘x’. Not sure you can replace an array value with an array (which kind of is what you're asking in your example), but: http://www.postgresql.org/docs/8.4/static/arrays.html Specifically section 8.14.4. Modifying Arrays Cheers, Andrej -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/econ/notes/htmlmail.htm -- 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] Error invalid byte sequence for encoding UTF8 on insert into BYTEA column
On Thu, Jan 21, 2010 at 11:54:32AM -0800, Alan Millington wrote: Today for the first time since upgrading to Postgres 8.4.1 I tried out part of the code which inserts some binary data into a table. The insert failed with the error invalid byte sequence for encoding UTF8. That is odd, because the column into which the insert was made is of type bytea: the data is meant to be binary data, not UTF8-encoded Unicode. Inserting in bytea needs an extra level of escaping when the parameters are sent inline. See http://www.postgresql.org/docs/8.4/interactive/datatype-binary.html When I was using Postgres 8.1.4 the same code worked. My code, the mxODBC code and the driver are all unaltered: only the Postgres version has changed.. Is there something I can tweak to get it to work on Postgres 8.4.1? Very odd. The syntax for this hasn't changed in a long time. And I would have thought you'd be sending your paramters out of line anyway. Can you check that? Hope this helps, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [GENERAL] \dt+ sizes don't include TOAST data
Florian Weimer wrote: The sizes displayed by \dt+ in version 8.4.2 do not take TOAST tables into account, presumably because the pg_relation_size does not reflect that, either. I think this is a bit surprising. From a user perspective, these are part of the table storage (I understand that the indices might be a different story, but TOAST table are a fairly deep implementation detail and should perhaps be hidden here). As of last week there's a new pg_table_size available that does what you want here: http://archives.postgresql.org/pgsql-committers/2010-01/msg00288.php I don't believe \dt+ has been updated yet to use that though; that's worth considering for a minute, not sure anybody thought about it yet. -- Greg Smith2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.com -- 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] Changing FS when full
Fernando Schapachnik wrote: I could play soft links tricks, but I'm afraid of paying the FS-traversal penalty on each file access (is that right?). Compared to everything else that goes into I/O, symlink traversal overhead is pretty low. So, any way of instructing PG (8.1 if that matters) to place those files elsewhere without an initdb? You can create another table just like the original on a tablespace using the new storage, drop the original, and then rename the new one to the original name. This is described as another way to cluster data in the Notes section of http://www.postgresql.org/docs/8.4/static/sql-cluster.html , and it has a few warnings related to information that can be lost in this situation. Make sure you've moved all temporary files onto the new filesystem first, observing the warning about that there too. -- Greg Smith2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.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] void stored procedure does return something?
I have the following stored procedure return void. CREATE OR REPLACE FUNCTION sandbox() RETURNS void AS $BODY$DECLARE DECLARE me text; DECLARE he int; BEGIN he = 100; RAISE NOTICE 'he is %', he; -- me = Hello PostgreSQL; END;$BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100; ALTER FUNCTION sandbox() OWNER TO postgres; When I perform query : SELECT * FROM sandbox(); Everything is fine. he is 100 is being printed in message area. However, when I remove -- from me = Hello PostgreSQL; I get the following error : ERROR: column Hello PostgreSQL does not exist LINE 1: SELECT Hello PostgreSQL ^ QUERY: SELECT Hello PostgreSQL CONTEXT: PL/pgSQL function sandbox line 7 at assignment But isn't my stored procedure is void? Isn't it shouldn't return anything? Thanks and Regards Yan Cheng CHEOK -- 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] void stored procedure does return something?
On Thursday 21 January 2010 5:57:14 pm Yan Cheng Cheok wrote: I have the following stored procedure return void. CREATE OR REPLACE FUNCTION sandbox() RETURNS void AS $BODY$DECLARE DECLARE me text; DECLARE he int; BEGIN he = 100; RAISE NOTICE 'he is %', he; -- me = Hello PostgreSQL; END;$BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100; ALTER FUNCTION sandbox() OWNER TO postgres; When I perform query : SELECT * FROM sandbox(); Everything is fine. he is 100 is being printed in message area. However, when I remove -- from me = Hello PostgreSQL; I get the following error : ERROR: column Hello PostgreSQL does not exist LINE 1: SELECT Hello PostgreSQL ^ QUERY: SELECT Hello PostgreSQL CONTEXT: PL/pgSQL function sandbox line 7 at assignment But isn't my stored procedure is void? Isn't it shouldn't return anything? Thanks and Regards Yan Cheng CHEOK You need to single quote the string like this; 'Hello PostgreSQL' Double quotes are for identifiers. See here for full explanation. http://www.postgresql.org/docs/8.4/interactive/sql-syntax-lexical.html -- Adrian Klaver adrian.kla...@gmail.com -- 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] Extremely Slow Cascade Delete Operation
I try to create a following simple scenario, to demonstrate cascade delete is
rather slow in PostgreSQL.
Can anyone help me to confirm? Is this my only machine problem, or every
PostgreSQL users problem?
I create 1 lot.
every lot is having 1 unit
every unit is having 100 measurement.
hence :
lot - 1 row entry
unit - 1 row entries
measurement - 100 row entries
run command :
delete from lot where lot_id = 1;
Opps. Never ending
To reproduce :
(1) Download SQL script from
http://sites.google.com/site/yanchengcheok/Home/test.sql
(2) Create a empty database named Sandbox. Follow all default parameters in
pgAdmin.
(3) Execute SQL statement in test.sql. It will create tables, inserting data.
It may take up to few minutes.
(4) run
delete from lot where lot_id = 1;
dang! dang! dang! a never ending story.
Do you guys get an extremely slow experience as me? Does other database (like
MySQL) experience same issues too?
Thanks and Regards
Yan Cheng CHEOK
--- On Wed, 1/13/10, Grzegorz Jaśkiewicz gryz...@gmail.com wrote:
From: Grzegorz Jaśkiewicz gryz...@gmail.com
Subject: Re: [GENERAL] Extremely Slow Cascade Delete Operation
To: Yan Cheng Cheok ycch...@yahoo.com
Cc: pgsql-general@postgresql.org
Date: Wednesday, January 13, 2010, 4:35 PM
It doesn't look like it is locked, so
it is carrying the delete out.
However that doesn't mean, that there isn't any other
locking
occurring, or simply your disks are rather busy.
Also, maybe the DB is rather big, what are the table sizes
?
If you are using 8.4+, than do \dt+ to get an idea,
otherwise SELECT
pg_size_pretty(pg_total_relation_size('table_name')); for
each table.
--
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] Slow Query / Check Point Segments
Hii am experience slow queries when i run some functions. I noticed the following entries in my server log. From this, can anyone tell me if I need to change some config parmeters? System has 18GB Memoryshared_buffers = 4GB# min 128kBtemp_buffers = 32MB # min 800kBmax_prepared_transactions = 100 # zero disables the featurework_mem = 256MB# min 64kBmaintenance_work_mem = 1GB # min 1MBwal_buffers = 1024kB# min 32kBcheckpoint_segments = 32# in logfile segments, min 1, 16MB eachcheckpoint_timeout = 30min # range 30s-1h 2010-01-22 12:18:11 JSTLOG: checkpoint complete: wrote 52037 buffers (9.9%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=67.441 s, sync=0.000 s, total=67.453 s2010-01-22 12:21:48 JSTLOG: checkpoint complete: wrote 83874 buffers (16.0%); 0 transaction log file(s) added, 0 removed, 32 recycled; write=138.040 s, sync=0.000 s, total=138.063 s2010-01-22 12:23:32 JSTLOG: checkpoint complete: wrote 82856 buffers (15.8%); 0 transaction log file(s) added, 0 removed, 32 recycled; write=18.740 s, sync=0.000 s, total=18.783 s2010-01-22 12:24:26 JSTLOG: checkpoint complete: wrote 75145 buffers (14.3%); 0 transaction log file(s) added, 0 removed, 32 recycled; write=12.129 s, sync=0.000 s, total=12.132 s2010-01-22 12:25:30 JSTLOG: checkpoint complete: wrote 82108 buffers (15.7%); 0 transaction log file(s) added, 0 removed, 32 recycled; write=10.619 s, sync=0.000 s, total=10.621 s2010-01-22 12:28:03 JSTLOG: checkpoint complete: wrote 87349 buffers (16.7%); 0 transaction log file(s) added, 0 removed, 32 recycled; write=82.190 s, sync=0.000 s, total=82.192 s2010-01-22 12:30:02 JSTLOG: checkpoint complete: wrote 80797 buffers (15.4%); 0 transaction log file(s) added, 0 removed, 32 recycled; write=78.198 s, sync=0.000 s, total=78.201 s2010-01-22 12:32:03 JSTLOG: checkpoint complete: wrote 81365 buffers (15.5%); 0 transaction log file(s) added, 0 removed, 32 recycled; write=75.968 s, sync=0.000 s, total=75.971 s2010-01-22 12:33:32 JSTLOG: checkpoint complete: wrote 98258 buffers (18.7%); 0 transaction log file(s) added, 0 removed, 32 recycled; write=52.156 s, sync=0.000 s, total=52.159 s2010-01-22 12:34:51 JSTLOG: checkpoint complete: wrote 80089 buffers (15.3%); 0 transaction log file(s) added, 0 removed, 32 recycled; write=53.385 s, sync=0.000 s, total=53.387 s2010-01-22 12:36:08 JSTLOG: checkpoint complete: wrote 80819 buffers (15.4%); 0 transaction log file(s) added, 0 removed, 32 recycled; write=51.476 s, sync=0.000 s, total=51.478 s2010-01-22 13:01:54 JSTLOG: checkpoint complete: wrote 4892 buffers (0.9%); 0 transaction log file(s) added, 0 removed, 32 recycled; write=494.868 s, sync=0.000 s, total=494.982 s Thanks for any adviceAlex _ Shopping Trolley Mechanic If It Exists, You'll Find it on SEEK http://clk.atdmt.com/NMN/go/157639755/direct/01/
Re: [GENERAL] Extremely Slow Cascade Delete Operation
Yan Cheng Cheok wrote: I try to create a following simple scenario, to demonstrate cascade delete is rather slow in PostgreSQL. Can anyone help me to confirm? Is this my only machine problem, or every PostgreSQL users problem? I create 1 lot. every lot is having 1 unit every unit is having 100 measurement. 101 measurements per unit by the looks. But it doesn't much matter. test= CREATE INDEX fk_unit_id_idx ON measurement (fk_unit_id); CREATE INDEX Time: 3072.635 ms Now suddenly everything is much faster: test= delete from lot; DELETE 1 Time: 8066.140 ms Before that index creation, every deletion of a unit required a seqscan of `measurement' to find referenced measurements. At 200ms apiece, it would've taken about half an hour to `delete from lot' on my machine, and smaller deletes took a proportional amount of time (ie 20s for 100 units). Now it takes 8 seconds to delete the lot. You just forgot to create an index on one of the foreign key relationships that you do a cascade delete on. BTW, Pg doesn't force you to do this because sometimes you'd prefer to wait. For example, you might do the deletes very rarely, and not way to pay the cost of maintaining the index the rest of the time. (What I was personally surprised by is that it's no faster to DELETE FROM measurement; directly than to delete via LOT. I would've expected a seqscan delete of the table to be MUCH faster than all the index-hopping required to delete via lot. I guess the reason there's no real difference is because the whole dataset fits in cache, so there's no seek penalty. ) AFAIK, Pg isn't clever enough to batch foreign key deletes together and then plan them as a single operation. That means it can't use something other than a bunch of little index lookups where doing a sequential scan or a hash join might be faster. Adding this facility would certainly be an interesting project. Most of the time, though, you get on fine using index-based delete cascading, and you can generally pre-delete rows using a join on those rare occasions it is a problem. -- Craig Ringer -- 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 Query / Check Point Segments
On Thu, Jan 21, 2010 at 9:13 PM, Alex - ainto...@hotmail.com wrote: Hi i am experience slow queries when i run some functions. I noticed the following entries in my server log. From this, can anyone tell me if I need to change some config parmeters? System has 18GB Memory shared_buffers = 4GB # min 128kB temp_buffers = 32MB # min 800kB max_prepared_transactions = 100 # zero disables the feature work_mem = 256MB # min 64kB That's really big work_mem. What's max_connections? maintenance_work_mem = 1GB # min 1MB That's pretty big too. wal_buffers = 1024kB # min 32kB checkpoint_segments = 32 # in logfile segments, min 1, 16MB each checkpoint_timeout = 30min # range 30s-1h 2010-01-22 12:18:11 JSTLOG: checkpoint complete: wrote 52037 buffers (9.9%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=67.441 s, sync=0.000 s, total=67.453 s 2010-01-22 12:21:48 JSTLOG: checkpoint complete: wrote 83874 buffers (16.0%); 0 transaction log file(s) added, 0 removed, 32 recycled; write=138.040 s, sync=0.000 s, total=138.063 s 2010-01-22 12:23:32 JSTLOG: checkpoint complete: wrote 82856 buffers (15.8%); 0 transaction log file(s) added, 0 removed, 32 recycled; write=18.740 s, sync=0.000 s, total=18.783 s 2010-01-22 12:24:26 JSTLOG: checkpoint complete: wrote 75145 buffers (14.3%); 0 transaction log file(s) added, 0 removed, 32 recycled; write=12.129 s, sync=0.000 s, total=12.132 s 2010-01-22 12:25:30 JSTLOG: checkpoint complete: wrote 82108 buffers (15.7%); 0 transaction log file(s) added, 0 removed, 32 recycled; write=10.619 s, sync=0.000 s, total=10.621 s 2010-01-22 12:28:03 JSTLOG: checkpoint complete: wrote 87349 buffers (16.7%); 0 transaction log file(s) added, 0 removed, 32 recycled; write=82.190 s, sync=0.000 s, total=82.192 s 2010-01-22 12:30:02 JSTLOG: checkpoint complete: wrote 80797 buffers (15.4%); 0 transaction log file(s) added, 0 removed, 32 recycled; write=78.198 s, sync=0.000 s, total=78.201 s 2010-01-22 12:32:03 JSTLOG: checkpoint complete: wrote 81365 buffers (15.5%); 0 transaction log file(s) added, 0 removed, 32 recycled; write=75.968 s, sync=0.000 s, total=75.971 s 2010-01-22 12:33:32 JSTLOG: checkpoint complete: wrote 98258 buffers (18.7%); 0 transaction log file(s) added, 0 removed, 32 recycled; write=52.156 s, sync=0.000 s, total=52.159 s 2010-01-22 12:34:51 JSTLOG: checkpoint complete: wrote 80089 buffers (15.3%); 0 transaction log file(s) added, 0 removed, 32 recycled; write=53.385 s, sync=0.000 s, total=53.387 s 2010-01-22 12:36:08 JSTLOG: checkpoint complete: wrote 80819 buffers (15.4%); 0 transaction log file(s) added, 0 removed, 32 recycled; write=51.476 s, sync=0.000 s, total=51.478 s Up to now it looks pretty normal. Then: 2010-01-22 13:01:54 JSTLOG: checkpoint complete: wrote 4892 buffers (0.9%); 0 transaction log file(s) added, 0 removed, 32 recycled; write=494.868 s, sync=0.000 s, total=494.982 s Wow that is a really long time for a checkpoint of only 0.9% to take. I'm gonna guess you went from a lot of inserts up above, then started running lots of queries that used lots of memory and / or vacuum chewed up a lot, and the OS swapped out your shared_buffers and they were getting swapped back in during the checkpoint. But that's just a guess. Could be other stuff. When this stuff happens, what does the output of vmstat 60 look like (not just the first line, run it a few minutes)? -- 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 Query / Check Point Segments
Oh yeah, what's your swappiness setting (assuming you're running some flavor of linux: sysctl -a|grep swapp should tell you. I set it to something small like 5 or so on db servers. Default of 60 is fine for an interactive desktop but usually too high for a server. -- 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] Extremely Slow Cascade Delete Operation
Hi Craig Ringer, Really appreciate a lot for your advice! This at least has cleared my doubt, which had been confused me for quite some time. Thanks and Regards Yan Cheng CHEOK --- On Fri, 1/22/10, Craig Ringer cr...@postnewspapers.com.au wrote: From: Craig Ringer cr...@postnewspapers.com.au Subject: Re: [GENERAL] Extremely Slow Cascade Delete Operation To: Yan Cheng Cheok ycch...@yahoo.com Cc: Grzegorz Jaśkiewicz gryz...@gmail.com, pgsql-general@postgresql.org Date: Friday, January 22, 2010, 12:51 PM Yan Cheng Cheok wrote: I try to create a following simple scenario, to demonstrate cascade delete is rather slow in PostgreSQL. Can anyone help me to confirm? Is this my only machine problem, or every PostgreSQL users problem? I create 1 lot. every lot is having 1 unit every unit is having 100 measurement. 101 measurements per unit by the looks. But it doesn't much matter. test= CREATE INDEX fk_unit_id_idx ON measurement (fk_unit_id); CREATE INDEX Time: 3072.635 ms Now suddenly everything is much faster: test= delete from lot; DELETE 1 Time: 8066.140 ms Before that index creation, every deletion of a unit required a seqscan of `measurement' to find referenced measurements. At 200ms apiece, it would've taken about half an hour to `delete from lot' on my machine, and smaller deletes took a proportional amount of time (ie 20s for 100 units). Now it takes 8 seconds to delete the lot. You just forgot to create an index on one of the foreign key relationships that you do a cascade delete on. BTW, Pg doesn't force you to do this because sometimes you'd prefer to wait. For example, you might do the deletes very rarely, and not way to pay the cost of maintaining the index the rest of the time. (What I was personally surprised by is that it's no faster to DELETE FROM measurement; directly than to delete via LOT. I would've expected a seqscan delete of the table to be MUCH faster than all the index-hopping required to delete via lot. I guess the reason there's no real difference is because the whole dataset fits in cache, so there's no seek penalty. ) AFAIK, Pg isn't clever enough to batch foreign key deletes together and then plan them as a single operation. That means it can't use something other than a bunch of little index lookups where doing a sequential scan or a hash join might be faster. Adding this facility would certainly be an interesting project. Most of the time, though, you get on fine using index-based delete cascading, and you can generally pre-delete rows using a join on those rare occasions it is a problem. -- Craig Ringer -- 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 Query / Check Point Segments
Alex - wrote: checkpoint_segments = 32# in logfile segments, min 1, 16MB each checkpoint_timeout = 30min # range 30s-1h These parameters are not so interesting on their own. The important thing to check is how often checkpoints are happening, and how much work each of them do. Here's a useful section from your logs to comment on: 2010-01-22 12:21:48 JSTLOG: checkpoint complete: wrote 83874 buffers (16.0%); 0 transaction log file(s) added, 0 removed, 32 recycled; write=138.040 s, sync=0.000 s, total=138.063 s 2010-01-22 12:23:32 JSTLOG: checkpoint complete: wrote 82856 buffers (15.8%); 0 transaction log file(s) added, 0 removed, 32 recycled; write=18.740 s, sync=0.000 s, total=18.783 s 2010-01-22 12:24:26 JSTLOG: checkpoint complete: wrote 75145 buffers (14.3%); 0 transaction log file(s) added, 0 removed, 32 recycled; write=12.129 s, sync=0.000 s, total=12.132 s 2010-01-22 12:25:30 JSTLOG: checkpoint complete: wrote 82108 buffers (15.7%); 0 transaction log file(s) added, 0 removed, 32 recycled; write=10.619 s, sync=0.000 s, total=10.621 s 2010-01-22 12:28:03 JSTLOG: checkpoint complete: wrote 87349 buffers (16.7%); 0 transaction log file(s) added, 0 removed, 32 recycled; write=82.190 s, sync=0.000 s, total=82.192 s Here you're getting a checkpoint every minute or three, and each of them is writing out ~8 buffers = 625MB. That is crazy, and no wonder your queries are slow--the system is spending all of its time doing constant, extremely expensive checkpoints. You should re-tune this system until the checkpoints show up no more often than every 5 minutes, and preferably closer to 10. In your case, that means greatly increasing checkpoint_segments.. And you might as well decrease checkpoint_timeout, because right now the timeout is rarely ever being reached before the system runs out of working segments and executes a checkpoint for that reason. I would suggest changing the parameters to these: checkpoint_segments = 96# in logfile segments, min 1, 16MB each checkpoint_timeout = 10min # range 30s-1h And seeing how long the interval between checkpoints becomes under load afterwards. That should make it in the just 5 minute range. I'd expect that the number of buffers will only increase a bit, so instead of dumping out 625MB every minute or three you should see maybe 800MB every 5 minutes--big improvement. Also: just after making the change, save a copy of: select now(),* from pg_stat_bgwriter And then run that same query again a few hours after the change (and maybe the next day twoo). Subtract the two values to see how much they changed, and then you'll find some useful info to compute the checkpoint interval without even looking at the logs. There's a lot of theory and comments about this area at http://www.westnet.com/~gsmith/content/postgresql/chkp-bgw-83.htm 2010-01-22 12:36:08 JSTLOG: checkpoint complete: wrote 80819 buffers (15.4%); 0 transaction log file(s) added, 0 removed, 32 recycled; write=51.476 s, sync=0.000 s, total=51.478 s 2010-01-22 13:01:54 JSTLOG: checkpoint complete: wrote 4892 buffers (0.9%); 0 transaction log file(s) added, 0 removed, 32 recycled; write=494.868 s, sync=0.000 s, total=494.982 s See that last one? That's a checkpoint that's being driven by time expiring (30 minutes) rather than running out of segments. That just suggests that the system was mostly idle during that period. Long write times are perfectly fine here, the checkpoints are spread out over a long time if possible in order to reduce average I/O. You're just not seeing that the rest of the time because checkpoints are happening so often. -- Greg Smith2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.com
[GENERAL] Optimization on JOIN
I create 1 lot. every lot is having 1 unit every unit is having 100 measurement. hence : lot - 1 row entry unit - 1 row entries measurement - 100 row entries Currently, I am having JOIN statement as follow (1st case) SELECT measurement_type.value, measurement.value, measurement_unit.value FROM measurement_type INNER JOIN (measurement_unit INNER JOIN (measurement INNER JOIN (lot INNER JOIN unit ON (lot_id = fk_lot_id)) ON (fk_unit_id = unit_id)) ON (fk_measurement_unit_id = measurement_unit_id)) ON (fk_measurement_type_id = measurement_type_id) WHERE lot_id = 7; I thought, I may optimized it using : (2nd case, Take note on the WHERE statement) SELECT measurement_type.value, measurement.value, measurement_unit.value FROM measurement_type INNER JOIN (measurement_unit INNER JOIN (measurement INNER JOIN (lot INNER JOIN unit ON (lot_id = fk_lot_id) WHERE lot_id = 7) ON (fk_unit_id = unit_id)) ON (fk_measurement_unit_id = measurement_unit_id)) ON (fk_measurement_type_id = measurement_type_id); My thought is as follow : For 1st case, my visualization is : (lot join unit) lot_id unit_id - 6 rows === 11 12 13 24 25 26 measurement join (lot join unit) lot_id unit_id measurement_id - 18 rows 11 1 11 2 11 3 12 4 12 5 12 6 13 7 13 8 13 9 24 10 24 11 24 12 25 13 25 14 25 15 26 16 26 17 26 18 measurement join (lot join unit) where lot_id = 1 lot_id unit_id measurement_id - 9 rows 11 1 11 2 11 3 12 4 12 5 12 6 13 7 13 8 13 9 For 2nd case, my visualization is : (lot join unit where lot_id = 1) lot_id unit_id - 3 rows === 11 12 13 measurement join (lot join unit where lot_id = 1) lot_id unit_id measurement_id - 9 rows 11 1 11 2 11 3 12 4 12 5 12 6 13 7 13 8 13 9 During the process, 2nd case only need maximum 9 rows, compare to 1st case 18 rows. However, the 2nd case syntax is incorrect :( ERROR: syntax error at or near WHERE LINE 6: ... (lot INNER JOIN unit ON (lot_id = fk_lot_id) WHERE lot_... ^ Is there any way I may first perform filter on the small table, then only I use the filtered result for sub-sequence join? Instead of I first join into a very large table, only I perform filtering (which I assume will be slower) Thanks Thanks and Regards Yan Cheng CHEOK -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
