[GENERAL] Postgres on SSD

[Ondrej Ivanič]

Hi,

On 12 August 2011 14:57, Greg Smith  wrote:
> ioDrive hardware is fast at executing all sorts of I/O, but it particularly
> excels compared to normal drives with really random workloads.

That's what I hope for :). It looks like that ioDrive is 3 to 5 times
faster for seq IO comparing to our SAN

> Your tables are pretty big; not much of them will fit in memory.  If your
> aggregated queries end up executing a lot of sequential scans of the data
> set in order to compute, or for them to be utilized, you will probably
> discover this is barely faster on FusionIO.

About 99% disk activity is random IO (reads). Seq IO is caused by
reading last X minutes of data by aggregates cron job. Majority of the
queries are primary key scan queries plus some extra where condition
filtering. Default random_page_cost and seq_page_cost wasn't way to go
because planner chose seq scan and query execution took so long

> Is there a component to your workload that does a lot of random read or
> write requests?  If so, is that chunk of the data set bigger than RAM, but
> small enough to fit on the FusionIO drive?  Only when all those conditions
> are true does that hardware really make sense.

Yes, almost all queries do random IO. Final result set is the top 1000
rows only but several weeks of data must be read and sorted before
applying the limit. I have two cards available (= 2.4TB) so I can have
entire dataset there.

> But at the same time, tests on database sizes that fit into RAM were slower
> on FusionIO than the regular disk array.  When there's no random I/O to
> worry about, the slower read/write write of the SSD meant it lost the small
> database tests.

Makes sense to me.

>>  checkpoint_segments          | 48
>>  maintenance_work_mem         | 256MB
>>  shared_buffers               | 9GB
>>  wal_buffers                  | 50MB
>>  work_mem                     | 256MB
>>
>
> checkpoint_segments should be higher, at least 64 and probably 128 to 256.
>  shared_buffers should be lower (at most 8GB, maybe even less).
>  maintenance_work_mem should be 1 to 2GB on a server with 80GB of RAM.
>  There's no proven benefit to increasing wal_buffers over 16MB.

Yes, shared_buffers are high; based on your input and other sources I
would like to try lower values. I really do not understand this part
of the "magic" - I mean checkpoint_segments and WAL related settings.

> This setting for work_mem can easily allow your server to allocate over
> 250GB of RAM for query working memory, if all 100 connections do something.
>  Either reduce that a lot, or decrease max_connections, if you want this
> server to run safely.

Yes, I'm aware about this issue. There nothing like pg-pool between
Apache (PHP) and Postgres and there is no more than 15 connections
simultaneously

Thanks,
--
Ondrej Ivanic
(ondrej.iva...@gmail.com)



-- 
Ondrej Ivanic
(ondrej.iva...@gmail.com)

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

Re: [GENERAL] How to tame a gigantic (100+ lines) query in a web app?

[Rob Sargent]

W. Matthew Wilson wrote:

I'm sure I'm not the first person to end up with a gigantic query that
does lots of left joins and subselects.

It seems to work, but I would love to break it up into smaller chunks.

I'm thinking about rewriting the query to make several temporary
tables that are dropped on commit, and then joining them at the end.

I can't just use views for everything because I use parameters passed
in from the web app.  I am using a few views where I can.

Is there anything dangerous about making temporary tables in this way?
 I started two transactions simultaneously and they were both able to
make their own temporary tables.

More generally, how to tame this big ol' query?

The temporary tables mean I'm only pulling data from the database one
time.  ORMs often pull data from one query and then use that data to
write the next query.  This seems slow to me.

Matt


  

I'm a big fan of the temp table plan, even though they are the
epitome of the very thing of which you indict ORMs!  And you might be
wrong on that thesis but who cares, you're in hand-craft mode.  My very
biased take is that you will at worst break even performance-wise, and
probably come out on top, especially if a left join goes through an
intersection record.



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

Re: [GENERAL] Where to start, graphs and routing.

[Ondrej Ivanič]

Hi,

On 14 August 2011 20:25, k_b  wrote:
> Hi.
> For learning purpose i would like to make a small database with a small
> graph of locations, roads and public transport information.
> Then calculate the fastest or cheapest way between two points.
>
> If we think of a minimal network, as below.
>
> A ---5-- B ---10 C
>  \                 /
>  \-5-/

Welcome in the club! I've been there and I can say that is very
interesting exercise. My schema was simple:
- bus stop table: just list of all bus stop and related meta data
(like this bus stop is part of transit centre X, ...)
- schedule table: contains all possible combination how to travel
between two adjacent stops: (stopA, stopB, timeA, timeB, route_n).
Table had several million rows which was necessary because of the
following anomalies:
* A -> B could be 5 min but B -> A could be less or more
* peak / off peak / weekend schedule could be different
* you can take bus A -> B -> C but on the way back bus doesn't serve
stop B (ie C -> A)

It would be possible to limit number of row in that table using
smarter encoding system for bus departure/arrival times. I didn't use
it because I generated that table from official timetables.

queries were simple; first query was something like this
select * from schedule_table where stopA = :start
then for each row from the previous query (and repeat this query):
select * from schedule_table where stopA = :stopB and timeA <
result_timeB + :threshold

After the second, third, ... query I did the following checks:
- merge parts with the same bus number (ie A -> B, B -> C => A -> C)
- increment waiting/transfer and total travel time accordingly
- remove stupid routes. This part is quite tricky and some heuristics
is needed. I removed routes with many service changes and excessive
waiting/travel times

Today, I would try to use Postgres spatial data types/extensions
because you can get bus stop locations from openstreetmap (or google
maps). Moreover you can exclude bus stops (or complete routes) which
are too far from from/to locations (again, some heuristics/rules could
be necessary)

-- 
Ondrej Ivanic
(ondrej.iva...@gmail.com)

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

Re: [GENERAL] streaming replication: one problem & several questions

[rockwell]

Hi.

I've experienced very similar behavior and log message. I do not know what
is the problem in detail but my guess is that standby system has data which
is incompatible to accept streaming replication of the master.

The way I solved is the following on Ubuntu11.04. Suppose postgresql.conf
and recover.conf (on standby) are ready.

0. Stop postgresql on standby
standby# /etc/init.d/postgresql stop

1. invoke pg_start_backup() on master
master# sudo -u postgres psql -c "SELECT pg_start_backup('2011-08-15_04:49)"

2. remove data files on standby
standby# cd /var/lib/postgresql/9.0/main
standby# /bin/rm -rf base pg_*
standby# mkdir pg_xlog; chown postgres.postgres pg_xlog; chmod 700 pg_xlog

3. copy data files
master# rsync -av --delete /var/lib/postgresql/9.0/main --exclude=pg_xlog
--exclude=postmaster.pid  --exclude=server.crt --exclude=server.key
/path/to/standby/data/directory

4. invoke pg_stop_backup() on master
master# sudo -u postgres psql -c "SELECT pg_stop_backup()"

5. start postgresql on standby
standby# /etc/init.d/postgresql start

Then, I found streaming replication just started to work.

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/streaming-replication-one-problem-several-questions-tp4687602p4698911.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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

Re: [GENERAL] [ADMIN] Using Postgresql as application server

[c k]

Thanks for everyone.
I will give reply after some experiments.
Regards,

Chaitanya Kulkarni

On Sun, Aug 14, 2011 at 6:04 PM,  wrote:

> On Aug 14 2011, Uwe Schroeder wrote:
>
>
>>
>>  > can u please give me it's link.
>>> > I found ngx_postgres module.
>>> > >> there is nginx_htttp_postgresql_module
>>> >> exactly to connect webserver directly to postgresql
>>> >> and to OUTPUT query result to a browser.
>>>
>>> http://wiki.nginx.org/**3rdPartyModules
>>> http://labs.frickle.com/nginx_**ngx_postgres/
>>>
>>
>>
>  Personally I'd never ever hook my database directly to the internet - for
>> me that's like writing your PIN on your banking card and leave it next to
>> the ATM :-)
>>
>
>
> DSTABASE IS THE BEST TOOL TO AGGANGE AN ACCESS CONTROL SCHEME
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-ad...@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-admin
>

Re: [GENERAL] Compile pgadmin1-1.14.0-beta3 - ctlMenuToolbar

[Guillaume Lelarge]

On Sun, 2011-08-14 at 12:23 -0400, Colin Beckingham wrote:
> On 08/14/2011 12:16 PM, Guillaume Lelarge wrote:
> > On Sun, 2011-08-14 at 10:45 -0400, Colin Beckingham wrote:
> >> Using OpenSUSE 11.4, kernel 3.0.
> >> wxWidgets version 2.8.11.
> >> While compiling pgadmin1-1.14.0-beta3, configure completes correctly but
> >> make fails on:
> >>
> >> ./ctl/ctlMenuToolbar.cpp: In member function ‘void
> >> ctlMenuToolbar::DoProcessLeftClick(wxMouseEvent&)’:
> >> ./ctl/ctlMenuToolbar.cpp:161:2: error: ‘Node’ is not a member of
> >> ‘ctlMenuToolList’
> >> ./ctl/ctlMenuToolbar.cpp:161:25: error: ‘node’ was not declared in this
> >> scope
> >> make[3]: *** [ctlMenuToolbar.o] Error 1
> >> make[3]: Leaving directory
> >> `/home/colin/downloads/pgadmin3-1.14.0-beta3/pgadmin'
> >> make[2]: *** [all] Error 2
> >> make[2]: Leaving directory
> >> `/home/colin/downloads/pgadmin3-1.14.0-beta3/pgadmin'
> >> make[1]: *** [all-recursive] Error 1
> >> make[1]: Leaving directory `/home/colin/downloads/pgadmin3-1.14.0-beta3'
> >> make: *** [all] Error 2
> >>
> >> Suggestions?
> >
> > Not the best mailing list to talk about this (pgadmin-hackers or
> > -support would be better).
> >
> > Anyway, I don't have the issue on my laptop. With beta3, wxWidgets
> > 2.8.12, gcc 4.6, I have no problem compiling pgAdmin:
> >
> > ./configure --prefix=/opt/pgadmin-1.14
> >--with-pgsql=/opt/postgresql-head
> >--enable-debug
> >--with-wx=/opt/wxgtk-2.8
> >--with-wx-version=2.8
> > make
> > make install
> >
> > I don't think it has anything to do with the fact you have wxWidgets
> > 2.8.11 and me 2.8.12.
> >
> > Did you change the source? and which configure line do you use?
> >
> >
> 
> Thanks for the response. My apologies for posting in the wrong area, 
> unfamiliar territory, just recently started using Postgresql. I will 
> take the enquiry to a more appropriate area.
> 

Thanks.

> I did not change the source at all, just used a plain './configure' 
> which seemed to find everything.
> 

Can you tell me which version of gcc you're using?


-- 
Guillaume
  http://blog.guillaume.lelarge.info
  http://www.dalibo.com


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

Re: [GENERAL] Compile pgadmin1-1.14.0-beta3 - ctlMenuToolbar

[Colin Beckingham]

On 08/14/2011 12:16 PM, Guillaume Lelarge wrote:

On Sun, 2011-08-14 at 10:45 -0400, Colin Beckingham wrote:

Using OpenSUSE 11.4, kernel 3.0.
wxWidgets version 2.8.11.
While compiling pgadmin1-1.14.0-beta3, configure completes correctly but
make fails on:

./ctl/ctlMenuToolbar.cpp: In member function ‘void
ctlMenuToolbar::DoProcessLeftClick(wxMouseEvent&)’:
./ctl/ctlMenuToolbar.cpp:161:2: error: ‘Node’ is not a member of
‘ctlMenuToolList’
./ctl/ctlMenuToolbar.cpp:161:25: error: ‘node’ was not declared in this
scope
make[3]: *** [ctlMenuToolbar.o] Error 1
make[3]: Leaving directory
`/home/colin/downloads/pgadmin3-1.14.0-beta3/pgadmin'
make[2]: *** [all] Error 2
make[2]: Leaving directory
`/home/colin/downloads/pgadmin3-1.14.0-beta3/pgadmin'
make[1]: *** [all-recursive] Error 1
make[1]: Leaving directory `/home/colin/downloads/pgadmin3-1.14.0-beta3'
make: *** [all] Error 2

Suggestions?


Not the best mailing list to talk about this (pgadmin-hackers or
-support would be better).

Anyway, I don't have the issue on my laptop. With beta3, wxWidgets
2.8.12, gcc 4.6, I have no problem compiling pgAdmin:

./configure --prefix=/opt/pgadmin-1.14
   --with-pgsql=/opt/postgresql-head
   --enable-debug
   --with-wx=/opt/wxgtk-2.8
   --with-wx-version=2.8
make
make install

I don't think it has anything to do with the fact you have wxWidgets
2.8.11 and me 2.8.12.

Did you change the source? and which configure line do you use?




Thanks for the response. My apologies for posting in the wrong area, 
unfamiliar territory, just recently started using Postgresql. I will 
take the enquiry to a more appropriate area.


I did not change the source at all, just used a plain './configure' 
which seemed to find everything.


--
---
Colin Beckingham

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

Re: [GENERAL] Compile pgadmin1-1.14.0-beta3 - ctlMenuToolbar

[Guillaume Lelarge]

On Sun, 2011-08-14 at 10:45 -0400, Colin Beckingham wrote:
> Using OpenSUSE 11.4, kernel 3.0.
> wxWidgets version 2.8.11.
> While compiling pgadmin1-1.14.0-beta3, configure completes correctly but 
> make fails on:
> 
> ./ctl/ctlMenuToolbar.cpp: In member function ‘void 
> ctlMenuToolbar::DoProcessLeftClick(wxMouseEvent&)’:
> ./ctl/ctlMenuToolbar.cpp:161:2: error: ‘Node’ is not a member of 
> ‘ctlMenuToolList’
> ./ctl/ctlMenuToolbar.cpp:161:25: error: ‘node’ was not declared in this 
> scope
> make[3]: *** [ctlMenuToolbar.o] Error 1
> make[3]: Leaving directory 
> `/home/colin/downloads/pgadmin3-1.14.0-beta3/pgadmin'
> make[2]: *** [all] Error 2
> make[2]: Leaving directory 
> `/home/colin/downloads/pgadmin3-1.14.0-beta3/pgadmin'
> make[1]: *** [all-recursive] Error 1
> make[1]: Leaving directory `/home/colin/downloads/pgadmin3-1.14.0-beta3'
> make: *** [all] Error 2
> 
> Suggestions?

Not the best mailing list to talk about this (pgadmin-hackers or
-support would be better).

Anyway, I don't have the issue on my laptop. With beta3, wxWidgets
2.8.12, gcc 4.6, I have no problem compiling pgAdmin:

./configure --prefix=/opt/pgadmin-1.14
  --with-pgsql=/opt/postgresql-head
  --enable-debug
  --with-wx=/opt/wxgtk-2.8
  --with-wx-version=2.8
make
make install

I don't think it has anything to do with the fact you have wxWidgets
2.8.11 and me 2.8.12.

Did you change the source? and which configure line do you use?


-- 
Guillaume
  http://blog.guillaume.lelarge.info
  http://www.dalibo.com


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

Re: [GENERAL] backup-strategies for large databases

[Mikko Partio]

On Sun, Aug 14, 2011 at 12:44 AM, MirrorX  wrote:

> the issue here is that the server is heavily loaded. the daily traffic is
> heavy, which means the db size is increasing every day (by 30 gb on
> average)
> and the size is already pretty large (~2TB).
>
> at the moment, the copy of the PGDATA folder (excluding pg_xlog folder),
> the
> compression of it and the storing of it in a local storage disk takes about
> 60 hours while the file size is about 550 GB. the archives are kept in a
> different location so that not a problem. so, i dont want even to imagine
> how much time the uncompress and copy will take in 'disaster' scenario.
>
> plus, we cannot keep the PGDATA in an older version and just replicate the
> wals b/c due to the heavy load they are about 150GB/day. so, even though
> that we can suppose that we have unlimited disk storage its not reasonable
> to use 5 TB for the wals (if the PGDATA is sent once a month) and
> furthermore a lot of time will be needed for the 2nd server to recover
> since
> it will have to process all this huge amount of wals.
>

We have a pretty similar situation, database size is ~3TB with daily xlog
generation of about 25G. We do a full backup (tar PGDATA + xlogs) every
fortnight and backup just the xlogs in between. The full backup takes almost
48h and is about 500G in size. All backups are gzipped of course.

The backup duration is not a problem, but the restore _might_ be. We have
restored this database more than once, and each time it got fully restored
surprisingly quick (a matter of hours). Of course if you have a 24/7
database this might not be acceptable, but then again if that's the case you
should have a standby ready anyways.

Regards

Mikko

Re: [GENERAL] How to tame a gigantic (100+ lines) query in a web app?

[Peter Bex]

On Sun, Aug 14, 2011 at 10:39:48AM -0400, W. Matthew Wilson wrote:
> I'm sure I'm not the first person to end up with a gigantic query that
> does lots of left joins and subselects.
> 
> It seems to work, but I would love to break it up into smaller chunks.
> 
> I'm thinking about rewriting the query to make several temporary
> tables that are dropped on commit, and then joining them at the end.

That's possible, but you also want to consider using CTEs (common table
expressions).  I generally prefer those when my queries are getting too
hairy to read.  You'll need PostgreSQL 8.4 or later for those.

See section 7.8 in the manual:
http://www.postgresql.org/docs/current/interactive/queries-with.html

> Is there anything dangerous about making temporary tables in this way?

AFAIK there isn't, but there might be some overhead that you don't get
with CTEs, since a temporary table will probably get materialized on disk
(AFAIK), and the optimizer probably can't do smart things to leave out
rows that cancel out through related WITH blocks.

> The temporary tables mean I'm only pulling data from the database one
> time.  ORMs often pull data from one query and then use that data to
> write the next query.  This seems slow to me.

Yeah, ORMs are stupid that way :)

Cheers,
Peter
-- 
http://sjamaan.ath.cx
--
"The process of preparing programs for a digital computer
 is especially attractive, not only because it can be economically
 and scientifically rewarding, but also because it can be an aesthetic
 experience much like composing poetry or music."
-- Donald Knuth

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

[GENERAL] Compile pgadmin1-1.14.0-beta3 - ctlMenuToolbar

[Colin Beckingham]

Using OpenSUSE 11.4, kernel 3.0.
wxWidgets version 2.8.11.
While compiling pgadmin1-1.14.0-beta3, configure completes correctly but 
make fails on:


./ctl/ctlMenuToolbar.cpp: In member function ‘void 
ctlMenuToolbar::DoProcessLeftClick(wxMouseEvent&)’:
./ctl/ctlMenuToolbar.cpp:161:2: error: ‘Node’ is not a member of 
‘ctlMenuToolList’
./ctl/ctlMenuToolbar.cpp:161:25: error: ‘node’ was not declared in this 
scope

make[3]: *** [ctlMenuToolbar.o] Error 1
make[3]: Leaving directory 
`/home/colin/downloads/pgadmin3-1.14.0-beta3/pgadmin'

make[2]: *** [all] Error 2
make[2]: Leaving directory 
`/home/colin/downloads/pgadmin3-1.14.0-beta3/pgadmin'

make[1]: *** [all-recursive] Error 1
make[1]: Leaving directory `/home/colin/downloads/pgadmin3-1.14.0-beta3'
make: *** [all] Error 2

Suggestions?
--
---
Colin Beckingham

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

[GENERAL] How to tame a gigantic (100+ lines) query in a web app?

[W. Matthew Wilson]

I'm sure I'm not the first person to end up with a gigantic query that
does lots of left joins and subselects.

It seems to work, but I would love to break it up into smaller chunks.

I'm thinking about rewriting the query to make several temporary
tables that are dropped on commit, and then joining them at the end.

I can't just use views for everything because I use parameters passed
in from the web app.  I am using a few views where I can.

Is there anything dangerous about making temporary tables in this way?
 I started two transactions simultaneously and they were both able to
make their own temporary tables.

More generally, how to tame this big ol' query?

The temporary tables mean I'm only pulling data from the database one
time.  ORMs often pull data from one query and then use that data to
write the next query.  This seems slow to me.

Matt


-- 
W. Matthew Wilson
m...@tplus1.com
http://tplus1.com

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

Re: [GENERAL] Using Postgresql as application server

[Gavin Flower]

On 14/08/11 05:12, David Johnston wrote:


On Aug 13, 2011, at 11:57, c k  wrote:


Dear Postgres users,
from last few months I am reading and searching for can postgresql used as 
application server? As postgresql supports many languages like pl/perl, 
pl/python etc, supports dblink like functions to connect to other postgresql 
servers and now features are in development to use external data. Postgresql 
works well on many operating systems and has a stable and good quality code. As 
many users are using plpython or plperl to work on many types of data and to 
implement logic that can be useful in web application management.
So i am thinking if I can use postgresql as web application server.
Few points that supports this opinion:
1. multiple languages support for stored procs and triggers.
2. can connect to other postgresql servers or databases
3. it is easy to manage stored procs or triggers than managing web application 
in other servers.
4. data and logic/processing can be separated. One postgresql can be used as 
application server and another as database.
5. stable, mature and open codebase.

I request to users and developers give your suggestions and opinions.
Waiting for your replies.

Thanks and regards,

Chaitanya Kulkarni

Code yourself a nice "hello world" application that can accessed by a web 
browser and outputs in HTML using only PostgreSQL.  IF you can actually do that simple 
task you will then be in a better position to decide if such an architecture is worth 
expanding upon.

The better question to ask is why wouldn't you want to use something like 
Tomcat or Apache+Programming Language?

David J.




Hi,

I see from other posts that you are using Python (A lovely language, but 
one that does not scale well for my own purposes -- unfortunately) so 
this is most likely not relevant to your current situation.


However, for those people who would consider Java, then consider using 
JBoss 7, it is very fast and will support very sophisticated web front 
ends while allowing very powerful logic written in Java, and can easily 
be configured to use PostgreSQL.


The combination of JBoss http://www.jboss.org/overview.html and JEE 
(Java Enterprise Edition), is well suited to large projects that can 
benefit from multi-core processors and massive amounts of RAM. My own 
humble development machine has a mere 4 cores and 8GB, but production 
machines can easily use a terrabyte of memory and many more cores. 
Eclipse is an IDE that is well suited for developing applications in 
Java, and can be configured to develop applications for JBoss.



Cheers,
Gavin

I have written a script to install JBoss 7 (a lot easier than for JBoss 
4 and earlier!) and convert it to use PostgreSQL 9.1 beta3. This I am 
using to revise my JEE knowledge and get to grips with version 6 of JEE.

Re: [GENERAL] backup-strategies for large databases

[MirrorX]

thx a lot. i will definitely look into that option

in the meantime, if there are any other suggestions i 'd love to hear them

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/backup-strategies-for-large-databases-tp4697145p4698006.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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

[GENERAL] Where to start, graphs and routing.

[k_b]

Hi.
For learning purpose i would like to make a small database with a small graph 
of locations, roads and public transport information.
Then calculate the fastest or cheapest way between two points.

If we think of a minimal network, as below.

A ---5-- B ---10 C
 \ /
  \-5-/


To travel from A to C can be done in two ways, either via B at a total cost of 
15, or directly at a cost of 5.
Let's now say that there are tow buses, one travelling A-B-C and one travelling 
A-C.
Lets say the departure schedule from A is:

bus A-B-C:
leaves A: 10:00, arrives C 10:15.
leaves A: 11:00, arrives C 11:15.


bus A-C  :
leaves A 10:15, arrives C 10:20.
leaves A 11:05, arrives C 11:10.



To get started somewhere i have a few questions about how to make the data 
model, etc.
1.
What is a good practice, having the graph represent
a) the roads and bus stops,
b) the graph to represent the bus routes and stops, or
c) having the graph to represent every single bus trip (for each departure)?

b) feels quite natural as the network gets smaller, but how do i then represent 
each and every tip made on the route?
Eg. the bus departures on the route once per hour.



2.
What are the minimum information i need about the graph?
Nodes (stops), edges (travel way), neighbour list of some sort, and some sort 
of cost to ride on a edge.
Anything else. Direction information?


3.
Is it possible to write recursive SQL in PostgreSQL without using procedural 
language? How, are there examples?


4.
How do i handle waiting time from the start point, or arrival time to 
destination?
As an example i don't want the result to be only trips with A-C (as this is 
cheepest path),
instead i want both options because sometimes A-B-C will bring me to the 
destination earlier
then A-C, and sometimes it is good to wait for A-C even though there is A-B-C 
departuring earlier.

if time is 09:55 -> take A-B-C. (obvious).
if time is 10:01 -> take A-C. (obvious).
if time is 10:55 -> take A-C. (not obvious).


5.
Last question, are theree any books covering topics like this?



Thank you.

karl

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

[GENERAL] INSERT-colision/MERGE in postgresql

[Rafal Pietrak]

Hi,

I've recently looked into the problem of my INSERTs throwing an ROW
error, when a new row hits an already present one, by unique constraint.
It triggers an expensive rollback, and I'd like to have it sort of
"optimised". In my case, duplicates can be discarded on an attempt
INSERT, but an UPDATE instead would also do.

When I was looking for a solution, I found this:
http://wiki.postgresql.org/wiki/SQL_MERGE

Which would do nicely, but I understand postgresql does not have it,
yet.

On the other hand, I think that providing the OLD.* table for RULES and
TRIGGERS on INSERT, for an application level programmer (like myself),
could provide a simple way to overcome the missing feature, until it's
fully implemented as MERGE statement according to SQL:2003. 

Such OLD.* table on INSERTS should contain a row from current table
content, that matches unique constraints of a currently inserted row or
nothing, if there is no collision. This way I could make a conditional
RULE with "... WHERE exists(OLD.someting)..." instead of doing an
explicit SELECT in that WHERE clausure, which I think is more expensive
then referring a column already fetched by the engine.

Possibly, this may pave the way to MERGE implementation??

As of today, no application level code can possibly expect a valid OLD.*
table within ROLE/TRIGGER on INSERT - so no current code will be broken
by this.

Would it be possible to add this to a whishlist for 9.2 or something?

Regards,

-R


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