Re: [GENERAL] pg_stat_tmp and pg_upgrade
On Thu, Sep 28, 2017 at 08:02:44AM -0700, kbran...@pwhome.com wrote: > With Pg10.0 coming, I have a question about pg_stat_tmp and upgrades that I'm > hoping I can get some advice on. > > Historically, we've created a tmpfs "disk" and mounted it on > $PGDATA/pg_stat_tmp and then started Pg. For > most situations, this works well. However, we have one situation where it > doesn't. Therefore, we've decided > to put pg_stat_tmp outside of $PGDATA. > > OTOH, it occurs to me that when we run pg_upgrade, we're going to have 2 Pg's > running at the same time. So > my concern is that if both new and old have "stats_temp_directory = > '/db/pg_stat_tmp'" in their > postgresql.conf, are there going to be conflicts and "bad things" happen? Or > is there some sort of > versioning within that area that will keep stuff separate and we'll be OK? > > I'm concerned because I don't see anything like versioning when I look in the > "dir", so if both of them try > to create a "global.stat" then it seems like "bad things" could happen. > > OTOH, it's only stats and only an upgrade, so would it be better to make sure > that the new version doesn't > use that (that config isn't set so it uses the dir in PGDATA) and then change > it over once the upgrade is > done or what? > > I could see where the upgrade process wouldn't do anything with stats while > the 2 servers are running (but > only after the upgrade is done) so this might not matter. I will also say > that I don't see anything like > this mentioned in the 10.0 docs for pg_upgrade. > > I don't think it matters, but we'll be going from 9.5.1 to 10.0. pg_upgrade never runs the old and new servers at the same time. I am not sure if that would be OK, but I have never heard of problems related to this. I am afraid you will just need to test it. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- 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] pgcrypto encrypt
On Tue, Sep 19, 2017 at 12:42:40PM -0700, Jeff Janes wrote: > On Tue, Sep 19, 2017 at 12:20 PM, Bruce Momjian <br...@momjian.us> wrote: > > On Wed, Sep 6, 2017 at 04:19:52PM -0400, Stephen Cook wrote: > > Hello! > > > > Is there a way to decrypt data encrypted with the pgcrypto "encrypt" > > function, outside the database? Assuming that I know the key etc... > > Yes, I think so. pgcrypto uses openssl and gpg internally, so using > those tools should work. > > > I know that pgp_sym_encrypt and pgp_sym_decrypt interoperates well with > "outside the database" gpg, although dealing with armoring and de-armoring as > well as text mode or binary mode is a bit of a bother until you get used to > it. > > But he seems to be asking about the "F.25.4. Raw Encryption Functions". I > wouldn't want to reassure him that it would be easy to make those work outside > the database, without having seen it done. But it should of course be > possible > to make it work, even if that means rearranging the code of pgcrypto and > compiling into something that is standalone. Uh, it should be possible with openssl, but I have never tried it. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- 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] SAP Application deployment on PostgreSQL
On Fri, Sep 8, 2017 at 12:44:18PM -0700, John R Pierce wrote: > On 9/8/2017 12:34 PM, chiru r wrote: > > > >We have multiple SAP applications running on Oracle as backend and looking > >for an opportunity to migrate from Oracle to PostgreSQL. Has anyone ever > >deployed SAP on PostgreSQL community edition? > > > >Is PostgreSQL community involved in any future road-map of SAP application > >deployment on PostgreSQL? > > > Does SAP support PostgreSQL ? You would have to ask SAP, but the agreed-upon answer is no. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- 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] pgcrypto encrypt
On Wed, Sep 6, 2017 at 04:19:52PM -0400, Stephen Cook wrote: > Hello! > > Is there a way to decrypt data encrypted with the pgcrypto "encrypt" > function, outside the database? Assuming that I know the key etc... Yes, I think so. pgcrypto uses openssl and gpg internally, so using those tools should work. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- 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] [HACKERS] USER Profiles for PostgreSQL
On Tue, Sep 19, 2017 at 01:28:11PM -0400, Stephen Frost wrote: > Tom, > > * Tom Lane (t...@sss.pgh.pa.us) wrote: > > chiru r <chir...@gmail.com> writes: > > > We are looking for User profiles in ope source PostgreSQL. > > > For example, If a user password failed n+ times while login ,the user > > > access has to be blocked few seconds. > > > Please let us know, is there any plan to implement user profiles in > > > feature > > > releases?. > > > > Not particularly. You can do that sort of thing already via PAM, > > for example. > > Ugh, hardly and it's hokey and a huge pain to do, and only works on > platforms that have PAM. > > Better is to use an external authentication system (Kerberos, for > example) which can deal with this, but I do think this is also something > we should be considering for core, especially now that we've got a > reasonable password-based authentication method with SCRAM. Does LDAP do this too? -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- 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] effective_io_concurrency increasing
On Mon, Jun 19, 2017 at 10:49:59AM -0500, Merlin Moncure wrote: > On Mon, Jun 19, 2017 at 10:36 AM, Jeff Janes <jeff.ja...@gmail.com> wrote: > > If you have a RAID, set it to the number of spindles in your RAID and forget > > it. It is usually one of the less interesting knobs to play with. (Unless > > your usage pattern of the database is unusual and exact fits the above > > pattern.) > > Isn't that advice obsolete in a SSD world though? I was able to show > values up to 256 for a single device provided measurable gains for a > single S3500. It's true though that the class of queries that this > would help is pretty narrow. Our developer docs are much clearer: https://www.postgresql.org/docs/10/static/runtime-config-resource.html#runtime-config-resource-disk For magnetic drives, a good starting point for this setting is the number of separate drives comprising a RAID 0 stripe or RAID 1 mirror being used for the database. (For RAID 5 the parity drive should not be counted.) However, if the database is often busy with multiple queries issued in concurrent sessions, lower values may be sufficient to keep the disk array busy. A value higher than needed to keep the disks busy will only result in extra CPU overhead. SSDs and other memory-based storage can often process many concurrent requests, so the best value might be in the hundreds. I didn't backpatch this change since the original docs were not incorrect. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- 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] Upgrade method from 9.2 to 10 ?
On Fri, Jun 16, 2017 at 04:14:07PM -0400, Paul Jones wrote: > Would it be possible to upgrade from 9.2 to 10 by doing a pg_basebackup > to the new server, followed by pg_upgrade -k, then streaming replication > from the 9.2 server to the 10 server until we're ready to cut over to 10? > > The idea is to minimize downtime. You can't use streaming replication between different major versions. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [DOCS] [GENERAL] pg_upgrade --link on Windows
On Tue, Jun 13, 2017 at 04:07:48PM -0400, Bruce Momjian wrote: > On Fri, Jun 9, 2017 at 07:55:55AM -0700, Adrian Klaver wrote: > I apologize for not being smarter on this thread. When I helped with > the Windows port, I was told Windows didn't have hard links for use by > tablespace directories, so I got it into my head that Windows didn't > have hard links. Therefore, when I was writing the docs, I called them > junction points. > > Looking back to Postgres 9.0 where pg_upgrade was added to the tree, I > see that the code even at that time used hard links on Windows. I have > created the attached patch which I will apply to all current Postgres > versions to fix this error. > > Thanks for the report and the research. :-) Patch applied all the way back to 9.3, where the junction point mention first appeared. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- 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] pg_upgrade --link on Windows
On Wed, Jun 14, 2017 at 09:59:04AM +0200, Klaus P. Pieper wrote: > > -Ursprüngliche Nachricht- > > > > I apologize for not being smarter on this thread. When I helped with the > > Windows port, I was told Windows didn't have hard links for use by > tablespace > > directories, so I got it into my head that Windows didn't have hard links. > > Therefore, when I was writing the docs, I called them junction points. > > It's actually not "Windows" providing hard links, it is the file system > NTFS. FAT and its modern cousins don't provide hard links - but this will > rarely be used for databases these days. > However, ReFS (introduced with server 2012 and providing some new features > like automatic integrity checks, clustering etc.) does no longer provide > hard links. > Are hard links used anywhere else but in pg_upgrade? Nope, it is used only by transfer_relfile() calling linkFile(). -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- 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] pg_upgrade --link on Windows
On Fri, Jun 9, 2017 at 07:55:55AM -0700, Adrian Klaver wrote:
> On 06/09/2017 07:39 AM, Arnaud L. wrote:
> >See this page for more details :
> >http://cects.com/overview-to-understanding-hard-links-junction-points-and-symbolic-links-in-windows/
> >
> >
> >Under "Hard Link (Linking for individual files)" :
> >"If the target is deleted, its content is still available through the hard
> >link"
> >
> >Junction Point (Directory Hard Link):
> >"If the target is moved, renamed or deleted, the Junction Point still
> >exists, but points to a non-existing directory"
> >
> >BUT, when I try to "pg_upgrade --link --check" with old-data-dir and
> >new-data-dir on different volumes, I get an error saying that both
> >directories must be on the same volume if --link is used.
> >So maybe pg_upgrade uses hard-links (i.e. to files), and only the
> >documentation is wrong by calling them junctions (i.e. soft links to
> >files) ?
>
> Looks that way. In file.c in ~/src/bin/pg_upgrade I see:
>
> #ifdef WIN32
> 300 /* implementation of pg_link_file() on Windows */
> 301 static int
> 302 win32_pghardlink(const char *src, const char *dst)
> 303 {
> 304 /*
> 305 * CreateHardLinkA returns zero for failure
> 306 * http://msdn.microsoft.com/en-us/library/aa363860(VS.85).aspx
> 307 */
> 308 if (CreateHardLinkA(dst, src, NULL) == 0)
> 309 {
> 310 _dosmaperr(GetLastError());
> 311 return -1;
> 312 }
> 313 else
> 314 return 0;
> 315 }
> 316 #endif
[docs list added]
I apologize for not being smarter on this thread. When I helped with
the Windows port, I was told Windows didn't have hard links for use by
tablespace directories, so I got it into my head that Windows didn't
have hard links. Therefore, when I was writing the docs, I called them
junction points.
Looking back to Postgres 9.0 where pg_upgrade was added to the tree, I
see that the code even at that time used hard links on Windows. I have
created the attached patch which I will apply to all current Postgres
versions to fix this error.
Thanks for the report and the research. :-)
--
Bruce Momjian <br...@momjian.us>http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
diff --git a/doc/src/sgml/ref/pgupgrade.sgml b/doc/src/sgml/ref/pgupgrade.sgml
new file mode 100644
index bf58a0a..4e27112
*** a/doc/src/sgml/ref/pgupgrade.sgml
--- b/doc/src/sgml/ref/pgupgrade.sgml
***
*** 123,129
-k
--link
use hard links instead of copying files to the new
! cluster (use junction points on Windows)
--- 123,129
-k
--link
use hard links instead of copying files to the new
! cluster
--
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] pg_upgrade --link on Windows
On Fri, Jun 9, 2017 at 12:00:56PM +0200, Arnaud L. wrote: > Hi > > The pg_upgrade documentation for PostgreSQL 9.6 states that --link will use > junction points on Windows. > Shouldn't it rather user hard-links ? > If I'm not mistaken, with junction points (i.e. soft-links to directories), > the old data dir cannot be removed. > With hard-links to file, we can get rid of the old data dir once we are sure > that the upgrade is fine. I was told junction points on Windows were hard links and no one has ever complained about not being able to remove them. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- 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] Top posting....
On Thu, May 11, 2017 at 01:43:52PM -0400, Tom Lane wrote: > Absolutely. The point of quoting previous messages is not to replicate > the entire thread in each message; we have archives for that. The point > is to *briefly* remind readers what it is that you're responding to. > If you can't be brief, you are disrespecting your readers by wasting their > time. They've probably already read the earlier part of the thread anyway. Totally agree, and I am seeing non-trimmed bottom posts more often on the hackers list than I used to. I am thinking someone needs to start a hackers thread about that. Amen to the usability of the archives with no thread breaks --- I am sure that was not easy. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- 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] full text search on hstore or json with materialized view?
On Tue, Apr 18, 2017 at 02:38:15PM -0700, Rj Ewing wrote: > I am evaluating postgres for as a datastore for our webapp. We are moving away > from a triple store db due to performance issues. > > Our data model consists of sets of user defined attributes. Approx 10% of the > attributes tend to be 100% filled with 50% of the attributes having approx 25% > filled. This is fairly sparse data, and it seems that jsonb or hstore will be > best for us. > > Unfortunately, from my understanding, postres doesn't support fulltext search > across hstore or jsonb key:values or even the entire document. While this is > not a deal breaker, this would be a great feature to have. We have been > experimenting w/ elasticsearch a bit, and particularly enjoy this feature, > however we don't really want to involve the complexity and overhead of adding > elasticsearch in front of our datasource right now. Full text search of JSON and JSONB data is coming in Postgres 10, which is to to be released in September of this year: https://www.depesz.com/2017/04/04/waiting-for-postgresql-10-full-text-search-support-for-json-and-jsonb/ -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- 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] browser interface to forums please?
On Tue, Apr 4, 2017 at 12:01:24PM +0200, vinny wrote: > Now, I'm not saying the mailinglists should go, I'm saying there should be > an easier way > to access them. It should be possible to register on the site, post a > message and read replies, > without having to subscribe to the list and setup a way of dealing with the > influx of messages > that are, for the most post, simply not interesting to the average user. > > I'd love to have an RSS feed that contains only new questions, so I can just > watch the popup > on my screen the way I do with the rest of the world, and not have to deal > with replies to topics that I don't care about anyway. You might want to look at PgLife, which gives you a read-only view of what's currently happening in the Postgres community: http://pglife.momjian.us/ -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- 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] Configuring ssl_crl_file
On Tue, Feb 28, 2017 at 10:50:02PM +0100, Frazer McLean wrote: > On 28 Feb 2017, at 21:51, Bruce Momjian wrote: > >I have researched this and will post a blog and and document the fix in > >the next few months. The reason you have to supply the entire > >certificate chain to the root CA on the client is because you have not > >used the "-extensions v3_ca" flag to openssl when creating the CA x509 > >request. You have to mark the certificates as CAs so they are passed > >from the server to the client. You are looking for the CA certificates > >to say: > > > > X509v3 Basic Constraints: > > CA:TRUE > > > > My `ca.cert.pem` file has > > X509v3 Basic Constraints: critical > CA:TRUE > > The `intermediate.cert.pem` has > > X509v3 Basic Constraints: critical > CA:TRUE, pathlen:0 > > This intermediate cert was generated using the `v3_intermediate_ca` > extension defined in [1]. I wouldn’t expect *not* to have to give the full > certificate chain to the client, since both were created by me. > > To summarise my problem and solution: the connection worked fine until > `ssl_crl_file` was enabled. I was trying to use a CRL generated from the > intermediate CA, assuming PostgreSQL would trust it since it knows about the > full CA chain in `ssl_ca_file`. Apparently, it must be a CRL generated from > the root concatenated to a CRL generated from the intermediate, and then it > works. Oh, OK, that is beyond my understanding. Thanks. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- 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] Configuring ssl_crl_file
On Mon, Feb 27, 2017 at 12:11:47AM +0100, Frazer McLean wrote:
> I found a solution to the problem, which I’l send here to help those who
> find the original email via search.
>
> The intermediate CRL file must be concatenated to CRL files going back to
> the root CA.
I have researched this and will post a blog and and document the fix in
the next few months. The reason you have to supply the entire
certificate chain to the root CA on the client is because you have not
used the "-extensions v3_ca" flag to openssl when creating the CA x509
request. You have to mark the certificates as CAs so they are passed
from the server to the client. You are looking for the CA certificates
to say:
X509v3 Basic Constraints:
CA:TRUE
--
Bruce Momjian <br...@momjian.us>http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
--
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] About the MONEY type
On Wed, Nov 30, 2016 at 01:35:12PM -0800, John R Pierce wrote: > note, btw, TIMESTAMP WITH TIME ZONE doesn't actually store the timezone... > rather, it converts it to an internal representation of GMT, and then converts > it back to display time at the client's current (or specified) time zone. Right, TIMESTAMP WITH TIME ZONE converts the timestamp value to the local time zone on output. Imagine a monetary type that converted the money amount to local currency on output --- that would be cool. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- 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 is the 'data2' directory for?
On Tue, Oct 25, 2016 at 06:03:43PM -0400, Steven Hirsch wrote: > I notice that PostgreSQL (both 9.5.4 and 9.6) create a 'data2' directory as > a peer to the 'data' directory I specified at database initialization. What > is this directory for and what parameter determines its location? I am guessing someone at your site created it --- Postgres does not create such directories by default. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres upgrade from 9.4.9 to 9.6 using pg_upgrade error
On Tue, Oct 18, 2016 at 10:15:47PM +, Ashish Chauhan wrote: > · I am getting below error while executing above command: > > command: "/usr/lib/postgresql/9.4/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D > "/var/lib/postgresql/9.4/main" -o "-p 50432 -b -c config_file=/etc/postgresql/ > 9.4/main/postgresql.conf -c config_file=/etc/postgresql/9.6/main/ > postgresql.conf -c listen_addresses='' -c unix_socket_permissions=0700 -c > unix_socket_directories='/tmp'" start >> "pg_upgrade_server.log" 2>&1 > > waiting for server to start2016-10-18 22:11:30 UTC [13107-1] FATAL: > database files are incompatible with server > > 2016-10-18 22:11:30 UTC [13107-2] DETAIL: The data directory was initialized > by PostgreSQL version 9.6, which is not compatible with this version 9.4.9. > > stopped waiting > > pg_ctl: could not start server > > Examine the log output. > > > > Is it possible to upgrade Postgres 9.4.9 to 9.6 with pg_upgrade option or I > need to do it through pg_dump only? Any help would be appreciated. It should work. I think you haven't follow the cluster initialization instructions properly and installed the wrong version somewhere. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- 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] pg_upgrade not able to cope with pg_largeobject being in a different tablespace
On Thu, Oct 13, 2016 at 04:35:35PM +0200, Andreas Joseph Krogh wrote: > På torsdag 13. oktober 2016 kl. 16:09:34, skrev Bruce Momjian > <br...@momjian.us > >: > > On Thu, Oct 13, 2016 at 10:14:08AM +0200, Andreas Joseph Krogh wrote: > > I would assume that having pg_largeobject in a separate tablespace is > more and > > more common these days, having real-cheap SAN vs. fast-SSD for normal > tables/ > > indexes/wal. > > So common that no one has ever asked for this feature before? > > Sometimes one gets the feeling that one is the only one in the universe doing > something one considers "quite common":-) Yes, I often feel the same way. :-) Like, why am I the only person who thinks this is a natural thing to do. I find a lot of bugs that way. :-) -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- 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] pg_upgrade not able to cope with pg_largeobject being in a different tablespace
On Thu, Oct 13, 2016 at 10:14:08AM +0200, Andreas Joseph Krogh wrote: > I would assume that having pg_largeobject in a separate tablespace is more and > more common these days, having real-cheap SAN vs. fast-SSD for normal tables/ > indexes/wal. So common that no one has ever asked for this feature before? > So - I'm wondering if we can fund development of pg_upgrade to cope with this > configuration or somehow motivate to getting this issue fixed? > > Would any of the PG-companies (2ndQ, EDB, PgPro) take a stab at this? > > Any feedback welcome, thanks. You would need to get buy-in that that community wants the relocation of pg_largeobject to be supported via an SQL command, and at that point pg_upgrade would be modified to support that. It is unlikely pg_upgrade is going to be modified to support something that isn't supported at the SQL level. Of course, you can create a custom version of pg_upgrade to do that. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- 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 limits Postgres performance when the whole database lives in cache?
On Sat, Sep 3, 2016 at 10:45:47AM +1000, dandl wrote: > > Agreed. Stonebraker measured Shore DBMS, which is an academic > > database: > > > > http://research.cs.wisc.edu/shore/ > > > > If he had measured a production-quality database that had been > > optimized like Postgres, I would take more stock of his "overhead" > > numbers. > > Exactly! And that's what I'm asking: has anyone done or know of any figures > for Postgres, to set against these? Uh, well, there are Postgres tools that measure the overhead of locking on queries and stuff. I don't know any numbers myself. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- 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 limits Postgres performance when the whole database lives in cache?
On Fri, Sep 2, 2016 at 10:32:46AM -0700, Andres Freund wrote: > On 2016-09-02 11:10:35 -0600, Scott Marlowe wrote: > > On Fri, Sep 2, 2016 at 4:49 AM, dandl <da...@andl.org> wrote: > > > Re this talk given by Michael Stonebraker: > > > > > > http://slideshot.epfl.ch/play/suri_stonebraker > > > > > > > > > > > > He makes the claim that in a modern ‘big iron’ RDBMS such as Oracle, DB2, > > > MS > > > SQL Server, Postgres, given enough memory that the entire database lives > > > in > > > cache, the server will spend 96% of its memory cycles on unproductive > > > overhead. This includes buffer management, locking, latching (thread/CPU > > > conflicts) and recovery (including log file reads and writes). > > I think those numbers are overblown, and more PR than reality. > > But there certainly are some things that can be made more efficient if > you don't care about durability and replication. Agreed. Stonebraker measured Shore DBMS, which is an academic database: http://research.cs.wisc.edu/shore/ If he had measured a production-quality database that had been optimized like Postgres, I would take more stock of his "overhead" numbers. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- 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] 9.2 to 9.5 pg_upgrade losing data
On Mon, Aug 15, 2016 at 12:18:04PM -0700, Adrian Klaver wrote: > https://www.postgresql.org/docs/9.5/static/pgupgrade.html > > "Obviously, no one should be accessing the clusters during the upgrade. > pg_upgrade defaults to running servers on port 50432 to avoid unintended > client connections. You can use the same port number for both clusters when > doing an upgrade because the old and new clusters will not be running at the > same time. However, when checking an old running server, the old and new > port numbers must be different." > > In your OP you do not show overriding pg_upgrade defaults for ports, so > assuming the scripts are looking for the live ports and not the upgrade > ports that should not be an issue. Agreed. I have no idea what would cause this, and have never heard a report like this before. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- 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] Uber migrated from Postgres to MySQL
On Mon, Aug 1, 2016 at 11:54:00AM -0700, Jeff Janes wrote: > > Uh, that is only true if the slowness was in _dumping_ many objects. > > Most of the fixes have been for _restoring_ many objects, and that is > > done in the new cluster, so they should be OK. > > There have been improvements on both sides. For the improvements that > need to exist in the old-server to be effective, we did backpatch the > main one back to 9.1, in the October 2015 releases, specifically to > help people get off the old versions. So if you are on 9.1 with > tens/hundreds of thousands of objects, you need to do a minor version > upgrade to at least 9.1.19 before doing the major version upgrade. If > you are on 9.0 or before with so many objects, you don't have a lot of > good options. Yeah, that's what I remember. Thanks for the details. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- 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] Allowing multiple versions of PG under NetBSD
On Fri, Jul 29, 2016 at 03:09:59PM -0500, Larry Rosenman wrote: > >>I might take a look at the NetBSD package (I'm a developer) to see how > >>hard it would be to allow multiple versions. We do keep all the lib > >>stuff in a separate directory so that part would be relatively simple. > >>We just need to find all the binaries and make the names versioned and > >>add a symlink to the user selected primary version to the bare version > >>of the binary name. Example: > >> - psql.8.3 > >> - psql.9.1 > >> - psql.9.3 > >> - psql ==> psql.9.3 > >> > >>Other than linking to the correct library can you think of any other > >>issues with this? > > > >Data Directory naming, as well as keeping the init-scripts straight. > > > And who gets 5432, and Unix socket naming, it starts to get messy. Well, pg_upgrade uses a socket created in the current run directory, so that should be fine. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- 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] Uber migrated from Postgres to MySQL
On Fri, Jul 29, 2016 at 02:50:32PM -0400, Stephen Frost wrote: > > Er, using a not yet invented pg_downgrade:-) > > The short answer is 'no'. Consider a case like the GIN page changes- as > soon as you execute DML on a column that has a GIN index on it, we're > going to rewrite that page using a newer version of the page format and > an older version of PG isn't going to understand it. > > Those kind of on-disk changes are, I suspect, why you have to set the > "compatibility" option in the big $O product to be able to later do a > downgrade. Yes, you would need a mode that prevented new-format writes on the new server. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- 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] Uber migrated from Postgres to MySQL
On Fri, Jul 29, 2016 at 03:03:46PM -0400, D'Arcy J.M. Cain wrote: > As does NetBSD. The problem is that unlike Python (which BSD allows > multiple versions) there is only one executable to deal with. It's not > an insurmountable problem but it could get messy. > > The answer is either chroot or mount and run pg_upgrade on another > server. If you can afford the downtime you can also delete PG, install > the new version and run pg_upgrade without modifying the existing DB. > If it succeeds then replace the directories and restart the new > version. If it fails then uninstall PG, reinstall the older version > and restart. Lather, rinse, repeat until it upgrades cleanly. pg_upgrade needs to run the old and new server binaries as part of its operation, so that would not work. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- 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] Uber migrated from Postgres to MySQL
On Fri, Jul 29, 2016 at 07:49:36PM +0200, Maeldron T. wrote: > And yes, I hate upgrading PostgreSQL especially on FreeBSD where pg_upgrade > isn’t really an option. Is that because it is hard to install the old and new clusters on the same server on FreeBSD? -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- 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] Uber migrated from Postgres to MySQL
On Wed, Jul 27, 2016 at 01:02:40PM -0400, Bruce Momjian wrote: > Watching the video was helpful: > > https://vimeo.com/145842299 > > You can see the failover happened because of various user errors. That > doesn't excuse our bug, but I am not sure exactly how much they > understood of Postgres behavior. His talk near the end about the > replication infrastucture being exposed to them was also interesting. Here is a more balanced blog post that corrects some missing information, e.g. HOT updates, hot_standby_feedback: http://use-the-index-luke.com/blog/2016-07-29/on-ubers-choice-of-databases -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- 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] Uber migrated from Postgres to MySQL
On Thu, Jul 28, 2016 at 03:26:17PM -0700, John R Pierce wrote: > Uh, that is only true if the slowness was in _dumping_ many objects. > Most of the fixes have been for _restoring_ many objects, and that is > done in the new cluster, so they should be OK. > > > I thought we were talking about pg_upgrade in -k link mode? or does that > rely on a dump/restore --schema-only operation to create the metadata? Yes, it does, with our without -k --- -k only controls file link vs file copy. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- 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] Uber migrated from Postgres to MySQL
On Thu, Jul 28, 2016 at 12:35:23AM -0700, Jeff Janes wrote: > On Wed, Jul 27, 2016 at 9:48 PM, John R Pierce <pie...@hogranch.com> wrote: > > On 7/27/2016 9:39 PM, Jeff Janes wrote: > >> > >> That depends on how how many objects there are consuming that 1 TB. > >> With millions of small objects, you will have problems. Not as many > >> in 9.5 as there were in 9.1, but still it does not scale linearly in > >> the number of objects. If you only have thousands of objects, then as > >> far as I know -k works like a charm. > > > > > > millions of tables? > > Well, it was a problem at much smaller values, until we fixed many of > them. But the perversity is, if you are stuck on a version before the > fixes, the problems prevent you from getting to a version on which it > is not a problem any more. Uh, that is only true if the slowness was in _dumping_ many objects. Most of the fixes have been for _restoring_ many objects, and that is done in the new cluster, so they should be OK. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- 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] Uber migrated from Postgres to MySQL
On Thu, Jul 28, 2016 at 02:23:18AM -, Greg Sabino Mullane wrote: > > Marc wrote: > > I donât have a 1TB database to try it on, mind you, so your > > âwait couple of daysâ might be *with* the âlink option? > > I think you mean *without*, but yeah, there is no way the --link > option is going to take that long. Hard links are awesome. We've upgraded > some really big databases, and --link is really, really fast. > If you can't use --link (usually because you want to get over > the checksum hump), we use something like Bucardo to help out. > No need to ever wait a "couple of days" as OP claims. :) > > What really bites is the analyze afterwards. That's the part > that takes too long (yes, --in-stages helps some). Would love > to see progress made there. Agreed. I will try to prioritize it. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- 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] Uber migrated from Postgres to MySQL
On Wed, Jul 27, 2016 at 07:02:52PM -0400, Alvaro Herrera wrote: > Patrick B wrote: > > > > > > I think it's safe to say that that has absolutely nothing to do > > > with the size being 3TB. They symptoms you report are a little > > > thin to diagnose the actual cause. > > > > might be... we're using SATA disks... and that's a big problem. But still.. > > the size of the DB is indeed a problem. > > Andrew is correct -- the size of the database is not a limitation for > pg_upgrade. Disk tech is not relevant either. You may run into the > problem that you don't have enough disk space, but then that is not a > database or pg_upgrade problem, is it? > > Other things might cause issues, but since you haven't actually reported > the problem, we don't know what is or whether there is any possible fix. > > Then again, if you want to report a pg_upgrade failure, I suggest you > open a thread of your own rather than hijack this one. You need only minimal disk space when using pg_upgrade --link. I agree we would like a full bug report so we can find a fix for you. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- 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] Uber migrated from Postgres to MySQL
On Wed, Jul 27, 2016 at 01:58:25PM -0400, Rakesh Kumar wrote: > On Wed, Jul 27, 2016 at 1:54 PM, Marc Fournier > <marc.fourn...@2ndquadrant.com> wrote: > > > Stupid question here, but do we provide any less then what MySQL does? I’m > > reading: > > mysql provides same functionality for rollback like oracle/db2 > provides. That is, > rollback on a minor version upgrade possible, but not on major version > upgrade. > > I am surprised PG does not even allow minor version rollback. Uh, I thought we did. Perhaps there a few that didn't. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- 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] Uber migrated from Postgres to MySQL
On Wed, Jul 27, 2016 at 10:54:25AM -0700, Marc Fournier wrote: > http://dev.mysql.com/doc/refman/5.7/en/downgrading.html#downgrade-paths > > == > >Unless otherwise documented, the following downgrade paths are > supported: > > • Downgrading from a release series version to an older release series > version is supported using all downgrade methods. For example, downgrading > from 5.7.10 to 5.7.9 is supported. Skipping release series versions is > also > supported. For example, downgrading from 5.7.11 to 5.7.9 is supported. > > • Downgrading one release level is supported using the logical downgrade > method. For example, downgrading from 5.7 to 5.6 is supported. > > • Downgrading more than one release level is supported using the logical > downgrade method, but only if you downgrade one release level at a time. > For example, you can downgrade from 5.7 to 5.6, and then to 5.5. > > == > > So, downgrade minor releases can be done by just changing the binaries … > downgrading an older ‘major release’ requires a dump/reload … > > Unless I’m missing something, whether on PostgreSQL or MySQL, if you want to > go > back a major release, you would need to dump./ reload that 1TB database … What they wanted, and I think was mentioned in the document, was that they wanted to upgrade the slaves independently, then the master. I think MySQL supports that, Postgres doesn't. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- 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] Uber migrated from Postgres to MySQL
On Wed, Jul 27, 2016 at 12:59:59PM -0400, Bruce Momjian wrote: > On Wed, Jul 27, 2016 at 12:47:24PM -0400, Bruce Momjian wrote: > > On Wed, Jul 27, 2016 at 12:33:27PM -0400, Rakesh Kumar wrote: > > > On Wed, Jul 27, 2016 at 11:45 AM, Bruce Momjian <br...@momjian.us> wrote: > > > > > > > I agree, but I am not sure how to improve it. The big complaint I have > > > > heard is that once you upgrade and open up writes on the upgraded > > > > server, you can't re-apply those writes to the old server if you need to > > > > fall back to the old server. I also don't see how to improve that > > > > either. > > > > > > doesn't and pg_logical solve this by logically replicating and allowing > > > for > > > different architecture/version between the replication nodes ? > > > > Yes. I was saying I don't know how to improve pg_upgrade to address it. > > I think long-term we are looking at pg_logical for zero-downtime > upgrades and _downgrades_, and pg_upgrade for less overhead (I don't > want to make a second copy of my data) upgrades (but not downgrades). > > I think this is probably the best we are going to be able to do for a > long time. Oh, let me give credit to Simon, who has always seen pg_logical as providing superior upgrade options where the logical replication setup isn't a problem. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- 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] Uber migrated from Postgres to MySQL
On Wed, Jul 27, 2016 at 09:17:58AM +0200, Chris Travers wrote: > The replication section made me wonder though if they were using the right > replication solution for the job. If you don't want an on-disk copy, don't > use > physical replication. This being said there is one serious issue here that is > worth mentioning, which is that since autovacuum on the master has no > knowledge > of autovacuum on the slave, it is easy to have longer-running queries on a > slave that have rows they need to see removed by autovacuum and replication. Uh, see hot_standby_feedback: #hot_standby_feedback = off # send info from standby to prevent # query conflicts > All of the above being said, there are solutions to all the major problems. > But you have to know about them, where to look, and what to do. And with > higher scale, one very important aspect is that attention to detail starts to > matter a whole lot. I agree that there are some good points raised but I > wonder what the solutions are. There is room for some improvement in the > backend (it would really be nice to instrument and measure toasting/detoasting > overhead in explain analyze) but for a lot of these I wonder if that is > secondary. PostgreSQL is very well optimized for a certain series of tasks, > and one can build well optimized solutions well outside that. At a certain > point (including a certain scale) therewill be no substitute for a teamof > people who really know the db backend inside and out who can design around > limitations and I think that is true for all databases I have worked with. Watching the video was helpful: https://vimeo.com/145842299 You can see the failover happened because of various user errors. That doesn't excuse our bug, but I am not sure exactly how much they understood of Postgres behavior. His talk near the end about the replication infrastucture being exposed to them was also interesting. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- 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] Uber migrated from Postgres to MySQL
On Wed, Jul 27, 2016 at 12:47:24PM -0400, Bruce Momjian wrote: > On Wed, Jul 27, 2016 at 12:33:27PM -0400, Rakesh Kumar wrote: > > On Wed, Jul 27, 2016 at 11:45 AM, Bruce Momjian <br...@momjian.us> wrote: > > > > > I agree, but I am not sure how to improve it. The big complaint I have > > > heard is that once you upgrade and open up writes on the upgraded > > > server, you can't re-apply those writes to the old server if you need to > > > fall back to the old server. I also don't see how to improve that either. > > > > doesn't and pg_logical solve this by logically replicating and allowing for > > different architecture/version between the replication nodes ? > > Yes. I was saying I don't know how to improve pg_upgrade to address it. I think long-term we are looking at pg_logical for zero-downtime upgrades and _downgrades_, and pg_upgrade for less overhead (I don't want to make a second copy of my data) upgrades (but not downgrades). I think this is probably the best we are going to be able to do for a long time. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- 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] Uber migrated from Postgres to MySQL
On Wed, Jul 27, 2016 at 12:51:40PM -0400, Rakesh Kumar wrote: > On Wed, Jul 27, 2016 at 12:47 PM, Bruce Momjian <br...@momjian.us> wrote: > > > Yes. I was saying I don't know how to improve pg_upgrade to address it. > > This problem is there even in oracle/db2/sqlserver. None of them allow > rollback to the lower version > unless it is a minor version upgrade. Major version upgrade almost > definitely involves change in transaction log > (WAL) structure and hence no rollback. Oh, good to know. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- 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] Uber migrated from Postgres to MySQL
On Wed, Jul 27, 2016 at 12:33:27PM -0400, Rakesh Kumar wrote: > On Wed, Jul 27, 2016 at 11:45 AM, Bruce Momjian <br...@momjian.us> wrote: > > > I agree, but I am not sure how to improve it. The big complaint I have > > heard is that once you upgrade and open up writes on the upgraded > > server, you can't re-apply those writes to the old server if you need to > > fall back to the old server. I also don't see how to improve that either. > > doesn't and pg_logical solve this by logically replicating and allowing for > different architecture/version between the replication nodes ? Yes. I was saying I don't know how to improve pg_upgrade to address it. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- 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] Uber migrated from Postgres to MySQL
On Wed, Jul 27, 2016 at 10:22:27AM -0400, Scott Mead wrote: > That being said, it doesn't really provide a back-out plan. The beauty of > replication is that you can halt the upgrade at any point if need be and cut > your (hopefully small) losses. If you use -k, you are all in. Sure, you could > setup a new standby, stop traffic, upgrade whichever node you'd like (using > -k) > and still have the other ready in the event of total catastrophe. More often > than not, I see DBAs and sysads lead the conversation with "well, postgres > can't replicate from one version to another, so instead " followed by a > fast-glazing of management's eyes and a desire to buy a 'commercial > database'. I agree, but I am not sure how to improve it. The big complaint I have heard is that once you upgrade and open up writes on the upgraded server, you can't re-apply those writes to the old server if you need to fall back to the old server. I also don't see how to improve that either. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- 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] 9.6 beta2 win-x64 download links still point to beta1
On Fri, Jul 1, 2016 at 05:39:28PM -0400, Bruce Momjian wrote: > > Replacing beta1 with beta2 leads to a 404 (for both direct download links) > > Yes, I confirmed in a later email that it is still broken. > > It was also reported by someone else today: > > > https://www.postgresql.org/message-id/flat/CAJF2B_1ryzDSMuSR23mjBnxGbbSYS-8aSAx-hYtwRR2P5VUTiw%40mail.gmail.com > > I reported it to EDB a few days ago as well. This has been fixed. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- 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] 9.6 beta2 win-x64 download links still point to beta1
On Fri, Jul 1, 2016 at 10:12:46PM +0200, Thomas Kellerer wrote:
> Bruce Momjian schrieb am 28.06.2016 um 05:36:
> >On Fri, Jun 24, 2016 at 07:44:17AM +0200, Thomas Kellerer wrote:
> >>the Beta2 downloads on
> >>
> >> http://www.enterprisedb.com/products-services-training/pgdownload
> >> http://www.enterprisedb.com/products-services-training/pgbindownload
> >>
> >>still lead to Beta1 for the Windows 64bit builds.
> >>
> >>All others properly link to beta1
> >
> >This looks fixed now.
> >
>
> I still get the beta1 packages.
>
> This
>
>
> http://www.enterprisedb.com/postgresql-960-binaries-win64?ls=Crossover=Crossover
>
> redirects to
>
>
> http://get.enterprisedb.com/postgresql/postgresql-9.6.0-beta1-windows-x64-binaries.zip
>
>
> and
>
>
> http://www.enterprisedb.com/postgresql-960-beta-installers-win64?ls=Crossover=Crossover
>
> redirects to
>
>
> http://get.enterprisedb.com/postgresql/postgresql-9.6.0-beta1-windows-x64.exe
>
>
> Replacing beta1 with beta2 leads to a 404 (for both direct download links)
Yes, I confirmed in a later email that it is still broken.
It was also reported by someone else today:
https://www.postgresql.org/message-id/flat/CAJF2B_1ryzDSMuSR23mjBnxGbbSYS-8aSAx-hYtwRR2P5VUTiw%40mail.gmail.com
I reported it to EDB a few days ago as well.
--
Bruce Momjian <br...@momjian.us>http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
--
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] 9.6 beta2 win-x64 download links still point to beta1
On Fri, Jun 24, 2016 at 07:44:17AM +0200, Thomas Kellerer wrote: > Hello, > > the Beta2 downloads on > > http://www.enterprisedb.com/products-services-training/pgdownload > http://www.enterprisedb.com/products-services-training/pgbindownload > > still lead to Beta1 for the Windows 64bit builds. > > All others properly link to beta1 This looks fixed now. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- 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] Columnar store as default for PostgreSQL 10?
On Mon, Apr 25, 2016 at 11:20:11AM -0300, Alvaro Herrera wrote: > In our design, columnar or not is going to be an option: you're going to > be able to say "Dear server, for this table kindly set up columnar > storage for me, would you? Thank you very much." And then you’re going > to get a table which may be slower for regular usage but which will rock > for analytics. For most of your tables the current row-based store will > still likely be the best option, because row-based storage is much > better suited to the more general cases. I am coming late to this thread, but one item not discussed about columnar storage is the use of compression of identical column values across rows. Existing Postgres storage only compresses single values, not values across rows. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- 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] pg_upgrade with an extension name change
On Fri, Apr 8, 2016 at 12:15:27PM -0700, Christophe Pettus wrote: > I'm attempting to upgrade a database from 9.2 to 9.5 using pg_upgrade. > The 9.2 database has the "orafunc" extension installed, which appears > to have changed names to "orafce". pg_upgrade complains that it can't > find "orafunc" on 9.5, which is true. Is there a standard way of > handling this situation? Uh, I would uninstall the extension on the old cluster and reinstall it on the new one. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- 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] Does PG support in place upgrade
On Wed, Apr 27, 2016 at 10:30:41AM -0400, Bruce Momjian wrote:
> On Wed, Apr 27, 2016 at 10:24:36AM -0400, Rakesh Kumar wrote:
> > [PUsaBSKn_n] Compose (@composeio)
> > 4/26/16, 1:24 PM
> > You can now upgrade your #PostgreSQL 9.4 to 9.5 easily at Compose. buff.ly/
> > 1WRsFFu #RDBMS
> >
> >
> > Based on the above tweet it seems that PG has no native way of doing an
> > inplace
> > upgrade of a db. How do users upgrade db of tera byte size.
>
> That web page mentions pg_upgrade, which allows in-place major upgrades
> to happen in several minutes:
>
> Whichever backup you go with, it will be be restored to a new PostgreSQL
> deployment where we may, or may not, run the pg_upgrade tool.
>
> The only _zero-downtime_ upgrade option is with logical-based
> replication like Slony.
Oh, here are the upgrade docs about Postgres:
http://www.postgresql.org/docs/9.5/static/upgrading.html
--
Bruce Momjian <br...@momjian.us>http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +
--
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] Does PG support in place upgrade
On Wed, Apr 27, 2016 at 10:24:36AM -0400, Rakesh Kumar wrote: > [PUsaBSKn_n] Compose (@composeio) > 4/26/16, 1:24 PM > You can now upgrade your #PostgreSQL 9.4 to 9.5 easily at Compose. buff.ly/ > 1WRsFFu #RDBMS > > > Based on the above tweet it seems that PG has no native way of doing an > inplace > upgrade of a db. How do users upgrade db of tera byte size. That web page mentions pg_upgrade, which allows in-place major upgrades to happen in several minutes: Whichever backup you go with, it will be be restored to a new PostgreSQL deployment where we may, or may not, run the pg_upgrade tool. The only _zero-downtime_ upgrade option is with logical-based replication like Slony. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -- 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 advocacy
On Tue, Mar 22, 2016 at 10:16:22AM -0600, Scott Marlowe wrote: > On Tue, Mar 22, 2016 at 9:15 AM, Thomas Kellerer <spam_ea...@gmx.net> wrote: > > Bruce Momjian schrieb am 22.03.2016 um 16:07: > >> > >> However, I do think database upgrades are easier with Oracle RAC > > > > I think you can do a rolling upgrade with a standby, but I'm not entirely > > sure. > > I find Slony good for upgrading versions with minimal downtime, > including major version changes. It's very nature allows you to > migrate pieces and parts for testing etc, in ways that any kind of > byte streaming just can't do. Yes, and I assume logical replication will allow similar easy upgrades. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Roman grave inscription + -- 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 advocacy
On Mon, Mar 21, 2016 at 04:46:51PM +, Jernigan, Kevin wrote: > Disk is only a single point of failure in RAC if you configure > non-redundant storage. In general, Oracle recommends triple mirroring > to protect against disk failures, as they have had many experiences > over the years where customers with mirrored disks would see > consecutive disk failures within short periods of time. > > And RAC is widely used by Oracle’s larger customers, not only > for HA, but also in some cases for scale-out. Having said that, > it’s very true that any application running on Oracle RAC must be > configured to avoid hot block contention across RAC nodes, so it’s > not a completely transparent solution for scale out. I get asked about Oracle RAC often. My usual answer is that Oracle RAC gives you 50% of high reliability (storage is shared, mirroring helps) and 50% of scaling (CPU/memory is scaled, storage is not). The requirement to partition applications to specific nodes to avoid cache consistency overhead is another downside. (Slide 24 of my scaling presentation shows Oracle RAC, http://momjian.us/main/writings/pgsql/scaling.pdf .) I said the community is unlikely to go the Oracle RAC direction because it doesn't fully solve a single problem, and it is overly complex. The community prefers fully-solved problems and simpler solutions. For me, streaming replication fully solves the high reliability problem and sharding fully solves the scaling problem. Of course, if you need both, you have to deploy both, which gives you 100% of two solutions, rather than Oracle RAC which gives you 50% of each. However, I do think database upgrades are easier with Oracle RAC, and I think it is much easier to add/remove nodes than with sharding. For me, this chart summarizes it: HA Scaling Upgrade Add/Remove Oracle RAC 50% 50%easyeasy Streaming Rep. 100% 25%* hardeasy Sharding 0%100%hardhard * Allows read scaling -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Roman grave inscription + -- 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] Replacement for Oracle Text
On Fri, Feb 19, 2016 at 02:49:16PM +0100, s d wrote: > On 19 February 2016 at 14:19, Bruce Momjian <br...@momjian.us> wrote: > > Ah, no. That's not possible > > > > > > ...not possible, Yet. > > > > PostgreSQL grows by adding the features people need and its changing > rapidly. > > I wonder if PLPerl could be used to extract the words from a PDF > document and create a tsvector column from it. > > I don't know about PLPerl(I'm pretty sure it could be used for this purpose, > though.). On the other hand I've written code for this in Python which should > be easy to adapt for PLPython, if necessary. Right, so you would write a PL/Perl or PL/Python trigger function that would populate the tsvector column on every INSERT or UPDATE. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Roman grave inscription + -- 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] Replacement for Oracle Text
On Fri, Feb 19, 2016 at 11:53:26AM +, Simon Riggs wrote: > On 19 February 2016 at 11:46, Thomas Kellerer <spam_ea...@gmx.net> wrote: > > Daniel Westermann schrieb am 19.02.2016 um 12:41: > >>>> if I'd need to implement/replace Oracle Text (ww.oracle.com/ > technetwork/testcontent/index-098492.html). > >>>>> What choices do I have in PostgreSQL (9.5+) ? > > > >>Postgres also has a full text search (which I find much easier to use > than Oracle's): > >> > >>http://www.postgresql.org/docs/current/static/textsearch.html > > > > Yes, i have seen this. Can this be used to index and search binary > documents, e.g. pdf ? > > Ah, no. That's not possible > > > ...not possible, Yet. > > PostgreSQL grows by adding the features people need and its changing rapidly. I wonder if PLPerl could be used to extract the words from a PDF document and create a tsvector column from it. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Roman grave inscription + -- 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 vs Firebird SQL
On Wed, Feb 10, 2016 at 10:34:53AM +0200, Achilleas Mantzios wrote: > PostgreSQL *is* a reliable DB. > > About checksums in our office master DB that's a fine idea, too bad that > pg_upgrade doesn't cope with them > (and upgrading without pg_upgrade is out of the question) Just to clarify, pg_upgrade handles cases where the old/new clusters either both have checksums, or neither you can't change the checksum setting during pg_upgrade. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Roman grave inscription + -- 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] Recurring and non recurring events.
On Sat, Dec 26, 2015 at 03:15:50PM -0500, Tom Lane wrote: > Gavin Flower <gavinflo...@archidevsys.co.nz> writes: > > The motivation of bottom posting like this: is that people get to see > > the context before the reply, AND emails don't end up getting longer & > > longer as people reply at the beginning forgetting to trim the now > > irrelevant stuff at the end. > > Of course, this also requires that people have the discipline to trim > as much as possible of what they're quoting. Otherwise, not only do > the messages get longer and longer anyway, but you have to scroll to the > bottom to find what's new. > > The general rule for proper email quoting is to quote just enough to > remind readers what the context is. You are not trying to create a > complete archive of the whole thread in every message; we have email > archives for that. > > And the reason why this is worth doing is that it shows respect for > your readers' time. I'm not sure how many people look at each message > in a popular list like pgsql-general, but surely it's measured in the > thousands. If you spend a few minutes judiciously cutting quotes and > interspersing your responses in a logical fashion, that may save each > reader only a few seconds in reading/understanding your message, but > that's still a large net savings of time. Jumping in late here, but I am getting concerned that most web and mobile email readers make it difficult to inline quote stuff. Trimming text is particularly hard on mobile devices. As more people use web-based or mobile email clients, will the "nice" type of email formatting become rarer and rarer? -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Roman grave inscription + -- 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] JSONB performance enhancement for 9.6
On Mon, Jan 11, 2016 at 09:01:03PM -0500, Tom Smith wrote: > Hi, > > Congrats on the official release of 9.5 > > And I'd like bring up the issue again about if 9.6 would address the jsonb > performance issue > with large number of top level keys. > It is true that it does not have to use JSON format. it is about serialization > and fast retrieval > of dynamic tree structure objects. (at top level, it might be called dynamic > columns) > So if postgresql can have its own way, that would work out too as long as it > can have intuitive query > (like what are implemented for json and jsonb) and fast retrieval of a tree > like object, > it can be called no-sql data type. After all, most motivations of using no-sql > dbs like MongoDB > is about working with dynamic tree object. > > If postgresql can have high performance on this, then many no-sql dbs would > become history. I can give you some backstory on this. TOAST was designed in 2001 as a way to store, in a data-type-agnostic way, long strings compressed and any other long data type, e.g. long arrays. In all previous cases, _part_ of the value wasn't useful. JSONB is a unique case because it is one of the few types that can be processed without reading the entire value, e.g. it has an index. We are going to be hesitant to do something data-type-specific for JSONB. It would be good if we could develop a data-type-agnostic approach to has TOAST can be improved. I know of no such work for 9.6, and it is unlikely it will be done in time for 9.6. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Roman grave inscription + -- 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] Code of Conduct: Is it time?
On Tue, Jan 5, 2016 at 08:47:16AM -0800, Joshua Drake wrote: > Hello, > > I had a hard time writing this email. I think Code of Conducts are > non-essential, a waste of respectful people's time and frankly if > you are going to be a jerk, our community will call you out on it. > Unfortunately a lot of people don't agree with that. I have over the > course of the last year seen more and more potential users very > explicitly say, "I will not contribute to a project or attend a > conference that does not have a CoC". > > Some of us may be saying, "Well we don't want those people". I can't > argue with some facts though. Ubuntu has had a CoC[1] since the > beginning of the project and they grew exceedingly quick. Having > walls in the hallway of interaction isn't always a bad thing. > > In reflection, the only thing a CoC does is put in writing what > behaviour we as a project already require, so why not document it > and use it as a tool to encourage more contribution to our project? Just to give some context, the core team has quietly handled discipline issues for years. In fact, it was so quiet that no one really knew it was happening, unless you were one of those people that core had to discipline. This secrecy caused people who felt they needed help with unfair treatment to try to deal with discipline themselves, rather than come to core. The recognition of this behavior caused the creation of a core responsibilities web page: http://www.postgresql.org/developer/core/ I see a CoC as a way of codifying expected behavior in the same way the "core responsibilities" document does. It is also true that any document you create to try to fix bad behavior can be abused, e.g. laws to compensate victims of carelessly unsafe environments have yielded many unethical personal injury lawyers in the USA. Therefore, we need to be careful of negative CoC effects. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Roman grave inscription + -- 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] 9.4 upgrade Help using pg_upgrade
On Thu, Dec 3, 2015 at 12:33:25AM +, Sheena, Prabhjot wrote: > Current Architecture > > PrimaryHot Standby > > Postgresql 9.3 Postgresql 9.3 > DB Size 1.4 TB Database Size 1.4 TB > > > Want to upgrade both primary and hot Standby to 9.4 at same time. I m not > able > to figure out how to upgrade Hot standby and primary simultaneously. What ever > I have read so far only tells me about upgrading primary which I am able to do > it . Can anyone please guide me How to upgrade Secondary database so that I > don’t have to build standby again from the scratch after upgrading primary > database? Yes, read the 9.5 docs for upgrading the standby --- it also works for 9.4 and earlier: http://momjian.us/main/blogs/pgblog/2015.html#June_10_2015 -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Roman grave inscription + -- 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] Problems with pg_upgrade after change of unix user running db.
On Mon, Nov 30, 2015 at 04:51:15PM +, Benedikt Grundmann wrote: > Are you able to compile from 9.4 git head and test that? It seems > dumping inheriting constraints from parents has not worked properly for > some time. > > > Do I need to get the latest/head 9.2 or the latest/head 9.4 or both? For what > it is worth I just tried after upgrading to the latest released 9.2 (and same > 9.45) and that didn't work :-( You actually need non-released 9.4.X code that is in pg_dump, and we use 9.4 pg_dump to dump the 9.2 database. > I should certainly be able to compile from source. But the upgrade to 9.4 is > by far not high on my priority stack (other than maybe some speed wins there > is > nothing in 9.4 that we are eager for, there are some niceties but I can > happily > live without all of them for years) and has already consumed way more time > than > I had scheduled for it. So I'll return to focus on other work for at least > this week and maybe more depending on how that work goes. > > Thanks to everyone I'll certainly update this thread if / when I have more > time > to devote to this. The simplest solution is to wait for 9.4.6 to be released and test that. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Roman grave inscription + -- 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] Problems with pg_upgrade after change of unix user running db.
On Mon, Nov 30, 2015 at 08:08:50AM +, Benedikt Grundmann wrote: > > > On Sat, Nov 28, 2015 at 2:39 AM, Adrian Klaver <adrian.kla...@aklaver.com> > wrote: > > On 11/27/2015 06:07 PM, Tom Lane wrote: > > Adrian Klaver <adrian.kla...@aklaver.com> writes: > >> On 11/27/2015 08:15 AM, Bruce Momjian wrote: > >>> My guess is you are sharing the constraint name "seqno_not_null" with > >>> multiple tables. I think you are going to have to dig into the system > >>> tables to see where that is referenced and fix it. > > > >> In the post below the OP shows the tables involved(they where > inherited): > >> http://www.postgresql.org/message-id/ > cadbmknm_y9ewdawdq_8dj1muc0z_fgwtyad2rwchgexj2jv...@mail.gmail.com > > > > Inherited eh? Maybe related to 074c5cfbf. > > > I forgot to mention this earlier. This cluster is running 9.2.6 and I'm > attempting to upgrade to the latest 9.4.5 Well, 9.4.5 we released on October 8, 2015, and the commit mentioned happened on November 20, 2015, so that fix is not in 9.4.5: commit 074c5cfbfb4923158be9ccdb77420d6522d77538 Author: Tom Lane <t...@sss.pgh.pa.us> Date: Fri Nov 20 14:55:28 2015 -0500 Fix handling of inherited check constraints in ALTER COLUMN TYPE (again). The previous way of reconstructing check constraints was to do a separate "ALTER TABLE ONLY tab ADD CONSTRAINT" for each table in an inheritance hierarchy. However, that way has no hope of reconstructing the check constraints' own inheritance properties correctly, as pointed out in bug #13779 from Jan Dirk Zijlstra. What we should do instead is to do a regular "ALTER TABLE", allowing recursion, at the topmost table that has a particular constraint, and then suppress the work queue entries for inherited instances of the constraint. Annoyingly, we'd tried to fix this behavior before, in commit 5ed6546cf, but we failed to notice that it wasn't reconstructing the pg_constraint field values correctly. As long as I'm touching pg_get_constraintdef_worker anyway, tweak it to always schema-qualify the target table name; this seems like useful backup to the protections installed by commit 5f173040. In HEAD/9.5, get rid of get_constraint_relation_oids, which is now unused. (I could alternatively have modified it to also return conislocal, but that seemed like a pretty single-purpose API, so let's not pretend it has some other use.) It's unused in the back branches as well, but I left it in place just in case some third-party code has decided to use it. In HEAD/9.5, also rename pg_get_constraintdef_string to pg_get_constraintdef_command, as the previous name did nothing to explain what that entry point did differently from others (and its comment was equally useless). Again, that change doesn't seem like material for back-patching. I did a bit of re-pgindenting in tablecmds.c in HEAD/9.5, as well. Otherwise, back-patch to all supported branches. Are you able to compile from 9.4 git head and test that? It seems dumping inheriting contraints from parents has not worked properly for some time. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Roman grave inscription + -- 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] Problems with pg_upgrade after change of unix user running db.
On Fri, Nov 27, 2015 at 04:05:46PM +, Benedikt Grundmann wrote:
> > [as-proddb@nyc-dbc-001 upgrade-logs]$ tail pg_upgrade_dump_16416.log
> > pg_restore: creating CHECK CONSTRAINT seqno_not_null
> > pg_restore: creating CHECK CONSTRAINT seqno_not_null
> > pg_restore: [archiver (db)] Error while PROCESSING TOC:
> > pg_restore: [archiver (db)] Error from TOC entry 8359; 2606 416548282
> CHECK
> > CONSTRAINT seqno_not_null postgres_prod
> > pg_restore: [archiver (db)] could not execute query: ERROR: constraint
> > "seqno_not_null" for relation "js_activity_2011" already exists
> > Command was: ALTER TABLE "js_activity_2011"
> > ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT
> VALID;
>
> I have no idea, but this is a pg_dump bug or inconsistent system tables,
> rather than a pg_upgrade-specific bug.
>
>
> Any recommendation on how to proceed?
My guess is you are sharing the constraint name "seqno_not_null" with
multiple tables. I think you are going to have to dig into the system
tables to see where that is referenced and fix it.
--
Bruce Momjian <br...@momjian.us>http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Roman grave inscription +
--
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] Problems with pg_upgrade after change of unix user running db.
On Fri, Nov 27, 2015 at 09:38:54AM +, Benedikt Grundmann wrote:
> That worked (I also swapped the password columns so that I don't have to
> change
> pgpass entries). But I then ran into a different problem a little later on.
> I
> thought I quickly mention it here in case somebody can point me into the right
> direction:
>
...
> Restoring database schemas in the new cluster
>
> *failure*
> Consult the last few lines of "pg_upgrade_dump_16416.log" for
> the probable cause of the failure.
> child worker exited abnormally: Invalid argument
>
> *failure*
> Consult the last few lines of "pg_upgrade_server.log" for
> the probable cause of the failure.
>
> [as-proddb@nyc-dbc-001 upgrade-logs]$ tail pg_upgrade_dump_16416.log
> pg_restore: creating CHECK CONSTRAINT seqno_not_null
> pg_restore: creating CHECK CONSTRAINT seqno_not_null
> pg_restore: [archiver (db)] Error while PROCESSING TOC:
> pg_restore: [archiver (db)] Error from TOC entry 8359; 2606 416548282 CHECK
> CONSTRAINT seqno_not_null postgres_prod
> pg_restore: [archiver (db)] could not execute query: ERROR: constraint
> "seqno_not_null" for relation "js_activity_2011" already exists
> Command was: ALTER TABLE "js_activity_2011"
> ADD CONSTRAINT "seqno_not_null" CHECK (("seqno" IS NOT NULL)) NOT VALID;
I have no idea, but this is a pg_dump bug or inconsistent system tables,
rather than a pg_upgrade-specific bug.
--
Bruce Momjian <br...@momjian.us>http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Roman grave inscription +
--
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] Problems with pg_upgrade after change of unix user running db.
On Wed, Nov 25, 2015 at 08:04:49AM +, Benedikt Grundmann wrote: > You can see the 9.5 requirements in the pg_upgrade function > check_is_install_user(). You might as well just honor what that > requires as you will eventually be moving to 9.5. > > > Thanks I'll try this in one of the next days. Sorry for the radio silence in > the last 2 days. We have been quite busy at work. I don't think I understand Sure, no problem. I would have liked to reply to this sooner too, but had to do some research. > yet why this restriction exists (Neither the old nor the new). Is there some > doc somewhere that explains what's going on? I tried to find something in the > otherwise excellent postgres docs but failed. The comments at the top of pg_upgrade.c do explain this: * To simplify the upgrade process, we force certain system values to be * identical between old and new clusters: * * We control all assignments of pg_class.oid (and relfilenode) so toast * oids are the same between old and new clusters. This is important * because toast oids are stored as toast pointers in user tables. * * While pg_class.oid and pg_class.relfilenode are initially the same * in a cluster, they can diverge due to CLUSTER, REINDEX, or VACUUM * FULL. In the new cluster, pg_class.oid and pg_class.relfilenode will * be the same and will match the old pg_class.oid value. Because of * this, old/new pg_class.relfilenode values will not match if CLUSTER, * REINDEX, or VACUUM FULL have been performed in the old cluster. * * We control all assignments of pg_type.oid because these oids are stored * in user composite type values. * * We control all assignments of pg_enum.oid because these oids are stored * in user tables as enum values. * * We control all assignments of pg_authid.oid because these oids are stored <--- * in pg_largeobject_metadata. <--- I never expected users to care, but based on what you did, you obviously did need to care. The good news is that the system generated an error message that helped diagnose the problem, and the 9.5 error message is much clearer. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Roman grave inscription + -- 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] Problems with pg_upgrade after change of unix user running db.
On Mon, Nov 23, 2015 at 11:12:25AM +, Benedikt Grundmann wrote: > I got this error trying to upgrade one of our database clusters (happily in > testing) from 9.2 to 9.4: > > Old and new cluster install users have different values for pg_authid.oid > > Important background here is that we used to run the database as the postgres > unix user, but recently we had changed it to run as a different user (because > we have several different databases all running as the postgres user on > different machines and we wanted each logically separate database to run as a > different extra for that purpose unix user -- this simplified internal > administration management). > > We had done this by adding a new superuser to the database (with the name of > the unix user it will run as in the future). turning off the database, chown > -R > databasedir, starting the database Your description is very clear. In 9.4 and earlier, Postgres checks that the user running upgrade has the same pg_authid.oid in the old and new clusters. In 9.5 we check that the user is the BOOTSTRAP_SUPERUSERID (10) on both the old and new cluster. Therefore, what I suggest you do, before running pg_upgrade, is to rename the pg_authid.oid = 10 row to be your new install user instead of 'postgres', and make your new user row equal 'postgres', e.g. something like: -- You already did this first one --> test=> create user my_new_install_user; --> CREATE ROLE select oid from pg_authid where rolname = 'my_new_install_user'; oid --- 16385 (1 row) select oid from pg_authid where rolname = 'postgres'; oid - 10 (1 row) -- 'XXX' prevents duplicate names update pg_authid set rolname = 'XXX' where oid = 10; UPDATE 1 update pg_authid set rolname = 'postgres' where oid = 16385; UPDATE 1 update pg_authid set rolname = 'my_new_install_user' where oid = 10; UPDATE 1 What this does it to make your new install user the bootstrap user, which is a requirement for 9.5 pg_upgrade. You would do this _before_ running pg_upgrade as my_new_install_user. However, keep in mind that once you do this, everthing owned by my_new_install_user and postgres are now swapped. This is basically what you need to do after changing the ownership of the Postgres file system files. You can see the 9.5 requirements in the pg_upgrade function check_is_install_user(). You might as well just honor what that requires as you will eventually be moving to 9.5. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Roman grave inscription + -- 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] Creating Report for PieChart
On Wed, Oct 14, 2015 at 01:56:11AM +0200, Alex Magnum wrote: > Hello, > I need to process some statistics for a pie chart (json) where I only want to > show a max of 8 slices. If I have more data points like in below table I need > to combine all to a slice called others. If there are less or equal 8 i use > them as is. > > I am currently doing this with a plperl function which works well but was just > wondering out of curiosity if that could be done withing an sql query. > > Anyone having done something similar who could point me in the right > direction? I think you want the HAVING clause, e.g. HAVING COUNT(*) > 8. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Roman grave inscription + -- 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] Getting a leading zero on negative intervals with to_char?
On Wed, May 13, 2015 at 01:40:41PM -0400, Bruce Momjian wrote: > > I can't find any way to produce the output '-09:00' . There's no apparent > > way > > to add an additional width-specifier. HH24 is clearly not constrained to be > > 2 > > digits wide, since "-11" and "101" and "-101" are all output by "HH24". It > > seems like "-9" should be "-09" with the HH24 specifier, and "-9" with the > > "FMHH24" specifier. > > > > Opinions? > > > > Unless I'm doing something woefully wrong, Oracle compatibility doesn't > > seem to > > be an issue because we format intervals wildly differently to Oracle anyway: > > > > http://sqlfiddle.com/#!4/d41d8/2751 > > > > and it looks like Oracle handling of intervals isn't much like Pg anyway: > > > > http://stackoverflow.com/questions/970249/format-interval-with-to-char > > > > > > Arose from trying to find a non-ugly solution to this SO post: > > > > > > http://stackoverflow.com/questions/12335438/server-timezone-offset-value/ > > 12338490#12338490 > > [This is for 9.6.] > > I looked over this report from 2012, and the behavior still exists. I > think we have not seen more reports about this because negative > hours/years is not something people regularly use, but you found a need > for it. > > I think the big question is whether (4) or HH24 (2) represents > characters. or digits for zero-padding. printf() assumes it is > characters, e.g. %02d outputs "-2" not "-02", but I think our API > suggests it is digits, meaning the minus sign is not part of the > specific length, i.e. a minus sign is not a digit. > > I have developed the attached unified-diff patch which changes the > behavior to not consider the negative sign as a digit in all the places > I thought it was reasonable. Applied. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Roman grave inscription + -- 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] to_number, to_char inconsistency.
On Thu, May 14, 2015 at 01:02:01PM -0400, Bruce Momjian wrote:
> On Sun, Feb 10, 2013 at 06:27:02PM -0500, Tom Lane wrote:
> > Jeremy Lowery <jslow...@gmail.com> writes:
> > > I load and dump text files with currency values in it. The decimal in
> > > these
> > > input and output formats in implied. The V format character works great
> > > for
> > > outputing numeric data:
> >
> > > # select to_char(123.45, '999V99');
> > > to_char
> > > -
> > > 12345
> > > (1 row)
> >
> > > However, when importing data, the V doesn't do the same thing:
> >
> > > # select to_number('12345', '999V99');
> >
> > A look at the source code shows that to_number doesn't do anything at
> > all with the V format code, so this isn't terribly surprising. It
> > wouldn't be very hard to make it do the right thing, probably, but
> > nobody's had that particular itch yet. Feel free to scratch it and
> > send a patch ...
>
> (This is for 9.6.)
>
> I have developed the attached patch to support 'V' with to_number().
> Oracle doesn't support that, so we are on our own in defining the API.
>
> The patch doesn't handle non-whole-number strings very well as there is
> no way for the user to specify decimal precision because we have
> overridden the decimal digit meaning, but that seems fine to me as most
> users will be using whole numbers.
Patch applied.
--
Bruce Momjian <br...@momjian.us>http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Roman grave inscription +
--
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_statement = 'mod' does not log all data modifying statements
On Fri, Jun 12, 2015 at 01:54:30PM -0500, Jack Christensen wrote: > I was recently surprised by changes that were not logged by > log_statement = 'mod'. After changing log_statement to 'all', I > found that the changes were occurring in a writable CTE. > > Is there a way to log all statements that update data? Not really. Is this something we should document better? -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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] 9.5 beta pg_upgrade documentation
On Tue, Sep 1, 2015 at 06:56:11PM -0500, Andy Colson wrote: > >I also added a mention that rsync, not pg_upgrade, will be run on the > >standbys. You can see all the results of the patch here: > > > > http://momjian.us/pgsql_docs/pgupgrade.html > > > >Thanks. > > > > Sweet, I'm glad I stopped where I did. I think I'm safe to pick up at step > f. Which seemed to work ok. But now we get to step g (run rsync). I > checked the rsync manual and don't see anything like this three directory > argument thing you are trying to run. Unless you want to use --link-dest. > In which case I think the cmd would be like: > > rsync --archive --delete --hard-links --size-only --link-dest=old_pgdata > new_pgdata remote_dir > > I'm gonna try this now, will report back. No, you are copying "old_pgdata and new_pgdata" to remote_dir. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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] 9.5 beta pg_upgrade documentation
On Sun, Aug 23, 2015 at 09:45:50AM -0500, Andy Colson wrote: > I think we should add a step 6.5 (before step 7 Stop both servers) with > something like: > > If you are upgrading both a primary and standby, then we need to make sure > the standby is caught up. > If you are wal shipping then on primary run: select pg_switch_xlog(); > shut down primary > before you shut down the standby make sure it gets caught up to the primary. > > I don't think its 100% required for them to be exact, is it? If they are a > little different then rsync has more data to xfer from primary to standby ... > but it would still work. Right? You are one of the first to use this new ability so it is good to get your feedback. I have developed the attached applied patch to address the problems you saw. First, the verification has to happen earlier, before pg_upgrade is run. I think what is happening is that a checkpoint on server shutdown is changing the value while pg_upgrade is running, and the rename of the controldata file is another issue, so doing it right after the primary is shut down is the right place. I also added a mention that rsync, not pg_upgrade, will be run on the standbys. You can see all the results of the patch here: http://momjian.us/pgsql_docs/pgupgrade.html Thanks. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + diff --git a/doc/src/sgml/ref/pgupgrade.sgml b/doc/src/sgml/ref/pgupgrade.sgml new file mode 100644 index ebc0d58..fcb0316 *** a/doc/src/sgml/ref/pgupgrade.sgml --- b/doc/src/sgml/ref/pgupgrade.sgml *** NET STOP postgresql-9.0 *** 310,317 ! Streaming replication and log-shipping standby servers can remain running until ! a later step. --- 310,330 ! Streaming replication and log-shipping standby servers can ! remain running until a later step. ! ! ! ! ! Verify standby servers ! ! ! If you are upgrading Streaming Replication and Log-Shipping standby ! servers, verify that the old standby servers are caught up by running ! pg_controldata against the old primary and standby ! clusters. Verify that the Latest checkpoint location ! values match in all clusters. (There will be a mismatch if old ! standby servers were shut down before the old primary.) *** pg_upgrade.exe *** 404,410 If you have Streaming Replication () or Log-Shipping () standby servers, follow these steps to ! upgrade them (before starting any servers): --- 417,425 If you have Streaming Replication () or Log-Shipping () standby servers, follow these steps to ! upgrade them. You will not be running pg_upgrade ! on the standby servers, but rather rsync. Do not ! start any servers yet. *** pg_upgrade.exe *** 447,464 - - Verify standby servers - - -To prevent old standby servers from being modified, run -pg_controldata against the primary and standby -clusters and verify that the Latest checkpoint location -values match in all clusters. (This requires the standbys to be -shut down after the primary.) - - - Save configuration files --- 462,467 -- 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] bdr download
On Mon, Aug 31, 2015 at 10:24:26AM -0400, Ray Stell wrote: > Two comments on the BDR docs: > > The second option provided here, http://bdr-project.org/docs/stable/ > installation-source.html#INSTALLATION-SOURCE-PREREQS > "3.3.2.2 Downloading release source tarballs," seems to be an endless loop > between http://bdr-project.org/ and http://2ndquadrant.com/en/resources/bdr/ > > Might want to edit here: http://bdr-project.org/docs/stable/ > appendix-signatures.html > " The BDR / 9.4 RPM releases key key is in turn signed..." BDR is not currently part of community Postgres so you will need to report it to them directly. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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] bdr download
On Mon, Aug 31, 2015 at 12:30:52PM -0300, Alvaro Herrera wrote: > > BDR is not currently part of community Postgres so you will need to > > report it to them directly. > > As discussed a year ago or so, this list is what to use for BDR reports > and discussions, so this report is in the right place. Huh, why did we decide that when the community doesn't control any of it? That doesn't make any sense. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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] bdr download
On Mon, Aug 31, 2015 at 09:58:36AM -0700, Joshua Drake wrote: > On 08/31/2015 09:37 AM, Bruce Momjian wrote: > >On Mon, Aug 31, 2015 at 12:30:52PM -0300, Alvaro Herrera wrote: > >>>BDR is not currently part of community Postgres so you will need to > >>>report it to them directly. > >> > >>As discussed a year ago or so, this list is what to use for BDR reports > >>and discussions, so this report is in the right place. > > > >Huh, why did we decide that when the community doesn't control any of > >it? That doesn't make any sense. > > > > It was decided during initial development because the end result is > that BDR will be included in core. > > At least as far as I recall. Yeah, I just read the thread. I guess with the low volume makes sense to use "general", but I figured if someone went to the work of developing a website for BDR, they would just as soon create a mailing list hosted there, but I guess not. It clearly is being developed by 2nd Quadrant: http://bdr-project.org/docs/next/index.html BDR is developed by the BDR team at 2ndQuadrant. Multiple customers contribute funding and other resources to make BDR development possible. 2ndQuadrant continues to fund the ongoing development of BDR to meet internal needs and those of customers. It doesn't look like a community project to me --- it looks like an open source project funded by 2nd Quadrant and its customers, which is fine, but I don't know why the community is fielding questions about it on its mailing list. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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] bdr download
On Mon, Aug 31, 2015 at 04:30:33PM -0300, Alvaro Herrera wrote: > The whole thing is intended to be integrated to upstream Postgres. I > can't believe you haven't seen the following pieces being committed: > > - logical decoding > - replication slots > - replication origin identifier > - commit timestamp > - DDL deparsing > > 2ndQuadrant's intention is that eventually what we now call BDR will be > integrated functionality, part of the core git repository. As I > understand, the only pieces that touch the backend code remaining to > implement the complete BDR functionality is the sequence AM patch, > submitted to prior commitfests, considered almost ready, and not yet > updated but hopefully will be submitted for 9.6 soon. > > Questions by users and discussion in the community lists help shape the > design so that it is most useful to them, just like they shape Postgres > itself. If other people want to join the team developing BDR, they are > most welcome. If you think things are moving a positive direction for inclusion, that's fine with me. I had not seen much activity recently. -- Bruce Momjian <br...@momjian.us>http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] The purpose of the core team
There has been some confusion by old and new community members about the purpose of the core team, and this lack of understanding has caused some avoidable problems. Therefore, the core team has written a core charter and published it on our website: http://www.postgresql.org/developer/core/ Hopefully this will be helpful to people. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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] Minor revision downgrade (9.2.11 - 9.2.10)
On Tue, Jun 2, 2015 at 04:40:15PM +1200, Fabio Ugo Venchiarutti wrote: We're fairly confident that it's an issue with the hardware but we have political reasons to downgrade PG to 9.2.10 to show the hosting supplier that it's their fault. The release notes for 9.2.11 mention no data structure changes (in line with the usual PG versioning policy). Is it just as safe to downgrade too? We tested it on a couple non-critical boxes to no ill effect whatsoever, but we'd like a second opinion before we do it on the live installation too. I have rarely seen this question asked. I think minor-release downgrading is fine in this case. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] Re: [GENERAL] 9.4.1 - 9.4.2 problem: could not access status of transaction 1
On Thu, May 28, 2015 at 07:24:26PM -0400, Robert Haas wrote: On Thu, May 28, 2015 at 4:06 PM, Joshua D. Drake j...@commandprompt.com wrote: FTR: Robert, you have been a Samurai on this issue. Our many thanks. Thanks! I really appreciate the kind words. So, in thinking through this situation further, it seems to me that the situation is pretty dire: 1. If you pg_upgrade to 9.3 before 9.3.5, then you may have relminmxid or datminmxid values which are 1 instead of the correct value. Setting the value to 1 was too far in the past if your MXID counter is 2B, and too far in the future if your MXID counter is 2B. 2. If you pg_upgrade to 9.3.7 or 9.4.2, then you may have datminmxid values which are equal to the next-mxid counter instead of the correct value; in other words, they are two new. 3. If you pg_upgrade to 9.3.5, 9.3.6, 9.4.0, or 9.4.1, then you will have the first problem for tables in template databases, and the second one for the rest. (See 866f3017a.) I think we need to step back and look at the brain power required to unravel the mess we have made regarding multi-xact and fixes. (I bet few people can even remember which multi-xact fixes went into which releases --- I can't.) Instead of working on actual features, we are having to do this complex diagnosis because we didn't do a thorough analysis at the time a pattern of multi-xact bugs started to appear. Many projects deal with this compound bug debt regularly, but we have mostly avoided this fate. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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] ECPG SET CONNECTION
On Fri, May 15, 2015 at 01:10:27PM +0200, Michael Meskes wrote: On 14.05.2015 19:35, Bruce Momjian wrote: On Fri, May 31, 2013 at 02:26:08PM +0200, Leif Jensen wrote: Hi guys. In the ECPG manual (including latest 9.1.9) about ECPG SQL SET CONNECTION connection name; it is stated that This is not thread-aware. When looking in the ecpg library code connect.c for ECPGsetconn( ... ), it looks very much like it is thread-aware if translated with the --enable-thread-safety option. What should I believe ? Can someone comment on this report from 2013? Sorry, it seems I missed this email. Yes, the code should be thread-aware, at least I don't know of any problems with it. It appears to me that the docs haven't been updated by the patch that made ecpg work with threads back in the day. Thanks. Is that the only doc line that needs adjustment? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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] pg_upgrade failing from 9.3 to 9.4 because template0 already exists
On Fri, May 15, 2015 at 10:49:43AM -0400, Stephen Frost wrote: Bruce, * Bruce Momjian (br...@momjian.us) wrote: On Mon, Mar 9, 2015 at 12:43:05PM -0400, Bruce Momjian wrote: On Fri, Mar 6, 2015 at 06:10:15PM -0500, Stephen Frost wrote: The first is required or anyone who has done that will get the funny error that started this thread and things won't work anyway, but I believe the latter is also necessary to patch and back-patch as it could lead to data loss. It's not a high potential as, hopefully, people will check first, but I can imagine a hosting provider or environments where there are lots of independent clusters not catching this issue in their testing, only to discover someone set their database to 'datallowconn = false' for whatever reason and now that database is gone... Agreed. I will work on a patch for this. Attached is a patch that implements this, and it should be backpatch to all versions. Excellent and agreed. Just looked through the patch and didn't do a full review, but it looks good to me. OK, thanks. I will apply it all branches later today as it is a data loss bug. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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] pg_upgrade failing from 9.3 to 9.4 because template0 already exists
On Fri, May 15, 2015 at 10:51:15AM -0400, Bruce Momjian wrote: On Fri, May 15, 2015 at 10:49:43AM -0400, Stephen Frost wrote: Bruce, * Bruce Momjian (br...@momjian.us) wrote: On Mon, Mar 9, 2015 at 12:43:05PM -0400, Bruce Momjian wrote: On Fri, Mar 6, 2015 at 06:10:15PM -0500, Stephen Frost wrote: The first is required or anyone who has done that will get the funny error that started this thread and things won't work anyway, but I believe the latter is also necessary to patch and back-patch as it could lead to data loss. It's not a high potential as, hopefully, people will check first, but I can imagine a hosting provider or environments where there are lots of independent clusters not catching this issue in their testing, only to discover someone set their database to 'datallowconn = false' for whatever reason and now that database is gone... Agreed. I will work on a patch for this. Attached is a patch that implements this, and it should be backpatch to all versions. Excellent and agreed. Just looked through the patch and didn't do a full review, but it looks good to me. OK, thanks. I will apply it all branches later today as it is a data loss bug. Patch applied back through 9.0. Thanks for the report and analysis. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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] to_number, to_char inconsistency.
On Sun, Feb 10, 2013 at 06:27:02PM -0500, Tom Lane wrote:
Jeremy Lowery jslow...@gmail.com writes:
I load and dump text files with currency values in it. The decimal in these
input and output formats in implied. The V format character works great for
outputing numeric data:
# select to_char(123.45, '999V99');
to_char
-
12345
(1 row)
However, when importing data, the V doesn't do the same thing:
# select to_number('12345', '999V99');
A look at the source code shows that to_number doesn't do anything at
all with the V format code, so this isn't terribly surprising. It
wouldn't be very hard to make it do the right thing, probably, but
nobody's had that particular itch yet. Feel free to scratch it and
send a patch ...
(This is for 9.6.)
I have developed the attached patch to support 'V' with to_number().
Oracle doesn't support that, so we are on our own in defining the API.
The patch doesn't handle non-whole-number strings very well as there is
no way for the user to specify decimal precision because we have
overridden the decimal digit meaning, but that seems fine to me as most
users will be using whole numbers.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
new file mode 100644
index b1e94d7..b895757
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
*** SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1
*** 6090,6101
listitem
para
!literalV/literal effectively
multiplies the input values by
literal10^replaceablen/replaceable/literal, where
replaceablen/replaceable is the number of digits following
!literalV/literal.
!functionto_char/function does not support the use of
literalV/literal combined with a decimal point
(e.g., literal99.9V99/literal is not allowed).
/para
--- 6090,6103
listitem
para
!literalV/literal with functionto_char/function
multiplies the input values by
literal10^replaceablen/replaceable/literal, where
replaceablen/replaceable is the number of digits following
!literalV/literal. literalV/literal with
!functionto_number/function divides in a similar manner.
!functionto_char/function and functionto_number/function
!do not support the use of
literalV/literal combined with a decimal point
(e.g., literal99.9V99/literal is not allowed).
/para
diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c
new file mode 100644
index 84e4db8..f615af3
*** a/src/backend/utils/adt/formatting.c
--- b/src/backend/utils/adt/formatting.c
*** numeric_to_number(PG_FUNCTION_ARGS)
*** 5047,5053
VARSIZE(value) - VARHDRSZ, 0, 0, false, PG_GET_COLLATION());
scale = Num.post;
! precision = Max(0, Num.pre) + scale;
if (shouldFree)
pfree(format);
--- 5047,5053
VARSIZE(value) - VARHDRSZ, 0, 0, false, PG_GET_COLLATION());
scale = Num.post;
! precision = Num.pre + Num.multi + scale;
if (shouldFree)
pfree(format);
*** numeric_to_number(PG_FUNCTION_ARGS)
*** 5056,5061
--- 5056,5078
CStringGetDatum(numstr),
ObjectIdGetDatum(InvalidOid),
Int32GetDatum(((precision 16) | scale) + VARHDRSZ));
+
+ if (IS_MULTI(Num))
+ {
+ Numeric x;
+ Numeric a = DatumGetNumeric(DirectFunctionCall1(int4_numeric,
+ Int32GetDatum(10)));
+ Numeric b = DatumGetNumeric(DirectFunctionCall1(int4_numeric,
+ Int32GetDatum(-Num.multi)));
+
+ x = DatumGetNumeric(DirectFunctionCall2(numeric_power,
+ NumericGetDatum(a),
+ NumericGetDatum(b)));
+ result = DirectFunctionCall2(numeric_mul,
+ result,
+ NumericGetDatum(x));
+ }
+
pfree(numstr);
return result;
}
--
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] ECPG SET CONNECTION
On Fri, May 31, 2013 at 02:26:08PM +0200, Leif Jensen wrote: Hi guys. In the ECPG manual (including latest 9.1.9) about ECPG SQL SET CONNECTION connection name; it is stated that This is not thread-aware. When looking in the ecpg library code connect.c for ECPGsetconn( ... ), it looks very much like it is thread-aware if translated with the --enable-thread-safety option. What should I believe ? Can someone comment on this report from 2013? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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] client_min_messages documentation typo
On Fri, Dec 20, 2013 at 11:29:22AM -0500, George Woodring wrote: Currently the documentation looks like: client_min_messages (enum) Controls which message levels are sent to the client. Valid values are DEBUG5, DEBUG4, DEBUG3, DEBUG2, DEBUG1, LOG, NOTICE, WARNING, ERROR, FATAL, and PANIC. Each level includes all the levels that follow it. The later the level, the fewer messages are sent. The default is NOTICE. Note that LOG has a different rank here than in log_min_messages. log_min_messages (enum) Controls which message levels are written to the server log. Valid values are DEBUG5, DEBUG4, DEBUG3, DEBUG2, DEBUG1, INFO, NOTICE, WARNING, ERROR, LOG, FATAL, and PANIC. Each level includes all the levels that follow it. The later the level, the fewer messages are sent to the log. The default is WARNING. Note that LOG has a different rank here than in client_min_messages. Only superusers can change this setting. Shouldn't client_min_messages include INFO? Sorry for the super-late reply, but INFO messages are always displayed on the client so internally INFO is marked as 'hidden' so it doesn't display as an option. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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] Documentation missing bigint?
On Thu, Dec 11, 2014 at 12:03:56PM -0800, Paul Jungwirth wrote: Hello, The table of which C types represent which SQL types seems to be missing bigint: http://www.postgresql.org/docs/9.3/static/xfunc-c.html#XFUNC-C-TYPE-TABLE It looks like bigint should be listed and should correspond to an int64 C type. Also I see there is an INT8OID, PG_GETARG_INT64, DatumGetInt64, and Int64GetDatum---I think all for bigints. Does that sound right? If so, would you like a documentation patch? Applied doc patch attached. Thanks for the report. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + diff --git a/doc/src/sgml/xfunc.sgml b/doc/src/sgml/xfunc.sgml new file mode 100644 index 9de7ccc..9c15950 *** a/doc/src/sgml/xfunc.sgml --- b/doc/src/sgml/xfunc.sgml *** memcpy(destination-data, buffer, 40); *** 1955,1960 --- 1955,1965 entryfilenameutils/nabstime.h/filename/entry /row row + entrytypebigint/type (typeint8/type)/entry + entrytypeint64/type/entry + entryfilenamepostgres.h/filename/entry + /row + row entrytypeboolean/type/entry entrytypebool/type/entry entryfilenamepostgres.h/filename (maybe compiler built-in)/entry -- 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] pg_upgrade failing from 9.3 to 9.4 because template0 already exists
On Mon, Mar 9, 2015 at 12:43:05PM -0400, Bruce Momjian wrote:
On Fri, Mar 6, 2015 at 06:10:15PM -0500, Stephen Frost wrote:
Technically, there haven't been any complaints about either pg_dumpall's
behavior in this regard, or pg_upgrade's, but pg_upgrade's post-upgrade
scripts would happily remove any databases which were marked as
'datallowconn = false' and that scares the daylights out of me. To that
end, I'd suggest patching (and back-patching) pg_upgrade to check early
on that:
template0 is set to 'datallowconn = false'
AND
all databases except template0 are set to 'datallowconn = true'
The first is required or anyone who has done that will get the funny
error that started this thread and things won't work anyway, but I
believe the latter is also necessary to patch and back-patch as it could
lead to data loss. It's not a high potential as, hopefully, people will
check first, but I can imagine a hosting provider or environments where
there are lots of independent clusters not catching this issue in their
testing, only to discover someone set their database to 'datallowconn =
false' for whatever reason and now that database is gone...
Agreed. I will work on a patch for this.
Attached is a patch that implements this, and it should be backpatch to
all versions.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
diff --git a/src/bin/pg_upgrade/check.c b/src/bin/pg_upgrade/check.c
new file mode 100644
index be66b24..5eaa67b
*** a/src/bin/pg_upgrade/check.c
--- b/src/bin/pg_upgrade/check.c
*** static void check_databases_are_compatib
*** 19,24
--- 19,25
static void check_locale_and_encoding(DbInfo *olddb, DbInfo *newdb);
static bool equivalent_locale(int category, const char *loca, const char *locb);
static void check_is_install_user(ClusterInfo *cluster);
+ static void check_proper_datallowconn(ClusterInfo *cluster);
static void check_for_prepared_transactions(ClusterInfo *cluster);
static void check_for_isn_and_int8_passing_mismatch(ClusterInfo *cluster);
static void check_for_reg_data_type_usage(ClusterInfo *cluster);
*** check_and_dump_old_cluster(bool live_che
*** 93,98
--- 94,100
* Check for various failure cases
*/
check_is_install_user(old_cluster);
+ check_proper_datallowconn(old_cluster);
check_for_prepared_transactions(old_cluster);
check_for_reg_data_type_usage(old_cluster);
check_for_isn_and_int8_passing_mismatch(old_cluster);
*** check_is_install_user(ClusterInfo *clust
*** 640,645
--- 642,699
check_ok();
}
+
+
+ static void
+ check_proper_datallowconn(ClusterInfo *cluster)
+ {
+ int dbnum;
+ PGconn *conn_template1;
+ PGresult *dbres;
+ int ntups;
+ int i_datname;
+ int i_datallowconn;
+
+ prep_status(Checking for proper database connection permissions);
+
+ conn_template1 = connectToServer(cluster, template1);
+
+ /* get database names */
+ dbres = executeQueryOrDie(conn_template1,
+ SELECT datname, datallowconn
+ FROM pg_catalog.pg_database);
+
+ i_datname = PQfnumber(dbres, datname);
+ i_datallowconn = PQfnumber(dbres, datallowconn);
+
+ ntups = PQntuples(dbres);
+ for (dbnum = 0; dbnum ntups; dbnum++)
+ {
+ char *datname = PQgetvalue(dbres, dbnum, i_datname);
+ char *datallowconn = PQgetvalue(dbres, dbnum, i_datallowconn);
+
+ if (strcmp(datname, template0) == 0)
+ {
+ /* avoid restore failure when pg_dumpall tries to create template0 */
+ if (strcmp(datallowconn, t) == 0)
+ pg_fatal(template0 must not allow connections,\n
+ i.e. its pg_database.datallowconn must be false\n);
+ }
+ else
+ {
+ /* avoid datallowconn == false databases from being skipped on restore */
+ if (strcmp(datallowconn, f) == 0)
+ pg_fatal(All non-template0 databases must allow connections,\n
+ i.e. their pg_database.datallowconn must be true\n);
+ }
+ }
+
+ PQclear(dbres);
+
+ PQfinish(conn_template1);
+
+ check_ok();
+ }
/*
--
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] Thousands of schemas and ANALYZE goes out of memory
On Wed, May 13, 2015 at 06:23:58PM -0400, Tom Lane wrote: Bruce Momjian br...@momjian.us writes: On Wed, May 13, 2015 at 06:10:26PM -0400, Tom Lane wrote: Bruce Momjian br...@momjian.us writes: Is there a reason the following patch wasn't applied? I don't think anybody ever did the legwork to verify it was a good idea. In particular, it'd be good to check if sending a tabstat message for each table adds noticeable overhead. OK, I will mark it as closed then. Thanks. I don't know that it should be closed exactly --- if we don't do this, we should do something else about the performance issue. Maybe put it on TODO? Thanks, TODO added: Reduce memory use when analyzing many tables (This is part of my clean up of old issues in preparation for 9.5 feature freeze.) -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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] Thousands of schemas and ANALYZE goes out of memory
On Wed, May 13, 2015 at 06:10:26PM -0400, Tom Lane wrote: Bruce Momjian br...@momjian.us writes: Is there a reason the following patch wasn't applied? I don't think anybody ever did the legwork to verify it was a good idea. In particular, it'd be good to check if sending a tabstat message for each table adds noticeable overhead. OK, I will mark it as closed then. Thanks. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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] SELECT INTO and ON COMMIT
On Wed, May 13, 2015 at 06:05:43PM -0600, Yves Dorfsman wrote: On Wed, May 13, 2015 at 05:29:36PM -0600, Yves Dorfsman wrote: Is there any way to add an ON COMMIT clause to a SELECT INTO TEMP TABLE? On 2015-05-13 17:56, David G. Johnston wrote: From the documentation of SELECT INTO The PostgreSQL usage of SELECT INTO to represent table creation is historical. It is best to use CREATE TABLE AS for this purpose in new code. http://www.postgresql.org/docs/9.4/interactive/sql-createtableas.html Which effectively means consider the feature deprecated. Especially since CREATE TABLE is standard conforming and SELECT INTO is not. Ah! This works. Thanks. Will `SELECT INTO` be deprecated? It is very convenient when writing pgplsql functions, to select into a record. The pl/psql `SELECT INTO` is not related to the SQL SELECT INTO command --- yeah, confusing, so no, the pl/psql ability is not deprecated. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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] Thousands of schemas and ANALYZE goes out of memory
Is there a reason the following patch wasn't applied?
---
On Thu, Oct 4, 2012 at 07:14:31PM -0400, Tom Lane wrote:
Jeff Janes jeff.ja...@gmail.com writes:
For the record, the culprit that causes analyze; of a database with
a large number of small objects to be quadratic in time is
get_tabstat_entry and it is not fixed for 9.3.
I was a bit surprised by this assertion, as I'd thought that tabstats
were flushed to the collector at transaction end, and thus that the
internal transaction boundaries in a VACUUM or ANALYZE should prevent
the tabstats table from getting unreasonably large. However, a look
at the code shows that pgstat_report_stat() is only called when the main
loop in postgres.c is about to wait for client input.
We could build a lot of infrastructure to try to index the tabstat
arrays more efficiently ... or we could just do something like the
attached.
It appears that the next tallest mole in the VACUUM case is
CleanupTempFiles. This workload is not creating any temp files, I hope,
so the implication is that have_pending_fd_cleanup is getting set by
FileSetTransient (probably from blind writes). We might want to revisit
how that works --- particularly since I see no reason that there would
be any actually-blind writes in this example. But in any case, that
innocent looking flag setting can result in a lot of work.
regards, tom lane
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 14d1c08..a5d00fc 100644
*** a/src/backend/commands/vacuum.c
--- b/src/backend/commands/vacuum.c
*** vacuum(VacuumStmt *vacstmt, Oid relid, b
*** 251,256
--- 251,257
{
PopActiveSnapshot();
CommitTransactionCommand();
+ pgstat_report_stat(false);
}
}
}
*** vacuum_rel(Oid relid, VacuumStmt *vacstm
*** 1071,1080
relation_close(onerel, NoLock);
/*
! * Complete the transaction and free all temporary memory used.
*/
PopActiveSnapshot();
CommitTransactionCommand();
/*
* If the relation has a secondary toast rel, vacuum that too while we
--- 1072,1083
relation_close(onerel, NoLock);
/*
! * Complete the transaction and free all temporary memory used. Also,
! * flush table-access statistics to the stats collector after each
table.
*/
PopActiveSnapshot();
CommitTransactionCommand();
+ pgstat_report_stat(false);
/*
* If the relation has a secondary toast rel, vacuum that too while we
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
--
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] SELECT INTO and ON COMMIT
On Wed, May 13, 2015 at 05:29:36PM -0600, Yves Dorfsman wrote:
Is there any way to add an ON COMMIT clause to a SELECT INTO TEMP TABLE?
Well CREATE TABLE has a ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP }
clause, but I don't see it in SELECT INTO, so it seems you have to
create the temp table using CREATE TABLE, then INSERT ... SELECT.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
--
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] Getting a leading zero on negative intervals with to_char?
On Thu, Sep 20, 2012 at 09:42:33AM +0800, Craig Ringer wrote:
Hi all
I'm wondering if there's any way to convince `to_char` to add a leading zero
to
the hours in negative intervals. The current behaviour feels wrong, in that
FMHH24:MM and HH24:MM produce the same output for negative intervals:
regress=# WITH x(i) AS (VALUES (INTERVAL '9:00'),(INTERVAL '-9:00'),
(INTERVAL '11:00'),(INTERVAL '-11:00'),(INTERVAL '101:00'),(INTERVAL
'-101:00') )
SELECT i as interval, to_char(i,'HH24:MM') as HH24:MM, to_char
(i,'FMHH24:MM') AS FMHH24:MM FROM x;
interval | HH24:MM | FMHH24:MM
+-+---
09:00:00 | 09:00 | 9:00
-09:00:00 | -9:00 | -9:00
11:00:00 | 11:00 | 11:00
-11:00:00 | -11:00 | -11:00
101:00:00 | 101:00 | 101:00
-101:00:00 | -101:00 | -101:00
(6 rows)
I can't find any way to produce the output '-09:00' . There's no apparent way
to add an additional width-specifier. HH24 is clearly not constrained to be 2
digits wide, since -11 and 101 and -101 are all output by HH24. It
seems like -9 should be -09 with the HH24 specifier, and -9 with the
FMHH24 specifier.
Opinions?
Unless I'm doing something woefully wrong, Oracle compatibility doesn't seem
to
be an issue because we format intervals wildly differently to Oracle anyway:
http://sqlfiddle.com/#!4/d41d8/2751
and it looks like Oracle handling of intervals isn't much like Pg anyway:
http://stackoverflow.com/questions/970249/format-interval-with-to-char
Arose from trying to find a non-ugly solution to this SO post:
http://stackoverflow.com/questions/12335438/server-timezone-offset-value/
12338490#12338490
[This is for 9.6.]
I looked over this report from 2012, and the behavior still exists. I
think we have not seen more reports about this because negative
hours/years is not something people regularly use, but you found a need
for it.
I think the big question is whether (4) or HH24 (2) represents
characters. or digits for zero-padding. printf() assumes it is
characters, e.g. %02d outputs -2 not -02, but I think our API
suggests it is digits, meaning the minus sign is not part of the
specific length, i.e. a minus sign is not a digit.
I have developed the attached unified-diff patch which changes the
behavior to not consider the negative sign as a digit in all the places
I thought it was reasonable.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
diff --git a/src/backend/utils/adt/formatting.c b/src/backend/utils/adt/formatting.c
index 84e4db8..1005c52 100644
--- a/src/backend/utils/adt/formatting.c
+++ b/src/backend/utils/adt/formatting.c
@@ -2426,7 +2426,7 @@ DCH_to_char(FormatNode *node, bool is_interval, TmToChar *in, char *out, Oid col
* display time as shown on a 12-hour clock, even for
* intervals
*/
-sprintf(s, %0*d, S_FM(n-suffix) ? 0 : 2,
+sprintf(s, %0*d, S_FM(n-suffix) ? 0 : (tm-tm_hour = 0) ? 2 : 3,
tm-tm_hour % (HOURS_PER_DAY / 2) == 0 ? HOURS_PER_DAY / 2 :
tm-tm_hour % (HOURS_PER_DAY / 2));
if (S_THth(n-suffix))
@@ -2434,19 +2434,22 @@ DCH_to_char(FormatNode *node, bool is_interval, TmToChar *in, char *out, Oid col
s += strlen(s);
break;
case DCH_HH24:
-sprintf(s, %0*d, S_FM(n-suffix) ? 0 : 2, tm-tm_hour);
+sprintf(s, %0*d, S_FM(n-suffix) ? 0 : (tm-tm_hour = 0) ? 2 : 3,
+ tm-tm_hour);
if (S_THth(n-suffix))
str_numth(s, s, S_TH_TYPE(n-suffix));
s += strlen(s);
break;
case DCH_MI:
-sprintf(s, %0*d, S_FM(n-suffix) ? 0 : 2, tm-tm_min);
+sprintf(s, %0*d, S_FM(n-suffix) ? 0 : (tm-tm_min = 0) ? 2 : 3,
+ tm-tm_min);
if (S_THth(n-suffix))
str_numth(s, s, S_TH_TYPE(n-suffix));
s += strlen(s);
break;
case DCH_SS:
-sprintf(s, %0*d, S_FM(n-suffix) ? 0 : 2, tm-tm_sec);
+sprintf(s, %0*d, S_FM(n-suffix) ? 0 : (tm-tm_sec = 0) ? 2 : 3,
+ tm-tm_sec);
if (S_THth(n-suffix))
str_numth(s, s, S_TH_TYPE(n-suffix));
s += strlen(s);
@@ -2503,7 +2506,8 @@ DCH_to_char(FormatNode *node, bool is_interval, TmToChar *in, char *out, Oid col
break;
case DCH_OF:
INVALID_FOR_INTERVAL;
-sprintf(s, %+0*d, S_FM(n-suffix) ? 0 : 3, (int) tm-tm_gmtoff / SECS_PER_HOUR);
+sprintf(s, %+0*d, S_FM(n-suffix) ? 0 : (tm-tm_gmtoff = 0) ? 3 : 4,
+ (int) tm-tm_gmtoff / SECS_PER_HOUR);
s += strlen(s);
if ((int) tm-tm_gmtoff % SECS_PER_HOUR != 0)
{
@@ -2653,7 +2657,8 @@ DCH_to_char(FormatNode *node, bool is_interval, TmToChar *in, char *out, Oid col
s += strlen(s);
break;
case DCH_MM:
-sprintf(s, %0*d, S_FM(n-suffix) ? 0 : 2, tm-tm_mon);
+sprintf(s, %0*d, S_FM(n-suffix) ? 0 : (tm-tm_mon = 0) ? 2 : 3,
+ tm-tm_mon);
if (S_THth(n-suffix))
str_numth(s, s, S_TH_TYPE(n-suffix
Re: [GENERAL] Upgrading hot standbys
On Wed, Apr 29, 2015 at 01:13:13PM +0200, Magnus Hagander wrote: On Wed, Apr 29, 2015 at 6:19 AM, Aaron Burnett aaron.burn...@us.dunnhumby.com wrote: Greetings, I'm in the process of upgrading PG 9.1.3 to 9.4.1 in the near future. I have several machines which each house unique databases. Each of those are replicated to a standby server with matching configurations. A total of 10 servers, 5 masters, 5 slaves. Everything runs on Ubuntu. My question, as I can't seem to find any documentation on this part, is once I successfully upgrade the master I will need to upgrade the standby as well. Will I have to rebuild the standby from scratch, or will the standby pick up where it was before the upgrade if I do things correctly? You upgrade the master, and then you rebuild the standbys from a new basebackup (using pg_basebackup or manually with start/stop backups etc). You can't upgrade the standbys and have them re-join the master, they have to be redone from scratch. Well, 9.5's pg_upgrade manual page has instructions on upgrading streaming standbys via rsync, and it will work for all versions of pg_upgrade back to 9.0: http://www.postgresql.org/docs/devel/static/pgupgrade.html Let me know if you have any problems. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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] Documentation Inaccuracy – Transaction Isolation
On Tue, Apr 28, 2015 at 08:00:24PM +, Nicholson, Brad (Toronto, ON, CA) wrote: Hi, I noticed an inaccuracy in the transaction isolation docs. Under the Repeatable Read Isolation Level section it states: “The Repeatable Read isolation level only sees data committed before the transaction began; it never sees either uncommitted data or changes committed during transaction execution by concurrent transactions.” That is not entirely accurate. The snapshot starts with the first SQL statement in the transaction, not at the start of the transaction. Any change that is committed in another transaction after the start of the transaction but before the first SQL statement will be seen. Yes, we have fixed that for PG 9.5: http://www.postgresql.org/docs/devel/static/transaction-iso.html This level is different from Read Committed in that a query in a -- repeatable read transaction sees a snapshot as of the start of the first -- non-transaction-control statement in the transaction, not as of the start of the current statement within the transaction. Thus, successive SELECT commands within a single transaction see the same data, i.e., they do not see changes made by other transactions that committed after their own transaction started. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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] Running pg_upgrade under Debian
On Mon, Apr 20, 2015 at 03:02:48PM -0700, Adrian Klaver wrote: But pg_upgrade supports tablespaces, and I assume pg_dump/pg_restore do as well. I don't think it is about the underlying programs, it is about teaching the wrapper script what do with the choices. Sort of like pgAdmin not supporting all pg_backup/pg_restore combinations or for that matter pg_restore not knowing what to do with a plain text pg_dump file. Understoo, but I was not aware there was anything special required for pg_upgrade to support tablespaces. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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] Running pg_upgrade under Debian
On Mon, Apr 20, 2015 at 07:06:37PM -0300, Alvaro Herrera wrote: ISTM there's a documentation bug here: in the code, the dump method checks for tablespaces and raises an error if they are found, but the upgrade method does not check. I think the documentation should state that only the dump method does not support tablespaces. OK, it would be nice if someone could report that to upstream Debian. I am a little confused why pg_dump/pg_restore can't use tablespaces though. Years ago we used to not use PG-major-version-specific subdirectories in tablespaces, but we added that for pg_upgrade, and I am sure they would work fine for pg_dump too. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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] Running pg_upgrade under Debian
On Mon, Apr 20, 2015 at 02:41:09PM -0700, Adrian Klaver wrote: On 04/20/2015 12:49 PM, Bruce Momjian wrote: On Sat, Apr 18, 2015 at 09:08:20AM +1000, rob stone wrote: For what it's worth: Debian provides a pg_upgradecluster tailored to its specific setup of PostgreSQL clusters. That has worked well for me across several major version bumps. Karsten Indeed I have that program installed in /usr/lib but the man pages state that it cannot handle tablespaces. Wow, that is odd. I wonder why. Best guess is because pg_upgradecluster is a wrapper script that by default uses pg_dump/pg_restore. Using is pg_upgrade is the second choice. http://manpages.ubuntu.com/manpages/trusty/man8/pg_upgradecluster.8.html But pg_upgrade supports tablespaces, and I assume pg_dump/pg_restore do as well. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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] Running pg_upgrade under Debian
On Sat, Apr 18, 2015 at 09:08:20AM +1000, rob stone wrote: For what it's worth: Debian provides a pg_upgradecluster tailored to its specific setup of PostgreSQL clusters. That has worked well for me across several major version bumps. Karsten Indeed I have that program installed in /usr/lib but the man pages state that it cannot handle tablespaces. Wow, that is odd. I wonder why. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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] pg_upgrade failing from 9.3 to 9.4 because template0 already exists
On Fri, Mar 6, 2015 at 06:10:15PM -0500, Stephen Frost wrote: Technically, there haven't been any complaints about either pg_dumpall's behavior in this regard, or pg_upgrade's, but pg_upgrade's post-upgrade scripts would happily remove any databases which were marked as 'datallowconn = false' and that scares the daylights out of me. To that end, I'd suggest patching (and back-patching) pg_upgrade to check early on that: template0 is set to 'datallowconn = false' AND all databases except template0 are set to 'datallowconn = true' The first is required or anyone who has done that will get the funny error that started this thread and things won't work anyway, but I believe the latter is also necessary to patch and back-patch as it could lead to data loss. It's not a high potential as, hopefully, people will check first, but I can imagine a hosting provider or environments where there are lots of independent clusters not catching this issue in their testing, only to discover someone set their database to 'datallowconn = false' for whatever reason and now that database is gone... Agreed. I will work on a patch for this. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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] pg_upgrade failing from 9.3 to 9.4 because template0 already exists
On Mon, Mar 9, 2015 at 12:43:05PM -0400, Bruce Momjian wrote: On Fri, Mar 6, 2015 at 06:10:15PM -0500, Stephen Frost wrote: Technically, there haven't been any complaints about either pg_dumpall's behavior in this regard, or pg_upgrade's, but pg_upgrade's post-upgrade scripts would happily remove any databases which were marked as 'datallowconn = false' and that scares the daylights out of me. To that end, I'd suggest patching (and back-patching) pg_upgrade to check early on that: template0 is set to 'datallowconn = false' AND all databases except template0 are set to 'datallowconn = true' The first is required or anyone who has done that will get the funny error that started this thread and things won't work anyway, but I believe the latter is also necessary to patch and back-patch as it could lead to data loss. It's not a high potential as, hopefully, people will check first, but I can imagine a hosting provider or environments where there are lots of independent clusters not catching this issue in their testing, only to discover someone set their database to 'datallowconn = false' for whatever reason and now that database is gone... Agreed. I will work on a patch for this. Oh, also, thanks for the analysis on this --- you are spot-on. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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] Sequences not moved to new tablespace
On Tue, Feb 24, 2015 at 10:32:42AM -0500, Tom Lane wrote: For implementation reasons, ALTER DATABASE SET TABLESPACE refuses the case where the database already has some tables that have been explicitly placed into that tablespace. (I forget the exact reason for this, but it's got something to do with needing to preserve a distinction between tables that have had a tablespace explicitly assigned and those that are just inheriting the database's default tablespace.) So the best bet at this point seems to be to move everything back to the database's original tablespace and then use ALTER DATABASE SET TABLESPACE. FYI, I added docs for this to the 9.5 ALTER DATABASE manual page: The fourth form changes the default tablespace of the database. Only the database owner or a superuser can do this; you must also have create privilege for the new tablespace. This command physically moves any tables or indexes in the database's old default tablespace to the new tablespace. The new default tablespace must be empty for this database, and no one can be connected to the database. Tables and indexes in non-default tablespaces are unaffected. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
