[SQL] Help - Oracle 9i to PostgreSQL SQL conversion
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 error message from "SQLERRM" keyword and inserting it into table. How can I get error message/code in PostgreSQL after an EXCEPTION or RAISE EXCEPTION occurs in EXCEPTION block?? Pls help me or send me some example. Fuction attached below Thanks Dinesh Pandey CREATE OR REPLACE FUNCTION DOES_NODE_HAVE_RULE (IN_SENTRYID_ID IN NUMBER ,IN_NODE_ID IN NUMBER ,IN_DEVICEID IN NUMBER ,IN_ACTION IN VARCHAR2 ) RETURN BOOLEAN IS does NUMBER(2) := 0; mesg VARCHAR2(500) := 'Does rule exist failed for sentry: '||in_sentryid_id||', node: '||in_node_id||'.'; c_context VARCHAR2(50) := 'DOES NODE HAVE RULE'; c_program VARCHAR2(100) := 'RULE_CONTROL.DOES_NODE_HAVE_RULE'; v_sql VARCHAR2(1000);BEGIN v_sql := 'SELECT COUNT(*) FROM PORTAL_'||in_action||'_NODE_RULE WHERE sentryid_id = '||in_sentryid_id|| ' AND node_id = '||in_node_id||' AND device_id ='||in_deviceid; EXECUTE IMMEDIATE v_sql INTO does; IF does > 0 THEN RETURN TRUE; ELSIF does = 0 THEN RETURN FALSE; END IF; EXCEPTION WHEN OTHERS THEN DATAMAN.log_error(c_program, c_context, 1, 'PACKAGE', 'OTHERS: '||mesg, SQLERRM); RAISE_APPLICATION_ERROR(-2,SUBSTR(SQLERRM,1,250)); END does_node_have_rule;/SHOW ERROR
Re: [SQL] gone blind - can't see syntax error
Try this FROM requests r left outer join users sm on sm.u_id = r.r_salesman left outer join users u on r.r_u_id = u.u_id left outer join request_types t on r.r_t_id = t.t_id left outer join request_states s on r.r_s_id = s.s_id left outer join dealerships d on r.r_d_id = d.d_id left outer join departments de on r.r_de_id = de.de_id left outer join customers c on r.r_c_id = c.c_id left outer join comment_tallies co on r.r_id = co.r_id ORDER BY r.r_id; -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Gary Stainburn Sent: Friday, April 01, 2005 5:06 PM To: [email protected] Subject: [SQL] gone blind - can't see syntax error Hi folks. I've been looking at this for 10 minutes and can't see what's wrong. Anyone care to enlighten me. Thanks Gary [EMAIL PROTECTED] webroot]# psql -a -f new-view.sql SELECT r.r_id, r.r_registration, r.r_chassis, r.r_vehicle, r.r_fuel, r.r_pack_mats, r.r_delivery, (date(r.r_delivery) - date(now())) AS r_remaining, r.r_created, r.r_completed, r.r_salesman, sm.r_salesman as salesman_name, d.d_des, de.de_des, u.u_id, u.u_userid, u.u_username, u.u_salesman, u.u_target, t.t_id, t.t_des, s.s_id, s.s_des, c.c_id, c.c_des, co.com_count, co.com_unseen FROM requests r, left outer join users sm on sm.u_id = r.r_salesman, left outer join users u on r.r_u_id = u.u_id, left outer join request_types t on r.r_t_id = t.t_id, left outer join request_states s on r.r_s_id = s.s_id, left outer join dealerships d on r.r_d_id = d.d_id, left outer join departments de on r.r_de_id = de.de_id, left outer join customers c on r.r_c_id = c.c_id, left outer join comment_tallies co on r.r_id = co.r_id ORDER BY r.r_id; psql:new-view.sql:19: ERROR: parser: parse error at or near "left" -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Speed up slow select - was gone blind
Try with creating INDEX on the used tables...It will make your search query faster. Thanks Dinesh Pandey -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Gary Stainburn Sent: Friday, April 01, 2005 6:03 PM To: [email protected] Subject: [SQL] Speed up slow select - was gone blind Hi folks. I've got my select working now, but I haven't received the speed increase I'd expected. It replaced an earlier select which combined a single explicit join with multiple froms. The first select is the old one, the second one is the new one (with a new join). The new one takes 24 seconds to run while the old one took 29. How can I redo the select to improve the speed, or what else can I do to optimaise the database? original (ugly) ~ SELECT r.r_id, r.r_registration, r.r_chassis, r.r_vehicle, r.r_fuel, r.r_pack_mats, r.r_delivery, (date(r.r_delivery) - date(now())) AS r_remaining, r.r_created, r.r_completed, r.r_salesman, r.salesman_name, d.d_des, de.de_des, u.u_id, u.u_userid, u.u_username, u.u_salesman, u.u_target, t.t_id, t.t_des, s.s_id, s.s_des, c.c_id, c.c_des, co.com_count, co.com_unseen FROM (SELECT r.r_id, r.r_t_id, r.r_d_id, r.r_de_id, r.r_s_id, r.r_registration, r.r_chassis, r.r_c_id, r.r_vehicle, r.r_fuel, r.r_pack_mats, r.r_delivery, r.r_salesman, r.r_created, r.r_completed, r.r_u_id, u.u_username AS salesman_name FROM (requests r LEFT JOIN users u ON ((r.r_salesman = u.u_id r, users u, request_types t, request_states s, dealerships d, departments de, customers c, comment_tallies co WHERE (r.r_d_id = d.d_id) AND (r.r_s_id = s.s_id) AND (r.r_c_id = c.c_id) AND (r.r_t_id = t.t_id) AND (r.r_d_id = d.d_id) AND (r.r_de_id = de.de_id) AND (r.r_u_id = u.u_id) AND (r.r_id = co.r_id)) ORDER BY r.r_id; new ~~~ SELECT r.r_id, r.r_registration, r.r_chassis, r.r_vehicle, r.r_fuel, r.r_pack_mats, r.r_delivery, (date(r.r_delivery) - date(now())) AS r_remaining, r.r_created, r.r_completed, r.r_salesman, sm.u_username as salesman_name, d.d_des, de.de_des, u.u_id, u.u_userid, u.u_username, u.u_salesman, u.u_target, t.t_id, t.t_des, s.s_id, s.s_des, c.c_id, c.c_des, co.com_count, co.com_unseen, pl.pl_id, pl.pl_desc as plates FROM requests r left outer join users sm on sm.u_id = r.r_salesman left outer join users u on r.r_u_id = u.u_id left outer join request_types t on r.r_t_id = t.t_id left outer join request_states s on r.r_s_id = s.s_id left outer join dealerships d on r.r_d_id = d.d_id left outer join departments de on r.r_de_id = de.de_id left outer join customers c on r.r_c_id = c.c_id left outer join comment_tallies co on r.r_id = co.r_id left outer join plates pl on r.r_plates = pl.pl_id ORDER BY r.r_id; -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Migrated Oracle database to PostgreSQL ??
Hi there, Has anyone migrated Oracle database to PostgreSQL? If yes, could you please help me - 1. How to migrate Procedures and Packages from Oracle to PostgreSQL 2. How to find error messages in PostgreSQL thrown from an Exception (as we can get messages in Oracle with "SQLERRM" keyword) for example -- --Raise an exception RAISE EXCEPTION 'No Data found for record id % ' ,recordid --Insert exception messages into database EXCEPTION IF RAISE_EXCEPTION THEN -- my_insert_function(SQLERRM); Thanks in advance. RegardsDinesh Pandey
Re: [SQL] Migrated Oracle database to PostgreSQL ??
Yes, but after creating some function, we can get sql error messages. But I not comfortable that much in that. For Procedures and Packages we can use Functions Any other option in pl/PGSQL Thanks Dinesh Pandey -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Yasir Malik Sent: Tuesday, April 05, 2005 1:06 AM To: PostgreSQL Subject: Re: [SQL] Migrated Oracle database to PostgreSQL ?? > 1. How to migrate Procedures and Packages from Oracle to PostgreSQL > > > 2. How to find error messages in PostgreSQL thrown from an Exception > (as we can get messages in Oracle with "SQLERRM" keyword) >for example -- > > --Raise an exception >RAISE EXCEPTION 'No Data found for record id % ' ,recordid > > --Insert exception messages into database >EXCEPTION >IF RAISE_EXCEPTION THEN > --my_insert_function(SQLERRM); All your answers can be found here: http://www.postgresql.org/docs/8.0/static/plpgsql.html I can't say if PL/pgSQL is as good as PL/SQL, though. Yasir ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] CURSOR EXAMPLE - ORACLE TO POSTGRES CONVERSION
Title: CURSOR EXAMPLE - ORACLE TO POSTGRES CONVERSION
CURSOR EXAMPLE - ORACLE TO POSTGRES CONVERSION
--Oracle
CREATE OR REPLACE FUNCTION MYCURSOR (
VARINA IN VARCHAR2
)
RETURN VARCHAR2
IS
v_sql VARCHAR2(2000);
alert_mesg VARCHAR2(32767);
IN_VAR1 VARCHAR2(10);
IN_VAR2 VARCHAR2(10);
V_COUNT NUMBER;
v_cursorid NUMBER;
v_dummy INTEGER;
v_source VARCHAR2(100);
BEGIN
v_cursorid := DBMS_SQL.OPEN_CURSOR;
v_sql := 'SELECT A1, A2, count(*) FROM A group by A1,A2';
--Parse the query.
DBMS_SQL.PARSE(v_cursorid, v_sql, DBMS_SQL.V7);
--Define output columns
DBMS_SQL.DEFINE_COLUMN(v_cursorid, 1, IN_VAR1, 10);
DBMS_SQL.DEFINE_COLUMN(v_cursorid, 2, IN_VAR2, 10);
DBMS_SQL.DEFINE_COLUMN(v_cursorid, 3, V_COUNT);
--Execute dynamic sql
v_dummy := DBMS_SQL.EXECUTE(v_cursorid);
LOOP
IF DBMS_SQL.FETCH_ROWS(v_cursorid) = 0 then
exit;
END IF;
DBMS_SQL.COLUMN_VALUE(v_cursorid,1,IN_VAR1);
DBMS_SQL.COLUMN_VALUE(v_cursorid,2,IN_VAR2);
--Build output string
alert_mesg := alert_mesg||rpad(IN_VAR1,20)||rpad(IN_VAR2,20);
END LOOP;
DBMS_SQL.CLOSE_CURSOR(v_cursorid);
RETURN alert_mesg;
EXCEPTION
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(v_cursorid);
RETURN 'No troubleshooting information at this time.'|| SQLERRM;
END MYCURSOR;
/
SHOW ERROR
=
--Oracle
CREATE OR REPLACE FUNCTION MYCURSOR (
VARINA IN VARCHAR2
)
RETURN VARCHAR2
IS
alert_mesg VARCHAR2(32767);
IN_VAR1 VARCHAR2(10);
IN_VAR2 VARCHAR2(10);
CURSOR MYCUR IS SELECT A1, A2, count(*) FROM A group by A1,A2;
BEGIN
FOR rec IN MYCUR LOOP
IN_VAR1 := rec.A1;
IN_VAR2 := rec.A2;
--Build output string
alert_mesg := alert_mesg||rpad(IN_VAR1,20)||rpad(IN_VAR2,20);
END LOOP;
RETURN alert_mesg;
EXCEPTION
WHEN OTHERS THEN
RETURN 'No troubleshooting information at this time.'|| SQLERRM;
END MYCURSOR;
/
SHOW ERROR
--PostgreSQL
CREATE OR REPLACE FUNCTION MYCURSOR (
VARINA VARCHAR
)
RETURNS VARCHAR
AS
$$
DECLARE
_record RECORD;
alert_mesg VARCHAR(2000);
IN_VAR1 VARCHAR(10);
IN_VAR2 VARCHAR(10);
BEGIN
alert_mesg := '';
--Define output columns
FOR _record IN SELECT A1, A2, count(*) FROM A group by A1,A2
LOOP
IN_VAR1 := _record.A1;
IN_VAR2 := _record.A2;
--Build output string
alert_mesg := alert_mesg||rpad(IN_VAR1,20)||rpad(IN_VAR2,20);
END LOOP;
RETURN alert_mesg;
--EXCEPTION
-- WHEN OTHERS THEN
-- RETURN 'No troubleshooting information at this time.';
END;
$$ LANGUAGE plpgsql;
--PostgreSQL
CREATE OR REPLACE FUNCTION MYCURSOR (VARINA VARCHAR) RETURNS VARCHAR AS $$
declare
--cur1 cursor is select A1, A2 from A;
cur1 refcursor;
cid integer;
_A1 varchar (10) ;
_A2 varchar (10) ;
alert_mesg VARCHAR(2000) := '';
BEGIN
--open cur1;
OPEN cur1 FOR execute('select * from A');
loop
fetch cur1 into _A1, _A2;
if not found then
exit ;
end if;
alert_mesg := alert_mesg||rpad(_A1,20)||rpad(_A2,20);
end loop;
close cur1;
return alert_mesg;
END;
$$ LANGUAGE plpgsql
------
Dinesh Pandey
Sr. Software Engineer
[SQL] createlang 'pltclu' with postgreSQL.
Title: createlang 'pltclu' with postgreSQL. Hi, I have already installed Postgres 8.0.1. I am able to use pl/pgsql language but...I want to use language 'pltclu' with postgreSQL. Its unable to find "pltcl". I am getting this error. $ createlang pltclu testdb ERROR: could not access file "$libdir/pltcl": No such file or directory createlang: language installation failed: ERROR: could not access file "$libdir/pltcl": No such file or directory Where this file "$libdir/pltcl" can be found I have installed "tk-8.4.9-sol9-sparc-local.gz" and "tcl-8.4.9-sol9-sparc-local.gz" file using pkgadd -d command. Any othe configuration required Regards Dinesh Pandey
Re: [SQL] createlang 'pltclu' with postgreSQL.
Hi Mike, Have you installed tcl and tk before popstgres installation and set some PATH for that before installing postgres.Because I installed "tk-8.4.9-sol9-sparc-local.gz" and "tcl-8.4.9-sol9-sparc-local.gz" after postgres installation.Can I use "configure --with-tcl --with-tclconfig=/usr/local/lib" this command now?? What is "/usr/local/lib" - Path of TCL or Path of Postgres installation??ThanksDinesh Pandey -Original Message-From: Michael Fuhr [mailto:[EMAIL PROTECTED]]Sent: Wednesday, April 06, 2005 9:14 PMTo: Dinesh PandeyCc: 'PostgreSQL'Subject: Re: [SQL] createlang 'pltclu' with postgreSQL.On Wed, Apr 06, 2005 at 07:39:02PM +0530, Dinesh Pandey wrote:>> I have already installed Postgres 8.0.1.How did you install PostgreSQL? Did you build it from source?> $ createlang pltclu testdb> ERROR: could not access file "$libdir/pltcl": No such file or> directory> createlang: language installation failed: ERROR: could not access> file> "$libdir/pltcl": No such file or directoryIf you built from source, did you run configure with --with-tcl?> I have installed "tk-8.4.9-sol9-sparc-local.gz" and> "tcl-8.4.9-sol9-sparc-local.gz" file using pkgadd -d command.> Any othe configuration requiredWhen I build PostgreSQL on Solaris 9, I run configure with the following options (amongst others not shown):configure --with-tcl --with-tclconfig=/usr/local/libTry rebuilding and reinstalling PostgreSQL with those options and see if you can then createlang pltclu.--Michael Fuhrhttp://www.fuhr.org/~mfuhr/
Re: [SQL] createlang 'pltclu' with postgreSQL.
1. I installed with compiled package using "pkgadd -d postgresql-8.0.1-sol9-sparc-local" 2. I haven't used --with-tcl? Thanks Dinesh Pandey -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Michael Fuhr Sent: Wednesday, April 06, 2005 9:14 PM To: Dinesh Pandey Cc: 'PostgreSQL' Subject: Re: [SQL] createlang 'pltclu' with postgreSQL. On Wed, Apr 06, 2005 at 07:39:02PM +0530, Dinesh Pandey wrote: > > I have already installed Postgres 8.0.1. How did you install PostgreSQL? Did you build it from source? > $ createlang pltclu testdb > ERROR: could not access file "$libdir/pltcl": No such file or > directory > createlang: language installation failed: ERROR: could not access > file > "$libdir/pltcl": No such file or directory If you built from source, did you run configure with --with-tcl? > I have installed "tk-8.4.9-sol9-sparc-local.gz" and > "tcl-8.4.9-sol9-sparc-local.gz" file using pkgadd -d command. > Any othe configuration required When I build PostgreSQL on Solaris 9, I run configure with the following options (amongst others not shown): configure --with-tcl --with-tclconfig=/usr/local/lib Try rebuilding and reinstalling PostgreSQL with those options and see if you can then createlang pltclu. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
FW: [SQL] createlang 'pltclu' with postgreSQL.
Unable to run "createlang pltcl testdb" [1]. I tried with this command ./configure --with-tcl --with-tclconfig=/usr/local/lib . checking whether the C compiler still works... yes checking how to run the C preprocessor... gcc -E checking whether to build with Tcl... yes . . checking for tclsh... /usr/local/bin/tclsh checking for tclConfig.sh... /usr/local/lib/tclConfig.sh checking tcl.h usability... yes checking tcl.h presence... yes checking for tcl.h... yes [2]. And then createlang pltcl testdb ERROR: could not access file "$libdir/pltcl": No such file or directory createlang: language installation failed: ERROR: could not access file "$libdir/pltcl": No such file or directory Thanks Dinesh Pandey -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dinesh Pandey Sent: Wednesday, April 06, 2005 9:30 PM To: 'Michael Fuhr' Cc: 'PostgreSQL' Subject: Re: [SQL] createlang 'pltclu' with postgreSQL. 1. I installed with compiled package using "pkgadd -d postgresql-8.0.1-sol9-sparc-local" 2. I haven't used --with-tcl? Thanks Dinesh Pandey -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Michael Fuhr Sent: Wednesday, April 06, 2005 9:14 PM To: Dinesh Pandey Cc: 'PostgreSQL' Subject: Re: [SQL] createlang 'pltclu' with postgreSQL. On Wed, Apr 06, 2005 at 07:39:02PM +0530, Dinesh Pandey wrote: > > I have already installed Postgres 8.0.1. How did you install PostgreSQL? Did you build it from source? > $ createlang pltclu testdb > ERROR: could not access file "$libdir/pltcl": No such file or > directory > createlang: language installation failed: ERROR: could not access > file > "$libdir/pltcl": No such file or directory If you built from source, did you run configure with --with-tcl? > I have installed "tk-8.4.9-sol9-sparc-local.gz" and > "tcl-8.4.9-sol9-sparc-local.gz" file using pkgadd -d command. > Any othe configuration required When I build PostgreSQL on Solaris 9, I run configure with the following options (amongst others not shown): configure --with-tcl --with-tclconfig=/usr/local/lib Try rebuilding and reinstalling PostgreSQL with those options and see if you can then createlang pltclu. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(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
[SQL] ar: Command not found.
Title: ar: Command not found. Hi I am getting this error while "make install" of postgres. make[2]: ar: Command not found. make[2]: *** [libpgport.a] Error 127 make[2]: Leaving directory `/export/home/psingh/postgre/postgresql-8.0.1/src/port' make[1]: *** [install] Error 2 make[1]: Leaving directory `/export/home/psingh/postgre/postgresql-8.0.1/src' make: *** [install] Error 2 Which package need to be installed? 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: [SQL] ar: Command not found.
Title: ar: Command not found. In Solaris "ar" command is in "/usr/ccs/bin" directory. Thanks Dinesh Pandey From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dinesh PandeySent: Thursday, April 07, 2005 10:21 AMTo: 'PostgreSQL'Subject: [SQL] ar: Command not found. Hi I am getting this error while "make install" of postgres. make[2]: ar: Command not found. make[2]: *** [libpgport.a] Error 127 make[2]: Leaving directory `/export/home/psingh/postgre/postgresql-8.0.1/src/port' make[1]: *** [install] Error 2 make[1]: Leaving directory `/export/home/psingh/postgre/postgresql-8.0.1/src' make: *** [install] Error 2 Which package need to be installed? RegardsDinesh Pandey------Dinesh Pandey Sr. Software EngineerSecond Foundation (India) Pvt. Ltd.Plot# 52Industrial Area, Phase IIChandigarh. (India)PH: (O) 0172-2639202, Extn: 233
[SQL] Table PARTITION
Title: Table PARTITION
How can we create oracle's table with partition in Postgres. (How to create Table partion in postgres)
CREATE TABLE invoices
(invoice_no NUMBER NOT NULL,
invoice_date DATE NOT NULL,
comments VARCHAR2(500))
PARTITION BY RANGE (invoice_date)
(PARTITION invoices_q1 VALUES LESS THAN (TO_DATE('01/04/2001', 'DD/MM/')) TABLESPACE users
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: [SQL] Table PARTITION
Hi Richard,
I am using postgres 8.0.1. It allows us to create TABLESPACE but I don't
know how to create PARTITION in postgres.
Can you pls tell me little more.
Thanks
Dinesh Pandey
-Original Message-
From: Richard Huxton [mailto:[EMAIL PROTECTED]
Sent: Thursday, April 07, 2005 5:36 PM
To: [EMAIL PROTECTED]
Cc: 'PostgreSQL'
Subject: Re: [SQL] Table PARTITION
Dinesh Pandey wrote:
> How can we create oracle's table with partition in Postgres. (How to
> create Table partion in postgres)
>
> CREATE TABLE invoices
> (invoice_noNUMBER NOT NULL,
> invoice_date DATE NOT NULL,
> comments VARCHAR2(500))
>
> PARTITION BY RANGE (invoice_date)
> (PARTITION invoices_q1 VALUES LESS THAN (TO_DATE('01/04/2001',
> 'DD/MM/')) TABLESPACE users
By hand, I'm afraid (although I think people are looking into this feature
for version 8.1)
There's lots of discussion in the mail archives about how people are
handling this. You're basically looking at some combination of
- partial/conditional indexes
- unions
- inheritance
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Table PARTITION
Yes, I will do in this way Thanks Dinesh Pandey -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Enrico Weigelt Sent: Friday, April 08, 2005 7:18 AM To: [email protected] Subject: Re: [SQL] Table PARTITION * Richard Huxton wrote: > Sean Davis wrote: > >This is a totally selfish question, but IF someone has a few minutes, > >could he/she explain why table partitioning is such an important tool? > > Say you have a large log-table, you could partition it by month. If > most queries only search the last month or two, a lot of your > partitioned data could be moved to cheaper/slower disks (via tablespaces). You can solve this problem with multiple tables rules quite easily. At this point you can also filter out some unused data (often historical data requires less information than live data, because only the end result of certain finished things is interesting for the future, but many things needed as long as things are open are completely irrelevant for later usage, i.e. an archive of accounting information for webhosters wont require datails of single http requests) Lets give me some examples on one of my customer's projects: At fXignal - an forex market trading platform - we're maintaining an long-time archive of all run orders. An "open" trade (you've bought some position) has one order, while an "closed" trade (things are sold again) has two. I.g we've got two kind of accesses to trade information: a) viewing and manipulating open trades - active trading (must be fast!) b) only viewing closed trades for reports (account report, etc) Also we've got some information which are only interesting for open trades, ie. limits (points where trade should be closed automatically). We've solved this by having two tables: one for open trades and one for archived (closed) trades. When an trade is opened, it goes to the open-trade table and resides there until it goes to closed state (by setting a "closed" flag). Once the trades has reached closed state its copied to the archive table and removed from the open trade table by an rule. (see CREATE RULE). When archived trades get old (3 month) we need less information from that, which has to be kept very long (several years). For that we catch the DELETE on the archive table and copy data to the longtime archive before it gets removed from the archive table. For long time analyses we've got some views which map together interesting information from all tables. Well, this way we've got the same benefits as with partitions, with a little bit more coding work, but then with better control and filtering out unneeded stuff. cu -- - Enrico Weigelt== metux IT service phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: [EMAIL PROTECTED] cellphone: +49 174 7066481 - -- DSL ab 0 Euro. -- statische IP -- UUCP -- Hosting -- Webshops -- - ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] How to Port Oracle's user defined "Package" into Postgres 8.0.1.
Title: How to Port Oracle's user defined "Package" into Postgres 8.0.1. Hi folks, Can any one give me an idea about: How to Port Oracle's user defined "Package" into Postgres 8.0.1. If possible pls attache sample code. Thanks Dinesh Pandey
Re: [SQL] ignore single character in SELECT query?
Use
select replace(quote_literal('don\'t'), '\'', '');
Or
select replace(quote_ident(myColumnName, '\'', '');
Thanks
Dinesh Pandey
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of [EMAIL PROTECTED]
Sent: Wednesday, April 13, 2005 8:16 AM
To: [email protected]
Subject: [SQL] ignore single character in SELECT query?
Given select criteria "dont" I would like to return rows with values
"don't". Or similarily I want rows with "they're" given input criteria
"theyre".
So basically I want to ignore a single character (the apostrophe
character), anywhere in the middle of my search word, in selecting
results. How can I do this?
---(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 7: don't forget to increase your free space map settings
Re: [SQL] max question
Try "select oid,* from ccontinue where citkey ='04-0594703' group by oid,citkey,contby,contdate,abcontinue,ccdate having contdate= max(contdate)" Thanks Dinesh Pandey -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of A. R. Van Hook Sent: Tuesday, April 12, 2005 10:54 PM To: [email protected] Subject: [SQL] max question I have the following in a table: oid | citkey | contby | contdate | abcontinue | ccdate -++--+++ 5774835 | 04-0594703 | ||| 5775325 | 04-0594703 | Attorney | 04/06/2005 | 6 | 03/07/2005 5776060 | 04-0594703 | Attorney | 05/04/2005 | 6 | 04/05/2005 5776067 | 04-0594703 | Attorney | 05/04/2005 | 6 | 04/05/2005 I am trying to pull rows that have the max. contdate. Why does the following give more than 2 rows? ql "select oid,* from ccontinue where citkey ='04-0594703' group by oid,citkey,contby,contdate,abcontinue,ccdate having max(contdate) = contdate" oid | citkey | contby | contdate | abcontinue | ccdate -++--+++ 5776067 | 04-0594703 | Attorney | 05/04/2005 | 6 | 04/05/2005 5775325 | 04-0594703 | Attorney | 04/06/2005 | 6 | 03/07/2005 5776060 | 04-0594703 | Attorney | 05/04/2005 | 6 | 04/05/2005 (3 rows) thanks ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] dynamic 'INSERT' query?
How can we set A1, A2 values in dynamic 'INSERT’ query? DECLARE _record RECORD; _sql VARCHAR(2000); FOR _record IN SELECT A1, A2 FROM A LOOP _sql := 'INSERT INTO B VALUES (:A1, :A2)’; EXECUTE (_sql); END LOOP; = I can do this as (but I need another way instead of using || operator). _sql := 'INSERT INTO B VALUES (‘ || _record.A1 || ’,’ || _record.A2 || ’)’; ===== Regards Dinesh Pandey ------ Dinesh Pandey Sr. Software Engineer
Re: [SQL] dynamic 'INSERT' query?
FOR _record IN SELECT A1, A2 FROM A
LOOP
--[Dinesh Pandey] -- Sorry I didn't get this part.
_sql := "INSERT INTO B VALUES ( '*" || _record.A1 || "'** , '" || _record.A2
|| "' ***)";
EXECUTE (_sql);
END LOOP;
Dinesh Pandey wrote:
> How can we set A1, A2 values in dynamic 'INSERT' query?
>
> DECLARE
>
> _record RECORD;
>
> _sql VARCHAR(2000);
>
> FOR _record IN SELECT A1, A2 FROM A
>
> LOOP
>
> _sql := 'INSERT INTO B VALUES (*:A1, :A2*)';
>
> EXECUTE (_sql);
>
> END LOOP;
>
> =
>
> I can do this as (but I need another way instead of using || operator).
>
> _sql := 'INSERT INTO B VALUES (' || _record.A1 || ',' || _record.A2 ||
> ')';
>
> =========
>
> Regards
> Dinesh Pandey
>
>
>
*---
---*
> *Dinesh Pandey*
> Sr. Software Engineer
>
---(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
[SQL] Prepared query ?
How can I Write a prepared query and Set values at run time and Execute that query for different values. LOOP ….. ….. sql := INSERT INTO MYTABLE VALUES(?, ?); …… --EXECUTE (sql); END LOOP; 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: [SQL] dynamic 'INSERT' query?
Yes here the TABLE NAME is also coming at runtime. Like In Oracle I have done in this way: --- LOOP v_sql := 'INSERT INTO ' || mytable || ' VALUES(:in_sentryid_id, :in_node_id)'; EXECUTE IMMEDIATE v_sql USING v_sentryid, v_nodeid; END LOOP; How to convert this into Postgres? Thanks Dinesh Pandey ---(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
Re: [SQL] Prepared query ?
I am using PL/pgSQL. Thanks Dinesh Pandey From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Frank Bax Sent: Thursday, April 14, 2005 8:24 PM To: [email protected] Subject: Re: [SQL] Prepared query ? At 10:28 AM 4/14/05, Dinesh Pandey wrote: How can I Write a prepared query and Set values at run time and Execute that query for different values. Depends on what language you are using - try interfaces mailing list.
Re: [SQL] Prepared query ?
Yes you got my problem correctly. I want to change tablename and values at run time. Is there any other way instead using "||" (append) ? like prepared statement. PREPARE fooplan (int, text, bool, numeric) AS INSERT INTO foo VALUES($1, $2, $3, $4); EXECUTE fooplan(1, 'Hunter Valley', 't', 200.00); Thanks Dinesh Pandey -Original Message- From: Richard Huxton [mailto:[EMAIL PROTECTED] Sent: Thursday, April 14, 2005 8:29 PM To: [EMAIL PROTECTED] Cc: 'PostgreSQL' Subject: Re: [SQL] Prepared query ? Dinesh Pandey wrote: > How can I > >1. Write a prepared query and >2. Set values at run time and >3. Execute that query for different values. > LOOP > ... > ... > sql := INSERT INTO MYTABLE VALUES(?, ?); > --EXECUTE (sql); You don't say what language you want to use, but assuming it's plpgsql, then for the simple case you can just do: LOOP INSERT INTO mytable VALUES (var1, var2); END LOOP Now, let's say you wanted to change mytable as well as the values, you'd use sql := ''INSERT INTO '' || quote_ident(my_table_variable) || '' VALUES ...etc''; EXECUTE sql; Does that help? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] EXCEPTION handling in PL/pgSQL.
Hi, I am new in Postgres. Can any one tell me best way of EXCEPTION handling in PL/pgSQL. 1. I mean How to get Raised Exception messages in "EXCEPTION" block (I mean exception messages thrown by us OR by Postgres SQL Exception). 2. Is EXCEPTION datatype is available in Postgres as it is available in ORACLE. RegardsDinesh Pandey------Dinesh Pandey Sr. Software Engineer
[SQL] send mail from Postgres using PLTCLU language.
How to send mail
from Postgres using PLTCLU language.
I am able to send
mail, but I want to know how to add "Cc" option also. (Program attached
below)
How to add
"Cc" (Right now containd "From", "To", "Subject" and
"Message").
CREATE or REPLACE FUNCTION pgmail(text,
text, text, text) returns int4 as ' set mailfrom $1 set mailto
$2 set mailsubject $3 set mailmessage
$4 set myHost "localhost" set myPort
25 set mySock [socket $myHost $myPort] set
toemailaddress_start [string first "<" $mailto] if
{$toemailaddress_start != -1} { set toemailaddress_finish [string
first ">" $mailto] set toemailaddress_start [expr
$toemailaddress_start + 1] set toemailaddress_finish [expr
$toemailaddress_finish - 1] set toemailaddress [string range
$mailto $toemailaddress_start $toemailaddress_finish] } else
{ set toemailaddress $mailto } set
fromemailaddress_start [string first "<" $mailfrom] if
{$fromemailaddress_start != -1} { set fromemailaddress_finish
[string first ">" $mailfrom] set fromemailaddress_start [expr
$fromemailaddress_start + 1] set fromemailaddress_finish [expr
$fromemailaddress_finish - 1] set fromemailaddress [string range
$mailfrom $fromemailaddress_start $fromemailaddress_finish] } else
{ set fromemailaddress
$mailfrom } fileevent $mySock writable [list
svcHandler $mySock] fconfigure $mySock -buffering
none puts $mySock "helo 127.0.0.1" gets
$mySock name puts $mySock "mail from: $fromemailaddress" gets
$mySock name puts $mySock "rcpt to: $toemailaddress" gets
$mySock name puts $mySock "data" gets $mySock
name puts $mySock "To: $mailto" puts $mySock "From:
$mailfrom" puts $mySock "Subject: $mailsubject" puts $mySock
"" puts $mySock "$mailmessage" puts $mySock "." gets
$mySock name close $mySock return 1'language
'pltclu';
RegardsDinesh Pandey
Re: [SQL] send mail from Postgres using PLTCLU language.
After using
puts $mySock "rcpt cc: [EMAIL PROTECTED]"
I am able to send mail as Cc:
also.
Thanks
Dinesh Pandey
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Dinesh
PandeySent: Friday, April 15, 2005 6:18 PMTo:
'PostgreSQL'Subject: [SQL] send mail from Postgres using PLTCLU
language.
How to send mail
from Postgres using PLTCLU language.
I am able to send
mail, but I want to know how to add "Cc" option also. (Program attached
below)
How to add
"Cc" (Right now containd "From", "To", "Subject" and
"Message").
CREATE or REPLACE FUNCTION pgmail(text,
text, text, text) returns int4 as ' set mailfrom $1 set mailto
$2 set mailsubject $3 set mailmessage
$4 set myHost "localhost" set myPort
25 set mySock [socket $myHost $myPort] set
toemailaddress_start [string first "<" $mailto] if
{$toemailaddress_start != -1} { set toemailaddress_finish [string
first ">" $mailto] set toemailaddress_start [expr
$toemailaddress_start + 1] set toemailaddress_finish [expr
$toemailaddress_finish - 1] set toemailaddress [string range
$mailto $toemailaddress_start $toemailaddress_finish] } else
{ set toemailaddress $mailto } set
fromemailaddress_start [string first "<" $mailfrom] if
{$fromemailaddress_start != -1} { set fromemailaddress_finish
[string first ">" $mailfrom] set fromemailaddress_start [expr
$fromemailaddress_start + 1] set fromemailaddress_finish [expr
$fromemailaddress_finish - 1] set fromemailaddress [string range
$mailfrom $fromemailaddress_start $fromemailaddress_finish] } else
{ set fromemailaddress
$mailfrom } fileevent $mySock writable [list
svcHandler $mySock] fconfigure $mySock -buffering
none puts $mySock "helo 127.0.0.1" gets
$mySock name puts $mySock "mail from: $fromemailaddress" gets
$mySock name puts $mySock "rcpt to: $toemailaddress" gets
$mySock name puts $mySock "data" gets $mySock
name puts $mySock "To: $mailto" puts $mySock "From:
$mailfrom" puts $mySock "Subject: $mailsubject" puts $mySock
"" puts $mySock "$mailmessage" puts $mySock "." gets
$mySock name close $mySock return 1'language
'pltclu';
RegardsDinesh Pandey
[SQL] Error handling in pltcl
Hi, Can any one tell me how to handle error in PL/TCL language. On Error/Exception, In the exception block I want to call a FUNCTION (written in PL/pgSQL). ThanksDinesh Pandey
Re: [SQL] Error handling in pltcl
I have attached below an e-mail program.
I want to log messages into a table from a function written in 'plpgsql'
whenever any exception occurs (like unable to connect/invalid email etc..)
Pls update the code attached below
Thanks
Dinesh Pandey
CREATE OR REPLACE FUNCTION PGMAIL(text, text, text, text, text, text)
RETURNS INT4 AS '
set mailfrom$1
set mailto $2
set mailcc $3
set mailserver $4
set mailsubject $5
set mailmessage $6
# Mail Server
set myHost $mailserver
# Mail Server SMTP Port
set myPort 25
set mySock [socket $myHost $myPort]
# Mail To Address
set toemailaddress_start [string first "<" $mailto]
if {$toemailaddress_start != -1} {
set toemailaddress_finish [string first ">" $mailto]
set toemailaddress_start [expr $toemailaddress_start + 1]
set toemailaddress_finish [expr $toemailaddress_finish - 1]
set toemailaddress [string range $mailto
$toemailaddress_start $toemailaddress_finish]
} else {
set toemailaddress $mailto
}
# Mail Cc Address
set ccemailaddress_start [string first "<" $mailcc]
if {$ccemailaddress_start != -1} {
set ccemailaddress_finish [string first ">" $mailcc]
set ccemailaddress_start [expr $ccemailaddress_start + 1]
set ccemailaddress_finish [expr $ccemailaddress_finish - 1]
set ccemailaddress [string range $mailcc
$toemailaddress_start $ccemailaddress_finish]
} else {
set ccemailaddress $mailcc
}
# Mail From Address
set fromemailaddress_start [string first "<" $mailfrom]
if {$fromemailaddress_start != -1} {
set fromemailaddress_finish [string first ">" $mailfrom]
set fromemailaddress_start [expr $fromemailaddress_start +
1]
set fromemailaddress_finish [expr $fromemailaddress_finish -
1]
set fromemailaddress [string range $mailfrom
$fromemailaddress_start $fromemailaddress_finish]
} else {
set fromemailaddress $mailfrom
}
fileevent $mySock writable [list svcHandler $mySock]
fconfigure $mySock -buffering none
puts $mySock "helo $mailserver"
gets $mySock name
puts $mySock "mail from: $fromemailaddress"
gets $mySock name
puts $mySock "rcpt to: $toemailaddress"
gets $mySock name
puts $mySock "rcpt cc: $ccemailaddress"
gets $mySock name
puts $mySock "data"
gets $mySock name
puts $mySock "To: $mailto"
puts $mySock "Cc: $mailcc"
puts $mySock "From: $mailfrom"
puts $mySock "Subject: $mailsubject"
puts $mySock ""
puts $mySock "$mailmessage"
puts $mySock "."
gets $mySock name
close $mySock
return 1'
#Exception handling
LANGUAGE 'pltclu';
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[SQL] "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
Re: [SQL] user connection over tcp
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.1.11.54 255.255.255.0 trust Thanks Dinesh Pandey -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Sean Davis Sent: Monday, April 18, 2005 7:13 PM To: Frank Habermann; [email protected] Subject: Re: [SQL] user connection over tcp Did you start the postmaster with -i to allow tcp connections? What is the error that you get when you try to connect? Sean - Original Message - From: "Frank Habermann" <[EMAIL PROTECTED]> To: Sent: Monday, April 18, 2005 9:31 AM Subject: [SQL] user connection over tcp > hello! > > i have some problems to understand the manual with pg_hba.conf. > > i use a tcp connection with phppgadmin to connect to the db. in my > pg_hba.conf i say for this connection trust! in phppgadmin i need the for > my user the right password to connect. but the manual says that every > password will accepted. why isnt that work at me? > > i also try password in pg_hba.conf for my tcpconnections. but after this i > cant connect to the db. but the manual says that users with right password > can connect. but this doesnt work here. > > can somebody explain me whats wrong with me or my postgre?! > > thx > > frank habermann > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > ---(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 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] 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 --
[SQL] 'Select INTO" in Execute (dynamic query )
Hi What’s 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
[SQL] 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
[SQL] 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 <> 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'; '[email protected]' 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
[SQL] Postgres 8.0.1 on Solaris 10 Sparc: library -lgcc_s: not found
I am installing Postgres 8.0.1 on Solaris 10 Sparc: I am getting this error gcc -shared -h libascii_and_mic.so.0 ascii_and_mic.o -L../../../../../../src/port -o libascii_and_mic.so.0.0 ld: fatal: library -lgcc_s: not found ld: fatal: library -lgcc_s: not found ld: fatal: File processing errors. No output written to libascii_and_mic.so.0.0 collect2: ld returned 1 exit status make[3]: *** [libascii_and_mic.so.0.0] Error 1 make[3]: Leaving directory `/export/home/softwares/postgresql-8.0.1/src/backend/utils/mb/conversion_procs/ascii_and_mic' make[2]: *** [all] Error 2 make[2]: Leaving directory `/export/home/softwares/postgresql-8.0.1/src/backend/utils/mb/conversion_procs' make[1]: *** [all] Error 2 make[1]: Leaving directory `/export/home/softwares/postgresql-8.0.1/src' make: *** [all] Error 2 Regards Dinesh Pandey --
[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: [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 it’s 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]; [email protected]; PostgreSQL Subject: RE: [SQL] index row size 2728 exceeds btree maximum, 2713 Hi, The issue looks like your Index width exceeds 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: [email protected]; '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: [SQL] [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: [email protected]; '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: [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]; [email protected]; 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; [email protected]; '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 it’s 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]; [email protected]; PostgreSQL Subject: RE: [SQL] index row size 2728 exceeds btree maximum, 2713 Hi, The issue looks like your Index width exceeds 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: [email protected]; '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?
[SQL] 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: [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: [email protected]; '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 ‘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? 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: [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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [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'; [email protected]; '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
[SQL] Create connection with Oracle database from Postgres plpgsql function
How can we create connection with Oracle database from Postgres plpgsql function and execute some oracle stored procedure? Thanks Dinesh
[SQL] How to store and retrieve data with special characters (\n, \r, \f, \t) in the same format
How to store and retrieve data with special characters (\n, \r, \f, \t) in the same format, because while storing \f treats data in strange way. Example: ‘This is sample data \n \root\files.txt, \root\text.txt’ Sol: One way replaces \ with \\ Thanks Dinesh
[SQL] 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
[SQL] How to connect ORACLE database from Postgres function using plpgsql/pltclu?
Is there any way to connect ORACLE database from Postgres function using plpgsql/pltclu? Thanks Dinesh Pandey
Re: [SQL] How to connect ORACLE database from Postgres functionusing plpgsql/pltclu?
Title: Re: [SQL] How to connect ORACLE database from Postgres function using plpgsql/pltclu? Thanks Sean, It great help from you. Thanks Dinesh Pandey From: Sean Davis [mailto:[EMAIL PROTECTED] Sent: Monday, August 01, 2005 4:47 PM To: [EMAIL PROTECTED] Cc: PostgreSQL Subject: Re: [SQL] How to connect ORACLE database from Postgres functionusing plpgsql/pltclu? On 8/1/05 6:35 AM, "Dawid Kuroczko" <[EMAIL PROTECTED]> wrote: On 8/1/05, Dinesh Pandey <[EMAIL PROTECTED]> wrote: > Is there any way to connect ORACLE database from Postgres function using > plpgsql/pltclu? With PLpgSQL I don't think its possible. I don't know how about PLtclU (should be possible), but I'm sure its doable from PLperlU (using DBI). Don't expect it to be easy to set up and fast performing though. ;) Have a look at DBI-Link. http://pgfoundry.org/projects/dbi-link There is a PostgreSQL tidbits column on DBI-Link here: http://www.pervasive-postgres.com/postgresql/tidbits.asp Sean
[SQL] SQLException - SET AUTOCOMMIT TO OFF is no longer supported
I have created datasource but getting this error on JBOSS startup. Using: PostgreSQL 8.0 JBOSS: JBOSS-3.2.6 16:09:37,093 WARN [TransactionImpl] XAException: tx=TransactionImpl:XidImpl [FormatId=257, GlobalId=dinesh//1, BranchQual=] errorCode=XA_UNKNOWN(0) org.jboss.resource.connectionmanager.JBossLocalXAException: Error trying to start local tx: ; - nested throwable: (org.jboss.resource.JBossResourceException: SQLException; - nested throwable: (java.sql.SQLException: ERROR: SET AUTOCOMMIT TO OFF is no longer supported )) at org.jboss.resource.connectionmanager.TxConnectionManager$LocalXAResource.start(TxConnectionManager.java:654) at org.jboss.tm.TransactionImpl.startResource(TransactionImpl.java:1196) at org.jboss.tm.TransactionImpl.enlistResource(TransactionImpl.java:649) Thanks Dinesh Pandey
Re: [SQL] SQLException - SET AUTOCOMMIT TO OFF is no longer supported
After using correct version of JDBC driver I am able to start JBOSS server. Thanks Dinesh Pandey I have created datasource but getting this error on JBOSS startup. Using: PostgreSQL 8.0 JBOSS: JBOSS-3.2.6 16:09:37,093 WARN [TransactionImpl] XAException: tx=TransactionImpl:XidImpl [FormatId=257, GlobalId=dinesh//1, BranchQual=] errorCode=XA_UNKNOWN(0) org.jboss.resource.connectionmanager.JBossLocalXAException: Error trying to start local tx: ; - nested throwable: (org.jboss.resource.JBossResourceException: SQLException; - nested throwable: (java.sql.SQLException: ERROR: SET AUTOCOMMIT TO OFF is no longer supported )) at org.jboss.resource.connectionmanager.TxConnectionManager$LocalXAResource.start(TxConnectionManager.java:654) at org.jboss.tm.TransactionImpl.startResource(TransactionImpl.java:1196) at org.jboss.tm.TransactionImpl.enlistResource(TransactionImpl.java:649) Thanks Dinesh Pandey
Re: [SQL] Table Constraint CHECK(SELECT()) alternative?
You can create a function and call it from a trigger on that column insert event Thanks Dinesh Pandey -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Robert D. Kennedy Sent: Sunday, September 04, 2005 12:20 AM To: [email protected] Cc: [EMAIL PROTECTED] Subject: [SQL] Table Constraint CHECK(SELECT()) alternative? Hello, I have seen in another thread that sub-queries in a CHECK constraint have implementation ramifications that make them awkward to implement and support. OK, fair enough, c'est la vie. ERROR: cannot use subquery in check constraint is the result. I have a model which seems to BEG for just such a feature though. What alternatives are there to the use of CHECK(SELECT()) in this model? I am implementing a basic tree of nodes. There are leafs and directories... node types. Each node in a directory has a unique name, and the node type ids point to a table with node type names. Each node points to a valid node (its parent). There is more... but now to my question. I wish to constrain parent nodes to only be directories type nodes. Leafs cannot be parents. Table "public.raw_pnfs_nodes" Column | Type | Modifiers +---+--- -- node_id| integer | not null default nextval('public.raw_pnfs_nodes_node_id_seq'::text) parent_node_id | integer | not null node_name | character varying(80) | not null node_type_id | smallint | not null Indexes: "raw_pnfs_nodes_pkey" primary key, btree (node_id) "raw_pnfs_nodes_node_name_key" unique, btree (node_name, parent_node_id) Foreign-key constraints: "parent_refersto_node" FOREIGN KEY (parent_node_id) REFERENCES raw_pnfs_nodes(node_id) ON UPDATE RESTRICT ON DELETE RESTRICT "raw_pnfs_nodes" FOREIGN KEY (node_type_id) REFERENCES node_types(node_type_id) ON UPDATE RESTRICT ON DELETE RESTRICT What I WANT to write is the table constraint: CONSTRAINT parents_are_dirs_check CHECK( (SELECT A.node_type_id FROM raw_pnfs_nodes A, raw_pnfs_nodes B WHERE A.node_id = B.parent_node_id) = 1) where "1" is the id for directory node types (ok, this is not polished yet) Hopefully this illustrates the idea. The node type of a node that is a parent to another node must be the directory node type. Is there another way to express this in SQL? I would like to avoid putting this into the business logic layer. Thanks much, RDK ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] 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.
[SQL] unsubscribe
unsubscribe
