[GENERAL] Connection Oracle database from Postgres function
I want to access Oracle database from
Postgres. One way to use DBI-LINK and use plperl function in Postgres
to make connection with Oracle. Using that I am getting the following error.
Environment
Solaris 9 SPARC
PostgreSQL 8.0
DBI-LINK
Perl-5.8.5
---
My Function
CREATE FUNCTION perl_max (integer, integer) RETURNS
integer AS $$
if ($_[0] $_[1]) { return $_[0]; }
return $_[1];
$$ LANGUAGE plperl;
---
On running this above function from sql
prompt, I am getting this error, and connection with database lost
test=# select perl_max(1,2);
server closed the connection unexpectedly
This probably means the server
terminated abnormally
before or while processing the
request.
The connection to the server was lost.
Attempting reset: Failed.
Re: [GENERAL] help needed for functions
Yes, you can use SETOF for multiple records. See Examples --- CREATE TABLE department(id int primary key, name text); CREATE TABLE employee(id int primary key, name text, salary int, departmentid int references department); - INSERT INTO department values (1, 'Management'); INSERT INTO department values (2, 'IT'); INSERT INTO employee values (1, 'John Smith', 3, 1); INSERT INTO employee values (2, 'Jane Doe', 5, 1); INSERT INTO employee values (3, 'Jack Jackson', 6, 2); - CREATE OR REPLACE FUNCTION GetEmployees() RETURNS SETOF employee AS 'select * from employee;' LANGUAGE 'sql'; - CREATE TYPE HOLDER AS (departmentid int, totalsalary int8); - CREATE or replace FUNCTION SqlDepartmentSalaries() RETURNS SETOF holder AS ' select departmentid, sum(salary) as totalsalary from GetEmployees() as a group by departmentid ' LANGUAGE 'sql'; select * from SqlDepartmentSalaries(); - --We can do the same in PLPGSQL in this way. CREATE OR REPLACE FUNCTION PLpgSQLDepartmentSalaries() RETURNS SETOF holder AS ' DECLARE r holder%rowtype; BEGIN FOR r in select departmentid, sum(salary) as totalsalary from GetEmployees() group by departmentid LOOP return next r; END LOOP; RETURN; END ' LANGUAGE 'plpgsql'; - Thanks Dinesh Pandey From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Nitin Ahire Sent: Friday, September 16, 2005 7:21 PM To: pgsql-general@postgresql.org Subject: [GENERAL] help needed for functions Hello All, I am new to postgresql database. I am transferring current database from mssql database to postgresql 7.4 I am facing problems for stored procedures. Is their any way so that I can transfer my existing stored procedure from mssql to postgre ? I already read about functions tried to implement it but I could not found any way to get multiple columns from a function. Also I would like to get help about selecting values from two tables using function. Thanks Nitin __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: [GENERAL] 64 bits?
You can use Postgres 8.x, it supports 64 bit (if your OS supports 64 bit). But you have to install it -- ./configure --enable-integer-datetimes make make install -- Thanks Dinesh Pandey -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of nicolas hafner Sent: Wednesday, September 07, 2005 4:51 PM To: pgsql-general@postgresql.org Subject: [GENERAL] 64 bits? Hi, I just wanted to know if there is a specific version of PostGreSQL for 64 Bits CPU (AMD/Intel) on a platform like Linux or Windows XP 64. thx ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Postgres for Fedora Core 2 OS ****************
From where can I download? Postgres 8.x + required packages and installation instruction of Postgres for Fedora Core 2 OS. Thanks Dinesh Pandey
[GENERAL] Permission denied for language pltclu
Hi, I am using Postgres-.8.0.1. I am creating a function with pltclu language. I have already created database with pltclu language. But on creation this function I am getting this error and failed to create this function - ERROR:Permission denied for language pltclu - What is the cause? Regards Dinesh Pandey -- Dinesh Pandey Sr. Software Engineer Second Foundation (India) Pvt. Ltd. Plot# 52 Industrial Area, Phase II Chandigarh. (India) PH: (O) 0172-2639202, Extn: 233
Re: [GENERAL] [SQL] Permission denied for language pltclu
I have installed the Postgres from postgres user with pltcl option and able to create these function with another dbUSER successfully and never get this error. But our client is getting this error, How to solve it now? Any Idea? Thanks Dinesh Pandey -Original Message- From: Michael Glaesemann [mailto:[EMAIL PROTECTED] Sent: Friday, June 10, 2005 12:58 PM To: [EMAIL PROTECTED] Cc: pgsql-general@postgresql.org; 'PostgreSQL' Subject: Re: [SQL] Permission denied for language pltclu On Jun 10, 2005, at 4:10 PM, Dinesh Pandey wrote: I am using Postgres-.8.0.1. I am creating a function with pltclu language. I have already created database with pltclulanguage. But on creation this function I am getting this error and failed to create this function - ERROR: Permission denied for language pltclu - What is the cause? It means that the user you are creating the function as does not have permission to use pltclu. Only superusers can create functions using untrusted languages. http://www.postgresql.org/docs/8.0/interactive/pltcl.html Hope this helps. Michael Glaesemann grzm myrealbox com
Re: [GENERAL] [SQL] Permission denied for language pltclu
Sorry I didn't get it exactly. Because the same function (send e-mail) I am able to create at my end, but our client is not able to create it at their end. 1. Is there some problem in installation? Or 2. Problem with system user permission executing that database? OR 3. Problem With Database user permission? Now how to change permission of that user to be able to create this function? If you created an untrusted function as user dbUSER then it was a superuser too. Honest. Thanks Dinesh ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] [SQL] Permission denied for language pltclu
OR 3. Problem With Database user permission? Only a superuser can create a pltclu function. dbUSER must be a PostgreSQL superuser if it created the pltclu function. You client must use a PostgreSQL superuser to create a pltclu function. What do mean with super user. The user who has installed the Postgres (like I have installed it using Postgres user) and initialized the pgsql/data? I am able to create this pltcl function with another dataman user but the same getting failed at out client end. Now pls tell me how to fix it, so that I can tell our client. Now how to change permission of that user to be able to create this function? Make sure the user creating the function is a PostgreSQL superuser.
Re: [GENERAL] [SQL] Permission denied for language pltclu
Hi Richard/ Michael Thanks for your great help. I got the problem. Actually, I was not getting the cause of this problem, because it was working properly at our end. Actually this problem occurs when the function is being created by the user who has not created the current database. Solution: The database must be created by the user who is creating the pltcl function? Right Thanks Dinesh Pandey -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Richard Huxton Sent: Friday, June 10, 2005 2:41 PM To: [EMAIL PROTECTED] Cc: 'Michael Glaesemann'; pgsql-general@postgresql.org; 'PostgreSQL' Subject: Re: [GENERAL] [SQL] Permission denied for language pltclu Dinesh Pandey wrote: Sorry I didn't get it exactly. Because the same function (send e-mail) I am able to create at my end, but our client is not able to create it at their end. 1. Is there some problem in installation? 2. Problem with system user permission executing that database? 3. Problem With Database user permission? Number 3 - it is to do with a PostgreSQL user account. That user needs to be a superuser. Now how to change permission of that user to be able to create this function? A good place to start with this sort of thing is the manuals. In the 7.4 manuals, I'd start with: Ch 36.1. Installing Procedural Languages Ch 17.2. User Attributes Reference I - the ALTER USER command Note that you may want to make the client's user a superuser just long enough to install the language and/or functions. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] [SQL] Permission denied for language pltclu
In short, a superuser is a user who can create other users. But if the user is not super user, he is not allowed to install the language 'plpgsql' and 'pltcl' for database. But my problem was the language is already installed but getting error on creation of the function. And if any one is creating this function who is not owner of database, this problem occurs. Thanks Dinesh Pandey
Re: [GENERAL] Postgres 8.0.1 configure failed
Sorry I forgot to set LD_LIBRARY_PATH , after setting able to install. LD_LIBRARY_PATH=/usr/local/lib export LD_LIBRARY_PATH Thanks Dinesh Pandey -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Neil Conway Sent: Wednesday, June 08, 2005 11:42 AM To: [EMAIL PROTECTED] Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Postgres 8.0.1 configure failed Dinesh Pandey wrote: ./configure --enable-integer-datetimes --prefix=/usr/local/pgsql --with-tclconfig=/usr/local/lib --with-tcl configure: error: *** Could not execute a simple test program. This may be a problem *** related to locating shared libraries. Check the file 'config.log' *** for the exact reason. Sounds like good advice to me -- what does config.log say? -Neil ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Postgres 8.0.1 configure failed
I am installing postgres 8.0.1 (solaris 9 SPARC), but while configure it is failing. (I have already installed gcc/tcl/tk and tested a simple c program) ./configure --enable-integer-datetimes --prefix=/usr/local/pgsql --with-tclconfig=/usr/local/lib --with-tcl ERROR checking for strtoll... yes checking for strtoull... yes checking for atexit... yes checking for _LARGEFILE_SOURCE value needed for large files... no checking for fseeko... (cached) yes checking test program... failed configure: error: *** Could not execute a simple test program. This may be a problem *** related to locating shared libraries. Check the file 'config.log' *** for the exact reason. # # # Thanks Dinesh
[GENERAL] index row size 2728 exceeds btree maximum, 2713
TABLE ---+---+--- Column | Type ---+---+--- scan_id | bigint host_ip | character varying(15) port_num | integer plugin_id | integer severity | character varying(50) data | text Indexes: pk_scanned_port_info PRIMARY KEY, btree (scan_id, host_ip, port_num, plugin_id, severity, data) On inserting record I am getting this error index row size 2728 exceeds btree maximum, 2713 How to solve this problem?
Re: [GENERAL] [SQL] index row size 2728 exceeds btree maximum, 2713
Hi, One of the columns in primary key is of type TEXT. I am able to insert with small data, but for around 3000 characters its failing. How to handle that? Thanks Dinesh Pandey From: Ramakrishnan Muralidharan [mailto:[EMAIL PROTECTED] Sent: Thursday, June 02, 2005 3:11 PM To: [EMAIL PROTECTED]; pgsql-general@postgresql.org; PostgreSQL Subject: RE: [SQL] index row size 2728 exceeds btree maximum, 2713 Hi, The issue looks like your Index widthexceeds the maximum width of the index key limit, Please review the keys used in the index. Regards, R.Muralidharan -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Dinesh Pandey Sent: Thursday, June 02, 2005 12:35 PM To: pgsql-general@postgresql.org; 'PostgreSQL' Subject: [SQL] index row size 2728 exceeds btree maximum, 2713 TABLE ---+---+--- Column | Type ---+---+--- scan_id | bigint host_ip | character varying(15) port_num | integer plugin_id | integer severity | character varying(50) data | text Indexes: pk_scanned_port_info PRIMARY KEY, btree (scan_id, host_ip, port_num, plugin_id, severity, data) On inserting record I am getting this error index row size 2728 exceeds btree maximum, 2713 How to solve this problem?
Re: [GENERAL] index row size 2728 exceeds btree maximum, 2713
I am inserting some log messages in the column data. (Basically I am inserting records from reading an xml file) In the PRIMARY KEY, btree (scan_id, host_ip, port_num, plugin_id, severity, data) data is of type TEXT and can contain long string values. The question is how to remove this error index row size 2728 exceeds btree maximum, 2713 by increasing the btree size? The big problem is I can not add any additional column in this table. Thanks Dinesh Pandey -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Richard Huxton Sent: Thursday, June 02, 2005 3:29 PM To: [EMAIL PROTECTED] Cc: pgsql-general@postgresql.org; 'PostgreSQL' Subject: Re: [GENERAL] index row size 2728 exceeds btree maximum, 2713 Dinesh Pandey wrote: ---+---+--- Column| Type ---+---+--- scan_id| bigint host_ip| character varying(15) port_num | integer plugin_id | integer severity | character varying(50) data | text Indexes: pk_scanned_port_info PRIMARY KEY, btree (scan_id, host_ip, port_num, plugin_id, severity, data) On inserting record I am getting this error index row size 2728 exceeds btree maximum, 2713 Well - the error message is clear enough. The question is, what to do. Without knowing what the table means, it's difficult to say what the primary-key should be, but it seems unlikely to include an unlimited-length text-field called data. If the data itself doesn't offer any suitable candidate keys (as can well be the case) then common practice is to generate a unique number and use that as an ID - in PostgreSQL's case by use of the SERIAL pseudo-type. Does that help? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] [SQL] index row size 2728 exceeds btree maximum, 2713
Yes I am storing some error messages in data column, and the PK columns are party of search criteria. Thanks Dinesh Pandey From: Ramakrishnan Muralidharan [mailto:[EMAIL PROTECTED] Sent: Thursday, June 02, 2005 4:44 PM To: [EMAIL PROTECTED]; pgsql-general@postgresql.org; PostgreSQL Subject: RE: [SQL] index row size 2728 exceeds btree maximum, 2713 Hi It is not advisable to add a variable length data field in the Index key, since it is very difficult predict the size of the field which may vary from record to record. are you included this field for Full text search on data field? Regards, R.Muralidharan -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Dinesh Pandey Sent: Thursday, January 01, 2004 3:14 PM To: Ramakrishnan Muralidharan; pgsql-general@postgresql.org; 'PostgreSQL' Subject: Re: [SQL] index row size 2728 exceeds btree maximum, 2713 Hi, One of the columns in primary key is of type TEXT. I am able to insert with small data, but for around 3000 characters its failing. How to handle that? Thanks Dinesh Pandey From: Ramakrishnan Muralidharan [mailto:[EMAIL PROTECTED] Sent: Thursday, June 02, 2005 3:11 PM To: [EMAIL PROTECTED]; pgsql-general@postgresql.org; PostgreSQL Subject: RE: [SQL] index row size 2728 exceeds btree maximum, 2713 Hi, The issue looks like your Index widthexceeds the maximum width of the index key limit, Please review the keys used in the index. Regards, R.Muralidharan -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Dinesh Pandey Sent: Thursday, June 02, 2005 12:35 PM To: pgsql-general@postgresql.org; 'PostgreSQL' Subject: [SQL] index row size 2728 exceeds btree maximum, 2713 TABLE ---+---+--- Column | Type ---+---+--- scan_id | bigint host_ip | character varying(15) port_num | integer plugin_id | integer severity | character varying(50) data | text Indexes: pk_scanned_port_info PRIMARY KEY, btree (scan_id, host_ip, port_num, plugin_id, severity, data) On inserting record I am getting this error index row size 2728 exceeds btree maximum, 2713 How to solve this problem?
[GENERAL] How can I write trigger on a columns insert/update?
How can I write trigger on a columns insert/update? CREATE TRIGGER mytrigger BEFORE INSERT OR UPDATE OF mycolumn ON mytable FOR EACH ROW EXECUTE PROCEDURE myfunction(); I am getting error syntax error at or near OF Thanks
Re: [GENERAL] ORDER BY options (how to order data as AAA, aaa, BBB, bbb, ... ZZZ, zzz)
SELECT * FROM MY_TABLE ORDER BY LOWER(NAME); Thanks Dinesh Pandey -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Julian Legeny Sent: Tuesday, May 10, 2005 3:12 PM To: pgsql-general@postgresql.org Subject: [GENERAL] ORDER BY options (how to order data as AAA, aaa, BBB, bbb, ... ZZZ, zzz) Hello, I have following problem: I have table MY_TABLE with following records: NAME --- ccc CCC AAA aaa bbb BBB When I use default select that sort all data by NAME: SELECT * FROM MY_TABLE ORDER BY NAME; result is following: NAME --- AAA BBB CCC aaa bbb ccc But I would like to sort all data as following: NAME --- AAA aaa BBB bbb CCC ccc How can I write sql command (or set up ORDER BY options) for selecting that? Thank you in advance for answer, best regards, Julian Legeny mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] database export in pgsql
What is the command you have used while dumping the objects? Thanks Dinesh Pandey -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Vikas Sent: Wednesday, May 11, 2005 10:00 AM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] database export in pgsql Richard Huxton wrote: Vikas wrote: Hi, Im having problems in exporting my local pgdb to my server with data. I do a backup and everytime i get stuck in functions or some $libdir or function pg_logdir_ls() etc. pg_dump shouldn't ever get stuck at any point. It was not the pg_dump which got stuck. it was the importing the generated SQL where I got stuck. How do I prevent functions of other system type objects from being dumped in the SQL? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] database export in pgsql
http://www.postgresql.org/docs/8.0/static/app-pgdump.html Thanks Dinesh Pandey -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Vikas Sent: Wednesday, May 11, 2005 10:57 AM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] database export in pgsql Im doing through the GUI... pg_dump.exe -i -h 192.168.1.31 -p 5432 -U postgres -F p -v -f c:\del.sql -n public DBNAME Dinesh Pandey wrote: What is the command you have used while dumping the objects? Thanks Dinesh Pandey -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Vikas Sent: Wednesday, May 11, 2005 10:00 AM To: pgsql-general@postgresql.org Subject: Re: [GENERAL] database export in pgsql Richard Huxton wrote: Vikas wrote: Hi, Im having problems in exporting my local pgdb to my server with data. I do a backup and everytime i get stuck in functions or some $libdir or function pg_logdir_ls() etc. pg_dump shouldn't ever get stuck at any point. It was not the pg_dump which got stuck. it was the importing the generated SQL where I got stuck. How do I prevent functions of other system type objects from being dumped in the SQL? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] can I send execution log into a file.
I am using Postgres. How can I send execution log into a file. Testdb=\i MyCreateScript.sql Thanks
Re: [GENERAL] Date addition/subtraction
Select current_timestamp - '30 day'::interval Select current_timestamp - '1 hour'::interval Thanks Dinesh Pandey -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Craig Bryden Sent: Tuesday, May 03, 2005 11:02 PM To: pgsql Subject: [GENERAL] Date addition/subtraction Hi How in postgres can I do date/time subtraction or addition. e.g. If I want to get today's date - 30 days? or current_timestamp - 1 hour? Thanks Craig ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] unable to open editor.
In Solaris 10 with Postgres 8.0, I am getting this error and unable to open editor. testdb=# \e test.sql ttdt_open failed: TT_ERR_PROCID The process id passed is not valid. Thanks
Re: [GENERAL] unable to open editor.
In Solaris 10 with Postgres 8.0, I am getting only for EDITOR. bash-2.05b$ echo $EDITOR /usr/dt/bin/dtpad bash-2.05b$ echo $VISUAL bash-2.05b$ bash-2.05b$ echo $PSQL_EDITOR bash-2.05b$ Thanks Dinesh Pandey -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Michael Fuhr Sent: Monday, May 02, 2005 8:32 PM To: Dinesh Pandey Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] unable to open editor. On Mon, May 02, 2005 at 06:06:46PM +0530, Dinesh Pandey wrote: In Solaris 10 with Postgres 8.0, I am getting this error and unable to open editor. testdb=# \e test.sql ttdt_open failed: TT_ERR_PROCID The process id passed is not valid. This looks like a ToolTalk error. What editor are you trying to use? What's the value of the PSQL_EDITOR or EDITOR or VISUAL environment variable? Have you googled for this error message to see what might cause it and how to fix it? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Postgres source (tar file) for Fedora Core OS?
From where can I download latest Postgres source (tar file) for Fedora Core OS? Regards Dinesh Pandey
[GENERAL] FW: How to install Postgres that supports 64-bit integer/date-time.
Continued.. I solved this problem by myself. A. For 64 bit development you need these packages installed on Solaris server: SUNWarcx, SUNWbtoox, SUNWdplx, SUNWscpux, SUNWsprox, SUNWtoox, SUNWlmsx, SUNWlmx, SUNWlibCx Pls confirm these using the following command. pkginfo SUNWarcx pkginfo SUNWbtoox pkginfo SUNWdplx pkginfo SUNWscpux pkginfo SUNWsprox, pkginfo SUNWtoox pkginfo SUNWlmsx pkginfo SUNWlmx pkginfo SUNWlibCx B. Install missing Solaris packages. Insert Solaris software CD 1/2. Log in as root. pkgadd -d /cdrom/Solaris_9/Product package name eject cdrom C. Check Solaris OS mode and supported bits isainfo -v 64-bit sparcv9 applications 32-bit sparc applications D. To compile a 64-bit application on an UltraSparc with a recent Sun Compiler. getconf -a | grep v9 XBS5_LP64_OFF64_CFLAGS: -xarch=v9 XBS5_LP64_OFF64_LDFLAGS: -xarch=v9 . . E. The transitional compilation environment is obtained with the following compiler and linker flags: getconf LFS64_CFLAGS -D_LARGEFILE64_SOURCE F. The large file compilation environment is obtained with (A 64-bit application automatically has large file support built in by default.) getconf LFS_CFLAGS -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 G. Re-Configure the Postgres with 64-bit integer date/time support from its source: bash-2.03# cd postgres 8.0.1/ bash-2.03# ./configure --enable-integer-datetimes --prefix=/usr/local/pgsql --with-perl --with-tclconfig=/usr/local/lib --with-tcl checking build system type... sparc-sun-solaris2.8 checking host system type... sparc-sun-solaris2.8 checking which template to use... solaris checking whether to build with 64-bit integer date/time support... yes checking whether NLS is wanted... no checking for default port number... 5432 . . bash-2.03# make install Kindly let me know if you need any further clarification or any problem occurs. Thanks Dinesh Pandey From: Dinesh Pandey [mailto:[EMAIL PROTECTED]] Sent: Friday, April 22, 2005 11:00 AM To: 'PostgreSQL'; 'pgsql-general@postgresql.org' Subject: How to install Postgres that supports 64-bit integer/date-time. How to install Postgres 8.0.1 that supports 64-bit integer/date-time. # ./configure --prefix=/usr/local/pgsql --with-tclconfig=/usr/local/lib --with-tcl checking build system type... sparc-sun-solaris2.8 checking host system type... sparc-sun-solaris2.8 checking which template to use... solaris checking whether to build with 64-bit integer date/time support... no checking whether NLS is wanted... no checking for default port number... 5432 checking for gcc... gcc while installation I am getting this message: checking whether to build with 64-bit integer date/time support... no Thanks
Re: [GENERAL] ShmemAlloc: out of memory
http://www.postgresql.org/docs/8.0/interactive/kernel-resources.html#SYSVIPC Thanks Dinesh Pandey -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of JM Sent: Friday, April 22, 2005 2:49 PM To: pgsql-general@postgresql.org Subject: [GENERAL] ShmemAlloc: out of memory hi all, i need help on this.. i'm experiencing this problem too often and i need a permanent solution. im using 7.3.4 config file tcpip_socket = true max_connections = 150 superuser_reserved_connections = 2 port = 5432 #shared_buffers = 45800 shared_buffers = 47900 sort_mem = 4 #max_locks_per_transaction=160 max_locks_per_transaction=200 #fsync = true #wal_sync_method = fsync # # Locale settings # # (initialized by initdb -- may be changed) LC_MESSAGES = 'en_US.UTF-8' LC_MONETARY = 'en_US.UTF-8' LC_NUMERIC = 'en_US.UTF-8' LC_TIME = 'en_US.UTF-8' ... config ends im having problems on our DB.. # update table_fan set active_flag='n' where num='12345678'; WARNING: ShmemAlloc: out of memory ERROR: FreeSpaceMap hashtable out of memory temporary solution: restart the DB and all things went back to normal. trying the same SQL would result to an update.. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Table Partition
How to create this table (with
partition) in Postgres.
--ORACLE
CREATE TABLE A (col1 NUMBER NOT NULL,
col2 DATE NOT
NULL,
col3 VARCHAR2(500)
)
PARTITION BY RANGE (col2) (
PARTITION partition_one VALUES LESS
THAN (TO_DATE('01/04/2001', 'DD/MM/')) TABLESPACE MYTABSPACE
);
Thanks
Re: [GENERAL] List of Functions
\df \df functionname Thanks Dinesh Pandey -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Rob Stone Sent: Thursday, April 21, 2005 9:48 AM To: 'pgsql-general@postgresql.org' Subject: [GENERAL] List of Functions I have no idea if this is the correct list to use. How do you obtain a list of the functions and their argument lists that have been created on a Postgres database? I have tried searching various lists and archives plus the documentation. All I want to find is a list of:- Function name return valuearguments. After you run the create or replace script, this data has to be saved somewhere. Just the name of the table would do. Thanks, Robert Stone CONFIDENTIALITY NOTICE AND DISCLAIMER - The information in this transmission may be confidential and/or protected by legal privilege, and is intended only for the person or persons to whom it is addressed. If you are NOT such a person, you are warned that any disclosure, copying or dissemination of the information is unauthorised. If you have received this transmission in error, please advise Park Lane Information Technology -- +61 (03) 9813 1000 -- and permanently delete all copies of this transmission from your mail server(s), mail client(s) and from any physical records. Park Lane Information Technology has no liability (including liability in negligence) for any unauthorised use of the information contained in this transmission. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] How to install Postgres that supports 64-bit integer/date-time.
How to install Postgres 8.0.1 that supports 64-bit integer/date-time. # ./configure --prefix=/usr/local/pgsql --with-tclconfig=/usr/local/lib --with-tcl checking build system type... sparc-sun-solaris2.8 checking host system type... sparc-sun-solaris2.8 checking which template to use... solaris checking whether to build with 64-bit integer date/time support... no checking whether NLS is wanted... no checking for default port number... 5432 checking for gcc... gcc while installation I am getting this message: checking whether to build with 64-bit integer date/time support... no Thanks
[GENERAL] pltcl function.
What is error in this statement of pltcl function. set var 'SENDING EMAIL TO: '||$mailto||' from: '||$mailfrom||' with: '||$emailserver I am getting this error: ERROR: wrong # args: should be set varName ?newValue? Regards Dinesh Pandey
[GENERAL] How to add 1 hour in a date or time stamp?
How to add 1 hour in a date or time stamp? Regards Dinesh Pandey --
[GENERAL] 'Select INTO in Execute (dynamic query )
Hi Whats wrong with this code (ERROR: syntax error at or near INTO at character 8)? Problem: I want to put A1, A2 values in two variables vara, varb. CREATE OR REPLACE FUNCTION test(text) RETURNS VARCHAR AS $$ Declare vara VARCHAR(10) :=''; varb VARCHAR(10) :=''; result VARCHAR(10) :='Result'; BEGIN EXECUTE( 'Select INTO vara, varb A1, A2 from '|| $1 ); RETURN result||': '|| vara ||' '|| varb; END; $$ LANGUAGE plpgsql; Regards Dinesh Pandey
Re: [GENERAL] Urgent
Edit postgres.conf and pg_hba.conf to access database from a remote machine Edit postgres.conf: -- listen_addresse='*' Edit pg_hba.conf: -- hostall all 10.10.0.76 255.255.255.0 trust Thanks Dinesh Pandey -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of ElayaRaja S Sent: Tuesday, April 19, 2005 12:26 AM To: pgsql-general@postgresql.org Subject: [GENERAL] Urgent Hi, I am using Redhat linux 9. i had configure in pg_hba.conf as hostpostgres postgres 10.10.0.76 255.255.255.0 password If i try to connect with postgresql admin i am getting excpetion as An erro has occured: Error connecting to the server: could not connect to server: Connection refuesed(0x274D/10061) Is the server running on host 10.10.0.76 and accepting TCP/IP connections on port 5432? Please help me. -- Warm Regards, S.ElayaRaja Mobile: (+91) 98450 59540 E-Mail: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Exception handling: Oracle's SQLERRM keyword option?
Hi, I have installed PostgreSQL 8.0.1 on Solaris 9. I am porting my database from Oracle 9i to PostgreSQL. I am using PL/pgSQL language. In Oracle we can get errormessagefrom SQLERRM keyword and insert messages into log table. How can I get errormessage/error codein PostgreSQL after an EXCEPTION or RAISE EXCEPTION occurs in EXCEPTION block?? Pls help me or send me some example. Thanks Dinesh Pandey
