Re: [GENERAL] Naming conventions for column names
On 11/6/17 05:36, Sachin Kotwal wrote: > Is there any special reason to keep column names as usesysid > and usename instead of usersysid and username in below system View? The reason to *keep* them is compatibility. The reason they are like that to start with is because that is the naming pattern used in the system catalogs: 3 letters indicating the catalog, plus additional letters or words. It is useful to use the same name in views such as pg_stat_replication, so you can easily join different views and catalogs. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgres 10 manual breaks links with anchors
On 10/16/17 03:19, Thomas Kellerer wrote: > I don't know if this is intentional, but the Postgres 10 manual started to > use lowercase IDs as anchors in the manual. > > So, if I have e.g.: the following URL open in my browser: > > > https://www.postgresql.org/docs/current/static/sql-createindex.html#sql-createindex-concurrently > > I cannot simply switch to an older version by replacing "current" with e.g. > "9.5" because in the 9.5 manual the anchor was all uppercase, and the URL > would need to be: > > > https://www.postgresql.org/docs/9.5/static/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY A fix for this has been committed. Once 10.1 comes out (next week), the old-style anchors will work again. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Monitoring of a hot standby with a largely idle master
On 7/27/17 16:14, Jeff Janes wrote: > On Wed, Jul 26, 2017 at 1:44 AM, Michael Paquier > <michael.paqu...@gmail.com <mailto:michael.paqu...@gmail.com>> wrote: > > On Mon, Jul 24, 2017 at 9:08 PM, Jeff Janes <jeff.ja...@gmail.com > <mailto:jeff.ja...@gmail.com>> wrote: > > On Sun, Jul 16, 2017 at 8:47 AM, Michael Paquier > <michael.paqu...@gmail.com <mailto:michael.paqu...@gmail.com>> > > wrote: > >> What do you think about the patch attached? > > > > Looks OK. Should it mention specifically "On a hot standby" rather > than "On > > a standby"? Otherwise people might be left confused on how they are > > supposed to do this on a generic standby. It is the kind of thing > which is > > obvious once you know it, but confusing the first time you encounter it. > > Yes, right. Let's update as you suggest. > > > new version looks good. committed I changed to links to xrefs, which automatically generated the correct target texts. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Making subscribers read only in Postgres 10 logical replication
On 10/11/17 14:18, rverghese wrote: > I'm testing out logical replication on PostgreSQL 10. Is there a setting to > make subscribers read-only slaves like with Slony. Currently I can insert > into the Publisher and the Subscriber. If there is a conflict, i.e. same > record exists in both, then all replication gets backed up (even to other > tables) till that one record is resolved. Right now there is no direct way to do that. The other answers have suggested some workarounds. It might be a valuable feature to implement something like that. One would just have to think through exactly how to present this in the user interface. Another longer-term solution here is to implement conflict resolution mechanisms. So if you don't like local updates to break the incoming replication stream, a remote-update-wins policy would help. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Get user defined type OID (PostgreSQL extension in C)
On 8/25/17 14:34, Fabiana Zioti wrote: > I'm developing an extension to PostgreSQL using C. I created a > user-defined type called geo_trajc_elem. I also created a function to > generate an array of this new type. > In this case, it is not possible to use get_fn_expr_argtype because I am > not passing the new type as argument but creating it in the function > itself, correct ? The array type for a base type is automatically created by CREATE TYPE. So the type is already there. What you are (possibly) doing is constructing a value of that array type. But then the OIDs of all the types involved don't change, and you can use the standard functions to look up the types of your arguments and the associated array types. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Invalid magic number 0000 in log file
On 8/25/17 14:24, Moreno Andreo wrote: > I know that's related to XLOG files, but do not have idea on how to deal > with it. I had a quick googling but found nothing but "your files are > corrupted. You'd better initdb." That's pretty much it. You might not have to initdb if you have an uncorrupted copy of the WAL files somewhere (i.e., a backup). But then I would have concerns about what else might be corrupted. > Then I bumped into this (old) post from Alvaro > > https://www.postgresql.org/message-id/20070515173401.gb12...@alvh.no-ip.org > > If it's applicable, is it still valid or too many things have changed? That doesn't seem related. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Adding a new Clause in the Source Code
On 8/14/17 04:14, Stefan Wagner wrote: > I would really appreciate if someone could hack me a quick example of a > new clause lets call it ADDITIONAL with Keywords HIGH, LOW to order > columns. I just want the construct. What > the Keywords accomplish and the Algorithm is something I'm gonna try to > figure out on my own. I just need to know in which File and which Line I > need to enter my Code. For projects like this, figuring out all the places where to change things is often half the work. If I were to approach this, I would see how ORDER BY is implemented and track all those places down and then see whether I need to make analogous changes there. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Fwd: 2 process postgres -D for one instance
On 8/11/17 10:15, Murtuza Zabuawala wrote: > some time whe have 2 process postgres for 1 instance like this > > > exppgs 17769 1 0 01:06 ?00:01:04 > /usr/pgsql-9.3/bin/postgres -D /bases/postgresql/scl/data -i -p 5450 -h > bd-sillage.info. > exppgs 39922 17769 0 15:39 ?00:00:00 > /usr/pgsql-9.3/bin/postgres -D /bases/postgresql/scl/data -i -p 5450 -h > bd-sillage.info. It appears that the second one is a process forked off from the first one. That looks normal to me. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Compiling libpq only on Linux
On 8/8/17 14:49, Igor Korot wrote: > Quick question - what is the best way to compile libpq only on Linux? > Should I grab it, unpack it and do configure and then make inside > libpq directory > manually? Pretty much yes. > Or there is some other way? You could look for updated distribution packages, even if they are not directly from your vendor. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [HACKERS] [GENERAL] Not able to create collation on Windows
On 8/1/17 10:53, Tom Lane wrote: > Murtuza Zabuawala <murtuza.zabuaw...@enterprisedb.com> writes: >> I am trying to create collation on windows using default POSIX collation >> with pgAdmin3 but I am getting error as shown in screenshot, Can someone >> suggest how to fix this? > >> *Syntax:* >> CREATE COLLATION public.test from pg_catalog."POSIX"; > >> *Error:* >> ERROR: could not create locale "POSIX". No error > > Hmm. Evidently Windows' _create_locale() doesn't accept "POSIX". > You might find that "C" works instead, don't know for sure. > > I think this is actually a bug, because the collations code clearly > means to allow clones of the C/POSIX locales --- see eg lc_collate_is_c, You seem to say that we should support a "POSIX" locale even on systems where the C library does not support that. I'm not convinced about that. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pglogical vs. built-in logical replication in pg-10
On 6/22/17 04:21, Andreas Joseph Krogh wrote: > 1. Why should one prefer built-in logical replication in pg-10 to > pglogical, does it do anything pglogical doesn't? > It seems pglogical is more feature-rich... You are right that pglogical has more functionality. Much of that functionality can be expected to trickle into core eventually. One advantage of the in-core feature is that the initial table synchronization can be parallelized, which can make the initial setup faster and more robust. pglogical will probably support that too at some point once PG10 is out. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: Fwd: [GENERAL] Streaming replication bandwith per table
On 6/21/17 22:04, Maeldron T. wrote: > * Logical replication is in 10.0 Beta 1. I might be oldschool but I > would install 10.1 or maybe 10.0.2 into production There are also other logical replication options such as pglogical and londiste. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Streaming replication bandwith per table
On 6/19/17 20:50, Maeldron T. wrote: > Streaming replication generates too much traffic to set it up between > different regions for financial reasons. The streaming replication would > cost more than every other hosting expense altogether (including every > the traffic, even though it’s web and huge amount of emails). > > Is there a way to see in the log how much bandwidth is used per table? Not easily. You could play around with pg_xlogdump to see what's going on in the WAL. But even if you figure it out, there is not much you can do about it. Try perhaps logical replication. That would save you the bandwidth for updating all the indexes at least. It might work for you. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] relation create time
On 5/10/17 12:05, Hu, Patricia wrote: > I am trying to find out when a table was created in postgresql. Thought > it would be easy (coming from Oracle world), but haven’t had any luck, > especially since we are on RDS and can’t peek at the timestamp on the > file system. Is this information stored anywhere in the catalog? It is not. > Or I > need to store it myself? Is there any plan to add such meta data > information to the catalog as a feature? Thanks a lot! You could write an event trigger to record it. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] character encoding of the postgres database
On 5/10/17 11:48, Sandeep Gupta wrote: > Currently, the postgres database by has SQL_ASCII encoding. > Is it possible to start the postgres database with UTF-8 encoding, instead > of modifying it later. This is done when initdb is run, with the --locale and/or --encoding option. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PQerrorMessage documentation
On 5/3/17 21:23, Igor Korot wrote: > Hi, ALL, > [quote] > Nearly all libpq functions will set a message for PQerrorMessage if > they fail. Note that by libpq convention, a nonempty PQerrorMessage > result can consist of multiple lines, and will include a trailing > newline. The caller should not free the result directly. It will be > freed when the associated PGconn handle is passed to PQfinish. The > result string should not be expected to remain the same across > operations on the PGconn structure. > [/quote] > > Since there may be multiple errors, I presume that in this case the string > will end with just \0, correct? It's not going to be \0\0 like with MSVC. I don't know what you mean by \0\0 with MSVC, but it is correct that the error message string will end with \0, like any C string. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Logical replication
On 5/1/17 10:32, Adrian Klaver wrote: > On 04/30/2017 09:07 AM, Adrian Klaver wrote: >> I have started looking at the logical replication feature in Postgres >> 10. One thing I have no been able to determine is the interoperability >> between it and pglogical(www.2ndquadrant.com/en/resources/pglogical/). I >> know the one is derived from the other, what I can not find is whether a >> Postgres 9.4 instance with the pglogical extension installed can >> communicate with a Postgres 10 instance using the built in code? > Some testing says the answer is no: correct > If I am correct, this means from 9.4 <--> 10 and points in between you > would need to use the pglogical extension on both ends. correct > Going from 10 > --> you could use the builtin logical replication. This leads to another > question. Is is possible to use both at the same time?: > > 9.4 ---> 10(instance 1)---> 10(instance 2) > pglogicalpglogical > builtinbuiltin That is possible. pglogical will continue to exist, so you can also keep using it if you already have it. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] AEXPR_OR, AEXPR_AND is not in postgres 9.6, how can I rewrite where it used in 9.3 ?
On 4/4/17 23:28, lin wrote: > all. I use "AEXPR_OR", "AEXPR_AND" in postgres 9.3 ,now I update the > postgres version to 9.6, but the 9.6 version has not the "AEXPR_OR", > "AEXPR_AND". > How can I solve the problem ? See commit 2146f13408cdb85c738364fe8f7965209e08c6be about how the internal representation was changed. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] What's the benefit (or usage scenario) of a "typed table"?
On 12/31/16 10:34 AM, Thomas Kellerer wrote: > I recently stumbled over "typed tables" in Postgres > (there were several questions containing this on stackoverflow recently) > > create type some_type as (id integer, data text); > create table some_table of some_type; > > I wonder what the benefit of a typed table is and when this would be useful? One use is with PL/Proxy. You create the type on the proxy, thus allowing you to define functions using the type. Then create the table on the backend from the type, thus ensuring they are the same. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Recovering data from an old disk image
On 7/15/16 11:37 AM, Richard Kuhns wrote: > I've copied the entire pgsql directory to a new machine & installed the > most recent 9.3 to try to read it. When I start the server it tells me > that the database was initialized by version 9.4, so it can't handle it. > > I uninstalled 9.3 & installed the most recent 9.4. When I try to start > it, it tells me: > > postgres[99770]: [1-1] FATAL: database files are incompatible with server > postgres[99770]: [1-2] DETAIL: The database cluster was initialized > with PG_CONTROL_VERSION 937, but the server was compiled with > PG_CONTROL_VERSION 942. > > Based on a search of the mailing list archives I'm guessing that the > original postgresql server was a 9.4 beta. Yeah, based on that it looks like it might have been 9.4beta1. If you can't find a tarball for that anywhere anymore, you can check out the tag REL9_4_BETA1 from git and built it yourself. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Ubuntu/Debian PGDP
On 5/7/16 2:43 AM, Vincenzo Romano wrote: In a fresh new install of PostgreSQL 9.5.2 on Ubuntu 16.04 I am getting this: ... Setting up postgresql-9.5 (9.5.2-1.pgdg16.04+1) ... Unescaped left brace in regex is deprecated, passed through in regex; marked by <-- HERE in m/(? This issue is known and being worked on. It's only a deprecation warning, so you can ignore it for now. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pgpass (in)flexibility
On 9/15/15 1:48 AM, Ben Chobot wrote:
> We're in a situation where we would like to take advantage of the pgpass
> hostname field to determine which password gets used. For example:
>
> psql -h prod-server -d foo # should use the prod password
> psql -h beta-server -d foo # should use the beta password
>
> This would *seem* to be simple, just put "prod-server" or "beta-server" into
> the hostname field of .pgpass. But if somebody uses the FQDN of those hosts,
> then the line does not match. If somebody uses the IP address of those hosts,
> again, no match. It seems that the hostname must match the hostname *exactly*
> - or match any host ("*"), which does not work for our use case.
>
> This seems to make the hostname field unnecessarily inflexible. Has anybody
> else experienced - and hopefully overcome - this pain? Maybe I'm just going
> about it all wrong.
The alternative would be to do a double host name resolution before
every connection that asks for a password, which would probably also
have some concerns.
I note, for example, that the OpenSSH configuration also goes by the
host name as you wrote it, and then has additional options to
canonicalize host names. That might be something to look into.
--
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] [ADMIN] How Many PG_Locks are considered too many
On 7/30/15 6:13 AM, Renato Oliveira wrote: We have a Nagios plugin, which monitors pg_locks and almost daily we see 3000 to 4 pg_locks. Can we just ignore them, can we let them grow without worrying? How many pg_locks are considered unsafe for any given postgres server? That depends on how many concurrent clients you have and what they are doing. Every table access will at least create a share lock of some kind, so if you have a lot of activity that does a lot of things, you will see a lot of locks, but that doesn't impact database performance in a significant way. I don't think monitoring the absolute number of locks is useful. You might want to chart it, to compare over time. If you want to monitor locks, you could monitor lock waits, which you can get by checking the server log. -- 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/python composite type array as input parameter
On 5/18/15 10:52 AM, Filipe Pina wrote: But one of the functions I need to create needs to accept an array of records. PL/Python doesn't support that. Some more code needs to be written to support that. You did everything correctly. I don't know of a good workaround. -- 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] Change UUID type default output representation
On 5/28/15 5:35 PM, Randall Lucas wrote: Can I compile my own version of uuid_out and update the system catalogs, or create a uuid_dashless type that uses my own custom uuid_dashless.c that's hacked to remove dashes? Either one would work. -- 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] Replicate over pgbouncer?
On 5/21/15 12:12 PM, Andomar wrote: Hi, Today I installed pgbouncer. I added a second installation as a hot standby. Before starting the standby, I configured recovery.conf to connect to pgbouncer. This results in an error message: Pooler Error: Unsupported startup parameter: replication Is it possible to replicate over a connection through pgbouncer? Currently not. -- 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] RPM building tools and info missing?
On 5/9/15 10:47 AM, Bill Moran wrote: https://wiki.postgresql.org/wiki/RPM_Packaging The link to the specfiles and other data at http://svn.pgrpms.org/repo/ gives a 404. It's been move to git. I have updated the wiki page with the new URL. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Running pg_upgrade under Debian
On 4/20/15 6:09 PM, Bruce Momjian wrote: On Mon, Apr 20, 2015 at 07:06:37PM -0300, Alvaro Herrera wrote: ISTM there's a documentation bug here: in the code, the dump method checks for tablespaces and raises an error if they are found, but the upgrade method does not check. I think the documentation should state that only the dump method does not support tablespaces. OK, it would be nice if someone could report that to upstream Debian. I am a little confused why pg_dump/pg_restore can't use tablespaces though. Years ago we used to not use PG-major-version-specific subdirectories in tablespaces, but we added that for pg_upgrade, and I am sure they would work fine for pg_dump too. https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=772202 -- 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] pitr archive_command cp fsync
On 3/14/15 3:27 PM, Миша Тюрин wrote: should we add disclaimer in pitr documentation about cp and fsync? cp does not fsync. and dd for example can do fsync. only on some platforms -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] SELECT, GROUP BY, and aggregates
On 2/13/15 1:48 PM, Jeff Janes wrote: I waste an inordinate amount of time retyping select lists over into the group by list, or copying and pasting and then deleting the aggregate clauses. It is an entirely pointless exercise. I can't fault PostgreSQL for following the standard, but its too bad the standards aren't more sensible. An extension like GROUP BY ALL might be useful, without breaking much. Also note that you can group by primary key only. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] anyone using oid2name?
pgsql-hackers are discussing some housekeeping in contrib. Is anyone using the oid2name tool? Otherwise, we might deprecate and eventually remove it. -- 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] anyone using oid2name?
On 12/12/14 9:25 AM, Bruce Momjian wrote: On Fri, Dec 12, 2014 at 09:18:53AM -0500, Peter Eisentraut wrote: pgsql-hackers are discussing some housekeeping in contrib. Is anyone using the oid2name tool? Otherwise, we might deprecate and eventually remove it. Uh, if we remove it, what tool does someone use from the command-line to find the objects represented by files? I don't know. I want to find out what people are using it for. One option would be to tweak some psql backslash commands to show the information. Most of the functionality is already there. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_upgrade and ubuntu
On 11/14/14 2:10 PM, Jonathan Vanasco wrote: I ran into an issue migrating from 9.1 to 9.3 on ubuntu using pg_upgrade the default ubuntu package, and the one from postgresql.org, both store `postgresql.conf` in etc as `/etc/postgresql/VERSION/main/postgresql.conf` however, the pg_upgrade script expects it in the `datadir`. the simple solution seems to be just symlinking the /etc files into the data dirs. it took me a while to realize this was the error. it might make sense to upgrade the docs with a note about what should be in the data dir to enable an upgrade. On Debian/Ubuntu, use pg_upgradecluster --method upgrade. See the man page for details. -- 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/Python prepare example's use of setdefault
On 10/15/14 5:56 PM, Tom Lane wrote: Hm ... this was changed in commit 6f6b46c9c0ca3d96. Peter, did you consider efficiency here? Fixed. -- 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/Python prepare example's use of setdefault
On 10/15/14 5:58 PM, Jonathan Rogers wrote: BTW, I would rewrite the 9.1 example to be shorter while behaving the same: CREATE FUNCTION usesavedplan() RETURNS trigger AS $$ plan = SD.get(plan) if plan is None: If we're going for shortness, how about if not plan: ? SD[plan] = plan = plpy.prepare(SELECT 1) and here maybe plan = SD[plan] = plpy.prepare(SELECT 1) to emphasize the assignment to plan? # rest of function $$ LANGUAGE plpythonu; -- 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] Why isn't Java support part of Postgresql core?
On 9/15/14 1:30 PM, cowwoc wrote: Any chance you guys could help cleaning up the build/deploy process? This is a pretty big hurdle to overcome for new users. I'm glad to hear that PL/Java is still working well for some people. Last I saw it was stuck in a transition of the build system from make to maven, and the documentation was inconsistent either way. It looks like with a little non-coding help this could be cleared up. -- 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] Why isn't Java support part of Postgresql core?
On 9/15/14 1:46 PM, Pavel Stehule wrote: I am strong sceptic. There is relative slow progress in JDBC driver, that is 100x more important project than PL/Java - so It is hard to believe, so there can be 3 developers, who start work on PL/Java. Stupid, completely offensive guess: Most Java programmers work in enterprise environments and are not allowed to or used to contributing to open source? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Re: PostgreSQL 9.3 XML parser seems not to recognize the DOCTYPE element in XML files
On 5/29/14, 11:59 AM, Bob Moyers wrote: When I try this update: UPDATE REPORT_STYLE SET JASPER_STYLE = XMLPARSE(DOCUMENT ?) WHERE (REPORT_STYLE_NAME = ?) I get: org.postgresql.util.PSQLException: ERROR: invalid XML content Detail: line 2: StartTag: invalid element name !DOCTYPE jasperTemplate PUBLIC -//JasperReports//DTD Template//EN http://jas This could be a problem with the JDBC driver's handling of xmlparse. Maybe you could help some debugging help on their mailing list. Also check the server log. Maybe you can see there what the server actually received. -- 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] XML validation of whitespace values
On 3/14/14, 11:12 AM, Tim Kane wrote:
clone=# select xml_is_well_formed(' ');
xml_is_well_formed
t
(1 row)
clone=# select xpath_exists (‘//test', ' ');
ERROR: could not parse XML document
DETAIL: line 1: Start tag expected, '' not found
There are several issues at work here:
- contrib/xml2 has a slightly different notion of what is an OK xml
value than the built-in xml type.
- A string consisting of whitespace is well-formed XML content, but not
a well-formed XML document. Compare xmlparse(document ' ') vs
xmlparse(content ' '). contrib/xml2 (apparently) follows the latter
interpretation.
- xpath queries only work properly on XML documents.
It might be better if you wrote your code without using contrib/xml2,
and instead caught any parse exceptions in, say, plpgsql code.
--
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] pgsql and asciidoc output
On 2/11/14, 6:25 PM, Vik Fearing wrote: I personally find Markdown to be more pleasing to the eye than AsciiDoc. Markdown can embed HTML tables, so there is nothing that we need to implement. -- 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] concurrent SELECT blocking ALTER?
On 1/29/14, 4:59 PM, Neil Harkins wrote: Why are those exclusive locks present? Can't the database rely on mvcc for those reads without locking? The autocommit should be increasing the xid used for the reads, so the ALTER should be able to slip in-between? One would think so, but it's more complicated. There is a long thread on pgsql-hackers spreading over many months that discusses the intricacies of reducing the strength of the locks taken by DDL commands. This is being addressed, but at the moment most DDL commands take exclusive locks. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] commit fest 2014-01 wants reviewers
Commit fest 2014-01, the fourth and final commit fest in the PostgreSQL 9.4 development cycle, has started. What is a commit fest? https://wiki.postgresql.org/wiki/CommitFest As before, we need more people to help review submitted patches. How do you help reviewing? https://wiki.postgresql.org/wiki/Reviewing_a_Patch To sign up, go to https://commitfest.postgresql.org/action/commitfest_view?id=21 and put your name down for any patch you like. If you'd like to help but don't know which patch to take or have other questions, send me an email and I'll try to set you up. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Does LC_CTYPE affect performance, index use?
On Wed, 2013-11-20 at 14:57 -0500, Steven Dodd wrote: I've read that setting LC_COLLATE to something other than C / POSIX negatively affects performance, and disables use of indexes for LIKE, etc... It doesn't disable the use of indexes, you just need to create different indexes. Does the same apply to LC_CTYPE? Yes. I am considering setting LC_COLLATE = C, and LC_CTYPE = en_US.UTF-8, and using LOWER() to case-fold strings for sorting, and equality. The motivation for setting LC_CTYPE = en_US.UTF-8, instead of C, is to gain at least some degree of case-folding for international characters. Does this sound like a reasonable plan? No, if you need internalized behavior, then set both LC_COLLATE and LC_CTYPE to en_US.UTF-8 (or some other suitable locale) and check the relevant documentation sections about how to create the right indexes. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] commit fest 2013-11 wants reviewers
Commit fest 2013-11, the third commit fest (out of four) in the PostgreSQL 9.4 development cycle, will start this Friday, November 15. What is a commit fest? https://wiki.postgresql.org/wiki/CommitFest We always need more people to help review submitted patches. You don't have to be an elite hacker to do that. How do you help reviewing? https://wiki.postgresql.org/wiki/Reviewing_a_Patch (Don't be overwhelmed by that page. Any kind of feedback is ultimately helpful.) Great, where do you sign up? https://commitfest.postgresql.org/action/commitfest_view?id=20 If you'd like to help but don't know which patch to take or have other questions, send me an email and I'll try to set you up. -- 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] Clang 3.3 Analyzer Results
On 11/11/13, 1:33 AM, Jeffrey Walton wrote: The analyzer is reporting some findings, and some of the findings look legitimate. We have been tracking clang scan-build results for some time, and fixed quite a few of them. Most of the remaining ones are false positives. Maybe there are still a few that could be fixed, but certainly scan-build is not suitable as an acceptance test of the PostgreSQL source at this point. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_upgrade 9.1.9 -9.3.1
On 10/25/13, 7:20 AM, Marc Mamin wrote: Hello, I'm evaluating pg_upgrade and there seems to be something wrong with my test: the data get copied within the old data directory instead of the new one Do I have to explicitely set more option or define some environment variables ? If this is of concern, there are some redirections with symlinks within the old $PGDATA ./pg_upgrade \ --old-datadir /data/postgresql-data-9\ --new-datadir /pgdata/postgresql_93-data-9\ --old-bindir /opt/intershop/postgresql-9.1.9-9/bin\ --new-bindir /opt/intershop/postgresql-9.3.1-9/bin = ll /data/postgresql-data-9/tblspc_data/cicpg_logs/ drwx-- 3 isdb9 isgrp9 4096 Oct 21 15:48 PG_9.1_201105231 drwx-- 3 isdb9 isgrp9 4096 Oct 25 12:26 PG_9.3_201306121 It appears you are using tablespaces. In that case, that's normal. -- 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] Links in docs broken
On Sat, 2013-10-19 at 02:17 +0900, Amit Langote wrote: Hi, In 9.2 docs, the first link (i18ngurus) in the further reading section here: http://www.postgresql.org/docs/9.2/static/multibyte.html seems to be broken. Should it be updated/removed? (I see it's removed in 9.3 docs) I have backpatched the change from 9.3 now. -- 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] Analyze during a transaction
On 10/18/13 12:28 PM, bobJobS wrote: If I analyze our database during a transaction and the transaction fails (rollback occurs), with the table statistics rollback to their original values? Yes. ANALYZE isn't really that special. It reads data from some tables, does some math on it, and writes the results to other tables (pg_statistic). All of that is subject to transaction semantics. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Call for translations
In anticipation of the release of PostgreSQL 9.3, it is once again time to update the message translations. We are now in a string freeze, which has traditionally been associated with the first release candidate, so it's a good time to do this work now. If you want to help, see http://babel.postgresql.org/ for instructions and other information. If there are already active translation teams, please communicate with them first. The mailing list pgtranslation-translat...@pgfoundry.org is available for general discussion and coordination of translation activities. -- 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/Python runtime overhead
On 8/7/13 10:43 AM, Seref Arikan wrote: When a pl/python based function is invoked, does it keep a python runtime running across calls to same function? That is, if I use connection pooling, can I save on the python runtime initialization and loading costs? The Python interpreter is initialized once during a session, normally when the first PL/Python function is called. So yes, connection pooling can be helpful here. Are there any documents/books etc you'd recommend to get a good understanding of extending postgres with languages like python? I'd really like to get a good grip of the architecture of this type of extension, and possibly attempt to introduce a language of my own choosing. The docs I've seen so far are mostly too specific, making it a bit for hard for me to see the forest from the trees. The basic documentation is here: http://www.postgresql.org/docs/devel/static/plhandler.html. The rest is mainly experience and copying from existing language handler implementations. -- 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] pgxs question - linking c-functions to external libraries
On 6/6/13 11:49 PM, Rad Cirskis wrote: Hi John, have you managed to get it to link with external shared libs? Sure, many extensions to that. Do something like SHLIB_LINK += -lfoo in your Makefile. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_upgrade -u
On 5/21/13 2:41 PM, Bruce Momjian wrote: I have thought about this and there are potentially several options specified to pg_upgrade that could be passed into scripts: -O, --new-options=OPTIONS new cluster options to pass to the server -P, --new-port=NEWPORTnew cluster port number (default 50432) -u, --user=NAME cluster superuser (default root) However, if we pass these items into the scripts, we then force these values to be used, even if the user wants to use a different value. It is a balance between supplying defaults vs. requiring the user to supply or change the values used during the ugprade. At this point, I have favored _not_ supplying defaults in the script. Do you have an alternative argument in favor of supplying defaults? You could put environment variable assignments at the top of the script, so they are easy to change or remove. But it seems to me the values should be in there somehow. -- 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] Fast Shutdown (SIGINT) results in a CHECKPOINT...
On 3/20/13 4:28 PM, Sean Chittenden wrote: For a while now I've known that sending a SIGINT can trigger a CHECKPOINT, but I don't know if this is intentional or a bug. It's intentional. If you don't want that, use SIGQUIT. That's how they are different. Of course, when using SIGQUIT, you will have to spend the time you saved on the checkpoint for the recovery at startup. So you have to put in the time either way. It is not unheard of that a shutdown can take minutes. That's why the -t option was added to pg_ctl some time ago. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 9.0 to 9.2 pg_upgrade pain due to collation mismatch
On 9/12/12 2:31 PM, Tom Lane wrote: C is the official name of that locale. Not sure how you got it to say POSIX ... maybe we didn't have normalization of the locale name back then? Says who? I think C and POSIX are distinct locales that just happen to behave the same way. -- 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] Bad pg_dump error message
On Tue, 2012-09-11 at 01:21 -0400, Tom Lane wrote: Mike Christensen m...@kitchenpc.com writes: Oh reading the online docs, it looks like what I may have wanted was: --format=custom Right. That does everything tar format does, only better --- the only thing tar format beats it at is you can disassemble it with tar. Back in the day that seemed like a nice thing, open standards and all; but the 8GB per file limit is starting to get annoying. We could change the tar code to produce POSIX 2001 format archives, which don't have that limitation. But if someone wanted to do some work in this area, it might be more useful to look into a zip-based format. -- 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] Packaging of plpython
On Fri, 2012-09-07 at 22:41 +0300, Gražvydas Valeika wrote: What is the problem to provide both plpython2 and plpython3, or keep same (2 or 3) plpython available by default on both platforms? It is the decision of the respective packagers which version they provide and how much effort they want to put in. If you have issues with their decisions, you could try to submit a bug report to their respective bug trackers. Btw., Debian and Ubuntu provide PL/Python for Python 2 and 3, so it's possible. And it happened because someone submitted a bug report, and someone put in the effort. ;-) Personally, I think the Windows packagers made a mistake by providing Python 3 only at this point. -- 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] fast-archiver tool, useful for pgsql DB backups
On Fri, 2012-08-24 at 15:48 -0600, Mathieu Fenniak wrote: Hi pgsql-general, Has anyone else ever noticed how slow it can be to rsync or tar a pgdata directory with hundreds of thousands or millions of files? Yes: http://petereisentraut.blogspot.com/2012/05/base-backup-compression-options.html My analysis showed that the archiving was CPU-bound on the compression task. It might different when you are dealing with a lot of small files as opposed to a few big files. So parallelizing the archiving itself could still be useful. -- 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] C locale versus en_US.UTF8. (Was: String comparision in PostgreSQL)
On Wed, 2012-08-29 at 10:31 -0700, Aleksey Tsalolikhin wrote: What is the difference between C and en_US.UTF8, please? There are many differences, but here is a simple one: $ (echo a; echo A; echo b; echo B) | LC_ALL=C sort A B a b $ (echo a; echo A; echo b; echo B) | LC_ALL=en_US.utf8 sort a A b B -- 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] Regarding pc-lint on PostgreSQL code
On Tue, 2012-08-14 at 11:44 -0400, Bruce Momjian wrote: On Tue, Aug 14, 2012 at 04:58:13AM +, Rajeev rastogi wrote: We are in process of evaluating the PostgreSQL static report. So wanted to know if there is any available result for pc-lint on latest source code of PostgreSQL (preferably on 9.2 Beta 3). Not that I know of. There are Coverity reports on the Postgres source, and I think Peter Eisentraut is familiar with them. I am not familiar with pc-lint or anyone having used it with PostgreSQL. I have used Coverity and the clang static analyzer, and they are both work in progress with respect to PostgreSQL. -- 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] Output of query_to_xml
On tor, 2012-06-21 at 07:49 +0200, P. Broennimann wrote:
select query_to_xml('select * from table12', true, true, '') into ...
The result is OK but there is always an empty line:
row xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance;
*-- Empty line here*
vanoaoFG8976SDFRETG/vanoao
country_codeNL/country_code
build_date2011-02-28/build_date
tcodeMFT/tcode
exterior_color_codeGAB/exterior_color_code
interior_color_codeTAFP/interior_color_code
/row
Is this supposed to be or is this a cosmetic bug?
No reason. I think it just came out this way because there are so many
modes to handle, and you want some legible output in all of them.
I've removed outputting the extra newline in 9.3devel.
--
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] Simultaneous Installations 7.4 9.x
On mån, 2012-05-07 at 11:08 -0400, Randy Johnson wrote: Hello, I have a redhat E5 installation with PostgreSQL 7.4 installation. I have been tasked with upgrading it to 9.x I have read that I can install both versions at the same time via YUM and edit the config file on the new installation to use a different port until go live time. Can anyone confirm if this is true? That would be a good way to go about it, if your packaging supports parallel installations like this. Judging from the age of your existing installation (7.4), however, I'd guess that it does not. So you probably need to build one of the versions from source, do a partial upgrade, or remove the old version. You will likely have some thinking to do. I also read this is the ideal way to go so I can use the newer version of pg_dumpall to transfer the data because the newer version contains bug fixes and other efficiencies. Yes, it's best (or in some cases required) to use the newest pg_dumpall. -- 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] installation of plpython2.7
On tis, 2012-05-01 at 12:56 -0700, Mark Rostron wrote: hi I want to install madlib into a postgresql9.1.3 installation. i am trying to: a) make python2.7 b) configure postgresql to point at the python2.7 working directory, and c) install postgresql9.1.3 (using python2.7) cd Python-2.7.3 ./configure --prefix=SOMEWHERE make make install cd postgresql-9.1.3 ./configure ...other options... --with-python PYTHON=SOMEWHERE/bin/python make make install -- 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] Unaccent characters
On fre, 2012-04-20 at 09:15 +0100, Thom Brown wrote: I had a look at the unaccent.rules file and noticed the following characters aren't properly converted: ß (U+00DF) An eszett represents a double-s SS but this replaces it with one S. Shouldn't this be replace with SS? Probably, but it certainly shouldn't be upper case. Æ (U+00C6) and æ (U+00E6) These doesn't have an accent, diacritic or anything added to a single latin character. It's simply a ligature of A and E or a and e. If someone has the text æther, I would imagine they'd be surprised at it being converted to ather instead of aether. It depends on what the point of this module is supposed to be. Doing unaccenting usefully depends on language and context. For example, it would be very reasonable to map æ to ae, but in a Scandinavian context, æ is equivalent to ä, which is mapped to a, which is itself questionable. Œ (U+0152) and œ (U+0153). Same as above. This is a ligature of O and E or o and e. Except this time the unaccent module chooses the 2nd character instead of the 1st which is confusing. That certainly seems wrong. It's also worth noting that while æ is in some languages considered a separate letter, œ is generally just a typographical ligature. -- 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] Compile docs on ArchLinux
On ons, 2012-04-18 at 10:08 -0400, Tom Lane wrote: Now having said that, you could certainly try adjusting the DOCTYPE declaration in the docs and seeing if they'd build with 4.5. It should work. The problem, as I recall it, with DocBook 4.5 was that the are some problems in the source packaging that would require users who install from source to do some extra patching. So it would create more work for some without any actual benefit. -- 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] Supporting SQL/MED DATALINK
On fre, 2012-01-06 at 15:53 +0100, Damiano ALBANI wrote: Do you plan on supporting SQL/MED features concerning DATALINKs? I've seen DATALINK mentionned on the Wiki [1] but I couldn't find it on the TODO list [2]. I'm not aware of any plans. What would be your use case? By the way, do you know any vendor that provides support for DATALINK? Except DB2, there seems to be very little visibility of this 2003 feature in the whole SQL world ! I believe DB2 is pretty much it in this area. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to create database with default system locale is set to et_EE.UTF-8
On tor, 2011-12-22 at 18:29 +0200, Andrus wrote: How to force command CREATE DATABASE yourdbname TEMPLATE = template0 to use et_EE.UTF-8 locale by default ? If you don't want to re-initdb, you could just update the datctype and datcollate columns of pg_database for template0. If you want to re-initdb, check the pg_createcluster man page on Debian. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to create database with default system locale is set to et_EE.UTF-8
On fre, 2011-12-23 at 17:32 +0200, Andrus wrote: If you don't want to re-initdb, you could just update the datctype and datcollate columns of pg_database for template0. Thank you. where to find sql update statement which does this ? Is update pg_database set datctype ='et_EE.UTF-8', datcollate ='et_EE.UTF-8' best for this ? add WHERE datname = 'template0' template0 is read-only, how to connect to and update it using pgAdmin ? pg_database is shared between all databases, so you can connect to any database to do this. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to install latest stable postgresql on Debian
On sön, 2011-11-20 at 12:09 +0200, Andrus wrote: Debian seems to require update-rc.d and Centos chkconfig How to use single command for every distro ? apt-get install chkconfig /etc/init.d/postgresql start works in all distros. Adding to postgresql to startup requires different commands in different distros ?! PostgreSQL is started automatically on Debian. Even the backports. You did something funny if it didn't work. -- 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] syntax highlighting in emacs after \e in psql
On mån, 2011-11-14 at 08:08 -0800, MikeW wrote: When I open *.sql files in my emacs it highlights the SQL and Postgres syntax correctly. But does anybody know how to make it behave like that also after invoking \e command in psql (so that I don't need to say: M-x sql-mode each time). My .profile contains: PSQL_EDITOR=emacs; export PSQL_EDITOR. (add-to-list 'auto-mode-alist '(/psql.edit.[0-9]+\\' . sql-mode)) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 9.1 replication on different arch
On ons, 2011-11-02 at 22:40 -0300, Martín Marqués wrote: 2011/11/2 John R Pierce pie...@hogranch.com: On 11/02/11 11:21 AM, Martín Marqués wrote: Don't worry, they are both x86 arch, so I'll just install 32bit postgresql on the 64 bit server. That should make it work, right? yes, that should work fine. Sad thing is that it's not so easy on Debian. With Fedora all I had to do is select the arch type and that's all. Put it in a chroot. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_upgrade 8.4 - 9.1 failures
On sön, 2011-10-02 at 15:45 -0400, Joseph S wrote: Mismatch of relation names: database dbname, old rel pg_toast.pg_toast_1280475, new rel pg_toast.pg_toast_1202320 Failure, exiting This will be fixed in 9.1.2 (or get the code already from git). -- 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] regression between 8.4.8 and 8.4.2?
I don't have an answer for you, but this report looks suspiciously similar to the one I posted the other day at http://archives.postgresql.org/pgsql-hackers/2011-08/msg01224.php, which, now that I think about it, also manifested itself after the upgrade to 8.4.8. On tis, 2011-08-30 at 15:24 -0700, Ben Chobot wrote: We recently took a copy of our production data (running on 8.4.2), scrubbed many data fields, and then loaded it onto a qa server (running 8.4.8). We're seeing some odd planner performance that I think might be a bug, though I'm hoping it's just idiocy on my part. I've analyzed things and looked into pg_stats and it seems as if the relevant columns have about the same statistics. I've managed to simplify the query, but if I make it any simpler, then the two servers end up with the same good plan. The query is down to: SELECT machines.quota_purchased FROM machines WHERE NOT deleted AND machines.user_id IN ( SELECT id FROM users WHERE user_group_id IN ( select 607547 offset 0 ) OFFSET 0 ); (Those offset 0 are in there to protect us from planner regressions we saw when moving to 8.4. When we move to 9, they can hopefully go away.) On the production server, this returns a fairly accurate plan: QUERY PLAN -- Nested Loop (cost=843.59..1447.90 rows=243 width=8) (actual time=0.044..0.045 rows=1 loops=1) - HashAggregate (cost=843.59..845.59 rows=200 width=4) (actual time=0.027..0.027 rows=1 loops=1) - Limit (cost=0.02..823.90 rows=1575 width=4) (actual time=0.024..0.025 rows=1 loops=1) - Nested Loop (cost=0.02..823.90 rows=1575 width=4) (actual time=0.023..0.024 rows=1 loops=1) - HashAggregate (cost=0.02..0.03 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=1) - Limit (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.002 rows=1 loops=1) - Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=1) - Index Scan using users_user_groups_idx on users (cost=0.00..804.18 rows=1575 width=8) (actual time=0.017..0.018 rows=1 loops=1) Index Cond: (users.user_group_id = (607547)) - Index Scan using machines_sid_un on machines (cost=0.00..3.00 rows=1 width=12) (actual time=0.015..0.015 rows=1 loops=1) Index Cond: (machines.user_id = users.id) Total runtime: 0.121 ms (12 rows) On the QA server, things are not so accurate. It doesn't hurt the timing of this simplified query much, but when put into the actual query, the row estimation being off by 6 orders of magnitude really throws the planning in the wrong direction. The plan on the QA server is: QUERY PLAN --- Nested Loop (cost=1887.16..3671.20 rows=1192462 width=8) (actual time=0.049..0.051 rows=1 loops=1) - HashAggregate (cost=1887.16..1889.16 rows=200 width=4) (actual time=0.032..0.033 rows=1 loops=1) - Limit (cost=0.02..1868.20 rows=1517 width=4) (actual time=0.027..0.029 rows=1 loops=1) - Nested Loop (cost=0.02..1868.20 rows=1517 width=4) (actual time=0.027..0.028 rows=1 loops=1) - HashAggregate (cost=0.02..0.03 rows=1 width=4) (actual time=0.008..0.008 rows=1 loops=1) - Limit (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1) - Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1) - Index Scan using users_user_groups_idx on users (cost=0.00..1849.20 rows=1517 width=8) (actual time=0.015..0.016 rows=1 loops=1) Index Cond: (users.user_group_id = (607547)) - Index Scan using machines_sid_un on machines (cost=0.00..8.90 rows=1 width=12) (actual time=0.013..0.013 rows=1 loops=1) Index Cond: (machines.user_id = users.id) Total runtime: 0.148 ms (12 rows) The problem here (I think) seems to be that the QA server believes that running a nested loop over 200 users.id values and joining that against machines.user_id will result in 1M rows. The production servers sees this more accurately as the nearly 1:1 relationship that it is. The reason I wonder if this might be a bug is because if I change the obtuse clause WHERE user_group_id IN (select 607547 offset 0) to simply where user_group_id in (607547) then the plan collapses to the same plan on both servers: explain analyze SELECT
Re: [GENERAL] regression between 8.4.8 and 8.4.2?
On ons, 2011-08-31 at 10:42 +0300, Peter Eisentraut wrote: I don't have an answer for you, but this report looks suspiciously similar to the one I posted the other day at http://archives.postgresql.org/pgsql-hackers/2011-08/msg01224.php, which, now that I think about it, also manifested itself after the upgrade to 8.4.8. See this thread: http://archives.postgresql.org/pgsql-performance/2011-08/msg00248.php It looks like there are a number of users affected by this. On tis, 2011-08-30 at 15:24 -0700, Ben Chobot wrote: We recently took a copy of our production data (running on 8.4.2), scrubbed many data fields, and then loaded it onto a qa server (running 8.4.8). We're seeing some odd planner performance that I think might be a bug, though I'm hoping it's just idiocy on my part. I've analyzed things and looked into pg_stats and it seems as if the relevant columns have about the same statistics. I've managed to simplify the query, but if I make it any simpler, then the two servers end up with the same good plan. The query is down to: SELECT machines.quota_purchased FROM machines WHERE NOT deleted AND machines.user_id IN ( SELECT id FROM users WHERE user_group_id IN ( select 607547 offset 0 ) OFFSET 0 ); (Those offset 0 are in there to protect us from planner regressions we saw when moving to 8.4. When we move to 9, they can hopefully go away.) On the production server, this returns a fairly accurate plan: QUERY PLAN -- Nested Loop (cost=843.59..1447.90 rows=243 width=8) (actual time=0.044..0.045 rows=1 loops=1) - HashAggregate (cost=843.59..845.59 rows=200 width=4) (actual time=0.027..0.027 rows=1 loops=1) - Limit (cost=0.02..823.90 rows=1575 width=4) (actual time=0.024..0.025 rows=1 loops=1) - Nested Loop (cost=0.02..823.90 rows=1575 width=4) (actual time=0.023..0.024 rows=1 loops=1) - HashAggregate (cost=0.02..0.03 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=1) - Limit (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.002 rows=1 loops=1) - Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=1) - Index Scan using users_user_groups_idx on users (cost=0.00..804.18 rows=1575 width=8) (actual time=0.017..0.018 rows=1 loops=1) Index Cond: (users.user_group_id = (607547)) - Index Scan using machines_sid_un on machines (cost=0.00..3.00 rows=1 width=12) (actual time=0.015..0.015 rows=1 loops=1) Index Cond: (machines.user_id = users.id) Total runtime: 0.121 ms (12 rows) On the QA server, things are not so accurate. It doesn't hurt the timing of this simplified query much, but when put into the actual query, the row estimation being off by 6 orders of magnitude really throws the planning in the wrong direction. The plan on the QA server is: QUERY PLAN --- Nested Loop (cost=1887.16..3671.20 rows=1192462 width=8) (actual time=0.049..0.051 rows=1 loops=1) - HashAggregate (cost=1887.16..1889.16 rows=200 width=4) (actual time=0.032..0.033 rows=1 loops=1) - Limit (cost=0.02..1868.20 rows=1517 width=4) (actual time=0.027..0.029 rows=1 loops=1) - Nested Loop (cost=0.02..1868.20 rows=1517 width=4) (actual time=0.027..0.028 rows=1 loops=1) - HashAggregate (cost=0.02..0.03 rows=1 width=4) (actual time=0.008..0.008 rows=1 loops=1) - Limit (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1) - Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1) - Index Scan using users_user_groups_idx on users (cost=0.00..1849.20 rows=1517 width=8) (actual time=0.015..0.016 rows=1 loops=1) Index Cond: (users.user_group_id = (607547)) - Index Scan using machines_sid_un on machines (cost=0.00..8.90 rows=1 width=12) (actual time=0.013..0.013 rows=1 loops=1) Index Cond: (machines.user_id = users.id) Total runtime: 0.148 ms (12 rows) The problem here (I think) seems to be that the QA server believes that running a nested loop over 200 users.id values and joining that against machines.user_id will result in 1M rows. The production servers sees this more
[GENERAL] Call for translations
In anticipation of the release of PostgreSQL 9.1, it is once again time to update the message translations. We are now in a string freeze, which has traditionally been associated with the first release candidate, so it's a good time to do this work now. If you want to help, see http://babel.postgresql.org/ for instructions and other information. If there are already active translation teams, please communicate with them first. The mailing list pgtranslation-translat...@pgfoundry.org is available for general discussion and coordination of translation activities. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] documentation for hashtext?
On tor, 2011-08-25 at 14:05 +0200, Massa, Harald Armin wrote: conclusion was that it's not documented because it's internal and you're not supposed to use/rely on it. My impression is that people are allready using it, relying their sharding on it, even building indexes on it. I think a better solution is either to implement lookup3 in PostgreSQL, which is what hashtext was originally based on, so you have a hash function that won't change and is comparable to the current one in behavior. Or you use a standard cryptographic hash function such as md5 or sha1 and shard by that. They are slower than the lookup3-type hash functions, but for multiple-node applications, it's probably OK. So... I suggest we start documenting it. Even if there was a recommendation not to use it, people will get hurt anyway if their application rely on it and it breaks. Well, it's already broken for what people are using it or are thinking about using it, and documenting it won't help that. Better come up with a purpose-built solution, as per above. -- 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] call initdb as regular user
On tor, 2011-08-18 at 11:20 -0700, alexondi wrote: Can I call initdb with some params as regular user (not root or postgres)? Sure. -- 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] query_to_xml nulls set to false
On mån, 2011-07-11 at 18:54 -0700, Lynn Dobbs wrote: I am using query_to_xml with nulls set to false in postgresql 9.0.4. (I believe the behavior was also present in 8.4.) The documentation for query_to_xml says that if set to true, nulls with be treated with xsi:nil=true and An appropriate namespace declaration will be added to the result value. If false, columns containing null values are simply omitted from the output. This suggests to me that if set to false, there should be no added namespace declaration, but, in practice, the xsi namespace is present. Is this the designed, intentional behavior or accidental? The xsi namespace is always added, but this is an implementation detail, it's not something that has to be one way or another. The XML is equivalent, after all. -- 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] Linux, Hungarian charset (Win1250) is supports the hungarian collation?
On mån, 2011-03-21 at 11:22 +0100, Durumdara wrote: The language is Windows 1250 (ISO-8859-2). I remembered that when I tried in 8.1 to create database as same in Windows: CharSet: Win1250 Collation: - (disabled, and it is handled as HUN - iso-8859-2) then I failed. Because in Linux (Ubuntu as I remembered) the collation with Win1250 is not supports, only C ordering. Only one possible way was that if change CharSet to UTF, then collation can be Windows1250... But I want to avoid the UTF hell if possible. Because now I don't have Linux here, I cannot test the PG 9.0... May Latin2 is the solution, but may Latin2 is also supports only C collation. On Linux you can use locale hu_HU.iso88592. It should do what you want. -- 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] XML Encoding problem
On mån, 2011-02-07 at 12:44 +0100, rsmogura wrote: I have test database with UTF-8 encoding. I putted there XML aЁĄ¡/a, (U+0401, U+0104, U+00A1). I changed client encoding to iso8859-2, as the result of select I got ERROR: character 0xd081 of encoding UTF8 has no equivalent in LATIN2 Stan SQL:22P05. I should got result with characters entities for unparsable characters #...;. Hehe, interesting idea, but it's not implemented that way. We don't alter the XML data, except for the XML declaration. -- 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 understanding collation order
On tis, 2011-01-18 at 10:33 +1100, raf wrote: p.s. if anyone in debian locale land is listening, 'E' does not sort before ','. what were you thinking? :-) What is actually happening is that the punctuation is sorted in a second pass after the letters. Which is both correct according to the relevant standards and also practical in many situations. It's usually actually the Mac OS X locales that are broken. -- 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] Using regexp_replace to remove small words
On fre, 2010-12-10 at 10:47 -0200, Henrique de Lima Trindade wrote:
I'm trying to find a regular expression that removes all small (length N)
words from a string. But, until now I've not been successful.
Here is a start:
select regexp_replace('Tommy Lee Jones', $$\y\w{2,3}\y$$, ' ', 'g' );
If you want to normalize the spaces after the removal and handle
beginning and end of the word, you will need to expand this to cover
those cases, but the example should contain the key ingredients.
--
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] Type cast removal - proposed exceptions for xml,enum
On tis, 2010-12-07 at 11:49 +0800, Craig Ringer wrote: This is driving me nuts when working with PgJDBC via various ORM layers (I know, I know, but they're life at this point) that would work happily with these types if they were implicitly castable to/from strings, but don't understand how to explicitly specify these postgresql-specific types when talking to the JDBC layer. Since that is a quite specific use case, why don't you add the casts yourself? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to find correct locale name for CREATEDATABASE
On tor, 2010-12-02 at 19:53 +0200, Andrus wrote: I'm trying to create portable application which can automatically create database using estonian locale in any server. Postgres returns different values for same locale: In Fedoraet_EE.UTF8 Other linuxeset_EE.UTF-8 In WindowsEstonian_Estonia.1257 In some other Linuxes something like Estonian.Estonia For this reason I'm looking for a way to probe server for locale existence. Well, the solution here would be that someone sits down and creates a universal taxonomy of all possible locale names in all possible environments. Until someone embarks on that task, you will probably be better off just tracking the handful of possible locale names applicable to your concern yourself. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to find correct locale name for CREATE DATABASE
On ons, 2010-12-01 at 10:26 +0200, Andrus wrote: How to get list of available locale names from Postgres ? Why Postgres does not have command which returns available locale names ? How to use same locale names in every platform? There is no portable operating system interface to get the names of all locales. -- 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] lock file permisson
On mån, 2010-11-29 at 21:57 +0530, Mohammed Rashad wrote: when i start postgresql using /etc/init.d/postgresql-8.4 start I am getting this error IST FATAL: could not create lock file /var/run/postgresql/.s.PGSQL.5432.lock: Permission denied You probably need to run this command as root. -- 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] ipv4 data type does not allow to use % assubnet mask delimiter
On mån, 2010-11-15 at 11:06 +0200, Andrus wrote: Possibly someday the standard will actually standardize the things, and then maybe we can work with them usefully ... From http://tools.ietf.org/html/rfc4007#section-11.2 implementation SHOULD support the following format: address%zone_id where address is a literal IPv6 address, zone_id is a string identifying the zone of the address, and `%' is a delimiter character to distinguish between address and zone_id. so this is clearly standardized. Send a patch please. -- 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] ipv4 data type does not allow to use % as subnet mask delimiter
On tor, 2010-11-11 at 20:33 +0200, Andrus wrote: Windows uses % as subnet mask delimiter. This is not a subnet mask but a zone index, but it should probably still be supported. -- 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] ipv4 data type does not allow to use % as subnet mask delimiter
On sön, 2010-11-14 at 16:46 -0500, Tom Lane wrote: Peter Eisentraut pete...@gmx.net writes: On tor, 2010-11-11 at 20:33 +0200, Andrus wrote: Windows uses % as subnet mask delimiter. This is not a subnet mask but a zone index, but it should probably still be supported. I believe we looked into that some time ago and decided that the behavior was too platform-dependent to be worth messing with. I suppose the problem is that the zone identifier could be almost any string, and storing that would upset the inet storage format. Then again, this is part of the IPv6 standard, so just giving up might not be sustainable in the long run. -- 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 ROW_COUNT from MOVE in 8.3
On sön, 2010-10-24 at 15:41 +0200, Reuven M. Lerner wrote: But is there any way for me to get, in 8.3, the number of rows over which a cursor has skipped? Keep in mind that after this count has executed, we're then going to rewind the cursor, chunking through the result set with a separate function. You could store the query result in a temporary table. -- 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] Missing uuid_generate_v1()
On ons, 2010-10-06 at 18:18 -0700, Mike Christensen wrote: ERROR: could not load library /opt/PostgreSQL/9.0/lib/postgresql/uuid-ossp.so: libuuid.so.16: cannot open shared object file: No such file or directory SQL state: 58P01 apt-get install libossp-uuid16 -- 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] queriing the version of libpq
On tis, 2010-10-05 at 09:33 +0200, Massa, Harald Armin wrote: Now I would love to have an additional check is the used psycopg2 linked to an advanced-enough libpq, to be able to set bytea_output to 'escape' if the libpq is not worthy. This is really something that psycopg2 should work out for you. I suggest you take up a discussion on this on their mailing list. -- 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] Feature proposal
On ons, 2010-08-25 at 00:15 -0700, wstrzalka wrote: I'm currently playing with very large data import using COPY from file. As this can be extremely long operation (hours in my case) the nice feature would be some option to show operation progress - how many rows were already imported. A feature like this is being worked on: https://commitfest.postgresql.org/action/patch_view?id=368 -- 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] plpython feature idea: an option to return row results as lists
On fre, 2010-07-30 at 16:45 -0500, Derek Arnold wrote: Has there ever been any interest in adding a keyword option for returning row lists rather than dicts? I don't think so, but it sounds like a reasonable idea. Other possible approaches are - Using a factory class like psycopg (http://initd.org/psycopg/docs/extras.html) - Using an OrderedDict -- 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] XML - DOCTYPE element - documentation suggestion
On fre, 2010-06-18 at 02:43 +0800, Craig Ringer wrote: The xml datatype documentation should probably mention that whole documents must be loaded with an XMLPARSE(DOCUMENT 'doc_text_here), they cannot just be cast from text to xml as happens when you pass an xml document as text to a parameter during an INSERT. This should probably appear under CREATING XML VALUES in: http://www.postgresql.org/docs/current/static/datatype-xml.html ... and probably deserves mention in a new CAVEATS or NOTES section too, as it' *will* catch people out even if they R TFM. Done -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] A thought about other open source projects
On lör, 2010-06-19 at 22:56 +0100, David Goodenough wrote: These projects need help to realise that adding Postgresql is not a big job, especially for those using JDBC which can already connect to all DBs. It strikes me that if the project could write a few pages gleaned from other porting operations, then whenever a project like this is found they can be pointed to these pages and shown how easy it is to do. http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL -- 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] Cognitive dissonance
On lör, 2010-06-12 at 11:18 +0200, John Gage wrote: A one file html version would be a godsend. I've committed a build target for that now. Use 'make postgres.html' in doc/src/sgml/. -- 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] Cognitive dissonance
On lör, 2010-06-12 at 09:10 -0400, Tom Lane wrote: Peter Eisentraut pete...@gmx.net writes: On lör, 2010-06-12 at 11:18 +0200, John Gage wrote: A one file html version would be a godsend. I've committed a build target for that now. Use 'make postgres.html' in doc/src/sgml/. Huh, is that actually worth anything? How many browsers will open it without crashing, or will navigate the page with decent performance if they do manage to open it? Text output is generated by going through HTML. I haven't figured out the best way to do the second step yet. We use lynx for INSTALL and HISTORY, but the results for this big file aren't very clean. Browsers seem to handle the file OK, btw. -- 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] Cognitive dissonance
On tis, 2010-06-08 at 11:04 +0200, John Gage wrote: Yet, the only one file edition of the Postgres documentation is in...pdf format. Huh? I know. I know. I have already brought this up. And various ways of creating a one file text edition of the documentation have been proposed to me. I know. But either I am a visitor from the Crab Nebula, or there is someone else out there who would like to have a text file of the entire documentation. As I said back then, doing this is straightforward, but we kind of need more than one user who asks for it before we make it part of a regular service, which comes with maintenance costs. -- 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] cursor_to_xml iteration of a table
On tis, 2010-05-25 at 12:05 -0400, Richard Wallace wrote: 1) When using cursor_to_xml in a plpgsql function, the FOUND variable does not seem to get set, so there is no way to exit a loop that is iterating over the cursor. Below is the function code; it loops indefinitely when it is run. create or replace function getxml() returns setof xml as $$ declare resultxml xml; curs refcursor; begin open curs for select * from groups; loop select cursor_to_xml(curs,1000, false, false, '') into resultxml; return next resultxml; exit when not found; end loop; end; $$ language plpgsql; Yeah, there doesn't seem to be a good way out of that. When the end of the cursor is reached, cursor_to_xml returns an empty xml value (which is probably bogus in itself, since that is not a valid xml value to begin with), so you could test it like this: exit when resultxml::text = ''; 2) Assuming the above issue is fixed, how can I go about ensuring that the result set from the function isn't stored in memory until the function completes? Ideally, I'd like to write the output to a file with each iteration of the cursor, but I know file IO is a big no-no with plpgsql since transactions can't manage the state of files being written. cursor_to_xml is more meant to be used from a client. If you do it like in the above function, you will indeed build the result in memory (multiple times, perhaps). -- 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] Bizarre problem: Python stored procedure using protocol buffers not working
On lör, 2010-05-15 at 22:50 -0700, Yang Zhang wrote: On Sat, May 15, 2010 at 10:20 PM, Peter Eisentraut pete...@gmx.net wrote: You have null bytes in the data value, which is not supported very well in PL/Python. Try the 9.0 beta version; it should be fixed there. Thanks. Out of curiosity, is this an issue just with PL/Python or with other stored procedure languages as well? It's an artifact of the PL/Python implementation. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
