Re: [GENERAL] Subquery for column names of tablefunc crosstab queries
Thanks Joe
I'm very much learning as I go.
I've followed your example from top to bottom - your sample code works -
however its not clear to me how to execute the generate_crosstab_sql
function to produce the results in one single step.
I've tried this:
CREATE OR REPLACE VIEW mycrosstabresults AS select * FROM (SELECT
generate_crosstab_sql('mytable',
'rowid',
'text',
'rowdt::date',
'temperature',
'int',
'1 = 1')) as crosstabresults;
but this simply populates the view with the string below, not the actual
crosstab results.
SELECT * FROM crosstab ('SELECT rowid,rowdt::date,temperature FROM
mytable WHERE 1 = 1 ORDER BY
1,2','VALUES($v$2003-03-01$v$),($v$2003-03-02$v$),($v$2003-03-03$v$)')
AS (rowid
text,2003-03-01 int,2003-03-02 int,2003-03-03 int)
Is it possible to incorporate and execute this string, all within my CREATE
OR REPLACE VIEW statement?
I would appreciate any help you can give.
Many thanks
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Subquery-for-column-names-of-tablefunc-crosstab-queries-tp3351437p3374199.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] OSDL DBT-2 for PostgreSQL
Hi everyone, I've just got our DBT-2 workload (TPC-C derivate) working with PostgreSQL using C stored functions and libpq. I'd love to get some feedback. v0.10 is available on SourceForge at: http://prdownloads.sourceforge.net/osdldbt/dbt2-v0.10.tar.gz?download We keep the source in BitKeeper at: bk://developer.osdl.org/dbt2 For anyone interested in more discussion on our workloads, we have a mailing list setup at: [EMAIL PROTECTED] -- Mark Wong - - [EMAIL PROTECTED] Open Source Development Lab Inc - A non-profit corporation 12725 SW Millikan Way - Suite 400 - Beaverton, OR 97005 (503)-626-2455 x 32 (office) (503)-626-2436 (fax) http://www.osdl.org/archive/markw/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] how good is PostgreSQL
Arnold Gamboa wrote: Hi, For users of large PostgreSQL and PostgreSQL builders, this is for you. I'm having a terrible time deciding now. :( We're about to build a "huge" website now. I got tied up in signing the contract without really getting enough information about PgSQL since this what we plan to implement with PHP (normally we use mySQL but i guess it does not fit for huge databases like that). Here's my problem.. We're about to build a site like hitbox.com where there is a large amount of database required.. If say there is 100,000 users with 1000 page hits per day for each, and everything will be logged, you could imagine how huge this will be. I'm just so "nervous" (really, that's the term) if we implement this and later on experience a slow down or worse than that, crash in the server. That is a LOT of work for any system. That is over 1100 page views a second, or under 900us each.. A standard Pentium III system, serving static pages would have problems with that. If you look at search engines, to get that performance with readonly data, they usually cluster multiple systems and load balance across them. You may need to segment your data and have multiple SQL servers perform different functions. Also, that 1100 page view per second is assuming an even distribution of traffic, which does not happen in a web server. If you average that much, chances are there will be periods of twice that. Look into a "local director," "Alteon," or even LVS. My questions are: 1. What is the limit for number of records in a table WITHOUT SUFFERING SLOW DOWN. 2. limit in number of tables per database 3. ... limit in number of database. There are a couple factors involved, more complex than a simple response. Use multiple databases and put each on a separate disk, with its own controller. Better yet, have multiple load balanced web boxes do a lot of processing in PHP and offload much of the CPU bound SQL work to the "cheap" web boxes, and have multiple SQL databases in the back handling various independent tasks. In a web site I worked on, we had multiple front end web servers, load balanced with an Alteon. Each web server had its own SQL database which provided SQL access to "static" data which was updated each week. We had an additional single SQL database backend which all the Web servers accessed for synchronized dynamic data. If you are serious about the load you expect to put on this system you must be careful: Do not create any indexes you do not need. Do not use the "foreign key" constraint as it forces a trigger for each insert. Make sure you index the keys by which you will access data. Avoid searching by strings, try to use keys. Even after that, you have a long way to go before you will hit 1000 transactions per second from any SQL database. If you are betting your business on this implementation, you have a lot of homework to do. Thanks for you comments. I would really appreciate every comment that I'll receive regarding this. Arnold
Re: [GENERAL] True ACID under linux (no fsync)?
"Gary Howland (During daytime)" wrote: Hi, Just a quickie - I heard that linux does not have a working fsync() call (it has no concept of raw devices). In other words, fsync cannot be used to ensure that data is on disk (try it with a floppy - it returns IMMEDIATELY!!! - long before the write finishes) Are you sure? Where is this information? This is for ALL linuxes. Does this mean that postgres is unrealiable (or non ACID-conforming) under linux? Gary -- Friends help you move. Real friends help you move bodies. ___ Free Unlimited Internet Access! Try it now! http://www.zdnet.com/downloads/altavista/index.html ___
Re: [GENERAL] True ACID under linux (no fsync)?
shawn everett wrote: I may be horriblly out of my element here but, I'm going to jump in anyway :) If you mount a floppy and copy a large file to it, you will get a prompt back fairly quickly. If you type: sync right after The sync command writes everything to the floppy as expected. Yes, but does cp call fsync()? That is the question. I would suspect that it does not. A non-functional fsync call is a serious error, and one should think more people would mention it. This is the first I have heard. Shawn On Tue, 31 Oct 2000, markw wrote: "Gary Howland (During daytime)" wrote: Hi, Just a quickie - I heard that linux does not have a working fsync() call (it has no concept of raw devices). In other words, fsync cannot be used to ensure that data is on disk (try it with a floppy - it returns IMMEDIATELY!!! - long before the write finishes) Are you sure? Where is this information? This is for ALL linuxes. Does this mean that postgres is unrealiable (or non ACID-conforming) under linux? Gary -- Friends help you move. Real friends help you move bodies. ___ Free Unlimited Internet Access! Try it now! http://www.zdnet.com/downloads/altavista/index.html ___
