[GENERAL] Another RAID controller recommendation question
We're looking to deploy a bunch of new machines. Our DB is fairly small and write-intensive. Most of the disk traffic is PG WAL. Historically we've avoided RAID controllers for various reasons, but this new deployment will be done with them (also for various reasons ;) We like to use white-boxish machines and we run CentOS. This would be a good example of the kind of machine we'd buy: http://www.newegg.com/Product/Product.aspx?Item=N82E16816101339 manufacturer page : http://www.supermicro.com/products/system/1U/6016/SYS-6016T-URF4_.cfm?UIO=N these boxes have a proprietary controller slot, with these cards: http://www.supermicro.com/products/nfo/UIO.cfm#Adapters specifically this LSI-based one which seems to be the newest/fastest, with BBWBC: http://www.supermicro.com/products/accessories/addon/AOC-USAS2LP-H8iR.cfm I'd be interested to hear any options good or bad on these controllers, or ideas for alternatives. These machines are operated in a lights-out mode, and will handle heavy constant load (hundreds of write txn/s) with 15K SAS drives in a RAID-1 setup (2 drives, or 2 + 2 with data and WAL split between spindle groups). Thanks! -- 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] ncoding Table Name and Filed Name
Hi Mr. David, Thanks for the comment. I understood, its more difficult to mange the schema; when schema itself written in jig jack. Vikram From: David Johnston pol...@yahoo.com To: 'Vikram A' vikkiatb...@yahoo.in Cc: pgsql-general@postgresql.org Sent: Friday, 17 June 2011 7:00 PM Subject: RE: [GENERAL] ncoding Table Name and Filed Name We are expecting following advantages through this, Unauthorized use either by the 1. DB administrator 2. ex-developer Or 3. Any body This why PostgreSQL has the concept of “ROLE”s… The data, not the schema, is what needs to be secured…you are not preventing unauthorized use only making both it and authorized use more difficult. It isn’t that hard to get the database to tell you all the table and field names then it is just a matter of issuing a “SELECT * FROM ….” to see/retrieve the data. A rose is a rose by any other name… For the people you do trust you are making them jump through lots of hoops in order to work with the schema. David J.
Re: [GENERAL] Another RAID controller recommendation question
On 06/18/2011 02:46 AM, David Boreham wrote: We're looking to deploy a bunch of new machines. Our DB is fairly small and write-intensive. Most of the disk traffic is PG WAL. Historically we've avoided RAID controllers for various reasons, but this new deployment will be done with them (also for various reasons ;) If the traffic is heavy on WAL, avoiding RAID controllers isn't a great practice. They're by far the best way possible to speed that up. http://www.newegg.com/Product/Product.aspx?Item=N82E16816101339 manufacturer page : http://www.supermicro.com/products/system/1U/6016/SYS-6016T-URF4_.cfm?UIO=N This a solid basic server model. The Intel 5520 chipset they're built on is nice and fast if you load it up with a bunch of RAM. these boxes have a proprietary controller slot, with these cards: http://www.supermicro.com/products/nfo/UIO.cfm#Adapters specifically this LSI-based one which seems to be the newest/fastest, with BBWBC: http://www.supermicro.com/products/accessories/addon/AOC-USAS2LP-H8iR.cfm I don't really like the whole proprietary controller slot thing if it can be avoided. We seem to have a lot of customers buying from Dell recently, and it's partly because they've made it pretty straightforward to swap out their PERC controller. That makes troubleshooting a broken server easier, spare parts are simple to manage, lots of advantages. You almost need to stock your own spares for things like the RAID cards if they're these propriety slot ones, because you're unlikely to find one in an emergency. That said, the card itself looks like plain old simple LSI MegaRAID. Get the battery backup unit, check the battery and cache policy to make sure they're sane, and learn how to use megaci to monitor it. Fast and generally trouble free after that initial setup time investment. These machines are operated in a lights-out mode, and will handle heavy constant load (hundreds of write txn/s) with 15K SAS drives in a RAID-1 setup (2 drives, or 2 + 2 with data and WAL split between spindle groups). If you can try to measure the exact ratio of database to WAL traffic here, that might help guide which of these configurations makes more sense. Hard to answer in a general way. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us PostgreSQL 9.0 High Performance: http://www.2ndQuadrant.com/books -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Standby server does not start with base backup
Hi, I am running a test setup for verifying the warm standby feature in Postgres 9.0 release. Both my Master (Win 7 Professional, 64 bit) Standby (Win 7 Professional, 64 bit) machines are having Postgres 9.0 (postgresql-9.0.4-1-windows) installed both are in our LAN environment. After taking a base backup of the master database using pg_start_backup() copy of the data folder to Standby machine followed by pg_stop_backup() in Master, the Standby server is started. The Standby server logs show the following error and the standby Postgres server shuts down. After this it never starts 2011-06-18 13:56:32 IST LOG: entering standby mode 2011-06-18 13:56:32 IST LOG: streaming replication successfully connected to primary 2011-06-18 13:56:32 IST FATAL: the database system is starting up 2011-06-18 13:56:33 IST LOG: redo starts at 0/320 2011-06-18 13:56:33 IST FATAL: the database system is starting up 2011-06-18 13:56:34 IST LOG: consistent recovery state reached at 0/500 2011-06-18 13:56:34 IST FATAL: the database system is starting up 2011-06-18 13:56:35 IST FATAL: the database system is starting up 2011-06-18 13:56:36 IST FATAL: the database system is starting up 2011-06-18 13:56:37 IST FATAL: the database system is starting up 2011-06-18 13:56:38 IST FATAL: the database system is starting up 2011-06-18 13:56:39 IST FATAL: the database system is starting up . . . .and same message continues.. I have attached the configuration files of standby server. Please help me if anybody have any such experience or know the exact cause of the issue. Regards, Sanjay # PostgreSQL Client Authentication Configuration File # === # # Refer to the Client Authentication section in the PostgreSQL # documentation for a complete description of this file. A short # synopsis follows. # # This file controls: which hosts are allowed to connect, how clients # are authenticated, which PostgreSQL user names they can use, which # databases they can access. Records take one of these forms: # # local DATABASE USER METHOD [OPTIONS] # host DATABASE USER CIDR-ADDRESS METHOD [OPTIONS] # hostsslDATABASE USER CIDR-ADDRESS METHOD [OPTIONS] # hostnossl DATABASE USER CIDR-ADDRESS METHOD [OPTIONS] # # (The uppercase items must be replaced by actual values.) # # The first field is the connection type: local is a Unix-domain # socket, host is either a plain or SSL-encrypted TCP/IP socket, # hostssl is an SSL-encrypted TCP/IP socket, and hostnossl is a # plain TCP/IP socket. # # DATABASE can be all, sameuser, samerole, replication, a # database name, or a comma-separated list thereof. # # USER can be all, a user name, a group name prefixed with +, or a # comma-separated list thereof. In both the DATABASE and USER fields # you can also write a file name prefixed with @ to include names # from a separate file. # # CIDR-ADDRESS specifies the set of hosts the record matches. It is # made up of an IP address and a CIDR mask that is an integer (between # 0 and 32 (IPv4) or 128 (IPv6) inclusive) that specifies the number # of significant bits in the mask. Alternatively, you can write an IP # address and netmask in separate columns to specify the set of hosts. # Instead of a CIDR-address, you can write samehost to match any of # the server's own IP addresses, or samenet to match any address in # any subnet that the server is directly connected to. # # METHOD can be trust, reject, md5, password, gss, sspi, # krb5, ident, pam, ldap, radius or cert. Note that # password sends passwords in clear text; md5 is preferred since # it sends encrypted passwords. # # OPTIONS are a set of options for the authentication in the format # NAME=VALUE. The available options depend on the different # authentication methods -- refer to the Client Authentication # section in the documentation for a list of which options are # available for which authentication methods. # # Database and user names containing spaces, commas, quotes and other # special characters must be quoted. Quoting one of the keywords # all, sameuser, samerole or replication makes the name lose # its special character, and just match a database or username with # that name. # # This file is read on server startup and when the postmaster receives # a SIGHUP signal. If you edit the file on a running system, you have # to SIGHUP the postmaster for the changes to take effect. You can # use pg_ctl reload to do that. # Put your actual configuration here # -- # # If you want to allow non-local connections, you need to add more # host records. In that case you will also need to make PostgreSQL # listen on a non-local interface via the listen_addresses # configuration parameter, or via the -i or -h command line switches. # TYPE DATABASEUSERCIDR-ADDRESSMETHOD # IPv4 local connections: hostall all
[GENERAL] While converting the Master to Standby , FATAL: timeline 2 of the primary does not match recovery target timeline 1
Hi, We are doing Master/Standby setup, We unplugged the master server, then the standby server became the master (by creating trigger file). While converting the old master to new slave server, We are getting the following error in old master, FATAL: the database system is starting up FATAL: the database system is starting up FATAL: timeline 2 of the primary does not match recovery target timeline 1 FATAL: the database system is starting up FATAL: the database system is starting up In new Master Machine the log is as follows, LOG: selected new timeline ID: 2 LOG: archive recovery complete LOG: database system is ready to accept connections LOG: autovacuum launcher started LOG: unexpected EOF on client connection Could anyone please help on this issue. Thanks and Regards Nithya R ::DISCLAIMER:: --- The contents of this e-mail and any attachment(s) are confidential and intended for the named recipient(s) only. It shall not attach any liability on the originator or HCL or its affiliates. Any views or opinions presented in this email are solely those of the author and may not necessarily reflect the opinions of HCL or its affiliates. Any form of reproduction, dissemination, copying, disclosure, modification, distribution and / or publication of this message without the prior written consent of the author of this e-mail is strictly prohibited. If you have received this email in error please delete it and notify the sender immediately. Before opening any mail and attachments please check them for viruses and defect. ---
Re: [GENERAL] interval hour to minute or interval day to minute
On Thu, Jun 16, 2011 at 06:07:50PM -0400, Bruce Momjian wrote: Noah Misch wrote: On Sun, Apr 17, 2011 at 04:55:51PM +0100, Jack Douglas wrote: I discovered the 'fields' option of 'interval', but i can't figure out from the docs how it is supposed to work. Are hour to minute and day to minute really the same thing? And if not, in what circumstances are they treated differently? As of version 8.4, they behave identically. The code has this comment, some form of which probably belongs in the documentation: /* * Our interpretation of intervals with a limited set of fields is * that fields to the right of the last one specified are zeroed out, * but those to the left of it remain valid. Thus for example there * is no operational difference between INTERVAL YEAR TO MONTH and * INTERVAL MONTH. In some cases we could meaningfully enforce that * higher-order fields are zero; for example INTERVAL DAY could reject * nonzero month field. However that seems a bit pointless when we * can't do it consistently. (We cannot enforce a range limit on the * highest expected field, since we do not have any equivalent of * SQL's interval leading field precision.) * * Note: before PG 8.4 we interpreted a limited set of fields as * actually causing a modulo operation on a given value, potentially * losing high-order as well as low-order information. But there is * no support for such behavior in the standard, and it seems fairly * undesirable on data consistency grounds anyway. Now we only * perform truncation or rounding of low-order fields. */ I am lost on how we could mention that in the docs. Perhaps something like this? *** a/doc/src/sgml/datatype.sgml --- b/doc/src/sgml/datatype.sgml *** *** 1622,1627 HOUR TO MINUTE --- 1622,1630 HOUR TO SECOND MINUTE TO SECOND /literallayout + These only zero less-significant fields, making literalDAY TO + MINUTE/, literalHOUR TO MINUTE/ and literalMINUTE/ equivalent. + The redundant forms exist for compatibility with the SQL standard. Note that if both replaceablefields/replaceable and replaceablep/replaceable are specified, the replaceablefields/replaceable must include literalSECOND/, -- 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] Standby server does not start with base backup
On Sat, Jun 18, 2011 at 10:57, Sanjay Rao s...@noida.interrasystems.com wrote: Hi, I am running a test setup for verifying the warm standby feature in Postgres 9.0 release. Both my Master (Win 7 Professional, 64 bit) Standby (Win 7 Professional, 64 bit) machines are having Postgres 9.0 (postgresql-9.0.4-1-windows) installed both are in our LAN environment. After taking a base backup of the master database using pg_start_backup() copy of the data folder to Standby machine followed by pg_stop_backup() in Master, the Standby server is started. The Standby server logs show the following error and the standby Postgres server shuts down. After this it never starts 2011-06-18 13:56:32 IST LOG: entering standby mode 2011-06-18 13:56:32 IST LOG: streaming replication successfully connected to primary 2011-06-18 13:56:32 IST FATAL: the database system is starting up 2011-06-18 13:56:33 IST LOG: redo starts at 0/320 2011-06-18 13:56:33 IST FATAL: the database system is starting up 2011-06-18 13:56:34 IST LOG: consistent recovery state reached at 0/500 2011-06-18 13:56:34 IST FATAL: the database system is starting up 2011-06-18 13:56:35 IST FATAL: the database system is starting up 2011-06-18 13:56:36 IST FATAL: the database system is starting up 2011-06-18 13:56:37 IST FATAL: the database system is starting up 2011-06-18 13:56:38 IST FATAL: the database system is starting up 2011-06-18 13:56:39 IST FATAL: the database system is starting up . . . .and same message continues.. I have attached the configuration files of standby server. Please help me if anybody have any such experience or know the exact cause of the issue. That seems perfectly normal. There's nothing in those logs indicating that the standby shut down - if it did, there should be some other messages when that happens. If you want to be able to actually connect to the slave, you need to run in hot standby. -- 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] Functional dependencies
Thanks! 2011/6/14 Darren Duncan dar...@darrenduncan.net Alpha Beta wrote: Hi list, (newbie with databases) I was looking out in net about how can we determine or find all functional dependencies in a relational database, but didn't find. So can please anyone here tell me if functional dependecies for each table of a relational database can be found explicitly or we need algorithms for it or any other way? Best regards! Look for pairs of columns/attributes X and Y (or X may be a set of columns/attrs) where, for every row/tuple having the same value for X, the row/tuple always has the same value for Y. In this situation, for the data you have at least, there would seem to be a relationship where X determines Y and Y depends on X. Use an algorithm that does this looking for pairs. -- Darren Duncan
Re: [GENERAL] 2 questions re RAID
On Jun 17, 2011, at 11:23 PM, Greg Smith wrote: I guess you could call Highpoint a RAID manufacturer, but I wouldn't do so. They've released so many terrible problems over the years that it's hard to take the fact that they may have something reasonable you can buy now (the 43XX cards I think?) seriously. Ah, I see. So they're on par with Apple's RAID controller instead of being the first step up. Atto is so Mac focused that you're not going to find much experience here, for the same reason you didn't get any response to your original question. Their cards are using the same Intel IO Processor (IOP) hardware as some known capable cards. For example, the ExpressSAS R348 is named that because it has an Intel 348 IOP. That's the same basic processor as on the medium sized Areca boards: http://www.areca.us/products/pcietosas1680series.htm So speed should be reasonable, presuming they didn't make any major errors in board design or firmware. Good info. Didn't know about their focus, because the last time I dealt with them was so many years ago they still had a significant focus on Windows, or so it seemed to me at the time. Focus on Mac says nothing about the firmware on the card, but it should bode well for the driver. The real thing you need to investigate is whether the write cache setup is done right, and whether monitoring is available in a way you can talk to. What you want is for the card to run in write-back mode normally, degrading to write-through when the battery stops working well. If you don't see that sort of thing clearly documented as available, you really don't want to consider their cards. Well, right up front in their marketing materials they make a major point about cache protection, how important it is, how good it is, using ultracapacitor+flash over batteries (on some of their controllers). So they have awareness intent; competence and follow-through of course are not assured by marketing materials. (Also they talk about background scanning of drives for defects.) And it looks like they offer all of: GUI setup/monitoring that runs on OS X, command-line setup/monitoring that runs on OS X, SNMP... You're basically asking if I don't write to the database, does the fact that write performance on RAID5 is slow matter? When asked that way, sure, it's fine. If after applying the write cache to help, your write throughput requirements don't ever exceed what a single disk can provide, than maybe RAID5 will be fine for you. Make sure you keep shared_buffers low though, because you're not going to be able to absorb a heavy checkpoint sync on RAID5. Yes, basically I wanted to confirm that's what I was actually asking ;-) The only circumstance under which I could see overflowing the card's write cache is during migrations. So my choice then really is better performance during rare migrations vs being able to lose any 2 drives out of 4 (RAID6). Which is OK, since neither choice is really bad--having been burned by bad disk runs before, I'll probably go for safety. (FYI this is not my only margin for failure. Two geographically-distributed WAL-streaming replicas with low-end RAID1 are the next line of defense. Followed by, god forbid I should ever have to use them, daily dumps.) Thanks for all the info. I guess about all I have remaining to do is sanity-check my beliefs about disk I/O. -- 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
Re: [GENERAL] While converting the Master to Standby , FATAL: timeline 2 of the primary does not match recovery target timeline 1
On 06/18/2011 05:22 AM, Nithya Rajendran wrote: We are doing Master/Standby setup, We unplugged the master server, then the standby server became the master (by creating trigger file). While converting the old master to new slave server, We are getting the following error in old master, You can't make a system that used to be in the replication set join it again the way you're trying to do. The timeline feature you're seeing errors from is there to keep you from making mistakes like this. Even though this system used to be the master, it still needs to go through the full procedure for creating a new standby: start a backup, sync all the files from the master, send new archive WAL data over. The fact that this used to be the master doesn't change that. If the database is large, it may be worthwhile to use rsync and features such as its --inplace feature to make the copy from new master - old master faster. Since it has older copies of the files, the copy can go faster than one to an empty system would take. But you can't just convert the old master to be a standby of a new master. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us PostgreSQL 9.0 High Performance: http://www.2ndQuadrant.com/books
Re: [GENERAL] set returning function with variable argument - possible?
On Thu, 16 Jun 2011, David Johnston wrote:
The only way to feed another relation into a function is to place the
function in the SELECT list. If you then want to expand the result of the
function call you alias the function result and then .* against it.
WITH feeder AS (
SELECT f(var) AS result FROM xtable WHERE ...
)
SELECT (feeder.result).*
FROM feeder
;
[snip]
Thanks, David, this worked perfectly! {thanks also to Pavel}
-Frank
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Search for lists
Hello, I need to search a table to find sets of rows that have a column matching itself for the whole set and another column matching row for row with a list I am going to supply. The result I should receive should be value of the column that matches itself. For example given the following data in my table: 3; 1 3; 2 4; 8 4; 9 4; 10 I might need to search for 1,2. This should produce the result 3. Or if I were to search for 8, 9, 10 the result should be 4. Searching for 8, 9 should produce an empty result as should 8, 9, 10, 11. Can anyone recommend a strategy? -- 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] Search for lists
Untested approach Use array_agg on column 2 along with group by on column 1 to build check arrays and then use equals to compare with an array of your desired input values. You should omit duplicates and order ascending both the data and the input to ensure you are matching canonical forms. David J. On Jun 18, 2011, at 17:51, Daron Ryan daron.r...@gmail.com wrote: Hello, I need to search a table to find sets of rows that have a column matching itself for the whole set and another column matching row for row with a list I am going to supply. The result I should receive should be value of the column that matches itself. For example given the following data in my table: 3; 1 3; 2 4; 8 4; 9 4; 10 I might need to search for 1,2. This should produce the result 3. Or if I were to search for 8, 9, 10 the result should be 4. Searching for 8, 9 should produce an empty result as should 8, 9, 10, 11. Can anyone recommend a strategy? -- 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] Search for lists
An alternative approach would be to select using a IN condition on the where clause and group by column 1 and column 2. Then, using this as a sub-select group by the resultant column 1 and a count on column two. The matching identifiers are those with a count equal to the number of entries in the original IN condition. Basically count how many of values each distinct key in column 1 matches and keep those keys where the count and the number of values match. David J. On Jun 18, 2011, at 17:51, Daron Ryan daron.r...@gmail.com wrote: Hello, I need to search a table to find sets of rows that have a column matching itself for the whole set and another column matching row for row with a list I am going to supply. The result I should receive should be value of the column that matches itself. For example given the following data in my table: 3; 1 3; 2 4; 8 4; 9 4; 10 I might need to search for 1,2. This should produce the result 3. Or if I were to search for 8, 9, 10 the result should be 4. Searching for 8, 9 should produce an empty result as should 8, 9, 10, 11. Can anyone recommend a strategy? -- 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] fill factor guestimate
Hi, I have a table that will be updated frequently. Each row will be updated at least once during it's lifetime, and most rows will be updated ~10 times in their first 2 weeks of existence, and then relatively rarely after that. The rows are being updated by a prepared statement that gets executed with ~100 - ~600 different parameter sets, each one updating a single row. What should my initial best-guess fill-factor for the table be? Most of the updates don't touch an indexed column, so I'm hoping to take advantage of the HOT update feature. Will EXPLAIN tell me if an UPDATE uses HOT? thanks -Dave -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Referencing function value inside CASE..WHEN
Hi, In the following statement, does the custom function get executed twice? SELECT MY_FUNCTION(...), CASE WHEN MY_FUNCTION(...) = '...' THEN '...' ELSE '...' END ... ... If so, is there a way I can make it execute once? I tried this but it gave me a Column my_function does not exist error. SELECT MY_FUNCTION(...) AS my_function, CASE WHEN my_function = '...' THEN '...' ELSE '...' END ... ... Thank you in advance.
Re: [GENERAL] fill factor guestimate
On Sat, Jun 18, 2011 at 6:12 PM, Dave Thompson dthomp...@gmail.com wrote: Hi, I have a table that will be updated frequently. Each row will be updated at least once during it's lifetime, and most rows will be updated ~10 times in their first 2 weeks of existence, and then relatively rarely after that. The rows are being updated by a prepared statement that gets executed with ~100 - ~600 different parameter sets, each one updating a single row. What should my initial best-guess fill-factor for the table be? Most of the updates don't touch an indexed column, so I'm hoping to take advantage of the HOT update feature. Will EXPLAIN tell me if an UPDATE uses HOT? No but you can monitor pg_stat_user_tables to see how many updates were HOT updates. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Referencing function value inside CASE..WHEN
Hi, In the following statement, does the custom function get executed twice? SELECT MY_FUNCTION(...), CASE WHEN MY_FUNCTION(...) = '...' THEN '...' ELSE '...' END ... ... If so, is there a way I can make it execute once? I tried this but it gave me a Column my_function does not exist error. SELECT MY_FUNCTION(...) AS my_function, CASE WHEN my_function = '...' THEN '...' ELSE '...' END ... ... Thank you in advance.
[GENERAL] Port forwarding via iptables to postgres listening locally
Hi, I have a posgres instance configured to listen at the localhost default. I'm trying to enable port forwarding via iptables. I set up the following rule: iptables -t nat -I PREROUTING --source 0/0 --destination 0/0 -p tcp --dport 5432 -j REDIRECT and can see that it is redirecting packets received at port 5432. But I am unable to establish a remote connection to the postgres instance. Actually my situation seems to be the same as in this [1] question ( Message-id: AANLkTim7uAXOvwp=pjr6ueucon-8ttmgkw7mu0x4o...@mail.gmail.com ). Thanks in advance, David [1] http://archives.postgresql.org/pgsql-general/2010-11/msg00783.php
Re: [GENERAL] Referencing function value inside CASE..WHEN
You can try using a CTE/With clause. Failing that use a function with SELECT INTO... On Jun 18, 2011, at 22:45, lover boi lover-...@live.co.uk wrote: Hi, In the following statement, does the custom function get executed twice? SELECT MY_FUNCTION(...), CASE WHEN MY_FUNCTION(...) = '...' THEN '...' ELSE '...' END ... ... If so, is there a way I can make it execute once? I tried this but it gave me a Column my_function does not exist error. SELECT MY_FUNCTION(...) AS my_function, CASE WHEN my_function = '...' THEN '...' ELSE '...' END ... ... Thank you in advance. -- 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] Search for lists
Thanks. On 19/06/2011 8:09 AM, David Johnston wrote: An alternative approach would be to select using a IN condition on the where clause and group by column 1 and column 2. Then, using this as a sub-select group by the resultant column 1 and a count on column two. The matching identifiers are those with a count equal to the number of entries in the original IN condition. Basically count how many of values each distinct key in column 1 matches and keep those keys where the count and the number of values match. David J. On Jun 18, 2011, at 17:51, Daron Ryandaron.r...@gmail.com wrote: Hello, I need to search a table to find sets of rows that have a column matching itself for the whole set and another column matching row for row with a list I am going to supply. The result I should receive should be value of the column that matches itself. For example given the following data in my table: 3; 1 3; 2 4; 8 4; 9 4; 10 I might need to search for 1,2. This should produce the result 3. Or if I were to search for 8, 9, 10 the result should be 4. Searching for 8, 9 should produce an empty result as should 8, 9, 10, 11. Can anyone recommend a strategy? -- 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
