Re: [GENERAL] Poor Plan selected w/ not provided a date/time but selecting date/time from a table
Ow Mun Heng wrote: On Wed, 2007-10-17 at 20:37 +0100, Richard Huxton wrote: Your query plans don't seem to match your queries. That makes it difficult to provide meaningful advice. Well, then that makes both you and me(both) stumped. because the 2 queries are exactly the same except for the data part. Your queries were like this: select foo, bar, foobar from A, join B on a.id = b.id join C on c.id = b.id and c.start_dtime = b.start_dtime where audit_key_dtime = '2007-08-08 18:00:00' and audit_key_dtime '2007-08-08 18:01:00' Your plans contained these: Filter: (pber_err_rate_hd_zn_2 0::numeric) Filter: (web_id 0::numeric) I don't see the 0 condition anywhere in your SQL - presumably there's a view involved somehwere? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL]
roopal oswal wrote: Hi all, Initially i installed postgresql 8.2.5 for my Dspace software..everything was fine.. but all of sudden yeterday while connecting an error popped out sayin server could'nt listen i have uninstalled it and reinstalled many a times with different ports and diffferent accounts.. but its still not working.. how do i go ahead.. You'll need to provide an actual error message before anyone can give you any help - server could'nt listen isn't something that PostgreSQL will display. What's the error message, and what's in the logs? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Poor Plan selected w/ not provided a date/time but selecting date/time from a table
On Thu, 2007-10-18 at 07:28 +0100, Richard Huxton wrote: Ow Mun Heng wrote: On Wed, 2007-10-17 at 20:37 +0100, Richard Huxton wrote: Your query plans don't seem to match your queries. That makes it difficult to provide meaningful advice. Well, then that makes both you and me(both) stumped. because the 2 queries are exactly the same except for the data part. Your queries were like this: select foo, bar, foobar from A, join B on a.id = b.id join C on c.id = b.id and c.start_dtime = b.start_dtime where audit_key_dtime = '2007-08-08 18:00:00' and audit_key_dtime '2007-08-08 18:01:00' and web_id 0 Your plans contained these: Filter: (pber_err_rate_hd_zn_2 0::numeric) Filter: (web_id 0::numeric) I don't see the 0 condition anywhere in your SQL - presumably there's a view involved somehwere? My apologies, in an effort to disguise the column_names, I missed out the 1st one. There is no View it's just a filtering condition. Having said that, the only difference between the 2 queries are just the dates. (or the way the dates are provided to the query..) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] dblink and hostname resolution problem
[EMAIL PROTECTED] a écrit :
Hi list!
I am using DBLink on a Win32 system running PostgreSQL 8.2 to query a table on
another similar system (Win32 / PostgreSQL 8.2).
If I query the server with dblink('hostaddr=ip_address ...', ...), it works
fine, but if I try to use the hostname instead
(dblink('hostaddr=myserver.mydomain.com ...', ...)), I have an error :
ERROR: could not establish connection
DETAIL: could not translate host name myserver.mydomain.com to address:
Unknown server error
OK I've got it : I had to use host=... instead of hostaddr=
Seems obvious now that I know it.
Regards
--
Arnaud
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] Suggestions for Remote Procedure Calls from PG, please?
Em Thursday 18 October 2007 01:44:33 Bret Schuhmacher escreveu: Thanks for the reply, Tom. I was thinking I could have my remote process send a message back to PG via XMLBlaster, too. XMLBlaster is a MOM-like message-queuing app that guarantees delivery to subscribers. (www.xmlblaster.org). The problem, as you stated, though, is transactional integrity :-(. Hmmm, I'll see about the to-do queue idea. You can try implementing a queue, Bret. Make it a FIFO queue and poll from it regularly. Ten make your transaction insert a record on that queue and take your action based on that. A table as simple as: id SERIAL, -- you can have a routine to reset this when empty table TEXT, -- includes schema primary_key TEXT -- to allow for numeric and text PKs would allow you to retrieve the row that has been changed and take your action based on that. -- Jorge Godoy [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] move databases files location
Richard Huxton wrote: jehan.procaccia wrote: my postgresql installation from redhat package: $ rpm -q postgresql postgresql-7.4.17-1.RHEL4.1 is located in /var partition, unfortunalty it is nearly full $ df -H . Filesystem Size Used Avail Use% Mounted on /dev/sda12 11G 8.2G 1.7G 84% /var How can I tell postgresql to record databases in an other partition ? For now they are in: [EMAIL PROTECTED] /var/lib/pgsql/data/base] $ ls 1 17142 18275591 26876456 537107 6123076 657110 802399 17141 17837306 18372925 533001596944 613177321277 8500925 will it be simply a move of the files, or should I have to dumprestore the databases ? I don't think 7.4 had tablespaces (check the manual). In which case you'll have to do it manually. 1. Stop the database server. 2. Create a new location for your db stuff /some/where/pgsql2/data/base 3. Move the directories you want over to the new location 4. Symlink each of the directories cd /var/lib/pgsql/data/base link -s /some/where/pgsql2/data/base/17142 5. Check ownership permissions 6. Restart database If you want to move all of them, rather than just individual databases then you just need to move .../data and reset your PGDATA to point at that. Can't remember where that gets set with the old RPMS - try in /etc/postgresql or /etc/pgsql apperently PGDATA is set in .bash_profile and points to /var/lib/pgsql/data: [EMAIL PROTECTED] ~] $ cat .bash_profile PGDATA=/var/lib/pgsql/data [ -f $PGDATA/../initdb.i18n ] source $PGDATA/../initdb.i18n export PGDATA So I suppose I'll have to stop postgres, move (or copy to be safe ..) /var/lib/pgsql/data to /data2/pgsql/data (/data2 is a file system with plenty of space) , set PGDATA=/data2/pgsql/data and restart postgres, that's all ? thanks for a confirmation. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] a failover scenario
I am considering pgsql as the RDBMS in a project with the following constraints: - there's a master and reserve instance of the RDBMS on every remote location - there's a master and reserve instance of the RDBMS on a central location - the connections are DSL connections and therefore unreliable - all changes have to be propagated to all servers (multimaster replication) - if the connection between a remote location and the central location fails, the local server continues working and resynchronizes with the central server when the connection is restored - if any master fails, the reserve instance takes over and the rest of the system acts as though nothing happened The master/reserve instance is, from what I read, standard functionality, but I'm not so sure about the resynchronization part of a failed link...I imagine something like WAL shipping might be of use here, but it's just an uneducated guess. Does code exist to support this on pgsql or is it considered application specific functionality? Do other RDBMSs support similar functionality? TIA, t.n.a. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Poor Plan selected w/ not provided a date/time but selecting date/time from a table
Ow Mun Heng wrote: On Wed, 2007-10-17 at 22:47 -0300, Alvaro Herrera wrote: Ow Mun Heng wrote: Index Cond: ((audit_key_dtime = $0) AND (audit_key_dtime $1)) Index Cond: ((audit_key_dtime = '2007-08-08 18:00:00'::timestamp without time zone) AND (audit_key_dtime '2007-08-08 18:01:00'::timestamp without time zone)) This is _the_ only difference between the 2 queries where on one, the dates are provided, and the other is selected from a table. I have no idea why the plans are so different between the two. The difference is that it has to consider the worst possibility in the second case, whereas the other one knows the interval is just one minute. Actually the dates are just 1 min apart in both cases. Of course. My point is that the planner doesn't know that in the first case. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] a failover scenario
In response to Tomi N/A [EMAIL PROTECTED]: I am considering pgsql as the RDBMS in a project with the following constraints: - there's a master and reserve instance of the RDBMS on every remote location - there's a master and reserve instance of the RDBMS on a central location - the connections are DSL connections and therefore unreliable - all changes have to be propagated to all servers (multimaster replication) - if the connection between a remote location and the central location fails, the local server continues working and resynchronizes with the central server when the connection is restored - if any master fails, the reserve instance takes over and the rest of the system acts as though nothing happened The master/reserve instance is, from what I read, standard functionality, but I'm not so sure about the resynchronization part of a failed link...I imagine something like WAL shipping might be of use here, but it's just an uneducated guess. Does code exist to support this on pgsql or is it considered application specific functionality? Do other RDBMSs support similar functionality? I don't know of any system that will just hand you those capabilities. Every multi-master system I've ever heard of requires high-speed links between the masters, otherwise the synchronization is far too slow to be usable. I believe you could do what you want in the application. PostgreSQL 8.3 will have a native UUID type, which will help with managing conflicts between multiple masters. If you can define clear rules on how to manage conflicts, that can be done automatically. If the rules aren't so clear, you'll need an interface where a human can manage conflicts. With triggers and LISTEN/NOTIFY, you can put together an app that handles replicating data when tables experience changes. From there, you'll need to structure your schema so such an app can detect conflicts, (create last_updated timestamps on all tables, and ensure that primary keys include a UUID or other mechanism to guarantee uniqueness) and design some sort of queue mechanism to ensure updates can wait while network problems are resolved. How much effort such a thing requires is dependent on how complex the data is. If it's a sales database (for example) it's not all that hard, since there aren't typical cases where two people are simultaneously updating the same record. I know, for example, that the PA gaming commission is putting something like this together for the race tracks. Each track has handheld devices that are used to record bets/payouts, etc. These devices can't be connected all the time, but a sync system is pretty easy because all they ever do is _add_ new records. Thus, you assign each handheld a unique device ID, and that's part of the primary key for each table, so there's no chance of of conflict. Sounds like a fun and challenging project. I'm jealous. -- Bill Moran http://www.potentialtech.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Crosstab Problems
Hi there, successfully installed the tablefunc package. Now, I would like to transform this kind of result based on a normal SQL: c_name |year|value --- Germany | 2001| 123 Germany | 2002| 125 Germany | 2003| 128 Germany | 2004| 132 Germany | 2005| 135 Italy | 2001| 412 Italy | 2002| 429 Italy | 2003| 456 Italy | 2004| 465 Italy | 2005| 477 to this one: c_name | 2001 |2002 | 2003 | 2004 | 2005 Germany| 123 | 125 . Italy | 412 | . I use this SQL statement: SELECT * FROM crosstab( 'SELECT c.name AS name, year_start AS year, value FROM agri_area AS d LEFT JOIN countries AS c ON c.id = id_country WHERE year_start = 2003 OR year_start = 2002 OR year_start = 2001 ORDER BY name ASC, year_start ASC;' , 3) AS ct(name varchar, y_2003 numeric, y_2002 numeric, y_2001 numeric) I had a couple of problems getting there. But now that I have the feeling that this is OK, it tells me this: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. Can anyone tell me why? And how to get it right? Thanks for any advice! Stef
Re: [GENERAL] Crosstab Problems
Stefan Schwarzer [EMAIL PROTECTED] writes: I had a couple of problems getting there. But now that I have the feeling that this is OK, it tells me this: server closed the connection unexpectedly Could you provide a self-contained test case for this? There's not really enough information here for someone else to duplicate the problem. Also, which PG version are you using? regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Am I overseen ?
Hi, Several groups I post most of them does not answer. Did noone see my postings ? Please at least give me one answer :-) Thanks, Lothar ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Am I overseen ?
Lothar Behrens wrote: Hi, Several groups I post most of them does not answer. Did noone see my postings ? Please at least give me one answer :-) Thanks, Lothar ping What was your query? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Crosstab Problems
Could you provide a self-contained test case for this? There's not really enough information here for someone else to duplicate the problem. Also, which PG version are you using? Wasn't sure what you ment with a self containted test case. Is it the raw data? Here is a SQL dump for the table. One can just neglect the JOIN with the countries table (which just replaces the country id with the country name): http://geodata.grid.unep.ch/download/sql_agri_area.sql.zip But when re-doing the query now without the JOIN, it works (almost): SELECT * FROM crosstab( 'SELECT id_country AS id, year_start AS year, value FROM agri_area AS d WHERE year_start = 2003 OR year_start = 2002 OR year_start = 2001 ORDER BY year_start ASC, id_country ASC;' , 3) AS ct(id int2, y_2003 numeric, y_2002 numeric, y_2001 numeric) Now, the problem is that it lists three times the IDs, and only the first year column is filled with values. The other two year columns stay empty. Thanks for any advice! Stef ---(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] Am I overseen ?
2007/10/18, Lothar Behrens [EMAIL PROTECTED]: Hi, Several groups I post most of them does not answer. Did noone see my postings ? Please at least give me one answer :-) Thanks, Lothar I see your post Pavel ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] running postgres
and i though PostgreSQL would be easier to get up and running than SQL server -- Ken
Re: [GENERAL] running postgres
Ken Johansson wrote: and i though PostgreSQL would be easier to get up and running than SQL server Question? Complaint? Thinking out loud? Lyrics to a song? We're all standing by, Ken! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Crosstab Problems
Stefan Schwarzer [EMAIL PROTECTED] writes: Here is a SQL dump for the table. One can just neglect the JOIN with the countries table (which just replaces the country id with the country name): http://geodata.grid.unep.ch/download/sql_agri_area.sql.zip But when re-doing the query now without the JOIN, it works (almost): OK, after poking at it, it seems that crosstab() isn't prepared for null rowids. I can reproduce the crash without any data: contrib_regression=# select * from crosstab( 'SELECT null::text as name, 10 as year, 42 as value', 3) as ct(name text, year int, value int); server closed the connection unexpectedly Backtrace looks like #0 0xc008774c in ?? () from /usr/lib/libc.1 #1 0x3eb0bc in MemoryContextStrdup (context=0x40167048, string=0x0) at mcxt.c:662 #2 0xc0a5f2e4 in crosstab (fcinfo=0x7b03b858) at tablefunc.c:539 #3 0x239e24 in ExecMakeTableFunctionResult (funcexpr=0x401615e8, econtext=0x401611f0, expectedDesc=0x401613a0, returnDesc=0x7b03b7d8) at execQual.c:1566 #4 0x24d264 in FunctionNext (node=0x40161160) at nodeFunctionscan.c:68 #5 0x23ed8c in ExecScan (node=0x40161160, accessMtd=0x400170b2 DINFINITY+3218) at execScan.c:68 #6 0x24d2c4 in ExecFunctionScan (node=0x40167048) at nodeFunctionscan.c:109 so it's trying to pstrdup a null result from SPI_getvalue. Obviously it shouldn't crash, but I'm not sure what it *should* do in this case. Joe? In the meantime, it appears that you want to not use a LEFT JOIN here, or else maybe COALESCE(c.name, '') so that a null isn't returned to crosstab. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] running postgres
On 10/18/07, Ken Johansson [EMAIL PROTECTED] wrote: and i though PostgreSQL would be easier to get up and running than SQL server I sincerely hope you don't consider that the sole criteria for choosing databases. Personally, I found it quite easy. apt-get install postgresqlp8.2 sudo /etc/init.d/postgres-8.2 start sudo su - postgres psql 4 whole steps. I must be forgetting something there. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Problem of installation on Mac
Hi, I am a newbie for pgsql and tried to install it on my MacBookPro Intel. I got everything correct until creating the user: postgres and initdb, then the error message showed up for running the db. How to fix the problem? LizheXusComputer:/Users/lizhexu pgsql$ /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data logfile 21 [1] 17837 LizheXusComputer:/Users/lizhexu pgsql$ su: logfile: Permission denied [1]+ Exit 1 /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data logfile 21 LizheXusComputer:/Users/lizhexu pgsql$ su lizhexu Password: LizheXusComputer:~ lizhexu$ /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data logfile 21 [1] 17842 LizheXusComputer:~ lizhexu$ /usr/local/pgsql/bin/createdb test createdb: could not connect to database postgres: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket /tmp/.s.PGSQL.5432? [1]+ Exit 2 /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data logfile 21 Thank you very much. L
Re: [GENERAL] Am I overseen ?
On 18 Okt., 17:17, [EMAIL PROTECTED] (brian) wrote: Lothar Behrens wrote: Hi, Several groups I post most of them does not answer. Did noone see my postings ? Please at least give me one answer :-) Thanks, Lothar ping What was your query? I do convert an UML XMI model to a database script to create the database schema. To enable multiple iterations I need conditional alter table add column like syntax. For sample: if not exsist column a in table b then alter table b add a char(100); Is there any way to do this ? Tanks, Lothar ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Crosstab Problems
On 10/18/07, Stefan Schwarzer [EMAIL PROTECTED] wrote: Could you provide a self-contained test case for this? There's not really enough information here for someone else to duplicate the problem. Also, which PG version are you using? Wasn't sure what you ment with a self containted test case. Is it the raw data? Here is a SQL dump for the table. One can just neglect the JOIN with the countries table (which just replaces the country id with the country name): http://geodata.grid.unep.ch/download/sql_agri_area.sql.zip But when re-doing the query now without the JOIN, it works (almost): SELECT * FROM crosstab( 'SELECT id_country AS id, year_start AS year, value FROM agri_area AS d WHERE year_start = 2003 OR year_start = 2002 OR year_start = 2001 ORDER BY year_start ASC, id_country ASC;' , 3) AS ct(id int2, y_2003 numeric, y_2002 numeric, y_2001 numeric) Now, the problem is that it lists three times the IDs, and only the first year column is filled with values. The other two year columns stay empty. I use crosstab for a rather large weekly report in our db and it works fine, however, you can't feel it nulls. It needs all the holes filled in, so to speak. In mine I had to use generate_series to make sure all the rows were there, then coalesce to make sure there were no nulls. You might need to do something like that in yours. I'm trying it out now. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Am I overseen ?
Gotcha! - Original Message From: Lothar Behrens [EMAIL PROTECTED] To: pgsql-general@postgresql.org Sent: Thursday, 18 October, 2007 7:40:54 PM Subject: [GENERAL] Am I overseen ? Hi, Several groups I post most of them does not answer. Did noone see my postings ? Please at least give me one answer :-) Thanks, Lothar ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ___ Want ideas for reducing your carbon footprint? Visit Yahoo! For Good http://uk.promotions.yahoo.com/forgood/environment.html
Re: [GENERAL] Am I overseen ?
On 10/18/07, Lothar Behrens [EMAIL PROTECTED] wrote: On 18 Okt., 17:17, [EMAIL PROTECTED] (brian) wrote: Lothar Behrens wrote: Hi, Several groups I post most of them does not answer. Did noone see my postings ? Please at least give me one answer :-) Thanks, Lothar ping What was your query? I do convert an UML XMI model to a database script to create the database schema. To enable multiple iterations I need conditional alter table add column like syntax. For sample: if not exsist column a in table b then alter table b add a char(100); Is there any way to do this ? What kind of tools are you familiar with? I'm guessing you could write something in perl or php to do it. Or do you want to do it in sql? I'm sure you could do it in plpgsql or some other pl/language, if you want it all in the db. But I'm not that familiar with UML as to say what's the best approach. Normally when you get thundering silence, you've asked a question no one feels real qualified to answer, and rather than answer it halfway, they just leave it for the next guy to get. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re : [GENERAL] pg_dump - schema diff compatibility
Hi, May be a solution will be to make a diff fo the XML file produced by another tool: DdlUtils (http://db.apache.org/ddlutils/) ! However, DdlUtils won't produce a file with all PG elements (no user types, views, triggers, ...). Have fun, [EMAIL PROTECTED] The Computing Froggy - Message d'origine De : Sualeh Fatehi [EMAIL PROTECTED] À : pgsql-general@postgresql.org Envoyé le : Mardi, 16 Octobre 2007, 13h53mn 00s Objet : Re: [GENERAL] pg_dump - schema diff compatibility SchemaCrawler for PostgreSQL will allow you to do the diffs. With SchemaCrawler for PostgreSQL, you can take human-readable snapshots of the schema and data, for later comparison. SchemaCrawler outputs details of your schema (tables, views, procedures, and more) in a diff- able plain-text format (text, CSV, or XHTML). You can order data alphabetically, or by ordinal order. SchemaCrawler can also output data (including CLOBs and BLOBs) in the same plain-text formats. SchemaCrawler for PostgreSQL is free and open-source (under the GPL), and is available at SourceForge: http://schemacrawler.sourceforge.net/ Sualeh Fatehi. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq __ Stockage illimité de vos mails avec Yahoo! Mail. Changez aujourd'hui de mail !
Re: [GENERAL] Problem of installation on Mac
I just install it as my own user on my laptop... I usually do something like ./configure --prefix=/Users/myaccount/pgsql make make install then cd ~/ pgsql/bin/initdb -D /Users/myaccount/pgsql/data pgsql/bin/pg_ctl _D /Users/myaccount/pgsql/data start and I'm off to the races ;-) Good luck! On 10/18/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi, I am a newbie for pgsql and tried to install it on my MacBookPro Intel. I got everything correct until creating the user: postgres and initdb, then the error message showed up for running the db. How to fix the problem? LizheXusComputer:/Users/lizhexu pgsql$ /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data logfile 21 [1] 17837 LizheXusComputer:/Users/lizhexu pgsql$ su: logfile: Permission denied [1]+ Exit 1 /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data logfile 21 LizheXusComputer:/Users/lizhexu pgsql$ su lizhexu Password: LizheXusComputer:~ lizhexu$ /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data logfile 21 [1] 17842 LizheXusComputer:~ lizhexu$ /usr/local/pgsql/bin/createdb test createdb: could not connect to database postgres: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket /tmp/.s.PGSQL.5432? [1]+ Exit 2 /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data logfile 21 Thank you very much. L
[GENERAL] ordering rows
Hi I have a table 'import' which is an amalgam of two other tables 'loops' and 'devices'. The ID column of loops is reflected in the import table. However the order of rows ends up as, a for instance, 6, 8, 7, 4, 5 3, 2, 1. I need to fetch these rows one at a time, in sequence with the loop ID, to process them in a delphi interface before going on to the next row. Query- 1. Can I somehow ensure that the rows are transfered to the import table in numerical sequence with the ID of the loop table? 2. Is there a method of using the fetch command to ensure that rows are picked up sequentially - lowest ID to highest ID or vice-versa? 3. Is there another mehod of accomplishing this task other than fetch? Bob Pawley
Re: [GENERAL] Crosstab Problems
On 10/18/07, Stefan Schwarzer [EMAIL PROTECTED] wrote: But when re-doing the query now without the JOIN, it works (almost): SELECT * FROM crosstab( 'SELECT id_country AS id, year_start AS year, value FROM agri_area AS d WHERE year_start = 2003 OR year_start = 2002 OR year_start = 2001 ORDER BY year_start ASC, id_country ASC;' , 3) AS ct(id int2, y_2003 numeric, y_2002 numeric, y_2001 numeric) Now, the problem is that it lists three times the IDs, and only the first year column is filled with values. The other two year columns stay empty. You missed this point in the docs: Notes 1. The sql result must be ordered by 1,2. Change your order by to that and it works fine. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] running postgres
Hello, Sorry everyone for the stupid rant. Wont happen again. And thanks for offer assistance. I seem to have it running and once i have a valid question ill post it. Sorry again.. Ken On 18/10/2007, brian [EMAIL PROTECTED] wrote: Ken Johansson wrote: and i though PostgreSQL would be easier to get up and running than SQL server Question? Complaint? Thinking out loud? Lyrics to a song? We're all standing by, Ken! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Ken
Re: [GENERAL] ordering rows
Bob Pawley wrote: Hi I have a table 'import' which is an amalgam of two other tables 'loops' and 'devices'. The ID column of loops is reflected in the import table. However the order of rows ends up as, a for instance, 6, 8, 7, 4, 5 3, 2, 1. These are the IDs from loops? I need to fetch these rows one at a time, in sequence with the loop ID, to process them in a delphi interface before going on to the next row. ORDER BY loops.id ASC Query- 1. Can I somehow ensure that the rows are transfered to the import table in numerical sequence with the ID of the loop table? Ensure that *what* is transferred? The rows from imports? Do they have a foreign key pointing to loops? 2. Is there a method of using the fetch command to ensure that rows are picked up sequentially - lowest ID to highest ID or vice-versa? 3. Is there another mehod of accomplishing this task other than fetch? Perhaps you should post your SELECT statement and a sample of the result you're getting (and a sample of what you desire to get wouldn't hurt). brian ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] running postgres
On 10/18/07, Ken Johansson [EMAIL PROTECTED] wrote: Hello, Sorry everyone for the stupid rant. Wont happen again. And thanks for offer assistance. I seem to have it running and once i have a valid question ill post it. Don't worry about it. We've all had to cut our teeth sometime. And don't worry, it shouldn't take long before something truly puzzling has you back on the lists. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] running postgres
Ken Johansson wrote: Hello, Sorry everyone for the stupid rant. Wont happen again. And thanks for offer assistance. I seem to have it running and once i have a valid question ill post it. Sorry again.. Ken We'll put it down as thinking out loud then. It happens. ;-) brian ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] ordering rows
That's marvelous - thanks. Bob - Original Message - From: brian [EMAIL PROTECTED] To: pgsql-general@postgresql.org Sent: Thursday, October 18, 2007 9:45 AM Subject: Re: [GENERAL] ordering rows Bob Pawley wrote: Hi I have a table 'import' which is an amalgam of two other tables 'loops' and 'devices'. The ID column of loops is reflected in the import table. However the order of rows ends up as, a for instance, 6, 8, 7, 4, 5 3, 2, 1. These are the IDs from loops? I need to fetch these rows one at a time, in sequence with the loop ID, to process them in a delphi interface before going on to the next row. ORDER BY loops.id ASC Query- 1. Can I somehow ensure that the rows are transfered to the import table in numerical sequence with the ID of the loop table? Ensure that *what* is transferred? The rows from imports? Do they have a foreign key pointing to loops? 2. Is there a method of using the fetch command to ensure that rows are picked up sequentially - lowest ID to highest ID or vice-versa? 3. Is there another mehod of accomplishing this task other than fetch? Perhaps you should post your SELECT statement and a sample of the result you're getting (and a sample of what you desire to get wouldn't hurt). brian ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Am I overseen ?
On 18 Okt., 18:22, [EMAIL PROTECTED] (Scott Marlowe) wrote: On 10/18/07, Lothar Behrens [EMAIL PROTECTED] wrote: On 18 Okt., 17:17, [EMAIL PROTECTED] (brian) wrote: Lothar Behrens wrote: Hi, Several groups I post most of them does not answer. Did noone see my postings ? Please at least give me one answer :-) Thanks, Lothar ping What was your query? I do convert an UML XMI model to a database script to create the database schema. To enable multiple iterations I need conditional alter table add column like syntax. For sample: if not exsist column a in table b then alter table b add a char(100); Is there any way to do this ? What kind of tools are you familiar with? I'm guessing you could write something in perl or php to do it. Or do you want to do it in sql? I'm sure you could do it in plpgsql or some other pl/language, if you want it all in the db. Just a copy of my mail. UML is only a hint, where my source is. I transform it to a big SQL query to be executed inside a C/C++ application. So no scripting available yet. Also no XML decoding to issue ODBC API commands to determine existing tables / columns. But I'm not that familiar with UML as to say what's the best approach. Transforming it to SQL queries is best for me, because the application model is stored in SQL database. UML is only a possible input to better model the app. ER diagramming tools that support XML export would also be an option. Normally when you get thundering silence, you've asked a question no one feels real qualified to answer, and rather than answer it halfway, they just leave it for the next guy to get. In my opinion the question was clear. How to alter table if the column not already inserted of later of given type. I know about the systables of postgresql as each other database system has - propably :-) But I'm not that familar with it. I need a sample how that is done in plain SQL (i pass the script via ODBC to the database). Lothar ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Resetting SEQUENCEs
Hi,
I am quite surprised I could not find a way to automatically reset the value of
a sequence for all my tables.
Of course, I can write:
SELECT setval('serial', max(id)) FROM distributorsBut if I reload data into all
my tables, it's a real pain to have to write something like this for every
single table with a sequence.
I would expect PostgreSQL to provide some command like:
resynchAllSequences my_schema;
Can this be a feature in the future ?
Does anybody have a solution for now ? A script or (even better) a pl/pgSql
function ?
Cheers,
[EMAIL PROTECTED]
The Computing Froggy
_
Ne gardez plus qu'une seule adresse mail ! Copiez vos mails vers Yahoo! Mail
Re: [GENERAL] Problem of installation on Mac
[EMAIL PROTECTED] wrote: Hi, I am a newbie for pgsql and tried to install it on my MacBookPro Intel. I got everything correct until creating the user: postgres and initdb, then the error message showed up for running the db. How to fix the problem? LizheXusComputer:/Users/lizhexu pgsql$ /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data logfile 21 [1] 17837 LizheXusComputer:/Users/lizhexu pgsql$ su: logfile: Permission denied [1]+ Exit 1 /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data logfile 21 LizheXusComputer:/Users/lizhexu pgsql$ su lizhexu Password: LizheXusComputer:~ lizhexu$ /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data logfile 21 [1] 17842 LizheXusComputer:~ lizhexu$ /usr/local/pgsql/bin/createdb test createdb: could not connect to database postgres: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket /tmp/.s.PGSQL.5432? [1]+ Exit 2 /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data logfile 21 Thank you very much. L I would say the problem is when you are logged in as pgsql the logfile part tries to put the log file in the current working directory (/Users/lizhexu in your example). Your pgsql user doesn't have permission to write the log file there. When you are logged in as lizhexu you don't get permission to read the data files. Also it should be logfile changing logfile to /usr/local/pgsql/data/logfile will most likely fix the problem. I would suggest looking into contrib/start-scripts (from the source distro) - look at PostgreSQL.darwin - the comments at the top should get you started. These steps will have postgres running when you startup your Mac. You will need to change PGUSER=postgres to PGUSER=pgsql, leave the rest and try running it. hint - with startup scripts if you don't want to restart you can use - sudo /Library/StartupItems/PostgreSQL/PostgreSQL start Also which version are you installing and did you get a binary package somewhere or are you building from source? -- Shane Ambler [EMAIL PROTECTED] Get Sheeky @ http://Sheeky.Biz ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Am I overseen ?
Lothar Behrens wrote: On 18 Okt., 18:22, [EMAIL PROTECTED] (Scott Marlowe) wrote: Normally when you get thundering silence, you've asked a question no one feels real qualified to answer, and rather than answer it halfway, they just leave it for the next guy to get. In my opinion the question was clear. How to alter table if the column not already inserted of later of given type. I think Scott meant that if a question--clear or not--is not something one feels one is qualified to answer, then one generally does not. In my opinion, though, the question was not clear. I know about the systables of postgresql as each other database system has - propably :-) But I'm not that familar with it. I need a sample how that is done in plain SQL (i pass the script via ODBC to the database). I don't feel at all qualified to answer this. Sorry. brian ---(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] Resetting SEQUENCEs
2007/10/18, Laurent ROCHE [EMAIL PROTECTED]:
Hi,
I am quite surprised I could not find a way to automatically reset the value
of a sequence for all my tables.
Of course, I can write:
SELECT setval('serial', max(id)) FROM distributors
But if I reload data into all my tables, it's a real pain to have to write
something like this for every single table with a sequence.
I would expect PostgreSQL to provide some command like:
resynchAllSequences my_schema;
try something like
CREATE FUNCTION execute(in_sql TEXT) RETURNS void as $$
BEGIN
EXECUTE in_sql;
RETURN;
END;
$BODY$ language plpgsql;
select execute(
$$select setval( '$$
|| table_name ||
$$_id_seq', coalesce((select max(id) from $$
|| table_name ||
$$),1), false ) $$
) from information_schema.tables where you want;
--
Filip Rembiałkowski
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Resetting SEQUENCEs
On Thursday 18 October 2007, Laurent ROCHE [EMAIL PROTECTED] wrote:
Hi,
I am quite surprised I could not find a way to automatically reset the
value of a sequence for all my tables.
Of course, I can write:
SELECT setval('serial', max(id)) FROM distributorsBut if I reload data
into all my tables, it's a real pain to have to write something like this
for every single table with a sequence.
I would expect PostgreSQL to provide some command like:
resynchAllSequences my_schema;
Can this be a feature in the future ?
Restoring backups normally will set sequences to the correct values; you're
doing something wrong if yours are not.
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[GENERAL] pg_dump SERIAL and SEQUENCE
Hi,
I have a problem with pg_dump generating a code with no SERIALs id but with
SEQUENCEs instead: if I write in a SERIAL I want to see a SERIAL, even when I
reload the database.
We had this conversation before on this mailing list and I was told that's
because SERIAL is just a kind of macro generating the same thing as the
equivalent code translated using SEQUENCE.
This is not true however, if I create tab_a like this:
CREATE TABLE tab_a (
cola1 SERIAL
);and tab_b like this (whic is what pg_dump will do)
CREATE SEQUENCE tab_b_colb1_seq;
CREATE TABLE tab_b(
colb1 integer DEFAULT nextval('tab_b_colb1_seq') NOT NULL
);
Then when I drop tab_b, the SEQUENCE tab_b_colb1_seq
is not dropped, however when I drop tab_a, the SEQUENCE tab_a_cola1_seq is
dropped too !
I am using PG 8.1 and ALTER SEQUENCE ... OWNED BY does not exist ! ! !
Cheers,
[EMAIL PROTECTED]
The Computing Froggy
_
Ne gardez plus qu'une seule adresse mail ! Copiez vos mails vers Yahoo! Mail
Re : [GENERAL] Am I overseen ?
Hi, You might want to have a look at DdlUtils that does similar things that you want to do: compare databases schemas using XML files and synchronising the schemas. Interesting tool ! ! http://db.apache.org/ddlutils/ Have fun, [EMAIL PROTECTED] The Computing Froggy - Message d'origine De : Scott Marlowe [EMAIL PROTECTED] À : Lothar Behrens [EMAIL PROTECTED] Cc : pgsql-general@postgresql.org Envoyé le : Jeudi, 18 Octobre 2007, 18h22mn 12s Objet : Re: [GENERAL] Am I overseen ? On 10/18/07, Lothar Behrens [EMAIL PROTECTED] wrote: On 18 Okt., 17:17, [EMAIL PROTECTED] (brian) wrote: Lothar Behrens wrote: Hi, Several groups I post most of them does not answer. Did noone see my postings ? Please at least give me one answer :-) Thanks, Lothar ping What was your query? I do convert an UML XMI model to a database script to create the database schema. To enable multiple iterations I need conditional alter table add column like syntax. For sample: if not exsist column a in table b then alter table b add a char(100); Is there any way to do this ? What kind of tools are you familiar with? I'm guessing you could write something in perl or php to do it. Or do you want to do it in sql? I'm sure you could do it in plpgsql or some other pl/language, if you want it all in the db. But I'm not that familiar with UML as to say what's the best approach. Normally when you get thundering silence, you've asked a question no one feels real qualified to answer, and rather than answer it halfway, they just leave it for the next guy to get. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ _ Ne gardez plus qu'une seule adresse mail ! Copiez vos mails vers Yahoo! Mail
Re: [GENERAL] Problem of installation on Mac
Thank you very much Shane. It fixed my problem, at least now :-). I installed the pgsql 8.2.5 from source. The user postgres has a shortname of pgsql. Another question, what's the purpose/advantage to create a user of postgres? May I just create the database under my user account? Shane Ambler [EMAIL PROTECTED] 10/18/2007 01:27 PM To [EMAIL PROTECTED] cc pgsql-general@postgresql.org Subject Re: [GENERAL] Problem of installation on Mac [EMAIL PROTECTED] wrote: Hi, I am a newbie for pgsql and tried to install it on my MacBookPro Intel. I got everything correct until creating the user: postgres and initdb, then the error message showed up for running the db. How to fix the problem? LizheXusComputer:/Users/lizhexu pgsql$ /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data logfile 21 [1] 17837 LizheXusComputer:/Users/lizhexu pgsql$ su: logfile: Permission denied [1]+ Exit 1 /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data logfile 21 LizheXusComputer:/Users/lizhexu pgsql$ su lizhexu Password: LizheXusComputer:~ lizhexu$ /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data logfile 21 [1] 17842 LizheXusComputer:~ lizhexu$ /usr/local/pgsql/bin/createdb test createdb: could not connect to database postgres: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket /tmp/.s.PGSQL.5432? [1]+ Exit 2 /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data logfile 21 Thank you very much. L I would say the problem is when you are logged in as pgsql the logfile part tries to put the log file in the current working directory (/Users/lizhexu in your example). Your pgsql user doesn't have permission to write the log file there. When you are logged in as lizhexu you don't get permission to read the data files. Also it should be logfile changing logfile to /usr/local/pgsql/data/logfile will most likely fix the problem. I would suggest looking into contrib/start-scripts (from the source distro) - look at PostgreSQL.darwin - the comments at the top should get you started. These steps will have postgres running when you startup your Mac. You will need to change PGUSER=postgres to PGUSER=pgsql, leave the rest and try running it. hint - with startup scripts if you don't want to restart you can use - sudo /Library/StartupItems/PostgreSQL/PostgreSQL start Also which version are you installing and did you get a binary package somewhere or are you building from source? -- Shane Ambler [EMAIL PROTECTED] Get Sheeky @ http://Sheeky.Biz
Re : [GENERAL] Resetting SEQUENCEs
It is not as easy as that :
1/ select table_name from information_schema.tables does not give you the
tables list on the current schema but the list of all objects !
But the following will
SELECT c.relname
FROM pg_namespace AS nc, pg_class AS c
WHERE c.relnamespace = nc.oid
AND c.relkind IN ('r' )
2/ Having the tables list is not enough (because obviously I know how to do
this), then you need to get the list of sequences (actually you want to get
first the sequences) and then the list of colums using the sequence and (at
last the tables where the columns are) .
At that point, it is possible to generate the SQL order to reset the SEQUENCE.
Unfortunately I do not know how to produce the list of sequences and columns
using them and columns tables.
Have fun,
[EMAIL PROTECTED]
The Computing Froggy
- Message d'origine
De : Filip Rembiałkowski [EMAIL PROTECTED]
À : Laurent ROCHE [EMAIL PROTECTED]
Cc : pgsql-general@postgresql.org
Envoyé le : Jeudi, 18 Octobre 2007, 19h33mn 50s
Objet : Re: [GENERAL] Resetting SEQUENCEs
2007/10/18, Laurent ROCHE [EMAIL PROTECTED]:
Hi,
I am quite surprised I could not find a way to automatically reset
the value
of a sequence for all my tables.
Of course, I can write:
SELECT setval('serial', max(id)) FROM distributors
But if I reload data into all my tables, it's a real pain to have to
write
something like this for every single table with a sequence.
I would expect PostgreSQL to provide some command like:
resynchAllSequences my_schema;
try something like
CREATE FUNCTION execute(in_sql TEXT) RETURNS void as $$
BEGIN
EXECUTE in_sql;
RETURN;
END;
$BODY$ language plpgsql;
select execute(
$$select setval( '$$
|| table_name ||
$$_id_seq', coalesce((select max(id) from $$
|| table_name ||
$$),1), false ) $$
) from information_schema.tables where you want;
--
Filip Rembiałkowski
---(end of
broadcast)---
TIP 2: Don't 'kill -9' the postmaster
__
Stockage illimité de vos mails avec Yahoo! Mail. Changez aujourd'hui de mail !
Re: [GENERAL] pg_dump SERIAL and SEQUENCE
Laurent ROCHE [EMAIL PROTECTED] schrieb: Then when I drop tab_b, the SEQUENCE tab_b_colb1_seq is not dropped, however when I drop tab_a, the SEQUENCE tab_a_cola1_seq is dropped too ! I am using PG 8.1 and ALTER SEQUENCE ... OWNED BY does not exist ! ! ! Read this message: [EMAIL PROTECTED] ,[ quote Tom Lane ] | In 8.2 and up you can use ALTER SEQUENCE ... OWNED BY ... to establish ` You see the difference? Regards, Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) If I was god, I would recompile penguin with --enable-fly.(unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] running postgres
Scott Marlowe wrote: On 10/18/07, Ken Johansson [EMAIL PROTECTED] wrote: and i though PostgreSQL would be easier to get up and running than SQL server I sincerely hope you don't consider that the sole criteria for choosing databases. I don't know - it's put me off MS-SQL in a big way. Not only don't they have an apt repository for Debian, they don't even have compile instructions on their site - gcc is throwing up all sorts of errors. Maybe I'll try on intel rather than ppc... -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: Re : [GENERAL] Am I overseen ?
I think this is not the way I should go. My application is a database prototype design tool that also is able to run the design because the designer it self is a design. So I did not only need a DDL to create the target application's database, also I need to feed a model into the system database that specifies the model at GUI level. (Data in general means) Therefore I have choosen the XMI / XML / XSLT approach to get SQL scripts at once. When there is a way to optionally add columns to existing tables via SQL I have very less development needs to get this working. If I do read the XML model into a DOM tree or use extarnal tools I probably have more to work on a solution. But thanks, it may help somehow. I'll have a look. Lothar Am 18.10.2007 um 19:41 schrieb Laurent ROCHE: Hi, You might want to have a look at DdlUtils that does similar things that you want to do: compare databases schemas using XML files and synchronising the schemas. Interesting tool ! ! http://db.apache.org/ddlutils/ Have fun, [EMAIL PROTECTED] The Computing Froggy - Message d'origine De : Scott Marlowe [EMAIL PROTECTED] À : Lothar Behrens [EMAIL PROTECTED] Cc : pgsql-general@postgresql.org Envoyé le : Jeudi, 18 Octobre 2007, 18h22mn 12s Objet : Re: [GENERAL] Am I overseen ? On 10/18/07, Lothar Behrens [EMAIL PROTECTED] wrote: On 18 Okt., 17:17, [EMAIL PROTECTED] (brian) wrote: Lothar Behrens wrote: Hi, Several groups I post most of them does not answer. Did noone see my postings ? Please at least give me one answer :-) Thanks, Lothar ping What was your query? I do convert an UML XMI model to a database script to create the database schema. To enable multiple iterations I need conditional alter table add column like syntax. For sample: if not exsist column a in table b then alter table b add a char(100); Is there any way to do this ? What kind of tools are you familiar with? I'm guessing you could write something in perl or php to do it. Or do you want to do it in sql? I'm sure you could do it in plpgsql or some other pl/language, if you want it all in the db. But I'm not that familiar with UML as to say what's the best approach. Normally when you get thundering silence, you've asked a question no one feels real qualified to answer, and rather than answer it halfway, they just leave it for the next guy to get. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ Ne gardez plus qu'une seule adresse mail ! Copiez vos mails vers Yahoo! Mail -- Lothar Behrens | Rapid Prototyping ... Heinrich-Scheufelen-Platz 2 | 73252 Lenningen | www.lollisoft.de
Re : [GENERAL] pg_dump SERIAL and SEQUENCE
Yep, of course but what's the situation for people using 8.1 and pg_dump that does not generate code with SERIAL ? Have fun, [EMAIL PROTECTED] The Computing Froggy - Message d'origine De : Andreas Kretschmer [EMAIL PROTECTED] À : pgsql-general@postgresql.org Envoyé le : Jeudi, 18 Octobre 2007, 20h02mn 47s Objet : Re: [GENERAL] pg_dump SERIAL and SEQUENCE Laurent ROCHE [EMAIL PROTECTED] schrieb: Then when I drop tab_b, the SEQUENCE tab_b_colb1_seq is not dropped, however when I drop tab_a, the SEQUENCE tab_a_cola1_seq is dropped too ! I am using PG 8.1 and ALTER SEQUENCE ... OWNED BY does not exist ! ! ! Read this message: [EMAIL PROTECTED] ,[ quote Tom Lane ] | In 8.2 and up you can use ALTER SEQUENCE ... OWNED BY ... to establish ` You see the difference? Regards, Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) If I was god, I would recompile penguin with --enable-fly. (unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ _ Ne gardez plus qu'une seule adresse mail ! Copiez vos mails vers Yahoo! Mail
Re: [GENERAL] Resetting SEQUENCEs
On Thu, Oct 18, 2007 at 10:06:00AM -0700, Laurent ROCHE wrote: Hi, I am quite surprised I could not find a way to automatically reset the value of a sequence for all my tables. I never bother resetting sequences. It's not like the numbers mean anything... Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] Am I overseen ?
On 18 Okt., 19:33, [EMAIL PROTECTED] (brian) wrote: Lothar Behrens wrote: On 18 Okt., 18:22, [EMAIL PROTECTED] (Scott Marlowe) wrote: Normally when you get thundering silence, you've asked a question no one feels real qualified to answer, and rather than answer it halfway, they just leave it for the next guy to get. In my opinion the question was clear. How to alter table if the column not already inserted of later of given type. I think Scott meant that if a question--clear or not--is not something one feels one is qualified to answer, then one generally does not. In my opinion, though, the question was not clear. Hmm, ok. I know about the systables of postgresql as each other database system has - propably :-) But I'm not that familar with it. I need a sample how that is done in plain SQL (i pass the script via ODBC to the database). I don't feel at all qualified to answer this. Sorry. I will ask differently. Is there a tool that keeps track to syncronize database models supporting postgresql (natively) ? Laurent gave me a hint to http://db.apache.org/ddlutils/ but it is only to convert database model to XML representation and vica versa. What I could read, it does not syncronize. Thus I would propably not able to look into it for how it works. I think I'll study the system tables for postgreSQL and do it anyhow. Thanks, Lothar brian ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: Re : [GENERAL] pg_dump SERIAL and SEQUENCE
Laurent ROCHE wrote: Yep, of course but what's the situation for people using 8.1 and pg_dump that does not generate code with SERIAL ? They grab 8.2's pg_dump and connect with it to their 8.1 server, to generate a dump that does. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(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] Crosstab Problems
Tom Lane wrote: so it's trying to pstrdup a null result from SPI_getvalue. Obviously it shouldn't crash, but I'm not sure what it *should* do in this case. Joe? The row is pretty useless without a rowid in this context -- it seems like the best thing to do would be to skip those rows entirely. Of course you could argue I suppose that it ought to throw an ERROR and bail out entirely. Maybe a good compromise would be to skip the row but throw a NOTICE? Joe ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Abbreviation list
Is there a comprehensive (or semi-comprehensive) on-line list of commonly used PG-related abbreviations used in PostgreSQL documentation, mail-lists, etc.? If there is not, would such a list make a reasonable one-page addition to the PG manual? Things like GUC and DDL for example. Couldn't find them in the user documentation index. I looked at what Google offered up with GUC site:www.postgresql.org and got 64 results. Virtually all were links to the weekly news but I found no result that said what GUC means. Cheers, Steve ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Abbreviation list
Steve Crawford wrote: Is there a comprehensive (or semi-comprehensive) on-line list of commonly used PG-related abbreviations used in PostgreSQL documentation, mail-lists, etc.? If there is not, would such a list make a reasonable one-page addition to the PG manual? I don't think we have one, but IMHO it would be an excellent idea. We do have some abbreviations, e.g. HOT, TOAST, WAL are the ones that jump immediately to mind (besides the ones you mentioned). -- Alvaro Herrera http://www.amazon.com/gp/registry/5ZYLFMCVHXC Granting software the freedom to evolve guarantees only different results, not better ones. (Zygo Blaxell) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: Re : [GENERAL] Am I overseen ?
Lothar Behrens [EMAIL PROTECTED] writes: When there is a way to optionally add columns to existing tables via SQL I have very less development needs to get this working. Why don't you just issue the ALTER ADD COLUMN and ignore the error if it's duplicate column? Or if you don't like that, it's not that hard to look into the system catalogs and see if the column already exists. A program bright enough to be issuing dynamic DDL commands ought to be able to do that ... regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Did pg_dumpall and imported, NEED TO START OVER
I'm preparing to upgrade and am practicing. I'm using v7.4 I created a blank .7.4 to import a pg_dumpall into. (Also a v8.2 to upgrade to.) The encoding was wrong and I have a lot of: psql:/tmp/cluster_pg_dumpall.txt:4382: ERROR: invalid byte sequence for encoding UTF8: 0x91 HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by client_encoding. CONTEXT: COPY brokenbin, line 443 Do I have to back out and start over? drop database? Or clear out and start over w/ a new install? If the latter, what do I drop before I can start over w/ an `initdb - E SQL_ASCII` THANKS! Ralph Smith [EMAIL PROTECTED] =
[GENERAL] autovacuum and locks
Hi all, recently our application was locked, and postgres shows several processes in LOCK TABLE waiting state. Even the autovacuum process is stalled. The pg_locks table shows the following: SELECT pg_class.relname AS table, transaction, pid, mode, granted FROM pg_locks, pg_class, pg_database WHERE pg_locks.relation = pg_class.oid AND pg_locks.database = pg_database.oid; pg_class3389057 | 26130 | AccessShareLock | t pg_class_oid_index 3389057 | 26130 | AccessShareLock | t pg_locks3389057 | 26130 | AccessShareLock | t cgreylist 3368984 | 10979 | ShareUpdateExclusiveLock | t cgreylist_pkey 3368984 | 10979 | ShareUpdateExclusiveLock | t cgreylist_extime_index 3368984 | 10979 | ShareUpdateExclusiveLock | t cgreylist_instance_sender_index 3368984 | 10979 | ShareUpdateExclusiveLock | t cgreylist_mtime_index 3368984 | 10979 | ShareUpdateExclusiveLock | t cgreylist 3368998 | 10980 | ExclusiveLock | f cgreylist 3369000 | 10984 | AccessShareLock | t cgreylist 3369000 | 10984 | RowExclusiveLock | f # WHY? cgreylist_extime_index 3369000 | 10984 | AccessShareLock | t cgreylist 3388458 | 10023 | ExclusiveLock | f ? cgreylist 3388420 | 10021 | ExclusiveLock | f ? Why cant postgres get the RowExclusiveLock in transaction 3369000? I do not see any confliction lock types here - or do i miss something? (we use postgresql-8.1 (8.1.8-1) on debian 4.0) - Dietmar ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] PG/Tcl and Expect?
Does the PG/Tcl interface allow expect scripts? I want to create a stored procedure that ssh's to another server and runs a Perl script. Expect could do it, but can I load the expect module from pgtcl? Thanks, Bret
re[GENERAL] lations does not exist
Hi, I doing a simple insert into a table re Perl/DBI INSERT INTO party (party_id, party_type_id, description, status_id) VALUES ($partyId, 'PERSON', 'Initial Import','PARTY_ENABLED') and I'm getting a ERROR: relations party does not exist I get the same error message in pgadmin. The table party certainly exists. I have searched for answers without any luck. Anyone know what might be going on and how to fix it? Thanks in advance, Case -- View this message in context: http://www.nabble.com/relations-does-not-exist-tf4636218.html#a13240608 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: Re : [GENERAL] pg_dump SERIAL and SEQUENCE
Alvaro Herrera [EMAIL PROTECTED] writes: Laurent ROCHE wrote: Yep, of course but what's the situation for people using 8.1 and pg_dump that does not generate code with SERIAL ? They grab 8.2's pg_dump and connect with it to their 8.1 server, to generate a dump that does. Actually, I bet the OP's problem is exactly that he did that already. 8.1 pg_dump *will* generate a SERIAL column definition, if it sees that the table being dumped was made that way. 8.2 prefers the OWNED BY route (for good reasons btw). regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] pg_dump SERIAL and SEQUENCE
On Oct 18, 2007, at 1:02 PM, Andreas Kretschmer wrote: Laurent ROCHE [EMAIL PROTECTED] schrieb: Then when I drop tab_b, the SEQUENCE tab_b_colb1_seq is not dropped, however when I drop tab_a, the SEQUENCE tab_a_cola1_seq is dropped too ! I am using PG 8.1 and ALTER SEQUENCE ... OWNED BY does not exist ! ! ! Read this message: [EMAIL PROTECTED] ,[ quote Tom Lane ] | In 8.2 and up you can use ALTER SEQUENCE ... OWNED BY ... to establish ` You see the difference? Regards, Andreas Andreas's point is that the OWNED BY clause was added in 8.2 to fix your issue and it is policy to not change functionality of older releases unless it is to fix an actual, proven error. Basically, if your sequence isn't owned by any particular table then it is considered an independent relation and you need to manage it's being dump/restored on its own just like with any other independent relation. Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate market in style. Visit us online at http://www.myemma.com ---(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] Group By question
Okay, actually the query is something like: select dept, (col1 + col2) * col3) from table group by dept So, the output would look something like: DeptTotal -- --- 1 26 2 18 3 9 Sam Mason [EMAIL PROTECTED] wrote: On Tue, Oct 16, 2007 at 07:46:34AM -0700, Jeff Lanzarotta wrote: Hello, I have a table that looks something like this: SKU Dept Col1 Col2 Col3 --- -- --- --- -- 1112 3 2123 4 3210 1 4201 2 5241 3 6312 3 I am having a problem trying to get the Is there a query that can do something like this: select sku, dept, (col1 + col2) * col3) from table group by dept What are you expecting the group by to do here? It may be helpful if you show what you expect the output to be. Sam ---(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] Fetch
When I fetch a row it returns a row number. Is there a method under the fetch command of either not returning the row number or of ignoring it after it is returned. begin work; Declare loop_set Cursor for Select one, two from loop_import order by loop_id ; fetch next From loop_set; Bob Pawley
[GENERAL] pg_dumpall's subseq IMPORT ERRORS
I looked farther up the STDOUT/STDERR of the psql import using the file from a pg_dumpall There are tons of errors there besides encoding. Sample errors: psql:/tmp/cluster_pg_dumpall.txt:10: ERROR: role airburst does not exist SET psql:/tmp/cluster_pg_dumpall.txt:14: ERROR: relation public.computers does not existist psql:/tmp/cluster_pg_dumpall.txt:19: ERROR: index stats2_etime_index does not exist psql:/tmp/cluster_pg_dumpall.txt:58: ERROR: table stats_before_spring04 does not exist psql:/tmp/cluster_pg_dumpall.txt:76: ERROR: sequence new_computers_id_seq does not exist psql:/tmp/cluster_pg_dumpall.txt:170: ERROR: language plpgsql does not exist SET psql:/tmp/cluster_pg_dumpall.txt:173: ERROR: function public.plpgsql_call_handler() does not exist CREATE FUNCTION SET psql:/tmp/cluster_pg_dumpall.txt:191: NOTICE: using pg_pltemplate information instead of CREATE LANGUAGE parameters psql:/tmp/cluster_pg_dumpall.txt:4382: ERROR: invalid byte sequence for encoding UTF8: 0x91 HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by client_encoding. CONTEXT: COPY brokenbin, line 443 Obviously this import is not a panacea! I do know that the new DB was initialized w/ UNICODE and the old w/ SQL_ASCII. I did a grep for CREATED or CREATE D, they're not in the dump. So, A) How do I back out since I imported this into (I guess) the 'postgres' database instead of 'airburst'? B) Once I get back there, what do I need to do besides a)CREATE DATABASE (with SQL_ASCII encoding) b) In that DB create the ROLE 'airburst' c) ? C) Then do I import the production version's dump? Thanks again all, Ralph Smith [EMAIL PROTECTED] =
Re: re[GENERAL] lations does not exist
On Tuesday 16 October 2007 12:20 pm, ctorres wrote: Hi, I doing a simple insert into a table re Perl/DBI INSERT INTO party (party_id, party_type_id, description, status_id) VALUES ($partyId, 'PERSON', 'Initial Import','PARTY_ENABLED') and I'm getting a ERROR: relations party does not exist I get the same error message in pgadmin. The table party certainly exists. I have searched for answers without any luck. Anyone know what might be going on and how to fix it? Thanks in advance, Case Two things come to mind. 1) Do you have the necessary permissions to access the schema table 'party' is in? 2) How was the name for 'party' originally entered? It could be a case sensitive problem. See http://www.postgresql.org/docs/8.2/interactive/sql-syntax-lexical.html Section 4.1.1 for a complete explanation. Basically if the table name was entered with quotes in a form other than 'party' then selecting for 'party' will result in the error above. -- Adrian Klaver [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] a failover scenario
2007/10/18, Bill Moran [EMAIL PROTECTED]: I don't know of any system that will just hand you those capabilities. Every multi-master system I've ever heard of requires high-speed links between the masters, otherwise the synchronization is far too slow to be usable. I supposed so. However, I have the advantage of not having a massive amount of data change anywhere at once...transactions are human-generated (with as little as 20-30 users) and typically affect just a handful of records at once. This means that an hour can pass between two transactions, but at peak times, a transaction every second or two might not be unusual. I believe you could do what you want in the application. PostgreSQL 8.3 will have a native UUID type, which will help with managing conflicts between multiple masters. If you can define clear rules on how to manage conflicts, that can be done automatically. If the rules aren't so clear, you'll need an interface where a human can manage conflicts. I don't intend to let conflicts occur...ever. Not in the sense that two databases allocate the same unique resource for different purposes. With triggers and LISTEN/NOTIFY, you can put together an app that handles replicating data when tables experience changes. From there, you'll need to structure your schema so such an app can detect conflicts, (create last_updated timestamps on all tables, and ensure that primary I was thinking something along those lines. It might make things easier if nothing was ever erased, only invalidated. keys include a UUID or other mechanism to guarantee uniqueness) and design some sort of queue mechanism to ensure updates can wait while network problems are resolved. UUIDs didn't come to mind, thanks for pointing them out. How much effort such a thing requires is dependent on how complex the data is. If it's a sales database (for example) it's not all that hard, since there aren't typical cases where two people are simultaneously updating the same record. Well...it's a sales database...and the risk of simultaneous updates is huge. :) I know, for example, that the PA gaming commission is putting something like this together for the race tracks. Each track has handheld devices that are used to record bets/payouts, etc. These devices can't be connected all the time, but a sync system is pretty easy because all they ever do is _add_ new records. Thus, you assign each handheld a unique device ID, and that's part of the primary key for each table, so there's no chance of of conflict. Sounds pretty simple...probably the tip of the iceberg. :) Sounds like a fun and challenging project. I'm jealous. It's not a project yet, but the tender is out...all we've got to do is win it. It's a shame the tender is fixed (for someone else), but blowing the whistle might help. Thanks for sharing. Cheers, t.n.a. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] variable in COPY table TO variable
A case for dynamic SQL!http://www.postgresql.org/docs/8.2/static/plpgsql-statements.html#PLPGSQ L-STATEMENTS-EXECUTING-DYN Yours, Laurenz Albe ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings Perfect. Thanks for the assistance Laurenz. Very useful. Rob ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: re[GENERAL] lations does not exist
On Tuesday 16 October 2007, ctorres [EMAIL PROTECTED] wrote: Hi, I doing a simple insert into a table re Perl/DBI INSERT INTO party (party_id, party_type_id, description, status_id) VALUES ($partyId, 'PERSON', 'Initial Import','PARTY_ENABLED') and I'm getting a ERROR: relations party does not exist I get the same error message in pgadmin. The table party certainly exists. Are you really connecting to the database you think you are? -- Peak Oil is now accepted as inevitable, and the debate only becomes as to when - James Schlesinger, former US Secretary of Energy ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Determining oldest WAL for Archiving PITR Standby - SOLVED
On 17-Oct-07, at 12:01 AM, Brian Wipf wrote: I'm working on a script that takes backups in intervals from our warm PITR stand by server (both servers running PG 8.2.5). The documentation advises running pg_controldata on the standby server to inspect the control file and determine the current checkpoint WAL location. I am hoping someone can confirm how to perform this step. From pg_controldata: Latest checkpoint location: 8E/624808 Latest checkpoint's TimeLineID: 1 Using the timeline id of 1, log id of 8E and log segment of 0, the oldest WAL needed for a recoverable backup is 0001008E It's not obvious to me why the output in this example doesn't indicate a log segment of 62 and offset of 4808, or a log segment of 6 and offset of 24808. After watching more output from pg_controldata, I can now answer the question I posted above. (Note: this is for PG 8.2.5. The behavior may be different for other PG versions.) The offset is the last 6 hex digits of the checkpoint location value. The offset contains leading zeros to make it 6 digits if its actual value is less than 6 digits. Therefore, the digits between the slash and the last 6 digits are the log segment value. If there are no digits between the slash and the last 6 hex digits, the log segment value is simply 0. If the checkpoint location is 2/320 and the timeline id is 1, the corresponding WAL is 00010002 Hope this helps, Brian Wipf ClickSpace Interactive Inc. [EMAIL PROTECTED] ---(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 : Re : [GENERAL] pg_dump SERIAL and SEQUENCE
Not good because in that case pg_dump will generate code that can not run in 8.1 ... like : ALTER SEQUENCE ... OWNED BY Have fun, [EMAIL PROTECTED] The Computing Froggy - Message d'origine De : Alvaro Herrera [EMAIL PROTECTED] À : Laurent ROCHE [EMAIL PROTECTED] Cc : Andreas Kretschmer [EMAIL PROTECTED]; pgsql-general@postgresql.org Envoyé le : Jeudi, 18 Octobre 2007, 20h33mn 15s Objet : Re: Re : [GENERAL] pg_dump SERIAL and SEQUENCE Laurent ROCHE wrote: Yep, of course but what's the situation for people using 8.1 and pg_dump that does not generate code with SERIAL ? They grab 8.2's pg_dump and connect with it to their 8.1 server, to generate a dump that does. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support _ Ne gardez plus qu'une seule adresse mail ! Copiez vos mails vers Yahoo! Mail
Re : [GENERAL] Abbreviation list
Yep, having a list would be good ! To answer your question, DDL is not a PG (here is another one) abbreviation but a database abbreviation. It stands for Data Definition Language ... and basically that means SQL orders to manage objects (CREATE, DROP, ALTER) see Wikipedia for more info. No idea what GUC is, though ! Have fun, [EMAIL PROTECTED] The Computing Froggy - Message d'origine De : Alvaro Herrera [EMAIL PROTECTED] À : Steve Crawford [EMAIL PROTECTED] Cc : pgsql-general@postgresql.org Envoyé le : Jeudi, 18 Octobre 2007, 21h10mn 47s Objet : Re: [GENERAL] Abbreviation list Steve Crawford wrote: Is there a comprehensive (or semi-comprehensive) on-line list of commonly used PG-related abbreviations used in PostgreSQL documentation, mail-lists, etc.? If there is not, would such a list make a reasonable one-page addition to the PG manual? I don't think we have one, but IMHO it would be an excellent idea. We do have some abbreviations, e.g. HOT, TOAST, WAL are the ones that jump immediately to mind (besides the ones you mentioned). -- Alvaro Herrera http://www.amazon.com/gp/registry/5ZYLFMCVHXC Granting software the freedom to evolve guarantees only different results, not better ones. (Zygo Blaxell) ---(end of broadcast)--- TIP 6: explain analyze is your friend _ Ne gardez plus qu'une seule adresse mail ! Copiez vos mails vers Yahoo! Mail
Re : Re : [GENERAL] Resetting SEQUENCEs
Hi, So nobody can help me to write the SELECT that will return the SEQUENCE names, and their linked columns and their linked tables ? Are the system tables documented somewhere ? Cheers, [EMAIL PROTECTED] The Computing Froggy - Message d'origine De : Laurent ROCHE [EMAIL PROTECTED] À : Martijn van Oosterhout [EMAIL PROTECTED] Envoyé le : Jeudi, 18 Octobre 2007, 20h15mn 46s Objet : Re : [GENERAL] Resetting SEQUENCEs Yes, but when I change a database schema and I reload the data, and I need to resynch the SEQUENCE so that the INSERT command will work ! Have fun, [EMAIL PROTECTED] The Computing Froggy - Message d'origine De : Martijn van Oosterhout [EMAIL PROTECTED] À : Laurent ROCHE [EMAIL PROTECTED] Cc : pgsql-general@postgresql.org Envoyé le : Jeudi, 18 Octobre 2007, 20h02mn 31s Objet : Re: [GENERAL] Resetting SEQUENCEs On Thu, Oct 18, 2007 at 10:06:00AM -0700, Laurent ROCHE wrote: Hi, I am quite surprised I could not find a way to automatically reset the value of a sequence for all my tables. I never bother resetting sequences. It's not like the numbers mean anything... Have a nice day, -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. Ne gardez plus qu'une seule adresse mail ! Copiez vos mails vers Yahoo! Mail _ Ne gardez plus qu'une seule adresse mail ! Copiez vos mails vers Yahoo! Mail
Re: [GENERAL] Determining oldest WAL for Archiving PITR Standby - SOLVED
On 18-Oct-07, at 3:15 PM, Brian Wipf wrote: The offset is the last 6 hex digits of the checkpoint location value. The offset contains leading zeros to make it 6 digits if its actual value is less than 6 digits. Therefore, the digits between the slash and the last 6 digits are the log segment value. If there are no digits between the slash and the last 6 hex digits, the log segment value is simply 0. If the checkpoint location is 2/320 and the timeline id is 1, the corresponding WAL is 00010002 Sorry, typo. The corresponding WAL for a checkpoint location of 2/320 is 000100020003 Brian Wipf ClickSpace Interactive Inc. [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: Re : [GENERAL] Abbreviation list
On 18/10/2007 22:26, Laurent ROCHE wrote: No idea what GUC is, though ! Grand Unified Contraption? ;-) Ray (who's just been reading Jules Verne). --- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] --- ---(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] XMIN semantic at peril ?
On Thu, Oct 11, 2007 at 12:03:47PM -0400, Tom Lane wrote: If in the meantime another writer changed the data we originally read we would detect that by xmin having changed hence no row to be updated. So, yes, there is a *tiny* failure condition: Hmm. I think the failure condition is not what you are thinking: in your example, you'd correctly conclude that some other transaction modified the row. Not really, notice: - original XMIN read, transaction is closed - a lng time passes: - original row gets frozen, XMIN changed to FrozenTransactionId - XMIN wraps - original XMIN is *reused* on the *exact same* original row by another concurrent writer without changing the primary key - the original row is written back to the database with ... where xmin = original_xmin and pk = original_pk ... Now the update succeeds, although the data DID change ! This is a worst-case failure but should be *very* rare. The problem case is - read (a rather old) row including XMIN - VACUUM comes along and decides to set XMIN = FrozenTransactionId - update row with ... where pk = ... and XMIN = old_xmin_from_read - update fails, when there is no need to fail As long as the failure is soft, ie, you recover reasonably, this shouldn't be a big problem. But it's certainly not a scenario you should dismiss as not credible because of timescales. Very true. I documented this in our code and set up a TODO item to switch to a AFTER-trigger updated non-system oplock column. Thanks, Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: Re : Re : [GENERAL] Resetting SEQUENCEs
Laurent ROCHE wrote: Hi, So nobody can help me to write the SELECT that will return the SEQUENCE names, and their linked columns and their linked tables ? Are the system tables documented somewhere ? Of course -- in the internals section. You need pg_class where relkind = 's', pg_depend, and possibly pg_attribute. -- Alvaro Herrera http://www.PlanetPostgreSQL.org/ La naturaleza, tan frágil, tan expuesta a la muerte... y tan viva ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: Re : Re : [GENERAL] pg_dump SERIAL and SEQUENCE
Laurent ROCHE [EMAIL PROTECTED] writes: Not good because in that case pg_dump will generate code that can not run in 8.1 ... like : ALTER SEQUENCE ... OWNED BY Dumps produced by version X of pg_dump have never been guaranteed to load into earlier versions. -Doug ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Group By question
Jeff Lanzarotta wrote: Sam Mason [EMAIL PROTECTED] wrote: On Tue, Oct 16, 2007 at 07:46:34AM -0700, Jeff Lanzarotta wrote: Hello, I have a table that looks something like this: SKU Dept Col1 Col2 Col3 --- -- --- --- -- 1112 3 2123 4 3210 1 4201 2 5241 3 6312 3 I am having a problem trying to get the Is there a query that can do something like this: select sku, dept, (col1 + col2) * col3) from table group by dept What are you expecting the group by to do here? It may be helpful if you show what you expect the output to be. Okay, actually the query is something like: select dept, (col1 + col2) * col3) from table group by dept So, the output would look something like: DeptTotal -- --- 1 26 2 18 3 9 Please don't top-post. The problem may have been that you were selecting SKU (at least, in the first example). But, as you're aggregating the columns, this is impossible. SELECT Dept, SUM((Col1 + Col2) * col3) AS total FROM foo GROUP BY Dept ORDER BY Dept; dept | total --+--- 1 |29 2 |18 3 | 9 (your example had an arithmetic error) brian ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Abbreviation list
I prefer the manual. I would think the list would be pretty long and deal with lots of internals terms. Also the manual has more reference character; as we saw with GUC in this discussion it is still possible to find a CORRECT definition of them. Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 fx 01212-5-13695179 - EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] PG/Tcl and Expect?
am Tue, dem 16.10.2007, um 21:55:38 -0700 mailte Bret Schuhmacher folgendes: Does the PG/Tcl interface allow expect scripts? I want to create a stored procedure that ssh's to another server and runs a Perl script. Expect could do it, but can I load the expect module from pgtcl? I think, you need the untrusted version pl/tclU for such tasks. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Abbreviation list
Steve Crawford [EMAIL PROTECTED] writes: My vote is to add Appendix I. Abbreviations. It seems more like FAQ material than something for the manual. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: Re : [GENERAL] Abbreviation list
Alvaro Herrera [EMAIL PROTECTED] writes: Steve Crawford wrote: Raymond O'Donnell wrote: On 18/10/2007 22:26, Laurent ROCHE wrote: No idea what GUC is, though ! It's Global User Configuration. But the confusion does point out the need for a reference. I think it is Grand Unified Configuration actually ... Alvaro remembers correctly. Peter invented the term when he proposed replacing a bunch of ad-hoc configuration thingies with one mechanism: http://archives.postgresql.org/pgsql-hackers/2000-03/msg00107.php If you don't remember how things worked before that, be glad ;-) regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: Re : [GENERAL] Abbreviation list
Steve Crawford wrote: Raymond O'Donnell wrote: On 18/10/2007 22:26, Laurent ROCHE wrote: No idea what GUC is, though ! Grand Unified Contraption? ;-) Ray (who's just been reading Jules Verne). It's Global User Configuration. But the confusion does point out the need for a reference. I think it is Grand Unified Configuration actually ... what does Verne use? I don't know the reference. -- Alvaro Herrerahttp://www.advogato.org/person/alvherre We are who we choose to be, sang the goldfinch when the sun is high (Sandman) ---(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: Re : [GENERAL] Abbreviation list
Raymond O'Donnell wrote: On 18/10/2007 22:26, Laurent ROCHE wrote: No idea what GUC is, though ! Grand Unified Contraption? ;-) Ray (who's just been reading Jules Verne). It's Global User Configuration. But the confusion does point out the need for a reference. -Steve ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Abbreviation list
Alvaro Herrera wrote: Steve Crawford wrote: Is there a comprehensive (or semi-comprehensive) on-line list of commonly used PG-related abbreviations used in PostgreSQL documentation, mail-lists, etc.? If there is not, would such a list make a reasonable one-page addition to the PG manual? I don't think we have one, but IMHO it would be an excellent idea. We do have some abbreviations, e.g. HOT, TOAST, WAL are the ones that jump immediately to mind (besides the ones you mentioned). My vote is to add Appendix I. Abbreviations. Don't know if it's practical for 8.3 documentation but it would be nice to add even if it only has a few entries as additional ones could be collected via the user notes. I suggest as a discussion starting-point the following inclusion criteria: 1. Any abbreviation/acronym that appears in the PostgreSQL documentation (even if those terms may not be PG specific - we shouldn't assume that everyone knows them). Good documentation practice recommends defining abbreviations the first time they are used. Better still, ensure that they are in the abbreviation list. 2. PostgreSQL-specific abbreviations/acronyms commonly used in on-line conversations, news-releases, notes, etc. even if not (or not yet) in the official documentation. 3. Abbreviations/acronyms that do not meet criteria 1 or 2 but which are likely to clarify PG related conversations (optional, lower priority and within the limitations of avoiding appendix bloat). So BSD, STONITH, DBA, DBMS, RDBMS, GEQO would meet both criteria 1 and criteria 2. GUC and DDL (currently) only meet the second criteria. A starter list mostly gleaned from PG documentation (typing defs quickly and from memory - feel free to make corrections): BKI - Backend Interface CID - Command Identifier CLI - Call Level Interface / Command Line Interface CVE - Common Vulnerabilities and Exposures CVS - Concurrent Version System DBA - Database Administrator DBMS - Database Management System DDL - Data Definition Language DML - Data Manipulation Language DSN - Data Source Name ECPG - Embedded SQL in C ?? GEQO - Genetic Query Optimizer GIN - Generalized Inverted Index GIST - Generalized Search Tree GNU - Gnu's Not Unix GSSAPI - Generic Security Services Application Programming Interface GUC - Global User Configuration HOT - Heap Oriented Tuples IEC - International Electrotechnical Commission ISO - International Standards Organization JDBC - Java Database Connectivity JRT - Java Routines and Types LDAP - Lightweight Directory Access Protocol MED - Management of External Data MVCC - Multi Version Concurrency Control NLS - Natural Language Support ODBC - Open Database Connectivity OID - Object Identifier OLAP - Online Analytical Processing OLB - Object Language Bindings PAM - Pluggable Authentication Modules PITR - Point In Time Recovery POSIX - Portable Operating System Interface PSM - Persistent Stored Modules RPM - Redhat Package Manager SSPI - Security Support Provider Interface SQL - Structured Query Language SSL - Secure Sockets Layer STONITH - Shoot The Other Node In The Head TOAST - The Oversize Attribute Storage Technique UUID - Universally Unique Identifier WAL - Write Ahead Log XID - Transaction Identifier XML - Extensible Markup Language Cheers, Steve ---(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] running postgres
Em Thursday 18 October 2007 14:57:27 brian escreveu: Ken Johansson wrote: Hello, Sorry everyone for the stupid rant. Wont happen again. And thanks for offer assistance. I seem to have it running and once i have a valid question ill post it. Sorry again.. Ken We'll put it down as thinking out loud then. It happens. ;-) OK... But was it harder or easier than MS SQL Server? :-) -- Jorge Godoy [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Abbreviation list
Tom Lane wrote: Steve Crawford [EMAIL PROTECTED] writes: My vote is to add Appendix I. Abbreviations. It seems more like FAQ material than something for the manual. I prefer the manual. I would think the list would be pretty long and deal with lots of internals terms. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(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] Crosstab Problems
Em Thursday 18 October 2007 16:37:59 Joe Conway escreveu: Tom Lane wrote: so it's trying to pstrdup a null result from SPI_getvalue. Obviously it shouldn't crash, but I'm not sure what it *should* do in this case. Joe? The row is pretty useless without a rowid in this context -- it seems like the best thing to do would be to skip those rows entirely. Of course you could argue I suppose that it ought to throw an ERROR and bail out entirely. Maybe a good compromise would be to skip the row but throw a NOTICE? If I were using it and having this problem I'd rather have an ERROR. It isn't uncommon for people not look at their logs and it isn't uncommon for them just run command from some language using a database adapter that might not return the NOTICE output. The ERROR wouldn't pass unnoticed. -- Jorge Godoy [EMAIL PROTECTED] ---(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] Crosstab Problems
Jorge Godoy [EMAIL PROTECTED] writes: Em Thursday 18 October 2007 16:37:59 Joe Conway escreveu: The row is pretty useless without a rowid in this context -- it seems like the best thing to do would be to skip those rows entirely. Of course you could argue I suppose that it ought to throw an ERROR and bail out entirely. Maybe a good compromise would be to skip the row but throw a NOTICE? If I were using it and having this problem I'd rather have an ERROR. I can think of four reasonably credible alternatives: 1. Treat NULL rowid as a category in its own right. This would conform with the behavior of GROUP BY and DISTINCT, for instance. 2. Throw an ERROR if NULL rowid is seen. 3. Throw a NOTICE or WARNING (hopefully only one message not repeated ones) if NULL rowid is seen, then ignore the row. 4. Silently ignore rows with NULL rowid. Not being a heavy user of crosstab(), I'm not sure which of these is the most appropriate, but #1 seems the most defensible from a theoretical perspective. Since the bug has gone undiscovered this long, it seems obvious that not too many people actually try to feed null rowids to crosstab; so expending a lot of effort to fix it is probably not reasonable. If you don't like #1 I'd vote for #2 second. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Crosstab Problems
Tom Lane wrote: Jorge Godoy [EMAIL PROTECTED] writes: Em Thursday 18 October 2007 16:37:59 Joe Conway escreveu: The row is pretty useless without a rowid in this context -- it seems like the best thing to do would be to skip those rows entirely. Of course you could argue I suppose that it ought to throw an ERROR and bail out entirely. Maybe a good compromise would be to skip the row but throw a NOTICE? If I were using it and having this problem I'd rather have an ERROR. I can think of four reasonably credible alternatives: 1. Treat NULL rowid as a category in its own right. This would conform with the behavior of GROUP BY and DISTINCT, for instance. 2. Throw an ERROR if NULL rowid is seen. Not being a heavy user of crosstab(), I'm not sure which of these is the most appropriate, but #1 seems the most defensible from a theoretical perspective. Since the bug has gone undiscovered this long, it seems obvious that not too many people actually try to feed null rowids to crosstab; so expending a lot of effort to fix it is probably not reasonable. If you don't like #1 I'd vote for #2 second. Hadn't really thought about #1, but now that you mention it, it does make sense. #1 gets my vote too. I'll pick this up next week if that's OK. Joe ---(end of broadcast)--- TIP 6: explain analyze is your friend
