Re: [GENERAL] Submit query using dblink that hung the host

[Alban Hertroys]

On 14 Jun 2012, at 20:25, Merlin Moncure wrote:

>> CREATE OR REPLACE VIEW missing_archiveset_in_mds_ops
>>  (filename, esdt, archiveset) AS
>> select * from dblink('host=ops_host port=4001 user=omiops dbname=omiops',
>> 'select filename, esdt, archiveset from
>>  filemeta_archiveset join filemeta_common using(fileid)
>>  join file using(fileid)') as t1(filename text,esdt text,archiveset int)
>> where (filename, esdt, archiveset) not in (
>> select filename, esdt, archiveset
>>  from dblink('host=ops_host port=4002 user=omiops dbname=metamine',
>> 'select filename, esdt, archiveset from
>>  file_archiveset join filemeta using(fileid)
>>  join filename using(fileid)') as t2(filename text,esdt text,archiveset
>> int));
> 
> In the meantime, restructure both dblinks to gather the data into
> separate local tables (temporary if you can wing it), then create
> indexes in advance of the join.


I was thinking along those lines, with the difference that I'd create a temp 
table with the data from the 2nd dblink query in the database of the first. 
That way you can perform the query in one database, which will only have to 
move the rows needed for the end result.

I also notice that you don't use any data from the local database in that query 
at all. Perhaps you could query the database on port 4001 instead? That would 
seem to make more sense for this particular query.

Alban Hertroys

--
The scale of a problem often equals the size of an ego.



-- 
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] Reference with inheritance propagate data

[Jeff Davis]

On Thu, 2012-06-14 at 13:08 +0400, Yuriy Rusinov wrote:
> We're need common numeration for primary key for all users tables, but
> others columns may be different for tables.

Will a sequence shared between the two tables solve this problem?

http://www.postgresql.org/docs/9.1/static/sql-createsequence.html

Regards,
Jeff Davis


-- 
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] parsing SQLERRM ?

[Albe Laurenz]

david.sahag...@emc.com wrote:
> (version == 9.1)
> 
> In my PL/pgSQL stored functions,
> I want to be able to distinguish which FK-constraint caused the
[foreign_key_violation] exception.
>   . . .
>   BEGIN
> delete from MY_COOL_TABLE where id = 123 ;
>   EXCEPTION
> WHEN foreign_key_violation THEN
>   CASE
> WHEN (SQLERRM tells me it blew up because of FK X)  THEN . . .
;
> WHEN (SQLERRM tells me it blew up because of FK Y)  THEN . . .
;
> WHEN (SQLERRM tells me it blew up because of FK Z)  THEN . . .
;
>   END;
> WHEN others THEN
>   raise;
>   END;
>   . . .
> 
> Is a "robust enough" parsing of SQLERRM actually the best/only way to
determine this ?

I think so.

Not that it is particularly nice, though.  It should be fairly robust to
search for the name of the constraint in the error message.

Yours,
Laurenz Albe

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

[GENERAL] Starting a cluster as a service

[Léa Massiot]

Hello and thank you for reading my post.

My problem is that I do not manage to start a PostgreSQL cluster as a
Windows service.
The OS is WinXP.

- I've created a PostgreSQL cluster "a_pgcluster" with the associated port
5433.
- Running "cmd.exe" under Windows as "a_user", I can start and stop it
manually in command line using the following commands:

- The cluster data directory is "a_pgcluster". Its (filesystem) owner is
"a_user" with this user having "full control" on it.

- Now, I would like it to be run automatically at machine startup as a
service.
- So I registered the cluster using the following command:

- When I go to "Control Panel" -> Administrative Tools" -> "Services" and
try to start the service, I get the following message:

- I also tried the following commands in command-line:


Can you help me try to figure out what's wrong?
Best regards.
--
OS: WinXP Pro SP3
DBMS: PostgreSQL v.9.1

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Starting-a-cluster-as-a-service-tp5712728.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] Trying to execute several queries involving temp tables in a PHP script

[Alexander Farber]

Thank you Misa, the without-temp-tables query has worked flawlessly.


On Wed, Jun 13, 2012 at 5:01 PM, Misa Simic  wrote:
> I think you can have all in one query, without temp tables:
>
>  SELECT r.rid, r.cards, to_char(r.stamp, 'DD.MM.
> HH24:MI') as day,
>                    c.bid, c.trix, c.pos, c.money, c.last_ip, c.quit,
>                    u.id, u.first_name, u.avatar, u.female, u.city,
> u.vip > CURRENT_DATE as vip
>                    FROM pref_rounds r, pref_cards c, pref_users u
>                    WHERE u.id = c.id and
>                        r.rid = c.rid and
>                        r.rid in (
>
>  select rid
>                    from pref_cards
>                    where stamp > now() - interval '1 day' and
>                    id in (
>  select id
>                    from pref_money
>                    where yw = to_char(current_timestamp - interval '1
> week', 'IYYY-IW')
>                    order by money
>                    desc limit 10
>
> ) and
>                    bid = 'Misere' and
>                    trix > 0
>
>
> )
>                    order by rid, pos;

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

[GENERAL] full text index / search

[Philipp Kraus]

Hello,

I have created a table with a text field under PG 9.1, that should store source 
codes. I would like to search in this text field with regular expressions. I 
think I need a full-text-index, do I?
How can I create this index, do I need some additional extensions? The PG 
server runs under OSX (installed on the DMG package).

Thanks

Phil
-- 
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] Empty arrays vs. NULLs, 9.1 & 8.3

[Tom Lane]

Ken Tanzer  writes:
> Thanks Steve.  FWIW I looked at the 9.0 and 9.1 release notes, and didn't
> find much on arrays in them.

This particular issue was fixed in 8.4.

> I do have one follow-up curiosity question, though.  Why does
> array_dims(array[]::varchar[]) return NULL instead of 0?  I would expect
> NULL for a NULL array, but not an empty one.  (And the same for
> array_[upper,lower,length] functions as well.

Yeah, there are still a lot of inconsistencies there :-(.  Eventually
I'd like to see somebody go through all the array operations and make
a proposal for consistent handling of empty arrays.  I think it would
be better if we changed all those things at once, rather than causing
piecemeal compatibility hits.

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] Starting a cluster as a service

[Thomas Boussekey]

Hello Léa,

Command line, message and commands are missing.
Difficult to help you!

Regards,
Thomas

2012/6/15 Léa Massiot 

> Hello and thank you for reading my post.
>
> My problem is that I do not manage to start a PostgreSQL cluster as a
> Windows service.
> The OS is WinXP.
>
> - I've created a PostgreSQL cluster "a_pgcluster" with the associated port
> 5433.
> - Running "cmd.exe" under Windows as "a_user", I can start and stop it
> manually in command line using the following commands:
>
> - The cluster data directory is "a_pgcluster". Its (filesystem) owner is
> "a_user" with this user having "full control" on it.
>
> - Now, I would like it to be run automatically at machine startup as a
> service.
> - So I registered the cluster using the following command:
>
> - When I go to "Control Panel" -> Administrative Tools" -> "Services" and
> try to start the service, I get the following message:
>
> - I also tried the following commands in command-line:
>
>
> Can you help me try to figure out what's wrong?
> Best regards.
> --
> OS: WinXP Pro SP3
> DBMS: PostgreSQL v.9.1
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/Starting-a-cluster-as-a-service-tp5712728.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 INTO...RETURNING with partitioned table using trigger/rule

[John Lumby]

This topic came up before 
    http://archives.postgresql.org/pgsql-general/2010-12/msg00542.php
and there was some discussion on how-to.

Briefly,   the table is partitioned and there is an id column declared as
  id bigint DEFAULT nextval('history_id_seq'::regclass) NOT NULL
and the application issues
  "INSERT into history  (column-list which excludes id)  values () 
RETURNING id"

In our case,  the table is not (yet) partitioned and this RETURNING clause 
works,
but I need to partition the table.  And one of the requirements is that the
application source code must not need to be changed,  so e.g. not allowed to 
make
it explicitly fetch nextval('history_id_seq').

I am running postgresql-9.2beta2

I can get the re-direction of the INSERT *without* RETURNING to work
using either trigger or rule,   in which the trigger/rule invokes a procedure,
but whichever way I do it,  I could not get this RETURNING clause to work.
For a trigger,  the INSERT ... RETURNING was accepted but returned no rows,
(as I would expect),   and for the RULE,    the INSERT ... RETURNING was 
rejected
with :
 
ERROR:  cannot perform INSERT RETURNING on relation "history"
HINT:  You need an unconditional ON INSERT DO INSTEAD rule with a RETURNING 
clause.

but this hint was not much help,  since :

CREATE RULE insert_part_history as ON INSERT to history DO INSTEAD SELECT 
history_insert_partitioned(NEW) returning NEW.id
ERROR:  syntax error at or near "returning"
LINE 1: ...DO INSTEAD SELECT history_insert_partitioned(NEW) returning ...

In looking at what was happening for the RULE scenario,   I found I could get 
it to work by making some relatively
straightforward changes to the postgresql source code in the areas of rewrite, 
planner and portal,
basically just to set and propagate a flag to encourage all those components 
that my rewritten query
really would return something so let it do it.    All the infrastructure for 
making this work,  
i.e. actually building the result tuple and sending it to the output stream,  
was already there.

Is anyone else working on this or are there any plans to support it?   It does 
seem to be a useful capability.
I can send my patch in the the hackers list if there is any interest.

John
  
-- 
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] parsing SQLERRM ?

[Tom Lane]

"Albe Laurenz"  writes:
> david.sahag...@emc.com wrote:
>> In my PL/pgSQL stored functions,
>> I want to be able to distinguish which FK-constraint caused the
>> [foreign_key_violation] exception.
>> Is a "robust enough" parsing of SQLERRM actually the best/only way to
>> determine this ?

> I think so.

Yeah, at the moment.  There are plans to fix this, but it won't happen
before 9.3 at the earliest.

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] Get RULE condition and commands

[Tom Lane]

Vlad Arkhipov  writes:
> What is the proper way of getting RULE condition and commands?

pg_get_ruledef()

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] Smaller multiple tables or one large table?

[Benedict Holland]

Hi All,

I am on postgres 9.0. I don't know the answer to what should be a fairly
straight forward question. I have several static tables which are very
large (around the order of 14 million rows and about 10GB). They are all
linked together through foreign keys and indexed on rows which are queried
and used most often. While they are more or less static, update operations
do occur. This is not on a super fast computer. It has 2 cores with 8gb of
ram so I am not expecting queries against them to be very fast but I am
wondering in a structural sense if I should be dividing up the tables into
1 million row tables through constraints and a view. The potential speedup
I could see being quite large where postgresql would split off all of the
queries into n table chucks running on k cores and then aggregate all of
the data for display or operation. Is there any documentation to make
postgesql do this and is it worth it?

Also, is there a benefit to have one large table or many small tables as
far indexes go?

Thanks,
~Ben

Re: [GENERAL] Smaller multiple tables or one large table?

[John R Pierce]

On 06/15/12 11:34 AM, Benedict Holland wrote:
I am on postgres 9.0. I don't know the answer to what should be a 
fairly straight forward question. I have several static tables which 
are very large (around the order of 14 million rows and about 10GB). 
They are all linked together through foreign keys and indexed on rows 
which are queried and used most often. While they are more or less 
static, update operations do occur. This is not on a super fast 
computer. It has 2 cores with 8gb of ram so I am not expecting queries 
against them to be very fast but I am wondering in a structural sense 
if I should be dividing up the tables into 1 million row tables 
through constraints and a view. The potential speedup I could see 
being quite large where postgresql would split off all of the queries 
into n table chucks running on k cores and then aggregate all of the 
data for display or operation. Is there any documentation to make 
postgesql do this and is it worth it?


postgres won't do that, one query is one process.  your application 
could conceivably run multiple threads, each with a seperate postgres 
connection, and execute multiple queries in parallel, but it would have 
to do any aggregation of the results itself.




Also, is there a benefit to have one large table or many small tables 
as far indexes go? 


small tables only help  if you can query the specific table you 'know' 
has your data, for instance, if you have time based data, and you put a 
month in each table, and you know that this query only needs to look at 
the current month, so you just query that one month's table.




--
john r pierceN 37, W 122
santa cruz ca mid-left coast


--
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] Smaller multiple tables or one large table?

[Benedict Holland]

Will the processes know that I have n tables which are constrained in their
definition on primary keys? I am thinking a table constraint specifying
that the primary key on that table is within some boundary. That way the
single process can spawn one thread per n table and leave the thread
management to the OS. Assuming it is well behaved, this should use every
ounce of resource I throw at it and instead of sequentially going though
one large table, it will sequentially go through 1 of n short tables in
parallel with k other tables. The results of this would have to be
aggregated but with a large enough table, the aggregation would pale in
comparison to the run time of the query split between several smaller
tables.

The tables would have to be specified with a table pk constraint falling
between two ranges. A view would then be created to manage all of the small
tables with triggers handling insert and update operations. Select would
have to be view specific but that is really cheap compared to updates. That
should have the additional benefit of only hitting a specific table(s) with
an update.

Basically, I don't see how this particular configuration breaks and if
PostgreSQL already has the ability to do this as it seems very useful to
manage very large data sets.

Thanks,
~Ben

On Fri, Jun 15, 2012 at 2:42 PM, John R Pierce  wrote:

> On 06/15/12 11:34 AM, Benedict Holland wrote:
>
>> I am on postgres 9.0. I don't know the answer to what should be a fairly
>> straight forward question. I have several static tables which are very
>> large (around the order of 14 million rows and about 10GB). They are all
>> linked together through foreign keys and indexed on rows which are queried
>> and used most often. While they are more or less static, update operations
>> do occur. This is not on a super fast computer. It has 2 cores with 8gb of
>> ram so I am not expecting queries against them to be very fast but I am
>> wondering in a structural sense if I should be dividing up the tables into
>> 1 million row tables through constraints and a view. The potential speedup
>> I could see being quite large where postgresql would split off all of the
>> queries into n table chucks running on k cores and then aggregate all of
>> the data for display or operation. Is there any documentation to make
>> postgesql do this and is it worth it?
>>
>
> postgres won't do that, one query is one process.  your application could
> conceivably run multiple threads, each with a seperate postgres connection,
> and execute multiple queries in parallel, but it would have to do any
> aggregation of the results itself.
>
>
>
>> Also, is there a benefit to have one large table or many small tables as
>> far indexes go?
>>
>
> small tables only help  if you can query the specific table you 'know' has
> your data, for instance, if you have time based data, and you put a month
> in each table, and you know that this query only needs to look at the
> current month, so you just query that one month's table.
>
>
>
> --
> john r pierceN 37, W 122
> santa cruz ca mid-left coast
>
>
> --
> 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] full text index / search

[Mark Phillips]

I am not an expert on FTS, but I have been reading and experimenting. Further, 
I don't know what you are really attempting. With those warnings behind us, I 
think a GIN or GiST index are helpful in full text searches.

You may find this useful:
Understanding Full Text Search
 http://linuxgazette.net/164/sephton.html

I suggest that you review the Postgres Documentation for FTS:
 http://www.postgresql.org/docs/9.1/interactive/textsearch.html

One option you may find interesting is the pg_trgm module:
 http://www.postgresql.org/docs/9.1/static/pgtrgm.html

hth,

- Mark Phillips

On Jun 15, 2012, at 8:18 AM, Philipp Kraus wrote:

> Hello,
> 
> I have created a table with a text field under PG 9.1, that should store 
> source codes. I would like to search in this text field with regular 
> expressions. I think I need a full-text-index, do I?
> How can I create this index, do I need some additional extensions? The PG 
> server runs under OSX (installed on the DMG package).
> 
> Thanks
> 
> Phil
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


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

Re: [GENERAL] pg_upgrade: "pg_ctl failed to start the new server"

[Bruce Momjian]

On Thu, Jun 14, 2012 at 08:03:49AM -0400, Evan D. Hoffman wrote:
> So it appears the problem was inability to connect, although
> pg_upgrade reported that it couldn't start the server (I assume
> ability to connect is how it determines whether or not the server was
> started).

Ah, OK, so it was the connection that failed.  I wasn't aware pg_ctl
start could fail in that case (we added PGping), but obviously it can.

The attached patch applied to PG 9.2 and current will give a more
appropriate error message saying it might be a connection problem.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +
diff --git a/contrib/pg_upgrade/server.c b/contrib/pg_upgrade/server.c
new file mode 100644
index f83d6fa..57f09d4
*** a/contrib/pg_upgrade/server.c
--- b/contrib/pg_upgrade/server.c
*** start_postmaster(ClusterInfo *cluster)
*** 193,199 
  
  	/* If the connection didn't fail, fail now */
  	if (pg_ctl_return != 0)
! 		pg_log(PG_FATAL, "pg_ctl failed to start the %s server\n",
  			   CLUSTER_NAME(cluster));
  
  	os_info.running_cluster = cluster;
--- 193,199 
  
  	/* If the connection didn't fail, fail now */
  	if (pg_ctl_return != 0)
! 		pg_log(PG_FATAL, "pg_ctl failed to start the %s server, or connection failed\n",
  			   CLUSTER_NAME(cluster));
  
  	os_info.running_cluster = cluster;

-- 
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] Smaller multiple tables or one large table?

[Gabriele Bartolini]

Hi Benedict,

Il 15/06/12 20:58, Benedict Holland ha scritto:
The tables would have to be specified with a table pk constraint 
falling between two ranges. A view would then be created to manage all 
of the small tables with triggers handling insert and update 
operations. Select would have to be view specific but that is really 
cheap compared to updates. That should have the additional benefit of 
only hitting a specific table(s) with an update.


Basically, I don't see how this particular configuration breaks and if 
PostgreSQL already has the ability to do this as it seems very useful 
to manage very large data sets.


What you are looking for is called 'partitioning' (horizontal 
partitioning). I suggest that you read this chapter: 
http://www.postgresql.org/docs/9.1/static/ddl-partitioning.html


Cheers,
Gabriele

--
 Gabriele Bartolini - 2ndQuadrant Italia
 PostgreSQL Training, Services and Support
 gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it


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