Re: [GENERAL] Off Topic: Anybody reading this via news.gmane.org?
No problem reading (and hopefully posting) via Gmane here. -- Ian Pilcher arequip...@gmail.com "I grew up before Mark Zuckerberg invented friendship" -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Off Topic: Anybody reading this via news.gmane.org?
Just wondering if anyone else reads this list via gmane and also is having problems? I follow several lists via gmane and a few days ago all my posts started being rejected - no authorization email, just an immediate "you are not allowed to post" error. Then 2 days ago, I also lost read access to all the lists - attempts get "480 Read access denied". I can ping the NNTP server but I can't connect to it. I have sent a couple of emails to gmane's adminstrator but so far I haven't gotten any response. Gmane is an open service that doesn't require accounts, so I am uncertain how I suddenly could be denied reading privilege ... even to post it only requires that you be subscribed to the particular list. I can't think of any reason my address would be black-listed ... unless, of course, all of comcast.net has been black-listed. Thanks, George -- 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] Synchronous replication + pgPool: not all transactions immediately visible on standby
>> PostgreSQL's "synchronous" replication is actually not >> synchronous > > Well, that statement is a bit misleading. What is synchronous with > the COMMIT request is that data is persisted on at least two > targets before the COMMIT request returns an indication of success. > It guarantees that much (which some people complain about because > if there is only one synchronous replication target the commit > request hangs indefinitely if it, or communications to it, goes > down) and no more (because some people expect that it is not just > about durability, but also about visibility). There have been many > discussions about allowing configuration of broader or less strict > guarantees, but for now, you have just the one option. > >> (it's confusing but the naming was developer's decision). > > There was much discussion at the time, and this was the consensus > for an initial implementation. I know what PostgreSQL's synchronous replication does. But, as you saw, still many users expect "synchronous replication" will do "visibility synchronous". I'm a little bit tired of making this kind of explanation to users but that's not users fault, I think. Maybe "crash safe replication" or some such was more appropriate term, but of course this is just a hindsight. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp -- 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] Re: Synchronous replication + pgPool: not all transactions immediately visible on standby
>> If you dislike the PostgreSQL's behavior, you may want to try >> pgpool-II's "native replication mode" (set replication_mode = on and >> master_slave_mode = off). In the mode, pgpool-II does not return >> response to the client until all PostgreSQL returns a commit >> response. Thus right after the commit, querying to any PostgreSQL >> should return committed row immediately. > > OK, we will try that out. > > The documentation could a bit more specific on what exactly "replication > mode" means. > It seems that this only influences the way pgPool distributes queries, it > does not actually turn on any kind of replication, right? With replication mode pgpool-II implicitely sends all write queries to all PostgreSQL servers. The idea is, "sending identical query will result in same result". Thus some queries having oid, xid, random() or any object which results in different result among PostgreSQL servers will bring different data in replication mode. So you should be very carefull if you want to use such queries. Queries including time/data datatypes, functions (for example now()) are rewritten by pgpool-II so that it uses local time at pgpool-II to avoid the problem. With the mode, PostgreSQL's streaming replication mode should be turn off by the way. >>From the flow chart[1] it also seems that this will only properly distribute >>read-only queries if we turn off auto-commit. > Did I understand that correctly? Yes, the chart only explains read queries. Write queries are sent to all PostgreSQL servers as stated above. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp -- 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] Synchronous replication + pgPool: not all transactions immediately visible on standby
Tatsuo Ishii wrote: > I think your problem is not relevant to pgpool-II. Agreed. > PostgreSQL's "synchronous" replication is actually not > synchronous Well, that statement is a bit misleading. What is synchronous with the COMMIT request is that data is persisted on at least two targets before the COMMIT request returns an indication of success. It guarantees that much (which some people complain about because if there is only one synchronous replication target the commit request hangs indefinitely if it, or communications to it, goes down) and no more (because some people expect that it is not just about durability, but also about visibility). There have been many discussions about allowing configuration of broader or less strict guarantees, but for now, you have just the one option. > (it's confusing but the naming was developer's decision). There was much discussion at the time, and this was the consensus for an initial implementation. > Primary server sends the committed transaction's WAL record to > standby and wait for it is written to the standby's WAL file (and > synched to the disk if synchronous_commit = on). Then report to > the client "the transaction has been committed". That means if > you send query on the just committed row to the standby, it may > returns an old row because WAL record may replay yet. Right. -- Kevin Grittner EDB: 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
[GENERAL] Re: Synchronous replication + pgPool: not all transactions immediately visible on standby
> I think your problem is not relevant to pgpool-II. > > PostgreSQL's "synchronous" replication is actually not synchronous > (it's confusing but the naming was developer's decision). Primary > server sends the committed transaction's WAL record to standby and > wait for it is written to the standby's WAL file (and synched to the > disk if synchronous_commit = on). Then report to the client "the > transaction has been committed". That means if you send query on the > just committed row to the standby, it may returns an old row because > WAL record may replay yet. Thanks for the insight. I wasn't aware of that. I assumed that if the slave said "transaction applied" this would also be visible "on the SQL level". > If you dislike the PostgreSQL's behavior, you may want to try > pgpool-II's "native replication mode" (set replication_mode = on and > master_slave_mode = off). In the mode, pgpool-II does not return > response to the client until all PostgreSQL returns a commit > response. Thus right after the commit, querying to any PostgreSQL > should return committed row immediately. OK, we will try that out. The documentation could a bit more specific on what exactly "replication mode" means. It seems that this only influences the way pgPool distributes queries, it does not actually turn on any kind of replication, right? >From the flow chart[1] it also seems that this will only properly distribute >read-only queries if we turn off auto-commit. Did I understand that correctly? > Postgres-XC (or Postgres-XL which is a fork of Postgres-XC) overcomes > the issue by using "global transaction management" technique. I know of those two options, but those are currently not on our roadmap (although I'd really like to play around with them at some time). Regards Thomas [1] http://www.pgpool.net/docs/latest/where_to_send_queries.pdf -- 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] deadlock of lock-waits (on transaction and on tuple) using same update statement
Andrej Vanek wrote: > Hi, > > > now I've checked release-notes of 9.3.5 (my version 9.3.4)- found a fix > which probably could lead to my deadlocks: > > > Fix race condition when updating a tuple concurrently locked by another > > process (Andres Freund,Álvaro Herrera) > > How can I make sure I've run into this bug? Update and see if you can reproduce the bug there. If you can, let us know; I didn't trace through your example and won't unless you confirm it's still an issue. It's a bad idea to run any 9.3 version other than the latest one. There are some ugly bugs in there. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- 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] Installing Postgresql on Linux Friendlyarm
On 09/24/2014 11:34 PM, Xiang Gan wrote: Finally, I managed to run Postgresql in Linux FriendlyARM environment. It seems that Postgresql server starts to work, however, it prints out some WARNING info. during the start. The error info. is as follows: LOG: could not resolve "localhost": Temporary failure in name resolution LOG: disabling statistics collector for lack of working socket WARNING: autovacuum not started because of misconfiguration HINT: Enable the "track_counts" option. So the above log indicates that autovacuum failed to start mainly due to the problem that "localhost" cannot be resolved as 127.0.0.1? But I can ping localhost from command line without any problem. My /etc/hosts file reads as: 127.0.0.1 localhost.localdomain localhost In addition, options "autovacuum" and "track_counts" are turned on by default in postgresql.conf file. But in database, with command "show autovacuum", its value is shown as "on" while with command "show track_counts", its value is shown as "off". There is a cascading effect. The statistics collector needs a working socket and autovacuum needs a working statistics collector, in particular track_counts. You fix the socket/networking issues the other issues should resolve. Could anyone provide some hints about how to handle this kind of problem? Well this going to depend greatly on what you did to get to this phrase 'I managed to run Postgresql in Linux FriendlyARM environment.'. So a detailed explanation on how you got Postgres to sort of start would be in order. Kind regards, Gerry -- Adrian Klaver adrian.kla...@aklaver.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 clone CURRENT_DATE to SYSDATE ?
On 09/25/2014 03:24 AM, Emanuel Araújo wrote: Thank's Adrian, I want really create another CURRENT_DATE called SYSDATE. postgres=# SELECT CURRENT_DATE ; date 2014-09-25 (1 row) I need that: postgres=# SELECT SYSDATE ; date 2014-09-25 Because, I am trying SymmetricDS between Oracle and PostgreSQL, in my case, there are a lot of fields with "DEFAULT trunc(sysdate)". This situation break when I start the sincronization why the data type there isn't in PostgreSQL. Best guess is the answer lies here: http://www.symmetricds.org/doc/3.6/user-guide/html/config.html#configuration-transforms -- Adrian Klaver adrian.kla...@aklaver.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] Synchronous replication + pgPool: not all transactions immediately visible on standby
On Thu, Sep 25, 2014 at 8:40 AM, Tatsuo Ishii wrote: > Postgres-XC (or Postgres-XL which is a fork of Postgres-XC) overcomes > the issue by using "global transaction management" technique. ... At the cost of reducing data availability for sharded tables, and increasing write load for replicated tables, both things not that cool for data warehouse applications, better for OLTP loads. Regards, -- Michael -- 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 clone CURRENT_DATE to SYSDATE ?
Hi 2014-09-25 12:24 GMT+02:00 Emanuel Araújo : > Thank's Adrian, > > I want really create another CURRENT_DATE called SYSDATE. > It needs a hack to postgres. Pseudoconstant functions needs a support in PostgreSQL parser. There is no other possibility Pavel > > postgres=# SELECT CURRENT_DATE ; > date > > 2014-09-25 > (1 row) > > I need that: > > postgres=# SELECT SYSDATE ; > date > > 2014-09-25 > > Because, I am trying SymmetricDS between Oracle and PostgreSQL, in my > case, there are a lot of fields with "DEFAULT trunc(sysdate)". This > situation break when I start the sincronization why the data type there > isn't in PostgreSQL. > > > > 2014-09-24 16:43 GMT-03:00 Adrian Klaver : > >> On 09/24/2014 07:39 AM, Emanuel Araújo wrote: >> >>> Hi, >>> >>> I need to clone function CURRENT_DATE to SYSDATE in my PostgreSQL. >>> >>> Does anybody know how to do that it ? >>> >> >> Not sure what you want? >> >> A clone is an exact replica so cloning CURRENT_DATE would create another >> CURRENT_DATE. My guess is that this not what you want. >> >> So do you want to create SYSDATE in Postgres? >> >> If so, look at this thread for the issues: >> >> http://www.postgresql.org/message-id/1409288790481- >> 5816851.p...@n5.nabble.com >> >> >>> >>> -- >>> *Atenciosamente, >>> >>> Emanuel Araújo* >>> */Linux Certified, DBA PostgreSQL >>> /* >>> >> >> >> -- >> Adrian Klaver >> adrian.kla...@aklaver.com >> > > > > -- > > > *Atenciosamente,Emanuel Araújo* > > *Linux Certified, DBA PostgreSQL* >
Re: [GENERAL] How to clone CURRENT_DATE to SYSDATE ?
Thank's Adrian, I want really create another CURRENT_DATE called SYSDATE. postgres=# SELECT CURRENT_DATE ; date 2014-09-25 (1 row) I need that: postgres=# SELECT SYSDATE ; date 2014-09-25 Because, I am trying SymmetricDS between Oracle and PostgreSQL, in my case, there are a lot of fields with "DEFAULT trunc(sysdate)". This situation break when I start the sincronization why the data type there isn't in PostgreSQL. 2014-09-24 16:43 GMT-03:00 Adrian Klaver : > On 09/24/2014 07:39 AM, Emanuel Araújo wrote: > >> Hi, >> >> I need to clone function CURRENT_DATE to SYSDATE in my PostgreSQL. >> >> Does anybody know how to do that it ? >> > > Not sure what you want? > > A clone is an exact replica so cloning CURRENT_DATE would create another > CURRENT_DATE. My guess is that this not what you want. > > So do you want to create SYSDATE in Postgres? > > If so, look at this thread for the issues: > > http://www.postgresql.org/message-id/1409288790481- > 5816851.p...@n5.nabble.com > > >> >> -- >> *Atenciosamente, >> >> Emanuel Araújo* >> */Linux Certified, DBA PostgreSQL >> /* >> > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > -- *Atenciosamente,Emanuel Araújo* *Linux Certified, DBA PostgreSQL*
Re: [GENERAL] deadlock of lock-waits (on transaction and on tuple) using same update statement
Hi, now I've checked release-notes of 9.3.5 (my version 9.3.4)- found a fix which probably could lead to my deadlocks: > Fix race condition when updating a tuple concurrently locked by another process (Andres Freund,Álvaro Herrera) How can I make sure I've run into this bug?
Re: [GENERAL] deadlock of lock-waits (on transaction and on tuple) using same update statement
Hi Bill, thanks for your answer. > most often caused by something earlier in the transactions > need all of the statements in each transaction It would be great if we could reveal an application error. Whole transactions I've already posted (in postgres log: log_min_duration_statement=0). Nothing suspicious yet: - both sessions COMMIT/ROLLBACK before BEGIN - both sessions run the same SQL statements - deadlock: FIRST statement of both transactions - deadlock: update single table, same row, column without any constraints (WHY?) - no statements of other sessions executed in between Any idea? thanks, Andrej
