Re: [GENERAL] General query optimization howto
Miroslav ¦ulc wrote: is there on the net any general howto on SQL query optimizations? We have recently moved our project from MySQL to PostgreSQL and are having problem with one of our queries. I doubt that there is a generic documentation on SQL optimization, because this heavily depends on the particular implementation that you are using. The EXPLAIN command is surely useful but I don't know how to read it and how to use the output to optimize the query so I'm looking for some intro that could help me. You should probably start with the Performance Tips chapter in the PostgreSQL documentation. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] General query optimization howto
On Sun, 2005-03-13 at 02:34 +0100, Miroslav ulc wrote: is there on the net any general howto on SQL query optimizations? We have recently moved our project from MySQL to PostgreSQL and are having problem with one of our queries. The EXPLAIN command is surely useful but I don't know how to read it and how to use the output to optimize the query so I'm looking for some intro that could help me. EXPLAIN ANALYZE is even more useful. start with looking for inconsistencies between row estimates and actual row counts. these could mean that you need to ANALYZE, or increase statistics for some columns. also look for expensive sequential scans where you would expect an index scan. this may be due to missing indexes, imcompatible column types, lack of ANALYZE, or insufficient statistics. browse through the archives of the pgsql-performance list, to get a feel of typical problems, and to read illuminating responses from regulars. if you still are having problems, make the simplest test case you can, and post an EXPLAIN ANALYZE to pgsql-performance, along with relevant data, such as table definitions, typical data distributions and postgres version. gnari ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] General query optimization howto
Bruce Momjian wrote: Have you read the FAQ? Yes, but I have found only some useful information saying when indexes are not used. Miroslav ulc begin:vcard fn;quoted-printable:Miroslav =C5=A0ulc n;quoted-printable:=C5=A0ulc;Miroslav org:StartNet s.r.o. adr;quoted-printable;quoted-printable:;;Vrchlick=C3=A9ho 161/5;Praha 5;;150 00;=C4=8Cesk=C3=A1 republika email;internet:[EMAIL PROTECTED] title:CEO tel;work:+420 257 225 602 tel;cell:+420 603 711 413 x-mozilla-html:TRUE url:http://www.startnet.cz version:2.1 end:vcard ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] General query optimization howto
Peter Eisentraut wrote: Miroslav ¦ulc wrote: is there on the net any general howto on SQL query optimizations? We have recently moved our project from MySQL to PostgreSQL and are having problem with one of our queries. I doubt that there is a generic documentation on SQL optimization, because this heavily depends on the particular implementation that you are using. The EXPLAIN command is surely useful but I don't know how to read it and how to use the output to optimize the query so I'm looking for some intro that could help me. You should probably start with the Performance Tips chapter in the PostgreSQL documentation. I've read that one. Sure it is good starting point but for a newbie (= me) it's not enough :-( Miroslav ¦ulc begin:vcard fn;quoted-printable:Miroslav =C5=A0ulc n;quoted-printable:=C5=A0ulc;Miroslav org:StartNet s.r.o. adr;quoted-printable;quoted-printable:;;Vrchlick=C3=A9ho 161/5;Praha 5;;150 00;=C4=8Cesk=C3=A1 republika email;internet:[EMAIL PROTECTED] title:CEO tel;work:+420 257 225 602 tel;cell:+420 603 711 413 x-mozilla-html:TRUE url:http://www.startnet.cz version:2.1 end:vcard ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] partitionning
On Sat, Mar 12, 2005 at 11:35:51PM -0500, Tom Lane wrote: The issue isn't really whether the planner *could* use a constraint to decide that a table need not be scanned at all. As you say, we have practically all the infrastructure needed for such inferences. To me the real reason why we don't do that is the lack of a mechanism to force the plan to be reconsidered if the constraint is dropped. Without the constraint, the plan will still run, but it may deliver wrong answers. (The closest current equivalent is the possibility of dropping an index that a plan depends on --- but at least we will fail outright if the index isn't there anymore.) Wow, now that's a corner case I hadn't though of. Actually, it seems to me a similar thing may happen if you have a query on a table and you create a new inherited table from that. Is the new table included in this old plan? There are any number of DDL statements that can affect planning. All of them I guess... In short, I won't accept any such patch until after we build a mechanism for invalidating cached plans. Which we need anyway for other reasons. So the path ahead is clear, in my mind anyway ... but this is not the next step to take. People still keep running into the invalid plan issue, consider temporary tables. Would if be feasable to have a plan maintain a list of objects it depends on, every column, table, constraint, domain, function, type, etc referred to and regenerate the plan if any of them change. This list could become huge for any moderatly complicated query and there doesn't seem to be a lot of gain most of the time. But it would be needed for completeness. Actually, it would probably be enough to just list tables and columns and have changes in constraints, triggers and default values be changes on the columns they refer to. I'm sure this has been discussed to death already though and is just waiting for a suitable implementation. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgp9AtLZP1Ob8.pgp Description: PGP signature
[GENERAL] where is the locale set for each server instance?
Hi! I'm pluggin ICU into PostgreSQL for unicode collation, since FreeBSD has no support for unicode collation. It works fine, but I cannot find out where to set the default locale for each backend instance. I want to use the LC_COLLATE used in initdb, now I've just hard wired it for my own needs. I tried backend/access/transam/xlog.c:ReadControlFile, but it is not sufficient. in backend/main/main.c: /* * Set up locale information from environment. Note that LC_CTYPE and * LC_COLLATE will be overridden later from pg_control if we are in an * already-initialized database. So, I'm trying to find out where LC_COLLATE is overridden. Any tips? Thanks, Palle ---(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: [GENERAL] where is the locale set for each server instance?
Palle Girgensohn [EMAIL PROTECTED] writes: So, I'm trying to find out where LC_COLLATE is overridden. Any tips? access/transam/xlog.c (which is the only file that touches pg_control, I believe). regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] [PATCHES] A way to let Vacuum warn if FSM settings are low. [final?]
Bruce Momjian pgman@candle.pha.pa.us writes: These new messages: NOTICE: max_fsm_relations(1000) equals the number of relations checked HINT: You have = 44 relations. Consider increasing the configuration parameter max_fsm_relations. NOTICE: the number of page slots needed (704) exceeds max_fsm_pages (2) HINT: Consider increasing the configuration parameter max_fsm_relations to a value over 704. VACUUM Those statements seem a tad strange with those numbers... -- greg ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] where is the locale set for each server instance?
--On söndag, mars 13, 2005 17.01.31 -0500 Tom Lane [EMAIL PROTECTED] wrote: Palle Girgensohn [EMAIL PROTECTED] writes: So, I'm trying to find out where LC_COLLATE is overridden. Any tips? access/transam/xlog.c (which is the only file that touches pg_control, I believe). OK, seems my problem is with ICU. The xlog.c:ReadConfigFile() is not run for each backend, it seems, I assume it is forked after that. Seems ICU cannot remember a setDefault(locale) call for some reason. I'll just have to do a setlocale(LC_COLLATE, NULL) when I need one, I guess... It'll work fine. Thanks! Palle ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] where is the locale set for each server instance?
Palle Girgensohn [EMAIL PROTECTED] writes: OK, seems my problem is with ICU. The xlog.c:ReadConfigFile() is not run for each backend, it seems, I assume it is forked after that. No, it is run once in the postmaster, as indeed the comments in it say. Seems ICU cannot remember a setDefault(locale) call for some reason. Curious. You'd expect whatever state that sets to persist across a fork. You should probably ping the ICU people about it. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] where is the locale set for each server instance?
--On söndag, mars 13, 2005 18.37.24 -0500 Tom Lane [EMAIL PROTECTED] wrote: Palle Girgensohn [EMAIL PROTECTED] writes: OK, seems my problem is with ICU. The xlog.c:ReadConfigFile() is not run for each backend, it seems, I assume it is forked after that. No, it is run once in the postmaster, as indeed the comments in it say. Seems ICU cannot remember a setDefault(locale) call for some reason. Curious. You'd expect whatever state that sets to persist across a fork. You should probably ping the ICU people about it. You're right, I will. Thanks, Palle ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Postgres jobs mailing list?
On Mar 11, 2005, at 6:49 PM, Jerry Sievers wrote: The PG mailing lists web page contains artifacts of a jobs list but no such list appeared in the dropdown of available lists. I am referring to; http://www.postgresql.org/community/lists/subscribe Is there such a resource and if so, could someone direct me to it? Try here: http://mail.postgresql.org/mj/mj_wwwusr? domain=postgresql.orgfunc=lists-long-fullextra=pgsql-jobs John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] preoblem in jdbc postgresql and tomcat
hi,
i have installed postgresql-7.4.1,and tomcat4.1.31and
j2sdk1.4.2_07
for tomcat i have included path
/usr/local/jakarta-tomcat-4.1.31
for j2sdk path set is /usr/local/j2sdk1.4.2_07
and CLASSPATH SET IS:
/usr/local/pgsql/pg73jdbc3.jar:/usr/local/pgsql/pg74.215.jdbc3.jar:/usr/local/pg
sql/postgresql-8.0-310.jdbc3.jar:/usr/local/j2sdk1.4.2_07/lib/tools.jar:/usr/loc
al/j2sdk1.4.2_07/jre/lib/rt.jar:/usr/local/jakarta-tomcat-4.1.31/common/lib/serv
let.jar:/usr/local/jakarta-tomcat-4.1.31/common/lib:/usr/local/pgsql:/usr/local/
j2sdk1.4.2_07/jre/lib:/usr/local/j2sdk1.4.2_07/lib
i have included all the paths
all the above path are set in /etc/profile/
and i set path for all three jar
files:/usr/local/pgsql/pg73jdbc3.jar:/usr/local/pgsql/pg74.215.jdbc3.jar:/usr/local/pg
sql/postgresql-8.0-310.jdbc3.jar
when i run this simple java program i get
error as follows:
[EMAIL PROTECTED] bin]# javac PostgreSQLTest.java//compile
PostgreSQLTest.java: In class `PostgreSQLTest':
PostgreSQLTest.java: In method
`PostgreSQLTest.main(java.lang.String[])':
PostgreSQLTest.java:7: Exception
`java.lang.ClassNotFoundException' must be caught, or
it must be declared in the `throws' clause of `main'.
Class.forName(postgresql.Driver).newInstance();
^
PostgreSQLTest.java:7: Exception
`java.lang.InstantiationException' must be caught, or
it must be declared in the `throws' clause of `main'.
Class.forName(postgresql.Driver).newInstance();
^
PostgreSQLTest.java:7: Exception
`java.lang.IllegalAccessException' must be caught, or
it must be declared in the `throws' clause of `main'.
Class.forName(postgresql.Driver).newInstance();
my PostgreSQLTest.java is as below
import java.sql.*;
class PostgreSQLTest {
public static void main (String[] args) {
try {
Driver driver = (Driver)
Class.forName(postgresql.Driver).newInstance();
DriverManager.registerDriver(driver);
String url = jdbc:postgresql:javatest;
Connection con =
DriverManager.getConnection(url, postgres, );
Statement stm = con.createStatement();
stm.setQueryTimeout(10);
ResultSet rs = stm.executeQuery(select col1
from test);
rs.next();
System.out.println(rs.getString(1));
} catch (SQLException e) {
System.out.println(Exception!);
System.out.println(e.toString());
}
}
}
This is one simple example
Now when i tried to perform operations with tomcat
simple sevlet works but with database operations i get
the following errors
file is:ShowBedrock.java
import javax.servlet.*;
import javax.servlet.http.*;
public class ShowBedrock extends HttpServlet
{
public String getServletInfo()
{
return Servlet connects to PostgreSQL database
and displays result of a SELECT;
}
private Connection dbcon; // Connection for scope
of ShowBedrock
// init sets up a database connection
public void init(ServletConfig config) throws
ServletException
{
String loginUser = postgres;
String loginPasswd = roopesh;
String loginUrl =
jdbc:postgresql://localhost/bedrock;
// Load the PostgreSQL driver
try
{
Class.forName(org.postgresql.Driver);
dbcon =
DriverManager.getConnection(loginUrl, loginUser,
loginPasswd);
}
catch (ClassNotFoundException ex)
{
System.err.println(ClassNotFoundException: +
ex.getMessage());
throw new ServletException(Class not
found Error);
}
catch (SQLException ex)
[GENERAL] fied separator change from the shell command line
Hello, I'm trying to change the usal | table field separator from the shell command line: psql -d ect -f pl_lost.sql -o pl_lost.out.txt -F \t -U asaadmin But it doesn't work. It keeps the same | separator in the output file. Can anyone please help me? I need to output to a tab separated file. Can anynone hel me with this? Thanks in advance, PJO ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] sql question
Hi all My table definition : id | fref | mref --+---+-- 1 | 23 | 25 2 | 24 | 28 3 | 25 | 31 4 | 26 | 34 My problem : i need a query that results in this : id | ref --+-- 1 | 23 1 | 25 2 | 24 2 | 28 3 | 25 3 | 31 4 | 26 4 | 34 Do I need a crosstab-query ? Who can help me ? -- A computer is like an airconditioner. When windows open, it stops working ! -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- Steven Verhoeven, ICT Support Engineer Department for Molecular Biomedical Research (DMBR) VIB - Ghent University 'Fiers-Schell-Van Montagu' building Technologiepark 927B - 9052 Ghent (Zwijnaarde) Belgium Tel : +32-(0)9-33-13.606 Fax : +32-(0)9-33-13.609 E-mail : [EMAIL PROTECTED] URL : http://www.dmbr.UGent.be
[GENERAL] PostgreSQL training
Hi, I'm working on an application for a client that uses PostgreSQL as its database backend. The client wants to train their team on PostgreSQL so that they can maintain the application and the database themselves after it goes live should they need to. As far as I know the majority of them don't have any experience with databases (other than using applications that rely on them of course). The type of training that they would need would need to cover generic database concepts (database design, SQL), as well as PostgreSQL-specific information (mostly database administration, maybe some performance tuning). They can travel anywhere within the US to take the training. I noticed there were a few training events posted on postgresql.org, but only one of them is within the US (PostgreSQL Bootcamp, next month), and I'm not sure if that course would offer the right type of training for them (I think they'd need something more elementary). Do you guys have any suggestions as far as training is concerned (training events throughout the year, companies that do PostgreSQL training, etc.)? Thanks a lot, Alex ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] PostgreSQL still for Linux only?
Tope Akinniyi wrote: Hi all, In my country Nigeria (and even African continent), we do not eat what the western world eat. We wear different styles of cloths. In the same vein, our computerisation culture is different. Having lived in Indonesia, I can sympathize with your situation. It is not just Africa, but most of the developing world. I must submit that computers became popular in Nigeria by Windows desktop system. While the western world were exposed to *NIX from the beginning, we were introduced to computing via DOS and later Windows. That is our IT antecedent and culture. People use database engines such as Oracle, Firebird, Sybase, mySQL, etc on Windows here and they manage them and survive. If because you want to recommend PostgreSQL, you insist on Non-Windows OS, the first question clients ask you is why is your own different? Why must I switch from Windows to *NIX because of your PostgreSQL? You might end up not succeeding in that bid. And we are used to the blue screen (crashes) and each IT house in Nigeria has gone the extra mile to ensure the safety of the operations of its clients. Everyone is a product of his environment, peculiarities and experiences. If you want a reasonable open source RDBMS for production use on Wondows, I would suggest that you use Firebird. However if Windows is not the selling point, consider the following: 1) You may be able to get extra use out of older systems by installing Linux and PostgreSQL. This may perform better than Windows and Firebird as long as you don't need a GUI. This may be more reliable than Windows especially if you can't afford high-end hardware (ECC RAM, SCSI drives, etc) for your production servers anyway. 2) The PL's available for PostgreSQL add a lot of flexability. As an IT organisation that wants to stay in business you need to give to people what they wants. I think that is the basis of service. I have some deployments of PostgreSQL on Windows servers. I must admit that we have not had any problems so far. The glory of open source is that people will do what they want with it. PostgreSQL for Windows is not really something I would run a large production database on at the moment. However, open source tools tend to develop in strange ways. I am sure that as PostgreSQL on Windows becomes more popular, the issues will get worked out as much as possible. Notwithstanding, due efforts must be made to protect your clients' operations whether you use Windows or Posix. In that regards, I thought of reducing the risk factor by implementing replication on some of the servers. Command Prompt's solution works on Windows. Slony will require some porting, but if this is important, you can hire a programmer to help with the porting :-) Otherwise you can wait for someone else to do it. I sought Windows replication tool for and could not get. I checked PgFoundry and the one there put a banner and said NOT FOR WINDOWS. Then I said is this PostgreSQL for Windows a joke? That prompted my post - IS POSTGRESQL FOR LINUX ONLY? Check the archives about Slony-I and Windows. Maybe ask the developers how much work it would be to port it. If labor is inexpensive in Nigeria, maybe you can hire a programmer to do it. Now, as the CEO of an IT organisation, I want to draft my final blueprint on PostgreSQL. I need your advice on this. 1. If I can manage it, can I continue to use PostgreSQL on Windows and watch as it evolves? I recognise the points certain respondents made on earlier; which was PostgreSQL on Windows is still a baby boy, do not expect it to walk like a man or expect it to possess the features of a man. Ok, maybe others can provide more refined estimates, but I expect that it will be 1-2 years before PostgreSQL on Windows is mature enough for higher-load purposes. You can however help by using it, and communicating your experiences with programmers. If this is not enough, you can even pay someone to fix things for you. These are selling points of open source software. 2. This response is alarming: Tom Lane wrote in digest V1.5092: We are supporting Windows as a Postgres platform for the benefit of developers who want to do testing on their laptops (and for reasons best known to themselves feel a need to run Windows on their laptops). a. Who are the 'we' Tom is talking about? b. Is he speaking for PostgreSQL Developers and the entire PostgreSQL community? As much as I don't like to speak for others, I read this as saying something like: We (the core developers) began work on the Windows port because we wanted to support developers running PostgreSQL on their systems. c. Does this mean that PostgreSQL for Windows is just a toy or model - Oh do not take it serious? Or is the Windows version by design a miniature of the *NIX version, lacking the requisite mechanism of a reliable database? I think the core team takes all aspects of PostgreSQL very seriously.
[GENERAL] sql question
My table definition : id | fref | mref --+---+-- 1 | 23| 25 2 | 24| 28 3 | 25| 31 4 | 26| 34 i need a query that results in this : id |ref --+-- 1 | 23 1 | 25 2 | 24 2 | 28 3 | 25 3 | 31 4 | 26 4 | 34 Do I need a crosstab-query ? Who can help me ? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Permission's question
Hi, I am new to Postgres so please bear with me. How do you create a database where only the owner can read and write to it. I created a database owned by a user but I noticed that when logined in as another user that was not an owner of that database that they could write to it. So my question is that when creating databases, is there a way to deny everyone except for the owner? Is this something that would be done in the pg_hba.conf. Is there a way to specify permissions on an entire database? If I overlooked something in the manual, please send me the link. Thank you. - Ryan ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Postgres stored proc that extracts data from Oracle
Hello All, I am a beginning PL/pgSQL and PL/SQL developer and I have a question. I need to create a procedure in postgres that would compare data between a table in Postgres and a table in Oracle. Let's put it like this, I have a list of projects in a table in PG and the properties of those projects can be found in a table in an Oracle DB. I need to create a procedure that gets a list of all projects with a certain status. A list of project ID's must be fetched from postgres and the status of each project ID is in Oracle. Do you guys have thoughts on how to implement this? I am not expecting an easy solution but I wish to get started asap. Any advice would be appreciated. Thanks in advance, James __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] fied separator change from the shell command line
[EMAIL PROTECTED] writes: Hello, I'm trying to change the usal | table field separator from the shell command line: psql -d ect -f pl_lost.sql -o pl_lost.out.txt -F \t -U asaadmin But it doesn't work. It keeps the same | separator in the output file. Can anyone please help me? I need to output to a tab separated file. Can anynone hel me with this? Thanks in advance, Changing the field separator only works with unaligned output. You need to add a -A to your command line switches (or --no-align). Doing this, though, drops the column headers. -- Remove -42 for email ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] German umlauts problem (under WindowsXP, COBOL programm)
Hello everyone, my colleagues and I try to convert our old data base system to PG. We created a small client-server prototype and used a java programm (J2SE, Version 1.4.1_01, JDBC-Treiber: pgdev.307.jdbc3) to test. Everything goes well and the German umlauts (ä, ö, ü, ß, Ä, Ö, Ü) can be inserted, updated and displayed correctly. Since we have already many COBOL programms, so we want to use them. Unfortunately we have problems in handling the German umlauts. Here are the problems in detail: Operating system: WindowsXP Professional Version 2002, with Service Package 1 PostgreSQL Version 8.0.0 Locale: German (set during installation with PG_installer) default encoding: LATIN1 (set during installation with PG_installer) ODBC-Driver: psqlodbc, Version 8.00.01.01 (of 05.03.2005) Micro Focus Cobol, Compiler: NetExpress Version 4.0.38 The data base is created with the encoding Latin1 (createdb -E LATIN1 ). 1) Insert umlauts: Before we insert umlauts we have to in the COBOL programms explicitly set the client_encoding to be LATIN1, otherwise we get the error could not convert UTF-8 character 0x00e4 to ISO8859-1 (0x00e4 = 228 = German ä in ISO8859-1). After setting the client_encodign to LATIN1, we can see through pgAdminIII that the umlauts are saved correctly. 2) Read umlauts To read the umlauts we have to set the client_encoding to be UNICODE, otherwise we get only question mark (?) instead of umlauts. If we set the client_encoding to be LATIN1 we get also question marks. 3) Umlauts in select condition Say text001 is Verträge. If we execute the query select fromwhere text text001, we get the error could not convert UTF-8 character 0x00e4 to ISO8859-1. (Here it is useless to set the client_encoding to be LATIN1 or UNICODE). We opened the PG-log (configurate in System-DSN) and found out that, the PG-Server does the following: a) checks that what the client_encoding is: conn=3620872, query='select pg_client_encoding()' [ fetched 1 rows ] [ Client encoding = 'LATIN1' (code = 8) b) After knowing the client_encoding it will set the client_encoding to be UTF8. So if we don't reset it to be LATIN1, the server will think the client send UTF8-code and it will try to convert UTF8-code to be ISO8859-1, which triggered the error 1) during insertion of umlauts. But why the server set the client_encoding to be UTF8? Should I set some environment variables? In the COBOL test program we do the following: 1) read a tuple which has umlauts from a table 2) then update it with new umlauts 3) at last read tupels whose seltext Kunden mit Verträgen' . Here is the log file psqlodbc_3888.log: conn=3620872, PGAPI_DriverConnect( in)='DSN=AVUSDB;UID=avus;PWD=;', fDriverCompletion=0 DSN info: DSN='AVUSDB',server='localhost',port='5432',dbase='avusdb',user='avus',passwd='x' onlyread='0',protocol='6.4',showoid='0',fakeoidindex='0',showsystable='0' conn_settings='',conn_encoding='OTHER' translation_dll='',translation_option='' Global Options: Version='08.00.0101', fetch=100, socket=8192, unknown_sizes=0, max_varchar_size=254, max_longvarchar_size=8190 disable_optimizer=1, ksqo=1, unique_index=1, use_declarefetch=0 text_as_longvarchar=1, unknowns_as_longvarchar=0, bools_as_char=1 NAMEDATALEN=64 extra_systable_prefixes='dd_;', conn_settings='' conn_encoding='OTHER' conn=3620872, query=' ' conn=3620872, query='select version()' [ fetched 1 rows ] [ PostgreSQL version string = 'PostgreSQL 8.0.0 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special)' ] [ PostgreSQL version number = '8.0' ] conn=3620872, query='set DateStyle to 'ISO'' conn=3620872, query='set geqo to 'OFF'' conn=3620872, query='set extra_float_digits to 2' conn=3620872, query='select oid from pg_type where typname='lo'' [ fetched 0 rows ] conn=3620872, query='select pg_client_encoding()' [ fetched 1 rows ] [ Client encoding = 'LATIN1' (code = 8) ] // this is the default encoding. That's OK. conn=3620872, query='set client_encoding to 'UTF8'' // Why does the server set the client_encoding to be UTF8 here conn=3620872, PGAPI_DriverConnect(out)='DSN=AVUSDB;DATABASE=avusdb;SERVER=localhost;PORT=5432;UID=avus;PWD=;A6=;A7=100;A8=8192;B0=254;B1=8190;BI=0;C2=dd_;;CX=1b50fa9' conn=3620872, query='SELECT * FROM DB31 WHERE SELNR = '90001' ' [ fetched 1 rows ] conn=3620872, query='UPDATE DB31 SET SELTEXT = 'äöüßÄÖÜ update' , SELANW1 = ' ' , SELANW2 = ' ' , SELANW3 = ' ' , SELANW4 = ' ' , SELANW5 = ' ' , SELNUTZ = ' ' , SELANZ = '0' , SELSTEU = ' ' , SELETEXT = ' ' , SELKEY = ' ' WHERE SELNR = '90001' ' ERROR from backend during send_query: 'ERROR: could not convert UTF-8 character 0x00e4 to ISO8859-1' conn=3620872, query='ROLLBACK' STATEMENT ERROR: func=SC_execute, desc='', errnum=7, errmsg='Error
[GENERAL] Oracle's Virtual Private Database functionality
In the spirit of tell us what you're trying to do...
I'd like to mimic a subset of Oracle's Virtual Private Database
functionality (see
http://dba-oracle.com/oracle_news/2004_1_16_vpd_security.htm for an
overview) in Postgres: based on some per-connection setting, I'd like a
query to return a different set of rows. In VPD, the Oracle engine
actually applies a defined predicate (say, country_code='USA') to every
query. The idea is that a given set of users can only see rows in a
table that match this predicate, while the other rows are invisible to
them.
Now for the how I tried to do it part...
I thought I was on my way to doing this in Postgres by making use of
schemas and search_path:
CREATE USER user1 UNENCRYPTED PASSWORD 'user1';
CREATE SCHEMA AUTHORIZATION user1;
CREATE SCHEMA canada;
GRANT USAGE ON SCHEMA canada TO user1;
CREATE TABLE canada.row_limiter (country_code VARCHAR(3));
INSERT INTO canada.row_limiter VALUES('CAN');
GRANT SELECT ON canada.row_limiter TO user1;
CREATE SCHEMA usa;
GRANT USAGE ON SCHEMA usa TO user1;
CREATE TABLE usa.row_limiter (country_code VARCHAR(3));
INSERT INTO usa.row_limiter VALUES('USA');
GRANT SELECT ON usa.row_limiter TO user1;
\CONNECT - user1
SET SEARCH_PATH TO '$user',canada;
CREATE TABLE my_data (country_code VARCHAR(3), data NUMERIC);
INSERT INTO my_data VALUES('USA',11);
INSERT INTO my_data VALUES('USA',12);
INSERT INTO my_data VALUES('USA',13);
INSERT INTO my_data VALUES('CAN',21);
INSERT INTO my_data VALUES('CAN',22);
INSERT INTO my_data VALUES('CAN',23);
CREATE VIEW my_data_v AS
SELECT md.*
FROM my_data md,
row_limiter rl
WHERE rl.country_code=md.country_code;
SELECT * FROM my_data_v;
-- Looks great - I only see Canadian data!!
-- country_code | data
+--
-- CAN | 21
-- CAN | 22
-- CAN | 23
SET SEARCH_PATH TO '$user',usa;
SELECT * FROM my_data_v;
-- Darn, I still only see Canadian data :-(
-- country_code | data
+--
-- CAN | 21
-- CAN | 22
-- CAN | 23
\d my_data_v
View definition:
SELECT md.country_code, md.data
FROM my_data md, CANADA.row_limiter rl -- --ah, and here's the
reason...
WHERE rl.country_code::text = md.country_code::text;
It's apparent why: the view determines which table it's going to use at
view creation time, not at query time, so this method is no good.
Is there a right way to accomplish what I'm trying to do?
---(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
[GENERAL] PL/Java vs PL/pgSQL
1. Who is faster? 2. Who is recomended? ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] normal user dump gives error because of plpgsql
Hi, i have a normal user with rights to create a db. template1 contains language plpgsql. the user wants to - dump his db - drop his db - create it again - and use the dump file to fill it. it gives errors because of CREATE LANGUAGE statements inside the dump. How can i prevent that the dump contains CREATE LANGUAGE statements. They are not needed if template1 contains the language, right? I didnt found anythng in the archives even though i am sure not to be the first one having this problem. kind regards, janning here is what i did with 7.4.6: +++ AS DB SUPERUSER # createlang plpgsql template1 # createuser -Ad testuser CREATE USER # su testuser +++ AS TESTUSER $ createdb $ pg_dump -O -x /tmp/dump.sql $ dropdb $ createdb $ psql testuser /tmp/dump.sql SET SET SET ERROR: permission denied for language c ERROR: must be superuser to create procedural language ERROR: must be owner of schema public kind regards, janning -- PLANWERK 6 websolutions Herzogstraße 85, 40215 Düsseldorf Tel.: 0211-6015919 Fax: 0211-6015917 http://www.planwerk6.de/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] LIMIT and him usage
Hello, I don't have a problem. I would like know, how is it implemented on low-level (base) layer. I'm sorry for my english. Thank you for your answers LADiS On Mon, Mar 07, 2005 at 11:31:06AM +0100, Ladislav Linhart wrote: Create temprorary table before applyes LIMIT ? Exists any way for don't create temporary table with all records ? Could you describe what you're trying to do? What problem are you trying to solve? pgp9vTDQ5Mefh.pgp Description: PGP signature
Re: [GENERAL] pl sql to check if table of table_name exists
Shaun Clements wrote: Hi Hate to ask, but it isnt obvious to me from the documentation. How do I perform a query in pgplsql, to check it a table exists of a particular name. Thanks in advance Kind Regards, Shaun Clements -- A list of tables: SELECT schemaname, tablename FROM pg_tables; -- Returns true if a table exists: SELECT count(*)0 FROM pg_tables WHERE schemaname='...' AND tablename='...' -- Here's an untested function: CREATE OR REPLACE FUNCTION table_exists(TEXT, TEXT) RETURNS BOOLEAN AS ' DECLARE r RECORD; BEGIN SELECT INTO r count(*)0 AS exists FROM pg_tables WHERE schemaname='$1' AND tablename='$2' RETURN r.exists; END; ' LANGUAGE plpgsql STABLE; Check out http://www.postgresql.org/docs/8.0/static/catalogs.html for more info. Adam ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] Postgres - Tsearch2 Module
Hi, I am running Postgres 7.4.1 and donot have the TSEARCH2 module. If i download this module alone and then install, should i have to restart the database. / postmaster service. Thanks for your help, Ganti Raghunath. ---(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: [GENERAL] PostgreSQL still for Linux only?
Tope Akinniyi wrote: Hi, I am wondering at this display of extreme Linux mentality being displayed by the 'top bras' of the PostgreSQL community. And I ask, are we encouraging Windows use of PostgreSQL at all? At the moment? There are some known issues... Bear in mind that the Windows port is quite new, and much less tested than on other platforms. Take a look at tools being rolled out at PgFoundry on daily basis; all for Linux except the Windows installer. I ask myself what is being done to encourage PostgreSQL Windows users. Nothing is available to them except the Database and PgAdmin. No replication tool, no this, no that. I would assume that most of the Linux-only tools would work equally well on AIX, Solaris, *BSD, IRIX, etc. Not sure what you mean by Linux Sorry for this: Firebird provides equal tools for Linux and Windows users. We are not the one to tell the Windows users whether they need them. Until 8.0, PostgreSQL was not available natively on Windows. If you wanted to run it on Windows prior, you had to install it via Cygwin (a POSIX emulation layer). So the fact that there are few tools is mostly due to the newness of the software on that platform. Give it some time, and the tools will be ported. Whether Windows is bad or good; Linux is the angel and Windows the devil is not the issue here. PostgreSQL has gone the Windows way and must not be shown to be deficient. Again give it some time. However, as a second point, I would point out that *I* would never run production databases on Windows. This is because I don't trust the platform not to crash and mess up my data, and my data is worth the best hardware and software. PostgreSQL on Windows is, however, nice for developers who want to run it on their development efforts. But YMMV. And again, this is not the reason for the dearth of tools, but something to think about when deploying a solution. Best Wishes, Chris Travers Metatron Technology Consulting begin:vcard fn:Chris Travers n:Travers;Chris email;internet:[EMAIL PROTECTED] x-mozilla-html:FALSE version:2.1 end:vcard ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] New user: Windows, Postgresql, Python
Hi, I'm just starting to look at Postgresql. My platform (for better or worse) is Windows, and I'm quite interested in the pl/python support. However, when I run the binary installer, it is not offered to me as an option (it's there, but greyed out). The plpython.dll file is installed, however. When I check, it looks like plpython.dll is linked against Python 2.3. I have Python 2.4 installed on my PC, and I don't really want to downgrade. I suppose my first (lazy) question is, is there a Python 2.4 compatible plpython.dll available anywhere? Alternatively, is there a way I can build one for myself? I'm happy enough doing my own build (I have mingw and msys available), but I'd rather not build the whole of postgresql if possible, just for the sake of one DLL Thanks in advance, Paul. -- Bother, said the Borg, We've assimilated Pooh. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Disabling triggers in a transaction
Bruce Momjian wrote:
Geoffrey wrote:
Terry Lee Tucker wrote:
Tom,
Do you feel this is a safe method for disabling triggers in the rare cases
where one finds that it is prudent to do that? Do you think that the column,
reltriggers, is permanent fixture in pg_class? What is your advice on this?
I'd be quite interested in this as well. Can one depend on this column
in the future? Even if not, as long as one verifies it still exists, is
this a viable option for trigger control within a transaction?
(This guy Tucker comes up with some interesting stuff...) :)
It is the only known way to control triggers though it isn't regularly
tested by the developers.
There's another way, provided you're willing to modify your triggers.
If so, you can gain per-session control over any and all triggers and
functions.
For example, suppose I have a trigger that logs certain events, but I
also want to be able to turn off logging while I embezzle the
funds^H^H^H^H^H^H^H^H do maintenance. I still want the logging trigger
to work for other clients, just not mine.
You even get transaction support, so if you disable logging, then
rollback, logging will be turned back on in your next transaction.
Example:
BEGIN;
SELECT disable_logging();
UPDATE some_table ...;
if (some_error) {
// Don't have to remember to enable_logging()
ROLLBACK;
}
SELECT enable_logging();
COMMIT;
The catch is, my logging trigger must be changed to look like this:
BEGIN
IF logging_enabled() THEN
-- Do logging
END IF;
END;
It takes advantage of the fact that temporary tables can only be seen in
the session that creates them. You create a real 'session_vars' table
with default values and a flag that can tell you if you are looking at
the real or temporary table. Then copy it into a temporary table and
reset your flag to mark it as such. You can then update other flags in
your temporary table that are only seen by the current session. So,
when you disable_logging(), you'll get FALSE from logging_enabled(), but
all other sessions will get TRUE.
---
CREATE TABLE session_vars (
id INT PRIMARY KEY,
valueBOOL NOT NULL,
description CHAR(20)
);
---
INSERT INTO session_vars(id, value, description)
VALUES (1, TRUE, 'table is non-temp');
INSERT INTO session_vars(id, value, description)
VALUES (2, FALSE, 'logging enabled');
---
CREATE FUNCTION session_vars_is_real() RETURNS BOOLEAN AS
'SELECT value FROM session_vars WHERE id=1;'
LANGUAGE SQL STABLE;
---
CREATE FUNCTION setup_session_vars() RETURNS BOOLEAN AS '
BEGIN
-- We''ll only ever get TRUE from the real table ...
IF session_vars_is_real() THEN
EXECUTE \'CREATE TEMPORARY TABLE session_vars AS
SELECT * FROM session_vars\';
-- ... and FALSE from the temporary table
EXECUTE \'UPDATE session_vars SET value=FALSE WHERE id=1\';
END IF;
RETURN TRUE;
END;
' LANGUAGE plpgsql VOLATILE;
---
CREATE FUNCTION logging_enabled() RETURNS BOOLEAN AS
'SELECT value FROM session_vars WHERE id=2;'
LANGUAGE SQL STABLE;
---
CREATE FUNCTION enable_logging() RETURNS BOOLEAN AS '
DECLARE
r RECORD;
BEGIN
PERFORM setup_session_vars();
IF NOT logging_enabled() THEN
UPDATE session_vars SET value=TRUE WHERE id=2;
END IF;
RETURN TRUE;
END;
' LANGUAGE plpgsql VOLATILE;
---
CREATE FUNCTION disable_logging() RETURNS BOOLEAN AS '
BEGIN
PERFORM setup_session_vars();
UPDATE session_vars SET value=FALSE WHERE id=2;
RETURN TRUE;
END;
' LANGUAGE plpgsql VOLATILE;
---(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
[GENERAL] Question about accessing current row data inside trigger
Hello, I have a trigger function written in C. The trigger function is called via: CREATE TRIGGER after_update AFTER UPDATE ON some_table FOR EACH ROW EXECUTE PROCEDURE my_trigger_function(); Since the trigger is called after each row update the actual row data should be available in some way to the trigger. What functionality (SPI ?) do I use to use the column values from the current row in the actual trigger? thanks for any insight, Peter ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Stuck with a query...
[EMAIL PROTECTED] (Greg Stark) writes: Geoff Caplan [EMAIL PROTECTED] writes: Hi folks, Sorry to ask a newbie SQL question but I'm struggling... There's no efficient way to write this in standard SQL. However Postgres has an extension DISTINCT ON that would do it: select url,count(*) from (select distinct on (session_id) url from clickstream order by session_id,sequence_num desc ) group by url This isn't going to be a superfast query. It has to sort all the clickstream records by session and sequence, take just the last one, then probably sort those again. As an experiment I tried a more 'standard SQL' approach to this problem: SELECT url, count(1) FROM clickstream WHERE (session_id, sequence_num) IN (SELECT session_id, max(sequence_num) FROM clickstream GROUP BY session_id) GROUP BY url; On a table with about 100,000 rows this runs in about 720ms on my system , compared to the ON DISTICNT version which runs in about 1000ms. Adding an index on (session_id, sequence_num) reduced the run time to about 690ms, but made no difference to the DISTINCT ON version. With only about 10,000 rows, there's no appreciable difference. This surprised me, because I expected the DISTINCT ON to be better. -- Remove -42 for email ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] sql question
SELECT t1.id , t1.fref FROM t1 UNION ALL SELECT t2.id , t2.mref FROM t2 - Original Message - From: Steven Verhoeven To: pgsql-general@postgresql.org ; [EMAIL PROTECTED] Sent: Friday, March 11, 2005 4:36 AM Subject: [GENERAL] sql question Hi all My table definition : id | fref | mref--+---+-- 1 | 23 | 25 2 | 24 | 28 3 | 25 | 31 4 | 26 | 34My problem : i need a query that results in this : id | ref--+-- 1 | 23 1 | 25 2 | 24 2 | 28 3 | 25 3 | 31 4 | 26 4 | 34Do I need a crosstab-query ?Who can help me ? -- A computer is like an airconditioner. When windows open, it stops working ! -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- Steven Verhoeven, ICT Support Engineer Department for Molecular Biomedical Research (DMBR)VIB - Ghent University 'Fiers-Schell-Van Montagu' buildingTechnologiepark 927B - 9052 Ghent (Zwijnaarde)Belgium Tel : +32-(0)9-33-13.606 Fax : +32-(0)9-33-13.609 E-mail : [EMAIL PROTECTED] URL : http://www.dmbr.UGent.be
Re: [GENERAL] sql question
On Fri, 11 Mar 2005 11:36 pm, Steven Verhoeven wrote: Hi all My table definition : id | fref | mref --+---+-- 1 | 23| 25 2 | 24| 28 3 | 25| 31 4 | 26| 34 My problem : i need a query that results in this : id |ref --+-- 1 | 23 1 | 25 2 | 24 2 | 28 3 | 25 3 | 31 4 | 26 4 | 34 SELECT id, fref as ref FROM table UNION ALL SELECT id, mref as ref FROM table; Should do the trick. Do I need a crosstab-query ? Who can help me ? ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] sql question
On Fri, 11 Mar 2005 13:36:17 +0100, Steven Verhoeven [EMAIL PROTECTED] wrote: Hi all My table definition : id | fref | mref --+---+-- 1 | 23| 25 2 | 24| 28 3 | 25| 31 4 | 26| 34 My problem : i need a query that results in this : id |ref --+-- 1 | 23 1 | 25 2 | 24 2 | 28 3 | 25 3 | 31 4 | 26 4 | 34 Do I need a crosstab-query ? select id, fref from mytable union all select id, mref from mytable order by 1,2 klint. +---+-+ : Klint Gore: Non rhyming: : EMail : [EMAIL PROTECTED] : slang - the: : Snail : A.B.R.I.: possibilities : : Mail University of New England : are useless : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---+-+ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [NOVICE] [GENERAL] sql question
OOPs. I mean SELECT t1.id , t1.fref FROM t1 UNION ALL SELECT t1.id , t1.mref FROM t1 - Original Message - From: Vincent Hikida To: Steven Verhoeven ; pgsql-general@postgresql.org ; [EMAIL PROTECTED] Sent: Sunday, March 13, 2005 6:34 PM Subject: Re: [NOVICE] [GENERAL] sql question SELECT t1.id , t1.fref FROM t1 UNION ALL SELECT t2.id , t2.mref FROM t2 - Original Message - From: Steven Verhoeven To: pgsql-general@postgresql.org ; [EMAIL PROTECTED] Sent: Friday, March 11, 2005 4:36 AM Subject: [GENERAL] sql question Hi all My table definition : id | fref | mref--+---+-- 1 | 23 | 25 2 | 24 | 28 3 | 25 | 31 4 | 26 | 34My problem : i need a query that results in this : id | ref--+-- 1 | 23 1 | 25 2 | 24 2 | 28 3 | 25 3 | 31 4 | 26 4 | 34Do I need a crosstab-query ?Who can help me ? -- A computer is like an airconditioner. When windows open, it stops working ! -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- Steven Verhoeven, ICT Support Engineer Department for Molecular Biomedical Research (DMBR)VIB - Ghent University 'Fiers-Schell-Van Montagu' buildingTechnologiepark 927B - 9052 Ghent (Zwijnaarde)Belgium Tel : +32-(0)9-33-13.606 Fax : +32-(0)9-33-13.609 E-mail : [EMAIL PROTECTED] URL : http://www.dmbr.UGent.be
Re: [GENERAL] [PATCHES] A way to let Vacuum warn if FSM settings are low. [final?]
Ron Mayer wrote: Bruce Momjian wrote: You didn't like server_min_messages = 'notify'? I merely don't have a feeling for how much additional stuff verbose would be putting in the log files. You will probably see the creation of indexes and sequences, like this: test= create table test(x int primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index test_pkey for table test If it's a good practice for production systems to be logging NOTIFY's I'm happy with the change. Not really. The FSM message has a lot more interest than the other NOTIFY messages. My reasoning why I thought the log file was more useful was that only an admin with access to the log files could really do anything about the message anyway. The log file is useful, but I think showing the VACUUM user is _more_ useful than the log file. Also since the message happing occasionally is probably OK, yet if it happens a lot it's more likely worth looking into - I think the historical record of when it happened is more interesting than a one-time occurrence which is all you seen in the active session. Seems it could be made to be both client and log, but I can't think of any case were we do that now, and am unsure it is a good idea to add it just for this. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (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 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] [PATCHES] A way to let Vacuum warn if FSM settings are
Greg Stark wrote: Bruce Momjian pgman@candle.pha.pa.us writes: These new messages: NOTICE: max_fsm_relations(1000) equals the number of relations checked HINT: You have = 44 relations. Consider increasing the configuration parameter max_fsm_relations. NOTICE: the number of page slots needed (704) exceeds max_fsm_pages (2) HINT: Consider increasing the configuration parameter max_fsm_relations to a value over 704. VACUUM Those statements seem a tad strange with those numbers... I forced those to print for testing. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (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 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Oracle's Virtual Private Database functionality
On Wed, Mar 09, 2005 at 13:52:28 -0500, Doug Bloebaum [EMAIL PROTECTED] wrote: It's apparent why: the view determines which table it's going to use at view creation time, not at query time, so this method is no good. Is there a right way to accomplish what I'm trying to do? I think the normal way is to have the base tables have an extra attribute column, an extra table mapping users (or something about the current session) to attributes and a view for each base table combining the base table with user to attribute table so that only appropiate rows show up. If these need to be updatable, you will need to use rules on the views. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Postgres stored proc that extracts data from Oracle
On Thu, Mar 10, 2005 at 17:12:46 -0800, James [EMAIL PROTECTED] wrote: Do you guys have thoughts on how to implement this? I am not expecting an easy solution but I wish to get started asap. Any advice would be appreciated. My usual solution is to use a perl script which talks to both databases. The dblink module in postgres might also be useful to you. ---(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: [GENERAL] sql question
On Fri, 11 Mar 2005 13:26:07 +0100, Steven Verhoeven [EMAIL PROTECTED] wrote: My table definition : id | fref | mref --+---+-- 1 | 23| 25 2 | 24| 28 3 | 25| 31 4 | 26| 34 i need a query that results in this : id |ref --+-- 1 | 23 1 | 25 2 | 24 2 | 28 3 | 25 3 | 31 4 | 26 4 | 34 Do I need a crosstab-query ? Who can help me ? select id, fref as ref from my_table union select id, mref as ref from my_table; ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Question about accessing current row data inside trigger
On Tue, Mar 08, 2005 at 11:37:14AM -0800, peter Willis wrote: I have a trigger function written in C. The trigger function is called via: CREATE TRIGGER after_update AFTER UPDATE ON some_table FOR EACH ROW EXECUTE PROCEDURE my_trigger_function(); Since the trigger is called after each row update the actual row data should be available in some way to the trigger. What functionality (SPI ?) do I use to use the column values from the current row in the actual trigger? See Writing Trigger Functions in C and C-Language Functions in the documentation. Here are links to documentation for the latest version of PostgreSQL: http://www.postgresql.org/docs/8.0/interactive/trigger-interface.html http://www.postgresql.org/docs/8.0/interactive/xfunc-c.html -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] fied separator change from the shell command line
Edmund Bacon [EMAIL PROTECTED] writes: [EMAIL PROTECTED] writes: I'm trying to change the usal | table field separator from the shell command line: psql -d ect -f pl_lost.sql -o pl_lost.out.txt -F \t -U asaadmin Changing the field separator only works with unaligned output. You need to add a -A to your command line switches (or --no-align). Doing this, though, drops the column headers. There's a bit of a documentation issue I think: the psql reference page doesn't point out that -F only affects unaligned output. You could find that out by consulting the crossreferenced description of \pset fieldsep, but it seems better to mention it under -F too. I've changed the documentation accordingly in CVS HEAD. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Question about accessing current row data inside trigger
peter Willis [EMAIL PROTECTED] writes: I have a trigger function written in C. ... Since the trigger is called after each row update the actual row data should be available in some way to the trigger. Sure: tg_trigtuple or tg_newtuple depending on which state you want. See http://www.postgresql.org/docs/8.0/static/trigger-interface.html regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] [PATCHES] A way to let Vacuum warn if FSM settings are low. [final?]
Bruce Momjian pgman@candle.pha.pa.us writes: Ron Mayer wrote: My reasoning why I thought the log file was more useful was that only an admin with access to the log files could really do anything about the message anyway. The log file is useful, but I think showing the VACUUM user is _more_ useful than the log file. I think that reasoning is fundamentally unsound, because (a) a lot of people already do vacuuming via a cron job or autovacuum, and (b) autovacuum is definitely the wave of the future. So it's foolish to design this messaging around the assumption that there will be a human attentive to the on-line output from VACUUM. We should be ensuring that the message gets into the postmaster log --- whether it gets sent to the client is secondary. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
