[BUGS] BUG #5940: Creating a foreign key on an empty table locks table
The following bug has been logged online: Bug reference: 5940 Logged by: Adriaan Email address: adriaa...@gmail.com PostgreSQL version: 8.4 / 9.0 Operating system: Ubuntu Description:Creating a foreign key on an empty table locks table Details: I am using creation of table during the runtime of my application. This gives some locking issues which are actually not needed. Let me describe what i am doing. 1. Create a table A 2. Create a table B 3. Insert a record in A 4. start transaction t1 5. select a record from A -> read lock on A (the proces is not starting another parallel trans:) 6. in another transaction t2 create a foreign key from B to A. --> this is causing a lock on table A, thus causing a hanging lock I think the lock during the creation on foreign key is not needed since B has no records and since the table definition of A is NOT changed. Is it possible to remove the lock during creation of foreign keys in such situations so the database is more robust for runtime table alters? Test queries: 1. create table A (field1 int primary key); 2. create table B (field2 int); 3. insert into A (field1) VALUES (3); 4. start transaction; select * from A where field1=3; in another shell start another trans: 5. alter table B add constraint "x" foreign key (field2) references A (field1) on update no action --> hanging lock now, since waiting for query 4 btw: mention that database engines lik mssql are very flesible in such scenarios, which makes it feeling robust. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] Join/table alias bug
Hi, I could not understand why I was getting 6 rows back, when I should only have been getting one back, until I realised that I had given an alias for the table 'fund_class' without using it in the first case. If I use the alias I get the expected result. Perhaps this should raise an error, but I think the two queries should not give a different results. This is with postgres 7.0beta5 on Dec-Alpha. select f.fc_id,it.el_id,ip.ip_id,m.c_id,m.ip_id from ip_categories cat, ip_cat_items it, ip_cat_map m, ip_item ip, fund_class f where cat.cat_table='fund_class' and cat.cat_id=it.cat_id and it.el_id=fund_class.fc_id and m.c_id=it.c_id and m.ip_id=ip.ip_id; fc_id | el_id | ip_id | c_id | ip_id ---+---+---+--+--- 2 | 6 | 6 |9 | 6 3 | 6 | 6 |9 | 6 5 | 6 | 6 |9 | 6 4 | 6 | 6 |9 | 6 7 | 6 | 6 |9 | 6 6 | 6 | 6 |9 | 6 (6 rows) select f.fc_id,it.el_id,ip.ip_id,m.c_id,m.ip_id from ip_categories cat, ip_cat_items it, ip_cat_map m, ip_item ip, fund_class f where cat.cat_table='fund_class' and cat.cat_id=it.cat_id and it.el_id=f.fc_id and m.c_id=it.c_id and m.ip_id=ip.ip_id; fc_id | el_id | ip_id | c_id | ip_id ---+---+---+--+--- 6 | 6 | 6 |9 | 6 (1 row) Adriaan
[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] not sorted clustered index (8.2)
hi, As part of the definition of a clustered index, the default sort of a table is based on the clustered index. In our application sometimes we see that the sort is invalid. Our table is like: iid identity (clustered primary key) data varchar if we do a query like: select * from table where iid in (1,2,3) we suspect that we get the result based on the iid, but sometimes this is not happening. Is this a known issue? our configuration is postgresql 8.2 on ubuntu greetings, Adriaan ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[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 #4097: can join + where query be optimized?
The following bug has been logged online:
Bug reference: 4097
Logged by: Adriaan van Kekem
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.1
Operating system: Ubuntu 7.04
Description:can join + where query be optimized?
Details:
In the following query you see 2 times the same part between ###. if i ONLY
set this filter by the where, the query is not performing at all. If i add
the filter by both the join condition and at the where part, it is
performing!
Can this be a standard optimization in postgres? We where thinking of the
procedure:
- check if the where can be done on an index
- if yes, add the condition also to the join condition.
What do u guys think of this?
SELECT "Zoekenclientbooster_data".data, "Zoekenclientbooster_data".iid,
"Zoekenclientbooster_data".locked
FROM "Zoekenclientbooster_data" inner join "Zoekenclientbooster_keys" as
sortkeys on
sortkeys.iid = "Zoekenclientbooster_data".iid
### and sortkeys.name='burgerservicenummer' ###
inner join "Zoekenclientbooster_keys" as "searchkeyseinddatum" on
sortkeys.iid = "searchkeyseinddatum".iid
and "searchkeyseinddatum".name = 'einddatum' AND
("searchkeyseinddatum".value_index >= '/2008|s04|s07/') inner join
"Zoekenclientbooster_keys" as "searchkeysmedewerkeriid" on sortkeys.iid =
"searchkeysmedewerkeriid".iid
and "searchkeysmedewerkeriid".name = 'medewerkeriid' AND
("searchkeysmedewerkeriid".value_index = '//') inner join
"Zoekenclientbooster_keys" as "searchkeysnaam" on sortkeys.iid =
"searchkeysnaam".iid
and "searchkeysnaam".name = 'naam' AND
("searchkeysnaam".value_index LIKE '/jansen%/')
WHERE
### sortkeys.name='burgerservicenummer' ###
order by sortkeys.value_index ASC LIMIT 9 OFFSET 0
--
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 #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
