Re: [pgadmin-support] [GENERAL] Postgres DB crashing
Thanks for the quick response. These errors are after disabling the autovacuum. auto_vacuum parameter was set to off. Can find the exact reason for this crash. Thanks and Regards Radha Krishna Date: Tue, 18 Jun 2013 13:54:09 -0400 Subject: Re: [pgadmin-support] [GENERAL] Postgres DB crashing From: rumman...@gmail.com To: udayabhanu1...@hotmail.com CC: kgri...@mail.com; adrian.kla...@gmail.com; pgsql-general@postgresql.org; pgadmin-supp...@postgresql.org; laurenz.a...@wien.gv.at; chris.trav...@gmail.com; mag...@hagander.net Stop the autovacuum process and try again. On Tue, Jun 18, 2013 at 1:31 PM, bhanu udaya wrote: Hello, Greetings. My PostgresSQL (9.2) is crashing after certain load tests. Currently, postgressql is crashing when simulatenously 800 to 1000 threads are run on a 10 million records schema. Not sure, if we have to tweak some more parameters of postgres. Currently, the postgressql is configured as below on a 7GB Ram on an Intel Xeon CPU E5507 2.27 GZ. Is this postgres limitation to support only 800 threads or any other configuration required. Please look at the log as below with errors. Please reply max_connections 5000 shared_buffers 2024 MB synchronous_commit off wal_buffers 100 MB wal_writer_delays 1000ms checkpoint_segments 512 checkpoint_timeout 5 min checkpoint_completion_target 0.5 checkpoint_warning 30s work_memory 1G effective_cache_size 5 GB 2013-06-11 15:11:17 GMT [26201]: [1-1]ERROR: canceling autovacuum task 2013-06-11 15:11:17 GMT [26201]: [2-1]CONTEXT: automatic vacuum of table "newrelic.tenant1.customer" 2013-06-11 15:11:17 GMT [25242]: [1-1]LOG: sending cancel to blocking autovacuum PID 26201 2013-06-11 15:11:17 GMT [25242]: [2-1]DETAIL: Process 25242 waits for ExclusiveLock on extension of relation 679054 of database 666546. 2013-06-11 15:11:17 GMT [25242]: [3-1]STATEMENT: UPDATE tenant1.customer SET lastmodifieddate = $1 WHERE id IN ( select random_range((select min(id) from tenant1.customer ), (select max(id) from tenant1.customer )) as id ) AND softdeleteflag IS NOT TRUE 2013-06-11 15:11:17 GMT [25242]: [4-1]WARNING: could not send signal to process 26201: No such process 2013-06-11 15:22:29 GMT [9]: [11-1]WARNING: worker took too long to start; canceled 2013-06-11 15:24:10 GMT [26511]: [1-1]WARNING: autovacuum worker started without a worker entry 2013-06-11 16:03:33 GMT [23092]: [1-1]LOG: could not receive data from client: Connection timed out 2013-06-11 16:06:05 GMT [23222]: [5-1]LOG: could not receive data from client: Connection timed out 2013-06-11 16:07:06 GMT [26869]: [1-1]FATAL: canceling authentication due to timeout 2013-06-11 16:23:16 GMT [25128]: [1-1]LOG: could not receive data from client: Connection timed out 2013-06-11 16:23:20 GMT [25128]: [2-1]LOG: unexpected EOF on client connection with an open transaction 2013-06-11 16:30:56 GMT [23695]: [1-1]LOG: could not receive data from client: Connection timed out 2013-06-11 16:43:55 GMT [24618]: [1-1]LOG: could not receive data from client: Connection timed out 2013-06-11 16:44:29 GMT [25204]: [1-1]LOG: could not receive data from client: Connection timed out 2013-06-11 16:54:14 GMT [6]: [1-1]PANIC: stuck spinlock (0x2aaab54279d4) detected at bufmgr.c:1239 2013-06-11 16:54:14 GMT [32521]: [8-1]LOG: checkpointer process (PID 6) was terminated by signal 6: Aborted 2013-06-11 16:54:14 GMT [32521]: [9-1]LOG: terminating any other active server processes 2013-06-11 16:54:14 GMT [26931]: [1-1]WARNING: terminating connection because of crash of another server process 2013-06-11 16:54:14 GMT [26931]: [2-1]DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2013-06-11 16:54:14 GMT [26931]: [3-1]HINT: In a moment you should be able to reconnect to the database and repeat your command. 2013-06-11 16:54:14 GMT [26401]: [1-1]WARNING: terminating connection because of crash of another server process 2013-06-11 16:54:14 GMT [26401]: [2-1]DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2013-06-11 16:55:08 GMT [27579]: [1-1]FATAL: the database system is in recovery mode 2013-06-11 16:55:08 GMT [24041]: [1-1]WARNING: terminating connection because of crash of another server process 2013-06-11 16:55:08 GMT [24041]: [2-1]DETAIL: The postmaster has commanded this server process to roll back the current
[GENERAL] Migration from DB2 to PostgreSQL
While migrating my application from DB2 to PostgreSQL. I want to migrate following functions in PostgreSQL. TO_CHAR() in DB2 which can take three arguments as follows: SELECT TO_CHAR(CURRENT_DATE,'-MM-DD',112.50) FROM SYSIBM.SYSDUMMY1 I am not sure what is the purpose of third argument in TO_CHAR() function of DB2. I think we can create one user define function with name TO_CHAR which can take three argument like DB2. I want to know that if we want to create user define function TO_CHAR(). What should be the data type of third argument? which can handle maximum data types and more suitable for real time scenarios. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Migration-from-DB2-to-PostgreSQL-tp5759820.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Migration from DB2 to PostgreSQL-ROLLUP()
While migrating my application from DB2 to PostgreSQL. I want to migrate ROLLUP() function in PostgreSQL. Example: SELECT WEEK(SALES_DATE) AS WEEK, DAYOFWEEK(SALES_DATE) AS DAY_WEEK, SUM(SALES) AS UNITS_SOLD FROM SALES GROUP BY ROLLUP ( WEEK(SALES_DATE), DAYOFWEEK(SALES_DATE) ) ORDER BY WEEK, DAY_WEEK This example shows two simple ROLLUP queries followed by a query which treats the two ROLLUPs as grouping sets in a single result set and specifies row ordering for each column involved in the grouping sets. How can I migrate ROLLUP() function in PostgreSQL? or is there any similar function in PostgreSQL which can done this job? -- View this message in context: http://postgresql.1045698.n5.nabble.com/Migration-from-DB2-to-PostgreSQL-ROLLUP-tp5759825.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Migration from DB2 to PostgreSQL
sachin kotwal wrote: > While migrating my application from DB2 to PostgreSQL. > > I want to migrate following functions in PostgreSQL. > > TO_CHAR() in DB2 which can take three arguments as follows: > > SELECT TO_CHAR(CURRENT_DATE,'-MM-DD',112.50) FROM SYSIBM.SYSDUMMY1 > > I am not sure what is the purpose of third argument in TO_CHAR() function of > DB2. > > I think we can create one user define function with name TO_CHAR which can > take three argument like DB2. > > I want to know that if we want to create user define function TO_CHAR(). > What should be the data type of third argument? > > which can handle maximum data types and more suitable for real time > scenarios. Look at IBM's documentation at http://pic.dhe.ibm.com/infocenter/db2luw/v10r1/index.jsp?topic=%2Fcom.ibm.db2.luw.sql.ref.doc%2Fdoc%2Fr0007110.html TO_CHAR is a synonym for VARCHAR_FORMAT. The third argument is an optional locale name, like 'en_US', which will be used to generate the text parts of the format you chose. For a format like '-MM-DD' this has no effect at all and can be omitted. PostgreSQL has no such capability. Unless you need that and want to code it yourself, the best solution would be to write a function that just ignores the third argument. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Migration from DB2 to PostgreSQL-ROLLUP()
Hello 2013/6/19 sachin kotwal : > While migrating my application from DB2 to PostgreSQL. > > I want to migrate ROLLUP() function in PostgreSQL. > > Example: > SELECT WEEK(SALES_DATE) AS WEEK, > DAYOFWEEK(SALES_DATE) AS DAY_WEEK, > SUM(SALES) AS UNITS_SOLD > FROM SALES > GROUP BY ROLLUP ( WEEK(SALES_DATE), DAYOFWEEK(SALES_DATE) ) > ORDER BY WEEK, DAY_WEEK > > This example shows two simple ROLLUP queries followed by a query which > treats the two ROLLUPs as grouping sets in a single result set and specifies > row ordering for each column involved in the grouping sets. > > How can I migrate ROLLUP() function in PostgreSQL? or is there any similar > function in PostgreSQL which can done this job? this functionality is not supported you should to rewrite query SELECT .. FROM GROUP BY weeks(sales_date), dayofweek(salesdate) UNION ALL SELECT ... FROM .. GROUP BY dayofweek(salesdate) ORDER BY .. Regards Pavel Stehule > > > > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/Migration-from-DB2-to-PostgreSQL-ROLLUP-tp5759825.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- 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] json functions
On Wed, Jun 19, 2013 at 12:00 AM, Jayadevan M wrote: > Hi, > > > > I have PostgreSQL 9.2.1. I can see a few json functions under pg_catalog, > json_send, for example. But I can’t find any documentation. Am I missing > something? json_send like all send functions is internal. Not all functions available in the catalog are exposed through SQL -- for example they may be used to serialize data for transmission over the wire. If you can handle C you can hunt down the location of the function to see what it does (which isn't much since json is an already serialized format). merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pgxs question - linking c-functions to external libraries
On 6/6/13 11:49 PM, Rad Cirskis wrote: > Hi John, > have you managed to get it to link with external shared libs? Sure, many extensions to that. Do something like SHLIB_LINK += -lfoo in your Makefile. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] postgresql query
I have numeric values in a numeric column.the column has two parts.i want to split in 2 differnet column . The column value looks like this: Quantity 2000 -1000 both the quantity values are of a same product.but i want these in a single line. so what i want is a result set that looks like: In quantity Out quantity --- 2000 -1000 how can i get this in a select statement ? -- View this message in context: http://postgresql.1045698.n5.nabble.com/postgresql-query-tp5759846.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] postgresql query
Which version of Postgresql are you using? However, you may use string_agg like below if its available in your version: \d t1 Table "public.t1" Column | Type | Modifiers +-+--- i | integer | amt| integer | select * from t1; i | amt ---+- 1 | 20 1 | 30 2 | 30 (3 rows) select i, string_agg(amt::text,',') as c from t1 group by i;; i | c ---+--- 1 | 20,30 2 | 30 (2 rows) Have a good day. On Wed, Jun 19, 2013 at 6:51 AM, Jashaswee wrote: > I have numeric values in a numeric column.the column has two parts.i want > to > split in 2 differnet column . > The column value looks like this: > > Quantity > 2000 > -1000 > > both the quantity values are of a same product.but i want these in a > single > line. > so what i want is a result set that looks like: > In quantity Out quantity > --- > 2000 -1000 > > how can i get this in a select statement ? > > > > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/postgresql-query-tp5759846.html > Sent from the PostgreSQL - general mailing list archive at Nabble.com. > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Re: [GENERAL] postgresql query
2013/6/19 Jashaswee > The column value looks like this: > > Quantity > 2000 > -1000 > > both the quantity values are of a same product.but i want these in a > single > line. > so what i want is a result set that looks like: > In quantity Out quantity > --- > 2000 -1000 > How do we decide wether 2000 or -1000 refers to the “In quantity”? Can you show a complete table's structure, please? -- Victor Y. Yegorov
Re: [GENERAL] Migration from DB2 to PostgreSQL-ROLLUP()
On Wed, Jun 19, 2013 at 3:56 AM, sachin kotwal wrote: > While migrating my application from DB2 to PostgreSQL. > > I want to migrate ROLLUP() function in PostgreSQL. > > Example: > SELECT WEEK(SALES_DATE) AS WEEK, > DAYOFWEEK(SALES_DATE) AS DAY_WEEK, > SUM(SALES) AS UNITS_SOLD > FROM SALES > GROUP BY ROLLUP ( WEEK(SALES_DATE), DAYOFWEEK(SALES_DATE) ) > ORDER BY WEEK, DAY_WEEK > > This example shows two simple ROLLUP queries followed by a query which > treats the two ROLLUPs as grouping sets in a single result set and specifies > row ordering for each column involved in the grouping sets. > > How can I migrate ROLLUP() function in PostgreSQL? or is there any similar > function in PostgreSQL which can done this job? Yeah, we don't have it. To work around the problem I typically create a function or a view dynamically, that takes a template and forces the rollup with a UNION ALL. This technique is not generic, so each case has to be handled specially. Lack of OLAP style sql features is #2 missing feature for me in postgres (after stored procedures). merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Problem with connection
Hi Folks, I'm having trouble with connection / remote connection. I'm running Postgres on Solaris 10. $ netstat -a |grep 5432 localhost.5432 *.*0 0 49152 0 LISTEN 6002ae2ee98 stream-ord 6002af1d080 /tmp/.s.PGSQL.5432 _Works fine_ $ psql -d director Welcome to psql 8.1.19 (server 8.2.17), the PostgreSQL interactive terminal. _*Does not work*_ $ psql -h phys-brmtso-2 -d director psql: could not connect to server: Connection refused Is the server running on host "phys-brmtso-2" and accepting TCP/IP connections on port 5432? I tried both md5 & trust. phys-brmtso-2# tail -15 pg_hba.conf # superuser. If you do not trust all your local users, use another # authentication method. # TYPE DATABASEUSERCIDR-ADDRESS METHOD # "local" is for Unix domain socket connections only local all all trust # IPv4 local connections: #hostall all 127.0.0.1/32 trust hostall all 127.0.0.1/32 md5 # IPv6 local connections: hostall all ::1/128 trust # remote connetions: hostall all 10.195.1.0/24 md5
Re: [GENERAL] Problem with connection
On 13-06-19 04:13 PM, karen chau wrote: Hi Folks, I'm having trouble with connection / remote connection. I'm running Postgres on Solaris 10. $ netstat -a |grep 5432 localhost.5432 *.*0 0 49152 0 LISTEN 6002ae2ee98 stream-ord 6002af1d080 /tmp/.s.PGSQL.5432 _Works fine_ $ psql -d director Welcome to psql 8.1.19 (server 8.2.17), the PostgreSQL interactive terminal. _*Does not work*_ $ psql -h phys-brmtso-2 -d director psql: could not connect to server: Connection refused Is the server running on host "phys-brmtso-2" and accepting TCP/IP connections on port 5432? I tried both md5 & trust. phys-brmtso-2# tail -15 pg_hba.conf # superuser. If you do not trust all your local users, use another # authentication method. # TYPE DATABASEUSERCIDR-ADDRESS METHOD # "local" is for Unix domain socket connections only local all all trust # IPv4 local connections: #hostall all 127.0.0.1/32 trust hostall all 127.0.0.1/32 md5 # IPv6 local connections: hostall all ::1/128 trust # remote connetions: hostall all 10.195.1.0/24 md5 Hi, Check this line in your postgresql.conf maybe? #listen_addresses = 'localhost'# what IP address(es) to listen on; You should have it set to localhost *and* the hostname you want to use (i.e. the phys-brmtso-2), that might be the problem I think. Ziggy
Re: [GENERAL] Problem with connection
On 06/19/2013 01:13 PM, karen chau wrote: Hi Folks, I'm having trouble with connection / remote connection. I'm running Postgres on Solaris 10. $ netstat -a |grep 5432 localhost.5432 *.*0 0 49152 0 LISTEN 6002ae2ee98 stream-ord 6002af1d080 /tmp/.s.PGSQL.5432 _Works fine_ $ psql -d director Welcome to psql 8.1.19 (server 8.2.17), the PostgreSQL interactive terminal. _*Does not work*_ $ psql -h phys-brmtso-2 -d director psql: could not connect to server: Connection refused Is the server running on host "phys-brmtso-2" and accepting TCP/IP connections on port 5432? Are you connecting locally in both cases or across a network in the second case? If across a network, might there be a firewall in between blocking connections? -- 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] Problem with connection
On 6/19/2013 1:53 PM, Ziggy Skalski wrote: #listen_addresses = 'localhost'# what IP address(es) to listen on; You should have it set to localhost *and* the hostname you want to use (i.e. the phys-brmtso-2), that might be the problem I think. or better, listen_addresses = '*' # listen to all network interfaces. (note you have to remove the leading # as thats a comment). -- john r pierce 37N 122W somewhere on the middle of the left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] intagg
Hi All, I am trying to use the intagg extension. in 9.1.9 I have created the extension as such "CREATE EXTENSION intagg" Then tried to use the function int_array_aggregate. Returns this message function int_array_aggregate(integer[]) does not exist select int_array_aggregate(transactions) from x x being create table x (transactions int4[]); Can anyone please advise.. Thanks Andrew Bartley
Re: [GENERAL] intagg
Sorry that should be aggregate int_array_aggregate not function On 20 June 2013 08:16, Andrew Bartley wrote: > Hi All, > > I am trying to use the intagg extension. in 9.1.9 > > I have created the extension as such "CREATE EXTENSION intagg" > > Then tried to use the function int_array_aggregate. > > Returns this message > > function int_array_aggregate(integer[]) does not exist > > select int_array_aggregate(transactions) from x > > x being > > create table x (transactions int4[]); > > Can anyone please advise.. > > Thanks > > Andrew Bartley >
[GENERAL] Carry forward last observation
Hello, I have a table with dates and stock prices. Some of the prices are NULL because the stock did not trade on that day. In such instances, I'd like to replace the NULL value with the most recent non-NULL value, but I can't find an efficient way to do this. I had thought a clever WINDOW function could be used, but I think I may be in need of the unimplemented "IGNORE NULL" parameter in the last_value() window function to do this. Any help or SQL trick would be greatly appreciated. Here's an example: CREATE TABLE stk_prc (dtidx INTEGER PRIMARY KEY, price REAL); INSERT INTO stk_prc SELECT x-100 as dtidx, CASE WHEN x IN (102,103,105) THEN NULL ELSE x END as price FROM (SELECT generate_series(101,105) as x) z; SELECT * FROM stk_prc; dtidx | price ---+--- 1 | 101 2 | 3 | 4 | 104 5 | And here is what I would like to see: dtidx | price ---+--- 1 | 101 2 | 101 3 | 101 4 | 104 5 | 104 I was able to get the solution with the below query using a self join, but I'm hoping that this isn't the best answer as the query is slow and expensive for large tables. SELECT x.dtidx, p.price FROM stk_prc p, (SELECT a.dtidx, max(b.dtidx) as lastidx FROM stk_prc a, stk_prc b WHERE a.dtidx>=b.dtidx AND b.price IS NOT NULL GROUP BY a.dtidx) x WHERE p.dtidx=x.lastidx; Thanks, Robert McGehee, CFA Geode Capital Management, LLC One Post Office Square, 20th Floor | Boston, MA | 02109 Direct: (617)392-8396 This e-mail, and any attachments hereto, are intended for use by the addressee(s) only and may contain information that is (i) confidential information of Geode Capital Management, LLC and/or its affiliates, and/or (ii) proprietary information of Geode Capital Management, LLC and/or its affiliates. If you are not the intended recipient of this e-mail, or if you have otherwise received this e-mail in error, please immediately notify me by telephone (you may call collect), or by e-mail, and please permanently delete the original, any print outs and any copies of the foregoing. Any dissemination, distribution or copying of this e-mail is strictly prohibited. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] postgresql query
Jashaswee wrote > I have numeric values in a numeric column.the column has two parts.i want > to split in 2 differnet column . > The column value looks like this: > > Quantity > 2000 > -1000 > > both the quantity values are of a same product.but i want these in a > single line. > so what i want is a result set that looks like: > In quantity Out quantity > --- > 2000 -1000 > > how can i get this in a select statement ? I presume this is a debit/credit situation. Basically you use a CASE expression to put the amount into the correct column depending on whether it is greater or less than zero. The rest of the query is simply a matter of what kind of detail you want. WITH make_debit_credit_columns_for_each_record AS ( SELECT ... , CASE WHEN amt >= 0 THEN amt ELSE 0.00 END AS debit , CASE WHEN amt < 0 THEN amt ELSE 0.00 END AS credit FROM source_table ) SELECT ..., SUM(debit) AS total_debit, SUM(credit) AS total_credit FROM make_debit_credit_columns_for_each_record GROUP BY ... David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/postgresql-query-tp5759846p5760003.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Carry forward last observation
McGehee, Robert wrote > Hello, > I have a table with dates and stock prices. Some of the prices are NULL > because the stock did not trade on that day. In such instances, I'd like > to replace the NULL value with the most recent non-NULL value, but I can't > find an efficient way to do this. Cannot speak to efficiency but something like this may work for you: self-contained SQL: WITH input_src (id, idx, price) AS ( VALUES (1,1,'101'),(1,2,NULL),(1,3,NULL),(1,4,'104'),(1,5,NULL) ) , construct_possibles AS ( SELECT *, array_agg(price) OVER ( PARTITION BY id ORDER BY idx ROWS 3 PRECEDING --# attempts to limit size of array by only going back a limited number of days ) AS possibles FROM input_src ORDER BY idx ASC ) SELECT * --, some_function_to_get_most_recent_nonnull_value(possibles) FROM construct_possibles ; HTH David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Carry-forward-last-observation-tp5759988p5760005.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Migration from DB2 to PostgreSQL
On Wed, Jun 19, 2013 at 6:00 PM, Albe Laurenz wrote: > sachin kotwal wrote: >> While migrating my application from DB2 to PostgreSQL. >> >> I want to migrate following functions in PostgreSQL. >> >> TO_CHAR() in DB2 which can take three arguments as follows: >> >> SELECT TO_CHAR(CURRENT_DATE,'-MM-DD',112.50) FROM SYSIBM.SYSDUMMY1 >> >> I am not sure what is the purpose of third argument in TO_CHAR() function of >> DB2. >> >> I think we can create one user define function with name TO_CHAR which can >> take three argument like DB2. >> >> I want to know that if we want to create user define function TO_CHAR(). >> What should be the data type of third argument? >> >> which can handle maximum data types and more suitable for real time >> scenarios. > > Look at IBM's documentation at > http://pic.dhe.ibm.com/infocenter/db2luw/v10r1/index.jsp?topic=%2Fcom.ibm.db2.luw.sql.ref.doc%2Fdoc%2Fr0007110.html > > TO_CHAR is a synonym for VARCHAR_FORMAT. > The third argument is an optional locale name, like 'en_US', > which will be used to generate the text parts of the > format you chose. > > For a format like '-MM-DD' this has no effect at all > and can be omitted. > > PostgreSQL has no such capability. Unless you need that and > want to code it yourself, the best solution would be to > write a function that just ignores the third argument. > Just wondering: If this particular function is to be used repeatedly in a single query, would the cost of having a wrapper function around the original function be too large? For example, if this function appears in a WHERE clause against a table containing millions of rows. -- Amit Langote -- 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] Migration from DB2 to PostgreSQL
On Thu, Jun 20, 2013 at 11:10 AM, Amit Langote wrote: > If this particular function is to be used repeatedly in a single > query, would the cost of having a wrapper function around the original > function be too large? For example, if this function appears in a > WHERE clause against a table containing millions of rows. If your wrapper function is written in SQL and is trivial (eg ignore the third parameter and pass the other two on), the planner should be able to optimize right through it. Best way to find out is with EXPLAIN, which I've been using a good bit lately. The optimizer's pretty smart. ChrisA -- 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] Migration from DB2 to PostgreSQL
Hi, On Thu, Jun 20, 2013 at 10:27 AM, Chris Angelico wrote: > On Thu, Jun 20, 2013 at 11:10 AM, Amit Langote > wrote: >> If this particular function is to be used repeatedly in a single >> query, would the cost of having a wrapper function around the original >> function be too large? For example, if this function appears in a >> WHERE clause against a table containing millions of rows. > > If your wrapper function is written in SQL and is trivial (eg ignore > the third parameter and pass the other two on), the planner should be > able to optimize right through it. Best way to find out is with > EXPLAIN, which I've been using a good bit lately. The optimizer's > pretty smart. For example consider following rough example: postgres=# create table nums as select * from generate_series(1,100) as num; SELECT Time: 1185.589 ms postgres=# select count(*) from nums where num > 3450; count 996550 (1 row) Time: 183.987 ms postgres=# create or replace function gt(n int, m int) returns boolean as $$ begin return n > m; end; $$ language plpgsql; CREATE FUNCTION Time: 1.080 ms postgres=# select count(*) from nums where gt(num, 3450); count 996550 (1 row) Time: 1327.800 ms postgres=# create or replace function gt3(n int, m int, o int) returns boolean as $$ begin return gt(n, m); end; $$ language plpgsql; CREATE FUNCTION Time: 1.073 ms postgres=# select count(*) from nums where gt3(num, 3450, 0); count 996550 (1 row) Time: 2356.576 ms postgres=# explain select count(*) from nums where gt3(num, 3450, 0); QUERY PLAN Aggregate (cost=265258.34..265258.35 rows=1 width=0) -> Seq Scan on nums (cost=0.00..264425.00 rows=33 width=0) Filter: gt3(num, 3450, 0) (3 rows) -- Amit Langote -- 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] Migration from DB2 to PostgreSQL
On Thu, Jun 20, 2013 at 11:35 AM, Amit Langote wrote: > On Thu, Jun 20, 2013 at 10:27 AM, Chris Angelico wrote: >> If your wrapper function is written in SQL and is trivial (eg ignore >> the third parameter and pass the other two on), the planner should be >> able to optimize right through it. Best way to find out is with >> EXPLAIN, which I've been using a good bit lately. The optimizer's >> pretty smart. > > For example consider following rough example: > > postgres=# create table nums as select * from generate_series(1,100) as > num; > SELECT > Time: 1185.589 ms > postgres=# select count(*) from nums where num > 3450; > count > > 996550 > (1 row) > > Time: 183.987 ms > > postgres=# create or replace function gt(n int, m int) returns boolean as $$ > begin > return n > m; > end; > $$ > language plpgsql; > CREATE FUNCTION > Time: 1.080 ms > > postgres=# select count(*) from nums where gt(num, 3450); > count > > 996550 > (1 row) > > Time: 1327.800 ms > Huge difference between 'language plpgsql' and 'language sql'. Here's my timings using your code - similar to your timings: rosuav=> select count(*) from nums where num > 3450; count 996550 (1 row) Time: 293.836 ms rosuav=> select count(*) from nums where gt(num, 3450); count 996550 (1 row) Time: 2412.186 ms rosuav=> select count(*) from nums where gt3(num, 3450, 0); count 996550 (1 row) Time: 4332.554 ms Now here's the SQL version of the code: rosuav=> create or replace function gt(n int, m int) returns boolean as 'select n>m' language sql; CREATE FUNCTION Time: 39.196 ms rosuav=> select count(*) from nums where gt(num, 3450); count 996550 (1 row) Time: 258.153 ms rosuav=> create or replace function gt3(n int, m int,o int) returns boolean as 'select gt(n,m)' language sql; CREATE FUNCTION Time: 21.891 ms rosuav=> select count(*) from nums where gt3(num, 3450, 0); count 996550 (1 row) Time: 258.998 ms The original, the one with the SQL function, and the one with two SQL functions, all are within margin of error. (Repeated execution shows times varying down as far as 237ms for the last one.) ChrisA -- 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] Migration from DB2 to PostgreSQL
On Thu, Jun 20, 2013 at 10:54 AM, Chris Angelico wrote: > On Thu, Jun 20, 2013 at 11:35 AM, Amit Langote > wrote: >> On Thu, Jun 20, 2013 at 10:27 AM, Chris Angelico wrote: >>> If your wrapper function is written in SQL and is trivial (eg ignore >>> the third parameter and pass the other two on), the planner should be >>> able to optimize right through it. Best way to find out is with >>> EXPLAIN, which I've been using a good bit lately. The optimizer's >>> pretty smart. >> >> For example consider following rough example: >> >> postgres=# create table nums as select * from generate_series(1,100) as >> num; >> SELECT >> Time: 1185.589 ms >> postgres=# select count(*) from nums where num > 3450; >> count >> >> 996550 >> (1 row) >> >> Time: 183.987 ms >> >> postgres=# create or replace function gt(n int, m int) returns boolean as $$ >> begin >> return n > m; >> end; >> $$ >> language plpgsql; >> CREATE FUNCTION >> Time: 1.080 ms >> >> postgres=# select count(*) from nums where gt(num, 3450); >> count >> >> 996550 >> (1 row) >> >> Time: 1327.800 ms >> > > Huge difference between 'language plpgsql' and 'language sql'. Here's > my timings using your code - similar to your timings: > Umm, my bad! I almost forgot I could write pure SQL function bodies. Although, why does following happen? (sorry, a 8.4.2 installation) : postgres=# create or replace function gt(n int, m int) returns boolean as 'select n>m' language sql; ERROR: column "n" does not exist LINE 2: as 'select n>m' language sql; -- Amit Langote -- 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] Migration from DB2 to PostgreSQL
On Thu, Jun 20, 2013 at 12:09 PM, Amit Langote wrote: > Umm, my bad! I almost forgot I could write pure SQL function bodies. > Although, why does following happen? (sorry, a 8.4.2 installation) : > > postgres=# create or replace function gt(n int, m int) returns boolean > as 'select n>m' language sql; > ERROR: column "n" does not exist > LINE 2: as 'select n>m' language sql; Hmm, no idea. I'm using 9.2.4, could well have been changes. ChrisA -- 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] Migration from DB2 to PostgreSQL
On Thu, Jun 20, 2013 at 11:10 AM, Chris Angelico wrote: > On Thu, Jun 20, 2013 at 12:09 PM, Amit Langote > wrote: >> Umm, my bad! I almost forgot I could write pure SQL function bodies. >> Although, why does following happen? (sorry, a 8.4.2 installation) : >> >> postgres=# create or replace function gt(n int, m int) returns boolean >> as 'select n>m' language sql; >> ERROR: column "n" does not exist >> LINE 2: as 'select n>m' language sql; > > Hmm, no idea. I'm using 9.2.4, could well have been changes. > Hmm, I guess in 8.4.2, one needs to refer to function arguments as $1, $2 ... postgres=# create or replace function gt(n int, m int) returns boolean as 'select $1>$2' language sql; CREATE FUNCTION postgres=# create or replace function gt3(n int, m int, o int) returns boolean as 'select gt($1,$2)' language sql; CREATE FUNCTION postgres=# select count(*) from nums where num > 3450; count 996550 (1 row) Time: 126.184 ms postgres=# select count(*) from nums where gt(num, 3450); count 996550 (1 row) Time: 130.754 ms postgres=# select count(*) from nums where gt3(num, 3450, 0); count 996550 (1 row) Time: 140.031 ms And yes. OP can go ahead with his migration using this suggested wrapping function idea. -- Amit Langote -- 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] Migration from DB2 to PostgreSQL
On Thu, Jun 20, 2013 at 12:34 PM, Amit Langote wrote: > On Thu, Jun 20, 2013 at 11:10 AM, Chris Angelico wrote: >> On Thu, Jun 20, 2013 at 12:09 PM, Amit Langote >> wrote: >>> Umm, my bad! I almost forgot I could write pure SQL function bodies. >>> Although, why does following happen? (sorry, a 8.4.2 installation) : >>> >>> postgres=# create or replace function gt(n int, m int) returns boolean >>> as 'select n>m' language sql; >>> ERROR: column "n" does not exist >>> LINE 2: as 'select n>m' language sql; >> >> Hmm, no idea. I'm using 9.2.4, could well have been changes. >> > > Hmm, I guess in 8.4.2, one needs to refer to function arguments as $1, $2 ... Ah, okay. I'm not all that familiar with different versions of PostgreSQL; I used it first back in the 1990s, then didn't use it for years (was all DB2), and now picked it up again at version 9.0/9.1, moving to 9.2 when it came out. > And yes. OP can go ahead with his migration using this suggested > wrapping function idea. Absolutely! ChrisA -- 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] json functions
Hi, >json_send like all send functions is internal. Not all functions available in >the >catalog are exposed through SQL -- for example they may be used to serialize >data for transmission over the wire. If you can handle C you can hunt down >the location of the function to see what it does (which isn't much since json >is >an already serialized format). OK. That explains it. I was wondering why we have quite a few functions under pg_catalog and just a few in the documentation. Thank you. Regards, Jayadevan DISCLAIMER: "The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect." -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] variadic args to C functions
Has anyone got any pointers on implementing a C function in an extension that takes variadic args? I would like to do something like: select my_function(XXX,...); where XXX will be between 1 and many integers. Possible? I didn't see any examples in the contrib directory. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] LDAP authentication timing out
Hello All, I have the following config: hostsamerole+myrole samenetldap ldapserver="ldap1,ldap2,ldap3" ldapbinddn="mybinddn" ldapbindpasswd="mypass" ldapbasedn="mybase" ldapsearchattribute="myatt" Usually auth works perfectly with LDAP (starting a session from psql using an LDAP connection, authenticating with the LDAP password then exiting straight away) I see this: 2013-06-20 15:19:53 EST DEBUG: edb-postgres child[15901]: starting with ( 2013-06-20 15:19:53 EST DEBUG: forked new backend, pid=15901 socket=10 2013-06-20 15:19:53 EST DEBUG: edb-postgres 2013-06-20 15:19:53 EST DEBUG: dccn 2013-06-20 15:19:53 EST DEBUG: ) 2013-06-20 15:19:53 EST DEBUG: InitPostgres 2013-06-20 15:19:53 EST DEBUG: my backend ID is 1 2013-06-20 15:19:53 EST DEBUG: StartTransaction 2013-06-20 15:19:53 EST DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children: 2013-06-20 15:19:53 EST DEBUG: received password packet 2013-06-20 15:19:53 EST DEBUG: CommitTransaction 2013-06-20 15:19:53 EST DEBUG: name: unnamed; blockState: STARTED; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children: 2013-06-20 15:19:56 EST DEBUG: shmem_exit(0): 7 callbacks to make 2013-06-20 15:19:56 EST DEBUG: proc_exit(0): 3 callbacks to make 2013-06-20 15:19:56 EST DEBUG: exit(0) 2013-06-20 15:19:56 EST DEBUG: shmem_exit(-1): 0 callbacks to make 2013-06-20 15:19:56 EST DEBUG: proc_exit(-1): 0 callbacks to make 2013-06-20 15:19:56 EST DEBUG: reaping dead processes 2013-06-20 15:19:56 EST DEBUG: server process (PID 15901) exited with exit code 0 However around 10% of the time (although this varies) the session hangs after I type in my password till the auth timeout and I see this: 2013-06-20 15:07:46 EST DEBUG: forked new backend, pid=15587 socket=10 2013-06-20 15:07:46 EST DEBUG: edb-postgres child[15587]: starting with ( 2013-06-20 15:07:46 EST DEBUG: edb-postgres 2013-06-20 15:07:46 EST DEBUG: dccn 2013-06-20 15:07:46 EST DEBUG: ) 2013-06-20 15:07:46 EST DEBUG: InitPostgres 2013-06-20 15:07:46 EST DEBUG: my backend ID is 1 2013-06-20 15:07:46 EST DEBUG: StartTransaction 2013-06-20 15:07:46 EST DEBUG: name: unnamed; blockState: DEFAULT; state: INPROGR, xid/subid/cid: 0/1/0, nestlvl: 1, children: 2013-06-20 15:07:46 EST DEBUG: received password packet 2013-06-20 15:08:46 EST DEBUG: shmem_exit(1): 7 callbacks to make 2013-06-20 15:08:46 EST DEBUG: proc_exit(1): 3 callbacks to make 2013-06-20 15:08:46 EST DEBUG: exit(1) 2013-06-20 15:08:46 EST DEBUG: shmem_exit(-1): 0 callbacks to make 2013-06-20 15:08:46 EST DEBUG: proc_exit(-1): 0 callbacks to make 2013-06-20 15:08:46 EST DEBUG: reaping dead processes 2013-06-20 15:08:46 EST DEBUG: server process (PID 15587) exited with exit code 1 Anyone have any ideas? I never see this with MD5. I can multiple quickfire binds from an LDAP application and the same bind DN with no problems. Cheers, James Sewell PostgreSQL Team Lead / Solutions Architect _ [image: http://www.lisasoft.com/sites/lisasoft/files/u1/2013hieghtslogan_0.png] Level 2, 50 Queen St, Melbourne, VIC, 3000 P: 03 8370 8000 F: 03 8370 8099 W: www.lisasoft.com -- -- The contents of this email are confidential and may be subject to legal or professional privilege and copyright. No representation is made that this email is free of viruses or other defects. If you have received this communication in error, you may not copy or distribute any part of it or otherwise disclose its contents to anyone. Please advise the sender of your incorrect receipt of this correspondence. <>
[GENERAL] Archiving and recovering pg_stat_tmp
Hello, I was trying to figure out how does one recover server statistics to the same snapshot to which a database is restored after PITR. The steps i had in mind were 1.Set up WAL archiving 2.On server shutdown one would need to backup pg_stat_tmp along with file system level back of database 3. On server crash setup configuration for recovery mode 4. Restart server, which replays WAL files and hen moves from recovery to normal mode What will be behavior be regarding pg_stat_tmp? Will it be deleted on startup? Is it possible to recover the same statistics as on last server shutdown? ICan the statistics recovered to the same PITR? Thank you Sameer
[GENERAL] Snapshot backups
Hey All,
This is a message to confirm my thoughts / validate a possible approach.
In a situation where PGDATA and {XLOG, ARCHIVELOG} are on different SAN/NAS
volumes and a backup is to be initiated do pg_start_backup and
pg_stop_backup need to be used?
I am using snapshots of each volume for backup.
My thinking is that they are not needed (although I realise it is good
practice).
As far as I can tell all they are doing is something like:
pg_start_backup:
- create backup label
- trigger checkpoint
pg_stop_backup
- remove backup label file
- creates backup history file
- trigger log switch
There is nothing in here that is *required* from a backup point of view. Am
I missing anything?
James Sewell
Solutions Architect
_
[image:
http://www.lisasoft.com/sites/lisasoft/files/u1/2013hieghtslogan_0.png]
Level 2, 50 Queen St,
Melbourne, VIC, 3000
P: 03 8370 8000 F: 03 8370 8099 W: www.lisasoft.com
--
--
The contents of this email are confidential and may be subject to legal or
professional privilege and copyright. No representation is made that this
email is free of viruses or other defects. If you have received this
communication in error, you may not copy or distribute any part of it or
otherwise disclose its contents to anyone. Please advise the sender of your
incorrect receipt of this correspondence.
<>
