Re: [GENERAL] Postresql RFD version 2.0 Help Wanted.
On Sat, 06 Nov 2004 22:27:08 -0600, Dave Balderstone > You're way too impatient. Things don't happen here in time scales that > are measured in hours or days. Hang in there. You've got a good start > and some good people supporting what you want to do. > > Relax, take your time (and the advise of the wise ones here (not me... > um well, whatever)) and work through the process. > > Your proposal is, in its genesis, sound. Now, evolution. I agree. The idea of the Usenet group comp.databases.postgresql is sound, and I think it really should be created, so go on with the RFD process. I think group should NOT be mail-gated to pgsql maillists. IMHO "Big 8" groups and maillists serve different purposes. I think having "local" mail-news gateways is good (like ones already existing), but IMHO there is vast difference between "local" gateways and full-blown "Big 8" network. And finally, I think "comp.databases.postgresql.*" names are poor choice for "local" gateway. They clash with "Big 8" servers and most properly configured newsservers will not pass such groups. And in future, when Big8 c.d.postgresql.* matures, some clashes are inevitable. I personally think postgresql.* names for "local" gate is THE right way (and it would make it OK to pass the feed without said clash). Regards, Dawid PS: And I think one should ask if it is OK to propose someone else as Big8 group gateway admin/moderator/etc before doing so. It was... uncourteous... ---(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] Visual DATA MODEL Designer in linux?
Although it is not Free I think that you should give EMS Postgres Manager a try (www.ems-hitech.com), it Does pretty much everything that MS-SQL Enterprise manger does, including a good Diagramming tool. Embarcardo Technologies have some very good, (but expensive) Db design tools , mostly for windows, but with very wide Database support http://www.embarcadero.com/ AquaFold Aqua Data studio is a good Design tool, how good it is at generating PL/SQL statements is something I have not tried http://www.aquadatastudio.com/downloads.html And The Kompany have a pretty decent, though relatively new product called Data Architect, that appeared promising. http://www.thekompany.com/home/ Cheers Johan Wehtje Eric wrote: Heu... I search for a graphical DATA MODEL designer in linux to developp my database project (to draw) it on screen instead of paper and pencil... Thanks for answers about Database GUI developper... I will surely need it later but for now, I want to "draw" boxes etc... on screen for my tables and interactions. I don't know about UML (somebody told me about it). I'd like DBDesigner4 at this moment but I will have to test with ODBC because it seems that postgresql isn't native support like mysql with this tool... Any other suggestions welcome. I hope this message will be more accurate then the one before :) Thanks every body for your advices. Eric. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html . ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] how to use COPY within plperl
Hello,
I need to use COPY (instead of INSERT) within plperl function. I know
that COPY will work if data will be taken from file - however I need to
use STDIN. I tried this:
spi_exec_query("COPY sometable (field1, field2) FROM
stdin;"."\n"."sometext"."\t"."sometext"."\n"."\.")
But it didn't work. Thanks in advance.
---(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] Can this be indexed?
PostgreSQL doesn't provide pre-configured support for materialized views as such, but using some PL/pgSQL and triggers, one can easily implement any kind of materialized view as seen fit for the specific intended purpose (Snapshot, Eager, Lazy, Very Lazy). You may find an excellent tutorial on materialized views with PostgreSQL here: http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html > -Ursprüngliche Nachricht- > Von: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Im Auftrag von > Net Virtual Mailing Lists > Gesendet: Samstag, 6. November 2004 16:49 > An: Matteo Beccati > Betreff: Re: [GENERAL] Can this be indexed? > > I am not clear how to use a trigger for this, I will need to > look into that > > It is my understanding that Postgres does not have > materialized views though (which I believe would solve this > problem nicely) - am I mistaken?... > > > - Greg > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] SQL question
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Thanks for your answers Greg & Vincent. Although I solved the problem by a change of schema - I'm happy that I have something to digest I didn't know before. One never learns enough ... U.C. On Saturday 06 November 2004 03:13 pm, Uwe C. Schroeder wrote: > Here's a question for the SQL guru's out there, which I've been trying to > solve for the last couple of hours. There's got to be a solution to this, > but somehow I can't find it. > > Tables: > > table1 ( > uid int PK, > uname varchar(64) > ) > > table2 ( > uid int FK to table1, > xuid int FK to table 1 > ) > > table3 ( > uid int FK to table1, > yuid int FK to table1 > ) > > There might be more tables of the type like table2 and table3, but I'd > already be happy to solve the puzzle with the 3 tables above. > Ok, assume table1 is the master table - in my case a table used for login > authentication (some columns removed above) > table2 and table3 are tables where the uid always references to the uid in > table1. The second "uid" (xuid and yuid in this example) references to > another uid record in table1. The problem is that there may or may not be > entries in table2 (or table3) referencing a specific uid in their second > uid field. > Maybe some data: > > table1: > 1 test1 > 2 test2 > 3 test3 > > table2: > 1 2 > 1 3 > 3 1 > > table3: > 1 2 > 2 3 > 3 2 > > What I want to do in a view is the following resultset: > > uid uname xuid yuid > 1test1 2 2 > 1test1 3 > 2test2 3 > 3test3 1 > 3test3 2 > > > So basically I want to know which uid is connected to which uid, one > relationship per row. So xuid and yuid shall be identical if records exist > in both table2 and table3 or the value shall be NULL if a corresponding > record can't be found in either table2 or table3. > > Can anyone here help me out? > > Thanks a lot > > UC - -- Open Source Solutions 4U, LLC 2570 Fleetwood Drive Phone: +1 650 872 2425 San Bruno, CA 94066 Cell: +1 650 302 2405 United States Fax:+1 650 872 2417 -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) iD8DBQFBjz6bjqGXBvRToM4RApNRAJ9tJzn/3DHSYEZPlGSjzU0H/FsQIwCffw4N XJuHiF0al0pzInvOb3BP1Jg= =490X -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] subselect, order by and left join
(re-post) Dear list, Please let me know if this is not the list to ask this kind of question. I am trying to optimize a query that joins two relatively large (75 rows in each) tables. If I do it using a subselect, I can "force" the planner to choose the fastest path. Now, my question is: If I have a subselect with an ORDER BY, and I LEFT JOIN the result with the other table, is the order maintained? Or is PostgreSQL free to return the rows in any order, after the join? My query is the following: SELECT a.* FROM (SELECT * FROM tree WHERE parent_id=1363405 ORDER BY order_index DESC) AS a LEFT JOIN content AS b ON a.object_id=b.id WHERE (b.onair = 't') LIMIT 1; Thanks, Morten -- Morten K. Poulsen <[EMAIL PROTECTED]> http://www.afdelingp.dk/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Proper nesting of hierarchical objects
Hi all. I'm working (well, rather, reworking) a database schema that, in part, models a company organizational structure. For example: group company division head office department department branch department department branch department department division company division ... I would like to model each node of this hierarchy as a generic "org", as they will all share a lot of characteristics, such as each will have an address, phone numbers, email addresses (most departments have one email address rather than an email address for each person... but that's not my problem :). I'd prefer to model this with nested sets rather than an adjacency list for easy summaries, but either way, I'd like to make sure they nest properly, so I don't end up with companies as children of departments, for example. What I've done so far is assign an org_type (e.g., group, company, division) to each org. My first thought was to assign each org_type a number, and set the numbers such that parents had numbers higher than children (or vice versa), and enforce that with triggers. One drawback was that I might want to use department as a catchall for anything relatively small, so a department could be a parent of another department. Enforcing this could be implemented by requiring the parent org_type number to be greater than or equal to the child org_type number, but that would also allow, for example, companies to nest in companies, which is undesirable. My second thought was to set up a table that mapped allowable parent-child relations, and again, enforce immediate parent-child relationship validity using triggers. This is beginning to feel a bit hackish to me, so I thought I'd ask if anyone had some advice, words of encouragement, or pointers to where I might find information on modeling this. Comments, suggestions, ideas, hints, criticism appreciated! Regards, Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Visual DATA MODEL Designer in linux?
You could try DbDesigner(4) at http://dbdesigner.sourceforge.net/ -Message d'origine- De : Johan Wehtje [mailto:[EMAIL PROTECTED] Envoyé : lundi 8 novembre 2004 09:51 À : Postgres General Objet : Re: [GENERAL] Visual DATA MODEL Designer in linux? Although it is not Free I think that you should give EMS Postgres Manager a try (www.ems-hitech.com), it Does pretty much everything that MS-SQL Enterprise manger does, including a good Diagramming tool. Embarcardo Technologies have some very good, (but expensive) Db design tools , mostly for windows, but with very wide Database support http://www.embarcadero.com/ AquaFold Aqua Data studio is a good Design tool, how good it is at generating PL/SQL statements is something I have not tried http://www.aquadatastudio.com/downloads.html And The Kompany have a pretty decent, though relatively new product called Data Architect, that appeared promising. http://www.thekompany.com/home/ Cheers Johan Wehtje Eric wrote: >Heu... > >I search for a graphical DATA MODEL designer in linux to developp my >database project (to draw) it on screen instead of paper and pencil... > >Thanks for answers about Database GUI developper... I will surely need >it later but for now, I want to "draw" boxes etc... on screen for my >tables and interactions. > >I don't know about UML (somebody told me about it). > >I'd like DBDesigner4 at this moment but I will have to test with ODBC >because it seems that postgresql isn't native support like mysql with >this tool... > >Any other suggestions welcome. > >I hope this message will be more accurate then the one before :) > >Thanks every body for your advices. > >Eric. > > > >---(end of broadcast)--- >TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html > > >. > > > ---(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] subselect, order by and left join
On Mon, 8 Nov 2004, Morten K. Poulsen wrote: > Please let me know if this is not the list to ask this kind of question. > > I am trying to optimize a query that joins two relatively large (75 rows > in > each) tables. If I do it using a subselect, I can "force" the planner to > choose > the fastest path. Now, my question is: > > If I have a subselect with an ORDER BY, and I LEFT JOIN the result with the > other table, is the order maintained? Or is PostgreSQL free to return the rows > in any order, after the join? AFAIK, you have no guarantees as to the output order unless you have another order by. The join may destroy the ordering, so even if you get the ordering you want right now, you shouldn't rely on it. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] subselect, order by and left join
On Mon, Nov 08, 2004 at 04:54:40AM -0800, Stephan Szabo wrote: > > If I have a subselect with an ORDER BY, and I LEFT JOIN the result with the > > other table, is the order maintained? Or is PostgreSQL free to return the > > rows in any order, after the join? > > AFAIK, you have no guarantees as to the output order unless you have another > order by. The join may destroy the ordering, so even if you get the ordering > you want right now, you shouldn't rely on it. OK. Thanks for the reply. Morten -- Morten K. Poulsen <[EMAIL PROTECTED]> http://www.afdelingp.dk/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] Installing pgbench
Hi Everybody, I am having problems while installing pgbench program. I followed the below procedure to install according to the docs: (1) Configure and build the standard Postgres distribution. You can get away with just running configure at the top level and doing "make all" in src/interfaces/libpq. (2) Run make in this directory. You will see an executable file "pgbench". You can run it here, or install it with the standard Postgres programs by doing "make install". After running ./configure at the postgres source tree as root I went to src/interfaces/libpq and did 'make all' but i got the following error --> make: Nothing to be done for `all' am I missing something simple here ? or is the way i am doins i s wrong ? can anyone please help me with this problem ? BTW: I am running this on postgres 8.0.4 beta and red hat linux 9 Thanks! Pallav ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] RFD: comp.databases.postgresql.general
At 10:20 PM 11/7/2004, you wrote: Andy wrote: > Someone posted this official proposal to create > comp.databases.postgresql.general again. He wrote his own charter. As > far as I know, he did not consult any of the postgresql groups first. > There may be an upcoming vote on this, so please stay informed and read > news.newgroups.announce for updates. > > Also see message <[EMAIL PROTECTED]> for an example of the > proponent's temperament. > I can see how this would ruffle some serious feathers. But if I can risk getting a smack, I'd like to say that I had a bit of trouble figuring out how to get on to this group. The "respectable" news server I use does not carry it, but it shows up on Google. What's that about? Google tries to carry everything so it can archive it. The more group it carries, the more it can charge its advertisers. DataBasix carries it even though it wasn't an officially created group because some of our users requested it and they read it. Then I've noticed some notes here and there that you are supposed to send some emails to a list-server if you post, to avoid messing up the mailing list? Is that right? Why would I be worried about a listserv? Because it's gated. It flows both ways (although in a broken fashion. Of course, posting through tle list serve, I see IT's broken too since the setup has the reply going to the sending party instead of back to the list. Finally figured out this is some kind of hybrid newsgroup/mail-list. Is that right? Not hybrid. Just a bit different. Real question is, why I am trying to figure this out? Why isn't it on the news server with all of the other technical groups? To make a long story short, the request might not have been made in the most diplomatic way, but it would, if adopted, solve some real anomalies that confuse newcomers to this group and its relatives. Exactly. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] Using Postgres with Latin1 (ISO8859-1) and Unicode (utf-8) character sets.
I am posting this to the General and JDBC groups so that it's in the archive -- I know many people have had problems with Latin1 characters, and I'd like to share what has worked for us. If anyone can add this information to a more permanent FAQ, I'd be much obliged. --- Using Postgres with Latin1 (ISO8859-1) and Unicode (utf-8) character sets. This is a general primer for using postgres with alternate character sets. For those who have done so successfully, the process is obvious in hindsight. For those who haven't gotten it to work yet, it can be very complex, especially with web applications where four separate encodings can come into play. Postgres is quite intelligent in dealing with character sets, but even so, it's possible to get into a situation where your application returns garbage for Latin1 or other extended characters (n with a tilde, etc.). This is particularly true in Java-based web applications. There are four steps to ensuring proper character retrieval, with the fourth applying to Java web applications. They are explained briefly here, and then in more detail further down in the document: 1. Use a database encoding that will store the appropriate characters. 2. Set the client encoding first, before importing or adding data to the database. 3. Set the client encoding for each session where a user or program accesses the database. 4. Set the proper character encoding for the web page. Do these four steps, and things should work out nicely. Miss even one of them, and your application will mysteriously return goofy extended characters. For anyone who has already done this, the solution is probably obvious. However, it took us months to figure this out through trial-and-error, even with the help of the Java and Postgres communities. After all, we had five different potential encodings to deal with, in four separate steps, and that yields a LOT of non-working combinations. We found dozens of people with the same problem, but most of them never fully resolved their problems. We tried a heck of a lot of different strategies, but the only thing that worked was breaking the situation down into these steps, and finding the right encoding combination for each step, one-at-a-time. Now that our application works, we want to help minimize the number of people who have to reinvent this wheel from scratch. The rest of this document explains the steps in more detail. --- 1. Create the database with the proper encoding. In our experience, the best thing to do is simply choose Unicode as the database's character encoding if you think there's a chance of storing Latin1 or other characters. You could choose Latin1, and this should work in most cases. However, there are times when normal-looking characters refuse to be stored in a Latin1 database, such as character 239, which is the same in Latin1 as it is is Utf-8. Rather than attempt to beat our heads against this wall, we went with Unicode because it will hold whatever we need to hold. --- 2. Set client encoding before importing or adding data to the database. If you do a mass import from another database, or a data retore from Postgres, make sure the encoding is set first. If you try to restore a unicode characters to a Latin1 database, the data likely won't look right. Likewise, if you try to restore Latin1 characters into a Unicode database without first telling Postgres that it's getting Latin1 characters, the extended characters will come back as garbage, *even*though* a unicode database can hold them. The command to set the client encoding for a session is: "SET CLIENT_ENCODING TO Unicode", "SET CLIENT_ENCODING TO Latin1", and so forth. If you find out what kind of characters are coming into the postgres database, and tell Postgres ahead of time, it should be able to automatically translate them. As it is designed to do, of course. Just remember that unicode is a HUGE character set, and there will be unicode characters that can't be translated into the much smaller Latin1 character set. You may have to experiment to find out what the right client encoding is. It will likely depend upon the database from which you're getting data, such as a backup of a Latin1 postgres database, or data retrieved from a SQL Server database, which will have its own encoding. It could also depend upon the character set used by your operating system, such as the character set of data someone entered into a Microsoft Access database (which, if they used the US English Windows, will be cp1252). Fortunately a Unicode or Latin1 client encoding will handle most Windows-related encoding issues. --- 3. Set the client encoding for each session where a user or program accesses the database. If you have users entering data from a Java application, the encoding will vary from JVM to JVM. A windows-based JVM might have an encoding of cp1252, while a
Re: [GENERAL] RFD: comp.databases.postgresql.general
On Mon, 8 Nov 2004, Gary L. Burnore wrote: DataBasix carries it even though it wasn't an officially created group because some of our users requested it and they read it. To improve speed, do you want to setup an inter-connect between our news server and yours? Then I've noticed some notes here and there that you are supposed to send some emails to a list-server if you post, to avoid messing up the mailing list? Is that right? Why would I be worried about a listserv? Because it's gated. It flows both ways (although in a broken fashion. Of course, posting through tle list serve, I see IT's broken too since the setup has the reply going to the sending party instead of back to the list. Actually, we tried setting the Reply-To to the list, and I don't think that very many ppl liked that, so we removed it ... personally, I liked the reply-to, but that's just a personal thing *shrug* Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(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] [JDBC] Using Postgres with Latin1 (ISO8859-1) and Unicode (utf-8)
On Mon, 8 Nov 2004, J. Michael Crawford wrote:
>
>Even in Java, where you can do all sorts of character-encoding
> translation, it can be impossible to translate data retrieved from Postgres
> if it's in the wrong encoding. We've tried changing the JVM encoding,
> altering the jdbc driver, translating encodings on the database read, and
> translating encodings after the read while building a new string, to no
> avail. We tried 25 combinations of each strategy (five different possible
> read encodings and five different possible string encodings), and nothing
> worked. We could get an application working in one JVM with one encoding,
> but another JVM would break, and no amount of translation would help.
>
>But when we finally told Postgres what to return, everythign worked like
> a charm.
>
>Just as with step two, the key is to use the "SET CLIENT_ENCODING TO
> (encoding)" sql command. If you're using an application where you can send
> SQL to the server, this is all you need. In something like MS Access,
> you'll have to move to a passthrough query. For Java, you'll need to send
> a command through JDBC:
>
> String DBEncoding = "Unicode" //use a real encoding, either returned from
> the jvm or explicitly stated
> PreparedStatement statement = dbCon.prepareStatement("SET CLIENT_ENCODING
> TO '" + DBEncoding + "'");
> statement.execute();
>
This is bad advice for a Java client and does not work. The JDBC driver
always expects data in unicode and issues a SET client_encoding of it's
own at connection startup to make sure it gets unicode data. Changing
this to another encoding will break the driver and in the cvs version a
check has been added to error out if it detects you doing this.
Kris Jurka
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Mass Import/Generate PKs
Yes you can use the copy command. Check for copy TABLE NAME from 'c:\\bcpdata\\Files\\FILENAME.txt' with delimiter as '\t' NULL as ''; When creating a table, use an incremental column (data type is serial). Hope the above helps. - Goutam > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of > Hunter Hillegas > Sent: Saturday, November 06, 2004 3:01 PM > To: PostgreSQL > Subject: [GENERAL] Mass Import/Generate PKs > > > I have a CSV file with 400,000 lines of email mailing list > information that I need to migrate to a new PostgreSQL database. > > Each line has all the info I need except a PK (I usually use > an int4 column for a PK). > > If the file were smaller I would probably just use Excel to > pop in a PK and then just load into the table... > > Since Excel chokes on files greater than 65k lines, this won't work. > > Is there a way to get this done inside psql for instance? Or > another route? > > Thanks, > Hunter > > > > ---(end of > broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to > [EMAIL PROTECTED]) > Confidentiality Notice The information contained in this e-mail is confidential and intended for use only by the person(s) or organization listed in the address. If you have received this communication in error, please contact the sender at O'Neil & Associates, Inc., immediately. Any copying, dissemination, or distribution of this communication, other than by the intended recipient, is strictly prohibited. ---(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] how to use COPY within plperl
Why can you not use simple insert statements (sql insert).
Copy is meant to transfer large amount of data from text files to
databases and vice versa.
- Goutam
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Marek Lewczuk
> Sent: Monday, November 08, 2004 3:59 AM
> To: Lista dyskusyjna pgsql-general; Lista dyskusyjna pgsql-sql
> Subject: [GENERAL] how to use COPY within plperl
>
>
> Hello,
> I need to use COPY (instead of INSERT) within plperl
> function. I know that COPY will work if data will be taken
> from file - however I need to use STDIN. I tried this:
> spi_exec_query("COPY sometable (field1, field2) FROM
> stdin;"."\n"."sometext"."\t"."sometext"."\n"."\.")
>
> But it didn't work. Thanks in advance.
>
>
>
> ---(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
>
Confidentiality Notice
The information contained in this e-mail is confidential and intended for use
only by the person(s) or organization listed in the address. If you have
received this communication in error, please contact the sender at O'Neil &
Associates, Inc., immediately. Any copying, dissemination, or distribution of
this communication, other than by the intended recipient, is strictly
prohibited.
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] how to use COPY within plperl
Goutam Paruchuri wrote: Why can you not use simple insert statements (sql insert). Copy is meant to transfer large amount of data from text files to databases and vice versa. "Insert" is much slower if there are many (hundreds, thousands) data to be inserted - and in my case there will be thousands of inserts made by plperl. I can make a file with the data (and load using COPY), but it would be better to use copy from stdin. Thanks ML ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] index not always used when selecting on a date field
Russell Smith <[EMAIL PROTECTED]> writes: > now() and CURRENT_DATE, are and cannot be planned as constants. > So the planner cannot use an index for them. It's not that it cannot use an index, but that it doesn't know it should use an index. The planner knows that it can't count on now() to be constant so it doesn't use the value it has. As far as it's concerned you're comparing against an unknown value. And in general the postgres optimizer assumes single sided inequalities with unknown constants aren't selective enough to justify an index scan. The easiest work-around is probably just putting in a bogus second inequality to make it a range. The planner generally assumes ranges are selective enough to justify index scans. -- greg ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Backend disconnect problems
Hello everyone, I have received any error message in my Postgres error log as follows: "The Postmaster has informed me that some other backend died abnormally and possibly corrupted shared memory. I have rolled back the current transaction and am going to terminate your database system connection and exit. Please reconnect to the database system and repeat your query." This error message repeats itself at 11:13, 11:16 and 11:57 on the same day AM. Then it dissappeared. I can find no evidence of it over the weekend and went back 20 days in the old logs and no occurrences of it there either. I had a user who uses pgamind on occassion for quering the backend who told me that he did not intterupt any of the criteria he was running. Do I just chalk it up to a "transient anomaly" ? Thanks for any help you can give. Bob Powell Database Administrator ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Loading data Binary data and text with newlines ..
Hello, 2 questions ! Question 1 Iam trying to load binary data from sql server to postges. Do i have to write a script .. ?? Question 2 How i do load text data with newlines into postgres database .. (as newline is the default row delimiter and cannot be changed in the column table). Thanks ! Goutam "Two things are infinite: the universe and human stupidity; and I'm not sure about the the universe." Sir Albert Einstein Goutam Paruchuri Database Consultant, O'NEIL & ASSOCIATES, INC. http://www.oneil.com495 Byers Rd.Miamisburg, Ohio 45342-3662Phone: (937) 865-0846 ext. 3051Fax: (937) 865-5858 Confidentiality Notice The information contained in this e-mail is confidential and intended for use only by the person(s) or organization listed in the address. If you have received this communication in error, please contact the sender at O'Neil & Associates, Inc., immediately. Any copying, dissemination, or distribution of this communication, other than by the intended recipient, is strictly prohibited.
Re: [GENERAL] Can this be indexed?
Markus, Thank you for your hint! I spent the better part of last night working on this and finally was able to get it to work the way I wanted. The short version: I am continually amazed by the flexibility in Postgres, this isn't the sort of thing I'd want to go back years from now and digest what I did but this really has given me the best of both worlds: *very* quick query times (4631ms down to 2 ms!) and when the data changes the trigger which does the update is very quick, which beats my previous plan which involved rerunning the query again and then caching the result for subsequent queries Really this is great stuff! I simply cannot thank you (and all the other folks on this list who have helped me) enough! - Greg >PostgreSQL doesn't provide pre-configured support for materialized views >as such, but using some PL/pgSQL and triggers, one can easily implement >any kind of materialized view as seen fit for the specific intended >purpose (Snapshot, Eager, Lazy, Very Lazy). > >You may find an excellent tutorial on materialized views with PostgreSQL >here: http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html > >> -Ursprüngliche Nachricht- >> Von: [EMAIL PROTECTED] >> [mailto:[EMAIL PROTECTED] Im Auftrag von >> Net Virtual Mailing Lists >> Gesendet: Samstag, 6. November 2004 16:49 >> An: Matteo Beccati >> Betreff: Re: [GENERAL] Can this be indexed? >> >> I am not clear how to use a trigger for this, I will need to >> look into that >> >> It is my understanding that Postgres does not have >> materialized views though (which I believe would solve this >> problem nicely) - am I mistaken?... >> >> >> - Greg >> ---(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] RFD: comp.databases.postgresql.general
At 11:48 AM 11/8/2004, you wrote: On Mon, 8 Nov 2004, Gary L. Burnore wrote: At 11:01 PM 11/7/2004, you wrote: On Sun, 7 Nov 2004, Gary L. Burnore wrote: User makes a comment in USENet. Post gets seen on usenet servers around the world. Moderator chooses not to approve. Unless ist Spam, moderator always approves ... I know, cause its me ... Always? Then why moderate? Because 99% of what I have to check through is spam, which does get refused, as I state above ... Someone asks a question in USENet. Two other people reply in USENet but both with an incorret answer. You approve the original message hours or maybe even a day later. (You're not up 24x7) people on the list post answers. A while later they see the incorrect answeres and reply to them.l Meanwhile original poster has already acted on incorrect information. This could just as easily happen the other way around too ... someone posts, moderator lets it through, seperate ppl on the lists themselves post incorrect answer but someone that I haven't approved yet posts the right one ... It's not the same. Your list may not see the post for days. USENet is faster than that. You approve an email. It goes to a server not currently carrying the groups and gets marked as received but not valid group. Email never makes it to a server after the site refusing it. Some sites have it some sites don't. Each with different message ID's most likely. then the site after it wouldn't have received any of teh original messages either . It receives those posted to USENet just as it would any other group. That's why those gated don't make it to databasix.com for days after they're actually posted. So each appears twice. Once as the original USENet post and once as a post forwarded by you. If all the groups were set to moderated and sent on to you instead of being immediately posted, that part of the probem would go away. .. and message-ids don't change ... ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] RFD: comp.databases.postgresql.general
On Mon, 8 Nov 2004, Gary L. Burnore wrote: It receives those posted to USENet just as it would any other group. That's why those gated don't make it to databasix.com for days after they're actually posted. So each appears twice. Once as the original USENet post and once as a post forwarded by you. 'k, then I do have something mis-configured, as I thought I had originally configured it to honor MSG-IDs in both directions as well ... will look into this ... If all the groups were set to moderated and sent on to you instead of being immediately posted, that part of the probem would go away. Note that I have no problem with it being set as moderated, but the 'moderator address' would be the list itself (ie. [EMAIL PROTECTED]) ... of course, that still doesn't fix the case where someone on the lists themselves will go through fast, while someone not subscribe will get held up ... so, from my perspective, there are no changes *shrug* Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] [JDBC] Using Postgres with Latin1 (ISO8859-1)
<>
Well then, perhaps we shouldn't share the procedure with other folks. I
apologize if I'm introducing some misinformation.
However, this has been the only way to get our system to work on more
than one JVM. People from this group provided many suggestions, people
from other groups did the same, and nothing helped. Taking bytes and
translating encodings (examples follow my signature below) had no
effect. Changing the url of the postgres connection to include an encoding
also had no effect. Setting the encoding for the entire JVM didn't work
either. Either the data worked in a Linux VM, or a Windows VM, but not both.
So, if you're going to correct us for the wrong solution (which I'm glad
you have done), do you have any suggestions as to what the right solution
might be?
- Mike
Encoding translations that didn't work:
a) Getting encoded bytes from the result set. We tried the following block
five times, once for each different encoding we were trying to test with
the database:
dataRead = new String(result.getBytes(longName),"utf-8");
dataLatin_a = new String(dataRead.getBytes("ISO-8859-1"));
dataLatin_b = new String(dataRead.getBytes("Latin1"));
dataUnicode_a = new String(dataRead.getBytes("utf-8"));
dataUnicode_b = new String(dataRead.getBytes("UTF8"));
dataWin = new String(dataRead.getBytes("Cp1252"));
b) Getting a string, turning it bytes, and then translating. Same process
as above, but we use result.getString...
No matter what, strings showed up as gibberish in one JVM or another,
depending upon the native encoding of the database. A Latin1 database
worked in the windows JVM, a Unicode in the Linux JVM, but not the other
way around.
At 12:15 PM 11/8/2004, Kris Jurka wrote:
>
>
>On Mon, 8 Nov 2004, J. Michael Crawford wrote:
>>
>>Even in Java, where you can do all sorts of character-encoding
>> translation, it can be impossible to translate data retrieved from
Postgres
>> if it's in the wrong encoding. We've tried changing the JVM encoding,
>> altering the jdbc driver, translating encodings on the database read, and
>> translating encodings after the read while building a new string, to no
>> avail. We tried 25 combinations of each strategy (five different possible
>> read encodings and five different possible string encodings), and nothing
>> worked. We could get an application working in one JVM with one encoding,
>> but another JVM would break, and no amount of translation would help.
>>
>>But when we finally told Postgres what to return, everythign worked
like
>> a charm.
>>
>>Just as with step two, the key is to use the "SET CLIENT_ENCODING TO
>> (encoding)" sql command. If you're using an application where you can
send
>> SQL to the server, this is all you need. In something like MS Access,
>> you'll have to move to a passthrough query. For Java, you'll need to send
>> a command through JDBC:
>>
>> String DBEncoding = "Unicode" //use a real encoding, either returned from
>> the jvm or explicitly stated
>> PreparedStatement statement = dbCon.prepareStatement("SET CLIENT_ENCODING
>> TO '" + DBEncoding + "'");
>> statement.execute();
>>
>
>This is bad advice for a Java client and does not work. The JDBC driver
>always expects data in unicode and issues a SET client_encoding of it's
>own at connection startup to make sure it gets unicode data. Changing
>this to another encoding will break the driver and in the cvs version a
>check has been added to error out if it detects you doing this.
>
>Kris Jurka
>
>---(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/faqs/FAQ.html
Re: [GENERAL] Postresql RFD version 2.0 Help Wanted.
Brian {Hamilton Kelly} wrote:
> On Saturday, in article <[EMAIL PROTECTED]>
> [EMAIL PROTECTED] "Mike Cox" wrote:
>
>> Woodchuck Bill wrote:
>>
>> > Mike Cox <[EMAIL PROTECTED]> wrote in
>> > news:[EMAIL PROTECTED]:
>> >
>> >> I cannot handle the volume of email that a mailing list would place
>> >> on my
>> >> inbox.
>> >
>> > Ever heard of a digest version?
>> >
>>
>> I don't care. Its too much of a hassle to dig through without being able
>> to google groups search it.
>
> Mike makes here a VERY valid point about the mailing list vs newsgroups
> controversy: often there is no means to search past articles from the
> mailing list unless one maintains one's own complete archive thereof. In
> contrast, posts of articles to *public* newsgroups (which includes the
> Big-8, alt.*, and thousands of national and other hierarchies, such as
> demon.*) are generally[1] archived by GoogleGroups, and thereby readily
> searchable.
>
There is also the point of having to post. If I post and I subscribe to the
digest version or if I post with the option of no emails (since my inbox
cannot handle the load), how would I respond to a thread I created? Would
I have to create a new thread for each response nameing the Subject with
the previous one, and prefixing it with "RE:"?
The usenet experience is more seemless and efficient IMHO. That is why I
rarely subscribe to mailing lists. The KLM (kernel mailing list) destroyed
my inbox after a few hours and I bet the postgresql mailing list would do
the same if I didn't delete my inbox within a few days.
A news reader is meant for the high amount of posts that many groups get.
An email inbox is not meant to have hundreds of emails weekly (or daily in
the case of KLM). Email is personal, so one knows that each messege is
addressed to you and could be very important. In usenet, one can choose to
follow threads created by themselves or with browse, knowing that if they
miss an article it won't be something that can have a personal consequence
like email.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] [JDBC] Using Postgres with Latin1 (ISO8859-1)
J. Michael Crawford wrote: Encoding translations that didn't work: a) Getting encoded bytes from the result set. We tried the following block five times, once for each different encoding we were trying to test with the database: b) Getting a string, turning it bytes, and then translating. Same process as above, but we use result.getString... No matter what, strings showed up as gibberish in one JVM or another, depending upon the native encoding of the database. A Latin1 database worked in the windows JVM, a Unicode in the Linux JVM, but not the other way around. The "right way" is to just use getString() and not do any translation yourself. The driver has already done the transcoding from whatever the DB encoding is, to the internal UTF-16 string representation. You don't need to mess with byte-based representations. When you then display that string, you will need to use an appropriate encoding, obviously.. Can you provide a self-contained testcase that demonstrates getString() doing the wrong thing? -O ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] RFD: comp.databases.postgresql.general
On 2004-11-08, Woodchuck Bill <[EMAIL PROTECTED]> wrote:
> [EMAIL PROTECTED] ("Marc G. Fournier") wrote in
> news:[EMAIL PROTECTED]:
>
>> Unless its spam, it goes through ... I don't (nor have I ever) refused a
>> post based on content other then spam ... even if its anti-PostgreSQL
>> *shrug*
>
> The problem with the system is that the spam *all* gets posted to Usenet,
> but not the mailing lists. The mailing lists may be moderated, but the
> newsgroups are not. That needs to be changed.
>
> Marc, please stop removing news.groups from your replies.
He's posting to the mailing list; he probably can't avoid dropping the
crosspost.
--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] index not always used when selecting on a date field
In article <[EMAIL PROTECTED]>,
Greg Stark <[EMAIL PROTECTED]> wrote:
>
>Russell Smith <[EMAIL PROTECTED]> writes:
>
>> now() and CURRENT_DATE, are and cannot be planned as constants.
>> So the planner cannot use an index for them.
>
>It's not that it cannot use an index, but that it doesn't know it should use
>an index. The planner knows that it can't count on now() to be constant so it
>doesn't use the value it has. As far as it's concerned you're comparing
>against an unknown value. And in general the postgres optimizer assumes single
>sided inequalities with unknown constants aren't selective enough to justify
>an index scan.
>
>The easiest work-around is probably just putting in a bogus second inequality
>to make it a range. The planner generally assumes ranges are selective enough
>to justify index scans.
Well, strangely enough, after checking once more, that works
with 7.3, but with 7.4 it doesn't.
techdb2=> explain select * from lines where (removed > CURRENT_DATE AND removed
< '-01-01');
QUERY PLAN
Seq Scan on lines (cost=0.00..259.89 rows=2189 width=178)
Filter: ((removed > ('now'::text)::date) AND (removed < '-01-01'::date))
(2 rows)
With 7.3, this query used the index, while with 7.4 it doesn't.
Using an immutable function that returns CURRENT_DATE indeed
makes it work as I expected:
techdb2=> explain select * from lines where removed > today();
QUERY PLAN
-
Index Scan using lines_removed_idx on lines (cost=0.00..4.85 rows=1 width=178)
Index Cond: (removed > '2004-11-08'::date)
(2 rows)
Thanks for the advice,
Mike.
---(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] RFD: comp.databases.postgresql.general
On Mon, 8 Nov 2004, Andrew - Supernews wrote:
On 2004-11-08, Woodchuck Bill <[EMAIL PROTECTED]> wrote:
[EMAIL PROTECTED] ("Marc G. Fournier") wrote in
news:[EMAIL PROTECTED]:
Unless its spam, it goes through ... I don't (nor have I ever) refused a
post based on content other then spam ... even if its anti-PostgreSQL
*shrug*
The problem with the system is that the spam *all* gets posted to Usenet,
but not the mailing lists. The mailing lists may be moderated, but the
newsgroups are not. That needs to be changed.
Marc, please stop removing news.groups from your replies.
He's posting to the mailing list; he probably can't avoid dropping the
crosspost.
Exactly, thanks Andrew :)
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664
---(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] index not always used when selecting on a date field
[EMAIL PROTECTED] ("Miquel van Smoorenburg" ) writes:
> techdb2=> explain select * from lines where (removed > CURRENT_DATE AND
> removed < '-01-01');
> With 7.3, this query used the index, while with 7.4 it doesn't.
Perhaps you hadn't ANALYZEd in 7.3? AFAICS 7.3 and 7.4 behave
essentially alike on this point, given comparable statistics.
One thing I did notice in looking at this is that the preferential
treatment for range constraints only applies when *both* sides of the
range are un-estimatable. So you need to write something like
WHERE (removed > CURRENT_DATE AND removed < CURRENT_DATE + 1)
to get it to work nicely. I'll see if I can improve on that for 8.0;
seems like the way you tried ought to work, too.
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] trouble with rpmbuild on WBEL3.0/x86_64
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, On Mon, 1 Nov 2004, Marcel Gsteiger wrote: I wanted to install postgreSQL 7.4 on my ia32e box (x86_64 dual xeon PL370G4) running WBEL3.0. I looked for binary RPMs but did not find any. So I tried to rpmbuild --rebuild --define 'build9 1' postgresql-7.4.6-2PGDG.src.rpm but during configure I get the error checking for python... /usr/bin/python checking Python installation directories... /usr/lib/python2.2 checking how to link an embedded Python application... no configure: error: Python Makefile not found Fehler: Bad exit status from /var/tmp/rpm-tmp.75919 (%build) python-devel-2.2.3-5 is installed. On a similar i386 system (same versions of rpms), this error does not happen; here I could build all RPMs successfully (but, of course, not for x86_64). python-2.2.3-5 should have distuils module installed. Do you have /usr/lib/python2.2/distutils ? Anyway, http://archives.postgresql.org/pgsql-hackers/2004-09/msg00447.php has a patch that fixes your problems. Could you please apply it to your postgresql source and retry building rpm? On my x86_64 system, rh-postgresql 7.3.6-7 is still installed - I thought I could rebuild newer RPMS before removing the old ones. Should I remove rhdb first? No. You can still rebuild the RPMS even if you dont't have PostgreSQL installed on your server. But you'll need to remove the old binaries befora installing the new ones. Regards, - -- Devrim GUNDUZ devrim~gunduz.orgdevrim.gunduz~linux.org.tr http://www.tdmsoft.com http://www.gunduz.org -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQFBj/1Ktl86P3SPfQ4RAlOZAJ9xGK5i+NZ/71dVHmFw1P4nn19KfwCg4I73 q4RUJOwn5HROuPVbgP3ryOU= =2doM -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] [JDBC] Using Postgres with Latin1 (ISO8859-1)
<> That was the first thing we tried. Sorry I didn't mention it. The next step was getting a string, turning it into bytes, and translating the bytes. The third step was getting bytes. Nothing worked in our Java GUI, the console, or the web page returned. Maybe it was just something weird in our system. Perhaps Postgres just wanted to send Latin1 characters out of our database, which at the time was Latin1. I don't know. Whatever it was sending we couldn't use, regardless of the translation attempted. Forcing it to Unicode (improperly) did the trick for us. Now that our database is unicode, I'm not sure we'd get the same problem. If there's a better way to make sure Postgres is sending out Unicode in our JVM session, while simultaneously sending out other encodings to other apps that access the database using different encodings, I'm all ears. <> Definitely. <> I can try. If I can find a copy of the old Latin1 database, and replicate the error, I'll let you know via email. --- Thanks for your attention on this, both this time, and back in the summer when I was asking questions before we arrived at our "fix". You and Kris really carry this discussion group, and we're all lucky to have you do it. My goal was to add something to the group so that I could do more than just receive help. I also wanted something in the archives that would help "newbie" searchers who haven't yet had to deal with the encoding process in a java servlet -- we searched for weeks without finding anything that covered all the bases. If there's a way to remove/correct/comment my posting, I'm fine with that. - Mike At 04:55 PM 11/8/2004, Oliver Jowett wrote: J. Michael Crawford wrote: Encoding translations that didn't work: a) Getting encoded bytes from the result set. We tried the following block five times, once for each different encoding we were trying to test with the database: b) Getting a string, turning it bytes, and then translating. Same process as above, but we use result.getString... No matter what, strings showed up as gibberish in one JVM or another, depending upon the native encoding of the database. A Latin1 database worked in the windows JVM, a Unicode in the Linux JVM, but not the other way around. The "right way" is to just use getString() and not do any translation yourself. The driver has already done the transcoding from whatever the DB encoding is, to the internal UTF-16 string representation. You don't need to mess with byte-based representations. When you then display that string, you will need to use an appropriate encoding, obviously.. Can you provide a self-contained testcase that demonstrates getString() doing the wrong thing? -O ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] INTERVAL in a function
I have a simple function which I use to set up a users' expiry date. If a field in a table contains an interval then this function returns a timestamp some time in the future (usually two weeks), null otherwise. I can't pass the interval from the table into a variable properly within the function. Any ideas? CREATE OR REPLACE FUNCTION getUnitTimeLength(int) RETURNS timestamp AS ' DECLARE grpID ALIAS FOR $1; intval INTERVAL; exptime TIMESTAMP; BEGIN SELECT INTO intval unitTimeLength::INTERVAL FROM customer.groups WHERE groupsID = grpID; IF intval IS NULL THEN RETURN NULL; ELSE SELECT INTO exptime current_timestamp + INTERVAL ''intval''; RETURN exptime; END IF; END; ' LANGUAGE 'plpgsql'; SELECT getUnitTimeLength(55); ERROR: invalid input syntax for type interval: "intval" CONTEXT: PL/pgSQL function "getunittimelength" line 11 at select into variables However if I change the else clause to this: ELSE SELECT INTO exptime current_timestamp; RETURN exptime; END IF; it works: 2004-11-08 16:14:40.273597 (1 row) Thanks Ron ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] server auto-restarts and ipcs
A power failure led to failed postmaster restart using 7.4.6 (see output below). The short-term fix is usually to delete the pid file and restart. I often wonder why ipcs never seems to show the shared memory block in question? Am I using the wrong command? Does the key mentioned by pgsql map to the key in the ipcs output? And if the shared segment is simply not there, would it be possible for pgsql to figure that out ala Apache, search the process table, and go ahead and restart if it didn't see a postmaster already running? I'm sure this has been asked and answered, I just couldn't find it via google... TIA. Ed Database and process is pg746dba... $ cat logs-pg746-7.4.6/server_log.Mon pg_ctl: Another postmaster may be running. Trying to start postmaster anyway. 2004-11-08 17:17:22.398 [18038] FATAL: pre-existing shared memory block (key 9746001, ID 658210829) is still in use HINT: If you're sure there are no old server processes still running, remove the shared memory block with the command "ipcrm", or just delete the file "/users/pg746dba/dbclusters/pg746/postgresql-7.4.6/data/postmaster.pid". pg_ctl: cannot start postmaster Examine the log output. $ ipcs -- Shared Memory Segments keyshmid owner perms bytes nattch status 0x 32768 ed777393216 2 dest 0x 131073 root 644110592 4 dest 0x 3538946ed777393216 2 dest 0x 3670019ed777393216 2 dest 0x 4685828ed777393216 2 dest 0x 4816901ed777393216 2 dest 0x 4915206ed777393216 2 dest 0x 4980743ed777393216 2 dest 0x 5046280ed777393216 2 dest 0x 5111817ed777393216 2 dest 0x 5537802root 644110592 3 dest 0x 6651915ed777393216 2 dest 0x 19595276 ed66614400 1 dest 0x 11272205 root 644110592 2 dest -- Semaphore Arrays keysemid owner perms nsems -- Message Queues keymsqid owner perms used-bytes messages ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Backend disconnect problems
"Bob Powell" <[EMAIL PROTECTED]> writes: > I have received any error message in my Postgres error log as follows: > "The Postmaster has informed me that some other backend died abnormally > and possibly corrupted shared memory. Is there a core dump from this event? A backtrace from the core dump would tell us something useful, but the above message doesn't. (There should at least be a postmaster log entry mentioning the signal number that killed the failed backend...) regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] INTERVAL in a function
On Mon, Nov 08, 2004 at 04:15:34PM -0800, Ron St-Pierre wrote: > SELECT INTO exptime current_timestamp + INTERVAL ''intval''; You're using the literal value 'intval' instead of its value, thus the syntax error. You can simplify the statement to this: exptime := current_timestamp + intval; But I think the entire function can be shortened to: CREATE OR REPLACE FUNCTION getUnitTimeLength(int) RETURNS TIMESTAMP AS ' SELECT CURRENT_TIMESTAMP::timestamp + unitTimeLength FROM customer.groups WHERE groupsID = $1 ' LANGUAGE sql; You don't need to check for NULL because the result of the addition will already be NULL if either operand is NULL. Casting CURRENT_TIMESTAMP is necessary to avoid a "return type mismatch" error. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Newbie Question, how to grant permissions on all tables in schema/db
Hi guys, I'm wondering what the easy way to grant user/group access on all tables in a db. I just migrated about 200 tables from my MySQL database using a cool migration script, but now all the tables are owned by me in the schema public. That is all fine and all, but I wish I could run a command like: grant all on * to group developers; where developers contain my created users. is there a way to do this easily? Thanks in advance! -Kenji ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] server auto-restarts and ipcs
"Ed L." <[EMAIL PROTECTED]> writes: > A power failure led to failed postmaster restart using 7.4.6 (see output > below). The short-term fix is usually to delete the pid file and restart. > I often wonder why ipcs never seems to show the shared memory > block in question? The shared memory block would certainly not still exist after a system reboot, so what we have here is a misleading error message. Looking at the code, the most plausible explanation appears to be that shmctl(IPC_STAT) is failing (which it ought to) and returning some errno code different from EINVAL (which is the case we are expecting to see). What platform are you on, and what does its shmctl(2) man page document as error conditions? regards, tom lane ---(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] Newbie Question, how to grant permissions on all tables in schema/db
On Mon, Nov 08, 2004 at 05:00:49PM -0800, Kenji Morishige wrote: > I'm wondering what the easy way to grant user/group access on all tables > in a db. This comes up from time to time -- search the archives for phrases like "grant" and "all tables". The responses usually suggest writing a script or function to query the system catalogs for the list of tables, views, sequences, etc. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Postresql RFD version 2.0 Help Wanted.
On Mon, 08 Nov 2004 12:55:06 -0800 Mike Cox <[EMAIL PROTECTED]> wrote > ... > There is also the point of having to post. If I post and I subscribe to the > digest version or if I post with the option of no emails (since my inbox > cannot handle the load), how would I respond to a thread I created? Would > I have to create a new thread for each response nameing the Subject with > the previous one, and prefixing it with "RE:"? > > The usenet experience is more seemless and efficient IMHO. That is why I > rarely subscribe to mailing lists. The KLM (kernel mailing list) destroyed > my inbox after a few hours and I bet the postgresql mailing list would do > the same if I didn't delete my inbox within a few days. > ... postgresql-general averages in the light-to-moderate range, between 30 to 80 posts a day. It's not that bad, although it would not feel so great if you were on dial-up. Incidentally, there are several archives, including the one at http://marc.theaimsgroup.com http://marc.theaimsgroup.com/?l=postgresql-general&r=1&w=2 which picks posts up very quickly. Might be useful until a charter can be worked out that reflects the list policies. http://www.postgresql.org/lists.html http://archives.postgresql.org/pgsql-general/ For instance, as I understand it, you will need to specified that it is moderated at the SPAM-block level. Since they seem to be concerned about whether Marc (all by himself?) would be appropriate, particularly on the usenet side, perhaps it would be good to set up a group of moderators? Ideally, they could be spread around the globe and take shifts, to get good time coverage. Oh, and thanks, Mike, for stirring up the hornets' nest. ;-P (Somebody had to take the brunt of it.) -- Joel <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Can this be indexed?
On Sun, Nov 07, 2004 at 09:29:30 +, Jerry III <[EMAIL PROTECTED]> wrote: > But if you do build an index over "id" then pgsql would only have to do a > sequential scan on that index, which might be a lot faster if your table > contains a lot of other data, won't it? A full table index scan will be slower than a sequential scan; typically by a lot. In the old days a sort step would have been needed and that would have slowed things down. Now a method using hashing is available that will work unless there is an extremely large number of unique values for "id". ---(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] server auto-restarts and ipcs
On Monday November 8 2004 6:16, Tom Lane wrote: > "Ed L." <[EMAIL PROTECTED]> writes: > > A power failure led to failed postmaster restart using 7.4.6 (see > > output below). The short-term fix is usually to delete the pid file > > and restart. > > > > I often wonder why ipcs never seems to show the shared memory > > block in question? > > The shared memory block would certainly not still exist after a system > reboot, so what we have here is a misleading error message. Looking at > the code, the most plausible explanation appears to be that > shmctl(IPC_STAT) is failing (which it ought to) and returning some errno > code different from EINVAL (which is the case we are expecting to see). > What platform are you on, and what does its shmctl(2) man page document > as error conditions? Platform is Linux 2.4.20-30.9 on i686 (Pentium 4, I think). From man 2 schctl: ERRORS On error, errno will be set to one of the following: EACCES is returned if IPC_STAT is requested and shm_perm.modes does not allow read access for shmid. EFAULT The argument cmd has value IPC_SET or IPC_STAT but the address pointed to by buf isnât accessible. EINVAL is returned if shmid is not a valid identifier, or cmd is not a valid command. EIDRM is returned if shmid points to a removed identifier. EPERM is returned if IPC_SET or IPC_RMID is attempted, and the effective user ID of the calling process is not the creator (as found in shm_perm.cuid), the owner (as found in shm_perm.uid), or the super-user. EOVERFLOW is returned if IPC_STAT is attempted, and the gid or uid value is too large to be stored in the structure pointed to by buf. CONFORMING TO SVr4, SVID. SVr4 documents additional error conditions EINVAL, ENOENT, ENOSPC, ENOMEM, EEXIST. Neither SVr4 nor SVID documents an EIDRM error condition. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] server auto-restarts and ipcs
On Monday November 8 2004 7:24, Ed L. wrote: > On Monday November 8 2004 6:16, Tom Lane wrote: > > "Ed L." <[EMAIL PROTECTED]> writes: > > > A power failure led to failed postmaster restart using 7.4.6 (see > > > output below). The short-term fix is usually to delete the pid file > > > and restart. > > > > > > I often wonder why ipcs never seems to show the shared memory > > > block in question? > > > > The shared memory block would certainly not still exist after a system > > reboot, so what we have here is a misleading error message. Looking at > > the code, the most plausible explanation appears to be that > > shmctl(IPC_STAT) is failing (which it ought to) and returning some > > errno code different from EINVAL (which is the case we are expecting to > > see). What platform are you on, and what does its shmctl(2) man page > > document as error conditions? > > Platform is Linux 2.4.20-30.9 on i686 (Pentium 4, I think). I recently saw this same thing happen from a power failure on several HPUX boxes as well (I think running B.11.00/11.23 with 7.3.4/7.3.7, but not sure). Ed ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] Per-Table Transaction Isolation Level?
Hi I'd like to know if there is a way to specify different transaction isolation levels for different tables in the db. The reason i'm asking this (rather bizarre sounding, i know ;-) ) question is the following: I'm importing about 2 million records into my application each day (the data is more or less fully replaced each day). My importer updates only a few tables (about 5 - 10), but reads a lot of other tables (10 or so) while importing. Those (read-only, meta-information) tables contains information on how to import the data, and what reports to calculate from the imported data. My import sometimes crashed, becausse the meta-information tables are changed while importing (e.h, I pass a id to a function, the function does some calculations, than tries to select the row with the given id, but fails, because the row was deleted in the meantime). I understand that the standard approach to this problem is to set the transaction isolation level to "serializeable", thus avoiding non-repeatable reads. But since the import is a lenghty operation (a few hours), I don't want to import in a searializeable transaction, since it would force me to import "in a loop" until no serialization error occurs while importing. But since it's only the meta-information tables for which I want to avoid non-repeatable reads, and since those are read-only anyway (for my importer), I wouldn't have to fear getting "serialization errors" when I access only those tables in serializeable mode (since read-only transaction never trigger serialization errors). I know I could simulate something like that using dblink, but if possible I'd prefer a simpler approach (Using dblink would meand that I need to rewrite large parts of import, since it's mostly stored procedures). greetings, Florian Pflug ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] server auto-restarts and ipcs
"Ed L." <[EMAIL PROTECTED]> writes: > A power failure led to failed postmaster restart using 7.4.6 (see > output below). The short-term fix is usually to delete the pid file > and restart. Thinking some more about this ... does anyone know the algorithm used in Linux to assign shared memory segment IDs? Your report shows about a dozen shmem segments in use; which would put the probability of an accidental collision at pretty-tiny. But if the kernel's assignment algorithm is nonrandom then it'd be plausible for the Postgres shmem ID from the previous system boot cycle to match one of the shmem IDs already handed out in the current boot cycle. In that case we'd get EACCES from shmctl() which we take to be a trouble indication. (This is probably over-conservatism, but I don't want to relax it without knowing for sure that we need to.) BTW, do you know what all those shmem segments are for? My Linux box shows only one segment in use besides the ones Postgres is using. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Per-Table Transaction Isolation Level?
On Tue, Nov 09, 2004 at 04:34:16AM +0100, Florian G. Pflug wrote: > My import sometimes crashed, becausse the meta-information tables are > changed while importing (e.h, I pass a id to a function, the function > does some calculations, than tries to select the row with the given id, > but fails, because the row was deleted in the meantime). I understand > that the standard approach to this problem is to set the transaction > isolation level to "serializeable", thus avoiding non-repeatable reads. Sounds like you could use savepoints to be able to retry without starting from scratch: - function gets the Id - savepoint foo - do something with Id - try to get row == Id - if it doesn't exist, rollback to foo, go to top - release foo - go to top -- Alvaro Herrera () "Uno combate cuando es necesario... ¡no cuando está de humor! El humor es para el ganado, o para hacer el amor, o para tocar el baliset. No para combatir." (Gurney Halleck) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] server auto-restarts and ipcs
On Monday November 8 2004 8:41, Tom Lane wrote: > > BTW, do you know what all those shmem segments are for? My Linux box > shows only one segment in use besides the ones Postgres is using. Looks like Ximian Evolution apps, X, Mozilla, Wombat, etc ... Ed ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] troubleshooting deadlocks
I'm wrestling with tracking down a deadlock. Here's a 7.4.6 deadlock message: ERROR: deadlock detected DETAIL: Process 15655 waits for ShareLock on transaction 9381; blocked by process 15600. Process 15600 waits for ShareLock on transaction 9388; blocked by process 15655. I know the original statement is printed right after this, but with complex triggers doing lots of write queries, I'm finding it difficult to identify which subsequent query in the trigger is really the one immediately preceding the deadlock. It would be helpful in debugging if the error message included info on which tables are involved, maybe even the deadlocking query itself, in the "DETAIL" output for future releases. Maybe something like: DETAIL: Process 15655 waits on transaction 9381 for ShareLock on public.this_table for statement: UPDATE public.this_table SET foo = 1; blocked by process 15600. Process 15600 waits on transaction 9388 for ShareLock on public.that_table for statement: UPDATE public.that_table SET bar = 1; blocked by process 15655. Maybe there is a simpler way to troubleshoot this that I'm overlooking? ps - Here's a query I use to show locks, maybe others might find it useful (or find bugs in it): SELECT dbu.usename as locker, l.mode as locktype, pg_stat_get_backend_pid(S.backendid) as pid, db.datname||'.'||n.nspname||'.'||r.relname as relation, l.mode, substring(pg_stat_get_backend_activity(S.backendid), 0, 30) as query FROM pg_user dbu, (SELECT pg_stat_get_backend_idset() AS backendid) AS S, pg_database db, pg_locks l, pg_class r, pg_namespace n WHERE db.oid = pg_stat_get_backend_dbid(S.backendid) AND dbu.usesysid = pg_stat_get_backend_userid(S.backendid) AND l.pid = pg_stat_get_backend_pid(S.backendid) AND l.relation = r.oid AND l.database = db.oid AND r.relnamespace = n.oid AND l.granted ORDER BY db.datname, n.nspname, r.relname, l.mode; ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
