Re: [SQL] Change date format through an environmental variable?
Am Donnerstag, 2. März 2006 08:33 schrieb Michael Fuhr: > 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? No, these variables are not used there, only by initdb. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(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
[SQL] dump with lo
Hello pgsql-sql, I try copy my old database with large objects from postgresql7.4 to postgresql-8.1. How can I dump database with lo? pg_dumpall don't save lo. -- Best regards, Maciej mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Help with trigger that updates a row prior to a potentially aborted deletion?
On Wed, Mar 01, 2006 at 08:41:20PM -, Simon Kinsella wrote: > Ok thanks, will check this out. Is that the same as savepoints, or something > different? (am using 8.1.2) Yes, same thing. > 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. In a message you sent that I read after I sent mine, you also said you had to be able to handle deletes from the table with CASCADE. My suggestion won't work for that, I don't think (but it might be worth a try). The only other thing I can think of is just flag everything, and use a daemon to go around and perform the actual deletes for you. A -- Andrew Sullivan | [EMAIL PROTECTED] The plural of anecdote is not data. --Roger Brinner ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Without OIDs
Hello, I'm creating a temporary table in a function and using Alter Table ... Without OIDs, but when the function is called for the second time I get an error "relation with OIDs ... doesn't exist" on an insert statement to the table. I'm using Postgre 8.0 which says that supports without OIDs. Any suggestions? __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] FW: sql copy does not work
Title: FW: sql copy does not work
Hi
I have a problem with the copy command. I need to write a Perl script where I copy the log files into the database.
If I use the copy following syntax in shell script it works, but I need to use it in Perl.
In shell script look like this and it works.
su - postgres -c "echo "\""copy test from '/data/20060227.out';"\"" | psql imsdb"
this is how it looks like in Perl:
system ("su - postgres -c \"echo \"\"\"copy test from \'/data/log/20060115.out\'\;\"\"\" | psql imsdb\"");
I escape always the " but then I get the following error.
copy
sh: | psql imsdb: not found
If I try the other way around there is always an error that role c does not exist
system ("su - postgres -c \"psql imsdb -c \"copy mss6n from '/data/20060227.out'\;\"");
psql: warning: extra command-line argument "copy" ignored
psql: FATAL: role "-c" does not exist
If I try to use copy with the -c option I always get an error that role -c does not exist.
Anyone has any idea why?
I was searching in the knowledge database and docs for this error but I can not find anything.
Many thanks
Aniko
Re: [SQL] dump with lo
Maciej Piekielniak <[EMAIL PROTECTED]> writes: >I try copy my old database with large objects from postgresql7.4 to >postgresql-8.1. >How can I dump database with lo? pg_dumpall don't save lo. Use 8.1's pg_dump and pg_dumpall to dump from the old server. We fixed that problem finally. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Change date format through an environmental variable?
Try the PGDATESTYLE environment variable. Works in 7.4 and 8.1, though it is claimed to be deprecated. -- George Young On Wed, 01 Mar 2006 12:32:26 -0500 Mark Fenbers <[EMAIL PROTECTED]> threw this fish to the penguins: > 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 > -- "Are the gods not just?" "Oh no, child. What would become of us if they were?" (CSL) ---(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] Without OIDs
On Thu, Mar 02, 2006 at 05:14:54AM -0800, Emil Rachovsky wrote: > I'm creating a temporary table in a function and using > Alter Table ... Without OIDs, but when the function is Why alter the table? Why not specify WITHOUT OIDS when you create the table? > called for the second time I get an error "relation > with OIDs ... doesn't exist" on an insert statement to > the table. I'm using Postgre 8.0 which says that > supports without OIDs. Any suggestions? This problem isn't related to WITHOUT OIDS. See FAQ 4.19 for an explanation: http://www.postgresql.org/docs/faqs.FAQ.html#item4.19 -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] Question about Sql SELECT and optimizer
I am trying to understand something I have seen happen. I had a select that looked like: select f(A) from A, B, C where g(A) Where f(A) is the select that only depends on table A; g(A) is the where part that only depends on table A. What I saw happen was the optimizer will waste a lot of time (seconds!) bringing in table B and C. I was wondering why doesn't the optimizer drop references to tables B and C since they aren't used any where? Peace and thanks in advance Chuck Wegrzyn ---(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] dump with lo
Hello Tom, Thursday, March 2, 2006, 4:21:22 PM, you wrote: TL> Maciej Piekielniak <[EMAIL PROTECTED]> writes: >>I try copy my old database with large objects from postgresql7.4 to >>postgresql-8.1. >>How can I dump database with lo? pg_dumpall don't save lo. TL> Use 8.1's pg_dump and pg_dumpall to dump from the old server. We fixed TL> that problem finally. TL> regards, tom lane I try to copy pg_wrapper,PgCommon.pm to /usr/bin but when i type pg_dump -V i got: Argument "." isn't numeric in numeric lt(<) at PgCommon.pm line 439 Error: You must install at least one postgresql-client-package I have debian sarge -- Best regards, Maciejmailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Question about Sql SELECT and optimizer
On Thu, Mar 02, 2006 at 11:19:32AM -0500, Chaz. wrote: > I am trying to understand something I have seen happen. I had a select > that looked like: > > select f(A) from A, B, C where g(A) > > Where f(A) is the select that only depends on table A; > g(A) is the where part that only depends on table A. > > What I saw happen was the optimizer will waste a lot of time (seconds!) > bringing in table B and C. I was wondering why doesn't the optimizer > drop references to tables B and C since they aren't used any where? The above query does a cross join. Even though you're not using values from B and C they're still contributing rows to the result set. test=> SELECT * FROM a; aid - a1 a2 (2 rows) test=> SELECT * FROM b; bid - b1 b2 (2 rows) test=> SELECT * FROM c; cid - c1 c2 (2 rows) test=> SELECT a.*, b.*, c.* FROM a, b, c WHERE a.aid = 'a1'; aid | bid | cid -+-+- a1 | b1 | c1 a1 | b2 | c1 a1 | b1 | c2 a1 | b2 | c2 (4 rows) test=> SELECT a.*, b.* FROM a, b, c WHERE a.aid = 'a1'; aid | bid -+- a1 | b1 a1 | b2 a1 | b1 a1 | b2 (4 rows) test=> SELECT a.* FROM a, b, c WHERE a.aid = 'a1'; aid - a1 a1 a1 a1 (4 rows) -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Why can't I use daylight savings time aware time zones with the AT TIME ZONE clause?
Title: Why can't I use daylight savings time aware time zones with the AT TIME ZONE clause? I would like to use daylight savings time aware time zones to display dates, but can't seem to get them to work no matter what alias I use: select '2005-02-25 00:00:00 -800' AT TIME ZONE 'CST6CDT' as interval … ERROR: time zone "cst6cdt" not recognized select '2005-02-25 00:00:00 -800' AT TIME ZONE 'America/Chicago' as interval ... ERROR: time zone "america/chicago" not recognized However non-aware time zones are available: select '2005-02-25 00:00:00 -800' AT TIME ZONE 'CST' as interval 2005-02-25 02:00:00 select '2005-02-25 00:00:00 -800' AT TIME ZONE 'CDT' as interval 2005-02-25 03:00:00 Setting the time zone works just fine set time zone 'America/Chicago'; Query returned successfully with no result in 20 ms. set time zone 'CST6CDT'; Query returned successfully with no result in 20 ms. There is a file ../pgsql/share/timezone/CST6CDT And ../pgsql/share/timezone/America/Chicago select version(); PostgreSQL 8.0.3 on i686-pc-linux-gnu, compiled by GCC 2.95.3 Why can't I use daylight savings time aware time zones with the AT TIME ZONE clause? Many thanks, Robert Davidson
Re: [SQL] Why can't I use daylight savings time aware time zones with the AT TIME ZONE clause?
On Thu, Mar 02, 2006 at 02:33:08PM -0800, Davidson, Robert wrote: > I would like to use daylight savings time aware time zones to > display dates, but can't seem to get them to work no matter what > alias I use: > > select '2005-02-25 00:00:00 -800' AT TIME ZONE 'CST6CDT' as interval > ... > ERROR: time zone "cst6cdt" not recognized This works only since 8.1. Here's an excerpt from the 8.1 Release Notes: * Allow the full use of time zone names in AT TIME ZONE, not just the short list previously available (Magnus) -- Michael Fuhr ---(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?
Hi Andrew, I think I may have cracked this problem by combining a RULE ON DELETE which calls a function instead of the standard DELETE op. No triggers. It was a real nightmare as it was my first attempt at a rule and it kept ending up in circular self-references. In the end though it boiled down to a pretty compact solution. It's getting late now but I'll be testing it out properly tomorrow. I can post it up then if you're interested. Thanks for your help, Simon -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Andrew Sullivan Sent: Thursday, March 02, 2006 12:20 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 08:41:20PM -, Simon Kinsella wrote: > Ok thanks, will check this out. Is that the same as savepoints, or > something different? (am using 8.1.2) Yes, same thing. > 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. In a message you sent that I read after I sent mine, you also said you had to be able to handle deletes from the table with CASCADE. My suggestion won't work for that, I don't think (but it might be worth a try). The only other thing I can think of is just flag everything, and use a daemon to go around and perform the actual deletes for you. A -- Andrew Sullivan | [EMAIL PROTECTED] The plural of anecdote is not data. --Roger Brinner ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(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
[SQL] How to check date-interval constraints
Hi, I'd like to have a table that looks like this: my_option ( id serial primary key, myvalue double, valid_start timestamp, valid_stop timestamp ); I want to store values that are only valid in a given start-stop-interval so I could find a date-specific value for NOW() or some other given date. select myvalue from my_option where somedate between valid_start and valid_stop; How can I have a constraint, that prohibits nesting or overlapping intervals? 172006-1-1 2006-1-31 292006-2-1 2006-2-28 OK 352006-1-10 2006-1-20 BAD lies within line 1 432006-1-20 2006-2-10 BAD starts within line 1 and ends in line 2 To make it even more interesting, it'd be nice to add a type-column so I could ask: select myvalue from my_option where now() between valid_start and valid_stop AND mytype=42; Then interval should ONLY not overlap with other intervals of the SAME type. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] How to check date-interval constraints
On Fri, Mar 03, 2006 at 04:28:01AM +0100, Andreas wrote: > How can I have a constraint, that prohibits nesting or overlapping > intervals? > > 172006-1-1 2006-1-31 > 292006-2-1 2006-2-28 OK > 352006-1-10 2006-1-20 BAD lies within line 1 > 432006-1-20 2006-2-10 BAD starts within line 1 > and ends in line 2 This is just a brainstorm, but what about creating a composite type, a comparison function, and an operator class, then declaring a unique index on that composite type? Something like the following: CREATE TYPE drange AS ( dstart date, dstop date ); CREATE FUNCTION drange_cmp(drange, drange) RETURNS integer AS $$ BEGIN RETURN CASE WHEN $1.dstop < $2.dstart THEN -1 WHEN $1.dstart > $2.dstop THEN 1 ELSE 0 END; END; $$ LANGUAGE plpgsql IMMUTABLE STRICT; CREATE OPERATOR CLASS drange_ops DEFAULT FOR TYPE drange USING btree AS FUNCTION 1 drange_cmp(drange, drange); Here's an example that includes your additional constraint of the range being non-overlapping only for rows with the same type-column: CREATE TABLE foo ( id integer PRIMARY KEY, ftype integer NOT NULL, fstart date NOT NULL, fstop date NOT NULL CHECK (fstop >= fstart) ); CREATE UNIQUE INDEX foo_uniq_idx ON foo (ftype, (row(fstart, fstop)::drange)); INSERT INTO foo VALUES (1, 1, '2006-01-01', '2006-01-31'); -- ok INSERT INTO foo VALUES (2, 1, '2006-02-01', '2006-02-28'); -- ok INSERT INTO foo VALUES (3, 1, '2006-01-10', '2006-01-20'); -- bad INSERT INTO foo VALUES (4, 1, '2006-01-21', '2006-02-10'); -- bad INSERT INTO foo VALUES (5, 2, '2006-01-10', '2006-01-20'); -- ok INSERT INTO foo VALUES (6, 2, '2006-01-21', '2006-02-10'); -- ok I've done only minimal testing with this but so far it seems to work, even with concurrent transactions. However, I'm not sure this is the best way to approach the problem; if it's flawed then hopefully somebody will point out why and maybe suggest something else. -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] How to check date-interval constraints
On Mar 3, 2006, at 14:13 , Michael Fuhr wrote: On Fri, Mar 03, 2006 at 04:28:01AM +0100, Andreas wrote: How can I have a constraint, that prohibits nesting or overlapping intervals? 172006-1-1 2006-1-31 292006-2-1 2006-2-28 OK 352006-1-10 2006-1-20 BAD lies within line 1 432006-1-20 2006-2-10 BAD starts within line 1 and ends in line 2 This is just a brainstorm, but what about creating a composite type, a comparison function, and an operator class, then declaring a unique index on that composite type? Another way is presented in Snodgrass' "Developing Time-Oriented Database Applications in SQL", out of print but available as a PDF download from his website: http://www.cs.arizona.edu/people/rts/tdbbook.pdf You'll need to use CREATE CONSTRAINT TRIGGER rather than just CREATE TRIGGER to apply the constraints you're looking for, as often you'll need to wrap a multi-statement update in a transaction to ensure integrity. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Sequential scan where Index scan expected.
I'm getting sequential scans (and poor performance), on scans using my primary keys. Can anyone help figure out why? demo=# \d xx_thing Table "public.xx_thing" Column |Type | Modifiers -+-+--- thing_id | bigint | not null thing_model | character varying(128) | thing_color | character varying(128) | thing_year| integer | Indexes: "xx_thing_pkey" primary key, btree (thing_id) demo=# analyze verbose xx_thing_event; INFO: analyzing "public.xx_thing_event" INFO: "xx_thing_event": 3374 pages, 3000 rows sampled, 197478 estimated total rows demo=# explain update xx_thing_event set thing_color='foo' where thing_event_id=1; QUERY PLAN - Seq Scan on xx_thing_event (cost=0.00..5842.48 rows=1 width=110) Filter: (thing_event_id = 1) (2 rows) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Sequential scan where Index scan expected (update)
I'm getting sequential scans (and poor performance), on scans using my primary keys. This is an older postgres. Can anyone help figure out why? demo=# \d xx_thing Table "public.xx_thing" Column |Type | Modifiers -+-+--- thing_id | bigint | not null thing_model | character varying(128) | thing_color | character varying(128) | thing_year| integer | Indexes: "xx_thing_pkey" primary key, btree (thing_id) demo=# analyze verbose xx_thing_event; INFO: analyzing "public.xx_thing_event" INFO: "xx_thing_event": 3374 pages, 3000 rows sampled, 197478 estimated total rows demo=# explain update xx_thing_event set thing_color='foo' where thing_event_id=1; QUERY PLAN - Seq Scan on xx_thing_event (cost=0.00..5842.48 rows=1 width=110) Filter: (thing_event_id = 1) (2 rows) demo=# select * from version(); version -- PostgreSQL 7.4.1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-20) (1 row) ---(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] Sequential scan where Index scan expected (update)
Perhaps it is clogged with dead tuples -- has it been vacuumed recently with enough FSM space ? It seems unlikely but maybe try an explict cast for the thing_id call, e.g. explain update xx_thing_event set thing_color='foo' where thing_event_id=1::bigint; It may also be that 5842 rows is enough that the planner decides it is faster to do a sequential scan that the busier index scan (read index, get data row, versus just reading all the necessary pages in one faster sequential scan). If you set the sequential scan parameter in the config file and reload postgres does the same query get faster ? (not suggesting this for real runtime use but it can be useful to diagnose issues). Greg Williamson DBA GlobeXplorer LLC -Original Message- From: [EMAIL PROTECTED] on behalf of Bryce Nesbitt Sent: Thu 3/2/2006 11:28 PM To: pgsql-sql@postgresql.org Cc: Subject:[SQL] Sequential scan where Index scan expected (update) I'm getting sequential scans (and poor performance), on scans using my primary keys. This is an older postgres. Can anyone help figure out why? demo=# \d xx_thing Table "public.xx_thing" Column |Type | Modifiers -+-+--- thing_id | bigint | not null thing_model | character varying(128) | thing_color | character varying(128) | thing_year| integer | Indexes: "xx_thing_pkey" primary key, btree (thing_id) demo=# analyze verbose xx_thing_event; INFO: analyzing "public.xx_thing_event" INFO: "xx_thing_event": 3374 pages, 3000 rows sampled, 197478 estimated total rows demo=# explain update xx_thing_event set thing_color='foo' where thing_event_id=1; QUERY PLAN - Seq Scan on xx_thing_event (cost=0.00..5842.48 rows=1 width=110) Filter: (thing_event_id = 1) (2 rows) demo=# select * from version(); version -- PostgreSQL 7.4.1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-20) (1 row) ---(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 !DSPAM:4407f048253644359117518! ---(end of broadcast)--- TIP 6: explain analyze is your friend
