Re: [BUGS] BUG #6052: ADD COLUMN - ERROR: tables can have at most 1600 columns
On lör, 2011-06-04 at 07:09 +, dinesh wrote: > I have a table which is used during data uploads, a so-called staging table. > This table has a fixed number of columns that [must] match the input CSV > file. This CSV file is uploaded using COPY command. Following the COPY, a > new column (meant for indexing) is constructed on this table using some > application logic; and dropped after that data upload cycle is over. > > After some 1500+ cycles, I get the following error: > > ERROR: tables can have at most 1600 columns > SQL state: 54011 > Context: SQL statement "ALTER TABLE stage_fo ADD COLUMN exch_ticker char > varying" > > So it appears that the command > ALTER TABLE stage_fo DROP COLUMN exch_ticker > is only producing some soft effects, not sufficient for the db engine. Yeah, a DROP COLUMN only hides the column and gradually phases it out from the storage, it doesn't remove it from the metadata storage, which is where the 1600 limit applies. So that application design is not going to work. I find it a bit suspicious that you add a column "for indexing", when you could perhaps be using an expression index. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] MACADDR parsing issues
Hey, While playing around with different MAC address notations supported by postgresql, I encountered the following: db=> select '08002b-010203'::macaddr; macaddr --- 08:00:2b:01:02:03 (1 row) db=> select '08002b-01023'::macaddr; macaddr --- 08:00:2b:01:02:03 (1 row) db=> select '08002b-0123'::macaddr; macaddr --- 08:00:2b:00:12:03 (1 row) db=> select '08002b-123'::macaddr; ERROR: invalid input syntax for type macaddr: "08002b-123" LINE 1: select '08002b-123'::macaddr; ^ db=> select '08002b-1203'::macaddr; ERROR: invalid octet value in "macaddr" value: "08002b-1203" LINE 1: select '08002b-1203'::macaddr; ^ (These particular results have been encountered on 8.4.4, but similar issues still seem to exist in the git head I pulled last week.) Note how for example '08002b-0123' is accepted as a valid MAC and is parsed as '08:00:2b:00:12:03' leading to two additional zeroes being added in different places. Furthermore, the last example actually matches the pattern for a MAC without delimiters, incorrectly parsing "-1" as one of the octets and thus resulting in an error about invalid octets instead of an invalid syntax. In case anyone is interested, I have attached a simple attempt at a patch for the MAC address parser that makes it a bit more strict in such a way that it still accepts the formats specified in the documentation, but rejects many other "broken" addresses that are currently accepted with sometimes surprising results. The attached version also rejects MACs containing additional whitespace between the octets and separators etc. The patch probably still needs a bit of work to make it more in line with your coding style, as well as a decent review to make sure it doesn't break anything else, but I'll leave that to those who know more about postgresql, MAC notations and sscanf :) I have also added a couple of additional test cases in the same diff, although the code could still use a few more for possible corner cases etc. Regards, Matthijs Bomhoff mac_parser.diff 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] MACADDR parsing issues
Matthijs Bomhoff writes: > The attached version also rejects MACs containing additional > whitespace between the octets and separators etc. I was under the impression that allowing whitespace there was a feature, not a bug. I'm not sure about the more general question of which abbreviated MAC formats are or should be allowed, though. Can you point to any standards about that? I'm disinclined to incur the inevitable application-compatibility complaints from making the code reject things it now accepts unless we have a pretty solid argument that "it now acts more like RFC says it should". 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] MACADDR parsing issues
On Jun 6, 2011, at 4:31 PM, Tom Lane wrote: > Matthijs Bomhoff writes: >> The attached version also rejects MACs containing additional >> whitespace between the octets and separators etc. > > I was under the impression that allowing whitespace there was a feature, > not a bug. Ah, I was not sure about that, that's why I explicitly mentioned it. In my patch I disallowed whitespace on both sides of the separators, as "01:02:03:04:05: 06" is currently fine, but "01:02:03:04:05 :06" is not, so I thought this might simply have been an unintended consequence of using sscanf. This could of course be changed in my patch. > I'm not sure about the more general question of which > abbreviated MAC formats are or should be allowed, though. Can you point > to any standards about that? I'm disinclined to incur the inevitable > application-compatibility complaints from making the code reject things > it now accepts unless we have a pretty solid argument that "it now acts > more like RFC says it should". According to the postgres documentation for the MACADDR data type, "The remaining four input formats are not part of any standard.", and I haven't been able to find any evidence to the contrary regarding that during a quick search. I do think some network hardware vendors allow some abbreviated formats, while others don't. Although I am by no means an expert on this, that's why I mentioned someone with more knowledge of such notations should probably look at it. I understand and agree that backwards-compatibility is a good thing, however I was personally rather surprised to see this happen: db=> select '08002b-0123'::macaddr; macaddr --- 08:00:2b:00:12:03 I can't imagine anyone writing an application that counts on this behavior (turning "0123" into "001203"; I am inclined not to qualify that as abbreviation.), in particular as the following leads to an error: db=> select '08002b-1203'::macaddr; ERROR: invalid octet value in "macaddr" value: "08002b-1203" LINE 1: select '08002b-1203'::macaddr; ^ Anyway, I only wanted to point this out as some of the current behavior struck me as slightly odd. And I figured it would be nice to add a possible patch and some additional tests to my email in order to help out a bit in case anyone thought it would be a good idea to change it. Do with it as you see fit :) Regards, Matthijs Bomhoff -- 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 #6051: wCTE query fail with wrong error text on a table with rules
On Sat, Jun 04, 2011 at 05:24:22PM -0400, Tom Lane wrote: > Merlin Moncure writes: > > On Fri, Jun 3, 2011 at 10:42 AM, Jehan-Guillaume (ioguix) de Rorthais > > wrote: > >> test=# CREATE RULE test_ins AS ON INSERT TO test DO INSTEAD INSERT INTO > >> test2 VALUES (NEW.i); > >> CREATE RULE > >> test=# WITH t1 AS ( > >> DELETE FROM ONLY test RETURNING * > >> ) > >> INSERT INTO test SELECT * FROM t1; > >> ERROR: could not find CTE "t1" > > > IIRC the fact that rules don't play nice with wCTE was brought up > > several times during the implementation discussions. I'm not saying > > the error message is great, but you can pretty much add this to the > > giant pile of reasons not to use rules at all (particularly in 9.1 > > with the view triggers). > > There are definitely cases that don't work, but I had thought we at > least threw an intelligible "not implemented" error for all of them. > This one seems to be an oversight: specifically, rewriteRuleAction() > isn't considering the possibility that the rewritten rule action will > need to make use of CTEs from the original query. > > We could paste a copy of the original's cteList into the rule action, > but there are still issues: > > * If there's more than one rule action, we could end up executing > multiple copies of the same CTE query; which breaks the expectation > of single evaluation for a CTE. > > * If there are CTEs attached to the rule action, as well as to the > original query, and there is a conflict of CTE names between them, > we can't handle that AFAICS. (The planner expects to look up entries > in a cteList by name...) > > * Maybe some other things that aren't obvious yet. > > I don't particularly mind throwing a not-implemented error for the first > case (ie, just say multiple rule actions don't mix with CTE queries); Great :) > but the second case seems seriously annoying, since there's no way > for someone to write a CTE-containing rule action without risking a > conflict. Ideas anybody? Is there some way to throw "not implemented" there, too? It seems to me that making accommodations for the user-modifiable part of our rewrite rules isn't the right direction to go, as we're well on our way to phasing the user-modifiable part out. Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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 #6041: Unlogged table was created bad in slave node
On Fri, Jun 3, 2011 at 1:01 PM, Alvaro Herrera wrote: > Excerpts from Robert Haas's message of vie jun 03 12:44:45 -0400 2011: >> On Wed, Jun 1, 2011 at 2:28 PM, Robert Haas wrote: > >> > (4) It strikes me that it might be possible to address this problem a >> > bit more cleanly by allowing mdnblocks() and smgrnblocks() and >> > RelationGetNumberOfBlocksInFork() to take a boolean argument >> > indicating whether or not an error should be thrown if the underlying >> > physical file happens not to exist. When no error is to be signaled, >> > we simply return 0 when the main fork doesn't exist, rather than >> > throwing an error. >> >> If we don't want to gum this with the above-mentioned cruft, the other >> obvious alternative here is to do nothing, and live with the >> non-beauty of the resulting error message. > > Option 4 seems reasonable to me ... can you get rid of the dupe > smgrnblocks call simultaneously? What dup smgrnblocks call? Patch along these lines attached. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company reject-unlogged-during-recovery-v2.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 #6051: wCTE query fail with wrong error text on a table with rules
David Fetter writes: > On Sat, Jun 04, 2011 at 05:24:22PM -0400, Tom Lane wrote: >> We could paste a copy of the original's cteList into the rule action, >> but there are still issues: >> >> * If there's more than one rule action, we could end up executing >> multiple copies of the same CTE query; which breaks the expectation >> of single evaluation for a CTE. >> >> * If there are CTEs attached to the rule action, as well as to the >> original query, and there is a conflict of CTE names between them, >> we can't handle that AFAICS. (The planner expects to look up entries >> in a cteList by name...) >> >> * Maybe some other things that aren't obvious yet. >> >> I don't particularly mind throwing a not-implemented error for the first >> case (ie, just say multiple rule actions don't mix with CTE queries); > Great :) >> but the second case seems seriously annoying, since there's no way >> for someone to write a CTE-containing rule action without risking a >> conflict. Ideas anybody? > Is there some way to throw "not implemented" there, too? It seems to > me that making accommodations for the user-modifiable part of our > rewrite rules isn't the right direction to go, as we're well on our > way to phasing the user-modifiable part out. Yes, we certainly can throw an error for that, it's just going to be a pain from a usability standpoint. But I agree that the number of people affected might be small, so it's not worth doing a significant rewrite for right now. I've committed a patch along these lines. 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 #6041: Unlogged table was created bad in slave node
On Wed, Jun 1, 2011 at 7:28 PM, Robert Haas wrote: > On Thu, May 26, 2011 at 12:34 PM, Tom Lane wrote: >> Alvaro Herrera writes: >>> Excerpts from Euler Taveira de Oliveira's message of jue may 26 12:00:05 >>> -0400 2011: I think we should emit the real cause in those cases, if possible (not too much overhead). The message would be "Unlogged table content is not available in standby server". >> >>> I guess what it should do is create an empty file in the slave. >> >> Probably it should, because won't the table malfunction after the slave >> is promoted to master, if there's no file at all there? Or will the >> process of coming live create an empty file even if there was none? > > Coming live creates an empty file. > >> But Euler is pointing out a different issue, which is usability. If the >> slave just acts like the table is present but empty, we are likely to >> get bug reports about that too. An error telling you you aren't allowed >> to access such a table on slaves would be more user-friendly, if we can >> do it without too much pain. > > I looked into this a bit. A few observations: > > (1) This problem is actually not confined to unlogged tables; > temporary tables have the same issue. For example, if you create a > temporary table on the master and then, on the slave, do SELECT * FROM > pg_temp_3.hi_mom (or whatever the name of the temp schema where the > temp table is) you get the same error. In fact I suspect if you took > a base backup that included the temporary relation and matched the > backend ID you could even manage to read out the old contents (modulo > any fun and exciting XID wraparound issues). But the problem is of > course more noticeable for unlogged tables since they're not hidden > away in a special funny schema. Seems like you're trying to fix the problem directly, which as you say, has problems. At some point we resolve from a word mentioned in the FROM clause to a relfilenode. Surely somewhere there we can notice its unlogged before we end up down in the guts of smgr? -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
