Re: [GENERAL] Apparent Problem With NULL in Restoring pg_dump
On 09/16/2011 04:42 PM, Rich Shepard wrote: On Thu, 15 Sep 2011, Andy Colson wrote: First you need to trim the \n and spaces: andy=# insert into junk values (E'GW-22'); INSERT 0 1 andy=# insert into junk values (E'GW-22 \n'); INSERT 0 1 andy=# insert into junk values (E'GW-22 \n'); Andy, Here's what worked for me: nevada=# \i junk.sql CREATE TABLE nevada=# insert into junk select * from chemistry where site_id = (E'GW-22'); INSERT 0 803 nevada=# insert into junk select * from chemistry where site_id = (E'GW-22 \n'); INSERT 0 0 nevada=# insert into junk select * from chemistry where site_id = (E'GW-22 \n'); INSERT 0 0 nevada=# insert into junk select * from chemistry where site_id = (E'GW-22\n'); INSERT 0 1409 nevada=# select '['|| rtrim(trim(trailing E'\n' from site_id)) || ']' from junk; ?column? -- [GW-22] [GW-22] and so on for 2212 rows. Trim it up: andy=# select '['|| rtrim(trim(trailing E'\n' from a)) || ']' from junk; If you have a unique index you'll wanna drop it first. Once you get that done, we can remove the dups. No index on junk; I can remove it from chemistry prior to reinserting the cleaned rows. Also, where can I read about the select syntax you use? I find nothing about it in Rick van der Lans' 4th edition, the most comprehensive language reference I've read. Thanks, Rich The fine online manual: http://www.postgresql.org/docs/current/interactive/index.html Especially the string ops: http://www.postgresql.org/docs/current/interactive/functions-string.html Trim it up: andy=# select '['|| rtrim(trim(trailing E'\n' from a)) || ']' from junk; Andy, Scrolling through the table with rows ordered by date and chemical I find no duplicates ... so far. However, what I do find is that the above did not work: No, it wasnt supposed to. A select statement builds a new result set and returns it to you, it wont update a table. That select statement was meant as an example for writing an update statement. Like: update chemistry set side_id = rtrim(trim(trailing E'\n' from site_id)); If there was a unique index on chemistry(site_id), the above would throw an error, so I was warning you to drop it. Once the site_id was trimmed, you could then delete the dups, with: delete from chemistry where site_id = 'GW-22' and ctid <> (select min(ctid) from chemistry site_id = 'GW-22'); Those 11 steps you had... I was thinking two steps. The update and the delete above. Sorry, I should have been a little more clear, but, at least you got things cleaned up. PG has a huge number of data manipulation functions. If you have to export data out of a database in order to massage it, then that's a failure of a database. PG (and sql) were meant for just this kind of job. -Andy -- 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] Arrays
On Wed, Sep 14, 2011 at 21:05, Fabrízio de Royes Mello wrote: > postgres@bdteste=# SELECT array_upper(ARRAY['foo', 'bar'], 1); On Wed, Sep 14, 2011 at 21:09, Merlin Moncure wrote: > select count(*) from unnest(_array_); On Wed, Sep 14, 2011 at 21:15, Steve Crawford wrote: > Look at array_dims, array_upper and array_lower. Huh, what's up with people suggesting overcomplicated solutions? Just use the one function that's designed to do this: array_length(arr, 1) Note that for an empty array, this will return NULL. If you want to get 0 instead, use: coalesce(array_length(arr, 1), 0) Note that, for multidimensional arrays, this returns the length of the 1st dimension (hence 1 in arguments) Regards, Marti -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: RES: [GENERAL] Foreign PostgreSQL server
On Friday, September 16, 2011 4:03:03 pm Edson Carlos Ericksson Richter wrote: > Ok, managed to get the first part working: > > --- > create extension dblink; > CREATE FOREIGN DATA WRAPPER postgresql VALIDATOR postgresql_fdw_validator; > CREATE SERVER simfrete02 FOREIGN DATA WRAPPER postgresql OPTIONS (host > '127.0.0.1', dbname 'simfrete', port '5432'); > CREATE FOREIGN TABLE emp02 (ID integer NOT NULL, NOME varchar(100) NOT > NULL) SERVER simfrete02; > select * from usuario join emp02 on usuario.empresa_id = emp02.id; > --- > This error relates to the "select ..." part of the script above: > > ERRO: foreign-data wrapper "postgresql" has no handler > ** Error ** > ERRO: foreign-data wrapper "postgresql" has no handler > SQL state: 55000 You did not specify a handler for the FDW postgresql and per the Docs: http://www.postgresql.org/docs/9.1/interactive/sql-createforeigndatawrapper.html "It is possible to create a foreign-data wrapper with no handler function, but foreign tables using such a wrapper can only be declared, not accessed. " > > > If I remove the "select ...", then the script runs without any error. > > What then? > > > Thanks for your support, > > Edson Richter -- 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 get Transaction Timestamp ?
On Fri, Sep 16, 2011 at 21:39, Raghavendra wrote: > We can get a Transaction ID, but not the transaction timestamp when it > performed. Short answer: You can't. Instead, add a new "timestamptz default now()" column, that will get you the time of the insert. If you want the update time, create a BEFORE UPDATE ON x FOR EACH ROW trigger on this table to update it. Regards, Marti -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL]
http://drpersoff.com/invitation.html";>http://drpersoff.com/invitation.html
Re: [GENERAL] Indexes not allowed on (read-only) views: Why?
On 09/17/2011 05:47 AM, Stefan Keller wrote: A (read-only) view should behave like a table, right? CREATE INDEX t1_idx ON t1 (rem); ERROR: »v1« not a table SQL state: 42809 => Why should'nt it be possible to create indexes on views in PG? It's not so much that it's not allowed, as that it's not implemented and not very practical for non-materialized views. A normal (non-materialized) view doesn't have any data of its own, it pulls it from one or more other tables on the fly during query execution. The execution of a view is kind of similar to a set-returning function or a subquery, almost as if you'd substituted the view definition into the original query. That means that the view will use any indexes on the original table(s), but there isn't really even an opportunity to check for indexes on the view its self because the view's definition is effectively substituted into the query. If the view definition is complex enough that it does a lot of work where indexes on the original table(s) don't help, that work has to be done every time. It only really makes sense to have indexes on materialized views. PostgreSQL doesn't have any native support for materialized views, so it doesn't support indexes on views. What you *CAN* do is use triggers to maintain your own materialized views as regular tables, and have indexes on the tables you maintain using triggers. This is widely discussed on the mailing list and isn't hard to do, though it's tricky to make updates perform well with some kinds of materialized view query. And there is no practical reason since SQL Server can do it! See "Creating Indexes on Views" http://msdn.microsoft.com/en-us/library/aa933124%28v=sql.80%29.aspx It sounds like they probably use a materialized view, possibly stored as an index-oriented table. That'd be a cool thing to support, but if done that way would require TWO new major features PostgreSQL doesn't have. -- 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] Apparent Problem With NULL in Restoring pg_dump [SOLVED]
On Fri, 16 Sep 2011, Rich Shepard wrote: Scrolling through the table with rows ordered by date and chemical I find no duplicates ... so far. However, what I do find is that the above did not work: Turns out there was 1 duplicate. Reading the psql man page and making an error in the \copy command syntax taught me the (E' ') escape syntax. Kool! To close this thread, here's what I did to remove the table rows with embedded newlines: 1) Made a copy of the chemistry table named 'junk'. 2) insert into junk select * from chemistry where site_id = 'GW-22'; 3) insert into junk select * from chemistry where site_id = (E'GW-22\n'); 4) \copy from junk to '' 5) In emacs, use global search and replace to remove unwanted '\n'. 6) delete from junk where site_id = 'GW-22'; 7) delete from junk where site_id = (E'GW-22\n'); 8) \copy junk from ' 9) delete from chemistry where site_id = 'GW-22'; 10) delete from chemistry where site_id = (E'GW-22\n'); 11) insert into chemistry select * from junk; The latter found the one duplicate so I fixed that in emacs, then dropped junk and repeated steps 8-11. This message is as much for my future reference as it is for others who might face the same problem. Rich -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
RES: [GENERAL] Foreign PostgreSQL server
Ok, managed to get the first part working: --- create extension dblink; CREATE FOREIGN DATA WRAPPER postgresql VALIDATOR postgresql_fdw_validator; CREATE SERVER simfrete02 FOREIGN DATA WRAPPER postgresql OPTIONS (host '127.0.0.1', dbname 'simfrete', port '5432'); CREATE FOREIGN TABLE emp02 (ID integer NOT NULL, NOME varchar(100) NOT NULL) SERVER simfrete02; select * from usuario join emp02 on usuario.empresa_id = emp02.id; --- This error relates to the "select ..." part of the script above: ERRO: foreign-data wrapper "postgresql" has no handler ** Error ** ERRO: foreign-data wrapper "postgresql" has no handler SQL state: 55000 If I remove the "select ...", then the script runs without any error. What then? Thanks for your support, Edson Richter -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Foreign PostgreSQL server
Hi! Sorry if this was already asked. My platform: Win7, PostgreSQL 9.1 64bit installed using EnterpriseDB package. Im trying to setup a bunch of foreign PostgreSQL servers, but whenever I execute the script below, I get errors: -- drop foreign data wrapper postgresql_fdw cascade; create extension dblink; CREATE FOREIGN DATA WRAPPER postgresql_fdw VALIDATOR postgresql_fdw_validator HANDLER postgresql_fdw_handler; CREATE SERVER simfrete02 FOREIGN DATA WRAPPER pgsql OPTIONS (host '127.0.0.1', dbname 'simfrete', port '5432'); --- And the errors are: ERROR: function postgresql_fdw_handler() does not exists ** Error ** ERROR: function postgresql_fdw_handler() does not exists SQL state: 42883 Can you point what I'm doing wrong? Thanks & Regards, Edson Carlos Ericksson Richter SimKorp Infomática Ltda Fone: (51) 3366-7964 Celular: (51) 8585-0796 www.simkorp.com.br -- 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] Apparent Problem With NULL in Restoring pg_dump
On Thu, 15 Sep 2011, Andy Colson wrote: Trim it up: andy=# select '['|| rtrim(trim(trailing E'\n' from a)) || ']' from junk; Andy, Scrolling through the table with rows ordered by date and chemical I find no duplicates ... so far. However, what I do find is that the above did not work: GW-22 | 1998-12-16 | Zinc| 0.01 GW-22 | 1998-12-16 | pH | 7.83 GW-22 +| 1999-03-09 | Alkalinity, Bicarbonate |97 | | | GW-22 +| 1999-03-09 | Alkalinity, Total |80 | | | GW-22 +| 1999-03-09 | Aluminum| 0.01 | | | GW-22 +| 1999-03-09 | Antimony| As my previous message noted, selecting rows with two spaces before the newline returned 0 rows. I'll continue looking for duplicates, but I suspect there are none. I just need to get rid of the newlines, drop all rows for site_id from the chemistry table, then insert the cleaned rows from the junk table. Regards, Rich -- 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] Indexes not allowed on (read-only) views: Why?
On Fri, Sep 16, 2011 at 4:47 PM, Stefan Keller wrote: > A (read-only) view should behave like a table, right? > >> CREATE INDEX t1_idx ON t1 (rem); > ERROR: »v1« not a table > SQL state: 42809 > > => Why should'nt it be possible to create indexes on views in PG? > > An index on a view can speed up access to the tuples underlying. And > "indexed views" could be a method of storing the result set of the > view in the database, thereby reducing the overhead of dynamically > building the result set. An "indexed view" should automatically adapt > modifications made to the data in the base tables. So, there is some > overhead here, but this is ok when speed in retrieving results > outweighs the cost... > > And there is no practical reason since SQL Server can do it! See > "Creating Indexes on Views" > http://msdn.microsoft.com/en-us/library/aa933124%28v=sql.80%29.aspx implementation across databases is different. in postgres, views are essentially macros, thus there is no data to index. 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] Indexes not allowed on (read-only) views: Why?
A (read-only) view should behave like a table, right? > CREATE INDEX t1_idx ON t1 (rem); ERROR: »v1« not a table SQL state: 42809 => Why should'nt it be possible to create indexes on views in PG? An index on a view can speed up access to the tuples underlying. And "indexed views" could be a method of storing the result set of the view in the database, thereby reducing the overhead of dynamically building the result set. An "indexed view" should automatically adapt modifications made to the data in the base tables. So, there is some overhead here, but this is ok when speed in retrieving results outweighs the cost... And there is no practical reason since SQL Server can do it! See "Creating Indexes on Views" http://msdn.microsoft.com/en-us/library/aa933124%28v=sql.80%29.aspx Stefan -- 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] Apparent Problem With NULL in Restoring pg_dump
On Thu, 15 Sep 2011, Andy Colson wrote: First you need to trim the \n and spaces: andy=# insert into junk values (E'GW-22'); INSERT 0 1 andy=# insert into junk values (E'GW-22 \n'); INSERT 0 1 andy=# insert into junk values (E'GW-22 \n'); Andy, Here's what worked for me: nevada=# \i junk.sql CREATE TABLE nevada=# insert into junk select * from chemistry where site_id = (E'GW-22'); INSERT 0 803 nevada=# insert into junk select * from chemistry where site_id = (E'GW-22 \n'); INSERT 0 0 nevada=# insert into junk select * from chemistry where site_id = (E'GW-22 \n'); INSERT 0 0 nevada=# insert into junk select * from chemistry where site_id = (E'GW-22\n'); INSERT 0 1409 nevada=# select '['|| rtrim(trim(trailing E'\n' from site_id)) || ']' from junk; ?column? -- [GW-22] [GW-22] and so on for 2212 rows. Trim it up: andy=# select '['|| rtrim(trim(trailing E'\n' from a)) || ']' from junk; If you have a unique index you'll wanna drop it first. Once you get that done, we can remove the dups. No index on junk; I can remove it from chemistry prior to reinserting the cleaned rows. Also, where can I read about the select syntax you use? I find nothing about it in Rick van der Lans' 4th edition, the most comprehensive language reference I've read. Thanks, Rich -- 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] Log message " last_statrequest ... is later than collector's time" - what does it mean?
Stephan Vollmer writes: >>> 2011-09-16 13:48:54 CEST: LOG: last_statrequest 2011-09-16 >>> 13:48:55.890743+02 is later than collector's time 2011-09-16 >>> 13:48:54.614476+02 > is there a way to filter these log messages other than setting > "log_min_messages" to "fatal" which is not really desireable? Nope, not without changing the source code. If you don't mind recompiling then it's easy enough to remove the elog call (look in src/backend/postmaster/pgstat.c). 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] Log message " last_statrequest ... is later than collector's time" - what does it mean?
On Fri, Sep 16, 2011 at 19:16, Tom Lane wrote: > > Stephan Vollmer writes: > > I upgraded our test database from PostgreSQL 8.4.8 to 9.0.4 via pg_dumpall. > > The database seems to work fine, but now the logfile of the new database is > > flooded with log messages like these: > > > 2011-09-16 13:48:54 CEST: LOG: last_statrequest 2011-09-16 > > 13:48:55.890743+02 is later than collector's time 2011-09-16 > > 13:48:54.614476+02 > > Wow. AFAIK this is an indication of major system clock problems, > as in there's at least one backend process that is seeing gettimeofday() > results significantly later than what the stats collector process is > seeing. We have seen small processor-to-processor skews before, but > you've apparently got skews that are more than a second. It was > presumably happening before too, but pre-9.0 the stats collector doesn't > bleat about it --- we added that logging to try to diagnose such > problems. > > Now it's relatively harmless so far as this particular issue goes (I > think the log bleating is the only real consequence); but it's not hard > to envision very serious problems elsewhere, for instance gmake failing > to rebuild things because file timestamps are in the future compared to > what it thinks the time is. You want to get that fixed. Thank you for this information! I agree that this problem should be fixed, but unfortunately I'm not the server admin and there are other applications running on this machine (even MySQL...). In the meantime, is there a way to filter these log messages other than setting "log_min_messages" to "fatal" which is not really desireable? > > Configuration: > > - SUSE Linux Enterprise Server 10 (i586) > > - uname -a: Linux 2.6.16.21-0.8-bigsmp #1 SMP Mon Jul 3 18:25:39 UTC 2006 > > i686 athlon i386 GNU/Linux > > I'd file a bug report with SUSE. But probably the first thing they'd > say is you should be using a less ancient kernel, so maybe upgrade that > first and see if the issue goes away. I'll try to persuade the admin to install at least the latest patch version of this old kernel. Thanks for your help and kind regards, Stephan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] CUDA Sorting
Hello everyone, I'm implementing a CUDA based sorting on PostgreSQL, and I believe it can improve the ORDER BY statement performance in 4 to 10 times. I already have a generic CUDA sort that performs around 10 times faster than std qsort. I also managed to load CUDA into pgsql. Since I'm new to pgsql development, I replaced the code of pgsql qsort_arg to get used with the way postgres does the sort. Everything looks easy and "GPU-able". The only problem is that I can't use the qsort_arg_comparator comparator function on GPU, I need to implement my own. I didn't find out how to access the sorting key value data of the tuples on the Tuplesortstate or SortTuple structures. If I solve this problem I'll make a really big step forward. Cheers, Vítor Uwe Reus -- 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 get Transaction Timestamp ?
Respected All, Can we get the transaction timestamp for INSERT/UPDATE/DELETE ran against table in the database ? postgres=# create table trx_test(id int, name char(30)); CREATE TABLE postgres=# insert into trx_test VALUES (1,'AAA'); INSERT 0 1 postgres=# insert into trx_test VALUES (2,'BBB'); INSERT 0 1 postgres=# insert into trx_test VALUES (3,'CCC'); INSERT 0 1 postgres=# select xmin,* from trx_test ; xmin | id | name -++ 1348711 | 1 | AAA 1348712 | 2 | BBB 1348713 | 3 | CCC (3 rows) We can get a Transaction ID, but not the transaction timestamp when it performed. Kindly advice me. --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
Re: [GENERAL] Log duration and statement for slow queries + limiting the number of log files generated
Thanks, I got duration logging to work the way I wanted. I will look into logrotate next. On Fri, Sep 16, 2011 at 11:22 AM, Thom Brown wrote: > On 16 September 2011 18:16, Alec Swan wrote: >> Hello, >> >> I am trying to get postgres 8.4.4 to log the duration and statement of >> queries that take longer than 200 ms. I played with the log settings >> in postgresql.conf but I still see logs of durations of very fast >> statements. Here is my current configuration: >> >> log_min_duration_statement = 200 >> log_duration = on >> log_line_prefix = '<%t> ' >> log_statement = 'all' >> >> With this configuration I am still getting these in the log files: >> >> <2011-09-02 14:47:19 EDT> LOG: duration: 0.017 ms >> <2011-09-02 14:47:19 EDT> LOG: execute : SET SESSION >> CHARACTERISTICS AS TRANSACTION READ WRITE >> <2011-09-02 14:47:19 EDT> LOG: duration: 0.004 ms >> <2011-09-02 14:47:19 EDT> LOG: duration: 0.014 ms >> <2011-09-02 14:47:19 EDT> LOG: duration: 0.017 ms >> <2011-09-02 14:47:19 EDT> LOG: execute : SET SESSION >> CHARACTERISTICS AS TRANSACTION READ ONLY >> >> >> How do I filter these out? > > You have log_statement set to 'all', and since it's mostly independent > of log_min_duration_statement, it's logging everything anyway. You > may wish to just set log_statement to 'none'. > >> Also, how do I restrict the maximum number of log files generated to 2 >> with the assumption that they will roll over when filled? > > -- > Thom Brown > Twitter: @darkixion > IRC (freenode): dark_ixion > Registered Linux user: #516935 > > EnterpriseDB UK: http://www.enterprisedb.com > The Enterprise PostgreSQL Company > -- 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] Log duration and statement for slow queries + limiting the number of log files generated
On 16 September 2011 18:16, Alec Swan wrote: > Hello, > > I am trying to get postgres 8.4.4 to log the duration and statement of > queries that take longer than 200 ms. I played with the log settings > in postgresql.conf but I still see logs of durations of very fast > statements. Here is my current configuration: > > log_min_duration_statement = 200 > log_duration = on > log_line_prefix = '<%t> ' > log_statement = 'all' > > With this configuration I am still getting these in the log files: > > <2011-09-02 14:47:19 EDT> LOG: duration: 0.017 ms > <2011-09-02 14:47:19 EDT> LOG: execute : SET SESSION > CHARACTERISTICS AS TRANSACTION READ WRITE > <2011-09-02 14:47:19 EDT> LOG: duration: 0.004 ms > <2011-09-02 14:47:19 EDT> LOG: duration: 0.014 ms > <2011-09-02 14:47:19 EDT> LOG: duration: 0.017 ms > <2011-09-02 14:47:19 EDT> LOG: execute : SET SESSION > CHARACTERISTICS AS TRANSACTION READ ONLY > > > How do I filter these out? You have log_statement set to 'all', and since it's mostly independent of log_min_duration_statement, it's logging everything anyway. You may wish to just set log_statement to 'none'. > Also, how do I restrict the maximum number of log files generated to 2 > with the assumption that they will roll over when filled? -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Log duration and statement for slow queries + limiting the number of log files generated
On Fri, 2011-09-16 at 11:16 -0600, Alec Swan wrote: > Hello, > > I am trying to get postgres 8.4.4 to log the duration and statement of > queries that take longer than 200 ms. I played with the log settings > in postgresql.conf but I still see logs of durations of very fast > statements. Here is my current configuration: > > log_min_duration_statement = 200 > log_duration = on > log_line_prefix = '<%t> ' > log_statement = 'all' > > With this configuration I am still getting these in the log files: > > <2011-09-02 14:47:19 EDT> LOG: duration: 0.017 ms > <2011-09-02 14:47:19 EDT> LOG: execute : SET SESSION > CHARACTERISTICS AS TRANSACTION READ WRITE > <2011-09-02 14:47:19 EDT> LOG: duration: 0.004 ms > <2011-09-02 14:47:19 EDT> LOG: duration: 0.014 ms > <2011-09-02 14:47:19 EDT> LOG: duration: 0.017 ms > <2011-09-02 14:47:19 EDT> LOG: execute : SET SESSION > CHARACTERISTICS AS TRANSACTION READ ONLY > > > How do I filter these out? > You should set log_duration to off, and log_statement to none. log_min_duration_statement will take care of logging duration and statement if the query duration is longer than 200 ms. > Also, how do I restrict the maximum number of log files generated to 2 > with the assumption that they will roll over when filled? > You can't do that with PostgreSQL configuration. You need another tool, such as logrotate. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.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] Log message " last_statrequest ... is later than collector's time" - what does it mean?
Stephan Vollmer writes: > I upgraded our test database from PostgreSQL 8.4.8 to 9.0.4 via pg_dumpall. > The database seems to work fine, but now the logfile of the new database is > flooded with log messages like these: > 2011-09-16 13:48:54 CEST: LOG: last_statrequest 2011-09-16 > 13:48:55.890743+02 is later than collector's time 2011-09-16 > 13:48:54.614476+02 Wow. AFAIK this is an indication of major system clock problems, as in there's at least one backend process that is seeing gettimeofday() results significantly later than what the stats collector process is seeing. We have seen small processor-to-processor skews before, but you've apparently got skews that are more than a second. It was presumably happening before too, but pre-9.0 the stats collector doesn't bleat about it --- we added that logging to try to diagnose such problems. Now it's relatively harmless so far as this particular issue goes (I think the log bleating is the only real consequence); but it's not hard to envision very serious problems elsewhere, for instance gmake failing to rebuild things because file timestamps are in the future compared to what it thinks the time is. You want to get that fixed. > Configuration: > - SUSE Linux Enterprise Server 10 (i586) > - uname -a: Linux 2.6.16.21-0.8-bigsmp #1 SMP Mon Jul 3 18:25:39 UTC 2006 > i686 athlon i386 GNU/Linux I'd file a bug report with SUSE. But probably the first thing they'd say is you should be using a less ancient kernel, so maybe upgrade that first and see if the issue goes away. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Log duration and statement for slow queries + limiting the number of log files generated
Hello, I am trying to get postgres 8.4.4 to log the duration and statement of queries that take longer than 200 ms. I played with the log settings in postgresql.conf but I still see logs of durations of very fast statements. Here is my current configuration: log_min_duration_statement = 200 log_duration = on log_line_prefix = '<%t> ' log_statement = 'all' With this configuration I am still getting these in the log files: <2011-09-02 14:47:19 EDT> LOG: duration: 0.017 ms <2011-09-02 14:47:19 EDT> LOG: execute : SET SESSION CHARACTERISTICS AS TRANSACTION READ WRITE <2011-09-02 14:47:19 EDT> LOG: duration: 0.004 ms <2011-09-02 14:47:19 EDT> LOG: duration: 0.014 ms <2011-09-02 14:47:19 EDT> LOG: duration: 0.017 ms <2011-09-02 14:47:19 EDT> LOG: execute : SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY How do I filter these out? Also, how do I restrict the maximum number of log files generated to 2 with the assumption that they will roll over when filled? Thanks, Alec -- 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] Log Apply Delay
On Fri, Sep 16, 2011 at 10:53 AM, Thom Brown wrote:
> On 16 September 2011 16:41, Ian Harding wrote:
>> On Fri, Sep 16, 2011 at 8:35 AM, hubert depesz lubaczewski
>> wrote:
>>> On Fri, Sep 16, 2011 at 08:02:31AM -0700, Ian Harding wrote:
Oracle has a configuration option for its version of hot standby
(DataGuard) that lets you specify a time based delay in applying logs.
They get transferred right away, but changes in them are only applied
as they reach a certain age. The idea is that if something horrible
happens on the master, you can keep it from propagating to one or more
of your standby databases (or keep from having to reinstate one in the
case of a failover)
Anyway, Is there any plan to add a knob like that to the streaming
replication in Postgres?
>>>
>>> In streaming - no. But if you want delay, perhaps normal WAL-files based
>>> approach would be good enough? OmniPITR, for one, has a option to delay
>>> applying wal segments.
>>>
>>
>> The file based approach is pretty close, unless the Bad Thing happens
>> right before a file gets transferred. This is not a super important
>> feature to me but It's a nice security blanket and almost takes the
>> place of a PITR plan including big file transfers of the data
>> directory at regular intervals.
>
> You could always ship the log to a waiting directory on the
> destination server, then run a command like this every few mins:
>
> find /holding/dir -maxdepth 1 -mtime +1 -exec mv '{}' /actual/dir/ ';'
>
> That particular command would move all files over a day old to the
> directory the standby is looking at.
>
> Or change +1 to +1h to leave a gap of an hour instead of a day.
+1 on this approach -- there's a tremendous amount of flexibility that
you can utilize using with a non-SR hot standby if you can handle a
little scripting. another nifty trick is to multiplex the log file to
multiple receiving standbys so you only have to pay the network
bandwidth getting the file off the server once...
with non-SR hot standby, don't forget you can set archive_timeout to a
small number of minutes if the server is lightly loaded and you wand
to keep the data loss window down.
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] forcing table ownership
Hi Richard, > -Original Message- > From: Richard Huxton [mailto:d...@archonet.com] > Sent: Friday, September 16, 2011 9:54 AM > To: Darin Perusich > Cc: Richard Broersma; pgsql-general@postgresql.org > Subject: Re: [GENERAL] forcing table ownership > > On 16/09/11 14:13, Darin Perusich wrote: > > > > Altering the table owner by setting it to the group role effectively > > denies permission to all users of the group. Unless they explicitly > "SET > > role grp1" that is. > > I've already got a user "richardh" > > As a superuser: >CREATE GROUP mygroup INHERIT; >GRANT mygroup TO richardh; >CREATE TABLE shared_table (i int); > As richardh: >ALTER TABLE shared_table ADD COLUMN t text; >ERROR: must be owner of relation shared_table > As superuser: >ALTER TABLE shared_table OWNER TO mygroup; > As richardh: >ALTER TABLE shared_table ADD COLUMN t text; >ALTER TABLE > > I think the key bit you're missing is the "INHERIT" on the group. Also > note that the CREATE USER/CREATE GROUP commands actually just run > CREATE > ROLE under the hood. > This works but I found that I also needed to set the database owner to the group as well. It would be nice to be able to set the default group but it appears thats only available in PG9. Thanks. -- Darin Perusich Email: darin.perus...@ctg.com Office: 716-888-3690 The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you are not the intended recipient of this message, please contact the sender and delete this material from this computer. -- 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] different unnest function
2011/9/16 David Johnston :
> -Original Message-
> From: pgsql-general-ow...@postgresql.org
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Ondrej Ivanic
> Sent: Friday, September 16, 2011 12:54 AM
> To: pgsql-general@postgresql.org general
> Subject: [GENERAL] different unnest function
>
> Hi,
>
> I need function which unnest array in a different way. Input table has
> ineger[][] column:
> col1
> --
> {{1,2,3,4}, {5,6,7,8}, {9, 10, 11, 12}}
> {{11,12,13,14}, {15,16,17,18}, {19, 110, 111, 112}} ...
>
> and output should be:
>
> select unnest2(col1) from T
> unnest2
> -
> {1,2,3,4}
> {5,6,7,8}
> {9, 10, 11, 12}
> {11,12,13,14}
> {15,16,17,18}
> {19, 110, 111, 112}
>
> My function is:
> create or replace function unnest2(anyarray) returns setof anyarray AS $BODY$
> select $1[i:i] from generate_series(array_lower($1,1), array_upper($1,1)) i;
> $BODY$ language 'sql';
>
> and the result is:
> {{1,2,3,4}}
> {{5,6,7,8}}
> {{9, 10, 11, 12}}
> {{11,12,13,14}}
> {{15,16,17,18}}
> {{19, 110, 111, 112}}
>
> which is almost what I need...(or I'm at the beginning :)) Any ideas?
>
>
>
> Untested by try something like:
>
> SELECT ($1[i:i])[1] FROM generate_series(...) i;
>
> Basically you want to take the first (and only) array element from your
> slice. You might need to use sub-queries to get the syntax to work but the
> idea should be possible.
nope -- that doesn't work. there is no way to manipulate array
dimensionality with the [] operator except in the special case from
D1->D0 (datum). this is a fundamental awkwardness that can be weird
to most people.
to get the output OP wants, you need to expand and rewrap:
create or replace function unnest2(anyarray) returns setof anyarray AS
$BODY$
select array(select unnest($1[i:i])) from
generate_series(array_lower($1,1), array_upper($1,1)) i;
$BODY$
language 'sql';
-- ugh.
9.1 has a vastly improved (plpgsql only) way to do this -- FOREACH SLICE...
http://www.postgresql.org/docs/9.1/interactive/plpgsql-control-structures.html#PLPGSQL-FOREACH-ARRAY
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] Log Apply Delay
On 16 September 2011 16:41, Ian Harding wrote:
> On Fri, Sep 16, 2011 at 8:35 AM, hubert depesz lubaczewski
> wrote:
>> On Fri, Sep 16, 2011 at 08:02:31AM -0700, Ian Harding wrote:
>>> Oracle has a configuration option for its version of hot standby
>>> (DataGuard) that lets you specify a time based delay in applying logs.
>>> They get transferred right away, but changes in them are only applied
>>> as they reach a certain age. The idea is that if something horrible
>>> happens on the master, you can keep it from propagating to one or more
>>> of your standby databases (or keep from having to reinstate one in the
>>> case of a failover)
>>>
>>> Anyway, Is there any plan to add a knob like that to the streaming
>>> replication in Postgres?
>>
>> In streaming - no. But if you want delay, perhaps normal WAL-files based
>> approach would be good enough? OmniPITR, for one, has a option to delay
>> applying wal segments.
>>
>
> The file based approach is pretty close, unless the Bad Thing happens
> right before a file gets transferred. This is not a super important
> feature to me but It's a nice security blanket and almost takes the
> place of a PITR plan including big file transfers of the data
> directory at regular intervals.
You could always ship the log to a waiting directory on the
destination server, then run a command like this every few mins:
find /holding/dir -maxdepth 1 -mtime +1 -exec mv '{}' /actual/dir/ ';'
That particular command would move all files over a day old to the
directory the standby is looking at.
Or change +1 to +1h to leave a gap of an hour instead of a day.
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
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] Steps to use pl/pgtcl
If you install using a package manager, you might only have to install the postgresql-pltcl (or similarly named) package, then do createlang pltcl mydatabase from the command line and you are ready to go. If you build from source, you have to worry about prerequisites yourself. On Thu, Sep 15, 2011 at 10:23 PM, wrote: > Hi, > > > > If I am planning to use the procedures/packages present in an sql server in > postgresql, can I do it by using pl/pgtcl feature of postgresql? If yes, > What shall be my initial and major steps. I am new to postgresql , kindly > help me. > > > > Thanks, > > Mamatha -- 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] Log Apply Delay
On Fri, Sep 16, 2011 at 8:35 AM, hubert depesz lubaczewski wrote: > On Fri, Sep 16, 2011 at 08:02:31AM -0700, Ian Harding wrote: >> Oracle has a configuration option for its version of hot standby >> (DataGuard) that lets you specify a time based delay in applying logs. >> They get transferred right away, but changes in them are only applied >> as they reach a certain age. The idea is that if something horrible >> happens on the master, you can keep it from propagating to one or more >> of your standby databases (or keep from having to reinstate one in the >> case of a failover) >> >> Anyway, Is there any plan to add a knob like that to the streaming >> replication in Postgres? > > In streaming - no. But if you want delay, perhaps normal WAL-files based > approach would be good enough? OmniPITR, for one, has a option to delay > applying wal segments. > The file based approach is pretty close, unless the Bad Thing happens right before a file gets transferred. This is not a super important feature to me but It's a nice security blanket and almost takes the place of a PITR plan including big file transfers of the data directory at regular intervals. - Ian -- 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] Log Apply Delay
On Fri, Sep 16, 2011 at 08:02:31AM -0700, Ian Harding wrote: > Oracle has a configuration option for its version of hot standby > (DataGuard) that lets you specify a time based delay in applying logs. > They get transferred right away, but changes in them are only applied > as they reach a certain age. The idea is that if something horrible > happens on the master, you can keep it from propagating to one or more > of your standby databases (or keep from having to reinstate one in the > case of a failover) > > Anyway, Is there any plan to add a knob like that to the streaming > replication in Postgres? In streaming - no. But if you want delay, perhaps normal WAL-files based approach would be good enough? OmniPITR, for one, has a option to delay applying wal segments. Best regards, depesz -- 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] Log Apply Delay
On Fri, 2011-09-16 at 08:02 -0700, Ian Harding wrote: > Oracle has a configuration option for its version of hot standby > (DataGuard) that lets you specify a time based delay in applying logs. > They get transferred right away, but changes in them are only applied > as they reach a certain age. The idea is that if something horrible > happens on the master, you can keep it from propagating to one or more > of your standby databases (or keep from having to reinstate one in the > case of a failover) > > Anyway, Is there any plan to add a knob like that to the streaming > replication in Postgres? > IIRC, Robert Haas had a WIP patch to do that. Not sure what its status is now. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.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] Log Apply Delay
Oracle has a configuration option for its version of hot standby (DataGuard) that lets you specify a time based delay in applying logs. They get transferred right away, but changes in them are only applied as they reach a certain age. The idea is that if something horrible happens on the master, you can keep it from propagating to one or more of your standby databases (or keep from having to reinstate one in the case of a failover) Anyway, Is there any plan to add a knob like that to the streaming replication in Postgres? Hypothetically, if I had a standby database with max_standby_*_delay set to -1, and there had been a long running query so log apply was an hour behind, could I use that database for point in time recovery if something went wrong on the primary? Say something bad happened on primary, and I rushed over to the standby (in this delayed situation) and shut it down. Could I then alter the recovery.conf and have it come up read/write at a point in time? Seems like I could - Ian -- 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 benchmarked on XFS vs ZFS vs btrfs vs ext4
On Wed, Sep 14, 2011 at 11:18 PM, Toby Corkindale wrote: > The zpool was created against an LVM logical volume (which was the same one > used for all the filesystems measured in the tests). That LV was itself part > of a volume group that was striped over three disks (Western Digital > WD1003FBYX). So you're throwing in the complication of a logical volume manager interfering with zfs. If you put ZFS on three separate drives, it has a better chance of optimizing its operations. -- 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] different unnest function
-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Ondrej Ivanic
Sent: Friday, September 16, 2011 12:54 AM
To: pgsql-general@postgresql.org general
Subject: [GENERAL] different unnest function
Hi,
I need function which unnest array in a different way. Input table has
ineger[][] column:
col1
--
{{1,2,3,4}, {5,6,7,8}, {9, 10, 11, 12}}
{{11,12,13,14}, {15,16,17,18}, {19, 110, 111, 112}} ...
and output should be:
select unnest2(col1) from T
unnest2
-
{1,2,3,4}
{5,6,7,8}
{9, 10, 11, 12}
{11,12,13,14}
{15,16,17,18}
{19, 110, 111, 112}
My function is:
create or replace function unnest2(anyarray) returns setof anyarray AS $BODY$
select $1[i:i] from generate_series(array_lower($1,1), array_upper($1,1)) i;
$BODY$ language 'sql';
and the result is:
{{1,2,3,4}}
{{5,6,7,8}}
{{9, 10, 11, 12}}
{{11,12,13,14}}
{{15,16,17,18}}
{{19, 110, 111, 112}}
which is almost what I need...(or I'm at the beginning :)) Any ideas?
Untested by try something like:
SELECT ($1[i:i])[1] FROM generate_series(...) i;
Basically you want to take the first (and only) array element from your slice.
You might need to use sub-queries to get the syntax to work but the idea should
be possible.
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] PostgreSQL benchmarked on XFS vs ZFS vs btrfs vs ext4
On Tue, Sep 13, 2011 at 9:15 PM, Toby Corkindale wrote: > However we have a new contender - ZFS performed *extremely* well on the > latest Ubuntu setup - achieving triple the performance of regular ext4! Did you do any tuning to ZFS? There are many tweaks to it, like putting a cache disk in front of it, or moving the logs to SSD and such. I haven't run any produciton DBs on ZFS yet, but it sure is tempting. The speed penalty for the features it gives you (snapshots, robust against power fails, etc.) is worth the tradeoff. -- 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] Noob help for charting on web site, need assistance
On Wed, Sep 14, 2011 at 5:56 PM, Greg Howard wrote: > Flot, Open Flash Chart, AmCharts, Emprise JavaScript Charts, PlotKit, Flotr, > PHP/SWF Charts, Visifire, FusionCharts, and JFreeChart. > We've used AmCharts with great success. Flaw is that it is flash, and doesn't show up on the ipad. We are moving to Hicharts JS which uses HTML5 to make awesome looking charts that display everywhere. It is, however, much lower-level than AmCharts API so you have to do a lot of JS work yourself to generate the plots. But as Joshua recommends, try using Google not necessarily importing into google docs, but just directly use their chart API. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] What do you like to get ?
>>> On Thu, Sep 15, 2011 at 7:29 AM, Marc Mamin wrote: >>> I miss a discussion place for feature wishes. (Is there one ?) > From: Josh Kupershmidt > Maybe it could look like this: > http://wiki.audacityteam.org/wiki/Feature_Requests Yes this fit very well my conception of such a place. regards, Marc Mamin -- 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] forcing table ownership
On 16/09/11 14:13, Darin Perusich wrote: Altering the table owner by setting it to the group role effectively denies permission to all users of the group. Unless they explicitly "SET role grp1" that is. I've already got a user "richardh" As a superuser: CREATE GROUP mygroup INHERIT; GRANT mygroup TO richardh; CREATE TABLE shared_table (i int); As richardh: ALTER TABLE shared_table ADD COLUMN t text; ERROR: must be owner of relation shared_table As superuser: ALTER TABLE shared_table OWNER TO mygroup; As richardh: ALTER TABLE shared_table ADD COLUMN t text; ALTER TABLE I think the key bit you're missing is the "INHERIT" on the group. Also note that the CREATE USER/CREATE GROUP commands actually just run CREATE ROLE under the hood. -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] What do you like to get ?
On Thu, Sep 15, 2011 at 12:16 PM, Adam Cornett wrote: > This sounds like something that should be in the Postgres wiki > (http://wiki.postgresql.org/wiki) not sure if there is a page (a quick > search didn't turn one up). > On Thu, Sep 15, 2011 at 7:29 AM, Marc Mamin wrote: >> I miss a discussion place for feature wishes. (Is there one ?) Well, there is the Todo list, http://wiki.postgresql.org/wiki/Todo But AFAIK there's not a wiki page for users to add or talk about feature requests; that page says "Please do not add items here without discussion on the mailing list." And often things mentioned on-list fall through the cracks, as people aren't motivated enough to build consensus by asking "Is this a TODO?", then adding to that list. I don't know that a separate mailing list for such topics would be helpful or used, but I think a less-official wiki page that users were free to edit would be helpful. Maybe it could look like this: http://wiki.audacityteam.org/wiki/Feature_Requests One feature I'd be interested in is something like an ON STARTUP trigger, i.e. a way to run some function upon server start. This might be useful in conjunction with unlogged tables, e.g. you might want to queue a refresh of a materialized view after a server crash. There are plenty more, too. If someone wants to start a wiki page, I'll help. Josh -- 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] Alternative JDBC driver
On Tue, 13 Sep 2011 10:18:46 +0200, Jimmy K. wrote: Hello, I asked this question on JDBC list, but it seems "dead". I found alternative JDBC driver marked as Beta 2 (actually last time bumped to Beta 2.1) http://softperience.eu/pages/cmn/ngpgjdbc.xhtml. We still test it. Those features are, mainly, in our interest - binary transfer - possibility of loading large bytea in JVM with small heap (we loaded about 1GB in JVM with 256MB) - XA But, I would like to ask, if someone of You have tested it, and / or compared. We actually looking for general opinion if it's suitable for JavaEE + JPA/Hibernate Regards, J. Hello, The driver I develop is mainly focused for JavaEE + JPA. We and friendly company already, uses it during test and for some "less critical", but live systems (in J2EE 6 environment) (during this tests we had found two problems, one is fixed, and 2nd looks like comes from PostgreSQL, but it will be fixed). If You ask for performance there is available test of original driver with some binary patches (http://blogs.oracle.com/jkshah/entry/postgres_8_4_testing_with). The driver started from our internal interest for making our apps stable (statement timeout), and faster (binary protocol), rest of changes came from propositions on forum (JDBC4 exceptions, "possibility of loading large bytea in JVM with small heap", UDTs). Some of patches was sent for official version, but topics "died". We have, as well, different conception, then is visible in original driver - we want to remove as much processing as possible from database, because app server is clusterizable, and db not. If you have some ideas or if you found bug, You may contact me or submit data through our web side. Last time we think, about creating complete and modern free database system for j2ee platforms. Best regards, Radoslaw Smogura http://softperience.eu -- 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] CUBE, ROLLUP, GROUPING SETS?
Apologies for raising this thread back from the dead - Zombie thread if you like! Very interested in the Rollup and Cube OLAP fuinctions :) Does anyone know where this todo is in current plans. I've searched the archives but the last patch I could find was from 2008... Marc -- View this message in context: http://postgresql.1045698.n5.nabble.com/GENERAL-CUBE-ROLLUP-GROUPING-SETS-tp1868659p4810233.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] Remote connection shows localhost databases
Thanks Guillaume and Richard. I removed the server from the host name field and entered only the IP address. I then edited my pg_hba.conf, adding the IP address and it worked! I appreciate all of your help. Jeff > > Thanks for the response Richard. I have tried to connect via pgAdminIII. > When I try to connect to the remote machine, I enter \\\ > into the host name field. What I found was it didn't matter what I entered > into the hostname field (I put in random strings to test), it always brought > up the databases on the localhost > In pgAdmin, you have a Name field (which is a description, so free text), and you have a Host field. The host field should contain the socket complete path, or the host name, or the ip address. With this, you're able to connect to any PostgreSQL server, as long as their pg_hba.conf file allows it. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.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] forcing table ownership
Hi Richard, > > I'm trying to accomplish is to have multiple users/roles connect to a > > database and have ALL privileges to do whatever they want. The > problem > > I'm running into is that is user1 creates table1 nobody else has > > permissions to it since they are not the table owner. How can I > > accomplish this? > > It looks like you have two choices from what I can find in the manual > after 5 minutes of reading. > 1) alter the tables/schema/... to be owned by a role that all of these > users belong to: > http://www.postgresql.org/docs/8.4/interactive/sql-altertable.html Altering the table owner by setting it to the group role effectively denies permission to all users of the group. Unless they explicitly "SET role grp1" that is. > 2) grant the table/schema to the role: > http://www.postgresql.org/docs/8.4/interactive/privileges.html "GRANT ALL ON table TO grp1;" has the effect as mentioned above. I'm finding it hard to believe this is so difficult... -- Darin Perusich Email: darin.perus...@ctg.com Office: 716-888-3690 The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you are not the intended recipient of this message, please contact the sender and delete this material from this computer. -- 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] multi-master replication (Was: Has Pg 9.1.0 been released today?)
2011/9/16 Grzegorz Jaśkiewicz : > Is anyone actually working on Postgres-R ? Last git commit was in January > 2011. > What are the chances of it getting integrated with the core, which it > is probably targeted for ? > > If I picked it up, and tried to make usable for my own needs - instead > of implementing trigger/log (slony like) multi master async on my own > (other opensource ones are no use to me, or didn't perform well during > tests), > how much work is there to be done ? > > Lastly, is PostgresR async or sync MM replication ? According to wikipedia, postgres-r is synchronous. FWIW, I'm pretty skeptical that any MM replication solution will get adopted in core unless it is developed in concert with the community. Most MM products tend to be niche solutions with crippling downsides that make them not suitable for general use. Asynchronous MM rep in particular is totally incompatible with SQL since it breaks transaction guarantees. Postgresql replication is currently very much 'one way', and even if you could somehow work it so that multiple backends could server data modifying queries, I really wonder what the technical benefit of doing that would be -- what's wrong with putting a pgbouncer-ish front end(s) between the application and the server that can promote synchronously masters and redirect traffic? Extra intelligence could be put into that layer that could interleave certain types of queries for parallel execution The ndb approach to clustering really complex and not necessarily a good model to emulate IMO. The biggest weaknesses around postgresql replication are dealing with syncing standbys to a new master after promotion -- solve that problem well and after getting sync rep latency down as much as reasonably possible, and you could see some amazing stuff. 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] forcing table ownership
> -Original Message- > From: Andy Colson [mailto:a...@squeakycode.net] > Sent: Thursday, September 15, 2011 7:51 PM > To: Darin Perusich > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] forcing table ownership > > On 09/15/2011 04:31 PM, Darin Perusich wrote: > > Hi Andy, > > > >> > >> Any way you can update to PG 9? > >> > >> http://www.postgresql.org/docs/9.0/static/sql- > >> alterdefaultprivileges.html > > > > That's not really an option I'd like to consider. I really don't want > to > > deploy anything outside of what's distributed with the OS, since I > have > > a TON of opensuse servers to keep track of and there will be a bunch > > running postgres. > > > > Thanks! > > How about "set role ..." then? > > http://www.postgresql.org/docs/8.4/static/sql-set-role.html > This works but it seems rather cumbersome to have to set the role every time someone connects to the database. There has to be another way. The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you are not the intended recipient of this message, please contact the sender and delete this material from this computer. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Log message " last_statrequest ... is later than collector's time" - what does it mean?
Hi, I upgraded our test database from PostgreSQL 8.4.8 to 9.0.4 via pg_dumpall. The database seems to work fine, but now the logfile of the new database is flooded with log messages like these: 2011-09-16 13:48:32 CEST: LOG: database system was shut down at 2011-09-16 13:48:29 CEST 2011-09-16 13:48:32 CEST: LOG: autovacuum launcher started 2011-09-16 13:48:32 CEST: LOG: database system is ready to accept connections 2011-09-16 13:48:54 CEST: LOG: last_statrequest 2011-09-16 13:48:55.890743+02 is later than collector's time 2011-09-16 13:48:54.614476+02 2011-09-16 13:48:54 CEST: LOG: last_statrequest 2011-09-16 13:48:55.890743+02 is later than collector's time 2011-09-16 13:48:54.626524+02 2011-09-16 13:48:54 CEST: LOG: last_statrequest 2011-09-16 13:48:55.890743+02 is later than collector's time 2011-09-16 13:48:54.638498+02 2011-09-16 13:48:54 CEST: LOG: last_statrequest 2011-09-16 13:48:55.890743+02 is later than collector's time 2011-09-16 13:48:54.650502+02 2011-09-16 13:48:54 CEST: LOG: last_statrequest 2011-09-16 13:48:55.890743+02 is later than collector's time 2011-09-16 13:48:54.662507+02 2011-09-16 13:48:54 CEST: LOG: last_statrequest 2011-09-16 13:48:55.890743+02 is later than collector's time 2011-09-16 13:48:54.674506+02 2011-09-16 13:48:54 CEST: LOG: last_statrequest 2011-09-16 13:48:55.890743+02 is later than collector's time 2011-09-16 13:48:54.686507+02 2011-09-16 13:48:54 CEST: LOG: last_statrequest 2011-09-16 13:48:55.890743+02 is later than collector's time 2011-09-16 13:48:54.698498+02 2011-09-16 13:48:54 CEST: LOG: last_statrequest 2011-09-16 13:48:55.890743+02 is later than collector's time 2011-09-16 13:48:54.710517+02 2011-09-16 13:48:54 CEST: LOG: last_statrequest 2011-09-16 13:48:55.890743+02 is later than collector's time 2011-09-16 13:48:54.722504+02 2011-09-16 13:48:54 CEST: LOG: last_statrequest 2011-09-16 13:48:55.890743+02 is later than collector's time 2011-09-16 13:48:54.734495+02 2011-09-16 13:48:54 CEST: LOG: last_statrequest 2011-09-16 13:48:55.890743+02 is later than collector's time 2011-09-16 13:48:54.746498+02 2011-09-16 13:48:54 CEST: LOG: last_statrequest 2011-09-16 13:48:55.890743+02 is later than collector's time 2011-09-16 13:48:54.758503+02 2011-09-16 13:48:54 CEST: LOG: last_statrequest 2011-09-16 13:48:55.890743+02 is later than collector's time 2011-09-16 13:48:54.770501+02 2011-09-16 13:48:54 CEST: LOG: last_statrequest 2011-09-16 13:48:55.890743+02 is later than collector's time 2011-09-16 13:48:54.782509+02 2011-09-16 13:48:54 CEST: LOG: last_statrequest 2011-09-16 13:48:55.890743+02 is later than collector's time 2011-09-16 13:48:54.794496+02 2011-09-16 13:49:07 CEST: LOG: last_statrequest 2011-09-16 13:49:08.176573+02 is later than collector's time 2011-09-16 13:49:07.500691+02 2011-09-16 13:49:07 CEST: LOG: last_statrequest 2011-09-16 13:49:08.176573+02 is later than collector's time 2011-09-16 13:49:07.51259+02 2011-09-16 13:49:07 CEST: LOG: last_statrequest 2011-09-16 13:49:08.176573+02 is later than collector's time 2011-09-16 13:49:07.524583+02 2011-09-16 13:49:07 CEST: LOG: last_statrequest 2011-09-16 13:49:08.176573+02 is later than collector's time 2011-09-16 13:49:07.536605+02 2011-09-16 13:49:07 CEST: LOG: last_statrequest 2011-09-16 13:49:08.176573+02 is later than collector's time 2011-09-16 13:49:07.548601+02 2011-09-16 13:49:07 CEST: LOG: last_statrequest 2011-09-16 13:49:08.176573+02 is later than collector's time 2011-09-16 13:49:07.560569+02 2011-09-16 13:49:07 CEST: LOG: last_statrequest 2011-09-16 13:49:08.176573+02 is later than collector's time 2011-09-16 13:49:07.572571+02 2011-09-16 13:49:07 CEST: LOG: last_statrequest 2011-09-16 13:49:08.176573+02 is later than collector's time 2011-09-16 13:49:07.584579+02 Configuration: - SUSE Linux Enterprise Server 10 (i586) - uname -a: Linux 2.6.16.21-0.8-bigsmp #1 SMP Mon Jul 3 18:25:39 UTC 2006 i686 athlon i386 GNU/Linux - PostgreSQL 9.0.4, installed with RPM: postgresql-9.0.4-54.5 What do these messages mean and what can I do to avoid them? Cheers, Stephan
Re: [GENERAL] Remote connection shows localhost databases
On 15/09/11 22:40, Guillaume Lelarge wrote: On Thu, 2011-09-15 at 15:30 -0400, Jeff Adams wrote: When I try to connect to the remote machine, I enter \\\ into the host name field. The host field should contain the socket complete path, or the host name, or the ip address. As Guillaume says - try just the IP address to start with. What you've been trying is sort-of a Windows networking path. Odd that pgAdmin doesn't give an error though. If you were using psql you'd type something like: psql -h -U -d Once you're happy the ip-address is working, try just the server-name by itself. You'll want the internet name for the machine which in theory can be different from the Windows network name, but usually is the same. -- Richard Huxton Archonet Ltd -- 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] Upgrading from 9.0->9.1 Ubuntu Best Practices
On 16/09/11 12:43, adebarros wrote: Hi, all, first post on this list. I'm running Ubuntu 11.04 and have Postgres 9.0 and 8.4 both installed, although I'm only working with 9.0 at this time for some Django development. I have added this PPA to my Upgrade Manager: http://ppa.launchpad.net/pitti/postgresql/ubuntu The other day I noticed the Upgrade Manager fetched a few 9.1-related files that were quite small, certainly not the entire 9.1 update. What is the recommended way for me to: -- Install 9.1 -- Move my databases to 9.1 from 9.0 -- Remove 9.0 and 8.4 from my system Thanks! Since you're on Ubuntu, there's a fairly easy way. * Install all the 9.1 stuff - server, client, etc. * Run pg_upgradecluster * Verify it's all working OK on the new DB. * Uninstall the 9.0 stuff Note that the 9.1 server will get installed on a higher port number so it doesn't conflict with the existing server package.. You'll want to edit postgresql.conf and change it back to normal once you're done. -- 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 the 9.1 one-click installer Windows7 64bit
Craig Ringer, 16.09.2011 05:02: On 15/09/2011 4:18 PM, Thomas Kellerer wrote: I ran another install and monitored what the process was doing and it *is* recursively touching all files on my harddisk when icacls C:\ /grant "tkellerer":RX is called. Even without the /t switch. That's a worry. It'd be interesting to generate a Process Monitor trace for this, as it'll show exactly what iacls is doing. Just having a handle open to some random file is weird, but it'd be good to know if it's messing with permissions too. I have created such a trace file which is quite large, so I removed a lot of repetitions where each and every file that was processed was shown. I hope it still contains enough information for you to find out what is going on. I have uploaded the file here: http://www.sql-workbench.net/icacls_trace.zip As this works correctly on XP I'm wondering if there is a substantial difference between cacls.exe (which is used on XP by the installer script) and the new icacls.exe I wonder if it's to do with ACL inheritance? Maybe one of the inheritance control flags like "NP" is needed. I don't know enough about NT permissions to say, but inheritance would be my suspect. No, idea either. I am by no means a Windows expert. Regards Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to convert ByteA to Large Objects
> > Caused by: org.postgresql.util.PSQLException: ERROR: column "docdta" is of > type bytea but expression is of type oid > Hint: You will need to rewrite or cast the expression." > Looks like that table has a column of type BYTEA, while the code expects it to be a LOB. Perhaps the easiest fix is to change the table to store a LOB instead? One of the benefits of doing that is that you can "stream" LOB's (you access it like a file; opening, reading, seeking), whereas AFAIK you have to retrieve BYTEA data as a whole before you can start sending it to the client.
Re: [GENERAL] PostgreSQL 9.1.0 bug?
In article <21641.1316159...@sss.pgh.pa.us>, Tom Lane writes: > Harald Fuchs writes: >> I think I've found a bug either in PostgreSQL 9.1.0 or in the ip4r >> package (version 1.05). > Good catch --- gistendscan is forgetting to free so->giststate. Confirmed - adding a "pfree(so->giststate)" solves my problem. Thanks for the quick fix! -- 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 the 9.1 one-click installer Windows7 64bit
On 15/09/2011 4:18 PM, Thomas Kellerer wrote: I ran another install and monitored what the process was doing and it *is* recursively touching all files on my harddisk when icacls C:\ /grant "tkellerer":RX is called. Even without the /t switch. That's a worry. I verified this using ProcessExplorer displaying the open file handles for the running icacls.exe It'd be interesting to generate a Process Monitor trace for this, as it'll show exactly what iacls is doing. Just having a handle open to some random file is weird, but it'd be good to know if it's messing with permissions too. I wonder if it's to do with ACL inheritance? Maybe one of the inheritance control flags like "NP" is needed. I don't know enough about NT permissions to say, but inheritance would be my suspect. -- 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
[GENERAL] different unnest function
Hi,
I need function which unnest array in a different way. Input table has
ineger[][] column:
col1
--
{{1,2,3,4}, {5,6,7,8}, {9, 10, 11, 12}}
{{11,12,13,14}, {15,16,17,18}, {19, 110, 111, 112}}
...
and output should be:
select unnest2(col1) from T
unnest2
-
{1,2,3,4}
{5,6,7,8}
{9, 10, 11, 12}
{11,12,13,14}
{15,16,17,18}
{19, 110, 111, 112}
My function is:
create or replace function unnest2(anyarray) returns setof anyarray AS
$BODY$
select $1[i:i] from generate_series(array_lower($1,1), array_upper($1,1)) i;
$BODY$
language 'sql';
and the result is:
{{1,2,3,4}}
{{5,6,7,8}}
{{9, 10, 11, 12}}
{{11,12,13,14}}
{{15,16,17,18}}
{{19, 110, 111, 112}}
which is almost what I need...(or I'm at the beginning :)) Any ideas?
Thanks,
--
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
Re: [GENERAL] How to convert ByteA to Large Objects
> > Thank you. We are working on an Oracle to PostgreSQL migration project. > > BLOB columns got converted to BYTEA in PostgreSQL and we ran into problems. > > We used this to convert the data type to OID. Thank you. > > you probably should detail the problems you ran into. large objects > normally aren't used unless you need to store over 1GB objects in the > database, and at least speaking for myself, I'd rather not have objects > that large in my database at all, I'd as soon use files for things that big. > Well, we are storing scanned images of boarding passes. We are using Hibernate and the insert statement generated threw this error. "Caused by: org.hibernate.exception.SQLGrammarException: could not insert: at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43) at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2202) at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2595) at org.hibernate.action.EntityInsertAction.execute(EntityInsertAction.java:51) at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:248) at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:232) at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:139) at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298) at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27) at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000) at com.ibsplc.xibase.server.framework.persistence.hibernate.HibernateJTATxWrapper.commit(HibernateJTATxWrapper.java:93) ... 51 more Caused by: org.postgresql.util.PSQLException: ERROR: column "docdta" is of type bytea but expression is of type oid Hint: You will need to rewrite or cast the expression." If we try rewriting, that would mean code changes in a few other places. We changed the data type and now 'some' data has been inserted. Once we fix the retrieval screen, we will know it is getting processed correctly. select data from pg_largeobject where loid= gave us a couple of hundred records, so we assume data has been inserted. Switching to file storage will mean quite a bit of changes at the code level, and a lot of testing of the products that runs fine on Oracle now. 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."
Re: [GENERAL] multi-master replication (Was: Has Pg 9.1.0 been released today?)
Is anyone actually working on Postgres-R ? Last git commit was in January 2011. What are the chances of it getting integrated with the core, which it is probably targeted for ? If I picked it up, and tried to make usable for my own needs - instead of implementing trigger/log (slony like) multi master async on my own (other opensource ones are no use to me, or didn't perform well during tests), how much work is there to be done ? Lastly, is PostgresR async or sync MM replication ? -- 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] Remote connection shows localhost databases
On 16/09/11 09:01, Guillaume Lelarge wrote: On Fri, 2011-09-16 at 08:14 +0100, Richard Huxton wrote: Odd that pgAdmin doesn't give an error though. Probably because the OP entered the Windows networking path in the Name field, and didn't change the Host field. In which case, pgAdmin most likely try to connect locally. Ah - I can see how that would make sense. -- Richard Huxton Archonet Ltd -- 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] Remote connection shows localhost databases
On Fri, 2011-09-16 at 08:14 +0100, Richard Huxton wrote: > On 15/09/11 22:40, Guillaume Lelarge wrote: > > On Thu, 2011-09-15 at 15:30 -0400, Jeff Adams wrote: > >> > >> When I try to connect to the remote machine, I enter > >> \\\ > >> into the host name field. > > > >The host field should contain the > > socket complete path, or the host name, or the ip address. > > As Guillaume says - try just the IP address to start with. What you've > been trying is sort-of a Windows networking path. Odd that pgAdmin > doesn't give an error though. > Probably because the OP entered the Windows networking path in the Name field, and didn't change the Host field. In which case, pgAdmin most likely try to connect locally. > If you were using psql you'd type something like: >psql -h -U -d > > Once you're happy the ip-address is working, try just the server-name by > itself. You'll want the internet name for the machine which in theory > can be different from the Windows network name, but usually is the same. > -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.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 9.1.0 bug?
Harald Fuchs writes: > I think I've found a bug either in PostgreSQL 9.1.0 or in the ip4r > package (version 1.05). Good catch --- gistendscan is forgetting to free so->giststate. But it seems just as broken in 9.0 and probably before ... 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
