[GENERAL] unique index for periods
Hello,
I try to create an unique index for a (time)period, and my goal is to
prevent two overlapping periods in a row.
For this I created a type with following command:
CREATE TYPE period AS
("first" timestamp with time zone,
"next" timestamp with time zone);
To use the btree index I added a compare function:
CREATE OR REPLACE FUNCTION period_compare(period, period)
RETURNS integer AS $BODY$
begin
raise info 'compare % <=> % = %', $1, $2,
CASE
WHEN $1.next <= $2.first THEN -1
WHEN $2.next <= $1.first THEN 1
ELSE 0
END;
return
CASE
WHEN $1.next <= $2.first THEN -1
WHEN $2.next <= $1.first THEN 1
ELSE 0
END;
end
$BODY$ LANGUAGE 'plpgsql' IMMUTABLE STRICT COST 1;
After this I created a operator class:
CREATE OPERATOR CLASS period_overlap
DEFAULT FOR TYPE period USING btree AS
FUNCTION 1 period_compare(period, period);
To test everything I use this table:
CREATE TABLE p (
p period NOT NULL,
CONSTRAINT p_pkey PRIMARY KEY (p)
);
Now I fill the table with data:
DELETE FROM p;
-- clean up
VACUUM p;
INSERT INTO p VALUES (('-infinity', 'today')::period);
-- this one fails
-- INSERT INTO p VALUES (('-infinity', 'infinity')::period);
DELETE FROM p;
-- the index tree is still there, why?
INSERT INTO p VALUES (('-infinity', 'infinity')::period);
-- intersects with the deleted value, so compare returns 0
-- and the data goes to the left side of the tree
-- this one should fail
INSERT INTO p VALUES (('today', 'infinity')::period);
-- but this one is bigger than the deleted value, goes to
-- the right side of the tree and is not compared to the
-- entry inserted above.
What do I do wrong? Is there another solution to solve my problem?
Thanks,
Gerhard
signature.asc
Description: Digital signature
Re: [GENERAL] Problem with bacula and 8.3/8.4
On Wed, 2009-08-19 at 08:58 -0400, Arturo Pérez wrote:
> 19-Aug 02:24 bacula-dir JobId 1951: Fatal error: sql_create.c:789
> Fill Path table Query failed: INSERT INTO Path (Path) SELECT a.Path
> FROM (SELECT DISTINCT Path FROM batch) AS a WHERE NOT EXISTS (SELECT
> Path FROM Path WHERE Path = a.Path) : ERR=ERROR: array size exceeds
> the maximum allowed (268435455)
That's odd. Where's the array? Subqueries shouldn't result in arrays in
any way, no?
On my Bacula install there are no triggers on the `path' table and it's
a very simple table:
Table "public.path"
Column | Type | Modifiers
+-+---
pathid | integer | not null default nextval('path_pathid_seq'::regclass)
path | text| not null
Indexes:
"path_pkey" PRIMARY KEY, btree (pathid)
"path_name_idx" btree (path)
so I'm a bit puzzled. Just to see, try expressing the query in two steps:
SELECT path INTO TEMPORARY TABLE batch_path
FROM batch GROUP BY path;
INSERT INTO Path (Path)
SELECT batch_path.Path
FROM batch_path AS a
WHERE NOT EXISTS (SELECT 1 FROM Path WHERE Path = a.Path);
--
Craig Ringer
--
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] Postgre RAISE NOTICE and PHP
On 08/19/2009 11:41 PM, Randal L. Schwartz wrote:
>> "Clemens" == Clemens Schwaighofer
>> writes:
>
> Clemens> Just in my opinion, this regex is completely too large. For basic
> Clemens> validating something like:
> Clemens>
> ^[A-Za-z0-9!#$%&'*+-\/=?^_`{|}~][A-Za-z0-9!#$%&'*+-\/=?^_`{|}~\.]{0,6...@[a-za-z0-9-]+(\.[a-zA-Z0-9-]{1,})*\.([a-zA-Z]{2,4}){1}$
> Clemens> works very well
>
> Fails on ".mobile" TLD. Has a pointless {1} in it, which does
> absolutely nothing, providing that the creator of the regex was already
> missing a few clues.
>
> That's the problem with these kinds of regex... you test it on what
> you know, but you're not consulting the *actual* *internet* specifications
> (which have been readily available since the dawn of Internet time).
>
> Either use the regex I pointed to already, or stay with the simpler:
>
> /\...@.*\s/
>
> which will at least not deny anyone with a *perfectly legitimate* email
> address from making it into your system.
>
> Or, use your regex *only* in an *advice* category, with the ability
> for the user to say "yes, I'm really sure this is my address".
>
> Please, for the sake of the net, do the Right Thing here. This is
> what I'm arguing for. Anything less than that, and your code deserves
> to end up in thedailywtf.com as an example of what *not* to do.
I am not going to defend any regex here, but in my opinion it helps on
what I want to see in email addresses.
Yes it fails on mobile, but I have not yet seen one. Probably the best
thing is to test nothing at all. Just accept it ...
--
[ Clemens Schwaighofer -=:~ ]
[ IT Engineer/Web Producer/Planning ]
[ E-Graphics Communications SP Digital ]
[6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ]
[ Tel: +81-(0)3-3545-7706Fax: +81-(0)3-3545-7343 ]
[ http://www.e-gra.co.jp ]
signature.asc
Description: OpenPGP digital signature
Re: [GENERAL] Postgre RAISE NOTICE and PHP
> "Clemens" == Clemens Schwaighofer > writes: Clemens> I am not going to defend any regex here, but in my opinion it helps on Clemens> what I want to see in email addresses. Clemens> Yes it fails on mobile, but I have not yet seen one. And that's the problem. You get near-sighted if you put up a strong validation for only things that *you* have seen. Because, guess what, nobody outside your narrow view can sign up or be a customer. Bad for business. Clemens> Probably the best Clemens> thing is to test nothing at all. Just accept it ... Exactly! If you don't want to use the 950-character regex, DON'T DO ANYTHING AT ALL. Far simpler. -- Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095 http://www.stonehenge.com/merlyn/> Smalltalk/Perl/Unix consulting, Technical writing, Comedy, etc. etc. See http://methodsandmessages.vox.com/ for Smalltalk and Seaside discussion -- 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] Stock Market Price Data & postgreSQL? HELLPPP Please
On Wed, 19 Aug 2009, Tom Lane wrote: BTW, we were doing full-speed tick data collection and real-time trading analysis in the nineties, on machines that my current cell phone would leave in the dust. The market data volume has grown a lot since then of course, but the price of hardware has fallen a lot more. Trading volume always grows to where it's just possible to keep up with it using a well designed app on the fastest hardware available. If you're always faster than someone else there's money to be made from them using that fact. The continuous arms race for the fastest scanning and execution platform keeps volume moving upward in lock step with what hardware is capable of. The last mainstream news article on this topic was http://www.nytimes.com/2009/07/24/business/24trading.html -- * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD -- 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] Stock Market Price Data & postgreSQL? HELLPPP Please
On Thu, 20 Aug 2009, Sanjay Arora wrote:
What exactly the difference in layman sys admin terms between our
everyday postgres and this time series data.
There is a good introduction to where regular databases fail to solve
time-series data problems at http://cs.nyu.edu/shasha/papers/jagtalk.html
As mentioned there, one of the very hard problems to solve in a general
way is how to deal with all the gaps in your data when the market isn't
open, which really complicates how you compute indicators. For example,
in regular SQL you might compute an average over some period using
something like:
select sum(x) / count(*) where ts>=t1 and ts<=t2
You might think that you could pick t1 and t2 here based on the number of
samples you want to average; let's say you want an average over the last
minute of data, so you try this:
t1=
t2= + interval('1 minute')
This completely falls apart when when the data isn't continuous. If the
market was closed for some period between t1 and t2, you need to use a
rolling window over the data you've got instead. As of PostgreSQL 8.4's
support for SQL window fuctions, it's easier to compute this sort of
thing, but there's still plenty of rough spots to get nailed by.
--
* Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD
--
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] Temp table or normal table for performance?
Peter Hunsberger wrote: On Wed, Aug 19, 2009 at 2:03 AM, Stephen Cook wrote: Let's say I have a function that needs to collect some data from various tables and process and sort them to be returned to the user. In general, would it be better to create a temporary table in that function, do the work and sorting there, and return it... or keep a permanent table for pretty much the same thing, but add a "user session" field and return the relevant rows from that and then delete them? Sorry this is vague, I know it most likely depends on the workload and such, but I'm just putting this together now. I could go either way, and also switch it up in the future if necessary. Is there a rule of thumb on this one? I'm a bit biased against temporary tables, but then again if the normal table gets a lot of action it might not be the optimal choice. This completely depends on the specifics, there's no way anyone can give you a general answer for this kind of problem. However, why do you think you will need a temp or permanent table? Why can't you just use your function to compute the answers at the time the user needs the data? I figured that would be the response I'd get :) I've decided on some type of table storage because basically I'm combining information from several different tables (some of which need to recursively get other rows) and massaging it and sorting it in ways far too convoluted to use a single query with UNION and ORDER BY, and then returning the results. -- 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] Stock Market Price Data & postgreSQL? HELLPPP Please
On Wed, 19 Aug 2009, Adrian Klaver wrote: In the its a small world category I just ran across a streaming database called Telegraph(http://telegraph.cs.berkeley.edu/telegraphcq/v2.1/) yesterday. It is based on Postgres 7.3.2, so approach with caution. TelegraphCQ was the research project that Truviso, the company where I work, originated from. Having taken considerably more mature code of this type than what's posted there and applied it to market data, I can tell you that just having a streaming database doesn't make that many of the problems go away. Streaming database technology certainly helps compute technical indicators and such more efficiently than having to pull the data back out of the database, but that's only a small subset of what you have to do in order to build a complete trading system. As for Sanjay's project, it sounds like it's underfunded by between two and four orders of magnitude. Before getting involved with Truviso, I once wrote a PostgreSQL based automatic trading system that pulled in streaming market data and made trading decisions based on it, with backtesting and everything. Took about six months of full time work to get the first version working, and I doubt that approach would scale up to more than a few hundred active symbols even on modern hardware. The libraries provided by Marketcetera would give you a big head start on the coding compared to where I began at, but without a serious development budget you're going to be stuck taking somebody's entire software stack as-is. You should be asking programatic traders where there are complete applications ready to go here, not asking about the database details because you're really not going to have any choice besides just using whatever the app is designed around. Given your lack of developent background, you sound more like a Tradestation or Metastock customer than someone who is going to assemble this app yourself. Sorry to sound like a downer here, but your problem is much bigger than you think it is and I'm not sure where to even begin sorting out the bits you're going to need but don't have yet. -- * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD -- 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] Stock Market Price Data & postgreSQL? HELLPPP Please
Adrian Klaver escribió: > In the its a small world category I just ran across a streaming database > called Telegraph(http://telegraph.cs.berkeley.edu/telegraphcq/v2.1/) > yesterday. It is based on Postgres 7.3.2, so approach with caution. TelegraphCQ became Truviso. I clearly remember somebody talking about how Truviso was all set up to solve the stock ticker problem, but if you go to their site now they don't mention stock at all. Does this mean anything? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] Stock Market Price Data & postgreSQL? HELLPPP Please
On Thu, Aug 20, 2009 at 4:23 AM, Tom Lane wrote: > It does sound like you are doing pretty much exactly what we were > doing. One thing to think about is that the real-time case is actually > much slower and easier to deal with than back-testing. When you are > back-testing, you'd like to test a trading strategy over say a year's > worth of data, and you'd like that to require a bit less than a year > to run, no? So the path you have to optimize is the one feeding stored, > pre-aggregated data into your test engine. The people hollering about > real-time performance are just betraying that they've never built one of > these things. I'm not familiar with this CEP software, but it sounds to > me like you want that as part of the real-time data collection process > and nowhere near your back-testing data path. > CEP (Continuous Event Processing...just check out the explanation para at http://esper.codehaus.org/ ) is basically running queries on data streams in memory (i got that as rdbms tables in memory)...on time series data (whatever that is). no concept of storage etc. BTW, would you please look back a bit in this thread, a post (by Adrian Klaver see.. http://archives.postgresql.org/pgsql-general/2009-08/msg00745.php ) about a database built by a berkeley computer science deptt. project on postgres 7.2, that stores & queries, time series data just like this and returns data in a stream instead of a recordset. It is said that this type of databases are made for analyzing this type of data. And it has normal postgres mode for returning records and streams mode for returning results in a streamwhich I think is a nothing but a TCP-IP socket which receives results on an ongoing basis I have also posted a question in its reply (see .. http://archives.postgresql.org/pgsql-general/2009-08/msg00748.php ), about why postgres itself does not have this (whatever this time series data is in mathematical terms) when that project itself extends postgres 7.2. Will you please have a look at these posts and this project which is using postgres itself. And please explain in layman terms what these guys are doing different that we cannot have a time series data types (we do have GIS & tons of other data types) & stream data returns. Maybe not immediately of use to me but I understand ODBMS, RDBMS and I want to understand this at a conceptual level, at least. > > Another little tip: if your trading strategies are like ours were, > they need to ramp up for actual trading by pre-scanning some amount of > historical data. So you're going to need a mechanism that starts by > reading the historical data at high speed and smoothly segues into > reading the real-time feed (at which the passage of time in the model > suddenly slows to one-to-one with real time). Also consider what > happens when your machine crashes (it happens) and you need to not only > redo that startup process, but persuade the model that its actual > trading position is whatever you had open. Or you changed the model a > bit and restart as above. The model might now wish it was in a different > position, but it has to cope with your actual position. > > The crash reliability of a DBMS is a strong reason why you track your > live positions in one, btw ... your brokerage isn't going to forget you > were short IBM, even if your model crashes. Thats very good advisewe had already planned it that way. And yes, the brokerages are not going to care that we ran out of money while our server was restarting ;-) > > 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] Stock Market Price Data & postgreSQL? HELLPPP Please
Sanjay Arora writes: > Do you mean something like storing one month worth tick data in a blob > type field and giving the contents to the CEP engine for further > building of required data streams? Either that or put the tick data in an external file and store that file's pathname in the database row. As I said, you can find plenty of argumentation on both sides of that in the PG archives. When SSS were doing this, we had the raw tick data in one set of files and pre-aggregated bar data in other files (I think we stored 5-min and daily bars, but it was a long time ago). The analysis functions would automatically build the bar width they wanted from the widest stored form that divided the desired width, so as to minimize what they had to read from disk. It does sound like you are doing pretty much exactly what we were doing. One thing to think about is that the real-time case is actually much slower and easier to deal with than back-testing. When you are back-testing, you'd like to test a trading strategy over say a year's worth of data, and you'd like that to require a bit less than a year to run, no? So the path you have to optimize is the one feeding stored, pre-aggregated data into your test engine. The people hollering about real-time performance are just betraying that they've never built one of these things. I'm not familiar with this CEP software, but it sounds to me like you want that as part of the real-time data collection process and nowhere near your back-testing data path. Another little tip: if your trading strategies are like ours were, they need to ramp up for actual trading by pre-scanning some amount of historical data. So you're going to need a mechanism that starts by reading the historical data at high speed and smoothly segues into reading the real-time feed (at which the passage of time in the model suddenly slows to one-to-one with real time). Also consider what happens when your machine crashes (it happens) and you need to not only redo that startup process, but persuade the model that its actual trading position is whatever you had open. Or you changed the model a bit and restart as above. The model might now wish it was in a different position, but it has to cope with your actual position. The crash reliability of a DBMS is a strong reason why you track your live positions in one, btw ... your brokerage isn't going to forget you were short IBM, even if your model crashes. 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] Stock Market Price Data & postgreSQL? HELLPPP Please
Thanks Tom For this reply and many earlier ones that have guided me around postgres ;-) On Thu, Aug 20, 2009 at 3:34 AM, Tom Lane wrote: > Sanjay Arora writes: > It might be worth managing your > tick data using DBMS entries that represent, say, monthly tick data > files. Activities like removing old data would get a lot easier that > way. (I wish this had occurred to me twelve years ago, but it's water > over the dam now.) Do you mean something like storing one month worth tick data in a blob type field and giving the contents to the CEP engine for further building of required data streams? This approach would allow getting big chunks of data to be handled with not caring location, naming & indexing etc. of tick data files? Anything else you can suggest? Best Regards. Sanjay. -- 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] Postgre RAISE NOTICE and PHP
On Wed, Aug 19, 2009 at 11:57:45AM -0600, Scott Marlowe wrote:
> On Wed, Aug 19, 2009 at 8:41 AM, Randal L.
> Schwartz wrote:
> >> "Clemens" == Clemens Schwaighofer
> >> writes:
> >
> > Clemens> Just in my opinion, this regex is completely too large. For basic
> > Clemens> validating something like:
> > Clemens>
> > ^[A-Za-z0-9!#$%&'*+-\/=?^_`{|}~][A-Za-z0-9!#$%&'*+-\/=?^_`{|}~\.]{0,6...@[a-za-z0-9-]+(\.[a-zA-Z0-9-]{1,})*\.([a-zA-Z]{2,4}){1}$
> > Clemens> works very well
> >
> > Fails on ".mobile" TLD. Has a pointless {1} in it, which does
> > absolutely nothing, providing that the creator of the regex was already
> > missing a few clues.
>
> Remonds me of the saying that for every complex problem there is a
> simple, elegant and incorrect solution.
That's from H. L. Mencken.
For every complex problem, there is an answer which is clear,
simple, and wrong.
Cheers,
David.
--
David Fetter http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fet...@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
--
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] Stock Market Price Data & postgreSQL? HELLPPP Please
Sanjay Arora writes: > I want to store stock market price data in postgreSQL and some of my > associates are saying this is not workable, not only in postgreSQL but > in any database. Arguments given are listed below. Kindly comment and > if possible suggest solutions and pointers to further reading. Actually, the way I came to use Postgres was from a company that was working on technical analysis of market data. I would recommend looking at a hybrid approach. Storing raw tick data in a DBMS at one row per tick is indeed not very bright. You could possibly make it work if you throw beefy hardware at the problem, but there's not much point because you're really not playing to a DBMS's strengths when you do that. Once it's arrived the data is static (barring very-infrequent corrections, which in practice you might never do at all anyway). And the access patterns for it (at least if you're doing the same type of technical analysis we were doing) are extremely predictable and linear. So you aren't doing anything wherein SQL will shine. On the other hand, there are definitely components of the big picture where SQL *can* shine. I'd strongly recommend tracking your live trading positions in an RDBMS, for example. It might be worth managing your tick data using DBMS entries that represent, say, monthly tick data files. Activities like removing old data would get a lot easier that way. (I wish this had occurred to me twelve years ago, but it's water over the dam now.) Any performance issues can be avoided by having the technical analysis processes read the static tick data files directly. This is generally pretty similar to the frequently-asked question "should I store a lot of big image or document files directly in the database, or just use it as an index of external files?". If you dig around in the PG list archives you'll find more than you want to read about that on both sides. If you've aggregated the ticks in suitable volumes you can solve it either way, really, but an external file is a bit easier to append to during raw data collection. Don't fall into the trap of assuming that all your requirements must be solved by a single tool. You'll spend all your time building the perfect tool, and go broke before you finish it. Use a DBMS for the parts of the problem it's good for, and go outside it for what it isn't. BTW, we were doing full-speed tick data collection and real-time trading analysis in the nineties, on machines that my current cell phone would leave in the dust. The market data volume has grown a lot since then of course, but the price of hardware has fallen a lot more. I'd not recommend designing your system on the assumption that raw per-tick speed is the be-all and end-all. 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] Stock Market Price Data & postgreSQL? HELLPPP Please
On Thu, Aug 20, 2009 at 3:11 AM, Adrian Klaver wrote: > > > In the its a small world category I just ran across a streaming database > called Telegraph(http://telegraph.cs.berkeley.edu/telegraphcq/v2.1/) > yesterday. It is based on Postgres 7.3.2, so approach with caution. > Thanks Adrian, Very interesting. The pages seem to have moved. I had to read them in google cache. Will email them regarding the project roadmap and if they will be pursuing it for some time to come or is it only a minor research project, that will stop as soon as the research paper is submitted. Mailing list of the project though shows mails are decreasing to almost one per day. However, it threw a very interesting question for me. It was based on postgres itself, v. 7.2 but thats beside the point. What exactly the difference in layman sys admin terms between our everyday postgres and this time series data. postgres supports geo data types and many others. Whats the big issue in not having this sort of time series data type and query as a normal support by postgres? It would have a big feasibility in stock market data storage and analysis. I am sure there would be some other major scientific applications like real time data acquisition & analysis & etc. So why is this sort of application not supported in postgres natively? First time, I've come across a db application that I cannot handle with postgres in my 9 years of using postgres.. Best regards. Sanjay. -- 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] Help interpreting pg_stat_bgwriter output
On Thu, Aug 13, 2009 at 3:00 AM, Greg Smith wrote: > >> buffers_backend = 740 > > This number represents the behavior the background writer is trying to > prevent--backends having to clean their own buffers up. > so what we want on busy systems is buffers_backend to be (at least) equal or (better) lower than buffers_clean, rigth? or i'm understanding wrong? -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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] Stock Market Price Data & postgreSQL? HELLPPP Please
On Thu, Aug 20, 2009 at 2:45 AM, Merlin Moncure wrote: > > I think your first step is to stand back, think about the problem as a > whole and see if you can't define your requirements better: > > *) how much data comes in each day? how much rolls out? Need to get some data...will have this tomorrow. > *) what types of analysis of the data would you like to do? Basically, what I need to do is feed the tick data to the CEP engine as a data stream. The CEP engine creates various data bars streams say 2 minute OHLC (open/high/low/close), 3 min, 5 min, 15 min, 30 min or 20 tick, 2000 tick, 5000 tick etc. & so on. These are to be fed to strategies which will trade. At least this is how it is done if data arrives from the online data provider. I am sure that this could be done on database. I had originally thought of populated views of prebuilt data bars so building the bars should not be required. They should be built using triggers as soon as the data arrives in postgres, but then everyone advised that it was exactly due to slow results in this type of data that CEP engines were built. I was told that if I used this type of thing, so many triggers would be running that I would have my processors and RAM maxing out, not to say the exponential increase in disk space requirements. And this is for one symbol. On an average 2000 plus scrips need to be analysed. Again the analysis is backtesting trade simulation results according to various strategies, so that means at least three years plus data, if one wants to be a stickler 5-6 years but one year is the bare minimum. > *) what are your performance expectations? > Well, needfully the simulations should finish overnight with three years worth of data. > For example, try and describe what you would like to do in detail. > The data you would import, how you would like it structured, and a > typical query. While the amount of price data a stock market produces > is immense, these types of problems tend to scale very well > horizontally. So let's define the basic amount of work you'd like to > do, then build off that. > I'll get back on this by tomorrow. Thanks. With best regards. Sanjay. -- 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] Stock Market Price Data & postgreSQL? HELLPPP Please
- "Greg Stark" wrote: > On Wed, Aug 19, 2009 at 9:22 PM, Sanjay > Arora wrote: > > - This is Time Series Data (I don't know what that is except that > it > > relates to data marked/related to time) and not suited to a RDBMS. > > - You need it in Esper (a CEP engine used by Marketcetera, an open > > source trading platform) which needs to get a data stream, whereas > an > > RDBMS will return a recordset. > > - A RDBMS including postgreSQL simply cannot handle the amount of > > real-time data coming in from a stock exchange. > > - A RDBMS simply cannot provide data, even if you build streams > from > > recordsets returned, at the speeds required by any technical > analysis > > charting program. > > There are whole companies doing nothing but providing streaming RDBMS > used by traders, airline reservation systems, etc. c.f Streambase, > and > Truviso amongst others. > > I'm not aware of any open source streaming databaes so for a > shoestring budget you're going to be limited to using existing tools. > There are certainly people who scale up Postgres to high oltp traffic > but it does require time and money as you scale up, TANSTAAFL... I'm > sure it can be done, Postgres is very flexible, though whether it's a > perfect fit with your needs or not I can't say. > > You might also consider whether one of the existing streaming > database > system providers would give you a development license for free or on > a > trial basis if you hope to be making big money down the road. > > -- > greg > http://mit.edu/~gsstark/resume.pdf In the its a small world category I just ran across a streaming database called Telegraph(http://telegraph.cs.berkeley.edu/telegraphcq/v2.1/) yesterday. It is based on Postgres 7.3.2, so approach with caution. Adrian Klaver akla...@comcast.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] Stock Market Price Data & postgreSQL? HELLPPP Please
On Wed, Aug 19, 2009 at 4:22 PM, Sanjay Arora wrote: > Hello All > > I want to store stock market price data in postgreSQL and some of my > associates are saying this is not workable, not only in postgreSQL but > in any database. Arguments given are listed below. Kindly comment and > if possible suggest solutions and pointers to further reading. > > - This is Time Series Data (I don't know what that is except that it > relates to data marked/related to time) and not suited to a RDBMS. > - You need it in Esper (a CEP engine used by Marketcetera, an open > source trading platform) which needs to get a data stream, whereas an > RDBMS will return a recordset. > - A RDBMS including postgreSQL simply cannot handle the amount of > real-time data coming in from a stock exchange. > - A RDBMS simply cannot provide data, even if you build streams from > recordsets returned, at the speeds required by any technical analysis > charting program. > > My level of knowledge is basic SysAdmin & Programming Concepts, not > actual programming. I will be getting someone to build a solution for > me. Need the correct direction please. Again, shoestring budget, all > open source pointers please, unless they are for reading & general > knowledge. > > I am hoping for an adapted postgreSQL solution, can't spend more than > few hundred dollars for thisso please...HELLLPPP ;-) I think your first step is to stand back, think about the problem as a whole and see if you can't define your requirements better: *) how much data comes in each day? how much rolls out? *) what types of analysis of the data would you like to do? *) what are your performance expectations? For example, try and describe what you would like to do in detail. The data you would import, how you would like it structured, and a typical query. While the amount of price data a stock market produces is immense, these types of problems tend to scale very well horizontally. So let's define the basic amount of work you'd like to do, then build off that. 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] Stock Market Price Data & postgreSQL? HELLPPP Please
On Wed, Aug 19, 2009 at 9:22 PM, Sanjay Arora wrote: > - This is Time Series Data (I don't know what that is except that it > relates to data marked/related to time) and not suited to a RDBMS. > - You need it in Esper (a CEP engine used by Marketcetera, an open > source trading platform) which needs to get a data stream, whereas an > RDBMS will return a recordset. > - A RDBMS including postgreSQL simply cannot handle the amount of > real-time data coming in from a stock exchange. > - A RDBMS simply cannot provide data, even if you build streams from > recordsets returned, at the speeds required by any technical analysis > charting program. There are whole companies doing nothing but providing streaming RDBMS used by traders, airline reservation systems, etc. c.f Streambase, and Truviso amongst others. I'm not aware of any open source streaming databaes so for a shoestring budget you're going to be limited to using existing tools. There are certainly people who scale up Postgres to high oltp traffic but it does require time and money as you scale up, TANSTAAFL... I'm sure it can be done, Postgres is very flexible, though whether it's a perfect fit with your needs or not I can't say. You might also consider whether one of the existing streaming database system providers would give you a development license for free or on a trial basis if you hope to be making big money down the road. -- greg http://mit.edu/~gsstark/resume.pdf -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Stock Market Price Data & postgreSQL? HELLPPP Please
Hello All I want to store stock market price data in postgreSQL and some of my associates are saying this is not workable, not only in postgreSQL but in any database. Arguments given are listed below. Kindly comment and if possible suggest solutions and pointers to further reading. - This is Time Series Data (I don't know what that is except that it relates to data marked/related to time) and not suited to a RDBMS. - You need it in Esper (a CEP engine used by Marketcetera, an open source trading platform) which needs to get a data stream, whereas an RDBMS will return a recordset. - A RDBMS including postgreSQL simply cannot handle the amount of real-time data coming in from a stock exchange. - A RDBMS simply cannot provide data, even if you build streams from recordsets returned, at the speeds required by any technical analysis charting program. My level of knowledge is basic SysAdmin & Programming Concepts, not actual programming. I will be getting someone to build a solution for me. Need the correct direction please. Again, shoestring budget, all open source pointers please, unless they are for reading & general knowledge. I am hoping for an adapted postgreSQL solution, can't spend more than few hundred dollars for thisso please...HELLLPPP ;-) With best regards. Sanjay. -- 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] Re: Unit conversion database (was: multiple paramters in aggregate function)
Greg Stark wrote: > On Wed, Aug 19, 2009 at 8:24 PM, Alvaro > Herrera wrote: > >> [1] It doesn't correctly convert °C to °F or vv, that was one of the > >> first things I tried. > > > > Seems it's easy to misuse it. You need tempF(x) and tempC notation for > > converting absolute temperature differences: > > > > You have: tempF(212) > > You want: tempC > > 100 > > That depends on whether you're converting a temperature or a > temperature difference. If you want to know what a 100 degree C drop > in temperature equates to in Fahrenheit the answer is not 212 but > rather 180. Right -- and there's a different interface for that. You have: 100 degC You want: degF * 180 / 0.005556 > I think it would be useful to have a builtin data type which contained > a float and an opaque text unit. It could support linear operations > like +, -, and sum() by just throwing an error if the units didn't > match. This sounds very much like Martijn's tagged types. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: Unit conversion database (was: multiple paramters in aggregate function)
On Wed, Aug 19, 2009 at 8:24 PM, Alvaro Herrera wrote: >> [1] It doesn't correctly convert °C to °F or vv, that was one of the >> first things I tried. > > Seems it's easy to misuse it. You need tempF(x) and tempC notation for > converting absolute temperature differences: > > You have: tempF(212) > You want: tempC > 100 That depends on whether you're converting a temperature or a temperature difference. If you want to know what a 100 degree C drop in temperature equates to in Fahrenheit the answer is not 212 but rather 180. I think it would be useful to have a builtin data type which contained a float and an opaque text unit. It could support linear operations like +, -, and sum() by just throwing an error if the units didn't match. Then you could add an add-on function which converted one such datum to another with a desired new units by calling out to the units program. That would allow people to store values with heterogenous units. So for example you could have SMART stats in a single table where the time values, unitless values, and temperature values are all in the same column. As long as you only compare, say, drive temperatures to max temperatures you never actually need to know about the units. It would serve as an assertion check to ensure you don't compare drive temperatures to error counts or something like that. -- greg http://mit.edu/~gsstark/resume.pdf -- 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] Unit conversion database (was: multiple paramters in aggregate function)
Alban Hertroys wrote: > >There's also a Debian package which comes with a text format > >units database: > > > > http://packages.debian.org/source/sid/units > > > >The original source for that: > > > >This package was put together by me, James Troup , > >from the GNU sources, which I obtained from > >sunsite.doc.ic.ac.uk:/pub/gnu/units-1.54.tar.gz. > > I don't consider that tool very reliable[1]. A number of their > concepts are probably usable though. I have it's source in my source > tree (FreeBSD), so ample opportunity to peek. > > [1] It doesn't correctly convert °C to °F or vv, that was one of the > first things I tried. Seems it's easy to misuse it. You need tempF(x) and tempC notation for converting absolute temperature differences: You have: tempF(212) You want: tempC 100 -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] Changing owner of pg_toast tables
Glen Jarvis writes: > I found this thread online because I have the same problem. > =# drop role ; > ERROR: role "" cannot be dropped because some objects depend on it > DETAIL: owner of type pg_toast.pg_toast_51797 > 1 objects in database I think this is a known issue: http://archives.postgresql.org/pgsql-general/2009-02/msg01021.php http://archives.postgresql.org/pgsql-committers/2009-02/msg00224.php If you're not running a very recent minor release then you'd be subject to the bug, and even if you were this could be leftover damage from a previous episode. If your situation doesn't fit the summary in the second message cited above, we'd be interested to have more details. 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] Changing owner of pg_toast tables
Mark Styles-2 wrote:
>
> On Thu, Jan 29, 2009 at 02:11:37PM -0500, Tom Lane wrote:
>> Mark Styles writes:
>> > Thanks, I managed to clear out the offending dependencies. relowner was
>> > actually set correctly, but the pg_shdepend records were wrong.
>>
>> Hmm ... what actually was in the pg_shdepend entries?
>
> I guess I should've noted that down eh? From memory, the classid was
> the oid of the pg_toast object, the refobjid was the oid of the role,
> the deptype was 'o', I don't recall what the other values were.
>
> I'll keep my eye out for more problems as I work through tidying this
> database.
>
I found this thread online because I have the same problem. So, I thought
I'd share what I've discovered. I could not drop a role. pg_dumpall doesn't
show any dependencies to this toast table. Here is output (with some
information to protect the privacy of the company I am working
for):
=# drop role ;
ERROR: role "" cannot be dropped because some objects depend on it
DETAIL: owner of type pg_toast.pg_toast_51797
1 objects in database
I found the role in question (with oid = 1237) from pg_roles. Then, I was
able to find a list of dependencies:
postgres=# select * from pg_shdepend where refobjid=17158;
dbid | classid | objid | refclassid | refobjid | deptype
---+-+---++--+-
16388 |1247 | 51802 | 1260 |17158 | o
52721 |1247 | 51802 | 1260 |17158 | o
pfacts003=# select * from pg_class where oid = 1247;
relname | relnamespace | reltype | relowner | relam | relfilenode |
reltablespace | relpages | reltuples | reltoastrelid | reltoastidxid |
relhasindex | relisshared | relkind | relnatts | relchecks | reltriggers |
relukeys | relfkeys | relrefs | relhasoids | relhaspkey | relhasrules |
relhassubclass |relacl
-+--+-+--+---+-+---+--+---+---+---+-+-+-+--+---+-+--+--+-+++-++---
pg_type | 11 | 71 | 10 | 0 |1247 |
0 |8 | 329 | 0 | 0 | t | f
| r | 23 | 0 | 0 |0 |0 |
0 | t | f | f | f | {=r/postgres}
(1 row)
pfacts003=# select * from pg_class where oid = 1260;
relname | relnamespace | reltype | relowner | relam | relfilenode |
reltablespace | relpages | reltuples | reltoastrelid | reltoastidxid |
relhasindex | relisshared | relkind | relnatts | relchecks | reltriggers |
relukeys | relfkeys | relrefs | relhasoids | relhaspkey | relhasrules |
relhassubclass | relacl
---+--+-+--+---+-+---+--+---+---+---+-+-+-+--+---+-+--+--+-+++-++-
pg_authid | 11 | 10281 | 10 | 0 |1260 |
1664 |1 |12 | 10290 | 0 | t |
t | r | 11 | 0 | 1 |0 |
0 | 0 | t | f | f | f |
{postgres=arwdRxt/postgres}
(1 row)
This may help explain what happened. I can't give any history of the
situation since I inherited this database. But, I think the above should be
somewhat helpful. Is it possible that the person who first "needed" a toast
table gets the type build by default and therefore owns it?
--
View this message in context:
http://www.nabble.com/Changing-owner-of-pg_toast-tables-tp21728869p25048954.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] Unit conversion database (was: multiple paramters in aggregate function)
On 19 Aug 2009, at 19:20, Karsten Hilbert wrote: Alban, I think having an installable schema for units of measure with definitions and functions would be a great addition to PostgreSQL. Karsten, Thanks for the praise and the links. I for one know we would use it in GNUmed (wiki.gnumed.de). A few points: Would these guys be of use as a source for reference data ? http://unitsofmeasure.org/ That looks certainly interesting, especially the fact that they provide a source of units and conversions in an XML format. Although their conversion formulas don't look all that easy to parse. I've run into a few of the problems they mention already; for example the slight differences between imperial and US units of measurement with the same names and abbreviations... You may want to think about whether there's use in combining units with tagged types: http://svana.org/kleptog/pgsql/taggedtypes.html Yes, I've been thinking the same thing. I had it bookmarked already for the very purpose of checking it out and see how I could use tagged types with units. There's also a Debian package which comes with a text format units database: http://packages.debian.org/source/sid/units The original source for that: This package was put together by me, James Troup , from the GNU sources, which I obtained from sunsite.doc.ic.ac.uk:/pub/gnu/units-1.54.tar.gz. I don't consider that tool very reliable[1]. A number of their concepts are probably usable though. I have it's source in my source tree (FreeBSD), so ample opportunity to peek. [1] It doesn't correctly convert °C to °F or vv, that was one of the first things I tried. The current version of the package was obtained from ftp://ftp.gnu.org/gnu/units by John Hasler, the current Debian maintainer. I think this database is fairly usable in its current state. Any more development on it Yes please ! :-) He he, all right then! There certainly are some things left to improve. One thing I noticed from the links you sent is that I ignored a few units used in medicine assuming they were deprecated ages ago - apparently not... Then again, encouraging their usage may not be the best thing to do, but who am I to decide what units people use eh? warrants its own project page somewhere and taking it off-list, I'll no longer pester you with updates on this ;) Ah, no problem. Please keep posting release announcements. Maybe on -announce if so. Yes, announce would be the right place. I dislike it when people start using this list for announcements of new versions of their software, so let's not start doing that myself :) Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4a8c44fa10131730049303! -- 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] pg_restore returns always error if backup is greater than 2 GB
"Andrus Moor" writes: > I noticed that backups created by pg_dump are not usable if backup file size > is greater than 2 GB. > Backups are create in 8.1 This is a known bug that was fixed more than two years ago ... but not in 8.1, which was already considered unsupportable on Windows at the time. You *really* need to get off of 8.1. 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] Postgre RAISE NOTICE and PHP
On Wed, Aug 19, 2009 at 8:41 AM, Randal L.
Schwartz wrote:
>> "Clemens" == Clemens Schwaighofer
>> writes:
>
> Clemens> Just in my opinion, this regex is completely too large. For basic
> Clemens> validating something like:
> Clemens>
> ^[A-Za-z0-9!#$%&'*+-\/=?^_`{|}~][A-Za-z0-9!#$%&'*+-\/=?^_`{|}~\.]{0,6...@[a-za-z0-9-]+(\.[a-zA-Z0-9-]{1,})*\.([a-zA-Z]{2,4}){1}$
> Clemens> works very well
>
> Fails on ".mobile" TLD. Has a pointless {1} in it, which does
> absolutely nothing, providing that the creator of the regex was already
> missing a few clues.
Remonds me of the saying that for every complex problem there is a
simple, elegant and incorrect solution.
--
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] Requesting help on PostgreSQL Replication
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > Major Replication Requirements (from the Central Office side): > > 1. Apply updates on certain tables like products, customers, > users and settings and commit on the remote location POS. > 2. Query Sales, Transactions, Inventory and other tables > for reporting purposes from POS. > 3. Fetch update at least within 4-8 hours interval. > 4. Generate Backups Not sure what "generate backups" means in the replication sense, but if you have sites that are flaky, you might want to look at Bucardo: http://bucardo.org/ It does master->slave (like Slony), but also does master<->master if you need it. - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation PGP Key: 0x14964AC8 200908191342 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkqMOZEACgkQvJuQZxSWSsgo4gCfYhM1itn1KyVt4l/nEG4nkek5 w5MAoMrnYEbtrqSoKpcHR1m2qtTem2z8 =T0gZ -END PGP SIGNATURE- -- 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] Monitoring the sequence cycles
> How about disabling cycling and doing your own monitoring? > It seems a bit fiddly because sequence values are created "outside" > of any transaction and hence if you write something into a logging > table you're going to loose it if the transaction rolls back. The sequences are expected to cycle a few times per year, I use them to generate Ids accross large timed tables that are dropped when obsolete. I want to make sure that I have enough free Ids left... I will probably implement a "fuzzy" monitoring while logging the current values on a regulary basis. Thanks, Marc Mmain -- Sam http://samason.me.uk/ -- 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] Wich the best way to control the logic of a web application?
On Aug 19, 2009, at 9:46 AM, Andre Lopes wrote: What do you think aboout this? Should I mix logic in Database and PHP or should I control the logic only in the PHP? There are no real hard and fast rules, of course, just rules of thumb. Here are some of mine. First, remember that there is rarely "an application" for a database. One of the most useful parts of a database is that it provides a central repository for data across multiple applications. For now, the web interface might be the only way to get at the data, but live systems tend to grow clients. Soon, you have external processes handling data interchange with other companies, processes sending customers email... This means two things: 1. You want to make sure that the database maintains as much data integrity as it can, as long as that data integrity really must be enforced across all applications. For example, in an inventory database, if an item's inventory level *always* equals all inventory receivings minus all shipments, then that's a good candidate for a rule enforced by a trigger in the database. 2. On the other hand, be careful of business logic that isn't universal creeping into the database. For example, for the web application, you might send an email to a user when they register. But do you *really* want to do *exactly the same thing* on a bulk load of new users from an external source? In other examples, page-to-page flow is probably not a great candidate for encoding in the database; I would think that it makes far more sense for the database to store the state of the various business objects, and let the PHP application decide what to display to the user. Similarly, formatting is often a better idea in PHP, since you may have more information about the right kind of formatting. (Kind of a shame, since PostgreSQL's data type text formatting is in many ways superior to PHP's!) However, a business rule that is designed to prevent bad data from entering the database is a good candidate from being enforced in the database. Lastly, as a matter of preference, I don't like putting things into the database that can block for extended periods, like sending email. I think those are better put into external processes that run against the database. (And, of course, putting things that can block for an extended period into the web application isn't good either.) Here's one concrete example of a decision I made recently; of course, I may have made the wrong one. :) Customers on this side can search across a large number of different types of entities, including catalog items, catalog categories, product buying guides, articles, etc. The design required that these be presented in particular ways, separate one from the other. I could have implemented a procedure in the database which took the search and returned the results, but I decided that would be pushing too much of the UI display use case into what should be a data store. Instead, the application does separate queries for each type, and unifies the results. (This does have a negative performance characteristic, since the application has to make multiple trips to the database instead of calling one function, but it wasn't significant enough to be a problem.) Hope this helps! -- -- Christophe Pettus x...@thebuild.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] index "pg_authid_rolname_index" is not a btree
"Andrus Moor" writes: > pg_amop in mydb contains 5 rows. > pg_amop in template1 database contains large number of rows. > mydb does not contain user-defined operators. > How to repair pg_amop in mydb ? Well, you could try copying the physical file for pg_amop from template1 to mydb (and then reindexing it again). I am not holding out a lot of hope though. I think you're most likely going to run into a dead end, unfixable problem before you get any data out. > Most of backup size contains few big tables which are not required to > recover. Maybe you should forget about pg_dump and just see if you can COPY the tables you care about. 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] Unit conversion database (was: multiple paramters in aggregate function)
Alban, I think having an installable schema for units of measure with definitions and functions would be a great addition to PostgreSQL. I for one know we would use it in GNUmed (wiki.gnumed.de). A few points: Would these guys be of use as a source for reference data ? http://unitsofmeasure.org/ You may want to think about whether there's use in combining units with tagged types: http://svana.org/kleptog/pgsql/taggedtypes.html There's also a Debian package which comes with a text format units database: http://packages.debian.org/source/sid/units The original source for that: This package was put together by me, James Troup , from the GNU sources, which I obtained from sunsite.doc.ic.ac.uk:/pub/gnu/units-1.54.tar.gz. The current version of the package was obtained from ftp://ftp.gnu.org/gnu/units by John Hasler, the current Debian maintainer. > I think this database is fairly usable in its current state. Any more > development on it Yes please ! :-) > warrants its own project page somewhere and taking > it off-list, I'll no longer pester you with updates on this ;) Ah, no problem. Please keep posting release announcements. Maybe on -announce if so. Karsten -- Jetzt kostenlos herunterladen: Internet Explorer 8 und Mozilla Firefox 3 - sicherer, schneller und einfacher! http://portal.gmx.net/de/go/chbrowser -- 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] Wich the best way to control the logic of a web application?
On Wed, Aug 19, 2009 at 05:46:17PM +0100, Andre Lopes wrote: > What do you think aboout this? Should I mix logic in Database and PHP or > should I control the logic only in the PHP? As always, it depends! I tend to put things where ever it's most convenient, however data integrity and other invariants within your code will dictate some levels above which you can't place code. In your email address example before, this can easily be done in PHP (or even JavaScript, client side) as the database doesn't care whether it's getting a valid email address or not--it's just a character string to the database. To go to the other extreme, referential integrity is (almost?) always best done inside the database as it has all the information needed to do the right thing. -- Sam http://samason.me.uk/ -- 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] question about /etc/init.d/postgresql in PGDG
On Wed, 19 Aug 2009, Scott Marlowe wrote:
The only reason I noticed it was that I was building a server with a
separate /data partition for the db to live in, and went to edit
/etc/init.d/postgresql and was faced with two PGDATA assignments... I
too deleted the entire if else block when faced with it.
You should never edit that script, because then you'll be stuck resolving
conflicts if you upgrade and the packager makes a change to it to fix a
bug or something like that. If you want to relocate PGDATA, you should
change /etc/sysconfig/pgsql/postgresql instead and put your local
customizations there. That file is overlaid on top of the defaults just
after they're set:
# Override defaults from /etc/sysconfig/pgsql if file is present
[ -f /etc/sysconfig/pgsql/${NAME} ] && . /etc/sysconfig/pgsql/${NAME}
What I do is put *all* the defaults into that file, so that there's no
confusion about which version I'm using.
$ cat /etc/sysconfig/pgsql/postgresql
PGENGINE=/usr/bin
PGPORT=5432
PGDATA=/var/lib/pgsql/data
PGLOG=/var/lib/pgsql/pgstartup.log
The nice side-effect of this is that it makes it easy to set all these
values in a user's login profile, so that you can do things like run
pg_ctl manually instead of using the init scripts as root. Put something
like this in your profile:
. /etc/sysconfig/pgsql/postgresql
export PGDATA PGPORT PGLOG
export PATH="$PGENGINE:$PATH"
And then cycling the engine as the postgres user is as easy as:
pg_ctl start -l $PGLOG
pg_ctl stop
For my login, I add these two bits as well to make that easier, since I
never use the real start/stop commands anyway:
alias start="pg_ctl -D $PGDATA -l $PGLOG -w start && tail $PGLOG"
alias stop="pg_ctl -D $PGDATA stop -m fast"
--
* Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD
--
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] index "pg_authid_rolname_index" is not a btree
Tom, Thank you. Alvaro was right --- you've got damage in the system catalogs, not just their indexes. This looks like missing entries in pg_amop. postgres -D data mydb PostgreSQL stand-alone backend 8.1.9 backend> select * from pg_amop 1: amopclaid (typeid = 26, len = 4, typmod = -1, byval = t) 2: amopsubtype (typeid = 26, len = 4, typmod = -1, byval = t) 3: amopstrategy(typeid = 21, len = 2, typmod = -1, byval = t) 4: amopreqcheck(typeid = 16, len = 1, typmod = -1, byval = t) 5: amopopr (typeid = 26, len = 4, typmod = -1, byval = t) pg_amop in mydb contains 5 rows. pg_amop in template1 database contains large number of rows. mydb does not contain user-defined operators. How to repair pg_amop in mydb ? (You did say you reindexed all the system catalogs, right? If not it's possible this is only index damage, but I'm not very hopeful.) reindex system mydb reindex database mydb complete without errors. I suspect that if you did get to the point of being able to run pg_dump without error, you'd find just as much damage to the user data. I'm afraid this database is toast and you should write it off as a learning experience. Hardware fails, you need backups. Backup is 4.2 GB and is corrupted after 2 GB as I described in other thread. Also, backup is too old. Most of backup size contains few big tables which are not required to recover. I ran truncate commands for those tables. This reduces whole data directory size to 1.2 GB in uncompressed form. I know which tables contain data to be recovered. How to dump those tables out ? Andrus. -- 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] Postgre RAISE NOTICE and PHP
On Wed, Aug 19, 2009 at 02:11, Randal L. Schwartz wrote:
>> "Andre" == Andre Lopes writes:
>
> Andre> I'm developing a function with some checks, for example... to check if
> the
> Andre> e-mail is valid or not.
>
> How are you hoping to do this? The regex to validate an email
> address syntactically is pretty large:
>
> http://ex-parrot.com/~pdw/Mail-RFC822-Address.html
>
> And no, I'm not kidding. If your regex is smaller than that, you aren't
> validating email... you're validating something "kinda like email".
Just in my opinion, this regex is completely too large. For basic
validating something like:
^[A-Za-z0-9!#$%&'*+-\/=?^_`{|}~][A-Za-z0-9!#$%&'*+-\/=?^_`{|}~\.]{0,6...@[a-za-z0-9-]+(\.[a-zA-Z0-9-]{1,})*\.([a-zA-Z]{2,4}){1}$
works very well
> For example, is a valid email address. (Go
> ahead, try it... it has an autoresponder.)
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
--
★ Clemens 呉 Schwaighofer
★ IT Engineer/Web Producer/Planning
★ E-Graphics Communications SP Digital
★ 6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN
★ Tel: +81-(0)3-3545-7706
★ Fax: +81-(0)3-3545-7343
★ http://www.e-gra.co.jp
This e-mail is intended only for the named person or entity to which it is
addressed and contains valuable business information that is privileged,
confidential and/or otherwise protected from disclosure. If you received this
e-mail in error, any review, use, dissemination, distribution or copying of
this e-mail is strictly prohibited. Please notify us immediately of the error
via e-mail to disclai...@tbwaworld.com and please delete the e-mail from your
system, retaining no copies in any media.We appreciate your cooperation.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Problem with bacula and 8.3/8.4
Hi all, I've tried, in various combinations, bacula (2.4, 3.0) and pgsql 8.4 and 8.3 in an upgrade attempt. Whichever combo I try I get the following error: 19-Aug 02:24 bacula-dir JobId 1951: Fatal error: sql_create.c:789 Fill Path table Query failed: INSERT INTO Path (Path) SELECT a.Path FROM (SELECT DISTINCT Path FROM batch) AS a WHERE NOT EXISTS (SELECT Path FROM Path WHERE Path = a.Path) : ERR=ERROR: array size exceeds the maximum allowed (268435455) Batch is a temp table so I tried bumping temp_buffers up to 268MB but I still got the error. Anything I else I should try before I rollback the upgrade? tia, arturo -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Wich the best way to control the logic of a web application?
Hi, Now I'am with some doubts on how to control the logic of a web application? So... the web application uses PHP, object oriented and PostgresSQL Database. I have developed some procedures and functions in the database but I don't know if it is correct to mix logic in the database and PHP. What do you think aboout this? Should I mix logic in Database and PHP or should I control the logic only in the PHP? Best Regards, André.
Re: [GENERAL] Monitoring the sequence cycles
On Wed, Aug 19, 2009 at 03:53:29PM +0200, Marc Mamin wrote: > I'm looking for a way to track the wrap arounds of cycle sequences. How about disabling cycling and doing your own monitoring? It seems a bit fiddly because sequence values are created "outside" of any transaction and hence if you write something into a logging table you're going to loose it if the transaction rolls back. -- Sam http://samason.me.uk/ -- 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] question about /etc/init.d/postgresql in PGDG
2009/8/19 Tom Lane : > Scott Marlowe writes: >> In the init script from the PGDG rpms there's this block of code: > >> PGDATA=/var/lib/pgsql/data >> if [ -f "$PGDATA/PG_VERSION" ] && [ -d "$PGDATA/base/template1" ] >> then >> echo "Using old-style directory structure" >> else >> PGDATA=/var/lib/pgsql/data >> fi > >> Is it just me, or is the else extra noise? Just wondering if there's >> a reason a config variable is in two places at once. > > In the original coding, the first assignment was > > PGDATA=/var/lib/pgsql > > and thus the if-test did indeed do something useful with setting PGDATA > differently in the two cases. However, there is no reason whatsoever > for this initscript to be prepared to work with postmaster versions that > would be old enough for the if-test to succeed. I took the whole > if-block out of the just-updated Fedora RPMs, and would recommend the > same for PGDG. The only reason I noticed it was that I was building a server with a separate /data partition for the db to live in, and went to edit /etc/init.d/postgresql and was faced with two PGDATA assignments... I too deleted the entire if else block when faced with it. -- 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] "Could not open relation XXX: No such file or directory"
On Wed, Aug 19, 2009 at 4:26 AM, Alan Millington wrote: > I am running Postgres 8.1.4 on Windows XP Professional Service Pack 3. In addition to the very helpful advice Craig provided, you also need to look at upgrading your pgsql install. 8.1 is no longer supported on windows due to issues in it which were deemed unfixable without major changes. 8.2 and above are supported on windows. I'd suggest migrating off of 8.1 and onto at least 8.3, if not 8.4. -- 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] Unit conversion database (was: multiple paramters in aggregate function)
On 18 Aug 2009, at 19:59, Alban Hertroys wrote:
Hello all,
Inspired by the original discussion on aggregating quantities of
different units I made a start at a unit conversion database and the
result is here: http://solfertje.student.utwente.nl/documents/units.sql
I just uploaded an updated version that handles scaling of units
properly for at least the base-units. For example:
development=> select convert_unit(28, '°C', '°F');
convert_unit
-
82.3960
(1 row)
development=> select convert_unit(28, 'mg', 'gr');
convert_unit
0.43210603388236005822
(1 row)
development=> select convert_unit(28, 'lb', 'kg');
convert_unit
-
10.450768204800
(1 row)
development=> select convert_unit(28, 'kg', 'lb');
convert_unit
-
75.0184086601319546
(1 row)
development=> select convert_unit(28, 'dm', 'mm');
convert_unit
---
2800.
(1 row)
Problem areas are:
- It doesn't contain every possible conversion yet.
Some units are probably just plain wrong too. I don't know every
unit in the list, that's why. I'm especially unfamiliar with
imperial units and some of the more esoteric units. Corrections and
additions are welcome.
I added several units from http://en.wikipedia.org/wiki/Conversion_of_units
(my original source was Binas, an old book I had left over from high-
school days). Imperial units should be more complete now.
Still, if you notice any obvious errors or omissions, let me know.
- It can't handle unit scaling yet ('mm' to 'm' for example).
There are some units in there that are scaled by default ('kg' is
the standard unit for mass and not 'g'), and some units seem to be
not scalable at all (ever heard of 'mK' - 'milliKelvin'?). This may
be solved by adding a base_scale column which could be NULL if not
applicable.
This is working now.
- Some units are combinations of multiple base-units that would
require parsing the combined unit to determine how to scale or
convert parts of it. I haven't found a good way of handling that
yet, maybe I just shouldn't... I have a feeling that at the very
least parsing units should only happen if the unit isn't a base-
unit, which can simply be flagged.
These are flagged now, but that's about it. No conversions for such
units have been entered yet.
One change is that the formatting of the units has changed to
something less likely to give ambiguous results (eg. 'ms-1' has been
changed to 'm.s^-1').
I think this database is fairly usable in its current state. Any more
development on it warrants its own project page somewhere and taking
it off-list, I'll no longer pester you with updates on this ;)
Have a nice day!
Alban Hertroys
--
Screwing up is the correct approach to attaching something to the
ceiling.
!DSPAM:737,4a8c220b10137643883901!
--
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] text type has no default operator class for GIN?
Bob Gobeille writes: > Yes, I was looking for full text searching in english. Since my > postgresql.conf contained: > default_text_search_config = 'pg_catalog.english' > doesn't this specify the parser, dictionary, and template to use for > full text searching in english? It does, but that's a default for tsvector, not bare text. 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] index "pg_authid_rolname_index" is not a btree
"Andrus Moor" writes: > Alvaro, >> You can get around that particular problem by reindexing the pg_authid >> table. But my guess is that you'll find that there's corruption >> elsewhere that's not so easily recoverable ... > Thank you. > reindexing system tables and whole database succeeds. > After that I can connect to database containing data to recover. > However pg_dump fails: > pg_dump: Error message from server: ERROR: could not identify an ordering > operator for type name Alvaro was right --- you've got damage in the system catalogs, not just their indexes. This looks like missing entries in pg_amop. (You did say you reindexed all the system catalogs, right? If not it's possible this is only index damage, but I'm not very hopeful.) I suspect that if you did get to the point of being able to run pg_dump without error, you'd find just as much damage to the user data. I'm afraid this database is toast and you should write it off as a learning experience. Hardware fails, you need backups. 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] text type has no default operator class for GIN?
On Aug 18, 2009, at 6:21 PM, Tom Lane wrote: What GIN indexes are good for is indexing equality queries on the components of something the database otherwise thinks of as a single object. For instance you can GIN-index searches for arrays containing a particular value as a member. Now type text doesn't have any built-in notion of a component, other than individual characters, which aren't normally that interesting to search for. What I suppose the OP has in mind is full-text searching, which is looking for component *words*. But "word" is a very language- and context-dependent concept. And defining which words are to be considered equal for searching purposes is even more so. If we'd hard-wired one notion of "word" into datatype text, it wouldn't be very flexible. The point of the tsvector layer is to have a configurable way to extract searchable words from a chunk of text. There are also some implementation advantages like not having to repeat that processing constantly during a search --- but the main point is having a place to define what a word is and what search equality means. Yes, I was looking for full text searching in english. Since my postgresql.conf contained: default_text_search_config = 'pg_catalog.english' doesn't this specify the parser, dictionary, and template to use for full text searching in english? I should have mentioned the above in my post but since it was in the original conf file (debian install) I didn't think of it. Bob Gobeille b...@fossology.org -- 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] text type has no default operator class for GIN?
On Tue, Aug 18, 2009 at 08:21:49PM -0400, Tom Lane wrote:
> Sam Mason writes:
> > On Tue, Aug 18, 2009 at 03:50:47PM -0600, Bob Gobeille wrote:
> >>> CREATE INDEX "ufile_name_search" ON "public"."uploadtree" USING GIN
> >>> ("ufile_name");
> >>> ERROR: data type text has no default operator class for access method
> >>> "gin"
>
> > Not sure if understand very well myself, but GIN indexes can only speed
> > up specific access patterns and these are exposed through various
> > different operators.
>
> What GIN indexes are good for is indexing equality queries on the
> components of something the database otherwise thinks of as a single
> object. For instance you can GIN-index searches for arrays containing
> a particular value as a member.
Yup, that's a much better description than I could muster!
> What I suppose the OP has in mind is full-text
> searching, which is looking for component *words*. But "word" is a
> very language- and context-dependent concept.
That's what I was trying to get the OP to think about when I said "what
are you expecting PG do to when you create a GIN index on this TEXT
column" but reading it back now I was being my normal oblique self.
Writing nice emails is depressingly difficult!
--
Sam http://samason.me.uk/
--
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] Postgre RAISE NOTICE and PHP
> "Clemens" == Clemens Schwaighofer
> writes:
Clemens> Just in my opinion, this regex is completely too large. For basic
Clemens> validating something like:
Clemens>
^[A-Za-z0-9!#$%&'*+-\/=?^_`{|}~][A-Za-z0-9!#$%&'*+-\/=?^_`{|}~\.]{0,6...@[a-za-z0-9-]+(\.[a-zA-Z0-9-]{1,})*\.([a-zA-Z]{2,4}){1}$
Clemens> works very well
Fails on ".mobile" TLD. Has a pointless {1} in it, which does
absolutely nothing, providing that the creator of the regex was already
missing a few clues.
That's the problem with these kinds of regex... you test it on what
you know, but you're not consulting the *actual* *internet* specifications
(which have been readily available since the dawn of Internet time).
Either use the regex I pointed to already, or stay with the simpler:
/\...@.*\s/
which will at least not deny anyone with a *perfectly legitimate* email
address from making it into your system.
Or, use your regex *only* in an *advice* category, with the ability
for the user to say "yes, I'm really sure this is my address".
Please, for the sake of the net, do the Right Thing here. This is
what I'm arguing for. Anything less than that, and your code deserves
to end up in thedailywtf.com as an example of what *not* to do.
--
Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777 0095
http://www.stonehenge.com/merlyn/>
Smalltalk/Perl/Unix consulting, Technical writing, Comedy, etc. etc.
See http://methodsandmessages.vox.com/ for Smalltalk and Seaside discussion
--
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] Function Logging
On Tuesday 18 August 2009 8:08:39 pm Craig Ringer wrote: > On 18/08/2009 9:26 PM, Adrian Klaver wrote: > > On Monday 17 August 2009 8:50:09 pm Andrew Bartley wrote: > >> So the information i have thus far is that, I am not easily able to log > >> the statements from a function. > >> > >> Does anyone know why it was removed... that is it 7.2 logged this > >> information. > >> > >> Thanks Again > >> > >> Andrew Bartley > > > > You are going to have to show an example of what you want and/or provide > > a better description of what you wish to achieve. At this point I ,for > > one, am confused as to what you want. > > It sounds to me like the OP is saying that in 7.2 "log_statement" logged > each statement of a PL/PgSQL function where in 8.3 it does not. > > -- > Craig Ringer Now I understand. Thanks, -- Adrian Klaver akla...@comcast.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] Postgre RAISE NOTICE and PHP
On Aug 18, 2009, at 16:45 , Scott Marlowe wrote: On Tue, Aug 18, 2009 at 10:57 AM, Andre Lopes wrote: I need to know if it is possible to show this RAISE NOTICE when I run this function from PHP. http://www.php.net/manual/en/function.pg-last-notice.php Thanks, Scott. Is there an equivalent in PDO? Looking through the docs I don't see one. It'd also be nice to be able to get at any/all of the additional information that's passed back: warning, context, hint, etc. Michael Glaesemann grzm seespotcode net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Monitoring the sequence cycles
Hello, I'm looking for a way to track the wrap arounds of cycle sequences. I also couldn't find the view/catalog where sequence definitions are kept; this could be a good place to store at least the timestamp of the last wrap around. I'm looking for a way to log all wrap arounds, thought I guess this is feature request... best regards, Marc Mamin
Re: [GENERAL] index "pg_authid_rolname_index" is not a btree
Alvaro, You can get around that particular problem by reindexing the pg_authid table. But my guess is that you'll find that there's corruption elsewhere that's not so easily recoverable ... Thank you. reindexing system tables and whole database succeeds. After that I can connect to database containing data to recover. However pg_dump fails: bin\pg_dump -f recover.backup -i -v -F c -h localhost -p 5433 -U postgres mydb pg_dump: reading schemas pg_dump: reading user-defined functions pg_dump: reading user-defined types pg_dump: SQL command failed pg_dump: Error message from server: ERROR: could not identify an ordering operator for type name HINT: Use an explicit ordering operator or modify the query. pg_dump: The command was: SELECT tableoid, oid, conname, pg_catalog.pg_get_constraintdef(oid) AS consrc FROM pg_catalog.pg_constraint WHERE contypid = '10635':: pg_catalog.oid ORDER BY conname pg_dump: *** aborted because of error How to recover data from this database ? Should I re-create ordering operator in some way ? Andrus. -- 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] "Could not open relation XXX: No such file or directory"
On 19/08/2009 6:26 PM, Alan Millington wrote: 2009-08-19 03:06:45 ERROR: could not read block 0 of relation 1663/52752/52896: No such file or directory Clearly something is amiss, but I don't know what. I should be grateful for any suggestions as to what I should check. Got a virus scanner installed? If so, remove it (do not just disable it) and see if you can reproduce the problem. Ditto anti-spyware software. You should also `chkdsk' your file system(s) and use a SMART diagnostic tool to test your hard disk (assuming it's a single ATA disk). -- Craig Ringer -- 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] A history procedure that prevents duplicate entries
On 16 Aug 2009, at 17:38, Madison Kelly wrote: Besides that, you don't need the SELECT statement or the RECORD- type variable as the data you need is already in the NEW and OLD records. But, you only have an OLD record when your trigger fired from an UPDATE, so you need to check whether your trigger fired from INSERT or UPDATE. So, what you need is something like: IF TG_OP = 'INSERT' THEN hist_radical := NEW; ELSE -- TG_OP = 'UPDATE' hist_radical := OLD; END IF; INSERT INTO history.radical (rad_id, rad_char, rad_name) VALUES (hist_radical.rad_id, hist_radical.rad_char, hist_radical.rad_name); To help me improve my understanding of procedures, how would this prevent an UPDATE from creating a new entry in the history schema when all the column values are the same as the last entry in history? It doesn't, as it wasn't entirely clear to me how you wanted it to behave. To prevent duplicate history entries from updates you would need to compare the values of NEW and OLD and return if they're equal. In 8.4 that's as simple as checking that NEW IS DISTINCT FROM OLD, but in earlier versions it's a bit more involved. There was a discussion about this very topic here recently. Alban Hertroys -- Screwing up is the correct approach to attaching something to the ceiling. !DSPAM:737,4a8bd41d10131434511488! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] "Could not open relation XXX: No such file or directory"
I am running Postgres 8.1.4 on Windows XP Professional Service Pack 3.
Yesterday when attempting to select from one particular database I repeatedly
encountered errors such as the following:
2009-08-18 10:49:54 ERROR: could not open relation 1663/51796/1247: No such
file or directory
2009-08-18 10:49:54 STATEMENT: SELECT typname FROM pg_type WHERE oid = 1043
1247 is pg_type. In Explorer I could see the file 51796/1247, and the timestamp
indicated that it had not recently been updated. I had never previously seen
this error.
As that database is used only for testing, I dropped it and loaded a new one
from a dump. However, earlier today a colleague working against this new
database experienced a slightly different error on several occasions:
2009-08-19 03:06:45 ERROR: could not read block 0 of relation
1663/52752/52896: No such file or directory
2009-08-19 03:06:45 STATEMENT: SELECT * FROM "RP"."RP_ATTRIBUTE" WHERE rp_id =
(('MM100')::text)
52752/52896 is the Primary Key index on the referenced table. Again, I can see
the file in Explorer, and it has not been updated since the database was
created. When I try the same Select statement now I get no error.
Clearly something is amiss, but I don't know what. I should be grateful for any
suggestions as to what I should check.
[GENERAL] SPI_ERROR_CONNECT within pl/pgsql PG 8.4
Hello, Somebody has solved the problem that was not present in 8.3.x ? Thankyou Omar
[GENERAL] pg_restore returns always error if backup is greater than 2 GB
I noticed that backups created by pg_dump are not usable if backup file size is greater than 2 GB. Backups are create in 8.1 I tried to restore them in 8.4. Backup contains many tables. There is a large table, attachme containing bytea field. This table grows rapidly. If .backup file size is bigger that 2 GB, pg_restore always fails for every table restored after that table: C:/Program Files/PostgreSQL/8.4/bin\pg_restore.exe --host localhost --port 5432 --username postgres --dbname mydb --verbose "\\ls\share\my cackups\backups\090703 my backup.backup" pg_restore: connecting to database for restore pg_restore: creating SCHEMA firma1 pg_restore: creating SCHEMA firma2 pg_restore: creating SCHEMA firma3 pg_restore: creating SCHEMA firma4 pg_restore: creating SCHEMA firma5 pg_restore: creating SCHEMA firma6 pg_restore: creating SCHEMA firma7 pg_restore: creating SCHEMA firma8 pg_restore: creating SCHEMA firma9 pg_restore: creating SCHEMA public pg_restore: creating COMMENT SCHEMA public pg_restore: creating PROCEDURAL LANGUAGE plpgsql pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 2214; 2612 16787 PROCEDURAL LANGUAGE plpgsql postgres pg_restore: [archiver (db)] could not execute query: ERROR: language "plpgsql" already exists Command was: CREATE PROCEDURAL LANGUAGE plpgsql; pg_restore: creating DOMAIN ebool pg_restore: creating FUNCTION dok_seq_trig() ... pg_restore: restoring data for table "attachme" pg_restore: restoring data for table "bilkaib" pg_restore: [custom archiver] found unexpected block ID (654399830) when reading data -- expected 12781 pg_restore: *** aborted because of error Process returned exit code 1. How to recover data from tables in backup after attachme table ? Andrus. Environment: Backups are created in PostgreSql 8.1 running in Windows pg_dump is used to create backup copies to Buffalo Linkstation Pro ls-320 GB external disk device ( \\ls ) connected to LAN backup command: pg_dump.exe -ibv -Z3 -f \\ls\backup\mybackup.backup -Fc -h localhost -U admin mydb What causes this ? is it Buffalo device, Windows/LAN or PostgreSql 8.1 issue ? Will upgrading to 8.4 fix this ? -- 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] PQgetlength vs. octet_length()
Michael Clark wrote: > That is what Pierre pointed out, and you are both right. I > am using the text mode. > > But it seems pretty crazy that a 140meg bit of data goes to > 1.3 gigs. Does that seem a bit excessive? > > I avoided the binary mode because that seemed to be rather > confusing when having to deal with non-bytea data types. The > docs make it sound like binary mode should be avoided because > what you get back for a datetime varies per platform. That is true. The best thing would be to retrieve only the bytea columns in binary format and the rest as text. The Bind message in the frontend/backend protocol allows to specify for each individual result column whether it should be text or binary ( http://www.postgresql.org/docs/current/static/protocol-message-formats.html ) but the C API only allows you to get *all* result columns in either binary or text. You could resort to either speaking line protocol with the backend, (which is probably more than you are ready to do), or you could create a separate query only for the bytea value. Yours, Laurenz Albe -- 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] Idle processes chewing up CPU?
On 19/08/2009 1:34 PM, Brendan Hill wrote: Hi Craig, thanks for the analysis. If I attach a debugger on the runaway child process, will this halt execution for all the other child processes (ie. freeze the server)? And, can I attach Visual Studio C++ 2008, or is there a recommended debugger for Windows debugging? Visual C++ 2008's debugger should be fine - and it's certainly a lot nicer to use than windbg.exe . LOTS nicer. I'm surprised you have VS 2008 on your production server, though - or are you planning on using remote debugging? Anyway: If you attach to a given backend, execution of the other backends won't freeze. If you promptly unpause execution of the backend you attached to everything will run normally. You might not want to interrupt the backend's execution for too long at a time though, as my understanding is that Pg does have tasks that require synchronization across all backends and leaving one in a state of paused execution for too long might slow things down. I did some quick testing before posting. First, I downloaded and unpacked the 8.4.0 sources since that's what I'm running on my workstation. I then establishined two sessions to an otherwise idle 8.4 DB on WinXP, then attaching VS 2008 EE's debugger to one of them: Tools -> Attach to Process, check "show processes from all users", select the target postgres.exe by pid, attach. It took a while for VS to load symbols for the first time, but the other backend was responsive during that time. When VS finished loading symbols it auto-resumed execution of the backend. When I pause execution the other backend remains responsive. I can still establish new connections too. With execution running normally I added a breakpoint at pq_recvbuf: Debug -> New Breakpoint -> Break at Function (CTRL-B), "pq_recvbuf", line 1 char 1 language "C", OK then issued a query to the backend I was debugging. It processed the query and then execution stopped at the breakpoint. I was prompted to locate the source file I'd broken in, and when I did so it showed an execution marker at the appropriate point, I could step execution through the sources, etc. When I was done, I just detached from the process with Tools -> Detach All, leaving it running as before. In your position I'd start by waiting until you have an out-of-control backend, attaching to it without pausing it, and setting a breakpoint at my_sock_read. If the breakpoint is hit then something's called my_sock_read again; it won't trigger if my_sock_read is somewhere on the call stack, only when the current point of execution enters the function. You can step through execution from there see where it's looping. If you find that my_sock_read isn't being called repeatedly, then the infinite loop is in my_sock_read or something it's calling. Break into execution and step through to see what Pg is doing. Given the reliability of the server in the past, I'd probably be expecting an issue with OpenSSL instead, but with debugging attached I should be able to say for sure. Yep. If, for example, you waited until a backend was in the problem state where it was using 100% CPU, attached the debugger, and set a breakpoint at the start of my_sock_read in postgres.exe then you could see if my_sock_read(...) was being called repeatedly or just once. -- Craig Ringer -- 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] question about /etc/init.d/postgresql in PGDG
On Wed, 2009-08-19 at 00:01 -0600, Scott Marlowe wrote: > In the init script from the PGDG rpms there's this block of code: > > PGDATA=/var/lib/pgsql/data > > if [ -f "$PGDATA/PG_VERSION" ] && [ -d "$PGDATA/base/template1" ] > then > echo "Using old-style directory structure" > else > PGDATA=/var/lib/pgsql/data > fi > > Is it just me, or is the else extra noise? I haven't bothered to remove it (call me lazy). I will remove it in the upcoming sets. Thanks. Regards, -- Devrim GÜNDÜZ, RHCE Command Prompt - http://www.CommandPrompt.com devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org signature.asc Description: This is a digitally signed message part
Re: [GENERAL] pl/pgsql loop thru columns names
2009/8/19 Dilyan Berkovski :
> Hi All,
>
> I have a nasty table with many repeating columns of the kind
> port_ts_{i}_, where {i} is from 0 to 31, and could be
> 3 different words.
> I have made a pl/pgsql function that checks those columns from
> port_ts_1_status to port_ts_31_status and counts something, however this is
> nasty and ugly thing to do - repeat 31 times one statement for looping thru
> {i} and do it 3 times to loop thru . This is how I do it now:
> REATE OR REPLACE FUNCTION auto_util()
> RETURNS "trigger" AS
> $BODY$DECLARE
> count_free integer;
> util real;
> BEGIN
> count_free = 0;
> IF new.port_ts_1_status='free' THEN count_free = count_free + 1;
> End if;
> IF new.port_ts_2_status='free' THEN count_free = count_free + 1;
> End if;
> IF new.port_ts_3_status='free' THEN count_free = count_free + 1;
> End if;
> IF new.port_ts_4_status='free' THEN count_free = count_free + 1;
> End if;
> IF new.port_ts_5_status='free' THEN count_free = count_free + 1;
> End if;
> IF new.port_ts_6_status='free' THEN count_free = count_free + 1;
> End if;
> IF new.port_ts_7_status='free' THEN count_free = count_free + 1;
> End if;
> and till the end ilke this.
>
> Can I do a loop, and make the column naming to be build dynamically (as the
> dynamic queries in pl/pgsql)?
> something like
> for i in 1..31 do
> Loop
> If new.port_ts_[i]_status='free' ... and so on
> end Loop.
> I tryed but unsuccessfully :(, and can not find much in the documentation.
> I am using 8.1 and 8.2 Postgresql DBs.
> Someone with an idea?
yes - on this versions, you have to use dynamic pl languages as plperl
or pltcl. On 8.4 you can do it in plpgsql too.
http://okbob.blogspot.com/2008/06/execute-using-feature-in-postgresql-84.html
regards
Pavel Stehule
> Thanks
>
>
>
>
> --
> 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] Temp table or normal table for performance?
Let's say I have a function that needs to collect some data from various tables and process and sort them to be returned to the user. In general, would it be better to create a temporary table in that function, do the work and sorting there, and return it... or keep a permanent table for pretty much the same thing, but add a "user session" field and return the relevant rows from that and then delete them? Sorry this is vague, I know it most likely depends on the workload and such, but I'm just putting this together now. I could go either way, and also switch it up in the future if necessary. Is there a rule of thumb on this one? I'm a bit biased against temporary tables, but then again if the normal table gets a lot of action it might not be the optimal choice. Thanks and regards, Stephen Cook -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
