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

[Dann Corbit]

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, it will be obvious how to use the tool.

-Original Message-
From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Modulok
Sent: Friday, March 29, 2013 7:39 PM
To: pgsql-general@postgresql.org
Subject: [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(
name varchar(32) primary key
);
create table person_gene(
person varchar(32) references person(name),
gene varchar(32) references gene(name)
);

And I have data like::

insert into person(name)
values
('foo')
;
insert into gene(name)
values
('hair'),
('eye')
;
insert into person_gene(person, gene)
values
('foo', 'hair'),
('foo', 'eye')
;

Great. This is important as I need to be able to ask questions like "who 
carries gene 'x'?" as well as "what genes does person 'y' carry?" But then 
things get thorny...

I also need to store the properties of the individual genes (the alleles). This 
is akin to an instance of one of the many gene classes in my application code.
So I make more tables::

create table hair(
id serial primary key,
density float,
thickness float
);
create table eye(
id serial primary key,
pupil_type int
);

How do I store a reference to this data? I'd add a column to the person_gene 
table, but it points to what? I can't reference a column name because they're 
all stored in different tables. I also can't store them in the same table, as 
they all store different data. Do I store the *table name* itself in a column 
of the gene_table? (Smells like a klude.)

A person might not carry all genes. The number of genes in existence is not 
fixed. New ones are introduced infrequently. There may be genes that no one 
carries. (I assume I just make a new table each time a new gene is introduced?)

I thought about just pickling/marshaling the instances of my various gene 
classes and just having a single 'genes' table which has a blob column but I 
hesitate to do that because I want to be able to do queries on aggregate allele 
stats. Things like "how many persons have pupil type 1?", etc.

It's late and I've probably over complicated it. Any pointers or advice on how 
to model this would be greatly appreciated.

Cheers!
-Modulok-


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


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

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

[Modulok]

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(
name varchar(32) primary key
);
create table person_gene(
person varchar(32) references person(name),
gene varchar(32) references gene(name)
);

And I have data like::

insert into person(name)
values
('foo')
;
insert into gene(name)
values
('hair'),
('eye')
;
insert into person_gene(person, gene)
values
('foo', 'hair'),
('foo', 'eye')
;

Great. This is important as I need to be able to ask questions like "who
carries gene 'x'?" as well as "what genes does person 'y' carry?" But then
things get thorny...

I also need to store the properties of the individual genes (the alleles). This
is akin to an instance of one of the many gene classes in my application code.
So I make more tables::

create table hair(
id serial primary key,
density float,
thickness float
);
create table eye(
id serial primary key,
pupil_type int
);

How do I store a reference to this data? I'd add a column to the person_gene
table, but it points to what? I can't reference a column name because they're
all stored in different tables. I also can't store them in the same table, as
they all store different data. Do I store the *table name* itself in a column
of the gene_table? (Smells like a klude.)

A person might not carry all genes. The number of genes in existence is not
fixed. New ones are introduced infrequently. There may be genes that no one
carries. (I assume I just make a new table each time a new gene is introduced?)

I thought about just pickling/marshaling the instances of my various gene
classes and just having a single 'genes' table which has a blob column but I
hesitate to do that because I want to be able to do queries on aggregate allele
stats. Things like "how many persons have pupil type 1?", etc.

It's late and I've probably over complicated it. Any pointers or advice on how
to model this would be greatly appreciated.

Cheers!
-Modulok-


-- 
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] Regular function

[Michael Paquier]

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 postgresql.   you could write your function,
> then have an external cron job invoke it, like: psql -c "select
> yourfunction()"
>
Note also that if you are planning some development with the coming release
9.3, you could also achieve that inside the server by using a custom
bgworker.
-- 
Michael

Re: [GENERAL] Money casting too liberal?

[Gavan Schneider]

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 as '000s (e.g., that end-of-month view)
[,ROUND -- need to allow for multiple rules here, sometimes
-- cents are just dropped, otherwise it can be
-- required that rounding is up or down
[,OTHER?
]]])


Addition...

No MONEY column would be complete without the ability to specify 
whether it is normally DEBIT or CREDIT (or in my preferred case 
NATURAL, i.e., no sign is pre-applied before any arithmetic 
between columns).


This is possibly the best use case for the type since it really 
allows for the DB/CR (IMNSHO arcane) conventions to be properly 
handled within established industry traditions and has special 
benefits with externally provided data... values will enter the 
dB with sign conventions properly observed.


Regards
Gavan Schneider



--
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] Money casting too liberal?

[Jeff Davis]

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.
> 
> As a result of an expression, it will have the type of the data in the
> expression.  What if the result is the addition of two columns of
> different precisions?  Pick the higher precision?  Forbid the
> operation?  The latter may make sense.  How can you add Yen and US$?

Why not have various rounding functions that do exactly what you want?
Then you can use them anywhere you want in an expression.

Tying a bunch of magic to the column, I/O function, or type system just
seems like the wrong approach when it comes to real differences (like
precision).

Regards,
Jeff Davis



-- 
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] unique indices without pg_constraint rows

[Tom Lane]

"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 (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Daniel Verite]

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 the postgres php layer
does not provides a conversion from the file descriptor returned by
PQsocket() to such php socket resources.
Thus socket_select() can't be used to monitor a postgres connection.

However, you can get a behavior that's close enough in practice to
select() with code like this:

pg_query($conn, "LISTEN event_name");
while (!$end) {
  $arr=pg_get_notify($conn);
  if (!$arr) {
usleep(10);
  }
  else
// process the notification
}

This will catch a notification 1/10 second max after it's available and sleep
the rest of the time.

Best regards,
-- 
Daniel
PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org


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

[GENERAL] unique indices without pg_constraint rows

[Ed L.]

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 row in 
pg_constraint?


Thanks in advance.

Ed

Re: [GENERAL] Money casting too liberal?

[Gavan Schneider]

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.


The only reason I have tried to explore these ideas is that the 
type is currently too quirky for most use cases. So I must agree 
that remove/ignore is the least work option. An argument for 
making the type more useful can be made by analogy to the 
geolocation add-in type. Most never go there but those who need 
to do so seem to prefer the builtin functionality over hand 
coding the same behaviour with columns of arrays that just 
happen to contain location data.



It has some use-cases but they are narrow, and it's not clear how
much wider the use-cases would be if we tried to generalize it.


A well designed and specific tool can be worth the effort.

The use cases include:

Financial data, accounts in a single currency, i.e., the 
money column in a transaction


Multi currency data, i.e., keeping track of transactions 
across several currencies.
specifically we are NOT doing conversions, what 
arrives/leaves as $ or ¥ stays that way,
this implies the dB has tables for each area of 
operation or columns for each currency


One thing the type should not attempt or allow any implicit 
transforming of alues. Mostly a currency change is a transaction 
and whenever it happens it has to be recored as such, e.g., so 
many ¥ leave their column, appropriate $ are added to their 
column, and commission $/¥ is added to its column, also 
included will be: exchange rate reference time-stamp journal 
reference, etc. A constraint could be constructed to ensure the 
double entry book keeping zero sum convention has been 
maintained across the whole transaction.


One time this might not be so detailed is for a VIEW where 
something akin to total worth is being reported. In cases like 
this the exchange rates would usually be in their table and the 
business rules would dictate which one is to be used to build 
the VIEW, e.g., end of month report, and it might be shown with 
all values in a single currency depending on the company's HQ.




I wonder if our vision isn't a little tunneled here.  Using this type
for money is, perhaps, a specialized use and the type should really be
called something else and modified to remove all connotations of money
from it.  So...




- Drop the currency symbol
- Allow number of decimals to be defined once for the column
- Don't use locale except to specify decimal separator (',' vs. '.')

Mostly this is cosmetic and only relevant for parsing text on 
data entry or default formatting with SELECT on the command 
line. The power of the class is that none of this is in the data 
other than as dB column flags. The values themselves are 
integer. The class is meant to keep the books moving right along.



- Allow operations against numeric


Whatever else is done this should happen.


Not sure what to rename it to.  Decimal would be good if it wasn't
already in use.  Maybe DecimalInt.

I don't think there is much use for another fixed precision 
integral type. NUMERIC does a good job when INTEGER isn't 
suitable. If this exercise is worth anything then MONEY should 
just do its job better so people who track money (and there is 
an awful lot of them) will find it useful.



My own experience with this sort of thing leads me to think that
real applications dealing with a variety of currencies will be
needing to store additional details, such as the exact exchange
rate that applied to a particular transaction.  So while merely


Seems like something that can be stored in a different column.


Exactly. We to think this through as would a real user.

If the business is receiving money from multiple regions then 
there will be rows which show the currency, number of units 
(numeric type since the column is not devoted to a specific 
currency), transaction tracing data, exchange reference (another 
table), amt_received::MONEY('USD','D2'), 
amt_transaction_fee::MONEY('USD','D3'), etc.


Within the accounts of the organisation the MONEY columns are 
likely to be in a single currency with movements between ledgers 
in the time honoured fashion of adding to this while removing 
the same from other(s) so all money entries add to zero across 
the row. Movements between currencies are just another 
transaction as detailed above.


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 as '000s (e.g., 
that end-of-month view)
[,ROUND  

Re: [GENERAL] Money casting too liberal?

[D'Arcy J.M. Cain]

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 to a column. That leaves
> out values that aren't stored in a column, e.g. literals or the
> results of some expression.

OK, I hadn't though of that but now that I am..

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.

As a result of an expression, it will have the type of the data in the
expression.  What if the result is the addition of two columns of
different precisions?  Pick the higher precision?  Forbid the
operation?  The latter may make sense.  How can you add Yen and US$?

-- 
D'Arcy J.M. Cain  |  Democracy is three wolves
http://www.druid.net/darcy/|  and a sheep voting on
+1 416 788 2246 (DoD#0082)(eNTP)   |  what's for dinner.
IM: da...@vex.net, VOIP: sip:da...@vex.net


-- 
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] Is there any way to listen to NOTIFY in php without polling?

[Misa Simic]

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 loop inside select() - or outside is irrelevant to
me (i see the benefit... Though, not sure the benefit - is that
big...nowdays.)

>From my point of view - what is important - is the main goal what should be
achieved...

If with resources what I have (php - though I am not php developer either)
I dont have the same power like in another language - who cares - main goal
must be achived . things work - or dont, but we must make them work :)

I am just curious:

LISTEN foo
Select()

Another client says: NOTIFY bar

Will select() - get something from server?


Thanks,

Misa

On Thursday, March 28, 2013, Jasen Betts wrote:

> On 2013-03-27, Misa Simic > wrote:
> > --20cf3074d6a0c370ce04d8ef50c1
> > Content-Type: text/plain; charset=UTF-8
> >
> > Hi Clemens,
> >
> > Well, I am not sure what you mean by polling...
> >
> > But Example shows - that C app - actually asks all the time to get
> > notify... when gets something more then 4 times - exit... until 4 times
> > loops...
>
> that's polling.
>
> I just do a select() on the socket and the select either times out or
> lets me know something has come from the server. then I call whatever
> it is to check for notifies.
>
> > The same you can achieve with PHP...
>
> except PHP doesn't have select() and hides the socket.
>
> --
> ⚂⚃ 100% natural
>
>
>
> --
> 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] Money casting too liberal?

[Michael Nolan]

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:

create table wkdata
(numval numeric(5,2))

CREATE TABLE
Time: 6.761 ms
nolan=> insert into wkdata
nolan-> values (123.456789);
INSERT 569625265 1
Time: 4.063 ms
nolan=> select * from wkdata;
select * from wkdata;
numval
--
123.46

So rounding a money field doesn't seem inconsistent with other data types.
--
Mike Nolan


-- 
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] Money casting too liberal?

[Jeff Davis]

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 column, e.g. literals or the results
of some expression.

Regards,
Jeff Davis




-- 
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] Money casting too liberal?

[Thomas Munro]

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 understand its
>> features.
>>
>
> You're not the only one. In the financial industry, we can't even use the
> money type for a few reasons:
>
> [... snip ... ]
>

Speaking as a another finance/trading industry techie who works with
various kinds of price data, I also find the money type interesting but
useless.  I am interested in scaled integers in general though, be they of
fixed scale (per column, part of the type) or of floating scale (floating
point decimal).  I have run into those all over the place in software and
protocols.  They can be stored and computed more efficiently than the more
general variable sized BCD string system where scale and precision are more
like check constraints than limits of representation allowing for fixed
size bitfields

For floating point decimal, IEEE 754 2008 decimal32, decimal64, decimal128
types would make interesting additions (the scale travels with each
number.. it's essentially a bitfield of sign + exponent/scale + significand
which is efficient for software implements, or an isomorphic BCD-like fixed
size encoding which is used by IBM's POWER DFP hardware).  But that can be
implemented as custom types outside core PostgreSQL (I've done some initial
experimentation with this, defining a type DECIMAL64, and not encountered
any obstacles, using IBM decNumber, which is available under the liberal
ICU license or the GPL license, and is used by many projects; there is also
an Intel library with a BSD license IIRC).

For fixed point  decimal, a new scaled integer type with fixed scale and
precision could be made that uses different representation depending on the
parameters, much like the way Common LIsp implementations use fixnums based
on word size while possible, and fall back to arbitrary sized systems if
needed.  That would of course be implementable outside core too.

Even the built-in NUMERIC could in theory use multiple encodings, whenever
the scale and precision are provided, since it can work out whether they
are within the limits that are implementable with different binary
representations (in other words, when you ask for NUMERIC(*, 2), do what
MONEY for US locales does, otherwise fall back to the more general case).
 But that would change the rules about when rewrites are required if you
change scale/precision, so wouldn't be reasonable.

Re: [GENERAL] Regular function

[John R Pierce]

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  37N 122W
somewhere on the middle of the left coast



--
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] Regular function

[John R Pierce]

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 invoke it, like: psql -c 
"select yourfunction()"



--
john r pierce  37N 122W
somewhere on the middle of the left coast



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

[GENERAL] Regular function

[Yuriy Rusinov]

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

[Quentin Hartman]

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 -m immediate) then the bug fix
> I was thinking about wouldn't explain this.  The fact that the panic
> didn't recur after restarting seems to void that theory as well.  I'm
> not sure what to make of that angle.
>

Yes, it was shut down cleanly. A good thought, but I don't think it's
relevant in this case.


> Can you determine which table is being complained of in the failure
> message, ie, what has relfilenode 63370 in database 63229?  If so it
> would be interesting to know what was being done to that table on the
> master.
>

Good point! Looking deeper into that, it's actually one of our smaller
tables, and it doesn't seem to have any corruption, on either server. I was
able to select all the records from it and the content seems sane. The only
thing that would have been happening on that table is an INSERT or UPDATE.

I think I'm going to run with the spurious EC2 hiccup explanation. I'm
comfortable with that given the extra due diligence I've done with your
(and Lonni's) guidance.

Thanks!

QH

Re: [GENERAL] Streaming replication slave crash

[Tom Lane]

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 explain this.  The fact that the panic
didn't recur after restarting seems to void that theory as well.  I'm
not sure what to make of that angle.

Can you determine which table is being complained of in the failure
message, ie, what has relfilenode 63370 in database 63229?  If so it
would be interesting to know what was being done to that table on the
master.

regards, tom lane


-- 
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 slave crash

[Quentin Hartman]

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 both were stopped.


> This theory would be more probable if it's a relatively new slave, since
> any corruption would have been there in the slave's initial state, just
> waiting for the replay to run into it.
>

It's newish. I upgraded this pair from 9.1.x to 9.2.3 a little over a week
ago, so did a dump/reload and resynced then. It was running happily in
between now and then.

Thanks!

QH

Re: [GENERAL] Streaming replication slave crash

[Tom Lane]

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 how it was set up:

* Ensure we do crash recovery before entering archive recovery,
if the database was not stopped cleanly and a recovery.conf file
is present (Heikki Linnakangas, Kyotaro Horiguchi, Mitsumasa Kondo)

This is needed to ensure that the database is consistent in certain
scenarios, such as initializing a standby server with a filesystem
snapshot from a running server.

This theory would be more probable if it's a relatively new slave, since
any corruption would have been there in the slave's initial state, just
waiting for the replay to run into it.

regards, tom lane


-- 
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 slave crash

[Quentin Hartman]

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 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.  It
> would be interesting to try re-running the pg_dump again to see if
> this crash can be replicated.  If so, does it also replicate if you
> run pg_dump against the master?  If not, then the corruption is
> isolated to the slave, and you might have a hardware problem which is
> causing the data to get corrupted.
>

Yes, we've gotten several clean dumps form the slave since then w/o
crashing. We're running these machines on EC2 so we sadly have no control
over the hardware. With your confirmation, and an apparently clean state
now, I'm inclined to chalk this up to an EC2 hiccup getting caught by
Postgres and get on with life.

Thanks!

QH

Re: [GENERAL] Money casting too liberal?

[D'Arcy J.M. Cain]

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 narrow, and it's not clear how
> much wider the use-cases would be if we tried to generalize it.

I wonder if our vision isn't a little tunneled here.  Using this type
for money is, perhaps, a specialized use and the type should really be
called something else and modified to remove all connotations of money
from it.  So...

 - Drop the currency symbol
 - Allow number of decimals to be defined once for the column
 - Don't use locale except to specify decimal separator (',' vs. '.')
 - Allow operations against numeric

Not sure what to rename it to.  Decimal would be good if it wasn't
already in use.  Maybe DecimalInt.

> My own experience with this sort of thing leads me to think that
> real applications dealing with a variety of currencies will be
> needing to store additional details, such as the exact exchange
> rate that applied to a particular transaction.  So while merely

Seems like something that can be stored in a different column.

-- 
D'Arcy J.M. Cain  |  Democracy is three wolves
http://www.druid.net/darcy/|  and a sheep voting on
+1 416 788 2246 (DoD#0082)(eNTP)   |  what's for dinner.
IM: da...@vex.net, VOIP: sip:da...@vex.net


-- 
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 slave crash

[Lonni J Friedman]

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.  It
would be interesting to try re-running the pg_dump again to see if
this crash can be replicated.  If so, does it also replicate if you
run pg_dump against the master?  If not, then the corruption is
isolated to the slave, and you might have a hardware problem which is
causing the data to get corrupted.

On Fri, Mar 29, 2013 at 9:19 AM, Quentin Hartman
 wrote:
> 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 1663/63229/63370;
> 2013-03-28 12:49:30 GMT PANIC:  WAL contains references to invalid pages
> 2013-03-28 12:49:30 GMT CONTEXT:  xlog redo delete: index 1663/63229/109956;
> iblk 303, heap 1663/63229/63370;
> 2013-03-28 12:49:31 GMT LOG:  startup process (PID 22941) was terminated by
> signal 6: Aborted
> 2013-03-28 12:49:31 GMT LOG:  terminating any other active server processes
> 2013-03-28 12:49:31 GMT WARNING:  terminating connection because of crash of
> another server process
> 2013-03-28 12:49:31 GMT DETAIL:  The postmaster has commanded this server
> process to roll back the current transaction and exit, because another
> server process exited abnormally and possibly corrupted shared memory.
> 2013-03-28 12:49:31 GMT HINT:  In a moment you should be able to reconnect
> to the database and repeat your command.
> 2013-03-28 12:57:44 GMT LOG:  database system was interrupted while in
> recovery at log time 2013-03-28 12:37:42 GMT
> 2013-03-28 12:57:44 GMT HINT:  If this has occurred more than once some data
> might be corrupted and you might need to choose an earlier recovery target.
> 2013-03-28 12:57:44 GMT LOG:  entering standby mode
> 2013-03-28 12:57:44 GMT LOG:  redo starts at 19/2367CE30
> 2013-03-28 12:57:44 GMT LOG:  incomplete startup packet
> 2013-03-28 12:57:44 GMT LOG:  consistent recovery state reached at
> 19/241835B0
> 2013-03-28 12:57:44 GMT LOG:  database system is ready to accept read only
> connections
> 2013-03-28 12:57:44 GMT LOG:  invalid record length at 19/2419EE38
> 2013-03-28 12:57:44 GMT LOG:  streaming replication successfully connected
> to primary
>
> As you can see I was able to restart it and it picked up and synchronized
> right away, but this crash still concerns me.
>
> The DB has about 75GB of data in it, and it is almost entirely write
> traffic. It's essentially a log aggregator. I believe it was doing a pg_dump
> backup at the time of the crash. It has hot_standby_feedback on to allow
> that process to complete.
>
> Any insights into this, or advice on figuring out the root of it would be
> appreciated. So far all the things I've found like this are bugs that should
> be fixed in this version, or the internet equivalent of a shrug.
>
> Thanks!
>
> QH



-- 
~
L. Friedmannetll...@gmail.com
LlamaLand   https://netllama.linux-sxs.org


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

[GENERAL] Streaming replication slave crash

[Quentin Hartman]

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 1663/63229/63370;
2013-03-28 12:49:30 GMT PANIC:  WAL contains references to invalid pages
2013-03-28 12:49:30 GMT CONTEXT:  xlog redo delete: index
1663/63229/109956; iblk 303, heap 1663/63229/63370;
2013-03-28 12:49:31 GMT LOG:  startup process (PID 22941) was terminated by
signal 6: Aborted
2013-03-28 12:49:31 GMT LOG:  terminating any other active server processes
2013-03-28 12:49:31 GMT WARNING:  terminating connection because of crash
of another server process
2013-03-28 12:49:31 GMT DETAIL:  The postmaster has commanded this server
process to roll back the current transaction and exit, because another
server process exited abnormally and possibly corrupted shared memory.
2013-03-28 12:49:31 GMT HINT:  In a moment you should be able to reconnect
to the database and repeat your command.
2013-03-28 12:57:44 GMT LOG:  database system was interrupted while in
recovery at log time 2013-03-28 12:37:42 GMT
2013-03-28 12:57:44 GMT HINT:  If this has occurred more than once some
data might be corrupted and you might need to choose an earlier recovery
target.
2013-03-28 12:57:44 GMT LOG:  entering standby mode
2013-03-28 12:57:44 GMT LOG:  redo starts at 19/2367CE30
2013-03-28 12:57:44 GMT LOG:  incomplete startup packet
2013-03-28 12:57:44 GMT LOG:  consistent recovery state reached at
19/241835B0
2013-03-28 12:57:44 GMT LOG:  database system is ready to accept read only
connections
2013-03-28 12:57:44 GMT LOG:  invalid record length at 19/2419EE38
2013-03-28 12:57:44 GMT LOG:  streaming replication successfully connected
to primary

As you can see I was able to restart it and it picked up and synchronized
right away, but this crash still concerns me.

The DB has about 75GB of data in it, and it is almost entirely write
traffic. It's essentially a log aggregator. I believe it was doing a
pg_dump backup at the time of the crash. It has hot_standby_feedback on to
allow that process to complete.

Any insights into this, or advice on figuring out the root of it would be
appreciated. So far all the things I've found like this are bugs that
should be fixed in this version, or the internet equivalent of a shrug.

Thanks!

QH

Re: [GENERAL] Money casting too liberal?

[Tom Lane]

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 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 narrow, and it's not clear how
much wider the use-cases would be if we tried to generalize it.

My own experience with this sort of thing leads me to think that
real applications dealing with a variety of currencies will be
needing to store additional details, such as the exact exchange
rate that applied to a particular transaction.  So while merely
decoupling MONEY from lc_monetary doesn't sound like a bad thing,
it's not clear it really buys that much.

regards, tom lane


-- 
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] Money casting too liberal?

[Gavan Schneider]

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 in full blown money 
systems it lacks the ability to carry fractions of cents and 
keep track of currencies. It also needs to play nicer with other 
exact types such as numeric, i.e., no intermediate calculations 
as real.


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)?


If there is merit in making MONEY work for most situations 
involving financial transactions I think the following might apply:


- keep integer as the underlying base type (for performance)

- generalise the decimal multiplier of a MONRY column so a 
specific MONEY column can be what its creator wants (from 
partial cents to millions of dollars/Yen/Other, along with 
rounding/truncating rules as required by r the user of his/her 
external agencies)


- define the currency for a given column and only allow this to 
change in defined ways, and specifically forbid implicit changes 
such as would arise from altering LOCALE information


- ensure the MONEY type plays nice with other exact precision 
types, i.e., convert to REAL/FLOAT as a very last resort



Personally I don't think it is appropriate for the MONEY type to 
have variable characteristics (such as different currencies) 
within a given column, rather the column variable should define 
the currency along with the desired decimal-multiplier and 
whatever else is required. The actual values within the column 
remain as simple integers. This is mostly based on performance 
issues. If the MONRY type is to be used it has to offer real 
performance benefits over bespoke NUMERIC applications.


Regards
Gavan Schneider



--
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] Understanding behavior of SELECT with multiple unnested columns

[Merlin Moncure]

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 functions in a SELECT list,
> the number of rows you get is the least common multiple of their
> periods.  (See the logic in ExecTargetList that cycles the SRFs until
> they all report "done" at the same time.)  I guess there's some value
> in this for the case where they all have the same period, but otherwise
> it's kind of bizarre.  It's been like that since Berkeley days though,
> so I doubt we'll consider changing it now.  Rather, it'll just be
> quietly deprecated in favor of putting SRFs into FROM (with LATERAL
> where needed).

It's a neat way to make a query that doesn't terminate (which AFAIK is
impossible in vanilla SQL):

create sequence s;
select generate_series(1,nextval('s')), generate_series(1,nextval('s'));

merlin


-- 
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] Money casting too liberal?

[Gavan Schneider]

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 Schneider



--
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] ts_tovector() to_query()

[Severn, Chris]

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 through it a bit, this may be more of a ranking issue 
than an actual query issue. I will read up more on ranking in the postgres 
docs. I skimmed past it since it didn't seem to apply, but now looking over it 
again, I think it could help.

Thanks.
Chris

-Original Message-
From: dep...@depesz.com [mailto:dep...@depesz.com] 
Sent: Friday, March 29, 2013 7:59 AM
To: Severn, Chris
Cc: pgsql-general@postgresql.org
Subject: 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.item_title) @@ 
to_tsquery('Robocop')

will do what you need, since "dvd" and "collection" are irrelevant for the 
results.

Best regards,

depesz

--
The best thing about modern society is how easy it is to avoid contact with it.
 http://depesz.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] ts_tovector() to_query()

[hubert depesz lubaczewski]

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.item_title) @@ 
to_tsquery('Robocop')

will do what you need, since "dvd" and "collection" are irrelevant for
the results.

Best regards,

depesz

-- 
The best thing about modern society is how easy it is to avoid contact with it.
 http://depesz.com/


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