Re: [GENERAL] Problem with libpq3 & postgresql8

vishal saberwal wrote: > hi, > tom and many others helped me with someting similar to this. > > Check for the following: > (a) in /usr/lib check what libpq you have installed. It should be > libpq3.2 or higher. If it is not, you can copy from your compiled source > and then create the symbolic lin

Re: [GENERAL] Problem with libpq3 & postgresql8

Tom Lane wrote: > "Alex R. Mosteo" <[EMAIL PROTECTED]> writes: > >>About the certificate thing, I'm not sure what you mean, but I've >>configured the pg_hba.conf file useing hostnossl. I see this line in the >>server log: >>LOG: no se pudo aceptar una conexión SSL: se detectó EOF >>Which translat

Re: [GENERAL] Postgres locks table schema?

Hi. I will give it a try. Although, I do remember that the lock occurs on the first transaction - so basically there should be nothing to commit yet? Thanks, Stas. -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 21, 2005 9:35 PM To: Stas Oskin Cc:

Re: [GENERAL] Win32 Backup and Restore of large databases.

Lincoln Yeoh wrote: At 12:24 PM 9/21/2005 +0100, Howard Cole wrote: On a Win32 machine, can I backup a database if the backup file exceeds 2GB? In linux, I can split the backup file into multiple files. Can this be done on Win32? Of course you can split the backup file into multiple files o

[GENERAL] date_trunc('week', '2005-01-01'::TIMESTAMP)

select date_trunc('week', '2005-01-01'::TIMESTAMP) returns '2006-01-02 00:00:00' is this the intended result or is some kind of a bug ? the result should not be '2004-12-27 00:00:00' ? thanks, Razvan Radu ---(end of broadcast)--- TIP 2: Don't

Re: [GENERAL] date_trunc('week', '2005-01-01'::TIMESTAMP)

am 22.09.2005, um 12:40:00 +0300 mailte [EMAIL PROTECTED] folgendes: > > select date_trunc('week', '2005-01-01'::TIMESTAMP) returns '2006-01-02 > 00:00:00' Which version? test=# select date_trunc('week', '2005-01-01'::TIMESTAMP); date_trunc - 2004-12-27 00:00:00 (1 ro

Re: [GENERAL] Problem with libpq3 & postgresql8

"Alex R. Mosteo" <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> What's the platform exactly, and exactly which PG 8.0.* release are you >> using? For that matter, are you sure you are linking your program to >> the 8.0 libpq.so, and not still the 7.4 one? > Server is 8.0.3, client is in another

Re: [GENERAL] Slow search.. quite clueless

Dawid Kuroczko wrote: On 9/20/05, *Yonatan Ben-Nes* <[EMAIL PROTECTED] > wrote: Hi all, Im building a site where the users can search for products with up to 4 diffrent keywords which all MUST match to each product which found as a result to the search

Re: [GENERAL] Slow search.. quite clueless

Yonatan Ben-Nes wrote: Dawid Kuroczko wrote: Hmm, JOIN on a Huge table with LIMIT. You may be suffering from the same problem I had: http://archives.postgresql.org/pgsql-performance/2005-07/msg00345.php Tom came up with a patch which worked marvellous in my case: http://archives.postgresql.o

[GENERAL] ORDER BY results

Hi, I have a query which presents results sorted using the SQL "ORDER BY... LIMIT". Now my question is... if the column that the order refers to has some repeated data, will the order of results always be the same? For instance, say I have a table as follows: create table example {id seria

Re: [GENERAL] Cost based SELECT/UPDATE

On 9/7/2005 10:45 PM, Leonid Safronie wrote: Hi, ppl Is there any way to do SELECTs with different priorities? Once a month I need to do some complex reports on table with over 7 billion rows, which implies several nested SELECTS and grouping (query runs over 20 minutes on P4/2.4GHz). Concurre

Re: [GENERAL] date_trunc('week', '2005-01-01'::TIMESTAMP)

On Thu, Sep 22, 2005 at 12:20:58PM +0200, A. Kretschmer wrote: > am 22.09.2005, um 12:40:00 +0300 mailte [EMAIL PROTECTED] folgendes: > > select date_trunc('week', '2005-01-01'::TIMESTAMP) returns '2006-01-02 > > 00:00:00' > > Which version? This bug was fixed in 8.0.2: http://archives.postgre

[GENERAL] wrong protocol sequence?

Hello! I'm sending such messages to server in native postgresql protocol: 'Parse' -> 'Describe' -> 'Sync' (get: 'Parse Complete' and 'Ready For Query' messages back). But when the sequence became: 'Parse' -> 'Sync' (get: 'Parse Complete' and 'Ready For Query' messages back), little pause, '

Re: [GENERAL] wrong protocol sequence?

On Sep 22, 2005, at 10:11 AM, Андрей wrote: I'm sending such messages to server in native postgresql protocol: 'Parse' -> 'Describe' -> 'Sync' (get: 'Parse Complete' and 'Ready For Query' messages back). But when the sequence became: 'Parse' -> 'Sync' (get: 'Parse Complete' and 'R

Re: [GENERAL] [Re] wrong protocol sequence?

John DeSoi wrote: On Sep 22, 2005, at 10:11 AM, Андрей wrote: I'm sending such messages to server in native postgresql protocol: 'Parse' -> 'Describe' -> 'Sync' (get: 'Parse Complete' and 'Ready For Query' messages back). But when the sequence became: 'Parse' -> 'Sync' (get: 'Parse

Re: [GENERAL] ORDER BY results

On Thu, Sep 22, 2005 at 02:19:00PM +0100, Howard Cole wrote: > I have a query which presents results sorted using the SQL "ORDER BY... > LIMIT". Now my question is... if the column that the order refers to > has some repeated data, will the order of results always be the same? Not necessarily

Re: [GENERAL] ORDER BY results

Howard Cole <[EMAIL PROTECTED]> writes: > I have a query which presents results sorted using the SQL "ORDER BY... > LIMIT". Now my question is... if the column that the order refers to > has some repeated data, will the order of results always be the same? No. You need to add more columns to

Re: [GENERAL] wrong protocol sequence?

=?UTF-8?B?0JDQvdC00YDQtdC5?= <[EMAIL PROTECTED]> writes: > I'm sending such messages to server in native postgresql protocol: > 'Parse' -> 'Describe' -> 'Sync' (get: 'Parse Complete' and 'Ready For > Query' messages back). > But when the sequence became: 'Parse' -> 'Sync' (get: 'Pars

Re: [GENERAL] array_dims array_lower/upper distance

On Wed, 2005-21-09 at 07:48 -0700, Tony Wasson wrote: > On 9/20/05, Matthew Peter <[EMAIL PROTECTED]> wrote: > > Wondering if there's a way for postgres to return how > > many elements are in a array as a single integer? For > > instance, returning 10 (items in array) instead of > > [-5:4] > > > >

Re: [GENERAL] Network Flow Schema + Bulk Import/Updates

On 9/21/05, Michael L. Artz <[EMAIL PROTECTED]> wrote: > Tony Wasson wrote: > > >You can use the merge trigger below to do this. You'll need to add > >some code to update the count. You may also benefit from using the new > >constraint exclusion (table partitioning) in PostgreSQL 8.1. I am not > >s

Re: [GENERAL] array_dims array_lower/upper distance

Guy Fraser <[EMAIL PROTECTED]> writes: > So to answer his question he would likely want : > > SELECT > array_upper(item,1) - array_upper(item,0) + 1 as elements > FROM > arraytest ; Note that this doesn't work for empty arrays. It will return NULL instead of 0. -- greg --

[GENERAL] Lines of code in PG 8.0 & 8.1?

Hello I am preparing a presentation about PostgreSQL and I need some help. I have found some information [1] about the lines of code the different versions (until 7.4) of PostgreSQL have, but I can not find numbers for version 8.0 and 8.1. This is what I have: 1996: 6.0 - 178K 1997: 6.[12] - 22

[GENERAL] Referencing columns in a record (or dereferencing variables)

In plpgsql, is it possible to indirectly reference a column in a record object? For instance, in a trigger function I've got a variable, v_column_name containing (predictably) a column name in existing in OLD and NEW. I want to reference that column. This of course doesn't work: IF OLD.

Re: [GENERAL] [Re] wrong protocol sequence?

On Sep 22, 2005, at 11:16 AM, Андрей wrote: The reason is to get parameters description (if there are any in query) before binding them to the backend. For example 'select * from table_name where column_name = $1'. I would like to get required type of parameter $1 to bind later. But I c

[GENERAL] database update question

I have a psql database (8.01) running on a Suse 9.3 linux server, and I have the same database (8.03) running on winxp laptop. I do updates, that is, add parts to a table on the linux server quite frequently using MS Access, all well and good. I also have pgadmin 3 on my machine for editing the

Re: [GENERAL] database update question

Best bet is probably either dump and restore or shipping transaction logs. Slony is another option, but it's probably overkill. On Thu, Sep 22, 2005 at 11:19:14AM -0700, Art Fore wrote: > I have a psql database (8.01) running on a Suse 9.3 linux server, and I > have the same database (8.03) runni

Re: [GENERAL] Lines of code in PG 8.0 & 8.1?

Try searching -hackers; I believe it's been discussed there. On Thu, Sep 22, 2005 at 06:53:06PM +0200, Rafael Martinez wrote: > Hello > > I am preparing a presentation about PostgreSQL and I need some help. > > I have found some information [1] about the lines of code the different > versions (

[GENERAL] Questions about Rollback - after insert, update, delete ... operations?

greetings, I remembered I read something in the mailing list about "*rollback*" a while ago. People mentioned that some operations cannot rollback. I cannot remember what kinds of perations are not be able to rollback? For example, begin ... ... insert ... ... delete ... ...

Re: [GENERAL] Questions about Rollback - after insert, update,

On Thu, 2005-09-22 at 14:20, Emi Lu wrote: > greetings, > > I remembered I read something in the mailing list about "*rollback*" a > while ago. People mentioned that some operations cannot rollback. > I cannot remember what kinds of perations are not be able to rollback? create database and drop

[GENERAL] Replicating new sequences

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 regular basis, maybe a couple times a day.

Re: [GENERAL] Questions about Rollback - after insert, update,

We are using (struts) ibates to run the transaction. We already setup autocommitte = false, and put insert, update, delete into one transaction. However, we found data were not rollback successfully. Moreover, in our atomic transaction, some operations are not finished successfull, but the da

Re: [GENERAL] Questions about Rollback - after insert, update,

On Thu, 2005-09-22 at 14:59, Emi Lu wrote: > We are using (struts) ibates to run the transaction. We already setup > autocommitte = false, and put insert, update, delete into one > transaction. However, we found data were not rollback successfully. > Moreover, in our atomic transaction, some ope

Re: [GENERAL] array_dims array_lower/upper distance

On Thu, 2005-22-09 at 12:43 -0400, Greg Stark wrote: > Guy Fraser <[EMAIL PROTECTED]> writes: > > > So to answer his question he would likely want : > > > > SELECT > > array_upper(item,1) - array_upper(item,0) + 1 as elements > > FROM > > arraytest ; > > Note that this doesn't work for empty a

Re: [GENERAL] Questions about Rollback - after insert, update,

If ibatis can catch the exception in the program, rollback will work fine. Could the failure of the transaction in ibatis because of the network lost or tomcat server shutting down during the procedure. For instance, in java program set autocommit = false; startTranaction insert ...

[GENERAL] Getting the amount of overlap when using OVERLAPS

Given 2 date ranges, the overlaps functions returns TRUE or FALSE. I want to find the # of days that are overlapping. Is there a "built in" way to do this? Should I just write a function to do it? For instance, this example overlaps, but I want to know how much does it overlap? SELECT (DATE '200

Re: [GENERAL] Need for java based web admin tool

On 22.09.2005 20:41, Jobu wrote: Is there a java based web admin or is it just a dream and there is no java analog for phpPgAdmin? There is none to my knowledge. Java client side frontends such as Aqua Data Studio (http://www.aquafold.com/) exist.. ..which runs in Java Application Server (

Re: [GENERAL] FATAL: the database system is starting up

On 21.09.2005 16:16, Keve Nagy wrote: postgres[432]: [2-1] FATAL: the database system is starting up I guess it means that something tried to connect to early during the startup of the DBMS. This may easily happen if you have shut down the DBMS but the clients are still trying.. nothing to w

[GENERAL] Finding (and deleting) dupes in relation table

I have a table that relates id's of two other tables: table1id, table2id Dupes have found their way into it (create unique index across both fields fails). Is there a quick and easy way to find and delete the dupes (there are tens of thousands of records)? Thanks, CSN

Re: [GENERAL] connecting windows xp to remote server

On 21.09.2005 18:05, Jeanne Thibeault wrote: Apparently I need to be able to send my password as md5 encrypted, The ODBC driver should do that for you, you don't need to hash it yourself. -- Regards, Hannes Dorbath ---(end of broadcast)--- TIP 5:

Re: [GENERAL] Lines of code in PG 8.0 & 8.1?

For 8.1beta2 I get: 684533 and for 8.0.X I get: 648130 I used: # This script is used to compute the total number of "C" lines in the release # This should be run from the top of the CVS tree after a 'make distclean' find . -name '*.[chyl]' | xargs cat

Re: [GENERAL] returning the primary key value

hi, If this primary key is a guid or a number that you create as unique, create it local to your function and then return the same after inserting within the function. create or replace function test_insert() returns guid as $$ DECLARE  ret guid; BEGIN    select into ret newid();    -- ret is the

Re: [GENERAL] Getting the amount of overlap when using OVERLAPS

create or replace function test1() returns text as $$  DECLARE     mm varchar;     ma bool; BEGIN     SELECT into ma (DATE '2001-02-16', DATE '2001-12-21') OVERLAPS (DATE '2001-10-30', DATE '2002-10-30');     if (ma) then         select into mm  (age(DATE '2001-02-16', DATE '2001-12-21')-age(DATE

Re: [GENERAL] Finding (and deleting) dupes in relation table

Nevermind, figured it out: select distinct on (table1id, table2id) * into temp from table3; delete from table3; insert into table3 select * from temp; --- CSN <[EMAIL PROTECTED]> wrote: > I have a table that relates id's of two other > tables: > > table1id, table2id > > Dupes have found the

Re: [GENERAL] PQtrace doesn't work

W. van den Akker wrote: > Hello, > > I've send this message also on 29-1-2004 and have since no solution for > this problem .. >:o . > I have a little test program (see at the end of the message). > The program crashes when PQTrace is called (instruction referenced > memory at "0x0010",

Re: [GENERAL] array_dims array_lower/upper distance

On Thu, Sep 22, 2005 at 14:16:48 -0600, Guy Fraser <[EMAIL PROTECTED]> wrote: > On Thu, 2005-22-09 at 12:43 -0400, Greg Stark wrote: > > Guy Fraser <[EMAIL PROTECTED]> writes: > > > > > So to answer his question he would likely want : > > > > > > SELECT > > > array_upper(item,1) - array_upper(

Re: [GENERAL] Lines of code in PG 8.0 & 8.1?

Is there some reasonable way to get that to not include comments? On Thu, Sep 22, 2005 at 06:04:55PM -0400, Bruce Momjian wrote: > > For 8.1beta2 I get: > > 684533 > > and for 8.0.X I get: > > 648130 > > I used: > > # This script is used to compute the total number of "C" l

Re: [GENERAL] Questions about Rollback - after insert, update, delete ... operations?

On Thu, Sep 22, 2005 at 15:20:17 -0400, Emi Lu <[EMAIL PROTECTED]> wrote: > greetings, > > I remembered I read something in the mailing list about "*rollback*" a > while ago. People mentioned that some operations cannot rollback. > I cannot remember what kinds of perations are not be able to ro

[GENERAL] Redhat 9 RPM dependency problem

Hi, If I run 'rpm -qp --requires postgresql-libs-8.0.3-1PGDG.i686.rpm' on a freshly downloaded RH9 rpm, I get: /sbin/ldconfig /sbin/ldconfig libc.so.6 libc.so.6(GLIBC_2.0) libc.so.6(GLIBC_2.1) libc.so.6(GLIBC_2.1.3) libc.so.6(GLIBC_2.2) libc.so.6(GLIBC_2.3) libcom_err.so.2 libcrypt.so.1 libcryp

[GENERAL] Need for java based web admin tool

Hello, I would like to admin PostgreSQL databases by using web interface which runs in Java Application Server (Tomcat). But I couldn't find any admin tool deployd as WAR file. Is there a java based web admin or is it just a dream and there is no java analog for phpPgAdmin? Jaanus

Re: [GENERAL] Redhat 9 RPM dependency problem

Hi, On Thu, 22 Sep 2005, [EMAIL PROTECTED] wrote: If I run 'rpm -qp --requires postgresql-libs-8.0.3-1PGDG.i686.rpm' on a freshly downloaded RH9 rpm, I get: Notice libpq.so.3. and libecpg.so.4 (of the 7.x series). This means that 8.0.3 declares itself dependent on the earlier version of