Re: [GENERAL] about scape characters
Hello I've used oracle for 10years,but I've never seen such notations. In fact the SQL retuns an error . PostgreSQL postgres=# select E'\\'; ?column? -- \ (1 row) ORACLE(10.2.0) SQL select E'\\' from dual; select E'\\' from dual * ERROR at line 1: ORA-00923: FROM keyword not found where expected --Of cource,this SQL works. SQL select '\\' from dual; '\ -- \\ --or SQL set escape on SQL select '\\' from dual; ' - \ Sorry ,but I don't know SQLServer. Hi!, maybe is a silly question but... Exists some syntax standard to escape especial characters on querys??? i mean, the notation E'\\ to escape especial characters only works in postgres (8.3.11) or this works on oracle, ms sql server too??? regards, eddie. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] getting the last N tuples of a query
Hello. I agree Ben. But,I try your question as an SQL puzzle. Doses this SQL meet what you want? select * from wantlast offset (select count(*)-10 from wantlast); --test case create table wantlast(col1 int); insert into wantlast select g from generate_series(1,1000) as g; postgres=# select * from wantlast offset (select count(*)-10 from wantlast); col1 -- 991 992 993 994 995 996 997 998 999 1000 (10 rows) postgres=# analyze wantlast ; ANALYZE postgres=# explain select * from wantlast offset (select count(*)-10 from wantlast); QUERY PLAN -- Limit (cost=17.91..30.52 rows=900 width=4) InitPlan 1 (returns $0) - Aggregate (cost=16.50..16.52 rows=1 width=0) - Seq Scan on wantlast (cost=0.00..14.00 rows=1000 width=0) - Seq Scan on wantlast (cost=0.00..14.00 rows=1000 width=4) (5 rows) *I try this test Postgresql8.4.4 On Jul 8, 2010, at 4:17 PM, Edmundo Robles L. wrote: Hi! if a want the first 5,10,N tuples of a query (even without order) i just have to do a: select * from table limit 10; That does not get the first 10 tuples, it merely gets 10 tuples. The database is free to return whichever 10 it can, and in practice, the results will change given enough inserts or deletes. So, what is the right way to do that with no order Without an order by clause, there is no concept of first or last. Once you have the order by clause, combine your limit with ascending or descending sorts to get the first or last, respectively. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] SQL error: could not connect to database
Hi.
At first,I think you lack some settings.
(eg. pg_hba.conf,listen_addresses or restarting)
But you say I can log in to postgresql without password prompt using
the psql tool.
I can image some case,but we have to get some more information.
So I'd like you to try the following program.This program works fine on
my environment.
I believe this test can indicate which makes problem your environment or
your program.
Can you try this?
void GiveUp()
{
fprintf(stderr, Fatal error\n);
sqlprint();
}
main ()
{
/* declare variables*/
exec sql begin declare section;
char v_datcollate[256];
char id [] = database;
exec sql end declare section;
exec sql whenever sqlerror do GiveUp();
/*connection*/
exec sql connect to :id;
exec sql set autocommit = on;
exec sql begin work;
/* declare cursor */
exec sql declare c_db cursor for select datcollate from pg_database
where datname=:id;
/*1.select test*/
printf(1.Select test starts!! \n);
exec sql open c_db;
exec sql fetch in c_db into :v_datcollate;
printf(datcollate = %s \n,v_datcollate);
exec sql close c_db;
exec sql disconnect;
return(0);
}
(2010/06/23 3:13), Juba, Salahaldin I. wrote:
I am trying to connect to database using ECPG called 'database' - please see
the code below-. When I am excuting the code I am getting this message
Fatal error
SQL error: could not connect to database database on line 22
Fatal error
SQL error: connection NULL does not exist on line 23
How can I make the ECPG access my database. I have already configured my
database to trust all local connections and I can log in to postgresql without
password prompt using the psql tool. where did I make a mistake and what are
the configuration I need to do
I am using libecpg 6 and postgresql 8.4.4
Best,
#includeiostream
using namespace std;
EXEC SQL whenever sqlwarning sqlprint;
EXEC SQL whenever sqlerror do GiveUp();
void GiveUp()
{
fprintf(stderr, Fatal error\n);
sqlprint();
}
int main()
{
EXEC SQL BEGIN DECLARE SECTION;
char id [] = database;
EXEC SQL END DECLARE SECTION;
EXEC SQL CONNECT TO :id;
EXEC SQL CREATE USER tom WITH PASSWORD 'myPassword';
EXEC SQL DISCONNECT ALL;
return 0;
}
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] WINDOWS : PostgreSQL 8.4 Server Start Error
Hi. At an environment(Ver8.4.2),a simmilar error happened and we could work around setting postgresql.conf client_min_messages = log to client_min_messages = notice . I'm not sure that your error is same, but can you try this? //Your message is following //2010-06-07 14:11:40 ICT LOG: incomplete startup packet //So I guess that you set client_min_messages = log or higher. (2010/06/07 16:49), Zery wrote: Craig, Nothing is usefull or barely i understand :-) In pg_log i only find this : 2010-06-07 14:11:40 ICT LOG: could not receive data from client: An operation was attempted on something that is not a socket. 2010-06-07 14:11:40 ICT LOG: incomplete startup packet In event viewer i found : : Waiting for server startup.. and : Timed out waiting for server startup I have removed PowerDVD and still it doesn't solve the problem, this the second time it happen, the first time i couldn't figure out what's the problem, so I reinstalled Windows, this time i found the problem is crashing with PowerDVD, but i'm avoiding to reinstall Windows. I read some other way is to manage Firewall, i did turn off the firewall, still it dosn't solve either. Any other way?? On 6/7/10, Craig Ringercr...@postnewspapers.com.au wrote: On 07/06/10 08:34, Zery wrote: All, I'm a newbie in postgresql, a few days ago i install postgresql 8.4.3.1 and it works fine, yesterday i install cyberlink powerdvd 9 and after that my postgresql server cannot start, it give the following error info : The postgresql-8.4 - PostgreSQL Server 8.4 service is starting The postgresql-8.4 - PostgreSQL Server 8.4 service could not be started. The service did not report an error . Look in the Windows event log for details on why it might be failing to start. You can find Event Viewer in Adminstrative Tools in the Control Panel. Also look at the PostgreSQL log files in C:\Program Files\postgresql\8.4\data\pg_log (I think that path is right, by default anyway) and see if there's anything useful there. I wouldn't be too surprised to find that PowerDVD has installed something it shouldn't into C:\WINDOWS\SYSTEM32. -- Craig Ringer -- Kenichiro Tanaka K.K.Ashisuto http://www.ashisuto.co.jp/english/index.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] archive_command
Hello In my environment,archive_command works fine with this command. archive_command = 'COPY %p C:\\Program Files\\PostgreSQL\\8.4\\data\\archive\\%f' (Is this what you want to know?) Where can I find an example shell script and windows batch file for archive_command for backup? Thanks. Jack -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] effective_io_concurrency details
Hello. Is effective_io_concurrency working on WinXP sp2 ? No. effective_io_concurrency works when an OS can use posix_fadvise(). But I think WinXP doesn't have posix_fadvise(). and what is the difference between effective_io_concurrency = 0 and effective_io_concurrency = 1 If effective_io_concurrency = 0,PostgreSQL never uses PREFETCH. On my environment, my PostgreSQL doesn't start up when effective_io_concurrency=1. #My environment WinXP sp2 and PostgreSQL is 8.4.4, too. What I referenced) http://archives.postgresql.org/pgsql-hackers/2009-03/msg00911.php http://doxygen.postgresql.org/bufmgr_8c-source.html#l00075 pgsql/src/backend/storage/buffer/bufmgr.c 00070 /* 00071 * How many buffers PrefetchBuffer callers should try to stay ahead of their 00072 * ReadBuffer calls by. This is maintained by the assign hook for 00073 * effective_io_concurrency. Zero means never prefetch. 00074 */ Hello. I have 2 questions: Is effective_io_concurrency working on WinXP sp2 ? and what is the difference between effective_io_concurrency = 0 and effective_io_concurrency = 1 Postgres 8.4.4 pasman -- Kenichiro Tanaka K.K.Ashisuto http://www.ashisuto.co.jp/english/index.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Help on update.
Hello. First,we can not execute the SQL which Paulo indicated in PostgreSQL. See this manual. == http://www.postgresql.org/docs/8.4/interactive/sql-update.html Compatibility This command conforms to the SQL standard, except that the FROM and RETURNING clauses are PostgreSQL extensions. According to the standard, the column-list syntax should allow a list of columns to be assigned from a single row-valued expression, such as a sub-select: UPDATE accounts SET (contact_last_name, contact_first_name) = (SELECT last_name, first_name FROM salesmen WHERE salesmen.id = accounts.sales_id); This is not currently implemented — the source must be a list of independent expressions. Some other database systems offer a FROM option in which the target table is supposed to be listed again within FROM. That is not how PostgreSQL interprets FROM. Be careful when porting applications that use this extension. = So, I tried to following SQL, but I got error. update test t1 set t1.j= (COALESCE(Lag(t2.j) over(order by t2.j),null,0) ) + t2.j from test t2; ERROR: cannot use window function in UPDATE at character 36 If I use temporary table ,I can. But I feel this way is not simple. = ex) PostgreSQL is 8.4.4 --drop table test; create table test(i int , j int); insert into test values(1,2); insert into test values(1,3); insert into test values(1,4); begin; create temporary table test_temp (i int , j int); insert into test_temp SELECT i,COALESCE(Lag(j) over(order by j),null,0) + j from test; truncate table test; insert into test select * from test_temp; drop table test_temp; commit; = Anyone have a good idea? (2010/05/26 22:46), paulo matadr wrote: |create table test(i number , j number);| |insert into test values(1,2) ||insert into test values(1,3) ||insert into test values(1,4) | select * from test; I J -- -- 1 2 1 3 1 4 Myintentions: after update select * from test; I J -- -- 1 2 1 3+2 1 4+3 after select * from test; I J -- -- 1 2 1 5+2 1 7+5 In oracle,I use this:| update test x set x.j = x.j + (select lag_j from (select i, j, nvl(lag(j) over (order by i,j) ,0) as lag_j from test) y where x.i = y.i and x.j = y.j) how can translate this for work in postgres? Thanks's Paul | Paulo -- Kenichiro Tanaka K.K.Ashisuto http://www.ashisuto.co.jp/english/index.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] cleaning wal files from postgres
Hello. When you execute pg_stop_backup,postgresql makes a special file, like this 00010015.0064.backup. This means you can delete wal 00010014 and before. The PostgreSQL manual also says following. http://www.postgresql.org/docs/8.4/interactive/continuous-archiving.html#BACKUP-ARCHIVING-WAL 24.3.2. Making a Base Backup To make use of the backup, you will need to keep around all the WAL segment files generated during and after the file system backup. To aid you in doing this, the pg_stop_backup function creates a backup history file that is immediately stored into the WAL archive area. This file is named after the first WAL segment file that you need to have to make use of the backup. For example, if the starting WAL file is 0001123455CD the backup history file will be named something like 0001123455CD.007C9330.backup. (The second part of the file name stands for an exact position within the WAL file, and can ordinarily be ignored.) Once you have safely archived the file system backup and the WAL segment files used during the backup (as specified in the backup history file), all archived WAL segments with names numerically less are no longer needed to recover the file system backup and can be deleted. However, you should consider keeping several backup sets to be absolutely certain that you can recover your data. Thank you. oops, sorry, my question was very ambiguous ... What I want to know is: The correct procedure to remove the wal files that have been used in the recovery of a postgres server. Consider the following; 1.- A main server in production. 2.- A secondary server that is normally off. 3.- By connecting the secondary server, the primary server must copy the directory data as well as wal files generated during the copying process. 4.- start up the postgres on the secondary server. 5o.Delete wal files no longer will be used by postgres to save disk space. how can i know which wal file is not required by postgres and delete wal files from main and secondary server, to save space disk on the servers ? (after a succesfully start up on secondary server) regards eddie. On 05/21/2010 03:43 PM, Andreas Schmitz wrote: maybe I don't understand the problem. there is no need to clean WAL files after recovery. where exactly is the problem ? regards andreas erobles wrote: PITR recovery On 05/21/2010 03:04 PM, Andreas Schmitz wrote: erobles wrote: which is the right procedure to clean wal files after a recovery ?? what kind of recovery ? regards andreas -- Kenichiro Tanaka K.K.Ashisuto http://www.ashisuto.co.jp/english/index.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to deal with NULL values on dynamic queries?
Hi
Null + 1=null.
and
null + 'a' = null.
I expect that pVAL_COMENT is null (as you say).
===
create test environment.
===
create table nulltest (col1 varchar(10),col2 varchar(10));
insert into nulltest (col1,col2) values ('A','B');
insert into nulltest (col1) values ('A');
insert into nulltest (col2) values ('B');
select col1,col2 from nulltest;
select col1||col2 from nulltest;
postgres=# select col1,col2 from nulltest;
col1 | col2
--+--
A| B
A|
| B
(3 rows)
===
null make col1||col2 null
===
ex.
postgres=# select col1||col2 from nulltest;
?column?
--
AB
(3 rows)
=
even if we use quote_literal,we can not avoid tihs behavior.
=
ex.
postgres=# select quote_literal(col1)||quote_literal(col2) from nulltest;
?column?
--
'A''B'
(3 rows)
So we can use COALESCE() function to avoid this.
ex.
postgres=# select COALESCE(col1,'')||COALESCE(col2,'') from nulltest;
?column?
--
AB
A
B
(3 rows)
ex2.
postgres=# select
quote_literal(COALESCE(col1,''))||quote_literal(COALESCE(col2,'')) from
nulltest;
?column?
--
'A''B'
'A'''
'''B'
(3 rows)
Can you work around like this?
||quote_literal(COALESCE(pVAL_COMENT,''))
Thank you.
Hi,
I have a query that some values could be NULL, how can I deal with
this problem?
[code]
EXECUTE 'INSERT INTO '
|| quote_ident(pNOME_VIEW)
|| '('
|| quote_ident(pCHAVE_1)
|| ', DAT_INICIO, DAT_FIM, COMENT) values ('
|| quote_literal(pVAL_CHAVE_1)
|| ', '
|| quote_literal(pVAL_CHAVE_2)
|| ', '
|| quote_literal(pVAL_CAMPO1)
|| ', '
|| quote_literal(pVAL_COMENT)
|| ')';
[/code]
The variable pVAL_COMENT could be NULL or have a value. How can I deal
with this?
Sorry the bad english.
Best Regards,
--
Kenichiro Tanaka
K.K.Ashisuto
http://www.ashisuto.co.jp/english/index.html
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Select with string that has a lone hyphen yields nothing
Hi The hyphen which written in 'Olympus E-PL1' is different from the one which written in 'Camera - Black'. em-dash http://www.fileformat.info/info/unicode/char/2014/index.htm en-dash http://www.fileformat.info/info/unicode/char/2013/index.htm figure-dash http://www.fileformat.info/info/unicode/char/2012/index.htm I have no idea to fix using PostgreSQL's function,because they don't equal. I think you have to change the data or change the behavior of your application . Thank you. I have a product names table like this: datab=# select product_id, name from table.product_synonyms where name ilike '%%olympus e-pl1%%'; product_id | name +--- 8736 | Olympus E-PL1 8736 | Olympus E-PL1 Interchangeable Lens Type Live View Digital Camera – Black (Body Only) (Call for pre-order. Available on: 2010-04-09) 8736 | Olympus E-PL1 Interchangeable Lens Type Live View Digital Camera – Blue (Body Only) 8736 | Olympus E-PL1 Interchangeable Lens Type Live View Digital Camera w/ ED 14-42mm f3.5-5.6 (champagne/gold) (4 rows) Any select statement prior to the hyphen yields a result, but from the hyphen on yields nothing: datab=# select product_id, name from table.product_synonyms where name ilike '%%Olympus E-PL1 Interchangeable Lens Type Live View Digital Camera - Blue %%'; product_id | name +-- (0 rows) Any ideas how to fix this? -- Kenichiro Tanaka K.K.Ashisuto http://www.ashisuto.co.jp/english/index.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] gmake check problem
Hi Jim To resolve this problem, we have to indicate which SQL is hanging. stats test executes postgresql-8.4.3/src/test/regress/sql/stats.sql. we can see what the test does. And we can see the log at postgresql-8.4.3/src/test/regress/results/stats.out So I suppose we can indicate which SQL is hanging. What does stats.out say? Thank you. I built 8.4.3 on Centos 5 with just ./configure and gmake When I run gmake check, the process stops at test stats... There is no residual postgresql or postmaster running (consuming clock cycles on top). When I break the process, I get the following errors: gmake[2]: *** wait: No child processes. Stop. gmake[2]: *** Waiting for unfinished jobs gmake[2]: *** wait: No child processes. Stop. gmake[1]: *** [check] Error 2 gmake: *** [check] Interrupt [postg...@bubachubs postgresql-8.4.3]$ Any help would be appreciated. Thanks, Jim -- Kenichiro Tanaka K.K.Ashisuto http://www.ashisuto.co.jp/english/index.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_hba.conf
Hi Would there be a line earlier in the file that matches and is preventing a connection? At first, I think so too. But if there is a line earlier in the file ,we get following error. psql: could not connect to server: Connection refused Is the server running on host 192.168.23.132 and accepting TCP/IP connections on port 1843? ex: my pg_hba.conf hostall all 192.168.23.132 255.255.255.255 deny hostall all 192.168.23.132 255.255.255.255 trust The Jim's message say pg_hba.conf has no entory. FATAL: no pg_hba.conf entry for host 209.159.145.248, user postgres, database arc 1)Is pg_hba.conf's location correct? You can check to execute this command. postgres=# show hba_file; hba_file --- /home/p843/pgdata/pg_hba.conf (1 row) 2)Did you reload pg_hba.conf? If we change pg_hba.conf ,we must execute pg_ctl reload 3)pg_hba.conf may have a trash. Can you recreate pg_hba.conf? *Don't copy old pg_hba.conf. Thank you. On 27/04/2010 11:42, jkun...@laurcat.com wrote: I am putting up a new server on version 8.4.3. I copied pg_hba.conf from a running 8.3.6 system, changing only the public IP address for the local machine. I get the error: FATAL: no pg_hba.conf entry for host 209.159.145.248, user postgres, database arc pg_hba.conf contains the line: hostall all209.159.145.248 255.255.255.255 trust Would there be a line earlier in the file that matches and is preventing a connection? Ray. -- Kenichiro Tanaka K.K.Ashisuto http://www.ashisuto.co.jp/english/index.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Problem with pg_prepare
HI. Call to undefined function pg_prepare(). What version is your PostgreSQL(libpq)? And do your error occur on PHP program? pg_prepare() is supported only against PostgreSQL 7.4 or higher connections. http://php.net/manual/en/function.pg-prepare.php Thank you. (2010/04/24 6:57), Giancarlo Boaron wrote: Hi all. I'm receiving the following message when I try to use pg_prepare() function: Call to undefined function pg_prepare(). My application works very well with others pg_* commands... I already checked my configuration files and I have no more ideas about how to fix it. Any suggestions? Thank you. -- Kenichiro Tanaka K.K.Ashisuto http://www.ashisuto.co.jp/english/index.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Need some help with a query (uniq -c)
Hello. I try with With Query. http://www.postgresql.org/docs/8.4/static/queries-with.html #We can use With Queries v8.4 #That'll only work if the time values are contiguous, but there's probably a #similar trick for non-contiguous ,too. --create data drop table foo; create table foo( time int,message text); insert into foo values(1,'a'); insert into foo values(2,'b'); insert into foo values(3,'b'); insert into foo values(4,'c'); insert into foo values(5,'a'); insert into foo values(6,'c'); insert into foo values(7,'c'); insert into foo values(8,'a'); insert into foo values(9,'a'); insert into foo values(10,'a'); --begin Answer with recursive r as ( select foo.time,foo.message,1 as dummy from foo union all select foo.time,foo.message,r.dummy+1 from foo , r where foo.time=r.time-1 and foo.message=r.message ) ,rr as ( select foo.time,foo.message,'OLID' as flag from foo union all select foo.time,foo.message,'DUP' as flag from foo , rr where foo.time-1=rr.time-2 and foo.message=rr.message ) select time min,time+max(dummy)-1 max,message,max(dummy) counts from r where time not in (select distinct (time+1) times from rr where flag='DUP') group by time,message order by time; --result postgres(# where flag='DUP') group by time,message order by time; min | max | message | counts -+-+-+ 1 | 1 | a | 1 2 | 3 | b | 2 4 | 4 | c | 1 5 | 5 | a | 1 6 | 7 | c | 2 8 | 10 | a | 3 (6 rows) --end But I think some one can provide more simple SQL. Thank you. On Apr 12, 2010, at 11:31 AM, Scott Marlowe wrote: On Mon, Apr 12, 2010 at 12:22 PM, A Bgentosa...@gmail.com wrote: Hello! I have a table (think of it as a table of log messages) time | message --- 1 | a 2 | b 3 | b 4 | b 5 | a the three 'b' are the same message, so I would like to write a query that would give me a result that is similar to what the unix command uniq -c would give: first | message | last | count -- 1 | a | 1 | 1 2 | b | 4 | 3--- here it squeezes similar consecutive messages into a single row 5 | a | 5 | 1 How do I write such a command? Pretty straight ahead: select min(t), message, max(t), count(*) from table group by message. That was my first though too, but it combines everything not just adjacent messages. Something like this, maybe select t1.message, t1.time as first, t2.time as last, t2.time-t1.time+1 as count from foo as t1, foo as t2 where t1.time= t2.time and t1.message = t2.message and not exists (select * from foo as t3 where (t3.time between t1.time and t2.time and t3.message t1.message) or (t3.time = t2.time + 1 and t3.message = t1.message) or (t3.time = t1.time - 1 and t3.message = t1.message)); message | first | last | count -+---+--+--- a | 1 |1 | 1 b | 2 |4 | 3 a | 5 |5 | 1 That'll only work if the time values are contiguous, but there's probably a similar trick for non-contiguous. Cheers, Steve -- Kenichiro Tanaka K.K.Ashisuto http://www.ashisuto.co.jp/english/index.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] when i logged in mydb,any sql command used to list all the tables in this mydb?
Hello sunpeng
First,I answer this question.
another question:how postgresql internal knows which
relations belongs to which database?
When we use psql command ,it means psql -d postgres.
#you can change default parameter to set PGDATABASE (OS parameter)
-d indicate your connecting database and this is the ANSWER.
So, it is natural that pg_class does not have database field.
We can not access other database with psql without using dblink.
I think we can display some database's pg_class with following command,
#DISPLAY test and postgres's tables
#Your postgresql have to be installed dblink
#1)create dblink
select dblink_connect('test','host=postgres01 port=1843 dbname=test
user=p843');
select dblink_connect('postgres','host=postgres01 port=1843
dbname=postgres user=p843');
#2) display
select 'test',* from dblink('test','select oid, relname from pg_class')
t1(oid oid, relname text)
union
select 'postgres',* from dblink('postgres','select oid, relname from
pg_class') t1(oid oid, relname text);
Thank you
once i have created mydb and several relations in it,are there any sql
commands used to list all the tables in this mydb?
i noticed there are no database( pg_database.oid) field in pg_class
table,so i can not use
select relname from pg_class,pg_database where pg_database.datname
like 'mydb' and pg_class.database = pg_database.oid;
anybody knows how to do it?
another question:how postgresql internal knows which relations belongs
to which database?
thanks
--
Kenichiro Tanaka
K.K.Ashisuto
http://www.ashisuto.co.jp/english/index.html
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] when i logged in mydb,any sql command used to list all the tables in this mydb?
Hi peng
According to 8.4 document 44.1(Most system catalogs are
copied from the template database during database creation and are
thereafter database
-specific.),we know that each created database has their own pg_...
tables
I believe it is right.
and thus if a superuser administrator wants to list all the tables
in all the
databases,how do the postgresql interval implement it? will the
postgresql
interval load all the pg_ tables in all the databases to get
the final answer?
I think postgresql never interval load all the pg_ tables in all
the databases.
If administrator want to list all the tables,we heve to create application.
ex.
oid2name -q | awk '{print$2}'|grep -v template0 |xargs -l1 oid2name -d
* oid2name is contrib module.
Thank you
hi,Kenichiro,
Thanks for your answer!
Then another question comes: According to 8.4 document 44.1(Most
system catalogs are copied from the template database during database
creation and are thereafter database-specific.),we know that each
created database has their own pg_... tables and thus if a superuser
administrator wants to list all the tables in all the databases,how do
the postgresql interval implement it? will the postgresql interval
load all the pg_ tables in all the databases to get the final answer?
Thanks!
peng
2010/4/8 Kenichiro Tanaka ketan...@ashisuto.co.jp
mailto:ketan...@ashisuto.co.jp
Hello sunpeng
First,I answer this question.
another question:how postgresql internal knows which
relations belongs to which database?
When we use psql command ,it means psql -d postgres.
#you can change default parameter to set PGDATABASE (OS parameter)
-d indicate your connecting database and this is the ANSWER.
So, it is natural that pg_class does not have database field.
We can not access other database with psql without using dblink.
I think we can display some database's pg_class with following
command,
#DISPLAY test and postgres's tables
#Your postgresql have to be installed dblink
#1)create dblink
select dblink_connect('test','host=postgres01 port=1843
dbname=test user=p843');
select dblink_connect('postgres','host=postgres01 port=1843
dbname=postgres user=p843');
#2) display
select 'test',* from dblink('test','select oid, relname from
pg_class') t1(oid oid, relname text)
union
select 'postgres',* from dblink('postgres','select oid, relname
from pg_class') t1(oid oid, relname text);
Thank you
once i have created mydb and several relations in it,are there
any sql commands used to list all the tables in this mydb?
i noticed there are no database( pg_database.oid) field in
pg_class table,so i can not use
select relname from pg_class,pg_database where
pg_database.datname like 'mydb' and pg_class.database =
pg_database.oid;
anybody knows how to do it?
another question:how postgresql internal knows which relations
belongs to which database?
thanks
--
Kenichiro Tanaka
K.K.Ashisuto
http://www.ashisuto.co.jp/english/index.html
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org
mailto:pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Kenichiro Tanaka
K.K.Ashisuto
http://www.ashisuto.co.jp/english/index.html
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to fix set-valued function called in context that cannot accept a set in earlier versions
Hello, Andrus
I tested this reproduce case.
8.3.9 return exception
8.3.10 return exception
8.4.0 Crash Server!!
8.4.1 OK
8.4.2 OK
8.4.3 OK
Just for your information,
8.4.0's clash made this call stack(I analyzed core file)
#0 DirectFunctionCall1 (func=0x8095a70 hashoid, arg1=16421) at
fmgr.c:1012
1012result = (*func) (fcinfo);
(gdb) where
#0 DirectFunctionCall1 (func=0x8095a70 hashoid, arg1=16421) at
fmgr.c:1012
#1 0x082c9eab in CatalogCacheComputeHashValue (cache=0xa091058,
nkeys=value optimized out, cur_skey=0xbeca1298) at catcache.c:207
#2 0x082cad19 in SearchCatCache (cache=0xa091058, v1=16421, v2=0, v3=0,
v4=0)
at catcache.c:1137
#3 0x082df1cf in fmgr_info_cxt_security (functionId=16421,
finfo=0xbeca144c,
mcxt=value optimized out, ignore_security=0 '\0') at fmgr.c:209
#4 0x082df881 in fmgr_info (functionId=16421, finfo=0xbeca144c) at
fmgr.c:156
#5 0x082df282 in fmgr_info_cxt_security (functionId=43373,
finfo=0xb56c6320,
mcxt=value optimized out, ignore_security=1 '\001') at fmgr.c:406
#6 0x082e0520 in fmgr_security_definer (fcinfo=0xa0e2f34) at fmgr.c:898
#7 0x082e041c in fmgr_security_definer (fcinfo=0xa0e2f34) at fmgr.c:961
#8 0x082e041c in fmgr_security_definer (fcinfo=0xa0e2f34) at fmgr.c:961
I usually see this call stack in version 8.4.0 and I guess this error
does not occur in 8.4.1
Thank you.
(2010/04/05 20:14), Andrus wrote:
In 8.3 code below causes exception
ERROR: set-valued function called in context that cannot accept a set
CONTEXT: PL/pgSQL function wordwrap line 21 at RETURN NEXT
How to fix this so that wordwrap works in any PostgreSql 8.x version
or at least in
8.3 and 8.4 ?
Andrus
CREATE OR REPLACE FUNCTION wordwrap(line text, linelen integer)
RETURNS SETOF text as $$
DECLARE
words text[] := string_to_array(line,' ');
i integer;
res text:='';
BEGIN
if trim(line)='' then
return next '';
return;
end if;
for i IN 1 .. array_upper(words,1) LOOP
if length(res)+length(words[i]) linelen THEN
return next res;
res := '';
END IF ;
if res'' then
res := res || ' ';
end if;
res := res || words[i];
end loop;
return next res;
END
$$ LANGUAGE plpgsql;
select wordwrap('fdgdf',10)
--
Kenichiro Tanaka
K.K.Ashisuto
http://www.ashisuto.co.jp/english/index.html
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
