[BUGS] BUG #5662: Incomplete view
The following bug has been logged online: Bug reference: 5662 Logged by: Email address: saer...@hotmail.com PostgreSQL version: 8.4 Operating system: Windows Vista Description:Incomplete view Details: The Sequence view in the information schema is incomplete. It does not return a Sequence's maximum_value, minimum_value or increment. Please complete the view. -- 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 #5662: Incomplete view
On sön, 2010-09-19 at 09:41 +, saer...@hotmail.com wrote: > The Sequence view in the information schema is incomplete. It does not > return a Sequence's maximum_value, minimum_value or increment. Please > complete the view. This is known and documented: http://www.postgresql.org/docs/8.4/static/infoschema-sequences.html Should still be fixed eventually, of course. -- 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 #5662: Incomplete view
Peter Eisentraut writes: > On sön, 2010-09-19 at 09:41 +, saer...@hotmail.com wrote: >> The Sequence view in the information schema is incomplete. It does not >> return a Sequence's maximum_value, minimum_value or increment. Please >> complete the view. > This is known and documented: > http://www.postgresql.org/docs/8.4/static/infoschema-sequences.html > Should still be fixed eventually, of course. I think the difficulty is in the fact that you can't join to a sequence whose name isn't predetermined. In the past we've speculated about creating a single catalog or view containing all sequences' parameters, so that information_schema.sequences could be implemented with a join to that. However, there's never been any movement on that, and it seems less than trivial to do. What about inventing a function to extract a sequence's parameters? Perhaps something like pg_sequence_parameter(seq regclass, colname text) returns bigint which would do an appropriate permissions check and then fetch the named column. (This could actually be implemented in a line or two in plpgsql, but I think we want it in C because information_schema shouldn't depend on plpgsql.) This would work OK for all the bigint columns, and we could cheat a bit for the boolean columns by returning 0 or 1. You couldn't fetch the sequence_name column this way, but that's okay with me --- we don't maintain that anyway. Given that, the sequence view would include outputs like CAST(pg_sequence_parameter(c.oid, 'max_value') AS cardinal_number) AS maximum_value, The main objection I can see to this is that fetching multiple column values would involve multiple accesses to the sequence. But it's not clear that a solution based on a single view would be any better performance-wise. Another possibility, if we had LATERAL, would be a function that takes just the sequence OID and returns all its parameters as a row. But again, if we want to do it that way then fixing the view will involve waiting for a complex feature that might or might not show up anytime soon. Or maybe we could implement that function, call it like this CAST((pg_sequence_parameters(c.oid)).max_value AS cardinal_number) AS maximum_value, and plan on optimizing the view when we get LATERAL. 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 #5661: The character encoding in logfile is confusing.
Craig Ringer writes: > Yes, the mismatched encodings in the data are clear and obvious. > Given that the messages are coming purely from postgresql, not client > code, I'm now wondering if what we're dealing with is mismatched > encodings in the translation files, where some messages were translated > with a different encoding to other messages. The examples you give don't seem to support that idea. I don't read Japanese, but at least these cases look like they are all UTF8 as expected in the .po files. > One of the correctly encoded messages is "Unexpected EOF received on > client connection" > One of the incorrectly encoded (shift-JIS) messages is: "Fast Shutdown > request received". Another is "Aborting any active transactions". > ... question now is where the messages are converted from UTF-8 to shift-JIS > and why that conversion is being applied inconsistently. Given those three examples, I wonder whether all the mis-encoded messages are emitted by the postmaster, rather than backends. Anyway it seems that you ought to look for some pattern in which messages are correctly vs incorrectly encoded. 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
[BUGS] Error in sorting strings
Hi guys,
I found a weird behaviour in sorting character varying columns, I think
it could really be a bug.
Table definition:
CREATE TABLE dummy (string character varying(255));
Test dataset:
INSERT INTO dummy VALUES ('10.');
INSERT INTO dummy VALUES ('100');
If I query the table sorting by the string column I get the following:
SELECT * FROM dummy ORDER BY string ASC;
string
100%
10.1
(2 rows)
I would expect the order of the results to be '100%', '10.1'.
Using the ascii function on the third character of the strings gives:
SELECT string, ascii(substr(string, 3, 1)) FROM dummy;
string | ascii
+---
100% |48
10.1 |46
Moreover, if I use the following dataset, I get the correct sorting
INSERT INTO dummy VALUES ('100');
INSERT INTO dummy VALUES ('10.');
SELECT string, ascii(substr(string, 3, 1)) FROM dummy ORDER BY string ASC;
string | ascii
+---
10.|46
100|48
I'm using PostgreSQL 8.3.11 on a Linux Debian Lenny
My 2 cents
Edoardo Serra
--
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] Error in sorting strings
Edoardo Serra wrote: > SELECT * FROM dummy ORDER BY string ASC; > string > > 100% > 10.1 > (2 rows) > > I would expect the order of the results to be '100%', '10.1'. > Moreover, if I use the following dataset, I get the correct sorting > > SELECT string, ascii(substr(string, 3, 1)) FROM dummy ORDER BY > string ASC; > string | ascii > +--- > 10. | 46 > 100 | 48 You left out the most important piece of information -- what collation have you configured? Many common collations ignore special characters (except perhaps as tie-breakers), so your examples sort based on: '100' '101' versus '10' '100' Both examples look right if you are using such a collation. -Kevin -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5664: index "idx000_mytable19" contains unexpected zero page
The following bug has been logged online: Bug reference: 5664 Logged by: simon Email address: xuboc...@163.com PostgreSQL version: 8.3.11 Operating system: suse 10 Linux omu 2.6.16.60-0.54.5-bigsmp #1 SMP Fri Sep Description:index "idx000_mytable19" contains unexpected zero page Details: Version information] Postgres 8.3.11 [Symptom] omu=# vacuum mytable19; ERROR: SQLSTATE XX002: index "idx000_mytable19" contains unexpected zero page at block 523 HINT: Please REINDEX it. LOCATION: _bt_checkpage, nbtpage.c:432 [Operation information before the problem occurs] 1. Define 300 tables for the following table (the value of %d ranges from 0 to 299). CREATE TABLE mytable%d( id integer NOT NULL, iformatid integer NOT NULL, imodulenum integer NOT NULL, icircuitno integer NOT NULL, icircuitstatus smallint NOT NULL, igrpcode integer NOT NULL, icic integer NOT NULL, isendamplify smallint NOT NULL, ireceiveamplify smallint NOT NULL, icallerctrflag smallint NOT NULL, icallobserveflag smallint NOT NULL, ireserved smallint NOT NULL, iv5pcmid smallint NOT NULL, icirsortnum integer NOT NULL, imgwindex integer NOT NULL, sterminationid character varying(16) NOT NULL, iserverindex smallint NOT NULL, idthflag smallint NOT NULL, itid integer NOT NULL, icircuittype smallint NOT NULL, i_mog integer NOT NULL, i_referable integer NOT NULL, istatus smallint NOT NULL ); CREATE UNIQUE INDEX idx000_mytable%d ON mytable%d USING btree (id DESC, istatus); CREATE INDEX idx001_mytable%d ON mytable%d USING btree (iformatid); CREATE INDEX idx002_mytable%d ON mytable%d USING btree (iformatid, istatus); CREATE INDEX idx003_mytable%d ON mytable%d USING btree (imodulenum, icircuitno, istatus); CREATE INDEX idx004_mytable%d ON mytable%d USING btree (igrpcode, icic, istatus); CREATE INDEX idx005_mytable%d ON mytable%d USING btree (igrpcode, icircuitno, istatus); CREATE INDEX idx006_mytable%d ON mytable%d USING btree (igrpcode, sterminationid, istatus); CREATE INDEX idx007_mytable%d ON mytable%d USING btree (id, imodulenum, istatus); CREATE INDEX idx008_mytable%d ON mytable%d USING btree (igrpcode, iserverindex, imodulenum, istatus, id); CREATE INDEX idx009_mytable%d ON mytable%d USING btree (imodulenum, iserverindex, istatus); CREATE INDEX idx010_mytable%d ON mytable%d USING btree (imodulenum, istatus); CREATE INDEX idx011_mytable%d ON mytable%d USING btree (iserverindex, istatus); CREATE INDEX idx012_mytable%d ON mytable%d USING btree (icic, igrpcode, istatus); CREATE INDEX idx013_mytable%d ON mytable%d USING btree (sterminationid, imgwindex, istatus); CREATE INDEX idx014_mytable%d ON mytable%d USING btree (sterminationid, imgwindex, igrpcode, istatus); CREATE INDEX idx015_mytable%d ON mytable%d USING btree (sterminationid, igrpcode, imodulenum, istatus); CREATE INDEX idx016_mytable%d ON mytable%d USING btree (icircuitno, imodulenum, istatus); / 2. For each mytable% table, first insert 100,000 records, establishes indexes, and then circularly run the following commands by simultaneously starting 600 links (The indexes function normally during the following operations): INSERT INTO PUBLIC.MYTABLE%d VALUES(%d, 123777, 456678, 789555, 45, 65455, 9874, 12, 34, 56, 78, 90, 2123, 4456, 5567, 'AAABBBCCCDDD', 4345, 6222, 1, 87, 7894562, 66548, 98) SELECT * FROM PUBLIC.MYTABLE%d WHERE ID = %d DELETE FROM PUBLIC.MYTABLE%d WHERE ID = %d 3. The postgres process was killed during the concurrent execution of the 600 links. 4. After the concurrent execution is complete, the system powers off. [Preliminary analysis] The index file idx000_mytable19 has 524 pages in total, and page 523 of the file is blank. When checking the validity of page 523 during the vacuum operation, the system believes that the page is invalid and reports the preceding error. The use of the indexes, however, is not affected. You can obtain the same information by running select * from mytable19 order by id,istatus and select * from mytable19. [Scenario recurrence based on simulation] 1. Start 600 links to concurrently operate data tables. After a period, kill the postgres process. Repeat this scenario for more than 50 times, the symptom does not recur. 2. Add a breakpoint at the smgrextend function, and kill the postgres process after about ten minutes. Repeat this scenario for more than 50 times, the symptom does not recur. The blank page is displayed at the last part each time, which is normal. [Information obtained from the forums] The following solutions are provided in almost all forums: 1. Set fsync to on (The parameter is set to on in the current version). 2. Reestablish the indexes. -- 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 #5664: index "idx000_mytable19" contains unexpected zero page
"simon" writes: > Description:index "idx000_mytable19" contains unexpected zero page > ... > 4. After the concurrent execution is complete, the system powers off. > ... > The following solutions are provided in almost all forums: > 1. Set fsync to on (The parameter is set to on in the current version). If you're implying that you ran a test involving power loss with fsync off, then this is not a bug. Even if you had fsync on, I would wonder about whether your disk drives execute fsync properly. 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
