Re: [GENERAL] dynamic table names

[Alban Hertroys]

On Jul 17, 2013, at 22:39, John Smith  wrote:

> so my query goes like so:
> 
> > execute 'select * from ' || tabname::regclass || ' where firstname = 
> > "john"' into e;

Are those quotes around 'john' double-quotes (for identifiers) or double 
single-quotes (for literals)?

They look like double-quotes to me, so unless your table has a column named 
"john" (and lowercase at that) that would fail. It's probably not what you 
meant anyway.

Does this fare any better?
execute 'select * from ' || tabname::regclass || ' where firstname = ''john''' 
into e;

> but i am getting an error:
> 
> > ERROR: syntax error at or near "'select * from '" at character 9

Do you always get that error or do you only get it with certain table names? If 
so, which ones?

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Reply: Reply: [GENERAL] ?????? [GENERAL] Can't create plpython language

[guxiaobo1982]

Problem with ActivePython-2.7.2.5 is 


[postgres@lix stado]$ psql
psql (9.3beta2)
Type "help" for help.


postgres=# create language plpython2u;
ERROR:  could not load library 
"/opt/PostgreSQL/93b2src/lib/postgresql/plpython2.so": 
/opt/PostgreSQL/93b2src/lib/postgresql/plpython2.so: undefined symbol: 
PyObject_Bytes





with environment variables as


export PGDATA=/opt/PostgreSQL/pgdata/93b2src
export PYTHONHOME=/opt/ActivePython-2.7
export PYTHONPATH=/opt/ActivePython-2.7/bin


export PATH=/opt/PostgreSQL/93b2src/bin:$PYTHONHOME/bin:$PATH
export LD_LIBRARY_PATH=$PYTHONHOME/lib:$LD_LIBRARY_PATH











-- Original --
Sender: "Raghavendra";
Send time: Tuesday, Jul 16, 2013 4:53 PM
To: "guxiaobo1982"; 
Cc: "Jov"; "Michael Paquier"; 
"pgsql-general"; "Asif 
Naeem"; 
Subject: Re: Reply: [GENERAL] ?? [GENERAL] Can't create plpython language



On Mon, Jul 15, 2013 at 7:10 PM, guxiaobo1982  wrote:
 It works with ActivePython 3.2.2.3,


Thanks for notifying. 
  
but not ActivePython 2.7.2.5.
 




No idea... :),  I tried AP 3.2 with PG 9.2 & PG 9.3B it went fine for me.


---
 Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/





 







 



 -- Original --
From:  "Raghavendra";
 Date:  Jul 11, 2013
To:  "guxiaobo1982"; 
Cc:  "Jov"; "Michael Paquier"; 
"pgsql-general"; "Asif 
Naeem"; 
 Subject:  Re: Reply: [GENERAL] ?? [GENERAL] Can't create plpython language



On Wed, Jul 10, 2013 at 7:36 AM, guxiaobo1982  wrote:
  I am using PostgreSQL 9.3 beta 2


[postgres@lix Multicorn-master]$ psql
psql (9.3beta2)
Type "help" for help.


postgres=# 

 



It does not work with 
ActivePython3.2(ActivePython-3.2.2.3-linux-x86_64.tar.gz), Which version of 
PostgreSQL are you running?




Sorry for being late on this email. I overlooked the version you were pointing. 
I didn't test on PG 9.3Beta, I did on PG 9.2 after looking your error showing 
PG 9.2.4 
 

[postgres@lix PostgreSQL]$ psql
Password:
 psql (9.2.4)
Type "help" for help.
postgres=# create language plpython2u;
 ERROR:  could not access file "$libdir/plpython2": No such file or directory
postgres=# create language plpython3u;
 ERROR:  could not access file "$libdir/plpython3": No such file or directory


Later, I began testing it with PG 9.3beta and encountered same error message as 
you have shared here. 
 Did some analysis and finally succeeded to create language plpython3u with 
AP-3.2, however with not many tweaks in compilation.(Steps might be arguing. 
Pardon me). 


Despite the fact that I have ActivePython-3.2 on my system, source compilation 
was looking for shared library. Hence compiled explicitly with 
shared_libpython=yes.
 

Pre-Steps: (Assuming you have ActivePython 3.2)


cd /opt/ActivePython-3.2/bin/
cp python3.2-config python-config
cp python3. python


 Test:
export PATH=/opt/ActivePython-3.2/bin:$PATH
# which python
 /opt/ActivePython-3.2/bin/python
 # which python-config
 /opt/ActivePython-3.2/bin/python-config
 

Steps:


1. Install PG 9.3 beta with below steps:


export PATH=/opt/ActivePython-3.2/bin:$PATH
./configure --prefix=/usr/local/pg93b3 --with-python
 make shared_libpython=yes
 make shared_libpython=yes install



 2. After installation you should see below files in your PG installation Path:
 

[root@localhost pg93b3]# pwd
 /usr/local/pg93b3
[root@localhost pg93b3]# find . | grep python
 ./lib/postgresql/plpython3.so
./share/postgresql/extension/plpython3u--unpackaged--1.0.sql
 ./share/postgresql/extension/plpython3u--1.0.sql
 ./share/postgresql/extension/plpython3u.control



 3. Now, create new cluster using INITDB
4. Before starting the cluster set the PYTHONHOME, PYTHONPATH, LD_LIBRARY_PATH
 

 export PYTHONHOME=/opt/ActivePython-3.2/
 export PYTHONPATH=/opt/ActivePython-3.2/bin:$PATH
export LD_LIBRARY_PATH=/opt/ActivePython-3.2/lib:$LD_LIBRARY_PATH
 


 5. Start the cluster and create the language:


 /usr/local/pg93b3/bin/pg_ctl -D /usr/local/pg93b3/data/ start
 

 -bash-4.1$ psql -p 
psql (9.3beta1)
Type "help" for help.

postgres=# create language plpython3u;
CREATE LANGUAGE
 

Out of my analysis on the issue, Asif Naeem from our Dev group shared his 
valuable thoughts to conclude this. Thanks Asif.
 

 Question still in mind, Why plpython depends on Shared Libraries (.so) ?
 

http://www.postgresql.org/docs/9.3/static/install-requirements.html
 

 ---
Regards,
Raghavendra
EnterpriseDB Corporation
 Blog: http://raghavt.blogspot.com/

Re: [GENERAL] pgAdmin for ubuntu

[Luca Ferrari]

On Wed, Jul 17, 2013 at 4:02 PM, Muhammad Bashir Al-Noimi
 wrote:

> Failed to fetch
> bzip2:/var/lib/apt/lists/partial/archive.ubuntu.com_ubuntu_dists_quantal-updates_main_binary-amd64_Packages
>  Hash Sum mismatch

I guess this is the problem. It happened to me sometimes when working
behind a proxy, but I don't have a good workaround. It is better to
ask to someone on an ubuntu mailing list.

Luca


-- 
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] Update big table

[Haiming Zhang]

Thanks.

I have fixed the problem. And now the query can run in 4 mins for 1.5 million 
records in TABLE1 and 0.5 million records in TABLE2.

The solution is I created a function that gets all event_id from TABLE2 then 
travel through each event_id and do the update for TABLE1. This removes the 
side effects of using IN predicate. Also I have
1. set FILLFACTOR = 70
2. remove trigger and index before the function, create index 
based on event_id
3. add trigger and index back after run function.

Regards,
Haiming

From: BladeOfLight16 [mailto:bladeofligh...@gmail.com]
Sent: Monday, 15 July 2013 5:26 AM
To: pgsql-general@postgresql.org; Haiming Zhang
Subject: Re: [GENERAL] Update big table

I don't believe you can use JOIN explicitly in this situation because it's an 
UPDATE, but I believe you can accomplish the same effect with FROM and WHERE.

UPDATE table SET column1 = TRUE
FROM table2
WHERE table1.event_id = table2.event_id;

I would make sure there's an index on table2.event_id if it's not the PK. If 
it's part of a composite key, either make sure that table2.event_id is the 
first column in the index (PostgreSQL will use an index like that; right?) or 
create an index where it is.

(Sorry. Forgot to hit Reply All.)
On Sun, Jul 14, 2013 at 7:03 AM, Vincenzo Romano 
mailto:vincenzo.rom...@notorand.it>> wrote:
2013/7/14 Haiming Zhang 
mailto:haiming.zh...@redflex.com.au>>:
> Hi All,
>
>
>
> I am using postgres 9.1, I have a question about updating big table. Here is
> the basic information of this table.
>
> 1. This table has one primary key and other 11 columns.
>
> 2.  It also has a trigger that before update records,
> another table got updated first.
>
> 3. The has millions of records now.
> 4. I am not allowed to delete records in this table when
> UPDATE
>
> The problem is when I do a "Update" query it takes a long time to execute.
> Eg. when I run query like this " update TABLE set column1 = true where
> EVENT_ID in (select EVENT_ID from TABLE2 );" , it took hours to update the
> whole table. In order to optimize the update speed. I tried the following
> strategies:
>
> 1. create index based on primary key, column1 and
> combination of primary key and column1.
>
> 2. Alter FILLFACTOR = 70, vacuum all and then reindex
>
> 3. drop trigger before update
>
> Then I use "EXPLAIN" to estimate query plan, all of the above strategies do
> not improve the UPDATE speed dramatically.
>
>
>
> Please comments on my three strategies (eg, does I index too many columns in
> 1?) and please advise me how to improve the update speed. Any advice is
> welcomed. I appreciate all you help.
>
>
>
> Thanks,
>
>
>
> Regards,
>
> Haiming
A JOIN would solve your speed problem.
The IN() predicate is the cause.
AFAIK.


--
Sent via pgsql-general mailing list 
(pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



If you are not an authorised recipient of this e-mail, please contact me at 
Redflex immediately by return phone call or by email. In this case, you should 
not read, print, retransmit, store or act in reliance on this e-mail or any 
attachments, and should destroy all copies of them. This e-mail and any 
attachments are confidential and may contain privileged information and/or 
copyright material of Redflex or third parties. You should only retransmit, 
distribute or commercialise the material if you are authorised to do so. This 
notice should not be removed.

Re: [GENERAL] dynamic table names

[David Johnston]

John Smith-54 wrote
> any help? 

Sorry.

Its hard enough teaching people via e-mail let alone teaching them on an
unsupported version of PostgreSQL that has reduced functionality with
respect to function writing compared to the more recent versions.

You are going to need to some kind of "FOR" loop to iterate over each table
you want to query and then use "EXECUTE" to actually perform the query using
the syntax you referred to earlier.

The more complete the examples of your work-in-progress you post the more
help you are likely to get.  Basically read the documentation and try things
and if you get stumped post a specific question about what is stumping you
(with as much detail as possible; but take time to organize and comment it)
to get past the block.

David J.




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/dynamic-table-names-tp5764134p5764161.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] dynamic table names

[John Smith]

david, you're right. i didn't realize it had to be executed inside a
function so now i'm trying this:

-- create function
create or replace function get_tables(sname varchar) returns record as $$
select tablename from pg_tables where schemaname = $1;
$$ language 'plpgsql';

-- query tables where column 'fname' = 'john'
select * from get_tables('myschema') where fname = 'john';

any help? wishing for a "select * from *..." :)

thanks, jzs

Re: [GENERAL] About postgres scale out

[David Kerr]

On Wed, Jul 17, 2013 at 03:10:37PM +0800, Xiang Jun Wu wrote:
- Hello,
- 
- I'd like to ask a common question about scale out for postgres.
- 
- Our current data volume is about 500GB ~ 1TB in one pg cluster(postgres 9.2). 
We've set up master/slave replication to keep sync. 
- To reach better performance from apps side,  we want to use pgbounder like 
app to split read/write on multiple servers instead of single server.
- Sometimes, there is repl lag between master and slave pg servers (e.g. backup 
or other issues unexpected). Is there flexible solution from pgbounder (or 
other ways) to identify that and switch read/write to not staled server smartly?
- 
- 
- Thank you in advance!

PgPool does this for you.

http://www.pgpool.net/docs/latest/pgpool-en.html#replication_mode
---
delay_threshold V3.0 -
Specifies the maximum tolerated replication delay of the standby against the 
primary server in WAL bytes. If
the delay exceeds delay_threshold, pgpool-II does not send SELECT queries to 
the standby server anymore.
Everything is sent to the primary server even if load balance mode is enabled, 
until the standby has
caught-up. If delay_threshold is 0 or sr checking is disabled, the delay 
checking is not performed. This
check is performed every 'sr_check_period'. The default value for 
delay_threshold is 0.
You need to reload pgpool.conf if you change this directive.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] About postgres scale out

[Xiang Jun Wu]

Hello,

I'd like to ask a common question about scale out for postgres.

Our current data volume is about 500GB ~ 1TB in one pg cluster(postgres 9.2). 
We've set up master/slave replication to keep sync. 
To reach better performance from apps side,  we want to use pgbounder like app 
to split read/write on multiple servers instead of single server.
Sometimes, there is repl lag between master and slave pg servers (e.g. backup 
or other issues unexpected). Is there flexible solution from pgbounder (or 
other ways) to identify that and switch read/write to not staled server smartly?


Thank you in advance!




-- 
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] dynamic table names

[Thomas Kellerer]

John Smith wrote on 17.07.2013 22:39:

guys,

have to use legacy 8.1.

i have 100,000 tables in a schema that need to be queried (optimizing this by 
combining them into one will have to wait).

so my query goes like so:

 > execute 'select * from ' || tabname::regclass || ' where firstname = "john"' 
into e;

but i am getting an error:

 > ERROR: syntax error at or near "'select * from '" at character 9

? do someone have a stored procedure for this?


"john" is a column name, not a string value. You need to use 'john'
but as that is part of another string literal you need to use two single quotes
(which is something different than one double quote)

execute 'select * from ' || tabname::regclass || ' where firstname = ''john''' 
into e;




--
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] dynamic table names

[David Johnston]

John Smith-54 wrote
> guys,
> 
> have to use legacy 8.1.
> 
> i have 100,000 tables in a schema that need to be queried (optimizing this
> by combining them into one will have to wait).
> 
> so my query goes like so:
> 
>> execute 'select * from ' || tabname::regclass || ' where firstname =
> "john"' into e;
> 
> but i am getting an error:
> 
>> ERROR: syntax error at or near "'select * from '" at character 9

First, your treatment of the constant "john" is problematic since
double-quotes are not going to work.  You will need to use
quote_literal(...) just like the example in the documentation shows.  And
you should probably use "quote_ident(...)" as well.  In fact depending on
the name of your table you may have to.

Second at first glance I do not see where you have a syntax in your e-mail
but that does not mean your code is correct since you have not provided it.

The only unusual thing is the casting to regclass of "tabname" and that too
falls into the second problem that you haven't actually defined where
"tabname" comes from or what values it could take on.

I'm also curious if you realize that "EXECUTE" used this way has to be done
within a function.  I assume you do because of the presence of the "tabname"
variable in your example but again you provide no actually executable code
so there is no way we know for sure.

David J.





--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/dynamic-table-names-tp5764134p5764139.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] dynamic table names

[Rob Sargent]

On 07/17/2013 02:39 PM, John Smith wrote:

guys,

have to use legacy 8.1.

i have 100,000 tables in a schema that need to be queried (optimizing 
this by combining them into one will have to wait).


so my query goes like so:

> execute 'select * from ' || tabname::regclass || ' where firstname = 
"john"' into e;


but i am getting an error:

> ERROR: syntax error at or near "'select * from '" at character 9

? do someone have a stored procedure for this?

http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
http://www.postgresql.org/message-id/aa44f4a30510071254p46064ca0vff639cd492b22...@mail.gmail.com
http://stackoverflow.com/questions/5772699/postgresql-dynamic-table-access
http://www.sql-workbench.net/

thanks, jzs



Sorry, disregard prev post



--
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] dynamic table names

[Rob Sargent]

On 07/17/2013 02:39 PM, John Smith wrote:

guys,

have to use legacy 8.1.

i have 100,000 tables in a schema that need to be queried (optimizing 
this by combining them into one will have to wait).


so my query goes like so:

> execute 'select * from ' || tabname::regclass || ' where firstname = 
"john"' into e;


but i am getting an error:

> ERROR: syntax error at or near "'select * from '" at character 9

? do someone have a stored procedure for this?

http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
http://www.postgresql.org/message-id/aa44f4a30510071254p46064ca0vff639cd492b22...@mail.gmail.com
http://stackoverflow.com/questions/5772699/postgresql-dynamic-table-access
http://www.sql-workbench.net/

thanks, jzs


Drop the outer single quotes.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] dynamic table names

[John Smith]

guys,

have to use legacy 8.1.

i have 100,000 tables in a schema that need to be queried (optimizing this
by combining them into one will have to wait).

so my query goes like so:

> execute 'select * from ' || tabname::regclass || ' where firstname =
"john"' into e;

but i am getting an error:

> ERROR: syntax error at or near "'select * from '" at character 9

? do someone have a stored procedure for this?

http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
http://www.postgresql.org/message-id/aa44f4a30510071254p46064ca0vff639cd492b22...@mail.gmail.com
http://stackoverflow.com/questions/5772699/postgresql-dynamic-table-access
http://www.sql-workbench.net/

thanks, jzs

[GENERAL] What are my options to avoid a Row Exclusive/RowShareLock conflict / is there a better way?

[rox]

I have a long running stored procedure that pulls data from 2-3 tables, 
updates columns in a row in the IMPORT_STATUS table, creates a couple of 
temp tables, and then deletes/inserts/updates back into 3-4 tables... 
but never once does anything directly with the USER table.


The long running stored procedure is run in the background from php via 
cron... "a queued event" which the user can cause to happen at just 
about any time, but which may not run for some time, and then runs for 
as short as a few seconds, usually averaging minutes, and occasionally 
goes for up to an hour or so.


I have a logon process that selects from the USER table, then does an 
update on it and inserts into an EVENT table.


If the stored procedure is accessing records that belong to the user, 
the user can not log in because there is a RowShareLock against the USER 
table from the stored procedure and the login process does an UPDATE 
which attempts to grab a Row Exclusive lock.


I know at this point that if I remove the Foreign Key constraint from 
the IMPORT_STATUS table, the RowShareLock is not grabbed on the USER 
table when the stored procedure runs.  Nominally, that fixes my problem.


However I'm curious, so here goes... I know the stored procedure isn't 
referencing anything related to that foreign key relationship.  Does it 
always grab a ShareLock on all FK constraints even if the columns are 
not referenced?  What if the column is allowed NULL?


Is my best option just to remove the FK constraint on IMPORT_STATUS and 
then ensure that the application otherwise enforces the requirement that 
the user_id exists in the User table on insert/update?


Is there a better way to capture active running "status"  information?  
Our approach leaves no visibility to progress until the procedure ends.


thanks,

Roxanne
"PostgreSQL 9.1.3 on x86_64-unknown-linux-gnu, compiled by gcc 
(Ubuntu/Linaro 4.6.3-1ubuntu2) 4.6.3, 64-bit"



CREATE OR REPLACE FUNCTION buildGeom (_collection_id integer, 
_import_status_id integer, _import_status_order integer)

  RETURNS SETOF collection_points AS
{
 ...
 UPDATE import_status SET ppa_cleaned[_import_status_order] = 
_i_count_points WHERE import_status_id = _import_status_id;

 ...
 UPDATE import_status SET ppa_deleted[_import_status_order] = 
_i_count_pointsdeleted WHERE import_status_id = _import_status_id;

 
}

CREATE TABLE import_status
(
  import_status_id integer NOT NULL DEFAULT 
nextval('import_status_import_status_id_seq'::regclass), -- Artificial 
primary key

  queue_id integer,
  files_id integer,
  user_id integer NOT NULL,
  import_status_type_id integer NOT NULL,
  point_count integer,
  start_time timestamp without time zone,
  end_time timestamp without time zone,
  ppa_parsed integer[],
  ppa_saved integer[],
  ppa_cleaned integer[],
  ppa_deleted integer[],
  time_queued timestamp without time zone[],
  time_started timestamp without time zone[],
  time_parsed timestamp without time zone[],
  time_saved timestamp without time zone[],
  time_processed timestamp without time zone[],
  time_ended timestamp without time zone[],
  CONSTRAINT import_status_id_pk PRIMARY KEY (import_status_id ),
  CONSTRAINT import_status_files_id_fkey FOREIGN KEY (files_id)
  REFERENCES public.files (files_id) MATCH SIMPLE
  ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT import_status_import_status_type_id_fkey FOREIGN KEY 
(import_status_type_id)
  REFERENCES public.import_status_type (import_status_type_id) 
MATCH SIMPLE

  ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT import_status_queue_id_fkey FOREIGN KEY (queue_id)
  REFERENCES public.queue (queue_id) MATCH SIMPLE
  ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT import_status_user_id_fkey FOREIGN KEY (user_id)
  REFERENCES public."user" (user_id) MATCH SIMPLE
  ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (  OIDS=FALSE );

CREATE TABLE "user"
(
  user_id integer NOT NULL DEFAULT 
nextval('user_user_id_seq'::regclass),

  user_type_id integer NOT NULL,
  name character varying(255) NOT NULL,
  password character varying(255) NOT NULL,
  status boolean NOT NULL DEFAULT true,
  address1 character varying(100),
  address2 character varying(100),
  city character varying(50),
  state character varying(20),
  postal_code character varying(20),
  email_address character varying(254),
  full_name character varying(75),
  session_id character varying(40),
  CONSTRAINT user_pkey PRIMARY KEY (user_id ),
  CONSTRAINT user_user_type_id_fkey FOREIGN KEY (user_type_id)
  REFERENCES public.user_type (user_type_id) MATCH SIMPLE
  ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (  OIDS=FALSE );



--
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] Query plan different depending on the value of where filter

[Tom Lane]

"Looby, Denis"  writes:
> What I don't understand is why the plan is different just because the 
> group_id = has changed value?
> Does the planner have some statistical info on the contents of non-indexed 
> rows?

Of course.  In this case it knows that a nestloop would be a loser
because many rows would be fetched from the outer table.

> I don't quite understand why this plan executes the sequential scan once, 
> whereas the slow one does it 5001 times, which I believe is the main source 
> of the difference.

In the hash join case, it builds the hash table, discovers that it's
empty (because there are no rows with type = 2), and concludes that it
need not scan the outer relation.  This is a corner case that the
planner doesn't try to account for because it couldn't be predicted
reliably.  I wouldn't get too excited about it if I were you, because
presumably the case isn't going to happen all that much in production
either (else why are you bothering with the query?).

Personally I'd suggest getting rid of the
WHERE outer_tab.outer_key = inner_tab_1.key_to
clause, which is entirely redundant with the IN condition and is
preventing the planner from seeing that it could optimize the IN
into a semijoin.

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] Query plan different depending on the value of where filter

[Looby, Denis]

Hi All,

I have an interesting query scenario I'm trying to understand.
I came across this while investigating a slow query in our application.
I've been able to reproduce the scenario in a psql script that sets up the 
tables and runs the queries.
Script here http://pastebin.com/CBkdDmWp if anyone is interested.

This is the scenario.
Version is "PostgreSQL 9.2.4, compiled by Visual C++ build 1600, 32-bit"
Three tables
Outer_tab  : 5000 rows
Inner_tab_1 : 1000 rows
Inner_tab_2 : 16000 rows

This is the query
SELECT outer_tab.outer_key
  FROM outer_tab
WHERE outer_tab.outer_key IN (
   SELECT inner_tab_1.key_to
 FROM inner_tab_2
INNER JOIN inner_tab_1 ON (inner_tab_2.outer_key = 
inner_tab_1.key_from AND inner_tab_1.type = 2)
WHERE outer_tab.outer_key = inner_tab_1.key_to AND 
inner_tab_2.group_id = 9
);

Two important things here, type = 2 does not occur in inner_tab_1 and group_id 
= 9 does not occur in inner_tab_2 and group_id is not indexed.
The result is 0 rows.
Now this is quite slow about 15 seconds on my machine.
Here is the explain plan http://explain.depesz.com/s/BVg
I understand that the seq scan on inner_tab_2 and its 16000 rows is the culprit 
and the easy fix is to swap inner_tab_2 and inner_tab_1 between the FROM and 
the JOIN.
This lets it drive off inner_tab_1 with an index scan and skip the sequential 
scan as seen here http://explain.depesz.com/s/pkG
Much better at 14ms.

That's fine but what has me somewhat confused is if group_id in the WHERE is 
changed to 1, which does exist in inner_tab_2, we get quite a different plan.
http://explain.depesz.com/s/FX4
It's quick too 63ms

What I don't understand is why the plan is different just because the group_id 
= has changed value?
Does the planner have some statistical info on the contents of non-indexed rows?
I don't quite understand why this plan executes the sequential scan once, 
whereas the slow one does it 5001 times, which I believe is the main source of 
the difference.

Also if I don't ANALYZE  the tables the original query will run in a few ms 
instead of 15 seconds, it actually uses the same query plan that swapping the 
tables creates.
So it runs the index scan on inner_tab_1 first.
It's a bit surprising that with ANALYSE it picks a plan that is so much worse.
Any one able to shed some light?

Thanks for your time,

Denis Looby

Re: [GENERAL] pgAdmin for ubuntu

[Muhammad Bashir Al-Noimi]

On Wed, Jul 17, 2013 at 4:10 PM, Vincenzo Romano
 wrote:
> Did you run "apt-get update" once you did che change?


sure yes.

--
Best Regards
Muhammad Bashir Al-Noimi


-- 
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] pgAdmin for ubuntu

[Vincenzo Romano]

2013/7/17 Muhammad Bashir Al-Noimi :
> On Wed, Jul 17, 2013 at 3:34 PM, Vincenzo Romano
>  wrote:
>> Once you "sudo add-apt-repository ppa:pitti/postgresql" then you go in
>> /etc/apt/sources.list.d/pitti* and you replace the word with your
>> Ubuntu version with "precise". By doing so you'll install that version
>> into your distribution.
>
> Thanks Vincenzo,
>
> OMG, akthough it fixes my issue but this absolute painful proceduer!
>
> by the way, I recived this error message after apt-get update:
> ---
> Failed to fetch
> bzip2:/var/lib/apt/lists/partial/archive.ubuntu.com_ubuntu_dists_quantal-updates_main_binary-amd64_Packages
>  Hash Sum mismatch
> Failed to fetch
> bzip2:/var/lib/apt/lists/partial/archive.ubuntu.com_ubuntu_dists_quantal-updates_main_binary-i386_Packages
>  Hash Sum mismatch
> Some index files failed to download. They have been ignored, or old
> ones used instead.
>
> --
> Best Regards
> Muhammad Bashir Al-Noimi

Did you run "apt-get update" once you did che change?


-- 
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] Build RPM from Postgres Source

[Michael Paquier]

On Wed, Jul 17, 2013 at 9:28 PM, Devrim GÜNDÜZ  wrote:
>
> Hi,
>
> On Fri, 2013-07-12 at 14:37 -0700, ktewari1 wrote:
>> Hi,
>> I need to have some different settings(like NAMEDATALEN  etc.) and
>> that's why I'm trying to build postgres from the source and to create an rpm
>> to be send for install.
>>
>> Now, the build works fine but, I don't see a way to create an RPM.
>
> Here is how we build the community RPMs:
>
> * Fetch patches and spec files from SVN. For example:
>
> http://svn.pgrpms.org/browser/rpm/redhat/9.2/postgresql/EL-6/
>
> * Add any patches to spec file:
>
> http://svn.pgrpms.org/browser/rpm/redhat/9.2/postgresql/EL-6/postgresql-9.2.spec#L89
>
> * Build the RPM.
>
> You can add your own patches, and build your own RPMs like this.
Here is also a post written by Devrim describing how to do that more in details:
http://people.planetpostgresql.org/devrim/index.php?/archives/44-How-To-Build-Your-Own-PostgreSQL-and-related-software-RPMs-on-CentOSRHELFedora.html
--
Michael


-- 
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] pgAdmin for ubuntu

[Muhammad Bashir Al-Noimi]

On Wed, Jul 17, 2013 at 3:34 PM, Vincenzo Romano
 wrote:
> Once you "sudo add-apt-repository ppa:pitti/postgresql" then you go in
> /etc/apt/sources.list.d/pitti* and you replace the word with your
> Ubuntu version with "precise". By doing so you'll install that version
> into your distribution.

Thanks Vincenzo,

OMG, akthough it fixes my issue but this absolute painful proceduer!

by the way, I recived this error message after apt-get update:
---
Failed to fetch
bzip2:/var/lib/apt/lists/partial/archive.ubuntu.com_ubuntu_dists_quantal-updates_main_binary-amd64_Packages
 Hash Sum mismatch
Failed to fetch
bzip2:/var/lib/apt/lists/partial/archive.ubuntu.com_ubuntu_dists_quantal-updates_main_binary-i386_Packages
 Hash Sum mismatch
Some index files failed to download. They have been ignored, or old
ones used instead.

--
Best Regards
Muhammad Bashir Al-Noimi


-- 
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] pgAdmin for ubuntu

[Vincenzo Romano]

2013/7/17 Muhammad Bashir Al-Noimi :
> Howdy,
>
> After upgrading my Pg from 9.1 to 9.2 the avaliable pgAdmin in ubuntu
> 21.10 reporotiy can't deal with Pg 9.2.
>
> How can I get recent pgAdmin version for ubuntu 12.10?
>
> P.S.
> - I tried ppa:pitti/postgresql but it doesn’t contain on suitable
> pgAdmin version for Pg 9.2
> - I don’t prefer to build pgAdmin from the source code.
>
> --
> Best Regards
> Muhammad Bashir Al-Noimi

Once you "sudo add-apt-repository ppa:pitti/postgresql" then you go in
/etc/apt/sources.list.d/pitti* and you replace the word with your
Ubuntu version with "precise". By doing so you'll install that version
into your distribution.
I know, that sadly the transition from launchpad.net to
apt.postgresql.org just covers 10.04 and 12.04. Not even 13.04!
In the meanwhile ...


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] pgAdmin for ubuntu

[Muhammad Bashir Al-Noimi]

Howdy,

After upgrading my Pg from 9.1 to 9.2 the avaliable pgAdmin in ubuntu
21.10 reporotiy can't deal with Pg 9.2.

How can I get recent pgAdmin version for ubuntu 12.10?

P.S.
- I tried ppa:pitti/postgresql but it doesn’t contain on suitable
pgAdmin version for Pg 9.2
- I don’t prefer to build pgAdmin from the source code.

--
Best Regards
Muhammad Bashir Al-Noimi


-- 
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] Upgrading from Pg 9.1 to 9.2

[Muhammad Bashir Al-Noimi]

On Tue, Jul 16, 2013 at 11:52 PM, salah jubeh  wrote:
> Also have a look here
>
> http://wiki.postgresql.org/wiki/Using_pg_upgrade_on_Ubuntu/Debian


Thanks a lot, I could successfully upgrade my server

--
Best Regards
Muhammad Bashir Al-Noimi


-- 
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] Build RPM from Postgres Source

[Devrim GÜNDÜZ]

Hi,

On Fri, 2013-07-12 at 14:37 -0700, ktewari1 wrote:
> Hi,
> I need to have some different settings(like NAMEDATALEN  etc.) and
> that's why I'm trying to build postgres from the source and to create an rpm
> to be send for install. 
> 
> Now, the build works fine but, I don't see a way to create an RPM.

Here is how we build the community RPMs:

* Fetch patches and spec files from SVN. For example:

http://svn.pgrpms.org/browser/rpm/redhat/9.2/postgresql/EL-6/

* Add any patches to spec file:

http://svn.pgrpms.org/browser/rpm/redhat/9.2/postgresql/EL-6/postgresql-9.2.spec#L89

* Build the RPM.

You can add your own patches, and build your own RPMs like this.

Regards,
-- 
Devrim GÜNDÜZ
Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com
PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer
Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org  Twitter: http://twitter.com/devrimgunduz


signature.asc
Description: This is a digitally signed message part

Re: [GENERAL] transactional swap of tables

[Vincenzo Romano]

I have done the following test pn v9.2.4 with two concurrent sessions:

-- session no.1
tmp1=# create table t1 ( t text );
CREATE TABLE
Tempo: 37,351 ms
tmp1=# create table t2 ( t text );
CREATE TABLE
Tempo: 33,363 ms
tmp1=# create or replace function f1( out tx text )
tmp1-# language plpgsql
tmp1-# stable as $l0$
tmp1$# begin
tmp1$# select t into tx from t1 limit 1;
tmp1$# end;
tmp1$# $l0$;
CREATE FUNCTION
Tempo: 14,148 ms
tmp1=# create or replace function f2( out tx text )
tmp1-# language plpgsql
tmp1-# volatile as $l0$
tmp1$# begin
tmp1$# select t into tx from t1 limit 1;
tmp1$# end;
tmp1$# $l0$;
CREATE FUNCTION
Tempo: 12,712 ms
tmp1=# insert into t1 values ( 'ciao' );
INSERT 0 1
Tempo: 14,777 ms
tmp1=# insert into t2 values ( 'hello' );
INSERT 0 1
Tempo: 9,032 ms
tmp1=# select * from f1();
  tx
--
 ciao
(1 riga)

Tempo: 0,600 ms
tmp1=# select * from f2();
  tx
--
 ciao
(1 riga)

Tempo: 0,549 ms

-- session no.2
tmp1=# begin;
BEGIN
Tempo: 0,287 ms
tmp1=# alter table t1 rename to t3;
ALTER TABLE
Tempo: 1,023 ms
tmp1=# alter table t2 rename to t1;
ALTER TABLE
Tempo: 0,533 ms
tmp1=# alter table t3 rename to t2;
ALTER TABLE
Tempo: 0,449 ms

-- back to session no.1
tmp1=# select * from f1();
-- not ending, possibly due to table lock

-- back to session no.2
tmp1=# commit;
COMMIT
Tempo: 10,986 ms

-- back to session no.1
  tx
---
 hello
(1 riga)

Tempo: 39946,137 ms

The result changes slightly if I query the function f1() just after ALTERing t1.
In this case from f1() I get NULL as result after COMMIT on session no.2.
A subsequent query returns 'hello'.
While from f2() I get always the right result.
This makes me think that the volatility specification in the function
declaration obviously changes something in the caching of the catalog
queries.
The NULL remains a mystere for me.
Any hint? Any way to avoid such a behaviour?


-- 
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] V8.4 TOAST table problem

[David Welton]

Hi,

I'm talking about our own massively bloated toast table - described in
an earlier post - that I think I can replicate.  I didn't mean to
steal your thread, but the problem seems very similar, and we're using
9.1.  I don't know a lot about Postgres internals, but to me it smells
like a bug of some sort.

On Mon, Jul 15, 2013 at 7:23 PM, Bradley McCune  wrote:
> David,
>
> I'm sorry, but I'm not sure that I follow how this is pertinent to this
> particular thread.  Are you proposing a way to replicate the scenario we
> experienced of our massively bloated TOAST table?  If so, I'm not entirely
> sure that's doable given that the source of the issue was never clear.
> There still remains a number of reasons for why that table had so much
> "still in use" bloat.  At this moment, it's near impossible to tell given
> that it is no longer a problem.
>
> Thanks for the offer, and I apologize if I'm just slightly ignorant about
> your intentions.
>
>
> On Mon, Jul 15, 2013 at 4:33 AM, David Welton  wrote:
>>
>> Hi,
>>
>> I think I could write a script to do something similar to what is
>> happening if anyone is interested.  I'd want some direction as to the
>> best way to handle this though: it'd be easier for me to script it as
>> Rails code because that's what the app is.  Perhaps from that we can
>> get the generated SQL so as to make it easier for others to deal with.
>>  The operation itself is basically:
>>
>> * Extract a value from a row of a table that is stored as a bytea.
>>
>> * Unmarshall it into a Ruby object.
>>
>> * Add to that Ruby object.
>>
>> * update the row and set the value by marshalling the Ruby object.
>>
>> I suspect that the actual value isn't terribly relevant, and they
>> how's and why's of what it is like it is are best left for a different
>> discussion.
>>
>> --
>> David N. Welton
>>
>> http://www.dedasys.com/
>
>
>
>
> --
> Bradley D. J. McCune



--
David N. Welton

http://www.dedasys.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] Question re contribs of Julian Assange

[Luca Ferrari]

On Wed, Jul 17, 2013 at 10:15 AM, ERR ORR  wrote:
> I noticed on the postgresql website that a certain Julian Assange is
> mentioned among the contributors to Postgresql.
>
> Out of curiosity, could anybody post in short what exactly he contributed to
> the DB?


http://lmgtfy.com/?q=julian+assange+postgresql


-- 
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] Question re contribs of Julian Assange

[Magnus Hagander]

On Wed, Jul 17, 2013 at 9:15 AM, ERR ORR  wrote:
> I noticed on the postgresql website that a certain Julian Assange is
> mentioned among the contributors to Postgresql.
>
> Out of curiosity, could anybody post in short what exactly he contributed to
> the DB?

You can find a list of his contributions at:
http://git.postgresql.org/gitweb/?p=postgresql.git&a=search&h=HEAD&st=author&s=julian

Not sure if there might be others as well that he didn't commit
himself, but back in the days I think he mostly did.


--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.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] Question re contribs of Julian Assange

[ERR ORR]

I noticed on the postgresql website that a certain *Julian Assange* is
mentioned among the contributors to Postgresql.

Out of curiosity, could anybody post in short what exactly he contributed
to the DB?

Thanks, RD

Re: [GENERAL]

[ERR ORR]

- Is your postgres server actually up and running?
- Did you add localhost to the addresses served by postgres in pg_hba.conf
(with proper permissions)?
- Have you considered upgrading your postgres installation to a current
version? 8.0.0-rc1 is WAY obsolete.


On 23 March 2013 15:13, News Subsystem  wrote:

> Sat, 23 Mar 2013 07:13:47 -0700 (PDT)
>  23 Mar 2013 07:13:47 -0700 (PDT)
> X-Newsgroups: pgsql.general
> Date: Sat, 23 Mar 2013 07:13:47 -0700 (PDT)
> Complaints-To: groups-ab...@google.com
> Injection-Info: glegroupsg2000goo.googlegroups.com;
> posting-host=79.173.238.135;
>  posting-account=olGTqwoh6jEnGJU_fjLVfoHldlc3
> User-Agent: G2/1.0
> MIME-Version: 1.0
> Message-ID: <15aaed27-5c77-44e8-8cbe-fe451bb57...@googlegroups.com>
> Subject: Moteview database problem
> From: "Hana'a AL-Theiabat" 
> Injection-Date: Sat, 23 Mar 2013 14:13:47 +
> Content-Type: text/plain; charset=ISO-8859-1
> To: pgsql-general@postgresql.org
>
> when i install Moteview 2.0 on windows XP this problem appeared  :
>
> ""Moteview the database server localhost is not available please input a
> valid server name""
>
> the version of pgsql is PostgreSQL 8.0.0-rc1
>
> anyone have any idea to solve this ?
>
>
> --
> 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] Driver Question

[Luca Ferrari]

On Tue, Jul 16, 2013 at 5:36 PM, Corbett, James
 wrote:
> Hello all:
>
> My first official message so please be gentle with me.
>
> I’m attempting to make a new JDBC Connection Profile via my Eclipse IDE.
> Apparently it’s looking for the following jar in the driver wizard:
>
> postgresql-8.1-404.jdbc2.jar.
>
> However when I burrow down to locate the driver in my Jboss server as
> indicated below I only see the following.
>
> C:\APPS\lse_jboss-4.2.3.GA-1.1\server\default\lib\postgresql-9.0-801.jdbc4.jar


You have to edit the driver definition removing the 8.1 jar file from
the "JAR List" tab leaving only the recent driver.
I tried it with 9.1-902.jbdc4.jar and Eclipse Juno. I don't know why
Eclipse asks for such an old driver, I suppose is something not update
in the Eclipse pre-built configuration.

Luca


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general