Thank you Kevin and Jeff for the responses.
These are very helpful.
On Fri, Sep 6, 2013 at 10:48 PM, Jeff Janes wrote:
> On Friday, September 6, 2013, pg noob wrote:
>
>>
>> Hi all,
>>
>> I'm curious about some of the query estimates that I'm seeing wi
Hi all,
I'm curious about some of the query estimates that I'm seeing with queries
that use DISTINCT.
I am using postgres 8.4.13
I did a couple of quick tests, and found that PostgreSQL seems to do some
expensive work to
return DISTINCT rows. This is contrary to what I was expecting because I
ex
Hi all,
I recently ran a couple of tests where I took one of my production
systems and did a drop-in replacement of postgres 8.4 with 9.2.4.
I was expecting to see some performance improvement given the release
notes describing 9.2 as a "largely performance related release".
At least for my appli
Hi all,
In PostgreSQL 8.4...
I am wondering if autovacuum will periodically rebuild indexes?
If not, how advisable is it to reindex periodically?
We recently had a case of unique index corruption which ended up allowing
duplicate
primary key IDs to get inserted and caused widespread data model
9.3, please, where RI uses more gently locks
>
> It still behaves this way in 9.4dev.
>
> >>
> >> On Mon, Jul 8, 2013 at 9:54 AM, pg noob wrote:
> >>>
> >>>
> >>> Thank you for the responses. Is it a bug?
>
> I don't think s
40 AM, Moshe Jacobson wrote:
> Confirmed reproducible on version 9.1 as well. Very odd.
>
>
> On Wed, Jul 3, 2013 at 1:30 PM, pg noob wrote:
>
>>
>> Hi all,
>>
>> I am trying to understand some odd locking behaviour.
>> I apologize in advance if this is a
Hi all,
I am trying to understand some odd locking behaviour.
I apologize in advance if this is a basic question and should be widely
understood but
I don't see it described in the documentation as far as I could find.
I'm using Postgres 8.4.13
I have two tables, call them A & B for example purp
un 26, 2013, at 11:04 AM, pg noob wrote:
>
> >
> > Hi all,
> >
> > There are some places in our application where unique constraint
> violations are difficult to avoid due to multithreading.
> > What we've done in most places to handle this is to retry in
Hi all,
There are some places in our application where unique constraint violations
are difficult to avoid due to multithreading.
What we've done in most places to handle this is to retry in a loop.
Generally it starts by checking if a value already exists, if not - try to
insert it, which may ca
previous block
(block 40) to create block boundary, making free space?
So from these logs, this looks like this is happening at file level or
hardware level, Am I correct?
Thank you again.
- Nachiket
On Thu, May 23, 2013 at 6:22 AM, Tom Lane wrote:
> PG User writes:
> > ... Later
Hi All,
We are facing one strange problem about data file corruptions.
We have many postgres databases. At some point, one simple query on one
database started crashing back-end.
The query is
select count(*), col1 from tab1 group by col1;
After using pg_filedump (http://pgfoundry.org/projects
hi-
supposing i have the following table structure:
vendors
- id int
- name varchar
products
- product_list_id int
- vendor_id int references vendors
- part_number varchar
- description varchar
- price
- textsearch
how can i execute some type of join and get the name of the vendor to be
inclu
b garbage? Looking at the
code for enough_space(), it is almost hard to believe that a lot of time is
being spent here, but perhaps it's just getting called a LOT from
repair_frag(). I'm no stranger to hacking code if I have tothanks!
Sam
On Fri, Jan 22, 2010 at 4:06 PM, PG User 2010 wro
Hi Tom,
As always, your insight is VERY helpful. We'll try your suggestions and see
if that helps things out... Thanks!
Sam
On Fri, Jan 22, 2010 at 4:01 PM, Tom Lane wrote:
> PG User 2010 writes:
> > 1) is there any easy way to fiddle with the vacuum process so that it is
Hi there,
I originally posted these questions to the pgsql-performance mailing list,
but due to lack of response, I think that these may be more general in
nature--so I'm re-posting them here. Apologies for the cross-posting ahead
of time.
We are having real issues trying to reclaim dead blob sp
e extremely unlikely event that
PostgreSQL becomes compromised.
"
So, I'm running a recent enough version of pg, now how do I get it to
drop Administrator privs so it will run, rather than give me an error?
Is there some special command line -c option or something (I would have
though
Here I'm posting a function to convert array to records.
any other suggestions are welcome
create or replace function array_to_records(int[]) RETURNS SETOF record AS
$$
DECLARE
ret_rec record;
a int;
b int;
BEGIN
b = length(array_dims($1));
a = substr(array_dims($1),4, (b-4) );
FOR
Which data type is smaller and will lead to better query performance -
smallint or char?
Basic query optimization question- does Postgres process
x IN (y1, y2)
as fast as
(x = y1 OR x = y2)
in a function?
I have tried testing the perofmance on indexing array element using standard
btree and it doesn't help anything. It still costing alot.
create index idx_properties_address_4 on properties ((address_arr[4]))
does contrib/intarray effective for text array?
On 3/16/06, Oleg Bartunov wrote:
co
Is it possible to search partial word as like '%...%' in tsearch?
Yudie
Help!
Try to install plperl
./createlang plperl mydb
createlang: language installation failed: ERROR: could not access file "$libdir/plperl": No such file or directory
Back to my original question where is it possible to run a command line from a function?
I'm running postgresql 7.4.1. is it can't really be used for trigger function?
http://www.postgresql.org/docs/7.4/static/plperl-missing.html
I want to execute a command line from trigger function.
The reason is I want to execute my perl script from stored procedure.
any idea?
Yudie
How can I possible to find out what table a record belong to from record's oid?
Thanks
Yudie
Hello,
the following used to work:
create table t1(t1f1 text, t1f2 text, t1f3 text);
create table t2(t2f2 text, t2f3 text);
insert ...
update t1
set t1f1='test'
where t1.t1f2=t2.t2f2
and t1.t1f3=t2.t2f3;
unfortunately, now I get the error that t2 is not in the FROM clause.
I know I can d
On 8/5/05, Tom Lane <[EMAIL PROTECTED]> wrote:
Certainly not --- per the SQL spec, different elements of a FROM listare independent, so the datelist relation can't refer to P.
(I think SQL 2003 has a construct called LATERAL that would allowsuch things, but we don't implement that yet.)The only wa
what about something likeselect id,datelistfrom payment as p, (select * from datelist('8/1/2005, 8/5/2005')) as list
where datelist between p.date_start and p.date_end;
That's works but have to put the whole date range into the parameters before it can be joined.
This would need 2 queries whe
try select * from payment as p, (select * from datelist('8/1/2005, 8/5/2005')) as datewhere date.. = p.
The problem is the function's parameters '8/1/2005', '8/5/2005' has to refer to whatever value on the payment records.
Hi everyone,
I have a function returning set of date called datelist(date,date)
example:
select * from datelist('8/1/2005, 8/5/2005');
8/1/2005
8/2/3005
8/3/2004
8/4/2005
8/5/2005
I would like to join this function with a table
create table payment(
id int4 not null,
date_start date,
date_e
I do not believe you can do this without a subquery - you are tryingto get 2 separate pieces of information from your data
* some data about the record having MAX(rank) for each categoryand * the count of records in each category
Hi, I guess i try to answer my own question which end up with c
Hello,
I have a table, structure like this:
create table product(
sku, int4 not null,
category int4 null,
display_name varchar(100) null,
rank int4 null
)
let say example data:
sku, category, display_name
===
10001, 5, postgresql, 132
10002, 5, mysql, 243
10003, 5, oracl
> In my client be empty value for date fields ('. . ') , and I would
> like to use NULL values and empty values also.
What is your reason to put empty value ('') as alternative of null value?
---(end of broadcast)---
TIP 7: don't forget to inc
On Tue, 29 Mar 2005 16:00:37 -0500, John Burger <[EMAIL PROTECTED]> wrote:
> >> If it were me, and someone proposed a model where two-way replication
> >> was needed, I would tell them to rethink their model. It's broken.
> >
> > I would respectfully disagree that the requirement for two-way
> > r
> One way to do this is to add a write_access column to actions and use
> a constraint to force it to be true.
>Create a UNIQUE key of
> (name, write_access) for user_data and then add a FOREIGN KEY
> reference from (name, write_access) in actions to (name, write_access)
> in user_data.
Yes the
> > create index prdt_new_url_dx on prdt_new (url)
> > create index prdt_new_sku_dx on prdt_new (sku)
> > create index prdt_old_sku_dx on prdt_old (sku)
> > create index prdt_new_url_null_dx on prdt_new (url) where prdt_new.url
> > IS NULL
I added indexes & redo the analyze - Query plan looks bett
> Also, this is important, have you anayzed the table? I'm guessing no,
> since the estimates are 1,000 rows, but the has join is getting a little
> bit more than that. :)
>
> Analyze your database and then run the query again.
I analyze the table and it decrease number of rows in nested loop o
Howdy! I apologize in advance for the ugly query I'm about to throw
your way
I need to delete some data from a table based on a multi-column join.
Is there a better way to write this?
delete
from tbldata
where unitID || '_' || variableID || '_' || cycleID in
(select unitID || '_' || variab
Hello, all. I have a query that runs perfectly when I run it from
pgAdmin3, but bombs when I run it from ColdFusion using the JDBC
driver. I'm using postgres 7.4. The query uses dblink(), which I assume
is the source of the problem.
Can anyone provide me with any insight about why this would fa
last query, I can use the client side
array as pulldown data without waiting for long transmition time.
I wonder if there is some more direct method, or thru the pg system tables
to get this info. If there's not out there, I would use a trigger which will
update a seperate table containin
41 matches
Mail list logo
