[SQL] shared_buffers and shmall,shmmax
] The PostgreSQL documentation contains more information about shared memory configuration. Jan 25 09:56:17 iguard postgres[49970]: [1-1] LOG: database system was shut down at 2007-01-25 09:52:09 PST Jan 25 09:56:17 iguard postgres[49970]: [2-1] LOG: checkpoint record is at 14B/649807D0 Jan 25 09:56:17 iguard postgres[49970]: [3-1] LOG: redo record is at 14B/649807D0; undo record is at 0/0; shutdown TRUE Jan 25 09:56:17 iguard postgres[49970]: [4-1] LOG: next transaction ID: 4566517; next OID: 361483975 Jan 25 09:56:17 iguard postgres[49970]: [5-1] LOG: next MultiXactId: 1; next MultiXactOffset: 0 Jan 25 09:56:17 iguard postgres[49970]: [6-1] LOG: database system is ready Jan 25 09:56:17 iguard postgres[49970]: [7-1] LOG: transaction ID wrap limit is 1077475224, limited by database urldbdev Jan 25 09:57:00 iguard postgres[49988]: [1-1] LOG: statement: SELECT count(*) FROM public.unchecked WHERE allocatedto IS NULL OR allocatedto = 'EX' Jan 25 09:57:12 iguard postgres[49969]: [1-1] LOG: received fast shutdown request Jan 25 09:57:12 iguard postgres[49971]: [1-1] LOG: shutting down Jan 25 09:57:12 iguard postgres[49971]: [2-1] LOG: database system is shut down Jan 25 09:57:13 iguard postgres[49997]: [1-1] FATAL: could not create shared memory segment: Cannot allocate memory Jan 25 09:57:13 iguard postgres[49997]: [1-2] DETAIL: Failed system call was shmget(key=5432001, size=310394880, 03600). Jan 25 09:57:13 iguard postgres[49997]: [1-3] HINT: This error usually means that PostgreSQL's request for a shared memory segment exceeded available memory or swap space. Jan 25 09:57:13 iguard postgres[49997]: [1-4] To reduce the request size (currently 310394880 bytes), reduce PostgreSQL's shared_buffers parameter (currently 3) and/or Jan 25 09:57:13 iguard postgres[49997]: [1-5] its max_connections parameter (currently 120). Jan 25 09:57:13 iguard postgres[49997]: [1-6] The PostgreSQL documentation contains more information about shared memory configuration. Jan 25 09:58:20 iguard postgres[50012]: [1-1] LOG: database system was shut down at 2007-01-25 09:57:12 PST Jan 25 09:58:20 iguard postgres[50012]: [2-1] LOG: checkpoint record is at 14B/64980820 Jan 25 09:58:20 iguard postgres[50012]: [3-1] LOG: redo record is at 14B/64980820; undo record is at 0/0; shutdown TRUE Jan 25 09:58:20 iguard postgres[50012]: [4-1] LOG: next transaction ID: 4566519; next OID: 361483975 Jan 25 09:58:20 iguard postgres[50012]: [5-1] LOG: next MultiXactId: 1; next MultiXactOffset: 0 Jan 25 09:58:20 iguard postgres[50012]: [6-1] LOG: database system is ready Question: How come that shared_buffers=4 (or 3), which request size 394256384 (or 310394880) bytes is much lower then shmmax(536870912 bytes), and max_connection is set to 125 still failed to start postmaster? Thanks, Best regards, Jie Liang ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] [JDBC] Prepare Statement
Does plperl catch the plan also? Thanks. Jie Liang -Original Message- From: Kris Jurka [mailto:[EMAIL PROTECTED] Sent: Friday, June 18, 2004 2:47 PM To: Jie Liang Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: [JDBC] Prepare Statement On Fri, 18 Jun 2004, Jie Liang wrote: However, I am still thinking if I call one SELECT and one DELECT and one UPDATE and one INSERT a thousand times against same table with different arguments, should I consider performance iusse? Right, this is a case where some benefits can be found, but remember the premature optimization adage. Secondly, I assume the function should be a pre-compiled object stored on server side, doesn't it. I depends on the language the function is written. plpgsql caches plans, but not all procedural languages do. Kris Jurka ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---(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
Re: [SQL] [JDBC] Prepare Statement
Nope, I think you are right. The improvement of performance will be minimal. Because that to parse SELECT * FROM myfunction(?,?,?) is very very quick. However, I am still thinking if I call one SELECT and one DELECT and one UPDATE and one INSERT a thousand times against same table with different arguments, should I consider performance iusse? Secondly, I assume the function should be a pre-compiled object stored on server side, doesn't it. Thanks. Jie Liang -Original Message- From: Kris Jurka [mailto:[EMAIL PROTECTED] Sent: Thursday, June 17, 2004 10:51 PM To: Jie Liang Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: [JDBC] Prepare Statement On Thu, 17 Jun 2004, Jie Liang wrote: Hmm, intersting. I am using jdk 1.3.1, and pg74.213.jdbc2.jar driver, I hope this bug could be fixed in later version. I suppose, but I'm going to put it pretty close to the bottom of my todo list because it still works even though it doesn't use a server prepared statement, and as I mentioned earlier the performance improvement if any will be minimal. Have you done any testing to show that you are even getting a performance gain? Kris Jurka ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] [JDBC] Prepare Statement
So, I think that PreparedStatement should have a way at least case a
String to an Array or a way to create a Array, because of
conn.prepareStatement(SELECT myfunction('{1,2,3}')) is NOT very
useful.
Comment?
Jie Liang
-Original Message-
From: Kris Jurka [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 17, 2004 10:47 PM
To: Jie Liang
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: [JDBC] Prepare Statement
On Thu, 17 Jun 2004, Jie Liang wrote:
Kris,
I have another question, I saw some discussion regarding
PreparedStatement work with array argument, I get a error when I try
to play with it. E.g.
I have myfunction(int[]),
So,
PrepareStatement st = conn.prepareStatment(SELECT myfunction(?));
String arr={1,2,3};
St.setString(1,arr};
Result rs = st.executeQuery();
Then it will complaint when it run:
Myfuntion(text) does not exist!
This is actually a case where prepared statements actually cause
trouble.
With the directly executed SELECT myfunction('{1,2,3}'); The backend can
determine that there is only one version of myfunction so it can convert
the unkown argument type to it, but note that this won't work if
myfunction is overloaded.
With the prepared case, you must tell it what types to use when doing
the prepare. The JDBC driver doesn't have a whole lot of information to
work with, so it takes what it knows (that you called setString) and
says the argument is of type text, issuing a prepare like this: PREPARE
JDBC_STATEMENT_1(text) AS SELECT myfunction($1); At this time (before
it actually calls EXECUTE) it tries to lookup myfunction that takes a
text argument and determines there isn't one. In this case it doesn't
have the opportunity to apply any casts because we were quite clear in
specifying that it should take a text argument, not one of unknown type.
Ideally you should be using setArray, but there is no existing way to
create Array objects and I'm not sure that code would work even if there
was.
Kris Jurka
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] [JDBC] Prepare Statement
Kirs, I re-compile with setUseServerPrepare(true), it works fine, thanks. However, reading from my log file, what I saw is that five same SELECTs with different argument, so I am wondering that the PrepareStatement really save time than individualy execute five SELECTs ??? If I use one parepare sql command and five execute sql commands, the log file shown what I typed, so I think it really used server side prepared object! Any comment? Thanks. Jie Liang -Original Message- From: Kris Jurka [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 16, 2004 9:30 PM To: Jie Liang Cc: Tom Lane; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: [JDBC] Prepare Statement On Wed, 16 Jun 2004, Jie Liang wrote: Kris, Thank you for your valuable response, I used the code you list following: [7.5 code example] Then, the compiler complaint: ServerSidePreparedStatement.java:20: cannot resolve symbol symbol : method setPrepareThreshold (int) location: interface org.postgresql.PGStatement pgstmt.setPrepareThreshold(3); I downloaded pg74.213.jdbc2.jar and pg74.213.jdbc2ee.jar at This example is from the 7.5 documentation and requires a pgdev.302.jdbcX.jar file. I mentioned this cvs example because this functionality is undocumented in the released version. In the 7.4 version the enabling of server side statements is only possible via a boolean flag at the statement level, namely PGStatement.setUseServerPrepare(true); Kris Jurka ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] [JDBC] Prepare Statement
Kris,
You are right, I modified that piece of code a little bit,
CallableStatement stmt = conn.prepareCall({?=call chr(?)});
Then my log file were:
Select * from chr(65) as result;
Select * from chr(66) as result;
..
However, if I use:
PrepareStatement stmt = conn.prepareStatement(SELECT chr(?));
Then my log file are same as yours.i.e. it use PREPARE and EXECUTE.
So, I am getting confusion.
I think CallableStatement is extended from PrepareStatement, it should
have same behaviou.
Any comment?
Thanks.
Jie Liang
-Original Message-
From: Kris Jurka [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 17, 2004 11:59 AM
To: Jie Liang
Cc: Tom Lane; [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: RE: [JDBC] Prepare Statement
On Thu, 17 Jun 2004, Jie Liang wrote:
Kirs,
I re-compile with setUseServerPrepare(true), it works fine, thanks.
However, reading from my log file, what I saw is that five same
SELECTs with different argument, so I am wondering that the
PrepareStatement really save time than individualy execute five
SELECTs ???
This is what I see in the log file:
2004-06-17 11:55:35 [23254] LOG: statement: PREPARE
JDBC_STATEMENT_1(integer) AS SELECT $1 ; EXECUTE JDBC_STATEMENT_1(1)
2004-06-17 11:55:35 [23254] LOG: statement: EXECUTE JDBC_STATEMENT_1(2)
2004-06-17 11:55:35 [23254] LOG: statement: EXECUTE JDBC_STATEMENT_1(3)
2004-06-17 11:55:35 [23254] LOG: statement: EXECUTE JDBC_STATEMENT_1(4)
2004-06-17 11:55:35 [23254] LOG: statement: EXECUTE JDBC_STATEMENT_1(5)
2004-06-17 11:55:35 [23254] LOG: statement: DEALLOCATE JDBC_STATEMENT_1
I don't know why this would be different for you. What exact version of
the server and driver are you using?
Kris Jurka
---(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
Re: [SQL] [JDBC] Prepare Statement
Hmm, intersting.
I am using jdk 1.3.1, and pg74.213.jdbc2.jar driver, I hope this bug
could be fixed in later version.
Thanks.
Jie Liang
-Original Message-
From: Kris Jurka [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 17, 2004 3:26 PM
To: Jie Liang
Cc: Tom Lane; [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: [JDBC] Prepare Statement
On Thu, 17 Jun 2004, Jie Liang wrote:
Kris,
You are right, I modified that piece of code a little bit,
CallableStatement stmt = conn.prepareCall({?=call chr(?)}); Then my
log file were: Select * from chr(65) as result;
Select * from chr(66) as result;
..
However, if I use:
PrepareStatement stmt = conn.prepareStatement(SELECT chr(?));
Then my log file are same as yours.i.e. it use PREPARE and EXECUTE.
So, I am getting confusion.
I think CallableStatement is extended from PrepareStatement, it should
have same behaviou.
What's happening here is that you can only use prepared statements for
certain operations. You can't for example prepare a CREATE TABLE
statement. The driver examines the query to see if it is valid for
preparing and I believe the problem here is that with a callable
statement
it is examinging the query with call before it is transformed to a
SELECT, so it doesn't recognize it as a preparable. This looks like a
bug
to me.
Kris Jurka
---(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
Re: [SQL] [JDBC] Prepare Statement
Kris,
I have another question, I saw some discussion regarding
PreparedStatement work with array argument, I get a error when I try to
play with it.
E.g.
I have myfunction(int[]),
So,
PrepareStatement st = conn.prepareStatment(SELECT myfunction(?));
String arr={1,2,3};
St.setString(1,arr};
Result rs = st.executeQuery();
Then it will complaint when it run:
Myfuntion(text) does not exist!
Did I miss something??
Thanks.
Jie Liang
-Original Message-
From: Kris Jurka [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 17, 2004 3:26 PM
To: Jie Liang
Cc: Tom Lane; [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: [JDBC] Prepare Statement
On Thu, 17 Jun 2004, Jie Liang wrote:
Kris,
You are right, I modified that piece of code a little bit,
CallableStatement stmt = conn.prepareCall({?=call chr(?)}); Then my
log file were: Select * from chr(65) as result;
Select * from chr(66) as result;
..
However, if I use:
PrepareStatement stmt = conn.prepareStatement(SELECT chr(?));
Then my log file are same as yours.i.e. it use PREPARE and EXECUTE.
So, I am getting confusion.
I think CallableStatement is extended from PrepareStatement, it should
have same behaviou.
What's happening here is that you can only use prepared statements for
certain operations. You can't for example prepare a CREATE TABLE
statement. The driver examines the query to see if it is valid for
preparing and I believe the problem here is that with a callable
statement
it is examinging the query with call before it is transformed to a
SELECT, so it doesn't recognize it as a preparable. This looks like a
bug
to me.
Kris Jurka
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] [JDBC] Prepare Statement
Kris,
Thank you for your valuable response, I used the code you list
following:
import java.sql.*;
public class ServerSidePreparedStatement
{
public static void main(String args[]) throws Exception
{
Class.forName(org.postgresql.Driver);
String url = jdbc:postgresql://localhost:5432/test;
Connection conn =
DriverManager.getConnection(url,test,);
PreparedStatement pstmt = conn.prepareStatement(SELECT
?);
// cast to the pg extension interface
org.postgresql.PGStatement pgstmt =
(org.postgresql.PGStatement)pstmt;
// on the third execution start using server side
statements
pgstmt.setPrepareThreshold(3);
for (int i=1; i=5; i++)
{
pstmt.setInt(1,i);
boolean usingServerPrepare =
pgstmt.isUseServerPrepare();
ResultSet rs = pstmt.executeQuery();
rs.next();
System.out.println(Execution: +i+, Used
server side: + usingServerPrepare + , Result: +rs.getInt(1));
rs.close();
}
pstmt.close();
conn.close();
}
}
Then, the compiler complaint:
ServerSidePreparedStatement.java:20: cannot resolve symbol symbol :
method setPrepareThreshold (int)
location: interface org.postgresql.PGStatement
pgstmt.setPrepareThreshold(3);
I downloaded pg74.213.jdbc2.jar and pg74.213.jdbc2ee.jar at
http://jdbc.postgresql.org/download.html
And had a try, I got same error msg.
I use java 1.3.1, postgresql -7.4.2, FreeBSD 4.7
What I need to do to make it work??
Thanks.
Jie Liang
-Original Message-
From: Kris Jurka [mailto:[EMAIL PROTECTED]
Sent: Tuesday, June 15, 2004 11:00 AM
To: Jie Liang
Cc: Tom Lane; [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: [JDBC] Prepare Statement
On Mon, 14 Jun 2004, Jie Liang wrote:
I have a question about performance, in SQL commands: there is a
prepare/execute command, document says it will improve the performance
while repeatly execute a statement. In java.sql: there is a
PreparedStatement object, which can store precompiled SQL statement,
document says it can improve the performance also. If I use java jdbc
to connect postgresql database, which one I should use? Can I use
both?
When using JDBC it is best to use the standard
Statement/PreparedStatement
interfaces. It is possible to directly use PREPARE/EXECUTE, but this
can
be handled by the driver. Let me give you a run down of the different
driver versions and their capabilities:
Current released version: can enable using PREPARE/EXECUTE behind the
scenes on PreparedStatement by casting the prepared statement to
PGStatement and issuing setUseServerPrepare.
Current cvs version: can enable using PREPARE/EXECUTE by setting an
execution threshold that will turn it on when reached. This threshold
can
be set at a number of levels, see the following for more information
http://www.ejurka.com/pgsql/docs/cvs/ch09s05.html
Soon to be committed cvs version: can directly use server prepared
statements without using the SQL level PREPARE/EXECUTE.
Kris Jurka
---(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] Prepare Statement
Tom, Does java.sql.PreparedStatement do the same thing as SQL command prepare/execute ?? Which one should be used while I am using jdbc talking to postgresql? If using both, do them help the performance? Thanks. Jie Liang -Original Message- From: Jie Liang Sent: Monday, June 14, 2004 4:33 PM To: Tom Lane Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: [SQL] Prepare Statement Hi, I have a question about performance, in SQL commands: there is a prepare/execute command, document says it will improve the performance while repeatly execute a statement. In java.sql: there is a PreparedStatement object, which can store precompiled SQL statement, document says it can improve the performance also. If I use java jdbc to connect postgresql database, which one I should use? Can I use both? Thanks. Jie Liang ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] Prepare Statement
Hi, I have a question about performance, in SQL commands: there is a prepare/execute command, document says it will improve the performance while repeatly execute a statement. In java.sql: there is a PreparedStatement object, which can store precompiled SQL statement, document says it can improve the performance also. If I use java jdbc to connect postgresql database, which one I should use? Can I use both? Thanks. Jie Liang ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] rules
According to the document of rule:
CREATE RULE rulename AS ON delete TO mytablename DO
(
delete from aaa where id=OLD.id;
Delete from bbb where id=OLD.id;
Delete from ccc where id=OLD.id
);
Should work, but it doesn't, what wrong with it?
Even I use { }
Jie Liang
---(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
Re: [SQL] [ADMIN] rules
Sorry, wrong question.
-Original Message-
From: Jie Liang
Sent: Wednesday, May 19, 2004 10:20 AM
To: Tom Lane
Cc: postgres-list; [EMAIL PROTECTED]
Subject: [ADMIN] rules
According to the document of rule:
CREATE RULE rulename AS ON delete TO mytablename DO
(
delete from aaa where id=OLD.id;
Delete from bbb where id=OLD.id;
Delete from ccc where id=OLD.id
);
Should work, but it doesn't, what wrong with it?
Even I use { }
Jie Liang
---(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 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
Re: [SQL] \set
I am not talking about SET, I am talking about \set command.
Yes, I understand, plpgsql can work around it, but I think postgresql
should have a simple way to do it.
E.g.
Db\set AAA 'whatever'
Db\set
You will see AAA associate with 'whatever', it's an internal variable,
but how could I use it in my SQL query?
Thanks anyway.
Jie Liang
-Original Message-
From: Christian Kratzer [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 12, 2004 3:13 AM
To: Jie Liang
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: [SQL] \set
Hi,
On Tue, 11 May 2004, Jie Liang wrote:
Hi,
How to use an internal variable?
Original question was how to set a variable in postgresql?
If I want to set a variable like start_date='2004-05-10';
How could I use it in my SQL statement?
E.g.
Db set start_date '2004-05-10'
Db select start_date as 'start date';
It's not executable!
from doc/postgresql/html/sql-set.html
--snipp--
Synopsis
SET [ SESSION | LOCAL ] name { TO | = } { value | 'value' |
DEFAULT }
SET [ SESSION | LOCAL ] TIME ZONE { timezone | LOCAL | DEFAULT }
...
...
...
name
Name of a settable run-time parameter.
Available parameters are documented in Section 16.4 and
below.
--snipp--
that is you can only use SET to change specific predefined parameters.
It does not say you could use SET to store other data local to the
database session.
As far as I know there are no session local variables in postgresql. I
could use something like this myself. The only workaround I know of
to have data local to a session is to create a temporary table for the
data.
There are local variables in plpgsql if you are just looking for local
variables.
Greetings
Christian
--
Christian Kratzer [EMAIL PROTECTED]
CK Software GmbHhttp://www.cksoft.de/
Phone: +49 7452 889 135 Fax: +49 7452 889 136
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] [SQL] \set
Thank you, Tom. Jie -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 12, 2004 4:06 PM To: Jie Liang Cc: Christian Kratzer; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: [ADMIN] [SQL] \set Jie Liang [EMAIL PROTECTED] writes: You will see AAA associate with 'whatever', it's an internal variable, but how could I use it in my SQL query? regression=# \set AAA 'whatever' regression=# select :AAA; ERROR: column whatever does not exist regression=# \set AAA '\'whatever\'' regression=# select :AAA; ?column? -- whatever (1 row) regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] \set
Hi, How to use an internal variable? Original question was how to set a variable in postgresql? If I want to set a variable like start_date='2004-05-10'; How could I use it in my SQL statement? E.g. Db set start_date '2004-05-10' Db select start_date as 'start date'; It's not executable! Thanks. Jie Liang ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] \df
Hi, What sql statement equal to \df function_name I want to know the result data type for a given function within plpgsql. Thanks. Jie Liang ---(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
Re: [SQL] \df
Thanks. Jie Liang -Original Message- From: Stefan Weiss [mailto:[EMAIL PROTECTED] Sent: Friday, May 07, 2004 4:40 PM To: [EMAIL PROTECTED] Subject: Re: [SQL] \df On Saturday, 08 May 2004 01:15, Jie Liang wrote: What sql statement equal to \df function_name I want to know the result data type for a given function within plpgsql. Try the -E switch for pgsql: [EMAIL PROTECTED]:~ $ psql -E Welcome to psql 7.4.2, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit spaceman=# \df alt_to_iso * QUERY ** SELECT CASE WHEN p.proretset THEN 'setof ' ELSE '' END || pg_catalog.format_type(p.prorettype, NULL) as Result data type, n.nspname as Schema, p.proname as Name, pg_catalog.oidvectortypes(p.proargtypes) as Argument data types FROM pg_catalog.pg_proc p LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace WHERE p.prorettype 'pg_catalog.cstring'::pg_catalog.regtype AND p.proargtypes[0] 'pg_catalog.cstring'::pg_catalog.regtype AND NOT p.proisagg AND pg_catalog.pg_function_is_visible(p.oid) AND p.proname ~ '^alt_to_iso$' ORDER BY 2, 3, 1, 4; ** List of functions Result data type | Schema |Name| Argument data types --+++--- --+++-- void | pg_catalog | alt_to_iso | integer, integer, cstring, ... (1 row) HTH, stefan weiss ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] pg_restore cannot restore an index
Last July, I pointed out this problem when I use v7.2.1, I got the answer that will be
resolved in v7.3, however, I am using v7.3.1, pg_restore.c seems have no change in
this section. So it still doesn't work.
Jie Liang
Jie Liang wrote:
I read the pg_restore.c source code, I found:
#ifdef HAVE_GETOPT_LONG
struct option cmdopts[] = {
{clean, 0, NULL, 'c'},
{create, 0, NULL, 'C'},
{data-only, 0, NULL, 'a'},
{dbname, 1, NULL, 'd'},
{file, 1, NULL, 'f'},
{format, 1, NULL, 'F'},
{function, 1, NULL, 'P'},
{host, 1, NULL, 'h'},
{ignore-version, 0, NULL, 'i'},
{index, 1, NULL, 'I'},
So, -i may be mapped wrong, however, -I is illegal option.
Thanks!
---(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] pg_restore cannot restore an index
Sorry, it because I have another index with same indexname because pg_restore index
fail.
Thanks.
Jie Liang
-Original Message-
From: Bruce Momjian [mailto:[EMAIL PROTECTED]]
Sent: Thursday, January 16, 2003 10:07 AM
To: Jie Liang
Cc: Tom Lane; [EMAIL PROTECTED]
Subject: Re: [SQL] pg_restore cannot restore an index
Yes, I remember this. The code in 7.3 looks OK to me. Can you show me a
command line that fails for you?
I just tried:
$ pg_restore -I x asdf
pg_restore: [archiver] could not open input file: No such file or directory
so it looks like -I is working.
---
Jie Liang wrote:
Last July, I pointed out this problem when I use v7.2.1, I got the answer that will
be resolved in v7.3, however, I am using v7.3.1, pg_restore.c seems have no change in
this section. So it still doesn't work.
Jie Liang
Jie Liang wrote:
I read the pg_restore.c source code, I found:
#ifdef HAVE_GETOPT_LONG
struct option cmdopts[] = {
{clean, 0, NULL, 'c'},
{create, 0, NULL, 'C'},
{data-only, 0, NULL, 'a'},
{dbname, 1, NULL, 'd'},
{file, 1, NULL, 'f'},
{format, 1, NULL, 'F'},
{function, 1, NULL, 'P'},
{host, 1, NULL, 'h'},
{ignore-version, 0, NULL, 'i'},
{index, 1, NULL, 'I'},
So, -i may be mapped wrong, however, -I is illegal option.
Thanks!
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] server terminated by a query in 7.3
Tom, I've a perl script, which has been used for a long time, it works well, however, after I upgrade my postgresql from 7.2 to 7.3, one query always makes server terminated, could you give me a solution for it? Thanks. Jie Liang SELECT urlinfo.id,url,iprism_map,iprism_map_sg,level,domid, CASE WHEN ratedon '2002-11-24' AND ratedby NOT LIKE '%jurl' THEN 1 ELSE 0 END as trunc INTO TEMP filter96512 FROM urlinfo,ratings_by_serial,cid_code96512 WHERE pidwsr=0 AND urlinfo.id=ratings_by_serial.id AND ratings_by_serial.cid=cid_code96512.cid; CREATE index filter_temp_id ON filter96512(id); VACUUM ANALYZE filter96512; SELECT id,url,9 as iprism_map,max(level) as level,domid,trunc INTO TEMP filter0 FROM filter96512 WHERE iprism_map!~'^[14]' GROUP BY id,url,domid,trunc; SELECT distinct on(id) id,url,1 as iprism_map,99 as level,domid,trunc INTO TEMP filter_0 FROM filter96512 WHERE iprism_map='1'; server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. FROM LOG: Dec 13 09:21:38 beijing postgres[699]: [30-1] LOG: query: SELECT distinct on(id) id,url,1 as iprism_map,99 as level,domid,trunc INTO TEMP filter_0 FROM filter96512 Dec 13 09:21:38 beijing postgres[699]: [30-2] WHERE iprism_map='1' Dec 13 09:22:34 beijing postgres[141]: [9] LOG: server process (pid 699) was terminated by signal 11 Dec 13 09:22:34 beijing postgres[141]: [10] LOG: terminating any other active server processes Dec 13 09:22:34 beijing postgres[141]: [11] LOG: all server processes terminated; reinitializing shared memory and semaphores Dec 13 09:22:34 beijing postgres[1844]: [12] LOG: connection received: host=[local] Dec 13 09:22:34 beijing postgres[1844]: [13] FATAL: The database system is starting up Dec 13 09:22:35 beijing postgres[1843]: [12] LOG: database system was interrupted at 2002-12-13 09:08:04 PST Dec 13 09:22:35 beijing postgres[1843]: [13] LOG: checkpoint record is at 1/D654B54 Dec 13 09:22:35 beijing postgres[1843]: [14] LOG: redo record is at 1/D654B54; undo record is at 0/0; shutdown FALSE Dec 13 09:22:35 beijing postgres[1843]: [15] LOG: next transaction id: 5377; next oid: 35529333 Dec 13 09:22:35 beijing postgres[1843]: [16] LOG: database system was not properly shut down; automatic recovery in progress Dec 13 09:22:35 beijing postgres[1843]: [17] LOG: redo starts at 1/D654B94 Dec 13 09:22:35 beijing postgres[1843]: [18] LOG: ReadRecord: record with zero length at 1/D6819E8 Dec 13 09:22:35 beijing postgres[1843]: [19] LOG: redo done at 1/D6819A4 Dec 13 09:22:38 beijing postgres[1843]: [20] LOG: database system is ready ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] server terminated by a query in 7.3
Maybe you did a patch in localbuff.c to fix that vacuuming temp table? Jie liang -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED]] Sent: Friday, December 13, 2002 10:12 AM To: Jie Liang Cc: [EMAIL PROTECTED]; '[EMAIL PROTECTED]' Subject: Re: server terminated by a query in 7.3 Jie Liang [EMAIL PROTECTED] writes: however, after I upgrade my postgresql from 7.2 to 7.3, one query always makes server terminated, could you give me a solution for it? I cannot reproduce this with the information you gave. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] schedule of v7.3
Bruce, What is the schedule for releasing v7.3 stable? Oct? Thanks. Jie Liang ---(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] pg_restore cannot restore index
Same problem, did you test: pg_restore --index=aa --dbname=test /bjm/x ?? I didn't make it work, I may miss someting. Thanks! Jie Liang -Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED]] Sent: Saturday, July 13, 2002 7:51 AM To: Jie Liang Cc: 'Jan Wieck'; '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]' Subject: Re: pg_restore cannot restore index Jie Liang wrote: On this point, I'd like to ask: 1. where I can download this new version? 2. does pg_restore --index=aa --dbname=test /bjm/x works also??? OK, the attached patch should allow -I to work in 7.2.X. This will all be fixed in 7.3. Because pg_restore --table=mytable --dbname=mydb mydumpfile doesn't work! Is this a different problem? --table doesn't work either? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(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] pg_restore cannot restore index
su postgres -c /usr/local/pgsql/bin/pg_restore --table=mytable --dbname=mydb mydumpfile error msg pg_restore: [archiver] could not open input file: No such file or directory I run it on 7.2.0 Jie Liang -Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED]] Sent: Monday, July 15, 2002 3:24 PM To: Jie Liang Cc: 'Jan Wieck'; '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]' Subject: Re: pg_restore cannot restore index I just ran some tests in 7.2.1 and 7.3 and both worked fine. Can I see the exact error it generates? --- Jie Liang wrote: Same problem, did you test: pg_restore --index=aa --dbname=test /bjm/x ?? I didn't make it work, I may miss someting. Thanks! Jie Liang -Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED]] Sent: Saturday, July 13, 2002 7:51 AM To: Jie Liang Cc: 'Jan Wieck'; '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]' Subject: Re: pg_restore cannot restore index Jie Liang wrote: On this point, I'd like to ask: 1. where I can download this new version? 2. does pg_restore --index=aa --dbname=test /bjm/x works also??? OK, the attached patch should allow -I to work in 7.2.X. This will all be fixed in 7.3. Because pg_restore --table=mytable --dbname=mydb mydumpfile doesn't work! Is this a different problem? --table doesn't work either? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[SQL] how pg_restore long form works?
su postgres -c /usr/local/pgsql/bin/pg_restore --table=mytable --dbname=mydb mydumpfile error msg pg_restore: [archiver] could not open input file: No such file or directory I run it on 7.2.0 Jie Liang -Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED]] Sent: Monday, July 15, 2002 3:24 PM To: Jie Liang Cc: 'Jan Wieck'; '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]' Subject: Re: pg_restore cannot restore index I just ran some tests in 7.2.1 and 7.3 and both worked fine. Can I see the exact error it generates? --- Jie Liang wrote: Same problem, did you test: pg_restore --index=aa --dbname=test /bjm/x ?? I didn't make it work, I may miss someting. Thanks! Jie Liang -Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED]] Sent: Saturday, July 13, 2002 7:51 AM To: Jie Liang Cc: 'Jan Wieck'; '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]' Subject: Re: pg_restore cannot restore index Jie Liang wrote: On this point, I'd like to ask: 1. where I can download this new version? 2. does pg_restore --index=aa --dbname=test /bjm/x works also??? OK, the attached patch should allow -I to work in 7.2.X. This will all be fixed in 7.3. Because pg_restore --table=mytable --dbname=mydb mydumpfile doesn't work! Is this a different problem? --table doesn't work either? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] pg_restore --flag
I tried, I got same error msg. I even run as user postgres or myself, same same. My OS=FreeBSD4.3 DB=PostgreSQL7.2 Jie Liang -Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED]] Sent: Monday, July 15, 2002 4:25 PM To: Jie Liang Cc: 'Jan Wieck'; '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]' Subject: Re: pg_restore cannot restore index Jie Liang wrote: su postgres -c /usr/local/pgsql/bin/pg_restore --table=mytable --dbname=mydb mydumpfile error msg pg_restore: [archiver] could not open input file: No such file or directory I run it on 7.2.0 OK, my guess is that the 'su' is moving you to another directory. Try specifying the full path of the file, e.g. /var/tmp/mydumpfile. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Please, HELP! Why is the query plan so wrong???
please copy and paste the whole msg and your query! Note:what I mean ' join key' is the fields that link two tables. I don't think fb.b=0 is a join key! Jie Liang -Original Message- From: Dmitry Tkach [mailto:[EMAIL PROTECTED]] Sent: Friday, July 12, 2002 7:34 AM To: Jie Liang Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: [SQL] Please, HELP! Why is the query plan so wrong??? Jie Liang wrote: I believe that SQL will use the index of join 'key' when you join the tables if have any, in your query the (a,c) is the join key but d is not. Jie Liang Not really... I tried this: explain select * from fb joing fbr on (fb.a=fbr.a and fb.c=fbr.c and fbr.d is null) where fb.b=0 It results in the same query plan (seq scan on fbr). Dima -Original Message- From: Dmitry Tkach [mailto:[EMAIL PROTECTED]] Sent: Thursday, July 11, 2002 3:51 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: [SQL] Please, HELP! Why is the query plan so wrong??? Hi, everybody! Here is the problem: test=# create table fb (a int, b int, c datetime); CREATE test=# create table fbr (a int, c datetime, d int); CREATE test=# create unique index fb_idx on fb(b); CREATE test=# create index fbr_idx on fbr(a,c) where d is null; CREATE test=# set enable_seqscan=off; SET VARIABLE rapidb=# explain select * from fb, fbr where fb.b=0 and fb.a=fbr.a and fb.c=fbr.c and fbr.d is null; NOTICE: QUERY PLAN: Hash Join (cost=10005.82..11015.87 rows=1 width=32) - Seq Scan on fbr (cost=1.00..11010.00 rows=5 width=16) - Hash (cost=5.81..5.81 rows=1 width=16) - Index Scan using fb_idx on fb (cost=0.00..5.81 rows=1 width=16) Could someone PLEASE explain to me, why doesn't it want to use the index on fbr? If I get rid of the join, then it works: test=# explain select * from fbr where a=1 and c=now() and d is null; NOTICE: QUERY PLAN: Index Scan using fbr_idx on fbr (cost=0.00..5.82 rows=1 width=16) What's the catch??? Any help would be greatly appreciated! Thanks! Dima ---(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]
Re: [SQL] pg_restore cannot restore index
On this point, I'd like to ask:
1. where I can download this new version?
2. does
pg_restore --index=aa --dbname=test /bjm/x
works also???
Because
pg_restore --table=mytable --dbname=mydb mydumpfile
doesn't work!
I got same error msg.
Jie Liang
-Original Message-
From: Bruce Momjian [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, July 10, 2002 7:06 PM
To: Jie Liang
Cc: 'Jan Wieck'; '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]'
Subject: Re: pg_restore cannot restore index
Jie Liang wrote:
Thanks!
But I did not make long form works also, is it:
pg_restore --index=\indexname\ --dbname=mydb mydumpfile
???
msg:
pg_restore:[archiver] could open input file: No such file or directory
Strange. I found a few more problems with the getopt values in
pg_restore.c not matching the 'case' statement or the documentation.
I got it working here with my patched version using:
pg_restore -I aa -d test /bjm/x
It can't find the file? I didn't fix anything in that area. I am
confused how that could be messed up. I don't see any other meaningful
changes to pg_restore except the quote fixes I did. Are you sure the
file is correct?
Thanks for pointing these things out. It is a big help. Now I am
starting to wonder what else is wrong in the code. :-)
could you give out a example of long form
Thanks again.
Jie Liang
-Original Message-
From: Bruce Momjian [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, July 09, 2002 7:59 PM
To: Jie Liang
Cc: 'Jan Wieck'; '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]'
Subject: Re: pg_restore cannot restore index
Yep, documentation is wrong. Documentation patch attached and applied.
Also, in 7.3 you will not need the weird quoting for objects.
---
Jie Liang wrote:
Another possible bug:
pg_restore -i \indexname\ -d mydb mydumpfile
msg:
pg_restore: connecting to database for restore
pg_restore: creating FUNCTION plpgsql_call_handler ()
pg_restore: [archiver (db)] could not execute query: ERROR: function
plpgsql_call_handler already exists with same argument types
pg_restore: *** aborted because of error
I read the pg_restore.c source code, I found:
#ifdef HAVE_GETOPT_LONG
struct option cmdopts[] = {
{clean, 0, NULL, 'c'},
{create, 0, NULL, 'C'},
{data-only, 0, NULL, 'a'},
{dbname, 1, NULL, 'd'},
{file, 1, NULL, 'f'},
{format, 1, NULL, 'F'},
{function, 1, NULL, 'P'},
{host, 1, NULL, 'h'},
{ignore-version, 0, NULL, 'i'},
{index, 1, NULL, 'I'},
So, -i may be mapped wrong, however, -I is illegal option.
Thanks!
Jie Liang
-Original Message-
From: Jie Liang [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, July 03, 2002 12:03 PM
To: 'Jan Wieck'; Jie Liang
Cc: 'Bruce Momjian'; '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]'
Subject: Re: [SQL] pg_restore cannot restore function
OK, we figured it out.
The problem is the documentation confused me!!!
In man page of pg_restore:
-P function-name
--function=function name
Specify a procedure or function to be restored.
User will assume that syntax of restoring a function is same as
restoring a table, but it's not true, it's slightly different.
To restore a table:
pg_restore -Rxt mytable -d mydb2 dbf
works, but to restore a function:
pg_restore -P myfunction -d mydb2 dbf
won't work, and you need to use:
pg_restore -P \myfunction\ (args and type) -d mydb2 dbf
to make it work!
I believe that the man page of pg_restore should be improved.
Thanks.
Jie Liang
-Original Message-
From: Jan Wieck [mailto:[EMAIL PROTECTED]]
Sent: Monday, July 01, 2002 11:14 AM
To: Jie Liang
Cc: 'Bruce Momjian'; '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]'
Subject: Re: [SQL] pg_restore cannot restore function
Jie Liang wrote:
Oops,my OS is FreeBSD4.3 PostgreSQL7.2
I cannot see such an error message in the pg_restore sources at all. Are
you sure to use the right versions together?
Jan
Thanks
Jie Liang
-Original Message-
From: Jie Liang
Sent: Friday, June 28, 2002 1:46 PM
To: 'Jan Wieck'
Cc: 'Bruce Momjian'; '[EMAIL PROTECTED]';
'[EMAIL PROTECTED]'
Subject: RE: [SQL] pg_restore cannot restore function
No any error msg in the logfile, I didn't see any create function
statement
in my logfile which I enabled the query log.
This function is written in PL/pgSQL which is enabled in target db,
If I pg_dump the schema into a plain text file, I can see its
defination
there, I can easily copy paste (restore) it into mydb2.
however, I failed to restore it by using flag -P with compressed file.
I also tried to use
Re: [SQL] pg_restore cannot restore index
What parameter I should change in order to make postmaster taking CPU as much as possible? Maybe I should ask: how can I make big tables equijoin faster? I have a serveral tables that contain more 2.5 million records, I need to equijoin those tables often. Thanks! Jie Liang ---(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
Re: [SQL] Please, HELP! Why is the query plan so wrong???
I believe that SQL will use the index of join 'key' when you join the tables if have any, in your query the (a,c) is the join key but d is not. Jie Liang -Original Message- From: Dmitry Tkach [mailto:[EMAIL PROTECTED]] Sent: Thursday, July 11, 2002 3:51 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: [SQL] Please, HELP! Why is the query plan so wrong??? Hi, everybody! Here is the problem: test=# create table fb (a int, b int, c datetime); CREATE test=# create table fbr (a int, c datetime, d int); CREATE test=# create unique index fb_idx on fb(b); CREATE test=# create index fbr_idx on fbr(a,c) where d is null; CREATE test=# set enable_seqscan=off; SET VARIABLE rapidb=# explain select * from fb, fbr where fb.b=0 and fb.a=fbr.a and fb.c=fbr.c and fbr.d is null; NOTICE: QUERY PLAN: Hash Join (cost=10005.82..11015.87 rows=1 width=32) - Seq Scan on fbr (cost=1.00..11010.00 rows=5 width=16) - Hash (cost=5.81..5.81 rows=1 width=16) - Index Scan using fb_idx on fb (cost=0.00..5.81 rows=1 width=16) Could someone PLEASE explain to me, why doesn't it want to use the index on fbr? If I get rid of the join, then it works: test=# explain select * from fbr where a=1 and c=now() and d is null; NOTICE: QUERY PLAN: Index Scan using fbr_idx on fbr (cost=0.00..5.82 rows=1 width=16) What's the catch??? Any help would be greatly appreciated! Thanks! Dima ---(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 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] pg_restore cannot restore index
Thanks!
But I did not make long form works also, is it:
pg_restore --index=\indexname\ --dbname=mydb mydumpfile
???
msg:
pg_restore:[archiver] could open input file: No such file or directory
could you give out a example of long form
Thanks again.
Jie Liang
-Original Message-
From: Bruce Momjian [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, July 09, 2002 7:59 PM
To: Jie Liang
Cc: 'Jan Wieck'; '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]'
Subject: Re: pg_restore cannot restore index
Yep, documentation is wrong. Documentation patch attached and applied.
Also, in 7.3 you will not need the weird quoting for objects.
---
Jie Liang wrote:
Another possible bug:
pg_restore -i \indexname\ -d mydb mydumpfile
msg:
pg_restore: connecting to database for restore
pg_restore: creating FUNCTION plpgsql_call_handler ()
pg_restore: [archiver (db)] could not execute query: ERROR: function
plpgsql_call_handler already exists with same argument types
pg_restore: *** aborted because of error
I read the pg_restore.c source code, I found:
#ifdef HAVE_GETOPT_LONG
struct option cmdopts[] = {
{clean, 0, NULL, 'c'},
{create, 0, NULL, 'C'},
{data-only, 0, NULL, 'a'},
{dbname, 1, NULL, 'd'},
{file, 1, NULL, 'f'},
{format, 1, NULL, 'F'},
{function, 1, NULL, 'P'},
{host, 1, NULL, 'h'},
{ignore-version, 0, NULL, 'i'},
{index, 1, NULL, 'I'},
So, -i may be mapped wrong, however, -I is illegal option.
Thanks!
Jie Liang
-Original Message-
From: Jie Liang [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, July 03, 2002 12:03 PM
To: 'Jan Wieck'; Jie Liang
Cc: 'Bruce Momjian'; '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]'
Subject: Re: [SQL] pg_restore cannot restore function
OK, we figured it out.
The problem is the documentation confused me!!!
In man page of pg_restore:
-P function-name
--function=function name
Specify a procedure or function to be restored.
User will assume that syntax of restoring a function is same as
restoring a table, but it's not true, it's slightly different.
To restore a table:
pg_restore -Rxt mytable -d mydb2 dbf
works, but to restore a function:
pg_restore -P myfunction -d mydb2 dbf
won't work, and you need to use:
pg_restore -P \myfunction\ (args and type) -d mydb2 dbf
to make it work!
I believe that the man page of pg_restore should be improved.
Thanks.
Jie Liang
-Original Message-
From: Jan Wieck [mailto:[EMAIL PROTECTED]]
Sent: Monday, July 01, 2002 11:14 AM
To: Jie Liang
Cc: 'Bruce Momjian'; '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]'
Subject: Re: [SQL] pg_restore cannot restore function
Jie Liang wrote:
Oops,my OS is FreeBSD4.3 PostgreSQL7.2
I cannot see such an error message in the pg_restore sources at all. Are
you sure to use the right versions together?
Jan
Thanks
Jie Liang
-Original Message-
From: Jie Liang
Sent: Friday, June 28, 2002 1:46 PM
To: 'Jan Wieck'
Cc: 'Bruce Momjian'; '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]'
Subject: RE: [SQL] pg_restore cannot restore function
No any error msg in the logfile, I didn't see any create function
statement
in my logfile which I enabled the query log.
This function is written in PL/pgSQL which is enabled in target db,
If I pg_dump the schema into a plain text file, I can see its defination
there, I can easily copy paste (restore) it into mydb2.
however, I failed to restore it by using flag -P with compressed file.
I also tried to use
su postgres -c /usr/local/pgsql/bin/pg_restore --function=myfunction
--dbname=mydb2 dbf
error msg
pg_restore: [archiver] could not open input file: No such file or
directory
weird???
I use
pg_restore -Rxt mytable -d mydb2 dbf
have no such a problem, it works.
Is any syntax error??
I am confused by documentation now!
Is it a bug
Thanks
Jie Liang
-Original Message-
From: Jan Wieck [mailto:[EMAIL PROTECTED]]
Sent: Friday, June 28, 2002 12:39 PM
To: Jie Liang
Cc: 'Bruce Momjian'; '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]'
Subject: Re: [SQL] pg_restore cannot restore function
Jie Liang wrote:
I use
pg_dump -Fc mydb dbf
then I create another db by:
createdb mydb2
I use
pg_restore -P myfunction -d mydb2 dbf
cannot restore myfunction into mydb2
why??
Good question. Is there any error message in the postmaster log?
If the function is written in a procedural language, is that language
enabled in the target database? If the function is written in the SQL
language, do all underlying objects like tables and views exist? If it's
a C language function, does the shared object containing
[SQL] pg_restore cannot restore index
Another possible bug:
pg_restore -i \indexname\ -d mydb mydumpfile
msg:
pg_restore: connecting to database for restore
pg_restore: creating FUNCTION plpgsql_call_handler ()
pg_restore: [archiver (db)] could not execute query: ERROR: function
plpgsql_call_handler already exists with same argument types
pg_restore: *** aborted because of error
I read the pg_restore.c source code, I found:
#ifdef HAVE_GETOPT_LONG
struct option cmdopts[] = {
{clean, 0, NULL, 'c'},
{create, 0, NULL, 'C'},
{data-only, 0, NULL, 'a'},
{dbname, 1, NULL, 'd'},
{file, 1, NULL, 'f'},
{format, 1, NULL, 'F'},
{function, 1, NULL, 'P'},
{host, 1, NULL, 'h'},
{ignore-version, 0, NULL, 'i'},
{index, 1, NULL, 'I'},
So, -i may be mapped wrong, however, -I is illegal option.
Thanks!
Jie Liang
-Original Message-
From: Jie Liang [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, July 03, 2002 12:03 PM
To: 'Jan Wieck'; Jie Liang
Cc: 'Bruce Momjian'; '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]'
Subject: Re: [SQL] pg_restore cannot restore function
OK, we figured it out.
The problem is the documentation confused me!!!
In man page of pg_restore:
-P function-name
--function=function name
Specify a procedure or function to be restored.
User will assume that syntax of restoring a function is same as
restoring a table, but it's not true, it's slightly different.
To restore a table:
pg_restore -Rxt mytable -d mydb2 dbf
works, but to restore a function:
pg_restore -P myfunction -d mydb2 dbf
won't work, and you need to use:
pg_restore -P \myfunction\ (args and type) -d mydb2 dbf
to make it work!
I believe that the man page of pg_restore should be improved.
Thanks.
Jie Liang
-Original Message-
From: Jan Wieck [mailto:[EMAIL PROTECTED]]
Sent: Monday, July 01, 2002 11:14 AM
To: Jie Liang
Cc: 'Bruce Momjian'; '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]'
Subject: Re: [SQL] pg_restore cannot restore function
Jie Liang wrote:
Oops,my OS is FreeBSD4.3 PostgreSQL7.2
I cannot see such an error message in the pg_restore sources at all. Are
you sure to use the right versions together?
Jan
Thanks
Jie Liang
-Original Message-
From: Jie Liang
Sent: Friday, June 28, 2002 1:46 PM
To: 'Jan Wieck'
Cc: 'Bruce Momjian'; '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]'
Subject: RE: [SQL] pg_restore cannot restore function
No any error msg in the logfile, I didn't see any create function
statement
in my logfile which I enabled the query log.
This function is written in PL/pgSQL which is enabled in target db,
If I pg_dump the schema into a plain text file, I can see its defination
there, I can easily copy paste (restore) it into mydb2.
however, I failed to restore it by using flag -P with compressed file.
I also tried to use
su postgres -c /usr/local/pgsql/bin/pg_restore --function=myfunction
--dbname=mydb2 dbf
error msg
pg_restore: [archiver] could not open input file: No such file or
directory
weird???
I use
pg_restore -Rxt mytable -d mydb2 dbf
have no such a problem, it works.
Is any syntax error??
I am confused by documentation now!
Is it a bug
Thanks
Jie Liang
-Original Message-
From: Jan Wieck [mailto:[EMAIL PROTECTED]]
Sent: Friday, June 28, 2002 12:39 PM
To: Jie Liang
Cc: 'Bruce Momjian'; '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]'
Subject: Re: [SQL] pg_restore cannot restore function
Jie Liang wrote:
I use
pg_dump -Fc mydb dbf
then I create another db by:
createdb mydb2
I use
pg_restore -P myfunction -d mydb2 dbf
cannot restore myfunction into mydb2
why??
Good question. Is there any error message in the postmaster log?
If the function is written in a procedural language, is that language
enabled in the target database? If the function is written in the SQL
language, do all underlying objects like tables and views exist? If it's
a C language function, does the shared object containing the function
exist at the expected location?
Jan
--
#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#== [EMAIL PROTECTED] #
--
#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#== [EMAIL PROTECTED] #
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
---(end of broadcast)---
TIP 3
Re: [SQL] pg_restore cannot restore index
Another possible bug:
pg_restore -i \indexname\ -d mydb mydumpfile
msg:
pg_restore: connecting to database for restore
pg_restore: creating FUNCTION plpgsql_call_handler ()
pg_restore: [archiver (db)] could not execute query: ERROR: function
plpgsql_call_handler already exists with same argument types
pg_restore: *** aborted because of error
I read the pg_restore.c source code, I found:
#ifdef HAVE_GETOPT_LONG
struct option cmdopts[] = {
{clean, 0, NULL, 'c'},
{create, 0, NULL, 'C'},
{data-only, 0, NULL, 'a'},
{dbname, 1, NULL, 'd'},
{file, 1, NULL, 'f'},
{format, 1, NULL, 'F'},
{function, 1, NULL, 'P'},
{host, 1, NULL, 'h'},
{ignore-version, 0, NULL, 'i'},
{index, 1, NULL, 'I'},
So, -i may be mapped wrong, however, -I is illegal option.
Thanks!
Jie Liang
---(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] pg_restore cannot restore function
I am sure. I assume that pg_restore -t mytable -d mydb mydumpfile is same as pg_restore --table=mytable --dbname=mydb mydumpfile but it is not! the 2nd one will get: pg_restore: [archiver] could not open input file: No such file or directory Jie Liang -Original Message- From: Jan Wieck [mailto:[EMAIL PROTECTED]] Sent: Monday, July 01, 2002 11:14 AM To: Jie Liang Cc: 'Bruce Momjian'; '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]' Subject: Re: [SQL] pg_restore cannot restore function Jie Liang wrote: Oops,my OS is FreeBSD4.3 PostgreSQL7.2 I cannot see such an error message in the pg_restore sources at all. Are you sure to use the right versions together? Jan Thanks Jie Liang -Original Message- From: Jie Liang Sent: Friday, June 28, 2002 1:46 PM To: 'Jan Wieck' Cc: 'Bruce Momjian'; '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]' Subject: RE: [SQL] pg_restore cannot restore function No any error msg in the logfile, I didn't see any create function statement in my logfile which I enabled the query log. This function is written in PL/pgSQL which is enabled in target db, If I pg_dump the schema into a plain text file, I can see its defination there, I can easily copy paste (restore) it into mydb2. however, I failed to restore it by using flag -P with compressed file. I also tried to use su postgres -c /usr/local/pgsql/bin/pg_restore --function=myfunction --dbname=mydb2 dbf error msg pg_restore: [archiver] could not open input file: No such file or directory weird??? I use pg_restore -Rxt mytable -d mydb2 dbf have no such a problem, it works. Is any syntax error?? I am confused by documentation now! Is it a bug Thanks Jie Liang -Original Message- From: Jan Wieck [mailto:[EMAIL PROTECTED]] Sent: Friday, June 28, 2002 12:39 PM To: Jie Liang Cc: 'Bruce Momjian'; '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]' Subject: Re: [SQL] pg_restore cannot restore function Jie Liang wrote: I use pg_dump -Fc mydb dbf then I create another db by: createdb mydb2 I use pg_restore -P myfunction -d mydb2 dbf cannot restore myfunction into mydb2 why?? Good question. Is there any error message in the postmaster log? If the function is written in a procedural language, is that language enabled in the target database? If the function is written in the SQL language, do all underlying objects like tables and views exist? If it's a C language function, does the shared object containing the function exist at the expected location? Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(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
Re: [SQL] pg_restore cannot restore function
This is not the case, because those db on a same server, it's I dump data from one db and try restore one of it function into another db. Thanks for your response anyway. Jie Liang -Original Message- From: Achilleus Mantzios [mailto:[EMAIL PROTECTED]] Sent: Wednesday, July 03, 2002 12:52 AM To: Jie Liang Cc: 'Jan Wieck'; 'Bruce Momjian'; '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]' Subject: Re: [SQL] pg_restore cannot restore function In the case that you moved your backup to another system where possibly the shared library (.so) where the function exists is on a different location then thats the problem, in which case you only need to recreate the function (with the same isstrict,iscachable attributes). -- Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] pg_restore cannot restore function
OK, we figured it out. The problem is the documentation confused me!!! In man page of pg_restore: -P function-name --function=function name Specify a procedure or function to be restored. User will assume that syntax of restoring a function is same as restoring a table, but it's not true, it's slightly different. To restore a table: pg_restore -Rxt mytable -d mydb2 dbf works, but to restore a function: pg_restore -P myfunction -d mydb2 dbf won't work, and you need to use: pg_restore -P \myfunction\ (args and type) -d mydb2 dbf to make it work! I believe that the man page of pg_restore should be improved. Thanks. Jie Liang -Original Message- From: Jan Wieck [mailto:[EMAIL PROTECTED]] Sent: Monday, July 01, 2002 11:14 AM To: Jie Liang Cc: 'Bruce Momjian'; '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]' Subject: Re: [SQL] pg_restore cannot restore function Jie Liang wrote: Oops,my OS is FreeBSD4.3 PostgreSQL7.2 I cannot see such an error message in the pg_restore sources at all. Are you sure to use the right versions together? Jan Thanks Jie Liang -Original Message- From: Jie Liang Sent: Friday, June 28, 2002 1:46 PM To: 'Jan Wieck' Cc: 'Bruce Momjian'; '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]' Subject: RE: [SQL] pg_restore cannot restore function No any error msg in the logfile, I didn't see any create function statement in my logfile which I enabled the query log. This function is written in PL/pgSQL which is enabled in target db, If I pg_dump the schema into a plain text file, I can see its defination there, I can easily copy paste (restore) it into mydb2. however, I failed to restore it by using flag -P with compressed file. I also tried to use su postgres -c /usr/local/pgsql/bin/pg_restore --function=myfunction --dbname=mydb2 dbf error msg pg_restore: [archiver] could not open input file: No such file or directory weird??? I use pg_restore -Rxt mytable -d mydb2 dbf have no such a problem, it works. Is any syntax error?? I am confused by documentation now! Is it a bug Thanks Jie Liang -Original Message- From: Jan Wieck [mailto:[EMAIL PROTECTED]] Sent: Friday, June 28, 2002 12:39 PM To: Jie Liang Cc: 'Bruce Momjian'; '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]' Subject: Re: [SQL] pg_restore cannot restore function Jie Liang wrote: I use pg_dump -Fc mydb dbf then I create another db by: createdb mydb2 I use pg_restore -P myfunction -d mydb2 dbf cannot restore myfunction into mydb2 why?? Good question. Is there any error message in the postmaster log? If the function is written in a procedural language, is that language enabled in the target database? If the function is written in the SQL language, do all underlying objects like tables and views exist? If it's a C language function, does the shared object containing the function exist at the expected location? Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] postgres7.2.1 upgrading
http://www.ca.postgresql.org/sitess.html says that: The current version of PostgreSQL is 7.2.1. NOTE: An initdb will only be required if upgrading from pre 7.2 So, if my current version is 7.2.0 and I want upgrade it to 7.2.1, what file should I download in order to get 'intidb'? if only the 'initdb' is required, then what is the upgrade procedure? I don't need to install whole 7.2.1 at all, do I. my guess is that: 1.shutdown the db; 2.copy initdb of 7.2.1 to the location of initdb of 7.2.0; 3.re_intialize db by running initdb; 4.re_start postmaster. However, no documentation says that. Anybody can tell me? Thanks! Jie Liang ---(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
[SQL] pg_restore cannot restore function
I use pg_dump -Fc mydb dbf then I create anpther db by: createdb mydb2 I use pg_restore -P myfunction -d mydb2 dbf cannot restore myfunction into mydb2 why?? Jie Liang ---(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
[SQL] FW: RESTORE A TABLE
I have serveral tables that they have more then 2 millions, I want dump they out from one server and then restore them back on another server every day, the questions are: 1. What is the fastest way to dump/restore my data, I am try to use: pg_dump -aRt mytable -Fc -f mytable dbname pg_restore -aRt mytable -d dbname mytable but this takes too long to restore. 2. Is any way to disable unique index checking when loading, then enable the index after restored? Jie Liang ---(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
Re: [SQL] batch file
Hi, I am not sure what's your question. However, you can list a bunch of SQL statements in a text file: e.g. myfile.sql which contains: select * from tablename where update tablename set ... where ... . then, you can just run it like: psql -q dbname myfile.sql Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Fri, 25 May 2001, Milen wrote: Hi all!, I want to execute several SQL statements one after another. Does anyone know if it is possible to create something like a batch file which contains all SQL commands I want. Best Regards: Milen ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] PL/Perl documentation ...
http://www.brasileiro.net/postgres/ Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Wed, 2 May 2001, Adam Walczykiewicz wrote: Is there available some more examples of writing functions in PL/Perl ? In standard documentation there is only just 2 examples. Thanks in advance for any help. Adam -- ---(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 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] random rows
How I can return random N rows from my select stmt? like: e.g. what my selectee is a set of 1000 rows, I want randomly pickup 100 of 1000. thanks. Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com ---(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] plpgsql
Roberto, Thanks for your help, I read the docs. I am still not entire sure, my problem is in my plpgsql function I used for rec in select ... for update loop update stmt end loop; I don't want above chosen rows be selected(i.e. I want them to be lock exclusively) by another user until transaction done. do I need an explicit LOCK stmt? can it be used in the plpgsql function?? Thanks again. Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Fri, 20 Apr 2001, Roberto Mello wrote: On Fri, Apr 20, 2001 at 04:58:02PM -0700, Jie Liang wrote: I 've a question about begin...end in plpgsql does sql stmts in begin end; will go one transaction? Read the documentation (programmer's guide). It's all there. It's to answer your questions that we take the time to write docs in the first place :) Short answer: everything in your function is executed in one transaction. BEGIN and END in PL/pgSQL are NOT the same as in the transaction semantics. -Roberto -- +| http://fslc.usu.edu USU Free Software GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer If at first you don't succeed, destroy all evidence that you tried. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(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
[ADMIN] select ... for update in plpgsql
Hi, I have a question about 'select ... for update'; according to the docs, clause 'for update' will lock selected rows, I believe it should be put into a begin; select ... for update; update ...; end; block. however, if I use it in a plpgsql function, do I need another pair of begin...end? or say begin...end in plpgsql can lock chosen rows until updated ALL ROWS? if not, do you have any suggestion? Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[SQL] plpgsql
I 've a question about begin...end in plpgsql does sql stmts in begin end; will go one transaction? i.e. begin...end have same meaning as sql stmts BEGIN...COMMIT?? if failed, transaction abort? if select..for update is used then another update stmt will wait on the same rows?? if begin...end in plpgsql connot have same functionality as sql, how can I ensure my sql stmts go one transaction?? thanks. Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Does pg_dump stable on v7.0
When I dump out my whole db with pg_dump -x dbname dbname.out
then when I reload them, one of creation always failed
msg is relation 'urlinfo' is not exist,
so I dump out scheme first -- pg_dump -x -a dbname dbname.out.s
reload them , samething happend, so I cut paste the definition of
this table, it's OK. then load data is OK.
but how come??
foollowing is this table:
CREATE TABLE "urlinfo" (
"url" text NOT NULL,
"id" int4 NOT NULL,
"ratedby" character varying(32),
"ratedon" timestamp DEFAULT "timestamp"('now'::text),
"comments" text,
"list" int2,
"pidwsr" int4,
CONSTRAINT "host_ck" CHECK ((urlpart('host'::text,
(url)::"varchar") '*.com'::text) AND (urlpart('host'::text,
(url)::"varchar") '*.net'::text)) AND (urlpart('host'::text,
(url)::"varchar") '*.gov'::text)) AND (urlpart('host'::text,
(url)::"varchar") '*.*'::text)) AND (urlpart('host'::text,
(url)::"varchar") '*'::text))),
PRIMARY KEY ("id")
);
Jie LIANG
St. Bernard Software
10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873
[EMAIL PROTECTED]
www.stbernard.com
www.ipinc.com
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] using for rec inside a function: behavior very slow
I have a function: CREATE FUNCTION hasdup(text) RETURNS int4 AS ' declare v_id int4; rat1 text; rat2 text; v_urltext; rec record; begin select id into v_id from urlinfo where url = $1; if NOT FOUND then return -1; end if; select codestr(v_id) into rat1; v_url:= $1||''%''; for rec in select id,url from urlinfo where url like v_url order by url loop raise notice ''%'',rec.url; select codestr(rec.id) into rat2; if rec.id v_id and rat1 = rat2 then update urlinfo set list = 1 where id = rec.id; return rec.id; end if; end loop; return 0; end; ' LANGUAGE 'plpgsql'; 'where url like clause' is very slow in inside the function, but when I directly use this statement in SQL, it is very quick, is any quick way to return match: where field like 'something%' inside the plsql function?? Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] select statement inside a function: behavior bad
I tested select statement inside sql and plpgsql function, very slow CREATE FUNCTION geturllike(text) RETURNS SETOF text AS ' SELECT url as url FROM urlinfo WHERE url LIKE $1; 'LANGUAGE 'sql'; CREATE FUNCTION hasdup(text) RETURNS int4 AS ' declare v_id int4; rat1 text; rat2 text; v_urltext; rec record; begin v_url:= $1||''%''; for rec in select id,url from urlinfo where url like v_url order by url loop raise notice ''%'',rec.url; end loop; return 0; end; ' LANGUAGE 'plpgsql'; Why so slow Is it a bug?? Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] About Raise Exception
At least raise exception will abort the transaction but raise notice does not. Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Mon, 26 Mar 2001, datactrl wrote: According to user guide, both Raise Notice Raise Exception will write message to database log. Which system table is the database log about? By the way what is the difference between Raise Notice Raise Exception? Jack ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] about raise exception
Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Mon, 26 Mar 2001, Jack wrote: According to user guide, both Raise Notice Raise Exception will write message to database log. Which system table is the database log about? I believe it means write a message to your log file such as /.../pgsql.log By the way what is the difference between Raise Notice Raise Exception? raise notice does not terminate program but raise exception does. Jack ---(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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] drop table in PL/pgSQL
You cannot CREATE|DROP ALTER table in PL/pgSQL, in general, plsql can only take DML(i.e. SELECT| INSERT|UPDATE..) Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Fri, 23 Mar 2001, datactrl wrote: When I use "drop Table ..." in PL/pgSQL, it always causes an error as "ERROR copyObject: don't know how to copy 614" JACK ---(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
Re: [SQL] SOME PL/PGSQL PROBLEMS
You blame something should not be blamed. Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Fri, 23 Mar 2001, datactrl wrote: I found there are some problems with PL/pgSQL. 1) 'Drop Table' doesn't work at all In general, PLSQL can only take DML instead of DDL. 2) '--' comment causes parsing error Not true. 3) Execute doesn't work at all. It always causes parsing error! I havn't tested, I cannot say. Some questions 1) Can I use variable in FROM clause for a table name? No, table name cannot be a variable except in execute statement. 2) Can I use variable in WHERE such as xxx = variable? Same as 1). JACK ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] drop table in PL/pgSQL
Hmm, I didn't know that, this general idea from Orcale plsql, So, I assume that you can SELECT somefield into a_new_table FROM a_old_table in pg 7.1??? Thank you. No DDL can be roll back. Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Fri, 23 Mar 2001, Roberto Mello wrote: On Fri, Mar 23, 2001 at 09:52:56AM -0800, Jie Liang wrote: You cannot CREATE|DROP ALTER table in PL/pgSQL, in general, plsql can only take DML(i.e. SELECT| INSERT|UPDATE..) You can't? I just did (on PG 7.1). AFAIK, you _can_ CREATE/DROP, but you can't roll back. -Roberto -- +| http://fslc.usu.edu USU Free Software GNU/Linux Club|--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Web Developer "Carrier detected." Go to the dentist... ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] pl/Perl
Tom, 1.Where or how I can get pltcl.so? I have not find this file anywhere in my source except a pltcl.c. 2.Dose installation same as plpgsql? i.e. CREATE FUNCTION pltcl_call_handler () RETURNS OPAQUE AS '/usr/local/pgsql/lib/pltcl.so' LANGUAGE 'C'; CREATE TRUSTED PROCEDURAL LANGUAGE 'pltcl' HANDLER pltcl_call_handler LANCOMPILER 'PL/pgtcl'; 3.Where I can find more doc about pltcl? Thanks for your time. Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Wed, 21 Feb 2001, Tom Lane wrote: Jie Liang [EMAIL PROTECTED] writes: My choice: if involving a lot of regular expressions, pl/Perl is better; if involving a lot of SQLs or other functions(or store procedures), then pl/pgsql is better. Also consider pltcl, which has pretty nearly perl-equivalent regexp support, and can do queries too. Besides which it's easier to build/ install than plperl. It's a shame that plperl doesn't yet have support for making queries. It hasn't really progressed much past the proof-of-concept stage IMHO, but no one is working on it :-( regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[ADMIN] how to reload a function
I have 2 plpgsql defined functions, say: create function A() returns ... as' . 'language 'plpgsql'; create function B() returns ... as ' declare begin select A(..) into myvar from end; 'language 'plpgsql'; If I modify function A (drop re_create), then I have to re_create function B though no change to function B. Is there any way (sql stmt) let me re_load function B's defination without using drop and create?? Thanks. Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Comparing dates
I think if you cast it then works. e.g. '02-03-2001'::date '02-03-2001'::timestamp Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Tue, 6 Mar 2001, Markus Fischer wrote: Hello, I've a SELECT statement on many joined Tabled and one of them has a date column called 'date_date'. When I fetch a date e.g. '02-03-2001', I get, say, 60 results back. When I now perform the same query with another date, lets take '03-03-2001', I get back about 70 results. When I now modify my query to get both results in one I write SELECT FROM .. AND date_date = '2001-03-02' AND date_date = '2001-03-03' AND I think I should get back the rows for both days, 60 + 70 makes 130 to me. But what I get back is even smaller then 60. I allready tried TO_DATE conversion, an OR construct but always the same result. Is there something special to know when comparing/working with date-datetypes ? kind regards, Markus -- Markus Fischer, http://josefine.ben.tuwien.ac.at/~mfischer/ EMail: [EMAIL PROTECTED] PGP Public Key: http://josefine.ben.tuwien.ac.at/~mfischer/C2272BD0.asc PGP Fingerprint: D3B0 DD4F E12B F911 3CE1 C2B5 D674 B445 C227 2BD0 ---(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 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Date question
you can say: (now() + '1year'::timespan)::date Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Tue, 6 Mar 2001, Boulat Khakimov wrote: Hi, Im a little bit stuck here. Does anyone know how to get date in format '-MM-DD' of a date one year from now. So for example today is '2001-03-06' I need to get date 12 months from now which will be '2002-03-06' in todays case... In mysql I used DATE_ADD(CURDATE(), INTERVAL 12 MONTH) , but that doesnt work in PG. Regards, Boulat Khakimov -- Nothing Like the Sun ---(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 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Urgent help
Tom, I think one of system file has been crupted. I tried to drop some user then db is hangged there forever, so kill that session, when I relogin and type urldb=# \z categories NOTICE: get_groname: group 2 not found The connection to the server was lost. Attempting reset: Failed. !# and new user added cannot retrive data from any table. what I can do?? Jie LIANG St. Bernard Software Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Wed, 21 Feb 2001, Tom Lane wrote: Jie Liang [EMAIL PROTECTED] writes: My choice: if involving a lot of regular expressions, pl/Perl is better; if involving a lot of SQLs or other functions(or store procedures), then pl/pgsql is better. Also consider pltcl, which has pretty nearly perl-equivalent regexp support, and can do queries too. Besides which it's easier to build/ install than plperl. It's a shame that plperl doesn't yet have support for making queries. It hasn't really progressed much past the proof-of-concept stage IMHO, but no one is working on it :-( regards, tom lane
Re: [SQL] Urgent help
another weired thing is, when I: select * from pg_group; it takes about 2 minites to return, it took just a tick before, it seems that some sys tables has been locked for a timeout. Thanks. Jie LIANG St. Bernard Software Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com
Re: [SQL] How can i escape a '+' or a '+' in a regexp ?
select field from table where field like '%\\%%' or field like '%*%'; select field from table where field ~ '.*\\*.*' or ~ '.*%.*'; Jie LIANG St. Bernard Software Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Fri, 23 Feb 2001, Gabriel Fernandez wrote: Hi fellows, I'm trying to the following query: select * from areas where titulo ~ '+' or titulo ~ '*' and the answer is: ERROR: regcomp failed with error repetition-operator operand invalid I have tried to escape the '+' and the '*' with a backslash, as follows: select * from areas where titulo ~ '\+' or titulo ~ '\*' but the answer is the same. If I use the LIKE operator, then I have the problem with '%' and '_' :-) As long as the values in the field can contain either '+' or '*' or '%' or '_' I need to escape these characters. How can i do it ? I'm using PostgreSQL 6.5.3 on Linux Red Hat 6.2. Thanks, Gabi :-)
Re: [SQL] Need your help
e.g. Try: CREATE TABLE emp ( id int4 primary key, empname text, salary int4, last_date datetime, last_user name); CREATE FUNCTION emp_stamp () RETURNS OPAQUE AS BEGIN update emp set last_date=''now''::timestamp where id=NEW.id; RETURN NEW; END; ' LANGUAGE 'plpgsql'; CREATE TRIGGER emp_stamp BEFORE UPDATE ON emp FOR EACH ROW EXECUTE PROCEDURE emp_stamp(); Jie LIANG St. Bernard Software Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Mon, 19 Feb 2001, Jyotsna Kypa wrote: Hi, I need your help on something. I have to write a trigger (in sybase) that does this: Everytime a record gets updated it should update a column in that record with the current date/time. I am able to do it for the whole table, but how do I make sure the update happens only for that record which is being updated? Please respond. Thanks a bunch, Jyotsna. __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/
Re: [SQL] Controlling Reuslts with Limit
My understanding: because you return a subset instead of a single value, so between 2 select ... limit ... queries. if you delete a record(say song_id=947) then insert it again. then results are different. So for a multiple users db, you should use oder by when you use limit. Jie LIANG St. Bernard Software Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Sat, 24 Feb 2001, Najm Hashmi wrote: Hi, I was reading through Bruce's on line . I found follwing bit unclear... "Notice that each query uses ORDER BY . Although this clause is not required, LIMIT without ORDER BY returns random rows from the query, which would be useless. " When I run a query several time I get the same results as given flipr=# select song_id from songs limit 5; song_id - 945 946 947 948 949 (5 rows) flipr=# select song_id from songs limit 5; song_id - 945 946 947 948 949 (5 rows) flipr=# select song_id from songs limit 5; song_id - 945 946 947 948 949 (5 rows) flipr=# select song_id from songs limit 5; song_id - 945 946 947 948 949 (5 rows) flipr=# select song_id from songs limit 5; song_id - 945 946 947 948 949 (5 rows) flipr=# select song_id from songs limit 5; song_id - 945 946 947 948 949 I just want to know what exatly --"LIMIT without ORDER BY returns random rows from the query" --means Regards
Re: [SQL] Can a function return a record set?
in plpgsql you've to use select field into a_variable from table where ...(single value return) or for record|row in select fields from table loop ... end loop; Jie LIANG St. Bernard Software Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Sat, 17 Feb 2001, John Taves wrote: Am I correct in concluding that I can't return a record set from a function? For example, in MS SQL I would do: create procedure foo as select * from yada I expected to be able to do the following in postgresql. create function foo (integer) returns (integer) as ' begin select * from yada; end; ' language 'plpgsql'; I am concluding that I can't. jt
Re: [SQL] pl/Perl
FYI, My choice: if involving a lot of regular expressions, pl/Perl is better; if involving a lot of SQLs or other functions(or store procedures), then pl/pgsql is better. Jie LIANG St. Bernard Software Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Wed, 21 Feb 2001, Jeff MacDonald wrote: 1: can you call other stored procedures from within pl/Perl No. darn. 2: from within a pl/Perl script , can i do a select etc.. i'm assuming no, because you cannot use DBI.. but just wondering if there is a way.. Not currently. darn. 3: installing it.. i installed postgres 7.0.3 from ports on my fbsd system. when i tried to install pl/perl i get this.. cd /usr/ports/databases/postgresql7/work/postgresql-7.0.2/src/pl/plperl/ perl Makefile.pl make Try using gmake instead of make (cd /usr/ports/devel/gmake, make) thanks ! works great. Jeff MacDonald, - PostgreSQL Inc| Hub.Org Networking Services [EMAIL PROTECTED]| [EMAIL PROTECTED] www.pgsql.com | www.hub.org 1-902-542-0713| 1-902-542-3657 - Facsimile : 1 902 542 5386 IRC Nick : bignose PGP Public Key : http://bignose.hub.org/public.txt
Re: [SQL] Datetime Query
Try:
SELECT request_no FROM request where status_code ='C' and
(completed_date::date
between '01/01/2000'::date and '01/01/2001'::date)
actually date('01/01/2000') does same thing as '01/01/2000'::date
Jie LIANG
St. Bernard Software
Internet Products Inc.
10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873
[EMAIL PROTECTED]
www.stbernard.com
www.ipinc.com
On Thu, 15 Feb 2001, Mark Byerley wrote:
I need to create a query which will select a request_no between Data1 and
Date2 so...
SELECT request_no FROM request where status_code ='C' and (completed_date
between 01/01/2000 and 01/01/2001);
The problem I have run into is that the completed_date field is a datetime
format (not by my own design) and I am having some problems extracting just
the request_no's between those dates.
I have tried a few extract month,day,year clauses with no success. If
anyone has an idea I would appreciate it!
Thanks in advance.
Mark
Re: [SQL] Help Retrieving Latest Record
Subquery will do: select * from basket where Date in (select max(Date) from basket); Jie LIANG St. Bernard Software Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Fri, 16 Feb 2001, Steve Meynell wrote: Hi, I am not sure if this went through the first time so here it is again. Ok what I am trying to do is select out of the database the latest record meeting a certain criteria. Example: Number |Fruit | Date 15Apples July 20, 1999 20OrangesJune 7, 2000 13 PearsJan 31, 2000 17 Apples April 10, 1999 Now what I need to do is select the oranges out because the date is the latest one, something like: select * from basket where max(date); This would yield me: 20OrangesJune 7, 2000 I know this doesn't work but I need something like it. or something like select * from basket where max(date) and fruit='Apples'; This would yield me: 15Apples July 20, 1999 Thank you in advance, -- Steve Meynell Candata Systems
Re: [SQL] constraint/restrict
add an foriegn key on address(country_id), let country(id) be a primary key. Jie LIANG St. Bernard Software Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Wed, 14 Feb 2001, Olaf Marc Zanger wrote: hi there, with two tables i want to make some constraint-restrictions create table address ( id serial, country_id int4, ); and create table country (id serial, ...); to make sure that now country-row is deleted if there is still a country_id in address table. e.g. address: 1, 2, ... country: 2, ... now country wouldn't be allowed to be deleted. how to do that? thanks fo help olaf -- soli-con Engineering Zanger, Dipl.-Ing. (FH) Olaf Marc Zanger Lorrainestrasse 23, 3013 Bern / Switzerland fon:+41-31-332 9782, mob:+41-76-572 9782 mailto:[EMAIL PROTECTED], http://www.soli-con.com
Re: [SQL] createuser problem
run this as user 'postgres' instead of 'fion' Jie LIANG St. Bernard Software Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Tue, 13 Feb 2001, fion yong wrote: It gives the following error when i tried to a new user createuser demouser1 Connection to database 'template1' failed. FATAL 1: SetUserId: user 'fion' is not in 'pg_shadow' how should i solve this problem?
Re: [SQL] How to create a type ?
hope it helps. e.g. create function foo() returns setof varchar as ' select name from categories ' language 'sql'; db=# select foo() as name; name - recreation business web education questionable sex social society weapons/bombs mature humor pornography tasteless computer hacking nudity drugs lingerie/bikini profanity ... Jie LIANG St. Bernard Software Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Tue, 13 Feb 2001, Ines Klimann wrote: Hello, I am trying to understand how works CREATE TYPE, but it seems to be too difficult for me... Can someone help me ? I have tried this : -- create function personne_in(text) returns personne as 'select $1;' language 'sql'; create function personne_out(text) returns text as 'select $1;' language 'sql'; create type personne ( internallength = variable, input = personne_in, output = personne_out ); - but it is full of mistakes. Even a simple example in language 'sql' will help me. Thanks, Ines.
Re: [SQL] pgsql and cursor
I just know you can use implict cursor inside the plpgsql e.g declare rec record; begin FOR rec IN select_clause LOOP statements END LOOP; end; Jie LIANG St. Bernard Software Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com On Sat, 10 Feb 2001, Najm Hashmi wrote: Hi all, I am not sure If it is allowed to use cursor inside pgsql functions. If it is possible, please someone could send exact synatx how it is used. If it is not allowed is there a way arround it? I need to do some calculations and then return this value as text. Thanks in advance for all your help. Najm
Re: [SQL] What's wrong with this function
I just know you can use implict cursor inside the plpgsql e.g declare rec record; begin FOR rec IN select_clause LOOP statements END LOOP; end; Jie LIANG St Bernard Software Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com On Sat, 10 Feb 2001, Najm Hashmi wrote: Hi all, Here is a plpgsql function: flipr'#create function test_cur() returns text as' flipr'# declare flipr'# mycur cursor for select title from songs where song_id=10; flipr'# usrrecord; flipr'# resultstext; flipr'# begin flipr'# open mycur; flipr'# fetch next from mycur into usr; flipr'# close mycur; flipr'# results:= usr.title; flipr'# flipr'# end; flipr'# ' language 'plpgsql'; CREATE flipr=# select test_cur() as Title; NOTICE: plpgsql: ERROR during compile of test_cur near line 2 ERROR: parse error at or near "cursor" What I am doing wrong? Thanks in advance for your help. Regards, Najm
Re: [SQL] What's wrong with this function
Try:
create function foo(text) returns int4 as '
delcare
tcount int4:=0;
begin
for rec IN select title, dcount from songs where artist=$1 LOOP
tcount:= tcount+rec.dcount;
END LOOP;
return tcount;
end;
' language 'plpgsql';
call it by:
db select foo('Najm Hashmi');
it will return how many songs of 'Najm Hashmi' in your database.
Jie LIANG
St. Bernard Software
Internet Products Inc.
10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873
[EMAIL PROTECTED]
www.stbernard.com
www.ipinc.com
On Sat, 10 Feb 2001, Najm Hashmi wrote:
Jie Liang wrote:
I just know you can use implict cursor inside the plpgsql
e.g
declare
result text;
tcount int4;
rec record;
begin
FOR rec IN select_clause LOOP
statements
END LOOP;
end;
Thank you Jie for your help. I am bit confused about how it works. I want for
each row , obtained by select statment, get certain values and then do some
calculations and out put that resulst eg
for rec IN select title, dcount from songs where artist='xyz'
tcount:= tcount+rec.dcount;
END LOOP;
return tcount;
would this work ?
Thanks again for your help.
Regards, Najm
Re: [SQL] String Concatnation
Hi, You can use every sql function and operator in plpgsql, so v||''|''||v2 is OK. however, you cannot do: declare v,v2 text; you should do: v text; v2 text; also you initialize like: v text:=; Jie LIANG St. Bernard Software Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.stbernard.com www.ipinc.com On Sat, 10 Feb 2001, Najm Hashmi wrote: Hi, How can I concatnate two varialbles, seperated by a |, that are type text together? v, v1 text; some work then res:= v ||''|''|| v1; this syntex does not work in plpgsql?? Any ideas how to do it ??? Thanks. Najm
Re: [SQL] Use of RETURN in pl/pgsql function
You may try like: if block end if; return somefakething; no matter this return can be reached or not. then compile will be no problem. Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com On Wed, 7 Feb 2001, Jeff Eckermann wrote: My script is below. I thought (based on recent posts) that this use of RETURN is allowed, but when trying an insert to report_table, I get the following error: ERROR: control reaches end of trigger procedure without RETURN I have solved several problems in getting to this point, but have now run out of ideas. I would appreciate any pointers. jeffe@kiyoko= uname -a FreeBSD kiyoko.la.verio.net 4.0-STABLE FreeBSD 4.0-STABLE #0: Thu Apr 27 10:44:07 CDT 2000 jeffe@kiyoko= psql -V psql (PostgreSQL) 7.0.0 Script: drop function mrr(); create function mrr() returns opaque as ' begin if NEW.billing_frequency = ''Monthly'' -- That's doubled single quotes (and below as well) then return NEW; else if NEW.billing_frequency = ''Yearly'' then NEW.rate := NEW.rate/12; NEW.rate_override := NEW.rate_override/12; return NEW; else if NEW.billing_frequency = ''Semi-Annual'' then NEW.rate := NEW.rate/6; NEW.rate_override := NEW.rate_override/6; return NEW; else if NEW.billing_frequency = ''Quarterly'' then NEW.rate := NEW.rate/3; NEW.rate_override := NEW.rate_override/3; return NEW; end if; end if; end if; end if; end; 'language 'plpgsql'; drop trigger mrr_set_trigger on report_table; create trigger mrr_set_trigger before insert on report_table for each row execute procedure mrr();
Re: [SQL] Search
Hi, You seem want to match string insensitively, I guess. Try: ~* 'test' -- match Test|tEst|tESt ... ~* '.*test.*' -- match whateverTesTwhatever Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com On Mon, 5 Feb 2001, Sebastian --[ www.flashhilfe.de ]-- wrote: Hi I hope someone can help me My problem: I have make a search machine whit: LIKE '%$suchbegriffe[$i]%' but when I search Test - the search machine shows only entries whit Test. But not test or tESt. (sorry for my bad english) Regards, Sebastian
Re: [SQL] interval query.
Hi, where id= or id between 3 and 12; or where id in (3,4,5,6,7,8,9,10,11,12,); Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com On Wed, 31 Jan 2001, Antti Linno wrote: Good morning. Is there some way to make interval query? Towns table(estonia towns, heh :P) id | name 1 Elva 2 Tartu Tallinn 3 Tallinn/Haabersti 4 Tallinn/Mustamae ... etc. What I need is when the town's id= I want to make query where id= OR id=[3..12] for example. I could generate bunch of OR's like id=3 OR id=4 ... but is there some more elegant solution? Greetings, Antti
Re: [SQL] Array as parameter in plpgSQL functions
e.g.
create function foo(_int4) returns int2 as'
declare
a _int4 alias for $1;
i int:=1;
begin
while a[i] loop
i:=i+1;
end loop;
return i-1;
end;
' language 'plpgsql';
you can call it by:
select foo('{1232131,12312321,3424234}');
you should get 3.
Jie LIANG
Internet Products Inc.
10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873
[EMAIL PROTECTED]
www.ipinc.com
On Wed, 31 Jan 2001, Sveinung Haslestad wrote:
I need to pass an array to a function ( int4, variable number of elements) .
How do i declare the parameter, and how can i tell the numer of recieved elements?
Thanks
/Sveinung
Re: [SQL] ' in SQL INSERT statement
Hi,
Using a backslash to escape it.
insert into table(field) values('what\'s that');
Jie LIANG
Internet Products Inc.
10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873
[EMAIL PROTECTED]
www.ipinc.com
On Thu, 25 Jan 2001, Markus Wagner wrote:
Hi,
I have some data that I wish to transfer into a database using perl/DBI.
Some of the data are strings containing the apostrophe "'" which I use
as string delimiter.
How can I put these into my database using the INSERT statement?
Thanks,
Markus
Re: [SQL] How to change the ownership of the table?
Hi, there, I believe that you can use iat least: pg_dump -t table -f out dbname then vi out change the owner (first line -- connnect ...) then drop the old table and reload new table by psql dbname out If your table have no index or any constraint, you can use SELECT * into newtable then if you have the previlage. Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com On Wed, 24 Jan 2001, Ramesh H R wrote: Hai Please, any one guide me how to change the ownership of the table? Regards, -- Ramesh HR Trainee Engineer EASi Technologies 213, 3rd Main, 4th Cross Chamrajpet, Bangalore - 560 018 India Ph.: 660 1086 / 660 2365 / 667 2984 Extn.: 155 Facsimile: 667 5274 www.easi.soft.net
[SQL] hex number
Hi, Does anybody knows that is any function can covert an inet(IP addr) type to a hex number?? Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
Re: [SQL] Create table doesn't work in plpgsql
Hi,there,
I don't think you can use DDL(data definition language) in PL/SQL.
create table is not DML(data munipulation language) instead
it's a DDL.
Jie LIANG
Internet Products Inc.
10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873
[EMAIL PROTECTED]
www.ipinc.com
On Tue, 19 Dec 2000, Volker Paul wrote:
Hi,
can I do some table manipulation in plpgsql?
Look at only the "create table" line and the error message:
create function plural (text) returns text as '
begin
create table tmp (num int4);
return $1 || ''s'';
end;' language 'plpgsql';
select plural('test');
CREATE
ERROR: copyObject: don't know how to copy 611
What does the error message mean? Where can I read more about it?
Cheers, Volker
Re: [SQL] `~' operator and indices
Hi, there, Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com On Wed, 20 Dec 2000, Artur Rataj wrote: Hello, I would like to ask you why do `~' gives the following results, if there is an index on `string': select string from indextbk_fti_fkey where string ~ '^IE'; === try this: where string ~ '^IE.*'; string (0 rows) select string from indextbk_fti_fkey where string ~ '^IECIA'; string IECIA (1 row) `E' here is a polish letter. I have set locale to `pl_PL' before starting postgres. Best regards Artur Rataj
Re: [SQL] How to set autocommit on/off
Hi, there, I think you can use : BEGIN;-- turn off any DDL stmts -- you can rollback them by ROLLBACK; END|COMMIT; -- turn on Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com On Wed, 20 Dec 2000, Kevin wrote: As titled, is autocommit of Postgresql is default to be "ON", how to turn it off then? Thnaks, Kevin -- - Kevin LAM, System Analyst Crown Development Ltd. A Shun Tak Group Company Tel: (852) 2283-2132 Fax:(852) 2283-2727 -
Re: [SQL] SQL query not working when GROUP BY / HAVING is used
I hope it may help: 1. if you use group clause in a select stmt, the select list must be agregate function such as sum(field),count(field), max(field)..., cannot use field. 2. for field have NULL field, should use field IS NULL, = NULL will give you wrong result! Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com On Tue, 19 Dec 2000 [EMAIL PROTECTED] wrote: Hello there I have a question regarding a SQL statement. When I execute (and that's what I need) SELECT Zylinder.Z_durch_soll, Zylinder.Z_id, Zylinder.Z_durch_ist, ((Zylinder.Z_durch_soll+0.12) - Zylinder.Z_durch_ist) AS durchmesserdelta, (Zylinder.Z_durch_soll+0.12) AS effektiv FROM Auftrag,Zylinder_Typen, Zylinder WHERE Auftrag.A_nr = '11' AND ( Auftrag.A_farbe_1 '0' AND Zylinder_Typen.Z_farbe='1' AND Zylinder_Typen.Z_SW='0') AND Zylinder_Typen.Z_durch_soll = Zylinder.Z_durch_soll AND Auftrag.A_Ztyp=Zylinder.Z_typ AND Z_A_nr = NULL AND Z_status = 'zcu' GROUP BY Zylinder.Z_durch_soll HAVING durchmesserdelta = 0.085 ORDER BY Zylinder_Typen.Z_durch_soll desc I get the following error in the pgadmin.log file. 19.12.00 10:53:34 Executing: SELECT Zylinder.Z_durch_soll, Zylinder.Z_id, Zylinder.Z_durch_ist, ((Zylinder.Z_durch_soll+0.12) - Zylinder.Z_durch_ist) AS durchmesserdelta, (Zylinder.Z_durch_soll+0.12) AS effektiv FROM Auftrag,Zylinder_Typen, Zylinder WHERE Auftrag.A_nr = '11' AND ( Auftrag.A_farbe_1 '0' AND Zylinder_Typen.Z_farbe='1' AND Zylinder_Typen.Z_SW='0') AND Zylinder_Typen.Z_durch_soll = Zylinder.Z_durch_soll AND Auftrag.A_Ztyp=Zylinder.Z_typ AND Z_A_nr = NULL AND Z_status = 'zcu' GROUP BY Zylinder.Z_durch_soll HAVING durchmesserdelta = 0.085 ORDER BY Zylinder_Typen.Z_durch_soll desc 19.12.00 10:53:34 Executing SQL Query... 19.12.00 10:53:34 Done - 0,01 Secs. ** * Error - 19.12.00 10:53:34 ** Software Program: pgAdmin Version: 7.0.4 Sub or Function: frmSQL, cmdExecute_Click Error Details * Error No: -2147217887 Error Description: Der ODBC-Treiber unterstützt die angeforderten Eigenschaften nicht. Error Source: Microsoft OLE DB Provider for ODBC Drivers DLL Error Code: 0 Memory Details ** Total Physical: 132435968 Total Swap: 434098176 Total Virtual: 2147352576 Available Physical: 34004992 Available Swap: 291512320 Available Virtual: 2079350784 Percentage Free: 0 System Details ** Processor: 586 OEM ID: 0 No. Processors: 1 Page Size: 4096 OS Details ** Platform: Windows NT Version: 4.0 Build: 1381 OS Info: Service Pack 5 Environment Details *** Datasource: pgmondadori Tracking: False TrackVer: 0 Connect: Provider=MSDASQL.1;Extended Properties ="DSN=pgmondadori;DATABASE=mondadori;SERVER=srvlnx01.pup.ch;PORT=5432;UID=postgres;PWD **;READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=1;SHOWOIDCOLUMN=1;ROWVERSIONING=0;SHOWSYSTEMTABLES=0;CONNSETTINGS " Version: 2.6 Using the same statement without the GROUP BY and HAVING it is ok ! SELECT Zylinder.Z_durch_soll, Zylinder.Z_id, Zylinder.Z_durch_ist, ((Zylinder.Z_durch_soll+0.12) - Zylinder.Z_durch_ist) AS durchmesserdelta, (Zylinder.Z_durch_soll+0.12) AS effektiv FROM Auftrag,Zylinder_Typen, Zylinder WHERE Auftrag.A_nr = '11' AND (Auftrag.A_farbe_1 '0' AND Zylinder_Typen.Z_farbe='1' AND Zylinder_Typen.Z_SW='0') AND Zylinder_Typen.Z_durch_soll = Zylinder.Z_durch_soll ANDAuftrag.A_Ztyp=Zylinder.Z_typ ANDZ_A_nr =NULL ANDZ_status = 'zcu' ORDER BY Zylinder_Typen.Z_durch_soll desc Whats wrong, according to the docs, the syntax is ok and it should be possible to use these keywords! PFISTER + PARTNER, SYSTEM - ENGINEERING AG Juerg Rietmann Grundstrasse 22a 6343 Rotkreuz Switzerland phone: +4141 790 4040 fax: +4141 790 2545 mobile: +4179 211 0315
Re: [SQL] SQL query not working when GROUP BY / HAVING is used
Hope it helps: 1. If you use GROUP, the select list should sum|count|max ..., no single field. 2. If you use NULL, the condition should be field IS [NOT] NULL, = NULL will give the wrong answer. Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com On Tue, 19 Dec 2000 [EMAIL PROTECTED] wrote: Hello there I have a question regarding a SQL statement. When I execute (and that's what I need) SELECT Zylinder.Z_durch_soll, Zylinder.Z_id, Zylinder.Z_durch_ist, ((Zylinder.Z_durch_soll+0.12) - Zylinder.Z_durch_ist) AS durchmesserdelta, (Zylinder.Z_durch_soll+0.12) AS effektiv FROM Auftrag,Zylinder_Typen, Zylinder WHERE Auftrag.A_nr = '11' AND ( Auftrag.A_farbe_1 '0' AND Zylinder_Typen.Z_farbe='1' AND Zylinder_Typen.Z_SW='0') AND Zylinder_Typen.Z_durch_soll = Zylinder.Z_durch_soll AND Auftrag.A_Ztyp=Zylinder.Z_typ AND Z_A_nr = NULL AND Z_status = 'zcu' GROUP BY Zylinder.Z_durch_soll HAVING durchmesserdelta = 0.085 ORDER BY Zylinder_Typen.Z_durch_soll desc I get the following error in the pgadmin.log file. 19.12.00 10:53:34 Executing: SELECT Zylinder.Z_durch_soll, Zylinder.Z_id, Zylinder.Z_durch_ist, ((Zylinder.Z_durch_soll+0.12) - Zylinder.Z_durch_ist) AS durchmesserdelta, (Zylinder.Z_durch_soll+0.12) AS effektiv FROM Auftrag,Zylinder_Typen, Zylinder WHERE Auftrag.A_nr = '11' AND ( Auftrag.A_farbe_1 '0' AND Zylinder_Typen.Z_farbe='1' AND Zylinder_Typen.Z_SW='0') AND Zylinder_Typen.Z_durch_soll = Zylinder.Z_durch_soll AND Auftrag.A_Ztyp=Zylinder.Z_typ AND Z_A_nr = NULL AND Z_status = 'zcu' GROUP BY Zylinder.Z_durch_soll HAVING durchmesserdelta = 0.085 ORDER BY Zylinder_Typen.Z_durch_soll desc 19.12.00 10:53:34 Executing SQL Query... 19.12.00 10:53:34 Done - 0,01 Secs. ** * Error - 19.12.00 10:53:34 ** Software Program: pgAdmin Version: 7.0.4 Sub or Function: frmSQL, cmdExecute_Click Error Details * Error No: -2147217887 Error Description: Der ODBC-Treiber unterstützt die angeforderten Eigenschaften nicht. Error Source: Microsoft OLE DB Provider for ODBC Drivers DLL Error Code: 0 Memory Details ** Total Physical: 132435968 Total Swap: 434098176 Total Virtual: 2147352576 Available Physical: 34004992 Available Swap: 291512320 Available Virtual: 2079350784 Percentage Free: 0 System Details ** Processor: 586 OEM ID: 0 No. Processors: 1 Page Size: 4096 OS Details ** Platform: Windows NT Version: 4.0 Build: 1381 OS Info: Service Pack 5 Environment Details *** Datasource: pgmondadori Tracking: False TrackVer: 0 Connect: Provider=MSDASQL.1;Extended Properties ="DSN=pgmondadori;DATABASE=mondadori;SERVER=srvlnx01.pup.ch;PORT=5432;UID=postgres;PWD **;READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=1;SHOWOIDCOLUMN=1;ROWVERSIONING=0;SHOWSYSTEMTABLES=0;CONNSETTINGS " Version: 2.6 Using the same statement without the GROUP BY and HAVING it is ok ! SELECT Zylinder.Z_durch_soll, Zylinder.Z_id, Zylinder.Z_durch_ist, ((Zylinder.Z_durch_soll+0.12) - Zylinder.Z_durch_ist) AS durchmesserdelta, (Zylinder.Z_durch_soll+0.12) AS effektiv FROM Auftrag,Zylinder_Typen, Zylinder WHERE Auftrag.A_nr = '11' AND (Auftrag.A_farbe_1 '0' AND Zylinder_Typen.Z_farbe='1' AND Zylinder_Typen.Z_SW='0') AND Zylinder_Typen.Z_durch_soll = Zylinder.Z_durch_soll ANDAuftrag.A_Ztyp=Zylinder.Z_typ ANDZ_A_nr =NULL ANDZ_status = 'zcu' ORDER BY Zylinder_Typen.Z_durch_soll desc Whats wrong, according to the docs, the syntax is ok and it should be possible to use these keywords! PFISTER + PARTNER, SYSTEM - ENGINEERING AG Juerg Rietmann Grundstrasse 22a 6343 Rotkreuz Switzerland phone: +4141 790 4040 fax: +4141 790 2545 mobile: +4179 211 0315
Re: [SQL] question on SELECT
use: \dt -- all tables \dv -- all views \df -- all functions ... Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com On 19 Dec 2000, Prasanth A. Kumar wrote: Howard Hiew [EMAIL PROTECTED] writes: Hi, I would like to know what is the sql statement that list all the tables name. For example in Oracle, 'SELECT TABLE_NAME from ALL_TABLES where owner="Oracle" '; What is the statement for Postgres? Thank you Best Regards, Howard CIM/MASTEC Tel:(65)8605283 You can do '\dt' to list all tables. There is also a system table 'pg_tables' which you can use if you like to do a select instead. Do SELECT tablename FROM pg_tables where tableowner='postgres'; -- Prasanth Kumar [EMAIL PROTECTED]
Re: [SQL] Problem with function...
Hi, there, modify the code as following. Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com On Mon, 18 Dec 2000 [EMAIL PROTECTED] wrote: Hi, I hope my question is appropriate for this list. I'm trying to create a function that calculates the distance between a pair of latitude/longitude points. This is what I have: /* latlon_distance.pgsql * by Jamu Kakar [EMAIL PROTECTED], Dec 18, 2000. * * Calculates the distance between 2 lat/lon pairs. Syntax: * distance (lat1, lon1, lat2, lon2) where parameters are in decimal degrees. */ CREATE FUNCTION distance (float8, float8, float8, float8) RETURNS float8 AS ' DECLARE radius constant float8 := 6378; distance float8; lat1 ALIAS FOR $1; lon1 ALIAS FOR $2; lat2 ALIAS FOR $3; lon2 ALIAS FOR $4; BEGIN distance := radius * acos ((sin (radians (lat1)) * sin (radians (lat2))) + (cos (radians (lat1)) * cos (radians (lat2)) * cos (radians (lon1) - radians (lon2; -- RETURN ''distance'';-- here means text instead of float8 which -- you defined. === RETURN distance; END; ' LANGUAGE 'plpgsql'; When I try a: select distance(49.0,-122.0,50.0,-123.0) as distance; I get: ERROR: Bad float8 input format 'distance' I've tried a variety of ways of specifying the values and I've hunted through the mailing lists but haven't turned up anything useful. Any help would be appreciated. Thanks, Jamu. -- Jamu Kakar (Developer)Expressus Design Studio, Inc. [EMAIL PROTECTED] 708-1641 Lonsdale Avenue V: (604) 988-6999 North Vancouver, BC, V7M 2J5
[SQL] plpgsql
Hi, How can I declare an array in plpgsql?? when I use declare url text[10]; ERROR: parse error at or near "[" if I use _text; declare is OK, however, when I assgin a value after BEGIN url[i]:=whatever; get same ERROR, Is it possible to return an array from a plpgsql function?? Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
Re: [SQL] plpgsql
OO, That's a big disadvantage, because if the table is huge, using select stmt walking even on an index will take some time and duplicate occur not often, efficiency is a big problem. Thanks anyway. Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com On Tue, 12 Dec 2000, Kovacs Zoltan Sandor wrote: Hi, there, Is there any way to handle exception ( such as cannot insert duplicate key on a unique index) in plpgsql function? I don't want it abort whole transaction instead I want to do something else if it happened, but I don't want to use a select stmt first to waste the time. Bad news: there is no such statement in PLPGSQL you like. My usual way to do this is the same you wrote (SELECT first, if no rows FOUND, do the INSERT). Zoltan
[SQL] select ... for update
Hi,
How can I use select ... for update to update limit to update what I
select??
somewhat like:
select url,id from mytable for update order by priority,id limit 5;
I want update the id in above return like:
update mytable set allocatedto='whatever' where id in above return set.
Could I do it in one stmt.
And what is class_name in following:
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
expression [ AS name ] [, ...]
[ INTO [ TEMPORARY | TEMP ] [ TABLE ] new_table ]
[ FROM table [ alias ] [, ...] ]
[ WHERE condition ]
[ GROUP BY column [, ...] ]
[ HAVING condition [, ...] ]
[ { UNION [ ALL ] | INTERSECT | EXCEPT } select ]
[ ORDER BY column [ ASC | DESC | USING operator ] [, ...] ]
[ FOR UPDATE [ OF class_name [, ...] ] ]
LIMIT { count | ALL } [ { OFFSET | , } start ]
can any one give me a example??
--
Jie LIANG
Internet Products Inc.
10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873
[EMAIL PROTECTED]
www.ipinc.com
Re: [SQL] how to execute a C program via trigger ?
Hi, Is any other SQL implicit cursor attribute in PL/plsql ?? when you say (in pl/plsql): select field into v_1 from atable where whatever; special variable FOUND can be used to tell return is null or not. this functions like SQL%FOUND or SQL%NOTFOUND in Oracle, however, when I do some DML(insert,delete,update), is there any other special variable can tell me howmany success. Like SQL%ROWCOUNT in Orcale?? And if there is an error such as : cannot insert since duplicate key on an unique index, is it possible to catch it?? Thanks. -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
[SQL] need help urgent
Hi,
Is anybody have experience about core dump?
We have some scripts have been used for a long time, however, recently,
they seem to have some problems,
the message I got:
SSELECT priority from priority where source=lower('questionable')
Total insert into preunchecked: 1
Segmentation fault - core dumped
Segmentation fault - core dumped
This script has been used for a few monthes, no problem.
I checked my pgsql.log, I saw:
Nov 3 00:58:45 wipeout postgres[1638]: query: INSERT INTO
source(id,source) SELECT id,'questionable' from
preunchecked WHERE insertdate= '2000-11
-03 00:58:45-08'
Nov 3 00:58:46 wipeout postgres[1642]: query: SELECT version()
Nov 3 00:58:46 wipeout postgres[1642]: query: begin
Nov 3 00:58:46 wipeout postgres[1642]: ProcessUtility: begin
Nov 3 00:58:46 wipeout postgres[1642]: query: set transaction isolation
level serializable
Nov 3 00:58:46 wipeout postgres[1642]: ProcessUtility: set transaction
isolation level serializable
Nov 3 00:58:46 wipeout postgres[1642]: query: SELECT oid from
pg_database where datname = 'template1'
Nov 3 00:58:46 wipeout postgres[1642]: query: SELECT pg_type.oid,
typowner, typname, typlen, typprtlen, typinput, typoutput, typreceive,
typsend, typelem, typdeli
m, typdefault, typrelid, typbyval, usename from pg_type, pg_user where
typowner = usesysid
Nov 3 00:58:46 wipeout postgres[1642]: query: SELECT pg_proc.oid,
proname, prolang, pronargs, prorettype, proretset, proargtypes, prosrc,
probin, usename from pg_
proc, pg_user where pg_proc.oid '17216'::oid and proowner = usesysid
Nov 3 00:58:46 wipeout postgres[1642]: query: SELECT pg_aggregate.oid,
aggname, aggtransfn1, aggtransfn2, aggfinalfn, aggtranstype1,
aggbasetype, aggtranstype2, a
gginitval1, agginitval2, usename from pg_aggregate, pg_user where
aggowner = usesysid
We have no script to open a new session[1642], how this happen? how I
to prevent this??
Thanks!
--
Jie LIANG
Internet Products Inc.
10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873
[EMAIL PROTECTED]
www.ipinc.com
Re: [SQL] Problem whith Stored queries
Hi, anybody know how to call shell command in postgres rule or trigger, urgent!! -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
[SQL] plperl
hi, I followed README tried to install plperl: su-2.04# cd /work/src/pgsql702/src/pl/plperl su-2.04# perl Makefile.PL Writing Makefile for plperl su-2.04# make "../../../src/Makefile.global", line 135: Need an operator "../../../src/Makefile.global", line 139: Missing dependency operator "../../../src/Makefile.global", line 143: Need an operator "../../../src/Makefile.global", line 144: Missing dependency operator "../../../src/Makefile.global", line 148: Need an operator "../../../src/Makefile.global", line 149: Need an operator "../../../src/Makefile.global", line 150: Need an operator "../../../src/Makefile.port", line 1: Need an operator "../../../src/Makefile.port", line 3: Need an operator "../../../src/Makefile.port", line 6: Need an operator "../../../src/Makefile.port", line 8: Need an operator "../../../src/Makefile.port", line 16: Need an operator "../../../src/Makefile.global", line 246: Missing dependency operator "../../../src/Makefile.global", line 247: Could not find ../../../src/Makefile.custom "../../../src/Makefile.global", line 248: Need an operator "../../../src/Makefile.global", line 253: Missing dependency operator "../../../src/Makefile.global", line 255: Need an operator "../../../src/Makefile.global", line 284: Missing dependency operator "../../../src/Makefile.global", line 286: Need an operator "../../../src/Makefile.global", line 288: Missing dependency operator "../../../src/Makefile.global", line 290: Need an operator "../../../src/Makefile.global", line 292: Missing dependency operator "../../../src/Makefile.global", line 294: Need an operator "../../../src/Makefile.global", line 296: Need an operator "../../../src/Makefile.global", line 299: Need an operator "../../../src/Makefile.global", line 301: Need an operator "../../../src/Makefile.global", line 304: Need an operator make: fatal errors encountered -- cannot continue su-2.04# what I need to do? -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
Re: [SQL] COUNT
Hi, there, You want how many rows in your table??? select count(*) from yourtablename; Craig May wrote: Hi, How do I get a row count, like "Select [COUNT] from Table" ?? Regards, Craig May Enth Dimension http://www.enthdimension.com.au -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
Re: [SQL] if else query help
Hey, there, This is very interesting. I have similar problem: I want drop some junky table in my database, how can I detect a table when last time it is used. I try to say that I want to know how long this table has NOT been used at all. I don't which system table holds this statistics. Josh Berkus wrote: Brian, Jean-Christophe, Someone corrects me if I'm wrong, I come from the Oracle world... Dates (or I should say TimeStamps) are stored as floating point values : the integer part is the number of days since a certain date (epoch or 01/01/1970 on unix-based databases) and the fractionnal part is the portion of the day (although I don't know --yet-- how to convert date2-date1 to an integer, trunc does not work). You're doing this the hard way. One of Postgres' best features is its rich collection of date-manipulation functions. Please see: ... H. The online docs appear to be down. When they're back up, please check the sections on: Date/Time data types, and Date/Time manipulation functions. -Josh Berkus P.S. Brian, a general tutorial on writing SQL, such as O'Reilly's soon-to-be released SQL book, might help you a great deal. -- __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 436-9166 for law firms, small businesses fax 436-0137 and non-profit organizations. pager 338-4078 San Francisco -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
Re: [SQL] copying/moving from one table to another
Hi, there, I do not quite sure is this what you want? 1. if newtable(one you want save) does not exist: select * into newtable from oldtable; or create table newtable as select * from oldtable; 2. if newtable existed, you append some record into it: insert into newtable select * from oldtable where clause; Joachim Trinkwitz wrote: Hi all, is there a handy way to copy or (preferrably) move a whole record from one table to another, equally structured table in the same DB? Background: I have some tables which hold information concerning our staff, where people quite so often come and go, because their employment contract is limited to a year or two. When deleting a person in the DB, I don't want to lose this information completely, but I want to save it in a backup table. I suppose this is a newbie question, maybe I am stirring me blind somewhere ... Greetings and thanks, jaochim -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
Re: [SQL] Data Type precision
Hi, there, You can use function: urldb=# select round(234.356534,2); round 234.36 (1 row) Jerome Raupach wrote: CREATE TABLE TR (f1 FLOAT4, f2 INT4, f3 INT4) ; UPDATE TR SET f1=f2/f3::FLOAT4 ; f1 - xx,xx - but I want f1 - xx,xx. (6,6) (6,2) anybody can help me ? Thanks. Jerome. -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
Re: [SQL] select
Hi, there, only possible is null, so select blah from tableblah where field is null; Jeff MacDonald wrote: how would i select all rows where a boolean value is neither t nor f.. ? ie if someone inserted without setting the boolean tag. Jeff MacDonald, - PostgreSQL Inc | Hub.Org Networking Services [EMAIL PROTECTED] | [EMAIL PROTECTED] www.pgsql.com | www.hub.org 1-902-542-0713 | 1-902-542-3657 - Fascimile : 1 902 542 5386 IRC Nick : bignose -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 [EMAIL PROTECTED] www.ipinc.com
