Re: [GENERAL] pg_dump fails
Lorenzo Thurman [EMAIL PROTECTED] writes: I'm trying that right now. I think there may be mis-match in the build settings between upgrades of postgresql. The USE settings may be at fault: - - pg-hier: Enables recursive queries like Oracle's 'CONNECT BY' feature. [ rolls eyes... ] Yup, that's Gentoo all right: throw in random patches that have been rejected by the upstream developers. Now that I think about it, this failure is exactly what that patch is known to cause, because it makes an incompatible change in Query structures and hence in on-disk view rule representation. I think these may have been changed since the original install. Go back to your prior setting, or even better stop using Gentoo's hacked-up version. I'm not sure why we even bother to answer support requests from Gentoo users, when what they are using is not our software but some randomly-modified variant. I wonder what other brokennesses Gentoo may be including ... (Just for the record: I work for Red Hat, which has a rather different notion of the level of reliability it wants to ship. So take my opinion with the appropriate grain of salt. But I'm a mite ticked off at the moment --- you're not the first person to have been bitten by this, and you likely won't be the last, and I think it's entirely because Gentoo has such a low quality standard for the patches they ship.) regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Unsubscribe
unsubscribe
Re: [GENERAL] current transaction is aborted, commands ignored until
On 19.04.2005 02:33 Kris Jurka wrote: Is this a problem with the JDBC interface that I'm using, or is this a general Postgres problem? This is an open todo item for the JDBC driver. It could be done by automatically wrapping all statements in savepoints behind the scenes. Then any error would rollback to the savepoint and you could continue on with your transaction. This option would only be used for compatibility which makes it a low priority for people who are mostly PostgreSQL users. Also, it'll certainly decrease performance. No problem. I can work around most of the areas where this can occur and I have control over the transaction handling. So it's not a big issue. It would have been nice though :) Thanks Thomas ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] Clarification
Hi, I am using postgreSQL 7.4.5 in Redhat Linux 9. Whenever i faced power failure , i am unable to stop the postgres. bash-2.05b$ pg_ctl stop /usr/local/pgsql/bin/pg_ctl: line 274: kill: (19859) - No such process waiting for postmaster to shut down failed pg_ctl: postmaster does not shut down Please help me regarding this. -- Warm Regards, S.ElayaRaja Mobile: (+91) 98450 59540 E-Mail: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] PostgreSQL as a filesystem
On Mon, 2005-04-18 at 17:18 -0400, Tom Lane wrote: Christopher Nelson [EMAIL PROTECTED] writes: I'm developing a hobby OS and I'm looking into file systems. I've thought about writing my own, and that appeals, but I'm also very interested in the database-as-a-filesystem paradigm. It would be nice to not have to write all of the stuff that goes into the DBMS (e.g. parsers, query schedulers, etc) myself. So I was wondering what sort of filesystem requirements Postgre has. There are DB's you could use for this, but Postgres (not Postgre, please, there is no such animal) isn't one of them :-(. We really assume we are sitting on top of a full-spec file system --- we want space management for variable-size files, robust storage of directory information, etc. I've been thinking of it, too. I think no filesystem out there is really optimized for a steady write load with many fsyncs, that is, is really transaction-oriented on the data side (journalled ones may implement real transactions for meta-data, but only for it). Out of curiosity, do you have any feedback from filesystem people, are they interested in optimizing for the kind of workload (expecially on write) a database generates? I ask for it seems to me it's a corner case to them, or even a degenerated one. I'm not aware of _any_ comparative benchmarch among different filesystems that is based on write+fsync load, for one. Using a DB as filesystem at OS level is a different matter, of course. Christopher, you may have a look at FUSE. http://fuse.sourceforge.net/ It may help in both developing a new filesystem and in understanding how it works under Linux (with a nice separation of userspace and kernelspace). I think you could even write one based on PostgreSQL, but it won't help much, since PostgreSQL needs a filesystem to work. But if your OS has TCP/IP, it could be interesting anyway. Note that I'm not aware of any other way to access PostgreSQL than sockets, so you need those at least. There's no standalone library you can link to in order to access database files, AFAIK. .TM. -- / / / / / / Marco Colombo ___/ ___ / / Technical Manager / / / ESI s.r.l. _/ _/ _/ [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] multibyte question
lately i download postgresql 8.0.2 (window version) from postgresql mirror website site. and i read the document, saying that postgresql support multibyte encoding (http://www.postgresql.org/docs/8.0/static/multibyte.html). but during installation, i can't find the item in the drop-down list, which indicates to the encoding - euc_tw, for instance. should i change to other version? or what should i do in order to create db to get encoding supported other than ascii? i apprecaite any suggestion, sincerely. jason Send instant messages to your online friends http://uk.messenger.yahoo.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] Simplified (a-la [G|N]DBM) DB access
Good day, excuse me, if my question is lame, but is it possible to get some simplified access to the PostgreSQL? What I mean is: currently to get/put/delete/edit any data I have to compose an SQL query, which should be parsed, compiled, optimized and so on. While, in some tasks simple interface a-la [G|N]DBM should be more than enough, but it will be more preferable to store all data in one database, which support concurrent access, transactions, etc. For example, let me have some textual data in PostgreSQL, and let me wish to have an inverted index for some statistical analyses purpose (for example, search, but without using tsearch2). For now, to make any operations with all that data, I have to use an SQL, which makes such thing really slow (take a look at sql-mode mnogosearch and others). Yes, I can store all that data outside the db, but I will have to reinvent all the features, which realized great in PostgreSQL (for example, recovery, transactions) and I will get harder administrative support, backup and so on. Thank you in advance, Regards, /Alexandre. ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Postgresql 8.0.2 SLES 9 rpm - libpq.so.3 broken
Anyone ran across a broken libpq.so.3 link from the SLES rpm, and if so found a solution to fix it? Regards, Paul PS, YaST2 conflicts list - generated 2005-04-19 04:22:00 freeradius 0.9.3-106.6 conflict Unresolved Requirements: freeradius requires libpq.so.3()(64bit) Conflict Resolution: ( ) Ignore Conflict and Risk System Inconsistencies libpq.so.3()(64bit) not available Required by: PyGreSQL requires libpq.so.3()(64bit) freeradius requires libpq.so.3()(64bit) pure-ftpd requires libpq.so.3()(64bit) samba-pdb requires libpq.so.3()(64bit) snort requires libpq.so.3()(64bit) Conflict Resolution: ( ) Remove All 5 Referring Packages Delete PyGreSQL Delete snort Delete samba-pdb Delete pure-ftpd Delete freeradius ( ) Ignore Conflict and Risk System Inconsistencies pure-ftpd 1.0.18-39.4 conflict Unresolved Requirements: pure-ftpd requires libpq.so.3()(64bit) Conflict Resolution: ( ) Ignore Conflict and Risk System Inconsistencies PyGreSQL 3.4-34.1 conflict Unresolved Requirements: PyGreSQL requires libpq.so.3()(64bit) Conflict Resolution: ( ) Ignore Conflict and Risk System Inconsistencies samba-pdb 3.0.9-2.6 conflict Unresolved Requirements: samba-pdb requires libpq.so.3()(64bit) Conflict Resolution: ( ) Ignore Conflict and Risk System Inconsistencies snort 2.1.1-37.6 conflict Unresolved Requirements: snort requires libpq.so.3()(64bit) Conflict Resolution: ( ) Ignore Conflict and Risk System Inconsistencies YaST2 conflicts list END ###
Re: [GENERAL] Indexes in PostgreSQL
Yes. Thanks. I ran VACUUM ANALYZE and got the same results... -Jack ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] pg_dump fails
Thanks for the reply. I've tried recompiling with my install build settings, but no luck. I've posted a message on the Gentoo forums. Hopefully they will have an answer. If they do, I'll post back here for future reference. On Apr 19, 2005, at 1:01 AM, Tom Lane wrote: Lorenzo Thurman [EMAIL PROTECTED] writes: I'm trying that right now. I think there may be mis-match in the build settings between upgrades of postgresql. The USE settings may be at fault: - - pg-hier: Enables recursive queries like Oracle's 'CONNECT BY' feature. [ rolls eyes... ] Yup, that's Gentoo all right: throw in random patches that have been rejected by the upstream developers. Now that I think about it, this failure is exactly what that patch is known to cause, because it makes an incompatible change in Query structures and hence in on-disk view rule representation. I think these may have been changed since the original install. Go back to your prior setting, or even better stop using Gentoo's hacked-up version. I'm not sure why we even bother to answer support requests from Gentoo users, when what they are using is not our software but some randomly-modified variant. I wonder what other brokennesses Gentoo may be including ... (Just for the record: I work for Red Hat, which has a rather different notion of the level of reliability it wants to ship. So take my opinion with the appropriate grain of salt. But I'm a mite ticked off at the moment --- you're not the first person to have been bitten by this, and you likely won't be the last, and I think it's entirely because Gentoo has such a low quality standard for the patches they ship.) regards, tom lane Tech/Library Combo Lab Manager Northwestern University Office Tech MG49 mailto:[EMAIL PROTECTED] voice: 847-467-6565 pager: 847-536-0094 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Postgresql 8.0.2 SLES 9 rpm - libpq.so.3 broken
R WARRINER wrote: Anyone ran across a broken libpq.so.3 link from the SLES rpm, and if so found a solution to fix it? Regards, Paul PS, YaST2 conflicts list - generated 2005-04-19 04:22:00 freeradius 0.9.3-106.6 conflict Unresolved Requirements: freeradius requires libpq.so.3()(64bit) Conflict Resolution: ( ) Ignore Conflict and Risk System Inconsistencies libpq.so.3()(64bit) not available Required by: PyGreSQL requires libpq.so.3()(64bit) freeradius requires libpq.so.3()(64bit) pure-ftpd requires libpq.so.3()(64bit) samba-pdb requires libpq.so.3()(64bit) snort requires libpq.so.3()(64bit) Conflict Resolution: ( ) Remove All 5 Referring Packages Delete PyGreSQL Delete snort Delete samba-pdb Delete pure-ftpd Delete freeradius ( ) Ignore Conflict and Risk System Inconsistencies pure-ftpd 1.0.18-39.4 conflict Unresolved Requirements: pure-ftpd requires libpq.so.3()(64bit) Conflict Resolution: ( ) Ignore Conflict and Risk System Inconsistencies PyGreSQL 3.4-34.1 conflict Unresolved Requirements: PyGreSQL requires libpq.so.3()(64bit) Conflict Resolution: ( ) Ignore Conflict and Risk System Inconsistencies samba-pdb 3.0.9-2.6 conflict Unresolved Requirements: samba-pdb requires libpq.so.3()(64bit) Conflict Resolution: ( ) Ignore Conflict and Risk System Inconsistencies snort 2.1.1-37.6 conflict Unresolved Requirements: snort requires libpq.so.3()(64bit) Conflict Resolution: ( ) Ignore Conflict and Risk System Inconsistencies YaST2 conflicts list END ### There's been a change on libpq from libpq.so.3 to libpq.so.4 you need to recompile all the pagackes that depend on libpq.so.3 so they can use the new libpq.so.4 Leonel Nunez ---(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] Postgresql 8.0.2 SLES 9 rpm - libpq.so.3 broken
Hi, On Tue, 19 Apr 2005, Leonel Nunez wrote: There's been a change on libpq from libpq.so.3 to libpq.so.4 you need to recompile all the pagackes that depend on libpq.so.3 so they can use the new libpq.so.4 Actually, no. Reinhard Max, the SuSE PostgreSQL RPM maintainer has just wrote that: Whoops, it seems I accidentally used a version of the spec file that didn't the libpq.so.3 workaround. I'll build and upload a fixed postgresql-libs package shortly... FYI -- Devrim GUNDUZ devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.tdmsoft.com http://www.gunduz.org ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Trigger Commandline Command from Postgresql
Hi List! What ist the best and easiest way to trigger a commandline command out from the database? We want to start a printjob. I think it could work with pl/tclu but i am not familar with that language. Thanks in Advance, Thomas ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Simplified (a-la [G|N]DBM) DB access
Alexandre [EMAIL PROTECTED] writes: What I mean is: currently to get/put/delete/edit any data I have to compose an SQL query, which should be parsed, compiled, optimized and so on. While, in some tasks simple interface a-la [G|N]DBM should be more than enough, but it will be more preferable to store all data in one database, which support concurrent access, transactions, etc. I seem to recall that someone has written an ISAM-style interface library, which might be more or less what you are asking for. Check the archives, and/or look at gborg and pgfoundry. 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
Re: [GENERAL] Clarification
ElayaRaja S [EMAIL PROTECTED] writes: bash-2.05b$ pg_ctl stop /usr/local/pgsql/bin/pg_ctl: line 274: kill: (19859) - No such process waiting for postmaster to shut down failed pg_ctl: postmaster does not shut down Is there actually a postmaster running? Is pg_ctl looking in the right data directory? The above could happen if you have a PGDATA environment setting that corresponds to a data directory that's not actively in use. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] substring and POSIX re's
Hi.
First: PG version 7.4 and 8.0.
I have a question regarding the following simplified query:
gds2=# select substring('NE NE SE 2310 FSL 330 FEL' from '^([A-Z][A-Z] )+');
substring
---
SE
(1 row)
The pg docs say that this form of substring uses POSIX re's, and my
understanding of POSIX re's is they are always greedy. So, why do I get
only SE instead of NE NE SE? Pilot error, probably, but would someone
please enlighten me? Thank you very much.
Don
p.s. The target string can have from 1 to 6 of the 2 char strings, not
just 3 as shown in this example.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Finding FOREIGN KEY constraints via information_schema
I'm trying to find out which columns of which tables reference which columns of which tables by querying the information_schema. I found the referencing columns in key_column_usage and the referenced columns in constraint_column_usage - fine so far. Now consider the following: CREATE TABLE t1 ( id1 INT NOT NULL, id2 INT NOT NULL, PRIMARY KEY (id1, id2) ); CREATE TABLE t2 ( id1 INT NOT NULL, id2 INT NOT NULL, CONSTRAINT t2_id_fk FOREIGN KEY (id1, id2) REFERENCES t1 (id1, id2) ); PostgreSQL groks that, and pg_dump correctly generates ALTER TABLE ONLY t2 ADD CONSTRAINT t2_id_fk FOREIGN KEY (id1, id2) REFERENCES t1(id1, id2); My problem is that, while key_column_usage knows the ordinal_position, constraint_column_usage doesn't. How can I find out that it's really REFERENCES t1(id1, id2) and not REFERENCES t1(id2, id1) instead? ---(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] substring and POSIX re's
Don Isgitt [EMAIL PROTECTED] writes:
gds2=# select substring('NE NE SE 2310 FSL 330 FEL' from '^([A-Z][A-Z] )+');
substring
---
SE
(1 row)
The pg docs say that this form of substring uses POSIX re's, and my
understanding of POSIX re's is they are always greedy. So, why do I get
only SE instead of NE NE SE? Pilot error, probably, but would someone
please enlighten me? Thank you very much.
I think you want
regression=# select substring('NE NE SE 2310 FSL 330 FEL' from '^(([A-Z][A-Z]
)+)');
substring
---
NE NE SE
(1 row)
ie, you need the + to be *inside* the capturing parentheses. When
it's outside, I guess the engine chooses to consider the last match
of the parenthesized subexpression as the thing to return. (I can't
recall if this choice is specified in the docs or not.)
regards, tom lane
---(end of broadcast)---
TIP 8: explain analyze is your friend
[GENERAL] Datatypes in PL/PSQL functions with multiple arguments
Hello- This is my first foray into pl/psql so forgive me if I sound totally incompetent. I've been writing a few functions, and have come across some screwing data typing issues. When creating a function which accepts a single argument, things work just fine, variable can be used throughout the function as expected with no modification. When creating functions containing two or more arguments, I have to explicity cast the arguments whenever I use them (loading/casting into another variable is an option, haven't tried though) to prevent runtime errors. The functions get called just fine, but then run into problems using any of the given arguments. Has anyone had any experience with this? Please advise! Thanks! Benjamin select version(); version PostgreSQL 7.4.7 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.1 (Mandrakelinux 10.1 3.4.1-4mdk)
Re: [GENERAL] Datatypes in PL/PSQL functions with multiple arguments
Benjamin Holmberg wrote: Hello- This is my first foray into pl/psql so forgive me if I sound totally incompetent. I've been writing a few functions, and have come across some screwing data typing issues. When creating a function which accepts a single argument, things work just fine, variable can be used throughout the function as expected with no modification. When creating functions containing two or more arguments, I have to explicity cast the arguments whenever I use them (loading/casting into another variable is an option, haven't tried though) to prevent runtime errors. The functions get called just fine, but then run into problems using any of the given arguments. Could you perhaps give an example function? Something with one or two lines of code perhaps. Oh, and how you are calling it too. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Datatypes in PL/PSQL functions with multiple arguments
On Tue, Apr 19, 2005 at 10:01:26AM -0500, Benjamin Holmberg wrote: When creating a function which accepts a single argument, things work just fine, variable can be used throughout the function as expected with no modification. When creating functions containing two or more arguments, I have to explicity cast the arguments whenever I use them (loading/casting into another variable is an option, haven't tried though) to prevent runtime errors. The functions get called just fine, but then run into problems using any of the given arguments. Please post an example of what you're doing: a simple function, how you're invoking it, and the error message(s). -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Datatypes in PL/PSQL functions with multiple arguments
This is one of the bad ones hacked up to work like it should...
I would call it like the following:
SELECT SIMPLE_date_used('5/11/06','5');
beginning_date and ending_date are date columns in MyTable. The
function is checking to see if given_date falls within a date range
that has already been established in another row, with the exclusion of
the row defined by arg_id.
==
CREATE FUNCTION SIMPLE_date_used (date,integer) RETURNS text AS '
DECLARE
given_date ALIAS for $1;
arg_id ALIAS for $2;
result boolean;
BEGIN
IF arg_production_schedule_id != 0 THEN
SELECT INTO result
((CAST(given_date AS date) = beginning_date) AND (CAST(given_date
AS date) = ending_date)) FROM MyTable WHERE CAST(given_date AS
date) = beginning_date) AND (CAST(given_date AS date) =
ending_date)) = TRUE) AND MyTable.arg_id != (CAST(arg_id AS integer)));
IF result = TRUE THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END;
' LANGUAGE 'plpgsql';
This is how I would think it should work changed (CAST(arg_id AS integer)) TO MyTable.arg_id != ''arg_id'':
CREATE FUNCTION SIMPLE_date_used (date,integer) RETURNS text AS '
DECLARE
given_date ALIAS for $1;
arg_id ALIAS for $2;
result boolean;
BEGIN
IF arg_production_schedule_id != 0 THEN
SELECT INTO result
((CAST(given_date AS date) = beginning_date) AND (CAST(given_date
AS date) = ending_date)) FROM MyTable WHERE CAST(given_date AS
date) = beginning_date) AND (CAST(given_date AS date) =
ending_date)) = TRUE) AND MyTable.arg_id != ''arg_id'');
IF result = TRUE THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END;
' LANGUAGE 'plpgsql';
This is the error message I'm getting by using: ''arg_id'' instead of: (CAST(arg_id AS integer))
SELECT production_scheduled_for_date('2005-05-12', '49');
ERROR: invalid input syntax for integer: arg_id
CONTEXT: PL/pgSQL function SIMPLE_date_used line 10 at select into variables
Any thoughts?On 4/19/05, Richard Huxton
dev@archonet.com wrote:
Benjamin Holmberg wrote: Hello- This is my first foray into pl/psql so forgive me if I sound totally incompetent. I've been writing a few functions, and have come across some screwing data
typing issues. When creating a function which accepts a single argument, things work just fine, variable can be used throughout the function as expected with no modification. When creating functions containing two or more arguments, I have to
explicity cast the arguments whenever I use them (loading/casting into another variable is an option, haven't tried though) to prevent runtime errors. The functions get called just fine, but then run into problems using
any of the given arguments.Could you perhaps give an example function? Something with one or twolines of code perhaps. Oh, and how you are calling it too.-- Richard Huxton Archonet Ltd
[GENERAL] What means Postgres?
Just for fun and interrest. What means Postgres? Where and why this name was born? Daniel ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] substring and POSIX re's
Tom Lane wrote:
Don Isgitt [EMAIL PROTECTED] writes:
gds2=# select substring('NE NE SE 2310 FSL 330 FEL' from '^([A-Z][A-Z] )+');
substring
---
SE
(1 row)
The pg docs say that this form of substring uses POSIX re's, and my
understanding of POSIX re's is they are always greedy. So, why do I get
only SE instead of NE NE SE? Pilot error, probably, but would someone
please enlighten me? Thank you very much.
I think you want
regression=# select substring('NE NE SE 2310 FSL 330 FEL' from '^(([A-Z][A-Z]
)+)');
substring
---
NE NE SE
(1 row)
ie, you need the + to be *inside* the capturing parentheses. When
it's outside, I guess the engine chooses to consider the last match
of the parenthesized subexpression as the thing to return. (I can't
recall if this choice is specified in the docs or not.)
regards, tom lane
Thanks, Tom. Interestingly enough, neither my original query or your
corrected one returns anything with pg 7.4--another good reason to
upgrade to 8.*
Don
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] What means Postgres?
Its a takeoff of ingres, the ORDBMS (Object-Relational Database Management System) postgres is based on...The origins date to 1977 at UC Berkeley. On 4/19/05, Daniel Schuchardt [EMAIL PROTECTED] wrote: Just for fun and interrest.What means Postgres? Where and why this name was born?Daniel---(end of broadcast)---TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] What means Postgres?
On Tue, Apr 19, 2005 at 05:24:22PM +0200, Daniel Schuchardt wrote: What means Postgres? Where and why this name was born? See A Brief History of PostgreSQL in the PostgreSQL documentation and some of the documents it links to: http://www.postgresql.org/docs/8.0/interactive/history.html According to The design of POSTGRES by Stonebreaker and Rowe, POSTGRES means POST inGRES (the successor to INGRES). Various other sources say that INGRES means INteractive Graphics (and) REtrieval System. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] What means Postgres?
Or, according to Babelfish, if Postgres is a Spanish word, it translates to poststoneware in English. Nonsense of course, but I thought it was funny. On 4/19/05, Michael Fuhr [EMAIL PROTECTED] wrote: On Tue, Apr 19, 2005 at 05:24:22PM +0200, Daniel Schuchardt wrote: What means Postgres? Where and why this name was born? See A Brief History of PostgreSQL in the PostgreSQL documentation and some of the documents it links to: http://www.postgresql.org/docs/8.0/interactive/history.html According to The design of POSTGRES by Stonebreaker and Rowe, POSTGRES means POST inGRES (the successor to INGRES). Various other sources say that INGRES means INteractive Graphics (and) REtrieval System. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Datatypes in PL/PSQL functions with multiple arguments
Don't forget to cc: the list...
Benjamin Holmberg wrote:
This is one of the bad ones...
I would call it like the following:
SELECT SIMPLE_date_used('5/11/06','5');
Well, you're trying to call it with two text-values here (or at least
two unknown values).
SELECT simple_date_used('5/11/06'::date, 5)
beginning_date and ending_date are date columns in MyTable. The function is
checking to see if given_date falls within a date range that has already
been established in another row, with the exclusion of the row defined by
arg_id.
==
CREATE FUNCTION SIMPLE_date_used (date,integer) RETURNS text AS '
DECLARE
given_date ALIAS for $1;
arg_id ALIAS for $2;
You've got a column called arg_id below, so it's best to call this
something else (p_arg_id or something). That stops both me and plpgsql
from getting confused :-)
result boolean;
BEGIN
IF arg_production_schedule_id != 0 THEN
SELECT INTO result ((CAST(given_date AS date) = beginning_date) AND
Now, these casts shouldn't be necessary. Are you saying you get errors
when you just use given_date = ending_date?
(CAST(given_date AS date) = ending_date)) FROM MyTable WHERE
CAST(given_date AS date) = beginning_date) AND (CAST(given_date AS
date) = ending_date)) = TRUE) AND MyTable.arg_id != (CAST(arg_id AS
integer)));
IF result = TRUE THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END;
' LANGUAGE 'plpgsql';
I've got to say I'd write the function more like:
SELECT INTO result true FROM MyTable
WHERE p_given_date = beginning_date AND p_given_date = ending_date
AND arg_id p_arg_id
RETURN FOUND;
The FOUND variable gets set when a query returns results.
--
Richard Huxton
Archonet Ltd
---(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] substring and POSIX re's
On Tue, Apr 19, 2005 at 11:17:46AM -0500, Don Isgitt wrote: Thanks, Tom. Interestingly enough, neither my original query or your corrected one returns anything with pg 7.4--another good reason to upgrade to 8.* Hmmm...for me both queries give the results shown if I run them in 7.3.9, 7.4.7, 8.0.2, or 8.1devel. What's different about your 7.4 installation? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(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] What means Postgres?
Michael Fuhr schrieb: According to The design of POSTGRES by Stonebreaker and Rowe, POSTGRES means POST inGRES (the successor to INGRES). Various other sources say that INGRES means INteractive Graphics (and) REtrieval System. Ah, this is what i searched. I read http://www.postgresql.org/docs/8.0/interactive/history.html but there it is only clear that Postgres is based in Ingres. But i also don't know what Ingres means. now I know ;-) Daniel ---(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] substring and POSIX re's
On Tue, Apr 19, 2005 at 10:03:45AM -0600, Michael Fuhr wrote: On Tue, Apr 19, 2005 at 11:17:46AM -0500, Don Isgitt wrote: Thanks, Tom. Interestingly enough, neither my original query or your corrected one returns anything with pg 7.4--another good reason to upgrade to 8.* Hmmm...for me both queries give the results shown if I run them in 7.3.9, 7.4.7, 8.0.2, or 8.1devel. What's different about your 7.4 installation? Maybe the regex_flavor setting? (not sure of the exact name) -- Alvaro Herrera ([EMAIL PROTECTED]) Maybe there's lots of data loss but the records of data loss are also lost. (Lincoln Yeoh) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] substring and POSIX re's
On Tue, Apr 19, 2005 at 12:39:52PM -0400, Alvaro Herrera wrote:
On Tue, Apr 19, 2005 at 10:03:45AM -0600, Michael Fuhr wrote:
On Tue, Apr 19, 2005 at 11:17:46AM -0500, Don Isgitt wrote:
Thanks, Tom. Interestingly enough, neither my original query or your
corrected one returns anything with pg 7.4--another good reason to
upgrade to 8.*
Hmmm...for me both queries give the results shown if I run them in
7.3.9, 7.4.7, 8.0.2, or 8.1devel. What's different about your 7.4
installation?
Maybe the regex_flavor setting? (not sure of the exact name)
Ah yes, I forgot about that
test= SET regex_flavor TO basic;
SET
test= SELECT substring('NE NE SE 2310 FSL 330 FEL' FROM '^(([A-Z][A-Z] )+)');
substring
---
(1 row)
test= SET regex_flavor TO advanced;
SET
test= SELECT substring('NE NE SE 2310 FSL 330 FEL' FROM '^(([A-Z][A-Z] )+)');
substring
---
NE NE SE
(1 row)
test= SELECT version();
version
---
PostgreSQL 7.4.7 on sparc-sun-solaris2.9, compiled by GCC gcc (GCC) 3.4.2
(1 row)
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] reorder table data
Hi, I have a 40GB database with a few tables containing approx 10 million rows. Most of the data in these tables is inactive and there is only a few rows which get used for our benchmark run each day. We cannot delete the inactive data since it might be required for a particular run some day while it is active data that is used mostly. Problem here is that the update and select queries are very slow becuase of this background data. I am looking for some way to reorganize the data in such a way that the active data gets accessed much faster as compared to inactive data. I am using btree indices which assumes that all the rows have the same probability. Is there an index or some other way to order this data so that active data can be accessed most efficiently (want to reduce the effect of background data as much as possible).?? Also, what is the best way to find out which rows are getting accessed in a table for a particular run?? Regards, Vinita Bansal _ Find,Compare,Buy Sell! http://adfarm.mediaplex.com/ad/ck/4686-26272-10936-265?ck=Register Do it all on eBay! ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] PostgreSQL as a filesystem
On Mon, 2005-04-18 at 17:18 -0400, Tom Lane wrote:
Christopher Nelson [EMAIL PROTECTED] writes:
I'm developing a hobby OS and I'm looking into file systems. I've
thought about writing my own, and that appeals, but I'm also very
interested in the database-as-a-filesystem paradigm. It would be
nice
to not have to write all of the stuff that goes into the DBMS
(e.g.
parsers, query schedulers, etc) myself.
So I was wondering what sort of filesystem requirements Postgre
has.
There are DB's you could use for this, but Postgres (not Postgre,
please, there is no such animal) isn't one of them :-(. We really
assume we are sitting on top of a full-spec file system --- we want
space management for variable-size files, robust storage of
directory
information, etc.
I've been thinking of it, too. I think no filesystem out there is
really
optimized for a steady write load with many fsyncs, that is, is really
transaction-oriented on the data side (journalled ones may implement
real transactions for meta-data, but only for it). Out of curiosity,
do you have any feedback from filesystem people, are they interested
in
optimizing for the kind of workload (expecially on write) a database
generates? I ask for it seems to me it's a corner case to them, or
even
a degenerated one. I'm not aware of _any_ comparative benchmarch among
different filesystems that is based on write+fsync load, for one.
I don't know of any filesystem people who have a desire to explicitly
support that sort of traffic. I have looked at the internals of systems
like BFS, and those journaled systems support transactions for all
data... not just metadata. For example, on BFS there is an area where
all data is journaled, then once it's been verified that the data
journaling is done, the log is rolled forward. XFS has an interesting
alternative. They do only journal metadata, but no filedata is
overwritten until the transaction succeeds. So what they do is write
the transaction metadata, allocate new storage for the block, write the
block, copy the extents map with the new block, commit the new extents
map, and then commit the metadata. So during all parts of the process,
up until the final commit of the metadata, two copies of everything
exist for that context.
Using a DB as filesystem at OS level is a different matter, of course.
Which is what I'm trying to accomplish.
Christopher, you may have a look at FUSE.
http://fuse.sourceforge.net/
Thanks for the link. It's not exactly what I'm looking for, since I'm
using the spoon microkernel and the file system is going to be a user
space agent in any case. But the information is interesting.
It may help in both developing a new filesystem and in understanding
how it works under Linux (with a nice separation of userspace and
kernelspace). I think you could even write one based on PostgreSQL,
but it won't help much, since PostgreSQL needs a filesystem to work.
But if your OS has TCP/IP, it could be interesting anyway.
Note that I'm not aware of any other way to access PostgreSQL than
sockets, so you need those at least. There's no standalone library
you can link to in order to access database files, AFAIK.
Hmm. So it would be a LOT of work to use it. Obviously I wouldn't be
using sockets, but I would be using an IPC primitive similar to sockets.
It would be relatively simple to create a basic filesystem abstraction
that kept track of large blocks of data, and nothing else. Then mount
the database layer on top of that.
I suppose it would make more sense to have both raw data streams and
associated relational object data. Streams for data performance, and
the relational data for information about the stream.
-={C}=-
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Datatypes in PL/PSQL functions with multiple arguments
Benjamin Holmberg wrote:
This is the error message I'm getting by using: ''arg_id'' instead of:
(CAST(arg_id
AS integer))
SELECT production_scheduled_for_date('2005-05-12', '49');
ERROR: invalid input syntax for integer: arg_id
That's because ''arg_id'' is the string value arg_id, those six
characters rather than the value of any variable.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Datatypes in PL/PSQL functions with multiple arguments
Then I guess I need to know how one can encapsulate variables in quotes, yet let the pl/pgsql interpreter interpolate.
In the case of my SELECT INTO, are the quotes even needed to avoid potential confusion with column names?On 4/19/05, Richard Huxton
dev@archonet.com wrote:Benjamin Holmberg wrote: This is the error message I'm getting by using: ''arg_id'' instead of:
(CAST(arg_id AS integer)) SELECT production_scheduled_for_date('2005-05-12', '49'); ERROR: invalid input syntax for integer: arg_idThat's because ''arg_id'' is the string value arg_id, those six
characters rather than the value of any variable.-- Richard Huxton Archonet Ltd
Re: [GENERAL] Help! Access is Denied Installation on WinXP rolled
Thanks for helping Joshua! While I do attempt to install PostgreSQL using my normal windows account (which of course has administrative rights), I've installed PostgreSQL before and never run into this problem. It appears that as long as you establish a different user name which isn't an administrator to run the service under then it should be ok. The problem with actually attempting to do the entire install under a non-administrative account is that naturally windows doesn't let it touch any system directories (i.e. the Windows directory - which PostgreSQL needs access to) and won't let it modify any permissions. Essentially, literally installing an application with a non-administrative account is nearly impossible because that's the whole purpose of having such things as non-administrative accounts. I'm probably preaching to the choir here. Sorry about that. Here's the contents of my init.db log if it helps... The files belonging to this database system will be owned by user Administrator. This user must also own the server process. The database cluster will be initialized with locale C. fixing permissions on existing directory C:/Program Files/PostgreSQL/8.0/data ... ok creating directory C:/Program Files/PostgreSQL/8.0/data/global ... ok creating directory C:/Program Files/PostgreSQL/8.0/data/pg_xlog ... ok creating directory C:/Program Files/PostgreSQL/8.0/data/pg_xlog/archive_status ... ok creating directory C:/Program Files/PostgreSQL/8.0/data/pg_clog ... ok creating directory C:/Program Files/PostgreSQL/8.0/data/pg_subtrans ... ok creating directory C:/Program Files/PostgreSQL/8.0/data/base ... ok creating directory C:/Program Files/PostgreSQL/8.0/data/base/1 ... ok creating directory C:/Program Files/PostgreSQL/8.0/data/pg_tblspc ... ok selecting default max_connections ... Access is denied. Access is denied. Access is denied. Access is denied. Access is denied. Access is denied. 10 selecting default shared_buffers ... Access is denied. Access is denied. Access is denied. Access is denied. Access is denied. Access is denied. Access is denied. Access is denied. Access is denied. Access is denied. Access is denied. 50 creating configuration files ... ok creating template1 database in C:/Program Files/PostgreSQL/8.0/data/base/1 ... ok initializing pg_shadow ... Access is denied. child process was terminated by signal 1 initdb: removing contents of data directory C:/Program Files/PostgreSQL/8.0/data Joshua D. Drake wrote: log file it tells me to check before it deletes it in the rollback it says repeatedly something about access is denied. Below is the actual install log (or a small portion of it, near the end where the error occurs). Any ideas? Regards, Abe p.s. this is a repost from my initial post on the novice list... which I've become aware is relatively inactive. Did you check the initdb.log that was referenced in your Windows error message? I do not know this to be your issue but I am guess that you tried to install PostgreSQL with Administrator (or as an Administrator) rights. PostgreSQL must be installed as a non Administrative user on Windows. Sincerely, Joshua D. Drake begin:vcard fn:Abe Burnett n:Burnett;Abe adr:;;4143 Rain Roper Drive;Bozeman;MT;59715;USA email;internet:[EMAIL PROTECTED] tel;home:1-406-582-0870 x-mozilla-html:TRUE version:2.1 end:vcard ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Simplified (a-la [G|N]DBM) DB access
Lane, thank you, but it is not: PostISAM generates SQL statements on the fly from traditional ISAM (read, write, start) statements, so it just add overhead, and is not what I'm looking for. Anyway, thank you for the information. Regards, /Alexandre. On Apr 19, 2005, at 18:16, Tom Lane wrote: Alexandre [EMAIL PROTECTED] writes: What I mean is: currently to get/put/delete/edit any data I have to compose an SQL query, which should be parsed, compiled, optimized and so on. While, in some tasks simple interface a-la [G|N]DBM should be more than enough, but it will be more preferable to store all data in one database, which support concurrent access, transactions, etc. I seem to recall that someone has written an ISAM-style interface library, which might be more or less what you are asking for. Check the archives, and/or look at gborg and pgfoundry. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] substring and POSIX re's
Michael Fuhr wrote:
On Tue, Apr 19, 2005 at 12:39:52PM -0400, Alvaro Herrera wrote:
On Tue, Apr 19, 2005 at 10:03:45AM -0600, Michael Fuhr wrote:
On Tue, Apr 19, 2005 at 11:17:46AM -0500, Don Isgitt wrote:
Thanks, Tom. Interestingly enough, neither my original query or your
corrected one returns anything with pg 7.4--another good reason to
upgrade to 8.*
Hmmm...for me both queries give the results shown if I run them in
7.3.9, 7.4.7, 8.0.2, or 8.1devel. What's different about your 7.4
installation?
Maybe the regex_flavor setting? (not sure of the exact name)
Ah yes, I forgot about that
test= SET regex_flavor TO basic;
SET
test= SELECT substring('NE NE SE 2310 FSL 330 FEL' FROM '^(([A-Z][A-Z] )+)');
substring
---
(1 row)
test= SET regex_flavor TO advanced;
SET
test= SELECT substring('NE NE SE 2310 FSL 330 FEL' FROM '^(([A-Z][A-Z] )+)');
substring
---
NE NE SE
(1 row)
test= SELECT version();
version
---
PostgreSQL 7.4.7 on sparc-sun-solaris2.9, compiled by GCC gcc (GCC) 3.4.2
(1 row)
Thank you, Alvaro and Michael,
The regex_flavor setting was the culprit; I never knew of such a
creature in pg! Mystery solved. The members on this board are great.
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] reorder table data
vinita bansal wrote: Hi, I have a 40GB database with a few tables containing approx 10 million rows. Most of the data in these tables is inactive and there is only a few rows which get used for our benchmark run each day. We cannot delete the inactive data since it might be required for a particular run some day while it is active data that is used mostly. Is there some pattern to which data is being accessed. For example, in an accounts system it might be rows with paid=false. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] reorder table data
Richard Huxton wrote: vinita bansal wrote: Hi, I have a 40GB database with a few tables containing approx 10 million rows. Most of the data in these tables is inactive and there is only a few rows which get used for our benchmark run each day. We cannot delete the inactive data since it might be required for a particular run some day while it is active data that is used mostly. Is there some pattern to which data is being accessed. For example, in an accounts system it might be rows with paid=false. If the above is the case you could create an expression index specifically for your clause. You could also archive out the old information into another schema and access it when required using UNIONS. Sincerely, Joshua D. Drake Command Prompt, Inc. -- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedication Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Simplified (a-la [G|N]DBM) DB access
Alexandre [EMAIL PROTECTED] writes: Lane, thank you, but it is not: PostISAM generates SQL statements on the fly from traditional ISAM (read, write, start) statements, so it just add overhead, and is not what I'm looking for. Well, if you don't want any SQL capability at all, I think you are looking for something more like Berkeley DB ... regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Help! Access is Denied Installation on WinXP rolled
On Tue, Apr 19, 2005 at 11:31:50AM -0600, Abe Burnett wrote: Essentially, literally installing an application with a non-administrative account is nearly impossible because that's the whole purpose of having such things as non-administrative accounts. I'm probably preaching to the choir here. Sorry about that. Here's the contents of my init.db log if it helps... [snip] selecting default max_connections ... Access is denied. Access is denied. Access is denied. Access is denied. Access is denied. Access is denied. 10 selecting default shared_buffers ... Access is denied. Access is denied. Access is denied. Access is denied. Access is denied. Access is denied. Access is denied. Access is denied. Access is denied. Access is denied. Access is denied. 50 Judging by the content of your log, I'd say the access that was denied has nothing to do with file or directory creation; it has to do with being able to set up shared memory. If the account you are using for installation doesn't have permissions to set even a minimally sized shared memory segment, there's no way Postgres can run. Note those settings are chosen not because they did work, but because they were the last setting that was tried. I think this is a bug: initdb should fail if not even the lowest setting can be used, instead of going ahead. -- Alvaro Herrera ([EMAIL PROTECTED]) Officer Krupke, what are we to do? Gee, officer Krupke, Krup you! (West Side Story, Gee, Officer Krupke) ---(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] Simplified (a-la [G|N]DBM) DB access
Have you actually seen any problem in simple get/put/delete/update situations? It's a shame when people spend a great deal of effort to invent a cure for a disease that does not exist. If you have problems with any of these things, there are usually good solutions. Using the copy API, you can insert like a raving madman. PostgreSQL has a prepared insert. Safe, transacted inserts will be hard to do much faster than that. You can also bracket bunches of operations in a single transaction if you like. In real life, how fast can you update records? If you have a titanic pile of users all trying to update, then the MVCC model is probably close to optimal anyway. Do you have some measurement that shows PostgreSQL is not performing up to a real business case time requirement? Sometimes, we can be guilty of 1980's batch oriented mind-set, if we have been doing data processing for a long time. The old paradigms no longer apply for the most part. Recommended reading: http://research.microsoft.com/research/pubs/view.aspx?msr_tr_id=MSR-TR-2 005-39 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Alexandre Sent: Tuesday, April 19, 2005 10:41 AM To: Tom Lane Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Simplified (a-la [G|N]DBM) DB access Lane, thank you, but it is not: PostISAM generates SQL statements on the fly from traditional ISAM (read, write, start) statements, so it just add overhead, and is not what I'm looking for. Anyway, thank you for the information. Regards, /Alexandre. On Apr 19, 2005, at 18:16, Tom Lane wrote: Alexandre [EMAIL PROTECTED] writes: What I mean is: currently to get/put/delete/edit any data I have to compose an SQL query, which should be parsed, compiled, optimized and so on. While, in some tasks simple interface a-la [G|N]DBM should be more than enough, but it will be more preferable to store all data in one database, which support concurrent access, transactions, etc. I seem to recall that someone has written an ISAM-style interface library, which might be more or less what you are asking for. Check the archives, and/or look at gborg and pgfoundry. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] What means Postgres?
Ironic too, if we think of the name Stonebreaker -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ian Harding Sent: Tuesday, April 19, 2005 9:02 AM To: Michael Fuhr Cc: Daniel Schuchardt; pgsql-general@postgresql.org Subject: Re: [GENERAL] What means Postgres? Or, according to Babelfish, if Postgres is a Spanish word, it translates to poststoneware in English. Nonsense of course, but I thought it was funny. On 4/19/05, Michael Fuhr [EMAIL PROTECTED] wrote: On Tue, Apr 19, 2005 at 05:24:22PM +0200, Daniel Schuchardt wrote: What means Postgres? Where and why this name was born? See A Brief History of PostgreSQL in the PostgreSQL documentation and some of the documents it links to: http://www.postgresql.org/docs/8.0/interactive/history.html According to The design of POSTGRES by Stonebreaker and Rowe, POSTGRES means POST inGRES (the successor to INGRES). Various other sources say that INGRES means INteractive Graphics (and) REtrieval System. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(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] Simplified (a-la [G|N]DBM) DB access
At 9:40 PM +0400 4/19/05, Alexandre wrote: Lane, thank you, but it is not: PostISAM generates SQL statements on the fly from traditional ISAM (read, write, start) statements, so it just add overhead, and is not what I'm looking for. Speaking from experience, as I have a system which hides Postgres behind an ISAM interface (though not PostISAM -- I rolled my own DB library) as part of a legacy 4GL migration, the overhead's ignorable. Dismissing it for that reason's not a good idea. On Apr 19, 2005, at 18:16, Tom Lane wrote: Alexandre [EMAIL PROTECTED] writes: What I mean is: currently to get/put/delete/edit any data I have to compose an SQL query, which should be parsed, compiled, optimized and so on. While, in some tasks simple interface a-la [G|N]DBM should be more than enough, but it will be more preferable to store all data in one database, which support concurrent access, transactions, etc. I seem to recall that someone has written an ISAM-style interface library, which might be more or less what you are asking for. Check the archives, and/or look at gborg and pgfoundry. -- Dan --it's like this--- Dan Sugalski even samurai [EMAIL PROTECTED] have teddy bears and even teddy bears get drunk ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] postgresql start/stop/status script
Hi,
I am working on making a postgresql/drbd/heartbeat high availability
cluster. I need a script for heartbeat to start, stop and query the
service. I wrote the following:
pgStart() {
su - pg0 -c cd data ; /mnt/data0/postgresql/bin/pg_ctl start -D
/mnt/data0/postgresql/data -w -o '-i -h 192.168.2.50'
}
pgStop () {
su - pg0 -c cd data ; /mnt/data$user/postgresql/bin/pg_ctl stop
-D /mnt/data0/postgresql/data -m fast -w
}
pgStatus () {
if su - pg0 -c cd data ; /mnt/data0/postgresql/bin/pg_ctl
status -D /mnt/data0/postgresql/data | grep -q postmaster is running
then
echo running
else
echo stopped
fi
}
This works fine. The only problem is that status - it seems to only
check for the existance of the PID file. If the file is there, it
assumes that postgresql is running. In the case of a failover, the PID
file will of course still be there, but it will be stale. The effect is
that heartbeat never starts postgresql because my pgStatus claims it is
already running, even though it is not.
Is there a better way to query the status of postgresql? I would expect
it to at least check that the process in the PID is actually running and
that it is a postgresql process.
I am also confused by the need to specify -h 192.168.2.50 - that is
already in the postgres.conf file, but pg_ctl start ignores it.
Thanks,
Baldur
---(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] pg_dump fails
On Tue, 19 Apr 2005 11:53 pm, Lorenzo Thurman wrote: Thanks for the reply. I've tried recompiling with my install build settings, but no luck. I've posted a message on the Gentoo forums. Hopefully they will have an answer. If they do, I'll post back here for future reference. I read your post in the forums. And as Tom suggested, it's going nothing to do with pg_dump, you need to remerge postgresql at the very least, and with some C and USE flags you understand. The Usual Gentoo causes come to mind first. USE flags set correctly? what are they? What are your GCC flags. I see a lot of gentoo users who just about turn on every compiler flag without actually knowing what they do, or how they effect things. Are your C_FLAGS conservative? I've been using Postgresql on gentoo for both 7.4, and 8.0 from beta to 8.0.2 with no problems. But then I always set my C_FLAGS to something conservative like CGLAGS=-march=i586 -mcpu=i586 -O2 -pipe yes, it may seems a Gentoo Conservative buy I don't get broken software. Always check extra patches applied to the default distribution if you ever have trouble to weed out problem. And never build with and USE flags you don't understand the implications of. Especially package specific ones. I understand Tom's frustration, as Redhat is in business and ships quality checked software, and Gentoo is run by a community group. Of which I think may of the packagers are not tied to the projects they are packaging. But I also think there is often fault with the Gentoo user attempting to bleed his system a little too much for speed, without considering the stability or even understand it. Regards Russell Smith. On Apr 19, 2005, at 1:01 AM, Tom Lane wrote: Lorenzo Thurman [EMAIL PROTECTED] writes: I'm trying that right now. I think there may be mis-match in the build settings between upgrades of postgresql. The USE settings may be at fault: - - pg-hier: Enables recursive queries like Oracle's 'CONNECT BY' feature. [ rolls eyes... ] Yup, that's Gentoo all right: throw in random patches that have been rejected by the upstream developers. Now that I think about it, this failure is exactly what that patch is known to cause, because it makes an incompatible change in Query structures and hence in on-disk view rule representation. I think these may have been changed since the original install. Go back to your prior setting, or even better stop using Gentoo's hacked-up version. I'm not sure why we even bother to answer support requests from Gentoo users, when what they are using is not our software but some randomly-modified variant. I wonder what other brokennesses Gentoo may be including ... (Just for the record: I work for Red Hat, which has a rather different notion of the level of reliability it wants to ship. So take my opinion with the appropriate grain of salt. But I'm a mite ticked off at the moment --- you're not the first person to have been bitten by this, and you likely won't be the last, and I think it's entirely because Gentoo has such a low quality standard for the patches they ship.) regards, tom lane Tech/Library Combo Lab Manager Northwestern University Office Tech MG49 mailto:[EMAIL PROTECTED] voice: 847-467-6565 pager: 847-536-0094 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] lots of puzzling log messages
I am Seeing twelve of these messages every five to ten seconds in the Postgresql serverlog when my java application is running: 2005-04-19 16:43:03 LOG: 0: duration: 0.246 ms LOCATION: exec_simple_query, postgres.c:960 2005-04-19 16:43:03 LOG: 0: statement: rollback; begin; LOCATION: pg_parse_query, postgres.c:464 postgresql.conf has: log_connections = true log_duration = true log_statement = true log_timestamp = true Running postgres 7.4.2. The Java application uses postgresql-7.4.1.jar. It is a custom developed application. I am wondering what these log messages mean. Any ideas? Dennis ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] pg_dump fails
What I was trying to do was export the database on one computer and import it onto another. I gave up trying to fix the export problem since I had an old backup of the database. It was old enough that it was short three tables, but I have the raw tab delimited data so I just reconstructed the database on this new machine. I've been running Gentoo for about a year and a half now, and in the early days, I did not fully understand all of the possible USE settings, but I've gotten more comfortable with it over time. As you probably know, once you've settled on what your USE settings should be, you can rebuild your system to reflect those new settings. I did that, and since everything appeared to be working OK, I assumed everything was OK, but obviously the damage to PostgreSQL was already done. Anyway, I think my settings now are pretty conservative and I know ot to play around with the Postgres USE flags. One of the reasons I'm migrating is to do a complete rebuild and apply what I've learned about Gentoo from scratch on a new computer. Here are my settings, as you asked. I don't think they're too out of line, but... On Apr 19, 2005, at 6:06 p, Russell Smith wrote: I read your post in the forums. And as Tom suggested, it's going nothing to do with pg_dump, you need to remerge postgresql at the very least, and with some C and USE flags you understand. The Usual Gentoo causes come to mind first. USE flags set correctly? what are they? USE=X -gnome -gtk -gtk2 cups -kde -qt What are your GCC flags. I see a lot of gentoo users who just about turn on every compiler flag without actually knowing what they do, or how they effect things. Are your C_FLAGS conservative? CFLAGS=-O2 -mtune=G3 -fno-strict-aliasing -pipe I've been using Postgresql on gentoo for both 7.4, and 8.0 from beta to 8.0.2 with no problems. But then I always set my C_FLAGS to something conservative like CGLAGS=-march=i586 -mcpu=i586 -O2 -pipe yes, it may seems a Gentoo Conservative buy I don't get broken software. Always check extra patches applied to the default distribution if you ever have trouble to weed out problem. And never build with and USE flags you don't understand the implications of. Especially package specific ones. I've always been a bit concerned about the patches myself. I understand Tom's frustration, as Redhat is in business and ships quality checked software, and Gentoo is run by a community group. Of which I think may of the packagers are not tied to the projects they are packaging. But I also think there is often fault with the Gentoo user attempting to bleed his system a little too much for speed, without considering the stability or even understand it. My Break-Dancing days are over, but there's always the Funky Chicken --The Full Monty
Re: [GENERAL] Idea for the statistics collector
Added to TODO list: * Log queries where the optimizer row estimates were dramatically different from the number of rows actually found (?) --- Doug Fields wrote: Tom Lane wrote: Bruce Momjian pgman@candle.pha.pa.us writes: Martijn van Oosterhout wrote: Firstly, I was only thinking of going for the basic nodes (Index Scan, Seq Scan, Distinct). Other types have far more variables. Secondly, even if you only count, it's useful. For example, if it tells you that the planner is off by a factor of 10 more than 75% of the time, that's useful information independant of what the actual variables are. And reduce the usefulness even more ;-). As a pure stats-gathering exercise it might be worth doing, but not if you only log the failure cases. How will you know how well you are doing if you take a biased-by-design sample? Personally, given that it seems like at least once or twice a day someone asks about performance or why isn't my index being used and other stuff - I think doing this would be a great idea. Perhaps not necessarily in the full-fledged way, but creating a sort of ANALYZE log, wherein it logs the optimizer's estimate of a query and the actual results of a query, for every query. This, of course, could be enableable/disableable on a per-connection basis, per-table basis (like OIDs), or whatever other basis makes life easiest to the developers. Then, when the next ANALYZE is run, it could do it's usual analysis, and apply some additional heuristics based upon what it learns from the ANALYZE log, possibly to do several things: 1) Automatically increase/decrease the SET STATISTICS information included in the analyze, for example, increasing it as a table grows larger and the randomness grows less than linearly with size (e.g., if you have 50 or 60 groups in a 1,000,000 row table, that certainly needs a higher SET STATISTICS and I do it on my tables). 2) Have an additional value on the statistics table called the index_heuristic or random_page_adjustment_heuristic which when 1 does nothing, but otherwise modifies the cost of using an index/seq scan by that factor - and don't ever change this more than a few percent each ANALYZE 3) Flags in a second log (maybe the regular log) really bad query estimates - let it do an analysis of the queries and flag anything two or three std deviations outside. Now, I suggest all this stuff in the name of usability and self-maintainability. Unfortunately, I don't have the wherewithal to actually assist in development. Another possibility is to put use_seq_scan default to OFF, or whatever the parameter is (I did my optimizing a while ago so it's fading), so that if there's an index, it will use it, regardless - as this seems to be what the great majority of people expect to happen. And/or add this to a FAQ, and let us all reply see http://.../indexfaq.html.; :) Cheers, Doug ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] reorder table data
Hi, There is no particular pattern but it is generally the update queries of the form update tableName set colName='something' that are taking a lot of time incase there is a lot of background data. Also, I would not like to change my application to access data from another schema when required. I want this to be handled at database level wherein everything in database itself is organised to make access faster. Regards, Vinita Bansal From: Joshua D. Drake [EMAIL PROTECTED] To: Richard Huxton dev@archonet.com CC: vinita bansal [EMAIL PROTECTED], pgsql-general@postgresql.org Subject: Re: [GENERAL] reorder table data Date: Tue, 19 Apr 2005 11:25:06 -0700 Richard Huxton wrote: vinita bansal wrote: Hi, I have a 40GB database with a few tables containing approx 10 million rows. Most of the data in these tables is inactive and there is only a few rows which get used for our benchmark run each day. We cannot delete the inactive data since it might be required for a particular run some day while it is active data that is used mostly. Is there some pattern to which data is being accessed. For example, in an accounts system it might be rows with paid=false. If the above is the case you could create an expression index specifically for your clause. You could also archive out the old information into another schema and access it when required using UNIONS. Sincerely, Joshua D. Drake Command Prompt, Inc. -- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedication Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ _ Find,Compare,Buy Sell! http://adfarm.mediaplex.com/ad/ck/4686-26272-10936-265?ck=Register Do it all on eBay! ---(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] multibyte question
On Apr 19, 2005, at 4:35 AM, neo anderson wrote: lately i download postgresql 8.0.2 (window version) from postgresql mirror website site. and i read the document, saying that postgresql support multibyte encoding (http://www.postgresql.org/docs/8.0/static/multibyte.html). but during installation, i can't find the item in the drop-down list, which indicates to the encoding - euc_tw, for instance. should i change to other version? or what should i do in order to create db to get encoding supported other than ascii? i apprecaite any suggestion, sincerely. jason I don't recall what the encoding options are using the installer, but you can create a database at any time with any of the supported encodings. See the ENCODING option with CREATE DATABASE: http://www.postgresql.org/docs/8.0/interactive/sql-createdatabase.html John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
