Re: [GENERAL] Forward referencing of table aliases in subqueries does not work in 9.1 beta2 ( works in 9.0 and 8.4.2 )
BangarRaju Vadapalli bangarraju.vadapa...@infor.com writes: The select query for creating the view is executing fine in 8.4.2. But not in 9.1 beta2. The issue is forward referencing of table aliases in subqueries is not working fine in 9.1 beta2. This complaint is unintelligible. Please provide a self-contained test case. 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] Postgres service refuses to start on windows
hello ! I having an issue with my postgres database installed on a windows xp. For some reason, I can no longer start the postgres service through pgadmin or the services window. I get the following error: Error 1053: The service did not respond to the start or control request in a timely fashion. Any ideas ? Roman.
Re: [GENERAL] Location Data
* Adarsh Sharma wrote: Today I am facing a simple problem that I fail to solve after 2 day try. I have a places table in database whose structure is as : CREATE TABLE places ( woe_id character varying(15) NOT NULL, iso character varying(6), name text, language character varying(6), place_type character varying, parent_woe_id character varying(15), lat numeric(12,8), lon numeric(12,8) CONSTRAINT places_pkey PRIMARY KEY (woe_id) )WITH ( OIDS=FALSE); It's simple *name *column contains the name of places in a hierarchical order. fore.g *woe_id iso name language places_type parent_woe_id lat lon 1 ZZ Earth ENG Supername 0 13.3445 234.666 10 IN INDIA ENG Country 1 12.44 234.667 11 IN JK ENG State 10 4535.56 3453.77 12 IN Udhanput ENG District 11 1222 3443.8 15 IN Parth ENG Town 12 111.6 1222.5 *I hope U understand what i am trying to explain. Now I want this data in the same table in extra columns fore.g *woe_id iso name language places_type parent_woe_id lat lon town district state country 1 ZZ Earth ENG Supername 0 13.3445 234.666 10 IN INDIA ENG Country 1 12.44 234.667 11 IN JK ENG State 10 4535.56 3453.77 **INDIA* *12 IN Udhanput ENG District 11 1222 3443.8 **JK **INDIA* *15 IN Parth ENG Town 12 111.6 1222.5 **Udhanput **JK **INDIA* Write a set of functions to get the higher-level structures (country for states, etc.) for any given record, and put a trigger on the table that populates the fields on insert and update. Sort of a materialized view. If you have little query activity on the table, create a view that calls the functions. -- Christian -- 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] Location Data
* Adarsh Sharma wrote:
Christian Ullrich wrote:
Write a set of functions to get the higher-level structures (country
for states, etc.) for any given record, and put a trigger on the table
that populates the fields on insert and update.
All the world data is populated in the places table. Now I don't think
insert occurs anymore now.
Then add the new fields to the table and update them from the function
results.
Sort of a materialized view. If you have little query activity on the
table, create a view that calls the functions.
yes a function is must needed for this problem, bt any idea about the
flow of the function.
Simple version:
create function get_ancestor(p_woe_id varchar, p_level varchar) returns
text language plpgsql as $$
declare
v_woe_id varchar;
v_name text;
v_place_type varchar;
v_parent varchar;
begin
select woe_id, place_type, parent_woe_id into v_woe_id, v_place_type,
v_parent from places where woe_id = p_woe_id;
if (not found or v_place_type = p_level) then
return null;
else
while (found and v_place_type != p_level) loop
select name, place_type, parent_woe_id into v_name,
v_place_type, v_parent from places where woe_id = v_parent;
end loop;
if found then
return v_name;
else
return null;
end if;
end if;
end;$$;
select get_ancestor('15', 'State') as state, get_ancestor('15',
'District') as district;
You could produce a more refined version using WITH RECURSIVE, but if
your table is already complete and all you need is to put in the
denormalized data, this will work just as well. Beware of cyclical
references.
--
Christian
--
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] Postgres service refuses to start on windows
On 20/06/11 14:45, Roman Isitua wrote: hello ! I having an issue with my postgres database installed on a windows xp. For some reason, I can no longer start the postgres service through pgadmin or the services window. I get the following error: Error 1053: The service did not respond to the start or control request in a timely fashion. Read this: http://wiki.postgresql.org/wiki/Guide_to_reporting_problems and try again. -- Craig Ringer -- 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] Forward referencing of table aliases in subqueries does not work in 9.1 beta2 ( works in 9.0 and 8.4.2 )
On 20/06/11 13:59, Tom Lane wrote: BangarRaju Vadapalli bangarraju.vadapa...@infor.com writes: The select query for creating the view is executing fine in 8.4.2. But not in 9.1 beta2. The issue is forward referencing of table aliases in subqueries is not working fine in 9.1 beta2. This complaint is unintelligible. Please provide a self-contained test case. In other words: Please provide details, including at least the exact text of the error message you get when you run the query on 9.1 beta 2. I suspect from earlier discussion off-list (while we were trying to get any kind of info on the crash) that he's getting the error: Error: JOIN qualification cannot refer to other relations. when executing that query, and did not get that error in earlier versions of PostgreSQL. Bangar Raju: If at all possible, please put together a small self-contained .SQL file that: - Defines a table - Defines a view of that table - Runs a query against that view that works on 8.4 and 9.0 but fails on 9.1 beta 2. As your post stands, it's very incomplete and hard to answer because the view is complex and refers to lots of tables you haven't provided definitions for. If you just post all your table definitions it'll probably be too complex for anyone to be willing to investigate - for free in their spare time, anyway. You will get the best help if you write a better question and try to cut things out of your test view and query until it is as simple and self-contained as possible. When I suggested that you ask about this on-list, I didn't expect you to re-post your question to me word-for-word. Remember, those on the list don't have the context of our prior conversation, and even with that (which would only tell the the missing error message) they wouldn't know enough to be able to help you. Whenver asking for help on any mailing list, try to read your post before clicking send as if you were someone who didn't know anything about your tables, business, app, environment, etc and only knew what you actually wrote in the email. See if it would make sense to them. Imagine you were trying to answer the question, as if it came from somebody else in another situation you knew nothing about. You will quickly notice pieces of information that you left out, like your operating system, exact database version, table definitions, error messages, etc etc etc. (All this is, of course, in the guide to reporting problems that I linked to before). -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] connection time out
Hello Guys, I have a database server and I have created another replica of it on another machine. The new replica is running fine locally, but I can not access it from another clients, the pg_hba.conf file is the same as the original database server. when I try to connect to the server I get psql: could not connect to server: Connection timed out Is the server running on host xxx and accepting TCP/IP connections on port 5432? I can access the original server from the client I am trying to access the replica, So, I think the problem is not related to postgresql conf. Certainly I can access the replica using ssh and I pinged it and every thing is fine. I am wondering what are the reasons that might cause this problem. Regards
Re: [GENERAL] Port forwarding via iptables to postgres listening locally
I've turned on connection logging in postgres; there is no indication of any connection attempt. There don't seem to be any additional rules configured in iptables that would drop the packets. Are the tables set to drop by default, or allow by default? They are set to drop by default. I'll see if removing those rules is what's preventing this from working. Thanks for your help! - David -- Craig Ringer
Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening
On Mon, Jun 20, 2011 at 5:08 AM, Amitabh Kant amitabhk...@gmail.com wrote:
On Mon, Jun 20, 2011 at 1:43 AM, Alexander Farber
alexander.far...@gmail.com wrote:
Hello Cedric and others,
On Sun, Jun 19, 2011 at 9:56 PM, Cédric Villemain
cedric.villemain.deb...@gmail.com wrote:
2011/6/19 Alexander Farber alexander.far...@gmail.com:
[pgbouncer]
logfile = /var/log/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
listen_port = 6432
unix_socket_dir = /tmp
auth_type = md5
auth_file = /var/lib/pgsql/data/global/pg_auth
pool_mode = transaction
server_check_delay = 10
max_client_conn = 200
default_pool_size = 20
My php script displaying player stats:
http://preferans.de/user.php?id=OK493430777441
will sometimes exit with the PDO error:
SQLSTATE[26000]: Invalid sql statement name:
7 ERROR: prepared statement
pdo_stmt_0016 does not exist
snip
why add a begin/commit if I only
have SELECT statements
there (in the default mode) and
the data isn't critical to me
(just some player statistics and
notes by other players - i.e.
a statistic or note is ok to be lost
occasionally)?
Also I've changed my PHP-script
to non-persistent connections:
$options = array(PDO::ATTR_ERRMODE = PDO::ERRMODE_EXCEPTION);
$db = new PDO(sprintf('pgsql:host=%s port=%u; dbname=%s',
DBHOST, DBPORT, DBNAME), DBUSER, DBPASS, $options);
and restarted Apache 2.2.3,
but that error is still there:
SQLSTATE[26000]: Invalid sql
statement name: 7 ERROR: prepared
statement pdo_stmt_000a does not exist
Regards
Alex
Try setting set server_reset_query = DISCARD ALL; in your pgbouncer
configuration file.
No, in transaction mode the reset_query should actually be empty.
Instead you need to disable use of db-side prepared statements
by adding option PDO::ATTR_EMULATE_PREPARES = true
--
marko
--
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] PostgreSQL 8.4.8 bringing my website down every evening
I've added $db-beginTransaction(); $db-commit(); around _all_ statements, but now get: SQLSTATE[25P02]: In failed sql transaction: 7 ERROR: current transaction is aborted, commands ignored until end of transaction block quite often. I don't understand why would transaction get aborted here... Maybe I should try session mode of pgbouncer again, now that I've got rid of the persistent PHP connections? Regards 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] Forward referencing of table aliases in subqueries does not work in 9.1 beta2 ( works in 9.0 and 8.4.2 )
Hi Tom, Self contained test case attached... When executing the query below in 9.1 beta 2 we are getting the error ERROR: JOIN qualification cannot refer to other relations. The same query is executing fine in 8.4.2 and 9.0. SELECT dbai.dim_base_agg_instr_key AS instr_key, dba.dim_base_key AS basekey, dl.datamart_letter, dba.agg_number AS aggnum, dl.enabled, dba.dim_base_agg_key, dbai.dim_base_name, dbai.dim_base_agg_name, dl.dim_base_agg_letter_key, dl.agg_size FROM dim_base_agg_instr_view dbai, dim_base_agg dba, dim_base_agg_letter dl WHERE dba.dim_base_agg_key = dl.dim_base_agg_key AND dbai.dim_base_key = dba.dim_base_key AND NOT (EXISTS ( SELECT 1 FROM dim_base_agg_instr_def dbaid WHERE dbai.dim_base_agg_instr_key = dbaid.dim_base_agg_instr_key AND NOT (EXISTS ( SELECT 1 FROM dim_base_agg_col dbac WHERE dba.dim_base_agg_key = dbac.dim_base_agg_key AND dbac.dim_col_key = dbaid.dim_col_key; I have tried to find the cause and found out that the dba table alias ( the condition dba.dim_base_agg_key = dbac.dim_base_agg_key in bold and italic above which is nested 2 levels ) in the subquery is not visible from the outer query. As already mentioned we are experiencing a backend crashes in 8.4.2, 9.0 and are trying to use crash dump handler to generate the trace in 9.1 beta2. We have so many views in our application which use select queries like above, so we are looking for any workarounds... Given below the self contained test case... create table a(x int); create table b(x int, y int); create table c(x int, y int, z int); This query fails with ERROR: JOIN qualification cannot refer to other relations - select a.x from a, b where a.x = b.x and not exists ( select 1 from c where b.y = c.y and not exists ( select 1 from c where a.x = c.x)) I suppose other environmental information is not needed here... Thanks, Bangar Raju -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Monday, June 20, 2011 11:30 AM To: BangarRaju Vadapalli Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Forward referencing of table aliases in subqueries does not work in 9.1 beta2 ( works in 9.0 and 8.4.2 ) BangarRaju Vadapalli bangarraju.vadapa...@infor.com writes: The select query for creating the view is executing fine in 8.4.2. But not in 9.1 beta2. The issue is forward referencing of table aliases in subqueries is not working fine in 9.1 beta2. This complaint is unintelligible. Please provide a self-contained test case. regards, tom lane
Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening
On Mon, Jun 20, 2011 at 9:36 AM, Alexander Farber alexander.far...@gmail.com wrote: I've added $db-beginTransaction(); $db-commit(); around _all_ statements, but now get: I don't think that was a good idea. SQLSTATE[25P02]: In failed sql transaction: 7 ERROR: current transaction is aborted, commands ignored until end of transaction block quite often. I don't understand why would transaction get aborted here... Probably some commands were meant to be run in same transaction, or were transaction control commands. Don't do it without synchronizing with business logic... Maybe I should try session mode of pgbouncer again, now that I've got rid of the persistent PHP connections? You could, but try to turn off prepared statements in PDO first. -- marko -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] unique across two tables
I have two tables, town and townalias, the latter containing alternative town names. I would like to ensure that a town name is unique per country-region across the two tables. Can I do this with a constraint ot do I need to implement the logic via trigger? = PostgreSQl 8.4 CREATE TABLE town ( country_fk character varying(3) NOT NULL, region_fk character varying(3) NOT NULL, id serial NOT NULL, name character varying(50) NOT NULL ) CREATE TABLE townalias ( country_fk character varying(3) NOT NULL, region_fk character varying(3) NOT NULL, town_fk integer NOT NULL, id serial NOT NULL, name character varying(50) NOT NULL, CONSTRAINT town_townalias_fk FOREIGN KEY (country_fk, region_fk, town_fk) REFERENCES town (country_fk, region_fk, id) ) -- Best Regards, Tarlika Elisabeth Schmitz -- 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] unique across two tables
I have two tables, town and townalias, the latter containing alternative
town names.
I would like to ensure that a town name is unique per
country-region across the two tables.
Can I do this with a constraint ot do I need to implement the logic via
trigger?
You can't have a constraint spreading multiple tables. And fixing this
using a trigger is harder than it looks, because a trigger does not see
uncommited changes made by other sessions.
So for example this will be hard to catch:
Session A: INSERT INTO town(name) VALUES ('Chicago');
Session B: INSERT INTO town(name) VALUES ('Chicago');
Session A: COMMIT;
Session B: COMMIT;
What I'd do is I'd keep the primary name in the 'townalias' table too,
maybe with a 'primary=true' flag. That way you can use traditional UNIQUE
constraint.
regards
Tomas
--
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] connection time out
Hello Guys, It was about some firewall rules setup by the system administrator. Thanks From: salah jubeh s_ju...@yahoo.com To: pgsql pgsql-general@postgresql.org Sent: Mon, June 20, 2011 12:39:51 PM Subject: [GENERAL] connection time out Hello Guys, I have a database server and I have created another replica of it on another machine. The new replica is running fine locally, but I can not access it from another clients, the pg_hba.conf file is the same as the original database server. when I try to connect to the server I get psql: could not connect to server: Connection timed out Is the server running on host xxx and accepting TCP/IP connections on port 5432? I can access the original server from the client I am trying to access the replica, So, I think the problem is not related to postgresql conf. Certainly I can access the replica using ssh and I pinged it and every thing is fine. I am wondering what are the reasons that might cause this problem. Regards
Re: [GENERAL] PostgreSQL 8.4.8 bringing my website down every evening
Hell Marko and others, On Mon, Jun 20, 2011 at 4:08 PM, Marko Kreen mark...@gmail.com wrote: Maybe I should try session mode of pgbouncer again, now that I've got rid of the persistent PHP connections? You could, but try to turn off prepared statements in PDO first. isn't having prepared statements good for overall performance? I've decided to try another way first - I've set pgsql.allow_persistent = Off in /etc/php.ini and have changed pgbouncer back to session mode (sorry, here's my config again - it unfortunately was eaten by Gmail in the previous mail): [databases] pref = host=/tmp user=pref password=XXX dbname=pref [pgbouncer] logfile = /var/log/pgbouncer.log pidfile = /var/run/pgbouncer/pgbouncer.pid listen_port = 6432 unix_socket_dir = /tmp auth_type = md5 auth_file = /var/lib/pgsql/data/global/pg_auth pool_mode = session server_check_delay = 10 max_client_conn = 200 default_pool_size = 20 log_connections = 0 log_disconnections = 0 log_pooler_errors = 1 I'll see, if my server survives the next few evenings. I must add, that PostgreSQL doesn't make it easy to use it - at least for me as an amateur user :-( Wonder, if MySQL would put less hassle on me (just want to run Drupal 7.2 + my custom PHP/Perl scripts on what I think is a good enough hardware...) Still I will try to stick with PostgreSQL, I somehow have a good feeling using it :-) Regards 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] unique across two tables
On 20 Jun 2011, at 17:03, Tarlika Elisabeth Schmitz wrote: I have two tables, town and townalias, the latter containing alternative town names. I would like to ensure that a town name is unique per country-region across the two tables. Can I do this with a constraint ot do I need to implement the logic via trigger? This is very similar to what was recently discussed in the topic Constraint to ensure value does NOT exist in another table? in this list. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4dff868612099297279502! -- 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] PostgreSQL 8.4.8 bringing my website down every evening
Nope, pool_mode = session kills my site... -- 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] how to find a tablespace for the table?
thank you Greg, here is what I get, I createed view as you suggested. I'm not sure why tablespace column is empty profiler1=# select * from pg_tables where schemaname ='public' limit 10; schemaname |tablename| tableowner | tablespace | hasindexes | hasrules | hastri ers +-++++--+--- public | ttt | postgres | | f | f| f public | summ_hrly_1514609 | postgres | | t | f| f public | summ_5min_1514610 | postgres | | t | f| f public | exp_cnt | postgres | | f | f| f public | auth_type| postgres | | t | f| f public | druid_mapping | postgres | | t | f| f public | application_category | postgres | | t | f| f public | application_risk | postgres | | t | f| f public | policy_history| postgres | | t | f| f public | datasource | postgres | | t | f| f (10 rows) thank you. Helen -- View this message in context: http://postgresql.1045698.n5.nabble.com/how-to-find-a-tablespace-for-the-table-tp4500200p4507266.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] unique across two tables
On Mon, 20 Jun 2011 19:42:20 +0200 Alban Hertroys dal...@solfertje.student.utwente.nl wrote: On 20 Jun 2011, at 17:03, Tarlika Elisabeth Schmitz wrote: I have two tables, town and townalias, the latter containing alternative town names. I would like to ensure that a town name is unique per country-region across the two tables. Can I do this with a constraint ot do I need to implement the logic via trigger? This is very similar to what was recently discussed in the topic Constraint to ensure value does NOT exist in another table? in this list. Thanks - found it: http://postgresql.1045698.n5.nabble.com/Constraint-to-ensure-value-does-NOT-exist-in-another-table-td4493651.html -- Best Regards, Tarlika Elisabeth Schmitz -- 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] how to find a tablespace for the table?
That's right, if the tables are in default tablespace, those columns will be
blank, if any of the table created under any of the
tablespace then it will show up.
Eg:-
postgres=# show default_tablespace ; (this would be blank becz am in
pg_default/pg_global)
default_tablespace
(1 row)
postgres=# create table foo(id int);
CREATE TABLE
postgres=# select * from pg_tables where tablename='foo';
-[ RECORD 1 ]-
schemaname | public
tablename | foo
tableowner | postgres
tablespace |
hasindexes | f
hasrules| f
hastriggers | f
Now I have the table in one of my tablespace.
postgres=#create table tab_test(id int) tablespace t1;
Expanded display is on.
postgres=# select * from pg_tables where tablename='tab_test';
-[ RECORD 1 ]-
schemaname | public
tablename | tab_test
tableowner | postgres
*tablespace | t1*
hasindexes | f
hasrules| f
hastriggers | f
If you want to know the tablespace default information, you can try with
this query.
select spcname, case spcname when 'pg_default' then (select setting from
pg_settings where name = 'data_directory')||'/base' when 'pg_global' then
(select setting from pg_settings where name = 'data_directory')||'/global'
else spclocation end from pg_tablespace;
To get the exact table's and its tablespace's below query will work.
select relname,reltablespace from pg_class where reltablespace in(select
oid from pg_tablespace where spcname not in ('pg_default','pg_global'));
---
Best Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/
On Mon, Jun 20, 2011 at 11:40 PM, hyelluas helen_yell...@mcafee.com wrote:
thank you Greg,
here is what I get, I createed view as you suggested.
I'm not sure why tablespace column is empty
profiler1=# select * from pg_tables where schemaname ='public' limit 10;
schemaname |tablename| tableowner | tablespace |
hasindexes
| hasrules | hastri
ers
+-++++--+---
public | ttt | postgres |
| f | f| f
public | summ_hrly_1514609 | postgres | | t
| f| f
public | summ_5min_1514610 | postgres | | t
| f| f
public | exp_cnt | postgres |
| f | f| f
public | auth_type| postgres |
|
t | f| f
public | druid_mapping | postgres | |
t | f| f
public | application_category | postgres | | t
| f| f
public | application_risk | postgres |
| t | f| f
public | policy_history| postgres |
| t | f| f
public | datasource | postgres |
|
t | f| f
(10 rows)
thank you.
Helen
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/how-to-find-a-tablespace-for-the-table-tp4500200p4507266.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] Forward referencing of table aliases in subqueries does not work in 9.1 beta2 ( works in 9.0 and 8.4.2 )
BangarRaju Vadapalli bangarraju.vadapa...@infor.com writes: create table a(x int); create table b(x int, y int); create table c(x int, y int, z int); This query fails with ERROR: JOIN qualification cannot refer to other relations - select a.x from a, b where a.x = b.x and not exists ( select 1 from c where b.y = c.y and not exists ( select 1 from c where a.x = c.x)) Thanks, I've applied a patch for this. 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] how to find a tablespace for the table?
well,
here is the query :
profiler1=# select relname,reltablespace from pg_class where reltablespace
in(select oid from pg_tablespace where spcname not in
('pg_default','pg_global'));
relname | reltablespace
-+---
why it shows no records?
profiler1=# select * from pg_catalog.pg_tables where
tablename='application_category';
schemaname | tablename | tableowner | tablespace | hasindexes |
hasrules | hastrigge
rs
+--++++--+--
---
public | application_category | postgres || t |
f| f
(1 row)
and that query show empty for the tablespace...
thank you
Helen
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/how-to-find-a-tablespace-for-the-table-tp4500200p4507624.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] how to find a tablespace for the table?
let me correct on this query, it shows only those tables which wont belong
to default_tablespace...
select relname,reltablespace from pg_class where reltablespace
in(select oid from pg_tablespace where spcname not in
('pg_default','pg_global'));
---
Best Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/
On Tue, Jun 21, 2011 at 12:55 AM, Raghavendra
raghavendra@enterprisedb.com wrote:
hmmmStrange..
What is the output of
select oid,* from pg_tablespace;
and
select relname,reltablespace from pg_class where relname='
application_category';
---
Best Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/
On Tue, Jun 21, 2011 at 12:48 AM, hyelluas helen_yell...@mcafee.comwrote:
well,
here is the query :
profiler1=# select relname,reltablespace from pg_class where reltablespace
in(select oid from pg_tablespace where spcname not in
('pg_default','pg_global'));
relname | reltablespace
-+---
why it shows no records?
profiler1=# select * from pg_catalog.pg_tables where
tablename='application_category';
schemaname | tablename | tableowner | tablespace | hasindexes
|
hasrules | hastrigge
rs
+--++++--+--
---
public | application_category | postgres || t
|
f| f
(1 row)
and that query show empty for the tablespace...
thank you
Helen
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/how-to-find-a-tablespace-for-the-table-tp4500200p4507624.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] insert a SYSTIMESTAMP value in postgres
Hello, I am trying to re-create few objects from oracle into postgres. I have a problem inserting a timestamp value into the table: insert into request_queue (request_id, received_time ) values (new.request_id, SYSTIMESTAMP ); How can I insert a dynamic timestamp value in postgress, please? Thank you, Leon leon.ma...@convergia.net
Re: [GENERAL] how to find a tablespace for the table?
hmmmStrange..
What is the output of
select oid,* from pg_tablespace;
and
select relname,reltablespace from pg_class where relname='
application_category';
---
Best Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/
On Tue, Jun 21, 2011 at 12:48 AM, hyelluas helen_yell...@mcafee.com wrote:
well,
here is the query :
profiler1=# select relname,reltablespace from pg_class where reltablespace
in(select oid from pg_tablespace where spcname not in
('pg_default','pg_global'));
relname | reltablespace
-+---
why it shows no records?
profiler1=# select * from pg_catalog.pg_tables where
tablename='application_category';
schemaname | tablename | tableowner | tablespace | hasindexes |
hasrules | hastrigge
rs
+--++++--+--
---
public | application_category | postgres || t |
f| f
(1 row)
and that query show empty for the tablespace...
thank you
Helen
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/how-to-find-a-tablespace-for-the-table-tp4500200p4507624.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] insert a SYSTIMESTAMP value in postgres
On Jun 20, 2011, at 1:32 PM, Leon Match wrote: How can I insert a dynamic timestamp value in postgress, please? http://www.postgresql.org/docs/9.0/static/functions-datetime.html -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- 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] insert a SYSTIMESTAMP value in postgres
Please look at the section on Date/Time Functions in the PostgreSQL documentation (Section 9.9 in Version 9.0 documentation) for a full listing of the date and time functions available in PostgreSQL and how they work. There is a sub-section (9.9.4) that specifically details functions that return the current time. Feel free to post with whichever function you feel is the correct one if you are still unsure after looking at the available options. Your conversion from Oracle to PostgreSQL is going to go VERY slowly if you are going to ask a question for every syntax or feature that exists in Oracle that is different in PostgreSQL. You need to understand WHAT the original code is doing and then at least look for what feature/syntax in PostgreSQL will accomplish the same result. Personally I can see where the missing MERGE feature maybe warrants a list posting but, really, getting the Current Time from a function call is basic functionality that you can be expected to be readily found in the PostgreSQL documentation if you had attempted to look. There are some articles in the WIKI about database migrations that may be worth a read as well - though I have not perused them myself. No disrespect intended, and the community appreciates newcomers, but please at least look through the extensive documentation before posting any more how do I do this in PostgreSQL questions. Then, if you still need to post, try describe what you are trying to accomplish WITHOUT resorting to posting Oracle code (code is OK but odds are many/most people on this list have never used Oracle). In this instance, SYSTIMESTMP looks like a function but we do not know for sure. And does it return a clock-time or a session-time? David J. From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Leon Match Sent: Monday, June 20, 2011 3:33 PM To: pgsql-general@postgresql.org Subject: [GENERAL] insert a SYSTIMESTAMP value in postgres Hello, I am trying to re-create few objects from oracle into postgres. I have a problem inserting a timestamp value into the table: insert into request_queue (request_id, received_time ) values (new.request_id, SYSTIMESTAMP ); How can I insert a dynamic timestamp value in postgress, please? Thank you, Leon leon.ma...@convergia.net
Re: [GENERAL] how to find a tablespace for the table?
here it is : profiler1=# select oid,* from pg_tablespace; oid | spcname | spcowner | spclocation | spcacl ---++--+--+ 1663 | pg_default | 10 | | 1664 | pg_global | 10 | | 19955 | profiler1 | 10 | /data/psql/profiler1 | (3 rows) profiler1=# select relname,reltablespace from pg_class where reltablespace=19955; relname | reltablespace -+--- (0 rows) thanks Helen -- View this message in context: http://postgresql.1045698.n5.nabble.com/how-to-find-a-tablespace-for-the-table-tp4500200p4508020.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] how to find a tablespace for the table?
profiler1=# select * from pg_catalog.pg_tables where tablename='application_category'; schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastrigge rs +--++++--+-- --- public | application_category | postgres || t | f| f (1 row) Whats the output of this.. select relname,reltablespace from pg_class where relname=' application_category'; --- Best Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/ On Tue, Jun 21, 2011 at 2:16 AM, hyelluas helen_yell...@mcafee.com wrote: here it is : profiler1=# select oid,* from pg_tablespace; oid | spcname | spcowner | spclocation | spcacl ---++--+--+ 1663 | pg_default | 10 | | 1664 | pg_global | 10 | | 19955 | profiler1 | 10 | /data/psql/profiler1 | (3 rows) profiler1=# select relname,reltablespace from pg_class where reltablespace=19955; relname | reltablespace -+--- (0 rows) thanks Helen -- View this message in context: http://postgresql.1045698.n5.nabble.com/how-to-find-a-tablespace-for-the-table-tp4500200p4508020.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] how to find a tablespace for the table?
profiler1=# select relname,reltablespace from pg_class where relname='application_category'; relname| reltablespace --+--- application_category | 0 (1 row) -- View this message in context: http://postgresql.1045698.n5.nabble.com/how-to-find-a-tablespace-for-the-table-tp4500200p4508040.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] insert a SYSTIMESTAMP value in postgres
On Mon, 20 Jun 2011 15:32:31 -0400, Leon Match wrote:
Hello,
I am trying to re-create few objects from oracle into postgres.
I have a problem inserting a timestamp value into the table:
insert into request_queue (request_id, received_time
)
values (new.request_id, SYSTIMESTAMP
);
How can I insert a
dynamic timestamp value in postgress, please?
Thank you,
Leon
leon.ma...@convergia.net
May be: insert into request_queue
(request_id, received_time) values (new.request_id,
(abstime(('now'::text)::timestamp(6) with time zone))::integer);
--
Regards,
Condor
Re: [GENERAL] how to find a tablespace for the table?
Can you give a try updating the catalogs with ANALYZE command and re-check ? --- Best Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/ On Tue, Jun 21, 2011 at 2:22 AM, hyelluas helen_yell...@mcafee.com wrote: profiler1=# select relname,reltablespace from pg_class where relname='application_category'; relname| reltablespace --+--- application_category | 0 (1 row) -- View this message in context: http://postgresql.1045698.n5.nabble.com/how-to-find-a-tablespace-for-the-table-tp4500200p4508040.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] how to find a tablespace for the table?
Actually to say, everything looks ok to me, until unless you say the table * '**application_category'* on other tablespace ... :) profiler1=# select relname,reltablespace from pg_class where relname='application_category'; relname| reltablespace --+--- application_category | 0 (1 row) Above result, indicates that its in default tablespace. http://www.postgresql.org/docs/9.0/static/catalog-pg-class.html Regards Raghav -- View this message in context: http://postgresql.1045698.n5.nabble.com/how-to-find-a-tablespace-for-the-table-tp4500200p4508040.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] insert a SYSTIMESTAMP value in postgres
El 20/06/2011 14:33, Leon Match leon.ma...@convergia.net escribió: Hello, I am trying to re-create few objects from oracle into postgres. I have a problem inserting a timestamp value into the table: insert into request_queue (request_id, received_time ) values (new.request_id, SYSTIMESTAMP ); How can I insert a dynamic timestamp value in postgress, please? Maybe this document can help you http://wiki.postgresql.org/wiki/Oracle_to_Postgres_Conversion -- Jaime Casanovawww.2ndQuadrant.com
Re: [GENERAL] how to find a tablespace for the table?
that is exactly the case - application_category table is in profiler1 tablespace as well as all tables in my profilre1 database. I'm not sure how to update catalog... vacuum ? -- View this message in context: http://postgresql.1045698.n5.nabble.com/how-to-find-a-tablespace-for-the-table-tp4500200p4508315.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] PostgreSQL 8.4.8 bringing my website down every evening
PDO::ATTR_EMULATE_PREPARES = true kills my server too... On Mon, Jun 20, 2011 at 7:34 PM, Alexander Farber alexander.far...@gmail.com wrote: Nope, pool_mode = session kills my site... -- 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] how to find a tablespace for the table?
I've got it now - it will be 0/empty for a table in default tablespace, if the table has been created in a different tablespace - it will show the name. thank you!. Helen -- View this message in context: http://postgresql.1045698.n5.nabble.com/how-to-find-a-tablespace-for-the-table-tp4500200p4508750.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Help needed with PostgreSQL clustering/switching from MySQL
Hello I'm new to PostgreSQL, coming at it from a MySQL background. I'm currently looking at switching one of our applications (which currently uses MySQL) over to PostgreSQL and had some questions. We're considering the switch because of issues we have faced when using MySQL in a clustered scenario and we're hoping that switching to PostgreSQL will help us resolve these issues. Our three biggest pain points with MySQL are: 1. MySQL's NDB engine (used for clustering) cannot index textual data stored in a BLOB field 2. When configuring a MySQL cluster, there is a memory limit on the number of objects (tables and fields). We often have problems when importing new tables, wherein we need to increase the memory limit for the server's NDB engine before it allows us to import. This can be a problem for dynamically-generated tables, as we cannot accurately forecast the number of database objects in advance in these cases. 3. MySQL's NDB engine doesn't support or enforce foreign keys. So my first question is, I'd like to know if PostgreSQL has similar issues when running in a clustered scenario. Second, on reviewing the manual and some sites, it seems that there are a number of different OSS solutions for implementing failover and clustering with PostgreSQL, but no official version. Is this understanding correct? If yes, which solution is best suited for running PostgreSQL in a private cloud, with clustering/failover support? Thank you, Vikram THIS EMAIL ANY ATTACHED FILES ARE PRIVATE CONFIDENTIAL If you are not the addressee, any disclosure, reproduction, copying, distribution, or any other dissemination or use of this communication is strictly prohibited. If you have received this transmission in error please notify the sender immediately and then delete this email. Email transmission cannot be guaranteed to be secure or error free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any errors or omissions in the contents of this message which arise as a result of email transmission. If verification is required please request a hard copy version.
Re: [GENERAL] Help needed with PostgreSQL clustering/switching from MySQL
On Tue, Jun 21, 2011 at 05:07:10AM +, Vikram Vaswani wrote: Hello I'm new to PostgreSQL, coming at it from a MySQL background. I'm currently looking at switching one of our applications (which currently uses MySQL) over to PostgreSQL and had some questions. We're considering the switch because of issues we have faced when using MySQL in a clustered scenario and we're hoping that switching to PostgreSQL will help us resolve these issues. Our three biggest pain points with MySQL are: 1. MySQL's NDB engine (used for clustering) cannot index textual data stored in a BLOB field PostgreSQL's full text capability is quite good. There are better specialty (non-relational) engines out there, and some proprietary engines that do more (or at least different) things, so you'll need to assess carefully what type of text searching you want to do, and what you'll trade that capability for. 2. When configuring a MySQL cluster, there is a memory limit on the number of objects (tables and fields). We often have problems when importing new tables, wherein we need to increase the memory limit for the server's NDB engine before it allows us to import. This can be a problem for dynamically-generated tables, as we cannot accurately forecast the number of database objects in advance in these cases. Dynamically generated tables are generally a problem at the design level. Neither PostgreSQL nor any other engine will solve that. 3. MySQL's NDB engine doesn't support or enforce foreign keys. This, PostgreSQL does extremely well. So my first question is, I'd like to know if PostgreSQL has similar issues when running in a clustered scenario. I'm not entirely sure what you mean by a clustered scenario, but I'd like to digress into the matter of multi-master replication. It can be fast, so long as it doesn't have to be correct, or it can be correct, so long as your users are willing to wait, but it can't be both fast and correct at once. In 90%+ of cases, it's neither fast nor correct. I guess what I'm saying here is that you should not design systems that depend on unicorn steak, skyhooks, magic pixie dust, or fast, accurate multi-master replication. Second, on reviewing the manual and some sites, it seems that there are a number of different OSS solutions for implementing failover and clustering with PostgreSQL, but no official version. As of 9.0, there is built-in asynchronous replication, which can be streamed (lower lag times) if you like. You might also want to consider some of the other solutions. http://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling Is this understanding correct? If yes, which solution is best suited for running PostgreSQL in a private cloud, with clustering/failover support? Please to understand that you need to set priorities for these things and decide which you might sacrifice in order to get the others. Thank you, Vikram THIS EMAIL ANY ATTACHED FILES ARE PRIVATE CONFIDENTIAL If you are not the addressee, any disclosure, reproduction, copying, distribution, or any other dissemination or use of this communication is strictly prohibited. If you have received this transmission in error please notify the sender immediately and then delete this email. Email transmission cannot be guaranteed to be secure or error free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any errors or omissions in the contents of this message which arise as a result of email transmission. If verification is required please request a hard copy version. In future, please to elide these disclaimers. The serve no legal or practical purpose, but they do give people they annoy a convenient excuse not to reply. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Tuning for a tiny database
Hello, I couldn't find much information in the archives on this -- perhaps this is a bit of a specialized need, but I was hoping for some input from some experienced postgres admins. I'm moving some DNS servers from djbdns/tinydns to PowerDNS. While it supports many backends, postgresql seems like the best choice for us since it's what is used elsewhere (in larger configurations). As a bit of background, PowerDNS does not query the db for every incoming DNS query, it caches at various levels (both a packet cache and a db cache), so it's database needs are quite modest. Some raw numbers: We're only looking at a total of about six tables in one db. In total there are going to be well under 10,000 records in ALL tables. That might increase to at most 100,000 in the next few years. Our raw DNS queries/second tops out around 50 qps over three distinct servers. Keeping in mind that PowerDNS is doing heavy caching, we should never really see more than a few db queries per second. There will be one master pgsql db and three slaves using streaming replication. Now given the number of records and the frequency of queries, how should I be tuning for such a small setup? Ideally PowerDNS with it's huge amount of caching should get as much RAM and CPU as I can give it, but I do want to ensure the tiny bit of data postgres has is stuck in physical memory as well. What would you suggest for this scenario? Thanks, Charles -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
