Re: [pgsql-advocacy] [HACKERS] How can we expand PostgreSQL ecosystem?
erprise >> Consortium (PGECons) for the first time and proposed starting the >> following activity. PGECons is a Japanese non-profit organization to >> promote PostgreSQL for enterprise use. The members include NTT, SRA OSS >> (Tatsuo Ishii runs), NEC, Hitachi, HP, Fujitsu, etc. We concluded that >> we need to consult the PostgreSQL community on how to proceed the >> activity and work in cooperation with the community. >> * Attract and ask product/service vendors to support/use PostgreSQL. >> Provide technical assistance to those vendors as an organization so that >> they can support PostgreSQL smoothly. >> If the vendors aren't proactive, we verify the interoperability with >> their software by executing it. >> * Make a directory of software/services that can be used with PostgreSQL >> on the community web site (wiki.postgresql.org or www.postgresql.org). >> Software/services vendors and PostgreSQL developers/users can edit this >> directory. >> This list not only has the names of software and its vendors, but also >> other information such as the level of interoperability (certified by >> the vendor, or verified by the community/users) and remarks about >> configuration, tuning, and whatever useful for users. >> PostgreSQL Enterprise Consortium (PGECons) >> https://www.pgecons.org/en >> CONSULTATION AND DISCUSSION >> == >> I'd like to discuss and hear opinions on how can we expand the ecosystem >> of PostgreSQL. Example points are: >> * How/Where can we get the knowledge of expanding the software >> ecosystem? Is there any OSS project that we can learn from? >> How can we attract software vendors to support PostgreSQL? What words >> are convincing to appeal the increasing potential of PostgreSQL as a >> good replacement for commercial databases? >> * How can we gain momentum for the activity? >> Can we involve globally influential companies like Dell, HPE, and Red Hat? >> * Do we need some kind of assistance from a foundation or establish a >> foundation? >> There should be many, many software to address, so the ecosystem >> activity has to be long-lasting. Plus, money and expertise is necessary. >> Would we benefit if we join the Linux Foundation Collaborative Projects? >> Linux Foundation Collaborative Projects >> http://collabprojects.linuxfoundation.org/ >> * Which software/services in what category should we address preferentially? >> What software would many users desire to be interoperable when migrating >> from commercial databases? >> What is the effective way to absorb user requests for this? Is it >> enough to make a questionnaire like the following? What is the popular >> questionnaire site which can catch many users (SurveyMonkey?) >> https://postgresql.uservoice.com/forums/21853-general >> Regards >> MauMau > > -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] ON_ERROR_ROLLBACK
On 12/29/2014 09:55 AM, Tom Lane wrote: Adrian Klaver adrian.kla...@aklaver.com writes: So it seems you can turn ON_ERROR_ROLLBACK on with either 1 or 'on', but you can only turn it off with 'off'. With ON_ERROR_STOP 1/on and 0/off both seem to work. Is this expected? Given the lack of previous complaints, this probably isn't backpatching material, but it sure seems like a bit of attention to consistency would be warranted here. I would appreciate it, thanks. regards, tom lane -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: [GENERAL] issue in postgresql 9.1.3 in using arrow key in Solaris platform
On 11/26/2014 02:16 AM, M Tarkeshwar Rao wrote: Hi all, We are facing following issue in postgresql 9.1.3 in using arrow key in Solaris platform. *Can you please help us to resolve it or any new release has fix for this or any workaround for this?* Would seem to me to be an interaction between Postgres and readline. Not sure exactly what, but some information would be helpful for those that might know: 1) What version of Solaris? 2) How was Postgres installed and from what source? 3) What version of readline is installed? 4) Are you using a psql client that is the same version as the server? issue: psql client generates a core when up arrow is used twice. Regards Tarkeshwar -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] [SQL] pg_multixact issues
On 09/18/2014 10:22 AM, Dev Kumkar wrote:
On Thu, Sep 18, 2014 at 6:20 PM, Dev Kumkar devdas.kum...@gmail.com
mailto:devdas.kum...@gmail.com wrote:
On Thu, Sep 18, 2014 at 4:03 PM, Andres Freund
and...@2ndquadrant.com mailto:and...@2ndquadrant.com wrote:
I don't think that's relevant for you.
Did you upgrade the database using pg_upgrade?
That's correct! No, there is no upgrade here.
The above sentence is not clear to me.
Did you run pg_upgrade to get the data into the database?
If not, how did the database get populated?
Can you show pg_controldata output and the output of 'SELECT oid,
datname, relfrozenxid, age(relfrozenxid), relminmxid FROM
pg_database;'?
Here are the details:
oid datname datfrozenxidage(datfrozenxid)datminmxid
16384 myDB1673 10872259 1
Additionally wanted to mention couple more points here:
When I try to run vacuum full on this machine then facing
following issue:
INFO: vacuuming myDB.mytable
ERROR: MultiXactId 3622035 has not been created yet --
apparent wraparound
No Select statements are working on this table, is the table corrupt?
Any inputs/hints/tips here?
Have you run the query from here?:
http://www.postgresql.org/docs/9.3/interactive/release-9-3-5.html
WITH list(file) AS (SELECT * FROM pg_ls_dir('pg_multixact/offsets'))
SELECT EXISTS (SELECT * FROM list WHERE file = '') AND
NOT EXISTS (SELECT * FROM list WHERE file = '0001') AND
NOT EXISTS (SELECT * FROM list WHERE file = '') AND
EXISTS (SELECT * FROM list WHERE file != '')
AS file__removal_required;
--
Adrian Klaver
adrian.kla...@aklaver.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] unable to build postgres-9.4 in os x 10.9 with python
On 05/30/2014 11:52 AM, Tom Lane wrote: Asif Naeem anaeem...@gmail.com writes: On Fri, May 30, 2014 at 9:49 PM, reiner peterke zedaa...@drizzle.com wrote: Since upgrading my mac from os x 10.8 to 10.9, i can no long build postgres with '--with-python’. Latest PG 9.4 source code seems building fine with --with-python option on my OS X 10.9.3 box i.e. [ please don't top-post in PG mailing lists ] I can reproduce the failure as described, not only in 9.4 but the back branches too; I would've noticed sooner except I don't build plpython routinely. It looks to me like Apple broke something in the build toolchain. If you add -v as suggested, what you see is If you do the ld call by hand without the -syslibroot option, it works. AFAICS it could never have worked with such an option, so I'm thinking this is some new misbehavior in the latest version of Xcode. There is and the SO thread that goes into detail on this is here: http://stackoverflow.com/questions/19555395/python-framework-is-missing-from-os-x-10-9-sdk-why-also-workaround The Apple document referenced in above is: https://developer.apple.com/library/ios/technotes/tn2328/_index.html regards, tom lane -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] unable to build postgres-9.4 in os x 10.9 with python
On 05/30/2014 01:31 PM, Tom Lane wrote: Adrian Klaver adrian.kla...@aklaver.com writes: On 05/30/2014 11:52 AM, Tom Lane wrote: If you do the ld call by hand without the -syslibroot option, it works. AFAICS it could never have worked with such an option, so I'm thinking this is some new misbehavior in the latest version of Xcode. There is and the SO thread that goes into detail on this is here: http://stackoverflow.com/questions/19555395/python-framework-is-missing-from-os-x-10-9-sdk-why-also-workaround The Apple document referenced in above is: https://developer.apple.com/library/ios/technotes/tn2328/_index.html Fun. So after all these years of catering to Apple's preferred weirdness in this regard, they reverse course and tell us to do it like everywhere else. Mavericks, the gift that keeps giving:) -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] jsonb and nested hstore
On 02/25/2014 10:54 AM, Josh Berkus wrote: On 02/25/2014 10:50 AM, Robert Haas wrote: On Tue, Feb 25, 2014 at 1:45 PM, Josh Berkus j...@agliodbs.com wrote: On 02/25/2014 10:31 AM, Robert Haas wrote: And I definitely don't agree that our documentation should push people towards stuffing everything in a JSON blob instead of using real column definitions. Where did you get this out of my doc patch? Way to quote what I said out of context. Way to put words in my mouth. But to make a long story short, I get that from the fact that you want to railroad everyone into using jsonb. That's called a straw man argument, Robert. Me: We should recommend that people use jsonb unless they have a specific reason for using json. Merlin: We should present them side-by-side with a complex comparison. From the cheap seats. To me the whole hstore/json/jsonb family is a WIP and any enlightenment in the form of comparisons would be greatly appreciated by me and other end users I would suspect. Robert: Josh wants to junk all relational data and use only jsonb! I mean, really, WTF? Seems to be a hot topic all the way around. I am neck deep in learning Web development and am coming to grips with the role of JSON in that world. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] open and close columns in the NEW record not allowed
On 02/06/2014 06:35 AM, Rafael Martinez Guerrero wrote: Hello One of our users is having a problem with a trigger in a system running postgresql 9.3. The problem is that pl/pgsql does not accept open and close as column names when used in the NEW record in a trigger function. This page: http://www.postgresql.org/docs/9.3/static/sql-keywords-appendix.html does not say that they are reserved words in postgresql (although they are reserved words in the sql standard) In the other hand, postgres allows to create and update tables with columns named open/close without problems. We think the behavior should be consistent, either it is allow to use them or not, but not like it is today. The catch all from here: http://www.postgresql.org/docs/9.3/interactive/sql-keywords-appendix.html is: As a general rule, if you get spurious parser errors for commands that contain any of the listed key words as an identifier you should try to quote the identifier to see if the problem goes away. Which indeed solves the problem on my end at least: test= CREATE OR REPLACE FUNCTION public.test_open() RETURNS trigger LANGUAGE plpgsql AS $function$ BEGIN INSERT INTO test_open_trigger (id, open) VALUES (NEW.id, NEW.open); RETURN NEW; END; $function$ ; test= \d test_open Table public.test_open Column |Type | Modifiers +-+--- id | integer | open | timestamp without time zone | Triggers: test_open AFTER INSERT ON test_open FOR EACH ROW EXECUTE PROCEDURE test_open() test= INSERT INTO test_open (id,open) VALUES (1,now()); INSERT 0 1 Thanks in advance. regards, -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] narwhal and PGDLLIMPORT
On 02/04/2014 10:53 AM, Tom Lane wrote: Joshua D. Drake j...@commandprompt.com writes: On 02/04/2014 09:34 AM, Tom Lane wrote: My own opinion is that I've already wasted untold man-hours thanks to the random porting problems induced by Windows, a platform that I never have and never will care about personally. I will *not* spend my own time doing tests that someone else could do. If we can't get some effort contributed by someone who does use that platform, I'm personally prepared to declare the entire damn thing no longer supported. Although that is obviously your prerogative it is important to remember that Windows is easily the second most used version of PostgreSQL out there (behind Linux). [ shrug... ] If it's so widely used, why is it so hard to find somebody who's willing to put in some development effort for it? Well, from what I have seen of the Windows world there is a fairly sharp demarcation between users and developers. So use does not necessarily mean developer knowledge. regards, tom lane -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Incorrectly reporting config errors
On 01/21/2014 10:26 AM, Thom Brown wrote: Hi all, I'm getting a report of a config error when changing a config value that requires a restart: In postgresql.conf max_connections = 92 (pg_ctl restart) postgres=# show max_connections ; max_connections - 92 (1 row) (Edit postgresql.conf so that max_connections = 93) (pg_ctl reload) Now the log file contains: 2014-01-21 18:14:53 GMT [28718]: [4-1] user=,db=,client= LOG: received SIGHUP, reloading configuration files 2014-01-21 18:14:53 GMT [28718]: [5-1] user=,db=,client= LOG: parameter max_connections cannot be changed without restarting the server 2014-01-21 18:14:53 GMT [28718]: [6-1] user=,db=,client= LOG: configuration file /home/thom/Development/data/postgresql.conf contains errors; unaffected changes were applied It doesn't contain errors. I changed the 92 to 93. If I restart, it doesn't complain, and there's nothing in the log about the config anymore. This seems to be the case for any parameter with a postmaster context. I can understand why it logs the message about it not changing without a restart, but the other one seems like a bug. You wanted a change in a value, the change did not occur, hence an error. I've tested this on 9.3 and 9.4devel. Thom -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Incorrectly reporting config errors
On 01/21/2014 12:29 PM, Robert Haas wrote: On Tue, Jan 21, 2014 at 1:59 PM, Thom Brown t...@linux.com wrote: On 21 January 2014 18:35, Tom Lane t...@sss.pgh.pa.us wrote: Thom Brown t...@linux.com writes: I'm getting a report of a config error when changing a config value that requires a restart: ... 2014-01-21 18:14:53 GMT [28718]: [4-1] user=,db=,client= LOG: received SIGHUP, reloading configuration files 2014-01-21 18:14:53 GMT [28718]: [5-1] user=,db=,client= LOG: parameter max_connections cannot be changed without restarting the server 2014-01-21 18:14:53 GMT [28718]: [6-1] user=,db=,client= LOG: configuration file /home/thom/Development/data/postgresql.conf contains errors; unaffected changes were applied It doesn't contain errors. Yeah it does: it's got a value that can't be applied. I think you're making a semantic quibble. I see it as technically wrong. There's nothing wrong with my config file. A reload of the file may not be able to apply all the settings, but there's no typo or mistake anywhere in my file. I would just need to restart instead of reload. However, given that you find it unsurprising, I'll leave it there. I kind of agree with Thom. I understand why it's doing what it's doing, but it still seems sort of lame. Though I am not sure why it is lame when it seems to be following protocol; announce the problem, then tell where it originates. Seems like useful information to me. postgres-2014-01-21 14:39:54.738 PST-0ERROR: parameter max_connections cannot be changed without restarting the server postgres-2014-01-21 14:39:54.738 PST-0STATEMENT: SET max_connections=99; -2014-01-21 14:42:23.166 PST-0LOG: received SIGHUP, reloading configuration files -2014-01-21 14:42:23.168 PST-0LOG: parameter max_connections cannot be changed without restarting the server -2014-01-21 14:42:23.169 PST-0LOG: configuration file /usr/local/pgsql93/data/postgresql.conf contains errors; unaffected changes were applied -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] pg_upgrade tablespaces
On 01/12/2014 07:02 PM, Bruce Momjian wrote: On Sun, Jan 12, 2014 at 12:48:40PM -0500, Tom Lane wrote: Bruce Momjian br...@momjian.us writes: On Sat, Jan 11, 2014 at 12:48:51PM -0800, Adrian Klaver wrote: I see, though I have another question. If pg_tablespace and the symlinks can get out of sync, as you say below, why is pg_tablespace considered the authority? Or to put it another way, why not just look at the symlinks as in 9.2+? Uh, good question. I think I used the system tables because they were easier to access. I can't remember if we used the symlinks for some things and pg_tablespace for other things in pre-9.2. Well, pre-9.2 pg_dumpall is going to make use of the pg_tablespace entries, because it has no other choice. We could conceivably teach pg_upgrade to look at the symlinks for itself, but we're not going to do that in pg_dumpall. Which means that the intermediate dump script would contain inconsistent location values anyway if the catalog entries are wrong. So I don't see any value in changing the quoted code in pg_upgrade. OK, agreed. It does however seem reasonable for pg_upgrade to note whether any of the paths are prefixed by old PGDATA and warn about the risks involved. Uh, the problem is that once you rename the old PGDATA, the pg_tablespace contents no longer point to the current PGDATA. The symlinks, if they used absolute paths, wouldn't point to the current PGDATA either. Well the problem is that it actually points to a current PGDATA just the wrong one. To use the source installation path and the suggested upgrade method from pg_upgrade. Start. /usr/local/pgsql/data/tblspc_dir mv above to /usr/local/pgsql_old/ install new version of Postgres to /usr/local/pgsql/data/ In the pgsql_old installation you have symlinks pointing back to the current default location. As well pg_tablespace points back to /usr/local/pgsql/data/ The issue is that there is not actually anything there in the way of a tablespace. So when pg_upgrade runs it tries to upgrade from /usr/local/pgsql/data/tblspc_dir to /usr/local/pgsql/data/tblspc_dir where the first directory either does not exist. or if the user went ahead and created the directory in the new installation, is empty. What is really wanted is to upgrade from /usr/local/pgsql_old/data/tblspc_dir to /usr/local/pgsql/data/tblspc_dir. Right now the only way that happens is with user intervention. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] pg_upgrade tablespaces
On 01/12/2014 08:04 PM, Bruce Momjian wrote: On Sun, Jan 12, 2014 at 07:58:52PM -0800, Adrian Klaver wrote: Well the problem is that it actually points to a current PGDATA just the wrong one. To use the source installation path and the suggested upgrade method from pg_upgrade. In the pgsql_old installation you have symlinks pointing back to the current default location. As well pg_tablespace points back to /usr/local/pgsql/data/ The issue is that there is not actually anything there in the way of a tablespace. So when pg_upgrade runs it tries to upgrade from /usr/local/pgsql/data/tblspc_dir to /usr/local/pgsql/data/tblspc_dir where the first directory either does not exist. or if the user went ahead and created the directory in the new installation, is empty. What is really wanted is to upgrade from /usr/local/pgsql_old/data/tblspc_dir to /usr/local/pgsql/data/tblspc_dir. Right now the only way that happens is with user intervention. Right, it points to _nothing_ in the _new_ cluster. Perhaps the simplest approach would be to check all the pg_tablespace locations to see if they point at real directories. If not, we would have to have the user update pg_tablespace and the symlinks. :-( Actually, even in 9.2+, those symlinks are going to point at the same nothing. That would support checking the symlinks in all versions. Agreed. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] pg_upgrade tablespaces
On 01/11/2014 10:55 AM, Bruce Momjian wrote: On Sat, Jan 11, 2014 at 10:40:20AM -0800, Adrian Klaver wrote: Right. I know there were multiple issue with this upgrade, jails probably being the biggest, but a new one I had never heard is that _if_ you are placing your tablespaces in the PGDATA directory, and you are upgrading from pre-9.2, if you rename the old data directory, you also need to start the old server and update pg_tablespace.spclocation. No one has ever reported that failure, but it would certainly happen. I wonder if pg_upgrade should be modified to check that pg_tablespace.spclocation point to real directories for pre-9.2 servers. I thought I was understanding, now I am not. This starts with your post of last night. So in pre-9.2 cases the tablespace location is recorded in two places pg_tablespace and the symlinks in pg_tblspc/. [ I am moving this discussion to hackers to get developer feedback. ] Right. When you upgrade pg_upgrade only looks at the pg_tablespace entry for pre-9.2 instances or does it look at the pg_tblspc symlinks also? If it looks at the symlinks would they need to be changed also? pg_upgrade looks in the pg_tablespace in pre-9.2, and uses a function in 9.2+. The query is: snprintf(query, sizeof(query), SELECT%s FROM pg_catalog.pg_tablespace WHERE spcname != 'pg_default' AND spcname != 'pg_global', /* 9.2 removed the spclocation column */ (GET_MAJOR_VERSION(old_cluster.major_version) = 901) ? -- spclocation : pg_catalog.pg_tablespace_location(oid) AS spclocation); I see, though I have another question. If pg_tablespace and the symlinks can get out of sync, as you say below, why is pg_tablespace considered the authority? Or to put it another way, why not just look at the symlinks as in 9.2+? As to your check for directories that sounds like a good idea, though I have one question. What constitutes a 'real' directory? I can see a situation where someone moves an existing instance from $PGDATA to $PGDATA.old and the installs a new version in $PGDATA. Then before they do the upgrade they create a new tablespace directory in $PGDATA. If they did not upgrade the spclocation in the old instance and ran the check it would find a directory but there would be nothing in it. So would the check look for actual tablespace data? I would probably just look for the directory. People are not supposed to be modifying their clusters during the upgrade, though, as stated, if they move the old cluster, the are required to update pg_tablespace if they have tablespaces in PGDATA, which is unfortunate. I think the big question on adding a check is, how many users of 9.4 are going to be upgrading from pre-9.2 and have tablespaces in PGDATA, and will be renaming their old PGDATA directory during the upgrade? We could add the test to 9.3 too, of course. Well it is not generally accepted that users should even be creating tablespaces in $PGDATA, but it is allowed by the program. My inclination is to say that it is then the programs'(Postgres) responsibility to deal with it. The alternative is to clarify the documentation and make it the users responsibility. As to users upgrading from 9.1- to 9.2+, I see still a lot of users posting to --general using 9.1- versions. At some point they will likely migrate, so I can see a fix being worthwhile. Having pg_tablespace and the symlinks get out of sync was the reason Magnus removed that duplication in 9.2, but I was unaware of how pg_upgrade really magnifies the problem for tablespaces in PGDATA by recommending a PGDATA rename. Well it was based on the valid assumption that people would create new tablespaces outside $PGDATA because that is really is what is meant to happen. You know us users we like to test assumptions:) -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Standalone synchronous master
On 01/10/2014 02:33 PM, Andres Freund wrote: On 2014-01-10 14:29:58 -0800, Joshua D. Drake wrote: db02 goes down. It doesn't matter why. It is down. db01 continues to accept orders, allow people to log into the website and we can still service accounts. The continuity of service continues. Why is that configuration advantageous over a async configuration is the question. Why, with those requirements, are you using a synchronous standby at all? +1 Greetings, Andres Freund -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Standalone synchronous master
On 01/10/2014 03:38 PM, Joshua D. Drake wrote: On 01/10/2014 03:17 PM, Josh Berkus wrote: Any continuous replication should not be a SPOF. The current behavior guarantees that a two node sync cluster is a SPOF. The proposed behavior removes that. Again, if that's your goal, then use async replication. I think I have gone about this the wrong way. Async does not meet the technical or business requirements that I have. Sync does except that it increases the possibility of an outage. That is the requirement I am trying to address. The purpose of sync rep is to know determinatively whether or not you have lost data when disaster strikes. If knowing for certain isn't important to you, then use async. PostgreSQL Sync replication increases the possibility of an outage. That is incorrect behavior. I want sync because on the chance that the master goes down, I have as much data as possible to fail over to. However, I can't use sync because it increases the possibility that my business will not be able to function on the chance that the standby goes down. What's a bad idea is adding an auto-degrade option without any tools to manage and monitor it, which is what this patch does by my reading. If This we absolutely agree on. As I see it the state of replication in Postgres is as follows. 1) Async. Runs at the speed of the master as it does not have to wait on the standby to signal a successful commit. There is some degree of offset between master and standby(s) due to latency. 2) Sync. Runs at the speed of the standby + latency between master and standby. This is counter balanced by knowledge that the master and standby are in the same state. As Josh Berkus pointed out there is a loop hole in this when multiple standbys are involved. The topic under discussion is an intermediate mode between 1 and 2. There seems to be a consensus that this is not unreasonable. The issue seems to be how to achieve this with ideas falling into roughly two camps. A) Change the existing sync mode to allow the master and standby fall out of sync should a standby fall over. B) Create a new mode that does this without changing the existing sync mode. My two cents would be to implement B. Sync to me is a contract that master and standby are in sync at any point in time. Anything else should be called something else. Then it is up to the documentation to clearly point out the benefits/pitfalls. If you want to implement something as important as replication without reading the docs then the results are on you. JD -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Standalone synchronous master
On 01/10/2014 04:25 PM, Stephen Frost wrote: Adrian, * Adrian Klaver (adrian.kla...@gmail.com) wrote: A) Change the existing sync mode to allow the master and standby fall out of sync should a standby fall over. I'm not sure that anyone is argueing for this.. Looks like here, unless I am really missing the point: http://www.postgresql.org/message-id/52d07466.6070...@commandprompt.com Proposed behavior: db01-sync-db02 Transactions are happening. Everything is happy. Website is up. Orders are being made. db02 goes down. It doesn't matter why. It is down. db01 continues to accept orders, allow people to log into the website and we can still service accounts. The continuity of service continues. Yes, there are all kinds of things that need to be considered when that happens, that isn't the point. The point is, PostgreSQL continues its uptime guarantee and allows the business to continue to function as (if) nothing has happened. For many and I dare say the majority of businesses, this is enough. They know that if the slave goes down they can continue to operate. They know if the master goes down they can fail over. They know that while both are up they are using sync rep (with various caveats). They are happy. They like that it is simple and just works. They continue to use PostgreSQL. B) Create a new mode that does this without changing the existing sync mode. My two cents would be to implement B. Sync to me is a contract that master and standby are in sync at any point in time. Anything else should be called something else. Then it is up to the documentation to clearly point out the benefits/pitfalls. If you want to implement something as important as replication without reading the docs then the results are on you. The issue is that there are folks who are argueing, essentially, that B is worthless, wrong, and no one should want it and therefore we shouldn't have it. Well you will not please everyone, just displease the least. Thanks, Stephen -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Standalone synchronous master
On 01/10/2014 04:48 PM, Joshua D. Drake wrote: On 01/10/2014 04:38 PM, Stephen Frost wrote: Adrian, * Adrian Klaver (adrian.kla...@gmail.com) wrote: On 01/10/2014 04:25 PM, Stephen Frost wrote: * Adrian Klaver (adrian.kla...@gmail.com) wrote: A) Change the existing sync mode to allow the master and standby fall out of sync should a standby fall over. I'm not sure that anyone is argueing for this.. Looks like here, unless I am really missing the point: Elsewhere in the thread, JD agreed that having it as an independent option was fine. Yes. I am fine with an independent option. I missed that. What confused me and seems to be generally confusing is the overloading of the term sync: Proposed behavior: db01-sync-db02 In my mind if that is an independent option it should have different name. I propose Schrödinger:) JD -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE
On 12/30/2013 12:45 PM, Andres Freund wrote: On 2013-12-30 12:29:22 -0800, Peter Geoghegan wrote: But even if that wasn't true, I don't know why you feel the need to go on and on about buffer locking like this months later. Are you trying to be provocative? Can you *please* stop? ERR? Peter? *You* quoted a statement of mine that only made sense in it's original context. And I *did* say that the point about buffer locking applied to the *past* version of the patch. Alright this seems to have gone from confusion about the proposal to confusion about the confusion. Might I suggest a cooling off period and a return to the discussion in possibly a Wiki page where the points/counter points could be laid out more efficiently? Andres -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SSL: better default ciphersuite
On 12/17/2013 08:26 AM, Bruce Momjian wrote: On Tue, Dec 17, 2013 at 09:51:30AM -0500, Robert Haas wrote: On Sun, Dec 15, 2013 at 5:10 PM, James Cloos cl...@jhcloos.com wrote: For reference, see: https://wiki.mozilla.org/Security/Server_Side_TLS for the currently suggested suite for TLS servers. ... But for pgsql, I'd leave off the !PSK; pre-shared keys may prove useful for some. And RC4, perhaps, also should be !ed. And if anyone wants Kerberos tls-authentication, one could add KRB5-DES-CBC3-SHA, but that is ssl3-only. Once salsa20-poly1305 lands in openssl, that should be added to the start of the list. I'm starting to think we should just leave this well enough alone. We can't seem to find two people with the same idea of what would be better than what we have now. And of course the point of making it a setting in the first place is that each person can set it to whatever they deem best. Yes, I am seeing that too. Can we agree on one that is _better_ than what we have now, even if we can't agree on a _best_ one? Agreed. I would note that what is being proposed is a default that helps those of us (myself included) that do not know ciphers in and out, start with reasonable expectation of protection. This is a GUC so it can be modified to suite personal taste. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] note to reviewers: reply to the original email
On Mon, Dec 2, 2013 at 1:30 PM, Peter Eisentraut pete...@gmx.net wrote: A note to reviewers participating in the commit fests: When you send a review of a patch, reply to the email that contains the patch. Do no start a new email thread like Review of 'Some Patch'. This is important for several reasons: Might want to change the example (http://www.postgresql.org/message-id/1247855889.6125.6.camel@lapdragon) in: https://wiki.postgresql.org/wiki/Reviewing_a_Patch It shows the behavior you do not want :) - It maintains the integrity of the email archives. The commit fest app is supposed to track discussions, not be a participating link in the discussions. - It makes sure the right people get your review. If you start a new thread, worst case, no one participating in the original thread will see your email. This is especially true if the original thread and the commit fest entry have different titles, and you don't even CC the patch author on your review. If you don't have the original email in your email client, at least doctor up the subject line so it looks like a reply, so that email clients can do a reasonable job sorting the two threads together. Yes, email is stupid. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] why semicolon after begin is not allowed in postgresql?
On 11/22/2013 02:24 PM, AK wrote: I am reading the following in the documentation: Tip: A common mistake is to write a semicolon immediately after BEGIN. This is incorrect and will result in a syntax error. So, common mistake means semicolons after BEGIN seem consistent to many people - it seems consistent to me as well. If PostgreSql allowed them, we would have one less rule to memorize, shorter documentation, less mistakes and so on. In other words, without this limitation PostgreSql would be slightly more useful, right? In Postgresql it is allowed: test= BEGIN ; BEGIN In plpgsql it is not, which is where you got the above documentation. That is because SQL BEGIN != plpgsql BEGIN What am I missing? Why do we need this rule? How is it making PostgreSql better? -- View this message in context: http://postgresql.1045698.n5.nabble.com/why-semicolon-after-begin-is-not-allowed-in-postgresql-tp5779905.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Review:Patch: SSL: prefer server cipher order
On 11/16/2013 06:24 AM, Marko Kreen wrote: On Fri, Nov 15, 2013 at 02:16:52PM -0800, Adrian Klaver wrote: On 11/15/2013 11:49 AM, Marko Kreen wrote: On Fri, Nov 15, 2013 at 11:16:25AM -0800, Adrian Klaver wrote: The description of the GUCs show up in the documentation but I am not seeing the GUCs themselves in postgresql.conf, so I could test no further. It is entirely possible I am missing a step and would appreciate enlightenment. Sorry, I forgot to update sample config. ssl-prefer-server-cipher-order-v2.patch - Add GUC to sample config - Change default value to 'true', per comments from Alvaro and Magnus. ssl-ecdh-v2.patch - Add GUC to sample config Well that worked. I made ssl connections to the server using psql and verified it respected the order of ssl_ciphers. I do not have a client available with a different view of cipher order so I cannot test that. Well, these are GUC patches so the thing to test is whether the GUCs work. ssl-prefer-server-cipher-order: Use non-standard cipher order in server, eg: RC4-SHA:DHE-RSA-AES128-SHA, see if on/off works. You can see OpenSSL default order with openssl ciphers -v. ssl_ciphers = 'RC4-SHA:DHE-RSA-AES128-SHA' ssl_prefer_server_ciphers = on #ssl_ecdh_curve = 'prime256v1' aklaver@panda:~/pgsqlTest/data ../bin/psql -d postgres -U aklaver -h localhost psql (9.4devel) SSL connection (cipher: RC4-SHA, bits: 128) ssl_ciphers = 'RC4-SHA:DHE-RSA-AES128-SHA' ssl_prefer_server_ciphers = off #ssl_ecdh_curve = 'prime256v1' aklaver@panda:~/pgsqlTest/data ../bin/psql -d postgres -U aklaver -h localhost psql (9.4devel) SSL connection (cipher: DHE-RSA-AES128-SHA, bits: 128) ssl-ecdh: It should start using ECDHE-RSA immediately. Also see if adding !ECDH to ciphers will fall back to DHE. It's kind of hard to test the ssl_ecdh_curve as you can't see it anywhere. I tested it by measuring if bigger curve slowed connecting down... ssl_ciphers = 'RC4-SHA:DHE-RSA-AES128-SHA' ssl_prefer_server_ciphers = off ssl_ecdh_curve = 'prime256v1' aklaver@panda:~/pgsqlTest/data ../bin/psql -d postgres -U aklaver -h localhost psql (9.4devel) SSL connection (cipher: DHE-RSA-AES128-SHA, bits: 128) ssl_ciphers = 'RC4-SHA:DHE-RSA-AES128-SHA' ssl_prefer_server_ciphers = on ssl_ecdh_curve = 'prime256v1' aklaver@panda:~/pgsqlTest/data ../bin/psql -d postgres -U aklaver -h localhost psql (9.4devel) SSL connection (cipher: RC4-SHA, bits: 128) ssl_ciphers = 'DEFAULT:!LOW:!EXP:!MD5:@STRENGTH' ssl_prefer_server_ciphers = on OR off ssl_ecdh_curve = 'prime256v1' aklaver@panda:~/pgsqlTest/data ../bin/psql -d postgres -U aklaver -h localhost psql (9.4devel) SSL connection (cipher: ECDHE-RSA-AES256-SHA, bits: 256) ssl_ciphers = 'DEFAULT:!ECDH:!LOW:!EXP:!MD5:@STRENGTH' ssl_prefer_server_ciphers = on ssl_ecdh_curve = 'prime256v1' aklaver@panda:~/pgsqlTest/data ../bin/psql -d postgres -U aklaver -h localhost psql (9.4devel) SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256) Bonus - test EC keys: $ openssl ecparam -name prime256v1 -out ecparam.pem $ openssl req -x509 -newkey ec:ecparam.pem -days 9000 -nodes \ -subj '/C=US/ST=Somewhere/L=Test/CN=localhost' \ -keyout server.key -out server.crt EC test: ssl_ciphers = 'DEFAULT:!LOW:!EXP:!MD5:@STRENGTH' ssl_prefer_server_ciphers = off OR on ssl_ecdh_curve = 'prime256v1' aklaver@panda:~/pgsqlTest/data ../bin/psql -d postgres -U aklaver -h localhost psql (9.4devel) SSL connection (cipher: ECDHE-ECDSA-AES256-SHA, bits: 256) ssl_ciphers = 'RC4-SHA:DHE-RSA-AES128-SHA' ssl_prefer_server_ciphers = on #ssl_ecdh_curve = 'prime256v1' Or ssl_ecdh_curve = 'prime256v1' aklaver@panda:~/pgsqlTest/data ../bin/psql -d postgres -U aklaver -h localhost psql: SSL error: sslv3 alert handshake failure FATAL: no pg_hba.conf entry for host ::1, user aklaver, database postgres, SSL off ssl-better-default: SSL should stay working, openssl ciphers -v 'value' should not contain any weak suites (RC4, SEED, DES-CBC, EXP, NULL) and no non-authenticated suites (ADH/AECDH). Not sure about the above, if it is a GUC I can't find it. If it is something else than I will have to plead ignorance. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Review:Patch: SSL: prefer server cipher order
On 11/16/2013 12:37 PM, Marko Kreen wrote: Thanks for testing! On Sat, Nov 16, 2013 at 12:17:40PM -0800, Adrian Klaver wrote: On 11/16/2013 06:24 AM, Marko Kreen wrote: ssl-better-default: SSL should stay working, openssl ciphers -v 'value' should not contain any weak suites (RC4, SEED, DES-CBC, EXP, NULL) and no non-authenticated suites (ADH/AECDH). Not sure about the above, if it is a GUC I can't find it. If it is something else than I will have to plead ignorance. The patch just changes the default value for 'ssl_ciphers' GUC. I am still not sure what patch you are talking about. The two patches I saw where for server_prefer and ECDH key exchange. The question is if the value works at all, and is good. What value would we be talking about? Note: I have been working through a head cold and thought processes are sluggish, handle accordingly:) -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Review:Patch: SSL: prefer server cipher order
On 11/16/2013 01:13 PM, Marko Kreen wrote: On Sat, Nov 16, 2013 at 01:03:05PM -0800, Adrian Klaver wrote: On 11/16/2013 12:37 PM, Marko Kreen wrote: Thanks for testing! On Sat, Nov 16, 2013 at 12:17:40PM -0800, Adrian Klaver wrote: On 11/16/2013 06:24 AM, Marko Kreen wrote: ssl-better-default: SSL should stay working, openssl ciphers -v 'value' should not contain any weak suites (RC4, SEED, DES-CBC, EXP, NULL) and no non-authenticated suites (ADH/AECDH). Not sure about the above, if it is a GUC I can't find it. If it is something else than I will have to plead ignorance. The patch just changes the default value for 'ssl_ciphers' GUC. I am still not sure what patch you are talking about. The two patches I saw where for server_prefer and ECDH key exchange. The question is if the value works at all, and is good. What value would we be talking about? Ah, sorry. It's this one: https://commitfest.postgresql.org/action/patch_view?id=1310 Got it, applied it. Results: openssl ciphers -v 'HIGH:!aNULL'|egrep '(RC4|SEED|DES-CBC|EXP|NULL|ADH|AECDH)' ECDHE-RSA-DES-CBC3-SHA SSLv3 Kx=ECDH Au=RSA Enc=3DES(168) Mac=SHA1 ECDHE-ECDSA-DES-CBC3-SHA SSLv3 Kx=ECDH Au=ECDSA Enc=3DES(168) Mac=SHA1 EDH-RSA-DES-CBC3-SHASSLv3 Kx=DH Au=RSA Enc=3DES(168) Mac=SHA1 EDH-DSS-DES-CBC3-SHASSLv3 Kx=DH Au=DSS Enc=3DES(168) Mac=SHA1 ECDH-RSA-DES-CBC3-SHA SSLv3 Kx=ECDH/RSA Au=ECDH Enc=3DES(168) Mac=SHA1 ECDH-ECDSA-DES-CBC3-SHA SSLv3 Kx=ECDH/ECDSA Au=ECDH Enc=3DES(168) Mac=SHA1 DES-CBC3-SHASSLv3 Kx=RSA Au=RSA Enc=3DES(168) Mac=SHA1 DES-CBC3-MD5SSLv2 Kx=RSA Au=RSA Enc=3DES(168) Mac=MD5 Note: I have been working through a head cold and thought processes are sluggish, handle accordingly:) Get better soon! :) Thanks, the worst is over. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Review:Patch: SSL: prefer server cipher order
On 11/16/2013 02:41 PM, Marko Kreen wrote: On Sat, Nov 16, 2013 at 02:07:57PM -0800, Adrian Klaver wrote: On 11/16/2013 01:13 PM, Marko Kreen wrote: https://commitfest.postgresql.org/action/patch_view?id=1310 Got it, applied it. Results: openssl ciphers -v 'HIGH:!aNULL'|egrep '(RC4|SEED|DES-CBC|EXP|NULL|ADH|AECDH)' ECDHE-RSA-DES-CBC3-SHA SSLv3 Kx=ECDH Au=RSA Enc=3DES(168) Mac=SHA1 ECDHE-ECDSA-DES-CBC3-SHA SSLv3 Kx=ECDH Au=ECDSA Enc=3DES(168) Mac=SHA1 EDH-RSA-DES-CBC3-SHASSLv3 Kx=DH Au=RSA Enc=3DES(168) Mac=SHA1 EDH-DSS-DES-CBC3-SHASSLv3 Kx=DH Au=DSS Enc=3DES(168) Mac=SHA1 ECDH-RSA-DES-CBC3-SHA SSLv3 Kx=ECDH/RSA Au=ECDH Enc=3DES(168) Mac=SHA1 ECDH-ECDSA-DES-CBC3-SHA SSLv3 Kx=ECDH/ECDSA Au=ECDH Enc=3DES(168) Mac=SHA1 DES-CBC3-SHASSLv3 Kx=RSA Au=RSA Enc=3DES(168) Mac=SHA1 DES-CBC3-MD5SSLv2 Kx=RSA Au=RSA Enc=3DES(168) Mac=MD5 DES-CBC3 is 3DES, which is fine. Plain DES-CBC would be bad. If you don't see any other issues perhaps they are ready for committer? I do not have any other questions/issues at this point. I am new to the review process, so I am not quite sure how it proceeds from here. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Review:Patch: SSL: prefer server cipher order
On 11/16/2013 03:09 PM, Marko Kreen wrote: On Sat, Nov 16, 2013 at 02:54:22PM -0800, Adrian Klaver wrote: On 11/16/2013 02:41 PM, Marko Kreen wrote: If you don't see any other issues perhaps they are ready for committer? I do not have any other questions/issues at this point. I am new to the review process, so I am not quite sure how it proceeds from here. Simple - just click on edit patch on commitfest page and change patch status to ready for committer. Then committers will know that they should look at the patch. Done for both: SSL: better default ciphersuite SSL: prefer server cipher order Thanks for helping me through the process. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Review:Patch: SSL: prefer server cipher order
On 11/16/2013 03:46 PM, Marko Kreen wrote: On Sat, Nov 16, 2013 at 03:21:19PM -0800, Adrian Klaver wrote: On 11/16/2013 03:09 PM, Marko Kreen wrote: On Sat, Nov 16, 2013 at 02:54:22PM -0800, Adrian Klaver wrote: On 11/16/2013 02:41 PM, Marko Kreen wrote: If you don't see any other issues perhaps they are ready for committer? I do not have any other questions/issues at this point. I am new to the review process, so I am not quite sure how it proceeds from here. Simple - just click on edit patch on commitfest page and change patch status to ready for committer. Then committers will know that they should look at the patch. Done for both: SSL: better default ciphersuite SSL: prefer server cipher order I think you already handled the ECDH one too: https://commitfest.postgresql.org/action/patch_view?id=1286 Aah, missed that one. I updated to show my review and mark as Ready for Committer. Thanks for helping me through the process. Thanks for reviewing. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Review:Patch: SSL: prefer server cipher order
First review of the above patch as listed in current CommitFest as well as subsequent ECDH patch in the thread below: http://www.postgresql.org/message-id/1383782378-7342-1-git-send-email-mark...@gmail.com Platform OpenSuse 12.2 Both patches applied cleanly. Configured: ./configure --with-python --with-openssl --prefix=/home/aklaver/pgsqlTest --with-pgport=5462 --enable-cassert make and make check ran without error. The description of the GUCs show up in the documentation but I am not seeing the GUCs themselves in postgresql.conf, so I could test no further. It is entirely possible I am missing a step and would appreciate enlightenment. The general premise seems sound, allowing the DBA control over the type of cipher of used. Thanks, -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Review:Patch: SSL: prefer server cipher order
On 11/15/2013 11:49 AM, Marko Kreen wrote: On Fri, Nov 15, 2013 at 11:16:25AM -0800, Adrian Klaver wrote: The description of the GUCs show up in the documentation but I am not seeing the GUCs themselves in postgresql.conf, so I could test no further. It is entirely possible I am missing a step and would appreciate enlightenment. Sorry, I forgot to update sample config. ssl-prefer-server-cipher-order-v2.patch - Add GUC to sample config - Change default value to 'true', per comments from Alvaro and Magnus. ssl-ecdh-v2.patch - Add GUC to sample config Well that worked. I made ssl connections to the server using psql and verified it respected the order of ssl_ciphers. I do not have a client available with a different view of cipher order so I cannot test that. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Review: Patch insert throw error when year field len 4 for timestamptz datatype
Initial review of the patch submitted in this message and listed in the current CommitFest: http://www.postgresql.org/message-id/cagpqqf3xwwc_4fhinz_g6ecvps_ov3k2pe4-aj1dg4iyy+f...@mail.gmail.com This patch would seem to be already committed here http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=7778ddc7a2d5b006edbfa69cdb44b8d8c24ec1ff Is a review necessary at this point? -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] Urgent Help Required
On 10/08/2013 03:55 AM, shailesh singh wrote: I had got this message while running vacuum full from backend . Now My database is not starting , Help pls. backend vacuum full debug; WARNING: database debug must be vacuumed within 99 transactions HINT: To avoid a database shutdown, execute a full-database VACUUM in debug. ERROR: relation debug does not exist Now what? First some information. 1) What version of Postgres are you using? 2) Does database debug in fact exist or not? In other words does it show up with \l in psql? Also it not necessary to use FULL with the VACUUM. Thanks in advance. Shailesh Singh -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] Urgent Help Required
On 10/08/2013 08:03 AM, shailesh singh wrote: Dear all, First of all i wish to share actual error meassge, Below are the queries i had executed on the terminal on my server -bash-3.2$ touch fix.sql -bash-3.2$ echo VACUUM FULL; fix.sql -bash-3.2$ postgres -D /var/lib/pgsql/data patnadbold fix.sql WARNING: database patnadbold must be vacuumed within 100 transactions HINT: To avoid a database shutdown, execute a full-database VACUUM in patnadbold. WARNING: database patnadbold must be vacuumed within 100 transactions HINT: To avoid a database shutdown, execute a full-database VACUUM in patnadbold. PostgreSQL stand-alone backend 8.1.11 After this i am able to stop /start my db server but i am not able to connect to my databases (it tells to run vacuum full first on patnadbold databases) 1)I am using postgres 8.4 version. This seems to be at odds with PostgreSQL stand-alone backend 8.1.11. Are you sure you are working on the correct database cluster? 2) I had two databases on this server i) patnadbold ii) patnaonlinedb For me patnadbold is of no use if at this moment i lost this database that also fine to me. I wanted to connect patnaonlinedb any how and wanted to perform backup of this , Solution please. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] currval and DISCARD ALL
On 04/19/2013 06:50 AM, Robert Haas wrote: On Wed, Apr 17, 2013 at 6:17 PM, Tom Lane t...@sss.pgh.pa.us wrote: No, it's a critical tool in complexity management. When you're dealing with systems as complicated as a database, every little non-orthogonal detail adds up. DISCARD ALL has a clear definition in terms of simpler commands, and it's going to stay that way. Either this is worth a subcommand, or it's not worth worrying about at all. And then you did this: commit e309739670ac8c2fa0b236d116fcd44b0522025a Author: Tom Lane t...@sss.pgh.pa.us Date: Thu Nov 27 00:28:06 2008 + Tweak wording of DISCARD ALL description to avoid giving the impression that the presented list of equivalent operations is meant to be the primary definition of what it does. Per comment from Guillaume Smet. So it seems to me that we pretty much already made a decision that the controlling definition of DISCARD ALL is that, as the fine manual says DISCARD ALL resets a session to its original state. Whatever decision we make now ought to be consistent with that. IOW, I don't care whether we introduce a new subcommand or not. But I *do* think that that we ought to make our best effort to have DISCARD ALL clear everything that smells like session-local state. Random incompatibilities between what you see when running under a connection pooler and what you see when connecting the DB directly are *bad*, regardless of whether a well-designed application should be relying on those particular things or not. The whole point of having a transparent connection pooler is that it's supposed to be transparent to the application. I understand the confusion on what constitutes ALL in DISCARD, though I am not sure about the incompatibility argument. The OP is using the transaction mode from pgBouncer and from their docs: http://wiki.postgresql.org/wiki/PgBouncer Transaction pooling Server connection is assigned to client only during a transaction. When PgBouncer notices that transaction is over, the server will be put back into pool. This mode breaks few session-based features of PostgreSQL. You can use it only when application cooperates by not using features that break. See the table below for incompatible features. Note that 'transaction' pooling breaks client expectations of server by design and can be used only if application cooperates by not using non-working features. Session pooling server_reset_query = DISCARD ALL; This will clean everything. Transaction pooling server_reset_query = Yes, empty. In transaction pooling mode the clients should not use any session-based features, so there is no need to clean anything. The server_reset_query would only add unnecessary round-trip between transactions and would drop various caches that the next transaction would unnecessarily need to fill again. I could see the argument for a transparent pooler where it part of the core code. Not sure if it is the projects responsibility to maintain transparency with the feature matrices of external projects. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: [GENERAL] timeofday() and clock_timestamp() produce different results when casting to timestamptz
On 03/23/2013 08:16 AM, Tom Lane wrote: Adrian Klaver adrian.kla...@gmail.com writes: Seems the time zone info still thinks Moscow time is being setting forward an hour for DST when in fact the time remains constant through the year. I think the discrepancy is between this in timezone/data/europe: Zone Europe/Moscow 2:30:20 - LMT 1880 2:30 - MMT 1916 Jul 3 # Moscow Mean Time 2:30:48 Russia %s 1919 Jul 1 2:00 3:00 Russia MSK/MSD 1922 Oct 2:00 - EET 1930 Jun 21 3:00 Russia MSK/MSD 1991 Mar 31 2:00s 2:00 Russia EE%sT 1992 Jan 19 2:00s 3:00 Russia MSK/MSD 2011 Mar 27 2:00s 4:00 - MSK and this in timezone/tznames/Default: MSD 14400 D # Moscow Daylight Time # (Europe/Moscow) MSK 10800# Moscow Time # (Europe/Moscow) We really need to figure out a way to update the tznames data automatically, or at least notice when it's become inconsistent with the underlying Olson database. So I temporary fix would be to go into /share/timezonesets/Default and change : MSK 10800# Moscow Time to MSK 14400# Moscow Time and then you get: test= set time zone 'Europe/Moscow'; SET test= select timeofday(), clock_timestamp(); timeofday |clock_timestamp -+--- Sun Mar 24 03:50:45.066537 2013 MSK | 2013-03-24 03:50:45.066582+04 (1 row) test= select timeofday()::timestamptz, clock_timestamp()::timestamptz; timeofday |clock_timestamp ---+--- 2013-03-24 03:50:52.485092+04 | 2013-03-24 03:50:52.485188+04 (1 row) regards, tom lane -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: DROP OWNED BY fails to drop privileges granted by non-owners (was Re: [GENERAL] Bug, Feature, or what else?)
On 02/08/2013 09:09 AM, Tom Lane wrote: Adrian Klaver adrian.kla...@gmail.com writes: On 02/08/2013 08:14 AM, Tom Lane wrote: Of course, postgres has other options besides that, of which DROP OWNED BY ak02 is probably the most appropriate here. Or if you really want to get rid of just that grant, SET ROLE TO akretschmer01 and revoke. The DROP OWNED was tried further up the thread and did not seem to work: Huh. You're right, here is a complete test case: regression=# create schema s1; cCREATE SCHEMA regression=# create user u1; CREATE ROLE regression=# create user u2; CREATE ROLE regression=# grant all on schema s1 to u1 with grant option; GRANT regression=# \c - u1 You are now connected to database regression as user u1. regression= grant all on schema s1 to u2; GRANT regression= \c - postgres You are now connected to database regression as user postgres. regression=# \dn+ s1 List of schemas Name | Owner | Access privileges | Description --+--+--+- s1 | postgres | postgres=UC/postgres+| | | u1=U*C*/postgres+| | | u2=UC/u1 | (1 row) regression=# drop user u2; -- expect failure here ERROR: role u2 cannot be dropped because some objects depend on it DETAIL: privileges for schema s1 regression=# drop owned by u2; DROP OWNED regression=# drop user u2; -- failure here is wrong ERROR: role u2 cannot be dropped because some objects depend on it DETAIL: privileges for schema s1 regression=# \dn+ s1 List of schemas Name | Owner | Access privileges | Description --+--+--+- s1 | postgres | postgres=UC/postgres+| | | u1=U*C*/postgres+| | | u2=UC/u1 | (1 row) I believe the problem is that DROP OWNED for privileges is implemented by calling REVOKE. As noted upthread, when a superuser does REVOKE, it's executed as though the object owner did the REVOKE, so only privileges granted directly by the object owner go away. In this particular example, DROP OWNED BY u1 makes the grant to u1 go away, and then the grant to u2 goes away via cascade ... but DROP OWNED BY u2 fails to accomplish anything at all, because postgres never granted anything directly to u2. We haven't seen this reported before, probably because the use of GRANT OPTIONS isn't very common, but AFAICS it's been wrong since the invention of DROP OWNED. It looks to me like DropOwnedObjects doesn't actually insist on superuserness to do DROP OWNED, only ability to become the role, which means that DROP OWNED BY is completely broken for privileges if executed by a non-superuser; the only privileges it would remove would be those granted by the current user to the target user. I'm not really sure what the desirable behavior would be in such a case though. Ordinary users can't revoke privileges granted *to* them, only privileges granted *by* them. So it's not necessarily the case that a non-superuser should be able to make all privileges granted to a target role go away, even if he's allowed to become the target role and thereby drop objects that it owns. I wonder how sensible it is really to allow DROP OWNED to non-superusers. I am not sure I am following. Are we talking two different cases here? 1) As mentioned in the first paragraph the case where running DROP OWNED as a supersuser does not work. 2) A non-superuser running DROP OWNED and not having the necessary privileges. regards, tom lane -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: DROP OWNED BY fails to drop privileges granted by non-owners (was Re: [GENERAL] Bug, Feature, or what else?)
On 02/08/2013 10:09 AM, Tom Lane wrote: Adrian Klaver adrian.kla...@gmail.com writes: I am not sure I am following. Are we talking two different cases here? What I was pointing out was that the non-superuser case seems to be broken almost completely, whereas the superuser case is only broken if the object owner has given away some grant options and those have been exercised. Got it, thanks. regards, tom lane -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] pg_dump -s dumps data?!
On 01/31/2012 04:36 AM, Robert Haas wrote: On Mon, Jan 30, 2012 at 11:18 PM, Tom Lanet...@sss.pgh.pa.us wrote: I don't recall that we thought very hard about what should happen when pg_dump switches are used to produce a selective dump, but ISTM reasonable that if it's user data then it should be dumped only if data in a regular user table would be. Yep. What's not apparent to me is whether there's an argument for doing more than that. It strikes me that the current design is not very friendly towards the idea of an extension that creates a table that's meant solely to hold user data --- you'd have to mark it as config which seems a bit unfortunate terminology for that case. Is it important to do something about that, and if so what? Is this anything more than a naming problem? Seems to me that would be dependent on what the future plans are for the extension mechanism. There is also the issue of backward compatibility for those people that are using configuration tables in their extensions and would like to maintain that separation. I could see adding another function that is similar and would be used to identify strictly user data tables. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] Strange problem with create table as select * from table;
On Friday, November 04, 2011 6:04:02 pm Tom Lane wrote: I wrote: A different line of thought is that there's something about these specific source rows, and only these rows, that makes them vulnerable to corruption during INSERT/SELECT. Do they by any chance contain any values that are unusual elsewhere in your table? One thing I'm wondering about right now is the nulls bitmap --- so do these rows have nulls (or not-nulls) in any place that's unusual elsewhere? Hah ... I have a theory. This is trivial to fix, now that we know there's a problem --- the function is only using that assumption to save itself a couple lines of code. Penny wise, pound foolish :-( I killed a few brain cells just reading the explanation:) regards, tom lane -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] Date conversion using day of week
On Wednesday, March 30, 2011 8:39:25 pm Brendan Jurd wrote:
On 31 March 2011 03:15, Steve Crawford scrawf...@pinpointresearch.com wrote:
On 03/29/2011 04:24 PM, Adrian Klaver wrote:
...
Well the strange part is only fails for SUN:...
test(5432)aklaver=select to_date('2011-13-SUN', 'IYYY-IW-DY');
to_date
2011-03-28
...
You specified Sunday as the day but the date returned is a Monday. I
would categorize that as a bug. (Hackers cc'd). Since Sunday is the last
day of an ISO week, it should have returned 2011-04-03.
My first inclination without consulting source or morning coffee is that
PostgreSQL is seeing Sunday as day zero. Note that while:
The relevant paragraphs in the docs are:
--
An ISO week date (as distinct from a Gregorian date) can be specified
to to_timestamp and to_date in one of two ways:
* Year, week, and weekday: for example to_date('2006-42-4',
'IYYY-IW-ID') returns the date 2006-10-19. If you omit the weekday it
is assumed to be 1 (Monday).
* Year and day of year: for example to_date('2006-291',
'IYYY-IDDD') also returns 2006-10-19.
Attempting to construct a date using a mixture of ISO week and
Gregorian date fields is nonsensical, and will cause an error. In the
context of an ISO year, the concept of a month or day of month has
no meaning. In the context of a Gregorian year, the ISO week has no
meaning. Users should avoid mixing Gregorian and ISO date
specifications.
--
We *could* make the OP's query return the Sunday of ISO week 2011-13,
which would be properly written 2011-13-7, but I think the right move
here would be to throw the error for illegal mixture of format tokens.
This is a trivial change -- just a matter of changing the from_date
type on the DAY, Day, day, DY, Dy, dy keys.
With the attached patch applied, this is what happens instead:
# select to_date('2011-13-SUN', 'IYYY-IW-DY');
ERROR: invalid combination of date conventions
HINT: Do not mix Gregorian and ISO week date conventions in a
formatting template.
If we wanted to make it work, then I think the thing to do would be
to add a new set of formatting tokens IDY, IDAY etc. I don't like the
idea of interpreting DY and co. differently depending on whether the
other tokens happen to be ISO week or Gregorian.
Just to play Devils advocate here, but why not? The day name is the same either
way, it is the index that changes. I am not sure why that could not be context
specific?
Cheers,
BJ
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] Date conversion using day of week
On 03/31/2011 08:27 AM, Brendan Jurd wrote: On 1 April 2011 02:00, Adrian Klaveradrian.kla...@gmail.com wrote: On Wednesday, March 30, 2011 8:39:25 pm Brendan Jurd wrote: If we wanted to make it work, then I think the thing to do would be to add a new set of formatting tokens IDY, IDAY etc. I don't like the idea of interpreting DY and co. differently depending on whether the other tokens happen to be ISO week or Gregorian. Just to play Devils advocate here, but why not? The day name is the same either way, it is the index that changes. I am not sure why that could not be context specific? To be perfectly honest, it's mostly because I was hoping not to spend very much more of my time in formatting.c. Every time I go in there I come out a little bit less sane. I'm concerned that if I do anything further to it, I might inadvertently summon Chattur'gha or something. But since you went to the trouble of calling me on my laziness, let's take a look at the problem. I understand, my foray into formatting.c has left an impression. At the time when the day-of-week token gets converted into a numeric value and put into the TmFromChar.d field, the code has no knowledge of whether the overall pattern is Gregorian or ISO (the DY field could well be at the front of the pattern, for example). Later on, in do_to_timestamp, the code expects the 'd' value to make sense given the mode (it should be zero-based on Sunday for Gregorian, or one-based on Monday for ISO). That's all well and good *except* in the totally bizarre case raised by the OP. Now I am confused the docs say: D day of the week, Sunday(1) to Saturday(7) ID ISO day of the week, Monday(1) to Sunday(7) This would seem to say they both are one-based but differ on the day that is 1. To resolve it, we could make TmFromChar.d always stored using the ISO convention (because zero then has the useful property of meaning not set) and converted to the Gregorian convention as necessary in do_to_timestamp. Since I am in this deep might as well go deeper. When I see the requirement: IYYY-IW-IDY(proposed) or YYY-WW-DY which is implied GYYY-GWW-GDY I see the constant being pulled out: I YYY-W-DY G YYY-W-DY I know this presents backwards compatibility issues. Also that the data formatting functions are supposed to track Oracle behavior. It just seems a way to simplify the formatting process. Thanks for taking the time to explain the process. Cheers, BJ -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] Date conversion using day of week
On 03/30/2011 09:15 AM, Steve Crawford wrote:
On 03/29/2011 04:24 PM, Adrian Klaver wrote:
...
Well the strange part is only fails for SUN:...
test(5432)aklaver=select to_date('2011-13-SUN', 'IYYY-IW-DY');
to_date
2011-03-28
...
You specified Sunday as the day but the date returned is a Monday. I
would categorize that as a bug. (Hackers cc'd). Since Sunday is the last
day of an ISO week, it should have returned 2011-04-03.
My first inclination without consulting source or morning coffee is that
PostgreSQL is seeing Sunday as day zero. Note that while:
I started going through the source (formatting.c,timestamp.c), got as
far as the Julian date functions before the brain imploded and I had to
take a break:) I would agree it has to do with the difference in the
week rotating around either Sunday or Monday.
select to_date('2011-13-1', 'IYYY-IW-ID');
to_date
2011-03-28
So does:
steve=# select to_date('2011-13-0', 'IYYY-IW-ID');
to_date
2011-03-28
So something isn't right. All sorts of other stuff is allowed as well -
I don't know if that's by design or not:
Well I can see how this is possible and indeed likely. The permutations
of all the possible date/time representations is immense. It just
emphasizes that when dealing with time consistency is good.
steve=# select to_date('2011-13--23', 'IYYY-IW-ID');
to_date
2011-03-04
steve=# select to_date('2011-13-56', 'IYYY-IW-ID');
to_date
2011-05-22
Cheers,
Steve
--
Adrian Klaver
adrian.kla...@gmail.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [GENERAL] [HACKERS] Postgres 9.1 - Release Theme
On 04/01/2010 09:54 AM, Scott Marlowe wrote: On Thu, Apr 1, 2010 at 10:05 AM, David E. Wheelerda...@kineticode.com wrote: On Apr 1, 2010, at 3:01 AM, Magnus Hagander wrote: I prefer to dump all my data in a big text file and grep it for the information I need. As long as you implement your own grep, that sounds about on par with the current trends! Go for it! Well, first you have to implement your own compiler. Also a lexer and a parser. All that will be for naught unless you hand wire your own logic boards. I mean really, come on. Actually I think this calls for quantum computing (http://en.wikipedia.org/wiki/Quantum_computer). The intersection of NoSQL and Quantum decoherence is almost to good to be true. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
