Re: [GENERAL] database constraints

2004-10-07 Thread David Garamond
David Fetter wrote: BTW, id is a terrible name for a column. Better call it foo_id. I disagree with the idea that id is a terrible name for a column. The only negative to it, is that you will have to be explicit in your declarations when doing joins and such... ex: SELECT * FROM foo JOIN

[GENERAL] interfaces for python

2004-10-07 Thread Scott Frankel
I'd like to know if anyone has recommendations for which Python DB-API 2.0 interface to use with PostGreSQL-7.4.5. The database and tools to interact with it will be hosted on a MacOS 10.3.x machine. The db schema represents a small production studio environment with only a handful of users.

Re: [GENERAL] database constraints

2004-10-07 Thread David Garamond
Joshua D. Drake wrote: This brings up an interesting idea. What if it were possible to set some kind of rules on DDL at database creation time? For example, I'd like to be able to throw an error if somebody tries to name an object any of the SQL keywords. Other possible rules: * Every table must

Re: [GENERAL] interfaces for python

2004-10-07 Thread Pierre-Frdric Caillaud
I'd advise psycopg as the fastest one (by a factor of 10x on large selects). On Wed, 6 Oct 2004 21:13:02 -0700, Scott Frankel [EMAIL PROTECTED] wrote: I'd like to know if anyone has recommendations for which Python DB-API 2.0 interface to use with PostGreSQL-7.4.5. The database and tools

Re: [GENERAL] database constraints

2004-10-07 Thread Marco Colombo
On Thu, 7 Oct 2004, David Garamond wrote: With all due respect, David, everybody is entitled to his own opinion and yours is not the absolute truth. Column and table naming is not exact science. Naming every single-column PK as id has advantages over tablename_id: - you instantly know that id

Re: [GENERAL] pgpool log errors

2004-10-07 Thread Tatsuo Ishii
I've started using pgpool and while everything appears to be working, I've been getting a lot of the following errors in my logs: ERROR: pid 14761: pool_read: EOF encountered This seems to be simple enough - the client/user probably just canceled the request and isn't

Re: [GENERAL] Random not so random

2004-10-07 Thread Bruno Wolff III
On Thu, Oct 07, 2004 at 12:08:51 +0200, Marco Colombo [EMAIL PROTECTED] wrote: On Wed, 6 Oct 2004, Bruno Wolff III wrote: You don't necessarily need to break SHA1 to be able to track the internal state. Well, I'm not an expert. I base my knowledge on what other people say. Quoting the

[GENERAL] Problem with boolean type

2004-10-07 Thread Johann Robette
Hi, I'm using Postgres 8.0 beta 3 and the appropriate JDBC Driver. I use EJB to create a record in a table containing a Boolean field answered. The EJB method expects a Boolean object so I pass new Boolean(false) in order to create it as false. But I get the following error :

[GENERAL] unsubscription

2004-10-07 Thread ma tr
I want de unsuscribe from this site .how can I do? Vous manquez d’espace pour stocker vos mails ? Yahoo! Mail vous offre GRATUITEMENT 100 Mo ! Créez votre Yahoo! Mail sur http://fr.benefits.yahoo.com/ Le nouveau Yahoo! Messenger est arrivé ! Découvrez

Re: [GENERAL] Current wisdom wrt fsm on 8.0

2004-10-07 Thread Tom Lane
elein [EMAIL PROTECTED] writes: What is the current wisdom on setting the fsm variables for 8.0? How is it different from 7.4? Or is it? Same as before. I am assuming these are the values that changed with Jan's changes. If not what were those variables? No, I can't think of any 8.0

Re: [GENERAL] interfaces for python

2004-10-07 Thread Steven Klassen
* Pierre-Frédéric Caillaud [EMAIL PROTECTED] [2004-10-07 10:45:57 +0200]: I'd advise psycopg as the fastest one (by a factor of 10x on large selects). I second this recommendation. -- Steven Klassen - Lead Programmer Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Replication

[GENERAL] Alternate interval output function

2004-10-07 Thread Ian E. Morgan
After being frustrated with the inflexible output of intervals, I've written a pl/pgsql function to do what I want, and hopefully some other people might find it useful. Output is a string that matches the output format of an interval as closely as possible, but rather than counting days as a

[GENERAL] Reading a text file into PostgreSQL

2004-10-07 Thread Eyinagho Newton
Hiya Everyone, Can anyone explain how postgreSQL reads from a text file into tables already created in PostgreSQL? I am also checking the thread in the Forum just to see if someone has written about it in the past. Thanks. Newton ___ Do you

Re: [GENERAL] Reading a text file into PostgreSQL

2004-10-07 Thread Steven Klassen
* Eyinagho Newton [EMAIL PROTECTED] [2004-10-07 09:41:47 -0700]: Can anyone explain how postgreSQL reads from a text file into tables already created in PostgreSQL? Where did this text file come from? What format is it in? -- Steven Klassen - Lead Programmer Command Prompt, Inc. -

Re: [GENERAL] PostgreSQL seems to wait and block all the queries

2004-10-07 Thread William Yu
HM wrote: I would like to know why the database seems frozen. ... The problem happened at 04:15 AM during a vacuum FULL and could occurs at every moment VACUUM FULL locks your tables. ---(end of broadcast)--- TIP 9: the planner will ignore your

Re: [GENERAL] Reading a text file into PostgreSQL

2004-10-07 Thread Ron St-Pierre
Eyinagho Newton wrote: Hiya Everyone, Can anyone explain how postgreSQL reads from a text file into tables already created in PostgreSQL? I am also checking the thread in the Forum just to see if someone has written about it in the past. Thanks. Newton Do you mean when you read it in using the

[GENERAL] flattened tables with normalized tables

2004-10-07 Thread Dennis Gearon
If I want to set up a dbase with normalized tables for inserts,and a flattened table for selects, am i going in the right direction for speeding up a busy site? Also, if some of you are also doing this, how and how often do you do the SELECT from the normalized tables to the flattened table?

[GENERAL] pre-sroted table (w/ index)

2004-10-07 Thread Vic Cekvenich
In TransacSQL, we used to have the tabled sorted by marking an index for example by create_date. This way we did not need to do an order by or at least it was faster. Is there a way to force the table to be physicaly stroed sorted by a certain index? tia, .V ---(end of

[GENERAL] Date variables in psql

2004-10-07 Thread Francisco Reyes
Trying the following simple sql file: \set proc_date 6/30/2004 \echo Date is :proc_date select * from feeds where date = :proc_date limit 20; If I start psql with the -a option I see the output: \set proc_date 6/30/2004 \echo Date is :proc_date Date is 6/30/2004 select * from feeds where date =

Re: [GENERAL] pre-sroted table (w/ index)

2004-10-07 Thread Steve Atkins
On Thu, Oct 07, 2004 at 11:30:37AM -0500, Vic Cekvenich wrote: In TransacSQL, we used to have the tabled sorted by marking an index for example by create_date. This way we did not need to do an order by or at least it was faster. You'll always need to do an order by, as there's no guarantee

Re: [GENERAL] Date variables in psql

2004-10-07 Thread Tom Lane
Francisco Reyes [EMAIL PROTECTED] writes: Trying the following simple sql file: \set proc_date 6/30/2004 \echo Date is :proc_date select * from feeds where date = :proc_date limit 20; That's going to expand to select * from feeds where date = 6/30/2004 limit 20; whereas what you need is

Re: [GENERAL] interfaces for python -- thanks

2004-10-07 Thread Scott Frankel
Thanks for the responses! I have selected psycopg based on: - your recommendations (though massively parallel connections aren't currently likely in my environment - my success in building the target (with kudos props to their install documentation)

Re: [GENERAL] Date variables in psql

2004-10-07 Thread Francisco Reyes
On Thu, 7 Oct 2004, Tom Lane wrote: It's fairly painful to get single quotes into a psql variable; AFAIK you have to do it like this: \set proc_date '\'6/30/2004\'' Thanks that worked. I figure I needed to escape the single quotes, but I had tried \'6/30/2004\', which did not work.

[GENERAL] 8.0 questions

2004-10-07 Thread Artem Litvinovich
Dear postgres community, I would like to know what are the significant changes and new features introduced in Postgres 8.0 which warranted the major version number increment. From the postres website it is not very clear as I can only find technical changelogs for the beta releases. //Artem

[GENERAL] PSQL undesired transaction behavior when connection is lost.

2004-10-07 Thread Mike Benoit
I assume I'm not the first person to run in to this, however searching google didn't seem to come up with anything useful. its= begin; delete from pay_stub_entry where pay_stub_id in (select id from pay_stub where created_date = 1096527603 order by created_date desc); delete from pay_stub where

Re: [GENERAL] European dates with Win32 version

2004-10-07 Thread Bruce Momjian
OK, we just tested this on Win32 and it now works fine. Seems we must have fixed it somehow between your report and now. --- Raymond O'Donnell wrote: Hi all, This is probably a silly question, but...how do I get

Re: [GENERAL] PSQL undesired transaction behavior when connection is lost.

2004-10-07 Thread Steven Klassen
* Mike Benoit [EMAIL PROTECTED] [2004-10-07 11:47:50 -0700]: I assume I'm not the first person to run in to this, however searching google didn't seem to come up with anything useful. AFAICT, the first query is just constructed poorly, while the second seems to recurse on itself. The order in

Re: [GENERAL] PSQL undesired transaction behavior when connection is lost.

2004-10-07 Thread Steven Klassen
* Steven Klassen [EMAIL PROTECTED] [2004-10-07 12:33:34 -0700]: DELETE FROM pay_stub_entry JOIN pay_stub ON (pay_stub_entry.pay_stub_id = pay_stub.id) WHERE pay_stub.created_date =1096527603; After RTFM'ing it appears you can't do actual joins with delete so we'll just have to daisy-chain the

[GENERAL] Question about timezones

2004-10-07 Thread Martijn van Oosterhout
Hi, The operating system I run (Linux) comes with many, many timezone files for many different places in the world. For example: $ TZ='Australia/Sydney' date Fri Oct 8 06:15:31 EST 2004 $ TZ='Europe/Amsterdam' date Thu Oct 7 22:15:38 CEST 2004 $ TZ='Africa/Bissau' date Thu Oct 7 20:18:44 GMT

Re: [GENERAL] PSQL undesired transaction behavior when connection is lost.

2004-10-07 Thread Tom Lane
Steven Klassen [EMAIL PROTECTED] writes: * Mike Benoit [EMAIL PROTECTED] [2004-10-07 11:47:50 -0700]: I assume I'm not the first person to run in to this, however searching google didn't seem to come up with anything useful. AFAICT, the first query is just constructed poorly, while the second

Re: [GENERAL] PSQL undesired transaction behavior when connection is lost.

2004-10-07 Thread Steven Klassen
* Tom Lane [EMAIL PROTECTED] [2004-10-07 16:33:26 -0400]: Steven Klassen [EMAIL PROTECTED] writes: * Mike Benoit [EMAIL PROTECTED] [2004-10-07 11:47:50 -0700]: I assume I'm not the first person to run in to this, however searching google didn't seem to come up with anything useful.

Re: [GENERAL] Question about timezones

2004-10-07 Thread Steven Klassen
* Martijn van Oosterhout [EMAIL PROTECTED] [2004-10-07 22:22:24 +0200]: Is there any way I can use these from within postgresql? Those files contains details about daylight saving changes and other useful details like that, which a simple PST or EST won't cover. Or should I simply do all my

[GENERAL] regular expression searches

2004-10-07 Thread David Bitner
I am trying to create a PL/PGSQL function that can parse a street address into the component parts (i.e. 200 W 54th Street into num-200 dir-W street-54th type-ST). What I would like is to be able to use regular expressions within PL/PGSQL to accomplish this using mapping tables for the different

Re: [GENERAL] [JDBC] Problem with boolean type

2004-10-07 Thread Kris Jurka
On Thu, 7 Oct 2004, Johann Robette wrote: I use EJB to create a record in a table containing a Boolean field answered. The EJB method expects a Boolean object so I pass new Boolean(false) in order to create it as false. But I get the following error : java.sql.SQLException: ERROR:

[GENERAL] Index problem.... GIST (tsearch2)

2004-10-07 Thread Net Virtual Mailing Lists
Hello, I have a table like this with some indexes as identified: CREATE TABLE sometable ( dataTEXT, data_ftiTSVECTOR, category1 INTEGER, category2 INTEGER, category3 INTEGER ); CREATE OR REPLACE FUNCTION is_null(anyelement) RETURNS BOOLEAN AS 'SELECT $1 IS

Re: [GENERAL] 8.0 questions

2004-10-07 Thread Robby Russell
On Thu, 2004-10-07 at 14:48 -0400, Artem Litvinovich wrote: Dear postgres community, I would like to know what are the significant changes and new features introduced in Postgres 8.0 which warranted the major version number increment. From the postres website it is not very clear as I can

Re: [GENERAL] regular expression searches

2004-10-07 Thread David Fetter
On Thu, Oct 07, 2004 at 04:07:08PM -0500, David Bitner wrote: I am trying to create a PL/PGSQL function that can parse a street address into the component parts (i.e. 200 W 54th Street into num-200 dir-W street-54th type-ST). What I would like is to be able to use regular expressions within

Re: [GENERAL] Index problem.... GIST (tsearch2)

2004-10-07 Thread Greg Stark
Net Virtual Mailing Lists [EMAIL PROTECTED] writes: SELECT * from sometable WHERE is_null(category1)='f' AND data_fti @@ to_tsquery('default', 'postgres'); How can I make this query first use the is_null index?... It strikes me that this would almost always be faster then doing the

[GENERAL] Question from a newbie

2004-10-07 Thread Kiarash Bodouhi
Hello All I used to be MySQL user. I recently changed to PostGres. It is much more fun. I have two questions. First, is it possible to call other functions from plpython functions? I used following but didn't work. Any comments? CREATE OR REPLACE FUNCTION test4() RETURNS varchar AS ' return

Re: [GENERAL] flattened tables with normalized tables

2004-10-07 Thread Michael Fuhr
On Thu, Oct 07, 2004 at 10:07:47AM -0700, Dennis Gearon wrote: If I want to set up a dbase with normalized tables for inserts,and a flattened table for selects, am i going in the right direction for speeding up a busy site? Are you familiar with views? If so, is there a reason not to use

Re: [GENERAL] Index problem.... GIST (tsearch2)

2004-10-07 Thread Greg Stark
Net Virtual Mailing Lists [EMAIL PROTECTED](by way of Net Virtual Mailing Lists [EMAIL PROTECTED]) writes: SELECT * from sometable WHERE category1 IS NOT NULL AND data_fti @@ to_tsquery('default', 'postgres'); What you can do is a partial index: create index fulltextindex on sometable

Re: [GENERAL] interfaces for python

2004-10-07 Thread Gaetano Mendola
Steven Klassen wrote: * Pierre-Frédéric Caillaud [EMAIL PROTECTED] [2004-10-07 10:45:57 +0200]: I'd advise psycopg as the fastest one (by a factor of 10x on large selects). I second this recommendation. Also because this interface is not affected by the idle in transaction, indeed as the last

Re: [GENERAL] 8.0 questions

2004-10-07 Thread Tom Lane
Robby Russell [EMAIL PROTECTED] writes: On Thu, 2004-10-07 at 14:48 -0400, Artem Litvinovich wrote: I would like to know what are the significant changes and new features introduced in Postgres 8.0 which warranted the major version number increment. I think that if you look in the postgresql

Re: [GENERAL] Index problem.... GIST (tsearch2)

2004-10-07 Thread Tom Lane
Greg Stark [EMAIL PROTECTED] writes: 8.0 will have statistics on how often is_null() will return false. But that isn't really going to solve your problem since it still won't have any idea how many rows the full text search will find. I don't even know of anything you can do to influence the

[GENERAL] CGI program cannot access database

2004-10-07 Thread Samik Raychaudhuri
Hello, I am trying to run a perl CGI program for accessing a database. The program runs fine from command prompt, producing expected output. But it gives errors while running as CGI. - Program - use DBI; use DBD::Pg; print Content-type: text/plain\n\n;

Re: [GENERAL] Index problem.... GIST (tsearch2)

2004-10-07 Thread Tom Lane
Net Virtual Mailing Lists [EMAIL PROTECTED] writes: I have a table like this with some indexes as identified: CREATE OR REPLACE FUNCTION is_null(anyelement) RETURNS BOOLEAN AS 'SELECT $1 IS NULL;' LANGUAGE 'SQL' IMMUTABLE; CREATE FUNCTION sometable_category1_idx ON sometable (category1);

Re: [GENERAL] Question from a newbie

2004-10-07 Thread Tom Lane
Kiarash Bodouhi [EMAIL PROTECTED] writes: I have two questions. First, is it possible to call other functions from plpython functions? I used following but didn't work. Any comments? CREATE OR REPLACE FUNCTION test4() RETURNS varchar AS ' return plpy.execute(select

Re: [GENERAL] flattened tables with normalized tables

2004-10-07 Thread Dennis Gearon
Michael Fuhr wrote: On Thu, Oct 07, 2004 at 10:07:47AM -0700, Dennis Gearon wrote: If I want to set up a dbase with normalized tables for inserts,and a flattened table for selects, am i going in the right direction for speeding up a busy site? Are you familiar with views? If so, is there a

Re: [GENERAL] Index problem.... GIST (tsearch2)

2004-10-07 Thread Net Virtual Mailing Lists
Thanks, you are right, I mis-typed the statements (lack of sleep *shrug*), thanks for parsing through it... Your suggestion did resolve the situation nicely! - Greg Net Virtual Mailing Lists [EMAIL PROTECTED] writes: I have a table like this with some indexes as identified: CREATE OR REPLACE

Re: [GENERAL] flattened tables with normalized tables

2004-10-07 Thread Michael Fuhr
On Thu, Oct 07, 2004 at 09:08:18PM -0700, Dennis Gearon wrote: About regular views, how does that speed things up, other than the initial SQL interpretation of the view not needing to be done? I didn't mean to imply that views would speed things up -- I was merely suggesting them as an

Re: [GENERAL] flattened tables with normalized tables

2004-10-07 Thread Dennis Gearon
Michael Fuhr wrote: On Thu, Oct 07, 2004 at 09:08:18PM -0700, Dennis Gearon wrote: About regular views, how does that speed things up, other than the initial SQL interpretation of the view not needing to be done? I didn't mean to imply that views would speed things up -- I was merely suggesting

Re: [GENERAL] flattened tables with normalized tables

2004-10-07 Thread Bruno Wolff III
On Thu, Oct 07, 2004 at 22:35:50 -0600, Michael Fuhr [EMAIL PROTECTED] wrote: On Thu, Oct 07, 2004 at 09:08:18PM -0700, Dennis Gearon wrote: About regular views, how does that speed things up, other than the initial SQL interpretation of the view not needing to be done? I didn't mean

Re: [GENERAL] flattened tables with normalized tables

2004-10-07 Thread Dennis Gearon
Bruno Wolff III wrote: On Thu, Oct 07, 2004 at 22:35:50 -0600, Michael Fuhr [EMAIL PROTECTED] wrote: On Thu, Oct 07, 2004 at 09:08:18PM -0700, Dennis Gearon wrote: About regular views, how does that speed things up, other than the initial SQL interpretation of the view not needing to be done? I

Re: [GENERAL] Question from a newbie

2004-10-07 Thread Michael Fuhr
On Thu, Oct 07, 2004 at 10:55:03PM -0400, Tom Lane wrote: return plpy.execute(select getcountrycode(\'9821788\'),1) plpy.execute() returns a result object; querying a function that returns a result object will probably look like this: test= select test4(); test4

[GENERAL] when to use NULL and when to NOT NULL DEFAULT ''

2004-10-07 Thread Miles Keaton
PG peeps: What's the prevailing wisdom best-practice advice about when to let a varchar (or any) column be NULL, and when to make it NOT NULL DEFAULT '' (or '-00-00' or whatever) - in PostgreSQL? {Moving to PG from MySQL where we were always advised to use NOT NULL to save a byte or

Re: [GENERAL] Index problem.... GIST (tsearch2)

2004-10-07 Thread Net Virtual Mailing Lists
Than you very much Andrew... Yes you are right.. I mis-typeed CREATE INDEX.. ;-) The actual create indexes are as you suggested: CREATE INDEX sometable_category1_idx ON sometable (is_null(category1)); CREATE INDEX sometable_category2_idx ON sometable (is_null(category2)); CREATE INDEX

DDL triggers [was Re: [GENERAL] database constraints]

2004-10-07 Thread David Fetter
On Thu, Oct 07, 2004 at 11:47:55AM +0700, David Garamond wrote: Joshua D. Drake wrote: This brings up an interesting idea. What if it were possible to set some kind of rules on DDL at database creation time? For example, I'd like to be able to throw an error if somebody tries to name an

Re: DDL triggers [was Re: [GENERAL] database constraints]

2004-10-07 Thread Tom Lane
David Fetter [EMAIL PROTECTED] writes: How big a project would it be to have generalized DDL triggers? Apart from resource allocation, what are some downsides of providing such a facility? BEFORE triggers on the system catalogs are Right Out. In principle we could support AFTER triggers,

Re: [HACKERS] DDL triggers [was Re: [GENERAL] database constraints]

2004-10-07 Thread Gavin Sherry
On Thu, 7 Oct 2004, David Fetter wrote: On Thu, Oct 07, 2004 at 11:47:55AM +0700, David Garamond wrote: Joshua D. Drake wrote: This brings up an interesting idea. What if it were possible to set some kind of rules on DDL at database creation time? For example, I'd like to be able to