Re: [GENERAL] [PERFORM] Incr/Decr Integer
On Thursday 16 July 2009 23:20:34 William Scott Jordan wrote: > Hi Andrew, > > That's a very good guess. We are in fact updating this table multiple > times within the same triggered function, which is being called on an > INSERT. Essentially, we're using this to keep a running total of the > number of rows being held in another table. The function we're using > currently looks something like this: > > --- > CREATE OR REPLACE FUNCTION the_function() RETURNS "trigger" > AS $$ > BEGIN; > UPDATE the_table > SET first_column = first_column + 1 > WHERE first_id = NEW.first_id ; > > UPDATE the_table > SET second_column = second_column + 1 > WHERE second_id = NEW.second_id ; > > UPDATE the_table > SET third_column = third_column + 1 > WHERE third_id = NEW.third_id ; > RETURN NULL; > END; > $$ > LANGUAGE plpgsql; > --- > > For something like this, would it make more sense to break out the three > different parts into three different functions, each being triggered on > INSERT? Or would all three functions still be considered a single > transaction, since they're all being called from the same insert? > > Any suggestions would be appreciated! You need to make sure *all* your locking access happens in the same order. Then you will possibly have one transaction waiting for the other, but not deadlock: The formerly described Scenario now works: Session 1: BEGIN; UPDATE the_table SET the_int = the_int + 1 WHERE the_id = 1; Session 2: BEGIN UPDATE the_table SET the_int = the_int + 1 WHERE the_id = 1; Wait. Session 1: UPDATE the_table SET the_int = the_int + 1 WHERE the_id = 2; Fine Session 2: Still waiting Session 1: commit Session 2: waiting ends. UPDATE the_table SET the_int = the_int + 1 WHERE the_id = 2; commit; Sensible? Works? Andres -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [PERFORM] Incr/Decr Integer
Hi Andrew, That's a very good guess. We are in fact updating this table multiple times within the same triggered function, which is being called on an INSERT. Essentially, we're using this to keep a running total of the number of rows being held in another table. The function we're using currently looks something like this: --- CREATE OR REPLACE FUNCTION the_function() RETURNS "trigger" AS $$ BEGIN; UPDATE the_table SET first_column = first_column + 1 WHERE first_id = NEW.first_id ; UPDATE the_table SET second_column = second_column + 1 WHERE second_id = NEW.second_id ; UPDATE the_table SET third_column = third_column + 1 WHERE third_id = NEW.third_id ; RETURN NULL; END; $$ LANGUAGE plpgsql; --- For something like this, would it make more sense to break out the three different parts into three different functions, each being triggered on INSERT? Or would all three functions still be considered a single transaction, since they're all being called from the same insert? Any suggestions would be appreciated! -William Andres Freund wrote: On Thursday 16 July 2009 19:56:47 William Scott Jordan wrote: Hey all! Is there a better way to increase or decrease the value of an integer than doing something like: --- UPDATE the_table SET the_int = the_int + 1 WHERE the_id = 123 ; --- We seem to be getting a lot of deadlocks using this method under heavy load. Just wondering if we should be doing something different. Is this the only statement in your transaction? Or are you issuing multiple such update statements in one transactions? I am quite sure its not the increment of that value causing the problem. If you issue multiple such statements you have to be carefull. Example: Session 1: BEGIN; UPDATE the_table SET the_int = the_int + 1 WHERE the_id = 1; Session 2: BEGIN UPDATE the_table SET the_int = the_int + 1 WHERE the_id = 2; Fine so far. Session 1: UPDATE the_table SET the_int = the_int + 1 WHERE the_id = 2 ; Waits for lock. Session 2: UPDATE the_table SET the_int = the_int + 1 WHERE the_id = 1; Deadlock. Andres PS: Moved to pgsql-general, seems more appropriate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [PERFORM] Incr/Decr Integer
Hi Andrew, That's a very good guess. We are in fact updating this table multiple times within the same triggered function, which is being called on an INSERT. Essentially, we're using this to keep a running total of the number of rows being held in another table. The function we're using currently looks something like this: --- CREATE OR REPLACE FUNCTION the_function() RETURNS "trigger" AS $$ BEGIN; UPDATE the_table SET first_column = first_column + 1 WHERE first_id = NEW.first_id ; UPDATE the_table SET second_column = second_column + 1 WHERE second_id = NEW.second_id ; UPDATE the_table SET third_column = third_column + 1 WHERE third_id = NEW.third_id ; RETURN NULL; END; $$ LANGUAGE plpgsql; --- For something like this, would it make more sense to break out the three different parts into three different functions, each being triggered on INSERT? Or would all three functions still be considered a single transaction, since they're all being called from the same insert? Any suggestions would be appreciated! -William Andres Freund wrote: On Thursday 16 July 2009 19:56:47 William Scott Jordan wrote: Hey all! Is there a better way to increase or decrease the value of an integer than doing something like: --- UPDATE the_table SET the_int = the_int + 1 WHERE the_id = 123 ; --- We seem to be getting a lot of deadlocks using this method under heavy load. Just wondering if we should be doing something different. Is this the only statement in your transaction? Or are you issuing multiple such update statements in one transactions? I am quite sure its not the increment of that value causing the problem. If you issue multiple such statements you have to be carefull. Example: Session 1: BEGIN; UPDATE the_table SET the_int = the_int + 1 WHERE the_id = 1; Session 2: BEGIN UPDATE the_table SET the_int = the_int + 1 WHERE the_id = 2; Fine so far. Session 1: UPDATE the_table SET the_int = the_int + 1 WHERE the_id = 2 ; Waits for lock. Session 2: UPDATE the_table SET the_int = the_int + 1 WHERE the_id = 1; Deadlock. Andres PS: Moved to pgsql-general, seems more appropriate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Areca 1680 and RHEL/Centos 5.3 issue
On Wed, 15 Jul 2009, Scott Marlowe wrote: About two months ago I updated one of our to servers to 5.3, and the new kernel is 2.6.18-128.1.14.el5. I had nothing but trouble on my one system with an Areca ARC-1210 using the 2.6.18-92 kernel from 5.2 you said worked fine for you. The problem I ran into was similar to yours, the card just went off-line mysteriously under load. I upgraded that system to run a stock 2.6.22.19 using the same basic configuration as the stock RHEL kernel and all the issues went away, so I never circled back to check the later 2.6.18 releases from them. -- * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Concurrency issue under very heay loads
On Wed, 15 Jul 2009, Raji Sridar (raji) wrote: When multiple clients are concurrently accessing this table and updating it, under extermely heavy loads in the system (stress testing), we find that the same order number is being generated for multiple clients. The only clean way to generate sequence numbers without needing to worry about duplicates is using nextval: http://www.postgresql.org/docs/current/static/functions-sequence.html If you're trying to duplicate that logic in your own code, there's probably a subtle race condition in your implementation that is causing the bug. If you had two calls to nextval from different clients get the same value returned, that might be a PostgreSQL bug. Given how much that code gets tested, the more likely case is that there's something to tweak in your application instead. I would advise starting with the presumption it's an issue in your app rather than on the server side of things. P.S. Posting the same question to two lists here is frowned upon; pgsql-general is the right one for a question like this. -- * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Error in insert statement
> From: pgsql-general-ow...@postgresql.org
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of
> Thomas Kellerer
> Sent: Thursday, July 16, 2009 4:05 PM
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Error in insert statement
>
> Relyea, Mike wrote on 16.07.2009 21:40:
> > I need help understanding what I'm doing wrong with an
> insert statement.
> > I'm running 8.3.7 on Windows and the DB is complaining about the
> > select statement in my insert statement.
>
> When using a SELECT for an INSERT the values part is not
> needed in fact its incorrect syntax.
>
> You need to run:
>
> INSERT INTO "tblSpecs"
> ("CartridgeTypeID", "ColorID", "TestTypeID", "ZoneID",
> "PaperID", "AttributeID", "Spec") SELECT * FROM "tblTempSpecs";
Got it. Thanks.
> But: using a "SELECT *" here is calling for trouble. You are
> relying on an implicit an not guaranteed order and number of columns.
> Listing all the needed columns in the SELECT statement is
> much more robust.
Point taken. Since this is just to load the table initially to do my
development work no harm done. If it were a production environment I'll
make sure to list all of the columns specifically.
>
> Btw: you should create your tables without using double
> quotes, thus you can get rid of them when doing normal DML.
I would if I could. I'm interfacing with a database I did not create.
Instead of having some needing quotes and some not, I find it easier to
just quote everything.
> Out of curiosity: why do you prefix the table with "tbl"?
> Don't you know it's a table? Sounds like a strange naming
> scheme to me.
It's an old habit that I picked up from MS Access. There were times
that when going through some of the wizards in Access it wasn't clear if
the choice you were selecting was a table or a query. So I learned
there to prefix all of my tables with "tbl" and all of my queries with
"qry". Old habits die hard.
Mike
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Error in insert statement
> From: bricklen [mailto:brick...@gmail.com]
> Sent: Thursday, July 16, 2009 4:05 PM
> To: Relyea, Mike
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Error in insert statement
>
> Try dropping the word "VALUES".
>
> eg.
> INSERT INTO "tblSpecs" ("CartridgeTypeID", "ColorID",
> "TestTypeID","ZoneID", "PaperID", "AttributeID", "Spec")
> SELECT "CartridgeTypeID", "ColorID", "TestTypeID","ZoneID",
> "PaperID", "AttributeID","Spec" from "tblTempSpecs";
>
I knew there had to be some bone-headed mistake I was making. That
worked of course. Thanks.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Error in insert statement
Try dropping the word "VALUES".
eg.
INSERT INTO "tblSpecs" ("CartridgeTypeID", "ColorID",
"TestTypeID","ZoneID", "PaperID", "AttributeID", "Spec")
SELECT "CartridgeTypeID", "ColorID", "TestTypeID","ZoneID", "PaperID",
"AttributeID","Spec" from "tblTempSpecs";
On Thu, Jul 16, 2009 at 12:40 PM, Relyea, Mike wrote:
> I need help understanding what I'm doing wrong with an insert statement.
> I'm running 8.3.7 on Windows and the DB is complaining about the select
> statement in my insert statement.
>
> I'm trying to execute
>
> DROP TABLE IF EXISTS "tblTempSpecs";
> CREATE TEMP TABLE "tblTempSpecs" AS SELECT "tblSpecs"."CartridgeTypeID",
> "tblSpecs"."ColorID", "tblTestTypes"."TestTypeID", "tblZones"."ZoneID",
> "tblSpecs"."PaperID",
> "tblSpecs"."AttributeID", "tblSpecs"."Spec"
> FROM "tblSpecs", "tblZones", "tblTestTypes"
> WHERE "tblSpecs"."ZoneID" IS NULL AND "tblTestTypes"."TestTypeID" IN
> (SELECT DISTINCT "TestTypeID" FROM "tblCartridgePQSetIntervals" WHERE
> "CartridgeTypeID" = 74);
>
> INSERT INTO "tblSpecs" ("CartridgeTypeID", "ColorID", "TestTypeID",
> "ZoneID", "PaperID", "AttributeID", "Spec") VALUES SELECT * FROM
> "tblTempSpecs";
>
> To insert data into
>
> CREATE TABLE "tblSpecs"
> (
> "SpecID" integer NOT NULL DEFAULT
> nextval('"sequence_SpecID"'::regclass),
> "CartridgeTypeID" integer NOT NULL,
> "ColorID" integer NOT NULL,
> "TestTypeID" integer,
> "ZoneID" integer,
> "PaperID" integer,
> "PrintCopyID" integer,
> "AttributeID" integer NOT NULL,
> "Spec" numeric NOT NULL,
> CONSTRAINT "tblSpecs_pkey" PRIMARY KEY ("SpecID"),
> CONSTRAINT "tblSpecs_AttributeID_fkey" FOREIGN KEY ("AttributeID")
> REFERENCES "tblAttributes" ("AttributeID") MATCH SIMPLE
> ON UPDATE NO ACTION ON DELETE NO ACTION,
> CONSTRAINT "tblSpecs_CartridgeTypeID_fkey" FOREIGN KEY
> ("CartridgeTypeID")
> REFERENCES "tblCartridgeTypes" ("CartridgeTypeID") MATCH SIMPLE
> ON UPDATE NO ACTION ON DELETE NO ACTION,
> CONSTRAINT "tblSpecs_ColorID_fkey" FOREIGN KEY ("ColorID")
> REFERENCES "tblColors" ("ColorID") MATCH SIMPLE
> ON UPDATE NO ACTION ON DELETE NO ACTION,
> CONSTRAINT "tblSpecs_PaperID_fkey" FOREIGN KEY ("PaperID")
> REFERENCES "tblPaperTypes" ("PaperID") MATCH SIMPLE
> ON UPDATE NO ACTION ON DELETE NO ACTION,
> CONSTRAINT "tblSpecs_TestTypeID_fkey" FOREIGN KEY ("TestTypeID")
> REFERENCES "tblTestTypes" ("TestTypeID") MATCH SIMPLE
> ON UPDATE NO ACTION ON DELETE NO ACTION,
> CONSTRAINT "tblSpecs_ZoneID_fkey" FOREIGN KEY ("ZoneID")
> REFERENCES "tblZones" ("ZoneID") MATCH SIMPLE
> ON UPDATE NO ACTION ON DELETE NO ACTION
> )
> WITH (
> OIDS=FALSE
> );
>
> What am I doing wrong?
>
> Mike
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Error in insert statement
Relyea, Mike wrote on 16.07.2009 21:40:
I need help understanding what I'm doing wrong with an insert statement.
I'm running 8.3.7 on Windows and the DB is complaining about the select
statement in my insert statement.
When using a SELECT for an INSERT the values part is not needed in fact its
incorrect syntax.
You need to run:
INSERT INTO "tblSpecs"
("CartridgeTypeID", "ColorID", "TestTypeID",
"ZoneID", "PaperID", "AttributeID", "Spec")
SELECT * FROM "tblTempSpecs";
But: using a "SELECT *" here is calling for trouble. You are relying on an
implicit an not guaranteed order and number of columns.
Listing all the needed columns in the SELECT statement is much more robust.
Btw: you should create your tables without using double quotes, thus you can get
rid of them when doing normal DML.
Out of curiosity: why do you prefix the table with "tbl"? Don't you know it's a
table? Sounds like a strange naming scheme to me.
Thomas
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Error in insert statement
I need help understanding what I'm doing wrong with an insert statement.
I'm running 8.3.7 on Windows and the DB is complaining about the select
statement in my insert statement.
I'm trying to execute
DROP TABLE IF EXISTS "tblTempSpecs";
CREATE TEMP TABLE "tblTempSpecs" AS SELECT "tblSpecs"."CartridgeTypeID",
"tblSpecs"."ColorID", "tblTestTypes"."TestTypeID", "tblZones"."ZoneID",
"tblSpecs"."PaperID",
"tblSpecs"."AttributeID", "tblSpecs"."Spec"
FROM "tblSpecs", "tblZones", "tblTestTypes"
WHERE "tblSpecs"."ZoneID" IS NULL AND "tblTestTypes"."TestTypeID" IN
(SELECT DISTINCT "TestTypeID" FROM "tblCartridgePQSetIntervals" WHERE
"CartridgeTypeID" = 74);
INSERT INTO "tblSpecs" ("CartridgeTypeID", "ColorID", "TestTypeID",
"ZoneID", "PaperID", "AttributeID", "Spec") VALUES SELECT * FROM
"tblTempSpecs";
To insert data into
CREATE TABLE "tblSpecs"
(
"SpecID" integer NOT NULL DEFAULT
nextval('"sequence_SpecID"'::regclass),
"CartridgeTypeID" integer NOT NULL,
"ColorID" integer NOT NULL,
"TestTypeID" integer,
"ZoneID" integer,
"PaperID" integer,
"PrintCopyID" integer,
"AttributeID" integer NOT NULL,
"Spec" numeric NOT NULL,
CONSTRAINT "tblSpecs_pkey" PRIMARY KEY ("SpecID"),
CONSTRAINT "tblSpecs_AttributeID_fkey" FOREIGN KEY ("AttributeID")
REFERENCES "tblAttributes" ("AttributeID") MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT "tblSpecs_CartridgeTypeID_fkey" FOREIGN KEY
("CartridgeTypeID")
REFERENCES "tblCartridgeTypes" ("CartridgeTypeID") MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT "tblSpecs_ColorID_fkey" FOREIGN KEY ("ColorID")
REFERENCES "tblColors" ("ColorID") MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT "tblSpecs_PaperID_fkey" FOREIGN KEY ("PaperID")
REFERENCES "tblPaperTypes" ("PaperID") MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT "tblSpecs_TestTypeID_fkey" FOREIGN KEY ("TestTypeID")
REFERENCES "tblTestTypes" ("TestTypeID") MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT "tblSpecs_ZoneID_fkey" FOREIGN KEY ("ZoneID")
REFERENCES "tblZones" ("ZoneID") MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
OIDS=FALSE
);
What am I doing wrong?
Mike
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [PERFORM] Incr/Decr Integer
On Thursday 16 July 2009 19:56:47 William Scott Jordan wrote: > Hey all! > > Is there a better way to increase or decrease the value of an integer > than doing something like: > > --- > UPDATE the_table SET the_int = the_int + 1 WHERE the_id = 123 ; > --- > > We seem to be getting a lot of deadlocks using this method under heavy > load. Just wondering if we should be doing something different. Is this the only statement in your transaction? Or are you issuing multiple such update statements in one transactions? I am quite sure its not the increment of that value causing the problem. If you issue multiple such statements you have to be carefull. Example: Session 1: BEGIN; UPDATE the_table SET the_int = the_int + 1 WHERE the_id = 1; Session 2: BEGIN UPDATE the_table SET the_int = the_int + 1 WHERE the_id = 2; Fine so far. Session 1: UPDATE the_table SET the_int = the_int + 1 WHERE the_id = 2 ; Waits for lock. Session 2: UPDATE the_table SET the_int = the_int + 1 WHERE the_id = 1; Deadlock. Andres PS: Moved to pgsql-general, seems more appropriate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] best practice transitioning from one datatype to another
Trying to fix a dump file ... cat backup.sql | sed -e "s/uniqueidentifier/uuid/g" > backup_fixed.sql ... gives me a dump that won't import. It is hard digging through 30+ gigs of text data to find where sed ate a field delimiter, so I'm going to give Tom's idea a try. I didn't even know the ALTER TYPE x RENAME TO y; was even available, and I probably wouldn't have tried it if Tom hadn't suggested it. It takes a certan amount of chutzpah to make that kind of change before diving into the lengthy process of database upgrading. From: Arndt Lehmann To: pgsql-general@postgresql.org Sent: Thursday, July 16, 2009 5:22:26 AM Subject: Re: [GENERAL] best practice transitioning from one datatype to another On Jul 16, 5:46 pm, a.w...@netzmeister-st-pauli.de (Andreas Wenk) wrote: > Arndt Lehmann schrieb: > > > On Jul 16, 8:05 am, t...@sss.pgh.pa.us (Tom Lane) wrote: > >> CG writes: > >>> While transitioning from 8.1 to 8.4, I need to transition to the internal > >>> UUID type in place of the contrib/uniqueidentifier module. I've built the > >>> database around uniqueidentifier, so nearly every table has one column of > >>> that data type. It's going to be tedious to > >>> ALTER TABLE foo ALTER COLUMN bar SET DATA TYPE uuid; > >>> ...repeat 600 times... > >>> I'll also have to drop and reload the views and the rules on tables. > >>> It'll be tedious even if the tables have no data in them. > >>> Can anyone recommend a better/faster way to make the transition? > >> Couldn't you rename the type to uuid in the 8.1 database before you > >> dump? > > >> regards, tom lane > > >> -- > >> Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) > >> To make changes to your > >> subscription:http://www.postgresql.org/mailpref/pgsql-general > > > Just an idea - don't know if it works, first try on a test server: > > 1. Dump the complete database into text format (pg_dump --format=t) > > 2. Do a search and replace from "uniqueidentifier" to "uuid" > > 3. Reimport > > > Best Regards, > > Arndt Lehmann > > uh - --format=t means tar format. --format=p means plain text ... > > Or am I missing something? > > $pg_dump --help > Usage: > pg_dump [OPTION]... [DBNAME] > > General options: > ... > -F, --format=c|t|p output file format (custom, tar, plain text) > ... > > Cheers > > Andy > > -- > Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) > To make changes to your > subscription:http://www.postgresql.org/mailpref/pgsql-general Hi Andy, You are perfectly right. My oversight. Best Regards, Arndt Lehmannn -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] something to suggest indexes
Hi, Is there something built-in to Postgres that would suggest what indexes I might add to improve performance? I created my required tables (they only contain small amounts of test data) and the performance is great. But as the data starts growing I'm betting that creating a few indexes will be needed. In the past I just started playing with explain using a hit and miss way of doing it. If there is nothing in Postgres does anyone have any suggestions? John -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problems with 8.4, FLOAT8PASSBYVAL and x86_64 GNU/Linux
Rafael Martinez wrote: > This is the Makefile we use: > - --- > PG_SRC=/usr/local/src > PG_LIB=/usr/local/lib > SERVER_INCLUDES += -I $(shell /local/opt/pgsql-8.4/bin/pg_config > - --includedir) > SERVER_INCLUDES += -I $(shell /local/opt/pgsql-8.4/bin/pg_config > - --includedir-server) I suggest you rewrite your makefile to use PGXS. The problem might be a difference in CFLAGS. It would make the makefile a lot simpler too. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Please help
Hello, Roseller ! > > What should I do to access my database and retrieve the important records in > it? Possible you should keep files in database cluster, recreate db cluster and put your old files into newly created cluster. -- Best regards, Sincerely yours, Yuriy Rusinov. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Create (function, procedure) and trigger to increment a counter
I have a table usage, I would like to create a (function or procedure) called by the trigger to increment column counter after an update. Can someone lend me a hand with the process behind creating this function,procedure and trigger. Table "public.usage" Column | Type | Modifiers ---+---+--- instrument| character varying(13) | date | date | counter | integer | _ Internet explorer 8 lets you browse the web faster. http://go.microsoft.com/?linkid=9655582
Re: [GENERAL] Working around spurious unique constraint errors due to SERIALIZABLE bug
* Albe Laurenz: >SELECT COUNT(id) INTO i2 FROM a WHERE id = i; >IF i2 = 0 THEN > /* This INSERT will never throw an exception if the > transactions are truly serialized */ > INSERT INTO a (id) VALUES (i); > RETURN TRUE; >ELSE > RETURN FALSE; >END IF; > This is what you are talking about, right? Yes. > I am not sure what exactly you mean by retrying the transaction in > Session A. Even on a second try A would not be able to insert the > duplicate key. But at least there would not be an error: I often need to obtain the automatically generated primary key in both cases (with and without INSERT). > The best way to work around a problem like this is to write > code that does not assume true serializability, for example: > > BEGIN >INSERT INTO a (id) VALUES (i); >RETURN TRUE; > EXCEPTION >WHEN unique_violation THEN > RETURN FALSE; > END; Oh, since when does this perform an implicit snapshot? I haven't noticed this before. The drawback is that some of the side effects of the INSERT occur before the constraint check fails, so it seems to me that I still need to perform the select. My main concern is that the unqiue violation could occur for another reason (which would be a bug), and I want to avoid an endless loop in such cases. But if it's possible to isolate this type of error recovery to a single statement, this risk is greatly reduced. -- Florian Weimer BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Asking for assistance in determining storage requirements
No other takers on this one? I'm wondering what exactly "direct attached storage" entails? At PG Con I heard a lot about using only direct-attached storage, and not a SAN. Are there numbers to back this up? Does fibre-channel count as direct-attached storage? I'm thinking it would. What exactly is recommended against? Any strorage that is TCP/IP based? On Thu, Jul 9, 2009 at 11:15 AM, Chris Barnes wrote: > You assistance is appreciated. > > > I have question regarding disk storage for postgres servers > > > > We are thinking long term about scalable storage and performance and would > like some advise > or feedback about what other people are using. > > > > We would like to get as much performance from our file systems as possible. > > > > We use ibm 3650 quad processor with onboard SAS controller ( 3GB/Sec) with > 15,000rpm drives. > > We use raid 1 for the centos operating system and the wal archive logs. > > The postgres database is on 5 drives configured as raid 5 with a global hot > spare. > > > > We are curious about using SAN with fiber channel hba and if anyone else > uses this technology. > > We would also like to know if people have preference to the level of raid > with/out striping. > > Sincerely, > > Chris Barnes > Recognia Inc. > Senior DBA > > > Attention all humans. We are your photos. Free us. -- “Don't eat anything you've ever seen advertised on TV” - Michael Pollan, author of "In Defense of Food" -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Please help
Please help me with this. I really need your advice as to how to retrieve the data in my postgresql database. I have postgresql installed in Windows XP platform five months ago. Just yesterday my OS bugged down and saying NTDLR is missing. What I did was I re-installed my OS. When I finished my installation I found out that I cannot access anymore my postgresql using PGAdmin III. What should I do to access my database and retrieve the important records in it? Unfortunately, I have not created a backup of it. Please help me. Thanks in advance and God bless... Roseller Romanos STI-Pagadian Gillamac Building, Pagadian City Office Nos: (062) 2144-785 Home Nos: (062) 2144-695 Mobile Nos: 09203502636
Re: [GENERAL] suggestion: log_statement = sample
hi, thanks for your comments on this. On Thursday 16 July 2009 15:05:58 you wrote: > In response to Janning Vygen : > > hi, > > > > http://archives.postgresql.org/pgsql-general/2009-03/msg00581.php > > > > This was my suggestion about introducing a statment to get a sample of > > SQL statements. Nobody answered yet. Why not? i think my suggestion would > > help a lot. Or was it kind of stupid? > > For my part, I don't think this would be useful. > > Since most of your queries are run by software, you're going to see a > fairly predictable pattern to the queries, which means your sampling isn't > going to be anywhere near random, thus it will still be inaccurate and > incomplete. I dont think so. In my use case i will get a good sampling of queries as I could keep my log_sample running over long period of time. The sampling is in any case much better than with log_minduration while logging all statement is not acceptable in production. > In my experience, I've found that enabling full logging for a short time > (perhaps a few hours) gathers enough data to run through tools like > pgFouine and find problem areas. It is not possible for us. Logging millions of statements take too much time. > Also, we have development servers that > run automated tests, and since it's not critical that they be performant, > we can run full query logging on them all the time. But you dont run the real use cases with automated tests. There so many factors involved in real time: caching, concurrency, data, peaktime, deadlocks, doubleclicks, robots etc. that you just can't reproduce it on a development system without lots of effort. > Additionally, we make > sure our production systems have enough hardware behind them that we can > add additional tasks without it affecting production use. that's nice, but not everybody can afford it. Of course i would love to log every statement. But do you really log every statement in production? I guess not. > All of these are (in my opinion) better approaches to the problem than > yet another arbitrary query filtering technique. I mean, logging only > the most time-consuming queries is already arbitrary enough (as you > already stated). With log_min duration i get only most time-consuming queries. With log sample i can detect if there is a fast query which is called to often. This is impossible today. Again: for my use case it makes sense to have a log_sample feature. kind regards Janning -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Using the geqo
Thanks Andres, > 1. Since the value of geqo_threshold is 12, does the geqo get automatically > activated for queries with 12 or more joins? No, not directly. It will get used for 12 joins with no predefined order. This is a bit complicated by the fact that even a predefined order like: a JOIN b ON (..) JOIN c ON (..) ... might get reordered if the number of joins is less than join_collapse_limit. See http://www.postgresql.org/docs/current/static/explicit-joins.html> *Oh, that is, I can't make sure that a query given by me uses the geqo. Is it? > 3. How do we come to know whether the geqo has been used to solve the query > or not? Does Explain Analyze report it? No, you cannot see it directly unless you recompile with different options.> *Recompile? Sorry, but I couldn't get this. > Please let me know these basics on the use of the geqo. Why do you want to specifically use GEQO? Just Research? Normally you try not to get where GEQO is used ;-) Andres> *Yes, I know. But then yes again, its for research. Thanks,RegardsSwati
Re: [GENERAL] Working around spurious unique constraint errors due to SERIALIZABLE bug
Florian Weimer wrote: > SERIALIZABLE isolation level doesn't really conform to the spec > because it doesn't deal with phantoms. The only case I've come across > where this actually matters is when you're implementing some sort of > "insert into table if not yet present" operation. This will typically > result in a unique constraint violation.[*] > > Usually, constraint violations are programming errors, but not this > one. It's more like a detected deadlock. Is there a way to tell this > type of constraint violation from other types, so that the transaction > can be restarted automatically (as if there was a deadlock)? > Theoretically, PostgreSQL should detect that the conflicting row > wasn't there when the snapshot for the transaction was taken, and > somehow export this piece of information, but I'm not sure if it's > available to the client. > > [*] One way to work around this is to batch inserts and eventually > perform them in a background task which doesn't run in parallel, but > this approach isn't always possible. Let me construct an example: CREATE TABLE a (id integer PRIMARY KEY); CREATE FUNCTION ins(i integer) RETURNS boolean LANGUAGE plpgsql STRICT AS $$DECLARE i2 integer; BEGIN SELECT COUNT(id) INTO i2 FROM a WHERE id = i; IF i2 = 0 THEN /* This INSERT will never throw an exception if the transactions are truly serialized */ INSERT INTO a (id) VALUES (i); RETURN TRUE; ELSE RETURN FALSE; END IF; END;$$; Now sessions A and B do the following: A: START TRANSACTION ISOLATION LEVEL SERIALIZABLE; A: SELECT * FROM a; id (0 rows) B: START TRANSACTION ISOLATION LEVEL SERIALIZABLE; B: SELECT * FROM a; id (0 rows) B: SELECT ins(1); ins - t (1 row) A: SELECT ins(1); Session A is blocked by B's exclusive lock. B: COMMIT; Now A gets: ERROR: duplicate key value violates unique constraint "a_pkey" CONTEXT: SQL statement "INSERT INTO a (id) VALUES ( $1 )" PL/pgSQL function "ins" line 1 at SQL statement This is what you are talking about, right? I am not sure what exactly you mean by retrying the transaction in Session A. Even on a second try A would not be able to insert the duplicate key. But at least there would not be an error: A: ROLLBACK; A: SELECT ins(1); ins - f (1 row) The best way to work around a problem like this is to write code that does not assume true serializability, for example: BEGIN INSERT INTO a (id) VALUES (i); RETURN TRUE; EXCEPTION WHEN unique_violation THEN RETURN FALSE; END; Maybe my example is too simple, but it should work similar to this whenever error conditions are involved. Other problems will be more tricky (I am thinking of the example I constructed for http://archives.postgresql.org/pgsql-hackers/2009-05/msg00316.php). I don't think that there is a "king's way" to cope with all possible problems. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] suggestion: log_statement = sample
In response to Janning Vygen : > hi, > > http://archives.postgresql.org/pgsql-general/2009-03/msg00581.php > > This was my suggestion about introducing a statment to get a sample of SQL > statements. Nobody answered yet. Why not? i think my suggestion would help a > lot. Or was it kind of stupid? For my part, I don't think this would be useful. Since most of your queries are run by software, you're going to see a fairly predictable pattern to the queries, which means your sampling isn't going to be anywhere near random, thus it will still be inaccurate and incomplete. In my experience, I've found that enabling full logging for a short time (perhaps a few hours) gathers enough data to run through tools like pgFouine and find problem areas. Also, we have development servers that run automated tests, and since it's not critical that they be performant, we can run full query logging on them all the time. Additionally, we make sure our production systems have enough hardware behind them that we can add additional tasks without it affecting production use. All of these are (in my opinion) better approaches to the problem than yet another arbitrary query filtering technique. I mean, logging only the most time-consuming queries is already arbitrary enough (as you already stated). -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problems with 8.4, FLOAT8PASSBYVAL and x86_64 GNU/Linux
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Marko Kreen wrote: > On 7/16/09, Rafael Martinez wrote: >> >> Any other ideas? >> > >> > The version you compile against is not the version you have running. >> > >> >> Well, the only version I have installed on this server is 8.4.0 > > Yes, but the 8.4.0 you have running seems to be compiled with different > options than the 8.4.0 you compile against. > It is the same installation and I have checked that we only have one/same version installed. Are you sure this problem is related to compilation? Could the problem be anywhere else? Thanks so far for your time regards, - -- Rafael Martinez, Center for Information Technology Services University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.7 (GNU/Linux) iD8DBQFKXyE4BhuKQurGihQRAlqQAJ9Gv12fWZHZmwRGJRwHH54WMVb1OwCgkIa3 q9Dt3nWe9UYGzjfxq1UawEA= =5G4F -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problems with 8.4, FLOAT8PASSBYVAL and x86_64 GNU/Linux
On 7/16/09, Rafael Martinez wrote: > Marko Kreen wrote: > > On 7/16/09, Rafael Martinez wrote: > >> Peter Eisentraut wrote: > >> > > > >> > You need to recompile your module. > >> > >> We recompile the module automatically when a new postgres cluster gets > >> installed. The module has been compiled locally on the 64bit server that > >> is going to use it. > >> > >> Any other ideas? > > > > The version you compile against is not the version you have running. > > > > > Well, the only version I have installed on this server is 8.4.0 Yes, but the 8.4.0 you have running seems to be compiled with different options than the 8.4.0 you compile against. -- marko -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problems with 8.4, FLOAT8PASSBYVAL and x86_64 GNU/Linux
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Marko Kreen wrote: > On 7/16/09, Rafael Martinez wrote: >> Peter Eisentraut wrote: >> > >> > You need to recompile your module. >> >> We recompile the module automatically when a new postgres cluster gets >> installed. The module has been compiled locally on the 64bit server that >> is going to use it. >> >> Any other ideas? > > The version you compile against is not the version you have running. > Well, the only version I have installed on this server is 8.4.0 - -- Rafael Martinez, Center for Information Technology Services University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.7 (GNU/Linux) iD4DBQFKXxucBhuKQurGihQRAi66AJiNoPt8BWw/Re7/pWY+hDCS/5ZzAKCJt/P+ psR8cTaHE8NNFC/ZjtxHFA== =wKmk -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problems with 8.4, FLOAT8PASSBYVAL and x86_64 GNU/Linux
On 7/16/09, Rafael Martinez wrote: > Peter Eisentraut wrote: > > On Thursday 16 July 2009 12:14:48 Rafael Martinez wrote: > >> ERROR: incompatible library "/usr/local/lib/pg_uname_8.4.so": magic > >> block mismatch > >> DETAIL: Server has FLOAT8PASSBYVAL = true, library has false. > > > > You need to recompile your module. > > We recompile the module automatically when a new postgres cluster gets > installed. The module has been compiled locally on the 64bit server that > is going to use it. > > Any other ideas? The version you compile against is not the version you have running. -- marko -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Function does not exist
Pavel Stehule schrieb: 2009/7/16 Andreas Wenk : Pavel Stehule schrieb: 2009/7/16 dipesh mistry (Imap) : Hello, In my function i had defined addnewuser(integer,character,..), and i call this function by Java code. I had created function with integer datatype but database always gives me error, org.postgresql.util.PSQLException: ERROR: function addnewuser(bigint,character varying,) does not exist Why database gives me bigint error instead i had declare integer in function. Next i create one more function named adduser(bigint,character,). but then it gives me same error. are all others parameters really varchar? You can use explicit cast to varchar like SELECT addnewuser(19, ''::varchar, 'a'::varchar, Pavel, just a question. Why should it be necessary to add explicit typcasting here? I can't see the problem in more depth. I still think that the function is called with the wrong parameter. Would be cool to hear your points to understand the problem better ;-) ofcourse - explicit casting is hard method, and it's better don't use it. But some times java environments are too smart. Explicit cast should help with searching an problematic param. Pavel Thanks Pavel, then I suggest to first get the function running in psql and then check it in the Java environment ... if this has not be done allready ;-) Cheers Andy Another idea to be sure that the function is working correctly is to call the function in psql and see if an error is thrown ... if yes its a paramter problem. If no its an external problem (I think this is called by a Java app ... isn't it?). Cheers Andy regards Pavel Stehule -- Thanks, Dipesh If you are not confident, you are doing a trial run. Pavel Stehule wrote: Hello it's look like problem with casting. You defined function name(integer,...) but you call it with bigint param. Bigint cannot be casted to int - so you have to redefine your func - name(bigint, ... regards Pavel Stehule 2009/7/15 dipesh mistry (Imap) : Hello, I have create my own function name "addnewuser(integer,varchar.)", and when I call this function it gives me below error, org.postgresql.util.PSQLException: ERROR: function addnewuser(bigint, character varying, character varying, character varying, character varying, character varying, character varying, unknown, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, integer) does not exist Even though function exist why it gives me this error, we use postgres-8.3.7 latest. We install postgres by .tar,gz file. -- Thanks, Dipesh If you can't make a mistake, you can't make anything. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Function does not exist
2009/7/16 Andreas Wenk : > Pavel Stehule schrieb: >> >> 2009/7/16 dipesh mistry (Imap) : >>> >>> Hello, >>> >>> In my function i had defined addnewuser(integer,character,..), and i >>> call this function by Java code. >>> >>> I had created function with integer datatype but database always gives me >>> error, >>> org.postgresql.util.PSQLException: ERROR: function >>> addnewuser(bigint,character varying,) does not exist >>> >>> Why database gives me bigint error instead i had declare integer in >>> function. >>> >>> Next i create one more function named adduser(bigint,character,). >>> but then it gives me same error. >> >> are all others parameters really varchar? You can use explicit cast to >> varchar like >> >> SELECT addnewuser(19, ''::varchar, 'a'::varchar, > > Pavel, > > just a question. Why should it be necessary to add explicit typcasting here? > I can't see the problem in more depth. I still think that the function is > called with the wrong parameter. Would be cool to hear your points to > understand the problem better ;-) ofcourse - explicit casting is hard method, and it's better don't use it. But some times java environments are too smart. Explicit cast should help with searching an problematic param. Pavel > > Another idea to be sure that the function is working correctly is to call > the function in psql and see if an error is thrown ... if yes its a paramter > problem. If no its an external problem (I think this is called by a Java app > ... isn't it?). > > Cheers > > Andy > > >> regards >> Pavel Stehule >> >>> -- >>> Thanks, >>> Dipesh >>> If you are not confident, you are doing a trial run. >>> >>> >>> Pavel Stehule wrote: Hello it's look like problem with casting. You defined function name(integer,...) but you call it with bigint param. Bigint cannot be casted to int - so you have to redefine your func - name(bigint, ... regards Pavel Stehule 2009/7/15 dipesh mistry (Imap) : > Hello, > > I have create my own function name "addnewuser(integer,varchar.)", > and when I call this function it gives me below error, > > org.postgresql.util.PSQLException: ERROR: function addnewuser(bigint, > character varying, character varying, character varying, character > varying, > character varying, character varying, unknown, character varying, > character > varying, character varying, character varying, character varying, > character > varying, character varying, character varying, character varying, > character > varying, character varying, character varying, integer) does not exist > > Even though function exist why it gives me this error, we use > postgres-8.3.7 > latest. > We install postgres by .tar,gz file. > > -- > Thanks, > Dipesh > If you can't make a mistake, you can't make anything. >> > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Function does not exist
Pavel Stehule schrieb: 2009/7/16 dipesh mistry (Imap) : Hello, In my function i had defined addnewuser(integer,character,..), and i call this function by Java code. I had created function with integer datatype but database always gives me error, org.postgresql.util.PSQLException: ERROR: function addnewuser(bigint,character varying,) does not exist Why database gives me bigint error instead i had declare integer in function. Next i create one more function named adduser(bigint,character,). but then it gives me same error. are all others parameters really varchar? You can use explicit cast to varchar like SELECT addnewuser(19, ''::varchar, 'a'::varchar, Pavel, just a question. Why should it be necessary to add explicit typcasting here? I can't see the problem in more depth. I still think that the function is called with the wrong parameter. Would be cool to hear your points to understand the problem better ;-) Another idea to be sure that the function is working correctly is to call the function in psql and see if an error is thrown ... if yes its a paramter problem. If no its an external problem (I think this is called by a Java app ... isn't it?). Cheers Andy regards Pavel Stehule -- Thanks, Dipesh If you are not confident, you are doing a trial run. Pavel Stehule wrote: Hello it's look like problem with casting. You defined function name(integer,...) but you call it with bigint param. Bigint cannot be casted to int - so you have to redefine your func - name(bigint, ... regards Pavel Stehule 2009/7/15 dipesh mistry (Imap) : Hello, I have create my own function name "addnewuser(integer,varchar.)", and when I call this function it gives me below error, org.postgresql.util.PSQLException: ERROR: function addnewuser(bigint, character varying, character varying, character varying, character varying, character varying, character varying, unknown, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, integer) does not exist Even though function exist why it gives me this error, we use postgres-8.3.7 latest. We install postgres by .tar,gz file. -- Thanks, Dipesh If you can't make a mistake, you can't make anything. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problems with 8.4, FLOAT8PASSBYVAL and x86_64 GNU/Linux
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Peter Eisentraut wrote: > On Thursday 16 July 2009 12:14:48 Rafael Martinez wrote: >> ERROR: incompatible library "/usr/local/lib/pg_uname_8.4.so": magic >> block mismatch >> DETAIL: Server has FLOAT8PASSBYVAL = true, library has false. > > You need to recompile your module. Hei We recompile the module automatically when a new postgres cluster gets installed. The module has been compiled locally on the 64bit server that is going to use it. Any other ideas? This is the Makefile we use: - --- PG_SRC=/usr/local/src PG_LIB=/usr/local/lib SERVER_INCLUDES += -I $(shell /local/opt/pgsql-8.4/bin/pg_config - --includedir) SERVER_INCLUDES += -I $(shell /local/opt/pgsql-8.4/bin/pg_config - --includedir-server) CFLAGS = $(SERVER_INCLUDES) CC = gcc all:clean pg_uname_8.4 install pg_uname_8.4: pg_uname_8.4.c $(CC) $(CFLAGS) -fpic -c $< $(CC) $(CFLAGS) -shared -o $(basename $<).so $(basename $<).o install: cp $(PG_SRC)/pg_uname_8.4.so $(PG_LIB)/pg_uname_8.4.so clean: rm -f *.o rm -f *.so rm -f *~ - --- regards, - -- Rafael Martinez, Center for Information Technology Services University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.7 (GNU/Linux) iD8DBQFKXvoZBhuKQurGihQRAiJdAJ9HBG33gDF16Uiu+Z5QvGDHtnzj7gCaAmBz Lfll6Lshy8shhLK62lb3fMs= =sIud -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Getting list of tables used within a query
Hello you need analyze execution plan - parser does know nothing about table order. look on EXPLAIN statement regards Pavel Stehule -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Concurrency issue under very heay loads
"Raji Sridar (raji)" wrote: > > We use a typical counter within a transaction to generate order sequence > number and update the next sequence number. This is a simple next counter - > nothing fancy about it. When multiple clients are concurrently accessing > this table and updating it, under extermely heavy loads in the system (stress > testing), we find that the same order number is being generated for multiple > clients. Could this be a bug? Is there a workaround? Please let me know. As others have said: using a sequence/serial is best, as long as you can deal with gaps in the generated numbers. (note that in actual practice, the number of gaps is usually very small.) Without seeing the code, here's my guess as to what's wrong: You take out a write lock on the table, then acquire the next number, then release the lock, _then_ insert the new row. Doing this allows a race condition between number generation and insertion which could allow duplicates. Am I right? Did I guess it? If so, you need to take out the lock on the table and hold that lock until you've inserted the new row. If none of these answers help, you're going to have to show us your code, or at least a pared down version that exhibits the problem. [I'm stripping off the performance list, as this doesn't seem like a performance question.] -- Bill Moran http://www.potentialtech.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] psql \du
On Thursday 16 July 2009 02:12:05 ANdreas Wenk wrote: > Hi, > > I recognized in psql using the internal help (\?) that the *+* sign is > missing for the shortcut \du: > > # \du >List of roles >Role name | Attributes | Member of > --+--+--- > > # \du+ > List of roles >Role name | Attributes | Member of | Description > --+--+---+- > > Where shall I place this info? Is this also a bug? Looks like the help is missing this info. If you could provide a patch that also fixes up the translations, that would be most helpful. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] suggestion: log_statement = sample
hi, http://archives.postgresql.org/pgsql-general/2009-03/msg00581.php This was my suggestion about introducing a statment to get a sample of SQL statements. Nobody answered yet. Why not? i think my suggestion would help a lot. Or was it kind of stupid? kind regards Janning -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Function does not exist
2009/7/16 dipesh mistry (Imap) : > Hello, > > In my function i had defined addnewuser(integer,character,..), and i > call this function by Java code. > > I had created function with integer datatype but database always gives me > error, > org.postgresql.util.PSQLException: ERROR: function > addnewuser(bigint,character varying,) does not exist > > Why database gives me bigint error instead i had declare integer in > function. > > Next i create one more function named adduser(bigint,character,). > but then it gives me same error. are all others parameters really varchar? You can use explicit cast to varchar like SELECT addnewuser(19, ''::varchar, 'a'::varchar, regards Pavel Stehule > > -- > Thanks, > Dipesh > If you are not confident, you are doing a trial run. > > > Pavel Stehule wrote: >> >> Hello >> >> it's look like problem with casting. You defined function >> name(integer,...) but you call it with bigint param. Bigint cannot be >> casted to int - so you have to redefine your func - name(bigint, ... >> >> regards >> Pavel Stehule >> >> 2009/7/15 dipesh mistry (Imap) : >> >>> >>> Hello, >>> >>> I have create my own function name "addnewuser(integer,varchar.)", >>> and when I call this function it gives me below error, >>> >>> org.postgresql.util.PSQLException: ERROR: function addnewuser(bigint, >>> character varying, character varying, character varying, character >>> varying, >>> character varying, character varying, unknown, character varying, >>> character >>> varying, character varying, character varying, character varying, >>> character >>> varying, character varying, character varying, character varying, >>> character >>> varying, character varying, character varying, integer) does not exist >>> >>> Even though function exist why it gives me this error, we use >>> postgres-8.3.7 >>> latest. >>> We install postgres by .tar,gz file. >>> >>> -- >>> Thanks, >>> Dipesh >>> If you can't make a mistake, you can't make anything. > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problems with 8.4, FLOAT8PASSBYVAL and x86_64 GNU/Linux
On Thursday 16 July 2009 12:14:48 Rafael Martinez wrote: > ERROR: incompatible library "/usr/local/lib/pg_uname_8.4.so": magic > block mismatch > DETAIL: Server has FLOAT8PASSBYVAL = true, library has false. You need to recompile your module. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] best practice transitioning from one datatype to another
On Jul 16, 5:46 pm, a.w...@netzmeister-st-pauli.de (Andreas Wenk) wrote: > Arndt Lehmann schrieb: > > > On Jul 16, 8:05 am, t...@sss.pgh.pa.us (Tom Lane) wrote: > >> CG writes: > >>> While transitioning from 8.1 to 8.4, I need to transition to the internal > >>> UUID type in place of the contrib/uniqueidentifier module. I've built the > >>> database around uniqueidentifier, so nearly every table has one column of > >>> that data type. It's going to be tedious to > >>> ALTER TABLE foo ALTER COLUMN bar SET DATA TYPE uuid; > >>> ...repeat 600 times... > >>> I'll also have to drop and reload the views and the rules on tables. > >>> It'll be tedious even if the tables have no data in them. > >>> Can anyone recommend a better/faster way to make the transition? > >> Couldn't you rename the type to uuid in the 8.1 database before you > >> dump? > > >> regards, tom lane > > >> -- > >> Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) > >> To make changes to your > >> subscription:http://www.postgresql.org/mailpref/pgsql-general > > > Just an idea - don't know if it works, first try on a test server: > > 1. Dump the complete database into text format (pg_dump --format=t) > > 2. Do a search and replace from "uniqueidentifier" to "uuid" > > 3. Reimport > > > Best Regards, > > Arndt Lehmann > > uh - --format=t means tar format. --format=p means plain text ... > > Or am I missing something? > > $pg_dump --help > Usage: > pg_dump [OPTION]... [DBNAME] > > General options: > ... > -F, --format=c|t|p output file format (custom, tar, plain text) > ... > > Cheers > > Andy > > -- > Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) > To make changes to your > subscription:http://www.postgresql.org/mailpref/pgsql-general Hi Andy, You are perfectly right. My oversight. Best Regards, Arndt Lehmannn -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [PERFORM] Concurrency issue under very heay loads
May be a simple way would be to use a "SEQUENCE" database object. And
call nextval('your_sequence') to obtain the next unique value (of type
bigint).
According to PG docs
"http://www.postgresql.org/docs/8.4/interactive/functions-sequence.html";,
the sequence object has functions that "provide simple, multiuser-safe
methods for obtaining successive sequence values from sequence
objects. "
You may either provide this function as a default to the field in
which you'd like the unique values to go to.
OR
If you'd like to make use of this value before data is inserted to the
table simply call SELECT nextval('your_sequence') to obtain the next
unique bigint value which you may insert into the appropriate field in
your table and still the the value for later use maybe to populate a
child table.
Allan.
On Thu, Jul 16, 2009 at 11:15 AM, Albe Laurenz wrote:
> Raji Sridar wrote:
>> We use a typical counter within a transaction to generate
>> order sequence number and update the next sequence number.
>> This is a simple next counter - nothing fancy about it. When
>> multiple clients are concurrently accessing this table and
>> updating it, under extermely heavy loads in the system
>> (stress testing), we find that the same order number is being
>> generated for multiple clients. Could this be a bug? Is there
>> a workaround? Please let me know.
>
> Please show us your code!
>
> Yours,
> Laurenz Albe
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Autovacuum and pg_stat_reset()
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello We are playing around with different statistics provided by postgresql to get a better overview of our systems. Until postgresql 8.2, and the presence of 'stats_reset_on_server_start=on' + pg_postmaster_start_time(), we could calculate very easy many average values/sec. for our statistics. With 8.3 and later, it is not possible to do this anymore. But we are thinking that if we run pg_stat_reset() when the postgres server is startet we could achieve the same. Our question is: Does the use of pg_stat_reset() affects the statistics autovacuum uses to find out what to do and when this should be done? Can the use of pg_stat_reset() affect performance in any way? regards, - -- Rafael Martinez, Center for Information Technology Services University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.7 (GNU/Linux) iD8DBQFKXvU+BhuKQurGihQRAjz6AJ4r7i0aBZU17/u4xw/21q0fOQrWuQCfRj1h gfe9Z6yT2eZ1cqxfYet19og= =uf0D -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Using the GEQO
On Thursday 16 July 2009 09:02:53 Ms swati chande wrote: > 1. Since the value of geqo_threshold is12, does the geqo get automatically > activated for queries with 12 or more joins? No, not directly. It will get used for 12 joins with no predefined order. This is a bit complicated by the fact that even a predefined order like: a JOIN b ON (..) JOIN c ON (..) ... might get reordered if the number of joins is less than join_collapse_limit. See http://www.postgresql.org/docs/current/static/explicit-joins.html > 2. Can the geqo threshold be set to a smaller value like 4 or 5? Yes > 3. How do we come to know whether the geqo has been used to solve the query > or not? Does Explain Analyze report it? No, you cannot see it directly unless you recompile with different options. > 4. If we change the value of geqo_threshold do we have to change any other parameters necessarily? No. > Please let me know these basics on the use of the geqo. Why do you want to specifically use GEQO? Just Research? Normally you try not to get where GEQO is used ;-) Andres -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Working around spurious unique constraint errors due to SERIALIZABLE bug
SERIALIZABLE isolation level doesn't really conform to the spec because it doesn't deal with phantoms. The only case I've come across where this actually matters is when you're implementing some sort of "insert into table if not yet present" operation. This will typically result in a unique constraint violation.[*] Usually, constraint violations are programming errors, but not this one. It's more like a detected deadlock. Is there a way to tell this type of constraint violation from other types, so that the transaction can be restarted automatically (as if there was a deadlock)? Theoretically, PostgreSQL should detect that the conflicting row wasn't there when the snapshot for the transaction was taken, and somehow export this piece of information, but I'm not sure if it's available to the client. [*] One way to work around this is to batch inserts and eventually perform them in a background task which doesn't run in parallel, but this approach isn't always possible. -- Florian Weimer BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] overwrite the encoding of a database
On Thursday 16 July 2009 10:53:37 Massa, Harald Armin wrote: > Hello, > > I have an old PostgreSQL Database which was created with encoding = > SQL_ASCII. That is an old sin of mine; 9years ago I did not know better. > > Now I know better than to use SQL_ASCII. > > On the bright side: I am totally sure that the TEXT within that database is > in WIN1252 / CP1252 encoding. > > Is there any way to overwrite the encoding-information? something like > "udpate set pg_encodingcol='xxx' where > databasename='oldsin'; Would a dump+reload work? Andres -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] overwrite the encoding of a database
Hello, I have an old PostgreSQL Database which was created with encoding = SQL_ASCII. That is an old sin of mine; 9years ago I did not know better. Now I know better than to use SQL_ASCII. On the bright side: I am totally sure that the TEXT within that database is in WIN1252 / CP1252 encoding. Is there any way to overwrite the encoding-information? something like "udpate set pg_encodingcol='xxx' where databasename='oldsin'; Best wishes, Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 no fx, no carrier pigeon - LASIK good, steroids bad?
Re: [GENERAL] [PERFORM] Concurrency issue under very heay loads
Raji Sridar wrote: > We use a typical counter within a transaction to generate > order sequence number and update the next sequence number. > This is a simple next counter - nothing fancy about it. When > multiple clients are concurrently accessing this table and > updating it, under extermely heavy loads in the system > (stress testing), we find that the same order number is being > generated for multiple clients. Could this be a bug? Is there > a workaround? Please let me know. Please show us your code! Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Problems with 8.4, FLOAT8PASSBYVAL and x86_64 GNU/Linux
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Hello
We have a C function that works without problems when we use it with
postgresql 8.3 (32/64bit) and with postgreSQL 8.4 (32bit).
But with 8.4 in a 64bit server, postgresql generates this error when we
try to install the function:
ERROR: incompatible library "/usr/local/lib/pg_uname_8.4.so": magic
block mismatch
DETAIL: Server has FLOAT8PASSBYVAL = true, library has false.
This is the query we use to install the function:
CREATE OR REPLACE FUNCTION pg_uname(text) RETURNS text
AS '/usr/local/lib/pg_uname_8.4.so', 'pg_uname'
LANGUAGE c STRICT;
The only reference we have found about this is in the 8.4 changelog:
" ... Pass float8, int8, and related datatypes by value inside the
server on 64-bit platforms (Zoltan Boszormenyi)
Add configure option --disable-float8-byval to use the old behavior. As
above, this change might break old-style external C functions. ..."
We cannot find more information in the documentation about things we
should change so the function works with 8.4 without using
- --disable-float8-byval under the compilation of postgresql.
What do we need to change in the function to avoid this problem?
The function we are talking about is this one:
- ---
#include "postgres.h"
#include
#include "fmgr.h"
#include
#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif
PG_FUNCTION_INFO_V1(pg_uname);
Datum
pg_uname(PG_FUNCTION_ARGS)
{
text *argument = PG_GETARG_TEXT_P(0);
size_t argumentlen = VARSIZE(argument)-VARHDRSZ;
text *result = (text *) palloc(256);
char *option = (char *) palloc(argumentlen+1);
char sysname[] = "sysname";
char nodename[] = "nodename";
char release[] = "release";
char version[] = "version";
char machine[] = "machine";
char null[] = "null";
struct utsname uname_pointer;
uname(&uname_pointer);
memcpy(option,VARDATA(argument),argumentlen);
option[argumentlen] = '\0';
if (strcmp(option,sysname) == 0){
SET_VARSIZE(result, strlen(uname_pointer.sysname) + VARHDRSZ);
memcpy(VARDATA(result),uname_pointer.sysname,strlen(uname_pointer.sysname));
}
else if (strcmp(option,nodename) == 0){
SET_VARSIZE(result, strlen(uname_pointer.nodename) + VARHDRSZ);
memcpy(VARDATA(result),uname_pointer.nodename,strlen(uname_pointer.nodename));
}
else if (strcmp(option,release) == 0){
SET_VARSIZE(result, strlen(uname_pointer.release) + VARHDRSZ);
memcpy(VARDATA(result),uname_pointer.release,strlen(uname_pointer.release));
}
else if (strcmp(option,version) == 0){
SET_VARSIZE(result, strlen(uname_pointer.version) + VARHDRSZ);
memcpy(VARDATA(result),uname_pointer.version,strlen(uname_pointer.version));
}
else if (strcmp(option,machine) == 0){
SET_VARSIZE(result, strlen(uname_pointer.machine) + VARHDRSZ);
memcpy(VARDATA(result),uname_pointer.machine,strlen(uname_pointer.machine));
}
else{
memcpy(VARDATA(result),null,sizeof(null));
}
pfree(option);
PG_RETURN_TEXT_P(result);
}
- ---
Thanks in advance.
regards,
- --
Rafael Martinez,
Center for Information Technology Services
University of Oslo, Norway
PGP Public Key: http://folk.uio.no/rafael/
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.7 (GNU/Linux)
iD8DBQFKXu+GBhuKQurGihQRAqP/AJ4p7DPCwMYzEdO/cykEqf2QpSuiygCeMp9D
GGwluLrii7FRy6+GLo86P3I=
=APUL
-END PGP SIGNATURE-
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Getting list of tables used within a query
Hi, for need of our application we need to get list of tables, that are used within given SELECT query - we need them in order to track changes of the data, that may be returned by the query - it is some kind of caching mechanism, that checks db data modifications. Right now we create a temporary view and after that we use information_schema.view_table_usage - it works fine, but it has two disadvantages - firstly is quite slow and secondly only owner of the tables can do that, which means that application must use two types of connections, one for normal operations and second for getting list of tables. Is there any other way to achieve that without need of using owner role and without creating a view ? Browsing the PG source I see that include/parser/.. can be used, but its quite complicated (especially for me as I'm not to good in C programming) - maybe anyone has some examples how to use parser to achieve my goals or maybe someone already has got working code that does similar things ? Best wishes, ML -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] best practice transitioning from one datatype to another
Arndt Lehmann schrieb: On Jul 16, 8:05 am, t...@sss.pgh.pa.us (Tom Lane) wrote: CG writes: While transitioning from 8.1 to 8.4, I need to transition to the internal UUID type in place of the contrib/uniqueidentifier module. I've built the database around uniqueidentifier, so nearly every table has one column of that data type. It's going to be tedious to ALTER TABLE foo ALTER COLUMN bar SET DATA TYPE uuid; ...repeat 600 times... I'll also have to drop and reload the views and the rules on tables. It'll be tedious even if the tables have no data in them. Can anyone recommend a better/faster way to make the transition? Couldn't you rename the type to uuid in the 8.1 database before you dump? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general Just an idea - don't know if it works, first try on a test server: 1. Dump the complete database into text format (pg_dump --format=t) 2. Do a search and replace from "uniqueidentifier" to "uuid" 3. Reimport Best Regards, Arndt Lehmann uh - --format=t means tar format. --format=p means plain text ... Or am I missing something? $pg_dump --help Usage: pg_dump [OPTION]... [DBNAME] General options: ... -F, --format=c|t|p output file format (custom, tar, plain text) ... Cheers Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] psql \du
Peter Eisentraut schrieb: On Thursday 16 July 2009 02:12:05 ANdreas Wenk wrote: Hi, I recognized in psql using the internal help (\?) that the *+* sign is missing for the shortcut \du: # \du List of roles Role name | Attributes | Member of --+--+--- # \du+ List of roles Role name | Attributes | Member of | Description --+--+---+- Where shall I place this info? Is this also a bug? Looks like the help is missing this info. If you could provide a patch that also fixes up the translations, that would be most helpful. Moin, will be the first time for me but sure, I will do it ;-) Cheers Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] initdb --encoding=LATIN1 fails on Windows
Thanks. I guess my question is then, how should I configure Windows to have the right locale so that I can create a LATIN1 encoding cluster. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to DB export XML File in PostgreSQL?
On Thursday 16 July 2009 09:03:09 Steve Choi wrote: > Hello. Thank you for your int > If I queryed on PostgreSQL, SELECT Item_ID FROM Engine, > > I want to export the result to Single XML file. How can I make it? Have a > nice day. Thank you for your answer. :) http://www.postgresql.org/docs/current/static/functions-xml.html#FUNCTIONS-XML-MAPPING -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] initdb --locale=LATIN1 fails on Windows
On Wednesday 15 July 2009 23:02:47 Abraham, Danny wrote: > Actually the command is:initdb --encoding=LATIN1. > > It fails on Windos. fails on Windows > > The same command worked fine on PG 8.2.4. > > Now, using 8.3.7, the command succeeds only with encoding 1252. > It comments something on wrong lc_ctype. You need to also set the right locale to match the encoding you chose. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Using the GEQO
Hi, I have a few queries on the use of the geqo. 1. Since the value of geqo_threshold is12, does the geqo get automatically activated for queries with 12 or more joins? 2. Can the geqo threshold be set to a smaller value like 4 or 5? 3. How do we come to know whether the geqo has been used to solve the query or not? Does Explain Analyze report it? 4. If we change the value of geqo_threshold do we have to change any other parameters necessarily? Please let me know these basics on the use of the geqo. Thanks Regards Swati
Re: [GENERAL] Function does not exist
Hello, In my function i had defined addnewuser(integer,character,..), and i call this function by Java code. I had created function with integer datatype but database always gives me error, org.postgresql.util.PSQLException: ERROR: function addnewuser(bigint,character varying,) does not exist Why database gives me bigint error instead i had declare integer in function. Next i create one more function named adduser(bigint,character,). but then it gives me same error. -- Thanks, Dipesh If you are not confident, you are doing a trial run. Pavel Stehule wrote: Hello it's look like problem with casting. You defined function name(integer,...) but you call it with bigint param. Bigint cannot be casted to int - so you have to redefine your func - name(bigint, ... regards Pavel Stehule 2009/7/15 dipesh mistry (Imap) : Hello, I have create my own function name "addnewuser(integer,varchar.)", and when I call this function it gives me below error, org.postgresql.util.PSQLException: ERROR: function addnewuser(bigint, character varying, character varying, character varying, character varying, character varying, character varying, unknown, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, integer) does not exist Even though function exist why it gives me this error, we use postgres-8.3.7 latest. We install postgres by .tar,gz file. -- Thanks, Dipesh If you can't make a mistake, you can't make anything. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
