Re: [GENERAL] Problem restoring from backup file

try to give output file path in FILENAME text box and select onw of the option in PLAIN OPTIONS FRAME. It works on my machine. Hope this will help :) With Regards Ashish - Original Message - From: "Rohit Prakash Khare" <[EMAIL PROTECTED]> To: Sent: Sunday, April 01, 2007 10:58 PM Su

Re: [GENERAL] inserting multiple values in version 8.1.5

[EMAIL PROTECTED] wrote: Hi I am trying to insert multiple values into a table like this. INSERT INTO tab_name (col1, col2) VALUES (val1, val2), (val3, val4) This works in postgres version 8.2.1 My production server runs in 8.1.5. It gives me ERROR: syntax error at or near "," at character 35

[GENERAL] inserting multiple values in version 8.1.5

Hi I am trying to insert multiple values into a table like this. INSERT INTO tab_name (col1, col2) VALUES (val1, val2), (val3, val4) This works in postgres version 8.2.1 My production server runs in 8.1.5. It gives me ERROR: syntax error at or near "," at character 35 What to do? thanks

Re: [GENERAL] documentation generator for pgsql

On Apr 2, 2007, at 4:50 PM, jezemine wrote: Just a quick note to let you all know about a shareware app I wrote called SqlSpec that will generate documentation for any PostgreSQL 8.0 and up database. As far as I know, this is the only doc generator tool on the market that works against Postgre

Re: [GENERAL] Using C# to create stored procedures

Andrus wrote: Last change for this project was 3 years ago. So I think that it is dead. I'm writing application in C#. I expected that I can wrote stored procedures in C# also using something like mod_mono in Apache. So it seems that most reasonable way is to learn dreaded plpgsql language

Re: [GENERAL] Unexplained case insensitive results

Hi Lloyd, Please ignore searches on names that are in lower case. The PBSI-EMR will search only on first and last names that are in upper case. In this way, we are synchronized with PBSI-DOC which uses and sends only upper case names, and we avoid searching problems. Teresa and I have discussed

[GENERAL] ECPG: inserting NULL values when using prepared statements

The situation: A loop that inserts thousands of values into a table. In hopes of optimizing the bunches of inserts, I prepared a statement like the follows: "insert into some_table_name ( value, id, date ) values ( ?, ?, ? )" then executing the prepared statement identifier numero

[GENERAL] documentation generator for pgsql

Just a quick note to let you all know about a shareware app I wrote called SqlSpec that will generate documentation for any PostgreSQL 8.0 and up database. As far as I know, this is the only doc generator tool on the market that works against PostgreSQL. In addition to PostgreSQL, it also support

[GENERAL] pg_live

Hi everyone, I can't find a pg_live mailing list, so hopefully someone here might have some experience with this. I've tried burning the ISO twice now with the exact same result. The checksum after downloading is fine, and the burn completes without errors, but when I boot: 1) If I use the 'che

Re: [GENERAL] Using C# to create stored procedures

Last change for this project was 3 years ago. So I think that it is dead. I'm writing application in C#. I expected that I can wrote stored procedures in C# also using something like mod_mono in Apache. So it seems that most reasonable way is to learn dreaded plpgsql language and write stor

Re: [GENERAL] Working between Windows and Unix

On 02/04/2007 19:51, Erick Papadakis wrote: How can I find the path for pg_dump? You mean on Windows or on Linux? I am unable to make the "dump" or SQL backup from my Windows machine to begin with! On my laptop (WinXP) it's in c:\program files\postgresql\8.2\bin. I've added this to my system

Re: [GENERAL] SQLConnect failure

> Original Message > Subject: Re: [GENERAL] SQLConnect failure > From: Bill Moran <[EMAIL PROTECTED]> > Date: Mon, April 02, 2007 2:54 pm > To: [EMAIL PROTECTED] > Cc: pgsql-general@postgresql.org > > In response to [EMAIL PROTECTED]: > > > We have code that has been using MSD

Re: [GENERAL] SQLConnect failure

In response to [EMAIL PROTECTED]: > We have code that has been using MSDE/SQL Server successfully for years, > and are adding Postgres support. Doing a SQLConnect to connect to a > local Postgres server works fine, but if we try to connect to a remote > system, the SQLConnect fails, and we get an

Re: [GENERAL] SQLConnect failure

On Apr 2, 2007, at 4:36 PM, [EMAIL PROTECTED] wrote: We have code that has been using MSDE/SQL Server successfully for years, and are adding Postgres support. Doing a SQLConnect to connect to a local Postgres server works fine, but if we try to connect to a remote system, the SQLConnect fail

[GENERAL] SQLConnect failure

We have code that has been using MSDE/SQL Server successfully for years, and are adding Postgres support. Doing a SQLConnect to connect to a local Postgres server works fine, but if we try to connect to a remote system, the SQLConnect fails, and we get an error code that seems to indicate "The val

Re: [GENERAL] best way to calculate accumulating intervals timestamps

> Management wants to know A+B over the course of multiple days and the start > and finish times can occur in arbitrary times. Any ideas for quickest way to > solve this problem? I know I can do it the dirty way in perl or whatever, > but I was hoping for a solution in SQL/PLSQL. Without using a

Re: [GENERAL] Importing data into views fails

Karl O. Pinc wrote: I've created an INSERT rule on a view but when I try to use the COPY command to import data into the view it fails with. cannot copy to view "viewname" If the day was 24hours earlier, and Mr. Pinc was asking about some problem with his "frail loop" view update, I would

[GENERAL] best way to calculate accumulating intervals timestamps

I'm hoping there is some way to do this without writing a function, but there may be no way around it. I have a table that contains two epoch based time stamps, 'start' and 'finish'. They are both timestamp values that are data wherehoused for resource utilization monitoring. Management wants t

Re: [GENERAL] VACUUM FULL takes long time to complete

On Mar 31, 2007, at 5:15 PM, Martijn van Oosterhout wrote: Firstly, with autovacuum you really don't need to be running VACUUM FULL anyway. VACUUM FULL is actually quite inefficient, you're better off having higher FSM settings and leaving it to autovacuum. Thank you (and Bill Moran) for the r

Re: [GENERAL] Importing data into views fails

"Karl O. Pinc" <[EMAIL PROTECTED]> writes: > Is there some reason why COPYing into > views is disallowed or is there just an > extra error check in the code that's getting > in my way? If so, would the project be > open to accepting a patch? COPY doesn't fire rules. Feel free to send a patch, bu

Re: [GENERAL] Working between Windows and Unix

On 4/2/07, Russell Smith <[EMAIL PROTECTED]> wrote: Erick Papadakis wrote: > Hello: > > I am working on Windows and Unix with Postgresql (newbie, so please be > kind). I use phppgadmin on both platforms. Windows is my local > machine. Linux (CentOS as usual) is the cpanel thing that my hosting >

[GENERAL] Importing data into views fails

Hi, I've created an INSERT rule on a view but when I try to use the COPY command to import data into the view it fails with. cannot copy to view "viewname" I can create a temporary table and COPY into the temporary table and then INSERT into the view by selecting from the temporary table. Bu

Re: [GENERAL] Using C# to create stored procedures

On Fri, Mar 30, 2007 at 12:19:44PM +0300, Andrus wrote: > Any idea how to write server-side stored procedures in C# for > PostgreSQL database ? There's an old project called PL/Mono , but as far as I know it's unmaintained. You might wa

[GENERAL] Webappication and PostgreSQL login roles

Hi, I designed a Java web application. The persistence layer is a PostgreSQL database. The application needs user authentication. I think it's a good choice to implement this authentication mechanism via PostgreSQL login roles. So I can create several database login roles and set the database

Re: [GENERAL] Unexplained case insensitive results

"Lloyd Mason" <[EMAIL PROTECTED]> writes: > There appears to be a difference in the results from the following SQL > statement when run on an 8.1.5 version versus a 8.1.8 version: I imagine you initdb'd the two installations with different locale settings. Check "show lc_collate".

Re: [GENERAL] [ADMIN] Increasing the shared memory

There is also a add on in contrib (pg_buffercache) that can be used to give an indication of the number of buffers in use, this can be used to help find a 'good' shared mem size for your configuration. David. ---(end of broadcast)--- TIP 4: Have

Re: [GENERAL] Deleted Flag/Unique Constraint

Yeah, personally, I'm not entire 100% convinced of the concept myself and am actively investigating alternatives. Most of what I'm doing right now is simple research, as well as trying out a few ideas to see what works in practice and what doesn't. Unfortunately, this is one of those areas where

Re: [GENERAL] [ADMIN] Increasing the shared memory

In response to "Sorin N. Ciolofan" <[EMAIL PROTECTED]>: > > I've a value of 1000 set for shared_buffers, does this means > that I use 8kbX1000=8Mb of Shared Mem? > > > > The definition from the manual is quite confusing: > > > > shared_buffers (integer) > > Sets th

Re: [GENERAL] COPY command details

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 03/29/07 23:56, Gerald Timothy G Quimpo wrote: [snip] > > How do people take consistent backups of very large > databases on Linux/FreeBSD? I'm aware of PITR, but > might not be able to set aside a box with enough > drives for it. LVM Snapshot?

Re: [GENERAL] Wrong increments of Sequence

RPK <[EMAIL PROTECTED]> schrieb: > > I have created a sequence with minimum value=1 and increment=1. But whenever > I enter a record in a table and use nextval('seqstudentmaster') to insert > new studentid, it is incremented by 20. How to set it to increment by 1? Can you show us an example and

Re: [GENERAL] [ADMIN] Increasing the shared memory

An extract from --> http://www.powerpostgresql.com/PerfList/ might help you shared_buffers: As a reminder: This figure is NOT the total memory PostgreSQL has to work with. It is the block of dedicated memory PostgreSQL uses for active operations, and should be a minority of your total RAM on

Re: [GENERAL] [ADMIN] Increasing the shared memory

Thanks, I've a value of 1000 set for shared_buffers, does this means that I use 8kbX1000=8Mb of Shared Mem? The definition from the manual is quite confusing: shared_buffers (integer) Sets the amount of memory the database server uses for shared

Re: [GENERAL] Some encoding trouble via libpq

On Fri, Mar 30, 2007 at 11:43:08AM -0400, William Gray wrote: > The solution was to not clear result until later, or to copy the data in > allocated memory, then clear the result, which we did thusly: > >tmp_str = PQgetvalue(result, 0, 0); >tmp_str_len = strlen(tmp_str); >event_id = xm

Re: [GENERAL] PlPg/SQL

am Sat, dem 31.03.2007, um 15:40:56 -0700 mailte MaDog folgendes: > Hi! > > How can i return one more row form a pgpl/sql function? You can use a set-of-record - function for this: http://www.postgresql.org/docs/current/static/xfunc-sql.html#XFUNC-SQL-TABLE-FUNCTIONS or http://www.postgresql.

Re: [GENERAL] stored queries and quoted strings

On fös, 2007-03-30 at 00:31 -0700, filippo wrote: > Hello, > > I have a strange problem with stored queries like this > > $sql = qq/ > SELECT city, country > FROM countries > WHERE city LIKE ? > ORDER BY city >/; > $sthCity= $dbh->prepare($sql);

Re: [GENERAL] Using C# to create stored procedures

On Fri, Mar 30, 2007 at 12:19:44PM +0300, Andrus wrote: > Any idea how to write server-side stored procedures in C# for PostgreSQL > database ? > > In windows .NET 2 framework should be used and in Linuc/Mac/Windows MONO > should be used for this. > > How to install MONO engine as server-side

Re: [GENERAL] time series query

Good idea. I tried it and got a 12% decrease in execution time! Still slower than the usual JOIN, but not by that much. William Garrison wrote: Would it speed things up siginficantly if you set the dtval_smaller() function to be immutable? Volatile is the default, so it may be redundantly eval

Re: [GENERAL] calling a stored procedure using sql query in 7.4

BaseTwo wrote: > The following statement works on 8.x servers, but not on 7.4 and I > can't figure out how to get it to operate in the earlier version: > > select calc_cum_gpa_mp(marking_period_id::TEXT) > from (select distinct marking_period_id from student_mp_stats) as > sms1; > > I get the err

Re: [GENERAL] queries stop using indexes

On Apr 2, 2007, at 7:40 AM, Ragnar wrote: if you analyze the table after recreating the index, does it then stop using it? possibly you need to increase statistic targets on some or all of the index columns. a EXPLAIN ANALYZE could help figuring this out. no, analyzing the virgin index is f

Re: [GENERAL] coalesce for null AND empty strings

On Friday 30 March 2007 10:19:35 Richard Huxton wrote: > Don't forget to cc: the list! snip > > OK this shortens the function, but does not help to "solve" the coalesce > > problem > > coalecse(empty2null(var1),empty2null(var2),empty2null(var-n)) > > instead of > > coalecse2(var1,var2,...var-

[GENERAL] Using C# to create stored procedures

Any idea how to write server-side stored procedures in C# for PostgreSQL database ? In windows .NET 2 framework should be used and in Linuc/Mac/Windows MONO should be used for this. How to install MONO engine as server-side language to PostgreSQL ? How to call .NET dlls from PostgreSQL stor

Re: [GENERAL] Arrays instead of join tables

William Garrison wrote: I've never worked with a database with arrays, so I'm curious what the advantages and disadvantages of using it are. For example: I am prejudiced against arrays because they violate the relational model. I do not see an advantage over a related table. Arrays seem to

Re: [GENERAL] Deleted Flag/Unique Constraint

*Jonathan Hedstrom* wrote Yes, something like this: CREATE UNIQUE INDEX index_name ON table_name ( unique_field) WHERE deleted=0; My only concern here is Note: The preferred way to add a unique constraint to a table is ALTER TABLE ... ADD CONSTRAINT. The use of indexes to enforce unique const

[GENERAL] Wrong increments of Sequence

I have created a sequence with minimum value=1 and increment=1. But whenever I enter a record in a table and use nextval('seqstudentmaster') to insert new studentid, it is incremented by 20. How to set it to increment by 1? -- View this message in context: http://www.nabble.com/Wrong-increments-

[GENERAL] stored queries and quoted strings

Hello, I have a strange problem with stored queries like this $sql = qq/ SELECT city, country FROM countries WHERE city LIKE ? ORDER BY city /; $sthCity= $dbh->prepare($sql); my $tempCity = $dbh->quote("n%"); $sthCity->execute($tempCity); m

[GENERAL] PlPg/SQL

Hi! How can i return one more row form a pgpl/sql function? CREATE OR REPLACE FUNCTION GetRows(INTEGER, INTEGER, INTEGER) RETURNS TEXT AS $$ DECLARE Rows table_name%ROWTYPE; BEGIN SELECT * INTO Rows FROM table1_name WHERE id = $1; --

Re: [GENERAL] coalesce for null AND empty strings

Ferdinand Gassauer wrote: * looking through Google it IS a concern for many others and thus this function should be distributed as default. The mere fact that something is requested does not imply that the request should be accepted. I'm sure there's a name for this fallacy, but I can't think

[GENERAL] Unexplained case insensitive results

There appears to be a difference in the results from the following SQL statement when run on an 8.1.5 version versus a 8.1.8 version: SELECT T1.MRPtPro AS MRPtPro, T1.MRPtDOB, T1.MRPtSSN, T1.MRPtNbr, T2.MRMDNam AS MRPtProN, T1.MRPtChtCmpFlg, T1.MRPtPhA, T1.MRPtPhP, T1.MRPtPhS, T1.MRPtFNam, T1.MRPt

[GENERAL] plpy prepare problem

I'm having a bit of a problem getting plpython's prepare to work properly: CREATE OR REPLACE FUNCTION batch_item_reversal(b batch_item) RETURNS "varchar" AS $BODY$ if b['reversal_flag'] == 'Y': sql = plpy.prepare(""" SELECT batch_item_number FROM batch_it

Re: [GENERAL] coalesce for null AND empty strings

Ferdinand Gassauer wrote: it would be great to have a coalesce2 function which treats empty strings as null values. I disagree, it would be the opposite of "great". "" is a /known/ value and not in the same semantic space as NULL at all. If you really feel you need such a function, though, w

[GENERAL] calling a stored procedure using sql query in 7.4

I've created some sql scripts to update a database in version 8.x and now I'm trying to get them to run on servers using v7.4 The following statement works on 8.x servers, but not on 7.4 and I can't figure out how to get it to operate in the earlier version: select calc_cum_gpa_mp(marking_period_

Re: [GENERAL] ERROR: out of shared memory

Dear Mr. Tom Lane, From what I've read from the postgresql.conf file I've understood that which each unit increasing of the "max_locks_per_transaction" parameter the shared memory used is also increased. But the shared memory looks to be already fully consumed according to the error messag

Re: [GENERAL] COPY command details

On Thu, 2007-03-29 at 21:30 -0700, Benjamin Arai wrote: > Rebuilding an index can't be the PostgreSQL solution for all > cases. I am dealing with databases in the hundreds of gigs > range and I am adding about 10gigs of data a week. At > some point its going to take longer than a week to rebuil

Re: [GENERAL] Deleted Flag/Unique Constraint

Bryan Murphy wrote: I think the other guys suggestion will work better. ;) Really, the table was just an example off the top of my head. I believe we do use a boolean as the deleted flag. We primarily use it to track the user who originally created an item (even if their account is deleted)

Re: [GENERAL] Some encoding trouble via libpq

Ahhh, I get it! Thanks for pointing this out. It ended up not being the problem I was running into, but eventually it would have snagged me (solution to original problem below). I spent some time going over that bit of code and what you said with a coworker, and we ended up taking care of it.

Re: [GENERAL] Some encoding trouble via libpq

Tom, Thanks for your help, I really appreciate it! I'm kinda new to fread (I grew up in the land of Java, so I haven't had to get this close to memory since my assembler class back in college!), so I'm not sure what you mean by a partial buffer load? Since the first read is only for data up to

Re: [GENERAL] time series query

Would it speed things up siginficantly if you set the dtval_smaller() function to be immutable? Volatile is the default, so it may be redundantly evaluating things. Jaime Silvela wrote: In case anyone is interested, I was able to solve this, more or less. Here's my new "Latest value" query:

Re: [GENERAL] to_tsvector in 8.2.3

Sorry, no - I tested on CVS HEAD, so dll isn't compatible :( Wait a bit for 8.2.4 richardcraig wrote: Teodor As a non-C windows user (yes - throw stones at me :) ) Do you have a fixed dll for this patch that I can try? Thanks Richard Teodor Sigaev-2 wrote: Solved, see attached patch. I had

Re: [GENERAL] use superuser connection from php

> we have a php application which gets from time to time database errors > which look like there are not enough connections (we have 100 > connections allowed to postgresql) - > > i read that there are two db connections reserved for su. is there a way > to use them from php in order to check i

Re: [GENERAL] to_tsvector in 8.2.3

Teodor As a non-C windows user (yes - throw stones at me :) ) Do you have a fixed dll for this patch that I can try? Thanks Richard Teodor Sigaev-2 wrote: > > Solved, see attached patch. I had found old Celeron-300 box and install > Windows > on it, and it was very slow :) > > >> Nope, sa

Re: [GENERAL] time series query

In case anyone is interested, I was able to solve this, more or less. Here's my new "Latest value" query: select obj_id, val_type_id, (max(row(observation_date, val))).val from measurements group by obj_id, val_type_id It was only necessary to define a new (date, numeric) type. Below is the

Re: [GENERAL] Problem restoring from backup file

I think the problem is that the restore window looks for snapshots with the extension "backup", as made with the default options of the pgAdmin Backup function. If you're using "Plain", the backups are in .sql files, and you can execute with psql or a query window in pgAdmin. Rohit Prakash Kha

Re: [GENERAL] Problem restoring from backup file

try to give output file path in FILENAME text box and select onw of the option in PLAIN OPTIONS FRAME. It works on my machine. Hope this will help :) With Regards Ashish - Original Message - From: "Rohit Prakash Khare" <[EMAIL PROTECTED]> To: Sent: Sunday, April 01, 2007 10:58 PM S

Re: [GENERAL] Tsearch2 crashes my backend, ouch !

Fixed. Thanks for the report. Anyway, just to signal that tsearch2 crashes if SELECT is not granted to pg_ts_dict (other tables give a proper error message when not GRANTed).On -- Teodor Sigaev E-mail: [EMAIL PROTECTED]

[GENERAL] A little help for big forms

Hi PostgreSQL afficionados, I have a little question that I know is not fully on topic, but because I'm lurking this list I know many highly skilled and experienced people are there. So I try it: I have to store a big html form in my database. It has around 100 fields of differents kinds (varcha

Re: [GENERAL] queries stop using indexes

On lau, 2007-03-31 at 18:51 -0400, Jonathan Vanasco wrote: > I have a table with 15 cols , there is an joint index on 5 of them > > there are ~15million records in the table, and it gets heavy use via > update & insert > > i recently noticed that after a day of use, the index is no longer >

Re: [GENERAL] use superuser connection from php

On Mon, Apr 02, 2007 at 11:33:50AM +0200, [EMAIL PROTECTED] wrote: > i read that there are two db connections reserved for su. is there a way > to use them from php in order to check if the database is really out of > connections (with - 'SELECT count(*) FROM pg_stat_activity') or the > problem

Re: [GENERAL] Working between Windows and Unix

Erick Papadakis wrote: Hello: I am working on Windows and Unix with Postgresql (newbie, so please be kind). I use phppgadmin on both platforms. Windows is my local machine. Linux (CentOS as usual) is the cpanel thing that my hosting provider offers. Basically this setup works well and I am lear

Re: [GENERAL] Working between Windows and Unix

Why dont you use the pg_dump ( http://www.postgresql.org/docs/8.2/static/backup-dump.html) utility of PostgreSQL. Use the -s switch for taking just the schema and -a for taking the data dumps. Try that and let us know if you face a problem doing so. -- Shoaib Mir EnterpriseDB (www.enterprisedb.co

[GENERAL] use superuser connection from php

hello, we have a php application which gets from time to time database errors which look like there are not enough connections (we have 100 connections allowed to postgresql) - i read that there are two db connections reserved for su. is there a way to use them from php in order to check if

Re: [GENERAL] [ADMIN] Increasing the shared memory

I guess shared_buffers (in postgresql.conf file) will help you here if you have properly setup your kernel.SHMMAX value. -- Shoaib Mir EnterpriseDB (www.enterprisedb.com) On 4/2/07, Sorin N. Ciolofan <[EMAIL PROTECTED]> wrote: Hello! I'd like to ask you if there is any Postgre configura

[GENERAL] Increasing the shared memory

Hello! I'd like to ask you if there is any Postgre configuration parameter (like the ones defined in postgresql.conf file) that could be used for increasing the shared memory for Postgre? Thank you very much With best regards, Sorin

[GENERAL] Working between Windows and Unix

Hello: I am working on Windows and Unix with Postgresql (newbie, so please be kind). I use phppgadmin on both platforms. Windows is my local machine. Linux (CentOS as usual) is the cpanel thing that my hosting provider offers. Basically this setup works well and I am learning the ropes. But the