[BUGS] BUG #8470: 9.3 locking/subtransaction performance regression
The following bug has been logged on the website: Bug reference: 8470 Logged by: Oskari Saarenmaa Email address: o...@ohmu.fi PostgreSQL version: 9.3.0 Operating system: Linux Description: The following code performs a lot slower on PostgreSQL 9.3.0 than on PostgreSQL 9.2.4: DROP TABLE IF EXISTS tmp; CREATE TABLE tmp (id BIGSERIAL, vals BIGINT[]); DO $$ DECLARE r_id BIGINT; n BIGINT; BEGIN FOR n IN 1..1000 LOOP BEGIN SELECT id INTO r_id FROM tmp WHERE array_length(vals, 1) < 100 LIMIT 1 FOR UPDATE NOWAIT; EXCEPTION WHEN lock_not_available THEN r_id := NULL; END; IF r_id IS NULL THEN INSERT INTO tmp (vals) VALUES (ARRAY[n]::BIGINT[]); ELSE UPDATE tmp SET vals = array_append(vals, n::BIGINT) WHERE id = r_id; END IF; END LOOP; END; $$; PostgreSQL 9.3.0: Time: 7278.910 ms PostgreSQL 9.2.4: Time: 128.008 ms Removing the BEGIN/EXCEPTION/END block and just doing a 'SELECT FOR UPDATE' for a suitable row is significantly slower in 9.3.0 (314.765 ms vs 118.894 ms on 9.2.4). A 'SELECT' without a FOR UPDATE and BEGIN/EXCEPTION/END has the same performance on 9.2.4 and 9.3.0. I'm running 9.2.4 and 9.3.0 packages from apt.postgresql.org on a Debian Squeeze host. -- 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 #2906: slow windows network performance
The following bug has been logged online: Bug reference: 2906 Logged by: Adriaan van Os Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1.4 Operating system: Windows XP Description:slow windows network performance Details: Somehow, native Windows networking is very slow. Extensive tests show that transferring large amounts of data from a Windows XP server to a Windows XP client is up to 60 times slower than sending the same amount of data from the same Windows server to a Mac OS X client with "comparable" (Intel) hardware. This is true when working with libpg synchronously as well as asynchronously. The probable cause is PQconsumeInput and the native Windows networking and/or the wait-for-single-object-or-so routines that it calls. This has been tested on a range of machines, including a brand new out-of-the-box Windows XP machine. Processor usage during the data transfer on the Windows client machine is very low, so obviously, the client machine spends most time in a wait-for-nothing state. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[BUGS] BUG #2905: min and max return incorrect text type
The following bug has been logged online: Bug reference: 2905 Logged by: Adriaan van Os Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1.4 Operating system: Mac OS X 10.4.6, intel Description:min and max return incorrect text type Details: Table 9-37. Aggregate Functions in the Postgres docs states that the return type for min and max is the "same as argument type". However, min(VARCHAROID) and max(VARCHAROID) return TEXTOID as a result type. ---(end of broadcast)--- TIP 1: 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 #2907: pg_get_serial_sequence quoting
The following bug has been logged online: Bug reference: 2907 Logged by: Adriaan van Os Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1.4 Operating system: Mac OS X 10.4.6, intel Description:pg_get_serial_sequence quoting Details: In order to work with capitals (etc.), the table_name parameter of pg_get_serial_sequence needs double quotes inside single quotes, the column_name parameter requires a name within single quotes only. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [BUGS] BUG #2906: slow windows network performance
jose fuenmayor wrote: Use linux mac or any other unix like operating system, it performs better in every aspect. Thank you for your wonderful advice, but this is really a bug report. Adriaan van Os ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #2905: min and max return incorrect text type
Bruce Momjian wrote: Adriaan van Os wrote: The following bug has been logged online: Bug reference: 2905 Logged by: Adriaan van Os Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1.4 Operating system: Mac OS X 10.4.6, intel Description:min and max return incorrect text type Details: Table 9-37. Aggregate Functions in the Postgres docs states that the return type for min and max is the "same as argument type". However, min(VARCHAROID) and max(VARCHAROID) return TEXTOID as a result type. Yea, they are internally treated as very similar types. But "internally treated as very similar" is still not "same as argument type". Computing requires exactness. Adriaan van OS ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #2907: pg_get_serial_sequence quoting
Bruce Momjian wrote: Strangely, this was reported before, but not until November of 2006: http://archives.postgresql.org/pgsql-general/2006-11/msg0.php That was a follow up on this thread <http://archives.postgresql.org/pgsql-hackers/2004-10/msg00964.php>. Regards, Adriaan van Os ---(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] BUG #2905: min and max return incorrect text type
Peter Eisentraut wrote: Adriaan van Os wrote: However, min(VARCHAROID) and max(VARCHAROID) return TEXTOID as a result type. Yea, they are internally treated as very similar types. But "internally treated as very similar" is still not "same as argument type". Computing requires exactness. Aside from the apparent discrepancy between the documentation and the actual behavior, is there an actual use case where this is a problem? Dijkstra's "Rule 0: Don’t Make a Mess of It” <http://www.cs.utexas.edu/users/EWD/> and the virtues of strong typing, which, for SQL, imply checks at runtime <http://en.wikipedia.org/wiki/Type_safety>. Besides, the question is absurd. I stumble over a stone on the road, report it and then you ask "is there an actual use case where this is a problem". Why else do I report it ? What you probably wanted to ask is: "Apart from the missing warning along the road, couldn't you have walked around that stone ?" Well, in answer to that last question, I could have, but that is the wrong approach to computing. Regards, Adriaan van Os ---(end of broadcast)--- TIP 1: 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
Re: [HACKERS] [BUGS] BUG #2907: pg_get_serial_sequence quoting
Tom Lane wrote:
Bruce Momjian <[EMAIL PROTECTED]> writes:
I presume the reason for that is that the first paramater can be
qualified:
select pg_get_serial_sequence('"public"."FOO"', 'Ff1');
Would someone explain why qualification makes us lowercase the first
parameter by default? I don't understand it well enough to document it.
The point is that we have to parse the first parameter, whereas the
second one can be taken literally.
It still looks inconsistent and ugly. I think the design mistake of pg_get_serial_sequence is that
it takes two parameters rather than one (a fully qualified doublequoted columnname path) or three
(optionally empty schema, tablename, columnname, all three literal).
Regards,
Adriaan van Os
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [BUGS] BUG #2905: min and max return incorrect text type
Tom Lane wrote: Bruce Momjian <[EMAIL PROTECTED]> writes: Peter Eisentraut wrote: Aside from the apparent discrepancy between the documentation and the actual behavior, is there an actual use case where this is a problem? No, I don't think so, and I am reluctant to adjust the documentation to say "or similar". The documentation is correct as it stands: max(text) returns text. Adriaan's complaint about max(varchar) is off base because there is no such function. No, the documentation says that the Argument Type of max and min can be "any array, numeric, string, or date/time type" and that the Return type is the "same as argument type". The functions min and max applied to a field of type varchar return a function result of type text. So, if a max(varchar) function is missing and the cause of the text result type is implicit type casting, then the fact that there is no max(varchar) function is exactly the bug. Adriaan van Os ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[BUGS] BUG #2977: dow doesn't conform to ISO-8601
The following bug has been logged online: Bug reference: 2977 Logged by: Adriaan van Os Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1.4 Operating system: Mac OS X 10.4.6, intel Description:dow doesn't conform to ISO-8601 Details: Section 9.9.1 of the Postgres docs <http://www.postgresql.org/docs/8.2/interactive/functions-datetime.html> mentions that the "week" subfield of the date_part function conforms to the ISO-8601 standard. The same ISO-8601 standard <http://www.omg.org/docs/ISO-stds/06-08-01.pdf> defines on page 9 in table-2 of section 3.2.2 that Monday is day 1 and Sunday day seven. However, the "dow" subfield of the date_part function returns 0 for Sunday. You can not, in the same function, ignore ISO-8601 for one subfield and follow it in another. Besides, if in the same week Sunday comes before Monday, how can the result of the "week" and "dow" fields conform to each other ? If "dow" can not be changed for reasons of backward compatibility, I suggest a new subfield "dayofweek" that does conform to the standard. Adriaan van Os ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[BUGS] BUG #3811: Getting multiple values from a sequence generator
The following bug has been logged online: Bug reference: 3811 Logged by: Adriaan van Os Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2.5 Operating system: Mac OS X 10.5 Description:Getting multiple values from a sequence generator Details: The following has been observed In Postgres 8.2.5 1. LOCK TABLE doesn't work on a sequence 2. nextval will happily ignore ISOLATION LEVEL SERIALIZABLE of concurrent transactions 3. nextval doesn't have an optional "increase" parameter (the increase is always one). Therefore, there is no way (I know of) to prevent a concurrent nextval between a nextval and a setval call. Consequently, it is not possible to create an atomic operation that increases nextval by a value N > 1. I suggest an optional "increase" parameter to the nextval function. This can make a certain kind of bulk operation and bulk import much more efficient. ---(end of broadcast)--- TIP 6: explain analyze is your friend
[BUGS] BUG #3891: Multiple UPDATE doesn't handle UNIQUE constraint correctly
The following bug has been logged online: Bug reference: 3891 Logged by: Adriaan van Os Email address: [EMAIL PROTECTED] PostgreSQL version: 8.2.4 Operating system: Mac OS X 10.5 Description:Multiple UPDATE doesn't handle UNIQUE constraint correctly Details: Suppose we have a table A with a UNIQUE column Name of type VARCHAR, a PRIMARY KEY record_id of type INT4 and the following records A record_id Name 1 X 2 Y Furthermore, we have a temporary table temp_A with a UNIQUE column Name of type VARCHAR, a PRIMARY KEY record_id of type INT4 and the following records temp_A record_id Name 1 Y 2 X Now, we update table A with values from temporary table temp_A doing something like UPDATE "A" SET "Name" = "temp_A"."Name" FROM "temp_A" WHERE "A".record_id = "temp_A".record_id This will cause a UNIQUE violation in de middle of the UPDATE statement, although after completion of the statement there is no such violation. Although deferred constraints are not yet implemented for Postgres, the docs at <http://www.postgresql.org/docs/8.2/static/sql-set-constraints.html> state: IMMEDIATE constraints are checked at the end of each statement. Apparently, this is not correct, as in the above example constraints are checked in the middle of a statement, rather than at the end. I feel the docs are right here and the software wrong. Sincerely, Adriaan van Os ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[BUGS] BUG #2292: Calling conventions in docs
The following bug has been logged online: Bug reference: 2292 Logged by: Adriaan van Os Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1 Operating system: Mac OS X Description:Calling conventions in docs Details: Section 32.9.5. Writing Code of the 8.1 docs says: >>While it may be possible to load functions written in languages other than C into PostgreSQL, this is usually difficult (when it is possible at all) because other languages, such as C++, FORTRAN, or Pascal often do not follow the same calling convention as C. That is, other languages do not pass argument and return values between functions in the same way<< This is nonsense. GNU Pascal and GNU C use the same calling conventions. In fact, they share the same compiler-backend. Sincerely, Adriaan van Os <http://www.microbizz.nl/gpc.html> ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [BUGS] BUG #2292: Calling conventions in docs
Bruno Wolff III wrote: Adriaan van Os <[EMAIL PROTECTED]> wrote: Section 32.9.5. Writing Code of the 8.1 docs says: While it may be possible to load functions written in languages other than C into PostgreSQL, this is usually difficult (when it is possible at all) because other languages, such as C++, FORTRAN, or Pascal often do not follow the same calling convention as C. That is, other languages do not pass argument and return values between functions in the same way<< This is nonsense. GNU Pascal and GNU C use the same calling conventions. In fact, they share the same compiler-backend. You seem to be assuming that Postgres will always be built using gcc. That isn't the case. Note that the manual says "this is usually difficult (when it is possible at all)". I don't think there are much C compilers around with calling conventions that a Pascal compiler cannot reproduce. Name me one ! Anyway, recompiling with gcc will solve the problem. The manual is simply taking an old prejudice as a fact. Sincerely, Adriaan van Os ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #2292: Calling conventions in docs
Tom Lane wrote: Adriaan van Os <[EMAIL PROTECTED]> writes: The manual is simply taking an old prejudice as a fact. No, it is stating a fact as as fact. The existence of one counterexample does not disprove the generalization. Keep dreaming. Ignorance rules the world. Adriaan van Os ---(end of broadcast)--- TIP 6: explain analyze is your friend
