Re: [GENERAL] Insert ImageFile in PostgreSQL
Am Dienstag, den 02.08.2005, 08:52 -0700 schrieb [EMAIL PROTECTED]: > I'm sorry, but I know only the basics about DBMs, but I think is not > possible to put a image file INSIDE a database. You can put in a > database the path of the file... > > (Maybe I'm wrong...) sorry to say that, but indeed you are ;) Postgres has 2 ways to accomplish this: one is to use large objects (lo) which is a bit painfull with the interface and w/ backups. the other is just to store the file data in a bytea field, which is limited in size (1GB) and requires you to fetch the complete file data at once. -- Tino Wildenhain <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] wanting to use postgres with python - WHAT am I
Am Mittwoch, den 03.08.2005, 09:49 -0600 schrieb Ronni: > I want to use postgres with python but I can't seem to get it working. > If I read things correctly I need python installed then I need postgres > installed. I did both on my Sun Solaris Opteron AMD 64 (Mandrake 10.1 > os) using the --enable-shared and --with-python options. Both came back > with ready statements. I can do stuff with python and I can do stuff > with postgres - but I can't seem to do stuff with both! I installed > Python-2.4.1 and PostGreSQL-8.0.3. > > I read up and decided that I needed PyGreSQL so downloaded > PyGreSQL-3.6.2 and had to work extensively (before asking for help there > again) to get just the compile to work. It seems to add the shared > library but the "python setup.py install" doesn't work AT ALL. > > My question is - is there a more simple way? I must surely be doing > something wrong. Maybe I don't even need PyGreSQL. > "doesnt work at all" is not a good problem report. Its better to include the output of the run. python setup.py will also tell you options you can give (specify path to your postgres libs/includes and so on) Did you install python from source too? Most people use psycopg with python and postgres. -- Tino Wildenhain <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Postgresql Hosting
On Fri, 5 Aug 2005, Richard Sydney-Smith wrote: > I have asked my internet host to include postgresql as part of their > service but it seems that there are issues in getting it to work with > "cpanel" which is their support service for their clients. Is their a > reason why Postgresql is harder to hostthan mysql? Is their any docs I > can point the server admin to that would help him? > > If not what service providers are people recommending? > > thanks > > Richard > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend > If you're talking about cpanel.net then postgresql is supported by default. --- Emilian Ursu http://www.emuadmin.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Weird lock or bug maybe?
Hi All, I got 2 very simple tables, one hold few values and got a unique field to be used as identifier and the other table got a reference to that table (foreign key). At my last testing before the problem I inserted to each one of the tables about 1 million rows, for the process of inserting new information I begin a transaction, delete all the previous information, insert the new information and commit the transaction. So far it never gave me any problems (though its the first time that I let a foreign key to exist) but now from some reason it "locks" a specific row from being deleted for example: If ill query: DELETE FROM table1; it will just get stuck... If ill try: DELETE FROM table1 WHERE table1_id=1523; it will work in most cases but for some rows it will just get stuck! Anyone know anything about this weird problem? By the way when I restarted the DB server I was able to delete the current row which stucked the process but then I got stuck at some other row at the table Thanks in advance, Ben-Nes Yonatan ---(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
[GENERAL] Does preparing statements other than selects help performance?
I was looking at an application recently which was written in Java and used Postgresql as it DB. In it extensive use had been made of PreparedStatements both for SELECTs and for INSERT, UPDATE and DELETE statements. Some of the routines had multiple UPDATEs doing much the same thing but with slightly different parameters. In the comments it was stated that it was better to prepare lots of statements in advance rather than build one on the spot (and then prepare it, it needed the substitution) because of the optimiser. This set me thinking (always dangerous). I can see how a SELECT can be helped by preparing the statement, but not really how an INSERT could or, other than the SELECT implicit in the WHERE clause on an UPDATE or DELETE, how UPDATE or DELETE statements would be helped. Can anyone enlighten me please? David ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Weird lock or bug maybe?
Hi there I have much problems with wierd locking too. I thing that it problem is connected with localisation - when i update a "problem" row (which have text fields) with any different text data in text fields - all locks dissapper. I hope this info can help to solve this bug (because in PG 7.2.3 i didnt saw this problems//) -- Thanks BNY> Hi All, BNY> I got 2 very simple tables, one hold few values and got a unique field BNY> to be used as identifier and the other table got a reference to that BNY> table (foreign key). BNY> At my last testing before the problem I inserted to each one of the BNY> tables about 1 million rows, for the process of inserting new BNY> information I begin a transaction, delete all the previous information, BNY> insert the new information and commit the transaction. BNY> So far it never gave me any problems (though its the first time that I BNY> let a foreign key to exist) but now from some reason it "locks" a BNY> specific row from being deleted for example: BNY> If ill query: DELETE FROM table1; it will just get stuck... BNY> If ill try: DELETE FROM table1 WHERE table1_id=1523; it will work in BNY> most cases but for some rows it will just get stuck! BNY> Anyone know anything about this weird problem? BNY> By the way when I restarted the DB server I was able to delete the BNY> current row which stucked the process but then I got stuck at some other BNY> row at the table BNY> Thanks in advance, BNY> Ben-Nes Yonatan BNY> ---(end of broadcast)--- BNY> TIP 9: In versions below 8.0, the planner will ignore your desire to BNY>choose an index scan if your joining column's datatypes do not BNY>match -- С уважением, Игорь mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Slow Inserts on 1 table?
On Tue, Aug 02, 2005 at 10:55:22AM -0700, Gregory Youngblood wrote: > Not to fan the flames, so to speak, but I do have on question. If > this is a known limitation, I'd expect the behavior to be consistent. > Instead, he is saying that the problem is intermittent. Sometimes it > runs fine, other times it slows down to a snail's pace. > > So, does that mean the known problem is indeed intermittent, and not > something that happens every time? Or, is this an issue that can be > mostly eliminated with appropriate tuning? The problem is basically that PostgreSQL chooses a seq scan if it thinks the table is empty. But a normal CREATE TABLE fudges the statistics so that it chooses an index scan. The problem mostly arises if you use an ANALYZE or VACUUM ANALYZE *after* you've deleted all the rows but *before* you load the data. Don't do that. If you need to run VACUUM to clear out the table, do so, just don't analyze at the same time. Or use TRUNCATE. At least that's my experience. Hope this helps, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. pgpP1CRSRMnHH.pgp Description: PGP signature
Re: [GENERAL] renaming a table, and its primary key constraint
On Sat, Jul 30, 2005 at 02:02:16PM -0700, Jim wrote:
> Hello,
>
> Forgive me if this is a dopey question. I'm running a web app with a
> dB that takes me a half hour to regenerate. Instead of closing down
> every day, I'd like insert the data into a temp table "stuff_tmp" and
> then rename that to the permanent table "stuff", so the application is
> not off-line for so long.
>
> The table "stuff_tmp" has a primary key constraint. When I rename the
> table, this constraint does not get renamed-- it continues to have the
> name "stuff_tmp_pkey"-- and (you guessed it) the next time I run the
> script pg complains that it can't make "stuff_tmp" because the
> constraint already exists.
>
> That I can see, I can't rename the constraint. Do I have that correct?
Well, you can. Something like:
BEGIN:
ALTER TABLE stuff_tmp_pkey RENAME TO stuff_pkey;
ALTER TABLE stuff ALTER pkey SET DEFAULT nextval('stuff_pkey');
COMMIT;
> Can I simulate (sort of) a primary key constraint by adding a UNIQUE
> index, and a NOT NULL check? That is, if I add those two, do I lose
> anything compared with the original primary key constraint?
Only that foreign key references don't automatically find the right
column by default, but you can still specify directly...
Hope this helps,
--
Martijn van Oosterhout http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.
pgp2NDnxudIoQ.pgp
Description: PGP signature
Re: [GENERAL] Instalation batch file
Thank you for answer. It seems that %HOMEDRIVE% is just enough to solve this problem. Thanks. - Original Message - From: "Glenn Davy" <[EMAIL PROTECTED]> To: "Zlatko Matić" <[EMAIL PROTECTED]> Cc: Sent: Thursday, August 04, 2005 12:15 AM Subject: Re: [GENERAL] Instalation batch file On Wed, 2005-08-03 at 23:29 +0200, Zlatko Matić wrote: Hello. I would like to install database schema on a server using files with dumped schema and globals (produced by dumpall), that are placed on CD. The installation script (batch file) that restores these two files is also placed on the same CD, (let's say E:) in the same folder. cd C:\Program Files\PostgreSQL\8.0\bin psql -f E:\MYBASE_SHEMA.dumpall template1 -U zmatic psql -f E:\MYBASE_SHEMA.dumpall template1 -U zmatic vacuumdb -d MYBASE -U zmatic But, what if CD is F: or some other unit ? How could I change this batch file (Windows XP) to be able to recognize what is the actual path of the folder that containes these three files ? Im not sure how to determine the path to the media (esp if more than one cd/dvd), but you could take a couple of different approaches: 1)that is to run the script from the cd drive and build the path to pg's bin with %HOMEDRIVE%. Type 'SET' to see what other shell variables there are 2) Run the script from anywhere and take %1 type command line paramaters to ask location of either bin and/or cd/dvd Been years since I've had the misfortune to have to use windows shell scipts, but there are prob many other approachs - best to do a little research into windows shell scripting and determine best for your scenario. Glenn Thanks in advance, Zlatko ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(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] Does preparing statements other than selects help performance?
On Fri, Aug 05, 2005 at 10:08:42AM +0100, David Goodenough wrote: > I was looking at an application recently which was written in Java and used > Postgresql as it DB. In it extensive use had been made of PreparedStatements > both for SELECTs and for INSERT, UPDATE and DELETE statements. Some of > the routines had multiple UPDATEs doing much the same thing but with > slightly different parameters. In the comments it was stated that it was > better to prepare lots of statements in advance rather than build one on the > spot (and then prepare it, it needed the substitution) because of the > optimiser. Which version of PostgreSQL was this built for? Until recently there was no support for server side prepared statements so it mattered not one wit whether you had one or a thousand prepared queries, it was all done by the client anyway. > This set me thinking (always dangerous). I can see how a SELECT can be > helped by preparing the statement, but not really how an INSERT could > or, other than the SELECT implicit in the WHERE clause on an UPDATE or > DELETE, how UPDATE or DELETE statements would be helped. For the executors point of view, there is no difference between a SELECT, INSERT, DELETE or UPDATE. Each is doing a query on the database but doing different things with the result. SELECT sends it to the client, UPDATE changes some values and writes the new tuple out, DELETE marks the rows deleted. INSERT ... VALUES () has a trivial plan but INSERT .. SELECT can be complicated. On the client side, prepared statements simplify coding, since they seperate the actual SQL text from the function it performs. So there you should use one statement for each "operation" you perform, whatever that means for your app. On the server side, prepared statements are a way of saving the plan of a query and using it multiple times. So the benefit is related to how many times you use the statement vs how complex the query is (parsing and planning time). If your INSERT statement is simple, why bother with prepared stataments, since the planning time will be almost nil anyway. If your hugely complicated DELETE is only run once, again, no benefit since you're not reusing the plan. Only in the case where you have a query which you execute a lot of times (10, 100, 1000) is it a noticable benefit. Accordingly, several Postgres frontends support prepared stataments, but only actually plan them in the server if you use them more than a predefined number of times. Actually, there is one downside with prepared queries. When processing each query individually, PostgreSQL can use the statistics for the values given to produce the optimal plan for that set. If your value are not "equally distributed" (can't think of a better phrase) then that plan might not be optimal for all the other substitutions you might do. Something to think about. In any case, I hope this has clarified things for you. It's all a tradeoff between code clarity, parsing, planning and execution time. Hope this helps, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. pgpdjXF6eEIjX.pgp Description: PGP signature
Re: [GENERAL] Does preparing statements other than selects help performance?
David Goodenough wrote: This set me thinking (always dangerous). I can see how a SELECT can be helped by preparing the statement, but not really how an INSERT could or, other than the SELECT implicit in the WHERE clause on an UPDATE or DELETE, how UPDATE or DELETE statements would be helped. Can anyone enlighten me please? Well, it does take time to parse/plan even a simple INSERT. But, unless you are repeating that query many times (e.g. 1000 inserts to bulk-load a table) I'm not sure you'll notice any gain. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Weird lock or bug maybe?
Ben-Nes Yonatan wrote: If ill query: DELETE FROM table1; it will just get stuck... If ill try: DELETE FROM table1 WHERE table1_id=1523; it will work in most cases but for some rows it will just get stuck! Anyone know anything about this weird problem? By the way when I restarted the DB server I was able to delete the current row which stucked the process but then I got stuck at some other row at the table What do you mean by "get stuck"? Are you sure it's not scanning one of the tables to check references before deleting? If you don't have an index on the table in question then PG will have to scan the entire table. To check for locks, try: SELECT * FROM pg_stat_activity; SELECT * FROM pg_locks; Let's see what's actually happening. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Weird lock or bug maybe?
go wrote: Hi there I have much problems with wierd locking too. I thing that it problem is connected with localisation - when i update a "problem" row (which have text fields) with any different text data in text fields - all locks dissapper. I hope this info can help to solve this bug (because in PG 7.2.3 i didnt saw this problems//) Doesn't sound likely. Are you seeing this problem with PostgreSQL, or perhaps with Access talking to PostgreSQL over ODBC. If you can provide a simple, repeatable example then I'm sure the developers will be interested. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Does preparing statements other than selects help performance?
On Friday 05 August 2005 11:57, Martijn van Oosterhout wrote: > On Fri, Aug 05, 2005 at 10:08:42AM +0100, David Goodenough wrote: > > I was looking at an application recently which was written in Java and > > used Postgresql as it DB. In it extensive use had been made of > > PreparedStatements both for SELECTs and for INSERT, UPDATE and DELETE > > statements. Some of the routines had multiple UPDATEs doing much the > > same thing but with slightly different parameters. In the comments it > > was stated that it was better to prepare lots of statements in advance > > rather than build one on the spot (and then prepare it, it needed the > > substitution) because of the optimiser. > > Which version of PostgreSQL was this built for? Until recently there > was no support for server side prepared statements so it mattered not > one wit whether you had one or a thousand prepared queries, it was all > done by the client anyway. I am not sure it was originally build for PostgreSQL, but it all client side anyway, or that its inside Tomcat and thus from PG's point of view client side. I presume by server side you mean triggers and functions or am I misunderstanding you? > > > This set me thinking (always dangerous). I can see how a SELECT can be > > helped by preparing the statement, but not really how an INSERT could > > or, other than the SELECT implicit in the WHERE clause on an UPDATE or > > DELETE, how UPDATE or DELETE statements would be helped. > > For the executors point of view, there is no difference between a > SELECT, INSERT, DELETE or UPDATE. Each is doing a query on the database > but doing different things with the result. SELECT sends it to the > client, UPDATE changes some values and writes the new tuple out, DELETE > marks the rows deleted. INSERT ... VALUES () has a trivial plan but > INSERT .. SELECT can be complicated. This particular application is only using INSERT ... VALUES( ) so this is in the trivial camp. I had not ever thought of DELETE and UPDATE being variants on SELECT, but it makes sense the way you explains it. > > On the client side, prepared statements simplify coding, since they > seperate the actual SQL text from the function it performs. So there > you should use one statement for each "operation" you perform, whatever > that means for your app. > > On the server side, prepared statements are a way of saving the plan of > a query and using it multiple times. So the benefit is related to how > many times you use the statement vs how complex the query is (parsing > and planning time). > > If your INSERT statement is simple, why bother with prepared stataments, > since the planning time will be almost nil anyway. If your hugely > complicated DELETE is only run once, again, no benefit since you're not > reusing the plan. > > Only in the case where you have a query which you execute a lot of > times (10, 100, 1000) is it a noticable benefit. Accordingly, several > Postgres frontends support prepared stataments, but only actually plan > them in the server if you use them more than a predefined number of > times. Understood. > > Actually, there is one downside with prepared queries. When processing > each query individually, PostgreSQL can use the statistics for the > values given to produce the optimal plan for that set. If your value > are not "equally distributed" (can't think of a better phrase) then > that plan might not be optimal for all the other substitutions you > might do. Something to think about. > > In any case, I hope this has clarified things for you. It's all a > tradeoff between code clarity, parsing, planning and execution time. > > Hope this helps, Thanks, David ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Optimizing large data loads
Hi guys, We have a Java process that uses Hibernate to load approximately 14 GB of data. One a dual-proc 2.4 GHZ Xeon with 2048 MB RAM, it's currently taking over 13 hours to load (PostgreSQL 7.4.8). We're flushing from hibernate every 50 records. I've turned fsync to false in postgresql.conf, and we've turned autocommit off in our code. Is there anything else I could try to temporarily speed up inserts? Thanks very much for your help. John ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] wanting to use postgres with python - WHAT am I doing wrong?????? AGGGHHHH
On Wed, Aug 03, 2005 at 09:49:18AM -0600, Ronni <[EMAIL PROTECTED]> wrote a message of 24 lines which said: > I want to use postgres with python This is too vague. Do you want: 1) To do server-side programming in Python (creating functions in Python instead of plPgSQL or SQL)? If so: http://www.postgresql.org/docs/8.0/interactive/plpython.html 2) Access the database from a client written in Python? If so: http://initd.org/projects/psycopg1 (There are other Python client interfaces such as PoPy, but psycopg is the most common, and best maintained version.) The second is typically much simpler. (I do both.) > I did both on my Sun Solaris Opteron AMD 64 (Mandrake 10.1 os) ^^^ ??? Solaris or Mandrake? > using the --enable-shared and --with-python options. --with-python is only for 1), server-side programming. > but I can't seem to do stuff with both! Commands typed and actual results obtained? Otherwise, noone can help you. > I read up and decided that I needed PyGreSQL No. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] DNS vs /etc/hosts
Hi, On Thursday 04 August 2005 17:13, [EMAIL PROTECTED] wrote: | I am changing from 7.2 to 8.0 and have both installed now on various Linux | machines. When I use the psql command line interface with a -h hostname, | the connection time from 7.2 is instant while the connection time from 8.0 | is 15 seconds. My assumption is that 7.2 checks the /etc/hosts file first | and if unable to find the specified host it reverts to a DNS lookup, and | the 8.0 is just the opposite. Is this a correct assumption, and if so, | can I modify 8.0 to behave as 7.2 does? I've once seen nameservice and connection delays caused by improperly configured IPV6 support on some Linux machines. Removing the responsible modules from the kernel fixed it. Just another guess though :-) Ciao, Thomas -- Dr. Thomas Pundt <[EMAIL PROTECTED]> http://rp-online.de/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Cursor Issue??
# [EMAIL PROTECTED] / 2005-08-04 09:56:03 -0700: > Thanks Roman for sticking with me on this! > For whatever reason I cannot load another langage, I think it has to do > with recompiling the program and installing all the options. Not sure > though?? LANGUAGE plpgsql doesn't exist for me. Please send the output of this command: createlang -U postgres -d $dbname plpgsql > I still find this cursor limitation wacked. I find it hard to believe > that nobody else is requiring this curosr funcionality. Why else have > a cursor? You are trying to use a cursor in interactive SQL. SQL99 doesn't allow that at all! > See the bottom from this link > http://www.postgresql.org/docs/7/interactive/sql-fetch.htm Even in > there is display of a teaser that it can be done. Too bad there is no > example. It says: : Compatibility : SQL92 : : Note: The non-embedded use of cursors is a Postgres extension. : The syntax and usage of cursors is being compared against the : embedded form of cursors defined in SQL92. : : SQL92 allows absolute positioning of the cursor for FETCH, and : allows placing the results into explicit variables. : : FETCH ABSOLUTE # : FROM cursor : INTO :variable [, ...] The text above is valid for SQL:1999 as well. You fall in the non-embedded category, IOW that insufficient functionality you are trying to use is outside the SQL standard. The example servers to document the paragraph that begins "SQL92 allows (...)", IOW what you see is the SQL92, Embedded SQL syntax, not what you can do in PostgreSQL. > My objective is to look at each record one at at time from top to > bottom. I need to take that information in variable form, and run it > through a routine that is in the cursor block, then the end result > needs to end up in another table. There will be times where I will > also need to scroll forward and backward. To me the fact that the interactive FETCH can only *display* the row, while PL/PGSQL is always NO SCROLL and you can only FETCH NEXT on it, looks like a bad combination of features and shortcomings. Maybe you'd like to take this to the pgsql-docs@ list? At least the more knowledgable people could add some real life (= nontrivial) examples. -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] How to write jobs in postgresql
On Wed, Aug 03, 2005 at 09:08:48AM +0530, chiranjeevi.i wrote: > Is it possible to write jobs in postgresql & if possible how should I > write .please help me. Please explain what you mean by "job." What are you trying to do? -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Optimizing large data loads
John Wells wrote: Hi guys, We have a Java process that uses Hibernate to load approximately 14 GB of data. One a dual-proc 2.4 GHZ Xeon with 2048 MB RAM, it's currently taking over 13 hours to load (PostgreSQL 7.4.8). We're flushing from hibernate every 50 records. I've turned fsync to false in postgresql.conf, and we've turned autocommit off in our code. Is there anything else I could try to temporarily speed up inserts? You don't say what the limitations of Hibernate are. Usually you might look to: 1. Use COPY not INSERTs 2. If not, block INSERTS into BEGIN/COMMIT transactions of say 100-1000 3. Turn fsync off 4. DROP/RESTORE constraints/triggers/indexes while you load your data 5. Increase sort_mem/work_mem in your postgresql.conf when recreating indexes etc. 6. Use multiple processes to make sure the I/O is maxed out. Any of those do-able? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] How to write jobs in postgresql
[Please copy the mailing list on replies so others can contribute to and learn from the discussion.] On Fri, Aug 05, 2005 at 06:28:04PM +0530, chiranjeevi.i wrote: > I have to dump up my database into a text file(using copy) at 2 am on every > day. And from that I have to get the database details. & please don't > suggest me to go for CRON as it has caused so many problems in my server. I'll suggest cron anyway because that's the usual way to schedule commands to run at regular times on Unix-like platforms. If you're having problems with cron then you should investigate and fix those problems. The pgAdmin folks have been working on a scheduler called pgAgent. Use a search engine to find more information about it. -- Michael Fuhr ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Problems to install pg 8.0.3
"[EMAIL PROTECTED]" <[EMAIL PROTECTED]> writes: > I want to install pg with readline. ./configure told me I have no > readline. I downloaded it and installed it, but ./configure still > outputing there is no readline. How can I solve it? You probably need readline-devel as well. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] renaming a table, and its primary key constraint
"Jim" <[EMAIL PROTECTED]> writes: > The table "stuff_tmp" has a primary key constraint. When I rename the > table, this constraint does not get renamed-- it continues to have the > name "stuff_tmp_pkey"-- and (you guessed it) the next time I run the > script pg complains that it can't make "stuff_tmp" because the > constraint already exists. If you can update to PG 8.0, this problem should pretty much go away, since it chooses nonconflicting names by default: regression=# create table stuff_tmp(f1 int primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "stuff_tmp_pkey" for table "stuff_tmp" CREATE TABLE regression=# alter table stuff_tmp rename to stuff; ALTER TABLE regression=# create table stuff_tmp(f1 int primary key); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "stuff_tmp_pkey1" for table "stuff_tmp" CREATE TABLE regression=# > That I can see, I can't rename the constraint. Do I have that correct? Yes, but you can rename the underlying index (use ALTER TABLE for this). > So I thought to drop the constraint. That I can see I can't add a > primary key constraint "stuff_pkey". Is that correct? No. Try "alter table t add constraint foo primary key(f1)" > Can I simulate (sort of) a primary key constraint by adding a UNIQUE > index, and a NOT NULL check? That is, if I add those two, do I lose > anything compared with the original primary key constraint? It doesn't create a default REFERENCES target for foreign keys; which may or may not be important to you. If it is, the whole idea won't work at all, because dropping a table and renaming another one into its place isn't going to cause foreign key references to transfer over. There are other hazards involved in the idea, too, due to the fact that cached plans won't transfer over. Which in particular means that plpgsql functions using the table are likely to fail. Depending on how big the table is, you might be better off with ... compute new data in stuff_tmp ... BEGIN; TRUNCATE TABLE stuff; -- acquires exclusive lock INSERT INTO stuff SELECT * FROM stuff_tmp; COMMIT; Because of the lock, the intermediate state with no data isn't visible to other transactions; the only effect will be a delay until they can get at the table. (Note that the equivalent effect would be hard to get in a RENAME-based solution, because in that case you don't have a single continuously-existing table you can use a lock on.) regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] How to explode an array into multiple rows
Kevin Murphy <[EMAIL PROTECTED]> writes:
> I'd like to do something like this:
> select array_explode(array_col) from table1 where col2 = 'something';
> where array_explode returns the elements of array_col unpacked onto
> separate rows.
> I tried creating a function returning a setof type, but postgresql
> produces a "set-valued function called in context that cannot accept a
> set" error. I've seen this error in the list archives, but I'm not sure
> how to translate the simple cases discussed into this situation.
This is a limitation of the SRF implementation in plpgsql. You can work
around it in a grotty way by wrapping the plpgsql function inside a sql
function:
regression=# create function explode1(anyarray) returns setof anyelement as
regression-# 'begin
regression'# for i in array_lower($1, 1) .. array_upper($1, 1) loop
regression'# return next $1[i];
regression'# end loop;
regression'# return;
regression'# end' language plpgsql strict immutable;
CREATE FUNCTION
-- this doesn't work:
regression=# select explode1('{1,2,3,4}'::int[]);
ERROR: set-valued function called in context that cannot accept a set
CONTEXT: PL/pgSQL function "explode1" line 3 at return next
-- but this does:
regression=# create function explode(anyarray) returns setof anyelement as
regression-# 'select * from explode1($1)' language sql strict immutable;
CREATE FUNCTION
regression=# select explode('{1,2,3,4}'::int[]);
explode
-
1
2
3
4
(4 rows)
I tested this in PG 8.0.3; not sure if it will work in pre-8.0 releases.
Not sure about the performance, either, but at least it works.
regards, tom lane
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] How to write jobs in postgresql
"chiranjeevi.i" <[EMAIL PROTECTED]> writes: > Is it possible to write jobs in postgresql & if possible how should I > write .please help me. What does "write jobs" mean? -Doug ---(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] Going beyond sql
On Wed, Aug 03, 2005 at 06:50:48AM +1000, Jon wrote: > I'd like to run some scripts against a postgres database but has more > than just standard SQL commands. I'd like to be able to use conditional > statements (i.e. if .. .then ... ) and such. I had a look and can see > that I could use pgplsql for this. However it seems that it must be part > of a function. Is there any way of doing this without the function > coating as these commands would be a once off? PostgreSQL's implementation of SQL doesn't have control structures (aside from simple conditionals like CASE expressions), so you'll have to write a server-side function in a language like PL/pgSQL or do the control in client-side code. -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Going beyond sql
Jon wrote: Hi, I'd like to run some scripts against a postgres database but has more than just standard SQL commands. I'd like to be able to use conditional statements (i.e. if .. .then ... ) and such. I had a look and can see that I could use pgplsql for this. However it seems that it must be part of a function. Is there any way of doing this without the function coating as these commands would be a once off? Jon. Have you considered writing them in a programming language that connects to the database? I have to write one-time database code at times, and writing it in Perl is the most efficient way to do it for me. But then I also wrote an extremely simple wrapper to the DBI / DBD::Pg module so that I don't have to muck with the extra house keeping steps in my programs. -- Thanks, Laura Vance Systems Engineer ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] What causes lock??
Frank Miles <[EMAIL PROTECTED]> writes: > ... By the way, in this forced condition, the rows that show granted='f' > have blank relname, relation, and database fields :( Those would be locks on transaction IDs, which is what you see in pg_locks when someone is blocked on a row-level lock. (For reasons of implementation efficiency, we don't record individual row locks in a way that lets pg_locks see them :-() This is definitely theorizing in advance of the evidence, but I'm betting that your problem is due to locking of rows referenced by foreign keys. Did you recently add some foreign key constraints to your database? regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Why pgsql function is working on vers 8.0/windows and not on ver 7.3.2/rad hat?
On Tue, Aug 02, 2005 at 01:52:41PM +0300, in-consulting.net wrote: > I'm using a type defined by me: output_mt, on the function > CREATE OR REPLACE FUNCTION happy() > RETURNS SETOF output_mt AS > and when I want to initialize from another function: > CREATE OR REPLACE FUNCTION msg() > RETURNS output_mt AS > as: output_mt=msg(); > it gaves me: > WARNING: plpgsql: ERROR during compile of happy near line xx > ERROR: parse error at or near "output_mt". > Why is this working on version 8.0 with windows and not on version 7.3.2 on > linux, how can I solve this problem, do I need a cast or something ? Could we see a complete example? It would be easier to say what the problem is if we could see the actual code and an unedited error message. Is output_mt a composite type? 8.0 does have better support for composite types than previous versions; it also has better syntax checking for PL/pgSQL functions. -- Michael Fuhr ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] How to write jobs in postgresql
Douglas McNaught wrote: >> Is it possible to write jobs in postgresql & if possible how should I >>write .please help me. > > > What does "write jobs" mean? > I'm assuming this person has an Oracle background, if so, jobs are Oracle's equivalent to a built-in cron scheduler. This has been discussed extensively in these lists in the past - whether or not to implement an pg version of Oracle's job mechanism, etc. -- ___ This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. ___ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] DNS vs /etc/hosts
How might I check for that? And if it is determined to be a problem, how would I remove the guilty modules? Thomas Pundt <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 08/05/2005 07:19 AM To pgsql-general@postgresql.org cc Lowell Hought/AGL/[EMAIL PROTECTED] Subject Re: [GENERAL] DNS vs /etc/hosts Hi, On Thursday 04 August 2005 17:13, [EMAIL PROTECTED] wrote: | I am changing from 7.2 to 8.0 and have both installed now on various Linux | machines. When I use the psql command line interface with a -h hostname, | the connection time from 7.2 is instant while the connection time from 8.0 | is 15 seconds. My assumption is that 7.2 checks the /etc/hosts file first | and if unable to find the specified host it reverts to a DNS lookup, and | the 8.0 is just the opposite. Is this a correct assumption, and if so, | can I modify 8.0 to behave as 7.2 does? I've once seen nameservice and connection delays caused by improperly configured IPV6 support on some Linux machines. Removing the responsible modules from the kernel fixed it. Just another guess though :-) Ciao, Thomas -- Dr. Thomas Pundt <[EMAIL PROTECTED]> http://rp-online.de/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] What causes lock??
Following up on my question regarding locking... Well... I'm less and less inclined to think that the update/insert failure is due to a lock problem. Weird! Perhaps the python/psycopg interface? I've never had a case where the python/psycopg invocation worked differently than with the psql interface. It may yet be time to build the psycopg from source, as the Debian version is pretty old... The problem has been resolved... ok, I've been dense. A "minor" change resulted in going down a slightly different pathway in a python function (that I wrote) that handles certain python-database exchanges. This alternate pathway had a bug (now expunged) that caused it to begin a transaction, but failed to rollback or commit. Doh! This family of functions has been used for years without finding the particular combination that excited this annoying behavior, but it got me this time! Added another test to the regression set... My apologies to Federico (psycopg), which was blameless, and my continued thanks to the Postgresql community (particularly Michael and Tom) for their help! -frank ---(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] DNS vs /etc/hosts
Your are correct in that 8.0 is doing a request first. I am running Red Hat version 8.0. The difference in the way 7.2 and 8.0 resolve the host option has to be because of the change from gethostbyname to getaddrinfo. Is there some way I can force my machine to do an A search before a search? Here is the output from the tcpdump you suggested for 7.2: 14:50:37.679429 10.32.104.97.32777 > 10.32.104.5.domain: [udp sum ok] 9750+ A? zmpweb5.dms.ats.agl.faa.gov. [|domain] (DF) (ttl 64, id 23879, len 73) 14:50:37.680131 10.32.104.5.domain > 10.32.104.97.32777: [udp sum ok] 9750* q: A? zmpweb5.dms.ats.agl.faa.gov. 1/2/2 zmpweb5.dms.ats.agl.faa.gov. A 10.32.104.110 ns: dms.ats.agl.faa.gov. NS agldmszmps1.dms.ats.agl.faa.gov., dms.ats.agl.faa.gov. NS agldmss3.dms.ats.agl.faa.gov. ar: agldmss3.dms.ats.agl.faa.gov. A 10.32.104.3, agldmszmps1.dms.ats.agl.faa.gov. A 10.32.104.5 (142) (ttl 128, id 33877, len 170) Here is the output from 8.0: 14:50:03.736903 10.32.104.97.32777 > 10.32.104.5.domain: [udp sum ok] 18412+ ? zmpweb5.dms.ats.agl.faa.gov. [|domain] (DF) (ttl 64, id 6499, len 73) 14:50:03.737652 10.32.104.5.domain > 10.32.104.97.32777: [udp sum ok] 18412* q: ? zmpweb5.dms.ats.agl.faa.gov. 0/1/0 ns: dms.ats.agl.faa.gov. SOA agldmszmps1.dms.ats.agl.faa.gov. root.dms.ats.agl.faa.gov. 2001145122 10800 3600 43200 7200 (98) (ttl 128, id 44115, len 126) 14:50:03.737822 10.32.104.97.32777 > 10.32.104.5.domain: [udp sum ok] 18413+ ? zmpweb5. [|domain] (DF) (ttl 64, id 6500, len 53) 14:50:08.738756 10.32.104.97.32777 > 10.32.104.5.domain: [udp sum ok] 18413+ ? zmpweb5. [|domain] (DF) (ttl 64, id 6501, len 53) 14:50:10.686497 10.32.104.5.domain > 10.32.104.97.32777: [udp sum ok] 21278 ServFail q: ? zmpweb5. 0/0/0 (25) (ttl 128, id 7764, len 53) 14:50:10.686617 10.32.104.5.domain > 10.32.104.97.32777: [udp sum ok] 21278 ServFail q: ? zmpweb5. 0/0/0 (25) (ttl 128, id 8020, len 53) 14:50:10.686622 10.32.104.5.domain > 10.32.104.97.32777: [udp sum ok] 18413 ServFail q: ? zmpweb5. 0/0/0 (25) (ttl 128, id 8276, len 53) 14:50:10.686676 10.32.104.5.domain > 10.32.104.97.32777: [udp sum ok] 18413 ServFail q: ? zmpweb5. 0/0/0 (25) (ttl 128, id 8532, len 53) 14:50:10.687162 10.32.104.97.32777 > 10.32.104.5.domain: [udp sum ok] 18414+ A? zmpweb5.dms.ats.agl.faa.gov. [|domain] (DF) (ttl 64, id 10058, len 73) 14:50:10.688109 10.32.104.5.domain > 10.32.104.97.32777: [udp sum ok] 18414* q: A? zmpweb5.dms.ats.agl.faa.gov. 1/2/2 zmpweb5.dms.ats.agl.faa.gov. A 10.32.104.110 ns: dms.ats.agl.faa.gov. NS agldmss3.dms.ats.agl.faa.gov., dms.ats.agl.faa.gov. NS agldmszmps1.dms.ats.agl.faa.gov. ar: agldmss3.dms.ats.agl.faa.gov. A 10.32.104.3, agldmszmps1.dms.ats.agl.faa.gov. A 10.32.104.5 (142) (ttl 128, id 8788, len 170) --- Michael Fuhr <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 08/04/2005 05:30 PM To Lowell Hought/AGL/[EMAIL PROTECTED] cc pgsql-general@postgresql.org Subject Re: [GENERAL] DNS vs /etc/hosts On Thu, Aug 04, 2005 at 04:01:43PM -0500, [EMAIL PROTECTED] wrote: > I also performed the trace you suggested. The results are the same until > this point, where the time for > version 8.0 totals 0.025960 and for > version 7.2 totals 0.009481 Those differences probably don't matter, but what comes next does. The 7.2 trace shows a DNS query to 10.32.104.5 for a name that begins with zmpweb5.dms.ats.agl (the strace output is truncated after that). The DNS server responds with a packet of 142 bytes, after which the process makes a TCP connection to 10.32.104.110:5432, which is presumably the database server. The 8.0 trace is different: it appears to make the same DNS query to 10.32.104.5, but the response it receives is only 98 bytes (was it in fact the same query?). The process then makes a DNS query to 10.32.104.5 for just zmpweb5, and that query times out after 5 seconds. Then the process sends a query for zmpweb5 to 172.17.46.46, which refuses the connection, possibly because no DNS server is running on that machine. We then see a query for zmpweb5 to 172.17.40.42, and that query times out after 6 seconds. Then another query for zmpweb5 to 10.32.104.5 and a 5-second timeout, a query for zmpweb5 to 172.17.46.46 and a refused connection, and a query for zmpweb5 to 172.17.40.42 and a 6-second timeout. We then see the process read /etc/hosts, but afterwards it makes another DNS query to 10.32.104.5 for zmpweb5.dms.ats.agl., and this time we see a 142-byte response, as 7.2 had received on its f
[GENERAL] How to join function with a table?
Hi everyone,
I have a function returning set of date called datelist(date,date)
example:
select * from datelist('8/1/2005, 8/5/2005');
8/1/2005
8/2/3005
8/3/2004
8/4/2005
8/5/2005
I would like to join this function with a table
create table payment(
id int4 not null,
date_start date,
date_end date
)
id | date_start | date_end
1 | 8/1/2005 | 8/2/2005
2 | 8/4/2005 | 8/6/2005
I wish I could do join that returns something like this with the function
id | datelist
--
1 | 8/1/2005
1 | 8/2/2005
2 | 8/4/2005
2 | 8/5/2005
2 | 8/6/2005
I thought simple join like this would work, but it doesn't
select * from payment P, datelist(P.date_start
, P.date_end)
because it require a relation.
help is appreciated.
Yudie
[GENERAL] slow update query
Pg 7.4.5 I'm running an update statement on about 2 million records using the following query: Update table_A set F1 = b.new_data from table_B b where b.keyfield = table_A.keyfield both keyfields are indexed, yet this job has been running over 3 hours. Is this normal? thanks Patrick Hatcher Macys.Com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] initskript after db start; locks on transactions
On Aug 3, 2005, at 4:33 PM, Thomas Chille wrote: Hi! I have have two questions: 1. What is the best approach to trigger a service script wich will clean up something in the db after every db (re-)start? Has PG its own mechanism for such things or have i to use my /etc/init.d/postgresql script? I don't think there's anything built in that will trigger a "service script". I'm pretty sure you'll want to add to or supplement the existing init script. 2. Sometime i read something about locks on transactions. Is this only an internal thing or can i set them by my own and if yes, for what? With the LOCK command i can only lock tables, or? You can use any lock mode specified: http://www.postgresql.org/docs/8.0/static/sql-lock.html -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i™ http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-469-5150 615-469-5151 (fax) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] DNS vs /etc/hosts
Hi, On Friday 05 August 2005 16:21 [EMAIL PROTECTED] wrote: | How might I check for that? If it's a standard distribution kernel, try "lsmod | grep ipv6" - this will show you if you have loaded the IPv6 module; try to remove the module by issueing "rmmod ipv6". If that fails, you probably have to edit your /etc/modprobe.conf or /etc/modprobe.d/aliases file, comment out the ipv6 module entry and reboot the machine. Then repeat your test. But again, I'm just guessing in the wild. Ciao, Thomas -- Thomas Pundt --- http://www.pundt.de/ E-Mail: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] How to join function with a table?
On 8/5/05, Yudie Pg <[EMAIL PROTECTED]> wrote:
I thought simple join like this would work, but it doesn't
select * from payment P, datelist(P.date_start
, P.date_end)
try select * from payment as p, (select * from datelist('8/1/2005, 8/5/2005')) as date
where date.. = p.
[GENERAL] Clog/Transaction problems
Hello, I have a really weird problem; queries against a very large table are failing against a very large table with a strange error. Case in point: dqfull=# vacuum freeze mytable; ERROR: could not access status of transaction 538989714 DETAIL: could not open file "/srv/db/postgresql/pg_clog/0202": No such file or directory WTF? The only activity this table has seen is a massive data import of around ~40M rows. Is there a way to fix clog info and make it think all transactions on it have committed? (Note: I tried a VACUUM FREEZE after other commands were failing) What’s a good strategy to fix this table. I’d prefer to not have to reload it since that will take over 1 day. Logan Bowers
Re: [GENERAL] How to join function with a table?
try select * from payment as p, (select * from datelist('8/1/2005, 8/5/2005')) as datewhere date.. = p.
The problem is the function's parameters '8/1/2005', '8/5/2005' has to refer to whatever value on the payment records.
Re: [GENERAL] How to join function with a table?
On Fri, 2005-08-05 at 10:53 -0500, Yudie Pg wrote:
> Hi everyone,
>
> I have a function returning set of date called datelist(date,date)
> example:
> select * from datelist('8/1/2005, 8/5/2005');
> 8/1/2005
> 8/2/3005
> 8/3/2004
> 8/4/2005
> 8/5/2005
>
> I would like to join this function with a table
> create table payment(
> id int4 not null,
> date_start date,
> date_end date
> )
> id | date_start | date_end
>
> 1 | 8/1/2005 | 8/2/2005
> 2 | 8/4/2005 | 8/6/2005
>
> I wish I could do join that returns something like this with the
> function
>
> id | datelist
> --
> 1 | 8/1/2005
> 1 | 8/2/2005
> 2 | 8/4/2005
> 2 | 8/5/2005
> 2 | 8/6/2005
>
what about something like
select id,datelist
from payment as p,
(select * from datelist('8/1/2005, 8/5/2005')) as list
where datelist between p.date_start and p.date_end;
gnari
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Clog/Transaction problems
On Fri, Aug 05, 2005 at 04:52:29PM -0400, Logan Bowers wrote: > Hello, > > > > I have a really weird problem; queries against a very large table are > failing against a very large table with a strange error. Case in point: > > > > dqfull=# vacuum freeze mytable; > > ERROR: could not access status of transaction 538989714 > > DETAIL: could not open file "/srv/db/postgresql/pg_clog/0202": No such > file or directory Looks like a flipped bit. 538989714 is 1000100101010010010010. Have you checked your RAM? > WTF? The only activity this table has seen is a massive data import of > around ~40M rows. Is there a way to fix clog info and make it think all > transactions on it have committed? You could fill the pg_clog/0202 file with 0x55, which means "all transactions committed". You'd also have to change the nextXid counter to beyond what the 0202 file records (pg_resetxlog can do that for you), in order not to have problems in the future. However, if bad RAM is the issue, you'll have other problems in the future if not fixed. . -- Alvaro Herrera () "Pensar que el espectro que vemos es ilusorio no lo despoja de espanto, sólo le suma el nuevo terror de la locura" (Perelandra, CSLewis) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] How to join function with a table?
what about something likeselect id,datelistfrom payment as p, (select * from datelist('8/1/2005, 8/5/2005')) as list
where datelist between p.date_start and p.date_end;
That's works but have to put the whole date range into the parameters before it can be joined.
This would need 2 queries where the first query only to find minumum & maximum date that possibly recorded on payment table.
[GENERAL] changing default lockfile location
I'm trying to run two database clusters on the same box. Both are bound to their own ip but use the same port. I can't see a way to change the location of the lockfile on a per cluster basis though. Is there one? Chris ---(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] changing default lockfile location
On 8/5/05, snacktime <[EMAIL PROTECTED]> wrote: > I'm trying to run two database clusters on the same box. Both are > bound to their own ip but use the same port. I can't see a way to > change the location of the lockfile on a per cluster basis though. Is > there one? > > Chris > Never mind, I figured out the socket file is also the lockfile. Chris ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Insert ImageFile in PostgreSQL
[EMAIL PROTECTED] wrote: I'm sorry, but I know only the basics about DBMs, but I think is not possible to put a image file INSIDE a database. You can put in a database the path of the file... (Maybe I'm wrong...) See ya! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq See the postgresql org docs for the bytea datatype. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] DNS vs /etc/hosts
[EMAIL PROTECTED] writes: > Your are correct in that 8.0 is doing a request first. I am running > Red Hat version 8.0. The difference in the way 7.2 and 8.0 resolve the > host option has to be because of the change from gethostbyname to > getaddrinfo. Is there some way I can force my machine to do an A search > before a search? On a recent RH system, "man 5 resolver" suggests that putting "options inet6" into /etc/resolv.conf is what makes this happen ... if there is such an entry on your system, try removing it. RH 8.0 is a good ways back though, so read the local version of that man page before doing anything with that config file. I concur with Michael's previous suggestion that the best answer is to fix the clearly-broken DNS environment you're dealing with. It is no longer acceptable for anyone to be running nameservers that have not heard of IPv6 --- unless it's for a network that only contains clients that have not heard of IPv6, which yours evidently is not. Have a word with your local network admin. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] How to join function with a table?
Yudie Pg <[EMAIL PROTECTED]> writes: > I have a function returning set of date called datelist(date,date) > ... > I would like to join this function with a table > create table payment( > id int4 not null, > date_start date, > date_end date > ) > ... > I thought simple join like this would work, but it doesn't > select * from payment P, datelist(P.date_start, P.date_end) Certainly not --- per the SQL spec, different elements of a FROM list are independent, so the datelist relation can't refer to P. (I think SQL 2003 has a construct called LATERAL that would allow such things, but we don't implement that yet.) The only way to do this at the moment in Postgres is to put the set-returning function into the SELECT target list: select id, datelist(date_start, date_end) from payment; which will work fine if datelist() is implemented as a SQL function, and not so fine if it's implemented in plpgsql. You can work around this by wrapping the plpgsql function in a SQL function (ick). I posted an example in another thread a day or so ago. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] timestamp default values
Hi all, I have a table which is used for logging, and I want a timestamp column which reliably stores the insert time for each row inside a transaction, with maximum precision. Now, if I'm reading the documentation (http://www.postgresql.org/docs/8.0/interactive/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT) correctly, the only way to get the current time inside a transaction is to use timeofday(). timeofday() returns text, and moreover it returns in a bizarre format which cannot be converted directly into any useful temporal types, at least not in 8.0.2: => select timeofday(); timeofday - Sat Aug 06 14:41:49.596859 2005 EST (1 row) => select timeofday()::timestamp; ERROR: invalid input syntax for type timestamp: "Sat Aug 06 14:41:57.875478 2005 EST" => select timeofday()::date; ERROR: invalid input syntax for type date: "Sat Aug 06 14:43:41.672518 2005 EST" So, if I'm on the right track here, the only way to really get the value I want is to do something like: DEFAULT to_timestamp(timeofday(), 'Dy Mon DD HH24:MI:SS.US ') Does this strike anybody else as circumlocutive? From the aforementioned manual page: It is important to know that CURRENT_TIMESTAMP and related functions return the start time of the current transaction; their values do not change during the transaction. This is considered a feature: the intent is to allow a single transaction to have a consistent notion of the "current" time, so that multiple modifications within the same transaction bear the same time stamp. timeofday() returns the wall-clock time and does advance during transactions. I agree that being able to reference the time the transaction started is a useful feature, but it should not be made available at the expense of being able to reference the actual time. Terms like "now" and "current timestamp" seem unambiguous to me -- they are misleading names for the transaction start time. At least, there should be a function that really does return the current timestamp. -- BJ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] timestamp default values
Brendan Jurd <[EMAIL PROTECTED]> writes: > timeofday() returns text, and moreover it returns in a bizarre format > which cannot be converted directly into any useful temporal types, at > least not in 8.0.2: Hm? Works fine for me. What datestyle setting do you have exactly? regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
