Re: [GENERAL] timezone abbreviation in timestamp string input
Michael Fuhr <[EMAIL PROTECTED]> writes: > Maybe a warning that the specified timezone wouldn't be in effect > on the given date? No thanks. It is not wrong for example to refer to EST all year round. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] plpgsql loop not returning value
On 17 Oct 2004 01:24:27 -0400, Greg Stark <[EMAIL PROTECTED]> wrote: > Uh, not in Postgres. Perhaps you're thinking of another database system? > In fact I suspect it's slowing down your system somewhat. > > -- > greg So, there is no locking taking place during inserts at all? Or updates? Also, where would I find some more basic info on stuff like this? In the postgres docs? -Josh ---(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] OS X Install
On Oct 16, 2004, at 5:56 PM, Monte Milanuk wrote: I read the post above about deleting the group info as well; that allowed me to create a user 'postgres' via the Account Manager. Figure I'll have to twiddle the home dir and shell and whatnot via NetInfo. How do I make that user not visible in the login selection? Should I delete the user 'postgres' in the Account Manager and re-create him w/ the necessary parameters (shell, home dir, password) from the Terminal? I think that would be the easiest approach (delete the account and recreate it from the command line). Hopefully this will give you a setup similar to the original one if you don't want postgres in the login menu. Once you have it in NetInfo, you can set it up like the other invisible users. Best, 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
Re: [GENERAL] OS X Install
John DeSoi wrote: I think that would be the easiest approach (delete the account and recreate it from the command line). Hopefully this will give you a setup similar to the original one if you don't want postgres in the login menu. Once you have it in NetInfo, you can set it up like the other invisible users. I went ahead and deleted the postgres user via the Account Manager and set about creating the account from the command line. A little more involved than I'd expected; I'd forgotten that OS X, while nominally UNIX, doesn't use /etc/passwd and /etc/groups the way most of the BSD's and Linux variants that I've played with do. Had to search the Unix forum at Apple's Discussion board, and found a link to an article at O'Reilly's that included details on how to set up a new user and group from the command line using 'nicl' to manipulate NetInfo (the article was specifically for Oracle, but it had the info I needed). Double checked everything in NetInfo, looked good, so I started up postgreSQL. Seems to be running OK. Now I just need to figure out what I'm going to do w/ it ;) Thanks, Monte ---(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] OS X Install
Title: Re: [GENERAL] OS X Install At 8:09 PM -0400 10/15/04, Nathan Mealey wrote: I am trying to install PostgreSQL on OS X 10.3, using the package from Entropy.ch. The installation instructions there, as well as anywhere else I have seen them on the net, say to create a user (using the System Preferences pane) with a shortname "postgres". The problem is, this user already exists in my netinfo database/domain Note that fink installs a postgres user. -- Dan --it's like this--- Dan Sugalski even samurai [EMAIL PROTECTED] have teddy bears and even teddy bears get drunk
[GENERAL] How to increase number of connections to 7.2.1
I need to increase the number of connections to PostgreSQL 7.2.1. I have tried changing the value of max_connections in Postgresql.conf. It was commented out (as are all other entries except "tcpip_socket = true"). Setting it to any other value that the default 32 resulted in the server failing to restart. I know we should upgrade from 7.2.1 and that is going to happen soon, but this is a production server and I need a solution sooner. Thanks in advance, Edwin New Analyst Programmer Toll - Integrated Business Systems 43 - 63 Princes Highway, Doveton, VIC. 3175 Ph: 03 8710 0858 Fax: 03 9793 3970 Mobile: 0417 341 074 Email: [EMAIL PROTECTED]
Re: [GENERAL] How to increase number of connections to 7.2.1
On Mon, 2004-10-18 at 11:15, Edwin New wrote: > I have tried changing the value of max_connections in > Postgresql.conf. It was commented out (as are all other entries > except "tcpip_socket = true"). Setting it to any other value that the > default 32 resulted in the server failing to restart. Without knowing the error message you got, it's possible that you didn't increase shared_buffers to correspond to the newly increased max_connections. If you increase shared_buffers, then you're probably exceeding the system SysV shared memory restrictions. See http://www.postgresql.org/docs/7.4/static/kernel-resources.html#SYSVIPC -Neil ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] How to increase number of connections to 7.2.1
Edwin New <[EMAIL PROTECTED]> writes: >I need to increase the number of connections to PostgreSQL 7.2.1. > > >I have tried changing the value of max_connections in >Postgresql.conf. It was commented out (as are all other entries >except "tcpip_socket = true"). Setting it to any other value that the >default 32 resulted in the server failing to restart. 1) Make sure your shared_buffers setting is big enough to handle your desired connection limit. 2) Make sure your kernel's shared memory limits allow for your shared_buffers setting. If you can't get it to start, please post the server log entries from the failed attempt--otherwise we have nothing to go on. -Doug -- Let us cross over the river, and rest under the shade of the trees. --T. J. Jackson, 1863 ---(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] How to increase number of connections to 7.2.1
What error do you get when the server fails to restart? It sounds like it could be a kernel resource issue. See: http://www.postgresql.org/docs/7.4/static/kernel-resources.html -tfo On Oct 17, 2004, at 8:15 PM, Edwin New wrote: I need to increase the number of connections to PostgreSQL 7.2.1. I have tried changing the value of max_connections in Postgresql.conf. It was commented out (as are all other entries except "tcpip_socket = true"). Setting it to any other value that the default 32 resulted in the server failing to restart. I know we should upgrade from 7.2.1 and that is going to happen soon, but this is a production server and I need a solution sooner. Thanks in advance, Edwin New Analyst Programmer ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] How to increase number of connections to 7.2.1
Title: RE: [GENERAL] How to increase number of connections to 7.2.1 It was the shared buffers. Thanks to everyone for your quick and accurate responses. Regards, Edwin. -Original Message- From: Doug McNaught [mailto:[EMAIL PROTECTED]] Sent: Monday, 18 October 2004 11:45 AM To: Edwin New Cc: [EMAIL PROTECTED] Subject: Re: [GENERAL] How to increase number of connections to 7.2.1 Edwin New <[EMAIL PROTECTED]> writes: > I need to increase the number of connections to PostgreSQL 7.2.1. > > > I have tried changing the value of max_connections in > Postgresql.conf. It was commented out (as are all other entries > except "tcpip_socket = true"). Setting it to any other value that the > default 32 resulted in the server failing to restart. 1) Make sure your shared_buffers setting is big enough to handle your desired connection limit. 2) Make sure your kernel's shared memory limits allow for your shared_buffers setting. If you can't get it to start, please post the server log entries from the failed attempt--otherwise we have nothing to go on. -Doug -- Let us cross over the river, and rest under the shade of the trees. --T. J. Jackson, 1863
[GENERAL] 8.0.0beta3 vacuum analyze
I *think* I'm seeing "vacuum analyze" queries launched automatically on an 8.0.0beta3 (unless I have a rogue autovac running that I haven't spotted). Is this something new in 8.0 and to be expected? My settings: #vacuum_cost_delay = 0# 0-1000 milliseconds #vacuum_cost_page_hit = 1 # 0-1 credits #vacuum_cost_page_miss = 10 # 0-1 credits #vacuum_cost_page_dirty = 20# 0-1 credits #vacuum_cost_limit = 200# 0-1 credits Ed ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] OS X Install
A few comments -- On Fri, 15 Oct 2004 20:09:42 -0400 Nathan Mealey <[EMAIL PROTECTED]> wrote > I am trying to install PostgreSQL on OS X 10.3, using the package from > Entropy.ch. The installation instructions there, as well as anywhere > else I have seen them on the net, say to create a user (using the > System Preferences pane) with a shortname "postgres". The problem is, > this user already exists in my netinfo database/domain, and so I cannot > either create a new user with the same short name, or use this user, > because I do not know the password (I assume it is a system default > user). Thus, I am unable to complete the installation, because I > cannot run the following commands as user postgres. > > /usr/local/bin/initdb -D /usr/local/pgsql/data > > /usr/local/bin/pg_ctl -D /usr/local/pgsql/data -l postgres.log start > > Has anyone else encountered this before? I'm so confused by this...why > does this user already exist, and why can't I make use of it for this > installation? Should I create a different user? man sudo man sudoers And then look up netinfo. I'm pretty sure netinfo is available in the man, but you can use Google on Apple's site to get plenty of information. There is no need to give either the postgres user or root a password, much less a shell or a login directory, if you use sudo. Deleting the postgres user/group and adding them again should not be necessary (and will likely change the userid/groupid assigned, if you don't watch, but I don't know whether that might end up an issue). (And I have no idea why Apple would suggest using the system preferences user pane to add the postgres user unless they are intentionally dumbing it down. It works, but then you have OS login enabled for the "postgres" user.) My JPY 2. -- Joel <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] create view problem
dear sir, i am trying to create view with following query create view salesQ as select * from sales, customer where sales.customerkey = customer.customerkey; it is giving ERROR: column "customerkey" duplicated create view salesQ as select sales.*, customer.* from sales, customer where sales.customerkey = customer.customerkey; also giving same error. i cannot change field names or specify field names in view definition because many fields are there in both tables. how to create this view? thanks in advance, -Simon Moses, Bangalore, India. = ** Visit My Home Page http://www.geocities.com/ks_moses updated: 28 Sep 2004. Simon Moses ** ___ Do you Yahoo!? Declare Yourself - Register online to vote today! http://vote.yahoo.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] [JDBC] create view problem
On Sun, 17 Oct 2004, Simon Moses wrote: > dear sir, > i am trying to create view with following query This has nothing to do with JDBC or Java, so it should not be sent to the JDBC list. > create view salesQ as > select * from sales, customer where sales.customerkey > = customer.customerkey; > > it is giving > > ERROR: column "customerkey" duplicated > > create view salesQ as > select sales.*, customer.* from sales, customer where > sales.customerkey = customer.customerkey; > > also giving same error. > i cannot change field names or specify field names in > view definition because many fields are there in both > tables. how to create this view? > You need to use aliases in your SELECT... SELECT sales.customerkey AS salescustomerkey, customer.customerkey AS customercustomerkey, ... FROM sales, customer WHERE ... Kris Jurka ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] 8.0.0beta3 vacuum analyze
On Mon, 2004-10-18 at 14:49, Ed L. wrote: > I *think* I'm seeing "vacuum analyze" queries launched automatically on an > 8.0.0beta3 (unless I have a rogue autovac running that I haven't spotted). > Is this something new in 8.0 and to be expected? No. > #vacuum_cost_delay = 0# 0-1000 milliseconds > #vacuum_cost_page_hit = 1 # 0-1 credits > #vacuum_cost_page_miss = 10 # 0-1 credits > #vacuum_cost_page_dirty = 20# 0-1 credits > #vacuum_cost_limit = 200# 0-1 credits Vacuum cost delay affects the way that VACUUM behaves, not the frequency with which it is executed. -Neil ---(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] Avoiding sequential scans with OR join condition
Sim Zacks wrote: I would use 2 left joins and use the where condition to make sure one of them is true, such as: select big_table.* from big_table left join little_table as l1 on big_table.y1=l1.y and l1.x=10 left join little_table as l2 on big_table.y2=l2.y and l1.x=10 where l1.p_key is not null and l2.p_key is not null I have never tried this in postgresql, but in my experience with various other DB engines it is a lot faster then using an or in the join and faster then a union. Wow! Thanks! That certainly did the trick. Mike Mascari ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Avoiding sequential scans with OR join condition
Mike Mascari wrote: Sim Zacks wrote: I would use 2 left joins and use the where condition to make sure one of them is true, such as: select big_table.* from big_table left join little_table as l1 on big_table.y1=l1.y and l1.x=10 left join little_table as l2 on big_table.y2=l2.y and l1.x=10 where l1.p_key is not null and l2.p_key is not null I have never tried this in postgresql, but in my experience with various other DB engines it is a lot faster then using an or in the join and faster then a union. Wow! Thanks! That certainly did the trick. I'm thinking that the WHERE clauses condition should read: WHERE l1.p_pkey is not null OR l2.p_key is not null; My condition for a given selection of a big_table tuple is that either y1 or y2 exist as a valid x from little_table. So I think I need an OR instead of an AND. And AND condition would require that both y1 and y2 for the sample tuple of big_table be a valid x from little_table. Correct? Mike Mascari ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Avoiding sequential scans with OR join condition
Mike, You are probably correct, I was thinking in English, not SQL. That's what happens when I bang code too early in the morning. Thank You Sim Zacks IT Manager CompuLab 04-829-0145 - Office 04-832-5251 - Fax Mike Mascari wrote: > Sim Zacks wrote: > >> I would use 2 left joins and use the where condition to make sure one >> of them is true, such as: >> >> select big_table.* from >> big_table left join little_table as l1 on big_table.y1=l1.y and >> l1.x=10 >> left join little_table as l2 on big_table.y2=l2.y and l1.x=10 >> where l1.p_key is not null and l2.p_key is not null >> >> I have never tried this in postgresql, but in my experience with >> various other DB engines it is a lot faster then using an or in the >> join and faster then a union. > > Wow! Thanks! That certainly did the trick. I'm thinking that the WHERE clauses condition should read: WHERE l1.p_pkey is not null OR l2.p_key is not null; My condition for a given selection of a big_table tuple is that either y1 or y2 exist as a valid x from little_table. So I think I need an OR instead of an AND. And AND condition would require that both y1 and y2 for the sample tuple of big_table be a valid x from little_table. Correct? Mike Mascari ---(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] Newbie table definition question
* Ken Tozier <[EMAIL PROTECTED]> [2004-10-17 00:25:07 -0400]:
> So, assuming there's no "quantity" field defined in the "cart" table,
> if 3 apples, 2 oranges and 1 head of lettuce were purchased on a
> specific shopping trip, I would do something like this?
If you need a quantity field, add one.
CREATE TABLE cart (
id bigserial primary key NOT NULL,
trips_id bigint NOT NULL,
grocery_items_id bigint NOT NULL,
quantity integer NOT NULL -- assuming whole numbers
);
> INSERT INTO cart (trips_id, grocery_items_id, quantity) VALUES ({1,
> 1, 3}, {1, 2, 2}, {1, 4, 1})
Separate queries, but that's the idea.
> Having to remember ids for grocery items seem rather
> user-unfriendly. Would this be a candidate for a view? Allowing the
> user to enter something like {{"apples", 3}, {"oranges", 2},
> {"lettuce", 1}}
The idea is that whatever interface you use will be able to use the
numeric values in the grocery_types table.
For example, you'd pull the id/name pairs from grocery_types and
generate a drop-down box for them to select:
Fruit
Vegetable
Now if you needed to get at all the items you've defined along with
the names of the types in a human-readable format, you could create a
view like the following:
CREATE VIEW items_types_view AS
SELECT grocery_items.id,
grocery_items.name AS item_name,
grocery_types.name AS type_name
FROM grocery_items
JOIN grocery_types ON (grocery_items.grocery_types_id = grocery_types.id);
xinu=> select * from items_types_view;
id | item_name | type_name
+---+---
1 | Apple | fruit
2 | Orange| fruit
3 | Brocolli | fruit
4 | Lettuce | fruit
(4 rows)
The documentation on the postgreSQL site is going to be your best bet
for up-to-date information, but the Practical PostgreSQL book is still
an easy read for the basics.
http://www.commandprompt.com/ppbook/
--
Steven Klassen - Lead Programmer
Command Prompt, Inc. - http://www.commandprompt.com/
PostgreSQL Replication & Support Services, (503) 667-4564
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Newbie table definition question
* Steven Klassen <[EMAIL PROTECTED]> [2004-10-17 01:52:47 -0700]: > xinu=> select * from items_types_view; > id | item_name | type_name > +---+--- > 1 | Apple | fruit > 2 | Orange| fruit > 3 | Brocolli | fruit > 4 | Lettuce | fruit > (4 rows) And after I fixed the types for the latter items: xinu=> select * from items_types_view; id | item_name | type_name +---+--- 1 | Apple | fruit 2 | Orange| fruit 3 | Brocolli | vegatable 4 | Lettuce | vegatable (4 rows) -- Steven Klassen - Lead Programmer Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Replication & Support Services, (503) 667-4564 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] timezone abbreviation in timestamp string input
When a timestamp string input contains a timezone abbreviation (CDT,
PST, etc), which timezone offset is used? The input date's or today
date's? The result on my computer suggests the latter.
# create table ts (ts timestamptz);
# insert into ts values ('2004-10-17 00:00:00 CDT'); -- UTC-5
# insert into ts values ('2004-11-17 00:00:00 CDT'); -- UTC-6
# select ts at time zone 'utc' from ts;
timezone
-
2004-10-17 05:00:00
2004-11-17 05:00:00
(2 rows)
If this is true, then perhaps forbid timezone abbreviation in input
string, or emit warning about this?
--
dave
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Newbie table definition question
Thanks again Stephen
It helps to see a problem you understand defined in a language you
don't. I've got a handhold now.
Ken
On Oct 17, 2004, at 4:52 AM, Steven Klassen wrote:
* Ken Tozier <[EMAIL PROTECTED]> [2004-10-17 00:25:07 -0400]:
So, assuming there's no "quantity" field defined in the "cart" table,
if 3 apples, 2 oranges and 1 head of lettuce were purchased on a
specific shopping trip, I would do something like this?
If you need a quantity field, add one.
CREATE TABLE cart (
id bigserial primary key NOT NULL,
trips_id bigint NOT NULL,
grocery_items_id bigint NOT NULL,
quantity integer NOT NULL -- assuming whole numbers
);
INSERT INTO cart (trips_id, grocery_items_id, quantity) VALUES ({1,
1, 3}, {1, 2, 2}, {1, 4, 1})
Separate queries, but that's the idea.
Having to remember ids for grocery items seem rather
user-unfriendly. Would this be a candidate for a view? Allowing the
user to enter something like {{"apples", 3}, {"oranges", 2},
{"lettuce", 1}}
The idea is that whatever interface you use will be able to use the
numeric values in the grocery_types table.
For example, you'd pull the id/name pairs from grocery_types and
generate a drop-down box for them to select:
Fruit
Vegetable
Now if you needed to get at all the items you've defined along with
the names of the types in a human-readable format, you could create a
view like the following:
CREATE VIEW items_types_view AS
SELECT grocery_items.id,
grocery_items.name AS item_name,
grocery_types.name AS type_name
FROM grocery_items
JOIN grocery_types ON (grocery_items.grocery_types_id =
grocery_types.id);
xinu=> select * from items_types_view;
id | item_name | type_name
+---+---
1 | Apple | fruit
2 | Orange| fruit
3 | Brocolli | fruit
4 | Lettuce | fruit
(4 rows)
The documentation on the postgreSQL site is going to be your best bet
for up-to-date information, but the Practical PostgreSQL book is still
an easy read for the basics.
http://www.commandprompt.com/ppbook/
--
Steven Klassen - Lead Programmer
Command Prompt, Inc. - http://www.commandprompt.com/
PostgreSQL Replication & Support Services, (503) 667-4564
---(end of
broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] timezone abbreviation in timestamp string input
On Sun, Oct 17, 2004 at 04:45:45PM +0700, David Garamond wrote:
> When a timestamp string input contains a timezone abbreviation (CDT,
> PST, etc), which timezone offset is used? The input date's or today
> date's? The result on my computer suggests the latter.
>
> # create table ts (ts timestamptz);
> # insert into ts values ('2004-10-17 00:00:00 CDT'); -- UTC-5
> # insert into ts values ('2004-11-17 00:00:00 CDT'); -- UTC-6
> # select ts at time zone 'utc' from ts;
> timezone
> -
> 2004-10-17 05:00:00
> 2004-11-17 05:00:00
> (2 rows)
The input strings specifically say that the timezone is CDT (UTC-5),
so apparently that's the offset the database uses, regardless of
date. If you set the session's timezone to CST6CDT and omit the
timezone specification, then the database should use the offset
that would be in effect on that date:
SET TimeZone TO 'CST6CDT';
INSERT INTO TS VALUES ('2004-10-17 00:00:00');
INSERT INTO TS VALUES ('2004-11-17 00:00:00');
SELECT ts AT TIME ZONE 'UTC' FROM ts;
timezone
-
2004-10-17 05:00:00
2004-11-17 06:00:00
> If this is true, then perhaps forbid timezone abbreviation in input
> string, or emit warning about this?
Maybe a warning that the specified timezone wouldn't be in effect
on the given date?
--
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] Question about timezones
On Fri, Oct 08, 2004 at 09:52:00AM -0400, Tom Lane wrote: > The infrastructure needed for this is finally present in 8.0, ie we have > the timezone data available, but actually teaching AT TIME ZONE about it > didn't get done in time. Likely it will appear in 8.1 (especially if > you step up and do the work ;-)). I've had a look at the code that is in CVS and it looks like everything needed is basically there. I think what's basically needed is a system to keep track of tzname => struct state mappings, probably a hash of some sort. I'd consider creating a timezone preserving type, but it's not necessary for what I'm doing. Is that library already built into 8.0? If that's the case there is an opportunity to create a contrib module that hooks into it. Hmm... -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. pgpNeS17G64Jh.pgp Description: PGP signature
