Re: [GENERAL] Dump only functions...
Cristian Prieto [EMAIL PROTECTED] schrieb: Any of you knows is there is any way in pg_dump or anything to dump just the functions from a database? Net really a solution, but your defined functions are stored in information_schema.routines HTH, Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] a stored procedure ..with integer as the parameter
Am Mittwoch, den 26.10.2005, 09:37 +0530 schrieb surabhi.ahuja: what do u suggest i do then in that case? i mean how should i make a query - i mean how do i make a command? Need more details. What language are you using, what is the exact problem and so on. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Dump only functions...
Am Dienstag, den 18.10.2005, 15:31 -0600 schrieb Cristian Prieto: Any of you knows is there is any way in pg_dump or anything to dump just the functions from a database? pg_dump -Fc -v -f temp.dump yourdatabase pg_restore -l temp.dump | grep FUNCTION functionlist pg_restore -L functionlist temp.dump yourfunctions.sql of course you can just use your regular dump and so skip the first part. HTH Tino ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Autogenerated backup of a password protected database
Jamie Deppeler wrote: Since pg_dump doesnt support password is there a way that password can be supplied -w option. Currently writting a application to do backups in java as i need a solution that is cross platform. Any help would be greatfully recieved. You can use a .pgpass or pgpass.conf file - see the section of the manuals on libpq -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Getting Stated
Bob Pawley wrote: I am very new. I am running Postgresql 8 on Windows. I have managed to create tables and have searched all the documentation available to get to the next step I need to take. Would anyone on the list be interested in giving me a few pointers on a one to one basis? It's unlikely that a working consultant will design a system for you or provide training - most make their living doing that. However, there are some good sources of information I can point you towards. You already know about the manuals at http://www.postgresql.org/docs/ and they are a good source of reference information, but not really a place to learn about database design. A book: An Introduction to Database Systems by C.J. Date is a good guide to the underlying principles. As for SQL books - I'm not sure. Anyone else have any ideas? Finally - there is plenty of help in the lists if you have a specific problem. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Win32 libpq and ecpg thread safety
Hi, Is there a paragraph in the document talking about this? I've tried to look into it, but can't find it. Can anybody point me to it? Thanks, Qingqing ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] alt+F not working after calling pg_dump
Any idea how to make Alt key to work in my application
immediately after calling pg_dump ?
What parameters do you pass to CreateProcess()?
I use modified Ed Rauh API_APP class. Relevant part of of the calling code:
* This API call does the work. The parameters are as follows:
* lpszModuleName - ptr- file name of module to execute. Since we aren't
launching .CPLs, do not use
* lpszCommandLine - ptr- command to execute, as passed in method
* lpSecurityAttributesProcess - ptr- SECURITY_ATTRIBUTES structure for
Process. Pass a null pointer
* lpSecurityAttributesThread - ptr- SECURITY_ATTRIBUTES structure for
first thread. Pass a null pointer
* bInheritHandles - whether or not chlid inherits parent handles. Since
no SECURITY_ATTRIBUTES passed, default to FALSE
* dwCreateFlags - Process Creation Mode flag set. we use the default mode
at normal priority, ie 0
* lpvEnvironment - ptr- a set of environment strings as if a MULTI_SZ.
We don't set, so pass a null pointer
* lpszStartupDir - ptr- the starting directory. If none provided to
method, pass a null pointer
* lpStartInfo - ptr- a STARTUPINFO structure. We use one structure
member at times.
* lpProcessInfo - ptr- a PROCESS_INFORMATION structure, used to return
PID/PHANDLE detail. We use one member
DECLARE SHORT CreateProcess IN WIN32API AS CrPr ;
STRING lpszModuleName, ;
STRING @lpszCommandLine, ;
STRING lpSecurityAttributesProcess, ;
STRING lpSecurityAttributesThread, ;
SHORT bInheritHandles, ;
INTEGER dwCreateFlags, ;
STRING @lpvEnvironment, ;
STRING lpszStartupDir, ;
STRING @lpStartInfo, ;
STRING @lpProcessInfo
* Make default Structures for the CreateProcess call
*
* ProcessInfo - 4 bytes, a Process handle, a Thread Handle, a (DWORD)
ProcessId and a (DWORD) ThreadID
* we save the Process handle and return it to caller in
tnReturnProcessHandle
cProcessInfo = REPL(CHR(0),16)
* StartUpInfo is a 68 byte long complex structure; we either have 68 bytes
with a cb member (byte 1) 68
* or with cb of 68, dwFlag low order byte (byte 45) of 1, and low order
byte wShowWindow (byte 49) set to
* the SW_ value appropriate for the Window Mode desired.
* Use default of application
cStartUpInfo = CHR(68) + REPL(CHR(0),67)
LOCAL cstr
cstr= 'PGPASSWORD=xxx' +CHR(0)+ ;
'HOMEPATH='+GETENV('HOMEPATH') +CHR(0)+ ;
'SystemDrive='+GETENV('SystemDrive')+CHR(0)+ ;
'SystemRoot='+GETENV('SystemRoot')+CHR(0)+ ;
'USERDOMAIN='+GETENV('USERDOMAIN')+CHR(0)+ ;
'USERNAME='+GETENV('USERNAME')+CHR(0)+ ;
'USERPROFILE='+GETENV('USERPROFILE')+CHR(0)+ ;
'windir'+GETENV('windir')+CHR(0)+ CHR(0)
lResult = CrPr( 0, ;
cCommandLine, ;
0, 0, 0, 0, @cstr, ;
uFromDir, ;
@cStartUpInfo, ;
@cProcessInfo)
I think you're bit by the general windows behaviour that a new process
started by default will take focus away from your app, and it does not
necessarily return it when it quits.
I'm not sure that this is the reason because:
1. I changed commandline parameter to c:\wind98\system32\chcp.com . In
this case, Alt+F works.
2. Other keys like Enter, Ctrl+K works in my application.
3. After calling pg_dump I tried BringWindowToFront procedure below as
described in
http://www.tek-tips.com/faqs.cfm?fid=4262
This does not fix the problem.
I don't *think* it's pg_dump
specific. It'd be interesting to know both about flags above, and also
what happens if you just call a bat-file that does nothing and then
exits.
Specifiing empty .bat file directly as CreateProcess() command line causes
Createprocess to return error.
I tried to run chcp.com (randomly seleted windows console app which does not
wait for user input).
After chcp.com finishes , Alt+F works.
Andrus.
* http://www.tek-tips.com/faqs.cfm?fid=4262
* Force Window to Front (not blink in taskbar)
* You may have noticed that when you try and bring your application's
* window to the front using API calls (SetForeGroundWindow and
* BringWindowToTop) that it just blinks down in the taskbar. This
* applies to Windows 2000 and later Microsoft OS's (see MS
* Documentation below). Well, if you would rather have your
* applicaiton's window truly come to the front (and I'm sure you do)
* then here's a VFP workaround using API calls.
* MS Documentation for SetForegroundWindow reads as follows:
* Windows NT 5.0 and later: An application cannot force a window to the
* foreground while the user is working with another window. Instead,
* SetForegroundWindow will activate the window (see SetActiveWindow)
* and call the FlashWindowEx function to notify the user.
* ...and it appears that the same is true for BringWindowToTop.
* The only way Windows 2000 and Windows XP let you bring your
* application's window up to the front is if the thread it is running on
* is the thread of the foreground window at the time. So, you have to
* attach the thread of your application to the thread of the foreground
* window and then bring your application's
Re: [GENERAL] function DECODE and triggers
On Tue, 2005-10-25 at 18:31 +0200, Rafael Montoya wrote: Thanks for your answer, and if i have many options like decode (pre.C_EST,'01','U','02','M','03','W','04','D','05','O','06','S','') as Est do i have to write many else options in this way? select case when pre.C_EST = '01' THEN 'U' ELSE when pre-C_EST = '02' THEN 'M' ... END AS EST Rafael The syntax is CASE WHEN condition1 THEN value1 WHEN condition2 THEN value2 ... ELSE default_value END Oliver ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] function DECODE and triggers
Rafael Montoya wrote: Thanks for your answer, and if i have many options like decode (pre.C_EST,'01','U','02','M','03','W','04','D','05','O','06','S','') as Est do i have to write many else options in this way? select case when pre.C_EST = '01' THEN 'U' ELSE when pre-C_EST = '02' THEN 'M' ... END AS EST You could write a stored procedure that does what you want. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede //Showing your Vision to the World// ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] How to get the value in the lastvalue field
Hi,
Can anyone in the list tell me how to get the value in the lastvalue field of a sequence. I tried
select currval('field_seq');
but got an error message
ERROR: currval of sequence "field_seq" is not yet defined in this session.
I think currval will work only after an insert. I don't want to use nextval as this increases the lastvalue value.
Thanks in advance
Kind Regards,Venki
Re: [GENERAL] How to get the value in the lastvalue field
You can select it from the sequence's associated relation as from any
table... try:
select * from sequence_name;
However, be aware that what you see there is the situation only in your
transaction, and it is very possible that other transactions will use
higher values concurrently. So it really depends on what you want to do
if it is actually safe to do it this way...
Cheers,
Csaba.
On Wed, 2005-10-26 at 14:23, Venki wrote:
Hi,
Can anyone in the list tell me how to get the value in the lastvalue
field of a sequence. I tried
select currval('field_seq');
but got an error message
ERROR: currval of sequence field_seq is not yet defined in this
session.
I think currval will work only after an insert. I don't want to use
nextval as this increases the lastvalue value.
Thanks in advance
Kind Regards,
Venki
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] How to get the value in the lastvalue field
On 10/26/05 8:23 AM, Venki [EMAIL PROTECTED] wrote:
Hi,
Can anyone in the list tell me how to get the value in the lastvalue field of
a sequence. I tried
select currval('field_seq');
but got an error message
ERROR: currval of sequence field_seq is not yet defined in this session.
I think currval will work only after an insert. I don't want to use nextval as
this increases the lastvalue value.
That is correct. You can't call currval until you have called nextval
first. Why do you want to know? That might help answer the question.
Sean
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] How to get the value in the lastvalue field
Hi
thanks for the replies. The situation is as follows. We get backups from the production server and update the local database in the localserver but each time when we restore the database backup the sequence values are not getting updated properly. So what i thought was to write a function which will check the last value of the row in the table and compare it with the lastvalue of the sequnce and if it is not proper then update the lastvalue of the sequence to a proper value.
I Hope that i have explained the situation properly. If you have any other suggestion it will be greatly helpful as we are new to postgres and might be doing something wrong when restoring the database.
Regards
venki
---Original Message---
From: Csaba Nagy
Date: 10/26/05 18:24:48
To: Venki
Cc: Postgres general mailing list
Subject: Re: [GENERAL] How to get the value in the lastvalue field
You can select it from the sequence's associated relation as from any
table... try:
select * from sequence_name;
However, be aware that what you see there is the situation only in your
transaction, and it is very possible that other transactions will use
higher values concurrently. So it really depends on what you want to do
if it is actually safe to do it this way...
Cheers,
Csaba.
On Wed, 2005-10-26 at 14:23, Venki wrote:
Hi,
Can anyone in the list tell me how to get the value in the lastvalue
field of a sequence. I tried
select currval('field_seq');
but got an error message
ERROR:currval of sequence "field_seq" is not yet defined in this
session.
I think currval will work only after an insert. I don't want to use
nextval as this increases the lastvalue value.
Thanks in advance
Kind Regards,
Venki
Re: [GENERAL] How to get the value in the lastvalue field
If I was you, I would assign separate non-overlapping sequence ranges
for all servers from start. The ranges should be sized considering what
traffic each server will have. When one of the servers is close to use
up it's sequence range, assign it another one...
Then you will always know that one or other record is coming from which
server, and don't need to care when moving between servers. All other
scenarios are error prone.
Cheers,
Csaba.
On Wed, 2005-10-26 at 14:42, Venki wrote:
Hi
thanks for the replies. The situation is as follows. We get backups
from the production server and update the local database in the
local server but each time when we restore the database backup the
sequence values are not getting updated properly. So what i thought
was to write a function which will check the last value of the row in
the table and compare it with the lastvalue of the sequnce and if it
is not proper then update the lastvalue of the sequence to a proper
value.
I Hope that i have explained the situation properly. If you have any
other suggestion it will be greatly helpful as we are new to postgres
and might be doing something wrong when restoring the database.
Regards
venki
---Original Message---
From: Csaba Nagy
Date: 10/26/05 18:24:48
To: Venki
Cc: Postgres general mailing list
Subject: Re: [GENERAL] How to get the value in the lastvalue field
You can select it from the sequence's associated relation as from any
table... try:
select * from sequence_name;
However, be aware that what you see there is the situation only in
your
transaction, and it is very possible that other transactions will use
higher values concurrently. So it really depends on what you want to
do
if it is actually safe to do it this way...
Cheers,
Csaba.
On Wed, 2005-10-26 at 14:23, Venki wrote:
Hi,
Can anyone in the list tell me how to get the value in the lastvalue
field of a sequence. I tried
select currval('field_seq');
but got an error message
ERROR: currval of sequence field_seq is not yet defined in this
session.
I think currval will work only after an insert. I don't want to use
nextval as this increases the lastvalue value.
Thanks in advance
Kind Regards,
Venki
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] How to get the value in the lastvalue field
On 10/26/05 8:42 AM, Venki [EMAIL PROTECTED] wrote: Hi thanks for the replies. The situation is as follows. We get backups from the production server and update the local database in the local server but each time when we restore the database backup the sequence values are not getting updated properly. So what i thought was to write a function which will check the last value of the row in the table and compare it with the lastvalue of the sequnce and if it is not proper then update the lastvalue of the sequence to a proper value. I Hope that i have explained the situation properly. If you have any other suggestion it will be greatly helpful as we are new to postgres and might be doing something wrong when restoring the database. I may be wrong, but I thought that sequences were also dumped and restored with database dumps, unless you are dumping and restoring only data. Is that the case? Sean ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Win32 libpq and ecpg thread safety
Qingqing Zhou wrote: Hi, Is there a paragraph in the document talking about this? I've tried to look into it, but can't find it. Can anybody point me to it? Well, libpq has a section: http://candle.pha.pa.us/main/writings/pgsql/sgml/libpq-threading.html I don't see a thread section for ecpg, but I see several mentions of threading in ecpg.sgml. If you are asking about Win32-specific threading, there is no documentation about it because it works just like Unix threading. At least, that is what I am told. -- 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 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] the best way to catch table modification
Am Dienstag, 25. Oktober 2005 19:40 schrieb David Gagnon: Hi, I posted on the same subject a month ago . .you can search for the current title in the JDBC mailing list [JDBC] implementing asynchronous notifications PLEASE CONFIRM MY I ended using statement-level trigger. I haven't found another way to do it . Regards /David Marek Lewczuk wrote: Hello, I'm implementing db-queries caching system - for this I need to know each table's modification time (or at least modification counter). I know that I can make a statement-level trigger, which will update a table with tables modification times - however this is inefficient if many inserts or updates are made on single table (in single transaction). The best would be some kind of transaction-level trigger, but this is not available. Are there any other, better options ? What did you mean with many inserts or updates? Did you mean statements which modify or insert many rows but are still one single statement: you could use rules instead. I think rules are much more powerful than triggers and they are much faster if a statement affects many rows. Triggers fires for each row, rules are just modifying the original statement. so usually if you can handle the load of the statements you should handle the loads of statements rewritten by rules, too. Rules are usually much more efficient than triggers. And they are much more relational in my opinion. kind regards, janning ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Map of Postgresql Users (OT)
On Tue, 25 Oct 2005, Claire McLister wrote: Thanks. I looked at the Mapserver maps, and at first glance it seems Google Maps API provides better map images and more interactive features (zooming, panning, JS popups, satellite/map views, etc.) So, what would be the advantage of Mapserver apart from it being based on Open Source? More vector datasets to overlay more control of the output map. For example in New Zealand, we have access to higher resolution imagery than google maps, as well as detailed vector data. But I also think it could make a nice showcase for an Open Soure suite, with Postgres, PostGIS, GEOS, GDAL/OGR, Proj4, Mapserver, using global local datasets, etc. We use Postgresql server to store the data. Currently, we do not use PostGIS as we do not do much geographical queries. Over time we could move in that direction to offer better geographical query support. Posting this (or a more detailed map) on the Postgresql web site sounds like a great idea. We could even allow people to add markers for their locations, and even enter more information about themselves to help postgresql users connect with each other. One potential opportunity is for users to quickly find consultants in their own area. How would we initiate this activity? I'll forward this to a few people in the Open Source web mapping community who may be able to assist. Best wishes Claire On Oct 24, 2005, at 2:28 PM, Brent Wood wrote: How about using Postgres/PostGIS UMN mapserver for a fully Open Source software Postgres based map? For a map like the one at http://www.qgis.org/index.php?option=com_wrapperItemid=53 perhaps on the Postgres web site? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Quickly calculating row size of a table?
Thanks very much for both your posts.
I tried both:
First, I performed a full vacuum on the entire database then
dbn=# SELECT relname, relpages*8192/reltuples from pg_class where
reltuples 0 and relname not like 'pg%';
atablename| 2047.95
The first SQL statement gave me a rough idea of the tuple size while
the second SQL statement seemed to give out more details about the
table and its tuples.
dbn=# select 'atablename' as table_name, * from
pgstattuple('public.atablename');
-[ RECORD 1 ]--+--
table_name | atablename
table_len | 160137216
tuple_count| 78194
tuple_len | 129868251
tuple_percent | 81.1
dead_tuple_count | 0
dead_tuple_len | 0
dead_tuple_percent | 0
free_space | 29419740
free_percent | 18.37
129868251 / 78194 = 1660.85
There's still a difference between the two reported tuple size of
atablename: 2047.95 - 1660.85 = 387.10
Can someone shed some more light on this and which one more closely
approximates the size of the tuples?
Jared
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] [ODBC] versions of oDBC driver
Hello.
After I tried different things, I finally figured out where is the problem
with connection string: Driver={PostgreSQL} must be changed to
Driver={PostgreSQL Unicode}. Now it works.
But this new connection string works only with Postgres 8.1, while it
doesn't work with Postgres 8.0...
I must say that current documentation lacks with information about
connection string parameters. Everything I could find about it was quite old
and not sufficient. It would be really nice if someone competent would write
some document regarding ODBC connection string parameters to explain their
meaning for dummies like me. I suppose I'm not the only one trying to use
MS Access with PostgreSQL. I think it is quite good combination for
hobbiests and people who are not programmers but want to create some
specific solutions for their job. MS Access is very easy to use and learn
and is widespread also, so supporting people in such efforts would
definitely increase popularity of PostgreSQL. Understanding ODBC connection
string parameters is essential for that.
Regarding connection parameters, for example, I would kindly ask you to tell
me which parameters are not neccessary in my connection string, so that I
can remove it, in order to allow more space in connection string. You have
already mentioned: Secondarily, I find that not every parameter is
essential for the connection string. You might trying dropping your
strConnParams, and if that helps, debugging them one at a time, or by
halves., so I would like to optimize my connection string to allow more
space for really important parameters in limited connection string in
Access...
This is my connection string:
strConnInfo = ODBC;Driver={PostgreSQL Unicode};Server= SERVER ;Port=
PORT ;Database= DATABASE ;
strConnUserPass = Uid= USERNAME ;Pwd= PASSWORD ;
strConnParms = A0=0;A1=6.4;A2=0;A3=0;A4=1;A5=0;A6= A6 ;A7=100;A8=
SOCKET ;A9=1; _
B0=254;B1=8190;B2=0;B3=0;B4=1;B5=1;B6=0;B7=1;B8=0;B9=0; _
C0=0;C1=0;C2=dd_;
strConnection = strConnInfo strConnUserPass strConnParms
What can I remove from it?
Thanks,
Zlatko
- Original Message -
From: Greg Campbell [EMAIL PROTECTED]
To: Zlatko Matić [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Friday, October 21, 2005 5:12 PM
Subject: Re: [ODBC] versions of oDBC driver
Did you changed the Postgresql server and the client ODBC driver at the same
time?
My guess is that the pg_hba is not configured to let you in,...
I would make sure the PostgreSQL server is set to having connection logging
enabled, and see what type of
errors it is throwing on the server.
Secondarily, I find that not every parameter is essential for the connection
string. You might trying
dropping your strConnParams, and if that helps, debugging them one at a
time, or by halves.
Zlatko Matić wrote:
Hello.
The error is error 3151: ODBC--connection to '{PostgreSQL}Localhost'
failed.
I have a form with text boxes for entering Database name, IP adress,
username, password etc. When a user push the confirmation button, a
function ConnectionToServer is executed to: a) create connection string,
b) to check whether connection string works, c) to call functions for
relinking linked tables and adjusting connection string in pass-through
queries. The code is following:
Option Compare Database
Public strConnection As String
Function ConnectionToServer(SERVER As String, PORT As String, SOCKET As
String, DATABASE As String, USERNAME As String, PASSWORD As String,
ENCODING As String) As Boolean
Dim db As Object
Dim qdf As Object
Dim qdfSQL As String
Dim rs As Object
Dim strConnInfo As String
Dim strConnUserPass As String
Dim strConnParms As String
Dim CurrentUser As String
Dim A6 As String
On Error GoTo ErrorHandler
DoCmd.Hourglass True
Set db = CurrentDb
' PG_ODBC_PARAMETER ACCESS_PARAMETER
' *
' READONLYA0
' PROTOCOLA1
' FAKEOIDINDEXA2 'A2 must be 0 unless A3=1
' SHOWOIDCOLUMN A3
' ROWVERSIONING A4
' SHOWSYSTEMTABLESA5
' CONNSETTINGSA6
' FETCH A7
' SOCKET A8
' UNKNOWNSIZESA9 ' range [0-2]
' MAXVARCHARSIZE B0
' MAXLONGVARCHARSIZE B1
' DEBUG B2
' COMMLOG B3
' OPTIMIZER B4 ' note that 1 = _cancel_ generic
optimizer...
' KSQOB5
' USEDECLAREFETCH B6
' TEXTASLONGVARCHAR B7
' UNKNOWNSASLONGVARCHAR B8
' BOOLSASCHAR B9
' PARSE C0
' CANCELASFREESTMTC1
' EXTRASYSTABLEPREFIXES C2
Select Case ENCODING
Case DEFAULT
A6 =
Case UNICODE
A6 = CLIENT_ENCODING=UNICODE
Case SQL_ASCII
A6 = CLIENT_ENCODING=SQL_ASCII
Case
[GENERAL] querying PostgreSQL version?
Hello. Is there any way to check the version of PostgreSQL by a query? Maybe by querying catalog tables? Thanks, Zlatko
[GENERAL] Need help with INOUT mis-understanding
Yes, I'm a Newbie but I really like it so far except a few misunderstandings I have, like INOUT. (I am using 8.1 beta 3 on both WIN and FC w/ pgadmin III.) I cannot use multiple in/out/inouts within functions to return results. How can I use INOUTS the right way? Here I thought I'd get an updated value from func2 within func1: -- FUNC1 CREATE OR REPLACE FUNCTION func1(INOUT var_1 int4) AS ' DECLARE var_X RECORD; var_Y int4; BEGIN var_Y := var_1; RAISE NOTICE ''var_1 starts as %'',var_1; var_X := func2(var_1,var_Y); RAISE NOTICE ''var_X Now is %'',var_X; RAISE NOTICE ''var_1 Now is %'',var_1; END --test__inout ' LANGUAGE 'plpgsql' VOLATILE; ---FUNC2 CREATE OR REPLACE FUNCTION func2(INOUT var_1 int4, INOUT var_Y int4) AS ' BEGIN var_1 = var_1 + var_1; RAISE NOTICE ''var_1 in func2 is %'',var_1; END --func2 ' LANGUAGE 'plpgsql' VOLATILE; -RESULTS: NOTICE: var_1 starts as 5 NOTICE: var_1 in func2 is 10 CONTEXT: PL/pgSQL function func1 line 7 at assignment NOTICE: var_X Now is (10,5) NOTICE: var_1 Now is 5 Total query runtime: 30 ms. Data retrieval runtime: 0 ms. 1 rows retrieved. (Shouldn't var_1 = 10 Now?) ANY ADVICE? Where can I find out how to handle them correctly? I have tried using Return and such but I don't get it. (Been reading all the online and support help I can find.) Does PL/PGSQL handle INOUTS the same as ORACLE PL/SQL? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] How to use LIKE and $1 in a function ?
I am learning PostgreSQL with an O'Reilly book and I have a problem
with functions : I created a very basic function to look for a name in
a table :
If your reading that book then you're not going to be using INOUT -
which is what I'm learning. But here is how you could do it with inout
in postgres 8.1:
CREATE OR REPLACE FUNCTION func1(INOUT var_1 varchar) AS
'
DECLARE
X VARCHAR(50);
BEGIN
var_1 = var_1 || ''%'';
SELECT INTO X Books FROM testtable WHERE Books LIKE var_1;
RAISE NOTICE ''found X = %'', X;
var_1 := X;
END
'
LANGUAGE 'plpgsql' VOLATILE;
- --- -
SELECT INTO returns first returned row. So if you called SELECT
func1('P'); you'd get any books starting with the letter P. I'm sure
there are many ways but I hope this helps some.
Troy -
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] newbie question: reading sql commands from script
On Tue, 25 Oct 2005, basel novo wrote: What is the equivalent of the mysql 'source' command for reading sql commands from ascii script files? I have not used mysql, so am not familiar with the source command, but to have postgres run a set of sql statements/queries from a file you can: psql DB -f filename also, to run a single command from a script (or commandline) psql DB -c sql command Cheers, Brent Wood ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Looking for a command to list schemas
Hello, We are presently experimenting with Postgresql schemas... We used basic commands like \d to list info from all tables/sequence/owner in a db... The schema info was always public since we never created one... In version 7.3.4 and with a test db where schemas were created, the same \d does not show the schema of each table... the \dp doesn't help width the privileges... It seems that once we created the schema and created a table with this schema, if we forget about it, the is no way to list the schema or to have the tables/sequence with it's associated schema... Can someone tell us how we can get this important info? Thanks! -- Mark ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] querying PostgreSQL version?
select version() Zlatko Matić wrote: Hello. Is there any way to check the version of PostgreSQL by a query? Maybe by querying catalog tables? Thanks, Zlatko
Re: [GENERAL] Dump only functions...
I'm not sure of your intentions but just as a suggestion, download PGADMIN III. You can select a SCHEMA and do a BACKUP with many options for your custom editing. (I'm new and that's the fastest way if you have hundreds of functions procedures like me) you can tweek the generated backup file and reinsert what you need... Just my 2 cents Troy ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] How Do I install the Admin Module (pgAdmin III)
I did 8.1 beta3 for windows and PGAdmin III was nicely included. Then when I downloaded Postgresql 8.1 on Fedora Core it wasn't there so I downloaded it seperatly and it works pretty much the same. ( I downloaded the second to lated version because of installer errors on my system.) Hope is useful - good luck Troy ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] querying PostgreSQL version?
On Wed, Oct 26, 2005 at 02:52:36PM +0200, Zlatko Matić wrote: Is there any way to check the version of PostgreSQL by a query? Maybe by querying catalog tables? Easier: select version(); Peter ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] querying PostgreSQL version?
On 10/26/05 8:52 AM, Zlatko Matić [EMAIL PROTECTED] wrote: Hello. Is there any way to check the version of PostgreSQL by a query? Maybe by querying catalog tables? Select version(); ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] querying PostgreSQL version?
am 26.10.2005, um 14:52:36 +0200 mailte Zlatko Mati? folgendes: Hello. Is there any way to check the version of PostgreSQL by a query? Maybe by querying catalog tables? Thanks, select version(); HTH, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Looking for a command to list schemas
Cosmopo [EMAIL PROTECTED] schrieb: It seems that once we created the schema and created a table with this schema, if we forget about it, the is no way to list the schema or to have the tables/sequence with it's associated schema... Can someone tell us how we can get this important info? \dn in psql list all schemas. HTH, Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] querying PostgreSQL version?
rnd=# select version(); version -- PostgreSQL 7.4.6 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-49) (1 row) On Wednesday 26 October 2005 08:52 am, Zlatko Matić saith: Hello. Is there any way to check the version of PostgreSQL by a query? Maybe by querying catalog tables? Thanks, Zlatko ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Looking for a command to list schemas
On Tue, Oct 25, 2005 at 11:25:22AM -0700, Cosmopo wrote: Hello, We are presently experimenting with Postgresql schemas... We used basic commands like \d to list info from all tables/sequence/owner in a db... The schema info was always public since we never created one... \dn list schemas \dt *.* list all tables in all schemas \z *.* list priveledges for all tables in all schemas etc... Is this helpful? -- 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. pgpRJDNAWu7xl.pgp Description: PGP signature
Re: [GENERAL] querying PostgreSQL version?
am 26.10.2005, um 10:22:27 -0300 mailte Rodrigo Gonzalez folgendes: !DOCTYPE html PUBLIC -//W3C//DTD HTML 4.01 Transitional//EN html head meta content=text/html;charset=ISO-8859-2 http-equiv=Content-Type /head body bgcolor=#ff text=#00 select version()br br Zlatko Mati? wrote: blockquote cite=[EMAIL PROTECTED] type=cite meta http-equiv=Content-Type content=text/html; meta content=MSHTML 6.00.2900.2769 name=GENERATOR style/style divfont face=Arial size=2Hello./font/div div /div divfont face=Arial size=2Is there any way to check the version of PostgreSQL by a query? Maybe by querying catalog tables?/font/div divfont face=Arial size=2Thanks,/font/div div /div divfont face=Arial size=2Zlatko/font/div /blockquote /body /html Please, no HTML! Thanks, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Dump only functions...
Tino Wildenhain wrote:
Am Dienstag, den 18.10.2005, 15:31 -0600 schrieb Cristian Prieto:
Any of you knows is there is any way in pg_dump or anything to dump
just the functions from a database?
pg_dump -Fc -v -f temp.dump yourdatabase
pg_restore -l temp.dump | grep FUNCTION functionlist
pg_restore -L functionlist temp.dump yourfunctions.sql
of course you can just use your regular dump and so
skip the first part.
HTH
Tino
Another way of dumping only the functions, based off the view pga_functions:
-- view definition
create or replace view pga_functions as
select
l.lanname as language,
n.nspname||'.'||p.proname||'('
||pg_catalog.oidvectortypes(p.proargtypes)||')' as name,
t.typname as returntype,
'\n\n'||'CREATE OR REPLACE FUNCTION
'||n.nspname||'.'||p.proname||'('||pg_catalog.oidvectortypes(p.proargtypes)||')\n'||
' RETURNS '||t.typname||' AS'||'\n''\n' ||(select case when lanname 'c'
then replace(prosrc,'\'','\\\'') else replace(prosrc,'\'','\\\'')||'.so'
end)||'\n''\n'||' LANGUAGE ''' || l.lanname || ''' VOLATILE;\n' as source
from pg_proc p, pg_type t, pg_namespace n, pg_language l
where p.prorettype = t.oid and p.pronamespace = n.oid
and p.prolang = l.oid;
dev=# select source from pga_functions where name like 'public%'
dev-# \o dump_all_functions.sql;
This will dump public's functions to a file. You may want to play with the
settings of view etc to get the formatted results you want.
Note: I did not create this view, I found it in the archives a while back, along
with pga_objects, pga_columns, and pga_views.
Cheers,
Bricklen
--
___
This e-mail may be privileged and/or confidential, and the sender does
not waive any related rights and obligations. Any distribution, use or
copying of this e-mail or the information it contains by other than an
intended recipient is unauthorized. If you received this e-mail in
error, please advise me (by return e-mail or otherwise) immediately.
___
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] Map of Postgresql Users (OT)
Hi Joshua, No, you should not need a plugin. Can you open the Firefox Javascript console and see if you get errors in Javascript? The mapping relies heavily on Javascript and sometimes that gives errors. I just tried it on Firefox 1.0 and it showed up okay. Let me know if the problem persists and we can try to resolve it. Claire On Oct 25, 2005, at 4:59 PM, Joshua D. Drake wrote: I do like the fact you pulled the names off the emails as well as the email addresses. That is important. Is there a plugin I am missing to make this work in Firefox? I just get a blank grey screen. Sincerely, Joshua D. Drake -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Where is the webaddress for the most recent postgresql version?
Good morning, May I know the link/web address where I can get the most recent postgresql version information please? I'd love also to get the online manual address about updating old version to the new one and the patches. Thanks a lot, Emi ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Where is the webaddress for the most recent postgresql
Hi, On Wed, 26 Oct 2005, Emi Lu wrote: May I know the link/web address where I can get the most recent postgresql version information please? http://www.PostgreSQL.org/ftp I'd love also to get the online manual address about updating old version to the new one and the patches. http://www.PostgreSQL.org/docs . You'll find the relevant info in the Release Notes part of each documentation set. Regards, -- Devrim GUNDUZ Kivi Bilişim Teknolojileri - http://www.kivi.com.tr devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Need help with INOUT mis-understanding
Troy [EMAIL PROTECTED] writes: Does PL/PGSQL handle INOUTS the same as ORACLE PL/SQL? Probably not, if the way you seem to expect it to work is like Oracle. An INOUT parameter isn't some sort of modifiable by-reference variable, it's just a shorthand for declaring an IN parameter and an OUT parameter. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Quickly calculating row size of a table?
Jared Evans [EMAIL PROTECTED] writes: dbn=# SELECT relname, relpages*8192/reltuples from pg_class where reltuples 0 and relname not like 'pg%'; That calculation lumps free space (and page header overhead and so on) into the size of the tuples. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Need help with INOUT mis-understanding
Tom Lane wrote: Troy [EMAIL PROTECTED] writes: Does PL/PGSQL handle INOUTS the same as ORACLE PL/SQL? Probably not, if the way you seem to expect it to work is like Oracle. An INOUT parameter isn't some sort of modifiable by-reference variable, it's just a shorthand for declaring an IN parameter and an OUT parameter. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match Tom, I have noticed this as well, if I declare OUT params I can modify them to my hearts content before they go out, however if you declare it as a INOUT you can't modify it because it is declared as a constant. I would expect the behavior of a INOUT to be ths same as OUT, not the same as a IN. There have been many times I really could have even used IN params that where not declared as CONST. How difficult would it be to have INOUT params not declared as CONST? Thanks, Tony Caduto ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Need help with INOUT mis-understanding
Tony Caduto [EMAIL PROTECTED] writes: I have noticed this as well, if I declare OUT params I can modify them to my hearts content before they go out, however if you declare it as a INOUT you can't modify it because it is declared as a constant. Uh, I don't think so. /* input argument vars are forced to be CONSTANT */ if (argmode == PROARGMODE_IN) ((PLpgSQL_var *) argvariable)-isconst = true; Can you provide a test case that misbehaves that way? regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] FoxPro in WINE to Postgresql on LINUX?
Can you use Postgres savepoints from VFP ?
sqlexec('ROLLBACK TO mysavepoint') and even sqlexec('ROLLBACK') cause C5
error.
Andrus.
William Yu [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
Here's what I do. Once I get a good config, I just tarball my .wine dir
and untarball it for any Linux/WINE box I need to get running.
For users already running WINE w/ their own app configs already, then
copying the files manually into the appropriate dirs and then appending
the necessary text into the various config files will also work. WINE
doesn't store registry settings in Windows' binary registry format.
Instead, it's all flat text so installs are scriptable.
Andrus wrote:
Is it possible to create zero config installation by using connecton
string and simply including odbc dll files (odbc32.dll, odbcint.dll,
odbccp32.dll ) with application ?
This is a lot simpler for end user, no manual config required.
William Yu [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
[EMAIL PROTECTED] wrote:
VFP will run in WINE up to VFP version 8. But the people I talk to that
are doing this are using VFP's native tables. Does anyone know if it's
possible to run VFP8 in WINE and connect to Postgresql on a Linux
server? I think part of the question here is how would WINE emulate the
ODBC to get to the Postgresql server.
I can confirm FoxPro running under Wine/Linux can access Postgres via
ODBC and performs nearly comparable to FoxPro under Windows. I got 90%+
performance on some of my data processing programs. Where you do get
really slow is accessing DBFs over the network because NFS is so
inefficient.
It doesn't work with UnixODBC so you need to install the Postgres Windows
ODBC driver under WINE. Double-click on the ODBC installer, then type:
wine odbcad32.exe to launch the config file.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Why database is corrupted after re-booting
Yesterday computer running Postgres re-boots suddenly. After that, select * from firma1.klient returns ERROR: invalid page header in block 739 of relation klient I have Quantum Fireball IDE drive, write caching is turned OFF. I have Windows XP with FAT32 file system. I'm using PostgreSQL 8.0.2 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special) Why the corruption occurs ? How to avoid data corruption? Will NTFS file system prevent all corruptions ? If yes, how to convert FAT32 to NTFS without losing data in drive ? Andrus. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Why database is corrupted after re-booting
To change partition types you need to re-format (resetting partitions will lose data structure - reformat required). Troy, Whole my IDE drive is 20 GB FAT32 C: drive booting XP I have a lot of data in this drive so it is not possible to re-format. Also I do'nt want to create two logical disks in single drive. Is this prevents data corruption for Postgres, is there some utility which can convert C: drive to NTFS ? Can Partition Magic help ? Andrus ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] escape string type for upcoming 8.1
Bruce Momjian wrote: E'' is more a marker than a type. I realize making E a type might work, but it seems unusual. What we could do is backpatch E'' to 8.0.X as a no-op like it will be in 8.1. Bruce, Is it possible in the 8.1 betas to 'switch on' on the standard SQL escape behavior? This is from the use-case perspective of someone who does not have backwards compatibility concerns, rather, I'd like to preemptively forward-port / certify an app from another databases, onto PostgreSQL -- so all I need to do is switch that config on, if possible. From the changelog: While this release does not change the default? handling of backslashes in strings, it does add new configuration parameters to help users migrate applications for future releases: o standard_conforming_strings .. o escape_string_warning .. The standard_conforming_strings value is read-only. The last quoted sentence seems to answer my question (as no), but hopeful optimism is my motto :-) If it is indeed readonly, can it be made 'writable' before the 8.3 release where is would be made the default behavior? For that matter, if the current backslash behavior stayed as the default for pre-8.3 releases, and the patches are backported, I don't see any harm to the old-style apps/users; yet the correct behavior option is a useful opt-in one (one that I would like to try, now, even on 8.1). Thank you, -Ken ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Why database is corrupted after re-booting
On Wed, 2005-10-26 at 10:27, Andrus wrote: Yesterday computer running Postgres re-boots suddenly. After that, select * from firma1.klient returns ERROR: invalid page header in block 739 of relation klient I have Quantum Fireball IDE drive, write caching is turned OFF. I have Windows XP with FAT32 file system. I'm using PostgreSQL 8.0.2 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special) Why the corruption occurs ? How to avoid data corruption? Will NTFS file system prevent all corruptions ? If yes, how to convert FAT32 to NTFS without losing data in drive ? If your machine crashes, FAT makes no promises that it will come back up, uncorrupted or otherwise. NTFS has journaling, and should provide more safety. Turning off the write cache is the right thing to do. Putting your db on FAT is the (very very) wrong thing to do. I would run the ntfs converter if I were you, but you'll likely need a backup to get your database back on its feet again. Don't forget the backups. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Why database is corrupted after re-booting
Talking with various people that ran postgres at different times, one thing they always come back with in why mysql is so much better: postgresql corrupts too easily and you lose your data. Personally, I've not seen corruption in postgres since 5.x or 6.x versions from several years ago. And, I've seen corruption on mysql (though I could not isolate between a reiserfs or mysql problem - both with supposedly stable releases installed as part of a distro). Is corruption a problem? I don't think so - but I want to make sure I haven't had my head in the sand for a while. :) I realize this instance appears to be on Windows, which is relatively new as a native Windows program. I'm really after the answer on more mature platforms (including Linux). Thanks, Greg On Wed, 2005-10-26 at 18:27 +0300, Andrus wrote: Yesterday computer running Postgres re-boots suddenly. After that, select * from firma1.klient returns ERROR: invalid page header in block 739 of relation klient I have Quantum Fireball IDE drive, write caching is turned OFF. I have Windows XP with FAT32 file system. I'm using PostgreSQL 8.0.2 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special) Why the corruption occurs ? How to avoid data corruption? Will NTFS file system prevent all corruptions ? If yes, how to convert FAT32 to NTFS without losing data in drive ? Andrus. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Why database is corrupted after re-booting
On Wed, 2005-10-26 at 18:27 +0300, Andrus wrote: Yesterday computer running Postgres re-boots suddenly. After that, select * from firma1.klient returns ERROR: invalid page header in block 739 of relation klient I have Quantum Fireball IDE drive, write caching is turned OFF. I have Windows XP with FAT32 file system. I'm using PostgreSQL 8.0.2 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special) Why the corruption occurs ? Most likely because the IDE was caching the information. IDE drives sometimes lie about having caching turned on or off. How to avoid data corruption? You could also have a bad drive. Will NTFS file system prevent all corruptions ? No. Sincerely, Joshua D. Drake If yes, how to convert FAT32 to NTFS without losing data in drive ? Andrus. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Why database is corrupted after re-booting
Gregory Youngblood wrote: Is corruption a problem? I don't think so - but I want to make sure I haven't had my head in the sand for a while. :) I realize this instance appears to be on Windows, which is relatively new as a native Windows program. I'm really after the answer on more mature platforms (including Linux). crappy disk drives and bad windows file systems, nothing more. postgresql is rather corruption free when the surrounding hardware and software environments are well chosen. if you do have to use cheap disk drives/controllers, then a battery backup unit that shuts the server down automagically is a really really good idea. getting that IDE cache flushed is pretty high on the priority list. richard ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Why database is corrupted after re-booting
Gregory Youngblood [EMAIL PROTECTED] writes: Is corruption a problem? I don't think so - but I want to make sure I haven't had my head in the sand for a while. :) I realize this instance appears to be on Windows, which is relatively new as a native Windows program. I'm really after the answer on more mature platforms (including Linux). It's been quite some time since I've seen an instance of data corruption that appeared to be due to a Postgres bug. (At least, not corruption in tables ... we've had some index bugs, but those you can always fix with REINDEX.) I have seen lots of cases that seemed to be due to hardware or OS misfeasance, eg, disk sectors filled with data that didn't come from Postgres at all. You can reduce your exposure by making sure things are correctly configured (eg, disable write caching, or better yet don't use consumer-grade drives at all). In the end there's no substitute for a good backup policy ;-) AFAICS mysql will have exactly the same problems. So will oracle or any other DB. Oracle may have a better looking track record, but that's probably because people don't try to run it on cheap junk PCs. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Why database is corrupted after re-booting
On Wed, 2005-10-26 at 19:14 +0300, Andrus wrote: To change partition types you need to re-format (resetting partitions will lose data structure - reformat required). Troy, Whole my IDE drive is 20 GB FAT32 C: drive booting XP I have a lot of data in this drive so it is not possible to re-format. Also I do'nt want to create two logical disks in single drive. Is this prevents data corruption for Postgres, is there some utility which can convert C: drive to NTFS ? Can Partition Magic help ? XP at least on install I believe has the ability to convert to NTFS. Have you tried just right clicking on your C: selecting properties and then seeing if there is a convert option? Andrus ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Generic Q about max(id) vs ORDER BY ID DESC LIMIT 1 -- SOLVED
On Mon, Oct 24, 2005 at 16:21:57 -0700, [EMAIL PROTECTED] wrote: On Mon, Oct 24, 2005 at 07:14:43PM -0400, Alex Turner wrote: I believe based on semi-recent posts that MIN and MAX are now treated as special cases in 8.1, and are synonymous with select id order by id desc limit 1 etc.. Aha! I looked it up in the release notes, you are right. I had never thought they would not be special cased. They really aren't being special cased in 8.1. There is a new way of handling them in a general way to could be used by other functions with similar properties. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Why database is corrupted after re-booting
AFAICS mysql will have exactly the same problems. So will oracle or any other DB. Oracle may have a better looking track record, but that's probably because people don't try to run it on cheap junk PCs. Can I quote this? regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Why database is corrupted after re-booting
Type the following at the Windows command prompt (start, run, cmd): convert c: /fs:ntfs /v It will complain about locked files and perform the convert at the next reboot, which you should do immediately. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Joshua D. Drake Sent: Wednesday, October 26, 2005 1:10 PM To: Andrus Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Why database is corrupted after re-booting On Wed, 2005-10-26 at 19:14 +0300, Andrus wrote: To change partition types you need to re-format (resetting partitions will lose data structure - reformat required). Troy, Whole my IDE drive is 20 GB FAT32 C: drive booting XP I have a lot of data in this drive so it is not possible to re-format. Also I do'nt want to create two logical disks in single drive. Is this prevents data corruption for Postgres, is there some utility which can convert C: drive to NTFS ? Can Partition Magic help ? XP at least on install I believe has the ability to convert to NTFS. Have you tried just right clicking on your C: selecting properties and then seeing if there is a convert option? Andrus ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] improve 'where not exists' query..
On Tue, Oct 25, 2005 at 15:46:52 -0500, Noel Whelan [EMAIL PROTECTED] wrote: I'm wondering if there's an ideal way to improve the efficiency of this query: SELECT i.id http://i.id FROM items i WHERE (NOT (EXISTS (SELECT c.id http://c.id FROM contacts c WHERE (c.id http://c.id = i.id http://i.id; It takes a while to execute, clearly. Thank you, On recent versions of postgres, NOT IN is potentially faster. Have you looked at EXPLAIN ANALYZE output to see if anything looks amiss? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] improve 'where not exists' query..
I've done an explain analyze - nothing looks wrong to me. I'm thinking it's not exactly an issue with the query itself; it's just an inefficient thing I want to do (essentially, for each id in the one table, identify whether or not one exists in the other table). Current installation is 7.3.4; but I'll look into NOT IN in case that would be an improvement. Thanks, - Noel On 10/26/05, Bruno Wolff III [EMAIL PROTECTED] wrote: On Tue, Oct 25, 2005 at 15:46:52 -0500,Noel Whelan [EMAIL PROTECTED] wrote: I'm wondering if there's an ideal way to improve the efficiency of this query: SELECT i.id http://i.id FROM items i WHERE (NOT (EXISTS (SELECT c.id http://c.id FROM contacts c WHERE (c.id http://c.id = i.id http://i.id; It takes a while to execute, clearly. Thank you,On recent versions of postgres, NOT IN is potentially faster. Have you looked at EXPLAIN ANALYZE output to see if anything looks amiss?
Re: [GENERAL] querying PostgreSQL version?
On Oct 26, 2005, at 7:52 AM, Zlatko Matić wrote: Hello. Is there any way to check the version of PostgreSQL by a query? Maybe by querying catalog tables? Thanks, select version(); Zlatko -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 214-351-4152 E-Mail: ler@lerctr.org US Mail: 3535 Gaspar Drive, Dallas, TX 75220-3611 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Why database is corrupted after re-booting
Additionally, you should also take the opportunity to defrag the filesystem after the conversion as the change in cluster size (I'm guessing from 64k to 4k) will leave your shiny new NTFS file system highly fragmented. --- Wes Williams [EMAIL PROTECTED] wrote: Type the following at the Windows command prompt (start, run, cmd): convert c: /fs:ntfs /v It will complain about locked files and perform the convert at the next reboot, which you should do immediately. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Joshua D. Drake Sent: Wednesday, October 26, 2005 1:10 PM To: Andrus Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Why database is corrupted after re-booting On Wed, 2005-10-26 at 19:14 +0300, Andrus wrote: To change partition types you need to re-format (resetting partitions will lose data structure - reformat required). Troy, Whole my IDE drive is 20 GB FAT32 C: drive booting XP I have a lot of data in this drive so it is not possible to re-format. Also I do'nt want to create two logical disks in single drive. Is this prevents data corruption for Postgres, is there some utility which can convert C: drive to NTFS ? Can Partition Magic help ? XP at least on install I believe has the ability to convert to NTFS. Have you tried just right clicking on your C: selecting properties and then seeing if there is a convert option? Andrus ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster __ Yahoo! FareChase: Search multiple travel sites in one click. http://farechase.yahoo.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Why database is corrupted after re-booting
On Wed, 2005-10-26 at 11:14, Gregory Youngblood wrote: Talking with various people that ran postgres at different times, one thing they always come back with in why mysql is so much better: postgresql corrupts too easily and you lose your data. Personally, I've not seen corruption in postgres since 5.x or 6.x versions from several years ago. And, I've seen corruption on mysql (though I could not isolate between a reiserfs or mysql problem - both with supposedly stable releases installed as part of a distro). Is corruption a problem? I don't think so - but I want to make sure I haven't had my head in the sand for a while. :) I realize this instance appears to be on Windows, which is relatively new as a native Windows program. I'm really after the answer on more mature platforms (including Linux). I have been using PostgreSQL since version 6.5.2. There are many people on this list that have been using it longer than that. In all that time, I've had exactly zero problems with data corruption. Of course, every server I've run PostgreSQL on has been burnt in for at least a week of heavy testing, and they've all had SCSI drives, and if they had RAID controllers they all had battery backed cache. Every machine was tested by running pg_bench for many days, about 100 clients wide, while doing other, more general work at the same time. A part of the testing was to switch the machine off many times while it was committing to the database, often forcing a flush before pulling the plug. I found quickly that IDE drives are not reliable with the cache turned on, and are too slow for most production purposes without the cache. So, SCSI was (and apparently still is) the only way to go. Now, I'm willing to bet that PostgreSQL is more likely to notice corruption and report it than MySQL. I wonder if MySQL can detect most simple single bit errors or not? I'd have to do some testing on it to see if it can detect such errors easily. I'd much rather have a database that simply stops and reports a data corruption error than one that doesn't notice, wouldn't you? ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] How to get the value in the lastvalue field
On Wed, Oct 26, 2005 at 18:12:32 +0530, Venki [EMAIL PROTECTED] wrote: Hi thanks for the replies. The situation is as follows. We get backups from the production server and update the local database in the local server but each time when we restore the database backup the sequence values are not getting updated properly. So what i thought was to write a function which will check the last value of the row in the table and compare it with the lastvalue of the sequnce and if it is not proper then update the lastvalue of the sequence to a proper value. I Hope that i have explained the situation properly. If you have any other suggestion it will be greatly helpful as we are new to postgres and might be doing something wrong when restoring the database. If you are dumping and restoring by cluster or database, your sequences should be having their values set upon restore. If you are just restoring indvidual tables, than you need to adjust your process to also restore the sequence values. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Where is the webaddress for the most recent postgresql version?
On Wed, Oct 26, 2005 at 10:33:21 -0400, Emi Lu [EMAIL PROTECTED] wrote: Good morning, May I know the link/web address where I can get the most recent postgresql version information please? I'd love also to get the online manual address about updating old version to the new one and the patches. Note that 8.1 is nearing release (the first release candidate will probably be out later this week) and you may want to take a look at the developer documentation. The release notes for upgrading to 8.1 are already there. http://developer.postgresql.org/docs/postgres/index.html ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Why database is corrupted after re-booting
Even with a primary UPS on the *entire PostgreSQL server* does one still need, or even still recommend, a battery-backed cache on the RAID controller card? [ref SCSI 320, of course] If so, I'd be interest in knowing briefly why. Thanks. -Original Message- ===snip=== ... every server I've run PostgreSQL on has been burnt in for at least a week of heavy testing, and they've all had SCSI drives, and if they had RAID controllers they all had battery backed cache. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Postgresql 8
I am running version 8 on Windows. Why do I get error messages stating that functions and/or tables do not exist when these tables and functions are visible, accessible and very much do exist, as called? Bob Pawley
Re: [GENERAL] Why database is corrupted after re-booting
Wes Williams [EMAIL PROTECTED] writes: Even with a primary UPS on the *entire PostgreSQL server* does one still need, or even still recommend, a battery-backed cache on the RAID controller card? [ref SCSI 320, of course] If so, I'd be interest in knowing briefly why. UPSs can fail just like any other piece of hardware. -Doug ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Postgresql 8
What exactly do those messages say? How are you using the functions? From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Bob Pawley Sent: Wednesday, October 26, 2005 11:42 AM To: Postgre General Subject: [GENERAL] Postgresql 8 I am running version 8 on Windows. Why do I get error messages stating that functions and/or tables do not exist when these tables and functions are visible, accessible and very much do exist, as called? Bob Pawley
Re: [GENERAL] Postgresql 8
Bob Pawley wrote: I am running version 8 on Windows. Why do I get error messages stating that functions and/or tables do not exist when these tables and functions are visible, accessible and very much do exist, as called? Bob Pawley It would probably help if you supplied some more details, such as how you called them, and how you tested that they are visible etc. Did you create them enclosed in double quotes? eg. dev=# create table Foo (x date); CREATE TABLE dev=# select * from Foo; ERROR: relation foo does not exist dev=# select * from Foo; x --- (0 rows) -- ___ This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. ___ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Why database is corrupted after re-booting
On Wed, 2005-10-26 at 13:38, Wes Williams wrote: Even with a primary UPS on the *entire PostgreSQL server* does one still need, or even still recommend, a battery-backed cache on the RAID controller card? [ref SCSI 320, of course] If so, I'd be interest in knowing briefly why. I'll tell you a quick little story. Got a new server, aged out the old one. new server was a dual P-IV 2800 with 2 gigs ram and a pair of 36 gig U320 drives in a RAID-1 mirror under a battery backed cache. This machine also had four 120 gig IDE drives for file storage. But the database was on the dual SCSIs under the RAID controller. I tested it with the power off test, etc... And it passed with flying colors. Put it into production. Many other servers, including our Oracle servers, were not tested in this way. This machine had dual redundant power supplies with separate power cables running into two separate rails, each running off of a different UPS. The UPSes were fed by power conditioners, and there was a switch on the other side of that to switch us over to diesel generators should the power go out. The UPSes were quite large, and even with a hundred or so computers in the hosting center, there was about 3 hours of battery time before the diesel generator HAD to be up or we'd lose power. Seems pretty solid, right? We're talking a multi million dollar hosting center, the kind with an ops center that looks like the deck of the Enterprise. Raised floors, everything. Fast forward six months. An electrician working on the wiring in the ceiling above one of the power conditioners clips off a tiny piece of wire. Said tiny piece of wire drops into the power conditioner. Said power conditioner overloads, and trips the other two power conditioners in the hosting center. This also blew out the master controller on the UPS setup, so it didn't come up. The switch for the Diesel generator would have switched over, but it was fried too. The UPSes, luckily, were the constant on variety, so they took the hit for the computers on the other side of them, about half the UPSes were destroyed. After about 3 hours, we had enough of the power jury rigged to bring the systems back up. In a company with dozens and dozens, ranging from MySQL to Oracle to PostgreSQL to Ingres to MSSQL to interbase to foxpro, exactly one of our database servers came up without any errors. You already know which one it was, or I wouldn't be writing this letter. Power supplies fail, UPSes fail, hard drives fail, and raid controllers and batter backed caches fail. You can remove every possibility of failure, but you can limit the number of things that can harm you should they fail. I do know that after that outage, I never once got shit for using postgresql ever again from anybody. The sad thing is, if any of those other machines had had battery backed raid controllers with local storage (many were running on NFS or SMB mounts) they would have been fine too. But many of the DBAs for those other databases had the same who needs to worry about sudden power off when we have UPSes and power conditioners. You can guess what optional feature suddenly seemed like a good idea for every new database server after that. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Postgresql 8
On Wed, 2005-10-26 at 13:41, Bob Pawley wrote: I am running version 8 on Windows. Why do I get error messages stating that functions and/or tables do not exist when these tables and functions are visible, accessible and very much do exist, as called? The best way to get help with these things is to post a test case that shows us exactly how you get this error. Create table, create function, insert rows of data, run select, boom ERROR. If you have one of those, we can help you quite a bit more than trying to mind read what exactly the problem might be you're having. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Why database is corrupted after re-booting
Wes Williams writes: Even with a primary UPS on the *entire PostgreSQL server* does one still need, or even still recommend, a battery-backed cache on the RAID controller card? [ref SCSI 320, of course] If so, I'd be interest in knowing briefly why. it can be a lot faster. if the raid controller knows it has a battery backup, then it'll be free to do whatever it sees fit in terms of write order. some controllers (the ibm serveraid 4 units that i have a couple of, for example) won't do this unless they know the battery is there, they have no option for overriding that setting. richard ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Why database is corrupted after re-booting
snacktime wrote: I remember a few months back when someone hit the emergency power switch to the whole floor where we host at Internap. Subsequently the backup power system had a cascading failure. Livejournal, who also hosts there, was up all night and into the next day restoring their mysql databases after a bunch of them were corrupted. I believe they had write cache turned on. Of course our postgresql servers on scsi drives came right back up. If it wasn't for a couple of servers that won't reboot automatically if the power goes out I wouldn't have even had to go down to the data center. Chris I remember reading a detailed account on Livejournal about the hoops they had to jump through to get up and running again after that incident. Bit of a nightmare for them. -- ___ This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. ___ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Postgresql 8
On Oct 26, 2005, at 2:41 PM, Bob Pawley wrote: I am running version 8 on Windows. Why do I get error messages stating that functions and/or tables do not exist when these tables and functions are visible, accessible and very much do exist, as called? Bob Pawley Most likely cause is the schema_path is set wrong or the tables/ functions have been created using quoted names (e.g. myTable) and you are calling them without quotes. PostgreSQL folds identifier names to lower case unless you double quote them. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Postgresql 8
-Original Message- From: [EMAIL PROTECTED] [mailto:pgsql-general- [EMAIL PROTECTED] On Behalf Of John DeSoi Sent: Wednesday, October 26, 2005 12:03 PM To: Bob Pawley Cc: Postgre General Subject: Re: [GENERAL] Postgresql 8 On Oct 26, 2005, at 2:41 PM, Bob Pawley wrote: I am running version 8 on Windows. Why do I get error messages stating that functions and/or tables do not exist when these tables and functions are visible, accessible and very much do exist, as called? Bob Pawley Most likely cause is the schema_path is set wrong or the tables/ functions have been created using quoted names (e.g. myTable) and you are calling them without quotes. PostgreSQL folds identifier names to lower case unless you double quote them. He could also be calling the functions with the wrong signature (e.g. create numeric functions and then call them with doubles without using a cast). Without more information, trying to help is a shot in the dark. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Map of Postgresql Users (OT)
I took a look at your map, this is great wonderful that you were
already thinking along these lines.
Looks like there are two issues here: (1) Getting the data points for
users/developers maps, and (2) using the most appropriate mapping
technology.
I think we can use the Zeemaps service (http://www.zeemaps.com) for
number (1) above. The way this works is that you create a map and set a
few passwords: (a) moderator, (b) member, and (c) viewer. Using the
moderator password, you can set certain map properties, e.g.,
attributes to maintain with each entry, color legends, etc. With a
member password you can modify entries and their attributes, and with a
viewer password you can just see details without the ability to modify
anything. A casual user can just see the map with the markers and their
names, without access to any details for the entries. Hence
distributing the member password to the group and having each person
add his or her details would be a good way to go. The number of points
for Postgresql users can, of course, go pretty high. So far, we have
seen reasonable performance with up to 500-600 markers. I don't know if
there is an easy way of partitioning the user base into segments that
can fit within this range. Another idea would be to just split the map
after say 500 points.
For the number (2) the choice seems to be between Google Maps vs.
Mapserver and its associated Open Source tools. Having looked at
Brent's follow up emails, it seems that Mapserver and associated
toolkits can give much more flexibility. Plus they have the advantage
that we are not limited by Google's terms, the most annoying of which
is that you cannot save, copy, or distribute the map images created.
So, it seems best to move towards the Open Source alternatives,
specially since they have better images as Brent points out. I've been
looking at some of the Mapserver toolkits, and there seems to be an
overwhelming amount of choice available for what to pick and not. So, I
don't feel qualified to pick the right technologies to use for the base
implementation. If someone can pick the mantle on that, I'd be happy to
work with that person to see how we can exchange information from the
map points stored from (1). If for the time being you feel comfortable
continuing with Google Maps, then there's multiple ways we can share
information developed by (1) through some kind of a web service API.
Claire
On Oct 25, 2005, at 2:33 PM, Robert Treat wrote:
Actually I have already made a basic google map for the website[1]
meant
to replace the old developers map. Check out the blog post at
http://people.planetpostgresql.org/xzilla/index.php?/archives/76-
Maptastic.html
I'd be interested in talking more about getting more content into the
map and getting it in officially; One concern I had was finding a way
to
include information that won't bog down end users with too much data.
(I've noticed large numbers of points tend to do that)
Robert Treat
On Tue, 2005-10-25 at 12:11, Claire McLister wrote:
Thanks.
I looked at the Mapserver maps, and at first glance it seems Google
Maps API provides better map images and more interactive features
(zooming, panning, JS popups, satellite/map views, etc.)
So, what would be the advantage of Mapserver apart from it being based
on Open Source?
We use Postgresql server to store the data. Currently, we do not use
PostGIS as we do not do much geographical queries. Over time we could
move in that direction to offer better geographical query support.
Posting this (or a more detailed map) on the Postgresql web site
sounds
like a great idea. We could even allow people to add markers for their
locations, and even enter more information about themselves to help
postgresql users connect with each other. One potential opportunity is
for users to quickly find consultants in their own area.
How would we initiate this activity?
Best wishes
Claire
On Oct 24, 2005, at 2:28 PM, Brent Wood wrote:
How about using Postgres/PostGIS UMN mapserver for a fully Open
Source
software Postgres based map?
For a map like the one at
http://www.qgis.org/index.php?option=com_wrapperItemid=53
perhaps on the Postgres web site?
---(end of
broadcast)---
TIP 6: explain analyze is your friend
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of
broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Why database is corrupted after re-booting
Just to add another story... I've been running PostgreSQL on Linux since the 6.x days and back then I was almost always on IDE drives with an EXT2 filesystem. To date, the worse class of experiences I've had was going through the fs recovery steps for EXT2. In those cases I never lost data in the database even when I might have lost files. Once XFS became an in kernel option for Linux, I moved almost all my servers to that filesystem whether they are IDE or SCSI. In a recent experience where I was forced to hard reset a server with XFS and IDE drives, the box came right back up with no data loss. There is only one case of a major problem I've have in the last 8 years or so and I posted to this list and with Tom's help I was able to get the box online. That wasn't a filesystem problem though. Its off topic but (for those interested) that thread, Database Recovery Procedures, was from September 16, 2003. It had to deal with padding out one of the pg_clog files in a 7.3.x system. Quoting Welty, Richard [EMAIL PROTECTED]: Wes Williams writes: Even with a primary UPS on the *entire PostgreSQL server* does one still need, or even still recommend, a battery-backed cache on the RAID controller card? [ref SCSI 320, of course] If so, I'd be interest in knowing briefly why. it can be a lot faster. if the raid controller knows it has a battery backup, then it'll be free to do whatever it sees fit in terms of write order. some controllers (the ibm serveraid 4 units that i have a couple of, for example) won't do this unless they know the battery is there, they have no option for overriding that setting. richard ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Keith C. Perry, MS E.E. Director of Networks Applications VCSN, Inc. http://vcsn.com This email account is being host by: VCSN, Inc : http://vcsn.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Error Message
I am attempting to create a new trigger through the "new Trigger" interface on version 8 installed on Windows. The following is the sql that the interface generates CREATE TRIGGER trig1 AFTER INSERT ON process FOR EACH ROW EXECUTE PROCEDURE base(int4);COMMENT ON TRIGGER trig1 ON process IS 'insert into specification (fluid_id)'; The error message reads - ERROR: function base() does not exist The function name is listed under Functions as - base(int4) Bob
Re: [GENERAL] Error Message
Bob Pawley [EMAIL PROTECTED] writes: The function name is listed under Functions as - base(int4) Trigger functions cannot take any explicit parameters. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Need help with INOUT mis-understanding
Tom Lane wrote: Tony Caduto [EMAIL PROTECTED] writes: I have noticed this as well, if I declare OUT params I can modify them to my hearts content before they go out, however if you declare it as a INOUT you can't modify it because it is declared as a constant. Uh, I don't think so. /* input argument vars are forced to be CONSTANT */ if (argmode == PROARGMODE_IN) ((PLpgSQL_var *) argvariable)-isconst = true; Can you provide a test case that misbehaves that way? regards, tom lane Tom, Maybe we are mis-communicating here. So what you are saying is INOUT params are NOT constants and you can modify them in the function body? I could have sworn that did not work for me in beta1, I will try it again in beta 4 and see if things have changed. Thanks, Tony Caduto ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Need help with INOUT mis-understanding
Tony Caduto [EMAIL PROTECTED] writes: So what you are saying is INOUT params are NOT constants and you can modify them in the function body? If you couldn't modify them, there would be no way to return a new value (ie, anything but the passed-in value), so it'd be pretty broken IMHO ... regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Win32 libpq and ecpg thread safety
On Wed, 26 Oct 2005, Bruce Momjian wrote: If you are asking about Win32-specific threading, there is no documentation about it because it works just like Unix threading. At least, that is what I am told. So both libpq and ecpg are thread-safe in Win32? I look into the code, seems at least ecpg is not, and there is some possible improvements of libpq (esp. PTHREAD_MUTEX_INITIALIZER). Regards, Qingqing ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Error Message
I'm not sure what you mean. base(int4) is the name of the function that I want to call. It follows the format of an example in a Postgresql book I use (or perhaps misuse). Are you saying that I need to redo the function??? Bob - Original Message - From: Tom Lane [EMAIL PROTECTED] To: Bob Pawley [EMAIL PROTECTED] Cc: Postgre General pgsql-general@postgresql.org Sent: Wednesday, October 26, 2005 2:23 PM Subject: Re: [GENERAL] Error Message Bob Pawley [EMAIL PROTECTED] writes: The function name is listed under Functions as - base(int4) Trigger functions cannot take any explicit parameters. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Error Message
Bob, You cannot pass argments to trigger functions. You can to other types of functions, but not functions used as triggers. Arguments are passed regarding the old and new records and other built in variables regarding what kind of operation is going on, but all of that is unseen. They must be created as in: CREATE TRIGGER trig1 AFTER INSERT ON process FOR EACH ROW EXECUTE PROCEDURE base(); ^^ Note: no argument. On Wednesday 26 October 2005 07:24 pm, Bob Pawley saith: I'm not sure what you mean. base(int4) is the name of the function that I want to call. It follows the format of an example in a Postgresql book I use (or perhaps misuse). Are you saying that I need to redo the function??? Bob - Original Message - From: Tom Lane [EMAIL PROTECTED] To: Bob Pawley [EMAIL PROTECTED] Cc: Postgre General pgsql-general@postgresql.org Sent: Wednesday, October 26, 2005 2:23 PM Subject: Re: [GENERAL] Error Message Bob Pawley [EMAIL PROTECTED] writes: The function name is listed under Functions as - base(int4) Trigger functions cannot take any explicit parameters. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Error Message
Bob Pawley [EMAIL PROTECTED] writes: I'm not sure what you mean. base(int4) is the name of the function that I want to call. It follows the format of an example in a Postgresql book I use (or perhaps misuse). Are you saying that I need to redo the function??? You need to make it a function that takes zero arguments. Trigger functions take their arguments in a weird way, not via the usual mechanism. See the docs. -Doug ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Variable return type...
Hi, I was working in a set of SPs inside PL/pgSQL and I was wonder if I could return a set of variable types from a function, for example, in some time the same function could return a set of tuples with an integer and a string, in other times It may return a set of tuples with an integer, a string and another string a so on I know I can use anyelement but then I would need to specify an anyelement input type (but in my case the input element would the the same all the time); Any idea in how I could do this? Thanks a lot!
Re: [GENERAL] Error Message
On Wed, Oct 26, 2005 at 07:45:19PM -0400, Terry Lee Tucker wrote: You cannot pass argments to trigger functions. You can to other types of functions, but not functions used as triggers. Arguments are passed regarding the old and new records and other built in variables regarding what kind of operation is going on, but all of that is unseen. They must be created as in: CREATE TRIGGER trig1 AFTER INSERT ON process FOR EACH ROW EXECUTE PROCEDURE base(); ^^ Note: no argument. You *can* pass arguments to trigger functions but it's done a little differently than with non-trigger functions. The function must be defined to take no arguments; it reads the arguments from a context structure instead of in the normal way. PL/pgSQL trigger functions, for example, read their arguments from the TG_ARGV array. http://www.postgresql.org/docs/8.0/interactive/plpgsql-trigger.html http://www.postgresql.org/docs/8.0/interactive/trigger-interface.html Example: CREATE TABLE foo (id integer, x integer); CREATE FUNCTION func() RETURNS trigger AS $$ BEGIN NEW.x := TG_ARGV[0]; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER footrig BEFORE INSERT OR UPDATE ON foo FOR EACH ROW EXECUTE PROCEDURE func(12345); INSERT INTO foo (id) VALUES (1); SELECT * FROM foo; id | x +--- 1 | 12345 (1 row) However, it's not clear if this is what Bob is trying to do. His original attempt was: CREATE TRIGGER trig1 AFTER INSERT ON process FOR EACH ROW EXECUTE PROCEDURE base(int4); He's given what looks like a function signature instead of passing an argument. Even if this worked, he hasn't specified what argument should be passed. Bob, can you explain what you're trying to do? -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Error Message
On Wed, Oct 26, 2005 at 07:00:06PM -0600, Michael Fuhr wrote:
You *can* pass arguments to trigger functions but it's done a little
differently than with non-trigger functions. The function must be
defined to take no arguments; it reads the arguments from a context
structure instead of in the normal way. PL/pgSQL trigger functions,
for example, read their arguments from the TG_ARGV array.
http://www.postgresql.org/docs/8.0/interactive/plpgsql-trigger.html
http://www.postgresql.org/docs/8.0/interactive/trigger-interface.html
Also
http://www.postgresql.org/docs/8.0/interactive/sql-createtrigger.html
where the documentation says
CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] }
ON table [ FOR [ EACH ] { ROW | STATEMENT } ]
EXECUTE PROCEDURE funcname ( arguments )
...
arguments
An optional comma-separated list of arguments to be provided to the
function when the trigger is executed. The arguments are literal
string constants. Simple names and numeric constants may be written
here, too, but they will all be converted to strings. Please check
the description of the implementation language of the trigger function
about how the trigger arguments are accessible within the function; it
may be different from normal function arguments.
--
Michael Fuhr
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Variable return type...
On Wed, Oct 26, 2005 at 06:14:23PM -0600, Cristian Prieto wrote:
Hi, I was working in a set of SPs inside PL/pgSQL and I was wonder if I
could return a set of variable types from a function, for example, in some
time the same function could return a set of tuples with an integer and a
string, in other times It may return a set of tuples with an integer, a
string and another string. a so on.
You could declare the function to return a record type. You'll
have to provide a column definition list when you call the function,
which means you must know in advance what kind of record will be
returned.
CREATE FUNCTION foo() RETURNS SETOF record AS $$
DECLARE
retval record;
BEGIN
SELECT INTO retval 1::integer, 'abc'::text;
RETURN NEXT retval;
SELECT INTO retval 2::integer, 'def'::text;
RETURN NEXT retval;
RETURN;
END;
$$ LANGUAGE plpgsql;
SELECT * FROM foo();
ERROR: a column definition list is required for functions returning record
SELECT * FROM foo() AS foo(x integer, t text);
x | t
---+-
1 | abc
2 | def
(2 rows)
Another possibility would be to return a cursor. You wouldn't have
to know the record structure in advance, but you also wouldn't be
able to use the function as part of a larger query (somebody please
correct me if I'm mistaken).
CREATE FUNCTION foo(refcursor) RETURNS refcursor AS $$
BEGIN
OPEN $1 FOR SELECT 1::integer, 'abc'::text
UNION
SELECT 2::integer, 'def'::text;
RETURN $1;
END;
$$ LANGUAGE plpgsql;
BEGIN;
SELECT foo('curs');
FETCH ALL FROM curs;
int4 | text
--+--
1 | abc
2 | def
(2 rows)
COMMIT;
--
Michael Fuhr
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
[GENERAL] Seq Scan but I think it should be Index Scan
So the details of it:
I'm using PostgreSQL 8.0.3 on a Gentoo kernel 2.6.12 on a P4/2.8G+HT proc, with kernel RAID0 on some SATA drives and 1G
RAM. Don't know the bus speed. I'm thinking that my queries are not using indexs correctly and therefore taking longer
to complete than they should. I've put the details below, but changed some names. If anyone could shed some light?
pg_config --configure
'--prefix=/usr' '--mandir=/usr/share/man' '--host=i686-pc-linux-gnu' '--with-docdir=/usr/share/doc/postgresql-8.0.3'
'--libdir=/usr/lib' '--enable-depend' '--with-gnu-ld' '--with-perl' '--with-openssl' '--enable-nls' '--with-pam'
'CFLAGS=-O2 -march=pentium4' 'host_alias=i686-pc-linux-gnu'
data=# explain analyze select count(id) from x_base where x_type 100 and
x_date='2005-10-26' and x_time'06:00:00';
QUERY PLAN
---
Aggregate (cost=539453.36..539453.36 rows=1 width=4) (actual
time=66200.763..66200.764 rows=1 loops=1)
- Seq Scan on x_base (cost=0.00..539136.18 rows=126871 width=4) (actual
time=31618.624..66174.710 rows=37451 loops=1)
Filter: ((x_type 100) AND (x_date = '2005-10-26'::date) AND (x_time
'06:00:00'::time without time zone))
Total runtime: 66200.811 ms
data=# \d x_base
Table public.x_base
Column| Type |Modifiers
-++--
id | integer| not null default
nextval('public.x_base_id_seq'::text)
x_code | character(8) |
x_date | date | not null
x_time | time without time zone | not null
a | character(1) |
b | integer|
c | character(5) |
d | character(16) |
e | character(1) |
f | character(1) |
g | character(10) |
h | character(1) |
i | character(1) |
j | character varying(32) |
k | integer|
l | integer|
m | integer|
n | character varying(32) |
o | integer|
p | character varying(14) |
q | integer|
Indexes:
x_base_pkey PRIMARY KEY, btree (id)
ix_d_cd btree (x_date)
ix_t_cb btree (x_type)
Foreign-key constraints:
fk_k_id FOREIGN KEY (k) REFERENCES x_file(id)
Now, see that x_type index? Why didn't this thing Index Scan ix_t_cb on that column? Me thinks if it had my query
would be much faster. Or perhaps if I only where x_type? I tried that but it still took a minute. I took out count()
and it still took a minute. Always using Seq Scan, am I doing something dumb here? There are more than six million
records in that table, maybe thats just how long it takes? Perhaps I should change architecture or schema to improve
performance? Tweak the log? Thanks.
/djb
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] Variable return type...
Thanks a lot, your answer enlighten me a lot in the path to take to resolve
the problem into the database...
Thanks man...
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Michael Fuhr
Sent: Miércoles, 26 de Octubre de 2005 07:38 p.m.
To: Cristian Prieto
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Variable return type...
On Wed, Oct 26, 2005 at 06:14:23PM -0600, Cristian Prieto wrote:
Hi, I was working in a set of SPs inside PL/pgSQL and I was wonder if I
could return a set of variable types from a function, for example, in some
time the same function could return a set of tuples with an integer and a
string, in other times It may return a set of tuples with an integer, a
string and another string. a so on.
You could declare the function to return a record type. You'll
have to provide a column definition list when you call the function,
which means you must know in advance what kind of record will be
returned.
CREATE FUNCTION foo() RETURNS SETOF record AS $$
DECLARE
retval record;
BEGIN
SELECT INTO retval 1::integer, 'abc'::text;
RETURN NEXT retval;
SELECT INTO retval 2::integer, 'def'::text;
RETURN NEXT retval;
RETURN;
END;
$$ LANGUAGE plpgsql;
SELECT * FROM foo();
ERROR: a column definition list is required for functions returning
record
SELECT * FROM foo() AS foo(x integer, t text);
x | t
---+-
1 | abc
2 | def
(2 rows)
Another possibility would be to return a cursor. You wouldn't have
to know the record structure in advance, but you also wouldn't be
able to use the function as part of a larger query (somebody please
correct me if I'm mistaken).
CREATE FUNCTION foo(refcursor) RETURNS refcursor AS $$
BEGIN
OPEN $1 FOR SELECT 1::integer, 'abc'::text
UNION
SELECT 2::integer, 'def'::text;
RETURN $1;
END;
$$ LANGUAGE plpgsql;
BEGIN;
SELECT foo('curs');
FETCH ALL FROM curs;
int4 | text
--+--
1 | abc
2 | def
(2 rows)
COMMIT;
--
Michael Fuhr
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] escape string type for upcoming 8.1
Ken Johanson wrote: Bruce Momjian wrote: E'' is more a marker than a type. I realize making E a type might work, but it seems unusual. What we could do is backpatch E'' to 8.0.X as a no-op like it will be in 8.1. Bruce, Is it possible in the 8.1 betas to 'switch on' on the standard SQL escape behavior? This is from the use-case perspective of someone who does not have backwards compatibility concerns, rather, I'd like to preemptively forward-port / certify an app from another databases, onto PostgreSQL -- so all I need to do is switch that config on, if possible. From the changelog: While this release does not change the default? handling of backslashes in strings, it does add new configuration parameters to help users migrate applications for future releases: o standard_conforming_strings .. o escape_string_warning .. The standard_conforming_strings value is read-only. The last quoted sentence seems to answer my question (as no), but hopeful optimism is my motto :-) If it is indeed readonly, can it be made 'writable' before the 8.3 release where is would be made the default behavior? For that matter, if the current backslash behavior stayed as the default for pre-8.3 releases, and the patches are backported, I don't see any harm to the old-style apps/users; yet the correct behavior option is a useful opt-in one (one that I would like to try, now, even on 8.1). The configuration parameter will be writable in 8.2. We would have liked to have it writable in 8.1 but found it too complex to do in a limited amount of time. -- 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 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Error Message
I have a base table called process. Each row of this table is anchored by a serial column labeled fluid_id. After data has been entered into a row in process, I want to trigger a row in another table labeled specification also with a column labeled fluid_id. I would like this number from process entered into specification as an integer. I would like this to happen after each row in process has satisfied the not null requirements. I may not be employing the language you are use to using however, I hope this explanation is somewhat clear. Thanks for your help. Bob - Original Message - From: Michael Fuhr [EMAIL PROTECTED] To: Terry Lee Tucker [EMAIL PROTECTED] Cc: Postgre General pgsql-general@postgresql.org Sent: Wednesday, October 26, 2005 6:00 PM Subject: Re: [GENERAL] Error Message On Wed, Oct 26, 2005 at 07:45:19PM -0400, Terry Lee Tucker wrote: You cannot pass argments to trigger functions. You can to other types of functions, but not functions used as triggers. Arguments are passed regarding the old and new records and other built in variables regarding what kind of operation is going on, but all of that is unseen. They must be created as in: CREATE TRIGGER trig1 AFTER INSERT ON process FOR EACH ROW EXECUTE PROCEDURE base(); ^^ Note: no argument. You *can* pass arguments to trigger functions but it's done a little differently than with non-trigger functions. The function must be defined to take no arguments; it reads the arguments from a context structure instead of in the normal way. PL/pgSQL trigger functions, for example, read their arguments from the TG_ARGV array. http://www.postgresql.org/docs/8.0/interactive/plpgsql-trigger.html http://www.postgresql.org/docs/8.0/interactive/trigger-interface.html Example: CREATE TABLE foo (id integer, x integer); CREATE FUNCTION func() RETURNS trigger AS $$ BEGIN NEW.x := TG_ARGV[0]; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER footrig BEFORE INSERT OR UPDATE ON foo FOR EACH ROW EXECUTE PROCEDURE func(12345); INSERT INTO foo (id) VALUES (1); SELECT * FROM foo; id | x +--- 1 | 12345 (1 row) However, it's not clear if this is what Bob is trying to do. His original attempt was: CREATE TRIGGER trig1 AFTER INSERT ON process FOR EACH ROW EXECUTE PROCEDURE base(int4); He's given what looks like a function signature instead of passing an argument. Even if this worked, he hasn't specified what argument should be passed. Bob, can you explain what you're trying to do? -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Win32 libpq and ecpg thread safety
Qingqing Zhou wrote: On Wed, 26 Oct 2005, Bruce Momjian wrote: If you are asking about Win32-specific threading, there is no documentation about it because it works just like Unix threading. At least, that is what I am told. So both libpq and ecpg are thread-safe in Win32? I look into the code, seems at least ecpg is not, and there is some possible improvements of libpq (esp. PTHREAD_MUTEX_INITIALIZER). As far as I know, both are thread safe, except you have to read the documention limitations for both of them. If you have improvements, we would love to see them. -- 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: don't forget to increase your free space map settings
Re: [GENERAL] Seq Scan but I think it should be Index Scan
On Wed, Oct 26, 2005 at 06:55:54PM -0700, Edoceo Lists wrote: I'm thinking that my queries are not using indexs correctly and therefore taking longer to complete than they should. Index scans aren't necessarily faster than sequential scans: if the query reads a significant amount of the table then a sequential scan can be faster. data=# explain analyze select count(id) from x_base where x_type 100 and x_date='2005-10-26' and x_time'06:00:00'; QUERY PLAN --- Aggregate (cost=539453.36..539453.36 rows=1 width=4) (actual time=66200.763..66200.764 rows=1 loops=1) - Seq Scan on x_base (cost=0.00..539136.18 rows=126871 width=4) (actual time=31618.624..66174.710 rows=37451 loops=1) Filter: ((x_type 100) AND (x_date = '2005-10-26'::date) AND (x_time '06:00:00'::time without time zone)) Total runtime: 66200.811 ms Notice that the estimated row count (126871) is much higher than the actual row count (37451). It's possible that the planner would prefer an index scan if the row count estimate was more accurate. Has this table been vacuumed and analyzed recently? If so then you might get more accurate estimates by increasing columns' statistics targets with ALTER TABLE ... SET STATISTICS. If you do that then be sure to analyze the table afterwards to update the planner's statistics. Now, see that x_type index? Why didn't this thing Index Scan ix_t_cb on that column? Me thinks if it had my query would be much faster. No need to guess: disable sequential scans and see if an index scan is faster. SET enable_seqscan TO off; EXPLAIN ANALYZE SELECT ... Run the query several times with and without sequential scans to make sure that timing differences aren't due more to disk caching than to the query plan. Have you adjusted any settings in postgresql.conf? With 1G RAM the defaults are probably too conservative. In particular, you could probably use a much higher effective_cache_size than the default, and that's one of the settings that the planner uses when considering whether to do an index scan. Some people also see performance improvements by lowering random_page_cost, although doing so isn't really correct. BTW, pgsql-performance might be a better list to post performance questions. -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Error Message
On Wed, Oct 26, 2005 at 07:47:51PM -0700, Bob Pawley wrote: I have a base table called process. Each row of this table is anchored by a serial column labeled fluid_id. What do you mean by anchored by? Is fluid_id the primary key for process? Or is fluid_id a foreign key reference to some other table? Or do you mean something else? After data has been entered into a row in process, I want to trigger a row in another table labeled specification also with a column labeled fluid_id. I would like this number from process entered into specification as an integer. By trigger a row do you mean that you want the trigger on process to insert a new row into specification? Is the following example close to what you're looking for? CREATE TABLE process (fluid_id integer PRIMARY KEY); CREATE TABLE specification (fluid_id integer NOT NULL); CREATE FUNCTION base() RETURNS trigger AS $$ BEGIN INSERT INTO specification (fluid_id) VALUES (NEW.fluid_id); RETURN NULL; -- ignored in AFTER triggers END; $$ LANGUAGE plpgsql; CREATE TRIGGER trig1 AFTER INSERT ON process FOR EACH ROW EXECUTE PROCEDURE base(); INSERT INTO process (fluid_id) VALUES (123); INSERT INTO process (fluid_id) VALUES (456); SELECT * FROM process; fluid_id -- 123 456 (2 rows) SELECT * FROM specification; fluid_id -- 123 456 (2 rows) -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] count( only if true)
On Wed, Oct 12, 2005 at 22:24:48 +0200, peter pilsl [EMAIL PROTECTED] wrote: knowledge=# select x,count(case when id5 then 't' else null end) from test2 group by x; x | count ---+--- e | 1 - thats the result I want !!! b | 1 c | 1 d | 0 a | 1 (5 rows) For simple cases like this you probably want to do the following: SELECT x, count(*) FROM test2 WHERE id 5 GROUP BY x ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Why database is corrupted after re-booting
Andrus wrote: Will NTFS file system prevent all corruptions ? If yes, how to convert FAT32 to NTFS without losing data in drive ? iirc (i'm not on windows currently, google for the exact syntax), at the dos prompt, type: convert /fs:ntfs C: and it will schedule a conversion after the next reboot. you *should* backup all important data to another drive/computer though (imagine what will happen if your computer dies again in the middle of conversion). -- dave ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
