Re: [BUGS] BUG #5889: Intersects for polygons broken
Robert Haas wrote: On Tue, Mar 8, 2011 at 3:20 PM, Konrad Garus konrad.ga...@gmail.com wrote: http://www.postgresql.org/docs/current/static/functions-geometry.html I wish it explained what arguments each of the operators accepts, and whether any automatic conversions take place (like polygon to box in that 8.3 issue). What happens when I call ?# or ?- on a polygon and point? Two points? Open path? Some of these are obvious, but others not so much. Hmm, yeah. That looks like it could be improved. It's certainly not obvious to me what box * point means, for example, even though the description says scaling/rotation. Would someone who uses these features please post changes and I will see that get into the docs? Thanks. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Problem with ALTER TABLE - occasional tuple concurrently updated
Was this fixed? --- Alvaro Herrera wrote: Excerpts from Alvaro Herrera's message of jue nov 18 15:31:16 -0300 2010: Excerpts from Robert Haas's message of jue nov 18 15:11:37 -0300 2010: In the current master branch, it appears that ALTER TABLE c INHERIT p takes a ShareUpdateExclusiveLock on the child, which seems sufficient, and an AccessShareLock on the parent, which seems like it might not be; though I'm having a hard time figuring out exactly when it wouldn't be, especially since in 8.4 I'm fairly sure any ALTER TABLE command takes an AccessExclusiveLock. What if two of these run at the same time, and the parent doesn't have children when they start? They would both try to set relhassubclass, no? Yep, duplicated the issue that way. -- ??lvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Problem with ALTER TABLE - occasional tuple concurrently updated
Robert Haas wrote: On Thu, Mar 10, 2011 at 4:08 PM, Bruce Momjian br...@momjian.us wrote: Was this fixed? Not yet. I can probably fix it, if nobody else wants to do it. Well, it has languished for five months, so the nobody else wants part is probably accurate. ;-) -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5814: documentation bug
Antje Petersen wrote: The following bug has been logged online: Bug reference: 5814 Logged by: Antje Petersen Email address: antje.peter...@desy.de PostgreSQL version: 8.1.21 Operating system: Scientific Linux 5 Description:documentation bug Details: According to the documentation createuser --no-superuser and createuser --no-createrole is the default. This is not true. The default is to be asked Shall the new role be a superuser? (y/n) Shall the new role be allowed to create more new roles? (y/n) Sorry for the late reply. If you press enter, no is the default. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Documentation bug: Chapter 35.4, paragraph 4
Robert Haas wrote: On Tue, Nov 23, 2010 at 1:08 PM, Jonathan Pool p...@utilika.org wrote: Chapter 35.4, paragraph 4, of the PostgreSQL 9.0.1 Documentation says: Any collection of commands in the SQL language can be packaged together and defined as a function. Besides SELECT queries, the commands can include data modification queries (INSERT, UPDATE, and DELETE), as well as other SQL commands. (The only exception is that you cannot put BEGIN, COMMIT, ROLLBACK, or SAVEPOINT commands into a SQL function.) This appears to be incorrect, in that attempting to include a VACUUM command in a query-language function elicits the following error message: ERROR: ?VACUUM cannot be executed from a function or multi-command string Thus, presumably VACUUM should be added to the list of exceptions. I fear it's worse than that. Taking a look at the places where we call PreventTransactionChain(), they appear to include database-wide CLUSTER, DISCARD ALL, VACUUM (as you noted), COMMIT PREPARED, ROLLBACK PREPARED, CREATE TABLESPACE, DROP TABLESPACE, ALTER TYPE enum ADD VALUE label (but the PreventTransactionChain call says ADD rather than ADD VALUE), CREATE INDEX CONCURRENTLY, CREATE DATABASE, DROP DATABASE, and REINDEX DATABASE. I'm not sure if there's some generic way we could refer to all that rather than listing them all individually. I just made the wording more generic; we rarely are asked about this, so generic seemed appropriate. Applied doc patch attached. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + diff --git a/doc/src/sgml/xfunc.sgml b/doc/src/sgml/xfunc.sgml new file mode 100644 index c65f852..1a35014 *** a/doc/src/sgml/xfunc.sgml --- b/doc/src/sgml/xfunc.sgml *** *** 116,124 Besides commandSELECT/command queries, the commands can include data modification queries (commandINSERT/command, commandUPDATE/command, and commandDELETE/command), as well as ! other SQL commands. (The only exception is that you cannot put ! commandBEGIN/, commandCOMMIT/, commandROLLBACK/, or ! commandSAVEPOINT/ commands into a acronymSQL/acronym function.) However, the final command must be a commandSELECT/command or have a literalRETURNING/ clause that returns whatever is --- 116,124 Besides commandSELECT/command queries, the commands can include data modification queries (commandINSERT/command, commandUPDATE/command, and commandDELETE/command), as well as ! other SQL commands. (You cannot use transaction control commands, e.g. ! commandCOMMIT/, commandSAVEPOINT/, and some utility ! commands, e.g. literalVACUUM/, in acronymSQL/acronym functions.) However, the final command must be a commandSELECT/command or have a literalRETURNING/ clause that returns whatever is -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5765: pg_dump fail to find upper case table name
Kevin Grittner wrote: Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Manual says (at the bottom of http://www.postgresql.org/docs/8.4/static/app-pgdump.html): To specify an upper-case or mixed-case name in -t and related switches, you need to double-quote the name; else it will be folded to lower case (see Patterns). But double quotes are special to the shell, so in turn they must be quoted. Thus, to dump a single table with a mixed-case name, you need something like $ pg_dump -t 'MixedCaseName' mydb mytab.sql Perhaps some of that should be moved up to the definition of the -t switch? It wouldn't seem too out of place to me to put it somewhere near this sentence: | When using wildcards, be careful to quote the pattern if needed to | prevent the shell from expanding the wildcards. While examples are useful, information which is only provided there is easily missed when someone goes to read up on a particular switch. I have applied the attached doc patch to reference the example section from the specific pg_dump options sections. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml new file mode 100644 index 25dc2a7..e78d275 *** a/doc/src/sgml/ref/pg_dump.sgml --- b/doc/src/sgml/ref/pg_dump.sgml *** PostgreSQL documentation *** 301,307 linkend=APP-PSQL-patterns endterm=APP-PSQL-patterns-title), so multiple schemas can also be selected by writing wildcard characters in the pattern. When using wildcards, be careful to quote the pattern ! if needed to prevent the shell from expanding the wildcards. /para note --- 301,308 linkend=APP-PSQL-patterns endterm=APP-PSQL-patterns-title), so multiple schemas can also be selected by writing wildcard characters in the pattern. When using wildcards, be careful to quote the pattern ! if needed to prevent the shell from expanding the wildcards; see ! xref linkend=pg-dump-examples endterm=pg-dump-examples-title. /para note *** PostgreSQL documentation *** 435,441 linkend=APP-PSQL-patterns endterm=APP-PSQL-patterns-title), so multiple tables can also be selected by writing wildcard characters in the pattern. When using wildcards, be careful to quote the pattern ! if needed to prevent the shell from expanding the wildcards. /para para --- 436,443 linkend=APP-PSQL-patterns endterm=APP-PSQL-patterns-title), so multiple tables can also be selected by writing wildcard characters in the pattern. When using wildcards, be careful to quote the pattern ! if needed to prevent the shell from expanding the wildcards; see ! xref linkend=pg-dump-examples endterm=pg-dump-examples-title. /para para *** CREATE DATABASE foo WITH TEMPLATE templa *** 973,979 /refsect1 refsect1 id=pg-dump-examples ! titleExamples/title para To dump a database called literalmydb/ into a SQL-script file: --- 975,981 /refsect1 refsect1 id=pg-dump-examples ! title id=pg-dump-examples-titleExamples/title para To dump a database called literalmydb/ into a SQL-script file: -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5707: Cross compilation for windows is broken
Robert Haas wrote: On Sat, Feb 26, 2011 at 2:14 AM, Bruce Momjian br...@momjian.us wrote: Has this been addressed? Not me. Sounds like no one cares enough to figure out how to do this. Perhaps this should be a TODO. Agreed. TODO added: Fix cross-compiling on Windows * http://archives.postgresql.org/pgsql-bugs/2010-10/msg00110.php -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5705: btree_gist: Index on inet changes query result
This is currently a TODO so at least we are tracking it. --- andreas wrote: On Tue, 2010-10-19 at 18:22 -0400, Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: On Mon, Oct 11, 2010 at 7:50 PM, Tom Lane t...@sss.pgh.pa.us wrote: Well, actually the btree_gist implementation for inet is a completely broken piece of junk: it thinks that convert_network_to_scalar is 100% trustworthy and can be used as a substitute for the real comparison functions, which isn't even approximately true. Are you planning to fix this? No. I don't understand why Teodor did it like that, so I'm not going to try to change it. I'd be willing to take responsibility for ripping out btree_gist's inet support altogether ... regards, tom lane That is the reason why I just reported it instead of trying to fix it myself first. Since I could not understand why it was done like that, I did not feel like fixing it. Best regards, Andreas Karlsson -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5722: vacuum full does not update last_vacuum statistics
Tom Lane wrote: Jochen Erwied joc...@pgsql.erwied.eu writes: Monday, October 25, 2010, 4:12:39 PM you wrote: Jochen Erwied joc...@pgsql.erwied.eu writes: VACUUM FULL does not update statistics so display of pg_stat_user_tables is wrong. A normal VACUUM updates the relevant information. Hmm. This is a definitional issue: what do we really mean by last_vacuum? I'm inclined to think that the current behavior is reasonable. VACUUM FULL is (still) not intended as a routine maintenance operation, and the point of that column is to track routine maintenance operations. Well, when reading http://www.postgresql.org/docs/current/static/monitoring-stats.html then last_vacuum contains the last time of a user-initiated vacuum. There's no distinction made what kind of vacuum was made. And IMHO even if VACUUM FULL isn't meant for routine vacuuming, the state should be changed. Perhaps. The new implementation of VACUUM FULL is really more like a CLUSTER, or one of the rewriting variants of ALTER TABLE. Should all of those operations result in an update of last_vacuum? From an implementation standpoint it's difficult to say that only some of them should, because all of them result in a table that has no immediate need for vacuuming. The only argument I can see for having only VACUUM FULL update the timestamp is that it's called VACUUM and the others aren't. Which is an argument, but not a terribly impressive one IMO. Of course the easiest way to fix this bug (or better flaw) is to change the documentation :-) Yeah, that part of the docs will require editing no matter what we do. I'm just trying to get some clarity on what the most reasonable behavior is. I have updated the documentation to say that vacuum statistics and counts are for non-FULL vacuums; applied patch attached. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index 2dc1bfc..aaa613e 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -325,11 +325,11 @@ postgres: replaceableuser/ replaceabledatabase/ replaceablehost/ re scans, numbers of row insertions, updates, and deletions, number of row updates that were HOT (i.e., no separate index update), numbers of live and dead rows, - the last time the table was vacuumed manually, + the last time the table was non-optionFULL/ vacuumed manually, the last time it was vacuumed by the autovacuum daemon, the last time it was analyzed manually, the last time it was analyzed by the autovacuum daemon, - number of times it has been vacuumed manually, + number of times it has been non-optionFULL/ vacuumed manually, number of times it has been vacuumed by the autovacuum daemon, number of times it has been analyzed manually, and the number of times it has been analyzed by the autovacuum daemon. @@ -781,7 +781,7 @@ postgres: replaceableuser/ replaceabledatabase/ replaceablehost/ re entryliteralfunctionpg_stat_get_last_vacuum_time/function(typeoid/type)/literal/entry entrytypetimestamptz/type/entry entry - Time of the last vacuum initiated by the user on this table + Time of the last non-optionFULL/option vacuum initiated by the user on this table /entry /row @@ -814,7 +814,7 @@ postgres: replaceableuser/ replaceabledatabase/ replaceablehost/ re entryliteralfunctionpg_stat_get_vacuum_count/function(typeoid/type)/literal/entry entrytypebigint/type/entry entry - The number of times this table has been vacuumed manually + The number of times this table has been non-optionFULL/ vacuumed manually /entry /row -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5705: btree_gist: Index on inet changes query result
Teodor, would you please comment on this bug after reading the entire
thread which includes comments from other developers?
http://archives.postgresql.org/pgsql-bugs/2010-10/msg00099.php
Thanks.
---
Andreas Karlsson wrote:
The following bug has been logged online:
Bug reference: 5705
Logged by: Andreas Karlsson
Email address: andr...@proxel.se
PostgreSQL version: 9.1
Operating system: Linux
Description:btree_gist: Index on inet changes query result
Details:
Hi,
I was looking at the code to see how one would improve indexing of the inet
types and saw an inconsistency between the compressed format
(gbt_inet_compress) and how network_cmp_internal works. The btree_gist
module ignores the netmask.
This means that while the operator thinks 1.255.255.200/8 is smaller than
1.0.0.0 the GiST index thinks the opposite.
An example for how to reproduce the bug:
-- Demostrate that I did not get the operator wrong. :)
SELECT '1.255.255.200/8'::inet '1.0.0.0'::inet;
?column?
--
t
(1 row)
-- Create and populate table
CREATE TABLE inet_test (a inet);
INSERT INTO inet_test VALUES ('1.255.255.200/8');
-- Without index
SELECT * FROM inet_test WHERE a '1.0.0.0'::inet;
a
-
1.255.255.200/8
(1 row)
EXPLAIN SELECT * FROM inet_test WHERE a '1.0.0.0'::inet;
QUERY PLAN
-
Seq Scan on inet_test (cost=0.00..26.38 rows=437 width=32)
Filter: (a '1.0.0.0'::inet)
(2 rows)
-- With index
CREATE INDEX inet_test_idx ON inet_test USING gist (a);
SET enable_seqscan = false;
SELECT * FROM inet_test WHERE a '1.0.0.0'::inet;
a
---
(0 rows)
EXPLAIN SELECT * FROM inet_test WHERE a '1.0.0.0'::inet;
QUERY PLAN
Index Scan using inet_test_idx on inet_test (cost=0.00..8.27 rows=1
width=32)
Index Cond: (a '1.0.0.0'::inet)
(2 rows)
-- With btree index
DROP INDEX inet_test_idx;
CREATE INDEX inet_test_btree_idx ON inet_test USING btree (a);
SELECT * FROM inet_test WHERE a '1.0.0.0'::inet;
a
-
1.255.255.200/8
(1 row)
EXPLAIN SELECT * FROM inet_test WHERE a '1.0.0.0'::inet;
QUERY PLAN
Index Scan using inet_test_btree_idx on inet_test (cost=0.00..8.27 rows=1
width=32)
Index Cond: (a '1.0.0.0'::inet)
(2 rows)
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5707: Cross compilation for windows is broken
Has this been addressed? --- Alvaro Herrera wrote: Excerpts from Robert Haas's message of vie oct 29 13:23:39 -0300 2010: On Tue, Oct 12, 2010 at 2:25 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: Excerpts from Richard Evans's message of mar oct 12 12:48:45 -0300 2010: When cross compiling for Windows using a separate build area, libpq.dll does not build because the .def file cannot be found. This appears to be caused by these lines in Makefile.shlib: Hmm, apparently this was made to work here: http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=26af72b4 and subsequently broken later: http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=234c7ce9 I think the real fix is to make the DEF files be generated in the builddir, to complete the intention of the latter patch. Is anyone working on that? Not me. I tried, but mingw32-gcc doesn't work out of the box for me, because configure is not testing for the right accept() arguments. That's what I can do with the time I can dedicate to a problem like this right now (i.e. not much). -- ??lvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] issue about information_schema REFERENTIAL_CONSTRAINTS
Fabien COELHO wrote: If you're going to use something which is PostgreSQL-specific, you may as well write your own views or use the native tables and views directly. I wish I could write portable code, if possible:-) I'm basically writing views on top of the information_schema under the assumption that what is expected to be a key is a key. The information schema is *useless* otherwise as wrong tuples are built on join, and derived information is not reliable. I guess I must the only actual user of the information_schema, and it will soon be back to zero user, which will be fine from the developers point of view. Based on this report and later discussion, I have applied the attached documentation patch to warn users about the Postgres behavior of information_schema.referential_constraints. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + diff --git a/doc/src/sgml/information_schema.sgml b/doc/src/sgml/information_schema.sgml index 52407a7..91c2dd4 100644 *** a/doc/src/sgml/information_schema.sgml --- b/doc/src/sgml/information_schema.sgml *** ORDER BY c.ordinal_position; *** 3211,3216 --- 3211,3229 /tbody /tgroup /table + + note +para + The SQL standard requires constraint names to be unique within a + schema; productnamePostgreSQL/productname, however, does not + enforce this restriction. If duplicate-named constraints are + stored in the same productnamePostgreSQL/productname schema, a + standard-compliant query that expects to return one row might + return several, one for each matching constraint stored in the + specified schema. +/para + /note + /sect1 sect1 id=infoschema-role-column-grants -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] possible bug: orphaned files left after immediate shutdown during DDL
Jeff Davis wrote: On Wed, 2011-02-09 at 22:58 -0500, Tom Lane wrote: It's intentional ... not that other people haven't complained about it before. Remember that what you have done is forced a crash, and recovery from it is crash recovery. If we proactively removed such files we would very possibly be destroying evidence of forensic value. I thought that might be the case, but I wasn't able to find any previous discussions. It might be a good idea to issue a warning during recovery, however, like possible orphaned file I'm not sure if it's worth the bookkeeping effort though. I thought we had a TODO item about removing orphaned files, but I don't see it now, perhaps because I thought we had fixed that. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5559: Full SSL verification fails when hostaddr provided
Christopher Head wrote: On Wed, 14 Jul 2010 18:35:55 -0400 Tom Lane t...@sss.pgh.pa.us wrote: Bruce Momjian br...@momjian.us writes: Do the docs need any more updating? No doubt, but it's a bit premature to consider that while we're still arguing whether the code needs to change more. regards, tom lane Sorry to bother everyone, but AFAICT this discussion kind of disappeared. Did I perhaps get dropped from CC? I'm interested to know what the final resolution of this is. My own thought would be: host means the thing you intended to connect to: a unique identifier for the server, probably (usually) the hostname, and also the thing that goes in a certificate. Should (probably) never be omitted. hostaddr means the thing you actually send your TCP SYN packet to: maybe an IP address if you want to save a DNS lookup, maybe even localhost if you want to use an SSH tunnel (or even some other hostname if you have an even stranger tunnel set up), but purely a network-layer thing about *how to get to* the server, and not a user-trust-layer thing about *who the server is*. If omitted, defaults to being equal to host. I don't know if that's what was intended, but that's what I thought they would mean. I have adjusted the libpq docs to be clearer about 'hostaddr' by using an itemized list and rewording; attached and applied. I am not sure what else needs to be done, and I don't think anyone else knows either, so unless I hear otherwise, I will consider this item closed. Perhaps the clearer docs will highlight a new open item. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + diff --git a/doc/src/sgml/libpq.sgml b/doc/src/sgml/libpq.sgml index e78d708..3824588 100644 *** a/doc/src/sgml/libpq.sgml --- b/doc/src/sgml/libpq.sgml *** PGconn *PQconnectdbParams(const char **k *** 164,185 Using literalhostaddr/ instead of literalhost/ allows the application to avoid a host name look-up, which might be important in applications with time constraints. However, a host name is !required for Kerberos, GSSAPI, or SSPI authentication, as well as !for full SSL certificate verification. The following rules are !used: !If literalhost/ is specified without literalhostaddr/, !a host name lookup occurs. !If literalhostaddr/ is specified without literalhost/, !the value for literalhostaddr/ gives the server network address. !The connection attempt will fail in any of the cases where a !host name is required. !If both literalhost/ and literalhostaddr/ are specified, !the value for literalhostaddr/ gives the server network address. !The value for literalhost/ is ignored unless needed for !authentication or verification purposes, in which case it will be !used as the host name. Note that authentication is likely to fail !if literalhost/ is not the name of the machine at !literalhostaddr/. Also, note that literalhost/ rather than literalhostaddr/ is used to identify the connection in filename~/.pgpass/ (see xref linkend=libpq-pgpass). --- 164,199 Using literalhostaddr/ instead of literalhost/ allows the application to avoid a host name look-up, which might be important in applications with time constraints. However, a host name is !required for Kerberos, GSSAPI, or SSPI authentication !methods, as well as for literalverify-full/ SSL !certificate verification. The following rules are used: !itemizedlist ! listitem ! para ! If literalhost/ is specified without literalhostaddr/, ! a host name lookup occurs. ! /para ! /listitem ! listitem ! para ! If literalhostaddr/ is specified without literalhost/, ! the value for literalhostaddr/ gives the server network address. ! The connection attempt will fail if the authentication ! method requires a host name. ! /para ! /listitem ! listitem ! para ! If both literalhost/ and literalhostaddr/ are specified, ! the value for literalhostaddr/ gives the server network address. ! The value for literalhost/ is ignored unless the ! authentication method requires it, in which case it will be ! used as the host name. ! /para ! /listitem !/itemizedlist !Note that authentication is likely to fail if literalhost
Re: [BUGS] superuser unable to modify settings of a system table
Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: Is there really a use case for users fiddling with pg_proc, pg_class, etc. directly? There's a use case for *superusers* to fiddle with them, yes. (Superusers are presumed to be adults.) I think I recommend a quick UPDATE on some catalog at least once a month on the lists. You might care to consider the fact that no modern Unix system prevents root from doing rm -rf /, even though that's obviously disastrous. Yet (stretching the analogy all out of shape) there's no convenient user tool for rearranging the contents of all the inodes on a filesystem. At any rate, I'd be happy to drop that part of the proposal. It would be a step forward just to permit (even without allow_system_table_mods) those changes which don't alter the structure of the catalog. For ALTER TABLE, the SET STATISTICS, (RE)SET (attribute_option), SET STORAGE, CLUSTER ON, SET WITHOUT CLUSTER, and (RE)SET (reloptions) forms are all things that fall into this category, I believe. It would be far less work to just drop allow_system_table_mods to SUSET. And we wouldn't get questions about which forms of ALTER TABLE require it. Are we going to make the allow_system_table_mods to SUSET change? Is it a TODO? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Feature request: include script file into function body
Steve White wrote: Hi Kevin, On 1.02.11, Kevin Grittner wrote: [Please don't top-post. Rearranged for clarity.] As you like. Steve White swh...@aip.de wrote: On 1.02.11, Tom Lane wrote: Steve White swh...@aip.de writes: It would be really nice to have a way to load script (especially Python and Perl) from a separate file into a function body. This seems like a security hole, ie, you could use it to read any file the backend has access to. Isn't the \i command a similar security hole? That is run by a client program on a client machine. Sorry I don't understand this remark. Are you saying that \i is disabled to user postgres? Just tried: it isn't. Are you saying that as a normal user I can use \i to load a file that I don't normally have access to? Just tried: nope -- permission denied. What scenario do you have in mind? \i is a psql client command, not something the backend runs. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5854: base64 decode returns bytea and no text
Chris R. wrote:
The following bug has been logged online:
Bug reference: 5854
Logged by: Chris R.
Email address: chri...@gmx.net
PostgreSQL version: 9.02
Operating system: CentOS 5.5
Description:base64 decode returns bytea and no text
Details:
There is a break in how pg9.0 handles decoding base64 encoded data.
With PostgreSQL 8.4:
select decode(encode('abc', 'base64'), 'base64');
decode
--
\x616263
With PostgreSQL 9.0:
select decode(encode('abc', 'base64'), 'base64');
decode
--
\x616263
To get the old result, convert_from helps out:
select convert_from(decode(encode('abc', 'base64'), 'base64'), 'UTF8');
Still, shouldn't this be consistent with 8.x and 9.x?
Uh, they look the same to me. cut/paste error?
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5781: unaccent() function should be marked IMMUTABLE
Robert Haas wrote: On Wed, Dec 22, 2010 at 8:45 PM, Bruce Momjian br...@momjian.us wrote: Tom Lane wrote: Grant Hutchins and Peter Jaros gr...@pivotallabs.com writes: The unaccent(text) function supplied by contrib/unaccent is marked VOLATILE. This prevents it from being used in indexes. We believe that the function meets the requirements to be marked IMMUTABLE. No, it most certainly doesn't. ?It depends on the behavior of a dictionary that it has no hard-wired connection to, so the specific behavior of the dictionary is uncertain. ?Even if you're willing to assume that the dictionary being used is the one defined by this module, that dictionary depends on external configuration files which are easily changeable. Arguably it'd be reasonable to change the function's marking from volatile to stable, but that's not going to be enough to allow use in indexes. So, should we change unaccent() from VOLATILE to STABLE? Sounds like it, but it doesn't sound like it will help much. :-( OK, done, with attached, applied patch. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + diff --git a/contrib/unaccent/unaccent.sql.in b/contrib/unaccent/unaccent.sql.in index 7e397cc..6d712e7 100644 *** /tmp/l3zFae_unaccent.sql.in Mon Dec 27 15:33:55 2010 --- contrib/unaccent/unaccent.sql.in Mon Dec 27 15:24:16 2010 *** SET search_path = public; *** 6,17 CREATE OR REPLACE FUNCTION unaccent(regdictionary, text) RETURNS text AS 'MODULE_PATHNAME', 'unaccent_dict' ! LANGUAGE C STRICT; CREATE OR REPLACE FUNCTION unaccent(text) RETURNS text AS 'MODULE_PATHNAME', 'unaccent_dict' ! LANGUAGE C STRICT; CREATE OR REPLACE FUNCTION unaccent_init(internal) RETURNS internal --- 6,17 CREATE OR REPLACE FUNCTION unaccent(regdictionary, text) RETURNS text AS 'MODULE_PATHNAME', 'unaccent_dict' ! LANGUAGE C STABLE STRICT; CREATE OR REPLACE FUNCTION unaccent(text) RETURNS text AS 'MODULE_PATHNAME', 'unaccent_dict' ! LANGUAGE C STABLE STRICT; CREATE OR REPLACE FUNCTION unaccent_init(internal) RETURNS internal -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5784: CREATE INDEX USING GIN complains about array containing null values yet none exist
Heikki Linnakangas wrote: On 05.12.2010 18:26, Tom Lane wrote: Andres Freundand...@anarazel.de writes: On Sunday 05 December 2010 17:42:59 Tom Lane wrote: I think the reason the given example fails is just that it's all being done in one transaction. If the null-containing row were known dead it wouldn't get indexed. So: commit. Um I doubt it. [ gets out gdb... ] Oh: the reason GIN is complaining is that it's just looking at ARR_HASNULL(), and the array's has-nulls flag is still set because we don't bother to try to clear it after replacing one element of the array. (Which in general would be an expensive thing to try to do...) If we were intending to leave GIN in its current nulls-hating state, the thing to do would be to replace the stupid ARR_HASNULL check with something more intelligent. But really it needs to be fixed to handle nulls properly, so I'm thinking that might be a dead-end patch. Sounds like we'd still want to just replace ARR_HASNULL() with something more intelligent in back-branches though. Added to TODO: Improve GIN's handling of NULL array values * http://archives.postgresql.org/pgsql-bugs/2010-12/msg00032.php -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5781: unaccent() function should be marked IMMUTABLE
Tom Lane wrote: Grant Hutchins and Peter Jaros gr...@pivotallabs.com writes: The unaccent(text) function supplied by contrib/unaccent is marked VOLATILE. This prevents it from being used in indexes. We believe that the function meets the requirements to be marked IMMUTABLE. No, it most certainly doesn't. It depends on the behavior of a dictionary that it has no hard-wired connection to, so the specific behavior of the dictionary is uncertain. Even if you're willing to assume that the dictionary being used is the one defined by this module, that dictionary depends on external configuration files which are easily changeable. Arguably it'd be reasonable to change the function's marking from volatile to stable, but that's not going to be enough to allow use in indexes. So, should we change unaccent() from VOLATILE to STABLE? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5768: Inefficiency of large offsets should be mentioned on SELECT documentation page
Pavel Arnost wrote: The following bug has been logged online: Bug reference: 5768 Logged by: Pavel Arnost Email address: pavel.arn...@loutka.cz PostgreSQL version: 9.01 Operating system: N/A Description:Inefficiency of large offsets should be mentioned on SELECT documentation page Details: Inefficiency of large offsets should be mentioned on SELECT documentation page - now it's only on LIMIT and OFFSET page. I have no idea what you are suggesting. There is no LIMIT and OFFSET page in the manuals. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5721: EnterpriseDB linux one-click installer missing libuuid.so.16
Alexia Lau wrote: The following bug has been logged online: Bug reference: 5721 Logged by: Alexia Lau Email address: a...@esri.com PostgreSQL version: 9.0.1 Operating system: SUSE 11/RHEL 5.5 32-bit 64-bit Description:EnterpriseDB linux one-click installer missing libuuid.so.16 Details: Hi, I'm running into the same problem reported with bug #5677, but I'm installing on SUSE 11 and RedHat 5.5. I saw a thread below and Dave Page mentioned that there's a bug which didn't copy the library properly which has been fixed at PostgreSQL 9.0.2. What is the bug number related to this and when is the planned release for 9.0.2? Will EnterpriseDB fix the one-click installer for linux builds to this? The release of PG 9.0.2 is really under the control of the community timetable. I am not sure when the next minor update will happen. Sorry. --- Thank you, Alexia http://archives.postgresql.org/pgsql-general/2010-10/msg00311.php On Thu, Oct 7, 2010 at 3:56 AM, Tom Lane t...@x wrote: Mike Christensen m...@x writes: On Wed, Oct 6, 2010 at 7:38 PM, Tom Lane t...@x wrote: If you have a libossp-uuid.so.16, you might try symlinking libuuid.so.16 to that instead of carrying a separate file. So now what you're saying is if it's not broke, fix it till it is :) Well, it's hard to argue with that position ;-). But I'll try anyway: the platform-provided version of the library will be updated for bug fixes, compatibility rebuilds, etc. Your private copy won't be, unless you remember to do it. Eventually that's gonna bite ya. Of course the best fix would be for EDB to ship a build of Postgres that actually follows the platform-standard naming convention for this library. I'm still wondering why they're linking to libuuid.so. Dave? Because that's what comes with ossp-uuid 1.6.2, and I assume is what configure chooses when we use --with-ossp-uuid: [buildf...@bf2-linux ~]$ uuid-config --libs -luuid FYI, there was also a bug in the installer which didn't copy the library properly, which has been fixed for 9.0.2. -- Dave Page PostgreSQL Core Team http://www.postgresql.org/ -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5735: pg_upgrade thinks that it did not start the old server
Arturas Mazeika wrote: On 11/10/2010 05:32 AM, Tom Lane wrote: Bruce Momjianbr...@momjian.us writes: On 10/30/2010 7:33 PM, Dave Page wrote: upgrade from a 32bit 8.3 server to a 64 bit 9.0 server, which isn't going to work without a dump/restore. With pg_upgrade, the two builds need to be from the same platform, same word size, and have the same configuration for certain settings like integer_datetimes. Can anyone suggest a way pg_upgrade could detect an upgrade from a 32-bit to 64-bit cpu and throw an error? Surely it does that already, as a result of comparing pg_control contents. The HTML manual might need an update or a small clarification too. Currently, it does not seem that the manual explicitly states that ``pg_upgrade is not applicable in upgrading 32bit systems to 64bit ones''. A good place to write such a sentence would be at the beginning of [1], at the intro of F.32. pg_upgrade. Maybe the documentation already implicitly states that in F.32.4. Limitations in Migrating from PostgreSQL 8.3 section of [1] by this description: ``For Windows users, note that due to different integer datetimes settings used by the one-click installer and the MSI installer, it is only possible to upgrade from version 8.3 of the one-click distribution to version 8.4 or later of the one-click distribution. It is not possible to upgrade from the MSI installer to the one-click installer.'' Unfortunately, I could not understand in full detail the above. Thanks, arturas [1] http://www.postgresql.org/docs/9.0/static/pgupgrade.html I have added a mention about 32/64-bit isssues to the pg_upgrade manual page, attached. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + commit 8f742d1cdab987ba4624ad0c6aa008ced15cd87c Author: Bruce Momjian br...@momjian.us Date: Wed Nov 10 14:08:30 2010 + Mention that pg_upgrade requires compatible 32/64-bit binaries. diff --git a/doc/src/sgml/pgupgrade.sgml b/doc/src/sgml/pgupgrade.sgml index 6d2cdaa..9081cc9 100644 --- a/doc/src/sgml/pgupgrade.sgml +++ b/doc/src/sgml/pgupgrade.sgml @@ -21,7 +21,8 @@ regularly added to PostgreSQL major releases, the internal data storage format rarely changes. applicationpg_upgrade/ does its best to make sure the old and new clusters are binary-compatible, e.g. by - checking for compatible compile-time settings. It is important that + checking for compatible compile-time settings, including 32/64-bit + binaries. It is important that any external modules are also binary compatible, though this cannot be checked by applicationpg_upgrade/. /para -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5735: pg_upgrade thinks that it did not start the old server
Tom Lane wrote: Bruce Momjian br...@momjian.us writes: On 10/30/2010 7:33 PM, Dave Page wrote: upgrade from a 32bit 8.3 server to a 64 bit 9.0 server, which isn't going to work without a dump/restore. With pg_upgrade, the two builds need to be from the same platform, same word size, and have the same configuration for certain settings like integer_datetimes. Can anyone suggest a way pg_upgrade could detect an upgrade from a 32-bit to 64-bit cpu and throw an error? Surely it does that already, as a result of comparing pg_control contents. Surely it does, but I didn't understand how the user able to run pg_upgrade? I see now that he failed before we completed our checks so he would have gotten an error later if he could have started his server: http://archives.postgresql.org/pgsql-bugs/2010-10/msg00282.php Thanks. Not sure why he was unable to start the old server, but we decided he couldn't use pg_upgrade anyway in his setup. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5735: pg_upgrade thinks that it did not start the old server
Arturas Mazeika wrote: Hi Dave, Thanks for the info, this explains a lot. Yes, I am upgrading from the 32bit version to the 64bit one. We have pretty large databases (some over 1 trillion of rows, and some containing large documents in blobs.) Giving a bit more memory than 4GB limit to Postgres was what we were long longing for. Postgres was able to handle large datasets (I suppose it uses something like long long (64bit) data type in C++) and I hoped naively that Postgres would be able to migrate from one version to the other without too much trouble. I tried to pg_dump one of the DBs with large documents. I failed with out of memory error. I suppose it is rather hard to migrate in my case :-( Any suggestions? Thanks, arturas On 10/30/2010 7:33 PM, Dave Page wrote: upgrade from a 32bit 8.3 server to a 64 bit 9.0 server, which isn't going to work without a dump/restore. With pg_upgrade, the two builds need to be from the same platform, same word size, and have the same configuration for certain settings like integer_datetimes. Can anyone suggest a way pg_upgrade could detect an upgrade from a 32-bit to 64-bit cpu and throw an error? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5650: Postgres service showing as stopped when in fact it is running
Robert Haas wrote: On Mon, Sep 27, 2010 at 12:44 PM, Ashesh Vashi ashesh.va...@enterprisedb.com wrote: We're happy to see the problem resolved on your end. :-)-- However, it doesn't seem that we've actually done anything about the underlying issue with pg_ctl. I will look at that in the next few weeks. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5682: Postgres Service crashes with exception 0xC0000135
Craig Ringer wrote: On 29/09/2010 5:11 PM, aswin jayaraman wrote: I ll try that out.Is there means to do an upgrade to 8.3 to 8.4/9.0 with the data in place? No, it requires a dump and reload. Sorry. You'll want to read the release notes, as there have been changes between 8.3 and 9.0 that may affect applications. http://www.postgresql.org/docs/9/static/release-8-4.html http://www.postgresql.org/docs/9/static/release-9-0.html PostgreSQL has seen significant improvements in the Windows port, so it's worth the update. I unfortunately cannot promise that it'll fix the issue you're having, though. pg_upgrade works for upgrades from 8.3 to 9.0. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] UNLISTEN bug
Jeff Davis wrote: In honor of the very first bug report I sent to postgresql more than 10 years ago regarding UNLISTEN[1], I have decided to submit another UNLISTEN bug (against HEAD): Session1: LISTEN foo; BEGIN; UNLISTEN foo; Session2: NOTIFY foo; Session1: SELECT 1; COMMIT; SELECT 1; I seem to recall testing out similar situations during my review of this patch, but I think the code has changed since that time. So the problem report is? I tested it and the problem is that the final SELECT 1 hung. Is that the problem? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] UNLISTEN bug
Bruce Momjian wrote: Jeff Davis wrote: In honor of the very first bug report I sent to postgresql more than 10 years ago regarding UNLISTEN[1], I have decided to submit another UNLISTEN bug (against HEAD): Session1: LISTEN foo; BEGIN; UNLISTEN foo; Session2: NOTIFY foo; Session1: SELECT 1; COMMIT; SELECT 1; I seem to recall testing out similar situations during my review of this patch, but I think the code has changed since that time. So the problem report is? I tested it and the problem is that the final SELECT 1 hung. Is that the problem? To confirm, it was majorly hung. Cancel and kill did not work, pg_ctl -m fast did not work either. I had to kill -3. Bad. :-( -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5660: Can't start db service if specify effective_io_concurrency
tkbysh2...@yahoo.co.jp wrote: Hi, Thank you very much for your quick support. I've understood. Please close this bug ticket. I hope add comment e.g(unix only) onto effective_io_concurrency line in postgresql.conf if possible. We currently have in our docs: Asynchronous I/O depends on an effective functionposix_fadvise/ function, which some operating systems lack. If the function is not present then setting this parameter to anything but zero will result in an error. On some operating systems (e.g., Solaris), the function is present but does not actually do anything. It is not Unix-only, but only some versions of Unix. --- Regards. -- tkbysh2...@yahoo.co.jp On Thu, 16 Sep 2010 10:36:19 -0400 Tom Lane t...@sss.pgh.pa.us wrote: Mikio tkbysh2...@yahoo.co.jp writes: I'm using postgresql 9.0 rc1, and I specified 10 for effective_io_concurrency in postgresql.conf. I restarted postgresql windows service, but the service didn't start. This is unsurprising: you can only set effective_io_concurrency on platforms that have posix_fadvise(), which I rather doubt Windows does. I looked windows event viewer, I found an event from PostgreSQL, and it was below.(The characters in the message were broken.) FATAL: p[^effective_io_concurrency? Hm, what I get when I try that on a machine without posix_fadvise() is FATAL: parameter effective_io_concurrency cannot be changed I think you have some other configuration problem that's messing up your log entries. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5656: parameter 'client_min_messages' accept values not listed in enumvals
Euler Taveira de Oliveira wrote:
Tom Lane escreveu:
thommy der.tho...@gmx.net writes:
I just came across a small inconsistency:
pg=# select enumvals from pg_settings where name='client_min_messages';
enumvals
---
{debug5,debug4,debug3,debug2,debug1,log,notice,warning,error}
It's intentional that PANIC isn't listed there (nor is FATAL),
on the grounds that it's not really a useful setting.
Fine. But shouldn't we remove these options from docs and/or code?
We are basically reusing the same validation code for this and other
min_messages settings. Is it worth creating a custom one just for
client_min_messages? Probably not.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5629: ALTER SEQUENCE foo START execute a RESTART
Alexsander Rosa wrote: Well, if it's not going to be fixed, then at least the docs should be revised to warn all 8.4+ users to avoid this command and, if it's really needed, always check the server version before using the ALTER SEQUENCE ... START command, once it has a potentially hazardous bug that interprets it as a RESTART in 8.3 version and below. The proper place for such notification is the 8.4 release notes, which states: Sequences now contain an additional 'start_value' column (Zoltan Boszormenyi) This supports commandALTER SEQUENCE ... RESTART. Which means basically 8.3 didn't have a start field at all. This is the first complaint I have heard about it. Do you have new wording to suggest? Perhaps: In previous releases, START behaved as RESTART. --- 2010/9/7 Bruce Momjian br...@momjian.us Alexsander Rosa wrote: What about the risk of using ALTER SEQUENCE ... START N in a mixed environment? In the 8.4.x servers it will work as designed but in the 8.3.x (and below) servers, instead of issuing an error it will CORRUPT the sequence value without notice. I understand the point of keeping a (mis)feature when it's harmless or at least not amibiguous, but this is not the case here. While the 8.4 behavior -- the correct one -- is a mere configuration of little consequence, the 8.3 (and below) behavior is an unexpected RESET. I think it's safer to require the people that was using old versions with the wrong spell to fix their code than put lots of users of the current version in risk of using a potentially disastrous command -- when executed in previous versions. Should all 8.4.x (and beyond) users be forced to check server version before issuing this command? Should all 8.3 users be required to retest their applications after a minor upgrade? No. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Atenciosamente, Alexsander da Rosa Linux User #113925 Extremismo na defesa da liberdade n?o ? defeito. Modera??o na busca por justi?a n?o ? virtude. -- Barry Goldwater -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5629: ALTER SEQUENCE foo START execute a RESTART
Alexsander Rosa wrote: At 8.4 release notes, item E.5.3.3.1. TRUNCATE there's a sentence: The start value of a sequence can be changed by ALTER SEQUENCE START WITH. Maybe this sentence should be copied/moved to E.5.3.4.1. ALTER with extra text stating that START, in previous versions, was an (unintended) alias to RESTART -- with the wording you suggested or something like that. The advise to check server_version when using this command could be mentioned, also. I don't think we have had enough people confused by this to add that level of detail. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5642: pg_upgrade does not handle shared libraries for language handlers
David Platt wrote: The following bug has been logged online: Bug reference: 5642 Logged by: David Platt Email address: davidpl...@davidplatt.com PostgreSQL version: 9.0 RC1 Operating system: CentOS 5.5 Description:pg_upgrade does not handle shared libraries for language handlers Details: The following definition is my database: CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler LANGUAGE c AS '/opt/postgres/8.4.1/lib/plpgsql', 'plpgsql_call_handler'; The file /opt/postgres/9.rc1/lib/plpgsql.o exists but the pg_upgrade run fails on an error loading /opt/postgres/8.4.1/lib/plpgsql.o. What is the error? What old version of PG are you migrating from? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5647: COPY TO does not respect the [standard_conforming_strings] setting
Tom Lane wrote: David Sahagian david.sahag...@gmail.com writes: Description:COPY TO does not respect the [standard_conforming_strings] setting This is not a bug. The COPY format is well defined and is not going to be changed by standard_conforming_strings. Yes, remember COPY data are not strings, they are delimited data, so there is no standard to match here. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5642: pg_upgrade does not handle shared libraries for language handlers
Tom Lane wrote: Bruce Momjian br...@momjian.us writes: David Platt wrote: The following definition is my database: CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler LANGUAGE c AS '/opt/postgres/8.4.1/lib/plpgsql', 'plpgsql_call_handler'; The file /opt/postgres/9.rc1/lib/plpgsql.o exists but the pg_upgrade run fails on an error loading /opt/postgres/8.4.1/lib/plpgsql.o. What is the error? What old version of PG are you migrating from? Well, it's obviously going to fail, because it will try to load an 8.4 version of plpgsql.so into 9.0. The same would happen if you tried to pg_dump and reload --- it's by no means the fault of pg_upgrade. IMO this is just pilot error. The call handler should never have been declared like that, precisely because the definition will not port to other releases or even other installation locations. The right way for the definition to look like is ... AS '$libdir/plpgsql' or perhaps even just ... AS 'plpgsql' if you'd like to rely on the dynamic_library_path setting. I suspect David thinks that pg_upgrade should try to edit the library path name, but IMO that would be seriously dangerous, as well as not necessary if reasonable practices have been followed. I am confused how it got defined that way? Who would be defining their own plpgsql handler? I am concerned there is some packaging that is impoperly defining it. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5631: psql dumps core during command editing
David L Kensiski wrote: I though of that, but it's linked into the binary. Maybe this is a problem with the Sun build and not the code? How do I get in touch with the build maintainer? Is there a README in the build install somewhere? --- --Dave to...@i2:~$ /usr/ccs/bin/nm -o /usr/postgres/current/bin/64/psql | grep readline [542] |20547260|0072|FUNC |GLOB |0|12 | initialize_readline [924] |20676600|0610|FUNC |GLOB |0|12 | readline [306] |||FILE |LOCL |0|ABS| readline.c [834] |22164570|0004|OBJT |GLOB |0|19 | readline_echoing_p [866] |22165210|0010|OBJT |GLOB |0|19 | rl_readline_name On Aug 27, 2010, at 9:38 PM, Tom Lane wrote: David Kensiski da...@schoolloop.com writes: The psql binary (64-bit from binary install) dumps core when I attempt to edit the input line after a few characters are entered: This most likely indicates a problem with the libreadline or libedit library you're using. regards, tom lane -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5632: won't unistall properly
Robert Koch wrote: The following bug has been logged online: Bug reference: 5632 Logged by: Robert Koch Email address: expenda...@aemail4u.com PostgreSQL version: 8.4.4-1 Operating system: Windows 2000 Pro Description:won't unistall properly Details: When installing, PostgreSQL fails to create an entry in Windows' Add/Remove Programs feature. One can remove the files and directories by hand, but the System Registry features about a couple dozen entries for PostgreSQL, some of which regedit.exe cannot remove. A cleaner uninstall feature is necessary. Similarly, the bundled versions of Apache and phpBB leave fingerprints in the registry when being removed by hand, as necessitated by their absence from the approved Add/Remove Programs method. I know the Add/Remove Programs link gets created on XP; in fact this is the first reported failure we have heard about this. Where did you get the Postgres download from? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] pg_upgrade issues
depst...@alliedtesting.com wrote: Bruce Momjian wrote: depst...@alliedtesting.com wrote: I am trying to obtain a binary dump of a small test database where this issue could be reproduced, but so far, no luck. At present, the least such database is 1.5 GB compressed and contains a lot of proprietary info. I would welcome any suggestions on how to do this. Your diagnosis is 100% on target, and very perceptive. Because we preserve pg_class.relfilenode, if the table has not been rebuilt, for example by CLUSTER, the old system the pg_class.oid and pg_class.relfilenode are the same, and hence pg_class.oid is preserved through pg_class.relfilenode during the migration. If they are different, e.g. they ran CLUSTER, pg_upgrade will be wrong because the oid has changed, and you will see the errors you are reporting. I am inclined to prevent pg_upgrade from migrating any database that uses any of these reg* data types, and document this restriction. I probably could allow regtype because that pg_type is preserved. I have applied the attached patch to CVS HEAD and 9.0 that prevent migration when any reg* data type is used in a user table (except regtype because pg_type.oid is preserved). I documented this restriction. Thanks again for the report. Thank you for the explanation and the swift action. I just want to note that one reason regclass may be used in user tables (as opposed to, say, regtype) is that in PL/pgSQL trigger procedures there is a special variable TG_RELID, which provides a convenient reference to the table that pulled the trigger (this is the case for some of our uses). OK, thanks. I was curious about your usage so I could determine how widespread usage of those reg* types is. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] pg_upgrade issues
Robert Haas wrote: On Sat, Jul 24, 2010 at 11:37 PM, Bruce Momjian br...@momjian.us wrote: I am inclined to prevent pg_upgrade from migrating any database that uses any of these reg* data types, and document this restriction. ?I probably could allow regtype because that pg_type is preserved. I have applied the attached patch to CVS HEAD and 9.0 that prevent migration when any reg* data type is used in a user table (except regtype because pg_type.oid is preserved). This is a good change; however, there is still some potential for lossage here. What if the column were declared as type OID? Then it would be hard to tell whether migration was safe or not. Perhaps the right long-term solution is to try harder to preserve OIDs in more cases. You are right that an oid column cannot be tracked easily. It could refer to a user table with oids, or it might be a system row reference. I have considered preserving more oids, but that is going to increase the backend changes for pg_upgrade, and I am hesistant to do that until there is a claarer demand. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] pg_upgrade issues
Alvaro Herrera wrote: Excerpts from depstein's message of lun jul 26 08:05:24 -0400 2010: I just want to note that one reason regclass may be used in user tables (as opposed to, say, regtype) is that in PL/pgSQL trigger procedures there is a special variable TG_RELID, which provides a convenient reference to the table that pulled the trigger (this is the case for some of our uses). I've wanted to use regclass (and regproc too, for that matter) in some db designs, but I've refrained precisely because of the movability issues. Were you worried about pg_upgrade movability issues, or just general movability issues? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] pg_upgrade issues
Bruce Momjian wrote:
depst...@alliedtesting.com wrote:
I am trying to obtain a binary dump of a small test database where this
issue could be reproduced, but so far, no luck. At present, the least
such database is 1.5 GB compressed and contains a lot of proprietary
info. I would welcome any suggestions on how to do this.
Your diagnosis is 100% on target, and very perceptive. Because we
preserve pg_class.relfilenode, if the table has not been rebuilt, for
example by CLUSTER, the old system the pg_class.oid and
pg_class.relfilenode are the same, and hence pg_class.oid is preserved
through pg_class.relfilenode during the migration. If they are
different, e.g. they ran CLUSTER, pg_upgrade will be wrong because the
oid has changed, and you will see the errors you are reporting.
I am inclined to prevent pg_upgrade from migrating any database that
uses any of these reg* data types, and document this restriction. I
probably could allow regtype because that pg_type is preserved.
I have applied the attached patch to CVS HEAD and 9.0 that prevent
migration when any reg* data type is used in a user table (except
regtype because pg_type.oid is preserved).
I documented this restriction. Thanks again for the report.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ None of us is going to be here forever. +
Index: contrib/pg_upgrade/check.c
===
RCS file: /cvsroot/pgsql/contrib/pg_upgrade/check.c,v
retrieving revision 1.12
diff -c -c -r1.12 check.c
*** contrib/pg_upgrade/check.c 13 Jul 2010 15:56:53 - 1.12
--- contrib/pg_upgrade/check.c 25 Jul 2010 03:19:48 -
***
*** 14,19
--- 14,20
static void check_new_db_is_empty(migratorContext *ctx);
static void check_locale_and_encoding(migratorContext *ctx, ControlData *oldctrl,
ControlData *newctrl);
+ static void check_for_reg_data_type_usage(migratorContext *ctx, Cluster whichCluster);
void
***
*** 61,71
* Check for various failure cases
*/
! old_8_3_check_for_isn_and_int8_passing_mismatch(ctx, CLUSTER_OLD);
/* old = PG 8.3 checks? */
if (GET_MAJOR_VERSION(ctx-old.major_version) = 803)
{
old_8_3_check_for_name_data_type_usage(ctx, CLUSTER_OLD);
old_8_3_check_for_tsquery_usage(ctx, CLUSTER_OLD);
if (ctx-check)
--- 62,73
* Check for various failure cases
*/
! check_for_reg_data_type_usage(ctx, CLUSTER_OLD);
/* old = PG 8.3 checks? */
if (GET_MAJOR_VERSION(ctx-old.major_version) = 803)
{
+ old_8_3_check_for_isn_and_int8_passing_mismatch(ctx, CLUSTER_OLD);
old_8_3_check_for_name_data_type_usage(ctx, CLUSTER_OLD);
old_8_3_check_for_tsquery_usage(ctx, CLUSTER_OLD);
if (ctx-check)
***
*** 439,441
--- 441,544
check_ok(ctx);
}
+
+
+ /*
+ * check_for_reg_data_type_usage()
+ * pg_upgrade only preserves these system values:
+ * pg_class.relfilenode
+ * pg_type.oid
+ * pg_enum.oid
+ *
+ * Most of the reg* data types reference system catalog info that is
+ * not preserved, and hence these data types cannot be used in user
+ * tables upgraded by pg_upgrade.
+ */
+ void
+ check_for_reg_data_type_usage(migratorContext *ctx, Cluster whichCluster)
+ {
+ ClusterInfo *active_cluster = (whichCluster == CLUSTER_OLD) ?
+ ctx-old : ctx-new;
+ int dbnum;
+ FILE *script = NULL;
+ bool found = false;
+ char output_path[MAXPGPATH];
+
+ prep_status(ctx, Checking for reg* system oid user data types);
+
+ snprintf(output_path, sizeof(output_path), %s/tables_using_reg.txt,
+ ctx-cwd);
+
+ for (dbnum = 0; dbnum active_cluster-dbarr.ndbs; dbnum++)
+ {
+ PGresult *res;
+ bool db_used = false;
+ int ntups;
+ int rowno;
+ int i_nspname,
+ i_relname,
+ i_attname;
+ DbInfo *active_db = active_cluster-dbarr.dbs[dbnum];
+ PGconn *conn = connectToServer(ctx, active_db-db_name, whichCluster);
+
+ res = executeQueryOrDie(ctx, conn,
+ SELECT n.nspname, c.relname, a.attname
+ FROM pg_catalog.pg_class c,
+ pg_catalog.pg_namespace n,
+ pg_catalog.pg_attribute a
+ WHERE c.oid = a.attrelid AND
+ NOT a.attisdropped AND
+ a.atttypid IN (
+ 'pg_catalog.regproc'::pg_catalog.regtype,
+ 'pg_catalog.regprocedure'::pg_catalog.regtype,
+ 'pg_catalog.regoper'::pg_catalog.regtype,
+ 'pg_catalog.regoperator'::pg_catalog.regtype,
+ 'pg_catalog.regclass'::pg_catalog.regtype,
+ /* regtype.oid is preserved, so 'regtype' is OK */
+ 'pg_catalog.regconfig'::pg_catalog.regtype,
+ 'pg_catalog.regdictionary'::pg_catalog.regtype) AND
+ c.relnamespace = n.oid AND
+ n.nspname != 'pg_catalog' AND
+ n.nspname != 'information_schema
Re: [BUGS] pg_upgrade issues
Bruce Momjian wrote:
I have applied the attached patch to CVS HEAD and 9.0 that prevent
migration when any reg* data type is used in a user table (except
regtype because pg_type.oid is preserved).
I documented this restriction. Thanks again for the report.
Attached is a secondary patch for /contrib/isn, in case you want that
too.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ None of us is going to be here forever. +
Index: contrib/pg_upgrade/check.c
===
RCS file: /cvsroot/pgsql/contrib/pg_upgrade/check.c,v
retrieving revision 1.13
diff -c -c -r1.13 check.c
*** contrib/pg_upgrade/check.c 25 Jul 2010 03:28:32 - 1.13
--- contrib/pg_upgrade/check.c 25 Jul 2010 03:43:07 -
***
*** 14,19
--- 14,21
static void check_new_db_is_empty(migratorContext *ctx);
static void check_locale_and_encoding(migratorContext *ctx, ControlData *oldctrl,
ControlData *newctrl);
+ static void check_for_isn_and_int8_passing_mismatch(migratorContext *ctx,
+ Cluster whichCluster);
static void check_for_reg_data_type_usage(migratorContext *ctx, Cluster whichCluster);
***
*** 63,73
*/
check_for_reg_data_type_usage(ctx, CLUSTER_OLD);
/* old = PG 8.3 checks? */
if (GET_MAJOR_VERSION(ctx-old.major_version) = 803)
{
- old_8_3_check_for_isn_and_int8_passing_mismatch(ctx, CLUSTER_OLD);
old_8_3_check_for_name_data_type_usage(ctx, CLUSTER_OLD);
old_8_3_check_for_tsquery_usage(ctx, CLUSTER_OLD);
if (ctx-check)
--- 65,75
*/
check_for_reg_data_type_usage(ctx, CLUSTER_OLD);
+ check_for_isn_and_int8_passing_mismatch(ctx, CLUSTER_OLD);
/* old = PG 8.3 checks? */
if (GET_MAJOR_VERSION(ctx-old.major_version) = 803)
{
old_8_3_check_for_name_data_type_usage(ctx, CLUSTER_OLD);
old_8_3_check_for_tsquery_usage(ctx, CLUSTER_OLD);
if (ctx-check)
***
*** 444,449
--- 446,543
/*
+ * check_for_isn_and_int8_passing_mismatch()
+ *
+ * /contrib/isn relies on data type int8, and in 8.4 int8 can now be passed
+ * by value. The schema dumps the CREATE TYPE PASSEDBYVALUE setting so
+ * it must match for the old and new servers.
+ */
+ void
+ check_for_isn_and_int8_passing_mismatch(migratorContext *ctx, Cluster whichCluster)
+ {
+ ClusterInfo *active_cluster = (whichCluster == CLUSTER_OLD) ?
+ ctx-old : ctx-new;
+ int dbnum;
+ FILE *script = NULL;
+ bool found = false;
+ char output_path[MAXPGPATH];
+
+ prep_status(ctx, Checking for /contrib/isn with bigint-passing mismatch);
+
+ if (ctx-old.controldata.float8_pass_by_value ==
+ ctx-new.controldata.float8_pass_by_value)
+ {
+ /* no mismatch */
+ check_ok(ctx);
+ return;
+ }
+
+ snprintf(output_path, sizeof(output_path), %s/contrib_isn_and_int8_pass_by_value.txt,
+ ctx-cwd);
+
+ for (dbnum = 0; dbnum active_cluster-dbarr.ndbs; dbnum++)
+ {
+ PGresult *res;
+ bool db_used = false;
+ int ntups;
+ int rowno;
+ int i_nspname,
+ i_proname;
+ DbInfo *active_db = active_cluster-dbarr.dbs[dbnum];
+ PGconn *conn = connectToServer(ctx, active_db-db_name, whichCluster);
+
+ /* Find any functions coming from contrib/isn */
+ res = executeQueryOrDie(ctx, conn,
+ SELECT n.nspname, p.proname
+ FROM pg_catalog.pg_proc p,
+ pg_catalog.pg_namespace n
+ WHERE p.pronamespace = n.oid AND
+ p.probin = '$libdir/isn');
+
+ ntups = PQntuples(res);
+ i_nspname = PQfnumber(res, nspname);
+ i_proname = PQfnumber(res, proname);
+ for (rowno = 0; rowno ntups; rowno++)
+ {
+ found = true;
+ if (script == NULL (script = fopen(output_path, w)) == NULL)
+ pg_log(ctx, PG_FATAL, Could not create necessary file: %s\n, output_path);
+ if (!db_used)
+ {
+ fprintf(script, Database: %s\n, active_db-db_name);
+ db_used = true;
+ }
+ fprintf(script, %s.%s\n,
+ PQgetvalue(res, rowno, i_nspname),
+ PQgetvalue(res, rowno, i_proname));
+ }
+
+ PQclear(res);
+
+ PQfinish(conn);
+ }
+
+ if (found)
+ {
+ fclose(script);
+ pg_log(ctx, PG_REPORT, fatal\n);
+ pg_log(ctx, PG_FATAL,
+ | Your installation contains \/contrib/isn\ functions\n
+ | which rely on the bigint data type. Your old and\n
+ | new clusters pass bigint values differently so this\n
+ | cluster cannot currently be upgraded. You can\n
+ | manually migrate data that use \/contrib/isn\\n
+ | facilities and remove \/contrib/isn\ from the\n
+ | old cluster and restart the migration. A list\n
+ | of the problem functions is in the file:\n
+ | \t%s\n\n, output_path);
+ }
+ else
+ check_ok(ctx);
+ }
+
+
+ /*
* check_for_reg_data_type_usage()
* pg_upgrade only preserves these system values
Re: [BUGS] pg_upgrade issues
depst...@alliedtesting.com wrote: I have encountered another problem with pg_upgrade, while migrating from 8.4 to 9.0 (beta2, as well as beta3) on Windows XP Pro. Wow, your testing of pg_upgrade has been excellent! I hope you can continue and test other areas of our system too. I am actually curious how you are so good at this. I have a table with a regclass column, which references other tables in the same database: CREATE TABLE common_inst.reg_asset ( asset_id integer NOT NULL, table_name regclass, CONSTRAINT asset_registered_pkey PRIMARY KEY (asset_id) ) Sometimes after I migrate the database, the values in the table_name column show integer numbers (e.g. '284551' for a table named 'common_inst.asset_spot_equity_index') instead of table references. These numbers are the OIDs of the tables in the old database, but in the new database these OIDs have no referent. Ah, I never thought of the migrations issues of user tables using the reg* data types: pg_catalog | regclass| registered class pg_catalog | regconfig | registered text search configuration pg_catalog | regdictionary | registered text search dictionary pg_catalog | regoper | registered operator pg_catalog | regoperator | registered operator (with args) pg_catalog | regproc | registered procedure pg_catalog | regprocedure| registered procedure (with args) pg_catalog | regtype | registered type In fact, I never even considered that user tables would be using these data types. The basic problem is that we don't preserve most of these oids when recreating them in the new cluster --- we only preserve pg_type.oid, pg_class.relfilenode, and pg_enum.oid. FWIW, when looking at the pg_class entries for the referenced tables, I have noticed that in the old database the table OID and the column relfilenode have different values. In the migrated database the values are the same and coincide with relfilenode in the old database. For example, Old database: Table name: common_inst.asset_spot_equity_index pg_class.oid = 284551 pg_class.relfilenode = 288011 Migrated database: Table name: common_inst.asset_spot_equity_index pg_class.oid = 288011 pg_class.relfilenode = 288011 I am trying to obtain a binary dump of a small test database where this issue could be reproduced, but so far, no luck. At present, the least such database is 1.5 GB compressed and contains a lot of proprietary info. I would welcome any suggestions on how to do this. Your diagnosis is 100% on target, and very perceptive. Because we preserve pg_class.relfilenode, if the table has not been rebuilt, for example by CLUSTER, the old system the pg_class.oid and pg_class.relfilenode are the same, and hence pg_class.oid is preserved through pg_class.relfilenode during the migration. If they are different, e.g. they ran CLUSTER, pg_upgrade will be wrong because the oid has changed, and you will see the errors you are reporting. I am inclined to prevent pg_upgrade from migrating any database that uses any of these reg* data types, and document this restriction. I probably could allow regtype because that pg_type is preserved. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5559: Full SSL verification fails when hostaddr provided
Do the docs need any more updating? --- Tom Lane wrote: Stephen Frost sfr...@snowman.net writes: Perhaps I was being a bit overzealous in my last response, sorry about that. If the point here is that people who are using hostaddr are in an environment where DNS is non-functional or actively broken, then yes, just bombing out would probably be fine. Well, if your environment includes broken DNS then you are clearly going to get nowhere anyway with Kerberos auth, no? The point of hostaddr is *not* to try to avoid that problem. Rather, it's to allow the application to shift the time expense of the forward DNS lookup to some other place than its PQconnect() call. If you've got an app where the cost of PQconnect() is that critical, you're likely going to want to avoid Kerberos auth anyway, so I don't think it's all that important exactly how the two features play together. As the code stands in HEAD, I think everything is nicely self-consistent: host is what we believe the server name is for authentication purposes, and hostaddr is an optional pre-looked-up address corresponding to that. There is nothing in this suggesting that we should be expected to try to generate an authentication name from hostaddr alone. In particular, the fact that Kerberos is capable of trying to do that is at odds with the other three code paths where the server name is needed for authentication. I don't feel any need to expose Kerberos' peculiarity here. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] pg_upgrade issues
depst...@alliedtesting.com wrote:
Encountered another problem with pg_upgrade on Windows XP Pro:
I was trying to migrate from 8.4 to 9.0beta2 without linking, and
apparently there was not enough space on the hard drive. However,
pg_upgrade didn't report any problems, and it looked for all the world
as if everything went well. I only found out that not all files were
copied to the new cluster when vacuumdb reported missing files and when
I actually compared the sizes of the two clusters on the disk.
Thank you for the clear bug report. Magnus has diagnosed the problem,
and I am attaching the patch fix that will appear in 9.0 beta4.
Fortunately this problem only happens in copy mode, and only when the
copy fails, as you saw.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ None of us is going to be here forever. +
Index: contrib/pg_upgrade/file.c
===
RCS file: /cvsroot/pgsql/contrib/pg_upgrade/file.c,v
retrieving revision 1.13
diff -c -c -r1.13 file.c
*** contrib/pg_upgrade/file.c 6 Jul 2010 19:18:55 - 1.13
--- contrib/pg_upgrade/file.c 9 Jul 2010 16:41:46 -
***
*** 170,175
--- 170,177
if (nbytes 0)
{
+ int save_errno = errno;
+
if (buffer != NULL)
free(buffer);
***
*** 179,184
--- 181,187
if (dest_fd != 0)
close(dest_fd);
+ errno = save_errno;
return -1;
}
***
*** 190,197
if (write(dest_fd, buffer, nbytes) != nbytes)
{
/* if write didn't set errno, assume problem is no disk space */
! if (errno == 0)
! errno = ENOSPC;
if (buffer != NULL)
free(buffer);
--- 193,199
if (write(dest_fd, buffer, nbytes) != nbytes)
{
/* if write didn't set errno, assume problem is no disk space */
! int save_errno = errno ? errno : ENOSPC;
if (buffer != NULL)
free(buffer);
***
*** 202,207
--- 204,210
if (dest_fd != 0)
close(dest_fd);
+ errno = save_errno;
return -1;
}
}
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5531: REGEXP_ REPLACE causes connection drop
Ola Sergatchov wrote: The following bug has been logged online: Bug reference: 5531 Logged by: Ola Sergatchov Email address: ola_sergatc...@hotmail.com PostgreSQL version: 8.1.18 Operating system: RedHat Linux 4.1.2-46 Description:REGEXP_ REPLACE causes connection drop Details: Passing large string to REGEXP_REPLACE function causes the DB connection to drop. We executed this function with very large strings (20,000 - 250,000 characters) both from RedHat and PgAdmin and in both cases the function fails to return and eventually the connection drops. From looking in the documentation, there is no reference to the maximum size of the string that his function can process. Can you show us any relevant entries in the server logs? FYI, 8.1.18 is both old for minor and major release. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] pg_upgrade issues
Hiroshi Saito wrote: Hi. Ooops, I can't follow your quick thread sorry, It will be a weekend if allowed. I have replied and I think I have it fixed. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] pg_upgrade issues
depst...@alliedtesting.com wrote: BB PostgreSQL 9.0 beta 2 Windows XP Professional SP2 While migrating the database from 8.4 to 9.0 using pg_upgrade (now part of the Postgres project), the following issues came up: 1. When using the --logfile option, pg_upgrade quits with an error like this: The process cannot access the file because it is being used by another process. There were problems executing C:\PostgreSQL\8.4\bin/pg_ctl -l pg_upgrade.log -D D:\PostgreSQL84_matlab1b -o -p 5432 -c autovacuum=off -c autovacuum_free ze_max_age=20 start pg_upgrade.log 21 Hiroshi, can you comment on the above bug report? I was able to reproduce this on XP. I think we added -l for Win32 because the code says: /* use -l for Win32 */ snprintf(cmd, sizeof(cmd), SYSTEMQUOTE \%s/pg_ctl\ -l \%s\ -D \%s\ but I don't remember the details, and cvs.pgfoundry.org is down right now. Thanks. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5498: PgAdmin III write to file adds carriage return
Ramesh wrote: The following bug has been logged online: Bug reference: 5498 Logged by: Ramesh Email address: ramn...@rediffmail.com PostgreSQL version: 8.1.7 Operating system: Linux Description:PgAdmin III write to file adds carriage return Details: I have connected PgAdmin III version 1.10.3 to PostgresSQL database version 8.1.7. I am trying to generate a report with comma (,) seperated values and the columns are with in double quotes. Once its saved when I check the report the last column is like below, after the value is printed a carriage return is added (\n) to the last column and then a quote is added. a,b,c,d e,f,g,h Can some point me a fix for this ? I think you need to report this to pgadmin: http://www.pgadmin.org/ -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
Stefan Kaltenbrunner wrote: I do agree that the human readability of pg_dump is an asset in many situations - I have often dumped out the DDL for particular objects just to look at it, for example. However, I emphatically do NOT agree that leaving someone with a 500MB dump file (or, for some people on this list, a whole heck of a lot larger than that) that has to be manually edited to reload is a useful behavior. It's a huge pain in the neck. well that's why we recommend to use the new version of pg_dump to dump the old cluster if the intention is an upgrade not sure that is any more pain than manually hacking the dump... Or rename the identifier in the old cluster and modify the application before doing the upgrade. The only valid reason I have heard for allowing this flag (default off), is that some application stacks quote all identifiers and therefore there would be no need to ever change the name of the identifier. In fact, such stacks might already have many identifers that require quoting, like a table called select. The problem is that some of our reserved keywords change from release to release, and using the old pg_dump causes problems. It is sufficient to require people using such application stacks to use the new pg_dump? From a code perspective, the difficulting in adding such a flag is that much of the quoting happens inside the backend, not by pg_dump, and therefore there is significant code change required to add this flag. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
Magnus Hagander wrote: On Thu, Jun 10, 2010 at 15:35, Stefan Kaltenbrunner ste...@kaltenbrunner.cc wrote: Robert Haas wrote: On Thu, Jun 10, 2010 at 9:02 AM, Stefan Kaltenbrunner ste...@kaltenbrunner.cc wrote: I for myself would be rather annoyed if we started quoting all column names in our dumps. This is seriously hampering readability and while it is already annoying that pg_dump output is slightly different from the original DDL used this would make it far worse. It's only been proposed to make it an option, not to shove it down anyone's throat. that will pretty much defeat the purpose for most use cases i guess because people will dump with the defaults and only discover the problem after the fact. Well, if you dump in custom format, it could be useful to be able to do this on pg_restore time. Not having followed this thread in detail, but would that work? That would be a much more useful option... I don't think so because much of the quoting has to be done in the backend, and it would be hard for pg_dump to munge the dump file before sending it to the backend --- it doesn't have enough knowledge, I am afraid. It could try targeting just new keywords, but I am worried that would cause more problems than it fixes. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Invalid YAML output from EXPLAIN
Robert Haas wrote:
On Wed, Jun 9, 2010 at 4:48 PM, Robert Haas robertmh...@gmail.com wrote:
On Wed, Jun 9, 2010 at 4:47 PM, Dean Rasheed dean.a.rash...@gmail.com
wrote:
On 9 June 2010 20:56, Robert Haas robertmh...@gmail.com wrote:
On Wed, Jun 9, 2010 at 3:50 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Dean Rasheed dean.a.rash...@gmail.com writes:
Hmm. Well it's quite subjective, but IMO it's already more readable
than JSON regardless of whether or not values are quoted, simply
because it doesn't have [ ] and { } for lists and maps, which for JSON
adds significantly to the number of lines in longer plans.
Yeah. ?Also, I think it would be fair to not quote values that are known
constants (for example, Node Type: Seq Scan) and are chosen to not need
quoting. ?It's just the things that are variables that worry me.
Passing down information about which things are known constants seems
more complicated to me than just getting the quoting rules right in
the first place. ?If you look at the patch I proposed, you'll see that
it's really quite simple and only a slight tightening of what I
committed already.
Reading the YAML spec, I've just spotted yet another case that'll
break what you're proposing: if you don't quote true and false,
the parser will think they're booleans rather than strings.
This is really why I'm opposed to this approach. There are just so
many gotchas that it's impossible to be 100% sure that you've
accounted for them all.
OK, I give up.
I have committed your patch, with some changes to the comments.
Thanks for bearing with me.
So, is there still value to a YAML format vs. JSON? They look similar
to me in this simple case:
test= EXPLAIN (FORMAT JSON) SELECT * FROM pg_class;
QUERY PLAN
[ +
{ +
Plan: { +
Node Type: Seq Scan,+
Relation Name: pg_class,+
Alias: pg_class,+
Startup Cost: 0.00, +
Total Cost: 9.53, +
Plan Rows: 253, +
Plan Width: 190 +
} +
} +
]
(1 row)
test= EXPLAIN (FORMAT YAML) SELECT * FROM pg_class;
QUERY PLAN
---
- Plan: +
Node Type: Seq Scan+
Relation Name: pg_class+
Alias: pg_class+
Startup Cost: 0.00 +
Total Cost: 9.53 +
Plan Rows: 253 +
Plan Width: 190
(1 row)
Is unquoted identifiers the only value for YAML?
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ None of us is going to be here forever. +
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Invalid YAML output from EXPLAIN
Tom Lane wrote: Bruce Momjian br...@momjian.us writes: So, is there still value to a YAML format vs. JSON? They look similar to me in this simple case: Well, removing the various braces and brackets reduces the line count significantly. Not convinced it's really worth much though. Ah, I see that now. Thanks. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
Robert Haas wrote: On Sun, Jun 6, 2010 at 2:53 PM, Dimitri Fontaine dfonta...@hi-media.com wrote: Robert Haas robertmh...@gmail.com writes: Well as Bruce said this option won't solve the OP's problem, unless the application he's using for managing the backups do use the option. Well, that's a pretty trivial change to the backup script. ?+1 from me on providing a pg_dump option. The application still have to have been using the option in the past. Well, if your point is that it's too late to help anyone upgrading from 8.3 to 8.4, then I agree with you. But we will likely add more keywords at some point in the future, and while providing an output format that quotes everything won't fix every potential problem, it might make life easier for some people. I certainly have had times where it would have saved me hassle and aggravation. The point is that if WINDOW was not a reserved word in 8.3 but is in 8.4, then every reference to a user column of WINDOW in any 8.4 application will need to be double-quoted, and odds are the user did not do that in 8.3. I think users would rather have the restore fail, and know right away they have an issue, than to do the upgrade, and find out later that some of their application queries fail and they need to run around fixing them. (FYI, pg_upgrade would use the new pg_dump and would not fail.) In a way, the fact that the restore fails can be seen as a feature --- they get the error before the go live on 8.4. (Yeah, I am serious.) -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
Robert Haas wrote: I think users would rather have the restore fail, and know right away they have an issue, than to do the upgrade, and find out later that some of their application queries fail and they need to run around fixing them. ?(FYI, pg_upgrade would use the new pg_dump and would not fail.) In a way, the fact that the restore fails can be seen as a feature --- they get the error before the go live on 8.4. ?(Yeah, I am serious.) Eeh, I've had this happen to me on earlier releases, and it didn't feel like a feature to me. YMMV, of course. Would you have preferred later application failure? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
Alvaro Herrera wrote: Excerpts from Bruce Momjian's message of mi?? jun 09 21:10:21 -0400 2010: I think users would rather have the restore fail, and know right away they have an issue, than to do the upgrade, and find out later that some of their application queries fail and they need to run around fixing them. (FYI, pg_upgrade would use the new pg_dump and would not fail.) I think it is quite a stretch to consider this a feature. How about a desireable behavior considering the alternatives? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
Kevin Grittner wrote: Hartmut Goebel h.goe...@goebel-consult.de wrote: The application already quotes all column names :-) It's using a generic framework which does not (and must not) rely on column names being non-keywords. Same here. I suspect that this is much more commonn than many PostgreSQL developers realize; and I think it makes a reasonable case for at least an *option* to quote all identifiers emitted by pg_dump. Even if we quote them in the dump, I assume applications would need to quote them too, which I doubt many do. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
David Fetter wrote: On Fri, Jun 04, 2010 at 02:59:48PM -0400, Bruce Momjian wrote: Kevin Grittner wrote: Hartmut Goebel h.goe...@goebel-consult.de wrote: The application already quotes all column names :-) It's using a generic framework which does not (and must not) rely on column names being non-keywords. Same here. I suspect that this is much more commonn than many PostgreSQL developers realize; and I think it makes a reasonable case for at least an *option* to quote all identifiers emitted by pg_dump. Even if we quote them in the dump, I assume applications would need to quote them too, which I doubt many do. It seems like something that's doable by pg_dump as a default off option. TODO for 9.1? This is the bug report that prompted this thread: http://archives.postgresql.org/pgsql-bugs/2010-06/msg00018.php I bigger question is why wouldn't we backpatch WINDOW as quoted in pg_dump when we release back-branches? That would make the bug go away, rather than require users to use a special flag (and find out only after they were doing the reload). -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5364: citext behavior when type not in public schema
Tom Lane wrote: Bruce Momjian br...@momjian.us writes: I have documented this citext limitation with the attached, applied patch. Are you planning to insert similar verbiage into every other contrib module's docs? Uh, do they all have this odd behavior? Most people assume they would get an error in such cases, not case-sensitivity. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5469: regexp_matches() has poor behaviour and more poor documentation
Bruce Momjian wrote: Robert Haas wrote: On Mon, May 31, 2010 at 10:11 AM, Bruce Momjian br...@momjian.us wrote: Robert Haas wrote: On Sat, May 29, 2010 at 5:00 PM, Bruce Momjian br...@momjian.us wrote: I have updated the patch, attached, to clarify that this returns text arrays, and that you can force it to always return one row using COALESCE() and a '|' pattern (the later suggested by Daniele Varrazzo). I don't find this part to be something we should include in the documentation. ?If we want to include a workaround, how about defining a non-SRF that just calls the SRF and returns the first row? Remember this has to return one row for no matches, so a simple SRF will not work. ?I also have not seen enough demand for another function. ?A single doc mention seemed the appropriate level of detail for this. Well, we can debate later whether to add another function to core, but what I meant was that the user having the problem could create a user-defined function that calls regexp_matches() and returns the first row, or NULL. But actually here's an even simpler workaround, which is IMHO less ugly than the original one: SELECT foo, bar, (SELECT regexp_matches(bar, pattern)) FROM table; Good idea. Simplified patch attached. Applied. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5364: citext behavior when type not in public schema
Markus Wichitill wrote: On 03.06.2010 05:05, Bruce Momjian wrote: The schema containing the typecitext/ operators must be in the current varnamesearch_path/ (typically literalpublic/); It's been a while, but the way I read my own example is that the schema containing the citext operators being in the current search_path isn't enough. public must be in the search_path, too, even if it's not really involved. Uh, that doesn't make any sense because there is nothing special about 'public'. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5364: citext behavior when type not in public schema
Robert Haas wrote: On Fri, Mar 5, 2010 at 5:24 AM, Markus Wichitill ma...@gmx.de wrote: The following bug has been logged online: Bug reference: ? ? ?5364 Logged by: ? ? ? ? ?Markus Wichitill Email address: ? ? ?ma...@gmx.de PostgreSQL version: 8.4.2 Operating system: ? Linux, Win7 Description: ? ? ? ?citext behavior when type not in public schema Details: Comparisons with columns of type citext silently work case-sensitively without any error message, unless the search_path contains public, even if the type is not located in public, but in the same schema as the table using it. Interestingly we recently got another report of this same problem. Tom did some analysis of it here: http://archives.postgresql.org/pgsql-bugs/2010-03/msg00017.php I have documented this citext limitation with the attached, applied patch. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + Index: doc/src/sgml/citext.sgml === RCS file: /cvsroot/pgsql/doc/src/sgml/citext.sgml,v retrieving revision 1.2 diff -c -c -r1.2 citext.sgml *** doc/src/sgml/citext.sgml 12 Sep 2008 18:29:49 - 1.2 --- doc/src/sgml/citext.sgml 3 Jun 2010 03:02:48 - *** *** 205,210 --- 205,219 will need two indexes if you want both types of searches to be fast. /para /listitem + + listitem + para + The schema containing the typecitext/ operators must be + in the current varnamesearch_path/ (typically literalpublic/); + if it is not, a normal case-sensitive typetext/ comparison + is performed. + /para + /listitem /itemizedlist /sect2 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5469: regexp_matches() has poor behaviour and more poor documentation
Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: But actually here's an even simpler workaround, which is IMHO less ugly than the original one: SELECT foo, bar, (SELECT regexp_matches(bar, pattern)) FROM table; Doesn't that blow up if the subselect returns more than one row? I think you could make it work by wrapping regexp_matches in a simple (non-SETOF) SQL function, but just writing out the sub-SELECT doesn't do it. This goes back to the recent discussion of why SQL functions can't always be inlined --- the semantics are a bit different in some cases. If you don't use 'g' as a third argument, it can't return more than one row. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5469: regexp_matches() has poor behaviour and more poor documentation
Robert Haas wrote: On Mon, May 31, 2010 at 10:11 AM, Bruce Momjian br...@momjian.us wrote: Robert Haas wrote: On Sat, May 29, 2010 at 5:00 PM, Bruce Momjian br...@momjian.us wrote: I have updated the patch, attached, to clarify that this returns text arrays, and that you can force it to always return one row using COALESCE() and a '|' pattern (the later suggested by Daniele Varrazzo). I don't find this part to be something we should include in the documentation. ?If we want to include a workaround, how about defining a non-SRF that just calls the SRF and returns the first row? Remember this has to return one row for no matches, so a simple SRF will not work. ?I also have not seen enough demand for another function. ?A single doc mention seemed the appropriate level of detail for this. Well, we can debate later whether to add another function to core, but what I meant was that the user having the problem could create a user-defined function that calls regexp_matches() and returns the first row, or NULL. But actually here's an even simpler workaround, which is IMHO less ugly than the original one: SELECT foo, bar, (SELECT regexp_matches(bar, pattern)) FROM table; Good idea. Simplified patch attached. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + Index: doc/src/sgml/func.sgml === RCS file: /cvsroot/pgsql/doc/src/sgml/func.sgml,v retrieving revision 1.513 diff -c -c -r1.513 func.sgml *** doc/src/sgml/func.sgml 7 Apr 2010 06:12:52 - 1.513 --- doc/src/sgml/func.sgml 1 Jun 2010 14:40:22 - *** *** 3445,3463 /para para ! The functionregexp_matches/ function returns all of the captured ! substrings resulting from matching a POSIX regular expression pattern. ! It has the syntax functionregexp_matches/function(replaceablestring/, replaceablepattern/ optional, replaceableflags/ /optional). ! If there is no match to the replaceablepattern/, the function returns ! no rows. If there is a match, the function returns a text array whose replaceablen/'th element is the substring matching the replaceablen/'th parenthesized subexpression of the pattern (not counting quotenon-capturing/ parentheses; see below for ! details). If the pattern does not contain any parenthesized ! subexpressions, then the result is a single-element text array containing ! the substring matching the whole pattern. The replaceableflags/ parameter is an optional text string containing zero or more single-letter flags that change the function's behavior. Flag literalg/ causes the function to find --- 3445,3466 /para para ! The functionregexp_matches/ function returns a text array of ! all of the captured substrings resulting from matching a POSIX ! regular expression pattern. It has the syntax functionregexp_matches/function(replaceablestring/, replaceablepattern/ optional, replaceableflags/ /optional). ! The function can return no rows, one row, or multiple rows (see ! the literalg/ flag below). If the replaceablepattern/ ! does not match, the function returns no rows. If the pattern ! contains no parenthesized subexpressions, then each row ! returned is a single-element text array containing the substring ! matching the whole pattern. If the pattern contains parenthesized ! subexpressions, the function returns a text array whose replaceablen/'th element is the substring matching the replaceablen/'th parenthesized subexpression of the pattern (not counting quotenon-capturing/ parentheses; see below for ! details). The replaceableflags/ parameter is an optional text string containing zero or more single-letter flags that change the function's behavior. Flag literalg/ causes the function to find *** *** 3490,3495 --- 3493,3508 /programlisting /para +para + It is possible to force functionregexp_matches()/ to always + return one row by using a sub-select; this is particularly useful + in a literalSELECT/ target list when you want all rows + returned, even non-matching ones: + programlisting + SELECT col1, (SELECT regexp_matches(col2, '(bar)(beque)')) FROM tab; + /programlisting +/para + para The functionregexp_split_to_table/ function splits a string using a POSIX regular expression pattern as a delimiter. It has the syntax -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5469: regexp_matches() has poor behaviour and more poor documentation
Daniele Varrazzo wrote: On Sun, May 30, 2010 at 4:45 AM, Robert Haas robertmh...@gmail.com wrote: On Sat, May 29, 2010 at 5:00 PM, Bruce Momjian br...@momjian.us wrote: I have updated the patch, attached, to clarify that this returns text arrays, and that you can force it to always return one row using COALESCE() and a '|' pattern (the later suggested by Daniele Varrazzo). I don't find this part to be something we should include in the documentation. ?If we want to include a workaround, how about defining a non-SRF that just calls the SRF and returns the first row? I think a documentation correction could be backported without problem to all the currently maintained version of PostgreSQL (which would be of good google value, as very often google searches lands you to previous releases doc pages), whereas a easier to use function would be a new feature and as such could only be introduced in 9.0 or even 9.1. While you might have had this problem, it is not a common problem so not something we are about to take tons of time addressing. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5416: int4inc() is wrong
John Regehr wrote: Hi Tom, If you can show me rewrites of all the basic arithmetic operations that detect overflow in full compliance with the C standard, and are readable, portable, and efficient, I'm all ears. These are the best ones that I know of: https://www.securecoding.cert.org/confluence/display/seccode/INT32-C.+Ensure+that+operations+on+signed+integers+do+not+result+in+overflow Even if you dislike these, please take a look at the safety checks for shifts. The current postgresql shift functions need to be strengthened, and it is easy to do. Added to TODO: Consider improving overflow detection * http://archives.postgresql.org/message-id/4bc66a57.2030...@cs.utah.edu -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5416: int4inc() is wrong
Tom Lane wrote: Bruce Momjian br...@momjian.us writes: Consider improving overflow detection * http://archives.postgresql.org/message-id/4bc66a57.2030...@cs.utah.edu I did look at those at the time, and saw absolutely no reason to prefer them over what we do now. OK, removed from TODO. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Bug in CREATE FUNCTION with character type (CONFIRMED BUG)
Pavel Stehule wrote: 2010/4/15 Tom Lane t...@sss.pgh.pa.us: Pavel Stehule pavel.steh...@gmail.com writes: I think, so RETURNS TABLE can be modified for returning typmode without significant problems - this function is called in table context and I don't see any problematic use case. RETURNS TABLE is just a shorthand for some OUT parameters. ?I don't believe it's either easy or a good idea to make it work differently from every other function-argument-or-result case. I don't know now. It minimally have to be documented Can you suggest some documentation? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5477: CREATE DOMAIN NOT NULL constraints not always enforced for INSERT with subquery
Applied. --- Bruce Momjian wrote: Tom Lane wrote: Matt Nourse matt...@nplus1.com.au writes: CREATE DOMAIN test_id_domain INT NOT NULL; CREATE TABLE test_state(id test_id_domain PRIMARY KEY, display_value varchar(20) NOT NULL); CREATE TABLE test_city(state_id test_id_domain REFERENCES test_state(id)); This produces an error as expected: INSERT INTO test_city(state_id) VALUES (NULL); This successfully inserts a NULL value into the state_id field: INSERT INTO test_city(state_id) VALUES ((SELECT id FROM test_state WHERE display_value = 'Nonexistent state')); There are any number of ways you can get a similar result, for example a LEFT JOIN. To my mind, this demonstrates why not-null constraints associated with datatypes are a fundamentally flawed concept. If the SELECT or LEFT JOIN can produce a null value, as it clearly can, then it's nonsensical to think that the output column should be considered to be of a NOT NULL domain type. But what else should it be? If we smash domains to their base types when assigning result types of queries, that will make many people unhappy. Moral: NOT NULL constraints at the domain level suck. Don't use 'em. I have written up the following documentation patch to document this behavior. It doesn't seem like something we want to fix, so I am not making it a TODO item. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com [ text/x-diff is unsupported, treating like TEXT/PLAIN ] Index: doc/src/sgml/ref/create_domain.sgml === RCS file: /cvsroot/pgsql/doc/src/sgml/ref/create_domain.sgml,v retrieving revision 1.34 diff -c -c -r1.34 create_domain.sgml *** doc/src/sgml/ref/create_domain.sgml 3 Apr 2010 07:22:58 - 1.34 --- doc/src/sgml/ref/create_domain.sgml 28 May 2010 17:19:35 - *** *** 121,127 termliteralNOT NULL//term listitem para ! Values of this domain are not allowed to be null. /para /listitem /varlistentry --- 121,132 termliteralNOT NULL//term listitem para ! Values of this domain are normally prevented from being null. ! It is still possible for a domain with this constraint ! to take a null value if it is assigned a matching domain type ! that has become null, e.g. via a LEFT OUTER JOIN, or ! commandINSERT INTO tab (domcol) VALUES ((SELECT domcol FROM ! tab WHERE false))/command. /para /listitem /varlistentry -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5469: regexp_matches() has poor behaviour and more poor documentation
I have updated the patch, attached, to clarify that this returns text arrays, and that you can force it to always return one row using COALESCE() and a '|' pattern (the later suggested by Daniele Varrazzo). --- Bruce Momjian wrote: Daniele Varrazzo wrote: If there is no match to the pattern, the function returns no rows is easily overlooked as it returns null, or some other behaviour that don't change the returned set. The point is, because the function is listed in the string function, you would expect the function to manipulate text, not the dataset. The function as it is is not safe to be used in a construct SELECT foo, bar, regexp_matches(bar, pattern) FROM table; unless you really wanted: SELECT foo, bar, regexp_matches(bar, pattern) FROM table WHERE bar ~ pattern; otherwise you have to take measures to be able to deal with records in which the pattern is not matched, for example: SELECT foo, bar, regexp_matches(bar, pattern || '|') FROM table; the latter still doesn't work when bar is NULL: in this case the record is dropped anyway, so I don't think it can be proposed as general solution. The characteristics of returning a set of text[] is useful when the user wants all the matches, not only the first one: the behaviour is selected specifying the flag 'g' as third argument. From this point of view, I hope it can be stated that in its current form the regexp_matches() has not the most optimal interface. Please accept my apology for the tone being too rude in my previous message. I found the description in the documentation quite confusing also. I have created the attached documention patch which is clearer about the behavior of regexp_matches(). -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com Index: doc/src/sgml/func.sgml === RCS file: /cvsroot/pgsql/doc/src/sgml/func.sgml,v retrieving revision 1.513 diff -c -c -r1.513 func.sgml *** doc/src/sgml/func.sgml 7 Apr 2010 06:12:52 - 1.513 --- doc/src/sgml/func.sgml 29 May 2010 20:55:23 - *** *** 3445,3463 /para para ! The functionregexp_matches/ function returns all of the captured ! substrings resulting from matching a POSIX regular expression pattern. ! It has the syntax functionregexp_matches/function(replaceablestring/, replaceablepattern/ optional, replaceableflags/ /optional). ! If there is no match to the replaceablepattern/, the function returns ! no rows. If there is a match, the function returns a text array whose replaceablen/'th element is the substring matching the replaceablen/'th parenthesized subexpression of the pattern (not counting quotenon-capturing/ parentheses; see below for ! details). If the pattern does not contain any parenthesized ! subexpressions, then the result is a single-element text array containing ! the substring matching the whole pattern. The replaceableflags/ parameter is an optional text string containing zero or more single-letter flags that change the function's behavior. Flag literalg/ causes the function to find --- 3445,3466 /para para ! The functionregexp_matches/ function returns a text array of ! all of the captured substrings resulting from matching a POSIX ! regular expression pattern. It has the syntax functionregexp_matches/function(replaceablestring/, replaceablepattern/ optional, replaceableflags/ /optional). ! The function can return no rows, one row, or multiple rows (see ! the literalg/ flag below). If the replaceablepattern/ ! does not match, the function returns no rows. If the pattern ! contains no parenthesized subexpressions, then each row ! returned is a single-element text array containing the substring ! matching the whole pattern. If the pattern contains parenthesized ! subexpressions, the function returns a text array whose replaceablen/'th element is the substring matching the replaceablen/'th parenthesized subexpression of the pattern (not counting quotenon-capturing/ parentheses; see below for ! details). The replaceableflags/ parameter is an optional text string containing zero or more single-letter flags that change the function's behavior. Flag literalg/ causes the function to find *** *** 3490,3495 --- 3493,3509 /programlisting /para +para + It is possible to force functionregexp_matches()/ to always + return one row by using functionCOALESCE()/ and an empty + literal|/ pattern; this is particularly useful in a + literalSELECT/ target list when you want all
Re: [BUGS] BUG #5478: ILIKE operator returns wrong result
Tom Lane wrote:
Markus markus.her...@outpost24.com writes:
select 'ba' ilike '%__%';
return true as expected in 8.2 but false in 8.4.
I have a feeling that this represents still another bug in the
special-case path for % followed by _ (cf bug #4821). If so,
maybe we ought to just toss out that optimization?
Yea, looks like it is this code in like_match.c:
/* %_ is the same as _% - avoid matching _ repeatedly */
do
{
NextChar(t, tlen);
NextByte(p, plen);
} while (tlen 0 plen 0 *p == '_');
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5478: ILIKE operator returns wrong result
Tom Lane wrote: BTW, while I'm looking at this, I notice that there was an oversight in the change that made us throw an error for \ at the end of the LIKE pattern. We throw error in the first code chunk that deals with \ but we don't do so here: if (plen 2) return LIKE_FALSE; firstpat = CHAR(p[1]); In some cases the problem is masked because we'll eventually apply the normal \ processing, but I think there are other cases where we'll reach a LIKE_ABORT condition and return false without ever throwing the error. Seems like this should be fixed. But should we back-patch that fix into 8.4? We didn't backpatch the original change for fear of breaking existing apps, and the same argument could probably be made this time. Should I change it in 8.4, or only 9.0? Tom has patch this and the fix will appear in the next minor release of Postgres 8.3.X and 8.4.X. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] psql: SELECT INTO with FETCH_COUNT enabled
I have added the following TODO: Fix FETCH_COUNT to handle SELECT ... INTO and WITH queries * http://archives.postgresql.org/pgsql-hackers/2010-05/msg01565.php * http://archives.postgresql.org/pgsql-bugs/2010-05/msg00192.php --- Josh Williams wrote: While tinkering with some psql settings on 9.0beta1... [local]:5432|postgres=# \set FETCH_COUNT 1 [local]:5432|postgres=# SELECT foo INTO bar FROM baz; ERROR: DECLARE CURSOR cannot specify INTO LINE 2: SELECT foo INTO bar FROM baz; ^ [local]:5432|postgres=#! If I'm reading it right its using src/bin/psql/common.c's is_select_command() to determine if the query is cursor-able, and that function is just looking to see that the query starts with 'select' (or 'values'.) I'm not sure catching a non-alias use of INTO will be all that easy here without adding undue complexity. So considering no one else has reported it at least than I've been able to find, +1 for leaving it as is. Just thought I'd post it in case anyone has any better ideas for tackling it. - Josh -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] psql or pgbouncer bug?
Added to TODO:
Prevent psql from sending remaining single-line multi-statement queries
after reconnection
* http://archives.postgresql.org/pgsql-bugs/2010-05/msg00159.php
* http://archives.postgresql.org/pgsql-hackers/2010-05/msg01283.php
---
Tom Molesworth wrote:
Hi Jakub,
On 24/05/10 08:52, Jakub Ouhrabka wrote:
The auto-reconnect behavior is long-established and desirable. What's
not desirable is continuing with any statements remaining on the same
line, I think. We need to flush the input buffer on reconnect.
So if I understand it correctly, if I need correct transaction
behaviour in psql even in case of disconnection the only safe way is
to use one statement per line.
You'd have to pay close attention to the responses if you go for that
option, personally I wouldn't recommend it - much safer to use \set
autocommit false, and that way you'll only ever get transactions
committed when you explicitly issue a commit.
Since the connection could drop at any point during a psql session, the
following sequence would also end up with some unwanted steps committed
automatically:
begin;
update table set col = X;
-- connection drops after above two statements complete - not important
whether they're on separate lines --
update table set col = Y; -- this statement will use current autocommit
behaviour
rollback; -- no transaction in progress message if autocommit was enabled
If you happen to miss the reconnection message during the above
sequence, you'll inadvertently be back in autocommit mode - so the 3rd
statement will be committed immediately.
Compare this to:
\set autocommit false
update table set col = X;
update table set col = Y;
rollback;
If the connection drops at any point before or after those statements,
the new connection will still be in transactional (manual commit) mode,
so there's no chance of any of the above statements being committed
(either the rollback on disconnect, or the explicit rollback will take
place).
Personally I always use '\set autocommit false' under psql, since it's
closer in behaviour to the Perl DBI -connect(... { AutoCommit = 0 })
behaviour I'm used to. I'd definitely never risk using 'begin' in psql
with multiple statements.
Tom
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5469: regexp_matches() has poor behaviour and more poor documentation
Daniele Varrazzo wrote: If there is no match to the pattern, the function returns no rows is easily overlooked as it returns null, or some other behaviour that don't change the returned set. The point is, because the function is listed in the string function, you would expect the function to manipulate text, not the dataset. The function as it is is not safe to be used in a construct SELECT foo, bar, regexp_matches(bar, pattern) FROM table; unless you really wanted: SELECT foo, bar, regexp_matches(bar, pattern) FROM table WHERE bar ~ pattern; otherwise you have to take measures to be able to deal with records in which the pattern is not matched, for example: SELECT foo, bar, regexp_matches(bar, pattern || '|') FROM table; the latter still doesn't work when bar is NULL: in this case the record is dropped anyway, so I don't think it can be proposed as general solution. The characteristics of returning a set of text[] is useful when the user wants all the matches, not only the first one: the behaviour is selected specifying the flag 'g' as third argument. From this point of view, I hope it can be stated that in its current form the regexp_matches() has not the most optimal interface. Please accept my apology for the tone being too rude in my previous message. I found the description in the documentation quite confusing also. I have created the attached documention patch which is clearer about the behavior of regexp_matches(). -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com Index: doc/src/sgml/func.sgml === RCS file: /cvsroot/pgsql/doc/src/sgml/func.sgml,v retrieving revision 1.513 diff -c -c -r1.513 func.sgml *** doc/src/sgml/func.sgml 7 Apr 2010 06:12:52 - 1.513 --- doc/src/sgml/func.sgml 29 May 2010 01:59:32 - *** *** 3450,3463 It has the syntax functionregexp_matches/function(replaceablestring/, replaceablepattern/ optional, replaceableflags/ /optional). ! If there is no match to the replaceablepattern/, the function returns ! no rows. If there is a match, the function returns a text array whose replaceablen/'th element is the substring matching the replaceablen/'th parenthesized subexpression of the pattern (not counting quotenon-capturing/ parentheses; see below for ! details). If the pattern does not contain any parenthesized ! subexpressions, then the result is a single-element text array containing ! the substring matching the whole pattern. The replaceableflags/ parameter is an optional text string containing zero or more single-letter flags that change the function's behavior. Flag literalg/ causes the function to find --- 3450,3466 It has the syntax functionregexp_matches/function(replaceablestring/, replaceablepattern/ optional, replaceableflags/ /optional). ! The function can return no rows, one row, or multiple rows (see ! the literalg/ flag below). If the replaceablepattern/ ! does not match, the function returns no rows. If the pattern ! contains no parenthesized subexpressions, then each row ! returned is a single-element text array containing the substring ! matching the whole pattern. If the pattern contains parenthesized ! subexpressions, the function returns a text array whose replaceablen/'th element is the substring matching the replaceablen/'th parenthesized subexpression of the pattern (not counting quotenon-capturing/ parentheses; see below for ! details). The replaceableflags/ parameter is an optional text string containing zero or more single-letter flags that change the function's behavior. Flag literalg/ causes the function to find -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5457: dblink_connect now restricts non-superusers to password
Chana Slutzkin wrote: The following bug has been logged online: Bug reference: 5457 Logged by: Chana Slutzkin Email address: ch...@cs.huji.ac.il PostgreSQL version: 8.4 Operating system: FreeBSD 7.2 Description:dblink_connect now restricts non-superusers to password Details: dblink in version 8.4 forces a non-superuser to connect using a password. I would prefer to use ident. I don't think ident is a good idea because the connection is coming from the database server, not the client. The database server is always going to be user 'postgres'. However, I assume pg_hba.conf could allow you do make this work somehow, but with little security. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5430: initdb fails due to permissions of /usr/share/pgsql
Would you report this to the RPM maintainers?
---
Jamie Strachan wrote:
The following bug has been logged online:
Bug reference: 5430
Logged by: Jamie Strachan
Email address: frostfr...@yahoo.com
PostgreSQL version: 8.4.3-2PGDG.el4
Operating system: CentOS 4
Description:initdb fails due to permissions of /usr/share/pgsql
Details:
Hello,
Downloaded the following RPM:
http://yum.pgsqlrpms.org/8.4/redhat/rhel-4-i386/postgresql-server-8.4.3-2PGD
G.el4.i386.rpm
for my CentOS 4 install.
service postgresql initdb fails with no error message.
So, I su'd to postgres, and ran
initdb --pgdata=/var/lib/pgsql/data --auth=ident
which is what service postgresql initdb does.
This command failed, with the error message:
could not open directory /usr/share/pgsql/timezonesets
I did a
chown -R postgres:postgres /usr/share/pgsql
and then it worked fine.
Using rpm2cpio {file} | cpio -tv
I see:
drwxr-xr-x 6 root root0 Mar 17 16:21 ./usr/share/pgsql
which is fine, however there is no specific entry for
/usr/share/timezone.
The post-install script doesn't seem to fix it, either.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Reset ACL to default for pg 8.0
Juan C. Aragon wrote:
Thanks Bruce for replying.
The ACL is display in the PgAdmin tool for Windows on the properties for a
table or function. ACL is the Privileges. By default, when you create a new
function or table, the ACL is null. However, if you add some privileges, and
then remove all, the ACL is not longer null, it displays the brackets {}.
I can reproduce your reported behavior in psql, which I admit is odd:
test= create table test(x int);
CREATE TABLE
test= \pset null '(null)'
Null display is (null).
test= grant all on test to postgres;
GRANT
test= revoke all on test from postgres;
REVOKE
test= select relacl from pg_class where relname = 'test';
relacl
{}
(1 row)
Community, do we want to make a permission reset cause the column to
become null?
I just need to know if the brackets are normal when all the privileges are
remove. Or how to reset the privileges (ACL) to default (null).
They are the same.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Reset ACL to default for pg 8.0
Juan C. Aragon wrote:
Hi Guys:
I need to know how to reset the ACL to default (null) for a function. This
is for PostgreSQL 8.0.14 and 8.4.1 on Windows.
I used the User Interface to removed all the Privileges, but the ACL is
still = {}
I need to make the ACL to be blank (null), nothing in there.
Because if the ACL = {} and I do a db restore, it sets the ACL = owner,
but I need it to be blank.
Well, if I create a table and look at pg_dump, I see:
ALTER TABLE public.test OWNER TO postgres;
and the owner has permissions by default on the table. Perhaps you want
to revoke owner permissions on the table.
Where are you seeing this ACL?
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5420: pg_attribute broken
That is a serious problem. Based on your ASAP requirement, I think you need a commercial support contract or consultant to fix this for you. --- cool wrote: The following bug has been logged online: Bug reference: 5420 Logged by: cool Email address: coolshower2...@yahoo.co.jp PostgreSQL version: 8.1.8 Operating system: debian 2.6.18-6-686 Description:pg_attribute broken Details: We use PostgreSQL Databse on Hospital Information System in Japan. In case of normal condition(Not busy, Not Backup, Not Start up and Not Shutdown), Suddenly, PostgreSQL outputs error message and pg_attribute catalog file is broken as bellow. Could you sent it to us A.S.A.P. Environment debian debian 2.6.18-6-686 / PostgreSQL 8.1.8 Error message invalid attribute number 0 for tbl_syuday pg_attribute attrelid addname atttypidattstattarget attlen attnum 45762tableoid 26 0 4 -7 45762cmax 29 0 4 -6 45762xmax 28 0 4 -5 45762cmin 29 0 4 -4 45762xmin 28 0 4 -3 45762ctid 27 0 6 -1 45762santeiflg18_4 0 0 0 0 (?) 45762nyugaikbn 1042 -1 -1 1 45762ptid1700 -1 -1 2 . .(Omission) . 45762santeiflg17_1 1700 -1 -1 343 45762ftmoney17_1 1700 -1 -1 344 45762ftmoney18_4 1700 -1 -1 380 (345..379 are deleted?) 45762comptfn18_4 1700 -1 -1 381 . .(Omission) . Q1:Does anything have same reports ? Q2:Does anything have repair patches ? -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5370: Shortcut for currently connected users
Igor Moiseev wrote: The following bug has been logged online: Bug reference: 5370 Logged by: Igor Moiseev Email address: moiseev.i...@gmail.com PostgreSQL version: 8.3.9 Operating system: Ubuntu Description:Shortcut for currently connected users Details: Dear developers, thank you for the perfect product! I'd like to request one shortcut in psql terminal. That would simplify a lot manual administrating of postgresql servers! The query I need always to execute is select * from pg_stat_activity ; That would be nice to have something like \cu as a shortcut for this query!! Add this to your ~/.psqlrc: \set mon 'SELECT * FROM pg_stat_activity'; and then you can use this in psql: test= :mon datid | datname | procpid | usesysid | usename | application_name | ... -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5348: Postgres crashes with index on xpath_string
Robert Haas wrote: On Fri, Mar 5, 2010 at 3:52 AM, Thomas Kellerer bestell...@kellerer.name wrote: Thanks for the feedback. Is the problem specific to 8.4.2 or to the Windows platform (or both)? Neither, actually. The fix will be in the next minor Postgres release. Keep an eye out for it when it is announced. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Cache lookup failure for index during pg_dump
Euler Taveira de Oliveira wrote: Tom Lane escreveu: The window for this sort of thing isn't very large, because the first thing pg_dump does is acquire AccessShareLock on every table it intends to dump, and past that point it won't be possible for anyone to modify the table's DDL. But it can happen. I did not see it documented anywhere. Should we at least add a comment at the top of pg_dump documenting this behavior? Attached is a proposed patch using your own words. Applied, thanks. I also added the URL of the discussion. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] possible bug not in open items
Tom Lane wrote: Jeff Davis pg...@j-davis.com writes: On Thu, 2010-02-25 at 23:15 -0500, Bruce Momjian wrote: Was this ever addressed? It doesn't appear to be fixed, and I don't see it on the TODO, either. Should we add it there? +1. It likely wouldn't be real hard to fix, but given the lack of field complaints I'm not thinking we need to treat it as urgent. Added to TODO: Allow a stalled COPY to exit if the backend is terminated * http://archives.postgresql.org/pgsql-bugs/2009-04/msg00067.php -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5354: Type timestamptz doesn't allow to store time zone
Vitali Malinouski wrote: Thank you for the response. I see your point. When I insert into field timezonetz it does convert into correct timezone and inserts the converted value. At the same time timestamp field just drops the timezone and inserts the value. However, I think that confusion comes when timetz works differently. Are there any plans to make them work the same either one way or another. I think we are following the specification on that one so I don't see how we can change it. --- - Original Message - From: Kevin Grittner kevin.gritt...@wicourts.gov To: robertmh...@gmail.com, vit...@lumensoftware.com Cc: pgsql-bugs@postgresql.org Sent: Tuesday, March 2, 2010 7:35:32 AM Subject: Re: [BUGS] BUG #5354: Type timestamptz doesn't allow to store time zone Robert Haas wrote: Vitali wrote: When I select from the table, the timetz has the correct time zone, the timestamptz has -6 as a time zone, which is my server default. I'm not sure what you think the bug is, but timestamptz definitely doesn't store the time zone in which the value is input. What it does is makes input and output relative to the then-current time zones rather than fixed wall-clock times. We should probably add this to the FAQ -- the OP was expecting the behavior specified by the standard, in which TIMESTAMP WITH TIME ZONE includes a time zone. -Kevin -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5348: Postgres crashes with index on xpath_string
Yes, we have received a few reports about this and are working on a fix.
---
Thomas Kellerer wrote:
The following bug has been logged online:
Bug reference: 5348
Logged by: Thomas Kellerer
Email address: tho...@kellerer.name
PostgreSQL version: 8.4.2
Operating system: Windows XP
Description:Postgres crashes with index on xpath_string
Details:
With the contrib module xml2 (pgxml.sql) installed, run the following
script:
create table t1 (id integer, xml_data xml);
insert into t1 (id, xml_data)
values
(1, 'attributesattribute name=attr_1Some
Value/attribute/attributes');
create index idx_xpath on t1 ( xpath_string
('/attributes/attribu...@name=attr_1]/text()', xml_data::text));
This will crash the backend.
The entry in the logfile is:
2010-02-27 17:21:28 CET STATEMENT: create index idx_xpath on t1 (
xpath_value ('/attributes/attribu...@name=attr_1]/text()',
xml_data::text))
2010-02-27 17:21:55 CET LOG: server process (PID 2544) was terminated by
exception 0xC005
2010-02-27 17:21:55 CET HINT: See C include file ntstatus.h for a
description of the hexadecimal value.
2010-02-27 17:21:55 CET LOG: terminating any other active server processes
2010-02-27 17:21:55 CET LOG: all server processes terminated;
reinitializing
When restarting the server, the following messages are written to the
logfile:
2010-02-27 17:22:05 CET FATAL: pre-existing shared memory block is still in
use
2010-02-27 17:22:05 CET HINT: Check if there are any old server processes
still running, and terminate them.
2010-02-27 17:22:22 CET LOG: database system was interrupted; last known up
at 2010-02-27 16:22:32 CET
2010-02-27 17:22:22 CET LOG: database system was not properly shut down;
automatic recovery in progress
2010-02-27 17:22:22 CET LOG: redo starts at 3/B77FAB28
2010-02-27 17:22:22 CET LOG: unexpected pageaddr 3/9585 in log file 3,
segment 183, offset 8716288
2010-02-27 17:22:22 CET LOG: redo done at 3/B784F758
2010-02-27 17:22:22 CET LOG: last completed transaction was at log time
2010-02-27 17:21:20.382+01
2010-02-27 17:22:23 CET FATAL: the database system is starting up
2010-02-27 17:22:23 CET LOG: database system is ready to accept
connections
2010-02-27 17:22:24 CET LOG: autovacuum launcher started
When the following index is created, things seem to work (at least the
backend is not crashing)
create index idx_xpath on t1 ( xpath_string
('/attributes/attribu...@name=attr_1]', xml_data::text))
(note the missing text() in the xpath)
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do
+ If your life is a hard drive, Christ can be your backup. +
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] to_timestamp error handling.
Tom Lane wrote:
Bruce Momjian br...@momjian.us writes:
Tom Lane wrote:
Anybody know what Oracle's to_timestamp does?
The old thread reported Oracle returned an error;
http://archives.postgresql.org/pgsql-bugs/2009-06/msg00100.php
Well, nothing's likely to get done about it for 9.0. Maybe we should
add a TODO item for further tightening of the function's error checking.
There doesn't seem to be any error checking:
test= select to_timestamp('20090140','MMDD');
to_timestamp
2009-02-09 00:00:00-05
(1 row)
The odd thing is we seems to do something reasonable for some definition
of reasonable so maybe we just leave it unchanged.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do
+ If your life is a hard drive, Christ can be your backup. +
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4806: Bug with GiST index and empty integer array?
I can reproduce this but in current CVS by installing /contrib/intarray.
---
Joerg Kiegeland wrote:
The following bug has been logged online:
Bug reference: 4806
Logged by: Joerg Kiegeland
Email address: kiegel...@ikv.de
PostgreSQL version: PostgreSQL8.3.7
Operating system: Windows XP
Description:Bug with GiST index and empty integer array?
Details:
The GiST index seems not to be able to find empty integer arrays.
The bug can be easily reproduced on a simple test database:
To create the table and the data execute:
CREATE TABLE test_intarray_table (
id text NOT NULL PRIMARY KEY,
intarray_column integer[]
);
INSERT INTO test_intarray_table (id, intarray_column) VALUES ('x', '{}');
CREATE INDEX intarray_index ON test_intarray_table USING gist
(intarray_column);
To query the data execute:
SET ENABLE_SEQSCAN TO OFF; --disable sequential scan, which is performed for
small tables
SELECT * FROM test_intarray_table WHERE intarray_column = '{}';
The result set of this query does not include the row with id x, though the
condition should match! When deleting the index intarray_index, x is found!
So we proposed our customer to simply delete the index. However it goes
slower then. I would expect the GiST index to either report an error that
empty array queries are not supported (like GIN index does) or otherwise to
return the correct result, since the result of a query should be independent
of an index usage.
As we reduced this bug to this little example from a very large database and
a much larger query (took hours for this simplification), we disabled the
sequential scan, however in our large database we could reproduce this error
without disabling the sequential scan.
The bug also appears with PostgreSQL8.4 Beta and with PostgreSQL8.3.6.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do
+ If your life is a hard drive, Christ can be your backup. +
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4769: xmlconcat produces invalid xml values - data corruption
Where are we on this? The 9.0 behavior is the same.
---
Arjen Nienhuis wrote:
The following bug has been logged online:
Bug reference: 4769
Logged by: Arjen Nienhuis
Email address: a.g.nienh...@gmail.com
PostgreSQL version: 8.3.7
Operating system: Ubuntu 8.10 and 9.04
Description:xmlconcat produces invalid xml values - data corruption
Details:
'!DOCTYPE htmlhtml/' is a valid xml document but not a valid xml
fragment. xmlconcat does not check for this:
SELECT xmlconcat('foo', xmlparse(DOCUMENT '!DOCTYPE htmlhtml/'));
xmlconcat
---
foo!DOCTYPE htmlhtml/
The result is an invalid xml value that can end up in a table.
==
= SELECT version();
version
-
PostgreSQL 8.3.7 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.3.real
(Ubuntu 4.3.3-5ubuntu4) 4.3.3
(1 row)
libxml2 version is 2.6.32.dfsg-5ubuntu4
=
I tried to test this with 8.4 but the ubuntu ppa has depency problems.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do
+ If your life is a hard drive, Christ can be your backup. +
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] possible bug not in open items
Was this ever addressed? --- Jeff Davis wrote: On Thu, 2009-03-26 at 21:45 -0400, Bruce Momjian wrote: http://archives.postgresql.org/pgsql-bugs/2009-03/msg00062.php It may or may not be a real bug, but I didn't receive any response. If you think it might be a bug, can you please add it to the open items? Hmm, odd I don't have it either; can you repost it? The docs say: SIGINT -- The server disallows new connections and sends all existing server processes SIGTERM, which will cause them to abort their current transactions and exit promptly. http://www.postgresql.org/docs/8.3/static/server-shutdown.html If you have an open COPY and no data is moving, it simply won't terminate it. You can terminate it with ctrl-C from psql, but not a SIGINT to the postmaster or a SIGINT or SIGTERM to the backend. Regards, Jeff Davis -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4921: ltree @ ltree[] operator shouldn't fail if ltree[] is empty
Was this ever addressed? --- Tom Lane wrote: Alan Pinstein apinst...@mac.com writes: ... hierarchy @ ARRAY(select hierarchy from feature where description ilike '%pool%this%') ... EXPECTED BEHAVIOR: - return 0 rows ACTUAL BEHAVIOR: ERROR: array must be one-dimensional Possibly from: https://projects.commandprompt.com/public/replicator/browser/trunk/contrib/ltree/_ltree_op.c?rev=1905 line 46 NOTES: This query worked in 8.1.x and started failing in 8.3.6 (only 2 versions I tested). Hmm. ltree has always had that ARR_NDIM == 1 check. I think the reason the behavior changed is that ARRAY(SELECT ...) used to return a NULL for zero rows, and now it returns an empty (zero-dimensional) array. I can see two reasonable ways to address this: * Change the ltree test to reject only ARR_NDIM 1. * Drop the ARR_NDIM check altogether, and let it search any sort of array. I'm leaning to #2 myself. However, there are probably other places with the same kind of issue, and in some of them it might make more sense to reject multidimensional arrays. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #4921: ltree @ ltree[] operator shouldn't fail if ltree[] is empty
Tom Lane wrote: Bruce Momjian br...@momjian.us writes: Was this ever addressed? No, it doesn't look like the code's been changed. I was looking for some comments about which to do: I can see two reasonable ways to address this: * Change the ltree test to reject only ARR_NDIM 1. * Drop the ARR_NDIM check altogether, and let it search any sort of array. I'm leaning to #2 myself. However, there are probably other places with the same kind of issue, and in some of them it might make more sense to reject multidimensional arrays. Thoughts? Do something. ;-) LOL -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] to_timestamp error handling.
FYI, this behavior now returns:
test= select to_timestamp('20096010','MMDD');
to_timestamp
2013-12-18 00:00:00-05
(1 row)
which doesn't have the :30 but is still odd. I don't see any value
checking in to_timestamp.
---
Dhaval Jaiswal wrote:
Hi All,
postgres=# select to_timestamp('20096010','MMDD');
to_timestamp
---
2013-12-18 00:00:00+05:30
(1 row)
The month is 60 in my case and it is giving some random value, whereas I am
expecting some error message like date is not valid.
Is it an expected behaviour?
--
Thanks Regards,
Dhaval Jaiswal
EnterpriseDB
Contact: 732-331-1300 Ext- 2022
+91-20-30589 516 / 494
web: www.enterprisedb.com
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do
+ If your life is a hard drive, Christ can be your backup. +
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] to_timestamp error handling.
Tom Lane wrote:
Bruce Momjian br...@momjian.us writes:
FYI, this behavior now returns:
test= select to_timestamp('20096010','MMDD');
to_timestamp
2013-12-18 00:00:00-05
(1 row)
which doesn't have the :30 but is still odd.
I don't think the behavior has changed, you're merely checking it in
a different timezone from the OP.
The real question is whether we should throw error for out-of-range
MM (or other fields). I think there are actual use cases for certain
invalid inputs, like adding one to the day field without worrying
about end of month. Perhaps there is not a use case for a month value
as far out of range as this, but where would we draw the line?
Anybody know what Oracle's to_timestamp does?
The old thread reported Oracle returned an error;
http://archives.postgresql.org/pgsql-bugs/2009-06/msg00100.php
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do
+ If your life is a hard drive, Christ can be your backup. +
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5118: start-status-insert-fatal
Was this ever addressed? --- Tom Lane wrote: Kevin Grittner kevin.gritt...@wicourts.gov writes: Tom Lane t...@sss.pgh.pa.us wrote: I'm not sure whether we'd want to provide a function within libpq for this, or just code it in pg_ctl. I'm inclined to think there would be value to a pg_ping utility to support automated monitoring by unprivileged users on other boxes. True. I had first thought that pg_ctl itself could serve that purpose, but it's really designed around the assumption that it has direct access to $PGDATA, so it wouldn't fit well for monitoring from another machine. That both suggests libpq as the location, and one or two additional pieces of information. An indication of in archive recovery versus production or shutdown, for example, might be useful. I'm not sure what else might make sense. IIRC, that's already covered by the CanAcceptConnections state. We need to be pretty conservative about how much information we expose here, anyhow, since it will be handed out to absolutely anybody who can reach the postmaster port. Within libpq the natural thing would be to take a conninfo connection string, but I'm not sure that suits pg_ctl's purposes. I'm a little lost on that. Would it cause any problems for pg_ctl, or just be more than it would need if it's only implemented there? Well, given what we were saying about a postmaster.ports file, pg_ctl would typically be working with an absolute path to the socket file. Which is not what normally goes into a conninfo string. Perhaps that could be addressed by specifying the file contents differently, but I'd be wary of assuming that *all* users of the ports file will be libpq-based --- for instance a Java version of pg_ctl wouldn't be. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5118: start-status-insert-fatal
Kevin Grittner wrote: Bruce Momjian br...@momjian.us wrote: Was this ever addressed? It should probably be on the TODO list. I was going to try to do this along with other items which came out of generating an LSB conforming init script, but have been pulled in different directions for now. When I get the time I've been intending to get back to this, if nobody beats me to it. Do we want one entry with all the miscellaneous pg_ctl issues I've got, or would it be better to keep the separate? I think you should just edit the TODO wiki and list all the things we agree need fixing: http://wiki.postgresql.org/wiki/Todo -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5103: pg_ctl -w (re)start fails with custom unix_socket_directory
Michael Renner wrote: Alvaro Herrera wrote: Michael Renner wrote: Mentioning PGHOST in pg_ctl's manpage/documentation should make this entirely clear for people who aren't familiar with the extensive environment variables PostgreSQLs client library can use. [1] Yeah, this has been complained about many, many times. Usually an indicator that it need's fixing, eh? ;) If this is a viable option I can write a small patch against the documentation. I think the patch we need here is something that creates a separate manpage for the libpq environment variables, and some kind of note in each and every libpq-using program referring the user to that page. Looking through the available variables probably only PGHOST seems to be of interest for pg_ctl's purposes; psql's manpage already refers to The Documentation for further variables and information. What's needed in pg_ctl's case is just a pointer for the uninformed, at least for me that'd have sufficed. Based on your suggestion, I have documented the use of PGHOST by pg_ctl with the attached patch. I specifically mentioned the socket location. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do + If your life is a hard drive, Christ can be your backup. + Index: doc/src/sgml/ref/pg_ctl-ref.sgml === RCS file: /cvsroot/pgsql/doc/src/sgml/ref/pg_ctl-ref.sgml,v retrieving revision 1.47 diff -c -c -r1.47 pg_ctl-ref.sgml *** doc/src/sgml/ref/pg_ctl-ref.sgml 10 Dec 2009 06:32:28 - 1.47 --- doc/src/sgml/ref/pg_ctl-ref.sgml 22 Feb 2010 22:28:14 - *** *** 393,406 /varlistentry varlistentry termenvarPGPORT/envar/term listitem para ! Default port for xref linkend=app-psql (used by the -w option). /para /listitem /varlistentry /variablelist para --- 393,418 /varlistentry varlistentry + termenvarPGHOST/envar/term + + listitem + para + Default hostname or Unix-domain socket location for xref + linkend=app-psql (used by the -w option). + /para + /listitem +/varlistentry + +varlistentry termenvarPGPORT/envar/term listitem para ! Default port number for xref linkend=app-psql (used by the -w option). /para /listitem /varlistentry + /variablelist para Index: doc/src/sgml/ref/postgres-ref.sgml === RCS file: /cvsroot/pgsql/doc/src/sgml/ref/postgres-ref.sgml,v retrieving revision 1.51 diff -c -c -r1.51 postgres-ref.sgml *** doc/src/sgml/ref/postgres-ref.sgml 9 Jul 2007 01:08:09 - 1.51 --- doc/src/sgml/ref/postgres-ref.sgml 22 Feb 2010 22:28:14 - *** *** 579,585 listitem para ! Default port (preferably set in the configuration file) /para /listitem /varlistentry --- 579,585 listitem para ! Default port number (preferably set in the configuration file) /para /listitem /varlistentry -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] to_char issue?
Dave Page wrote: This was posted as a documentation comment: to_char(interval '0d 0h 12m 44s', 'DD HH MI SS'); with HH and HH12 will return 12 instead of 0. Testing on 8.4.1, it does seem to be the case that you get 00 12 12 44. Seems bogus to me, but am I and the OP missing something? Fixed with the attached patch. I think HH and HH24 should be the same for intervals. It is hard to explain why zero hours should show as '12' for intervals. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do + If your life is a hard drive, Christ can be your backup. + Index: src/backend/utils/adt/formatting.c === RCS file: /cvsroot/pgsql/src/backend/utils/adt/formatting.c,v retrieving revision 1.163 diff -c -c -r1.163 formatting.c *** src/backend/utils/adt/formatting.c 16 Feb 2010 21:18:01 - 1.163 --- src/backend/utils/adt/formatting.c 23 Feb 2010 01:39:21 - *** *** 2089,2096 case DCH_HH: case DCH_HH12: sprintf(s, %0*d, S_FM(n-suffix) ? 0 : 2, ! tm-tm_hour % (HOURS_PER_DAY / 2) == 0 ? 12 : ! tm-tm_hour % (HOURS_PER_DAY / 2)); if (S_THth(n-suffix)) str_numth(s, s, S_TH_TYPE(n-suffix)); s += strlen(s); --- 2089,2096 case DCH_HH: case DCH_HH12: sprintf(s, %0*d, S_FM(n-suffix) ? 0 : 2, ! !is_interval tm-tm_hour % (HOURS_PER_DAY / 2) == 0 ? ! 12 : tm-tm_hour % (HOURS_PER_DAY / 2)); if (S_THth(n-suffix)) str_numth(s, s, S_TH_TYPE(n-suffix)); s += strlen(s); -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] to_char issue?
Bruce Momjian wrote: Dave Page wrote: This was posted as a documentation comment: to_char(interval '0d 0h 12m 44s', 'DD HH MI SS'); with HH and HH12 will return 12 instead of 0. Testing on 8.4.1, it does seem to be the case that you get 00 12 12 44. Seems bogus to me, but am I and the OP missing something? Fixed with the attached patch. I think HH and HH24 should be the same for intervals. It is hard to explain why zero hours should show as '12' for intervals. Oh, I should also mention that with the old code, 24 and 36 interval hours would also return '12'. :-( -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
