[BUGS] BUG #5246: Misleading/inconsistent SQLSTATE behavior
The following bug has been logged online: Bug reference: 5246 Logged by: Chris Travers Email address: chris.trav...@gmail.com PostgreSQL version: 8.1.18 Operating system: Fedora Linux 12 Description:Misleading/inconsistent SQLSTATE behavior Details: Hi all; I am noticing that that a failed database connection results in an unusable SQLSTATE in libpq, and a very different SQLSTATE than the backend registers. For example, if a connection fails due to a database not found, the backend registers 3D000 as a SQL state, but the front-end registers 25P01. If a login fails, the back-end registers 28000 but the front-end registers 25P01 again. 25P01 is "no_active_sql_transaction" and provides little information to the programmer as to how to handle the error. I may be missing something but the error looks to be entirely meaningless as it relates to a failed connection attempt as I, as a programmer, am loathe to trust that a generic transaction-related status message would be only used to track connection problems. >From a programming perspective, it would be ideal for the same SQLSTATE triggered on the back-end to be available to the front-end.This leads to a number of very substandard workarounds. This might not be addressable within stable versions, but it would be very nice to see it fixed. Best Wishes, Chris Travers -- 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 #5246: Misleading/inconsistent SQLSTATE behavior
On Wed, Dec 16, 2009 at 12:35 PM, Kevin Grittner wrote: > "Chris Travers" wrote: > >> I am noticing that that a failed database connection results in an >> unusable SQLSTATE in libpq, and a very different SQLSTATE than the >> backend registers. > > Well, if the client fails to connect to the server, I'm not sure how > the server could communicate its SQLSTATE to the client, in order to > force them to match. It does send an error message. Currently I end up parsing that error message and checking to see if a connection is active. Unfortunately this becomes annoying where the locale of the PostgreSQL instance could change the messages received. It would be nice to have at least an option for software to pick up a code as to why a connection request fails instead of having to try to deal with feedback intended for a human, but I would settle for at least a SQLSTATE that indicated a connection problem instead of a transaction issue (08001 or 08004) since these would be a lot less ambiguous on the program interface side. Best Wishes, Chris Travers -- 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 #5246: Misleading/inconsistent SQLSTATE behavior
trying again. Sorry for the duplicate. On Wed, Dec 16, 2009 at 1:12 PM, Tom Lane wrote: > "Chris Travers" writes: >> I am noticing that that a failed database connection results in an unusable >> SQLSTATE in libpq, and a very different SQLSTATE than the backend >> registers. > >> For example, if a connection fails due to a database not found, the backend >> registers 3D000 as a SQL state, but the front-end registers 25P01. If a >> login fails, the back-end registers 28000 but the front-end registers 25P01 >> again. > > Exactly what "frontend" are you talking about here? Because what this > sounds like to me is a client-side programming error. It's certainly > not the backend's fault, and I doubt it is libpq's either. I am using DBD::Pg. I asked about it on #postgresql and was told this was the SQLSTATE code passed up from libpq (by the author of DBD::Pg). Several other folks there seemed to concur. It is certainly not a backend error. The back-end logs correct errors when logging is set to verbose. Best Wishes, Chris Travers -- 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 #5246: Misleading/inconsistent SQLSTATE behavior
On Wed, Dec 16, 2009 at 1:39 PM, Tom Lane wrote: > Chris Travers writes: >> trying again. Sorry for the duplicate. >> On Wed, Dec 16, 2009 at 1:12 PM, Tom Lane wrote: >>> Exactly what "frontend" are you talking about here? Because what this >>> sounds like to me is a client-side programming error. It's certainly >>> not the backend's fault, and I doubt it is libpq's either. > >> I am using DBD::Pg. > >> I asked about it on #postgresql and was told this was the SQLSTATE code >> passed up from libpq (by the author of DBD::Pg). Several other folks >> there seemed to concur. > > Could you provide a self-contained test case? And what versions of > DBD::Pg etc are we talking about? Just to weed out possibilities of DBD::Pg behavior causing this, I am going to try to create a straight C test case. If I can't I will end the perl scripts I was using to test. Best Wishes, Chris Travers > > regards, tom lane > -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5246: Misleading/inconsistent SQLSTATE behavior
Hi Tom and everyone else; After significant additional research this is what I have turned up: 1) The problem was a problem in DBD::Pg which didn't quite succeed in setting the connection state to 08006. I have submitted a patch for that to the DBD::Pg project. 2) As of 8.1, tshark shows that the server does send the SQLSTATE to the client regarding why the login fails (for example 3D000 in the case of bad db name). Libpq as far as I can tell (from reading the code) doesn't do anything with this code. Certainly there seems to be no exposure of the SQLSTATE to anything as it relates to a failed connection attempt. I could be missing something because I am not extremely familiar with the libpq codebase, but it seems that the value is just discarded. Are there any plans to expose the SQLSTATE from a failed connection attempt upwards through the library? (I would be happy to try to write a patch but you probably don't want my C code in your library.) Best Wishes, Chris Travers -- 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 #5246: Misleading/inconsistent SQLSTATE behavior
Just to be clear (before I consider attempting a patch maybe I can hand off to someone else to double-check)... On Wed, Dec 16, 2009 at 5:31 PM, Tom Lane wrote: > Yeah. The problem is that the only infrastructure libpq has for returning > individual error message fields (like the SQLSTATE) is associated with a > PGresult, and there's no PGresult for a connection failure. I see no > easy way to fix that without incompatible changes in libpq's API. Looking through the code it looks like this is for two reasons: 1) The current API assumes a PGResult rather than a connection being handed in. 2) There is no place in the struct for a connection to handle the message fields. > > This is related to the fact that errors detected internally in libpq > generally lack SQLSTATEs. Part of the reason that fixing that has been > so low-priority is that in many cases there's no existing API whereby > they could be returned anyhow. It's been on the TODO list since 7.4, > but nobody has cared to tackle it. I do a lot with SQLSTATEs in my Perl code and having access to this would be really quite helpful. (More info below but don't want to crowd out my questions.) It looks like this could be added without a disruption to programmer interfaces, but it seems like any major change in this area would create binary compatibility issues (i.e. require recompile of linked software). Is this correct in what you mean by API incompatibility? A quick review suggests to me it shouldn't be too bad to add this, but at the same time my C code is not the best out there. I might still be willing to give it a shot. As for more info: I use the SQLSTATE field quite heavily for error handling and while it isn't always sufficient by itself, it is quite helpful in detecting errors and providing more helpful messages to end users. The application I am working on at the moment uses database roles as application roles and enforces security in the database. On login, the user has to enter username, password, and database name in order to access the application. The problem I was running into is that if the user enters a non-existant database, the program would prompt for username/password instead of letting them know the database was wrong. My workaround at the moment is to check the error message against a configurable value to see if it represents a missing database. It sucks because it means that foreign locale users must go through extra configuration steps. If I had the SQLSTATE data it would be easy to set up so that wouldn't be needed. -- 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 #5246: Misleading/inconsistent SQLSTATE behavior
On Wed, Dec 16, 2009 at 7:07 PM, Tom Lane wrote: > Chris Travers writes: >> It looks like this could be added without a disruption to programmer >> interfaces, but it seems like any major change in this area would >> create binary compatibility issues (i.e. require recompile of linked >> software). Is this correct in what you mean by API incompatibility? > > No, I'm concerned about the programmer interface at the moment. What > have you got in mind? My thinking (rather hackish) was to do as follows: (Rough version) 1) Add an errFields member to pg_conn similar to what exists in pg_result 2) create a "dummy" pgResult instance inside the connection object to track the messages (thus use internally any functions to store errorfields there) 3) Store error message fields using the errFields section of the pgResult using whatever standard methods currently used for pgResult structs. 4) Copy over to connection struct 5) Create a new function to expose this to the application. Maybe named PQresultErrorField. Then test this and make sure it works Then refine as follows: 1) Refactor any errfields/Alloc-type functions as possible to get rid of the necessity to go through a dummy result struct. Remove the dummy result struct and copy fields manually. Maybe refactor the field checking to centralize it between the result and connection functions? Then test this to make sure it works again. Then see if I can get someone else to further comment on/review/refine the patch. > >> ... The problem I was running into is that if the >> user enters a non-existant database, the program would prompt for >> username/password instead of letting them know the database was wrong. >> My workaround at the moment is to check the error message against a >> configurable value to see if it represents a missing database. It >> sucks because it means that foreign locale users must go through extra >> configuration steps. If I had the SQLSTATE data it would be easy to >> set up so that wouldn't be needed. > > We do have a workaround for distinguishing "password required" from > other errors without any locale-specific tests. It is surely a crock, > but you'd want to use that in the near term anyway. Any real fix here > could not appear before 8.5 at the earliest. What sort of current workarounds are there? Best Wishes, Chris Travers > > regards, tom lane > -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5281: Timestamp fields not inserting from 8.3 to 8.4
On Fri, Jan 15, 2010 at 9:38 AM, Kevin Grittner wrote: > "Jodi Escalante" wrote: > >> INSERT INTO assessment (id, created, taken, current_weight, note, >> assessment_type, stay_id, contact_id, estimated_discharge_date, >> cond_chf, cond_pulm_heart, cond_endocrine_other, cond_skin_temp, >> ) VALUES ( 50, 2008-01-11 15:06:40.257000 -07:00:00, >> 2008-01-11 00:00:00.00 -07:00:00, 2000.0, NULL, Initial, >> 3452, 2147, NULL, N, N, N, N) > > Without quotes you've got the calculation (2008 minus 1 minus 11) > which equals the integer 1996. It doesn't know what to make of the > number which comes next. Try something like TIMESTAMP WITH TIME > ZONE '2008-01-11 15:06:40.257000 -0700'. Similar issues seem to > exist with most of your other literals. Just as a note this might have appeared to work in pre-8.3 but may have done the wrong thing. Definitely quote your literals. Best Wishes, Chris Travers -- 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 #5288: Restoring a 7.4.5 -Fc dump using -j 2 segfaults (patch included)
Just weighing in here. On Tue, Jan 19, 2010 at 9:15 AM, Tom Lane wrote: > It doesn't seem worth it > to try to support parallel restore from nearly-obsolete versions, and > I suspect that we couldn't do it even if we tried --- the reason the > representation got changed is that the old way simply didn't work for > any significant use of the dependency info. Just ignoring the > dependencies, as your patch effectively proposes, is going to lead to > restore failures or worse. Just to clarify, the only part that would not be supported would be the parallel part, right? Best Wishes, Chris Travers -- 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 #5306: psql: symbol lookup error: /usr/lib/libreadline.so.5: undefined symbol: BC
On Tue, Feb 2, 2010 at 7:38 AM, Tom Lane wrote: > "" writes: >> I made an installation of Postgres on a redhat machine after compiling >> sources on a redhat machine. Everything is OK. >> When i install Postgres on a debian machine, i get an error when using psql >> : "psql: symbol lookup error: /usr/lib/libreadline.so.5: undefined symbol: >> BC" > >> If i compile sources on debian-machine, psql does not work on a redhat >> machine. Same error message. > > This is not a bug. They're two different platforms and you shouldn't > expect compiled executables to be portable between them. > Just as a note (and clarification): Sometimes (very occasionally) I have had to move existing Pg binaries across machines (and even different versions of the same Linux Distro: Fedora). Occasionally I have done this when I have to make absolutely certain that file-level backups restore on a different machine. However, it is not a common task. Very often when I have done this, I have found that I also have to copy over the Readline .so files from the original machine. First, this isn't a PostgreSQL problem, and it certainly isn't a Pg bug. It has to do with changes to the Readline library and the way linking works on Linux systems. In general, the word of advice is that unless you know what you are doing, don't try this at home. Best Wishes, Chris Travers -- 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 #5308: How to disable Case sensitivity on naming identifiers
On Tue, Feb 2, 2010 at 12:11 PM, Kelly SACAULT wrote: > > The following bug has been logged online: > > Bug reference: 5308 > Logged by: Kelly SACAULT > Email address: kelly.saca...@gmail.com > PostgreSQL version: 8.4.2 > Operating system: Ubuntu 9.10 > Description: How to disable Case sensitivity on naming identifiers > Details: > > I have installed Postgresql using Ubuntu Synaptic. > > In the contrary of what is stated in the official manual, I have to write > case sensitive SQL statements in my postgresql connexion. > > What parameter do I have to change in the postgresaql configuration ? I have > spent many hours in studying the parameters, the faqs and the forums. I have > found nothing to make my SQL statements case-insensitive. > I want to be able to execute successfully such stmts: > > SELECT col1 FROM myTABLE > > SELECT Col1 FROM myTable > > please, may you help ? I thought PgSQL was case insensitive by default and that both those would be executed as: SELECT col1 FROM mytable; If you are seeing otherwise in the manual, can you provide a section? Best Wishes, Chris Travers > > Kelly > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs > -- 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 #5308: How to disable Case sensitivity on naming identifiers
On Wed, Feb 3, 2010 at 11:30 AM, Robert Haas wrote: > Quoting an identifier also makes it case-sensitive, whereas unquoted > names are always folded to lower case. For example, the identifiers > FOO, foo, and "foo" are considered the same by PostgreSQL, but "Foo" > and "FOO" are different from these three and each other. (The folding > of unquoted names to lower case in PostgreSQL is incompatible with the > SQL standard, which says that unquoted names should be folded to upper > case. Thus, foo should be equivalent to "FOO" not "foo" according to > the standard. If you want to write portable applications you are > advised to always quote a particular name or never quote it.) > > You may not like the current behavior (that's up to you), but I don't > believe there's any problem with how it's documented. There might actually be two reasons to document the folding-to-lower though: 1) The SQL standards mandate folding to upper instead, so this is a deviation from the standard (a good one IMO), but it might be useful to highlight it for the reader esp. since it will hit those trying to support multiple databases. 2) While I doubt that too many people get stuck on that (I did for all of 30 seconds), more clarity might be helpful for individuals just starting to pick up PostgreSQL. I don't like the proposed wording though. I would suggest something more like: "Unless double-quoted, all identifiers are folded to lower case, making comparisons generally case insensitive. The SQL standard mandates folding identifiers to upper case, but the consensus among the PostgreSQL development team is that folding to lower case is better. If double-quotes are not used ever, or are used consistently throughout the application, this poses no compatibility problems in terms of SQL queries." -- 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 #5308: How to disable Case sensitivity on naming identifiers
On Wed, Feb 3, 2010 at 11:37 AM, Tom Lane wrote: > Kelly SACAULT writes: >> Here is what I read from the officiel manual : >> http://www.postgresql.org/docs/8.0/static/sql-syntax.html >> stating that : >> ".. Identifier and key word names are case insensitive..." > > You need to not stop reading at that point, but continue on to the part > that explains how quoted identifiers work. The only issue here (not a major one, maybe not even worth fixing, but probably worth mentioning) is that the discussion of case folding is more than a screen away and that it isn't immediately clear that there is more discussion. It is probably understandable that some people would miss it (I did, a moment ago, until you mentioned it). A simple (see below) might be a good idea. Then again it might be a good thing for someone else (not generally engrossed in development on the project) to submit a patch for :-) Best Wishes, Chris Travers -- 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 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. Best Wishes, Chris Travers -- 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 #5331: Integer overflow in tmie counter
On Wed, Feb 17, 2010 at 5:59 AM, Euler Taveira de Oliveira wrote: > Kajetan Abt escreveu: >> Amusingly, the counter on the lower right of the SQL-input window sometimes >> shows negative numbers counting up, probably some sort over overflow. No >> harm done though. >> > If you don't provide more details (test case) it's hard to say if it's a bug > or not. Is that a pg-Admin bug report? If so, consider sending it to the email lists of that project. Best Wishes, Chris Travers -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Bug in triggers
On Fri, Mar 5, 2010 at 2:32 PM, Tom Lane wrote: > Robert Haas writes: >>> On Wed, Mar 3, 2010 at 9:53 PM, Robert Haas wrote: >>> It does seem weird that assigning NEW to var changes the value; I'm >>> not sure why that happens. Is that what you're asking about? > >> Anyone else have an opinion on whether this is a bug? > > It's arguably a bug, but since we lack consensus on whether NULL and > ROW(NULL,NULL,...) are the same thing, it's difficult to make a > bulletproof case either way. In any case nothing is likely to get done > about it in the near term because it's wired into plpgsql's > implementation. Changing from row to record representation of such > variables is possible but would probably have side effects, ie, it would > create new compatibility issues of unknown seriousness. I'm not too > optimistic about the performance implications either. I don't know if it is a bug. Different textual representations could easily happen due to intermediate conversions of datatypes For example: I wouldn't expect timestamp::date::text to equal timestamp::text. Textual representations are not necessarily consistent. I guess a better question for Oleg might be: "Why is it important to you to get this fixed? What are you trying to do that you can't do without fixing this?" Best Wishes, Chris Travers -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Bug in triggers
Accidentally replied to Tom directly. Sending to the list now. On Sun, Mar 7, 2010 at 9:08 AM, Tom Lane wrote: > Robert Haas writes: >> On Fri, Mar 5, 2010 at 5:32 PM, Tom Lane wrote: >>> It's arguably a bug, but since we lack consensus on whether NULL and >>> ROW(NULL,NULL,...) are the same thing, it's difficult to make a >>> bulletproof case either way. > >> Have we or can we somehow document why this happens? > > The reason it happens is that the assignment target is a "row" variable, > meaning that it doesn't have concrete existence as a tuple but is just > an alias for a list of scalar variables. So there is no way for it to > represent an atomic NULL; setting each of the individual scalars to NULL > is possible but the result acts more like ROW(NULL,NULL,...). I am going to offer a slightly different perspective here. Oleg is putting casting both the record and row to text before comparing them. I personally wouldn't necessarily expect this to be safe across datatypes. Regardless of whether NULL is the same as ROW(NULL), it seems that it is inherently questionable to rely on textual representations of different datatypes in such comparisons. > > I'm not sure about documenting that. It seems like an implementation > detail. If we had consensus that the two cases either should or should > not be distinguishable, we could work towards making that happen; but > lacking such consensus I'm hesitant to touch it at all. I am not sure about that. If we have a ROW variable, how do we know, when we cast it to text, whether or not a given NULL is really a single NULL or rather a ROW(NULL,NULL) variable? Absent such information, how can you be sure that textual representations will be equal? It seems to me the fundamental issue here (which might be worth documenting) is that NEW is not currently a tuple, so textual representations of NEW and the tuple cannot be guaranteed to be identical (because the amount of information in the record is greater than in the row). This seems to be separate from the question of whether ROW(NULL...) and NULL are the same from a row comparison viewpoint. Hope this adds something to the discussion. Best Wishes, Chris Travers -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Bug in triggers
On Tue, Mar 9, 2010 at 7:31 AM, Pavel Stehule wrote: > 2010/3/9 Tom Lane : >> Robert Haas writes: >>> What seems odd to me is that NEW is apparently some other kind of >>> thing that is not the same kind of thing as the row variable. >> >> NEW is a record variable, not a row variable. In this context that's >> sensible because its actual rowtype is unspecified by the function text. >> The implications for row-null handling aren't obvious though :-( >> > > is it necessary definition there? This is defined well from context. > I am assuming that Tom's previous objections may have to do with C-language triggers as well but I couldn't tell from reading the docs. This may because I am no C-guru. I think this behavior is unexpected, but not a bug. The best fix is documenting the datatype better. Something like adding a paragraph to chapter 38.9 just above the examples (going off the 8.4 docs): Please note, NEW and OLD records are not guaranteed to follow the full internal representation of the tuple in question. In some cases (such as casting to text) this can create subtle differences which make comparisons problematic. In some cases you may need to properly cast NEW and OLD prior to making comparisons. Best Wishes, Chris Travers -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Bug in triggers
On Wed, Mar 10, 2010 at 8:20 AM, Robert Haas wrote: > On Tue, Mar 9, 2010 at 11:02 AM, Tom Lane wrote: >> We may need to document it, but not like that; it's (a) incorrect and >> (b) unhelpful to the reader, who is left without any clear idea of what >> to avoid. I think that the real issue here doesn't have anything to do >> with NEW/OLD as such, but is related to the representational difference >> between record and row variables. > > I agree. That's precisely what I'm confused about. > - Show quoted text - On Wed, Mar 10, 2010 at 8:20 AM, Robert Haas wrote: > On Tue, Mar 9, 2010 at 11:02 AM, Tom Lane wrote: >> We may need to document it, but not like that; it's (a) incorrect and >> (b) unhelpful to the reader, who is left without any clear idea of what >> to avoid. I think that the real issue here doesn't have anything to do >> with NEW/OLD as such, but is related to the representational difference >> between record and row variables. > > I agree. That's precisely what I'm confused about. Additionally, plpgsql uses "record" seemingly to refer to row variables, so pointing folks to this conversation may not necessarily clear up confusion -- 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] what can i do
On Wed, Mar 17, 2010 at 12:37 PM, Kevin Grittner wrote: > vladislav DONCHEV wrote: > >> Hello guys , i have a problem :( I had a postgreSQL before some >> days but i delete it , and now i cant install it , cause there are >> 2 message at the end of install when it making account or >> something like that > > You haven't told us what operating system you're using, what version > of PostgreSQL, how you're trying to install it, or what the messages > are. He is on Windows (the run/cmd reference gives that away). I agree this doesn't sound like a bug, though. Here are a few questions though to say for sure: 1) This is Windows, right? Which version? 2) You say you "deleted" it. How? What exactly did you do to delete it? 3) Can you try again and copy the full error messages here? Best Wishes, Chris Travers -- 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] dividing money by money
Forgot to send to the list On Tue, Mar 30, 2010 at 8:25 AM, Kevin Grittner wrote: > John R Pierce wrote: >> Boszormenyi Zoltan wrote: > >>> But then any operator between two money values would >>> only work if both values have the same currency. > > That sounds like a sane limitation. > >> and, are there still any currenccies like old style UK where >> the subunits aren't 100ths? schillings or whatever > > I'm not sure if you're arguing for or against the database type > knowing how to divide those to get a percentage, versus putting the > onus on the application programmer. Where does it make the most > sense to you to put such logic? With due respect, this sort of thing is rather difficult to get right all at once. I would suggest at some point having a modified MONEY or maybe to avoid conflicts let's call it a CURRENCY datatype on Pg-foundry where we can experiment and get these details right. I am thinking of doing a rough draft in SQL and PLPGSQL so that someone can convert to C once everything works properly :-). If folks are interested, I might make a simple approximation of this that would require 8.4 or higher. It might come in handy for LedgerSMB too. Best Wishes, Chris Travers -- 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] dividing money by money
On Tue, Mar 30, 2010 at 12:22 PM, Kevin Grittner wrote: > [Did you mean to take this off-list, or was that accidental?] Accidental. > > Chris Travers wrote: > >> With due respect, this sort of thing is rather difficult to get >> right all at once. > > Division of two fixed-point numbers we already know how to add, or > the whole suite of all features one might possibly want in a > monetary data type? (I get the feeling that some of the posts on > this thread involve a straw man going down a slippery slope ;-) Ok. Here is my application: I write a multi-currency accounting program backed by PostgreSQL. After 1.3 is released (2Q this year), we expect to be doing a full redesign. What I am thinking about is having a custom data type, something like: CREATE DOMAIN curr VARCHAR(3); CREATE TYPE monetary AS (amount NUMERIC, currency CURR, multiplier NUMERIC); This reduces into two basic components: a value (amount * multiplier) and a currency identifier (USD, etc). One could also then store monetary[] arrays for addressing specific denomination storage. I.e. "When closing the till we had 26 pennies, 53 nickles, 12 quarters, 25 $1 bills, 35 $5 bills, 15 $10 bills, and 5 $20 bills." Then we can allow NUMERIC arithmetic on monetary amounts provided that the CURR field is the same. We could also store things like the cash counted from a till at the end of the day by denomination. One could have easy monetary::numeric casts as well. Anyway, that's my basic thinking. One could further add currency conversion tables to an application if necessary. Just thinking about the more general problem and how things could be handled more gracefully... Best Wishes, Chris Travers -- 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] dividing money by money
On Fri, Apr 2, 2010 at 9:51 AM, Dimitri Fontaine wrote: >> One could also then store monetary[] arrays for addressing specific >> denomination storage. I.e. "When closing the till we had 26 pennies, >> 53 nickles, 12 quarters, 25 $1 bills, 35 $5 bills, 15 $10 bills, and 5 >> $20 bills." >> >> Then we can allow NUMERIC arithmetic on monetary amounts provided that >> the CURR field is the same. We could also store things like the cash >> counted from a till at the end of the day by denomination. One could >> have easy monetary::numeric casts as well. > > Sounds a good starting point, but it sounds like we'll have to think > about it to see how it survive a more detailed approach. Sure. See below. > >> Anyway, that's my basic thinking. One could further add currency >> conversion tables to an application if necessary. > > That's where it become interesting. Finding a nice way to solve the > problem of more than one currency in the same table, with dated > (timestamped?) conversion rates that are possibly unknown at INSERT > time… Well, you have another problem (this may be wandering far afield from the original question but here it goes): Suppose I live in Canada and I have two checking accounts for my business, one in CAD and one in USD. In essence I have to account for a floating balance of a foreign currency. Consequently, I don't think you can just suggest "convert at insert time" as a way to handle that. In cases where you do (payments converted on deposit), that's a subset of the more general problem, which is converting at an arbitrary point in time. However, even where you do (suppose instead I live in the US and someone pays me in CAD), there is no guarantee that the conversion rate when you enter the payment into your system as received and when you convert it is the same. The check could be deposited the next day, for example and converted at that point. OTOH, if it is an incoming wire transfer in AUD, I would expect it to be converted on receipt. So conversion between currencies is something which has to be done at a specified point in time. While some of this could be automated to an extent, it would really be business-specific. In essence, I think you would need a function like convert_currency(source monetary, target curr, date) to do the conversion. Furthermore this would require currency tables, and would be probably outside the core data type definition. In essence, to handle exchange rates, I think you would need additional tables and the like, and UDF's to do the actual conversions. For simplicity's sake, I think this would be broken off into a separate module although I would be happy to collaborate on that as well. -- 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] dividing money by money
Hi Chris, Many thanks for your comments. On Mon, Apr 5, 2010 at 10:16 AM, Chris Browne wrote: > Indeed. > > You can only be certain of there actually being a conversion if the > transaction directly involved a conversion between currencies. > > Thus... > > 1. If I buy materials using $USD on the $USD checking account, it's not > evident what conversion *ever* takes place for this transaction. Right. That's a major problem with the way LSMB (and SQL-Ledger) currently handle this. There are plenty of other issues that come up here as well > > Expressing that transaction in $CDN will *always* reflect an > estimate, never a "reality." Furthermore, since there is no conversion, there isn't any realized fx gain or loss. In other words, any fx gain or loss is a mere estimate necessary for accounting reports. IMO, any estimated unrealized gains or losses should be dynamically calculated anyway. > > 2. In contrast, a funds transfer from the $CDN account to the $USD > account will indicate some kind of "spot rate" because there will be > two specific amounts: > > - The amount of $CDN currency taken out of the one account, and > - The amount of $USD currency put into the other account. > > You may or may not know both values immediately; as Chris Travers > observes, there may be some time separation. > > It seems like an awfully bad idea to try to model this as if you > immediately know the exchange rate at the time the transaction is > recorded. Thinking through this further, I have concluded that at least two ways are necessary: 1) Conversion of currencies as an intrinsic process at a known rate. I.e. if I transfer money from a USD to a CDN account, and I have the numbers and the rates, I tell it the appropriate rate. 2) Conversion of currencies as an extrinsic process at a discovered rate. I.e. if I am running an income statement for 2009, I look up rates for converting my totals from USD to CDN at the end points and calculate estimated, unrealized fx gains and losses on that basis. My initial reasoning was different, namely that data types shouldn't depend on database tables to be usable. However, this then squarely addresses the other concern. So I suppose that's a good thing :-) Obviously any extrinsic elements shouldn't be tightly coupled with the intrinsic elements (meaning you have monetary types with intrinsic operators and functions, and then you have a separate, optional business logic module which can provide those extrinsic elements along with tables to store the values). > > To the contrary, it seems to me that rate conversion shouldn't be > treated as being at all tightly integrated into this. Agreed. If you'd be interested in seeing the first draft of the spec I came up with, I would be happy to forward it along. A few things in it will need to be changed due to what you have noted regarding looked up exchange rates, but the basic type definitions and base functions seem solid. > > I actually have a similar situation to this, albeit not for business; I > have a $USD denominated account that earns interest. > > I am expected to report on interest earnings on an annual basis by the > tax authorities. There tend to be three approaches considered readily > acceptable: > > 1. Use an annual average exchange rate (I presume it's a geometric > mean, but am not sure) on a total amount. > > This is the easiest, and is what I do. This is a published rate by the tax authorities? > > 2. Use monthly average exchange rates, applying the appropriate > one to each month's earnings. Are these published as well by tax authorities? > > 3. Use spot rates as reported by an authority, applying them to each > transaction. (For a bank account, this is actually pretty nearly > equivalent to #2, just with a different way of picking the > exchange rate!) > > The fact that there are multiple policies like this points to the > conclusion that it's inappropriate to try to capture exchange rates as > something tightly coupled inside each transactions. It's something > you'd want to have the option to change later, because the reporting > policy could well change. Right. There's also the following issue: 1) I send an invoice in CDN 2) 1 month later that invoice is paid in CDN. 3) I have to report relavant fx gains and losses. The only way I can see of doing this is to look up an accepted rate as of date of invoice, convert that, and then use the spot rate on the conversion of the payment. So one has (in this case) a realized gain or loss which is in part estimated (and extrinsic to the "conversion" which isn't "real) and in part known (and intrinsic to a r
Re: [BUGS] BUG #3593: Postgres Installer does not work
Omkar Patkar wrote: The following bug has been logged online: Bug reference: 3593 Logged by: Omkar Patkar Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2.4.1 Operating system: Windows Vista Home Basic Edition Description:Postgres Installer does not work Details: I tried to install postgres on Windows Vista Home Basic. It gave me two problems :- 1) The UAC (User Access Control) of Windows Vista was blocking the installer from creating a user account by the name "postgres" Hmmm Last time I tried (8.2.4) I had no problems installing on Vista Basic once I disabled UAC. See if the following whitepaper helps: http://port25.technet.com/archive/2007/02/22/postgresql-on-windows-a-primer.aspx 2) I some how managed to disable the UAC, and was the installer was able to create the user account for postgres. But the installer halted at one point and the installation could not be proceeded. Try: 1) Disabling UAC 2) Rebooting 3) If it says it is installed, remove it through the controll panel 4) Remove the C:\Program Files\PostgreSQL\8.2 directory 5) Try again. Note that win32 and general are probably better lists for this sort of issue to start on. Best WIshes, Chris Travers begin:vcard fn:Chris Travers n:Travers;Chris email;internet:[EMAIL PROTECTED] tel;work:509-888-0220 tel;cell:509-630-7794 x-mozilla-html:FALSE version:2.1 end:vcard ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] Problems with initdb
Hi Denise, I ran into this problem for quite a while until I read the README that came with the PostgreSQL installation (under /usr/share/doc, I think). I highly recommend referring to this document. There could be a couple causes of your error, and since cygwin handles the Sysv IPC calls differently than UNIX, the process, while straight-forward, is not intuitive if you have never done it. Best Wishes, Chris Travers On Fri, 2003-12-12 at 03:40, [EMAIL PROTECTED] wrote: > Hi, > > > I tried installed PostgreSql using CygWin in Windows 2000. > I got the following error when I tried to run initdb. > > creating configuration files... ok > > creating template1 database in /usr/share/postgresql/data/bas > > d not create shared memory segment: Function not implemented > > DETAIL: Failed system call was shmget(key=1, size=1081344, 0 > > > > initdb: failed > Could you help me to solve this problem? > > Thanks in advance, > > Denise & Glenda > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] PostgreSQL 7.4 on Windows XP?
Hi Arvind; Sorry, a native Windows port of PostgreSQL is not yet complete. If you want to run it on Windows (as I am doing until my RedHat Laptop is repaired), you can do so by installing it via cygwin (http://www.cygwin.com). This is a free download, but it can take some time. I found that getting PostgreSQL up and running under Cygwin was very difficult until I read the install docs, which explained things very well :-). I suggest read the install docs (/usr/share/doc/postgresql-7.4/FAQ_MSWIN) once you install the software. If you can't still get it running, you can send another email to the list. You may also want to look at installing the ipc-daemon2 and postgresql as NT Services. The other document worth reading is: /usr/share/doc/Cygwin/postgresql-7.4.README Best Wishes, Chris Travers - Original Message - From: Arvind Tiwari To: '[EMAIL PROTECTED]' ; '[EMAIL PROTECTED]' Sent: Saturday, January 03, 2004 6:57 PM Subject: [BUGS] PostgreSQL 7.4 on Windows XP? Hi. I am an amateur & want to learn PostgreSQL. I work on a Windows XP system. Is there any binary installer available to install the PostgreSQL database on my windows based system? I got this e-mail address from http://www.postgresql.org/docs/7.4/static/supported-platforms.html. But I could not follow the Chapter 15, as I am not a programmer, I just want to use the DB for learning SQL etc. Could you please help. Best Regards, Arvind Tiwari
Re: [BUGS] Fwd: Bug#308535: postgresql-client: [psql] manual page
Maybe it would be a good idea to have a manual page called somelint like libpq-connect and document it there. The other man pages could then reference it. Best Wishes, Chris Travers ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[BUGS] Bug report regarding reconcilliation
Summary: Under certain circumstances, the reconcilliation routine will clear more transactions than expected. Severity: Moderately Severe, not a showstopper, but impacts the consistancy of the accounting data. Frequency: Always Details: When a customer purchases an item with a split payment (say, two different checks) on the same date, these are usually entered with different source numbers (i.e. the numbers of the checks). If, during reconcilliation, only one of these checks is marked for reconciliation, the other one will be reconciled too if the details report is chosen. This is due to the fact that the details report reconciles by transaction id, and the fact that two payments are attached to the same transaction means that they will be reconciled as a unit. Steps to Reproduce: Create an AR transaction or sales invoice with an amount due. Create two payments against this invoice on the same day but with different source numbers against the same account. Reconile the account and only check off one of the payments. Go to reconcile the account again and note that the other payment has been flagged as cleared as well. Suggestions for Fixing: The problem occurs because the acc_trans table has no unique or primary key which can be used to identify a specific row. In lieu of this, the combination of trans_id, trans_date, and chart_id are used to identify rows for reconciliation, but these are not guaranteed to be unique. If you add source, you might be closer to the mark (this is the hack I am using to avoid this problem). The best solution would be to have an autogenerated sequence for the primary key of acc_trans. But this is a relatively invasive fix and would require some testing (better to include it in a beta release than the production one). Best Wishes, Chris Travers Metatron Technology Consulting begin:vcard fn:Chris Travers n:Travers;Chris email;internet:[EMAIL PROTECTED] x-mozilla-html:FALSE version:2.1 end:vcard ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [BUGS] could help me?
KLEBER SILVA wrote: Hello to all, Gentlemen, I have a doubt with relation to the number of connections that the Bank of Dados (POSTGRESQL) supports in a machine "Pentium III with 256 MB Memory", could help me? It depends... How big are the data sets being accessed by the queries? How intensive are the queries to run? How big is your typical database? What type of hard drives do you have and how fast are they? My best advice to you is to assume that PostgreSQL is close to the commerical offerings in these areas and then test with realistic data sets and your application. Best Wishes, Chris Travers Metatron Technology Consulting ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] HELP
Vaccaro wrote: I never download your product i don't even know what it is. When i started my computer up i saw 2 new accounts created. User Accounts. The accounts names were postgres services. It was password protected and limited account. I am running on windows XP. I Did delete the accounts. I want to know what happened i want it fixed. And i never want this to happen again You probably installed a piece of software that required PostgreSQL. If something you installed stops working, you will know what it is. Either that or someone else installed it for you in order to work with it. PostgreSQL is a relational database manager. It stores data for other programs. It is not adware, spyware, or anything like that. You can read more about it at http://www.postgresql.org Best Wishes, Chris Travers Metatron Technology Consulting [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #1947: Enhancement Request - CONCAT() function
Tom Lane wrote: "Jim C. Nasby" <[EMAIL PROTECTED]> writes: And you might want to make it a project at http://pgfoundry.org so others can make use of it. You might also want to define it as accepting an array; I think that would allow you to accept any number of parameters. I think Tony is trying to avoid putting in any actual work ;-). And that he wants to write queries that work on Oracle, MySQL, and PostgreSQL at the same time. One point I would make is that although || might appear to break MySQL at the moment, you can set the operator to be concat for the application (maybe in the function that connects to the DB?) Best Wishes Chris Travers Metatron Technology Consulting ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [BUGS] BUG #1947: Enhancement Request - CONCAT() function
Tony Marston wrote: No, but Oracle does, which is why I am trying to produce SQL statements that will run on MySQL, PostgreSQL and Oracle without the need for conversion. Hi Tony, Let me make a constructive suggestion. I see what you are trying to do and I can understand why this is useful. However, I agree with the main individuals here that it should not be a part of the core project. Fortunately PostgreSQL is extensible and it is quite easy to release custom extensions that can make these things happen without messing with the core project. One of the things I am going to be doing is creating either a Perl (client) or PLPGSQL (server) function to create concat() functions with up to x number of arguments. This will be used as part of our server-side porting framework to allow for easier migration from MySQL in particular. Would you be interested in participating in/testing/contributing to such a project? Best Wishes, Chris Travers Metatron Technology Consulting Tony Marston http://www.tonymarston.net -Original Message- From: Jim C. Nasby [mailto:[EMAIL PROTECTED] Sent: 10 October 2005 18:19 To: [EMAIL PROTECTED] Cc: Bruce Momjian; 'David Fetter'; pgsql-bugs@postgresql.org Subject: Re: [BUGS] BUG #1947: Enhancement Request - CONCAT() function PostgreSQL runs on machines that use EBCDIC? On Mon, Oct 10, 2005 at 04:26:15PM +0100, [EMAIL PROTECTED] wrote: Here is a direct quote from the ORACLE manual: On most platforms, the concatenation operator is two solid vertical bars, as shown in Table 3-3. However, some IBM platforms use broken vertical bars for this operator. When moving SQL script files between systems having different character sets, such as between ASCII and EBCDIC, vertical bars might not be translated into the vertical bar required by the target Oracle environment. Oracle provides the CONCAT character function as an alternative to the vertical bar operator for cases when it is difficult or impossible to control translation performed by operating system or network utilities. Use this function in applications that will be moved between environments with differing character sets. Tony Marston http://www.tonymarston.net pgman@candle.pha.pa.us wrote: Tony Marston wrote: which Oracle supports and MySQL can be made to support via a runtime option. They also both support CONCAT() because there are sometimes difficulties in dealing with vertical bars in the character sets used by certain operating systems and file systems. If enough database vendors offer it then it becmes a "de facto" standard. I have never heard of problems with vertical bars in any of those settings. Can you elaborate? I don't see how operating systems and file system character sets relate to SQL query characters. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #5652: Optimizer does wrong thing with partitioned tables
Just adding my voice to the "fix it" camp. Is there any reason the table scans in this sort of thing cannot be independently planned? Best Wishes, Chris Travers -- 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 1:53 PM, Mladen Gogala wrote: > Jeff, that's the problem. Functions like "MAX" are rather ordinary and > frequently used. Using sequential scan to read all partitions is the wrong > thing to do. I agree that AVG() cannot be computed using index but MAX() and > MIN() can. I will send you personally 2 versions of a script that I am still > writing, just to see to what extent do I go to get the necessary > performance. Optimizer definitely needs fixes when it comes to partitions. > IIRC, the planner already has been tweaked to allow index scans on MAX for single tables. This of course did not happen within a stable branch. The question over whether this is a "bug" or a "feature" depends to a large extent on how one defines a bug. I would be inclined to call this a "bug" for discussion purposes since it causes the planner to make plan choices that are well known to be problematic in these cases, but it I would not be in favor of correcting this in a stable branch. My reading of the change log is that it is rare that changes to long-standing behavior in general, and particularly for the optimizer, occur within a stable branch. Given that this is long-standing behavior, I think it is worth accepting that it is not a "bug" we might want fixed within a stable release. I agree with the suggestion that a discussion start on -hackers. I still think it is a problem that should be fixed. Just not in a stable branch, esp. because this has a reasonable workaround (changing to an order by... limit 1). I guess what I am trying to suggest here is that "bug" and "feature" are not distinct categories which have no overlap. Where software, like an RDBMS, is mission-critical, I think it is a good practice to do what the Pg developers do and avoid making unnecessary changes within a stable release. This means that some "bugs" should be treated as "features" where the behavior is longstanding, a workaround is possible, and the fix likely to involve changes to important components. Best Wishes, Chris Travers -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] GROUP BY checks inadequate when set returning functions in column list
Hi all;
In some of my tests regarding set-returning functions I came across
some very strange behavior. Group by seems to have very strange (and
inconsistent) behavior when connected to the use of a set-returning
function in a column list.
Consider the following function which returns a list of rows from a table:
mtech_test=# select * from account_heading__list();
id | accno | parent_id |description
---+---+---+---
10001 | 1000 | | CURRENT ASSETS
10006 | 1500 | | INVENTORY ASSETS
10010 | 1800 | | CAPITAL ASSETS
10015 | 2000 | | CURRENT LIABILITIES
10027 | 2600 | | LONG TERM LIABILITIES
10451 | 2700 | | Expense Accounts for Individuals
10225 | 3000 | | CAPITAL
10030 | 3300 | | SHARE CAPITAL
10032 | 4000 | | SALES REVENUE
10036 | 4300 | | CONSULTING REVENUE
10039 | 4400 | | OTHER REVENUE
10043 | 5000 | | COST OF GOODS SOLD
10049 | 5400 | | PAYROLL EXPENSES
10055 | 5600 | | GENERAL & ADMINISTRATIVE EXPENSES
(14 rows)
(Source code for function will be included below but I dont think this
is a function issue).
The above results are expected. Similarly if I run it in the column
list, I get tuple representations of the same data:
mtech_test=# select account_heading__list();
account_heading__list
---
(10001,1000,,"CURRENT ASSETS")
(10006,1500,,"INVENTORY ASSETS")
(10010,1800,,"CAPITAL ASSETS")
(10015,2000,,"CURRENT LIABILITIES")
(10027,2600,,"LONG TERM LIABILITIES")
(10451,2700,,"Expense Accounts for Individuals")
(10225,3000,,CAPITAL)
(10030,3300,,"SHARE CAPITAL")
(10032,4000,,"SALES REVENUE")
(10036,4300,,"CONSULTING REVENUE")
(10039,4400,,"OTHER REVENUE")
(10043,5000,,"COST OF GOODS SOLD")
(10049,5400,,"PAYROLL EXPENSES")
(10055,5600,,"GENERAL & ADMINISTRATIVE EXPENSES")
(14 rows)
It's when we add group by that things appear broken. Note it starts
returning 196 (14 x 14) records, which suggests a cross join against
itself.
mtech_test=# explain analyze select (account_heading__list()).* group by accno
mtech_test-# ;
QUERY PLAN
-
HashAggregate (cost=0.26..1.27 rows=1 width=0) (actual
time=0.456..1.986 rows=1
96 loops=1)
-> Result (cost=0.00..0.26 rows=1 width=0) (actual
time=0.170..0.194 rows=14
loops=1)
Total runtime: 2.076 ms
(3 rows)
My guess from looking at this deeper is that this is likely just
behavior that is prevented by group by column checks absent set
returning functions. The behavior goes away when the return columns
are brought back in line with the group by:
mtech_test=# select count(*) from (select
(account_heading__list()).accno group by accno) c;
count
---
14
(1 row)
Is this something we should be checking for and throwing exceptions based on?
mtech_test=# select version()
mtech_test-# ;
version
-
--
PostgreSQL 9.1.4 on i386-redhat-linux-gnu, compiled by gcc (GCC)
4.7.0 20120507
(Red Hat 4.7.0-5), 32-bit
(1 row)
mtech_test=# \df+ account_heading__list
List of functions
Schema | Name | Result data type| Argument
data types |
Type | Volatility | Owner | Language | Source
code
| Description
+---+---+-+--
--++--+--+---
--+--
-
public | account_heading__list | SETOF account_heading |
| n
ormal | stable | postgres | sql | SELECT * FROM
account_heading order b
y accno; | Returns a list of all account headings, currently ordered
by account
number.+
| | |
|
|| | |
|
(1 row)
Best Wishes,
Chris Travers
--
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] GROUP BY checks inadequate when set returning functions in column list
On Wed, Aug 22, 2012 at 8:04 AM, Tom Lane wrote: > Chris Travers writes: >> It's when we add group by that things appear broken. Note it starts >> returning 196 (14 x 14) records, which suggests a cross join against >> itself. > >> mtech_test=# explain analyze select (account_heading__list()).* group by >> accno >> mtech_test-# ; > > Hm, that really ought to throw an error, since you have ungrouped > columns in the result. Not sure why it doesn't. Yeah, that was my point. I don't know if it is worth fixing but always better to report. > > Beyond that, though, using a SRF in the target list this way is a bad > idea because the semantics are very ill-defined. Yeah. It looks like when you pair it with a general set in a from clause you get an implicit cross join. I have been avoiding it because I don't like implicit cross joins. I will be following the LATERAL feature with interest. Thanks for that. Best Wishes, Chris Travers -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] Minor inheritance/check bug: Inconsistent behavior
Hi;
I figured I would report this as well, primarily because people
getting into table inheritance may try to use this to solve the set
exclusion problem (i.e. partly required to prevent duplicate key
values in an inheritance tree). I see this as minor because I don't
see a lot of people using these aspects of the software in this way
now.
or_examples=# create table cities (city text, state text, is_capital
bool, altitude int, check(not(is_capital and tableoid::regclass::text
= 'cities')));
The intent of the check constraint is to force rows in the parent
table to use only a part of the key domain, while another portion
(where is_capital is true) can be reserved for child tables.
or_examples=# insert into cities values ('Seattle', 'Washington', false, 100);
INSERT 0 1
or_examples=# insert into cities values ('Olympia', 'Washington', true, 100);
INSERT 0 1
Ok, note that the check constraint was violated by the second row but
apparently this wasn't caught.
or_examples=# select *, tableoid::regclass::text from cities;
city | state| is_capital | altitude | tableoid
-+++--+--
Seattle | Washington | f | 100 | cities
Olympia | Washington | t | 100 | cities
(2 rows)
And indeed if we try to add the constraint again over the top
PostgreSQL will complain loudly.
or_examples=# alter table cities add check(not(is_capital and
tableoid::regclass::name = 'cities'));
ERROR: check constraint "cities_check1" is violated by some row
My guess is that tableoid is not known when the check constraint is
checked. It seems to me one option would be to either disallow
checking tableoid in the check constraint or making this known.
However as it is, PostgreSQL will not raise an error until after the
insert has already been made and the check constraint is re-applied.
or_examples=# select version();
version
-
--
PostgreSQL 9.1.4 on i386-redhat-linux-gnu, compiled by gcc (GCC)
4.7.0 20120507
(Red Hat 4.7.0-5), 32-bit
(1 row)
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Minor inheritance/check bug: Inconsistent behavior
On Fri, Aug 24, 2012 at 3:52 AM, Amit Kapila wrote: > From: pgsql-bugs-ow...@postgresql.org > [mailto:pgsql-bugs-ow...@postgresql.org] On Behalf Of Tom Lane > Sent: Friday, August 24, 2012 10:33 AM > Chris Travers writes: >>> My guess is that tableoid is not known when the check constraint is >>> checked. > >>None of the system columns are set at the time check constraints are >>checked. > > Is there any problem if set tableOID before calling ExecConstarints()? > I am not sure may be this is not so important problem for which we don't > want to > change existing code. Just to be sure my initial concern is this: Table inheritance would be easier if there was a way to declare a constraint such that it prevents insert for some rows on the parent but not for a child and/or vice versa. This can be used to partition the primary key between a parent and child tables to avoid some key overlap issues. My concern was that this was the "clever" solution that someone would try, insufficiently test, and find out that their clever solution in fact did nothing except preventing the constraint from being dropped and later re-applied. As for the ideal way of looking at addressing this possibility: I am further not going to speak for the developers here but I do wonder about system columns generally and check constraints, and whether the same solution is just to check the check constraint and error if a system column is checked. Some things seem obvious but what happens if someone says "this table cannot grow beyind 5 pages and we will do this by checking against ctid"? If we start pulling out some system columns for special treatment I am not sure where it ends. I am assuming that ctid cannot be safely known before the row is formally stored on disk. I think the cleanest fix interface-wise is to prevent check constraints from being added to tables in this case. I don't see it as a high priority though. My larger priority is to flag this as a possible thing someone is likely to try to get around the issues storing rows in both parent and child tables. Best Wishes, Chris Travers -- 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] Minor inheritance/check bug: Inconsistent behavior
On Mon, Aug 27, 2012 at 1:34 PM, Tom Lane wrote: > Robert Haas writes: > > Maybe, but in that case shouldn't referencing a system column chuck an > error? > > Yeah, possibly. I think none of them are populated with anything useful > during INSERT checks (though OID might be an exception?). Less sure > about the state during UPDATE checks, though. > > My vote honestly would be to make it be an error. A check constraint which fails only after it has been saved and then updated strikes me as behavior outside the role of a check constraint and dangerously so. It doesn't work as advertised, and people will find this out only after their data is shown not to be consistent with the check constraint. This being said, again, my sense is that no inherit check constraints will make it quite unlikely that this will ever affect production servers. So failing this it's sufficient I think in future versions (maybe 9.3 forward) to add a paragraph to the docs. Something like: Warning: The behavior of a check constraint operating against a system column is undefined. Check constraints are not intended to be used this way and behavior may change without notice. Maybe worth bringing up on the docs list. I don't mind the fact that behavior is undefined in some cases. However, it might be a good idea to let people know that they are moving into "we won't commit not to breaking your app even if you get this to work" territory. Best Wishes, Chris Travers
Re: [BUGS] BUG #6489: Alter table with composite type/table
here's my sense from what I've done in this area so far. On Tue, Aug 28, 2012 at 9:54 AM, Tom Lane wrote: > Bruce Momjian writes: > > On Wed, Mar 14, 2012 at 07:19:14PM +0100, Rikard Pavelic wrote: > >> On 13.3.2012. 20:49, Merlin Moncure wrote: > >>> I personally think it's an oversight. This was just discussed a > >>> couple of days ago here: > >>> > http://postgresql.1045698.n5.nabble.com/Altering-a-table-with-a-rowtype-column-td5544844.html > > >> TODO: alter table-type columns according to attribute type rules. > >> Enforce only TYPE features and ignore TABLE features when altering > composite table-types. > > > Should we add this TODO? I am confused by the text above though. > > I think this is making an assumption that we have consensus on what > are "type" properties and what are only "table" properties; that is, > is it a feature or a bug that column defaults don't work for instances > of composite types? > I think right now the fact is that multiple inheritance is usually a cleaner way to incorporate multiple table types in a single table. There are some giant gotchas here of course, but nothing like the area of using composite types in columns. This is an area where we may do well to work towards consensus. Right now tables and composite types work almost the same but there are so many odd cases where they don't that it is somewhat disorienting. > > The ALTER code is rejecting the case on the assumption that we think > this is a bug that should get fixed eventually. I'd only want to relax > the check if we have consensus that that will never happen. > But at the same time, you can create the table with a not null constraint and then insert nulls, so I am not sure what the difference is. Again this is a case where different assumptions are followed partway through and consequently you run into very unexpected sharp corners. > > The thread linked to via nabble above covers a lot of the background and > issues here. It didn't seem to me that there was clear consensus. > I have some blog posts written (to be published next week) on the sharp corners of these sorts of things and how to avoid them. My overall recommendation actually is to use table inheritance as an alternative if you can (prefixing column names to avoid collisions) but reserve these mostly for views. Maybe it would be a good idea to re-hash this on -general at that point. > > In any case, if we do do this, ISTM the TODO is much less about removing > one test in ALTER TABLE and much more about documenting the chosen > behavior. I think the reason you're confused by the proposed TODO > wording is exactly that it uses the phrases "TYPE features" and "TABLE > features" as if those concepts were defined or documented anywhere. > To be honest, having worked with these a bit, I think we need to choose the behavior before we can document or even implement it. Best Wishes, Chris Travers
Re: [BUGS] BUG #6489: Alter table with composite type/table
dding functions to manage the check constraints and explicitly checking them. Again if you had domains available you could create a domain that would presumably be expanded in table storage. > So, > you wouldn't have to go around looking for type_defaults() in the > event you added a defaulted column to a table (or, if we going in this > direction, a type). If adding a constraint, you'd probably have to go > looking around for type_constraints() though. > > merlin > I suppose this is yet another reason why multiple inheritance is an absolutely killer feature in PostgreSQL is that currently you *can* model your data in an equivalent way *and* have check constraints and not null constraints enforced ;-) Best Wishes, Chris Travers
Re: [BUGS] BUG #6489: Alter table with composite type/table
On Fri, Aug 31, 2012 at 12:32 AM, Rikard Pavelic wrote:
>
>
> I'm doing something most DBA would probably think it's a bad idea, but at
> least
> I can provide you with use case of Postgres usage.
>
There are bad ideas and there are bad ideas. The question of course is
what you get and what it costs. I think there are two big costs. The
first is that this area is full of inconsistencies in assumptions about
correct behavior and inconsistencies as you have found out. The second is
that composite types as columns make it harder for a lot of add-on
reporting tools to extract data out (which is why I think that multiple
inheritance is cleaner). But those have to be weighed against what you are
doing, naturally.
>
> We are trying to support DDD programming paradigm on top of Postgres.
> DDD modeling blocks are entities (and aggregates) and values.
> We map entities to tables and values to types.
> This fits mostly very nicely (values don't have identity, so they are
> inlined with
> other values and entities which uses them).
>
> I won't pretend to be an expert on DDD.
> What I think would be a great goal for Postgres is if lot of constraints
> would
> move from tables to types.
> This include NOT NULL, CHECK and even FOREIGN KEY (somewhere in the far
> future).
>
BTW, you can do NOT NULL and CHECK at the domain level fwiw and those will
pass through when composite types are stored.
>
> It would make me very happy if Postgres could check all constraints for
> model like this:
>
> aggregate country(code) {
> string code;
> string local_name;
> }
> aggregate person {
> string name;
> address[] addresses;
> }
> value address {
> string? street;
> string town;
> country *country; //this will create surrogate country_code field in
> address,
> //function country(address) which
> returns country
> //and it would be great if it could
> maintain relationship with country
> }
>
Take a look at recent blog entries in my blog for how to do the foreign key
dereferencing: http://ledgersmbdev.blogspot.com
Basically:
CREATE TABLE country_ref (
country_id int,
);
CREATE FUNCTION country(country_ref) RETURNS COUNTRY
STABLE LANGUAGE SQL AS $$
SELECT * FROM COUNTRY WHERE id = $1.country_id $$;
Then inherit from country and define the fkey in the child table.
>
> Currently Postgres can't declare NOT NULL for town and reference from
> address to country.
> I would be happy if direction Postgres takes would allow design like this
> to be enforced by database.
>
Sure it can:
CREATE DOMAIN not_null_string as text not null;
use not_null_string in place of text in your parent tables and it will be
enforced when pull these into the column. This is one of those
inconsistencies I mentioned above.
This is one of those reasons I don't see the backwards-compatibility
reasons so convincing. We can't create some modicum of consistency in
behavior without breaking *something.* I think the big issue is that
nobody has figured out exactly what we want to break.
Best Wishes,
Chris Travers
[BUGS] BUG #7549: max_connections check should query master when starting standby
On Mon, Sep 17, 2012 at 11:46 PM, Craig Ringer wrote: > On 09/18/2012 07:57 AM, Fujii Masao wrote: > >> If you change the max_connections on the master, you need to take a >> fresh backup from the master and start the standby from it. >> > > WTF, really? > > What else breaks the replication and forces a re-clone? > > Another WTF from me too. I can understand moving from read-only to read-write, but internally *why* does this happen? If it is the case, it needs to be clearly documented. (mental note to test this and report back). Why would this create a different timeline? Best Wishes, Chris Travers
[BUGS] BUG #8170: alter user does not accept timestamp output format in certain datestyles and timezones.
The following bug has been logged on the website:
Bug reference: 8170
Logged by: Chris Travers
Email address: chris.trav...@gmail.com
PostgreSQL version: 9.2.4
Operating system: Debian Linux
Description:
I have a pl/pgsql function which calculates at imestamp and alters a user's
password to be valid for 24 hours pending a password change. When the
datestyle and timezone are set to certain settings this throws an
exception.
Here is an approximation without plpgsql:
db=# show timezone;
TimeZone
--
Asia/Jakarta
(1 row)
db=# show datestyle;
DateStyle
---
Postgres, DMY
(1 row)
db=# select now();
now
-
Mon 20 May 11:39:24.273508 2013 WIT
(1 row)
db=# select 'ALTER USER ' || quote_ident('chris') || ' with valid until ' ||
quote_literal(now() + '1 day');
?column?
ALTER USER chris with valid until 'Tue 21 May 11:41:14.58554 2013 WIT'
(1 row)
db=# ALTER USER chris with valid until 'Tue 21 May 11:41:14.58554 2013
WIT';
ERROR: invalid input syntax for type timestamp with time zone: "Tue 21 May
11:41:14.58554 2013 WIT"
This worked before with different timezones with the same datestyle. Why is
this failing?
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
