Wondering if there's a way for postgres to return how
many elements are in a array as a single integer? For
instance, returning 10 (items in array) instead of
[-5:4]
Also, is there a way to return the position of an item
in a array?
__
Yahoo! Ma
I am not sure why I used subselect, I just saw an example and followed it..
But now it's working as it should... Thx for all the help! :)
BTJ
On 9/19/05, Bjørn T Johansen wrote:
CREATE OR REPLACE FUNCTION trykkStatus (pressID INTEGER)
RETURNS SetOf trykkstatus_type AS '
DECLARE
hi this following words from Dinesh did worked for me. Thanks guys
and really appreciate your help and advice.
PG_DATA=/usr/local/pgsql/data
regards
suresh
---(end of broadcast)---
TIP 6: explain analyze is your friend
On Sep 21, 2005, at 10:34 AM, CSN wrote:
id, title, yield, directions
and would like to prevent duplicate records from being
added (i.e. according to the title, yield, and
directions fields).
- Create a unique index across the title, yield, and
directions fields.
This scenario is exactl
I have a table like so:
id, title, yield, directions
and would like to prevent duplicate records from being
added (i.e. according to the title, yield, and
directions fields). I won't normally be querying on
the yield or directions fields, so I just have indexes
for id and title. What's the best w
On Tue, Sep 20, 2005 at 11:04:44AM -0400, Belinda M. Giardine wrote:
> > For vacuuming, you could try to use the autovacuum daemon, it is
> > included in the contrib part of postgres. If you installed from source,
> > you will likely need to separately install autovacuum, if you installed
> > a pre
On Mon, Sep 19, 2005 at 07:23:54AM +0200, hubert depesz lubaczewski wrote:
> On 9/18/05, Jim C. Nasby <[EMAIL PROTECTED]> wrote:
> >
> > ltree is part of contrib, right?
>
>
>
> yes.
>
> You probably need to define a functional index of some kind. How are you
> > querying now? IIRC you'll be d
Bill Moseley <[EMAIL PROTECTED]> writes:
> ws2=> select count(*) from person_role;
> count
> ---
>123
> (1 row)
> ...
>-> Seq Scan on person_role (cost=0.00..2.83 rows=1 width=4) (actual
> time=0.130..0.165 rows=1 loops=1)
> Filter: ((role = 2) AND (person = 94))
> Why do
On Tue, 20 Sep 2005, Bill Moseley wrote:
> ws2=> select count(*) from person_role;
> count
> ---
>123
> (1 row)
>
> ws2=> select count(*) from person;
> count
> ---
> 11033
> (1 row)
>
> ws2=> EXPLAIN ANALYZE select id, first_name, last_name from person,
> person_role where id = 94
Brandon Metcalf wrote:
> We're using pg_autovacuum with PostgreSQL 8.0.3 and it isn't sleeping
> as long as the claims it will. For example,
>
> ...
> [2005-09-20 15:40:23 CDT] INFO: last_analyze_count: 21735550;
> last_vacuum_count: 21735009
> [2005-09-20 15:40:23 CDT] INFO:
I'm still trying to understand EXPLAIN ANALYZE output.
ws2=> select count(*) from person_role;
count
---
123
(1 row)
ws2=> select count(*) from person;
count
---
11033
(1 row)
ws2=> EXPLAIN ANALYZE select id, first_name, last_name from person, person_role
where id = 94 and
On 9/20/05, Berend Tober <[EMAIL PROTECTED]> wrote:
[snip]
> I guess I originally thought using INHERIT rather than LIKE was that,
> having the audit history, I might at some point present a select view
> across both the base and descendant tables or something ("...if you
> record it, they (PHB's
We're using pg_autovacuum with PostgreSQL 8.0.3 and it isn't sleeping
as long as the claims it will. For example,
...
[2005-09-20 15:40:23 CDT] INFO: last_analyze_count: 21735550;
last_vacuum_count: 21735009
[2005-09-20 15:40:23 CDT] INFO: analyze_threshold: 40676;
vacuum_
You can set up pg_hba.conf so that only certain Unix users that have
access to the local Unix PostgreSQL socket can access the database
without a password (every other process uses a TCP/IP connection); then
move the socket location to other than /tmp and restrict its access w/
Unix controls. De
I was thinking if this was possible in some way..
I have this table where we have X and Y coordinates, and i need to
select several in one go.
# select * from xy where (x = 1 and y = 2) or (x = 2 and y = 2);
This works but are not so nice looking.
It would be nice to be able to do it like this:
On Tue, 20 Sep 2005, Philip Hallstrom wrote:
contrib/tsearch2 ( http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/
)
might works for you. It might because performance depends on cardinality of
your keywords.
Seconded. We use tsearch2 to earch about 40,000 rows containing
manufacturer,
contrib/tsearch2 ( http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/ )
might works for you. It might because performance depends on cardinality of
your keywords.
Seconded. We use tsearch2 to earch about 40,000 rows containing
manufacturer, brand, and product name and it returns a result
I"m by no means an expert on this, and perhaps someone with more
knowledge can help, but it looks to me like the planner estimate and
the actual cost are significantly different which to me means that an
analyze is required, or/and increase the stats on these tables would be
usefull. Also I'm wond
contrib/tsearch2 ( http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/ )
might works for you. It might because performance depends on
cardinality of your keywords.
Oleg
On Tue, 20 Sep 2005, Yonatan Ben-Nes wrote:
Hi all,
Im building a site where the users can search for products
Hi all,
Im building a site where the users can search for products with up to 4
diffrent keywords which all MUST match to each product which found as a
result to the search.
I got 2 tables (which are relevant to the issue :)), one is the product
table (5 million rows) and the other is the ke
On Tue, 20 Sep 2005, John DeSoi wrote:
>
> On Sep 19, 2005, at 2:27 PM, Belinda M. Giardine wrote:
>
> > As far as I can tell vacuum must be run by the table or database
> > owner.
> > It wouldn't be as bad to have the password in the script if it was a
> > limited permissions user. Any suggest
On Tue, 20 Sep 2005, Csaba Nagy wrote:
> You could use the system wide crontab, which is configured via
> /etc/crontab, and there you can specify what user should execute the
> command, without needing passwords. The system wide crontab is executed
> as root and it will su to the user you specif
You might be looking for a DEFERRED constraint.
You can declare your constraint to be checked only at transaction end,
and then make all your data changes, in one transaction.
You will find details here:
http://www.postgresql.org/docs/8.0/static/sql-createtable.html
Search for DEFERRED.
HTH,
Csab
Oleg wrote:
Dear All,
is it possible to temporary deactivate a constraint in PostgreSQL?
There is a constraint that does not allow me to write some data (see
e-mail below). But after all datasets are written the constraint is
valid. So I was wondering wether it is possible to deactivate a
cons
At 10:00 AM 9/20/2005 -0400, Vivek Khera wrote:
On Sep 14, 2005, at 9:45 AM, vinita bansal wrote:
I have a 4 proc. AMD Opteron machine with 32 GB RAM and ~400GB HDD
and a 40GB database. I need to take backup of this database and
restore it some other location (say some test environment). I am
Dear All,
is it possible to temporary deactivate a constraint in PostgreSQL?
There is a constraint that does not allow me to write some data (see
e-mail below). But after all datasets are written the constraint is
valid. So I was wondering wether it is possible to deactivate a
constraint write
> Indeed. But just to stress the point, I wasn't stating that the included
> replication in MySQL was any good (though it's not terrible as we're
> using it heavily in an extremely high-volume situation with few
> problems), I was just bringing up the idea of getting a decent
> replication solution
On Sep 15, 2005, at 11:25 PM, Logan Bowers wrote:Many of the other folks at the company feel an open-source DB is more risky because it is less well tested compared to commercial counterparts. I’m looking for examples of large installations of Postgres with huge data sets, high traffic volumes, hi
On Sep 15, 2005, at 9:56 AM, Peter Alberer wrote:
I compiled postgres in 64-bit mode by adding the following switches
in the
make file:
To the gcc lines: -m64 -mcpu=power5 -mtune=power5
To ld lines: -m elf64ppc
FWIW Postgres works splendidly in 64-bit mode on FreeBSD on Opteron
On Sep 14, 2005, at 9:45 AM, vinita bansal wrote:
I have a 4 proc. AMD Opteron machine with 32 GB RAM and ~400GB HDD
and a 40GB database. I need to take backup of this database and
restore it some other location (say some test environment). I am
currently using pg_dump and pg_restore utili
On Sep 19, 2005, at 2:27 PM, Belinda M. Giardine wrote:
As far as I can tell vacuum must be run by the table or database
owner.
It wouldn't be as bad to have the password in the script if it was a
limited permissions user. Any suggestions on the best methods?
Setup a .pgpass file so you do
You could use the system wide crontab, which is configured via
/etc/crontab, and there you can specify what user should execute the
command, without needing passwords. The system wide crontab is executed
as root and it will su to the user you specify.
For vacuuming, you could try to use the autova
On Tuesday 20 September 2005 08:44, Berend Tober wrote:
> Greg Sabino Mullane wrote:
> >-BEGIN PGP SIGNED MESSAGE-
> >Hash: SHA1
[MS: on audit tables]
> >>Can anyone relate their experiences with such a thing? Which
> >> approaches should I take into consideration?
> >
> >I like the multi-
Mike Rylander wrote:
On 9/20/05, Berend Tober <[EMAIL PROTECTED]> wrote:
/*
The following is based on suggestion by Mike Rylander posted on
Postgresql-General
Sun, 18 Sep 2005 23:29:51 +
Rylander's original suggestion employed a trigger and tracked
only row updates. My implementation ma
This seems like it should be a frequently asked question, but I am having
trouble finding the answer. I am in the process of switching to using
Postgres, and realize that I need to run vacuum analyze regularly on the
tables. This is on a Unix system so cron is the obvious choice. The
problem is
On 9/20/05, Berend Tober <[EMAIL PROTECTED]> wrote:
> /*
> The following is based on suggestion by Mike Rylander posted on
> Postgresql-General
> Sun, 18 Sep 2005 23:29:51 +
>
> Rylander's original suggestion employed a trigger and tracked
> only row updates. My implementation makes use of rul
Martijn van Oosterhout schrieb:
Yes you'r right here. Because we use Cursor Fetch, every statement
starts a transaction. So your right I tested it and this forces a table
lock. Hm... i will look how to do this in another way.
Just COMMIT when you're done. This does kill the cursor thou
hi,
i'd like to know what sql commands were issued to a postgresql database
this morning.
i know this is not the most technically-formulated question, but this is
what i'd like to know..
is there a way?
the transaction logs... they should have registered all the db-changes,
aren't they?
On Tue, Sep 20, 2005 at 12:01:46PM +0200, Daniel Schuchardt wrote:
> Martijn van Oosterhout schrieb:
> >I think you'll find that locks are held to the end of the transaction.
> >You're not holding a transaction open but not doing anything, are you?
> >
> >
> Yes you'r right here. Because we use Cu
Martijn van Oosterhout schrieb:
On Tue, Sep 20, 2005 at 11:18:48AM +0200, Daniel Schuchardt wrote:
So if we want to change a table structure (add a field or sth like this)
many clients own AccessShareLock's because it seams that a simple SELECT
* FROM table will grant a AccessShareLock and
On Tue, Sep 20, 2005 at 11:18:48AM +0200, Daniel Schuchardt wrote:
> So if we want to change a table structure (add a field or sth like this)
> many clients own AccessShareLock's because it seams that a simple SELECT
> * FROM table will grant a AccessShareLock and don't release it unitl the
> co
Stas Oskin schrieb:
Hi.
We are using PostgreSQL as the RDBMS for our product, and are very
happy with it. Recently, we have encountered a need to store a lot of
binary files, mainly images (up to ~100,000 files, with sizes varying
from 300K-2MB).
The question is, how well PostgreSQL
Hi group,
I have the following problem:
We have developed a ERP/PPS Developed with pgsql over the last 4 years.
Now we introduce it on some of our customers {pgsql works great and gets
good ratings :-)} and so we have to change Tablestructure and so on very
often. For technologie reasons eve
Berend Tober wrote:
...See "User Comments" at
"http://www.postgresql.org/docs/8.0/interactive/tutorial-inheritance.html";
for something that should set you afire.
And, commenting on my own post, try this cool function:
/*
The following is based on suggestion by Mike Rylander posted on
P
44 matches
Mail list logo
