Re: [GENERAL] Join Tutorial
Why don't you download the user's tutorial (pdf format) from www.postgresql.org . It has quite comprehensive discussion on joins N Banerjee --- Sai Hertz And Control Systems <[EMAIL PROTECTED]> wrote: > Dear All, > > I was just looking for a nice in depth tutorial for > JOINS > would be greatefull if anyone of you pass me on > some links in said regards. > > > > Regards, > Vishal Kashyap > > ---(end of > broadcast)--- > TIP 7: don't forget to increase your free space map settings Yahoo! Messenger - Communicate instantly..."Ping" your friends today! Download Messenger Now http://uk.messenger.yahoo.com/download/index.html ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Mirrors that don't suck.
ftp://ftp.au.postgresql.org/pub/postgresql/ is still only displaying 7.4.0 right now... Marc G. Fournier wrote: Send the details to webmaster@ so that the guys can add it to the database ... but, how are you mirroring? All official mirrors use rsync, and they are auto-added to the list of mirrors, and updated nightly ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] "unicode" warnings
Sorry about the cross posting ... I am curently import data into rt 3 (3.0.7_01) and I have the following message repeated in the logs for each binary Attachments: + [warning]: DBD::Pg::st execute failed: ERROR: invalid byte sequence for encoding "UNICODE": 0xe0 at /usr/lib/perl5/site_perl/5.8.0/DBIx/SearchBuilder/Handle.pm line 410. + The attachment is subsequently not created. What could be the cause of this problem, and how can I solve it? () ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] PostgreeSQL C header files
> See the "Client Interfaces" and possibly the "Server Programming" > sections in the PostgreSQL manual: > http://www.postgresql.org/docs/current/static/client-interfaces.html > http://www.postgresql.org/docs/current/static/server-programming.html > The examples in the contrib directory of the source distribution might > also be helpful. Thank you very much. -- Linux is like a wigwam - no gates, no windows and an apache inside. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Installing Postgres w/RH9
I just installed RH9 on a new machine and installed postgres in the installation process. I got the following error when trying to start the postmaster so... I tried removing the postgres software then reinstalling. rpm first said the software was not installed then when I tried to reinstall rpm said it was already installed. How do I solve this problem? Thanks for your help! Lynn -bash-2.05b$ postmaster& [1] 3139 -bash-2.05b$ FATAL: File /var/lib/pgsql/data/PG_VERSION is missing. This is not a valid data directory. [1]+ Exit 1 postmaster -bash-2.05b$ [EMAIL PROTECTED] root]# cd /var/lib/pgsql [EMAIL PROTECTED] pgsql]# for i in `ls postgresql*.rpm`; do rpm -e $i; done error: package postgresql72-libs-1-3.i386.rpm is not installed error: package postgresql-7.3.2-3.i386.rpm is not installed error: package postgresql-contrib-7.3.2-3.i386.rpm is not installed error: package postgresql-devel-7.3.2-3.i386.rpm is not installed error: package postgresql-docs-7.3.2-3.i386.rpm is not installed error: package postgresql-jdbc-7.3.2-3.i386.rpm is not installed error: package postgresql-libs-7.3.2-3.i386.rpm is not installed error: package postgresql-odbc-7.2.5-1.i386.rpm is not installed error: package postgresql-pl-7.3.2-3.i386.rpm is not installed error: package postgresql-python-7.3.2-3.i386.rpm is not installed error: package postgresql-server-7.3.2-3.i386.rpm is not installed error: package postgresql-tcl-7.3.2-3.i386.rpm is not installed error: package postgresql-test-7.3.2-3.i386.rpm is not installed [EMAIL PROTECTED] pgsql]# for i in `ls postgresql*.rpm`; do rpm -i $i; done warning: postgresql72-libs-1-3.i386.rpm: V3 DSA signature: NOKEY, key ID db42a60e package postgresql72-libs-1-3 is already installed warning: postgresql-7.3.2-3.i386.rpm: V3 DSA signature: NOKEY, key ID db42a60e package postgresql-7.3.2-3 is already installed warning: postgresql-contrib-7.3.2-3.i386.rpm: V3 DSA signature: NOKEY, key ID db42a60e package postgresql-contrib-7.3.2-3 is already installed warning: postgresql-devel-7.3.2-3.i386.rpm: V3 DSA signature: NOKEY, key ID db42a60e package postgresql-devel-7.3.2-3 is already installed warning: postgresql-docs-7.3.2-3.i386.rpm: V3 DSA signature: NOKEY, key ID db42a60e package postgresql-docs-7.3.2-3 is already installed warning: postgresql-jdbc-7.3.2-3.i386.rpm: V3 DSA signature: NOKEY, key ID db42a60e package postgresql-jdbc-7.3.2-3 is already installed warning: postgresql-libs-7.3.2-3.i386.rpm: V3 DSA signature: NOKEY, key ID db42a60e package postgresql-libs-7.3.2-3 is already installed warning: postgresql-odbc-7.2.5-1.i386.rpm: V3 DSA signature: NOKEY, key ID db42a60e package postgresql-odbc-7.2.5-1 is already installed warning: postgresql-pl-7.3.2-3.i386.rpm: V3 DSA signature: NOKEY, key ID db42a60e package postgresql-pl-7.3.2-3 is already installed warning: postgresql-python-7.3.2-3.i386.rpm: V3 DSA signature: NOKEY, key ID db42a60e package postgresql-python-7.3.2-3 is already installed warning: postgresql-server-7.3.2-3.i386.rpm: V3 DSA signature: NOKEY, key ID db42a60e package postgresql-server-7.3.2-3 is already installed warning: postgresql-tcl-7.3.2-3.i386.rpm: V3 DSA signature: NOKEY, key ID db42a60e package postgresql-tcl-7.3.2-3 is already installed warning: postgresql-test-7.3.2-3.i386.rpm: V3 DSA signature: NOKEY, key ID db42a60e package postgresql-test-7.3.2-3 is already installed [EMAIL PROTECTED] pgsql]# ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [webmaster] [GENERAL] Mirrors that don't suck.
> -Original Message- > From: Marc G. Fournier [mailto:[EMAIL PROTECTED] > Sent: 23 December 2003 20:48 > To: Joshua D. Drake > Cc: Marc G. Fournier; Brian Hirt; Robert Treat; > [EMAIL PROTECTED]; [EMAIL PROTECTED]; Dan Vande More > Subject: Re: [webmaster] [GENERAL] Mirrors that don't suck. > > On Tue, 23 Dec 2003, Joshua D. Drake wrote: > > > I just tried : > > > > ftp6.us.postgresql.org > > > > Which doesn't even have PostgreSQL on it ;) It is the home > of opennms > > (at least to some degree). > > If it doesn't have PostgreSQL on it, why is it listed as one? :) Ftp6.us is not currently listed on the website because it hasn't synced up in about 12 months. Guessing the numbers is not a good idea because at any given time something like 50% of the mirrors are out of date and therefore automatically removed from the published list. Their DNS entries are not removed however as that would just cause all sorts of fun due to the propagation time/ttl value. In response to a earlier comment on the 'freshness' of the mirrors, the system tracks which mirrors sync up and when, and lists only those that have synced within the last 48 hours +/- a few hours for reasons I won't bore you all with. For those that are interested, there are over 225 mirrors currently in the system, all provided by volunteers. There will often be an error on at least one of them (or on the net between any given client and a mirror) and unfortunately we cannot easily detect such problems. We do remain grateful to those that donate their time and resources to provide a mirror though. Regards, Dave. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Triggers for FK on Views - can they be made deferrable?
Hi Jan, Thanks for the reply. I did come across the CREATE CONSTRAINT TRIGGER doc page after my posting. It's not too well documented and seems to want various parameters yet it's not clear what they'd be, so any pointers are appreciated. My other two approaches were: (a) Update the pg_trigger table, setting tgdeferrable and tginitdeferred accordingly (--hey, if you're gonna live on the wild side) (b) Force users to make changes via a stored proc/function that sort-of does what I want. (Not ideal, but stays on the "safe" side of everything.) EE "Jan Wieck" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > There is a way to create them as CONSTRAINT triggers. It's totally > non-standard, not guaranteed to exist in future releases, yadda, yadda. > But it get's you where you want to be now(). > > > Jan > > ezra epstein wrote: > > > I've got the case of a table which has unusual FK constraints. I'm > > implementing them as triggers against a view. It all works, BUT I don't see > > a way to make the triggers executed only on Commit -- i.e., I'd like the > > same "deferrable" behavior that true FKs provide. Does anyone know how to > > do this? > > > > Thanks, > > > > Ezra E. > > > > > > > > ---(end of broadcast)--- > > TIP 7: don't forget to increase your free space map settings > > > -- > #==# > # It's easier to get forgiveness for being wrong than for being right. # > # Let's break this rule - forgive me. # > #== [EMAIL PROTECTED] # > > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Mirrors that don't suck.
http://mirror.ehpg.net/postgresql/ doesn't suck ;-) But I'm partial. http://mirror.ehpg.net/postgresql/source/v7.4.1/ is a direct URL. Gavin Robert Treat wrote: On Tue, 2003-12-23 at 13:39, Dan Vande More wrote: Hey list, I'm just wondering if anyone can point me in the direction of a mirror that doesn't suck. I generally don't do alot with postgres other than downloading and installing the newest releases. Everytime there is a release, it takes forever for it to show up on an ftp server. This is in the case that the ftp server even works. Now in a case where one likes to browse with the browser of their choice, it totally blows because it's _so_ slow. So is there maybe an http download somewhere? Or an FTP site that doesn't give me an "directory doesn't exist" error, or one that has more than 15k available, or one that maybe has the newest releases(7.4.1)? All of those would be perfect... Dan So this is how not to write an email asking people for help, seriously. I just checked the first 5 mirror sites on the ftp mirrors page and everyone of them had the latest code under pub/source/v7.4.1/ (or something similar). What might have been a more helpful email would be to point out that the ftp site are still showing 7.4.0 in the main directory and the directory for latest is also pointing to 7.4.0, both should be updated to 7.4.1. You might have also included a list of the mirror sites you were having trouble with so problems with those sites could be tracked down easier. You might also want to CC [EMAIL PROTECTED] as well as posting to the -general list. Oh, and for future reference you can download via http at http://developer.postgresql.org/ftpsite/, and also at the sourceforge site http://sourceforge.net/projects/pgsql/ (though I hesitate to mention it because I haven't uploaded 7.4.1 there quite yet...) I hope this helps. Robert Treat ---(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] Tables Referencing themselves As Foreign Keys
This is a fine approach. The FK will work fine. You'll probably want CatID to be NOT NULL and CatParent to allow nulls. Having a Null parent indicating root is easier for traversals. Common other features to add include: a "path" column that is maintaned by insert/update triggers. Quite easy to do and very helpful. Once you have that you can do a simple test for circularity also on insert/update, like: IF "path" ~ '(^|\\.)' || "CatID"::text || '(\\.|$)' THEN RAISE EXCEPTION ''circular hierarchy detected...''; END IF; There's also a short-cut way to do this since you use Serial for the CatIDs. Just do a CHECK (CatParent < CatID) -- of course it makes an assumption about the CatIDs really come in serially... == Ezra Epstein ""Tony (Unihost)"" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Hi, > > I'm still new to this so if I'm sounding dumb or my premise is flawed > please forgive me. I have a DB design which contains a table which has > categories, each category has a parent category, and is recursed until > the top category is reached, in order to create breadcrumbs. Is there > any problem with using foreign keys to reference the same table? So a > when category is added the CatParent MUST be present as a CatID > > CatID - Serial > CatParent - int4 - References CatID > CatName - Text > > Am I likeley to come unstuck with this? > > Cheers > > T. > > > > > > > > ---(end of broadcast)--- > TIP 8: explain analyze is your friend > ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] hardware requirements under Redhat
> Use RAID 5 or 0+1...
I have had the Mirroring vs RAID 5 debate before.
You would go with RAID 5 to obtain the fault tolerance.
That was my first choice but I was told I was wrong.
kd
[EMAIL PROTECTED] ("Joshua D. Drake") wrote in message news:<[EMAIL PROTECTED]>...
> >
> >
> >I am considering a generic box with a single 2 - 2.6 Gig processor.
> >2 Gig of RAM and mirrored 200 Gig drives.
> >
> >
> >
> Use RAID 5 or 0+1...
>
>
>
> >Does this config cover the above requirements very well.
> >Does anybody know if RedHat 9 or Fedora can address 2 Gig
> >of RAM out of the box?
> >
> >
> >
> Yes they can.
>
>
>
> >many thanks
> >
> >kd
> >
> >---(end of broadcast)---
> >TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to [EMAIL PROTECTED] so that your
> > message can get through to the mailing list cleanly
> >
> >
>
>
> --
> Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
> Postgresql support, programming shared hosting and dedicated hosting.
> +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com
> Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL
>
>
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [webmaster] [GENERAL] Mirrors that don't suck.
> -Original Message- > From: Marc G. Fournier [mailto:[EMAIL PROTECTED] > Sent: 23 December 2003 21:09 > To: Oliver Elphick > Cc: Marc G. Fournier; Robert Treat; Dan Vande More; > [EMAIL PROTECTED]; [EMAIL PROTECTED] > Subject: Re: [webmaster] [GENERAL] Mirrors that don't suck. > > > It would be helpful if someone were to add to the links page the UK > > mirror http://www.mirror.ac.uk/sites/ftp.postgresql.org/ > > Send the details to webmaster@ so that the guys can add it to > the database ... but, how are you mirroring? All official > mirrors use rsync, and they are auto-added to the list of > mirrors, and updated nightly ... > It is listed - it's: ftp2.uk.postgresql.org /D ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] SELECT INTO broken (?) in PL/pgSQL when using a set returning function in FROM clause (BGUG?)
I'm been banging my head over this for a little while now.
Here's a simple function to return a record:
CREATE OR REPLACE FUNCTION create_item_record_for_override(INTEGER,
VARCHAR )
RETURNS RECORD AS '
DECLARE
recRECORD;
BEGIN
/* Normally we would not have a separate check here. We would use IF
NOT FOUND, but that appears to be broken. */
IF NOT EXISTS(SELECT 1 FROM merchandise.ds_item($1) WHERE "client_key" =
$2) THEN
RAISE EXCEPTION ''No base row for override. dsrc_id=%,
client_key=%"'', $1, $2;
END IF;
SELECT INTO rec * FROM merchandise.ds_item($1) WHERE "client_key" = %2;
IF NOT FOUND THEN
/* We should NEVER get here. The EXISTS check uses the same query
and so will raise an exception under the same conditions. IT APPEARS as
though SELECT INTO is not working when there is a function in the FROM
clause. */
RAISE EXCEPTION ''No base row for override. dsrc_id=%,
client_key=%"'', $1, $2;
END IF;
RETURN rec;
END;
' LANGUAGE plpgsql STABLE;
Basically passing in valid parameters, one's where the result of doing
SELECT * FROM merchandise.ds_item($1) WHERE "client_key" = %2;
on the psql command line work just fine, fail always in this function. All
I can conclude (after about 2 hours) is that we can not SELECT INTO, FROM a
set returning function in PL/pgSQL.
For the curious, here is the definition of the ds_item function.
CREATE OR REPLACE FUNCTION "merchandise".ds_item(INTEGER)
RETURNS setof "merchandise"."item" AS '
SELECT DISTINCT ON (client_key) * FROM "merchandise"."item"
WHERE "ovr_status" >= 0 AND "dsrc_id" IN (SELECT * FROM
"common".get_path_parts("merchandise".ds_get_id_path($1)))
ORDER BY client_key, dsrc_id DESC;
' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT;
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Parser does not like %ROWTYPE in the RETURNS clause of a function declaration (BUG?)
Aother head banger for me. Below is a complete example of the code Using Postgres 7.4, the function "test" gets this: psql:temp3.sql:10: ERROR: syntax error at or near "%" at character 135 the function "test2" gets this: psql:temp3.sql:10: ERROR: syntax error at or near "ROWTYPE" at character 141 Very odd. The first doesn't even like the '%' character -- perhaps because doof is a table type rather than a column (domain) type??? And when we schema qualify the name of the table then the % is ok, but ROWTYPE is not. Is this a well-known limitation or a new (7.4) bug? I tried combing the docs to no avail. Thanks, Ezra E. /* CREATE TABLE doof ( "pk_id" serial ) WITHOUT OIDS; */ CREATE OR REPLACE FUNCTION test(INTEGER) RETURNS doof%ROWTYPE AS ' SELECT * FROM doof WHERE pk_id=$1; ' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT; CREATE OR REPLACE FUNCTION test2(INTEGER) RETURNS public.doof%ROWTYPE AS ' SELECT * FROM doof WHERE pk_id=$1; ' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT; ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Parser does not like %ROWTYPE in the RETURNS clause
ezra epstein wrote: Aother head banger for me. Below is a complete example of the code Using Postgres 7.4, the function "test" gets this: psql:temp3.sql:10: ERROR: syntax error at or near "%" at character 135 the function "test2" gets this: psql:temp3.sql:10: ERROR: syntax error at or near "ROWTYPE" at character 141 Very odd. The first doesn't even like the '%' character -- perhaps because doof is a table type rather than a column (domain) type??? And when we schema qualify the name of the table then the % is ok, but ROWTYPE is not. Is this a well-known limitation or a new (7.4) bug? I tried combing the docs to no avail. Thanks, Ezra E. /* CREATE TABLE doof ( "pk_id" serial ) WITHOUT OIDS; */ CREATE OR REPLACE FUNCTION test(INTEGER) RETURNS doof%ROWTYPE AS ' SELECT * FROM doof WHERE pk_id=$1; ' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT; CREATE OR REPLACE FUNCTION test2(INTEGER) RETURNS public.doof%ROWTYPE AS ' SELECT * FROM doof WHERE pk_id=$1; ' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT; ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] Try replacing the rowtype with SETOF doof: CREATE OR REPLACE FUNCTION test(INTEGER) RETURNS SETOF doof AS ' SELECT * FROM doof WHERE pk_id=$1; ' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT; Hope that helps. Ron ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Parser does not like %ROWTYPE in the RETURNS clause of a
Dear ezra epstein ; Using Postgres 7.4, the function "test" gets this: psql:temp3.sql:10: ERROR: syntax error at or near "%" at character 135 the function "test2" gets this: psql:temp3.sql:10: ERROR: syntax error at or near "ROWTYPE" at character 141 Very odd. The first doesn't even like the '%' character -- perhaps because doof is a table type rather than a column (domain) type??? ROWTYPE for SQL Language you may please check that /* CREATE TABLE doof ( "pk_id" serial ) WITHOUT OIDS; */ CREATE OR REPLACE FUNCTION test(INTEGER) RETURNS doof%ROWTYPE AS ' SELECT * FROM doof WHERE pk_id=$1; ' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT; CREATE OR REPLACE FUNCTION test2(INTEGER) RETURNS public.doof%ROWTYPE AS ' SELECT * FROM doof WHERE pk_id=$1; ' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT; The above code gave error on mine system also PostgreSQL 7.3.4 what I think you want to something like this CREATE OR REPLACE FUNCTION test2(INTEGER) RETURNS public.doof AS ' SELECT * FROM doof WHERE pk_id = $1; ' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT; CREATE OR REPLACE FUNCTION test1(INTEGER) RETURNS doof AS ' SELECT * FROM doof WHERE pk_id = $1; ' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT; Mine Limited knowledge tells me that this is not a BUG but just an effect of thinking out of the box Shoot back if I was right please. Regards, Vishal Kashyap ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Firebird and PostgreSQL at the DB Corral.
On Sun, 2003-12-21 at 18:49, Paul Ganainm wrote:
> [EMAIL PROTECTED] says...
> > > What then is a derived table, or is a derived table just a synonym for
> > > inline view?
>
> > I'm not sure what the "official" name for this is. I have heard both. So
> > from my point of view a derived table and an inline view are the same.
>
> OK - I'm fine with the idea that there can be more than one name for the
> same thing, and that different geographies and/or cultures can mean that
> people use synonyms.
>
> I've recently started looking at Oracle as well - boy oh boy, you'd want
> to know your FTLA's with that system!
>
> > > What's wrong with SELECT COUNT(col1) FROM table?
>
> > Nothing. But my statement was just an example to show the syntax.
>
> > But sometimes when things get more complicated it *is* very handy, and I
> > have used it now and then, and wouldn't want to miss it :-)
>
> OK, fine. Can you show me an example of where your construct (inline
> view and/or derived table) behaves differently from and is better than
> the syntax that I used?
>
> Paul...
select distinct * FROM (
select ss.* from (
(
SELECT s.site_id, s.name, r.name AS region_name, e.active,
coalesce(max,0) AS status, match_type
FROM prod1 a, host h,
entity_profile_1 e, site s, region r,
(
SELECT 'Site name' AS match_type, site_id AS search_id FROM site
WHERE name ilike '%abc%'
UNION
SELECT 'prod1 license' AS match_type, findsite(prod1_id) AS
search_id FROM prod1 WHERE prod1_license ilike 'abc%'
UNION
SELECT 'prod2 license' AS match_type, findsite(prod2_id) AS
search_id FROM prod2 WHERE prod2_license ilike 'abc%'
UNION
SELECT 'prod3 license' AS match_type, findsite(prod3_id) AS
search_id FROM prod3 WHERE prod3_license ilike '%abc%'
) AS sr
LEFT JOIN
(
SELECT findsite(entity_id) AS error_id, max(status_id)
FROM current ce
GROUP BY findsite(entity_id)
) AS errors ON (error_id = search_id)
WHERE
search_id = s.site_id AND s.region_id = r.region_id AND search_id =
e.entity_id
AND a.host_id = h.host_id AND h.site_id = s.site_id
)
) AS ss ORDER BY UPPER(ss.name) ASC, region_name asc
) as matches;
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] hardware requirements under Redhat
kbd wrote:
Use RAID 5 or 0+1...
I have had the Mirroring vs RAID 5 debate before.
You would go with RAID 5 to obtain the fault tolerance.
That was my first choice but I was told I was wrong.
0+1 is RAID + STRIPE, it is (theoretically) faster than RAID 5 but
requires 4 disks where RAID 5 only requires 3.
RAID1 is ok but slower on writes.
My suggestion is go RAID 0+1 if you can, or RAID 5 with a spare.
Sincerley,
Joshua D. Drake
kd
[EMAIL PROTECTED] ("Joshua D. Drake") wrote in message news:<[EMAIL PROTECTED]>...
I am considering a generic box with a single 2 - 2.6 Gig processor.
2 Gig of RAM and mirrored 200 Gig drives.
Use RAID 5 or 0+1...
Does this config cover the above requirements very well.
Does anybody know if RedHat 9 or Fedora can address 2 Gig
of RAM out of the box?
Yes they can.
many thanks
kd
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] ecpg question
On Tue, Dec 23, 2003 at 02:06:14AM -0500, George Gensure wrote:
> EXEC SQL SELECT * FROM foo() AS TBL( c int, i int );
>
> or any other query with multiple columns to a TBL description causes a
> segfault in ecpg. It also concerns me that all other symbols are
I just fixed this. The reason was a usage of ',' instead of
make_str(",") in the cat_str call. Since the argument is free'd
afterwards it couldn't work.
Ecpg does not lowercase all symbols per default but only those that need
some special treatment like keywords. There's no logic to just lowercase
everything as this is not requiered IIRC.
Michael
--
Michael Meskes
Email: Michael at Fam-Meskes dot De
ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED]
Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [GENERAL] Installing Postgres w/RH9
On Tue, 23 Dec 2003 [EMAIL PROTECTED] wrote:
> I just installed RH9 on a new machine and installed postgres in the
> installation process. I got the following error when trying to
> start the postmaster so... I tried removing the postgres software
> then reinstalling. rpm first said the software was not installed
> then when I tried to reinstall rpm said it was already installed.
I've seen this happen but never been able to figure out exactly what
caused it.
> How do I solve this problem?
First navigate to /var/lib/rpm and remove the three files that begin with
double underscores typically named __db.001, __db.002, and __db.003
When trying to remove packages I use the rpm -qa | grep xxx to determine
the actual package names. If, like the case of PostgreSQL, they are all
lumped together with a naming that is common to all the packages I use
this
rpm -e $(rpm -qa | grep postgres)
and nuke them all.
Also make sure that you have stopped the postmaster (PostgreSQL) before
trying to remove the packages. /etc/init.d/postgresql stop
Good luck to you.
Rod
--
"Open Source Software - You usually get more than you pay for..."
"Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL"
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] hardware requirements under Redhat
> > Use RAID 5 or 0+1... > I have had the Mirroring vs RAID 5 debate before. > You would go with RAID 5 to obtain the fault tolerance. > > That was my first choice but I was told I was wrong. I doubt there is a general rule as to which is better, it will depend upon the individual circumstances (including budget). Were you told why that was the wrong choice? -- Mike Nolan ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [webmaster] [GENERAL] Mirrors that don't suck.
Dave, I can go about setting up a web mirror, but i cannot offer a download mirror if we have to use ftp. Our security policies are not to allow ftp access. It's just one more thing to worry about if we open up our firewall. I hope you understand. Will it be a problem only offer the web mirror? We have a 10mbit/sec connection, but probably wouldn't want postgresql to take up more than 1mbit/sec on average. we also have several free gigabytes of disk. Best Regards, Brian Hirt On Dec 24, 2003, at 4:46 PM, Dave Page wrote: It's rumoured that Brian Hirt once said: What type bandwidth can you expect to use if you become a mirror (1meg/sec? more less?) I honestly couldn't say, but I would suspect it would be a *lot* less than that. Whilst PostgreSQL is popular, the type of application that it is means that it will never get anything remotely like the level of downloads of say, Mozilla or OpenOffice. Also are http mirrors acceptable? Currently only to mirror the web content (this is mainly because the mirror code expects an ftp mirror to use ftp and a web mirror to use http - changing this would require a fair bit of recoding). I would like to offer up one of our servers on mobygames.com because I love postgresql and it would be a way to help out a bit, but I don't really know enough about the requirements right now. Who should i talk to? Me please. I'll need your server IP, and the IP of whatever boxes will be used to rsync the content. For web mirrors you must be able to setup a virtual host, for ftp, I alsoneed to know the path to the content (eg. /pub/postgresql/) However, there are already 7 US mirrors. There's closer to 20, but most are not active :-( Regards, Dave. ---(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] hardware requirements under Redhat
I doubt there is a general rule as to which is better, it will depend upon the individual circumstances (including budget). It is my experience that 0+1 is a bit faster that RAID 5. Sincerely, Joshua D. Drake Were you told why that was the wrong choice? -- Mike Nolan ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC - S/JDBC Postgresql support, programming, shared hosting and dedicated hosting. +1-503-222-2783 - [EMAIL PROTECTED] - http://www.commandprompt.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [webmaster] [GENERAL] Mirrors that don't suck.
It's rumoured that Brian Hirt once said: > What type bandwidth can you > expect to use if you become a mirror (1meg/sec? more less?) I honestly couldn't say, but I would suspect it would be a *lot* less than that. Whilst PostgreSQL is popular, the type of application that it is means that it will never get anything remotely like the level of downloads of say, Mozilla or OpenOffice. > Also are > http mirrors acceptable? Currently only to mirror the web content (this is mainly because the mirror code expects an ftp mirror to use ftp and a web mirror to use http - changing this would require a fair bit of recoding). > I would like to offer up one of our servers > on mobygames.com because I love postgresql and it would be a way to > help out a bit, but I don't really know enough about the requirements > right now. > > Who should i talk to? Me please. I'll need your server IP, and the IP of whatever boxes will be used to rsync the content. For web mirrors you must be able to setup a virtual host, for ftp, I alsoneed to know the path to the content (eg. /pub/postgresql/) > However, there are already 7 US mirrors. There's closer to 20, but most are not active :-( Regards, Dave. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Postgresql 7.4.1 Compile problem for SCO 5.0.5
I am trying to compile postgresql 7.4.1 for sco 5.0.5 and get the following error: "thread.c", line 269: error: undefined symbol: h_errno Any ideas? ./configure --with-libs=/usr/local/lib --with-includes=/usr/local/include herbie!/tmp/postgresql-7.4.1 # gmake gmake -C doc all gmake[1]: Entering directory `/tmp/postgresql-7.4.1/doc' gmake[1]: Nothing to be done for `all'. gmake[1]: Leaving directory `/tmp/postgresql-7.4.1/doc' gmake -C src all gmake[1]: Entering directory `/tmp/postgresql-7.4.1/src' gmake -C port all gmake[2]: Entering directory `/tmp/postgresql-7.4.1/src/port' cc -b elf -O -I../../src/include -I/usr/local/include -c thread.c "thread.c", line 269: error: undefined symbol: h_errno gmake[2]: *** [thread.o] Error 1 gmake[2]: Leaving directory `/tmp/postgresql-7.4.1/src/port' gmake[1]: *** [all] Error 2 gmake[1]: Leaving directory `/tmp/postgresql-7.4.1/src' gmake: *** [all] Error 2 herbie!/tmp/postgresql-7.4.1 # ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] SELECT INTO broken (?) in PL/pgSQL when using a set returning function in FROM clause (BGUG?)
Up too late. This works fine. At least it works for some simpler test
functions under the just-downloaded 7.4.1 release.
-- Ezra Epstein
"ezra epstein" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> I'm been banging my head over this for a little while now.
>
> Here's a simple function to return a record:
>
>
> CREATE OR REPLACE FUNCTION create_item_record_for_override(INTEGER,
> VARCHAR )
> RETURNS RECORD AS '
> DECLARE
> recRECORD;
> BEGIN
> /* Normally we would not have a separate check here. We would use IF
> NOT FOUND, but that appears to be broken. */
> IF NOT EXISTS(SELECT 1 FROM merchandise.ds_item($1) WHERE "client_key"
=
> $2) THEN
> RAISE EXCEPTION ''No base row for override. dsrc_id=%,
> client_key=%"'', $1, $2;
> END IF;
>
> SELECT INTO rec * FROM merchandise.ds_item($1) WHERE "client_key" =
%2;
> IF NOT FOUND THEN
> /* We should NEVER get here. The EXISTS check uses the same query
> and so will raise an exception under the same conditions. IT APPEARS as
> though SELECT INTO is not working when there is a function in the FROM
> clause. */
> RAISE EXCEPTION ''No base row for override. dsrc_id=%,
> client_key=%"'', $1, $2;
> END IF;
>
> RETURN rec;
> END;
> ' LANGUAGE plpgsql STABLE;
>
>
> Basically passing in valid parameters, one's where the result of doing
> SELECT * FROM merchandise.ds_item($1) WHERE "client_key" = %2;
> on the psql command line work just fine, fail always in this function.
All
> I can conclude (after about 2 hours) is that we can not SELECT INTO, FROM
a
> set returning function in PL/pgSQL.
>
> For the curious, here is the definition of the ds_item function.
>
>
> CREATE OR REPLACE FUNCTION "merchandise".ds_item(INTEGER)
> RETURNS setof "merchandise"."item" AS '
> SELECT DISTINCT ON (client_key) * FROM "merchandise"."item"
> WHERE "ovr_status" >= 0 AND "dsrc_id" IN (SELECT * FROM
> "common".get_path_parts("merchandise".ds_get_id_path($1)))
> ORDER BY client_key, dsrc_id DESC;
> ' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT;
>
>
>
>
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
