[GENERAL] How to retrieve number of rows affected, in an after statement trigger?
Hello. Is it possible to retrieve information about how many rows were changed/inserted in a table that fired after statement trigger? Thanks, Zlatko ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Update a single row without firing its triggers?
Hello. These days I was trying to temporarily disable triggers, too, and had much problems with ALTER TABLE..DISABLE TRIGGER ALL. So, I was googling for another solution and have found this: http://www.varlena.com/GeneralBits/101.php. Works OK in my case... Turn off triggers for bulk load Issue: 9-3 [GENERAL] Turning off triggers ? 25-Nov-2002 Another issue with bulk loading is triggers firing with each row inserted. If you are sure your data is trustworthy and already meets your referential integrity requirements, you can turn off triggers for the bulk load and turn them back on immediately afterward. You should not use this option when your data is not completely clean. The reltriggers field in the pg_class table contains the number of triggers active for each table. It can be set to 0 the disable the triggers, but will need to be reset to the proper number of triggers to have them re-enabled. UPDATE "pg_class" SET "reltriggers" = 0 WHERE "relname" = 'tablename'; UPDATE pg_class SET reltriggers = ( SELECT count(*) FROM pg_trigger where pg_class.oid = tgrelid) WHERE relname = 'table name'; Contributors: Stephan Szabo sszabo at megazone23.bigpanda.com, Jean-Luc Lachance jllachan at nsd.ca, Glen Eustace geustace at godzone.net.nz, Adam Witney awitney at sghms.ac.uk Regards, Zlatko - Original Message - From: Dmitry Koterov To: Postgres General Sent: Friday, July 06, 2007 10:06 PM Subject: [GENERAL] Update a single row without firing its triggers? Hello. Suppose I have a table tbl with columns (a, b, c, counter). And I have 5 ON UPDATE triggers assigned to this table. They process (a, b, c) columns, but never depend on counter. I need to update counter field, but I know that it is totally independent, so - for performance reason I want to temporarily disable all triggers during the tbl.counter updation. How could I do it? (Please do not offer ALTER TABLE tbl DISABLE TRIGGER ALL. It is NOT a production case: ALTER TABLE locks all the table during, so it cannot be used in heavy-loaded systems.) (Please do not also offer top move the counter to another table, because it is used in complex indices, e.g. INDEX ON (counter, a, c) to speedup fetching.) Possible solution: add an additional column named "disable_trg" BOOLEAN: (a, b, c, disable_trg). Then, I use the following UPDATE: UPDATE tbl SET counter = counter + 1, disable_trg = true WHERE a = 10; In each trigger I firstly run an instruction: IF NEW.disable_trg THEN RETURN NEW; END IF; And the latest trigger resets disable_trg field to NULL, so it is not written to the table. So, in some queries I may explicitly specify do I need to disable triggers or not. But this solution (the only possible?) looks like a brute-force method. Possibly Postgrs has another one, better?
[GENERAL] plpgsql equivalent to plperl $_SHARED and plpythonu global dictionary GD?
Does plpgsql has something equivalent to plperl $_SHARED or plpythonu global dictionary GD? Thanks, Zlatko ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] plpgsql equivalent to plperl $_SHARED and plpythonu global dictionary GD?
Hello. OK. I created a new table that holds information about rows inserted/updated in a transaction. I realized that after row-level trigger fires always before after statement-level trigger. Therefore I can use row-level triger to populate the auxiliary table which holds information about affected rows, so that after statement-level trigger can read that information. It works and is fast enough. So, I emulated NEW and OLD for statement level trigger:) Regards, Zlatko - Original Message - From: hubert depesz lubaczewski To: Zlatko Matic Cc: pgsql-general@postgresql.org Sent: Tuesday, July 10, 2007 10:17 AM Subject: Re: [GENERAL] plpgsql equivalent to plperl $_SHARED and plpythonu global dictionary GD? On 7/9/07, Zlatko Matic <[EMAIL PROTECTED]> wrote: Does plpgsql has something equivalent to plperl $_SHARED or plpythonu global dictionary GD? no, but you can use some table to emulate this. or a temp table. depesz -- http://www.depesz.com/ - nowy, lepszy depesz
[GENERAL] free scheduled import utility
Hello. Is there any free program/utility for batch imports from .csv files, that can be easily scheduled for daily inserts of data to PostgreSQL tables? Regards, Zlatko ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] odbc parameters
Hello, I use MS Access for data import. Access imports csv file, make some calculation and transffers data to PostgreSQL. Unfortunately, it takes a lot of time to transfer data to PostgreSQL. My odbc settings are following: [ODBC] DRIVER=PostgreSQL Unicode UID=postgres XaOpt=1 LowerCaseIdentifier=0 UseServerSidePrepare=1 ByteaAsLongVarBinary=0 BI=0 TrueIsMinus1=0 DisallowPremature=1 UpdatableCursors=1 LFConversion=1 ExtraSysTablePrefixes=dd_ CancelAsFreeStmt=0 Parse=1 BoolsAsChar=0 UnknownsAsLongVarchar=0 TextAsLongVarchar=1 UseDeclareFetch=0 Ksqo=1 Optimizer=1 CommLog=0 Debug=0 MaxLongVarcharSize=8190 MaxVarcharSize=255 UnknownSizes=0 Socket=4096 Fetch=100 ConnSettings= ShowSystemTables=0 RowVersioning=1 ShowOidColumn=0 FakeOidIndex=0 Protocol=7.4-1 ReadOnly=0 SSLmode=allow PORT=5432 SERVER=localhost DATABASE=PLANINGZ Could you suggest what parameters values would yield best performance for batch import to PostgreSQL? Thanks. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] odbc parameters
I have already tried COPY. But, it has problems with type castings. For example, COPY operation fails because PostgreSQL can't copy value 7.844,000 into NUMERIC field... Regards, Zlatko - Original Message - From: "A. Kretschmer" <[EMAIL PROTECTED]> To: Sent: Wednesday, July 11, 2007 2:31 PM Subject: Re: [GENERAL] odbc parameters am Wed, dem 11.07.2007, um 14:15:02 +0200 mailte Zlatko Matic folgendes: Hello, please don't hijack other threads. If you only change the subject for a new question, your mail will be sorted completely wrong. (within modern email-clients such thunderbird or mutt) I use MS Access for data import. Access imports csv file, make some calculation and transffers data to PostgreSQL. Import the csv-file directly in postgresql and make the calculations within PG? Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Import to excel to postgres based website?
I would recomend MS Access/ODBC. It works very fine both with linked Excel spreadsheets and PostgreSQL tables. Therefore, you can easily automate data import from Excel to PostgreSQL via Acess. - Original Message - From: "Steve Atkins" <[EMAIL PROTECTED]> To: "pgsql-general General" Sent: Monday, July 23, 2007 5:29 PM Subject: Re: [GENERAL] Import to excel to postgres based website? On Jul 22, 2007, at 11:26 PM, novnov wrote: The answer may or many not be very postgres specific but...what are some possible routes that I could take to allows users to upload data from excel into a postgres via a website? I've never tried anything like this before. Are there standard modules that might help with this or will I have to create from scratch? I am obviously hoping to find something I can just plug in. Users would be expected to have the xls ordered properly for the routine to work. It's something that would be pretty easy, given the right web platform, quite hard using the wrong one. Using perl, say, it would be less than a hundred lines of code. (Take uploaded file, crack xls format to extract data using one of the several CPAN excel reader modules, use DBI to insert it into the database). Uploading CSV (comma separated values) is likely to be easier in languages that don't happen to have support for xls, but CSV can only represent a small subset of xls. If the webserver itself it running on windows then there's all sorts of games you can play by remote controlling an instance of Excel, but doing that tends to be pretty fragile. Cheers, Steve ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(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] bigserial field in a view, nextval function ?
I need to have an "identity" column in a view. I was using bigserial columns in tables and Postgre created nextval function expression automatically. Now I have tried with nextval function in the view, but with no success How can I put a bigserial column in a view ? Thanks. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] bigserial field in a view, nextval function ?
Well, my front-end is MS Access, and Access sees views as tables. When I have forms with subforms there is a problem with linking them if table has no primary key. As Access thinks that a view is a table, I need a primary key in the view. Also, Access doesn't like text field of ODBC-linked table to be primary key (#Deleted phenomena#). Instead, it should be a numeric field. Therefore, I would like to have an autoincrement field, which Access will consider as primary key...I need a calculated bigserial field... Can I accomplish it whith nextval ? Greetings, Zlatko - Original Message - From: "Richard Huxton" To: "Zlatko Matic" <[EMAIL PROTECTED]> Cc: Sent: Thursday, April 21, 2005 5:06 PM Subject: Re: [GENERAL] bigserial field in a view, nextval function ? Zlatko Matic wrote: I need to have an "identity" column in a view. I was using bigserial columns in tables and Postgre created nextval function expression automatically. Now I have tried with nextval function in the view, but with no success How can I put a bigserial column in a view ? What do you mean by an "identity" column in a view? A view is just a named query, so doesn't hold any data of its own. Can you explain what you are trying to achieve? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] Calculated bigserial column in a view
I know that it sounds crazy, but I need a bigserial coulumn in a view that is consisted of several tables. That column should not be based on bigserial column of any table, but should be a calculated column... How can I accomplish it ? Thanks in advance. Zlatko
Re: [GENERAL] psqlodbc MSAccess and Postgresql
Hello, I am currently migrating my MSDE/Access (Access Project) aplication to PostgreSQL. I have experienced a lot of obstacles till now, but anyway it seems quite posible to make a good aplication by this combination of Access front-end and PostgreSQL base. I use the following ODBC settings for linked tables successfully: [ODBC]DRIVER=PostgreSQLUID=zmaticUseServerSidePrepare=0ByteaAsLongVarBinary=0BI=0TrueIsMinus1=1DisallowPremature=0UpdatableCursors=1LFConversion=1ExtraSysTablePrefixes=dd_CancelAsFreeStmt=0Parse=1BoolsAsChar=1UnknownsAsLongVarchar=0TextAsLongVarchar=1UseDeclareFetch=1Ksqo=1Optimizer=1CommLog=0Debug=0MaxLongVarcharSize=8190MaxVarcharSize=254UnknownSizes=0Socket=4096Fetch=100ConnSettings=CLIENT%5fENCODING%3dWIN1250ShowSystemTables=0RowVersioning=1ShowOidColumn=0FakeOidIndex=0Protocol=6.4ReadOnly=0PORT=5432SERVER=localhostDATABASE=MyDatabase Tips and tricks: 1. Disable "Recognize Unique Indexes" every time you link tables. That will allow you to give proper primary keys to Access (you will be prompted). Otherwise, Access do it quite bad. 2- Use Row Versioning 3. Every table must have numeric primary key. Don't use text field as primary key. Access will be confused and you will have "#DELETED#" in your tables. 4. Instead of having JET queries on linked tables, it is better to have good pass-through query. But it doesn't accept parameters, so you will have to combine regular JEt queries with pass-through queries. I use pass-through queries and server-side functions (for calculated columns) that prepare recordset and then filtrate it by regular JET query additionaly (for example: start and end date)... Other possibility is to use server views linked as tables in Access... Greetings Zlatko - Original Message - From: [EMAIL PROTECTED] To: pgsql-general@postgresql.org Sent: Friday, April 22, 2005 6:28 AM Subject: [GENERAL] psqlodbc MSAccess and Postgresql Thanks for the help with /dt... Mike, your post helped me to discover psql command window, I had been trying out the pgAdmin ... I found an interesting article regarding MSAccess as a front end to Postgresql, so I downloaded the psqlodbc installer. I will be getting up the nerve to run the install and go into windows odbc and install that driver so I can use MSAccess with Postgresql ... any tips or caveats appreciated This is the link which is guiding me http://database.sarang.net/database/postgres/postodbc/faq.html
[GENERAL] calculated identity field in views, again...
I asked this question several weeks ago, but nobody proposed a solution, so I am repeating the same question again... I have an MS Access front-end for a database on PostgreSQL. I could use pass-through queries as record sources for reports and it works fine... Unfortunately, MS Access doesn't allow pass-through queries to be records sources for subforms. Therefore I tried to base subforms on regular JET queries on linked tables. It was too slow... Then I tried to base subforms on DAO recordset code generated from pass-through QueryDef objects. Although it worked, it was very unstable... Now it seems to me that POstgreSQL views are the best solution, but Access considers views as tables (!) and needs column with unique values. All those views are complicated queries on several tables, so I can't use any table's column as primary key. I need a calculated column in the view that Access will consider as primary key column. In regular tables, I use bigserial field, but how can I create calculated bigserial column in a view ? Thanks. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] [INTERFACES] calculated identity field in views, again...
You mean VB.NET ? - Original Message - From: "Keith Worthington" <[EMAIL PROTECTED]> To: "Zlatko Matic" <[EMAIL PROTECTED]> Cc: ; <[EMAIL PROTECTED]> Sent: Wednesday, May 04, 2005 5:58 AM Subject: Re: [INTERFACES] calculated identity field in views, again... Zlatko Matic wrote: I asked this question several weeks ago, but nobody proposed a solution, so I am repeating the same question again... I have an MS Access front-end for a database on PostgreSQL. I could use pass-through queries as record sources for reports and it works fine... Unfortunately, MS Access doesn't allow pass-through queries to be records sources for subforms. Therefore I tried to base subforms on regular JET queries on linked tables. It was too slow... Then I tried to base subforms on DAO recordset code generated from pass-through QueryDef objects. Although it worked, it was very unstable... Now it seems to me that POstgreSQL views are the best solution, but Access considers views as tables (!) and needs column with unique values. All those views are complicated queries on several tables, so I can't use any table's column as primary key. I need a calculated column in the view that Access will consider as primary key column. In regular tables, I use bigserial field, but how can I create calculated bigserial column in a view ? Thanks. ---(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 Another option is to toss the MS Access altogether and program the front end entirely in VB. That is what we did. -- Kind Regards, Keith ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 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] [INTERFACES] calculated identity field in views, again...
I will try...if it will be useless, I will quite. Then the only solution
will be make-table query based on nested pass-through query, so I will be
working on local JET tables that will be refreshed from server on each
session. But I would like to avoid local tables, if possible...
How do I create sequence ?
- Original Message -
From: "Greg Stark" <[EMAIL PROTECTED]>
To: "Zlatko Matic" <[EMAIL PROTECTED]>
Cc: ; <[EMAIL PROTECTED]>
Sent: Wednesday, May 04, 2005 7:11 AM
Subject: Re: [GENERAL] [INTERFACES] calculated identity field in views,
again...
"Zlatko Matic" <[EMAIL PROTECTED]> writes:
In regular tables, I use bigserial field, but how can I create calculated
bigserial column in a view ?
You would have to create a sequence and reference it with
nextval('sequencename') in your view. But I doubt very much that it will
do
anything useful.
It sounds like Access wants to be able to update records by looking them
up by
primary key. In that case assigning a new value in your view will make
Postgres make up a brand new number that is utterly useless for finding
the
record again later.
--
greg
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [GENERAL] [INTERFACES] calculated identity field in views, again...
Hello. Thanks for answers... After considering all proposed, I think that it is probably possible to give MS Acces some composite primary keys while linking views as tables, in order to help Access not to fall into "#deleted#", but it would take some extra time to experiment with every view. In meantime, I successfully implemented solution with local tables. Append queries based on pass-through queries are triggered and local tables are refreshed. It seems to be fast and reliable... Thank you anyway, maybe I will try something with views next time... - Original Message - From: "Jeff Eckermann" <[EMAIL PROTECTED]> To: "Zlatko Matic" <[EMAIL PROTECTED]>; ; <[EMAIL PROTECTED]> Sent: Wednesday, May 04, 2005 6:01 PM Subject: Re: [GENERAL] [INTERFACES] calculated identity field in views, again... --- Zlatko Matic <[EMAIL PROTECTED]> wrote: I asked this question several weeks ago, but nobody proposed a solution, so I am repeating the same question again... I have an MS Access front-end for a database on PostgreSQL. I could use pass-through queries as record sources for reports and it works fine... Unfortunately, MS Access doesn't allow pass-through queries to be records sources for subforms. Unless you use unbound form/controls. Which means handling everything in code, which might work out best for you, depending on what you want (this is effectively equivalent to the VB-only option which someone else mentioned). Therefore I tried to base subforms on regular JET queries on linked tables. It was too slow... Then I tried to base subforms on DAO recordset code generated from pass-through QueryDef objects. Although it worked, it was very unstable... Now it seems to me that POstgreSQL views are the best solution, but Access considers views as tables (!) and needs column with unique values. AFAIK a composite key (combination of several columns) should work ok for a primary key for Access. When linking to the view, just select the columns you want to use. Or are you saying that you tried this, and it didn't work? Alternatively, you could try including in your view definition the oid column for each of the constituent tables. If I understand right, oids are globally unique within your database. This assumes that you have created your tables with oids, which may not be the case. Basing a subform on a mult-table join sounds like odd database design. Perhaps if you can explain more about what you are trying to do, people can offer more suggestions. All those views are complicated queries on several tables, so I can't use any table's column as primary key. I need a calculated column in the view that Access will consider as primary key column. In regular tables, I use bigserial field, but how can I create calculated bigserial column in a view ? Thanks. ---(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 __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Adventures in Quest for GUI RAD
Hello. It seems that many people fanatically recommend Delphi, while others fanaticaly despise Delphi. I've sent a question about comparative features of MS Access/Visual Studio/Delphi for working with databases to a newsgroup and people started to quarell instead of argumenting anything ! Interesting I started to be involved in programming and databases two years ago when I got an idea for very specific project. First I had to learn about databses in general, from zero. As MS Office is widespread and present in my job, MS Access was logical decision. I started learning Access and VBA fanatically. Now, I feel that I'm ready for something more powerfull but don't know what to choose...I already started learning VB.NET...There are also some freeware IDE for .NET, like SharpDevelop, for example. But I would like to find some good and easy to use IDE that will be both powerfull enough for making proffessional aplications and easy to use as Access/VBA was...Also, it would be great if such IDE is both for Windows and Linux. Is Delphi solution for me ? - Original Message - From: "Tony Caduto" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: Sent: Friday, May 06, 2005 12:47 AM Subject: Re: [GENERAL] Adventures in Quest for GUI RAD Programmers Paradise has delphi in their catalogs... If you call borland and tell them you want to do a competive upgrade for the old VB you are using they will let you buy the upgrade to Delphi 2005 PRO. You have to ask. Like I said before you get what you pay for and all this time you have been fooling around you could have just bought Delphi. All the time you spent researching is actually costing someone money. Not sure what you mean by this And several years ago, I had some experiences with people using Delphi, and their database server that make me not so enthusiastic to get involved... but I did want to give credit to Tony that his arguments for using Delphi are convincing, if it were readily available. And I do thank him for all his time and efforts to advise me. Delphi has been superior to VB,Access etc since 1995 when version 1 came out. Version 1 had features that MS has only recently included in .net I am just giving you good advice, I have been a developer for a long time and have used VB, C++, Assembly, Access etc and when I say Delphi is the best tool for creating win32 database apps, I mean it. If Access or C# was better I would for sure be using it. Also I have had good luck with Microolap, I use their postgresdac components, and guess what? That MySQL RAD thing they sell is created with.yep you guessed it Delphi. -- Tony Caduto AM Software Design Home of PG Lightning Admin for Postgresql 8.x http://www.amsoftwaredesign.com ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] = or := ?
Hello. This is a newbie question: what is the difference between using = and := in plpgsql function ? I tried with both and in both cases my function works... Thanks. Zlatko ---(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] = or := ?
thanks. - Original Message - From: "Michael Glaesemann" <[EMAIL PROTECTED]> To: "Zlatko Matic" <[EMAIL PROTECTED]> Cc: Sent: Monday, May 09, 2005 11:30 AM Subject: Re: [GENERAL] = or := ? On May 9, 2005, at 18:00, Zlatko Matic wrote: This is a newbie question: what is the difference between using = and := in plpgsql function ? In pl/pgsql, := is assignment = is a logical comparison for equality Some languages, such as perl, use = for assignment and == for logical comparison. x = 1 is a boolean result x := 1 gives x the value of 1 Quick example: x := 2; -- gives x the value of 2 IF (x = 1) THEN -- x = 1 is false x := 3; -- skipped END IF; RETURN X; -- returns 2 Again: x := 1; -- gives x the value of 1 IF (x = 1) THEN -- x = 1 is true x := 3; -- perform assignment END IF; RETURN x; -- returns 3 Hope this helps. Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] backup compress...blobs/insert commands/verbose messages
Can someone explain me the following options while using pgAdimn III for backup: blobs insert commands verbose messages Thanks. - Original Message - From: "Sean Davis" <[EMAIL PROTECTED]> To: "Hrishikesh Deshmukh" <[EMAIL PROTECTED]>; "Ben" <[EMAIL PROTECTED]> Cc: "Postgresql-General" Sent: Monday, May 09, 2005 6:03 PM Subject: Re: [GENERAL] Postgres and GnuPlot As an alternative, be sure to check out pl/R. Everything happens inside the server except for the actual plot which goes to a file. Sean - Original Message - From: "Hrishikesh Deshmukh" <[EMAIL PROTECTED]> To: "Ben" <[EMAIL PROTECTED]> Cc: "Postgresql-General" Sent: Monday, May 09, 2005 11:40 AM Subject: Re: [GENERAL] Postgres and GnuPlot H..looks like i simply can't send the query results to gnuplot without an intermediary!! So far my search results have not yielded anythingi guess intermediary is the only way to !! :((( Hrishi On 5/9/05, Ben <[EMAIL PROTECTED]> wrote: Heh. Funny you should ask; I spent a fair amount of time yesterday doing precisely this. Here's my gnuplot file: set terminal png small color picsize 1000 400 set style fill solid 1.0 border set title "Concurrent listeners" set xdata time set xlabel "Time" set ylabel "Cuncurrent Logged In Listeners" set format x "%Y/%m/%d\n%H:%M:%S" set timefmt "%s" plot "< echo \"select t-7*3600,c from stats_concurrent_listener_count (extract(epoch from '2004/12/13 20:00'::timestamp)::int,null);\" | psql -h plur -U greenroom gr-dev -t | sed 's/|//'" using 1:2 with boxes cat file | gnuplot > image.png works perfectly. The bitch of it is that I seem to be having a hard time running this as a cgi. I can't figure out. Not that this question has anything to do with postgres, but maybe somebody else can tell me where I'm going wrong? My CGI is: #!/bin/bash echo -e "Content-type: image/png\n\n"; echo -e "set terminal png small color picsize 1000 400\nset style fill solid 1.0 border\nset title \"Concurrent listeners\"\nset xdata time\nset xlabel \"Time\"\nset ylabel \"Cuncurrent Logged In Listeners \"\nset format x \"%Y/%m/%d\\\n%H:%M:%S\"\nset timefmt \"%s\"\nplot \"< echo \\\"select t-7*3600,c from stats_concurrent_listener_count (extract(epoch from '2004/12/13 20:00'::timestamp)::int,null);\\\" | / usr/bin/psql -h plur -U greenroom gr-dev -t | sed 's/|//'\" using 1:2 with boxes" | /usr/bin/gnuplot ...and it seems that when printing to stdout, it gives a truncated version of the file. When redirecting, it does not. Maybe that's not too surprising, if apache is looking for a null to see when stdout stops but I don't know how to get around that. On May 9, 2005, at 7:51 AM, Hrishikesh Deshmukh wrote: > Hi All, > > Has anybody tried using gnuplot to plot results from queries; there > are some suggestions given in PostgreSQL Developer's Handbook but i > have not any luck so far! > Any pointers as how to send results from queries straight to a simple > plotting utility (ex:gnuplot)! > > Thanks, > Hrishi > > ---(end of > broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 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 - Developers start develop Access components for Postgres?
What about Lazarus Has anybody tried working with Lazarus? - Original Message - From: "Daniel Schuchardt" <[EMAIL PROTECTED]> To: Sent: Tuesday, May 10, 2005 2:27 PM Subject: [GENERAL] Delphi - Developers start develop Access components for Postgres? Hy, in thread "Adventures in Quest for GUI RAD" there awnsered some Delphi - Developers. We actually use Delphi to access PostgreSQL too. But with some problems: The older Versions of microolab postgresql dac are absolutely trash. I haven't tried the newer ones. ODBC / Delphi BDE is ripped out by Borland. ADO is Microsoft and we look for a not Microsoft dependend product because are application also runs on unix (with Kylix). dbexpress is really hard to handle; I dont have extended experience with DBExpress and vitavoom - dbexpress driver. (www.vitavoom.com). we use zeos but the older version because of we found many problems in 6.X - Versions. I have fixed up many bugs in zeos 5.4 so it now works fine with postgres.(7.3-8 running) All in all i have to say Zeos works but i'm not really happy with zeos because it seems not clear if it has a future. Components are very inefficient, every time you open a table all configuration / table meta data is fetched. No internal caching or sth like this. (they are still faster than dbexpress / odbc / Zeos6.X) Fields with unknown length are mapped wrong. and so on- With Zeos 6.X you cannot use Postgresql - search path because 6.X Versions always wrote schema name before table name. 6.X does not support Cursor Fetch. 6.X isn't speacially for postgres, they try to support all bigger databases. Has anyone tried .net - pgsql - driver with Delphi 2005? All in all my opinion is that there is no really good solution to access Postgres from Delphi. Perhaps we should start a project at pgfoundry "PostgreSQL - Access for Delphi". Opinions? Daniel. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] lazarus/zeos - installation ?
I have installed lazarus. A have also downloaded zeos library, but don't know how to install it. What am I suppsoed to do? Thanks. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
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] 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 Mike.
I have found your code to be very usefull for me.
I combined it with some other codes in order to establich a procedure for
startup on client.
The problem apers with relinking tables. It seems that Access creates fake
indexes automaticcaly whern relinking using your proposed conncetion string.
So, I should disable that option, but don't know which option is that ?
Where can I find description of these constants in connection string (A, B,
C)?
- Original Message -
From: "Relyea, Mike" <[EMAIL PROTECTED]>
To:
Sent: Friday, May 13, 2005 2:12 PM
Subject: Re: [GENERAL] MS-Access and Stored procedures
I do the same thing with DAO and changing my querydef at run time, but I've
added a few 'enhancements'. First, I use a DSNLess connection - that way I
don't have to set up a DSN on each client's PC. Check out
http://gborg.postgresql.org/project/psqlodbc/genpage.php?howto-accessvba
for more info on it. Essentially, the connection string is generated from
a form that requests the username and password of the user. It looks like
this:
+
Dim strConnInfo as string, strConnUserPass as string, strConnParms as
string, strConnection as string
strConnInfo =
"ODBC;Driver={PostgreSQL};Server=MyServer;Port=5432;Database=MyDB;"
strConnUserPass = "Uid=" & Me.UserName.Value & ";Pwd=" & Me.Password.Value
& ";"
strConnParms = "A0=0;A1=6.4;A2=0;A3=0;A4=1;A5=0;A6=;A7=100;A8=4096;A9=1;"
& _
"B0=254;B1=8190;B2=0;B3=0;B4=1;B5=1;B6=0;B7=0;B8=0;B9=1;" & _
"C0=0;C1=0;C2=dd_"
strConnection = strConnInfo & strConnUserPass & strConnParms
++
Next, I created a function to create the query because I do it frequently:
++
Function DefineQuery(strName As String, _
strConnect As String, _
intTimeout As Integer, _
strSQL As String, _
boolReturnsRecords As Boolean _
)
'A function to create a query given the listed parameters
On Error GoTo ErrorHandler
Dim db As DAO.Database
Dim qrydef As DAO.QueryDef
Set db = CurrentDb
db.QueryDefs.Delete (strName) 'Delete the query first if it exists
'Create the query
create_query:
Set qrydef = db.CreateQueryDef(strName)
qrydef.Connect = strConnect
qrydef.ODBCTimeout = intTimeout
qrydef.SQL = strSQL
qrydef.ReturnsRecords = boolReturnsRecords
ErrorHandler:
Select Case Err.Number
Case 0
Err.Clear
Case 2501
Err.Clear
Case 3265
GoTo create_query
Case 3151
MsgBox "Connection to database was lost. Please close and reopen
this program."
Case Else
MsgBox "An error occured in the function 'DefineQuery': " &
Err.Number & " " & Err.Description
End Select
End Function
+
Lastly, I dump the results of my passthrough query to a local table
because I found I got _much_ better response time that way when opening
the report that the data is used for. Again, I created a function to do
that:
+
Function TransferQueryToTable(strqryName As String, strtblName As String)
On Error GoTo ErrorHandler
Dim qryrs As DAO.Recordset, tblrs As DAO.Recordset
Dim I As Integer
'Define the recordsets we're working with
Set qryrs = CurrentDb.QueryDefs(strqryName).OpenRecordset
Set tblrs = CurrentDb.TableDefs(strtblName).OpenRecordset
'Make sure the table is empty before we fill it
If tblrs.RecordCount = 0 Then
qryrs.MoveFirst 'Make sure we start with the first record in the query
tblrs.AddNew 'Prepare the table for the first record
Else
tblrs.MoveFirst
Do Until tblrs.EOF
tblrs.Delete 'Delete all records in the table
tblrs.MoveNext
Loop
qryrs.MoveFirst 'Make sure we start with the first record in the query
tblrs.AddNew 'Prepare the table for the first record
End If
'Loop through records
Do Until qryrs.EOF
For I = 0 To qryrs.Fields.count - 1
tblrs(I) = qryrs(I) 'Set each field in the table equal to each field in
the query
Next I
qryrs.MoveNext 'Move to the next record in the query
tblrs.Update 'Update the table
tblrs.AddNew 'Prepare the table for the next record
Loop
'close the recordsets
qryrs.Close
tblrs.Close
ErrorHandler:
Select Case Err.Number
Case 0
Err.Clear
Case 3021
MsgBox "No data available"
Case Else
MsgBox "An error occured in the function 'TransferQueryToTable': "
& Err.Number & " " & Err.Description
End Select
End Function
+++
[GENERAL] ODBC connection string-constants A,B,C ?
Hello. When building ODBC connection string for PostgreSQL there are constants beginning with A, B and C. Where can I find description of each of these ? Thanks. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] PostgreSQL/MS Access - solution for passing parameters to pass through queries
Hi everybody! Recently I was struggling with client/server issues in MS Access/PostgreSQL combination. Although Access is intuitive and easy to use desktop database solution, many problems appear when someone is trying to use it as front-end for real server database systems such as PostgreSQL or MySQL. One of these problems is regarding pass-through queries and parameters. I wanted to have all the code on client, while executing it on the server in order to increase performance and speed. Therefore I created pass-through queriers for my forms and reports. The problem was that I couldn't pass parameters for where clause criteria, such as start and end-date. Therefore I have written procedure that passes parameters to pass-through queries. I hope it will help to those dealing with the same problem... For this method we use 2 saved pass-through queries.First, we have query with parameter name included in code in criteria expression. Then, we have another query which SQL string is generated from the first one. The SQL string is refreshed each time before query execution, so that parameter name is replaced with actual value. The form is based on that executive pass-through query... ' ' This code has a list of saved pass-through queries along with parameters.and can be called ' on Click event. ' Theprocedure calls function ParametersToQueries () that recreates SQL string of executive query. ' written by: Zlatko Matic ' Sub QueriesAndParameters () Dim ws As DAO.Workspace Dim db As DAO.DATABASE Dim QueryName As String Dim NumberOfParameters As Integer On Error GoTo ErrorHandler DoCmd.Hourglass True Set ws = DBEngine(0) Set db = CurrentDb 'List of queries and parameters...For example: QueryName = "SomeQuery" NumberOfParameters = 3 ' Transfer name of the query and parameters to funtion ParametersToQuery Call ParametersToQuery (QueryName, NumberOfParameters, _ "StartDate", Format([Forms]![MenuForm]![START_DATE], "-mm-dd"), _ "EndDate", Format([Forms]![MenuForm]![END_DATE], "-mm-dd"), _ "Option", [Forms]![MenuForm]![OPTION]) Exit: DoCmd.Hourglass False Exit Sub ErrorHandler: Dim strErr As String strErr = "VBA-Error Information" & vbNewLine strErr = strErr & "Number: " & vbTab & vbTab & Err.Number & vbNewLine strErr = strErr & "Description: " & vbTab & Err.Description & vbNewLine strErr = strErr & "LastDLLError: " & vbTab & Err.LastDllError & vbNewLine strErr = strErr & vbNewLine MsgBox strErr, vbOKOnly + vbExclamation, "Error" Resume Exit End Sub Here is the code for function ParametersToQuery: ' ' This function recreates SQL string of executive pass-through query ' written by: Zlatko Matic ' Function ParametriziranjePstUpita(QueryName As String, NumberOfParameters As Integer, ParamArray Parameters () As Variant) Dim ws As DAO.Workspace Dim db As DAO.DATABASE Dim qdf As DAO.QueryDef Dim strSQL As String Dim strConnect As String Dim PstQueryName As String Dim n As Integer Dim x As Integer Dim ParameterName As Variant Dim ParameterValue As Variant Dim Parameter As Variant On Error GoTo ErrorHandler DoCmd.Hourglass True Set ws = DBEngine(0) Set db = CurrentDb PstQueryName = QueryName & "_prm" 'Open thempass-through query to extract SQL string Set qdf = db.QueryDefs(PstQueryName) strSQL = qdf.SQL strConnect = qdf.Connect 'Creation of new SQL string 'Assign parameters If NumberOfParameters > 0 Then x = 0 For n = 0 To ((NumberOfParameters * 2) - 1) Step 2 ParameterName = Parameters (n) ParameterValue = Parameters (n + 1) strSQL = Replace(strSQL, ParameterName, ParameterValue) x = x + 1 Next n End If qdf.Close 'Assignig of changed SQL string to executive pass-through query If ObjectExists(acQuery, QueryName) Then 'If executive query exists, open it Set qdf = db.QueryDefs(QueryName) qdf.Connect = strConnect Else 'If executive pass-thrpough query doesn't exist, create it Set qdf = db.CreateQueryDef(QueryName) qdf.Connect = strConnect qdf.ODBCTimeout = 0 qdf.ReturnsRecords = True End If 'Set SQL string qdf.SQL = strSQL qdf.
[GENERAL] Audit trail ?
Hello. I must have audit trail of all insert/update/delete on several table. I have several questions regarding that: 1. Is it better to have one audit trail table that collects insert/update/delete of all audited tables, or it is better to have separate audit trail table for every audited table ? 2. To use triggers or rules ? Example for both ? 3. Could someone give me an example of a successfull audit trail solution ? I'm running on lack of time, so any help would be precious... Thanks. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Audit trail ?
Hello, Mike! Your solution for audit trail is wonderfull! Easy and elegant ! It helped me a lot and I successfully implemented it, with small modifications. Thanky you very much! - Original Message - From: "Mike Rylander" <[EMAIL PROTECTED]> To: "Zlatko Matic" <[EMAIL PROTECTED]> Cc: "Postgresql-General" Sent: Sunday, May 29, 2005 6:21 PM Subject: Re: [GENERAL] Audit trail ? On 5/29/05, Zlatko Matic <[EMAIL PROTECTED]> wrote: Hello. I must have audit trail of all insert/update/delete on several table. I have several questions regarding that: 1. Is it better to have one audit trail table that collects insert/update/delete of all audited tables, or it is better to have separate audit trail table for every audited table ? 2. To use triggers or rules ? Example for both ? 3. Could someone give me an example of a successfull audit trail solution ? I'm running on lack of time, so any help would be precious... We use the "audit table per real table" approach. The SQL script to create the audit trail functions and triggers is attached. There are three example audit trail table creation calls right before the COMMIT. Hope that helps! -- Mike Rylander [EMAIL PROTECTED] GPLS -- PINES Development Database Developer http://open-ils.org ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] How to add an INHERITS to an already populated table ?
Hi. Recently I have tried to do the same thing and I coudn't include inheritence in existing tables. After a half of day of frustration, I have got an idea. I have successfully done it by using EMS PostgreSQL Manager Lite (you can download it from the net). There is an option "Duplicate" in EMS Manager, by which you can duplicate any table with all properties except foreign keys. During that process you can modify code (before Commit) so you can include inheritence in your new table. Then you delete original table, rename new table to old name and recreate foreign key... Well, I'm a newbie, so maybe someone more experienced offer you some better advice. If not, this will work... Bye. - Original Message - From: "David Pradier" <[EMAIL PROTECTED]> To: Sent: Tuesday, May 31, 2005 10:10 AM Subject: [GENERAL] How to add an INHERITS to an already populated table ? Hi everybody, is it possible to add some inheritance lively, without doing a dump/restore ? Some bits of information to explain why I'd like to do that : I've got those big tables, without correct constraints, sometimes even without foreign keys et with sometimes some problems of data corruption, coming from the application part. What I'd really like to do is to add to every important table some inheritance to a table we have which contains the following information : (created_by, modified_by, date_of_creation, date_of_modification). And of course, I can't stop the server. Best regards, David -- [EMAIL PROTECTED] - tel: 01.46.47.21.33 - fax: 01.46.47.21.37 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Pb with linked tables on PG8
Hello. I have experoenced the same problem. It seems to be common problem with Access connectiong to ODBC data source. It seems that Access has some problems to determine primary key... You should not use textual fields as primary key. Insted, add some bigserial (integer autoincrement field) into your tables and make such field primary key. Then relink your tables. This will solve your problem. Also, it is good to add timestamp field into tables. Also, be aware that your tables names should not be too long, because if they are long you will have problems with relinking. Access would not relink correctly (preassuming that you will use DSN-less and relinking on each startup). Bye. Zlatko For your information, this is explanation from MSDN: " ACC: "#Deleted" Errors with Linked ODBC Tables View products that this article applies to. Article ID : 128809 Last Review : May 6, 2003 Revision : 1.0 This article was previously published under Q128809 On this page SYMPTOMS CAUSE RESOLUTION MORE INFORMATION Steps to Reproduce Behavior APPLIES TO SYMPTOMS When you retrieve, insert, or update records in a linked ODBC table, each field in a record contains the "#Deleted" error message. When you retrieve, insert, or update records using code, you receive the error message "Record is deleted." Back to the top CAUSE The Microsoft Jet database engine is designed around a keyset-driven model. This means that data is retrieved, inserted, and updated based on key values (in the case of a linked ODBC table, the unique index of a table). After Microsoft Access performs an insert or an update of a linked ODBC table, it uses a Where criteria to select the record again to verify the insert or update. The Where criteria is based on the unique index. Although numerous factors can cause the select not to return any records, most often the cause is that the key value Microsoft Access has cached is not the same as the actual key value on the ODBC table. Other possible causes are as follows: Having an update or insert trigger on the table, modifying the key value. Basing the unique index on a float value. Using a fixed-length text field that may be padded on the server with the correct amount of spaces. Having a linked ODBC table containing Null values in any of the fields making up the unique index. These factors do not directly cause the "#Deleted" error message. Instead, they cause Microsoft Access to go to the next step in maintaining the key values, which is to select the record again, this time with the criteria based on all the other fields in the record. If this step returns more than one record, Microsoft Access returns the "#Deleted" message because it does not have a reliable key value to work with. If you close and re-open the table or choose Show All Records from the Records menu, the "#Deleted" errors are removed. Microsoft Access uses a similar process to retrieve records from an linked ODBC table. First, it retrieves the key values and then the rest of the fields that match the key values. If Microsoft Access is not able to find that value again when it tries to find the rest of the record, it assumes that the record is deleted. Back to the top RESOLUTION The following are some strategies that you can use to avoid this behavior: Avoid entering records that are exactly the same except for the unique index. Avoid an update that triggers updates of both the unique index and another field. Do not use a Float field as a unique index or as part of a unique index because of the inherent rounding problems of this data type. Do all the updates and inserts by using SQL pass-through queries so that you know exactly what is sent to the ODBC data source. Retrieve records with an SQL pass-through query. An SQL pass-through query is not updateable, and therefore does not cause "#Delete" errors. Avoid storing Null values within any field making up the unique index of your linked ODBC table. Back to the top MORE INFORMATION Note: In Microsoft Access 2.0, linked tables were called attached tables. Steps to Reproduce Behavior 1. Open the sample database Northwind.mdb (or NWIND.MDB. in Microsoft Access 2.0) 2. Use the Upsizing Tools to upsize the Shippers table. NOTE: This table contains an AutoNumber field (or Counter field in Microsoft Access 2.0) that is translated on SQL Server by the Upsizing Tools into a trigger that emulates a counter. 3. Open the linked Shippers table and enter a new
Re: [GENERAL] pg_dumpall
Done. Thanks. - Original Message - From: <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>; Sent: Thursday, June 16, 2005 3:06 PM Subject: Re: [GENERAL] pg_dumpall On 16 Jun 2005 at 11:36, Zlatko Matic wrote: Ok. but I have a problem. When i double-click, a black window appears, prompting for password. But I can't input anything! Cursor is just blinking. I'm typing and nothing apears. If I click enter, pg_dumpall just closes... Postgres 8.02 is installed on Windows XP... Don't double-click on the applicationopen a console window first by going to Start -> All programs -> Accessiories -> Command prompt, then run it by typing (and substituing as necessary for the bits in <...>): c:\\bin\pg_dumpall I'd recommend including the option -U which makes the connection as that user - otherwise it tries to connect as the Windows user under which you're current logged into the machine, and if there is no corresponding user in Postgres the connection will fail. --Ray. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] pg_dumpall
Ok. - Original Message - From: <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>; Sent: Thursday, June 16, 2005 3:06 PM Subject: Re: [GENERAL] pg_dumpall On 16 Jun 2005 at 11:36, Zlatko Matic wrote: Ok. but I have a problem. When i double-click, a black window appears, prompting for password. But I can't input anything! Cursor is just blinking. I'm typing and nothing apears. If I click enter, pg_dumpall just closes... Postgres 8.02 is installed on Windows XP... Don't double-click on the applicationopen a console window first by going to Start -> All programs -> Accessiories -> Command prompt, then run it by typing (and substituing as necessary for the bits in <...>): c:\\bin\pg_dumpall I'd recommend including the option -U which makes the connection as that user - otherwise it tries to connect as the Windows user under which you're current logged into the machine, and if there is no corresponding user in Postgres the connection will fail. --Ray. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] automating backup ?
How to automate backup, so that Postgres automatically backups, for example, once in a week ? The same question about vacuum ? Concerning backup, how to prevent that someone makes a copy (for example pg_dumpall) of a database, then installs new instance of Postgres, create the same user acount that was the original owner and then restore the database. In that case all restrictions would be overriden, right ? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] automating backup ?
Thanks Fuhr. Anybody can tell me how to do it on Windows XP ? Thanks. - Original Message - From: "Michael Fuhr" <[EMAIL PROTECTED]> To: "Zlatko Matic" <[EMAIL PROTECTED]> Cc: Sent: Sunday, June 26, 2005 2:59 PM Subject: Re: [GENERAL] automating backup ? On Sun, Jun 26, 2005 at 11:18:31AM +0200, Zlatko Matic wrote: How to automate backup, so that Postgres automatically backups, for example, once in a week ? Using the operating system's mechanism for scheduling jobs to run periodically. For example, cron on Unix-like systems. The same question about vacuum ? Same answer as above. See also contrib/pg_autovacuum. Concerning backup, how to prevent that someone makes a copy (for example pg_dumpall) of a database, then installs new instance of Postgres, create the same user acount that was the original owner and then restore the database. In that case all restrictions would be overriden, right ? All what restrictions? If you've granted users permission to select certain data, then they can copy that data to somewhere else and do whatever they like with it; but with the original data they can do only what you grant them permission to do. If you don't want users to copy data then don't grant them select privilege on it, and make sure they don't have database superuser or operating system superuser (administrator) privileges on the database server. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] automating backup ?
thank you Andreas! - Original Message - From: "Andreas" <[EMAIL PROTECTED]> To: Cc: "Zlatko Matic" <[EMAIL PROTECTED]> Sent: Monday, June 27, 2005 12:43 PM Subject: Re: [GENERAL] automating backup ? Zlatko Matic schrieb: Thanks Fuhr. Anybody can tell me how to do it on Windows XP ? Thanks. That's again a pure Windows issue, but not commonly needed. I guess, it should be in the Windows PG-FAQ, too. I'll describe the way you go with Windows 2000. If you are lucky WinXP Pro does it the same way and if you have only WinXP Home you might be lucky if you have the needed timer-service at all. Please try it and come back to the list and tell us what you did and if it solved your problem. First you write a text file, that contains all commands you want to get executed. All those which you would otherwise type yourself on the command line. Call this file zlatko_backup.batjust that it has a unique name. In Windows' start menue you find "programs". There is a subfolder where I don't know the English name, but it holds among other things the calculator and a systemprograms-folder. In this Systemprograms-folder you should find "planned tasks" as a folder. Open the "planned tasks" folder. It is empty. Right click in it and select "new" --> "planned task". Select it and you will get an settings menue of a task where you can define what program should run when. Enter here the path to zlatko_backup.bat. Provided Window's planned-tasks-service runs all is set now. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] automating backup ?
Hello. I created a Windows XP schedule for backup, following your instruction. Now I have a .bat file with this script: cd D:\Program Files\PostgreSQL\8.0\bin pg_dumpall >D:\MYDATABASE_DUMPALL -U postgres pg_dumpall >D:\MYDATABASE_SHEMA -U postgres -s pg_dumpall >D:\MYDATABASE_GLOBALS -U postgres -g Well, it works OK, but prompts for password every time. Is there any way that I pass the superuser password (off course, in safe way) so that it works automatically without prompting for password ? - Original Message - From: "Andreas" <[EMAIL PROTECTED]> To: Cc: "Zlatko Matic" <[EMAIL PROTECTED]> Sent: Monday, June 27, 2005 12:43 PM Subject: Re: [GENERAL] automating backup ? Zlatko Matic schrieb: Thanks Fuhr. Anybody can tell me how to do it on Windows XP ? Thanks. That's again a pure Windows issue, but not commonly needed. I guess, it should be in the Windows PG-FAQ, too. I'll describe the way you go with Windows 2000. If you are lucky WinXP Pro does it the same way and if you have only WinXP Home you might be lucky if you have the needed timer-service at all. Please try it and come back to the list and tell us what you did and if it solved your problem. First you write a text file, that contains all commands you want to get executed. All those which you would otherwise type yourself on the command line. Call this file zlatko_backup.batjust that it has a unique name. In Windows' start menue you find "programs". There is a subfolder where I don't know the English name, but it holds among other things the calculator and a systemprograms-folder. In this Systemprograms-folder you should find "planned tasks" as a folder. Open the "planned tasks" folder. It is empty. Right click in it and select "new" --> "planned task". Select it and you will get an settings menue of a task where you can define what program should run when. Enter here the path to zlatko_backup.bat. Provided Window's planned-tasks-service runs all is set now. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] automating backup ?
Hi. I can't find pgpass.conf file. It should be in Application Data subdirectory, but there is no PostgreSQL subdirectory in Application Data directory (!?). I couldn't find pgpass.conf even by searching the hard disk.. Regards, Zlatko - Original Message - From: "Magnus Hagander" <[EMAIL PROTECTED]> To: "Zlatko Matic" <[EMAIL PROTECTED]>; "Andreas" <[EMAIL PROTECTED]>; Sent: Tuesday, June 28, 2005 10:16 AM Subject: Re: [GENERAL] automating backup ? Hello. I created a Windows XP schedule for backup, following your instruction. Now I have a .bat file with this script: cd D:\Program Files\PostgreSQL\8.0\bin pg_dumpall >D:\MYDATABASE_DUMPALL -U postgres pg_dumpall >D:\MYDATABASE_SHEMA -U postgres -s pg_dumpall >D:\MYDATABASE_GLOBALS -U postgres -g Well, it works OK, but prompts for password every time. Is there any way that I pass the superuser password (off course, in safe way) so that it works automatically without prompting for password ? Use a pgpass.conf file: http://www.postgresql.org/docs/8.0/static/libpq-pgpass.html (remember the file has to be in the profile of the account that executes the step. And be sure to protect it with file system ACLs so other users can't read it) //Magnus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] automating backup ?
I would appreciate some example. Thanks. - Original Message - From: "Relyea, Mike" <[EMAIL PROTECTED]> To: "Zlatko Matic" <[EMAIL PROTECTED]>; Sent: Tuesday, June 28, 2005 3:56 PM Subject: Re: [GENERAL] automating backup ? That's because they don't exist. You need to create them. I did it on WinXP and it works fine. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Zlatko Matic Sent: Tuesday, June 28, 2005 9:08 AM To: Magnus Hagander; Andreas; pgsql-general@postgresql.org Subject: Re: [GENERAL] automating backup ? Importance: High Hi. I can't find pgpass.conf file. It should be in Application Data subdirectory, but there is no PostgreSQL subdirectory in Application Data directory (!?). I couldn't find pgpass.conf even by searching the hard disk.. Regards, Zlatko - Original Message - From: "Magnus Hagander" <[EMAIL PROTECTED]> To: "Zlatko Matic" <[EMAIL PROTECTED]>; "Andreas" <[EMAIL PROTECTED]>; Sent: Tuesday, June 28, 2005 10:16 AM Subject: Re: [GENERAL] automating backup ? Hello. I created a Windows XP schedule for backup, following your instruction. Now I have a .bat file with this script: cd D:\Program Files\PostgreSQL\8.0\bin pg_dumpall >D:\MYDATABASE_DUMPALL -U postgres pg_dumpall >D:\MYDATABASE_SHEMA -U postgres -s pg_dumpall >D:\MYDATABASE_GLOBALS -U postgres -g Well, it works OK, but prompts for password every time. Is there any way that I pass the superuser password (off course, in safe way) so that it works automatically without prompting for password ? Use a pgpass.conf file: http://www.postgresql.org/docs/8.0/static/libpq-pgpass.html (remember the file has to be in the profile of the account that executes the step. And be sure to protect it with file system ACLs so other users can't read it) //Magnus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] automating backup ?
Now I have pgpass.conf file in D:\Documents and Settings\Zlatko\Application Data\postgresql content of pgpass.conf is: localhost:*:MONITORINGZ:postgres:tralalala content of backup_script.bat is: cd D:\Program Files\PostgreSQL\8.0\bin pg_dumpall >D:\MONITORINGZ_DUMPALL -U postgres still prompts for password...What is wrong ? - Original Message - From: "Relyea, Mike" <[EMAIL PROTECTED]> To: "Zlatko Matic" <[EMAIL PROTECTED]>; Sent: Tuesday, June 28, 2005 7:55 PM Subject: RE: [GENERAL] automating backup ? 1) Create the directory %APPDATA%\postgresql in my case it's C:\Documents and Settings\Administrator\Application Data\postgresql 2) Create the file %APPDATA%\postgresql\pgpass.conf I created it with Notepad 3) Put the necessary information into %APPDATA%\postgresql\pgpass.conf I put one line in mine - localhost:*:myDBname:myUserName:myPassword 4) Create the batch file to run your backup command In my case, it reads: "C:\Program Files\PostgreSQL\8.0\bin\psql" -h localhost -d myDBname -U myUserName -f Name-Of-File-With-Maintenance-Commands "C:\Program Files\PostgreSQL\8.0\bin\pg_dump" -f Name-Of-My-Dump-File -Fc -Z 9 -h localhost -U myUserName myDBname 5) Use the task scheduler to run your newly created batch file whenever you'd like it to run I actually run my batch file every night. My DB has no activity during the night, so I run my maintenance then. Name-Of-File-With-Maintenance-Commands contains SQL to refresh a materialized view and do a vacuum full analyze -Original Message- From: Zlatko Matic [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 28, 2005 1:07 PM To: Relyea, Mike; pgsql-general@postgresql.org Subject: Re: [GENERAL] automating backup ? I would appreciate some example. Thanks. - Original Message - From: "Relyea, Mike" <[EMAIL PROTECTED]> To: "Zlatko Matic" <[EMAIL PROTECTED]>; Sent: Tuesday, June 28, 2005 3:56 PM Subject: Re: [GENERAL] automating backup ? That's because they don't exist. You need to create them. I did it on WinXP and it works fine. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Zlatko Matic Sent: Tuesday, June 28, 2005 9:08 AM To: Magnus Hagander; Andreas; pgsql-general@postgresql.org Subject: Re: [GENERAL] automating backup ? Importance: High Hi. I can't find pgpass.conf file. It should be in Application Data subdirectory, but there is no PostgreSQL subdirectory in Application Data directory (!?). I couldn't find pgpass.conf even by searching the hard disk.. Regards, Zlatko - Original Message - From: "Magnus Hagander" <[EMAIL PROTECTED]> To: "Zlatko Matic" <[EMAIL PROTECTED]>; "Andreas" <[EMAIL PROTECTED]>; Sent: Tuesday, June 28, 2005 10:16 AM Subject: Re: [GENERAL] automating backup ? Hello. I created a Windows XP schedule for backup, following your instruction. Now I have a .bat file with this script: cd D:\Program Files\PostgreSQL\8.0\bin pg_dumpall >D:\MYDATABASE_DUMPALL -U postgres pg_dumpall >D:\MYDATABASE_SHEMA -U postgres -s pg_dumpall >D:\MYDATABASE_GLOBALS -U postgres -g Well, it works OK, but prompts for password every time. Is there any way that I pass the superuser password (off course, in safe way) so that it works automatically without prompting for password ? Use a pgpass.conf file: http://www.postgresql.org/docs/8.0/static/libpq-pgpass.html (remember the file has to be in the profile of the account that executes the step. And be sure to protect it with file system ACLs so other users can't read it) //Magnus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] automating backup ?
Hi. Yes, you were right. I added lines for template0 and template1 into pgpass.conf file and now it works. I'm wondering how to include timestamp in backup file name ? - Original Message - From: "Andreas" <[EMAIL PROTECTED]> To: "Zlatko Matic" <[EMAIL PROTECTED]> Cc: Sent: Wednesday, June 29, 2005 1:56 AM Subject: Re: [GENERAL] automating backup ? Zlatko Matic schrieb: Now I have pgpass.conf file in D:\Documents and Settings\Zlatko\Application Data\postgresql content of pgpass.conf is: localhost:*:MONITORINGZ:postgres:tralalala content of backup_script.bat is: cd D:\Program Files\PostgreSQL\8.0\bin pg_dumpall >D:\MONITORINGZ_DUMPALL -U postgres still prompts for password...What is wrong ? Perhaps its like this. pg_dumpall wants to dump the whole database-cluster (every database in your server) that is not only "MONITORINGZ" but the two templates, too. So pg_dumpall doesn't ask you for the password to your own database but 2 times for the pw for the 2 template DBs. In pgpass.conf write * instead of MONITORINGZ or copy the line for template0 and template1. Or don't use pg_dumpall and use pg_dump instead just for MONITORINGZ. Maybe it's somerthing else ... one never knows with those computers ... ;) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] automating backup ?
Mike, you have: pg_dump -f Name-Of-My-Dump-File -Fc -Z 9 -h localhost -U myUserName myDBname How do you exactly restore it ? By pg_restore or psql ? What parameters ? - Original Message - From: "Relyea, Mike" <[EMAIL PROTECTED]> To: "Zlatko Matic" <[EMAIL PROTECTED]>; Sent: Tuesday, June 28, 2005 7:55 PM Subject: RE: [GENERAL] automating backup ? 1) Create the directory %APPDATA%\postgresql in my case it's C:\Documents and Settings\Administrator\Application Data\postgresql 2) Create the file %APPDATA%\postgresql\pgpass.conf I created it with Notepad 3) Put the necessary information into %APPDATA%\postgresql\pgpass.conf I put one line in mine - localhost:*:myDBname:myUserName:myPassword 4) Create the batch file to run your backup command In my case, it reads: "C:\Program Files\PostgreSQL\8.0\bin\psql" -h localhost -d myDBname -U myUserName -f Name-Of-File-With-Maintenance-Commands "C:\Program Files\PostgreSQL\8.0\bin\pg_dump" -f Name-Of-My-Dump-File -Fc -Z 9 -h localhost -U myUserName myDBname 5) Use the task scheduler to run your newly created batch file whenever you'd like it to run I actually run my batch file every night. My DB has no activity during the night, so I run my maintenance then. Name-Of-File-With-Maintenance-Commands contains SQL to refresh a materialized view and do a vacuum full analyze -Original Message- From: Zlatko Matic [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 28, 2005 1:07 PM To: Relyea, Mike; pgsql-general@postgresql.org Subject: Re: [GENERAL] automating backup ? I would appreciate some example. Thanks. - Original Message - From: "Relyea, Mike" <[EMAIL PROTECTED]> To: "Zlatko Matic" <[EMAIL PROTECTED]>; Sent: Tuesday, June 28, 2005 3:56 PM Subject: Re: [GENERAL] automating backup ? That's because they don't exist. You need to create them. I did it on WinXP and it works fine. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Zlatko Matic Sent: Tuesday, June 28, 2005 9:08 AM To: Magnus Hagander; Andreas; pgsql-general@postgresql.org Subject: Re: [GENERAL] automating backup ? Importance: High Hi. I can't find pgpass.conf file. It should be in Application Data subdirectory, but there is no PostgreSQL subdirectory in Application Data directory (!?). I couldn't find pgpass.conf even by searching the hard disk.. Regards, Zlatko - Original Message - From: "Magnus Hagander" <[EMAIL PROTECTED]> To: "Zlatko Matic" <[EMAIL PROTECTED]>; "Andreas" <[EMAIL PROTECTED]>; Sent: Tuesday, June 28, 2005 10:16 AM Subject: Re: [GENERAL] automating backup ? Hello. I created a Windows XP schedule for backup, following your instruction. Now I have a .bat file with this script: cd D:\Program Files\PostgreSQL\8.0\bin pg_dumpall >D:\MYDATABASE_DUMPALL -U postgres pg_dumpall >D:\MYDATABASE_SHEMA -U postgres -s pg_dumpall >D:\MYDATABASE_GLOBALS -U postgres -g Well, it works OK, but prompts for password every time. Is there any way that I pass the superuser password (off course, in safe way) so that it works automatically without prompting for password ? Use a pgpass.conf file: http://www.postgresql.org/docs/8.0/static/libpq-pgpass.html (remember the file has to be in the profile of the account that executes the step. And be sure to protect it with file system ACLs so other users can't read it) //Magnus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] how to use pg_dump and then restored onto development server
Concerning this question about pg_dump, I'm also confused and don't understand when to use pg_restore and when to use psql ? For example, how to restore in these two cases: A) pg_dump -f D:\MYDB_BCP -c -x -h localhost -U postgres MYDB B) pg_dump -f D:\MYDB_BCP -Fc -c -x -h localhost -U postgres MYDB Thanks. - Original Message - From: ketan shah To: pgsql-general@postgresql.org Sent: Wednesday, June 29, 2005 4:48 PM Subject: [GENERAL] how to use pg_dump and then restored onto development server Hi, All , I am newbie to postgres database. How to take pg_dump from production database and restore into development server using pg_restore.. From documentation, I find that I have to use pg_dump for backup and pg_restore for restoration. My database name is otsdb.. For backup I use command Pg_dump otsdb > /home/Ketan/otsdbbkp.out But after successfully dump I cant see the otsdbbkp.out file in /home/Ketan/ If I get otsdbbkp.out file then I copy this file on my development server and then I issue the following command Psql d otsdb f otsdbbkp.out (my development server database name is otsdb) pl. help me . If any thing wrong pl. guide me.. Ketan shah __Do You Yahoo!?Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: [GENERAL] how to use pg_dump and then restored onto development server
OK..i get it. It works... My additional question is: how to incorporate timestamp in dumped file name ? Let's say, if we have script: pg_dump -f D:\MYDB_BCP -Fc -c -x -h localhost -U postgres MYDB, so that output file is named something like MYDB_BCP_2005-29-01, for example. Is that possible? Thanks. - Original Message - From: "Douglas McNaught" <[EMAIL PROTECTED]> To: "Zlatko Matic" <[EMAIL PROTECTED]> Cc: "ketan shah" <[EMAIL PROTECTED]>; Sent: Wednesday, June 29, 2005 5:29 PM Subject: Re: [GENERAL] how to use pg_dump and then restored onto development server "Zlatko Matic" <[EMAIL PROTECTED]> writes: Concerning this question about pg_dump, I'm also confused and don't understand when to use pg_restore and when to use psql ? For example, how to restore in these two cases: A) pg_dump -f D:\MYDB_BCP -c -x -h localhost -U postgres MYDB B) pg_dump -f D:\MYDB_BCP -Fc -c -x -h localhost -U postgres MYDB If you use any of the binary dump formats (-Fc or -Ft), use pg_restore. For text dumps (the default) use psql. -Doug ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(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] how to use pg_dump and then restored onto development server
thanks.
- Original Message -
From: "Matt Van Mater" <[EMAIL PROTECTED]>
To: "Zlatko Matic" <[EMAIL PROTECTED]>
Cc:
Sent: Thursday, June 30, 2005 7:25 PM
Subject: Re: [GENERAL] how to use pg_dump and then restored onto development
server
On 6/29/05, Douglas McNaught <[EMAIL PROTECTED]> wrote:
"Zlatko Matic" <[EMAIL PROTECTED]> writes:
> OK..i get it. It works...
> My additional question is: how to incorporate timestamp in dumped file
> name ?
> Let's say, if we have script: pg_dump -f D:\MYDB_BCP -Fc -c -x -h
> localhost -U postgres MYDB,
> so that output file is named something like MYDB_BCP_2005-29-01, for
> example. Is that possible?
I'm sure it's possible, but I'm not an expert on Windows batch command
language. There are some good references for that stuff on the
web--check them out.
To get the date in a format that should work for you in win2000 and
winxp, open a command prompt and type the following command:
FOR /F "tokens=2-4 delims=/ " %f IN ('date /t') DO (echo %h-%g-%f)
To get the same date format inside a .bat batch script, replace each %
sign with two % signs, like this:
FOR /F "tokens=2-4 delims=/ " %%f IN ('date /t') DO (echo %%h-%%g-%%f)
You can run a similar command to get the current timestamp as well.
For more information on how to do this, and to better understand the
commands above, open a command prompt and type "for /?"... you will
see a help screen on the FOR construct in the windows command shell.
enjoy:)
Matt
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Pb with boolean between MS-Access and PostgreSQl 8.0.3
Hello Ets! I think that you should use Format, so that Access can understands bools. I have done it in this way: In Access Query Builder use alias for field, for example AliasName: Format([FieldName]) In Criteria use "True" or "False" instead of -1 Tell me if it works for you. In my case it worked fine... Greetings, Zlatko - Original Message - From: Ets ROLLAND To: pgsql-general@postgresql.org Sent: Tuesday, July 12, 2005 4:16 PM Subject: [GENERAL] Pb with boolean between MS-Access and PostgreSQl 8.0.3 Hello ! I use psql ODBC v.8.00.0101 with MS-Access 2002 under XP Pro. If I use a System Data Source configured with : Datasource using : - Bool AS Char, - True is -1. So Query with criteria "true" work but checked fields don't work !? If Datasource use : - Bool NOT char, - True is -1. So Query with criteria True don't work, but checked fields work !? How can I obtain an good support for logical between MS-Access and PostgreSQL 8.0.3 ? Thanks for any explaination... Best regards
[GENERAL] temporary tables ?
Hello. I have some tables that are updated by several users in the same time and are used in queries for reports. Those tables have rows that are actualy copied from original tables that are not to be altered. There is a procedure that inserts rows for every user when connects, along with his username, so different users can't interfere with each other because every user has his own copy of rows that he can update, and records are filtered by current_user. Well, it's my heritage from MS Access, before I moved to Postgres, because there is no such thing as temporary table in Access... Now, I'm wondering is there any true advantage to implement temporary tables for each user, insted of one table with inserted rows with username for every user ? What would be advantage of temporary tables? Would database grow less if I implement temporary tables (less need for vacuum ?), in comparison to my current solution with true table with rows for every user ? Zlatko ---(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] temporary tables ?
What is the influence on database growing in comparrison to permanent table frequently inserted/deleted rows ? - Original Message - From: "Sean Davis" <[EMAIL PROTECTED]> To: "Zlatko Matic" <[EMAIL PROTECTED]> Cc: "pgsql list" Sent: Friday, July 22, 2005 8:06 PM Subject: Re: [GENERAL] temporary tables ? On Jul 22, 2005, at 1:55 PM, Zlatko Matic wrote: Hello. I have some tables that are updated by several users in the same time and are used in queries for reports. Those tables have rows that are actualy copied from original tables that are not to be altered. There is a procedure that inserts rows for every user when connects, along with his username, so different users can't interfere with each other because every user has his own copy of rows that he can update, and records are filtered by current_user. Well, it's my heritage from MS Access, before I moved to Postgres, because there is no such thing as temporary table in Access... Now, I'm wondering is there any true advantage to implement temporary tables for each user, insted of one table with inserted rows with username for every user ? Temporary tables are not per-user, but per-connection. A user can be connected twice, but a temporary table created on one connection is not visible from the other connection. Also, temporary tables are temporary--they disappear after the connection is closed. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] temporary tables ?
Thanks for explaination. Zlatko - Original Message - From: "Sean Davis" <[EMAIL PROTECTED]> To: "Zlatko Matic" <[EMAIL PROTECTED]> Cc: "pgsql list" Sent: Sunday, July 24, 2005 1:27 AM Subject: Re: [GENERAL] temporary tables ? What is the influence on database growing in comparrison to permanent table frequently inserted/deleted rows ? The tables are dropped automatically after the connection is closed. The database doesn't grow because of temporary tables. As for comparison to a frequently inserted/deleted table, that would depend on the time between vacuums. The rows aren't "removed" from a table until a vacuum is performed. On Jul 22, 2005, at 1:55 PM, Zlatko Matic wrote: Hello. I have some tables that are updated by several users in the same time and are used in queries for reports. Those tables have rows that are actualy copied from original tables that are not to be altered. There is a procedure that inserts rows for every user when connects, along with his username, so different users can't interfere with each other because every user has his own copy of rows that he can update, and records are filtered by current_user. Well, it's my heritage from MS Access, before I moved to Postgres, because there is no such thing as temporary table in Access... Now, I'm wondering is there any true advantage to implement temporary tables for each user, insted of one table with inserted rows with username for every user ? Temporary tables are not per-user, but per-connection. A user can be connected twice, but a temporary table created on one connection is not visible from the other connection. Also, temporary tables are temporary--they disappear after the connection is closed. ---(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 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Bad locking with MS-Access
Hello, Andreas! You mentioned: "Use serial or serial4 to create auto-values. Don't use any bigint-types like bigserial. Access doesn't like 8-byte-ints.". Could you please explain why you don't recommend bigserial for primary key ? I use bigserial primary keys in Postgres tables, and din't realise problems with MS Access front-end. What problems could I expect ? Thanks, Zlatko - Original Message - From: "Andreas" <[EMAIL PROTECTED]> To: "Ets ROLLAND" <[EMAIL PROTECTED]> Cc: Sent: Sunday, July 24, 2005 11:01 PM Subject: Re: [GENERAL] Bad locking with MS-Access Ets ROLLAND schrieb: For all the data created BEFORE the transfert to PostgreSQL, all works fine. For the records created SINCE this transfert, it is impossible to modify or delete these records !? MS-Access say that "The record is acceded by an other user", even I am the only user. As Richard wrote in his mail, do set row versioning in the ODBC setup. Have a primary key in every table and a timestamp. Be careful not to use to big data types in PG that aren't supportet by Access. Use timestamp(0) to get timestamps compatible to Access' DateTime values. Use serial or serial4 to create auto-values. Don't use any bigint-types like bigserial. Access doesn't like 8-byte-ints. Keep in mind that Access' autovalues are signed, so they'll roll over at about 2 billion. ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(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] remote connection, web hosting, IP adress
ok, thanks - Original Message - From: "A. Kretschmer" <[EMAIL PROTECTED]> To: Sent: Monday, September 05, 2005 6:15 PM Subject: Re: [GENERAL] remote connection, web hosting, IP adress am 05.09.2005, um 15:49:23 +0200 mailte Zlatko Mati? folgendes: How can I connect to remote server from a remote personal computer without its own IP adress ? You can connect to the remote server via ssh. Then you are a local user. SSH is a highly secure protocol, i suggest, use ssh with PublicKey-AUTH. If you connect to the server, than you can use 'psql -h localhost'. Regards, 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 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] user privilages for executing pg_autovacuum?
If I put password in pgpass file it's still a plain text. How to hide it ? - Original Message - From: "Jim C. Nasby" <[EMAIL PROTECTED]> To: "Tom Lane" <[EMAIL PROTECTED]> Cc: "Zlatko Mati?" <[EMAIL PROTECTED]>; "Matthew T. O'Connor" ; Sent: Wednesday, October 12, 2005 1:14 AM Subject: Re: [GENERAL] user privilages for executing pg_autovacuum? On Tue, Oct 11, 2005 at 02:39:24PM -0400, Tom Lane wrote: =?iso-8859-2?Q?Zlatko_Mati=E6?= <[EMAIL PROTECTED]> writes: > That's the reason why I ask. If a user that executes pg_autovacuum must > be > owner of tables or a superuser, that it is a security problem to pass > password as plain text... > How peple solve this problem ? Put the password in a ~/.pgpass file belonging to the user that runs the autovacuum task. Or you can run pg_autovacuum on the server itself and allow ident authentication for unix sockets (assuming you're on unix/linux). -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] user privilages for executing pg_autovacuum?
No, I'm on Windows... - Original Message - From: "Jim C. Nasby" <[EMAIL PROTECTED]> To: "Tom Lane" <[EMAIL PROTECTED]> Cc: "Zlatko Mati?" <[EMAIL PROTECTED]>; "Matthew T. O'Connor" ; Sent: Wednesday, October 12, 2005 1:14 AM Subject: Re: [GENERAL] user privilages for executing pg_autovacuum? On Tue, Oct 11, 2005 at 02:39:24PM -0400, Tom Lane wrote: =?iso-8859-2?Q?Zlatko_Mati=E6?= <[EMAIL PROTECTED]> writes: > That's the reason why I ask. If a user that executes pg_autovacuum must > be > owner of tables or a superuser, that it is a security problem to pass > password as plain text... > How peple solve this problem ? Put the password in a ~/.pgpass file belonging to the user that runs the autovacuum task. Or you can run pg_autovacuum on the server itself and allow ident authentication for unix sockets (assuming you're on unix/linux). -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(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] user privilages for executing pg_autovacuum?
No, I didn't try ident authentication... It seems to me that security issues should be passd to client company's system administrator ? - Original Message - From: "Jim C. Nasby" <[EMAIL PROTECTED]> To: "Zlatko Matic" <[EMAIL PROTECTED]> Cc: "Tom Lane" <[EMAIL PROTECTED]>; "Matthew T. O'Connor" ; Sent: Thursday, October 13, 2005 9:35 PM Subject: Re: [GENERAL] user privilages for executing pg_autovacuum? AFAIK you can't, and there's not really much point anyway. Anyone with taccess to that file will be able to connect to the database. Have you looked at using ident authentication on localhost? On Wed, Oct 12, 2005 at 10:12:31AM +0200, Zlatko Matic wrote: If I put password in pgpass file it's still a plain text. How to hide it ? - Original Message - From: "Jim C. Nasby" <[EMAIL PROTECTED]> To: "Tom Lane" <[EMAIL PROTECTED]> Cc: "Zlatko Mati?" <[EMAIL PROTECTED]>; "Matthew T. O'Connor" ; Sent: Wednesday, October 12, 2005 1:14 AM Subject: Re: [GENERAL] user privilages for executing pg_autovacuum? >On Tue, Oct 11, 2005 at 02:39:24PM -0400, Tom Lane wrote: >>=?iso-8859-2?Q?Zlatko_Mati=E6?= <[EMAIL PROTECTED]> writes: >>> That's the reason why I ask. If a user that executes pg_autovacuum >>> must >>> be >>> owner of tables or a superuser, that it is a security problem to pass >>> password as plain text... >>> How peple solve this problem ? >> >>Put the password in a ~/.pgpass file belonging to the user that runs >>the >>autovacuum task. > >Or you can run pg_autovacuum on the server itself and allow ident >authentication for unix sockets (assuming you're on unix/linux). >-- >Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] >Pervasive Software http://pervasive.comwork: 512-231-6117 >vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 > >---(end of broadcast)--- >TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(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 2: Don't 'kill -9' the postmaster
Re: [GENERAL] [ANNOUNCE] PostgreSQL 8.1 Beta 4
what is the schedule for releasing first official 8.1 ? - Original Message - From: "Magnus Hagander" <[EMAIL PROTECTED]> To: "Marc G. Fournier" <[EMAIL PROTECTED]> Cc: Sent: Monday, October 24, 2005 8:04 PM Subject: Re: [GENERAL] [ANNOUNCE] PostgreSQL 8.1 Beta 4 >> In order to address some issues found with the Windows port, as well >> as GCC4, we have released a Beta 4 of the upcoming >> 8.1 Release. > > Are you specifically referring to the interrupt/signals test on > windows here? Because that one isn't in beta4, it was disabled again > before it was packaged... > > Or are you referring to something else? I was referring to: http://archives.postgresql.org/pgsql-committers/2005-10/msg00321.php But hadn't seen the followup on the 22nd concerning temporarily disabling it :( Sorry about that ... Ok. No problem, just wanted to make sure you knew it wasn't in there :-) //Magnus ---(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 6: explain analyze is your friend
Re: [GENERAL] [ANNOUNCE] PostgreSQL 8.1 Beta 4
thanks for information - Original Message - From: "Matthew T. O'Connor" To: "Zlatko Matic" <[EMAIL PROTECTED]> Cc: "Magnus Hagander" <[EMAIL PROTECTED]>; "Marc G. Fournier" <[EMAIL PROTECTED]>; Sent: Tuesday, October 25, 2005 2:40 PM Subject: Re: [GENERAL] [ANNOUNCE] PostgreSQL 8.1 Beta 4 Zlatko Matic wrote: what is the schedule for releasing first official 8.1 ? Ahh the eternal question. I believe the official answers, and always will be: When it's ready. However seeing as they think they are just about ready for Release Candidate stage, I would say, not too much longer. Anywhere from 2 - 6 weeks would be my best guess. Matt ---(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 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] Unicode Corruption and upgrading to 8.0.4. to 8.1
Have you tried to restore just schema first, then data? Greetings, Zlatko - Original Message - From: "Howard Cole" <[EMAIL PROTECTED]> To: "'PgSql General'" Sent: Friday, December 02, 2005 3:02 PM Subject: [GENERAL] Unicode Corruption and upgrading to 8.0.4. to 8.1 Hi everyone, I have a problem with corrupt UTF-8 sequences in my 8.0.4 dump which is preventing me from upgrading to 8.1 - which spots the errors and refuses to import the data. Is there some SQL command that I can use to fix or cauterise the sequences in the 8.0.4 database before dumping to 8.1? I think the problem arose using invalid client encodings - which were not rejected prior to 8.1. Regards, Howard Cole www.selestial.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] unsubscribe
unsubscribe
