Re: [GENERAL] data modeling genes and alleles... help!

Everything should have an id, and combination tables like person-gene should have the person id + the gene id. You might look at this tool to make your life easier: https://github.com/pgmodeler/pgmodeler First, read up on this: http://en.wikipedia.org/wiki/Entity%E2%80%93relationship_model then,

[GENERAL] data modeling genes and alleles... help!

List, I have a data modeling problem. That much, I know. The question is how do I model this? (Below.) I'm making a database which will store pseudo-genetic data. It's a basic many-to-many setup:: create table person( name varchar(32) primary key ); create table gene(

Re: [GENERAL] Regular function

On Sat, Mar 30, 2013 at 3:13 AM, John R Pierce wrote: > On 3/29/2013 10:54 AM, Yuriy Rusinov wrote: > >> I have to write pl/pgsql function that has to be triggered not >> before/after insert, but in definite time. Does postgresql allows to do >> this ? >> > > > there is no 'cron' built into postg

Re: [GENERAL] Money casting too liberal?

On 30/3/13 at 9:30 AM, I wrote: I have sketched something of a notation for MONEY columns along these lines: amt_received MONEY (CURRENCY-- e.g., 'USD' 'AUD' 'YEN' ... [,SCALE -- default as per currency, e.g. USD 2 decimals -- but could be used to see money in bigger units -- such a

Re: [GENERAL] Money casting too liberal?

On Fri, 2013-03-29 at 16:30 -0400, D'Arcy J.M. Cain wrote: > How would this be an issue? If you are assigning a literal to a column > then that's no issue. Otherwise, a literal is simply a value that can > be cast depending on the situation. The money type is no different in > that regard. > >

Re: [GENERAL] unique indices without pg_constraint rows

"Ed L." writes: > Why is this? What determines if a unique index will also have a row in > pg_constraint? If you made it with constraint syntax, it'll have such a row. If you made it with CREATE INDEX, not. regards, tom lane -- Sent via pgsql-general mailing list (pg

Re: [GENERAL] Is there any way to listen to NOTIFY in php without polling?

Misa Simic wrote: > I am not a C developer - was not aware about select()... I was read it as > some kind of sleep... php provides socket_select() as an equivalent to C's select(). See http://php.net/manual/en/function.socket-select.php But it takes "socket resources" as arguments and th

[GENERAL] unique indices without pg_constraint rows

I've been looking at unique indices in a PostgreSQL 8.3.x cluster. Some unique indices clearly have a corresponding row in pg_constraint, while other unique indices appear to have no corresponding row in pg_constraint at all. Why is this? What determines if a unique index will also have a

Re: [GENERAL] Money casting too liberal?

On 29/3/13 at 3:32 AM, D'Arcy J.M. Cain wrote: On Fri, 29 Mar 2013 11:46:40 -0400 Tom Lane wrote: Well, this has been discussed before, and the majority view every time has been that MONEY is a legacy thing that most people would rather rip out than sink a large amount of additional effort into

Re: [GENERAL] Money casting too liberal?

On Fri, 29 Mar 2013 12:02:49 -0700 Jeff Davis wrote: > On Thu, 2013-03-28 at 23:43 +1100, Gavan Schneider wrote: > > If the money type is meant to be serious then these > > conventions need to be followed/settable on a column by column > > basis. > > I don't like the idea of tying the semantics

Re: [GENERAL] Is there any way to listen to NOTIFY in php without polling?

Thanks Janes... I am not a C developer - was not aware about select()... I was read it as some kind of sleep... Than Clemens explained to me what select() does... However - to me it is just implementation detail... Which is possible in one language, but not in another... But technically, is the

Re: [GENERAL] Money casting too liberal?

On 3/27/13, Steve Crawford wrote: > Somewhat more worrisome is the fact that it automatically rounds input > (away from zero) to fit. > > select '123.456789'::money; >money > - > $123.46 So does casting to an integer: select 1.25::integer ; int4 1 And then there's this:

Re: [GENERAL] Money casting too liberal?

On Thu, 2013-03-28 at 23:43 +1100, Gavan Schneider wrote: > If the money type is meant to be serious then these > conventions need to be followed/settable on a column by column > basis. I don't like the idea of tying the semantics to a column. That leaves out values that aren't stored in a colum

Re: [GENERAL] Money casting too liberal?

On 28 March 2013 13:52, Shaun Thomas wrote: > On 03/28/2013 07:43 AM, Gavan Schneider wrote: > > Personally I have ignored the money type in favour of numeric. Money >> seemed to do too much behind the scenes for my taste, but, that's me >> being lazy as well, I haven't spend much time trying to

Re: [GENERAL] Regular function

On 3/29/2013 11:13 AM, John R Pierce wrote: there is no 'cron' built into postgresql. actually, there is pgAgent, which is associated with the pgAdmin package, this implements a cron-like facility to postgres. http://www.pgadmin.org/docs/1.16/pgagent.html -- john r pierce

Re: [GENERAL] Regular function

On 3/29/2013 10:54 AM, Yuriy Rusinov wrote: I have to write pl/pgsql function that has to be triggered not before/after insert, but in definite time. Does postgresql allows to do this ? there is no 'cron' built into postgresql. you could write your function, then have an external cron job

[GENERAL] Regular function

Hello colleagues ! I have to write pl/pgsql function that has to be triggered not before/after insert, but in definite time. Does postgresql allows to do this ? Thanks a lot. -- Best regards, Sincerely yours, Yuriy Rusinov.

Re: [GENERAL] Streaming replication slave crash

On Fri, Mar 29, 2013 at 10:50 AM, Tom Lane wrote: > Quentin Hartman writes: > > On Fri, Mar 29, 2013 at 10:37 AM, Tom Lane wrote: > >> What process did you use for setting up the slave? > > > I used an rsync from the master while both were stopped. > > If the master was shut down cleanly (not -

Re: [GENERAL] Streaming replication slave crash

Quentin Hartman writes: > On Fri, Mar 29, 2013 at 10:37 AM, Tom Lane wrote: >> What process did you use for setting up the slave? > I used an rsync from the master while both were stopped. If the master was shut down cleanly (not -m immediate) then the bug fix I was thinking about wouldn't expl

Re: [GENERAL] Streaming replication slave crash

On Fri, Mar 29, 2013 at 10:37 AM, Tom Lane wrote: > Quentin Hartman writes: > > Yesterday morning, one of my streaming replication slaves running 9.2.3 > > crashed with the following in the log file: > > What process did you use for setting up the slave? > I used an rsync from the master while

Re: [GENERAL] Streaming replication slave crash

Quentin Hartman writes: > Yesterday morning, one of my streaming replication slaves running 9.2.3 > crashed with the following in the log file: What process did you use for setting up the slave? There's a fix awaiting release in 9.2.4 that might explain data corruption on a slave, depending on h

Re: [GENERAL] Streaming replication slave crash

On Fri, Mar 29, 2013 at 10:23 AM, Lonni J Friedman wrote: > Looks like you've got some form of coruption: > page 1441792 of relation base/63229/63370 does not exist > Thanks for the insight. I thought that might be it, but never having seen this before I'm glad to have some confirmation. The que

Re: [GENERAL] Money casting too liberal?

On Fri, 29 Mar 2013 11:46:40 -0400 Tom Lane wrote: > Well, this has been discussed before, and the majority view every > time has been that MONEY is a legacy thing that most people would > rather rip out than sink a large amount of additional effort into. > It has some use-cases but they are narro

Re: [GENERAL] Streaming replication slave crash

Looks like you've got some form of coruption: page 1441792 of relation base/63229/63370 does not exist The question is whether it was corrupted on the master and then replicated to the slave, or if it was corrupted on the slave. I'd guess that the pg_dump tried to read from that page and barfed.

[GENERAL] Streaming replication slave crash

Yesterday morning, one of my streaming replication slaves running 9.2.3 crashed with the following in the log file: 2013-03-28 12:49:30 GMT WARNING: page 1441792 of relation base/63229/63370 does not exist 2013-03-28 12:49:30 GMT CONTEXT: xlog redo delete: index 1663/63229/109956; iblk 303, heap

Re: [GENERAL] Money casting too liberal?

Gavan Schneider writes: > Therefore the discussion is really about the desired role for > the MONEY type. Should it be refined in its current dallar and > cents mode? or, be promoted to a more universal role (akin to a > shift from ASCII to UTF)? Well, this has been discussed before, and the

Re: [GENERAL] Money casting too liberal?

Some thoughts. The current MONEY type might be considered akin to ASCII. Perfect for a base US centric accounting system where there are cents and dollars and no need to carry smaller fractions. As discussed, there are some details that could be refined. When it comes to this type being used

Re: [GENERAL] Understanding behavior of SELECT with multiple unnested columns

On Wed, Mar 27, 2013 at 9:03 AM, Tom Lane wrote: > Gavin Flower writes: >> The rule appears to be, >> where N_x & N_y are the number of entries returned for x & y: >> N_result = is the smallest positive integer that has N_x & N_y as factors. > > Right: if there are multiple set-returning function

Re: [GENERAL] Money casting too liberal?

Some people wrote: ... Hmm... This should optionally apply to time. ... for anything that really matters, I'll work with UTC. Is there a Godwin's law equivalent for when our conversations end up with timezones getting mentioned? :) Regards Gavan S

Re: [GENERAL] ts_tovector() to_query()

Because the query is what the user is typing in. I don't know what words the user is going to search for. if they simply search for 'Robocop' that would work. But how do I handle the search if they type in more than one word and still return half way accurate results? I suppose after talking t

Re: [GENERAL] ts_tovector() to_query()

On Thu, Mar 28, 2013 at 08:50:50PM +, Severn, Chris wrote: > What I want to do is return items that have 'Robocop' or 'Robocop and > DVD' or 'Robocop and Collection' or 'Robocop and DVD and collection' Based on the criteria above, I would say that: SELECT m.* FROM movies m WHERE to_tsvector(m.