Re: [HACKERS] Formulating an sql query with CTID

[Tzahi Fadida]

10x, 
Found my answer thanks to Ragnar.
SELECT DISTINCT ON (a0,a1) a0,a1,ctid FROM t1 

To tell the truth, i did not know there was an ON option in SQL.
This thing is very handy.

On Saturday 29 July 2006 03:37, Tzahi Fadida wrote:
> Hi,
> I have a query i am having trouble to formulate:
> I used to do:
> SELECT DISTINCT a0,a1 FROM public.t1
> However, now i need to add the CTID attribute, but CTID is unique
> thus, distinct is useless. In addition, i can't seem to be able to use
> max() etc... or casting to anything on CTID in order to use group by
> technique.
>
> What are my options?
>
> (I am using 8.2 or 8.1)

-- 
Regards,
Tzahi.
--
Tzahi Fadida
Blog: http://tzahi.blogsite.org | Home Site: http://tzahi.webhop.info
WARNING TO SPAMMERS:  see at 
http://members.lycos.co.uk/my2nis/spamwarning.html

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] Possible explanation for Win32 stats regression test

[[EMAIL PROTECTED]]

Is anyone working on this?

Tom Lane wrote:
> korry <[EMAIL PROTECTED]> writes:
> > The problem is that, each time you go through
> > pgwin32_waitforsinglesocket(), you tie the *same* kernel object
> > (waitevent is static) to each socket.
> 
> > The fix is pretty simple - just call WSAEventSelect( s, waitevent, 0 )
> > after WaitForMultipleObjectsEx() returns.  That disassociates the socket
> > from the Event (it will get re-associated the next time
> > pgwin32_waitforsingleselect() is called.  
> 
> Hmm.  Presumably we don't do this a whole lot (use multiple sockets) or
> we'd have noticed before.  Perhaps better would be to keep an additional
> static variable saying which socket the event is currently associated
> to, and only issue the extra WSAEventSelect calls if we need to change
> it.  Or is WSAEventSelect fast enough that it doesn't matter?
> 



Here's a simple patch that fixes the problem (I haven't explored the performance of this patch compared to Tom's suggestion).

    -- Korry


Index: src/backend/port/win32/socket.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/port/win32/socket.c,v
retrieving revision 1.11
diff -w -c -r1.11 socket.c
*** src/backend/port/win32/socket.c	5 Mar 2006 15:58:35 -	1.11
--- src/backend/port/win32/socket.c	29 Jul 2006 12:13:19 -
***
*** 132,137 
--- 132,154 
  	events[1] = waitevent;
  	r = WaitForMultipleObjectsEx(2, events, FALSE, INFINITE, TRUE);
  
+ 
+ 	/*
+ 	 * NOTE: we must disassociate this socket from waitevent - if we don't, then 
+ 	 *   we may accidentally fire waitevent at some point in the future if,
+ 	 *		 for example, the socket is closed.  That normally would not be a 
+ 	 *		 problem, but if you ever have two (or more) sockets in a single 
+ 	 *		 backend, they *ALL* share the same waitevent. So, if you pass through
+ 	 *		 this function for socket1 and socket2, a close on EITHER socket will
+ 	 *		 trigger an FD_CLOSE event, regardless of whether you're waiting for
+ 	 *		 socket1 or socket2.  That means that if you are waiting for socket1
+ 	 *		 and socket2 gets some interesting traffic (an FD_CLOSE or FD_READ
+ 	 *		 event for example), the above call to WaitForMultipleObjectsEx() 
+ 	 *		 will return even though nothing actually happened to socket1. Nasty...
+ 	 */
+ 
+ 	WSAEventSelect(s, waitevent, 0 );
+ 
  	if (r == WAIT_OBJECT_0 || r == WAIT_IO_COMPLETION)
  	{
  		pgwin32_dispatch_queued_signals();

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [HACKERS] On-disk bitmap index patch

[Bruce Momjian]

Luke Lonergan wrote:
> Mark, 
> 
> > -Original Message-
> > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
> > Sent: Friday, July 28, 2006 9:26 PM
> > 
> > But irrefutable? Irrefutable is not true. :-)
> 
> How about unrefuted.  The evidence has not been refuted, and not
> directly discussed or discounted.
> 
> BTREE can not be optimized to produce the results we've presented, the
> discussion about char(n) datatypes was irrelevant as we had shown
> results for INT, numeric and char/varchar and they were all dramatically
> better than BTREE.
> 
> I am hopeful this discussion takes a rapid turn toward the quantitative
> assessment of the results.

Right.  People need a patch to test on their workloads, and analysis can
be done after feature freeze.

-- 
  Bruce Momjian   [EMAIL PROTECTED]
  EnterpriseDBhttp://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

---(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: [PATCHES] [HACKERS] Possible explanation for Win32 stats regression

[Andrew Dunstan]

heh. I was just doing it the way Tom suggested - see attached. With a 
little more trouble we could also keep track if the listened for events 
and sometimes save ourselves a second call to WSAEventSelect, but I'm 
not sure it's worth it.


cheers

andrew


[EMAIL PROTECTED] wrote:

Is anyone working on this?

Tom Lane wrote:
> korry <[EMAIL PROTECTED] > writes:
> > The problem is that, each time you go through
> > pgwin32_waitforsinglesocket(), you tie the *same* kernel object
> > (waitevent is static) to each socket.
> 
> > The fix is pretty simple - just call WSAEventSelect( s, waitevent, 0 )

> > after WaitForMultipleObjectsEx() returns.  That disassociates the socket
> > from the Event (it will get re-associated the next time
> > pgwin32_waitforsingleselect() is called.  
> 
> Hmm.  Presumably we don't do this a whole lot (use multiple sockets) or

> we'd have noticed before.  Perhaps better would be to keep an additional
> static variable saying which socket the event is currently associated
> to, and only issue the extra WSAEventSelect calls if we need to change
> it.  Or is WSAEventSelect fast enough that it doesn't matter?
> 



Here's a simple patch that fixes the problem (I haven't explored the 
performance of this patch compared to Tom's suggestion).





Index: backend/port/win32/socket.c
===
RCS file: /cvsroot/pgsql/src/backend/port/win32/socket.c,v
retrieving revision 1.11
diff -c -r1.11 socket.c
*** backend/port/win32/socket.c	5 Mar 2006 15:58:35 -	1.11
--- backend/port/win32/socket.c	29 Jul 2006 14:19:23 -
***
*** 106,111 
--- 106,112 
  pgwin32_waitforsinglesocket(SOCKET s, int what)
  {
  	static HANDLE waitevent = INVALID_HANDLE_VALUE;
+ 	static SOCKET current_socket = -1;
  	HANDLE		events[2];
  	int			r;
  
***
*** 121,126 
--- 122,136 
  		ereport(ERROR,
  (errmsg_internal("Failed to reset socket waiting event: %i", (int) GetLastError(;
  
+ 	/*
+ 	 * make sure we don't multiplex this with a different socket 
+ 	 * from a previous call
+ 	 */
+ 
+ 	if (current_socket != s && current_socket != -1)
+ 		WSAEventSelect(current_socket, waitevent, 0);
+ 
+ 	current_socket = s;
  
  	if (WSAEventSelect(s, waitevent, what) == SOCKET_ERROR)
  	{

---(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

[HACKERS] pgindet ^M

[Tzahi Fadida]

I am trying to use pgindent but it adds ^M in my files.
However, it doesn't add it to postgresql src files (i tried).
I guess it is maybe something with encoding?
What can i do?
10x.

-- 
Regards,
Tzahi.
--
Tzahi Fadida
Blog: http://tzahi.blogsite.org | Home Site: http://tzahi.webhop.info
WARNING TO SPAMMERS:  see at 
http://members.lycos.co.uk/my2nis/spamwarning.html

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [HACKERS] pgindet ^M

[Andrew Dunstan]

Tzahi Fadida wrote:

I am trying to use pgindent but it adds ^M in my files.
However, it doesn't add it to postgresql src files (i tried).
I guess it is maybe something with encoding?
What can i do?
10x.

  


On what platform and on what files?

cheers

andrew

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [HACKERS] pgindet ^M

[Joshua D. Drake]

Andrew Dunstan wrote:

Tzahi Fadida wrote:

I am trying to use pgindent but it adds ^M in my files.
However, it doesn't add it to postgresql src files (i tried).
I guess it is maybe something with encoding?
What can i do?
10x.

  


On what platform and on what files?



Sounds like windows :)

Joshua D. Drake


cheers

andrew

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq




--

   === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
   Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/



---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [HACKERS] pgindet ^M

[Tzahi Fadida]

These are .c and .h files on linux.
Also i am using the patched indent and compiled the entab, etc...
From 8.2

On Saturday 29 July 2006 17:57, Andrew Dunstan wrote:
> Tzahi Fadida wrote:
> > I am trying to use pgindent but it adds ^M in my files.
> > However, it doesn't add it to postgresql src files (i tried).
> > I guess it is maybe something with encoding?
> > What can i do?
> > 10x.
>
> On what platform and on what files?
>
> cheers
>
> andrew

-- 
Regards,
Tzahi.
--
Tzahi Fadida
Blog: http://tzahi.blogsite.org | Home Site: http://tzahi.webhop.info
WARNING TO SPAMMERS:  see at 
http://members.lycos.co.uk/my2nis/spamwarning.html

---(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] Formulating an sql query with CTID

[Martijn van Oosterhout]

On Sat, Jul 29, 2006 at 03:37:15AM +0300, Tzahi Fadida wrote:
> Hi,
> I have a query i am having trouble to formulate:
> I used to do:
> SELECT DISTINCT a0,a1 FROM public.t1
> However, now i need to add the CTID attribute, but CTID is unique
> thus, distinct is useless. In addition, i can't seem to be able to use max()
> etc... or casting to anything on CTID in order to use group by technique.

Use DISTINCT ON, see the docs for details.

http://www.postgresql.org/docs/7.4/static/queries-select-lists.html

That's for an older version, but it still works the same, google isn't
delivering the newer version...

Have an nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

Re: [HACKERS] On-disk bitmap index patch

[Luke Lonergan]

Bruce,

On 7/29/06 6:31 AM, "Bruce Momjian" <[EMAIL PROTECTED]> wrote:

> Right.  People need a patch to test on their workloads, and analysis can
> be done after feature freeze.

Fair enough.

- Luke



---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [HACKERS] [PATCHES] putting CHECK_FOR_INTERRUPTS in

[Bruce Momjian]

Are we done with the sort interrupt issue mentioned in the subject line,
and the issue outlined below?

---

Tom Lane wrote:
> "Charles Duffy" <[EMAIL PROTECTED]> writes:
> > ... For the 'long' data, the compare moves on rightward until it
> > encounters 'flato', which is a TEXT column with an average length of
> > 7.5k characters (with some rows up to 400k). The first 6 columns are
> > mostly INTEGER, so compares on them are relatively inexpensive. All
> > the expensive compares on 'flato' account for the disproportionate
> > difference in sort times, relative to the number of rows in each set.
> 
> Yeah, and it's not just that it's text either.  At those sizes, all
> the values will be toasted, which means each compare is paying the
> price of fetching multiple rows from the toast table.  And decompressing
> them too, no doubt.  These costs are most likely swamping the actual
> strcoll() (not that that's not bad enough compared to int4cmp).
> 
> We could probably tweak the sorting code to forcibly detoast sort keys
> before beginning the sort, but I'm not entirely convinced that would be
> a win: reading and writing enormous sort keys won't be cheap either.
> 
> Meanwhile, for a cheap solution: do you really need to sort on flato
> at all?  Maybe sorting on substr(flato,1,100) would be good enough?
> 
>   regards, tom lane
> 
> ---(end of broadcast)---
> TIP 3: Have you checked our extensive FAQ?
> 
>http://www.postgresql.org/docs/faq

-- 
  Bruce Momjian   [EMAIL PROTECTED]
  EnterpriseDBhttp://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

---(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] [PATCHES] c.h is the problem of msvc.

[Bruce Momjian]

Patch applied.  Thanks.

---


Hiroshi Saito wrote:
> Ooops,
> I am uncertain at the reason for not knowing __BORLANDC__...
> It will be sure if __BORLANDC__ has the definition. 
> Thanks.
> 
> Regards,
> Hiroshi Saito
> 
> From: "Tom Lane"
> 
> 
> > "Hiroshi Saito" <[EMAIL PROTECTED]> writes:
> >> --- src/include/c.h.orig Sat Jul 15 01:38:59 2006
> >> +++ src/include/c.h Sat Jul 15 01:40:04 2006
> >> @@ -60,7 +60,9 @@
> >>  #if defined(_MSC_VER) || defined(__BORLANDC__)
> >>  #define WIN32_ONLY_COMPILER
> >>  #define errcode __vc_errcode
> >> +#if (_MSC_VER > 1400)
> >>  #include 
> >> +#endif
> >>  #undef errcode
> >>  #endif
> >>  
> > 
> > This patch certainly looks like it will break the __BORLANDC__ build ...
> > 
> > regards, tom lane

[ Attachment, skipping... ]

> 
> ---(end of broadcast)---
> TIP 2: Don't 'kill -9' the postmaster

-- 
  Bruce Momjian   [EMAIL PROTECTED]
  EnterpriseDBhttp://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

---(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

[HACKERS] DoS Attack Ended at Approx Noon Today ...

[Marc G. Fournier]

Just wanted to publicly thank the few ppl in Germany that offered to 
either call the ISP, who called the ISP, or / and offered to draft a 
letter *in* German for me to send to the ISP ...


Took awhile even after getting ahold of a 'human' at the ISP to actually 
get the IP shut down ...



Marc G. Fournier   Hub.Org Networking Services (http://www.hub.org)
Email . [EMAIL PROTECTED]  MSN . [EMAIL PROTECTED]
Yahoo . yscrappy   Skype: hub.orgICQ . 7615664

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] [PATCHES] c.h is the problem of msvc.

[Bruce Momjian]

Clarification, the attached version with the BORLAND adjustment is the
one that was applied.

---

Bruce Momjian wrote:
> 
> Patch applied.  Thanks.
> 
> ---
> 
> 
> Hiroshi Saito wrote:
> > Ooops,
> > I am uncertain at the reason for not knowing __BORLANDC__...
> > It will be sure if __BORLANDC__ has the definition. 
> > Thanks.

-- 
  Bruce Momjian   [EMAIL PROTECTED]
  EnterpriseDBhttp://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +
--- src/include/c.h.origSat Jul 15 01:38:59 2006
+++ src/include/c.h Sat Jul 15 01:40:04 2006
@@ -60,7 +60,9 @@
 #if defined(_MSC_VER) || defined(__BORLANDC__)
 #defineWIN32_ONLY_COMPILER
 #define errcode __vc_errcode
+#if defined(__BORLANDC__) || (_MSC_VER > 1400)
 #include 
+#endif
 #undef errcode
 #endif
 

---(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] Do we need multiple forms of the SQL2003 statistics

[Sergey E. Koposov]

Tom, thank you for the reviewing/correcting/applying my patches...

On Fri, 28 Jul 2006, Tom Lane wrote:


I wrote:

There is room to argue that the numeric-arithmetic version would be
worth having on the grounds of greater precision or range, but it's a
big chunk of code and the public demand for the functionality has not
exactly been overwhelming.



Comments?


Since no one's even bothered to respond, I take it there's insufficient
interest in the numeric versions of these aggregates.  I've committed
just the float8 versions.



My opinion on that is 
1) I agree that really the float4 version are useless, because they don't
offer anything new... comparing to the float8 
2) But I think the numeric versions of these functions are not useless...
(if somebody have numeric columns... ) 
I don't think the 10-15 additional functions in pg_proc is too much. 
Concerning to the amount of code in numeric.c I think it is large, but not

complicated at all, so I don't think that it will be a problem to support
that code.

I think since we are supporting the numeric type as a special 
high-precision type, Postgres must have the high-precision 
versions of all computational functions. Just my opinion.




I added some very trivial regression tests, which we'll have to keep an
eye on to see if they have any portability problems.  We may need to
back off the number of displayed fraction digits to get them to pass
everywhere.

If anyone wants to do better tests, feel free...


I will try to write some better tests and send a patch.

Regards,
Sergey

***
Sergey E. Koposov
Max Planck Institute for Astronomy/Sternberg Astronomical Institute
Tel: +49-6221-528-349
Web: http://lnfm1.sai.msu.ru/~math
E-mail: [EMAIL PROTECTED]

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] DTrace enabled build fails

[Peter Eisentraut]

Tom Lane wrote:
> Peter Eisentraut <[EMAIL PROTECTED]> writes:
> > Does it not like static functions?
>
> I seem to recall Robert mentioning that they'd only recently fixed
> DTrace to cope with probes in static functions.  Maybe you need to
> get an update?

That rings a bell.  Can we get a more precise designation on what 
version of DTrace we support?  And where can one get that required 
update?

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

---(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] Formulating an sql query with CTID

[Martijn van Oosterhout]

On Sat, Jul 29, 2006 at 01:37:44PM +0300, Tzahi Fadida wrote:
> 10x, 
> Found my answer thanks to Ragnar.
> SELECT DISTINCT ON (a0,a1) a0,a1,ctid FROM t1 
> 
> To tell the truth, i did not know there was an ON option in SQL.
> This thing is very handy.

There isn't, it's a postgresql extension, albeit a very very useful
one.

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

Re: [HACKERS] Do we need multiple forms of the SQL2003 statistics

[Peter Eisentraut]

Sergey E. Koposov wrote:
> I think since we are supporting the numeric type as a special
> high-precision type, Postgres must have the high-precision
> versions of all computational functions. Just my opinion.

Another way to look at it is whether you want to have accurate 
computations (numeric) or approximate computations (float).  I'm not a 
statistician, so I don't know what most of these functions are used 
for.  From a mathematician's point of view, however, some of these 
functions normally produce irrational numbers anyway, so it seems 
unlikely that numeric will be useful.  But looking at the definition 
of, say, regr_avgx(Y, X), if all the input values are integers, it 
might be useful if I could get an exact integer or rational number as 
output, instead of a float, that is.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] Do we need multiple forms of the SQL2003 statistics

[Sergey E. Koposov]

On Sat, 29 Jul 2006, Peter Eisentraut wrote:


Sergey E. Koposov wrote:

I think since we are supporting the numeric type as a special
high-precision type, Postgres must have the high-precision
versions of all computational functions. Just my opinion.


Another way to look at it is whether you want to have accurate
computations (numeric) or approximate computations (float).  I'm not a
statistician, so I don't know what most of these functions are used
for.  From a mathematician's point of view, however, some of these
functions normally produce irrational numbers anyway, so it seems
unlikely that numeric will be useful.  But looking at the definition
of, say, regr_avgx(Y, X), if all the input values are integers, it
might be useful if I could get an exact integer or rational number as
output, instead of a float, that is.


Exactly from the statistical point of view, there is no need to have the 
integer output of those 2-arg. aggregates. For example corr(), regr_*() 
are by definition not integer (they just don't have any sense as 
integers...)( -1<= corr(Y,X)<=1 ) (for example the stddev(int) do 
not produce int also, because it does not have any sense)


So it's perfectly fine that they are producing only floating numbers...

Regards,
Sergey

***
Sergey E. Koposov
Max Planck Institute for Astronomy/Sternberg Astronomical Institute
Tel: +49-6221-528-349
Web: http://lnfm1.sai.msu.ru/~math
E-mail: [EMAIL PROTECTED]

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [PATCHES] [HACKERS] Possible explanation for Win32 stats

[[EMAIL PROTECTED]]

heh. I was just doing it the way Tom suggested - see attached. With a 
little more trouble we could also keep track if the listened for events 
and sometimes save ourselves a second call to WSAEventSelect, but I'm 
not sure it's worth it.




It all depends on the overhead of WSAEventSelect().  I'm sure your version would run faster, but I just don't know if "slower" would be measurable.


BTW:  I would suggest changing your comment to:

   /*
    * make sure we don't multiplex this kernel event object with a different socket 
    * from a previous call
    */

Thanks for tackling this problem too.

    -- Korry






  Korry Douglas    [EMAIL PROTECTED]
  EnterpriseDB  http://www.enterprisedb.com

Re: [HACKERS] Formulating an sql query with CTID

[Hannu Krosing]

Ühel kenal päeval, L, 2006-07-29 kell 18:25, kirjutas Martijn van
Oosterhout:
> On Sat, Jul 29, 2006 at 03:37:15AM +0300, Tzahi Fadida wrote:
> > Hi,
> > I have a query i am having trouble to formulate:
> > I used to do:
> > SELECT DISTINCT a0,a1 FROM public.t1
> > However, now i need to add the CTID attribute, but CTID is unique
> > thus, distinct is useless. In addition, i can't seem to be able to use max()
> > etc... or casting to anything on CTID in order to use group by technique.
> 
> Use DISTINCT ON, see the docs for details.
> 
> http://www.postgresql.org/docs/7.4/static/queries-select-lists.html
> 
> That's for an older version, but it still works the same, google isn't
> delivering the newer version...

replace /7.4/ with /8.1/ to get a newer version ;)

> Have an nice day,
-- 

Hannu Krosing
Database Architect
Skype Technologies OÜ
Akadeemia tee 21 F, Tallinn, 12618, Estonia

Skype me:  callto:hkrosing
Get Skype for free:  http://www.skype.com



---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [PATCHES] [HACKERS] Possible explanation for Win32 stats regression

[Andrew Dunstan]

[EMAIL PROTECTED] wrote:
heh. I was just doing it the way Tom suggested - see attached. With a 
little more trouble we could also keep track if the listened for events 
and sometimes save ourselves a second call to WSAEventSelect, but I'm 
not sure it's worth it.



It all depends on the overhead of WSAEventSelect().  I'm sure your version would run 
faster, but I just don't know if "slower" would be measurable.
  


BTW:  I would suggest changing your comment to:

   /*
* make sure we don't multiplex this kernel event object with a 
different socket

* from a previous call
*/

Thanks for tackling this problem too.



Ok. Applied to HEAD and 8.1 and 8.0 branches.

cheers

andrew

---(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: [PATCHES] [HACKERS] pg_regress breaks on msys

[Tom Lane]

Bruce Momjian <[EMAIL PROTECTED]> writes:
> I modified pg_regress.c to use just the return code to determine if the
> diff worked, but I added in a WIN32-specific test for the file size.  I
> think that is the cleanest solution.  Attached.

It really needs a comment, along the lines of

/*
 * On Windows we'll get exit status 1 if the diff invocation
 * failed; so we need a way to distinguish failure from "files
 * are different".  Check to make sure that a diff file was
 * created and is nonempty.
 */

Also the test ought to account for file_size returning -1 if file's
not there, so

+ #ifdef WIN32
+   if (WEXITSTATUS(r) == 1 && file_size(filename) <= 0)
...

regards, tom lane

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] [PATCHES] putting CHECK_FOR_INTERRUPTS in

[Tom Lane]

Bruce Momjian <[EMAIL PROTECTED]> writes:
> Are we done with the sort interrupt issue mentioned in the subject line,
> and the issue outlined below?

I'm inclined not to apply the proposed patch (adding
CHECK_FOR_INTERRUPTS) because of the risk of memory leakage inside
qsort.  OTOH you could argue that there's an unfixable risk of memory
leakage there anyway, because it's always possible that the invoked
datatype comparison routine exits with elog(ERROR) for some reason,
or even contains a CHECK_FOR_INTERRUPTS call itself.  Comments?

As for the question of whether we should try to detoast sort keys before
sorting, I'd suggest adding that to TODO.  Investigating whether this
would be a good idea will take more time than we have for 8.2, so it's
gonna have to wait for a future cycle.

regards, tom lane

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [HACKERS] Do we need multiple forms of the SQL2003 statistics

[Tom Lane]

Peter Eisentraut <[EMAIL PROTECTED]> writes:
> ... From a mathematician's point of view, however, some of these 
> functions normally produce irrational numbers anyway, so it seems 
> unlikely that numeric will be useful.  But looking at the definition 
> of, say, regr_avgx(Y, X), if all the input values are integers, it 
> might be useful if I could get an exact integer or rational number as 
> output, instead of a float, that is.

The question is whether this is useful enough to justify adding a
thousand lines to numeric.c.

I believe also that the numeric forms of the aggregates will be
enormously slower than the float forms, at least on most modern
machines with decent floating-point performance.  I don't have time
to do any performance testing though.

regards, tom lane

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [HACKERS] Values list-of-targetlists patch for comments (was Re: [PATCHES]

[Joe Conway]

Tom Lane wrote:

If I'm reading the spec correctly, VALUES is exactly parallel to SELECT
in the grammar, which means that to use it in FROM you would need
parentheses and an alias:

SELECT ... FROM (SELECT ...) AS foo

SELECT ... FROM (VALUES ...) AS foo


One of the things I'm struggling with is lack of column aliases. Would 
it be reasonable to require something like this?


SELECT ... FROM (VALUES ...) AS foo(col1, col2, ...)

The other issue is how to determine column type. Even better would be to 
require (similar to SRF returning record):


SELECT ... FROM (VALUES ...) AS foo(col1 type1, col2 type2, ...)

This would unambiguously identify the column aliases and types. Assuming 
we stick with the spec:

SELECT ... FROM (VALUES ...) AS foo

1. How should we assign column names?
 values1, values2, ...?
   or
 col1, col2, ...?
   or
 ???

2. How should we assign datatypes? Use the first "row" and try to coerce
   the rest to that type?

Joe

---(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: Values list-of-targetlists patch for comments (was Re: [PATCHES] [HACKERS] 8.2 features?)

[Tom Lane]

Joe Conway <[EMAIL PROTECTED]> writes:
> One of the things I'm struggling with is lack of column aliases. Would 
> it be reasonable to require something like this?

>   SELECT ... FROM (VALUES ...) AS foo(col1, col2, ...)

Requiring column aliases is counter to spec ...

> The other issue is how to determine column type. Even better would be to 
> require (similar to SRF returning record):

>   SELECT ... FROM (VALUES ...) AS foo(col1 type1, col2 type2, ...)

... and this is even further away from it.

As for the names, just use "?column?", same as we do now in INSERT
... VALUES.  Anyone who wants to refer to those columns explicitly will
need to assign aliases, but if they don't assign aliases, we don't have
to do anything very intelligent.

As for the types, I believe that the spec pretty much dictates that we
apply the same type resolution algorithm as for a UNION.  This is fairly
expensive and we should avoid it in the case of INSERT ... VALUES, but
for VALUES appearing anywhere else I think we have little choice.

regards, tom lane

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [HACKERS] [PATCHES] New variable server_version_num

[Tom Lane]

Greg Sabino Mullane <[EMAIL PROTECTED]> writes:
> small patch to provide a new variable "server_version_num", which is
> almost the same as "server_version" but uses the handy PG_VERSION_NUM
> which allows apps to do things like if ($version >= 80200) without
> having to parse apart the value of server_version themselves.

This seems pretty useless, as it will be many years before any app that
actually tries to deal with back server versions could rely on the
variable existing.

The correct solution is for client-side libraries to provide the
feature.  libpq already does (PQserverVersion()) ... and it works
for any server version from about 6.4 forward ...

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] Values list-of-targetlists patch for comments (was Re: [PATCHES]

[Joe Conway]

Tom Lane wrote:

Joe Conway <[EMAIL PROTECTED]> writes:
One of the things I'm struggling with is lack of column aliases. Would 
it be reasonable to require something like this?



Requiring column aliases is counter to spec ...



SELECT ... FROM (VALUES ...) AS foo(col1 type1, col2 type2, ...)



... and this is even further away from it.


I figured as much, but thought I'd ask anyway :-). I did find something 
in the appendix to the spec after sending this:


Annex C
(informative)
Implementation-dependent elements

18) Subclause 7.3, “”:
a) The column names of a  or a typed table value constructor>

are implementation-dependent.


As for the names, just use "?column?", same as we do now in INSERT
... VALUES.  Anyone who wants to refer to those columns explicitly will
need to assign aliases, but if they don't assign aliases, we don't have
to do anything very intelligent.


OK, I just thought "?column?" was ugly and useless.


As for the types, I believe that the spec pretty much dictates that we
apply the same type resolution algorithm as for a UNION.  This is fairly
expensive and we should avoid it in the case of INSERT ... VALUES, but
for VALUES appearing anywhere else I think we have little choice.


Where do I find that algorithm -- somewhere in nodeAppend.c?

Thanks,

Joe


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] [PATCHES] putting CHECK_FOR_INTERRUPTS in

[Bruce Momjian]

Tom Lane wrote:
> Bruce Momjian <[EMAIL PROTECTED]> writes:
> > Are we done with the sort interrupt issue mentioned in the subject line,
> > and the issue outlined below?
> 
> I'm inclined not to apply the proposed patch (adding
> CHECK_FOR_INTERRUPTS) because of the risk of memory leakage inside
> qsort.  OTOH you could argue that there's an unfixable risk of memory
> leakage there anyway, because it's always possible that the invoked
> datatype comparison routine exits with elog(ERROR) for some reason,
> or even contains a CHECK_FOR_INTERRUPTS call itself.  Comments?

OK, we do check somewhere during sorting, I assume.  I can't imagine a
single qsort() call taking all that long because we do them in batches
anyway.

> As for the question of whether we should try to detoast sort keys before
> sorting, I'd suggest adding that to TODO.  Investigating whether this
> would be a good idea will take more time than we have for 8.2, so it's
> gonna have to wait for a future cycle.

Added to TODO:

* Consider detoasting keys before sorting

-- 
  Bruce Momjian   [EMAIL PROTECTED]
  EnterpriseDBhttp://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: Values list-of-targetlists patch for comments (was Re: [PATCHES] [HACKERS] 8.2 features?)

[Tom Lane]

Joe Conway <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> As for the types, I believe that the spec pretty much dictates that we
>> apply the same type resolution algorithm as for a UNION.

> Where do I find that algorithm -- somewhere in nodeAppend.c?

select_common_type(), in the parser.

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] The vacuum-ignore-vacuum patch

[Alvaro Herrera]

Alvaro Herrera wrote:

> Here is a patch pursuant to there ideas.  The main change is that in
> GetSnapshotData, a backend is skipped entirely if inVacuum is found to
> be true.

Patch applied.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

---(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] Going for "all green" buildfarm results

[Stefan Kaltenbrunner]

Tom Lane wrote:
> Stefan Kaltenbrunner <[EMAIL PROTECTED]> writes:
>> FWIW: lionfish had a weird make check error 3 weeks ago which I
>> (unsuccessfully) tried to reproduce multiple times after that:
> 
>> http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=lionfish&dt=2006-05-12%2005:30:14
> 
> Weird.
> 
>   SELECT ''::text AS eleven, unique1, unique2, stringu1 
> FROM onek WHERE unique1 < 50 
> ORDER BY unique1 DESC LIMIT 20 OFFSET 39;
> ! ERROR:  could not open relation with OID 27035
> 
> AFAICS, the only way to get that error in HEAD is if ScanPgRelation
> can't find a pg_class row with the mentioned OID.  Presumably 27035
> belongs to "onek" or one of its indexes.  The very next command also
> refers to "onek", and doesn't fail, so what we seem to have here is
> a transient lookup failure.  We've found a btree bug like that once
> before ... wonder if there's still one left?

FYI: lionfish just managed to hit that problem again:

http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=lionfish&dt=2006-07-29%2023:30:06



Stefan

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] Going for "all green" buildfarm results

[Alvaro Herrera]

Stefan Kaltenbrunner wrote:
> Tom Lane wrote:
> > Stefan Kaltenbrunner <[EMAIL PROTECTED]> writes:
> >> FWIW: lionfish had a weird make check error 3 weeks ago which I
> >> (unsuccessfully) tried to reproduce multiple times after that:
> > 
> >> http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=lionfish&dt=2006-05-12%2005:30:14
> > 
> > Weird.
> > 
> >   SELECT ''::text AS eleven, unique1, unique2, stringu1 
> > FROM onek WHERE unique1 < 50 
> > ORDER BY unique1 DESC LIMIT 20 OFFSET 39;
> > ! ERROR:  could not open relation with OID 27035
> > 
> > AFAICS, the only way to get that error in HEAD is if ScanPgRelation
> > can't find a pg_class row with the mentioned OID.  Presumably 27035
> > belongs to "onek" or one of its indexes.  The very next command also
> > refers to "onek", and doesn't fail, so what we seem to have here is
> > a transient lookup failure.  We've found a btree bug like that once
> > before ... wonder if there's still one left?
> 
> FYI: lionfish just managed to hit that problem again:
> 
> http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=lionfish&dt=2006-07-29%2023:30:06

The error message this time is

! ERROR:  could not open relation with OID 27006

It's worth mentioning that the portals_p2 test, which happens in the
parallel group previous to where this test is run, also accesses the
onek table successfully.  It may be interesting to see exactly what
relation is 27006.

The test alter_table, which is on the same parallel group as limit (the
failing test), contains these lines:

ALTER INDEX onek_unique1 RENAME TO tmp_onek_unique1;
ALTER INDEX tmp_onek_unique1 RENAME TO onek_unique1;

Maybe this is related.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

---(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] [PATCHES] New variable server_version_num

[David Fetter]

On Sat, Jul 29, 2006 at 09:44:10PM -0400, Tom Lane wrote:
> Greg Sabino Mullane <[EMAIL PROTECTED]> writes:
> > small patch to provide a new variable "server_version_num", which
> > is almost the same as "server_version" but uses the handy
> > PG_VERSION_NUM which allows apps to do things like if ($version >=
> > 80200) without having to parse apart the value of server_version
> > themselves.
> 
> This seems pretty useless, as it will be many years before any app
> that actually tries to deal with back server versions could rely on
> the variable existing.

In my case, its non-existence is a guarantee that the server version
number isn't high enough :)

> The correct solution is for client-side libraries to provide the
> feature.

Not if the app is written in SQL, as the bootstrap, regression test,
etc. code for modules frequently is.

> libpq already does (PQserverVersion()) ... and it works for any
> server version from about 6.4 forward ...

See above for why it's good also to have it surfaced to SQL :)

Cheers,
D
-- 
David Fetter <[EMAIL PROTECTED]> http://fetter.org/
phone: +1 415 235 3778AIM: dfetter666
  Skype: davidfetter

Remember to vote!

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster