[GENERAL] regarding select into

 Hello,   Is it not possible to write such an sql statement:   select ser into val1, count(*) into val2 from tab1 where id = $1;   do i need to perform the 2 selects separately ..wont that impact the performance?   in the table tab1, id is the primary key.   Thank You   Regards Surabhi Ahuja

Re: [GENERAL] Slow connection to the database

This is a known issue with Java 1.5 networking code, it tries to reverse look up the IP addresses in certain circumstances (which obviously occur in the JDBC driver) if you use IPs in your connection addresses. You're best off to make sure DNS is set up locally on your client machines for all the

Re: [GENERAL] regarding select into

surabhi.ahuja wrote: Hello, Is it not possible to write such an sql statement: select ser into val1, count(*) into val2 from tab1 where id = $1; do i need to perform the 2 selects separately ..wont that impact the performance? Well, considering id is your primary key and therefore very

[GENERAL] RI_ConstraintTrigger question

We have a database with about 30 tables and some RI. The RI constraints, however, were not named upon creation of the database 2-3 years ago and now when we get an error it contains for the constraint. I tried Google and the documentation, and I still have 2 questions - 1. Is it possible to r

[GENERAL] insertion becoming slow

i have seen that after insertion say (20 k rows) the insertion to tables becomes slow..why is it?   is there any way in which u can stop the performance from degrading

Re: [GENERAL] What is an 'unused item pointer'

On Sun, Sep 25, 2005 at 12:09:24AM -0400, Tom Lane wrote: > "Jim C. Nasby" <[EMAIL PROTECTED]> writes: > > On Sat, Sep 24, 2005 at 07:19:10PM -0400, Tom Lane wrote: > >> Currently, when a tuple is reclaimed by VACUUM, we just mark its item > >> pointer as unused (and hence recyclable). I think it

Re: [GENERAL] Data Entry Tool for PostgreSQL

On Fri, 23 Sep 2005, Ian Overton wrote: > Hello, > > We have a web application using PHP, Linux and PostgreSQL. We need a > simple data entry tool to allow non-IT people to edit data in our database > through a web-browser. Are there any free or commercial data management > tools out there, no

[GENERAL] Index use in BETWEEN statement...

Hello pals, I have the following table in Postgresql 8.0.1 Mydb# \d geoip_block Table "public.geoip_block" Column| Type | Modifiers -++--- locid | bigint | start_block | inet | end_block | inet | mydb# explain analyze select locid from geoip_bl

Re: [GENERAL] Function keys cause psql to segfault

CSN <[EMAIL PROTECTED]> writes: > Hmm, in putty (Terminal->Keyboard) I changed "the > function keys and keypad" from "ESC[n~" to "Linux". > Hitting F1-5 in psql outputs "ABCDE" - no segfaults! > Setting it to "Xterm R6" also results in function keys > 1-4 causing segfaults (there are also options

Re: [GENERAL] Data Entry Tool for PostgreSQL

On 9/25/05 8:49 PM, "Brent Wood" <[EMAIL PROTECTED]> wrote: > > > On Fri, 23 Sep 2005, Ian Overton wrote: > >> Hello, >> >> We have a web application using PHP, Linux and PostgreSQL. We need a >> simple data entry tool to allow non-IT people to edit data in our database >> through a web-brows

Re: [GENERAL] Index use in BETWEEN statement...

On 9/26/05 11:26 AM, "Cristian Prieto" <[EMAIL PROTECTED]> wrote: > > Hello pals, I have the following table in Postgresql 8.0.1 > > Mydb# \d geoip_block > Table "public.geoip_block" > Column| Type | Modifiers > -++--- > locid | bigint | > start_block | i

[GENERAL] could not send data to server: Operation not supported

Hi: A PHP script that is running every 30 minutes for three years has crashed when updating a Postgres database table with this error message: "pg_exec() query failed: could not send data to server: Operation not supported" I just ran it again and it worked fine. I could not find a single

[GENERAL] Error migrating from 7.4.3 to 8.0.3

Hi, We are trying to migrate our database from 7.4.3 of Postgresql to 8.0.3. While migrating a table, we got the following error: ERROR: could not write block 2830 of relation 1663/2276041/4965853: Operation not permitted This came when pg was trying to create an index on the relation

Re: [GENERAL] Slow search.. quite clueless

Oleg Bartunov wrote: contrib/tsearch2 ( http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/ ) might works for you. It might because performance depends on cardinality of your keywords. Oleg On Tue, 20 Sep 2005, Yonatan Ben-Nes wrote: Hi all, Im building a site where the users can s

Re: [GENERAL] Error migrating from 7.4.3 to 8.0.3

Raj Gupta wrote: Hi, We are trying to migrate our database from 7.4.3 of Postgresql to 8.0.3. While migrating a table, we got the following error: ERROR: could not write block 2830 of relation 1663/2276041/4965853: Operation not permitted This came when pg was trying to create an index

Re: [GENERAL] Error migrating from 7.4.3 to 8.0.3

On Sep 26, 2005, at 12:43 PM, Raj Gupta wrote: While migrating a table, we got the following error: ERROR: could not write block 2830 of relation 1663/2276041/4965853: Operation not permitted This came when pg was trying to create an index on the relation. Has anyone seen this behavi

Re: [GENERAL] Error migrating from 7.4.3 to 8.0.3

Yes, I followed the procedure for dumping and restoring binary data (to include large objects). It creates all the tables, etc. fine, and all the other indices are created. It's just this one index that it gives me a problem. The problem also happens if I manually try to create the same index

Re: [GENERAL] Data Entry Tool for PostgreSQL

On Sep 26, 2005, at 11:45 AM, Sean Davis wrote: These types of applications are typically called "CRUD" applications (Create, Read, Update, and Delete). Perl, Ruby, and Java, at least, have frameworks for building CRUD applications in a fairly straightforward (depending on needs and customi

Re: [GENERAL] Error migrating from 7.4.3 to 8.0.3

This is both while doing a pg_restore and also when doing a 'create index' command. This is running on Mac OS X 10.3 Postgresql 7.4 runs fine, and creates the index, so I would be surprised if this is a hardware error? Raj On Sep 26, 2005, at 10:23 AM, Vivek Khera wrote: On Sep 26, 200

Re: [GENERAL] insertion becoming slow

On Mon, Sep 26, 2005 at 05:55:18PM +0530, surabhi.ahuja wrote: > i have seen that after insertion say (20 k rows) the insertion to tables > becomes slow..why is it? Most likely due to indexes. > is there any way in which u can stop the performance from degrading If you're loading from scratch,

Re: [GENERAL] Index use in BETWEEN statement...

mydb=# explain analyze select locid from geoip_block where '216.230.158.50'::inet between start_block and end_block; QUERY PLAN ---

Re: [GENERAL] Replicating new sequences

On 9/22/2005 3:54 PM, Todd Eddy wrote: I know this gets asked all the time, but I'm having issues with replication. I got Slony setup between two computers and that does replication of transactions. But we have a table that because of how it works new sequences are added on a somewhat reg

Re: [GENERAL] SQL command to dump the contents of table failed: PQendcopy()

Thanks Tom and Michael: Michael Fuhr wrote: On Fri, Sep 23, 2005 at 05:11:19PM +0200, ruben wrote: The operating system is Red Hat Linux release 8.0 (Psyche) and PostgreSQL version is 7.4.6., without non-standard extensions. I cannot find any core dump in the PGDATA directory /usr/local/pgs

Re: [GENERAL] Slow search.. quite clueless

On Mon, 26 Sep 2005, Yonatan Ben-Nes wrote: Hi again everyone, Oleg I tried tsearch2 and happily it does work wonderfully for me returning results extremly fast and actually its working even better then I wanted with all of those neat features like: lexem, weight & stop words. I got only on

Re: [GENERAL] SQL command to dump the contents of table failed: PQendcopy()

ruben <[EMAIL PROTECTED]> writes: >> As I recall, released versions of PostgreSQL usually dump core under >> $PGDATA/base/. However, it's also possible that your >> coredumpsize resource limit prevents core dumps; you could fix that >> by putting a command like "ulimit -c unlimited" in your Postgr

Re: [GENERAL] Error migrating from 7.4.3 to 8.0.3

Raj Gupta <[EMAIL PROTECTED]> writes: > Postgresql 7.4 runs fine, and creates the index, so I would be > surprised if this is a hardware error? "Operation not permitted" during a write() is not very sensible; the kernel should have complained when the file was open()'d if there were permission is

Re: [GENERAL] Error migrating from 7.4.3 to 8.0.3

This is a stock Mac OS 10.3 (Panther). Has anyone else seen this problem with 8.0.3 on Panther? According to the INSTALL file 10.3 should work fine, as reported by Andrew Rawnsley, cc'd on this message. Raj On Sep 26, 2005, at 12:23 PM, Tom Lane wrote: "Operation not permitted" during a wr

Re: [GENERAL] How many insert + update should one transaction handle?

Robert Treat wrote: On Fri, 2005-09-23 at 14:53, Dawid Kuroczko wrote: On 9/23/05, Yonatan Ben-Nes < [EMAIL PROTECTED] > wrote: Hi all, Every few days I need to DELETE all of the content of few tables and INSERT new data in them. The amount of new data is about 5 mi

Re: [GENERAL] Index use in BETWEEN statement...

"Cristian Prieto" <[EMAIL PROTECTED]> writes: > mydb=# explain analyze select locid from geoip_block where > '216.230.158.50'::inet between start_block and end_block; > As you see it still using a sequential scan in the table and ignores the > index, any other suggestion? That two-column index is

Re: [GENERAL] Data Entry Tool for PostgreSQL

> I would have thought a php appliction would be able to this fairly > easily. My data entry scripts insert the new records with just a primary > key, then iterate through the various fields using an update sql for each > one which is not null. A generalized program to do this for (nearly) any tab

Re: [GENERAL] SQL command to dump the contents of table failed: PQendcopy()

On Mon, Sep 26, 2005 at 07:03:06PM +0200, ruben wrote: > > I guess I'm doing something wrong: > > -bash-2.05b$ ulimit > unlimited Please read manpages, in this case bash: ulimit -a Peter ---(end of broadcast)--- TIP 9: In versions below 8.0, the

Re: [GENERAL] Slow search.. quite clueless

Oleg Bartunov wrote: On Mon, 26 Sep 2005, Yonatan Ben-Nes wrote: Hi again everyone, Oleg I tried tsearch2 and happily it does work wonderfully for me returning results extremly fast and actually its working even better then I wanted with all of those neat features like: lexem, weight & stop

Re: [GENERAL] insertion becoming slow

On Mon, 2005-09-26 at 12:58, Jim C. Nasby wrote: > On Mon, Sep 26, 2005 at 05:55:18PM +0530, surabhi.ahuja wrote: > > i have seen that after insertion say (20 k rows) the insertion to tables > > becomes slow..why is it? > > Most likely due to indexes. > > > is there any way in which u can stop t

Re: [GENERAL] How many insert + update should one transaction handle?

On Mon, Sep 26, 2005 at 08:41:03PM +0200, Yonatan Ben-Nes wrote: > Anyway I saw the idea: > BEGIN; > CREATE new_table; > SELECT INTO new_table * FROM temp_table; > DROP TABLE table; > ALTER TABLE new_table RENAME TO table; > COMMIT; > > Where if I understood correctly "table" is the final table,

Re: [GENERAL] Index use in BETWEEN statement...

Cristian Prieto wrote: mydb=# explain analyze select locid from geoip_block where '216.230.158.50'::inet between start_block and end_block; QUERY PLAN -

[GENERAL] [SQL] add column if doesn't exist (fwd)

I figured pgsql-general would be a more appropriate forum for the question below. -- Brandon -- Forwarded message -- Date: Mon, 26 Sep 2005 15:38:56 -0500 (CDT) From: "Metcalf, Brandon [SC100:CM21:EXCH]" <[EMAIL PROTECTED]> To: pgsql-sql@postgresql.org Subject: [SQL] add column i

[GENERAL] c-language function .h file changes between (major) versions

I've been developing c-language functions in 7.4.5 and 7.4.8. I had not encountered a need to recompile, because of pg .h file, or other differences. I expected a need to do so when upgrading to 8.0.3. But I forgot to do so and it worked!! I figured I lucked out, a suspicion which was confirmed

Re: [GENERAL] c-language function .h file changes between (major) versions

"TJ O'Donnell" <[EMAIL PROTECTED]> writes: > So, my question is: Is there some hard and fast rule about when > one needs to recompile c-language functions from one pg version to > another? Always for a major version change? minor? I suppose > the wise thing is to recompile with ANY pg version ch

[GENERAL] Can't change language through LC_MESSAGES

Hello all, I'm trying to change the server messages back to english (initdb created 'German_Germany.1251' because I'm running a German Windows) by setting the lc_messages property to 'C'. But the messages from e.g. pgsql or pg_ctl still show up in German. I reloaded the config and I restarted

Re: [GENERAL] could not send data to server: Operation not supported

"ruben" <[EMAIL PROTECTED]> wrote > > A PHP script that is running every 30 minutes for three years has crashed > when updating a Postgres database table with this error message: > > "pg_exec() query failed: could not send data to server: Operation not > supported" > > I just ran it again and i

Re: [GENERAL] [SQL] add column if doesn't exist (fwd)

""Brandon Metcalf"" <[EMAIL PROTECTED]> wrote > > Is there a way to check for the existence of a column in a table other > than, say, doing a SELECT on that column name and checking the output? > Take a look at pg_attribute system table. http://www.postgresql.org/docs/8.0/static/catalog-pg-attri

Re: [GENERAL] Can't change language through LC_MESSAGES

Thomas Kellerer <[EMAIL PROTECTED]> writes: > I'm trying to change the server messages back to english (initdb created > 'German_Germany.1251' because I'm running a German Windows) by setting > the lc_messages property to 'C'. But the messages from e.g. pgsql or > pg_ctl still show up in German.

Re: [GENERAL] Can not get ODBC to log. Trace is not working.

"Roy Souther" <[EMAIL PROTECTED]> wrote >I am trying to get the ODBC client to log but I just can't. I have >included my odbc.ini and odbcinst.ini. Can anyone tell me why there is >never a log file created? I connect to the database with isql and >OpenOffice, run queries and get data but never do

Re: [GENERAL] How many insert + update should one transaction handle?

On 9/27/05, Jim C. Nasby <[EMAIL PROTECTED]> wrote: On Mon, Sep 26, 2005 at 08:41:03PM +0200, Yonatan Ben-Nes wrote:> Anyway I saw the idea:> BEGIN;> CREATE new_table;> SELECT INTO new_table * FROM temp_table;> DROP TABLE table; Instead of dropping it here, just rename to a different name and  the

Re: [GENERAL] RI_ConstraintTrigger question

On 9/26/05, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wro We have a database with about 30 tables and some RI. The RI constraints,however, were not named upon creation of the database 2-3 years ago andnow when we get an error it contains for the constraint. I tried Google and the documentation, and I

[GENERAL] insertion becoming slow

 thanks for the help.. i removed a few indexes, but i cant remove the primary key and uniqye key constraints ..whoch means that indexes will be made on these 2 attributes.   i have four tables, each having such 2 attributes, one which is the primary key and the other is the uniqu key.   and the