Re: [GENERAL] Delphi 2005, Postgresql, ZEOS & optimistic locking
Hi, Yes, I'm sure this kind of feature is application-based. There is nothing in the driver itself. If I'm not wrong, MS Access uses a timestamp column to check if the record was updated meanwhile, or the column values if not timestamp is available. This might be true only with a MS SQL Database, though. I'll try doing something similar in a Delphi event-handler. Thanks, bye Philippe -Message d'origine- De : Tony Caduto [mailto:[EMAIL PROTECTED] Envoyé : jeudi, 12. mai 2005 21:07 À : Philippe Lang Cc : pgsql-general@postgresql.org Objet : Re: [GENERAL] Delphi 2005, Postgresql, ZEOS & optimistic locking Why not just add a onenter event handler to the forms field in question and just check the data before they edit it. If it's different update the field with the most current data. Access probably just does something similar under the hood for you. I don't think that's a feature of the ODBC driver or is it? If it is you could always use the ODBC driver from Delphi as well. > > With Dephi/BDE/ODBC, this is different: as soon as you try updating a field > that has been modified by someone else meanwhile, the field is automatically > updated for you before you start making your own changes, and of course > before you try to commit them. That's fine too. I would have preferred an > error personnally. > > Is there a way to do the same with ZEOS? Or maybe is there another mecanism > that could be used to do optimistic locking? Some kind of "long > transactions", in the database server? > > Philippe Lang > ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Postgres 8.0.3 Fedora RPMS ?
Hello, I guess I was looking for the x86-64 version, I never bothered to check whether there were 32 bit ones. Anybody going to create the 64bit RPMS ? Cheers, Aly. Devrim GUNDUZ wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, On Thu, 12 May 2005, Aly Dharshi wrote: I hope that you are well. Don't mean to rush anybody but just curious as to when the RPMS and SRPMS for 8.0.3 will show up ? Thanks ! They are already on main FTP site (and its mirrors, if synced). Regards, - -- Devrim GUNDUZ devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.tdmsoft.com.tr http://www.gunduz.org -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQFChB3utl86P3SPfQ4RAqDzAJ4xmwTGimsJCfdGEArMASjv5KW23gCgnN7q Y13LtuaQ1Sqk9ZTJL62ppJY= =H/xT -END PGP SIGNATURE- -- Aly Dharshi [EMAIL PROTECTED] "A good speech is like a good dress that's short enough to be interesting and long enough to cover the subject" ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Postgres 8.0.3 Fedora RPMS ?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, On Thu, 12 May 2005, Aly Dharshi wrote: I hope that you are well. Don't mean to rush anybody but just curious as to when the RPMS and SRPMS for 8.0.3 will show up ? Thanks ! They are already on main FTP site (and its mirrors, if synced). Regards, - -- Devrim GUNDUZ devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.tdmsoft.com.tr http://www.gunduz.org -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQFChB3utl86P3SPfQ4RAqDzAJ4xmwTGimsJCfdGEArMASjv5KW23gCgnN7q Y13LtuaQ1Sqk9ZTJL62ppJY= =H/xT -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Please Recommend a Front End App
On May 12, 2005, at 7:44 PM, John DeSoi wrote: On May 12, 2005, at 7:06 PM, Kurt Gibson wrote: One important solution I created in Filemaker Pro requires very detailed and exact replication of official court forms with database data entered on the forms. Imagine a form with small type, boxes and spacing that must be exactly as on the official form and data from the database on the form as printed to pdf. One nice thing about Filemaker is that its reporting ability is very flexible. I also have an extensive solution with approx 50 tables/databases to run my small business. I built a calendar, contacts, clients, timelog, and billing solution. I have been bumping my head against Filemaker Pro's 50 database limit for years - server would allow you 125 databases for about $1000. For those of you with no experience with Filemaker, a database is a table. Each file only has one table and all layouts/reports/forms and scripts are in the same file. This setup has pros and cons that are irrelevant now. I'm not familiar with FileMaker, but my understanding is that it can communicate with other databases via ODBC. Would it not be possible to keep your investment (at least for a while) in the front end forms and convert to PostgreSQL as the data store? I have some colleagues who have tried to do this, but it seems that this (at least in their hands) is a bit of pie-in-the-sky. I'd love to hear otherwise, but I'm not sure this is a viable solution, particularly using Filemaker 5, which is not truly relational. Sean ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Please Recommend a Front End App
Though I haven't embarked on any expeditions into the world of printing yet, I've found Python to be a terrific language for db access. My solution uses Apple hardware, OSX, postgres, the psycopg programming interface to Python, Python, and my slowboat hacking with wxPython to build a GUI front end. So far so good! (Thanks in large part to useful info from this mail list!) Before you compost your older iMacs, you might want to check out YellowDog Linux. I've run various releases of their OS over the years and it's a great way to keep your hardware viable. Best luck - Scott On May 12, 2005, at 4:06 PM, Kurt Gibson wrote: Newbie - please help me choose a direction. I want to know what would be the best front-end app/language to use for postgresql for exact form replication and ease of use. PHP, Python, Java, Rekall (the Kompany), other? All suggestions welcome and appreciated. Background--- I am coming from Filemaker Pro 5.0 and Mac OS 10.2.8. I am the newest of newbies to postgresql. I just downloaded the database today. I have not started the database and do not even know if I can run it on my computers. I have a few old WinTel machines at home but run mainly Macs at the office. One important solution I created in Filemaker Pro requires very detailed and exact replication of official court forms with database data entered on the forms. Imagine a form with small type, boxes and spacing that must be exactly as on the official form and data from the database on the form as printed to pdf. One nice thing about Filemaker is that its reporting ability is very flexible. I also have an extensive solution with approx 50 tables/databases to run my small business. I built a calendar, contacts, clients, timelog, and billing solution. I have been bumping my head against Filemaker Pro's 50 database limit for years - server would allow you 125 databases for about $1000. For those of you with no experience with Filemaker, a database is a table. Each file only has one table and all layouts/reports/forms and scripts are in the same file. This setup has pros and cons that are irrelevant now. I am trying to migrate to linux and postgresql. I love the MacOS but they just upgraded to 10.4 (tiger) and made 3 of my iMacs obsolete because they do not have on-board firewire so are not supported by 10.4. Funny how Linux can be compiled to run on a 386 intel chip from a floppy drive but MacOS cannot now run without firewire on a 4 year old computer. Filemaker.com created Filemaker Pro 7 which adds great features and removes the 50 table limit - with a $1000 price tag for 3 units and a 5-simultaneous-user limit. The upgrade to 7 may require extensive changes to my solutions. Now would be a natural time to change to a new system since I have to relearn/change everything anyway. Additionally, I can only assume that I will be in for more of the same treatment as I and people like me get our data more entrenched in their proprietary business models. Thanks for your help. ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Postgres 8.0.3 Fedora RPMS ?
Hi Folks, I hope that you are well. Don't mean to rush anybody but just curious as to when the RPMS and SRPMS for 8.0.3 will show up ? Thanks ! Cheers, Aly. -- Aly Dharshi [EMAIL PROTECTED] "A good speech is like a good dress that's short enough to be interesting and long enough to cover the subject" ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] pg_dump: ERROR: Memory exhausted in AllocSetAlloc(875574064)
On Thu, May 12, 2005 at 02:56:35PM -0400, Tom Lane wrote: > "Reid Thompson" <[EMAIL PROTECTED]> writes: > > Is there any solution to this other than adding memory, or am I > > mis-understanding the error? > > > pg_dump: ERROR: Memory exhausted in AllocSetAlloc(875574064) > > pg_dump: lost synchronization with server, resetting connection > > My bet is that this is actually a corrupt-data problem ... unless you > actually have any 875MB fields in your table. The implication is that > the length word of a variable-width field contains garbage. More than > likely, the whole tuple is garbaged, but this happens to be the first > visible symptom. Just for curiosity sake: 875574064 base 10 = 34303330 base 16 Looks like the ASCII string "4030" Definitly corrupted tuple... -- Martijn van Oosterhout 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. pgpt1FhLkUUfS.pgp Description: PGP signature
Re: [GENERAL] Please Recommend a Front End App
Kurt, you might want to take a look at OpenRPT (http://pgfoundry.org/projects/openrpt/) - it's a Crystal/Access type report builder for PostgreSQL that works great on any of the feline Mac products. We don't do print to PDF yet, however, someone has raised his hand to work on that with some existing libraries. Cheers, Ned Kurt Gibson wrote: Newbie - please help me choose a direction. I want to know what would be the best front-end app/language to use for postgresql for exact form replication and ease of use. PHP, Python, Java, Rekall (the Kompany), other? All suggestions welcome and appreciated. Background--- I am coming from Filemaker Pro 5.0 and Mac OS 10.2.8. I am the newest of newbies to postgresql. I just downloaded the database today. I have not started the database and do not even know if I can run it on my computers. I have a few old WinTel machines at home but run mainly Macs at the office. One important solution I created in Filemaker Pro requires very detailed and exact replication of official court forms with database data entered on the forms. Imagine a form with small type, boxes and spacing that must be exactly as on the official form and data from the database on the form as printed to pdf. One nice thing about Filemaker is that its reporting ability is very flexible. I also have an extensive solution with approx 50 tables/databases to run my small business. I built a calendar, contacts, clients, timelog, and billing solution. I have been bumping my head against Filemaker Pro's 50 database limit for years - server would allow you 125 databases for about $1000. For those of you with no experience with Filemaker, a database is a table. Each file only has one table and all layouts/reports/forms and scripts are in the same file. This setup has pros and cons that are irrelevant now. I am trying to migrate to linux and postgresql. I love the MacOS but they just upgraded to 10.4 (tiger) and made 3 of my iMacs obsolete because they do not have on-board firewire so are not supported by 10.4. Funny how Linux can be compiled to run on a 386 intel chip from a floppy drive but MacOS cannot now run without firewire on a 4 year old computer. Filemaker.com created Filemaker Pro 7 which adds great features and removes the 50 table limit - with a $1000 price tag for 3 units and a 5-simultaneous-user limit. The upgrade to 7 may require extensive changes to my solutions. Now would be a natural time to change to a new system since I have to relearn/change everything anyway. Additionally, I can only assume that I will be in for more of the same treatment as I and people like me get our data more entrenched in their proprietary business models. Thanks for your help. ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Please Recommend a Front End App
with Filemaker, a database is a table. Each file only has one table and all layouts/reports/forms and scripts are in the same file. This setup has pros and cons that are irrelevant now. I'm not familiar with FileMaker, but my understanding is that it can communicate with other databases via ODBC. Would it not be possible to keep your investment (at least for a while) in the front end forms and convert to PostgreSQL as the data store? I don't think FileMaker is quite as flexible as say Access in that way. However they may want to look in to Paradox? Sincerely, Joshua D. Drake ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Please Recommend a Front End App
On May 12, 2005, at 7:06 PM, Kurt Gibson wrote: One important solution I created in Filemaker Pro requires very detailed and exact replication of official court forms with database data entered on the forms. Imagine a form with small type, boxes and spacing that must be exactly as on the official form and data from the database on the form as printed to pdf. One nice thing about Filemaker is that its reporting ability is very flexible. I also have an extensive solution with approx 50 tables/databases to run my small business. I built a calendar, contacts, clients, timelog, and billing solution. I have been bumping my head against Filemaker Pro's 50 database limit for years - server would allow you 125 databases for about $1000. For those of you with no experience with Filemaker, a database is a table. Each file only has one table and all layouts/reports/forms and scripts are in the same file. This setup has pros and cons that are irrelevant now. I'm not familiar with FileMaker, but my understanding is that it can communicate with other databases via ODBC. Would it not be possible to keep your investment (at least for a while) in the front end forms and convert to PostgreSQL as the data store? John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Please Recommend a Front End App
Newbie - please help me choose a direction. I want to know what would be the best front-end app/language to use for postgresql for exact form replication and ease of use. PHP, Python, Java, Rekall (the Kompany), other? All suggestions welcome and appreciated. Background--- I am coming from Filemaker Pro 5.0 and Mac OS 10.2.8. I am the newest of newbies to postgresql. I just downloaded the database today. I have not started the database and do not even know if I can run it on my computers. I have a few old WinTel machines at home but run mainly Macs at the office. One important solution I created in Filemaker Pro requires very detailed and exact replication of official court forms with database data entered on the forms. Imagine a form with small type, boxes and spacing that must be exactly as on the official form and data from the database on the form as printed to pdf. One nice thing about Filemaker is that its reporting ability is very flexible. I also have an extensive solution with approx 50 tables/databases to run my small business. I built a calendar, contacts, clients, timelog, and billing solution. I have been bumping my head against Filemaker Pro's 50 database limit for years - server would allow you 125 databases for about $1000. For those of you with no experience with Filemaker, a database is a table. Each file only has one table and all layouts/reports/forms and scripts are in the same file. This setup has pros and cons that are irrelevant now. I am trying to migrate to linux and postgresql. I love the MacOS but they just upgraded to 10.4 (tiger) and made 3 of my iMacs obsolete because they do not have on-board firewire so are not supported by 10.4. Funny how Linux can be compiled to run on a 386 intel chip from a floppy drive but MacOS cannot now run without firewire on a 4 year old computer. Filemaker.com created Filemaker Pro 7 which adds great features and removes the 50 table limit - with a $1000 price tag for 3 units and a 5-simultaneous-user limit. The upgrade to 7 may require extensive changes to my solutions. Now would be a natural time to change to a new system since I have to relearn/change everything anyway. Additionally, I can only assume that I will be in for more of the same treatment as I and people like me get our data more entrenched in their proprietary business models. Thanks for your help. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] MS-Access and Stored procedures
I was using ADO command object and both refresh method and method with
creating parameter object while working with Access Project...but I didn't
try to use it with PostgreSQL...
I would rather like to have all queries on client side anyway. Therefore I
use pass-through queries. But it doesn't allow using parameters (execept by
concatenation). Also, you can't base subforms on pass-through queries, so
now I use strange combination of local tables, append queries with
parameters based on pass-through queries etc. It works but I'm aware that it
is not very clever:)...
I think that it would be great if pass-through queries could accept
parameters. That would be a powerfull way for executing queries on client,
while keeping all the code on front-end side...But I doubt that Microsoft
will work on further Access improving anymore. It seems that Access is left
behind while VS.NET is top technology. Too bad...
IS there any good book covering MS Access usage as front-end for different
database servers except MSDE ?
Do you have form/subform/subform...based on stored procedures ? If so, how
do you synchronize form with subform ?
Greetings,
Zlatko
- Original Message -
From: "Hervé Inisan" <[EMAIL PROTECTED]>
To:
Sent: Thursday, May 12, 2005 11:06 PM
Subject: Re: [GENERAL] MS-Access and Stored procedures
Hello...This is very interesting. I have also asked myself
how to prepare and execute stored procedures on POstgre from
MS Access.
Could you, please, give some example of Postgre function with
parameters that is executed as stored procedure from MS
Access? How would you pass parameters ? Using ADO Command object?
AFAIK, there are 2 ways to send parameters from Access to a PG function,
using ADO:
1. Write the parameters as the CommandText string:
Set cmd = New ADODB.Command
cmd.ActiveConnection = cnn
cmd.CommandText = "mypgfunction('this is a parameter', 25)"
cmd.CommandType = adCmdStoredProc
cmd.Execute
Set cmd = Nothing
The CommandText string can be the result of a concatenation:
Cmd.CommandText = "mypgfunction('" & strMyString & "', " & intMyValue &
")"
2. Another way is to use "true" ADO parameters:
Set cmd = New ADODB.Command
cmd.ActiveConnection = cnn
cmd.CommandText = "mypgfunction"
cmd.CommandType = adCmdStoredProc
Dim prm1 As ADODB.Parameter
Set prm1 = New ADODB.Parameter
With prm1
.Type = adVarChar
.Direction = adParamInput
.Value = "another string sent to PG"
.Name = "param1"
.Size = 30
End With
Dim prm2 As ADODB.Parameter
Set prm2 = New ADODB.Parameter
With prm2
.Type = adInteger
.Direction = adParamInput
.Value = 25
.Name = "param2"
.Size = 0
End With
cmd.Parameters.Append prm1
cmd.Parameters.Append prm2
cmd.Execute
Set cmd = Nothing
Voilà!
-- Hervé Inisan, www.self-access.com
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [GENERAL] MS-Access and Stored procedures
> Hello...This is very interesting. I have also asked myself
> how to prepare and execute stored procedures on POstgre from
> MS Access.
> Could you, please, give some example of Postgre function with
> parameters that is executed as stored procedure from MS
> Access? How would you pass parameters ? Using ADO Command object?
AFAIK, there are 2 ways to send parameters from Access to a PG function,
using ADO:
1. Write the parameters as the CommandText string:
Set cmd = New ADODB.Command
cmd.ActiveConnection = cnn
cmd.CommandText = "mypgfunction('this is a parameter', 25)"
cmd.CommandType = adCmdStoredProc
cmd.Execute
Set cmd = Nothing
The CommandText string can be the result of a concatenation:
Cmd.CommandText = "mypgfunction('" & strMyString & "', " & intMyValue & ")"
2. Another way is to use "true" ADO parameters:
Set cmd = New ADODB.Command
cmd.ActiveConnection = cnn
cmd.CommandText = "mypgfunction"
cmd.CommandType = adCmdStoredProc
Dim prm1 As ADODB.Parameter
Set prm1 = New ADODB.Parameter
With prm1
.Type = adVarChar
.Direction = adParamInput
.Value = "another string sent to PG"
.Name = "param1"
.Size = 30
End With
Dim prm2 As ADODB.Parameter
Set prm2 = New ADODB.Parameter
With prm2
.Type = adInteger
.Direction = adParamInput
.Value = 25
.Name = "param2"
.Size = 0
End With
cmd.Parameters.Append prm1
cmd.Parameters.Append prm2
cmd.Execute
Set cmd = Nothing
Voilà!
-- Hervé Inisan, www.self-access.com
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] About Types
> > This works fine, but I'm trying to avoid building a > specific type for > > each row returning function. > > Is there any other solution (SQL or PLPGSQL)? > > Yes, return setof record, look at this site, it helped me a lot. > > http://techdocs.postgresql.org/guides/SetReturningFunctions > Thank you Miguel and Otto. RECORD did the trick :-) -- Hervé Inisan. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] alter table owner doesn't update acl information
On Thu, 2005-05-12 at 16:15 -0400, Tom Lane wrote: > "Madeleine Theile" <[EMAIL PROTECTED]> writes: > > But what if he doesn't? Then the only possibility is to drop all the > > views and recreate them as another user in order to fix the issue with > > the acl rights. > > See ALTER OWNER. The intention is to make you give away all the owned > objects before getting rid of the user. > > regards, tom lane > Try it, it doesn't work! I can give you examples in case you need them. Cheers, Madeleine ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] alter table owner doesn't update acl information
"Madeleine Theile" <[EMAIL PROTECTED]> writes: > But what if he doesn't? Then the only possibility is to drop all the > views and recreate them as another user in order to fix the issue with > the acl rights. See ALTER OWNER. The intention is to make you give away all the owned objects before getting rid of the user. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Data Modelling Tools
Do you know if this program will do forward-engineering. I would like to be able to take a copy of my live database. Reverse engineer it into SQLManager, modify it. Then forward-engineer, do you know if SQLManager allows me to do that? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Hrishikesh Deshmukh Sent: May 9, 2005 6:54 PM To: Mark Borins Cc: pgsql-general@postgresql.org; [EMAIL PROTECTED] Subject: Re: [GENERAL] Data Modelling Tools This is my favits just awesome... http://www.sqlmanager.net/ On 5/9/05, Mark Borins <[EMAIL PROTECTED]> wrote: > > > > Postgres Newsgroup, > > > > My company has been looking for a good database modelling tool for postgres > and have yet to find something that completely satisfies our needs. We are > currently using a product called DBWrench which is pretty good and has all > the features we are looking for but is full of bugs and creates much head > ache. > > > > For example, this tool doesn't realize that in postgres you can't add a > column and set not null in one ALTER TABLE statement. So we are forced to > manually comb through the SQL scripts it creates and fix the buggy > statements. > > > > So my question is, postgres general newsgroup, what database modelling tools > do you use? Open source or commercial it doesn't matter to me. > > > > Thank you, > > Mark ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] alter table owner doesn't update acl information
On Wed, 2005-05-11 at 12:04 -0400, Tom Lane wrote: > "Madeleine Theile" <[EMAIL PROTECTED]> writes: > > After I've dropped one of the superusers that created and thus > > owns some of the views and reinstalled it again with a different usesysid > > So reinstall it with the same usesysid --- that's why the option exists > to specify sysid in CREATE USER. > > There is work in progress that will disallow dropping a user that still > owns any objects, but I am unsure if it will be done in time for 8.1. > > regards, tom lane Hi Tom, then I got a follow-up question: dropping the database user was done for a reason: I am building an application with web-frontend. The user management/rights-management is done on the database basis. The user only has views/rules to access the data. So a user get's dropped when he's no longer allowed to log on. In this case it should be possible to drop the user but as a solution have an other user own the appropriate views when these are still needed. Why should it be possible to do it like this? In my case: there are several superusers and all superusers work on the same views. Therefore the idea to have another superuser own these views. OK, so we solved the problem with restoring the usesysid in case the user gets recreated/reactivated. But what if he doesn't? Then the only possibility is to drop all the views and recreate them as another user in order to fix the issue with the acl rights. Is this really the intention? Cheers, Madeleine ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] MS-Access and Stored procedures
Hello...This is very interesting. I have also asked myself how to prepare
and execute stored procedures on POstgre from MS Access.
Could you, please, give some example of Postgre function with parameters
that is executed as stored procedure from MS Access? How would you pass
parameters ? Using ADO Command object?
Greetings,
Zlatko
- Original Message -
From: "Hervé Inisan" <[EMAIL PROTECTED]>
To:
Sent: Thursday, May 12, 2005 6:15 PM
Subject: Re: [GENERAL] MS-Access and Stored procedures
How can I use stored procedures (functions) with MS-Access
2002 connected to PostgreSQL 8.0 ?
An alternative to Philippe's solution is to use ADO.
Here is an sample function :
(assuming ActiveX Data Object lib is checked in the Tools/References menu)
Function ADO_PG()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim cmd As ADODB.Command
Dim strSQL As String
' Open connection
Set cnn = New ADODB.Connection
cnn.CursorLocation = adUseClient
cnn.ConnectionString = "DSN="
cnn.Open
' Display resultset (SELECT...)
Set rst = New ADODB.Recordset
strSQL = "SELECT * FROM a_function_returning_rows()"
rst.Open strSQL, cnn, adOpenDynamic, adLockOptimistic
While Not rst.EOF
Debug.Print rst("one column name here")
' Next record
rst.MoveNext
Wend
rst.Close
Set rst = Nothing
' Execute function (e.g.: INSERT, UPDATE...)
Set cmd = New ADODB.Command
cmd.ActiveConnection = cnn
cmd.CommandText = "another_pg_function()"
cmd.CommandType = adCmdStoredProc
cmd.Execute
Set cmd = Nothing
' Close resources
cnn.Close
Set cnn = Nothing
End Function
Of course, parameters can be sent to stored procedures.
HTH,
-- Hervé Inisan, www.self-access.com
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] About Types
Hi Hervé, You should perhaps use record instead of a particular type. ... RETURNS SETOF record AS ... Otto - Original Message - From: "Hervé Inisan" <[EMAIL PROTECTED]> To: Sent: Thursday, May 12, 2005 8:31 PM Subject: [GENERAL] About Types Hi everybody, I'm a PG newbie, and I'm trying to write a function that returns a set of records. These records come from 2 or more tables (joined). When I have only 1 table, the return type of the function can be SETOF mytable. When I have 2 tables or more, am I obliged to create a type (CREATE TYPE) so that my function can return a SETOF mytype? This works fine, but I'm trying to avoid building a specific type for each row returning function. Is there any other solution (SQL or PLPGSQL)? TIA, -- Hervé Inisan. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] pg_dump: ERROR: Memory exhausted in AllocSetAlloc(875574064)
Tom Lane wrote: > "Reid Thompson" <[EMAIL PROTECTED]> writes: >> Is there any solution to this other than adding memory, or am I >> mis-understanding the error? > >> pg_dump: ERROR: Memory exhausted in AllocSetAlloc(875574064) >> pg_dump: lost synchronization with server, resetting connection > > My bet is that this is actually a corrupt-data problem ... > unless you actually have any 875MB fields in your table. The > implication is that the length word of a variable-width field > contains garbage. More than likely, the whole tuple is > garbaged, but this happens to be the first visible symptom. > > There is plenty of discussion of recovering from data > corruption in the archives, so go have a look. > >> v7.2.3 on RHAT 7.3 linux with 128MB RAM. > > Might want to think about an update sometime, too ;-) > > regards, tom lane No, no 875MB fields -- the largest field is limited to 2048 chars. Heading to the archives. thanks, reid ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Delphi 2005, Postgresql, ZEOS & optimistic locking
Hi!
On Thu, 12 May 2005 19:19:10 +0200
"Philippe Lang" <[EMAIL PROTECTED]> wrote:
> Thanks for your answer, but I'm not sure we are talking about the
> same thing. What I was used to with MS Access/ODBC/Postgresql, in a
multiuser network scenario, is the following:
>
> - User A loads data in a form
> - User B loads the same data in a form.
> - Before user A makes changes, B makes his changes, and commits them.
> - User A makes changes, and tries to commit them, but... the software
> shouts, because meanwhile, someone else made changes to the data
> loaded in the form.
>
> That's what I call optimistic locking.
[...]
> Is there a way to do the same with ZEOS? Or maybe is there another
> mecanism that could be used to do optimistic locking? Some kind of
> "long transactions", in the database server?
You could include the old values of columns which could change in the
where clause of your update statement. For example:
UPDATE mytable SET col1 = 'newValue'
WHERE col1 = 'oldValue1' AND col2 = 'oldValue2';
This way, whenever a row has changed (col1 or col2 have different
values), this update will fail (i.e. no row gets updated).
Ralf Schuchardt
> Hi Philippe,
>
> It still works the same way as the ODBC driver, because of
> Postgresql's multi version concurrency. Zeos uses libpq exactly like
> the ODBC driver does, except it talks directly to libpq without the
> overhead of ODBC, and all you have to deploy with your app is the
> super small libpq.dll.
>
> You really never have to worry about locks.
> However if you want to do a bunch of commands in the context of a
> long transaction you need to pick one of the isolation levels like
> read commited and then in your code do something like this:
>
> with myconnection do
> begin
> Myconnection.connection.StartTransaction;
> try
> sql.add('insert into
> sometable (field1) VALUES ('bla')'); execsql;
> //do some more operations in the
> same transaction sql.clear;
> sql.add('select * from sometable');
> open;
>
> Myconnection.commit;
> except
> //if a error occurs rollback
> everything we did in the transaction Myconnection.connection.Rollback;
> end;
>end;
>
>
> When ever I use Zeos I always set the isolation level to tiNone and
> let the server handle the transactions. When you use tiNone you
> simply do all your statements in one operation, just do a bunch of
> adds and seperate each statement with a semi colon, then do the
> execsql. All the statements will be executed in a single transaction
> by the server and if a error occurs they all get rolled back.
>
> Hope this helps you out.
>
> --
> Tony Caduto
> AM Software Design
> Home of PG Lightning Admin for Postgresql 8.x
> http://www.amsoftwaredesign.com
> Philippe Lang wrote:
> > Hi,
> >
> > I've been testing Delphi 2005 with Postgresql 7.4.5, through ZEOS
> > Lib 6.5.1, and I have a question:
> >
> > How do you implement an optimistic locking strategy with these
> > tools? With an Access front-end, and the ODBC driver, this is
> > completely transparent. A test showed me that the Delphi client
> > writes to the database without worrying about another user doing
> > that meanwhile...
> >
> > I saw it's possible to manipulate the isolation level (read
> > commited or serializable only) in the ZEOS controls, but it does
> > not help at all here. An optimistic lock is a kind of "long
> > transaction" for me.
> >
> > Thanks for your time!
> >
> > Philippe
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] pg_dump fails on 7.4 Postgres
Tom Lane wrote:
"Jimmie H. Apsey" <[EMAIL PROTECTED]> writes:
At this point, I am unable to do a pg_dump using our new Rec Hat
Enterprise Linux AS 4 version of Postgres which is version 7.4.
Here's what I get when I try to do a pg_dump of our database:
[ ~]$ /usr/bin/pg_dump dcf_20050404 >& /~/dcf_20050404_`date +%y%m%d`.dmp
audit(1115732852.025:0): avc: denied { write } for pid=11023
exe=/usr/bin/pg_dump path=/~/dcf_20050404_050510.dmp
dev=sda3 ino=5522308 scontext=user_u:system_r:postgresql_t
tcontext=user_u:object_r:file_t tclass=file
Hmm, what is the SELinuxWe disabled
the SELinux protection for the postgres deamon and were able to
successfully run pg_dump on our new Red Hat Enterprise Linux AS 4
postgres. Do you have any opinion about this 'fix'?
Jim Apsey labeling for pg_dump? Try
$ ls -Z /usr/bin/pg_dump
-rwxr-xr-x root root system_u:object_r:bin_t /usr/bin/pg_dump
If you get something other than that, try "/sbin/restorecon -R /usr/bin"
as root; if that doesn't fix it, you probably need to update your
SELinux policy (RPM selinux-policy-targeted). I am not entirely sure
whether a policy RPM update automatically does the equivalent of
"/sbin/restorecon -R /", but if you don't see the right context after
an update, that's what I'd suggest.
Here's Postgres rpm on the machine in question:
postgresql-7.4.6-1.RHEL4.2
postgresql-server-7.4.6-1.RHEL4.2
I think that was what went out on the RHEL4 CD-ROMs, but why aren't you
running up2date? There are serious known bugs in that version. If
you're paying Red Hat for support, you should be using that support ;-)
regards, tom lane
Thank you once again Tom Lane. We disabled the SELinux protection for
the postgres daemon and were able to successfully run pg_dump on our
new Red Hat Enterprise Linux AS 4 postgres. Do you have any opinion
about this 'fix'? We have hired a Linux professional and he installed
AS 4 on our new Dell Server. I don't know how we keep things
up-to-date with up2date anymore.
Jim Apsey
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] Delphi 2005, Postgresql, ZEOS & optimistic locking
Why not just add a onenter event handler to the forms field in question and just check the data before they edit it. If it's different update the field with the most current data. Access probably just does something similar under the hood for you. I don't think that's a feature of the ODBC driver or is it? If it is you could always use the ODBC driver from Delphi as well. With Dephi/BDE/ODBC, this is different: as soon as you try updating a field that has been modified by someone else meanwhile, the field is automatically updated for you before you start making your own changes, and of course before you try to commit them. That's fine too. I would have preferred an error personnally. Is there a way to do the same with ZEOS? Or maybe is there another mecanism that could be used to do optimistic locking? Some kind of "long transactions", in the database server? Philippe Lang ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] pg_dump: ERROR: Memory exhausted in AllocSetAlloc(875574064)
"Reid Thompson" <[EMAIL PROTECTED]> writes: > Is there any solution to this other than adding memory, or am I > mis-understanding the error? > pg_dump: ERROR: Memory exhausted in AllocSetAlloc(875574064) > pg_dump: lost synchronization with server, resetting connection My bet is that this is actually a corrupt-data problem ... unless you actually have any 875MB fields in your table. The implication is that the length word of a variable-width field contains garbage. More than likely, the whole tuple is garbaged, but this happens to be the first visible symptom. There is plenty of discussion of recovering from data corruption in the archives, so go have a look. > v7.2.3 on RHAT 7.3 linux with 128MB RAM. Might want to think about an update sometime, too ;-) regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] About Types
[EMAIL PROTECTED] wrote: > Hi everybody, > > I'm a PG newbie, and I'm trying to write a function that returns a > set of records. > These records come from 2 or more tables (joined). > > When I have only 1 table, the return type of the function can be SETOF > mytable. > When I have 2 tables or more, am I obliged to create a type (CREATE > TYPE) so that my function can return a SETOF mytype? > > This works fine, but I'm trying to avoid building a specific type for > each row returning function. > Is there any other solution (SQL or PLPGSQL)? Yes, return setof record, look at this site, it helped me a lot. http://techdocs.postgresql.org/guides/SetReturningFunctions --- Miguel ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] About Types
Hi everybody, I'm a PG newbie, and I'm trying to write a function that returns a set of records. These records come from 2 or more tables (joined). When I have only 1 table, the return type of the function can be SETOF mytable. When I have 2 tables or more, am I obliged to create a type (CREATE TYPE) so that my function can return a SETOF mytype? This works fine, but I'm trying to avoid building a specific type for each row returning function. Is there any other solution (SQL or PLPGSQL)? TIA, -- Hervé Inisan. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Thanks. Was: [GENERAL] Need to determine how badly tables need vacuuming
Thanks to both Elein and Tom. pgstattuple (and dbsize) from contrib gave me exactly what I wanted. The statistics views give me something extra. __ Marc signature.asc Description: This is a digitally signed message part
[GENERAL] pg_dump: ERROR: Memory exhausted in AllocSetAlloc(875574064)
Is there any solution to this other than adding memory, or am I mis-understanding the error? pg_dump: ERROR: Memory exhausted in AllocSetAlloc(875574064) pg_dump: lost synchronization with server, resetting connection pg_dump: SQL command to dump the contents of table "table_name" failed: PQendcopy() failed. pg_dump: Error message from server: pg_dump: The command was: COPY "table_name" TO stdout; v7.2.3 on RHAT 7.3 linux with 128MB RAM. Thanks, reid ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Delphi 2005, Postgresql, ZEOS & optimistic locking
Hi,
Thanks for your answer, but I'm not sure we are talking about the same thing.
What I was used to with MS Access/ODBC/Postgresql, in a multiuser network
scenario, is the following:
- User A loads data in a form
- User B loads the same data in a form.
- Before user A makes changes, B makes his changes, and commits them.
- User A makes changes, and tries to commit them, but... the software shouts,
because meanwhile, someone else made changes to the data loaded in the form.
That's what I call optimistic locking.
Between each step, you can easily imagine a duration of several minutes, so
from my point of view, this is not a feature that has to be implemented with
things like isolation level & database locking. These transactions have to be
as short a possible, right?
Until now, I was not able to reproduce optimistic locking with Delphi/ZEOS.
With Dephi/BDE/ODBC, this is different: as soon as you try updating a field
that has been modified by someone else meanwhile, the field is automatically
updated for you before you start making your own changes, and of course before
you try to commit them. That's fine too. I would have preferred an error
personnally.
Is there a way to do the same with ZEOS? Or maybe is there another mecanism
that could be used to do optimistic locking? Some kind of "long transactions",
in the database server?
Philippe Lang
-Message d'origine-
De : Tony Caduto [mailto:[EMAIL PROTECTED]
Envoyé : jeudi, 12. mai 2005 18:43
À : Philippe Lang
Cc : pgsql-general@postgresql.org
Objet : Re: [GENERAL] Delphi 2005, Postgresql, ZEOS & optimistic locking
Hi Philippe,
It still works the same way as the ODBC driver, because of Postgresql's multi
version concurrency.
Zeos uses libpq exactly like the ODBC driver does, except it talks directly to
libpq without the overhead of ODBC, and all you have to deploy with your app is
the super small libpq.dll.
You really never have to worry about locks.
However if you want to do a bunch of commands in the context of a long
transaction you need to pick one of the isolation levels like read commited and
then in your code do something like this:
with myconnection do
begin
Myconnection.connection.StartTransaction;
try
sql.add('insert into sometable (field1)
VALUES ('bla')');
execsql;
//do some more operations in the same
transaction
sql.clear;
sql.add('select * from sometable');
open;
Myconnection.commit;
except
//if a error occurs rollback everything we did
in the transaction
Myconnection.connection.Rollback;
end;
end;
When ever I use Zeos I always set the isolation level to tiNone and let the
server handle the transactions.
When you use tiNone you simply do all your statements in one operation, just do
a bunch of adds and seperate each statement with a semi colon, then do the
execsql. All the statements will be executed in a single transaction by the
server and if a error occurs they all get rolled back.
Hope this helps you out.
--
Tony Caduto
AM Software Design
Home of PG Lightning Admin for Postgresql 8.x http://www.amsoftwaredesign.com
Philippe Lang wrote:
> Hi,
>
> I've been testing Delphi 2005 with Postgresql 7.4.5, through ZEOS Lib 6.5.1,
> and I have a question:
>
> How do you implement an optimistic locking strategy with these tools? With an
> Access front-end, and the ODBC driver, this is completely transparent. A test
> showed me that the Delphi client writes to the database without worrying
> about another user doing that meanwhile...
>
> I saw it's possible to manipulate the isolation level (read commited or
> serializable only) in the ZEOS controls, but it does not help at all here. An
> optimistic lock is a kind of "long transaction" for me.
>
> Thanks for your time!
>
> Philippe
>
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Delphi 2005, Postgresql, ZEOS & optimistic locking
Hi Philippe,
It still works the same way as the ODBC driver, because of Postgresql's multi
version concurrency.
Zeos uses libpq exactly like the ODBC driver does, except it talks directly to
libpq without the overhead of ODBC, and all
you have to deploy with your app is the super small libpq.dll.
You really never have to worry about locks.
However if you want to do a bunch of commands in the context of a long
transaction you need to pick one of the isolation levels
like read commited and then in your code do something like this:
with myconnection do
begin
Myconnection.connection.StartTransaction;
try
sql.add('insert into sometable (field1) VALUES
('bla')');
execsql;
//do some more operations in the same
transaction
sql.clear;
sql.add('select * from sometable');
open;
Myconnection.commit;
except
//if a error occurs rollback everything we did
in the transaction
Myconnection.connection.Rollback;
end;
end;
When ever I use Zeos I always set the isolation level to tiNone and let the
server handle the transactions.
When you use tiNone you simply do all your statements in one operation, just do
a bunch of adds and seperate each
statement with a semi colon, then do the execsql. All the statements will be
executed in a single transaction by the
server and if a error occurs they all get rolled back.
Hope this helps you out.
--
Tony Caduto
AM Software Design
Home of PG Lightning Admin for Postgresql 8.x
http://www.amsoftwaredesign.com
Philippe Lang wrote:
Hi,
I've been testing Delphi 2005 with Postgresql 7.4.5, through ZEOS Lib 6.5.1,
and I have a question:
How do you implement an optimistic locking strategy with these tools? With an
Access front-end, and the ODBC driver, this is completely transparent. A test
showed me that the Delphi client writes to the database without worrying about
another user doing that meanwhile...
I saw it's possible to manipulate the isolation level (read commited or serializable
only) in the ZEOS controls, but it does not help at all here. An optimistic lock is a
kind of "long transaction" for me.
Thanks for your time!
Philippe
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] MS-Access and Stored procedures
> How can I use stored procedures (functions) with MS-Access
> 2002 connected to PostgreSQL 8.0 ?
An alternative to Philippe's solution is to use ADO.
Here is an sample function :
(assuming ActiveX Data Object lib is checked in the Tools/References menu)
Function ADO_PG()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim cmd As ADODB.Command
Dim strSQL As String
' Open connection
Set cnn = New ADODB.Connection
cnn.CursorLocation = adUseClient
cnn.ConnectionString = "DSN="
cnn.Open
' Display resultset (SELECT...)
Set rst = New ADODB.Recordset
strSQL = "SELECT * FROM a_function_returning_rows()"
rst.Open strSQL, cnn, adOpenDynamic, adLockOptimistic
While Not rst.EOF
Debug.Print rst("one column name here")
' Next record
rst.MoveNext
Wend
rst.Close
Set rst = Nothing
' Execute function (e.g.: INSERT, UPDATE...)
Set cmd = New ADODB.Command
cmd.ActiveConnection = cnn
cmd.CommandText = "another_pg_function()"
cmd.CommandType = adCmdStoredProc
cmd.Execute
Set cmd = Nothing
' Close resources
cnn.Close
Set cnn = Nothing
End Function
Of course, parameters can be sent to stored procedures.
HTH,
-- Hervé Inisan, www.self-access.com
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] MS-Access and Stored procedures
Hi,
1) The simplest way to call a function from MS Access is to use a "pass-through
query", like:
SELECT * FROM public."search_article"();
2) If the parameter is/are dynamic, that's more complicated. You have to edit
the query at run-time, like with this kind of code:
Sub search_store(query As String, p As String)
On Error GoTo search_storeError
Dim MyDatabase As DAO.DataBase
Dim MyQueryDef As DAO.QueryDef
cmdSourisSablier
Set MyDatabase = CurrentDb()
If (QueryExists(query)) Then MyDatabase.QueryDefs.Delete query
Set MyQueryDef = MyDatabase.CreateQueryDef(query)
MyQueryDef.Connect = "ODBC;DSN=" & global_dsn_name() & ";"
MyQueryDef.SQL = "SELECT * FROM public." & & query & & "('" & p &
"');"
MyQueryDef.ReturnsRecords = True
MyQueryDef.Close
Set MyQueryDef = Nothing
MyDatabase.Close
Set MyDatabase = Nothing
search_storeExit:
cmdSourisNormal
Exit Sub
search_storeError:
MsgBox "Error in search_store."
Resume search_storeExit
End Sub
That's fine if your query is linked to a report, for example.
3) You can also call a function from code without using a pass-through query,
just to retreive a result:
Function charge_disponible_semaine(code_etape As String, semaine As Integer,
année As Integer) As Double
On Error GoTo charge_disponible_semaineError
Dim MyWorkspace As DAO.Workspace
Dim MyConnection As DAO.Connection
Dim MyRecordset As DAO.Recordset
Dim MySQLString As String
Dim MyODBCConnectString As String
Dim query As String
query = "charge_disponible_semaine"
Set MyWorkspace = CreateWorkspace("ODBCWorkspace", "", "", dbUseODBC)
MyODBCConnectString = "ODBC;DSN=" & global_dsn_name() & ";"
Set MyConnection = MyWorkspace.OpenConnection("Connection1",
dbDriverNoPrompt, , MyODBCConnectString)
MySQLString = "SELECT * FROM public." & & query & & "('" &
code_etape & "', " & semaine & ", " & année & ");"
Set MyRecordset = MyConnection.OpenRecordset(MySQLString, dbOpenDynamic)
With MyRecordset
If Not .EOF Then
charge_disponible_semaine = MyRecordset("charge_disponible_semaine")
Else
charge_disponible_semaine = 0
End If
End With
MyRecordset.Close
Set MyRecordset = Nothing
MyConnection.Close
Set MyConnection = Nothing
MyWorkspace.Close
Set MyWorkspace = Nothing
charge_disponible_semaineExit:
Exit Function
charge_disponible_semaineError:
MsgBox "Error in charge_disponible_semaine."
Resume charge_disponible_semaineExit
End Function
I hope this helps. One or two utility function are needed:
Public Function global_dsn_name() As String
global_dsn_name = "you_dsn_name"
End Function
Public Function QueryExists(QueryName As String) As Boolean
On Error Resume Next
QueryExists = IsObject(CurrentDb().QueryDefs(QueryName))
End Function
Philippe Lang
De : [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] De la part de Ets ROLLAND
Envoyé : jeudi, 12. mai 2005 17:28
À : pgsql-general@postgresql.org
Objet : [GENERAL] MS-Access and Stored procedures
Hello !
How can I use stored procedures (functions) with MS-Access 2002 connected to
PostgreSQL 8.0 ?
Best regards.
Luc
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
[GENERAL] MS-Access and Stored procedures
Hello ! How can I use stored procedures (functions) with MS-Access 2002 connected to PostgreSQL 8.0 ? Best regards. Luc
Re: [GENERAL] Need input on postgres used for phpBB
On Thu, 2005-05-12 at 00:33, Jerome Macaranas wrote: > On Tuesday 10 May 2005 22:00, Scott Marlowe wrote: > > On Mon, 2005-05-09 at 23:35, Jerome Macaranas wrote: > > > i didnt set fsm... the config i paste is all that i put into place... > > > > OK, that's likely a part of your problem. > > > > Did you run the vacuumdb -af I recommended? Did it help? If so, you > > i have a routine of vacuumdb -af every midnight and vacuumdb -a every 8:00 , > 12:00, 17:00 > > what im seeing is: > > > postgres 25542 32.3 10.5 337680 327816 ? R12:17 1:09 postgres: > myuser mydb myip DELETE > postgres 25578 34.5 10.5 337684 327880 ? R12:17 1:13 postgres: > myuser mydb myip DELETE > > delete takes too long to finish.. You might want to run one of those vacuums, like the one at 1700 by hand and do a vacuum verbose to see how many tuples are being reclaimed and how many, if any, are getting left behind etc... > > Note you may also need to reindex as well. > > > ill be doing this.. can i reindex all tables in my DB without starting my db > on standalone mode? Everything but some system indexes and tables, i believe. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] bg-writer queries regarding ( [Testperf-general] BufferSync and bgwriter )
browsing through the archives i came across this:: http://pgfoundry.org/pipermail/testperf-general/2004-December/80.html which seems to be of the time when bgwriter was in development stage i need some clarifications.. is there a separate list of dirty buffers and clean buffers. if there is then its ok bgwriter will start from LRU side and keep writing till it has written either bgwriter_maxpages or bgwriter_percent now if there is no separate list for dirty buffers bgwriter will scan the entire list to check how many are there which it needs for bgwriter_percent... and then write bgwriter_maxpages or bgwriter_percent pages... which ever is less also how should we decide wht should be the bgwriter settings is there a way to determine how many dirty buffers are there currently... should i check the io usage...and if its free increase bgwriter and if its full reduce it which tool should i use for determining the io-usage regards himanshu Yahoo! Mail Stay connected, organized, and protected. Take the tour
Re: [GENERAL] Tsearch2 lexeme position
On Thu, 12 May 2005, Peter Schmelzer wrote: Hi, this is an answer of thread http://archives.postgresql.org/pgsql-general/2003-08/msg00694.php You say it exist a funktion to fin positions an frequency of lexems for own calculate of an ranking since this time. But where is the description of this? May Im to blind but I spend a lot of time to search in internet for this. http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch-V2-intro.html http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch2-guide.html http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch2-ref.html has to less examples to see it. May it is'nt in?.. I need it to serch terms with more than one lexem and later for an ranking for nearst lexems. ranking functions is already aware of this. See tsearch2.sql in contrib/tsearch2/sql for examples. We don't have API to specify your own ranking function. Do you really need it ? greeds Peter Schmelzer ---(end of broadcast)--- TIP 8: explain analyze is your friend Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Table and index size
Dan Black wrote: How can i calculate table and index size on hard disk? See contrib/dbsize in the PostgreSQL source tarball. -Neil ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] how to calculate checkpoint_segments
got it i think i need to sleep i forgot each segment is 16 Mb... sorry for buggung u all himanshuTom Lane <[EMAIL PROTECTED]> wrote: Himanshu Baweja <[EMAIL PROTECTED]>writes:> i am trying to optimise postgres 8 running on a system is there any way to know how wht should be the value of checkpoint_segments...Enough so you usually aren't checkpointing more often than is specifiedby checkpoint_timeout. If you do not know what your system's normalconsumption of WAL is, try setting checkpoint_warning to the same valueas checkpoint_timeout and then keep an eye on the postmaster log to seehow often it complains. You really really *don't* want the thingcheckpointing more often than once every five or ten or so minutes.> increasing checkpoint_segments degrade the performace while checkpointing as it will have a whole lot of dirty buffers to write.This is a fundamental misconception --- the bgwriter exists to prevent that.See the pgsql-perf ormance archives for more information.regards, tom lane__Do You Yahoo!?Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: [GENERAL] Tsearch2 lexeme position
Hi, this is an answer of thread http://archives.postgresql.org/pgsql-general/2003-08/msg00694.php You say it exist a funktion to fin positions an frequency of lexems for own calculate of an ranking since this time. But where is the description of this? May Im to blind but I spend a lot of time to search in internet for this. http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch-V2-intro.html http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch2-guide.html http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch2-ref.html has to less examples to see it. May it is'nt in?.. I need it to serch terms with more than one lexem and later for an ranking for nearst lexems. greeds Peter Schmelzer ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] how to calculate checkpoint_segments
i think there is a bug in the checkpoint warning system. i had set => checkpoint_timeout = 2000 checkpoint_warning = 2100 checkpoint_segments = 256 (for 1st run) and 64 (for 2nd run) now i ran my test application my Wal-logs in PGDATA/pg_xlog increased by around 1.9 GB while in my log file there were only two warnings so i re-run the test application this time turned the archive on... the archive logs generated were also of around 2 GB. but this time i had just one warning in my log file. now theoritically it should give me a warning each-time a checkpoint occurs but i am getting just one am i missing something or there is a problem ... and is there any other way by which i can check how frequently my database is checkpointing Regards Himanshu__Do You Yahoo!?Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
[GENERAL] Table and index size
How can i calculate table and index size on hard disk?
[GENERAL] Delphi 2005, Postgresql, ZEOS & optimistic locking
Hi, I've been testing Delphi 2005 with Postgresql 7.4.5, through ZEOS Lib 6.5.1, and I have a question: How do you implement an optimistic locking strategy with these tools? With an Access front-end, and the ODBC driver, this is completely transparent. A test showed me that the Delphi client writes to the database without worrying about another user doing that meanwhile... I saw it's possible to manipulate the isolation level (read commited or serializable only) in the ZEOS controls, but it does not help at all here. An optimistic lock is a kind of "long transaction" for me. Thanks for your time! Philippe -Message d'origine- De : [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] De la part de Arthur Hoogervorst Envoyé : lundi, 9. mai 2005 12:46 À : pgsql-general@postgresql.org Objet : Re: [GENERAL] Adventures in Quest for GUI RAD Hi, The company I work for actually uses the Zeos lib/Postgres extensively to track the shipping and sales side for almost 3 years. We're still running on a 7.2/7.4 Postgres database, because I haven't been convinced yet to either update or upgrade to 8.x.x. I'm curious if others have successfully moved their (production) database successfully to Postgres 8.0. Regards, Arthur On 5/9/05, Philippe Lang <[EMAIL PROTECTED]> wrote: > Hi, > > I'm testing Delphi 2005 at the moment, with ZEOS Lib (libpq), and I have to > say it work fine, as Tony mentioned. I have a few questions: > > 1) I'm curious: are there a lot of big projects using ZEOS with PG or is that > technology still relatively new? I would like to use it a replacement for > ODBC, but I have no experience regarding its stability. > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Monitoring locks
I wanted to see which tables/transaction have acquired or are waiting for which locks as far as i know there are two ways to do it 1) pg_locks ::: but we need to continously keep getting data from the table and it also locks lock manager data structures which is not a good thing to do... 2) trace_locks,trace_lwlocks ... etc etc ::: well for this my server says undefined parameters... so as far as i know i need to rebuild the server with some flag... is there any other better way by which i can get a list of locks acquired and waited for during entire run of my application thx Himanshu Do you Yahoo!? Yahoo! Small Business - Try our new resources site!
[GENERAL] pgadmin on Solaris
Hi, Is Solaris 10 supported platform for pgAdmin III. If yes, where is the distribution or what's the way to compile it on Solaris 10. Thanks Patrick --- Patrick Fiche email : [EMAIL PROTECTED] tél : 01 69 29 36 18 ---
