Re: [GENERAL] Should casting to integer produce same result as trunc()
On 11 Oct 2011, at 2:55, Harvey, Allan AC wrote: Hi all, Had to squash timestamps to the nearest 5 minutes and things went wrong. My simple understanding of trunc() and casting to an integer says that there is a bug here. I think you may be right there, something about the rounding in the cast seems wrong. -- should be different but are not. select (((extract( epoch from '2011-08-22 08:37:30'::timestamp ) + 10 * 3600) / 300 )::integer), (((extract( epoch from '2011-08-22 08:42:30'::timestamp ) + 10 * 3600) / 300 )::integer); int4 | int4 -+- 4380008 | 4380008 (1 row) Without the cast, that gives (I'm in a different TZ apparently): select (extract( epoch from '2011-08-22 08:37:30'::timestamp ) + 10 * 3600) / 300, (extract( epoch from '2011-08-22 08:42:30'::timestamp ) + 10 * 3600) / 300; ?column? | ?column? ---+--- 4380103.5 | 4380104.5 (1 row) Which the type-cast should round to 4380103 and 4380104 respectively. It doesn't: select ((extract( epoch from '2011-08-22 08:37:30'::timestamp ) + 10 * 3600) / 300)::integer, floor((extract( epoch from '2011-08-22 08:42:30'::timestamp ) + 10 * 3600) / 300); int4 | floor -+- 4380104 | 4380104 (1 row) Floor() works fine though: select floor((extract( epoch from '2011-08-22 08:37:30'::timestamp ) + 10 * 3600) / 300), floor((extract( epoch from '2011-08-22 08:42:30'::timestamp ) + 10 * 3600) / 300); floor | floor -+- 4380103 | 4380104 (1 row) Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. -- 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] SQL Help - Finding Next Lowest Value of Current Row Value
Thanks for the suggestions Chris (and Chris). After a bit more investigation I stumbled upon the Window functions. The approach below turned out to be much more efficient that a function or self join approach. The SQL that I used is provided below (event_id and mmsi uniquely identify a vessel transit for which I wished to compute how much time had elapsed between successive records): SELECT a.event_id, a.mmsi, (a.epoch - lag(epoch) OVER (PARTITION BY event_id, mmsi ORDER BY epoch ASC))/60 AS elapsed FROM dmas_ais a Jeff From: ccur...@gmail.com [mailto:ccur...@gmail.com] On Behalf Of Chris Curvey Sent: Saturday, October 01, 2011 10:55 PM To: Jeff Adams Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] SQL Help - Finding Next Lowest Value of Current Row Value On Sat, Oct 1, 2011 at 5:30 PM, Jeff Adams jeff.ad...@noaa.gov wrote: Greetings, I have a large table (~19 million records). Records contains a field identifying a vessel and a field containing an time (epoch). Using the current rows vessel and time values, I need to be able to find the next lowest time value for the vessel and use it to compute how much time has elapsed between the records. I have tried a scalar subquery in the SELECT, which works, but it runs quite slowly. Is there an alternative approach that might perform better for this type of query. Any information would be greatly appreciated. Thanks... Jeff Would a self-join with a MAX() help, like this? (Where v is your vessel_id and e is your time value?) create table stuff ( v int , e timestamp ); insert into stuff (v, e) values (1, '1/1/2011'); insert into stuff (v, e) values (1, '1/2/2011'); insert into stuff (v, e) values (1, '1/3/2011'); insert into stuff (v, e) values (2, '2/1/2011'); insert into stuff (v, e) values (2, '2/2/2011'); select a.v, a.e, max(b.e), a.e - max(b.e) from stuff a join stuff b on a.v = b.v where a.e b.e group by a.v, a.e I don't have a multi-million row table handy, but I'd be interested to hear your results. -- e-Mail is the equivalent of a postcard written in pencil. This message may not have been sent by me, or intended for you. It may have been read or even modified while in transit. e-Mail disclaimers have the same force in law as a note passed in study hall. If your corporate attorney says that you need an disclaimer in your signature, you need a new corporate attorney.
[GENERAL] Question on GiST re-index
Hi All, I am trying to upgrade my postgres server from 8.3.3 to 8.3.15. Postgres 8.3.15 has a dependency on 8.3.8 and this has a dependency on 8.3.5. *.3.5 states to reindex all GiST indexes after the upgrade. Also 8.3.8 states 'fix hash calculation for data type 'interval'. Will these (both reindexing) be covered by a full re-index of the DB using the reindexdb.exe command? Thanks, Krishnanand Visit our Website at http://www.rmesi.co.in This message is confidential. You should not copy it or disclose its contents to anyone. You may use and apply the information for the intended purpose only. Internet communications are not secure; therefore, RMESI does not accept legal responsibility for the contents of this message. Any views or opinions presented are those of the author only and not of RMESI. If this email has come to you in error, please delete it, along with any attachments. Please note that RMESI may intercept incoming and outgoing email communications. Freedom of Information Act 2000 This email and any attachments may contain confidential information belonging to RMESI. Where the email and any attachments do contain information of a confidential nature, including without limitation information relating to trade secrets, special terms or prices these shall be deemed for the purpose of the Freedom of Information Act 2000 as information provided in confidence by RMESI and the disclosure of which would be prejudicial to RMESI's commercial interests. This email has been scanned for viruses by Trend ScanMail.
[GENERAL] Global Variables?
When writing unit tests it's sometimes useful to stub functions such as the current date and time -- define mock functions CREATE OR REPLACE FUNCTION _now() RETURNS timestamp with time zone AS $$ BEGIN RETURN '2011-10-10 10:00'; END; $$ LANGUAGE plpgsql; -- define tables accounts CREATE TABLE accounts (username varchar, expiration timestamp); -- populate with sample data COPY accounts FROM '/home/eradman/sample_accounts.txt'; -- define view expired_accounts CREATE OR REPLACE VIEW expired_accounts AS SELECT * FROM accounts WHERE expiration _now(); -- test views SELECT assert(0, (SELECT count(*) FROM expired_accounts)::integer); Is it possible to declare a global variable that can be referenced from the user-defined function _now()? I'm looking for a means of abstraction that allows me to avoid issuing CREATE OR REPLACE FUNCTION ... before each assert() current_time := '2012-01-01'::timestamp SELECT assert(5, (SELECT count(*) FROM expired_accounts)::integer); -- Eric Radman | http://eradman.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] Should casting to integer produce same result as trunc()
Alban Hertroys haram...@gmail.com writes: On 11 Oct 2011, at 2:55, Harvey, Allan AC wrote: My simple understanding of trunc() and casting to an integer says that there is a bug here. Which the type-cast should round to 4380103 and 4380104 respectively. It doesn't: That's because a cast from float to int rounds, it doesn't truncate. regression=# select (4.7::float8)::int; int4 -- 5 (1 row) regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Global Variables?
On 11 October 2011 16:06, Eric Radman ericsh...@eradman.com wrote: When writing unit tests it's sometimes useful to stub functions such as the current date and time Is it possible to declare a global variable that can be referenced from the user-defined function _now()? I'm looking for a means of abstraction that allows me to avoid issuing CREATE OR REPLACE FUNCTION ... before each assert() current_time := '2012-01-01'::timestamp SELECT assert(5, (SELECT count(*) FROM expired_accounts)::integer); You could crate a table for such constants and read your current-time from that table. Additionally, I would put such stub functions in a separate schema and create a test role with that schema as the top of their search_path. That way, you could even override system function implementations (and other definitions) and only have them apply to the role you're using for unit testing. CREATE ROLE unit_tester; CREATE SCHEMA unit_tests AUTHORIZATION unit_tester; SET search_path TO unit_tests, my_schema, public; CREATE TABLE unit_test_parameters ( current_time timestamp without time zone NOT NULL DEFAULT now() ); CREATE OR REPLACE FUNCTION now() RETURNS timestamp without time zone AS $$ SELECT current_time FROM unit_test_parameters LIMIT 1; $$ LANGUAGE SQL ...etc... UPDATE unit_test_parameters SET current_time = '2012-01-01'::timestamp; SELECT assert(5, (SELECT count(*) FROM expired_accounts)::integer); I'm not sure how you planned to use that _now() function with the assert; I expected a WHERE clause in that query, but it isn't there. -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest. -- 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] Should casting to integer produce same result as trunc()
On 11 October 2011 15:41, Tom Lane t...@sss.pgh.pa.us wrote: Alban Hertroys haram...@gmail.com writes: On 11 Oct 2011, at 2:55, Harvey, Allan AC wrote: My simple understanding of trunc() and casting to an integer says that there is a bug here. Which the type-cast should round to 4380103 and 4380104 respectively. It doesn't: That's because a cast from float to int rounds, it doesn't truncate. regression=# select (4.7::float8)::int; int4 -- 5 (1 row) I figured it would be something like that. Is that how it's defined in the SQL standard? All other programming languages I've come to know truncate floats on such casts (C, php, python, java, to name a few). This is probably quite surprising to people used to these languages. I agree that rounding is the right thing to do. We are probably just too used to programming languages that truncate instead of round because it was more convenient to implement it that way at the time. -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest. -- 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] [postgis-users] Query slow down, never completes
On Tue, Oct 11, 2011 at 02:25:20PM +0200, Andreas Forø Tollefsen wrote: I also tried to close the db1 connection for each year in the loop, and reopen the connection for the next year in the loop. Same problem. I have tried both with insert into ... select .. and select into annual tables and the put them together. Same problem. Then I guess the problem is with the specific data you encounter at the given year. Can you reproduce the hangup in the query for a single year ? Keep an eye on memory use and CPU utilization. --strk; () Free GIS Flash consultant/developer /\ http://strk.keybit.net/services.html -- 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] Global Variables?
It would be interesting if the parameters/settings framework could be extended to provide session/table/user/database level custom settings, accessible via the SET/SHOW/RESET commands. Is there anything like this ever been considered/discussed ? Στις Tuesday 11 October 2011 17:06:50 ο/η Eric Radman έγραψε: When writing unit tests it's sometimes useful to stub functions such as the current date and time -- define mock functions CREATE OR REPLACE FUNCTION _now() RETURNS timestamp with time zone AS $$ BEGIN RETURN '2011-10-10 10:00'; END; $$ LANGUAGE plpgsql; -- define tables accounts CREATE TABLE accounts (username varchar, expiration timestamp); -- populate with sample data COPY accounts FROM '/home/eradman/sample_accounts.txt'; -- define view expired_accounts CREATE OR REPLACE VIEW expired_accounts AS SELECT * FROM accounts WHERE expiration _now(); -- test views SELECT assert(0, (SELECT count(*) FROM expired_accounts)::integer); Is it possible to declare a global variable that can be referenced from the user-defined function _now()? I'm looking for a means of abstraction that allows me to avoid issuing CREATE OR REPLACE FUNCTION ... before each assert() current_time := '2012-01-01'::timestamp SELECT assert(5, (SELECT count(*) FROM expired_accounts)::integer); -- Eric Radman | http://eradman.com -- Achilleas Mantzios -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [postgis-users] Query slow down, never completes
Hi Sandro, What i find strange is that it stops processing at different years on my desktop and my laptop. While my desktop stops processing at 1980, my slower laptop goes on to 1991 before halting. I also tried with different postgresql.conf shared_buffers settings without making any difference. Therefore it is hard to reproduce this for a single year. I can easily process 1980 or 1991 if just running the script for that year. 2011/10/11 Sandro Santilli s...@keybit.net On Tue, Oct 11, 2011 at 02:25:20PM +0200, Andreas Forø Tollefsen wrote: I also tried to close the db1 connection for each year in the loop, and reopen the connection for the next year in the loop. Same problem. I have tried both with insert into ... select .. and select into annual tables and the put them together. Same problem. Then I guess the problem is with the specific data you encounter at the given year. Can you reproduce the hangup in the query for a single year ? Keep an eye on memory use and CPU utilization. --strk; () Free GIS Flash consultant/developer /\ http://strk.keybit.net/services.html ___ postgis-users mailing list postgis-us...@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [GENERAL] [postgis-users] Query slow down, never completes
On Tue, Oct 11, 2011 at 05:12:24PM +0200, Andreas Forø Tollefsen wrote: Hi Sandro, What i find strange is that it stops processing at different years on my desktop and my laptop. While my desktop stops processing at 1980, my slower laptop goes on to 1991 before halting. I also tried with different postgresql.conf shared_buffers settings without making any difference. Therefore it is hard to reproduce this for a single year. I can easily process 1980 or 1991 if just running the script for that year. But you mentioned you had stopped the backend and restarted for each year ? Does the problem still occur if you avoid writing any table (could be an I/O issue) ? --strk; () Free GIS Flash consultant/developer /\ http://strk.keybit.net/services.html -- 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] Should casting to integer produce same result as trunc()
Alban Hertroys haram...@gmail.com writes: On 11 October 2011 15:41, Tom Lane t...@sss.pgh.pa.us wrote: That's because a cast from float to int rounds, it doesn't truncate. I figured it would be something like that. Is that how it's defined in the SQL standard? SQL99 says Whenever an exact or approximate numeric value is assigned to an exact numeric value site, an approximation of its value that preserves leading significant digits after rounding or truncating is represented in the declared type of the target. The value is converted to have the precision and scale of the target. The choice of whether to truncate or round is implementation-defined. An approximation obtained by truncation of a numeric value N for an exact numeric type T is a value V in T such that N is not closer to zero than is V and there is no value in T between V and N. An approximation obtained by rounding of a numeric value N for an exact numeric type T is a value V in T such that the absolute value of the difference between N and the numeric value of V is not greater than half the absolute value of the difference between two successive numeric values in T. If there is more than one such value V, then it is implementation-defined which one is taken. or in short, you can do it in any reasonable fashion. It looks like our code for this has used rint() since the day it was put in, http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=64d9b508939fb15d72fdfa825ee8938506764d66 regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Logging queries cancelled due to replication timeouts
Christophe Pettus wrote: Greetings, Is there a combination of options that will cause a hot standby replica to log queries that are cancelled due to a replication timeout (max_standby_streaming_delay)? Sure, how about the system view pg_stat_database_conflicts in PG 9.1? Our docs say: Add the link linkend=monitoring-stats-views-tablestructnamepg_stat_database_conflicts//link system view to show queries that have been canceled and the reason (Magnus Hagander) /para para Cancellations can occur because of dropped tablespaces, lock timeouts, old snapshots, pinned buffers, and deadlocks. I assume replication cancellations are also in there, no? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] libpq 8.3 and 8.4 interfaces
Hello, Could someone point me, where I can find the difference between libpq 8.3 and 8.4, I have seen the new features of the 8.4, but I want to know about the API interface changes Thanks in advance
Re: [GENERAL] libpq 8.3 and 8.4 interfaces
On 10/11/11 12:42 PM, salah jubeh wrote: Could someone point me, where I can find the difference between libpq 8.3 and 8.4, I have seen the new features of the 8.4, but I want to know about the API interface changes open http://www.postgresql.org/docs/8.4/static/libpq.html and http://www.postgresql.org/docs/8.3/static/libpq.html side by side and compare each subchapter ? I don't believe there are any API 'changes' that break backwards compatability, but new versions might add APIs (I can't recall any such between 8.3 and 8.4, however). -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Global Variables?
On Tue, Oct 11, 2011 at 04:26:47PM +0200, Alban Hertroys wrote: On 11 October 2011 16:06, Eric Radman ericsh...@eradman.com wrote: When writing unit tests it's sometimes useful to stub functions such as the current date and time You could create a table for such constants and read your current-time from that table. Additionally, I would put such stub functions in a separate schema and create a test role with that schema as the top of their search_path. That way, you could even override system function implementations (and other definitions) and only have them apply to the role you're using for unit testing. CREATE ROLE unit_tester; CREATE SCHEMA unit_tests AUTHORIZATION unit_tester; SET search_path TO unit_tests, my_schema, public; CREATE TABLE unit_test_parameters ( current_time timestamp without time zone NOT NULL DEFAULT now() ); Excellent advice; this model works wonderfully. pg_catalog is normally implicit, but you're right, system functions can be overridden by setting the search path. Eric Radman | http://eradman.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] how to call a stored function from another stored function? even possible?
I have a stored functionA that returns void Inside there I have a line that says: select functionB(); and it gives this error. ERROR: query has no destination for result data HINT: If you want to discard the results of a SELECT, use PERFORM instead. CONTEXT: PL/pgSQL function functionA line 13 at SQL statement ** Error ** any ideas?
Re: [GENERAL] libpq 8.3 and 8.4 interfaces
Thanks for the quick support Best Regard From: John R Pierce pie...@hogranch.com To: pgsql-general@postgresql.org Sent: Tuesday, October 11, 2011 9:52 PM Subject: Re: [GENERAL] libpq 8.3 and 8.4 interfaces On 10/11/11 12:42 PM, salah jubeh wrote: Could someone point me, where I can find the difference between libpq 8.3 and 8.4, I have seen the new features of the 8.4, but I want to know about the API interface changes open http://www.postgresql.org/docs/8.4/static/libpq.html and http://www.postgresql.org/docs/8.3/static/libpq.html side by side and compare each subchapter ? I don't believe there are any API 'changes' that break backwards compatability, but new versions might add APIs (I can't recall any such between 8.3 and 8.4, however). -- john r pierce N 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] libpq 8.3 and 8.4 interfaces
John R Pierce wrote: On 10/11/11 12:42 PM, salah jubeh wrote: Could someone point me, where I can find the difference between libpq 8.3 and 8.4, I have seen the new features of the 8.4, but I want to know about the API interface changes open http://www.postgresql.org/docs/8.4/static/libpq.html and http://www.postgresql.org/docs/8.3/static/libpq.html side by side and compare each subchapter ? I don't believe there are any API 'changes' that break backwards compatability, but new versions might add APIs (I can't recall any such between 8.3 and 8.4, however). Certainly the release notes will document any changes. Is there something missing? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to call a stored function from another stored function? even possible?
On Oct 11, 2011, at 15:54, Java Services jvsr...@gmail.com wrote: I have a stored functionA that returns void Inside there I have a line that says: select functionB(); and it gives this error. ERROR: query has no destination for result data HINT: If you want to discard the results of a SELECT, use PERFORM instead. CONTEXT: PL/pgSQL function functionA line 13 at SQL statement ** Error ** any ideas? If you want to discard the results of a SELECT, use PERFORM instead. See 39.5.2 David J. -- 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] libpq 8.3 and 8.4 interfaces
Hello Bruce, Nothing is missing, I was looking for a summary of what has changed in libpq. But certainly the links are more than helpful. Thanks again for the quick response Regards From: Bruce Momjian br...@momjian.us To: John R Pierce pie...@hogranch.com Cc: pgsql-general@postgresql.org Sent: Tuesday, October 11, 2011 9:55 PM Subject: Re: [GENERAL] libpq 8.3 and 8.4 interfaces John R Pierce wrote: On 10/11/11 12:42 PM, salah jubeh wrote: Could someone point me, where I can find the difference between libpq 8.3 and 8.4, I have seen the new features of the 8.4, but I want to know about the API interface changes open http://www.postgresql.org/docs/8.4/static/libpq.html and http://www.postgresql.org/docs/8.3/static/libpq.html side by side and compare each subchapter ? I don't believe there are any API 'changes' that break backwards compatability, but new versions might add APIs (I can't recall any such between 8.3 and 8.4, however). Certainly the release notes will document any changes. Is there something missing? -- Bruce Momjian br...@momjian.us http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] how to save primary key constraints
I need to be able to query for all primary keys and save the table name and the name of the primary key field into some structure that I can iterate through later. How would I go about this? I want to hard code the number of tables and be able to iterate through some structure to get the table name and the primary key field. Regards, J.V. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to call a stored function from another stored function? even possible?
On 11/10/2011 20:54, Java Services wrote: I have a stored functionA that returns void Inside there I have a line that says: select functionB(); and it gives this error. ERROR: query has no destination for result data HINT: If you want to discard the results of a SELECT, use PERFORM instead. CONTEXT: PL/pgSQL function functionA line 13 at SQL statement As the message says, in pl/pgsql you need to specify a destination for the returned data: create or replace function as $$ declare my_value begin select function_b() into my_value; (etc) If you don't need the return value of function_b(), or if it returns void, then use PERFORM instead: perform function_b(); Gory details here: www.postgresql.org/docs/9.1/static/plpgsql-statements.html HTH Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to save primary key constraints
On 10/11/11 2:16 PM, J.V. wrote: I need to be able to query for all primary keys and save the table name and the name of the primary key field into some structure that I can iterate through later. How would I go about this? I want to hard code the number of tables and be able to iterate through some structure to get the table name and the primary key field. that info is all in pg_catalog... pg_tables is a view of all tables... if you left join that with pg_index qualified by indisprimary, you'll probably get what you need. you'll probably need to join pg_namespace to get the index name from its oid. -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] libpq 8.3 and 8.4 interfaces
On Tue, Oct 11, 2011 at 3:03 PM, salah jubeh s_ju...@yahoo.com wrote: Hello Bruce, Nothing is missing, I was looking for a summary of what has changed in libpq. But certainly the links are more than helpful. Thanks again for the quick response Regards another great place to get a bird's eye view of what's going on with libpq is here: http://git.postgresql.org/gitweb/?p=postgresql.git;a=history;f=src/interfaces/libpq/libpq-fe.h;h=d13a5b94ab6b858839d8b90ddb4f676860183e44;hb=master and look for 'add'. not much has really been added -- probably the biggest change is the events system. 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] Should casting to integer produce same result as trunc()
-Original Message- From: Alban Hertroys [mailto:haram...@gmail.com] Sent: Wednesday, 12 October 2011 1:35 AM To: Tom Lane Cc: Harvey, Allan AC; pgsql-general@postgresql.org Subject: Re: [GENERAL] Should casting to integer produce same result as trunc() On 11 October 2011 15:41, Tom Lane t...@sss.pgh.pa.us wrote: Alban Hertroys haram...@gmail.com writes: On 11 Oct 2011, at 2:55, Harvey, Allan AC wrote: My simple understanding of trunc() and casting to an integer says that there is a bug here. Which the type-cast should round to 4380103 and 4380104 respectively. It doesn't: That's because a cast from float to int rounds, it doesn't truncate. regression=# select (4.7::float8)::int; int4 -- 5 (1 row) I figured it would be something like that. Is that how it's defined in the SQL standard? All other programming languages I've come to know truncate floats on such casts (C, php, python, java, to name a few). This is probably quite surprising to people used to these languages. Thanks guys, yes C/C++ is my poison, forming my expectations. Allan The material contained in this email may be confidential, privileged or copyrighted. If you are not the intended recipient, use, disclosure or copying of this information is prohibited. If you have received this document in error, please advise the sender and delete the document. Neither OneSteel nor the sender accept responsibility for any viruses contained in this email or any attachments. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to save primary key constraints
On 10/11/2011 05:16 PM, J.V. wrote: I need to be able to query for all primary keys and save the table name and the name of the primary key field into some structure that I can iterate through later. How would I go about this? I want to hard code the number of tables and be able to iterate through some structure to get the table name and the primary key field. A query such as the following may help: SELECT nspname, conrelid::regclass::name, conname FROM pg_constraint c JOIN pg_namespace ON (connamespace = pg_namespace.oid) LEFT JOIN pg_class on (conname = relname) WHERE (nspname != 'pg_catalog' AND nspname != 'information_schema') AND contype = 'p' ORDER BY nspname, 2, conname; The first column is the schema name, the second the table name and the third the constraint (primary key) name. Joe -- 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] Postgres 9.01, Amazon EC2/EBS, XFS, JDBC and lost connections
On Mon, Oct 10, 2011 at 8:09 AM, Craig Ringer ring...@ringerc.id.au wrote: On 10/07/2011 01:21 AM, Sean Laurent wrote: Within a few seconds of the backup, our application servers start throwing exceptions that indicate the database connection was closed. Meanwhile, Postgres still shows the connections and we start seeing a really high number (for us) of locks in the database. The application servers refuse to recover and must be killed and restarted. Once they're killed off, the connections actually go away and the locks disappear. Did you have any luck with this? No, but I have avoided it by simply not using xfs_freeze and snapshotting EBS volumes. Instead I've started taking pg_dumps off the slave database. This sort of thing sounds a lot like deadlock ... but I'm not really sure how Pg's backends/postmaster could get into a deadlock with each other. It'd be interesting to look at wchan in ps to see what the Pg processes are waiting on. That's definitely a strong contender. It may be that the xfs_freeze timing was an unrelated problem or even just a coincidence. Can you reproduce this on a non-EC2 system? Unfortunately, we don't have the hardware resources to test this on a non-EC2 system. -- Sean Laurent Director of Operations StudyBlue, Inc. -- 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] Postgres 9.01, Amazon EC2/EBS, XFS, JDBC and lost connections
On Fri, Oct 7, 2011 at 12:36 AM, Tom Lane t...@sss.pgh.pa.us wrote: Sean Laurent s...@studyblue.com writes: We've been running into a particularly strange problem that I'm trying to better understand. The super short version is that our application servers lose their connection to the database when I run a backup during periods of higher load and fail to reconnect. That's just weird. It sounds like the xfs_freeze operation, or the snapshotting operation, is somehow interrupting network traffic. I'd not expect such a thing on a normal server, but who knows what's connected to what in an Amazon EC2 instance? Anyway, I'd suggest trying to instrument something to prove or disprove that there's a networking failure involved. It might be as simple as watching ping behavior ... Agreed that's it very weird. EBS volumes are effectively networked attached storage, so blaming network connectivity was my first inclination as well. Unfortunately, it's definitely not a network failure: - AWS support team has not detected any network outages affecting the EC2 instance or the EBS volumes at any time remotely near when our outages occurred. - I can consistently ping the database instance from the application servers while the problem is occurring. - I can SSH into the database instance and access Postgres while the problem is occurring. -- Sean Laurent Director of Operations StudyBlue, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] how to find primary key field name?
If I have a table name, I know how to find the primary key constraint name, but see no way to find the primary key field name. select constraint_name from information_schema.tabale_constraints where table_name = table_name and constraint_type = 'PRIMARY KEY'; will return the constraint name, but given the table_name and the constraint_name, how do I find the database column/field name associated with that primary key? J.V. -- 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] Postgres 9.01, Amazon EC2/EBS, XFS, JDBC and lost connections
On Tue, Oct 11, 2011 at 12:04 AM, Craig Ringer ring...@ringerc.id.au wrote: On 11/10/11 12:48, John R Pierce wrote: On 10/10/11 7:44 PM, Craig Ringer wrote: If blocking writes causes a server failure that persists once writes have been unblocked, that's a bug IMO. You might have a bit of a backlog of writes to clear, but after that all should be well, and if it isn't then something needs fixing. the process is blocked waiting for this disk write to complete, meanwhile, the packets are queuing up and waiting for service. best of luck with all that xfs_freeze for long enough to take a snapshot doesn't take long, or it shouldn't, anyway. On average, xfs_freeze takes about 2 seconds for us with 8 EBS volumes at 60GB each in a software RAID-0 array. Even if it did, that shouldn't cause a server failure that persists past when disk I/O is resumed, though it might cause individual connections to drop. DELETED It is totally unreasonable for Pg to *stay* nonfunctional once disk I/O resumes. Existing connections should receive responses they're waiting on or die, depending on how long it's been, and new connections should be accepted fine. Exactly. I genuinely expect Postgres to be able to withstand a couple of seconds of blocked disk I/O. Especially since this isn't a heavy duty transaction processing system - it's under load, but not a tremendously high load. During our busier times we average something in the neighborhood of 300-400 transactions per second, which just doesn't seem like that much. As much as I would like Postgres to withstand a 2 second outage, I don't honestly care. I'd just like to figure out whether I'm looking at something that's actually a problem or if I should be looking elsewhere for the problem. -- Sean Laurent Director of Operations StudyBlue, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to find primary key field name?
On Tuesday, October 11, 2011 3:54:09 pm J.V. wrote: If I have a table name, I know how to find the primary key constraint name, but see no way to find the primary key field name. select constraint_name from information_schema.tabale_constraints where table_name = table_name and constraint_type = 'PRIMARY KEY'; will return the constraint name, but given the table_name and the constraint_name, how do I find the database column/field name associated with that primary key? Join against constraint_column_usage?: http://www.postgresql.org/docs/9.1/interactive/infoschema-constraint-column- usage.html J.V. -- 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] how to find primary key field name?
On 10/11/2011 06:54 PM, J.V. wrote: If I have a table name, I know how to find the primary key constraint name, but see no way to find the primary key field name. select constraint_name from information_schema.tabale_constraints where table_name = table_name and constraint_type = 'PRIMARY KEY'; will return the constraint name, but given the table_name and the constraint_name, how do I find the database column/field name associated with that primary key? If you query pg_constraint as I showed you before, you can also get conkey which is an array of smallints pointing at the columns (in pg_attribute) that form the key. Joe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to save primary key constraints
pg_catalog table does not exist. This is a solution for PostgreSQL 8.4. If you know of a way I can get all primary key fields or have a query that will work in 8.4, please help. I have done a lot of research and cannot find a simple way. J.V. On 10/11/2011 3:29 PM, John R Pierce wrote: On 10/11/11 2:16 PM, J.V. wrote: I need to be able to query for all primary keys and save the table name and the name of the primary key field into some structure that I can iterate through later. How would I go about this? I want to hard code the number of tables and be able to iterate through some structure to get the table name and the primary key field. that info is all in pg_catalog... pg_tables is a view of all tables... if you left join that with pg_index qualified by indisprimary, you'll probably get what you need. you'll probably need to join pg_namespace to get the index name from its oid. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to save primary key constraints
On 12/10/2011 00:24, J.V. wrote: pg_catalog table does not exist. It's not a table, it's PostgreSQL's version of the information_schema catalog: http://www.postgresql.org/docs/8.4/static/catalogs.html Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to save primary key constraints
On Tue, Oct 11, 2011 at 4:33 PM, Raymond O'Donnell r...@iol.ie wrote: On 12/10/2011 00:24, J.V. wrote: pg_catalog table does not exist. It's not a table, it's PostgreSQL's version of the information_schema catalog: http://www.postgresql.org/docs/8.4/static/catalogs.html Not quite. PostgreSQL has an information_schema too. The pg_catalog is the schema of system catalogs for PostgreSQL. The catalogs are not guaranteed to be stable interfaces the way the information_schema is. Best Wishes, Chris Travers -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to save primary key constraints
On 10/11/11 4:24 PM, J.V. wrote:
pg_catalog table does not exist.
This is a solution for PostgreSQL 8.4.
pg_catalog is a schema that has about 150 views and tables in it.
pg_tables is one such, as is pg_indexes (these two are both views)
you do realize, the primary key might not BE a field? it could easily
be an expression, or multiple fields.
this will list all non-catalog tables and any indexes they have.
select t.schemaname||'.'||t.tablename as name, i.indexname as
index, i.indexdef
from pg_tables t left outer join pg_indexes i
using (schemaname, tablename)
where t.schemaname not in ('pg_catalog', 'information_schema');
it doesn't identify the primary index, except via the _pkey in the name,
however.
the pg_indexes view doesn't include the indisprimary boolean field of
pg_index, so you'd need to expand that view, and I'm too tired to think
that clearly right now.
--
john r pierceN 37, W 122
santa cruz ca mid-left coast
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to find primary key field name?
On 10/11/2011 6:54 PM, J.V. wrote:
If I have a table name, I know how to find the primary key constraint
name, but see no way to find the primary key field name.
SELECT t.table_catalog,
t.table_schema,
t.table_name,
kcu.constraint_name,
kcu.column_name,
kcu.ordinal_position
FROMINFORMATION_SCHEMA.TABLES t
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
ON tc.table_catalog = t.table_catalog
AND tc.table_schema = t.table_schema
AND tc.table_name = t.table_name
AND tc.constraint_type = 'PRIMARY KEY'
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
ON kcu.table_catalog = tc.table_catalog
AND kcu.table_schema = tc.table_schema
AND kcu.table_name = tc.table_name
AND kcu.constraint_name = tc.constraint_name
WHERE t.table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY t.table_catalog,
t.table_schema,
t.table_name,
kcu.constraint_name,
kcu.ordinal_position;
For multi-column PKs, you'll have to deal with multiple rows (ordered by
ordinal_position), or you can array_agg them if you like.
-- Stephen
--
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] Postgres 9.01, Amazon EC2/EBS, XFS, JDBC and lost connections
On Tue, Oct 11, 2011 at 5:00 PM, Sean Laurent s...@studyblue.com wrote: As much as I would like Postgres to withstand a 2 second outage, I don't honestly care. I'd just like to figure out whether I'm looking at something that's actually a problem or if I should be looking elsewhere for the problem. Any chance this is a client side failure? I.e. the client lib is seeing the 2+ second zero response time as a disconnect? -- 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] 7
On Oct 11, 2011, at 8:18 PM, The Great SunWuKung wrote: This shop is number 1 at my shop-list! So why the fuck is your spam title 7??? -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Drill-downs and OLAP type data
Hi there! We have a typical data-warehouse type application, and we'd like to set up a star-schema type data analysis software product (which we'll be programming), on top of PG. The goal is to do fast roll-up, drill-down, and drill-through of objects / tables like locations, inventory items, and sales volume. After a few weeks of searching around, we're running into dead-ends on the front-end, and the back-end. PG doesn't support OLAP / MDX and the GUI tools that do this, for the most part, require MDX / OLAP (SPSS and DB2, MS SQL Analytics, etc...). What's the PG route here? Are there some secrets / tips / tricks / contrib modules for handling this? -- Anthony Presley
Re: [GENERAL] Postgres 9.01, Amazon EC2/EBS, XFS, JDBC and lost connections
On Tue, Oct 11, 2011 at 8:50 PM, Scott Marlowe scott.marl...@gmail.com wrote: On Tue, Oct 11, 2011 at 5:00 PM, Sean Laurent s...@studyblue.com wrote: As much as I would like Postgres to withstand a 2 second outage, I don't honestly care. I'd just like to figure out whether I'm looking at something that's actually a problem or if I should be looking elsewhere for the problem. Any chance this is a client side failure? I.e. the client lib is seeing the 2+ second zero response time as a disconnect? Good question. I don't know. Let me look into that and get back to the list when I have an better answer. -- Sean Laurent Director of Operations StudyBlue, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] how to save primary key constraints
Hi, On 12 October 2011 08:16, J.V. jvsr...@gmail.com wrote: I need to be able to query for all primary keys and save the table name and the name of the primary key field into some structure that I can iterate through later. psql -E is your friend here. Then use \d table and you get several internal queries like this: SELECT c.oid, n.nspname, c.relname FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relname ~ '^(queue)$' AND pg_catalog.pg_table_is_visible(c.oid) ORDER BY 2, 3; oid | nspname | relname ---+-+- 26732 | public | queue SELECT conname, conrelid::pg_catalog.regclass, pg_catalog.pg_get_constraintdef(c.oid, true) as condef FROM pg_catalog.pg_constraint c WHERE c.confrelid = '26732' AND c.contype = 'f' ORDER BY 1; conname |conrelid| condef ---++-- T_fkey | T | FOREIGN KEY (queue) REFERENCES queue(id) ... -- Ondrej Ivanic (ondrej.iva...@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
[GENERAL] Question on GiST re-index
Hi All, I am trying to upgrade my postgres server from 8.3.3 to 8.3.15. Postgres 8.3.15 has a dependency on 8.3.8 and this has a dependency on 8.3.5. *.3.5 states to reindex all GiST indexes after the upgrade. Also 8.3.8 states 'fix hash calculation for data type 'interval'. Will these (both reindexing) be covered by a full re-index of the DB using the reindexdb.exe command? Thanks, Krishnanand Visit our Website at http://www.rmesi.co.in This message is confidential. You should not copy it or disclose its contents to anyone. You may use and apply the information for the intended purpose only. Internet communications are not secure; therefore, RMESI does not accept legal responsibility for the contents of this message. Any views or opinions presented are those of the author only and not of RMESI. If this email has come to you in error, please delete it, along with any attachments. Please note that RMESI may intercept incoming and outgoing email communications. Freedom of Information Act 2000 This email and any attachments may contain confidential information belonging to RMESI. Where the email and any attachments do contain information of a confidential nature, including without limitation information relating to trade secrets, special terms or prices these shall be deemed for the purpose of the Freedom of Information Act 2000 as information provided in confidence by RMESI and the disclosure of which would be prejudicial to RMESI's commercial interests. This email has been scanned for viruses by Trend ScanMail.
[GENERAL] I need to load mysql dump to postgres...
good noon, subj. I don't want to load dump to mysql etc... Is there a program which would just parse mysql dump file and load data to postgresql using plain sql inserts? 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] I need to load mysql dump to postgres...
On 10/12/2011 10:37 AM, unclebob wrote: good noon, subj. I don't want to load dump to mysql etc... Is there a program which would just parse mysql dump file and load data to postgresql using plain sql inserts? There's no single, simple automatic migration tool. Numerous tools exist to help. See a simple Google search for convert mysql postgresql, the first result of which is: http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL For more complex jobs you may want to look at ETL tools like Pentaho or Talend, but the nature of your question suggests it's probably a fairly simple database. It's often easiest to just hand-write the new schema, then do a data-only MySQL dump in portable mode (with inserts) and run that through psql. Use mysqldump WITHOUT the --all or -a option so it doesn't dump as much MySQL-specific stuff, and use --no-create-db --no-create-info to suppress the schema definitions. Then edit out any remaining MySQL-specific stuff and feed it into psql. -- 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] Drill-downs and OLAP type data
On 10/12/2011 11:50 AM, Anthony Presley wrote: What's the PG route here? Are there some secrets / tips / tricks / contrib modules for handling this? I don't see much discussion of DW, OLAP-type workloads here. Pg doesn't support index-oriented tables (though IIRC 9.2 will add covering indexes - yay!), column-oriented storage, or other features that're pretty basic to OLAP workloads. Have you looked at Greenplum? -- 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] 7
On 10/11/2011 11:34 PM, Scott Ribe wrote: On Oct 11, 2011, at 8:18 PM, The Great SunWuKung wrote: This shop is number 1 at my shop-list! So why the fuck is your spam title 7??? Because 1 through 6 already get caught as SPAM? -- 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] Drill-downs and OLAP type data
Mondrian (which is a part of Pentaho BI stack) is an open source OLAP engine with MDX. See http://community.pentaho.com/projects/bi_platform/ 2011/10/12 Anthony Presley anth...@resolution.com Hi there! We have a typical data-warehouse type application, and we'd like to set up a star-schema type data analysis software product (which we'll be programming), on top of PG. The goal is to do fast roll-up, drill-down, and drill-through of objects / tables like locations, inventory items, and sales volume. After a few weeks of searching around, we're running into dead-ends on the front-end, and the back-end. PG doesn't support OLAP / MDX and the GUI tools that do this, for the most part, require MDX / OLAP (SPSS and DB2, MS SQL Analytics, etc...). What's the PG route here? Are there some secrets / tips / tricks / contrib modules for handling this? -- Anthony Presley
