Re: [BUGS] Unexpected omission of tables with duplicate names across schemas
On Wed, Sep 29, 2010 at 2:19 PM, Chris Ross wrote: > On 09/29/2010 02:08 PM, Chris Ross wrote: >> >> On 09/28/2010 01:17 PM, Tom Lane wrote: >>> >>> That's the intended behavior, because only the first one is actually >>> accessible without schema-qualifying its name. You can use a pattern >>> of "*.*" if you want to see objects that are hidden according to the >>> search path. The default behavior is equivalent to a pattern of "*", >>> which only shows objects reachable with unqualified names. >> >> Is there a way to ask the database "What are all of the tables/views/etc >> in my current search path?" without having it infer "that I can reach >> without schema-qualifing them" ? >> >> That's what I've always used \d for, and while it's certainly a habit >> rather than anything documented explicitly to do what I think it should >> do, there needs to be *a* way to do this I think... > > After thinking about this a little more, I think the problem here is more > subtle/complex. The problem, in some ways, is that I am (in some ways) > misusing search_path to hide parts of the database from my view. > > We have a database with dozens of schemas, some of which have many dozens > of tables et al. So, I typically use search_path to modify which section of > the database I'm looking at, and I typically schema-qualify everything when > I code (and often when I'm just typing in psql). So it's not really that I > want to know what I can reach without schema qualifications, it's that I > want a list of all things that exist, but only within the schemas that are > in my search_path. > > Of course, that's not what search_path is *for*, so it's a confusing issue. > I'm using it for something slightly along-side what it is really designed > for. But, except for this minor issue, it works well for that. > > Does this help make more clear what problem I'm trying to solve, and the > problem as I see it? I think what you're trying to do is totally reasonable, but psql doesn't really support it. You might be able to rig something up using macros (see \set). There's a pretty significant difference between the way that graphical admin tools like pgAdmin view schemas and the way they look from psql. In pgAdmin, you get a tree (which is how you seem to be thinking about it), whereas in psql it tends to feel more like a flat namespace that's constructed by smashing several namespaces together, a la UNIX $PATH. The underlying reality is that it's some of both. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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
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. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company
Re: [BUGS] BUG #5666: Pgadmin restore or back up database dont apper
On Mon, Sep 20, 2010 at 2:28 PM, Oscar Miguel Amezcua Estrella wrote: > > The following bug has been logged online: > > Bug reference: 5666 > Logged by: Oscar Miguel Amezcua Estrella > Email address: obel...@gmail.com > PostgreSQL version: 9.0.1 > Operating system: Opensuse 11.3 > Description: Pgadmin restore or back up database dont apper > Details: > > Hiyas im recent install PostgreSQL 9.0 in my pc im using a Gnu/Linux > Opensuse 11.3 and we try to restore my database in pgadmin buth it dont show > the option in database to restore :( we restore it in console :( this > problem onli in Gnu/Linux in windows it apper perfectly You might want to report this problem on the pgadmin list. http://archives.postgresql.org/pgadmin-support/ It might help to provide some more details. http://wiki.postgresql.org/wiki/Guide_to_reporting_problems http://www.postgresql.org/docs/current/static/bug-reporting.html -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] installer problems
On Thu, Sep 23, 2010 at 11:45 AM, Dave Page wrote: > On Thu, Sep 23, 2010 at 3:19 PM, Samuel Rettore wrote: >> Hi, >> >> I'm having problems installing the database, see: >> >> anfitrite:/opt# ./postgresql-9.0.0-1-linux.bin >> >> Error: Error running /tmp/postgresql_installer/getlocales : child killed: >> floating-point exception >> Press [Enter] to continue : >> anfitrite:/opt# >> >> >> anfitrite:/opt# uname -a >> Linux anfitrite 2.6.18-6-686 #1 SMP Fri Feb 19 23:40:03 UTC 2010 i686 >> GNU/Linux >> >> anfitrite:/opt# cat /etc/debian_version >> 4.0 > > Debian is not a supported platform for the installers - we don't see > enough demand for it to justify the testing requirements I'm afraid. So I think what that means for the OP is that he should install PostgreSQL by some other method - for example, apt-get, or compiling from source. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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 #5675: out of memory at request size 32Mb
On Fri, Sep 24, 2010 at 8:42 AM, Andreas Pflug wrote: > > The following bug has been logged online: > > Bug reference: 5675 > Logged by: Andreas Pflug > Email address: pgad...@pse-consulting.de > PostgreSQL version: 8.4.4 > Operating system: Linux 2.6.26 i686 > Description: out of memory at request size 32Mb > Details: > > A slony 2.0 cluster running for a year, no config changes lately. Slony has > to replicate a table with a bytea column; maximum so far was a little over > 32MB. > > Lately, the slave's slon process fails to replicate. The server reports "out > of memory", "Failed on request of size 33554432.". The slon log shows an > insert was the failing statement, the server log spits out its memory > contexts but not the failing statement. > > One log line that catched my attention was > MessageContext: 485902696 total in 6 blocks; 32160 free (16 chunks); > 485870536 used > which is well beyond the configured memory limits (280MB shared_buffers, > temp 32MB, workmem 64MB) Are you sure you meant to report this here? Seems likely to be a Slony issue, although of course I'm not sure of that. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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 #5669: server process was terminated by exception 0xC0000005
On Wed, Sep 22, 2010 at 9:04 PM, Tom Lane wrote: > Robert Haas writes: >> Can SHOW return a NULL value, rather than the empty string? > > I think that would take some work in guc.c, and likely a redefinition > of the API for show-hook functions. I'm not excited about doing it, > particularly not in a bug fix that needs to be back-patched. OK. Yeah, that doesn't seem worth it. It was just a thought. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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 #5669: server process was terminated by exception 0xC0000005
On Wed, Sep 22, 2010 at 1:54 PM, Tom Lane wrote: > Alvaro Herrera writes: >> Excerpts from Tom Lane's message of mié sep 22 13:36:18 -0400 2010: >>> Well, the more general point is what should "SHOW session_authorization" >>> show in an autovacuum process? The fact that Andrew wasn't >>> intentionally doing that doesn't mean that someone else might not try >>> it. I think we can either decide it should be an obviously-illegal >>> value, or try to make it return the name of the cluster-creating >>> superuser. The latter seems like more work than it's worth though. > >> +1 for the illegal value. > > OK. I just dug through all the other show-hook functions and confirmed > that show_session_authorization is the only one that will dump core if > its variable is NULL, so it looks like we have just one case to fix. > Will get on it once I get my repo back together ... Can SHOW return a NULL value, rather than the empty string? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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 #5671: ERROR: out of memory
On Tue, Sep 21, 2010 at 4:54 PM, Jairo Carrillo wrote: > > The following bug has been logged online: > > Bug reference: 5671 > Logged by: Jairo Carrillo > Email address: carsof...@gmail.com > PostgreSQL version: 8.4 > Operating system: windows server 2003 r2 > Description: ERROR: out of memory > Details: > > INFO: vacuuming "public.localizacion" >>> ERROR: out of memory >>> DETAIL: Failed on request of size 1048575996. >>> >>> ERROR: out of memory >>> DETAIL: Failed on request of size 1048575996. I'm not sure if there's a bug here or not, but it sounds like you're out of memory. :-) What is the value of maintenance_work_mem? How large is the table? How much memory do you have on your machine? Exactly which 8.4 release are you running? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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 #5667: install failure
On Mon, Sep 20, 2010 at 4:49 PM, Roedy Green wrote: > I executed postgresql-9.0.0-1-windows_x64.exe > It died with a message: > > "An error occured executing VC++ runtime installer." > > I had similar problems with version 8. People gave me various things to > try. None of it worked. I gave up on PostgresSQL. I decided to try again > with v 9.0.0. > > I would really like it you stopped using this flaky installer. Hmm, if by that you mean 8.0, a lot of things have changed since then. I'm not even sure we're using the same installer technology now that we did then. But in any case it does work for an awful lot of other people. I installed 9.0rc1 on a Windows 7 VM just a few weeks ago, and it worked fine. Of course, that does nothing to solve your problem. But we do need to try to understand what is different about your system than the systems of people who are not having this problem, because otherwise it's pretty hard to fix. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] Rules not executed on child tables
On Wed, Aug 18, 2010 at 5:36 AM, wrote: > I noticed that when an action is executed on a parent table, rules for that > action that are defined on its child tables are ignored. I think this is actually design behavior. It's really hard to use rules for anything useful (other than views); you might want to look at triggers instead. Rules are basically a crude query-rewrite system. The rule rewriting process doesn't understand anything about what your query is actually trying to do; it's just analyzing the syntax (where, of course, the child tables aren't mentioned). -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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 #5644: Selecting ROW() in variable with 9.0 not compatible with 8.4
On Thu, Sep 16, 2010 at 9:30 PM, Tom Lane wrote: > Robert Haas writes: >> On Wed, Sep 8, 2010 at 11:42 AM, Tom Lane wrote: >>>> SELECT ROW(10, 'a') INTO b.b2; -- ok in 8.4 but fails in 9.0 [ERROR: >>>> invalid input syntax for integer: "(10,a)"] > >>>> SELECT 100, 'a' INTO b.b2; -- ok in 9.0 but fails in 8.4 [ERROR: >>>> cannot assign non-composite value to a row variable] > >> If we know the types of everything, is it possible to make both cases work? > > We don't know the types of everything at the point where the decision > needs to be made. Even if we did, allowing both would be a klugy > unmaintainable mess IMO --- far more work than it's worth. Bummer. Maybe we should have more-different syntax for the two cases then. I've been bitten by this quite a few times over the years. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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 #5652: Optimizer does wrong thing with partitioned tables
On Fri, Sep 10, 2010 at 9:31 AM, Euler Taveira de Oliveira wrote: > Mladen Gogala escreveu: >> Optimizer chooses to scan each partitioned table sequentially, instead of >> using the available index: >> > This is not a bug. How would the optimizer know that the maximum value is in > that specific partition? There is neither a global index for a partitioned > table nor an optimizer artifact to know aggregate information before scanning > all of the partitions. Maybe when we have a better support for table > partitioning such optimizer artifact would be implemented but don't hold your > breath. I wonder if Merge Append could be made to help with this case. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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 #5644: Selecting ROW() in variable with 9.0 not compatible with 8.4
On Wed, Sep 8, 2010 at 11:42 AM, Tom Lane wrote: > Valentine Gogichashvili writes: >> CREATE TYPE ta AS (a1 integer, a2 text); >> CREATE TYPE tb AS (b1 integer, b2 ta); > >> DECLARE >> a ta; >> b tb; >> BEGIN > >> SELECT 1, 'a' INTO a; -- ok > >> SELECT ROW(10, 'a') INTO b.b2; -- ok in 8.4 but fails in 9.0 [ERROR: >> invalid input syntax for integer: "(10,a)"] > >> SELECT 100, 'a' INTO b.b2; -- ok in 9.0 but fails in 8.4 [ERROR: cannot >> assign non-composite value to a row variable] > > [ pokes around for a bit ... ] This is a consequence of the plpgsql > lexer rewrite I did for 9.0. In the previous code, "INTO b.b2" was > treated by the lexer as an assignment to a scalar variable, regardless > of the actual data type of b2. Which means that the SELECT has to > produce a single column that gets assigned to b.b2, so your first case > works and your second doesn't. The new code looks at the data type > of b2 rather than whether it's syntactically a field reference, so it > decides this is an assignment to a composite variable. That results in > behavior similar to the "INTO a" case: the SELECT is supposed to produce > one column for each field of the composite variable. Hence, second case > works and first doesn't. > > I am not sure how ugly a kluge would be needed to restore the previous > behavior. I'm inclined to say that the new behavior is more > self-consistent and so we should call this a bug fix rather than a bug. If we know the types of everything, is it possible to make both cases work? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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
On Thu, Sep 9, 2010 at 12:22 PM, Mark Llewellyn wrote: > > The following bug has been logged online: > > Bug reference: 5650 > Logged by: Mark Llewellyn > Email address: mark_llewel...@adp.com > PostgreSQL version: 9.0 RC1 > Operating system: Windows XP > Description: Postgres service showing as stopped when in fact it is > running > Details: > > The postgresql-9.0 service is showing as *not running* in pgAdmin 3 1.12 RC1 > and as *stopped* in the Windows services list, although the database is > actually running and I can connect to it and create databases, tables etc. > It also shows multiple postgresql tasks in Windows task manager. > However, because Windows XP is not aware that the postgresql-9.0 service is > running it is not possible to stop the service. Maybe this is a dumb question, but how did you start the service? And applying the good old Windows troubleshooting meme... if you reboot, does that fix it? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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
On Sun, Sep 12, 2010 at 12:40 AM, Tom Lane wrote: > Robert Haas writes: >> On Tue, Aug 31, 2010 at 10:46 AM, Tom Lane wrote: >>> We are not going to try to enforce uniqueness. This has been debated >>> before, and most people like the current behavior just fine, or at least >>> better than the alternatives. > >> Really? I thought the issue was that no one had figured out how to do >> it, or that no one had written the patch, not that anyone thought the >> current behavior was particularly desirable. What happens if you say >> ALTER TABLE .. DROP CONSTRAINT or COMMENT ON CONSTRAINT? You just >> pick one at random? > > No, because those syntaxes constrain the choice to one single > constraint. Perhaps if the SQL committee had designed 'em, > there'd be an issue; but they are Postgres-isms. Hrm. I was thinking of this old thread, but maybe that's not the same issue. http://archives.postgresql.org/pgsql-hackers/2008-10/msg00256.php -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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
On Tue, Aug 31, 2010 at 10:46 AM, Tom Lane wrote: > We are not going to try to enforce uniqueness. This has been debated > before, and most people like the current behavior just fine, or at least > better than the alternatives. Really? I thought the issue was that no one had figured out how to do it, or that no one had written the patch, not that anyone thought the current behavior was particularly desirable. What happens if you say ALTER TABLE .. DROP CONSTRAINT or COMMENT ON CONSTRAINT? You just pick one at random? That's really what most people want? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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 #5633: an empty row with null-like values in not-null field
On Sun, Aug 29, 2010 at 8:10 PM, tkim wrote: > > The following bug has been logged online: > > Bug reference: 5633 > Logged by: tkim > Email address: kth...@gmail.com > PostgreSQL version: 9.0 beta 4 > Operating system: windows 7 professional > Description: an empty row with null-like values in not-null field > Details: > > After inserting a lot of data into a partitioned table, i found a weird > thing. When I query the table, i can see an empty row with null-like values > in 'not-null' fields. > > To show you guys the captured image, i think it's good to link my question > on StackOverflow website. > > http://stackoverflow.com/questions/3597000/postgresql-9-0-an-empty-row-with- > null-like-values-in-not-null-field Can you send us the results of: pg_dump -t st_daily2 and the results of doing this in psql: \d st_daily2 -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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 #5627: Can not install on POSReady 2009
On Mon, Aug 23, 2010 at 6:27 AM, Johnny Wang wrote: > When we try to install the PG 8.3.4 on XP POSReady 2009 operating system, > but we always got an error message "Failed to set permissions on the > installed files. Please see the logfile in C:\Program Files\tmp\pgperm.log." > !! At the risk of asking an obvious question, what are the contents of that logfile? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] Automated analyze process fails with custom function, which works perfect as regular user (8.4.2).
On Mon, Aug 9, 2010 at 3:59 AM, Patric de Waha wrote: > Hello, > I found something weird in the logs. > Apparently the automated analyze process has some > problems with custom functions. > > Using my regular database user for this db, i get no problems > using the functions which fail for the automated analyze process. > Can this be a search_path problem? Maybe you should do ALTER FUNCTION name SET search_path = 'the right search path' and see if that helps. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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 #5305: Postgres service stops when closing Windows session
On Sat, Feb 6, 2010 at 9:09 PM, Chris Travers wrote: > On Sat, Feb 6, 2010 at 2:36 PM, Robert Haas wrote: >> That's really odd. Nothing pgAdmin does should be able to crash the >> PostgreSQL server, I would think. Have you got any custom code loaded >> into PostgreSQL? Or non-custom, but buggy? >> >> I'm guessing the problem only occurs if PGadmin is actually connected >> to the PostgreSQL server, but perhaps you could verify that. If so, I >> would see if you can get a stack backtrace of the backend to which >> PGadmin is connected. > > It wouldn't surprise me if this were a Windows bug (Terminal Services > may have improved since I was supporting it but it used to be quite > common that it would cause weird behavior in applications) I > personally think the stack trace is likely to be the best way to test > where the problem is. I suspect this is the same problem as bug #4897, and probably also the same problem as this: http://archives.postgresql.org/pgsql-bugs/2009-08/msg00114.php and maybe also this and this: http://archives.postgresql.org/pgsql-bugs/2010-02/msg00179.php http://archives.postgresql.org/pgsql-admin/2009-05/msg00105.php Unfortunately, it seems that no one has been able to get a stack trace yet. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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 #5623: xml2 and uuid-ossp contribs fail to compile
On Wed, Aug 18, 2010 at 9:55 AM, Jens Wilke wrote: > uuid-ossp.c:29:2: error: #error OSSP uuid.h not found This seems like the one to look at. Perhaps you need to apt-get install the package that contains that file. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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 #5622: Query failed: server closed the connection unexpectedly
On Tue, Aug 17, 2010 at 10:55 PM, Craig Ringer wrote: > On 18/08/10 05:26, Thue Janus Kristensen wrote: >> /var/log/syslog: >> Aug 17 23:08:26 thue-laptop kernel: [46188.626357] postgres[11461]: segfault >> at 21723858 ip 00772286 sp bf8a4a80 error 4 in postgres[621000+446000] > > If you're game, it might also be useful to see what the backend was up > to when it crashed. There are some instructions on how to get a stack > trace on the wiki: > > http://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Linux/BSD Yeah, that would be very helpful. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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 #5622: Query failed: server closed the connection unexpectedly
On Tue, Aug 17, 2010 at 5:26 PM, Thue Janus Kristensen wrote: > > The following bug has been logged online: > > Bug reference: 5622 > Logged by: Thue Janus Kristensen > Email address: thu...@gmail.com > PostgreSQL version: 8.4.4 > Operating system: Ubuntu 10.04 LTS i386 > Description: Query failed: server closed the connection unexpectedly > Details: > > I have a 100% reproducible server crash with postgresql. This crash does not > happen on my 8.3 installation. > > When the crash occurs I get > pg_query(): Query failed: server closed the connection unexpectedly > This probably means the server terminated abnormally > before or while processing the request. > > However, I have been unable to simplify the error condition :(. I tried > dumping 8000 lines of sql, but when I run them separately, I don't get a > crash. > > The last lines before the crash are ($db->query is a prepared query, > $db->simple_query is a non-prepared query): > $db->query("SAVEPOINT lala"); > $res = questions::move_question($t->e, $g1q1_id, $g2_id); //some queries > here > $db->query("ROLLBACK TO SAVEPOINT lala"); > $db->query("RELEASE SAVEPOINT lala"); > //some queries here > $res = aas::insert_in_group($t->e, $aa_id, $g2_id); > $res = questions::move_question($t->e, $g1q1_id, $g2_id); > //crash when line below is run! > $db->simple_query("SET CONSTRAINTS ALL IMMEDIATE"); > ?> > > /var/log/syslog: > Aug 17 23:08:26 thue-laptop kernel: [46188.626357] postgres[11461]: segfault > at 21723858 ip 00772286 sp bf8a4a80 error 4 in postgres[621000+446000] > > /var/log/postgresql/postgresql-8.4-main.log > 2010-08-17 23:08:26 CEST LOG: server process (PID 11461) was terminated by > signal 11: Segmentation fault > 2010-08-17 23:08:26 CEST LOG: terminating any other active server > processes > 2010-08-17 23:08:26 CEST WARNING: terminating connection because of crash > of another server process > 2010-08-17 23:08:26 CEST DETAIL: The postmaster has commanded this server > process to roll back the current transaction and exit,\ > because another server process exited abnormally and possibly corrupted > shared memory. > 2010-08-17 23:08:26 CEST HINT: In a moment you should be able to reconnect > to the database and repeat your command. > 2010-08-17 23:08:26 CEST LOG: all server processes terminated; > reinitializing > 2010-08-17 23:08:26 CEST LOG: database system was interrupted; last known > up at 2010-08-17 23:04:02 CEST > 2010-08-17 23:08:26 CEST LOG: database system was not properly shut down; > automatic recovery in progress > 2010-08-17 23:08:26 CEST LOG: redo starts at 0/37500054 > > I realize that this is probably not enough to understand the bug, but I will > be happy to try to debug it further, if I am given a pointer as to how. Well, obviously the best thing would be to isolate a reproducible test case. But maybe a good start would be to try to get a list of the exact series of SQL statements that are being executed. Perhaps you could set log_min_duration_statement=0 and then find 'em in the logs. If you do this with some trivial query in the places where you have "some queries here", it doesn't crash. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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 #5621: Insufficient locking of dependent objects
On Tue, Aug 17, 2010 at 2:15 PM, Caleb Welton wrote: > There is a general class of concurrency bugs related to insufficient > locking > of dependent objects. This can occur with just about any inter-object > dependency that exists in the system: > > Creating a table while dropping the schema it is in > Creating a table while dropping a type it depends on > Creating a function while dropping the language > Creating an aggregate while dropping the transition function. > Etc. More specifically, I think the bug is that we don't really do much locking on database objects other than tables. This would be good to fix, but probably we'd need to start by coming up with a coherent overall plan. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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 #5613: cannot delete
On Wed, Aug 11, 2010 at 1:41 PM, Scott wrote: > > The following bug has been logged online: > > Bug reference: 5613 > Logged by: Scott > Email address: wheels7...@hotmail.com > PostgreSQL version: 8.4 > Operating system: vista > Description: cannot delete > Details: > > I have recently tried to install PostgreSQL to use with poker tracker. I > have having problems connecting to the server, where then i was advised to > completly remove and download again. I have come accross a file located in > c:\programfilmes, called PostgresSQL. I am unable to delete this folder > file, even when using CMD it says the file cannot be found. I then proceeded > to open this file and delete the items individually to see what was left, > all the files were able to delete except one: pg_stat_tmp Please could you > help with this as i have spoke to comuter technichian company and they were > unable to remove the file. I think you have a Windows program rather than a PostgreSQL problem. Based on my limited previous experience with Windows, problems like this are often caused by trying to delete a file while it's in use. Perhaps you need to stop the PostgreSQL service. An even better idea might be to uninstall PostgreSQL using Add/Remove Programs, or whatever uninstaller came with the installer you used. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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 #5611: SQL Function STABLE promoting to VOLATILE
On Thu, Aug 12, 2010 at 11:15 AM, Tom Lane wrote: >> I'm not exactly following this. My guess is that the breakeven point >> is going to be pretty low because I think Param nodes are pretty >> cheap. > > If you have any significant number of executions of the expression, then > of course converting it to an initplan is a win. What I'm worried about > is where you have just a small number (like maybe only one, if it gets > converted to an indexqual for instance). Then the added expense of > setting up the initplan isn't going to be repaid. As long as the > expression is pretty expensive, the percentage overhead of wrapping it > in an initplan will probably be tolerable anyway, but I'm not sure where > the threshold of "pretty expensive" is for that. Oh, I see. It seems a lot more elegant if we can start by determining whether the expression is in a context where it's likely to be executed more than once. *thinks* I wonder if we could make this decision mostly based on node types. Maybe it's reasonable to say that if we're doing say, a Seq Scan, we always assume it's going to get evaluated more than once. Yeah, the table could have <2 rows in it, but mostly it won't, and I don't know that it's wise to optimize for that case even if we *think* that's what the statistics are telling us. Similarly for a Function Scan, CTE Scan, Worktable Scan, etc. We *could* look at the row estimates, but I bet it isn't necessary. On the other hand, for an index qual, it's probably pointless. I guess the hard case is a filter qual on an index scan... it's not too clear to me what the right thing to do is in that case. >> Well, that's certainly a good place to start, but I was thinking that >> it would be nice to optimize things like this: > >> SELECT * FROM foo WHERE somecolumn = somefunc(); > >> This is OK if we choose a straight index scan, but it's probably very >> much worth optimizing if we end up doing anything else. If that's too >> hairy, then maybe not, but it's not obvious to me why it would be >> expensive. > > Because you have to look at every subexpression of every subexpression > to figure out if it's (a) stable and (b) expensive. Each of those > checks is un-cheap in itself, and if you blindly apply them at every > node of an expression tree the cost will be exponential. I think you'd need some kind of expression tree walker that builds up a list of maximal stable subexpression trees. It would be nice to figure this out at some point in the process where we already have to check volatility anyway. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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 #5611: SQL Function STABLE promoting to VOLATILE
On Thu, Aug 12, 2010 at 10:44 AM, Tom Lane wrote: > Robert Haas writes: >> On Wed, Aug 11, 2010 at 5:12 PM, Tom Lane wrote: >>> Yeah, possibly. It would probably be difficult for the planner to >>> figure out where the cutover point is to make that worthwhile, though; >>> the point where you'd need to make the transformation is long before we >>> have any rowcount estimates. > >> This may be a stupid question, but why does the transformation have to >> be done before we have the row count estimates? > > Well, I was thinking in terms of doing it when we do the SRF inlining. > It might be that we could get away with just having an arbitrary cost > limit like 100*cpu_operator_cost, and not think about how many rows > would actually be involved. I'm not exactly following this. My guess is that the breakeven point is going to be pretty low because I think Param nodes are pretty cheap. >> I think we're just >> looking for a scan node with a filter condition that contains a stable >> subexpression that's expensive enough to be worth factoring out, > > I do *not* want to grovel over every subexpression (and > sub-sub-expression, etc) in a query thinking about whether to do this. > That gets O(expensive) pretty quickly. My idea of the appropriate scope > of a hack like this is just to prevent any performance loss from SRF > inlining. Well, that's certainly a good place to start, but I was thinking that it would be nice to optimize things like this: SELECT * FROM foo WHERE somecolumn = somefunc(); This is OK if we choose a straight index scan, but it's probably very much worth optimizing if we end up doing anything else. If that's too hairy, then maybe not, but it's not obvious to me why it would be expensive. > Another approach we could take is to fix the implementation limitation > in inline_set_returning_function() about punting when there's a > sub-select in the arguments. Then users could make this happen for > themselves when it matters. Hmm. I'm usually in favor of removing implementation restrictions, but I'm not too sure about the effects of removing this one. It seems like it would be nicer to have a solution that didn't require the user to write the query a certain way. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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 #5611: SQL Function STABLE promoting to VOLATILE
On Wed, Aug 11, 2010 at 5:12 PM, Tom Lane wrote: > Robert Haas writes: >> In theory, the optimization Brian wants is possible here, right? I >> mean, you could replace the functional call with a Param, and pull the >> Param out and make it an InitPlan. Seems like that would generally be >> a win, if you figure to loop more than once and the execution cost of >> the function is not too tiny. > > Yeah, possibly. It would probably be difficult for the planner to > figure out where the cutover point is to make that worthwhile, though; > the point where you'd need to make the transformation is long before we > have any rowcount estimates. This may be a stupid question, but why does the transformation have to be done before we have the row count estimates? I think we're just looking for a scan node with a filter condition that contains a stable subexpression that's expensive enough to be worth factoring out, so I feel like we have the necessary information when we're constructing the RelOptInfo. The startup cost is so trivial that I can't see generating mutiple paths for it; I think you could just make a local decision whether to apply the optimization or not. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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 #5588: I use a lot of the "INHERITS", results of tests found that the performance is very low.
On Sun, Aug 1, 2010 at 10:16 PM, runner.mei wrote: > > The following bug has been logged online: > > Bug reference: 5588 > Logged by: runner.mei > Email address: runner@gmail.com > PostgreSQL version: 8.4.4 > Operating system: windows > Description: I use a lot of the "INHERITS", results of tests found > that the performance is very low. > Details: > > Hello, I try to build a cmdb database with using postgresql , I use a lot of > the "INHERITS", results of tests found that the performance is very low, > there are 2,000,000 pieces of data when the data when a data query, it was > however spent 1672 ms, I was wrong it? Please refer to the following wiki page for information about getting help with this problem. http://wiki.postgresql.org/wiki/SlowQueryQuestions You'll need to provide more details to get help, and it would also be a good idea to post to the correct mailing list, which is pgsql-performance. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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 #5611: SQL Function STABLE promoting to VOLATILE
On Wed, Aug 11, 2010 at 4:47 PM, Brian Ceccarelli wrote: > Please show me an example where an inline query gets a performance boost. Sure. rhaas=# create table example as select a from generate_series(1,10) a; SELECT 10 rhaas=# alter table example add primary key (a); NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "example_pkey" for table "example" ALTER TABLE rhaas=# create function f() returns setof int as $$select a from example$$ language sql stable; rhaas=# explain analyze select * from f() where f = 1; QUERY PLAN -- Index Scan using example_pkey on example (cost=0.00..8.28 rows=1 width=4) (actual time=0.102..0.103 rows=1 loops=1) Index Cond: (a = 1) Total runtime: 0.149 ms (3 rows) rhaas=# alter function f() volatile; ALTER FUNCTION rhaas=# explain analyze select * from f() where f = 1; QUERY PLAN --- Function Scan on f (cost=0.25..12.75 rows=5 width=4) (actual time=34.585..51.972 rows=1 loops=1) Filter: (f = 1) Total runtime: 63.277 ms (3 rows) -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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 #5611: SQL Function STABLE promoting to VOLATILE
On Wed, Aug 11, 2010 at 11:50 AM, Brian Ceccarelli wrote: > OK. The documentation says "allows the optimizer to optimize . . . ." > But then the example guarantees the one-time-only for a index scan condition. > > From the documentation: 8.4.4 Chapter 32 and 8.2.17 Chapter 33. > > .A STABLE function cannot modify the database and is guaranteed to return > the same results given the same arguments for all rows within a single > statement. This category allows the optimizer to optimize multiple calls of > the function to a single call. In particular, it is safe to use an expression > containing such a function in an index scan condition. (Since an index scan > will evaluate the comparison value only once, not once at each row, it is not > valid to use a VOLATILE function in an index scan condition.) > > The behavior of the optimizers <= 8.2 certainly fit the description. The > 8.4 behavior is vastly different. Reading between the lines, I think I sense that this has got you pretty frustrated, so in defense of the new behavior, let me just mention that, in general, inlining SQL queries results in a HUGE performance benefit. It's sort of unfortunate that it doesn't work out that way for you in this case, but I don't think it's a bad idea in general. *thinks* In theory, the optimization Brian wants is possible here, right? I mean, you could replace the functional call with a Param, and pull the Param out and make it an InitPlan. Seems like that would generally be a win, if you figure to loop more than once and the execution cost of the function is not too tiny. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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 #5611: SQL Function STABLE promoting to VOLATILE
On Wed, Aug 11, 2010 at 11:01 AM, Brian Ceccarelli wrote: > My complaint remains. That inlined function f_return_ver_id_4() is a > STABLE function, inlined or not. Postgres now calls it multiple times during > the transaction, even though the arguments to f_return_ver_id_4() have not > changed. > > STABLE no longer means STABLE. This behavior is killing my performance. > I am getting 500% to 3% increase in latency. We've never guaranteed that, and almost certainly never will. Marking a function STABLE means that the planner is *allowed to assume* that the results won't change for a given set of arguments, not that it is *required to prevent* it from being called multiple times with the same set of arguments. You can certainly prevent the function from being inlined, though (perhaps, by writing it in PL/pgsql). -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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 #5604: Setting NOT NULL on inherited column turns to real column in dump
On Fri, Aug 6, 2010 at 12:31 AM, Jon Erdman (aka StuckMojo) wrote: > Description: Setting NOT NULL on inherited column turns to real > column in dump > > I think the fix here is relatively simple: make NOT NULL on an inherited > column dump as an ALTER TABLE. > > If you set NOT NULL on an inherited column in a child table, then drop the > column from the parent, it's gone from both and all is well. > > However, if you dump and restore the db, then drop the parent column, the > inherited column remains in the child table. This is a result of the NOT > NULL dumping as a column create in the child, which I assume then shadows > the inherited column after restore. That sucks. I have a feeling it's going to be hard to fix properly without this patch: https://commitfest.postgresql.org/action/patch_view?id=312 I don't think your proposed fix will work because the NOT NULL-ness could be either inherited or not inherited. The column could even be inherited from multiple parents, some of which have a NOT NULL constraint and others of which do not. Consider: create table top1 (a int not null); create table top2 (a int); create table bottom () inherits (top1, top2); alter table bottom no inherit ; If = top1, then bottom.a should now allow nulls, but if = top2, then it should still be not null. Unfortunately, we don't do enough bookkeeping right now to distinguish those cases. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] Measuring execution time
On Tue, Aug 10, 2010 at 3:46 AM, vamsi krishna wrote: > I want to measure the execution time spent running an SQL select query after > the plan generation. > > So precisely I want to put my start timer before createQueryDesc() or > ExecutorStart() and end timer after freeQueryDesc() or ExecutorEnd(). > > Right now I did so in "spi.c", "explain.c", "pquery.c" but they are not the > default execution cases. Can someone tell me which file holds the default > call to ExecutorStart(), because I also want to see the select query result > unlike in the case of "explain" ? Well, you should be able to find all the calls to ExecutorStart() by using grep. But it sounds like you might be better off implementing this as an executor hook. Or perhaps one of the existing ones (auto_explain or pg_stat_statements) would give you what you need. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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 #5603: pg_tblspc and pg_twoface directories get deleted when starting up service
On Thu, Aug 5, 2010 at 2:46 PM, Nacho Mezzadra wrote: > > The following bug has been logged online: > > Bug reference: 5603 > Logged by: Nacho Mezzadra > Email address: nachomezza...@gmail.com > PostgreSQL version: 8.3.11 > Operating system: Red Hat Enterprise 5.3 > Description: pg_tblspc and pg_twoface directories get deleted when > starting up service > Details: > > This issue happened not very frequently, but it happened to me 3 times, in 3 > different Red Hat servers. > The thing is that when stopping the Postgresql service with the > "/sbin/service postgresql-8.3 stop" command, and after that starting it with > the "/sbin/service postgresql-8.3 start" command (haven't tried with the > restart one though), a few times both pg_tblspc and pg_twoface directories > (inside data directory) get somehow deleted and hence the start service > command fails. Looking in the log files I find the following error: > > 2010-07-19 16:54:55 ISTFATAL: could not open directory "pg_tblspc": No such > file or directory > > So I manually create the "pg_tblspc" directory, and then try to start again > the service unsuccessfully, getting this time a similar error, but saying > that pg_twoface directory doesn't exist. > > After creating the pg_twoface directory, service can be successfully > started. > > Please note that all these always happened running the service command as > root. > All 3 linux boxes are running over a VMWare host. This is pretty scary, but it's a little hard to believe that Red Hat would ship a script which had even the faintest chance of obliterating two critical directories. Especially since the guy who does the packaging of PostgreSQL over thereabouts is our most knowledgeable, experienced, and prolific committer. So I suspect you've a (broken) custom script, or a cron job that's doing something evil, or some other weirdness that is specific to your installations, but you haven't provided enough details to speculate in detail (for example, perhaps you could reply to the list and post a copy of the script you think is doing this). Also, I'm pretty sure that we don't have a directory called pg_twoface, though it would pretty funny if we did. It's fairly obvious what this is meant to say, but it doesn't. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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 #5587: Installer non-default file association problem
On Sun, Aug 1, 2010 at 3:39 PM, Mike Parfitt wrote: > > The following bug has been logged online: > > Bug reference: 5587 > Logged by: Mike Parfitt > Email address: m_parf...@hotmail.com > PostgreSQL version: 8.4.4.1 > Operating system: Windows XP SP3 > Description: Installer non-default file association problem > Details: > > When the installer halts showing "Initialising the database cluster (this > may take a few minutes)" message it may be because that PC has a non-default > .bat file association. > > In my case, I used Process Explorer and looked at the other processes it had > spawned :- > > postgresql-8.4.4-1-windows.exe > cscript.exe > UEDIT32.exe > > UEDIT32.EXE (UltraEdit) is my text editor of choice. > > Either the installer should test whether the .bat file association is going > to start the right program, or the right program should be included in the > parameter passed to the .Run function. Is this the EDB one-click installer you're using, or something else? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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 #5586: network installation
On Sun, Aug 1, 2010 at 2:40 PM, franklyn wrote: > Output folder: C:\Program Files (x86)\RVG Software\Holdem Manager Sounds to me like you need to contact the makers of Holdem Manager (RVG Software?). I don't see any reason to think that there's a problem with PostgreSQL; it looks more like your installer isn't configuring it properly. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [HACKERS] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On Thu, Aug 5, 2010 at 3:16 PM, Tom Lane wrote: > Josh Berkus writes: >>> Well, maybe we need to expend some more sweat on the error message then. >>> But this patch was still a prerequisite thing, because without it there >>> is no error that we can complain about. > >> Yes, I'd say an addition to the HINT is in order *assuming* at that >> stage we can tell if the user passed an ORDER BY or not. > > I was just looking at this, and realized I was mistaken earlier: the > error is issued in ParseFuncOrColumn, which already is passed the > agg_order list, so actually it's completely trivial to tell whether > a variant error message is appropriate. I suggest that we key it off > there being not just an ORDER BY, but an ORDER BY with more than one > element; if there's only one then this cannot be the source of > confusion. > > Next question: exactly how should the variant HINT be phrased? > I'm inclined to drop the bit about explicit casts and make it read > something like > > HINT: No aggregate function matches the given name and argument > types. Perhaps you misplaced ORDER BY; ORDER BY must appear after all > regular arguments of the aggregate. Could we arrange to emit this error message only when there is an aggregate with the same name but different arguments? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [HACKERS] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On Wed, Aug 4, 2010 at 6:19 PM, Tom Lane wrote: > for: tgl, josh, badalex, mmoncure > against: rhaas, thom > Anybody else want to vote, or change their vote after seeing the patch? If we're not regarding this as beta-forcing, I abstain. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [HACKERS] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On Wed, Aug 4, 2010 at 7:07 PM, Tom Lane wrote: >> Or in other words, any thoughts on: >> select string_agg(delim, expression); > > That looks pretty weird to me anyway, with or without use of ORDER BY. > Nobody would think to write the delimiter first. Usually you put the > "most important" argument first, and no one would see the delimiter > as the most important one. Well, it would match the syntax of things like perl's join(). But I think that's probably not enough reason to go fiddling with it. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On Wed, Aug 4, 2010 at 3:25 PM, Alex Hunsaker wrote: > I think forcing an initdb might be more trouble than this wart is worth. +1. I would not make this change unless we have to force an initdb anyway. And I really hope we don't, because I'm sort of hoping the next 9.0 release will be rc1. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] string_agg delimiter having no effect with order by
On Wed, Aug 4, 2010 at 1:04 PM, Tom Lane wrote: > Robert Haas writes: >> On Wed, Aug 4, 2010 at 12:44 PM, Tom Lane wrote: >>> Robert Haas writes: >>>> I suppose this confusion is only possible because string_agg has both >>>> a one-argument and a two-argument form. >>> >>> Right, or at least that's what allows the mistake to go through without >>> reporting any error. > >> No, that's what lets the correct form go through without reporting any error. > > Really? IMO the reason Thom had a problem was he thought he was > invoking the two-argument form of string_agg, but he was really > invoking the one-argument form. I had my head tilted a slightly different way, but, yes. > If we were a bit earlier in the 9.0 cycle I would suggest that this > confusion is a sufficient reason to drop the one-argument form of > string_agg. It's too late now though. Agreed on both points. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] string_agg delimiter having no effect with order by
On Wed, Aug 4, 2010 at 12:44 PM, Tom Lane wrote: > Robert Haas writes: >> I suppose this confusion is only possible because string_agg has both >> a one-argument and a two-argument form. > > Right, or at least that's what allows the mistake to go through without > reporting any error. No, that's what lets the correct form go through without reporting any error. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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 #5598: Compatibility modes
On Wed, Aug 4, 2010 at 10:53 AM, Shine wrote: > We are planning to upgrade from Postgresql 8.2 to 8.4, and we have hit the > following error when running our application against it. > > org.postgresql.util.PSQLException: ERROR: operator does not exist: character > varying = integer > > We have noticed many people have reported this issue and that this was an > intentional change on your side. > > We would like to know if there is a workaround built into postgres by > setting some kind of COMPATIBILITY variable (similar to SQL SERVER 2005 / > 2008)... Sorry, there is no such mode... -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] string_agg delimiter having no effect with order by
On Wed, Aug 4, 2010 at 11:29 AM, Tom Lane wrote: > Robert Haas writes: >> Oh, yeah. I guess you need this: > >> select thing, string_agg(stuff, ',' order by stuff) from agg_test >> group by thing; > >> Rather than this: > >> select thing, string_agg(stuff order by stuff, ',') from agg_test >> group by thing; > >> It's all kinds of not obvious to me what the second one is supposed to >> mean, but I remember this was discussed before. Perhaps we need a >> somewhere about multi-argument aggregates. > > Done: > > + > + When dealing with multiple-argument aggregate functions, note that the > + ORDER BY clause goes after all the aggregate arguments. > + For example, this: > + > + SELECT string_agg(a, ',' ORDER BY a) FROM table; > + > + not this: > + > + SELECT string_agg(a ORDER BY a, ',') FROM table; -- not what you want > + > + The latter syntax will be accepted, but ',' will be > + treated as a (useless) sort key. > + Oh, right, that's what it's supposed to mean. Thanks for adding this. I suppose this confusion is only possible because string_agg has both a one-argument and a two-argument form. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] string_agg delimiter having no effect with order by
On Wed, Aug 4, 2010 at 6:03 AM, Thom Brown wrote: > Actually, this rings a bell. I think this may have been raised > before, something to do with the delimiter being accepted as one of > the order by values. If this isn't really a bug, could someone > mention it in the docs somewhere? Oh, yeah. I guess you need this: select thing, string_agg(stuff, ',' order by stuff) from agg_test group by thing; Rather than this: select thing, string_agg(stuff order by stuff, ',') from agg_test group by thing; It's all kinds of not obvious to me what the second one is supposed to mean, but I remember this was discussed before. Perhaps we need a somewhere about multi-argument aggregates. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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 #5543: Poor performance - Index scan backwards not used for order by desc with partitioned tables
On Tue, Jul 27, 2010 at 7:27 PM, Tom Lane wrote: > Robert Haas writes: >> Does it help if you put a CHECK (false) constraint on the parent table? > > It won't --- it'll still result in an append plan even if there's only > one surviving child. > > This is one of many things that seem to me to not make sense to tackle > until we have an explicit notion of partitioning. Having the planner > try to prove from individual constraints that it could get a correctly > sorted Append result without an explicit sort step would be hugely > expensive, and complicated --- imagine even trying to pick out the > relevant indexes without any infrastructure to help identify them. > With a partitioned structure we could understand that a-priori. Hmm, I thought we had something that made it behave more like the non-partitioned case when there is only one surviving partition. But I agree that, perhaps apart from that special case, there's not much hope of improving this until we have more infrastructure. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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 #5543: Poor performance - Index scan backwards not used for order by desc with partitioned tables
On Tue, Jul 6, 2010 at 2:20 PM, Ranga Gopalan wrote: > > The following bug has been logged online: > > Bug reference: 5543 > Logged by: Ranga Gopalan > Email address: ranga_gopa...@hotmail.com > PostgreSQL version: 8.4.4 > Operating system: Linux x86-64 > Description: Poor performance - Index scan backwards not used for > order by desc with partitioned tables > Details: > > My problem is regarding ORDER BY / LIMIT query behavior when using > partitioning. > > I have a large table (about 100 columns, several million rows) partitioned > by a column called day (which is the date stored as mmdd - say 20100502 > for May 2nd 2010 etc.). Say the main table is called FACT_TABLE and each > child table is called FACT_TABLE_mmdd (e.g. FACT_TABLE_20100502, > FACT_TABLE_20100503 etc.) and has an appropriate CHECK constraint created on > it to CHECK (day = mmdd). > > The query pattern I am looking at is (I have tried to simplify the column > names for readability): > > SELECT F1 from FACT_TABLE > where day >= 20100502 and day <= 20100507 # selecting for a week > ORDER BY F2 desc > LIMIT 100 > > > This is what is happening: > > When I query from the specific day's (child) table, I get what I expect - a > descending Index scan and good performance. > > # explain select F1 from FACT_TABLE_20100502 where day = 20100502 order by > F2 desc limit 100; > QUERY > PLAN > > > > -- > Limit (cost=0.00..4.81 rows=100 width=41) > -> Index Scan Backward using F2_20100502 on FACT_TABLE_20100502 > (cost=0.00..90355.89 rows=1876985 width=41 > ) > Filter: (day = 20100502) > > > > BUT: > > When I do the same query against the parent table it is much slower - two > things seem to happen - one is that the descending scan of the index is not > done and secondly there seems to be a separate sort/limit at the end - i.e. > all data from all partitions is retrieved and then sorted and limited - This > seems to be much less efficient than doing a descending scan on each > partition and limiting the results and then combining and reapplying the > limit at the end. > > explain select F1 from FACT_TABLE where day = 20100502 order by F2 desc > limit 100; > QUERY > PLAN > > > > --- > Limit (cost=2084948.01..2084948.01 rows=100 width=41) > -> Sort (cost=2084948.01..2084994.93 rows=1876986 width=41) > Sort Key: public.FACT_TABLE.F2 > -> Result (cost=100.00..2084230.64 rows=1876986 > width=41) > -> Append (cost=100.00..2084230.64 rows=1876986 > width=41) > -> Seq Scan on FACT_TABLE > (cost=100.00..110.02 rows=1 width=186) > Filter: (day = 20100502) > -> Seq Scan on FACT_TABLE_20100502 FACT_TABLE > (cost=100.00..1084220.62 rows=1876985 width=4 > 1) > Filter: (day = 20100502) > (9 rows) Does it help if you put a CHECK (false) constraint on the parent table? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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
On Sat, Jul 24, 2010 at 11:37 PM, Bruce Momjian 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. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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 #5562: icon "terrestrial globe" much too big
On Fri, Jul 16, 2010 at 6:39 AM, Heinz Groote wrote: > > The following bug has been logged online: > > Bug reference: 5562 > Logged by: Heinz Groote > Email address: heinz.gro...@t-online.de > PostgreSQL version: 8.4 > Operating system: opensuse 11.2 > Description: icon "terrestrial globe" much too big > Details: > > The image size of the icons with the terrestrial globe is so big that it > destroys the main menu of gnome. > > (Idea: By reducing the image size to 31x31 with GIMP, the size compares to > all the other icons.) > > I hopefully may report this subject on this place, because it is an overall > problem with the installation of postgresql with all the modules using this > type of icon. > > Thank you > regards > *** heinz *** PostgreSQL itself doesn't have icons. Perhaps this is a pgadmin issue? http://www.pgadmin.org/support/list.php -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] Data extraction via sql from postgres 8.2 to oracle9i.
On Wed, Jul 21, 2010 at 2:04 AM, wrote: > We look for Data extraction via sql from postgres 8.2 to oracle9i. > Postgres and oracle both are remote servers of solaries > Kindly suggest steps and drivers needed to perform the task. This doesn't sound like a bug to me, so you should probably choose a more appropriate mailing list on which to ask this question. You'll probably have better luck if you provide a few more details about what your setup is and what you're trying to do with it. http://wiki.postgresql.org/wiki/Guide_to_reporting_problems -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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 #5567: will not install
On Thu, Jul 22, 2010 at 2:08 PM, Alex Hunsaker wrote: > Its this bit: >>> The installation failed at step: PostGreSOL Installation. >>> For additional information, please review the log files stored in the >>> archive: /opt/IBM/tivoli/tip/logs.zip > Ive no idea what that the above is doing, it looks like some kind of > installer... The installer apparently isn't too smart, either, because the second-to-last name of our product is the 17th letter of the alphabet, not the 15th. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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 #5566: High levels of savepoint nesting trigger stack overflow in AssignTransactionId
On Thu, Jul 22, 2010 at 6:49 PM, Andres Freund wrote: >> Can someone provide a reproducible test case for this bug? I wasn't >> easily able to reproduce it. > 201007191950.13856.and...@anarazel.de contains a test script. You need > to actually do a action causing an xid to get calculated > (heap_(insert|update|delete) basically) after youre deeply stacked. Thanks, perfect. Committed and back-patched to 8.0. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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 #5566: High levels of savepoint nesting trigger stack overflow in AssignTransactionId
On Thu, Jul 22, 2010 at 5:01 PM, Robert Haas wrote: > On Mon, Jul 19, 2010 at 4:35 PM, Andres Freund wrote: >>> Well. I got that far. But why is that something worthy of support? >>> For one I have a hard time imaging a sensible use case, for another doing >>> anything in that deeply nested transactions seems to gets really slow (the >>> chain of transactions gets walked at some places for one thing, there seem >>> to be others). >>> >>> If want I can write a patch for that as well, seems to be trivial enough. >> Updated patch attached. > > Considering that this is a crasher, I think we'll need to back-patch > this. The proposed patch applies only as far back as 8.3, due to the > lazy XID assignment changes in that version, but it looks like the bug > exists all the way back to 8.0. It looks like only minor adjustments > are required for the older branches, though. 7.4 is not affected, as > it does not have subtransactions. Can someone provide a reproducible test case for this bug? I wasn't easily able to reproduce it. Attached please find a cleaned-up version of the patch for CVS HEAD. I am having a bit of trouble compiling the 8.2 patch I hacked up, and I believe that's because the git respository is borked. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company subxact-xid-recurse.patch Description: Binary data -- 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 #5566: High levels of savepoint nesting trigger stack overflow in AssignTransactionId
On Mon, Jul 19, 2010 at 4:35 PM, Andres Freund wrote: >> Well. I got that far. But why is that something worthy of support? >> For one I have a hard time imaging a sensible use case, for another doing >> anything in that deeply nested transactions seems to gets really slow (the >> chain of transactions gets walked at some places for one thing, there seem >> to be others). >> >> If want I can write a patch for that as well, seems to be trivial enough. > Updated patch attached. Considering that this is a crasher, I think we'll need to back-patch this. The proposed patch applies only as far back as 8.3, due to the lazy XID assignment changes in that version, but it looks like the bug exists all the way back to 8.0. It looks like only minor adjustments are required for the older branches, though. 7.4 is not affected, as it does not have subtransactions. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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 #5539: Generally incorrect planner estimations on join with UNION ALL
On Sat, Jul 3, 2010 at 8:47 PM, Maksym Boguk wrote: > notable planner had correctly estimated amount of rows in one side of the > join: > (Append (cost=0.00..0.98 rows=2 width=4)) > and planner hard correctly estimated amount of rows in the another side of > the join: > Index Scan using t3_value_key on t3 (cost=0.00..0.49 rows=1 width=8) > > And instead of use 2(from append)*1(from scan on t3)=2 as final row > estimation, planner chooses use heuristic value 1000. 2 isn't necessarily correct, because the two rows on the outer side needn't match the 1 row on the inner side - it could be less. But I agree with you that the estimate of 1000 doesn't seem to make much sense. I'm not sure where that's coming from. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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 #5542: Query optimization problem
On Tue, Jul 6, 2010 at 2:43 AM, Roman wrote: > I have a query: > > SELECT d1.ID, d2.ID > FROM DocPrimary d1 > JOIN DocPrimary d2 ON d2.BasedOn=d1.ID > WHERE (d1.ID=234409763) or (d2.ID=234409763) > > i think what QO(Query Optimizer) can make it faster (now it seq scan and on > million records works 7 sec) > > SELECT d1.ID, d2.ID > FROM DocPrimary d1 > JOIN DocPrimary d2 ON d2.BasedOn=d1.ID > WHERE (d2.BasedOn=234409763) or (d2.ID=234409763) EXPLAIN ANALYZE output for both queries, please? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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 #5545: permission denied on delete
On Thu, Jul 8, 2010 at 8:49 AM, Niranjan Pandit wrote: > role A is member of role B, role B has delete privilege also the select > privilege on the id field of the table. when logged in with role A and set > role to B, the permission is still denied to delete a record, any ideas why > is permission denied ? > > the set role is in a security invoker function before the delete statement. > Could this be related in anyway ? I tried it here and it worked for me. Can you provide a self-contained test case? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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 #5537: Makefile.darwin broken
On Fri, Jul 2, 2010 at 6:15 PM, Peter Abrahamsen wrote: > The following bug has been logged online: > > Bug reference: 5537 > Logged by: Peter Abrahamsen > Email address: rainh...@gmail.com > PostgreSQL version: 9.0beta2 > Operating system: Darwin x86_64 (MacOS 10.6.4) > Description: Makefile.darwin broken > Details: > > Makefile.darwin references src/backend/postgres, which doesn't exist. So what, specifically, is not working for you? I'm using MacOS X 10.6.4 x86_64 also, and I can build Postgres without a problem. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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 #5520: PG unable to find java stored procs without input parameters
On Tue, Jun 22, 2010 at 8:30 AM, Peter Mengaziol wrote: > > The following bug has been logged online: > > Bug reference: 5520 > Logged by: Peter Mengaziol > Email address: pmengaz...@electrainfo.com > PostgreSQL version: 8.4.4 > Operating system: OS X 10.4.13 > Description: PG unable to find java stored procs without input > parameters > Details: > > Under PG 8.4.1 and pl/java 1.4.0 have been unable to get a jsproc to be > registered and found when there is not a single input parameter. The same > code with a dummy input parameter is found and executed sucessfully. Since there's been no response here you might want to try the JDBC mailing list: http://archives.postgresql.org/pgsql-jdbc/ You might also want to provide some more details, like a complete test case that you think should work but doesn't. http://wiki.postgresql.org/wiki/Guide_to_reporting_problems -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] Postgres on AIX5.3 and AIX6.1
On Thu, Jun 24, 2010 at 11:02 PM, Robert Haas wrote: > On Thu, Jun 24, 2010 at 4:50 PM, Chris Browne wrote: >> alvhe...@commandprompt.com (Alvaro Herrera) writes: >>> Excerpts from Chris Browne's message of jue jun 24 14:40:30 -0400 2010: >>>> robertmh...@gmail.com (Robert Haas) writes: >>>> > This patch makes it clear that the workaround is no good on AIX 6.1, >>>> > but it doesn't seem quite clear about whether the underlying problem >>>> > has been fixed in AIX 6.1. It would be good to understand that. >>>> >>>> Better? >>> >>> Empty diff? >> >> Oops. Hopefully this is less empty. > > Yes. That seems pretty clear and unambiguous. Committed. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] Postgres on AIX5.3 and AIX6.1
On Thu, Jun 24, 2010 at 4:50 PM, Chris Browne wrote: > alvhe...@commandprompt.com (Alvaro Herrera) writes: >> Excerpts from Chris Browne's message of jue jun 24 14:40:30 -0400 2010: >>> robertmh...@gmail.com (Robert Haas) writes: >>> > This patch makes it clear that the workaround is no good on AIX 6.1, >>> > but it doesn't seem quite clear about whether the underlying problem >>> > has been fixed in AIX 6.1. It would be good to understand that. >>> >>> Better? >> >> Empty diff? > > Oops. Hopefully this is less empty. Yes. That seems pretty clear and unambiguous. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] Postgres on AIX5.3 and AIX6.1
On Thu, Jun 24, 2010 at 12:39 PM, Chris Browne wrote: > pie...@hogranch.com (John R Pierce) writes: >> On 06/23/10 6:02 PM, Chris Browne wrote: >>> There is already an FAQ on issues relating to AIX. It is, in recent >>> versions, part of the documentation tree: >>> >>> <http://www.postgresql.org/docs/9.0/static/installation-platform-notes.html> >>> >> >> can I take this opportunity to point out a problem on that page? >> >> In "5.8.1.3. Internet address issues", the bit about .. >> >> # ifconfig lo0 inet6 ::1/0 delete >> >> >> In 6.1, localhost may fail to come up at all when you next reboot >> after you do that. I had to recreate the ipv6 localhost before it >> would work correctly. I haven't investigated any farther. > > Here's a patch that makes this more version-sensitive. This patch makes it clear that the workaround is no good on AIX 6.1, but it doesn't seem quite clear about whether the underlying problem has been fixed in AIX 6.1. It would be good to understand that. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] odbc driver could not be loaded due to system error code 127
On Fri, Jun 18, 2010 at 7:09 AM, Praveen Upadhyaya (ZA) wrote: > I am getting the following error. Can you please help? > odbc driver could not be loaded due to system error code 127 You may want to try this question on the pgsql-odbc mailing list. http://archives.postgresql.org/pgsql-odbc/ -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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 #5510: ODBC database 8.4.2 not working on windows 7 32 bit
On Fri, Jun 18, 2010 at 4:02 AM, Ravi wrote: > > The following bug has been logged online: > > Bug reference: 5510 > Logged by: Ravi > Email address: surabi...@gmail.com > PostgreSQL version: 8 > Operating system: Windows 7 > Description: ODBC database 8.4.2 not working on windows 7 32 bit > Details: > > ODBC database 8.4.2 not working on windows 7 32 bit. > > When I click "Test setting" I get the following > error: > Could not connect to the server; > A connection attempt failed because the connected > party did not properly respond after a period of t > time. > > I have tried disabling the firewall etc. > > I suspect ODBC is not compatible with windows 7. > > I have been able to set it up and got it working > on Vista, XP for > serveral years. You may want to try this question on the pgsql-odbc mailing list. http://archives.postgresql.org/pgsql-odbc/ -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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 #5503: error in trigger function with dropped columns
On Sun, Jun 13, 2010 at 7:20 PM, Maxim Boguk wrote: > I think changes in 9.0 now mask actual bug instead of fix it. If I was > wrong, still would be useful to know how to use return record from > trigger function in that case, because I can't make a working version > at all. Why do you think that? I tried both the example you gave here and the example from your followup email on the 15th in 9.0beta, and the behavior seems correct there. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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 #5502: Preparing an array return Bug
On Sun, Jun 13, 2010 at 5:05 AM, xMoMx wrote: > > The following bug has been logged online: > > Bug reference: 5502 > Logged by: xMoMx > Email address: warit...@gmail.com > PostgreSQL version: 8.3 > Operating system: Window > Description: Preparing an array return Bug > Details: > > Spending the whole day trying to figure this out myself and finally I gave > up. My postGre simply refuse to prepare an array for insert and update. It > work fine when not preparing though. (But heck we all know we want to > prepare everything right?) > > Here's the code: > comm.Parameters.Add(new NpgsqlParameter("myArray", NpgsqlDbType.Array | > NpgsqlDbType.Smallint)); > comm.Parameters[0].Value = new Int16[2]{0,0}; > > Once execute, nothing is return. Not even error log. Hmm. This looks like it might be an npgsql problem. I confess I don't know the first thing about npgsql. I think this might be their mailing list though - maybe you want to try there? http://pgfoundry.org/mailman/listinfo/npgsql-devel -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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
On Fri, Jun 11, 2010 at 2:46 PM, Dimitri Fontaine wrote: >>>> 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? >> >> YES! It's a heck of a lot easier to fix the application than it is to >> doctor the dump output with vi. > > But of course you don't ever do that. What you do once the restore failed on > you is fix the schema and the application before to upgrade. Presumably, you mean that YOU don't ever do that. What everybody else does is up to them, and there are plenty of people on this thread saying either (1) they don't want to do what you're proposing or (2) their application doesn't need fixing because it already quotes everything. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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
On Thu, Jun 10, 2010 at 10:25 AM, Tom Lane wrote: > Bruce Momjian writes: >> 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. > > Yeah, and not only that, but you'd need the *old* server to cooperate. > > Which means BTW that "use the newer pg_dump" is only an 80% solution. > So maybe we do need to think about this. > > The least invasive answer that I can think of is to invent a "force > quoting" GUC that's looked at by all the deparsing functions used by > pg_dump. We have pg_dump set that once, on backend versions that > support it, and then we don't have to run around touching every single > deparsing function's signature (and adding extra code paths in pg_dump > to deal with older versions not having such functions). > > But the earliest this could be of use would be a 9.1->9.2 update ... That's OK. I don't have an immediate problem I need to solve; I just want to improve things for future users. The fact is, I've had this problem in the past, and it wasn't fun, so, I understand the OP's pain. But there's definitely a workaround until we get this done, it just isn't a particularly enjoyable one. The deparse_force_quoting GUC is a good idea - I bet the number of places that would need to examine the value of that GUC is extremely small. One possible objection is that such a GUC would also affect the output of tools like EXPLAIN, but I'm not sure we really need to worry about that. If someone really wants to force quoting in their EXPLAIN output, I say let 'em. If we really wanted to get sneaky we could even let the value of the GUC be a list of words to force-quote, with * meaning all. That would let a newer server talking to an older server hand over a specific list of keywords that it knows are problem cases, even if the user isn't using --quote-like-crazy. Not sure if we want to go there, though. The idea mentioned on another part of this thread of providing a way to separate schema and data dumps without tanking performance is a good one, too, but I still think this has merit even if we do that. Just because we make it easier to manually edit dump files is not a reason not to create options that render it unnecessary in the first place. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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_restore ignores -C when using a restore list -L
On Thu, Jun 10, 2010 at 9:39 AM, Tom Lane wrote: > Robert Haas writes: >> I believe this is the commit: > >> http://git.postgresql.org/gitweb?p=postgresql.git;a=commit;h=3a524653d18f29676b91f740634a673b72beb6b5 > >> It looks like the code was changed, but I don't see any doc updates. > > Eh? > http://archives.postgresql.org/pgsql-committers/2010-05/msg00191.php > and following entries Ah, missed that. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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
On Thu, Jun 10, 2010 at 9:35 AM, 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... Maybe so, but I don't give either method high marks for convenience. Suppose I have a server running 8.2 and I'm going to wipe it and install the latest version of $DISTRIBUTION which bundles 8.4. What our current policy essentially means is that I have to get 8.4 running on the old server before I wipe it (presumably compiling by hand, since the old version of the distro doesn't ship it), or else manually frobnicate the dump after I wipe it, or else find another server someplace to install 8.4 on and run the dump there prior to the OS upgrade. This really sucks. It's a huge pain in the tail, especially for people who aren't used to compiling PG from source at the drop of a hat. I'm sure someone will tell me my system administration practices suck, but people do these kinds of things, in real life, all the time. Maybe if we all had an IQ of 170 and an infinite hardware budget we wouldn't, but my IQ is only 169. :-) -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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_restore ignores -C when using a restore list -L
On Thu, Jun 10, 2010 at 3:58 AM, Russell Smith wrote: > On 15/05/10 05:15, Alvaro Herrera wrote: >> Excerpts from Tom Lane's message of vie may 14 13:26:06 -0400 2010: >> >> >>> However, I think -C is a special case because it's quite un-obvious >>> to the user that it effectively acts as a filter switch --- in fact a >>> de-filtering switch, because the lack of -C is what filters out the >>> DATABASE item. >>> >>> I'm inclined to think that we should document that the output of -l >>> is restricted by -n and similar switches, but change the code so that >>> -C doesn't affect -l output. Comments? >>> >> +1 >> > Was this discussed change made? Was it backported, or only applied to head? I believe this is the commit: http://git.postgresql.org/gitweb?p=postgresql.git;a=commit;h=3a524653d18f29676b91f740634a673b72beb6b5 It looks like the code was changed, but I don't see any doc updates. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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
On Thu, Jun 10, 2010 at 9:02 AM, Stefan Kaltenbrunner 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. Given Tom's comments upthread, I suspect that much of this will come down to whether anyone feels like trying to put in the work to make this happen, and whether they can come up with a reasonably clean design that doesn't involve massive code changes. Having not studied the problem, I don't have an opinion on whether that's possible. 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. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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
On Wed, Jun 9, 2010 at 4:48 PM, Robert Haas wrote: > On Wed, Jun 9, 2010 at 4:47 PM, Dean Rasheed wrote: >> On 9 June 2010 20:56, Robert Haas wrote: >>> On Wed, Jun 9, 2010 at 3:50 PM, Tom Lane wrote: >>>> Dean Rasheed 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. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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
On Wed, Jun 9, 2010 at 4:47 PM, Dean Rasheed wrote: > On 9 June 2010 20:56, Robert Haas wrote: >> On Wed, Jun 9, 2010 at 3:50 PM, Tom Lane wrote: >>> Dean Rasheed 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. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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
On Wed, Jun 9, 2010 at 9:35 PM, Bruce Momjian wrote: > 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? YES! It's a heck of a lot easier to fix the application than it is to doctor the dump output with vi. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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
On Wed, Jun 9, 2010 at 9:10 PM, Bruce Momjian wrote: > Robert Haas wrote: >> On Sun, Jun 6, 2010 at 2:53 PM, Dimitri Fontaine >> wrote: >> > Robert Haas 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.) Eeh, I've had this happen to me on earlier releases, and it didn't feel like a feature to me. YMMV, of course. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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
On Wed, Jun 9, 2010 at 3:50 PM, Tom Lane wrote: > Dean Rasheed 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. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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
On Wed, Jun 9, 2010 at 12:58 PM, Robert Haas wrote: > On Wed, Jun 9, 2010 at 12:57 PM, Dean Rasheed > wrote: >> On 9 June 2010 17:52, Robert Haas wrote: >>> On Wed, Jun 9, 2010 at 12:25 PM, Tom Lane wrote: >>>> Robert Haas writes: >>>>> On Wed, Jun 9, 2010 at 11:14 AM, Tom Lane wrote: >>>>>> Why not? Surely we can restrict EXPLAIN's set of key names to be safe. >>>> >>>>> It seems to me that it would be easy for a future patch to break this >>>>> by accident. >>>> >>>> Really? What likely key names would be in need of quoting? I can't >>>> imagine accepting a field name that contains punctuation or leading >>>> or trailing whitespace, for example. >>> >>> It seemed to me, in particular, that someone might use a # symbol, >>> like "# of Iterations". >>> >> >> Then the resulting XML tagname would be invalid too >> I think they would soon realise/be told that it was a bad idea. > > Hmm, you're right. Maybe we should go with your approach, then. After thinking about this further, I think I'd still like to take one more crack at fixing this without quoting absolutely everything. I argued against this feature, but we decided to take it, and it seems that one of the major arguments that is being put forward is that it will be more readable than JSON, because it will have less punctuation. While the idea of optimizing a machine-readable format for human-readability doesn't typically carry much water around here, it's really the only use case for having this particular feature at all, so, if we're not going to rip it out, ISTM we ought to respect what it's there for. I would be more than willing to agree that if one more attempt isn't sufficient to fix the problem then we'll either quote everything, or rip the whole thing out. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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
On Wed, Jun 9, 2010 at 12:58 PM, Robert Haas wrote: > On Wed, Jun 9, 2010 at 12:57 PM, Dean Rasheed > wrote: >> On 9 June 2010 17:52, Robert Haas wrote: >>> On Wed, Jun 9, 2010 at 12:25 PM, Tom Lane wrote: >>>> Robert Haas writes: >>>>> On Wed, Jun 9, 2010 at 11:14 AM, Tom Lane wrote: >>>>>> Why not? Surely we can restrict EXPLAIN's set of key names to be safe. >>>> >>>>> It seems to me that it would be easy for a future patch to break this >>>>> by accident. >>>> >>>> Really? What likely key names would be in need of quoting? I can't >>>> imagine accepting a field name that contains punctuation or leading >>>> or trailing whitespace, for example. >>> >>> It seemed to me, in particular, that someone might use a # symbol, >>> like "# of Iterations". >>> >> >> Then the resulting XML tagname would be invalid too >> I think they would soon realise/be told that it was a bad idea. > > Hmm, you're right. Maybe we should go with your approach, then. After thinking about this further, I think I'd still like to take one more crack at fixing this without quoting absolutely everything. I argued against this feature, but we decided to take it, and it seems that one of the major arguments that is being put forward is that it will be more readable than JSON, because it will have less punctuation. While the idea of optimizing a machine-readable format for human-readability doesn't typically carry much water around here, it's really the only use case for having this particular feature at all, so, if we're not going to rip it out, ISTM we ought to respect what it's there for. I would be more than willing to agree that if one more attempt isn't sufficient to fix the problem then we'll either quote everything, or rip the whole thing out. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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
On Wed, Jun 9, 2010 at 12:57 PM, Dean Rasheed wrote: > On 9 June 2010 17:52, Robert Haas wrote: >> On Wed, Jun 9, 2010 at 12:25 PM, Tom Lane wrote: >>> Robert Haas writes: >>>> On Wed, Jun 9, 2010 at 11:14 AM, Tom Lane wrote: >>>>> Why not? Surely we can restrict EXPLAIN's set of key names to be safe. >>> >>>> It seems to me that it would be easy for a future patch to break this >>>> by accident. >>> >>> Really? What likely key names would be in need of quoting? I can't >>> imagine accepting a field name that contains punctuation or leading >>> or trailing whitespace, for example. >> >> It seemed to me, in particular, that someone might use a # symbol, >> like "# of Iterations". >> > > Then the resulting XML tagname would be invalid too > I think they would soon realise/be told that it was a bad idea. Hmm, you're right. Maybe we should go with your approach, then. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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
On Wed, Jun 9, 2010 at 12:25 PM, Tom Lane wrote: > Robert Haas writes: >> On Wed, Jun 9, 2010 at 11:14 AM, Tom Lane wrote: >>> Why not? Surely we can restrict EXPLAIN's set of key names to be safe. > >> It seems to me that it would be easy for a future patch to break this >> by accident. > > Really? What likely key names would be in need of quoting? I can't > imagine accepting a field name that contains punctuation or leading > or trailing whitespace, for example. It seemed to me, in particular, that someone might use a # symbol, like "# of Iterations". Maybe I'm being paranoid. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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
On Wed, Jun 9, 2010 at 11:14 AM, Tom Lane wrote: > Robert Haas writes: >> On Wed, Jun 9, 2010 at 11:03 AM, Tom Lane wrote: >>> I still agree with Dean's original proposal: always quote the values of >>> strings. > >> I'd still rather rip the format out entirely than do that. > > I'd be on board with that too ;-) > >> Dean's >> proposal was based on the idea that it would be safe to quote only the >> values and not the keys, which is not something I care to bank on. > > Why not? Surely we can restrict EXPLAIN's set of key names to be safe. It seems to me that it would be easy for a future patch to break this by accident. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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
On Wed, Jun 9, 2010 at 11:03 AM, Tom Lane wrote: > Robert Haas writes: >> On Wed, Jun 9, 2010 at 9:35 AM, Dean Rasheed >> wrote: >>>> Does anyone care that Alias will sometimes be a string, and sometimes a >>>> number? > >> After further review, it appears to me that this change is pretty much >> required, because otherwise a string like 0xa won't be quoted. I >> might think it's OK for "123" to turn into 123, but I'm not going to >> be so happy about "0xa" turning into 10. Please test the attached >> patch. > > I still agree with Dean's original proposal: always quote the values of > strings. I'd still rather rip the format out entirely than do that. Dean's proposal was based on the idea that it would be safe to quote only the values and not the keys, which is not something I care to bank on. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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 #5475: Problem during Instalation
On Wed, Jun 9, 2010 at 10:52 AM, Dave Page wrote: > On Wed, Jun 9, 2010 at 3:50 PM, Robert Haas wrote: >> On Wed, Jun 9, 2010 at 6:09 AM, Dave Page wrote: >>> Please provide a password for the database superuser (${superaccount}) >>> and service account (${serviceaccount}). If the service account >>> already exists in Windows, you must enter the current password for the >>> account. If the account does not exist, it will be created when you >>> click 'Next'. >> >> I think that's REALLY confusing. It seems to me that asking for a >> password which might be used either to log into an existing account or >> to set the password for an account that's about to be created is not >> very user-friendly at all. And we get questions about it here >> regularly. Why not: >> >> If (account exists) >> prompt user to log into account >> else >> tell user account will be created, ask for account pw >> prompt user for db superuser pw > > Because without additional text, the user still doesn't know that > they're also setting the superuser password for the cluster. I'm suggesting that you prompt for that separately, as shown in the above pseudocode. It seems to me that conflating the postgres user account password with the database superuser password is confusing... IJWH, of course. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] Bad optimizer data for xml (WAS: xml data type implications of no =)
On Wed, Jun 9, 2010 at 1:14 AM, Tom Lane wrote: > Robert Haas writes: >> It's possible. I don't really see a reason not to add an = operator >> for XML - does anyone else? > > Yes, that was considered and rejected, IIRC. What is your definition > of equality for xml? I'd vote for !memcmp(). There can be (and probably already are) other ways to test for other kinds of equality, too, of course. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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 #5475: Problem during Instalation
On Wed, Jun 9, 2010 at 6:09 AM, Dave Page wrote: > Please provide a password for the database superuser (${superaccount}) > and service account (${serviceaccount}). If the service account > already exists in Windows, you must enter the current password for the > account. If the account does not exist, it will be created when you > click 'Next'. I think that's REALLY confusing. It seems to me that asking for a password which might be used either to log into an existing account or to set the password for an account that's about to be created is not very user-friendly at all. And we get questions about it here regularly. Why not: If (account exists) prompt user to log into account else tell user account will be created, ask for account pw prompt user for db superuser pw -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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
On Wed, Jun 9, 2010 at 9:35 AM, Dean Rasheed wrote: >>> Does anyone care that Alias will sometimes be a string, and sometimes a >>> number? >> I guess we could do this by (a) conditionalizing the YAML case in >> ExplainProperty() in the same way that the JSON case is currently >> conditionalized, and (b) changing the first if statement in >> escape_yaml() to set needs_quoting = true unless the first character >> is alphabetic or an underscore. > Yes, I think that would do it. After further review, it appears to me that this change is pretty much required, because otherwise a string like 0xa won't be quoted. I might think it's OK for "123" to turn into 123, but I'm not going to be so happy about "0xa" turning into 10. Please test the attached patch. >> By the way, can I ask why you're not just using the JSON format for >> this? I mean, I'm glad you are, because it exposed a bug that we got >> fixed before release, but it seems a little masochistic...! > > Actually I doubt that I will use this feature at all! I only use > EXPLAIN from psql, and usually I'm the only one who needs to read it, > so the TEXT format will remain my preferred option. > > I was just doing some random beta testing, working through the list of > cool new features. Quick, somebody give this man a cigar! -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company yet_another_yaml_fix.patch Description: Binary data -- 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
On Wed, Jun 9, 2010 at 8:46 AM, Dean Rasheed wrote: > On 9 June 2010 03:48, Robert Haas wrote: >> please test. > > Well your patch definitely fixes my original bug, and AFAICT always > produces valid YAML output now. I've only found one case where a > particular parser has difficulty parsing the output, and you'd have to > write a pretty perverse query to hit that case. Excellent. > So that just leaves this sort of thing: > > explain (format yaml) select * from foo as "123"; > QUERY PLAN > - > - Plan: + > Node Type: Seq Scan+ > Relation Name: foo + > Alias: 123 + > Startup Cost: 0.00 + > Total Cost: 23.10 + > Plan Rows: 1310 + > Plan Width: 32 > (1 row) > > Does anyone care that Alias will sometimes be a string, and sometimes a > number? > > ITSM that, since postgresql knows that it's a string, it ought to > output something that parsers can unambiguously treat as a string too. > > But this is also a pretty obscure case that probably only someone > deliberately trying to be awkward would do (which is me, with my > tester hat on :-)). I guess we could do this by (a) conditionalizing the YAML case in ExplainProperty() in the same way that the JSON case is currently conditionalized, and (b) changing the first if statement in escape_yaml() to set needs_quoting = true unless the first character is alphabetic or an underscore. By the way, can I ask why you're not just using the JSON format for this? I mean, I'm glad you are, because it exposed a bug that we got fixed before release, but it seems a little masochistic...! -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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
On Wed, Jun 9, 2010 at 7:23 AM, Dean Rasheed wrote: > On 9 June 2010 12:07, Robert Haas wrote: >> On Wed, Jun 9, 2010 at 2:58 AM, Dean Rasheed >> wrote: >>> On 9 June 2010 03:48, Robert Haas wrote: >>>> Er, I should also say, thanks for the report, and please test. I am >>>> definitely not an expert on YAML. >>>> >>> >>> I'm not an expert on YAML either, but I don't think this works (at >>> least it breaks against the online YAML parser here: >>> http://yaml-online-parser.appspot.com/). If the string starts with a >>> ".", then it tries to treat it as a floating point number and baulks >>> if the rest of the string isn't a valid number. >> >> Really? I enter: >> >> - foo >> - bar >> - .baz >> >> And it produces this JSON: >> >> [ >> "foo", >> "bar", >> ".baz" >> ] >> >> That looks OK to me. >> > > Ah, OK I didn't test those cases properly before composing my email. > It's actually only a "." on its own that it can't parse. Well, at first blush, that looks like it might be a bug in the parser. I don't see anything in the spec to indicate that that case should be treated specially. > My comment about numbers still applies though. The following are > different values: > > - just: write some > - yaml: > - 123 > - "123" Well, you can't have abc mean the same thing as "abc" but then complain that 123 isn't equivalent to "123"... This format is really a pain to work with. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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
On Wed, Jun 9, 2010 at 2:58 AM, Dean Rasheed wrote: > On 9 June 2010 03:48, Robert Haas wrote: >> Er, I should also say, thanks for the report, and please test. I am >> definitely not an expert on YAML. >> > > I'm not an expert on YAML either, but I don't think this works (at > least it breaks against the online YAML parser here: > http://yaml-online-parser.appspot.com/). If the string starts with a > ".", then it tries to treat it as a floating point number and baulks > if the rest of the string isn't a valid number. Really? I enter: - foo - bar - .baz And it produces this JSON: [ "foo", "bar", ".baz" ] That looks OK to me. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] Bad optimizer data for xml (WAS: xml data type implications of no =)
On Thu, Jun 3, 2010 at 7:16 PM, Mark Kirkwood wrote: > Maybe I gave this guy a bad title - is it a concern that the 'width' > estimate is so far off for xml datatypes (because of no = op)? It seemed to > me that this could result in some bad plan choices (e.g in subqueries etc). It's possible. I don't really see a reason not to add an = operator for XML - does anyone else? It would need to be done by updating src/include/catalog/pg_*.h, rather than via SQL, of course. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] Re: [BUGS] Reinicialização - Pg_Listener
2010/5/26 MarceloG : > Olá pessoal, > no meu aplicativo, ao invés de tabelas temporárias, utilizo o pg_listener > para verificar e impedir que o mesmo usuário acesse o sistema > simultaneamente. > Eu disparo um "notify usuario+código" e, a cada acesso, leio a tabela/ > catálogo pg_listener para saber se o usuário está ou não logado. > Assim, quando a conexão for encerrada, todos os "notify" da conexão são > automaticamente deletados. > Esse procedimento vai ser modificado. > Todavia, recentemente, em virtude de queda de energia, o servidor deixou de > funcionar de forma anormal. > Assim, os registros na tabela/ catálogo pg_listener foram mantidos, > obrigando um procedimento manual de deleção dos registros então existentes. > Assim, acho que em todas as inicializações do servidor, a tabela/ catálogo > pg_listener deve ser "limpa" ou seja, deletados todos os registros > existentes. This is an English-language mailing list, but you could try here: https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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 #5473: problema para reinstalar postgresql
2010/5/25 carolina : > > The following bug has been logged online: > > Bug reference: 5473 > Logged by: carolina > Email address: caro.herrer...@hotmail.com > PostgreSQL version: postgresql 8.4 > Operating system: windows xp > Description: problema para reinstalar postgresql > Details: > > Hola..tengo el siquiente problema, por razones tecnicas tuve que desinstalar > postgresql de mi maquina, al final de la desinstalacion me envio un mensaje > donde me decia que ni el usuario ni la base se habian eliminado. Cuando > Quise volver a instalarlo, se instalo pero al tratar de ejecutarlo me sale > el siguiente error: Server doesn't listen > The server doesn't accept connections: the connection library reports > > could not connect to server: Connection refused (0x274D/10061) Is the > server running on host "127.0.0.1" and accepting TCP/IP connections on port > > 5432? > > Por lo que me fui a archivos de programa y borre una carpeta que estaba de > nombre postgresql y tambien borre el usuario postgres, sin embargo nada > funciona, solo se instala el pgadminIII pero no puedo crear una base de > datos porque cada vez q lo intento me sale el mismo error. > > Tambien entre por modo a prueba de fallos y busque todo lo que tuviera > nombre postgres y lo elimine y volvi a instalar pero nada me funciona..que > puedo hacer?? Por favor pregunta aqui: http://archives.postgresql.org/pgsql-es-ayuda/ -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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 #5475: Problem during Instalation
On Wed, May 26, 2010 at 9:48 AM, Joel Henrique wrote: > > The following bug has been logged online: > > Bug reference: 5475 > Logged by: Joel Henrique > Email address: j...@cefet-al.br > PostgreSQL version: 8.4.4-1 > Operating system: Windows 2003 Server > Description: Problem during Instalation > Details: > > When I try to install postgres it asks for a password. > It says that if the service already exists I should put the current > password, otherwise a service will be created with new password. > > I've neves installed postgres before. What kind of password is that? I can't > install postgres here. I feel like we've had this question a few times before, and answered it, but I'm not a Windows guy and can't remember the answer. Can we add an FAQ entry for this, or something? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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 #5492: Query performs slowly and sequence corrupted
On Mon, Jun 7, 2010 at 5:33 PM, Greg Stark wrote: > On Mon, Jun 7, 2010 at 5:36 PM, Piergiorgio Buongiovanni > wrote: >> I reused the previous command to re-set the sequence value to the right one, >> but I see that the START value is now 59100. I reused the previous command >> another time and the START value is now 30440. >> >> I think this is a bug. I have a lot of problems with this sequence. > > Sequences wouldn't directly affect retrieval times. But one way you > could get both of these symptoms is by having an application which > inserts many rows but aborts and rolls back the inserts without > committing. Perhaps a large copy which is interrupted. That would fill > the table with garbage dead records which could slow down retrieval > depending on the access method and also increase the sequence value. If this is what happened, CLUSTER on the table might be enough to fix the problem. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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
On Tue, Jun 8, 2010 at 10:47 PM, Robert Haas wrote: > On Mon, Jun 7, 2010 at 4:14 AM, Dean Rasheed wrote: >> Testing 9.0 beta, I found that EXPLAINing certain queries in YAML >> format will produce invalid YAML, for example: >> >> explain (format yaml) select * from foo where str_val = 'a: b'; >> >> The problem in this case is that a colon followed by whitespace is not >> allowed in an unquoted plain YAML string because a parser would >> interpret it as the start of a map. >> >> So the current code in escape_yaml() is inadequate for producing valid >> YAML. I think it would have to also consider at least the following >> characters as special "-" ":" "[" "]" "{" "}" "," "\"" "'" >> "|" "*" "&". Technically, it would also need to trap empty strings, >> and strings with leading or trailing whitespace. >> >> Making escape_yaml() completely bulletproof with this approach would >> be quite difficult, and (IMO) not worth the effort, especially given >> that an important requirement is that the output be machine readable, >> and in my experience YAML parsers are often far from perfect. >> >> I would therefore argue for simply calling escape_json() to produce >> double quoted output for all string values, and only have numeric >> values unquoted. This is not really any less human readable, and is >> far more machine readable. >> >> Patch attached. > > I've committed a patch which I think will address this issue without > uglifying the output quite so much. Also, I didn't like the idea of > not applying escaping to both the keys and values, even though we > think we'll never have a key that requires escaping. With this > approach, that change isn't needed. Er, I should also say, thanks for the report, and please test. I am definitely not an expert on YAML. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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
On Mon, Jun 7, 2010 at 4:14 AM, Dean Rasheed wrote: > Testing 9.0 beta, I found that EXPLAINing certain queries in YAML > format will produce invalid YAML, for example: > > explain (format yaml) select * from foo where str_val = 'a: b'; > > The problem in this case is that a colon followed by whitespace is not > allowed in an unquoted plain YAML string because a parser would > interpret it as the start of a map. > > So the current code in escape_yaml() is inadequate for producing valid > YAML. I think it would have to also consider at least the following > characters as special "-" ":" "[" "]" "{" "}" "," "\"" "'" > "|" "*" "&". Technically, it would also need to trap empty strings, > and strings with leading or trailing whitespace. > > Making escape_yaml() completely bulletproof with this approach would > be quite difficult, and (IMO) not worth the effort, especially given > that an important requirement is that the output be machine readable, > and in my experience YAML parsers are often far from perfect. > > I would therefore argue for simply calling escape_json() to produce > double quoted output for all string values, and only have numeric > values unquoted. This is not really any less human readable, and is > far more machine readable. > > Patch attached. I've committed a patch which I think will address this issue without uglifying the output quite so much. Also, I didn't like the idea of not applying escaping to both the keys and values, even though we think we'll never have a key that requires escaping. With this approach, that change isn't needed. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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 #5490: INSERT doesn't force cast from text to timestamp
On Mon, Jun 7, 2010 at 10:30 AM, Andy Balholm wrote: > Craig Ringer wrote: >> showing that your issue isn't actually with DISTINCT at all, but with Pg's >> unwillingness to *implicitly* cast a value of explict text type to another >> type. > > Is there a way to make values of "undefined" type pass through the SELECT > DISTINCT filter (getting checked for uniqueness) but remain "undefined" if > all the values supplied for the column are "undefined"? I don't know if the > internal design of SELECT DISTINCT and the type system would allow for this, > but if it would, it would take care of Farid's problem without introducing > implicit type casts. The issue isn't what's technically possible, but what's least likely to lead to surprising behavior. This whole thread is basically about whether implicit casts to and from text are a good idea or not. The OP obviously thinks they are, and everyone else (whether they agree with the behavior or not) is trying to explain the reasons why we don't have them. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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
On Mon, Jun 7, 2010 at 10:37 AM, Greg Sabino Mullane wrote: > Tom Lane wrote: > I don't think the above would be particularly hard to implement myself, > but if it becomes a really big deal, we can certainly punt by simply > quoting anything containing an indicator (the special characters above). > It will still be 100% valid YAML, just with some excess quoting for the > very rare case when a value contains one of the special characters. Since you're the main advocate of this feature, I think you should implement it rather than leaving it to Tom or I. The reason why I was initially skeptical of adding a YAML output format is that JSON is a subset of YAML. Therefore, the JSON output format ought to be perfectly sufficient for anyone using a YAML parser. If it's not, that's because their YAML processor is broken, and they should get a new one, or because the YAML spec is defective. The YAML format got voted in by consensus because people thought that it would also make a nice alternative to the text format for human readable output. I don't believe that (it uses way too much vertical space) but even if you accept the argument, the more we make the YAML format look like the JSON format, the less water that argument holds. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs