Re: [SQL] Slow update SQL
You can try this too:...WHERE
EXTRACT(YEAR FROM dxdate::Date) > 2000
Mauricio Fernández A.Ingeniero de
SistemasUniversidad Autónoma de Manizales (Colombia)
-Mensaje
original-De: [EMAIL PROTECTED][mailto:[EMAIL PROTECTED]]En
nombre de Bungsuputra LinanEnviado el: lunes, 27 febrero, 2006 3:42Para:
[EMAIL PROTECTED]; Postgres HelpAsunto: Re: [SQL] Slow update
SQLHi Ken,I used to have the same problem. In my computer,
using date_part in WHEREclause will always slow down the system when the
table has plenty of rows.My suggestion is try to change the query
to:... WHERE dxdate >= '2001-01-01';I hope this
helps.Regards,bungsu- Original Message -From:
Ken HillTo: Postgres HelpSent: Tuesday, February 14, 2006 8:48
AMSubject: [SQL] Slow update SQLI'm experiencing a very slow
query. The table contains 611,564 rows of data.I vaccumed the
table:VACUUM ANALYZE ncccr10;SELECT count(*) FROM
ncccr10;count611564(1 row)When I try to analyze
the query plan with:EXPLAIN ANALYZEUPDATE ncccr10SET key =
facilityno||'-'||lastname||'-'||sex||'-'||ssno||'-'||birthdate||'-'||primarysit||'-'||dxdate||'-'||morphology3WHERE
date_part('year',dxdate) > '2000';The query just never finishes (even
1 hour later). The colum key100 isindexed, and I'm setting the value of
thiscolumn from other columns. Why is this so
slow?---(end of
broadcast)---TIP 6: explain analyze is your
friend
Re: [SQL] how to add primary key to existing table with multiple primary keys
Your table has a primary key yet, so you can´t add a second primary key. You only can define one pk, however it can be composed as your table has (it now has 2 columns), so I think you need to drop the current pk and add again the new pk with your 3 columns (probeid, tissueid, expid). Review this http://www.postgresql.org/docs/8.1/interactive/ddl-constraints.html#AEN2038 Mauricio Fernández A.Ingeniero de SistemasUniversidad Autónoma de Manizales (Colombia) -Mensaje original-De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]En nombre de Daniel JooEnviado el: viernes, 24 febrero, 2006 2:45Para: pgsql-sql@postgresql.orgAsunto: [SQL] how to add primary key to existing table with multiple primary keys Hi there, I am trying to add another primary key to an existing table with two other primary keys. I got the following error when I tried this command: alter table extprobe2tissue ADD primary key (expid); ERROR: multiple primary keys for table "extprobe2tissue" are not allowed The details of the extprobe2tissue table is: \d extprobe2tissue; Table "public.extprobe2tissue" Column | Type | Modifiers -++--- probeid | integer | not null tissueid | integer | not null value | double precision | not null entrydate | date | not null description | character varying(200) | qval | double precision | expid | integer | not null Indexes: "extprobe2tissue_pkey" PRIMARY KEY, btree (probeid, tissueid) "extprobe2tissue_probeid" btree (probeid) "extprobe2tissue_tissueid" btree (tissueid) Foreign-key constraints: "extprobe2tissue_tissueid_fkey" FOREIGN KEY (tissueid) REFERENCES tissue(tissueid) "extprobe2tissue_probeid_fkey" FOREIGN KEY (probeid) REFERENCES extprobe(probeid) Any suggestion would be much appreciated. Thanks!
Re: [SQL] Index to support LIKE '%suffix' queries
* Alvaro Herrera: >> > Florian Weimer <[EMAIL PROTECTED]> writes: >> >> Is it possible to create an index to support queries of the form >> >> "column LIKE '%suffix'" (similar to an ordinary index for LIKE >> >> 'prefix%', which I also need)? > > It is possible to create a functional index on the reverse of the > string. Okay. Is there a predefined reverse function? I couldn't find one and I'm wondering if I just missed it. > Whether or not this beats tsearch2 is something you should investigate ... It's also possible that for this type of query, sequential scans are good enough. I forgot that they are quite fast. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Index to support LIKE '%suffix' queries
am 01.03.2006, um 8:19:40 +0100 mailte Florian Weimer folgendes: > * Alvaro Herrera: > > >> > Florian Weimer <[EMAIL PROTECTED]> writes: > >> >> Is it possible to create an index to support queries of the form > >> >> "column LIKE '%suffix'" (similar to an ordinary index for LIKE > >> >> 'prefix%', which I also need)? > > > > It is possible to create a functional index on the reverse of the > > string. > > Okay. Is there a predefined reverse function? I couldn't find one > and I'm wondering if I just missed it. simple to write one, http://a-kretschmer.de/diverses.shtml HTH, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47215, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Help with trigger that updates a row prior to a potentially aborted deletion?
Hi all, I have a situation where a DELETE operation may (correctly) fail due to a RESTRICT FK constraint. If so, I need to set a flag in the row indicating that it has been marked for deletion so that I can disregarded in subsequent queries. I'm trying to achieve this with a BEFORE DELETE trigger, which would set the 'marked_for_deletion' field to TRUE before attempting the delete proper. Then if the DELETE fails the row would still be tagged and I'd be happy. Problem is, when the DELETE operation fails with a RESTRICT VIOLATION error the entire operation is rolled back, including the BEFORE triggers, leaving me back where I started. Is there anyway to get the DELETE operation, or more specifically the FK constraint, to fail silently, i.e. to skip over the failed operation and not throw an exception? I'm really racking my brains on this one but not really getting anywhere! I have sample data defs to play with if that would be helpful. Thanks in advance! Simon Kinsella ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] regarding grant option
AKHILESH GUPTA wrote: > here i have to grant permissions to that user individually for each and > every table by using: > :->> grant ALL ON to ; > GRANT > and all the permissions are granted to that user for that particular table. Yes. If you are annoyed by having to type too many commands, you can write a little shell script to do it for you. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Help with trigger that updates a row prior to a potentially aborted deletion?
Hello Achilleus Thanks for your feedback. On changing the return to NULL: According to the docs, if I return NULL in the BEFORE trigger itself, all subsequent triggers and the row-level op itself (the actual delete) will be skipped completely, which is no good. I will confirm this to make sure though. On your suggestion of manually updating, I have been trying something like this with interesting (but undesirable!) results: CREATE OR REPLACE FUNCTION fn_trg_mark_ref_as_deleted() RETURNS TRIGGER AS $$ BEGIN UPDATE ref_table SET deleted = TRUE WHERE ref_id = OLD.ref_id; RETURN OLD; END; $$ LANGUAGE 'plpgsql' VOLATILE; CREATE TRIGGER mark_ref_as_deleted BEFORE DELETE ON ref_table FOR EACH ROW EXECUTE PROCEDURE fn_trg_mark_ref_as_deleted(); (I'm returning OLD for the reason above). Oddly, this does indeed set the soft-delete flag but never deletes the row, even if there are no constraint dependencies. I'm going to keep playing but any other suggestions would be very welcome :) Here are some sample schema and defs for anyone who's interested: -- SCHEMA DEFS: BEGIN; CREATE TABLE ref_table ( ref_id INTEGER NOT NULL, deleted BOOLEAN DEFAULT FALSE, CONSTRAINT ref_table_pkey PRIMARY KEY (ref_id) ); CREATE TABLE dep_table ( dep_id INTEGER NOT NULL, ref_id INTEGER NOT NULL, CONSTRAINT dep_table_pkey PRIMARY KEY (dep_id) ); ALTER TABLE dep_table ADD CONSTRAINT dep_table_depends_on_ref_table FOREIGN KEY (ref_id) REFERENCES ref_table (ref_id) MATCH FULL ON DELETE NO ACTION ON UPDATE CASCADE NOT DEFERRABLE; CREATE OR REPLACE FUNCTION fn_trg_mark_ref_as_deleted() RETURNS TRIGGER AS $$ BEGIN UPDATE ref_table SET deleted = TRUE WHERE ref_id = OLD.ref_id; RETURN OLD; END; $$ LANGUAGE 'plpgsql' VOLATILE; CREATE TRIGGER mark_ref_as_deleted BEFORE DELETE ON ref_table FOR EACH ROW EXECUTE PROCEDURE fn_trg_mark_ref_as_deleted(); COMMIT; -- SAMPLE DATA: BEGIN; DELETE FROM dep_table; DELETE FROM ref_table; INSERT INTO ref_table (ref_id) VALUES (1); INSERT INTO ref_table (ref_id) VALUES (2); INSERT INTO ref_table (ref_id) VALUES (3); INSERT INTO ref_table (ref_id) VALUES (4); INSERT INTO ref_table (ref_id) VALUES (5); INSERT INTO dep_table (dep_id,ref_id) VALUES (100,1); INSERT INTO dep_table (dep_id,ref_id) VALUES (101,1); INSERT INTO dep_table (dep_id,ref_id) VALUES (102,2); INSERT INTO dep_table (dep_id,ref_id) VALUES (103,2); INSERT INTO dep_table (dep_id,ref_id) VALUES (104,3); COMMIT; -- SAMPLE QUERIES (which don't do what I would like!): DELETE FROM ref_table WHERE ref_id = 1 -- Ideally should sets the 'deleted' flag and not remove the row. (works OK) DELETE FROM ref_table WHERE ref_id = 5 -- Ideally should remove the row completely. (Does not work OK) Thanks all, Simon Kinsella ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Help with trigger that updates a row prior to a potentially
On Wed, 1 Mar 2006, Simon Kinsella wrote: > Hi all, > > I have a situation where a DELETE operation may (correctly) fail due to a > RESTRICT FK constraint. If so, I need to set a flag in the row indicating > that it has been marked for deletion so that I can disregarded in subsequent > queries. > > I'm trying to achieve this with a BEFORE DELETE trigger, which would set the > 'marked_for_deletion' field to TRUE before attempting the delete proper. > Then if the DELETE fails the row would still be tagged and I'd be happy. > Problem is, when the DELETE operation fails with a RESTRICT VIOLATION error > the entire operation is rolled back, including the BEFORE triggers, leaving > me back where I started. > > Is there anyway to get the DELETE operation, or more specifically the FK > constraint, to fail silently, i.e. to skip over the failed operation and not > throw an exception? Not with the standard constraint trigger, no. In general, constraint checks happen after the action and as such can't skip over an operation since it's already happened. You might be able to do this within a function however if you do the update and then start an exeption checking block to do the delete. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] regarding grant option
thank you very much sir for your valuable suggestion,but i am talking about direct database query...!On 3/1/06, Alvaro Herrera < [EMAIL PROTECTED]> wrote:AKHILESH GUPTA wrote:> here i have to grant permissions to that user individually for each and > every table by using:> :->> grant ALL ON to ;> GRANT> and all the permissions are granted to that user for that particular table.Yes. If you are annoyed by having to type too many commands, you can write a little shell script to do it for you.--Alvaro Herrerahttp://www.CommandPrompt.com/PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Thanks & Regards,AkhileshDAV Institute of ManagementFaridabad(Haryana)GSM:-(+919891606064) (+911744293789)"FAILURES CAN BE FORGIVEN BUT AIMING LOW IS A CRIME"
Re: [SQL] Help with trigger that updates a row prior to a potentially aborted deletion?
Yes I originally started working on a function based approach like you suggest, but realised it wouldn't cover the situation where the delete operation is fired as a result of a CASCADE ON DELETE constraint from a parent table, rather than as a manual query. I suppose I could ditch that particular cascading contraint and replace it with a trigger function that calls my custom delete function. Not sure if I like that though ;) Thanks for your feedback, Simon Kinsella -Original Message- From: Stephan Szabo [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 01, 2006 2:31 PM To: Simon Kinsella Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Help with trigger that updates a row prior to a potentially aborted deletion? On Wed, 1 Mar 2006, Simon Kinsella wrote: > Hi all, > > I have a situation where a DELETE operation may (correctly) fail due > to a RESTRICT FK constraint. If so, I need to set a flag in the row > indicating that it has been marked for deletion so that I can > disregarded in subsequent queries. > > I'm trying to achieve this with a BEFORE DELETE trigger, which would > set the 'marked_for_deletion' field to TRUE before attempting the delete proper. > Then if the DELETE fails the row would still be tagged and I'd be happy. > Problem is, when the DELETE operation fails with a RESTRICT VIOLATION > error the entire operation is rolled back, including the BEFORE > triggers, leaving me back where I started. > > Is there anyway to get the DELETE operation, or more specifically the > FK constraint, to fail silently, i.e. to skip over the failed > operation and not throw an exception? Not with the standard constraint trigger, no. In general, constraint checks happen after the action and as such can't skip over an operation since it's already happened. You might be able to do this within a function however if you do the update and then start an exeption checking block to do the delete. ---(end of broadcast)--- TIP 1: 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: [SQL] regarding grant option
AKHILESH GUPTA wrote: > thank you very much sir for your valuable suggestion, > but i am talking about direct database query...! There is none that can help you here, short of making a function in PL/pgSQL or other language ... > On 3/1/06, Alvaro Herrera <[EMAIL PROTECTED]> wrote: > > > > AKHILESH GUPTA wrote: > > > > > here i have to grant permissions to that user individually for each and > > > every table by using: > > > :->> grant ALL ON to ; > > > GRANT > > > and all the permissions are granted to that user for that particular > > table. > > > > Yes. If you are annoyed by having to type too many commands, you can > > write a little shell script to do it for you. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] grant select,... over schema
On Tue, Feb 28, 2006 at 09:43:58 -0700, Michael James <[EMAIL PROTECTED]> wrote: > GRANT ALL PRIVILEGES ON databaseName To username; This won't do what he wants. This will not grant access to objects contained in the database, only a few specific privileges. There is no single command that does this, but it isn't hard to write scripts to do things like this. For each object type you want to grant access to, you can query the catalogs to get a list of those objects and then generate appropiate grant statements and execute them. > > Michael Lee James | Database Administrator > d. 480-282-6043| c. 480-505-5802| f. 480-505-5801 > [EMAIL PROTECTED] > iCrossing, Inc. | Driving the Future of Advertising > New York | Chicago | Scottsdale | San Francisco > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of chester c young > Sent: Tuesday, February 28, 2006 9:35 AM > To: pgsql-sql@postgresql.org > Subject: [SQL] grant select,... over schema > > is there any way to grant over all applicable objects in a schema, > > > grant select on schema pop to public; <-- wrong > > without specifically granting the select on each table? > > __ > Do You Yahoo!? > Tired of spam? Yahoo! Mail has the best spam protection around > http://mail.yahoo.com > > ---(end of broadcast)--- > TIP 9: In versions below 8.0, the planner will ignore your desire to >choose an index scan if your joining column's datatypes do not >match > > iCrossing Privileged and Confidential Information > This email message is for the sole use of the intended recipient(s) and may > contain confidential and privileged information of iCrossing. Any > unauthorized review, use, disclosure or distribution is prohibited. If you > are not the intended recipient, please contact the sender by reply email and > destroy all copies of the original message. > > > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Replication - state of the art?
I'm interested in creating a mirror database, for use in case one our primary machine goes down. Can people here help sort out which of the several replication projects is most viable? As far as I can tell, the winner is slony1 at http://gborg.postgresql.org/project/slony1/projdisplay.php , but there are several contenders. -- The needs here are very relaxed. We have one main postmaster which runs 24x7x365. There's another machine nearby that can accept a log or journal of some sort. The alternate never needs DB access, except in case of main machine failure, and then we can take up to 15 minutes to switch over and rebuild the DB. "No-lost transaction" is far more important than switch time. Anyone here using replication or transaction journaling? Has it proved reliable, easy to maintain? Bryce Nesbitt ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Help with trigger that updates a row prior to a potentially aborted deletion?
On Wed, Mar 01, 2006 at 01:30:23PM -, Simon Kinsella wrote: > I'm trying to achieve this with a BEFORE DELETE trigger, which would set the > 'marked_for_deletion' field to TRUE before attempting the delete proper. > Then if the DELETE fails the row would still be tagged and I'd be happy. > Problem is, when the DELETE operation fails with a RESTRICT VIOLATION error > the entire operation is rolled back, including the BEFORE triggers, leaving > me back where I started. Yes. In 8.1, you could use a subtransaction for the DELETE, which I think would allow you to rollback at that point and still leave the UPDATE in place. A -- Andrew Sullivan | [EMAIL PROTECTED] The fact that technology doesn't work is no bar to success in the marketplace. --Philip Greenspun ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Replication - state of the art?
On Wed, Mar 01, 2006 at 09:51:46AM -0800, Bryce Nesbitt wrote: > switch over and rebuild the DB. "No-lost transaction" is far more > important than switch time. You can't guarantee that without two phase commit, no matter what you do. Log shipping doesn't require you to have an active database running on the origin (slony-1 does, which is one of its potential drawbacks). But that won't help you if a transaction committed at the instant an earthquake hit your datacentre, wiping it out. You can't get the data off the failed origin no matter what. > Anyone here using replication or transaction journaling? Has it proved > reliable, easy to maintain? Define "easy". Every possible replication system is going to have slightly grotty corners into which you find yourself wandering. The question is merely whether the room is octagonal or merely rectangular. A -- Andrew Sullivan | [EMAIL PROTECTED] I remember when computers were frustrating because they *did* exactly what you told them to. That actually seems sort of quaint now. --J.D. Baldwin ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Change date format through an environmental variable?
I want to get Pg (v7.4.7) to output a date field in a different format than -mm-dd through the use of an environmental variable (because I have no access the SQL). Is this possible? I know about the DATESTYLE variable, but that seems to work only within a query transaction, and has no effect if trying to set it as an envvar. Mark ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Change date format through an environmental variable?
On Wed, Mar 01, 2006 at 12:32:26PM -0500, Mark Fenbers wrote: > have no access the SQL). Is this possible? I know about the DATESTYLE > variable, but that seems to work only within a query transaction, and > has no effect if trying to set it as an envvar. No, it won't work as an environment variable. You can alter the postgresql.conf file, though. A -- Andrew Sullivan | [EMAIL PROTECTED] When my information changes, I alter my conclusions. What do you do sir? --attr. John Maynard Keynes ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Change date format through an environmental variable?
I found PGDATESTYLE that solves my problem, but ever since, I've been looking for a comprehensive list of environmental variables that Pg recognizes, but haven't been able to find such a list in any of the books I looked in or the man pages. Anyone know where I can find such a list? Mark Mark Fenbers wrote: I want to get Pg (v7.4.7) to output a date field in a different format than -mm-dd through the use of an environmental variable (because I have no access the SQL). Is this possible? I know about the DATESTYLE variable, but that seems to work only within a query transaction, and has no effect if trying to set it as an envvar. Mark ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Change date format through an environmental variable?
On Wed, Mar 01, 2006 at 02:20:57PM -0500, Mark Fenbers wrote: > I found PGDATESTYLE that solves my problem, but ever since, I've been > looking for a comprehensive list of environmental variables that Pg > recognizes, but haven't been able to find such a list in any of the > books I looked in or the man pages. Anyone know where I can find such a > list? That's a client variable, and it works for libpq-based clients that don't do something funny with them (none of them ought to, but one can't guarantee others' programs). So you're not modifying for other clients, AFAIK, just for you. (If that's what you want, well, good, but it's important to know what it does.) The list for 8.1 is in the docs: http://www.postgresql.org/docs/8.1/interactive/libpq-envars.html A -- Andrew Sullivan | [EMAIL PROTECTED] A certain description of men are for getting out of debt, yet are against all taxes for raising money to pay it off. --Alexander Hamilton ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Replication - state of the art?
Andrew Sullivan wrote: > On Wed, Mar 01, 2006 at 09:51:46AM -0800, Bryce Nesbitt wrote: > >> switch over and rebuild the DB. "No-lost transaction" is far more >> important than switch time. >> > > You can't guarantee that without two phase commit, no matter what you > do. Log shipping doesn't require you to have an active database > running on the origin (slony-1 does, which is one of its potential > drawbacks). But that won't help you if a transaction committed at > the instant an earthquake hit your datacentre, wiping it out. You > can't get the data off the failed origin no matter what. > Actually let me loosen that a bit: we don't need two phase commit. We can loose the most recent transaction, or even the last few seconds of transactions. What we can't survive is -- on the day of the emergency -- a long and complicated DB rebuild with mistakes and hard-to-debug data issues. >> Anyone here using replication or transaction journaling? Has it proved >> reliable, easy to maintain? >> > > Define "easy". Every possible replication system is going to have > slightly grotty corners into which you find yourself wandering. The > question is merely whether the room is octagonal or merely > rectangular. > There's no fire creating demand for replication, so there is little time budget. So is there a sort of padded, no-sharp-corners, playroom that gets us 90% of the way there? We're looking to reduce what's now a 24 hour window on data loss (since the most recent nightly) into something more reasonable (like 500 milliseconds). But risk -- of data corruption -- and time --too much-- will can the project. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Change date format through an environmental variable?
On Wed, Mar 01, 2006 at 02:20:57PM -0500, Mark Fenbers wrote: > I found PGDATESTYLE that solves my problem, but ever since, I've been > looking for a comprehensive list of environmental variables that Pg > recognizes, but haven't been able to find such a list in any of the > books I looked in or the man pages. Anyone know where I can find such a > list? The libpq documentation has a list of environment variables, although it's not complete: http://www.postgresql.org/docs/8.1/interactive/libpq-envars.html -- Michael Fuhr ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Replication - state of the art?
On Wed, Mar 01, 2006 at 11:28:06AM -0800, Bryce Nesbitt wrote: > Actually let me loosen that a bit: we don't need two phase commit. We > can loose the most recent transaction, or even the last few seconds of > transactions. What we can't survive is -- on the day of the emergency > -- a long and complicated DB rebuild with mistakes and hard-to-debug > data issues. Then I suggest you use Slony-I. While it is not plug and play, the thing it _is_ designed to handle reasonably well is failover and (better) switchover. Most systems plan to solve that piece of functionality later, with a script or something, at which point it is apparent that setting up failover or swichover to be anything approaching safe is actually very tricky. (Log shipping is probably not in this category, but AFAIK the promote-to-live support for a standby database copy is still not all built by anyone. If you like rolling your own, however, it might be your answer.) > There's no fire creating demand for replication, so there is little time > budget. > So is there a sort of padded, no-sharp-corners, playroom that gets us > 90% of the way there? The "no budget" remark here is what makes me strike CMD's Mammoth Replicator off the list. But I'm sure their administration tools are far sweeter than the admittedly hackish ones that Slony currently delivers out of the box. > nightly) into something more reasonable (like 500 milliseconds). But > risk -- of data corruption -- > and time --too much-- will can the project. Another big reason to use a live-standby system like Slony is that once you have the extra database online, you suddenly think of all sorts of nifty queries you can move there without destroying your production performance. Be careful not to get addicted, is all. A -- Andrew Sullivan | [EMAIL PROTECTED] Information security isn't a technological problem. It's an economics problem. --Bruce Schneier ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Replication - state of the art?
On Wed, 2006-03-01 at 11:51, Bryce Nesbitt wrote: > I'm interested in creating a mirror database, for use in case one our > primary machine goes down. Can people here help sort out which of the > several replication projects is most viable? > > As far as I can tell, the winner is slony1 at > http://gborg.postgresql.org/project/slony1/projdisplay.php , but there > are several contenders. > >-- > > The needs here are very relaxed. We have one main postmaster which runs > 24x7x365. There's another machine nearby that can accept a log or > journal of some sort. The alternate never needs DB access, except in > case of main machine failure, and then we can take up to 15 minutes to > switch over and rebuild the DB. "No-lost transaction" is far more > important than switch time. > > Anyone here using replication or transaction journaling? Has it proved > reliable, easy to maintain? You might want to look at pgpool in mirror replication mode as well. It's got some limitations due to it's query shipping nature, but may give you what you need. It knows how to switch off from the dead server and keep running on the one good one. It's solid software though. Mammoth may be a better option for you. It's not that horribly expensive, and setup is supposed to be a snap. If you use slony, you might want to look at frontending it with pgpool which makes switching the servers around a little easier, as you can do it in pgpool instead of your app layer. Lots of choices. Hard to say which is right. I really like slony, and use it at work. I'm quite happy with it. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Interval subtracting
Stephan Szabo wrote: > > justify_days doesn't currently do anything with this result --- it > > thinks its charter is only to reduce day components that are >= 30 days. > > However, I think a good case could be made that it should normalize > > negative days too; that is, the invariant on its result should be > > 0 <= days < 30, not merely days < 30. > > What about cases like interval '1 month -99 days', should that turn into > interval '-3 mons +21 days' or '-2 mons -9 days'? I think it should be the later. It is best to have a single sign, and I think it is possible in all cases: '2 mons -1 days' could be adjusted to '1 mons 29 days'. -- Bruce Momjian http://candle.pha.pa.us SRA OSS, Inc. http://www.sraoss.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Interval subtracting
On Wed, 2006-03-01 at 14:18, Bruce Momjian wrote: > Stephan Szabo wrote: > > > justify_days doesn't currently do anything with this result --- it > > > thinks its charter is only to reduce day components that are >= 30 days. > > > However, I think a good case could be made that it should normalize > > > negative days too; that is, the invariant on its result should be > > > 0 <= days < 30, not merely days < 30. > > > > What about cases like interval '1 month -99 days', should that turn into > > interval '-3 mons +21 days' or '-2 mons -9 days'? > > I think it should be the later. It is best to have a single sign, and I > think it is possible in all cases: > > '2 mons -1 days' > > could be adjusted to '1 mons 29 days'. There's a part of me that thinks the WHOLE THING should be positive or negative: -(2 months 1 day) ---(end of broadcast)--- TIP 1: 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: [HACKERS] [SQL] Interval subtracting
Scott Marlowe wrote: > On Wed, 2006-03-01 at 14:18, Bruce Momjian wrote: > > Stephan Szabo wrote: > > > > justify_days doesn't currently do anything with this result --- it > > > > thinks its charter is only to reduce day components that are >= 30 days. > > > > However, I think a good case could be made that it should normalize > > > > negative days too; that is, the invariant on its result should be > > > > 0 <= days < 30, not merely days < 30. > > > > > > What about cases like interval '1 month -99 days', should that turn into > > > interval '-3 mons +21 days' or '-2 mons -9 days'? > > > > I think it should be the later. It is best to have a single sign, and I > > think it is possible in all cases: > > > > '2 mons -1 days' > > > > could be adjusted to '1 mons 29 days'. > > There's a part of me that thinks the WHOLE THING should be positive or > negative: > > -(2 months 1 day) But it isn't '-2 months, -1 day'. I think what you are saying is what I am saying, that we should make the signs consistent. -- Bruce Momjian http://candle.pha.pa.us SRA OSS, Inc. http://www.sraoss.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 1: 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: [HACKERS] [SQL] Interval subtracting
On Wed, 1 Mar 2006, Hannu Krosing wrote: > Ühel kenal päeval, K, 2006-03-01 kell 15:18, kirjutas Bruce Momjian: > > Stephan Szabo wrote: > > > > justify_days doesn't currently do anything with this result --- it > > > > thinks its charter is only to reduce day components that are >= 30 days. > > > > However, I think a good case could be made that it should normalize > > > > negative days too; that is, the invariant on its result should be > > > > 0 <= days < 30, not merely days < 30. > > > > > > What about cases like interval '1 month -99 days', should that turn into > > > interval '-3 mons +21 days' or '-2 mons -9 days'? > > > > I think it should be the later. It is best to have a single sign, and I > > think it is possible in all cases: > > > > '2 mons -1 days' > > > > could be adjusted to '1 mons 29 days'. > > But unfortunately '2 mons -1 days' <> '1 mons 29 days' > > If I want something to happen 1 day less than two months from dome date, > then the only way to say that consistently *is* '2 mons -1 days'. Right, but would you call justify_days on such an interval? '2 months -1 days' <> '1 mon 29 days', but '1 mon 60 days' is also <> '3 mons' in general usage. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [SQL] Interval subtracting
Hannu Krosing wrote: > ?hel kenal p?eval, K, 2006-03-01 kell 15:18, kirjutas Bruce Momjian: > > Stephan Szabo wrote: > > > > justify_days doesn't currently do anything with this result --- it > > > > thinks its charter is only to reduce day components that are >= 30 days. > > > > However, I think a good case could be made that it should normalize > > > > negative days too; that is, the invariant on its result should be > > > > 0 <= days < 30, not merely days < 30. > > > > > > What about cases like interval '1 month -99 days', should that turn into > > > interval '-3 mons +21 days' or '-2 mons -9 days'? > > > > I think it should be the later. It is best to have a single sign, and I > > think it is possible in all cases: > > > > '2 mons -1 days' > > > > could be adjusted to '1 mons 29 days'. > > But unfortunately '2 mons -1 days' <> '1 mons 29 days' > > If I want something to happen 1 day less than two months from dome date, > then the only way to say that consistently *is* '2 mons -1 days'. Right, but you asked to justify the days by calling the function. -- Bruce Momjian http://candle.pha.pa.us SRA OSS, Inc. http://www.sraoss.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [SQL] Interval subtracting
Hannu Krosing <[EMAIL PROTECTED]> writes: > But unfortunately '2 mons -1 days' <> '1 mons 29 days' > If I want something to happen 1 day less than two months from dome date, > then the only way to say that consistently *is* '2 mons -1 days'. Sure, but if you want to represent that then you don't pass the value through justify_days(). The entire premise of justify_days() is that 1 month is interchangeable with 30 days and we should try to make the value "look nice" given that assumption. I think everyone's independently arrived at the same thought that justify_days should not produce a result with different signs for month and day (except for the case with month = 0, per my last message). regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Help with trigger that updates a row prior to a potentially aborted deletion?
Ok thanks, will check this out. Is that the same as savepoints, or something different? (am using 8.1.2) At the moment I'm investigating using a rule (rewrite the DELETE as an UPDATE to set the flag, then use an AFTER UPDATE trigger to attempt to delete the row if the flag was set). Not sure if it's going to work but if so I'll post back. Thanks! Simon -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Sullivan Sent: Wednesday, March 01, 2006 6:24 PM To: pgsql-sql@postgresql.org Subject: Re: [SQL] Help with trigger that updates a row prior to a potentially aborted deletion? On Wed, Mar 01, 2006 at 01:30:23PM -, Simon Kinsella wrote: > I'm trying to achieve this with a BEFORE DELETE trigger, which would > set the 'marked_for_deletion' field to TRUE before attempting the delete proper. > Then if the DELETE fails the row would still be tagged and I'd be happy. > Problem is, when the DELETE operation fails with a RESTRICT VIOLATION > error the entire operation is rolled back, including the BEFORE > triggers, leaving me back where I started. Yes. In 8.1, you could use a subtransaction for the DELETE, which I think would allow you to rollback at that point and still leave the UPDATE in place. A -- Andrew Sullivan | [EMAIL PROTECTED] The fact that technology doesn't work is no bar to success in the marketplace. --Philip Greenspun ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [SQL] Interval subtracting
On Wed, 2006-03-01 at 14:27, Bruce Momjian wrote: > Scott Marlowe wrote: > > On Wed, 2006-03-01 at 14:18, Bruce Momjian wrote: > > > Stephan Szabo wrote: > > > > > justify_days doesn't currently do anything with this result --- it > > > > > thinks its charter is only to reduce day components that are >= 30 > > > > > days. > > > > > However, I think a good case could be made that it should normalize > > > > > negative days too; that is, the invariant on its result should be > > > > > 0 <= days < 30, not merely days < 30. > > > > > > > > What about cases like interval '1 month -99 days', should that turn into > > > > interval '-3 mons +21 days' or '-2 mons -9 days'? > > > > > > I think it should be the later. It is best to have a single sign, and I > > > think it is possible in all cases: > > > > > > '2 mons -1 days' > > > > > > could be adjusted to '1 mons 29 days'. > > > > There's a part of me that thinks the WHOLE THING should be positive or > > negative: > > > > -(2 months 1 day) > > But it isn't '-2 months, -1 day'. I think what you are saying is what I > am saying, that we should make the signs consistent. Pretty much. It just seems wrong to have different signs in what is essentially a single unit. We don't say 42 degrees, -12 minutes when measuring arc, do we? Then again, maybe some folks do. It just seems wrong to me. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] regarding grant option
Though, it is pretty easy to do something like: select 'GRANT ALL ON ' || table_name || ' TO public;' from information_schema.tables where table_schema='blah'; You can feed the output of that to psql, ei: psql -qc "select 'GRANT ALL ON ' || table_name || ' TO public;' from information_schema.tables where table_schema='blah'" | psql On Wed, Mar 01, 2006 at 12:00:16PM -0300, Alvaro Herrera wrote: > AKHILESH GUPTA wrote: > > thank you very much sir for your valuable suggestion, > > but i am talking about direct database query...! > > There is none that can help you here, short of making a function in > PL/pgSQL or other language ... > > > On 3/1/06, Alvaro Herrera <[EMAIL PROTECTED]> wrote: > > > > > > AKHILESH GUPTA wrote: > > > > > > > here i have to grant permissions to that user individually for each and > > > > every table by using: > > > > :->> grant ALL ON to ; > > > > GRANT > > > > and all the permissions are granted to that user for that particular > > > table. > > > > > > Yes. If you are annoyed by having to type too many commands, you can > > > write a little shell script to do it for you. > > -- > Alvaro Herrerahttp://www.CommandPrompt.com/ > The PostgreSQL Company - Command Prompt, Inc. > > ---(end of broadcast)--- > TIP 9: In versions below 8.0, the planner will ignore your desire to >choose an index scan if your joining column's datatypes do not >match > -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 1: 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: [SQL] Replication - state of the art?
You could also use WAL shipping and some PITR trickery to keep a 'warm standby' database up to date. How far behind it falls is up to you, since you'll be periodically syncing the current WAL file to the backup machine. Do the sync once a minute, and at most you lose 60 seconds of data. On Wed, Mar 01, 2006 at 02:49:18PM -0500, Andrew Sullivan wrote: > On Wed, Mar 01, 2006 at 11:28:06AM -0800, Bryce Nesbitt wrote: > > Actually let me loosen that a bit: we don't need two phase commit. We > > can loose the most recent transaction, or even the last few seconds of > > transactions. What we can't survive is -- on the day of the emergency > > -- a long and complicated DB rebuild with mistakes and hard-to-debug > > data issues. > > Then I suggest you use Slony-I. While it is not plug and play, the > thing it _is_ designed to handle reasonably well is failover and > (better) switchover. Most systems plan to solve that piece of > functionality later, with a script or something, at which point it is > apparent that setting up failover or swichover to be anything > approaching safe is actually very tricky. (Log shipping is probably > not in this category, but AFAIK the promote-to-live support for a > standby database copy is still not all built by anyone. If you like > rolling your own, however, it might be your answer.) > > > There's no fire creating demand for replication, so there is little time > > budget. > > So is there a sort of padded, no-sharp-corners, playroom that gets us > > 90% of the way there? > > The "no budget" remark here is what makes me strike CMD's Mammoth > Replicator off the list. But I'm sure their administration tools are > far sweeter than the admittedly hackish ones that Slony currently > delivers out of the box. > > > nightly) into something more reasonable (like 500 milliseconds). But > > risk -- of data corruption -- > > and time --too much-- will can the project. > > Another big reason to use a live-standby system like Slony is that > once you have the extra database online, you suddenly think of all > sorts of nifty queries you can move there without destroying your > production performance. Be careful not to get addicted, is all. > > A > > -- > Andrew Sullivan | [EMAIL PROTECTED] > Information security isn't a technological problem. It's an economics > problem. > --Bruce Schneier > > ---(end of broadcast)--- > TIP 9: In versions below 8.0, the planner will ignore your desire to >choose an index scan if your joining column's datatypes do not >match > -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Problem with query on history table
Probably the easiest way is to switch to using table partitioning and switch to using start_timestamp and end_timestamp, so that when you modify a row you update the old one setting end_timestamp to now() and insert the new row (all within one transaction). There are other ways to do it, but they'll probably be much slower. I don't think they require a lot of CASE statements though. Show us what you were planning on doing and maybe I'll have more ideas. On Mon, Feb 27, 2006 at 08:19:30AM +0100, Andreas Joseph Krogh wrote: > Hi all! > > I don't know if there's a standard solution to the kind of problem I'm trying > to solve, but I will appreciate your thougts(and maybe solution:) on this > problem of mine: > > I have 2 tables: hist and curr which hold numbers for "history-data" and > "current-data" respectivly. Here is a simplified version of the schema: > > CREATE TABLE curr ( > id integer NOT NULL, > etc integer NOT NULL, > created timestamp without time zone NOT NULL, > modified timestamp without time zone > ); > > CREATE TABLE hist ( > id serial NOT NULL, > curr_id integer NOT NULL REFERENCES curr(id), > etc integer NOT NULL, > modified timestamp without time zone NOT NULL > ); > > andreak=# SELECT * from curr; > id | etc | created | modified > +-+-+- > 1 | 5 | 2006-02-01 00:00:00 | > 2 | 10 | 2006-01-15 00:00:00 | 2006-01-26 00:00:00 > 3 | 10 | 2006-01-08 00:00:00 | 2006-01-25 00:00:00 > (3 rows) > > andreak=# SELECT * from hist; > id | curr_id | etc | modified > ++-+- > 1 | 3 | 30 | 2006-01-16 00:00:00 > 2 | 3 | 20 | 2006-01-25 00:00:00 > 3 | 2 | 20 | 2006-01-26 00:00:00 > (3 rows) > > Now - I would like to get a report on what the "ETC" is on a given entry in > "curr" in a given "point in time". Let me explain. If I want status for 17. > jan.(17.01.2006) I would like to get these numbers out from the query: > > id | created |curr_modified|hist_modified| etc > +-+-+-+- > 3 | 2006-01-08 00:00:00 | 2006-01-25 00:00:00 | 2006-01-16 00:00:00 | 30 > 2 | 2006-01-15 00:00:00 | 2006-01-26 00:00:00 | 2006-01-26 00:00:00 | 20 > 1 | 2006-02-01 00:00:00 | | | 5 > > > That is; If the entry is modified after it's created, a snapshot of the "old > version" is copied to table "hist" with the hist.modified field set to the > "modified-timestamp". So there will exist several entries in "hist" for each > time an entry in "curr" is modified. > > If I want status for the 27. jan. I would like the query to return the > following rows: > > id | created |curr_modified|hist_modified| etc > +-+-+-+- > 3 | 2006-01-08 00:00:00 | 2006-01-25 00:00:00 | 2006-01-25 00:00:00 | 10 > 2 | 2006-01-15 00:00:00 | 2006-01-26 00:00:00 | 2006-01-26 00:00:00 | 10 > 1 | 2006-02-01 00:00:00 | | | 5 > > select curr.id, curr.created, curr.modified as curr_modified, hist.modified > as > hist_modified, coalesce(hist.etc, curr.etc) as etc FROM curr LEFT OUTER JOIN > hist ON(curr.id = hist.curr_id) WHERE ... > > I'm really stuck here. It seems to me that I need a lot of > CASE...WHEN...ELSE.. statements in the query, but is there an easier way? > > -- > Andreas Joseph Krogh <[EMAIL PROTECTED]> > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq > -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Replication - state of the art?
On Wed, Mar 01, 2006 at 04:15:18PM -0600, Jim C. Nasby wrote: > You could also use WAL shipping and some PITR trickery to keep a 'warm > standby' database up to date. How far behind it falls is up to you, > since you'll be periodically syncing the current WAL file to the backup > machine. Do the sync once a minute, and at most you lose 60 seconds of > data. Right. But you have to write all of that, and write the failover scripts, and such like. And the OP suggested that there wasn't time budget for that. But it'd work. A -- Andrew Sullivan | [EMAIL PROTECTED] The whole tendency of modern prose is away from concreteness. --George Orwell ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Problem with query on history table
On Wednesday 01 March 2006 23:19, Jim C. Nasby wrote: > Probably the easiest way is to switch to using table partitioning and > switch to using start_timestamp and end_timestamp, so that when you > modify a row you update the old one setting end_timestamp to now() and > insert the new row (all within one transaction). > > There are other ways to do it, but they'll probably be much slower. I > don't think they require a lot of CASE statements though. > > Show us what you were planning on doing and maybe I'll have more ideas. > > On Mon, Feb 27, 2006 at 08:19:30AM +0100, Andreas Joseph Krogh wrote: > > Hi all! > > > > I don't know if there's a standard solution to the kind of problem I'm > > trying to solve, but I will appreciate your thougts(and maybe solution:) > > on this problem of mine: > > > > I have 2 tables: hist and curr which hold numbers for "history-data" and > > "current-data" respectivly. Here is a simplified version of the schema: > > > > CREATE TABLE curr ( > > id integer NOT NULL, > > etc integer NOT NULL, > > created timestamp without time zone NOT NULL, > > modified timestamp without time zone > > ); > > > > CREATE TABLE hist ( > > id serial NOT NULL, > > curr_id integer NOT NULL REFERENCES curr(id), > > etc integer NOT NULL, > > modified timestamp without time zone NOT NULL > > ); > > > > andreak=# SELECT * from curr; > > id | etc | created | modified > > +-+-+- > > 1 | 5 | 2006-02-01 00:00:00 | > > 2 | 10 | 2006-01-15 00:00:00 | 2006-01-26 00:00:00 > > 3 | 10 | 2006-01-08 00:00:00 | 2006-01-25 00:00:00 > > (3 rows) > > > > andreak=# SELECT * from hist; > The scenario I'm having is this: I have some data in table "curr", and whenever that data changes, a copy of the "old data" for that entry is copied over to "hist" so that "hist" always holds all entries but the newest one with their values "one before" "curr". I solved my problem with the following query: SELECT curr.id, COALESCE( (SELECT h.etc FROM history h WHERE h.history_modified = (SELECT MIN(h2.history_modified) FROM history h2 WHERE h2.history_modified >= ? AND h2.curr_id = curr.id) AND curr.created <= ? AND curr.id = h.curr_id), CASE WHEN curr.created > ? THEN NULL ELSE curr.etc END) AS etc FROM curr; I don't know how well it performes on larger data, but it work for me for the moment. I very much welcome some feedback on my "solution", and some enlightenment on what the impact of subqueries like this have on performance on larger data-sets. -- AJK ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Change date format through an environmental variable?
Michael Fuhr <[EMAIL PROTECTED]> writes: > The libpq documentation has a list of environment variables, although > it's not complete: > http://www.postgresql.org/docs/8.1/interactive/libpq-envars.html Er, what's not complete about it? Feel free to send a doc patch ... regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Change date format through an environmental variable?
On Thu, Mar 02, 2006 at 01:16:47AM -0500, Tom Lane wrote: > Michael Fuhr <[EMAIL PROTECTED]> writes: > > The libpq documentation has a list of environment variables, although > > it's not complete: > > http://www.postgresql.org/docs/8.1/interactive/libpq-envars.html > > Er, what's not complete about it? Feel free to send a doc patch ... Missing from that page are mostly standard variables that aren't specific to PostgreSQL but that applications like psql might use. Documented elsewhere are: EDITOR (psql) PAGER (psql) PGDATA (various) PSQL_EDITOR (psql) SHELL (psql) TMPDIR (psql) TZ (postmaster) VISUAL (psql) I see LC_COLLATE and LC_CTYPE in a few places but not in the documentation for postgres/postmaster, which is where they appear to be used (backend/main/main.c). Should those pages mention them? A couple of variables are used in the code but aren't mentioned in the documentation. Are they worth documenting? COMSPEC (psql, Win32 only) PG_DBPATH (ecpg, Informix mode) -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Change date format through an environmental variable?
On Thu, Mar 02, 2006 at 12:33:31AM -0700, Michael Fuhr wrote: > On Thu, Mar 02, 2006 at 01:16:47AM -0500, Tom Lane wrote: > > Michael Fuhr <[EMAIL PROTECTED]> writes: > > > The libpq documentation has a list of environment variables, although > > > it's not complete: > > > http://www.postgresql.org/docs/8.1/interactive/libpq-envars.html > > > > Er, what's not complete about it? Feel free to send a doc patch ... > > Missing from that page are mostly standard variables that aren't > specific to PostgreSQL but that applications like psql might use. By "missing" and "incomplete" I didn't mean to suggest that the other variables belong in the libpq documentation; I just meant that various components of PostgreSQL use variables that aren't mentioned there. -- Michael Fuhr ---(end of broadcast)--- TIP 1: 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
