Re: [GENERAL] Significado logo PostgreSQL
On Fri, Dec 10, 2004 at 04:02:42PM -0500, Mirko Coz wrote: > ¿Qué significado tiene el logo de PostgreSQL? Questions in Spanish should probably go to pgsql-es-ayuda. An elephant is "Big, strong, reliable and never forgets...": http://archives.postgresql.org/pgsql-general/1998-07/msg00306.php Here's an earlier suggestion to use an elephant because "elephants can remember...": http://archives.postgresql.org/pgsql-hackers/1997-04/msg00094.php -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Natural ordering in postgresql? Does it exist?
"Clark Endrizzi" <[EMAIL PROTECTED]> writes: > Hi all, > I have a field that I'll be ordering and I noticed that ordering is > done logically and would confuse my users here (1,12,16,4,8, etc). Sounds like you're storing a number in a text field. Numeric fields sort in numerical order. -Doug ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Natural ordering in postgresql? Does it exist?
Numbers as text are ordered like that. Integers are ordered as you would like. The best hack I have seen if you are stuck with text is ...order by length(numbers_as_test), numbers_as_text which sorts first by number of "digits" then by text order. Ian Harding Programmer/Analyst II Tacoma-Pierce County Health Department [EMAIL PROTECTED] Phone: (253) 798-3549 Pager: (253) 754-0002 >>> "Clark Endrizzi" <[EMAIL PROTECTED]> 12/10/04 1:47 PM >>> Hi all, I have a field that I'll be ordering and I noticed that ordering is done logically and would confuse my users here (1,12,16,4,8, etc). I'm writing an application in PHP that connects to Postgres and while I know that PHP has some powerful natural ordering functions it would be much easier if I could just use something from postgres directly. Does there exist any way to order naturally? Thanks, Clark Endrizzi _ Is your PC infected? Get a FREE online computer virus scan from McAfee® Security. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] No mailing list posts
Don Isgitt wrote: Hello, I have received no posts from GENERAL since yesterday morning; is the list broken? Thank you. Don ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) Not anymore. : ) Paul ---(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] Performance tuning on RedHat Enterprise Linux 3
Does postgres actually do multiple concurrent sorts within a single backend? Certainly. Consider for example a merge join with each input being sorted by an explicit sort step. DISTINCT, ORDER BY, UNION, and related operators require their own sort steps in the current implementation. It's not difficult to invent queries that require arbitrarily large numbers of sort steps. Tom, in Bruce's document on performance tuning, the page titled "Multiple CPUs" states: "POSTGRESQL uses a multi-process model, meaning each database connection has its own Unix process...POSTGRESQL does not use multi-threading to allow a single process to use multiple CPUs." I took this to mean that PostgreSQL was not multi-threaded at all, and that each connection was serviced by a single, non-threaded process. Have I interpreted this incorrectly? Are you saying that the backend process actually is multi-threaded? In the example you site, multiple sorts could be accomplished serially in a non-threaded process. Guy, You understand correctly. Each process is only running one query at once, but in terms of memory usage, several sorts are executing in parallel. For example, a merge join requires that both the left and right tables be sorted; as the join is being executed, both the left and right tables are being sorted. (Why doesn't it sort one and then the other? It would be a waste of memory to require that one of the [sorted] tables be kept in memory or written completely to the disk and then fetched later. Instead, it just sorts them both as it goes along.) However, this does mean that the amount of per-process memory being used for sorting will not vary with the "workload" of the database or number of people running that query (as each process only runs the query once). The amount of per-process memory used will vary with the complexity of the query and the plan chosen by the planner. Paul Tillotson ---(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] Natural ordering in postgresql? Does it exist?
On Fri, Dec 10, 2004 at 02:47:53PM -0700, Clark Endrizzi wrote: > I have a field that I'll be ordering and I noticed that ordering is done > logically and would confuse my users here (1,12,16,4,8, etc). I think you mean that the ordering is done lexically and you want it done numerically. If the fields are entirely numeric then storing them using one of the numeric types (INTEGER, NUMERIC, DOUBLE PRECISION, etc.) will result in numeric sort orders. If you have all-numeric values in VARCHAR/TEXT fields, then you can cast them to one of the numeric types in the ORDER BY clause: SELECT ... ORDER BY fieldname::INTEGER; If the values are a mixture of text and numbers (e.g., ABC-1, ABC-12, etc.) then you could use string functions to order different parts of the field differently: SELECT ... ORDER BY SUBSTRING(fieldname, 1, 3), SUBSTRING(fieldname, 5)::INTEGER; SELECT ... ORDER BY SUBSTRING(fieldname FROM '^(\\w+)'), SUBSTRING(fieldname FROM '(\\d+)')::INTEGER; -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] How to get rid of notices for create table?
On Fri, Dec 10, 2004 at 06:31:15PM -0300, Pablo Santiago Blum de Aguiar wrote: > I'm getting boring notices when creating tables: [snip] > NOTICE: CREATE TABLE / PRIMARY KEY will create > implicit index "pk_cliente" for table "cliente" > NOTICE: CREATE TABLE / UNIQUE will create implicit > index "uk_email" for table "cliente" > CREATE TABLE > > Got a few questions: > > 1 - What those 2 notice messages mean? They're informational messages. PostgreSQL is telling you that it's creating indexes that you didn't explicitly request. > 2 - How can I get rid of them? See the "Error Reporting and Logging" section of the "Server Run-time Environment" chapter in the documentation. You can configure what message levels go to the server logs and to the client. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] information schema extra fields
On Dec 9, 2004, at 12:46 PM, Ron Peterson wrote: I was just reading the PostgreSQL documentation about Information Schemas. Some of the tables have fields which are described as "Applies to a feature not available in PostgreSQL" The information schema is a part of the SQL standard that allows access to information about the structure of the database schema. PostgreSQL strives to implement the SQL standard, but there are some features of the SQL standard that PostgreSQL has not yet implemented or has decided not to. You can check SQL conformance in the documentation. Michael Glaesemann grzm myrealbox com ---(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] ERROR: relation "table" does not exist - HELP
when I run select * from "table" I get this error. ERROR: relation "table" does not exist Itamar Reis Peixoto+55 34 3238 3845+55 38 9107 1250http://www.ispbrasil.com.br No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.296 / Virus Database: 265.5.0 - Release Date: 9/12/2004 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] any body able to access news.postgresql.org recently?
Fixed On Wed, 8 Dec 2004, Sehat Rosny wrote: Hi, I am able to access this news.postgresql.org group two days ago. Now I am not able to access. Is it the news group is down? Thanks, Rosny ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(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] Loading data from tab delimited file using COPY
On Wed, Dec 08, 2004 at 09:17:12PM -0700, Jason Sheets wrote: > Sample Data is available at http://www.idahoimageworks.com/sampledata.txt > > I'm using the command: COPY residential FROM 'file' WITH DELIMITER AS '\t'; > > I'm receiving the error: ERROR: missing data for column "builder" > > When opened with excel as a tab delimited file all the fields are > separated correctly, I know this e-mail is pretty long but I'm stumped. Your sample data has 85 fields but the table has 87 fields, so COPY complains. How many fields does Excel say the data has? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Spanning tables
On Thu, Dec 09, 2004 at 01:07:43PM +0800, JM wrote: > Im wondering sooner or later my disk will be filled-up by postgres's > data.. > > Can anyone give some suggestion on how to deal with this. In oracle you can > just assign tables on a diff partition. In PostgreSQL 8.0 you'll be able to use tablespaces. For earlier versions, see the "Alternative Locations" section of the "Managing Databases" chapter in the documentation, as well as the documentation for "initlocation" under "PostgreSQL Server Applications" and CREATE DATABASE under "SQL Commands." -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] gborg.postgresql.org
On Wed, 8 Dec 2004, Tatsuo Ishii wrote: It seems gborg does not accept new projects any more. Does anybody know what happens with it? Also it's really slow and sometimes does not respond. I need an open source development web site which can safely hosts pgpool. Recently I'm getting lot of mails regarding pgpool and need mails lists, archives and hopefully searching capabilty of it. You want to look at http://www.pgfoundry.org, where we are planning on migrating gborg projects over to ... Also, Josh/Sean are working on a new server that we are goin to be moving both over to over the next week or so ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] postgresql and javascript
You might want to look at this : https://bugzilla.mozilla.org/show_bug.cgi?id=81653 This project is a native Postgresql connection in XUL (an XML based language that describes the Widgets in Firefox). I have not had time to play with this but it looks like the Firefox client could connect natively to a Postgres DB and expose it's widgets to provide a rich DB interface, with the scripting being done in JScript. This could in essence turn Firefox into an access like client to a Postgres DB (at least in so far as the forms go). Seems very promising , but it does not look like development is very active at the moment. Cheers Johan Wehtje Geoffrey wrote: Chris Smith wrote: [EMAIL PROTECTED] wrote: Does anyone know how to connect javascript to a postgresql database You can't connect javascript to any sort of database. Actually you can, with server side javascript, although I don't know if it supports postgresql. It does support odbc. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Mailing List Problems?
Fixed On Fri, 10 Dec 2004 [EMAIL PROTECTED] wrote: I've not seen a post in days on many of the lists to which I subscribe. --Rick ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Scheduler in Postgres
Thank you Ben, I already have known these tools. I was wonder if Postgres has an integrated tool for scheduling sp's (like Oracle has!). OK it doesn't have. No problem just tried to make world more simpler... :) Bye, -- Csaba -Original Message- From: Ben [mailto:[EMAIL PROTECTED] Sent: Friday, December 10, 2004 11:29 PM To: Együd Csaba Cc: '[EMAIL PROTECTED]' Subject: Re: [GENERAL] Sheduler in Postgres cron works pretty well in unix. Scheduled tasks on windows have, in my experience, been on the flakey side. On Tue, 7 Dec 2004, [iso-8859-2] Együd Csaba wrote: > Hi, > I should schedule the execution of several stored procedures. Now I > use an NT service for this, but as far as I know e.g. the Oracle has such a thing. > It would be great if I could fire procedures on a timer basis. > > Is there a better solution for this than mine? > > Many thanks, > > -- Csaba > > > --- > Outgoing mail is certified Virus Free. > Checked by AVG anti-virus system (http://www.grisoft.com). > Version: 6.0.805 / Virus Database: 547 - Release Date: 2004.12.03. > > > > ---(end of > broadcast)--- > TIP 7: don't forget to increase your free space map settings > --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.805 / Virus Database: 547 - Release Date: 2004.12.03. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.805 / Virus Database: 547 - Release Date: 2004.12.03. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Sheduler in Postgres
OK, thank you. Yes we have a more native tool - the "at" service on NT/2K.
I wanted to boundle it into the application because I don't like to place
parts of the application outside the application. I wrote a scheduler
service sepcialy for the applications tasks. It works fine, but I thought
this should be inside the server.
Thak you,
-- Csaba
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Christopher Browne
Sent: Saturday, December 11, 2004 5:09 AM
To: [EMAIL PROTECTED]
Subject: Re: [GENERAL] Sheduler in Postgres
The world rejoiced as [EMAIL PROTECTED] (Együd Csaba) wrote:
> Hi, I should schedule the execution of several stored procedures. Now
> I use an NT service for this, but as far as I know e.g. the Oracle has
> such a thing. It would be great if I could fire procedures on a timer
> basis.
>
> Is there a better solution for this than mine?
Traditionally, PostgreSQL has consciously omitted such things where they
would merely be replicating existing operating system functionality.
On Unix, "cron" is the traditional service that provides this functionality.
I think there's a port to Windows NT, so you could presumably use that if
you haven't got any more "native" job scheduler.
--
output = reverse("moc.liamg" "@" "enworbbc")
http://www.ntlug.org/~cbbrowne/lsf.html
"How should I know if it works? That's what beta testers are for. I
only coded it." (Attributed to Linus Torvalds, somewhere in a
posting)
---(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
---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.805 / Virus Database: 547 - Release Date: 2004.12.03.
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.805 / Virus Database: 547 - Release Date: 2004.12.03.
---(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] postgresql and javascript
Geoffrey wrote: Chris Smith wrote: [EMAIL PROTECTED] wrote: Does anyone know how to connect javascript to a postgresql database You can't connect javascript to any sort of database. Actually you can, with server side javascript, although I don't know if it supports postgresql. It does support odbc. Javascript is simply designed for lightweight scripting against a pre-existing object set provided by the host program. The quick answer is that you will need to look for documentation in the program you are using. I think that Mozilla used to have an optional add-in for PostgreSQL support, but it doesn't look like it has been maintained in a while. Best Wishes, Chris Travers Metatron Technology Consulting ---(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] information schema extra fields
On Sat, Dec 11, 2004 at 11:58:31AM +0900, Michael Glaesemann wrote: > On Dec 9, 2004, at 12:46 PM, Ron Peterson wrote: > > >I was just reading the PostgreSQL documentation about Information > >Schemas. Some of the tables have fields which are described as > > > >"Applies to a feature not available in PostgreSQL" > > The information schema is a part of the SQL standard that allows access > to information about the structure of the database schema. PostgreSQL > strives to implement the SQL standard, but there are some features of > the SQL standard that PostgreSQL has not yet implemented or has decided > not to. You can check SQL conformance in the documentation. Ah, thanks. I didn't realize this was part of the SQL standard. -- Ron Peterson Network & Systems Manager Mount Holyoke College http://www.mtholyoke.edu/~rpeterso ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] trouble with on insert rule via libpg-perl
I have a simple table, a view, and an on insert rule. These work fine,
ordinarily. But when I attempt to to insert a value into thesis_ps_v
via libpq_sql, nothing happens. If I capture the sql string I'm sending
(as per code sample below), print it to a file, and paste it into a psql
session, it works fine. If I use the same perl code (with the minor
required modifications to the sql string) to insert rows directly into
the the table, that works fine also. I've included the relevant tables
and perl code below.
##
CREATE TABLE thesis (
thesis_sha1
BYTEA
NOT NULL,
ps
BYTEA
NOT NULL,
id
INTEGER
DEFAULT nextval( 'thesis_id_seq' )
PRIMARY KEY
);
CREATE UNIQUE INDEX
thesis__sha1_ndx
ON
thesis( thesis_sha1 );
CREATE VIEW
thesis_ps_v
AS
SELECT
ps
FROM
thesis;
CREATE RULE thesis_ps_v_insert AS
ON INSERT TO thesis_ps_v
DO INSTEAD
INSERT INTO thesis ( thesis_sha1, ps )
VALUES ( digest( new.ps, 'sha1' ), new.ps );
##
sub upload_val ($) {
my( $octstr_ref ) = @_;
my $sqlstr = exec( $sqlstr );
my $status = $result->resultStatus;
my $oid = $result->cmdTuples;
if( $CONN->errorMessage &&
! ( $oid eq "" || $status eq PGRES_COMMAND_OK ) ) {
print STDERR sprintf( 'SQL exec failed: %s',
$CONN->errorMessage ), "\n";
}
}
--
Ron Peterson
Network & Systems Manager
Mount Holyoke College
http://www.mtholyoke.edu/~rpeterso
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] postgresql and javascript
There is this from Mozilla: http://www.mozilla.org/projects/sql/ On Tue, 7 Dec 2004 10:27:05 -0500, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > Does anyone know how to connect javascript to a postgresql database > > Please send example if anyone has done it > -- Mike Rylander [EMAIL PROTECTED] GPLS -- PINES Development Database Developer http://open-ils.org ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] trouble with on insert rule via libpg-perl
On Fri, Dec 10, 2004 at 11:26:09PM -0500, rpeterso wrote: I should have mentioned... PostgreSQL versions 8.0.0beta5 and 7.4.6 libpq-perl 2.0.2 perl 5.8.4 -- Ron Peterson Network & Systems Manager Mount Holyoke College http://www.mtholyoke.edu/~rpeterso ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Natural ordering in postgresql? Does it exist?
Quoth [EMAIL PROTECTED] ("Clark Endrizzi"):
> I have a field that I'll be ordering and I noticed that ordering is
> done logically and would confuse my users here (1,12,16,4,8, etc).
>
> I'm writing an application in PHP that connects to Postgres and while
> I know that PHP has some powerful natural ordering functions it would
> be much easier if I could just use something from postgres directly.
> Does there exist any way to order naturally?
If you wish to impose an ordering on an SQL query, you must specify
that ordering using an "ORDER BY" clause.
That's not a PostgreSQL issue; that's how SQL works.
--
output = ("cbbrowne" "@" "gmail.com")
http://linuxfinances.info/info/linux.html
Rules of the Evil Overlord #204. "I will hire an entire squad of blind
guards. Not only is this in keeping with my status as an equal
opportunity employer, but it will come in handy when the hero becomes
invisible or douses my only light source."
Re: [GENERAL] Postgres 8 and Slony-1
Centuries ago, Nostradamus foresaw when [EMAIL PROTECTED] (Simon Windsor) would write: > Any news when Postgres 8 will be released? If things go well, there's a release candidate hoped to be suitable for a Dec 15th date. But releases will _actually_ be ready when they are ready. > Also, are there any plans to include Slony with it, as part of the > same source download, or as part of a group 'matched' source > files/rpms/dpkgs etc? That's not the PG Development Group's responsibility; if makers of Linux distributions choose to do so, that's _their_ problem. It looks as though that will be somewhat hard to do until Slony-I 1.1 gets released, as the 1.0 series isn't readily compilable without having a whole PG source tree around. In 1.1, there are more sophisticated source configuration options that make that particular inconvenience unnecessary. -- let name="cbbrowne" and tld="gmail.com" in name ^ "@" ^ tld;; http://www.ntlug.org/~cbbrowne/linux.html "They're deleting the word `gullible' from modern dictionaries, presumably because it's too difficult a concept for the modern man or woman to understand." ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] No mailing list posts
Hello, I have received no posts from GENERAL since yesterday morning; is the list broken? Thank you. Don ---(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] any body able to access news.postgresql.org recently?
A long time ago, in a galaxy far, far away, [EMAIL PROTECTED] ("Sehat Rosny")
wrote:
> I am able to access this news.postgresql.org group two days ago. Now
> I am not able to access. Is it the news group is down?
There have been some difficulties with servers; it appears as though
most if not all of it has been resolved.
--
let name="cbbrowne" and tld="gmail.com" in name ^ "@" ^ tld;;
http://www.ntlug.org/~cbbrowne/postgresql.html
"I'm not switching from slrn. I'm quite confident that anything that
*needs* to be posted in HTML is fatuous garbage not worth my time."
-- David M. Cook <[EMAIL PROTECTED]>
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Sheduler in Postgres
The world rejoiced as [EMAIL PROTECTED] (Együd Csaba) wrote:
> Hi, I should schedule the execution of several stored
> procedures. Now I use an NT service for this, but as far as I know
> e.g. the Oracle has such a thing. It would be great if I could fire
> procedures on a timer basis.
>
> Is there a better solution for this than mine?
Traditionally, PostgreSQL has consciously omitted such things where
they would merely be replicating existing operating system
functionality.
On Unix, "cron" is the traditional service that provides this
functionality.
I think there's a port to Windows NT, so you could presumably use that
if you haven't got any more "native" job scheduler.
--
output = reverse("moc.liamg" "@" "enworbbc")
http://www.ntlug.org/~cbbrowne/lsf.html
"How should I know if it works? That's what beta testers are for. I
only coded it." (Attributed to Linus Torvalds, somewhere in a
posting)
---(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
[GENERAL] information schema extra fields
I was just reading the PostgreSQL documentation about Information Schemas. Some of the tables have fields which are described as "Applies to a feature not available in PostgreSQL" What does that mean? -- Ron Peterson Network & Systems Manager Mount Holyoke College http://www.mtholyoke.edu/~rpeterso ---(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] Postgres 8 and Slony-1
If things go well, there's a release candidate hoped to be suitable for a Dec 15th date. But releases will _actually_ be ready when they are ready. > Also, are there any plans to include Slony with it, as part of the > same source download, or as part of a group 'matched' source > files/rpms/dpkgs etc? That's not the PG Development Group's responsibility; if makers of Linux distributions choose to do so, that's _their_ problem. It looks as though that will be somewhat hard to do until Slony-I 1.1 gets released, as the 1.0 series isn't readily compilable without having a whole PG source tree around. In 1.1, there are more sophisticated source configuration options that make that particular inconvenience unnecessary. -- let name="cbbrowne" and tld="gmail.com" in name ^ "@" ^ tld;; http://www.ntlug.org/~cbbrowne/linux.html "They're deleting the word `gullible' from modern dictionaries, presumably because it's too difficult a concept for the modern man or woman to understand." ---(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] any body able to access news.postgresql.org recently?
There have been some difficulties with servers; it appears as though most if not all of it has been resolved. -- let name="cbbrowne" and tld="gmail.com" in name ^ "@" ^ tld;; http://www.ntlug.org/~cbbrowne/postgresql.html "I'm not switching from slrn. I'm quite confident that anything that *needs* to be posted in HTML is fatuous garbage not worth my time." -- David M. Cook <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] postgresql and javascript
The world rejoiced as [EMAIL PROTECTED] ("Najib Abi Fadel") wrote:
> i don't see any practical use of javascript with Postgresql, since a
> java script is on the client side.
>
> Unless you want each client machine to have its own database
> !
Actually, there _is_ a practical use for this.
Mozilla/XUL applications presently have to jump through considerable
hoops to get data stored anywhere more sophisticated than files.
It seems to me that having PostgreSQL support more directly embedded
in Mozilla's ECMAScript implementation would encourage building more
data driven applications, which is hardly a bad thing.
[GENERAL] What's faster
Option 1: create table a (id serial, hosts text[]); OR Option 2: create table a (id serial); create table hosts (id int references a, host text); Table 'a' will have about 500,000 records. There will probably be about 20 reads for every write. Each id has approximately 1.1 hosts. If I use the array (option 1), I'll have to loop over the elements of the array to see if I have a match when querying a given id. This isn't hard, but it means that SELECT will always return 1 record when, in option 2, it might return 0 records and only have accessed the indexes. Given the indexes that will be built and disk pages used (cached or otherwise), which mechanism would be faster for searching. ---(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] Query is not using index when it should
On Fri, 10 Dec 2004, Tomas [iso-8859-1] Skäre wrote: > I have a table that looks like this: > > Table "public.cjm_object" > Column | Type| Modifiers > ---+---+--- > timestamp | bigint| not null > jobid | bigint| not null > objectid | bigint| not null > class | integer | not null > field | character varying | not null In 7.4.x and earlier, you need to cast the value you're comparing to into a bigint in order to make sure the indexes are used (in your timestamp case it appears to work because the value doesn't fit in a plain integer). 8.0 should handle this better. > But when doing a search with objectid, class and field, it doesn't use > the idx_cjm_object1 index. > db=# explain analyze select * from cjm_object where objectid=4534 and > class=12 and field='paroid'; Using one of objectid=4534::bigint objectid='4534' objectid=CAST(4534 as bigint) rather than objectid=4534 should make this indexable in 7.4.x. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] postgresql and javascript
Hi i don't see any practical use of _javascript_ with Postgresql, since a _javascript_ is on the client side. Unless you want each client machine to have its own database ! Najib. - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, December 07, 2004 5:27 PM Subject: [GENERAL] postgresql and _javascript_ Does anyone know how to connect _javascript_ to a postgresql database Please send example if anyone has done it thanks Robert Stewart Network Eng Commonwealth Office of Technology Finance and Administration Cabinet 101 Cold Harbor Work # 502 564 9696 Cell # 502 330 5991 Email [EMAIL PROTECTED]
[GENERAL] using inheritance in production application.
How 'ready for prime-time' is the table inheritance feature? I've seen some postings about particular issues (lack of full FK support, for example), but would like to get an overall sense of the stability and robustness of the feature. Also, is there a performance hit in using inheritance? For example if I have a sub-table that adds a couple of columns to a base table, would it be slower to query that structure than it would to represent the extra columns as a separate table that would be joined with the base table? thanks for your help. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] using postgresql functions from php
I have created a function in postgresql
CREATE OR REPLACE FUNCTION
public.insert_vpn_masteraccount(varchar, varchar, varchar, varchar, varchar,
varchar, varchar, varchar, varchar)
RETURNS varchar AS
'
DECLARE
BEGIN
insert into
masteraccount(fname,midint,lname,username,atype) values($1,$2,$3,$4,$5);
insert into passwd(u_id,currentpwd) values((select
max(u_id) from masteraccount where username=$4),$6);
insert into ipinfo(u_id,ipaddress,atype)
values((select max(u_id) from masteraccount where username=$4),$7,$5);
insert into userinfo(u_id,agency,user_email)
values((select max(u_id) from masteraccount where username=$4),$8,$9);
return masteraccount.u_id where
masteraccount.username=$4;
END;
'
LANGUAGE 'plpgsql' VOLATILE;
I can insert data using this function with a sql
statement by typing
select insert_vpn_masteraccount(‘tom’,’d’,’johnson’,’tomd.johnson’,’V’,’1234’,’DHCP’,’AGR’,’[EMAIL PROTECTED]’
this works fine
but when I try to do it from a php web page using
$enter = $db_object->query("SELECT insert_vpn_masteraccount('$fname','$mi','$lname','$acc_type','$passwd','$ip','$agency','$contact')");
This does not work
Can anyone help me?
Thanks
Robert Stewart
Network Eng
Commonwealth Office of Technology
Finance and Administration Cabinet
101 Cold Harbor
Work # 502 564 9696
Cell # 502 330 5991
Email [EMAIL PROTECTED]
Re: [GENERAL] migrating from informix
--- "Gregory S. Williamson" <[EMAIL PROTECTED]> escribió: > Jaime -- > > Sorry that I didn't respond earler -- been quite > busy. don't worry. > > We have migrated our runtime aspect (which is heavy > use of the Spatial Blade/postGIS in a mostly > read-only environment); we were using Informix 9.3 > dynamic server. > > I have some notes I wrote up that I'll post tonight that will be of help > -- they are on a machine I don't have access to > right now. Most of the tables and supporting SQL > (perl scripts mostly) transferred cleanly; i'm doing that at hand right now. > I didn't > try to automate conversion of stored procedures or > triggers (not a lot of these in our runtime). i have no one. > > Greg Williamson > DBA > GlobeXplorer LLC > thanx, greg for your answer regards, Jaime Casanova _ Do You Yahoo!? Información de Estados Unidos y América Latina, en Yahoo! Noticias. Visítanos en http://noticias.espanol.yahoo.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] RC1, missing -lpthread when building with --disable-shared on i686
I just confirmed that the same goes for: Linux 2.6.5-7.111.5-default, i686 i686 i386 GNU/Linux (SuSE v9.1) Makefile.global holds: PTHREAD_CFLAGS = -pthread -D_REENTRANT -D_THREAD_SAFE -D_POSIX_PTHREAD_SEMANTICS PTHREAD_LIBS= -lpthread Would you like confirmation for v9.2 as well? -- Best, Frank. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] replacing a view: bug or feature?
Is the error below a bug? Or a feature? % cat foo.sql SELECT version(); CREATE TABLE foo ( "key" serial NOT NULL PRIMARY KEY, msg character(100)); CREATE TABLE bar ( "key" serial NOT NULL PRIMARY KEY, msg character(100)); CREATE VIEW baz AS SELECT * FROM foo UNION ALL SELECT * FROM bar; CREATE OR REPLACE VIEW baz AS SELECT * FROM foo; % psql -e -f foo.sql SELECT version(); version - PostgreSQL 7.4.6 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.2 20030222 (Red Hat Linux 3.2.2-5) (1 row) CREATE TABLE foo ( "key" serial NOT NULL PRIMARY KEY, msg character(100)); psql:foo.sql:3: NOTICE: CREATE TABLE will create implicit sequence "foo_key_seq" for "serial" column "foo.key" psql:foo.sql:3: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo" CREATE TABLE CREATE TABLE bar ( "key" serial NOT NULL PRIMARY KEY, msg character(100)); psql:foo.sql:4: NOTICE: CREATE TABLE will create implicit sequence "bar_key_seq" for "serial" column "bar.key" psql:foo.sql:4: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "bar_pkey" for table "bar" CREATE TABLE CREATE VIEW baz AS SELECT * FROM foo UNION ALL SELECT * FROM bar; CREATE VIEW CREATE OR REPLACE VIEW baz AS SELECT * FROM foo; psql:foo.sql:6: ERROR: cannot change data type of view column "msg" ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Ridiculous load
On Monday we upgraded one of our PostgreSQL instances from v7.2 to v7.4. Yesterday the box (sabine) on which this runs became very unhappy: It runs RHEL ES v3, kernel 2.4.21-20.ELsmp It's generally a very stable box which runs a number of postgresql instances. But last night we hit very high low averages - 10+, vs the normal 0-2. The culprit appeared to be kswapd, which was using huge amounts of cpu. I'd like to know why! We resolved the problem last night via a reboot. And so far all is well, but I am concerned that the problem may reoccur. I wondered if you could help. In particular the only recent change is the postgresql upgrade above, so the timing seems suspect. We used to run two postgresql instances, each with the following parameters: Command line was: /usr/bin/postmaster '-p' '5679' '-i' '-N' '256' '-B' '128000' '-o' '-S 64000' As we only upgraded one database we split up the 2nd instance into two, each with the following parameters: /usr/bin/postmaster '-p' '5679' '-i' '-N' '256' '-B' '64000' '-o' '-S 32000' (different ports, obviously) The ram on the box should be generous. Right now (whilst it's behaving, and all 3 instances are running) free gives the following output: sabine% free ~ total used free sharedbuffers cached Mem: 38573123834676 22636 0 508803339568 -/+ buffers/cache: 4442283413084 Swap: 2096472 3194601777012 sabine% We plan to revert back to two instances when we upgrade the remaining databases next week. Should we change the amount of ram we allocate to these with the move to v7.4? Are there other settings we should be altering? All this is assuming that it's a postgres problem, rather than something else. Another leading candidate is this, which we are looking into: https://bugzilla.redhat.com/bugzilla/show_bug.cgi?id=132155 It would be good to eliminate all possible causes, though. -- Peter Haworth [EMAIL PROTECTED] "Of course, if you were to print(1..Inf), you'd have plenty of time to go and get a cup of coffee. And even then (given the comparatively imminent heat death of the universe) that coffee would be really cold before the output was complete. So there will probably be a warning when you try to do that." -- Damian Conway in Exegesis 3 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Performance
Werdin Jens wrote: > Hello, > > Ich have a big performance problem. > I'm running postgres 7.4.2 on Suse Linux 9.0 on a dual Xeon 3.0 GHz with 3 > Gbyte Ram. > In postgres.conf I'm using the defaults. That's the place to start. See the guide at: http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html For your hardware, the default configuration settings are far too small. Oh, and you should upgrade to the latest 7.4 as soon as convenient. # Thanks for that link. It helped me very in understanding the configuration file. I changed the shared buffers to 16384 and the shmmax and shmall to 137822208. # > Filesystem is ext3 with writeback > journaling > > I have 3 tables with ca 10 million entries with a gist index on GIS data and > 5 tables with 10 million entries with an index on (timestamp,double,double). > There are 10 tables with 1 million entries and index on int. and some > smaller tables. > > With 1 Gbyte Ram all went fine. Than I added a new table and it startet to > swap. I added 2 Gbyte but the Problem is still there. > The kswapd and kjournald are running nearly permanently. If the system is swapping that's not likely to be due to PostgreSQL, especially on the default configuration settings. > The first time I do a query it takes very long. But the second time it goes > a lot faster. That's because the data is cached in RAM the second time. > Is postgres only using a certain amount of Ram for the indexes? But why my > Ram is full then? > Am I too short of Ram? Is the filesystem too slow? What is "top" showing for memory usage? What does vmstat show for activity when you are having problems? -- Richard Huxton Archonet Ltd # Thank you for your help so far and here comes the output of top and vmstat: Tasks: 55 total, 2 running, 53 sleeping, 0 stopped, 0 zombie Cpu(s): 0.7% user, 9.6% system, 0.0% nice, 89.7% idle Mem: 3104688k total, 3025320k used,79368k free,21084k buffers Swap: 2104504k total, 4128k used, 2100376k free, 2788828k cached PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ SWAP COMMAND 301 postgres 16 0 132m 132m 131m D 22.9 4.4 0:12.03 184 postmaster 11 root 15 0 000 R 6.0 0.0 6:00.680 kswapd 13969 root 16 0 616 572 460 S 0.3 0.0 3:45.64 44 top 313 root 15 0 940 940 744 R 0.3 0.0 0:00.080 top 1 root 15 080 64 60 S 0.0 0.0 0:04.19 16 init 2 root RT 0 000 S 0.0 0.0 0:00.000 migration_CPU0 3 root RT 0 000 S 0.0 0.0 0:00.000 migration_CPU1 4 root RT 0 000 S 0.0 0.0 0:00.000 migration_CPU2 5 root RT 0 000 S 0.0 0.0 0:00.000 migration_CPU3 6 root 15 0 000 S 0.0 0.0 0:00.880 keventd 7 root 34 19 000 S 0.0 0.0 0:05.320 ksoftirqd_CPU0 8 root 34 19 000 S 0.0 0.0 0:01.180 ksoftirqd_CPU1 9 root 34 19 000 S 0.0 0.0 0:03.230 ksoftirqd_CPU2 10 root 34 19 000 S 0.0 0.0 0:01.500 ksoftirqd_CPU3 12 root 15 0 000 S 0.0 0.0 0:10.030 bdflush 13 root 15 0 000 S 0.0 0.0 0:11.520 kupdated 14 root 15 0 000 S 0.0 0.0 0:02.190 kinoded 15 root 25 0 000 S 0.0 0.0 0:00.000 mdrecoveryd 21 root 16 0 000 S 0.0 0.0 0:00.000 scsi_eh_0 24 root 15 0 000 S 0.0 0.0 0:19.950 kjournald 124 root 15 0 000 S 0.0 0.0 0:01.570 kjournald 487 root 15 0 264 236 224 S 0.0 0.0 28:43.54 28 syslogd 490 root 15 0 95244 S 0.0 0.0 0:00.06 948 klogd 535 root 19 0 000 S 0.0 0.0 0:00.000 khubd 645 root 20 06044 S 0.0 0.0 0:00.00 56 resmgrd 668 bin 16 07244 S 0.0 0.0 0:00.03 68 portmap 713 root 20 06844 S 0.0 0.0 0:00.03 64 acpid 722 root 15 0 428 252 200 S 0.0 0.0 0:00.03 176 sshd 761 ntp 15 0 2196 2196 1872 S 0.0 0.1 0:00.120 ntpd 927 root 15 0 384 180 140 S 0.0 0.0 0:00.24 204 master 983 root 15 0 420 396 340 S 0.0 0.0 0:00.12 24 nscd 984 root 15 0 420 396 340 S 0.0 0.0 0:00.01 24 nscd 985 root 15 0 420 396 340 S 0.0 0.0 0:00.09 24 nscd 986 root 15 0 420 396 340 S 0.0 0.0 0:00.06 24 nscd 987 root 15 0 420 396 340 S 0.0 0.0 0:00.05 24 nscd 988 root 15 0 420 396 340 S 0.0 0.0 0:00.07 24 nscd 989 root 15 0 420 396 340 S 0.0 0.0 0:00.05 24 nscd 990 root 15 0 156 128 92 S 0.0 0.0 0:00.10 28 cron 1070 root 19 06844 S 0.0 0.0
[GENERAL] Problems with JDBC site and Postgres mailing list subscription page
http://jdbc.postgresql.org/ seems to be down. Also I can't connect to the mailing list subscription page at: http://webmail.postgresql.org/mj/mj_wwwusr?domain=postgresql.org&func=lists-long-full&extra=pgsql-jdbc Can anyone point me to another site where I can download the postgres jdbc drivers? Thanks, Jc Sorry about the disclaimer. . Note: This e-mail contains privileged and confidential information and is for the sole use of the intended recipient(s). If you are not an intended recipient, you are hereby kindly requested to refrain from printing, copying, or distributing the information contained herein. Furthermore, any other use of the information contained herein is strictly prohibited. If you have received this transmission in error, please kindly notify the sender immediately and destroy all copies of the original message. ---(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] migrating from informix
Jaime -- Sorry that I didn't respond earler -- been quite busy. We have migrated our runtime aspect (which is heavy use of the Spatial Blade/postGIS in a mostly read-only environment); we were using Informix 9.3 dynamic server. I have some notes I wrote up that I'll post tonight -- they are on a machine I don't have access to right now. Most of the tables and supporting SQL (perl scripts mostly) transferred cleanly; I didn't try to automate conversion of stored procedures or triggers (not a lot of these in our runtime). Greg Williamson DBA GlobeXplorer LLC -Original Message- From: Jaime Casanova [mailto:[EMAIL PROTECTED] Sent: Monday, December 06, 2004 9:32 PM To: [EMAIL PROTECTED] Subject: [GENERAL] migrating from informix Hi, someone has successfully migrated a database from informix to postgresql? there are any tools that helps or maybe an script? regards, Jaime Casanova _ Do You Yahoo!? Información de Estados Unidos y América Latina, en Yahoo! Noticias. Visítanos en http://noticias.espanol.yahoo.com ---(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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Query is not using index when it should
I tried to subscribe to pgsql-performance, but there seems to be
something wrong with the majordomo, so I'm sending to general too,
where I'm already subscribed.
My problem is this, using PostgreSQL 7.4.6:
I have a table that looks like this:
Table "public.cjm_object"
Column | Type| Modifiers
---+---+---
timestamp | bigint| not null
jobid | bigint| not null
objectid | bigint| not null
class | integer | not null
field | character varying | not null
data | bytea |
Indexes:
"cjm_object_pkey" primary key, btree ("timestamp", jobid, objectid,
"class", field)
"idx_cjm_object1" btree (objectid, "class", field)
The table has 283465 rows, and the column combination
(objectid,class,field) can occur several times.
Doing a search with all columns in the pkey works, it uses the index:
db=# explain analyze select * from cjm_object where timestamp=1102497954815296
and jobid=9 and objectid=4534 and class=12 and field='paroid';
QUERY PLAN
--
Index Scan using cjm_object_pkey on cjm_object (cost=0.00..32.75 rows=1
width=54) (actual time=0.169..0.172 rows=1 loops=1)
Index Cond: ("timestamp" = 1102497954815296::bigint)
Filter: ((jobid = 9) AND (objectid = 4534) AND ("class" = 12) AND
((field)::text = 'paroid'::text))
Total runtime: 0.381 ms
(4 rows)
But when doing a search with objectid, class and field, it doesn't use
the idx_cjm_object1 index.
db=# explain analyze select * from cjm_object where objectid=4534 and class=12
and field='paroid';
QUERY PLAN
---
Seq Scan on cjm_object (cost=0.00..7987.83 rows=2 width=54) (actual
time=21.660..475.664 rows=1 loops=1)
Filter: ((objectid = 4534) AND ("class" = 12) AND ((field)::text =
'paroid'::text))
Total runtime: 475.815 ms
(3 rows)
I have tried to set enable_seqscan to false, but it gives the same
result, except that the estimated cost is higher.
I have also done a vacuum full analyze, and I have reindexed the
database, the table and the index. I have dropped the index and
recreated it, but it still gives the same result.
Please, could someone give me a clue to this?
Tomas
---(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] Install pgsql directory
I got a server from DELL which contained a Red Hat Linux AS 3 in the box. It contained no postgresql server which I got from an earlier link, http://archives.postgresql.org/pgsql-admin/2003-11/msg00368.php When I did rpm of everything I end up with a non-working postgresql. This may be because there is no /usr/local/pgsql directory. How/what do I need to do to get the directory structure wherein resides the database data. Thank you,
[GENERAL] gborg.postgresql.org
It seems gborg does not accept new projects any more. Does anybody know what happens with it? Also it's really slow and sometimes does not respond. I need an open source development web site which can safely hosts pgpool. Recently I'm getting lot of mails regarding pgpool and need mails lists, archives and hopefully searching capabilty of it. -- Tatsuo Ishii ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Strange NOTICE: messages in log file
Hello all, I was digging through my logs the other day and I noticed a bunch of the following log messages: Dec 8 10:50:18 bahamut postgres[7647]: [273-1] NOTICE: \203\304x\361\330k!\244\301\216w\366\000m\300\333 Dec 8 10:50:18 bahamut postgres[7647]: [274-1] NOTICE: \203\304x\361\330k!\244\301\216w\366\000m\300\333 They numbers after the NOTICE: section are apparently random. Any idea what these are / what they mean? -Joe P.S. please CC me since I am not subscribed to this list. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Spanning tables
Hi ALL, Im wondering sooner or later my disk will be filled-up by postgres's data.. Can anyone give some suggestion on how to deal with this. In oracle you can just assign tables on a diff partition. TIA ---(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] table with sort_key without gaps
Hi, i have a table like this: create table array ( account text NOT NULL, id int4 NOT NULL, value text NOT NULL, PRIMARY KEY (account, id) ); values like this: acc1,1,'hi' acc1,2,'ho' acc1,3,'ha' acc2,1,'ho' acc3,1,'he' acc3,2,'hu' "id" should be positive "id" should not have gaps within the same account "id" should start counting by 1 for each account i cant use sequences because they are producing gaps and doesn't start counting by 1 for each account and i dont want to use postgresql array type for various reasons. for this model to function you need a lot of sophisticated plpgsql function to insert, move or delete entries to keep - did anyone implemented a table like this and wrote some custom functions/triggers for inserting, deleting, moving and so on? If yes it would be nice if he/she is willing to sahre the code with me. - did anyone implemented a table like this and came to the conclusion that this shouldn't be done for any reasons out of my sight? (i don't bother about updating a primary key) kind regards, janning ---(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
[GENERAL] Test
test -- Command Prompt, Inc., home of PostgreSQL Replication, and plPHP. 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 begin:vcard fn:Joshua D. Drake n:Drake;Joshua D. org:Command Prompt, Inc. adr:;;PO Box 215;Cascade Locks;Oregon;97014;USA email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 note:Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL support provider. We provide the only commercially viable integrated PostgreSQL replication solution, but also custom programming, and support. We authored the book Practical PostgreSQL, the procedural language plPHP, and adding trigger capability to plPerl. x-mozilla-html:FALSE url:http://www.commandprompt.com/ version:2.1 end:vcard ---(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] Fwd: 8.0 vs. 7.4 benchmarks
Bruno Wolff III wrote: On Tue, Dec 07, 2004 at 08:43:03 -0800, "Joshua D. Drake" <[EMAIL PROTECTED]> wrote: IMHO 8.0 means, hey all you external developers -- time to test with your applications and report bugs. 8.1 means, alright we got some wide reports -- fixed a few mistakes and now were ready. That should probably be 8.0.1. That is what the next release will be named. Your right that was my bad. Sincerely, Joshua D. Drake We did have a thread about *.* releases about a month ago and the data seemed to suggest that the *.* releases tended to be better than the latest version of the previous *.* release. (I think the main problem is that some fixes were not being back ported because they are too extensive to be safely back ported.) So with 8.0, it might be a good idea to hold off for a little bit to see if anything major was missed during beta, but that it might be desirable to upgrade to 8.0 without waiting for 8.0.1 if there aren't any major problems reported within a few weeks of the release. -- Command Prompt, Inc., home of PostgreSQL Replication, and plPHP. 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 begin:vcard fn:Joshua D. Drake n:Drake;Joshua D. org:Command Prompt, Inc. adr:;;PO Box 215;Cascade Locks;Oregon;97014;USA email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 note:Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL support provider. We provide the only commercially viable integrated PostgreSQL replication solution, but also custom programming, and support. We authored the book Practical PostgreSQL, the procedural language plPHP, and adding trigger capability to plPerl. x-mozilla-html:FALSE url:http://www.commandprompt.com/ version:2.1 end:vcard ---(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] Temporary tables and disk activity
Dear All, I sent a message last weekend asking about temporary tables being written to disk but didn't get any replies. I'm sure there is someone out there who knows something about this - please help! Here is the question again: Looking at vmstat output on my database server I have been suprised to see lots of disk writes going on while it is doing what should be exclusively read-only transactions. I see almost no disk reads as the database concerned is small enough to fit into the OS disk cache. I suspect that it might be something to do with temporary tables. There are a couple of places where I create temporary tables to "optimise" queries by factoring out what would otherwise be duplicate work. The amount of data being written is of the right order of magnitude for this to be the cause. I fear that perhaps Postgresql is flushing these tables to disk, even though they will be dropped before the end of the transaction. Is this a possibility? What issues should I be aware of with temporary tables? Are there any other common causes of lots of disk writes within read-only transactions? Is there any debug output that I can look at to track this down? Thanks in advance for any help that you can offer. Regards, Phil Endecott. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] test
just a test ___ Yahoo! Mail - Agora com 250MB de espaço gratuito. Abra uma conta agora! http://br.info.mail.yahoo.com/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] Mailing List Problems?
I've not seen a post in days on many of the lists to which I subscribe. --Rick ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Fwd: 8.0 vs. 7.4 benchmarks
I would love to get my hands on any numbers that someone might have. Also does anyone know how long it will take for a stable release of 8.0 to come (any estimates would be good) ? The last target date I saw mentioned was 2004-12-15. If a second release candidate is needed, I don't know if that date will be met. It should also be noted that putting any .0 release into production right away is typically a bad idea. This is not a reflection on PostgreSQL but a reflection on software in general. IMHO 8.0 means, hey all you external developers -- time to test with your applications and report bugs. 8.1 means, alright we got some wide reports -- fixed a few mistakes and now were ready. Sincerely, Joshua D. Drake ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings -- 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 PostgreSQL Replicator -- production quality replication for PostgreSQL begin:vcard fn:Joshua Drake n:Drake;Joshua org:Command Prompt, Inc. adr:;;PO Box 215 ;Cascade Locks;OR;97014;US email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 x-mozilla-html:FALSE url:http://www.commandprompt.com version:2.1 end:vcard ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Postgres 8 and Slony-1
Hi Any news when Postgres 8 will be released? Also, are there any plans to include Slony with it, as part of the same source download, or as part of a group 'matched' source files/rpms/dpkgs etc? Thanks All Simon -- Simon Windsor Eml: [EMAIL PROTECTED] Tel: 01454 617689 Mob: 07960 321599 ---(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] any body able to access news.postgresql.org recently?
Hi, I am able to access this news.postgresql.org group two days ago. Now I am not able to access. Is it the news group is down? Thanks, Rosny ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] test
The list is either down or very quiet ? G ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Fwd: 8.0 vs. 7.4 benchmarks
On Tue, Dec 07, 2004 at 08:43:03 -0800, "Joshua D. Drake" <[EMAIL PROTECTED]> wrote: > > IMHO 8.0 means, hey all you external developers -- time to test > with your applications and report bugs. > > 8.1 means, alright we got some wide reports -- fixed a few mistakes > and now were ready. That should probably be 8.0.1. That is what the next release will be named. Typically there is a *.*.1 release not too long after the *.* release. My memory is that this has been around 2-3 months for the last serveral *.* releases. 8.1 will be an important release as it should include integrated autovacuum, some tools for handling PITR recoveries and other changes related to lessons learned from the several major feature additions in 8.0. I will be surprised if 8.1 is released before next fall. We did have a thread about *.* releases about a month ago and the data seemed to suggest that the *.* releases tended to be better than the latest version of the previous *.* release. (I think the main problem is that some fixes were not being back ported because they are too extensive to be safely back ported.) So with 8.0, it might be a good idea to hold off for a little bit to see if anything major was missed during beta, but that it might be desirable to upgrade to 8.0 without waiting for 8.0.1 if there aren't any major problems reported within a few weeks of the release. ---(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
[GENERAL] Simple function
Hi guys, I have a table with two integer fields (field1 and field2)... I would like to construct a function that perform an operation with this two fiels (field1 + fields2), and return the string value "A" if the sum is <20, "B" if the sum is >=20 or <=50, and finally return "C" if the sum is >50. So I would do: "SELECT id,function() FROM mytable;" and get rows like these: 001 A 002 A 003 C 004 C 005 C 006 B ... Could you help me??? Thanks in advance, See ya, Marcelo __ Converse com seus amigos em tempo real com o Yahoo! Messenger http://br.download.yahoo.com/messenger/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] Problem with Insert rules: using nextval: get schema *new* does not exist and other oddities
I am trying to see if it is possible to use rules to create complex updateable views covering two or more tables. Initially I created two test tables: one to hold CD names (cathyscds) and one to hold the track names/numbers/times for each cd (cathystracks) using the following SQL:
CREATE TABLE cathyscds
( cdname varchar,
cdartist varchar,
cdid int4)
CREATE TABLE cathystracks
( cdid int4,
tracknumber int4,
trackname varchar,
tracktime float4)
I want views that would show the tracknames/times/numbers as arrays. I wrote a simple aggregate function makearray (basically copying the toarray example in the documentation), and created the view cdall: using SQL:
CREATE OR REPLACE VIEW cdall as
SELECT cdname, cdid,
(SELECT makeArray (tracknumber) from cathystracks WHERE cathystracks.cdid = cathyscds.cdid)
AS numbers,
(SELECT makeArray (trackname) from cathystracks WHERE cathystracks.cdid = cathyscds.cdid)
AS names,
(SELECT makeArray (tracktime) from cathystracks WHERE cathystracks.cdid = cathyscds.cdid)
AS times
FROM cathyscds;
This works fine. However, I hit lots of problems trying to create an Insert rule that would allow us to do something like:-
INSERT INTO cdall (cdname, names) VALUES ('cd name', '{track1 name, track2 name}');
The function must create the PK cdid and put one row into the cathyscds table, and zero,one or more rows into the cathystracks table.
I wrote a simple function (arrayToTable) to convert arrays to tables, to support this. This works fine stand-alone.
I created a sequence cd_seq to give the next cdid numbers.
I tried the rule:
CREATE OR REPLACE RULE insert_cdall AS ON INSERT TO cdall DO INSTEAD (
select nextval ('cd_seq') INTO new.cdid;
insert into cathyscds (cdid, cdname) VALUES (new.cdid, new.cdname);
insert into cathystracks (trackname, cdid)
select arrayToTable (new.names), currval ('cd_seq') as cdid;
);
Which I think should work. However, the 'select nextval...' statement appears in pgadmin under the Rules section as "select nextval ('cd_seq') as nextval;" which seems odd. It fails: gives error 'schema *new* does not exist. Why is this?
If I get round this problem by using nextval and currval then I get problems that I cannot get round. The above SQL gives error: function _expression_ in FROM may not refer to other relations of same query level. I found a discussion thread 'nonexistent new relation in some places of rules' that hints that this is because the "select * from arrayToTable (new.names)" is being expanded into " select * from new, arrayToTable (new.names) ". Is this so, and if so, how do I get round it. Or should I give up and use functions instead?
We are using Postgres 7.4.1. on a Windows 2000 server. If you need the function code to help, please ask.
Thanks in advance
Cathy Hemsley
:.
CONFIDENTIALITY : This e-mail and any attachments are confidential and may be privileged. If you are not a named recipient, please notify the sender immediately and do not disclose the contents to another person, use it for any purpose or store or copy the information in any medium.
Re: [GENERAL] Performance tuning on RedHat Enterprise Linux 3
Tom Lane <[EMAIL PROTECTED]> writes: > Paul Tillotson <[EMAIL PROTECTED]> writes: > > Does postgres actually do multiple concurrent sorts within a single > > backend? > > Certainly. Consider for example a merge join with each input being > sorted by an explicit sort step. DISTINCT, ORDER BY, UNION, and related > operators require their own sort steps in the current implementation. > It's not difficult to invent queries that require arbitrarily large > numbers of sort steps. I think there's a bit of misunderstanding here. He's talking about two sorts actually being executed in parallel. I don't think Postgres actually does that even if there are multiple sorts in the plan. Postgres isn't threaded (either manually or via OS threads) and Postgres's sort isn't incremental and doesn't return any tuples to the outer nodes until it's completely finished sorting (it's not bubble sort or selection sort:). However a sort step still takes up memory after it's finished executing because it has to store the ordered tuples. So a merge join joining two sorted tables needs to do the sort on one and then keep around the tuples, and do the sort on the second and keep around the tuples for that one too. I think the actual sort algorithm used can consume up to 3x the space of just the sorted tuples. But I'm not really sure on that, nor am I sure whether that space is reclaimed once the actual execution is done. -- greg ---(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] Performance tuning on RedHat Enterprise Linux 3
Tom Lane wrote: > Paul Tillotson <[EMAIL PROTECTED]> writes: >> Does postgres actually do multiple concurrent sorts within a single >> backend? > > Certainly. Consider for example a merge join with each input being > sorted by an explicit sort step. DISTINCT, ORDER BY, UNION, and > related operators require their own sort steps in the current > implementation. It's not difficult to invent queries that require > arbitrarily large numbers of sort steps. Tom, in Bruce's document on performance tuning, the page titled "Multiple CPUs" states: "POSTGRESQL uses a multi-process model, meaning each database connection has its own Unix process...POSTGRESQL does not use multi-threading to allow a single process to use multiple CPUs." I took this to mean that PostgreSQL was not multi-threaded at all, and that each connection was serviced by a single, non-threaded process. Have I interpreted this incorrectly? Are you saying that the backend process actually is multi-threaded? In the example you site, multiple sorts could be accomplished serially in a non-threaded process. -- Guy Rouillier ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Measure the CPU Time per Transaction
Seems "explain" gives the run time, but not CPU time, right? I did not find such statistics in the Statistics Collector either. How to measure the CPU time per transaction in PostgreSQL? Thanks! Stan __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Detecting Temporary Tables
What i want to be able to do is create a tempory table and when i need a value stored in the table i want to check that the table has been created if it has not do a create temp table or just do an update. Michael Fuhr wrote: On Tue, Dec 07, 2004 at 09:22:51AM +1100, Jamie Deppeler wrote: Is it possible to detect the instance of a Temporary through a function? What are you trying to do? Temporary tables are stored in the pg_temp_NNN schema (e.g., pg_temp_1). See the "System Catalogs" chapter in the PostgreSQL documentation for information on finding a table's schema. If you're using 7.4 or later then see also the "Information Schema" chapter -- one of its views has two fields that should be helpful. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] postgresql and javascript
# Does anyone know how to connect javascript to a postgresql database # # Please send example if anyone has done it Maybe you mean Java? Javascript runs within the browser and AFAIK has no facilities for dealing with a database connection. FWIW, you can accomplish what you're probably attempting to do by having PHP or something comprable generate the javascript data sets you want when the page is rendered. -- Steven Klassen - Lead Programmer Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Replication & Support Services, (503) 667-4564 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Loading data from tab delimited file using COPY
Hello everyone, I have had a long term problem loading tab separated data from a text file that has prevented me from migrating my enterprise application from MySQL to PostgreSQL. With version 2 of this application I am making the move to PostgreSQL but am still running into the problem even with PostgreSQL 8 beta. Sample Data is available at http://www.idahoimageworks.com/sampledata.txt I'm using the command: COPY residential FROM 'file' WITH DELIMITER AS '\t'; I'm receiving the error: ERROR: missing data for column "builder" When opened with excel as a tab delimited file all the fields are separated correctly, I know this e-mail is pretty long but I'm stumped. Thanks, Here is the information from the table I'm loading into: Table "mls.residential" Column | Type | Modifiers --++--- id | character varying(10) | type | character varying(30) | area | character varying(30) | list_price | character varying(10) | address | character varying(30) | city | character varying(30) | county | character varying(30) | state| character varying(3) | zip | character varying(5) | status | character varying(30) | number_beds | character varying(10) | number_baths | character varying(10) | approximate_sqft | character varying(10) | land_size| character varying(30) | age | character varying(30) | level| character varying(30) | garage_capacity | character varying(10) | list_agent | character varying(30) | list_office | character varying(50) | list_agent_2nd_phone | character varying(25) | co_agent | character varying(30) | list_date| character varying(10) | directions | character varying(100) | approximate_acres| character varying(10) | subdivision | character varying(30) | completion_date | character varying(10) | year_built | character varying(10) | lot_length | character varying(10) | lot_width| character varying(10) | irrigation_district | character varying(30) | irrigation_district_name | character varying(25) | water_shares_avail | character varying(30) | water_deliverable| character varying(30) | school_district | character varying(30) | grade_school | character varying(30) | jr_high | character varying(30) | sr_high | character varying(30) | above_grade_fin | character varying(5) | below_grade_fin | character varying(5) | fin_sqft | character varying(5) | above_grade_unfin| character varying(5) | below_grade_unfin| character varying(5) | unfin_sqft | character varying(5) | price_per_sqft | character varying(10) | master_bedroom_size | character varying(5) | bedroom2_size| character varying(5) | bedroom3_size| character varying(5) | bedroom4_size| character varying(5) | bedroom5_size| character varying(5) | bonus_room_size | character varying(5) | den_study_size | character varying(5) | eating_space_size| character varying(5) | entry_size | character varying(5) | family_room_size | character varying(5) | formal_dining_size | character varying(5) | great_room_size | character varying(5) | kitchen_size | character varying(5) | living_room_size | character varying(5) | office_size | character varying(5) | other_room_size | character varying(5) | recreation_room_size | character varying(5) | utility_room_size| character varying(5) | shop_dimensions | character varying(10) | garage_dimensions| character varying(10) | remarks | character varying(512) | central_air | text | brick| text | one | text | attached | text | baseboard| text | breakfast| text | single | text | auto | text | abandoned_septic | text | above_ground | text | composition_shingle | text | holding_tank | text | alarm| text | artesian_well| tex
Re: [GENERAL] Get diagnistic (row_count) 7.3 vs. 7.4 changes
Hello. Seeking further clarity regarding GET DIAGNOSTICS behavior in 7.4. As described previously GET DIAGNOSTICS in the following example does not work in 7.4.5: CREATE OR REPLACE FUNCTION "public"."rowcount_test" () RETURNS bigint AS' DECLARE base_hits bigint; BEGIN base_hits := 0; CREATE TEMP TABLE ltbl_temp AS SELECT 1 AS data; GET DIAGNOSTICS base_hits = ROW_COUNT; RETURN base_hits; END; 'LANGUAGE 'plpgsql' VOLATILE Base_hits returns 0 and not 1 while 7.3 returns 1. Without base_hits := 0, null would be returned. Output: 7.3.3 queriesdbtest=# select * from public.rowcount_test(); rowcount_test --- 1 (1 row) 7.4.5 queriesdbtest=# select * from public.rowcount_test(); rowcount_test --- 0 (1 row) What is the preferred/recommended way for obtaining rows worked with via the last SQL statement? Can this be a bug in 7.4.5 as the documentation indicates that this should work as described? Thanks in advance, Rob Maksim Likharev <[EMAIL PROTECTED]> writes: consider following code: CREATE OR REPLACE FUNCTION rowcount_test() RETURNS bigint AS ' DECLARE base_hits bigint; BEGIN CREATE TEMP TABLE ltbl_temp AS SELECT 1 AS data; GET DIAGNOSTICS base_hits = ROW_COUNT; RETURN base_hits; END; ' LANGUAGE PLPGSQL VOLATILE; in 7.3.3 GET DIAGNOSTICS was returning number of selected rows into a temp table in 7.4.5 GET DIAGNOSTICS returns 0 Hmm. I'm not sure if that's a bug or an improvement. The command did not return any rows to plpgsql, so in that sense row_count = 0 is correct, but I can see why you feel you've lost some capability. Anyone else have an opinion about this? regards, tom lane Subject: Re: [GENERAL] Get diagnistic (row_count) 7.3 vs. 7.4 changes From: "Richard Huxton" <[EMAIL PROTECTED]> Date: Thu, 2 Dec 2004 01:34:37 -0800 To: "Tom Lane" <[EMAIL PROTECTED]> CC: <[EMAIL PROTECTED]>, <[EMAIL PROTECTED]> Tom Lane wrote: Maksim Likharev <[EMAIL PROTECTED]> writes: in 7.3.3 GET DIAGNOSTICS was returning number of selected rows into a temp table in 7.4.5 GET DIAGNOSTICS returns 0 Hmm. I'm not sure if that's a bug or an improvement. The command did not return any rows to plpgsql, so in that sense row_count = 0 is correct, but I can see why you feel you've lost some capability. Anyone else have an opinion about this? Well, from the manuals: "The currently available status items are ROW_COUNT, the number of rows processed by the last SQL command sent down to the SQL engine" Nothing there about rows being returned. And by analogy: "A PERFORM statement sets FOUND true if it produces (and discards) a row, false if no row is produced." If you've FOUND rows then presumably ROW_COUNT should be non-zero. So set it if rows aren't returned I'd opine. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Shared disk
On Thu, Dec 09, 2004 at 09:02:51AM +0530, Nageshwar Rao wrote: Hi, > I would like to use shared disk for two Postgresql database. I mean that two > Postgresql Database point to same $PGDATA directory. Is this possible in > Postgresql Not at all. You can of course create two databases within one cluster (== postmaster), but you can't have two clusters in the same data area. -- Alvaro Herrera (<[EMAIL PROTECTED]>) "El sentido de las cosas no viene de las cosas, sino de las inteligencias que las aplican a sus problemas diarios en busca del progreso." (Ernesto Hernández-Novich) ---(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] When to encrypt
On 12/6/2004 6:10 PM, Greg Stark wrote: Martijn van Oosterhout <[EMAIL PROTECTED]> writes: Actually, hard disk encryption is useful for one thing: so if somebody kills the power and takes the hard disk/computer, the data is safe. While it's running it's vulnerable though... Where do you plan to keep the key? I was wondering where he keeps his servers. Are they kept under a little rain shelter on the parking lot, so that one who steels them at least can't sue the company for hurting his back while carrying the racks outside? Jan -- #==# # 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 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Problems with information_schema
Hi all I just copied a rather complex application database together with all tables and triggers to 8.0.0RC1 running under windows (the original 7.4 database still runs on linux). I migrated everyting using pg_dump and then executing the resulting scripts via pgsql. Most things work as expected until now, except for the following problem. My application uses some functions that use the information_schema. Now these functions seem to fail. Further analysis reveals that some of the dictionary views (e.g. information_schema.table_constraints) always give empty result sets. I can see several differences between 7.4 and 8.0RC1, mainly with schema-qualifiyng all names. Below are the definitions I find in the view "table_constraints". Is this a known problem? If the problem is unknown, I could probably help to find out what's going wrong. The base tables (in schema pg_catalog) appear to be ok at first sight. Perhaps the information_schema dictionary views have not yet been debugged yet? Best regards --Marcel example of diffs in view definition (as reported by pgadmin III 1.2.0 final, Nov 29, 2004): In 8.0.0RC1: CREATE OR REPLACE VIEW information_schema.table_constraints AS SELECT current_database()::information_schema.sql_identifier AS constraint_catalog, nc.nspname::information_schema.sql_identifier AS constraint_schema, c.conname::information_schema.sql_identifier AS constraint_name, current_database()::information_schema.sql_identifier AS table_catalog, nr.nspname::information_schema.sql_identifier AS table_schema, r.relname::information_schema.sql_identifier AS table_name, CASE c.contype WHEN 'c'::"char" THEN 'CHECK'::text WHEN 'f'::"char" THEN 'FOREIGN KEY'::text WHEN 'p'::"char" THEN 'PRIMARY KEY'::text WHEN 'u'::"char" THEN 'UNIQUE'::text ELSE NULL::text END::information_schema.character_data AS constraint_type, CASE WHEN c.condeferrable THEN 'YES'::text ELSE 'NO'::text END::information_schema.character_data AS is_deferrable, CASE WHEN c.condeferred THEN 'YES'::text ELSE 'NO'::text END::information_schema.character_data AS initially_deferred FROM pg_namespace nc, pg_namespace nr, pg_constraint c, pg_class r, pg_user u WHERE nc.oid = c.connamespace AND nr.oid = r.relnamespace AND c.conrelid = r.oid AND r.relowner = u.usesysid AND r.relkind = 'r'::"char" AND u.usename = "current_user"(); ALTER TABLE information_schema.table_constraints OWNER TO postgres; GRANT ALL ON TABLE information_schema.table_constraints TO postgres; GRANT SELECT ON TABLE information_schema.table_constraints TO public; in 7.4: CREATE OR REPLACE VIEW information_schema.table_constraints AS SELECT current_database()::character varying::sql_identifier AS constraint_catalog, nc.nspname::character varying::sql_identifier AS constraint_schema, c.conname::character varying::sql_identifier AS constraint_name, current_database()::character varying::sql_identifier AS table_catalog, nr.nspname::character varying::sql_identifier AS table_schema, r.relname::character varying::sql_identifier AS table_name, CASE WHEN c.contype = 'c'::"char" THEN 'CHECK'::text WHEN c.contype = 'f'::"char" THEN 'FOREIGN KEY'::text WHEN c.contype = 'p'::"char" THEN 'PRIMARY KEY'::text WHEN c.contype = 'u'::"char" THEN 'UNIQUE'::text ELSE NULL::text END::character_data AS constraint_type, CASE WHEN c.condeferrable THEN 'YES'::text ELSE 'NO'::text END::character_data AS is_deferrable, CASE WHEN c.condeferred THEN 'YES'::text ELSE 'NO'::text END::character_data AS initially_deferred FROM pg_namespace nc, pg_namespace nr, pg_constraint c, pg_class r, pg_user u WHERE nc.oid = c.connamespace AND nr.oid = r.relnamespace AND c.conrelid = r.oid AND r.relowner = u.usesysid AND r.relkind = 'r'::"char" AND u.usename = "current_user"(); ALTER TABLE information_schema.table_constraints OWNER TO postgres; GRANT ALL ON TABLE information_schema.table_constraints TO postgres WITH GRANT OPTION; GRANT SELECT ON TABLE information_schema.table_constraints TO public; ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Clustering in the presence of hierarchies
I'd like to ask you about the clustering strategy that postgres implements. Exactly: I have created a hierachy of tables using 'inherits' relationship. I have populated tables with a huge amount of data and then I cluster each table according to an attribute that exists on Root table (hence, this attribute is inherited by all tables). In general, clustering for each of those table means to reorder on disc tuples, in order to be sequential stored. The question is: Does postgres uses the knowledge of the hierarchy structure to reorder tuples of each table to be stored almost after its direct paent-table tuples? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] postgresql and javascript
Chris Smith wrote: [EMAIL PROTECTED] wrote: Does anyone know how to connect javascript to a postgresql database You can't connect javascript to any sort of database. Actually you can, with server side javascript, although I don't know if it supports postgresql. It does support odbc. -- Until later, Geoffrey ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] postgresql and javascript
On Tuesday 07 December 2004 22:42, Chris Smith wrote: > [EMAIL PROTECTED] wrote: > > Does anyone know how to connect javascript to a postgresql database > > You can't connect javascript to any sort of database. You need something > like php, python etc - it can connect to your database and generate > javascript. Well you may not be able to now, but I seem to recall the a future version of Kexi (the KOffice version of Access) although it currently it scripted in Python it is intended to allow it to be scripted in ECMAScript (which is near enough JavaScript), so that will have to produce an interface. But you will need to wait till at least next year. David > > Regards, > > Chris Smith > > Suite 30, 45-51 Huntley St, Alexandria, NSW 2015 Australia > > Ph: +61 2 9517 2505 > Fx: +61 2 9517 1915 > > email: [EMAIL PROTECTED] > web: www.interspire.com > > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faqs/FAQ.html ---(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] Postgres not using shared memory
Karl O. Pinc wrote: Hi, I can't seem to get postgresql to use shared memory and performance is terrrible. PostgreSQL 7.4.6 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-42) $ uname -a Linux artsdata 2.4.21-20.0.1.ELsmp #1 SMP Wed Nov 24 20:34:01 EST 2004 i686 i686 i386 GNU/Linux $ free total used free sharedbuffers cached Mem: 20553361614408 440928 0 42401488104 -/+ buffers/cache: 1220641933272 Swap: 2096440 12162095224 $ /sbin/sysctl -a | grep shm ... kernel.shmall = 1073741823 kernel.shmmax = 1073741823 free will not correctly show shared memory usage if you are allocating more than 512 megs (I think). I don't recall the exact amount but this is a limitation of free. What type of hard drives do you have? What does a sar 1 or iostat report? Sincerely, Joshua D. Drake (I had both above 1 larger, and tried cutting it down by one out of endpoint paranoia. That should be 1GB.) postgresql.conf: #shared_buffers = 126976 # 1GB - 32MB (just to leave some below kernel limit) shared_buffers = 6000 # testing to get _some_ shared memory => select * from pg_settings where name = 'shared_buffers'; name | setting | context | vartype | source | min_val | max_val +-++-++-+ shared_buffers | 6000| postmaster | integer | configuration file | 16 | 2147483647 (1 row) I've see my setting in pg_settings all along, but free never shows me any shared memory used. I've tried shutting down all the other daemons and restarting postgresql and nothing changes. What am I doing wrong here? Thanks. Karl <[EMAIL PROTECTED]> Free Software: "You don't pay back, you pay forward." -- Robert A. Heinlein ---(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 -- 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 PostgreSQL Replicator -- production quality replication for PostgreSQL begin:vcard fn:Joshua Drake n:Drake;Joshua org:Command Prompt, Inc. adr:;;PO Box 215 ;Cascade Locks;OR;97014;US email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 x-mozilla-html:FALSE url:http://www.commandprompt.com version:2.1 end:vcard ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Natural ordering in postgresql? Does it exist?
Hi all, I have a field that I'll be ordering and I noticed that ordering is done logically and would confuse my users here (1,12,16,4,8, etc). I'm writing an application in PHP that connects to Postgres and while I know that PHP has some powerful natural ordering functions it would be much easier if I could just use something from postgres directly. Does there exist any way to order naturally? Thanks, Clark Endrizzi _ Is your PC infected? Get a FREE online computer virus scan from McAfee® Security. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] vacuum problem?
On Tue, Dec 07, 2004 at 06:35:39PM -0700, Ed L. wrote: > On Tuesday December 7 2004 6:27, Ed L. wrote: > > On Tuesday December 7 2004 5:58, marcelo Cortez wrote: > La traducción de Babelfish de mi mensaje anterior era hilarante, e indica > que mi español es lejos peor que pensamiento de I. Tends to happen :-) Babelfish translation is usually bad with good spanish, and is really funny with not-so-good spanish. If you want to practice spanish I invite you to join the pgsql-es-ayuda mailing list. And of course, I invite Marcelo to join that list too, because we can probably find out what's really happen if he is able to actually describe his problem. For Marcelo's sake, the URL of that list is http://archives.postgresql.org/pgsql-es-ayuda -- Alvaro Herrera (<[EMAIL PROTECTED]>) "Las mujeres son como hondas: mientras más resistencia tienen, más lejos puedes llegar con ellas" (Jonas Nightingale, Leap of Faith) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] How to get rid of notices for create table?
Hi, I did a search for this issue but don't seem to find anything reasonably satisfactory. I'm getting boring notices when creating tables: test=# CREATE TABLE cliente ( test(# idINTEGER NOT NULL, test(# id_indica INTEGER NULL, test(# email VARCHAR(40) NULL, test(# contato VARCHAR(40) NULL, test(# data DATE DEFAULT NOW(), test(# log VARCHAR(40) NOT NULL, test(# num VARCHAR(16) NOT NULL, test(# comp VARCHAR(16) NULL, test(# bairroVARCHAR(40) NOT NULL, test(# cep CHAR(8) NOT NULL, test(# cidadeVARCHAR(40) NOT NULL, test(# estadoCHAR(2) NOT NULL, test(# refendVARCHAR(256) NULL, test(# CONSTRAINT pk_cliente PRIMARY KEY (id), test(# CONSTRAINT uk_email UNIQUE (email), test(# CONSTRAINT ck_indica CHECK (id != id_indica) test(# ); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pk_cliente" for table "cliente" NOTICE: CREATE TABLE / UNIQUE will create implicit index "uk_email" for table "cliente" CREATE TABLE Got a few questions: 1 - What those 2 notice messages mean? 2 - How can I get rid of them? Regards, Scorphus. = -- .''`. Pablo Aguiar : :' : Proud Debian GNU/Linux Admin and User `. `'` GNU/Linux User #346447 - PC #238975 `- Debian, when you have better things to do than fix a system. ___ Yahoo! Mail - Agora com 250MB de espaço gratuito. Abra uma conta agora! http://br.info.mail.yahoo.com/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] Significado logo PostgreSQL
Amigos: ¿Qué significado tiene el logo de PostgreSQL? Saludos, -- Mirko Coz Berrospi ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Create AS question
Bob Powell wrote: Hello everyone: If I excute the following: CREATE table Test_backup as SELECT * FROM Test_Scores; it creates my table without the original table's constraints or triggers. Is there a way to preserve them in the copy? Only if you create a schema dump of just the table and then use insert into foo select * from bar Sincerely, Joshua D. Drake Thanks. Bob Powell Database Administrator ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings -- 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 PostgreSQL Replicator -- production quality replication for PostgreSQL begin:vcard fn:Joshua Drake n:Drake;Joshua org:Command Prompt, Inc. adr:;;PO Box 215 ;Cascade Locks;OR;97014;US email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 x-mozilla-html:FALSE url:http://www.commandprompt.com version:2.1 end:vcard ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] Questions on stored-procedure best practices
I'm used to writing C/Java/python type stuff and am a bit new to stored procedures and looking for a few best practices. I'm using plpgsql (as opposed to plpython, etc.) because it offers the most flexibility in terms of data-types, etc. good editor: Usually emacs does a good job, but postgres puts a stored procedure between ' and ' and this turns off any font-lock highlighting for the entire stored procedure. Can I use something other than quotes or fix this somehow? Is there a better editor? preprocessor: I saw one reference to people running stuff through the C-preprocessor before importing into their DB. Is this common practice? I don't see a huge benefit for it yet unless I want to support multiple DBs. file-names: I know it doesn't matter, but mostly it seems to make sense to put stuff in .sql files. I've seen some reference to people putting stuff in .sp files. What works best for people in terms of organization? packages: I saw there was a patch to support oracle-style packages in postgres back in ~2001, but I saw nothing else. Is this planned? I imagine I could use 'schemas', but I don't think this lets me share variables and I think these are more for splitting up table name-spaces than for associating a group of functions. other: Any other suggestions? Thanks. Eric Brown 408-571-6341 www.propel.com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] migrating from informix
--- "Gregory S. Williamson" <[EMAIL PROTECTED]> escribió: > Jaime -- > > > > Sorry that I didn't respond earler -- been quite > > busy. > don't worry. > > > > > We have migrated our runtime aspect (which is > heavy > > use of the Spatial Blade/postGIS in a mostly > > read-only environment); we were using Informix 9.3 > > dynamic server. > > > > I have some notes I wrote up that I'll post > tonight > that will be of help > > > -- they are on a machine I don't have access to > > right now. Most of the tables and supporting SQL > > (perl scripts mostly) transferred cleanly; > i'm doing that at hand right now. > > > I didn't > > try to automate conversion of stored procedures or > > triggers (not a lot of these in our runtime). > i have no one. > > > > > Greg Williamson > > DBA > > GlobeXplorer LLC > > > thanx, greg for your answer regards, Jaime Casanova _ > Do You Yahoo!? > Información de Estados Unidos y América Latina, en > Yahoo! Noticias. > Visítanos en http://noticias.espanol.yahoo.com > _ Do You Yahoo!? Información de Estados Unidos y América Latina, en Yahoo! Noticias. Visítanos en http://noticias.espanol.yahoo.com ---(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
[GENERAL] Join on virtual table
Hi. I'd like to return a result set from a plpgsql function constructed out of a 'virtual table' joined to an actual table, and struggling to find a sane approach. I have a table 'recs' with records like this. day | nums --- 2 | 1 5 | 3 2 | 2.5 For a particular month in the year I would like to generate all the days in the month into a virtual table. 'virt' vday --- 1 ... omitted ... 30 I would like a result set something like this: day | nums --- 1 | 0 2 | 3.5 3 | 0 4 | 0 5 | 3 6 | 0 ... etc. Below is a first attempt. It fails because it looks like one can't have two set returning loops defined in the same function, quite apart from any join oddities joining against 'dayom'. Thanks! Rory DROP TYPE dom CASCADE; CREATE TYPE dom AS ( d INTEGER ); DROP TYPE comb CASCADE; CREATE TYPE comb AS ( day INTEGER, val INTEGER ); CREATE OR REPLACE FUNCTION fn_test1 ( ) RETURNS setof comb AS ' DECLARE dayom dom%rowtype; resultercomb%rowtype; BEGIN FOR i IN 1..30 LOOP dayom.d = i; RETURN NEXT dayom; END LOOP; FOR resulter IN SELECT dayom.d as day, recs.nums FROM dayom LEFT OUTER JOIN recs r ON dayom.d = recs.day ORDER BY dayom.d LOOP RETURN NEXT resulter; END LOOP; RETURN; END;' LANGUAGE plpgsql; -- Rory Campbell-Lange <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Sheduler in Postgres
cron works pretty well in unix. Scheduled tasks on windows have, in my experience, been on the flakey side. On Tue, 7 Dec 2004, [iso-8859-2] Együd Csaba wrote: > Hi, > I should schedule the execution of several stored procedures. Now I use an > NT service for this, but as far as I know e.g. the Oracle has such a thing. > It would be great if I could fire procedures on a timer basis. > > Is there a better solution for this than mine? > > Many thanks, > > -- Csaba > > > --- > Outgoing mail is certified Virus Free. > Checked by AVG anti-virus system (http://www.grisoft.com). > Version: 6.0.805 / Virus Database: 547 - Release Date: 2004.12.03. > > > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Servers live ... news at 11 ...
I just wanted to get a quick note out now that I can ... I'll send a longer note out explain what happened later this evening when I get a spare breath to write one ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Reusable database design
Joachim Zobel wrote: Hi. I can't be the first to think about this. There is a million online shops out there, which all more or less have the same database design. Has anybody thought about creating generic reusable/customizable designs for such cases? Thx, Joachim Check out your favourite bookstore for resources. Amazon also lists a few books: * Data Model Patterns: Conventions of Thought by David C. Hay: "Analyzes data structures common to many types of businesses in areas such as accounting, material requirements planning, process manufacturing, contracts, laboratories, and documents, for analysts who have learned the basics of data modeling (or entity/relationship modeling) but who need more insight to prepare a model of a real business. Includes b&w diagrams, and a set of ready-to-use models for typical applications in many industries. Annotation copyright Book News, Inc. Portland, Or." from Amazon.com * The Data Model Resource Book, Vol. 1: A Library of Universal Data Models for All Enterprises by Len Silverston * The Data Model Resource Book, Vol. 2: A Library of Data Models for Specific Industries by Len Silverston * Information Modeling and Relational Databases: From Conceptual Analysis to Logical Design by Terry Halpin * The Data Modeling Handbook : A Best-Practice Approach to Building Quality Data Models by Michael C. Reingruber * Data Modeler's Workbench: Tools and Techniques for Analysis and Design by Steve Hoberman * Analysis Patterns : Reusable Object Models (Addison-Wesley Object Technology: Addison-Wesley Object Technology Series) by Martin Fowler I'm sure that there are even more available. Ron ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] Postgres 7.4.6 x86_64 RPMS
Hello I am looking for Postgres 7.4.6 x86_64 RPMS for Redhat ES 3.0. I Think 7.4.6 will be included in Redhat ES 4.0 but I am not sure when that will be released and we may not upgrade at that time. I have found RPMS for Fedora Core 3 but they want work. Thanks John Allgood - ESC ---(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] Postgres not using shared memory
"Karl O. Pinc" <[EMAIL PROTECTED]> writes: > Hi, > > I can't seem to get postgresql to use shared memory and performance is > terrrible. 1) Linux doesn't track shared pages (which is not the same as shared memory) anymore--the field the in 'free' output is just there to avoid breaking software. Use the 'ipcs' command to list shared memory segments--you'll find Postgres is using what you told it to (otherwise it woudn't start up at all). 2) The -performance list is a good place to find out why queries are running slowly. -Doug ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Performance tuning on RedHat Enterprise Linux 3
But isn't the problem when the planner screws up and not the sortmem setting? There was my case where the 7.4 planner estimated 1500 distinct rows when there were actually 1391110. On 7.3.4 it used about 4.4MB. Whereas 7.4 definitely used more than 400MB for the same query ) - I had to kill postgresql - didn't wait for it to use more. That's a lot more than 200%. Maybe 3x sort_mem is too low, but at least by default keep it below server RAM/number of backends or something like that. Even if the planner has improved a lot if cases like that still occur from time to time it'll be a lot better for stability/availability if there's a limit. Doubt if I still have the same data to test on 8.0. Link. At 12:35 AM 12/7/2004 -0500, Tom Lane wrote: Neil Conway <[EMAIL PROTECTED]> writes: > As a quick hack, what about throwing away the constructed hash table and > switching to hashing for sorting if we exceed sort_mem by a significant > factor? (say, 200%) We might also want to print a warning message to the > logs. If I thought that a 200% error in memory usage were cause for a Chinese fire drill, then I'd say "yeah, let's do that". The problem is that the place where performance actually goes into the toilet is normally an order of magnitude or two above the nominal sort_mem setting (for obvious reasons: admins can't afford to push the envelope on sort_mem because of the various unpredictable multiples that may apply). So switching to a hugely more expensive implementation as soon as we exceed some arbitrary limit is likely to be a net loss not a win. If you can think of a spill methodology that has a gentle degradation curve, then I'm all for that. But I doubt there are any quick-hack improvements to be had here. regards, tom lane ---(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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] vacuum problem?
On Tuesday December 7 2004 6:27, Ed L. wrote: > On Tuesday December 7 2004 5:58, marcelo Cortez wrote: > > > > /psql someDatabase > > > > vaccum full verbose; > > > > but nothing informs. > > > > in which cases vacuum do not inform anything? > > > > postgresql 7.4 on red hat 9.0 > > > > any clue be appreciate. > > > > best regards > > > > > > Is your server logging to a file? What does it show > > > when you issue your > > > command? > > > > nothing only the prompt :( > > Mi espanol es muy mal, pero yo creo que usted necessita aprender > como encender escribiendo registro para su servidor. Es differente > que símbolo del sistema. Cuando su tiene un registro, puede mirar > a eso para mas pistas. Es possible que esto recurso va a ayudar: > > http://www.postgresql.org/docs/7.4/static/runtime-config.html#RUNTIME-CON >FIG-LOGGING > > Espero eso ayuda usted. La traducción de Babelfish de mi mensaje anterior era hilarante, e indica que mi español es lejos peor que pensamiento de I. Esto es quizá mejor: Le pienso necesidad de aprender cómo forzar su servidor escribir a un fichero de diario. Cuando usted tiene eso, usted puede poder ver algunas pistas en ese archivo. Vea el URL arriba para los directorios de postgresql.conf que usted puede fijar para comenzar a registrar a un archivo. Ed ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] Sheduler in Postgres
Hi, I should schedule the execution of several stored procedures. Now I use an NT service for this, but as far as I know e.g. the Oracle has such a thing. It would be great if I could fire procedures on a timer basis. Is there a better solution for this than mine? Many thanks, -- Csaba --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.805 / Virus Database: 547 - Release Date: 2004.12.03. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] vacuum problem?
On Tuesday December 7 2004 5:58, marcelo Cortez wrote: > > > > > /psql someDatabase > > > vaccum full verbose; > > > but nothing informs. > > > in which cases vacuum do not inform anything? > > > postgresql 7.4 on red hat 9.0 > > > any clue be appreciate. > > > best regards > > > > Is your server logging to a file? What does it show > > when you issue your > > command? > > nothing only the prompt :( Mi espanol es muy mal, pero yo creo que usted necessita aprender como encender escribiendo registro para su servidor. Es differente que símbolo del sistema. Cuando su tiene un registro, puede mirar a eso para mas pistas. Es possible que esto recurso va a ayudar: http://www.postgresql.org/docs/7.4/static/runtime-config.html#RUNTIME-CONFIG-LOGGING Espero eso ayuda usted. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] signal 4 recieved when index creation
hi! I am testing postgreSQL 8.0 rc1 compiled with xlc on AIX5.2. I'm trying to create an index on a 4 500 000 raws table and postmaster recieves signal 4. what can I do to resolve it? Thanks Frédéric Germaneau ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Shared disk
Hi, I would like to use shared disk for two Postgresql database. I mean that two Postgresql Database point to same $PGDATA directory. Is this possible in Postgresql Clarification is appreciated. Thx
Re: [GENERAL] Performance tuning on RedHat Enterprise Linux 3
On Tue, 07 Dec 2004 07:50:44 -0500, P.J. Josh Rovero <[EMAIL PROTECTED]> wrote: > There are many reports of kernel problems with memory allocation > (too agressive) and swap issues with RHEL 3.0 on both RAID > and non-RAID systems. I hope folks have worked through all > those issues before blaming postgresql. We have seen several boxes have kswapd go crazy (near 100% CPU) on RHEL 3 boxes. Upgrading to kernel 2.4.21-4 fixed this. Tony Wasson ---(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] When to encrypt
Daniel Martini <[EMAIL PROTECTED]> writes: > Well, where do you plan to keep the key for your encrypted backup tapes, > like you suggested in another post in this thread ;-) > That's pretty much the same problem. No it's not. I can keep the key for the encrypted backup tapes in my pocket. I won't need it unless I'm restoring a backup, where I'll be present. Your hard drives need to be decrypted by the database whenever it's running. If you can deal with your database being unavailable after a reboot until manual intervention then you're ok. But most people can't. -- greg ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Create AS question
Hello everyone: If I excute the following: CREATE table Test_backup as SELECT * FROM Test_Scores; it creates my table without the original table's constraints or triggers. Is there a way to preserve them in the copy? Thanks. Bob Powell Database Administrator ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] Reusable database design
Hi. I can't be the first to think about this. There is a million online shops out there, which all more or less have the same database design. Has anybody thought about creating generic reusable/customizable designs for such cases? Thx, Joachim -- "... ein Geschlecht erfinderischer Zwerge, die fuer alles gemietet werden koennen."- Bertolt Brecht - Leben des Galilei ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Postgres not using shared memory
Hi, I can't seem to get postgresql to use shared memory and performance is terrrible. PostgreSQL 7.4.6 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-42) $ uname -a Linux artsdata 2.4.21-20.0.1.ELsmp #1 SMP Wed Nov 24 20:34:01 EST 2004 i686 i686 i386 GNU/Linux $ free total used free sharedbuffers cached Mem: 20553361614408 440928 0 4240 1488104 -/+ buffers/cache: 1220641933272 Swap: 2096440 12162095224 $ /sbin/sysctl -a | grep shm ... kernel.shmall = 1073741823 kernel.shmmax = 1073741823 (I had both above 1 larger, and tried cutting it down by one out of endpoint paranoia. That should be 1GB.) postgresql.conf: #shared_buffers = 126976 # 1GB - 32MB (just to leave some below kernel limit) shared_buffers = 6000 # testing to get _some_ shared memory => select * from pg_settings where name = 'shared_buffers'; name | setting | context | vartype | source | min_val | max_val +-++-++-+ shared_buffers | 6000| postmaster | integer | configuration file | 16 | 2147483647 (1 row) I've see my setting in pg_settings all along, but free never shows me any shared memory used. I've tried shutting down all the other daemons and restarting postgresql and nothing changes. What am I doing wrong here? Thanks. Karl <[EMAIL PROTECTED]> Free Software: "You don't pay back, you pay forward." -- Robert A. Heinlein ---(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
[GENERAL] Sql performace - why soo long ?
Hi there :)
I don't understand why following '3' statements are SOO different. We
run latest PgSQL, OpenBSD 3.4, AMD XP 2000+ and plenty of RAM... CPU was
fully dedicated to PG. All SQL's do the same and produces same result.
Imagine 2 small tables:
A - ~5000 records (5.000 record is small number :)
B - ~1500 records
B have 'key' to A (let's call it 'key') pointing to 'id' of table A
A includes (also) 'size' column (let's call it 'rsize') - INT type
B.key -> A.id
A.id is normal 'sequence'
B.key have created index (btree)
There are more B tables - B1, B2,... (tenths). So, B means 'some' of these
tables.
I need to get total size of all elemnts belongs to B.
Maybe important - maybe not. Table A includes '2' columns which holds LOOONG
datas (in total hundreds of megabytes). It's why divided from B tables
And also it is reason, why I don't need to include any other limitation in
following SQL's.
Simplest solution:
---
SELECT sum(rsize) from A where id IN (SELECT key FROM b)
This takes ~10 seconds to execute (!)
Explain:
Aggregate (cost=64235.38..64235.38 rows=1 width=4)
-> Seq Scan on A (cost=0.00..64229.17 rows=2486 width=4)
Filter: (subplan)
SubPlan
-> Seq Scan on B (cost=0.00..25.57 rows=1457 width=0)
(5 rows)
To me 10 seconds sounds really bad - because both tables are small ones
(only 7.5 millions of cross records)... The CPU isn't sooo slow.
'IN' style (generated)
---
SELECT SUM(rsize) FROM a WHERE id IN (1,2,3,4,5,6,7,8,..) [there is
LNG list]
This takes ~1500 ms to execute - it's ~7x faster than subselect, even there
is over 1500 'ORs'
Explain:
Aggregate (cost=11100.39..11100.39 rows=1 width=4)
Index Scan using B.id,B.id,B.id,B.id,B.id..
Index Cond: ((id = 560) OR (id = 561) OR (id = 562) OR (id = 563) OR (id =
741) OR ..)
The explain is shortcuted at place - both includes LONG list (repeating
the same basically).
'JOIN' style
SELECT SUM(rsize) FROM a,b WHERE a.key=b.id
This takes ~46 ms to execute
Explain:
Aggregate (cost=725.67..725.67 rows=1 width=12)
Hash Join (cost=29.22..722.03 rows=1458 width=12)
Hash Cond: ("outer".id = "inner".key)
Seq Scan on A (cost=0.00..649.72 rows=4972 width=8)
Hash (cost=25.58..25.58 rows=1458 width=4)
Seq Scan on B (cost=0.00..25.58 rows=1458 width=4)
All SQL produces same result: 96708362 (which is correct anyway :)
Please, can anyone expalin me (or point good article about it), why there
SOO big difference in execution times ? I would like to more understand how
internally PgSql 'executes' SQL...
Ofcourse, the best solution is JOIN, but isn't point. There is 200x speed
difference...Using generated "ID in (1,2,3...)" with 1500 terms is still 6x
faster rather subselect I really can't get it :)
I understand to output (which corespond with real times) - but don't have
clue, why it's soo differnet. I expect results to be 'similar'. Small
tables, 'fast' CPU. Nothing complicated in SQL's.
Thank you,
Best regards,
Lada 'Ray' Lostak
Unreal64 Develop group
http://www.orcave.com
http://www.unreal64.net
--
In the 1960s you needed the power of two C64s to get a rocket
to the moon. Now you need a machine which is a vast number
of times more powerful just to run the most popular GUI.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
