Re: [BUGS] BUG #8467: Slightly confusing pgcrypto example in docs

[Richard Neill]

Dear Magnus,

Thanks for your reply.

On 24/09/13 18:31, Magnus Hagander wrote:

The following bug has been logged on the website:

Bug reference:  8467

The documentation for pgcrypto:
http://www.postgresql.org/docs/current/static/pgcrypto.html
(and indeed all versions from 8.3-9.3)
contains the following:



---[ ONE] -
Example of authentication:
SELECT pswhash = crypt('entered password', pswhash) FROM ... ;

 > This returns true if the entered password is correct.



I found this confusing, because it's  using the same name, "pswhash" in 2
places, one of which is a boolean. It would be, imho, clearer to write the
example query as:

[ TWO ]
SELECT is_authenticated = crypt('entered password', pswhash) FROM ... ;



That would render the example incorrect. crypt(pwd, hash) returns the
hash. Not a boolean. This hash needs to be compared to the stored one,
as is explained in the instructions above the example. It's the whole
expression, including the "pswhash = " that returns boolean.


I'm sorry about that: I think I need to correct my proposed correction! 
 I think I've been writing too much C recently, and so I foolishly 
mis-read that as returning pswhash, rather than returning the truth of 
the comparison.


What I meant to write, for clarity, was:

SELECT (pswhash = crypt('entered password', pswhash)) AS pswmatch FROM ... ;

which would make it obvious that we're returning the boolean named pswmatch.




[Also, should the default example perhaps use gen_salt('bf'), as opposed to
gen_salt('md5') ?]


This, however, might be a good idea. People should of course always
read the documentation, but having the examples including the "best
practice" would probably be a good idea.


Incidentally, there are 2 other things that confused me in this section.

1. Table F-18. Supported algorithms for crypt()  has a column labelled 
"max password length".  It would perhaps also be useful to know the size 
of column needed to store the crypted password (my original crypt using 
md5 easily fits in a varchar(70), whereas using bf needs the column to 
be varchar(100).)



2. Table F-20. Hash algorithm speeds

What's the difference here between "crypt-md5" and "md5" ?

If I've rightly read this, the algorithm named "md5" in the crypt() 
documentation is named "crypt-md5" here, whereas Table F20's "md5" 
algorithm seems to refer to something else - probably the "normal" 
version of md5.


If so, it would be clearer to write that the last 2 lines ("md5" and 
"sha1") are for comparison only, and refer to the speed of doing an 
ordinary md5/sha1 sum, rather than the md5-variant of crypt().



Anyway, thanks again for your help - Postgres is a wonderful system, 
which I've found to be repeatedly useful.



Best wishes,

Richard







--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #5867: wish: plpgsql print table for debug

[Richard Neill]

Note that doing anything more than RAISE NOTICE or equivalent would
imply a significant protocol change.  You can't just shove a table out
to the client, because it'll think that that's the response to the outer
SELECT (or whatever) command that called your function.  So while it'd
be kind of cool if you could invoke psql's table pretty-printing stuff
this way, the amount of work required to get there seems vastly out of
proportion to the benefit.



Dear Tom,

Thanks for your help. I agree that changing the protocol would be great 
overhead; I'm not really suggesting that. Perhaps I should give an 
example of what I mean


(1) Consider the following table, tbl_numbers:

   number  |  english   |  french |  german
   
  1 one  un   ein
  2 two  deux zwei
  3 threetroisdrei


(2) My desired debug function would be called this:

RAISE NOTICE_DEBUG  ("SELECT * from tbl_numbers")


(3) The resulting logfile would then contain multiple separate lines, 
each looking a bit like this:


 NOTICE:  numberenglishfrenchgerman
 NOTICE:  1 oneunein
 NOTICE:  2 twodeux  zwei
 NOTICE:  3 three  trois drei



While pretty-printing would be nice, I agree it's not really important.
It would be nice to add the same space-padding to each field for 
alignment, but delimiting with a single tab would be sufficient.



Richard




--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #5867: wish: plpgsql print table for debug

[Richard Neill]

Sure it does.  You can pass the tuple to RAISE NOTICE easily enough.
It won't have all the same bells and whistles psql would supply, but
it prints out well enough for debugging.  Or at least it's never
bothered me.


Sorry if I'm being dense, but I can't see how you can pass a tuple; I 
think raise-notice only lets you pass individual strings/integers. But I 
don't think we can pass all of them without specifying in advance how 
many there are




--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #5867: wish: plpgsql print table for debug

[Richard Neill]

Dear Pavel,

Thanks for your help.


Do you not think it would be really amazingly useful? After all, in C, the
single most useful debugging tool is "fprintf(stderr,...)", and yet
postgresql doesn't have an equivalent that can operate on the most common
data format. [I'm stretching the analogy a bit here, but it seems to me that
a multi-row table is to postgresql as int is to C.]


it's nonsense - PL/pgSQL is procedural language - so there are same -
similar types like C


Sorry - I perhaps over-stretched the analogy. What I meant was that, at 
least apparently, SQL "types"  include anything that can result from an 
SQL statement, including an individual "record" or an entire temporary 
table. I know that strictly speaking this isn't true, but it seems to me 
that one should be able to do:

  RAISE NOTICE (SELECT )



CREATE OR REPLACE FUNCTION debug_query(text)
RETURNS void AS $$
DECLARE r record;
BEGIN
   FOR r IN EXECUTE $1 LOOP
 RAISE NOTICE r;
   END;
END;
$$ LANGUAGE plpgsql;


Thanks for your help - but I'm afraid this doesn't actually work. psql 
rejects the line "RAISE NOTICE r;"


Raise notice expects a format string and some variables, very similar to 
printf(). This means that we'd have to write something like:

   RAISE NOTICE ('first %, second %, third %', col1, col2, col3;
except that our debug_query function doesn't know in advance how many 
columns there are, (or the types and their names).



Richard

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #5867: wish: plpgsql print table for debug

[Richard Neill]

The following bug has been logged online:

Bug reference:  5867
Logged by:  Richard Neill
Email address:  postgre...@richardneill.org
PostgreSQL version: 9.03
Operating system:   Linux
Description:wish: plpgsql print table for debug
Details:

When debugging a plpgsql function, it would be really amazingly useful to be
able to do a regular psql-style SELECT, and have the result printed to
screen.

Something like:

   Raise Notice table 'SELECT  '

and then plpgsql would run the query and dump the result to screen, using
its helpful formatting.

As far as I can see, this isn't possible (though there are a lot of people
searching for how to do it), and the only workaround is to manually handle
the looping and formatting, raising lots of individual notices. This makes
debugging much harder than it should be.


It wouldn't be too hard to write a loop that runs the select statement
and does RAISE NOTICE on each row.  Getting that into the psql
formatting would be a little trickier, but I don't see why you
couldn't write a PL/pgsql function to do it.  Then you could just call
that function and pass it an SQL query every time you want to do this.



I'm rather hoping that this would actually be an enhancement to 
PL/PGSQL, (or at least an officially documented howto) rather than just 
a private debugging function.


Do you not think it would be really amazingly useful? After all, in C, 
the single most useful debugging tool is "fprintf(stderr,...)", and yet 
postgresql doesn't have an equivalent that can operate on the most 
common data format. [I'm stretching the analogy a bit here, but it seems 
to me that a multi-row table is to postgresql as int is to C.]


There are a lot of people who would benefit from it, most of whom 
(including me) don't really have the expertise to do it well.


Also, there is a lot of value in being able to debug as needed with a 
1-line debugging statement, then get back to the problem at hand, rather 
than having to break out of the current programming task to write a 
debug function :-)


Thanks very much,

Richard


--
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 #5867: wish: plpgsql print table for debug

[Richard Neill]

The following bug has been logged online:

Bug reference:  5867
Logged by:  Richard Neill
Email address:  postgre...@richardneill.org
PostgreSQL version: 9.03
Operating system:   Linux
Description:wish: plpgsql print table for debug
Details: 

When debugging a plpgsql function, it would be really amazingly useful to be
able to do a regular psql-style SELECT, and have the result printed to
screen.

Something like:

   Raise Notice table 'SELECT  '

and then plpgsql would run the query and dump the result to screen, using
its helpful formatting.

As far as I can see, this isn't possible (though there are a lot of people
searching for how to do it), and the only workaround is to manually handle
the looping and formatting, raising lots of individual notices. This makes
debugging much harder than it should be.

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #5293: constant function (date_trunc) is repeatedly evaluated inside loop

[Richard Neill]

Kevin Grittner wrote:

"Richard Neill"  wrote:
 

date_trunc('day', timestamp '2010-01-20 10:16:55')
 
What happens with a "timestamp with time zone" literal?
 
-Kevin





Good call!

This query is fast:

SELECT count(1) FROM tbl_tracker WHERE ((srep_timestamp >= 
date_trunc('day', timestamp with time zone '2010-01-20 10:16:55') AND 
srep_timestamp <  date_trunc('day', timestamp with time zone '2010-01-20 
10:16:55') + INTERVAL '24 hour' )) ;



In other words:

#fast
WHERE column <  '2010-010-20 00:00:00'

#fast
WHERE column <  date_trunc('day', timestamp with time zone
 '2010-01-20 10:16:55')

#slow
WHERE column <  date_trunc('day', timestamp
 '2010-01-20 10:16:55')


Why is that, I wonder?

Richard

--
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 #5293: constant function (date_trunc) is repeatedly evaluated inside loop

[Richard Neill]

The following bug has been logged online:

Bug reference:  5293
Logged by:  Richard Neill
Email address:  rn...@cam.ac.uk
PostgreSQL version: 8.4.2
Operating system:   Linux
Description:constant function (date_trunc) is repeatedly evaluated
inside loop
Details: 

SUMMARY
---

If I have a WHERE clause such as this:

WHERE srep_timestamp >= date_trunc('day', timestamp '2010-01-20 10:16:55')
...

then I'd expect the query planner to evaluate the constant function 
  date_trunc('day', timestamp '2010-01-20 10:16:55')
once, outside the loop.

However, it doesn't do this. 

As a result, the query time doubles from 160ms to 340ms
compared to: 

WHERE srep_timestamp >= '2010-01-20 00:00:00') ...




DETAILS
---

Here are some actual results from a 250k row table. 
srep_timestamp has times roughly linearly distributed over a 2 day period
(with about 20% nulls). 
There is an index tbl_tracker_srepz_timestamp_idx on srep_timestamp WHERE
srep_timestamp is not null.

The measured times are consistent and repeatable.


SELECT count(1) FROM tbl_tracker WHERE ((srep_timestamp >= '2010-01-20
00:00:00') AND (srep_timestamp <  '2010-01-21 00:00:00') );
 count

 198577
(1 row)

Time: 158.084 ms

SELECT count(1) FROM tbl_tracker WHERE ((srep_timestamp >= date_trunc('day',
timestamp '2010-01-20 10:16:55') AND srep_timestamp <  date_trunc('day',
timestamp '2010-01-20 10:16:55') + INTERVAL '24 hour' )) ;
 count

 198577
(1 row)

Time: 341.155 ms


explain analyze SELECT count(1) FROM tbl_tracker WHERE ((srep_timestamp >=
'2010-01-20 00:00:00') AND (srep_timestamp <  '2010-01-21 00:00:00') );

 QUERY PLAN


--
 Aggregate  (cost=3181.17..3181.18 rows=1 width=0) (actual
time=663.651..663.652 rows=1 loops=1)
   ->  Bitmap Heap Scan on tbl_tracker  (cost=29.39..3177.97 rows=1279
width=0) (actual time=101.197..396.428 rows=198577 loops=1)
 Recheck Cond: ((srep_timestamp >= '2010-01-20
00:00:00+00'::timestamp with time zone) AND (srep_timestamp < '2010-01-21
00:00:00+00'::timestamp with time zone))
 ->  Bitmap Index Scan on tbl_tracker_srepz_timestamp_idx 
(cost=0.00..29.07 rows=1279 width=0) (actual time=98.417..98.417 rows=198577
loops=1)
   Index Cond: ((srep_timestamp >= '2010-01-20
00:00:00+00'::timestamp with time zone) AND (srep_timestamp < '2010-01-21
00:00:00+00'::timestamp with time zone))
 Total runtime: 663.769 ms
(6 rows)

Time: 665.087 ms


explain analyze SELECT count(1) FROM tbl_tracker WHERE ((srep_timestamp >=
date_trunc('day', timestamp '2010-01-20 10:16:55') AND srep_timestamp < 
date_trunc('day', timestamp '2010-01-20 10:16:55') + INTERVAL '24 hour' ))
;

 QUERY PLAN


--
 Aggregate  (cost=3181.17..3181.18 rows=1 width=0) (actual
time=827.424..827.425 rows=1 loops=1)
   ->  Bitmap Heap Scan on tbl_tracker  (cost=29.39..3177.97 rows=1279
width=0) (actual time=276.367..563.503 rows=198577 loops=1)
 Recheck Cond: ((srep_timestamp >= '2010-01-20 00:00:00'::timestamp
without time zone) AND (srep_timestamp < '2010-01-21 00:00:00'::timestamp
without time zone))
 ->  Bitmap Index Scan on tbl_tracker_srepz_timestamp_idx 
(cost=0.00..29.07 rows=1279 width=0) (actual time=275.020..275.020
rows=198577 loops=1)
   Index Cond: ((srep_timestamp >= '2010-01-20
00:00:00'::timestamp without time zone) AND (srep_timestamp < '2010-01-21
00:00:00'::timestamp without time zone))
 Total runtime: 827.534 ms
(6 rows)

Time: 828.763 ms



Thanks very much - Richard

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #5174: [minor] directories symlinked into base/ are not recursively removed

[Richard Neill]

Tom Lane wrote:

"Richard Neill"  writes:

If subdirectories of base/ are actually symlinks, then postgresql deletes
just the symlink, not the directory.


Doing that is completely unsupported, and we cannot be expected to cope
with random manual modifications to the structure of the database.  


That's a fair point, although as I understand it, it's reasonable to 
move, say, pg_xlog in that manner.


Why

didn't you use a tablespace like you're supposed to?


Because it wasn't supposed to be anything other than a temporary 
workaround, and because I had only 5 minutes of downtime in which to 
find more diskspace!


Anyway, I guess that's another "bug" report of mine that turns out to be 
useless :-)


Best wishes,

Richard



regards, tom lane



--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

[BUGS] BUG #5174: [minor] directories symlinked into base/ are not recursively removed

[Richard Neill]

The following bug has been logged online:

Bug reference:  5174
Logged by:  Richard Neill
Email address:  rn...@cam.ac.uk
PostgreSQL version: 8.4.1
Operating system:   Linux
Description:[minor] directories symlinked into base/ are not
recursively removed
Details: 

This is rather a minor nit, but it might be a useful report. If not, sorry
for wasting your time.

Summary: 
If subdirectories of base/ are actually symlinks, then postgresql deletes
just the symlink, not the directory.


To reproduce:

I had a system which was running out of disk space, and required a CLUSTER
to recover some. However, there wasn't actually enough space to run the
cluster operation.  Therefore, I moved some of the larger subdirectories in
base/ to a different partition and symlinked them back.

For example:

service postgresql stop
cd /var/lib/postgresql/8.4/main/base/
mv 12345  /elsewhere
ln -s  /elsewhere/12345 .
#repeat for a few directories.
service postgresql start

This allowed me to get the system running again, and to recover space on the
main partition.

On shutting down postgres again to clean up, I found that all the symlinks
were gone (good), but that the directories on the /elsewhere partition were
still all present, and full of (now-useless) data.

The (possible) bug:
I'd expect Postgresql to first follow the link, then recursively clean-out
the linked directory; then delete the link, leaving (at most) an empty
directory /elsewhere/12345

Actually, postgresql deleted the symlink, then left everything in /elsewhere
unchanged.

Comparison with rm:

mkdir a
touch a/b
ln -s a c
rm -rf c
#c is deleted; a and b remain untouched, 

i.e. it does exactly the same thing that Postgresql did.


Conclusion:
I don't know if this is intentional; if not, then a minor RFE would be to
fix it.

Thanks for your help - Richard

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] Postgresql 8.4.1 segfault, backtrace

[Richard Neill]

Dear Tom,

Thanks for this, and sorry for not replying earlier. We finally obtained 
a window to deploy this patch on the real (rather busy!) production 
system as of last Saturday evening.


The good news is that the patch has now been in place for 5 days, and, 
despite some very high loading, it has survived without a single crash.


I'd venture to say that this issue is now fixed.

Best wishes,

Richard




Tom Lane wrote:

I wrote:

I'll get you a real fix as soon as I can, but might not be till
tomorrow.


The attached patch (against 8.4.x) fixes the problem as far as I can
tell.  Please test.

regards, tom lane





Index: src/backend/utils/cache/relcache.c
===
RCS file: /cvsroot/pgsql/src/backend/utils/cache/relcache.c,v
retrieving revision 1.287
diff -c -r1.287 relcache.c
*** src/backend/utils/cache/relcache.c  11 Jun 2009 14:49:05 -  1.287
--- src/backend/utils/cache/relcache.c  25 Sep 2009 17:32:02 -
***
*** 1386,1392 
 *
 * The data we insert here is pretty incomplete/bogus, but it'll serve 
to
 * get us launched.  RelationCacheInitializePhase2() will read the real
!* data from pg_class and replace what we've done here.
 */
relation->rd_rel = (Form_pg_class) palloc0(CLASS_TUPLE_SIZE);
  
--- 1386,1394 

 *
 * The data we insert here is pretty incomplete/bogus, but it'll serve 
to
 * get us launched.  RelationCacheInitializePhase2() will read the real
!* data from pg_class and replace what we've done here.  Note in 
particular
!* that relowner is left as zero; this cues 
RelationCacheInitializePhase2
!* that the real data isn't there yet.
 */
relation->rd_rel = (Form_pg_class) palloc0(CLASS_TUPLE_SIZE);
  
***

*** 2603,2619 
 * rows and replace the fake entries with them. Also, if any of the
 * relcache entries have rules or triggers, load that info the hard way
 * since it isn't recorded in the cache file.
 */
hash_seq_init(&status, RelationIdCache);
  
  	while ((idhentry = (RelIdCacheEnt *) hash_seq_search(&status)) != NULL)

{
Relationrelation = idhentry->reldesc;
  
  		/*

 * If it's a faked-up entry, read the real pg_class tuple.
 */
!   if (needNewCacheFile && relation->rd_isnailed)
{
HeapTuple   htup;
Form_pg_class relp;
--- 2605,2635 
 * rows and replace the fake entries with them. Also, if any of the
 * relcache entries have rules or triggers, load that info the hard way
 * since it isn't recorded in the cache file.
+*
+* Whenever we access the catalogs to read data, there is a possibility
+* of a shared-inval cache flush causing relcache entries to be removed.
+* Since hash_seq_search only guarantees to still work after the 
*current*
+* entry is removed, it's unsafe to continue the hashtable scan 
afterward.
+* We handle this by restarting the scan from scratch after each access.
+* This is theoretically O(N^2), but the number of entries that actually
+* need to be fixed is small enough that it doesn't matter.
 */
hash_seq_init(&status, RelationIdCache);
  
  	while ((idhentry = (RelIdCacheEnt *) hash_seq_search(&status)) != NULL)

{
Relationrelation = idhentry->reldesc;
+   boolrestart = false;
+ 
+ 		/*

+* Make sure *this* entry doesn't get flushed while we work 
with it.
+*/
+   RelationIncrementReferenceCount(relation);
  
  		/*

 * If it's a faked-up entry, read the real pg_class tuple.
 */
!   if (relation->rd_rel->relowner == InvalidOid)
{
HeapTuple   htup;
Form_pg_class relp;
***
*** 2630,2636 
 * Copy tuple to relation->rd_rel. (See notes in
 * AllocateRelationDesc())
 */
-   Assert(relation->rd_rel != NULL);
memcpy((char *) relation->rd_rel, (char *) relp, 
CLASS_TUPLE_SIZE);
  
  			/* Update rd_options while we have the tuple */

--- 2646,2651 
***
*** 2639,2660 
RelationParseRelOptions(relation, htup);
  
  			/*

!* Also update the derived fields in rd_att.
 */
!   relation->rd_att->tdtypeid = relp->reltype;
!   relation->rd_att->tdtypmod = -1;  /* unnecessary, 
but... */
!   rel

[BUGS] Postgresql 8.4.1 segfault, backtrace

[Richard Neill]

Dear All,

I've just upgraded from 8.4.0 to 8.4.1 because of a segfault in 8.4, and 
we've found that this is still happening repeatedly in 8.4.1. We're in a 
 bit of a bind, as this is a production system, and we get segfaults 
every few hours.


[It's a testament to how good the postgres crash recovery is that, with 
a reasonably small value of checkpoint_segments = 4, recovery happens in 
30 seconds, and the warehouse systems seem to continue OK].



The version I'm using is 8.4.1, in the source package provided for 
Ubuntu Karmic, compiled by me on a 64-bit server (running Ubuntu Jaunty).


I'm not sufficiently expert to debug it very far, but I wonder whether 
the following info from GDB would help one of the hackers here (I've 
trimmed out the uninteresting bits):



$ gdb /usr/lib/postgresql/8.4/bin/postgres core.200909030901
GNU gdb 6.8-debian

This GDB was configured as "x86_64-linux-gnu"...

Core was generated by `postgres: fensys fswcs [local] startup 
 '.

Program terminated with signal 11, Segmentation fault.
[New process 14965]
#0  RelationCacheInitializePhase2 () at relcache.c:2654
2654if (relation->rd_rel->relhasrules && 
relation->rd_rules == NULL)

(gdb) bt
#0  RelationCacheInitializePhase2 () at relcache.c:2654
#1  0x7f61355a1021 in InitPostgres (in_dbname=0x7f613788c610 
"fswcs", dboid=0, username=0x7f6137889450 "fensys", out_dbname=0x0) at 
postinit.c:576
#2  0x7f61354dbcc5 in PostgresMain (argc=4, argv=0x7f6137889480, 
username=0x7f6137889450 "fensys") at postgres.c:3334

#3  0x7f61354aefdd in ServerLoop () at postmaster.c:3447
#4  0x7f61354afecc in PostmasterMain (argc=5, argv=0x7f6137885140) 
at postmaster.c:1040

#5  0x7f61354568ce in main (argc=5, argv=0x7f6137885140) at main.c:188
(gdb) quit
-

A few more bits of info:

The backtrace points to line 2654 in relcache.c, in
  RelationCacheInitializePhase2()

There is a NULL dereference of "relation"

 => needNewCacheFile = false
criticalRelcachesBuilt = true

=> nothing is happening before it enters the failure code block.


I can give you a core dump if anyone would like to see it, but it's 405 
MB after bzipping.


One last observation: a dump and restore of the DB seems to prevent it 
crashing for about a day.


Thank you for your help,

Richard

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #4972: RFE: convert timestamps to fractional seconds

[Richard Neill]

Dear Peter and Tom,

Thanks for your help. Sorry for posting an incorrect bug report. I hope 
there are still a few useful parts...



Tom Lane wrote:

"Richard Neill"  writes:

* Convert a timestamp into a number of seconds since
the epoch. This can be done in an ugly way using EXTRACT epoch FROM
timestamp, but only to  integer precision.


Uh, nonsense.

regression=# select extract(epoch from now());
date_part 
--

 1249884955.29859
(1 row)



You're quite right - I stand corrected. I'm sorry - my experiment was 
clearly faulty - and when I checked the documentation, I read:



SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16  
20:38:40-08');
Result: 982384720

and saw that the result was an integer. (which is correct, but it threw 
me off the scent).



Aside: I still contend that this isn't a very obvious way to do it, 
being hard to find in the documentation, and slightly inconsistent 
because every other EXTRACT option pulls out some fraction of the field. 
(eg Extract month gives the current month number, rather than the number 
of whole months elapsed since the epoch). Also, a shorthand function 
name for this would be helpful.




There are two places where I think the documentation on this page
http://www.postgresql.org/docs/8.3/static/functions-datetime.html
could be improved:

 (a) Table 9-26. Date/Time Functions doesn't contain ANY summary for how
 to get the seconds since the epoch. An initial look at EXTRACT
 would make it appear irrelevant.

 (b) Nowhere on the page is there a full example for getting
 seconds+microseconds since the epoch






* Division of a timestamp by an interval should result in something
dimensionless.


This isn't a particularly sane thing to think about, because intervals
aren't single numbers.




Peter Eisentraut wrote:
> On Monday 10 August 2009 03:41:06 Richard Neill wrote:
>> * Division of a timestamp by an interval should result in something
>> dimensionless.
>
> What would be the semantics of this?  What's today divided by 2 hours?
>


I see your point. But on the other hand, it's very common to talk about
   "distance (in metres) = 300"
or "50 seconds /  seconds   = 50"

What I think I meant was dividing a differential timestamp by an 
interval. In this case, both should be unambiguously expressed in 
seconds, and the result will be dimensionless.



For example:
select interval '3 weeks' / interval '1 week';
will fail, yet

select extract (epoch  from interval '3 weeks') / extract (epoch
from interval '1 week');
gives the correct answer of 3.




Do you agree that an explicit cast of a timestamp to a double should work?

Do you agree that abs() should be able to operate on an interval?
 select abs( interval '-1 week');


Thanks for your help,

Richard







--
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 #4972: RFE: convert timestamps to fractional seconds

[Richard Neill]

The following bug has been logged online:

Bug reference:  4972
Logged by:  Richard Neill
Email address:  rn...@cam.ac.uk
PostgreSQL version: 8.3.7
Operating system:   Linux
Description:RFE: convert timestamps to fractional seconds
Details: 

Postgresql has a huge range of functions to convert things TO timestamp
format, but no way to convert back again.

For example:

* Convert a timestamp into a number of seconds since
the epoch. This can be done in an ugly way using EXTRACT epoch FROM
timestamp, but only to  integer
precision. If I want to keep the microseconds, and get a float, it's not
possible. [Also, this is such a common requirement that it should probably
have a dedicated function, such as "time()" or maybe "epoch()". In PHP, this
is done by strtotime().]

* Division of a timestamp by an interval should result in something
dimensionless.

* So, for example, to check whether two timestamps (ts1 and ts2) are less
than 2.5 seconds apart, (returning boolean), I'd like to be able to do at
least one of:

  abs(time(ts1 - ts2)) < 2.5
  #A "time" function converts timestamp to 
  #sec.us since epoch)

  abs(cast (ts1 - ts2) as double)  < 2.5
  #cast to double, might have to implicitly divide
  #by the unit of "1 second"

  (ts1 - ts2) / INTERVAL '1 second'  < 2.5
  #Divide 2 dimensioned quantities to get 
  #a dimensionless one.


Currently, it's necessary to do something really really long-winded, eg:

(ts1 - ts2 >= 0 AND ts1 - ts2 < interval '2.5 seconds') OR (ts2 - ts1 >= 0
AND ts2 - ts1 < interval '2.5 seconds')


BTW,The abs() function doesn't work on an INTERVAL, though there is no
reason why it shouldn't.

Thanks - Richard

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #4916: wish: more statistical functions (median, percentiles etc)

[Richard Neill]

Dear Joshua,

Thanks for your reply. Sadly, I haven't the time (or expertise) to write 
this myself. However, the feature would be really useful to have. I'd 
certainly be willing to make a £200 payment or donation in return.


I'm aware that this number is problematic, as it undervalues developer 
time (£200 is the value, to me, of that feature; which is probably 
uncorrelated with the value of his time to anyone who might implement it).


The offer is there if anyone wants to take it; please feel free to 
contact me off-list.


Richard




Joshua Tolley wrote:

On Fri, Jul 10, 2009 at 11:37:46PM +, Richard Neill wrote:

In addition to the existing aggregate functions (avg, stddev etc),  it would
be nice if postgres could return further information. For example, the
quartiles, percentiles, and median.  


[mode would also be useful, as an explicit function, though we can get it
easily enough using count(1) order by count desc].

According to google, this has been a wish since at least year 2000 for
various people, but doesn't seem to be implemented. 


That's because no one has yet taken the time. However, patches are welcome, if
you'd like it enough to implement it. It's on my list of things that might be
interesting to write, for example, but there are other things higher up on
that list.

--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com


--
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 #4916: wish: more statistical functions (median, percentiles etc)

[Richard Neill]

The following bug has been logged online:

Bug reference:  4916
Logged by:  Richard Neill
Email address:  rn...@cam.ac.uk
PostgreSQL version: 8.4
Operating system:   Linux
Description:wish: more statistical functions (median, percentiles
etc)
Details: 

In addition to the existing aggregate functions (avg, stddev etc),  it would
be nice if postgres could return further information. For example, the
quartiles, percentiles, and median.  

[mode would also be useful, as an explicit function, though we can get it
easily enough using count(1) order by count desc].

According to google, this has been a wish since at least year 2000 for
various people, but doesn't seem to be implemented. 

Thanks - Richard

-- 
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 #4909: wish: easier way to configure RAM allocation

[Richard Neill]

The following bug has been logged online:

Bug reference:  4909
Logged by:  Richard Neill
Email address:  rn...@cam.ac.uk
PostgreSQL version: 8.4
Operating system:   Linux
Description:wish: easier way to configure RAM allocation
Details: 

I've been using Postgres for ages (thanks very much, btw), and I still find
the various memory-configuration options confusing. It's rather a black art
to decide what should be allocated where, and postgres probably knows better
than I do.

So my wish is a single configuration setting that says 

"I've bought a shiny new server with 8GB of RAM in it. Postgres can have up
to 6GB for its own use, and I'll let Postgres make its own choice on how
best to use it."

In particular, things like the working memory are complex to configure. If I
have one really complex query, in a single connection, I want postgres to
use all the RAM it has, and never swap to disk. But I can't set the work-mem
size too high, otherwise (I think), that, at some other point, multiple
apache instances with simple queries will fight over which gets unique
access to the entire memory allocation.

It's even more confusing because I want, wherever possible, for most of the
tables, and all the indices to stay in RAM. But should one leave this to
Linux and the file-cache, or should Postgres do it?

Furthermore, though I appreciate how amazing it is that Postgres can run in
16MB of RAM, on a server with 500 x that much memory, the default
configuration isn't very helpful in terms giving hints how to scale up.

Thanks,

Richard

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #1540: Enhancement request: 'ambiguous' column reference

[Richard Neill]

Dear Tom and Neil,
Thanks very much for your help, and your explanations. This makes a lot 
of sense, and I agree - this bug is definitely invalid.

Best wishes
Richard

Tom Lane wrote:
"Richard Neill" <[EMAIL PROTECTED]> writes:
SELECT instrument,priceband,pounds FROM tbl_instruments,tbl_prices WHERE
tbl_instruments.priceband=tbl_prices.priceband;

ERROR:  column reference "priceband" is ambiguous

I think that the first query ought to succeed, since although priceband is
ambiguous (it could mean either tbl_prices.priceband or
tbl_instruments.priceband), the information in the WHERE clause means that
they are explicitly equal, and so it doesn't matter which one we use.

Doing that would be contrary to the SQL specification, AFAICS.
However, you can get the effect you want by writing the query like
SELECT instrument,priceband,pounds FROM
tbl_instruments JOIN tbl_prices USING (priceband);
which both provides the join condition and logically merges the two
input columns into just one output column.
regards, tom lane
---(end of broadcast)---
TIP 3: 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 #1540: Enhancement request: 'ambiguous' column reference in psql

[Richard Neill]

The following bug has been logged online:

Bug reference:  1540
Logged by:  Richard Neill
Email address:  [EMAIL PROTECTED]
PostgreSQL version: 8.01
Operating system:   Linux
Description:Enhancement request: 'ambiguous' column reference in
psql
Details: 

Dear Postgresql team,

I have a small suggestion, which isn't quite a bug, but where psql throws an
error which it could in principle recover from. These occur when a column
reference is ambiguous, but isn't really, because of information supplied in
the join.


Here is an example, which I just tested in 8.01.


These are the database tables:
--
tbl_instruments:
instrument  character varying
priceband   smallint

--
tbl_prices:
priceband   smallint
pounds  double precision
---


This query fails:

SELECT instrument,priceband,pounds FROM tbl_instruments,tbl_prices WHERE
tbl_instruments.priceband=tbl_prices.priceband;

ERROR:  column reference "priceband" is ambiguous
---



This query succeeds:
-
SELECT instrument,tbl_instruments.priceband,pounds FROM
tbl_instruments,tbl_prices WHERE
tbl_instruments.priceband=tbl_prices.priceband;
--


I think that the first query ought to succeed, since although priceband is
ambiguous (it could mean either tbl_prices.priceband or
tbl_instruments.priceband), the information in the WHERE clause means that
they are explicitly equal, and so it doesn't matter which one we use.


Thank you very much for all your work - Postgresql is really useful to me.

Richard

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

   http://archives.postgresql.org

Re: [BUGS] BUG #1082: Order by doesn't sort correctly.

[Richard Neill]

Tom Lane wrote:
"PostgreSQL Bugs List" <[EMAIL PROTECTED]> writes:

Description:Order by doesn't sort correctly.


It almost certainly is the correct sort order according to the locale
you're using.  Use pg_controldata to check the database locale.  You'll
probably want to re-initdb in C locale.  Most non-C locales have weird
rules that try to approximate dictionary sort order.
			regards, tom lane

Dear Tom,

Thanks for your email. I did check pg_controldata and found:
LC_COLLATE:  en_GB
LC_CTYPE:en_GB
The bug isn't the particular ascii-betical (or other) order.
But what I am getting as a supposedly ordered list includes:
Cymbal #1
Cymbal - 18 inch
Cymbal #2
This ordering is perverse! No matter what the priority is of the 
different characters, I cannot understand how the above can arise.
Whether '#' comes before or after '-', '#1' and '#2' should be adjacent.

Richard





---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match

Re: [BUGS] BUG #1082: Order by doesn't sort correctly.

[Richard Neill]

Dear Tom,

Thank you for your explanation. It's very helpful, although I was 
extremely surprised! I agree, it's not a postgresql bug.

Can I suggest it might be worth a mention on the "Order By" part of the 
documentation.

i.e. this page:
http://www.postgresql.org/docs/7.3/static/sql-select.html#SQL-ORDERBY
could possibly use a little more emphasis of this last paragraph:
Data of character types is sorted according to the locale-specific 
collation order that was established when the database cluster was 
initialized.

or perhaps a link to here:
http://www.postgresql.org/docs/7.3/static/charset.html#AEN21582
I did realise that the sort would be locale dependent, but failed to 
realise it wasn't byte-at-a-time.

Best wishes

Richard

Tom Lane wrote:
Richard Neill <[EMAIL PROTECTED]> writes:

This ordering is perverse!


No kidding.


No matter what the priority is of the 
different characters, I cannot understand how the above can arise.


You are assuming that it's a byte-at-a-time process.  It's not.  I
believe the first pass considers only letters and digits.
You can easily prove to yourself that it's not just Postgres.  Here's
an example on my Linux laptop:
[EMAIL PROTECTED] tgl]$ cat zzz
Cymbal #1
Cymbal - 18 inch
Cymbal #2
[EMAIL PROTECTED] tgl]$ LC_ALL=C sort zzz
Cymbal #1
Cymbal #2
Cymbal - 18 inch
[EMAIL PROTECTED] tgl]$ LC_ALL=en_GB sort zzz
Cymbal #1
Cymbal - 18 inch
Cymbal #2
[EMAIL PROTECTED] tgl]$
			regards, tom lane

--
[EMAIL PROTECTED]  **  http://www.richardneill.org
Richard Neill, Trinity College, Cambridge, CB21TQ, U.K.
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org