Re: [HACKERS] default_text_search_config
Tatsuo Ishii [EMAIL PROTECTED] wrote: For me the idea that a text-search configuration maps to a locale/language seems to be totally wrong. IMO an encoding/charset could include several languages and a text-search configuration should be mapped to an encoding/charset, rather than a language. I think mapping by encoding/charset *is* totally wrong and by locale is reasonable. How do you treat LATIN1? It can be used in French and German, etc. Moreover, UTF-8 can be used in almost all languages. The tight mapping of EUC_jp = Japanese is a special case in the world. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] default_text_search_config
Tatsuo Ishii [EMAIL PROTECTED] wrote: For me the idea that a text-search configuration maps to a locale/language seems to be totally wrong. IMO an encoding/charset could include several languages and a text-search configuration should be mapped to an encoding/charset, rather than a language. I think mapping by encoding/charset *is* totally wrong and by locale is reasonable. How do you treat LATIN1? It can be used in French and German, etc. Moreover, UTF-8 can be used in almost all languages. The tight mapping of EUC_jp = Japanese is a special case in the world. What? I didn't say that an encoding/charset is mapped to single language. Actually EUC_JP includes Japanese, English(ascii), Greek, Cyrillic and so on. So for the full text search being able to process EUC_JP text properly, it should be able to process multiple languages at a time. You know that PostgreSQL allows only one locale for a PostgreSQL cluster, and the fact that text-search being depending on locale prevent it from processing multi language text. The only solution I can think of today is creating new parser which can process EUC_JP properly (I mean it can process not only Japanese but also English) and use it on C locale/EUC_JP cluster. I would do this for 8.4 if I have time. -- Tatsuo Ishii SRA OSS, Inc. Japan ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] default_text_search_config
Tatsuo Ishii [EMAIL PROTECTED] wrote: You know that PostgreSQL allows only one locale for a PostgreSQL cluster, and the fact that text-search being depending on locale prevent it from processing multi language text. The only solution I can think of today is creating new parser which can process EUC_JP properly (I mean it can process not only Japanese but also English) and use it on C locale/EUC_JP cluster. I would do this for 8.4 if I have time. The correct solution is probably we will have multiple locales in single database cluster. We should set the locale after deciding the encoding nowm, but I think the current implementation is wrong because locale depends on encoding, but the opposite is not true. (locale = 'language_country.*encoding*') If you will go to the multiple text-search support, we'd better to get done the locale issue first. It might affect your new parser. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] default_text_search_config
The correct solution is probably we will have multiple locales in single database cluster. We should set the locale after deciding the encoding nowm, but I think the current implementation is wrong because locale depends on encoding, but the opposite is not true. (locale = 'language_country.*encoding*') If you will go to the multiple text-search support, we'd better to get done the locale issue first. It might affect your new parser. I'm not sure the locale per database solution is a silver bullet. With this, still we cannot solve the issue, for example, a LATIN1 encoded text includes several languages at a time, thus it needs multiple locales. Or we cannot have multiple different language columns, tables at a time because it requires multiple locales. Same thing can be said to Unicode too. After all it seems a half baked solution to me. -- Tatsuo Ishii SRA OSS, Inc. Japan ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Release Notes Overview
My suggested edit of the Overview section of the Release Notes. The emphasis is on user-noticeable features, so some of the major internal changes are lower down the list. Some items have been removed or placed below the performance features. New data types for SQL/XML, enum and uuid types Updateable Cursors, plus support in PL/pgSQL. PostgreSQL now supports all major items of Core SQL:2003 compatibility. Full text Search is now a built-in feature, so is easier/better Asynchronous Commit allows some transactions to commit faster than others, offering a trade-off between performance and durability for specific transaction types only Database Size reductions both per-row and per-field Additional security features: GSSAPI/SSPI and easier to implement security-definer functions using per function SET parameters New ORDER BY ... NULLS FIRST/LAST option and matching support for indexes allows easier migration of applications from other RDBMS Better scalability and more consistent response times come from systematic removal of internal contention points within the server Performance improvements in many important workloads - update-intensive workloads now avoid index inserts via a new internal mechanism known as HOT, plus multiple concurrent auto-vacuum processes maintain the database more consistently - initial data loads now avoid writing WAL, plus all data loads use less CPU than previously - large table scans by optimising cache usage and allowing multiple synchronous scans to reuse the same data, avoiding I/O - short read-only transactions give reduced costs and higher scalability using lazy transactionid assignment Tracking of internal activity has many additional features, improving your ability to design, manage and maintain the database Self-adjusting background writer helps write-intensive workloads -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Release Notes Overview
Simon Riggs [EMAIL PROTECTED] writes: Asynchronous Commit allows some transactions to commit faster than others, offering a trade-off between performance and durability for specific transaction types only A lot of users will be confused about what asynchronous commit does. I think it's important to be consistently precise when describing it. It doesn't allow commits to be any faster, what it does is allow clients to start a new transaction and continue working without waiting for their previous commit to complete. Saying something like This allows high volumes of short transactions such as typical web sites to run more efficiently and with fewer connections might also help clarify the use case it helps. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(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: [HACKERS] default_text_search_config
I'm not sure the locale per database solution is a silver bullet. With this, still we cannot solve the issue, for example, a LATIN1 encoded text includes several languages at a time, thus it needs multiple locales. Or we cannot have multiple different language columns, tables at a time because it requires multiple locales. Same thing can be said to Unicode too. After all it seems a half baked solution to me. -- There is only one correct solution - support of COLLATES. With COLLATES you can choise locale per database, per table, per column, per db operation. This is one point where PostgreSQL is late over others. Pavel Stehule ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] EXPLAIN doesnt show Datum sorts explicitly
Something to add to the TODO: EXPLAIN doesn't show Datum sorts explicitly that occur because of DISTINCT aggregates in the SELECT clause. EXPLAIN looks like this postgres=# explain select count(distinct col3) from blah3; QUERY PLAN --- Aggregate (cost=5221.00..5221.01 rows=1 width=4) - Seq Scan on blah3 (cost=0.00..4471.00 rows=30 width=4) (2 rows) though trace_sort demonstrates sort(s) are taking place. This can also be seen in the start up cost and total cost of the Aggregate node, but the unwary might not notice the sorts taking place when viewing the EXPLAIN. It might also account for some people thinking SeqScans are slow on Postgres. Perhaps we can add a distinct sorts=N line to that node? This would need reconsidering anyway for when/if we have hashed distinct processing, since there's currently nowhere to hang the differing explain output that would generate. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Polymorphic arguments and composite types
I have a few questions from recent attempts to perform a join between
two tables, where one table has an integer array in it. Join is of the
form:
select ... from t1 where col1 = any (select col2 from t2);
Not sure whether these are bugs, intentional, incomplete functionality.
I've solved the problem, but not in a very straightforward manner.
Here's a simpler example that shows the problem I hit.
postgres=# \d c
Table public.c
Column | Type| Modifiers
+---+---
col1 | integer |
col2 | integer[] |
postgres=# select * from c;
col1 | col2
--+---
1 | {1,2}
(1 row)
postgres=# select * from c where col1 = any ('{1,2}');
col1 | col2
--+---
1 | {1,2}
(1 row)
postgres=# select * from c where col1 = any (col2);
col1 | col2
--+---
1 | {1,2}
(1 row)
...which is fine on just one table, but I want to join...
postgres=# select * from c where col1 = any (select col2 from c);
ERROR: operator does not exist: integer = integer[]
HINT: No operator matches the given name and argument type(s). You
might need to add explicit type casts.
postgres=# select * from c where col1 = any (ARRAY(select col2 from c));
ERROR: could not find array type for data type integer[]
Q1: Why not?? In the SELECT clause a sub-select returning a single
column is allowed, but it seems not here. Maybe a composite type issue?
Doesn't appear to be, since it knows type is integer[]
postgres=# select col1, (select col2 from c) as col2 from c;
col1 | col2
--+---
1 | {1,2}
(1 row)
So we now try to create a function to do this instead...
postgres=# create function func() returns anyarray as $$
declare
val integer[];
begin
select col2 into val from c;
return val;
end;
$$ language plpgsql;
ERROR: cannot determine result data type
DETAIL: A function returning a polymorphic type must have at least one
polymorphic argument.
Q2: Why does a function returning a polymorphic type have to have at
least one polymorphic argument? It's easy to create a function that
returns a polymorphic result yet has no linkage at all to the input.
postgres=# create function func(inval anyelement) returns anyarray as $$
declare
val integer[];
begin
select col2 into val from c;
return val;
end;
$$ language plpgsql;
CREATE FUNCTION
postgres=# select func(1);
func
---
{1,2}
(1 row)
postgres=# select * from c where col1 = any (select func(1));
ERROR: operator does not exist: integer = integer[]
HINT: No operator matches the given name and argument type(s). You
might need to add explicit type casts.
...same error, which is good news I suppose
postgres=# select * from c where col1 = any (func(1));
col1 | col2
--+---
1 | {1,2}
(1 row)
Q3: Why is a composite type with just one attribute not the same type as
the attribute? We know this is possible in the SELECT list, but we don't
know its the same thing in other contexts.
--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] First steps with 8.3 and autovacuum launcher
On Thu, 2007-10-04 at 17:33 -0400, Alvaro Herrera wrote: Simon Riggs escribió: Seems like we don't need to mess with the deadlock checker itself. We can rely on the process at the head of the lock wait queue to sort this out for us. So all we need do is look at the isAutovacuum flag on the process that is holding the lock we're waiting on. If it isn't an autoANALYZE we can carry on with the main deadlock check. We just need a new kind of deadlock state to handle this, then let ProcSleep send SIGINT to the autoANALYZE and then go back to sleep, waiting to be reawoken when the auotANALYZE aborts. Ok, I think this makes sense. I can offer the following patch -- it makes it possible to determine whether an autovacuum process is doing analyze or not, by comparing the PGPROC of the running WorkerInfo list (the list has at most max_autovacuum_workers entries, so this is better than trolling ProcGlobal). Looks OK to me, thanks for noticing I glossed over the bit about how to tell whether it was an auto-ANALYZE. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(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] Polymorphic arguments and composite types
Simon Riggs [EMAIL PROTECTED] writes: ...which is fine on just one table, but I want to join... postgres=# select * from c where col1 = any (select col2 from c); ERROR: operator does not exist: integer = integer[] That isn't a join. Are you looking for something like select * from c, c as c2 where c.col1 = any (c2.col2) regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] default_text_search_config
Tatsuo Ishii [EMAIL PROTECTED] writes: You know that PostgreSQL allows only one locale for a PostgreSQL cluster, and the fact that text-search being depending on locale prevent it from processing multi language text. I think you are confusing the capabilities of tsearch with the fact that we have to pick one default setting. There's nothing that stops you from using a search configuration that includes multiple dictionaries for different languages. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Polymorphic arguments and composite types
On Fri, 2007-10-05 at 10:52 -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: ...which is fine on just one table, but I want to join... postgres=# select * from c where col1 = any (select col2 from c); ERROR: operator does not exist: integer = integer[] That isn't a join. Are you looking for something like I guess I was looking for the sub-select way of doing it, because I actually wanted to exclude rows. select * from c, c as c2 where c.col1 = any (c2.col2) That works, thanks. As I said, I already solved the problem a different way. I was looking to understand the 3 questions I raised along the way. Can you throw any light on those questions? 1. Why doesn't the subselect work? 2. Why does a function returning a polymorphic type have to have at least one polymorphic argument? It's easy to create a function that returns a polymorphic result yet has no linkage at all to the input. 3. Why is a composite type with just one attribute not the same type as the attribute? We know this is possible in the SELECT list, but we don't know its the same thing in other contexts. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(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: [HACKERS] Polymorphic arguments and composite types
Simon Riggs [EMAIL PROTECTED] writes: 1. Why doesn't the subselect work? Because x = ANY (SELECT y FROM ...) is defined by the SQL standard to involve performing x = y at each row of the SELECT output. There's no wiggle room there. The standard does not specify any meaning for x = ANY (not-a-SELECT) and we've shoehorned some array behavior into that gap, but it's completely different semantics. 2. Why does a function returning a polymorphic type have to have at least one polymorphic argument? So that the parser can figure out what type a particular call is supposed to return. 3. Why is a composite type with just one attribute not the same type as the attribute? Why in the world would you expect these to be the same? It'd be akin to claiming that a one-element array is the same as the element type. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] default_text_search_config
On Fri, 5 Oct 2007, Tom Lane wrote: Tatsuo Ishii [EMAIL PROTECTED] writes: You know that PostgreSQL allows only one locale for a PostgreSQL cluster, and the fact that text-search being depending on locale prevent it from processing multi language text. I think you are confusing the capabilities of tsearch with the fact that we have to pick one default setting. There's nothing that stops you from using a search configuration that includes multiple dictionaries for different languages. exactly ! regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(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: [HACKERS] Polymorphic arguments and composite types
On Fri, 2007-10-05 at 16:04 +0100, Simon Riggs wrote: select * from c, c as c2 where c.col1 = any (c2.col2) That works, thanks. As I said, I already solved the problem a different way. I was looking to understand the 3 questions I raised along the way. Can you throw any light on those questions? 1. Why doesn't the subselect work? You could do something like: SELECT * FROM c AS c1, c AS c2 WHERE ARRAY[c1.col1] @ ANY(SELECT c2.col2); Regards, Jeff Davis ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Polymorphic arguments and composite types
Jeff Davis [EMAIL PROTECTED] writes: You could do something like: SELECT * FROM c AS c1, c AS c2 WHERE ARRAY[c1.col1] @ ANY(SELECT c2.col2); Good point --- actually he could convert it back to the original subselect style, as long as he's using the correct operator: SELECT * FROM c WHERE ARRAY[col1] @ ANY(SELECT col2 FROM c); The one-element-array trick seems a bit awkward though. I wonder why we don't have an anyelement @ anyarray kind of operator... regards, tom lane ---(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: [HACKERS] Polymorphic arguments and composite types
On Fri, 2007-10-05 at 12:29 -0400, Tom Lane wrote: Jeff Davis [EMAIL PROTECTED] writes: You could do something like: SELECT * FROM c AS c1, c AS c2 WHERE ARRAY[c1.col1] @ ANY(SELECT c2.col2); Good point --- actually he could convert it back to the original subselect style, as long as he's using the correct operator: SELECT * FROM c WHERE ARRAY[col1] @ ANY(SELECT col2 FROM c); You're right, that's a better example. The one-element-array trick seems a bit awkward though. I wonder why we don't have an anyelement @ anyarray kind of operator... I thought we did -- until I decided to test my example in psql before hitting send. It certainly makes sense to me that we should have it. Regards, Jeff Davis ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Polymorphic arguments and composite types
On Fri, 2007-10-05 at 09:46 -0700, Jeff Davis wrote: On Fri, 2007-10-05 at 12:29 -0400, Tom Lane wrote: The one-element-array trick seems a bit awkward though. I wonder why we don't have an anyelement @ anyarray kind of operator... I thought we did -- until I decided to test my example in psql before hitting send. It certainly makes sense to me that we should have it. It does make sense to have one. (Thanks for the additional examples, guys). -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Polymorphic arguments and composite types
On Fri, 2007-10-05 at 11:42 -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: 1. Why doesn't the subselect work? Because x = ANY (SELECT y FROM ...) is defined by the SQL standard to involve performing x = y at each row of the SELECT output. There's no wiggle room there. The standard does not specify any meaning for x = ANY (not-a-SELECT) and we've shoehorned some array behavior into that gap, but it's completely different semantics. OK, so we should document it as not being possible. 2. Why does a function returning a polymorphic type have to have at least one polymorphic argument? So that the parser can figure out what type a particular call is supposed to return. The parser can look at the datatype of the RETURNS clause, it doesn't need to look at the datatype of the *input* arguments. That error looks like a bug to me. In my example the input datatype differed from the returns datatype, plus the input and output were totally disconnected. = Bug. 3. Why is a composite type with just one attribute not the same type as the attribute? Why in the world would you expect these to be the same? It'd be akin to claiming that a one-element array is the same as the element type. Because we already do exactly that here: select 1, (select col2 from c), 3; The inner select returns a ROW, yet we treat it as a single column value. I'll look at documenting that. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(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] Release Notes Overview
On Fri, 2007-10-05 at 11:24 +0100, Gregory Stark wrote: Simon Riggs [EMAIL PROTECTED] writes: Asynchronous Commit allows some transactions to commit faster than others, offering a trade-off between performance and durability for specific transaction types only A lot of users will be confused about what asynchronous commit does. I think it's important to be consistently precise when describing it. It doesn't allow commits to be any faster, what it does is allow clients to start a new transaction and continue working without waiting for their previous commit to complete. Saying something like This allows high volumes of short transactions such as typical web sites to run more efficiently and with fewer connections might also help clarify the use case it helps. The general shape of the overview was what I was looking at. I agree with your specific comment. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(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: [HACKERS] Polymorphic arguments and composite types
Simon Riggs [EMAIL PROTECTED] writes: I'll look at documenting that. I think the problem here is you've not bothered to read the manual, because all of these behaviors *are* documented; two of them are furthermore required by the SQL standard. regards, tom lane ---(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] Polymorphic arguments and composite types
On Fri, 5 Oct 2007, Simon Riggs wrote: Because we already do exactly that here: select 1, (select col2 from c), 3; The inner select returns a ROW, yet we treat it as a single column value. The inner select does not return a row. It's not a row subquery, it's a scalar subquery. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Not *quite* there on ecpg fixes
On Thu, Oct 04, 2007 at 02:12:14PM -0400, Tom Lane wrote: I see that libpq manufactures three different .def files, whereas the ecpg code is only making two. Is this OK or an oversight? I'm not Not knowing what the third one is for I deliberately created only two. If there is a reason for the 3rd I'lö surely add it. clear on the reason for the two different MSVC .def files in libpq. Also, do we actually care about supporting Borland builds of ecpg --- maybe we don't need the 'b' versions for ecpg? Fine with me. Do we need it in libpq? Or else we could remove if everywhere. Maybe someone's working on it. Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(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] Polymorphic arguments and composite types
On Fri, 2007-10-05 at 13:18 -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: I'll look at documenting that. I think the problem here is you've not bothered to read the manual, because all of these behaviors *are* documented; two of them are furthermore required by the SQL standard. Your thoughts aren't correct, but why so touchy? There is no comment in the manual here http://developer.postgresql.org/pgdocs/postgres/arrays.html#AEN6096 which is the logical place for it to live, but I explored other places too. Why would you object to improving the manual? -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Polymorphic arguments and composite types
On Fri, 2007-10-05 at 10:32 -0700, Stephan Szabo wrote: On Fri, 5 Oct 2007, Simon Riggs wrote: Because we already do exactly that here: select 1, (select col2 from c), 3; The inner select returns a ROW, yet we treat it as a single column value. The inner select does not return a row. It's not a row subquery, it's a scalar subquery. Thanks Stephan, Tom already explained that. My comments above were in response to Why would you think that? -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Polymorphic arguments and composite types
On Fri, 5 Oct 2007, Simon Riggs wrote: On Fri, 2007-10-05 at 10:32 -0700, Stephan Szabo wrote: On Fri, 5 Oct 2007, Simon Riggs wrote: Because we already do exactly that here: select 1, (select col2 from c), 3; The inner select returns a ROW, yet we treat it as a single column value. The inner select does not return a row. It's not a row subquery, it's a scalar subquery. Thanks Stephan, Tom already explained that. My comments above were in response to Why would you think that? Right, but I guess I couldn't see why you would consider that the same as treating a rowtype as a scalar, because when I look at that my brain converts that to a scalar subquery, so I guess I simply see the scalar. If we supported select 1, (select 2,3), select 4 giving something like (1,(2,3),4), I'd also have confusion over the case, but that should error. ---(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] Polymorphic arguments and composite types
On Fri, 2007-10-05 at 10:59 -0700, Stephan Szabo wrote: On Fri, 5 Oct 2007, Simon Riggs wrote: On Fri, 2007-10-05 at 10:32 -0700, Stephan Szabo wrote: On Fri, 5 Oct 2007, Simon Riggs wrote: Because we already do exactly that here: select 1, (select col2 from c), 3; The inner select returns a ROW, yet we treat it as a single column value. The inner select does not return a row. It's not a row subquery, it's a scalar subquery. Thanks Stephan, Tom already explained that. My comments above were in response to Why would you think that? Right, but I guess I couldn't see why you would consider that the same as treating a rowtype as a scalar, because when I look at that my brain converts that to a scalar subquery, so I guess I simply see the scalar. If we supported select 1, (select 2,3), select 4 giving something like (1,(2,3),4), I'd also have confusion over the case, but that should error. Well, my brain didn't... All I've said was that we should document it, to help those people that don't know they're SQL standard as good as the best people on this list. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(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: [HACKERS] Polymorphic arguments and composite types
On Fri, 5 Oct 2007, Simon Riggs wrote: On Fri, 2007-10-05 at 10:59 -0700, Stephan Szabo wrote: On Fri, 5 Oct 2007, Simon Riggs wrote: On Fri, 2007-10-05 at 10:32 -0700, Stephan Szabo wrote: On Fri, 5 Oct 2007, Simon Riggs wrote: Because we already do exactly that here: select 1, (select col2 from c), 3; The inner select returns a ROW, yet we treat it as a single column value. The inner select does not return a row. It's not a row subquery, it's a scalar subquery. Thanks Stephan, Tom already explained that. My comments above were in response to Why would you think that? Right, but I guess I couldn't see why you would consider that the same as treating a rowtype as a scalar, because when I look at that my brain converts that to a scalar subquery, so I guess I simply see the scalar. If we supported select 1, (select 2,3), select 4 giving something like (1,(2,3),4), I'd also have confusion over the case, but that should error. Well, my brain didn't... All I've said was that we should document it, to help those people that don't know they're SQL standard as good as the best people on this list. Where would you document this beyond 4.2 though? While I don't exactly like the wording of 4.2.9, it seems like it's already trying to say that. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Polymorphic arguments and composite types
On Fri, 2007-10-05 at 11:24 -0700, Stephan Szabo wrote: On Fri, 5 Oct 2007, Simon Riggs wrote: On Fri, 2007-10-05 at 10:59 -0700, Stephan Szabo wrote: On Fri, 5 Oct 2007, Simon Riggs wrote: On Fri, 2007-10-05 at 10:32 -0700, Stephan Szabo wrote: On Fri, 5 Oct 2007, Simon Riggs wrote: Because we already do exactly that here: select 1, (select col2 from c), 3; The inner select returns a ROW, yet we treat it as a single column value. The inner select does not return a row. It's not a row subquery, it's a scalar subquery. Thanks Stephan, Tom already explained that. My comments above were in response to Why would you think that? Right, but I guess I couldn't see why you would consider that the same as treating a rowtype as a scalar, because when I look at that my brain converts that to a scalar subquery, so I guess I simply see the scalar. If we supported select 1, (select 2,3), select 4 giving something like (1,(2,3),4), I'd also have confusion over the case, but that should error. Well, my brain didn't... All I've said was that we should document it, to help those people that don't know they're SQL standard as good as the best people on this list. Where would you document this beyond 4.2 though? While I don't exactly like the wording of 4.2.9, it seems like it's already trying to say that. Yeh, it does, but you're forgetting that my original complaint was that you couldn't use it in an ANY clause, which 4.2 does not exclude. Bearing in mind you can use a scalar subquery in lots of places, I thought it worth reporting. The ANY clause at 9.19.4 mentions a subquery, but doesn't say it can't be a scalar subquery; it doesn't restrict this to non-scalar subqueries. Searching in Arrays, 8.14.5 doesn't say it can't be a subquery either. I was aware of the potential for a scalar subquery, hence my surprise you couldn't use it there. Maybe others less familiar would not have tried the query formulation I did. Section 9.20.3 mentions ANY (array expression). The term array expression is not defined nor is there a link to where it is defined, nor is the term indexed. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Enforcing database encoding and locale match
Tom Lane wrote:
Alvaro Herrera [EMAIL PROTECTED] writes:
FWIW I tried this program here, and I get
C ... ANSI_X3.4-1968 - NO MATCH
POSIX ... ANSI_X3.4-1968 - NO MATCH
Note the funny name. Trying initdb with LC_ALL=C correctly uses
SQL_ASCII (I saw the special case in chklocale.c), but I'm wondering if
we should list those names explicitely.
Since we're already special-casing C/POSIX, I don't see a need.
It looks a bit hopeless to keep up with all the possibilities anyway
--- by my count we've tested four different platforms so far and
gotten four different answers for the CODESET name for C :-(
Linux ANSI_X3.4-1968
Darwin (empty)
Solaris 646
HP-UX roman8
There is some useful link:
http://www.simeji.com/bun/characterencoding_jvm142.txt
with aliases. I also checked all possible locales on Solaris nevada and
there are two new aliases and probably unsupported TIS620.2533 (thai)
encoding by postgres.
Patch with new aliases attached.
Zdenek
===
RCS file: /zfs_data/cvs_pgsql/cvsroot/pgsql/src/port/chklocale.c,v
retrieving revision 1.4
diff -c -r1.4 chklocale.c
*** src/port/chklocale.c2007/10/03 17:16:39 1.4
--- src/port/chklocale.c2007/10/05 17:55:10
***
*** 127,132
--- 127,133
{PG_WIN874, ???},
#endif
{PG_WIN1251, CP1251},
+ {PG_WIN1251, ansi-1251},
{PG_WIN866, CP866},
{PG_ISO_8859_5, ISO-8859-5},
***
*** 152,157
--- 153,159
{PG_BIG5, BIG5},
{PG_BIG5, BIG5HKSCS},
{PG_BIG5, CP950},
+ {PG_BIG5, Big5-HKSCS},
{PG_GBK, GBK},
{PG_GBK, CP936},
---(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
[HACKERS] Encoding and i18n
Reading the commit message about the TZ encoding issue I'm curious why this isn't a more widespread problem. How does gettext now what encoding we want messages in? How do we prevent things like to_char(now(),'month') from producing strings in an encoding different from the database's encoding? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] [SQL] Why does the sequence skip a number with generate_series?
Tom Lane wrote:
Alvaro Herrera [EMAIL PROTECTED] writes:
Shane Ambler wrote:
CREATE TABLE jefftest ( id serial, num int );
INSERT INTO jefftest (num) values (generate_series(1,10));
INSERT INTO jefftest (num) values (generate_series(11,20));
INSERT INTO jefftest (num) values (generate_series(21,30));
Don't use set-returning functions in scalar context.
I think what is actually happening is that the expanded targetlist is
nextval('seq'), generate_series(1,10)
On the eleventh iteration, generate_series() returns ExprEndResult to
show that it's done ... but the 11th nextval() call already happened.
If you switched the columns around, you wouldn't get the extra call.
If you think that's bad, the behavior with multiple set-returning
functions in the same targetlist is even stranger. The whole thing
is a mess and certainly not something we would've invented if we
hadn't inherited it from Berkeley.
regards, tom lane
Would a re-write be something worth adding to the todo list?
and/or maybe add something about this to the manual?
--
Shane Ambler
[EMAIL PROTECTED]
Get Sheeky @ http://Sheeky.Biz
---(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
