[GENERAL] Optimize the query, help me please.
Query is: SELECT * FROM a UNION SELECT * FROM b ORDER BY time DESC LIMIT 100 how can i get only last 100 row from a and b and then do union. Explain of select said that all recond selected from a and b. thanks.
Re: [GENERAL] Optimize the query, help me please.
On 23/11/2011 10:56, Капралов Александр wrote: Query is: SELECT * FROM a UNION SELECT * FROM b ORDER BY time DESC LIMIT 100 how can i get only last 100 row from a and b and then do union. Explain of select said that all recond selected from a and b. (select * from a order by time desc limit 100) union (select * from b order by time desc limit 100) order by time desc limit 100; BTW time is a reserved word AFAIK, so you should enclose it in double-quotes or call the column something else. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Optimize the query, help me please.
Hi, (SELECT * FROM a limit 10) union (SELECT * FROM b limit 10) is what you need Bèrto 2011/11/23 Капралов Александр alnk...@gmail.com Query is: SELECT * FROM a UNION SELECT * FROM b ORDER BY time DESC LIMIT 100 how can i get only last 100 row from a and b and then do union. Explain of select said that all recond selected from a and b. thanks. -- == If Pac-Man had affected us as kids, we'd all be running around in a darkened room munching pills and listening to repetitive music.
Re: [GENERAL] Optimize the query, help me please.
Στις Wednesday 23 November 2011 12:56:23 ο/η Капралов Александр έγραψε: Query is: SELECT * FROM a UNION SELECT * FROM b ORDER BY time DESC LIMIT 100 how can i get only last 100 row from a and b and then do union. Explain of select said that all recond selected from a and b. In order to get 100 last rows from a and 100 last rows from b do: (SELECT * FROM a ORDER BY time LIMIT 100) UNION ALL (SELECT * FROM b ORDER BY time LIMIT 100); omitting the ALL modifier, you may end up with less rows, since UNION normally returns only distinct rows. thanks. -- Achilleas Mantzios -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] In which catalog postgres Instance Crash time recorded ?
Respected All, Today on my local box, I observed crash and when I started Instance my beginning line of new pg_log's look like this. 2011-11-23 17:18:14 IST [2958]: [1-1] LOG: database system was interrupted; last known up at *2011-11-23 00:45:43 IST* 2011-11-23 17:18:14 IST [2958]: [2-1] LOG: database system was not properly shut down; automatic recovery in progress 2011-11-23 17:18:14 IST [2958]: [3-1] LOG: redo starts at 16/C260C028 2011-11-23 17:18:15 IST [2958]: [4-1] LOG: record with zero length at 16/C3609308 2011-11-23 17:18:15 IST [2958]: [5-1] LOG: redo done at 16/C36092D8 2011-11-23 17:18:15 IST [2958]: [6-1] LOG: last completed transaction was at log time 2011-11-23 00:49:21.225383+05:30 Its always there very much information in log's about the crash, and what went wrong etc., But my question is, where that crash time is recorded by Postgres, is it in any catalog or in any file ? Please assist me on this. Thanks in advance. --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
[GENERAL] Пользователь Капралов Александр хочет пообщаться с вами
--- Пользователь Капралов Александр хочет общаться с Вами, используя новейшие продукты Google. Если у Вас уже установлен Gmail или Google Talk, перейдите на страницу http://mail.google.com/mail/b-f536b5aea-955d3fea97-i7pB5IcEv3GSXgU7-jSFxk-Cn70. Нажмите на эту ссылку, чтобы общаться в чате с пользователем Капралов Александр . Чтобы получить бесплатный аккаунт Gmail, электронной почты от Google объемом более 2800 мегабайт, и начать общаться с пользователем Капралов Александр, откройте страницу http://mail.google.com/mail/a-f536b5aea-955d3fea97-i7pB5IcEv3GSXgU7-jSFxk-Cn70. Функции Gmail: обмен мгновенными сообщениями прямо в окне Gmail; эффективная защита от спама; встроенный поиск по сообщениям и удобный способ организации писем в виде цепочек; никаких всплывающих окон и случайных баннеров – только текстовые объявления и релевантная информация, связанная с содержанием ваших сообщений. Эти возможности предоставляются бесплатно. Однако это еще не все! Создавая аккаунт Gmail, Вы также получаете доступ к Google Talk, службе обмена мгновенными сообщениями Google: http://www.google.com/talk/intl/ru/. Функции Google Talk: веб-чат, которым можно пользоваться где угодно, не загружая специальные программы; список контактов, который синхронизируется с Вашим аккаунтом Gmail; бесплатный голосовой чат с высоким качеством между компьютерами, на которых установлен клиент Google Talk. Поскольку мы постоянно работаем над созданием новых функций и совершенствованием службы Gmail, нам могут понадобиться Ваши отзывы и комментарии. С Вашей помощью мы сможем сделать свои продукты еще лучше. Спасибо! Команда Google Дополнительную информацию о Gmail и Google Talk можно найти на следующей странице: http://mail.google.com/mail/help/intl/ru/about.html http://www.google.com/talk/intl/ru/about.html. Если ссылки в этом сообщении не работают, скопируйте и вставьте их в адресную строку браузера. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Пользователь Капралов Александр хочет пообщаться с вами
--- Пользователь Капралов Александр хочет общаться с Вами, используя новейшие продукты Google. Если у Вас уже установлен Gmail или Google Talk, перейдите на страницу http://mail.google.com/mail/b-f536b5aea-9238018186--lZdHgynL2Qd2ab_p5Vp21rZby8. Нажмите на эту ссылку, чтобы общаться в чате с пользователем Капралов Александр . Чтобы получить бесплатный аккаунт Gmail, электронной почты от Google объемом более 2800 мегабайт, и начать общаться с пользователем Капралов Александр, откройте страницу http://mail.google.com/mail/a-f536b5aea-9238018186--lZdHgynL2Qd2ab_p5Vp21rZby8. Функции Gmail: обмен мгновенными сообщениями прямо в окне Gmail; эффективная защита от спама; встроенный поиск по сообщениям и удобный способ организации писем в виде цепочек; никаких всплывающих окон и случайных баннеров – только текстовые объявления и релевантная информация, связанная с содержанием ваших сообщений. Эти возможности предоставляются бесплатно. Однако это еще не все! Создавая аккаунт Gmail, Вы также получаете доступ к Google Talk, службе обмена мгновенными сообщениями Google: http://www.google.com/talk/intl/ru/. Функции Google Talk: веб-чат, которым можно пользоваться где угодно, не загружая специальные программы; список контактов, который синхронизируется с Вашим аккаунтом Gmail; бесплатный голосовой чат с высоким качеством между компьютерами, на которых установлен клиент Google Talk. Поскольку мы постоянно работаем над созданием новых функций и совершенствованием службы Gmail, нам могут понадобиться Ваши отзывы и комментарии. С Вашей помощью мы сможем сделать свои продукты еще лучше. Спасибо! Команда Google Дополнительную информацию о Gmail и Google Talk можно найти на следующей странице: http://mail.google.com/mail/help/intl/ru/about.html http://www.google.com/talk/intl/ru/about.html. Если ссылки в этом сообщении не работают, скопируйте и вставьте их в адресную строку браузера. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] In which catalog postgres Instance Crash time recorded ?
On Wed, Nov 23, 2011 at 7:35 PM, Raghavendra raghavendra@enterprisedb.com wrote: Respected All, Today on my local box, I observed crash and when I started Instance my beginning line of new pg_log's look like this. 2011-11-23 17:18:14 IST [2958]: [1-1] LOG: database system was interrupted; last known up at *2011-11-23 00:45:43 IST* 2011-11-23 17:18:14 IST [2958]: [2-1] LOG: database system was not properly shut down; automatic recovery in progress 2011-11-23 17:18:14 IST [2958]: [3-1] LOG: redo starts at 16/C260C028 2011-11-23 17:18:15 IST [2958]: [4-1] LOG: record with zero length at 16/C3609308 2011-11-23 17:18:15 IST [2958]: [5-1] LOG: redo done at 16/C36092D8 2011-11-23 17:18:15 IST [2958]: [6-1] LOG: last completed transaction was at log time 2011-11-23 00:49:21.225383+05:30 Its always there very much information in log's about the crash, and what went wrong etc., But my question is, where that crash time is recorded by Postgres, is it in any catalog or in any file ? Please assist me on this. Thanks in advance. --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/ 2011-11-23 17:18:14 IST [2958]: [1-1] LOG: database system was interrupted; last known up at *2011-11-23 00:45:43 IST* * * Just to clear my question, am looking for this last known up at 2011-11-23 00:45:43 IST line information recorded in any pg_catalogs ? --Raghav
Re: [GENERAL] In which catalog postgres Instance Crash time recorded ?
On Wed, Nov 23, 2011 at 16:59, Raghavendra raghavendra@enterprisedb.com wrote: Just to clear my question, am looking for this last known up at 2011-11-23 00:45:43 IST line information recorded in any pg_catalogs ? How would it read that from a catalog if the cluster didn't recover from the crash yet? I'd think it's looking at last-modified timestamps of some of the PGDATA files instead. -- Alex -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] In which catalog postgres Instance Crash time recorded ?
On Wed, Nov 23, 2011 at 8:35 PM, Alex Shulgin alex.shul...@gmail.comwrote: On Wed, Nov 23, 2011 at 16:59, Raghavendra raghavendra@enterprisedb.com wrote: Just to clear my question, am looking for this last known up at 2011-11-23 00:45:43 IST line information recorded in any pg_catalogs ? How would it read that from a catalog if the cluster didn't recover from the crash yet? Right. Am expecting too much. I should have guessed this.. :) I'd think it's looking at last-modified timestamps of some of the PGDATA files instead. Is it from PGDATA/pg_clog ... --- Raghav
Re: [GENERAL] In which catalog postgres Instance Crash time recorded ?
Alex Shulgin alex.shul...@gmail.com writes: How would it read that from a catalog if the cluster didn't recover from the crash yet? I'd think it's looking at last-modified timestamps of some of the PGDATA files instead. No, it's looking at a last-update timestamp field in pg_control. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] In which catalog postgres Instance Crash time recorded ?
On Wed, Nov 23, 2011 at 9:01 PM, Tom Lane t...@sss.pgh.pa.us wrote: Alex Shulgin alex.shul...@gmail.com writes: How would it read that from a catalog if the cluster didn't recover from the crash yet? I'd think it's looking at last-modified timestamps of some of the PGDATA files instead. No, it's looking at a last-update timestamp field in pg_control. regards, tom lane Thanks Tom. So, Is it a approximate time of crash on the basis of last-modified timestamp of pg_control file ? --Raghav
[GENERAL] pg_dump of a v8.3.4 - v9.0.1
Hi: I have a pg_dump pf a v8.3.4 DB that I would like to bring into a v9.0.1 PG instance. What is the best way to do this? Note, the dump file is too big to fit into an editor, but I could sed it if need be. Thanks.
Re: [GENERAL] pg_dump of a v8.3.4 - v9.0.1
On 11/23/2011 08:08 AM, Gauthier, Dave wrote: Hi: I have a pg_dump pf a v8.3.4 DB that I would like to bring into a v9.0.1 PG instance. What is the best way to do this? Note, the dump file is too big to fit into an editor, but I could sed it if need be. Did you do the data dump using the 8.3.4 version of pg_dump or the 9.0.1? If you used the newer version the dump format should not cause you any problems. What you will need to do is read the release notes for the version 8.4 and 9.0 for any changes that may affect your data once it is in the database. Thanks. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_dump of a v8.3.4 - v9.0.1
I used pg_dump v 8.3.4, but I could rerun using v9.0.1. I was thinking that pg_dump v9 would refuse to play right with the v8 DB. Is that a false assumption? -Original Message- From: Adrian Klaver [mailto:adrian.kla...@gmail.com] Sent: Wednesday, November 23, 2011 11:48 AM To: Gauthier, Dave Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] pg_dump of a v8.3.4 - v9.0.1 On 11/23/2011 08:08 AM, Gauthier, Dave wrote: Hi: I have a pg_dump pf a v8.3.4 DB that I would like to bring into a v9.0.1 PG instance. What is the best way to do this? Note, the dump file is too big to fit into an editor, but I could sed it if need be. Did you do the data dump using the 8.3.4 version of pg_dump or the 9.0.1? If you used the newer version the dump format should not cause you any problems. What you will need to do is read the release notes for the version 8.4 and 9.0 for any changes that may affect your data once it is in the database. Thanks. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_dump of a v8.3.4 - v9.0.1
On 11/23/2011 08:50 AM, Gauthier, Dave wrote: I used pg_dump v 8.3.4, but I could rerun using v9.0.1. I was thinking that pg_dump v9 would refuse to play right with the v8 DB. Is that a false assumption? Yes. http://www.postgresql.org/docs/9.0/interactive/app-pgdump.html Because pg_dump is used to transfer data to newer versions of PostgreSQL, the output of pg_dump can be expected to load into PostgreSQL server versions newer than pg_dump's version. pg_dump can also dump from PostgreSQL servers older than its own version. (Currently, servers back to version 7.0 are supported.) However, pg_dump cannot dump from PostgreSQL servers newer than its own major version; it will refuse to even try, rather than risk making an invalid dump. Also, it is not guaranteed that pg_dump's output can be loaded into a server of an older major version — not even if the dump was taken from a server of that version. Loading a dump file into an older server may require manual editing of the dump file to remove syntax not understood by the older server. Thanks. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] In which catalog postgres Instance Crash time recorded ?
Raghavendra raghavendra@enterprisedb.com writes: On Wed, Nov 23, 2011 at 9:01 PM, Tom Lane t...@sss.pgh.pa.us wrote: No, it's looking at a last-update timestamp field in pg_control. So, Is it a approximate time of crash on the basis of last-modified timestamp of pg_control file ? IIRC, that's going to be the time of last checkpoint completion on a normally-operating server. So depending on your checkpoint settings, and how busy the server is, it might or might not be very close to the time of crash. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] In which catalog postgres Instance Crash time recorded ?
On Wed, Nov 23, 2011 at 10:27 PM, Tom Lane t...@sss.pgh.pa.us wrote: Raghavendra raghavendra@enterprisedb.com writes: On Wed, Nov 23, 2011 at 9:01 PM, Tom Lane t...@sss.pgh.pa.us wrote: No, it's looking at a last-update timestamp field in pg_control. So, Is it a approximate time of crash on the basis of last-modified timestamp of pg_control file ? IIRC, that's going to be the time of last checkpoint completion on a normally-operating server. So depending on your checkpoint settings, and how busy the server is, it might or might not be very close to the time of crash. regards, tom lane Perfect. I understood. Thanks for explaining Tom... :) --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/
[GENERAL] Blank Numeric Column For INSERT
I am trying to load 143K rows into a postgres-9.0.5 table from an ASCII text file. The file consists of INSERT INTO ... statements and the VALUES are comma delimited. One column is numeric (REAL), but ~10K rows have that value missing, and postgres rejects the lines. The column does not have a NOT NULL constraint. The command line I use is 'psql -d database_name -f wq.sql'. Originally I had two commas in sequence since there were no values between them. Next I tried a space between the two commas. I tried searching in the 9.0.5 manual for 'missing values', 'missing', and another term I don't recall but found nothing. An example: psql:wq.sql:8121: ERROR: syntax error at or near , LINE 1: ...its) VALUES (214,'SW-6','1992-11-25','oil_grease', ,'mg/L'); What is the approprate way to represent the missing column? TIA, Rich -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] On naming attributes in a WITH clause
Hello,
Is it possible to rename columns in a WITH clause which includes VALUES?
None of the following parse:
WITH map_table AS
(
VALUES ('1','r'),('2','w'),('3','a'),('4','d')
) AS (a,b)
SELECT * FROM map_table;
WITH map_table AS
(
VALUES ('1','r'),('2','w'),('3','a'),('4','d') AS t (a,b)
)
SELECT * FROM map_table;
I would like the map_table columns to have names other than column1 and column2.
WITH map_table AS
(
VALUES ('1','r'),('2','w'),('3','a'),('4','d')
)
SELECT column1 AS a, column2 AS b FROM map_table;
is close but I would prefer to rename the columns as part of the WITH clause
before the (poorly) named column1 and column2 hit the main query. Is it
possible?
Cheers,
M
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] On naming attributes in a WITH clause
It is described in the SELECT documentation WITH section but is somewhat
obscure; just add the column names, in (), after the WITH name.
WITH name (col1, col2, col3) AS (
SELECT 1, 2, 3
)
David J.
-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of AM
Sent: Wednesday, November 23, 2011 1:46 PM
To: Postgres General
Subject: [GENERAL] On naming attributes in a WITH clause
Hello,
Is it possible to rename columns in a WITH clause which includes VALUES?
None of the following parse:
WITH map_table AS
(
VALUES ('1','r'),('2','w'),('3','a'),('4','d')
) AS (a,b)
SELECT * FROM map_table;
WITH map_table AS
(
VALUES ('1','r'),('2','w'),('3','a'),('4','d') AS t (a,b)
)
SELECT * FROM map_table;
I would like the map_table columns to have names other than column1 and
column2.
WITH map_table AS
(
VALUES ('1','r'),('2','w'),('3','a'),('4','d')
)
SELECT column1 AS a, column2 AS b FROM map_table;
is close but I would prefer to rename the columns as part of the WITH clause
before the (poorly) named column1 and column2 hit the main query. Is it
possible?
Cheers,
M
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Blank Numeric Column For INSERT
On Wed, Nov 23, 2011 at 10:33 AM, Rich Shepard rshep...@appl-ecosys.com wrote: Originally I had two commas in sequence since there were no values between them. Next I tried a space between the two commas. I tried searching in the 9.0.5 manual for 'missing values', 'missing', and another term I don't recall but found nothing. My pg.dump files show nulls as: \N -- Regards, Richard Broersma Jr. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Blank Numeric Column For INSERT
On Wed, 23 Nov 2011, Richard Broersma wrote: My pg.dump files show nulls as: \N Richard, Mine do, too. But, that's not what postgres wants to see in the .sql file. It takes it as a newline (\n) whether quoted or not. Thanks, Rich -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Blank Numeric Column For INSERT
Rich Shepard rshep...@appl-ecosys.com writes: Mine do, too. But, that's not what postgres wants to see in the .sql file. In an insert command, you need to either write NULL or omit the column from the column list; empty expressions aren't syntactically correct. (Note that the latter option actually results in inserting the column's default, not necessarily null...) regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PGError: ERROR: could not open relation with OID?
Running Postgresql 9.0.5 on Ubuntu 10.10, we just saw: PGError: ERROR: could not open relation with OID 39008 SELECT questions.*, r.relevance as score, r.explanation as explanation FROM questions INNER JOIN users ON users.id = questions.user_id JOIN relevance(13218) AS r ON questions.id = r.question_id WHERE (questions.user_id != 467) AND (questions.user_id != 13218) ORDER BY r.relevance DESC LIMIT 10 OFFSET 0 This same query run manually from psql doesn't yield any error, so it must have been a transitive thing. We use no explicit locking statements, we weren't creating or dropping tables and haven't in the past week or so, and we have no replication, sharding or partitioning set up - it's pretty much a stock single-machine install, save for the typical postgresql.conf RAM tweaking. Our volume is fairly low, the tables are fairly small, we have complete logs. How can I troubleshoot to see what might have happened? Or is this a known bug in 9.0.5? Jay Levitt -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Blank Numeric Column For INSERT
On Wed, 23 Nov 2011, Tom Lane wrote: In an insert command, you need to either write NULL or omit the column from the column list; empty expressions aren't syntactically correct. (Note that the latter option actually results in inserting the column's default, not necessarily null...) Tom, I must have written Null rather than NULL yesterday evening. That's why it didn't work for me. Must be some other glitches but they scroll up too quickly to read. I'll fix those next. Many thanks. Happy Thanksgiving. Rich -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Compiler does not detect support for 64 bit integers
I'm trying to compile a parser for full text searching starting from the code in this example: http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/HOWTO-parser-tsearch2.html I'm using Code::block with MinGW on a Windows Seven x64 and I get these errors: ..\..\..\Program Files\PostgreSQL\9.0\include\server\c.h|284|error: #error must have a working 64-bit integer datatype| ..\..\..\Program Files\PostgreSQL\9.0\include\server\postgres.h|550|error: expected ')' before 'X'| ||=== Build finished: 2 errors, 0 warnings ===| Compiler detects errors in Postgresql's header files and I do not know how to solve them. Any suggestion? Thanks in advance, Antonio -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] plpgsql Difference in behaviour between versions?
Hi, I am upgrading a system from postgresql 8.4.8 (fedora 13 x64) to postgresql 9.0.5 (fedora 15 x64). As I build a database I've noticed that the following works on 8.4.8 but does not work on 9.0.5. Can someone tell me why this is not legal syntax in 9.0.5 but is legal in 8.4.8 please: ===snip=== create type mytype as ( somekey integer, open numeric(14, 2) ); CREATE OR REPLACE FUNCTION myfunc(IN INTEGER, IN NUMERIC(14, 2)) RETURNS VOID AS $$ DECLARE somekey ALIAS FOR $1; rec mytype; BEGIN rec.somekey = somekey; -- 9.0.5 will fail at the dot character in the -- following line here with syntax error sqlstate 42601 -- but 8.4.8 is happy. rec.open = 32; -- ^ RETURN; END; $$ LANGUAGE plpgsql; ===snip=== -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_standby for postgresql8.2
Hi Robert, Thanks for ur views, I got an advice from a DB professional Raghavendra Rao as below it worked From your contrib/pg_standby location you need to first set the path for pg_config and do ./configure or directly make, make install. This will create pg_standby executable in postgresql/8.2/bin... Go through the README file in the pg_standby contrib location which will brief you about the installation procedure. Mehdi On 11/23/2011 04:16 AM, Robert Treat wrote: On Tue, Nov 22, 2011 at 4:09 AM, khizerkhi...@srishtisoft.com wrote: Hi, May i know how to install pg_standby for postgresql8.2 in ubuntu 10.10 OS I copied the pg_standby folder for compilation which has the files pg_standby.c, Makefile initially i tried with make, make install inside contrip/pg_standby folder but i got an err Makefile.global no such file r directory ... so i compiled and reinstalled postgresql8.2 but not able to find pg_standby, How can i solve this guys? IIRC, the way to do this is to compile 8.3 for your platform, including the pg_standby contrib module, and then just copy the binary over to you system. Since it doesn't integrate directly, it will work against 8.2, save for the %r macro (for removing unneeded xlog segments). If you really need the %r capabilities, you might want to look at OmniPITR, which doesn't require compiling and implements that feature. Robert Treat play: xzilla.net work: omniti.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpgsql Difference in behaviour between versions?
Hello http://developer.postgresql.org/pgdocs/postgres/release-9-0.html PL/pgSQL no longer allows variable names that match certain reserved words (Tom Lane) use double quotes rec.open = 32; Regards Pavel Stehule 2011/11/23 Chris McDonald chrisjonmcdon...@gmail.com: Hi, I am upgrading a system from postgresql 8.4.8 (fedora 13 x64) to postgresql 9.0.5 (fedora 15 x64). As I build a database I've noticed that the following works on 8.4.8 but does not work on 9.0.5. Can someone tell me why this is not legal syntax in 9.0.5 but is legal in 8.4.8 please: ===snip=== create type mytype as ( somekey integer, open numeric(14, 2) ); CREATE OR REPLACE FUNCTION myfunc(IN INTEGER, IN NUMERIC(14, 2)) RETURNS VOID AS $$ DECLARE somekey ALIAS FOR $1; rec mytype; BEGIN rec.somekey = somekey; -- 9.0.5 will fail at the dot character in the -- following line here with syntax error sqlstate 42601 -- but 8.4.8 is happy. rec.open = 32; -- ^ RETURN; END; $$ LANGUAGE plpgsql; ===snip=== -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpgsql Difference in behaviour between versions?
Chris McDonald chrisjonmcdon...@gmail.com writes: Hi, I am upgrading a system from postgresql 8.4.8 (fedora 13 x64) to postgresql 9.0.5 (fedora 15 x64). As I build a database I've noticed that the following works on 8.4.8 but does not work on 9.0.5. Can someone tell me why this is not legal syntax in 9.0.5 but is legal in 8.4.8 please: ===snip=== create type mytype as ( somekey integer, open numeric(14, 2) ); CREATE OR REPLACE FUNCTION myfunc(IN INTEGER, IN NUMERIC(14, 2)) RETURNS VOID AS $$ DECLARE somekey ALIAS FOR $1; rec mytype; BEGIN rec.somekey = somekey; -- 9.0.5 will fail at the dot character in the -- following line here with syntax error sqlstate 42601 -- but 8.4.8 is happy. rec.open = 32; Hmmm, I do not see that open is a reserved word but the PL must be treating it special somehow. See where I've added quotes above which gets it working on my 9.1 instance. HTH -- ^ RETURN; END; $$ LANGUAGE plpgsql; ===snip=== -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 305.321.1144 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Compiler does not detect support for 64 bit integers
Antonio Franzoso antoniofranz...@yahoo.it writes: I'm using Code::block with MinGW on a Windows Seven x64 and I get these errors: ..\..\..\Program Files\PostgreSQL\9.0\include\server\c.h|284|error: #error must have a working 64-bit integer datatype| Um ... did you run the configure script? In a MinGW environment the build process is basically like Unix, it's not the MSVC way. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpgsql Difference in behaviour between versions?
Chris McDonald chrisjonmcdon...@gmail.com writes: Hi, I am upgrading a system from postgresql 8.4.8 (fedora 13 x64) to postgresql 9.0.5 (fedora 15 x64). As I build a database I've noticed that the following works on 8.4.8 but does not work on 9.0.5. Can someone tell me why this is not legal syntax in 9.0.5 but is legal in 8.4.8 please: ===snip=== create type mytype as ( somekey integer, open numeric(14, 2) ); CREATE OR REPLACE FUNCTION myfunc(IN INTEGER, IN NUMERIC(14, 2)) RETURNS VOID AS $$ DECLARE somekey ALIAS FOR $1; rec mytype; BEGIN rec.somekey = somekey; -- 9.0.5 will fail at the dot character in the -- following line here with syntax error sqlstate 42601 -- but 8.4.8 is happy. rec.open = 32; I wonder if this remark in the release notes is relevant. Tom will probably shed some light here. I don't get the connection but we'll see. * Improve handling of cases where PL/pgSQL variable names conflict with identifiers used in queries within a function (Tom Lane) The default behavior is now to throw an error when there is a conflict, so as to avoid surprising behaviors. This can be modified, via the configuration parameter plpgsql.variable_conflict or the per-function option # variable_conflict, to allow either the variable or the query-supplied column to be used. In any case PL/pgSQL will no longer attempt to substitute variables in places where they would not be syntactically valid. RETURN; END; $$ LANGUAGE plpgsql; ===snip=== -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consult...@comcast.net p: 305.321.1144 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Permission Problems
I'm trying to put together a very simple web application to display information about any table in my database that a web user wants to see. The general idea is to present a list of schemata and their associated tables to the user who then picks one, which causes the server to send the list of fields in that table. Pretty simple. My thought was to use an internal, low-privileged account to do the look-up and pass the requested info back to the client, but I think I'm running into permission problems and I don't know where to add all the GRANTS so that the go-between account has enough privilege to do its job, but not more. I'm getting the list of schemata from the information_schema.schemata table, and using my superuser account it works fine. However, using the account I've set up for this job isn't getting very far and I'm getting nothing returned. I've granted permissions for SELECT and REFERENCES on all of my tables, and granted USEAGE on the schemas including information_schema and pg_catalog and the relevant views; I've even granted execute priv on the functions used in the view, but I'm still not getting results for this psuedo user, even though the SQL selection works fine for my account. I'd prefer not to just hand out a superuser privilege to the database go-between because my purpose is to keep this db-web interface role's reach short. Am I digging too deep here, or what am I missing? Is there a better way to tranfer info between my database and the web than by using a generic account? It sure seems like I'm granting too much access to too little a player. Any advice would be welcome. TIA, - Bill Thoen
Re: [GENERAL] Permission Problems
On 11/23/2011 01:54 PM, Bill Thoen wrote: Am I digging too deep here, or what am I missing? Is there a better way to tranfer info between my database and the web than by using a generic account? It sure seems like I'm granting too much access to too little a player. Any advice would be welcome. If you are just letting people view, I would create views of what is allowed to be viewed and grant read permission to just the views to the unprivalged account. JD TIA, - Bill Thoen -- Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Support, Training, Professional Services and Development The PostgreSQL Conference - http://www.postgresqlconference.org/ @cmdpromptinc - @postgresconf - 509-416-6579 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpgsql Difference in behaviour between versions?
Jerry Sievers gsiever...@comcast.net writes: Hmmm, I do not see that open is a reserved word but the PL must be treating it special somehow. plpgsql has a different list of reserved words than the main SQL grammar does. I don't think we explicitly document it anywhere, but pretty much any keyword that can start a plpgsql command is considered reserved by plpgsql (since otherwise it'd be ambiguous against an assignment to a plpgsql variable of the same name). regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Permission Problems
Am 23.11.2011 22:54, schrieb Bill Thoen: I'm getting the list of schemata from the information_schema.schemata table, and using my superuser account it works fine. However, using the account I've set up for this job isn't getting very far and I'm getting nothing returned. I've granted permissions for SELECT and REFERENCES on all of my tables, and granted USEAGE on the schemas including information_schema and pg_catalog and the relevant views; I've even granted execute priv on the functions used in the view, but I'm still not getting results for this psuedo user, even though the SQL selection works fine for my account. I'd prefer not to just hand out a superuser privilege to the database go-between because my purpose is to keep this db-web interface role's reach short. The information schema gives you only access to objects the user has usage rights for. That means you have to grant rights for the objects you want to list for your unprivileged user. If you want these information without these restrictions you must use the tables of the pg_catalog schema. Sven -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpgsql Difference in behaviour between versions?
OK, I see it is the term open which fails the syntax checker - I guessed this might be because open is a reserved word but http://www.postgresql.org/docs/9.0/interactive/sql-keywords-appendix.html does not indicate whether open is either reserved or not in postgresql. Checking 8.4 doco, http://www.postgresql.org/docs/8.4/static/sql-keywords-appendix.html has the same detail when it comes to 'reservedness' of the term open. Still interested in a definitive answer, but the fix for me appears to be simply to change the word open to something else. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Installed. Now what?
On Tue, Nov 22, 2011 at 2:13 AM, Steve Crawford scrawf...@pinpointresearch.com wrote: .. The information in the pgbouncer pseudo-database is helpful, here (psql -U youradminuser -h 127.0.0.1 pgbouncer). Thanks, I finally got it connecting. Where's the pgbouncer database. Do I need to install it? It's not installed. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpgsql Difference in behaviour between versions?
On Wednesday, November 23, 2011 11:43:04 am Chris McDonald wrote: OK, I see it is the term open which fails the syntax checker - I guessed this might be because open is a reserved word but http://www.postgresql.org/docs/9.0/interactive/sql-keywords-appendix.html does not indicate whether open is either reserved or not in postgresql. The relevant part from above is: As a general rule, if you get spurious parser errors for commands that contain any of the listed key words as an identifier you should try to quote the identifier to see if the problem goes away. Still interested in a definitive answer, but the fix for me appears to be simply to change the word open to something else. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Installed. Now what?
On Thu, Nov 24, 2011 at 9:18 AM, Phoenix Kiula phoenix.ki...@gmail.com wrote: On Tue, Nov 22, 2011 at 2:13 AM, Steve Crawford ... Thanks, I finally got it connecting. Where's the pgbouncer database. Do I need to install it? It's not installed. (How else should I tell the load and utilization?) Also, how can I tell the pgbouncer log not to log proper connections and their closing. Right now it's filling up with nonsense. I only want it to log when there's a warning or error. Thanks! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Installed. Now what?
On Wednesday, November 23, 2011 5:31:10 pm Phoenix Kiula wrote: On Thu, Nov 24, 2011 at 9:18 AM, Phoenix Kiula phoenix.ki...@gmail.com wrote: On Tue, Nov 22, 2011 at 2:13 AM, Steve Crawford ... Thanks, I finally got it connecting. Where's the pgbouncer database. Do I need to install it? It's not installed. (How else should I tell the load and utilization?) It is a virtual database, see below for how to connect: http://pgbouncer.projects.postgresql.org/doc/usage.html#_quick_start Also, how can I tell the pgbouncer log not to log proper connections and their closing. Right now it's filling up with nonsense. I only want it to log when there's a warning or error. http://pgbouncer.projects.postgresql.org/doc/config.html#_log_settings Thanks! -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] plpgsql Difference in behaviour between versions?
Chris McDonald chrisjonmcdon...@gmail.com writes: Still interested in a definitive answer, but the fix for me appears to be simply to change the word open to something else. Well, if you want a definitive answer, you can consult the list of plpgsql reserved words here: http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/pl/plpgsql/src/pl_scanner.c;h=76e8436e50e9c1d4919a60916b0cabfa83a13eb1;hb=HEAD#l30 where you will find that open is indeed a reserved word. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
