Re: [GENERAL] [SQL] question

The issue has been solved thanks to a custom nullif_int() function. Which if anyone has the same issue, it was solved with... CREATE FUNCTION nullif_int(text) RETURNS integer AS 'SELECT nullif($1,)::int;' LANGUAGE SQL; SELECTS were not the issue; INSERT INTO a non-text column was the issue.

Re: [GENERAL] stack depth limit exceeded

On Mon, Aug 29, 2005 at 01:45:32PM +1000, Jamie Deppeler wrote: > > CREATE TRIGGER "updateContact" AFTER INSERT OR UPDATE > ON FOR EACH ROW > EXECUTE PROCEDURE "contacts"."addContactField"(); Please show the actual commands that you're running; the above fails with a syntax error because it's mis

Re: [GENERAL] PQConnectdb SSL (sslmode): Is this a bug

On Mon, Aug 29, 2005 at 04:23:13PM -0700, vishal saberwal wrote: > now i ran the program i had that has a conect command with ("hostaddr= > 169.254.59.60 dbname=dbm user=postgres > sslmode=prefer") parameters. > > [EMAIL PROTECTED] serv]# ./bin/test_lib > Connection failed:

[GENERAL] Access more than one database from pgAdmin III

How can we access more than one database template1from pgAdmin III?   Currently in my pgAdmin III, I'm already accessing one database. When I tried to create another new database it says ERROR: database "template1" already exists.   May I know if we can actually connect to 2nd database? e.g. templa

Re: [GENERAL] update functions locking tables

On Mon, Aug 29, 2005 at 09:41:21PM -0300, Clodoaldo Pinto wrote: > > 26873 ? S 0:18 postgres: user database 127.0.0.1(38946) SELECT waiting > 23973 ? S 0:22 postgres: user database 127.0.0.1(43396) SELECT waiting > 31212 ? S 0:02 postgres: user database 127.0.0.1(60619) SELECT waiting > 21601 ? S 0

Re: [GENERAL] update functions locking tables

Clodoaldo Pinto <[EMAIL PROTECTED]> writes: > I've been trying for 3 days to figure out what is happening to no > avail. What am i missing about transactions and MVCC? What could make > a plpgsql update function lock a table? What is the function doing to the table, exactly? DDL changes generally

[GENERAL] update functions locking tables

I have almost completed one web site migration from mysql to pgsql. It is already running totally pgsql. But there is one last conversion problem. Most of the queries use tables populated every 3 hours. In mysql, for the site to not be unavailable during updating i was creating temporary tables,

Re: [GENERAL] About "ERROR: must be *superuser* to COPY to or from a file"

Greg Stark <[EMAIL PROTECTED]> writes: > I was only suggesting using this from a local unix user where you can > actually authoritatively say something about the uid of the connecting > user. I suggested that if the owner of the file matches the uid of the > connecting user (which you can get on a

Re: [GENERAL] About dropped notifications

Chris Browne <[EMAIL PROTECTED]> writes: > [EMAIL PROTECTED] (Tom Lane) writes: >> with the current implementation, a transaction will emit only >> one notify per notify event name, even if NOTIFY is executed >> many times within the transaction. > An interesting question is whether or not the rel

Re: [GENERAL] PQConnectdb SSL (sslmode): Is this a bug

hi michael and tom, (A) With LIBPQ.SO.3.2 After reading your response i copied the libpq.so.3.2 from the compiled source tree to /usr/lib where the version available was libpq.so.3.1. I recreated the symbolic links and now the links are as below: [EMAIL PROTECTED] DBApi]# ls -l /usr/lib/libpq* -rw

Re: [GENERAL] PQConnectdb SSL (sslmode): Is this a bug

oops i forgot to attach logfile output for the second case (LIBPQ.SO.3.1) : [EMAIL PROTECTED] DBApi]# cat /var/lib/pgsql/logfile LOG:  database system was interrupted at 2005-08-29 15:01:11 PDT LOG:  checkpoint record is at 0/655FF630 LOG:  redo record is at 0/655FF630; undo record is at 0/0; shutd

[GENERAL] "GiST access is not concurrent"

Does this mean that read access is not concurrent, or write access, or both?   --John

Re: [GENERAL] About dropped notifications

[EMAIL PROTECTED] (Tom Lane) writes: > CSN <[EMAIL PROTECTED]> writes: >> I'm considering setting up a script that listens for >> notifications for a table and if a row is deleted the >> script will delete that row's corresponding files. If >> there are thousands of rows in the table, and I do >> "

Re: [GENERAL] Select gives the wrong results

It looks like your "value" column is of a varchar(), char() or text type. The > and < operators compare the ordinal value of the text when used on text types. You'll want to use ALTER TABLE ... ALTER COLUMN ... to change value into a numeric type (probably INT or BIGINT), and then you'll get the

Re: [GENERAL] Select gives the wrong results

a beter idea is to use -mm-dd hh:mi:ss format 2005/8/29, Martijn van Oosterhout : > On Mon, Aug 29, 2005 at 04:48:49PM -0400, Crystle Numan wrote: > > Dear all: > > > > I am fairly knowledgeable about PostgreSQL but this behaviour is > > stumping me. Any help would be wonderful. If you think i

Re: [GENERAL] Select gives the wrong results

On Mon, 29 Aug 2005, Crystle Numan wrote: > I am fairly knowledgeable about PostgreSQL but this behaviour is > stumping me. Any help would be wonderful. If you think it is a bug, let > me now and I'll file one. > > (select values in DB (date stamps) between Jan 1, 2000 and Jan 1, 2005, > no resul

Re: [GENERAL] Select gives the wrong results

On Mon, Aug 29, 2005 at 04:48:49PM -0400, Crystle Numan wrote: > Dear all: > > I am fairly knowledgeable about PostgreSQL but this behaviour is > stumping me. Any help would be wonderful. If you think it is a bug, let > me now and I'll file one. > > (select values in DB (date stamps) between Jan

Re: [GENERAL] About "ERROR: must be *superuser* to COPY to or from a file"

Tom Lane <[EMAIL PROTECTED]> writes: > Greg Stark <[EMAIL PROTECTED]> writes: > > In any case here's some quick results from my system. There seems to a > > greater > > than 21% slowdown associated with piping the data through two processes > > instead of reading directly. > > Well, if the penal

Re: [GENERAL] About "ERROR: must be *superuser* to COPY to or from a file"

"John D. Burger" <[EMAIL PROTECTED]> writes: > >> Well, they would have access to every world readable file on the > >> system, ie /etc, /usr, /lib, ... most files are world readable. There's > >> a lot of discussion about this, yet no-one has demonstrated that COPY > >> FROM STDIN isn't just as

[GENERAL] Select gives the wrong results

Dear all: I am fairly knowledgeable about PostgreSQL but this behaviour is stumping me. Any help would be wonderful. If you think it is a bug, let me now and I'll file one. (select values in DB (date stamps) between Jan 1, 2000 and Jan 1, 2005, no results) db_name=# SELECT * from person_detail W

Re: [GENERAL] POSS. FEATURE REQ: "Dynamic" Views

Greg Stark wrote: Bruce Momjian writes: Well, I just added to TODO: * Allow VIEW/RULE recompilation when the underlying tables change Is dynamic view a industry-standard name? If so, I will add it to the TODO. "DYNAMIC" is something I made up. "ALTER VIEW RECOMPILE" is O

Re: [GENERAL] revoke on database not working as expected

Stijn Hoop <[EMAIL PROTECTED]> writes: > template1=# revoke all on database privtest from testpriv; That doesn't do what you evidently think it does --- it revokes the right to create temp tables, and the right to create new schemas, but not every right in existence. Please read the GRANT/REVOKE

Re: [GENERAL] max_connections

"John D. Burger" <[EMAIL PROTECTED]> writes: > I'm now fiddling with some of the performance parameters, and I'm > wondering about max_connections. The default appears to be 100 - this > is at least an order of magnitude higher than I need. Would much be > saved by dropping this down to 10 or

[GENERAL] revoke on database not working as expected

Hi, I'm running into a setup problem (I guess) while trying to prevent a user from creating tables in a database. The setup is a FreeBSD 5.4 database server accessed from a FreeBSD 6.0 development box, both running PostgreSQL 8.0.3. This is what I have configured on the database server (firsa):

Re: [GENERAL] About dropped notifications

CSN <[EMAIL PROTECTED]> writes: > I'm considering setting up a script that listens for > notifications for a table and if a row is deleted the > script will delete that row's corresponding files. If > there are thousands of rows in the table, and I do > "delete from table", or even "delete from tab

[GENERAL] max_connections

I've recently succeeded in lobbying my sysadmins to upgrade from 7.2.0 to 7.4.8 (thanks to everyone for the advice on how to leverage this). I'm now fiddling with some of the performance parameters, and I'm wondering about max_connections. The default appears to be 100 - this is at least an o

[GENERAL] About dropped notifications

The docs state: "NOTIFY behaves like Unix signals in one important respect: if the same notification name is signaled multiple times in quick succession, recipients may get only one notification event for several executions of NOTIFY. So it is a bad idea to depend on the number of notifications re

Re: [GENERAL] stack depth limit exceeded

I think that you forgot the table name. CREATE TRIGGER "updateContact" AFTER INSERT OR UPDATE ON contacts FOR EACH ROW EXECUTE PROCEDURE "contacts"."addContactField"(); Frank Jamie Deppeler wrote: What i am trying to do is update the field contact with field values in firstname and lastname

Re: [GENERAL] Planner create a slow plan without an available index

Ben-Nes Yonatan <[EMAIL PROTECTED]> writes: > Indexes: > "items_items_id_key" UNIQUE, btree (items_id) > "items_left" btree (left) > "items_left_right" btree (left, right) You could get rid of the items_left index --- it's redundant with the first column of the combined index anyway

[GENERAL] Planner create a slow plan without an available index

Hi All, I got a weird problem with the planner which cause my queries to take ages... ill try to explain it shortly and summarized... :) I got the following table (which got 1.2 million rows): Table "public.items" Column | Type | Modifier

Re: [GENERAL] regarding threads and transactions - problem 2

Title: Re: [GENERAL] regarding threads and transactions - problem 2 patient_key is the unique key and the primary key is patient_id, which is a bigserial.   actually this is what the stored procedure does:   a patient comes and it is associated with patient_key ...if is not present in the