Entries tagged as sql

Related tags

S9Y: Getting all time exit url statistics

Posted by Doomshammer on Monday, March 26. 2007 at 13:28 in Anwendungen, Computer, English only, Privat, Thoughts, Web
Today I was searching for the count of exits from my blog to a specific URL I posted lately. Sadly the "Statistics" plugin of s9y only shows a brief list from the exits of the last 7 days or so, so I wrote a little SQL query to get the stats- maybe this could be interessting for someone else as well.

QUOTE:
SELECT SUM(count) AS "Exits", concat(scheme, '://', host, path) as "Exit URL"
  FROM serendipity_exits
 GROUP BY scheme, host, path
 ORDER BY Exits;


You can as well add a WHERE clause to get e. g. a specific host or path.



Defined tags for this entry: , , , ,

PostgreSQL - das offizielle Handbuch

Posted by Doomshammer on Saturday, February 10. 2007 at 15:35


Genau dieses Buch liegt jetzt auf meinem Tisch, nachdem ich es gestern vom Buchhaendler meines Vertrauens zugeschickt bekommen habe. Ich hab' gestern Abend ein paar Std. darin gelesen und ich muss sagen, es ist seine 34,- wirklich wert. Pures Wissen- einfach erklaert! Kann ich jedem empfehlen der mit PostgreSQL arbeitet.

PS: Ich muss sagen, mein Canon MP830 hat 'ne echt gute Scan-Qualitaet ;-)
Defined tags for this entry: , ,

Why indices are good

Posted by Doomshammer on Thursday, February 8. 2007 at 08:52 in Anwendungen, Arbeit, Computer, English only, Thoughts
I just saw that someone created a view on one of my DBs (statistics DB) to get the top IP that did requests to a webserver (over the last 10 minutes). The view looks like this:
QUOTE:
SELECT xxxxxx.remote_address AS "Remote Host", count(1) AS "Hits"
   FROM apache.xxxxxx
  WHERE xxxxxx.logdate > (now() - '00:10:00'::interval)
  GROUP BY xxxxxx.remote_address
  ORDER BY count(1) DESC;


No problem with it (at least not with the query). But I noticed that the load was unexpectedly when the view was executed. So I had a look that the query plan for this view:
QUOTE:
Sort  (cost=46678.83..46678.84 rows=1 width=17) (actual time=32655.224..32655.227 rows=1 loops=1)
   Sort Key: count(1)
   ->  HashAggregate  (cost=46678.81..46678.82 rows=1 width=17) (actual time=32655.065..32655.070 rows=1 loops=1)
         ->  Seq Scan on xxxxxxx  (cost=0.00..46678.49 rows=64 width=17) (actual time=32654.736..32654.877 rows=6 loops=1)
               Filter: (logdate > (now() - '00:10:00'::interval))
 Total runtime: 32655.806 ms


Full table-scan? WTF?! What an idiot!!!11XI!

So I quickly created an index on the logdate for that table and what's the result? Correct....:
QUOTE:
Sort  (cost=7.21..7.22 rows=1 width=17) (actual time=13.703..13.706 rows=1 loops=1)
   Sort Key: count(1)
   ->  HashAggregate  (cost=7.19..7.20 rows=1 width=17) (actual time=13.293..13.296 rows=1 loops=1)
         ->  Index Scan using xxxxxxx_idx_timestamp on xxxxxxxx  (cost=0.00..6.87 rows=64 width=17) (actual time=13.027..13.145 rows=6 loops=1)       
               Index Cond: (logdate > (now() - '00:10:00'::interval))
 Total runtime: 14.206 ms
(6 rows)


Defined tags for this entry: , , , , , ,

Mental note to myself...

Posted by Doomshammer on Friday, February 2. 2007 at 00:20 in Anwendungen, Computer, English only, Privat, Thoughts
...don't try to create indices at night (0.20h am) and wonder why it does not catch. I'm such a moron! D'Oh!
Defined tags for this entry: , , , , ,

Fibonacci in PL/PgSQL

Posted by Doomshammer on Saturday, January 20. 2007 at 15:59 in Anwendungen, English only, Fun, Privat, Thoughts
As if a Perl/Java/Lisp/C/Whitespace version of the fibonacci sequence isn't enough, here is my attempt in PL/PgSQL ;-)

QUOTE:
CREATE OR REPLACE FUNCTION fi(n integer) RETURNS integer AS 'BEGIN IF n < 2 THEN RETURN n; END IF; RETURN fi(n - 1) + fi(n - 2); END;' LANGUAGE 'plpgsql'; CREATE OR REPLACE FUNCTION f(x integer) RETURNS text AS 'DECLARE o TEXT = ''''; BEGIN FOR i IN 0..x LOOP o = o || ''f('' || i || ''): '' || fi(i) || ''\n''; END LOOP; RETURN o; END;' LANGUAGE 'plpgsql';


Results in:
QUOTE:
# SELECT f(10) AS "Fibonacci";
 Fibonacci
-----------
 f(0): 0
 f(1): 1
 f(2): 1
 f(3): 2
 f(4): 3
 f(5): 5
 f(6): 8
 f(7): 13
 f(8): 21
 f(9): 34
 f(10): 55


Defined tags for this entry: , , , , , , ,

[Update] How to get unique key inserts

Posted by Doomshammer on Friday, January 5. 2007 at 17:56 in Anwendungen, Computer, English only, Privat


I am currently rewritting my ordr interface from scratch, as it's currently using SQLite as backend which pretty much sucks. So during the rewrite I had the problem to avoid unique key violations when inserting the photos/photosets from the Flickr API to my database. The following solution helps to get this done:

QUOTE:
INSERT INTO ordr.photosets 
     SELECT 12345678, 
            87654321, 
            10, 
            'Testtitel', 
            'Just a long description Text', 
            '123MyUID@Flickr' 
     WHERE NOT EXISTS (
            SELECT sid FROM ordr.photosets WHERE sid = 12345678
     );

Side note: This works with PostgreSQL. I'm not sure if this will also work with other RDBMS. IIRC Sybase for instance has it's own function for such tasks. S. th. like this:

QUOTE:
IF NOT EXISTS
(SELECT * FROM MyTable WHERE name = 'FooBar')
INSERT INTO MyTable VALUES('FooBar', 50, 'Even more FooBar')


Update: AndrewSN from #postgresql on irc.freenode.net mentioned that my query has a race condition.
QUOTE:
[22:31:32] <AndrewSN> Doomshammer: if there's a concurrent insert in another transaction that isn't committed yet, then the "not exists" won't be able to see it
[22:32:09] <AndrewSN> Doomshammer: so your statement can still error out with duplicate key errors

Which of course is absolutelly true. So my example is not a good solution. But luckily PostgreSQL's exellent documentation has a solution for nearly every problem. So this is what AndrewSN pointed out:
http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-UPSERT-EXAMPLE

Thanks a lot AndrewSN!

Defined tags for this entry: , , , , ,

It works!

Posted by Doomshammer on Sunday, December 17. 2006 at 03:06 in Anwendungen, Arbeit, Computer, English only, Linux/Unix, Privat

QUOTE:
nnprod0=# SELECT VERSION();
version
--------------------------------------------------------------------------
PostgreSQL 8.2.0 on i386-pc-solaris2.11, compiled by GCC gcc (GCC) 3.4.6
(1 row)


Defined tags for this entry: , , , , , , ,

PostgreSQL 8.2 released

Posted by Doomshammer on Wednesday, December 6. 2006 at 08:24 in Anwendungen, Arbeit, Computer, English only, Linux/Unix, Privat

PostgreSQL 8.2.0 has been released yesterday. Find the release notes here.
Downloads can be found here.

Defined tags for this entry: , , , ,
(Page 1 of 1, totaling 8 entries)
View as PDF: This month | Full blog

Calendar

Back June '13
Mon Tue Wed Thu Fri Sat Sun
          1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30

Quicksearch

Not for Sale!

This blog is not for sale!

Latest twitter

Blog abonnieren

Latest Photos

ERROR: The HTTP server returned the error or the warning(result:403).

Last google searches

NTFS usb solaris
powered by SMF 2.0 1 minute funny poems

Getaggte Artikel

Kategorien



All categories

Buttons

Ich bin ein Dokuleser
Get OpenSolaris
Gimme a Smile
neessen.net - Webhosting
I'm a blogger!
Lebst Du noch oder oarks Du schon?
last.fm
trnd - be trendy
I hear Metal
Visit GeoURL
Powered by Linux
Zsh lover
VIM! The Editor
Get Firefox
Ihr, nicht ich!
My amazon wishlist

BLOGROLL