Re: [GENERAL] pg on Debian servers
On 12/11/17 19:15, Karsten Hilbert wrote: On Sat, Nov 11, 2017 at 01:03:18PM +, Mark Morgan Lloyd wrote: Several legacy programs written in Delphi ground to a halt this morning, which turned out to be because a Debian system had updated its copy of PostgreSQL and restarted the server, which broke any live connections. At least some versions of Delphi, not to mention other IDE/RAD tools with database-aware components, don't automatically try to reestablish a database session that's been interrupted. In any event, an unexpected server restart (irrespective of all investment in UPSes etc.) has the potential of playing havoc on a clustered system. Is there any way that either the package maintainer or a site administrator/programmer such as myself can mark the Postgres server packages as "manual upgrade only" or similar? Or since I'm almost certainly not the first person to be bitten by this, is there a preferred hack in mitigation? Apart from that (putting packages on hold), PostgreSQL updates on Debian don't upgrade existing clusters automatically. They do create a new cluster but the old one is kept around and stays running, IIRC even on the very same port. (Having gone all the way from PG 7.1 to PG 10 on Debian :) With the caveat that Debian has only comparatively-recently introduced unattended updates as the default... I think only with Stretch. If you're still on Jessie you can yet be saved :-) What did pg_lsclusters say ? I don't have it from the time of the problem, but currently it gives me Ver Cluster Port Status OwnerData directory Log file 9.6 main5432 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log i.e. a single-server system, although I've since done a manual restart so that I could change some DIMMs. However syslog and postgresql-9.6-main.log show me this: Nov 11 06:27:38 postgres1 systemd[1]: Starting Daily apt upgrade and clean activities... Nov 11 06:28:05 postgres1 systemd[1]: Reloading. Nov 11 06:28:07 postgres1 systemd[1]: Reloading. Nov 11 06:28:07 postgres1 systemd[1]: Stopped PostgreSQL RDBMS. Nov 11 06:28:07 postgres1 systemd[1]: Stopping PostgreSQL Cluster 9.6-main... Nov 11 06:28:08 postgres1 systemd[1]: Stopped PostgreSQL Cluster 9.6-main. Nov 11 06:28:10 postgres1 systemd[1]: Reloading. 2017-11-11 06:28:07.587 UTC [675] LOG: received fast shutdown request 2017-11-11 06:28:07.587 UTC [675] LOG: aborting any active transactions [Session names here] 2017-11-11 06:28:07.607 UTC [730] LOG: autovacuum launcher shutting down [More session names here] 2017-11-11 06:28:07.680 UTC [727] LOG: shutting down 2017-11-11 06:28:07.984 UTC [675] LOG: database system is shut down 2017-11-11 06:28:13.039 UTC [11122] LOG: database system was shut down at 2017-11-11 06:28:07 UTC 2017-11-11 06:28:13.081 UTC [11122] LOG: MultiXact member wraparound protections are now enabled 2017-11-11 06:28:13.085 UTC [11126] LOG: autovacuum launcher started 2017-11-11 06:28:13.085 UTC [11121] LOG: database system is ready to accept connections 2017-11-11 06:28:13.371 UTC [11128] [unknown]@[unknown] LOG: incomplete startup packet All live applications saw that as a loss of database connectivity, yet when I was alerted by their squeals of anguish (MIDI on app servers has its uses :-) I found the database server running and accepting connections. There must have been something additional at play. The apps are written in Delphi, I admit not a very recent version and they're due to be converted to Lazarus which is an open-source and portable clone. I'll defend my choice of language since it is, basically, the best "4GL" you'll find. However one flaw of Delphi etc. is that they assume that they can safely hold a database session open for an extended period. I can't speak for Delphi any more since it has, basically, priced itself out of our league particularly taking into account its lack of portability, but FPC/Lazarus appears to have something which is intended to reconnect a lost session, although it's so far unimplemented. So I've got multiple options for fixing this at the application level: either fill in the unimplemented bit of the database control in the Lazarus Class Library, or prevent apps from holding database connections open. But the real problem, I feel, is that Debian is enabling unattended upgrades without checking with the user, and while an attended upgrade normally asks for confirmation before restarting a daemon an unattended one doesn't. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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 on Debian servers
On 11/11/17 16:45, Jan Claeys wrote: On Sat, 2017-11-11 at 14:23 +, Mark Morgan Lloyd wrote: I think that the "preventing upgrades" route is the one to follow, since inhibiting the restart would obviously present a risk that something loaded dynamically could get out of step. As an at least temporary hack I've disabled unattended updates using # systemctl disable unattended-upgrades.service Unattended-upgrades is configurable and allows whitelisting package origins, as well as blacklisting packages so that they never get upgraded automatically (you can still upgrade them manually, of course). See /etc/apt/apt.conf.d/50unattended-upgrades (the default version of that file includes documentation as comments). Also see the unattended-upgrade(8) manpage, and the on/off switch in /etc/apt/apt.conf.d/20auto-upgrades Thanks Jan, noted. I was, of course, working to a fairly traditional priority: get things running again, whine for a few hours, and only later implement a proper fix :-) -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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 on Debian servers
On 11/11/17 13:45, Christoph Berg wrote: Re: Magnus Hagander 2017-11-11
[GENERAL] pg on Debian servers
Apologies for something which is distro related, but I was bitten by a "silly mistake"- one of my own, I hasten to say- earlier. Several legacy programs written in Delphi ground to a halt this morning, which turned out to be because a Debian system had updated its copy of PostgreSQL and restarted the server, which broke any live connections. At least some versions of Delphi, not to mention other IDE/RAD tools with database-aware components, don't automatically try to reestablish a database session that's been interrupted. In any event, an unexpected server restart (irrespective of all investment in UPSes etc.) has the potential of playing havoc on a clustered system. Is there any way that either the package maintainer or a site administrator/programmer such as myself can mark the Postgres server packages as "manual upgrade only" or similar? Or since I'm almost certainly not the first person to be bitten by this, is there a preferred hack in mitigation? -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Systemd support (was:Re: [GENERAL] Please say it isn't so)
On 12/07/17 05:00, Steve Litt wrote: Hi all, Please tell me this is a mistake: https://wiki.postgresql.org/wiki/Systemd Why a database system should care about how processes get started is beyond me. Systemd is an entangled mess that every year subsumes more and more of the operating system, in a very non-cooperative way. There are almost ten init systems. In every one of those init systems, one can run a process supervisor, such as runit or s6 or daemontools-encore, completely capable of starting the postgres server. Every year, systemd further hinders interoperability, further erodes interchangeability of parts, and continues to address problems with WONTFIX. In the long run, you do your users no favor by including init-system specific code in Postgres or its makefiles. If systemd can't correctly start Postgres, I guarantee you that s6 or runit, running on top of systemd, can. Postgres doesn't care which language makes a query to it. Why should Postgres care which init system started it? I hope you can free Postgres of init-specific code, and if for some reason you can't do that, at least don't recommend init-specific code. OTOH since systemd is what's being supported by a significant number of distributions it makes sense to at least try to work robustly with it. While my preference would have been to have made such a change at a major version transition, the reality is that database systems are competitive, and not keeping up with the underlying platform would have been very much to PostgreSQL's disadvantage, OP: Please note that you do yourself no favours at all by posting a subject line which could very easily be misinterpreted as spam. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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 on eMMC - Corrupt file system
On 09/02/17 23:00, Christoph Moench-Tegeder wrote: ## Thomas Güttler (guettl...@thomas-guettler.de): Is running linux with postgres on eMMC a bad idea in general? I'd say that running anything with a read-write load on eMMC will end in pieces. It's ok to occasionally write something, but a mixed load is not really what these things were designed for. The wear leveling can be quite basic, you never know when it's gonna happen (i.e. sudden power down can kill your filesystem - that's why disabling journaling is not a very great idea), and if your device is "mostly full" anyways, the wear leveling has not much space to redirect the writes to. Remember that some of those chips are sold mostly by price - that is, the hobbyist "embedded" devices get the cheapest chips. A safer bet would be adding an external storage; some 64GB SATA SSDs are available for less than 50€ (perhaps it's better not to go for the cheapest ones here, too). I agree, but three additional comments. First, we've got a fair number of RPis running their root filesystems on the internal SD-Card without problems, but the one Odroid which runs an eMMC card failed a few weeks ago. Second, a useful precaution is to put stuff which will be updated on an external device, although the same longevity concerns apply if it's Flash-based. Third, experience here suggests that reliability /might/ be improved if you fully zero a device before partitioning it to make absolutely sure that the internal controller has touched every block. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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] Regrding:- Arduino-Postgresql Direct Connection
On 09/12/16 21:30, John R Pierce wrote:
On 12/8/2016 8:56 PM, Roshan Jha wrote:
In my project ,I have to send the data from the arduino to the direct
postgresql for storing .So i want know that is it possible to make
direct connection between arduino to postgresql ,If yes, then let me
know, how should i do.
arduinos don't even have native networking, do they? so you'll need an
ethernet adapter, and someone's tcp/ip libraries to even get data off it...
it might be easier to do this on a raspberry pi... they run a small
linux system natively, and have ethernet built in, the pi3 has
integrated wifi, too.
To talk to postgresql directly, you would need a libpq compiled for
arduino, this compilation, while theoretically possible quite likely
would be rather tricky.
instead, I'd suggest implementing a front end, perhaps RESTful, that
runs on an application server, either on the postgres database server or
another nearby server, and have the arduino send the data to it, this
front end would format the data and insert it into the postgres
database. keep the app server API at a simple application-oriented
level ('GET record', 'PUT record' kind of thing...) so the arduino code
can be kept quite simple.
I really don't think an Arduino is the tool for this job, but I've known
plenty of people who'd try to make do... and wouldn't know when to give up.
I'd add my vote for Raspberry Pi here, if the OP has an unavoidable need
to use Arduino "shields" there's an interface called the Raspduino which
would help.
--
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk
[Opinions above are the author's, not those of his employers or colleagues]
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PostgreSQL and ArcGIS used in UK military exercise
Potentially useful publicity. http://www.theregister.co.uk/2016/10/28/unmanned_warrior_esri_argcis_cloud_based_mapping/ -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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 driver for mysql
On 05/09/16 18:46, Mimiko wrote: Hello to all. I want to move applications to postgres. But there are applications which can use only mysql or local mdb or mssql. For now I run a mysql server to which this applications are connected. Long time ago I've encountered a federeted module for mysql to allow to store data in postgres. Now that site doesnot exists. Have you checked archive.org? At the very least that would give you things like developers names that you can follow up, since there might be a continuation project. We certainly can't do that for you, since you haven't given us a URL or told us the name of the module. Can anyone suggest a db federeted plugin for mysql/mariadb to store data in pg. Changing applications is impossible, they are proprietary and work only with specific databases only. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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] Stored procedure version control
Mark Morgan Lloyd wrote: Elsewhere, somebody was asking how people implemented version control for stored procedures on (MS) SQL Server. The consensus was that this is probably best managed by using scripts or command files to generate stored procedures etc., but does anybody have any comment on that from the POV of PostgreSQL? For completeness although this appears to be MS-specific, somebody has drawn my attention to http://www.red-gate.com/products/sql-development/sql-source-control/ -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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] Stored procedure version control
Mike Sofen wrote: -Original Message- From: Mark Morgan Lloyd Sent: Thursday, June 30, 2016 2:41 AM Neil Anderson wrote: On 2016-06-29 12:37 PM, Mark Morgan Lloyd wrote: Elsewhere, somebody was asking how people implemented version control for stored procedures on (MS) SQL Server. The consensus was that this is probably best managed by using scripts or command files to generate stored procedures etc., but does anybody have any comment on that from the POV of PostgreSQL? [etc.] Thanks everybody, summary passed on. Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk A bit late to the thread, but here's some specific details on how I've implemented version control in PG 9.5, in a small team environment deploying to single database servers in each tier (dev, qa, stage, prod). It's working well so far, and allows my stored proc versions to be aligned with the middle and upper tier code releases. I'm the lead database architect-engineer for a brand new genomics application (lots of data). Thanks Mike, I'll pass that on if the thread on CIX still looks live. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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] Stored procedure version control
Neil Anderson wrote: On 2016-06-29 12:37 PM, Mark Morgan Lloyd wrote: Elsewhere, somebody was asking how people implemented version control for stored procedures on (MS) SQL Server. The consensus was that this is probably best managed by using scripts or command files to generate stored procedures etc., but does anybody have any comment on that from the POV of PostgreSQL? I can't comment from the POV of those who represent Postgres, but I used to work for a company who specialised in change management for database products, SQL Server and Oracle in particular. There are at least two approaches. The migrations approach and the state based approach. [etc.] Thanks everybody, summary passed on. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Stored procedure version control
Elsewhere, somebody was asking how people implemented version control for stored procedures on (MS) SQL Server. The consensus was that this is probably best managed by using scripts or command files to generate stored procedures etc., but does anybody have any comment on that from the POV of PostgreSQL? -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] System aggregation
ScaleMP apparently has a facility where a single system image can be spread over multiple host computers, possibly with guest paravirtualisation, although the non-priced variant only supports memory aggregation. http://www.scalemp.com/products/product-comparison/ There also used to be something called Virtual Iron (possibly based on Xen), but it was swallowed by Oracle where it was under the wing of Wim Coekaerts who I believe has just joined Microsoft. Has anybody ever experimented with PostgreSQL on this sort of thing, and is anybody aware of an open equivalent? -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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
Jernigan, Kevin wrote: On 3/25/16, 4:37 AM, "pgsql-general-ow...@postgresql.org on behalf of Mark Morgan Lloyd" <pgsql-general-ow...@postgresql.org on behalf of markmll.pgsql-gene...@telemetry.co.uk> wrote: Just because a corporate has a hundred sites cooperating for inventory management doesn't mean that the canteen menus have to be stored on Oracle RAC :-) Right, but often the customer has paid for a site license, in which case the IT department will just keep spinning up more Oracle (or SQL Server or DB2) databases when requests come in - even if it’s overkill for the proposed use case / workload, it’s less work if IT only has one database technology to support. OTOH, if the license takes the number of CPUs/cores into account then adding even unsophisticated unrelated databases will, eventually, cost. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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
Jernigan, Kevin wrote: On 3/22/16, 8:07 AM, "Bruce Momjian" <br...@momjian.us> wrote: 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 + Implementing RAC-equivalent functionality is extremely hard, as evidenced by the lack of any directly comparable capability from any other relational db engine, until the release of IBM DB2 Shareplex a few years ago. And given the improvement of PostgreSQL and other open source solutions over the past 20 years, it’s not clear that it makes sense to go through the initial design and implementation work and then the ongoing maintenance overhead - most of what RAC provides can be achieved through other existing capabilities. Hearing what IBM's strong points are is always useful, since the various flavours of DB2 obviously have facilities to which other databases should aspire. As with Oracle, DB2's strong points aren't really well-publicised, and things are further complicated by the variant terminology which IBM has evolved over the half century they've been building mainframes. While I’m not sure that the percentage breakdowns in your chart are totally accurate, I agree with the general assessment, except for the highest-end applications which have zero-downtime requirements which can’t be met with streaming replication: the overhead of synchronous replication limits scalability, and the failover time for moving from primary to a failover target is significantly slower than RAC - which can be literally zero if configured correctly. The higher-level point that I think is important is that while I may be able to win technical arguments that RAC is better for certain high-end extreme workloads - and maybe I can’t even win those arguments ;-) - the real issue is that there aren’t very many of those workloads, and the PostgreSQL community shouldn’t care: the vast majority of Oracle (and SQL Server etc) workloads don’t need all the fancy high-end RAC capabilities, or many of the other high-end commercial database capabilities. And those workloads can relatively easily be migrated to PostgreSQL, with minor disruption / change to schemas, data, triggers, constraints, procedural SQL… What I've seen so far suggests that if MS is positioning SQL Server to challenge Oracle, it's basically looking for low-hanging fruit: in particular supplementary databases which corporates have put onto Oracle out of habit but which quite simply don't need some of the higher-end facilities for which Oracle is harvesting revenue. Just because a corporate has a hundred sites cooperating for inventory management doesn't mean that the canteen menus have to be stored on Oracle RAC :-) -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PostgreSQL advocacy
If anybody puts together a "just the facts" document after Oracle's attack on PostgreSQL in Russia, please make sure it's drawn to the attention of this mailing list for the benefit of those who aren't in -advocacy. I was discussing this sort of thing elsewhere in the context of MS's apparent challenge to Oracle and IBM, and the dominant feeling appeared to be that actual use of things like Oracle RAC was vanishingly uncommon. Which surprised me, and which I'm treating with caution since the fact that facilities aren't used (in a certain population of developers etc.) can in no way be interpreted as meaning that the technology is not unavailable or unreliable. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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] "Web of trust" connections
Jim Nasby wrote: On 11/6/15 8:01 AM, Mark Morgan Lloyd wrote: Purely out of curiosity, is there any way of using some sort of "web of trust" (comparable with GPG or whatever) when verifying server and client certificates, rather than going back to a centralised CA? My apologies if this is a silly question, or if there are fundamental reasons why such a thing would be inappropriate. My scenario is that I'm looking at multiple PostgreSQL servers (with supporting custom software) arranged (approximately) as a tree, with nodes sending notifications to their peers as they see changes. I want to make it as easy as possible to set up a new server and get it cooperating with the rest, and some sort of WoT might be plausible rather than having to wait for the root administrator to send keys over a secure channel. Postgres does support PAM, so you might be able to craft such a solution using that along with something that support WoT (like GPG). Thanks for that Jim, very interesting suggestion. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] "Web of trust" connections
Purely out of curiosity, is there any way of using some sort of "web of trust" (comparable with GPG or whatever) when verifying server and client certificates, rather than going back to a centralised CA? My apologies if this is a silly question, or if there are fundamental reasons why such a thing would be inappropriate. My scenario is that I'm looking at multiple PostgreSQL servers (with supporting custom software) arranged (approximately) as a tree, with nodes sending notifications to their peers as they see changes. I want to make it as easy as possible to set up a new server and get it cooperating with the rest, and some sort of WoT might be plausible rather than having to wait for the root administrator to send keys over a secure channel. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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] Red Hat Policies Regarding PostgreSQL
Tom Lane wrote: Michael Convey <smcon...@gmail.com> writes: Due to policies for Red Hat family distributions, the PostgreSQL installation will not be enabled for automatic start or have the database initialized automatically. To which policies are they referring? Licensing, security, or other?​ Packaging policy: daemons shall not run merely by virtue of having been installed. Otherwise, if you install a boatload of software without checking each package, you'd have a boatload of probably-unwanted and possibly-incorrectly-configured daemons running. Which is a performance problem and likely a security hazard too. It's a good policy IMO (though I used to work there so no doubt I've just drunk too much Red Hat koolaid). Seems reasonable. In fact somewhat better than current KDE as in e.g. Debian "Jessie", which embeds a copy of MySQL whether the the user wants to use it or not. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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] Listen/notify, equivalents on other DBs
Thomas Kellerer wrote: Mark Morgan Lloyd schrieb am 25.09.2015 um 23:41: I'm trying to get support for PostgreSQL's listen/notify into a development environment, but since it supports multiple database backends: can anybody comment on how many other servers have a comparable facility? Minimal research has allowed me to code something for Firebird/Interbase, but I'm not in a position to investigate Oracle, IBM, MS and the rest. I'd appreciate any general comments from somebody who has broad SQL experience, I'm not asking for example code. For Oracle this would be the "change notification" feature which goes beyond what Postgres can do if I'm not mistaken as you can register a query and the client is notified if the result of that query would change: http://docs.oracle.com/database/121/JJDBC/dbchgnf.htm#JJDBC28815 Thanks for that, very useful. There's minimal info on the Firebird/Interbase equivalent at http://www.firebirdsql.org/file/documentation/papers_presentations/Power_Firebird_events.pdf but my recollection is that it's very lightweight- no useful payload. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Listen/notify, equivalents on other DBs
I'm trying to get support for PostgreSQL's listen/notify into a development environment, but since it supports multiple database backends: can anybody comment on how many other servers have a comparable facility? Minimal research has allowed me to code something for Firebird/Interbase, but I'm not in a position to investigate Oracle, IBM, MS and the rest. I'd appreciate any general comments from somebody who has broad SQL experience, I'm not asking for example code. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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_hba.conf] publish own Python application using PostgreSQL
Jan de Visser wrote: On July 6, 2015 06:43:53 AM c.bu...@posteo.jp wrote: On 2015-07-05 15:13 Jan de Visser j...@de-visser.net wrote: You could set up a whole new server with a different $PGDATA on a different port. I (and the user) don't want to setup anything - that is the point. Well, you don't have to setup anything. You do an initdb in a different directory, that will write a .conf file there, which you then massage to include a different port. You'll use the same binaries as the standard pgsql install, but in a different environment. I'm not sure that helps, since I think part of the question is what the true Debian way is to massage the configuration files to include appropriate entries. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] [No subject]
-- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PL/R etc.
I don't know whether anybody active on the list has R (and in particular PL/R) experience, but just in case... :-) i) Something like APL can operate on an array with minimal regard for index order, i.e. operations across the array are as easily-expressed and as efficient as operations down the array. Does this apply to PL/R? ii) Things like OpenOffice can be very inefficient if operating over a table comprising a non-trivial number of rows. Does PL/R offer a significant improvement, e.g. by using a cursor rather than trying to read an entire resultset into memory? -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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] PL/R etc.
Merlin Moncure wrote: On Fri, May 10, 2013 at 4:41 AM, Mark Morgan Lloyd markmll.pgsql-gene...@telemetry.co.uk wrote: I don't know whether anybody active on the list has R (and in particular PL/R) experience, but just in case... :-) i) Something like APL can operate on an array with minimal regard for index order, i.e. operations across the array are as easily-expressed and as efficient as operations down the array. Does this apply to PL/R? ii) Things like OpenOffice can be very inefficient if operating over a table comprising a non-trivial number of rows. Does PL/R offer a significant improvement, e.g. by using a cursor rather than trying to read an entire resultset into memory? pl/r (via R) very terse and expressive. it will probably meet or beat any performance expectations you have coming from openoffice. that said, it's definitely a memory bound language; typically problem solving involves stuffing data into huge data frames which then pass to the high level problem solving functions like glm. you have full access to sql within the pl/r function, so nothing is keeping you from paging data into the frame via a cursor, but that only helps so much. a lot depends on the specific problem you solve of course. Thanks Merlin and Joe. As an occasional APL user terse and oppressive doesn't really bother me :-) As a particular example of the sort of thing I'm thinking, using pure SQL the operation of summing the columns in each row and summing the rows in each column are very different. In contrast, in APL if I have an array B 1 2 3 4 5 6 7 8 9 10 11 12 I can perform a reduction operation using + over whichever axis I specify: +/[1]B 15 18 21 24 +/[2]B 10 26 42 or even by default +/B 10 26 42 Does PL/R provide that sort of abstraction in a uniform fashion? -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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] PL/R etc.
Merlin Moncure wrote: On Fri, May 10, 2013 at 2:32 PM, Mark Morgan Lloyd markmll.pgsql-gene...@telemetry.co.uk wrote: Merlin Moncure wrote: On Fri, May 10, 2013 at 4:41 AM, Mark Morgan Lloyd markmll.pgsql-gene...@telemetry.co.uk wrote: I don't know whether anybody active on the list has R (and in particular PL/R) experience, but just in case... :-) i) Something like APL can operate on an array with minimal regard for index order, i.e. operations across the array are as easily-expressed and as efficient as operations down the array. Does this apply to PL/R? ii) Things like OpenOffice can be very inefficient if operating over a table comprising a non-trivial number of rows. Does PL/R offer a significant improvement, e.g. by using a cursor rather than trying to read an entire resultset into memory? pl/r (via R) very terse and expressive. it will probably meet or beat any performance expectations you have coming from openoffice. that said, it's definitely a memory bound language; typically problem solving involves stuffing data into huge data frames which then pass to the high level problem solving functions like glm. you have full access to sql within the pl/r function, so nothing is keeping you from paging data into the frame via a cursor, but that only helps so much. a lot depends on the specific problem you solve of course. Thanks Merlin and Joe. As an occasional APL user terse and oppressive doesn't really bother me :-) As a particular example of the sort of thing I'm thinking, using pure SQL the operation of summing the columns in each row and summing the rows in each column are very different. In contrast, in APL if I have an array B 1 2 3 4 5 6 7 8 9 10 11 12 I can perform a reduction operation using + over whichever axis I specify: +/[1]B 15 18 21 24 +/[2]B 10 26 42 or even by default +/B 10 26 42 Does PL/R provide that sort of abstraction in a uniform fashion? certainly (for example see here: http://stackoverflow.com/questions/13352180/sum-different-columns-in-a-data-frame) -- getting good at R can take some time but it's worth it. R is hot right now with all the buzz around big data lately. The main challenge actually is the language is so rich it can be difficult to zero in on the precise behaviors you need. Also, the documentation is all over the place. pl/r plays in nicely because with some thought you can marry the R analysis functions directly to the query in terms of both inputs and outputs -- basically very, very sweet syntax sugar. It's a little capricious though (and be advised: Joe has put up some very important and necessary fixes quite recently) so usually I work out the R code in the R console first before putting in the database. [Peruse] Thanks, I think I get the general idea. I'm aware of the significance of R, and in particular that it's attracting attention due to the undesirability of hiding functionality in spreadsheets where these usurped APL for certain types of operation. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] libpq thread safety
Do any special precautions need to be taken when PQNotifies is being called, to make sure that nothing else is referencing the handle? The sort of nightmare scenario I'm thinking about is when a background thread is periodically pulling data from a table into a buffer, but a foreground (GUI) timer is asynchronously polling for notifications. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] libpq thread safety
Tom Lane wrote: Mark Morgan Lloyd markmll.pgsql-gene...@telemetry.co.uk writes: Do any special precautions need to be taken when PQNotifies is being called, to make sure that nothing else is referencing the handle? It's pretty much the same as any other operation on a PGconn: if there could be more than one thread touching the connection object concurrently, you'd be well advised to add some application-level locking. http://www.postgresql.org/docs/9.2/static/libpq-threading.html The lack of any such locking inside libpq is partly historical, and partly because in many practical situations you'll need application-side locks anyway to protect application data structures associated with the connection. Thanks, Tom. I'm fairly happy with the ways I've used it so far, but I'm just trying to think ahead for the future. In the case of Delphi/Lazarus, where you can have multiple queries on top of the same connection object, my experience so far is that using the connection object's handle is safe. But I think that to be absolutely confident of that I need to do some tracing, and find out under what circumstance calls are being issued directly against that handle rather than it just being a placeholder for authentication etc. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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] Trouble with PQnotifies()
seil...@so-net.net.tw wrote: The following listening worker thread behaves as expected if I insert/delete rows into/from table t1 in psql prompt. My trouble is when the SQL execution worker thread inserts/ deletes rows into/from table t1, the listening worker thread then goes crazy: PQnotifies() always returns NULL which pushes the listening thread to grab all CPU power because select() returns immediately in every iteration. The weird part is that select() says that there is something available but PQnotifies() returns NULL. .. Please ignore this question! My connection pool implementation seems to have flaw. Somehow and somewhere the connection acquired by a thread is robbed by other threads. The PGconn sending LISTEN NotifyMe becomes different from the PGconn passed to PQsocket(), PQconsumeInput(), and/or PQnotifies(). I was looking at it carefully, and was about to ask about the connection- in particular whether it was shared across threads etc. Glad you've found the issue, I've been caught by something very similar using list/notify on Lazarus/FPC where you can end up with several handles only one of which is reliable. Please also pardon me for asking inappropriate questions like this one. As far as I can recall, every issue I encountered before always finally proved that PostgreSQL is flawless. But at least it demonstrates that somebody's using that facility. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] libpq - prevent automatic reconnect
Chris Angelico wrote: On Thu, Dec 6, 2012 at 5:56 AM, Tom Lane t...@sss.pgh.pa.us wrote: I suspect this action isn't dropping the TCP connection. It's only equivalent to a momentary glitch in your network connectivity --- and you'd be very unhappy if that caused TCP connections to go down, because networks have glitches all the time. Generally, the operating system tries hard to prevent applications from even knowing that a glitch happened. (Connections will time out eventually if connectivity doesn't come back, but typically such timeouts are many minutes. Possibly whatever your real complaint is could be addressed by twiddling the TCP timeout parameters for the socket.) Yep. For a better test, try taking the interface down for a good while (several minutes), or actually shut down the Postgres server at the other end. I find PostgreSQL connections, particularly with listen/notify set up, to be fairly sensitive to disconnection. This is particularly the case with apps written using either Delphi or Lazarus, where a session is kept live for an extended period rather than simply being used to transfer a query and resultset. This isn't a recent thing, and I'm definitely not saying that it's a Postgres issue. I've tried forcing random connection drops at the application level in the past and have never been able to characterise the problem. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Installing minimal client libraries on Windows
Albe Laurenz wrote: Mark Morgan Lloyd wrote: Apologies for this old chestnut, but I think it's a question more often asked than answered. If I want to install a minimal binary libpq.dll on a non-developer machine to support Lazarus/FPC programs, where do I get it? I definitely don't want to tell the users to install a full server, or to build from source. I'd rather not give them pgadmin or psql. I'd prefer not to install ODBC since I need to get at listen/notify that it doesn't support. You can extract it from the precompiled ZIP archive that EnterpriseDB provides. Thanks, but in the end I installed ODBC as the path of least resistance and relied on the OS knowing where the DLLs were. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Installing minimal client libraries on Windows
Apologies for this old chestnut, but I think it's a question more often asked than answered. If I want to install a minimal binary libpq.dll on a non-developer machine to support Lazarus/FPC programs, where do I get it? I definitely don't want to tell the users to install a full server, or to build from source. I'd rather not give them pgadmin or psql. I'd prefer not to install ODBC since I need to get at listen/notify that it doesn't support. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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] Looking for ODBC drivers for NT4.
Mouse Dresden wrote: OK. I won't go into details about why I need them, since no one wants a long story. Simply put, I'm looking for odbc drivers for postgresql that will work with NT4. I've been using 8.02.01.04 on NT4 app servers for an extended period, look for psqlodbc.msi of the appropriate version. You might also need a separate MDAC installer. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Terminating a rogue connection
Assuming a *nix server: if a monitoring program determines that an established connection appears to be trying to so something inappropriate, what's the best way of terminating that session rapidly? -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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] Terminating a rogue connection
Chris Angelico wrote: On Fri, Jul 27, 2012 at 6:27 PM, Mark Morgan Lloyd markmll.pgsql-gene...@telemetry.co.uk wrote: Assuming a *nix server: if a monitoring program determines that an established connection appears to be trying to so something inappropriate, what's the best way of terminating that session rapidly? select pg_terminate_backend(procpid) from pg_stat_activity where . The main difficulty is recognizing which PID to terminate, though. Exactly :-) I'd add that this is a hypothetical situation at present, I'm just trying to plan ahead. There's a good lot of information available in pg_stat_activity; logins, application names, and connection IP addresses are handy here. But ultimately, it's just pg_terminate_backend. ChrisA -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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] Switching from OSX to Linux, multi-line queries in \copy don't work anymore
Craig Ringer wrote: On 07/27/2012 09:28 PM, Ryan Kelly wrote: I recently switched from OSX to Linux and \copy in psql no longer accepts multi-line queries. For instance: \copy ( select * from pg_settings ) to '/tmp/settings.csv' with csv header This works fine on OSX. On Linux I get: \copy: parse error at end of line Am I missing something here? A wild guess: I'd say this is a consequence of the fact that psql on OS X uses libedit, on Linux it uses readline. Personally I had no idea that multiline \copy was possible at all. I've always though the way backslash commands are EOL-terminated while everything else is semicolon terminated is a bit of a wart, though. I don't have an answer for you. Using the --no-readline argument makes no difference on my 9.1.4 here. This may just be an area where libedit is smarter than readline - or it might be that I'm totally wrong and the real issue is something else entirely. Thanks for making the effort to produce a good post with all the detailed version info, exact error text, etc. Also appear to get it here on single-line queries: markMLl= \copy (select * from pg_settings) to '/tmp/settings.csv' with csv header; \copy: parse error at select markMLl= However my psql and server are rather old which could be an issue (8.1.19 to server 8.4 if I recall correctly). -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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] Terminating a rogue connection
Chris Angelico wrote: On Fri, Jul 27, 2012 at 7:09 PM, Mark Morgan Lloyd markmll.pgsql-gene...@telemetry.co.uk wrote: Chris Angelico wrote: On Fri, Jul 27, 2012 at 6:27 PM, Mark Morgan Lloyd markmll.pgsql-gene...@telemetry.co.uk wrote: Assuming a *nix server: if a monitoring program determines that an established connection appears to be trying to so something inappropriate, what's the best way of terminating that session rapidly? select pg_terminate_backend(procpid) from pg_stat_activity where . The main difficulty is recognizing which PID to terminate, though. Exactly :-) I'd add that this is a hypothetical situation at present, I'm just trying to plan ahead. Something I've been developing at work lately combines this with editing pg_hba.conf to ensure that a kicked connection cannot reconnect. Services register themselves with a particular user name, then SET USER to switch to the one actual user who owns tables and stuff, so my overlording monitor can kick off any service based on IP and usename (note the spelling - it's not username in the table). Rewrite pg_hba.conf, SIGHUP, then pg_terminate_backend in a searched SELECT as seen above. This may be overkill for what you're doing, though. It's part of our prevent split-brain problems technique. One problem there is that if somebody is doing something that causes a significant CPU or memory overcommit, it might be some while before SIGHUP etc. works. I'm currently eyeballing the Linux capabilities stuff, it looks as though if a monitor has CAP_NET_ADMIN that it will be able to temporarily add a firewall rule that blocks the rogue client's traffic. I'm hoping to be able to avoid on the fly editing of configuration files, there's too much could go wrong. Which I suppose leads into another question... -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Adding users connection via SSL
I'm hoping to be able to avoid on the fly editing of configuration files, there's too much could go wrong. Is it possible to create or modify a user connecting via an authenticated/encrypted protocol as an SQL activity, in the same way that CREATE ROLE ... PASSWORD creates one authenticated by password? -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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] timestamps, formatting, and internals
Jasen Betts wrote: On 2012-05-29, David Salisbury salisb...@globe.gov wrote: On 5/27/12 12:25 AM, Jasen Betts wrote: The query: show integer_datetimes; should return 'on' which means timestamps are microsecond precision if it returns 'off' your database was built with floating point timstamps and equality tests will be unreliable, I find that rather interesting. I was told that I was losing microseconds when I extracted an epoch from the difference between two timestamps and casted that value to an integer. So if I have integer timestamps ( your case above ) I get microseconds, but integer epochs is without microseconds? yeah, the microseconds appear as fractions of seconds, so in the conversion to integer epoch they get rounded off. I think you need to consider what you're actually computing and measuring. My understanding is that Meeus's Equation of Time calculation is good to something like 250mSec so that's the limit of your accuracy, but as soon as you start taking refraction and atmospheric turbulence into account- even with the Sun high above the horizon- you're going to degrade that. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Libpq question
zeljko wrote: John Townsend wrote: It appears that some developers (Davart) are by-passing the standard client library, “libpq.dll”, and directly accessing the server using Delphi or FPC. I am not sure of the advantage here. All libpq.dll I'm FPC user and I use libpq.so(.dll,.dylib) via zeoslib. Those who bypass libpq probably uses odbc connections or similar. Apologies for my rather late comment. ODBC, JDBC, Zeos etc. are all appropriate if you want an app to be able to contact different types of backend. However, the further you move from native connections, the more difficult it becomes to use PostgreSQL-specific functionality such as the listen/notify mechanism: I've used this to good effect in an FPC/Lazarus program and while I believe it can be hacked in via ODBC the result is hardly pretty. In addition, while it is in principle possible to roll your own interface library, the libpq+pg combination has been in use for 10+ years, is actively maintained, and (hopefully) is bug- and backdoor-free. Reinventing this particular wheel is definitely not something that should be approached casually. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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] Listen and Notify
Merlin Moncure wrote: On Mon, Apr 30, 2012 at 1:47 AM, Alexander Reichstadt l...@mac.com wrote: Hi, From the documentation I was able to build a trigger firing upon deletion of a record a function that delivers tablename_operation as a notification one needs to subscribe to. So in terminal I can say LISTEN persons_delete and instantly will receive Asynchronous notification persons_delete received from server process with PID 54790. if there was a delete. But what I don't fully understand is how to do this with PQnotifies. Following the docu I get no notifications even though I subscribe to them after successfully connecting to the server the same way I do using terminal. Googling didn't give me examples I was able to use. Please, can someone help? I'm suspecting operator error because using PQnotifies is exactly what psql does. Maybe you could post a small test program? Are you using a mixed case notification name? Works here in a Pascal program built using Lazarus. You /must/ however, ensure that the session requesting the notification is kept active and is the one that's regularly checked, it's easy to lose track of this in a development framework which isolates you from the low-level handles etc. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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 DBA in SPAAAAAAAACE
Rob Sargent wrote: On 12/06/2011 01:56 PM, Glyn Astill wrote: __ From: Merlin Moncure mmonc...@gmail.com To: Joe Miller joe.d.mil...@gmail.com Cc: pgsql-general@postgresql.org Sent: Tuesday, 6 December 2011, 17:30 Subject: Re: [GENERAL] PostgreSQL DBA in SPCE On Tue, Dec 6, 2011 at 10:56 AM, Joe Miller joe.d.mil...@gmail.com wrote: You may have seen this, but RedGate software is sponsoring a contest to send a DBA on a suborbital space flight. And there is a PostgreSQL representativeme! https://www.dbainspace.com/finalists/joe-miller Voting is open for 7 days. Don't let one of those Oracle or SQL Server punks win :p so jealous -- I didn't make the cut. Well, you'll have my vote. merlin Me neither, voted. Good luck. Tried to vote, but never got the mail to confirm my vote? Message comes with header Confirm your vote, so a firewall that is over-zealous catching confirm your account type emails will can it. I suspect that we're supposed to do this every day- I'm sure that Certain Corporates will have no compunction at using multiple votes. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Listen/notify and ODBC
In early 2009 I asked whether there was a hack to allow PostgreSQL's listen/notify commands via ODBC, some while later I believe that somebody sent me example code encoded as base64 which erroneously got deleted as spam. Google suggests that several other people have asked about this over the years: if anybody still has this example do you think you could post a URL where it might be found? My apologies for posting this here as well as in the ODBC ML. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Per-query local timezone
Is it possible to incorporate SET TIMEZONE into a query, so that to_char(...'TZ') etc. is appropriately localised? The development environment I'm working with uses short-lifetime sessions, and it's proving difficult to get a set command and a query associated with the same handle. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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] Per-query local timezone
Karsten Hilbert wrote: On Tue, Jun 14, 2011 at 09:40:20AM +, Mark Morgan Lloyd wrote: Is it possible to incorporate SET TIMEZONE into a query, so that to_char(...'TZ') etc. is appropriately localised? You seem to want AT TIME ZONE. Thanks for that. How can I do /this/ select to_char(now() at time zone 'GMT0BST', 'TZ'); It appears to return '', while if I used a separate SET TIMEZONE I'd expect 'BST'. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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] Per-query local timezone
Tom Lane wrote: John R Pierce pie...@hogranch.com writes: On 06/14/11 2:40 AM, Mark Morgan Lloyd wrote: The development environment I'm working with uses short-lifetime sessions, and it's proving difficult to get a set command and a query associated with the same handle. this environment doesn't support even a transaction? Sounds kinda broken :-( ... but maybe Mark could wrap the operations he needs into custom functions. Is always a possibility. The problem is that particular component I'm using conflates the open and issue-query operations and has an implicit transaction, the developers are aware that this has undesirable implications. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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] Per-query local timezone
Steve Crawford wrote: On 06/14/2011 05:13 AM, Mark Morgan Lloyd wrote: Karsten Hilbert wrote: On Tue, Jun 14, 2011 at 09:40:20AM +, Mark Morgan Lloyd wrote: Is it possible to incorporate SET TIMEZONE into a query, so that to_char(...'TZ') etc. is appropriately localised? You seem to want AT TIME ZONE. Thanks for that. How can I do /this/ select to_char(now() at time zone 'GMT0BST', 'TZ'); It appears to return '', while if I used a separate SET TIMEZONE I'd expect 'BST'. The now() function returns a timestamp with time zone (aka a point in time). When you ask for a timestamp with time zone at a specific time zone, you get a timestamp *without* time zone (you provided and therefore know the desired time zone and PostgreSQL returned the timestamp in that zone). I'm a bit concerned with your initial statement that The development environment I'm working with uses short-lifetime sessions, and it's proving difficult to get a set command and a query associated with the same handle.. Do I take this to mean that connections are going through some sort of pooler that is allocating connections on as short as a per-statement basis so you might end up with a different connection between the set time zone.. statement and the query? If so, you may start to find all sorts of other issues. It's a bit convoluted, but you could get the zone from a subquery and select the timestamp converted to that zone along with the zone itself from the outer query: select now() at time zone foo.tz, foo.tz from (select 'est5edt'::text as tz) as foo; Looking back through the mailing list, the issue appears to be the way that AT TIME ZONE is parsed into a function which returns a string. I think the easiest way round most of this is going to be to use the PGTZ shell variable, otherwise I think I can pull the info I need out of pg_timezone_names subject to using the correct zone name. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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] ZEOS or PGDAC - How to lock a resource?
Merlin Moncure wrote: 2011/5/4 durumdara durumd...@gmail.com: Hi! We will porting an application to PGSQL from some table based app (BDE like). The older application used a special technic of the driver: if a record edited, some exclusive (over transaction), forever living lock put on it. On exit, cancel, or post this lock removed. We used this to lock the main resource from concurrent edits. advisory locks http://www.postgresql.org/docs/current/interactive/functions-admin.html#FUNCTIONS-ADVISORY-LOCKS (aside: borland delphi is increasingly obsolete in the scheme of things, but zeos is one of the best postgres drivers ever written!) I don't use Zeos, but a few months ago I was using listen/notify via FPC's standard classes to good effect so I'd be surprised if there were any problems. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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, PSN hack and table limits
Greg Smith wrote: On 05/01/2011 01:50 AM, Mark Morgan Lloyd wrote: Somebody is making a very specific claim that Postgres can support a limited number of rows Did you find this via http://www.reversecurity.com/2011/04/new-details-from-psn-hack.html ? That was the only Google-indexed source leading to it I found. I just left a note there about the silliness of these claims. I could run more than a 10M row PostgreSQL instance on my phone. Unless there's a new 16-bit only Vic 20 port of PostgreSQL available or something, it's seems unlikely the data had to be partitioned due to any hard limit. Yes, via Google. I was digging around for any information about what sort of access and APIs the network made available to end-user (or ersatz developer) systems. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Postgresql, PSN hack and table limits
Somebody is making a very specific claim that Postgres can support a limited number of rows: INPS (a data forensics team) said that there is 7 main Databases all hosted at different data centers but linked over a type of 'cloud' Each database uses PostGRESSQL which would mean the most amount of data each database could hold with no stability issues is aproximitely equal to that of 10,348,439 Rows http://pastebin.com/MtX1MDdh Does anybody have any idea where they've got hold of this figure? -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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, PSN hack and table limits
Magnus Hagander wrote: On Sun, May 1, 2011 at 07:50, Mark Morgan Lloyd markmll.pgsql-gene...@telemetry.co.uk wrote: Somebody is making a very specific claim that Postgres can support a limited number of rows: INPS (a data forensics team) said that there is 7 main Databases all hosted at different data centers but linked over a type of 'cloud' Each database uses PostGRESSQL which would mean the most amount of data each database could hold with no stability issues is aproximitely equal to that of 10,348,439 Rows http://pastebin.com/MtX1MDdh Does anybody have any idea where they've got hold of this figure? PostgreSQL, of course, has no such ridiculous limits. Whether a specific application running on top of PostgreSQL would have a limitation like that, is of course a different question - that might certainly be possible, even though the limit mentioned is a really weird number. I find it really hard to parse the text of that post to even understand what they mean, but it's rather obviously filled with other completely incorrect technical statements, so I wouldn't pay any attention to this one in particular. (e.g. since when did you need port 25 to download an email attachment? and suddenly they mention oracle metasploits, which obviously wouldn't work on postgres) I'm not paying attention because of the amount of rubbish in that text, but other people are likely to if Google makes the connection between the current unpleasantness and this project- which is why I'm avoiding mentioning certain names. I don't know for certain who these INPS people are or why they think they're qualified to pontificate. The Oracle angle could be related to older SQL injection attacks (2008?) or somebody could be assuming that a compatibility layer introduces vulnerabilities. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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, PSN hack and table limits
Tom Lane wrote: Jasen Betts ja...@xnet.co.nz writes: On 2011-05-01, Mark Morgan Lloyd markmll.pgsql-gene...@telemetry.co.uk wrote: Somebody is making a very specific claim that Postgres can support a limited number of rows: INPS (a data forensics team) said that there is 7 main Databases all hosted at different data centers but linked over a type of 'cloud' Each database uses PostGRESSQL which would mean the most amount of data each database could hold with no stability issues is aproximitely equal to that of 10,348,439 Rows http://pastebin.com/MtX1MDdh Does anybody have any idea where they've got hold of this figure? the figure is within 1% of the maximun size for data stored in text (or bytea) column. No it isn't; the max size per field is 1GB. Although actually manipulating such field values will probably not work very well unless you have a 64-bit machine, else you'll hit address-space issues. I could believe that a specific application using specific fields in a specific way in a 32-bit machine might start to hit out of memory errors for field widths somewhere in the tens-of-MB range. But the stated claim is about number of rows, not row width, and the exactness and breadth of the claim is, well, ridiculous on its face. I think INPS's level of knowledge about PG must be about as good as their ability to spell it :-( BTW, there *is* a hard limit of 32TB per table, arising from the limited size of BlockNumber. But it's hard to believe that INPS's claim has anything to do with that. I suspect that INPS is something to do with the French police, so might have been briefed by somebody who purports to know what he's talking about. But I'm assuming that whoever put those ramblings on Pastebin is unreliable, the only reason I mentioned it was that specific claims were being made which might become widely repeated. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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 the name of the timezone, adjusted for daylight saving
Tom Lane wrote:
Steve Crawford scrawf...@pinpointresearch.com writes:
I think you may have confused yourself by the order of operations. This:
('2011-03-01 12:00' AT TIME ZONE 'GMT0BST')::TIMESTAMP WITH TIME ZONE
created a timestamp from some text and you specified the time-zone to be
used in creating that value (stored internally in UTC). This was passed
to to_char which displayed the calculated the appropriate display of
that value in whatever time-zone the client was using.
One other point here is that I believe that given an undecorated literal
like that, the parser will prefer to assume it is timestamp *with* time
zone if the alternatives are with or without. So what you actually had
here was
1. Literal constant '2011-03-01 12:00' is assumed to be written in
whatever your timezone is; then it's rotated into UTC time.
2. AT TIME ZONE converts this to timestamp *without* time zone, rotating
into the specified 'GMT0BST' zone.
3. The cast converts back to timestamp *with* time zone, again assuming
that the given unlabeled timestamp is in the timezone zone and rotating
that to UTC.
4. If you feed the result to to_char, it's going to rotate the UTC value
back into the timezone zone and then format that.
Somewhere along here you have a complete mess. It might accidentally
fail to fail if tested with a timezone setting of GMT or GMT0BST, but
otherwise it's an extremely expensive way of getting the wrong answer.
The right way to specify a time that's known to be measured in a
particular timezone is something like
'2011-03-01 12:00 GMT0BST' :: timestamptz
or if you prefer you can do
'2011-03-01 12:00'::timestamp AT TIME ZONE 'GMT0BST'
Either of these will produce the same timestamptz value, but note the
explicit casts are important.
If you've got a timestamptz value, and you want to display it in a
particular zone and include the zone identity in the output, I don't
think there is any way that is more practical than changing the timezone
setting to that zone. After that you can either just display the value,
or use to_char if you don't like the default formatting. The AT TIME
ZONE construct is *not* very helpful for this because it won't show
anything about the zone. I suppose you could write
(timestamptz_value AT TIME ZONE 'GMT0BST') || ' GMT0BST'
but that seems like a crock.
Thanks Steve and Tom. So to sum up this particular subthread:
i) AT TIME ZONE primarily influences input, not output.
ii) If I want to influence output, then I need to (temporarily) change
the session's timezone setting.
But out of curiosity will (ii) also mess up extract(epoch ...), or will
that consistently return the number of UTC seconds rather than adjusting
for the local timezone?
If it does, is there a right way of restricting the scope of a
timezone change to a single function?
--
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk
[Opinions above are the author's, not those of his employers or colleagues]
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Getting the name of the timezone, adjusted for daylight saving
Running 8.4.6 hosted on Linux, if I do this...
SELECT to_char('2011-03-01 12:00' AT TIME ZONE 'GMT0BST', 'HH24:MI TZ');
to_char
-
12:00
(1 row)
..I don't get a timezone- I can live with that.
If I do this...
SELECT to_char(('2011-03-01 12:00' AT TIME ZONE 'GMT0BST')::TIMESTAMP
WITH TIME ZONE, 'HH24:MI TZ');
to_char
---
12:00 GMT
(1 row)
..then I get the GMT time with a timezone 'GMT'- that's what I want.
But if I do this...
SELECT to_char(('2011-04-01 12:00' AT TIME ZONE 'GMT0BST')::TIMESTAMP
WITH TIME ZONE, 'HH24:MI TZ');
to_char
---
13:00 GMT
(1 row)
..then I get the time corrected for daylight saving- which is what I
want- but the timezone doesn't indicate that daylight saving has been
applied.
In the general case, how can I get TZ indicating whether daylight saving
is in effect? Alternatively, is there a flag I can retrieve indicating
that a timestamp has been corrected for DST so that I can select an
alternative name for display?
--
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk
[Opinions above are the author's, not those of his employers or colleagues]
--
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 the name of the timezone, adjusted for daylight saving
Tom Lane wrote:
Mark Morgan Lloyd markmll.pgsql-gene...@telemetry.co.uk writes:
SELECT to_char(('2011-03-01 12:00' AT TIME ZONE 'GMT0BST')::TIMESTAMP
WITH TIME ZONE, 'HH24:MI TZ');
to_char
---
12:00 GMT
(1 row)
You haven't said exactly what you were hoping to accomplish, but I
suspect the point here is to format a time according to some other zone
than the prevailing TimeZone setting. You basically can't do that, at
least not with to_char and the timestamptz data type --- the information
just isn't there. Consider creating a little plpgsql function that
temporarily changes the timezone setting and then calls to_char.
Thanks Tom. Timestamps are going into the database which are implicitly
UTC, and I was looking for a way to convert them when displayed to the
local timezone (the client gets this from a configuration file and puts
it in the query) and also to present the timezone name.
So I think that what you're saying is that the result from to_char()
will always be UTC, possibly corrected for daylight saving (i.e. GMT or
BST respectively). I can live with that, the app is for local use and
we're not that far from Greenwich :-)
Now if I'm reading the documentation correctly I can refer to
pg_timezone_names and use is_dst to determine whether a particular
timezone (e.g. GMT) has a daylight saving correction applied at the
current date. But from there is there any cross-reference so that if DST
is active I can change the displayed TZ name to BST?
--
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk
[Opinions above are the author's, not those of his employers or colleagues]
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Getting current and average on a single row
What is best practice when extracting both current and average from a table? Demonstration table here contains data from a cheap weather station. I can obviously get the current reading like this: select temp_out, dewpoint from weather where datetime between (now() - '10 minutes'::interval) and now() order by datetime desc limit 1; and I can get averages like this: select avg(temp_out) as avg_temp_out, avg(dewpoint) as avg_dewpoint from weather where datetime between (now() - '45 minutes'::interval) and now(); In both cases there are a dozen or so columns in total. How are these best merged to yield a single row? Some form of join, or window functions? -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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 current and average on a single row
Adrian Klaver wrote: On Wednesday 24 November 2010 1:08:27 am Mark Morgan Lloyd wrote: What is best practice when extracting both current and average from a table? Demonstration table here contains data from a cheap weather station. I can obviously get the current reading like this: select temp_out, dewpoint from weather where datetime between (now() - '10 minutes'::interval) and now() order by datetime desc limit 1; and I can get averages like this: select avg(temp_out) as avg_temp_out, avg(dewpoint) as avg_dewpoint from weather where datetime between (now() - '45 minutes'::interval) and now(); In both cases there are a dozen or so columns in total. How are these best merged to yield a single row? Some form of join, or window functions? I am not seeing a dozen columns, maybe rows? I'd only put in a couple of columns as an example, but I was also making the point that it would be nice to avoid having to type in an excessive number of column names. I quick and dirty solution(testing needed): select temp_out,dewpoint,atbl.avg_temp_out,atbl.avg_dewpoint from (select avg(temp_out) as avg_temp_out, avg(dewpoint) as avg_dewpoint from weather where datetime between (now() - '45 minutes'::interval) and now()) as atbl, weather order by datetime desc limit 1; Has to be a bit more complex than that to make sure that the current reading really is current: select ctbl.temp_out,ctbl.dewpoint, atbl.avg_temp_out,atbl.avg_dewpoint from ( select avg(temp_out) as avg_temp_out, avg(dewpoint) as avg_dewpoint from weather where datetime between (now() - '45 minutes'::interval) and now() ) as atbl, ( select * from weather where datetime between (now() - '8 minutes'::interval) and now() order by datetime desc limit 1 ) as ctbl; explain prices that as Nested Loop (cost=8.30..16.62 rows=1 width=84). I think it's more elegant than the SQL I'm currently using select * from ( select * from weather where datetime between (now() - '10 minutes'::interval) and now() order by datetime desc limit 1 ) as foo left outer join ( select datetime, avg(temp_out) as avg_temp_out, avg(dewpoint) as avg_dewpoint from weather where datetime between (now() - '45 minutes'::interval) and now() group by datetime ) as bar using (datetime); but I note that explain prices that as Nested Loop Left Join (cost=0.02..16.63 rows=1 width=215). Does that mean that the query using the nested join will, on average, be more efficient? -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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] Best practice for file storage?
Joe Kramer wrote: Hi, I need to store a lot of large files (thousands of 10-100 MB files) uploaded through my web application and I find that storing them in database as bytea field is not practical for backup purposes. My database has full backup performed every 12 hours and backup is encrypted and copied to server on another continent. Having all the heavy binary data in database will make backups impossible. So I am thinking of having a table just for metadata and file id sequence and storing the file on file system: Wasn't one of the particle accelerator establishments using PostgreSQL in this way for their raw data? -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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] Help me about postgreSql code
John R Pierce wrote:
Elian Laura wrote:
i understand, but why my teacher wrote in his paper..Probably the
most obvious case is a database engine where the user defines, at run
time, if a field is integer, char, float, etc. but, it is not
necessary to compile the program again. All this felxibility must be
.
I am not a PostgreSQL developer but I think the thing to understand here
is that there are two stages involved. If I say much more I think I'll
be guilty of doing your homework for you, I suggest that you look very
carefully at the examples John gave you earlier and consider that from
the viewpoint of the database engine they are being entered at runtime.
CREATE TABLE users (uid INTEGER, username TEXT, firstname TEXT,
lastname TEXT);
INSERT INTO users (uid, username) VALUES (103, 'jpierce', 'John',
'Pierce'), ('104', 'elaura', 'Elian', 'Laura');
SELECT username,firstname,lastname FROM users WHERE uid=103;
If you think about it one of those statements is giving the system
information which it can store (I'm not sure the way it does this is
really important) and re-use.
--
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk
[Opinions above are the author's, not those of his employers or colleagues]
--
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] Server name in psql prompt
Mark Morgan Lloyd wrote: I thought earlier that I could use finger as a hack for querying the server, i.e. I could put e.g. a disc set name in /home/postgres/.plan. However I then realised that I'd need %M to be expanded before %`, so that I could do something like \set PROMPT1 '`finger postg...@%m|filter`: %/%R%# ' where filter only returned the bit that was needed. I've not tried this due to the ordering issue. I've just been playing with this. I can finger a fixed server for its true name using e.g. postgres: markMLl= \set PROMPT1 '%`finger postg...@postgres|head -1|cut -d \[ -f 2|cut -d . -f 1`: %/%R%# ' postgres1: markMLl= but if I try to replace the fixed server name in that \set PROMPT1 '%`finger postg...@%:HOST:|head... the nested variable doesn't get expanded. That's obviously useful if there's only one server or the servers are pooled since it means that the current disk set or whatever can be identified, but less so if there are multiple servers. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Server name in psql prompt
Is there any way of getting psql to display the name of the currently-connected server in its prompt, and perhaps a custom string identifying e.g. a disc set, without having to create a psqlrc file on every client system that's got a precompiled psql installed? I've just come close to dropping a table that would have been embarrassing because I couldn't see which server an instance of psql was talking to. Now obviously that's due to lackwittedness on my part and it could be cured by installing psqlrc files- but this might not be a viable option since it means chasing down every psql binary that's been installed on the LAN in an attempt to protect users from self-harm: far nicer if the default psql prompt could be loaded from the server. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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] Server name in psql prompt
hubert depesz lubaczewski wrote: On Fri, Jan 08, 2010 at 11:20:36AM +, Mark Morgan Lloyd wrote: Is there any way of getting psql to display the name of the currently-connected server in its prompt, and perhaps a custom string identifying e.g. a disc set, without having to create a psqlrc file on every client system that's got a precompiled psql installed? what exactly is the problem with distributing your own .psqlrc? for me it's one of the first things that I do - I setup environment. Thanks everybody for the comments. The problem is that in an environment where the end-users generally have enough nous (or are sufficiently assertive) to run their own systems (e.g. an engineering department) there is still a requirement to protect shared resources like a database. It's not really feasible for the overall sysadmin to work his way around all possible machines, work out which distro each is running, and install a suitable psqlrc in the place expected by that distro's psql. It's even less feasible to install a shim that forces default command-line parameters. When I referred to a disc set I wasn't thinking about something in the context of PostgreSQL, I was thinking about a group of discs in removable (Compaq) caddies that might be transferred to one of a number of chassis. At present I've got one chassis here into which I'm putting one of two disc sets, both of which are the 8.4 upgrade target: I'd like to be able to confirm from the client which set is in the chassis. After playing some more I think there are actually three issues: i) Getting psql to take its initial defaults, i.e. if there isn't a psqlrc file, from the server (e.g. for the prompt). ii) Getting the prompt to display the actual hostname of the server, rather than what was put on the command line which might be an alias or dotted-quad address. iii) Getting the prompt to display some other identifier from the server to identify the disc set. I don't think anybody else thinks this is an issue so I guess all I can say is thanks for listening :-) -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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] Server name in psql prompt
Adrian Klaver wrote: In the case you describe the below might work: http://www.postgresql.org/docs/8.4/interactive/app-psql.html Before starting up, psql attempts to read and execute commands from the system-wide psqlrc file and the user's ~/.psqlrc file. (On Windows, the user's startup file is named %APPDATA%\postgresql\psqlrc.conf.) See PREFIX/share/psqlrc.sample for information on setting up the system-wide file. It could be used to set up the client or the server to taste (using the \set and SET commands). Thanks Adrian, link noted. Set up a system psqlrc. I have done this when working with multiple versions/multiple database clusters of Postgres on one machine to keep track. I've gone round a number of machines setting up a basic psqlrc file, and will mail the most obnox^H^H^H^H^H demanding users warning them of the issues. ii) Getting the prompt to display the actual hostname of the server, rather than what was put on the command line which might be an alias or dotted-quad address. Same link as above. %M The full host name (with domain name) of the database server, or [local] if the connection is over a Unix domain socket, or [local:/dir/name], if the Unix domain socket is not at the compiled in default location. I've just checked that and if I do psql -h postgres where postgres is a DNS alias to postgres1 then the expansion of %M is postgres not postgres1. iii) Getting the prompt to display some other identifier from the server to identify the disc set Make either one of these different for each server. %:name: The value of the psql variable name. See the section Variables for details. %[ ... %] OK but if I understand you (and the docs) correctly I'd still need to find a way to set the variable on the client rather than having something fetched from the server. I thought earlier that I could use finger as a hack for querying the server, i.e. I could put e.g. a disc set name in /home/postgres/.plan. However I then realised that I'd need %M to be expanded before %`, so that I could do something like \set PROMPT1 '`finger postg...@%m|filter`: %/%R%# ' where filter only returned the bit that was needed. I've not tried this due to the ordering issue. Prompts can contain terminal control characters which, for example, change the color, background, or style of the prompt text, or change the title of the terminal window. In order for the line editing features of Readline to work properly, these non-printing control characters must be designated as invisible by surrounding them with %[ and %]. Multiple pairs of these can occur within the prompt. For example: testdb= \set PROMPT1 '%[%033[1;33;40m%...@%/%R%[%033[0m%]%# ' results in a boldfaced (1;) yellow-on-black (33;40) prompt on VT100-compatible, color-capable terminals. Thanks, noted. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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] Updating from 8.2 to 8.4
Adrian Klaver wrote: Thanks Adrian, noted. I'm hoping to get onto 8.4 before too long because of the analytic functions but that thread suggests that the best course of action would be to make sure that my code is robust against the new server and then decide on an upgrade schedule. I'm building from scratch here so all options are as default except for added Perl. -- Alright then, nothing worse then trying to fix a problem that does not exist :) When a programmer has a hard time fixing a bug it's because he's looking in the wrong place. :-) One other thing that came to mind in regards to the the 'changed record' problem is whether Row Versioning has been checked in the ODBC connection parameters? No. However once I got to the point that a development machine was looking at the new server I could reproduce the problem and (appear to have) fixed it: the app was a scheduler running on a Windows system and contained a lot of pathnames with backslashes hence standard_conforming_strings = on. I'll play with the new system for a few days and if all goes well try to migrate on New Year's Day. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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] Updating from 8.2 to 8.4
Adrian Klaver wrote: On Thursday 24 December 2009 11:20:35 am Mark Morgan Lloyd wrote: I was hoping to finally get the servers updated from 8.2 to 8.4 over the festive season, but by now I think I've left things too tight. Is it necessary to update the (Windows) ODBC driver as well? I've got a couple of app servers still on NT4 with ODBC 8.02.0100 which I'm trying to be careful with lest I put something on them which requires a later OS and can't back out. The apps are written in a rather old version of Delphi with BDE which is fine with 8.2. Trying to run against 8.4 I get Couldn't perform the edit because another user changed the record.- looking back through this and other MLs I see suggestion that this could be caused by an excessive number of decimal places in the data (current case only contains integers, timestamps and text) or possibly by a transaction isolation issue. My experience with this is it related to timestamps with fractional second precision. The other thing to note is that in 8.4 the default for datetimes is now 64-bit integer datetimes, not the previous float datetimes. Many thanks for that Adrian. I notice this in the ODBC release notes which could be relevant: -8- psqlODBC 08.04.0100 Release .. 8.) Remove a spurious . with no trailing digits in timestamp representation (bug report [#1010540] from Brian Feldman). -8- although I don't know why it didn't bite on 8.2 unless it's specifically when 64-bit timestamps are processed. I think that I'll try an ODBC update on a non-critical system, if that doesn't work I'll dig into my code. The table giving problems is part of a scheduling program where I can probably truncate timestamps, elsewhere it might not be so convenient. I'll report back if I find anything interesting. The sooner I can get this code off Delphi+BDE+ODBC+Windows to Lazarus+Linux the better. Happy Christmas everybody, whatever country you're in and whatever your tradition of celebration. We run 24x365 here but the caterers are hard at work :-) -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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] Updating from 8.2 to 8.4
Adrian Klaver wrote: although I don't know why it didn't bite on 8.2 unless it's specifically when 64-bit timestamps are processed. You might want to confirm your 8.4 installation is using integer datetimes as there is some variability in its useage among packagers. Follow this thread for one such discussion: http://archives.postgresql.org/pgsql-general/2009-07/msg01119.php Thanks Adrian, noted. I'm hoping to get onto 8.4 before too long because of the analytic functions but that thread suggests that the best course of action would be to make sure that my code is robust against the new server and then decide on an upgrade schedule. I'm building from scratch here so all options are as default except for added Perl. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Updating from 8.2 to 8.4
I was hoping to finally get the servers updated from 8.2 to 8.4 over the festive season, but by now I think I've left things too tight. Is it necessary to update the (Windows) ODBC driver as well? I've got a couple of app servers still on NT4 with ODBC 8.02.0100 which I'm trying to be careful with lest I put something on them which requires a later OS and can't back out. The apps are written in a rather old version of Delphi with BDE which is fine with 8.2. Trying to run against 8.4 I get Couldn't perform the edit because another user changed the record.- looking back through this and other MLs I see suggestion that this could be caused by an excessive number of decimal places in the data (current case only contains integers, timestamps and text) or possibly by a transaction isolation issue. I'll carry on hacking at this from the app side but is there anything obvious that I've missed? -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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] Forms generator ?
Stuart Adams wrote: Looking for a forms generator for a web based UI for entering/modifiying/viewing a table's records. Any recommendations ??? Thanks, Stuart I've been using OpenOffice Base to hack something needed in a hurry. Not bad with the native SDBC driver on Debian i386, problems on SPARC and PPC (form creation wizard broken, ODBC suspect) but I've not had time to investigate properly. To do the job properly I'd be using Lazarus. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] CREATE TABLE LIKE and SERIAL
What is the correct behavior of a serial column when a table is created with LIKE? The manual is silent on this. What appears to be happening with 8.2 is that the column in the new table refers to the original sequence generator. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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] CREATE TABLE LIKE and SERIAL
On Fri, Oct 30, 2009 at 8:44 AM, Mark Morgan Lloyd markmll.pgsql-gene...@telemetry.co.uk wrote: What is the correct behavior of a serial column when a table is created with LIKE? The manual is silent on this. What appears to be happening with 8.2 is that the column in the new table refers to the original sequence generator. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general silly wrote: In 8.4, the sequence value is copied only when INCLUDING DEFAULTS is specified. Otherwise, only the not null constraint is copied. I think this is the most reasonable behavior and I don't see why it should have been explicitly stated in the manual. I didn't say the sequence value, I said the sequence itself. After all the normal usage will be where the pattern table is empty. In other words the newly-created table is not completely decoupled from the pattern, the sequence is a shared resource and this is irrespective of any including/excluding specification. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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] CREATE TABLE LIKE and SERIAL
Tom Lane wrote: Thinking of SERIAL as a type is your first mistake ;-). It is not a type. It is a shorthand for making a sequence and sticking a suitable default on a plain integer column. So what LIKE sees is an integer column with a default, and it copies that. That's entirely fair, and the manual section dealing with types is very careful to start off with The data types serial and bigserial are not true types. However I think that the description of CREATE TABLE ... LIKE really could do with a health warning for this case. Looking at this very slightly deeper and assuming that the user is aware of the pitfalls, it's obviously easy for him to create a new sequence and to use it as the default value. But what if he wants the new sequence to inherit the current state of an existing one: might I suggest CREATE SEQUENCE ... LIKE would be appropriate here? -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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] Delphi connection ?
Raymond O'Donnell wrote: I fully agree I still use Delphi 6 a lot, and there's an ease of use about it that leaves other IDEs I've used in the shade. It's just a pity that it's so expensive Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- I trust you've investigated Lazarus? -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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] Delphi connection ?
John R Pierce wrote: Nico Callewaert wrote: The thing you always hear about ODBC is, that it is very slow ? ADO is significantly faster than ODBC, so the preferred stack would be delphi - ado - postgres ole db - libpq -postgres I believe there exists a delphi-ado wrapper (at least my brief googling popped one up) Granted that this is a general query issue rather than one specific to Delphi/ODBC etc., but the major speed problem that I see is where I have a clause approximately like WHERE now() - datetime = '24 hours' repeated several times in a query (e.g. in subselects or views). The easiest solution is to replace this with a function returning an timestamp which is evaluated once during query execution. The moral of the story is that even if your queries are wrapped in a fancy program generated by something like Delphi, always leave yourself a way that you can extract a representative query after any parameterisation and do an EXPLAIN to look for inefficiencies. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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] Delphi connection ?
Andy Colson wrote: Nico Callewaert wrote: Hi List, I tried already in the ODBC list, but without success... I have to develop a client/server application, heavily transaction oriented, that will serve around 100 users and database traffic will be intense (lot's of reads, updates, inserts). Development environment is Delphi 2007. I know there are a few commercial components available, but I was wondering if ODBC could do the job ? So, my question is, if ODBC is intended to be used for that ? Many simultanous connections, lots of inserts, updates ? The thing you always hear about ODBC is, that it is very slow ? Many thanks in advance, Best regards, Nico Callewaert I don't know about odbc, never used it to hit PG. I use Delphi and wrote a simple object on top of the libpq api. The api is simple to use. If odbc doesn't work out for you I'd recommend using libpq direct. I have using an older Delphi (v3), ODBC itself I believe was fine but the BDE layer introduced problems- apps needed to be rebooted evey month or so. Note that ODBC doesn't easily support any non-standard facilities, e.g. listen/notify. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Intermediate values and unprivileged users
My apologies if this is an FAQ or considered too general. I have a query like this which returns a single result: SELECT ( (SELECT avg(rel_pressure) as avg4 FROM weather WHERE now() - datetime = '4 hours' ) - (SELECT avg(rel_pressure) as avg24 FROM weather WHERE now() - datetime = '24 hours' ) ) AS diff; What I want to be able to do is have a slightly more complex query like this: SELECT ( (SELECT avg(rel_pressure) AS avg4 FROM weather WHERE now() - datetime = '4 hours' ) - (SELECT avg(rel_pressure) AS avg24 FROM weather WHERE now() - datetime = '24 hours' ) ) AS diff, CASE WHEN diff -0.1 THEN 'Falling' WHEN diff 0.1 THEN 'Rising' ELSE 'Stable' END AS tendency; i.e. the result should be a single row with two columns. Unfortunately all my attempts so far tell me that column diff does not exist. Now in most cases I could wing it using a view or temporary table, but in the current one users of the database will not have creation rights: they have to get their queries right or (eventually) use a high-level language. Is there a good way to get round this? Server is 8.2 on Linux x86, queries from psql. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PostgreSQL and eval()
I had to do some repeated ad-hoc queries yesterday for a report that was needed in a hurry, and found myself doing repeated editing of an embedded function name. If one wants to store the name of a function in a table for subsequent use in customised queries is the only way to use it by coding an eval() function using PL/pgSQL's execute? I appreciate that I could create a function on the fly as a wrapper of the one that was to be called, but again that takes manual editing. Alternatively I appreciate that I could use PL/pgSQL but that would assume that if I sent the sequence of operations to somebody else that he also had it compiled into his server. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Getting text into a table
I wonder if I could ask a question which might be marginally off-topic: how do people assemble multiple lines of text into a row in a table? I've got a number of cases where I've got a file containing some sort of activity log, where a sequence of activities extends over multiple lines. In some cases multiple activities might be interleaved, rather than each activity comprising a sequence of contiguous lines. Complicating things, related lines might be only recognisable by content. As an example, a Sendmail maillog file where a delayed outgoing message will result in a number of lines of text. I'd like each row in the table to contain the sender, recipient, eventual state, and the time it took to arrive at that state. I'm sure I'm not the only person doing this sort of thing, but there has to be a better answer than coding Perl for each job. Does anybody have any suggestions for tools well-matched to this sort of problem, i.e. that can match patterns, store matched patterns or update counters, backtrack where necessary, and so on? -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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 and eval()
Richard Huxton wrote: Mark Morgan Lloyd wrote: Alternatively I appreciate that I could use PL/pgSQL but that would assume that if I sent the sequence of operations to somebody else that he also had it compiled into his server. Well, pl/pgsql has been automatically included in all recent version of PG. You might have to issue CREATE LANGUAGE plpgsql in the database first (permissions might be an issue I suppose) but that's all. Thanks, I'd forgotten it was built by default. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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] Favorite Tom Lane quotes
Robert Treat wrote: http://archives.postgresql.org/pgsql-hackers/2006-04/msg00288.php I remember after reading this post wondering whether Tom uses caffeinated soap... Reading that link, I'm reminded of the tertiary storage code that somebody (at UCB?) grafted onto the PostgreSQL server. IIRC There were still hooks for it in v6 although they've since been removed. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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] server install recommendations?
Roberto Scattini wrote: in a couple of days i will reinstall an offline database server. It's a old HP Proliant DL580 G3 with three disks (147 GB each). Currently it has a debian Sarge in a RAID5 hardware array ( with HP Smart Array 6i, [RAID bus controller: Compaq Computer Corporation Smart Array 64xx (rev 01)] ). Make sure you do a test installation first. I'm currently tinkering with a slightly older ProLiant and find that when I move from Debian Sarge to Lenny it won't recognise SMP. This is almost certainly a kernel issue and is probably something that won't get fixed, I suspect that 2.6 quite simply doesn't like the machine's SMBIOS structure. -should i ask my boss to buy another disk? (it will be difficult, but if i can demonstrate It worth it...) With this type of hardware you should have a good stock of identical disc drives before you start. You are very likely to find that the array firmware will reject any disc which isn't within 5 or 10% of the size of the existing ones, when hardware vendors rebadge (e.g. Seagate) disc drives they knock the apparent size down to a standard value. our application (running on separate webserver) is of the type OLTP, the server will be entirely dedicated to postgresql. i will configure a warm-standby server, so the WAL files will be forwarded to another server. Again, make sure that you have spare compatible hardware- not ncessarily the same model but certainly using the same drive hardware, PSUs etc. If your boss won't spend the sort of money that these machines go for on eBay then your company isn't serious about running a service for its customers. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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] Numbering rows
David Rowley wrote: It's not 100% certain that it will be possible for 8.4, probably though. select row_number() over (order by employeeid) as nrow,* from employee order by employeeid That makes sense, thanks. So extracting rate-of-change etc. would be a join on two subselects followed by a bit of maths, that's likely to be a big incentive for an upgrade when it comes out. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Numbering rows
Is there an easy way to assign a sequential number, possibly based on an arbitrary minimum (typically 0 or 1) to each row of an ordered result set, or do I have to work with explicit sequences? I need to do quite a lot of maths on successive rows, extracting numeric and timestamp differences hence rates of change. I've typically been doing it manually or in a spreadsheet but there has to be a better way e.g. by a join on offset row numbers. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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] Numbering rows
Thanks everybody- I'm watching with a lot of interest. I was worried that I was asking something stupid with an obvious answer... ries van Twisk wrote: May be this function can help : http://www.postgresql.org/docs/8.3/static/functions-srf.html Thanks, that's already turning out to be useful for something else I was working on today: select percent, to_char(nedcar_tonnes_001(percent), '0.99') AS nedcar from generate_series(0,110,10) as percent; The one thing I'd say about generate_series() is that the description suggests that one has to put an explicit count() as the second parameter if using it to number rows, i.e. it doesn't have an as many as necessary option. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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] Numbering rows
D. Dante Lorenso wrote: PERL can remember variables in your session. Here's a function I wrote that sets a global variable in PL/PERL: Perl can do anything- that's cheating :-) Actually, I use Perl heavily but the advantage of being able to do the sort of analysis being discussed in a single query is that the query can be easily shipped with the results as a description of the method. Having to set up ad-hoc extra functions (in addition to those that are normally in the workflow) can be problematic, particularly if a recipient of the query only has restricted access. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Best practice for specifying an interval
I notice that section 9.9 of the manual has examples including both HOUR and HOURS, but the list of reserved words includes only the former. Am I correct in assuming that select time '11:00' - interval '3 hour'; is preferred to select time '11:00' - interval '3 hours'; In a similar vein, are fractional hours best specified as select time '11:00' - interval '3 hour 45 minute'; or as select time '11:00' - interval '3.75 hour'; or with the interval converted to an integer number of minutes? All these forms appear to work. I've got no intention of jumping ship but am curious as to the favoured and most portable style. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Tablespaces on tertiary media
Where does PostgreSQL stand with storing /really/ large amounts of data offline? Specifically, if a FUSE is used to move a tablespace to something like a tape archiver can the planner be warned that access might take an extended period? I know that at one point (v6?) there were hooks in the code for experimental Berkeley code to do this sort of thing but as far as I know there has never been anything publicly available. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Tablespaces on tertiary media
Gregory Stark wrote: Where does PostgreSQL stand with storing /really/ large amounts of data offline? Specifically, if a FUSE is used to move a tablespace to something like a tape archiver can the planner be warned that access might take an extended period? No, Postgres can't deal with this. You'll have to dump the tables with pg_dump or COPY or something like that and then drop them from the database. If you need them again you have to load them again. Actually if the tables are missing but nobody tries to access them (including autovacuum) then nothing will notice they're missing. But if you do try to access them you'll get an error. And if you leave it in this situation too long your database will shut down from getting too close to transaction wraparound. Thanks. If the tables were in a tablespace that was stored on something that looked like a conventional filesystem would the server code be prepared to wait the minutes that it took the operating system and FUSE implementation to load the tables onto disc? The earlier work e.g. http://www.vldb.org/conf/1996/P156.PDF apparently warned the planner about long-latency devices but that's probably unnecessary if the application program was aware that a table had been partitioned by age and accessing old data could be slow. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Tablespaces on tertiary media
Gregory Stark wrote: Thanks. If the tables were in a tablespace that was stored on something that looked like a conventional filesystem would the server code be prepared to wait the minutes that it took the operating system and FUSE implementation to load the tables onto disc? Ah, I see what you mean now. I think you might have a problem with the planner opening the files to do an lseek to measure how large they are. I'm not sure if that gets triggered before or after constraint exclusion. That's the only problem I can think of. The size could be stored in the catalogue though. However at that point I guess that anything that was used before constraint exclusion would have to be in the catalogue and anything after would have to initiate retrieval from tertiary media if it's not already cached. The earlier work e.g. http://www.vldb.org/conf/1996/P156.PDF apparently warned the planner about long-latency devices but that's probably unnecessary if the application program was aware that a table had been partitioned by age and accessing old data could be slow. Well it's not like there are any alternative plans that will avoid the need to access the data at all. I assume the FUSE setup will always have to load the entire file so there's no even any difference between indexed and sequential access. (Unless the table is over 1G in which case you might want to avoid sequential scans if index scans would avoid accessing some segments.) I'd imagine in most cases that sequential scan time would be dwarfed by medium-load and seek time. It would be important here that the server didn't time out assuming that it had hit a hardware problem when in actual fact the table was still being pulled from tape. I'd presume that when Sarawagi (who I believe is now with IBM) was doing the work that there wasn't a straightforward way to partition tables (as is currently described in section 5.9 of the manual) so she had to add internal hooks. Now granted that I don't pretend to really understand how things work (I'm a luser, not a guru) but it seems to me that it would not be difficult to extend the tablespace definition from CREATE TABLESPACE tablespacename LOCATION '/directory' to something like CREATE TABLESPACE tablespacename LOCATION '|check_loaded.pl /directory' where the check_loaded.pl script could check that the table was cached and return its name when available. However I guess that the script would probably need to see the initial lseek or whatever as well... there's probably a whole lot of non-obvious details that I've totally overlooked. Just my 2d-worth :-) -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Tablespaces on tertiary media
Ron Johnson wrote: I know that at one point (v6?) there were hooks in the code for experimental Berkeley code to do this sort of thing but as far as I know there has never been anything publicly available. While tertiary media certainly was relevant 10 years ago, is it really necessary in 2007? A couple of MSA-1000s stuffed with 1TB disks would hold an l-o-t *lot* of historical data. I was considering it from the point-of-view of completeness rather than anything else, but as a specific example I seem to recall that one of the particle accelerator sites uses PostgreSQL for cataloging captured data but actually stores it on either tape or optical disc (I forget which). I'm sure that there would be advantages to being able to retrieve both metadata and data using the same API, rather than using database queries for the former and something like an AMANDA-compatible interface for the latter. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Locking for function creation
Richard Huxton wrote: Mark Morgan Lloyd wrote: If there's a risk that multiple clients will try to execute a 'create or replace function' simultaneously, what's the recommended practice for putting it in a transaction and/or locking it? If a lock's incolved what should this be applied to- the table that the function is most likely to be involved with, an arbitrary table, or a dummy table specifically reserved for this purpose? What problem are you trying to prevent here? Do you want a particular version of the function to be available for a certain amount of time? I don't anticipate that the function will change, but it's (re)defined by a script triggered periodically on a client system. I'm pretty sure that I've seen a problem whilst I was doing maintenance when two clients tried to redefine it simultaneouly (i.e. on one of them the redefinition failed rather than waiting), in the interim I've set up a transaction with a lock on the table that is most likely to be involved noting that by default the lock type is the most restrictive. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Locking for function creation
Richard Huxton wrote: Was it tuple concurrently updated? You can reproduce this fairly simply by issuing BEGIN...CREATE OR REPLACE FUNCTION f... in two different psql sessions and delaying COMMIT appropriately. AFAIK it's harmless, but does abort your transaction. I /think/ so, but it was buried deep in custom scripting and (usual story) I was under pressure to get something else done at the time :-) in the interim I've set up a transaction with a lock on the table that is most likely to be involved noting that by default the lock type is the most restrictive. You probably want a userlock (see contrib/), or as low-impact a lock as you can get away with. Perhaps lock your dummy table (row contains function schema/name?). You'll still want to code your application in such a way that it copes with errors though - the lock attempt can always time out (in theory anyway). Thanks, noted. I'm not expecting this to be a regular occurence since in general the only time multiple sessions will be running will be during maintenance. If I might ask a related question- assuming that a client has grabbed a restrictive lock during a transaction that e.g. is create/replacing functions, what happens to other sessions that attempt to run a select or update- will they fail (i.e. an implicit NOWAIT) or will they wait until the lock is released? Does this vary depending on whether a select/update is within an explicit transaction? -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Locking for function creation
Richard Huxton wrote: Mark Morgan Lloyd wrote: If I might ask a related question- assuming that a client has grabbed a restrictive lock during a transaction that e.g. is create/replacing functions, what happens to other sessions that attempt to run a select or update- will they fail (i.e. an implicit NOWAIT) or will they wait until the lock is released? Does this vary depending on whether a select/update is within an explicit transaction? They will wait without an explicit NOWAIT. This applies whether the transaction is explicit or implicit. Just what I was hoping. Many thanks for your help :-) -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Locking for function creation
If there's a risk that multiple clients will try to execute a 'create or replace function' simultaneously, what's the recommended practice for putting it in a transaction and/or locking it? If a lock's incolved what should this be applied to- the table that the function is most likely to be involved with, an arbitrary table, or a dummy table specifically reserved for this purpose? -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Converting a timestamp to a time
I'm in the middle of moving a production database from 7.1 to 8.1 and have hit a slight problem. On the old system I've got a query including datastamp AS datastamp, date(datastamp ) as datadate, time(datastamp ) as datatime, status, -- etc. This is actually generated on the client to possibly include timezone correction. Obviously this has worked fine for a number of years on 7.1, but 8.1 is objecting to the time() cast. What is the correct (or even any :-) way of converting a timestamp into a time (without timezone etc.)? Any suggestions would be much appreciated. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Converting a timestamp to a time
Andreas Kretschmer schrieb: Mark Morgan Lloyd [EMAIL PROTECTED] wrote: What is the correct (or even any :-) way of converting a timestamp into a time (without timezone etc.)? You can CAST it: test=# select now(); now --- 2006-11-05 11:16:05.205235+01 (1 row) test=# select now()::time; now 11:16:18.22527 (1 row) Thanks Andreas, I've ended up using CAST( ... AS TIME). I think the :: notation might be fragile in this instance because of the machine-generated SQL which gets a bit hairy in places. I've also had to replace INTERVAL() with CAST( ... INTERVAL) wherever it occurs and replace the result of a function with TIMESTAMP WITH TIME ZONE... hopefully that won't mess anything up, the server is aggresively GMT since we have to deal with several timezones simultaneously and it's the only way I could work it. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Converting a timestamp to a time
Andreas Kretschmer schrieb: Mark Morgan Lloyd [EMAIL PROTECTED] wrote: test=# select now()::time; now 11:16:18.22527 (1 row) Thanks Andreas, I've ended up using CAST( ... AS TIME). I think the :: notation might be fragile in this instance because of the machine-generated SQL which Thats okay, because my version (the ::cast) is a PostgreSQL-feature, but the cast(... as ...) is more SQL-conform. Thanks for that, feedback on best practice is always useful. I must admit that the only server I've used before pg was the SOLID Server (using Bonzai Tree technology), but they changed their licensing terms which made it impractical. I looked briefly at MySQL which in those days didn't support transactions, apart from that we decided that we didn't fancy waving the incredibly-tacky name in front of our customers :-) -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] NT4 client problem
Sim Zacks wrote: I have a very strange problem that I cannot figure out and was hoping that someone else may have at least seen this problem because it makes no sense at all to me. I have a PostGreSQL 8.0.1-r1 server on gentoo. The data is accessed by MSAccess clients using linked tables. After a good amount of time, one user complains that the data is not working well. For example, he changes a value in a drop down box and it looks like it works, but if he opens it and closes it then the old value is there. He has done a reboot on his machine and that doesn't solve the problem. The only thing that solves the problem is to reboot the server. All of the other clients are Windows 2000 and nobody else has this problem. I even checked the exact problem that he was having on a Windows 2000 machine and the problem didn't occur. I checked the WinNT computer right after that and the problem still occurred. All the clients are using the ODBC driver 8.00.01.01 from Mar-05-2005 Could anyone could shed some light on this? There's been discussion of various problems relating to NT4W machines and possibly also '98 on the psql-odbc ML. I'm helping out in a very small way by doing some testing, I suggest you join us there. Current driver version is 8.02.0101 possibly with an updated DLL from Inoue-san's website. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Procedural language functions across servers
Michael Fuhr wrote: dbi-link is an alternative to dblink that uses Perl/DBI: http://pgfoundry.org/projects/dbi-link/ is this the only way available if additional procedural languages are installed? With the untrusted version of a language you can do essentially anything that language supports. For example, with plperlu, you could use DBI to open a connection to another database (even another DBMS like Oracle, MySQL, etc.), issue a query, fetch the results, and do whatever you want with those results. Example: CREATE FUNCTION remote_version(text, text, text) RETURNS text AS $$ use DBI; As a related question, assume I have PostgreSQL on an application server X with functions a() and b() defined to use some method (dblink or whatever) to return a result set from a remote backend server. If X runs a complex query including references to a() and b() which themselves initiate complex queries on backend servers A and B, can I tell X's planner to run a(A) and b(B) simultaneously, or is the only way to have these in distinct sessions storing their results in tables on X? -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Procedural language functions across servers
I know that the FAQ says that the only way to implement a query across databases is to use dblink, is this the only way available if additional procedural languages are installed? For example, assume I have a production server A that does not have PL/Perl installed, and a hacker's server B (let's assume this is a separate machine) complete with every possible bell and whistle. If I want to apply Perl (in a read-only fashion) to extract data from the production tables is the best way to have a PL/Perl function including references to dblink functions? Similarly, if I have PostGIS or PL/R on the hacker's server, or- heaven forfend- both, is the best way to get at the production server still to use dblink? -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
